Wednesday, August 26, 2009

Setup Oracle Stream for Schema replication between DB

Untuk membuat repikasi dengan menggunakan stream
satu schema dengan schema yang lain caranya adalah sebagai berikut , saya ambil contoh
untuk kloning schema HR  dari database PRODDB ke CLONEDB adalah sebagai berikut :
1. siapkan tablespace untuk logminer di PRODDB

CREATE TABLESPACE LOGMNRTS DATAFILE '/oradata/DB/logmnrtbs.dbf'
SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED;
 
2. jalankan logminer
BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNRTS');

END;

3. supplement logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (primary key,
unique, foreign key) COLUMNS;
ALTER TABLE apps_a.emp ADD SUPPLEMENTAL LOG GROUP
pk_emp (id) ALWAYS;

4. export schema HR dari PRODDB ke CLONEDB
5. create user stream admin di kedua database (PRODDB dan CLONEDB)
    create user STRMADMIN identified by STRMADMIN;
    grant CONNECT, DBA, IMP_FULL_DATABASE,EXP_FULL_DATABASE to "STRMADMIN";
    exec DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');
6. login sebagai strmadmin di PRODDB
    buat database ke user strmadmin di CLONEDB
7. configure apply process di CLONEDB
8. configure capture process di PRODDB
9. instantiate







CLONEDB (apply process) login dengan user strmadmin
-----------------------------


BEGIN

DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table ='"STREAMS_APPLY_QT"',
queue_name ='"STREAMS_APPLY_Q"',
queue_user ='"STRMADMIN"');
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name ='"HR"',
streams_type ='apply',
streams_name ='"STREAMS_APPLY"',
queue_name ='"STRMADMIN"."STREAMS_APPLY_Q"',
include_dml =true,
include_ddl =true,
include_tagged_lcr = false,
inclusion_rule = true);
END;
/

BEGIN
DBMS_APPLY_ADM.ALTER_APPLY
(
apply_name = 'STREAMS_APPLY',
apply_user = 'HR'
);
END;

COMMIT;

Jika kita tidak menginginkan apply proses di abort setiap kali error,
harus ada penanganan agar prosess apply tetap berjalan walaupun ada error,

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER
( apply_name ='STREAMS_APPLY',
  parameter ='DISABLE_ON_ERROR',
  value ='N' );
END;

start apply
BEGIN
DBMS_APPLY_ADM.START_APPLY
(
apply_name ='STREAMS_APPLY'
);
END;

stop apply
BEGIN
DBMS_APPLY_ADM.STOP_APPLY
(
apply_name = 'STREAMS_APPLY'
);
END;

PRODDB (capture process) login dengan user strmadmin
-----------------------------
BEGIN

DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => '"STREAMS_CAPTURE_QT"',
queue_name => '"STREAMS_CAPTURE_Q"',
queue_user => '"STRMADMIN"');
END;

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => '"HR"',
streams_type => 'capture',
streams_name => '"STREAMS_CAPTURE"',
queue_name => '"STRMADMIN"."STREAMS_CAPTURE_Q"',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
inclusion_rule => true);
END;

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => '"HR"',
streams_name => '"STREAMS_PROPAGATION"',
source_queue_name => '"STRMADMIN"."STREAMS_CAPTURE_Q"',
destination_queue_name => '"STRMADMIN"."STREAMS_APPLY_Q"@STRM',
include_dml => true,
include_ddl => true,
inclusion_rule => true );
END;
/
COMMIT;

process instantiate scn untuk schema :
1. di PRODDB login dengan user strmadmin

BEGIN

DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER());
END;

setelah dapat nilai nya

2. di CLONEDB login dengan user strmadmin
exec DBMS_APPLY_ADM.set_schema_instantiation_scn(source_schema_name => 'HR',source_database_name => 'CLONEDB',instantiation_scn => 514740);


3. Kemudian jalankan capture process

BEGIN

DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'STREAMS_CAPTURE');
END;

BEGIN
DBMS_CAPTURE_ADM.STOP_CAPTURE(
capture_name => 'STREAMS_CAPTURE');
END;


kemudian lakukan test .... !!!
untuk monitoring proses yang terjadi di stream dengan menggunakan table-table
berikut :

v$streams_apply_reader
v$streams_apply_coordinator
v$streams_capture
dba_apply
dba_apply_error
dba_capture
dba_propagation
dba_queue_schedules

untuk memanage stream dengan menggunakan package-package di bawah :
1. dbms_apply_adm
2. dbms_capture_adm
3. dbms_streams_adm
Posted by at No comments:

Managing Logical Standby Database

Logical standby juga perlu di managed dan dimonitoring salah satu step-step nya adalah sebagai berikut :
1. untuk menjadikan standby database dalam keadaan guard

ALTER DATABASE GUARD ...... ;
mode nya terdiri dari 
    a. ALL
    b. NONE
    c. STANDBY
   
2. untuk manage transaksi
    dengan menggunakan package berikut     
    dbms_logstdby
    untuk skip transaksi
    exec DBMS_LOGSTDBY.SKIP_TRANSACTION(10,38,234);
    skip_transaction(xidusn_p IN NUMBER,
                           xidslt_p IN NUMBER,
                           xidsqn_p IN NUMBER);
    untuk melihat transaksinya dari 
    sqlplus > select * from dba_logstdby_events where current_scn is not null


3. untuk skip objects :


    exec DBMS_LOGSTDBY.SKIP('DML','SCOTT','EMP');
    exec DBMS_LOGSTDBY.SKIP('PROCEDURE', 'XYZ', '%', null); 
    exec DBMS_LOGSTDBY.SKIP('SCHEMA_DDL', 'VCS_MONITOR', '%', null);
    exec DBMS_LOGSTDBY.SKIP('DML', 'VCS_MONITOR', '%', null); 

4.sql apply biasanya secara otomatis melakukan delete archive log yang
sudah tidak terpakai, supaya tidak demikian dapat menggunakan script berikut
EXECUTE DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', FALSE);
lebih detail lagi untuk fungsi dari dbms_logstdby ada di 
 
http://www.psoug.org/reference/dbms_logstdby.html




QUERY untuk monitoring



select * from DBA_LOGSTDBY_PROGRESS
select * from DBA_LOGSTDBY_SKIP_TRANSACTION
select * from DBA_LOGSTDBY_PARAMETERS
select * from DBA_LOGSTDBY_LOG
select * from DBA_LOGSTDBY_SKIP
select * from DBA_LOGSTDBY_UNSUPPORTED
select * from DBA_LOGSTDBY_EVENTS
select * from DBA_LOGSTDBY_HISTORY



LOGSTDBY$APPLY_PROGRESS
LOGSTDBY$APPLY_MILESTONE
LOGSTDBY$SCN
LOGSTDBY$SKIP_SUPPORT
LOGSTDBY$SKIP
LOGSTDBY_SUPPORT
LOGSTDBY_UNSUPPORTED_TABLES
LOGSTDBY_LOG
V$LOGSTDBY
V$LOGSTDBY_STATS
V_$LOGSTDBY_TRANSACTION
V$LOGSTDBY_STATE
GV$LOGSTDBY

note :
pakage yang biasa untuk dilakukan manage logical
adalah dbms_logstdby
dan ada lagi yaitu DBMS_INTERNAL_LOGSTDBY (masih bingung cara pakenya)
Posted by at No comments:

Sunday, August 23, 2009

Create logical standby database 10g R2

pada kesempatan ini ane mau bagi2 pengalaman, bagi yang pernah coba ... diem aja (ke..ke..ke)
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







Posted by at No comments:
Subscribe to: Posts (Atom)