数据库正常关闭下controfile丢失，rman的恢复

  今天问了几个人数据库正常关闭下controfile丢失，如何使用rman进行恢复，回答各种不靠谱，做个试验，看看到底如何恢复。

  环境：linux 5.2    oracle： 11.2.0.1

1.对数据库全备，并备份控制文件。

       

 oracle@gg ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Mar 31 17:55:11 2013

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

connected to target database: GG (DBID=1421453215)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name GG are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_gg.f'; # default

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> backup database;

Starting backup at 31-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oradata/GG/datafile/o1_mf_system_8kf05knp_.dbf
input datafile file number=00002 name=/u01/app/oradata/GG/datafile/o1_mf_sysaux_8kf05ksr_.dbf
input datafile file number=00003 name=/u01/app/oradata/GG/datafile/o1_mf_undotbs1_8kf05ksw_.dbf
input datafile file number=00004 name=/u01/app/oradata/GG/datafile/o1_mf_users_8kf05kto_.dbf
channel ORA_DISK_1: starting piece 1 at 31-MAR-13
channel ORA_DISK_1: finished piece 1 at 31-MAR-13
piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/0bo5u11g_1_1 tag=TAG20130331T175600 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:19
Finished backup at 31-MAR-13

Starting Control File and SPFILE Autobackup at 31-MAR-13
piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/c-1421453215-20130331-00 comment=NONE
Finished Control File and SPFILE Autobackup at 31-MAR-13

RMAN> exit

2.查看控制文件位置，并关闭数据库

[oracle@gg controlfile]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 31 18:14:04 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter contro

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u01/app/oradata/GG/controlfil
                                                 e/o1_mf_8kf0bz09_.ctl
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

3.删除控制文件

[oracle@gg onlinelog]$cd /u01/app/oradata/GG/controlfile

[oracle@gg controlfile]$ mv o1_mf_8kf0bz09_.ctl o1_mf_8kf0bz09_.ctl.bak

4.启动数据库到nomount状态

[oracle@gg controlfile]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 31 18:14:04 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  418484224 bytes
Fixed Size                  1336932 bytes
Variable Size             285215132 bytes
Database Buffers          125829120 bytes
Redo Buffers                6103040 bytes
SQL> exit

5.rman restore控制文件

[oracle@gg ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Mar 31 18:06:03 2013

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

connected to target database: GG (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/product/11.2.0/db_1/dbs/c-1421453215-20130331-00';

Starting restore at 31-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oradata/GG/controlfile/o1_mf_8kf0bz09_.ctl
Finished restore at 31-MAR-13

RMAN>exit

6.将数据库至于mount状态下。

[oracle@gg controlfile]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 31 18:14:04 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database mount;

Database altered.

7.recover 控制文件

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 835654 generated at 03/29/2013 11:21:44 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_9_806979809.dbf
ORA-00280: change 835654 for thread 1 is in sequence #9


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oradata/GG/onlinelog/o1_mf_2_8kf0c7d9_.log
Log applied.
Media recovery complete.
SQL>
SQL> alter database open resetlogs;

Database altered.

这地方需要手动指定位置，因为控制文件不知道redo是否归档，文件位置，只能手动指定。