gaotuan 发表于 2015-3-9 20:02:54

user 表空间 offline 后 就不能online 了

把user 表空间 offline   后   就不能online 了   
04:59:06 SYS@ ocat>select status,namefrom 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,namefrom 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 tablespaceusers online;
alter tablespaceusers 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 发表于 2015-3-9 20:05:19

Datafile Information

TS#FILE#FILE_SIZE_GSTATUSENABLEDCREATION_TIMESCNSTOP_SCNNAME
01.68359375SYSTEMREAD WRITE2014-07-20 02:36:192362686
+DATA/ocat/datafile/system.256.853382171
12.5859375ONLINEREAD WRITE2014-07-20 02:36:462362686
+DATA/ocat/datafile/sysaux.262.853382185
23.595703125ONLINEREAD WRITE2014-07-20 02:36:562362686
+DATA/ocat/datafile/undotbs1.263.853382209
44.137939453125OFFLINEDISABLED2014-07-20 02:37:2723630922363092+DATA/ocat/datafile/users.265.853382247

Elapsed: 00:00:00.01
Datafile Header Information


TS#FILE#TABLESPACE_NAMESTATUSERRORFORMATRECOVERFUZZYCREATE_TIMESCNRESETLOGS SCN
01SYSTEMONLINE 10NOYES2014-07-20 02:36:1923626861
12SYSAUXONLINE 10NOYES2014-07-20 02:36:4623626861
23UNDOTBS1ONLINE 10NOYES2014-07-20 02:36:5623626861
44USERSOFFLINE 10NONO2014-07-20 02:37:2723630921



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

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

xifenfei 发表于 2015-3-9 20:08:45

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 发表于 2015-3-9 20:09:20

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

xifenfei 发表于 2015-3-9 20:11:02

ASMCMD>ls -l +DATA/ocat/datafile/users.265.853382247
Type      RedundStripedTime             SysName
DATAFILEUNPROTCOARSE   MAR 09 05:00:00Y    users.265.853382247

gaotuan 发表于 2015-3-9 20:11:31

ASMCMD>ls -l +DATA/ocat/datafile/users.265.853382247
Type      RedundStripedTime             SysName
DATAFILEUNPROTCOARSE   MAR 09 05:00:00Y    users.265.853382247

gaotuan 发表于 2015-3-9 20:18:00


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 发表于 2015-3-9 21:09:00

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=2b88fa804f78bln=22avl=03flg=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=2b88fa700f88bln=2000avl=313flg=09
value="ERROR: DDL sequence [], marker sequence , DDL trace log file , 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 发表于 2015-3-9 21:26:30

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

广州-klaus 发表于 2015-3-9 21:30:03

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

:D为什么呢，飞总，解释原因
页: [1] 2
查看完整版本: user 表空间 offline 后 就不能online 了