10
Fixed Views

This chapter describes the fixed views that are used in a Data Guard environment. This is a subset of the views that are available for use in a database. This chapter contains the following sections:

• About Fixed Views

• V$ARCHIVE_DEST

• V$ARCHIVE_DEST_STATUS

• V$ARCHIVE_GAP

• V$ARCHIVED_LOG

• V$DATABASE

• V$DATAFILE

• V$LOG

• V$LOGFILE

• V$LOG_HISTORY

• V$MANAGED_STANDBY

• V$STANDBY_LOG

---

About Fixed Views

The Oracle database contains a set of underlying views that are maintained by the server and accessible to the database administrator. These fixed views are also called dynamic performance views because they are continuously updated while a database is open and in use, and their contents relate primarily to performance.

Although these views appear to be regular database tables, they are not. These views provide data on internal disk structures and memory structures. You can select from these views, but you can never update or alter them.

These view names are prefixed with either V$ or GV$, for example, V$ARCHIVE_DEST or GV$ARCHIVE_DEST.

Standard dynamic performance views (V$ fixed views) store information on the local instance. In contrast, global dynamic performance views (GV$ fixed views) store information on all open instances. Each V$ fixed view has a corresponding GV$ fixed view.

In most cases, the information available in fixed views persists across instance shutdowns. However, certain fixed view information is reset when the instance is shut down; these views are specifically identified in this chapter.

For additional information about views, see the Oracle9i Database Reference.

---

V$ARCHIVE_DEST

The V$ARCHIVE_DEST view describes, for the current instance, all the archived redo log destinations, their current value, mode, and status.

Note: The information in this view does not persist across an instance shutdown.

This view contains the following columns:

Column	Description
DEST_ID	Identifies the log archive destination parameter
STATUS	Identifies the current status of the destination. Possible values are: VALID - initialized and available INACTIVE - no destination information DEFERRED - manually disabled by the user ERROR - error during open or copy DISABLED - disabled after error BAD PARAM - parameter has errors ALTERNATE - destination is in an alternate state FULL - exceeded quota size for the destination
BINDING	Specifies how failure will affect the archival operation. Possible values are: OPTIONAL - successful archival is not required MANDATORY - successful archival is required
NAME_SPACE	Identifies the scope of parameter setting. Possible values are: SYSTEM - system definition SESSION - session definition
TARGET	Specifies whether the archive destination is local or remote to the primary database. Possible values are: PRIMARY - local STANDBY - remote
ARCHIVER	Identifies the archiver process relative to the database where the query is issued. Possible values are: ARCn FOREGROUND LGWR RFS
SCHEDULE	Indicates whether the archival of this destination is INACTIVE, PENDING, ACTIVE, or LATENT
DESTINATION	Specifies the location where the archived redo logs are to be archived
LOG_SEQUENCE	Identifies the sequence number of the last archived redo log to be archived
REOPEN_SECS	Identifies the retry time, in seconds, after error
DELAY_MINS	Identifies the delay interval, in minutes, before the archived redo log is automatically applied to a standby database
PROCESS	Identifies the archiver process relative to the primary database, even if the query is issued on the standby database. Possible values are: ARCn FOREGROUND LGWR
MANIFEST	Indicates whether the archived redo log is registered in the remote destination control file. If the archived redo log is registered, it is available to the managed recovery operation. Possible values are: REGISTER NOREGISTER
FAIL_DATE	Indicates the date and time of error
FAIL_SEQUENCE	Indicates the sequence number of the archived redo log being archived when the last error occurred
FAIL_BLOCK	Indicates the block number of the archived redo log being archived when the last error occurred
FAILURE_COUNT	Identifies the current number of contiguous archival operation failures that have occurred for the destination
MAX_FAILURE	Identifies the maximum number of contiguous archival operation failures allowed before the alternate destination is activated
ERROR	Displays the error text
ALTERNATE	Identifies the alternate destination, if any
DEPENDENCY	Identifies the dependent archive destination, if any
REGISTER	Specifies the template to be used to derive the location to be recorded
QUOTA_SIZE	Identifies the destination quotas, expressed in bytes
QUOTA_USED	Identifies the size of all the archived redo logs currently residing on the specified destination
MOUNTID	Identifies the instance mount identifier
AFFIRM	Specifies disk I/O mode
ASYNC_BLOCKS	Number of blocks specified for the ASYNC attribute
TRANSMIT_MODE	Specifies network transmission mode (SYNC or ASYNC)
TYPE	Indicates whether the archived log destination definition is PUBLIC or PRIVATE. Only PUBLIC destinations can be modified at runtime using the ALTER SYSTEM SET or ALTER SESSION SET statements. By default, all archived log destinations are PUBLIC.

---

V$ARCHIVE_DEST_STATUS

The V$ARCHIVE_DEST_STATUS view displays runtime and configuration information for the archived redo log destinations.

Note: The information in this view does not persist across an instance shutdown.

The V$ARCHIVE_DEST_STATUS view contains the following columns:

Column	Description
DEST_ID	Identifies the log archive destination parameter
STATUS	Identifies the current status of the destination. Possible values are: VALID - initialized and available INACTIVE - no destination information DEFERRED - manually disabled by the user ERROR - error during open or copy DISABLED - disabled after error BAD PARAM - parameter has errors ALTERNATE - destination is in an alternate state FULL - exceeded quota size for the destination
TYPE	Identifies the type of archival destination database. Possible values are: LOCAL - local to primary database PHYSICAL - physical standby CROSS-INSTANCE - an instance of the primary
DATABASE_MODE	Identifies the current mode of the archival destination database. Possible values are: STARTED - instance started, not mounted MOUNTED - mounted MOUNTED-STANDBY - mounted standby OPEN - open read/write OPEN_READ-ONLY - open read-only
RECOVERY_MODE	Identifies the current mode of media recovery at the archival destination database. Possible values are: IDLE - managed recovery is not active MANUAL - manual media recovery active MANAGED - managed recovery is active
DESTINATION	Specifies the location where the archived redo logs are to be archived
ARCHIVED_THREAD#	Identifies the thread number of the most recent archived redo log received at the destination
ARCHIVED_SEQ#	Identifies the log sequence number of the most recent archived redo log received at the destination
APPLIED_THREAD#	Identifies the thread number of the most recent applied redo log received at the destination
APPLIED_SEQ#	Identifies the log sequence number of the most recent applied redo log received at the destination
ERROR	Displays the error text
STANDBY_LOGFILE_COUNT	Indicates the total number of standby redo logs created on the standby database
STANDBY_LOGFILE_ACTIVE	Indicates the total number of standby redo logs on the standby database that are active and contain primary database online redo log information

---

V$ARCHIVE_GAP

The V$ARCHIVE_GAP view contains the following columns:

Column	Description
THREAD#	Specifies the thread number
LOW_SEQUENCE#	Specifies the low number of the log file
HIGH_SEQUENCE#	Specifies the high number of the log file

---

V$ARCHIVED_LOG

The V$ARCHIVED_LOG view displays archived redo log information from the control file, including archived log names. This view contains the following columns:

Column	Description
RECID	Archived log record ID
STAMP	Archived log record stamp
NAME	Archived log file name. If set to NULL, the log file was cleared before it was archived.
DEST_ID	The original destination from which the archived log was generated. Value is 0 if the destination identifier is not available.
THREAD#	Redo thread number
SEQUENCE#	Redo log sequence number
RESETLOGS_CHANGE#	Resetlogs change number of the database when this log was written
RESETLOGS_TIME	Resetlogs time of the database when this log was written
FIRST_CHANGE#	First change number in the archived log
FIRST_TIME	Time stamp of the first change
NEXT_CHANGE#	First change in the next log
NEXT_TIME	Time stamp of the next change
BLOCKS	Size of the archived log in blocks
BLOCK_SIZE	Redo log block size. This is the logical block size of the archived log, which is the same as the logical block size of the online log from which this archived log was copied. The online log logical block size is a platform-specific value that is not adjustable by the user.
CREATOR	Identifies the creator of the archived log
REGISTRAR	Identifies the registrar of the entry
STANDBY_DEST	Indicates if the entry is an archived log destination
ARCHIVED	Indicates that the online redo log was archived or that RMAN only inspected the log and created a record for future application of redo logs during recovery
APPLIED	Indicates whether the archived log has been applied to its corresponding standby database
DELETED	Specifies whether an RMAN DELETE command has physically deleted the archived log file from disk, as well as logically removing it from the control file of the target database and from the recovery catalog
STATUS	The status of this archived log. Possible values are: A - Available D - Deleted U - Unavailable X - Expired
COMPLETION_TIME	Time when the archiving completed
DICTIONARY_BEGIN	Indicates whether this log contains the start of a LogMiner dictionary
DICTIONARY_END	Indicates whether this log contains the end of a LogMiner dictionary
BACKUP_COUNT	Indicates the number of times this file has been backed up. Values range from 0 to 15. If the file has been backed up more than 15 times, the value remains 15.
END_OF_REDO	Indicates whether this archived redo log contains the end of all redo information from the primary database. Values are YES and NO.
ARCHIVAL_THREAD#	This is the redo thread number of the instance that performed the archival operation. This column differs from the THREAD# column only when a closed thread is archived by another instance.

---

V$DATABASE

The V$DATABASE view provides database information from the control file. This view contains the following columns:

Column	Description
DBID	The database identifier that is calculated when the database is created. This identifier is stored in all file headers.
NAME	Name of the database
CREATED	Creation date
RESETLOGS_CHANGE#	Change number at open resetlogs
RESETLOGS_TIME	Time stamp of open resetlogs
PRIOR_RESETLOGS_CHANGE#	Change number at prior resetlogs
PRIOR_RESETLOGS_TIME	Time stamp of prior resetlogs
LOG_MODE	Archive log mode
CHECKPOINT_CHANGE#	Last SCN checkpointed
ARCHIVE_CHANGE#	Last SCN archived
CONTROLFILE_TYPE	The type of control file. Possible values are: STANDBY - indicates database is in standby mode CLONE - indicates a clone database BACKUP | CREATED - indicates database is being recovered using a backup or created control file CURRENT - indicates the database is available for general use
CONTROLFILE_CREATED	Control file creation time stamp
CONTROLFILE_SEQUENCE#	Control file sequence number incremented by control file transactions
CONTROLFILE_CHANGE#	Last change number in the backup control file. Set to NULL if the control file is not a backup.
CONTROLFILE_TIME	Last time stamp in the backup control file. Set to NULL if the control file is not a backup.
OPEN_RESETLOGS	Indicates whether the next database open allows or requires the resetlogs option
VERSION_TIME	The version time
OPEN_MODE	Open mode information
STANDBY_MODE	Indicates whether the database is protected
REMOTE_ARCHIVE	The value of the REMOTE_ARCHIVE_ENABLE initialization parameter. Possible values are: TRUE FALSE
ACTIVATION#	Number assigned to the database instantiation.
ARCHIVELOG_CHANGE#	Highest NEXT_CHANGE# (from the V$ARCHIVED_LOG view) for an archived log
DATABASE_ROLE	Current role of the database; either primary or standby
SWITCHOVER_STATUS	Specifies whether switchover is allowed. Possible values are: NOT ALLOWED - Either this is a standby database and the primary database has not been switched first or this is a primary database and there are no standby databases. SESSIONS ACTIVE - Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted. Query the V$SESSION view to identify the specific processes that need to be terminated. SWITCHOVER PENDING - This is a standby database and the primary database switchover request has been received but not processed. SWITCHOVER LATENT - The switchover was in pending mode, but did not complete and went back to the primary database. TO PRIMARY - This is a standby database and is allowed to switch over to a primary database. TO STANDBY - This is a primary database and is allowed to switch over to a standby database. RECOVERY NEEDED - This is a standby database that has not received the switchover request.

---

V$DATAFILE

The V$DATAFILE view provides datafile information from the control file. This view contains the following columns:

Column	Description
FILE#	File identification number
CREATION_CHANGE#	Change number at which the datafile was created
CREATION_TIME	Time stamp of the datafile creation
TS#	Tablespace number
RFILE#	Tablespace relative datafile number
STATUS	Type of file (system or user) and its status. Possible values are: OFFLINE - cannot be written to ONLINE - can be written to SYSTEM - system datafile RECOVER - needs recovery SYSOFF - offline system
ENABLED	Describes how accessible the file is from SQL. Possible values are: DISABLED - no SQL access allowed READ ONLY - no SQL updates allowed READ WRITE - full access allowed
CHECKPOINT_CHANGE#	SCN at last checkpoint
CHECKPOINT_TIME	Time stamp of the last checkpoint
UNRECOVERABLE_CHANGE#	Last unrecoverable change number made to this datafile. This column is always updated when an unrecoverable operation completes.
UNRECOVERABLE_TIME	Time stamp of the last unrecoverable change
LAST_CHANGE#	Last change number made to this datafile. Set to NULL if the datafile is being changed.
LAST_TIME	Time stamp of the last change
OFFLINE_CHANGE#	Offline change number of the last offline range. This column is updated only when the datafile is brought online.
ONLINE_CHANGE#	Online change number of the last offline range
ONLINE_TIME	Online time stamp of the last offline range
BYTES	Current datafile size in bytes; 0 if inaccessible
BLOCKS	Current datafile size in blocks; 0 if inaccessible
CREATE_BYTES	Size when created, in bytes
BLOCK_SIZE	Block size of the datafile
NAME	Datafile name
PLUGGED_IN	Describes whether the tablespace is plugged in. The value is 1 if the tablespace is plugged in and has not been made read/write; 0 if not.
BLOCK1_OFFSET	The offset from the beginning of the file to where the Oracle generic information begins. The exact length of the file can be computed as follows: BYTES + BLOCK1_OFFSET
AUX_NAME	The auxiliary name that has been set for this file

---

V$LOG

The V$LOG view contains log file information from the online redo logs. This view contains the following columns:

Column	Description
GROUP#	Log group number
THREAD#	Log thread number
SEQUENCE#	Log sequence number
BYTES	Size of the log in bytes
MEMBERS	Number of members in the log group
ARCHIVED	Archive status
STATUS	Indicates the log status. Possible values are: UNUSED - The online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS when it is not the current redo log. CURRENT - This is the current redo log. This implies that the redo log is active. The redo log could be open or closed. ACTIVE - The log is active but is not the current log. It is needed for failure recovery. It may be in use for block recovery. It might or might not be archived. CLEARING - The 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 - The 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 - The log is no longer needed for instance recovery. It may be in use for managed recovery. It might or might not be archived. INVALIDATED - Archived the current redo log without a log switch.
FIRST_CHANGE#	Lowest SCN in the log
FIRST_TIME	Time of first SCN in the log

---

V$LOGFILE

The V$LOGFILE view contains information about the online redo logs. This view contains the following columns:

Column	Description
GROUP#	Redo log group identifier number
STATUS	Status of this log member. Possible values are: INVALID - file is inaccessible STALE - contents are incomplete DELETED - file is no longer used blank (no value listed) - file is in use
MEMBER	Redo log member name
TYPE	Specifies whether this is a standby log or an online log. Possible values are: STANDBY ONLINE

---

V$LOG_HISTORY

The V$LOG_HISTORY view contains log history information from the control file. This view contains the following columns:

Column	Description
RECID	Control file record ID
STAMP	Control file record stamp
THREAD#	Thread number of the archived log
SEQUENCE#	Sequence number of the archived log
FIRST_CHANGE#	Lowest SCN in the log
FIRST_TIME	Time of first entry (lowest SCN) in the log
NEXT_CHANGE#	Highest SCN in the log

---

V$MANAGED_STANDBY

The V$MANAGED_STANDBY view displays current and status information information for some Oracle database server processes related to Data Guard.

Note: The information in this view does not persist across an instance shutdown.

The V$MANAGED_STANDBY view contains the following columns:

Column	Description
PROCESS	Type of process whose information is being reported. Possible values are: RFS - remote file server MRP0 - detached recovery server process MR(fg) - foreground recovery session
PID	Operating system process identifier of process
STATUS	Current process status. Possible values are: UNUSED - no active process ALLOCATED - process is active but not currently connected to a primary database client CONNECTED - network connection established to a primary database client ATTACHED - process is actively attached and communicating to a primary database client 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 archived redo log data 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	Identifies the corresponding primary database process. Possible values are: 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
THREAD#	Archived redo log thread number
SEQUENCE#	Archived redo log sequence number
BLOCK#	Last processed archived redo log block number
BLOCKS	Size of the archived redo log in 512-byte blocks
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$STANDBY_LOG

The V$STANDBY_LOG view contains the following columns:

Column	Description
GROUP#	Log group number
THREAD#	Log thread number
SEQUENCE#	Log sequence number
BYTES	Size of the log in bytes
USED	Number of bytes used in the log
ARCHIVED	Archive status
STATUS	Indicates the log status. Possible values are: UNUSED - The online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS when it is not the current redo log. CURRENT - This is the current redo log. This implies that the redo log is active. The redo log could be open or closed. ACTIVE - The log is active but is not the current log. It is needed for failure recovery. It may be in use for block recovery. It might or might not be archived. CLEARING - The 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 - The 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 - The log is no longer needed for instance recovery. It may be in use for managed recovery. It might or might not be archived. INVALIDATED - Archived the current redo log without a log switch.
FIRST_CHANGE#	Lowest SCN in the log
FIRST_TIME	Time of first SCN in the log
LAST_CHANGE#	Last change number made to this datafile. Set to NULL if the datafile is being changed.
LAST_TIME	Time stamp of the last change