Oracle表空间无法online, 原来是用户自定义了系统触发器

Getting Error  While Move Relocate （迁移）Datafiles ORA-00604 ORA-00376 ORA-01110 (Doc ID  1958189.1)

In this Document

APPLIES TO:

Oracle  Database - Enterprise Edition - Version 11.2.0.3 and later
 Information in this document applies to any platform.
 All datafiles got recovered and online by command 'alter database datafile  ... online;'
 But while making tablespace online it gives following error:

SYMPTOMS

Trying to relocate datafiles to a new file  system.

Unfortunately（不幸地）  some datafiles were rename incorrectly.

Now 2 tablespaces cannot be onlined, they  appear to be pointing to the same datafile:

 
 SQL> select tablespace_name from dba_tablespaces where status='OFFLINE';
 
 TABLESPACE_NAME
 ------------------------------
 APPS_TS_TOOLS
 APPS_TS_NOLOGGING
 
 

SQL> alter  tablespace APPS_TS_NOLOGGING online;
 alter tablespace APPS_TS_NOLOGGING online
 *
 ERROR at line 1:
 ORA-00604: error occurred at recursive SQL level 1
 ORA-00376: file 7 cannot be read at this time
 ORA-01110: data file 7: '/d02/oracle/vssdevdata/APPS_TS_TOOLS01.dbf'
 ORA-06512: at line 2
 
 
 SQL> alter tablespace APPS_TS_TOOLS online;
 alter tablespace APPS_TS_TOOLS online
 *
 ERROR at line 1:
 ORA-00604: error occurred at recursive SQL level 1
 ORA-00376: file 7 cannot be read at this time
 ORA-01110: data file 7: '/d02/oracle/vssdevdata/APPS_TS_TOOLS01.dbf'
 ORA-06512: at line 2

BEFORE   triger，所以---tablespace  offline时是可以的；

CAUSE

Application  user has created custom trigger on database.

CREATE OR  REPLACE TRIGGER  READONLYDBA.DDL_LOG_BCD_TRG
 BEFORE CREATE OR  ALTER OR DROP OR TRUNCATE
 ON DATABASE
 BEGIN
 INSERT INTO ddl_log
 SELECT
 ora_dict_obj_owner,
 ora_dict_obj_name,
 ora_sysevent,
 ora_dict_obj_type,
 USER,
 SYSDATE
 FROM
 dual;

Due to above trigger,before for every DDL  command gets complete above trigger gets invoked.

While  tablespace recovery, due to ALTER command above trigger gets invoked and it  is trying to insert record in ddl_log.

Since tablespace for ddl_log table was  offline, trigger execution getting fail and because of that Tablespace  recovery ( alter tablespace APPS_TS_NOLOGGING online) getting failed.

So for any method for making tablespace  online because of using ALTER command trigger gets invoked and which  generates error for single datafile which is involved in trigger.

Below code has bee identified from trace  file from 604 trace.

PARSING IN  CURSOR #47484096562840 len=237 dep=1 uid=328 oct=47 lid=328  tim=1419955943160751 hv=2607812560 ad='c2f87ce18' sqlid='0ypc526dr03yh'
 BEGIN
   INSERT INTO ddl_log
   SELECT
   ora_dict_obj_owner,
   ora_dict_obj_name,
   ora_sysevent,
   ora_dict_obj_type,
   USER,
   SYSDATE
   FROM
   dual;
 END ddl_log_bcd_trg;
 END OF STMT
 PARSE  #47484096562840:c=0,e=174,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=0,tim=1419955943160750
 
 *** 2014-12-30 08:12:23.660
 =====================
 PARSING IN CURSOR #47484096552120 len=122 dep=2 uid=328 oct=2 lid=328  tim=1419955943660391 hv=492817528 ad='c2f8522d8' sqlid='0cgtdnwfpzm3s'
 INSERT INTO DDL_LOG SELECT ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME,  ORA_SYSEVENT, ORA_DICT_OBJ_TYPE, USER, SYSDATE FROM DUAL
 END OF STMT

SOLUTION

Solution is to invalidate or disable trigger without executing  any ddl (ALTER ..) command.

Used below parameter to disable system  trigger

alter  system set "_system_trig_enabled"=FALSE;

  
 
 After that execute below command to online the Tablespace, which is  successful now

sql> alter tablespace APPS_TS_TOOLS  online;
 
 sql> -- After that make default  behaviour for  _system_trig_enabled as :
 
 sql> alter system set "_system_trig_enabled"=TRUE;

REFERENCES

NOTE:160705.1 - ORA-00376 and ORA-01110 Database File  Unavailable