| # 요약 | |
| << 시나리오 16 >> data file, redo log file 손상되지 않고 control file 손상. (cold Backup O) << 시나리오 16 Re Test >> ControlFile 장애. Cold Backup O << 시나리오 17 >> datafile, redo log file 손상되지 않고 control file 손상 << 시나리오 18 >> datafile, control file 손상 << 시나리오 19 >> system data file, control file 손상 << 시나리오 20 >> redo log file, control file 손상 |
|
<< 시나리오 16 >> data file, redo log file 손상되지 않고 control file 손상. (cold Backup O)
select * from v$database;
desc v$database
SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- --------------- ------------- ----------- ------------ ---------
1 1 YES INACTIVE 1165188 17-JAN-24 1167175 17-JAN-24
1 2 NO CURRENT 1167175 17-JAN-24 2.8147E+14
1 0 YES UNUSED 0 0
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- --------------- ------- -------------------------------------------------- ----------------------
1 ONLINE /u01/app/oracle/oradata/ora11g/redo01.log NO
2 ONLINE /u01/app/oracle/oradata/ora11g/redo02.log NO
3 ONLINE /u01/app/oracle/oradata/ora11g/redo03.log NO
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 1167175
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1167175
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1167175
4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1167175
5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1167175
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
SELECT name,sequence#,first_change#,first_time,next_change#,next_time
FROM v$archived_log;
NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
----------------------------------------------- ---------- ------------- ----------- ------------ ---------
/home/oracle/arch1/arch_1_1_1158511351.arc 1 1165188 17-JAN-24 1167175 17-JAN-24
alter system archive log current;
desc v$database
SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- --------------- ------------- ----------- ------------ ---------
1 1 YES INACTIVE 1165188 17-JAN-24 1167175 17-JAN-24
1 2 YES ACTIVE 1167175 17-JAN-24 1176934 18-JAN-24
1 3 NO CURRENT 1176934 18-JAN-24 2.8147E+14
create table hr.emp_20 as select * from hr.employees where department_id = 20;
select count(*) from hr.emp_20;
COUNT(*)
----------
2
SQL> shutdown immediate
[oracle@oracle ora11g]$ cd /u01/app/oracle/oradata/ora11g
[oracle@oracle ora11g]$ ls
control01.ctl example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
# 장애 발생
! rm /u01/app/oracle/oradata/ora11g/control01.ctl
SQL> startup
ORACLE instance started.
Total System Global Area 711430144 bytes
Fixed Size 1367004 bytes
Variable Size 482346020 bytes
Database Buffers 222298112 bytes
Redo Buffers 5419008 bytes
ORA-00205: error in identifying control file, check alert log for more info
--ALTER DATABASE MOUNT
--ORA-00210: cannot open the specified control file
--ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
--ORA-27037: unable to obtain file status
--Linux Error: 2: No such file or directory
--Additional information: 3
--ORA-205 signalled during: ALTER DATABASE MOUNT...
--Thu Jan 18 09:57:32 2024
--Checker run found 1 new persistent data failures
SQL> select status from v$instance;
STATUS
---------------
STARTED
SQL> shutdown abort
ORACLE instance shut down.
# 마지막 백업 컨트롤 파일을 원래 위치로 복사
! cp -av /home/oracle/backup/arch/cold_20240117/control01.ctl /u01/app/oracle/oradata/ora11g/
SQL> startup
ORACLE instance started.
Total System Global Area 711430144 bytes
Fixed Size 1367004 bytes
Variable Size 482346020 bytes
Database Buffers 222298112 bytes
Redo Buffers 5419008 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'
ORA-01207: file is more recent than control file - old control file
--ALTER DATABASE OPEN
--Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_30794.trc:
--ORA-01122: database file 1 failed verification check
--ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'
--ORA-01207: file is more recent than control file - old control file
--ORA-1122 signalled during: ALTER DATABASE OPEN...
--Thu Jan 18 10:01:54 2024
--Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_30796.trc:
--ORA-00338: log 1 of thread 1 is more recent than control file
--ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ora11g/redo01.log'
--Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_30796.trc:
--ORA-00338: log 1 of thread 1 is more recent than control file
--ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ora11g/redo01.log'
--Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_30796.trc:
--ORA-00338: log 2 of thread 1 is more recent than control file
--ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ora11g/redo02.log'
--Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_30796.trc:
--ORA-00338: log 2 of thread 1 is more recent than control file
--ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ora11g/redo02.log'
--Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_30796.trc:
--ORA-00338: log 3 of thread 1 is more recent than control file
--ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ora11g/redo03.log'
--Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_30796.trc:
--ORA-00338: log 3 of thread 1 is more recent than control file
--ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ora11g/redo03.log'
--Checker run found 1 new persistent data failures
>> 백업본의 컨트롤파일과 기존 데이터 파일간의 checkpoint 불일치, 컨트롤 파일이 오래되었다
> 불안전한 복구 수행
SQL> recover database using backup controlfile;
ORA-00279: change 1166664 generated at 01/17/2024 16:42:31 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_1_1158511351.arc
ORA-00280: change 1166664 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
>> 그냥 엔터
ORA-00279: change 1167175 generated at 01/17/2024 17:04:15 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_2_1158511351.arc
ORA-00280: change 1167175 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arch1/arch_1_1_1158511351.arc' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
>> 그냥 엔터
ORA-00279: change 1176934 generated at 01/18/2024 09:52:21 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158511351.arc
ORA-00280: change 1176934 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arch1/arch_1_2_1158511351.arc' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
>> 그냥 엔터
ORA-00308: cannot open archived log '/home/oracle/arch1/arch_1_3_1158511351.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
>> 파일없다
> 1,2 아카이브파일에서 적용
> 3번은 current 상태 -> 아카이브파일 없다 = 아카이브가 아니다
> current 상태에 해당하는 리두로그파일을 적용하자
!! Cancel Base Recovery 를 해버리면 seq#3에서 작업한 내용이 날라간다! 주의!
> current 상태의 redo log file 정보는 alert log 모니터링 필수
SQL> recover database using backup controlfile;
ORA-00279: change 1176934 generated at 01/18/2024 09:52:21 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158511351.arc
ORA-00280: change 1176934 for thread 1 is in sequence #3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ora11g/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
>> 이거 완전복구 아닌가?? > 싱크가 맞지 않았다 > 불안전 복구 > resetlogs
select count(*) from hr.emp_20;
COUNT(*)
----------
2
SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- --------------- ------------- ----------- ------------ ---------
1 1 NO CURRENT 1177084 18-JAN-24 2.8147E+14
1 0 YES UNUSED 0 0
1 0 YES UNUSED 0 0
>> 컨트롤파일 다중화의 중요성. current 상태를 포함한 alert Log 모니터링
> 불안전한 복구를 한다는건 shutdown 수행 > DB 내린다 > 영업 중 아무때나 할 수 없다.
>> resetlogs로 시작했다는건 새로운 기준이 생긴거라 과거 백업파일로 부분복구 불가 > 백업 필수
###################################################################################################
# Restore : cold_20240117 - DataFile, ControlFile, RedoLogFile
SQL> SHUTDOWN IMMEDIATE
! cp -av /home/oracle/backup/arch/cold_20240117/*.dbf /u01/app/oracle/oradata/ora11g/
! cp -av /home/oracle/backup/arch/cold_20240117/*.ctl /u01/app/oracle/oradata/ora11g/
! cp -av /home/oracle/backup/arch/cold_20240117/*.log /u01/app/oracle/oradata/ora11g/
*/
SQL> STARTUP
-- ...
--ALTER DATABASE OPEN
--LGWR: STARTING ARCH PROCESSES
--Thu Jan 18 10:31:59 2024
--ARC0 started with pid=21, OS id=31391
--ARC0: Archival started
--LGWR: STARTING ARCH PROCESSES COMPLETE
--ARC0: STARTING ARCH PROCESSES
--Thu Jan 18 10:31:59 2024
--ARC1 started with pid=22, OS id=31393
--Thu Jan 18 10:31:59 2024
--ARC2 started with pid=23, OS id=31395
--Thread 1 opened at log sequence 1
-- Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/ora11g/redo01.log v
--Successful open of redo thread 1
-- ...
SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change#
FROM v$datafile a, v$tablespace b
WHERE a.ts# = b.ts#;
FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ---------- --------------- ------------------
1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 1166667
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1166667
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1166667
4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1166667
5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1166667
ALTER SYSTEM ARCHIVE LOG CURRENT;
--Thread 1 advanced to log sequence 2 (LGWR switch)
-- Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/ora11g/redo02.log
SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- --------------- ------------- ----------- ------------ ---------
1 1 YES ACTIVE 1165188 17-JAN-24 1167240 18-JAN-24
1 2 NO CURRENT 1167240 18-JAN-24 2.8147E+14
1 0 YES UNUSED 0 0
SELECT name,sequence#,first_change#,first_time,next_change#,next_time
FROM v$archived_log;
NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
----------------------------------------------- ---------- ------------- ----------- ------------ ---------
/home/oracle/arch1/arch_1_1_1158511351.arc 1 1165188 17-JAN-24 1167240 18-JAN-24
SQL> ! ls /home/oracle/arch*
arch_1_1_1158511351.arc
###################################################################################################
<< 시나리오 16 Re Test >> ControlFile 장애. Cold Backup O
/home/oracle/backup/arch/cold_20240117:
control01.ctl example01.dbf initora11g_20240117.ora redo01.log redo02.log
redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
/home/oracle/backup/arch/hot_20240117:
control01.ctl example01.dbf sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
select name from v$controlfile;
NAME
-----------------------------------------------
/u01/app/oracle/oradata/ora11g/control01.ctl
# 장애 발생
! rm /u01/app/oracle/oradata/ora11g/control01.ctl
SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- --------------- ------------- ----------- ------------ ---------
1 1 YES ACTIVE 1165188 17-JAN-24 1167240 18-JAN-24
1 2 NO CURRENT 1167240 18-JAN-24 2.8147E+14
1 0 YES UNUSED 0 0
ALTER SYSTEM ARCHIVE LOG CURRENT;
--Thread 1 advanced to log sequence 3 (LGWR switch)
-- Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/ora11g/redo03.log
--Archived Log entry 28 added for thread 1 sequence 2 ID 0xf7af1ec dest 1:
SQL> SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- --------------- ------------- ----------- ------------ ---------
1 1 YES INACTIVE 1165188 17-JAN-24 1167240 18-JAN-24
1 2 YES ACTIVE 1167240 18-JAN-24 1168323 18-JAN-24
1 3 NO CURRENT 1168323 18-JAN-24 2.8147E+14
SQL> shutdown immediate
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
--...
--Thread 1 closed at log sequence 3
--Successful close of redo thread 1
--Completed: ALTER DATABASE CLOSE NORMAL
--ALTER DATABASE DISMOUNT
--Shutting down archive processes
--Archiving is disabled
--ORA-210 signalled during: ALTER DATABASE DISMOUNT...
SQL> select status from v$instance;
STATUS
---------------
MOUNTED
! cp -av /home/oracle/backup/arch/cold_20240117/control01.ctl /u01/app/oracle/oradata/ora11g/
SQL> alter database open;
ORA-16196: database has been previously opened and closed
SQL> shutdown abort
SQL> startup
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'
ORA-01207: file is more recent than control file - old control file
SQL> select status from v$instance;
STATUS
---------------
MOUNTED
> 컨트롤파일은 깨졌지만 데이터 손상없이 복구할거다
SQL> recover database using backup controlfile;
ORA-00279: change 1166664 generated at 01/17/2024 16:42:31 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_1_1158511351.arc
ORA-00280: change 1166664 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 1167240 generated at 01/18/2024 10:33:37 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_2_1158511351.arc
ORA-00280: change 1167240 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arch1/arch_1_1_1158511351.arc' no longer needed for this recovery
ORA-00279: change 1168323 generated at 01/18/2024 10:52:27 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158511351.arc
ORA-00280: change 1168323 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arch1/arch_1_2_1158511351.arc' no longer needed for this recovery
ORA-00308: cannot open archived log '/home/oracle/arch1/arch_1_3_1158511351.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
>> seq#3에대한 아카이브 파일 없다
> 여기서 cancel하면 current 상태의 seq#3에서 작업한게 날라간다.
> seq#3 정보가 들어있는 redo log file로 적용하자.
SQL> recover database using backup controlfile;
ORA-00279: change 1168323 generated at 01/18/2024 10:52:27 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158511351.arc
ORA-00280: change 1168323 for thread 1 is in sequence #3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ora11g/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
>> 살아났다
###################################################################################################
# Restore : cold_20240117 - DataFile, ControlFile, RedoLogFile
SQL> SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- --------------- ------------- ----------- ------------ ---------
1 1 YES INACTIVE 1165188 17-JAN-24 1166999 18-JAN-24
1 2 YES INACTIVE 1166999 18-JAN-24 1167238 18-JAN-24
1 3 NO CURRENT 1167238 18-JAN-24 2.8147E+14
SQL> SELECT name,sequence#,first_change#,first_time,next_change#,next_time
FROM v$archived_log;
2
NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
----------------------------------------------- ---------- ------------- ----------- ------------ ---------
/home/oracle/arch1/arch_1_1_1158511351.arc 1 1165188 17-JAN-24 1166999 18-JAN-24
/home/oracle/arch1/arch_1_2_1158511351.arc 2 1166999 18-JAN-24 1167238 18-JAN-24
SQL> ! ls /home/oracle/arch*
arch_1_1_1158511351.arc arch_1_2_1158511351.arc
###################################################################################################
<< 시나리오 17 >> datafile, redo log file 손상되지 않고 control file 손상
control file 백업본으로 손상복구 후
SQL> SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- --------------- ------------- ----------- ------------ ---------
1 4 NO CURRENT 1167696 18-JAN-24 2.8147E+14
1 2 YES INACTIVE 1166999 18-JAN-24 1167238 18-JAN-24
1 3 YES ACTIVE 1167238 18-JAN-24 1167696 18-JAN-24
SQL> create table hr.emp_30 as select * from hr.employees where department_id = 30;
SQL> select count(*) from hr.emp_30;
COUNT(*)
----------
6
# 장애 발생
! rm /u01/app/oracle/oradata/ora11g/control01.ctl
SQL> alter system switch logfile;
ORA-03113: end-of-file on communication channel
Process ID: 32157
Session ID: 9 Serial number: 3
--Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_lgwr_32137.trc:
--ORA-00210: cannot open the specified control file
--ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
--ORA-27041: unable to open file
--Linux Error: 2: No such file or directory
--Additional information: 3
--LGWR (ospid: 32137): terminating the instance due to error 210
--Thu Jan 18 11:23:44 2024
--System state dump requested by (instance=1, osid=32137 (LGWR)), summary=[abnormal instance termination].
--System State dumped to trace file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_diag_32125_20240118112344.trc
--Dumping diagnostic data in directory=[cdmp_20240118112344], requested by (instance=1, osid=32137 (LGWR)), summary=[abnormal instance termination].
--Instance terminated by LGWR, pid = 32137
>> 또는 DB가 끊기지 않아 접속된 상태에서 일하고 있는 경우도 있다
! cp -av /home/oracle/backup/arch/cold_20240117/control01.ctl /u01/app/oracle/oradata/ora11g/
SQL> startup mount
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'
ORA-01207: file is more recent than control file - old control file
>> 복구실패
>> 컨트롤 파일만 깨진건데? seq# 그대로 쓰고 싶다
# 컨트롤 파일 재생성 후 데이터베이스 시작
SQL> select status from v$instance;
STATUS
---------------
MOUNTED
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/home/oracle/new_control.sql';
> 현재 컨트롤파일을 재생성할 수 있는 sql 파일 가이드 생성
> 구조가 바뀔 때마다 백업처럼 TRACE 해놓자 ★
SQL> shutdown abort
# 생성된 가이드 파일에서 내용 복사
SQL> ! vi /home/oracle/new_control.sql
-- Set #1. NORESETLOGS case
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS ARCHIVELOG
-- ORA11G DB이름 유지, NORESETLOGS로 오픈, ARCHIVELOG 모드로 재생성
MAXLOGFILES 16 -- 리드로그 그룹 최대 개수, 사이클에 따라 조정 ☆
MAXLOGMEMBERS 3 -- 한 그룹 당 멤버 최대 수 (기본 2, ~3)
MAXDATAFILES 100 -- 생성 가능한 데이터파일 최대 개수 ☆
MAXINSTANCES 8 -- 8코어
MAXLOGHISTORY 292 -- log History 라인 최대 수 ☆
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/ora11g/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/ora11g/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/ora11g/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/ora11g/system01.dbf',
'/u01/app/oracle/oradata/ora11g/sysaux01.dbf',
'/u01/app/oracle/oradata/ora11g/undotbs01.dbf',
'/u01/app/oracle/oradata/ora11g/users01.dbf',
'/u01/app/oracle/oradata/ora11g/example01.dbf'
CHARACTER SET AL32UTF8
;
:q!
# 신규 sql파일 생성
SQL> ! vi /home/oracle/create_control.spl
그대로 붙여넣기
:wq
# 실행
SQL> @/home/oracle/create_control.spl
★ 컨트롤파일 재생성하는 경우
- db 이름 바꿀 때
- max 값 수정 시
- 컨트롤파일 손상
SQL> recover database;
Media recovery complete.
--Media Recovery Start
-- started logmerger process
--Parallel Media Recovery started with 2 slaves
--Thu Jan 18 11:49:42 2024
--Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
-- Mem# 0: /u01/app/oracle/oradata/ora11g/redo03.log
--Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0
-- Mem# 0: /u01/app/oracle/oradata/ora11g/redo01.log
--Media Recovery Complete (ora11g)
--Completed: ALTER DATABASE RECOVER database
> 여기서 no recovery required 발생 할 경우 > 리커버리 할게 없다 > DB OPEN
> 애당초 control file 재생성 후 리커버리 할 필요가 없다 > 바로 DB 오픈하면 된다.
SQL> alter database open;
--...
--Completed crash recovery at
-- Thread 1: logseq 4, block 988, scn 1188231
-- 0 data blocks read, 0 data blocks written, 493 redo k-bytes read
--Initializing SCN for created control file
--Database SCN compatibility initialized to 3
--Warning - High Database SCN: Current SCN value is 1188232, threshold SCN value is 43827084524772
--...
--Thread 1 advanced to log sequence 5 (thread open)
--Thread 1 opened at log sequence 5
-- Current log# 2 seq# 5 mem# 0: /u01/app/oracle/oradata/ora11g/redo02.log
--...
--Tablespace 'TEMP' #3 found in data dictionary,
--...
--WARNING: The following temporary tablespaces contain no files.
-- This condition can occur when a backup controlfile has
-- been restored. It may be necessary to add files to these
-- tablespaces. That can be done using the SQL statement:
--
-- ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
--
-- Alternatively, if these temporary tablespaces are no longer
-- needed, then they can be dropped.
-- Empty temporary tablespace: TEMP
--..
>> Alert Log에 Temp가 없다는 내용이 나온다?
>재생성한 trace 파일 안에 db open 후 temp 재생성하라는 내용이 있다.
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ora11g/temp01.dbf' REUSE;
-- End of tempfile additions.
SQL> SELECT * FROM v$tempfile;
no rows selected
# 컨트롤 파일 재생성 후 템프파일 생성
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ora11g/temp01.dbf' REUSE;
> REUSE : 기존 파일 그대로 사용
SQL> SELECT * FROM v$tempfile;
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- ------------- ---------- ---------- --------------- ----------- ---------- ---------- ------------ ---------- -----------------------------------------------
1 1195765 18-JAN-24 3 1 ONLINE READ WRITE 20971520 2560 20971520 8192 /u01/app/oracle/oradata/ora11g/temp01.dbf
> 컨트롤 파일에는 리두로그파일만 보고 생성하기 때문에 템프 정보가 없다
> 컨트롤파일을 재생성 할 수 있는 sql파일안에도 주석 내용이 있으니 참고!
> 리두 정보가 살아있고 데이터파일 살아있으니 기존 seq 정보 그대로 사용한다
SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- --------------- ------------- ----------- ------------ ---------
1 4 YES INACTIVE 1167696 18-JAN-24 1188233 18-JAN-24
1 5 NO CURRENT 1188233 18-JAN-24 2.8147E+14
1 3 YES INACTIVE 1167238 18-JAN-24 1167696 18-JAN-24
SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change#
FROM v$datafile a, v$tablespace b
WHERE a.ts# = b.ts#;
FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ---------- --------------- ------------------
1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 1188234
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1188234
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1188234
4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1188234
5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1188234
SELECT name,sequence#,first_change#,first_time,next_change#,next_time
FROM v$archived_log;
NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
----------------------------------------------- ---------- ------------- ----------- ------------ ---------
/home/oracle/arch1/arch_1_2_1158511351.arc 2 1166999 18-JAN-24 1167238 18-JAN-24
/home/oracle/arch1/arch_1_3_1158511351.arc 3 1167238 18-JAN-24 1167696 18-JAN-24
/home/oracle/arch1/arch_1_4_1158511351.arc 4 1167696 18-JAN-24 1188233 18-JAN-24
SQL> ! ls /home/oracle/arch1
arch_1_1_1158511351.arc arch_1_2_1158511351.arc arch_1_3_1158511351.arc arch_1_4_1158511351.arc
SQL> select count(*) from hr.emp_30;
COUNT(*)
----------
6
>> 컨트롤파일 손상 ? 무조건 DB shutdown - mount 하기 때문에 DB운영중지 주의
# 다중화
show parameter control_file
ALTER SYSTEM SET control_files = '' SCOPE=SPFILE
shutdown immediate
cp -av 기존파일 신규파일이름변경
startup
show parameter control_file
###################################################################################################
# Restore : cold_20240117 - DataFile, ControlFile, RedoLogFile
SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change#
FROM v$datafile a, v$tablespace b
WHERE a.ts# = b.ts#;
FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ---------- --------------- ------------------
1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 1166667
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1166667
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1166667
4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1166667
5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1166667
ALTER SYSTEM ARCHIVE LOG CURRENT;
SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- --------------- ------------- ----------- ------------ ---------
1 1 YES ACTIVE 1165188 17-JAN-24 1167772 18-JAN-24
1 2 NO CURRENT 1167772 18-JAN-24 2.8147E+14
1 0 YES UNUSED 0 0
SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
ORDER BY 1;
GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS
---------- ---------- -------------------------------------------------- ---------- --------- ---------------
1 1 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES ACTIVE
2 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 NO CURRENT
3 0 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES UNUSED
SELECT name,sequence#,first_change#,first_time,next_change#,next_time
FROM v$archived_log;
NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
----------------------------------------------- ---------- ------------- ----------- ------------ ---------
/home/oracle/arch1/arch_1_1_1158511351.arc 1 1165188 17-JAN-24 1167772 18-JAN-24
SQL> ! ls /home/oracle/arch1
arch_1_1_1158511351.arc
###################################################################################################
SQL> create table hr.emp_50 as select * from hr.employees where department_id = 50;
SQL> select count(*) from hr.emp_50;
COUNT(*)
----------
45
SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- --------------- ------------- ----------- ------------ ---------
1 1 YES ACTIVE 1165188 17-JAN-24 1167772 18-JAN-24
1 2 NO CURRENT 1167772 18-JAN-24 2.8147E+14
1 0 YES UNUSED 0 0
alter system switch logfile;
--Thread 1 advanced to log sequence 3 (LGWR switch)
-- Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/ora11g/redo03.log
--Thu Jan 18 14:52:20 2024
--Archived Log entry 28 added for thread 1 sequence 2 ID 0xf7af1ec dest 1:
alter system switch logfile;
--Thread 1 cannot allocate new log, sequence 4
--Checkpoint not complete
-- Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/ora11g/redo03.log
--Thread 1 advanced to log sequence 4 (LGWR switch)
-- Current log# 1 seq# 4 mem# 0: /u01/app/oracle/oradata/ora11g/redo01.log
--Thu Jan 18 14:52:23 2024
--Expanded controlfile section 11 from 28 to 56 records
--Requested to grow by 28 records; added 1 blocks of records
--Archived Log entry 29 added for thread 1 sequence 3 ID 0xf7af1ec dest 1:
>> 딜레이가 생긴다
- 리두로그파일 사이즈가 작다
- 그룹이 적어 사이클이 빨리온다
SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- --------------- ------------- ----------- ------------ ---------
1 4 NO CURRENT 1170212 18-JAN-24 2.8147E+14
1 2 YES ACTIVE 1167772 18-JAN-24 1170209 18-JAN-24
1 3 YES ACTIVE 1170209 18-JAN-24 1170212 18-JAN-24
SELECT name,sequence#,first_change#,first_time,next_change#,next_time
FROM v$archived_log;
NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
----------------------------------------------- ---------- ------------- ----------- ------------ ---------
/home/oracle/arch1/arch_1_1_1158511351.arc 1 1165188 17-JAN-24 1167772 18-JAN-24
/home/oracle/arch1/arch_1_2_1158511351.arc 2 1167772 18-JAN-24 1170209 18-JAN-24
/home/oracle/arch1/arch_1_3_1158511351.arc 3 1170209 18-JAN-24 1170212 18-JAN-24
SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
ORDER BY 1;
GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS
---------- ---------- -------------------------------------------------- ---------- --------- ---------------
1 4 /u01/app/oracle/oradata/ora11g/redo01.log 50 NO CURRENT
2 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE
3 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES INACTIVE
<< 시나리오 18 >> datafile, control file 손상
# 장애 발생
! rm /u01/app/oracle/oradata/ora11g/*.dbf
! rm /u01/app/oracle/oradata/ora11g/*.ctl
*/
--Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_3943.trc:
--ORA-00210: cannot open the specified control file
--ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
--ORA-27041: unable to open file
--Linux Error: 2: No such file or directory
--Additional information: 3
select status from v$instance;
OPEN
SQL> alter system switch logfile;
ORA-03113: end-of-file on communication channel
Process ID: 3636
Session ID: 9 Serial number: 3
>> 다른 사람들은 open 상태에서 작동 됨
SQL> select status from v$instance;
ORA-03114: not connected to ORACLE
SQL> conn / as sysdba
SQL> startup
ORA-00205: error in identifying control file, check alert log for more info
SQL> select status from v$instance;
STATUS
---------------
STARTED
SQL> ! ls /u01/app/oracle/oradata/ora11g
redo01.log redo02.log redo03.log
SQL> shutdown abort
! cp -av /home/oracle/backup/arch/cold_20240117/*.dbf /u01/app/oracle/oradata/ora11g/
! cp -av /home/oracle/backup/arch/cold_20240117/*.ctl /u01/app/oracle/oradata/ora11g/
*/
SQL> startup mount
SQL> recover database using backup controlfile
ORA-00279: change 1166664 generated at 01/17/2024 16:57:54 needed for thread 1 -- 백업파일 정보
ORA-00289: suggestion : /home/oracle/arch1/arch_1_1_1158511351.arc
ORA-00280: change 1166664 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
<< 엔터
ORA-00279: change 1167772 generated at 01/18/2024 14:49:18 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_2_1158511351.arc
ORA-00280: change 1167772 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arch1/arch_1_1_1158511351.arc' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
<< 엔터
ORA-00279: change 1170209 generated at 01/18/2024 14:52:19 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158511351.arc
ORA-00280: change 1170209 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arch1/arch_1_2_1158511351.arc' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
<< 엔터
ORA-00279: change 1170212 generated at 01/18/2024 14:52:23 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_4_1158511351.arc
ORA-00280: change 1170212 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle/arch1/arch_1_3_1158511351.arc' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
<< 엔터
ORA-00308: cannot open archived log '/home/oracle/arch1/arch_1_4_1158511351.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
>>> seq#4 파일이 없다 > current 상태였던 것 > 해당하는 redo log file을 찾다 > alert log 참고
SQL> recover database using backup controlfile
ORA-00279: change 1170212 generated at 01/18/2024 14:52:23 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_4_1158511351.arc
ORA-00280: change 1170212 for thread 1 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ora11g/redo01.log
Log applied.
Media recovery complete.
alter database open resetlogs;
>> open resetlogs > 기존 백업본에서 부분복구 불가. 무조건 신규 백업 하자.
SQL> select count(*) from hr.emp_50;
COUNT(*)
----------
45
>> 아카이브 파일 + 리두 로그파일 => 최근 정보까지 복구 완료
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> 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#;
2 3
FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ---------- --------------- ------------------
1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 1170538
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1170538
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1170538
4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1170538
5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1170538
SQL> SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
2
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- --------------- ------------- ----------- ------------ ---------
1 1 YES ACTIVE 1170535 18-JAN-24 1171138 18-JAN-24
1 2 NO CURRENT 1171138 18-JAN-24 2.8147E+14
1 0 YES UNUSED 0 0
SQL> SELECT name,sequence#,first_change#,first_time,next_change#,next_time
FROM v$archived_log;
2
NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
----------------------------------------------- ---------- ------------- ----------- ------------ ---------
/home/oracle/arch1/arch_1_1_1158592636.arc 1 1170535 18-JAN-24 1171138 18-JAN-24
SQL> ! ls /home/oracle/arch1
arch_1_1_1158592636.arc
###################################################################################################
# Restore : cold_20240117 - DataFile, ControlFile, RedoLogFile
###################################################################################################
SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change#
FROM v$datafile a, v$tablespace b
WHERE a.ts# = b.ts#;
FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ---------- --------------- ------------------
1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 1166667
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1166667
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1166667
4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1166667
5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1166667
SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- --------------- ------------- ----------- ------------ ---------
1 1 YES ACTIVE 1165188 17-JAN-24 1166963 18-JAN-24
1 2 NO CURRENT 1166963 18-JAN-24 2.8147E+14
1 0 YES UNUSED 0 0
SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
ORDER BY 1;
GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS
---------- ---------- -------------------------------------------------- ---------- --------- ---------------
1 1 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES ACTIVE
2 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 NO CURRENT
3 0 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES UNUSED
SELECT name,sequence#,first_change#,first_time,next_change#,next_time
FROM v$archived_log;
NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
----------------------------------------------- ---------- ------------- ----------- ------------ ---------
/home/oracle/arch1/arch_1_1_1158511351.arc 1 1165188 17-JAN-24 1167772 18-JAN-24
###################################################################################################
<< 시나리오 19 >> system data file, control file 손상
SQL> create table hr.emp_50 as select * from hr.employees where department_id = 50;
SQL> select count(*) from hr.emp_50;
COUNT(*)
----------
45
SQL> alter system switch logfile;
--Thread 1 advanced to log sequence 3 (LGWR switch)
-- Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/ora11g/redo03.log
--Thu Jan 18 15:35:45 2024
--Archived Log entry 28 added for thread 1 sequence 2 ID 0xf7af1ec dest 1:
# 장애 발생
! rm /u01/app/oracle/oradata/ora11g/system01.dbf
! rm /u01/app/oracle/oradata/ora11g/control01.ctl
>> 작업 중에 ERROR:
ORA-00604: error occurred at recursive SQL level 1
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
>> alert log
--Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_4818.trc:
--ORA-00210: cannot open the specified control file
--ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
--ORA-27041: unable to open file
--Linux Error: 2: No such file or directory
--Additional information: 3
--Thu Jan 18 15:37:06 2024
--Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw1_4664.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
--DBW1 (ospid: 4664): terminating the instance due to error 1116
--Thu Jan 18 15:37:07 2024
--System state dump requested by (instance=1, osid=4664 (DBW1)), summary=[abnormal instance termination].
--System State dumped to trace file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_diag_4654_20240118153707.trc
--Dumping diagnostic data in directory=[cdmp_20240118153707], requested by (instance=1, osid=4664 (DBW1)), summary=[abnormal instance termination].
--Instance terminated by DBW1, pid = 4664
SQL> select status from v$instance;
SP2-0640: Not connected
SQL> conn / as sysdba
SQL> select status from v$instance;
ORA-01034: ORACLE not available
SQL> ! ls /u01/app/oracle/oradata/ora11g
example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf temp01.dbf undotbs01.dbf users01.dbf
! cp -av /home/oracle/backup/arch/cold_20240117/system01.dbf /u01/app/oracle/oradata/ora11g/
! cp -av /home/oracle/backup/arch/cold_20240117/control01.ctl /u01/app/oracle/oradata/ora11g/
SQL> startup mount
SQL> recover database using backup controlfile
ORA-00279: change 1166664 generated at 01/17/2024 16:57:54 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_1_1158511351.arc
ORA-00280: change 1166664 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 1166963 generated at 01/18/2024 15:32:07 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_2_1158511351.arc
ORA-00280: change 1166963 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arch1/arch_1_1_1158511351.arc' no longer needed for this recovery
ORA-00279: change 1170265 generated at 01/18/2024 15:35:44 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158511351.arc
ORA-00280: change 1170265 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arch1/arch_1_2_1158511351.arc' no longer needed for this recovery
ORA-00308: cannot open archived log '/home/oracle/arch1/arch_1_3_1158511351.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
# 최근자료까지 복구하기 위해 current 상태에 해당하는 redo log file 위치를 찾아 적용
SQL> recover database using backup controlfile
ORA-00279: change 1170265 generated at 01/18/2024 15:35:44 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158511351.arc
ORA-00280: change 1170265 for thread 1 is in sequence #3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ora11g/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open;
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open NORESETLOGS;
ORA-01588: must use RESETLOGS option for database open
SQL> alter database open RESETLOGS;
SQL> select count(*) from hr.emp_50;
COUNT(*)
----------
45
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
--Thread 1 advanced to log sequence 2 (LGWR switch)
-- Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/ora11g/redo02.log
--Archived Log entry 30 added for thread 1 sequence 1 ID 0xf7cdd9c dest 1:
SQL> 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 1170296
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1170296
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1170296
4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1170296
5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1170296
SQL> SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- --------------- ------------- ----------- ------------ ---------
1 1 YES ACTIVE 1170293 18-JAN-24 1170771 18-JAN-24
1 2 NO CURRENT 1170771 18-JAN-24 2.8147E+14
1 0 YES UNUSED 0 0
SQL> SELECT name,sequence#,first_change#,first_time,next_change#,next_time
FROM v$archived_log;
NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
----------------------------------------------- ---------- ------------- ----------- ------------ ---------
/home/oracle/arch1/arch_1_1_1158594544.arc 1 1170293 18-JAN-24 1170771 18-JAN-24
30 rows selected.
SQL> ! ls /home/oracle/arch1
arch_1_1_1158594544.arc
arch_1_1_1158511351.arc arch_1_2_1158511351.arc arch_1_3_1158511351.arc
###################################################################################################
# Restore : cold_20240117 - DataFile, ControlFile, RedoLogFile
###################################################################################################
! cp -av /home/oracle/backup/arch/cold_20240117/*.dbf /u01/app/oracle/oradata/ora11g/
! cp -av /home/oracle/backup/arch/cold_20240117/*.ctl /u01/app/oracle/oradata/ora11g/
! cp -av /home/oracle/backup/arch/cold_20240117/*.log /u01/app/oracle/oradata/ora11g/
*/
SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change#
FROM v$datafile a, v$tablespace b
WHERE a.ts# = b.ts#;
FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ---------- --------------- ------------------
1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 1166667
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1166667
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1166667
4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1166667
5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1166667
SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- --------------- ------------- ----------- ------------ ---------
1 1 YES ACTIVE 1165188 17-JAN-24 1166963 18-JAN-24
1 2 NO CURRENT 1166963 18-JAN-24 2.8147E+14
1 0 YES UNUSED 0 0
SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
ORDER BY 1;
GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS
---------- ---------- -------------------------------------------------- ---------- --------- ---------------
1 1 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES ACTIVE
2 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 NO CURRENT
3 0 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES UNUSED
SELECT name,sequence#,first_change#,first_time,next_change#,next_time
FROM v$archived_log;
NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
----------------------------------------------- ---------- ------------- ----------- ------------ ---------
/home/oracle/arch1/arch_1_1_1158511351.arc 1 1165188 17-JAN-24 1167772 18-JAN-24
! ls /home/oracle/arch1
###################################################################################################
<< 시나리오 20 >> redo log file, control file 손상
- redo log 손상
1. archieve log가 있으면 복구 가능
2. current group > archive log 미생성 > 무조건 cancel base recovery, 과거로 가야한다, 완전복구 불가
* cold backup 필수 control file + data file
옵션 redo log file
- restore control file + data file
- recover database until cancel using backup controlfile
1. archive 적용
2. current group > cancel
- 완전복구불가 > resetlogs > 신규 백업
SQL> select count(*) from hr.emp_50;
COUNT(*)
----------
45
SQL> create table hr.emp_40 as select * from hr.employees where department_id = 50;
SQL> select count(*) from hr.emp_40;
COUNT(*)
----------
45
SQL> alter system switch logfile;
--Thread 1 advanced to log sequence 3 (LGWR switch)
-- Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/ora11g/redo03.log
--Archived Log entry 31 added for thread 1 sequence 2 ID 0xf7cdd9c dest 1:
SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- --------------- ------------- ----------- ------------ ---------
1 1 YES INACTIVE 1170293 18-JAN-24 1170771 18-JAN-24
1 2 YES ACTIVE 1170771 18-JAN-24 1172238 18-JAN-24
1 3 NO CURRENT 1172238 18-JAN-24 2.8147E+14
# 장애 유발
! rm /u01/app/oracle/oradata/ora11g/*.log
! rm /u01/app/oracle/oradata/ora11g/*.ctl
*/
alter system switch logfile;
--Thread 1 advanced to log sequence 4 (LGWR switch)
-- Current log# 1 seq# 4 mem# 0: /u01/app/oracle/oradata/ora11g/redo01.log
--Thu Jan 18 16:16:56 2024
--Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_arc2_4971.trc:
--ORA-00313: open failed for members of log group 3 of thread 1
--ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ora11g/redo03.log'
--ORA-27037: unable to obtain file status
--Linux Error: 2: No such file or directory
--Additional information: 3
--Master archival failure: 313
--ARCH: Archival stopped, error occurred. Will continue retrying
--ORACLE Instance ora11g - Archival Error
--ORA-00313: open failed for members of log group 3 of thread 1
--ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ora11g/redo03.log'
--ORA-27037: unable to obtain file status
--Linux Error: 2: No such file or directory
--Additional information: 3
--Thu Jan 18 16:16:56 2024
--Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_5492.trc:
--ORA-00210: cannot open the specified control file
--ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
--ORA-27041: unable to open file
--Linux Error: 2: No such file or directory
--Additional information: 3
SQL> ! ls /u01/app/oracle/oradata/ora11g/
example01.dbf sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
SQL> shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
! cp -av /home/oracle/backup/arch/cold_20240117/*.dbf /u01/app/oracle/oradata/ora11g/
! cp -av /home/oracle/backup/arch/cold_20240117/*.ctl /u01/app/oracle/oradata/ora11g/
*/
>> 과거로 cold Back ...
SQL> startup mount
SQL> recover database until cancel using backup controlfile
ORA-00279: change 1166664 generated at 01/17/2024 16:57:54 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_1_1158511351.arc
ORA-00280: change 1166664 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 1166963 generated at 01/18/2024 15:32:07 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_2_1158511351.arc
ORA-00280: change 1166963 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arch1/arch_1_1_1158511351.arc' no longer needed for this recovery
ORA-00279: change 1170265 generated at 01/18/2024 15:35:44 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158511351.arc
ORA-00280: change 1170265 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arch1/arch_1_2_1158511351.arc' no longer needed for this recovery
Log applied.
Media recovery complete.
>> until cancel : cancel base recovery, 과거로 가야한다
> redo log file이 없으니 current한 부분은 복구 불가
SQL> recover database until cancel using backup controlfile
ORA-00279: change 1170292 generated at 01/18/2024 15:36:12 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158511351.arc
ORA-00280: change 1170292 for thread 1 is in sequence #3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
--Clearing online redo logfile 1 /u01/app/oracle/oradata/ora11g/redo01.log
--Clearing online log 1 of thread 1 sequence number 1
--Clearing online redo logfile 1 complete
> 리두로그파일 삭제 후 재생성
> seq#1부터 다시 기록
(참고) DB 운영중 - Redo Log File을 삭제 후 재생성
alter database clear unarchived logfile group 2;
SQL> select count(*) from hr.emp_50;
COUNT(*)
----------
45
SQL> select count(*) from hr.emp_40;
ORA-00942: table or view does not exist
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 1170296
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1170296
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1170296
4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1170296
5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1170296
ALTER SYSTEM ARCHIVE LOG CURRENT;
--Thread 1 advanced to log sequence 2 (LGWR switch)
-- Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/ora11g/redo02.log
--Archived Log entry 27 added for thread 1 sequence 1 ID 0xf7c93f9 dest 1:
SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- --------------- ------------- ----------- ------------ ---------
1 1 YES ACTIVE 1170293 18-JAN-24 1171127 18-JAN-24
1 2 NO CURRENT 1171127 18-JAN-24 2.8147E+14
1 0 YES UNUSED 0 0
SELECT name,sequence#,first_change#,first_time,next_change#,next_time
FROM v$archived_log;
NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
----------------------------------------------- ---------- ------------- ----------- ------------ ---------
/home/oracle/arch1/arch_1_1_1158597346.arc 1 1170293 18-JAN-24 1171127 18-JAN-24
'Oracle 11g > Backup & Recovery' 카테고리의 다른 글
| 70 Backup&Recovery, Log Miner, Clone DB, Export & Import (0) | 2024.01.22 |
|---|---|
| 69 Backup&Recovery, Archive Log Mode (0) | 2024.01.19 |
| 67 Backup&Recovery, Archive Log Mode, (0) | 2024.01.17 |
| 66 Backup&Recovery, Archive Log Mode, Backup (0) | 2024.01.16 |
| 65 Backup&Recovery, Archive Log Mode (0) | 2024.01.15 |