dg oracle 切换模式_oracle 单实例DG(切换篇三)

一,开篇

此篇操作承接上文,必须完成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#;

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

3 SEQUENCE# APPLIED4 ---------- ---------

5 6YES6 6YES7 7YES8 7YES9 8YES10 8YES11 9YES12 9YES13 10YES14 10YES15 11YES16

17 SEQUENCE# APPLIED18 ---------- ---------

19 11YES20 12YES21 12YES22 13YES23 13YES24 14YES25 14YES26 15YES27 15YES28 16YES29 16YES30

31 SEQUENCE# APPLIED32 ---------- ---------

33 17YES34 17YES35 18YES36 19YES37 20YES38 21YES39

40 28 rows selected.

View Code

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#;

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

3 SEQUENCE# APPLIED4 ---------- ---------

5 6YES6 6YES7 7YES8 7YES9 8YES10 8YES11 9YES12 9YES13 10YES14 10YES15 11YES16

17 SEQUENCE# APPLIED18 ---------- ---------

19 11YES20 12YES21 12YES22 13YES23 13YES24 14YES25 14YES26 15YES27 15YES28 16YES29 16YES30

31 SEQUENCE# APPLIED32 ---------- ---------

33 17YES34 17YES35 18YES36 19YES37 20YES38 21YES39 22YES40

41 29rows selected.42

43 SQL>

View Code

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>