使用DG备库的备份异机恢复一个测试库

刘泽宇

OracleDG使用备库的备份进行异机恢复

一、测试环境

主库：

操作系统：Redhat7.8

数据库版本：12.2.0.1

物理备库：

操作系统：Redhat7.8

数据库版本：12.2.0.1

测试库：

操作系统：Redhat7.6

数据库版本：12.2.0.1（仅安装数据库软件）

二、异机恢复步骤：

1. 查看备库的备份路径

SQL>show parameter db_recovery;

NAME                                       TYPE                 VALUE

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

db_recovery_file_dest                string                /u01/app/oracle/oradata/orcl71/flashback

db_recovery_file_dest_size         big integer         2G

2. 在备库进行完整+归档备份

[oracle@redhat2~]$ rman  target /

RecoveryManager: Release 12.2.0.1.0 - Production on Thu Nov 12 09:31:40 2020

Copyright(c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connectedto target database: ORCL70 (DBID=521283654, not open)

RMAN>run{

2>allocate channel ch1 type disk;

3>allocate channel ch2 type disk;

4>allocate channel ch3 type disk;

5>backup database format '/home/oracle/backupset/data_%U.bak';      #实际备份路径查看备份计划任务

6>backup archivelog all format '/home/oracle/backupset/arh_%U.bak';

7>backup current controlfile format '/home/oracle/backupset/ctl_%U.bak';

8>release channel ch1;

9>release channel ch2;

10>release channel ch3;

11> }

……

Finishedbackup at 12-NOV-20

releasedchannel: ch1

releasedchannel: ch2

releasedchannel: ch3

3.将备份文件和pfile文件拷贝到测试库上

[oracle@redhat2backupset]$ ll

total1088084

-rw-r-----.1 oracle dba   1153024 Nov 12 09:37arh_1gvfau76_1_1.bak

-rw-r-----.1 oracle dba    841216 Nov 12 09:37arh_1hvfau76_1_1.bak

-rw-r-----.1 oracle dba    136704 Nov 12 09:37arh_1ivfau76_1_1.bak

-rw-r-----.1 oracle dba  10665984 Nov 12 09:37ctl_1jvfau77_1_1.bak

-rw-r-----.1 oracle dba 711434240 Nov 12 09:37 data_1evfau5k_1_1.bak

-rw-r-----.1 oracle dba 389955584 Nov 12 09:37 data_1fvfau5k_1_1.bak

-rw-r--r--.1 oracle dba      2164 Nov 12 09:43pfile.ora    #pfile文件根据需要进行拷贝

[oracle@redhat2backupset]$ scp * oracle@192.168.133.124:/home/oracle/backupset

oracle@192.168.133.124'spassword:

arh_1gvfau76_1_1.bak                            100% 1126KB  27.7MB/s  00:00   

arh_1hvfau76_1_1.bak                            100%  822KB 32.3MB/s   00:00   

arh_1ivfau76_1_1.bak                            100%  134KB  9.4MB/s   00:00   

ctl_1jvfau77_1_1.bak                             100%   10MB 45.4MB/s   00:00   

data_1evfau5k_1_1.bak                           100%  678MB 32.3MB/s   00:21   

data_1fvfau5k_1_1.bak                          100%  372MB 28.6MB/s   00:12   

pfile.ora                                        100%2164     1.1MB/s   00:00

4. 将备库的参数文件进行修改作为测试库pfile（若不需要拷贝pfile文件则不用进行这一步）

orcl70.__data_transfer_cache_size=0

orcl70.__db_cache_size=620756992

orcl70.__inmemory_ext_roarea=0

orcl70.__inmemory_ext_rwarea=0

orcl70.__java_pool_size=16777216

orcl70.__large_pool_size=33554432

orcl70.__oracle_base='/u01/app'#ORACLE_BASEset from environment

orcl70.__pga_aggregate_target=637534208

orcl70.__sga_target=956301312

orcl70.__shared_io_pool_size=50331648

orcl70.__shared_pool_size=218103808

orcl70.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/orcl70/adump'

*.audit_trail='db'

*.compatible='12.2.0'

*.control_files='/u01/app/oradata/orcl70/control01.ctl','/u01/app/oradata/orcl70/control02.ctl'#Restore Controlfile

*.db_block_size=8192

*.db_name='orcl70'

*.db_recovery_file_dest_size=2147483648

*.db_recovery_file_dest='/u01/app/oracle/oradata/orcl70/flashback'

*.db_unique_name='ORCL70'

*.diagnostic_dest='/u01/app'

*.dispatchers='(PROTOCOL=TCP)(SERVICE=orcl70XDB)'

*.local_listener='LISTENER_ORCL70'

*.log_archive_dest_1='location=/u01/app/oracle/oradata/orcl70/archivelog'

*.log_archive_dest_state_1='enable'

*.memory_target=1507m

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=300

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

5.根据参数文件创建对应目录，将测试库开启到nomount状态

[oracle@testdb~]$ mkdir -p /u01/app/oracle/admin/orcl70/adump    #需要与源库路径进行对比，若存在同样路径则就不需要创建

[oracle@testdb~]$ mkdir -p /u01/app/oradata/orcl70/

[oracle@testdb~]$ mkdir -p /u01/app/oracle/oradata/orcl70/flashback

[oracle@testdb~]$ mkdir -p /u01/app/oracle/oradata/orcl70/archivelog

SQL>startup nomount pfile='/home/oracle/backupset/pfile.ora';

ORACLEinstance started.

TotalSystem Global Area 1593835520 bytes

FixedSize                  8621184 bytes

VariableSize            1023411072 bytes

DatabaseBuffers          553648128 bytes

RedoBuffers                8155136 bytes

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

Filecreated.

SQL>shutdown immediate

ORA-01507:database not mounted

ORACLEinstance shut down.

SQL>startup nomount

ORACLEinstance started.

TotalSystem Global Area 1593835520 bytes

FixedSize                  8621184 bytes

VariableSize            1023411072 bytes

DatabaseBuffers          553648128 bytes

RedoBuffers                8155136 bytes

6.还原控制文件

RMAN>restore controlfile from '/home/oracle/backupset/ctl_1jvfau77_1_1.bak';  #注意此处还原后为standby控制文件

Startingrestore at 12-NOV-20

usingtarget database control file instead of recovery catalog

allocatedchannel: ORA_DISK_1

channelORA_DISK_1: SID=36 device type=DISK

channelORA_DISK_1: restoring control file

channelORA_DISK_1: restore complete, elapsed time: 00:00:01

outputfile name=/u01/app/oradata/orcl70/control01.ctl

outputfile name=/u01/app/oradata/orcl70/control02.ctl

Finishedrestore at 12-NOV-20

RMAN>alter database mount;   #将测试库启动到mount状态

Statementprocessed

releasedchannel: ORA_DISK_1

7.还原和恢复数据文件

RMAN>restore database;

Finishedrestore at 12-NOV-20

RMAN>recover database;

Finishedrecover at 12-NOV-20

8.将数据库open

RMAN>alter database open resetlogs;

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

RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============

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

RMAN-03002:failure of sql statement command at 11/12/2020 09:59:47

ORA-01666:control file is for a standby database

由于控制文件是standby控制文件，所以不能正常open

9.激活数据库

RMAN>alter database activate standby database;

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

RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============

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

RMAN-03002:failure of sql statement command at 11/12/2020 10:00:55

ORA-00313:open failed for members of log group 11 of thread 1

ORA-00312:online log 11 thread 1: '/u01/app/oracle/oradata/orcl71/redo11_standby.log'

ORA-27037:unable to obtain file status

Linux-x86_64Error: 2: No such file or directory

Additionalinformation: 7

10. 由于日志组路径与源库不同，需要对standby log进行rename     #若测试库日志组路径与测试相同，则不需要进行rename

SQL>show parameter standby

NAME                                 TYPE        VALUE

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

standby_file_management              string      AUTO

SQL>alter system set standby_file_management = MANUAL;       #需要将standby_file_management参数修改为manual才能进行rename

Systemaltered.

SQL>alter database rename file '/u01/app/oracle/oradata/orcl71/redo11_standby.log'to '/u01/app/oracle/oradata/orcl70/redo11_standby.log';

Databasealtered.

SQL>alter database rename file '/u01/app/oracle/oradata/orcl71/redo12_standby.log'to '/u01/app/oracle/oradata/orcl70/redo12_standby.log';

Databasealtered.

SQL>alter database rename file '/u01/app/oracle/oradata/orcl71/redo13_standby.log'to '/u01/app/oracle/oradata/orcl70/redo13_standby.log';

Databasealtered.

SQL>alter database rename file '/u01/app/oracle/oradata/orcl71/redo14_standby.log'to '/u01/app/oracle/oradata/orcl70/redo14_standby.log';

Databasealtered.

SQL>select * from v$logfile;        #若standby日志组出现报错，可尝试进行clear group后重建，也可直接进行drop再从备库拷贝，拷贝后的文件可能会提示不匹配，这时候进行clear重建

GROUP#STATUS  TYPE    MEMBER                                            IS_     CON_ID

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

         3         ONLINE /u01/app/oradata/orcl70/redo03.log                 NO           0

         2         ONLINE /u01/app/oradata/orcl70/redo02.log                 NO           0

         1         ONLINE /u01/app/oradata/orcl70/redo01.log                 NO           0

        11         STANDBY/u01/app/oracle/oradata/orcl70/redo11_standby.log  NO          0

        12         STANDBY/u01/app/oracle/oradata/orcl70/redo12_standby.log  NO          0

        13         STANDBY /u01/app/oracle/oradata/orcl70/redo13_standby.log  NO          0

        14         STANDBY/u01/app/oracle/oradata/orcl70/redo14_standby.log  NO          0

7 rowsselected.

SQL>alter system set standby_file_management = AUTO;

Systemaltered.

11.激活数据库并启动到open，处于读写状态    #先激活数据库再打开

SQL>alter database activate standby database;

Databasealtered.

SQL>select open_mode , database_role, flashback_on from v$database;

OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON

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

MOUNTED                PRIMARY                YES

SQL>alter database open;

Databasealtered.

SQL>select open_mode , database_role, flashback_on from v$database;

OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON

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

READWRITE             PRIMARY               YES