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

In this DocumentSymptoms

Cause

Solution

References

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

BEFOREtriger，所以---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