关于Stanby全库rman备份文件恢复到异机实验

最新推荐文章于 2022-11-01 22:45:40 发布

转载最新推荐文章于 2022-11-01 22:45:40 发布 · 70 阅读

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/Richardzhu/articles/2795896.html

本文详细介绍了一次基于OracleDG架构的全库恢复实验过程，包括在Standby数据库上进行Rman备份，以及在Primary数据库的数据文件、控制文件和归档日志丢失的情况下，如何通过Standby的备份文件恢复Primary数据库，并最终将新的Standby数据库切换为主库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Standby全库恢复Primary数据库

一、实验目的

此实验是建立在Oracle DG架构基础之上。Rman备份Standby数据库。假设Primary数据库上的controlfiles、datafiles、archivelogs丢失，恢复Primary数据库。

二、实验步骤

1.在表test中加插入一行数据。

 1 SQL> select * from test;
 2 
 3     ID
 4   ----------
 5      1
 6      2
 7      3
 8      4
 9      5
10     SQL> insert into test values (6);
11 
12   1 row created.
13 
14   SQL> commit;
15 
16   Commit complete.
17 
18   SQL> select * from test;
19 
20     ID
21   ----------
22      1
23      2
24      3
25      4
26      5
27      6
28 
29   6 rows selected.
30 
31   SQL> shutdown abort;
32   ORACLE instance shut down.

2.直接shutdown abort，在人为去修改数据文件，但保留redolog，备份归档目录

1 [oracle@node1 oracle]$ mv oradata oradata.bak
2   [oracle@node1 oracle]$ mkdir -p oradata/YFT
3   [oracle@node1 oracle]$ cd oradata.bak/YFT/
4   [oracle@node1 YFT]$ cp redo01.log redo02.log redo03.log /u01/app/oracle/oradata/YFT/
5   [oracle@node1 u01]$ mv arch arch.bak

3.从Standby中拷贝Rman备份文件和归档文件

1 [oracle@node2 u01]$ scp -r arch node1:/u01        ----归档目录
2   [oracle@node2 u01]$ cd /u01/app/oracle
3   [oracle@node2 oracle]$ scp -r flash_recovery_area node1:/u01/app/oracle
4   oracle@node1's password: 
5   o1_mf_nnndf_TAG20121025T094057_88k659py_.bkp                                100%  510MB  16.5MB/s   00:31    
6   o1_mf_ncnnf_TAG20121025T094057_88k66f7p_.bkp                                100% 6944KB   6.8MB/s   00:00

4.在Standby上进入Rman查看备份信息和DBID

1 [oracle@node2 oracle]$ rman target /
2 
3   Recovery Manager: Release 10.2.0.1.0 - Production on Thu Oct 25 14:43:37 2012
4 
5   Copyright (c) 1982, 2005, Oracle.  All rights reserved.
6 
7   connected to target database: YFT (DBID=2950194747, not open)

 1 查看备份信息：
 2   RMAN> list backup;
 3   BS Key  Type LV Size       Device Type Elapsed Time Completion Time
 4   ------- ---- -- ---------- ----------- ------------ ---------------
 5   4       Full    6.77M      DISK        00:00:01     25-OCT-12      
 6         BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20121025T094057
 7         Piece Name: /u01/app/oracle/flash_recovery_area/YFTB/backupset/2012_10_25/o1_mf_ncnnf_TAG20121025T094057_88k66f7p_.bkp
 8   Standby Control File Included: Ckp SCN: 489513       Ckp time: 25-OCT-12
 9   
10 ---------主要是看备份的控制文件信息

5.在Primary上进入Rman

1  [oracle@node1 oracle]$ rman target /

6.设置DBID

1  RMAN> set dbid 2950194747
2 
3   executing command: SET DBID

7.启动Primary到nomount状态

1 RMAN> startup nomount;

8.恢复controlfile

 1 RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area/YFTB/backupset/2012_10_25/o1_mf_ncnnf_TAG20121025T094057_88k66f7p_.bkp';
 2 
 3   Starting restore at 25-OCT-12
 4   using target database control file instead of recovery catalog
 5   allocated channel: ORA_DISK_1
 6   channel ORA_DISK_1: sid=156 devtype=DISK
 7 
 8   channel ORA_DISK_1: restoring control file
 9   channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
10   output filename=/u01/app/oracle/oradata/YFT/control01.ctl
11   output filename=/u01/app/oracle/oradata/YFT/control02.ctl
12   output filename=/u01/app/oracle/oradata/YFT/control03.ctl
13   Finished restore at 25-OCT-12

9.使用新的controlfile启动数据库

1 RMAN> alter database mount;
2 
3   database mounted
4   released channel: ORA_DISK_1

10.转储数据库

1 RMAN> restore database;

11.恢复数据库是报错

 1 RMAN> recover database;
 2 
 3   Starting recover at 25-OCT-12
 4   using channel ORA_DISK_1
 5 
 6   starting media recovery
 7 
 8   archive log filename=/u01/arch/1_12_797522236.arc thread=1 sequence=12
 9   archive log filename=/u01/arch/1_13_797522236.arc thread=1 sequence=13
10   archive log filename=/u01/arch/1_14_797522236.arc thread=1 sequence=14
11   archive log filename=/u01/arch/1_15_797522236.arc thread=1 sequence=15
12   archive log filename=/u01/arch/1_16_797522236.arc thread=1 sequence=16
13   archive log filename=/u01/arch/1_17_797522236.arc thread=1 sequence=17
14   archive log filename=/u01/arch/1_18_797522236.arc thread=1 sequence=18
15   archive log filename=/u01/arch/1_19_797522236.arc thread=1 sequence=19
16   archive log filename=/u01/arch/1_20_797522236.arc thread=1 sequence=20
17   archive log filename=/u01/arch/1_20_797522236.arc thread=1 sequence=21
18   RMAN-00571: ===========================================================
19   RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
20   RMAN-00571: ===========================================================
21   RMAN-03002: failure of recover command at 10/25/2012 14:51:14
22   RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/arch/1_20_797522236.arc'
23   ORA-00310: archived log contains sequence 20; sequence 21 required
24   ORA-00334: archived log: '/u01/arch/1_20_797522236.arc'
25 
26 ----------这是因为使用的standby的备份文件恢复的，恢复出来的库还是standby库的样子，下面就将standby库恢复成主库

三、将新的standby数据库切换成主库

 1 [oracle@node1 oracle]$ sqlplus /nolog
 2 
 3   SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 25 14:53:00 2012
 4 
 5   Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 6 
 7   SQL> conn /as sysdba
 8   Connected.
 9   SQL> recover standby database;
10   ORA-00279: change 576159 generated at 10/25/2012 14:32:05 needed for thread 1
11   ORA-00289: suggestion : /u01/arch/1_21_797522236.arc
12   ORA-00280: change 576159 for thread 1 is in sequence #21
13 
14 
15   Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
16 
17 -------------因为21的归档日志还没出来，所以需要redolog恢复，因此需要去Primary上的alert日志上查找：
18   Thu Oct 25 14:32:05 2012
19   Thread 1 advanced to log sequence 21
20   Current log# 3 seq# 21 mem# 0: /u01/app/oracle/oradata/YFT/redo03.log
21   Thu Oct 25 14:32:06 2012
22   LNS: Standby redo logfile selected for thread 1 sequence 21 for destination LOG_ARCHIVE_DEST_2
23   Thu Oct 25 14:33:58 2012
24   Shutting down instance (abort)
25   License high water mark = 3
26   Instance terminated by USER, pid = 5438
27 
28 -------------从上面就可以看出需要redo03.log    或者在备库中查找 --------------------------------------------------
29   SQL> select group#,status from v$log;
30 
31     GROUP#   STATUS
32   ---------- ----------------
33      1          CLEARING
34      3          CLEARING
35      2          CLEARING_CURRENT
36 --------------------------------------------从上面的备库中查找到redo03.log是当时的current     
37      
38      
39   SQL> recover standby database;
40   ORA-00279: change 576159 generated at 10/25/2012 14:32:05 needed for thread 1
41   ORA-00289: suggestion : /u01/arch/1_21_797522236.arc
42   ORA-00280: change 576159 for thread 1 is in sequence #21
43 
44 
45   Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
46   /u01/app/oracle/oradata/YFT/redo03.log
47   Log applied.
48   Media recovery complete.
49 
50 -------------这样就把redolog里面的信息给恢复出来了。

2.到这里话需要去激活standby数据库，有两种方法。

首先，使用比较温柔的方法：

 1  SQL> alter database commit to switchover to primary;
 2   alter database commit to switchover to primary
 3   *
 4   ERROR at line 1:
 5   ORA-16139: media recovery required
 6 
 7 
 8   SQL> recover managed standby database finish;
 9   ORA-00283: recovery session canceled due to errors
10   ORA-00314: log 4 of thread 1, expected sequence# 12 doesn't match 0
11   ORA-00312: online log 4 thread 1:
12   '/u01/app/oracle/oradata/YFT/standbyredo04.rdo'
13 
14 -----------此处将standbyredo04.rdo拷贝过来以后还是报错，就只能使用暴力的方法。
15 
16   SQL> alter database activate standby database;
17 
18   Database altered.
19 
20   SQL> alter database open;
21 
22   Database altered.
23 
24   SQL> select * from test;
25 
26     ID
27   ----------
28      1
29      2
30      3
31      4
32      5
33      6
34 
35   6 rows selected.
36 
37 --------到此Primary恢复完成，而且数据没有丢失。

转载于:https://www.cnblogs.com/Richardzhu/articles/2795896.html