【RMAN】利用备份片还原数据库
BLOG文档结构图
群里有同学说把ORACLE_BASE目录删掉了，现在只有备份片和归档文件了，试问如何恢复。坑，，，，咋能这么干，ORACLE_BASE都敢删，，试着恢复了下，并且记录下来，当然软件安装部分就不记录了。
我再说说目前的情况，只有备份片和归档文件，且从文件名称是看不出dbname和dbid的，在这种情况下其实恢复控制文件是很重要的了，但是鬼知道备份片里边是不是有控制文件的备份呢？我们可能碰到的就是这样的情况，只有备份文件，其它的什么都不知道。我们且看这样的情况下如何恢复呢？
演示环境：
OS: RHEL 6.5
DB: 11.2.0.3.0
1.1 原库备份并传输到测试库
原库情况：
[oracle@rhel6_lhr ora11g]$ ORACLE_SID=ora11g
[oracle@rhel6_lhr ora11g]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 30 15:36:11 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
15:36:19 SQL> startup
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 281021824 bytes
Database Buffers 117440512 bytes
Redo Buffers 8503296 bytes
Database mounted.
Database opened.
15:37:20 SQL>
15:37:50 SQL>
15:37:50 SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string ora11g
db_unique_name string ora11g
global_names boolean FALSE
instance_name string ora11g
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string ora11g
15:47:37 SQL> col FILE_NAME format a50
15:47:47 SQL> select FILE_NAME,FILE_ID,a.TABLESPACE_NAME, b.status ts_status ,BYTES from dba_data_files a,dba_tablespaces b where a.TABLESPACE_NAME=b.TABLESPACE_NAME;
FILE_NAME FILE_ID TABLESPACE_NAME TS_STATUS BYTES
-------------------------------------------------- ---------- ------------------------------ --------- ----------
/u01/app/oracle/oradata/ora11g/users01.dbf 4 USERS ONLINE 5242880
/u01/app/oracle/oradata/ora11g/undotbs01.dbf 3 UNDOTBS1 ONLINE 99614720
/u01/app/oracle/oradata/ora11g/sysaux01.dbf 2 SYSAUX ONLINE 545259520
/u01/app/oracle/oradata/ora11g/system01.dbf 1 SYSTEM ONLINE 744488960
/u01/app/oracle/oradata/ora11g/example01.dbf 5 EXAMPLE READ ONLY 328335360
15:47:48 SQL> create table lhr.test as select * from dba_objects;
Table created.
15:47:49 SQL> select count(1) from lhr.test;
COUNT(1)
----------
75204
15:47:50 SQL>
执行备份，当然生产库上不能这么简单的备份：
[oracle@rhel6_lhr ~]$ ORACLE_SID=ora11g
[oracle@rhel6_lhr ~]$ ps -ef|grep ora_
oracle 3282 2997 0 15:36 pts/2 00:00:00 grep ora_
[oracle@rhel6_lhr ~]$ ps -ef|grep ora_
oracle 3286 2997 0 15:36 pts/2 00:00:00 grep ora_
[oracle@rhel6_lhr ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 30 15:36:52 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=4270446895)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORA11G 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/dbhome_1/dbs/snapcf_ora11g.f'; # default
RMAN> list backupset;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
RMAN> backup database plus archivelog delete input;
Starting backup at 2015-04-30 16:00:45
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=1 STAMP=878399828
input archived log thread=1 sequence=7 RECID=2 STAMP=878399829
input archived log thread=1 sequence=8 RECID=3 STAMP=878400047
channel ORA_DISK_1: starting piece 1 at 2015-04-30 16:00:48
channel ORA_DISK_1: finished piece 1 at 2015-04-30 16:00:49
piece handle=/u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_annnn_TAG20150430T160048_bn3r9jd7_.bkp tag=TAG20150430T160048 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_04_30/o1_mf_1_6_bn3r2mo1_.arc RECID=1 STAMP=878399828
archived log file name=/u01/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_04_30/o1_mf_1_7_bn3r2nz5_.arc RECID=2 STAMP=878399829
archived log file name=/u01/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_04_30/o1_mf_1_8_bn3r9hrl_.arc RECID=3 STAMP=878400047
Finished backup at 2015-04-30 16:00:49
Starting backup at 2015-04-30 16:00:49
using channel ORA_DISK_1
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/oracle/oradata/ora11g/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ora11g/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ora11g/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ora11g/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-04-30 16:00:49
channel ORA_DISK_1: finished piece 1 at 2015-04-30 16:02:34
piece handle=/u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_nnndf_TAG20150430T160049_bn3r9ksp_.bkp tag=TAG20150430T160049 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2015-04-30 16:02:37
channel ORA_DISK_1: finished piece 1 at 2015-04-30 16:02:39
piece handle=/u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_ncsnf_TAG20150430T160049_bn3rdxyz_.bkp tag=TAG20150430T160049 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 2015-04-30 16:02:39
Starting backup at 2015-04-30 16:02:39
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=4 STAMP=878400159
channel ORA_DISK_1: starting piece 1 at 2015-04-30 16:02:39
channel ORA_DISK_1: finished piece 1 at 2015-04-30 16:02:40
piece handle=/u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_annnn_TAG20150430T160239_bn3rdzd7_.bkp tag=TAG20150430T160239 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_04_30/o1_mf_1_9_bn3rdz8k_.arc RECID=4 STAMP=878400159
Finished backup at 2015-04-30 16:02:40
RMAN>
RMAN>
RMAN> list backupset;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
1 11.61M DISK 00:00:00 2015-04-30 16:00:48
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20150430T160048
Piece Name: /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_annnn_TAG20150430T160048_bn3r9jd7_.bkp
List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 6 1087169 2015-03-13 14:24:40 1094093 2015-04-30 15:57:07
1 7 1094093 2015-04-30 15:57:07 1094096 2015-04-30 15:57:08
1 8 1094096 2015-04-30 15:57:08 1094204 2015-04-30 16:00:47
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
2 Full 1.13G DISK 00:01:43 2015-04-30 16:02:32
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20150430T160049
Piece Name: /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_nnndf_TAG20150430T160049_bn3r9ksp_.bkp
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1094214 2015-04-30 16:00:49 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1094214 2015-04-30 16:00:49 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1094214 2015-04-30 16:00:49 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 Full 1094214 2015-04-30 16:00:49 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1092435 2015-04-30 15:42:04 /u01/app/oracle/oradata/ora11g/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
3 Full 9.36M DISK 00:00:04 2015-04-30 16:02:38
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20150430T160049
Piece Name: /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_ncsnf_TAG20150430T160049_bn3rdxyz_.bkp
SPFILE Included: Modification time: 2015-04-30 15:56:17
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1094280 Ckp time: 2015-04-30 16:02:34
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
4 19.00K DISK 00:00:00 2015-04-30 16:02:39
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20150430T160239
Piece Name: /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_annnn_TAG20150430T160239_bn3rdzd7_.bkp
List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 9 1094204 2015-04-30 16:00:47 1094286 2015-04-30 16:02:39
RMAN>
[root@rhel6_lhr ~]# cd /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/
[root@rhel6_lhr 2015_04_30]# ll -h
total 1.2G
-rw-r----- 1 oracle asmadmin 12M Apr 30 16:00 o1_mf_annnn_TAG20150430T160048_bn3r9jd7_.bkp
-rw-r----- 1 oracle asmadmin 20K Apr 30 16:02 o1_mf_annnn_TAG20150430T160239_bn3rdzd7_.bkp
-rw-r----- 1 oracle asmadmin 9.4M Apr 30 16:02 o1_mf_ncsnf_TAG20150430T160049_bn3rdxyz_.bkp
-rw-r----- 1 oracle asmadmin 1.2G Apr 30 16:02 o1_mf_nnndf_TAG20150430T160049_bn3r9ksp_.bkp
[root@rhel6_lhr 2015_04_30]# cd ..
[root@rhel6_lhr backupset]# ll
total 4
drwxr-x--- 2 oracle asmadmin 4096 Apr 30 16:02 2015_04_30
[root@rhel6_lhr backupset]# scp 2015_04_30 oracle@192.168.59.157:/tmp
The authenticity of host '192.168.59.157 (192.168.59.157)' can't be established.
RSA key fingerprint is 77:e6:11:1a:7c:c7:81:7c:88:c9:21:18:51:2a:84:d1.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.59.157' (RSA) to the list of known hosts.
oracle@192.168.59.157's password:
2015_04_30: not a regular file
[root@rhel6_lhr backupset]# scp -r 2015_04_30/ oracle@192.168.59.157:/tmp
oracle@192.168.59.157's password:
o1_mf_annnn_TAG20150430T160048_bn3r9jd7_.bkp 100% 12MB 11.6MB/s 00:00
o1_mf_nnndf_TAG20150430T160049_bn3r9ksp_.bkp 100% 1157MB 77.1MB/s 00:15
o1_mf_ncsnf_TAG20150430T160049_bn3rdxyz_.bkp 100% 9600KB 9.4MB/s 00:00
o1_mf_annnn_TAG20150430T160239_bn3rdzd7_.bkp 100% 20KB 19.5KB/s 00:00
[root@rhel6_lhr backupset]#
1.2 测试库编辑pfile文件
11g只写db_name即可，控制文件可以写上，免得后边又回来修改。
[oracle@testdb dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@testdb dbs]$ more initora11g.ora
db_name=ora11g
control_files='/u01/app/oracle/oradata/ora11g/control01.ctl','/u01/app/oracle/flash_recovery_area/ora11g/control02.ctl'
[oracle@testdb dbs]$
1.3 找回控制文件--备份集中含有控制文件
重点来了：
由于没有控制文件，所以首先只能启动数据库到nomount状态
[oracle@testdb dbs]$ ORACLE_SID=ora11g
[oracle@testdb dbs]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 30 16:16:53 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 250560512 bytes
Fixed Size 2227256 bytes
Variable Size 192938952 bytes
Database Buffers 50331648 bytes
Redo Buffers 5062656 bytes
RMAN>
首先我们要判断一下备份集中是否含有控制文件的备份，如果有我们可以直接还原，如果没有那就只有创建控制文件了，那么如何判断备份集中是否含有控制文件的备份呢？这里提供几种办法：① 推荐： 采用dbms_backup_restore.restoreControlfileTo从备份片中来尝试找回控制文件 ② 尝试采用创建临时库来找回控制文件 ③ 其它第三方工具来解析备份集
1.3.1 方法一：采用dbms_backup_restore.restoreControlfileTo从备份片中来尝试找回控制文件
在Oracle 816 以后的版本中,Oracle提供了一个包:DBMS_BACKUP_RESTORE. DBMS_BACKUP_RESTORE 包是由dbmsbkrs.sql 和 prvtbkrs.plb 这两个脚本创建的.catproc.sql 脚本运行后会调用这两个包.所以是每个数据库都有的这个包是Oracle服务器和操作系统之间IO操作的接口.由恢复管理器直接调用。由此可见，我们可以在数据库nomount 情况下调用这些package ,来达到我们的恢复目的。
从前边的备份中我们知道control文件和spfile其实是在备份片：o1_mf_ncsnf_TAG20150430T160049_bn3rdxyz_.bkp中，但是这里我们假装不知道位置，然后挨个尝试一下：
[oracle@testdb dbs]$ ORACLE_SID=ora11g
[oracle@testdb 2015_04_30]$ cd /tmp/2015_04_30/
[oracle@testdb 2015_04_30]$ ll
total 1206296
-rw-r-----. 1 oracle oinstall 12173312 Apr 30 16:06 o1_mf_annnn_TAG20150430T160048_bn3r9jd7_.bkp
-rw-r-----. 1 oracle oinstall 19968 Apr 30 16:06 o1_mf_annnn_TAG20150430T160239_bn3rdzd7_.bkp
-rw-r-----. 1 oracle oinstall 9830400 Apr 30 16:06 o1_mf_ncsnf_TAG20150430T160049_bn3rdxyz_.bkp
-rw-r-----. 1 oracle oinstall 1213218816 Apr 30 16:06 o1_mf_nnndf_TAG20150430T160049_bn3r9ksp_.bkp
[oracle@testdb 2015_04_30]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 30 16:56:34 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/tmp/2015_04_30/control01.ctl');
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_04_30/o1_mf_annnn_TAG20150430T160048_bn3r9jd7_.bkp', params=>null);
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /
DECLARE
*
ERROR at line 1:
ORA-19624: operation failed, retry possible
ORA-19870: error while restoring backup piece
/tmp/2015_04_30/o1_mf_annnn_TAG20150430T160048_bn3r9jd7_.bkp
ORA-19626: backup set type is archived log - can not be processed by this
conversation
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5827
ORA-06512: at line 8
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/tmp/2015_04_30/control01.ctl');
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_04_30/o1_mf_annnn_TAG20150430T160239_bn3rdzd7_.bkp', params=>null);
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /
DECLARE
*
ERROR at line 1:
ORA-19568: a device is already allocated to this session
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 235
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 210
ORA-06512: at line 5
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@testdb 2015_04_30]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 30 16:57:08 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/tmp/2015_04_30/control01.ctl');
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_04_30/o1_mf_annnn_TAG20150430T160239_bn3rdzd7_.bkp', params=>null);
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /
DECLARE
*
ERROR at line 1:
ORA-19624: operation failed, retry possible
ORA-19870: error while restoring backup piece
/tmp/2015_04_30/o1_mf_annnn_TAG20150430T160239_bn3rdzd7_.bkp
ORA-19626: backup set type is archived log - can not be processed by this
conversation
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5827
ORA-06512: at line 8
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@testdb 2015_04_30]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 30 16:57:33 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/tmp/2015_04_30/control01.ctl');
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_04_30/o1_mf_nnndf_TAG20150430T160049_bn3r9ksp_.bkp', params=>null);
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /
DECLARE
*
ERROR at line 1:
ORA-19697: standby control file not found in backup set
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5827
ORA-06512: at line 8
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@testdb 2015_04_30]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 30 16:57:46 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/tmp/2015_04_30/control01.ctl');
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_04_30/o1_mf_ncsnf_TAG20150430T160049_bn3rdxyz_.bkp', params=>null);
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
当合适的情况下，我们发现恢复了控制文件了，接下来就是启动数据库到mount状态了。
小插曲： 在同一个会话里执行会报错： ORA-19590: conversation already active，所以我整理了一下脚本，如下，可以直接运行：
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
sys.dbms_backup_restore.restoreSetDatafile;
sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/tmp/2015_04_30/control01.ctl');
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_04_30/o1_mf_annnn_TAG20150430T160048_bn3r9jd7_.bkp', params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;
/
conn / as sysdba
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
sys.dbms_backup_restore.restoreSetDatafile;
sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/tmp/2015_04_30/control01.ctl');
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_04_30/o1_mf_annnn_TAG20150430T160239_bn3rdzd7_.bkp', params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;
/
conn / as sysdba
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
sys.dbms_backup_restore.restoreSetDatafile;
sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/tmp/2015_04_30/control01.ctl');
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_04_30/o1_mf_ncsnf_TAG20150430T160049_bn3rdxyz_.bkp', params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;
/
conn / as sysdba
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
sys.dbms_backup_restore.restoreSetDatafile;
sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/tmp/2015_04_30/control01.ctl');
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_04_30/o1_mf_nnndf_TAG20150430T160049_bn3r9ksp_.bkp', params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;
/
执行过程：
[oracle@testdb ora11g]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 30 19:13:05 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/tmp/2015_04_30/control01.ctl');
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_04_30/o1_mf_annnn_TAG20150430T160048_bn3r9jd7_.bkp', params=>null);
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /
DECLARE
*
ERROR at line 1:
ORA-19624: operation failed, retry possible
ORA-19870: error while restoring backup piece
/tmp/2015_04_30/o1_mf_annnn_TAG20150430T160048_bn3r9jd7_.bkp
ORA-19626: backup set type is archived log - can not be processed by this
conversation
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5827
ORA-06512: at line 8
SQL> conn / as sysdba
Connected.
SQL> SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/tmp/2015_04_30/control01.ctl');
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_04_30/o1_mf_annnn_TAG20150430T160239_bn3rdzd7_.bkp', params=>null);
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /
DECLARE
*
ERROR at line 1:
ORA-19624: operation failed, retry possible
ORA-19870: error while restoring backup piece
/tmp/2015_04_30/o1_mf_annnn_TAG20150430T160239_bn3rdzd7_.bkp
ORA-19626: backup set type is archived log - can not be processed by this
conversation
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5827
ORA-06512: at line 8
SQL> conn / as sysdba
Connected.
SQL> SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/tmp/2015_04_30/control01.ctl');
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_04_30/o1_mf_ncsnf_TAG20150430T160049_bn3rdxyz_.bkp', params=>null);
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /
conn / as sysdba
PL/SQL procedure successfully completed.
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
Connected.
SQL> SQL> 2 3 4 5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/tmp/2015_04_30/control01.ctl');
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_04_30/o1_mf_nnndf_TAG20150430T160049_bn3r9ksp_.bkp', params=>null);
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /
DECLARE
*
ERROR at line 1:
ORA-19697: standby control file not found in backup set
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5827
ORA-06512: at line 8
SQL>
控制文件恢复了，接下来的操作就容易的多了。
一、 启动数据库到mount 状态
[oracle@testdb dbs]$ more initora11g.ora
db_name=ora11g
control_files='/u01/app/oracle/oradata/ora11g/control01.ctl','/u01/app/oracle/flash_recovery_area/ora11g/control02.ctl'
[oracle@testdb 2015_04_30]$ ll
total 1215816
-rw-r-----. 1 oracle oinstall 9748480 Apr 30 16:57 control01.ctl
-rw-r-----. 1 oracle oinstall 12173312 Apr 30 16:06 o1_mf_annnn_TAG20150430T160048_bn3r9jd7_.bkp
-rw-r-----. 1 oracle oinstall 19968 Apr 30 16:06 o1_mf_annnn_TAG20150430T160239_bn3rdzd7_.bkp
-rw-r-----. 1 oracle oinstall 9830400 Apr 30 16:06 o1_mf_ncsnf_TAG20150430T160049_bn3rdxyz_.bkp
-rw-r-----. 1 oracle oinstall 1213218816 Apr 30 16:06 o1_mf_nnndf_TAG20150430T160049_bn3r9ksp_.bkp
[oracle@testdb 2015_04_30]$ cp control01.ctl /u01/app/oracle/oradata/ora11g/control01.ctl
cp: cannot create regular file `/u01/app/oracle/oradata/ora11g/control01.ctl': No such file or directory
[oracle@testdb 2015_04_30]$ mkdir -p /u01/app/oracle/oradata/ora11g
[oracle@testdb 2015_04_30]$ cp control01.ctl /u01/app/oracle/oradata/ora11g/control01.ctl
[oracle@testdb 2015_04_30]$ cp control01.ctl /u01/app/oracle/flash_recovery_area/ora11g/control02.ctl
[oracle@testdb 2015_04_30]$
[oracle@testdb 2015_04_30]$
[oracle@testdb 2015_04_30]$
[oracle@testdb 2015_04_30]$ echo $ORACLE_SID
ora11g
[oracle@testdb 2015_04_30]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 30 17:03:14 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 250560512 bytes
Fixed Size 2227256 bytes
Variable Size 192938952 bytes
Database Buffers 50331648 bytes
Redo Buffers 5062656 bytes
Database mounted.
SQL>
SQL> col FILE_NAME format a50
SQL> select file#,name FILE_NAME,status from v$datafile;
FILE# FILE_NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE
5 /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE
二、 重新注册备份集，还原spfile ，还原数据库，不完全恢复数据库
将控制文件中的原库备份信息删除，然后重新注册备份集。
[oracle@testdb 2015_04_30]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 30 17:06:33 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=4270446895, not open)
RMAN> list backupset;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1 11.61M DISK 00:00:00 30-APR-15
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20150430T160048
Piece Name: /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_annnn_TAG20150430T160048_bn3r9jd7_.bkp
List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 1087169 13-MAR-15 1094093 30-APR-15
1 7 1094093 30-APR-15 1094096 30-APR-15
1 8 1094096 30-APR-15 1094204 30-APR-15
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 1.13G DISK 00:01:43 30-APR-15
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20150430T160049
Piece Name: /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_nnndf_TAG20150430T160049_bn3r9ksp_.bkp
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1094214 30-APR-15 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1094214 30-APR-15 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1094214 30-APR-15 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 Full 1094214 30-APR-15 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1092435 30-APR-15 /u01/app/oracle/oradata/ora11g/example01.dbf
RMAN> delete backupset;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=170 device type=DISK
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1 1 1 1 AVAILABLE DISK /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_annnn_TAG20150430T160048_bn3r9jd7_.bkp
2 2 1 1 AVAILABLE DISK /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_nnndf_TAG20150430T160049_bn3r9ksp_.bkp
Do you really want to delete the above objects (enter YES or NO)? yes
RMAN-06207: WARNING: 2 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_annnn_TAG20150430T160048_bn3r9jd7_.bkp
RMAN-06214: Backup Piece /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_nnndf_TAG20150430T160049_bn3r9ksp_.bkp
RMAN> crosscheck backupset;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_annnn_TAG20150430T160048_bn3r9jd7_.bkp RECID=1 STAMP=878400048
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_nnndf_TAG20150430T160049_bn3r9ksp_.bkp RECID=2 STAMP=878400049
Crosschecked 2 objects
RMAN> list backupset;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1 11.61M DISK 00:00:00 30-APR-15
BP Key: 1 Status: EXPIRED Compressed: NO Tag: TAG20150430T160048
Piece Name: /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_annnn_TAG20150430T160048_bn3r9jd7_.bkp
List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 1087169 13-MAR-15 1094093 30-APR-15
1 7 1094093 30-APR-15 1094096 30-APR-15
1 8 1094096 30-APR-15 1094204 30-APR-15
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 1.13G DISK 00:01:43 30-APR-15
BP Key: 2 Status: EXPIRED Compressed: NO Tag: TAG20150430T160049
Piece Name: /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_nnndf_TAG20150430T160049_bn3r9ksp_.bkp
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1094214 30-APR-15 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1094214 30-APR-15 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1094214 30-APR-15 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 Full 1094214 30-APR-15 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1092435 30-APR-15 /u01/app/oracle/oradata/ora11g/example01.dbf
RMAN> delete backupset;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1 1 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_annnn_TAG20150430T160048_bn3r9jd7_.bkp
2 2 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_nnndf_TAG20150430T160049_bn3r9ksp_.bkp
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_annnn_TAG20150430T160048_bn3r9jd7_.bkp RECID=1 STAMP=878400048
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_04_30/o1_mf_nnndf_TAG20150430T160049_bn3r9ksp_.bkp RECID=2 STAMP=878400049
Deleted 2 objects
RMAN> list backupset;
specification does not match any backup in the repository
RMAN> catalog start with '/tmp/2015_04_30/';
searching for all files that match the pattern /tmp/2015_04_30/
List of Files Unknown to the Database
=====================================
File Name: /tmp/2015_04_30/o1_mf_annnn_TAG20150430T160048_bn3r9jd7_.bkp
File Name: /tmp/2015_04_30/o1_mf_nnndf_TAG20150430T160049_bn3r9ksp_.bkp
File Name: /tmp/2015_04_30/o1_mf_ncsnf_TAG20150430T160049_bn3rdxyz_.bkp
File Name: /tmp/2015_04_30/o1_mf_annnn_TAG20150430T160239_bn3rdzd7_.bkp
File Name: /tmp/2015_04_30/control01.ctl
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/2015_04_30/o1_mf_annnn_TAG20150430T160048_bn3r9jd7_.bkp
File Name: /tmp/2015_04_30/o1_mf_nnndf_TAG20150430T160049_bn3r9ksp_.bkp
File Name: /tmp/2015_04_30/o1_mf_ncsnf_TAG20150430T160049_bn3rdxyz_.bkp
File Name: /tmp/2015_04_30/o1_mf_annnn_TAG20150430T160239_bn3rdzd7_.bkp
File Name: /tmp/2015_04_30/control01.ctl
RMAN> list backupset;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3 11.61M DISK 00:00:00 30-APR-15
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20150430T160048
Piece Name: /tmp/2015_04_30/o1_mf_annnn_TAG20150430T160048_bn3r9jd7_.bkp
List of Archived Logs in backup set 3
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 1087169 13-MAR-15 1094093 30-APR-15
1 7 1094093 30-APR-15 1094096 30-APR-15
1 8 1094096 30-APR-15 1094204 30-APR-15
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 1.13G DISK 00:00:00 30-APR-15
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20150430T160049
Piece Name: /tmp/2015_04_30/o1_mf_nnndf_TAG20150430T160049_bn3r9ksp_.bkp
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1094214 30-APR-15 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1094214 30-APR-15 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1094214 30-APR-15 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 Full 1094214 30-APR-15 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1092435 30-APR-15 /u01/app/oracle/oradata/ora11g/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 9.36M DISK 00:00:00 30-APR-15
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20150430T160049
Piece Name: /tmp/2015_04_30/o1_mf_ncsnf_TAG20150430T160049_bn3rdxyz_.bkp
SPFILE Included: Modification time: 30-APR-15
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1094280 Ckp time: 30-APR-15
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
6 19.00K DISK 00:00:00 30-APR-15
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20150430T160239
Piece Name: /tmp/2015_04_30/o1_mf_annnn_TAG20150430T160239_bn3rdzd7_.bkp
List of Archived Logs in backup set 6
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 9 1094204 30-APR-15 1094286 30-APR-15
RMAN> restore spfile from '/tmp/2015_04_30/o1_mf_ncsnf_TAG20150430T160049_bn3rdxyz_.bkp';
Starting restore at 30-APR-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=179 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /tmp/2015_04_30/o1_mf_ncsnf_TAG20150430T160049_bn3rdxyz_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 30-APR-15
RMAN>
RMAN> restore database;
Starting restore at 30-APR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ora11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ora11g/example01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/2015_04_30/o1_mf_nnndf_TAG20150430T160049_bn3r9ksp_.bkp
channel ORA_DISK_1: piece handle=/tmp/2015_04_30/o1_mf_nnndf_TAG20150430T160049_bn3r9ksp_.bkp tag=TAG20150430T160049
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 30-APR-15
先尝试做完全恢复，等到报错再做不完全恢复：
RMAN> recover database;
Starting recover at 30-APR-15
using channel ORA_DISK_1
datafile 5 not processed because file is read-only
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=9
channel ORA_DISK_1: reading from backup piece /tmp/2015_04_30/o1_mf_annnn_TAG20150430T160239_bn3rdzd7_.bkp
channel ORA_DISK_1: piece handle=/tmp/2015_04_30/o1_mf_annnn_TAG20150430T160239_bn3rdzd7_.bkp tag=TAG20150430T160239
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_9_874246769.dbf thread=1 sequence=9
unable to find archived log
archived log thread=1 sequence=10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/30/2015 17:11:33
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 10 and starting SCN of 1094286
RMAN> recover database until sequence 10;
Starting recover at 30-APR-15
using channel ORA_DISK_1
datafile 5 not processed because file is read-only
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 30-APR-15
RMAN> alter database open resetlogs;
database opened
RMAN>
RMAN> shutdown abort;
Oracle instance shut down
RMAN> exit
Recovery Manager complete.
这里恢复到10号停止了，10号文件很有可能丢失或者是在线日志，如果是在线日志，且在线日志可以找到，那么我们就可以做完全恢复了，不用丢失数据了，这里不演示了，在其它方法中演示。这里我们重新启动数据库就会以spfile来启动数据库了，当然不建议使用shutdown abort来强制关库：
[oracle@testdb 2015_04_30]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 30 17:42:20 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 276827520 bytes
Database Buffers 121634816 bytes
Redo Buffers 8503296 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileora11g.ora
SQL>
SQL>
SQL> select file#,name FILE_NAME from v$dbfile;
FILE# FILE_NAME
---------- --------------------------------------------------
4 /u01/app/oracle/oradata/ora11g/users01.dbf
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
1 /u01/app/oracle/oradata/ora11g/system01.dbf
5 /u01/app/oracle/oradata/ora11g/example01.dbf
SQL>
SQL> col FILE_NAME format a50
SQL> select FILE_NAME,FILE_ID,a.TABLESPACE_NAME, b.status ts_status ,BYTES from dba_data_files a,dba_tablespaces b where a.TABLESPACE_NAME=b.TABLESPACE_NAME;
FILE_NAME FILE_ID TABLESPACE_NAME TS_STATUS BYTES
-------------------------------------------------- ---------- ------------------------------ --------- ----------
/u01/app/oracle/oradata/ora11g/users01.dbf 4 USERS ONLINE 5242880
/u01/app/oracle/oradata/ora11g/undotbs01.dbf 3 UNDOTBS1 ONLINE 99614720
/u01/app/oracle/oradata/ora11g/sysaux01.dbf 2 SYSAUX ONLINE 566231040
/u01/app/oracle/oradata/ora11g/system01.dbf 1 SYSTEM ONLINE 744488960
/u01/app/oracle/oradata/ora11g/example01.dbf 5 EXAMPLE READ ONLY 328335360
17:50:35 SQL> select count(1) from lhr.test;
COUNT(1)
----------
75204
17:50:44 SQL>
好，数据库已经恢复，后边要做的就是备份新库等其他操作，这里就不演示了,至于第二种办法且看下一篇blog。
在上一篇blog中我们介绍了采用dbms_backup_restore来找回控制文件并恢复整个数据库的方法，本篇blog我们介绍采用创建临时库来找回控制文件的方法。
1.1.1 方法二：尝试采用创建临时库来找回控制文件
由于RMAN必须工作在MOUNT模式，所有的数据文件都丢失，无法通过只重建控制文件将其启动到MOUNT模式，所以这里利用dbca创建一个临时数据库环境，数据库的名称与原有名称保持不变，文件存放到默认位置即可。
一、 搭建临时库来注册备份集
这里我们假设有一个临时环境供我们测试，ORACLE_SID=orcltest，由于是第二天做的测试，所以我重新对原库做了备份并传递到备份库
[root@rhel6_lhr backupset]# scp -r 2015_05_02 oracle@192.168.59.129:/tmp
oracle@192.168.59.129's password:
o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp 100% 9728 9.5KB/s 00:00
o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp 100% 236MB 6.6MB/s 00:36
o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp 100% 1096MB 54.8MB/s 00:20
o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp 100% 10MB 9.8MB/s 00:00
o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp 100% 9600KB 9.4MB/s 00:00
[root@rhel6_lhr backupset]#
[root@rhel6_lhr 2015_05_02]# ll -h
total 1.4G
-rw-r----- 1 oracle asmadmin 9.9M May 2 11:23 o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
-rw-r----- 1 oracle asmadmin 9.5K May 2 11:31 o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
-rw-r----- 1 oracle asmadmin 9.4M May 2 11:31 o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
-rw-r----- 1 oracle asmadmin 236M May 2 10:56 o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
-rw-r----- 1 oracle asmadmin 1.1G May 2 11:31 o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
由下边的方法（备份集中无控制文件的备份）的方法得知，这里创建一个临时库的方法比较麻烦，不太推荐：
[oracle@orcltest tmp]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname orcltest -sid orcltest -sysPassword lhr -systemPassword lhr -responseFile NO_VALUE -datafileDestination /u02/app/oracle/oradata -redoLogFileSize 50 -recoveryAreaDestination /u02/app/oracle/flash_recovery_area -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 30 -totalMemory 200 -databaseType OLTP -emConfiguration NONE -automaticMemoryManagement true
Copying database files
1% complete
3% complete
10% complete
17% complete
24% complete
35% complete
Creating and starting Oracle instance
37% complete
42% complete
47% complete
52% complete
53% complete
56% complete
58% complete
Registering database with Oracle Restart
64% complete
Completing Database Creation
68% complete
71% complete
75% complete
85% complete
96% complete
100% complete
Look at the log file "/u02/app/oracle/cfgtoollogs/dbca/orcltest/orcltest0.log" for further details.
[oracle@orcltest tmp]$ crsstat
Name Type Target State Host
------------------------------ -------------------------- ---------- --------- -------
ora.DATA.dg ora.diskgroup.type ONLINE ONLINE orcltest
ora.LISTENER.lsnr ora.listener.type ONLINE ONLINE orcltest
ora.asm ora.asm.type ONLINE ONLINE orcltest
ora.cssd ora.cssd.type ONLINE ONLINE orcltest
ora.diskmon ora.diskmon.type OFFLINE OFFLINE
ora.evmd ora.evm.type ONLINE ONLINE orcltest
ora.ons ora.ons.type OFFLINE OFFLINE
ora.orcltest.db ora.database.type ONLINE ONLINE orcltest
[oracle@orcltest 2015_05_02]$ ll
total 1383664
-rw-r----- 1 oracle oinstall 10278400 May 2 11:34 o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
-rw-r----- 1 oracle oinstall 9728 May 2 11:33 o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
-rw-r----- 1 oracle oinstall 9830400 May 2 11:34 o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
-rw-r----- 1 oracle oinstall 247463936 May 2 11:34 o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
-rw-r----- 1 oracle oinstall 1149280256 May 2 11:34 o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
[oracle@orcltest 2015_05_02]$ ORACLE_SID=orcltest
[oracle@orcltest 2015_05_02]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat May 2 11:36:51 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@orcltest 2015_05_02]$
[oracle@orcltest 2015_05_02]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sat May 2 11:37:24 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLTEST (DBID=2626150865)
RMAN> list backupset;
specification does not match any backup in the repository
RMAN>
RMAN> catalog start with '/tmp/2015_05_02/';
using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp/2015_05_02/
List of Files Unknown to the Database
=====================================
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
File Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
no files cataloged
List of Files Which Where Not Cataloged
=======================================
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
RMAN-07518: Reason: Foreign database file DBID: 4270446895 Database Name: ORA11G
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
RMAN-07518: Reason: Foreign database file DBID: 4270446895 Database Name: ORA11G
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
RMAN-07518: Reason: Foreign database file DBID: 4270446895 Database Name: ORA11G
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
RMAN-07518: Reason: Foreign database file DBID: 4270446895 Database Name: ORA11G
File Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
RMAN-07518: Reason: Foreign database file DBID: 4270446895 Database Name: ORA11G
RMAN>
RMAN> catalog backuppiece '/tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp';
ORA-19870: error while restoring backup piece /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
ORA-19691: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp is from different database: id=4270446895, name=ORA11G
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 05/02/2015 11:40:26
RMAN-06209: List of failed objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
RMAN>
RMAN>
由此可以看出，命令catalog start with '/tmp/2015_05_02/'; 在异库注册可以查看到原库的信息：DBID: 4270446895 Database Name: ORA11G，（10g下测试也可以获取到这些信息）这里dbname和dbid都不一样所以不能注册在该数据库下，将备份片的信息加入到控制文件的时候报错，原因在于原有数据库和临时数据库的DBID不同，那么我们是否可以尝试修改临时数据库的DBID，使它与原有数据库DBID保持一致呢，试试呗。
1、 修改dbname和dbid
首先启动临时库到open read only状态，然后执行脚本：
[oracle@orcltest dbs]$ ORACLE_SID=orcltest
[oracle@orcltest dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat May 2 12:56:05 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open read only;
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 281021824 bytes
Database Buffers 117440512 bytes
Redo Buffers 8503296 bytes
Database mounted.
Database opened.
SQL> @?/dbs/change_dbid.sql
PL/SQL procedure successfully completed.
OLD_NAME
--------------------------------
ORCLTEST
Enter the new Database Name:ORA11G
Enter the new Database ID:4270446895
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Convert ORCLTEST(2626156129) to ORA11G(4270446895)
PL/SQL procedure successfully completed.
ControlFile:
=> Change Name:1
=> Change DBID:1
DataFile: /u02/app/oracle/oradata/orcltest/system01.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
DataFile: /u02/app/oracle/oradata/orcltest/sysaux01.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
DataFile: /u02/app/oracle/oradata/orcltest/undotbs01.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
DataFile: /u02/app/oracle/oradata/orcltest/users01.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
DataFile: /u02/app/oracle/oradata/orcltest/example01.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
DataFile: /u02/app/oracle/oradata/orcltest/temp01.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
PL/SQL procedure successfully completed.
SQL> create pfile from spfile;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@orcltest dbs]$ ll
total 52
-rw-r--r-- 1 oracle oinstall 2239 May 2 12:33 change_dbid.sql
-rw-rw---- 1 oracle oinstall 1544 May 2 10:56 hc_DBUA5452531.dat
-rw-rw---- 1 oracle oinstall 1544 May 2 12:45 hc_ORA11G.dat
-rw-rw---- 1 oracle oinstall 1544 May 2 12:56 hc_orcltest.dat
-rw-r----- 1 oracle oinstall 165 May 2 10:54 initDBUA5452531.ora
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r--r-- 1 oracle oinstall 640 May 2 12:10 initORA11G.ora
-rw-r--r-- 1 oracle oinstall 997 May 2 12:58 initorcltest.ora
-rw-r----- 1 oracle oinstall 0 May 2 10:55 lkinstDBUA5452531
-rw-r----- 1 oracle oinstall 24 May 2 12:10 lkORA11G
-rw-r----- 1 oracle oinstall 24 Apr 21 16:27 lkORCLTEST
-rw-r----- 1 oracle oinstall 1536 May 2 10:54 orapwDBUA5452531
-rw-r----- 1 oracle oinstall 1536 May 2 12:53 orapworcltest
-rw-r----- 1 oracle oinstall 3584 May 2 12:57 spfileorcltest.ora
[oracle@orcltest dbs]$ vi initorcltest.ora
。。。。
*.db_name='ORA11G'
。。。。
[oracle@orcltest dbs]$ mv initorcltest.ora initORA11G.ora
[oracle@orcltest dbs]$ ORACLE_SID=ORA11G
[oracle@orcltest dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat May 2 13:04:44 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup mount;
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 272633216 bytes
Database Buffers 125829120 bytes
Redo Buffers 8503296 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> select dbid,name from v$database;
DBID NAME
---------- ---------
4270446895 ORA11G
SQL>
好了，和原库一样了，现在我们重新注册
2、 重新注册
[oracle@orcltest dbs]$ORACLE_SID=ORA11G
[oracle@orcltest dbs]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sat May 2 13:10:35 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=4270446895)
RMAN> catalog start with '/tmp/2015_05_02/';
using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp/2015_05_02/
List of Files Unknown to the Database
=====================================
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
File Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
File Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
RMAN>
成功注册，接下来就简单了，查看备份集中含有哪些内容
RMAN> list backupset;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1 9.00K DISK 00:00:00 02-MAY-15
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20150502T113152
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 15 1145640 02-MAY-15 1145812 02-MAY-15
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 235.99M DISK 00:00:00 02-MAY-15
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20150502T105103
Piece Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1122334 02-MAY-15 /u02/app/oracle/oradata/orcltest/system01.dbf
2 Full 1122334 02-MAY-15 /u02/app/oracle/oradata/orcltest/sysaux01.dbf
3 Full 1122334 02-MAY-15 /u02/app/oracle/oradata/orcltest/undotbs01.dbf
4 Full 1122334 02-MAY-15 /u02/app/oracle/oradata/orcltest/users01.dbf
5 Full 1092435 30-APR-15 /u02/app/oracle/oradata/orcltest/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 1.07G DISK 00:00:00 02-MAY-15
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112355
Piece Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1145650 02-MAY-15 /u02/app/oracle/oradata/orcltest/system01.dbf
2 Full 1145650 02-MAY-15 /u02/app/oracle/oradata/orcltest/sysaux01.dbf
3 Full 1145650 02-MAY-15 /u02/app/oracle/oradata/orcltest/undotbs01.dbf
4 Full 1145650 02-MAY-15 /u02/app/oracle/oradata/orcltest/users01.dbf
5 Full 1092435 30-APR-15 /u02/app/oracle/oradata/orcltest/example01.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4 9.80M DISK 00:00:00 02-MAY-15
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112352
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 13 1122206 02-MAY-15 1145003 02-MAY-15
1 14 1145003 02-MAY-15 1145640 02-MAY-15
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 9.36M DISK 00:00:00 02-MAY-15
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112355
Piece Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
SPFILE Included: Modification time: 02-MAY-15
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1145807 Ckp time: 02-MAY-15
RMAN>
我们可以看到，控制文件和spfile都在备份文件： /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp 中，有归档文件，有数据文件，此时我们就可以考虑还原策略了，我们可以使用备份中的spfile和控制文件，也可以只还原数据文件，这个根据自己的需要而定，这里我们测试的话就从spfile、control file，datafile都还原吧。
二、 还原操作 1、 还原spfile
RMAN> restore spfile from '/tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp';
Starting restore at 02-MAY-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=144 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 02-MAY-15
RMAN>
[oracle@orcltest ~]$ cd $ORACLE_HOME/dbs
[oracle@orcltest dbs]$ ll spfile*
-rw-r----- 1 oracle oinstall 2560 May 2 13:21 spfileORA11G.ora
-rw-r----- 1 oracle oinstall 3584 May 2 12:57 spfileorcltest.ora
[oracle@orcltest dbs]$
2、 还原控制文件
RMAN> restore controlfile to '/tmp/2015_05_02/cont.ctl' from '/tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp';
Starting restore at 02-MAY-15
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 02-MAY-15
RMAN>
这里可以采用原数据库的控制文件，即现在还原出来的控制文件，也可以采用目前临时库的控制文件，都可以，当然，如果备份集中没有控制文件的备份的话我们可以考虑 采用临时库的控制文件，或者备份片段中没有控制文件的备份 这个方法。
3、 还原database
这里分情况，如果采用原库的控制文件的话，我们可以这样还原：
①、 方法一：采用原库控制文件--推荐
将原库控制文件拷贝到pfile定义的相关目录：
[oracle@orcltest orcltest]$ cp /tmp/2015_05_02/cont.ctl /u02/app/oracle/flash_recovery_area/orcltest/control02.ctl
[oracle@orcltest orcltest]$ cp /tmp/2015_05_02/cont.ctl /u02/app/oracle/oradata/orcltest/control01.ctl
[oracle@orcltest orcltest]$ cd /u01/app/oracle/oradata/ora11g/
[oracle@orcltest dbs]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sat May 2 13:32:55 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=4270446895, not open)
RMAN> list backupset;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
6 9.80M DISK 00:00:00 02-MAY-15
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112352
Piece Name: /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
List of Archived Logs in backup set 6
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 13 1122206 02-MAY-15 1145003 02-MAY-15
1 14 1145003 02-MAY-15 1145640 02-MAY-15
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 1.07G DISK 00:07:46 02-MAY-15
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112355
Piece Name: /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1145650 02-MAY-15 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1145650 02-MAY-15 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1145650 02-MAY-15 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 Full 1145650 02-MAY-15 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1092435 30-APR-15 /u01/app/oracle/oradata/ora11g/example01.dbf
RMAN> delete backupset;
Starting implicit crosscheck backup at 02-MAY-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 02-MAY-15
Starting implicit crosscheck copy at 02-MAY-15
using channel ORA_DISK_1
Finished implicit crosscheck copy at 02-MAY-15
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u02/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_02/o1_mf_1_2_bn8o55o8_.arc
File Name: /u02/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_02/o1_mf_1_3_bn8o6tkw_.arc
File Name: /u02/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_02/o1_mf_1_1_bn8o4j7z_.arc
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
6 6 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
7 7 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp RECID=6 STAMP=878642633
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp RECID=7 STAMP=878642635
Deleted 2 objects
RMAN> crosscheck backupset;
using channel ORA_DISK_1
specification does not match any backup in the repository
RMAN> list backupset;
specification does not match any backup in the repository
RMAN> exit
Recovery Manager complete.
[oracle@orcltest dbs]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sat May 2 13:33:41 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=4270446895, not open)
RMAN> list backupset;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
RMAN> catalog start with '/tmp/2015_05_02/';
searching for all files that match the pattern /tmp/2015_05_02/
List of Files Unknown to the Database
=====================================
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
File Name: /tmp/2015_05_02/cont.ctl
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
File Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
File Name: /tmp/2015_05_02/cont.ctl
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
File Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
RMAN> list backupset;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
8 9.00K DISK 00:00:00 02-MAY-15
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20150502T113152
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
List of Archived Logs in backup set 8
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 15 1145640 02-MAY-15 1145812 02-MAY-15
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9 Full 235.99M DISK 00:00:00 02-MAY-15
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20150502T105103
Piece Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
List of Datafiles in backup set 9
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1122334 02-MAY-15 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1122334 02-MAY-15 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1122334 02-MAY-15 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 Full 1122334 02-MAY-15 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1092435 30-APR-15 /u01/app/oracle/oradata/ora11g/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10 Full 1.07G DISK 00:00:00 02-MAY-15
BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112355
Piece Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
List of Datafiles in backup set 10
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1145650 02-MAY-15 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1145650 02-MAY-15 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1145650 02-MAY-15 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 Full 1145650 02-MAY-15 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1092435 30-APR-15 /u01/app/oracle/oradata/ora11g/example01.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
11 9.80M DISK 00:00:00 02-MAY-15
BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112352
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 13 1122206 02-MAY-15 1145003 02-MAY-15
1 14 1145003 02-MAY-15 1145640 02-MAY-15
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
12 Full 9.36M DISK 00:00:00 02-MAY-15
BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112355
Piece Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
SPFILE Included: Modification time: 02-MAY-15
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1145807 Ckp time: 02-MAY-15
RMAN> restore database;
Starting restore at 02-MAY-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ora11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
ORA-19504: failed to create file "/u01/app/oracle/oradata/ora11g/system01.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ora11g/example01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
ORA-19504: failed to create file "/u01/app/oracle/oradata/ora11g/example01.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
failover to previous backup
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ora11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
ORA-19504: failed to create file "/u01/app/oracle/oradata/ora11g/system01.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ora11g/example01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
ORA-19504: failed to create file "/u01/app/oracle/oradata/ora11g/example01.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
failover to previous backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/02/2015 13:34:38
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
缺少目录，我们建立目录重试：
[oracle@orcltest orcltest]$ mkdir -p /u01/app/oracle/oradata/ora11g/
RMAN> restore database;
Starting restore at 02-MAY-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ora11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
channel ORA_DISK_1: piece handle=/tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp tag=TAG20150502T112355
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ora11g/example01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
channel ORA_DISK_1: piece handle=/tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp tag=TAG20150502T105103
channel ORA_DISK_1: restored backup piece 1
restore not complete
Restore did not complete for some files from backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp (piecenum=1, pieces_done=1, done=FALSE, failover=FALSE)
Please check alert log for additional information.
failover to previous backup
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ora11g/example01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
channel ORA_DISK_1: piece handle=/tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp tag=TAG20150502T112355
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 02-MAY-15
RMAN> recover database;
Starting recover at 02-MAY-15
using channel ORA_DISK_1
datafile 5 not processed because file is read-only
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=15
channel ORA_DISK_1: reading from backup piece /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
channel ORA_DISK_1: piece handle=/tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp tag=TAG20150502T113152
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u02/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_02/o1_mf_1_15_bn8ro53m_.arc thread=1 sequence=15
channel default: deleting archived log(s)
archived log file name=/u02/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_02/o1_mf_1_15_bn8ro53m_.arc RECID=10 STAMP=878650661
unable to find archived log
archived log thread=1 sequence=16
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/02/2015 13:37:42
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 16 and starting SCN of 1145812
RMAN> recover database until sequence 16;
Starting recover at 02-MAY-15
using channel ORA_DISK_1
datafile 5 not processed because file is read-only
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 02-MAY-15
RMAN> alter database open resetlogs;
database opened
RMAN> exit
Recovery Manager complete.
[oracle@orcltest dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat May 2 13:40:07 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select count(1) from lhr.test;
COUNT(1)
----------
75204
SQL>
OK，一切搞定，其他的后续工作别忘记了。
②、 方法二：采用临时库的控制文件，或者备份片段中没有控制文件的备份
如果采用临时库的控制文件来还原数据库的，会出现这样的问题：
RMAN> restore database;
Starting restore at 02-MAY-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/02/2015 13:48:39
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
RMAN> list backupset;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1 9.00K DISK 00:00:00 02-MAY-15
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20150502T113152
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 15 1145640 02-MAY-15 1145812 02-MAY-15
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 235.99M DISK 00:00:00 02-MAY-15
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20150502T105103
Piece Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1122334 02-MAY-15 /u02/app/oracle/oradata/orcltest/system01.dbf
2 Full 1122334 02-MAY-15 /u02/app/oracle/oradata/orcltest/sysaux01.dbf
3 Full 1122334 02-MAY-15 /u02/app/oracle/oradata/orcltest/undotbs01.dbf
4 Full 1122334 02-MAY-15 /u02/app/oracle/oradata/orcltest/users01.dbf
5 Full 1092435 30-APR-15 /u02/app/oracle/oradata/orcltest/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 1.07G DISK 00:00:00 02-MAY-15
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112355
Piece Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1145650 02-MAY-15 /u02/app/oracle/oradata/orcltest/system01.dbf
2 Full 1145650 02-MAY-15 /u02/app/oracle/oradata/orcltest/sysaux01.dbf
3 Full 1145650 02-MAY-15 /u02/app/oracle/oradata/orcltest/undotbs01.dbf
4 Full 1145650 02-MAY-15 /u02/app/oracle/oradata/orcltest/users01.dbf
5 Full 1092435 30-APR-15 /u02/app/oracle/oradata/orcltest/example01.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4 9.80M DISK 00:00:00 02-MAY-15
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112352
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 13 1122206 02-MAY-15 1145003 02-MAY-15
1 14 1145003 02-MAY-15 1145640 02-MAY-15
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 9.36M DISK 00:00:00 02-MAY-15
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112355
Piece Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
SPFILE Included: Modification time: 02-MAY-15
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1145807 Ckp time: 02-MAY-15
RMAN>
虽然能用list backup命令看到相关数据文件的元数据信息，但是执行restore database却无法找到相应数据文件的备份，这时可以用到强大的DBMS_BACKUP_RESTORE包执行恢复操作，执行如下PL/SQL命令，注意如果同一个数据文件在多个备份集中，只写一个备份集名称即可：
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'/u02/app/oracle/oradata/orcltest/system01.dbf');
8 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'/u02/app/oracle/oradata/orcltest/sysaux01.dbf');
9 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'/u02/app/oracle/oradata/orcltest/undotbs01.dbf');
10 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'/u02/app/oracle/oradata/orcltest/users01.dbf');
11 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'/u02/app/oracle/oradata/orcltest/example01.dbf');
12 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp', params=>null);
13 sys.dbms_backup_restore.deviceDeallocate;
14 END;
15 /
PL/SQL procedure successfully completed.
[oracle@orcltest orcltest]$ ll -h
total 1.7G
-rw-r----- 1 oracle oinstall 9.3M May 2 14:00 control01.ctl
-rw-r----- 1 oracle oinstall 314M May 2 13:59 example01.dbf
-rw-r----- 1 oracle oinstall 551M May 2 13:59 sysaux01.dbf
-rw-r----- 1 oracle oinstall 721M May 2 13:59 system01.dbf
-rw-r----- 1 oracle oinstall 96M May 2 13:58 undotbs01.dbf
-rw-r----- 1 oracle oinstall 16M May 2 13:58 users01.dbf
[oracle@orcltest orcltest]$
好，我们看到数据文件已经到位，接下来就是recover操作了。
RMAN> recover database;
Starting recover at 02-MAY-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/02/2015 14:01:45
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/u02/app/oracle/oradata/orcltest/system01.dbf'
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORA11G 4270446895 PARENT 995548 02-MAY-15
3 3 ORA11G 4270446895 ORPHAN 995548 13-MAR-15
2 2 ORA11G 4270446895 CURRENT 1091863 02-MAY-15
RMAN> reset database to incarnation 3;
database reset to incarnation 3
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORA11G 4270446895 ORPHAN 995548 02-MAY-15
3 3 ORA11G 4270446895 CURRENT 995548 13-MAR-15
2 2 ORA11G 4270446895 ORPHAN 1091863 02-MAY-15
RMAN> recover database;
Starting recover at 02-MAY-15
using channel ORA_DISK_1
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/02/2015 14:07:33
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start
ORA-00283: recovery session canceled due to errors
ORA-01206: file is not part of this database - wrong database id
ORA-01110: data file 5: '/u02/app/oracle/oradata/orcltest/example01.dbf'
[oracle@orcltest ~]$ oerr ora 1206
01206, 00000, "file is not part of this database - wrong database id"
// *Cause: The database ID in the file header does not match the database id
// in the control file. The file may be from a different database, or
// it may not be a database file at all. If the database was rebuilt,
// this may be a file from before the rebuild. Note that if you see
// this error when the file is supposed to be plugged in from another
// database via the Transportable Tablespace feature, it means the
// database ID in the file header does not match the one expected.
// *Action: Restore a copy of the correct data file and do recovery as needed.
[oracle@orcltest ~]$
查了下资料发现：
ORA-01110: data file 9: 'H:xxxxxxxxx.dbf'
ORA-01206: file is not part of this database - wrong database id
Datafile 9 was a read-only tablespace, and the source database was actually created with RMAN DUPLICATE. Datafile 9 was read-only during that duplicate operation, and the status hadn’t changed since then. As you can imagine, the read-only datafiles were not changed and their headers still contained the DBID of the database that was the source of the RMAN DUPLICATE. A similar situation could probably happen if tablespaces were imported using transportable tablespaces feature, and left read only.
The fix in this case is to make tablespaces read-write for a moment, and then change back to read-only. The read-write operation will write new datafile headers and, consequently, put there the “right” DBID. IMPORTANT — this has to be done before backup and not after a disaster strikes. This case just emphasizes again the most important rule of any backup/recovery strategy is to do regular test-restores.
If it’s too late and something hit the fan — well, you probably have a chance to offline drop those tablespaces and, hopefully, be able to import them back, if those are transportable tablespaces, and the metadata dump file is still available.
Another idea would be to offline datafiles and then online them after OPEN RESETLOGS. Should someone try that — let us know if it works
知道了，原来example表空间是只读的，这个有点麻烦了，本来没有只读文件的话，直接recover做不完全恢复就ＯＫ了，这样一来我们就必须重建控制文件了，这里如果没有只读文件的话直接ｒｅｃｏｖｅｒ就可以成功了。
SQL> alter database backup controlfile to trace as '/tmp/aab.txt';
Database altered.
SQL>
拿出/tmp/aab.txt中创建控制文件的部分，直接在ｎｏｍｏｕｎｔ下创建控制文件，注意这里去掉只读数据文件，关于只读数据文件的处理参考：http://blog.itpub.net/26736162/viewspace-1425283/：
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA11G" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u02/app/oracle/oradata/orcltest/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u02/app/oracle/oradata/orcltest/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u02/app/oracle/oradata/orcltest/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u02/app/oracle/oradata/orcltest/system01.dbf',
'/u02/app/oracle/oradata/orcltest/sysaux01.dbf',
'/u02/app/oracle/oradata/orcltest/undotbs01.dbf',
'/u02/app/oracle/oradata/orcltest/users01.dbf'
-- '/u02/app/oracle/oradata/orcltest/example01.dbf'
CHARACTER SET ZHS16GBK
;
[oracle@orcltest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat May 2 14:52:41 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA11G" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u02/app/oracle/oradata/orcltest/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u02/app/oracle/oradata/orcltest/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u02/app/oracle/oradata/orcltest/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u02/app/oracle/oradata/orcltest/system01.dbf',
'/u02/app/oracle/oradata/orcltest/sysaux01.dbf',
'/u02/app/oracle/oradata/orcltest/undotbs01.dbf',
'/u02/app/oracle/oradata/orcltest/users01.dbf'
CHARACTER SET ZHS16GBK
;
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 272633216 bytes
Database Buffers 125829120 bytes
Redo Buffers 8503296 bytes
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
Control file created.
SQL> SQL>
SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@orcltest ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sat May 2 14:53:01 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=4270446895, not open)
RMAN> catalog start with '/tmp/2015_05_02/';
using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp/2015_05_02/
List of Files Unknown to the Database
=====================================
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
File Name: /tmp/2015_05_02/cont.ctl
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
File Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
File Name: /tmp/2015_05_02/cont.ctl
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
File Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
RMAN> list backupset;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
1 9.00K DISK 00:00:00 2015-05-02 11:31:52
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20150502T113152
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 15 1145640 2015-05-02 11:23:48 1145812 2015-05-02 11:31:52
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
2 Full 235.99M DISK 00:00:00 2015-05-02 10:51:03
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20150502T105103
Piece Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1122334 2015-05-02 10:51:03 /u02/app/oracle/oradata/orcltest/system01.dbf
2 Full 1122334 2015-05-02 10:51:03 /u02/app/oracle/oradata/orcltest/sysaux01.dbf
3 Full 1122334 2015-05-02 10:51:03 /u02/app/oracle/oradata/orcltest/undotbs01.dbf
4 Full 1122334 2015-05-02 10:51:03 /u02/app/oracle/oradata/orcltest/users01.dbf
5 Full 1092435 2015-04-30 15:42:04
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
3 Full 1.07G DISK 00:00:00 2015-05-02 11:23:55
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112355
Piece Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1145650 2015-05-02 11:23:55 /u02/app/oracle/oradata/orcltest/system01.dbf
2 Full 1145650 2015-05-02 11:23:55 /u02/app/oracle/oradata/orcltest/sysaux01.dbf
3 Full 1145650 2015-05-02 11:23:55 /u02/app/oracle/oradata/orcltest/undotbs01.dbf
4 Full 1145650 2015-05-02 11:23:55 /u02/app/oracle/oradata/orcltest/users01.dbf
5 Full 1092435 2015-04-30 15:42:04
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
4 9.80M DISK 00:00:00 2015-05-02 11:23:53
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112352
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 13 1122206 2015-05-02 10:50:47 1145003 2015-05-02 11:21:26
1 14 1145003 2015-05-02 11:21:26 1145640 2015-05-02 11:23:48
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
5 Full 9.36M DISK 00:00:00 2015-05-02 11:31:50
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112355
Piece Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
SPFILE Included: Modification time: 2015-05-02 11:21:13
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1145807 Ckp time: 2015-05-02 11:31:50
RMAN> recover database;
Starting recover at 2015-05-02 14:58:31
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=15
channel ORA_DISK_1: reading from backup piece /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
channel ORA_DISK_1: piece handle=/tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp tag=TAG20150502T113152
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u02/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_02/o1_mf_1_15_bn8xdzo2_.arc thread=1 sequence=15
channel default: deleting archived log(s)
archived log file name=/u02/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_02/o1_mf_1_15_bn8xdzo2_.arc RECID=1 STAMP=878655519
unable to find archived log
archived log thread=1 sequence=16
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/02/2015 14:58:46
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 16 and starting SCN of 1145812
RMAN> recover database until sequence 16;
Starting recover at 2015-05-02 14:59:16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2015-05-02 14:59:19
RMAN> alter database open resetlogs;
database opened
RMAN> exit
Recovery Manager complete.
ｒｅｃｏｖｅｒ 过程中的告警日志：
Sat May 02 15:00:37 2015
[20103] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:13457914 end:13468244 diff:10330 (103 seconds)
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'EXAMPLE' #6 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #5 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00005' in the controlfile.
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Sat May 02 15:00:39 2015
SMON: enabling tx recovery
*********************************************************************
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
*********************************************************************
Database Characterset is ZHS16GBK
Sat May 02 15:00:55 2015
No Resource Manager plan active
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
then disabled.
**********************************************************
replication_dependency_tracking turned off (no async multimaster replication found)
Sat May 02 15:01:31 2015
Errors in file /u02/app/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_ora_20103.trc (incident=13353):
ORA-25319: Queue table repartitioning aborted
Sat May 02 15:01:31 2015
Checker run found 2 new persistent data failures
Incident details in: /u02/app/oracle/diag/rdbms/ora11g/ORA11G/incident/incdir_13353/ORA11G_ora_20103_i13353.trc
Sat May 02 15:01:48 2015
Dumping diagnostic data in directory=[cdmp_20150502150148], requested by (instance=1, osid=20103), summary=[incident=13353].
Sat May 02 15:01:50 2015
error 25319 happened during Queue table repartitioning
Starting background process QMNC
Sat May 02 15:01:50 2015
QMNC started with pid=26, OS id=20168
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Sat May 02 15:02:03 2015
Sweep [inc][13353]: completed
Sweep [inc2][13353]: completed
Sat May 02 15:02:24 2015
Completed: alter database open resetlogs
Sat May 02 15:02:24 2015
Errors in file /u02/app/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_mmon_20057.trc (incident=13321):
ORA-25319: Queue table repartitioning aborted
Incident details in: /u02/app/oracle/diag/rdbms/ora11g/ORA11G/incident/incdir_13321/ORA11G_mmon_20057_i13321.trc
error 25319 happened during Queue table repartitioning
Sat May 02 15:02:26 2015
Dumping diagnostic data in directory=[cdmp_20150502150226], requested by (instance=1, osid=20057 (MMON)), summary=[incident=13321].
Sat May 02 15:02:36 2015
db_recovery_file_dest_size of 4122 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Sat May 02 15:02:39 2015
Starting background process CJQ0
Sat May 02 15:02:39 2015
CJQ0 started with pid=30, OS id=20198
Sat May 02 15:02:44 2015
Errors in file /u02/app/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_m000_20194.trc:
ORA-25153: Temporary Tablespace is Empty
Setting Resource Manager plan SCHEDULER[0x318E]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sat May 02 15:02:46 2015
Starting background process VKRM
Sat May 02 15:02:46 2015
VKRM started with pid=20, OS id=20204
Sat May 02 15:02:57 2015
Sweep [inc][13321]: completed
Sweep [inc2][13321]: completed
ＯＫ，基本恢复完成，还剩下只读文件了，我们看看只读文件如何恢复：
[oracle@orcltest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat May 2 15:02:33 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set line 9999 pagesize 9999
SQL> col FILE_NAME format a50
SQL> select file#,name FILE_NAME,status from v$datafile;
FILE# FILE_NAME STATUS
---------- -------------------------------------------------- -------
1 /u02/app/oracle/oradata/orcltest/system01.dbf SYSTEM
2 /u02/app/oracle/oradata/orcltest/sysaux01.dbf ONLINE
3 /u02/app/oracle/oradata/orcltest/undotbs01.dbf ONLINE
4 /u02/app/oracle/oradata/orcltest/users01.dbf ONLINE
5 /u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSIN OFFLINE
G00005
SQL> alter database rename file '/u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00005' to '/u02/app/oracle/oradata/orcltest/example01.dbf';
Database altered.
SQL> set line 9999 pagesize 9999
SQL> col FILE_NAME format a50
SQL> select file#,name FILE_NAME,status from v$datafile;
FILE# FILE_NAME STATUS
---------- -------------------------------------------------- -------
1 /u02/app/oracle/oradata/orcltest/system01.dbf SYSTEM
2 /u02/app/oracle/oradata/orcltest/sysaux01.dbf ONLINE
3 /u02/app/oracle/oradata/orcltest/undotbs01.dbf ONLINE
4 /u02/app/oracle/oradata/orcltest/users01.dbf ONLINE
5 /u02/app/oracle/oradata/orcltest/example01.dbf OFFLINE
SQL> select FILE_NAME,FILE_ID,a.TABLESPACE_NAME, b.status ts_status ,BYTES from dba_data_files a,dba_tablespaces b where a.TABLESPACE_NAME=b.TABLESPACE_NAME;
FILE_NAME FILE_ID TABLESPACE_NAME TS_STATUS BYTES
-------------------------------------------------- ---------- ------------------------------ --------- ----------
/u02/app/oracle/oradata/orcltest/users01.dbf 4 USERS ONLINE 15728640
/u02/app/oracle/oradata/orcltest/undotbs01.dbf 3 UNDOTBS1 ONLINE 99614720
/u02/app/oracle/oradata/orcltest/sysaux01.dbf 2 SYSAUX ONLINE 576716800
/u02/app/oracle/oradata/orcltest/system01.dbf 1 SYSTEM ONLINE 754974720
/u02/app/oracle/oradata/orcltest/example01.dbf 5 EXAMPLE READ ONLY
SQL> alter tablespace EXAMPLE online;
Tablespace altered.
SQL> select file#,name FILE_NAME,status from v$datafile;
FILE# FILE_NAME STATUS
---------- -------------------------------------------------- -------
1 /u02/app/oracle/oradata/orcltest/system01.dbf SYSTEM
2 /u02/app/oracle/oradata/orcltest/sysaux01.dbf ONLINE
3 /u02/app/oracle/oradata/orcltest/undotbs01.dbf ONLINE
4 /u02/app/oracle/oradata/orcltest/users01.dbf ONLINE
5 /u02/app/oracle/oradata/orcltest/example01.dbf ONLINE
SQL> select FILE_NAME,FILE_ID,a.TABLESPACE_NAME, b.status ts_status ,BYTES from dba_data_files a,dba_tablespaces b where a.TABLESPACE_NAME=b.TABLESPACE_NAME;
FILE_NAME FILE_ID TABLESPACE_NAME TS_STATUS BYTES
-------------------------------------------------- ---------- ------------------------------ --------- ----------
/u02/app/oracle/oradata/orcltest/users01.dbf 4 USERS ONLINE 15728640
/u02/app/oracle/oradata/orcltest/undotbs01.dbf 3 UNDOTBS1 ONLINE 99614720
/u02/app/oracle/oradata/orcltest/sysaux01.dbf 2 SYSAUX ONLINE 576716800
/u02/app/oracle/oradata/orcltest/system01.dbf 1 SYSTEM ONLINE 754974720
/u02/app/oracle/oradata/orcltest/example01.dbf 5 EXAMPLE READ ONLY 328335360
SQL> select count(1) from lhr.test;
COUNT(1)
----------
75204
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
临时文件的处理：
SQL>
SQL> select * from v$tempfile;
no rows selected
SQL> create temporary tablespace TEMP01 tempfile '/u02/app/oracle/oradata/orcltest/temp01.dbf' size 100m autoextend on next 10m;
Tablespace created.
SQL> alter database default temporary tablespace temp01;
Database altered.
SQL>
SQL> col name for a100
SQL> select file#,name from v$tempfile;
FILE# NAME
---------- ----------------------------------------------------------------------------------------------------
1 /u02/app/oracle/oradata/orcltest/temp01.dbf
SQL>
好了，至此我们就恢复了数据库了。
在上2篇blog中介绍了备份片中含有控制文件的备份的情况下，如何从备份集中找回控制文件的备份并恢复数据库，本篇blog来介绍下在备份片中没有控制文件的备份的情况下如何恢复数据库。
1.1 备份集中无控制文件情况下的数据库恢复
如果采用本文中所描述的3种方式均判断没有控制文件的备份的时候，那么我们唯一能做的就是重建控制文件了，而resetlogs方式重建控制文件之前需要控制文件脚本中的所有数据文件到位才能重建控制文件。
[root@orcltest 2015_05_02]# ll -h
total 1.2G
-rw-r----- 1 oracle oinstall 30M May 2 15:49 o1_mf_annnn_TAG20150502T154402_bn9022tb_.bkp
-rw-r----- 1 oracle oinstall 4.0K May 2 15:49 o1_mf_annnn_TAG20150502T154451_bn903mln_.bkp
-rw-r----- 1 oracle oinstall 1.1G May 2 15:49 o1_mf_nnndf_TAG20150502T154404_bn9025w1_.bkp
[root@orcltest 2015_05_02]# pwd
/tmp/2015_05_02
[oracle@orcltest dbs]$ cd $ORACLE_HOME/dbs
[oracle@orcltest dbs]$ more inittmp.ora
db_name=tmp
[oracle@orcltest dbs]$ ORACLE_SID=tmp
[oracle@orcltest dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun May 3 14:45:45 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'/tmp/2015_05_02/datafile1.dbf');
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_05_02/o1_mf_nnndf_TAG20150502T154404_bn9025w1_.bkp', params=>null);
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> SQL> ! ls -lh /tmp/2015_05_02/
total 1.9G
-rw-r----- 1 oracle asmadmin 721M May 3 14:46 datafile1.dbf
-rw-r----- 1 oracle oinstall 30M May 2 15:49 o1_mf_annnn_TAG20150502T154402_bn9022tb_.bkp
-rw-r----- 1 oracle oinstall 4.0K May 2 15:49 o1_mf_annnn_TAG20150502T154451_bn903mln_.bkp
-rw-r----- 1 oracle oinstall 1.1G May 2 15:49 o1_mf_nnndf_TAG20150502T154404_bn9025w1_.bkp
SQL> CREATE CONTROLFILE REUSE DATABASE "tmp" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/tmp/2015_05_02/redo01.log' SIZE 50M,
9 GROUP 2 '/tmp/2015_05_02/redo02.log' SIZE 50M
10 DATAFILE
11 '/tmp/2015_05_02/datafile1.dbf'
12 CHARACTER SET ZHS16GBK
13 ;
CREATE CONTROLFILE REUSE DATABASE "tmp" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01161: database name ORA11G in file header does not match given name of TMP
ORA-01110: data file 1: '/tmp/2015_05_02/datafile1.dbf'
由此可以看出备份集中的db_name为ORA11G，其实这些都是没有必要的，哪个dba不晓得数据库名呢？好吧，我们将pfile文件中的db_name修改一下，采用RESETLOGS创建控制文件，注意必须是RESETLOGS，NORESETLOGS需要online log文件在位，如下：
[oracle@orcltest dbs]$ more inittmp.ora
db_name=ORA11G
[oracle@orcltest dbs]$ echo $ORACLE_SID
tmp
[oracle@orcltest dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun May 3 14:56:44 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORA11G" RESETLOGS DATAFILE '/tmp/2015_05_02/datafile1.dbf';
Control file created.
SQL>
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/cntrltmp.dbf
SQL>
SQL> set line 9999 pagesize 9999
SQL> col FILE_NAME format a50
SQL> select file#,name FILE_NAME,status from v$datafile;
FILE# FILE_NAME STATUS
---------- -------------------------------------------------- -------
1 /tmp/2015_05_02/datafile1.dbf SYSTEM
SQL>
catalog 所有的backuppiece
[oracle@orcltest dbs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun May 3 15:40:32 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=4270446895, not open)
RMAN> list backupset;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
RMAN> catalog start with '/tmp/2015_05_02/';
searching for all files that match the pattern /tmp/2015_05_02/
List of Files Unknown to the Database
=====================================
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154451_bn903mln_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154402_bn9022tb_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T154404_bn9025w1_.bkp
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154451_bn903mln_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154402_bn9022tb_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T154404_bn9025w1_.bkp
RMAN>
RMAN> list backupset;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
1 3.50K DISK 00:00:00 2015-05-02 15:44:51
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20150502T154451
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154451_bn903mln_.bkp
List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 17 1166181 2015-05-02 15:44:02 1166209 2015-05-02 15:44:51
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
2 29.82M DISK 00:00:00 2015-05-02 15:44:02
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20150502T154402
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154402_bn9022tb_.bkp
List of Archived Logs in backup set 2
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 16 1145812 2015-05-02 11:31:52 1166181 2015-05-02 15:44:02
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
3 Full 1.07G DISK 00:00:00 2015-05-02 15:44:04
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20150502T154404
Piece Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T154404_bn9025w1_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1166189 2015-05-02 15:44:04 /tmp/2015_05_02/datafile1.dbf
2 Full 1166189 2015-05-02 15:44:04
3 Full 1166189 2015-05-02 15:44:04
4 Full 1166189 2015-05-02 15:44:04
5 Full 1166189 2015-05-02 15:44:04
6 Full 1166189 2015-05-02 15:44:04
RMAN>
RMAN> list backupset of spfile;
specification does not match any backup in the repository
RMAN> list backupset of controlfile;
specification does not match any backup in the repository
RMAN> list backupset of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
1 3.50K DISK 00:00:00 2015-05-02 15:44:51
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20150502T154451
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154451_bn903mln_.bkp
List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 17 1166181 2015-05-02 15:44:02 1166209 2015-05-02 15:44:51
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
2 29.82M DISK 00:00:00 2015-05-02 15:44:02
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20150502T154402
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154402_bn9022tb_.bkp
List of Archived Logs in backup set 2
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 16 1145812 2015-05-02 11:31:52 1166181 2015-05-02 15:44:02
RMAN> list backupset of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
3 Full 1.07G DISK 00:00:00 2015-05-02 15:44:04
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20150502T154404
Piece Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T154404_bn9025w1_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1166189 2015-05-02 15:44:04 /tmp/2015_05_02/datafile1.dbf
2 Full 1166189 2015-05-02 15:44:04
3 Full 1166189 2015-05-02 15:44:04
4 Full 1166189 2015-05-02 15:44:04
5 Full 1166189 2015-05-02 15:44:04
6 Full 1166189 2015-05-02 15:44:04
由备份集我们可以看出，有16和17号的归档文件备份，有6个数据文件备份，这里我们使用dbms_backup_restore package 来restore datafile。请注意：datafile的名字不重要，只要对于要恢复的datafile，是唯一的名字即可。
[oracle@orcltest dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun May 3 15:59:43 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
devtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>'d1');
6 dbms_backup_restore.RestoreSetDatafile;
7 --dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/tmp/2015_05_02/datafile1.dbf');
8 dbms_backup_restore.RestoreDatafileTo(dfnumber => 2,toname => '/tmp/2015_05_02/datafile2.dbf');
9 dbms_backup_restore.RestoreDatafileTo(dfnumber => 3,toname => '/tmp/2015_05_02/datafile3.dbf');
10 dbms_backup_restore.RestoreDatafileTo(dfnumber => 4,toname => '/tmp/2015_05_02/datafile4.dbf');
11 dbms_backup_restore.RestoreDatafileTo(dfnumber => 5,toname => '/tmp/2015_05_02/datafile5.dbf');
12 dbms_backup_restore.RestoreDatafileTo(dfnumber => 6,toname => '/tmp/2015_05_02/datafile6.dbf');
13 dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/tmp/2015_05_02/o1_mf_nnndf_TAG20150502T154404_bn9025w1_.bkp', params => null);
14 dbms_backup_restore.DeviceDeallocate;
15 END;
16 /
PL/SQL procedure successfully completed.
SQL>
SQL> set line 9999 pagesize 9999
SQL> col FILE_NAME format a50
SQL> select file#,name FILE_NAME,status from v$datafile;
FILE# FILE_NAME STATUS
---------- -------------------------------------------------- -------
1 /tmp/2015_05_02/datafile1.dbf SYSTEM
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
告警日志：
Sun May 03 15:59:44 2015
Full restore complete of datafile 6 to datafile copy /tmp/2015_05_02/datafile6.dbf. Elapsed time: 0:00:00
checkpoint is 1166189
last deallocation scn is 995550
Full restore complete of datafile 4 to datafile copy /tmp/2015_05_02/datafile4.dbf. Elapsed time: 0:00:02
checkpoint is 1166189
last deallocation scn is 3
Full restore complete of datafile 3 to datafile copy /tmp/2015_05_02/datafile3.dbf. Elapsed time: 0:00:02
checkpoint is 1166189
last deallocation scn is 1157819
Undo Optimization current scn is 1157346
Sun May 03 16:00:30 2015
Full restore complete of datafile 5 to datafile copy /tmp/2015_05_02/datafile5.dbf. Elapsed time: 0:00:44
checkpoint is 1166189
last deallocation scn is 1015098
Sun May 03 16:01:03 2015
Full restore complete of datafile 2 to datafile copy /tmp/2015_05_02/datafile2.dbf. Elapsed time: 0:01:18
checkpoint is 1166189
last deallocation scn is 1090388
元数据显示只有datafile 1，不奇怪，刚刚创建控制文件的时候只带了一个sysem文件，那么我们就需要重建控制文件以便带上所有需要恢复的datafiles
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA11G" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/tmp/2015_05_02/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/tmp/2015_05_02/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/tmp/2015_05_02/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/tmp/2015_05_02/datafile1.dbf',
'/tmp/2015_05_02/datafile2.dbf',
'/tmp/2015_05_02/datafile3.dbf',
'/tmp/2015_05_02/datafile4.dbf',
'/tmp/2015_05_02/datafile5.dbf',
'/tmp/2015_05_02/datafile6.dbf'
CHARACTER SET ZHS16GBK
;
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
SQL>
SQL> CREATE CONTROLFILE REUSE DATABASE "ORA11G" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/tmp/2015_05_02/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/tmp/2015_05_02/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/tmp/2015_05_02/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/tmp/2015_05_02/datafile1.dbf',
14 '/tmp/2015_05_02/datafile2.dbf',
15 '/tmp/2015_05_02/datafile3.dbf',
16 '/tmp/2015_05_02/datafile4.dbf',
17 '/tmp/2015_05_02/datafile5.dbf',
18 '/tmp/2015_05_02/datafile6.dbf'
19 CHARACTER SET ZHS16GBK
20 ;
Control file created.
SQL>
SQL> set line 9999 pagesize 9999
SQL> col FILE_NAME format a50
SQL> select file#,name FILE_NAME,status from v$datafile;
FILE# FILE_NAME STATUS
---------- -------------------------------------------------- -------
1 /tmp/2015_05_02/datafile1.dbf SYSTEM
2 /tmp/2015_05_02/datafile2.dbf RECOVER
3 /tmp/2015_05_02/datafile3.dbf RECOVER
4 /tmp/2015_05_02/datafile4.dbf RECOVER
5 /tmp/2015_05_02/datafile5.dbf RECOVER
6 /tmp/2015_05_02/datafile6.dbf RECOVER
6 rows selected.
SQL>
若是该备份不是冷备份，那么我们需要recover database，我们需要catalog 包括archivelog的backuppiece，然后restore archivelog，然后再recover
[oracle@orcltest dbs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun May 3 16:21:53 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=4270446895, not open)
RMAN> list backupset;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
RMAN> catalog start with '/tmp/2015_05_02/';
searching for all files that match the pattern /tmp/2015_05_02/
List of Files Unknown to the Database
=====================================
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154451_bn903mln_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154402_bn9022tb_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T154404_bn9025w1_.bkp
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154451_bn903mln_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154402_bn9022tb_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T154404_bn9025w1_.bkp
RMAN> recover database;
Starting recover at 2015-05-03 16:23:31
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=96 device type=DISK
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=17
channel ORA_DISK_1: reading from backup piece /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154451_bn903mln_.bkp
channel ORA_DISK_1: piece handle=/tmp/2015_05_02/o1_mf_annnn_TAG20150502T154451_bn903mln_.bkp tag=TAG20150502T154451
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_17_874246769.dbf thread=1 sequence=17
unable to find archived log
archived log thread=1 sequence=18
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/03/2015 16:23:34
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 18 and starting SCN of 1166209
RMAN> recover database until sequence 18;
Starting recover at 2015-05-03 16:23:44
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2015-05-03 16:23:44
RMAN> alter database open resetlogs;
database opened
RMAN>
RMAN> exit
Recovery Manager complete.
[oracle@orcltest dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun May 3 16:41:58 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string ORA11G
db_unique_name string ORA11G
global_names boolean FALSE
instance_name string tmp
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string ORA11G
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 250560512 bytes
Fixed Size 2227256 bytes
Variable Size 192938952 bytes
Database Buffers 50331648 bytes
Redo Buffers 5062656 bytes
Database mounted.
Database opened.
SQL> set line 9999 pagesize 9999
SQL> col FILE_NAME format a50
SQL> select file#,name FILE_NAME,status from v$datafile;
FILE# FILE_NAME STATUS
---------- -------------------------------------------------- -------
1 /tmp/2015_05_02/datafile1.dbf SYSTEM
2 /tmp/2015_05_02/datafile2.dbf ONLINE
3 /tmp/2015_05_02/datafile3.dbf ONLINE
4 /tmp/2015_05_02/datafile4.dbf ONLINE
5 /tmp/2015_05_02/datafile5.dbf ONLINE
6 /tmp/2015_05_02/datafile6.dbf ONLINE
6 rows selected.
SQL>
SQL> col FILE_NAME format a50
SQL> select FILE_NAME,FILE_ID,a.TABLESPACE_NAME, b.status ts_status ,BYTES from dba_data_files a,dba_tablespaces b where a.TABLESPACE_NAME=b.TABLESPACE_NAME;
FILE_NAME FILE_ID TABLESPACE_NAME TS_STATUS BYTES
-------------------------------------------------- ---------- ------------------------------ --------- ----------
/tmp/2015_05_02/datafile6.dbf 6 AA ONLINE 5242880
/tmp/2015_05_02/datafile5.dbf 5 EXAMPLE ONLINE 328335360
/tmp/2015_05_02/datafile4.dbf 4 USERS ONLINE 15728640
/tmp/2015_05_02/datafile3.dbf 3 UNDOTBS1 ONLINE 99614720
/tmp/2015_05_02/datafile2.dbf 2 SYSAUX ONLINE 576716800
/tmp/2015_05_02/datafile1.dbf 1 SYSTEM ONLINE 754974720
6 rows selected.
SQL> select count(1) from lhr.test;
COUNT(1)
----------
75204
recover 过程告警日志：
Sun May 03 16:39:56 2015
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6
Completed: alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6
alter database recover if needed
start until cancel using backup controlfile
Media Recovery Start
started logmerger process
Parallel Media Recovery started with 2 slaves
ORA-279 signalled during: alter database recover if needed
start until cancel using backup controlfile
...
alter database recover logfile '/u02/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_17_874246769.dbf'
Media Recovery Log /u02/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_17_874246769.dbf
ORA-279 signalled during: alter database recover logfile '/u02/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_17_874246769.dbf'...
alter database recover cancel
Media Recovery Canceled
Completed: alter database recover cancel
临时文件的处理：
SQL>
SQL> select * from v$tempfile;
no rows selected
SQL> create temporary tablespace TEMP01 tempfile '/tmp/2015_05_02/temp01.dbf' size 100m autoextend on next 10m;
Tablespace created.
SQL> alter database default temporary tablespace temp01;
Database altered.
SQL>
SQL> col name for a100
SQL> select file#,name from v$tempfile;
FILE# NAME
---------- ----------------------------------------------------------------------------------------------------
1 /tmp/2015_05_02/temp01.dbf
SQL>
接下来就是把数据文件命名格式化，执行如下代码：
shutdown immediate;
cp /tmp/2015_05_02/*.dbf /u02/app/oracle/oradata/ORA11G/
cp /tmp/2015_05_02/*.log /u02/app/oracle/oradata/ORA11G/
mv datafile1.dbf AA.dbf
mv datafile5.dbf EXAMPLE01.dbf
mv AA.dbf SYSTEM01.dbf
mv datafile4.dbf USERS01.dbf
mv datafile3.dbf UNDOTBS1.dbf
mv datafile2.dbf SYSAUX01.dbf
mv datafile6.dbf AA.dbf
startup mount
alter database rename file'/tmp/2015_05_02/datafile6.dbf' to '/u02/app/oracle/oradata/ORA11G/AA.dbf';
alter database rename file'/tmp/2015_05_02/datafile5.dbf' to '/u02/app/oracle/oradata/ORA11G/EXAMPLE01.dbf';
alter database rename file'/tmp/2015_05_02/datafile4.dbf' to '/u02/app/oracle/oradata/ORA11G/USERS01.dbf';
alter database rename file'/tmp/2015_05_02/datafile3.dbf' to '/u02/app/oracle/oradata/ORA11G/UNDOTBS1.dbf';
alter database rename file'/tmp/2015_05_02/datafile2.dbf' to '/u02/app/oracle/oradata/ORA11G/SYSAUX01.dbf';
alter database rename file'/tmp/2015_05_02/datafile1.dbf' to '/u02/app/oracle/oradata/ORA11G/SYSTEM01.dbf';
SQL> set line 9999 pagesize 9999
SQL> col FILE_NAME format a50
SQL> select file#,name FILE_NAME,status from v$datafile;
FILE# FILE_NAME STATUS
---------- -------------------------------------------------- -------
1 /u02/app/oracle/oradata/ORA11G/SYSTEM01.dbf SYSTEM
2 /u02/app/oracle/oradata/ORA11G/SYSAUX01.dbf ONLINE
3 /u02/app/oracle/oradata/ORA11G/UNDOTBS1.dbf ONLINE
4 /u02/app/oracle/oradata/ORA11G/USERS01.dbf ONLINE
5 /u02/app/oracle/oradata/ORA11G/EXAMPLE01.dbf ONLINE
6 /u02/app/oracle/oradata/ORA11G/AA.dbf ONLINE
6 rows selected.
SQL> alter database open;
Database altered.
SQL> set line 9999 pagesize 9999
SQL> col FILE_NAME format a50
SQL> select file#,name FILE_NAME,status from v$datafile;
FILE# FILE_NAME STATUS
---------- -------------------------------------------------- -------
1 /u02/app/oracle/oradata/ORA11G/SYSTEM01.dbf SYSTEM
2 /u02/app/oracle/oradata/ORA11G/SYSAUX01.dbf ONLINE
3 /u02/app/oracle/oradata/ORA11G/UNDOTBS1.dbf ONLINE
4 /u02/app/oracle/oradata/ORA11G/USERS01.dbf ONLINE
5 /u02/app/oracle/oradata/ORA11G/EXAMPLE01.dbf ONLINE
6 /u02/app/oracle/oradata/ORA11G/AA.dbf ONLINE
6 rows selected.
SQL> col FILE_NAME format a50
SQL> select FILE_NAME,FILE_ID,a.TABLESPACE_NAME, b.status ts_status ,BYTES from dba_data_files a,dba_tablespaces b where a.TABLESPACE_NAME=b.TABLESPACE_NAME;
FILE_NAME FILE_ID TABLESPACE_NAME TS_STATUS BYTES
-------------------------------------------------- ---------- ------------------------------ --------- ----------
/u02/app/oracle/oradata/ORA11G/AA.dbf 6 AA ONLINE 5242880
/u02/app/oracle/oradata/ORA11G/EXAMPLE01.dbf 5 EXAMPLE ONLINE 328335360
/u02/app/oracle/oradata/ORA11G/USERS01.dbf 4 USERS ONLINE 15728640
/u02/app/oracle/oradata/ORA11G/UNDOTBS1.dbf 3 UNDOTBS1 ONLINE 99614720
/u02/app/oracle/oradata/ORA11G/SYSAUX01.dbf 2 SYSAUX ONLINE 576716800
/u02/app/oracle/oradata/ORA11G/SYSTEM01.dbf 1 SYSTEM ONLINE 754974720
6 rows selected.
SQL>
好了，至此，整个数据库恢复完成，至于修改INSTANCE_NAME，pfile文件，密码文件、tnsnames文件，这些都是基本功了，这里就不赘述了。
1.2 总结
1. 只有备份片段的情况下，我们可以尝试将备份片注册到其它数据库这样来获取dbname和dbid
2. 控制文件备份不存在的情况下，我们可以利用dbms_backup_restore先把1号文件恢复，然后重建控制文件后再注册备份集来获取其它数据文件。
3. 可以使用包dbms_backup_restore 在nomount状态下来尝试获取控制文件的备份
直接通过rman的restore命令来尝试判断备份集中是否含有控制文件的备份
前边的3篇blog中介绍了，在只剩下一些备份片的情况下如何从这些仅剩的备份片中判断是否有控制文件的备份并还原整个数据库，我们介绍了2种① 推荐： 采用dbms_backup_restore.restoreControlfileTo从备份片中来尝试找回控制文件 ② 尝试采用创建临时库来找回控制文件 ③ 采用操作系统命令的strings来判断（私聊） ，今天我们来看看如何直接通过rman的restore命令来尝试判断备份集中是否含有控制文件的备份，这也是一种推荐的方法。
假设，现在我们只有下边的4个备份片段，不知道dbid和db_name，如果db_name不知道的话，可以任意指定一个实例名，然后尝试从rman来启动到nomount，这个时候会启动默认的DUMMY数据库，然后从各个备份集中来尝试恢复控制文件，如果控制文件不在备份集中那么就只能重建控制文件了，参考 http://blog.itpub.net/26736162/viewspace-1621672/
[oracle@orcltest ~]$ cd /tmp/2015_05_02/
[oracle@orcltest 2015_05_02]$ ll
total 1161972
-rw-r----- 1 oracle oinstall 10125312 May 4 14:23 01q63iof_1_1
-rw-r----- 1 oracle oinstall 31271424 May 2 15:49 o1_mf_annnn_TAG20150502T154402_bn9022tb_.bkp
-rw-r----- 1 oracle oinstall 4096 May 2 15:49 o1_mf_annnn_TAG20150502T154451_bn903mln_.bkp
-rw-r----- 1 oracle oinstall 1148452864 May 2 15:49 o1_mf_nnndf_TAG20150502T154404_bn9025w1_.bkp
[oracle@orcltest 2015_05_02]$
[oracle@orcltest ~]$ ORACLE_SID=TEST
[oracle@orcltest ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon May 4 14:27:00 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initTEST.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 158662656 bytes
Fixed Size 2211448 bytes
Variable Size 92275080 bytes
Database Buffers 58720256 bytes
Redo Buffers 5455872 bytes
RMAN> restore controlfile from '/tmp/2015_05_02/o1_mf_annnn_TAG20150502T154402_bn9022tb_.bkp';
Starting restore at 2015-05-04 14:28:03
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=87 device type=DISK
channel ORA_DISK_1: restoring control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/04/2015 14:28:04
ORA-19870: error while restoring backup piece /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154402_bn9022tb_.bkp
ORA-19626: backup set type is archived log - can not be processed by this conversation
RMAN> restore controlfile from '/tmp/2015_05_02/o1_mf_annnn_TAG20150502T154451_bn903mln_.bkp';
Starting restore at 2015-05-04 14:28:20
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/04/2015 14:28:20
ORA-19870: error while restoring backup piece /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154451_bn903mln_.bkp
ORA-19626: backup set type is archived log - can not be processed by this conversation
RMAN> restore controlfile from '/tmp/2015_05_02/o1_mf_nnndf_TAG20150502T154404_bn9025w1_.bkp';
Starting restore at 2015-05-04 14:28:33
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/04/2015 14:28:34
ORA-19697: standby control file not found in backup set
RMAN> restore controlfile from '/tmp/2015_05_02/01q63iof_1_1';
Starting restore at 2015-05-04 14:28:59
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/cntrlTEST.dbf
Finished restore at 2015-05-04 14:29:00
RMAN> alter database mount;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 05/04/2015 14:44:38
ORA-01103: database name 'ORA11G' in control file is not 'DUMMY'
RMAN> exit
Recovery Manager complete.
[oracle@orcltest ~]$
[oracle@orcltest ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon May 4 14:44:21 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DUMMY (not mounted)
RMAN> exit
Recovery Manager complete.
[oracle@orcltest ~]$ ps -ef|grep ora_
oracle 25153 1 0 14:27 ? 00:00:00 ora_pmon_TEST
oracle 25155 1 0 14:27 ? 00:00:00 ora_vktm_TEST
oracle 25159 1 0 14:27 ? 00:00:00 ora_gen0_TEST
oracle 25161 1 0 14:27 ? 00:00:00 ora_diag_TEST
oracle 25163 1 0 14:27 ? 00:00:00 ora_dbrm_TEST
oracle 25165 1 0 14:27 ? 00:00:00 ora_psp0_TEST
oracle 25167 1 0 14:27 ? 00:00:00 ora_dia0_TEST
oracle 25169 1 0 14:27 ? 00:00:00 ora_mman_TEST
oracle 25171 1 0 14:27 ? 00:00:00 ora_dbw0_TEST
oracle 25173 1 0 14:27 ? 00:00:00 ora_lgwr_TEST
oracle 25175 1 0 14:27 ? 00:00:00 ora_ckpt_TEST
oracle 25177 1 0 14:27 ? 00:00:00 ora_smon_TEST
oracle 25179 1 0 14:27 ? 00:00:00 ora_reco_TEST
oracle 25181 1 0 14:27 ? 00:00:00 ora_mmon_TEST
oracle 25183 1 0 14:27 ? 00:00:00 ora_mmnl_TEST
oracle 25255 25216 0 14:46 pts/3 00:00:00 grep ora_
[oracle@orcltest ~]$
尝试的过程中可以看到，能识别出是不是归档文件的备份集，是不是数据文件的备份集，最后mount的时候还可以识别出控制文件中记录的db_name，如果包含控制文件的话，就可以直接来恢复控制文件了，控制文件恢复了其他恢复都很简单了。
About Me
...............................................................................................................................
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-1621581/
● 本文博客园地址：http://www.cnblogs.com/lhrbest
● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群：230161599 微信群：私聊
● 联系我请加QQ好友(646634621)，注明添加缘由
● 于 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
...............................................................................................................................
拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。
