Data Guard物理备库的主备切换
1.查看状态
主库:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
------------------------------------------------------------
TO STANDBY //可以转换成备库
备库:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
------------------------------------------------------------
NOT ALLOWED
2.主切备
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
数据库已更改。
SQL> startup nomount;
ORACLE 例程已经启动。
Total System Global Area 751595520 bytes
Fixed Size 2292912 bytes
Variable Size 557843280 bytes
Database Buffers 188743680 bytes
Redo Buffers 2715648 bytes
SQL> alter database mount standby database;
数据库已更改。
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
数据库已更改。
3.备切主
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
------------------------------------------------------------
TO PRIMARY
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
数据库已更改。
SQL> select status from v$instance;
STATUS
------------------------------------
MOUNTED
SQL> alter database open;
数据库已更改。
4.查看备库(切换后的备库)
SQL> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';
CLIENT_PROCESS PROCESS THREAD# SEQUENCE# STATUS
------------------------ --------------------------- ---------- ---------- ------------------------------------
N/A MRP0 1 39 APPLYING_LOG
LGWR RFS 1 39 IDLE
SQL> select status from v$instance;
STATUS
------------------------------------
MOUNTED
5.验证
主库(切换完成后的)
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
41
SQL> alter system switch logfile;
系统已更改。
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
42
备库(切换后的)
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
42
问题汇总:
switchover_status(SELECT SWITCHOVER_STATUS FROM V$DATABASE;的返回)值为:session active/not allowed
切换语句用Alter database commit to switchover to physical standby with session shutdown;等同于ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY; 语句执行完成后,主库实例自动duown掉。
ORA- 01153:
alter database recover managed standby database finish; /Alter database recover managed standby database finish force; (要么前句,要么后一句)
alter database recover managed standby database disconnect from session; (用此命令)
ORA-16139:
alter database recover managed standby database disconnect from session;
#####################
迷途小运维原创
作者:john
转载请注明出处