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

最新推荐文章于 2023-03-18 23:47:40 发布

张玉龙

最新推荐文章于 2023-03-18 23:47:40 发布

阅读量2.1k

点赞数

分类专栏： oracle12c

本文链接：https://blog.csdn.net/u010692693/article/details/73826558

版权

oracle12c 专栏收录该内容

31 篇文章 2 订阅

订阅专栏

1. 实验环境

	主端	备端
主机名	12cr2	oracle
IP地址	192.168.16.81	192.168.16.130
db_unique_name	newcdb	newcdbdg

2. 主端的数据库环境

[oracle@12cr2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 24 22:50:48 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> 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/hosts
192.168.16.81   12cr2
192.168.16.130  oracle

4. 主端开启force logging

SQL> select name,log_mode,force_logging from gv$database;
NAME      LOG_MODE     FORCE_LOGGING
--------- ------------ ---------------------------------------
NEWCDB    ARCHIVELOG   NO

SQL> 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 a50
SQL> 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.log

SQL> 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=0
newcdb.__db_cache_size=541065216
newcdb.__inmemory_ext_roarea=0
newcdb.__inmemory_ext_rwarea=0
newcdb.__java_pool_size=4194304
newcdb.__large_pool_size=8388608
newcdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
newcdb.__pga_aggregate_target=264241152
newcdb.__sga_target=838860800
newcdb.__shared_io_pool_size=33554432
newcdb.__shared_pool_size=239075328
newcdb.__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.dbf
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-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 renamed
ORA-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 renamed
ORA-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 sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 24 23:59:36 2017
Copyright (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 bytes
Fixed Size                  8798312 bytes
Variable Size             322965400 bytes
Database Buffers          503316480 bytes
Redo Buffers                3780608 bytes

SQL> show parameter db_unique_name
NAME                                 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 nocatalog
Recovery Manager: Release 12.2.0.1.0 - Production on Sun Jun 25 00:12:14 2017
Copyright (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 catalog
connected 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 newcdbdg
Enabled.
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 member
Fast-Start Failover: DISABLED
Configuration Status:
ERROR   (status updated 17 seconds ago)

修改主备库的local_listener参数

SQL> show parameter local
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_NEWCDB
parallel_force_local                 boolean     FALSE

SQL> alter system set local_listener=newcdb;      ##主端修改
System altered.

SQL> alter system set local_listener=newcdbdg;    ##备端修改
System altered.

SQL> show parameter local
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      NEWCDBDG
parallel_force_local                 boolean     FALSE

主端和备端启动data guard broker

SQL> show parameter dg_broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/12.2.0
                                                 /db_1/dbs/dr1newcdb.dat
dg_broker_config_file2               string      /u01/app/oracle/product/12.2.0
                                                 /db_1/dbs/dr2newcdb.dat
dg_broker_start                      boolean     FALSE
SQL> !ps -ef |grep dmon
oracle    7078  7063  0 00:57 pts/0    00:00:00 /bin/bash -c ps -ef |grep dmon
oracle    7080  7078  0 00:57 pts/0    00:00:00 grep dmon

SQL> alter system set dg_broker_start=true;
System altered.

SQL> !ps -ef |grep dmon
oracle    7089     1  0 00:57 ?        00:00:00 ora_dmon_newcdb
oracle    7092  7063  0 00:57 pts/0    00:00:00 /bin/bash -c ps -ef |grep dmon
oracle    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 2017
Copyright (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 create

Creates a broker configuration
Syntax:
  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 database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED

DGMGRL> enable configuration        ##使配置生效
Enabled.

DGMGRL> show configuration;

Configuration - dg_newcdb
  Protection Mode: MaxPerformance
  Members:
  newcdb - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS   (status updated 12 seconds ago)

主端data guard broker添加备端

DGMGRL> help add
Adds a member to the broker configuration
Syntax:
  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" added

DGMGRL> show configuration;
Configuration - dg_newcdb
  Protection Mode: MaxPerformance
  Members:
  newcdb   - Primary database
    newcdbdg - Physical standby database (disabled)
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS   (status updated 53 seconds ago)

DGMGRL> enable database newcdbdg
Enabled.
DGMGRL> show configuration;
Configuration - dg_newcdb
  Protection Mode: MaxPerformance
  Members:
  newcdb   - Primary database
    newcdbdg - Physical standby database 
Fast-Start Failover: DISABLED
Configuration 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 determined
Fast-Start Failover: DISABLED
Configuration 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: DISABLED
Configuration Status:
SUCCESS   (status updated 4 seconds ago)

17. data guard broker的standby_file_management参数

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

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

SQL> show parameter standby_file_management
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL

SQL> ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
System altered.

SQL> show parameter standby_file_management
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO

DGMGRL> enable configuration
Enabled.

SQL>  show parameter standby_file_management
NAME                                 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" updated
DGMGRL> EDIT DATABASE newcdbdg SET PROPERTY 'StandbyFileManagement' = 'AUTO';
Property "StandbyFileManagement" updated

测试

SQL> show parameter standby_file_management
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO

DGMGRL> enable configuration
Enabled.

SQL> show parameter standby_file_management
NAME                                 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                         MOUNTED

SQL> alter database open;
Database altered.

SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY

SQL> /
OPEN_MODE
--------------------
READ ONLY

SQL> /
OPEN_MODE
--------------------
READ ONLY WITH APPLY

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DGPDB1                         MOUNTED
         4 DGPDB2                         MOUNTED
         5 DGPDB3                         MOUNTED

SQL> 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 a30
SQL> select name,value  from v$dataguard_stats;
NAME                             VALUE
-------------------------------- ------------------------------
transport lag                    +00 00:00:00
apply lag                        +00 00:00:00
apply finish time
estimated startup time           7

张玉龙

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
使用RMAN duplicate搭建12C的Data Guard环境

1. 实验环境主端备端主机名12cr2oracleIP地址192.168.16.81192.168.16.130db_unique_namenewcdbnewcdbdg2. 主端的数据库环境
复制链接

扫一扫