Lokeshaggarwal's DBA

Live the life you love. Love the life you live

Switchover from primary to standby database


Here, we are performing a switchover from tester(primary) database to standby database.

Primary Database (Tester)

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
——————–
SESSIONS ACTIVE

 

Session Active status means that you have to perform a switchover from WITH SESSION SHUTDOWN clause.It means that many users are still connecting to the database, so perform a session shutdown clause.

 

SQL> SELECT PROGRAM, TYPE FROM V$SESSION WHERE TYPE=’USER’;

PROGRAM                                                          TYPE
—————————————————————- ———-

 

SQL> SELECT PROGRAM, TYPE FROM V$SESSION WHERE TYPE=’USER’;

PROGRAM                                                          TYPE
—————————————————————- ———-
emagent.exe                                                      USER
ORACLE.EXE                                                       USER
sqlplus.exe                                                      USER

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY
  2  WITH SESSION SHUTDOWN;

Database altered.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             218106756 bytes
Database Buffers          385875968 bytes
Redo Buffers                7135232 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Database altered.

SQL> SHOW PARAMETER DB_NAME

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      tester
SQL> SELECT THREAD#, SEQUENCE#, APPLIED,to_char(COMPLETION_TIME,’DD-MON-YYYY:HH2
4:MI:SS’) FROM V$ARCHIVED_LOG;

   THREAD#  SEQUENCE# APP TO_CHAR(COMPLETION_T
———- ———- — ——————–
         1         29 NO  07-OCT-2011:20:49:17
         1         30 NO  10-OCT-2011:14:29:36
         1         31 NO  11-OCT-2011:21:41:22
         1         32 NO  11-OCT-2011:22:16:18
         1         32 NO  11-OCT-2011:22:16:19
         1         32 NO  11-OCT-2011:22:16:20
         1         33 NO  11-OCT-2011:22:47:50
         1         34 NO  12-OCT-2011:19:18:14
         1         34 NO  12-OCT-2011:19:18:15
         1         34 NO  12-OCT-2011:19:18:16
         1         35 NO  13-OCT-2011:21:06:59

   THREAD#  SEQUENCE# APP TO_CHAR(COMPLETION_T
———- ———- — ——————–
         1         36 NO  14-OCT-2011:09:55:03
         1         37 NO  14-OCT-2011:09:56:02
         1         38 NO  14-OCT-2011:09:56:23
         1         39 NO  14-OCT-2011:10:28:16
         1         39 NO  14-OCT-2011:10:28:17
         1         40 NO  14-OCT-2011:10:28:21
         1         41 NO  14-OCT-2011:21:02:05
         1         41 NO  14-OCT-2011:21:02:07
         1         42 NO  14-OCT-2011:21:02:10
         1         43 NO  14-OCT-2011:21:02:59
         1         44 NO  14-OCT-2011:22:08:59

   THREAD#  SEQUENCE# APP TO_CHAR(COMPLETION_T
———- ———- — ——————–
         1         44 NO  14-OCT-2011:22:09:00
         1         44 NO  14-OCT-2011:22:09:20
         1         41 NO  14-OCT-2011:22:09:40
         1         42 NO  14-OCT-2011:22:09:47
         1         43 NO  14-OCT-2011:22:09:54
         1         45 NO  14-OCT-2011:22:10:47
         1         45 YES 14-OCT-2011:22:11:21
         1         46 NO  15-OCT-2011:19:16:03
         1         46 NO  15-OCT-2011:19:16:04
         1         47 NO  15-OCT-2011:19:16:48
         1         48 NO  15-OCT-2011:19:17:04

   THREAD#  SEQUENCE# APP TO_CHAR(COMPLETION_T
———- ———- — ——————–
         1         49 NO  15-OCT-2011:19:19:13
         1         50 NO  15-OCT-2011:19:20:05
         1         51 NO  15-OCT-2011:19:20:08
         1         52 NO  15-OCT-2011:19:24:21
         1         53 NO  15-OCT-2011:19:24:59
         1         53 NO  15-OCT-2011:19:25:00
         1         46 NO  15-OCT-2011:19:25:11
         1         47 NO  15-OCT-2011:19:25:14
         1         48 NO  15-OCT-2011:19:25:14
         1         49 NO  15-OCT-2011:19:25:18
         1         50 NO  15-OCT-2011:19:25:18

   THREAD#  SEQUENCE# APP TO_CHAR(COMPLETION_T
———- ———- — ——————–
         1         51 NO  15-OCT-2011:19:25:23
         1         52 NO  15-OCT-2011:19:25:28
         1         54 YES 15-OCT-2011:19:26:33
         1         54 NO  15-OCT-2011:19:26:59
         1         55 YES 15-OCT-2011:19:32:00
         1         55 NO  15-OCT-2011:19:32:12
         1         56 YES 15-OCT-2011:19:49:15
         1         56 NO  15-OCT-2011:19:49:26
         1         57 NO  15-OCT-2011:20:05:05
         1         57 NO  15-OCT-2011:20:05:13
         1         58 NO  15-OCT-2011:20:06:58

   THREAD#  SEQUENCE# APP TO_CHAR(COMPLETION_T
———- ———- — ——————–
         1         58 NO  15-OCT-2011:20:06:59

56 rows selected.

SQL> SELECT THREAD#, MAX(SEQUENCE#) AS “LAST_APPLIED_LOG”    FROM V$LOG_HISTORY
    GROUP BY
  2  THREAD#;

   THREAD# LAST_APPLIED_LOG
———- —————-
         1               57

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_S
TANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
——— ———— ———- ———- ———- ———-
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0

SQL>  alter database recover managed standby database disconnect from session;
 alter database recover managed standby database disconnect from session
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
SQL>  alter database recover managed standby database;
Previous standby
—————————-

 Here we are performing a switchover from standby to primary database.

 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
——————–
TO PRIMARY

SQL> alter database commit to switchover to primary;

Database altered.

SQL> SHUTDOWN IMMEDIATE
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             180358020 bytes
Database Buffers          423624704 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.
SQL> SHOW PARAMETER DB_NAME

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      TESTER
SQL> SHOW PARAMETER DB_UNI

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_unique_name                       string      STANDBY
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
——————–
SESSIONS ACTIVE

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> SELECT THREAD#, SEQUENCE#, APPLIED,to_char(COMPLETION_TIME,’DD-MON-YYYY:HH2
4:MI:SS’) FROM V$ARCHIVED_LOG;

   THREAD#  SEQUENCE# APP TO_CHAR(COMPLETION_T
———- ———- — ——————–
         1         44 YES 14-OCT-2011:22:09:37
         1         41 YES 14-OCT-2011:22:09:39
         1         42 YES 14-OCT-2011:22:09:46
         1         43 YES 14-OCT-2011:22:09:53
         1         45 YES 14-OCT-2011:22:11:23
         1         53 YES 15-OCT-2011:19:25:00
         1         46 YES 15-OCT-2011:19:25:10
         1         47 YES 15-OCT-2011:19:25:14
         1         48 YES 15-OCT-2011:19:25:14
         1         49 YES 15-OCT-2011:19:25:18
         1         50 YES 15-OCT-2011:19:25:18

   THREAD#  SEQUENCE# APP TO_CHAR(COMPLETION_T
———- ———- — ——————–
         1         51 YES 15-OCT-2011:19:25:22
         1         52 YES 15-OCT-2011:19:25:28
         1         54 YES 15-OCT-2011:19:26:46
         1         55 YES 15-OCT-2011:19:32:05
         1         56 YES 15-OCT-2011:19:49:18
         1         57 YES 15-OCT-2011:20:05:08
         1         58 YES 15-OCT-2011:20:06:57
         1         59 NO  15-OCT-2011:20:18:21

19 rows selected.

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_S
TANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
——— ———— ———- ———- ———- ———-
ARCH      CLOSING               1         59          1        657
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
——————–
SESSIONS ACTIVE

Now, lets perform a switchover operation using DGMGRL(Data Guard Manager Role)

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>dgmgrl
DGMGRL for 32-bit Windows: Version 10.2.0.1.0 – Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.
DGMGRL> connect sys/oracle@tester
Connected.
DGMGRL> show configuration

Configuration
  Name:                primary
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    tester  – Primary database
    standby – Physical standby database

Current status for “primary”:
SUCCESS

DGMGRL> switchover to standby
Performing switchover NOW, please wait…
Operation requires shutdown of instance “tester” on database “tester”
Shutting down instance “tester”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance “standby” on database “standby”
Shutting down instance “standby”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “tester” on database “tester”
Starting instance “tester”…
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Failed.
You are no longer connected to ORACLE
Please connect again.
Unable to start instance “tester”
You must start instance “tester” manually
Operation requires startup of instance “standby” on database “standby”
You must start instance “standby” manually
Switchover succeeded, new primary is “standby”
DGMGRL> connect sys/oracle@standby
Connected.
DGMGRL> show configuration

Configuration
  Name:                primary
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    tester  – Physical standby database
    standby – Primary database

Current status for “primary”:
SUCCESS

DGMGRL>

 

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>set oracle_sid=tester

C:\Documents and Settings\Administrator>sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Wed Oct 19 18:24:36 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> exit
ERROR:
ORA-03113: end-of-file on communication channel
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – P
oduction
With the Partitioning, OLAP and Data Mining options (with complications)

C:\Documents and Settings\Administrator>sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Wed Oct 19 18:35:31 2011

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             197135236 bytes
Database Buffers          406847488 bytes
Redo Buffers                7135232 bytes
SQL> alter database mount standby database;

Database altered.

SQL> show parameter db_uniq

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_unique_name                       string      tester
SQL>

SQL> select database_role from v$database;

DATABASE_ROLE
—————-
PHYSICAL STANDBY

SQL>
Previous  Standby Database

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>set oracle_sid=standby

C:\Documents and Settings\Administrator>sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Wed Oct 19 18:26:09 2011

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             184552324 bytes
Database Buffers          419430400 bytes
Redo Buffers                7135232 bytes
SQL> alter database mount standby database;

Database altered.

SQL> startup
ORA-03113: end-of-file on communication channel
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             184552324 bytes
Database Buffers          419430400 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.
SQL> select database_role from v$database;

DATABASE_ROLE
—————-
PRIMARY

See, in the above we can see that Database Role here has been switched from Standby Role to Primary Role.

SQL> show parameter db_name

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      TESTER
SQL>

 
 

 

Advertisements

October 19, 2011 - Posted by | Data Guard

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: