Membuat Oracle Data Guard


(Data Guard memiliki 2 buah metode : Physical Standby Database dan Logical Standby Database).

Skenario :

Ada 2 buah server Database, dimana 1 sebagai primary database dan 1 sebagai standby database

1. Versi database: Oracle Database 10g Enterprise Edition Release 2
2. OS: Oracle Linux Enterprise 5

Primary Database
—————-
1. IP -> 192.168.58.10
2. DB_UNIQUE_NAME -> TRAINING
3. SERVICE_NAME -> TRAINING

Standby Database
—————-
1. IP -> 192.168.58.20
2. DB_UNIQUE_NAME -> TRAINING
3. SERVICE_NAME -> TRAINSBY

Dan standby database berfungsi sebagai server database cadangan

Persiapan – persiapan yang perlu dilakukan :

=== PRIMARY DATABASE ===

1. Apply force logging

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

2. Mengaktifkan mode archived log

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 5
Current log sequence 7

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 444596224 bytes
Fixed Size 1219880 bytes
Variable Size 163578584 bytes
Database Buffers 272629760 bytes
Redo Buffers 7168000 bytes
Database mounted.

SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

3. Membuat password file

[oracle@gunawan ~]$ cd $ORACLE_HOME/dbs
[oracle@gunawan dbs]$ orapwd file=orapwTRAINING password=oracle entries=10 force=y
[oracle@gunawan dbs]$ ls -al
total 112
drwxr-x— 2 oracle dba 4096 Dec 11 20:38 .
drwxr-x— 55 oracle dba 4096 Dec 9 20:23 ..
-rw-r–r– 1 oracle dba 5975 Dec 9 21:13 control01.txt
-rw-rw—- 1 oracle dba 1544 Dec 4 01:00 hc_TRAINING.dat
-rw-r—– 1 oracle dba 12920 May 3 2001 initdw.ora
-rw-r—– 1 oracle dba 8385 Sep 11 1998 init.ora
-rw-rw—- 1 oracle dba 24 Dec 4 01:01 lkTRAINING
-rw-r—– 1 oracle dba 1536 Dec 4 01:03 orapwTRAINING
-rw-r—– 1 oracle dba 3584 Dec 11 20:38 spfileTRAINING.ora
-rw-r–r– 1 oracle dba 1056 Dec 7 21:17 spfileTRAINSBY.txt
-rw-r–r– 1 oracle dba 719 Dec 7 21:34 sqlnet.log

4. Mengecek semua parameter yang akan dibutuhkan dalam pembuatan standby database

SQL> sho parameter log_file_name_convert;

NAME TYPE VALUE
———————————— ———– ——————————
log_file_name_convert string

SQL> sho parameter remote_login_passwordfile;

NAME TYPE VALUE
———————————— ———– ——————————
remote_login_passwordfile string EXCLUSIVE

SQL> sho parameter log_archive_config;

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_config string

SQL> sho parameter log_archive_dest_1;

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_1 string
log_archive_dest_10 string

SQL> sho parameter log_archive_dest_state_1;

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable

SQL> sho parameter log_archive_dest_state_2;

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_state_2 string enable

SQL> sho parameter log_archive_format;

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_format string %t_%s_%r.dbf

SQL> sho parameter fal_client;

NAME TYPE VALUE
———————————— ———– ——————————
fal_client string
SQL> sho parameter fal_server;

NAME TYPE VALUE
———————————— ———– ——————————
fal_server string

SQL> sho parameter log_archive_dest_2;

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_2 string

5. Buat backup spfile (usahakan untuk selalu membuat backup sebelum melakukan perubahan pada parameter-parameter
primary database

SQL> create pfile=’/oracle/product/10.2.0/training/dbs/init_TRAINING.txt’ from spfile;

File created.

6. Lakukan perubahan pada parameter database

SQL> alter system set log_file_name_convert=’/home/oracle/training/redo’,’home/oracle/training/redo’ scope=spfile;

System altered.

SQL> alter system set db_file_name_convert=’/data/training/files’,’/data/training/files’ scope=spfile;

System altered.

SQL> alter system set log_archive_config=’DG_CONFIG=(training,trainsby)’ scope=spfile;

System altered.

SQL> alter system set log_archive_dest_1=’LOCATION=/data/training/archive’ scope=spfile;

System altered.

SQL> alter system set log_archive_dest_state_2=’defer’ scope=spfile;

System altered.

SQL> alter system set log_archive_format=’%t_%s_%r.arc’ scope=spfile;

System altered.

SQL> alter system set fal_client=’training’ scope=spfile;

System altered.

SQL> alter system set fal_server=’trainsby’ scope=spfile;

System altered.

SQL> alter system set log_archive_dest_2=’service=trainsby optional LGWR ASYNC NOAFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=training’ scope=spfile;

System altered.

<<< Restart database >>>

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 444596224 bytes
Fixed Size 1219880 bytes
Variable Size 167772888 bytes
Database Buffers 268435456 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.

7. Buat pfile dari spfile terbaru yang akan digunakan untuk standby database
(setelah restart database maka spfile sudah berubah)

SQL> create pfile=’/home/oracle/pfile_training.txt’ from spfile;

File created.

8. Edit/buat file tnsnames.ora

[oracle@gunawan ~]$ cd $ORACLE_HOME/network/admin/
[oracle@gunawan admin]$ vi tnsnames.ora

TRAINING =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.58.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TRAINING)
)
)

TRAINSBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.58.20)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TRAINING)
)
)

9. Buat standby control file dari primary database

SQL> alter database create standby controlfile as ‘/home/oracle/controlstby.ctl’;

Database altered.

10. Buat backup database secara online

SQL> select TABLESPACE_NAME from dba_tablespaces where CONTENTS <>’TEMPORARY’;

TABLESPACE_NAME
——————————
SYSTEM
UNDOTBS1
SYSAUX
USERS

SQL> alter tablespace SYSTEM begin backup;

Tablespace altered.

SQL> alter tablespace UNDOTBS1 begin backup;

Tablespace altered.

SQL> alter tablespace SYSAUX begin backup;

Tablespace altered.

SQL> alter tablespace USERS begin backup;

Tablespace altered.

SQL> select name as file_name from
(select name from v$tempfile union
select name from v$datafile union
select member as name from v$logfile)
order by file_name;

FILE_NAME
——————————————————————————–
/data/training/files/sysaux01.dbf
/data/training/files/system01.dbf
/data/training/files/temp01.dbf
/data/training/files/undotbs01.dbf
/data/training/files/users01.dbf
/home/oracle/training/redo/redo01a.log
/home/oracle/training/redo/redo01b.log
/home/oracle/training/redo/redo02a.log
/home/oracle/training/redo/redo02b.log
/home/oracle/training/redo/redo03a.log
/home/oracle/training/redo/redo03b.log

11 rows selected.

====== STANDBY ========

11. Copy semua datafile dan redolog yang telah dibackup
(saya melakukan proses peng-copy-an dari standby database

[oracle@gunawan ~]$ scp -r oracle@192.168.58.10:/data/training/files/ /data/training/
oracle@192.168.58.10’s password:
users01.dbf 100% 5128KB 5.0MB/s 00:01
undotbs01.dbf 100% 25MB 2.5MB/s 00:10
system01.dbf 100% 480MB 3.5MB/s 02:17
temp01.dbf 100% 20MB 4.0MB/s 00:05
sysaux01.dbf 100% 240MB 2.4MB/s 01:39
[oracle@gunawan ~]$

[oracle@gunawan ~]$ scp -roracle@192.168.58.10:/home/oracle/training/redo/ /home/oracle/training/
oracle@192.168.58.10’s password:
redo02a.log 100% 50MB 7.1MB/s 00:07
redo01a.log 100% 50MB 5.6MB/s 00:09
redo01b.log 100% 50MB 7.1MB/s 00:07
redo03a.log 100% 50MB 6.3MB/s 00:08
redo03b.log 100% 50MB 6.3MB/s 00:08
redo02b.log 100% 50MB 6.3MB/s 00:08
[oracle@gunawan ~]$

[oracle@gunawan ~]$ scporacle@192.168.58.10:/home/oracle/controlstby.ctl /home/oracle/
oracle@192.168.58.10’s password:
controlstby.ctl 100% 6896KB 6.7MB/s 00:01
[oracle@gunawan ~]$ scporacle@192.168.58.10:/home/oracle/pfile_training.txt /home/oracle/
oracle@192.168.58.10’s password:
pfile_training.txt 100% 1567 1.5KB/s 00:00
[oracle@gunawan ~]$

[oracle@gunawan ~]$ scporacle@192.168.58.10:/oracle/product/10.2.0/training/dbs/orapwTRAINING /oracle/product/10.2.0/training/dbs/
oracle@192.168.58.10’s password:
orapwTRAINING 100% 2560 2.5KB/s 00:00

[oracle@gunawan ~]$ scporacle@192.168.58.10:/oracle/product/10.2.0/training/network/admin/tnsnames.ora /oracle/product/10.2.0/training/network/admin
oracle@192.168.58.10’s password:

===== PRIMARY ======

setelah backup selesai maka akhiri mode backup pada tablespace di primary database

SQL> alter tablespace SYSTEM end backup;

Tablespace altered.

SQL> alter tablespace UNDOTBS1 end backup;

Tablespace altered.

SQL> alter tablespace SYSAUX end backup;

Tablespace altered.

SQL> alter tablespace USERS end backup;

Tablespace altered.

====== STANDBY ======

12. Copy standby controlfile menjadi 3 buah

[oracle@gunawan ~]$ pwd
/home/oracle
[oracle@gunawan ~]$ cp -rp controlstby.ctl control01.ctl
[oracle@gunawan ~]$ cp -rp controlstby.ctl control02.ctl
[oracle@gunawan ~]$ cp -rp controlstby.ctl control03.ctl
[oracle@gunawan ~]$ mv control0* /home/oracle/training/control/

13. Edit file pfile_training.txt

log_archive_dest_2=’service=training optional LGWR ASYNC NOAFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=training’

14. Buat direktory untuk file-file dump

$ mkdir /oracle/product/admin/training/adump
$ mkdir /oracle/product/admin/training/bdump
$ mkdir /oracle/product/admin/training/cdump
$ mkdir /oracle/product/admin/training/udump

15. Naikkan standby database menggunakan pfile

SQL> startup mount pfile=’/home/oracle/pfile_training.txt’;
ORACLE instance started.

Total System Global Area 444596224 bytes
Fixed Size 1219880 bytes
Variable Size 167772888 bytes
Database Buffers 268435456 bytes
Redo Buffers 7168000 bytes
Database mounted.

16. Rubah parameter service_names

SQL> sho parameter SERVICE_NAMES;

NAME TYPE VALUE
———————————— ———– ——————————
service_names string TRAINING

<<< Untuk merubah parameter database harus dinaikkan menggunakan spfile >>>

SQL> create spfile from pfile=’/home/oracle/pfile_training.txt’;

File created.

<<< Restart Database >>>

SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 444596224 bytes
Fixed Size 1219880 bytes
Variable Size 167772888 bytes
Database Buffers 268435456 bytes
Redo Buffers 7168000 bytes
Database mounted.

SQL> alter system set SERVICE_NAMES=’TRAINSBY’ scope=spfile;

System altered.

<<< Restart Database >>>

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 444596224 bytes
Fixed Size 1219880 bytes
Variable Size 167772888 bytes
Database Buffers 268435456 bytes
Redo Buffers 7168000 bytes
Database mounted.

SQL> sho parameter SERVICE_NAMES;

NAME TYPE VALUE
———————————— ———– ——————————
service_names string TRAINSBY

17. Jalankan recovery di standby database untuk meng-apply arhived log

SQL> alter database recover managed standby database disconnect;

Database altered.

========= PRIMARY ======

18. Rubah parameter log_archive_dest_state_2 dari defer menjadi enable di primary database

SQL> alter system set log_archive_dest_state_2=’enable’ scope=both;

System altered.

SQL> sho parameter log_archive_dest_state_2;

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_state_2 string enable

Akhirnya standby database selesai di-create. Untuk melihat archived log yang telah di-apply di standby database
gunakan command ini.

SQL> set pages 100
SQL> col name for a45
SQL> select name,to_char(FIRST_TIME,’dd-mon-yy hh24:mi:ss’) TIME ,SEQUENCE#,APPLIED from v$archived_log;

Pastikan colomn APPLIED bernilai YES.

NAME TIME SEQUENCE# APP
——————————————— —————— ———- —
/data/training/archive/1_7_672541313.arc 11-dec-08 20:36:03 7 YES
/data/training/archive/1_8_672541313.arc 12-dec-08 17:29:07 8 YES
/data/training/archive/1_9_672541313.arc 12-dec-08 18:02:31 9 YES
/data/training/archive/1_10_672541313.arc 12-dec-08 18:06:36 10 YES
/data/training/archive/1_11_672541313.arc 12-dec-08 18:11:41 11 YES
/data/training/archive/1_12_672541313.arc 12-dec-08 18:15:23 12 YES
/data/training/archive/1_13_672541313.arc 12-dec-08 18:18:07 13 YES
/data/training/archive/1_14_672541313.arc 12-dec-08 18:27:07 14 YES
/data/training/archive/1_15_672541313.arc 12-dec-08 18:29:38 15 YES
/data/training/archive/1_16_672541313.arc 12-dec-08 18:32:03 16 YES
/data/training/archive/1_17_672541313.arc 12-dec-08 18:35:39 17 YES
/data/training/archive/1_18_672541313.arc 12-dec-08 18:36:25 18 YES
/data/training/archive/1_19_672541313.arc 12-dec-08 18:56:36 19 YES

13 rows selected.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s