Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Manually Switchback the OAT Database from primary001 to standby001

Posted by Hendry chinnapparaj on December 20, 2010

 

Hendry Dasan

Monday, 20 December 2010

 

 

 

Manually Switchback the OAT Database from primary001 to standby001

 

The current status is

Standby001 – standby

Primary001 – primary

 

 

 

 

PRIMARY

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

 

SWITCHOVER_STATUS

——————–

SESSIONS ACTIVE

 

 

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

 

Database altered.

 

 

SQL> shutdown immediate

ORA-01507: database not mounted

 

 

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area 2801795072 bytes

Fixed Size                  2086680 bytes

Variable Size            1711278312 bytes

Database Buffers         1073741824 bytes

Redo Buffers               14688256 bytes

Database mounted.

 

 

SQL> show parameter job_queue_processes

 

NAME                                 TYPE

———————————— ——————————–

VALUE

——————————

job_queue_processes                  integer

0

 

 

STANDBY

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

 

SWITCHOVER_STATUS

——————–

SWITCHOVER LATENT

 

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

 

SWITCHOVER_STATUS

——————–

TO PRIMARY

 

 

 

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY ;

 

Database altered.

 

SQL> select open_mode from v$database;

 

OPEN_MODE

———-

MOUNTED

 

SQL> alter database open;

 

Database altered.

 

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

 

SWITCHOVER_STATUS

——————–

SESSIONS ACTIVE

 

 

 

SQL> show parameter job_queue_processes

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

job_queue_processes                  integer     10

 

 

 

STANDBY

SQL> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Media recovery complete.

 

PRIMARY

SQL> show parameter job_queue_processes

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

job_queue_processes                  integer     4

SQL>

SQL> select dest_id, status from v$archive_dest_status;

 

DEST_ID STATUS

———- ———

1 VALID

2 DEFERRED

3 INACTIVE

4 INACTIVE

5 INACTIVE

6 INACTIVE

7 INACTIVE

8 INACTIVE

9 INACTIVE

10 INACTIVE

 

10 rows selected.

 

SQL> alter system set log_archive_dest_state_2=enable scope=memory;

 

System altered.

 

SQL> select dest_id, status from v$archive_dest_status;

 

DEST_ID STATUS

———- ———

1 VALID

2 VALID

3 INACTIVE

4 INACTIVE

5 INACTIVE

6 INACTIVE

7 INACTIVE

8 INACTIVE

9 INACTIVE

10 INACTIVE

 

10 rows selected.

 

 

STANDBY

SQL> select STATUS from v$managed_standby;

 

STATUS

————

CONNECTED

CLOSING

WAIT_FOR_LOG

IDLE

IDLE

 

SQL> select STATUS from v$managed_standby;

 

STATUS

————

CONNECTED

CLOSING

APPLYING_LOG

IDLE

IDLE

 

 

PRIMARY

oracle@standby001 logs>cat replica.stby_check.20101220_1224.log

 

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Dec 20 12:24:23 2010

 

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

 

 

PERFORMING – DATAGUARD STATUS CHECK FOR LOG_GAP <= 2 AND SYNCHRONIZATION_STATUS = OK

 

 

INST_NAME HOST_NAME  PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED TIME_APPLIED   LOG_GAP

——— ———- ——————– ———————- ———— ———– ————– ——-

replica    standby001 MAXIMUM AVAILABILITY                     OK         1863        1863 20/12 12:20:37       0

 

 

DATAGUARD STATUS CHECK RETURNED NO ROWS – CHECK V$ARCHIVED_LOG FOR POSSIBLE CAUSE

 

 

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

oracle@standby001 logs>

 

 

 

QL> SHOW PARAMETER JOB_QUEUE

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

job_queue_processes                  integer     10

 

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

 
%d bloggers like this: