How to Recover / Restore / Recreate Dropped Dual Table in Oracle? ORA-01775: looping chain of synonyms



DUAL table is owned by SYS schema and it can be dropped only by a DBA user. As a DBA you are not supposed to drop the DUAL table as it will affect the database badly. In case if the DUAL table gets dropped by mistake, you can follow below steps to recover the DUAL table and the database operations.


SQL> desc dual
 Name                                      Null?    Type
 ----------------------------------------- -------- --------
 DUMMY                                              VARCHAR2(1)

SQL> drop table dual;

Table dropped.

After dropping the table, if you try to access any pseudo information from the dual table you will end up with ORA-01775 error message.

SQL> select 1 from dual;
select 1 from dual
              *
ERROR at line 1:
ORA-01775: looping chain of synonyms


SQL>  select sysdate from dual;
 select sysdate from dual
                     *
ERROR at line 1:
ORA-01775: looping chain of synonyms

Alert Log file shows the following or similar messages.

/u01/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_j000_9242.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01775: looping chain of synonyms
ORA-12012: error on auto execute of job 4002
ORA-01775: looping chain of synonyms

Shutdown operation works as normal.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

But next normal startup will not succeed.
SQL> startup
ORACLE instance started.

Total System Global Area 1720328192 bytes
Fixed Size                  2227192 bytes
Variable Size            1308623880 bytes
Database Buffers          402653184 bytes
Redo Buffers                6823936 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01775: looping chain of synonyms
Process ID: 9371
Session ID: 125 Serial number: 5

Alert Content:
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_9371.trc:
ORA-01775: looping chain of synonyms
Error 1775 happened during db open, shutting down database
USER (ospid: 9371): terminating the instance due to error 1775
Instance terminated by USER, pid = 9371
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (9371) as a result of ORA-1092
Sat Dec 29 12:08:49 2012
ORA-1092 : opitsk aborting process

You have to startup the database in UPGRADE mode after setting the following parameter in the init file.
replication_dependency_tracking= false

You can startup the database in normal mode after setting the above parameter, but you will not be able to recreate the DUAL table. For recreating the table you have to open the database in UPGRADE mode.

SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/inittestdb.ora_UsedForRecovery';
ORACLE instance started.

Total System Global Area 1720328192 bytes
Fixed Size                  2227192 bytes
Variable Size            1308623880 bytes
Database Buffers          402653184 bytes
Redo Buffers                6823936 bytes
Database mounted.
Database opened.

Startup the database in upgrade mode and recreate the table.

SQL> startup upgrade pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/inittestdb.ora_UsedForRecovery';
ORACLE instance started.

Total System Global Area 1720328192 bytes
Fixed Size                  2227192 bytes
Variable Size            1308623880 bytes
Database Buffers          402653184 bytes
Redo Buffers                6823936 bytes
Database mounted.
Database opened.

DUAL table creation script:
  CREATE TABLE "SYS"."DUAL"
   ("DUMMY" VARCHAR2(1)) PCTFREE 10
  PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 16384 NEXT 1048576
  MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST
  GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
  DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"

SQL>   CREATE TABLE "SYS"."DUAL"
  2     (    "DUMMY" VARCHAR2(1)   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  3    STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  5    TABLESPACE "SYSTEM"
  6  ;

Table created.
SQL>  Insert Into Dual Values ('X');

1 row created.

SQL> commit;

Commit complete.

SQL> grant select on dual to public;

Grant succeeded.

SQL> select 1 from dual;

         1
----------
         1

SQL> select sysdate from dual;

SYSDATE
------------------
29-DEC-12

Perform a normal shutdown and startup with normal pfile or spfile.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1720328192 bytes
Fixed Size                  2227192 bytes
Variable Size            1308623880 bytes
Database Buffers          402653184 bytes
Redo Buffers                6823936 bytes
Database mounted.
Database opened.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google