복사되었습니다!
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 복제 성공
+
이스크립트는 작업 빠르게 반복하기 위해서 사용한 스크립트
내용
더보기
#cat clonetest.sh
rm -rfv /oracle/app/oracle/oradata/clone/*
rm -rfv /oracle/app/oracle/fast_recovery_area/clone/*
rm -rfv /oracle/app/oracle/fast_recovery_area/ORCL/*
export ORACLE_SID=orcl
sqlplus / as sysdba << EOF1
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;
EOF1
cp -av /oracle/app/oracle/oradata/ORCL/*.dbf /oracle/app/oracle/oradata/clone/
cp -av /oracle/app/oracle/oradata/ORCL/*.log /oracle/app/oracle/oradata/clone/
sqlplus / as sysdba << EOF2
alter tablespace UNDOTBS1 end backup;
alter tablespace SYSTEM end backup;
alter tablespace SYSAUX end backup;
alter tablespace MMMTS end backup;
alter tablespace USERS end backup;
alter system archive log current;
EOF2
cp -av /oracle/app/oracle/fast_recovery_area/ORCL/* /oracle/app/oracle/fast_recovery_area/clone
mkdir -p /oracle/app/oracle/admin/clone/adump
export ORACLE_SID=clone
sqlplus / as sysdba << EOF3
shutdown abort
startup nomount
@/home/oracle/re.sql
select status from v\$instance;
EOF3
echo "input : export ORACLE_SID=clone"
echo "scenario1 : recover database using backup controlfile until cancel;"
참조 : http://goalker.tistory.com/110
'ORACLE > Backup&Recover' 카테고리의 다른 글
| hot backup 복구 시나리오2 (0) | 2018.12.19 |
|---|---|
| hot backup 복구 시나리오1 (0) | 2018.12.17 |
| 최악의 복구 시나리오에 대한 해결방법(SCN이 모두 틀림) (2) | 2018.09.20 |
| Oracle Block change tracking 기능 (4) | 2018.05.24 |
| RMAN CrossCheck 명령 (0) | 2018.02.21 |