real-time apply：就是備庫USING CURRENT LOGFILE,對應檢視V$ARCHIVE_DEST_STATUS.RECOVERY_MODE=MANAGED REAL TIME APPLY
real-time query：就是備庫啟到open read only模式,對應檢視v$database.open_mode=READ ONLY WITH APPLY

real-time query
V$DATABASE.OPEN_MODE
READ ONLY WITH APPLY - A physical standby database is open in real-time query mode

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select V$DATABASE.OPEN_MODE from V$DATABASE;
OPEN_MODE
--------------------
READ ONLY WITH APPLY

real-time apply
Use the ALTER DATABASE statement to enable the real-time apply feature, as follows:
For physical standby databases, issue the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE statement.
SQL> alter database recover managed standby database using current logfile disconnect from session;

V$ARCHIVE_DEST_STATUS.RECOVERY_MODE
■ MANAGED - Managed recovery is active
■ MANAGED REAL TIME APPLY - Log apply services recover redo data from standby redo logs at the same time the logs are being written to,as opposed to recovering redo from archived redo logs when a log switch occurs

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select DEST_ID,DEST_NAME,RECOVERY_MODE from V$ARCHIVE_DEST_STATUS where dest_id<4;
DEST_ID DEST_NAME RECOVERY_MODE
1 LOG_ARCHIVE_DEST_1 MANAGED
2 LOG_ARCHIVE_DEST_2 IDLE

SQL> alter database recover managed standby database cancel;
Database altered.

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

SQL> select DEST_ID,DEST_NAME,RECOVERY_MODE from V$ARCHIVE_DEST_STATUS where dest_id<4;
DEST_ID DEST_NAME RECOVERY_MODE
1 LOG_ARCHIVE_DEST_1 MANAGED REAL TIME APPLY
2 LOG_ARCHIVE_DEST_2 IDLE

以下兩個檢視都不是查詢是否real-time apply的檢視
V$LOGSTDBY_STATE.REALTIME_APPLY
Y indicates that SQL Apply is running in real-time apply mode. If a standby redo log is configured, SQL Apply applies changes as they are written to the standby redo log files. N indicates that SQL Apply applies changes as each archived redo log file is received.

V$MANAGED_STANDBY.STATUS
APPLYING_LOG - Process is actively applying the archived redo log to the standby database

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select V$MANAGED_STANDBY.STATUS from V$MANAGED_STANDBY;
STATUS
------------
CLOSING
CLOSING
CONNECTED
CLOSING
WAIT_FOR_LOG
IDLE
IDLE
IDLE

8 rows selected.

SQL> select status, sequence#, block# from v$managed_standby where client_process='LGWR';
STATUS SEQUENCE# BLOCK#
------------ ---------- ----------
IDLE 60 1059

SQL> /
STATUS SEQUENCE# BLOCK#
------------ ---------- ----------
IDLE 60 1061

SQL> select V$LOGSTDBY_STATE.REALTIME_APPLY from V$LOGSTDBY_STATE;
no rows selected

SQL> alter database recover managed standby database cancel;
Database altered.

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

SQL> select V$MANAGED_STANDBY.STATUS from V$MANAGED_STANDBY;
STATUS
------------
CLOSING
CLOSING
CONNECTED
CLOSING
APPLYING_LOG
IDLE
IDLE
IDLE

8 rows selected.

SQL> select V$LOGSTDBY_STATE.REALTIME_APPLY from V$LOGSTDBY_STATE;
no rows selected

查詢real-time apply、real-time query的檢視

相關文章