无备份丢失控制文件的恢复

DB VERSION：11.2.0.3

oracle@szsckj:/home/oracle>sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 8 15:48:59 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;

 

TABLESPACE_NAME

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

SYSTEM

UNDOTBS1

SYSAUX

TEMP

USERS

EXAMPLE

JSSZ

JSYC

 

8 rows selected.

 

SQL> arcihve log list;

SP2-0734: unknown command beginning "arcihve lo..." - rest of line ignored.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /RECO/arch/szscdb/

Oldest online log sequence     87

Next log sequence to archive   89

Current log sequence           89

SQL>

 

 

SQL> !rman target /

 

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jul 8 15:51:26 2013

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: SZSCDB (DBID=2429344538)

 

RMAN> list backup;

 

using target database control file instead of recovery catalog

specification does not match any backup in the repository

 

####模拟控制文件丢失

alert里面已经看到报错了：

Errors in file /u02/app/oracle/diag/rdbms/szscdb/szscdb/trace/szscdb_m001_3621.trc:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/ORADATA/szscdb/control01.ctl'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

 

SQL> shutdown abort;

 

###重建控制文件

###此处的创建控制文件脚本的信息是通过alter database backup controfile to trace得到的。

SQL> startup nomount;

SQL> CREATE CONTROLFILE REUSE DATABASE "SZSCDB" RESETLOGS  ARCHIVELOG

  2      MAXLOGFILES 16

  3      MAXLOGMEMBERS 3

  4      MAXDATAFILES 100

  5      MAXINSTANCES 8

  6      MAXLOGHISTORY 292

  7  LOGFILE

  8    GROUP 1 '/ORADATA/szscdb/redo01.log'  SIZE 50M BLOCKSIZE 512,

  9    GROUP 2 '/ORADATA/szscdb/redo02.log'  SIZE 50M BLOCKSIZE 512,

 10    GROUP 3 '/ORADATA/szscdb/redo03.log'  SIZE 50M BLOCKSIZE 512

 11  -- STANDBY LOGFILE

 12  DATAFILE

 13    '/ORADATA/szscdb/system01.dbf',

 14    '/ORADATA/szscdb/undotbs01.dbf',

 15    '/ORADATA/szscdb/sysaux01.dbf',

 16    '/ORADATA/szscdb/users01.dbf',

 17    '/ORADATA/szscdb/example01.dbf',

 18    '/ORADATA/szscdb/jssz01.dbf',

 19    '/ORADATA/szscdb/jsyc01.dbf'

 20  CHARACTER SET ZHS16GBK

 21  ;

Control file created.

 

 

 

 

alert 日志中出现警告：

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 ADD TEMPFILE

 

         Alternatively, if these temporary tablespaces are no longer

         needed, then they can be dropped.

           Empty temporary tablespace: TEMP

 

由于上面创建控制文件的脚本里面没有临时表空间的信息，需要手动为TEMP表空间添加数据文件

SQL> SELECT * FROM DBA_TEMP_FILES;

no rows selected

##查看之前的临时文件大小，作为创建表空间大小的依据

oracle@szsckj:/ORADATA/szscdb>du -sh temp01.dbf

16M     temp01.dbf

 

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/ORADATA/szscdb/temp02.dbf' size 16M;

 

Tablespace altered.

 

查看控制文件和数据文件头文件的SCN号

SQL> select file#,checkpoint_change# from v$datafile;

 

     FILE# CHECKPOINT_CHANGE#

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

         1            1551170

         2            1551170

         3            1551170

         4            1551170

         5            1551170

         6            1551170

         7            1551170

 

7 rows selected.

 

SQL> select file#,checkpoint_change# from v$datafile_header;

 

     FILE# CHECKPOINT_CHANGE#

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

         1            1551170

         2            1551170

         3            1551170

         4            1551170

         5            1551170

         6            1551170

         7            1551170

 

7 rows selected.

 

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

使用该方法创建控制文件时，oracle会到数据文件的头文件中获取scn号同步到控制文件中，此时的控制文件不能用来做恢复，需要立即对数据库做全备。

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26169542/viewspace-765801/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26169542/viewspace-765801/