내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 7.2 (64bit)
DB 환경 : Oracle Database 12.2.0.2
방법 : Oracle 12c R2 Clone DB 생성 및 복구 시나리오
기존 DB 명 : orcl
기존 DB 데이터 파일 경로 : /oracle/app/oracle/oradata/orcl/
기존 DB 아카이브 파일 경로 : /oracle/app/oracle/fast_recovery_area/orcl/
Clone DB 명 : clone
Clone DB 데이터 파일 경로 : /oracle/app/oracle/oradata/clone/
Clone DB 아카이브 파일 경로 : /oracle/app/oracle/fast_recovery_area/clone/
시나리오 요약
복사 대상 : datafile, redo file, archive file, pfile(alter system archive log current; 명령 수행)
복사 대상 : datafile, archive file, pfile(alter system archive log current; 명령 수행)
복사 대상 : datafile, redo file, archive file, pfile(alter system archive log current; 명령 미수행)
이렇게 백업을 하고 복구를 시도해봄
시나리오 1.
복사 대상 : datafile, redo file, archive file, pfile(alter system archive log current; 명령 수행)
0. Clone DB 필요한 경로 생성
1 2 3 | $ mkdir -p /oracle/app/oracle/admin/clone/adump/ $ mkdir -p /oracle/app/oracle/fast_recovery_area/clone/ $ mkdir -p /oracle/app/oracle/oradata/orcl/ |
1. 기존 DB 아카이브 모드 확인
1 2 3 4 5 6 7 | SYS@orcl> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /oracle/app/oracle/fast_recovery_area/orcl/ Oldest online log sequence 96 Next log sequence to archive 98 Current log sequence 98 |
2. 컨트롤파일 생성문 백업
1 | SYS@orcl> alter database backup controlfile to trace as '/home/oracle/re.sql'; |
3. 테이블스페이스들 begin backup 진행(쿼리로 간단하게 명령어 만들어서 복사 붙여넣기)
1 2 3 4 5 6 7 8 9 | SYS@orcl> select distinct 'alter tablespace '||tablespace_name||' begin backup;' from dba_data_files 'ALTERTABLESPACE'||TABLESPACE_NAME||'BEGINBACKUP;' ------------------------------------------------------------- alter tablespace MMMTS begin backup; alter tablespace SYSTEM begin backup; alter tablespace SYSAUX begin backup; alter tablespace USERS begin backup; alter tablespace UNDOTBS1 begin backup; |
4. datafile, redo log file 을 clone DB 경로로 복사
1 2 3 4 5 6 7 8 9 10 11 | $ cp -av /app/oracle/oradata/orcl/*.dbf /app/oracle/oradata/clone/ '/oracle/app/oracle/oradata/orcl/mmmts01.dbf' -> '/oracle/app/oracle/oradata/clone/mmmts01.dbf' '/oracle/app/oracle/oradata/orcl/sysaux01.dbf' -> '/oracle/app/oracle/oradata/clone/sysaux01.dbf' '/oracle/app/oracle/oradata/orcl/system01.dbf' -> '/oracle/app/oracle/oradata/clone/system01.dbf' '/oracle/app/oracle/oradata/orcl/temp01.dbf' -> '/oracle/app/oracle/oradata/clone/temp01.dbf' '/oracle/app/oracle/oradata/orcl/undotbs01.dbf' -> '/oracle/app/oracle/oradata/clone/undotbs01.dbf' '/oracle/app/oracle/oradata/orcl/users01.dbf' -> '/oracle/app/oracle/oradata/clone/users01.dbf' $ cp -av /app/oracle/oradata/orcl/*.log /app/oracle/oradata/clone/ '/oracle/app/oracle/oradata/orcl/redo01.log' -> '/oracle/app/oracle/oradata/clone/redo01.log' '/oracle/app/oracle/oradata/orcl/redo02.log' -> '/oracle/app/oracle/oradata/clone/redo02.log' '/oracle/app/oracle/oradata/orcl/redo03.log' -> '/oracle/app/oracle/oradata/clone/redo03.log' |
5. 테이블스페이스들 end backup 진행(쿼리로 간단하게 명령어 만들어서 복사 붙여넣기)
1 2 3 4 5 6 7 8 9 | SYS@orcl> select distinct 'alter tablespace '||tablespace_name||' end backup;' from dba_data_files 'ALTERTABLESPACE'||TABLESPACE_NAME||'ENDBACKUP;' ----------------------------------------------------------- alter tablespace UNDOTBS2 end backup; alter tablespace SYSTEM end backup; alter tablespace SYSAUX end backup; alter tablespace USERS end backup; alter tablespace MMMTS end backup; |
6. 현재 redo log 모두 아카이브로 떨어뜨리기
1 | SYS@orcl> alter system archive log current; |
7. archive log file 을 clone DB 경로로 복사
1 2 | [oracle@oraora ~]$ cp -av /oracle/app/oracle/fast_recovery_area/ORCL/* /oracle/app/oracle/fast_recovery_area/clone '/oracle/app/oracle/fast_recovery_area/ORCL/1_97_979831519.dbf' -> '/oracle/app/oracle/fast_recovery_area/clone/1_97_979831519.dbf' |
8. pfile 복사 및 수정
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | [oracle@oraora ~]$ cd $ORACLE_HOME/dbs [oracle@oraora dbs]$ cp initorcl.ora initclone.ora [oracle@oraora dbs]$ vi initclone.ora db_name='clone' memory_target=1500M processes =300 audit_file_dest='/oracle/app/oracle/admin/clone/adump' audit_trail ='db' db_block_size=8192 db_recovery_file_dest='/oracle/app/oracle/fast_recovery_area' db_recovery_file_dest_size=2G diagnostic_dest='/oracle/app/oracle' dispatchers='(PROTOCOL=TCP) (SERVICE=CLONEXDB)' open_cursors=300 remote_login_passwordfile='EXCLUSIVE' undo_tablespace='UNDOTBS2' control_files = '/oracle/app/oracle/oradata/clone/control01.ctl','/oracle/app/oracle/oradata/clone/control02.ctl' compatible ='12.2.0' remote_login_passwordfile=EXCLUSIVE nls_territory='AMERICA' log_archive_dest_1 = 'LOCATION=/oracle/app/oracle/fast_recovery_area/clone' log_archive_format = %t_%s_%r.dbf |
빨간색으로 표시한 부분 수정
:wq
저장하고 나오기
9. 컨트롤파일 생성문(re.sql) 수정
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | [oracle@oraora dbs]$ cd /home/oracle [oracle@oraora ~]vi re.sql CREATE CONTROLFILE SET DATABASE "clone" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/oracle/app/oracle/oradata/clone/redo01.log' SIZE 200M BLOCKSIZE 512, GROUP 2 '/oracle/app/oracle/oradata/clone/redo02.log' SIZE 200M BLOCKSIZE 512, GROUP 3 '/oracle/app/oracle/oradata/clone/redo03.log' SIZE 200M BLOCKSIZE 512 DATAFILE '/oracle/app/oracle/oradata/clone/system01.dbf', '/oracle/app/oracle/oradata/clone/sysaux01.dbf', '/oracle/app/oracle/oradata/clone/undotbs01.dbf', '/oracle/app/oracle/oradata/clone/mmmts01.dbf', '/oracle/app/oracle/oradata/clone/users01.dbf' CHARACTER SET US7ASCII ; |
=>
다른부분 다 지우고 resetlogs 구문만 남기고 빨간색으로 표시한 부분 수정
reuse => set 으로 변경
database 이름 clone로 변경
datafile, redo 경로 clone로 변경
10. ORACLE_SID를 clone 로 변경 후 sqlplus 접속
1 2 3 4 5 6 7 8 | [oracle@oraora ~]$ export ORACLE_SID=clone [oracle@oraora ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 20 18:26:21 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. |
11. startup nomount 후 re.sql 실행
1 2 3 4 5 6 7 8 9 10 11 12 | SYS@clone> startup nomount ORACLE instance started. Total System Global Area 1577058304 bytes Fixed Size 8621136 bytes Variable Size 1023411120 bytes Database Buffers 536870912 bytes Redo Buffers 8155136 bytes SYS@clone> @/home/oracle/re.sql Control file created. |
db 상태확인
1 2 3 4 5 6 7 | SYS@clone> select status from v$instance; STATUS ------------ MOUNTED 1 row selected. |
컨트롤 파일을 생성하면 mount 상태가 된다.
12. 복구 작업 진행
recover database using backup controlfile until cancel; 명령 실행
1 2 3 4 5 6 7 8 9 10 11 12 | SYS@clone> recover database using backup controlfile until cancel; ORA-00279: change 4642683 generated at 09/20/2018 16:56:19 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/clone/1_100_979831519.dbf ORA-00280: change 4642683 for thread 1 is in sequence #100 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel <- cancel 입력 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01195: online backup of file 1 needs more recovery to be consistent ORA-01110: data file 1: '/oracle/app/oracle/oradata/clone/system01.dbf' ORA-01112: media recovery not started |
바로 cancel을 입력하면 recover 가 되지 않는다.
13. open시도를 해보지만 되지 않음
1 2 3 4 5 6 | SYS@clone> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01195: online backup of file 1 needs more recovery to be consistent ORA-01110: data file 1: '/oracle/app/oracle/oradata/clone/system01.dbf' |
해결 방법
14. 똑같이 recover 명령을 입력한다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SYS@clone> recover database using backup controlfile until cancel; ORA-00279: change 4642683 generated at 09/20/2018 16:56:19 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/clone/1_100_979831519.dbf ORA-00280: change 4642683 for thread 1 is in sequence #100 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 이부분에서 cancel이 아니라 엔터를 입력한다. ORA-00279: change 4642769 generated at 09/20/2018 16:57:08 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/clone/1_101_979831519.dbf ORA-00280: change 4642769 for thread 1 is in sequence #101 ORA-00278: log file '/oracle/app/oracle/fast_recovery_area/clone/1_100_979831519.dbf' no longer needed for this recovery 엔터를 입력하면 Specify log:~ 가 한번 더나온다 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel <- 이때 cancel 입력 Media recovery cancelled. |
Media recovery가 취소 되었다고 나온다.
15. open시도를 한다.
1 2 3 | SYS@clone> alter database open resetlogs; Database altered. |
16. 상태를 확인한다.
1 2 3 4 5 | SYS@clone> select instance_name, status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ clone OPEN |
clone db 복제 성공
시나리오 2.
복사 대상 : datafile, redo file, archive file, pfile(alter system archive log current; 명령 미수행)
0. Clone DB 필요한 경로 생성
1 2 3 | [oracle@oraora ~]$ mkdir -p /oracle/app/oracle/admin/clone/adump/ [oracle@oraora ~]$ mkdir -p /oracle/app/oracle/fast_recovery_area/clone/ [oracle@oraora ~]$ mkdir -p /oracle/app/oracle/oradata/orcl/ |
1. 기존 DB 아카이브 모드 확인
1 2 3 4 5 6 7 | SYS@orcl> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /oracle/app/oracle/fast_recovery_area/orcl/ Oldest online log sequence 99 Next log sequence to archive 101 Current log sequence 101 |
2. 컨트롤파일 생성문 백업
1 | SYS@orcl> alter database backup controlfile to trace as '/home/oracle/re.sql'; |
3. 테이블스페이스들 begin backup 진행(쿼리로 간단하게 명령어 만들어서 복사 붙여넣기)
1 2 3 4 5 6 7 8 9 | SYS@orcl> select distinct 'alter tablespace '||tablespace_name||' begin backup;' from dba_data_files 'ALTERTABLESPACE'||TABLESPACE_NAME||'BEGINBACKUP;' ------------------------------------------------------------- alter tablespace MMMTS begin backup; alter tablespace SYSTEM begin backup; alter tablespace SYSAUX begin backup; alter tablespace USERS begin backup; alter tablespace UNDOTBS1 begin backup; |
4. datafile, redo log file 을 clone DB 경로로 복사
1 2 3 4 5 6 7 8 9 10 11 12 | [oracle@oraora oradata]$ cp -av /app/oracle/oradata/orcl/*.dbf /app/oracle/oradata/clone/ '/oracle/app/oracle/oradata/orcl/mmmts01.dbf' -> '/oracle/app/oracle/oradata/clone/mmmts01.dbf' '/oracle/app/oracle/oradata/orcl/sysaux01.dbf' -> '/oracle/app/oracle/oradata/clone/sysaux01.dbf' '/oracle/app/oracle/oradata/orcl/system01.dbf' -> '/oracle/app/oracle/oradata/clone/system01.dbf' '/oracle/app/oracle/oradata/orcl/temp01.dbf' -> '/oracle/app/oracle/oradata/clone/temp01.dbf' '/oracle/app/oracle/oradata/orcl/undotbs01.dbf' -> '/oracle/app/oracle/oradata/clone/undotbs01.dbf' '/oracle/app/oracle/oradata/orcl/users01.dbf' -> '/oracle/app/oracle/oradata/clone/users01.dbf' [oracle@oraora oradata]$ cp -av /app/oracle/oradata/orcl/*.log /app/oracle/oradata/clone/ '/oracle/app/oracle/oradata/orcl/redo01.log' -> '/oracle/app/oracle/oradata/clone/redo01.log' '/oracle/app/oracle/oradata/orcl/redo02.log' -> '/oracle/app/oracle/oradata/clone/redo02.log' '/oracle/app/oracle/oradata/orcl/redo03.log' -> '/oracle/app/oracle/oradata/clone/redo03.log' |
5. 테이블스페이스들 end backup 진행(쿼리로 간단하게 명령어 만들어서 복사 붙여넣기)
1 2 3 4 5 6 7 8 9 | SYS@orcl> select distinct 'alter tablespace '||tablespace_name||' end backup;' from dba_data_files 'ALTERTABLESPACE'||TABLESPACE_NAME||'ENDBACKUP;' ----------------------------------------------------------- alter tablespace UNDOTBS2 end backup; alter tablespace SYSTEM end backup; alter tablespace SYSAUX end backup; alter tablespace USERS end backup; alter tablespace MMMTS end backup; |
6. archive log file 을 clone DB 경로로 복사
1 2 | [oracle@oraora ~]$ cp -av /oracle/app/oracle/fast_recovery_area/ORCL/* /oracle/app/oracle/fast_recovery_area/clone '/oracle/app/oracle/fast_recovery_area/ORCL/1_97_979831519.dbf' -> '/oracle/app/oracle/fast_recovery_area/clone/1_97_979831519.dbf' |
7. pfile 복사 및 수정
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | [oracle@oraora ~]$ cd $ORACLE_HOME/dbs [oracle@oraora dbs]$ cp initorcl.ora initclone.ora [oracle@oraora dbs]$ vi initclone.ora db_name='clone' memory_target=1500M processes =300 audit_file_dest='/oracle/app/oracle/admin/clone/adump' audit_trail ='db' db_block_size=8192 db_recovery_file_dest='/oracle/app/oracle/fast_recovery_area' db_recovery_file_dest_size=2G diagnostic_dest='/oracle/app/oracle' dispatchers='(PROTOCOL=TCP) (SERVICE=CLONEXDB)' open_cursors=300 remote_login_passwordfile='EXCLUSIVE' undo_tablespace='UNDOTBS2' control_files = '/oracle/app/oracle/oradata/clone/control01.ctl','/oracle/app/oracle/oradata/clone/control02.ctl' compatible ='12.2.0' remote_login_passwordfile=EXCLUSIVE nls_territory='AMERICA' log_archive_dest_1 = 'LOCATION=/oracle/app/oracle/fast_recovery_area/clone' log_archive_format = %t_%s_%r.dbf |
빨간색으로 표시한 부분 수정
:wq
저장하고 나오기
8. 컨트롤파일 생성문(re.sql) 수정
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | [oracle@oraora dbs]$ cd /home/oracle [oracle@oraora ~]vi re.sql CREATE CONTROLFILE SET DATABASE "clone" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/oracle/app/oracle/oradata/clone/redo01.log' SIZE 200M BLOCKSIZE 512, GROUP 2 '/oracle/app/oracle/oradata/clone/redo02.log' SIZE 200M BLOCKSIZE 512, GROUP 3 '/oracle/app/oracle/oradata/clone/redo03.log' SIZE 200M BLOCKSIZE 512 DATAFILE '/oracle/app/oracle/oradata/clone/system01.dbf', '/oracle/app/oracle/oradata/clone/sysaux01.dbf', '/oracle/app/oracle/oradata/clone/undotbs01.dbf', '/oracle/app/oracle/oradata/clone/mmmts01.dbf', '/oracle/app/oracle/oradata/clone/users01.dbf' CHARACTER SET US7ASCII ; |
=>
다른부분 다 지우고 resetlogs 구문만 남기고 빨간색으로 표시한 부분 수정
reuse => set 으로 변경
database 이름 clone로 변경
datafile, redo 경로 clone로 변경
9. ORACLE_SID를 clone 로 변경 후 sqlplus 접속
1 2 3 4 5 6 7 8 | [oracle@oraora ~]$ export ORACLE_SID=clone [oracle@oraora ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 20 17:19:49 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. |
10. startup nomount 후 re.sql 실행
1 2 3 4 5 6 7 8 9 10 11 12 | SYS@clone> startup nomount ORACLE instance started. Total System Global Area 1577058304 bytes Fixed Size 8621136 bytes Variable Size 1023411120 bytes Database Buffers 536870912 bytes Redo Buffers 8155136 bytes SYS@clone> @/home/oracle/re.sql Control file created. |
db 상태확인
1 2 3 4 5 6 7 | SYS@clone> select status from v$instance; STATUS ------------ MOUNTED 1 row selected. |
컨트롤 파일을 생성하면 mount 상태가 된다.
11. 복구 작업 진행
recover database using backup controlfile until cancel; 명령 실행
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SYS@clone> recover database using backup controlfile until cancel; ORA-00279: change 4643842 generated at 09/20/2018 17:18:50 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/clone/1_101_979831519.dbf ORA-00280: change 4643842 for thread 1 is in sequence #101 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel <- cancel 입력 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01195: online backup of file 1 needs more recovery to be consistent ORA-01110: data file 1: '/oracle/app/oracle/oradata/clone/system01.dbf' ORA-01112: media recovery not started |
바로 cancel을 입력하면 recover 가 되지 않는다.
recover database using backup controlfile until cancel; 명령 재실행
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SYS@clone> recover database using backup controlfile until cancel; ORA-00279: change 4643842 generated at 09/20/2018 17:18:50 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/clone/1_101_979831519.dbf ORA-00280: change 4643842 for thread 1 is in sequence #101 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 엔터를 입력 해본다. ORA-00308: cannot open archived log '/oracle/app/oracle/fast_recovery_area/clone/1_101_979831519.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01195: online backup of file 1 needs more recovery to be consistent ORA-01110: data file 1: '/oracle/app/oracle/oradata/clone/system01.dbf' |
시나리오 1처럼 엔터를 입력해도 진행이 되지 않는다.
해결방법
12. recover 명령어 재실행
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SYS@clone> recover database using backup controlfile until cancel; ORA-00279: change 4643842 generated at 09/20/2018 17:18:50 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/clone/1_101_979831519.dbf ORA-00280: change 4643842 for thread 1 is in sequence #101 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /oracle/app/oracle/oradata/clone/redo01.log <- redo01.log 를 입력해준다.(1번부터 순서대로) ORA-00310: archived log contains sequence 100; sequence 101 required ORA-00334: archived log: '/oracle/app/oracle/oradata/clone/redo01.log' ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01195: online backup of file 1 needs more recovery to be consistent ORA-01110: data file 1: '/oracle/app/oracle/oradata/clone/system01.dbf' |
redo01.log 를 적용 시켜보았지만 recovery가 되지 않는다.
13. redo 를 적용
recover database using backup controlfile until cancel; 명령 재실행
1 2 3 4 5 6 7 8 9 10 | SYS@clone> recover database using backup controlfile until cancel; ORA-00279: change 4643842 generated at 09/20/2018 17:18:50 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/clone/1_101_979831519.dbf ORA-00280: change 4643842 for thread 1 is in sequence #101 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /oracle/app/oracle/oradata/clone/redo02.log <- redo02.log 를 입력해준다. Log applied. Media recovery complete. |
media recovery 가 성공했다고 나온다.
14. open시도를 한다.
1 2 3 | SYS@clone> alter database open resetlogs; Database altered. |
15. 상태를 확인한다.
1 2 3 4 5 | SYS@clone> select instance_name, status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ clone OPEN |
clone db 복제 성공
시나리오 3.
복사 대상 : datafile, archive file, pfile(alter system archive log current; 명령 수행)
0. Clone DB 필요한 경로 생성
1 2 3 | [oracle@oraora ~]$ mkdir -p /oracle/app/oracle/admin/clone/adump/ [oracle@oraora ~]$ mkdir -p /oracle/app/oracle/fast_recovery_area/clone/ [oracle@oraora ~]$ mkdir -p /oracle/app/oracle/oradata/orcl/ |
1. 기존 DB 아카이브 모드 확인
1 2 3 4 5 6 7 | SYS@orcl> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /oracle/app/oracle/fast_recovery_area/orcl/ Oldest online log sequence 100 Next log sequence to archive 102 Current log sequence 102 |
2. 컨트롤파일 생성문 백업
1 | SYS@orcl> alter database backup controlfile to trace as '/home/oracle/re.sql'; |
3. 테이블스페이스들 begin backup 진행(쿼리로 간단하게 명령어 만들어서 복사 붙여넣기)
1 2 3 4 5 6 7 8 9 | SYS@orcl> select distinct 'alter tablespace '||tablespace_name||' begin backup;' from dba_data_files 'ALTERTABLESPACE'||TABLESPACE_NAME||'BEGINBACKUP;' ------------------------------------------------------------- alter tablespace MMMTS begin backup; alter tablespace SYSTEM begin backup; alter tablespace SYSAUX begin backup; alter tablespace USERS begin backup; alter tablespace UNDOTBS1 begin backup; |
4. datafile 을 clone DB 경로로 복사
1 2 3 4 5 6 7 | [oracle@oraora oradata]$ cp -av /app/oracle/oradata/orcl/*.dbf /app/oracle/oradata/clone/ '/oracle/app/oracle/oradata/orcl/mmmts01.dbf' -> '/oracle/app/oracle/oradata/clone/mmmts01.dbf' '/oracle/app/oracle/oradata/orcl/sysaux01.dbf' -> '/oracle/app/oracle/oradata/clone/sysaux01.dbf' '/oracle/app/oracle/oradata/orcl/system01.dbf' -> '/oracle/app/oracle/oradata/clone/system01.dbf' '/oracle/app/oracle/oradata/orcl/temp01.dbf' -> '/oracle/app/oracle/oradata/clone/temp01.dbf' '/oracle/app/oracle/oradata/orcl/undotbs01.dbf' -> '/oracle/app/oracle/oradata/clone/undotbs01.dbf' '/oracle/app/oracle/oradata/orcl/users01.dbf' -> '/oracle/app/oracle/oradata/clone/users01.dbf' |
5. 테이블스페이스들 end backup 진행(쿼리로 간단하게 명령어 만들어서 복사 붙여넣기)
1 2 3 4 5 6 7 8 9 | SYS@orcl> select distinct 'alter tablespace '||tablespace_name||' end backup;' from dba_data_files 'ALTERTABLESPACE'||TABLESPACE_NAME||'ENDBACKUP;' ----------------------------------------------------------- alter tablespace UNDOTBS2 end backup; alter tablespace SYSTEM end backup; alter tablespace SYSAUX end backup; alter tablespace USERS end backup; alter tablespace MMMTS end backup; |
6. 현재 redo log 모두 아카이브로 떨어뜨리기
1 | SYS@orcl> alter system archive log current; |
7. archive log file 을 clone DB 경로로 복사
1 2 | [oracle@oraora ~]$ cp -av /oracle/app/oracle/fast_recovery_area/ORCL/* /oracle/app/oracle/fast_recovery_area/clone '/oracle/app/oracle/fast_recovery_area/ORCL/1_101_979831519.dbf' -> '/oracle/app/oracle/fast_recovery_area/clone/1_101_979831519.dbf' |
8. pfile 복사 및 수정
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | [oracle@oraora ~]$ cd $ORACLE_HOME/dbs [oracle@oraora dbs]$ cp initorcl.ora initclone.ora [oracle@oraora dbs]$ vi initclone.ora db_name='clone' memory_target=1500M processes =300 audit_file_dest='/oracle/app/oracle/admin/clone/adump' audit_trail ='db' db_block_size=8192 db_recovery_file_dest='/oracle/app/oracle/fast_recovery_area' db_recovery_file_dest_size=2G diagnostic_dest='/oracle/app/oracle' dispatchers='(PROTOCOL=TCP) (SERVICE=CLONEXDB)' open_cursors=300 remote_login_passwordfile='EXCLUSIVE' undo_tablespace='UNDOTBS2' control_files = '/oracle/app/oracle/oradata/clone/control01.ctl','/oracle/app/oracle/oradata/clone/control02.ctl' compatible ='12.2.0' remote_login_passwordfile=EXCLUSIVE nls_territory='AMERICA' log_archive_dest_1 = 'LOCATION=/oracle/app/oracle/fast_recovery_area/clone' log_archive_format = %t_%s_%r.dbf |
빨간색으로 표시한 부분 수정
:wq
저장하고 나오기
9. 컨트롤파일 생성문(re.sql) 수정
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | [oracle@oraora dbs]$ cd /home/oracle [oracle@oraora ~]vi re.sql CREATE CONTROLFILE SET DATABASE "clone" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/oracle/app/oracle/oradata/clone/redo01.log' SIZE 200M BLOCKSIZE 512, GROUP 2 '/oracle/app/oracle/oradata/clone/redo02.log' SIZE 200M BLOCKSIZE 512, GROUP 3 '/oracle/app/oracle/oradata/clone/redo03.log' SIZE 200M BLOCKSIZE 512 DATAFILE '/oracle/app/oracle/oradata/clone/system01.dbf', '/oracle/app/oracle/oradata/clone/sysaux01.dbf', '/oracle/app/oracle/oradata/clone/undotbs01.dbf', '/oracle/app/oracle/oradata/clone/mmmts01.dbf', '/oracle/app/oracle/oradata/clone/users01.dbf' CHARACTER SET US7ASCII ; |
=>
다른부분 다 지우고 resetlogs 구문만 남기고 빨간색으로 표시한 부분 수정
reuse => set 으로 변경
database 이름 clone로 변경
datafile, redo 경로 clone로 변경
10. ORACLE_SID를 clone 로 변경 후 sqlplus 접속
1 2 3 4 5 6 7 8 | [oracle@oraora ~]$ export ORACLE_SID=clone [oracle@oraora ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 20 17:34:16 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. |
11. startup nomount 후 re.sql 실행
1 2 3 4 5 6 7 8 9 10 11 12 | SYS@clone> startup nomount ORACLE instance started. Total System Global Area 1577058304 bytes Fixed Size 8621136 bytes Variable Size 1023411120 bytes Database Buffers 536870912 bytes Redo Buffers 8155136 bytes SYS@clone> @/home/oracle/re.sql Control file created. |
db 상태확인
1 2 3 4 5 6 7 | SYS@clone> select status from v$instance; STATUS ------------ MOUNTED 1 row selected. |
컨트롤 파일을 생성하면 mount 상태가 된다.
12. 복구 작업 진행
recover database using backup controlfile until cancel; 명령 실행
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SYS@clone> recover database using backup controlfile until cancel; ORA-00279: change 4644541 generated at 09/20/2018 17:32:13 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/clone/1_101_979831519.dbf ORA-00280: change 4644541 for thread 1 is in sequence #101 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel <- cancel 입력 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01195: online backup of file 1 needs more recovery to be consistent ORA-01110: data file 1: '/oracle/app/oracle/oradata/clone/system01.dbf' ORA-01112: media recovery not started |
바로 cancel을 입력하면 recover 가 되지 않는다.
13. open시도를 해보지만 되지 않음
1 2 3 4 5 6 | SYS@clone> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01195: online backup of file 1 needs more recovery to be consistent ORA-01110: data file 1: '/oracle/app/oracle/oradata/clone/system01.dbf' |
해결 방법
14. 똑같이 recover 명령을 입력한다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SYS@clone> recover database using backup controlfile until cancel; ORA-00279: change 4644541 generated at 09/20/2018 17:32:13 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/clone/1_101_979831519.dbf ORA-00280: change 4644541 for thread 1 is in sequence #101 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 이부분에서 cancel이 아니라 엔터를 입력한다. ORA-00279: change 4644620 generated at 09/20/2018 17:32:51 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/clone/1_102_979831519.dbf ORA-00280: change 4644620 for thread 1 is in sequence #102 ORA-00278: log file '/oracle/app/oracle/fast_recovery_area/clone/1_101_979831519.dbf' no longer needed for this recovery 엔터를 입력하면 Specify log:~ 가 한번 더나온다 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel <- 이때 cancel 입력 Media recovery cancelled. |
Media recovery가 취소 되었다고 나온다.
15. open시도를 한다.
1 2 3 | SYS@clone> alter database open resetlogs; Database altered. |
16. 상태를 확인한다.
1 2 3 4 5 | SYS@clone> select instance_name, status from v$instance; INSTANCE_NAME STATUS --------------- ------------ clone OPEN |
clone db 복제 성공
+
이스크립트는 작업 빠르게 반복하기 위해서 사용한 스크립트
clonetest_sh.txt내용
참조 : http://goalker.tistory.com/110
'ORACLE > Backup&Recover' 카테고리의 다른 글
| hot backup 복구 시나리오2 (0) | 2018.12.19 |
|---|---|
| hot backup 복구 시나리오1 (0) | 2018.12.17 |
| 최악의 복구 시나리오에 대한 해결방법(SCN이 모두 틀림) (0) | 2018.09.20 |
| Oracle Block change tracking 기능 (0) | 2018.05.24 |
| RMAN CrossCheck 명령 (0) | 2018.02.21 |