（1）检查主数据库，是否支持转换

SQL> select switchover_status from v$database;


SWITCHOVER_STATUS

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

TO STANDBY

在switchover_stauts中如果不是to standy或者sessions active，则检查DG配置是否运行正常，如果


是session active，则需要确定并终止可能阻碍切换处理的活动或者sql会话，如果之后列还是显示


sessions active，可以通过添加with session shutdown来执行切换



（2）在主数据库上发起切换


SQL> alter database commit to switchover to physical standby;


Database altered.


警告日志文件如下

Stopping background process CJQ0

Wed Oct 16 17:03:17 2013

SMON: disabling tx recovery

Wed Oct 16 17:03:17 2013

Stopping background process QMNC

Waiting for dispatcher 'D000' to shutdown

All dispatchers and shared servers shutdown

Stopping background process FBDA

SMON: disabling cache recovery

Wed Oct 16 17:03:27 2013

Shutting down archive processes

Archiving is disabled

Wed Oct 16 17:03:27 2013

ARC1: Becoming the heartbeat ARCH

ARC1: Archiving disabled

ARCH shutting down

ARC1: Archival stopped

Wed Oct 16 17:03:27 2013

ARCH shutting down

ARC3: Archival stopped

Wed Oct 16 17:03:27 2013

ARCH shutting down

ARC2: Archival stopped

Wed Oct 16 17:03:28 2013

ARCH shutting down

ARC0: Archival stopped

Thread 1 closed at log sequence 686

Successful close of redo thread 1

Wed Oct 16 17:03:31 2013

idle dispatcher 'D000' terminated, pid = (16, 1)

ARCH: Noswitch archival of thread 1, sequence 686

ARCH: End-Of-Redo Branch archival of thread 1 sequence 686

ARCH: Archiving is disabled due to current logfile archival

Using STANDBY_ARCHIVE_DEST parameter default value as /u01/arch1/DGBJ

LOG_ARCHIVE_DEST_2 is a potential switchover target

Backup controlfile written to trace file


/u01/app/oracle/diag/rdbms/dgbj/DGBJ/trace/DGBJ_ora_28009.trc

Clearing standby activation ID 775996802 (0x2e40c582)

The primary database controlfile was created using the

'MAXLOGFILES 16' clause.

There is space for up to 13 standby redo logfiles

Use the following SQL commands on the standby database to create

standby redo logfiles that match the primary database:

ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;

Archivelog for thread 1 sequence 686 required for standby recovery

Wed Oct 16 17:03:40 2013

MRP0 started with pid=20, OS id=30611

MRP0: Background Managed Standby Recovery process started (DGBJ)

Fast Parallel Media Recovery NOT enabled

Managed Standby Recovery not using Real Time Apply

parallel recovery started with 2 processes

Online logfile pre-clearing operation disabled by switchover

Media Recovery Log /u01/arch1/DGBJ/log1_686_826058126.arc

Identified End-Of-Redo for thread 1 sequence 686

Resetting standby activation ID 775996802 (0x2e40c582)

Media Recovery End-Of-Redo indicator encountered

Media Recovery Applied until change 10410462

MRP0: Media Recovery Complete: End-Of-REDO (DGBJ)

MRP0: Background Media Recovery process shutdown (DGBJ)

Wed Oct 16 17:03:57 2013

Switchover: Complete - Database shutdown required (DGBJ)

Completed: alter database commit to switchover to physical standby


（3）重新启动主实例到mount

SQL> shutdown immediate;

ORA-01507: database not mounted



ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.


Total System Global Area 318046208 bytes

Fixed Size 1299652 bytes

Variable Size 289409852 bytes

Database Buffers 20971520 bytes

Redo Buffers 6365184 bytes

Database mounted.

SQL>


（4）在备用数据库检查是否支持转换到primary

SQL> select switchover_status from v$database;


SWITCHOVER_STATUS

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

TO PRIMARY


返回值是to promary 或者 sessions active，这表示备用数据库已经准备好切换到主角色，


（5）转换物理standby 到primary



SQL> alter database commit to switchover to primary;


Database altered.


警告日志

Backup controlfile written to trace file


/u01/app/oracle/diag/rdbms/dgwh/DGWH/trace/DGWH_ora_712.trc

SwitchOver after complete recovery through change 10410462

Online log /u01/app/oracle/oradata/DGWH/onlinelog/o1_mf_1_96bsf1w0_.log: Thread 1 Group 1


was previously cleared

Online log /u01/app/oracle/flash_recovery_area/DGWH/onlinelog/o1_mf_1_96bsftsy_.log: Thread


1 Group 1 was previously cleared

Online log /u01/app/oracle/oradata/DGWH/onlinelog/o1_mf_2_96bj1jvq_.log: Thread 1 Group 2


was previously cleared

Online log /u01/app/oracle/flash_recovery_area/DGWH/onlinelog/o1_mf_2_96bj25wh_.log: Thread


1 Group 2 was previously cleared

Online log /u01/app/oracle/oradata/DGWH/onlinelog/o1_mf_3_96bj387h_.log: Thread 1 Group 3


was previously cleared

Online log /u01/app/oracle/flash_recovery_area/DGWH/onlinelog/o1_mf_3_96bj4px8_.log: Thread


1 Group 3 was previously cleared

Standby became primary SCN: 10410460

Converting standby mount to primary mount.

Switchover: Complete - Database mounted as primary (DGWH)

Tue Oct 22 17:33:37 2013

ARC0: STARTING ARCH PROCESSES

Completed: alter database commit to switchover to primary


注意：转换前如果是最大可用或者最大保护模式，需要在standby上先创建standby redo log

alter database add standby logfile ('/u01/app/oracle/oradata/DGWH/onlinelog/standby01.log')


size 50m;

.....

另外需要检查参数standy log service属性是否正确

SQL> show parameter log_archive_dest_2;


NAME TYPE VALUE

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

log_archive_dest_2 string SERVICE=DGBJ LGWR SYNC AFFIRM

VALID_FOR=(ONLINE_LOGFILES,PRI

MARY_ROLE) DB_UNIQUE_NAME=DGBJ


（5）打开新的primary数据库


需要先关闭，然后重启

SQL> shutdown immediate;

ORA-01109: database not open



Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.


Total System Global Area 318046208 bytes

Fixed Size 1299652 bytes

Variable Size 301992764 bytes

Database Buffers 8388608 bytes

Redo Buffers 6365184 bytes

Database mounted.


（6）转换后的检查


-- 主库和备库角色

SQL> select database_role,protection_mode from v$database;


DATABASE_ROLE PROTECTION_MODE

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

PRIMARY MAXIMUM AVAILABILITY



SQL> select database_role,protection_mode from v$database;


DATABASE_ROLE PROTECTION_MODE

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

PHYSICAL STANDBY MAXIMUM AVAILABILITY


-- 日志序列是否同步

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/arch1/DGWH

Oldest online log sequence 687

Next log sequence to archive 688

Current log sequence 688


SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/arch1/DGBJ

Oldest online log sequence 685

Next log sequence to archive 0

Current log sequence 688



--在转换后的备库启用redo apply


SQL> alter database open;


Database altered.


SQL> alter database recover managed standby database using current logfile disconnect from


session;


Database altered.


-- 在主库创建表test，检查是否正常同步

SQL> create table test as select rownum id from dual connect by rownum < 5;


Table created.


SQL> alter system switch logfile;


System altered.


备库：

select * from test

*

ERROR at line 1:

ORA-00942: table or view does not exist


SQL> /

select * from test

*

ERROR at line 1:

ORA-00942: table or view does not exist



SQL> /


ID

----------

1

2

3

4