一,开篇

　　　此篇操作承接上文,必须完成DG实例搭建完成方可有执行以下内容的实例

二,切换物理备库

　　one. oracle01库命令

select switchover_status from v$database;

alter database commit to switchover to physical standby;

注意:　　上面 switchover_status 的值如果是 TO STANDBY，可以直接 switchover，如果是 sessions active，则需要在 switchover 的命令后面加上 with session shutdown，比如 alter database commit to switchover to physical standby with session shutdown;

select status from v$instance;--检查状态

shutdown immediate

startup nomount

切换主库为备库

　　　two.standy端　

select sequence#, applied from v$archived_log where applied='YES' order by sequence#;

SQL> select sequence#, applied from v$archived_log where applied='YES' order by sequence#;

 SEQUENCE# APPLIED

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

	 6 YES

	 7 YES

	 8 YES

	 9 YES

	10 YES

	11 YES

	12 YES

	13 YES

	14 YES

	15 YES

	16 YES

 SEQUENCE# APPLIED

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

	17 YES

	18 YES

	19 YES

14 rows selected.

日志同步

SQL> select switchover_status from v$database;



SWITCHOVER_STATUS

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

TO PRIMARY

SQL> alter database commit to switchover to primary;



Database altered.
备库切成主库

SQL> select status from v$instance;



STATUS

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

MOUNTED

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2505338880 bytes

Fixed Size		    2255832 bytes

Variable Size		  603980840 bytes

Database Buffers	 1879048192 bytes

Redo Buffers		   20054016 bytes

Database mounted.

Database opened.

SQL>

　　three.oracle01端

　　　　　　创建备日志

alter database add standby logfile ('/u01/app/oracle/oradata/oracle01/standby01.log') size 50m;

alter database add standby logfile('/u01/app/oracle/oradata/oracle01/standby02.log') size 50m;

alter database add standby logfile('/u01/app/oracle/oradata/oracle01/standby03.log') size 50m;

alter database add standby logfile('/u01/app/oracle/oradata/oracle01/standby04.log') size 50m;

　　　　执行同步语句:

alter database recover managed standby database using current logfile disconnect from session;

select sequence#, applied from v$archived_log where applied='YES'order by sequence#;

 SQL> select sequence#, applied from v$archived_log where applied='YES'order by sequence#;

  SEQUENCE# APPLIED

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

      6 YES

      6 YES

      7 YES

      7 YES

      8 YES

      8 YES

      9 YES

      9 YES

     10 YES

     10 YES

     11 YES

  SEQUENCE# APPLIED

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

     11 YES

     12 YES

     12 YES

     13 YES

     13 YES

     14 YES

     14 YES

     15 YES

     15 YES

     16 YES

     16 YES

  SEQUENCE# APPLIED

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

     17 YES

     17 YES

     18 YES

     19 YES

     20 YES

     21 YES

 28 rows selected.

　　four.standby执行创建数据库字段　　　

SQL> insert into dg values(2);

1 row created.

SQL> commit ;

　　

Commit complete.

　　five.oracle01执行　　　

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select * from dg;

	ID

----------

	 1

	 2

SQL>

　　　　　　切换成功

　　状态查看:

三,再次切oracle01为主库,standby为备库

　　　one.oracle01端执行　　　

SQL> select open_mode from v$database;



OPEN_MODE

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

READ ONLY

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

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ ONLY WITH APPLY

　　　two.standby端执行　　　

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO STANDBY

SQL> alter database commit to switchover to physical standby;

Database altered.

SQL> shutdown immediate

ORA-01012: not logged on

SQL> startup nomount

ORACLE instance started.

Total System Global Area 2505338880 bytes

Fixed Size		    2255832 bytes

Variable Size		  603980840 bytes

Database Buffers	 1879048192 bytes

Redo Buffers		   20054016 bytes

SQL> alter database mount standby database;

Database altered.

SQL>

　　　three.oracle01端执行

select sequence#, applied from v$archived_log where applied='YES' order by sequence#;

 SQL> select sequence#, applied from v$archived_log where applied='YES' order by sequence#;

  SEQUENCE# APPLIED

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

      6 YES

      6 YES

      7 YES

      7 YES

      8 YES

      8 YES

      9 YES

      9 YES

     10 YES

     10 YES

     11 YES

  SEQUENCE# APPLIED

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

     11 YES

     12 YES

     12 YES

     13 YES

     13 YES

     14 YES

     14 YES

     15 YES

     15 YES

     16 YES

     16 YES

  SEQUENCE# APPLIED

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

     17 YES

     17 YES

     18 YES

     19 YES

     20 YES

     21 YES

     22 YES

 29 rows selected.

 SQL>

select switchover_status from v$database;

alter database commit to switchover to primary;

select status from v$instance;

shutdown immediate

startup

　　　four.standy端执行　　　

alter database recover managed standby database using current logfile disconnect from session;

　　　 five.oracle01端.添加数据测试　

SQL> insert into dg values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

　　　six.standby 数据查看

SQL>

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

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select * from dg;

	ID

----------

	 1

	 2

	 3

SQL>

巴特西

oracle 单实例DG(切换篇三)