Spatial DBA - Oracle and ArcSDE: Jan 16, 2009

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.

====================================================================

V$DATABASE displays information about the database from the control file.

Column	Datatype	Description
DBID	NUMBER	Database identifier calculated when the database is created and stored in all file headers
NAME	VARCHAR2(9)	Name of the database
CREATED	DATE	Creation date of the database
RESETLOGS_CHANGE#	NUMBER	System change number (SCN) at open resetlogs
RESETLOGS_TIME	DATE	Timestamp of open resetlogs
PRIOR_RESETLOGS_CHANGE#	NUMBER	SCN at prior resetlogs
PRIOR_RESETLOGS_TIME	DATE	Timestamp of prior resetlogs
LOG_MODE	VARCHAR2(12)	Archive log mode: NOARCHIVELOG ARCHIVELOG MANUAL
CHECKPOINT_CHANGE#	NUMBER	Last SCN checkpointed
ARCHIVE_CHANGE#	NUMBER	Database force archiving SCN. Any redo log with a start SCN below this will be forced to archive out.
CONTROLFILE_TYPE	VARCHAR2(7)	Type of control file: STANDBY - Indicates that the database is in standby mode CLONE - Indicates a clone database BACKUP \| CREATED - Indicates the database is being recovered using a backup or created control file CURRENT - database is available for general use
CONTROLFILE_CREATED	DATE	Creation date of the control file
CONTROLFILE_SEQUENCE#	NUMBER	Control file sequence number incremented by control file transactions
CONTROLFILE_CHANGE#	NUMBER	Last SCN in backup control file; null if the control file is not a backup
CONTROLFILE_TIME	DATE	Last timestamp in backup control file; null if the control file is not a backup
OPEN_RESETLOGS	VARCHAR2(11)	(NOT ALLOWED \| ALLOWED \| REQUIRED) Indicates whether the next database open allows or requires the resetlogs option
VERSION_TIME	DATE	Version time
OPEN_MODE	VARCHAR2(10)	Open mode information: MOUNTED READ WRITE READ ONLY
PROTECTION_MODE	VARCHAR2(20)	Protection mode currently in effect for the database: MAXIMUM PROTECTION - Database is running in maximized protection mode MAXIMUM AVAILABILITY - Database is running in maximized availability mode RESYNCHRONIZATION - Database is running in resynchronization mode MAXIMUM PERFORMANCE - Database is running in maximized protection mode UNPROTECTED - Database is unprotected (this normally occurs when the primary database is mounted and not open)
PROTECTION_LEVEL	VARCHAR2(20)	Aggregated protection mode currently in effect for the database: MAXIMUM PROTECTION - Database is running in maximized protection mode MAXIMUM AVAILABILITY - Database is running in maximized availability mode RESYNCHRONIZATION - Database is running in resynchronization mode MAXIMUM PERFORMANCE - Database is running in maximized protection mode UNPROTECTED - Database is unprotected (this normally occurs when the primary database is mounted and not open) Note: This column is an aggregation of the PROTECTION_MODE of all standby archive log destinations.
REMOTE_ARCHIVE	VARCHAR2(8)	Value of the REMOTE_ARCHIVE_ENABLE initialization parameter
ACTIVATION#	NUMBER	Number assigned to the database instantiation
SWITCHOVER#	NUMBER	Number assigned to the database switchover
DATABASE_ROLE	VARCHAR2(16)	Current role of the database: SNAPSHOT STANDBY LOGICAL STANDBY PHYSICAL STANDBY PRIMARY
ARCHIVELOG_CHANGE#	NUMBER	Highest NEXT_CHANGE# (from the V$ARCHIVED_LOG view) for an archive log
ARCHIVELOG_COMPRESSION	VARCHAR2(8)	Status of the archive log compression (ENABLED) or (DISABLED)
SWITCHOVER_STATUS	VARCHAR2(20)	Indicates whether switchover is allowed: 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. PREPARING SWITCHOVER - Either this is a primary database that is accepting redo data from a logical standby database in preparation for switch over to the logical standby database role, or it is a logical standby database sending redo data to a primary database and other standby databases in preparation for switch over to the primary database role. In the latter case, a completed dictionary has already been sent to the primary database and other standby databases. PREPARING DICTIONARY - This is a logical standby database that is sending redo data to a primary database and other standby databases in the configuration in preparation for switch over to the primary database role. TO LOGICAL STANDBY - This is a primary database that has received a complete dictionary from a logical standby database.
DATAGUARD_BROKER	VARCHAR2(8)	Indicates whether the Data Guard configuration is being managed by the broker (ENABLED) or not (DISABLED)
GUARD_STATUS	VARCHAR2(7)	Protects data from being changed: ALL - Indicates all users other than SYS are prevented from making changes to any data in the database. STANDBY - Indicates all users other than SYS are prevented from making changes to any database object being maintained by logical standby. NONE - Indicates normal security for all data in the database.
SUPPLEMENTAL_LOG_DATA_MIN	VARCHAR2(8)	Ensures that LogMiner (and any products building on LogMiner technology) will have sufficient information to support chained rows and various storage arrangements such as cluster tables: NO - None of the database-wide supplemental logging directives are enabled IMPLICIT - Minimal supplemental logging is enabled because all or a combination of primary key, unique key, and foreign key supplemental logging is enabled YES - Minimal supplemental logging is enabled through an ALTER DATABASE ADD SUPPLEMENTAL LOG DATA statement
SUPPLEMENTAL_LOG_DATA_PK	VARCHAR2(3)	For all tables with a primary key, indicates whether all columns of the primary key are placed into the redo log whenever an update is performed (YES) or not (NO)
SUPPLEMENTAL_LOG_DATA_UI	VARCHAR2(3)	For all tables with a unique key, indicates whether all other columns belonging to the unique key are placed into the redo log if any of the unique key columns are modified (YES) or not (NO)
FORCE_LOGGING	VARCHAR2(3)	Indicates whether the database is under force logging mode (YES) or not (NO)
PLATFORM_ID	NUMBER	Platform identification number of the database
PLATFORM_NAME	VARCHAR2(101)	Platform name of the database
RECOVERY_TARGET_INCARNATION#	NUMBER	Incarnation number where all datafiles are recovered by the RECOVER DATABASE command
LAST_OPEN_INCARNATION#	NUMBER	Record number of the incarnation in V$DATABASE_INCARNATION that was last opened successfully
CURRENT_SCN	NUMBER	Current SCN; null if the database is not currently open. For a standby database, the current standby redo application SCN.
FLASHBACK_ON	VARCHAR2(18)	Possible values are as follows: YES - Flashback is on NO - Flashback is off RESTORE POINT ONLY - Flashback is on but one can only flashback to guaranteed restore points
SUPPLEMENTAL_LOG_DATA_FK	VARCHAR2(3)	For all tables with a foreign key, indicates whether all other columns belonging to the foreign key are placed into the redo log if any foreign key columns are modified (YES) or not (NO)
SUPPLEMENTAL_LOG_DATA_ALL	VARCHAR2(3)	For all columns, indicates whether all the fixed-length maximum size columns of that row are placed into the redo log (YES) or not (NO)
DB_UNIQUE_NAME	VARCHAR2(30)	Unique database name
STANDBY_BECAME_PRIMARY_SCN	NUMBER	SCN at which a physical standby database became a primary database. This SCN is useful for converting a failed primary database into a physical standby database after a forced failover.
FS_FAILOVER_STATUS	VARCHAR2(21)	Fast-start failover status: DISABLED BYSTANDER SYNCHRONIZED UNSYNCHRONIZED SUSPENDED STALLED LOADING DICTIONARY PRIMARY UNOBSERVED REINSTATE REQUIRED REINSTATE IN PROGRESS REINSTATE FAILED TARGET OVER LAG LIMIT TARGET UNDER LAG LIMIT
FS_FAILOVER_CURRENT_TARGET	VARCHAR2(30)	DB_UNIQUE_NAME of the standby that is the current FSFO target standby for the Data Guard configuration
FS_FAILOVER_THRESHOLD	NUMBER	Time (in seconds) that the observer will attempt to reconnect with a disconnected primary before attempting FSFO with the target standby
FS_FAILOVER_OBSERVER_PRESENT	VARCHAR2(7)	Indicates whether the observer is currently connected to the local database (YES) or not (NO) Note: This column is consistent throughout an Oracle RAC environment; that is, if the observer is connected to any instance, then all instances will show a value of YES.
FS_FAILOVER_OBSERVER_HOST	VARCHAR2(512)	Machine name that is currently hosting the observer process
CONTROLFILE_CONVERTED	VARCHAR2(3)	??? (YES) or (NO)
PRIMARY_DB_UNIQUE_NAME	VARCHAR2(30)	For any Standby database (Physical, Logical, or Reporting), this column will contain the DB_UNIQUE_NAME of the Primary database that this Standby last received current redo from. If this standby has not received any current redo since last being started, then this column will be null. For a Primary database that had previously been a Standby, this column will contain the DB_UNIQUE_NAME of the last Primary that this database received current redo from while acting as a Standby. For a Primary database that has never been a Standby, this column will be null.
SUPPLEMENTAL_LOG_DATA_PL	VARCHAR2(3)	??? (YES) or (NO)
MIN_REQUIRED_CAPTURE_CHANGE#	VARCHAR2(40)	Minimum REQUIRED_CHECKPOINT_SCN for all local capture processes on the database

Note:

1. Scripts using v$database

-- check database archivelog mode

select log_mode from v$database;

select name, log_mode from v$database;

--check the latest SCN in the database

select current_scn from v$database;

--check if suplementallogging is enabled: YES/IMPLICIT means enabled;

--ALTER DATABASE ADD SUPPLEMENTAL LOG DATA - to enable;

select supplemental_log_data_min from v$database;

--check the database platform

select platform_id, platform_name from v$database;

--check if flashback is turned on / off

select flashback_on from v$database;

2. The following views contain information from the control file:

· V$DATABASE displays information about the database from the control file.

· V$ARCHIVED_LOG displays archived log information from the control file, including archive log names.

· DBA_HIST_DATAFILE displays a history of the datafile information from the control file.

· DBA_HIST_LOG displays historical log file information from the control file. This view contains snapshots of V$LOG.

· DBA_HIST_TABLESPACE_STAT displays tablespace information from the control file. This view contains snapshots of V$TABLESPACE and DBA_TABLESPACE.

· DBA_HIST_TEMPFILE displays a history of the tempfile information from the control file. This view contains snapshots of V$TEMPFILE.

· DBA_HIST_THREAD displays historical thread information from the control file.

· V$BACKUP_CORRUPTION displays information about corrupt block ranges in datafile backups from the control file. Note that corruptions are not tolerated in the control file and archived redo log backups.

· V$BACKUP_DATAFILE displays information about control files and datafiles in backup sets from the control file.

· V$BACKUP_PIECE displays information about backup pieces from the control file. Each backup set consists of one or more backup pieces.

· V$BACKUP_REDOLOG displays information about archived logs in backup sets from the control file. Note that online redo logs cannot be backed up directly; they must be archived first to disk and then backed up. An archive log backup set can contain one or more archived logs.

· V$BACKUP_SET displays information about backup sets from the control file. A backup set record is inserted after the backup set is successfully completed.

· V$COPY_CORRUPTION displays information about datafile copy corruptions from the control file.

· V$BACKUP_SPFILE displays information about server parameter files in backup sets from the control file.

· V$DATAFILE displays datafile information from the control file.

· V$DATAFILE_COPY displays datafile copy information from the control file.

· V$DELETED_OBJECT displays information about deleted archived logs, datafile copies and backup pieces from the control file. The only purpose of this view is to optimize the recovery catalog resync operation. When an archived log, datafile copy, or backup piece is deleted, the corresponding record is marked deleted.

· V$LOG displays log file information from the control file.

· V$LOG_HISTORY displays log history information from the control file.

· V$LOGHIST contains log history information from the control file. This view is retained for historical compatibility. Oracle recommends that you use V$LOG_HISTORY instead.

· V$OFFLINE_RANGE displays datafile offline information from the control file. Note that the last offline range of each datafile is kept in the DATAFILE record.

· V$TABLESPACE displays tablespace information from the control file.

· V$THREAD displays thread information from the control file.

Last updated: Wednesday, 2009-10-07