You've got to find what you love.

# Control File 장애 복구

ORACLE instance started.

Total System Global Area  369098752 bytes
Fixed Size                  1280276 bytes
Variable Size              92276460 bytes
Database Buffers          272629760 bytes
Redo Buffers                2912256 bytes
Database mounted.
Database open

SQL>startup -> parameter file (no mount) -> control file (mount) -> data/redo log file (open)
- 위에서 본 것처럼 startup 명령어를 치면 parameter file을 읽어서 instance를 생성하고 그 후에 control file의 위치를 확인해서 메모리로 불러들이게 됩니다.
이 순서를 기억하면서 아래에 contron file 관련 장애를 자세하게 살펴보겠습니다.

1. control file 장애 1 - 경로가 틀린 경우
- contron file 의 위치는 parameter file에 있는데 parameter file에 있는 control file의 위치와 실제 control file의 위치가 다를 경우입니다.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>!
-bash-3.00$ cat /app/oracle/oraapp/product/10.2.0/dbs/inittestdb.ora
-bash-3.00$ rm -f /app/oracle/oradata/oracle10/control03.ctl
-bash-3.00$ ls /app/oracle/oradata/oracle10/control03.ctl
                control03.ctl: No such file or directory
-bash-3.00$ exit

SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  369098752 bytes
Fixed Size                  1280276 bytes
Variable Size              92276460 bytes
Database Buffers          272629760 bytes
Redo Buffers                2912256 bytes
ORA-00205: error in identifying control file, check alert log for more info -- 에러 발생

-- Alert_testdb.log 파일을 확인
Sun May 15 16:32:04 2011
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=998
Sun May 15 16:32:04 2011
starting up 1 shared server(s) ...
Sun May 15 16:32:05 2011
ALTER DATABASE   MOUNT
Sun May 15 16:32:05 2011
ORA-00202: Message 202 not found; No message file for product=RDBMS, facility=ORA; arguments: [/app/oracle/oradata/oracle10/control03.ctl]
ORA-27037: Message 27037 not found; No message file for product=RDBMS, facility=ORA
Intel SVR4 UNIX Error: 2: No such file or directory
Additional information: 3
Sun May 15 16:32:08 2011
ORA-205 signalled during: ALTER DATABASE   MOUNT...

-- 이런 장애가 생긴 이유는 parameter file 에는 control03.ctl이 있다고 적어두고 실제 파일은 없어서 그런 것입니다.
-- Database를 startup하게 되면 서버 프로세스가 지정된 경로에서 파라미터 파일을 찾아서 읽고 인스턴스 생성 등의 작업을 한 후 parameter file에 적혀 있는 경로대로 control file을 찾으러 가게 됩니다.
지금 발생한 에러는 parameter file에 있는 경로 모두에 control file 이 있어야 하는 데 control03.ctl 파일이 없기 때문에 발생한 것입니다.
해결 방법은 parameter file의 내용을 수정하든지 control03.ctl을 만들어주던지 하면 됩니다.
여기선 control03.ctl을 만들도록 합니다.

SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> !cp /app/oracle/oradata/oracle10/control01.ctl /app/oracle/oradata/oracle10/control03.ctl

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

2. control file 장애 2 - Version 에러 복구하기
- 모든 컨트롤 파일끼리는 정보가 동일해야 합니다. 두 번째 장애는 컨트롤 파일끼리 정보가 달라서 발생하는 version error를 봅니다.

# 현재 상태 확인

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/app/oracle/oradata/oracle10/control01.ctl
/app/oracle/oradata/oracle10/control02.ctl
/app/oracle/oradata/oracle10/control03.ctl

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
bash-3.00$ vi /app/oracle/oraapp/product/10.2.0/dbs/initora10g.ora
...
...
###########################################
# File Configuration
###########################################
control_files=("/app/oracle/oradata/oracle10/control01.ctl"
                , "/app/oracle/oradata/oracle10/control02.ctl")
        #       , "/app/oracle/oradata/oracle10/control03.ctl") -- 사용 못하게 주석처리


-- 즉 control03.ctl 은 사용 못하게 설정을 변경.

SQL> startup
ORACLE instance started.

Total System Global Area  369098752 bytes
Fixed Size                  1280276 bytes
Variable Size             113247980 bytes
Database Buffers          251658240 bytes
Redo Buffers                2912256 bytes
Database mounted.
Database opened.
SQL>
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/app/oracle/oradata/oracle10/control01.ctl
/app/oracle/oradata/oracle10/control02.ctl

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
bash-3.00$ pwd
/export/home/oracle
bash-3.00$ vi $ORACLE_HOME/dbs/initora10g.ora

###########################################
# File Configuration
###########################################
control_files=("/app/oracle/oradata/oracle10/control01.ctl"
                , "/app/oracle/oradata/oracle10/control02.ctl"
                , "/app/oracle/oradata/oracle10/control03.ctl")

"/app/oracle/oraapp/product/10.2.0/dbs/initora10g.ora" 87 lines, 2650 characters
bash-3.00$
bash-3.00$ exit

SQL> startup
ORACLE instance started.

Total System Global Area  369098752 bytes
Fixed Size                  1280276 bytes
Variable Size             113247980 bytes
Database Buffers          251658240 bytes
Redo Buffers                2912256 bytes
ORA-00214: control file '/app/oracle/oradata/oracle10/control01.ctl' version 1203
inconsistent with file '/app/oracle/oradata/oracle10/control03.ctl'  version 1188

-- 위의 에러를 보면 control01.ctl은 버전이 1203인데 control03.ctl은 버전이 1188이라는 에러를 보이고 있습니다.
즉 컨트롤 파일끼리 정보가 달라서 생기는 에러인 것입니다.
이 숫자는 실습할 때 본문과 다르게 나올 수 있습니다.
해결방법은 일반적으로 번호가 큰 파일이 최신이기 때문에 번호가 큰 파일을 작은 파일에 덮어씌면 됩니다.

SQL> !cp /app/oracle/oradata/oracle10/control01.ctl /app/oracle/oradata/oracle10/control03.ctl

SQL> aleter database mount;
SP2-0734: unknown command beginning "aleter dat..." - rest of line ignored.
SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/app/oracle/oradata/oracle10/control01.ctl
/app/oracle/oradata/oracle10/control02.ctl
/app/oracle/oradata/oracle10/control03.ctl



# control file 장애 3(old control file / control file 재생성하기)
-- 현재 상태 확인

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/app/oracle/oradata/oracle10/control01.ctl
/app/oracle/oradata/oracle10/control02.ctl
/app/oracle/oradata/oracle10/control03.ctl

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
bash-3.00$ vi $ORACLE_HOME/dbs/initora10g.ora

###########################################
# File Configuration
###########################################
control_files=("/app/oracle/oradata/oracle10/control01.ctl"
                , "/app/oracle/oradata/oracle10/control02.ctl")
#               , "/app/oracle/oradata/oracle10/control03.ctl")

bash-3.00$ exit
exit

SQL> startup
ORACLE instance started.

Total System Global Area  369098752 bytes
Fixed Size                  1280276 bytes
Variable Size             113247980 bytes
Database Buffers          251658240 bytes
Redo Buffers                2912256 bytes
Database mounted.
Database opened.
SQL>
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/app/oracle/oradata/oracle10/control01.ctl
/app/oracle/oradata/oracle10/control02.ctl

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> !
bash-3.00$ vi $ORACLE_HOME/dbs/initora10g.ora

###########################################
# File Configuration
###########################################
control_files=("/app/oracle/oradata/oracle10/control01.ctl"
                , "/app/oracle/oradata/oracle10/control02.ctl"
                , "/app/oracle/oradata/oracle10/control03.ctl")

bash-3.00$ exit

SQL> startup
ORACLE instance started.

Total System Global Area  369098752 bytes
Fixed Size                  1280276 bytes
Variable Size             113247980 bytes
Database Buffers          251658240 bytes
Redo Buffers                2912256 bytes
ORA-00214: control file '/app/oracle/oradata/oracle10/control01.ctl' version
1231 inconsistent with file '/app/oracle/oradata/oracle10/control03.ctl'
version 1214


SQL> !cp /app/oracle/oradata/oracle10/control03.ctl /app/oracle/oradata/oracle10/control01.ctl

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00214: control file '/app/oracle/oradata/oracle10/control02.ctl' version
1231 inconsistent with file '/app/oracle/oradata/oracle10/control01.ctl'
version 1214


SQL> !cp /app/oracle/oradata/oracle10/control01.ctl /app/oracle/oradata/oracle10/control02.ctl

SQL> alter database mount;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/app/oracle/oradata/oracle10/system01.dbf'
ORA-01207: file is more recent than control file - old control file

 이 에러의 원인은 control file의 체크포인트 정보가 데이터 파일의 체크포인트 정보보다 예전 내용이기 때문입니다.
이 문제를 해결하는 방법은 2가지가 있는데 한가지는 using backup controlfile 옵션으로 recovery 하는 것이고 그 방법이 불가할 경우 부득이하게 control file을 재생성해야 합니다.
정리를 하면 old control file 장애 해결방법은 아래의 두 가지 case 로 구분됩니다.

Case 1. Redo log와 archive log, data file 백업 있을 경우 복구하기 - using bckup control file
Case 2. Redo log와 archive log, data file 백업 없을 경우 복구하기 - 재생성하기


Case 1. Redo log와 archive log, data file 백업 있을 경우 복구하기


1. 전체 백업 수행합니다.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/app/oracle/oradata/oracle10/system01.dbf
/app/oracle/oradata/oracle10/undotbs01.dbf
/app/oracle/oradata/oracle10/sysaux01.dbf
/app/oracle/oradata/oracle10/SCOTT_DATA.dbf
/app/oracle/oradata/oracle10/users01.dbf
/app/oracle/oradata/oracle10/TS_SMS01.dbf
/app/oracle/oradata/oracle10/test01.dbf

7 rows selected.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/app/oracle/oradata/oracle10/control01.ctl
/app/oracle/oradata/oracle10/control02.ctl
/app/oracle/oradata/oracle10/control03.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/app/oracle/oradata/oracle10/redo01.log
/app/oracle/oradata/oracle10/redo02.log
/app/oracle/oradata/oracle10/redo03.log

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

bash-3.00$ cp /app/oracle/oradata/oracle10/* data/backup/close/
bash-3.00$

2. 장애를 발생시킴

-bash-3.00$ vi /app/oracle/oraapp/product/10.2.0/dbs/initora10g.ora

###########################################
# File Configuration
###########################################
control_files=("/app/oracle/oradata/oracle10/control01.ctl"
                , "/app/oracle/oradata/oracle10/control02.ctl")
#               , "/app/oracle/oradata/oracle10/control03.ctl")

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

bash-3.00$ vi $ORACLE_HOME/dbs/initora10g.ora

###########################################
# File Configuration
###########################################
control_files=("/app/oracle/oradata/oracle10/control01.ctl"
                , "/app/oracle/oradata/oracle10/control02.ctl"
                , "/app/oracle/oradata/oracle10/control03.ctl")


SQL> startup
ORACLE instance started.

Total System Global Area  369098752 bytes
Fixed Size                  1280276 bytes
Variable Size             113247980 bytes
Database Buffers          251658240 bytes
Redo Buffers                2912256 bytes
ORA-00214: control file '/app/oracle/oradata/oracle10/control01.ctl' version
1218 inconsistent with file '/app/oracle/oradata/oracle10/control03.ctl'
version 1216


SQL> !cp /app/oracle/oradata/oracle10/control03.ctl /app/oracle/oradata/oracle10/control01.ctl

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00214: control file '/app/oracle/oradata/oracle10/control02.ctl' version
1218 inconsistent with file '/app/oracle/oradata/oracle10/control01.ctl'
version 1216


SQL> !cp /app/oracle/oradata/oracle10/control01.ctl /app/oracle/oradata/oracle10/control02.ctl

SQL> alter database mount;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/app/oracle/oradata/oracle10/system01.dbf'
ORA-01207: file is more recent than control file - old control file

3. 백업 데이터 파일 복원 후 복구 시작

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> !pwd
/export/home/oracle

SQL> !cp /export/home/oracle/data/backup/close/*.dbf /app/oracle/oradata/oracle10/

SQL> startup mount
ORACLE instance started.

Total System Global Area  369098752 bytes
Fixed Size                  1280276 bytes
Variable Size             113247980 bytes
Database Buffers          251658240 bytes
Redo Buffers                2912256 bytes
Database mounted.

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 627967 generated at 05/15/2011 18:04:36 needed for thread 1
ORA-00289: suggestion :
/app/oracle/flash_recovery_area/ORACLE10/archivelog/2011_05_15/o1_mf_1_36_%u_.ar
c
ORA-00280: change 627967 for thread 1 is in sequence #36


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/app/oracle/flash_recovery_area/ORACLE10/archivelog/2011_05_15/o1_mf_1_36_%u_.a
rc'
ORA-27037: unable to obtain file status
Intel SVR4 UNIX Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/app/oracle/flash_recovery_area/ORACLE10/archivelog/2011_05_15/o1_mf_1_36_%u_.a
rc'
ORA-27037: unable to obtain file status
Intel SVR4 UNIX Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/app/oracle/oradata/oracle10/system01.dbf'


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/app/oracle/oradata/oracle10/system01.dbf'


SQL>


-- # 오류 발생
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/app/oracle/oradata/oracle10/system01.dbf'


- hidden parameter 적용
$ORACLE_HOME/dbs/initora10g.ora 파일에 아래를 추가

###########################################
# Controlfile Recreate
###########################################


_offline_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3 $,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
_corrupted_rollback_segments=true
"/app/oracle/oraapp/product/10.2.0/dbs/initora10g.ora" 98 lines, 2965 characters
bash-3.00$ exit

SQL> alter database backup controlfile to trace;

Database altered.

SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /app/oracle/admin/oracle10/udu
                                                 mp

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 627967 generated at 05/15/2011 18:04:36 needed for thread 1
ORA-00289: suggestion :
/app/oracle/flash_recovery_area/ORACLE10/archivelog/2011_05_15/o1_mf_1_36_%u_.ar
c
ORA-00280: change 627967 for thread 1 is in sequence #36


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
*
ORA-00308: cannot open archived log '*'
ORA-27037: unable to obtain file status
Intel SVR4 UNIX Error: 2: No such file or directory
Additional information: 3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
'/app/oracle/flash_recovery_area/ORACLE10/archivelog/2011_05_15/o1_mf_1_36_%u_.a
rc'
ORA-27037: unable to obtain file status
Intel SVR4 UNIX Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/app/oracle/oradata/oracle10/system01.dbf'


SQL> alter database open resetlogs;

Database altered.

SQL>
SQL> alter database open
  2  ;
alter database open
*
ERROR at line 1:
ORA-01531: a database already open by the instance


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  369098752 bytes
Fixed Size                  1280276 bytes
Variable Size             113247980 bytes
Database Buffers          251658240 bytes
Redo Buffers                2912256 bytes
Database mounted.
Database opened.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/app/oracle/oradata/oracle10/system01.dbf
/app/oracle/oradata/oracle10/undotbs01.dbf
/app/oracle/oradata/oracle10/sysaux01.dbf
/app/oracle/oradata/oracle10/SCOTT_DATA.dbf
/app/oracle/oradata/oracle10/users01.dbf
/app/oracle/oradata/oracle10/TS_SMS01.dbf
/app/oracle/oradata/oracle10/test01.dbf

7 rows selected.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/app/oracle/oradata/oracle10/redo01.log
/app/oracle/oradata/oracle10/redo02.log
/app/oracle/oradata/oracle10/redo03.log

SQL> select name from v$controlfile;   

NAME
--------------------------------------------------------------------------------
/app/oracle/oradata/oracle10/control01.ctl
/app/oracle/oradata/oracle10/control02.ctl
/app/oracle/oradata/oracle10/control03.ctl

Case 2. Redo log와 archive log, data file 백업 없을 경우 복구하기 - 재생성하기

Control file을 재생성하는 경우는 몇가지가 있는데 대표적인 경우는
1. control file이 전부 삭제되었을 때
2. old control file 에러 발생시에
3. DB Name 변경하고 싶을 때
4. 최대 데이터 파일 개수와 리두 로그 파일 개수를 변경하고 싶을 때 등입니다.


# Control file 재생성
Control file 을 재생성을 하려며 No mount 상태에서 DB를 재생성하는 명령어를 입력하면 디는데 그 명령어가 너무 길어서 스크립트로 만들어서 생성하는 방법을 사용.
Control file 을 재생성하는 스크립트는 현재 old control file로붜 trace해서 만들게 되며 아래의 방법으로 하면 됩니다.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database backup controlfile to trace as '/export/home/oracle/recon_mount.sql';

Database altered.

SQL> !      
bash-3.00$ vi /export/home/oracle/recon_mount.sql

-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="oracle10"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=2
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_10=ENABLE

--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT -- 여기서부터 실제 필요한 스크립트 부분이므로 이전 까지는 전부 지우세요.
CREATE CONTROLFILE REUSE DATABASE "ORACLE10" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/app/oracle/oradata/oracle10/redo01.log'  SIZE 50M,
  GROUP 2 '/app/oracle/oradata/oracle10/redo02.log'  SIZE 50M,
  GROUP 3 '/app/oracle/oradata/oracle10/redo03.log'  SIZE 50M
-- STANDBY LOGFILE -- 이 부분도 삭제해야 합니다.
                   -- 이 부분도 삭제해야 합니다. 공백이 없어야 합니다.
DATAFILE
  '/app/oracle/oradata/oracle10/system01.dbf',
  '/app/oracle/oradata/oracle10/undotbs01.dbf',
  '/app/oracle/oradata/oracle10/sysaux01.dbf',
  '/app/oracle/oradata/oracle10/SCOTT_DATA.dbf',
  '/app/oracle/oradata/oracle10/users01.dbf',
  '/app/oracle/oradata/oracle10/TS_SMS01.dbf',
  '/app/oracle/oradata/oracle10/test01.dbf'
CHARACTER SET AL32UTF8
; -- noresetlogs 옵션으로 만들 경우라면 여기까지만 필요합니다.

-- Take files offline to match current control file.
ALTER DATABASE DATAFILE '/app/oracle/oradata/oracle10/test01.dbf' OFFLINE DROP;

-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/app/oracle/flash_recovery_area/ORACLE10/archivelog/2011_05_22/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/app/oracle/flash_recovery_area/ORACLE10/archivelog/2011_05_22/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE

-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.
ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/app/oracle/oradata/oracle10/temp01.dbf' REUSE;
ALTER TABLESPACE SCOTT_TEMP ADD TEMPFILE '/app/oracle/oradata/oracle10/SCOTT_TEMP.dbf' REUSE;
ALTER TABLESPACE TEMP_SMS ADD TEMPFILE '/app/oracle/oradata/oracle10/TEMP_SMS01.dbf' REUSE;
-- End of tempfile additions.
--
--     Set #2. RESETLOGS case -- 여긴 resetlogs 옵션에서 사용될 control file을 생성합니다.
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT  -- 여기서부터 실제 필요한 스크립트입니다.
CREATE CONTROLFILE REUSE DATABASE "ORACLE10" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/app/oracle/oradata/oracle10/redo01.log'  SIZE 50M,
  GROUP 2 '/app/oracle/oradata/oracle10/redo02.log'  SIZE 50M,
  GROUP 3 '/app/oracle/oradata/oracle10/redo03.log'  SIZE 50M
-- STANDBY LOGFILE -- 이 부분도 삭제해야 합니다.
                   -- 이 부분도 삭제해야 합니다. 공백이 없어야 합니다.
DATAFILE
  '/app/oracle/oradata/oracle10/system01.dbf',
  '/app/oracle/oradata/oracle10/undotbs01.dbf',
  '/app/oracle/oradata/oracle10/sysaux01.dbf',
  '/app/oracle/oradata/oracle10/SCOTT_DATA.dbf',
  '/app/oracle/oradata/oracle10/users01.dbf',
  '/app/oracle/oradata/oracle10/TS_SMS01.dbf',
  '/app/oracle/oradata/oracle10/test01.dbf'
CHARACTER SET AL32UTF8
;  -- 여기까지만 필요합니다. 여기 밑으로는 전부 지우세요.

-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/app/oracle/flash_recovery_area/ORACLE10/archivelog/2011_05_22/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/app/oracle/flash_recovery_area/ORACLE10/archivelog/2011_05_22/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/app/oracle/oradata/oracle10/temp01.dbf' REUSE;
ALTER TABLESPACE SCOTT_TEMP ADD TEMPFILE '/app/oracle/oradata/oracle10/SCOTT_TEMP.dbf' REUSE;
ALTER TABLESPACE TEMP_SMS ADD TEMPFILE '/app/oracle/oradata/oracle10/TEMP_SMS01.dbf' REUSE;
-- End of tempfile additions.

- 그럼 위 스크립트에서 지금 상황(old control file 에러 발생 또는 모든 컨트롤 파일 삭제됨)에 맞는 control file 을 새로 생성합니다.
이런 상황이라면 resetlogs 옵션으로 DB를 open해야 합니다.
따라서 #2 RESETLOGS 옵션에 있는 스크립트를 이용합니다.

앞부분은 전부 삭제 하고

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORACLE10" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/app/oracle/oradata/oracle10/redo01.log'  SIZE 50M,
  GROUP 2 '/app/oracle/oradata/oracle10/redo02.log'  SIZE 50M,
  GROUP 3 '/app/oracle/oradata/oracle10/redo03.log'  SIZE 50M
DATAFILE
  '/app/oracle/oradata/oracle10/system01.dbf',
  '/app/oracle/oradata/oracle10/undotbs01.dbf',
  '/app/oracle/oradata/oracle10/sysaux01.dbf',
  '/app/oracle/oradata/oracle10/SCOTT_DATA.dbf',
  '/app/oracle/oradata/oracle10/users01.dbf',
  '/app/oracle/oradata/oracle10/TS_SMS01.dbf',
  '/app/oracle/oradata/oracle10/test01.dbf'
CHARACTER SET AL32UTF8
;

뒷부분도 전부 삭제하고 위 내용만 남겨두고 :w /export/home/oracle/recon_mount2.sql 새 이름으로 저장합니다.

# 위 스크립트의 내용 중 주의사항
1. DB가 Reserlogs로 open되는지 No resetlogs로 open 되는지 정확히 구분하세요.
2. 스크립트 안에 주석이나 공백이 있으면 생성 도중에 에러가 남.
3. 스크립트 내부의 Redo log는 실제 파일이 없어도 Resetlogsㄹ open될 때 생성됩니다.
4. 데이터 파일은 반드시 그 경로에 있어야 합니다.
5. control file의 생성 위치는 파라미터 파일에 지정된 경로입니다.
그 경로에 control file이 이미 존재하고 있으면 생성이 안되고 에러 발생.

위 5가지 사항을 주의해서 control file을 재생성해 주세요.

- 스크립트로 control file 생성

SQL> shutdown immediate

SQL> @/export/home/oracle/recon_mount2.sql

SQL> alter database open resetlogs;

SQL> select name from v$controlfile;

# Control file이 재생성되어 DB가 정상적을 Open 되는 것을 확인합니다.
Control file 을 재생성하는 작업은 어렵지는 않지만 신중하여야 합니다.
Control file 을 재생성 하면 RMAN 백업도 사용 못하게 될수 있고 FlashBack 기능도 사용 못하게 될수 있습니다.


14.4 종합 복구 문제
데이터 파일만 백업이 있고 사용 중이던 control file과 Redo log file 전부 다 삭제가 되었을 경우 복구

* 장애 상황 설명
DBA 박현영 엔지니어에게 고객 사이트에서 긴급복구 요청을 했습니다.
현재 상황은 운영 중인 서버의 RAID 장애로 모든 파일(데이터파일, 리두 로그 파일, 컨트롤 파일)이 소실되었으며 현재 가지고 있는 파일은 어제 백업 받았던 전체 데이터 파일과 2개월 전에 백업 받았던 컨트롤 파일밖에 없다고 합니다.
고객 사이트에서는 어제까지의 데이터라도 복구해 달라고 요청을 합니다.
어제의 데이터 파일들과 2개월 전의 컨트롤 파일만 가지고 어제 상태로 복구하세요.

step 1. 현재상태 확인 후 데이터 파일만 백업합니다.
이 작업 전에 미리 컨트로 파일만 백업 받으세요.
즉 데이터 파일보다 컨트롤 파일을 먼저 백업 받아야 합니다.

SQL> select name from v$datafile;  

NAME
--------------------------------------------------------------------------
/app/oracle/oradata/oracle10/system01.dbf
/app/oracle/oradata/oracle10/undotbs01.dbf
/app/oracle/oradata/oracle10/sysaux01.dbf
/app/oracle/oradata/oracle10/SCOTT_DATA.dbf
/app/oracle/oradata/oracle10/users01.dbf
/app/oracle/oradata/oracle10/TS_SMS01.dbf
/app/oracle/oradata/oracle10/test01.dbf

7 rows selected.

SQL> select name from v$controlfile;

NAME
---------------------------------------------------------------------------
/app/oracle/oradata/oracle10/control01.ctl
/app/oracle/oradata/oracle10/control02.ctl
/app/oracle/oradata/oracle10/control03.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------
/app/oracle/oradata/oracle10/redo01.log
/app/oracle/oradata/oracle10/redo02.log
/app/oracle/oradata/oracle10/redo03.log

SQL> !
bash-3.00$ cp /app/oracle/oradata/oracle10/*.dbf /export/home/oracle/data/backup/close/

step 2. 장애 발생
bash-3.00$ rm -f /app/oracle/oradata/oracle10/*.log
bash-3.00$ rm -f /app/oracle/oradata/oracle10/*.ctl

이 상황을 복구합니다.

### 종합 복구 문제 정답 - control file 재생성해서 복구

step 1. 복구를 위한 파일 복원
복구 작업은  /export/home/oracle/data/backup/imsy/ 디렉토리에 하겠습니다.
복구를 위해 필요한 일은 백업 데이터 파일과 예전에 백업 받았던 컨트롤 파일입니다.

-bash-3.00$ mkdir  /export/home/oracle/data/backup/imsy/
-bash-3.00$ cp /export/home/oracle/data/backup/close/*.dbf /export/home/oracle/data/backup/imsy/
-bash-3.00$ cp /export/home/oracle/data/backup/close/*.ctl /export/home/oracle/data/backup/imsy/

step 2. 컨트롤 파일 위치 변경 후 마운트


step 3. Control file 재생성

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> !
bash-3.00$ vi recon_mount2.sql
"recon_mount2.sql" 22 lines, 743 characters 
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORACLE10" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/app/oracle/oradata/oracle10/redo01.log'  SIZE 50M,
  GROUP 2 '/app/oracle/oradata/oracle10/redo02.log'  SIZE 50M,
  GROUP 3 '/app/oracle/oradata/oracle10/redo03.log'  SIZE 50M
DATAFILE
  '/app/oracle/oradata/oracle10/system01.dbf',
  '/app/oracle/oradata/oracle10/undotbs01.dbf',
  '/app/oracle/oradata/oracle10/sysaux01.dbf',
  '/app/oracle/oradata/oracle10/SCOTT_DATA.dbf',
  '/app/oracle/oradata/oracle10/users01.dbf',
  '/app/oracle/oradata/oracle10/TS_SMS01.dbf',
  '/app/oracle/oradata/oracle10/test01.dbf'
CHARACTER SET AL32UTF8
;

데이터 파일만 있으므로 Open 시킬 때 Resetlogs 옵션을 써야 합니다.
즉 Case 2. Resetlogs 옵션으로 사용될 내용만 위와 같이 남기고 다 지운 후 아래와 같이 로그와 데이터 파일 위치를 변경합니다.

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORACLE10" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/export/home/oracle/data/backup/imsy/redo01.log'  SIZE 50M,
  GROUP 2 '/export/home/oracle/data/backup/imsy/redo02.log'  SIZE 50M,
  GROUP 3 '/export/home/oracle/data/backup/imsy/redo03.log'  SIZE 50M
DATAFILE
  '/export/home/oracle/data/backup/imsy/system01.dbf',
  '/export/home/oracle/data/backup/imsy/undotbs01.dbf',
  '/export/home/oracle/data/backup/imsy/sysaux01.dbf',
  '/export/home/oracle/data/backup/imsy/SCOTT_DATA.dbf',
  '/export/home/oracle/data/backup/imsy/users01.dbf',
  '/export/home/oracle/data/backup/imsy/TS_SMS01.dbf',
  '/export/home/oracle/data/backup/imsy/test01.dbf'
CHARACTER SET AL32UTF8
;

SQL> @recon_mount2.sql
ORACLE instance started.

Total System Global Area  369098752 bytes
Fixed Size                  1280276 bytes
Variable Size             113247980 bytes
Database Buffers          251658240 bytes
Redo Buffers                2912256 bytes
CREATE CONTROLFILE REUSE DATABASE "ORACLE10" RESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01189: file is from a different RESETLOGS than previous files
ORA-01110: data file 7: '/export/home/oracle/data/backup/imsy/test01.dbf'

SQL> !vi recon_mount2.sql
"recon_mount2.sql" 22 lines, 823 characters 
  '/export/home/oracle/data/backup/imsy/test01.dbf'
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORACLE10" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/export/home/oracle/data/backup/imsy/redo01.log'  SIZE 50M,
  GROUP 2 '/export/home/oracle/data/backup/imsy/redo02.log'  SIZE 50M,
  GROUP 3 '/export/home/oracle/data/backup/imsy/redo03.log'  SIZE 50M
DATAFILE
  '/export/home/oracle/data/backup/imsy/system01.dbf',
  '/export/home/oracle/data/backup/imsy/undotbs01.dbf',
  '/export/home/oracle/data/backup/imsy/sysaux01.dbf',
  '/export/home/oracle/data/backup/imsy/SCOTT_DATA.dbf',
  '/export/home/oracle/data/backup/imsy/users01.dbf',
  '/export/home/oracle/data/backup/imsy/TS_SMS01.dbf'
   -- test01.dbf 삭제
CHARACTER SET AL32UTF8
;

~
"recon_mount2.sql" 21 lines, 770 characters 

SQL> 
SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> @recon_mount2.sql
ORACLE instance started.

Total System Global Area  369098752 bytes
Fixed Size                  1280276 bytes
Variable Size             113247980 bytes
Database Buffers          251658240 bytes
Redo Buffers                2912256 bytes

Control file created.

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------
/export/home/oracle/data/backup/imsy/system01.dbf
/export/home/oracle/data/backup/imsy/undotbs01.dbf
/export/home/oracle/data/backup/imsy/sysaux01.dbf
/export/home/oracle/data/backup/imsy/SCOTT_DATA.dbf
/export/home/oracle/data/backup/imsy/users01.dbf
/export/home/oracle/data/backup/imsy/TS_SMS01.dbf

6 rows selected.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL> @redo

GROUP# MEMBER                                                     MB ARC        SEQ STATUS
------ -------------------------------------------------- ---------- --- ---------- ------------
     1 /export/home/oracle/data/backup/imsy/redo01.log                    50 YES          0 UNUSED
     2 /export/home/oracle/data/backup/imsy/redo02.log                    50 YES          0 UNUSED
     3 /export/home/oracle/data/backup/imsy/redo03.log                    50 NO           1 CURRENT

- 이 실습의 핵심은 데이터 파일과 checkpoint SCN 정보가 다른 old control file을 이용해서 어떻게 현재 데이터 파일을 복구해 내느냐입니다.
즉 컨트롤 파일 생성을 할 수 있어야 해결할 수 있는 장애 상황이었습니다.



자료 출처 : 서진수 지음 - 원리부터 실무까지 오라클 백업과 복구