advertisements
_____________________________________________________________________________________________________________________
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.
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment