redo 没有apply (备库查询不到数据)

最新推荐文章于 2023-02-09 12:20:51 发布

shenghuiping2001

最新推荐文章于 2023-02-09 12:20:51 发布

阅读量4.2k

点赞数 2

CC 4.0 BY-SA版权

分类专栏： oracle - DataGuard 文章标签： database sql table thread branch file

本文链接：https://blog.youkuaiyun.com/shenghuiping2001/article/details/8042896

oracle - DataGuard 专栏收录该内容

7 篇文章

订阅专栏

本文详细记录了Oracle Data Guard同步测试的过程，包括启动备用数据库、在主库创建表并插入数据、解决同步过程中出现的redolog未应用问题及最终实现数据同步。

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

关于oracle dataguard 的同步测试：

1： Step1:

先启动 standby database:

SQL> startup nomount;

SQL> alter database mount standby database;

SQL> select name,database_role from v$database;

NAME DATABASE_ROLE
--------- ----------------
EZHOU PHYSICAL STANDBY

SQL> alter database recover managed standby database disconnect from session;
(这个语句是为了接受主库的 redo log).

再启动 priamry server.

-------

Step2:

在主库上插入一个table；

SQL> create table dgtest(id int,name char(10));

Table created.

SQL> insert into dgtest values(1,'sheng');

1 row created.

SQL> commit;

看看有没有写的redo log:

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APP
---------- ---
         4 NO
         4 NO
         5 NO
         5 NO
         6 NO
         6 NO

6 rows selected.

看到还咩有，看一下 standby server:

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APP
---------- ---
         4 NO
         5 NO
         6 NO

下面看table

alter database recover managed standby database cancel;

alter database open read only;

select * from dgtest;

SQL> select * from dgtest;
select * from dgtest
*
ERROR at line 1:
ORA-00942: table or view does not exist

原因是上面的 redo file 没有apply,

---------

下面分析一下原因：

备库上：

SQL> select process,client_process,status,sequence#,block# from v$managed_standby;

PROCESS   CLIENT_P STATUS        SEQUENCE#     BLOCK#
--------- -------- ------------ ---------- ----------
ARCH      ARCH     CLOSING               8       2049
ARCH      ARCH     CLOSING               7      30721
MRP0      N/A      WAIT_FOR_GAP          3          0
RFS       UNKNOWN IDLE                  0          0

发现，有gap, 看一下alert 日志：

FAL[client]: All defined FAL servers have been attempted.
-------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
500175

发现上面的mrp0 进程status 是： WAIT_FOR_GAP，Sequence 是3,所以MRP0 进程的process 是n/a

看一下主库上：

alert 日志：

FAL[server]: Fail to queue the whole FAL gap
GAP - thread 1 sequence 3-3
DBID 4046377924 branch 760310664

QL> select current_scn from v$database;

CURRENT_SCN
-----------
518905

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

下面重新配置一下：

SQL> alter database create standby controlfile as '/tmp/stdctr01.ctl';

Database altered.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
0

现在可以吧数据文件，copy 到备库上，create 一个standby control file:
也copy 过去。

下面重新启动主备库，

看一下备库的状态：

SQL> select process,client_process,status,sequence#,block# from v$managed_standby;

PROCESS   CLIENT_P STATUS        SEQUENCE#     BLOCK#
--------- -------- ------------ ---------- ----------
ARCH      ARCH     CONNECTED             0          0
ARCH      ARCH     CONNECTED             0          0
RFS       N/A      IDLE                  0          0

我在主库上switch 一下logfile:

看一下备库：

SQL> select process,client_process,status,sequence#,block# from v$managed_standby;

PROCESS   CLIENT_P STATUS        SEQUENCE#     BLOCK#
--------- -------- ------------ ---------- ----------
ARCH      ARCH     CLOSING               9       4097
ARCH      ARCH     CONNECTED             0          0
RFS       UNKNOWN IDLE                  0          0

看到有sequence 过来了。

如果可以的话，看一下信息,不需要看alert日志：

没有报错：

SQL> select message from v$dataguard_status;

MESSAGE
--------------------------------------------------------------------------------
ARC0: Archival started
ARC1: Archival started
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 10761
RFS[1]: Identified database type as 'physical standby'
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid

MESSAGE
--------------------------------------------------------------------------------
RFS[2]: Assigned to RFS process 10864
RFS[2]: Identified database type as 'physical standby'
RFS[2]: Successfully opened standby log 4: '/u02/ezhou/redo04.dbf'

14 rows selected.

好下面进行一下进行log 的操作：

SQL> alter database recover managed standby database disconnect from session;

Database altered.

再看一下信息：

SQL> select process,client_process,status,sequence#,block# from v$managed_standby;

PROCESS   CLIENT_P STATUS        SEQUENCE#     BLOCK#
--------- -------- ------------ ---------- ----------
ARCH      ARCH     CLOSING               9       4097
ARCH      ARCH     CONNECTED             0          0
RFS       UNKNOWN IDLE                  0          0
MRP0      N/A      WAIT_FOR_LOG         10          0

发现原来的报错变成了：WAIT_FOR_LOG ，我想离成功不远了。

看一下alert 日志：

Completed: alter database recover managed standby database disconnect from session
Sat Oct 6 11:39:50 2012
Media Recovery Waiting for thread 1 sequence 10

我想应该成功了。

---------

好下面在主库上switch log 看一下：

SQL> alter system switch logfile;

这时，看到备库上的alert 日志：

RFS[2]: Successfully opened standby log 4: '/u02/ezhou/redo04.dbf'
Sat Oct 6 11:41:21 2012
Media Recovery Log /u02/ezhou/arch/1_10_760310664.dbf
Media Recovery Waiting for thread 1 sequence 11

SQL> select process,client_process,status,sequence#,block# from v$managed_standby;

PROCESS   CLIENT_P STATUS        SEQUENCE#     BLOCK#
--------- -------- ------------ ---------- ----------
ARCH      ARCH     CLOSING               9       4097
ARCH      ARCH     CLOSING              10          1
RFS       UNKNOWN IDLE                  0          0
MRP0      N/A      WAIT_FOR_LOG         11          0

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

下面到主库上一下，有咩有applied:

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APP
---------- ---
         4 NO
         4 NO
         5 NO
         5 NO
         6 NO
         6 NO
         7 NO
         7 NO
         8 NO
         8 NO
         9 NO

SEQUENCE# APP
---------- ---
         9 YES
        10 NO
        10 YES

到备库上看一下：

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APP
---------- ---
         9 YES
        10 YES

这是我想要的。

下面进行查询一下，看看：

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select * from dgtest;

        ID NAME
---------- ----------
         1 sheng


如我所愿。O(∩_∩)O哈哈~