Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Graceful Switchover and Switchback for Oracle Standby Database 10g

manasMar 27 2012 — edited Apr 3 2012
Dear All,

How I Graceful Switchover and Switchback for Oracle Standby Database 10g ( production & standby).

Please suggest or share any document where step by step mention.


Thanks,
Manas
This post has been answered by Shivananda Rao on Apr 3 2012
Jump to Answer

Comments

Shivananda Rao
Please Refer this

http://baioradba.wordpress.com/2011/09/27/switchover-on-physical-standby-10-2-0-1-manual/
http://www.oracledistilled.com/oracle-database/data-guard-switchover-to-a-physical-standby/
http://docs.oracle.com/cd/B19306_01/server.102/b14230/sofo.htm
manas

Handle: manas
Email: oraclemanas@gmail.com
Status Level: Newbie
Registered: Jan 24, 2012
Total Posts: 12
Total Questions: 4 (4 unresolved)
Name Manas
Location kolkata
Please mark your questions as answered rather than having a heap of unanswered questions. Keep the forum clean.
Nikolay Ivankin
refer to this doc if you use Standard Edition:
http://www.oracle-base.com/articles/9i/DataGuard.php#DatabaseSwitchover

and use dgmgrl>switchover to <standby db> if you use Enterprise.

Edited by: Nikolay Ivankin on 27.03.2012 8:32
CKPT
Manas,

Refer switchover/failover best practices PDF from oracle MAA.

http://www.oracle.com/technetwork/database/features/availability/maa-wp-10gr2-switchoverfailoverbest-128455.pdf

Consider closing your threads and keep the forum clean.

Thanks.
manas
Hi Shivananda Rao,

when i executed the following command in primary database it show the error. please suggest.



SQL> alter database commit to switchover to physical standby with session shutdown;
alter database commit to switchover to physical standby with session shutdown
*
ERROR at line 1:
ORA-16014: log 1 sequence# 23 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/oracle/GWP/oraarch/GWP/redo01.log'


my switchover status shows below:

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE


Thanks,
Manas
CKPT
Perform couple of log switched, check standby is in SYNC with primary or not.
Once it's sync , then follow steps.

Read the mentioned links properly
manas
Hi CKPT,

Log switch happen successfully. I check it using command "alter system switch logfile".

standby is in SYNC with primary : yes , I apply the log manually no data guard is running.

Thanks,
Manas
CKPT
Log switch happen successfully. I check it using command "alter system switch logfile".

standby is in SYNC with primary : yes , I apply the log manually no data guard is running.
So there is problem in entire dataguard configuration,
Use this below snapper script, and post output here from both primary & standby.

Here is my link.

http://www.oracle-ckpt.com/dataguard_troubleshoot_snapper/
Shivananda Rao
SQL> alter database commit to switchover to physical standby with session shutdown;
alter database commit to switchover to physical standby with session shutdown
*
ERROR at line 1:
ORA-16014: log 1 sequence# 23 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/oracle/GWP/oraarch/GWP/redo01.log'
Check if there is disk space available on the primary to create archivelogs. Check the primary alert log file.

Here is one thread which is with the similar issue. Refer 999891
manas
Hi CKPT,
Here not running any dataguard or not configure any dataguard.

Still same problem happen.


SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
*
ERROR at line 1:
ORA-16014: log 3 sequence# 4 not archived, no available destinations
ORA-00312: online log 3 thread 1: '/oracle/GWP/sapdata/GWP/redo03.log'


Thanks
Manas
manas
Hi Shivananda Rao,

I Check disk space available on the primary to create archivelogs. alter file also show same error.

I follow the thread which is with the similar issue. but still not get any result.

Thanks
Manas
Nikolay Ivankin
Check if FRA has free space?
SELECT NAME,
TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,
TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE, '999,999,999,999') AS SPACE_AVAILABLE,
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;
Nikolay Ivankin
double post

Edited by: Nikolay Ivankin on 29.03.2012 9:36
Nikolay Ivankin
triple post :-)

Edited by: Nikolay Ivankin on 29.03.2012 9:36
Nikolay Ivankin
manas wrote:
I Check disk space available on the primary to create archivelogs. alter file also show same error.
Check FRA also at standby.
manas
Please find FRA output still same error.


SQL> SELECT NAME,
TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,
TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE, '999,999,999,999') AS SPACE_AVAILABLE,
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;
2 3 4 5
NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_AVAILABLE PERCENT_FULL
---------------- ---------------- ------------
/oracle/flash_recovery_area
2,147,483,648 2,147,483,648 0


SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY
*
ERROR at line 1:
ORA-16014: log 2 sequence# 6 not archived, no available destinations
ORA-00312: online log 2 thread 1: '/oracle/GWP/sapdata/GWP/redo02.log'


Thanks,
Manas
Shivananda Rao 
 

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY
*
ERROR at line 1:
ORA-16014: log 2 sequence# 6 not archived, no available destinations
ORA-00312: online log 2 thread 1: '/oracle/GWP/sapdata/GWP/redo02.log'
Please perform a couple of log switches on the primary and check for the error in the alert log file.

Also, please post from the primary:
show parameter log_archive_dest_
manas
when switch the log file no error is comming.

Please find the details of parameter:

SQL> show parameter log_archive_dest_

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=/oracle/GWP/oraarch
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable


Thanks,
Manas
Shivananda Rao
Answer
Hi,
log_archive_dest_1 string LOCATION=/oracle/GWP/oraarch
log_archive_dest_2 string
If you could notice, only dest_1 is set up in your primary database, what about the dest_2 ? It is not set. I hope the values you posted are from the primary database. Please set it as below

Primary DB:
log_archive_dest_1='location=/oracle/GWP/oraarch
valid_for=(all_logfiles,all_roles)
db_unique_name=<db_unique_name of Primary database>'
log_archive_dest_2='service=<net oracle service name of standby database>
valid_for=(online_logfiles,primary_role)
db_unique_name=<db_unique_name of Standby Database>'
Check for the same parameters on the standby database. If they are not set accordingly, then set it as below. I believe they are set up, but check it out.

Standby DB:
log_archive_dest_1='location=<location of the archives on the standby database>
valid_for=(all_logfiles,all_roles)
db_unique_name=<db_unique_name of Standby database>'
log_archive_dest_2='service=<net oracle service name of the primary database>
valid_for=(online_logfiles,primary_role)
db_unique_name=<db_unique_name of Primary Database>'
Also check out for the parameters FAL_SERVER, FAL_CLIENT on the standby database, so that you do not face issues later.
FAL_SERVER=<net oracle service name of the Primary Database>
FAL_CLIENT=<net oracle service name of the Standby database>
Marked as Answer by manas · Sep 27 2020
manas
Hi Shivananda Rao,

Thanks a lot ....

Problem now resolved.

Thanks,
Manas
1 - 19
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 1 2012
Added on Mar 27 2012
#data-guard, #performance-availability
19 comments
2,657 views