oracle dg not allowed,ORACLE 19.3 (DG fail over)

fail over前创建担保还原点

#主库

[oracle@XAG191 ~]$ sqlplus / as sysdba

SQL> select database_role,protection_mode,open_mode from v$database;

DATABASE_ROLE PROTECTION_MODE OPEN_MODE

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

PRIMARY MAXIMUM PERFORMANCE READ WRITE

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

NO

SQL> create restore point guar_restore_01 guarantee flashback database;

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

RESTORE POINT ONLY

SQL> exit

检查备库状态

[oracle@XAG192 ~]$ sqlplus / as sysdba

SQL> select database_role,protection_mode,open_mode from v$database;

DATABASE_ROLE PROTECTION_MODE OPEN_MODE

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

PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY WITH APPLY

开始(fail over)切换测试

#主库 我们通过shutdown abort方式人工模拟primary奔溃，直接关闭:

[oracle@XAG191 ~]$ sqlplus / as sysdba

SQL> select database_role,protection_mode,open_mode from v$database;

DATABASE_ROLE PROTECTION_MODE OPEN_MODE

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

PRIMARY MAXIMUM PERFORMANCE READ WRITE

SQL> shutdown abort

#在primary端执行如下操作(前提是能夠mount)oracle 11g中有一个新特性叫flush redo，

就是说如果主库挂了，只要能mount，就可以将没传到备库的online redo log信息刷到备库去，

这样就不会有数据丢失。

SQL> startup mount;

SQL> alter system flush redo to 'MYCDB192';

--这里记住名字得加上引号，并且这个名字是tnsname中的备库连接名(即備庫的db_unique_name)。

#在主库查看备库是否有gap

SQL> select thread#, low_sequence#, high_sequence# from v$archive_gap;

no rows selected

备库切换到主库操作

#如果没有发现明显的gap现象，说明此次的failover不会有数据损失情况。在standby端，要进行关闭apply和结束应用动作。

#若存在gap，copy之后，注册 sql>alter database register physical logfile ‘/oradata/dg/1_38_925143214.dbf’

#取消和停止应用,通过下列语句暂停redo 应用

[oracle@XAG192 ~]$ sqlplus / as sysdba

SQL> alter database recover managed standby database cancel;

SQL> alter database recover managed standby database finish;

#如果主库和备库之间的网络中断了，那么备库的RFS进程就会等待网络的连接，直到TCP超时。此时需要加上force关键字 sql>alter database recover managed standby database finish force;

#开启实时日志应用

SQL> select open_mode, switchover_status from v$database;

OPEN_MODE SWITCHOVER_STATUS

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

READ ONLY TO PRIMARY

#切换到primary 模式

SQL> alter database commit to switchover to primary with session shutdown;

SQL> select open_mode, switchover_status from v$database;

OPEN_MODE SWITCHOVER_STATUS

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

MOUNTED NOT ALLOWED

SQL> shutdown immediate;

ORA-01109: database not open

SQL> startup

SQL> select open_mode, switchover_status from v$database;

OPEN_MODE SWITCHOVER_STATUS

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

READ WRITE FAILED DESTINATION

主库 Failover后的还原 使用闪回还原主库：

[oracle@XAG191 ~]$ sqlplus / as sysdba

SQL> shutdown immediate;

ORA-01109: database not open

SQL> startup mount;

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

RESTORE POINT ONLY

SQL> flashback database to restore point guar_restore_01;

SQL> drop restore point guar_restore_01;

#切换到物理备库

SQL> alter database convert to physical standby;

SQL> shutdown immediate;

ORA-01109: database not open

SQL> startup

#开启实时日志应用

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

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

NO

SQL> select database_role,protection_mode,open_mode from v$database;

DATABASE_ROLE PROTECTION_MODE OPEN_MODE

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

PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY WITH APPLY

新主機(原備機)检查

[oracle@XAG192 ~]$ sqlplus / as sysdba

SQL> select database_role,protection_mode,open_mode, switchover_status from v$database;

DATABASE_ROLE PROTECTION_MODE OPEN_MODE SWITCHOVER_STATUS

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

PRIMARY MAXIMUM PERFORMANCE READ WRITE TO STANDBY