oracle12g ADG Construction

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, the main library add standby redo logfile (connected to cdb$root 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) of 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, Configuration 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)
)
)

Standby 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 main repository parameter file:
Main Library Operations:
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 '
. 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 '

Parts 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 '

The following changes are made to the parameter files of the repository:

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 '

The main changes to the

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 upload the backup file to the target side.
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 source side to generate the control file, and upload 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. Recovery of Standby Library
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 standby OpenMode
Sql> select Open_mode from V$database; --mounted
2. Automatic recovery of the standby library is canceled.
sql> ALTER DATABASE recover managed standby database cancel;
sql> ALTER DATABASE open;
Sql> select Open_mode from V$database; --This is read only
3. Read Only repository Recovery
sql> ALTER DATABASE recover managed standby database using current logfile disconnect;
Sql> select Open_mode from V$database; --This is read with APPLY

Dg_broker

--Simultaneous execution at the master repository

--Start Dg_broker
Alter system set Dg_broker_start=true sid= ' * ';

--Configuring monitoring
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

Standby 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 Dg_broker for configuration
Connection test:
$ dgmgrl
dgmgrl> connect [email protected]
dgmgrl> connect [email protected]

Test if it can be connected, if it is not possible that the SYSDG user is locked, give a password and unlock

--In the main library configuration
Dgmgrl> Connect SYSDG

dgmgrl> Create configuration ' ORCL_DG ' as primary database is ' ORCL ' connect identifier is ' ORCL ';
Show configuration;

--Description:
ORCL_DG is the configuration name and can be filled in.
Primary database is ' ORCL ' ORCL are db_unique_name
Connect identifier is ' ORCL ' ORCL is the net service name Tnsname.ora connected 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;

--View Library
Show Database ORCL;

Show Database ORCLBK;

--Switch test
Switchover to ORCLBK;

Switchover to ORCL;

oracle12g ADG Construction