| << 시나리오 12-3 >> 불안전한 복구 이후 example OFFLINE 상태 // 오류 시점 로그 자료 누락 << Online Backup >> << 시나리오 12 >> 다시 정리 / 특정 데이터 파일과 아카이브 파일이 손상되었을 경우 불안전 복구를 수행 # 일관성 있는 백업 수행 : close = offline = cold backup # 일관성 없는 백업 수행 : open = online = hot backup # Archive Dest 단일화 << 시나리오 13 >> Archive 생성된 후 Inactive Log File이 삭제된 후 DB Close 상태 << 시나리오 14 >> DB OPEN 상태에서 current 그룹이 아닌 리두로그 파일이 삭제 << 시나리오 15 >> current 그룹이 삭제된 후 DB 정상적인 종료 << 시나리오 16 >> current 그룹이 삭제된 후 DB 비정상적인 종료 |
|
| << 시나리오 12-3 >> 불안전한 복구 이후 example OFFLINE 상태 // 오류 시점 로그 자료 누락 | |
| # Recovery : 고장 전 seq#까지 복구한다 SQL> RECOVER DATABASE UNTIL CANCEL; # DB Open - seq# 1부터 시작 SQL> ALTER DATABASE OPEN RESETLOGS; 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# ---------- -------------------------------------------------- ---------- --------------- ------------------ 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE OFFLINE 1162976 SQL> ALTER TABLESPACE example ONLINE; >> Online으료 변경이 안된다 -> 오류 발생 # DB 비정상 종료 SQL> SHUTDOWN ABORT # Restore - cold Backup File - All SQL> ! cp -av /cold_Backup_File/*.* /u01/app/oracle/oradata/ora11g SQL> STARTUP 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#; SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time FROM v$log; SELECT name,sequence#,first_change#,first_time,next_change#,next_time FROM v$archived_log; SQL> ! ls -R /home/oracle/arch* >> 실제 생성된 Archive Log File도 같이 체크 필수 >> 백업 시점부터의 아카이브 파일이 필요하다. >> 구백업본으로 복구하는게 아닌 이상 과거의 아카이브는 필요없다. 운영 정책에 따라 >> 정상 복구되었다면 항상 백업을 하자. |
|
| << Online Backup >> hot_20240117 seq#3 | |
| SELECT a.file#, a.name, a.checkpoint_change#, b.status, b.change#, time FROM v$datafile a, v$backup b WHERE a.file# = b.file#; FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME ---------- -------------------------------------------- ------------------ ------------- ---------- --------- 1 /u01/app/oracle/oradata/ora11g/system01.dbf 1162976 NOT ACTIVE 1162853 16-JAN-24 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1162976 NOT ACTIVE 1162853 16-JAN-24 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 1162976 NOT ACTIVE 1162853 16-JAN-24 4 /u01/app/oracle/oradata/ora11g/users01.dbf 1162976 NOT ACTIVE 1162853 16-JAN-24 5 /u01/app/oracle/oradata/ora11g/example01.dbf 1162976 NOT ACTIVE 1162853 16-JAN-24 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 INACTIVE 1155932 16-JAN-24 1159428 16-JAN-24 1 2 YES INACTIVE 1159428 16-JAN-24 1162976 16-JAN-24 1 3 NO CURRENT 1162976 16-JAN-24 2.8147E+14 SQL> ALTER SYSTEM CHECKPOINT; SQL> ALTER DATABASE BEGIN BACKUP; SQL> ! mkdir -p /home/oracle/backup/arch/hot_20240117 SQL> ! cp -av /u01/app/oracle/oradata/ora11g/*.dbf /home/oracle/backup/arch/hot_20240117/ SQL> ! ls /home/oracle/backup/arch/hot_20240117/ example01.dbf sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf SQL> ALTER DATABASE END BACKUP; SELECT a.file#, a.name, a.checkpoint_change#, b.status, b.change#, time FROM v$datafile a, v$backup b WHERE a.file# = b.file#; FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME ---------- -------------------------------------------- ------------------ ------------- ---------- --------- 1 /u01/app/oracle/oradata/ora11g/system01.dbf 1170811 NOT ACTIVE 1170811 16-JAN-24 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1170811 NOT ACTIVE 1170811 16-JAN-24 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 1170811 NOT ACTIVE 1170811 16-JAN-24 4 /u01/app/oracle/oradata/ora11g/users01.dbf 1170811 NOT ACTIVE 1170811 16-JAN-24 5 /u01/app/oracle/oradata/ora11g/example01.dbf 1170811 NOT ACTIVE 1170811 16-JAN-24 SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/home/oracle/backup/arch/hot_20240117/control01.ctl'; SQL> ! ls /home/oracle/backup/arch/hot_20240117/ control01.ctl example01.dbf sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf 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 INACTIVE 1155932 16-JAN-24 1159428 16-JAN-24 1 2 YES INACTIVE 1159428 16-JAN-24 1162976 16-JAN-24 1 3 NO CURRENT 1162976 16-JAN-24 2.8147E+14 # Online Backup 후 Log Switch 발생하여 ArchiveLog 생성 SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; --Thread 1 advanced to log sequence 4 (LGWR switch) -- Current log# 1 seq# 4 mem# 0: /u01/app/oracle/oradata/ora11g/redo01.log --Archived Log entry 17 added for thread 1 sequence 3 ID 0xf78e75c dest 1: --Archived Log entry 18 added for thread 1 sequence 3 ID 0xf78e75c dest 2: 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 1170870 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1170870 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1170870 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1170870 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1170870 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 4 NO CURRENT 1170870 16-JAN-24 2.8147E+14 1 2 YES INACTIVE 1159428 16-JAN-24 1162976 16-JAN-24 1 3 YES ACTIVE 1162976 16-JAN-24 1170870 16-JAN-24 SELECT name,sequence#,first_change#,first_time,next_change#,next_time FROM v$archived_log; NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ----------------------------------------------- ---------- ------------- ----------- ------------ --------- ...49~54 RESETLOGS_ID : 1154915502 기존 내역은 나옴 /home/oracle/arch1/arch_1_1_1158372877.arc 1 1155932 16-JAN-24 1159428 16-JAN-24 /home/oracle/arch2/arch_1_1_1158372877.arc 1 1155932 16-JAN-24 1159428 16-JAN-24 /home/oracle/arch1/arch_1_2_1158372877.arc 2 1159428 16-JAN-24 1162976 16-JAN-24 /home/oracle/arch2/arch_1_2_1158372877.arc 2 1159428 16-JAN-24 1162976 16-JAN-24 /home/oracle/arch1/arch_1_3_1158372877.arc 3 1162976 16-JAN-24 1170870 16-JAN-24 /home/oracle/arch2/arch_1_3_1158372877.arc 3 1162976 16-JAN-24 1170870 16-JAN-24 # 실제 파일 확인 SQL> ! ls -R /home/oracle/arch* /home/oracle/arch1: arch_1_1_1158372877.arc arch_1_2_1158372877.arc arch_1_3_1158372877.arc /home/oracle/arch2: arch_1_1_1158372877.arc arch_1_2_1158372877.arc arch_1_3_1158372877.arc >> 운영정책 상 필요없는 아카이브 파일 삭제 |
|
| << 시나리오 12 >> 다시 정리 / 특정 데이터 파일과 아카이브 파일이 손상되었을 경우 불안전 복구를 수행 | |
| SQL> ALTER SYSTEM SWITCH LOGFILE; 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 4 YES ACTIVE 1170870 16-JAN-24 1171663 16-JAN-24 1 5 NO CURRENT 1171663 16-JAN-24 2.8147E+14 1 3 YES INACTIVE 1162976 16-JAN-24 1170870 16-JAN-24 SQL> CREATE TABLE hr.new_202401 AS SELECT * FROM hr.employees; SQL> SELECT COUNT(*) FROM hr.new_202401; COUNT(*) ---------- 107 SQL> ALTER SYSTEM SWITCH LOGFILE; 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 4 YES INACTIVE 1170870 16-JAN-24 1171663 16-JAN-24 1 5 YES ACTIVE 1171663 16-JAN-24 1172330 16-JAN-24 1 6 NO CURRENT 1172330 16-JAN-24 2.8147E+14 SQL> ! ls -R /home/oracle/arch* /home/oracle/arch1: arch_1_1_1158372877.arc arch_1_2_1158372877.arc arch_1_3_1158372877.arc arch_1_4_1158372877.arc arch_1_5_1158372877.arc /home/oracle/arch2: arch_1_1_1158372877.arc arch_1_2_1158372877.arc arch_1_3_1158372877.arc arch_1_4_1158372877.arc arch_1_5_1158372877.arc SELECT f.tablespace_name, f.file_name FROM dba_extents e, dba_data_files f WHERE f.file_id = e.file_id AND e.segment_name = 'NEW_202401' AND e.owner = 'HR'; TABLESPACE_NAME FILE_NAME ------------------------------ -------------------------------------------------- USERS /u01/app/oracle/oradata/ora11g/users01.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 1171663 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1171663 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1171663 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1171663 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1171663 # 장애발생 SQL> ! rm /home/oracle/arch1/*.* SQL> ! rm /home/oracle/arch2/*.* SQL> ! rm /u01/app/oracle/oradata/ora11g/users01.dbf SQL> SHUTDOWN IMMEDIATE SQL> STARTUP Database mounted. ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' SQL> SELECT * FROM v$recover_file; FILE# ONLINE ONLINE_STATUS ERROR CHANGE# TIME ---------- ------- ------------- ------------------------------------------------------- ---------- --------- 4 ONLINE ONLINE FILE NOT FOUND 0 SQL> ALTER DATABASE DATAFILE 4 OFFLINE; SQL> SELECT * FROM v$recover_file; FILE# ONLINE ONLINE_STATUS ERROR CHANGE# TIME ---------- ------- ------------- ------------------------------------------------------- ---------- --------- 4 OFFLINE OFFLINE FILE NOT FOUND 0 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 1172570 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1172570 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS OFFLINE 1172570 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1172570 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1172570 SQL> ALTER DATABASE OPEN; # 다른 테이블스페이스는 정상 SELECT COUNT(*) FROM hr.employees; COUNT(*) ---------- 107 # Restore SQL> ! cp -av /home/oracle/backup/arch/hot_20240117/users01.dbf /u01/app/oracle/oradata/ora11g/ # Recover SQL> RECOVER TABLESPACE USERS; ORA-00279: change 1170811 generated at 01/16/2024 20:05:11 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch2/arch_1_3_1158372877.arc ORA-00280: change 1170811 for thread 1 is in sequence #3 Specify log: {=suggested | filename | AUTO | CANCEL} AUTO ORA-00308: cannot open archived log '/home/oracle/arch2/arch_1_3_1158372877.arc' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 ORA-00308: cannot open archived log '/home/oracle/arch2/arch_1_3_1158372877.arc' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 SQL> ! ls -R /home/oracle/arch* /home/oracle/arch1: /home/oracle/arch2: >> Archive Log File이 없다. # Recover 재시도 SQL> RECOVER TABLESPACE USERS; ORA-00279: change 1170811 generated at 01/16/2024 20:05:11 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch2/arch_1_3_1158372877.arc ORA-00280: change 1170811 for thread 1 is in sequence #3 Specify log: {=suggested | filename | AUTO | CANCEL} CANCEL Media recovery cancelled. -- 성공이 아니다 SQL> ALTER TABLESPACE USERS ONLINE; ORA-01113: file 4 needs media recovery ORA-01110: data file 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' >> Archive Log File이 없기 때문에 특정 Tablespace Level에서는 Cencel Base Recovery 할 수 없다. >> 최근 정보로 손상없는 완전복구 불가 > 불안전복구 - Cold Backup - Data File 전체 복구 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 1172573 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1172573 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1172573 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS OFFLINE 1172570 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1172573 >> OFFLINE 상태는 복구가 안된다. SQL> SHUTDOWN IMMEDIATE SQL> STARTUP MOUNT SQL> ALTER DATABASE DATAFILE 4 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 1174008 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1174008 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1172570 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1174008 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1174008 SQL> SHUTDOWN IMMEDIATE # Restore - Cold Backup - Data File SQL> ! cp -av /home/oracle/backup/arch/cold_20240116/*.dbf /u01/app/oracle/oradata/ora11g/ > 전체 복구를 하면 아예 과거 정보로 돌아가버린다. SQL> STARTUP MOUNT SQL> RECOVER DATABASE UNTIL CANCEL ORA-00279: change 1162496 generated at 01/16/2024 02:54:17 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch2/arch_1_2_1158372877.arc ORA-00280: change 1162496 for thread 1 is in sequence #2 Specify log: {=suggested | filename | AUTO | CANCEL} CANCEL Media recovery cancelled. > RECOVER DATABASE : Redo Log File에서 백업 시점 SCN을 기준으로 복구 작업 진행 > UNTIL CANCEL : 사용자가 CANCEL을 입력할 때의 시점의 파일전까지 복구한다. SQL> ALTER DATABASE OPEN RESETLOGS; 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 1162500 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1162500 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1162500 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1162500 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1162500 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 NO CURRENT 1162497 16-JAN-24 2.8147E+14 1 0 YES UNUSED 0 0 1 0 YES UNUSED 0 0 SELECT COUNT(*) FROM hr.employees; >> 기존 데이터 정상 SELECT COUNT(*) FROM hr.new_202401; >> 백업 이후 생성된 데이터 없다 > 아카이브가 없다 > 불안정한 복구만 가능 > 안할수록 좋다 > users는 살렸지만 과거데이터로 돌아간 상태 = 최근 정보 손실 > 복구보다는 클론DB를 생성해서 users만 익스포트 받아 임포트 추천 > 주의!! DataFile 모두 online 상태여야한다 - offline이면 체크포인트 싱크가 안맞아 복구 불가 > shutdown - mount - Online - Cold Backup File - Cancel Base Recovery - Resetlogs - New Backup |
| # 일관성 있는 백업 수행 : close = offline = cold backup | |
| # cold_20240117 seq#1 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 NO CURRENT 1162497 16-JAN-24 2.8147E+14 1 0 YES UNUSED 0 0 1 0 YES UNUSED 0 0 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 1162500 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1162500 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1162500 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1162500 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1162500 SQL> ! mkdir -p /home/oracle/backup/arch/cold_20240117 CREATE PFILE ='/home/oracle/backup/arch/cold_20240117/initora11g_20240117.ora' FROM SPFILE; # 백업 시점의 날짜정보도 체크하자 SELECT to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss') systimestamp FROM dual; SQL> SHUTDOWN IMMEDIATE ! cp -av /u01/app/oracle/oradata/ora11g/*.dbf /home/oracle/backup/arch/cold_20240117/ ! cp -av /u01/app/oracle/oradata/ora11g/*.ctl /home/oracle/backup/arch/cold_20240117/ ! cp -av /u01/app/oracle/oradata/ora11g/*.log /home/oracle/backup/arch/cold_20240117/ SQL> ! ls /home/oracle/backup/arch/cold_20240117 control01.ctl example01.dbf initora11g_20240117.ora redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf SQL> STARTUP 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 1164608 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1164608 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1164608 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1164608 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1164608 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 NO CURRENT 1162497 16-JAN-24 2.8147E+14 1 0 YES UNUSED 0 0 1 0 YES UNUSED 0 0 |
|
| # 일관성 없는 백업 수행 : open = online = hot backup | |
| # hot_20240117 seq#1 SQL> ALTER SYSTEM CHECKPOINT; SELECT a.file#, a.name, a.checkpoint_change#, b.status, b.change#, time FROM v$datafile a, v$backup b WHERE a.file# = b.file#; FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME ---------- ----------------------------------------------- ------------------ ---------- ---------- --------- 1 /u01/app/oracle/oradata/ora11g/system01.dbf 1164608 NOT ACTIVE 0 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1164608 NOT ACTIVE 0 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 1164608 NOT ACTIVE 0 4 /u01/app/oracle/oradata/ora11g/users01.dbf 1164608 NOT ACTIVE 0 5 /u01/app/oracle/oradata/ora11g/example01.dbf 1164608 NOT ACTIVE 0 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 NO CURRENT 1162497 16-JAN-24 2.8147E+14 1 0 YES UNUSED 0 0 1 0 YES UNUSED 0 0 SQL> ALTER DATABASE BEGIN BACKUP; SQL> ! mkdir -p /home/oracle/backup/arch/hot_20240117 SQL> ! cp -av /u01/app/oracle/oradata/ora11g/*.dbf /home/oracle/backup/arch/hot_20240117/ SQL> ! ls /home/oracle/backup/arch/hot_20240117/ example01.dbf sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf SQL> ALTER DATABASE END BACKUP; SELECT a.file#, a.name, a.checkpoint_change#, b.status, b.change#, time FROM v$datafile a, v$backup b WHERE a.file# = b.file#; FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME ---------- -------------------------------------------- ------------------ ------------- ---------- --------- 1 /u01/app/oracle/oradata/ora11g/system01.dbf 1164962 NOT ACTIVE 1164962 16-JAN-24 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1164962 NOT ACTIVE 1164962 16-JAN-24 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 1164962 NOT ACTIVE 1164962 16-JAN-24 4 /u01/app/oracle/oradata/ora11g/users01.dbf 1164962 NOT ACTIVE 1164962 16-JAN-24 5 /u01/app/oracle/oradata/ora11g/example01.dbf 1164962 NOT ACTIVE 1164962 16-JAN-24 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 NO CURRENT 1162497 16-JAN-24 2.8147E+14 1 0 YES UNUSED 0 0 1 0 YES UNUSED 0 0 SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/home/oracle/backup/arch/hot_20240117/control01.ctl'; SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; 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 1162497 16-JAN-24 1165035 16-JAN-24 1 2 NO CURRENT 1165035 16-JAN-24 2.8147E+14 1 0 YES UNUSED 0 0 SELECT name,sequence#,first_change#,first_time,next_change#,next_time FROM v$archived_log; NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ----------------------------------------------- ---------- ------------- ----------- ------------ --------- ... /home/oracle/arch1/arch_1_1_1158441342.arc 1 1162497 16-JAN-24 1165035 16-JAN-24 /home/oracle/arch2/arch_1_1_1158441342.arc 1 1162497 16-JAN-24 1165035 16-JAN-24 SQL> ! ls /home/oracle/arch* /home/oracle/arch1: arch_1_1_1158441342.arc arch_1_6_1158372877.arc /home/oracle/arch2: arch_1_1_1158441342.arc arch_1_6_1158372877.arc # 백업정책에 따라 백업 아카이브 및 백업본 삭제에 주의 이전 백업본으로 복구 시에는 아카이브파일도 같이 필요하다 ! rm /home/oracle/arch1/*1158372877.arc ! rm /home/oracle/arch2/*1158372877.arc ! rm -R /home/oracle/backup/arch/*20240116 SQL> ! ls /home/oracle/arch* /home/oracle/arch1: arch_1_1_1158441342.arc /home/oracle/arch2: arch_1_1_1158441342.arc |
|
| # Archive Dest 단일화 | |
| SQL> SELECT destination,binding,status FROM v$archive_dest; DESTINATION BINDING STATUS -------------------------------------------------- --------- --------------- /home/oracle/arch1 MANDATORY VALID /home/oracle/arch2 OPTIONAL VALID SQL> SHOW PARAMETER log_archive_dest_1 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_1 string location=/home/oracle/arch1 mandatory SQL> SHOW PARAMETER log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string location=/home/oracle/arch2 optional # 기본값으로 리셋 SQL> ALTER SYSTEM SET log_archive_dest_2 = '' SCOPE = SPFILE; 또는 SQL> ALTER SYSTEM RESET log_archive_dest_2 SCOPE = SPFILE; SQL> SHUTDOWN IMMEDIATE SQL> STARTUP SQL> SHOW PARAMETER log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string SELECT TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS') SYSTIMESTAMP FROM DUAL; SYSTIMESTAMP ------------------- 2024-01-17 13:48:41 SQL> CREATE PFILE FROM SPFILE; SQL> ALTER SYSTEM SWITCH LOGFILE; 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 INACTIVE 1162497 16-JAN-24 1165035 16-JAN-24 1 2 YES ACTIVE 1165035 16-JAN-24 1173748 17-JAN-24 1 3 NO CURRENT 1173748 17-JAN-24 2.8147E+14 SELECT name,sequence#,first_change#,first_time,next_change#,next_time FROM v$archived_log; NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ----------------------------------------------- ---------- ------------- ----------- ------------ --------- /home/oracle/arch1/arch_1_1_1158441342.arc 1 1162497 16-JAN-24 1165035 16-JAN-24 /home/oracle/arch2/arch_1_1_1158441342.arc 1 1162497 16-JAN-24 1165035 16-JAN-24 /home/oracle/arch1/arch_1_2_1158441342.arc 2 1165035 16-JAN-24 1173748 17-JAN-24 SQL> ARCHIVE LOG LIST Database log mode Archive Mode Automatic archival Enabled Archive destination /home/oracle/arch1 Oldest online log sequence 1 Next log sequence to archive 3 Current log sequence 3 SQL> ! ls /home/oracle/arch* /home/oracle/arch1: arch_1_1_1158441342.arc arch_1_2_1158441342.arc /home/oracle/arch2: arch_1_1_1158441342.arc SQL> ! rm -r /home/oracle/arch2/ |
|
| << 시나리오 13 >> Archive 생성된 후 Inactive Log File이 삭제된 후 DB Close 상태 | |
| SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ---------- ---------- -------------------------------------------------- ---------- --------- --------------- 1 1 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE 2 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 3 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT SQL> ! ls /home/oracle/arch1 arch_1_1_1158441342.arc arch_1_2_1158441342.arc SQL> shutdown immediate # 장애유발 : INACTIVE 상태의 redo log file 삭제 SQL> ! rm /u01/app/oracle/oradata/ora11g/redo01.log SQL> STARTUP Database mounted. ORA-03113: end-of-file on communication channel Process ID: 23720 Session ID: 9 Serial number: 3 --Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_lgwr_23700.trc: --ORA-00313: open failed for members of log group 1 of thread 1 --ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ora11g/redo01.log' --ORA-27037: unable to obtain file status --Linux Error: 2: No such file or directory --Additional information: 3 >> INACTIVE 상태의 Redo Log File Group 삭제 시 STARTUP MOUNT가 안된다. >> Redo Log File 문제는 alert log 말고는 확인할 방법이 없다. SQL> conn / as sysdba SQL> startup mount SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ---------- ---------- -------------------------------------------------- ---------- --------- --------------- 1 1 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE 2 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 3 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT # 삭제 SQL> alter database drop logfile group 1; SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ---------- ---------- -------------------------------------------------- ---------- --------- --------------- 2 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 3 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT SQL> alter database open; SQL> alter database add logfile group 1 '/u01/app/oracle/oradata/ora11g/redo01.log' size 50M; SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ---------- ---------- -------------------------------------------------- ---------- --------- --------------- 1 0 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES UNUSED 2 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 3 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT |
|
| << 시나리오 14 >> DB OPEN 상태에서 current 그룹이 아닌 리두로그 파일이 삭제 | |
| SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ---------- ---------- -------------------------------------------------- ---------- --------- --------------- 1 0 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES UNUSED 2 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 3 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT ALTER SYSTEM SWITCH LOGFILE; SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ---------- ---------- -------------------------------------------------- ---------- --------- --------------- 1 4 /u01/app/oracle/oradata/ora11g/redo01.log 50 NO CURRENT 2 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 3 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES INACTIVE # 장애유발 : ACTIVE없는 INACTIVE 상태의 GROUP#2 SQL> ! rm /u01/app/oracle/oradata/ora11g/redo02.log 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 --Wed Jan 17 14:28:10 2024 --Expanded controlfile section 11 from 28 to 80 records --Requested to grow by 52 records; added 2 blocks of records --Archived Log entry 29 added for thread 1 sequence 4 ID 0xf7a334b dest 1: ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SWITCH LOGFILE; <<- hang 발생 ctrl + c ORA-01013: user requested cancel of current operation SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ---------- ---------- -------------------------------------------------- ---------- --------- --------------- 1 7 /u01/app/oracle/oradata/ora11g/redo01.log 50 NO CURRENT 2 5 /u01/app/oracle/oradata/ora11g/redo02.log 50 NO INACTIVE 3 6 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO INACTIVE >>> 진행은 되었다. SELECT name,sequence#,first_change#,first_time,next_change#,next_time FROM v$archived_log; NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ----------------------------------------------- ---------- ------------- ----------- ------------ --------- ... /home/oracle/arch1/arch_1_2_1158441342.arc 2 1165035 16-JAN-24 1173748 17-JAN-24 /home/oracle/arch1/arch_1_3_1158441342.arc 3 1173748 17-JAN-24 1175284 17-JAN-24 /home/oracle/arch1/arch_1_4_1158441342.arc 4 1175284 17-JAN-24 1175801 17-JAN-24 >> current seq#7 까지 왔는데 5, 6 파일이 없다?! >> 이 상태에서 고장나면 seq#4까지만 복구가능하다. SQL> ! ls /u01/app/oracle/oradata/ora11g/redo02.log ls: cannot access /u01/app/oracle/oradata/ora11g/redo02.log: No such file or directory >> 있어야 할 자리에 파일이 없는 상태 # DB 운영중 - Redo Log File을 삭제 후 재생성 alter database clear unarchived logfile group 2; SQL> ! ls /u01/app/oracle/oradata/ora11g/redo02.log /u01/app/oracle/oradata/ora11g/redo02.log >> 파일이 생겼다! SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ---------- ---------- -------------------------------------------------- ---------- --------- --------------- 1 7 /u01/app/oracle/oradata/ora11g/redo01.log 50 NO CURRENT 2 0 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES UNUSED 3 6 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES INACTIVE SELECT name,sequence#,first_change#,first_time,next_change#,next_time FROM v$archived_log; NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ----------------------------------------------- ---------- ------------- ----------- ------------ --------- ... /home/oracle/arch1/arch_1_2_1158441342.arc 2 1165035 16-JAN-24 1173748 17-JAN-24 /home/oracle/arch1/arch_1_3_1158441342.arc 3 1173748 17-JAN-24 1175284 17-JAN-24 /home/oracle/arch1/arch_1_4_1158441342.arc 4 1175284 17-JAN-24 1175801 17-JAN-24 5 1175801 17-JAN-24 1175813 17-JAN-24 /home/oracle/arch1/arch_1_6_1158441342.arc 6 1175813 17-JAN-24 1175819 17-JAN-24 SQL> ! ls /home/oracle/arch1 arch_1_1_1158441342.arc arch_1_2_1158441342.arc arch_1_3_1158441342.arc arch_1_4_1158441342.arc arch_1_6_1158441342.arc >> 갭 발생 >> 백업하자 |
|
| # Restore : cold_20240117 seq#1 | |
| SQL> SHUTDOWN IMMEDIATE ! cp -av /home/oracle/backup/arch/cold_20240117/*.dbf /u01/app/oracle/oradata/ora11g/ ! cp -av /home/oracle/backup/arch/cold_20240117/*.ctl /u01/app/oracle/oradata/ora11g/ ! cp -av /home/oracle/backup/arch/cold_20240117/*.log /u01/app/oracle/oradata/ora11g/ SQL> STARTUP 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 1164608 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1164608 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1164608 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1164608 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 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 NO CURRENT 1162497 16-JAN-24 2.8147E+14 1 0 YES UNUSED 0 0 1 0 YES UNUSED 0 0 ALTER SYSTEM ARCHIVE LOG CURRENT; SELECT name,sequence#,first_change#,first_time,next_change#,next_time FROM v$archived_log; NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ----------------------------------------------- ---------- ------------- ----------- ------------ --------- ... /home/oracle/arch1/arch_1_1_1158441342.arc 1 1162497 16-JAN-24 1167753 17-JAN-24 alter system switch logfile; 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 INACTIVE 1162497 16-JAN-24 1167753 17-JAN-24 1 2 YES ACTIVE 1167753 17-JAN-24 1167958 17-JAN-24 1 3 NO CURRENT 1167958 17-JAN-24 2.8147E+14 SELECT name,sequence#,first_change#,first_time,next_change#,next_time FROM v$archived_log; NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ----------------------------------------------- ---------- ------------- ----------- ------------ --------- ... /home/oracle/arch1/arch_1_1_1158441342.arc 1 1162497 16-JAN-24 1167753 17-JAN-24 /home/oracle/arch1/arch_1_2_1158441342.arc 2 1167753 17-JAN-24 1167958 17-JAN-24 14:59:47 SQL> ! ls /home/oracle/arch1 /home/oracle/arch1: arch_1_1_1158441342.arc arch_1_2_1158441342.arc arch_1_3_1158441342.arc arch_1_4_1158441342.arc arch_1_6_1158441342.arc ! rm /home/oracle/arch1/arch_1_3_1158441342.arc ! rm /home/oracle/arch1/arch_1_4_1158441342.arc ! rm /home/oracle/arch1/arch_1_6_1158441342.arc |
|
| << 시나리오 15 >> current 그룹이 삭제된 후 DB 정상적인 종료 | |
| SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ---------- ---------- -------------------------------------------------- ---------- --------- --------------- 1 1 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE 2 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES ACTIVE 3 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT create table hr.new_loc as select * from hr.locations; select count(*) from hr.new_loc; COUNT(*) ---------- 23 SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ---------- ---------- -------------------------------------------------- ---------- --------- --------------- 1 1 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE 2 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES ACTIVE 3 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT # 장애 유발 ! rm /u01/app/oracle/oradata/ora11g/redo03.log SQL> shutdown immediate SQL> startup Database mounted. ORA-03113: end-of-file on communication channel Process ID: 25091 Session ID: 9 Serial number: 3 --Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_lgwr_25071.trc: --ORA-00313: open failed for members of log group 3 of thread 1 --ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ora11g/redo03.log' --ORA-27037: unable to obtain file status --Linux Error: 2: No such file or directory --Additional information: 3 conn / as sysdba SQL> startup mount SQL> recover database until cancel Media recovery complete. > current group seq#3이 깨졌으니 직전 seq#2까지 복구하자 > until cancel > 불안전한 복구 > resetlogs alter database open resetlogs; SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ---------- ---------- -------------------------------------------------- ---------- --------- --------------- 1 1 /u01/app/oracle/oradata/ora11g/redo01.log 50 NO CURRENT 2 0 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES UNUSED 3 0 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES UNUSED SQL> ! ls /u01/app/oracle/oradata/ora11g/redo03.log /u01/app/oracle/oradata/ora11g/redo03.log > 파일도 생성되었다. > 깨진 시점 전까지 복구된다. select count(*) from hr.new_loc; COUNT(*) ---------- 23 > DB를 정상적으로 내렸기 때문에 full checkpoint 발생 > 메모리에 커밋된 더티버퍼들은 라이트, 커밋안한건 롤백 current한 redo group 전까지 복구 된다 > 리두정보가 있어야 오픈이 가능하니 리커버(복구할건없지만 복구할게 없다 resetlogs >> 백업하자 |
|
| # Restore : cold_20240117 seq#1 >> 완료 | |
| 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 1164608 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1164608 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1164608 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1164608 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 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 4 NO CURRENT 1167862 17-JAN-24 2.8147E+14 1 2 YES INACTIVE 1167696 17-JAN-24 1167701 17-JAN-24 1 3 YES ACTIVE 1167701 17-JAN-24 1167862 17-JAN-24 SELECT name,sequence#,first_change#,first_time,next_change#,next_time FROM v$archived_log; NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ----------------------------------------------- ---------- ------------- ----------- ------------ --------- ... /home/oracle/arch1/arch_1_1_1158441342.arc 1 1162497 16-JAN-24 1167696 17-JAN-24 /home/oracle/arch1/arch_1_2_1158441342.arc 2 1167696 17-JAN-24 1167701 17-JAN-24 /home/oracle/arch1/arch_1_3_1158441342.arc 3 1167701 17-JAN-24 1167862 17-JAN-24 14:59:47 SQL> ! ls /home/oracle/arch1 /home/oracle/arch1: arch_1_1_1158441342.arc arch_1_2_1158441342.arc arch_1_3_1158441342.arc |
|
| << 시나리오 16 >> current 그룹이 삭제된 후 DB 비정상적인 종료 | |
| create table hr.copy_emp as select * from hr.employees; SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ---------- ---------- -------------------------------------------------- ---------- --------- --------------- 1 1 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES ACTIVE 2 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 NO CURRENT 3 0 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES UNUSED alter system switch logfile; SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ---------- ---------- -------------------------------------------------- ---------- --------- --------------- 1 1 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES ACTIVE 2 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES ACTIVE 3 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT create table hr.copy_dept as select * from hr.departments; SELECT name,sequence#,first_change#,first_time,next_change#,next_time FROM v$archived_log; NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ----------------------------------------------- ---------- ------------- ----------- ------------ --------- /home/oracle/arch1/arch_1_1_1158441342.arc 1 1162497 16-JAN-24 1164964 17-JAN-24 /home/oracle/arch1/arch_1_2_1158441342.arc 2 1164964 17-JAN-24 1165187 17-JAN-24 16:33:01 SQL> ! ls /home/oracle/arch1 arch_1_1_1158441342.arc arch_1_2_1158441342.arc # 장애발생 : current 상태(seq#3)의 redo log file 손상 ! rm /u01/app/oracle/oradata/ora11g/redo03.log # DB 비정상 종료 SQL> shutdown abort SQL> startup Database mounted. ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ora11g/redo03.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 > DB 비정상 종료 > 메모리에 더티버퍼들 purge 상태 > db 오픈 - 인스턴스 리커버리 체크 시 리두 필요 > 없으니 고장 select status from v$instance; STATUS --------------- MOUNTED 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 1164964 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1164964 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1164964 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1164964 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1164964 # Restore - Cold Backup - Data File ! cp -av /home/oracle/backup/arch/cold_20240117/*.dbf /u01/app/oracle/oradata/ora11g/ # Recovery database SQL> recover database until cancel; ORA-00279: change 1164605 generated at 01/16/2024 21:40:44 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch1/arch_1_1_1158441342.arc ORA-00280: change 1164605 for thread 1 is in sequence #1 16:37:04 Specify log: {=suggested | filename | AUTO | CANCEL} << 그냥 엔터 ORA-00279: change 1164964 generated at 01/17/2024 16:27:52 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch1/arch_1_2_1158441342.arc ORA-00280: change 1164964 for thread 1 is in sequence #2 ORA-00278: log file '/home/oracle/arch1/arch_1_1_1158441342.arc' no longer needed for this recovery << seq#1 완료 16:37:06 Specify log: {=suggested | filename | AUTO | CANCEL} << 그냥 엔터 ORA-00279: change 1165187 generated at 01/17/2024 16:31:56 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158441342.arc ORA-00280: change 1165187 for thread 1 is in sequence #3 ORA-00278: log file '/home/oracle/arch1/arch_1_2_1158441342.arc' no longer needed for this recovery << seq#2 완료 16:37:10 Specify log: {=suggested | filename | AUTO | CANCEL} << 그냥 엔터 ORA-00308: cannot open archived log '/home/oracle/arch1/arch_1_3_1158441342.arc' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 << seq#3 파일이 없는게 맞으니 마무리를 위해 다시 실행 SQL> recover database until cancel; ORA-00279: change 1165187 generated at 01/17/2024 16:31:56 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158441342.arc ORA-00280: change 1165187 for thread 1 is in sequence #3 16:38:17 Specify log: {=suggested | filename | AUTO | CANCEL} CANCEL Media recovery cancelled. alter database open resetlogs; 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 1165191 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1165191 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1165191 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1165191 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1165191 select count(*) from hr.copy_emp; COUNT(*) ---------- 107 >>> 아카이브 로그가 있어 정상 작동 select count(*) from hr.copy_dept; >> current 상태의 group에서 생성된 작업으로 파일 손상으로 데이터 복구 불가 SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ---------- ---------- -------------------------------------------------- ---------- --------- --------------- 1 1 /u01/app/oracle/oradata/ora11g/redo01.log 50 NO CURRENT 2 0 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES UNUSED 3 0 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES UNUSED SELECT name,sequence#,first_change#,first_time,next_change#,next_time FROM v$archived_log; NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ----------------------------------------------- ---------- ------------- ----------- ------------ --------- /home/oracle/arch1/arch_1_1_1158441342.arc 1 1162497 16-JAN-24 1164964 17-JAN-24 /home/oracle/arch1/arch_1_2_1158441342.arc 2 1164964 17-JAN-24 1165187 17-JAN-24 SQL> ! ls /home/oracle/arch1 arch_1_1_1158441342.arc arch_1_2_1158441342.arc >>> (서로 다른 디스크에) Redo Log File의 이중화만 되어있었어도 문제없을 케이스이다. resetlogs >> 백업하자 |
|
| # Restore : cold_20240117 seq#1 >> 완료 | |
| 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 1166667 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1166667 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1166667 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1166667 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1166667 SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ---------- ---------- -------------------------------------------------- ---------- --------- --------------- 1 1 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES ACTIVE 2 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 NO CURRENT 3 0 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES UNUSED SELECT name,sequence#,first_change#,first_time,next_change#,next_time FROM v$archived_log; NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ----------------------------------------------- ---------- ------------- ----------- ------------ --------- /home/oracle/arch1/arch_1_1_1158511351.arc 1 1165188 17-JAN-24 1167175 17-JAN-24 SQL> ! ls /home/oracle/arch1 arch_1_1_1158511351.arc |
|
'Oracle 11g > Backup & Recovery' 카테고리의 다른 글
| 69 Backup&Recovery, Archive Log Mode (0) | 2024.01.19 |
|---|---|
| 68 Backup&Recovery, Archive Log Mode, control file 손상 (0) | 2024.01.18 |
| 66 Backup&Recovery, Archive Log Mode, Backup (0) | 2024.01.16 |
| 65 Backup&Recovery, Archive Log Mode (0) | 2024.01.15 |
| 64 Backup&Recovery, Archive Log Mode (0) | 2024.01.12 |