1、备库只读打开
备库现在是mount状态，不能查询数据。可以把它open read only;如果read only，备库就只能查询，并且不再应用日志。
备库操作：
SQL> recover managed standby database cancel; SQL> alter database open read only;
此时就可以查询数据，看主库最新的数据是否可以在备库查到。
如果需要继续应用日志，需要关闭数据库，启动到mount状态，重新应用日志。
2、主备切换
切换顺序，把主切换成从；再把从切换成主
主切换成从
SQL> select database_role,switchover_status from v$database; DATABASE_ROLE SWITCHOVER_STATUS ---------------- -------------------- PRIMARY SESSIONS ACTIVE SQL> alter database commit to switchover to physical standby with session shutdown; SQL> shutdown immediate SQL> startup nomount SQL> alter database mount standby database; SQL> recover managed standby database disconnect from session; SQL> select database_role,switchover_status from v$database; DATABASE_ROLE SWITCHOVER_STATUS ---------------- -------------------- PHYSICAL STANDBY SESSIONS ACTIVE
从切换成主
SQL> select database_role,switchover_status from v$database; DATABASE_ROLE SWITCHOVER_STATUS ---------------- -------------------- PHYSICAL STANDBY TO PRIMARY SQL> alter database commit to switchover to primary with session shutdown; SQL> shutdown immediate SQL> startup SQL> select database_role,switchover_status from v$database; DATABASE_ROLE SWITCHOVER_STATUS ---------------- -------------------- PRIMARY TO STANDBY
也可以按照上边的步骤重新切换回去。
3、逻辑standby
物理standby：通过redo apply来恢复进行同步
逻辑standby：通过sql apply来恢复进行同步
逻辑standby可以在open状态下完成数据同步，它的物理结构可以不一致
逻辑standby的实现：通过物理standby转换成逻辑standby
备库关闭日志应用
SQL> alter database recover managed standby database cancel;
生成记录的元数据信息到 redo log
主库运行
SQL> exec dbms_logstdby.build
备库操作
SQL> alter database recover to logical standby std; SQL> shutdown immediate SQL> startup mount
SQL> select member from v$logfile; SQL> alter database open resetlogs; SQL> alter database start logical standby apply immediate; SQL> select database_role from v$database; DATABASE_ROLE ---------------- LOGICAL STANDBY
查看逻辑standby的当前的归档日志应用情况
主库：
SQL> alter system switch logfile; System altered. SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) --------------
备库：
SQL> select sequence#,first_change#,next_change#,timestamp,applied from dba_logstdby_log; SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED ---------- ------------- ------------ -------------- ---------------- 49 463066 463602 26-3月 -16 CURRENT 50 463602 463613 26-3月 -16 CURRENT
4、逻辑standby切换
切换前的检查
切换前一定要检查下日志是否同步正常
相互sqlplus sys/sys@test as sysdba连接下，是否都能够连通。
主库准备转换为备库
SQL> alter database prepare to switchover to logical standby; Database altered. SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- PREPARING SWITCHOVER
注：取消准备ALTER DATABASE PREPARE TO SWITCHOVER CANCEL;
备库准备转换为主库
SQL> alter database prepare to switchover to primary; Database altered. SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- PREPARING SWITCHOVER
再次检查主库的状态
SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO LOGICAL STANDBY
转换主库为逻辑standby
SQL> alter database commit to switchover to logical standby; Database altered.
再次检查逻辑standby状态
SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO PRIMARY
转换逻辑standby为主库
SQL> alter database commit to switchover to primary; Database altered.
启用新逻辑standby的sql应用（别执行错库了）
SQL> alter database start logical standby apply; Database altered.
5、其他
停止逻辑standby应用
SQL> alter database stop logical standby apply; Database altered.
standby同步问题排查
SQL> column EVENT_TIME format a10 SQL> column EVENT_TIMESTAMP format a20 SQL> column STATUS format a40 SQL> select EVENT_TIME,EVENT_TIMESTAMP,STATUS from dba_logstdby_events order by event_time desc; EVENT_TIME EVENT_TIMESTAMP STATUS ---------- -------------------- ---------------------------------------- 28-3月 -16 28-3月 -16 03.02.30. ORA-16111: log mining and apply setting 573734 下午 up 28-3月 -16 28-3月 -16 03.02.30. Apply LWM 540429, HWM 540429, SCN 540434 597606 下午 28-3月 -16 28-3月 -16 11.37.51. ORA-16128: User initiated stop apply suc 578547 上午 cessfully completed 28-3月 -16 28-3月 -16 11.35.58. ORA-16226: DDL skipped due to lack of su 963554 上午 pport EVENT_TIME EVENT_TIMESTAMP STATUS ---------- -------------------- ---------------------------------------- 28-3月 -16 28-3月 -16 11.33.30. ORA-16111: log mining and apply setting 624892 上午 up 26-3月 -16 26-3月 -16 05.19.42. ORA-16128: User initiated stop apply suc 056570 下午 cessfully completed 26-3月 -16 26-3月 -16 02.34.53. ORA-16111: log mining and apply setting 643625 下午 up 7 rows selected.
