ORA-00065

For those of you still  on Oracle 9, here is a summary of bug 3368245. It caught me out on a few occasions until I looked it up on Metalink. If you are using a server parameter file, setting fixed_date to none causes an ORA-00065 the next time you open the database:

TEST9 > sqlplus '/ as sysdba'

SQL*Plus: Release 9.2.0.7.0 - Production on Fri Feb 11 08:40:19 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

SQL> alter system set fixed_date = none scope = both;

System altered.

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-00065: initialization of FIXED_DATE failed

SQL>

The workaround is to use a pfile or use SCOPE=MEMORY to set FIXED_DATE.

The bug was fixed in Oracle 10:

TEST10 > sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Feb 11 08:57:35 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> alter system set fixed_date = none scope = both;

System altered.

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  155189248 bytes

Fixed Size                  2028560 bytes

Variable Size              79694832 bytes

Database Buffers           71303168 bytes

Redo Buffers                2162688 bytes

Database mounted.

Database opened.

SQL>

Introduction to Auditing

A database’s audit trail is stored in the SYS.AUD$ table:

SQL> desc sys.aud$

Name                       Null?    Type

-------------------------- -------- ------------------

SESSIONID                  NOT NULL NUMBER

ENTRYID                    NOT NULL NUMBER

STATEMENT                  NOT NULL NUMBER

TIMESTAMP#                 NOT NULL DATE

USERID                              VARCHAR2(30)

USERHOST                            VARCHAR2(128)

TERMINAL                            VARCHAR2(255)

ACTION#                    NOT NULL NUMBER

RETURNCODE                 NOT NULL NUMBER

OBJ$CREATOR                         VARCHAR2(30)

OBJ$NAME                            VARCHAR2(128)

AUTH$PRIVILEGES                     VARCHAR2(16)

AUTH$GRANTEE                        VARCHAR2(30)

NEW$OWNER                           VARCHAR2(30)

NEW$NAME                            VARCHAR2(128)

SES$ACTIONS                         VARCHAR2(19)

SES$TID                             NUMBER

LOGOFF$LREAD                        NUMBER

LOGOFF$PREAD                        NUMBER

LOGOFF$LWRITE                       NUMBER

LOGOFF$DEAD                         NUMBER

LOGOFF$TIME                         DATE

COMMENT$TEXT                        VARCHAR2(4000)

CLIENTID                            VARCHAR2(64)

SPARE1                              VARCHAR2(255)

SPARE2                              NUMBER

OBJ$LABEL                           RAW(255)

SES$LABEL                           RAW(255)

PRIV$USED                           NUMBER

SESSIONCPU                          NUMBER

 

SQL>

 

You can tell Oracle what to monitor using the AUDIT command. The example below will record when the SYSTEM user connects to the database:

 

SQL> audit create session by system;

 

Audit succeeded.

 

SQL>

 

This database currently has auditing turned off i.e the audit_trail initialisation parameter is set to NONE:

 

SQL> l

  1  select value from v$parameter

  2* where name = 'audit_trail'

SQL> /

 

VALUE

------------------------------

NONE

 

SQL>

 

So subsequent logins by SYSTEM will not be recorded in SYS.AUD$:

 

SQL> conn system/manager

Connected.

SQL> select count(*) from sys.aud$

  2  /

 

  COUNT(*)

----------

         0

 

SQL>

 

In versions up to and including Oracle 11, you cannot turn auditing on while a database is open:

 

SQL> alter system set audit_trail = true;

alter system set audit_trail = true

                 *

ERROR at line 1:

ORA-02095: specified initialization parameter cannot

be modified

 

SQL>

 

You have to alter the pfile or spfile as appropriate and bounce the database. Once audit_trail is set to true, auditing will start working and a row will appear in SYS.AUD$ after SYSTEM has connected to the database:

 

SQL> select value from v$parameter

  2  where name = 'audit_trail'

  3  /

 

VALUE

------------------------------

TRUE

 

SQL> conn system/manager

Connected.

SQL> select count(*) from sys.aud$

  2  /

 

  COUNT(*)

----------

         1

 

SQL>

 

You can query records in SYS.AUD$ in the normal way. The TIMESTAMP# column has the logon time and a PRIV$USED of 5 stands for CREATE SESSION:

 

SQL> l

  1* select userid, timestamp#, priv$used from sys.aud$

SQL> /

 

USERID     TIMESTAMP#  PRIV$USED

---------- ---------- ----------

SYSTEM     21-MAR-12           5

 

SQL>

 

Various views record the auditing which you have asked Oracle to do in your database. The audit request we made above is stored in DBA_PRIV_AUDIT_OPTS:

 

SQL> select user_name, privilege

  2  from dba_priv_audit_opts

  3  /

 

USER_NAME            PRIVILEGE

-------------------- --------------------

SYSTEM               CREATE SESSION

 

SQL>

 

You can stop an audit request with the NOAUDIT command:

 

SQL> noaudit create session by system

  2  /

 

Noaudit succeeded.

 

SQL>

 

Once you have done this, the relevant entry disappears from DBA_PRIV_AUDIT_OPTS:

 

SQL> select user_name, privilege

  2  from dba_priv_audit_opts

  3  /

 

no rows selected

 

SQL>

 

And connections by SYSTEM will no longer be recorded in SYS.AUD$:

 

SQL> select count(*) from sys.aud$

  2  /

 

  COUNT(*)

----------

         1

 

SQL> conn system/manager

Connected.

SQL> select count(*) from sys.aud$

  2  /

 

  COUNT(*)

----------

         1

 

SQL>

 

Unlike other objects owned by SYS, you are allowed to delete rows from SYS.AUD$.

 

SQL> delete sys.aud$

  2  /

 

1 row deleted.

 

SQL>

 

However, you should check first that it does not contain rows produced by your colleagues with separate audit requests!