CREATE AND CONFIGURE PHYSICAL STANDBY DATAGUARD 12C ORACLE

Enable force logging on your database

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
NO

If previous output is YES, then ignore this step

SQL> alter database force logging;

Database altered.

Check the archive log status on database

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

If previous step output is NOARCHIVELOG mode, then follow below steps

SHU IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ALTER SYSTEM SWITCH LOGFILE;
SELECT NAME FROM V$ARCHIVED_LOG;

Check the number of redolog group with their size

SQL> select group#,bytes/1024/1024 from v$log;

    GROUP# BYTES/1024/1024
---------- ---------------
         1              50
         2              50
         3              50

Check the number of threads used by the database instance

SQL> select thread#,instance from v$thread;

   THREAD# INSTANCE
---------- --------------------------------------------------------------------------------
         1 orcldgp

Formula to get the number of standby redologs on standby database.

( number of redolog group * number of threads in primary ) +1

Check the number of member in primary database

SQL> select count(member) from v$logfile;

COUNT(MEMBER)
-------------
            3

There is only one thread as this is not RAC setup(Primary)

SQL> select count(thread#) from v$thread;

COUNT(THREAD#)
--------------
             1

Apply the formula for calculating standby redolog count

SQL> select (3*1)+1 as standbylog_count from dual;

STANDBYLOG_COUNT
----------------
               4

Add standby redologs in standby database

SQL> alter database add standby logfile thread 1 '/oracle/base/oradata/orcldgp/stdbylog1.log' size 50m;

Database altered.


SQL> alter database add standby logfile thread 1 '/oracle/base/oradata/orcldgp/stdbylog2.log' size 50m;

Database altered.

SQL> alter database add standby logfile thread 1 '/oracle/base/oradata/orcldgp/stdbylog3.log' size 50m;

Database altered.

SQL> alter database add standby logfile thread 1 '/oracle/base/oradata/orcldgp/stdbylog4.log' size 50m;

Database altered.

Check db_unique_name parameter in standby. This name should be the original name of standby database

SQL> show parameter db_unique

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      orcldgp

Set log_archive_config parameter in primary

SQL> alter system set log_archive_config='DG_CONFIG=(ORCL,ORCLDGP)';

System altered.

Set log_archive_dest_2 parameter on primary

SQL> alter system set log_archive_dest_2='service=orcldgp async valid_for=(online_logfiles,primary_role) db_unique_name=orcldgp';

System altered.

Ensure that all log archive dest parameter are deferred before initial configuration

SQL> alter system set log_archive_dest_state_2=defer;

System altered.

SQL> alter system set log_archive_dest_state_3=defer;

System altered.

set fal server parameter on primary. As here we have two standby databases , i have set two service names. If you have only one standby ,then you can specify only one

SQL> alter system set fal_server=ORCLDG,ORCLDGP;

System altered.

Set remote login password file to exclusive(Primary)

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

System altered.

Create pfile from current spfile in primary

SQL> create pfile='/tmp/initorcldgp.ora' from spfile;

File created.

Set service_names parameter with all the services used in primary database

SQL> alter system set service_names='orcl','orcldg','orcldgp';

System altered.

Set tnsnames.ora file with the tns entry of primary and standby databases. Note that standby database has an additional entry (UR=A) for auxiliary connection

[grid@orcl ~]$ vi /grid/base/product/12.1.0/grid/network/admin/tnsnames.ora


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcl)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
ORCLDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.212)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg)(UR = A)
    )
  )

ORCLDGP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.200)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldgp)(UR = A)
    )
  )

Create a password file on standby database or copy the password file from primary to standby and modify the file name with standby SID (orapw +standby SID)

[oracle@orcldg ~]$ orapwd file='$ORACLE_HOME/dbs/orapwORCLDGP' password=oracle entries=10 force=y

Set tns entries in standby tnsnames.ora file

[grid@orcldg ~]$ vi /grid/base/product/12.1.0/grid/network/admin/tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.202)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
ORCLDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcldg)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg)(UR = A)
    )
  )


ORCLDGP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.200)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldgp)(UR = A)
    )
  )

We are not going to use RMAN duplication method to construct standby database. Rather we use conventional method. Take current controlfile backup from primary database.

You can determine the method to clone primary database for standby based on the following condition

  • If the primary and standby datacenter are close to each other, then prefer RMAN active duplication
  • If there is a good network bandwidth then prefer RMAN duplication

You can check the network bandwidth using traceroute and ping from primary with standby ip address. As per network bandwidth, we have to determine the rate at which the duplication run

[oracle@dc01x02 ~]$ traceroute 192.168.56.192
traceroute to 192.168.56.197 (192.168.56.192), 30 hops max, 60 byte packets
 1  192.168.56.192 (192.168.56.192)  0.303 ms  0.243 ms  0.495 ms

[oracle@dc01x02 ~]$ ping -c 5 192.168.56.192
PING 192.168.56.192 (192.168.56.192) 56(84) bytes of data.
64 bytes from 192.168.56.192: icmp_seq=1 ttl=64 time=0.479 ms
64 bytes from 192.168.56.192: icmp_seq=2 ttl=64 time=0.758 ms
64 bytes from 192.168.56.192: icmp_seq=3 ttl=64 time=1.05 ms
64 bytes from 192.168.56.192: icmp_seq=4 ttl=64 time=0.340 ms
64 bytes from 192.168.56.192: icmp_seq=5 ttl=64 time=0.778 ms

--- 192.168.56.192 ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4111ms
rtt min/avg/max/mdev = 0.340/0.681/1.051/0.249 ms

If you want to perform the duplication method to create a standby database, then follow the below link

[oracle@orcl /]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sat Jun 5 17:55:09 2021

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

connected to target database: ORCL (DBID=1559282684)

RMAN> run{
2> sql 'alter system switch logfile';
3> backup current controlfile for standby format '/u01/stdctl.%T';
4> }

using target database control file instead of recovery catalog
sql statement: alter system switch logfile

Starting backup at 05-JUN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 05-JUN-21
channel ORA_DISK_1: finished piece 1 at 05-JUN-21
piece handle=/u01/stdctl.20210605 tag=TAG20210605T232534 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 05-JUN-21

Starting Control File and SPFILE Autobackup at 05-JUN-21
piece handle=+FRA/ORCL/AUTOBACKUP/2021_06_05/s_1074468340.614.1074468343 comment=NONE
Finished Control File and SPFILE Autobackup at 05-JUN-21

As the backup file reside in ASM, i copy the controlfile backup piece to filesystem

ASMCMD> cp +FRA/ORCL/AUTOBACKUP/2021_06_05/s_1074468340.614.1074468343 /home/grid
copying +FRA/ORCL/AUTOBACKUP/2021_06_05/s_1074468340.614.1074468343 -> /home/grid/s_1074468340.614.1074468343

Copy the controlfile backup to standby database

[grid@orcl ~]$ scp s_1074468340.614.1074468343 oracle@192.168.56.212:/home/oracle/
The authenticity of host '192.168.56.212 (192.168.56.212)' can't be established.
RSA key fingerprint is 6b:1a:39:5a:bc:c2:55:27:cd:7b:e6:26:ba:ef:a3:c9.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.56.212' (RSA) to the list of known hosts.
oracle@192.168.56.212's password:
s_1074468340.614.1074468343                   100%   17MB  16.5MB/s   00:00

Copy the pfile from primary to standby database home

--on primary 
[oracle@orcl ~]$ scp /oracle/base/product/12.1.0/dbhome_1/dbs/initorcl.ora oracle@192.168.56.123:/oracle/base/product/12.1.0/dbhome_1/dbs


--on standby 
[oracle@orcldg dbs]$ mv /oracle/base/product/12.1.0/dbhome_1/dbs/initorcl.ora /oracle/base/product/12.1.0/dbhome_1/dbs/initorcldgp.ora

Modify the below parameters in standby pfile. If you have different directory structures , then you need to specify db_file_name_convert and log_file_name_convert .Set db file name convert and log file name convert parameters to handle different directory structures in standby database

*.db_name='ORCL'
*.db_unique_name='ORCLDGP'
*.db_file_name_convert='+DATA','/oracle/base/oradata/orcldgp/'
*.log_file_name_convert='+DATA','/oracle/base/oradata/orcldgp/'

Startup the database in nomount mode using pfile

SQL> startup nomount pfile='/oracle/base/product/12.1.0/dbhome_1/dbs/initorcldgp.ora';
ORACLE instance started.

Total System Global Area 1509949440 bytes
Fixed Size                  2924640 bytes
Variable Size             956305312 bytes
Database Buffers          536870912 bytes
Redo Buffers               13848576 bytes

In standby, restore the standby controlfile backup which was taken from primary

[oracle@orcldg ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jun 6 00:04:33 2021

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

connected to target database: ORCLDGP (not mounted)

RMAN> restore standby controlfile from '/home/oracle/s_1074468340.614.1074468343';

Starting restore at 06-JUN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/oracle/base/oradata/orcldgp/control01.ctl
output file name=/u01/orcldgp/control02.ctl
Finished restore at 06-JUN-21

Generate a script to display all commands for restore of datafile to different directory

SQL> select 'set newname for datafile '||file_id||' to '||file_name||' ;'from dba_data_files
  2  /

'SETNEWNAMEFORDATAFILE'||FILE_ID||'TO'||FILE_NAME||';'
--------------------------------------------------------------------------------
set newname for datafile 2 to +DATA/ORCL/DATAFILE/system.265 ;
set newname for datafile 1 to +DATA/ORCL/DATAFILE/system.257.1031501823 ;
set newname for datafile 3 to +DATA/ORCL/DATAFILE/sysaux.256.1031501729 ;
set newname for datafile 4 to +DATA/ORCL/DATAFILE/undotbs1.259.1031501899 ;
set newname for datafile 8 to +DATA/ORCL/DATAFILE/users.274.1040915137 ;
set newname for datafile 7 to +DATA/ORCL/DATAFILE/users.272.1040745563 ;
set newname for datafile 6 to +DATA/ORCL/users.new ;
set newname for datafile 5 to +DATA/ORCL/DATAFILE/example.265.1031501993 ;
set newname for datafile 9 to /home/oracle/rcat.tbs ;

9 rows selected.

SQL> select 'set newname for tempfile '||file_id||' to '||file_name||' ;'from dba_temp_files
  2  /

'SETNEWNAMEFORTEMPFILE'||FILE_ID||'TO'||FILE_NAME||';'
--------------------------------------------------------------------------------
set newname for tempfile 1 to +DATA/ORCL/TEMPFILE/temp.264.1031501981 ;

Take full backup of database either incremental level 0 or full consistent backup from your primary database

rman> backup current controlfile;
rman> backup as compressed backupset database;

Copy backup pieces to standby

[oracle@orcl ~]$ scp /tmp/s_1075683731.549.1075683733 oracle@192.168.56.212:/tmp

oracle@192.168.56.212's password:
s_1075683731.549.1075683733                   100%   17MB  16.5MB/s   00:01

Check db name and db unique name once if they are set properly. If you find any mistakes correct them. Note that ‘db name‘ should be primary db and ‘db unique name‘ should be standby db

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      ORCL


SQL> alter system set db_name=orcl scope=spfile;

System altered.

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      ORCL

SQL> alter system set db_unique_name=orcldgp scope=spfile;

System altered.

Mount the database in standby mode on your standby database

RMAN> alter database mount standby database;

Statement processed
released channel: ORA_DISK_1

Catalog the backuppieces to make RMAN recognize the tags on disks

RMAN> catalog start with '/u01/backuppiece';

searching for all files that match the pattern /u01/backuppiece

List of Files Unknown to the Database
=====================================
File Name: /u01/backuppiece/nnndf0_TAG20210620T005353_0.461.1075683233
File Name: /u01/backuppiece/ncnnf0_TAG20210620T005345_0.533.1075683229
File Name: /u01/backuppiece/nnndf0_TAG20210620T005353_0.330.1075683605
File Name: /u01/backuppiece/nnndf0_TAG20210620T005353_0.530.1075683509
File Name: /u01/backuppiece/nnndf0_TAG20210620T005353_0.287.1075683685
File Name: /u01/backuppiece/nnndf0_TAG20210620T005353_0.540.1075683661

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/backuppiece/nnndf0_TAG20210620T005353_0.461.1075683233
File Name: /u01/backuppiece/ncnnf0_TAG20210620T005345_0.533.1075683229
File Name: /u01/backuppiece/nnndf0_TAG20210620T005353_0.330.1075683605
File Name: /u01/backuppiece/nnndf0_TAG20210620T005353_0.530.1075683509
File Name: /u01/backuppiece/nnndf0_TAG20210620T005353_0.287.1075683685
File Name: /u01/backuppiece/nnndf0_TAG20210620T005353_0.540.1075683661

Validate the size of all datafiles on primary for capacity planning on standby database

SQL> select sum(bytes/1073741824) as SIZEGB from v$datafile;

              SIZEGB
---------------------
            11.126709

Verify if sufficient space in standby database exist to accomodate the database files

[oracle@orcldg ~]$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sdd1        20G   11G  7.6G  59% /
tmpfs           1.9G  985M  865M  54% /dev/shm
/dev/sdd5        15G  9.3G  4.5G  68% /grid
/dev/sdd2        15G  9.2G  4.6G  67% /oracle
/dev/sde         12G  1.9G  9.2G  18% /u01

Create shell script to execute RMAN restore. Note that if the standby directory structure is different from primary ,then ‘set newname’ clause should be used or else the lines with “set newname” can be removed.

[oracle@orcldg ~]$ cat rman_restore.sh
rman target sys/password debug trace=/tmp/rmanlog << EOF > /home/oracle/rman.log

run
{
set newname for datafile 1 to '/u01/ORCLDGP/datafile/system.257.1031501823';
set newname for datafile 2 to '/u01/ORCLDGP/datafile/system.265';
set newname for datafile 3 to '/u01/ORCLDGP/datafile/sysaux.256.1031501729';
set newname for datafile 4 to '/u01/ORCLDGP/datafile/undotbs1.259.1031501899';
set newname for datafile 5 to '/oracle/example.265.1031501993';
set newname for datafile 6 to '/oracle/users.new';
set newname for datafile 7 to '/home/oracle/users.272.1040745563';
set newname for datafile 8 to '/home/oracle/users.274.1040915137';
set newname for datafile 9 to '/home/oracle/rcat.tbs';
restore database;
switch datafile all;
}
EOF

Run the script in nohup mode

[oracle@orcldg ~]$ nohup sh rman_restore.sh &
[1] 11240
[oracle@orcldg ~]$ nohup: ignoring input and appending output to `nohup.out'

Monitor the restore

[oracle@orcldg ~]$ tail -f rman.log

RMAN-03090: Starting restore at 20-JUN-21
RMAN-06009: using target database control file instead of recovery catalog
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08500: channel ORA_DISK_1: SID=21 device type=DISK

RMAN-08016: channel ORA_DISK_1: starting datafile backup set restore
RMAN-08089: channel ORA_DISK_1: specifying datafile(s) to restore from backup set
RMAN-08610: channel ORA_DISK_1: restoring datafile 00004 to /u01/ORCLDGP/datafile/undotbs1.259.1031501899
RMAN-08003: channel ORA_DISK_1: reading from backup piece /u01/nnndf0_TAG20210620T005353_0.461.1075683233

Wait until completion of restore

RMAN-08610: channel ORA_DISK_1: restoring datafile 00003 to oracle/sysaux.256.10                                      31501729
RMAN-08023: channel ORA_DISK_1: restored backup piece 1
RMAN-08180: channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
RMAN-03091: Finished restore at 21-JUN-21

RMAN>

Take archivelog backup from primary as we have inconsistent backup.Copy the archivelog backuppiece to standby

RMAN> backup archivelog all;

Catalog the backup of archivelog in standby database

RMAN> catalog start with '/tmp';

Apply the archives recover the database till the last sequence

RMAN> recover database;

Starting recover at 21-JUN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK

starting media recovery

archived log for thread 1 with sequence 345 is already on disk as file /tmp/thread_1_seq_345.561.1075831911
archived log for thread 1 with sequence 353 is already on disk as file /tmp/thread_1_seq_353.454.1075767321
archived log for thread 1 with sequence 354 is already on disk as file /tmp/thread_1_seq_354.462.1075817169
archived log for thread 1 with sequence 355 is already on disk as file /tmp/thread_1_seq_355.484.1075818959
archived log for thread 1 with sequence 356 is already on disk as file /tmp/thread_1_seq_356.510.1075820759
archived log for thread 1 with sequence 357 is already on disk as file /tmp/thread_1_seq_357.573.1075822559
archived log for thread 1 with sequence 358 is already on disk as file /tmp/thread_1_seq_358.418.1075824359
archived log for thread 1 with sequence 359 is already on disk as file /tmp/thread_1_seq_359.538.1075826159
archived log for thread 1 with sequence 360 is already on disk as file /tmp/thread_1_seq_360.539.1075827959
archived log for thread 1 with sequence 361 is already on disk as file /tmp/thread_1_seq_361.563.1075829639
archived log for thread 1 with sequence 362 is already on disk as file /tmp/thread_1_seq_362.578.1075829849
archived log for thread 1 with sequence 363 is already on disk as file /tmp/thread_1_seq_363.359.1075831647
archived log for thread 1 with sequence 364 is already on disk as file /tmp/thread_1_seq_364.464.1075832691
archived log for thread 1 with sequence 365 is already on disk as file /tmp/thread_1_seq_365.377.1075832711
archived log for thread 1 with sequence 366 is already on disk as file /tmp/thread_1_seq_366.566.1075832727
archived log file name=/tmp/thread_1_seq_345.561.1075831911 thread=1 sequence=345
unable to find archived log
archived log thread=1 sequence=346
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/21/2021 18:58:09
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 346 and starting SCN of 14768906

RMAN> exit

Recover the database until cancel using backup controlfile

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 14768906 generated at 06/20/2021 01:05:53 needed for thread 1
ORA-00289: suggestion : /u01/ORCLDGP/archivelog/2021_06_21/o1_mf_1_346_%u_.arc
ORA-00280: change 14768906 for thread 1 is in sequence #346


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

Enable log archive dest parameter on primary

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

Enable log_archive_config parameter

SQL> alter system set log_archive_config='DG_CONFIG=(ORCL,ORCLDG,ORCLDGP)';

System altered.

Set standby file management to auto on standby

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

Check the standby logfiles on standby. You need standby redologs for switchover purpose

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

    GROUP#    THREAD#  SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
        10          1          0 YES UNASSIGNED
        11          1          0 YES UNASSIGNED
        12          1          0 YES UNASSIGNED
        13          1          0 YES UNASSIGNED

Check the count of standby redologs on standby

SQL> SELECT TYPE, count(*) FROM V$LOGFILE GROUP BY TYPE;

TYPE      COUNT(*)
------- ----------
ONLINE           3
STANDBY          8

Verify the duplicate standby logs which were created during restore

SQL> select GROUP#, MEMBER from V$LOGFILE WHERE TYPE='STANDBY' ORDER BY 1,2;

 GROUP# MEMBER
------- --------------------
     10 +DATA/ORCL/ONLINELOG
        /group_10.267.103150
        4655

     10 +FRA/ORCL/ONLINELOG/
        group_10.260.1031504
        657

     11 +DATA/ORCL/ONLINELOG
        /group_11.268.103150
        4661

     11 +FRA/ORCL/ONLINELOG/
        group_11.261.1031504
        665

     12 +DATA/ORCL/ONLINELOG
        /group_12.269.103150
        4669

     12 +FRA/ORCL/ONLINELOG/
        group_12.262.1031504
        671

     13 +DATA/ORCL/ONLINELOG
        /group_13.270.103150
        4675

     13 +FRA/ORCL/ONLINELOG/
        group_13.263.1031504
        679


8 rows selected.

Drop the duplicate redologs as they are no longer required

SQL> alter database drop logfile member '+FRA/ORCL/ONLINELOG/group_10.260.1031504657';

Database altered.

SQL> alter database drop logfile member '+FRA/ORCL/ONLINELOG/group_11.261.1031504665';

Database altered.

SQL> alter database drop logfile member '+FRA/ORCL/ONLINELOG/group_12.262.1031504671';

Database altered.

SQL> alter database drop logfile member '+FRA/ORCL/ONLINELOG/group_13.263.1031504679';

Database altered.

Set fal client and fal server parameter on primary

SQL> alter system set fal_client=orcl;

System altered.

SQL> alter system set fal_server=orcldgp;

System altered.

Enable MRP on standby



SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

Check the message of replication status from primary

SQL> select message from v$dataguard_status;

MESSAGE
--------------------------------------------------------------------------------
WARNING: STANDBY_FILE_MANAGEMENT initialization parameter is
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC2: Becoming the heartbeat ARCH
ARC2: Becoming the active heartbeat ARCH
ARC3: Archival started
Managed Standby Recovery not using Real Time Apply
Managed Standby Recovery not using Real Time Apply
Attempt to start background Managed Standby Recovery process

MESSAGE
--------------------------------------------------------------------------------
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery starting Real Time Apply
Clearing online redo logfile 1 +DATA/ORCL/ONLINELOG/group_1.261.1031501951
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 +DATA/ORCL/ONLINELOG/group_2.262.1031501957
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 +DATA/ORCL/ONLINELOG/group_3.263.1031501965
Clearing online redo logfile 3 complete
Media Recovery Waiting for thread 1 sequence 345 branch(resetlogs_id) 1054563556
FAL[client]: Error fetching gap sequence, no FAL server specified
Primary database is in MAXIMUM PERFORMANCE mode

MESSAGE
--------------------------------------------------------------------------------
RFS[1]: Assigned to RFS process (PID:18054)
RFS[1]: No standby redo logfiles available for T-1
RFS[1]: No standby redo logfiles available for T-1
RFS[2]: Assigned to RFS process (PID:18062)
Media Recovery Log /u01/ORCLDGP/archivelog/2021_06_21/o1_mf_1_345_jf1kw6fq_.arc
Media Recovery Waiting for thread 1 sequence 1
RFS[3]: Assigned to RFS process (PID:18064)
RFS[4]: Assigned to RFS process (PID:18067)
Error 12154 received logging on to the standby
FAL[client, MRP0]: Error 12154 connecting to ORCL for fetching gap sequence
Fetching gap sequence in thread 1, gap sequence 1-1

MESSAGE
--------------------------------------------------------------------------------
RFS[1]: No standby redo logfiles available for T-1

34 rows selected.

Do some manual log switches from primary

SQL> alter system switch logfile;

System altered.

Verify the sync of log sequence from standby

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
MRP0      WAIT_FOR_GAP          1          1          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  1        379          7          1
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0

9 rows selected.

Try to connect from primary with service name which should not throw tns error

[oracle@orcl ~]$ sqlplus test/password@orcldgp as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 21 17:30:53 2021

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL>

Configure archivelog deletion policy in standby database to periodically delete applied archives from standby to release FRA space

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters are successfully stored

Finally recreate the tempfiles as well

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s