控制文件恢复—从自动或二进制备份中恢复

说明

         数据库在NOCATALOG模式下，RMAN备份信息会保存在控制文件中，如果控制文件丢失，备份信息也会找不到，如果有控制文件备份我们可以执行恢复，如果没有控制文件备份我们也可以利用创建控制文件脚本来恢复数据库。

 

从自动或二进制备份中恢复

1        创建新的表空间、数据文件、用户

SQL>create tablespace ttt datafile ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’ size 10m; SQL>grant connect,resource to ttt identified by ttt; SQL>alter user ttt default tablespace ttt; SQL>alter user ttt account unlock;

 

2        构造数据

SQL>conn ttt/ttt; SQL>create table ttt (id int); SQL> insert into ttt values(1); 1 row created. SQL> insert into ttt values(2); 1 row created. SQL> insert into ttt values(3); 1 row created. SQL> commit; Commit complete. SQL>select * from ttt; ID ---- 1 2 3

         ttt用户默认表空间为ttt,创建表ttt，并插入3数据。

3        切换日志    

SQL>conn / as sysdba; SQL>alter system switch logfile;

         切换日志，保证数据都写到DBF中。

4        开启控制文件自动备份

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;   new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored   RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/u01/app/oracle/backup/%F';   new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/u01/app/oracle/backup/%F'; new RMAN configuration parameters are successfully stored   RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT  '/oracle/u01/app/oracle/backup/%U';   new RMAN configuration parameters: CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/oracle/u01/app/oracle/backup/%U'; new RMAN configuration parameters are successfully stored released channel: ORA_DISK_1

 

5        备份数据库

RMAN>backup database;   Starting backup at 07-MAR-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=540 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00001 name=/oracle/u01/app/oracle/oradata/orcl/system01.dbf input datafile fno=00003 name=/oracle/u01/app/oracle/oradata/orcl/sysaux01.dbf input datafile fno=00005 name=/oracle/u01/app/oracle/oradata/orcl/admin01.dbf input datafile fno=00002 name=/oracle/u01/app/oracle/oradata/orcl/undotbs01.dbf input datafile fno=00004 name=/oracle/u01/app/oracle/oradata/orcl/users01.dbf input datafile fno=00006 name=/oracle/u01/app/oracle/oradata/orcl/ttt.dbf channel ORA_DISK_1: starting piece 1 at 07-MAR-13 channel ORA_DISK_1: finished piece 1 at 07-MAR-13 piece handle=/oracle/u01/app/oracle/backup/0lo3uo4l_1_1 tag=TAG20130307T175741 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45 Finished backup at 07-MAR-13   Starting Control File and SPFILE Autobackup at 07-MAR-13 piece handle=/oracle/u01/app/oracle/backup/c-1324770912-20130307-08 comment=NONE Finished Control File and SPFILE Autobackup at 07-MAR-13

         数据库备份集于/oracle/u01/app/oracle/backup/下

         控制文件自动备份于/oracle/u01/app/oracle/backup/c-1324770912-20130307-08

6        再插入一些数据  

SQL>conn ttt/ttt; SQL> insert into ttt values(4); 1 row created. SQL> insert into ttt values(5); 1 row created. SQL> insert into ttt values(6); 1 row created. SQL> commit; Commit complete. SQL>select * from ttt;

 

7        模拟丢失控制文件

SQL>conn / as sysdba; SQL>shutdown immediate; [oracle@dev206 ~]$ rm -rf /oracle/u01/app/oracle/oradata/orcl/control0*.ctl SQL> startup ORA-32004: obsolete and/or deprecated parameter(s) specified ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE instance started.   Total System Global Area 1224736768 bytes Fixed Size                  2020384 bytes Variable Size             352324576 bytes Database Buffers          855638016 bytes Redo Buffers               14753792 bytes ORA-00205: error in identifying control file, check alert log for more info

         此时在启动数据库时报错找不到控制文件而只能启动到nomount状态。

        

8        恢复控制文件

                  如果开启了控制文件自动备份可以利用autobackup来恢复控制文件或者

      利用备份集来恢复控制文件。    

RMAN>set dbid=1324770912 RMAN> restore controlfile from autobackup;   Starting restore at 07-MAR-13 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=540 devtype=DISK   recovery area destination: /oracle/u01/app/oracle/flash_recovery_area database name (or database unique name) used for search: ORCL channel ORA_DISK_1: no autobackups found in the recovery area channel ORA_DISK_1: looking for autobackup on day: 20130307 channel ORA_DISK_1: looking for autobackup on day: 20130306 channel ORA_DISK_1: looking for autobackup on day: 20130305 channel ORA_DISK_1: looking for autobackup on day: 20130304 channel ORA_DISK_1: looking for autobackup on day: 20130303 channel ORA_DISK_1: looking for autobackup on day: 20130302 channel ORA_DISK_1: looking for autobackup on day: 20130301 channel ORA_DISK_1: no autobackup in 7 days found RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 03/07/2013 18:00:34 RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

                  此时找不到控制文件，因为我们更改了自动备份控制文件路径，可以手动

      拷贝备份到./product/dbs下或者指定文件。

RMAN> restore controlfile from  '/oracle/u01/app/oracle/backup/c-1324770912-20130307-08';   Starting restore at 07-MAR-13 using channel ORA_DISK_1   channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output filename=/oracle/u01/app/oracle/oradata/orcl/control01.ctl output filename=/oracle/u01/app/oracle/oradata/orcl/control02.ctl output filename=/oracle/u01/app/oracle/oradata/orcl/control03.ctl Finished restore at 07-MAR-13   或者指定备份集来恢复控制文件 RMAN> restore controlfile from  '/oracle/u01/app/oracle/backup/0lo3uo4l_1_1d';

         控制文件提取完成，启动数据库到mount状态。

RMAN>alter database mount;

 

         利用RMAN恢复数据库

RMAN>recover database;

         启动数据库

RMAN>alter database open resetlogs;

        

9         检查数据

SQL> select count(*) from ttt.ttt;         ID ----------          1          2          3          4          5          6

         数据库恢复完成.

 

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

转载于:http://blog.itpub.net/26252014/viewspace-755621/