此篇操作承接上文,必须完成DG实例搭建完成方可有执行以下内容的实例
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 切换主库为备库
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>
创建备日志
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 by sequence#; 2 3 SEQUENCE# APPLIED 4 ---------- --------- 5 6 YES 6 6 YES 7 7 YES 8 7 YES 9 8 YES 10 8 YES 11 9 YES 12 9 YES 13 10 YES 14 10 YES 15 11 YES 16 17 SEQUENCE# APPLIED 18 ---------- --------- 19 11 YES 20 12 YES 21 12 YES 22 13 YES 23 13 YES 24 14 YES 25 14 YES 26 15 YES 27 15 YES 28 16 YES 29 16 YES 30 31 SEQUENCE# APPLIED 32 ---------- --------- 33 17 YES 34 17 YES 35 18 YES 36 19 YES 37 20 YES 38 21 YES 39 40 28 rows selected.
SQL> insert into dg values(2); 1 row created. SQL> commit ; Commit complete.
SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database open; Database altered. SQL> select * from dg; ID ---------- 1 2 SQL>
切换成功
状态查看:
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
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>
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 by sequence#; 2 3 SEQUENCE# APPLIED 4 ---------- --------- 5 6 YES 6 6 YES 7 7 YES 8 7 YES 9 8 YES 10 8 YES 11 9 YES 12 9 YES 13 10 YES 14 10 YES 15 11 YES 16 17 SEQUENCE# APPLIED 18 ---------- --------- 19 11 YES 20 12 YES 21 12 YES 22 13 YES 23 13 YES 24 14 YES 25 14 YES 26 15 YES 27 15 YES 28 16 YES 29 16 YES 30 31 SEQUENCE# APPLIED 32 ---------- --------- 33 17 YES 34 17 YES 35 18 YES 36 19 YES 37 20 YES 38 21 YES 39 22 YES 40 41 29 rows selected. 42 43 SQL>
select switchover_status from v$database;
alter database commit to switchover to primary;
select status from v$instance;
原文：https://www.cnblogs.com/kingle-study/p/10412511.html
