내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 8.1 (64bit)
DB 환경 : Oracle Database 19.9.0.0
방법 : 오라클 19c rman catalog db 이용 백업 복구
이전 글에서 이어지는 내용
오라클 19c rman catalog db 생성 https://positivemh.tistory.com/693
원본 db = instance_name : oracle19, IP : 192.168.137.50, hostname : oel8
카탈로그 db = instance_name : catdb, IP : 192.168.137.51, hostname : oel8catalog
아카이브 모드 확인(원본 db, 카탈로그 db)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 원본 db SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /ORA19/app/oracle/arch Oldest online log sequence 7 Next log sequence to archive 9 Current log sequence 9 카탈로그 db SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /ORA19/app/oracle/arch Oldest online log sequence 9 Next log sequence to archive 11 Current log sequence 11 |
아카이브모드가 아니라면 아카이브모드 설정 게시글 참조해서 설정 후 진행
오라클 아카이브 모드 설정 및 경로 설정 https://positivemh.tistory.com/147
체크포인트 및 로그스위치, 시간확인(추후 시나리오용 작업)
샘플 데이터 생성(원본 db)
1 2 3 4 5 6 7 8 9 | SQL> create table newimsi as select * from dba_objects; Table created. SQL> select count(*) from newimsi; COUNT(*) ---------- 22960 |
체크포인트 및 로그스위치, 시간확인(추후 시나리오용 작업)
rman으로 카탈로그 db로 접속 후 파라미터 수정(백업 경로)(원본 db)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | $ rman target / catalog catuser/catuser@catdb RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/ORA19/app/oracle/rman/%d_%U_%T.bk'; new RMAN configuration parameters: CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/ORA19/app/oracle/rman/%d_%U_%T.bk'; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/ORA19/app/oracle/rman/db_ctl_%F'; new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/ORA19/app/oracle/rman/db_ctl_%F'; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete |
rman으로 카탈로그 db로 접속 후 전체 백업 실행(원본 db)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | $ rman target / catalog catuser/catuser@catdb RMAN> backup database; Starting backup at 29-JAN-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/ORA19/app/oracle/oradata/ORACLE19/system01.dbf input datafile file number=00002 name=/ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf input datafile file number=00003 name=/ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf input datafile file number=00004 name=/ORA19/app/oracle/oradata/ORACLE19/users01.dbf channel ORA_DISK_1: starting piece 1 at 29-JAN-21 channel ORA_DISK_1: finished piece 1 at 29-JAN-21 piece handle=/ORA19/app/oracle/rman/ORACLE19_06vlrm0e_1_1_20210129.bk tag=TAG20210129T133926 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 29-JAN-21 Starting Control File and SPFILE Autobackup at 29-JAN-21 piece handle=/ORA19/app/oracle/rman/db_ctl_c-3209222764-20210129-03 comment=NONE Finished Control File and SPFILE Autobackup at 29-JAN-21 |
백업 정보 확인(원본 db)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | $ rman target / catalog catuser/catuser@catdb RMAN> list backup; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 162 Full 542.66M DISK 00:00:01 29-JAN-21 BP Key: 163 Status: AVAILABLE Compressed: NO Tag: TAG20210129T133926 Piece Name: /ORA19/app/oracle/rman/ORACLE19_06vlrm0e_1_1_20210129.bk List of Datafiles in backup set 162 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 Full 1038182 29-JAN-21 NO /ORA19/app/oracle/oradata/ORACLE19/system01.dbf 2 Full 1038182 29-JAN-21 NO /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf 3 Full 1038182 29-JAN-21 NO /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf 4 Full 1038182 29-JAN-21 NO /ORA19/app/oracle/oradata/ORACLE19/users01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 183 Full 10.52M DISK 00:00:00 29-JAN-21 BP Key: 188 Status: AVAILABLE Compressed: NO Tag: TAG20210129T133930 Piece Name: /ORA19/app/oracle/rman/db_ctl_c-3209222764-20210129-03 SPFILE Included: Modification time: 29-JAN-21 SPFILE db_unique_name: ORACLE19 Control File Included: Ckp SCN: 1038197 Ckp time: 29-JAN-21 |
정상적으로 백업됨
백업 파일 확인(원본 db, 카탈로그 db)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 원본 db $ cd /ORA19/app/oracle/rman/ $ ls -al total 566472 drwxr-xr-x 2 oracle oinstall 86 Jan 29 13:39 . drwxrwxr-x. 11 oracle oinstall 154 Jan 29 13:32 .. -rw-r----- 1 oracle oinstall 11042816 Jan 29 13:39 db_ctl_c-3209222764-20210129-03 -rw-r----- 1 oracle oinstall 569024512 Jan 29 13:39 ORACLE19_06vlrm0e_1_1_20210129.bk 카탈로그 db $ cd /ORA19/app/oracle/rman/ $ ls -al total 0 drwxr-xr-x 2 oracle oinstall 6 Jan 21 15:38 . drwxrwxr-x. 11 oracle oinstall 136 Jan 21 15:38 .. |
rman 백업 파일은 원본 db 서버에 저장됨
장애 발생
원본 db 컨트롤 파일 확인(원본 db)
1 2 3 4 5 6 | SQL> show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /ORA19/app/oracle/oradata/ORACLE19/control01.ctl, /ORA19/app/oracle/oradata/ORACLE19/control02.ctl |
컨트롤파일 삭제 후 db 강제종료(원본 db)
1 2 3 4 5 6 7 | SQL> !rm -rf /ORA19/app/oracle/oradata/ORACLE19/control0*.ctl SQL> !ls /ORA19/app/oracle/oradata/ORACLE19/con* ls: cannot access '/ORA19/app/oracle/oradata/ORACLE19/con*': No such file or directory SQL> shutdown abort ORACLE instance shut down. |
db 기동 시도(원본 db)
1 2 3 4 5 6 7 8 9 | SQL> startup ORACLE instance started. Total System Global Area 1073737800 bytes Fixed Size 8904776 bytes Variable Size 654311424 bytes Database Buffers 406847488 bytes Redo Buffers 3674112 bytes ORA-00205: error in identifying control file, check alert log for more info |
컨트롤파일이 없어서 nomount 까지만 올라가고 mount 되지 않음
복구
카탈로그 db rman 접속 후 컨트롤파일 복구
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | $ rman target / catalog catuser/catuser@catdb RMAN> restore controlfile; Starting restore at 29-JAN-21 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=422 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: restoring control file channel ORA_DISK_1: reading from backup piece /ORA19/app/oracle/rman/db_ctl_c-3209222764-20210129-03 channel ORA_DISK_1: piece handle=/ORA19/app/oracle/rman/db_ctl_c-3209222764-20210129-03 tag=TAG20210129T133930 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/ORA19/app/oracle/oradata/ORACLE19/control01.ctl output file name=/ORA19/app/oracle/oradata/ORACLE19/control02.ctl Finished restore at 29-JAN-21 |
정상적으로 컨트롤파일이 복구됨
db mount 상태로 변경
1 2 3 | SQL> alter database mount; Database altered. |
db open 시도
1 2 3 4 5 6 7 8 9 10 11 12 13 | SQL> alter database open; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/ORA19/app/oracle/oradata/ORACLE19/system01.dbf' |
alter database open 명령은 컨트롤파일을 백업본을 사용했기 때문에 불가하고
alter database open resetlogs 명령은 컨트롤파일 백업본 사용후 복원(recover)을 하지 않았기 때문에 에러가 발생함
db recover 시도
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SQL> recover database using backup controlfile; ORA-00279: change 1038182 generated at 01/29/2021 13:39:26 needed for thread 1 ORA-00289: suggestion : /ORA19/app/oracle/arch/1_15_1062864404.arc ORA-00280: change 1038182 for thread 1 is in sequence #15 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto <-- [auto 입력] ORA-00308: cannot open archived log '/ORA19/app/oracle/arch/1_15_1062864404.arc' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7 ORA-00308: cannot open archived log '/ORA19/app/oracle/arch/1_15_1062864404.arc' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7 |
1_15_1062864404.arc(sequence #15) 아카이브로그 파일을 찾지못해 복구에 실패함
리두로그 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 | SQL> set lines 200 pages 1000 col member for a70 select l.group#, member, archived, sequence# from v$log l, v$logfile f where f.group# = l.group# order by 1; GROUP# MEMBER ARC SEQUENCE# ---------- ---------------------------------------------------------------------- --- ---------- 1 /ORA19/app/oracle/oradata/ORACLE19/redo01.log YES 13 2 /ORA19/app/oracle/oradata/ORACLE19/redo02.log YES 14 3 /ORA19/app/oracle/oradata/ORACLE19/redo03.log NO 15 |
sequence #15에 해당하는 로그파일은 현재 redo03 임을 확인
다시 db recover 시도
1 2 3 4 5 6 7 8 9 10 | SQL> recover database using backup controlfile; ORA-00279: change 1038182 generated at 01/29/2021 13:39:26 needed for thread 1 ORA-00289: suggestion : /ORA19/app/oracle/arch/1_15_1062864404.arc ORA-00280: change 1038182 for thread 1 is in sequence #15 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /ORA19/app/oracle/oradata/ORACLE19/redo03.log <-- [redo03번 위치 입력] Log applied. Media recovery complete. |
정상적으로 복구됨
db resetlogs로 open 후 샘플 데이터 확인
1 2 3 4 5 6 7 8 9 | SQL> alter database open resetlogs; Database altered. SQL> select count(*) from newimsi; COUNT(*) ---------- 22960 |
정상적으로 확인됨
incarnation 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 | $ export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss" $ rman target / catalog catuser/catuser@catdb RMAN> list incarnation; new incarnation of database registered in recovery catalog starting full resync of recovery catalog full resync complete List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 2 ORACLE19 3209222764 PARENT 988348 2021-01-26 16:06:44 1 207 ORACLE19 3209222764 CURRENT 1038289 2021-01-29 13:45:25 |
참조 :
http://msutic.blogspot.com/2014/07/ora-19909-datafile-1-belongs-to-orphan.html
https://docs.oracle.com/cd/B28359_01/backup.111/b28273/rcmsynta036.htm#RCMRF148
https://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr006.htm
Docs 412113.1
https://jeeomlove.tistory.com/110
https://otsteam.tistory.com/112
https://goodusdata.tistory.com/84
https://cafe.naver.com/prodba/49570
http://egloos.zum.com/ietranger/v/3301271
'ORACLE > Backup&Recover' 카테고리의 다른 글
| 오라클 11g R2 datapump db full 백업 (2) | 2021.01.27 |
|---|---|
| 오라클 19c rman incarnation 설명 및 복구 시나리오 (0) | 2021.01.21 |
| 오라클 11g R2 ADG Gap 발생 시나리오1 (0) | 2021.01.18 |
| 오라클 19c rman catalog 설명 및 catalog db 생성 (0) | 2021.01.13 |
| 오라클 11g R2 hot 백업, 복구(파일 복사 후 샘플데이터 생성) (0) | 2021.01.11 |