【RMAN】利用备份片还原数据库

本文涉及的产品
云原生数据仓库AnalyticDB MySQL版,基础版 8ACU 100GB 1个月
日志服务 SLS,月写入数据量 50GB 1个月
简介: 【RMAN】利用备份片还原数据库 BLOG文档结构图       群里有同学说把ORACLE_BASE目录删掉了,现在只有备份片和归档文件了,试问如何恢复。

RMAN】利用备份片还原数据库

BLOG文档结构图

 

wps6252.tmp 

 

群里有同学说把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下测试也可以获取到这些信息)这里dbnamedbid都不一样所以不能注册在该数据库下,将备份片的信息加入到控制文件的时候报错,原因在于原有数据库和临时数据库的DBID不同,那么我们是否可以尝试修改临时数据库的DBID,使它与原有数据库DBID保持一致呢,试试呗。

 

1、 修改dbnamedbid

 

首先启动临时库到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和控制文件,也可以只还原数据文件,这个根据自己的需要而定,这里我们测试的话就从spfilecontrol filedatafile都还原吧。

 

二、 还原操作 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