Theory:
There are three possible/supported redo transport configuration modes:
1. Maximum Protection
This protection mode guarantees that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover each transaction must be written to both the local online redo log and to a standby redo log on at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down if a fault prevents it from writing its redo stream to at least one synchronized standby database.
Note: Because this data protection mode prioritizes data protection over primary database availability, Oracle recommends that a minimum of two standby databases be used to protect a primary database that runs in maximum protection mode to prevent a single standby database failure from causing the primary database to shut down.
2. Maximum Availability
This protection mode provides the highest level of data protection that is possible without affecting the availability of the primary database. Like maximum protection mode, transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one synchronized standby database. Unlike maximum protection mode, the primary database will not shut down if a fault prevents it from writing its redo stream to a synchronized standby database. Instead, the primary database will operate in RESYNCHRONIZATION until the fault is corrected and all log gaps have been resolved. When all log gaps have been resolved, the primary database automatically resumes operating in maximum availability mode.
3. Maximum Performance
This protection mode provides the highest level of data protection that is possible without affecting the performance of the primary database. This is accomplished by allowing a transaction to commit as soon as the redo data needed to recover that transaction is written to the local redo log. The primary database’s redo data stream is also written to at least one standby database, but that redo stream is written asynchronously with respect to the commitment of the transactions that create the redo data.
When network links with sufficient bandwidth and latency are used, this mode provides a level of data protection that approaches that of maximum availability mode with minimal impact on primary database performance.
Note: This is the default protection mode.
Max. Protection
|
Max. Availability
|
Max. Performance
|
|
Redo Archival Process
|
LGWR
|
LGWR
|
LGWR or ARCH
|
Network Transnission
|
SYNC
|
SYNC
|
ASYNC (when
using LGWR only)
|
Disk write option
|
AFFIRM
|
AFFIRM / NOAFFIRM
|
NOAFFIRM
|
Standby Redo logs
|
Yes
|
Yes
|
No, but
recommended and required for Real-Time Apply
|
What is Standby Redo Logs (SRL) and why we need them?
Redo transport scheme with NO SRL:
1. A transaction writes redo records into the Log Buffer in the System Global Area (SGA).
2. The Log Writer process (LGWR) writes redo records from the Log Buffer to the Online Redo Logs (ORLs).
3. When the ORL switches to the next log sequence (normally when the ORL fills up), the Archiver process (ARC0) will copy the ORL to the Archived Redo Log.
4. Because a standby database exists, a second Archiver process (ARC1) will read from a completed Archived Redo Log and transmit the redo over the network to the Remote File Server (RFS) process running for the standby instance.
5. RFS sends the redo stream to the local Archiver process (ARCn).
6. ARCn then writes the redo to the archived redo log location on the standby server.
7. Once the archived redo log is completed, the Managed Recovery Process (MRP0) sends the redo to the standby instance for applying the transaction.
Redo transport scheme with SRL:
1. Just like without SRLs, a transaction generates redo in the Log Buffer in the SGA.
2. The LGWR process writes the redo to the ORL.
3. Are we in Max Protect or Max Performance mode?
a. If Max Protect, then we are performing SYNC redo transport. The Network Server SYNC process (NSSn) is a slave process to LGWR. It ships redo to the RFS process on the standby server.
b. If Max Performance mode, then we are performing ASYNC redo transport. The Network Server ASYNC process (NSAn) reads from the ORL and transports the redo to the RFS process on the standby server.
4. RFS on the standby server simply writes the redo stream directly to the SRLs.
5. How the redo gets applied depends if we are using Real Time Apply or not.
a. If we are using Real Time Apply, MRP0 will read directly from the SRLs and apply the redo to the standby database.
b. If we are not using Real Time Apply, MRP0 will wait for the SRL’s contents to be archived and then once archived and once the defined delay has elapsed, MRP0 will apply the redo to the standby database.
Note: The NSSn and NSAn processes are new to Oracle 12c. In prior versions, a singular process, LNS performed this job.
Here are some best practices related to SRL:
- Make sure your ORL groups all have the same exact size. You want every byte in the ORL to have a place in its corresponding SRL.
- Create the SRLs with the same exact byte size as the ORL groups. If they can’t be the same exact size, make sure they are bigger than the ORLs.
- Do not assign the SRLs to any specific thread. That way, the SRLs can be used by any thread, even with Oracle RAC primary databases.
- When you create SRLs in the standby, create SRLs in the primary. They will normally never be used. But one day you may perform a switchover operation. When you do switchover, you want the old primary, now a standby database, to have SRLs. Create them at the same time.
- For an Oracle RAC primary database, create the number of SRLs equal to the number of ORLs in all primary instances. For example, if you have a 3-node RAC database with 4 ORLs in each thread, create 12 SRLs (3x4) in your standby. No matter how many instances are in your standby, the standby needs enough SRLs to support all ORLs in the primary, for all instances.
Hostname
|
SID
|
DB_UNIQUE_NAME
|
|
Primary
|
srv-dg-db1.oracle.com
|
PROD
|
PROD
|
Standby
|
srv-dg-db2.oracle.com
|
PROD
|
PROD_STBY
|
At this moment PROD database is 19.6 version up and running.
On standby server only database software (ORACLE_HOME) is ready.
Note: environment below is configured in Max. Performance mode with Real-Time Apply.
Note: environment below is configured in Max. Performance mode with Real-Time Apply.
###################################
Network connectivity:
###################################
>>> oracle@srv-dg-db1 /// srv-dg-db2
$ cat /etc/hosts
++++++++++
192.168.197.179 srv-dg-db1.oracle.com srv-dg-db1
192.168.197.180 srv-dg-db2.oracle.com srv-dg-db2
++++++++++
$ chkconfig --list iptables
$ chkconfig iptables off
$ chkconfig --list iptables
$ service iptables stop
/// OR ///
$ systemctl stop firewalld
$ systemctl disable firewalld
###################################
Oracle Net configuration:
###################################
>>> oracle@srv-dg-db1
$ cat $ORACLE_HOME/network/admin/tnsnames.ora:
++++++++++
PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srv-dg-db1.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD)
(INSTANCE_NAME = PROD)
)
)
PROD_STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srv-dg-db2.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD)
(INSTANCE_NAME = PROD)
)
)
++++++++++
$ cat $ORACLE_HOME/network/admin/listener.ora
++++++++++
LISTENER_PROD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srv-dg-db1.oracle.com)(PORT = 1521))
)
)
SID_LIST_LISTENER_PROD =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = PROD)
)
)
++++++++++
>>> oracle@srv-dg-db2
$ cat $ORACLE_HOME/network/admin/tnsnames.ora:
++++++++++
PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srv-dg-db1.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD)
(INSTANCE_NAME = PROD)
)
)
PROD_STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srv-dg-db2.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD)
(INSTANCE_NAME = PROD)
)
)
++++++++++
$ cat $ORACLE_HOME/network/admin/listener.ora
++++++++++
LISTENER_PROD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srv-dg-db2.oracle.com)(PORT = 1521))
)
)
SID_LIST_LISTENER_PROD =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = PROD)
)
)
++++++++++
###################################
Put PRIMARY in ARCHIVELOG mode:
###################################
>>> oracle@srv-dg-db1
SQL> shu immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
###################################
Put PRIMARY in force logging mode:
###################################
>>> oracle@srv-dg-db1
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
Note: The FORCE LOGGING option is the safest method to ensure that all the changes made in the database will be captured and available for recovery in the redo logs.
##########################################################
Set parameters on PRIMARY:
##########################################################
>>> oracle@srv-dg-db1
SQL> alter system set log_archive_config='dg_config=(PROD,PROD_STBY)';
SQL> alter system set log_archive_dest_state_2=DEFER;
SQL> alter system set log_archive_dest_2='SERVICE=PROD_STBY LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD_STBY';
SQL> alter system set standby_file_management=AUTO;
Note: This is related to data/undo datafiles only. Temp files will NOT be created automatically on standby side after adding new ones on primary. Ref. to 1514588.1.
Note: More about LOG_ARCHIVE_DEST_2 possible settings - https://docs.oracle.com/cd/B28359_01/server.111/b28294/log_arch_dest_param.htm#i104031
SQL> alter system set fal_server=PROD_STBY;
SQL> alter system set fal_client=PROD;
Note: FAL = Fetch Archive Log. When MRP finds that an archive log is missing during media recovery, it sends the fal_client information to the server identified by fal_server and requests fal_server to resend the file again. The fal_client and fal_server init.ora (spfile) parameters are set on the standby instance. But in my case I also set these parameters on primary database in case of switchover/failover.
##########################################################
Create / copy password file:
##########################################################
>>> oracle@srv-dg-db1
$ orapwd file=$ORACLE_HOME/dbs/orapwPROD password=****** format=12 force=y
$ scp $ORACLE_HOME/dbs/orapwPROD oracle@srv-dg-db2:$ORACLE_HOME/dbs/orapwPROD
Note: If your standby database SID is not the same as on primary then you have to rename password file on standby server: mv orapwPROD orapw<<<STANDBY DB SID>>>
##########################################################
Create standby redo logs on PRIMARY:
##########################################################
>>> oracle@srv-dg-db1
SQL> alter database add standby logfile group 4 ('/redo1/PROD/redo01a_STBY.log','/redo2/PROD/redo01b_STBY.log') size 512M;
SQL> alter database add standby logfile group 5 ('/redo1/PROD/redo02a_STBY.log','/redo2/PROD/redo02b_STBY.log') size 512M;
SQL> alter database add standby logfile group 6 ('/redo1/PROD/redo03a_STBY.log','/redo2/PROD/redo03b_STBY.log') size 512M;
##########################################################
Create parameter file for STANDBY:
##########################################################
>>> oracle@srv-dg-db2
$ cat $ORACLE_HOME/dbs/initPROD.ora
++++++++++
db_name='PROD'
db_unique_name='PROD_STBY'
control_files='/data/PROD_STBY/control01.ctl','/redo1/PROD_STBY/control02.ctl','/redo2/PROD_STBY/control03.ctl'
audit_file_dest='/u01/app/oracle/admin/PROD_STBY/adump'
sga_max_size=5264M
sga_target=5264M
pga_aggregate_limit=3500M
pga_aggregate_target=1750M
db_file_name_convert = ('/data/PROD/','/data/PROD_STBY/')
log_file_name_convert = ('/redo1/PROD/','/redo1/PROD_STBY/','/redo2/PROD/','/redo2/PROD_STBY/')
log_archive_config='dg_config=(PROD,PROD_STBY)'
log_archive_dest_1='LOCATION=/arch'
log_archive_dest_2='SERVICE=PROD LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD'
log_archive_dest_state_2='DEFER'
log_archive_format='log%t_%s_%r.arc'
fal_server='PROD'
fal_client='PROD_STBY'
standby_file_management='MANUAL'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
++++++++++
$ mkdir -p /u01/app/oracle/admin/PROD_STBY/adump /data/PROD_STBY /redo1/PROD_STBY /redo2/PROD_STBY
SQL> create spfile from pfile;
##########################################################
Start STANDBY:
##########################################################
>>> oracle@srv-dg-db2
SQL> startup nomount;
SQL> select instance_name, status from v$Instance;
INSTANCE_NAME STATUS
---------------- ------------
PROD STARTED
##########################################################
RMAN duplicate:
##########################################################
Note: you can also use backup to create standby database according to notes: 469493.1, 836986.1.
>>> oracle@srv-dg-db2
$ lsnrctl start LISTENER_PROD
$ tnsping PROD
++++++++++
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 11-MAR-2020 14:50:43
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = srv-dg-db1.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD) (INSTANCE_NAME = PROD)))
OK (0 msec)
++++++++++
$ tnsping PROD_STBY
++++++++++
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 11-MAR-2020 14:51:22
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = srv-dg-db2.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD) (INSTANCE_NAME = PROD)))
OK (0 msec)
++++++++++
>>> oracle@srv-dg-db1
$ tnsping PROD_STBY
++++++++++
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 11-MAR-2020 14:52:12
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = srv-dg-db2.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD) (INSTANCE_NAME = PROD)))
OK (10 msec)
++++++++++
>>> oracle@srv-dg-db2
$ rman
$ connect target sys/***@PROD
$ connect auxiliary sys/***@PROD_STBY
RMAN> run {
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate auxiliary channel s1 type disk;
allocate auxiliary channel s2 type disk;
duplicate target database for standby from active database dorecover nofilenamecheck;
}
##########################################################
Set standby_file_management=AUTO on STANDBY:
##########################################################
>>> oracle@srv-dg-db2
SQL> set lines 222
set pages 999
col member for a65
select * from v$logfile order by 1;
++++++++++
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ----------------------------------------------------------------- --- ----------
1 ONLINE /redo2/PROD_STBY/redo01b.log NO 0
1 ONLINE /redo1/PROD_STBY/redo01a.log NO 0
2 ONLINE /redo1/PROD_STBY/redo02a.log NO 0
2 ONLINE /redo2/PROD_STBY/redo02b.log NO 0
3 ONLINE /redo2/PROD_STBY/redo03b.log NO 0
3 ONLINE /redo1/PROD_STBY/redo03a.log NO 0
4 STANDBY /redo1/PROD_STBY/redo01a_STBY.log NO 0
4 STANDBY /redo2/PROD_STBY/redo01b_STBY.log NO 0
5 STANDBY /redo1/PROD_STBY/redo02a_STBY.log NO 0
5 STANDBY /redo2/PROD_STBY/redo02b_STBY.log NO 0
6 STANDBY /redo1/PROD_STBY/redo03a_STBY.log NO 0
6 STANDBY /redo2/PROD_STBY/redo03b_STBY.log NO 0
12 rows selected.
++++++++++
SQL> alter system set standby_file_management=AUTO scope=both;
##########################################################
Start redo apply in real time mode on STANDBY:
##########################################################
>>> oracle@srv-dg-db1
SQL> alter system set log_archive_dest_state_2='ENABLE';
>>> oracle@srv-dg-db2
SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database disconnect from session using current logfile;
Note: "disconnect from session" just starts recover in background. It will not disconnect your current SQL Plus session.
SQL> SET LINES 180
col DEST_NAME for a30
select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;
++++++++++
DEST_ID DEST_NAME STATUS TYPE SRL RECOVERY_MODE
---------- ------------------------------ --------- ---------------- --- ----------------------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL NO MANAGED REAL TIME APPLY
++++++++++
SQL> select protection_mode, protection_level from v$database;
++++++++++
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
++++++++++
Note: standby redo logs are populated with redo information as fast as the primary redo logs, rather than waiting for the redo log to be archived and shipped to the standby. This results in faster switchover and failover times because the standby redo log files have been applied already to the standby database by the time the failover or switchover begins.Oracle recommends the below formula to calculate the number of Standby redo logs file as == (maximum number of logfiles for each thread + 1) * maximum number of threads.
>>> oracle@srv-dg-db1
SQL> select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log
where first_time between (sysdate-1) and (sysdate+1)
group by thread# order by 1;
++++++++++
THREAD# Last Primary Seq Generated
---------- --------------------------
1 25
++++++++++
>>> oracle@srv-dg-db2
SQL> select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log
group by thread# order by 1;
++++++++++
THREAD# Last Standby Seq Received
---------- -------------------------
1 25
++++++++++
SQL> select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log where applied='YES'
group by thread# order by 1;
++++++++++
THREAD# Last Standby Seq Applied
---------- ------------------------
1 25
++++++++++
SQL> SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;
++++++++++
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
RFS IDLE 26 1199 1 0
RFS IDLE 0 0 0 0
MRP0 APPLYING_LOG 26 1199 1048576 0
9 rows selected.
++++++++++
##########################################################
STOP procedure:
##########################################################
>>> oracle@srv-dg-db1 [PRIMARY]
SQL> alter system set log_archive_dest_state_2=defer;
>>> oracle@srv-dg-db2 [STANDBY]
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> shu immediate;
$ lsnrctl stop LISTENER_PROD
##########################################################
START procedure:
##########################################################
>>> oracle@srv-dg-db2 [STANDBY]
$ lsnrctl start LISTENER_PROD
SQL> startup nomount;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> alter database recover managed standby database cancel;
SQL> ALTER DATABASE OPEN READ ONLY;
SQL> alter database recover managed standby database disconnect from session using current logfile;
>>> oracle@srv-dg-db1 [PRIMARY]
SQL> alter system set log_archive_dest_state_2=ENABLE;
##########################################################
Change Protection mode example:
##########################################################
===> Max. Performance with Real-Time Apply
Primary === log_archive_dest_2 = SERVICE=PROD_STBY LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD_STBY
Standby === log_archive_dest_2 = SERVICE=PROD LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD
>>> oracle@srv-dg-db1
SQL> archive log list;
++++++++++
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch
Oldest online log sequence 26
Next log sequence to archive 28
Current log sequence 28
++++++++++
SQL> create table test_dg (Name varchar2(30));
Table created.
SQL> insert into test_dg values ('Real Time Apply Mode');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test_dg;
++++++++++
NAME
------------------------------
Real Time Apply Mode
++++++++++
SQL> archive log list;
++++++++++
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch
Oldest online log sequence 26
Next log sequence to archive 28
Current log sequence 28
++++++++++
>>> oracle@srv-dg-db2
SQL> SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY WHERE PROCESS like 'MRP%';
++++++++++
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ---------- ----------
MRP0 APPLYING_LOG 28 7801 1048576 0
++++++++++
SQL> select recovery_mode from v$archive_dest_status where dest_id=1;
++++++++++
RECOVERY_MODE
----------------------------------
MANAGED REAL TIME APPLY
++++++++++
SQL> select * from test_dg;
++++++++++
NAME
------------------------------
Real Time Apply Mode
++++++++++
Note: redo switch on primary is not required to get changes on standby side. That is why it calls "Real-Time Apply".
===> Max. Performance (NO real-time apply)
>>> oracle@srv-dg-db1
SQL> alter system set log_archive_dest_state_2=DEFER;
System altered.
>>> oracle@srv-dg-db2
Note: Let's drop SRL on standby server (this is not fully required, because we can start just start recover in NO real-time apply mode, but to be clear in this experiment I will do it).
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> set lines 222
set pages 999
col member for a75
select * from v$logfile order by 1;
++++++++++
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------------------------------------------------------------------- --- ----------
1 ONLINE /redo2/PROD_STBY/redo01b.log NO 0
1 ONLINE /redo1/PROD_STBY/redo01a.log NO 0
2 ONLINE /redo1/PROD_STBY/redo02a.log NO 0
2 ONLINE /redo2/PROD_STBY/redo02b.log NO 0
3 ONLINE /redo2/PROD_STBY/redo03b.log NO 0
3 ONLINE /redo1/PROD_STBY/redo03a.log NO 0
4 STANDBY /redo1/PROD_STBY/redo01a_STBY.log NO 0
4 STANDBY /redo2/PROD_STBY/redo01b_STBY.log NO 0
5 STANDBY /redo1/PROD_STBY/redo02a_STBY.log NO 0
5 STANDBY /redo2/PROD_STBY/redo02b_STBY.log NO 0
6 STANDBY /redo1/PROD_STBY/redo03a_STBY.log NO 0
6 STANDBY /redo2/PROD_STBY/redo03b_STBY.log NO 0
12 rows selected.
++++++++++
SQL> alter database clear logfile group 4;
SQL> alter database drop logfile group 4;
SQL> !rm -f /redo1/PROD_STBY/redo01a_STBY.log /redo2/PROD_STBY/redo01b_STBY.log
SQL> alter database clear logfile group 5;
SQL> alter database drop logfile group 5;
SQL> !rm -f /redo1/PROD_STBY/redo02a_STBY.log /redo2/PROD_STBY/redo02b_STBY.log
SQL> alter database clear logfile group 6;
SQL> alter database drop logfile group 6;
SQL> !rm -f /redo1/PROD_STBY/redo03a_STBY.log /redo2/PROD_STBY/redo03b_STBY.log
>>> oracle@srv-dg-db2
SQL> alter database recover managed standby database disconnect from session using current logfile;
Note: Above statement is actually used for RTA, but in this case we don't have SRL and oracle will ignore it and work in same way if we just asked: alter database recover managed standby database disconnect from session;
Database altered.
No any errors as you can see.
SQL> select name, database_role, open_mode from v$database;
++++++++++
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
PROD PHYSICAL STANDBY READ ONLY WITH APPLY
++++++++++
>>> oracle@srv-dg-db1
SQL> alter system set log_archive_dest_state_2=ENABLE;
SQL> archive log list;
++++++++++
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch
Oldest online log sequence 28
Next log sequence to archive 30
Current log sequence 30
++++++++++
SQL> insert into test_dg values ('Try RTA with no SRL on standby');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test_dg;
++++++++++
NAME
------------------------------
Real Time Apply Mode
Try RTA with no SRL on standby
++++++++++
SQL> archive log list;
++++++++++
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch
Oldest online log sequence 28
Next log sequence to archive 30
Current log sequence 30
++++++++++
Note: Let's check on standby side.
>>> oracle@srv-dg-db2
SQL> select * from test_dg;
++++++++++
NAME
------------------------------
Real Time Apply Mode
++++++++++
Note: changes not applied on standby. Here are some information from standby alert log file:
++++++++++
...
...
...
2020-03-14T05:25:36.478413-07:00
Completed: alter database recover managed standby database disconnect from session using current logfile
2020-03-14T05:27:48.272035-07:00
rfs (PID:7496): Opened log for T-1.S-29 dbid 446522174 branch 1034683841
2020-03-14T05:27:48.459087-07:00
rfs (PID:7496): Archived Log entry 6 added for B-1034683841.T-1.S-29 ID 0x1a9df13e LAD:2
2020-03-14T05:27:48.788084-07:00
rfs (PID:7498): Primary database is in MAXIMUM PERFORMANCE mode
rfs (PID:7498): No SRLs created
2020-03-14T05:27:48.833814-07:00
rfs (PID:7498): Opened log for T-1.S-30 dbid 446522174 branch 1034683841
2020-03-14T05:27:49.295643-07:00
PR00 (PID:7324): Media Recovery Log /arch/log1_29_1034683841.arc
PR00 (PID:7324): Media Recovery Waiting for T-1.S-30 (in transit)
++++++++++
It's waiting for 30 redo sequence to archived and shipped on standby, then standby MRP process will apply it.
SQL> SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY WHERE PROCESS like 'MRP%';
++++++++++
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ---------- ----------
MRP0 WAIT_FOR_LOG 30 0 0 0
++++++++++
SQL> select recovery_mode from v$archive_dest_status where dest_id=1;
++++++++++
RECOVERY_MODE
----------------------------------
MANAGED
++++++++++
Let's switch redo on primary so 30 sequence can be archived and shipped/applied on standby.
>>> oracle@srv-dg-db1
SQL> alter system switch logfile;
>>> oracle@srv-dg-db2
SQL> select * from test_dg;
++++++++++
NAME
------------------------------
Real Time Apply Mode
Try RTA with no SRL on standby
++++++++++
Summary: In this situation we have no real-time apply, but primary LNS shipps/writes redo changes to standby RFS and writes info to archive log which will be applied after redo switch on primary.
Here are some proofs:
>>> oracle@srv-dg-db1
SQL> SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;
++++++++++
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ---------- ----------
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 28 8192 127 0
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 29 1 7490 0
ARCH CLOSING 27 1 14793 0
ARCH CLOSING 30 2048 631 0
DGRD ALLOCATED 0 0 0 0
LNS WRITING 31 1012 1 0
DGRD ALLOCATED 0 0 0 0
9 rows selected.
++++++++++
$ ls -ltr /arch/*31*
++++++++++
ls: cannot access /arch/*31*: No such file or directory
++++++++++
Note: Archive log for sequence 31 will be created only after redo switch on primary, but it actually already exist(standby RFS writes redo thread to it) on standby side.
>>> oracle@srv-dg-db2
SQL> SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;
++++++++++
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 28 8192 127 0
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 26 2048 11 0
MRP0 WAIT_FOR_LOG 31 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 31 1012 1 0
RFS IDLE 0 0 0 0
++++++++++
$ ls -ltr /arch/*31*
++++++++++
-rw-r-----. 1 oracle oinstall 536871424 Mar 14 05:57 /arch/log1_31_1034683841.arc
++++++++++
$ lsof log1_31_1034683841.arc
++++++++++
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
oracle_10 10862 oracle 259uW REG 253,5 536871424 18 log1_31_1034683841.arc
++++++++++
SQL> set linesize 150
column machine format a15
column username format a10 tru
column program format a20 tru
set verify off
set head off
select 'SID: '||s.sid||' OS PID:'||spid||chr(10)
||'User: '||s.username||' OS User:'||osuser||chr(10)
||'Machine: '||s.machine||' Program: '||s.program ||chr(10)
||'Module: '||MODULE||chr(10)
||'SQL ADDR: ' ||sql_address||chr(10)
||'Idle: '||floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) ||chr(10)
||'Action: '||action||chr(10)
||'Process: '||Process||chr(10)
||'Status: '|| s.status
from v$session s ,v$process p
where s.paddr=p.addr and p.spid=&pid;
++++++++++
SID: 439 OS PID:10862
User: PUBLIC OS User:oracle
Machine: srv-dg-db1.oracle.com Program: oracle@srv-dg-db1.oracle.com (TNS V1-V3)
Module: oracle@srv-dg-db1.oracle.com (TNS V1-V3)
SQL ADDR: 00
Idle: 0:0:0
Action:
Process: 11704
Status: INACTIVE
++++++++++
===> Max. Protection
Okay let's change mode to max. protection.
>>> oracle@srv-dg-db2 [STANDBY]
SQL> alter database recover managed standby database cancel;
>>> oracle@srv-dg-db1 [PRIMARY]
SQL> alter system set log_archive_dest_state_2=DEFER;
>>> oracle@srv-dg-db2 [STANDBY]
SQL> alter database add standby logfile group 4 ('/redo1/PROD_STBY/redo01a_STBY.log','/redo2/PROD_STBY/redo01b_STBY.log') size 512M;
SQL> alter database add standby logfile group 5 ('/redo1/PROD_STBY/redo02a_STBY.log','/redo2/PROD_STBY/redo02b_STBY.log') size 512M;
SQL> alter database add standby logfile group 6 ('/redo1/PROD_STBY/redo03a_STBY.log','/redo2/PROD_STBY/redo03b_STBY.log') size 512M;
SQL> alter system set log_archive_dest_2='SERVICE=PROD LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD';
>>> oracle@srv-dg-db1 [PRIMARY]
SQL> alter system switch logfile;
SQL> show parameter log_archive_dest_2
++++++++++
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=PROD_STBY LGWR ASYNC N
OAFFIRM VALID_FOR=(ONLINE_LOGF
ILES,PRIMARY_ROLE) DB_UNIQUE_N
AME=PROD_STBY
++++++++++
SQL> select protection_mode, protection_level from v$database;
++++++++++
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
++++++++++
SQL> shu immediate;
SQL> startup mount;
SQL> alter system set log_archive_dest_2='SERVICE=PROD_STBY LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD_STBY';
SQL> select protection_mode, protection_level from v$database;
++++++++++
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE UNPROTECTED
++++++++++
SQL> alter database set standby database to maximize PROTECTION;
++++++++++
Database altered.
++++++++++
SQL> select protection_mode, protection_level from v$database;
++++++++++
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PROTECTION UNPROTECTED
++++++++++
[!!! MISTAKE !!!] SQL> alter database open;
From alert log:
++++++++++
2020-03-14T07:04:58.496172-07:00
LGWR (PID:14580): Primary database is in MAXIMUM PROTECTION mode
LGWR (PID:14580): LAD:1 is not serviced by LGWR
LGWR (PID:14580): Minimum of one LGWR standby database required
2020-03-14T07:04:58.521942-07:00
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_lgwr_14580.trc:
ORA-16072: a minimum of one standby database destination is required
2020-03-14T07:04:58.522248-07:00
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_14628.trc:
ORA-16072: a minimum of one standby database destination is required
USER (ospid: 14628): terminating the instance due to ORA error 16072
++++++++++
SQL> startup mount;
SQL> alter system set log_archive_dest_state_2=enable;
SQL> alter database open;
++++++++++
Database altered.
++++++++++
SQL> select protection_mode, protection_level from v$database;
++++++++++
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PROTECTION MAXIMUM PROTECTION
++++++++++
>>> oracle@srv-dg-db2
SQL> select protection_mode, protection_level from v$database;
++++++++++
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
++++++++++
... primary opened in "Max. Protection"
SQL> select protection_mode, protection_level from v$database;
++++++++++
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PROTECTION MAXIMUM PROTECTION
++++++++++
SQL> select name, database_role, open_mode from v$database;
++++++++++
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
PROD PHYSICAL STANDBY READ ONLY
++++++++++
SQL> alter database recover managed standby database disconnect from session using current logfile;
SQL> select name, database_role, open_mode from v$database;
++++++++++
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
PROD PHYSICAL STANDBY READ ONLY WITH APPLY
++++++++++
SQL> select recovery_mode from v$archive_dest_status where dest_id=1;
++++++++++
RECOVERY_MODE
----------------------------------
MANAGED REAL TIME APPLY
++++++++++
/***************************
For testing purposes removed SRL on standby database and started recover.
Then I tied to switch redo on Primary side and ... switch command hang!
From primary alert log file during hang Redo Log Switch command:
+++++++++++++++
2020-03-14T07:46:57.819565-07:00
LGWR (PID:15076): Error 16086 attaching to RFS for reconnect
2020-03-14T07:47:03.885277-07:00
LGWR (PID:15076): Error 16086 attaching to RFS for reconnect
...
...
...
2020-03-14T07:49:59.719452-07:00
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_lgwr_15076.trc:
ORA-00270: error creating archive log
LGWR (PID:15076): Error 16086 disconnecting from LAD:2 standby host 'PROD_STBY'
LGWR (PID:15076): Error 270 closing archive log file 'PROD_STBY'
2020-03-14T07:49:59.719970-07:00
LGWR (PID:15076): LAD:2 is UNSYNCHRONIZED
LGWR (PID:15076): All standby destinations have failed
LGWR (PID:15076): *************************************************************************
LGWR (PID:15076): WARN: All standby database destinations have failed
LGWR (PID:15076): WARN: Instance shutdown required to protect primary
LGWR (PID:15076): *************************************************************************
LGWR (ospid: 15076): terminating the instance due to ORA error 16098
2020-03-14T07:49:59.766532-07:00
System state dump requested by (instance=1, osid=15076 (LGWR)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_diag_15059.trc
2020-03-14T07:50:00.035565-07:00
Dumping diagnostic data in directory=[cdmp_20200314074959], requested by (instance=1, osid=15076 (LGWR)), summary=[abnormal instance termination].
2020-03-14T07:50:01.170401-07:00
Instance terminated by LGWR, pid = 15076
+++++++++++++++
Finally Primary went down as expected in Max. Protection mode.
Same behavior if we just stop standby database (using abort option for example) - after a while primary will shutdown itself.
***************************/
##########################################################
Archive logs auto delete on STANDBY after apply:
##########################################################
https://blog.dbi-services.com/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard/
https://www.qualogy.com/techblog/oracle/deleting-archive-log-files-in-a-data-guard-environment
Data Guard Physical Standby - RMAN configure archivelog deletion policy reports RMAN-08591 (Doc ID 1984064.1)
##########################################################
Useful Queries:
##########################################################
//
// Standby recovery/protection mode check
//
set lines 222
set pages 999
col dest_name for a20
col status for a10
col type for a10
col database_mode for a20
col recovery_mode for a25
col protection_mode for a20
col destination for a30
select dest_name, status, type, database_mode, recovery_mode, protection_mode, destination from v$archive_dest_status where dest_id=1;
//
// Check lag
//
>>> primary
select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log
where first_time between (sysdate-1) and (sysdate+1)
group by thread# order by 1;
>>> standby
select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log
group by thread# order by 1;
select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log where applied='YES'
group by thread# order by 1;
//
// Check recovery process
//
SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY order by sequence# desc;
Network connectivity:
###################################
>>> oracle@srv-dg-db1 /// srv-dg-db2
$ cat /etc/hosts
++++++++++
192.168.197.179 srv-dg-db1.oracle.com srv-dg-db1
192.168.197.180 srv-dg-db2.oracle.com srv-dg-db2
++++++++++
$ chkconfig --list iptables
$ chkconfig iptables off
$ chkconfig --list iptables
$ service iptables stop
/// OR ///
$ systemctl stop firewalld
$ systemctl disable firewalld
###################################
Oracle Net configuration:
###################################
>>> oracle@srv-dg-db1
$ cat $ORACLE_HOME/network/admin/tnsnames.ora:
++++++++++
PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srv-dg-db1.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD)
(INSTANCE_NAME = PROD)
)
)
PROD_STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srv-dg-db2.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD)
(INSTANCE_NAME = PROD)
)
)
++++++++++
$ cat $ORACLE_HOME/network/admin/listener.ora
++++++++++
LISTENER_PROD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srv-dg-db1.oracle.com)(PORT = 1521))
)
)
SID_LIST_LISTENER_PROD =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = PROD)
)
)
++++++++++
>>> oracle@srv-dg-db2
$ cat $ORACLE_HOME/network/admin/tnsnames.ora:
++++++++++
PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srv-dg-db1.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD)
(INSTANCE_NAME = PROD)
)
)
PROD_STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srv-dg-db2.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD)
(INSTANCE_NAME = PROD)
)
)
++++++++++
$ cat $ORACLE_HOME/network/admin/listener.ora
++++++++++
LISTENER_PROD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srv-dg-db2.oracle.com)(PORT = 1521))
)
)
SID_LIST_LISTENER_PROD =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = PROD)
)
)
++++++++++
###################################
Put PRIMARY in ARCHIVELOG mode:
###################################
>>> oracle@srv-dg-db1
SQL> shu immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
###################################
Put PRIMARY in force logging mode:
###################################
>>> oracle@srv-dg-db1
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
Note: The FORCE LOGGING option is the safest method to ensure that all the changes made in the database will be captured and available for recovery in the redo logs.
##########################################################
Set parameters on PRIMARY:
##########################################################
>>> oracle@srv-dg-db1
SQL> alter system set log_archive_config='dg_config=(PROD,PROD_STBY)';
SQL> alter system set log_archive_dest_state_2=DEFER;
SQL> alter system set log_archive_dest_2='SERVICE=PROD_STBY LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD_STBY';
SQL> alter system set standby_file_management=AUTO;
Note: This is related to data/undo datafiles only. Temp files will NOT be created automatically on standby side after adding new ones on primary. Ref. to 1514588.1.
Note: More about LOG_ARCHIVE_DEST_2 possible settings - https://docs.oracle.com/cd/B28359_01/server.111/b28294/log_arch_dest_param.htm#i104031
SQL> alter system set fal_server=PROD_STBY;
SQL> alter system set fal_client=PROD;
Note: FAL = Fetch Archive Log. When MRP finds that an archive log is missing during media recovery, it sends the fal_client information to the server identified by fal_server and requests fal_server to resend the file again. The fal_client and fal_server init.ora (spfile) parameters are set on the standby instance. But in my case I also set these parameters on primary database in case of switchover/failover.
##########################################################
Create / copy password file:
##########################################################
>>> oracle@srv-dg-db1
$ orapwd file=$ORACLE_HOME/dbs/orapwPROD password=****** format=12 force=y
$ scp $ORACLE_HOME/dbs/orapwPROD oracle@srv-dg-db2:$ORACLE_HOME/dbs/orapwPROD
Note: If your standby database SID is not the same as on primary then you have to rename password file on standby server: mv orapwPROD orapw<<<STANDBY DB SID>>>
##########################################################
Create standby redo logs on PRIMARY:
##########################################################
>>> oracle@srv-dg-db1
SQL> alter database add standby logfile group 4 ('/redo1/PROD/redo01a_STBY.log','/redo2/PROD/redo01b_STBY.log') size 512M;
SQL> alter database add standby logfile group 5 ('/redo1/PROD/redo02a_STBY.log','/redo2/PROD/redo02b_STBY.log') size 512M;
SQL> alter database add standby logfile group 6 ('/redo1/PROD/redo03a_STBY.log','/redo2/PROD/redo03b_STBY.log') size 512M;
##########################################################
Create parameter file for STANDBY:
##########################################################
>>> oracle@srv-dg-db2
$ cat $ORACLE_HOME/dbs/initPROD.ora
++++++++++
db_name='PROD'
db_unique_name='PROD_STBY'
control_files='/data/PROD_STBY/control01.ctl','/redo1/PROD_STBY/control02.ctl','/redo2/PROD_STBY/control03.ctl'
audit_file_dest='/u01/app/oracle/admin/PROD_STBY/adump'
sga_max_size=5264M
sga_target=5264M
pga_aggregate_limit=3500M
pga_aggregate_target=1750M
db_file_name_convert = ('/data/PROD/','/data/PROD_STBY/')
log_file_name_convert = ('/redo1/PROD/','/redo1/PROD_STBY/','/redo2/PROD/','/redo2/PROD_STBY/')
log_archive_config='dg_config=(PROD,PROD_STBY)'
log_archive_dest_1='LOCATION=/arch'
log_archive_dest_2='SERVICE=PROD LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD'
log_archive_dest_state_2='DEFER'
log_archive_format='log%t_%s_%r.arc'
fal_server='PROD'
fal_client='PROD_STBY'
standby_file_management='MANUAL'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
++++++++++
$ mkdir -p /u01/app/oracle/admin/PROD_STBY/adump /data/PROD_STBY /redo1/PROD_STBY /redo2/PROD_STBY
SQL> create spfile from pfile;
##########################################################
Start STANDBY:
##########################################################
>>> oracle@srv-dg-db2
SQL> startup nomount;
SQL> select instance_name, status from v$Instance;
INSTANCE_NAME STATUS
---------------- ------------
PROD STARTED
##########################################################
RMAN duplicate:
##########################################################
Note: you can also use backup to create standby database according to notes: 469493.1, 836986.1.
>>> oracle@srv-dg-db2
$ lsnrctl start LISTENER_PROD
$ tnsping PROD
++++++++++
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 11-MAR-2020 14:50:43
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = srv-dg-db1.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD) (INSTANCE_NAME = PROD)))
OK (0 msec)
++++++++++
$ tnsping PROD_STBY
++++++++++
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 11-MAR-2020 14:51:22
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = srv-dg-db2.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD) (INSTANCE_NAME = PROD)))
OK (0 msec)
++++++++++
>>> oracle@srv-dg-db1
$ tnsping PROD_STBY
++++++++++
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 11-MAR-2020 14:52:12
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = srv-dg-db2.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD) (INSTANCE_NAME = PROD)))
OK (10 msec)
++++++++++
>>> oracle@srv-dg-db2
$ rman
$ connect target sys/***@PROD
$ connect auxiliary sys/***@PROD_STBY
RMAN> run {
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate auxiliary channel s1 type disk;
allocate auxiliary channel s2 type disk;
duplicate target database for standby from active database dorecover nofilenamecheck;
}
##########################################################
Set standby_file_management=AUTO on STANDBY:
##########################################################
>>> oracle@srv-dg-db2
SQL> set lines 222
set pages 999
col member for a65
select * from v$logfile order by 1;
++++++++++
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ----------------------------------------------------------------- --- ----------
1 ONLINE /redo2/PROD_STBY/redo01b.log NO 0
1 ONLINE /redo1/PROD_STBY/redo01a.log NO 0
2 ONLINE /redo1/PROD_STBY/redo02a.log NO 0
2 ONLINE /redo2/PROD_STBY/redo02b.log NO 0
3 ONLINE /redo2/PROD_STBY/redo03b.log NO 0
3 ONLINE /redo1/PROD_STBY/redo03a.log NO 0
4 STANDBY /redo1/PROD_STBY/redo01a_STBY.log NO 0
4 STANDBY /redo2/PROD_STBY/redo01b_STBY.log NO 0
5 STANDBY /redo1/PROD_STBY/redo02a_STBY.log NO 0
5 STANDBY /redo2/PROD_STBY/redo02b_STBY.log NO 0
6 STANDBY /redo1/PROD_STBY/redo03a_STBY.log NO 0
6 STANDBY /redo2/PROD_STBY/redo03b_STBY.log NO 0
12 rows selected.
++++++++++
SQL> alter system set standby_file_management=AUTO scope=both;
##########################################################
Start redo apply in real time mode on STANDBY:
##########################################################
>>> oracle@srv-dg-db1
SQL> alter system set log_archive_dest_state_2='ENABLE';
>>> oracle@srv-dg-db2
SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database disconnect from session using current logfile;
Note: "disconnect from session" just starts recover in background. It will not disconnect your current SQL Plus session.
SQL> SET LINES 180
col DEST_NAME for a30
select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;
++++++++++
DEST_ID DEST_NAME STATUS TYPE SRL RECOVERY_MODE
---------- ------------------------------ --------- ---------------- --- ----------------------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL NO MANAGED REAL TIME APPLY
++++++++++
SQL> select protection_mode, protection_level from v$database;
++++++++++
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
++++++++++
Note: standby redo logs are populated with redo information as fast as the primary redo logs, rather than waiting for the redo log to be archived and shipped to the standby. This results in faster switchover and failover times because the standby redo log files have been applied already to the standby database by the time the failover or switchover begins.Oracle recommends the below formula to calculate the number of Standby redo logs file as == (maximum number of logfiles for each thread + 1) * maximum number of threads.
>>> oracle@srv-dg-db1
SQL> select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log
where first_time between (sysdate-1) and (sysdate+1)
group by thread# order by 1;
++++++++++
THREAD# Last Primary Seq Generated
---------- --------------------------
1 25
++++++++++
>>> oracle@srv-dg-db2
SQL> select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log
group by thread# order by 1;
++++++++++
THREAD# Last Standby Seq Received
---------- -------------------------
1 25
++++++++++
SQL> select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log where applied='YES'
group by thread# order by 1;
++++++++++
THREAD# Last Standby Seq Applied
---------- ------------------------
1 25
++++++++++
SQL> SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;
++++++++++
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
RFS IDLE 26 1199 1 0
RFS IDLE 0 0 0 0
MRP0 APPLYING_LOG 26 1199 1048576 0
9 rows selected.
++++++++++
##########################################################
STOP procedure:
##########################################################
>>> oracle@srv-dg-db1 [PRIMARY]
SQL> alter system set log_archive_dest_state_2=defer;
>>> oracle@srv-dg-db2 [STANDBY]
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> shu immediate;
$ lsnrctl stop LISTENER_PROD
##########################################################
START procedure:
##########################################################
>>> oracle@srv-dg-db2 [STANDBY]
$ lsnrctl start LISTENER_PROD
SQL> startup nomount;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> alter database recover managed standby database cancel;
SQL> ALTER DATABASE OPEN READ ONLY;
SQL> alter database recover managed standby database disconnect from session using current logfile;
>>> oracle@srv-dg-db1 [PRIMARY]
SQL> alter system set log_archive_dest_state_2=ENABLE;
##########################################################
Change Protection mode example:
##########################################################
===> Max. Performance with Real-Time Apply
Primary === log_archive_dest_2 = SERVICE=PROD_STBY LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD_STBY
Standby === log_archive_dest_2 = SERVICE=PROD LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD
>>> oracle@srv-dg-db1
SQL> archive log list;
++++++++++
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch
Oldest online log sequence 26
Next log sequence to archive 28
Current log sequence 28
++++++++++
SQL> create table test_dg (Name varchar2(30));
Table created.
SQL> insert into test_dg values ('Real Time Apply Mode');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test_dg;
++++++++++
NAME
------------------------------
Real Time Apply Mode
++++++++++
SQL> archive log list;
++++++++++
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch
Oldest online log sequence 26
Next log sequence to archive 28
Current log sequence 28
++++++++++
>>> oracle@srv-dg-db2
SQL> SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY WHERE PROCESS like 'MRP%';
++++++++++
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ---------- ----------
MRP0 APPLYING_LOG 28 7801 1048576 0
++++++++++
SQL> select recovery_mode from v$archive_dest_status where dest_id=1;
++++++++++
RECOVERY_MODE
----------------------------------
MANAGED REAL TIME APPLY
++++++++++
SQL> select * from test_dg;
++++++++++
NAME
------------------------------
Real Time Apply Mode
++++++++++
Note: redo switch on primary is not required to get changes on standby side. That is why it calls "Real-Time Apply".
===> Max. Performance (NO real-time apply)
>>> oracle@srv-dg-db1
SQL> alter system set log_archive_dest_state_2=DEFER;
System altered.
>>> oracle@srv-dg-db2
Note: Let's drop SRL on standby server (this is not fully required, because we can start just start recover in NO real-time apply mode, but to be clear in this experiment I will do it).
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> set lines 222
set pages 999
col member for a75
select * from v$logfile order by 1;
++++++++++
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------------------------------------------------------------------- --- ----------
1 ONLINE /redo2/PROD_STBY/redo01b.log NO 0
1 ONLINE /redo1/PROD_STBY/redo01a.log NO 0
2 ONLINE /redo1/PROD_STBY/redo02a.log NO 0
2 ONLINE /redo2/PROD_STBY/redo02b.log NO 0
3 ONLINE /redo2/PROD_STBY/redo03b.log NO 0
3 ONLINE /redo1/PROD_STBY/redo03a.log NO 0
4 STANDBY /redo1/PROD_STBY/redo01a_STBY.log NO 0
4 STANDBY /redo2/PROD_STBY/redo01b_STBY.log NO 0
5 STANDBY /redo1/PROD_STBY/redo02a_STBY.log NO 0
5 STANDBY /redo2/PROD_STBY/redo02b_STBY.log NO 0
6 STANDBY /redo1/PROD_STBY/redo03a_STBY.log NO 0
6 STANDBY /redo2/PROD_STBY/redo03b_STBY.log NO 0
12 rows selected.
++++++++++
SQL> alter database clear logfile group 4;
SQL> alter database drop logfile group 4;
SQL> !rm -f /redo1/PROD_STBY/redo01a_STBY.log /redo2/PROD_STBY/redo01b_STBY.log
SQL> alter database clear logfile group 5;
SQL> alter database drop logfile group 5;
SQL> !rm -f /redo1/PROD_STBY/redo02a_STBY.log /redo2/PROD_STBY/redo02b_STBY.log
SQL> alter database clear logfile group 6;
SQL> alter database drop logfile group 6;
SQL> !rm -f /redo1/PROD_STBY/redo03a_STBY.log /redo2/PROD_STBY/redo03b_STBY.log
>>> oracle@srv-dg-db2
SQL> alter database recover managed standby database disconnect from session using current logfile;
Note: Above statement is actually used for RTA, but in this case we don't have SRL and oracle will ignore it and work in same way if we just asked: alter database recover managed standby database disconnect from session;
Database altered.
No any errors as you can see.
SQL> select name, database_role, open_mode from v$database;
++++++++++
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
PROD PHYSICAL STANDBY READ ONLY WITH APPLY
++++++++++
>>> oracle@srv-dg-db1
SQL> alter system set log_archive_dest_state_2=ENABLE;
SQL> archive log list;
++++++++++
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch
Oldest online log sequence 28
Next log sequence to archive 30
Current log sequence 30
++++++++++
SQL> insert into test_dg values ('Try RTA with no SRL on standby');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test_dg;
++++++++++
NAME
------------------------------
Real Time Apply Mode
Try RTA with no SRL on standby
++++++++++
SQL> archive log list;
++++++++++
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch
Oldest online log sequence 28
Next log sequence to archive 30
Current log sequence 30
++++++++++
Note: Let's check on standby side.
>>> oracle@srv-dg-db2
SQL> select * from test_dg;
++++++++++
NAME
------------------------------
Real Time Apply Mode
++++++++++
Note: changes not applied on standby. Here are some information from standby alert log file:
++++++++++
...
...
...
2020-03-14T05:25:36.478413-07:00
Completed: alter database recover managed standby database disconnect from session using current logfile
2020-03-14T05:27:48.272035-07:00
rfs (PID:7496): Opened log for T-1.S-29 dbid 446522174 branch 1034683841
2020-03-14T05:27:48.459087-07:00
rfs (PID:7496): Archived Log entry 6 added for B-1034683841.T-1.S-29 ID 0x1a9df13e LAD:2
2020-03-14T05:27:48.788084-07:00
rfs (PID:7498): Primary database is in MAXIMUM PERFORMANCE mode
rfs (PID:7498): No SRLs created
2020-03-14T05:27:48.833814-07:00
rfs (PID:7498): Opened log for T-1.S-30 dbid 446522174 branch 1034683841
2020-03-14T05:27:49.295643-07:00
PR00 (PID:7324): Media Recovery Log /arch/log1_29_1034683841.arc
PR00 (PID:7324): Media Recovery Waiting for T-1.S-30 (in transit)
++++++++++
It's waiting for 30 redo sequence to archived and shipped on standby, then standby MRP process will apply it.
SQL> SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY WHERE PROCESS like 'MRP%';
++++++++++
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ---------- ----------
MRP0 WAIT_FOR_LOG 30 0 0 0
++++++++++
SQL> select recovery_mode from v$archive_dest_status where dest_id=1;
++++++++++
RECOVERY_MODE
----------------------------------
MANAGED
++++++++++
Let's switch redo on primary so 30 sequence can be archived and shipped/applied on standby.
>>> oracle@srv-dg-db1
SQL> alter system switch logfile;
>>> oracle@srv-dg-db2
SQL> select * from test_dg;
++++++++++
NAME
------------------------------
Real Time Apply Mode
Try RTA with no SRL on standby
++++++++++
Summary: In this situation we have no real-time apply, but primary LNS shipps/writes redo changes to standby RFS and writes info to archive log which will be applied after redo switch on primary.
Here are some proofs:
>>> oracle@srv-dg-db1
SQL> SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;
++++++++++
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ---------- ----------
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 28 8192 127 0
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 29 1 7490 0
ARCH CLOSING 27 1 14793 0
ARCH CLOSING 30 2048 631 0
DGRD ALLOCATED 0 0 0 0
LNS WRITING 31 1012 1 0
DGRD ALLOCATED 0 0 0 0
9 rows selected.
++++++++++
$ ls -ltr /arch/*31*
++++++++++
ls: cannot access /arch/*31*: No such file or directory
++++++++++
Note: Archive log for sequence 31 will be created only after redo switch on primary, but it actually already exist(standby RFS writes redo thread to it) on standby side.
>>> oracle@srv-dg-db2
SQL> SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;
++++++++++
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 28 8192 127 0
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 26 2048 11 0
MRP0 WAIT_FOR_LOG 31 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 31 1012 1 0
RFS IDLE 0 0 0 0
++++++++++
$ ls -ltr /arch/*31*
++++++++++
-rw-r-----. 1 oracle oinstall 536871424 Mar 14 05:57 /arch/log1_31_1034683841.arc
++++++++++
$ lsof log1_31_1034683841.arc
++++++++++
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
oracle_10 10862 oracle 259uW REG 253,5 536871424 18 log1_31_1034683841.arc
++++++++++
SQL> set linesize 150
column machine format a15
column username format a10 tru
column program format a20 tru
set verify off
set head off
select 'SID: '||s.sid||' OS PID:'||spid||chr(10)
||'User: '||s.username||' OS User:'||osuser||chr(10)
||'Machine: '||s.machine||' Program: '||s.program ||chr(10)
||'Module: '||MODULE||chr(10)
||'SQL ADDR: ' ||sql_address||chr(10)
||'Idle: '||floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) ||chr(10)
||'Action: '||action||chr(10)
||'Process: '||Process||chr(10)
||'Status: '|| s.status
from v$session s ,v$process p
where s.paddr=p.addr and p.spid=&pid;
++++++++++
SID: 439 OS PID:10862
User: PUBLIC OS User:oracle
Machine: srv-dg-db1.oracle.com Program: oracle@srv-dg-db1.oracle.com (TNS V1-V3)
Module: oracle@srv-dg-db1.oracle.com (TNS V1-V3)
SQL ADDR: 00
Idle: 0:0:0
Action:
Process: 11704
Status: INACTIVE
++++++++++
===> Max. Protection
Okay let's change mode to max. protection.
>>> oracle@srv-dg-db2 [STANDBY]
SQL> alter database recover managed standby database cancel;
>>> oracle@srv-dg-db1 [PRIMARY]
SQL> alter system set log_archive_dest_state_2=DEFER;
>>> oracle@srv-dg-db2 [STANDBY]
SQL> alter database add standby logfile group 4 ('/redo1/PROD_STBY/redo01a_STBY.log','/redo2/PROD_STBY/redo01b_STBY.log') size 512M;
SQL> alter database add standby logfile group 5 ('/redo1/PROD_STBY/redo02a_STBY.log','/redo2/PROD_STBY/redo02b_STBY.log') size 512M;
SQL> alter database add standby logfile group 6 ('/redo1/PROD_STBY/redo03a_STBY.log','/redo2/PROD_STBY/redo03b_STBY.log') size 512M;
SQL> alter system set log_archive_dest_2='SERVICE=PROD LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD';
>>> oracle@srv-dg-db1 [PRIMARY]
SQL> alter system switch logfile;
SQL> show parameter log_archive_dest_2
++++++++++
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=PROD_STBY LGWR ASYNC N
OAFFIRM VALID_FOR=(ONLINE_LOGF
ILES,PRIMARY_ROLE) DB_UNIQUE_N
AME=PROD_STBY
++++++++++
SQL> select protection_mode, protection_level from v$database;
++++++++++
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
++++++++++
SQL> shu immediate;
SQL> startup mount;
SQL> alter system set log_archive_dest_2='SERVICE=PROD_STBY LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD_STBY';
SQL> select protection_mode, protection_level from v$database;
++++++++++
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE UNPROTECTED
++++++++++
SQL> alter database set standby database to maximize PROTECTION;
++++++++++
Database altered.
++++++++++
SQL> select protection_mode, protection_level from v$database;
++++++++++
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PROTECTION UNPROTECTED
++++++++++
[!!! MISTAKE !!!] SQL> alter database open;
From alert log:
++++++++++
2020-03-14T07:04:58.496172-07:00
LGWR (PID:14580): Primary database is in MAXIMUM PROTECTION mode
LGWR (PID:14580): LAD:1 is not serviced by LGWR
LGWR (PID:14580): Minimum of one LGWR standby database required
2020-03-14T07:04:58.521942-07:00
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_lgwr_14580.trc:
ORA-16072: a minimum of one standby database destination is required
2020-03-14T07:04:58.522248-07:00
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_14628.trc:
ORA-16072: a minimum of one standby database destination is required
USER (ospid: 14628): terminating the instance due to ORA error 16072
++++++++++
SQL> startup mount;
SQL> alter system set log_archive_dest_state_2=enable;
SQL> alter database open;
++++++++++
Database altered.
++++++++++
SQL> select protection_mode, protection_level from v$database;
++++++++++
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PROTECTION MAXIMUM PROTECTION
++++++++++
>>> oracle@srv-dg-db2
SQL> select protection_mode, protection_level from v$database;
++++++++++
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
++++++++++
... primary opened in "Max. Protection"
SQL> select protection_mode, protection_level from v$database;
++++++++++
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PROTECTION MAXIMUM PROTECTION
++++++++++
SQL> select name, database_role, open_mode from v$database;
++++++++++
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
PROD PHYSICAL STANDBY READ ONLY
++++++++++
SQL> alter database recover managed standby database disconnect from session using current logfile;
SQL> select name, database_role, open_mode from v$database;
++++++++++
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
PROD PHYSICAL STANDBY READ ONLY WITH APPLY
++++++++++
SQL> select recovery_mode from v$archive_dest_status where dest_id=1;
++++++++++
RECOVERY_MODE
----------------------------------
MANAGED REAL TIME APPLY
++++++++++
/***************************
For testing purposes removed SRL on standby database and started recover.
Then I tied to switch redo on Primary side and ... switch command hang!
From primary alert log file during hang Redo Log Switch command:
+++++++++++++++
2020-03-14T07:46:57.819565-07:00
LGWR (PID:15076): Error 16086 attaching to RFS for reconnect
2020-03-14T07:47:03.885277-07:00
LGWR (PID:15076): Error 16086 attaching to RFS for reconnect
...
...
...
2020-03-14T07:49:59.719452-07:00
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_lgwr_15076.trc:
ORA-00270: error creating archive log
LGWR (PID:15076): Error 16086 disconnecting from LAD:2 standby host 'PROD_STBY'
LGWR (PID:15076): Error 270 closing archive log file 'PROD_STBY'
2020-03-14T07:49:59.719970-07:00
LGWR (PID:15076): LAD:2 is UNSYNCHRONIZED
LGWR (PID:15076): All standby destinations have failed
LGWR (PID:15076): *************************************************************************
LGWR (PID:15076): WARN: All standby database destinations have failed
LGWR (PID:15076): WARN: Instance shutdown required to protect primary
LGWR (PID:15076): *************************************************************************
LGWR (ospid: 15076): terminating the instance due to ORA error 16098
2020-03-14T07:49:59.766532-07:00
System state dump requested by (instance=1, osid=15076 (LGWR)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_diag_15059.trc
2020-03-14T07:50:00.035565-07:00
Dumping diagnostic data in directory=[cdmp_20200314074959], requested by (instance=1, osid=15076 (LGWR)), summary=[abnormal instance termination].
2020-03-14T07:50:01.170401-07:00
Instance terminated by LGWR, pid = 15076
+++++++++++++++
Finally Primary went down as expected in Max. Protection mode.
Same behavior if we just stop standby database (using abort option for example) - after a while primary will shutdown itself.
***************************/
##########################################################
Archive logs auto delete on STANDBY after apply:
##########################################################
https://blog.dbi-services.com/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard/
https://www.qualogy.com/techblog/oracle/deleting-archive-log-files-in-a-data-guard-environment
Data Guard Physical Standby - RMAN configure archivelog deletion policy reports RMAN-08591 (Doc ID 1984064.1)
##########################################################
Useful Queries:
##########################################################
//
// Standby recovery/protection mode check
//
set lines 222
set pages 999
col dest_name for a20
col status for a10
col type for a10
col database_mode for a20
col recovery_mode for a25
col protection_mode for a20
col destination for a30
select dest_name, status, type, database_mode, recovery_mode, protection_mode, destination from v$archive_dest_status where dest_id=1;
//
// Check lag
//
>>> primary
select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log
where first_time between (sysdate-1) and (sysdate+1)
group by thread# order by 1;
>>> standby
select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log
group by thread# order by 1;
select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log where applied='YES'
group by thread# order by 1;
//
// Check recovery process
//
SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY order by sequence# desc;