1. primary database (ORADB01)
2. standby database (ORADB01)
KONFIGURASI DI PRIMARY (ORADB01) :
listener.ora
--------------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/apps/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER_LDG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db01.com)(PORT = 1521))
)
)
)
tnsnames.ora
LISTENER_LDG =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db01.com)(PORT = 1521))
)
ORADB02 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db02.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORADB02)
)
)
ORADB01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db01.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORADB01)
)
)
INIT.ORA
------------
log_archive_dest_1='LOCATION=/oracle/apps/archive VALID_FOR=(ALL_LOGFILES,ALL_RO
LES) DB_UNIQUE_NAME=ORADB01'
log_archive_dest_2='SERVICE=ORADB02 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMAR
Y_ROLE) DB_UNIQUE_NAME=ORADB02'
log_archive_dest_state_1=enable
log_archive_dest_state_2=defer
log_archive_max_processes=4
fal_server=ORADB02
fal_client=ORADB01
standby_file_management=auto
db_name=ORADB01
db_unique_name=ORADB01
KONFIGURASI DI STANDBY (ORADB02) :
listener.ora
--------------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/apps/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER_STDBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db02.com)(PORT = 1521))
)
)
)
tnsnames.ora
-----------------
LISTENER_STDBY =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db02.com)(PORT = 1521))
)
ORADB02 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db02.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORADB02)
)
)
ORADB01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db01.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORADB01)
)
)
init_parameter_standby :
-----------------------------
log_archive_dest_1='LOCATION=/oracle/apps/archive VALID_FOR=(ALL_LOGFILES,ALL_RO
LES) DB_UNIQUE_NAME=ORADB02'
log_archive_dest_2='SERVICE=ORADB01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMAR
Y_ROLE) DB_UNIQUE_NAME=ORADB01'
log_archive_dest_state_1=enable
log_archive_dest_state_2=defer
log_archive_max_processes=4
fal_server=ORADB01
fal_client=ORADB02
standby_file_management=auto
db_name=ORADB01
db_unique_name=ORADB02
1. persiapan untuk membaut standby database di ORADB02
@primary
jadikan archive log mode
SQL> alter database force logging;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
tambahkan standby log file
alter database add standby logfile group 4 ('/oracle/apps/oradata/ORADB01/stdby_redo04.log') size 52428800;
alter database add standby logfile group 5 ('/oracle/apps/oradata/ORADB01/stdby_redo05.log') size 52428800;
alter database add standby logfile group 6 ('/oracle/apps/oradata/ORADB01/stdby_redo06.log') size 52428800;
alter database add standby logfile group 7 ('/oracle/apps/oradata/ORADB01/stdby_redo07.log') size 52428800;
backup untuk standby dengan menggunakan rman :
a. rman target=/
b. backup full database format '/oracle/apps/flash_recovery_area/%d_%U.bckp' plus archivelog format
'/oracle/apps/flash_recovery_area/%d_%U.bckp';
c. CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/oracle/apps/flash_recovery_area/%U';
d. BACKUP CURRENT CONTROLFILE FOR STANDBY;
copykan hasil backup ke standby dengan lokasi yang sama
scp /oracle/apps/flash_refovery_area/* oracle@192.168.3.122:/oracle/apps/flash_refovery_area/
@standby database
1. buat orapwd
2. jalankan pfile dengan kondisi nomount
3. jalankan listener
4. restore clone rman dengan script berikut :
5. rman target=sys/oracle@oradb01 auxiliary=/
DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;
6. create standby logfile :
alter database add standby logfile group 4 ('/oracle/apps/oradata/ORADB01/stdby_redo04.log') size
52428800;
alter database add standby logfile group 5 ('/oracle/apps/oradata/ORADB01/stdby_redo05.log') size
52428800;
alter database add standby logfile group 6 ('/oracle/apps/oradata/ORADB01/stdby_redo06.log') size
52428800;
alter database add standby logfile group 7 ('/oracle/apps/oradata/ORADB01/stdby_redo07.log') size
52428800;
7. alter database recover managed standby database using current log file disconnect from sessions
perpare for logical standby database :
===========================
@primary
------------
tambahkan ubah dan tambahkan parameter berikut
SQL> alter system set log_archive_dest_3='LOCATION=/oracle/apps/archive2/ valid_for=(standby_logfiles,standby_role) db_unique_name=DBORA01' scope=both;
SQL> alter system set log_archive_dest_1='LOCATION=/oracle/apps/archive/ valid_for=(online_logfiles,all_roles) db_unique_name=DBORA01' scope=BOTH;
SQL> alter system set log_archive_dest_state_3=enable scope=both;
@standby
------------
SQL> alter system set log_archive_dest_3='LOCATION=/oracle/apps/archive2/ valid_for=(standby_logfiles,standby_role) db_unique_name=DBORA02' scope=both;
SQL> alter system set log_archive_dest_1='LOCATION=/oracle/apps/archive/ valid_for=(online_logfiles,all_roles) db_unique_name=DBORA02' scope=BOTH;
SQL> alter system set log_archive_dest_state_3=enable scope=both;
@primary
-------------
jalankan log minner
EXECUTE DBMS_LOGSTDBY.BUILD;
@standby
1. alter database recover managed standby database cancel;
2. mengganti db_name dan dbid yang baru
3. alter database recover to logical standby ORADB02;
4. shutdown immediate
5. startup mount
6. alter database open resetlogs
7. alter database start logical standby apply immediate; (jalankan process apply)
perhatikan alert nya di kedua database
untuk monitoring dapat menggunakan script di bawah ini :
SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS
ORDER BY EVENT_TIMESTAMP, COMMIT_SCN;
SELECT APPLIED_SCN, LATEST_SCN, MINING_SCN, RESTART_SCN FROM V$LOGSTDBY_PROGRESS;
SELECT APPLIED_TIME, LATEST_TIME, MINING_TIME, RESTART_TIME FROM V$LOGSTDBY_PROGRESS;
SELECT * FROM V$LOGSTDBY_STATS;
SELECT SESSION_ID, STATE FROM V$LOGSTDBY_STATE;
kemudian lakukan perubahan-perubahan di primary dan lihat hasilnya di standby
pastikan bahwa standby dan primary dalam status open mode read_write :
HANDLER :
skip transaksi untuk table tertentu
EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'DML', schema_name => 'SCOTT', object_name => 'TESTLOGICAL');
to be continued .... for handler
No comments:
Post a Comment