Oracle: Primary & Standby switch roles -Switchover easy

This is normally a planned activity. Roles could be flipped as when required for testing or any other purpose as application stack validation.

Steps on Primary : (First primary is converted to standby, followed by  standby to primary).

SQL> -----this is primary
SQL> select NAME,OPEN_MODE,DATABASE_ROLE,GUARD_STATUS from gv$database;

NAME      OPEN_MODE            DATABASE_ROLE    GUARD_S
--------- -------------------- ---------------- -------
ALOK      READ WRITE           PRIMARY          NONE

SQL> set time on
10:33:22 SQL> /

NAME      OPEN_MODE            DATABASE_ROLE    GUARD_S
--------- -------------------- ---------------- -------
ALOK      READ WRITE           PRIMARY          NONE

10:33:23 SQL> alter system switch logfile;

System altered.

10:33:43 SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

10:34:03 SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

10:34:50 SQL> select NAME,OPEN_MODE,DATABASE_ROLE,GUARD_STATUS from gv$database;
select NAME,OPEN_MODE,DATABASE_ROLE,GUARD_STATUS from gv$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 11521
Session ID: 125 Serial number: 3


10:35:08 SQL> startup mount;
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
10:35:42 SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
oracle@localhost:/oradata$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon May 8 10:35:50 2017

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size                  2253584 bytes
Variable Size             973081840 bytes
Database Buffers          587202560 bytes
Redo Buffers                7471104 bytes
Database mounted.
SQL> select NAME,OPEN_MODE,DATABASE_ROLE,GUARD_STATUS from gv$database;

NAME      OPEN_MODE            DATABASE_ROLE    GUARD_S
--------- -------------------- ---------------- -------
ALOK      MOUNTED              PHYSICAL STANDBY NONE

SQL> alter database recover managed standby database  parallel 2 disconnect
/  2

Database altered.

SQL> select inst_id,process, status, sequence#, block#,thread# from gv$managed_standby  order by 1,2,3;

   INST_ID PROCESS   STATUS        SEQUENCE#     BLOCK#    THREAD#
---------- --------- ------------ ---------- ---------- ----------
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0
         1 MRP0      WAIT_FOR_LOG         10          0          1

SQL> set time on
10:37:03 SQL> /

   INST_ID PROCESS   STATUS        SEQUENCE#     BLOCK#    THREAD#
---------- --------- ------------ ---------- ---------- ----------
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0
         1 MRP0      WAIT_FOR_LOG         10          0          1

10:37:06 SQL> -----Standby is not converted
10:37:16 SQL> /

   INST_ID PROCESS   STATUS        SEQUENCE#     BLOCK#    THREAD#
---------- --------- ------------ ---------- ---------- ----------
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0
         1 MRP0      WAIT_FOR_LOG         10          0          1

10:38:54 SQL> ----post standby conversion to primary
10:40:50 SQL> /

   INST_ID PROCESS   STATUS        SEQUENCE#     BLOCK#    THREAD#
---------- --------- ------------ ---------- ---------- ----------
         1 ARCH      CLOSING              11          1          1
         1 ARCH      CLOSING              10          1          1
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0
         1 MRP0      WAIT_FOR_LOG         12          0          1
         1 RFS       IDLE                 12         12          1
         1 RFS       IDLE                  0          0          0
         1 RFS       IDLE                  0          0          0

8 rows selected.

10:40:52 SQL> /

   INST_ID PROCESS   STATUS        SEQUENCE#     BLOCK#    THREAD#
---------- --------- ------------ ---------- ---------- ----------
         1 ARCH      CLOSING              13          1          1
         1 ARCH      CLOSING              14          1          1
         1 ARCH      CLOSING              11          1          1
         1 ARCH      CONNECTED             0          0          0
         1 MRP0      WAIT_FOR_LOG         16          0          1
         1 RFS       IDLE                  0          0          0
         1 RFS       IDLE                  0          0          0
         1 RFS       IDLE                  0          0          0
         1 RFS       IDLE                 16          2          1

9 rows selected.

Steps on Standby :

SQL> ------This is Standby

SQL> l

  1* select inst_id,process, status, sequence#, block#,thread# from gv$managed_standby  order by 1,2,3

SQL> /

   INST_ID PROCESS   STATUS        SEQUENCE#     BLOCK#    THREAD#

---------- --------- ------------ ---------- ---------- ----------

         1 ARCH      CLOSING               5          1          1

         1 ARCH      CLOSING               6          1          1

         1 ARCH      CONNECTED             0          0          0

         1 ARCH      CONNECTED             0          0          0

         1 MRP0      WAIT_FOR_LOG          8          0          1

         1 RFS       IDLE                  0          0          0

         1 RFS       IDLE                  8        116          1

         1 RFS       IDLE                  0          0          0

         1 RFS       IDLE                  0          0          0

9 rows selected.

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

NAME      OPEN_MODE            DATABASE_ROLE    GUARD_S

--------- -------------------- ---------------- -------

ALOK      MOUNTED              PHYSICAL STANDBY NONE

SQL> set time on

10:33:14 SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

--------------------

TO PRIMARY

10:37:46 SQL> alter database commit to switchover to primary;

Database altered.

10:38:07 SQL> alter database commit to switchover to primary;

alter database commit to switchover to primary

*

ERROR at line 1:

ORA-01109: database not open

10:38:15 SQL> alter database open;

Database altered.

10:38:24 SQL> alter database commit to switchover to primary;

alter database commit to switchover to primary

*

ERROR at line 1:

ORA-16109: failed to apply log data from previous primary

10:38:27 SQL> select NAME,OPEN_MODE,DATABASE_ROLE,GUARD_STATUS from gv$database;

NAME      OPEN_MODE            DATABASE_ROLE    GUARD_S

--------- -------------------- ---------------- -------

ALOK      READ WRITE           PRIMARY          NONE

10:38:42 SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

10:39:32 SQL> startup;

ORACLE instance started. <=Done here, db is ready to go

-------Following steps for enabling log shipping  and spfile creation. (Optional)

Total System Global Area 1570009088 bytes

Fixed Size                  2253584 bytes

Variable Size             973081840 bytes

Database Buffers          587202560 bytes

Redo Buffers                7471104 bytes

Database mounted.

Database opened.

10:39:48 SQL> alter system set log_archive_dest_3='SERVICE=ALOK LGWR ASYNC=20480 reopen=60 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ALOK' scope=both;

alter system set log_archive_dest_3='SERVICE=ALOK LGWR ASYNC=20480 reopen=60 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ALOK' scope=both

*

ERROR at line 1:

ORA-32001: write to SPFILE requested but no SPFILE is in use

10:40:09 SQL> alter system set log_archive_dest_3='SERVICE=ALOK LGWR ASYNC=20480 reopen=60 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ALOK';

System altered.

10:40:25 SQL> create spfile from pfile;

File created.

10:41:22 SQL> startup force;

ORACLE instance started.

Total System Global Area 1570009088 bytes

Fixed Size                  2253584 bytes

Variable Size             973081840 bytes

Database Buffers          587202560 bytes

Redo Buffers                7471104 bytes

Database mounted.

Database opened.

10:41:39 SQL> alter system set log_archive_dest_3='SERVICE=ALOK LGWR ASYNC=20480 reopen=60 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ALOK' scope=both;

System altered.

10:41:51 SQL> alter system switch logfile;

System altered

At this ppoint role is reversed and new standby is in recovery.

Additional recommendation: Add standby log for real-time recovery on primary & standby. 

Recomended read:

http://aloksk.blogspot.in/2017/05/oracle-activate-standby-database.html

Oracle: Standby on same host

Source : Turn on force logging on and auto file management on Standby

initBLOK.ora   # this is standby pfile created to mount in NOMOUNT mode

racle@localhost:/u01/app/oracle/product/11.2.0/dbs$ cat initBLOK.ora
BLOK.__db_cache_size=603979776
BLOK.__java_pool_size=16777216
BLOK.__large_pool_size=33554432
BLOK.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
BLOK.__pga_aggregate_target=637534208
BLOK.__sga_target=939524096
BLOK.__shared_io_pool_size=0
BLOK.__shared_pool_size=251658240
BLOK.__streams_pool_size=16777216
*.audit_file_dest='/u01/app/oracle/admin/BLOK/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.db_domain=''
*.db_name='ALOK'
*.instance_name='BLOK'
*.db_unique_name='BLOK'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=BLOKXDB)'
*.memory_target=1572864000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='/oradata/ALOK','/oradata/BLOK'
*.log_file_name_convert='/oradata/ALOK','/oradata/BLOK'
control_files='/oradata/BLOK/control01.ctl','/oradata/BLOK/control02.ctl'

Note: Critical one in bold.

RMAN script:

DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  NOFILENAMECHECK;

Run log:
RMAN> DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  NOFILENAMECHECK;2> 3> 4>

Starting Duplicate Db at 08-MAY-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=10 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=135 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=11 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/dbs/orapwALOK' auxiliary format
 '/u01/app/oracle/product/11.2.0/dbs/orapwBLOK'   ;
}
executing Memory Script

Starting backup at 08-MAY-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=147 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=24 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=143 device type=DISK
Finished backup at 08-MAY-17

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/oradata/BLOK/control01.ctl';
   restore clone controlfile to  '/oradata/BLOK/control02.ctl' from
 '/oradata/BLOK/control01.ctl';
}
executing Memory Script

Starting backup at 08-MAY-17
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/oradata/backup/ALOK/snapcf_alok.f tag=TAG20170508T072157 RECID=12 STAMP=943428119
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 08-MAY-17

Starting restore at 08-MAY-17
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

channel ORA_AUX_DISK_2: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_3: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_4: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 08-MAY-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
 "/oradata/BLOK/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/oradata/BLOK/system01.dbf";
   set newname for datafile  2 to
 "/oradata/BLOK/sysaux01.dbf";
   set newname for datafile  3 to
 "/oradata/BLOK/undotbs01.dbf";
   set newname for datafile  4 to
 "/oradata/BLOK/users01.dbf";
   set newname for datafile  5 to
 "/oradata/BLOK/example01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/oradata/BLOK/system01.dbf"   datafile
 2 auxiliary format
 "/oradata/BLOK/sysaux01.dbf"   datafile
 3 auxiliary format
 "/oradata/BLOK/undotbs01.dbf"   datafile
 4 auxiliary format
 "/oradata/BLOK/users01.dbf"   datafile
 5 auxiliary format
 "/oradata/BLOK/example01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /oradata/BLOK/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 08-MAY-17
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oradata/ALOK/system01.dbf
channel ORA_DISK_2: starting datafile copy
input datafile file number=00002 name=/oradata/ALOK/sysaux01.dbf
channel ORA_DISK_3: starting datafile copy
input datafile file number=00005 name=/oradata/ALOK/example01.dbf
channel ORA_DISK_4: starting datafile copy
input datafile file number=00003 name=/oradata/ALOK/undotbs01.dbf
output file name=/oradata/BLOK/undotbs01.dbf tag=TAG20170508T072215
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:36
channel ORA_DISK_4: starting datafile copy
input datafile file number=00004 name=/oradata/ALOK/users01.dbf
output file name=/oradata/BLOK/example01.dbf tag=TAG20170508T072215
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:01:22
output file name=/oradata/BLOK/users01.dbf tag=TAG20170508T072215
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:55
output file name=/oradata/BLOK/sysaux01.dbf tag=TAG20170508T072215
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:02:52
output file name=/oradata/BLOK/system01.dbf tag=TAG20170508T072215
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:12
Finished backup at 08-MAY-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=12 STAMP=943428328 file name=/oradata/BLOK/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=13 STAMP=943428328 file name=/oradata/BLOK/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=943428328 file name=/oradata/BLOK/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=15 STAMP=943428328 file name=/oradata/BLOK/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=16 STAMP=943428328 file name=/oradata/BLOK/example01.dbf
Finished Duplicate Db at 08-MAY-17

RMAN> exit

Post this just adjust archive dest and fal and you are done.

on my primary:
alter system set log_archive_dest_3='SERVICE=BLOK LGWR ASYNC=20480 reopen=60 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=BLOK' scope=both;

on standby:
SQL> show parameter fal

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_client                           string

fal_server                           string      alok

and started mrp.