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>

Server Parameter Files

This was tested on Oracle 11.2. Some initialisation parameters cannot be changed while the database is open. If you try to change one of these, you get an ORA-02095:

 

SQL> alter system set sessions = 30;

alter system set sessions = 30

                 *

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

 

SQL>

 

If you are using a server parameter file, you can make the change there and bounce the database. But if you are not then this option is not available to you either:

 

SQL> l

  1  alter system set sessions = 30

  2* scope = spfile

SQL> /

alter system set sessions = 30

*

ERROR at line 1:

ORA-32001: write to SPFILE requested but no SPFILE is in use

 

SQL>

 

You can start using a server parameter file as follows. This assumes that the database is a test one and nobody else is using it. Otherwise you should check with the users first then do a shutdown normal to bounce the database instead:

 

SQL> create spfile from pfile;

 

File created.

 

SQL> startup force;

ORACLE instance started.

 

Total System Global Area  417669120 bytes

Fixed Size                  2148672 bytes

Variable Size             255858368 bytes

Database Buffers          155189248 bytes

Redo Buffers                4472832 bytes

Database mounted.

Database opened.

SQL>

 

Then you can make the change which failed above:

 

SQL> alter system set sessions = 30

  2  scope = spfile;

 

System altered.

 

SQL> startup force

ORACLE instance started.

 

Total System Global Area  417669120 bytes

Fixed Size                  2148672 bytes

Variable Size             260052672 bytes

Database Buffers          150994944 bytes

Redo Buffers                4472832 bytes

Database mounted.

Database opened.

SQL>

 

I will look at server parameter files in more detail in a future post but I wanted to show you this as it happened to me today. You may wonder why I set the sessions parameter so low. I did it on purpose to demonstrate ORA-00018. Unfortunately, the change did not work:

 

SQL> l

  1  select value from v$parameter

  2* where name = 'sessions'

SQL> /

 

VALUE

----------

776

 

SQL>

 

I checked the Oracle 11.2 documentation on this and it said that the minimum value allowed for the sessions parameter is processes * 1.5 + 22. The processes parameter for this database is set to 500:

 

SQL> l

  1  select value from v$parameter

  2* where name = 'processes'

SQL> /

 

VALUE

----------

500

 

SQL>

 

This should give me a sessions value of 500 + 250 + 22 = 772 so it is not too far out. I'll just have to think of another way to demonstrate ORA-00018!

Drop Database

In version 10g, Oracle introduced the drop database SQL statement. This removes all datafiles, online redo log files, control files and server parameter files. I created a database with the Database Configuration Assistant so that I could try out this new command. To make it easier to demonstrate, I put the datafiles, online redo log files and control files all in the same directory:

SQL> col file_name format a55
SQL> select file_name from dba_data_files;

FILE_NAME
-------------------------------------------------------
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\USERS01.DBF
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\SYSAUX01.DBF
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\UNDOTBS01.DBF
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\SYSTEM01.DBF

SQL> select file_name from dba_temp_files;

FILE_NAME
-------------------------------------------------------
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\TEMP01.DBF
  
SQL> col member format a50
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\REDO03.LOG
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\REDO02.LOG
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\REDO01.LOG

SQL> col name format a55
SQL> select name from v$controlfile;

NAME
-------------------------------------------------------
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\CONTROL01.CTL
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\CONTROL02.CTL
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\CONTROL03.CTL

SQL>

It’s easier to see in the screen print below (click to enlarge it):
   

The server parameter file was in a different directory:

SQL> col value format a50
SQL> select value from v$parameter where name = 'spfile';

VALUE
--------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\SPFILETEST10.ORA

SQL>

Then I tried to drop the database:

SQL> set lines 60
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

SQL>

I followed the instructions and tried again: 

C:\Documents and Settings\Andrew>set ORACLE_SID=TEST10

C:\Documents and Settings\Andrew>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 24 09:35:34 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250452 bytes
Variable Size 176163692 bytes
Database Buffers 432013312 bytes
Redo Buffers 2940928 bytes
Database mounted.
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode

SQL>

Then putting the database into restricted session mode appeared to have the desired effect:

SQL> alter system enable restricted session;

System altered.

SQL> drop database;

Database dropped.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

The datafiles were removed:

C:\Documents and Settings\Andrew\test10>dir
Volume in drive C has no label.
Volume Serial Number is 18E4-B972

Directory of C:\Documents and Settings\Andrew\test10

24/12/2010 10:01 <DIR> .
24/12/2010 10:01 <DIR> ..
              0 File(s) 0 bytes
              2 Dir(s) 11,539,795,968 bytes free

C:\Documents and Settings\Andrew\test10>

And so was the spfile:

C:\oracle\product\10.2.0\db_1\dbs>dir
Volume in drive C has no label.
Volume Serial Number is 18E4-B972

Directory of C:\oracle\product\10.2.0\db_1\dbs

24/12/2010 10:00 <DIR> .
24/12/2010 10:00 <DIR> ..
              0 File(s) 0 bytes
              2 Dir(s) 11,539,415,040 bytes free

C:\oracle\product\10.2.0\db_1\dbs>