无备份丢失控制文件的恢复-CSDN博客

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

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

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/