ORACLE11G 将dataguard的rman备份恢复到测试环境的单机oracle中的详细过程


 

1,从生产库上copy好全备份文件

1.1,查看参数文件信息

RMAN> list backup of spfile;

从一大推list信息找出最近的备份信息

/pddata2/oracle/backup/data/ctl_auto/c-3391761643-20150820-01

 

 

1.2查看控制文件信息:

RMAN> list backup of controlfile;

找出里面的控制文件

/pddata2/oracle/backup/data/ctl_auto/c-3391761643-20150820-01

 

 

1.3 查看数据库信息:

RMAN> list backup of database;

1.4 查看归档日志信息:

RMAN> list backup of archivelog all;

 

BS Key Type LV Size       Device TypeElapsed Time Completion Time

------- ---- -- ---------- ----------------------- ---------------

4110   Full    18.36M     DISK       00:00:01     20-AUG-15     

       BP Key: 4110   Status: AVAILABLE  Compressed: NO  Tag: TAG20150820T032017

       Piece Name:/pddata2/oracle/backup/data/ctl_auto/c-3391761643-20150820-01

 Control File Included: Ckp SCN: 11412370967   Ckp time: 20-AUG-15

 

将参数文件控制文件copy到测试环境到测试环境/data/impdp/

cd /pddata2/oracle/backup/data/ctl_auto/

scp c-3391761643-20150820-01c-3391761643-20150820-01 192.168.180.60:/data/impdp/

 

 

2、开始恢复参数文件控制文件:

SQL> select dbid from v$database;        

2.1 设置DBID

注意:在rman下即使没有参数文件,默认也会启动一个DUMMY实例,以便能够恢复参数文件。

set dbid 3391761643

 

2.2 恢复spfile文件

startup到open状态,先查看spfile文件位置:

SQL> show parameter spfile;

 

/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/spfilepowerdes.ora

 

shutdown后,再startup 到 nomount状态

去生产环境查找

RMAN> show all;

......

CONFIGURE SNAPSHOT CONTROLFILE NAME TO'/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_powerdes.f'; # default

 

找到之后copy到测试环境:

scp/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_powerdes.f192.168.180.60:/data/impdp/

 

 

开始进行恢复

restore spfile to'/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/spfilepowerdes.ora' from  '/data/impdp/snapcf_powerdes.f';

 

 

startup nomount再恢复

 

恢复报错如下:

 

RMAN> restore spfile to'/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/spfilepowerdes.ora' from  '/data/impdp/c-3391761643-20150820-01';                                                            

RMAN> restore spfile to'/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/spfilepowerdes.ora' from  '/data/impdp/c-3391761643-20150820-01';                                                      

 

Starting restore at 20-AUG-15

using channel ORA_DISK_1

 

channel ORA_DISK_1: restoring spfile fromAUTOBACKUP /data/impdp/c-3391761643-20150820-01                                                                                                     

RMAN-00571:===========================================================

RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============

RMAN-00571:===========================================================

RMAN-03002: failure of restore command at08/20/2015 18:25:14

ORA-32011: cannot restore SPFILE tolocation already being used by the instance

 

RMAN> 

 

所以从pd线上重新拉一个参数文件出来,copy到测试环境

SQL> create pfile='/oracle/pfile01.ora'from spfile;                                                                                                                                           

                                                                                                                                                                                              

File created.

 

SQL> 

 

然后根据复制来的参数文件再在测试库上建立spfile

create spfile frompfile='/data/pfile01.ora';

SQL> create spfile frompfile='/data/impdp/pfile01.ora';                                                                                                                                      

 

File created.

 

SQL>   

然后将测试库启动到nomount

 

SQL> startup nomount                                                                                                                                                                           

ORA-00845: MEMORY_TARGET not supported onthis system                                                                                                                                         

SQL>

 

看到报内存错误了,然后修改/etc/fstab文件设置好内存标示配置

 vi/etc/fstab

tmpfs                   /dev/shm                tmpfs   defaults,size=11G        0 0

执行生效

mount -t tmpfs shmfs -o size=11g /dev/shm

 

 

 

SQL> startup mount;                                                                                                                                                                           

ORA-01078: failure in processing systemparameters

LRM-00109: could not open parameter file'/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initpowerdes.ora'

SQL>   

 

restore

 

2.3 在测试环境恢复控制文件:

restore controlfile to'/data/oracle/powerdes/control01.ctl' from'/data/impdp/c-3391761643-20150820-01';

RMAN> restore controlfile to'/data/oracle/powerdes/control01.ctl' from'/data/impdp/c-3391761643-20150820-01'       

 

restore controlfile to'/home/oradata/powerdes/control01.ctl' from'/data/impdp/c-3391761643-20150820-01';

 

                                                                         

2> ;                                                                                                                                                                                          

 

之后需要启动到mount才能进行restore和recover操作,但是启动失败

RMAN> alter database mount                                                                                                                                                                    

2> ;                                                                                                                                                                                          

 

RMAN-00571:===========================================================

RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============

RMAN-00571:===========================================================

RMAN-03002: failure of alter db command at08/21/2015 11:12:13

ORA-00205: error in identifying controlfile, check alert log for more info

 

RMAN>       

 

看到是因为控制文件不识别,去看下alert日志信息,

[root@testoracle1 /]# tail -f/oracle/app/oracle/diag/rdbms/pdunq/powerdes/trace/alert_powerdes.log

Checker run found 1 new persistent datafailures

Fri Aug 21 11:13:51 2015

alter database mount

Fri Aug 21 11:13:51 2015

ORA-00210: cannot open the specifiedcontrol file

ORA-00202: control file:'/home/oradata/powerdes/control01.ctl'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file ordirectory

Additional information: 3

ORA-205 signalled during: alter databasemount...

 

从alert日志可以看出控制文件已经变成了生产环境的路径了,原因是spfile是从生产环境copy出来的,生产环境的spfile里面记录的控制文件路径和测试环境不同,所以这里为了快速恢复,要重新指定和生产环境一样路径的,重新生成新路径的控制文件。

控制文件

 

RMAN> restore controlfile to'/home/oradata/powerdes/control01.ctl' from'/data/impdp/c-3391761643-20150820-01';                                                                               

 

Starting restore at 21-AUG-15

using channel ORA_DISK_1

 

channel ORA_DISK_1: restoring controlfile                                                                                                                                                    

channel ORA_DISK_1: restore complete,elapsed time: 00:00:01                                                                                                                                  

Finished restore at 21-AUG-15

 

OK,看到控制文件restore成功了,接下来直接将数据库状态改成mount

 

RMAN> alter database mount;                                                                                                                                                                  

 

database mounted                                                                                                                                                                              

released channel: ORA_DISK_1

                                                                                                                                                                                               

RMAN> 

 

                                                                                                                                                                          

然后就可以开始restore整个库                                                                                                                                                                                              

 

 

2.4, 在新控制文件中注册数据文件备份和归档备份

要将db状态改成mount才行

catalog start with'/data/impdp/2015-08-20/';

 

RMAN> alter database mount                                                                                                                                                                    

2> ;                                                                                                                                                                                          

 

using target database control file insteadof recovery catalog

database mounted                                                                                                                                                                              

                                                                                                                                                                                              

RMAN> catalog start with'/data/impdp/2015-08-20/';                                                                                                                                           

 

Starting implicit crosscheck backup at20-AUG-15

allocated channel: ORA_DISK_1                                                                                                                                                                 

channel ORA_DISK_1: SID=129 devicetype=DISK

Crosschecked 98 objects                                                                                                                                                                       

Finished implicit crosscheck backup at20-AUG-15

 

Starting implicit crosscheck copy at20-AUG-15

using channel ORA_DISK_1

Finished implicit crosscheck copy at20-AUG-15

 

searching for all files in the recoveryarea

cataloging files...

no files cataloged                                                                                                                                                                             

 

searching for all files that match thepattern /data/impdp/2015-08-20/

 

List of Files Unknown to the Database

=====================================

File Name:/data/impdp/2015-08-20/arch_POWERDES_20150820_4420.bak

File Name:/data/impdp/2015-08-20/rman_backup.log

File Name:/data/impdp/2015-08-20/arch_POWERDES_20150820_4418.bak

File Name:/data/impdp/2015-08-20/full_POWERDES_20150820_4419.bak

 

Do you really want to catalog the abovefiles (enter YES or NO)? YES                                                                                                                          

cataloging files...

cataloging done                                                                                                                                                                               

 

List of Cataloged Files

=======================

File Name:/data/impdp/2015-08-20/arch_POWERDES_20150820_4420.bak

File Name:/data/impdp/2015-08-20/arch_POWERDES_20150820_4418.bak

File Name:/data/impdp/2015-08-20/full_POWERDES_20150820_4419.bak

 

List of Files Which Where Not Cataloged

=======================================

File Name:/data/impdp/2015-08-20/rman_backup.log

 RMAN-07517: Reason: The file header is corrupted

                                                                                                                                                                                              

RMAN>  

 

2.5,开始恢复整个库

因为前面恢复了整个spfile已经controlfile,所以接下来恢复所有库的话,就不用带参数,直接恢复restore database就可以 ;

RMAN> restore database;

RMAN> restore database;                                                                                                                                                                       

 

Starting restore at 20-AUG-15

using channel ORA_DISK_1

 

channel ORA_DISK_1: starting datafilebackup set restore                                                                                                                                      

channel ORA_DISK_1: specifying datafile(s)to restore from backup set

channel ORA_DISK_1: restoring datafile00001 to /home/oradata/powerdes/system01.dbf

channel ORA_DISK_1: restoring datafile00002 to /home/oradata/powerdes/sysaux01.dbf

channel ORA_DISK_1: restoring datafile00003 to /home/oradata/powerdes/undotbs01.dbf

channel ORA_DISK_1: restoring datafile00004 to /home/oradata/powerdes/users01.dbf

channel ORA_DISK_1: restoring datafile00005 to /home/oradata/powerdes/powerdesk01.dbf

channel ORA_DISK_1: restoring datafile00006 to /home/oradata/powerdes/plas01.dbf

channel ORA_DISK_1: restoring datafile00007 to /home/oradata/powerdes/pl01.dbf

channel ORA_DISK_1: restoring datafile00008 to /home/oradata/powerdes/help01.dbf

channel ORA_DISK_1: restoring datafile00009 to /home/oradata/powerdes/adobelc01.dbf

channel ORA_DISK_1: restoring datafile00010 to /home/oradata/powerdes/sms01.dbf

channel ORA_DISK_1: restoring datafile00011 to /home/oradata/powerdes/plcrm01.dbf

channel ORA_DISK_1: restoring datafile00012 to /home/oradata/powerdes/powerdesk02.dbf

channel ORA_DISK_1: reading from backuppiece /data/impdp/2015-08-20/full_POWERDES_20150820_4419.bak

channel ORA_DISK_1: piecehandle=/data/impdp/2015-08-20/full_POWERDES_20150820_4419.baktag=TAG20150820T030008                                                                                

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete,elapsed time: 00:17:35

Finished restore at 20-AUG-15

 

RMAN>

 

2.6 然后recover修复数据库

RMAN> recover database;                                                                                                                                                                       

 

Starting recover at 20-AUG-15

using channel ORA_DISK_1

                                                                                                                                                                                              

starting media recovery

                                                                                                                                                                                              

channel ORA_DISK_1: starting archived logrestore to default destination                                                                                                                      

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=36277

channel ORA_DISK_1: reading from backuppiece /data/impdp/2015-08-20/arch_POWERDES_20150820_4420.bak

channel ORA_DISK_1: piecehandle=/data/impdp/2015-08-20/arch_POWERDES_20150820_4420.baktag=TAG20150820T032015                                                                                

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete,elapsed time: 00:00:01

archived log filename=/oracle/app/oracle/flash_recovery_area/POWERDES/archivelog/2015_08_20/o1_mf_1_36277_bxcjyzbg_.arcthread=1 sequence=36277                                               

channel default: deleting archivedlog(s)                                                                                                                                                     

archived log file name=/oracle/app/oracle/flash_recovery_area/POWERDES/archivelog/2015_08_20/o1_mf_1_36277_bxcjyzbg_.arcRECID=71395 STAMP=888264671

unable to find archived log                                                                                                                                                                   

archived log thread=1 sequence=36278

RMAN-00571:===========================================================

RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at08/20/2015 20:11:13

RMAN-06054: media recovery requestingunknown archived log for thread 1 with sequence 36278 and starting SCN of11412370952

 

RMAN> 

 

后台alet日志报错信息为:

Fri Aug 21 11:47:07 2015

alter database recover datafile list clear

Completed: alter database recover datafilelist clear

alter database recover datafile list

 1 ,2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12

Completed: alter database recover datafilelist

 1 ,2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12

alter database recover if needed

 start until cancel using backup controlfile

Media Recovery Start

 started logmerger process

Parallel Media Recovery started with 4slaves

ORA-279 signalled during: alter databaserecover if needed

 start until cancel using backup controlfile

...

alter database recover logfile'/oracle/app/oracle/flash_recovery_area/archivelog1_36277_821708334.dbf'

Media Recovery Log/oracle/app/oracle/flash_recovery_area/archivelog1_36277_821708334.dbf

ORA-279 signalled during: alter databaserecover logfile'/oracle/app/oracle/flash_recovery_area/archivelog1_36277_821708334.dbf'...

alter database recover cancel

Media Recovery Canceled

Completed: alter database recover cancel

Fri Aug 21 11:47:09 2015

Checker run found 1 new persistent datafailures

 

可见,出先此错误的原因是恢复需要的归档日志记录在控制文件或恢复目录中找不到。解决方法分两种情况:

1.如果相关的日志存在且可用的话,就将此日志记录添加到控制文件或恢复目录中。

2.如果相关的日志已经被删除了或不可用了,那么就按照错误的提示scn将数据库恢复到此scn,这里是11412370952。也就是说此时数据库只能进行不完全恢复了,在打开数据库时得使用resetlogs打开。

 

recover database until scn 11412370952;

 

 

RMAN> recover database until scn11412370952;                                                                                                                                                 

 

Starting recover at 20-AUG-15

using channel ORA_DISK_1

                                                                                                                                                                                              

starting media recovery

media recovery complete, elapsed time:00:00:00                                                                                                                                               

 

Finished recover at 20-AUG-15

 

RMAN> 

 

然后打开数据库

RMAN> alter database openresetlogs;                                                                                                                                                          

 

database opened                                                                                                                                                                                

                                                                                                                                                                                              

RMAN>   

 

 

3,一些调试过程中的意外报错

然后打开报错

RMAN> alter database open resetlogs;                                                                                                                                                         

 

RMAN-00571:===========================================================                                                                                                                       

RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============

RMAN-00571:===========================================================

RMAN-03002: failure of alter db command at08/20/2015 20:31:07;

ORA-03113: end-of-file on communicationchannel

Process ID: 30584

Session ID: 192 Serial number: 19

RMAN-00571:===========================================================

RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============

RMAN-00571:===========================================================

ORA-03114: not connected to ORACLE

RMAN-00571:===========================================================

RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============

RMAN-00571:===========================================================

RMAN-03002: failure of alter db command at08/20/2015 20:31:07

ORA-03113: end-of-file on communicationchannel

Process ID: 30584

Session ID: 192 Serial number: 19

[oracle@testoracle1 dbs]$

 

重新进去打开

RMAN> alter database open resetlogs;                                                                                                                                                          

 

using target database control file insteadof recovery catalog

RMAN-00571:===========================================================

RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============

RMAN-00571:===========================================================

RMAN-03002: failure of alter db command at08/20/2015 20:35:44

RMAN-06403: could not obtain a fullyauthorized session

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does notexist

Linux-x86_64 Error: 2: No such file ordirectory

 

RMAN> 

 

 

尝试去sqlplus模式下启动

SQL> startup

ORACLE instance started.

 

Total System Global Area 5010685952 bytes

Fixed Size              2212936 bytes

Variable Size              2751466424 bytes

Database Buffers    2214592512 bytes

Redo Buffers                42414080 bytes

Database mounted.

ORA-03113: end-of-file on communicationchannel

Process ID: 10504

Session ID: 191 Serial number: 3

 

 

SQL>

 

这里打不开的原因是,参数文件恢复失效后,跳过参数文件恢复这一步骤,然后进行控制文件恢复后restore库recover库引发的问题。解决方案:就是重新恢复参数文件后再进行下述步骤就完全OK。

阅读更多

扫码向博主提问

黄杉

博客专家

非学,无以致疑;非问,无以广识
  • 擅长领域:
  • MySQL Hbas
  • Hbase
  • Tidb
  • cdb
  • 数据拆分
去开通我的Chat快问

没有更多推荐了,返回首页