Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.