Showing posts with label Data Guard. Show all posts
Showing posts with label Data Guard. Show all posts

Thursday, May 21, 2026

Oracle Standby FIX using RMAN Incremental Backup from Primary DB

 

When you don’t have an option other than doing incremental backup from Primary DB (when ARCH logs are gone from all places) below is the high-level DB steps to fix the Standby GAP

On Standby:

Get the current DB SCN number

SYS@myoradb> select to_char (current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
---------------------------------
11411445224862      à  SCN to take Backup from
  
  1. [Standby] Stop the managed standby apply process:
  2. Shut down standby
  3. start RMAN incremental backup from source (primary OR standby)
    RMAN> Backup as compressed backupset incremental from scn 11411445224862 database format '/u01/app/oracle/RMAN_INC/ForStandby_%U' tag 'FORSTANDBY';
  4. backup control file from source
    backup current controlfile for standby format '/u01/app/oracle/RMAN_INC/TRGDB_ForStandbyCTRL.bck';
  5. SCP backup pieces to Standby Server
    oracle@SRCHOST testdb > nohup scp TRGHOST:/u01/app/oracle/RMAN_INC/ForStandby_6091 . > Nohup_6091.log &
  6. [on standby] shut down DB and copy standby control file to location specified in standby
    • Backup existing control file and replace with primary control file
  7. mount standby DB from only 1 NODE
    RMAN> alter database mount;

       8 . Catalog all backup files with RMAN and start recover
             RMAN> catalog start with '/u01/app/oracle/TESTDB_standby_';
             RMAN> recover database;

             SYS@myoradb> select thread#, low_sequence#, high_sequence# from v$archive_gap;

 


Wednesday, December 6, 2023

Add Oracle Physical Standby to Existing Data guard BROKER Configuration

 

 
PRIMARY  : oradb_prim
STANDBY  :  oradb_std
ORACLE Version : 19c
 
Assuming tnsping using TNSNAME is working from both sides.

On Standby :

sho parameter broker

 alter system set dg_broker_config_file1='+DATAC1/ORADB_STD/DATAGUARDCONFIG/oradb_primdg1.dat' scope=both sid='*'; 

 alter system set dg_broker_config_file2='+DATAC1/ORADB_STD/DATAGUARDCONFIG/oradb_primdg2.dat' scope=both sid='*';

 alter system set dg_broker_start=TRUE scope=both sid='*'; 

sho parameter broker

 

From primary

$ dgmgrl /

show configuration;            

    --- Add Standby database into Broker Config                                  
add database oradb_std  as connect identifier is oradb_std maintained as physical;
 

enable configuration;

show configuration;

        --- Add Redo Routes for Primary

show database oradb  redoroutes;

edit database oradb_prim set property RedoRoutes='(local:oradb_std ASYNC)';   -- Change as per requirements

  -- As soon as Redo property is added oracle dest_ parameter will set in database Automatically

show database oradb_prim redoroutes;

 

edit database 'oradb_std' set state='apply-off';

edit database 'oradb_std' set state='apply-on';

Enable configuration

            --- Optionally adjust some properties as you like

DGMGRL> edit database oradb_std set property TransportLagThreshold='900';

Property "transportlagthreshold" updated

 

DGMGRL> edit database oradb_std set property ApplyLagThreshold='900';

Property "applylagthreshold" updated



Enable Physical standby BROKER Configuration for oracle Primary


Primary : PRIMDB  
Standby  : primdb_std 
 
On primary :
 
12:14:55 SYSTEM@PRIMDB >sho parameter broker
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
connection_brokers                   string      ((TYPE=DEDICATED)(BROKERS=1)),
                                                  ((TYPE=EMON)(BROKERS=1))
dg_broker_config_file1               string      /opt/oracle/product/19.3.0/dbs
                                                 /dr1primdb.dat
dg_broker_config_file2               string      /opt/oracle/product/19.3.0/dbs
                                                 /dr2primdb.dat
dg_broker_start                      boolean     FALSE
use_dedicated_broker                 boolean     FALSE
 
12:15:05 SYSTEM@PRIMDB > show parameter log_archive_config
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string
 
oraclehost:PRIMDB:/home/oracle $ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jul 27 11:45:14 2023
Version 19.17.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
Connected to "PRIMDB"
Connected as SYSDG.
DGMGRL> show configuration
ORA-16525: The Oracle Data Guard broker is not yet available.
 
Configuration details cannot be determined by DGMGRL
 
 
 
SYNTAX:
DGMGRL> CREATE CONFIGURATION '<configuration name>' AS PRIMARY DATABASE IS '<primary db_unique_name>' CONNECT IDENTIFIER IS <primary connect string>;
 
 
DGMGRL>  create configuration Primdb_dg as primary database is 'PRIMDB' connect identifier is PRIMDB ;
Error:
ORA-16525: The Oracle Data Guard broker is not yet available.
 
 
Fix : set “dg_broker_start” to TRUE
 
11:50:11 SYSTEM@PRIMDB >   alter system set dg_broker_start=true sid='*';
 
System altered.
 
Also you can set desired broker config file path using below

alter system set dg_broker_config_file1='+<path>/DATAGUARDCONFIG/primdb_dg1.dat' scope=both sid='*'; 

alter system set dg_broker_config_file2='+<path>/DATAGUARDCONFIG/primdb_dg2.dat' scope=both sid='*'; 


11:51:53 SYSTEM@PRIMDB >sho parameter broker
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
connection_brokers                   string      ((TYPE=DEDICATED)(BROKERS=1)),
                                                  ((TYPE=EMON)(BROKERS=1))
dg_broker_config_file1               string      /opt/oracle/product/19.3.0/dbs/dr1PRIMDB.dat
dg_broker_config_file2               string      /opt/oracle/product/19.3.0/dbs/dr2PRIMDB.dat                                            
dg_broker_start                      boolean     TRUE
use_dedicated_broker                 boolean     FALSE
 
  
 
DGMGRL> create configuration primdb_dg as primary database is 'PRIMDB' connect identifier is primdb ;
Configuration "primdb_dg" created with primary database "PRIMDB"
 
DGMGRL>  show configuration
 
Configuration - Primdb_dg
 
  Protection Mode: MaxPerformance
  Members:
  PRIMDB - Primary database
 
Fast-Start Failover:  Disabled
 
Configuration Status:
DISABLED
 
Files will be created automatically on server
 
oraclehost:PRIMDB:/home/oracle $ ll /opt/oracle/product/19.3.0/dbs/dr1PRIMDB.dat
-rw-r----- 1 oracle oinstall 8192 Jul 27 11:58 /opt/oracle/product/19.3.0/dbs/dr1PRIMDB.dat
 
oraclehost:PRIMDB:/home/oracle $ ll /opt/oracle/product/19.3.0/dbs/dr2PRIMDB.dat
-rw-r----- 1 oracle oinstall  8192 Jul 27 11:58 /opt/oracle/product/19.3.0/dbs/dr2PRIMDB.dat
 
 
DGMGRL> enable configuration;
Enabled.
 
DGMGRL> show configuration
 
Configuration - Primdb_dg
 
  Protection Mode: MaxPerformance
  Members:
  PRIMDB - Primary database
    Warning: ORA-16905: The member was not enabled yet.
 
Fast-Start Failover:  Disabled
 
Configuration Status:
WARNING   (status updated 246 seconds ago)

 Doc:
12c Create Dataguard Broker Configuration - DGMGRL (Doc ID 1583588.1)
 
Add Standby to primary can be found here :  Add standby to Broker 

Saturday, July 15, 2023

Recover Standby DB when standby_file_management is MANUAL when new files added in primary 19c

 
 
When datafiles are added on primary I see 2 datafiles are in WRONG Location in standby database after the new standby build
 
On Standby :
09:52:55 SYSTEM@devdb_std >select file_name from dba_data_files order by 1;
 
FILE_NAME
--------------------------------------------------------------------------------
/acfs_data/devdb_std/datafile/DEVDB_STD/datafile/o1_mf_it2_lbtw9k_.dbf
/acfs_data/devdb_std/datafile/DEVDB_STD/datafile/o1_mf_it2_lbtymt_.dbf
/acfs_data/devdb_std/datafile/data_D-DEVDB_TS-A_FNO-8
/acfs_data/devdb_std/datafile/data_D-DEVDB_TS-AA_FNO-9
/acfs_data/devdb_std/datafile/data_D-DEVDB_TS-AD_FNO-10
…… Trimmed ….
 
09:54:26 SYSTEM@devdb_std >sho parameter convert
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      +DATAC1, /acfs_data/devdb_std/datafile
log_file_name_convert                string      +RECOC1, /acfs_reco/devdb_std/onlinelog
pdb_file_name_convert                string
 
09:53:19 SYSTEM@devdb_std >sho parameter standby
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enabled_PDBs_on_standby              string      *
standby_db_preserve_states           string      NONE
standby_file_management              string      AUTO
standby_pdb_source_file_dblink       string
standby_pdb_source_file_directory    string
 
Reason :
 
The reason being by default when “standby_file_management” is AUTO OMF creates files in “db_file_name_convert” parameter in below location
 
<db_file_name_convert PATH>/<CAPS DBNAME>/datafile/
 
FIX:
 
Check the file number and status
 
10:31:33 SYSTEM@devdb_std > select file#, error, name from v$datafile_header where ERROR='FILE MISSING';
 
     FILE# ERROR
---------- -----------------------------------------------------------------
NAME
------------------------------------------------------------------------------------------------------------------------------------------
       781 FILE MISSING
 
 
 
10:34:39 SYSTEM@devdb_std >select name from v$datafile where file#=781 ;
 
NAME
-------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/UNNAMED00781
 
10:34:53 SYSTEM@devdb_std >alter database create datafile '/u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/UNNAMED00781' as NEW;
alter database create datafile '/u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/UNNAMED00781' as NEW
*
ERROR at line 1:
ORA-01275: Operation CREATE DATAFILE is not allowed if standby file management is automatic.
 
Cannot run when standby_file_management is AUTO so disable and rerun
 
10:36:13 SYSTEM@devdb_std >alter system set standby_file_management=MANUAL scope=both;
 
System altered.
 
10:36:25 SYSTEM@devdb_std >alter database create datafile '/u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/UNNAMED00781' as NEW;
 
Database altered.
 
10:36:31 SYSTEM@devdb_std >alter system set standby_file_management=AUTO scope=both;
 
System altered.
 
 
Check the paths again
 
SQL >   select file_name from dba_data_files order by 1;
 
SQL >   select name from v$datafile where file#=779;
 
set the path correctly based on your location so that we cannot ran into this issue again
 
10:17:19 SYSTEM@devdb_std >alter system set db_create_file_dest='/acfs_data';
 
System altered.
 
10:23:41 SYSTEM@devdb_std >sho parameter file_dest
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u02/app/oracle/product/19.0.0.0/dbhome_1/rdbms/audit
db_create_file_dest                  string      /acfs_data
db_recovery_file_dest                string      /acfs_reco/devdb_std/archivelog
db_recovery_file_dest_size           big integer 3500G
remote_recovery_file_dest            string
 
 
Now add couple of datafiles in primary and verify.
 
10:52:44 SYSTEM@devdb_std >select name from v$datafile where file#=779;
 
NAME
------------------------------------------------------------------------------------------------------------------------------------------
/acfs_data/DEVDB_STD/datafile/o1_mf_imm2_lc2vtbcd_.dbf
 
 
More like this

Tuesday, June 13, 2023

Refresh 19c physical standby database using service

 
Primary DB (19c) : TESTDB_PRIM (standby)
Standby DB (19c) : TESTDB_STD (cascade standby)
 
Standby has a GAP and waiting on logs which are deleted from primary
 
NOTE : here my primary is also a STANDBY from where I setup another standby (cascade)
 
SQL> select inst_id,process, thread#, sequence#, status from gv$managed_standby where process='MRP0';
 
   INST_ID PROCESS      THREAD#  SEQUENCE# STATUS
---------- --------- ---------- ---------- ------------
         1 MRP0               1     973244 WAIT_FOR_GAP
 
 
 
SQL> SELECT name "Database name", status, ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (select name from v$database) name, (select status from v$instance) status,(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
 
Database  STATUS           Thread Last Sequence Received Last Sequence Applied Difference
--------- ------------ ---------- ---------------------- --------------------- ----------
TESTDB    MOUNTED               1                 975607                973243       2364
TESTDB    MOUNTED               3                 945126                942906       2220
TESTDB    MOUNTED               2                 995543                993282       2261
 
 ***** Switch some logfile on PRIMARY *****
 
SQL>   /
 
Database  STATUS           Thread Last Sequence Received Last Sequence Applied Difference
--------- ------------ ---------- ---------------------- --------------------- ----------
TESTDB    MOUNTED               1                 975607                973243       2364
TESTDB    MOUNTED               3                 945126                942906       2220
TESTDB    MOUNTED               2                 995545                993282       2263
 
 
I see logfiles are flowing to standby but MRP is waiting on GAP
 
Standby Alert log gives the GAP Message
 
2023-06-13T11:48:36.422200-05:00
PR00 (PID:397954): FAL: Failed to request gap sequence
PR00 (PID:397954):  GAP - thread 1 sequence 973244-973343
PR00 (PID:397954):  DBID 3401882687 branch 943793862
PR00 (PID:397954): FAL: All defined FAL servers have been attempted
PR00 (PID:397954): -------------------------------------------------------------------------
PR00 (PID:397954): Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
PR00 (PID:397954): parameter is defined to a value that's sufficiently large
PR00 (PID:397954): enough to maintain adequate log switch information to resolve
PR00 (PID:397954): archived redo log gaps.
PR00 (PID:397954): -------------------------------------------------------------
 
 
Solution :
 
OCI - Roll Forward A Standby Database Using Recover Database From Service (Doc ID 2931070.1)
How to Roll Forward a Standby Database Using Recover Database From Service (Doc ID 2850185.1)
 
From Oracle 18c and higher. Single command (RED) will do all the work
 
1.      STOP MRP
                     SQL>  recover managed standby database cancel;   ( or )
 
                     DGMGRL>  EDIT DATABASE '<standby>' SET STATE='APPLY-OFF';
 
 
2.      Stop all standby instances (if RAC) and mount only 1 node
3.       Run the shell script
HOSTNAME:NOTSET:/export/scripts $ cat TESTDB_INC_RECOVER_CB.sh
 
export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
rman target / trace /var/logs/RMAN_INC_recover_01jun2023.log << EOF
alter session set NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS';
select sysdate from dual;
 
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
allocate channel c8 type disk;
allocate channel c9 type disk;
allocate channel c10 type disk;
recover database from service TESTDB_PRIM section size 5g;
}
 
alter session set NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS';
select sysdate from dual;
 
EOF
 
 
4.      Once recovery is done then start MRP
 
 
 
 
Then logs will start apply normally.
 
 
 
 
 
 
 
 
 
 
 

Friday, June 9, 2023

ORA-38777: database must not be started in any other instance

 

I have 19c DB converted to SNAPSHOT STANDBY and now i wanted to revert back to Physical standby

SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;

NAME      OPEN_MODE            GUARD_S DATABASE_ROLE
--------- -------------------- ------- ----------------
MYPROD    READ WRITE           NONE    SNAPSHOT STANDBY

While Converting Snapshot standby back to Physical standby while both RAC Nodes are up and running we get this error


SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

ALTER DATABASE CONVERT TO PHYSICAL STANDBY

*

ERROR at line 1:

ORA-38777: database must not be started in any other instance


srvctl status database -d myprod_std -v
Instance myprod_std1 is running on node hostname1. Instance status: Mounted (Closed).
Instance myprod_std2 is running on node hostname2. Instance status: Mounted (Closed).


NOTE : 

   Instance should open in only 1 node to Convert back

 $ srvctl stop instance -i myprod2 -d myprod_std
 
hostname:myprod1:/export/scripts $ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 9 13:11:19 2023
Version 19.17.0.0.0
 
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
 
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
 
Database altered.
 
SQL> shut immediate
ORA-01109: database not open
 
 
Database dismounted.
ORACLE instance shut down.
SQL> exit
 
$ srvctl start database -d myprod_std -v
 
$ srvctl status database -d myprod_std -v

Instance myprod_std1 is running on node hostname1. Instance status: Mounted (Closed).

Instance myprod_std2 is running on node hostname2. Instance status: Mounted (Closed).


 
hostname:myprod_std:/export/scripts $ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 9 13:13:41 2023
Version 19.17.0.0.0
 
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
 
SQL> alter database open;
 
Database altered.
 
SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
 
NAME      OPEN_MODE            GUARD_S  DATABASE_ROLE
--------- -------------------- ------- ----------------
MYPROD    READ ONLY            NONE     PHYSICAL STANDBY
 
 Start MRP .......

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





Sunday, May 12, 2019

RFS[11]: Client instance is standby database instead of primary


Server: RHEL6
Oracle DB version: 12.1

In my Primary Alert log i'm seeing RFS messages writing frequently like below

Primary alert log :

RFS[10]: Assigned to RFS process (PID:16586)
RFS[10]: Database mount ID mismatch [0x6666ae9b:0x666707e4] (1718005403:171802260)
RFS[10]: Client instance is standby database instead of primary
Tue Feb 19 16:10:14 2019
RFS[11]: Assigned to RFS process (PID:174682)
RFS[11]: Database mount ID mismatch [0x6666ae9b:0x666707e4] (1718005403:1718028260)
RFS[11]: Client instance is standby database instead of primary

 After researching further and according to Oracle (Doc ID 1450132.1)

Cause:

It's due to my Standby database is sending ARCH logs back to my Primary site (RAC exadata machine) (don’t know how this was happening) and causing these messages writing to my Primary alert log  and to fix this I’ve to find and remove "dest_" parameter which is pointing back to primary site

On standby site

SQL> sho parameter dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_2          string
log_archive_dest_2                   string      service=myprod_prim ARCH SYNC NO
                                                              AFFIRM delay=0 OPTIONAL reopen=15 register


SQL> sho parameter dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      ENABLE

Yes i see my dest_2 is pointing to Primary and it is ENABLED so remove this parameter (OR) DEFER it 

FIX:

SQL> ALTER SYSTEM SET log_archive_dest_2='' scope=both;

System altered.

Now i see  NO messages are writing in alert log

Saturday, March 31, 2018

Convert 12c STANDBY database to SNAPSHOT Standby


Hello Everyone, today I’m going to write a post on how to convert a 12c standalone standby database to SNAPSHOT STANDBY database (which is Read/Write Mode)

Before Converting lets check the current status of the standby database

STANDBY database:

orastandby:myprod:/export/myprod $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 13 15:20:02 2017

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, OLAP, Advanced Analytics and Real Application Testing options

SQL> Show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /pkg/myprod/u200
db_recovery_file_dest_size           big integer 5G

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> SELECT FILE_TYPE "Type",PERCENT_SPACE_USED "% Used",PERCENT_SPACE_RECLAIMABLE "% Reclaim",
NUMBER_OF_FILES "# Files" FROM V$FLASH_RECOVERY_AREA_USAGE;

select space_limit/1024/1024/1024 maxGB,space_used/1024/1024/1024 usedGB from v$recovery_file_dest;


SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;

NAME      OPEN_MODE            GUARD_S DATABASE_ROLE
--------- -------------------- ------- ----------------
MYPROD    READ ONLY WITH APPLY NONE    PHYSICAL STANDBY

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1          72036

SQL> set line 280
SQL> SELECT name "Database name", STATUS "MRP process",ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (select name from v$database) name,(SELECT STATUS  FROM V$MANAGED_STANDBY where process like '%MRP%') process,(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Database  MRP process      Thread Last Sequence Received Last Sequence Applied Difference
--------- ------------ ---------- ---------------------- --------------------- ----------
MYPROD    WAIT_FOR_LOG          1                  72036                 72036          0

SQL> alter system set db_recovery_file_dest_size=30G  scope=both;

System altered.

SQL> Show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /pkg/myprod/u200
db_recovery_file_dest_size           big integer 30G

Check Filesystem space………

SQL> !df -h /pkg/myprod/u200
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg01130-lvol11
                      473G  204G  245G  46% /pkg/myprod/u200

SQL>  select protection_mode, protection_level from v$database;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE


STOP MRP:

SQL> set time on
15:27:39 SQL> alter database recover managed standby database cancel;

Database altered.

Check any Restore points

15:29:52 SQL> select * from v$restore_point;

 SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME                      RESTORE_POINT_TIME                                                          PRE NAME                       CON_ID
---------- --------------------- --- ------------ ------------------------- --------------------------------------------------------------------------- --- ---------------------- ----------
2.4753E+11                     1 NO             0 23-AUG-14 01.52.56.000000000 AM                                                                             NO  DR_BR_TO_RN_082214              0
                                                 

15:32:26 SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.

15:32:42 SQL> shut immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
15:33:15 SQL> Startup
ORACLE instance started.

Total System Global Area 5368709120 bytes
Fixed Size                  3722144 bytes
Variable Size            2919236704 bytes
Database Buffers         2147483648 bytes
Redo Buffers              298266624 bytes
Database mounted.
Database opened.

15:33:26 SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;

NAME                   OPEN_MODE            GUARD_S DATABASE_ROLE
---------------------- -------------------- ------- ----------------
MYPROD                 READ WRITE           NONE    SNAPSHOT STANDBY

15:33:34 SQL>  col TIME for a25
15:33:47 SQL> col NAME for a22
15:33:49 SQL> set line 280
15:33:53 SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT;

NAME                          SCN TIME                      DATABASE_INCARNATION# GUA STORAGE_SIZE
---------------------- ---------- ------------------------- --------------------- --- ------------
SNAPSHOT_STANDBY_REQUI 3.1991E+11 13-JAN-17 03.32.39.000000                     1 YES    134217728
RED_01/13/2017 15:32:39           000 PM


By DEFAULT Oracle will create a Guarantee Restore point and it uses this during reverting back to standby.

15:34:12 SQL>  select space_limit/1024/1024/1024 maxGB,space_used/1024/1024/1024 usedGB from v$recovery_file_dest;

     MAXGB     USEDGB
---------- ----------
        30          5

Now my Standby database is now in Read/Write mode


REVERT BACK TO PHYSICAL STANDBY:


15:49:53 SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

15:50:28 SQL> startup mount
ORACLE instance started.

Total System Global Area 5368709120 bytes
Fixed Size                  3722144 bytes
Variable Size            2919236704 bytes
Database Buffers         2147483648 bytes
Redo Buffers              298266624 bytes
Database mounted.

15:50:59 SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;   (watch the alert log)

Database altered.

15:51:16 SQL>  shut immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

15:51:57 SQL> startup nomount
ORACLE instance started.

Total System Global Area 5368709120 bytes
Fixed Size                  3722144 bytes
Variable Size            2919236704 bytes
Database Buffers         2147483648 bytes
Redo Buffers              298266624 bytes

15:52:24 SQL> alter database mount standby database;

Database altered.

Check wallet and OPEN if it is closed:

SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC
------------------------------ -------------------- --------- ---------
    CON_ID
----------
FILE
/opt/oracle/admin/myprod/wallet
OPEN                           UNKNOWN              SINGLE    NO
         0

SQL> alter database recover managed standby database disconnect from session;

NOTE:    If you use just “alter database recover managed standby database” from sql then recovery starts and the sql session doesn’t release until all recovery is completed so always use “disconnect from session” and it will run in the back ground


If active standby:

SQL> alter database recover managed standby database using current logfile disconnect ; (starts mrp )

orastandby:myprod:/opt/oracle/scripts $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 13 15:54:44 2017

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, OLAP, Advanced Analytics and Real Application Testing options

SQL> col TIME for a25
SQL> col name for a44
SQL> set line 280
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT;

No rows selected.
                                                       
Restore point has been dropped.

SQL> select space_limit/1024/1024/1024 maxGB,space_used/1024/1024/1024 usedGB from v$recovery_file_dest;

     MAXGB     USEDGB
---------- ----------
        30      3.375

SQL> SELECT name "Database name", STATUS "MRP process",ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (select name from v$database) name,(SELECT STATUS  FROM V$MANAGED_STANDBY where process like '%MRP%') process,(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Database  MRP process      Thread Last Sequence Received Last Sequence Applied Difference
--------- ------------ ---------- ---------------------- --------------------- ----------
MYPROD    WAIT_FOR_LOG          1                      1                     1          0


SQL>  select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;

NAME                                         OPEN_MODE            GUARD_S DATABASE_ROLE
-------------------------------------------- -------------------- ------- ----------------
MYPROD                                       READ ONLY WITH APPLY NONE    PHYSICAL STANDBY

SQL>  select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
1                 72036


After few log switches at primary then I see everything is normal

SQL> SELECT name "Database name", STATUS "MRP process",ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (select name from v$database) name,(SELECT STATUS  FROM V$MANAGED_STANDBY where process like '%MRP%') process,(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Database  MRP process      Thread Last Sequence Received Last Sequence Applied Difference
--------- ------------ ---------- ---------------------- --------------------- ----------
MYPROD    WAIT_FOR_LOG          1                  72041                 72041          0

Now "MYPROD" database is back to standby mode and working fine, Thanks 

Auto Scroll Stop Scroll