user 表空间 offline 后 就不能online 了

gaotuan

把user 表空间 offline   后   就不能online 了   
04:59:06 SYS@ ocat>select status,name  from V$datafile；
STATUS                NAME
--------------------- ------------------------------
SYSTEM                +DATA/ocat/datafile/system.256
                      .853382171

ONLINE                +DATA/ocat/datafile/sysaux.262
                      .853382185

ONLINE                +DATA/ocat/datafile/undotbs1.2
                      63.853382209

OFFLINE               +DATA/ocat/datafile/users.265.
                      853382247


Elapsed: 00:00:00.01
04:59:15 SYS@ ocat>alter database datafile 4 online;

Database altered.

Elapsed: 00:00:00.24
04:59:33 SYS@ ocat>select status,name  from V$datafile；
STATUS                NAME
--------------------- ------------------------------
SYSTEM                +DATA/ocat/datafile/system.256
                      .853382171

ONLINE                +DATA/ocat/datafile/sysaux.262
                      .853382185

ONLINE                +DATA/ocat/datafile/undotbs1.2
                      63.853382209

ONLINE                +DATA/ocat/datafile/users.265.
                      853382247




04:57:36 SYS@ ocat>alter tablespace  users online;
alter tablespace  users online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '+DATA/ocat/datafile/users.265.853382247'
ORA-06512: at line 999
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '+DATA/ocat/datafile/users.265.853382247'

xifenfei

Datafile Information

---

TS#	FILE#	FILE_SIZE_G	STATUS	ENABLED	CREATION_TIME	SCN	STOP_SCN	NAME
0	1	.68359375	SYSTEM	READ WRITE	2014-07-20 02:36:19	2362686		+DATA/ocat/datafile/system.256.853382171
1	2	.5859375	ONLINE	READ WRITE	2014-07-20 02:36:46	2362686		+DATA/ocat/datafile/sysaux.262.853382185
2	3	.595703125	ONLINE	READ WRITE	2014-07-20 02:36:56	2362686		+DATA/ocat/datafile/undotbs1.263.853382209
4	4	.137939453125	OFFLINE	DISABLED	2014-07-20 02:37:27	2363092	2363092	+DATA/ocat/datafile/users.265.853382247

[size=13.3333330154419px]Elapsed: 00:00:00.01

Datafile Header Information

---

TS#	FILE#	TABLESPACE_NAME	STATUS	ERROR	FORMAT	RECOVER	FUZZY	CREATE_TIME	SCN	RESETLOGS SCN
0	1	SYSTEM	ONLINE		10	NO	YES	2014-07-20 02:36:19	2362686	1
1	2	SYSAUX	ONLINE		10	NO	YES	2014-07-20 02:36:46	2362686	1
2	3	UNDOTBS1	ONLINE		10	NO	YES	2014-07-20 02:36:56	2362686	1
4	4	USERS	OFFLINE		10	NO	NO	2014-07-20 02:37:27	2363092	1

通过Oracle Database Recovery Check检查的结果，发现，users表空间和数据文件都offline，需要解决该问题，应该是先online数据文件，然后online 表空间

建议执行
recover datafile 4;
alter database datafile 4 online;
alter tablespace users online;

xifenfei

05:05:01 SYS@ ocat>recover datafile 4;
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 4 - file is in use or recovery
ORA-01110: data file 4: '+DATA/ocat/datafile/users.265.853382247'


05:05:02 SYS@ ocat>
05:05:17 SYS@ ocat>
05:05:17 SYS@ ocat>
05:05:17 SYS@ ocat>
05:05:18 SYS@ ocat>alter database datafile 4 offline;

Database altered.

Elapsed: 00:00:00.40
05:05:33 SYS@ ocat>recover datafile 4;
Media recovery complete.
05:05:38 SYS@ ocat>alter database datafile 4 online;

Database altered.

Elapsed: 00:00:00.07
05:05:48 SYS@ ocat>alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '+DATA/ocat/datafile/users.265.853382247'
ORA-06512: at line 999
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '+DATA/ocat/datafile/users.265.853382247'

xifenfei

su - grid
asmcmd ls -l +DATA/ocat/datafile/users.265.853382247

xifenfei

ASMCMD>  ls -l +DATA/ocat/datafile/users.265.853382247
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   MAR 09 05:00:00  Y    users.265.853382247

gaotuan

ASMCMD>  ls -l +DATA/ocat/datafile/users.265.853382247
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   MAR 09 05:00:00  Y    users.265.853382247

gaotuan

05:14:11 SYS@ ocat>select status ,name from V$datafile;

STATUS
---------------------
NAME
--------------------------------------------------------
--------------------------------------------------------
SYSTEM
+DATA/ocat/datafile/system.256.853382171

ONLINE
+DATA/ocat/datafile/sysaux.262.853382185

ONLINE
+DATA/ocat/datafile/undotbs1.263.853382209

ONLINE
+DATA/ocat/datafile/users.265.853382247

xifenfei

PARSING IN CURSOR #47867318434136 len=174 dep=2 uid=0 oct=2 lid=0 tim=1425904170192341 hv=3733968023 ad='6d2bde48' 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 #47867318434136:c=999,e=97,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=0,tim=1425904170192340
BINDS #47867318434136:
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=2b88fa804f78  bln=22  avl=03  flg=05
  value=9013
Bind#1
  oacdty=01 mxl=2000(313) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=206001 frm=01 csi=852 siz=2000 off=0
  kxsbbbfp=2b88fa700f88  bln=2000  avl=313  flg=09
  value="ERROR: DDL sequence [], marker sequence [9013], DDL trace log file [/ggs_ddl_trace.log], error code -376 error message ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '+DATA/ocat/datafile/users.265.853382247', error stack: ORA-06512:"...
DDE: Problem Key 'ORA 1110' was flood controlled (0x5) (no incident)
ORA-01110: data file 4: '+DATA/ocat/datafile/users.265.853382247'
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '+DATA/ocat/datafile/users.265.853382247'
EXEC #47867318434136:c=2000,e=12602,p=0,cr=0,cu=1,mis=0,r=0,dep=2,og=1,plh=0,tim=1425904170204977
ERROR #47867318434136:err=376 tim=1425904170204999
STAT #47867318434136 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  (cr=0 pr=0 pw=0 time=16 us)'
CLOSE #47867318434136:c=0,e=5,dep=2,type=0,tim=1425904170205115
EXEC #47867317175264:c=64990,e=589052,p=5,cr=295,cu=4,mis=0,r=0,dep=1,og=4,plh=0,tim=1425904170205158
ERROR #47867317175264:err=376 tim=1425904170205242
CLOSE #47867317175264:c=0,e=22,dep=1,type=1,tim=1425904170205356
EXEC #47867317153336:c=67989,e=593372,p=5,cr=295,cu=6,mis=0,r=0,dep=0,og=1,plh=0,tim=1425904170205380
ERROR #47867317153336:err=604 tim=1425904170205390
WAIT #47867317153336: nam='log file sync' ela= 7391 buffer#=1386 sync scn=2364946 p3=0 obj#=-1 tim=1425904170213120
WAIT #47867317153336: nam='SQL*Net break/reset to client' ela= 26 driver id=1650815232 break?=1 p3=0 obj#=-1 tim=1425904170213237
WAIT #47867317153336: nam='SQL*Net break/reset to client' ela= 535 driver id=1650815232 break?=0 p3=0 obj#=-1 tim=1425904170213784
WAIT #47867317153336: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1425904170213832


明显ogg的捕获ddl启用导致，建议disable ogg ddl，然后尝试online tbs

gaotuan

将ogg   disable ddl  后   表空间成功online，多谢飞总。

广州-klaus

xifenfei 发表于 2015-3-9 21:09
PARSING IN CURSOR #47867318434136 len=174 dep=2 uid=0 oct=2 lid=0 tim=1425904170192341 hv=3733968023 ...

为什么呢，飞总，解释原因