Oracle Data Guard (ODG) 19c Physical Standby Implementation Using RMAN Active Duplicate Method

Primary Database IP : 192.168.56.10 Hostname : oracle1 DB name : DIMUY

Standby Database IP : 192.168.56.12 Hostname : oracle2 DB name : DIMAY

Environment Oracle Database 19c Linux 8.8 Network NAT & Host-Only Adapter

1. Konfigurasi Network dengan menambahkan IP dan Hostname di kedua VM

nano /etc/hosts
192.168.56.10 oracle1
192.168.56.12 oracle2

2. Lalu konfigurasi TNS dan Listener di kedua VM

nano $ORACLE_HOME/network/admin/tnsnames.ora

DIMUY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = DIMUY)
    )
  )

DIMAY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.12)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = DIMAY)
    )
  )

nano $ORACLE_HOME/network/admin/listener.ora

#di Primary
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
    )
  )

#di standby
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.12)(PORT = 1521))
    )
  )

Lalu restart Listener dengan command lsnrctl stop, lsnrctl start

3. Langkah selanjutnya yaitu membuat password file di kedua VM

#di primary
orapwd file=$ORACLE_HOME/dbs/orapwDIMUY password='dimdim23!' entries=10

#di standby
orapwd file=$ORACLE_HOME/dbs/orapwDIMAY password='dimdim23!' entries=10

4. Copy password file dari primary ke standby

scp $ORACLE_HOME/dbs/orapwDIMUY oracle@192.168.56.12:$ORACLE_HOME/dbs/orapwDIMAY

5. Berikutnya membuat PFILE standby

nano $ORACLE_HOME/dbs/initDIMAY.ora

#isi dengan 
db_name='DIMUY'
db_unique_name='DIMAY'
memory_target=256M
control_files='/u01/app/oracle/oradata/DIMAY/control01.ctl'
db_file_name_convert=('DIMUY','DIMAY')
log_file_name_convert=('DIMUY','DIMAY')
log_archive_config='DG_CONFIG=(DIMUY,DIMAY)'
fal_server='DIMUY'
fal_client='DIMAY'
standby_file_management='AUTO'M

6. Berikutnya membuat Directory Standby

mkdir -p /u01/app/oracle/oradata/DIMAY
mkdir -p /u01/app/oracle/arch
mkdir -p /u01/app/oracle/fast_recovery_area

7. Setelah itu Startup Standby Nomount

sqlplus / as sysdba

startup nomount;

8. Membuat SPFILE di Standby

CREATE SPFILE FROM PFILE='/u01/app/oracle/product/19c/dbhome_1/dbs/initDIMAY.ora';

#lalu restart database
shutdown abort;
startup nomount;

9. Lalu konfigurasi Data guard Parameter

#di primary
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DIMUY,DIMAY)' SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=
'SERVICE=DIMAY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DIMAY'
SCOPE=BOTH;

ALTER SYSTEM SET FAL_SERVER=DIMAY SCOPE=BOTH;

ALTER SYSTEM SET FAL_CLIENT=DIMUY SCOPE=BOTH;

#di standby
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DIMUY,DIMAY)' SCOPE=BOTH;

ALTER SYSTEM SET FAL_SERVER=DIMUY SCOPE=BOTH;

ALTER SYSTEM SET FAL_CLIENT=DIMAY SCOPE=BOTH;

10. Jalankan RMAN Standby duplicate

#di Standby
rman target 'sys/dimdim23!@DIMUY' auxiliary 'sys/dimdim23!@DIMAY'

DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
NOFILENAMECHECK;

11. Mengaktifkan Managed Recovery di Standby

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

12. Verifikasi Data Guard di kedua VM

SELECT NAME, OPEN_MODE, DATABASE_ROLE, LOG_MODE FROM V$DATABASE;

13. Terakhir test redo shipping

#di primary
ALTER SYSTEM SWITCH LOGFILE;

#di standby
SELECT SEQUENCE#, APPLIED
FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE#;

Hal ini menunjukkan bahwa archivelog berhasil dikirim dan di-apply ke standby database

Troubleshooting Selama implementasi, terdapat beberapa kendala yang berhasil diselesaikan:

• ORA-12528: listener blocking new connections
• ORA-01017: invalid username/password
• ORA-65093: multitenant container database not set up properly
• RMAN-05001: filename conflicts
• ORA-32001: SPFILE not in use

Solusi dilakukan dengan:

• Reconfigure listener dan tnsnames
• Sinkronisasi password file
• Membuat SPFILE dari PFILE
• Menggunakan parameter NOFILENAMECHECK
• Mengubah path datafile standby

KESIMPULAN Implementasi Oracle Data Guard 19c Physical Standby berhasil dilakukan menggunakan metode RMAN Active Duplicate. Proses redo shipping dan managed recovery berhasil berjalan secara realtime antara primary database dan standby database.

Dengan konfigurasi ini, standby database dapat digunakan sebagai solusi disaster recovery untuk meningkatkan availability dan reliability database Oracle.