使用RMAN duplicate搭建12C的Data Guard环境

1. 实验环境

 

主端

备端

主机名

12cr2

oracle

IP地址

192.168.16.81

192.168.16.130

db_unique_name

newcdb

newcdbdg

2. 主端的数据库环境

[oracle@12cr2 ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 24 22:50:48 2017Copyright (c) 1982, 2016, Oracle.  All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionSQL> show pdbs;    CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------         2 PDB$SEED                       READ ONLY  NO         3 DGPDB1                         READ WRITE NO         4 DGPDB2                         READ WRITE NO         5 DGPDB3                         READ WRITE NO

3. 添加hosts文件

主备端添加以下内容

[root@oracle ~]# vi /etc/hosts192.168.16.81   12cr2192.168.16.130  oracle

4. 主端开启force logging

SQL> select name,log_mode,force_logging from gv$database;NAME      LOG_MODE     FORCE_LOGGING--------- ------------ ---------------------------------------NEWCDB    ARCHIVELOG   NOSQL> alter database force logging;Database altered.SQL> select name,log_mode,force_logging from gv$database;NAME      LOG_MODE     FORCE_LOGGING--------- ------------ ---------------------------------------NEWCDB    ARCHIVELOG   YES

5. 主端提前添加standby redo，备端会自动添加

SQL> col member format a50SQL> select a.GROUP#,BYTES/1024/1024STATUS,TYPE,MEMBER from v$log a,v$logfile b where a.GROUP#=b.GROUP#;    GROUP#     STATUS TYPE    MEMBER---------- ---------- ------- -------------------------------------------------         3        200 ONLINE  /u01/app/oracle/oradata/newcdb/redo03.log         2        200 ONLINE  /u01/app/oracle/oradata/newcdb/redo02.log         1        200 ONLINE  /u01/app/oracle/oradata/newcdb/redo01.logSQL> alter database add standby logfile  2  group 4 ('/u01/app/oracle/oradata/newcdb/stdbyredo01.log') size 200M,  3  group 5 ('/u01/app/oracle/oradata/newcdb/stdbyredo02.log') size 200M,  4  group 6 ('/u01/app/oracle/oradata/newcdb/stdbyredo03.log') size 200M,  5  group 7 ('/u01/app/oracle/oradata/newcdb/stdbyredo04.log') size 200M;

6. 主端配置静态监听

配置listener.ora文件，添加newcdb的静态监听条目

[oracle@12cr2 admin]$ vi listener.ora SID_LIST_LISTENER =  (SID_LIST =    (SID_DESC =      (GLOBAL_DBNAME = newcdb)      (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)      (SID_NAME = newcdb)    )   )LISTENER =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.81)(PORT = 1521))  )ADR_BASE_LISTENER = /u01/app/oracle

配置tnsnames.ora文件，添加备端的监听连接串

[oracle@12cr2 admin]$ vi tnsnames.ora LISTENER_NEWCDB =  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.81)(PORT = 1521))newcdb =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.81)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = newcdb)    )  )newcdbdg =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.130)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = newcdb)    )  )

7. 备端配置静态监听

配置listener.ora文件，添加newcdb的静态监听条目

[oracle@oracle admin]$ cat listener.ora SID_LIST_LISTENER =  (SID_LIST =    (SID_DESC =      (GLOBAL_DBNAME = newcdb)      (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)      (SID_NAME = newcdb)    )  )LISTENER =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.130)(PORT = 1521))  )ADR_BASE_LISTENER = /u01/app/oracle

配置tnsnames.ora文件，添加主端的监听连接串

[oracle@oracle admin]$ cat tnsnames.ora LISTENER_NEWCDB =  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.81)(PORT = 1521))newcdb =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.81)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = newcdb)    )  )newcdbdg =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.130)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = newcdb)    )  )

8. tnsping测试网络连通性

备端tnsping主端

[oracle@oracle ~]$ tnsping newcdb

主端tnsping备端

[oracle@12cr2 ~]$ tnsping newcdbdg

9. 主端创建pfile文件并将pfile和密码文件传输到备端

SQL> create pfile='/home/oracle/pfile.ora' from spfile;[oracle@12cr2 ~]$ scp /home/oracle/pfile.ora 192.168.16.130:`pwd`[oracle@12cr2 dbs]$ scp orapwnewcdb 192.168.16.130:`pwd`

10. 备端修改主端传输的pfile文件

添加db_unique_name，要不同于主库

[oracle@oracle ~]$ cat pfile.ora newcdb.__data_transfer_cache_size=0newcdb.__db_cache_size=541065216newcdb.__inmemory_ext_roarea=0newcdb.__inmemory_ext_rwarea=0newcdb.__java_pool_size=4194304newcdb.__large_pool_size=8388608newcdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentnewcdb.__pga_aggregate_target=264241152newcdb.__sga_target=838860800newcdb.__shared_io_pool_size=33554432newcdb.__shared_pool_size=239075328newcdb.__streams_pool_size=0*.audit_file_dest='/u01/app/oracle/admin/newcdb/adump'*.audit_trail='db'*.compatible='12.2.0'*.control_files='/u01/app/oracle/oradata/newcdb/control01.ctl','/u01/app/oracle/oradata/newcdb/control02.ctl'*.db_block_size=8192*.db_name='newcdb'*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=newcdbXDB)'*.enable_pluggable_database=true*.local_listener='LISTENER_NEWCDB'*.log_archive_dest_1='LOCATION=/archive'*.log_archive_format='%t_%s_%r.dbf'*.nls_language='AMERICAN'*.nls_territory='AMERICA'*.open_cursors=300*.pga_aggregate_target=250m*.processes=300*.remote_login_passwordfile='EXCLUSIVE'*.sga_target=800m*.undo_tablespace='UNDOTBS1'*.db_unique_name='newcdbdg'*.LOG_FILE_NAME_CONVERT=('/u01/app/oracle/oradata/newcdb','/u01/app/oracle/oradata/newcdb')

注意：备库参数文件加上LOG_FILE_NAME_CONVERT参数,不然duplicate过程会出现以下错误

RMAN> duplicate target database for standby from active database nofilenamecheck;input datafile copy RECID=22 STAMP=947550530 file name=/u01/app/oracle/oradata/newcdb/dgpdb3/users01.dbfORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamedORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/newcdb/redo01.log'RMAN-05535: warning: All redo log files were not defined properly.ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamedORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/newcdb/redo02.log'RMAN-05535: warning: All redo log files were not defined properly.ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamedORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/newcdb/redo03.log'RMAN-05535: warning: All redo log files were not defined properly.Finished Duplicate Db at 25-JUN-17

参考文档：
Duplicate for Standby fails with RMAN-05535 when directory path is the same as primary (文档 ID 783113.1)

11. 备端创建参数文件所需目录

[root@oracle ~]# mkdir /archive[root@oracle ~]# chown oracle:oinstall /archive/[oracle@oracle ~]$ mkdir -p /u01/app/oracle/admin/newcdb/adump[oracle@oracle ~]$ mkdir -p /u01/app/oracle/oradata/newcdb

12. 启动备库到nomount状态

[oracle@oracle ~]$ export ORACLE_SID=newcdb[oracle@oracle ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 24 23:59:36 2017Copyright (c) 1982, 2016, Oracle.  All rights reserved.Connected to an idle instance.SQL> create spfile from pfile='/home/oracle/pfile.ora';File created.SQL> startup nomount;ORACLE instance started.Total System Global Area  838860800 bytesFixed Size                  8798312 bytesVariable Size             322965400 bytesDatabase Buffers          503316480 bytesRedo Buffers                3780608 bytesSQL> show parameter db_unique_nameNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_unique_name                       string      newcdbdg

13. 测试主备库之间的连通性

[oracle@oracle ~]$ sqlplus sys/oracle@newcdbdg as sysdba[oracle@oracle ~]$ sqlplus sys/oracle@newcdb as sysdba[oracle@12cr2 dbs]$ sqlplus sys/oracle@newcdbdg as sysdba[oracle@12cr2 dbs]$ sqlplus sys/oracle@newcdb as sysdba

14. 备库执行RMAN duplicate

[oracle@oracle ~]$ rman target sys/oracle@newcdb auxiliary sys/oracle@newcdbdg nocatalogRecovery Manager: Release 12.2.0.1.0 - Production on Sun Jun 25 00:12:14 2017Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.connected to target database: NEWCDB (DBID=36700136)using target database control file instead of recovery catalogconnected to auxiliary database: NEWCDB (not mounted)RMAN> duplicate target database for standby from active database nofilenamecheck;

15. 主端和备端启动data guard broker

如果使用data guard broker，需要先修改主备库的local_listener参数，不然broker添加备库报以下错：

DGMGRL> enable database newcdbdgEnabled.DGMGRL> show configuration;Configuration - dg_newcdb  Protection Mode: MaxPerformance  Members:  newcdb   - Primary database    newcdbdg - Physical standby database       Error: ORA-16810: multiple errors or warnings detected for the memberFast-Start Failover: DISABLEDConfiguration Status:ERROR   (status updated 17 seconds ago)

修改主备库的local_listener参数

SQL> show parameter localNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------local_listener                       string      LISTENER_NEWCDBparallel_force_local                 boolean     FALSESQL> alter system set local_listener=newcdb;      ##主端修改System altered.SQL> alter system set local_listener=newcdbdg;    ##备端修改System altered.SQL> show parameter localNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------local_listener                       string      NEWCDBDGparallel_force_local                 boolean     FALSE

主端和备端启动data guard broker

SQL> show parameter dg_brokerNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------dg_broker_config_file1               string      /u01/app/oracle/product/12.2.0                                                 /db_1/dbs/dr1newcdb.datdg_broker_config_file2               string      /u01/app/oracle/product/12.2.0                                                 /db_1/dbs/dr2newcdb.datdg_broker_start                      boolean     FALSESQL> !ps -ef |grep dmonoracle    7078  7063  0 00:57 pts/0    00:00:00 /bin/bash -c ps -ef |grep dmonoracle    7080  7078  0 00:57 pts/0    00:00:00 grep dmonSQL> alter system set dg_broker_start=true;System altered.SQL> !ps -ef |grep dmonoracle    7089     1  0 00:57 ?        00:00:00 ora_dmon_newcdboracle    7092  7063  0 00:57 pts/0    00:00:00 /bin/bash -c ps -ef |grep dmonoracle    7094  7092  0 00:57 pts/0    00:00:00 grep dmon

16. 主端配置data guard broker

[oracle@12cr2 dbs]$ dgmgrl /DGMGRL for Linux: Release 12.2.0.1.0 - Production on Sun Jun 25 01:00:09 2017Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.Welcome to DGMGRL, type "help" for information.Connected to "newcdb"Connected as SYSDG.DGMGRL> help createCreates a broker configurationSyntax:  CREATE CONFIGURATION <configuration name> [AS]     PRIMARY DATABASE IS <database name>    CONNECT IDENTIFIER IS <connect identifier>;DGMGRL> create configuration dg_newcdb as primary database is newcdb connect identifier is newcdb;          ##添加主端配置Configuration "dg_newcdb" created with primary database "newcdb"DGMGRL> show configuration;Configuration - dg_newcdb  Protection Mode: MaxPerformance  Members:  newcdb - Primary databaseFast-Start Failover: DISABLEDConfiguration Status:DISABLEDDGMGRL> enable configuration        ##使配置生效Enabled.DGMGRL> show configuration;Configuration - dg_newcdb  Protection Mode: MaxPerformance  Members:  newcdb - Primary databaseFast-Start Failover: DISABLEDConfiguration Status:SUCCESS   (status updated 12 seconds ago)

主端data guard broker添加备端

DGMGRL> help addAdds a member to the broker configurationSyntax:  ADD { RECOVERY_APPLIANCE | DATABASE | FAR_SYNC } <object name>    [AS CONNECT IDENTIFIER IS <connect identifier>];DGMGRL> add database newcdbdg as connect identifier is newcdbdg;Database "newcdbdg" addedDGMGRL> show configuration;Configuration - dg_newcdb  Protection Mode: MaxPerformance  Members:  newcdb   - Primary database    newcdbdg - Physical standby database (disabled)Fast-Start Failover: DISABLEDConfiguration Status:SUCCESS   (status updated 53 seconds ago)DGMGRL> enable database newcdbdgEnabled.DGMGRL> show configuration;Configuration - dg_newcdb  Protection Mode: MaxPerformance  Members:  newcdb   - Primary database    newcdbdg - Physical standby database Fast-Start Failover: DISABLEDConfiguration Status:SUCCESS   (status updated 17 seconds ago)

如果出现以下WARNING，可以在主端手动切换redo

DGMGRL> show configuration;Configuration - dg_newcdb  Protection Mode: MaxPerformance  Members:  newcdb   - Primary database    newcdbdg - Physical standby database       Warning: ORA-16854: apply lag could not be determinedFast-Start Failover: DISABLEDConfiguration Status:WARNING   (status updated 5 seconds ago)SQL> alter system switch logfile;System altered.DGMGRL> show configuration;Configuration - dg_newcdb  Protection Mode: MaxPerformance  Members:  newcdb   - Primary database    newcdbdg - Physical standby database Fast-Start Failover: DISABLEDConfiguration Status:SUCCESS   (status updated 4 seconds ago)

17. data guard broker的standby_file_management参数

DGMGRL> enable configuration  会自动修改数据库参数

手动修改参数，broker会自动刷回到broker的默认参数，例如

SQL> show parameter standby_file_managementNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------standby_file_management              string      MANUALSQL> ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';System altered.SQL> show parameter standby_file_managementNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------standby_file_management              string      AUTODGMGRL> enable configurationEnabled.SQL>  show parameter standby_file_managementNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------standby_file_management              string      MANUAL

Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE (文档 ID 1075908.1)

Please note if standby_file_management was set to manual. Ensure you set it back to Auto on the standby database once the standby creation is completed

修改方法

DGMGRL> EDIT DATABASE newcdb SET PROPERTY 'StandbyFileManagement' = 'AUTO';Property "StandbyFileManagement" updatedDGMGRL> EDIT DATABASE newcdbdg SET PROPERTY 'StandbyFileManagement' = 'AUTO';Property "StandbyFileManagement" updated

测试

SQL> show parameter standby_file_managementNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------standby_file_management              string      AUTODGMGRL> enable configurationEnabled.SQL> show parameter standby_file_managementNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------standby_file_management              string      AUTO

参考文档：

How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database (文档 ID 1416554.1)
hen set standby_file_management to AUTO from DGMGRL,
DGMGRL> EDIT DATABASE '<standby db_unique_name>' SET PROPERTY 'StandbyFileManagement' = 'AUTO';

查看数据库参数详细配置信息

DGMGRL> show database verbose newcdb

18. 备端启动只读模式

SQL> show pdbs;    CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------         2 PDB$SEED                       MOUNTED         3 DGPDB1                         MOUNTED         4 DGPDB2                         MOUNTED         5 DGPDB3                         MOUNTEDSQL> alter database open;Database altered.SQL> select open_mode from v$database;OPEN_MODE--------------------READ ONLYSQL> /OPEN_MODE--------------------READ ONLYSQL> /OPEN_MODE--------------------READ ONLY WITH APPLYSQL> show pdbs    CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------         2 PDB$SEED                       READ ONLY  NO         3 DGPDB1                         MOUNTED         4 DGPDB2                         MOUNTED         5 DGPDB3                         MOUNTEDSQL> alter pluggable database DGPDB1 open;Pluggable database altered.SQL> show pdbs    CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------         2 PDB$SEED                       READ ONLY  NO         3 DGPDB1                         READ ONLY  NO         4 DGPDB2                         MOUNTED         5 DGPDB3                         MOUNTED

19. 查看主备同步

SQL> col VALUE format a30SQL> select name,value  from v$dataguard_stats;NAME                             VALUE-------------------------------- ------------------------------transport lag                    +00 00:00:00apply lag                        +00 00:00:00apply finish timeestimated startup time           7