【DATAGUARD 学习】监控primary库和standby库


1 查看进程的活动状态。
TESTDG>select process,status,thread#,sequence#,block#,blocks
  2  from v$managed_standby;
PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS             
--------- ------------ ---------- ---------- ---------- ----------             
ARCH      CLOSING               1         42          1        205             
ARCH      CLOSING               1         43       6145       1929             
ARCH      CLOSING               1         40          1        982             
ARCH      CLOSING               1         41          1        304             
RFS         IDLE                        0          0          0          0             
MRP0      WAIT_FOR_LOG  1         44          0          0             
RFS         IDLE                        1         44       5205          2             
RFS         IDLE                        0          0          0          0             
已选择8行。
PROCESS:进程名 ARCH ,RFS,MRP0
CLIENT_PROCESS:对应的主库中的进程如 ARCH,LGWR
STATUS: 进程的当前状态
1)ALLOCATED: 正在准备连接主库
2)ATTACHED: 正在连接主库
3)CONNECTED:已经连接主库
4)IDLE:空闲
5)RECEIVING:归档日志接收中
6)OPENING:归档日志处理中
7)CLOSING:归档日志处理完,收尾中
8)WRITING:REDO数据库写向归档文件中
9)WAIT_FOR_LOG:等待新的REDO数据中
10)WAIT_FOR_GAP:归档有中断,正在等待中断的那部分REDO数据.
11)APPLYING_LOG: 应用REDO数据。
THREAD#进程号
SEQUENCE# :归档序列号
BLOCK# :??
BLOCKS :
  ??
TESTDG>col dest_name for a35
TESTDG>select dest_name,archived_thread#,archived_seq#,applied_thread#,
  2  applied_seq#,db_unique_name
  3  from v$archive_dest_status
  4  where status='VALID';

DEST_NAME          ARCH_THR# ARCH_SEQ#  APP_THR# APP_SEQ# DB_UNIQUE_NAME            
------------------ ---------------------------- ------------ ------------------------LOG_ARCHIVE_DEST_1    1            43              0            0 testdg
LOG_ARCHIVE_DEST_2    0             0              0            0 orcl            
STANDBY_ARCHIVE_DEST  1            42              1           40 NONE 

TESTDG>--检查归档文件路径和创建信息
TESTDG>col name for a35
TESTDG>select name,creator,sequence#,applied,completion_time from v$archived_log;
NAME                                CREATOR  SEQUENCE# APP COMPLETION_TIM      
----------------------------------- ------- ---------- --- --------------      
D:\ORACLE\ARCHDG\ARC_1_33_728782665.ARC ARCH            33 YES 20-9月 -10     
D:\ORACLE\ARCHDG\ARC_1_31_728782665.ARC ARCH            31 YES 20-9月 -10       
D:\ORACLE\ARCHDG\ARC_1_32_728782665.ARC ARCH            32 YES 20-9月 -10      
D:\ORACLE\ARCHDG\ARC_1_34_728782665.ARC ARCH            34 YES 20-9月 -10     
D:\ORACLE\ARCHDG\ARC_1_35_728782665.ARC ARCH            35 YES 20-9月 -10      
D:\ORACLE\ARCHDG\ARC_1_36_728782665.ARC ARCH            36 YES 20-9月 -10      
D:\ORACLE\ARCHDG\ARC_1_37_728782665.ARC ARCH            37 YES 20-9月 -10      
D:\ORACLE\ARCHDG\ARC_1_38_728782665.ARC ARCH            38 YES 20-9月 -10        
D:\ORACLE\ARCHDG\ARC_1_39_728782665.ARC ARCH            39 YES 20-9月 -10      
D:\ORACLE\ARCHDG\ARC_1_40_728782665.ARC ARCH            40 YES 20-9月 -10      
D:\ORACLE\ARCHDG\ARC_1_41_728782665.ARC ARCH            41 YES 20-9月 -10      
D:\ORACLE\ARCHDG\ARC_1_42_728782665.ARC ARCH            42 YES 20-9月 -10      
D:\ORACLE\ARCHDG\ARC_1_43_728782665.ARC ARCH            43 YES 20-9月 -10    
已选择13行。
--查看当前REDO 应用和REDO传输服务的活动状态。
查询物理standby的当前redo应用和redo传输服务的状态。

TESTDG>select process,status,thread#,sequence#,block#,blocks
  2  from v$managed_standby;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS             
--------- ------------ ---------- ---------- ---------- ----------             
ARCH      CLOSING               1         42          1        205             
ARCH      CLOSING               1         43       6145       1929             
ARCH      CLOSING               1         40          1        982             
ARCH      CLOSING               1         41          1        304             
RFS       IDLE                          0          0          0          0             
RFS       IDLE                         1         44          2         38             
RFS       IDLE                          0          0          0          0             

已选择7行。

TESTDG>--检查归档历史
TESTDG>select  first_time,first_change#,next_change# ,sequence# from v$log_history;
FIRST_TIME     FIRST_CHANGE# NEXT_CHANGE#  SEQUENCE#                           
-------------- ------------- ------------ ----------                           
03-9月 -10            886308       921211          1                           
03-9月 -10            921211       929925          2                           
03-9月 -10            929925       946708          3                           
03-9月 -10            946708       967419          4                           
03-9月 -10            967419       992343          5                           
04-9月 -10            992343      1015062          6                           
05-9月 -10           1015062      1057813          7                           
06-9月 -10           1057813      1088443          8                           
07-9月 -10           1088443      1095408          9                           
07-9月 -10           1095408      1123437         10                           
08-9月 -10           1123437      1152797         11                            
09-9月 -10           1152797      1178390         12                           
10-9月 -10           1178390      1178915         13                           
10-9月 -10           1178915      1178920         14                           
10-9月 -10           1178920      1213830         15                           
11-9月 -10           1213830      1252759         16                           
12-9月 -10           1252759      1284851         17                           
13-9月 -10           1284851      1324677         18                           
17-9月 -10           1324677      1359386         19                           
18-9月 -10           1359386      1386121         20                           
18-9月 -10           1386121      1411650         21                           
19-9月 -10           1411650      1432583         22                           
19-9月 -10           1432583      1454040         23                           
20-9月 -10           1454040      1454045         24                           
20-9月 -10           1454045      1459973         25                           
20-9月 -10           1459973      1462648         26                           
20-9月 -10           1462648      1468542         27                           
20-9月 -10           1468542      1496216         28                           
20-9月 -10           1496216      1498057         29                           
20-9月 -10           1498057      1504914         30                           
20-9月 -10           1504914      1511719         31                           
20-9月 -10           1511719      1516010         32                           
20-9月 -10           1516010      1524084         33                           
20-9月 -10           1524084      1526028         34                           
20-9月 -10           1526028      1527720         35                           
20-9月 -10           1527720      1530395         36                           
20-9月 -10           1530395      1530671         37                           
20-9月 -10           1530671      1531644         38                           
20-9月 -10           1531644      1532453         39                           
20-9月 -10           1532453      1533213         40                           
20-9月 -10           1533213      1533482         41                           
20-9月 -10           1533482      1533698         42                           
20-9月 -10           1533698      1538619         43                           
已选择43行。
--查询最后应用的归档文件
TESTDG>select thread#,max(sequence#) as LAST_APPLIED_LOG
  2  FROM V$LOG_HISTORY
  3  GROUP BY THREAD#;
   THREAD# LAST_APPLIED_LOG                                                    
---------- ----------------                                                    
         1               43   
--也可以通过 v$archived_log的APP列获得相同的功能!                                           
TESTDG>select thread#,sequence#,applied from v$archived_log;
   THREAD#  SEQUENCE# APP                                                      
---------- ---------- ---                                                      
         1         33 YES                                                      
         1         31 YES                                                      
         1         32 YES                                                      
         1         34 YES                                                      
         1         35 YES                                                      
         1         36 YES                                                      
         1         37 YES                                                      
         1         38 YES                                                      
         1         39 YES                                                      
         1         40 YES                                                      
         1         41 YES                                                     
         1         42 YES                                                      
         1         43 YES                                                      

已选择13行。

ORCL>--在主库上查看未接收的日志
ORCL>select local.thread#,local.sequence# from
  2  ( select thread#,sequence# from v$archived_log where dest_id=1) local
  3  where local.sequence# not in
  4  (select sequence# from v$archived_log where dest_id=2 and thread#=local.thread#);
   THREAD#  SEQUENCE#                                                          
---------- ----------                                                          
         1         17                                                          
         1         18                                                          
         1         19                                                          
         1         20                                                          
         1         21                                                          
         1         22                                                          
         1         23                                                          
         1         24                                                          
         1         25                                                          
         1         26                                                          
         1         27                                                        
         1         28                                                          
         1         29                                                          
         1         30    

注意:dest_id =N 就是LOG_ARCHIVE_DEST_N 中的那个N.
--检查应用模式(是否启用的实时应用)
TESTDG>select recovery_mode from v$archive_dest_status where dest_id=2;
RECOVERY_MODE                                                                  
-----------------------                                                        
IDLE --没有启用,启用了实时应用则为:MANAGED READ TIME APPLY                           
---DATAGUARD 事件(v$dataguard_status).该视图显示alert.log和trace文件中记录的事件。                           
TESTDG>select message from v$dataguard_status;
MESSAGE                                                                        
-------------------------------------------------------------
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC3: 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 8424
RFS[1]: Identified database type as 'physical standby'
Redo Shipping Client Connected as PUBLIC                                       
-- Connected User is Valid      
RFS[2]: Assigned to RFS process 8308 
RFS[2]: Identified database type as 'physical standby'
Redo Shipping Client Connected as PUBLIC 
-- Connected User is Valid         
RFS[3]: Assigned to RFS process 8064
RFS[3]: Identified database type as 'physical standby'                         
Attempt to start background Managed Standby Recovery process                   
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery not using Real Time Apply

MESSAGE                                                                        
----------------------------------------------------------------
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_31_728782665.ARC
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_32_728782665.ARC 
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_33_728782665.ARC
Media Recovery Waiting for thread 1 sequence 34   
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid                                                     
RFS[4]: Assigned to RFS process 8656                                           
RFS[4]: Identified database type as 'physical standby'
Redo Shipping Client Connected as PUBLIC                                       
-- Connected User is Valid                                
RFS[5]: Assigned to RFS process 8812                     

MESSAGE                                                 
--------------------------------------------------------
RFS[5]: Identified database type as 'physical standby'                         
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Successfully opened standby log 4: 'D:\ORACLE\TESTDGDATA\STANDBY01.LOG'
RFS[5]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_34_728782665.ARC        
Media Recovery Waiting for thread 1 sequence 35 (in transit)   
Redo Shipping Client Connected as PUBLIC 
-- Connected User is Valid   
RFS[6]: Assigned to RFS process 7224   
RFS[6]: Identified database type as 'physical standby'

MESSAGE                                                                        
--------------------------------------------------------------------------------
Primary database is in MAXIMUM PERFORMANCE mode                                
RFS[4]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_35_728782665.ARC                     
Media Recovery Waiting for thread 1 sequence 36 (in transit)                   
MRP0: Background Media Recovery cancelled with status 16037                    
MRP0: Background Media Recovery process shutdown                               
Managed Standby Recovery Canceled                                              
Attempt to start background Managed Standby Recovery process                   
MRP0: Background Managed Standby Recovery process started                      
Managed Standby Recovery not using Real Time Apply                             
Media Recovery Waiting for thread 1 sequence 36 (in transit)                   

MESSAGE                                                                        
--------------------------------------------------------------------------------
Primary database is in MAXIMUM PERFORMANCE mode                                
RFS[4]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_36_728782665.ARC                     
Media Recovery Waiting for thread 1 sequence 37 (in transit)                   
Primary database is in MAXIMUM PERFORMANCE mode                                
RFS[4]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_37_728782665.ARC                     
Media Recovery Waiting for thread 1 sequence 38 (in transit)                   
Primary database is in MAXIMUM PERFORMANCE mode                                
RFS[4]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_38_728782665.ARC                     

MESSAGE                                                                        
--------------------------------------------------------------------------------
MRP0: Background Media Recovery terminated with error 1274                     
MRP0: Background Media Recovery process shutdown                               
Redo Shipping Client Connected as PUBLIC                                       
-- Connected User is Valid                                                     
RFS[7]: Assigned to RFS process 10272                                          
RFS[7]: Identified database type as 'physical standby'                         
Attempt to start background Managed Standby Recovery process                   
MRP0: Background Managed Standby Recovery process started                      
Managed Standby Recovery not using Real Time Apply                             
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_38_728782665.ARC                     
Media Recovery Waiting for thread 1 sequence 39                                

MESSAGE                                                                        
--------------------------------------------------------------------------------
Redo Shipping Client Connected as PUBLIC                                       
-- Connected User is Valid                                                     
RFS[8]: Assigned to RFS process 8292                                           
RFS[8]: Identified database type as 'physical standby'                         
Primary database is in MAXIMUM PERFORMANCE mode                                
Redo Shipping Client Connected as PUBLIC                                       
-- Connected User is Valid                                                     
RFS[9]: Assigned to RFS process 9672                                           
RFS[9]: Identified database type as 'physical standby'                         
Primary database is in MAXIMUM PERFORMANCE mode                                
RFS[8]: Successfully opened standby log 4: 'D:\ORACLE\TESTDGDATA\STANDBY01.LOG'

MESSAGE                                                                        
--------------------------------------------------------------------------------
RFS[9]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_39_728782665.ARC                     
Media Recovery Waiting for thread 1 sequence 40 (in transit)                   
Primary database is in MAXIMUM PERFORMANCE mode                                
RFS[8]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_40_728782665.ARC                     
MRP0: Background Media Recovery terminated with error 1274                     
MRP0: Background Media Recovery process shutdown                               
Primary database is in MAXIMUM PERFORMANCE mode                                
RFS[8]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Primary database is in MAXIMUM PERFORMANCE mode                                

MESSAGE                                                                        
--------------------------------------------------------------------------------
RFS[8]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Primary database is in MAXIMUM PERFORMANCE mode                                
RFS[8]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Attempt to start background Managed Standby Recovery process                   
MRP0: Background Managed Standby Recovery process started                      
Managed Standby Recovery not using Real Time Apply                             
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_40_728782665.ARC                     
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_41_728782665.ARC                     
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_42_728782665.ARC                     
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_43_728782665.ARC                     
Media Recovery Waiting for thread 1 sequence 44 (in transit)                   

MESSAGE                                                                        
--------------------------------------------------------------------------------
MRP0: Background Media Recovery cancelled with status 16037                    
MRP0: Background Media Recovery process shutdown                               
Managed Standby Recovery Canceled                                              
Attempt to start background Managed Standby Recovery process                   
MRP0: Background Managed Standby Recovery process started                      
Managed Standby Recovery not using Real Time Apply                             
Media Recovery Waiting for thread 1 sequence 44 (in transit)                   
已选择117行。
--查询当前数据库的基本信息如数据库的角色和保护模式,保护级别。
TESTDG>select database_role,db_unique_name,
  2  open_mode,protection_mode,
  3  protection_level,switchover_status
  4  from v$database;

DATABASE_ROLE DB_UNIQUE_ OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL   
------------- ---------- --------- --------------- ----------------
SWITCHOVER_STATUS                                                              
------------------                                                          
PHYSICAL STANDBY testdg  READ ONLY  MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE
NOT ALLOWED                                                                    
                                                                               
---查看failover后快速启动的信息。
TESTDG>select fs_failover_status ,
  2  fs_failover_current_target,
  3  fs_failover_threshold,
  4  fs_failover_observer_present from v$database;

FS_FAIL_STATUS  FS_FAIL_CUR_TARGET FS_FAIL_THRESHOLD  FS_FAIL   
-------------- ------------------- ------------------ ---- 
DISABLED                                                0    

上一篇:P6Spy & SQL Profiler & IronTrack SQL


下一篇:Eclipse 插件之Spring IDE