oracle12g ADG Construction

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.