Alok's view of the world and everything within !!!!: 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 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

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

Alok's view of the world and everything within !!!!

Monday, 8 May 2017

Oracle: Primary & Standby switch roles -Switchover easy

No comments:

Post a Comment

Still learning ways of the world after 23yrs in IT/Database arena.

About Me

Total Pageviews