时间：2021-07-01 10:21:17 帮助过：3人阅读
【RMAN】利用备份片还原数据库
群里有同学说把ORACLE_BASE目录删掉了，现在只有备份片和归档文件了，试问如何恢复。坑，，，，咋能这么干，ORACLE_BASE都敢删，，试着恢复了下，并且记录下来，当然软件安装部分就不记录了。
我再说说目前的情况，只有备份片和归档文件，且从文件名称是看不出dbname和dbid的，在这种情况下其实恢复控制文件是很重要的了，但是鬼知道备份片里边是不是有控制文件的备份呢？我们可能碰到的就是这样的情况，只有备份文件，其它的什么都不知道。我们且看这样的情况下如何恢复呢？
演示环境：
OS: RHEL 6.5
DB: 11.2.0.3.0
原库情况：
[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]#
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]$
重点来了：
由于没有控制文件，所以首先只能启动数据库到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从备份片中来尝试找回控制文件 ② 尝试采用创建临时库来找回控制文件 ③ 其它第三方工具来解析备份集
在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