LaGiPusHinGWitH ---- IT
Navigation
Home |
Categories
Monthly Archives
- September 2010
- October 2009
- December 2008
- November 2008
- October 2008
- September 2008
- July 2008
- May 2008
- March 2008
- December 2007
- October 2007
- September 2007
- August 2007
Most recent entries
- Create Oracle Tablespace on SAP with BRTools
- Audit on Oracle Database
- Membuat Oracle Data Guard
- Cerita Tentang Kamu
- Membuat ulang/re-create controlfile di oracle
- Membuat RMAN dengan crontab
- Saat kau hadir kembali
- Memindahkan controlfile dan redologfile pada Oracle
- Oracle Dataguard on Standart Edition
- Adakah aku dihatimu
- Instalasi 10g di HP-UX Itanium
- Instalasi OpenOffice di Slackware 12
- Syncronize archive log
- Merubah ukuran archived Log
- Alter Block Size for tablespace
Syndicate
Site Credits
Powered by:
ExpressionEngine
Design by:
BlogMoxie
Seperti yang saya janjikan, bahwa setelah menulis artikel pembuatan Data Guard pada Oracle Standart Edition.. saya
akan menulis artikel cara membuat Oracle Data Guard di Enterprise Edition, kali ini saya akan akan membuat
Physical Standby Database.
(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 -r oracle@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 ~]$ scp oracle@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 ~]$ scp oracle@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 ~]$ scp oracle@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 ~]$ scp oracle@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.
Computer DataBase • (0) Comments • (735) Trackbacks • Permalink