common.Views
V$LOG
显示控制文件里online redo logfile的信息
GROUP#
Log group number
THREAD#
Log thread number
SEQUENCE#
Log sequence number
BYTES
Size of the log (in bytes)
BLOCKSIZE
Block size of the logfile (512 or 4096),细粒度,datafile的blocksize为8192,粗粒度
MEMBERS
Number of members in the log group
ARCHIVED
Archive status (YES) or (NO)
STATUS
Log status:
- UNUSED - Online redo log has never been written to.通常指从未被使用的日志组,即新添加的日志组
- CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed.LGWR进程正把redo log buffer的日志写进日志组中,在进行实例恢复时是必须的
- ACTIVE
- Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived
- 活动的非当前日志,在进行实例恢复时会被用到,Active状态意味着Checkpoint尚未完成,因此该日志文件不能被覆盖,检查点没有做完的根本原因是dbwr执行的dirty buffer写出还没有执行完成,也就是说ckpt进程会等待dbwr写出数据完成,等待的过程就是表示等待将current日志中修改过的数据块信息写入到数据文件的过程,因为这些信息其实是实例恢复的时候要用到的信息,如shutdown abort时,oracle会利用redo日志中的信息对数据库进行实例恢复
- 为刚刚完成日志切换后的状态,此时该日志组中提交的事务引起的数据改变还没有完全从DB buffer cache写入到数据文件中,因此该日志组还不能被覆盖,并且待完全写入后变为inactive状态,如果数据库为归档模式,那么是在该状态下完成归档的
- CLEARING - Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
- CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
- INACTIVE -
- Log is no longer needed for instance recovery. It may be in use for media recovery. It may or may not be archived.
- INACTIVE状态的日志可以被覆盖,可以允许写入日志,如果是在归档模式下,那么此阶段证明归档已经完成
FIRST_CHANGE#
Lowest system change number (SCN) in the log
FIRST_TIME
Time of the first SCN in the log
NEXT_CHANGE#
Highest change number (SCN) in the log. When STATUS=CURRENT, NEXT_CHANGE# is set to the highest possible SCN, 281474976710655.
NEXT_TIME
Time of the highest SCN in the log. When STATUS=CURRENT, NEXT_TIME is set to NULL.
DG.Views
V$MANAGED_STANDBY
V$MANAGED_STANDBY显示DG环境中与物理standby相关进程的当前状态信息,实例关闭后,视图就无法查询
Column | Description |
PROCESS | Type of the process whose information is being reported: * RFS - Remote file server(standby) * MRP0 - Detached recovery server process(standby) * MR(fg) - Foreground recovery session * ARCH - Archiver process * FGRD * LGWR * RFS(FAL)(standby) * RFS(NEXP) * LNS - Network server process(primary) |
PID | Operating system process identifier of the process |
STATUS | 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,不管什么模式下,正常lns的状态 * 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 - 启用arch模式时mrp的状态 * WAIT_FOR_GAP - Process is waiting for the archive gap to be resolved * APPLYING_LOG - mrp进程的状态,物理standy启用realtime apply的状态 |
CLIENT_PROCESS | Identifies the corresponding primary database process: * Archival - Foreground (manual) archival process (SQL) * ARCH - Background ARCn process * LGWR - Background LGWR process |
CLIENT_PID | Operating system process identifier of the client process |
CLIENT_DBID | Database identifier of the primary database |
GROUP# | Standby redo log group |
RESETLOG_ID | Resetlogs identifier of the archived redo log |
THREAD# | Archived redo log thread number |
SEQUENCE# | Archived redo log sequence number |
BLOCK# | Last processed archived redo log block number |
BLOCKS | Count (in 512-byte blocks) of the last write to a redo log, or for a recovery process, the expected final read count |
DELAY_MINS | Archived redo log delay interval in minutes |
KNOWN_AGENTS | Total number of standby database agents processing an archived redo log |
ACTIVE_AGENTS | Number of standby database agents actively processing an archived redo log |
V$DATAGUARD_STATS
备库查询,V$DATAGUARD_STATS displays information about Data Guard metrics when queried on a standby database. No rows are returned when queried on a primary database.该动态性能视图显示出在主库上产生了多少重做日志数据,但是还没有被备库所应用,所以,通过查询该视图可以基本确定如果万一主库出现崩溃的话,备库上将丢失多少重做日志数据,可以在一套Dataguard环境下的任一备库的实例上从该视图里获取相关信息
Column | Description |
NAME | Name of the metric: * APPLY FINISH TIME - An estimate of the time needed to apply all received, but unapplied redo from the primary database. If there are one or more redo gaps on the standby database, an estimate of the time needed to apply all received, but unapplied redo up to the end of the last archived redo log before the beginning of the earliest redo gap.表示在备库上完成应用重做日志所需要的时间 * APPLY LAG - Apply lag is a measure of the degree to which the data in a standby database lags behind the data in the primary database, due to delays in propagating and applying redo to the standby database. This value is relevent only to the applying instance.表示通过在备库上应用主库传递过来的重做日志与主库同步所延迟的时间 * TRANSPORT LAG - Transport lag is a measure of the degree to which the transport of redo to the standby database lags behind the generation of redo on the primary database. If there are one or more redo gaps on the standby database, the transport lag is calculated as if no redo has been received after the beginning of the earliest redo gap.表示在单位时间内主库上产生的重做日志还没有传输到备库上,或者主库上产生的重做日志还没有被备库所应用 * ESTIMATED STARTUP TIME - An estimate of the time needed to start and open the database.表示启动和打开物理备库所需要的时间,该字段不适用于逻辑备库 |
VALUE | Value of the metric |
UNIT | Unit of measurement |
TIME_COMPUTED | Local time at the standby database when the metric was computed,物理备库上估算各个参数的本地时间 |
DATUM_TIME | The APPLY LAG and TRANSPORT LAG metrics are computed based on data that is periodically received from the primary database. An unchanging value in this column across multiple queries indicates that the standby database is not receiving data from the primary database.在物理备库上获取元数据来估算APPLY LAG和TRANSPORT LAG两个参数值的本地时间,如果从多次查询中看到该时间值对应的APPLY LAG和TRANSPORT LAG这两个参数值保持不变,那么就说明该物理备库已经停止从主库接收到重做数据 |
V$DATAGUARD_CONFIG
显示用DB_UNIQUE_NAME和LOG_ARCHIVE_CONFIG初始化参数定义的惟一数据库名称
视图的第一行列出了当前数据库的DB_UNIQUE_NAME,其他行显示LOG_ARCHIVE_CONFIG.DG_CONFIG配置的值
Column | Description |
DB_UNIQUE_NAME | Unique database name |
V$DATAGUARD_STATUS
V$DATAGUARD_STATUS displays messages recently written to the alert log or server process trace files that concern physical standby databases or redo transport services for all standby database types.
olumn | Description |
FACILITY | Facility that encountered the event: * Crash Recovery * Log Transport Services * Log Apply Services * Role Management Services * Remote File Server * Fetch Archive Log * Data Guard * Network Services |
SEVERITY | Severity of the event: * Informational - Informational message * Warning - Warning message * Error - Indicates the process has failed * Fatal * Control - An expected change in state such as the start or completion of an archival, log recovery, or switchover operation |
DEST_ID | Destination ID number to which the event pertains. If the event does not pertain to a particular destination, then the value is 0. |
MESSAGE_NUM | A chronologically increasing number giving each event a unique number |
ERROR_CODE | Error ID pertaining to the event |
CALLOUT | Reserved for future use |
TIMESTAMP | Message date |
MESSAGE | A text message describing the event |