ADG故障转移（failover)以及通过闪回机制恢复

最新推荐文章于 2025-05-13 09:24:10 发布

gcnlxx

最新推荐文章于 2025-05-13 09:24:10 发布

阅读量680

点赞数 1

文章标签：服务器数据库运维

本文链接：https://blog.youkuaiyun.com/qq_44233566/article/details/132896635

版权

本文详细介绍了如何在Oracle数据库中设置闪回恢复区，执行failover操作步骤，包括模拟故障、刷新redo日志、切换数据库角色以及验证新主备库状态。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、前置准备（开启闪回）：

1.主库执行show parameter db_recovery;

SQL> show parameter db_recovery;

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_recovery_file_dest                string

db_recovery_file_dest_size           big integer

由此可见没有设置闪回恢复区路径

2.设置恢复区路径以及大小

SQL> alter system set db_recovery_file_dest_size=5G;
SQL> alter system set db_recovery_file_dest='/home/oracle/db_recovery_file_dest';--这里可以自定义路径

3.主库再次执行show parameter db_recovery;查看

SQL> show parameter db_recovery;

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_recovery_file_dest                string
/home/oracle/db_recovery_file_
dest
db_recovery_file_dest_size           big integer
5G

4.执行alter database flashback on;开启闪回

SQL> alter database flashback on;

Database altered.

二、failover操作步骤：

1.模拟主库故障

主库启动至mount状态

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1586708480 bytes
Fixed Size                  2213736 bytes
Variable Size            1308625048 bytes
Database Buffers          268435456 bytes
Redo Buffers                7434240 bytes
Database mounted.

2.刷新redo日志到备库

SQL> ALTER SYSTEM FLUSH REDO TO 'standby';--standby为备库的db_unique_name

System altered.

3.备库查看V$ARCHIVED_LOG视图，确认备库是否应用最新的归档日志（通过查询出的日志序列号与主库的最大日志序列号比较）

SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

    THREAD       LAST
---------- ----------
         1        235

4.确认和解决日志GAP

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

5.无GAP后，备库停止日志应用

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

6.完成所有已经接收日志的应用

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

7.查看备库切换状态（SWITCHOVER_STATUS的值为“TO PRIMARY”或者“SESSIONS ACTIVE”即可进行切换）

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
----------------------------------------
SESSIONS ACTIVE

8.进行切换

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

9.打开新主库（即原备库）

SQL> ALTER DATABASE OPEN;

Database altered.

10.查看状态

SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE;

OPEN_MODE                                DATABASE_ROLE
---------------------------------------- --------------------------------
SWITCHOVER_STATUS                        FORCE_ DATAGUARD_BROKER GUARD_STATUS
---------------------------------------- ------ ---------------- --------------
READ WRITE                               PRIMARY
FAILED DESTINATION                       YES    DISABLED         NONE

三、闪回

1.新主库上查询SCN

SQL> select to_char(standby_became_primary_scn) from v$database;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
--------------------------------------------------------------------------------
4649230

2.原主库进行闪回

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1586708480 bytes
Fixed Size                  2213736 bytes
Variable Size            1308625048 bytes
Database Buffers          268435456 bytes
Redo Buffers                7434240 bytes
Database mounted.
SQL> flashback database to scn 4649230;

Flashback complete.

3.原主库切换至备库

SQL> alter database convert to physical standby;

Database altered.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup
ORACLE instance started.

Total System Global Area 1586708480 bytes
Fixed Size                  2213736 bytes
Variable Size            1325402264 bytes
Database Buffers          251658240 bytes
Redo Buffers                7434240 bytes
Database mounted.
Database opened.

4.新主库执行切换日志命令

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

5.新备库开启redo应用

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

操作至此完成。

四、验证

1.查询主备库状态

--新主库

SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE;

OPEN_MODE                                DATABASE_ROLE
---------------------------------------- --------------------------------
SWITCHOVER_STATUS                        FORCE_ DATAGUARD_BROKER GUARD_STATUS
---------------------------------------- ------ ---------------- --------------
READ WRITE                               PRIMARY
TO STANDBY                               YES    DISABLED         NONE


--新备库

SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE;

OPEN_MODE                                DATABASE_ROLE
---------------------------------------- --------------------------------
SWITCHOVER_STATUS                        FORCE_ DATAGUARD_BROKER GUARD_STATUS
---------------------------------------- ------ ---------------- --------------
READ ONLY WITH APPLY                     PHYSICAL STANDBY
NOT ALLOWED                              YES    DISABLED         NONE

2.新主库新增数据进行验证

主库：

#test_sync为自建测试表，可自行建表验证
SQL> insert into test_sync values('1');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_sync;

ID
--------------------------------------------------------------------------------
1

备库：

SQL> select * from test_sync;

ID
--------------------------------------------------------------------------------
1

验证通过！