Oracle Dataguard physical Standby configuration in 19c:(Active method-without using rman backup)

Overview steps:

Step:1 we have to enable the archive log mode ,flashback  on primary databases.

Step:2 Then enable the force logging option.By enabling the force logging option it helps us to capture all the changes made in the database and available for recovery in the redo logs.

Step:3 Add the redolog files for standby database,it is required when you are configuring the standby for maximum protection.

Step:4 Add the listener.ora and tnsnames.ora entries in both primary and standby sides.

Step:5 Change the some set of parameters in primary side,you can change these parameters either by sql or directly make changes in the pfile.

Step:6 Move the password file,pfile from primary to standby using scp.(we can also create the pfile in standby side also by having a single parameter db_name in it)

Step:7 create the directory structure in standby similar to the primary side.

Step:8 Start the standby side database in nomount using the pfile.

Step:9 Connect to the RMAN and duplicate the primary database using the command

duplicate target database for standby from active database dorecover nofilenamecheck;

Step:10 Now our standby database is ready for read only purpose.

Step:11 Then connect to the standby database and start the MRP process using alter database recover managed standby database using current logfile disconnect; This command is used to apply the log files from primary to standby.

Step:12 Verify the current log sequence in both primary and standby sides.

Do the following set of changes in primary side:

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

 SQL> alter database archivelog;
 Database altered
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5

Step:2 Enable the force logging. 

SQL>alter database force logging;
Database altered

Step:3 Flashback should be enabled in primary database.

 ALTER DATABASE FLASHBACK ON;
 Database altered

Check the archive log,force_logging,flashback is enabled.

SQL> select name,force_logging,flashback_on,log_mode from v$database;

   NAME    FORCE_LOGGING  FLASHBACK_ON   LOG_MODE      
--------- --------------- ------------- ----------
   PROD        YES            YES       ARCHIVELOG

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

SQL> alter database add standby logfile thread 1 group 4('/u01/app/oracle/oradata/redo04.log')size 50m;
Database altered.

SQL> alter database add standby logfile thread 1 group 5('/u01/app/oracle/oradata/redo05.log')size 50m;
Database altered.

SQL> alter database add standby logfile thread 1 group 6('/u01/app/oracle/oradata/redo06.log')size 50m;
Database altered.

SQL> alter database add standby logfile thread 1 group 7('/u01/app/oracle/oradata/redo07.log')size 50m;
Database altered.

Step:5  set the log archive config parameter. 

SQL> alter system set log_archive_config='DG_config=(prod,prodstdby)';
System altered.
SQL> show parameter log_archive_config
  NAME                 TYPE                VALUE
------------------- ----------- -----------------------------
log_archive_config    string      DG_config=(prod,prodstdby)
SQL> show parameter log_archive_dest_2

NAME                                TYPE               VALUE
------------------------------- ----------- ---------------------------
log_archive_dest_2                string     SERVICE=standby NOAFFIRM ASYNC
                                             VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
                                             DB_UNIQUE_NAME=prodstdby
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
NAME                                 TYPE               VALUE
---------------------------------  ----------  --------------------------
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string

set the log_archive_dest_1 and log_archive_dest_2 parameter respectively.

SQL> ALTER SYSTEM SET log_archive_dest_1='location=/u01/app/oracle/recovery_area/ valid_for=(all_logfiles,all_roles) db_unique_name=prod' SCOPE=SPFILE; 
System altered.
SQL> ALTER SYSTEM SET log_archive_dest_2='service=prodstdby async valid_for=(online_logfiles,primary_role) db_unique_name=prodstdby' SCOPE=SPFILE; 
System altered.

Step:6 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.

SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
System altered.

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

SQL> alter system set log_archive_max_processes=30;
System altered.

Step:8 register the fal_server and fal_client 

SQL> alter system set fal_server=prodstdby;
System altered.

SQL> alter system set fal_client=prod;
System altered.

Step:9 The standby_file_management initialization parameter plays an important role in the recovery process.

SQL> alter system set standby_file_management=auto;
System altered.

Step:10 change the remote_login_password file as exclusive.

SQL> show parameter remote_login_passwordfile

    NAME                                TYPE                 VALUE
------------------------------------ ----------- ------------------------
remote_login_passwordfile              string              EXCLUSIVE

Step:11 db_file_name_convert and log_file_name_convert, converts the filename of a

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

standby database.

SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/prod/','/u01/app/oracle/oradata/prodstdby/' scope=spfile;
System altered.

SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/prod/','/u01/app/oracle/oradata/prodstdby/' scope=spfile;
System altered.

Step:12 Configure the tns and listener files on both primary and standby databases.

Listener entry in the primary database. 

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.177.133)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
       (SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = prod)
                (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
                (SID_NAME = prod)
                )
        )

ADR_BASE_LISTENER = /u01/app/oracle

tnsnames entry in the primary database.

 prod =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.177.133)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prod)
        (GLOBAL_NAME = prod)
    )
  )

prodstdby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.177.132)(PORT = 1521))
    )
   (CONNECT_DATA =
      (SERVICE_NAME = prod)
        (GLOBAL_NAME = prod)
    )
  )

Listener entry in the standby database.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.177.132)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
        (SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = prod)
                (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
                (SID_NAME = prod)
                )
        )

ADR_BASE_LISTENER = /u01/app/oracle

tnsnames entry in the standby database.

prodstdby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.177.132)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prod)
        (GLOBAL_NAME = prod)
    )
  )
prod =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.177.133)(PORT = 1521))
    )
   (CONNECT_DATA =
      (SERVICE_NAME = prod)
        (GLOBAL_NAME = prod)
    )
  )

Step:13  ping your primary and standby databases from one to another.

[oracle@primary admin]$ tnsping prodstdby

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 28-MAY-2021 03:00:23

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.177.132)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = prod) (GLOBAL_NAME = prod)))

OK (10 msec)

[oracle@primary ~]$ tnsping prod

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 28-MAY-2021 03:00:54

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.177.133)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = prod) (GLOBAL_NAME = prod)))

OK (0 msec)

Step:14 create the similar directory structures in standby side as of in primary.

/u01/app/oracle/admin/prodstdby/adump
/u01/app/oracle/oradata/prodstdby
/u01/app/oracle/fast_recovery_area/prodstdby

Step:15 Transfer the password files,pfiles  to the standby side through scp.

[oracle@primary dbs]$ scp orapwprod initprod.ora
oracle@192.168.177.132:/u01/app/oracle/product/19.0.0/dbhome_1/dbs
The authenticity of host '192.168.177.132 (192.168.177.132)' can't be established.
ECDSA key fingerprint is 13:6a:28:ab:89:d8:77:c1:4a:74:13:ec:18:bd:70:f8.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.177.132' (ECDSA) to the list of known hosts.
oracle@192.168.177.132's password:
orapwprod                                            100% 2048     2.0KB/s   00:00   
initprod.ora                                         100% 1499     1.5KB/s   00:00

Step:16 Edit the pfile according to the standby side.

Step:17 start the db in nomount stage and connect to the rman to duplicate the target database.

[oracle@primary dbs]$ export ORACLE_SID=prod
[oracle@primary dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 28 03:44:50 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1543500144 bytes
Fixed Size                  8896880 bytes
Variable Size            1191182336 bytes
Database Buffers          335544320 bytes
Redo Buffers                7876608 bytes
SQL>

Step:18 Duplicate the target database using the following command. 

duplicate target database for standby from active database dorecover nofilenamecheck;
[oracle@primary ~]$ rman target sys/oracle@prod auxiliary sys/oracle@prodstdby

Recovery Manager: Release 19.0.0.0.0 - Production on Fri May 28 03:53:29 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=485494264)
connected to auxiliary database: PROD (not mounted)

RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;    

Starting Duplicate Db at 28-MAY-21

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=39 device type=DISK

current log archived

 

contents of Memory Script:

{

   backup as copy reuse

   passwordfile auxiliary format  '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwprod'   ;

}

executing Memory Script

 

Starting backup at 28-MAY-21

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=88 device type=DISK

Finished backup at 28-MAY-21

duplicating Online logs to Oracle Managed File (OMF) location

duplicating Datafiles to Oracle Managed File (OMF) location

 

contents of Memory Script:

{

   sql clone "create spfile from memory";

   shutdown clone immediate;

   startup clone nomount;

   restore clone from service  'prod' standby controlfile;

}

executing Memory Script

 

sql statement: create spfile from memory

 

Oracle instance shut down

 

connected to auxiliary database (not started)

Oracle instance started

 

Total System Global Area    1543500144 bytes

 

Fixed Size                     8896880 bytes

Variable Size               1191182336 bytes

Database Buffers             335544320 bytes

Redo Buffers                   7876608 bytes

 

Starting restore at 28-MAY-21

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=35 device type=DISK

 

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service prod

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04

output file name=/u01/app/oracle/oradata/PROD/controlfile/o1_mf_jbyo90vk_.ctl

output file name=/u01/app/oracle/fast_recovery_area/PROD/controlfile/o1_mf_jbyo90xc_.ctl

Finished restore at 28-MAY-21

 

contents of Memory Script:

{

   sql clone 'alter database mount standby database';

}

executing Memory Script

 

sql statement: alter database mount standby database

 

contents of Memory Script:

{

   set newname for clone tempfile  1 to new;

   switch clone tempfile all;

   set newname for clone datafile  1 to new;

   set newname for clone datafile  3 to new;

   set newname for clone datafile  4 to new;

   set newname for clone datafile  7 to new;

   restore

   from  nonsparse   from service 

 'prod'   clone database

   ;

   sql 'alter system archive log current';

}

executing Memory Script

 

executing command: SET NEWNAME

 

renamed tempfile 1 to /u01/app/oracle/oradata/PRODSTDBY/datafile/o1_mf_temp_%u_.tmp in control file

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

Starting restore at 28-MAY-21

using channel ORA_AUX_DISK_1

 

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service prod

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/PRODSTDBY/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:35

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service prod

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PRODSTDBY/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service prod

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PRODSTDBY/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service prod

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/PRODSTDBY/datafile/o1_mf_users_%u_.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02

Finished restore at 28-MAY-21

 

sql statement: alter system archive log current

current log archived

 

contents of Memory Script:

{

   restore clone force from service  'prod' 

           archivelog from scn  2114411;

   switch clone datafile all;

}

executing Memory Script

 

Starting restore at 28-MAY-21

using channel ORA_AUX_DISK_1

 

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: using network backup set from service prod

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=7

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: using network backup set from service prod

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=8

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02

Finished restore at 28-MAY-21

 

datafile 1 switched to datafile copy

input datafile copy RECID=5 STAMP=1073707288 file name=/u01/app/oracle/oradata/PRODSTDBY/datafile/o1_mf_system_jc079t3h_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=6 STAMP=1073707288 file name=/u01/app/oracle/oradata/PRODSTDBY/datafile/o1_mf_sysaux_jc07dry1_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=7 STAMP=1073707288 file name=/u01/app/oracle/oradata/PRODSTDBY/datafile/o1_mf_undotbs1_jc07flt2_.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=8 STAMP=1073707288 file name=/u01/app/oracle/oradata/PRODSTDBY/datafile/o1_mf_users_jc07ftx2_.dbf

 

contents of Memory Script:

{

   set until scn  2114927;

   recover

   standby

   clone database

    delete archivelog

   ;

}

executing Memory Script

 

executing command: SET until clause

 

Starting recover at 28-MAY-21

using channel ORA_AUX_DISK_1

 

starting media recovery

 

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/recovery_area/1_7_1073655931.arc

archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/recovery_area/1_8_1073655931.arc

archived log file name=/u01/app/oracle/recovery_area/1_7_1073655931.arc thread=1 sequence=7

archived log file name=/u01/app/oracle/recovery_area/1_8_1073655931.arc thread=1 sequence=8

media recovery complete, elapsed time: 00:00:00

Finished recover at 28-MAY-21

 

contents of Memory Script:

{

   delete clone force archivelog all;

}

executing Memory Script

 

released channel: ORA_DISK_1

released channel: ORA_AUX_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=88 device type=DISK

deleted archived log

archived log file name=/u01/app/oracle/recovery_area/1_7_1073655931.arc RECID=1 STAMP=1073707285

deleted archived log

archived log file name=/u01/app/oracle/recovery_area/1_8_1073655931.arc RECID=2 STAMP=1073707287

Deleted 2 objects

 

Finished Duplicate Db at 28-MAY-21
RMAN>

Yes,we have created the physical standby database successfully. 

Step:19 Then mount the database and open the database in the read only mode.check the name,open_mode,database_role in the standby database,in the role it should have the value as physical_standby. 

SQL> select name,open_mode,database_role from v$database;

NAME        OPEN_MODE              DATABASE_ROLE
--------- ---------------------   ----------------
PROD        READ ONLY             PHYSICAL STANDBY

Step:20 Check for the max(sequence#) from the v$archived_log view in both primary and standby databases.

SQL>  select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            8

Step:21 Apply the log files using MRP process.(in standby)

 SQL> alter database recover managed standby database disconnect from session;
 Database altered.

Step:22 We can check the difference in sequence using the following query.

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME )
IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME )
IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
SQL>

 Thread    Last Sequence Received   Last Sequence Applied  Difference
---------- ----------------------    --------------------- ----------
  1                 8                         8               0

 

Recommended Posts

Start typing and press Enter to search