ORACLE12C ADG building
ORACLE12C_DG configuration
Main library: orcl
Standby library: orclbk
1. / etc/hosts configuration
172.16.140.3 node1
172.16.140.4 node2
2. Main library 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. Add standby redo logfile to the main library (connect to CDB$ROOT for execution)
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
Add 4 (3'1) standby logfile
SQL > alter database add standby logfile'/ u01 size size 200m
SQL > alter database add standby logfile'/ u01 size size 200m
SQL > alter database add standby logfile'/ u01 size size 200m
SQL > alter database add standby logfile'/ u01 size size 200m
4. Configure tnsnames.ora
Main library:
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)
)
)
Prepare the library:
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. Modify the parameter files of the master and slave libraries:
Main library operation:
SQL > create pfile from spfile
Modified 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'
.customers ='(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
.clients = 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'
Part of the change:
. _ 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'
After the parameter file of the library is changed, it is as follows:
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'
.customers ='(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
.clients = 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'
The main changes are as follows:
. _ 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. Back up the source database and transfer the backup files to the destination
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. The control file of the standby database is generated at the source end and transmitted to the target end.
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. Prepare for database recovery
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. Check the repository openmode.
SQL > select open_mode from vested database;-- MOUNTED
2. Cancel the reserve database and automatically restore it.
SQL > alter database recover managed standby database cancel
SQL > alter database open
SQL > select open_mode from vested database;-- READ ONLY at this time
3. Restore the slave database under read only
SQL > alter database recover managed standby database using current logfile disconnect
SQL > select open_mode from vested database;-- READ ONLY WITH APPLY at this time
DG_BROKER
-- execute at the same time in the main and standby libraries
-- start dg_broker
Alter system set dg_broker_start=true sid='*'
-- configure snooping
Main library:
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
Prepare the library:
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
-- connect to dg_broker for configuration
Connectivity testing:
$dgmgrl
DGMGRL > connect sysdg@orcl
DGMGRL > connect sysdg@orclbk
Test whether it can be connected. If not, the sysdg user may be locked. Give a password and unlock it.
-- configure in the main library
DGMGRL > connect sysdg
DGMGRL > create configuration 'orcl_dg' as primary database is' orcl' connect identifier is' orcl'
Show configuration
-- description:
Orcl_dg is the name of the configuration and can be entered at will.
Primary database is' orcl' orcl is db_unique_name.
Connect identifier is' orcl' orcl is the net service name that tnsname.ora connects to the main library
-- add a standby library:
Add database 'orclbk' as connect identifier is orclbk maintained as physical
-- description:
Add database 'orclbk': orclbk is db_unique_name.
As connect identifier is orclbk: orclbk is the service name of tnsnames.ora
-- View configuration
Show configuration
-- enable configuration
Enable configuration
-- check the library
Show database orcl
Show database orclbk
-- switching test
Switchover to orclbk
Switchover to orcl
