Wednesday, May 21, 2014

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>

No comments: