Oracle Standby Redo Log实验两则(2)

DEST_ID DEST_NAME            STATUS    TYPE          DATABASE_MODE  RECOVERY_MODE          PROTECTION_MODE      DESTINATION                                                                      STANDBY_LOGFILE_COUNT STANDBY_LOGFILE_ACTIVE ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# ERROR                                                                            SRL DB_UNIQUE_NAME                SYNCHRONIZATION_STATUS SYNCHRONIZED GAP_STATUS

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

1 LOG_ARCHIVE_DEST_1  VALID    LOCAL          OPEN            IDLE                    MAXIMUM PERFORMANCE  /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch                                                    0                      0                1            36              0            0                                                                                  NO  NONE                          CHECK CONFIGURATION    NO           

2 LOG_ARCHIVE_DEST_2  ERROR    PHYSICAL      OPEN_READ-ONLY  MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE  vlifesb                                                                                              3                      0                1            36              1          35 ORA-12541: TNS: ???à?????ò                                                      YES vlifesb                        CHECK CONFIGURATION    NO          RESOLVABLE GAP

切换一下日志。

SQL> alter system switch logfile;

System altered

SQL> select recid, sequence#, ARCHIVED, APPLIED, DELETED from v$archived_log where;

RECID  SEQUENCE# ARCHIVED APPLIED  DELETED

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

(篇幅原因,有省略……)

24        33 YES      YES      NO

26        34 YES      YES      NO

28        35 YES      YES      NO

30        36 YES      NO        NO

32        37 YES      NO        NO

16 rows selected

新日志没有能够apply,v$log中信息。

SQL> select group#, sequence#, archived, status from v$log;

GROUP#  SEQUENCE# ARCHIVED STATUS

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

1        37 YES      ACTIVE

2        38 NO      CURRENT

3        36 YES      INACTIVE

强行手工checkpoint操作。

SQL> alter system checkpoint;

System altered

SQL> select group#, sequence#, archived, status from v$log;

GROUP#  SEQUENCE# ARCHIVED STATUS

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

1        37 YES      INACTIVE

2        38 NO      CURRENT

3        36 YES      INACTIVE

恢复连接之后,可以发现传输和应用持续过程。

[oracle@vLIFE-URE-OT-DB-STANDBY ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-OCT-2015 11:14:41

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production

System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/vLIFE-URE-OT-DB-STANDBY/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

STATUS of the LISTENER

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

Alias                    LISTENER

Version                  TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                19-OCT-2015 11:14:41

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level              off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File  /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File        /u01/app/oracle/diag/tnslsnr/vLIFE-URE-OT-DB-STANDBY/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "vlifesb" has 1 instance(s).

Instance "vlifesb", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

在standby端,可以查看到持续后追的applied动作。

--Standby端

SQL> select recid, sequence#, ARCHIVED, APPLIED, DELETED from v$archived_log where name is not null;

RECID  SEQUENCE# ARCHIVED APPLIED  DELETED

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

11        32 YES      YES      NO

12        33 YES      YES      NO

13        34 YES      YES      NO

14        35 YES      YES      NO

15        36 YES      YES      NO

16        37 YES      YES      NO

17        38 YES      IN-MEMORY NO

7 rows selected

SQL> select recid, sequence#, ARCHIVED, APPLIED, DELETED from v$archived_log where;

RECID  SEQUENCE# ARCHIVED APPLIED  DELETED

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

(篇幅原因,有省略……)

28        35 YES      YES      NO

30        36 YES      YES      NO

32        37 YES      YES      NO

34        38 YES      NO        NO

17 rows selected

3、终止apply过程实验

如果standby端终止apply过程,后续的redo log不断传入到standby redo log中,看现象如何。

Standby端处理,终止应用日志过程。

--Standby端

SQL> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE

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

READ ONLY WITH APPLY PHYSICAL STANDBY

--终止应用日志

SQL> alter database recover managed standby database cancel;

Database altered

SQL> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE

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

READ ONLY            PHYSICAL STANDBY

此时,standby端日志上显示信息。

Mon Oct 19 11:18:53 2015

alter database recover managed standby database cancel

Mon Oct 19 11:18:53 2015

MRP0: Background Media Recovery cancelled with status 16037

Errors in file /u01/app/oracle/diag/rdbms/vlifesb/vlifesb/trace/vlifesb_pr00_9008.trc:

ORA-16037: user requested cancel of managed recovery operation

Managed Standby Recovery not using Real Time Apply

Recovery interrupted!

Recovered data files to a consistent state at change 1398760

Mon Oct 19 11:18:53 2015

MRP0: Background Media Recovery process shutdown (vlifesb)

Managed Standby Recovery Canceled (vlifesb)

Completed: alter database recover managed standby database cancel

此时,主库情况也是进行到39号redo log。

SQL> select recid, sequence#, ARCHIVED, APPLIED, DELETED from v$archived_log where;

RECID  SEQUENCE# ARCHIVED APPLIED  DELETED

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

(篇幅原因,有省略……)

22        32 YES      YES      NO

24        33 YES      YES      NO

26        34 YES      YES      NO

28        35 YES      YES      NO

30        36 YES      YES      NO

32        37 YES      YES      NO

34        38 YES      YES      NO

17 rows selected

SQL> select group#, sequence#, archived, status from v$log;

GROUP#  SEQUENCE# ARCHIVED STATUS

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

1        37 YES      INACTIVE

2        38 YES      INACTIVE

3        39 NO      CURRENT

连续切换主库日志。

SQL> alter system switch logfile;

System altered

SQL> alter system switch logfile;

System altered

SQL> alter system switch logfile;

System altered

主库情况:

SQL> select group#, sequence#, archived, status from v$log;

GROUP#  SEQUENCE# ARCHIVED STATUS

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

1        40 YES      INACTIVE

2        41 YES      INACTIVE

3        42 NO      CURRENT

SQL> select recid, sequence#, ARCHIVED, APPLIED, DELETED from v$archived_log where;

RECID  SEQUENCE# ARCHIVED APPLIED  DELETED

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

(篇幅原因,有省略…..)

30        36 YES      YES      NO

32        37 YES      YES      NO

34        38 YES      YES      NO

36        39 YES      NO        NO

38        40 YES      NO        NO

40        41 YES      NO        NO

20 rows selected

当前日志切换到42号,由于网络传输是通畅的,所以三个日志是被成功的传输到Standby端,但是没有被应用。

这个时候,我们需要观察standby端的standby redo log情况。

(standby情况)

SQL> select group#, dbid, sequence#, used, archived, status from v$standby_log;

GROUP# DBID                  SEQUENCE#      USED ARCHIVED STATUS

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

4 4207470439                  42      17920 YES      ACTIVE

5 UNASSIGNED                    0          0 NO      UNASSIGNED

6 UNASSIGNED                    0          0 YES      UNASSIGNED

SQL> select recid, sequence#, ARCHIVED, APPLIED, DELETED from v$archived_log;

RECID  SEQUENCE# ARCHIVED APPLIED  DELETED

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

(篇幅原因,有省略……)

16        37 YES      YES      NO

17        38 YES      YES      NO

18        39 YES      NO        NO

19        40 YES      NO        NO

20        41 YES      NO        NO

20 rows selected

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/74d97e6c1a749d60230aefa6066541b4.html