| # 요약 | |
| << 시나리오 10 >> Undo Data File 손상 << 시나리오 11 >> 운영중에 Undo Data File 손상 + DB를 내릴 수 없다 << 시나리오 12 >> 특정 데이터 파일과 아카이브 파일이 손상되었을 경우 불안전 복구를 수행 << 시나리오 12-2 >> 중간에 Undo Tablespace 수정 후 백업을 하지 않아 기존 백업파일과 SCN 싱크가 맞지 않다. << Backup >> 일관성 있는 Backup = Cold Backup = Offline Backup = Closed Backup << Backup >> 일관성없는 백업 = Hot Backup = Open Backup = Online Backup |
|
| << 시나리오 10 >> Undo 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 1148361 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1148361 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1148361 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1148361 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1148755 SELECT a.file#,a.name, a.checkpoint_change#,b.status,b.change#,b.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 1148361 NOT ACTIVE 1147766 15-JAN-24 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1148361 NOT ACTIVE 1147766 15-JAN-24 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 1148361 NOT ACTIVE 1147766 15-JAN-24 4 /u01/app/oracle/oradata/ora11g/users01.dbf 1148361 NOT ACTIVE 1147766 15-JAN-24 5 /u01/app/oracle/oradata/ora11g/example01.dbf 1148755 NOT ACTIVE 1147766 15-JAN-24 SELECT sequence#, name, first_change#, first_time, next_change#, next_time FROM v$archived_log; SEQUENCE# NAME FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ------------------------------------------------- ------------- ----------- ------------ --------- 49 /home/oracle/arch1/arch_1_49_1154915502.arc 1145390 15-JAN-24 1146954 15-JAN-24 49 /home/oracle/arch2/arch_1_49_1154915502.arc 1145390 15-JAN-24 1146954 15-JAN-24 ... ★ 있어야할 자리에 아카이브 파일이 있는지 데일리 체크 ★ SQL> ! ls /home/oracle/arch* /home/oracle/arch1: arch_1_49_1154915502.arc arch_1_50_1154915502.arc arch_1_51_1154915502.arc arch_1_52_1154915502.arc arch_1_53_1154915502.arc /home/oracle/arch2: arch_1_49_1154915502.arc arch_1_50_1154915502.arc arch_1_51_1154915502.arc arch_1_52_1154915502.arc arch_1_53_1154915502.arc SELECT * FROM v$log_history; RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TI NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOG ---------- ---------- ---------- ---------- ------------- -------- ------------ ----------------- -------- ... 52 1158294836 1 52 1148070 24/01/15 1148089 635002 23/12/07 53 1158294839 1 53 1148089 24/01/15 1148093 635002 23/12/07 SELECT segment_id, segment_name, owner, tablespace_name, status FROM dba_rollback_segs; SEGMENT_ID SEGMENT_NAME OWNER TABLESPACE_NAME STATUS ---------- ---------------------- ------ ------------------------------ ----------- 0 SYSTEM SYS SYSTEM ONLINE 10 _SYSSMU10_4014540061$ PUBLIC UNDOTBS ONLINE 9 _SYSSMU9_2833524074$ PUBLIC UNDOTBS ONLINE 8 _SYSSMU8_2378381138$ PUBLIC UNDOTBS ONLINE 7 _SYSSMU7_281670344$ PUBLIC UNDOTBS ONLINE 6 _SYSSMU6_4002525576$ PUBLIC UNDOTBS ONLINE 5 _SYSSMU5_784711609$ PUBLIC UNDOTBS ONLINE 4 _SYSSMU4_2831617015$ PUBLIC UNDOTBS ONLINE 3 _SYSSMU3_1004418964$ PUBLIC UNDOTBS ONLINE 2 _SYSSMU2_1701567146$ PUBLIC UNDOTBS ONLINE 1 _SYSSMU1_886085095$ PUBLIC UNDOTBS ONLINE << HR SESSION >> 테스트 : 트랜잭션 발생 UPDATE hr.employees SET salary=200 WHERE employee_id=100; # 어떤 유저가 어떤 언두 세그먼트를 이용하고 있는가 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; USERNAME SID SERIAL# NAME XIDUSN UBAFIL UBABLK USED_UBLK -------- ---------- ---------- ---------------------------------- ---------- ---------- ---------- ---------- SYS 184 207 _SYSSMU7_281670344$ 7 3 534 1 HR 183 253 _SYSSMU10_4014540061$ 10 3 327 1 SID 세션고유ID NAME 언두 세그먼트 이름 xidusn 언두세그먼트번호 ubafil 언두세그먼트에 속한 파일번호 ubablk 언두블럭 used_ublk 사용중인 블럭 수 SQL> SHOW PARAMETER undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS > 언두 정보 -> parameter file -> spfile or pfile AUTO 언두세그먼트 자동관리, 기본값 10개씩 확장했다가 필요없으면 10개씩 offline 조절 undo_retention 커밋 하더라도 1800초 동안은 이전값을 가지고 있다. 단, 여유공간이 있어야한다. 참고) undo_retention : 1800 -> 900 SQL> ALTER SYSTEM SET undo_retention=900 [SCOPE=BOTH 기본값] ; SQL> SHUTDOWN IMMEDIATE # 장애 유발 SQL> ! rm /u01/app/oracle/oradata/ora11g/undotbs01.dbf SQL> 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' SQL> SELECT * FROM v$recover_file; FILE# ONLINE ONLINE_STATUS ERROR CHANGE# TIME ---------- ------- ------------- ------------------------------------------------------- ---------- --------- 3 ONLINE ONLINE FILE NOT FOUND 0 >> 시스템, 언두는 오프라인 불가 # Restore SQL> ! cp -av /home/oracle/backup/arch/hot_20240115/undotbs01.dbf /u01/app/oracle/oradata/ora11g/ # Recover SQL> RECOVER TABLESPACE undotbs; ORA-00279: change 1147766 generated at 01/15/2024 04:27:12 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch2/arch_1_50_1154915502.arc ORA-00280: change 1147766 for thread 1 is in sequence #50 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} AUTO ORA-00279: change 1147942 generated at 01/15/2024 04:32:40 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch2/arch_1_51_1154915502.arc ORA-00280: change 1147942 for thread 1 is in sequence #51 Log applied. Media recovery complete. SQL> ALTER DATABASE OPEN; << HR SESSION >> SELECT salary FROM hr.employees WHERE employee_id=100; SALARY ---------- 24000 UPDATE hr.employees SET salary=200 WHERE employee_id=100; 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; USERNAME SID SERIAL# NAME XIDUSN UBAFIL UBABLK USED_UBLK ---------- ---------- ---------- -------------------------------- ---------- ---------- ---------- ---------- HR 10 33 _SYSSMU2_1701567146$ 2 3 152 1 SQL> ROLLBACK; |
|
| << 시나리오 11 >> 운영중에 Undo Data File 손상 + DB를 내릴 수 없다 | |
| SELECT segment_id, segment_name, owner, tablespace_name, status FROM dba_rollback_segs; SEGMENT_ID SEGMENT_NAME OWNER TABLESPACE_NAME STATUS ---------- ---------------------- ------ ------------------------------ ----------- 0 SYSTEM SYS SYSTEM ONLINE 10 _SYSSMU10_4014540061$ PUBLIC UNDOTBS ONLINE 9 _SYSSMU9_2833524074$ PUBLIC UNDOTBS ONLINE 8 _SYSSMU8_2378381138$ PUBLIC UNDOTBS ONLINE 7 _SYSSMU7_281670344$ PUBLIC UNDOTBS ONLINE 6 _SYSSMU6_4002525576$ PUBLIC UNDOTBS ONLINE 5 _SYSSMU5_784711609$ PUBLIC UNDOTBS ONLINE 4 _SYSSMU4_2831617015$ PUBLIC UNDOTBS ONLINE 3 _SYSSMU3_1004418964$ PUBLIC UNDOTBS ONLINE 2 _SYSSMU2_1701567146$ PUBLIC UNDOTBS ONLINE 1 _SYSSMU1_886085095$ PUBLIC UNDOTBS ONLINE SQL> SHOW PARAMETER undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS << HR SESSION >> SQL> SELECT salary FROM hr.employees WHERE employee_id=100; SALARY ---------- 24000 SQL> UPDATE hr.employees SET salary=200 WHERE employee_id=100; 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; USERNAME SID SERIAL# NAME XIDUSN UBAFIL UBABLK USED_UBLK ---------- ---------- ---------- -------------------------------- ---------- ---------- ---------- ---------- HR 10 33 _SYSSMU2_1701567146$ 2 3 152 1 # 장애 유발 SQL> ! rm /u01/app/oracle/oradata/ora11g/undotbs01.dbf > 모니터링중에 발견 --Mon Jan 15 20:59:59 2024 --Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_j000_9748.trc: --ORA-12012: error on auto execute of job 3 --ORA-01116: error in opening database file 3 --ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' --ORA-27041: unable to open file --Linux Error: 2: No such file or directory --Additional information: 3 SQL> ! ls /u01/app/oracle/oradata/ora11g/undotbs01.dbf ls: cannot access /u01/app/oracle/oradata/ora11g/undotbs01.dbf: No such file or directory >> 언두 파일 손상 ?! DB는 절대 내릴 수 없다!! # 새로운 언두 테이블스페이스 생성 CREATE UNDO TABLESPACE undo_new DATAFILE '/u01/app/oracle/oradata/ora11g/undo_new01.dbf' SIZE 10M AUTOEXTEND ON; 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 1155496 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1155496 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1155496 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1155496 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1155496 6 /u01/app/oracle/oradata/ora11g/undo_new01.dbf UNDO_NEW ONLINE 1157255 SELECT segment_id, segment_name, owner, tablespace_name, status FROM dba_rollback_segs; SEGMENT_ID SEGMENT_NAME OWNER TABLESPACE_NAME STATUS ---------- ---------------------- ------ ------------------------------ ----------- 0 SYSTEM SYS SYSTEM ONLINE 10 _SYSSMU10_4014540061$ PUBLIC UNDOTBS ONLINE 9 _SYSSMU9_2833524074$ PUBLIC UNDOTBS ONLINE 8 _SYSSMU8_2378381138$ PUBLIC UNDOTBS ONLINE 7 _SYSSMU7_281670344$ PUBLIC UNDOTBS ONLINE 6 _SYSSMU6_4002525576$ PUBLIC UNDOTBS ONLINE 5 _SYSSMU5_784711609$ PUBLIC UNDOTBS ONLINE 4 _SYSSMU4_2831617015$ PUBLIC UNDOTBS ONLINE 3 _SYSSMU3_1004418964$ PUBLIC UNDOTBS ONLINE 2 _SYSSMU2_1701567146$ PUBLIC UNDOTBS ONLINE 1 _SYSSMU1_886085095$ PUBLIC UNDOTBS ONLINE 20 _SYSSMU20_2779260104$ PUBLIC UNDO_NEW OFFLINE 19 _SYSSMU19_3969571117$ PUBLIC UNDO_NEW OFFLINE 18 _SYSSMU18_2511651061$ PUBLIC UNDO_NEW OFFLINE 17 _SYSSMU17_300744309$ PUBLIC UNDO_NEW OFFLINE 16 _SYSSMU16_2366164722$ PUBLIC UNDO_NEW OFFLINE 15 _SYSSMU15_1354978705$ PUBLIC UNDO_NEW OFFLINE 14 _SYSSMU14_2609685890$ PUBLIC UNDO_NEW OFFLINE 13 _SYSSMU13_3818580637$ PUBLIC UNDO_NEW OFFLINE 12 _SYSSMU12_3309662486$ PUBLIC UNDO_NEW OFFLINE 11 _SYSSMU11_3300737399$ PUBLIC UNDO_NEW OFFLINE # Undo Tablespace로 지정 SQL> ALTER SYSTEM SET undo_tablespace = undo_new; SQL> SHOW PARAMETER undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDO_NEW SELECT segment_id, segment_name, owner, tablespace_name, status FROM dba_rollback_segs; SEGMENT_ID SEGMENT_NAME OWNER TABLESPACE_NAME STATUS ---------- ---------------------- ------ ------------------------------ ----------- 0 SYSTEM SYS SYSTEM ONLINE 10 _SYSSMU10_4014540061$ PUBLIC UNDOTBS OFFLINE 9 _SYSSMU9_2833524074$ PUBLIC UNDOTBS OFFLINE 8 _SYSSMU8_2378381138$ PUBLIC UNDOTBS OFFLINE 7 _SYSSMU7_281670344$ PUBLIC UNDOTBS OFFLINE 6 _SYSSMU6_4002525576$ PUBLIC UNDOTBS OFFLINE 5 _SYSSMU5_784711609$ PUBLIC UNDOTBS OFFLINE 4 _SYSSMU4_2831617015$ PUBLIC UNDOTBS OFFLINE 3 _SYSSMU3_1004418964$ PUBLIC UNDOTBS OFFLINE 2 _SYSSMU2_1701567146$ PUBLIC UNDOTBS ONLINE 1 _SYSSMU1_886085095$ PUBLIC UNDOTBS OFFLINE 20 _SYSSMU20_2779260104$ PUBLIC UNDO_NEW ONLINE 19 _SYSSMU19_3969571117$ PUBLIC UNDO_NEW ONLINE 18 _SYSSMU18_2511651061$ PUBLIC UNDO_NEW ONLINE 17 _SYSSMU17_300744309$ PUBLIC UNDO_NEW ONLINE 16 _SYSSMU16_2366164722$ PUBLIC UNDO_NEW ONLINE 15 _SYSSMU15_1354978705$ PUBLIC UNDO_NEW ONLINE 14 _SYSSMU14_2609685890$ PUBLIC UNDO_NEW ONLINE 13 _SYSSMU13_3818580637$ PUBLIC UNDO_NEW ONLINE 12 _SYSSMU12_3309662486$ PUBLIC UNDO_NEW ONLINE 11 _SYSSMU11_3300737399$ PUBLIC UNDO_NEW ONLINE >> 장애 발생 전 트랜잭션 작업중이던 것 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; USERNAME SID SERIAL# NAME XIDUSN UBAFIL UBABLK USED_UBLK ---------- ---------- ---------- -------------------------------- ---------- ---------- ---------- ---------- HR 10 33 _SYSSMU2_1701567146$ 2 3 152 1 << HR NEW SESSION >> UPDATE hr.employees SET salary = 1000 WHERE employee_id = 200; 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; USERNAME SID SERIAL# NAME XIDUSN UBAFIL UBABLK USED_UBLK ---------- ---------- ---------- -------------------------------- ---------- ---------- ---------- ---------- HR 10 33 _SYSSMU2_1701567146$ -- old 2 3 152 1 HR 22 117 _SYSSMU19_3969571117$ -- new 19 6 260 1 SELECT a.name, b.status FROM v$rollname a, v$rollstat b WHERE a.usn = b.usn; NAME STATUS ----------------------------------------------- --------------- SYSTEM ONLINE _SYSSMU2_1701567146$ PENDING OFFLINE _SYSSMU11_3300737399$ ONLINE _SYSSMU12_3309662486$ ONLINE _SYSSMU13_3818580637$ ONLINE _SYSSMU14_2609685890$ ONLINE _SYSSMU15_1354978705$ ONLINE _SYSSMU16_2366164722$ ONLINE _SYSSMU17_300744309$ ONLINE _SYSSMU18_2511651061$ ONLINE _SYSSMU19_3969571117$ ONLINE _SYSSMU20_2779260104$ ONLINE # 기존 세션 즉시종료 SQL> ALTER SYSTEM KILL SESSION '10,33' IMMEDIATE; 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; USERNAME SID SERIAL# NAME XIDUSN UBAFIL UBABLK USED_UBLK ---------- ---------- ---------- -------------------------------- ---------- ---------- ---------- ---------- HR 22 117 _SYSSMU19_3969571117$ 19 6 260 1 SELECT a.name, b.status FROM v$rollname a, v$rollstat b WHERE a.usn = b.usn; NAME STATUS ----------------------------------------------- ----------- SYSTEM ONLINE _SYSSMU11_3300737399$ ONLINE _SYSSMU12_3309662486$ ONLINE _SYSSMU13_3818580637$ ONLINE _SYSSMU14_2609685890$ ONLINE _SYSSMU15_1354978705$ ONLINE _SYSSMU16_2366164722$ ONLINE _SYSSMU17_300744309$ ONLINE _SYSSMU18_2511651061$ ONLINE _SYSSMU19_3969571117$ ONLINE _SYSSMU20_2779260104$ ONLINE # 기존 언두가 OFFLINE일 경우 기존 테이블스페이스 삭제 SQL> DROP TABLESPACE undotbs INCLUDING CONTENTS AND DATAFILES; |
|
| # 어떤 언두세그먼트가 PENDING OFFLINE이면 테이블스페이스를 삭제할 수 없을 경우 수동 작업 SELECT a.name, b.status FROM v$rollname a, v$rollstat b WHERE a.usn = b.usn; NAME STATUS ----------------------------------------------- ----------- SYSTEM ONLINE _SYSSMU2_1701567146$ PENDING OFFLINE _SYSSMU11_3300737399$ ONLINE _SYSSMU12_3309662486$ ONLINE _SYSSMU13_3818580637$ ONLINE _SYSSMU14_2609685890$ ONLINE _SYSSMU15_1354978705$ ONLINE _SYSSMU16_2366164722$ ONLINE _SYSSMU17_300744309$ ONLINE _SYSSMU18_2511651061$ ONLINE _SYSSMU19_3969571117$ ONLINE _SYSSMU20_2779260104$ ONLINE >> 세션 kill 했는데 아직도 PENDING OFFLINE ? 1. 현재 일자로 pfile 생성 SQL>CREATE PFILE = '$ORACLE_HOME/dbs/initora11g_20240116.ora' FROM SPFILE; 2. DB 정상 종료 SQL> SHUTDOWN IMMEDIATE 3. pfile 편집 SQL> ! vi $ORACLE_HOME/dbs/initora11g_20240116.ora _offline_rollback_segments=(_SYSSMU2_1701567146$, ...) >> 수동으로 오프라인만들기 추가 4. pfile 로 DB 시작 SQL> STARTUP PFILE=$ORACLE_HOME/dbs/initora11g_20240116.ora 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' 5. 기존 Undo File OFFLINE SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' OFFLINE; 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 1155496 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1155496 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1155496 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1155496 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS RECOVER 1155496 6 /u01/app/oracle/oradata/ora11g/undo_new01.dbf UNDO_NEW ONLINE 1157255 6. DB OPEN ALTER DATABASE OPEN; 7. 기존 언두테이블스페이스 삭제 SQL> DROP TABLESPACE undotbs 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 1178935 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1178935 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1178935 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1178935 6 /u01/app/oracle/oradata/ora11g/undo_new01.dbf UNDO_NEW ONLINE 1178935 |
|
| # PFILE -> SPFILE로 DB OPEN SQL> SHUTDOWN IMMEDIATE SQL> STARTUP SQL> SHOW PARAMETER spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/11.2.0.4/db_1/dbs/spfileora11g.ora |
|
| # Undo Tablespace UNDO_NEW -> UNDOTBS 변경 1. Undo Tablespace 재생성 CREATE UNDO TABLESPACE undotbs DATAFILE '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' SIZE 10M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL; 2. Undo Tablespace 설정 SQL> ALTER SYSTEM SET undo_tablespace = undotbs; SQL> SHOW PARAMETER undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS SELECT segment_id, segment_name, owner, tablespace_name, status FROM dba_rollback_segs; 3. 기존 Undo Tablespace 삭제 SQL> DROP TABLESPACE undo_new INCLUDING CONTENTS AND DATAFILES; 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 54 YES INACTIVE 1148093 15-JAN-24 1178934 15-JAN-24 1 55 NO CURRENT 1178934 15-JAN-24 2.8147E+14 1 53 YES INACTIVE 1148089 15-JAN-24 1148093 15-JAN-24 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 1180495 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1180495 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1180923 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1180495 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1180495 |
|
>>> undo tablespace 신규생성 = DataFile 정보 변경 = 백업하자
| << 시나리오 12 >> 특정 데이터 파일과 아카이브 파일이 손상되었을 경우 불안전 복구를 수행 | |
| SELECT a.file#,a.name, a.checkpoint_change#,b.status,b.change#,b.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 1156507 NOT ACTIVE 1156323 16-JAN-24 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1156507 NOT ACTIVE 1156323 16-JAN-24 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 1156507 NOT ACTIVE 1156323 16-JAN-24 4 /u01/app/oracle/oradata/ora11g/users01.dbf 1156507 NOT ACTIVE 1156323 16-JAN-24 5 /u01/app/oracle/oradata/ora11g/example01.dbf 1156507 NOT ACTIVE 1156323 16-JAN-24 SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> ALTER SYSTEM SWITCH LOGFILE; SELECT sequence#, name, first_change#, first_time, next_change#, next_time FROM v$archived_log; SEQUENCE# NAME FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ----------------------------------------------- ------------- ----------- ------------ --------- 49 /home/oracle/arch1/arch_1_49_1154915502.arc 1145390 15-JAN-24 1146954 15-JAN-24 49 /home/oracle/arch2/arch_1_49_1154915502.arc 1145390 15-JAN-24 1146954 15-JAN-24 50 /home/oracle/arch1/arch_1_50_1154915502.arc 1146954 15-JAN-24 1155615 16-JAN-24 50 /home/oracle/arch2/arch_1_50_1154915502.arc 1146954 15-JAN-24 1155615 16-JAN-24 51 /home/oracle/arch1/arch_1_51_1154915502.arc 1155615 16-JAN-24 1156507 16-JAN-24 51 /home/oracle/arch2/arch_1_51_1154915502.arc 1155615 16-JAN-24 1156507 16-JAN-24 52 /home/oracle/arch1/arch_1_52_1154915502.arc 1156507 16-JAN-24 1158351 16-JAN-24 52 /home/oracle/arch2/arch_1_52_1154915502.arc 1156507 16-JAN-24 1158351 16-JAN-24 53 /home/oracle/arch1/arch_1_53_1154915502.arc 1158351 16-JAN-24 1158354 16-JAN-24 53 /home/oracle/arch2/arch_1_53_1154915502.arc 1158351 16-JAN-24 1158354 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 54 NO CURRENT 1158354 16-JAN-24 2.8147E+14 1 52 YES ACTIVE 1156507 16-JAN-24 1158351 16-JAN-24 1 53 YES ACTIVE 1158351 16-JAN-24 1158354 16-JAN-24 # 장애발생 SQL> ! rm /home/oracle/arch1/*.* SQL> ! rm /home/oracle/arch2/*.* SQL> ! rm /u01/app/oracle/oradata/ora11g/example01.dbf SQL> ALTER SYSTEM CHECKPOINT; SQL> ALTER SYSTEM CHECKPOINT; >> Alert Log 반응이 없을 수 도 있다.. SQL> SHUTDOWN IMMEDIATE SQL> STARTUP Database mounted. ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: '/u01/app/oracle/oradata/ora11g/example01.dbf' SQL> SELECT * FROM v$recover_file; FILE# ONLINE ONLINE_STATUS ERROR CHANGE# TIME ---------- ------- ------------- ------------------------------------------------------- ---------- --------- 5 ONLINE ONLINE FILE NOT FOUND 0 SQL> ALTER DATABASE DATAFILE 5 OFFLINE; SQL> ALTER DATABASE OPEN; # RESTORE SQL> ! cp -av /home/oracle/backup/arch/hot_20240116/example01.dbf /u01/app/oracle/oradata/ora11g/ # RECOVERY SQL> RECOVER TABLESPACE example; ORA-00279: change 1147766 generated at 01/15/2024 04:27:12 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch2/arch_1_50_1154915502.arc ORA-00280: change 1147766 for thread 1 is in sequence #50 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} AUTO ORA-00308: cannot open archived log '/home/oracle/arch2/arch_1_50_1154915502.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_50_1154915502.arc' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 >> 완전복구 시도 실패 : 아카이브가 없다 # 완전 복구 작업을 수행하기 위해서는 마지막 백업파일 이후의 변경 이력정보가 있는 아카이브 파일이 있어야하는데 없어서 복구 작업 실패 # cancel base recovery 시도 SQL> RECOVER TABLESPACE example ORA-00279: change 1147766 generated at 01/15/2024 04:27:12 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch2/arch_1_50_1154915502.arc ORA-00280: change 1147766 for thread 1 is in sequence #50 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} CANCEL Media recovery cancelled. >> 성공?! SQL> ALTER TABLESPACE example ONLINE; ORA-01113: file 5 needs media recovery ORA-01110: data file 5: '/u01/app/oracle/oradata/ora11g/example01.dbf' >> 복구 실패 # 불안전한 복구는 테이블스페이스 레벨이 아닌 데이터베이스 레벨에서 수행해야한다. # 아카이브파일 손상되었을 경우, 전체 데이터베이스를 과거 시간으로 되돌아 가야하는 복구 방식을 수행해야 한다. # DB종료 SQL> SHUTDOWN ABORT # Data File Restore 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 1155931 generated at 01/16/2024 01:21:24 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch2/arch_1_51_1154915502.arc ORA-00280: change 1155931 for thread 1 is in sequence #51 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} CANCEL Media recovery cancelled. >>>>> 시나리오 12-2. 복구파일에 문제가 생겼다면 ?? SQL> ALTER DATABASE OPEN RESETLOGS; >> 복구한 데이터파일과 컨트롤파일 정보가 달라 resetlogs : 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 1155932 16-JAN-24 2.8147E+14 1 0 YES UNUSED 0 0 1 0 YES UNUSED 0 0 ** 불안전한 복구 = RESETLOGS 필수 # RESETLOGS 몇번 수행했는가 SELECT * FROM v$database_incarnation; INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS PRIOR_RESETLOGS STATUS RESETLOGS_ID _CHANGE# _TIME ------------ ----------------- -------------- --------------- --------------- ----------- ------------ 1 1 25-AUG-13 0 PARENT 824361512 2 635002 07-DEC-23 1 25-AUG-13 PARENT 1154915502 3 1155932 16-JAN-24 635002 07-DEC-23 CURRENT 1158372877 ALTER SYSTEM SWITCH LOGFILE; 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_54_1154915502.arc 54 1158354 16-JAN-24 1159310 16-JAN-24 /home/oracle/arch2/arch_1_54_1154915502.arc 54 1158354 16-JAN-24 1159310 16-JAN-24 /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/arch2/arch_1_1_[RESETLOGS_ID].arc >> RESETLOGS_ID 로 DB오픈 >> 완전히 과거로 가는게 아닌 이상, RESETLOGS_ID가 다른 과거파일에서 현재까지로 복구는 불가하다 # 데이터베이스를 resetlogs를 이용해서 open 한 후에 과거 백업본은 사용할 수 없다. 현재 상태에서 일관성있는 백업과 일관성 없는 백업을 수행 해야한다. 과거의 아카이브 파일도 필요없다. SQL> ! rm /home/oracle/arch1/arch_1_54_1154915502.arc SQL> ! rm /home/oracle/arch2/arch_1_54_1154915502.arc SQL> ! ls /home/oracle/arch* /home/oracle/arch1: arch_1_1_1158372877.arc /home/oracle/arch2: arch_1_1_1158372877.arc |
|
| << 시나리오 12-2 >> 중간에 Undo Tablespace 수정 후 백업을 하지 않아 기존 백업파일과 SCN 싱크가 맞지 않다. | |
| ... # Data File Restore SQL> ! cp -av /home/oracle/backup/arch/cold_20240115/*.dbf /u01/app/oracle/oradata/ora11g/ SQL> STARTUP MOUNT SQL> RECOVER DATABASE UNTIL CANCEL; ORA-00283: recovery session canceled due to errors ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' ORA-01122: database file 3 failed verification check ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' ORA-01203: wrong incarnation of this file - wrong creation SCN >> 복원한 undotbs01.dbf 파일과 Control File 내 싱크가 맞지 않아 데이터파일만 복구 불가 >> 완전 망한거다. 과거로 완전 복구 SQL> SHUTDOWN ABORT # Restore : DataFile, ControlFile, RedoLogFile ! cp -av /home/oracle/backup/arch/cold_20240115/*.dbf /u01/app/oracle/oradata/ora11g/ ! cp -av /home/oracle/backup/arch/cold_20240115/*.ctl /u01/app/oracle/oradata/ora11g/ ! cp -av /home/oracle/backup/arch/cold_20240115/*.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 1147247 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1147247 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1147247 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1147247 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1147247 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 48 YES INACTIVE 1145387 15-JAN-24 1145390 15-JAN-24 1 49 YES INACTIVE 1145390 15-JAN-24 1146954 15-JAN-24 1 50 NO CURRENT 1146954 15-JAN-24 2.8147E+14 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_49_1154915502.arc 49 1145390 15-JAN-24 1146954 15-JAN-24 /home/oracle/arch2/arch_1_49_1154915502.arc 49 1145390 15-JAN-24 1146954 15-JAN-24 /home/oracle/arch1/arch_1_50_1154915502.arc 50 1146954 15-JAN-24 1155615 16-JAN-24 /home/oracle/arch2/arch_1_50_1154915502.arc 50 1146954 15-JAN-24 1155615 16-JAN-24 |
|
| << Backup >> 일관성 있는 Backup = Cold Backup = Offline Backup = Closed Backup | |
| # /home/oracle/backup/arch/cold_20240116 # seq#2 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 NO CURRENT 1159428 16-JAN-24 2.8147E+14 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 1162016 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1162016 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1162016 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1162016 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1162016 SQL> ! mkdir -p /home/oracle/backup/arch/cold_20240116 CREATE PFILE ='/home/oracle/backup/arch/cold_20240116/initora11g_20240116.ora' FROM SPFILE; SQL> SHUTDOWN IMMEDIATE ! cp -av /u01/app/oracle/oradata/ora11g/*.dbf /home/oracle/backup/arch/cold_20240116/ ! cp -av /u01/app/oracle/oradata/ora11g/*.ctl /home/oracle/backup/arch/cold_20240116/ ! cp -av /u01/app/oracle/oradata/ora11g/*.log /home/oracle/backup/arch/cold_20240116/ SQL> ! ls /home/oracle/backup/arch/cold_20240116 control01.ctl example01.dbf initora11g_20240116.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 1162499 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1162499 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1162499 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1162499 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1162499 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 NO CURRENT 1159428 16-JAN-24 2.8147E+14 1 0 YES UNUSED 0 0 |
|
| << Backup >> 일관성없는 백업 = Hot Backup = Open Backup = Online Backup | |
| # /home/oracle/backup/arch/hot_20240116/ # seq#2 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 1162499 NOT ACTIVE 0 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1162499 NOT ACTIVE 0 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 1162499 NOT ACTIVE 0 4 /u01/app/oracle/oradata/ora11g/users01.dbf 1162499 NOT ACTIVE 0 5 /u01/app/oracle/oradata/ora11g/example01.dbf 1162499 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 YES INACTIVE 1155932 16-JAN-24 1159428 16-JAN-24 1 2 NO CURRENT 1159428 16-JAN-24 2.8147E+14 1 0 YES UNUSED 0 0 SQL> ALTER SYSTEM CHECKPOINT; SQL> ALTER DATABASE BEGIN BACKUP; SQL> ! mkdir -p /home/oracle/backup/arch/hot_20240116 SQL> ! cp -av /u01/app/oracle/oradata/ora11g/*.dbf /home/oracle/backup/arch/hot_20240116/ SQL> ! ls /home/oracle/backup/arch/hot_20240116/ 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 1162853 NOT ACTIVE 1162853 16-JAN-24 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1162853 NOT ACTIVE 1162853 16-JAN-24 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 1162853 NOT ACTIVE 1162853 16-JAN-24 4 /u01/app/oracle/oradata/ora11g/users01.dbf 1162853 NOT ACTIVE 1162853 16-JAN-24 5 /u01/app/oracle/oradata/ora11g/example01.dbf 1162853 NOT ACTIVE 1162853 16-JAN-24 SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/home/oracle/backup/arch/hot_20240116/control01.ctl'; 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 NO CURRENT 1159428 16-JAN-24 2.8147E+14 1 0 YES UNUSED 0 0 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 INACTIVE 1155932 16-JAN-24 1159428 16-JAN-24 1 2 YES ACTIVE 1159428 16-JAN-24 1162976 16-JAN-24 1 3 NO CURRENT 1162976 16-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_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 |
|
SQL> ! ls -R /home/oracle/backup/arch
/home/oracle/backup/arch/cold_20240116:
control01.ctl example01.dbf initora11g_20240116.ora redo01.log redo02.log
redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
/home/oracle/backup/arch/hot_20240116:
control01.ctl example01.dbf sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
SQL> ! ls -R /home/oracle/arch*
/home/oracle/arch1:
arch_1_1_1158372877.arc arch_1_2_1158372877.arc
/home/oracle/arch2:
arch_1_1_1158372877.arc arch_1_2_1158372877.arc
>> seq#2
'Oracle 11g > Backup & Recovery' 카테고리의 다른 글
| 68 Backup&Recovery, Archive Log Mode, control file 손상 (0) | 2024.01.18 |
|---|---|
| 67 Backup&Recovery, Archive Log Mode, (0) | 2024.01.17 |
| 65 Backup&Recovery, Archive Log Mode (0) | 2024.01.15 |
| 64 Backup&Recovery, Archive Log Mode (0) | 2024.01.12 |
| 63 Backup&Recovery, Noarchive Log, Redo Log File, Temp File (0) | 2024.01.11 |