CREATE AND CONFIGURE PHYSICAL STANDBY DATAGUARD 12C ORACLE
In primary,
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)
-------------
3There is only one thread as this is not RAC setup(Primary). In RAC, there may be multiple threads depending on the number of instances.
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. Specify the db unique name of primary and standby in the parameter.
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. Service name of primary and standby’s
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. Before configuration of tns and listener, disable firewall connection from source and destination.
[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{
sql 'alter system switch logfile';
backup current controlfile for standby format '/u01/stdctl.%T';
}
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
/
'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
/
'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_1Catalog 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;
}
EOFRun 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> exitRecover 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