| # 요약 | |
| << 시나리오 >> 백업받지않은 테이블스페이스에 데이터 파일 손상 << 시나리오 >> 테이블스페이스의 다중 데이터파일에서 부분 손상 << 정리 >> 사용하지 않는 테이블스페이스 삭제 후 백업파일 정리 << 문제 >> Undo File 삭제 후 복구 << 시나리오 >> 백업셋 물리적 파일 손상 -> 완벽 삭제하기 << 시나리오 >> 테이블스페이스 손상, Time Base Recovery # Backup : Data File Level RMAN> backup datafile '/u01/app/oracle/oradata/ora11g/data02.dbf'; # 논리적 백업정보와 물리적 파일정보 체크하기 RMAN> crosscheck backupset; # expired backupset RMAN> list expired backupset; # expired backupset 삭제 RMAN> delete expired backupset; |
|
| # Backup 확인 | |
| RMAN> report schema; Report of database schema for database with db_unique_name ORA11G List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 750 SYSTEM *** /u01/app/oracle/oradata/ora11g/system01.dbf 2 610 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf 3 14 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf 4 5 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf 5 331 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 20 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf RMAN> list backup; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 10 Full 1.15G DISK 00:00:29 25-JAN-24 BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20240125T095218 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T095218_lv3d6305_.bkp List of Datafiles in backup set 10 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf 2 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 3 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 4 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf 5 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 11 Full 9.36M DISK 00:00:00 25-JAN-24 BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20240125T095254 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159177974_lv3d76co_.bkp SPFILE Included: Modification time: 25-JAN-24 SPFILE db_unique_name: ORA11G Control File Included: Ckp SCN: 1286552 Ckp time: 25-JAN-24 >> DataFile과 ControlFile의 SCN 번호가 다르다 = Full CheckPoint X = Partial Backup (부분 백업) |
|
<< 시나리오 >> 백업받지않은 테이블스페이스에 데이터 파일 손상
| SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change# FROM v$datafile a, v$tablespace b WHERE a.ts# = b.ts#; FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE# ----- -------------------------------------------------- ---------- ------------- ------------------ 1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 1286535 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1286535 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1286535 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1286535 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1286535 create tablespace data_tbs datafile '/u01/app/oracle/oradata/ora11g/data01.dbf' size 5m; >> 용량 작게 생성. SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change# FROM v$datafile a, v$tablespace b WHERE a.ts# = b.ts#; FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE# ----- -------------------------------------------------- ---------- ------------- ------------------ 1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 1286535 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1286535 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1286535 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1286535 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1286535 6 /u01/app/oracle/oradata/ora11g/data01.dbf DATA_TBS ONLINE 1287981 create table hr.dept_tamp tablespace data_tbs as select * from hr.departments; select count(*) from hr.dept_tamp; COUNT(*) ---------- 27 SELECT a.group#,b.sequence#,a.member,b.bytes/1024/1024 mb,b.archived,b.status, b.first_time,b.first_change#,b.next_change# FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ------ --------- -------------------------------------------------- ---------- --------- ------------- FIRST_TIME FIRST_CHANGE# NEXT_CHANGE# ----------- ------------- ------------ 1 16 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE 24-JAN-24 1265237 1265253 2 17 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 24-JAN-24 1265253 1285295 3 18 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT 25-JAN-24 1285295 2.8147E+14 alter system switch logfile; alter system switch logfile; alter system switch logfile; --Thread 1 advanced to log sequence 21 (LGWR switch) -- Current log# 3 seq# 21 mem# 0: /u01/app/oracle/oradata/ora11g/redo03.log SELECT a.group#,b.sequence#,a.member,b.bytes/1024/1024 mb,b.archived,b.status, b.first_time,b.first_change#,b.next_change# FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ------ --------- -------------------------------------------------- ---------- --------- ------------- FIRST_TIME FIRST_CHANGE# NEXT_CHANGE# ----------- ------------- ------------ 1 19 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE 25-JAN-24 1288367 1288370 2 20 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 25-JAN-24 1288370 1288373 3 21 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT 25-JAN-24 1288373 2.8147E+14 SELECT name,sequence#,first_change#,first_time,next_change#,next_time FROM v$archived_log where name is not null order by next_time; NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME -------------------------------------------------- --------- ------------- ----------- ------------ --------- /home/oracle/arch1/arch_1_18_1158511351.arc 18 1285295 25-JAN-24 1288367 25-JAN-24 /home/oracle/arch1/arch_1_19_1158511351.arc 19 1288367 25-JAN-24 1288370 25-JAN-24 /home/oracle/arch1/arch_1_20_1158511351.arc 20 1288370 25-JAN-24 1288373 25-JAN-24 SYS@ora11g> ! ls /home/oracle/arch1 arch_1_18_1158511351.arc arch_1_19_1158511351.arc arch_1_20_1158511351.arc >> RMAN 백업 파일 최근 SCN 기준 - DataFile : 1286535 - ControlFile : 1286552 > seq#18 # 장애 발생 ! rm /u01/app/oracle/oradata/ora11g/data01.dbf SYS@ora11g> shutdown immediate ORA-03113: end-of-file on communication channel Process ID: 7845 Session ID: 10 Serial number: 41 SYS@ora11g> conn / as sysdba Connected to an idle instance. >> alert log --Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_lgwr_7344.trc: --ORA-63999: data file suffered media failure --ORA-01116: error in opening database file 6 --ORA-01110: data file 6: '/u01/app/oracle/oradata/ora11g/data01.dbf' --ORA-27041: unable to open file --Linux Error: 2: No such file or directory --Additional information: 3 --LGWR (ospid: 7344): terminating the instance due to error 63999 --Thu Jan 25 10:27:14 2024 --System state dump requested by (instance=1, osid=7344 (LGWR)), summary=[abnormal instance termination]. --System State dumped to trace file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_diag_7332_20240125102714.trc --Dumping diagnostic data in directory=[cdmp_20240125102714], requested by (instance=1, osid=7344 (LGWR)), summary=[abnormal instance termination]. --Instance terminated by LGWR, pid = 7344 |
|
| # 복구 | |
| SYS@ora11g> ! ls /u01/app/oracle/oradata/ora11g/data01.dbf ls: cannot access /u01/app/oracle/oradata/ora11g/data01.dbf: No such file or directory >> 문제된 테이블스페이스는 백업전 상태. Redo Log File, Archive File >> DB 비정상 종료 시 RMAN에서도 exit 후 재접속 해야한다 RMAN> exit RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows ORACLE error from target database: ORA-03135: connection lost contact Process ID: 8128 Session ID: 183 Serial number: 19 Recovery Manager complete. [oracle@oracle ~]$ rman target / connected to target database (not started) RMAN> startup mount RMAN> list failure; using target database control file instead of recovery catalog List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 45 HIGH OPEN 25-JAN-24 One or more non-system datafiles are missing 22 HIGH OPEN 17-DEC-23 One or more non-system datafiles are offline RMAN> list failure 45 detail; List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 45 HIGH OPEN 25-JAN-24 One or more non-system datafiles are missing Impact: See impact for individual child failures List of child failures for parent failure ID 45 Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 3986 HIGH OPEN 25-JAN-24 Datafile 6: '/u01/app/oracle/oradata/ora11g/data01.dbf' is missing Impact: Some objects in tablespace DATA_TBS might be unavailable RMAN> list backup; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 10 Full 1.15G DISK 00:00:29 25-JAN-24 BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20240125T095218 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T095218_lv3d6305_.bkp List of Datafiles in backup set 10 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf 2 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 3 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 4 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf 5 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 11 Full 9.36M DISK 00:00:00 25-JAN-24 BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20240125T095254 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159177974_lv3d76co_.bkp SPFILE Included: Modification time: 25-JAN-24 SPFILE db_unique_name: ORA11G Control File Included: Ckp SCN: 1286552 Ckp time: 25-JAN-24 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 12 Full 9.36M DISK 00:00:00 25-JAN-24 BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20240125T102008 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159179608_lv3ft8ko_.bkp SPFILE Included: Modification time: 25-JAN-24 SPFILE db_unique_name: ORA11G Control File Included: Ckp SCN: 1288545 Ckp time: 25-JAN-24 > control file 자동 백업 설정 : 컨트롤파일 정보가 바뀔 때 자동 백업 - 스토리지 구조 변경, 테이블스페이스 변경, 리두로그 그룹,멤버변경 등 RMAN> list backup of tablespace data_tbs; specification does not match any backup in the repository RMAN> report schema; Report of database schema for database with db_unique_name ORA11G List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 750 SYSTEM *** /u01/app/oracle/oradata/ora11g/system01.dbf 2 610 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf 3 14 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf 4 5 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf 5 331 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf 6 0 DATA_TBS *** /u01/app/oracle/oradata/ora11g/data01.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 20 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf >> DATA_TBS 손상되어 용량 0으로 나온다 RMAN> sql 'alter database datafile 6 offline'; RMAN> alter database open; SYS@ora11g> conn / as sysdba Connected. SYS@ora11g> select status, instance_name from v$instance; STATUS INSTANCE_NAME ------------- ---------------- OPEN ora11g SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change# FROM v$datafile a, v$tablespace b WHERE a.ts# = b.ts#; FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE# ----- -------------------------------------------------- ---------- ------------- ------------------ 1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 1308918 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1308918 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1308918 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1308918 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1308918 6 /u01/app/oracle/oradata/ora11g/data01.dbf DATA_TBS RECOVER 1288373 RMAN> sql "alter database create datafile ''/u01/app/oracle/oradata/ora11g/data01.dbf\''"; > recover : 컨트롤파일에서 creat 작업시점의 체크포인트의 리두파일 찾아 아카이브파일 현재까지 적용 RMAN> recover tablespace data_tbs; Starting recover at 25-JAN-24 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=17 device type=DISK starting media recovery archived log for thread 1 with sequence 18 is already on disk as file /home/oracle/arch1/arch_1_18_1158511351.arc archived log for thread 1 with sequence 19 is already on disk as file /home/oracle/arch1/arch_1_19_1158511351.arc archived log for thread 1 with sequence 20 is already on disk as file /home/oracle/arch1/arch_1_20_1158511351.arc archived log for thread 1 with sequence 21 is already on disk as file /home/oracle/arch1/arch_1_21_1158511351.arc archived log file name=/home/oracle/arch1/arch_1_18_1158511351.arc thread=1 sequence=18 archived log file name=/home/oracle/arch1/arch_1_19_1158511351.arc thread=1 sequence=19 media recovery complete, elapsed time: 00:00:00 Finished recover at 25-JAN-24 RMAN> sql 'alter database datafile 6 online'; 또는 RMAN> sql 'alter tablespace datafile 6 online'; > 오픈단계에에서는 테이블스페이스 레벨 온 오프 가능 > 마운트 단계에서는 테이블스페이스 레벨로 온 오프 불가, 무조건 데이터베이스 레벨로 사용 SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change# FROM v$datafile a, v$tablespace b WHERE a.ts# = b.ts#; FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE# ----- -------------------------------------------------- ---------- ------------- ------------------ 1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 1308918 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1308918 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1308918 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1308918 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1308918 6 /u01/app/oracle/oradata/ora11g/data01.dbf DATA_TBS ONLINE 1309606 SYS@ora11g> select count(*) from hr.dept_tamp; COUNT(*) ---------- 27 RMAN> report schema; Report of database schema for database with db_unique_name ORA11G List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 750 SYSTEM *** /u01/app/oracle/oradata/ora11g/system01.dbf 2 610 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf 3 14 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf 4 5 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf 5 331 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf 6 5 DATA_TBS *** /u01/app/oracle/oradata/ora11g/data01.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 20 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf RMAN> report need backup; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 Report of files with less than 1 redundant backups File #bkps Name ---- ----- ----------------------------------------------------- 6 0 /u01/app/oracle/oradata/ora11g/data01.dbf RMAN> backup tablespace data_tbs; ... piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T111951_lv3kb72t_.bkp tag=TAG20240125T111951 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 25-JAN-24 Starting Control File and SPFILE Autobackup at 25-JAN-24 piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159183192_lv3kb8cl_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 25-JAN-24 |
|
<< 시나리오 >> 테이블스페이스의 다중 데이터파일 부분 손상
| # 테이블스페이스에 데이터파일 추가 alter tablespace data_tbs add datafile '/u01/app/oracle/oradata/ora11g/data02.dbf' size 10m; create table hr.emp_20240125 tablespace data_tbs as select * from hr.employees; SYS@ora11g> select count(*) from hr.emp_20240125; COUNT(*) ---------- 107 SELECT e.segment_name,f.tablespace_name, f.file_name FROM dba_extents e, dba_data_files f WHERE f.file_id = e.file_id AND e.owner = 'HR' AND e.segment_name = 'EMP_20240125'; SEGMENT_NAME TABLESPACE_NAME FILE_NAME ----------------------- --------------- -------------------------------------------------- EMP_20240125 DATA_TBS /u01/app/oracle/oradata/ora11g/data01.dbf SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change# FROM v$datafile a, v$tablespace b WHERE a.ts# = b.ts#; FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE# ----- -------------------------------------------------- ---------- ------------- ------------------ 1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 1308918 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1308918 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1308918 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1308918 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1308918 6 /u01/app/oracle/oradata/ora11g/data01.dbf DATA_TBS ONLINE 1311308 7 /u01/app/oracle/oradata/ora11g/data02.dbf DATA_TBS ONLINE 1311517 SYS@ora11g> insert into hr.emp_20240125 select * from hr.emp_20240125; ... 반복 데이터 누적 27392 rows created. # 테이블 용량 select bytes/1024/1024 mb from dba_segments where segment_name = 'EMP_20240125' and owner = 'HR'; MB ---------- 5 # 테이블이 어떤 데이터파일에 속해있는가 SELECT f.tablespace_name, f.file_name, count(*) FROM dba_extents e, dba_data_files f WHERE f.file_id = e.file_id AND e.owner = 'HR' AND e.segment_name = 'EMP_20240125' group by f.tablespace_name,f.file_name; TABLESPACE_NAME FILE_NAME COUNT(*) --------------- -------------------------------------------------- ---------- DATA_TBS /u01/app/oracle/oradata/ora11g/data01.dbf 17 DATA_TBS /u01/app/oracle/oradata/ora11g/data02.dbf 3 RMAN> report need backup; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 Report of files with less than 1 redundant backups File #bkps Name ---- ----- ----------------------------------------------------- 7 0 /u01/app/oracle/oradata/ora11g/data02.dbf >> data01.dbf 는 이미 백업완료 > 테이블스페이스 레벨로 받으면 중복(용량중복사용) # Backup : Data File Level RMAN> backup datafile '/u01/app/oracle/oradata/ora11g/data02.dbf'; ... piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T113412_lv3l54ry_.bkp tag=TAG20240125T113412 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 25-JAN-24 RMAN> list backup of tablespace data_tbs; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 14 Full 1.07M DISK 00:00:00 25-JAN-24 BP Key: 14 Status: AVAILABLE Compressed: NO Tag: TAG20240125T111951 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T111951_lv3kb72t_.bkp List of Datafiles in backup set 14 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 6 Full 1311308 25-JAN-24 /u01/app/oracle/oradata/ora11g/data01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 17 Full 2.63M DISK 00:00:01 25-JAN-24 BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20240125T113412 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T113412_lv3l54ry_.bkp List of Datafiles in backup set 17 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 7 Full 1312459 25-JAN-24 /u01/app/oracle/oradata/ora11g/data02.dbf SYS@ora11g> alter system switch logfile; --Thread 1 advanced to log sequence 23 (LGWR switch) -- Current log# 2 seq# 23 mem# 0: /u01/app/oracle/oradata/ora11g/redo02.log create table hr.dept_20240125 tablespace data_tbs as select * from hr.departments; SYS@ora11g> select count(*) from hr.dept_20240125; COUNT(*) ---------- 27 SELECT f.tablespace_name, f.file_name, count(*) FROM dba_extents e, dba_data_files f WHERE f.file_id = e.file_id AND e.owner = 'HR' AND e.segment_name = 'DEPT_20240125' group by f.tablespace_name,f.file_name; TABLESPACE_NAME FILE_NAME COUNT(*) --------------- -------------------------------------------------- ---------- DATA_TBS /u01/app/oracle/oradata/ora11g/data02.dbf 1 alter system switch logfile; SELECT a.group#,b.sequence#,a.member,b.bytes/1024/1024 mb,b.archived,b.status, b.first_time,b.first_change#,b.next_change# FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ------ --------- -------------------------------------------------- ---------- --------- ------------- FIRST_TIME FIRST_CHANGE# NEXT_CHANGE# ----------- ------------- ------------ 1 22 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES ACTIVE 25-JAN-24 1308917 1312809 2 23 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES ACTIVE 25-JAN-24 1312809 1312955 3 24 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT 25-JAN-24 1312955 2.8147E+14 # 장애 발생 ! rm /u01/app/oracle/oradata/ora11g/data02.dbf |
|
| # 복구 | |
| SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change# FROM v$datafile a, v$tablespace b WHERE a.ts# = b.ts#; FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE# ----- -------------------------------------------------- ---------- ------------- ------------------ 1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 1308918 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1308918 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1308918 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1308918 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1308918 6 /u01/app/oracle/oradata/ora11g/data01.dbf DATA_TBS ONLINE 1311308 7 /u01/app/oracle/oradata/ora11g/data02.dbf DATA_TBS ONLINE 1312459 >> Alert Log --Thu Jan 25 11:42:31 2024 --Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ckpt_8978.trc: --ORA-63999: data file suffered media failure --ORA-01116: error in opening database file 7 --ORA-01110: data file 7: '/u01/app/oracle/oradata/ora11g/data02.dbf' --ORA-27041: unable to open file --Linux Error: 2: No such file or directory --Additional information: 3 --CKPT (ospid: 8978): terminating the instance due to error 63999 --Thu Jan 25 11:42:32 2024 --System state dump requested by (instance=1, osid=8978 (CKPT)), summary=[abnormal instance termination]. --System State dumped to trace file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_diag_8964_20240125114232.trc --Dumping diagnostic data in directory=[cdmp_20240125114232], requested by (instance=1, osid=8978 (CKPT)), summary=[abnormal instance termination]. --Instance terminated by CKPT, pid = 8978 select status, instance_name from v$instance; ORA-03135: connection lost contact Process ID: 9144 Session ID: 182 Serial number: 1 SYS@ora11g> conn / as sysdba Connected to an idle instance. >> 비정상 종료 = RMAN 재접속 [oracle@oracle ~]$ rman target / RMAN> startup mount RMAN> list failure; using target database control file instead of recovery catalog List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 22 HIGH OPEN 17-DEC-23 One or more non-system datafiles are offline >>>> 오류가 안나온다? RMAN> report schema; Report of database schema for database with db_unique_name ORA11G List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 750 SYSTEM *** /u01/app/oracle/oradata/ora11g/system01.dbf 2 610 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf 3 14 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf 4 5 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf 5 331 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf 6 5 DATA_TBS *** /u01/app/oracle/oradata/ora11g/data01.dbf 7 0 DATA_TBS *** /u01/app/oracle/oradata/ora11g/data02.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 20 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf >> 7번 파일 용량 0 >> alert log 보고 상황 확인, 복구 진행 > mount 단계 > datafile level로 부분 파일 복구 가능 RMAN> sql 'alter database datafile 7 offline'; RMAN> alter database open; SYS@ora11g> conn / as sysdba Connected. SYS@ora11g> select status, instance_name from v$instance; STATUS INSTANCE_NAME ------------- ---------------- OPEN ora11g SELECT e.segment_name,f.tablespace_name, f.file_name, count(*) FROM dba_extents e, dba_data_files f WHERE f.file_id = e.file_id AND e.owner = 'HR' and f.tablespace_name = 'DATA_TBS' group by e.segment_name,f.tablespace_name,f.file_name; SEGMENT_NAME TABLESPACE_NAME FILE_NAME COUNT(*) ----------------------- --------------- -------------------------------------------------- ---------- EMP_20240125 DATA_TBS /u01/app/oracle/oradata/ora11g/data02.dbf 3 EMP_20240125 DATA_TBS /u01/app/oracle/oradata/ora11g/data01.dbf 17 DEPT_TAMP DATA_TBS /u01/app/oracle/oradata/ora11g/data01.dbf 1 DEPT_20240125 DATA_TBS /u01/app/oracle/oradata/ora11g/data02.dbf 1 > data01.dbf 에만 속한 테이블 확인하기 select count(*) from hr.dept_tamp; COUNT(*) ---------- 27 > data01 + data02 에 속한 테이블 확인하기 select count(*) from hr.emp_20240125; SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change# FROM v$datafile a, v$tablespace b WHERE a.ts# = b.ts#; FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE# ----- -------------------------------------------------- ---------- ------------- ------------------ 1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 1332987 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1332987 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1332987 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1332987 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1332987 6 /u01/app/oracle/oradata/ora11g/data01.dbf DATA_TBS ONLINE 1332987 7 /u01/app/oracle/oradata/ora11g/data02.dbf DATA_TBS RECOVER 1312459 RMAN> restore datafile 7; Starting restore at 25-JAN-24 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=181 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ora11g/data02.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T113412_lv3l54ry_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T113412_lv3l54ry_.bkp tag=TAG20240125T113412 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 25-JAN-24 RMAN> recover datafile 7; Starting recover at 25-JAN-24 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 22 is already on disk as file /home/oracle/arch1/arch_1_22_1158511351.arc archived log for thread 1 with sequence 23 is already on disk as file /home/oracle/arch1/arch_1_23_1158511351.arc archived log for thread 1 with sequence 24 is already on disk as file /home/oracle/arch1/arch_1_24_1158511351.arc archived log file name=/home/oracle/arch1/arch_1_22_1158511351.arc thread=1 sequence=22 media recovery complete, elapsed time: 00:00:00 Finished recover at 25-JAN-24 RMAN> sql 'alter database datafile 7 online'; RMAN> report schema; Report of database schema for database with db_unique_name ORA11G List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 750 SYSTEM *** /u01/app/oracle/oradata/ora11g/system01.dbf 2 610 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf 3 14 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf 4 5 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf 5 331 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf 6 5 DATA_TBS *** /u01/app/oracle/oradata/ora11g/data01.dbf 7 10 DATA_TBS *** /u01/app/oracle/oradata/ora11g/data02.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 20 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change# FROM v$datafile a, v$tablespace b WHERE a.ts# = b.ts#; FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE# ----- -------------------------------------------------- ---------- ------------- ------------------ 1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 1332987 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1332987 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1332987 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1332987 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1332987 6 /u01/app/oracle/oradata/ora11g/data01.dbf DATA_TBS ONLINE 1332987 7 /u01/app/oracle/oradata/ora11g/data02.dbf DATA_TBS ONLINE 1333763 SYS@ora11g> select count(*) from hr.dept_20240125; COUNT(*) ---------- 27 |
|
<< 정리 >> 사용하지 않는 테이블스페이스 삭제 후 백업파일 정리
| # 테이블스페이스 삭제 drop tablespace data_tbs including contents and datafiles; SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change# FROM v$datafile a, v$tablespace b WHERE a.ts# = b.ts#; FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE# ----- -------------------------------------------------- ---------- ------------- ------------------ 1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 1332987 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1332987 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1332987 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1332987 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1332987 RMAN> report obsolete; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 Report of obsolete backups and copies Type Key Completion Time Filename/Handle -------------------- ------ ------------------ -------------------- Backup Set 11 25-JAN-24 Backup Piece 11 25-JAN-24 /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159177974_lv3d76co_.bkp Backup Set 12 25-JAN-24 Backup Piece 12 25-JAN-24 /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159179608_lv3ft8ko_.bkp Backup Set 13 25-JAN-24 Backup Piece 13 25-JAN-24 /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159181873_lv3j122l_.bkp Backup Set 14 25-JAN-24 Backup Piece 14 25-JAN-24 /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T111951_lv3kb72t_.bkp Backup Set 15 25-JAN-24 Backup Piece 15 25-JAN-24 /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159183192_lv3kb8cl_.bkp Backup Set 16 25-JAN-24 Backup Piece 16 25-JAN-24 /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159183980_lv3l2w82_.bkp Backup Set 17 25-JAN-24 Backup Piece 17 25-JAN-24 /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T113412_lv3l54ry_.bkp Backup Set 18 25-JAN-24 Backup Piece 18 25-JAN-24 /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159184053_lv3l5609_.bkp RMAN> list backup; List of Backup Sets =================== ... BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 14 Full 1.07M DISK 00:00:00 25-JAN-24 BP Key: 14 Status: AVAILABLE Compressed: NO Tag: TAG20240125T111951 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T111951_lv3kb72t_.bkp List of Datafiles in backup set 14 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 6 Full 1311308 25-JAN-24 ... BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 17 Full 2.63M DISK 00:00:01 25-JAN-24 BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20240125T113412 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T113412_lv3l54ry_.bkp List of Datafiles in backup set 17 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 7 Full 1312459 25-JAN-24 RMAN> delete obsolete; ...Do you really want to delete the above objects (enter YES or NO)? y RMAN> report obsolete; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 no obsolete backups found RMAN> list backup; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 10 Full 1.15G DISK 00:00:29 25-JAN-24 BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20240125T095218 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T095218_lv3d6305_.bkp List of Datafiles in backup set 10 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf 2 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 3 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 4 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf 5 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 19 Full 9.64M DISK 00:00:00 25-JAN-24 BP Key: 19 Status: AVAILABLE Compressed: NO Tag: TAG20240125T120457 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159185897_lv3mysfc_.bkp SPFILE Included: Modification time: 25-JAN-24 SPFILE db_unique_name: ORA11G Control File Included: Ckp SCN: 1337883 Ckp time: 25-JAN-24 SELECT name,sequence#,first_change#,first_time,next_change#,next_time FROM v$archived_log where name is not null order by next_time; NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME -------------------------------------------------- --------- ------------- ----------- ------------ --------- /home/oracle/arch1/arch_1_18_1158511351.arc 18 1285295 25-JAN-24 1288367 25-JAN-24 /home/oracle/arch1/arch_1_19_1158511351.arc 19 1288367 25-JAN-24 1288370 25-JAN-24 /home/oracle/arch1/arch_1_20_1158511351.arc 20 1288370 25-JAN-24 1288373 25-JAN-24 /home/oracle/arch1/arch_1_21_1158511351.arc 21 1288373 25-JAN-24 1308917 25-JAN-24 /home/oracle/arch1/arch_1_22_1158511351.arc 22 1308917 25-JAN-24 1312809 25-JAN-24 /home/oracle/arch1/arch_1_23_1158511351.arc 23 1312809 25-JAN-24 1312955 25-JAN-24 /home/oracle/arch1/arch_1_24_1158511351.arc 24 1312955 25-JAN-24 1332986 25-JAN-24 SYS@ora11g> ! ls /home/oracle/arch1 arch_1_18_1158511351.arc arch_1_19_1158511351.arc arch_1_20_1158511351.arc arch_1_21_1158511351.arc arch_1_22_1158511351.arc arch_1_23_1158511351.arc arch_1_24_1158511351.arc |
<< 문제 >> Undo File 삭제 후 복구
| << 문제 >> 데이터베이스를 종료한 후 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 삭제, 데이터베이스 시작 시 오류 확인 후 복구 작업 수행하세요. |
|
| shutdown immediate # 장애 유발 ! rm /u01/app/oracle/oradata/ora11g/undotbs01.dbf startup Database mounted. ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' SYS@ora11g> select status, instance_name from v$instance; STATUS INSTANCE_NAME ------------- ---------------- MOUNTED ora11g SYS@ora11g> ! ls /u01/app/oracle/oradata/ora11g/undotbs01.dbf ls: cannot access /u01/app/oracle/oradata/ora11g/undotbs01.dbf: No such file or directory [oracle@oracle ~]$ rman target / RMAN> report schema; Report of database schema for database with db_unique_name ORA11G List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 750 SYSTEM *** /u01/app/oracle/oradata/ora11g/system01.dbf 2 610 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf 3 0 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf 4 5 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf 5 331 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 20 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf RMAN> list failure; List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 45 HIGH OPEN 25-JAN-24 One or more non-system datafiles are missing 22 HIGH OPEN 17-DEC-23 One or more non-system datafiles are offline RMAN> list failure 45 detail; List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 45 HIGH OPEN 25-JAN-24 One or more non-system datafiles are missing Impact: See impact for individual child failures List of child failures for parent failure ID 45 Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 5526 HIGH OPEN 25-JAN-24 Datafile 3: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' is missing Impact: Some objects in tablespace UNDOTBS might be unavailable RMAN> list backup of tablespace UNDOTBS; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 10 Full 1.15G DISK 00:00:29 25-JAN-24 BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20240125T095218 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T095218_lv3d6305_.bkp List of Datafiles in backup set 10 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 3 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf RMAN> restore datafile 3; Starting restore at 25-JAN-24 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ora11g/undotbs01.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T095218_lv3d6305_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T095218_lv3d6305_.bkp tag=TAG20240125T095218 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 25-JAN-24 RMAN> recover datafile 3; Starting recover at 25-JAN-24 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 18 is already on disk as file /home/oracle/arch1/arch_1_18_1158511351.arc archived log for thread 1 with sequence 19 is already on disk as file /home/oracle/arch1/arch_1_19_1158511351.arc archived log for thread 1 with sequence 20 is already on disk as file /home/oracle/arch1/arch_1_20_1158511351.arc archived log for thread 1 with sequence 21 is already on disk as file /home/oracle/arch1/arch_1_21_1158511351.arc archived log for thread 1 with sequence 22 is already on disk as file /home/oracle/arch1/arch_1_22_1158511351.arc archived log for thread 1 with sequence 23 is already on disk as file /home/oracle/arch1/arch_1_23_1158511351.arc archived log for thread 1 with sequence 24 is already on disk as file /home/oracle/arch1/arch_1_24_1158511351.arc archived log file name=/home/oracle/arch1/arch_1_18_1158511351.arc thread=1 sequence=18 archived log file name=/home/oracle/arch1/arch_1_19_1158511351.arc thread=1 sequence=19 archived log file name=/home/oracle/arch1/arch_1_20_1158511351.arc thread=1 sequence=20 archived log file name=/home/oracle/arch1/arch_1_21_1158511351.arc thread=1 sequence=21 archived log file name=/home/oracle/arch1/arch_1_22_1158511351.arc thread=1 sequence=22 media recovery complete, elapsed time: 00:00:01 Finished recover at 25-JAN-24 RMAN> report schema; Report of database schema for database with db_unique_name ORA11G List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 750 SYSTEM *** /u01/app/oracle/oradata/ora11g/system01.dbf 2 610 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf 3 14 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf 4 5 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf 5 331 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 20 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf RMAN> sql 'alter database open'; ---------- update where hr/employess set salaly= 10000 where employee_id = 200; SELECT segment_id, segment_name, owner, tablespace_name, status FROM dba_rollback_segs; SELECT s.username,s.sid,s.serial#,r.name,t.xidusn,t.ubafil,t.ubablk,t.used_ublk FROM v$session s, v$transaction t, v$rollname r WHERE s.taddr = t.addr AND t.xidusn = r.usn; no rows selected rolbak |
|
<< 시나리오 >> 백업셋 물리적 파일 손상 -> 완벽 삭제하기
| RMAN> report schema; Report of database schema for database with db_unique_name ORA11G List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 750 SYSTEM *** /u01/app/oracle/oradata/ora11g/system01.dbf 2 610 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf 3 14 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf 4 5 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf 5 331 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 20 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf RMAN> list backup; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 10 Full 1.15G DISK 00:00:29 25-JAN-24 BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20240125T095218 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T095218_lv3d6305_.bkp List of Datafiles in backup set 10 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf 2 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 3 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 4 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf 5 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 19 Full 9.64M DISK 00:00:00 25-JAN-24 BP Key: 19 Status: AVAILABLE Compressed: NO Tag: TAG20240125T120457 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159185897_lv3mysfc_.bkp SPFILE Included: Modification time: 25-JAN-24 SPFILE db_unique_name: ORA11G Control File Included: Ckp SCN: 1337883 Ckp time: 25-JAN-24 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 20 Full 9.64M DISK 00:00:00 25-JAN-24 BP Key: 20 Status: AVAILABLE Compressed: NO Tag: TAG20240125T121459 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159186499_lv3nkmd3_.bkp SPFILE Included: Modification time: 25-JAN-24 SPFILE db_unique_name: ORA11G Control File Included: Ckp SCN: 1338390 Ckp time: 25-JAN-24 RMAN> host; >> OS로 나가기 [oracle@oracle ~]$ # DataFile 백업셋 삭제 [oracle@oracle ~]$ rm /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T095218_lv3d6305_.bkp RMAN> list backup; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 10 Full 1.15G DISK 00:00:29 25-JAN-24 BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20240125T095218 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T095218_lv3d6305_.bkp List of Datafiles in backup set 10 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf 2 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 3 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 4 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf 5 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 19 Full 9.64M DISK 00:00:00 25-JAN-24 BP Key: 19 Status: AVAILABLE Compressed: NO Tag: TAG20240125T120457 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159185897_lv3mysfc_.bkp SPFILE Included: Modification time: 25-JAN-24 SPFILE db_unique_name: ORA11G Control File Included: Ckp SCN: 1337883 Ckp time: 25-JAN-24 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 20 Full 9.64M DISK 00:00:00 25-JAN-24 BP Key: 20 Status: AVAILABLE Compressed: NO Tag: TAG20240125T121459 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159186499_lv3nkmd3_.bkp SPFILE Included: Modification time: 25-JAN-24 SPFILE db_unique_name: ORA11G Control File Included: Ckp SCN: 1338390 Ckp time: 25-JAN-24 RMAN> delete backupset 10; using channel ORA_DISK_1 List of Backup Pieces BP Key BS Key Pc# Cp# Status Device Type Piece Name ------- ------- --- --- ----------- ----------- ---------- 10 10 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T095218_lv3d6305_.bkp Do you really want to delete the above objects (enter YES or NO)? y RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status RMAN-06210: List of Mismatched objects RMAN-06211: ========================== RMAN-06212: Object Type Filename/Handle RMAN-06213: --------------- --------------------------------------------------- RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T095218_lv3d6305_.bkp > 물리적 파일이 없는 상태에서는 논리적 삭제 불가 > 모두 RMAN 관리영역 > crosscheck # 논리적 백업정보와 물리적 파일정보 체크하기 RMAN> crosscheck backupset; using channel ORA_DISK_1 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T095218_lv3d6305_.bkp RECID=10 STAMP=1159177938 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159185897_lv3mysfc_.bkp RECID=19 STAMP=1159185897 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159186499_lv3nkmd3_.bkp RECID=20 STAMP=1159186499 Crosschecked 3 objects # EXPIRED 상태의 Backupset 목록 확인 RMAN> list expired backupset; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 10 Full 1.15G DISK 00:00:29 25-JAN-24 BP Key: 10 Status: EXPIRED Compressed: NO Tag: TAG20240125T095218 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T095218_lv3d6305_.bkp List of Datafiles in backup set 10 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf 2 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 3 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 4 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf 5 Full 1286535 25-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf # expired backupset 삭제 RMAN> delete expired backupset; using channel ORA_DISK_1 List of Backup Pieces BP Key BS Key Pc# Cp# Status Device Type Piece Name ------- ------- --- --- ----------- ----------- ---------- 10 10 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T095218_lv3d6305_.bkp Do you really want to delete the above objects (enter YES or NO)? y deleted backup piece backup piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T095218_lv3d6305_.bkp RECID=10 STAMP=1159177938 Deleted 1 EXPIRED objects RMAN> list backup; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 19 Full 9.64M DISK 00:00:00 25-JAN-24 BP Key: 19 Status: AVAILABLE Compressed: NO Tag: TAG20240125T120457 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159185897_lv3mysfc_.bkp SPFILE Included: Modification time: 25-JAN-24 SPFILE db_unique_name: ORA11G Control File Included: Ckp SCN: 1337883 Ckp time: 25-JAN-24 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 20 Full 9.64M DISK 00:00:00 25-JAN-24 BP Key: 20 Status: AVAILABLE Compressed: NO Tag: TAG20240125T121459 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159186499_lv3nkmd3_.bkp SPFILE Included: Modification time: 25-JAN-24 SPFILE db_unique_name: ORA11G Control File Included: Ckp SCN: 1338390 Ckp time: 25-JAN-24 RMAN> delete backupset; Deleted 2 objects RMAN> list backup; specification does not match any backup in the repository RMAN> backup database; piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T142843_lv3wdcf4_.bkp tag=TAG20240125T142843 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 Finished backup at 25-JAN-24 Starting Control File and SPFILE Autobackup at 25-JAN-24 piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159194558_lv3wfgtv_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 25-JAN-24 RMAN> list backup; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 21 Full 1.16G DISK 00:00:31 25-JAN-24 BP Key: 21 Status: AVAILABLE Compressed: NO Tag: TAG20240125T142843 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T142843_lv3wdcf4_.bkp List of Datafiles in backup set 21 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 1345609 25-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf 2 Full 1345609 25-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 3 Full 1345609 25-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 4 Full 1345609 25-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf 5 Full 1345609 25-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 22 Full 9.64M DISK 00:00:00 25-JAN-24 BP Key: 22 Status: AVAILABLE Compressed: NO Tag: TAG20240125T142918 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159194558_lv3wfgtv_.bkp SPFILE Included: Modification time: 25-JAN-24 SPFILE db_unique_name: ORA11G Control File Included: Ckp SCN: 1345636 Ckp time: 25-JAN-24 >>> 이전 백업본이 없다면 아카이브도 필요없다 RMAN> report obsolete; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 Report of obsolete backups and copies Type Key Completion Time Filename/Handle -------------------- ------ ------------------ -------------------- Archive Log 49 25-JAN-24 /home/oracle/arch1/arch_1_18_1158511351.arc Archive Log 50 25-JAN-24 /home/oracle/arch1/arch_1_19_1158511351.arc Archive Log 51 25-JAN-24 /home/oracle/arch1/arch_1_20_1158511351.arc Archive Log 52 25-JAN-24 /home/oracle/arch1/arch_1_21_1158511351.arc Archive Log 53 25-JAN-24 /home/oracle/arch1/arch_1_22_1158511351.arc Archive Log 54 25-JAN-24 /home/oracle/arch1/arch_1_23_1158511351.arc Archive Log 55 25-JAN-24 /home/oracle/arch1/arch_1_24_1158511351.arc RMAN> delete obsolete; ... Do you really want to delete the above objects (enter YES or NO)? y ... Deleted 7 objects RMAN> report obsolete; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 no obsolete backups found RMAN> list archivelog all; specification does not match any archived log in the repository SYS@ora11g> ! ls /home/oracle/arch1 no file |
<< 시나리오 >> 테이블스페이스 손상, Time Base Recovery
RMAN> report schema;
Report of database schema for database with db_unique_name ORA11G
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 750 SYSTEM *** /u01/app/oracle/oradata/ora11g/system01.dbf
2 610 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 14 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 5 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf
5 331 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 1.16G DISK 00:00:30 25-JAN-24
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20240125T162231
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T162231_lv431qgg_.bkp
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1169996 25-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1169996 25-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1169996 25-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 Full 1169996 25-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1169996 25-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 9.36M DISK 00:00:00 25-JAN-24
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20240125T162306
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159201386_lv432trt_.bkp
SPFILE Included: Modification time: 25-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1170014 Ckp time: 25-JAN-24
SELECT a.group#,b.sequence#,a.member,b.bytes/1024/1024 mb,b.archived,b.status,
b.first_time,b.first_change#,b.next_change#
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
ORDER BY 1;
GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS
------ --------- -------------------------------------------------- ---------- --------- -------------
FIRST_TIME FIRST_CHANGE# NEXT_CHANGE#
----------- ------------- ------------
1 1 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE
17-JAN-24 1165188 1167000
2 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE
19-JAN-24 1167000 1169263
3 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT
25-JAN-24 1169263 2.8147E+14
ALTER SYSTEM SWITCH LOGFILE;
create tablespace data_tbs datafile '/u01/app/oracle/oradata/ora11g/data_tbs01.dbf' size 10m;
--Thu Jan 25 16:25:06 2024
--create tablespace data_tbs datafile '/u01/app/oracle/oradata/ora11g/data_tbs01.dbf' size 10m
--Completed: create tablespace data_tbs datafile '/u01/app/oracle/oradata/ora11g/data_tbs01.dbf' size 10m
create table hr.data_emp tablespace data_tbs as select * from hr.employees;
select count(*) from hr.data_emp;
COUNT(*)
----------
107
RMAN> report schema;
.Report of database schema for database with db_unique_name ORA11G
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 750 SYSTEM *** /u01/app/oracle/oradata/ora11g/system01.dbf
2 610 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 14 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 5 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf
5 331 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf
6 10 DATA_TBS *** /u01/app/oracle/oradata/ora11g/data_tbs01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf
RMAN> backup database;
...
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T162552_lv4380jm_.bkp tag=TAG20240125T162552 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 25-JAN-24
Starting Control File and SPFILE Autobackup at 25-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159201597_lv439fvt_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-JAN-24
RMAN> list backup;
...
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 1.16G DISK 00:00:36 25-JAN-24
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20240125T162552
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T162552_lv4380jm_.bkp
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
6 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/data_tbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 9.36M DISK 00:00:01 25-JAN-24
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20240125T162637
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159201597_lv439fvt_.bkp
SPFILE Included: Modification time: 25-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1170561 Ckp time: 25-JAN-24
create table hr.data_emp_new tablespace data_tbs as select * from hr.employees;
select count(*) from hr.data_emp_new;
COUNT(*)
----------
107
alter system switch logfile;
--Thread 1 advanced to log sequence 5 (LGWR switch)
-- Current log# 2 seq# 5 mem# 0: /u01/app/oracle/oradata/ora11g/redo02.log
SELECT a.group#,b.sequence#,a.member,b.bytes/1024/1024 mb,b.archived,b.status,
b.first_time,b.first_change#,b.next_change#
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
ORDER BY 1;
GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS
------ --------- -------------------------------------------------- ---------- --------- -------------
FIRST_TIME FIRST_CHANGE# NEXT_CHANGE#
----------- ------------- ------------
1 4 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES ACTIVE
25-JAN-24 1170109 1170744
2 5 /u01/app/oracle/oradata/ora11g/redo02.log 50 NO CURRENT
25-JAN-24 1170744 2.8147E+14
3 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES INACTIVE
25-JAN-24 1169263 1170109
SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change#
FROM v$datafile a, v$tablespace b
WHERE a.ts# = b.ts#;
FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE#
----- -------------------------------------------------- ---------- ------------- ------------------
1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 1170435
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1170435
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1170435
4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1170435
5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1170435
6 /u01/app/oracle/oradata/ora11g/data_tbs01.dbf DATA_TBS ONLINE 1170435
# 작업오류
drop tablespace data_tbs including contents and datafiles;
--Thu Jan 25 16:31:51 2024
--drop tablespace data_tbs including contents and datafiles
--Deleted file /u01/app/oracle/oradata/ora11g/data_tbs01.dbf
--Completed: drop tablespace data_tbs including contents and datafiles
RMAN> list backup;
...
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 1.16G DISK 00:00:36 25-JAN-24
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20240125T162552
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T162552_lv4380jm_.bkp
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
6 Full 1170435 25-JAN-24
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 9.36M DISK 00:00:01 25-JAN-24
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20240125T162637
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159201597_lv439fvt_.bkp
SPFILE Included: Modification time: 25-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1170561 Ckp time: 25-JAN-24
>> 6번 파일명이 지워져있다.
RMAN> shutdown abort
Oracle instance shut down
RMAN> startup nomount
connected to target database (not started)
Oracle instance started
# data_tbs 정보를 가진 컨트롤파일로 restore
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159201597_lv439fvt_.bkp';
Starting restore at 25-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ora11g/control01.ctl
Finished restore at 25-JAN-24
# restore 한 컨트롤파일을 기준으로 mount
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> sql 'alter session set nls_date_format = "yyyy-mm-dd hh24:mi:ss"';
> 문제발생 시간정보 이전으로 타임 백
--Thu Jan 25 16:31:51 2024
# Time Base Recovery, 한줄로는 사용 불가, 작업형(run)으로 만들어 사용하기
RMAN> run {
set until time '2024-01-25 16:31:40';
restore database;
recover database;
}
executing command: SET until clause
Starting restore at 25-JAN-24
Starting implicit crosscheck backup at 25-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 25-JAN-24
Starting implicit crosscheck copy at 25-JAN-24
using channel ORA_DISK_1
Finished implicit crosscheck copy at 25-JAN-24
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_24/o1_mf_s_1159107968_lv17vjom_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159194558_lv3wfgtv_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159197639_lv3zfqxc_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159201597_lv439fvt_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159202764_lv44fxn5_.bkp
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ora11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ora11g/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/ora11g/data_tbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T162552_lv4380jm_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T162552_lv4380jm_.bkp tag=TAG20240125T162552
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 25-JAN-24
Starting recover at 25-JAN-24
using channel ORA_DISK_1
starting media recovery
archived log file name=/home/oracle/arch1/arch_1_4_1158511351.arc thread=1 sequence=4
archived log file name=/home/oracle/arch1/arch_1_5_1158511351.arc thread=1 sequence=5
media recovery complete, elapsed time: 00:00:01
Finished recover at 25-JAN-24
RMAN> alter database open resetlogs;
RMAN> report schema;
Report of database schema for database with db_unique_name ORA11G
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 750 SYSTEM *** /u01/app/oracle/oradata/ora11g/system01.dbf
2 610 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 14 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 5 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf
5 331 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf
6 10 DATA_TBS *** /u01/app/oracle/oradata/ora11g/data_tbs01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf
SYS@ora11g> select count(*) from hr.data_emp;
COUNT(*)
----------
107
SYS@ora11g> select count(*) from hr.data_emp_new;
COUNT(*)
----------
107
RMAN> report obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log 29 25-JAN-24 /home/oracle/arch1/arch_1_3_1158511351.arc
Backup Set 6 25-JAN-24
Backup Piece 6 25-JAN-24 /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159201386_lv432trt_.bkp
RMAN> delete obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log 29 25-JAN-24 /home/oracle/arch1/arch_1_3_1158511351.arc
Backup Set 6 25-JAN-24
Backup Piece 6 25-JAN-24 /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159201386_lv432trt_.bkp
Do you really want to delete the above objects (enter YES or NO)? y
deleted archived log
archived log file name=/home/oracle/arch1/arch_1_3_1158511351.arc RECID=29 STAMP=1159201491
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159201386_lv432trt_.bkp RECID=6 STAMP=1159201386
Deleted 2 objects
RMAN> backup database;
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T174010_lv47mbjx_.bkp tag=TAG20240125T174010 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 25-JAN-24
Starting Control File and SPFILE Autobackup at 25-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159206045_lv47nfxt_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-JAN-24
SYS@ora11g> ALTER SYSTEM SWITCH LOGFILE;
--Thread 1 advanced to log sequence 2 (LGWR switch)
-- Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/ora11g/redo02.log
SELECT name,sequence#,first_change#,first_time,next_change#,next_time
FROM v$archived_log where name is not null order by next_time;
NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
-------------------------------------------------- --------- ------------- ----------- ------------ ---------
/home/oracle/arch1/arch_1_1_1159205920.arc 1 1171221 25-JAN-24 1171990 25-JAN-24
/u01/app/oracle/oradata/ora11g/redo03.log 0 0 0
/u01/app/oracle/oradata/ora11g/redo01.log 1 2282108 25-JAN-24 2.8147E+14
/u01/app/oracle/oradata/ora11g/redo02.log 0 0 0
SYS@ora11g> ! ls /home/oracle/arch1
arch_1_1_1159205920.arc
SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- --------- --------- ------------- ------------- ----------- ------------ ---------
1 1 YES ACTIVE 1171221 25-JAN-24 1171990 25-JAN-24
1 2 NO CURRENT 1171990 25-JAN-24 2.8147E+14
1 0 YES UNUSED 0 0
< test >
user + 권한 + 운영 중
drop
복구
drop table
truncate
commit
----- 오류 해결
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 1.16G DISK 00:00:30 25-JAN-24
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20240125T162231
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T162231_lv431qgg_.bkp
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1169996 25-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1169996 25-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1169996 25-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 Full 1169996 25-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1169996 25-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 9.36M DISK 00:00:00 25-JAN-24
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20240125T162306
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159201386_lv432trt_.bkp
SPFILE Included: Modification time: 25-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1170014 Ckp time: 25-JAN-24
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 1.16G DISK 00:00:36 25-JAN-24
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20240125T162552
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T162552_lv4380jm_.bkp
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
6 Full 1170435 25-JAN-24
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 9.36M DISK 00:00:01 25-JAN-24
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20240125T162637
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159201597_lv439fvt_.bkp
SPFILE Included: Modification time: 25-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1170561 Ckp time: 25-JAN-24
RMAN> shutdown abort
Oracle instance shut down
RMAN> startup nomount
connected to target database (not started)
Oracle instance started
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159201597_lv439fvt_.bkp';
Starting restore at 25-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ora11g/control01.ctl
Finished restore at 25-JAN-24
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> sql 'alter session set nls_date_format = "yyyy-mm-dd hh24:mi:ss"';
RMAN> run {
set until time '2024-01-25 16:31:40';
restore database;
recover database;
}
executing command: SET until clause
Starting restore at 25-JAN-24
Starting implicit crosscheck backup at 25-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 25-JAN-24
Starting implicit crosscheck copy at 25-JAN-24
using channel ORA_DISK_1
Finished implicit crosscheck copy at 25-JAN-24
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T142532_lv17tdlq_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_24/o1_mf_s_1159107968_lv17vjom_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159194558_lv3wfgtv_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159197639_lv3zfqxc_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159201597_lv439fvt_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159202764_lv44fxn5_.bkp
using channel ORA_DISK_1
skipping datafile 1; already restored to file /u01/app/oracle/oradata/ora11g/system01.dbf
skipping datafile 2; already restored to file /u01/app/oracle/oradata/ora11g/sysaux01.dbf
skipping datafile 3; already restored to file /u01/app/oracle/oradata/ora11g/undotbs01.dbf
skipping datafile 4; already restored to file /u01/app/oracle/oradata/ora11g/users01.dbf
skipping datafile 5; already restored to file /u01/app/oracle/oradata/ora11g/example01.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/ora11g/data_tbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T162552_lv4380jm_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T162552_lv4380jm_.bkp tag=TAG20240125T162552
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 25-JAN-24
Starting recover at 25-JAN-24
using channel ORA_DISK_1
starting media recovery
archived log file name=/home/oracle/arch1/arch_1_4_1158511351.arc thread=1 sequence=4
archived log file name=/home/oracle/arch1/arch_1_5_1158511351.arc thread=1 sequence=5
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-JAN-24
>>> 백업을 모두 당일 방금 한건데 시스템 파일 오류가 나온다.
검색해보다가 발견 shutdown 전 백업리스트의 내용이 상이 함.
최신 seq#8이 어제날짜를 하고 있다 > 삭제 > 다시 진행하니 정상 작동
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 9.36M DISK 00:00:00 25-JAN-24
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20240125T162306
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159201386_lv432trt_.bkp
SPFILE Included: Modification time: 25-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1170014 Ckp time: 25-JAN-24
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 1.16G DISK 00:00:36 25-JAN-24
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20240125T162552
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T162552_lv4380jm_.bkp
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
6 Full 1170435 25-JAN-24 /u01/app/oracle/oradata/ora11g/data_tbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 1.17G DISK 00:00:00 24-JAN-24
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20240124T142532
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T142532_lv17tdlq_.bkp
List of Datafiles in backup set 8
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 2282107 24-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 2282107 24-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 2282107 24-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 Full 2282107 24-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 2282107 24-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
RMAN> delete backupset 8;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
8 8 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T142532_lv17tdlq_.bkp
Do you really want to delete the above objects (enter YES or NO)? y
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T142532_lv17tdlq_.bkp RECID=8 STAMP=1159204685
Deleted 1 objects
ChatGPT>
RUN {
SET UNTIL TIME '2024-01-25 16:31:40';
RESTORE DATAFILE 1;
RECOVER DATAFILE 1;
}
ALTER DATABASE OPEN RESETLOGS;
SHUTDOWN ABORT;
STARTUP;
'Oracle 11g > Backup & Recovery' 카테고리의 다른 글
| 75 Backup&Recovery, RMAN (0) | 2024.01.29 |
|---|---|
| 74 Backup&Recovery, RMAN (0) | 2024.01.26 |
| 72 Backup&Recovery, RMAN (0) | 2024.01.24 |
| 71 Backup&Recovery, Export & Import, Data Pump (0) | 2024.01.23 |
| 70 Backup&Recovery, Log Miner, Clone DB, Export & Import (0) | 2024.01.22 |