Control file 재생성

1. user_dump_dest 경로 확인


2. 해당 경로로 이동



3. 컨트롤파일 트레이스





4. 해당 트레이스 파일 vi 편집실행
[oracle@localhost udump]$ ls -alrt
total 36
drwxr-x--- 8 oracle dba  4096 Feb 24 10:03 ..
-rw-r----- 1 oracle dba   623 Apr  4 21:34 orcl_ora_15069.trc
-rw-r----- 1 oracle dba  5921 Apr  4 21:35 orcl_ora_15096.trc
drwxr-x--- 2 oracle dba 12288 Apr  4 21:40 .
[oracle@localhost udump]$ vi orcl_ora_15096.trc

--     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 "ORCL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 111
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oracle/oradata/orcl/redo01.log'  SIZE 50M,
  GROUP 2 '/oracle/oradata/orcl/redo02.log'  SIZE 50M,
  GROUP 3 '/oracle/oradata/orcl/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/oracle/oradata/orcl/system01.dbf',
  '/oracle/oradata/orcl/undotbs01.dbf',
  '/oracle/oradata/orcl/sysaux01.dbf',
  '/oracle/oradata/orcl/users01.dbf',
  '/oracle/oradata/orcl/example01.dbf',
  '/oracle/oradata/orcl/example02.dbf'
CHARACTER SET KO16KSC5601
;
-- 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 '/oracle/arch/1_1_712473356.dbf';
-- 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;
-- No tempfile entries found to add.
--
--     Set #2. RESETLOGS 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.
-- 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 "ORCL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 111
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oracle/oradata/orcl/redo01.log'  SIZE 50M,
  GROUP 2 '/oracle/oradata/orcl/redo02.log'  SIZE 50M,
  GROUP 3 '/oracle/oradata/orcl/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/oracle/oradata/orcl/system01.dbf',
  '/oracle/oradata/orcl/undotbs01.dbf',
  '/oracle/oradata/orcl/sysaux01.dbf',
  '/oracle/oradata/orcl/users01.dbf',
  '/oracle/oradata/orcl/example01.dbf',
  '/oracle/oradata/orcl/example02.dbf'
CHARACTER SET KO16KSC5601
;
-- 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 '/oracle/arch/1_1_712473356.dbf';
-- 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;
-- No tempfile entries found to add.
--

5. resetlogs & noresetlogs 선택하여 sql 파일 만들기

[oracle@localhost udump]$ vi create_controlfile.sql
위에 스크립트를 복사하여 붙여넣기해서 스크립트 생성한다.
아래는 noresetlogs로 컨트롤파일 재생성하는 스크립트로 만들었다.



6. 데이터베이스 종료 후 컨트롤파일 재생성
   (실습시에는 꼭 기존에 컨트롤파일 백업해 두자)



7. 확인



만약 컨트롤 파일 내용중에 일부를 수정했으면 수정사항이 제대로 반영되었는지도 확인이 필요하다. 나는 컨트롤파일내용중 maxdatafile을 수정했다. maxdatafile은 데이터베이스의 데이터파일 최대갯수를 지정하는것인데 이것을 50에서 111로 늘려주었다.
아래는 제대로 maxdatafile갯수가 늘어났는지 확인한 예제이다.



DATAFILE 갯수가 111개로 되어있는것을 확인.