1.数据库环境介绍

1)主库(源数据库): Oracle 11G R2 IP:192.168.4.105 HOSTNAME:oadb SID:oa ORACLE_BASE:/u01/app/oracle ORACLE_HOME:/u01/app/oracle/product/11.2.0/dbhome_1

2)备库(目标数据库): Oracle 11g R2 IP:192.168.4.106 HOSTNAME:oadbstandby SID:oa ORACLE_BASE:/u01/app/oracle ORACLE_HOME:/u01/app/oracle/product/11.2.0/dbhome_1

2.配置db_unique_name

1)配置主库db_unique_name SQL> show parameter db_unique_name; NAME TYPE VALUE


db_unique_name string oa 注:主库db_unique_name这里不作修改,保持生产环境不受影响 2)配置备库db_unique_name SQL> show parameter db_unique_name; NAME TYPE VALUE


db_unique_name string oa

SQL> alter system set db_unique_name=standbydb scope=spfile; System altered 注:配置db_unique_name,需要重启数据库才能生效

3.主库配置 1)配置网络服务名 [oracle@oadb ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin [oracle@oadb admin]$ vi tnsnames.ora OA = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.105)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oa) ) )

STANDBYDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.106)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standbydb) ) ) 2)配置监听,添加静态注册 [root@oadb admin]# pwd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin [root@oadb admin]# vi listener.ora listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_DESC = (GLOBAL_DBNAME = oa) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = oa) )

LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = oadb)(PORT = 1521)) ) )

ADR_BASE_LISTENER = /u01/app/oracle 3)将主库修改为forcelogging模式 SQL> select name,open_mode,log_mode,force_logging from gv$database;

NAME OPEN_MODE LOG_MODE FOR


OA READ WRITE ARCHIVELOG NO

SQL> alter database force logging;

Database altered.

SQL> select name,open_mode,log_mode,force_logging from gv$database;

NAME OPEN_MODE LOG_MODE FOR


OA READ WRITE ARCHIVELOG YES 4)设置参数 a)设置所有启用的数据库名 SQL> show parameter log_archive_config;

NAME TYPE VALUE


log_archive_config string SQL> alter system set log_archive_config='dg_config=(oa,standbydb)';

System altered.

SQL> show parameter log_archive_config;

NAME TYPE VALUE


log_archive_config string dg_config=(oa,standbydb) 注:dg_config为主备库的db_unique_name b)开启归档模式 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started.

Total System Global Area 409194496 bytes Fixed Size 2253744 bytes Variable Size 310381648 bytes Database Buffers 92274688 bytes Redo Buffers 4284416 bytes Database mounted. SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered. c)设置归档日志的路径(本地和网络) SQL> show parameter log_archive_dest_1;

NAME TYPE VALUE


log_archive_dest_1 string log_archive_dest_10 string log_archive_dest_11 string log_archive_dest_12 string log_archive_dest_13 string log_archive_dest_14 string log_archive_dest_15 string log_archive_dest_16 string log_archive_dest_17 string log_archive_dest_18 string log_archive_dest_19 string SQL> show parameter log_archive_dest_2;

NAME TYPE VALUE


log_archive_dest_2 string log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string log_archive_dest_24 string log_archive_dest_25 string log_archive_dest_26 string log_archive_dest_27 string log_archive_dest_28 string log_archive_dest_29 string SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/OA/archivelog/ optional';

System altered.

SQL> alter system set log_archive_dest_2='service=standbydb lgwr sync affirm valid_for=(online_logfile,primary_role) db_unique_name=standbydb';

System altered.

SQL> show parameter log_archive_dest_1;

NAME TYPE VALUE


log_archive_dest_1 string LOCATION=/u01/app/oracle/fast_ recovery_area/OA/archivelog/ o ptional log_archive_dest_10 string log_archive_dest_11 string log_archive_dest_12 string log_archive_dest_13 string log_archive_dest_14 string log_archive_dest_15 string log_archive_dest_16 string log_archive_dest_17 string

NAME TYPE VALUE


log_archive_dest_18 string log_archive_dest_19 string SQL> show parameter log_archive_dest_2;

NAME TYPE VALUE


log_archive_dest_2 string service=standbydb lgwr sync af firm valid_for=(online_logfile ,primary_role) db_unique_name= standbydb log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string log_archive_dest_24 string log_archive_dest_25 string log_archive_dest_26 string

NAME TYPE VALUE


log_archive_dest_27 string log_archive_dest_28 string log_archive_dest_29 string 注:service值为备用数据库的TNSNAMES描述符 d)设置备库文件自动管理,可以在主库新建数据文件的时候自动新建备库 SQL> show parameter standby_file_management;

NAME TYPE VALUE


standby_file_management string MANUAL SQL> alter system set standby_file_management='AUTO';

System altered.

SQL> show parameter standby_file_management;

NAME TYPE VALUE


standby_file_management string AUTO e)设置FAL_SERVER为备库Oracle Net service name, SQL> show parameter fal_server;

NAME TYPE VALUE


fal_server string SQL> alter system set fal_server='standbydb';

System altered.

SQL> show parameter fal_server;

NAME TYPE VALUE


fal_server string standbydb f)创建standby redolog SQL> alter database add standby logfile group 11 ('/u01/app/oracle/oradata/oa/standby11.log') size 50m;

Database altered.

SQL> alter database add standby logfile group 12 ('/u01/app/oracle/oradata/oa/standby12.log') size 50m;

Database altered.

SQL> alter database add standby logfile group 13 ('/u01/app/oracle/oradata/oa/standby13.log') size 50m;

Database altered.

SQL> alter database add standby logfile group 14 ('/u01/app/oracle/oradata/oa/standby14.log') size 50m;

Database altered.

注1:由于备库后续会使用主库RMAN还原,这里无需在备库存创建,等备库使用RMAN还原后,再创建standby redolog 注2:建立standby如要注意以下几点: a)standby log files的大小和redo log files一样。 查询redo log files文件大小(默认50M,3个):select group#,bytes/1024/1024 as M from v$log; b)一般而言, standbyredo 日志文件组数要比 primary 数据库的 online redo 日志文件组数至少多一个。 有一个推荐的公式可以做参考:(每线程的日志组数+1)*最大线程数 假设现在节点是1个,则=(3+1)*1=4 如果是双节点 则=(3+1)*2=8 c)不建议组号group#紧挨着redo,因为后续redo有可能调整

c)不建议组号group#紧挨着redo,因为后续redo有可能调整 4.备库配置 1)配置网络服务名 [oracle@oadbstandby ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin [oracle@oadbstandby admin]$ vi tnsnames.ora OA = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.105)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oa) ) )

STANDBYDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.106)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standbydb) ) ) 2)配置监听,添加静态注册 [root@oadbstandby admin]# pwd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin [root@oadbstandby admin]# vi listener.ora listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_DESC = (GLOBAL_DBNAME = oa) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = oa) )

LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = oadbstandby)(PORT = 1521)) ) )

ADR_BASE_LISTENER = /u01/app/oracle 3)将备库修改为forcelogging模式 SQL> select name,open_mode,log_mode,force_logging from gv$database;

NAME OPEN_MODE LOG_MODE FOR


OA READ WRITE ARCHIVELOG NO

SQL> alter database force logging;

Database altered.

SQL> select name,open_mode,log_mode,force_logging from gv$database;

NAME OPEN_MODE LOG_MODE FOR


OA READ WRITE ARCHIVELOG YES 4)设置参数 a)开启归档模式 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started.

Total System Global Area 409194496 bytes Fixed Size 2253744 bytes Variable Size 310381648 bytes Database Buffers 92274688 bytes Redo Buffers 4284416 bytes Database mounted. SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered. b)设置所有的启用的数据库名 SQL> show parameter log_archive_config;

NAME TYPE VALUE


log_archive_config string SQL> alter system set log_archive_config='dg_config=(oa,standbydb)';

System altered.

SQL> show parameter log_archive_config;

NAME TYPE VALUE


log_archive_config string dg_config=(oa,standbydb) 注:dg_config为主备库的db_unique_name c)设置归档日志的路径(本地和网络) SQL> show parameter log_archive_dest_1;

NAME TYPE VALUE


log_archive_dest_1 string log_archive_dest_10 string log_archive_dest_11 string log_archive_dest_12 string log_archive_dest_13 string log_archive_dest_14 string log_archive_dest_15 string log_archive_dest_16 string log_archive_dest_17 string log_archive_dest_18 string log_archive_dest_19 string SQL> show parameter log_archive_dest_2;

NAME TYPE VALUE


log_archive_dest_2 string log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string log_archive_dest_24 string log_archive_dest_25 string log_archive_dest_26 string log_archive_dest_27 string log_archive_dest_28 string log_archive_dest_29 string SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/OA/archivelog/ optional';

System altered.

SQL> alter system set log_archive_dest_2='service=oa lgwr sync affirm valid_for=(online_logfile,primary_role) db_unique_name=oa';

System altered.

SQL> show parameter log_archive_dest_1;

NAME TYPE VALUE


log_archive_dest_1 string LOCATION=/u01/app/oracle/fast_ recovery_area/OA/archivelog/ o ptional log_archive_dest_10 string log_archive_dest_11 string log_archive_dest_12 string log_archive_dest_13 string log_archive_dest_14 string log_archive_dest_15 string log_archive_dest_16 string log_archive_dest_17 string

NAME TYPE VALUE


log_archive_dest_18 string log_archive_dest_19 string SQL> show parameter log_archive_dest_2;

NAME TYPE VALUE


log_archive_dest_2 string service=oa lgwr sync affirm va lid_for=(online_logfile,primar y_role) db_unique_name=oa log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string log_archive_dest_24 string log_archive_dest_25 string log_archive_dest_26 string log_archive_dest_27 string

NAME TYPE VALUE


log_archive_dest_28 string log_archive_dest_29 string
注: 目的,当备库转换为主库的时候把重做日志写到新备库 service值为备用数据库的TNSNAMES描述符 d)设置备库文件自动管理,可以在主库新建数据文件的时候自动新建备库 SQL> show parameter standby_file_management;

NAME TYPE VALUE


standby_file_management string MANUAL SQL> alter system set standby_file_management='AUTO';

System altered.

SQL> show parameter standby_file_management;

NAME TYPE VALUE


standby_file_management string AUTO e)设置FAL_SERVER为备库Oracle Net service name, SQL> show parameter fal_server;

NAME TYPE VALUE


fal_server string SQL> alter system set fal_server='oa';

System altered.

SQL> show parameter fal_server;

NAME TYPE VALUE


fal_server string oa

5.拷贝主库密码文件到备库 注:密码文件一般都在$ORACLE_HOME/dbs/下,名称为orapw实例名; 注意每个实例的实例名不一样,所以复制到对应的实例下时一定需要注意命名问题

6.使用RMAN备份主库 备份脚本: rman target / log=/backup/rmanbackup/2018-05-11/rman_oa_2018-05-11.log <<EOF run { allocate channel c1 type disk; allocate channel c2 type disk; change archivelog all crosscheck; crosscheck backup; delete noprompt expired backup; delete noprompt archivelog until time '(sysdate-10)'; backup database format '/backup/rmanbackup/2018-05-11/db_%U_%I_%T' TAG='standby_db'; sql 'alter system archive log current'; backup archivelog all not backed up format '/backup/rmanbackup/2018-05-11/arch_%U_%I_%T' tag='standby_arch'; backup current controlfile format='/backup/rmanbackup/2018-05-11/ctl_%U_%I_%T' tag='standby_ctl'; backup spfile format='/backup/rmanbackup/2018-05-11/spfile_%U_%I_%T' tag='standby_spfile'; release channel c1; release channel c2; } EOF

7.备库还原(使用主库RMAN备份文件还原) 1)授权从主库拷贝到备库的RMAN备份文件 [root@oadbstandby rmanbackup]# chown -R oracle:oinstall 2018-05-11 2)登录到备库,启动备库到nomount状态 [root@oadbstandby /]# su - oracle [oracle@oadbstandby ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri May 11 10:56:14 2018

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

connected to target database: OA (DBID=3393907898)

RMAN> startup nomount force;

Oracle instance started

Total System Global Area 409194496 bytes

Fixed Size 2253744 bytes Variable Size 314575952 bytes Database Buffers 88080384 bytes Redo Buffers 4284416 bytes

RMAN> 3)将备库dbid设置和主库dbid相同 RMAN> set dbid 3393850737;

executing command: SET DBID 4)还原控制文件 RMAN> restore standby controlfile from '/backup/rmanbackup/2018-05-11/ctl_0at2k9ev_1_1_3393850737_20180511';

Starting restore at 11-MAY-18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 output file name=/u01/app/oracle/oradata/oa/control01.ctl output file name=/u01/app/oracle/fast_recovery_area/oa/control02.ctl Finished restore at 11-MAY-18 5)启动数据库到mount状态 RMAN> alter database mount;

database mounted released channel: ORA_DISK_1 6)注册从源数据库拷贝过来的备份集到rman中 RMAN> catalog start with '/backup/rmanbackup/2018-05-11/';

Starting implicit crosscheck backup at 11-MAY-18 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=27 device type=DISK Crosschecked 7 objects Finished implicit crosscheck backup at 11-MAY-18

Starting implicit crosscheck copy at 11-MAY-18 using channel ORA_DISK_1 Finished implicit crosscheck copy at 11-MAY-18

searching for all files in the recovery area cataloging files... cataloging done

======================= List of Cataloged Files File Name: /u01/app/oracle/fast_recovery_area/STANDBYDB/archivelog/2018_05_11/o1_mf_1_8_fh9zs7mh_.arc File Name: /u01/app/oracle/fast_recovery_area/STANDBYDB/archivelog/2018_05_11/o1_mf_1_6_fh9znlgg_.arc File Name: /u01/app/oracle/fast_recovery_area/STANDBYDB/archivelog/2018_05_11/o1_mf_1_7_fh9zpxw5_.arc

searching for all files that match the pattern /backup/rmanbackup/2018-05-11/

List of Files Unknown to the Database

File Name: /backup/rmanbackup/2018-05-11/spfile_0bt2k9f1_1_1_3393850737_20180511 File Name: /backup/rmanbackup/2018-05-11/rman_oa_2018-05-11.log File Name: /backup/rmanbackup/2018-05-11/ctl_0at2k9ev_1_1_3393850737_20180511

Do you really want to catalog the above files (enter YES or NO)? yes cataloging files... cataloging done

======================= List of Cataloged Files File Name: /backup/rmanbackup/2018-05-11/spfile_0bt2k9f1_1_1_3393850737_20180511 File Name: /backup/rmanbackup/2018-05-11/ctl_0at2k9ev_1_1_3393850737_20180511

======================================= List of Files Which Where Not Cataloged

File Name: /backup/rmanbackup/2018-05-11/rman_oa_2018-05-11.log RMAN-07517: Reason: The file header is corrupted 7)还原数据文件 RMAN> run {
set newname for datafile 1 to '/u01/app/oracle/oradata/oa/system01.dbf'; set newname for datafile 2 to '/u01/app/oracle/oradata/oa/sysaux01.dbf'; set newname for datafile 3 to '/u01/app/oracle/oradata/oa/undotbs01.dbf'; set newname for datafile 4 to '/u01/app/oracle/oradata/oa/users01.dbf'; restore database; switch datafile all; }6> 7> 8>

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 11-MAY-18 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 00002 to /u01/app/oracle/oradata/oa/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/oa/undotbs01.dbf channel ORA_DISK_1: reading from backup piece /backup/rmanbackup/2018-05-11/db_04t2k9d9_1_1_3393850737_20180511 channel ORA_DISK_1: piece handle=/backup/rmanbackup/2018-05-11/db_04t2k9d9_1_1_3393850737_20180511 tag=STANDBY_DB channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 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/oa/system01.dbf channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/oa/users01.dbf channel ORA_DISK_1: reading from backup piece /backup/rmanbackup/2018-05-11/db_03t2k9d9_1_1_3393850737_20180511 channel ORA_DISK_1: piece handle=/backup/rmanbackup/2018-05-11/db_03t2k9d9_1_1_3393850737_20180511 tag=STANDBY_DB channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:16 Finished restore at 11-MAY-18

datafile 1 switched to datafile copy input datafile copy RECID=1 STAMP=975841382 file name=/u01/app/oracle/oradata/oa/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=2 STAMP=975841382 file name=/u01/app/oracle/oradata/oa/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=3 STAMP=975841382 file name=/u01/app/oracle/oradata/oa/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=4 STAMP=975841382 file name=/u01/app/oracle/oradata/oa/users01.dbf 8)恢复数据库 RMAN> recover database;

Starting recover at 11-MAY-18 using channel ORA_DISK_1

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=8 channel ORA_DISK_1: reading from backup piece /backup/rmanbackup/2018-05-11/arch_08t2k9es_1_1_3393850737_20180511 channel ORA_DISK_1: piece handle=/backup/rmanbackup/2018-05-11/arch_08t2k9es_1_1_3393850737_20180511 tag=STANDBY_ARCH 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/fast_recovery_area/OA/archivelog/1_8_975777331.dbf thread=1 sequence=8 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 /backup/rmanbackup/2018-05-11/arch_09t2k9eu_1_1_3393850737_20180511 channel ORA_DISK_1: piece handle=/backup/rmanbackup/2018-05-11/arch_09t2k9eu_1_1_3393850737_20180511 tag=STANDBY_ARCH 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/fast_recovery_area/OA/archivelog/1_9_975777331.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 05/11/2018 11:03:46 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 10 and starting SCN of 1001269

8.创建standby redolog SQL> alter database drop standby logfile group 11;

Database altered.

SQL> alter database drop standby logfile group 12;

Database altered.

SQL> alter database drop standby logfile group 13;

Database altered.

SQL> alter database drop standby logfile group 14;

Database altered.

SQL> alter database add standby logfile group 11 ('/data/oradata/standby11.log') size 50m;

Database altered.

SQL> alter database add standby logfile group 12 ('/data/oradata/standby12.log') size 50m;

Database altered.

SQL> alter database add standby logfile group 13 ('/data/oradata/standby13.log') size 50m;

Database altered.

SQL> alter database add standby logfile group 14 ('/data/oradata/standby14.log') size 50m;

9.以只读方式打开standbydb SQL> alter database open read only;

Database altered.

10.standbydb启动redo apply SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.