오라클 rman 마이그레이션
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 rman 이용한 이기종간 데이터 전환[편집]
- rman Backup 을 이용한 데이터 마이그레이션
- 이기종 플랫폼 마이그레이션이 가능.
- Convert 옵션을 사용하여 이기종간 datafile을 이관
1.1 RMAN 이용 마이그레이션 작업[편집]
1.1.1 소스 DB 작업[편집]
1) 백업 테스트 테이블 생성 및 데이터 입력
create table tb_test01 (
name varchar2(10) primary key
, address varchar2(10)
, bunho number);
-- 데이터 입력
begin
for a in 1..100 loop
for b in 1..100 loop
insert into tb_test01
values(dbms_random.string('U',10),dbms_random.string('A',7),dbms_random.value(10,100));
end loop;
commit;
end loop;
end;
-- 입력건수 확인
SQL> select count(*) from tb_test01;
COUNT(*)
----------
50000
-- tb_test01 테이블 인덱스 생성
create index ix_test01 on tb_test01(name,address);2) RMAN 백업
- rman 접속 및 환경설정
# su - oracle
/home/oracle> rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Feb 3 16:16:10 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1234567890)
RMAN>
run{
allocate channel c1 device type disk;
allocate channel c1 device type disk;
allocate channel c1 device type disk;
## data file backup
backup as compressed backupset format '/oracle/rman/data_%U_%T' database;
## archive log backup
backup as compressed backupset format '/oracle/rman/arch_%U_%T' archivelog all;
## control file backup
backup current controlfile format '/oracle/rman/ctl_%U_%T';
};
- 데이터파일 백업
RMAN> backup as compressed backupset format '/oracle/rman/data_%U_%T' database;
Starting backup at 03-FEB-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/oracle/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00001 name=/oracle/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/oracle/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00004 name=/oracle/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00005 name=/oracle/pump/tts01.dbf
channel ORA_DISK_1: starting piece 1 at 03-FEB-20
channel ORA_DISK_1: finished piece 1 at 03-FEB-20
piece handle=/oracle/rman/data_04unkofp_1_1_20200203 tag=TAG20200203T203049 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:16
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 03-FEB-20
channel ORA_DISK_1: finished piece 1 at 03-FEB-20
piece handle=/oracle/rman/data_05unkoi5_1_1_20200203 tag=TAG20200203T203049 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-FEB-20- 아카이브로그 백업
RMAN> backup as compressed backupset format '/oracle/rman/arch_%U_%T' archivelog all;
Starting backup at 03-FEB-20
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=730 RECID=1 STAMP=930621277
input archived log thread=1 sequence=731 RECID=2 STAMP=930621312
input archived log thread=1 sequence=732 RECID=3 STAMP=937752103
input archived log thread=1 sequence=733 RECID=4 STAMP=938045282
input archived log thread=1 sequence=734 RECID=5 STAMP=940360264
input archived log thread=1 sequence=735 RECID=6 STAMP=953856976
input archived log thread=1 sequence=736 RECID=7 STAMP=986504425
input archived log thread=1 sequence=737 RECID=8 STAMP=989797506
input archived log thread=1 sequence=738 RECID=9 STAMP=989797693
input archived log thread=1 sequence=739 RECID=10 STAMP=990842806
input archived log thread=1 sequence=740 RECID=11 STAMP=1020814571
input archived log thread=1 sequence=741 RECID=12 STAMP=1030929424
input archived log thread=1 sequence=742 RECID=13 STAMP=1031430214
input archived log thread=1 sequence=743 RECID=14 STAMP=1031430415
input archived log thread=1 sequence=744 RECID=15 STAMP=1031430423
input archived log thread=1 sequence=745 RECID=16 STAMP=1031430491
input archived log thread=1 sequence=746 RECID=17 STAMP=1031430611
input archived log thread=1 sequence=747 RECID=18 STAMP=1031430738
channel ORA_DISK_1: starting piece 1 at 03-FEB-20
channel ORA_DISK_1: finished piece 1 at 03-FEB-20
piece handle=/oracle/rman/arch_06unkoii_1_1_20200203 tag=TAG20200203T203218 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 03-FEB-20- 컨트롤 파일 백업
RMAN> backup current controlfile format '/oracle/rman/ctl_%U_%T';
Starting backup at 03-FEB-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 03-FEB-20
channel ORA_DISK_1: finished piece 1 at 03-FEB-20
piece handle=/oracle/rman/ctl_07unkoj9_1_1_20200203 tag=TAG20200203T203241 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-FEB-20
RMAN> exit
3) 백업 이후 Archive 발생 후 data 입력
SQL> select count(*) from tb_test01;
COUNT(*)
----------
800001.1.2 타겟 DB 작업[편집]
1) 테스트DB nomount로 기동
2) rman 접속
rman target /3) cotronfile 복원
- (복원 지정 장소는 init.ora 설정 한 곳에 복원)
RMAN> restore controlfile from '/oracle/rman/ctl_xxx_1_20230801';
Starting restore at 01-AUG-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oracle/temp/control01.ctl
Finished restore at 01-AUG-234) 타겟 DB mount 상태로 변경 후 백업파일 카탈로그 등록
SQL> alter database mount;
Database altered.
# 백업파일 카탈로그에 등록
catalog start with '/oracle/rman' -- 디렉토리 한꺼번에 등록;
# datafile 위치 확인
RMAN> report schema;
Starting implicit crosscheck backup at 04-FEB-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=162 device type=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 01-AUG-23
Starting implicit crosscheck copy at 01-AUG-23
using channel ORA_DISK_1
Finished implicit crosscheck copy at 01-AUG-23
searching for all files in the recovery area
cataloging files...
no files cataloged
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 750 SYSTEM *** /oracle/app/oracle/oradata/orcl/system01.dbf
2 600 SYSAUX *** /oracle/app/oracle/oradata/orcl/sysaux01.dbf
3 3395 UNDOTBS1 *** /oracle/app/oracle/oradata/orcl/undotbs01.dbf
4 132 USERS *** /oracle/app/oracle/oradata/orcl/users01.dbf
5 0 TTS *** /oracle/pump/tts01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 50 TEMP 32767 /oracle/app/oracle/oradata/orcl/temp01.dbf5) datafile 원복할 임시 저장 위치 지정
run {
allocate channel c1 device type disk;
set newname for datafile 1 to '/oracle/temp/system01.dbf';
set newname for datafile 2 to '/oracle/temp/sysaux01.dbf';
set newname for datafile 3 to '/oracle/temp/undotbs01.dbf';
set newname for datafile 4 to '/oracle/temp/users01.dbf';
set newname for datafile 5 to '/oracle/temp/tts01.dbf';
restore database;
switch datafile all;
}
6) using backup controlfile을 사용한 recovery 작업
- backup된 controlfile로 복구시에는 마지막 checkpoint SCN이 언제가 되는지 알수 없음.
- 그래서 datafile의 SCN을 보고 그 이후의 복구자료들(archive file, logfile)을 모두 복구
(using backup controlfile 옵션)
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 2928830 generated at 02/03/2020 20:30:49 needed for thread 1
ORA-00289: suggestion : /oracle/rman/arch/1_747_886462926.dbf
ORA-00280: change 2928830 for thread 1 is in sequence #747
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 2929405 generated at 02/03/2020 20:32:18 needed for thread 1
ORA-00289: suggestion : /oracle/rman/arch/1_748_886462926.dbf
ORA-00280: change 2929405 for thread 1 is in sequence #748
ORA-00278: log file '/oracle/rman/arch/1_747_886462926.dbf' no longer needed
for this recovery
ORA-00279: change 2931181 generated at 02/03/2020 20:34:08 needed for thread 1
ORA-00289: suggestion : /oracle/rman/arch/1_749_886462926.dbf
ORA-00280: change 2931181 for thread 1 is in sequence #749
ORA-00278: log file '/oracle/rman/arch/1_748_886462926.dbf' no longer needed
for this recovery
ORA-00279: change 2931205 generated at 02/03/2020 20:34:11 needed for thread 1
ORA-00289: suggestion : /oracle/rman/arch/1_750_886462926.dbf
ORA-00280: change 2931205 for thread 1 is in sequence #750
ORA-00278: log file '/oracle/rman/arch/1_749_886462926.dbf' no longer needed
for this recovery
ORA-00279: change 2931234 generated at 02/03/2020 20:34:15 needed for thread 1
ORA-00289: suggestion : /oracle/rman/arch/1_751_886462926.dbf
ORA-00280: change 2931234 for thread 1 is in sequence #751
ORA-00278: log file '/oracle/rman/arch/1_750_886462926.dbf' no longer needed
for this recovery
ORA-00308: cannot open archived log '/oracle/rman/arch/1_751_886462926.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
7) resetlog 옵션으로 해서 database open;
<source lang=sql>
SQL> alter database open resetlogs;
Database altered.
# 테이블 스페이스 및 tb_test01 테이블 로우 건수 확인
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TTS
6 rows selected.
SQL> select count(*) from tb_test01;
COUNT(*)
----------
80000