【练习】trace文本重建控制文件

这个小练习是针对控制文件全部丢失后怎么能快速的重建一个控制文件，快速的起库

1.备份控制文件到trace下

SQL> alter database backup controlfile to trace;

Database altered.

2.trace文本放在user_dump_dest的路径下

SQL> show parameter dump;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

background_core_dump                 string      partial

background_dump_dest                 string      /u01/app/oracle/diag/rdbms/ora

                                                 11gr2/ORA11GR2/trace

core_dump_dest                       string      /u01/app/oracle/diag/rdbms/ora

                                                 11gr2/ORA11GR2/cdump

max_dump_file_size                   string      unlimited

shadow_core_dump                     string      partial

user_dump_dest                       string      /u01/app/oracle/diag/rdbms/ora

                                                 11gr2/ORA11GR2/trace

3.打开alert查看生成的trace

[oracle@host02trace]$cd /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace

[oracle@host02 trace]$ cat alert_ORA11GR2.log

Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_3790.trc

Completed: alter database backup controlfile to trace

Fri Dec 02 10:19:33 2016

Starting background process SMCO

Fri Dec 02 10:19:33 2016

SMCO started with pid=21, OS id=3907

④按时间排序，最新生成文件排在最前

[oracle@host02 trace]$ ls -lt

total 500

-rw-r----- 1 oracle oinstall 50839 Dec  2 10:19 alert_ORA11GR2.log

-rw-r----- 1 oracle oinstall  1068 Dec  2 10:19 ORA11GR2_mmon_3778.trc

-rw-r----- 1 oracle oinstall    76 Dec  2 10:19 ORA11GR2_mmon_3778.trm

-rw-r----- 1 oracle oinstall 18936 Dec  2 10:16 ORA11GR2_ora_3790.trc

-rw-r----- 1 oracle oinstall   227 Dec  2 10:16 ORA11GR2_ora_3790.trm

4.复制create controlfile

[oracle@host02 trace]$ cat ORA11GR2_ora_3790.trc

CREATE CONTROLFILE REUSE DATABASE "ORA11GR2" RESETLOGS  NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/u01/app/oracle/oradata/ORA11GR2/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/u01/app/oracle/oradata/ORA11GR2/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/u01/app/oracle/oradata/ORA11GR2/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/u01/app/oracle/oradata/ORA11GR2/system01.dbf',

  '/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf',

  '/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf',

  '/u01/app/oracle/oradata/ORA11GR2/users01.dbf',

  '/u01/app/oracle/oradata/ORA11GR2/example01.dbf'

CHARACTER SET AL32UTF8

;

5.数据库启动到nomount状态

SQL> startup nomount

ORACLE instance started.

Total System Global Area  830930944 bytes

Fixed Size                  2257800 bytes

Variable Size             536874104 bytes

Database Buffers          289406976 bytes

Redo Buffers                2392064 bytes

6.根据trace文本重建控制文件

SQL> CREATE CONTROLFILE REUSE DATABASE "ORA11GR2" RESETLOGS  NOARCHIVELOG

  2      MAXLOGFILES 16

  3      MAXLOGMEMBERS 3

  4      MAXDATAFILES 100

  5      MAXINSTANCES 8

  6      MAXLOGHISTORY 292

  7  LOGFILE

  8    GROUP 1 '/u01/app/oracle/oradata/ORA11GR2/redo01.log'  SIZE 50M BLOCKSIZE 512,

  9    GROUP 2 '/u01/app/oracle/oradata/ORA11GR2/redo02.log'  SIZE 50M BLOCKSIZE 512,

 10    GROUP 3 '/u01/app/oracle/oradata/ORA11GR2/redo03.log'  SIZE 50M BLOCKSIZE 512

 11  -- STANDBY LOGFILE

 12  DATAFILE

 13    '/u01/app/oracle/oradata/ORA11GR2/system01.dbf',

 14    '/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf',

 15    '/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf',

 16    '/u01/app/oracle/oradata/ORA11GR2/users01.dbf',

 17    '/u01/app/oracle/oradata/ORA11GR2/example01.dbf'

 18  CHARACTER SET AL32UTF8

 19  ;

Control file created.

7.起到open：报错，需要RESETLOGS

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> select status from v$instance;

STATUS

------------

OPEN