Shulou(Shulou.com)05/31 Report--

Editor to share with you oracle 12c data guard how to use sqlplus active / standby switching, I believe most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's learn about it!

First, switch the previous check. Confirm that the standby library is ready to switch.

Make sure that the standby database is synchronized with the primary database and that the log switch is normal.

SQL > alter database switchover to verify

The above command validates the following information:

a. Verify that the version of the database is at least 12.1.

b. The REDO transfer of the main library is normal.

c. The slave MRP process runs normally and synchronizes with the master database, otherwise the following error will be reported in both SQLPROMPT and alert:

Such as:

SQL > alter database switchover to chicago verify

ORA-16470: Redo Apply is not running on switchover target

-Primary alert log-

SQL > alter database switchover to chicago verify

ORA-16470 signaled during: alter database switchover to chicago verify...

Check the status of ORL on the target repository

If it is dirty, the following information will be reported:

SQL > alter database switchover to chicago verify

ERROR at line 1:

ORA-16475: succeeded with warnings, check alert log for more details

-Primary Alert log-

SWITCHOVER VERIFY WARNING: switchover target has dirty online redo logfiles that require clearing.

It takes time to clear online redo logfiles. This may slow down switchover process.

What needs to be done?

Verify that the log_file_name_convert parameter is set correctly in the target slave database:

SQL > show parameter log_file_name_convert

Note: if it is not set, please set it. For example, if only db_uniquq_name has changed the path structure:

The log of the main library is at: / oradata/boston/

The log of the repository is at: / oradata/chicago/logfiles >

Set the parameters like this:

Alter system set LOG_FILE_NAME_CONVERT='boston','chicago' scope=spfile

Note: restart the slave library and start MRP. When the standby library is restarted and MRP is started, the logs set by the log_file_name_convert parameter of all standby libraries will be cleared.

If the standby library is synchronized with the main library, you will get the following information:

SQL > alter database switchover to chicago verify

Database altered.

-Primary Alert log-

SWITCHOVER VERIFY: Send VERIFY request to switchover target CHICAGO

SWITCHOVER VERIFY COMPLETE

Completed: alter database switchover to chicago verify

Confirm that the temporary data files of the main database and the standby database match

After the temporary data file is created, the temporary data file will not be created at the same time. Query the temporary data file with the following command, and create it in the standby database.

SQL > col name for A45

SQL > select ts#,name,ts#,status from v$tempfile

Note: for environments with multiple backup libraries, make sure that each standby library is synchronized with the main library. Check if log_archive_Dest (remote redo transport) set on Standby side

SWITCHOVER VERIFY command returns ORA-16475 when log_archive_dest_n is not set on Standby as follows.

SQL > ALTER DATABASE SWITCHOVER TO S1202 VERIFY

ALTER DATABASE SWITCHOVER TO S1202 VERIFY

*

ERROR at line 1:

ORA-16475: succeeded with warnings, check alert log for more details

In alert.log, following messages appear.

SWITCHOVER VERIFY: Send VERIFY request to switchover target S1202

SWITCHOVER VERIFY COMPLETE

SWITCHOVER VERIFY WARNING: switchover target has no standby database definedin LOG_ARCHIVE_DEST_n parameter. If the switchover target is converted to

A primary database, the new primary database will not be protected.ORA-16475 signalled during: ALTER DATABASE SWITCHOVER TO S1202 VERIFY...

What to do? Set log_archive_dest_state_n on Standby.

If the value of switchover_status of v$database is UNRESOLVABLE GAP (RAC or non-RAC)

1. Check to see if some closed threads exist and disable

SQL > SELECT thread#, instance, status FROM v$thread

Disable threads use:

SQL > ALTER DATABASE DISABLE THREAD

two。 Check if there is a log_archive_destination pointing to an illegal directory

SQL > select status,DEST_ID,TYPE,ERROR,GAP_STATUS,SYNCHRONIZED,SYNCHRONIZATION_STATUS,RECOVERY_MODE from V$ARCHIVE_DEST_STATUS where STatus' INACTIVE'

SQL > show parameter log_Archive_dest

Second, switch roles to turn on trace in both the main database and the standby database, which can be used to diagnose problems.

SQL > alter system set log_archive_trace=8191 sid='*'

Monitor the alert log of each instance (optional)

@ primary and standby

SQL > show parameter background_dump_dest

$tail-600f background_dump_dest/alert*

* Note: there is no need to shut down other instances in the RAC environment. Executing the command of Switchover will shut down all instances.

Switch the standby role to the primary library

Main Library-Boston

SQL > alter database switchover to chicago

Database altered.

The following is the alert output of the main library (BOSTON) and standby library (CHICAGO):

-Primary Alert log "alert_boston.log"

Fri Aug 23 11:05:23 2013

ALTER SYSTEM SET log_archive_trace=8191 SCOPE=BOTH

Alter database switchover to chicago

Fri Aug 23 11:05:43 2013

Starting switchover [Process ID: 3340]

Fri Aug 23 11:05:43 2013

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 3340] (boston)

.

Fri Aug 23 11:05:44 2013

Active, synchronized Physical Standby switchover target has been identified

Preventing updates and queries at the Primary

Generating and shipping final logs to target standby

Switchover End-Of-Redo Log thread 1 sequence 11 has been fixed alter database open

Restart a new standby library

SQL > shut abort

SQL > startup

SQL > alter database recover managed standby database disconnect

Third, close trace in the next step after switching.

SQL > alter system set log_archive_trace=0

System altered.

Confirm that the new master database has normally transferred logs to the standby database, and the standby database can be applied normally.

Execute on the side of the main library

SQL > alter system switch logfile

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

SQL > select max (sequence#), thread# from v$log_history group by thread#

If the standby end corresponds to log_archive_dest_2

SQL > select max (sequence#) from v$archived_log where applied='YES' and

Dest_id=2

On the repository side:

SQL > select thread#,sequence#,process,status from gv$managed_standby

SQL > select max (sequence#), thread# from v$archived_log group by thread#

Note: use v$dataguard_process instead of v$managed_standby in 12.2

SQL > select name,role,instance,thread#,sequence#,action from gv$dataguard_process

The above is all the contents of the article "oracle 12c data guard how to use sqlplus active / standby switching". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.