DataGuard 11g: Step-by-Step Instructions for Creating a Physical Standby Database (indonesia vers.)
Oracle Data Guard : Pengertian (1)
Salah satu solusi untuk disaster recovery pada database oracle adalah Oracle Data Guard,
selain tentunya Oracle RAC dan Oracle Stream.
Oracle Data Guard bekerja dengan cara :
a. ada 2 server primary (A) & standby (B)
b. jika server primary (A) error, standby (B) menjadi primary
membutuhkan waktu beberapa saat / detik untuk mengaktifkan
standby menjadi primary
c. setelah diperbaiki (A) dapat menjadi primary lagi
Oracle Data Guard, ada dua metode
a. physical standby database (Redo Apply)
sinkronisasi redo data yang dibuat server primary (A) dan dikirim
secara block per block ke server standby (B)
b. logical standby database (SQL Apply)
redo data dikonversi menjadi sql command oleh server primary (A)
dan dikirim ke server standby (B). Server standby (B) bersifat read
only, sehingga dpt dimanfaatkan untuk reporting server. Tetapi
tidak semua redo data daat dikonversi menjadi sql command.
Oracle Data Guard dapat diterapkan pada CPU, O/S dan Oracle database binaries yang berbeda pada primary dan standby systems. Misalnya, primary database menggunakan O/S Windows, dan standby database menggunakan Linux. (belum sempat coba beda O/S, Oracle versi yang beda)
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 (Harus dalam keadaan mounted tidak open)
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;
Oracle Data Guard : test pengiriman logfile (3)
Posted on February 28, 2009 by setijoagus
i
1 Votes
14. test
a. paksa agar primary server membuat log file (server primary)
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> host
[oracle@prmr11g ~]$ ls -la /u01/archive/prmr11g/
total 5224
drwxr-xr-x 2 oracle oinstall 4096 Feb 24 18:19 .
drwxr-xr-x 3 oracle oinstall 4096 Feb 21 17:20 ..
-rw-r—– 1 oracle oinstall 5322752 Feb 24 18:18 1_15_679442351.arc
-rw-r—– 1 oracle oinstall 1024 Feb 24 18:19 1_16_679442351.arc
b. apakah standby juga sudah mendapatkan logfile ? (server standby)
[oracle@stby11g stby11g]$ ls -la
total 5224
drwxr-xr-x 2 oracle oinstall 4096 Feb 24 15:57 .
drwxr-xr-x 3 oracle oinstall 4096 Feb 21 17:18 ..
-rw-r—– 1 oracle oinstall 5322752 Feb 24 15:57 1_15_679442351.arc
-rw-r—– 1 oracle oinstall 1024 Feb 24 15:57 1_16_679442351.arc
15. monitoring standby server (server primary)
a. berapa banyak
SQL> set pages 100
SQL> col name for a40
SQL> select name,to_char(FIRST_TIME,’dd-mon-yy hh24:mi:ss’) TIME ,SEQUENCE#,APPLIED from v$archived_log;
NAME TIME SEQUENCE# APP
—————————————- —————— ———- —
/u01/archive/prmr11g/1_15_679442351.arc 24-feb-09 16:43:02 15 NO
/u01/archive/prmr11g/1_16_679442351.arc 24-feb-09 18:18:35 16 NO
stby11g 24-feb-09 18:18:35 16 YES
stby11g 24-feb-09 16:43:02 15 YES
b. monitor pengiriman
SQL> set pages 100
SQL> col dest_id for a5
SQL> col destination for a50
SQL> col status for a10
SQL> col error for a10
SQL> select dest_id, destination, status, error from v$archive_dest;
DEST_ID DESTINATION STATUS ERROR
———- —————————————- ———- ———-
1 /u01/archive/prmr11g VALID
2 stby11g VALID
Oracle Data Guard : standby server untuk reporting (4)
Posted on February 28, 2009 by setijoagus
i
Rate This
16. standby server untuk reporting (server standby)
yaitu dengan cara mengubah menjadi READ ONLY, tetapi tidak bisa READ WRITE
a. periksa standby server dalam mode apa ? (server standby)
SQL> select database_role, open_mode from v$database;
DATABASE_ROLE OPEN_MODE
—————- ———-
PHYSICAL STANDBY MOUNTED
b. ubah dari mode recovery ke read only (server standby)
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> select database_role, open_mode from v$database;
DATABASE_ROLE OPEN_MODE
—————- ———-
PHYSICAL STANDBY READ ONLY
c. test untuk report, tidak dapat digunakan write (server standby)
SQL> conn scott/tiger;
SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> insert into dept values(41,’IT’,’SBY’);
insert into dept values(41,’IT’,’SBY’)
*
ERROR at line 1:
ORA-16000: database open for read-only access
d. primary server menambahkan record [41,’IT’,’SBY’] (server primary)
SQL> conn scott/tiger;
SQL> insert into dept values(41,’IT’,’SBY’);
SQL> commit;
SQL> conn / as sysdba
SQL> alter system switch logfile;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archive/prmr11g
Oldest online log sequence 18
Next log sequence to archive 19
Current log sequence 20
e. ubah mode ke recovery (server standby)
SQL> conn / as sysdba
SQL> alter database close;
SQL> alter database recover managed standby database disconnect;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archive/stby11g
Oldest online log sequence 18
Next log sequence to archive 0
Current log sequence 20
f. apakah data dari primary sudah ditransfer ke standby [41,’IT’,’SBY’] ? (server standby)
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> conn scott/tiger;
SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
41 IT SBY
Oracle Data Guard : Active Data Guard 11G (5)
Posted on February 28, 2009 by setijoagus
i
Rate This
17. Active Data Guard 11G
standby server untuk reporting READ ONLY, tetapi tidak bisa READ WRITE,
tetapi juga siap menerima pengiriman redo data
a. ubah setting standby server (server standby)
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> alter database recover managed standby database disconnect;
SQL> select DATABASE_ROLE, open_mode from v$database;
DATABASE_ROLE OPEN_MODE
—————- ———-
PHYSICAL STANDBY READ ONLY
b. saldo awal (server standby)
SQL> conn scott/tiger;
Connected.
SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
43 MK SBY
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
41 IT SBY
42 AU SBY
c. server primary menambah record [44, VA,SBY] (server primary)
SQL> conn scott/tiger
SQL> insert into dept values(44,’VA’,’SBY’);
SQL> commit;
d. standby server, apakah sudah menerima perubahan data [44, VA,SBY]? (server standby)
SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
43 MK SBY
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
41 IT SBY
42 AU SBY
e. server primary dipaksa mengirim redo data (server primary)
SQL> conn / as sysdba
SQL> alter system switch logfile;
f. standby server, apakah sudah menerima perubahan data [44, VA,SBY] ? (server standby)
SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
43 MK SBY
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
41 IT SBY
42 AU SBY
44 VA SBY
Oracle Data Guard : switchover & failover (6)
Posted on February 28, 2009 by setijoagus
i
Rate This
18. switchover & failover
a. persiapan (server primary) : Database OPEN, koneksi valid
SQL> select dest_id, destination, status, error from v$archive_dest;
DEST_ID DESTINATION STATUS ERROR
———- —————————————- ———- ———-
1 /u01/archive/prmr11g VALID
2 stby11g VALID
SQL> select DATABASE_ROLE, open_mode from v$database;
DATABASE_ROLE OPEN_MODE
—————- ———-
PRIMARY READ WRITE
b. persiapan (server standby) : Database MOUNT
SQL> select DATABASE_ROLE, open_mode from v$database;
DATABASE_ROLE OPEN_MODE
—————- ———-
PHYSICAL STANDBY MOUNTED
c. lakukan switchover (server primary) : error jika masih ada session yang aktif
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
d. Jika masih ada session yang aktif tambahkan command STANDBY WITH SESSION SHUTDOWN kemudian kill yang aktif jika perlu / tunggu sampai dengan logout (server primary)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
e. Jika semua sudah sukses langkah diatas, lakukan shutdown dan kemudian mount (server primary)
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
f. SWITCHOVER TO PRIMARY (server standby)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Oracle Data Guard : test switchover & failover (7)
Posted on February 28, 2009 by setijoagus
i
Rate This
19. test switchover & failover
a. primary standby yang sudah berubah menjadi standby server (sid = prmr11g), di down
SQL> shutdown immediate;
b. menambah record dari server standby yang sudah berubah menjadi primary standby (sid = stby11g)
SQL> conn scott/tiger
SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
43 MK SBY
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
41 IT SBY
42 AU SBY
44 VA SBY
SQL> insert into dept values(45,’KL’,’SBY’);
SQL> commit;
c. server primary dipaksa create redo data (sid = stby11g)
SQL> conn / as sysdba
SQL> alter system switch logfile;
d. (sid = prmr11g), mount dan diset menjadi active dataguard
SQL> startup mount;
SQL> alter database open read only;
SQL> alter database recover managed standby database disconnect;
SQL> conn scott/tiger
SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
43 MK SBY
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
41 IT SBY
42 AU SBY
44 VA SBY
45 KL SBY
e. Kembali ke posisi semula primary server (sid = prmr11g), standby server (sid = stby11g)
e1. lakukan switchover (server primary) (sid = stby11g)
SQL> conn / as sysdba
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
e2. Jika sudah sukses, lakukan shutdown dan kemudian mount (server primary) (sid = stby11g)
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
e3. SWITCHOVER TO PRIMARY (server standby) (sid = prmr11g)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Oracle Data Guard : LAMPIRAN init.ora (8)
Posted on February 28, 2009 by setijoagus
i
Rate This
############################################
#LAMPIRAN 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/\’
prmr11g.__db_cache_size=50331648
prmr11g.__java_pool_size=12582912
prmr11g.__large_pool_size=4194304
prmr11g.__oracle_base=\’/u01/app/oracle\’#ORACLE_BASE set from environment
prmr11g.__pga_aggregate_target=209715200
prmr11g.__sga_target=213909504
prmr11g.__shared_io_pool_size=0
prmr11g.__shared_pool_size=142606336
prmr11g.__streams_pool_size=0
*.audit_file_dest=\’/u01/app/oracle/admin/prmr11g/adump\’
*.audit_trail=\’db\’
*.compatible=\’11.1.0.0.0\’
*.control_files=\’/u01/app/oracle/oradata/prmr11g/control01.ctl\’,\’/u01/app/oracle/oradata/prmr11g/control02.ctl\’,\’/u01/app/oracle/oradata/prmr11g/control03.ctl\’
*.db_block_size=8192
*.db_domain=\’\’
#*.db_name=\’prmr11g\’
*.db_recovery_file_dest=\’/u01/app/oracle/flash_recovery_area/prmr11g\’
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest=\’/u01/app/oracle\’
*.dispatchers=\’(PROTOCOL=TCP) (SERVICE=prmr11gXDB)\’
*.log_archive_dest_1=\’location=/u01/archive/prmr11g\’
*.memory_target=422576128
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=\’EXCLUSIVE\’
*.undo_tablespace=\’UNDOTBS1\’
############################################
#LAMPIRAN 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/\’
stby11g.__db_cache_size=50331648
stby11g.__java_pool_size=12582912
stby11g.__large_pool_size=4194304
stby11g.__oracle_base=\’/u01/app/oracle\’#ORACLE_BASE set from environment
stby11g.__pga_aggregate_target=209715200
stby11g.__sga_target=213909504
stby11g.__shared_io_pool_size=0
stby11g.__shared_pool_size=142606336
stby11g.__streams_pool_size=0
*.audit_file_dest=\’/u01/app/oracle/admin/stby11g/adump\’
*.audit_trail=\’db\’
*.compatible=\’11.1.0.0.0\’
*.control_files=\’/u01/app/oracle/oradata/stby11g/control01.ctl\’,\’/u01/app/oracle/oradata/stby11g/control02.ctl\’,\’/u01/app/oracle/oradata/stby11g/control03.ctl\’
*.db_block_size=8192
*.db_domain=\’\’
*.db_recovery_file_dest=\’/u01/app/oracle/flash_recovery_area/stby11g\’
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest=\’/u01/app/oracle\’
*.dispatchers=\’(PROTOCOL=TCP) (SERVICE=stby11gXDB)\’
*.log_archive_dest_1=\’location=/u01/archive/stby11g\’
*.memory_target=422576128
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=\’EXCLUSIVE\’
*.undo_tablespace=\’UNDOTBS1\’
Tidak ada komentar:
Posting Komentar