ORACLE12C ADG搭建

原创

洛阳纸不贵 2018-03-22 20:37:48 ©著作权

文章标签 ORACLE 12C ADG 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者洛阳纸不贵的原创作品，请联系作者获取转载授权，否则将追究法律责任

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_archive_format='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_archive_format='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_archive_format='orclbk%t%s%r.arc' orclbk.log_archive_format='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/db_backup/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/db_backup/arch_%d_%T_%s_%p' delete input; backup current controlfile format '/home/oracle/db_backup/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;