【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