[Oracle] Data guard (active-standby)

$ cat initoradb.ora

oradb.__data_transfer_cache_size=0 oradb.__db_cache_size=1660944384 oradb.__inmemory_ext_roarea=0 oradb.__inmemory_ext_rwarea=0 oradb.__java_pool_size=0 oradb.__large_pool_size=16777216 oradb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment oradb.__pga_aggregate_target=822083584 oradb.__sga_target=2432696320 oradb.__shared_io_pool_size=117440512 oradb.__shared_pool_size=587202560 oradb.__streams_pool_size=33554432 oradb.__unified_pga_pool_size=0 # add parameter for data guard setting oradb.log_archive_config='dg_config=(oradb,stby)' oradb.log_archive_dest_2='SERVICE=STBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=STBY' oradb.fal_client='oradb' oradb.fal_server='stby' *.audit_file_dest='/u01/app/oracle/admin/oradb/adump' *.audit_trail='db' *.compatible='19.0.0' *.control_files='+DATA01/ORADB/CONTROLFILE/current.297.1047004077','+FRA/ORADB/CONTROLFILE/current.276.1047004077'#Restore Controlfile *.db_block_size=8192 *.db_create_file_dest='+DATA01' *.db_name='oradb' *.db_recovery_file_dest_size=107374182400 *.db_recovery_file_dest='+FRA' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=oradbXDB)' *.open_cursors=300 *.pga_aggregate_target=771m *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=2312m *.undo_tablespace='UNDOTBS1' # add parameter for data guard setting *.log_archive_dest_1='location=+FRA/ORADB/ARCHIVELOG mandatory' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' *.standby_file_management='auto'

 

$ cat initstby.ora

stby.__data_transfer_cache_size=0 stby.__db_cache_size=1660944384 stby.__inmemory_ext_roarea=0 stby.__inmemory_ext_rwarea=0 stby.__java_pool_size=0 stby.__large_pool_size=16777216 stby.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment stby.__pga_aggregate_target=822083584 stby.__sga_target=2432696320 stby.__shared_io_pool_size=117440512 stby.__shared_pool_size=587202560 stby.__streams_pool_size=33554432 stby.__unified_pga_pool_size=0 # add parameter for data guard setting stby.log_archive_config='dg_config=(oradb,stby)' stby.log_archive_dest_2='SERVICE=ORADB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORADB' stby.fal_client='oradb' stby.fal_server='stby' *.audit_file_dest='/u01/app/oracle/admin/stby/adump' *.audit_trail='db' *.compatible='19.0.0' *.control_files='+DATA01/STBY/CONTROLFILE/current.297.1047004077','+FRA/ASM/CONTROLFILE/current.276.1047004077'#Restore Controlfile *.db_block_size=8192 *.db_create_file_dest='+DATA01' *.db_name='oradb' *.db_recovery_file_dest_size=107374182400 *.db_recovery_file_dest='+FRA' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=stbyXDB)' *.open_cursors=300 *.pga_aggregate_target=771m *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=2312m *.undo_tablespace='UNDOTBS1' # add parameter for data guard setting *.log_archive_dest_1='location=+FRA/STBY/ARCHIVELOG mandatory' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' *.standby_file_management='auto'

 

# pre db 복제
// standby server
$ cat listener.ora

STBY =      (DESCRITIOPN_LIST =          (DESCRIPTION =             (ADDRESS = (PROTOCOL = TCP)(HOST = orcl19c2)(PORT = 1577))         )     ) SID_LIST_STBY =     (SID_LIST =         (SID_DESC =             (GLOBAL_DBNAME = stby)             (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1)             (SID_NAME = stby)         )     )

$ lsnrctl start STBY

$ sqlplus / as sysdba

SQL> startup nomount pfile='?/dbs/initstby.ora';

// primary server

$ cat tnsnames.ora

// tnsnames.ora에 standby server 등록 STBY =     (DESCRIPTION =         (ADDRESS = (PROTOCOL = TCP)(HOST = orcl19c2)(PORT = 1577))         (FAIL_OVER = ON)         (CONNECTD_DATA =             (SERVER = DEDICATED)             (SERVICE_NAME = STBY)             (UR = A)         )     )

// orapw 파일을 standby에 보내준다.

$ scp orapworadb oracle@orcl19c2:/u01/app/oracle/product/19.0.0/db_1/dbs

 

# db 복제
$ rman target sys/P@ssw0rd
RMAN > connect auxiliary sys/P@ssw0rd

RMAN > duplicate target database
for standby
from active database
dorecover
spfile
set db_unique_name='stby' comment 'Is standby'
set db_file_name_convert='+DATA01/ORADB/DATAFILE','+DATA01/STBY/DATAFILE/'
set log_file_name_convert='+DATA01/ORADB/ONLINEFILE','+DATA01/STBY/ONLINEFILE/'
set audit_file_dest='/u01/app/oracle/admin/stby/adump'
set log_archive_dest_1='location=+FRA/STBY/ARCHIVELOG mandatory'
set job_queue_processes='0'
nofilenamecheck;

 

SQL > select protection_mode from v$database;

 

# show process in standby database
SQL > select process,status,sequence# from v$managed_standby;

# test log transport
SQL > alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

SQL > select sequence#,first_time,next_time
from v$archived_log
order by sequence#;

SQL > alter system switch logfile;

SELECT
SWITCHOVER_STATUS
FROM v$DATABASE;

 

# logfile 생성

SQL > alter database add standby logfile thread 1
'+DATA01/STBY/ONLINELOG/rkcoredo15_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo16_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo17_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo18_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo19_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo20_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo21_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo22_lv' size 100M;

SQL > alter database add standby logfile thread 2
'+DATA01/STBY/ONLINELOG/rkcoredo23_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo24_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo25_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo26_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo27_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo28_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo29_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo30_lv' size 100M;

 

SQL > SELECT
NAME,
THREAD#,
SEQUENCE#,
TO_DATE(FIRST_TIME,'YYYY-MM-DD HH24:MI:SS') AS FST_TIME,
APPLIED
FROM
(SELECT NAME, THREAD#, SEQUENCE#,FIRST_TIME,APPLIED
FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE# DESC)
WHERE ROWNUM < 10;

 

# standby database restart
-- real-time redo apply
SQL> alter database recover managed standby database using current logfile disconnect;

# Convert primary database to standby
--primary
SQL > alter database commit to switchover to standby;
SQL > shutdown immediate;

 

# mount old primary database as standby database
--primary
SQL > startup nomount;
SQL > alter database mount standby database;
SQL > alter database recover manged standby database disconnect from session;
-- standby
SQL > alter database commit to switchover to primary;
SQL > shutdown immediate;
SQL > startup;

 

 

 
# failover (active data guard)

-- standby server
SQL > alter database recover manged standby database finish;
SQL > alter database active standby database;

# db 복제
$ rman target sys/P@ssw0rd
RMAN > connect auxiliary sys/P@ssw0rd

duplicate target database
for standby
from active database
dorecover
spfile
set db_unique_name='stby' comment 'Is standby'
set db_file_name_convert='+DATA01/ORADB/DATAFILE','+DATA01/STBY/DATAFILE/'
set log_file_name_convert='+DATA01/ORADB/ONLINEFILE','+DATA01/STBY/ONLINEFILE/'
set audit_file_dest='/u01/app/oracle/admin/stby/adump'
set log_archive_dest_1='location=+FRA/STBY/ARCHIVELOG mandatory'
set job_queue_processes='0'
nofilenamecheck;

 

select protection_mode from v$database;

# show process in standby database
select process,status,sequence# from v$managed_standby;

# test log transport
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

select sequence#,first_time,next_time
from v$archived_log
order by sequence#;

alter system switch logfile;

SELECT
SWITCHOVER_STATUS,
DATAGUARD_BROKER,
GUARD_STATUS
FROM v$DATABASE;

# logfile 생성

alter database add standby logfile thread 1
'+DATA01/STBY/ONLINELOG/rkcoredo15_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo16_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo17_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo18_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo19_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo20_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo21_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo22_lv' size 100M;

alter database add standby logfile thread 2
'+DATA01/STBY/ONLINELOG/rkcoredo23_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo24_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo25_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo26_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo27_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo28_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo29_lv' size 100M,
'+DATA01/STBY/ONLINELOG/rkcoredo30_lv' size 100M;

 

SELECT
NAME,
THREAD#,
SEQUENCE#,
TO_DATE(FIRST_TIME,'YYYY-MM-DD HH24:MI:SS') AS FST_TIME,
APPLIED
FROM
(SELECT NAME, THREAD#, SEQUENCE#,FIRST_TIME,APPLIED
FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE# DESC)
WHERE ROWNUM < 10;

# standby database restart
-- real-time redo apply
SQL> alter database recover managed standby database using current logfile disconnect;

# Convert primary database to standby
--primary
alter database commit to switchover to standby;
shutdown immediate;

# mount old primary database as standby database
--primary
startup nomount;
alter database mount standby database;
alter database recover manged standby database disconnect from session;
-- standby
alter database commit to switchover to primary;
shutdown immediate;
startup;

 

 

# failover
alter database recover manged standby database finish;
alter database active standby database;

 

MORE DETAIL > http://wiki.gurubee.net/display/CORE/19.+DATA+GUARD+11G