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注意：备库参数文件加上LOG_FILE_NAME_CONVERT参数,不然duplicate过程会出现以下错误
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')
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