当前位置:Gxlcms > 数据库问题 > 【RMAN】利用备份片还原数据库(上)

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

时间:2021-07-01 10:21:17 帮助过:3人阅读

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

 

群里有同学说把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

 

人气教程排行