Demo : Oracle Data Guard physical standby database (2)

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