Rolling forward physical standby database using RMAN incremental SCN backup

Rolling forward physical standby database using RMAN incremental SCN backup 
*************************************************************************************************

At PRIMARY database

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

MAX(SEQUENCE#)
--------------
            17

At STANDBY database

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

MAX(SEQUENCE#)
--------------
            10

Mistakenly archive log sequence no 11 deleted from primary database archive log location, till archive logs#16 received at physical standby.
But due missing archive log# 11, MRP process has been stopped, which results archive log gap. 

Below is practical illustration, how to roll forward physical standby databases using RMAN incremental SCN backup.


1) Take min SCN from out of 3 queries from Physical standby

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------------
962773 

SQL> select database_role, current_scn from v$database;

SQL> select min(checkpoint_change#) from v$datafile_header;

2) Stop MRP process at standby database

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3) Check MRP status

select RECOVERY_MODE from v$archive_dest_status;
select process,sequence#,status from v$managed_standby;

4) Take SCN based backup from primary database in order to sync with physical standby

$ rman target sys/dg123@prod

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jun 24 12:00:20 2017

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

connected to target database: prod (DBID=3216454145)

RMAN> backup incremental from scn 962773 database format '/u01/prod/fra/StbyIncr_%U';

Starting backup at 24-JUN-17

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
backup will be obsolete on date 01-JUL-17
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/prod/datafiles/prod/system01.dbf
input datafile file number=00002 name=/u01/prod/datafiles/prod/sysaux01.dbf
input datafile file number=00003 name=/u01/prod/datafiles/prod/undotbs01.dbf
input datafile file number=00004 name=/u01/prod/datafiles/prod/users01.dbf
channel ORA_DISK_1: starting piece 1 at 24-JUN-17
channel ORA_DISK_1: finished piece 1 at 24-JUN-17
piece handle=/u01/prod/fra/StbyIncr_0as7jiv4_1_1 tag=TAG20170624T120908 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

using channel ORA_DISK_1
backup will be obsolete on date 01-JUL-17
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 24-JUN-17
channel ORA_DISK_1: finished piece 1 at 24-JUN-17
piece handle=/u01/prod/fra/StbyIncr_0bs7jiv5_1_1 tag=TAG20170624T120908 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 24-JUN-17

RMAN> backup current controlfile for standby format '/u01/prod/fra/Stby_control_%U';

Starting backup at 24-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 24-JUN-17
channel ORA_DISK_1: finished piece 1 at 24-JUN-17
piece handle=/u01/prod/fra/Stby_control_0cs7jive_1_1 tag=TAG20170624T120918 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JUN-17

RMAN> exit


5) Copy RMAN backup including controlfile to standby FRA.

6) Connect RMAN to standby target

$ rman target sys/dg123@standby

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jun 24 12:14:20 2017

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

connected to target database: prod (DBID=3170486146)

RMAN> catalog start with '/u01/standby/fra/';

using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/standby/fra/

List of Files Unknown to the Database
=====================================
File Name: /u01/standby/fra/Stby_control_0cs7jive_1_1
File Name: /u01/standby/fra/StbyIncr_0as7jiv4_1_1
File Name: /u01/standby/fra/StbyIncr_0bs7jiv5_1_1

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

List of Cataloged Files
=======================
File Name: /u01/standby/fra/Stby_control_0cs7jive_1_1
File Name: /u01/standby/fra/StbyIncr_0as7jiv4_1_1
File Name: /u01/standby/fra/StbyIncr_0bs7jiv5_1_1


7) Before proceed with recover database, make sure standby database should be MOUNT state. If not below error will occure


RMAN> recover database noredo;

Starting recover at 24-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/standby/datafiles/system01.dbf
destination for restore of datafile 00002: /u01/standby/datafiles/sysaux01.dbf
destination for restore of datafile 00003: /u01/standby/datafiles/undotbs01.dbf
destination for restore of datafile 00004: /u01/standby/datafiles/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/standby/fra/StbyIncr_0as7jiv4_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/24/2017 12:16:25
ORA-19870: error while restoring backup piece /u01/standby/fra/StbyIncr_0as7jiv4_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 1


so,

SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ ONLY            MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1286066176 bytes
Fixed Size                  2213016 bytes
Variable Size             754977640 bytes
Database Buffers          520093696 bytes
Redo Buffers                8781824 bytes
Database mounted.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@stand dbs]$
[oracle@stand dbs]$  rman target sys/dg123@standby

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jun 24 13:05:24 2017

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

connected to target database: prod (DBID=3170486146, not open)

RMAN> recover database noredo;

Starting recover at 24-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/standby/datafiles/system01.dbf
destination for restore of datafile 00002: /u01/standby/datafiles/sysaux01.dbf
destination for restore of datafile 00003: /u01/standby/datafiles/undotbs01.dbf
destination for restore of datafile 00004: /u01/standby/datafiles/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/standby/fra/StbyIncr_0as7jiv4_1_1
channel ORA_DISK_1: piece handle=/u01/standby/fra/StbyIncr_0as7jiv4_1_1 tag=TAG20170624T120908
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished recover at 24-JUN-17

RMAN> 

[oracle@stand dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jun 24 13:06:30 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MOUNTED              MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED

SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.


SQL> startup nomount
ORACLE instance started.

Total System Global Area 1286066176 bytes
Fixed Size                  2213016 bytes
Variable Size             754977640 bytes
Database Buffers          520093696 bytes
Redo Buffers                8781824 bytes
SQL> !

[oracle@stand fra]$ ll
total 19720
drwxr-x--- 4 oracle oinstall    4096 Jun 24 11:37 standby
-rw-r----- 1 oracle oinstall 9797632 Jun 24 12:13 Stby_control_0cs7jive_1_1
-rw-r----- 1 oracle oinstall  557056 Jun 24 12:13 StbyIncr_0as7jiv4_1_1
-rw-r----- 1 oracle oinstall 9797632 Jun 24 12:14 StbyIncr_0bs7jiv5_1_1
[oracle@stand fra]$
[oracle@stand fra]$ rman target sys/dg123@standby

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jun 24 13:07:29 2017

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

connected to target database: prod (not mounted)

RMAN> restore standby controlfile from '/u01/standby/fra/Stby_control_0cs7jive_1_1';

Starting restore at 24-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/standby/datafiles/stndcnt.ctl
Finished restore at 24-JUN-17

RMAN> exit


Recovery Manager complete.


SQL> shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.


SQL> startup mount
ORACLE instance started.

Total System Global Area 1286066176 bytes
Fixed Size                  2213016 bytes
Variable Size             754977640 bytes
Database Buffers          520093696 bytes
Redo Buffers                8781824 bytes
Database mounted.
SQL>
SQL>
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MOUNTED              MAXIMUM PERFORMANCE  PHYSICAL STANDBY RECOVERY NEEDED

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

Database altered.

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

MAX(SEQUENCE#)
--------------
            17

SQL>
SQL> /

MAX(SEQUENCE#)
--------------
            18



SQL> select count(1) from sinu.emp;
select count(1) from sinu.emp;
                     *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only


SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MOUNTED              MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in
progress


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL>
SQL>
SQL> alter database open;

Database altered.

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

Database altered.

SQL>
SQL>
SQL>
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ ONLY WITH APPLY MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED

SQL> select count(1) from sinu.emp;

  COUNT(1)
----------
         4

SQL> select * from sinu.emp;

        ID
----------
         1
         1
         1
         1

SQL>

Happy DBA'ing :)

Conversion of Physical standby to Snapshot standby and vice versa

Conversion of PHYSICAL standby to SNAPSHOT standby
******************************************************************

SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MOUNTED              MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED

SQL>
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL>
SQL> sho parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/standby/fra
db_recovery_file_dest_size           big integer 3882M
SQL>
SQL>
SQL> alter database recover managed standby database cancel;

Database altered.

SQL>
SQL>
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 1286066176 bytes
Fixed Size                  2213016 bytes
Variable Size             754977640 bytes
Database Buffers          520093696 bytes
Redo Buffers                8781824 bytes
Database mounted.
SQL>
SQL> alter database convert to snapshot standby;

Database altered.

SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MOUNTED              MAXIMUM PERFORMANCE  SNAPSHOT STANDBY NOT ALLOWED

SQL> alter database open;

Database altered.

SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;


OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ WRITE           MAXIMUM PERFORMANCE  SNAPSHOT STANDBY NOT ALLOWED

SQL> SQL>



Conversion of SNAPSHOT standby to PHYSICAL standby
*******************************************************************

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 1286066176 bytes
Fixed Size                  2213016 bytes
Variable Size             754977640 bytes
Database Buffers          520093696 bytes
Redo Buffers                8781824 bytes
Database mounted.
SQL>
SQL> alter database convert to physical standby;

Database altered.

SQL> SQL> shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.


SQL> startup
ORACLE instance started.

Total System Global Area 1286066176 bytes
Fixed Size                  2213016 bytes
Variable Size             754977640 bytes
Database Buffers          520093696 bytes
Redo Buffers                8781824 bytes
Database mounted.
Database opened.
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ ONLY            MAXIMUM PERFORMANCE  PHYSICAL STANDBY RECOVERY NEEDED

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

Database altered.

SQL>
SQL> select RECOVERY_MODE from v$archive_dest_status;

RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE

32 rows selected.

SQL>
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ ONLY WITH APPLY MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED

SQL>
SQL>

Step by step Physical standby database creation using RMAN DUPLICATE in oracle 11g.

Step by step Physical standby database creation using RMAN DUPLICATE in oracle 11g.
*******************************************************************************************************

Oracle binaries installed with one stand alone database created.

IP: 192.168.56.2
Primary DB_NAME=prod
Primary DB_UNIQUE_NAME=prod

on Physical standby, I have installed only oracle binaries.

IP: 192.168.56.3
Primary DB_NAME=prod
Primary DB_UNIQUE_NAME=stdby

Required Parameters:
********************
                          
DB_NAME    - Must be same on primary & on all standby
DB_UNIQUE_NAME    - Must be UNIQUE on all database respectively
LOG_ARCHIVE_CONFIG    - This parameter includes DB_UNIQUE_NAME which are the part of DATAGUARD configuration
LOG_ARCHIVE_DEST_n    - Define Local & Remote archive log file location
LOG_ARCHIVE_DEST_STATE_n - Define state of archiving (ENABLE or DEFER)
REMOTE_LOGIN_PASSWORDFILE - Must be in EXCLUSIVE mode
FAL_SERVER - Use for archivelog gap resolution (required only in physical standby server)
DB_FILE_NAME_CONVERT - Required when directory structure is different FOR datafiles
LOG_FILE_NAME_CONVERT - Required when directory structure is different FOR logfiles
STANDBY_FILE_MANAGEMENT - Keep AUTO to create file automatically on standby


STEPS:
******
on PRIMARY database:

1) ENABLE ARCHIVELOG MODE

SELECT LOG_MODE FROM V$DATABASE;

IF ARCHIVELOG MODE is DISABLE, follow below steps to enable it.

SHUT IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

2) ENABLE FORCE LOGGING MODE

SELECT FORCE_LOGGING FROM V$DATABASE;

ALTER DATABASE FORCE LOGGING;

3) VERIFY DB_NAME & DB_UNIQUE_NAME OF Primary Database

SHOW PARAMETER DB_NAME;
SHOW PARAMETER DB_UNIQUE_NAME;

4) CONFIGURE LOG_ARCHIVE_CONFIG PARAMETER

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,stdby)';

5) SET ARCHIVELOG DESTINATION FOR STANDBY 

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ngprd VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ngprd';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

6) SET REMOTE LOGIN PASSWORD TO EXCLUSIVE

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE;

7) SET FAL_SERVER PARAMETER

ALTER SYSTEM SET FAL_SERVER=stdby;

8) ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

9) CONFIGURE REQUIRED NETWORK SERVICES using "NETMGR"


10) CREATE STANDBY CONTROLFILE & PFILE

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/stndcnt.ctl';
CREATE PFILE='/tmp/initstdby.ora' FROM SPFILE;

11) MODIFY initstdby.ora file 

i)   change DB_UNIQUE_NAME
ii)  change FAL_SERVER
iii) change log_archive_dest_n
iv)  change location of controlfile

To achieve switchover & switchback (DR) activity, modify same parameters as in production into standby pfile.

12) CREATE APPROPRIATE DIRECTORY STRUCTURE AND MOVE BACKUP, PASSWORD FILE, CONTROLFILE & PFILE

NOTE:- If you fail to move password file to standby destination, you will face below error, while transporting logs to standy site.

ORA-01031: insufficient privileges

In order to rectify this error, move password file to $ORACLE_HOME/dbs location and rename with ora$ORACLE_SID.


ON PHYSICAL SERVER:
*******************
1) CREATE NETWORK SERVICES & UPDATE /etc/oratab FILE

2) START LISTENER on BOTH database

3) Startup database in nomount with updated standby pfile.


SQL> startup nomount pfile='/u01/app/oracle/standbyuct/1120/dbhome/dbs/initstdby.ora';
ORACLE instance started.

Total System Global Area 1336176640 bytes
Fixed Size                  2213136 bytes
Variable Size             788531952 bytes
Database Buffers          536870912 bytes
Redo Buffers                8560640 bytes
SQL>


ON PRIMARY SERVER:
******************

13) Connect to RMAN with target and auxiliary server


[oracle@prod ~]$ rman target sys/dg123@prod auxiliary sys/dg123@stdby

Recovery Manager: Release 11.2.0.1.0 - standbyuction on Sun Jun 18 00:55:50 2017

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

connected to target database: prod (DBID=3169964256)
connected to auxiliary database: prod (not mounted)

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

Starting Duplicate Db at 18-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/standbyuct/1120/dbhome/dbs/orapwprod' auxiliary format
 '/u01/app/oracle/standbyuct/1120/dbhome/dbs/orapwstdby'   ;
}
executing Memory Script

Starting backup at 18-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
Finished backup at 18-JUN-17

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/prod/datafiles/prod/stndcnt.ctl';
}
executing Memory Script

Starting backup at 18-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/standbyuct/1120/dbhome/dbs/snapcf_prod.f tag=TAG20170618T005703 RECID=3 STAMP=946947426
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 18-JUN-17

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/prod/datafiles/prod/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/prod/datafiles/prod/system01.dbf";
   set newname for datafile  2 to
 "/u01/prod/datafiles/prod/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/prod/datafiles/prod/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/prod/datafiles/prod/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/u01/prod/datafiles/prod/system01.dbf"   datafile
 2 auxiliary format
 "/u01/prod/datafiles/prod/sysaux01.dbf"   datafile
 3 auxiliary format
 "/u01/prod/datafiles/prod/undotbs01.dbf"   datafile
 4 auxiliary format
 "/u01/prod/datafiles/prod/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/prod/datafiles/prod/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 18-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/prod/datafiles/prod/system01.dbf
output file name=/u01/prod/datafiles/prod/system01.dbf tag=TAG20170618T005716
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/prod/datafiles/prod/sysaux01.dbf
output file name=/u01/prod/datafiles/prod/sysaux01.dbf tag=TAG20170618T005716
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/prod/datafiles/prod/undotbs01.dbf
output file name=/u01/prod/datafiles/prod/undotbs01.dbf tag=TAG20170618T005716
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/prod/datafiles/prod/users01.dbf
output file name=/u01/prod/datafiles/prod/users01.dbf tag=TAG20170618T005716
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18-JUN-17

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=946947666 file name=/u01/prod/datafiles/prod/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=946947666 file name=/u01/prod/datafiles/prod/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=946947666 file name=/u01/prod/datafiles/prod/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=946947666 file name=/u01/prod/datafiles/prod/users01.dbf
Finished Duplicate Db at 18-JUN-17

RMAN>
RMAN>
RMAN> exit


Recovery Manager complete.
[oracle@standby ~]$


ON STANDBY SERVER
******************

4) Check the database status and start MRP process

SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MOUNTED              MAXIMUM PERFORMANCE  PHYSICAL STANDBY RECOVERY NEEDED

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

Database altered.

SQL> select recovery_mode from v$archive_dest_status;

RECOVERY_MODE
-----------------------
MANAGED
IDLE
IDLE
IDLE
IDLE
IDLE

32 rows selected.

SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MOUNTED              MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED

SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 1336176640 bytes
Fixed Size                  2213136 bytes
Variable Size             788531952 bytes
Database Buffers          536870912 bytes
Redo Buffers                8560640 bytes
Database mounted.
Database opened.
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ ONLY            MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED

ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival               Disabled
Archive destination              USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence   1
Current log sequence           2

SQL> alter system set log_archive_dest_1='/u01/prod/arch' ;
alter system set log_archive_dest_1='/u01/prod/arch'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE


When you move from the old log_archive_dest to the "new" log_archive_dest_<n>, a common mistake is missing the required string "location=" or "service=". 
So you need to say,

log_archive_dest_1='LOCATION=/u01/prod/arch'


SQL> alter system set log_archive_dest_1='LOCATION=/u01/prod/arch' scope=both;

System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup

OPatch failed with error code 73

Hi, while applying one bug fix to our production database, I had face below error,


#/u01/oracle/product/11.2.0/db_1/OPatch/./opatch apply

Oracle Interim Patch Installer version 11.2.0.3.16
Copyright (c) 2017, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/oracle/product/11.2.0/db_1
Central Inventory : /u01/oraInventory
from                  : /u01/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version    : 11.2.0.3.16
OUI version       : 11.2.0.3.0
Log file location : /u01/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2017-06-11_12-08-42PM_1.log

Verifying environment and performing prerequisite checks...
Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:

Following executables are active :
/u01/oracle/product/11.2.0/db_1/bin/oracle
UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.
Log file location: /u01/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2017-06-11_12-08-42PM_1.log

OPatch failed with error code 73

In order to investigate, I have crosschecked generated log file.


# more /u01/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2017-06-11_12-08-42PM_1.log
[INFO]   OPatch invoked as follows: 'apply -invPtrLoc /u01/oracle/product/11.2.0/db_1/oraInst.loc '
[INFO]   Oracle Home       : /u01/oracle/product/11.2.0/db_1
         Central Inventory : /u01/oraInventory
            from           : /u01/oracle/product/11.2.0/db_1/oraInst.loc
         OPatch version    : 11.2.0.3.16
         OUI version       : 11.2.0.3.0
         OUI location      : /u01/oracle/product/11.2.0/db_1/oui
         Log file location : /u01/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2017-06-11_12-08-42PM_1.log
[INFO]   Patch history file: /u01/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt
[INFO]   [OPSR-TIME] Loading raw inventory
[INFO]   Prereq checkPatchApplicableOnCurrentPlatform Passed for patch : 13399711
[INFO]   Start fuser command /usr/sbin/fuser /u01/oracle/product/11.2.0/db_1/bin/oracle at Sun Jun 11 12:08:47 IST 2017
[INFO]   Finish fuser command /usr/sbin/fuser /u01/oracle/product/11.2.0/db_1/bin/oracle at Sun Jun 11 12:08:47 IST 2017
[INFO]Files in use by a process: /u01/oracle/product/11.2.0/db_1/bin/oracle 
PID( 17561 )
[INFO]   Following executables are active :
         /u01/oracle/product/11.2.0/db_1/bin/oracle
[INFO]   Prerequisite check "CheckActiveFilesAndExecutables" failed.
                                    The details are:


                                    Following executables are active :
                                    /u01/oracle/product/11.2.0/db_1/bin/oracle
[SEVERE] OUI-67073:UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.
[INFO]   Finishing UtilSession at Sun Jun 11 12:08:47 IST 2017
[INFO]   Log file location: /u01/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2017-06-11_12-08-42PM_1.log
[INFO]   Stack Description: java.lang.RuntimeException: Prerequisite check "CheckActiveFilesAndExecutables" failed.
[INFO]   StackTrace: oracle.opatch.OPatchSessionHelper.runApplyPrereqs(OPatchSessionHelper.java:5917)
[INFO]   StackTrace: oracle.opatch.opatchutil.NApply.legacy_process(NApply.java:911)
[INFO]   StackTrace: oracle.opatch.opatchutil.NApply.legacy_process(NApply.java:347)
[INFO]   StackTrace: oracle.opatch.opatchutil.NApply.process(NApply.java:335)
[INFO]   StackTrace: oracle.opatch.opatchutil.OUSession.napply(OUSession.java:1102)
[INFO]   StackTrace: sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[INFO]   StackTrace: sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
[INFO]   StackTrace: sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
[INFO]   StackTrace: java.lang.reflect.Method.invoke(Method.java:597)
[INFO]   StackTrace: oracle.opatch.UtilSession.process(UtilSession.java:352)
[INFO]   StackTrace: oracle.opatch.OPatchSession.process(OPatchSession.java:2537)
[INFO]   StackTrace: oracle.opatch.OPatch.process(OPatch.java:821)
[INFO]   StackTrace: oracle.opatch.OPatch.main(OPatch.java:871)

In the log I come to know, one of the session still logged in, so opatch failed to process further.

#ps -ef|grep -i 17561

oracle 17561     1  0 11:58:22 ?     0:01 oracleprod (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

oracle 22939 18323  0 12:16:36 pts/5     0:00 grep -i 17561

# ptree 17561

17561      oracleprod (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))

# ps -ef|grep -i 17561

  oracle 23113 18323  0 12:17:27 pts/5     0:00 grep -i 17561

I have disconnected that session and process further to apply patch and successfully completed patching.

Hope this blog will help... cheers :)