| # 요약 | |
| [문제1~3] # DataFile 백업 일괄 작업 (Shell) << 시나리오 6 >> SYSTEM TABLESPACE에 속한 데이터 파일 삭제 (백업 이후 리두 정보가 있을 경우) << 시나리오 7 >> SYSTEM TABLESPACE에 속한 데이터 파일 삭제(백업 이후 리두 정보가 없을 경우) # Undo Tablespace 내 수행 중인 트랜젝션 정보 확인 << 시나리오 8 >> UNDO Data File 손상 # UNDO Tablespace 수정 후 System Data File 손상 << 시나리오 9 >> 모든 파일이 있는 디스크 손상 (Pfile 보유) |
|
| [문제1] archive log mode 인지 noarchive log mode 인지 확인하세요. | |
| SQL> ARCHIVE LOG LIST Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 97 Current log sequence 99 SELECT log_mode FROM v$database; LOG_MODE ------------ NOARCHIVELOG |
|
| [문제2] data file, temp file, redo log file 정보를 확인하세요. | |
| SELECT name,checkpoint_change# FROM v$datafile; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ /u01/app/oracle/oradata/ora11g/system01.dbf 2019998 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2019998 /u01/app/oracle/oradata/ora11g/users01.dbf 2019998 /u01/app/oracle/oradata/ora11g/example01.dbf 2019998 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2019998 SELECT name FROM v$tempfile; NAME -------------------------------------------------- /u01/app/oracle/oradata/ora11g/temp01.dbf 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 |
|
| [문제3] noarchive log mode 입니다. Whole database backup(일관성 있는 백업, cold backup) 을 /home/oracle/backup/noarch/20240110 디렉토리에 백업해 주세요. 초기 파라미터 파일도 백업해 주세요. |
|
| SPFILE : 서버 파리미터 파일 PFILE : 텍스트 파라미터 파일 (수동 편집 가능). undo 고장 시 pfile에서 수정하기 때문에 백업 필수 SQL> SHOW PARAMETER spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/11.2.0.4/db_1/dbs/spfileora11g.ora # SPFILE과 동일한 위치에 PFILE 생성 CREATE PFILE FROM SPFILE; # 위치 변경해서 PFILE 생성 CREATE PFILE='/home/oracle/backup/noarch/20240110/initora11g_20240110.ora' FROM SPFILE; SQL> SHUTDOWN IMMEDIATE Database closed. 신규 유저 차단 접속 중인 유저 킬 트랜잭션 자동 롤백 - pmon 체크포인트 발생 - ckpt > lgrw > redo log > dbrw Database dismounted. 컨트롤파일 오프 ORACLE instance shut down. 인스턴스 종료 SQL> ! # 디렉터리 생성 [oracle@oracle ~]$ mkdir -p /home/oracle/backup/noarch/20240110 [oracle@oracle ~]$ cd /u01/app/oracle/oradata/ora11g # 동일한 디렉토리에 있는 DataFile을 모두 백업(CP) [oracle@oracle ora11g]$ cp -av *.* /home/oracle/backup/noarch/20240110 [oracle@oracle 20240110]$ cd /u01/app/oracle/product/11.2.0.4/db_1/dbs # SPFILE 백업 [oracle@oracle dbs]$ cp -av spfileora11g.ora /home/oracle/backup/noarch/20240110 [oracle@oracle dbs]$ cd /home/oracle/backup/noarch/20240110 [oracle@oracle 20240110]$ ll total 2021920 -rw-r-----. 1 oracle oinstall 9748480 Jan 10 09:54 control01.ctl -rw-r-----. 1 oracle oinstall 347348992 Jan 10 09:54 example01.dbf -rw-r--r--. 1 oracle oinstall 1170 Jan 10 10:19 initora11g_20240110.ora -rw-r-----. 1 oracle oinstall 52429312 Jan 9 16:45 redo01.log -rw-r-----. 1 oracle oinstall 52429312 Jan 10 09:54 redo02.log -rw-r-----. 1 oracle oinstall 52429312 Jan 9 16:45 redo03.log -rw-r-----. 1 oracle oinstall 3584 Jan 9 16:46 spfileora11g.ora -rw-r-----. 1 oracle oinstall 723525632 Jan 10 09:54 sysaux01.dbf -rw-r-----. 1 oracle oinstall 786440192 Jan 10 09:54 system01.dbf -rw-r-----. 1 oracle oinstall 20979712 Jan 10 09:42 temp01.dbf -rw-r-----. 1 oracle oinstall 26615808 Jan 10 09:54 undotbs01.dbf -rw-r-----. 1 oracle oinstall 6561792 Jan 10 09:54 users01.dbf [oracle@oracle 20240110]$ exit SQL> STARTUP |
|
| # DataFile 백업 일괄 작업 | |
| # 다른 위치에 PFILE 생성 CREATE PFILE='/home/oracle/backup/noarch/20240110/initora11g_20240110.ora' FROM SPFILE; # 작업명령 일괄 생성 : 별도 메모 SELECT 'cp -av '||name||' /home/oracle/backup/noarch/20240110' FROM v$datafile UNION ALL SELECT 'cp -av '||name||' /home/oracle/backup/noarch/20240110' FROM v$tempfile UNION ALL SELECT 'cp -av '||member||' /home/oracle/backup/noarch/20240110' FROM v$logfile UNION ALL SELECT 'cp -av '||name||' /home/oracle/backup/noarch/20240110' FROM v$controlfile; SQL> SHUTDOWN IMMEDIATE -- alert log -- Thread 1 closed at log sequence 99 [oracle@oracle ~]$ cd /home/oracle/backup/noarch/20240110 # 기존 파일 삭제 [oracle@oracle 20240110]$ rm *.* # SHELL 생성 [oracle@oracle 20240110]$ vi backup.sh cp -av /u01/app/oracle/oradata/ora11g/system01.dbf /home/oracle/backup/noarch/20240110 cp -av /u01/app/oracle/oradata/ora11g/sysaux01.dbf /home/oracle/backup/noarch/20240110 cp -av /u01/app/oracle/oradata/ora11g/users01.dbf /home/oracle/backup/noarch/20240110 cp -av /u01/app/oracle/oradata/ora11g/example01.dbf /home/oracle/backup/noarch/20240110 cp -av /u01/app/oracle/oradata/ora11g/undotbs01.dbf /home/oracle/backup/noarch/20240110 cp -av /u01/app/oracle/oradata/ora11g/temp01.dbf /home/oracle/backup/noarch/20240110 cp -av /u01/app/oracle/oradata/ora11g/redo01.log /home/oracle/backup/noarch/20240110 cp -av /u01/app/oracle/oradata/ora11g/redo02.log /home/oracle/backup/noarch/20240110 cp -av /u01/app/oracle/oradata/ora11g/redo03.log /home/oracle/backup/noarch/20240110 cp -av /u01/app/oracle/oradata/ora11g/control01.ctl /home/oracle/backup/noarch/20240110 :wq [oracle@oracle 20240110]$ ls backup.sh # SHELL 실행 [oracle@oracle 20240110]$ sh backup.sh > backup.log >> cp -av : v 사용으로 화면에 출력해야하지만 sh 에서는 안나온다 > Redirection-write 파일 생성 [oracle@oracle 20240110]$ ll total 2021920 -rw-r--r--. 1 oracle oinstall 1068 Jan 10 10:52 backup.log -rw-r--r--. 1 oracle oinstall 865 Jan 10 10:48 backup.sh -rw-r-----. 1 oracle oinstall 9748480 Jan 10 10:45 control01.ctl -rw-r-----. 1 oracle oinstall 347348992 Jan 10 10:45 example01.dbf -rw-r-----. 1 oracle oinstall 52429312 Jan 10 10:02 redo01.log -rw-r-----. 1 oracle oinstall 52429312 Jan 10 10:45 redo02.log -rw-r-----. 1 oracle oinstall 52429312 Jan 10 10:02 redo03.log -rw-r-----. 1 oracle oinstall 723525632 Jan 10 10:45 sysaux01.dbf -rw-r-----. 1 oracle oinstall 786440192 Jan 10 10:45 system01.dbf -rw-r-----. 1 oracle oinstall 20979712 Jan 10 10:02 temp01.dbf -rw-r-----. 1 oracle oinstall 26615808 Jan 10 10:45 undotbs01.dbf -rw-r-----. 1 oracle oinstall 6561792 Jan 10 10:45 users01.dbf [oracle@oracle 20240110]$ rm backup.* [oracle@oracle 20240110]$ ls control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf |
|
| # 복구 Restore [oracle@oracle 20240110]$ cp -av *.* /u01/app/oracle/oradata/ora11g [oracle@oracle 20240110]$ exit 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. Database opened. SQL> SELECT current_scn FROM v$database; CURRENT_SCN ----------- 2022772 SQL> SELECT name,checkpoint_change# FROM v$datafile; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ /u01/app/oracle/oradata/ora11g/system01.dbf 2022367 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2022367 /u01/app/oracle/oradata/ora11g/users01.dbf 2022367 /u01/app/oracle/oradata/ora11g/example01.dbf 2022367 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2022367 SQL> SELECT * FROM v$log; SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------------- ------------- ---------- ------------ --------- 98 INACTIVE 1987011 24/01/09 2007014 24/01/09 99 CURRENT 2007014 24/01/09 2.8147E+14 97 INACTIVE 1987008 24/01/09 1987011 24/01/09 >> 백업 파일 당시 시점 SEQUENCE# 99, SCN 2022367 |
|
| << 시나리오 6 >> SYSTEM TABLESPACE에 속한 데이터 파일 삭제 (백업 이후 리두 정보가 있을 경우) | |
| SQL> SELECT current_scn FROM v$database; CURRENT_SCN ----------- 2023006 SQL> SELECT name,checkpoint_change# FROM v$datafile; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ /u01/app/oracle/oradata/ora11g/system01.dbf 2022367 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2022367 /u01/app/oracle/oradata/ora11g/users01.dbf 2022367 /u01/app/oracle/oradata/ora11g/example01.dbf 2022367 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2022367 SELECT sequence#,status,first_change#,first_time,next_change#,next_time FROM v$log; SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------------- ------------- --------- ------------ --------- 98 INACTIVE 1987011 09-JAN-24 2007014 09-JAN-24 99 CURRENT 2007014 09-JAN-24 2.8147E+14 97 INACTIVE 1987008 09-JAN-24 1987011 09-JAN-24 # 장애 유발 : 딕셔너리 정보를 가진 시스템파일 손상 SQL> ! rm /u01/app/oracle/oradata/ora11g/system01.dbf SQL> ALTER SYSTEM CHECKPOINT; ORA-03113: end-of-file on communication channel Process ID: 10882 Session ID: 9 Serial number: 3 >> DB off --alert log --Wed Jan 10 11:10:29 2024 --Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ckpt_10860.trc: --ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode --ORA-01116: error in opening database file 1 --ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf' --ORA-27041: unable to open file --Linux Error: 2: No such file or directory --Additional information: 3 -- ... --CKPT (ospid: 10860): terminating the instance due to error 1242 --Wed Jan 10 11:10:29 2024 --System state dump requested by (instance=1, osid=10860 (CKPT)), summary=[abnormal instance termination]. --System State dumped to trace file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_diag_10846_20240110111029.trc --Dumping diagnostic data in directory=[cdmp_20240110111029], requested by (instance=1, osid=10860 (CKPT)), summary=[abnormal instance termination]. --Instance terminated by CKPT, pid = 10860 SQL> SELECT status FROM v$instance; ORA-03114: not connected to ORACLE SQL> STARTUP ORA-24324: service handle not initialized ORA-01041: internal error. hostdef extension doesn't exist 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' --... --Completed: ALTER DATABASE MOUNT --Wed Jan 10 11:14:53 2024 --ALTER DATABASE OPEN --Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw0_11304.trc: --ORA-01157: cannot identify/lock data file 1 - see DBWR trace file --ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf' --ORA-27037: unable to obtain file status --Linux Error: 2: No such file or directory --Additional information: 3 --Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_11332.trc: --ORA-01157: cannot identify/lock data file 1 - see DBWR trace file --ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf' --ORA-1157 signalled during: ALTER DATABASE OPEN... --Wed Jan 10 11:14:54 2024 --Checker run found 1 new persistent data failures SQL> SELECT status FROM v$instance; STATUS ------------ MOUNTED 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@oracle 20240110]$ pwd /home/oracle/backup/noarch/20240110 # 백업 RESTORE [oracle@oracle 20240110]$ cp -av system01.dbf /u01/app/oracle/oradata/ora11g [oracle@oracle ora11g]$ exit # SYSTEM TABLESPACE 복구 # 마지막 백업 시점 이후 변경 이력정보를 redo log file 에서 찾아서 복구 작업을 진행한다. # 백업 시점의 마지막 체크포인트(SCN) 기준 SQL> RECOVER TABLESPACE SYSTEM; Media recovery complete. 또는 RECOVER DATABASE; --Wed Jan 10 11:22:00 2024 --ALTER DATABASE RECOVER tablespace system --Media Recovery Start --Serial Media Recovery started --Recovery of Online Redo Log: Thread 1 Group 2 Seq 99 Reading mem 0 -- Mem# 0: /u01/app/oracle/oradata/ora11g/redo02.log --Media Recovery Complete (ora11g) --Completed: ALTER DATABASE RECOVER tablespace system SQL> ALTER DATABASE OPEN; Database altered. SQL> SELECT current_scn FROM v$database; CURRENT_SCN ----------- 2043678 SQL> SELECT name,status,checkpoint_change# FROM v$datafile; NAME STATUS CHECKPOINT_CHANGE# -------------------------------------------------- ------- ------------------ /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 2043248 /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 2043248 /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 2043248 /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 2043248 /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE 2043248 SQL> SELECT sequence#,status,first_change#,first_time,next_change#,next_time FROM v$log; SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------------- ------------- --------- ------------ --------- 98 INACTIVE 1987011 09-JAN-24 2007014 09-JAN-24 99 INACTIVE 2007014 09-JAN-24 2043247 10-JAN-24 100 CURRENT 2043247 10-JAN-24 2.8147E+14 |
|
| << 시나리오 7 >> SYSTEM TABLESPACE에 속한 데이터 파일 삭제(백업 이후 리두 정보가 없을 경우) | |
| SQL> SELECT sequence#,status,first_change#,first_time,next_change#,next_time FROM v$log; SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------------- ------------- --------- ------------ --------- 98 INACTIVE 1987011 09-JAN-24 2007014 09-JAN-24 99 INACTIVE 2007014 09-JAN-24 2043247 10-JAN-24 100 CURRENT 2043247 10-JAN-24 2.8147E+14 SQL> SELECT current_scn FROM v$database; CURRENT_SCN ----------- 2050111 # Log Switch 발생 SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> SELECT sequence#,status,first_change#,first_time,next_change#,next_time FROM v$log; SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------------- ------------- --------- ------------ --------- 101 ACTIVE 2050547 10-JAN-24 2050566 10-JAN-24 102 ACTIVE 2050566 10-JAN-24 2050576 10-JAN-24 103 CURRENT 2050576 10-JAN-24 2.8147E+14 SQL> SELECT name,status,checkpoint_change# FROM v$datafile; NAME STATUS CHECKPOINT_CHANGE# -------------------------------------------------- ------- ------------------ /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 2050547 /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 2050547 /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 2050547 /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 2050547 /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE 2050547 # 장애 유발 SQL> ! rm /u01/app/oracle/oradata/ora11g/system01.dbf --alert log 에서는 바로 확인 가능 --Wed Jan 10 12:05:00 2024 --Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_12576.trc: --ORA-01116: error in opening database file 1 --ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf' --ORA-27041: unable to open file --Linux Error: 2: No such file or directory --Additional information: 3 # Log Switch 발생 SQL> ALTER SYSTEM CHECKPOINT; SQL> ALTER SYSTEM CHECKPOINT; SQL> ALTER SYSTEM CHECKPOINT; System altered. ?? 작업가능? 왜 고장안남? >> 타이밍이 다를 수 있다. 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' --Wed Jan 10 12:08:22 2024 --ALTER DATABASE OPEN --Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw0_12661.trc: --ORA-01157: cannot identify/lock data file 1 - see DBWR trace file --ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf' --ORA-27037: unable to obtain file status --Linux Error: 2: No such file or directory --Additional information: 3 --Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_12697.trc: --ORA-01157: cannot identify/lock data file 1 - see DBWR trace file --ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf' --ORA-1157 signalled during: ALTER DATABASE OPEN... # System Datafile Backup Restore [oracle@oracle ~]$ cp -av /home/oracle/backup/noarch/20240110/system01.dbf /u01/app/oracle/oradata/ora11g # 마지막 백업 이후에 변경 이력정보가 없어서 완전복구는 할 수 없다. SQL> RECOVER DATABASE; SP2-0734: unknown command beginning "SQL> recov..." - rest of line ignored. SQL> recover database; ORA-00279: change 2022364 generated at 01/10/2024 10:45:51 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_10/o1_mf_1_99_%u_.arc -- archive log mode에서 생성되는 파일ORA-00280: change 2022364 for thread 1 is in sequence #99 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} AUTO ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_10/o1_mf_1_99_%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_10/o1_mf_1_99_%u_.arc' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 SQL> SHUTDOWN ABORT ORACLE instance shut down. # 완전 복구 실패 시 불안전한 복구 방식을 수행 # 모든 Data File, Control File, Redo Log File, Temp File 을 Restore 해야 한다. [oracle@oracle 20240110]$ cp -av /home/oracle/backup/noarch/20240110/*.* /u01/app/oracle/oradata/ora11g SQL> STARTUP SQL> SELECT SEQUENCE#,STATUS,FIRST_CHANGE#,FIRST_TIME,NEXT_CHANGE#,NEXT_TIME FROM v$log; SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------------- ------------- --------- ------------ --------- 98 INACTIVE 1987011 09-JAN-24 2007014 09-JAN-24 99 CURRENT 2007014 09-JAN-24 2.8147E+14 97 INACTIVE 1987008 09-JAN-24 1987011 09-JAN-24 SQL> SELECT name,status,checkpoint_change# FROM v$datafile; NAME STATUS CHECKPOINT_CHANGE# -------------------------------------------------- ------- ------------------ /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 2022367 /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 2022367 /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 2022367 /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 2022367 /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE 2022367 SQL> SELECT current_scn FROM v$database; CURRENT_SCN ----------- 2022737 >> 백업 당시 시점 SEQUENCE# 99, SCN 2022367 |
|
| # Undo Tablespace 내 수행 중인 트랜젝션 정보 확인 |
|
| << HR SESSION >> UPDATE hr.employees SET salary = 2000 WHERE employee_id = 100; --Wed Jan 10 14:00:45 2024 --Thread 1 advanced to log sequence 100 (LGWR switch) -- Current log# 3 seq# 100 mem# 0: /u01/app/oracle/oradata/ora11g/redo03.log << SYS SESSION >> 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 173 169 _SYSSMU9_2700358368$ 9 6 370 1 # 즉시 SESSION 연결해제 ALTER SYSTEM KILL SESSION '173,169' IMMEDIATE; > 'SID, SERIAL#' > 롤백작업은 PMON --Immediate Kill Session#: 181, Serial#: 489 --Immediate Kill Session: sess: 0x499217d4 OS pid: 15266 << HR SESSION >> 다시 접속 UPDATE hr.employees SET salary = 2000 WHERE employee_id = 100; << SYS SESSION >> 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 173 171 _SYSSMU6_390113981$ 6 6 958 1 |
|
| << 시나리오 8 >> UNDO Data File 손상 | |
| SELECT a.file#, b.name tbs_name, a.name file_name, a.status, a.checkpoint_change# FROM v$datafile a, v$tablespace b WHERE a.ts# = b.ts#; FILE# TBS_NAME FILE_NAME STATUS CHECKPOINT_CHANGE# ------- --------- -------------------------------------------------- ------- ------------------ 1 SYSTEM /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 2022367 2 SYSAUX /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 2022367 4 USERS /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 2022367 5 EXAMPLE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 2022367 6 UNDOTBS /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE 2022367 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 1 _SYSSMU1_4132649891$ PUBLIC UNDOTBS ONLINE 2 _SYSSMU2_1514679532$ PUBLIC UNDOTBS ONLINE 3 _SYSSMU3_1655534222$ PUBLIC UNDOTBS ONLINE 4 _SYSSMU4_1128038038$ PUBLIC UNDOTBS ONLINE 5 _SYSSMU5_4280394726$ PUBLIC UNDOTBS ONLINE 6 _SYSSMU6_390113981$ PUBLIC UNDOTBS ONLINE 7 _SYSSMU7_1058177814$ PUBLIC UNDOTBS ONLINE 8 _SYSSMU8_4054980522$ PUBLIC UNDOTBS ONLINE 9 _SYSSMU9_2700358368$ PUBLIC UNDOTBS ONLINE 10 _SYSSMU10_4227184396$ PUBLIC UNDOTBS ONLINE SELECT sequence#,status,first_change#,first_time,next_change#,next_time FROM v$log; SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------------- ------------- --------- ------------ --------- 98 INACTIVE 1987011 09-JAN-24 2007014 09-JAN-24 99 INACTIVE 2007014 09-JAN-24 2033478 10-JAN-24 100 CURRENT 2033478 10-JAN-24 2.8147E+14 SQL> SELECT name,status,checkpoint_change# FROM v$datafile; NAME STATUS CHECKPOINT_CHANGE# -------------------------------------------- ------- ------------------ /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 2033478 /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 2033478 /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 2033478 /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 2033478 /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE 2033478 # 장애 유발 SQL> ! rm /u01/app/oracle/oradata/ora11g/undotbs01.dbf SQL> ALTER SYSTEM CHECKPOINT; SQL> ALTER SYSTEM CHECKPOINT; SQL> ALTER SYSTEM CHECKPOINT; >> 오류없다?! --alert log에서는 확인 가능 --Wed Jan 10 14:41:14 2024 --Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_j000_16033.trc: --ORA-12012: error on auto execute of job 3 --ORA-01116: error in opening database file 6 --ORA-01110: data file 6: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' --ORA-27041: unable to open file --Linux Error: 2: No such file or directory --Additional information: 3 >>> 원래 즉시 DB 내려가야 함 SQL> SHUTDOWN ABORT SQL> STARTUP Database mounted. ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' SQL> SELECT name,status,checkpoint_change# FROM v$datafile; NAME STATUS CHECKPOINT_CHANGE# -------------------------------------------------- ------- ------------------ /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 2035364 /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 2035364 /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 2035364 /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 2035364 /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE 2035364 SQL> ALTER DATABASE DATAFILE 6 OFFLINE FOR DROP; SQL> SELECT name,status,checkpoint_change# FROM v$datafile; NAME STATUS CHECKPOINT_CHANGE# -------------------------------------------------- ------- ------------------ /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 2035364 /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 2035364 /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 2035364 /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 2035364 /u01/app/oracle/oradata/ora11g/undotbs01.dbf RECOVER 2035364 SQL> SELECT count(*) FROM hr.employees; COUNT(*) ---------- 106 >> 조회는 가능하지만 DML 불가 상태 SQL> ALTER DATABASE OPEN; # 신규생성 CREATE UNDO TABLESPACE undo1 DATAFILE '/u01/app/oracle/oradata/ora11g/undo01.dbf' SIZE 10M AUTOEXTEND ON; SQL> SHOW PARAMETER undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS SELECT * FROM v$parameter WHERE name = 'undo_tablespace'; ISSES_MODIFIABLE : FALSE -- ALTER SYSTEM 만 가능 ISSYS_MODIFIABLE : IMMEDIATE -- 즉시 적용가능 ALTER SYSTEM SET undo_tablespace = undo1; SQL> SHOW PARAMETER undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDO1 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 1 _SYSSMU1_4132649891$ PUBLIC UNDOTBS NEEDS RECOVERY 2 _SYSSMU2_1514679532$ PUBLIC UNDOTBS NEEDS RECOVERY 3 _SYSSMU3_1655534222$ PUBLIC UNDOTBS NEEDS RECOVERY 4 _SYSSMU4_1128038038$ PUBLIC UNDOTBS NEEDS RECOVERY 5 _SYSSMU5_4280394726$ PUBLIC UNDOTBS NEEDS RECOVERY 6 _SYSSMU6_390113981$ PUBLIC UNDOTBS NEEDS RECOVERY 7 _SYSSMU7_1058177814$ PUBLIC UNDOTBS NEEDS RECOVERY 8 _SYSSMU8_4054980522$ PUBLIC UNDOTBS NEEDS RECOVERY 9 _SYSSMU9_2700358368$ PUBLIC UNDOTBS NEEDS RECOVERY 10 _SYSSMU10_4227184396$ PUBLIC UNDOTBS NEEDS RECOVERY 11 _SYSSMU11_3789594579$ PUBLIC UNDO1 ONLINE 12 _SYSSMU12_538368877$ PUBLIC UNDO1 ONLINE 13 _SYSSMU13_413883034$ PUBLIC UNDO1 ONLINE 14 _SYSSMU14_3838592153$ PUBLIC UNDO1 ONLINE 15 _SYSSMU15_1160803266$ PUBLIC UNDO1 ONLINE 16 _SYSSMU16_3156064909$ PUBLIC UNDO1 ONLINE 17 _SYSSMU17_117691824$ PUBLIC UNDO1 ONLINE 18 _SYSSMU18_632451427$ PUBLIC UNDO1 ONLINE 19 _SYSSMU19_29658220$ PUBLIC UNDO1 ONLINE 20 _SYSSMU20_3940470073$ PUBLIC UNDO1 ONLINE SQL> DROP TABLESPACE undotbs INCLUDING CONTENTS AND DATAFILES; ORA-01548: active rollback segment '_SYSSMU1_4132649891$' found, terminate dropping tables >> UNDO SEGMENT - NEEDS RECOVERY 상태에서는 DROP 불가 # Pfile 생성 SQL> CREATE PFILE FROM SPFILE; SQL> SHUTDOWN IMMEDIATE [oracle@oracle dbs]$ pwd /u01/app/oracle/product/11.2.0.4/db_1/dbs # UNDO SEGMENT를 수동으로 OFFLINE 상태로 만들기 [oracle@oracle dbs]$ vi initora11g.ora _offline_rollback_segments=( _SYSSMU1_4132649891$, _SYSSMU2_1514679532$, _SYSSMU3_1655534222$, _SYSSMU4_1128038038$, _SYSSMU5_4280394726$, _SYSSMU6_390113981$, _SYSSMU7_1058177814$, _SYSSMU8_4054980522$, _SYSSMU9_2700358368$, _SYSSMU10_4227184396$) :wq SQL> STARTUP PFILE='$ORACLE_HOME/dbs/initora11g.ora' SQL> DROP TABLESPACE undotbs INCLUDING CONTENTS AND DATAFILES; 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 11 _SYSSMU11_3789594579$ PUBLIC UNDO1 ONLINE 12 _SYSSMU12_538368877$ PUBLIC UNDO1 ONLINE 13 _SYSSMU13_413883034$ PUBLIC UNDO1 ONLINE 14 _SYSSMU14_3838592153$ PUBLIC UNDO1 ONLINE 15 _SYSSMU15_1160803266$ PUBLIC UNDO1 ONLINE 16 _SYSSMU16_3156064909$ PUBLIC UNDO1 ONLINE 17 _SYSSMU17_117691824$ PUBLIC UNDO1 ONLINE 18 _SYSSMU18_632451427$ PUBLIC UNDO1 ONLINE 19 _SYSSMU19_29658220$ PUBLIC UNDO1 ONLINE 20 _SYSSMU20_3940470073$ PUBLIC UNDO1 ONLINE |
|
| # UNDO Tablespace 수정 후 System Data File 손상 |
|
| SQL> SHUTDOWN IMMEDIATE [oracle@oracle 20240110]$ cd /u01/app/oracle/oradata/ora11g # 장애 유발 : [oracle@oracle ora11g]$ rm *.* # DataFile 모두 Restore [oracle@oracle ~]$ cp -av /home/oracle/backup/noarch/20240110/*.* /u01/app/oracle/oradata/ora11g SQL> startup ... Database mounted. ORA-01092: ORACLE instance terminated. Disconnection forced ORA-30012: undo tablespace 'UNDO1' does not exist or of wrong type Process ID: 18042 Session ID: 9 Serial number: 3 SQL> conn / as sysdba Connected to an idle instance. [oracle@oracle dbs]$ pwd /u01/app/oracle/product/11.2.0.4/db_1/dbs # PFILE에서 Undo Tablespace 정보 수정 [oracle@oracle dbs]$ vi initora11g.ora *.undo_tablespace='UNDOTBS' : 수정 SQL> STARTUP PFILE='$ORACLE_HOME/dbs/initora11g.ora' SQL> SELECT name,status,checkpoint_change# FROM v$datafile; NAME STATUS CHECKPOINT_CHANGE# -------------------------------------------------- ------- ------------------ /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 2042372 /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 2042372 /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 2042372 /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 2042372 /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE 2042372 SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS SQL> SHOW PARAMETER spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string # PFILE로 오픈 후 SPFILE 생성 SQL> CREATE SPFILE FROM PFILE; 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 |
|
| << 시나리오 9 >> 모든 파일이 있는 디스크 손상 (Pfile 보유) | |
| # 준비 작업 : 정기적인 백업 생활화 |
|
| # System 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 SELECT name FROM v$tempfile; NAME -------------------------------------------------- /u01/app/oracle/oradata/ora11g/temp01.dbf SELECT name FROM $logfile; MEMBER -------------------------------------------------- /u01/app/oracle/oradata/ora11g/redo01.log /u01/app/oracle/oradata/ora11g/redo02.log /u01/app/oracle/oradata/ora11g/edo03.log SELECT name FROM v$controlfile; NAME -------------------------------------------------- /u01/app/oracle/oradata/ora11g/control01.ctl SQL> SHOW PARAMETER spfile NAME TYPE VALUE ----------------------- ----------- ---------------------------------------------------------- spfile string /u01/app/oracle/product/11.2.0.4/db_1/dbs/spfileora11g.ora # PFILE 생성(백업) SQL> CREATE PFILE FROM SPFILE; # 백업본 체크 [oracle@oracle 20240110]$ pwd /home/oracle/backup/noarch/20240110 [oracle@oracle 20240110]$ ls control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf # Data File, Temp File, Redo Log File 위치 변경 작업 준비 ALTER DATABASE RENAME FILE '이전 파일' TO '새로운 파일'; SELECT 'ALTER DATABASE RENAME FILE '''||name||''' TO ''/home/oracle/ora_data'||substr(name,instr(name,'/',-1))||''';' FROM v$datafile UNION ALL SELECT 'ALTER DATABASE RENAME FILE '''||name||''' TO ''/home/oracle/ora_data'||substr(name,instr(name,'/',-1))||''';' FROM v$tempfile UNION ALL SELECT 'ALTER DATABASE RENAME FILE '''||member||''' TO ''/home/oracle/ora_data'||substr(member,instr(member,'/',-1))||''';' FROM v$logfile; ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/system01.dbf' TO '/home/oracle/ora_data/system01.dbf'; ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/sysaux01.dbf' TO '/home/oracle/ora_data/sysaux01.dbf'; ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/users01.dbf' TO '/home/oracle/ora_data/users01.dbf'; ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/example01.dbf' TO '/home/oracle/ora_data/example01.dbf'; ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' TO '/home/oracle/ora_data/undotbs01.dbf'; ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/temp01.dbf' TO '/home/oracle/ora_data/temp01.dbf'; ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/redo01.log' TO '/home/oracle/ora_data/redo01.log'; ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/redo02.log' TO '/home/oracle/ora_data/redo02.log'; ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/redo03.log' TO '/home/oracle/ora_data/redo03.log'; # Control File 위치 변경 작업 준비 초기 파리미터 파일 중 Pfile을 수정해서 수행예정 [oracle@oracle dbs]$ pwd /u01/app/oracle/product/11.2.0.4/db_1/dbs [oracle@oracle dbs]$ vi initora11g.ora *.control_files='/u01/app/oracle/oradata/ora11g/control01.ctl' ↓ *.control_files='/home/oracle/ora_data/control01.ctl' |
|
| # 장애 복구 | |
| # 장애 유발 [oracle@oracle 20240110]$ cd /u01/app/oracle/oradata/ora11g [oracle@oracle ora11g]$ rm *.* SQL> ALTER SYSTEM CHECKPOINT; ORA-03113: end-of-file on communication channel Process ID: 19507 Session ID: 9 Serial number: 3 SQL> conn / as sysdba Connected to an idle instance. SQL> exit [oracle@oracle 20240110]$ pwd /home/oracle/backup/noarch/20240110 [oracle@oracle 20240110]$ ls control01.ctl redo01.log sysaux01.dbf undotbs01.dbf example01.dbf redo02.log system01.dbf users01.dbf initora11g_20240110.ora redo03.log temp01.dbf [oracle@oracle ~]$ mkdir /home/oracle/ora_data # 다른 디스크로 백업본 복사 [oracle@oracle ora_data]$ cp -av /home/oracle/backup/noarch/20240110/*.* /home/oracle/ora_data [oracle@oracle ora_data]$ pwd /home/oracle/ora_data # 여기는 다른 디스크이다. [oracle@oracle ora_data]$ ls control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf [oracle@oracle ora_data]$ cd $ORACLE_HOME/dbs # 초기파라미터 파일의 컨트롤 파일의 위치 정보 수정 [oracle@oracle dbs]$ vi initora11g.ora *.control_files='/home/oracle/ora_data/control01.ctl' :위치 수정 [oracle@oracle ~]$ sqlplus / as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options >> DB 연결되어있다??? SQL> SELECT status FROM v$instance; STATUS ------------ STARTED SQL> STARTUP PFILE='$ORACLE_HOME/dbs/initora11g.ora' MOUNT ORA-01081: cannot start already-running ORACLE - shut it down first SQL> SHUTDOWN ABORT ORACLE instance shut down. # 데이터베이스를 마운트까지 올려놓고 데이터파일, 리두로그파일 위치 변경하기 SQL> STARTUP PFILE='$ORACLE_HOME/dbs/initora11g.ora' MOUNT Database mounted. SQL> select status from v$instance; STATUS ------------ MOUNTED SQL> show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /home/oracle/ora_data/control01.ctl # 데이터파일, 리두로그파일 새로운 위치로 변경 ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/system01.dbf' TO '/home/oracle/ora_data/system01.dbf'; ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/sysaux01.dbf' TO '/home/oracle/ora_data/sysaux01.dbf'; ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/users01.dbf' TO '/home/oracle/ora_data/users01.dbf'; ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/example01.dbf' TO '/home/oracle/ora_data/example01.dbf'; ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' TO '/home/oracle/ora_data/undotbs01.dbf'; ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/temp01.dbf' TO '/home/oracle/ora_data/temp01.dbf'; ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/redo01.log' TO '/home/oracle/ora_data/redo01.log'; ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/redo02.log' TO '/home/oracle/ora_data/redo02.log'; ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/redo03.log' TO '/home/oracle/ora_data/redo03.log'; SQL> ALTER DATABASE OPEN; SELECT * FROM v$datafile; SELECT tablespace_name,file_name FROM dba_data_files; TABLESPACE_NAME FILE_NAME ------------------------------ -------------------------------------------------- UNDOTBS /home/oracle/ora_data/undotbs01.dbf USERS /home/oracle/ora_data/users01.dbf SYSAUX /home/oracle/ora_data/sysaux01.dbf SYSTEM /home/oracle/ora_data/system01.dbf EXAMPLE /home/oracle/ora_data/example01.dbf SELECT * FROM v$tempfile; SELECT tablespace_name,file_name FROM dba_temp_files; TABLESPACE_NAME FILE_NAME ------------------------------ -------------------------------------------------- TEMP /home/oracle/ora_data/temp01.dbf SELECT member FROM v$logfile; MEMBER -------------------------------------------------- /home/oracle/ora_data/redo01.log /home/oracle/ora_data/redo02.log /home/oracle/ora_data/redo03.log SELECT * FROM v$log; SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------------- ------------- --------- ------------ --------- 98 INACTIVE 1987011 09-JAN-24 2007014 09-JAN-24 99 CURRENT 2007014 09-JAN-24 2.8147E+14 97 INACTIVE 1987008 09-JAN-24 1987011 09-JAN-24 SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string CREATE SPFILE FROM PFILE; 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 |
|
| # System Data File 위치 복구 | |
| # System Data File 위치 확인 SELECT name FROM v$datafile UNION ALL SELECT name FROM v$tempfile UNION ALL SELECT member FROM v$logfile UNION ALL SELECT name FROM v$controlfile; 현재 데이터 디렉터리 : /home/oracle/ora_data/ 변경할 디렉터리 : /u01/app/oracle/oradata/ora11g/ # Data File, Temp File, Redo Log File 위치 변경 작업 준비 SELECT 'ALTER DATABASE RENAME FILE '''||name||''' TO ''/u01/app/oracle/oradata/ora11g'||substr(name,instr(name,'/',-1))||''';' FROM v$datafile UNION ALL SELECT 'ALTER DATABASE RENAME FILE '''||name||''' TO ''/u01/app/oracle/oradata/ora11g'||substr(name,instr(name,'/',-1))||''';' FROM v$tempfile UNION ALL SELECT 'ALTER DATABASE RENAME FILE '''||member||''' TO ''/u01/app/oracle/oradata/ora11g'||substr(member,instr(member,'/',-1))||''';' FROM v$logfile; SQL> SHOW PARAMETER spfile NAME TYPE VALUE ----------------------- ----------- ---------------------------------------------------------- spfile string /u01/app/oracle/product/11.2.0.4/db_1/dbs/spfileora11g.ora # PFILE 생성(백업) SQL> CREATE PFILE FROM SPFILE; # 정상종료 SQL> SHUTDOWN IMMEDIATE # 기존 System Data File 위치 [oracle@oracle ~]$ cd /home/oracle/ora_data/ [oracle@oracle ora_data]$ ls control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf [oracle@oracle ora_data]$ cd /u01/app/oracle/oradata/ora11g/ [oracle@oracle ora11g]$ ll total 0 # 신규 디렉터리 위치로 데이터 복사 [oracle@oracle ora_data]$ cp -av /home/oracle/ora_data/*.* /u01/app/oracle/oradata/ora11g/ [oracle@oracle ora11g]$ ls control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf [oracle@oracle ora11g]$ cd $ORACLE_HOME/dbs # 초기파라미터 파일의 컨트롤 파일의 위치 정보 수정 [oracle@oracle dbs]$ vi initora11g.ora *.control_files='/u01/app/oracle/oradata/ora11g/control01.ctl' : 위치 정보 수정 # DB Mount 단계에서 Data File, Redo Log File, Temp File 위치 변경하기 SQL> STARTUP PFILE='$ORACLE_HOME/dbs/initora11g.ora' MOUNT SQL> show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /u01/app/oracle/oradata/ora11g/control01.ctl # 데이터파일, 리두로그파일 새로운 위치로 변경 ALTER DATABASE RENAME FILE '/home/oracle/ora_data/system01.dbf' TO '/u01/app/oracle/oradata/ora11g/system01.dbf'; ALTER DATABASE RENAME FILE '/home/oracle/ora_data/sysaux01.dbf' TO '/u01/app/oracle/oradata/ora11g/sysaux01.dbf'; ALTER DATABASE RENAME FILE '/home/oracle/ora_data/users01.dbf' TO '/u01/app/oracle/oradata/ora11g/users01.dbf'; ALTER DATABASE RENAME FILE '/home/oracle/ora_data/example01.dbf' TO '/u01/app/oracle/oradata/ora11g/example01.dbf'; ALTER DATABASE RENAME FILE '/home/oracle/ora_data/undotbs01.dbf' TO '/u01/app/oracle/oradata/ora11g/undotbs01.dbf'; ALTER DATABASE RENAME FILE '/home/oracle/ora_data/temp01.dbf' TO '/u01/app/oracle/oradata/ora11g/temp01.dbf'; ALTER DATABASE RENAME FILE '/home/oracle/ora_data/redo01.log' TO '/u01/app/oracle/oradata/ora11g/redo01.log'; ALTER DATABASE RENAME FILE '/home/oracle/ora_data/redo02.log' TO '/u01/app/oracle/oradata/ora11g/redo02.log'; ALTER DATABASE RENAME FILE '/home/oracle/ora_data/redo03.log' TO '/u01/app/oracle/oradata/ora11g/redo03.log'; # DB Open SQL> ALTER DATABASE OPEN; SELECT tablespace_name,file_name FROM dba_data_files UNION ALL SELECT tablespace_name,file_name FROM dba_temp_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 TEMP /u01/app/oracle/oradata/ora11g/temp01.dbf 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 SQL> SELECT sequence#,status,first_change#,first_time,next_change#,next_time FROM v$log; SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------------- ------------- --------- ------------ --------- 98 INACTIVE 1987011 09-JAN-24 2007014 09-JAN-24 99 CURRENT 2007014 09-JAN-24 2.8147E+14 97 INACTIVE 1987008 09-JAN-24 1987011 09-JAN-24 SQL> SHOW PARAMETER spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string SQL> CREATE SPFILE FROM PFILE; 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 |
|
테이블스페이스에 대한 모든 정보 기록 - 엑셀
------------------------------------------------------------------------------------------
★ v$log_history 체크
SELECT * FROM v$log_history;
------------------------------------------------------------------------------------------
/*
SELECT * FROM v$session;
SELECT * FROM v$transaction;
SELECT * FROM v$rollname;
SELECT * FROM dba_rollback_segs;
*/
------------------------------------------------------------------------------------------
SELECT * FROM v$parameter WHERE name = 'undo_tablespace';
ISSES_MODIFIABLE : FALSE -- ALTER SYSTEM 만 가능
ISSYS_MODIFIABLE : IMMEDIATE -- 즉시 적용가능
ALTER SYSTEM SET 파라미터 = 값 SCORE = SPFILE | MEMORY | BOTH;
SCORE 기본값 ???
ISSYS_MODIFIABLE : IMMEDIATE SCORE
------------------------------------------------------------------------------------------
substr(name,instr(name,'/',-1))
------------------------------------------------------------------------------------------
SELECT * FROM dba_log_files;
'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 |
| 61 Backup&Recovery, Noarchive Log, Data File (0) | 2024.01.09 |
| 60 Backup (1) | 2024.01.08 |