ORACLE12C ADG搭建

ORACLE12C_DG配置
主库：orcl
备库：orclbk

1、/etc/hosts配置

172.16.140.3 node1
172.16.140.4 node2

2、主库force logging

SQL> select name,open_mode from v$pdbs;
SQL> alter database force logging;
SQL> select force_logging from v$database;

FORCE_LOGGING

YES

3、主库添加standby redo logfile(连接到CDB$ROOT中执行)
SQL> show con_name;

CON_NAME

CDB$ROOT
SQL> select group#, members, bytes from v$log;

GROUP#    MEMBERS      BYTES

     1          1  209715200
     2          1  209715200
     3          1  209715200

SQL> select member from v$logfile;

MEMBER

/u01/app/oracle/oradata/orcl/redo01.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo03.log

添加4(3+1)个standby logfile
SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/standby_redo01.log' size 200m;
SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/standby_redo02.log' size 200m;
SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/standby_redo03.log' size 200m;
SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/standby_redo04.log' size 200m;

4、配置tnsnames.ora
主库：
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))

orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

orclbk =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclbk)
)
)

orclpdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclpdb)
)
)

备库：
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))

orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

orclbk =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclbk)
)
)

orclpdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclpdb)
)
)

5、修改主备库的参数文件：
主库操作：
SQL> create pfile from spfile;

修改后的pfile
orcl.data_transfer_cache_size=0
orcl.db_cache_size=1459617792
orcl.inmemory_ext_roarea=0
orcl.inmemory_ext_rwarea=0
orcl.java_pool_size=16777216
orcl.large_pool_size=33554432
orcl.oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.pga_aggregate_target=687865856
orcl.sga_target=2046820352
orcl.shared_io_pool_size=100663296
orcl.shared_pool_size=419430400
orcl.streams_pool_size=0
._undo_autotune=FALSE
.archive_lag_target=0
.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
.audit_trail='none'
.compatible='12.2.0'
.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'
.data_guard_sync_latency=0
.db_block_size=8192
.db_file_name_convert='/u01/app/oracle/oradata/orclbk','/u01/app/oracle/oradata/orcl'
.db_name='orcl'
.db_unique_name='orcl'
.dg_broker_start=TRUE
.diagnostic_dest='/u01/app/oracle'
.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
.enable_pluggable_database=true
.fal_client='orcl'
.fal_server=''
.local_listener='LISTENER_ORCL'
.log_archive_config='dg_config=(orcl,orclbk)'
.log_archive_dest_1='location=/u01/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcl'
.log_archive_dest_2='service="orclbk"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="orclbk" net_timeout=30','valid_for=(online_logfile,all_roles)'
.log_archive_dest_state_1='ENABLE'
.log_archive_dest_state_2='ENABLE'
.log_archiveformat='orcl%t%s%r.arc'
.log_archive_max_processes=4
.log_archive_min_succeed_dest=1
orcl.log_archive_trace=0
.log_file_name_convert='/u01/app/oracle/oradata/orclbk','/u01/app/oracle/oradata/orcl'
.nls_language='AMERICAN'
.nls_territory='AMERICA'
.open_cursors=1500
.pga_aggregate_target=650m
.processes=300
.remote_login_passwordfile='EXCLUSIVE'
.resource_limit=TRUE
.session_cached_cursors=1500
.sga_target=1948m
.standby_file_management='AUTO'
.undo_retention=7200
*.undo_tablespace='UNDOTBS1'

改动的部分：
._undo_autotune=FALSE
.db_file_name_convert='/u01/app/oracle/oradata/orclbk','/u01/app/oracle/oradata/orcl'
.db_name='orcl'
.db_unique_name='orcl'
.dg_broker_start=TRUE
.fal_client='orcl'
.fal_server=''
.log_archive_config='dg_config=(orcl,orclbk)'
.log_archive_dest_1='location=/u01/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcl'
.log_archive_dest_2='service="orclbk"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="orclbk" net_timeout=30','valid_for=(online_logfile,all_roles)'
.log_archive_dest_state_1='ENABLE'
.log_archive_dest_state_2='ENABLE'
.log_archiveformat='orcl%t%s%r.arc'
.log_file_name_convert='/u01/app/oracle/oradata/orclbk','/u01/app/oracle/oradata/orcl'
*.standby_file_management='AUTO'

备库的参数文件改动后如下：

orclbk.data_transfer_cache_size=0
orclbk.db_cache_size=1560281088
orclbk.inmemory_ext_roarea=0
orclbk.inmemory_ext_rwarea=0
orclbk.java_pool_size=16777216
orclbk.large_pool_size=33554432
orclbk.oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orclbk.pga_aggregate_target=687865856
orclbk.sga_target=2046820352
orclbk.shared_io_pool_size=0
orclbk.shared_pool_size=419430400
orclbk.streams_pool_size=0
._undo_autotune=FALSE
.archive_lag_target=0
.audit_file_dest='/u01/app/oracle/admin/orclbk/adump'
.audit_trail='none'
.compatible='12.2.0'
.control_files='/u01/app/oracle/oradata/orclbk/control01.ctl','/u01/app/oracle/oradata/orclbk/control02.ctl'#Restore Controlfile
.data_guard_sync_latency=0
.db_block_size=8192
.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orclbk'
.db_name='orcl'
.db_unique_name='orclbk'
.dg_broker_start=TRUE
.diagnostic_dest='/u01/app/oracle'
.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
.enable_pluggable_database=true
.fal_client='orclbk'
.fal_server='ORCL'
.log_archive_config='dg_config=(orclbk,orcl)'
.log_archive_dest_1='location=/u01/arch valid_for=(all_logfiles,all_roles) db_unique_name=orclbk'
.log_archive_dest_2=''
.log_archive_dest_state_1='ENABLE'
.log_archive_dest_state_2='ENABLE'
.log_archiveformat='orclbk%t%s%r.arc'
orclbk.log_archiveformat='orclbk%t%s%r.arc'
.log_archive_max_processes=4
.log_archive_min_succeed_dest=1
orcl.log_archive_trace=0
orclbk.log_archive_trace=0
.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orclbk'
.nls_language='AMERICAN'
.nls_territory='AMERICA'
.open_cursors=1500
.pga_aggregate_target=650m
.processes=300
.remote_login_passwordfile='EXCLUSIVE'
.resource_limit=TRUE
.session_cached_cursors=1500
.sga_target=1948m
.standby_file_management='AUTO'
.undo_retention=7200
.undo_tablespace='UNDOTBS1'

主要的改动部分如下：
._undo_autotune=FALSE
.audit_trail='none'
.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orclbk'
.db_name='orcl'
.db_unique_name='orclbk'
.enable_pluggable_database=true
.fal_client='orclbk'
.fal_server='ORCL'
.log_archive_config='dg_config=(orclbk,orcl)'
.log_archive_dest_1='location=/u01/arch valid_for=(all_logfiles,all_roles) db_unique_name=orclbk'
.log_archive_dest_2=''
.log_archive_dest_state_1='ENABLE'
.log_archive_dest_state_2='ENABLE'
.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orclbk'

6、备份源端数据库，将备份文件传到目标端
RMAN> run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup filesperset 2 database format '/home/oracle/dbbackup/full%d%T%s_%p';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all format '/home/oracle/dbbackup/arch%d%T%s_%p' delete input;
backup current controlfile format '/home/oracle/dbbackup/ctl%d%T%s_%p';
}

cd /home/oracle/db_backup/
scp * node2:/home/oracle/db_backup

7、源端生成备库的控制文件，并传到目标端
SQL> alter database create standby controlfile as '/home/oracle/db_backup/ctl';
cd /home/oracle/db_backup
scp ctl node2:/home/oracle/db_backup

8、备库恢复
SQL> startup nomount;
RMAN> restore controlfile from '/home/oracle/db_backup/ctl';
SQL> alter database mount standby database;
RMAN> restore database;
SQL> alter database recover managed standby database using current logfile disconnect from session;

ADG
1、查看备库openmode
SQL> select open_mode from v$database; --MOUNTED
2、取消备库自动恢复
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> select open_mode from v$database; --此时为READ ONLY
3、read only下备库恢复
SQL> alter database recover managed standby database using current logfile disconnect;
SQL> select open_mode from v$database; --此时为READ ONLY WITH APPLY

DG_BROKER

--在主备库同时执行

--启动dg_broker
alter system set dg_broker_start=true sid='*';

--配置监听
主库：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)
(SID_NAME = orcl)
)
)

ADR_BASE_LISTENER = /u01/app/oracle

备库：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orclbk_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)
(SID_NAME = orclbk)
)
)

ADR_BASE_LISTENER = /u01/app/oracle

--连接dg_broker进行配置
连接测试：
$ dgmgrl
DGMGRL> connect sysdg@orcl
DGMGRL> connect sysdg@orclbk

测试是否能连上，如果连不上可能是sysdg用户被锁定，给一个密码并解锁

--在主库配置
DGMGRL> connect sysdg

DGMGRL> create configuration 'orcl_dg' as primary database is 'orcl' connect identifier is 'orcl';
show configuration;

--说明：
orcl_dg是配置名称，可以随便填。
primary database is 'orcl' orcl是db_unique_name
connect identifier is 'orcl' orcl是tnsname.ora连接到主库的net service name

--添加备库：
add database 'orclbk' as connect identifier is orclbk maintained as physical;
--说明：
add database 'orclbk': orclbk是 db_unique_name
as connect identifier is orclbk: orclbk是tnsnames.ora的service name

--查看配置
show configuration;

--启用配置
enable configuration;

--查看库
show database orcl;

show database orclbk;

--切换测试
switchover to orclbk;

switchover to orcl;