oracle實驗記錄 (oracle 10G dataguard(5)實時應用)

fufuh2o發表於2009-11-16
Oracle

開啟 實時應用

當開了lgwr 的時候  sync時 過程是 當log_buffer的redo資料寫入redofile 同時 primary的lgwr邊寫入primary 的online logfile;邊開啟lsnn程式（主庫） 傳遞到standby，由standby的rfs程式，寫入standbylogfile ，sync指定的是同步，此時primary的事務會一直保持，直到含有lgwr sync的所有log_archive_dest_n都接收完畢
(所以必須需要配置standby log file)

SQL> show parameter log_archive_dest_2(primary)

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=standby1 lgwr net_time
                                                 ut=10 db_unique_name=standby1

此時是2組standby logfile group 4,group 5  size 50m
  
LGWR中又分sync,async 預設是 SYNC


SQL> alter database recover managed standby database using current logf           (standby)
ile disconnect from session;

SQL> select process,client_process,status,sequence# from v$managed_standby;(standby)

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CLOSING             102
ARCH      ARCH     CONNECTED             0
RFS       LGWR     IDLE                103
RFS       ARCH     IDLE                  0
RFS       N/A      IDLE                  0
MRP0      N/A      APPLYING_LOG        103

 

v$manged_standby 比較重要的是看 裡面程式的 狀態status

PROCESS VARCHAR2(9) Type of process whose information is being reported:
RFS - Remote file server

MRP0 - Detached recovery server process

MR(fg) - Foreground recovery session

ARCH - Archiver process

FGRD

LGWR

RFS(FAL)

RFS(NEXP)

LNS network server process
 
PID NUMBER Operating system process identifier of process

STATUS VARCHAR2(12) Current process status:
UNUSED - No active process

ALLOCATED - Process is active but not currently connected to a primary database

CONNECTED - Network connection established to a primary database

ATTACHED - Process is actively attached and communicating to a primary database

IDLE - Process is not performing any activities

ERROR - Process has failed

OPENING - Process is opening the archived redo log

CLOSING - Process has completed archival and is closing the archived redo log

WRITING - Process is actively writing redo data to the archived redo log

RECEIVING - Process is receiving network communication

ANNOUNCING - Process is announcing the existence of a potential dependent archived redo log

REGISTERING - Process is registering the existence of a completed dependent archived redo log

WAIT_FOR_LOG - Process is waiting for the archived redo log to be completed

WAIT_FOR_GAP - Process is waiting for the archive gap to be resolved

APPLYING_LOG - Process is actively applying the archived redo log to the standby database
 
CLIENT_PROCESS VARCHAR2(8) Identifies the corresponding primary database process:
Archival - Foreground (manual) archival process (SQL)

ARCH - Background ARCn process

LGWR - Background LGWR process
 
CLIENT_PID VARCHAR2(40) Operating system process identifier of the client process
CLIENT_DBID VARCHAR2(40) Database identifier of the primary database
GROUP# VARCHAR2(40) Standby redo log group
RESETLOG_ID NUMBER Resetlogs identifier of the archived redo log
THREAD# NUMBER Archived redo log thread number
SEQUENCE# NUMBER Archived redo log sequence number
BLOCK# NUMBER Last processed archived redo log block number
BLOCKS NUMBER Size of the archived redo log in 512-byte blocks
DELAY_MINS NUMBER Archived redo log delay interval in minutes
KNOWN_AGENTS NUMBER Total number of standby database agents processing an archived redo log
ACTIVE_AGENTS NUMBER Number of standby database agents actively processing an archived redo log


已選擇6行。
SQL> set time on
14:52:32 SQL> select dbid,group#,bytes,status ,first_change#,last_change# from v    (standby)
$standby_log;

DBID           GROUP#      BYTES STATUS     FIRST_CHANGE# LAST_CHANGE#
---------- ---------- ---------- ---------- ------------- ------------
3097375337          4   52428800 ACTIVE           1391729      1392370
UNASSIGNED          5   52428800 UNASSIGNED             0            0

14:52:34 SQL> select dbid,group#,bytes,status ,first_change#,last_change# from v   (standby)
$standby_log;

DBID           GROUP#      BYTES STATUS     FIRST_CHANGE# LAST_CHANGE#
---------- ---------- ---------- ---------- ------------- ------------
3097375337          4   52428800 ACTIVE           1391729      1392370
UNASSIGNED          5   52428800 UNASSIGNED             0            0

從上面可以看出standby log file在不斷接收primary 新產生的redo,last_change#大概每3S更新一次，因為primary LGWR每3秒啟動一次

 

 

SQL> select status,database_mode,recovery_mode from v$archive_dest_status;  (standby)

STATUS    DATABASE_MODE   RECOVERY_MODE
--------- --------------- -----------------------
VALID     MOUNTED-STANDBY MANAGED REAL TIME APPLY~~~~~~~~~~~~~開啟了 實時應用
VALID     MOUNTED-STANDBY MANAGED REAL TIME APPLY
INACTIVE  MOUNTED-STANDBY MANAGED REAL TIME APPLY
INACTIVE  MOUNTED-STANDBY MANAGED REAL TIME APPLY
INACTIVE  MOUNTED-STANDBY MANAGED REAL TIME APPLY
INACTIVE  MOUNTED-STANDBY MANAGED REAL TIME APPLY
INACTIVE  MOUNTED-STANDBY MANAGED REAL TIME APPLY
INACTIVE  MOUNTED-STANDBY MANAGED REAL TIME APPLY
INACTIVE  MOUNTED-STANDBY MANAGED REAL TIME APPLY
INACTIVE  MOUNTED-STANDBY MANAGED REAL TIME APPLY
VALID     MOUNTED-STANDBY MANAGED REAL TIME APPLY

已選擇11行。

SQL> select count(*) from t1; (primary)

  COUNT(*)
----------
        15

SQL> delete from t1;(primary)

已刪除15行。

SQL> commit;(primary)

提交完成。

SQL> select current_scn from v$database;(primary)

CURRENT_SCN
-----------
    1392497

SQL> select dbid,group#,bytes,status ,first_change#,last_change# from v$standby_   (standby)
log;

DBID           GROUP#      BYTES STATUS     FIRST_CHANGE# LAST_CHANGE#
---------- ---------- ---------- ---------- ------------- ------------
3097375337          4   52428800 ACTIVE           1391729      1392501~~~~主庫的改動redo已經包含進來了 從SCN 判斷
UNASSIGNED          5   52428800 UNASSIGNED             0            0
SQL> alter database recover managed standby database cancel; (standby)
 
資料庫已更改。

SQL> alter database open; (standby)

資料庫已更改。

SQL> select * from t1; (standby)

未選定行

SQL> alter system switch logfile;（primary ）

系統已更改。

SQL> select dbid,group#,bytes,status ,first_change# from v$standby_log;  (standby)

DBID           GROUP#      BYTES STATUS     FIRST_CHANGE#
---------- ---------- ---------- ---------- -------------
UNASSIGNED          4   52428800 UNASSIGNED             0
3097375337          5   52428800 ACTIVE           1393565

從這可以看出primary的switch 可以引起standby 的switch

SQL> alter database recover managed standby database using current logfile disco   (standby)
nnect from session;

資料庫已更改。
SQL> select process,client_process,status,sequence# from v$managed_standby;  (standby)

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CLOSING             102
ARCH      ARCH     CLOSING             103
RFS       LGWR     IDLE                104
RFS       ARCH     IDLE                  0
RFS       N/A      IDLE                  0
MRP0      N/A      APPLYING_LOG        104

已選擇6行。

SQL> insert into t1 values(1); （primary ）

已建立 1 行。
commit  ;（primary ）

提交完成。

SQL> alter database recover managed standby database cancel;

資料庫已更改。

SQL> alter database open;

資料庫已更改。

SQL> select * from t1;

         A
----------
         1

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-619714/，如需轉載，請註明出處，否則將追究法律責任。

相關文章