Switchover on Physical Standby (10.2.0.1) Manual

another notes for DG…. performing manual switch without using Dataguard Broker.


>check the init files of both nodes
[PRIMARY]

log_archive_dest_2     service=test01dr valid_for=(online_logfiles,primary_role) db_unique_name=test01dr
fal_client             test01dr
fal_server             test01, test01dr

[STANDBY]

log_archive_dest_2     service=test01.bai.com valid_for=(online_logfiles,primary_role) db_unique_name=test01
fal_client             test01dr
fal_server             test01, test01dr

>CHECK FOR THE TEMPFILES OF BOTH STANDBY AND PRIMARY

select ts.name as "Tablespace", tf.name as "Tempfile", tf.status as "Status" from v$tablespace ts join v$tempfile tf using (ts#);

[PRIMARY]

Tablespace               Tempfile                       Status
------------------------------ -------------------------------------------------- -------
TEMP                   /dbTEST01/u04/oradata/TEST01/temp01.dbf          ONLINE

[STANDBY]
Tablespace               Tempfile                       Status
------------------------------ -------------------------------------------------- -------
TEMP                   /dbTEST01/u04/oradata/TEST01DR/temp01.dbf      ONLINE

> Verify redo is current in primay.

SQL> alter system switch logfile;

System altered.

SQL> select status, error from v$archive_dest where dest_id = 2;

STATUS      ERROR
--------- -----------------------------------------------------------------
VALID

SQL> select client_process, process, sequence#, status from v$managed_standby;

CLIENT_P PROCESS    SEQUENCE# STATUS
-------- --------- ---------- ------------
ARCH     ARCH           56 CLOSING
ARCH     ARCH           57 CLOSING
ARCH     ARCH           58 CLOSING
ARCH     ARCH           59 CLOSING
N/A     MRP0           60 WAIT_FOR_LOG
UNKNOWN  RFS            0 IDLE
UNKNOWN  RFS            0 IDLE
UNKNOWN  RFS            0 IDLE

> Verify standby redo logfile on primary

SQL> select group#, status, type, count(*) as "Members" from v$logfile group by group#, status, type order by group#;

GROUP# STATUS  TYPE       Members
---------- ------- ------- ----------
1       ONLINE        1
2       ONLINE        1
3       ONLINE        1
4       STANDBY        1
5       STANDBY        1
6       STANDBY        1
7       STANDBY        1

7 rows selected.

>check/cancel all running backup on primary

SQL> select s.process, r.operation, r.status, r.mbytes_processed as "PCT", s.status
2  from v$rman_status r join v$session s using (sid);

no rows selected

>perform switchover operation with the physical standby

[PRIMARY]

SQL> select switchover_status from v$database;

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

If SWITCHOVER_STATUS returns a value of TO STANDBY, then everything is good and a switchover can occur. If on the other hand SWITCHOVER_STATUS returns SESSIONS ACTIVE, the SWITCHOVER command (below) will need to be issued with the "WITH SESSION SHUTDOWN" clause.

>Convert the primary database into a physical standby database.

SQL> alter database commit to switchover to physical standby;

Database altered.

As noted in the previous step, all sessions to the primary database need to be disconnected. If the SWITCHOVER_STATUS column of V$DATABASE indicated SESSIONS ACTIVE, issue the SWITCHOVER command with the "WITH SESSION SHUTDOWN" clause.
SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered

>Shut down and restart the former primary as a new standby database.

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size            2019320 bytes
Variable Size           83886088 bytes
Database Buffers       79691776 bytes
Redo Buffers            2174976 bytes
Database mounted.
SQL>
check alert log.
Successful mount of redo thread 1, with mount id 3570225048
Sun Sep 25 20:11:09 2011
Physical Standby Database mounted.
Completed: ALTER DATABASE   MOUNT
ARC3 started with pid=19, OS id=4488

[ON STANDBY]

SQL>  select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

>Convert the former standby to a primary database.
SQL> alter database commit to switchover to primary;

Database altered.

Or, if SWITCHOVER_STATUS returned SESSIONS ACTIVE in the previous step, use:
SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

>To complete the transition of the standby database to the primary role, the new primary database needs to be shut down and restarted.

SQL> SHUTDOWN IMMEDIATE;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size            2019320 bytes
Variable Size           75497480 bytes
Database Buffers       88080384 bytes
Redo Buffers            2174976 bytes
Database mounted.
Database opened.
SQL>

As a final step, issue either of the following statements from the new physical standby database to begin managed recovery operations.

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

Database altered.

SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL      DATABASE_ROLE
-------------------- -------------------- ----------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PHYSICAL STANDBY

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL      DATABASE_ROLE
-------------------- -------------------- ----------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PRIMARY

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

DONE

bai

Advertisements

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