| # 요약 | |
| ■ Noarchive Log Backup # Cold Backup << 시나리오 1 >> 특정한 데이터 파일이 손상 (백업 이후에 리두정보가 있을 경우) << 시나리오 2 >> 특정한 데이터 파일이 손상 (백업 이후에 리두정보가 없을 경우) << 시나리오 3 >> 백업 받지 않은 테이블스페이스의 Recovery 1 (세그먼트가 없을 경우) << 시나리오 4 >> 백업 받지 않은 테이블스페이스의 Recovery 2 (리두 정보가 있을 경우) << 시나리오 5 >> 백업 받지 않은 테이블스페이스의 Recovery 3 (리두 정보가 없을 경우) |
|
■ Noarchive Log Backup
- 일관성있는 Backup(Close Backup, Cold Backup, Offline Backup)
- DB를 정상적으로 종료해야한다. SHUTDOWN [ NORMAL | TRANSACTIONAL | IMMEDIATE ]
- Whole Database Backup : 모든 Data File,Control File, Redo Log File
| # Data File | |
| SELECT name FROM v$datafile; NAME -------------------------------------------- /u01/app/oracle/oradata/ora11g/system01.dbf /u01/app/oracle/oradata/ora11g/sysaux01.dbf /u01/app/oracle/oradata/ora11g/users01.dbf /u01/app/oracle/oradata/ora11g/example01.dbf /u01/app/oracle/oradata/ora11g/undotbs01.dbf |
|
| # Control File | |
| SELECT name FROM v$controlfile; NAME -------------------------------------------- /u01/app/oracle/oradata/ora11g/control01.ctl |
|
| # Redo Log File | |
| SELECT member FROM v$logfile; MEMBER -------------------------------------------- /u01/app/oracle/oradata/ora11g/redo01.log /u01/app/oracle/oradata/ora11g/redo02.log /u01/app/oracle/oradata/ora11g/redo03.log 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 NO CURRENT 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 NO INACTIVE 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO INACTIVE SELECT * FROM v$log; SEQUENCE# BLOCKSIZE ARCHIVED STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ... ---------- ---------- --------- -------- -------------- --------- ------------- --------- 89 512 NO CURRENT 1920426 08-JAN-24 2.8147E+14 84 512 NO INACTIVE 1920411 08-JAN-24 1920414 08-JAN-24 85 512 NO INACTIVE 1920414 08-JAN-24 1920417 08-JAN-24 SEQUENCE# : 84, 85, 89 <- 어제 Redo Log File을 수정하면서 갭이 생김 FIRST_CHANGE# : 1920426 <- 마지막 체크포인트 시점 |
|
| # Temp File | |
| SELECT name FROM v$tempfile; NAME ------------------------------------------ /u01/app/oracle/oradata/ora11g/temp01.dbf |
|
| # Archive Log | |
| SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 84 Current log sequence 89 SQL> show parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ------------ ----------------------------------- db_recovery_file_dest string /u01/app/oracle/fast_recovery_area db_recovery_file_dest_size big integer 4152M |
|
| # Tablespace Logging | |
| SELECT tablespace_name, logging FROM dba_tablespaces; TABLESPACE_NAME LOGGING ------------------------------ --------- SYSTEM LOGGING SYSAUX LOGGING TEMP NOLOGGING USERS LOGGING EXAMPLE LOGGING UNDOTBS LOGGING |
|
| # CHECKPOINT 발생한 시점 SCN 정보 |
|
| SELECT checkpoint_change# FROM v$database; CHECKPOINT_CHANGE# ------------------ 1920426 SELECT name, checkpoint_change# FROM v$datafile; NAME CHECKPOINT_CHANGE# --------------------------------------------- ------------------ /u01/app/oracle/oradata/ora11g/system01.dbf 1920426 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1920426 /u01/app/oracle/oradata/ora11g/users01.dbf 1920426 /u01/app/oracle/oradata/ora11g/example01.dbf 1920426 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 1920426 # 현재 SCN 번호 SELECT current_scn FROM v$database; CURRENT_SCN ----------- 1929237 # SCN 번호에 대한 시간 정보 SELECT scn_to_timestamp(1929237) FROM dual; SCN_TO_TIMESTAMP(1929237) --------------------------- 24/01/09 10:06:58.000000000 |
|
| # BACKUP | |
| SQL> SHUTDOWN IMMEDIATE SQL> EXIT [oracle@oracle ~]$ pwd /home/oracle # 백업 저장 폴더 생성 [oracle@oracle ~]$ mkdir -p backup/noarch [oracle@oracle ~]$ cd backup/noarch [oracle@oracle noarch]$ pwd /home/oracle/backup/noarch [oracle@oracle noarch]$ cd /u01/app/oracle/oradata/ora11g [oracle@oracle ora11g]$ pwd /u01/app/oracle/oradata/ora11g # Backup [oracle@oracle ora11g]$ cp -av *.* /home/oracle/backup/noarch ‘control01.ctl’ -> ‘/home/oracle/backup/noarch/control01.ctl’ ‘example01.dbf’ -> ‘/home/oracle/backup/noarch/example01.dbf’ ‘redo01.log’ -> ‘/home/oracle/backup/noarch/redo01.log’ ‘redo02.log’ -> ‘/home/oracle/backup/noarch/redo02.log’ ‘redo03.log’ -> ‘/home/oracle/backup/noarch/redo03.log’ ‘sysaux01.dbf’ -> ‘/home/oracle/backup/noarch/sysaux01.dbf’ ‘system01.dbf’ -> ‘/home/oracle/backup/noarch/system01.dbf’ ‘temp01.dbf’ -> ‘/home/oracle/backup/noarch/temp01.dbf’ ‘undotbs01.dbf’ -> ‘/home/oracle/backup/noarch/undotbs01.dbf’ ‘users01.dbf’ -> ‘/home/oracle/backup/noarch/users01.dbf’ ** alert log file 실시간 Log 체크 tail -f $ORACLE_BASE/diag/rdbms/ora11g/ora11g/trace/alert_ora11g.log [oracle@oracle ora11g]$ cd /home/oracle/backup/noarch [oracle@oracle noarch]$ ll total 2021912 -rw-r-----. 1 oracle oinstall 9748480 Jan 9 10:19 control01.ctl -rw-r-----. 1 oracle oinstall 347348992 Jan 9 10:19 example01.dbf -rw-r-----. 1 oracle oinstall 52429312 Jan 9 10:19 redo01.log -rw-r-----. 1 oracle oinstall 52429312 Jan 8 17:32 redo02.log -rw-r-----. 1 oracle oinstall 52429312 Jan 8 17:32 redo03.log -rw-r-----. 1 oracle oinstall 723525632 Jan 9 10:19 sysaux01.dbf -rw-r-----. 1 oracle oinstall 786440192 Jan 9 10:19 system01.dbf -rw-r-----. 1 oracle oinstall 20979712 Jan 9 09:32 temp01.dbf -rw-r-----. 1 oracle oinstall 26615808 Jan 9 10:19 undotbs01.dbf -rw-r-----. 1 oracle oinstall 6561792 Jan 9 10:19 users01.dbf [oracle@oracle noarch]$ sqlplus / as sysdba SQL> STARTUP SELECT * FROM v$log; SEQUENCE# BLOCKSIZE ARCHIVED STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ... ---------- ---------- --------- -------- -------------- --------- ------------- --------- 89 512 NO CURRENT 1920426 08-JAN-24 2.8147E+14 84 512 NO INACTIVE 1920411 08-JAN-24 1920414 08-JAN-24 85 512 NO INACTIVE 1920414 08-JAN-24 1920417 08-JAN-24 |
|
| << 시나리오 1 >> 특정한 데이터 파일이 손상 (백업 이후에 리두정보가 있을 경우) | |
| << HR SESSION >> CREATE TABLE new(id NUMBER) TABLESPACE example; INSERT INTO new(id) VALUES(1); COMMIT; SELECT * FROM hr.new; ID ---------- 1 << SYS SESSION >> SELECT f.file_name FROM dba_extents e, dba_data_files f WHERE e.file_id = f.file_id AND e.segment_name = 'NEW' AND e.owner = 'HR'; FILE_NAME -------------------------------------------- /u01/app/oracle/oradata/ora11g/example01.dbf SQL> SHUTDOWN IMMEDIATE # 장애 유발 SQL> ! rm /u01/app/oracle/oradata/ora11g/example01.dbf SQL> STARTUP ORACLE instance started. Total System Global Area 711430144 bytes Fixed Size 1367004 bytes Variable Size 448791588 bytes Database Buffers 255852544 bytes Redo Buffers 5419008 bytes 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> ! [oracle@oracle ~]$ cd backup/noarch/ [oracle@oracle noarch]$ pwd /home/oracle/backup/noarch # RESTORE : Backup 받았던 파일을 복원 [oracle@oracle noarch]$ cp -av example01.dbf /u01/app/oracle/oradata/ora11g/example01.dbf ‘example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’ [oracle@oracle noarch]$ ls /u01/app/oracle/oradata/ora11g control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf [oracle@oracle noarch]$ exit # RECOVERY : 복원된 파일에 Redo Log File 을 적용하여 복구 SQL> RECOVER TABLESPACE example; -- 특정 테이블스페이스에 redo log 적용 또는 SQL> RECOVER DATABASE; -- 전체 DataFile에 redo log 적용 SQL> ALTER DATABASE OPEN; SELECT name, checkpoint_change# FROM v$datafile; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ /u01/app/oracle/oradata/ora11g/system01.dbf 1932165 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1932165 /u01/app/oracle/oradata/ora11g/users01.dbf 1932165 /u01/app/oracle/oradata/ora11g/example01.dbf 1932165 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 1932165 SELECT * FROM v$log; SEQUENCE# BLOCKSIZE ARCHIVED STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ... ---------- ---------- --------- -------- -------------- --------- ------------- --------- 89 512 NO CURRENT 1920426 08-JAN-24 2.8147E+14 84 512 NO INACTIVE 1920411 08-JAN-24 1920414 08-JAN-24 85 512 NO INACTIVE 1920414 08-JAN-24 1920417 08-JAN-24 SQL> SELECT * FROM hr.new; ID ---------- 1 >> 마지막 백업 시점의 SEQUENCE#89 redo가 있어 백업 이 후 작업한 데이터도 복구 되었다. |
|
| # 전체 복구 | |
| SQL> SHUTDOWN IMMEDIATE SQL> EXIT [oracle@oracle noarch]$ pwd /home/oracle/backup/noarch # 전체 RESTORE [oracle@oracle noarch]$ cp -av *.* /u01/app/oracle/oradata/ora11g [oracle@oracle noarch]$ sqlplus / as sysdba SQL> STARTUP SELECT current_scn FROM v$database; CURRENT_SCN ----------- 1932647 SELECT name, checkpoint_change# FROM v$datafile; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ /u01/app/oracle/oradata/ora11g/system01.dbf 1929709 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1929709 /u01/app/oracle/oradata/ora11g/users01.dbf 1929709 /u01/app/oracle/oradata/ora11g/example01.dbf 1929709 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 1929709 (복구 전 1932165) SELECT * FROM v$log; SEQUENCE# BLOCKSIZE ARCHIVED STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ... ---------- ---------- --------- -------- -------------- --------- ------------- --------- 89 512 NO CURRENT 1920426 08-JAN-24 2.8147E+14 84 512 NO INACTIVE 1920411 08-JAN-24 1920414 08-JAN-24 85 512 NO INACTIVE 1920414 08-JAN-24 1920417 08-JAN-24 |
|
| << 시나리오 2 >> 특정한 데이터 파일이 손상 (백업 이후에 리두정보가 없을 경우) | |
| << HR SESSION >> CREATE TABLE new(id NUMBER) TABLESPACE example; INSERT INTO new(id) VALUES(1); COMMIT; SELECT * FROM hr.new; ID ---------- 1 << SYS SESSION >> SELECT * FROM v$log; SEQUENCE# BLOCKSIZE ARCHIVED STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ... ---------- ---------- --------- -------- -------------- --------- ------------- --------- 89 512 NO CURRENT 1920426 08-JAN-24 2.8147E+14 84 512 NO INACTIVE 1920411 08-JAN-24 1920414 08-JAN-24 85 512 NO INACTIVE 1920414 08-JAN-24 1920417 08-JAN-24 ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SWITCH LOGFILE; SELECT * FROM v$log; SEQUENCE# BLOCKSIZE ARCHIVED STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ... ---------- ---------- --------- -------- -------------- --------- ------------- --------- 92 512 NO INACTIVE 1933415 09-JAN-24 1933418 09-JAN-24 93 512 NO INACTIVE 1933418 09-JAN-24 1933421 09-JAN-24 94 512 NO CURRENT 1933421 09-JAN-24 2.8147E+14 SELECT current_scn FROM v$database; CURRENT_SCN ----------- 1933471 SELECT name, checkpoint_change# FROM v$datafile; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ /u01/app/oracle/oradata/ora11g/system01.dbf 1933421 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1933421 /u01/app/oracle/oradata/ora11g/users01.dbf 1933421 /u01/app/oracle/oradata/ora11g/example01.dbf 1933421 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 1933421 SELECT f.file_name FROM dba_extents e, dba_data_files f WHERE e.file_id = f.file_id AND e.segment_name = 'NEW' AND e.owner = 'HR'; FILE_NAME -------------------------------------------------- /u01/app/oracle/oradata/ora11g/example01.dbf SQL> SHUTDOWN IMMEDIATE # 장애 유발 SQL> ! rm /u01/app/oracle/oradata/ora11g/example01.dbf SQL> STARTUP ORACLE instance started. Total System Global Area 711430144 bytes Fixed Size 1367004 bytes Variable Size 448791588 bytes Database Buffers 255852544 bytes Redo Buffers 5419008 bytes 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 status FROM v$instance; STATUS ------------ MOUNTED SQL> ! [oracle@oracle noarch]$ pwd /home/oracle/backup/noarch # 해당 파일만 복구 [oracle@oracle noarch]$ cp -av example01.dbf /u01/app/oracle/oradata/ora11g/example01.dbf [oracle@oracle noarch]$ exit SQL> RECOVER DATABASE; ORA-00279: change 1929706 generated at 01/09/2024 10:19:50 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_09/o1_mf_1_89_%u_.arc ORA-00280: change 1929706 for thread 1 is in sequence #89 -- 마지막 체크포인트 시점인 시퀀스#89번이 필요한데 자료가 없다. Specify log: {=suggested | filename | AUTO | CANCEL} AUTO --> 자동으로 해줘 ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_09/o1_mf_1_89_%u_.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 '/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_09/o1_mf_1_89_%u_.arc' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 >> 있어야할 redo가 없으니 리커버리 실패. 완전복구 실패. SQL> SHUTDOWN ABORT SQL> ! # 불안전한 복구. Data File 전체 과거로 back.. [oracle@oracle noarch]$ cp -av *.* /u01/app/oracle/oradata/ora11g [oracle@oracle noarch]$ exit SQL> STARTUP SELECT current_scn FROM v$database; CURRENT_SCN ----------- 1933270 SELECT name, checkpoint_change# FROM v$datafile; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ /u01/app/oracle/oradata/ora11g/system01.dbf 1929709 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1929709 /u01/app/oracle/oradata/ora11g/users01.dbf 1929709 /u01/app/oracle/oradata/ora11g/example01.dbf 1929709 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 1929709 (복구 전 1933421) SELECT * FROM v$log; SEQUENCE# BLOCKSIZE ARCHIVED STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ... ---------- ---------- --------- -------- -------------- --------- ------------- --------- 89 512 NO CURRENT 1920426 08-JAN-24 2.8147E+14 84 512 NO INACTIVE 1920411 08-JAN-24 1920414 08-JAN-24 85 512 NO INACTIVE 1920414 08-JAN-24 1920417 08-JAN-24 SELECT * FROM hr.new; ORA-00942: table or view does not exist >> redo 정보가 없는 데이터 복구 불가 |
|
| << 시나리오 3 >> 백업 받지 않은 테이블스페이스의 Recovery 1 (세그먼트가 없을 경우) | |
| SELECT tablespace_name, file_name FROM dba_data_files; TABLESPACE_NAME FILE_NAME ------------------------------ -------------------------------------------------- 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 CREATE TABLESPACE insa_tbs DATAFILE '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' SIZE 10M; SELECT tablespace_name, file_name FROM dba_data_files; TABLESPACE_NAME FILE_NAME ------------------------------ -------------------------------------------------- INSA_TBS /u01/app/oracle/oradata/ora11g/insa_tbs01.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 SELECT * FROM v$log; SEQUENCE# BLOCKSIZE ARCHIVED STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ... ---------- ---------- --------- -------- -------------- --------- ------------- --------- 89 512 NO CURRENT 1920426 08-JAN-24 2.8147E+14 84 512 NO INACTIVE 1920411 08-JAN-24 1920414 08-JAN-24 85 512 NO INACTIVE 1920414 08-JAN-24 1920417 08-JAN-24 SELECT checkpoint_change# FROM v$database; CHECKPOINT_CHANGE# ------------------ 1929709 SELECT name, checkpoint_change# FROM v$datafile; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ /u01/app/oracle/oradata/ora11g/system01.dbf 1929709 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1929709 /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf 1937715 /u01/app/oracle/oradata/ora11g/users01.dbf 1929709 /u01/app/oracle/oradata/ora11g/example01.dbf 1929709 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 1929709 # 장애 유발 SQL> ! rm /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf SQL> SHUTDOWN IMMEDIATE ORA-03113: end-of-file on communication channel Process ID: 30723 Session ID: 9 Serial number: 3 SQL> SHUTDOWN ABORT -- ORA-24324: service handle not initialized -- ORA-01041: internal error. hostdef extension doesn't exist [oracle@oracle noarch]$ exit [oracle@oracle noarch]$ sqlplus / as sysdba Connected to an idle instance. >> 불안전 다운 SQL> STARTUP ORACLE instance started. Total System Global Area 711430144 bytes Fixed Size 1367004 bytes Variable Size 448791588 bytes Database Buffers 255852544 bytes Redo Buffers 5419008 bytes Database mounted. ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' SELECT name, status FROM v$datafile; NAME STATUS -------------------------------------------------- ------- /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf ONLINE /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE SELECT status FROM v$instance; STATUS ------------ MOUNTED # Noarchive Log Mode 에서는 데이터 파일을 OFFLINE DROP ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' OFFLINE DROP; # Archive Log Mode 에서는 OFFLINE SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' OFFLINE; SELECT name, status FROM v$datafile; NAME STATUS -------------------------------------------------- ------- /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf RECOVER /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE > Noarchive Log Mode 에서는 OFFLINE DROP -> RECOVER > Archive Log Mode 에서는 OFFLINE -> OFFLINE SQL> ALTER DATABASE OPEN; SQL> SELECT count(*) FROM hr.employees; COUNT(*) ---------- 106 >> db 잘돌아가는지 체크용 SQL> SELECT name, status FROM v$datafile; NAME STATUS -------------------------------------------------- ------- /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf RECOVER /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE SQL> DROP TABLESPACE insa_tbs; >> 세그먼트가 없다 = 데이터가 없다 => 삭제 후 다시 만들자. SQL> SELECT name, status FROM v$datafile; NAME STATUS -------------------------------------------------- ------- /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE |
|
| << 시나리오 4 >> 백업 받지 않은 테이블스페이스의 Recovery 2 (리두 정보가 있을 경우) | |
| DROP TABLESPACE insa_tbs PURGE; CREATE TABLESPACE insa_tbs DATAFILE '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' SIZE 10M; SELECT tablespace_name, file_name FROM dba_data_files; TABLESPACE_NAME FILE_NAME ------------------------------ -------------------------------------------------- INSA_TBS /u01/app/oracle/oradata/ora11g/insa_tbs01.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 SELECT * FROM v$log; SEQUENCE# BLOCKSIZE ARCHIVED STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ... ---------- ---------- --------- -------- -------------- --------- ------------- --------- 89 512 NO INACTIVE 1920426 08-JAN-24 1958537 09-JAN-24 90 512 NO CURRENT 1958537 09-JAN-24 2.8147E+14 85 512 NO INACTIVE 1920414 08-JAN-24 1920417 08-JAN-24 SELECT checkpoint_change# FROM v$database; CHECKPOINT_CHANGE# ------------------ 1958538 SELECT name, checkpoint_change# FROM v$datafile; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ /u01/app/oracle/oradata/ora11g/system01.dbf 1958538 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1958538 /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf 1960503 /u01/app/oracle/oradata/ora11g/users01.dbf 1958538 /u01/app/oracle/oradata/ora11g/example01.dbf 1958538 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 1958538 CREATE TABLE hr.new(id NUMBER) TABLESPACE insa_tbs; INSERT INTO hr.new(id) values(1); COMMIT; SELECT * FROM hr.new; ID ---------- 1 # 장애 유발 SQL> ! rm /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf SELECT * FROM v$log; SEQUENCE# BLOCKSIZE ARCHIVED STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ... ---------- ---------- --------- -------- -------------- --------- ------------- --------- 89 512 NO INACTIVE 1920426 08-JAN-24 1958537 09-JAN-24 90 512 NO CURRENT 1958537 09-JAN-24 2.8147E+14 85 512 NO INACTIVE 1920414 08-JAN-24 1920417 08-JAN-24 SELECT name, status FROM v$datafile; NAME STATUS -------------------------------------------------- ------- /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf ONLINE /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' OFFLINE DROP; SELECT name, status FROM v$datafile; NAME STATUS -------------------------------------------------- ------- /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf RECOVER /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE # 빈파일 생성 SQL> ALTER DATABASE CREATE DATAFILE'/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf'; SQL> ! ls /u01/app/oracle/oradata/ora11g control01.ctl redo01.log sysaux01.dbf undotbs01.dbf example01.dbf redo02.log system01.dbf users01.dbf insa_tbs01.dbf redo03.log temp01.dbf # 파일에 redo 적용 ALTER DATABASE RECOVER DATAFILE'/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf'; SELECT name, status FROM v$datafile; NAME STATUS -------------------------------------------------- ------- /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf OFFLINE /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE # OFFLINE -> ONLINE ALTER DATABASE DATAFILE'/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' ONLINE; SELECT name, status FROM v$datafile; NAME STATUS -------------------------------------------------- ------- /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf ONLINE /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE SELECT * FROM hr.new; ID ---------- 1 DROP TABLESPACE insa_tbs INCLUDING CONTENTS AND DATAFILES; |
|
| << 시나리오 5 >> 백업 받지 않은 테이블스페이스의 Recovery 3 (리두 정보가 없을 경우) | |
| CREATE TABLESPACE insa_tbs DATAFILE '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' SIZE 10M; CREATE TABLE hr.new(id NUMBER) TABLESPACE insa_tbs; INSERT INTO hr.new(id) VALUES(1); COMMIT; SELECT * FROM hr.new; ID ---------- 1 SELECT name, status FROM v$datafile; NAME STATUS -------------------------------------------------- ------- /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf ONLINE /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE SQL> SELECT * FROM v$log; SEQUENCE# BLOCKSIZE ARCHIVED STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ... ---------- ---------- --------- -------- -------------- --------- ------------- --------- 95 512 NO INACTIVE 1962961 09-JAN-24 1982995 09-JAN-24 96 512 NO CURRENT 1982995 09-JAN-24 2.8147E+14 94 512 NO INACTIVE 1962958 09-JAN-24 1962961 09-JAN-24 # 장애 유발 SQL> ! rm /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf *** alert_ora11g.log 파일에서 장애 발생하자마자 바로 log 확인 가능 ORA-01116: error in opening database file 3 ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3 >> 장애 이슈 모르고 계속 작업 insert into hr.new(id) values(2); insert into hr.new(id) values(3); commit; SQL> select * from hr.new; ID ---------- 3 2 1 ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SWITCH LOGFILE; ORA-03113: end-of-file on communication channel Process ID: 3686 Session ID: 9 Serial number: 3 ALTER SYSTEM SWITCH LOGFILE; ORA-03114: not connected to ORACLE >>> DB 끊어짐 SQL> exit [oracle@oracle noarch]$ sqlplus / 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/insa_tbs01.dbf' SQL> SELECT status FROM v$instance; STATUS ------------ MOUNTED SELECT name, status FROM v$datafile; NAME STATUS -------------------------------------------------- ------- /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf ONLINE /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE SQL> ! ls /u01/app/oracle/oradata/ora11g control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf >>> 물리적 파일이 없다. SQL> SELECT * FROM v$log; SEQUENCE# BLOCKSIZE ARCHIVED STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ... ---------- ---------- --------- -------- -------------- --------- ------------- --------- 98 512 NO CURRENT 1987011 09-JAN-24 2.8147E+14 97 512 NO ACTIVE 1987008 09-JAN-24 1987011 09-JAN-24 96 512 NO ACTIVE 1982995 09-JAN-24 1987008 09-JAN-24 SQL> ARCHIVE LOG LIST Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 96 Current log sequence 98 # DataFile Online -> Offline 상태 변경 SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' OFFLINE DROP; SQL> SELECT name, status FROM v$datafile; NAME STATUS -------------------------------------------------- ------- /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf RECOVER /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE # DB MOUNT - OPEN 상태 변경 SQL> ALTER DATABASE OPEN; # 빈 파일 생성 SQL> ALTER DATABASE CREATE DATAFILE '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf'; # Recovery : 빈 파일에 Redo Log File 적용하여 복구 SQL> ALTER DATABASE RECOVER DATAFILE '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf'; ORA-00279: change 1986622 generated at 01/09/2024 16:29:27 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_09/o1_mf_1_96_%u_.arc ORA-00280: change 1986622 for thread 1 is in sequence #96 SQL> DROP TABLESPACE insa_tbs including contents and datafiles; ORA-01156: recovery or flashback in progress may need access to files >> 리커버리 시도 실패하면 삭제 불가 => 무조건 DB 재실행 SQL> SHUTDOWN IMMEDIATE SQL> STARTUP 또는 STARTUP FORCE : shutdown abort + startup >> 운영DB에 함부로 사용하지 말자. >>> 백업파일 없다 + Redo Log 없다 => 복구 불가, 삭제 SQL> DROP TABLESPACE insa_tbs INCLUDING CONTENTS AND DATAFILES; SQL> SELECT name, status FROM v$datafile; NAME STATUS -------------------------------------------------- ------- /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE |
|
'Oracle 11g > Backup & Recovery' 카테고리의 다른 글
| 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 |
| 62 Backup&Recovery, Noarchive Log, Data File, Undo Data File (1) | 2024.01.10 |
| 60 Backup (1) | 2024.01.08 |