ORA-01075

I tried to log on to an Oracle 11.2 test database, which the developers could no longer use, and saw the following error:

MDMDEV1 /export/home/oracle > sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 28 09:41:24 2014

 

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

 

ERROR:

ORA-01075: you are currently logged on

 

Enter user-name:

I looked in the alert log and saw several of the following messages:

Fri Mar 28 09:36:46 2014

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Errors in file /oracle/app/oracle/product/diag/rdbms/mdmdev1/MDMDEV1/trace/MDMDEV1_smon_522.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","select count(*) from edition$","sga heap(1,0)","kglsim object batch")

I closed the database like this:

MDMDEV1 /export/home/oracle > sqlplus -prelim

 

SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 28 09:45:45 2014

 

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

 

Enter user-name: / as sysdba

SQL> shutdown abort

ORACLE instance shut down.

SQL>

Then I opened the database again:

MDMDEV1 /export/home/oracle > sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 28 09:49:39 2014

 

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

 

Connected to an idle instance.

 

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

 

Total System Global Area  534462464 bytes

Fixed Size                  2225832 bytes

Variable Size             390072664 bytes

Database Buffers          134217728 bytes

Redo Buffers                7946240 bytes

Database mounted.

Database opened.

SQL> 

... and the developers could login again.

ORA-00604 and ORA-00001

This happened in an Oracle 11.1.0.6.0 database:

 

A developer reported problems when running a CREATE OR REPLACE TYPE statement in a development database. It was failing with an ORA-00604 followed by an ORA-00001. These messages could be seen again and again in the alert log:

ORA-00604: error occurred at recursive SQL level 3

ORA-00001: unique constraint (SYS.I_OBJ1) violated

I could see no reason for this so I did some research on the Internet then suggested that the data dictionary might be corrupted. This was quite possible as a number of people have the SYSTEM password for the database in question so somebody could easily have done something silly with it.

 

A second colleague reported the same errors when compiling packages and dropping and creating global temporary tables. Another DBA looked at the problem this time. He tried to recompile all invalid objects in the database and noticed that the compilation of one procedure displayed several errors before failing with a core dump. He amended the procedure a bit at a time to fix the errors until the compilation no longer caused a core dump. He then purged the recycle bin and asked the 2^nd developer to try again. He reported that the problem had gone away.

 

I reran the CREATE OR REPLACE TYPE statement provided by the 1^st developer and it worked too.  

ORA-04098

I noticed this recently on an Oracle 11.1.0.6.0 database.

 

If you create a table, add a trigger then add a column, it all works:

 

SQL> create table tab1 (col1 varchar2(1))

  2  /

 

Table created.

 

SQL> create trigger trig1

  2  after update on tab1

  3  begin

  4  null;

  5  end;

  6  /

 

Trigger created.

 

SQL> alter table tab1

  2  add col2 varchar2(1)

  3  /

 

Table altered.

 

SQL> desc tab1

Name                       Null?    Type

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

COL1                                VARCHAR2(1)

COL2                                VARCHAR2(1)

 

SQL>

 

If the trigger is invalid (in this case because there is no semi-colon at the end of the null statement), you get an error when you try to create it but you can still add the column:

 

SQL> create table tab1 (col1 varchar2(1))

  2  /

 

Table created.

 

SQL> create trigger trig1

  2  after update on tab1

  3  begin

  4  null

  5  end;

  6  /

 

Warning: Trigger created with compilation errors.

 

SQL> alter table tab1

  2  add col2 varchar2(1)

  3  /

 

Table altered.

 

SQL> desc tab1

Name                       Null?    Type

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

COL1                                VARCHAR2(1)

COL2                                VARCHAR2(1)

 

SQL>

 

However, if you include a default clause when you add the column, you get an ORA-04098 instead and the column is not added to the table:

 

SQL> create table tab1 (col1 varchar2(1))

  2  /

 

Table created.

 

SQL> create trigger trig1

  2  after update on tab1

  3  begin

  4  null

  5  end;

  6  /

 

Warning: Trigger created with compilation errors.

 

SQL> alter table tab1

  2  add col2 varchar2(1) default 'Y'

  3  /

alter table tab1

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-04098: trigger 'ANDREW.TRIG1' is invalid and

failed re-validation

 

SQL> desc tab1

Name                       Null?    Type

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

COL1                                VARCHAR2(1)

 

SQL>

 

Of course, if the trigger is valid, the column gets added even with the default clause:

 

SQL> create table tab1 (col1 varchar2(1))

  2  /

 

Table created.

 

SQL> create trigger trig1

  2  after update on tab1

  3  begin

  4  null;

  5  end;

  6  /

 

Trigger created.

 

SQL> alter table tab1

  2  add col2 varchar2(1) default 'Y'

  3  /

 

Table altered.

 

SQL> desc tab1

Name                       Null?    Type

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

COL1                                VARCHAR2(1)

COL2                                VARCHAR2(1)

 

SQL>

ORA-00604, ORA-04092, ORA-06512 and ORA-00942

I saw an ORA-00604 in a database recently and decided to see what might have caused it. The test below was run in an Oracle 9 database and shows one possible reason for an ORA-00604. Strangely enough, when I repeated the test in Oracle 10 and Oracle 11 databases, the ORA-00604 did not appear. If / when I find out why, I will update this post accordingly. First I ensured that _system_trig_enabled was set to true. You will see why at the end:

 

SQL> conn / as sysdba

Connected.

SQL> alter system

  2  set "_system_trig_enabled"=TRUE

  3  /

 

System altered.

 

SQL>

 

Then I created a user:

SQL> grant create session to andrew

  2  identified by reid

  3  /

 

Grant succeeded.

 

SQL>

 

The user looked for a table which did not exist and got an ORA-00942 (as you do):

 

SQL> conn andrew/reid

Connected.

SQL> select * from tab1

  2  /

select * from tab1

              *

ERROR at line 1:

ORA-00942: table or view does not exist

 

SQL>

 

Next I created a user to own a trigger. This trigger is to fire on an after servererror on database event, write an error message to a table and commit it. To do this the user needs create trigger and administer database trigger privileges:  

 

SQL> conn / as sysdba

Connected.

SQL> create user fred identified by bloggs

  2  default tablespace users

  3  quota unlimited on users

  4  /

 

User created.

 

SQL> grant create session,

  2  create table,

  3  create trigger,

  4  administer database trigger

  5  to fred

  6  /

 

Grant succeeded.

 

SQL>

 

SQL> conn fred/bloggs

Connected.

SQL> create table failure_log

  2  (message varchar2(40))

  3  /

 

Table created.

 

SQL> create or replace trigger error_trigger

  2  after servererror on database

  3  begin

  4    insert into failure_log

  5    values('We have a problem');

  6    commit;

  7  end;

  8  /

 

Trigger created.

 

SQL>

 

You can identify these triggers as they have a trigger_type of AFTER EVENT:

 

SQL> select trigger_name, trigger_type

  2  from user_triggers

  3  /

 

TRIGGER_NAME         TRIGGER_TYPE

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

ERROR_TRIGGER        AFTER EVENT

 

SQL>

 

The next time Andrew tried to look for the table which did not exist, he got an ORA-00604 and an ORA-04092 as well because Fred’s trigger tried to do a commit:

 

SQL> conn andrew/reid

Connected.

SQL> select * from tab1

  2  /

select * from tab1

              *

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-04092: cannot COMMIT in a trigger

ORA-06512: at line 4

ORA-00942: table or view does not exist

 

SQL>

 

I set _system_trig_enabled to false to stop Fred’s trigger firing:

 

SQL> conn / as sysdba

Connected.

SQL> alter system

  2  set "_system_trig_enabled"=FALSE

  3  /

 

System altered.

 

SQL>

 

So the next time Andrew looked for the missing table, he just got an ORA-00942:

 

SQL> conn andrew/reid

Connected.

SQL> select * from tab1

  2  /

select * from tab1

              *

ERROR at line 1:

ORA-00942: table or view does not exist

 

SQL> 

ORA-02002

This was tested on Oracle 11.2.0.2.7. I wondered what would happen if you were auditing connections, SYS.AUD$ filled up then you tried to login again. First I started auditing: 

SQL> conn / as sysdba

Connected.

SQL> show user

USER is "SYS"

SQL> audit create session

  2  /

 

Audit succeeded.

 

SQL> select value from v$parameter

  2  where name = 'audit_trail'

  3  /

 

VALUE

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

TRUE

 

SQL>

Then I checked that auditing was working: 

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

  2  /

 

  COUNT(*)

----------

         0

 

SQL> conn andrew/reid

Connected.

SQL> show user

USER is "ANDREW"

SQL> conn / as sysdba

Connected.

SQL> show user

USER is "SYS"

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

  2  /

 

  COUNT(*)

----------

         2

 

SQL>

Next I looked at the current and maximum possible size of SYS.AUD$: 

SQL> select count(*) from dba_extents

  2  where owner = 'SYS'

  3  and segment_name = 'AUD$'

  4  /

 

  COUNT(*)

----------

         1

 

SQL> select next_extent, max_extents

  2  from dba_tables

  3  where owner = 'SYS'

  4  and table_name = 'AUD$'

  5  /

 

NEXT_EXTENT MAX_EXTENTS

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

    1048576  2147483645

 

SQL>

 

I tried to limit the maximum possible size of SYS.AUD$. I found that I could adjust the size of the next extent: 

SQL> alter table sys.aud$ storage (next 8k)

  2  /

 

Table altered.

 

SQL>

... but not the maximum number of extents: 

SQL> alter table sys.aud$ storage (maxextents 10)

  2  /

alter table sys.aud$ storage (maxextents 10)

*

ERROR at line 1:

ORA-25150: ALTERING of extent parameters not permitted

 

SQL>

So the only way to make SYS.AUD$ run out of space is to set a limit on the size of the tablespace it is in. To make this easier, I moved SYS.AUD$ to the USERS tablespace, which has 2 megabytes and autoextend off in this database: 

SQL> alter table sys.aud$ move tablespace users

  2  /

 

Table altered.

 

SQL>

Then I set up a loop in a UNIX shell script to connect to the database again and again. Each iteration connected as ANDREW first then as SYS. It started with a count of 1: 

$count is 1

 

SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 16 18:37:44 2012

 

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

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> USER is "ANDREW"

SQL> Connected.

SQL> USER is "SYS"

SQL>   2

AUDIT_ROWS

----------

         4

 

SQL>   2    3    4

LOGONS_CUMULATIVE

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

             4505

 

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Connection as user ANDREW failed with ORA-02002 when $count reached 1986: 

$count is 1986

 

SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 16 18:51:36 2012

 

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

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> USER is "ANDREW"

SQL> ERROR:

ORA-02002: error while writing to audit trail

ORA-00604: error occurred at recursive SQL level 1

ORA-01653: unable to extend table SYS.AUD$ by 8 in tablespace USERS

 

Connected.

SQL> USER is "SYS"

SQL>   2

AUDIT_ROWS

----------

      3973

 

SQL>   2    3    4

LOGONS_CUMULATIVE

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

             8484

 

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

I was still able to connect as SYS so, if I had wanted to continue using the database I could have deleted rows from SYS.AUD$, enlarged the USERS tablespace or simply stopped auditing. 

Reply to: Reply to Andrew Reid

Send

How to Open a Database in READ ONLY Mode

This was tested on Oracle 11.2.0.2.7. You can open a database in READ ONLY mode as follows: 

SQL> startup open read only;

ORACLE instance started.

 

Total System Global Area  522092544 bytes

Fixed Size                  2159904 bytes

Variable Size             314575584 bytes

Database Buffers          197132288 bytes

Redo Buffers                8224768 bytes

Database mounted.

Database opened.

SQL>

 

You can check how a database was opened like this:

 

SQL> select open_mode from v$database;

 

OPEN_MODE

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

READ ONLY

 

SQL>

 

When a database is open in READ ONLY mode, you cannot create tables in it:

 

SQL> create table andrews_table (col1 number);

create table andrews_table (col1 number)

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-16000: database open for read-only access

 

SQL>

 

You cannot update tables either:

 

SQL> insert into another_table

  2  select * from another_table;

insert into another_table

            *

ERROR at line 1:

ORA-16000: database open for read-only access

 

SQL> update another_table

  2  set col1 = 2;

update another_table

       *

ERROR at line 1:

ORA-16000: database open for read-only access

 

SQL> delete another_table;

delete another_table

       *

ERROR at line 1:

ORA-16000: database open for read-only access

 

SQL>

 

You cannot even drop a table. I wanted to try this out because, strangely enough, you CAN drop tables from READ ONLY tablespaces:

 

SQL> drop table another_table;

drop table another_table

*

ERROR at line 1:

ORA-16000: database open for read-only access

 

SQL>

 

Bouncing the database returns it to READ WRITE mode:

 

SQL> startup force

ORACLE instance started.

 

Total System Global Area  522092544 bytes

Fixed Size                  2159904 bytes

Variable Size             314575584 bytes

Database Buffers          197132288 bytes

Redo Buffers                8224768 bytes

Database mounted.

Database opened.

SQL> select open_mode from v$database;

 

OPEN_MODE

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

READ WRITE

 

SQL>

ORA-30036

I was recently using an Oracle 9 database. It had an undo tablespace with 1 datafile, which was set to autoextend. However, it could not do so as the file system it was on was full. This produced an ORA-30036 error. I decided to demonstrate this on Oracle 11.1.0.6.0 running on Windows XP. First I created a 500k undo tablespace. By the time I ran the SQL below, it had grown to 647168 bytes:

SQL> l
  1  select file_name, bytes, autoextensible
  2  from dba_data_files
  3* where tablespace_name = 'UNDOTBS2'
SQL> /

FILE_NAME                 BYTES AUT
-------------------- ---------- ---
A:\UNDOTBS2.DBF          647168 YES

SQL>

Notice that it is on the A: drive. This is the 3.5” floppy disk drive (remember them?). It contained a 720k disk. I did this on purpose to stop the undo tablespace growing any bigger than that. I told the database to use this new undo tablespace:

SQL> alter system set undo_tablespace = undotbs2;

System altered.

SQL>

Then I tried to create a table but Oracle returned an ORA-30036:

SQL> create table fred
  2  as select * from dba_tables
  3  /
as select * from dba_tables
                 *
ERROR at line 2:
ORA-00604: error occurred at recursive SQL level 1
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'

SQL>

The following errors appeared in the alert log:

Sun Sep 09 23:43:01 2012
GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file c:\app\andrew\diag\rdbms\orcl\orcl\trace\orcl_j003_2916.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'
Sun Sep 09 23:44:48 2012
Thread 1 advanced to log sequence 37
  Current log# 1 seq# 37 mem# 0: C:\APP\ANDREW\ORADATA\ORCL\REDO01.LOG
Mon Sep 10 00:06:16 2012
ORA-1652: unable to extend temp segment by 8 in tablespace                 UNDOTBS2 
Mon Sep 10 00:13:05 2012

And when I looked at the floppy disk in Windows, it was full:

If this happens to you, you need to add an extra datafile to the undo tablespace (on a file system with enough free space). Alternatively, you can extend the file system, although in this case it was not possible as a floppy disk has a finite size.