Implementation of Oracle Data Guard 11g

I. Keterangan Umum

server

home primary database = ORADEV1-DB (20.138.3.61)

home standby database = ORADEV2-DB (20.138.3.63)

database unique name

db unique name primary database = oradb

db unique name primary database = oradrc

Note :

Khusus untuk instance atau service name dibuat sama yaitu oradb dengan tujuan untuk digunakan pada saat koneksi di sisi aplikasi agar transparan dimana ketika terjadi failover atau switchover dari sisi database (primary database ke standby database) maka tidak diperlikukan kembali konfigurasi dari sisi client. Cukup dengan menggunakan connection string sebagai berikut :

ORADEV =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ORADEV1-DB)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = ORADEV2-DB)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = oradb)

)

)

II. Konfigurasi Koneksi

ORADEV1-DB

a. konfigurasi listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = oradb)

(ORACLE_HOME = /oracle/product/orahome/11.1.0/db_1)

(SID_NAME = oradb)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ORADEV-DB)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

b. configure tnsnames.ora

ORADB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ORADEV1-DB)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = oradb)

)

)

ORADRC =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ORADEV2-DB)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = oradb)

)

)

ORADEV2-DB

a. configure listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = oradb)

(ORACLE_HOME = /oracle/product/orahome/11.1.0/db_1)

(SID_NAME = oradb)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ORADEV-DB)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

b. configure tnsnames.ora

ORADB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ORADEV1-DB)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = oradb)

)

)

ORADRC =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ORADEV2-DB)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = oradb)

)

)

III. Inisialisasi instance standby database

ORADEV1-DB

a. backup spfile to pfile:

SQL> create pfile='/oracle/product/orahome/11.1.0/db_1/dbs/pfileora.ora' from spfile;

b. copy file password orapworadb database dari primary ke standby

ORADEV2-DB

a. create inittemp di ORADV2-DB

/oracle/product/orahome/11.1.0/db_1/dbs/inittemp.ora

bash-3.00$ cat inittemp.ora

b. dengan isi dari init temporary nya adalah

db_name=oradb

service_names=oradb

db_unique_name=oradrc

c. naikan instance standby database dengan status nomount state

SQL> startup pfile='/oracle/product/orahome/11.1.0/db_1/dbs/inittemp.ora' nomount;

Note : menyesuaikan mountpoint antara ORADDEV1-DB dengan ORADEV2-DB

dimana directory disamakan baik itu directory datafile , control file maupun logfile

IV. Konfigurasi parameter untuk standby database

ORADEV1-DB

Setting parameter untuk oracle dataguard konfigurasi :

alter system set db_unique_name='oradb' scope=spfile;

Alter system set fal_client='ORADB' scope=spfile;

Alter system set fal_server='ORADRC scope=spfile;

Alter system set standby_file_management='AUTO' scope=spfile;

Alter system set log_archive_config='dg_config=(ORADRC,ORADB)' scope=spfile;

ALTER SYSTEM SET log_archive_dest_1='LOCATION=/oradata01/archive/' scope=spfile;

ALTER SYSTEM SET log_archive_dest_state_1='ENABLE'scope=spfile;

ALTER SYSTEM SET log_archive_dest_2='SERVICE=ORADRC ARCH ASYNC DB_UNIQUE_NAME=ORADRC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)' scope=spfile;

ALTER SYSTEM SET log_archive_dest_state_2= 'DEFER' scope=spfile;

alter system set log_archive_format='log_%s_%t_%r.arc' scope=spfile;

alter system set log_file_name_convert='/oradata01/redolog','/oradata01/redolog',

'/oradata02/redolog','/oradata02/redolog',

'/oradata03/redolog','/oradata03/redolog',

'/oradata04/redolog','/oradata04/redolog' scope=spfile;

Create logfile standby database :

Split logfile di semua mountpoint dan dibuat roundrobin pada saat database mengalami switchlog dengan menggunakan script sebagai berikut ;

alter database add standby logfile group 5 '/oradata04/redolog/redo_stdby01.log' size 209715200;

alter database add standby logfile group 6 '/oradata03/redolog/redo_stdby02.log' size 209715200;

alter database add standby logfile group 7 '/oradata02/redolog/redo_stdby03.log' size 209715200;

alter database add standby logfile group 8 '/oradata01/redolog/redo_stdby04.log' size 209715200;

alter database add standby logfile group 9 '/oradata04/redolog/redo_stdby05.log' size 209715200;

V. Cloning database standby dengan menggunakan rman

ORADEV1-DB

Untuk cloning rman dapat melakukan secara online dengan transfer semua komponen database melalui network.

bash$ rman

rman > connect target sys/oracle@ORADB

rman > connect auxiliary sys/oracle@ORADRC

rman > RUN

{

ALLOCATE CHANNEL d1 TYPE DISK;

ALLOCATE CHANNEL d2 TYPE DISK;

ALLOCATE AUXILIARY CHANNEL cnv1 TYPE DISK;

ALLOCATE AUXILIARY CHANNEL cnv2 TYPE DISK;

DUPLICATE TARGET DATABASE

FOR STANDBY

FROM ACTIVE DATABASE

DORECOVER

SPFILE

SET db_unique_name='oradrc'

SET service_names='oradb'

SET control_files='/oradata03/control_file/stdby_ctl01.ctl'

SET log_archive_dest_1='LOCATION=/oradata01/archive/'

SET log_archive_dest_state_1='ENABLE'

SET log_archive_dest_2='SERVICE=ORADB ARCH ASYNC DB_UNIQUE_NAME=ORADB VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)'

SET log_archive_dest_state_2 = 'DEFER'

SET log_archive_format='log_%s_%t_%r.arc'

SET fal_client='ORADRC'

SET fal_server='ORADB'

SET standby_file_management='AUTO'

SET log_file_name_convert='/oradata01/redolog','/oradata01/redolog',

'/oradata02/redolog','/oradata02/redolog',

'/oradata03/redolog','/oradata03/redolog',

'/oradata04/redolog','/oradata04/redolog'

SET log_archive_config='dg_config=(ORADB,ORADRC)'

NOFILENAMECHECK;

}

ORADEV2-DB

1. Aktifkan recover standby database

Sql > alter database recover managed standby database disconnect from session;

2. Check status database

col DB_UNIQUE_NAME for a15

col flashback_on for a14

select DB_UNIQUE_NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,FLASHBACK_ON from v$database;

DB_UNIQUE_NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE FLASHBACK_ON

--------------- ---------- -------------------- ---------------- --------------

oradrc MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY NO

3. Check status MRP

SQL> select process, status from v$managed_standby;

PROCESS STATUS

--------- ------------

ARCH CLOSING

ARCH CLOSING

ARCH CLOSING

ARCH CLOSING

RFS IDLE

MRP0 WAIT_FOR_GAP

RFS IDLE

7 rows selected.

4. Check Gap

SQL> select * from v$archive_gap;

no rows selected

5. check applied archive

check :

SQL> col name for a50

SQL> /

NAME S APP

-------------------------------------------------- - ---

/oradata01/archive/log_6_1_690475917.arc A YES

/oradata01/archive/log_7_1_690475917.arc A YES

/oradata01/archive/log_8_1_690475917.arc A YES

/oradata01/archive/log_9_1_690475917.arc A YES

/oradata01/archive/log_10_1_690475917.arc A YES

/oradata01/archive/log_11_1_690475917.arc A YES

/oradata01/archive/log_12_1_690475917.arc A YES

/oradata01/archive/log_13_1_690475917.arc A YES

ORADB A YES

/oradata01/archive/log_14_1_690475917.arc A YES

ORADB A YES

/oradata01/archive/log_15_1_690475917.arc A YES

ORADB A YES

/oradata01/archive/log_16_1_690475917.arc A YES

ORADB A NO

/oradata01/archive/log_17_1_690475917.arc A YES

ORADB A NO

/oradata01/archive/log_18_1_690475917.arc A YES

/oradata01/archive/log_19_1_690475917.arc A YES

/oradata01/archive/log_20_1_690475917.arc A YES

/oradata01/archive/log_21_1_690475917.arc A YES

21 rows selected.

6. check sequence

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /oradata01/archive/

Oldest online log sequence 19

Next log sequence to archive 0

Current log sequence 22

ORADEV1-DB

1. check status pengiriman (shipping) archive yang sudah dicreate oleh primary

col destination format a25

col error format a20

select dest_id, destination, status, error from v$archive_dest;

DEST_ID DESTINATION STATUS ERROR

---------- ------------------------- --------- --------------------

1 /oradata01/archive/ VALID

2 ORADRC VALID

3 INACTIVE

4 INACTIVE

5 INACTIVE

6 INACTIVE

7 INACTIVE

8 INACTIVE

9 INACTIVE

10 INACTIVE

dari hasil query di atas maka status oke

2. check sequence archive primary (nilainya sama yaitu max # 22)

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /oradata01/archive/

Oldest online log sequence 19

Next log sequence to archive 22

Current log sequence 22

VI. Kesimpulan

Standby database oradb 11g sudah selesai dan proses sinkronisasi sudah berjalan sebagaimana mestinya, tidak ada gap antara kedua database oradb dan oradrc. Maintenance standby database diperlukan untuk masalah archive. Dimana archive dapat dilakukan rutin delete archive untuk mencegah jika terjadi penuh mount point. Dengan adanya standby database 11g kita dapat menggunakan standby database untuk kepentingan reporting dengan cara mengaktifkan active dataguard.