|
This is a classic Oracle 10g two-node Physical standby Data Guard setup:
Primary database name: proddb01
Standby database name: stby01
STEP 1: Verify that it is possible to fail over.
On primary database (proddb01):
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- SESSIONS ACTIVE
A value of TO STANDBY or SESSIONS ACTIVE indicates that the primary database is ready to be switched to the standby role.
In my case, the output was SESSIONS ACTIVE. In this case, check if this is because of a) active SQL sessions or b) active user sessions.
The section "Problems switching over to a standby database, section A.4.2 and A.4.3" gives good examples on how to clean up your primary before attempting to switch over.
It seems like you can still successfully perform a switchover by appending the WITH SESSION SHUTDOWN clause to the ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY statement, and that is exactly what I did:
STEP 2: Instruct the primary database to take the role as the standby database.
On the primary (proddb01):
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
From primary (proddb01) alert log:
idle dispatcher 'D000' terminated, pid = (16, 1) Tue Oct 28 19:13:39 2014 Switchover: Complete - Database shutdown required (proddb01) Tue Oct 28 19:13:39 2014 Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
From the standby (stby01) alert log:
-- Connected User is Valid RFS[40]: Assigned to RFS process 7078566 RFS[40]: Identified database type as 'physical standby' RFS[40]: Successfully opened standby log 7: '/u02/oradata/stby01/stb_redo7.log Tue Oct 28 19:10:57 2014 Media Recovery Log /u04/oradata/stby01/archive/log1_75389_681750063.arc Media Recovery Waiting for thread 1 sequence 75390 Tue Oct 28 19:13:31 2014 Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[41]: Assigned to RFS process 34669144 RFS[41]: Identified database type as 'physical standby' RFS[41]: Archived Log: '/u04/oradata/stby01/archive/log1_75390_681750063.arc' Tue Oct 28 19:13:32 2014 Media Recovery Log /u04/oradata/stby01/archive/log1_75390_681750063.arc Identified End-Of-Redo for thread 1 sequence 75390 Tue Oct 28 19:13:32 2014 Media Recovery End-Of-Redo indicator encountered Tue Oct 28 19:13:32 2014 Media Recovery Applied until change 142570810888 Tue Oct 28 19:13:32 2014 MRP0: Media Recovery Complete: End-Of-REDO (stby01) Resetting standby activation ID 1529144623 (0x5b24e52f) Tue Oct 28 19:13:35 2014 MRP0: Background Media Recovery process shutdown (stby01)
Step 3: shutdown the database and start it in mounted mode.
On primary (proddb01):
shutdown abort startup mountFrom the primary (proddb01) alert log:
Tue Oct 28 19:15:37 2014
Successful mount of redo thread 1, with mount id 1709512659
Tue Oct 28 19:15:37 2014
Physical Standby Database mounted.
Starting control autobackup
Control autobackup written to DISK device
handle '/u01/oracle/product/ora10g/dbs/c-1529124399-20141028-12'
Completed: ALTER DATABASE MOUNT
Step 4: verify the current status.
On the standby (stby01):
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO PRIMARY
A value of TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role. If neither of these values is returned, verify that Redo Apply is active and that redo transport is configured and working properly. Continue to query this column until the value returned is either TO PRIMARY or SESSIONS ACTIVE.
Step 5: Instruct the standby database to take on the role as a primary database.
On the standby (stby01):
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;From the standby (stby01) alert log:
-- Connected User is Valid RFS[41]: Assigned to RFS process 34669144 RFS[41]: Identified database type as 'physical standby' RFS[41]: Archived Log: '/u04/oradata/stby01/archive/log1_75390_681750063.arc' Tue Oct 28 19:13:32 2014 Media Recovery Log /u04/oradata/stby01/archive/log1_75390_681750063.arc Identified End-Of-Redo for thread 1 sequence 75390 Tue Oct 28 19:13:32 2014 Media Recovery End-Of-Redo indicator encountered Tue Oct 28 19:13:32 2014 Media Recovery Applied until change 142570810888 Tue Oct 28 19:13:32 2014 MRP0: Media Recovery Complete: End-Of-REDO (stby01) Resetting standby activation ID 1529144623 (0x5b24e52f) Tue Oct 28 19:13:35 2014 MRP0: Background Media Recovery process shutdown (stby01) Tue Oct 28 19:18:43 2014 ALTER DATABASE SWITCHOVER TO PRIMARY (stby01) Tue Oct 28 19:18:43 2014 If media recovery active, switchover will wait 900 seconds SwitchOver after complete recovery through change 142570810888 Online log /u02/oradata/stby01/redo1.log: Thread 1 Group 4 was previously cleared Online log /u02/oradata/stby01/redo2.log: Thread 1 Group 5 was previously cleared Online log /u02/oradata/stby01/redo3.log: Thread 1 Group 6 was previously cleared Standby became primary SCN: 142570810886 Converting standby mount to primary mount. Tue Oct 28 19:18:43 2014 Switchover: Complete - Database mounted as primary (stby01) Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY Tue Oct 28 19:18:43 2014 ARC0: STARTING ARCH PROCESSES ARC2: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE ARC2 started with pid=15, OS id=8847906 Tue Oct 28 19:19:41 2014 ALTER DATABASE OPENThe primary database (proddb01) is from now on considering itself to be the physical standby database.
From the old primary (proddb01) database alert log:
Tue Oct 28 19:15:37 2014
Physical Standby Database mounted.
Starting control autobackup
Control autobackup written to DISK device
handle '/u01/oracle/product/ora10g/dbs/c-1529124399-20141028-12'
Completed: ALTER DATABASE MOUNT
Tue Oct 28 19:26:10 2014
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/oracle/admin/proddb01/archive/
Tue Oct 28 19:26:19 2014
Starting control autobackup
Control autobackup written to SBT_TAPE device
comment 'API Version 2.0,MMS Version 5.3.3.0',
media '1362'
handle 'c-1529124399-20141028-13'
Step 6: Verify that the former standby database now has the primary role.
On stby01:
SQL> SELECT DB_UNIQUE_NAME,NAME,DATABASE_ROLE FROM V$DATABASE; DB_UNIQUE_NAME NAME DATABASE_ROLE ------------------------------ -------------- ---------------- stby01 proddb01 PRIMARY
After switchover, Oracle complained about some temporary files:
ORA-01187: cannot read from file 201 because it failed verification tests
ORA-01110: data file 201: '/u02/oradata/stby01/temp01.dbf'
SQL>
1 select tf.file#,tf.ts#,tf.status,tf.enabled, tf.bytes/1024/1024,tf.name "file_name",ts.name "ts_name"
2 from v$tempfile tf, v$tablespace ts
3* where tf.ts# = ts.ts#
FILE# TS# STATUS ENABLED TF.BYTES/1024/1024 file_name ts_name
---------- ---------- ------- ---------- ------------------ -------------------------------------- --------------------
1 3 ONLINE READ WRITE 32767 /u02/oradata/stby01/temp01.dbf TEMP
2 8 ONLINE READ WRITE 100 /u02/oradata/stby01/EDPROD_iastemp.dbf EDPROD_IAS_TEMP
3 3 ONLINE READ WRITE 9900 /u02/oradata/stby01/temp02.dbf TEMP
SQL> save temp_tabspc_overview.sql
Created file temp_tabspc_overview.sql
SQL> drop tablespace EDPROD_IAS_TEMP including contents and datafiles;
Tablespace dropped.
SQL> @temp_tabspc_overview.sql
FILE# TS# STATUS ENABLED TF.BYTES/1024/1024 file_name ts_name
---------- ---------- ------- ---------- ------------------ ------------------------------ --------------------
1 3 ONLINE READ WRITE 32767 /u02/oradata/stby01/temp01.dbf TEMP
3 3 ONLINE READ WRITE 9900 /u02/oradata/stby01/temp02.dbf TEMP
SQL> create temporary tablespace EDPROD_IAS_TEMP tempfile '/u02/oradata/stby01/EDPROD_iastemp.dbf' size 128M reuse autoextend on next 32M maxsize unlimited
Tablespace created.
SQL> @temp_tabspc_overview.sql
FILE# TS# STATUS ENABLED TF.BYTES/1024/1024 file_name ts_name
---------- ---------- ------- ---------- ------------------ -------------------------------------- --------------------
1 3 ONLINE READ WRITE 32767 /u02/oradata/stby01/temp01.dbf TEMP
2 8 ONLINE READ WRITE 128 /u02/oradata/stby01/EDPROD_iastemp.dbf EDPROD_IAS_TEMP
3 3 ONLINE READ WRITE 9900 /u02/oradata/stby01/temp02.dbf TEMP
SQL> alter database default temporary tablespace EDPROD_IAS_TEMP;
Database altered.
SQL> drop tablespace TEMP including contents and datafiles;
Tablespace dropped.
SQL> create temporary tablespace TEMP tempfile '/u02/oradata/stby01/temp01.dbf' size 128M reuse autoextend on next 32M maxsize unlimited;
Tablespace created.
SQL> @temp_tabspc_overview.sql
FILE# TS# STATUS ENABLED TF.BYTES/1024/1024 file_name ts_name
---------- ---------- ------- ---------- ------------------ -------------------------------------- --------------------
1 3 ONLINE READ WRITE 128 /u02/oradata/stby01/temp01.dbf TEMP
2 8 ONLINE READ WRITE 128 /u02/oradata/stby01/EDPROD_iastemp.dbf EDPROD_IAS_TEMP
No comments:
Post a Comment