切换的过程和步骤如下：基本按照oracle官方文档操作就可以了：

[oracle@mydg1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Jun 12 14:50:38 2007

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning and Data Mining options

SQL>select instace_name from v$instance;

instance_name

---------------

mydg

---old primary database,new logical standby database
Step 1 ：Verify it is possible to perform. a switchover on the primary database.

Primary> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

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

确定是“TO STANDBY”再执行下面的步骤

Step 2： Prepare the current primary database for the switchover.

Primary> ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;

Database altered.

Primary> SELECT SWITCHOVER_STATUS FROM V$DATABASE; /*上一个步骤的执行结果 */

SWITCHOVER_STATUS
--------------------
PREPARING SWITCHOVER

确定是上面的结果再执行下面的步骤

Step 4 ：Ensure the current primary database is ready for the future primary database’s redo stream.

Primary> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

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

确定是上面的结果再执行下面的步骤

Step 5： Switch the primary database to the logical standby database role.

Primary> set timing on
Primary> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

Database altered.

Elapsed: 00:00:23.79

Step 8： Start SQL Apply on the new logical standby database.

Primary> alter database start logical standby apply immediate;

Database altered.

--old logical standby database , new primary

SQL>select instance_name from v$instance;

instacne_name

------------------

stdby

Step 3 ：Prepare the target logical standby database for the switchover.

Logical> ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;

Database altered.

Logical> set timing on
Logical> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
PREPARING SWITCHOVER

Elapsed: 00:00:00.03

Step 6 ：Ensure all available redo has been applied to the target logical standby
database that is about to become the new primary database.

Logical> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

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

Elapsed: 00:00:00.04

确定是上面的结果再执行下面的步骤

Step 7 ：Switch the target logical standby database to the primary database role.

Logical> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

Elapsed: 00:00:12.51

关键的参数:设置了如下的关键参数，就可以不用修改任何参数就可以执行切换，也就是说下面的参数设置支持多次的switchover

initstdby.ora --old logical standby database , new primary parameter file

DB_UNIQUE_NAME=stdby
LOG_ARCHIVE_CONFIG='DG_CONFIG=(mydg,stdby)'
LOG_ARCHIVE_DEST_1='location=/oracle/arch VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby'
LOG_ARCHIVE_DEST_2='SERVICE=mydg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mydg'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
DB_FILE_NAME_CONVERT='mydg','stdby'
LOG_FILE_NAME_CONVERT='mydg','stdby'
FAL_SERVER=mydg
FAL_CLIENT=stdby
standby_file_management=auto

# add for logical standby database
*.log_archive_dest_3='location=/oracle/arch2 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=stdby'

LOG_ARCHIVE_DEST_STATE_3=ENABLE
standby_archive_dest=/oracle/arch2

initmydg.ora --old primary standby database , new logical parameter file

LOG_ARCHIVE_CONFIG='DG_CONFIG=(mydg,stdby)'
LOG_ARCHIVE_DEST_1='location=/oracle/arch VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mydg'
LOG_ARCHIVE_DEST_2='SERVICE=stdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
#LOG_ARCHIVE_MAX_PROCESSES=10

#add for primary database standby role
DB_FILE_NAME_CONVERT='stdby','mydg'
LOG_FILE_NAME_CONVERT='stdby','mydg'
FAL_SERVER=stdby
FAL_CLIENT=mydg
standby_file_management=auto

#Primary Database:Logical standby Role initialization Parameters
LOG_ARCHIVE_DEST_3='LOCATION=/oracle/arch2 VALID_FOR=(standby_logfiles,standby_role)
LOG_ARCHIVE_DEST_STATE_3=ENABLE
STANDBY_ARCHIVE_DEST=/oracle/arch2

ITPub博客

oracle10g R2 logical standby switchover to primary

最新文章