Demo : Oracle Data Guard physical standby database (2)

28 Feb

LINUX :
Linux test 2.6.18-92.el5 #1 SMP Tue Jun 10 18:51:06 EDT 2008
x86_64 x86_64 x86_64 GNU/Linux
CentOS release 5.2 (Final)
ORACLE :
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
PL/SQL Release 11.1.0.6.0 – Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 – Production
NLSRTL Version 11.1.0.6.0 – Production
ASUMSI :
server physical (SID) : prmr11g : 172.20.22.22
server standby (SID) : stby11g : 172.20.22.21

1. ubah dalam archive mode (server physical)
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 18
Current log sequence 20

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> host

[oracle@prmr11g ~]$ mkdir /u01/archive/prmr11g -p
[oracle@prmr11g ~]$ exit

SQL> alter system set log_archive_dest_1=’location=/u01/archive/prmr11g’;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archive/prmr11g
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3

2. Enable Forced Logging
SQL> alter database force logging;

3. membuat standby logfile (server physical)
dimana standby logfile = jumlah group online redo log + 1
pada contoh dibawah ini = 3 + 1 = 4

SQL> Select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
1 1 1 52428800 1 YES INACTIVE 844221 17-FEB-09
2 1 2 52428800 1 YES INACTIVE 879511 17-FEB-09
3 1 3 52428800 1 NO CURRENT 892251 17-FEB-09

SQL> select group#, member from v$logfile order by group#;
GROUP# MEMBER
——————————————————————————–
1 /u01/app/oracle/oradata/prmr11g/redo01.log
2 /u01/app/oracle/oradata/prmr11g/redo02.log
3 /u01/app/oracle/oradata/prmr11g/redo03.log

SQL> alter database add standby logfile group 4 (‘/u01/app/oracle/oradata/prmr11g/redo04.log’)
size 52428800;
SQL> alter database add standby logfile group 5 (‘/u01/app/oracle/oradata/prmr11g/redo05.log’)
size 52428800;
SQL> alter database add standby logfile group 6 (‘/u01/app/oracle/oradata/prmr11g/redo06.log’)
size 52428800;
SQL> alter database add standby logfile group 7 (‘/u01/app/oracle/oradata/prmr11g/redo07.log’)
size 52428800;

SQL> alter database open;

4. create pfile dan modifikasi (server physical)
SQL> create pfile=’/home/oracle/init_prmr11g.ora’ from spfile;
SQL> create pfile=’/home/oracle/init_stby11g.ora’ from spfile;
SQL> create pfile=’/home/oracle/init_prmr11g.ora.asli’ from spfile;
SQL> shutdown immediate;
SQL> exit

[oracle@prmr11g ~]$ scp ~/init_stby11g.ora oracle@172.20.22.21:init_stby11g.ora
[oracle@prmr11g ~]$ vim ~/init_prmr11g.ora

## Primary Role Parameters ##
db_name=’prmr11g’
db_unique_name=’prmr11g’
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(prmr11g,stby11g)’

LOG_ARCHIVE_DEST_1=
‘LOCATION=/u01/archive/prmr11g/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=prmr11g’

LOG_ARCHIVE_DEST_2=
‘SERVICE=stby11g LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=stby11g’

LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30

## Standby Role Parameters ##
*.fal_server=stby11g
*.fal_client=prmr11g
*.standby_file_management=auto
*.db_file_name_convert=’/u01/app/oracle/oradata/stby11g/’,’/u01/app/oracle/oradata/prmr11g/’
*.log_file_name_convert=’/u01/app/oracle/oradata/stby11g/’,’/u01/app/oracle/oradata/prmr11g/’

5. Menjalankan server physical dengan init yang sudah dimodifikasi (server physical)
SQL> startup nomount pfile=’/home/oracle/init_prmr11g.ora’;
SQL> create spfile from pfile=’/home/oracle/init_prmr11g.ora’;
SQL> shutdown immediate;
SQL> startup;

6. create controlfile untuk standby server (server physical)
SQL> alter database create standby controlfile as ‘/home/oracle/stby_control1.ctl’;
SQL> host
[oracle@prmr11g ~]$ scp /home/oracle/stby_control1.ctl oracle@172.20.22.21:/u01/app/oracle/oradata/stby11g/control01.ctl
[oracle@prmr11g ~]$ scp /home/oracle/stby_control1.ctl oracle@172.20.22.21:/u01/app/oracle/oradata/stby11g/control02.ctl
[oracle@prmr11g ~]$ scp /home/oracle/stby_control1.ctl oracle@172.20.22.21:/u01/app/oracle/oradata/stby11g/control03.ctl
[oracle@prmr11g ~]$ exit

7. inventarisasi data file (server physical)
SQL> select file_name from dba_data_files;
FILE_NAME
——————————————————————————–
/u01/app/oracle/oradata/prmr11g/users01.dbf
/u01/app/oracle/oradata/prmr11g/undotbs01.dbf
/u01/app/oracle/oradata/prmr11g/sysaux01.dbf
/u01/app/oracle/oradata/prmr11g/system01.dbf
/u01/app/oracle/oradata/prmr11g/example01.dbf

SQL> select file_name from dba_temp_files;
FILE_NAME
——————————————————————————–
/u01/app/oracle/oradata/prmr11g/temp01.dbf
SQL> shutdown immediate;
SQL> exit

8. Menyiapkan directory admin oracle, datafile oracle dan oratab (server standby).
a. menyiapkan folder admin oracle stby11g
[oracle@stby11g ~]$ mkdir -p /u01/app/oracle/admin/stby11g/adump
[oracle@stby11g ~]$ mkdir -p /u01/app/oracle/admin/stby11g/bdump
[oracle@stby11g ~]$ mkdir -p /u01/app/oracle/admin/stby11g/cdump
[oracle@stby11g ~]$ mkdir -p /u01/app/oracle/admin/stby11g/dpdump
[oracle@stby11g ~]$ mkdir -p /u01/app/oracle/admin/stby11g/pfile
[oracle@stby11g ~]$ mkdir -p /u01/app/oracle/admin/stby11g/scripts
[oracle@stby11g ~]$ mkdir -p /u01/app/oracle/admin/stby11g/udump
[oracle@stby11g ~]$ mkdir -p /u01/app/oracle/flash_recovery_area/stby11g/onlinelog

b. menyiapkan folder datafile dan archive oracle stby11g
[oracle@stby11g ~]$ mkdir -p /u01/app/oracle/oradata/stby11g
[oracle@stby11g ~]$ mkdir -p /u01/archive/stby11g

c. menyiapkan oratab dengan password yang sama dengan primer.
[oracle@stby11g ~]$ echo “stby11g:/u01/app/oracle/product/11.1.0/db_1:N” >> /etc/oratab
[oracle@stby11g ~]$ ORACLE_SID=stby11g; export ORACLE_SID
[oracle@stby11g ~]$ oraenv

d. menyiapkan Oracle Password File oracle clone
[oracle@stby11g ~]$ orapwd file=$ORACLE_HOME/dbs/orapwstby11g password=oracle0

9. copy datafile ke standby database (server physical)
[oracle@prmr11g ~]$ scp /u01/app/oracle/oradata/prmr11g/*.dbf oracle@172.20.22.21:/u01/app/oracle/oradata/stby11g/
[oracle@prmr11g ~]$ scp /u01/app/oracle/oradata/prmr11g/*.log oracle@172.20.22.21:/u01/app/oracle/oradata/stby11g/

10. create pfile dan modifikasi (server standby)
[oracle@stby11g ~]$ vim init_stby11g.ora
*.db_name=’prmr11g’
*.db_unique_name=’stby11g’
*.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(prmr11g,stby11g)’
*.LOG_ARCHIVE_DEST_1=’LOCATION=/u01/archive/stby11g/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=stby11g’
*.LOG_ARCHIVE_DEST_2=’SERVICE=prmr11g LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=prmr11g’

*.LOG_ARCHIVE_DEST_STATE_1=’ENABLE’
*.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’
*.LOG_ARCHIVE_FORMAT=’%t_%s_%r.arc’
*.LOG_ARCHIVE_MAX_PROCESSES=30

*.fal_server=’prmr11g’
*.fal_client=’stby11g’
*.standby_file_management=’auto’
*.db_file_name_convert=’/u01/app/oracle/oradata/prmr11g/’,’/u01/app/oracle/oradata/stby11g/’
*.log_file_name_convert=’/u01/app/oracle/oradata/prmr11g/’,’/u01/app/oracle/oradata/stby11g/’

11. edit listener & tnsnames
a. listener server physical

[oracle@prmr11g ~]$ vim /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(PROTOCOL = TCP)(HOST = 172.20.22.22)(PORT = 1521)(IP = FIRST)
(PROTOCOL = TCP)(HOST = 172.20.22.21)(PORT = 1521)(IP = FIRST))
)
)

SID_LIST_LISTENER_11G =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prmr11g)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1 )
(SID_NAME = prmr11g)
)
)

b. tnsnames server physical
[oracle@prmr11g ~]$ vim /u01/app/oracle/product/11.1.0/db_1/network/admin/tnsnames.ora
LISTENER_11G =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.22.22)(PORT = 1521)(IP = FIRST))
)
)

prmr11g =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.22.22)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prmr11g)
)
)

stby11g =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.22.21)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby11g)
)
)

c. listener server standby
[oracle@stby11g ~]$ vim /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(PROTOCOL = TCP)(HOST = 172.20.22.22)(PORT = 1521)(IP = FIRST)
(PROTOCOL = TCP)(HOST = 172.20.22.21)(PORT = 1521)(IP = FIRST))
)
)

SID_LIST_LISTENER_11G =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stby11g)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1 )
(SID_NAME = stby11g)
)
)

d. tnsnames server standby
[oracle@stby11g ~]$ vim /u01/app/oracle/product/11.1.0/db_1/network/admin/tnsnames.ora
LISTENER_11G =
(DESCRIPTION_LIST =
(DESCRIPTION =
(PROTOCOL = TCP)(HOST = 172.20.22.22)(PORT = 1521)(IP = FIRST)
(PROTOCOL = TCP)(HOST = 172.20.22.21)(PORT = 1521)(IP = FIRST))
)
)

prmr11g =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.22.22)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prmr11g)
)
)

stby11g =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.22.21)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby11g)
)
)

12. startup server
a. primary server
[oracle@prmr11g ~]$ sqlplus / as sysdba
SQL> startup;

b. standby server
[oracle@stby11g ~]$ sqlplus / as sysdba
SQL> create spfile from pfile=’/home/oracle/init_stby11g.ora’;
SQL> startup mount;

13. mengaktifkan physical standby database
a. server standby

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
b. server primary
SQL> alter system set log_archive_dest_state_2 = enable;

1. oracle data guard pengertian

2. demo oracle data guard physical standby database

3. oracle data guard test pengiriman logfile

4. oracle data guard standby server untuk reporting

5. oracle data guard active data guard 11g

6. oracle data guard switchover failover

7. oracle data guard test switchover failover

8. oracle data guard lampiran initora

16 Responses to “Demo : Oracle Data Guard physical standby database (2)”

  1. Wahyu February 3, 2010 at 12:01 pm #

    Mas, thanks artikelnya, saya sangat terbantu dengan panduan diatas.

    Tetap Menulis and selamat berkarya
    itung 2 pahala anda bertambah banyak
    karena banyak orang pintar karena anda
    itulah salah satu amal yang tidak ada putusnya
    yaitu ilmu yang bermamfaat bagi orang lain

  2. setijoagus February 4, 2010 at 5:52 am #

    saya senang kalau tulisan di blog ini membantu…….
    soal dapat pahala / tidak urusanNya. [he….he….]
    semoga anda juga dapat “virus” untuk menulis juga

  3. Jack Nickolson May 4, 2010 at 8:44 pm #

    Wonderful article about physical standby database configuration. Keep it up buddy.

  4. Toga May 20, 2010 at 11:25 pm #

    mas ada contoh utk setting dataguard di oracle 9i?

    Thanks

    • setijoagus May 21, 2010 at 1:56 am #

      apa sudah dicoba “how to” yang saya buat, untuk oracle 9i ?, kl sudah errornya bagian mana mungkin saya bisa bantu.
      BTW mungkin sudah saatnya lho upgrade ke 10g atau bahkan 11g

  5. Toga May 22, 2010 at 6:33 am #

    how to di artikel yang mana ya mas? sudah saya search pake keyword how to sama oracle 9i koq gak ketemu. Iya mas ini pake 9i karena kebetulan ini TA saya buat replikasi+standby database. Nah karena menemukan tutorial yang benar2 step by step utk replikasi di Oracle 9i akhirnya ya pake 9i itu. hehe…

    Kebetulan utk dataguardnya baru akan dibuat mas, apakah mas setijo ada tutorial yang step by step seperti artikel di atas ini utk yang 9i mas?

    matur nuwun sebelumnya

    Salam

    • setijoagus May 22, 2010 at 9:15 am #

      kalua boleh tahu url untuk “step by step utk replikasi di Oracle 9i” dimana ?
      kebetulan saya sendiri pernah bikin basic replikasi, advance replikasi dan snapshot [belum saya publish] dengan 11g, dan ada di blog ini

  6. setijoagus May 22, 2010 at 9:12 am #

    maksud saya dengan kalimat
    apa sudah dicoba “how to” yang saya buat tidak sama dengan judul artikel “how to”, tapi maksudnya ya artikel “Demo : Oracle Data Guard physical standby database (2) ” dan yang memang menggunakan 10g,

    saran saya coba dulu langkah – langkah saya buat di artikel itu.
    jika ada yang error bila diguanakn di oracle 9i tolong di publish disini, siapa tahu saya bisa membantu…..

    salam

  7. Toga May 22, 2010 at 3:25 pm #

    Disini pak linknya utk oracle 9i

    http://student.eepis-its.edu/~ahyar/TA/7403030001.pdf

    kebetulan dari TA kakak kelas pak, itu yang mendasari saya membuat TA saya dg topik sama seperti itu (karena disitu ada capture step by step pembuatan replikasi di OEMnya).

    Tapi setelah saya lihat mungkin kalo dibanding dengan artikel2 replikasi yang sudah ditulis bapak di blog ini, jauh lebih lengkap di blog bapak ini.

    oh untuk yg dataguard maksudnya ya artikel ini ya pak, hehe.. maaf kalo maksudnya seperti itu.

    Baik pak akan saya coba, tapi saya masih bingung, untuk pembuatan standby database itu bisa satu mesin (satu PC) dg primary dbnya atau harus dlm computer yang sendiri2? (beda computer antara primary dan standbynya?)

    sama satu lagi pak yang saya bingungkan, ini kan digabung dengan replikasi, dg asumsi misal saya akan membuat standby databasenya pada komputer yang terpisah dg primary database, apakah harus saya setting replikasi dulu juga pada computer standby database ini?

    Terima kasih banyak atas jwbnnya

    • setijoagus May 26, 2010 at 9:53 am #

      oooo wong suroboyo tho ?
      tapi kalaupakai oracle /10g 11g khan bisa juga

    • setijoagus June 9, 2010 at 7:07 am #

      menurut saya dataguard tidak sama dg replikasi, kl dataguard Aktif – Pasif / primary db – standby db
      sedangkan replikasi lebih cenderung Aktif – Aktif

  8. Toga June 13, 2010 at 2:15 pm #

    pak boleh minta contactnya? YM atau apa?

    begini pak sudah saya coba step by step diatas, tapi pada waktu step 4 (mengedit pfile) saya sesuaikan setingannya seperti disitu. ternyata setelah saya save dan menjalankan – startup nomount pfile=’…..’; ditempat saya (9i) error Pak.

    errornya begini
    LRM-00101: unknown parameter name ‘LOG_ARCHIVE_CONFIG’
    LRM-00123: value ’30’ for ‘ log_archive_max_processes’ must be between ‘1’ and ’10’
    ORA-01078: failure in processing system parameters

    kira2 solusi permasalahannya di 9i bgmn ya pak?

    matur nuwun

  9. setijoagus June 14, 2010 at 8:07 am #

    kl erornya
    LRM-00101: unknown parameter name ‘LOG_ARCHIVE_CONFIG’
    LRM-00123: value ’30′ for ‘ log_archive_max_processes’ must be between ’1′ and ’10′
    ORA-01078: failure in processing system parameters

    ya karena fitur itu di 9i belum ada
    salam

  10. Toga July 5, 2010 at 1:54 pm #

    saya baca dari webnya oracle fungsi dari LOG_ARCHIVE_CONFIG ini kan utk meng-enable atau mendisablekan pengiriman/penerimaan redo logsnya kan pak?

    Trus kira2 penggantinya di 9i bgmn ya Pak? kalopun terpaksa di 9i fitur ini blm ada dan harus menggunakan 10g,apakah bisa misalkan utk standbynya saya pake 10g dan di server physicalnya pake 9i?

    Soalnya di server physicalnya ini sudah saya set replikasi dg komputer yg lain lg menggunakan 9i ini pak?

    Matur nuwun

  11. setijoagus July 12, 2010 at 1:33 am #

    apakah bisa misalkan utk standbynya saya pake 10g dan di server physicalnya pake 9i?
    belum pernah mencoba, bisa ditanyakan pada om google dech
    salam

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

%d bloggers like this: