Step by Step Oracle 19C Data Guard setup on Oracle VM

 Dear All,

In this post i am going to share you oracle 19c Data guard step by step activity guide .

Primary database details :

 

Db name : PROD

Db unique name :stby

Host & IP :  standby.localdomain

SID : stby

 

Standby Database Details :

 

Db name : PROD

Db unique name : prod

Host & IP : primary.localdomain

SID : prod

DG Configuration steps : 

Step:1 Enable the archive log  mode in the primary database.

 

alter database archivelog;

archive log list;

Step:2 Enable the force logging. 

SQL> alter database force logging;

Database altered.

 

Note : to enable flash back we need to setup below pre-requisites

alter system set db_recovery_file_dest_size= 10G scope=both;

 

alter system set db_recovery_file_dest='/u02/app/oracle/fast_recovery_area' scope=both;

Step:3 Flashback should be enabled in primary database.

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

select name,FORCE_LOGGING,log_mode,flashback_on from v$database;

Step:4 Add the Standby redo log files for standby side.

 set lines 200

col MEMBER format a65

select l.GROUP#,l.THREAD#,f.member,l.ARCHIVED,l.status,(bytes/1024/1024) "size(MB)" from v$log l,v$logfile f where f.group#=l.group# order by 1,2;

 

select l.GROUP#,l.THREAD#,f.member,l.ARCHIVED,l.status,(bytes/1024/1024) "size(MB)" from v$standby_log l,v$logfile f where f.group#=l.group# order by 1,2;

 

SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

alter database add standby logfile group 4 '/u01/app/oracle/oradata/STBY/redo04.log' size 200m;

 alter database add standby logfile group 5 '/u01/app/oracle/oradata/STBY/redo05.log' size 200m;

alter database add standby logfile group 6 '/u01/app/oracle/oradata/STBY/redo06.log' size 200m;

alter database add standby logfile group 7 '/u01/app/oracle/oradata/STBY/redo07.log' size 200m;

select member from v$logfile;

Step:5 Take the backup of Primay database , pfile and password files on Primary database

 

create one directory for Primary side backups .

mkdir -p /u01/backup

cd /u01/backup

Create pfile from spfile on Primary database this we can use in the standby database side . there we need to modify some parameter as per the standby db .

create pfile='/u01backup/initprod.ora' from spfile;

copy the primary password file to backup location on Primary node this we need to copy to standby side oracle home / dbs location there we need to rename the sid as per the standby db.

cp /u01/app/oracle/product/19.0.0/db_home/dbs/initstby.ora .

rman target=/ nocatalog <<EOF

run {

    sql "alter system archive log current";

               allocate channel ch1 type disk format '/u01/backup/PROD_bkp_standby_%U';

               backup as compressed backupset database plus archivelog;

               backup current controlfile for standby;

               sql "alter system archive log current";

}

               exit;

EOF

 

Note : Tar the rman backup if you are using manual restore use this backup , if you are using active clone / active rman command no need of this backup .

tar -cvzf rmanbackup.tar.gz rman

tar -xvzf rmanbackup.tar.gz

 

Step:6  set the log archive config and other DG  parameters on primary database . see the below snap for all the parameters which i set on primary node side .

 ALTER SYSTEM SET log_archive_config='dg_config=(prod,stby)' SCOPE=both;

ALTER SYSTEM SET log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=prod' SCOPE=both;

ALTER SYSTEM SET log_archive_dest_2='service=stby async valid_for=(online_logfiles,primary_role) db_unique_name=stby' SCOPE=both;

ALTER SYSTEM SET fal_client='prod' SCOPE=both;

ALTER SYSTEM SET fal_server='stby' SCOPE=both;

ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=both;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

 

alter system set db_file_name_convert='/u01/app/oracle/oradata/STBY/','/u01/app/oracle/oradata/PROD' scope=spfile;

alter system set log_file_name_convert='/u01/app/oracle/oradata/STBY/','/u01/app/oracle/oradata/PROD' scope=spfile;

converts the filename of a

new datafile and logfile respectively on the primary database to a filename on the

standby database.

 

 

crosscheck the DR parameters on primary once

 

col name for a30

col value for a85

set lines 200 pagesize 200

 

select name, value from v$parameter where name in ('db_name','db_unique_name','log_archive_config','log_archive_dest_1','log_archive_dest_2','log_archive_dest_state_1′,’log_archive_dest_state_2','remote_login_passwordfile','log_archive_format','log_archive_max_processes','fal_server','fal_client','db_file_name_convert','log_file_name_convert','standby_file_management');

 

 

Set the log archive format = ‘%t_%s_%r.arc ,it determines the name of the archive log file it comes into role when archive log is enabled.

 

alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

 

Step:7 configure the log_archive_process to 6 ,it prevents to avoid any runtime overhead of invoking additional ARCn process,you can set the LOG_ARCHIVE_MAX_PROCESSES

 

alter system set log_archive_max_processes=6; ( optional ) default value is 4 

show parameter remote_login_passwordfile  ( default value )

 

Step:8 Configure standby database .

 

alter system set db_unique_name=stby scope=spfile;

select name from v$datafile;

 

show parameter audit

 

 

mkdir -p /u01/app/oracle/oradata/STBY/

mkdir -p /u01/app/oracle/oradata/STBY/pdbseed/

mkdir -p /u01/app/oracle/oradata/STBY/pdb         

mkdir -p /u01/app/oracle/oradata/STBY/adump

mkdir -p /u01/app/oracle/fast_recovery_area/

 

 

vi /u01/app/oracle/product/19.0.0/db_home/dbs/initstby.ora

*.audit_file_dest='/u01/app/oracle/admin/stby/adump'

*.audit_trail='db'

*.compatible='19.0.0.0.0'

*.control_files='/u01/app/oracle/oradata/stby/standby1.ctl'#Restore Controlfile

*.db_block_size=8192

*.db_file_name_convert='/u01/app/oracle/oradata/PROD/','/u01/app/oracle/oradata/STBY/'

*.db_name='prod'

*.db_recovery_file_dest_size=10737418240

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/'

*.db_unique_name='stby'

*.dg_broker_start=TRUE

*.diagnostic_dest='/u01/app/oracle/admin/stby/adump'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=stbyXDB)'

*.enable_pluggable_database=true

*.fal_client='stby'

*.fal_server=''

*.local_listener='LISTENER_PROD'

*.log_archive_config='dg_config=(stby,prod)'

*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stby'

*.log_archive_dest_2='service="prod"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="prod" net_timeout=30','valid_for=(online_logfile,all_roles)'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='%t_%s_%r.dbf'

*.log_archive_max_processes=5

*.log_file_name_convert='/u01/app/oracle/oradata/PROD/','/u01/app/oracle/oradata/STBY/'

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=300

*.pga_aggregate_target=568m

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=1704m

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

Step:9 Configure network files on both primary and standby databases.

 

Listener & TNS entry in the primary database. 

[oracle@primary admin]$ cat listener.ora

LISTENER =

      (DESCRIPTION =

           (ADDRESS = (PROTOCOL = TCP) (HOST = primary.localdomain) ( PORT = 1521))

     )

SID_LIST_LISTENER =

 (SID_LIST =

  (SID_DESC =

   (GLOBAL_DBNAME = prod)

   (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_home)

   (SID_NAME = prod)

 )

   (SID_DESC =

   (GLOBAL_DBNAME = prod_DGMGRL)

   (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_home)

   (SID_NAME = prod)

 )

)

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@primary admin]$

 [oracle@primary admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/db_home/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PROD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = prod)

    )

  )

STBY =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = stby)

    )

  )

PDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = pdb)

    )

  )

LISTENER_PROD =

  (ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))

[oracle@primary admin]$

Standby side Listener and TNS details : 

[oracle@standby admin]$ cat listener.ora

LISTENER =

      (DESCRIPTION =

           (ADDRESS = (PROTOCOL = TCP) (HOST = standby.localdomain) ( PORT = 1521))

     )

SID_LIST_LISTENER =

 (SID_LIST =

  (SID_DESC =

   (GLOBAL_DBNAME = stby)

   (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_home)

   (SID_NAME = stby)

 )

   (SID_DESC =

   (GLOBAL_DBNAME = stby_DGMGRL)

   (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_home)

   (SID_NAME = stby)

 )

)

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@standby admin]$ cat tnsnames.ora

STBY =

 (DESCRIPTION =

  (ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521))

  (CONNECT_DATA =

   (SERVER = DEDICATED)

   (SERVICE_NAME = stby)

  )

 )

PROD =

 (DESCRIPTION =

  (ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))

  (CONNECT_DATA =

   (SERVER = DEDICATED)

   (SERVICE_NAME = prod)

  )

 )

LISTENER_PROD =

(ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521))

[oracle@standby admin]$

tnsping should work on primary and standby side : 

=======================================

Step 10 :  update the parameter files on stand by and start standby database in nomount using newly created pfile.

 

 startup nomount pfile='initstby.ora';

 /u01/app/oracle/oradata/TEST,/u01/app/oracle/fast_recovery_area/TEST,/u01/app/oracle/oradata/TEST/pdb1

Fast recovery area location: /u01/app/oracle/fast_recovery_area/TEST

 

 rman target sys/Welcome_123@prod auxiliary sys/Welcome_123@stby

 

duplicate target database for standby from active database nofilenamecheck;

 

after restore completes enable the mrp process and then stop the db startup , it will start in read only mode .

select max(sequence#) from v$archived_log;

The same information can be obtained by checking the view v$archived_log, like this:

select sequence# from v$archived_log where registrar = 'RFS' and  applied = 'NO';

select sequence# , first_time,next_time from v$archived_log order by sequence#;

 

The actual lag that these non-applied logs causes, can be found with the query:

set lines 200

col source_db_unique_name format a10 

col value format a10

SELECT source_db_unique_name,name,value,unit,time_computed,datum_time FROM v$dataguard_stats WHERE name = 'apply lag';

 

select name,open_mode,database_role from v$database;

Note : start the mrp 

alter database recover managed standby database disconnect nodelay;

stop the mrp :

alter database recover managed standby database cancel;

DG commands : 

select name,open_mode,database_role from v$database;

 SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

select max(sequence#) from v$archived_log;

alter database recover managed standby database cancel;

alter database recover managed standby database disconnect from session;

alter database open read only;

alter database archivelog;

alter database open;

select FORCE_LOGGING,log_mode from v$database;

alter system switch logfile;

alter database flashback on;

show parameter recovery;

select group#, thread#, bytes/1024/1024 mb from v$log;

alter database add standby logfile thread 1 size 200m;

select member from v$logfile where type='STANDBY';

alter system set db_unique_name=prod scope=both;

set linesize 500 pages 100

col name format a30

col value format a100

select name, value

from v$parameter

where name in ('db_name','db_unique_name','log_archive_config',

'log_archive_dest_1','log_archive_dest_2',

'log_archive_dest_state_1','log_archive_dest_state_2',

'remote_login_passwordfile', 'log_archive_format', 'log_archive_max_processes',

'fal_server','db_file_name_convert', 'log_file_name_convert', 'standby_file_management');

alter system set STANDBY_FILE_MANAGEMENT=AUTO scope = both;

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,stby)' scope = both;

alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=prod' scope = both;

or 

alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope = both;

ALTER SYSTEM SET log_archive_dest_2='service=stby async valid_for=(online_logfiles,primary_role) db_unique_name=stby' SCOPE=both; 

fal_client  no longer needed in 19C 

alter system set FAL_SERVER='stby' scope = both;

alter system set db_unique_name=prod scope=both;

Thanks,

Srini