非归档，os层面删除ogg数据文件后drop tablespace

--问题描述：
刘总反映开发库宕机了，空间满了。


sqlplus / as sysdba 登录提示：


SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 13 14:15:57 2014


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


@>ERROR:
ORA-00020: maximum number of processes (550) exceeded




@>ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
@>Database instance "scard" shut down.


--查看alert日志如下：
[oracle@scard trace]$ less alert_scard.log
Sat Sep 13 13:36:56 2014
Errors in file /u01/app/oracle/diag/rdbms/scard/scard/trace/scard_pmon_2227.trc:
ORA-04030: out of process memory when trying to allocate 832 bytes (callheap,temporary memory)
......


--查找网络连接oracle的进程
ps -ef|grep ora|grep LOCAL


kill -9  xxx
杀掉local=no的进程后，还是sqlplus / as sysdba 登录不上。


--初级连接方式，强制shut
sqlplus -prelim / as sysdba
SYS@scard>shut abort


--至此，超出最大进程问题解决了。


在os层面 rm 掉了 ogg 的表空间，并shut abort了库。
因此，lsof 方法已无法找回改数据文件，句柄已释放。开发库又是非归档模式。


SYS@scard>startup
ORACLE instance started.


Total System Global Area 9486905344 bytes
Fixed Size                  2215624 bytes
Variable Size            4697620792 bytes
Database Buffers         4764729344 bytes
Redo Buffers               22339584 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 70 - see DBWR trace file
ORA-01110: data file 70: '/u01/app/oracle/oradata/scard/ogg.dbf'




SYS@scard>select status from v$instance;


STATUS
------------
MOUNTED


--无法恢复，ogg 的表空间，不要了
alter database datafile '/u01/app/oracle/oradata/scard/ogg.dbf' offline drop;
alter database open;


SYS@scard>drop tablespace ogg_tbs including contents and datafiles;
drop tablespace ogg_tbs including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 70 cannot be read at this time
ORA-01110: data file 70: '/u01/app/oracle/oradata/scard/ogg.dbf'
ORA-06512: at line 999
ORA-00376: file 70 cannot be read at this time
ORA-01110: data file 70: '/u01/app/oracle/oradata/scard/ogg.dbf'


--select其他数据文件上的数据没问题。
SYS@scard>select * from scott.emp;


     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        888                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30


14 rows selected.


--任何ddl都报错
SYS@scard>alter table ijx130355.T_KAOQIN_STUDENT_HIS truncate partition PT_KQ_STU_20140816
                      *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 70 cannot be read at this time
ORA-01110: data file 70: '/u01/app/oracle/oradata/scard/ogg.dbf'
ORA-06512: at line 999
ORA-00376: file 70 cannot be read at this time
ORA-01110: data file 70: '/u01/app/oracle/oradata/scard/ogg.dbf'


--开启10046追踪
alter session set tracefile_identifier='10046';
alter session set sql_trace=true;
alter session set events '10046 trace name context forever,level 12';
select ename from scott.emp where deptno=10;
alter session set events '10046 trace name context off';
alter session set sql_trace=false;


--找到trace文件
 select c.value||'/'||d.instance_name||'_ora_'||a.spid||'.trc' trace_name_file 
 from v$process a,v$session b,v$parameter c,v$instance d
 where a.addr=b.paddr
 and b.audsid=userenv('sessionid')
 and c.name='user_dump_dest';


--查看trace文件
less /u01/app/oracle/diag/rdbms/scard/scard/trace/scard_ora_17995_10046.trc 
 
--经惜分飞帮忙分析10046文件,查找ORA- ，发现如下错误
PARSING IN CURSOR #2 len=174 dep=2 uid=0 oct=2 lid=0 tim=1410704188173966 hv=3733968023 ad='28fc045b8' sqlid='89v1217g8zp4r'
INSERT INTO "OGG" . "GGS_MARKER" ( SEQNO, FRAGMENTNO, OPTIME, TYPE, SUBTYPE, MARKER_TEXT ) VALUES ( :B2 , 0, TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'), 'DDL', 'DDLINFO', :B1 )
END OF STMT
PARSE #2:c=0,e=118,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,plh=0,tim=1410704188173966
BINDS #2:
 Bind#0
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fab36128fa8  bln=22  avl=05  flg=05
  value=1278325
 Bind#1
  oacdty=01 mxl=2000(404) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=206001 frm=01 csi=852 siz=2000 off=0
  kxsbbbfp=7fab3624dde8  bln=2000  avl=404  flg=09
  value="ERROR: DDL sequence [], marker sequence [1278325], DDL trace log file [/ggs_ddl_trace.log], error code -376 error message ORA-00376: file 70 cannot be read at this time
ORA-01111: name for data file 70 is unknown - rename to correct file
ORA-01110: data f"...
 
--提示跟ogg的ddl触发器有关。删除相关触发器
SYS@scard>drop trigger GGS_DDL_TRIGGER_BEFORE;
SYS@scard>drop package DDLCtxInfo;
SYS@scard>drop tablespace ogg_tbs including contents and datafiles;


Tablespace dropped.


--ddl操作也恢复正常。