Queries to verify Oracle configuration

I wrote up this fairly basic cheat sheet for myself several years ago. At the time, I was not very familiar with the Oracle database, and was working with a DBA whom we suspected was not setting things up correctly or even consistently. So, I put together a set of queries to verify basic parameters and compare them to our project documentation. As a result, I learned a bit about database configuration, and we found a very good DBA.

I’m putting these queries on the blog in case someone else finds them useful. The queries were originally for 10gR2.

Data file locations:

SELECT 
  name 
FROM 
  v$datafile;

Temp file locations:

SELECT
  name 
FROM
  v$tempfile;

Undo file locations:

SELECT
  name
FROM
  v$datafile
WHERE
  ts# = (
         SELECT
           ts#
         FROM
           v$tablespace 
         WHERE
           name = (SELECT
                     tablespace_name
                   FROM 
                     dba_tablespaces
                   WHERE
                     contents = 'UNDO')
        );

Archive log destinations:

ARCHIVE LOG LIST;

– or –

SELECT destination FROM v$archive_dest;

Control file locations:

SELECT name FROM v$controlfile;

Redo log locations:

SELECT member FROM v$logfile;

Checking ARCHIVELOG mode:

SELECT log_mode FROM v$database;

Enabling ARCHIVELOG mode:

SQL> startup mount
ORACLE instance started.

Total System Global Area 184549376 bytes
Fixed Size 1300928 bytes
Variable Size 157820480 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

Determine which datafiles have AUTOEXTEND enabled:

SELECT file_name FROM dba_data_files WHERE autoextensible = 'YES';

Disable AUTOEXTEND for a datafile:

ALTER DATABASE DATAFILE '/path/to/datafile.dbf' AUTOEXTEND OFF

Enable AUTOEXTEND for a datafile, but with a maximum size:

ALTER DATABASE DATAFILE '/path/to/datafile.dbf' AUTOEXTEND ON MAXSIZE 500M

Estimate size of SYSAUX tablespace (after system has been running for >1 days):

@$ORACLE_HOME/rdbms/admin/utlsyxsz.sql;

and answer the questions that the script asks

Change archive log destination:

ALTER SYSTEM SET log_archive_dest_1='LOCATION=/path/to/archivelogs';

Getting ORA-19815 errors

“ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.”

What’s happening:

* Oracle is trying to write to the flashback area, but is bumping up against
a hard limit defined by the db_recovery_file_dest_size parameter

Possible causes:

* In ARCHIVELOG mode, but no destination is set (defaults to flashback)

Possible fixes:

* Set one or more archive log destinations
* Increase the db_recovery_file_dest_size (if not full)
– BUT: You’ll probably end up having this error again in the future
if you try to use this as a permanent fix

Open Cursors:

SELECT 
  a.value,
  b.name
FROM
  v$mystat a,
  v$statname b
WHERE
  a.statistic# = b.statistic# AND
  a.statistic# = 3;

Who opened cursors:

SELECT user_name, STATUS, osuser, machine, a.sql_text
FROM v$session b, v$open_cursor a
WHERE a.sid = b.sid;

Max number of cursors per user:

SELECT *
FROM v$parameter
WHERE name = 'open_cursors';