다른 명령
rman 이용한 이기종간 데이터 전환
- rman Backup 을 이용한 데이터 마이그레이션
- 이기종 플랫폼 마이그레이션이 가능.
- Convert 옵션을 사용하여 이기종간 datafile을 이관
RMAN 이용 마이그레이션 작업
소스 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(*) ---------- 80000
타겟 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-23
4) 타겟 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.dbf
5) 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