| # 요약 | |
| << 시나리오 4 >> 백업 받지않은 테이블스페이스에 데이터파일 손상 << 시나리오 5 >> 기존 위치가 아닌 새로운 위치로 복원 작업 수행 << 시나리오 6 >> 테이블스페이스에 속한 여러 데이터 파일들 중에 특정한 파일이 손상 << 시나리오 7 >> System Data File 손상 << 시나리오 8 >> 모든 데이터 파일 손상 : 백업, 리두, 아카이브 다 있다 << Cold Backup, Close Backup, Offline Backup, 일관성 백업 >> << Hot Backup, Open Backup, Online Backup, 일관성없는 백업 >> << 시나리오 9 >> 아카이브 파일이 다른 위치에 있을 경우 복구 |
|
| # 테이블스페이스 마지막 체크포인트 정보 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 2262003 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 2262003 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 2262003 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 2262003 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 2262003 # 백업 정보 SELECT a.file#, a.name, a.checkpoint_change#, b.status, b.change#, to_char(b.time,'yyyy-mm-dd hh24:mi:ss') 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 2262003 NOT ACTIVE 2203301 2024-01-12 14:07:23 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2262003 NOT ACTIVE 2203301 2024-01-12 14:07:23 4 /u01/app/oracle/oradata/ora11g/users01.dbf 2262003 NOT ACTIVE 2203301 2024-01-12 14:07:23 5 /u01/app/oracle/oradata/ora11g/example01.dbf 2262003 NOT ACTIVE 2203301 2024-01-12 14:07:23 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2262003 NOT ACTIVE 2203301 2024-01-12 14:07:23 SELECT * FROM v$log; GROUP# THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- --------- ---------------- ------------- ---------- ------------ --------- 1 1 7 YES INACTIVE 2248298 12-JAN-24 2262003 15-JAN-24 2 1 8 NO CURRENT 2262003 15-JAN-24 2.8147E+14 3 1 6 YES INACTIVE 2227527 12-JAN-24 2248298 12-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 ---------- -------------------------------------------------- ------------- ----------- ------------ --------- 3 /home/oracle/arch1/arch_1_3_1157971650.arc 2075466 24/01/12 2082030 24/01/12 3 /home/oracle/arch2/arch_1_3_1157971650.arc 2075466 24/01/12 2082030 24/01/12 4 /home/oracle/arch1/arch_1_4_1157971650.arc 2082030 24/01/12 2205455 24/01/12 4 /home/oracle/arch2/arch_1_4_1157971650.arc 2082030 24/01/12 2205455 24/01/12 5 /home/oracle/arch1/arch_1_5_1157971650.arc 2205455 24/01/12 2227527 24/01/12 5 /home/oracle/arch2/arch_1_5_1157971650.arc 2205455 24/01/12 2227527 24/01/12 ... 백업파일시점 seq$4 [oracle@oracle arch]$ ls /home/oracle/arch* /home/oracle/arch1: arch_1_2_1157971650.arc arch_1_3_1157971650.arc arch_1_4_1157971650.arc arch_1_5_1157971650.arc arch_1_6_1157971650.arc arch_1_7_1157971650.arc /home/oracle/arch2: arch_1_2_1157971650.arc arch_1_3_1157971650.arc arch_1_4_1157971650.arc arch_1_5_1157971650.arc arch_1_6_1157971650.arc arch_1_7_1157971650.arc |
|
| << 시나리오 4 >> 백업 받지않은 테이블스페이스에 데이터파일 손상 | |
CREATE TABLESPACE data01 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 2262003 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 2262003 3 /u01/app/oracle/oradata/ora11g/data01.dbf DATA01 ONLINE 2272201 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 2262003 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 2262003 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 2262003 # 백업 정보 SELECT a.file#, a.name, a.checkpoint_change#, b.status, b.change#, to_char(b.time,'yyyy-mm-dd hh24:mi:ss') 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 2262003 NOT ACTIVE 2203301 2024-01-12 14:07:23 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2262003 NOT ACTIVE 2203301 2024-01-12 14:07:23 3 /u01/app/oracle/oradata/ora11g/data01.dbf 2272201 NOT ACTIVE 0 4 /u01/app/oracle/oradata/ora11g/users01.dbf 2262003 NOT ACTIVE 2203301 2024-01-12 14:07:23 5 /u01/app/oracle/oradata/ora11g/example01.dbf 2262003 NOT ACTIVE 2203301 2024-01-12 14:07:23 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2262003 NOT ACTIVE 2203301 2024-01-12 14:07:23 CREATE TABLE hr.dept_temp TABLESPACE data01 AS SELECT * FROM hr.departments; SQL> SELECT COUNT(*) FROM hr.dept_temp; COUNT(*) ---------- 27 SQL> SELECT * FROM v$log; GROUP# THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- --------- ---------------- ------------- ----------- ------------ --------- 1 1 7 YES INACTIVE 2248298 12-JAN-24 2262003 15-JAN-24 2 1 8 NO CURRENT 2262003 15-JAN-24 2.8147E+14 3 1 6 YES INACTIVE 2227527 12-JAN-24 2248298 12-JAN-24 sep#8 ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SWITCH LOGFILE; SQL> SELECT * FROM v$log; GROUP# THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- --------- ---------------- ------------- ----------- ------------ --------- 1 1 10 YES INACTIVE 2272695 15-JAN-24 2272702 15-JAN-24 2 1 11 NO CURRENT 2272702 15-JAN-24 2.8147E+14 3 1 9 YES INACTIVE 2272687 15-JAN-24 2272695 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 ---------- -------------------------------------------------- ------------- ----------- ------------ --------- ... 8 /home/oracle/arch1/arch_1_8_1157971650.arc 2262003 24/01/15 2272687 24/01/15 8 /home/oracle/arch2/arch_1_8_1157971650.arc 2262003 24/01/15 2272687 24/01/15 9 /home/oracle/arch1/arch_1_9_1157971650.arc 2272687 24/01/15 2272695 24/01/15 9 /home/oracle/arch2/arch_1_9_1157971650.arc 2272687 24/01/15 2272695 24/01/15 10 /home/oracle/arch1/arch_1_10_1157971650.arc 2272695 24/01/15 2272702 24/01/15 10 /home/oracle/arch2/arch_1_10_1157971650.arc 2272695 24/01/15 2272702 24/01/15 ... 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 = 'DEPT_TEMP' AND e.owner = 'HR'; TABLESPACE_NAME FILE_NAME ------------------------------ -------------------------------------------------- DATA01 /u01/app/oracle/oradata/ora11g/data01.dbf # 장애 발생 SQL> ! rm /u01/app/oracle/oradata/ora11g/data01.dbf SQL> SHUTDOWN IMMEDIATE ORA-03113: end-of-file on communication channel Process ID: 20003 Session ID: 187 Serial number: 13 SQL> conn / as sysdba 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/data01.dbf' >> 1개만 노출되기에 꼭 v$recover_file 를 확인하자 SQL> SELECT * FROM v$recover_file; FILE# ONLINE ONLINE_ ERROR CHANGE# TIME ---------- ------- ------- ------------------------------------------------------- ---------- --------- 3 ONLINE ONLINE FILE NOT FOUND 0 SQL> SELECT file#, name, status FROM v$datafile; FILE# NAME STATUS ---------- -------------------------------------------------- ------- 1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 3 /u01/app/oracle/oradata/ora11g/data01.dbf ONLINE 4 /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 5 /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE SQL> ALTER DATABASE DATAFILE 3 OFFLINE; SQL> SELECT file#, name, status FROM v$datafile; FILE# NAME STATUS ---------- -------------------------------------------------- ------- 1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 3 /u01/app/oracle/oradata/ora11g/data01.dbf RECOVER 4 /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 5 /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE SQL> ALTER DATABASE OPEN; 다른 tbs 오류 없는지 체크 SQL> SELECT COUNT(*) FROM hr.employees; COUNT(*) ---------- 106 백업정보 체크 SELECT a.file#, a.name, a.checkpoint_change#, b.status, b.change#, to_char(b.time,'yyyy-mm-dd hh24:mi:ss') 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 2293413 NOT ACTIVE 2203301 2024-01-12 14:07:23 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2293413 NOT ACTIVE 2203301 2024-01-12 14:07:23 4 /u01/app/oracle/oradata/ora11g/users01.dbf 2293413 NOT ACTIVE 2203301 2024-01-12 14:07:23 5 /u01/app/oracle/oradata/ora11g/example01.dbf 2293413 NOT ACTIVE 2203301 2024-01-12 14:07:23 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2293413 NOT ACTIVE 2203301 2024-01-12 14:07:23 >> 오프라인으로 되어있어 보이지 않고 백업도 없다. ** 테이블스페이스 생성 당시, 테이블 생성 당시 redo log 실시간으로 체크하자 # 복구작업 수행해야할 기존 디렉터리에 데이터파일 재생성 ALTER DATABASE CREATE DATAFILE '/u01/app/oracle/oradata/ora11g/data01.dbf'; 참고) # 기존 위치가 아닌 새로운 위치에 데이터파일 재생성할 경우 ALTER DATABASE CREATE DATAFILE '/u01/app/oracle/oradata/ora11g/data01.dbf' AS '/home/oracle/data01.dbf'; 기존 to 새위치 생성된 파일 확인 SQL> ! ls -l /u01/app/oracle/oradata/ora11g/data01.dbf -rw-r-----. 1 oracle oinstall 5251072 Jan 15 10:29 /u01/app/oracle/oradata/ora11g/data01.dbf 5251072 5M로 자동생성 -> 딕셔너리+컨트롤파일의 정보로 재생성하기 때문에 사이즈도 그대로 생성됨, 단 내용이 없다. !! 아카이브, 리두 체크 >> 없다? DROP. 복구 불가 깡통파일에 리두 적용 SQL> RECOVER TABLESPACE data01; ORA-00279: change 2272200 generated at 01/15/2024 10:04:06 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch2/arch_1_8_1157971650.arc ORA-00280: change 2272200 for thread 1 is in sequence #8 Specify log: {=suggested | filename | AUTO | CANCEL} AUTO ORA-00279: change 2272687 generated at 01/15/2024 10:09:01 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch2/arch_1_9_1157971650.arc ORA-00280: change 2272687 for thread 1 is in sequence #9 Log applied. Media recovery complete. SQL> ALTER TABLESPACE data01 ONLINE; SQL> SELECT file#, name, status FROM v$datafile; FILE# NAME STATUS ---------- -------------------------------------------------- ------- 1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 3 /u01/app/oracle/oradata/ora11g/data01.dbf ONLINE 4 /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 5 /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE SQL> SELECT COUNT(*) FROM hr.dept_temp; COUNT(*) ---------- 27 |
|
| << 시나리오 5 >> 기존 위치가 아닌 새로운 위치로 복원 작업 수행 | |
SQL> CREATE TABLE hr.emp_temp TABLESPACE data01 AS SELECT * FROM hr.employees; SQL> SELECT COUNT(*) FROM hr.emp_temp; COUNT(*) ---------- 106 SQL> SELECT * FROM v$log; GROUP# THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- --------- ---------------- ------------- ----------- ------------ --------- 1 1 10 YES INACTIVE 2272695 15-JAN-24 2272702 15-JAN-24 2 1 11 YES INACTIVE 2272702 15-JAN-24 2293412 15-JAN-24 3 1 12 NO CURRENT 2293412 15-JAN-24 2.8147E+14 SELECT a.file#, a.name, a.checkpoint_change#, b.status, b.change#, to_char(b.time,'yyyy-mm-dd hh24:mi:ss') 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 2293413 NOT ACTIVE 2203301 2024-01-12 14:07:23 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2293413 NOT ACTIVE 2203301 2024-01-12 14:07:23 3 /u01/app/oracle/oradata/ora11g/data01.dbf 2294255 NOT ACTIVE 0 4 /u01/app/oracle/oradata/ora11g/users01.dbf 2293413 NOT ACTIVE 2203301 2024-01-12 14:07:23 5 /u01/app/oracle/oradata/ora11g/example01.dbf 2293413 NOT ACTIVE 2203301 2024-01-12 14:07:23 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2293413 NOT ACTIVE 2203301 2024-01-12 14:07:23 # data01 tablespace를 hot backup 받자 테이블스페이스 레벨로 백업 SQL> ALTER TABLESPACE data01 BEGIN BACKUP; SELECT a.file#, a.name, a.checkpoint_change#, b.status, b.change#, to_char(b.time,'yyyy-mm-dd hh24:mi:ss') 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 2293413 NOT ACTIVE 2203301 2024-01-12 14:07:23 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2293413 NOT ACTIVE 2203301 2024-01-12 14:07:23 3 /u01/app/oracle/oradata/ora11g/data01.dbf 2295964 ACTIVE 2295964 2024-01-15 11:06:50 4 /u01/app/oracle/oradata/ora11g/users01.dbf 2293413 NOT ACTIVE 2203301 2024-01-12 14:07:23 5 /u01/app/oracle/oradata/ora11g/example01.dbf 2293413 NOT ACTIVE 2203301 2024-01-12 14:07:23 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2293413 NOT ACTIVE 2203301 2024-01-12 14:07:23 [oracle@oracle ~]$ cd /home/oracle/backup/arch/hot_20240112 SQL> ! cp -av /u01/app/oracle/oradata/ora11g/data01.dbf /home/oracle/backup/arch/hot_20240112 SQL> ! ls /home/oracle/backup/arch/hot_20240112 backup.log backup.sh control01.ctl data01.dbf example01.dbf sysaux01.dbf system01.dbf temp_new01.dbf undotbs01.dbf users01.dbf SQL> ALTER TABLESPACE data01 END BACKUP; >> begin ~ end backup 빠르게 진행하자. redo log 쌓임 방지 SELECT a.file#, a.name, a.checkpoint_change#, b.status, b.change#, to_char(b.time,'yyyy-mm-dd hh24:mi:ss') 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 2293413 NOT ACTIVE 2203301 2024-01-12 14:07:23 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2293413 NOT ACTIVE 2203301 2024-01-12 14:07:23 3 /u01/app/oracle/oradata/ora11g/data01.dbf 2295964 NOT ACTIVE 2295964 2024-01-15 11:06:50 4 /u01/app/oracle/oradata/ora11g/users01.dbf 2293413 NOT ACTIVE 2203301 2024-01-12 14:07:23 5 /u01/app/oracle/oradata/ora11g/example01.dbf 2293413 NOT ACTIVE 2203301 2024-01-12 14:07:23 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2293413 NOT ACTIVE 2203301 2024-01-12 14:07:23 SQL> SELECT * FROM v$log; GROUP# THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- --------- ---------------- ------------- ----------- ------------ --------- 1 1 10 YES INACTIVE 2272695 15-JAN-24 2272702 15-JAN-24 2 1 11 YES INACTIVE 2272702 15-JAN-24 2293412 15-JAN-24 3 1 12 NO CURRENT 2293412 15-JAN-24 2.8147E+14 seq#12 SQL> CREATE TABLE hr.loc_temp TABLESPACE data01 AS SELECT * FROM hr.locations; SQL> SELECT COUNT(*) FROM hr.loc_temp; COUNT(*) ---------- 23 alter system switch logfile; alter system switch logfile; alter system switch logfile; SQL> SELECT * FROM v$log; GROUP# THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- --------- ---------------- ------------- ----------- ------------ --------- 1 1 13 YES INACTIVE 2296184 15-JAN-24 2296187 15-JAN-24 2 1 14 YES INACTIVE 2296187 15-JAN-24 2296193 15-JAN-24 3 1 15 NO CURRENT 2296193 15-JAN-24 2.8147E+14 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 ---------- -------------------------------------------------- ------------- ----------- ------------ --------- ... 11 /home/oracle/arch1/arch_1_11_1157971650.arc 2272702 24/01/15 2293412 24/01/15 11 /home/oracle/arch2/arch_1_11_1157971650.arc 2272702 24/01/15 2293412 24/01/15 12 /home/oracle/arch1/arch_1_12_1157971650.arc 2293412 24/01/15 2296184 24/01/15 12 /home/oracle/arch2/arch_1_12_1157971650.arc 2293412 24/01/15 2296184 24/01/15 13 /home/oracle/arch1/arch_1_13_1157971650.arc 2296184 24/01/15 2296187 24/01/15 13 /home/oracle/arch2/arch_1_13_1157971650.arc 2296184 24/01/15 2296187 24/01/15 ... 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 = 'LOC_TEMP' AND e.owner = 'HR'; TABLESPACE_NAME FILE_NAME ------------------------------ -------------------------------------------------- DATA01 /u01/app/oracle/oradata/ora11g/data01.dbf # 장애 발생 SQL> ! rm /u01/app/oracle/oradata/ora11g/data01.dbf 즉시 offline SQL> ALTER TABLESPACE data01 OFFLINE IMMEDIATE; SQL> SELECT file#, name, status FROM v$datafile; FILE# NAME STATUS ---------- -------------------------------------------------- ------- 1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 3 /u01/app/oracle/oradata/ora11g/data01.dbf RECOVER 4 /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 5 /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE 1. 백업파일을 새로운 위치에 Restore SQL> ! cp -av /home/oracle/backup/arch/hot_20240112/data01.dbf /home/oracle ‘/home/oracle/backup/arch/hot_20240112/data01.dbf’ -> ‘/home/oracle/data01.dbf’ 2. control file에 위치 변경 작업 ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/data01.dbf' TO '/home/oracle/data01.dbf'; SQL> SELECT file#, name, status FROM v$datafile; FILE# NAME STATUS ---------- -------------------------------------------------- ------- 1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 3 /home/oracle/data01.dbf RECOVER 4 /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 5 /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE 3. 복구 작업 SQL> RECOVER TABLESPACE data01; ORA-00279: change 2295964 generated at 01/15/2024 11:06:50 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch2/arch_1_12_1157971650.arc ORA-00280: change 2295964 for thread 1 is in sequence #12 Specify log: {=suggested | filename | AUTO | CANCEL} AUTO Log applied. Media recovery complete. 4. 테이블스페이스를 online SQL> ALTER TABLESPACE data01 ONLINE; SQL> SELECT file#, name, status FROM v$datafile; FILE# NAME STATUS ---------- -------------------------------------------------- ------- 1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 3 /home/oracle/data01.dbf ONLINE 4 /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 5 /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE SQL> SELECT COUNT(*) FROM hr.loc_temp; COUNT(*) ---------- 23 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 = 'LOC_TEMP' AND e.owner = 'HR'; TABLESPACE_NAME FILE_NAME ------------------------------ -------------------------------------------------- DATA01 /home/oracle/data01.dbf |
|
| << 시나리오 6 >> 테이블스페이스에 속한 여러 데이터 파일들 중에 특정한 파일이 손상 | |
| SQL> SELECT tablespace_name, file_name FROM dba_data_files; TABLESPACE_NAME FILE_NAME ------------------------------ -------------------------------------------------- DATA01 /home/oracle/data01.dbf UNDOTBS /u01/app/oracle/oradata/ora11g/undotbs01.dbf USERS /u01/app/oracle/oradata/ora11g/users01.dbf SYSAUX /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSTEM /u01/app/oracle/oradata/ora11g/system01.dbf EXAMPLE /u01/app/oracle/oradata/ora11g/example01.dbf # 테이블 스페이스 데이터파일 추가 SQL> ALTER TABLESPACE data01 ADD DATAFILE '/u01/app/oracle/oradata/ora11g/data02.dbf' SIZE 10M; SQL> SELECT tablespace_name, file_name FROM dba_data_files; TABLESPACE_NAME FILE_NAME ------------------------------ -------------------------------------------------- DATA01 /home/oracle/data01.dbf UNDOTBS /u01/app/oracle/oradata/ora11g/undotbs01.dbf USERS /u01/app/oracle/oradata/ora11g/users01.dbf SYSAUX /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSTEM /u01/app/oracle/oradata/ora11g/system01.dbf DATA01 /u01/app/oracle/oradata/ora11g/data02.dbf EXAMPLE /u01/app/oracle/oradata/ora11g/example01.dbf 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 = 'LOC_TEMP' AND e.owner = 'HR'; TABLESPACE_NAME FILE_NAME ------------------------------ -------------------------------------------------- DATA01 /home/oracle/data01.dbf SQL> CREATE TABLE hr.emp_2024 TABLESPACE data01 AS SELECT * FROM hr.employees; 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 = 'EMP_2024' AND e.owner = 'HR'; TABLESPACE_NAME FILE_NAME ------------------------------ -------------------------------------------------- DATA01 /home/oracle/data01.dbf SQL> INSERT INTO hr.emp_2024 SELECT * FROM hr.emp_2024; SQL> INSERT INTO hr.emp_2024 SELECT * FROM hr.emp_2024; SQL> INSERT INTO hr.emp_2024 SELECT * FROM hr.emp_2024; SQL> INSERT INTO hr.emp_2024 SELECT * FROM hr.emp_2024; SQL> INSERT INTO hr.emp_2024 SELECT * FROM hr.emp_2024; ... 대용량으로 만들기 SQL> INSERT INTO hr.emp_2024 SELECT * FROM hr.emp_2024; ORA-01653: unable to extend table HR.EMP_2024 by 128 in tablespace DATA01 SQL> COMMIT; SQL> SELECT bytes FROM dba_segments WHERE segment_name = 'EMP_2024' AND owner = 'HR'; BYTES ---------- 12582912 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.segment_name = 'EMP_2024' AND e.owner = 'HR' GROUP BY f.tablespace_name, f.file_name; TABLESPACE_NAME FILE_NAME COUNT(*) <- extents 개수 ------------------------------ -------------------------------------------------- ---------- DATA01 /u01/app/oracle/oradata/ora11g/data02.dbf 11 DATA01 /home/oracle/data01.dbf 16 SELECT * FROM dba_extents WHERE segment_name = 'EMP_2024' AND owner = 'HR'; SELECT a.file#, a.name, a.checkpoint_change#, b.status, b.change#, to_char(b.time,'yyyy-mm-dd hh24:mi:ss') 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 2296193 NOT ACTIVE 2203301 2024-01-12 14:07:23 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2296193 NOT ACTIVE 2203301 2024-01-12 14:07:23 3 /home/oracle/data01.dbf 2296814 NOT ACTIVE 2295964 2024-01-15 11:06:50 4 /u01/app/oracle/oradata/ora11g/users01.dbf 2296193 NOT ACTIVE 2203301 2024-01-12 14:07:23 5 /u01/app/oracle/oradata/ora11g/example01.dbf 2296193 NOT ACTIVE 2203301 2024-01-12 14:07:23 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2296193 NOT ACTIVE 2203301 2024-01-12 14:07:23 7 /u01/app/oracle/oradata/ora11g/data02.dbf 2298445 NOT ACTIVE 0 # Online Backup SQL> ALTER TABLESPACE data01 BEGIN BACKUP; SQL> ! cp -av /u01/app/oracle/oradata/ora11g/data02.dbf /home/oracle/backup/arch/hot_20240112/data02.dbf SQL> ! ls /home/oracle/backup/arch/hot_20240112 backup.log backup.sh control01.ctl data01.dbf data02.dbf example01.dbf sysaux01.dbf system01.dbf temp_new01.dbf undotbs01.dbf users01.dbf SQL> ALTER TABLESPACE data01 END BACKUP; SELECT a.file#, a.name, a.checkpoint_change#, b.status, b.change#, to_char(b.time,'yyyy-mm-dd hh24:mi:ss') 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 2296193 NOT ACTIVE 2203301 2024-01-12 14:07:23 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2296193 NOT ACTIVE 2203301 2024-01-12 14:07:23 3 /home/oracle/data01.dbf 2299433 NOT ACTIVE 2299433 2024-01-15 12:13:29 4 /u01/app/oracle/oradata/ora11g/users01.dbf 2296193 NOT ACTIVE 2203301 2024-01-12 14:07:23 5 /u01/app/oracle/oradata/ora11g/example01.dbf 2296193 NOT ACTIVE 2203301 2024-01-12 14:07:23 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2296193 NOT ACTIVE 2203301 2024-01-12 14:07:23 7 /u01/app/oracle/oradata/ora11g/data02.dbf 2299433 NOT ACTIVE 2299433 2024-01-15 12:13:29 SQL> SELECT * FROM v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --------- ---------------- ------------- ----------- ------------ --------- 1 1 13 52428800 512 1 YES INACTIVE 2296184 15-JAN-24 2296187 15-JAN-24 2 1 14 52428800 512 1 YES INACTIVE 2296187 15-JAN-24 2296193 15-JAN-24 3 1 15 52428800 512 1 NO CURRENT 2296193 15-JAN-24 2.8147E+14 seq#15 SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> SELECT * FROM v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --------- ---------------- ------------- ----------- ------------ --------- 1 1 16 52428800 512 1 YES INACTIVE 2299923 15-JAN-24 2299926 15-JAN-24 2 1 17 52428800 512 1 YES INACTIVE 2299926 15-JAN-24 2299929 15-JAN-24 3 1 18 52428800 512 1 NO CURRENT 2299929 15-JAN-24 2.8147E+14 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 ---------- -------------------------------------------------- ------------- ----------- ------------ --------- ... 15 /home/oracle/arch1/arch_1_15_1157971650.arc 2296193 15-JAN-24 2299923 15-JAN-24 15 /home/oracle/arch2/arch_1_15_1157971650.arc 2296193 15-JAN-24 2299923 15-JAN-24 ... SQL> CREATE TABLE hr.dept_2024 TABLESPACE data01 AS SELECT * FROM hr.departments; 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.segment_name = 'DEPT_2024' AND e.owner = 'HR' GROUP BY f.tablespace_name, f.file_name; TABLESPACE_NAME FILE_NAME COUNT(*) ------------------------------ -------------------------------------------------- ---------- DATA01 /u01/app/oracle/oradata/ora11g/data02.dbf 1 # 장애 발생 - 파일을 잘못지웠다 SQL> ! rm /u01/app/oracle/oradata/ora11g/data02.dbf SQL> ! ls /u01/app/oracle/oradata/ora11g/data02.dbf ls: cannot access /u01/app/oracle/oradata/ora11g/data02.dbf: No such file or directory 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 2299929 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 2299929 3 /home/oracle/data01.dbf DATA01 ONLINE 2299929 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 2299929 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 2299929 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 2299929 7 /u01/app/oracle/oradata/ora11g/data02.dbf DATA01 ONLINE 2299929 # 테이블스페이스에 속한 데이터파일을offline으로 수행하되 가능한 데이터파일은 체크포인트 발생하고 가능하지 않은 데이터파일은 그냥 offline으로 수행 SQL> ALTER TABLESPACE data01 OFFLINE TEMPORARY; 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 2299929 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 2299929 3 /home/oracle/data01.dbf DATA01 OFFLINE 2307654 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 2299929 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 2299929 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 2299929 7 /u01/app/oracle/oradata/ora11g/data02.dbf DATA01 OFFLINE 2307654 # Restore SQL> ! cp -av /home/oracle/backup/arch/hot_20240112/data02.dbf /u01/app/oracle/oradata/ora11g/data02.dbf # Recovery SQL> RECOVER TABLESPACE data01; ORA-00279: change 2299433 generated at 01/15/2024 12:13:29 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch2/arch_1_15_1157971650.arc ORA-00280: change 2299433 for thread 1 is in sequence #15 Specify log: {=suggested | filename | AUTO | CANCEL} AUTO Log applied. Media recovery complete. # Online SQL> ALTER TABLESPACE data01 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 2299929 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 2299929 3 /home/oracle/data01.dbf DATA01 ONLINE 2308053 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 2299929 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 2299929 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 2299929 7 /u01/app/oracle/oradata/ora11g/data02.dbf DATA01 ONLINE 2308053 SQL> SELECT COUNT(*) FROM hr.dept_2024; COUNT(*) ---------- 27 |
|
| << 시나리오 7 >> System Data File 손상 | |
| SELECT a.file#, a.name, a.checkpoint_change#, b.status, b.change#, to_char(b.time,'yyyy-mm-dd hh24:mi:ss') 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 2299929 NOT ACTIVE 2203301 2024-01-12 14:07:23 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2299929 NOT ACTIVE 2203301 2024-01-12 14:07:23 3 /home/oracle/data01.dbf 2308053 NOT ACTIVE 2299433 2024-01-15 12:13:29 4 /u01/app/oracle/oradata/ora11g/users01.dbf 2299929 NOT ACTIVE 2203301 2024-01-12 14:07:23 5 /u01/app/oracle/oradata/ora11g/example01.dbf 2299929 NOT ACTIVE 2203301 2024-01-12 14:07:23 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2299929 NOT ACTIVE 2203301 2024-01-12 14:07:23 7 /u01/app/oracle/oradata/ora11g/data02.dbf 2308053 NOT ACTIVE 2299433 2024-01-15 12:13:29 SQL> ! ls /home/oracle/backup/arch/hot_20240112 backup.log backup.sh control01.ctl data01.dbf data02.dbf example01.dbf sysaux01.dbf system01.dbf temp_new01.dbf undotbs01.dbf users01.dbf # 장애 발생 SQL> ! rm /u01/app/oracle/oradata/ora11g/system01.dbf 모르고 DB내림 SQL> SHUTDOWN IMMEDIATE SQL> STARTUP Database mounted. ORA-01157: cannot identify/lock data file 1 - see DBWR trace file ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf' SQL> SELECT * FROM v$recover_file; FILE# ONLINE ONLINE_ ERROR CHANGE# TIME ---------- ------- ------- ----------------------------------------------------------------- ---------- --------- 1 ONLINE ONLINE FILE NOT FOUND 0 # Restore SQL> ! cp -av /home/oracle/backup/arch/hot_20240112/system01.dbf /u01/app/oracle/oradata/ora11g/ # Recovery SQL> RECOVER TABLESPACE SYSTEM; ORA-00279: change 2203301 generated at 01/12/2024 14:07:23 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch2/arch_1_4_1157971650.arc ORA-00280: change 2203301 for thread 1 is in sequence #4 Specify log: {=suggested | filename | AUTO | CANCEL} AUTO ORA-00279: change 2205455 generated at 01/12/2024 14:28:07 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch2/arch_1_5_1157971650.arc ORA-00280: change 2205455 for thread 1 is in sequence #5 ... 하나라도 없으면 복구 불가 ORA-00279: change 2296193 generated at 01/15/2024 11:14:22 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch2/arch_1_15_1157971650.arc ORA-00280: change 2296193 for thread 1 is in sequence #15 Log applied. Media recovery complete. # Open SQL> ALTER DATABASE OPEN; SQL> SELECT * FROM v$recover_file; no rows selected 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 2308961 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 2308961 3 /home/oracle/data01.dbf DATA01 ONLINE 2308961 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 2308961 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 2308961 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 2308961 7 /u01/app/oracle/oradata/ora11g/data02.dbf DATA01 ONLINE 2308961 |
|
| << 시나리오 8 >> 모든 데이터 파일 손상 : 백업, 리두, 아카이브 다 있다 | |
| # 장애 발생 SQL> ! rm /u01/app/oracle/oradata/ora11g/*.dbf SQL> ALTER SYSTEM CHECKPOINT; ORA-03113: end-of-file on communication channel Process ID: 26199 Session ID: 9 Serial number: 3 SQL> conn / as sysdba Connected to an idle instance. SQL> STARTUP Database mounted. ORA-01157: cannot identify/lock data file 1 - see DBWR trace file ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf' SQL> SELECT status FROM v$instance; STATUS ------------ MOUNTED -> v$~ control file에 정보가 있구나 SQL> SELECT * FROM v$recover_file; FILE# ONLINE ONLINE_ ERROR CHANGE# TIME ---------- ------- ------- ----------------------------------------------------------------- ---------- --------- 1 ONLINE ONLINE FILE NOT FOUND 0 2 ONLINE ONLINE FILE NOT FOUND 0 4 ONLINE ONLINE FILE NOT FOUND 0 5 ONLINE ONLINE FILE NOT FOUND 0 6 ONLINE ONLINE FILE NOT FOUND 0 7 ONLINE ONLINE 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 2308961 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 2308961 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 2308961 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 2308961 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 2308961 7 /u01/app/oracle/oradata/ora11g/data02.dbf DATA01 ONLINE 2308961 3 /home/oracle/data01.dbf DATA01 ONLINE 2308961 # 백업파일 체크 SQL> ! ls /home/oracle/backup/arch/hot_20240112 backup.log backup.sh control01.ctl data01.dbf data02.dbf example01.dbf sysaux01.dbf system01.dbf temp_new01.dbf undotbs01.dbf users01.dbf # Restore SQL> ! cp -av /home/oracle/backup/arch/hot_20240112/*.dbf /u01/app/oracle/oradata/ora11g/ SQL> ! ls /u01/app/oracle/oradata/ora11g/ control01.ctl data01.dbf data02.dbf example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp_new01.dbf undotbs01.dbf users01.dbf # Recovery SQL> RECOVER DATABASE; ORA-00279: change 2203301 generated at 01/12/2024 14:07:23 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch2/arch_1_4_1157971650.arc ORA-00280: change 2203301 for thread 1 is in sequence #4 Specify log: {=suggested | filename | AUTO | CANCEL} AUTO ORA-00279: change 2205455 generated at 01/12/2024 14:28:07 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch2/arch_1_5_1157971650.arc ORA-00280: change 2205455 for thread 1 is in sequence #5 ... ORA-00279: change 2296193 generated at 01/15/2024 11:14:22 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch2/arch_1_15_1157971650.arc ORA-00280: change 2296193 for thread 1 is in sequence #15 Log applied. Media recovery complete. # Open SQL> ALTER DATABASE OPEN; SQL> SELECT * FROM v$recover_file; no rows selected 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 2329654 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 2329654 3 /home/oracle/data01.dbf DATA01 ONLINE 2329654 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 2329654 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 2329654 6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 2329654 7 /u01/app/oracle/oradata/ora11g/data02.dbf DATA01 ONLINE 2329654 SQL> DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES; >> 스토리지의 구조가 변경되었으니 백업하자 |
|
============================================================================================
OS 고장 - VM - OS copy 본으로 변경
============================================================================================
| << Cold Backup, Close Backup, Offline 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 1147046 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1147046 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1147046 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1147046 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1147046 SQL> SELECT name,status FROM v$controlfile; NAME -------------------------------------------------- /u01/app/oracle/oradata/ora11g/control01.ctl SQL> SELECT name,status,enabled FROM v$tempfile; NAME STATUS ENABLED -------------------------------------------------- ----------- ----------- /u01/app/oracle/oradata/ora11g/temp01.dbf ONLINE READ WRITE SELECT a.group#, 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,2; GROUP# MEMBER MB ARCHIVED STATUS ---------- -------------------------------------------------- ---------- --------- ----------- 1 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT SQL> SELECT * FROM v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --------- ----------- ------------- ----------- ------------ --------- 1 1 48 52428800 512 1 YES INACTIVE 1145387 15-JAN-24 1145390 15-JAN-24 2 1 49 52428800 512 1 YES INACTIVE 1145390 15-JAN-24 1146954 15-JAN-24 3 1 50 52428800 512 1 NO CURRENT 1146954 15-JAN-24 2.8147E+14 seq#50 SQL> SHUTDOWN IMMEDIATE sh 사용하자 SQL> ! mkdir -p /home/oracle/backup/arch/cold_20240115 SQL> ! ls /home/oracle/backup/arch SQL> ! cp -av /u01/app/oracle/oradata/ora11g/*.* /home/oracle/backup/arch/cold_20240115 SQL> ! ls /home/oracle/backup/arch/cold_20240115 control01.ctl example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf SQL> STARTUP SQL> SELECT * FROM v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --------- ----------- ------------- ----------- ------------ --------- 1 1 48 52428800 512 1 YES INACTIVE 1145387 15-JAN-24 1145390 15-JAN-24 2 1 49 52428800 512 1 YES INACTIVE 1145390 15-JAN-24 1146954 15-JAN-24 3 1 50 52428800 512 1 NO CURRENT 1146954 15-JAN-24 2.8147E+14 SQL> CREATE PFILE ='/home/oracle/backup/arch/cold_20240115/initora11g_20240115.ora' FROM SPFILE; # 정책상 필요없는 과거 백업본 지우는 작업 SQL> ! rm -r /home/oracle/backup/arch/cold_20240112 # 아카이브 파일 삭제 : 백업파일 seq#50 기준 이전자료 ! ls /home/oracle/arch* |
|
| << Hot Backup, Open Backup, Online Backup, 일관성없는 백업 >> | |
| SELECT a.file#, a.name, a.checkpoint_change#, b.status, b.change#, to_char(b.time,'yyyy-mm-dd hh24:mi:ss') 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 1147690 NOT ACTIVE 0 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1147690 NOT ACTIVE 0 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 1147690 NOT ACTIVE 0 4 /u01/app/oracle/oradata/ora11g/users01.dbf 1147690 NOT ACTIVE 0 5 /u01/app/oracle/oradata/ora11g/example01.dbf 1147690 NOT ACTIVE 0 SQL> SELECT * FROM v$tempfile; FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME ---------- ---------------- --------- ---------- ---------- ----------- ----------- ---------- ---------- ------------ ---------- -------------------------------------------------- 1 635086 07-DEC-23 3 1 ONLINE READ WRITE 20971520 2560 20971520 8192 /u01/app/oracle/oradata/ora11g/temp01.dbf SQL> SELECT * FROM v$controlfile; STATUS NAME IS_RECOVERY_DEST_FILE BLOCK_SIZE FILE_SIZE_BLKS ----------- -------------------------------------------------- ---------------------- ---------- -------------- /u01/app/oracle/oradata/ora11g/control01.ctl NO 16384 594 SQL> SELECT * FROM v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --------- ----------- ------------- ----------- ------------ --------- 1 1 48 52428800 512 1 YES INACTIVE 1145387 15-JAN-24 1145390 15-JAN-24 2 1 49 52428800 512 1 YES INACTIVE 1145390 15-JAN-24 1146954 15-JAN-24 3 1 50 52428800 512 1 NO CURRENT 1146954 15-JAN-24 2.8147E+14 seq#50 SQL> ALTER SYSTEM CHECKPOINT; SELECT 'cp -av '||name||' /home/oracle/backup/arch/hot_20240115' FROM v$datafile UNION ALL SELECT 'cp -av '||name||' /home/oracle/backup/arch/hot_20240115' FROM v$tempfile; SQL> ALTER DATABASE BEGIN BACKUP; SELECT a.file#, a.name, a.checkpoint_change#, b.status, b.change#, to_char(b.time,'yyyy-mm-dd hh24:mi:ss') 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 1147766 ACTIVE 1147766 2024-01-15 04:27:12 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1147766 ACTIVE 1147766 2024-01-15 04:27:12 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 1147766 ACTIVE 1147766 2024-01-15 04:27:12 4 /u01/app/oracle/oradata/ora11g/users01.dbf 1147766 ACTIVE 1147766 2024-01-15 04:27:12 5 /u01/app/oracle/oradata/ora11g/example01.dbf 1147766 ACTIVE 1147766 2024-01-15 04:27:12 SQL> ! mkdir -p /home/oracle/backup/arch/hot_20240115 # cp Data File, Temp File ! cp -av /u01/app/oracle/oradata/ora11g/system01.dbf /home/oracle/backup/arch/hot_20240115/ ! cp -av /u01/app/oracle/oradata/ora11g/sysaux01.dbf /home/oracle/backup/arch/hot_20240115/ ! cp -av /u01/app/oracle/oradata/ora11g/undotbs01.dbf /home/oracle/backup/arch/hot_20240115/ ! cp -av /u01/app/oracle/oradata/ora11g/users01.dbf /home/oracle/backup/arch/hot_20240115/ ! cp -av /u01/app/oracle/oradata/ora11g/example01.dbf /home/oracle/backup/arch/hot_20240115/ ! cp -av /u01/app/oracle/oradata/ora11g/temp01.dbf /home/oracle/backup/arch/hot_20240115/ SQL> ALTER DATABASE END BACKUP; SELECT a.file#, a.name, a.checkpoint_change#, b.status, b.change#, to_char(b.time,'yyyy-mm-dd hh24:mi:ss') 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 1147766 NOT ACTIVE 1147766 2024-01-15 04:27:12 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1147766 NOT ACTIVE 1147766 2024-01-15 04:27:12 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 1147766 NOT ACTIVE 1147766 2024-01-15 04:27:12 4 /u01/app/oracle/oradata/ora11g/users01.dbf 1147766 NOT ACTIVE 1147766 2024-01-15 04:27:12 5 /u01/app/oracle/oradata/ora11g/example01.dbf 1147766 NOT ACTIVE 1147766 2024-01-15 04:27:12 # Control File Backup SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/home/oracle/backup/arch/hot_20240115/control01.ctl'; SQL> SELECT * FROM v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --------- ----------- ------------- ----------- ------------ --------- 1 1 48 52428800 512 1 YES INACTIVE 1145387 15-JAN-24 1145390 15-JAN-24 2 1 49 52428800 512 1 YES INACTIVE 1145390 15-JAN-24 1146954 15-JAN-24 3 1 50 52428800 512 1 NO CURRENT 1146954 15-JAN-24 2.8147E+14 SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; SQL> 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 1147942 15-JAN-24 50 /home/oracle/arch2/arch_1_50_1154915502.arc 1146954 15-JAN-24 1147942 15-JAN-24 # 리두에 대한 과거 정보를 확인 : 누적 히스토리 SELECT * FROM v$log_history; RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS ---------- ---------- ---------- ---------- ------------- ----------- ------------ ----------------- --------- 49 1158293812 1 49 1145390 15-JAN-24 1146954 635002 07-DEC-23 50 1158294760 1 50 1146954 15-JAN-24 1147942 635002 07-DEC-23 51 1158294835 1 51 1147942 15-JAN-24 1148070 635002 07-DEC-23 52 1158294836 1 52 1148070 15-JAN-24 1148089 635002 07-DEC-23 53 1158294839 1 53 1148089 15-JAN-24 1148093 635002 07-DEC-23 ... |
|
| << 시나리오 9 >> 아카이브 파일이 다른 위치에 있을 경우 복구 | |
| SQL> SELECT * FROM v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --------- ----------- ------------- ----------- ------------ --------- 1 1 51 52428800 512 1 NO CURRENT 1147942 15-JAN-24 2.8147E+14 2 1 49 52428800 512 1 YES INACTIVE 1145390 15-JAN-24 1146954 15-JAN-24 3 1 50 52428800 512 1 YES ACTIVE 1146954 15-JAN-24 1147942 15-JAN-24 SQL> CREATE TABLE hr.new_2024 AS SELECT * FROM hr.employees; SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> CREATE TABLE hr.old_2024 AS SELECT * FROM hr.employees; SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> SELECT * FROM v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --------- ----------- ------------- ----------- ------------ --------- 1 1 54 52428800 512 1 NO CURRENT 1148093 15-JAN-24 2.8147E+14 2 1 52 52428800 512 1 YES INACTIVE 1148070 15-JAN-24 1148089 15-JAN-24 3 1 53 52428800 512 1 NO ACTIVE 1148089 15-JAN-24 1148093 15-JAN-24 SQL> SELECT * FROM v$archived_log; >> 49~53 생성 SQL> SHUTDOWN IMMEDIATE 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 SQL> ! mkdir -p /home/oracle/archive_file # 아카이브 파일 위치 변경 SQL> ! mv /home/oracle/arch2/*.* /home/oracle/archive_file/ # 아카이브 파일 삭제 SQL> ! rm /home/oracle/arch1/*.* SQL> ! ls /home/oracle/arch* /home/oracle/arch1: /home/oracle/arch2: /home/oracle/archive_file: 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 # 장애 유발 ! rm /u01/app/oracle/oradata/ora11g/example01.dbf 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_ ERROR CHANGE# TIME ---------- ------- ------- ----------------------------------------------------------------- ---------- --------- 5 ONLINE ONLINE FILE NOT FOUND 0 >시스템파일이 아니니 바로 오프라인 SQL> ALTER DATABASE DATAFILE 5 OFFLINE; SQL> ALTER DATABASE OPEN; # Hot Backup Restore ! cp -av /home/oracle/backup/arch/hot_20240115/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: {=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 > 자동으로 기존 위치에서 파일을 찾을 수 없어 에러 # 다시 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: {=suggested | filename | AUTO | CANCEL} /home/oracle/archive_file/arch_1_50_1154915502.arc 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 Specify log: {=suggested | filename | AUTO | CANCEL} /home/oracle/archive_file/arch_1_51_1154915502.arc Log applied. Media recovery complete. > 다른 위치에 있는 아카이브 파일을 직접 파일 하나씩 순서대로 붙여 넣기 SQL> ALTER TABLESPACE example ONLINE; SQL> SELECT COUNT(*) FROM hr.new_2024; COUNT(*) ---------- 107 |
|
지난 콜드백업 : 아카이브 파일 무관하게 과거로 돌아갈수있다
> 불안전한 복구에 대한 대비
지난 hot백업파일 : 아카이브파일을 지웠다면 이미 필요없다
hot백업은 cancel base recovery 불가.
'Oracle 11g > Backup & Recovery' 카테고리의 다른 글
| 67 Backup&Recovery, Archive Log Mode, (0) | 2024.01.17 |
|---|---|
| 66 Backup&Recovery, Archive Log Mode, Backup (0) | 2024.01.16 |
| 64 Backup&Recovery, Archive Log Mode (0) | 2024.01.12 |
| 63 Backup&Recovery, Noarchive Log, Redo Log File, Temp File (0) | 2024.01.11 |
| 62 Backup&Recovery, Noarchive Log, Data File, Undo Data File (1) | 2024.01.10 |