ANBOB™

My case is 2 nodes 10.2.0.5 RAC on aix,The previous we do move all objects on a tablespace to new tablespace For reasons of space Fragment , and then to rename the new tablespace to the original tablespace , and alter the original tablespace offline. no errors during operation, and there is no attention to use it.and crontab and oracle job not use the offline tablespace, But ORA-01135  ORA-01110 error in the alert.log file I keep seeing the message every 10 minutes:

This case is very similar to the symptoms of OTN ‘

# alert log on node2 #
——————————————
Wed Nov 26 11:43:36 BEIST 2014
Errors in file /oracle/app/oracle/admin/anbob/bdump/anbob2_m000_10945212.trc:
ORA-01135: file 377 accessed for DML/query is offline
ORA-01110: data file 377: ‘/dev/rzwa_lv30_218’
Wed Nov 26 11:43:36 BEIST 2014
Errors in file /oracle/app/oracle/admin/anbob/bdump/anbob2_m000_10945212.trc:
ORA-01135: file 380 accessed for DML/query is offline
ORA-01110: data file 380: ‘/dev/rzwa_lv15_169’
Wed Nov 26 11:43:36 BEIST 2014
…
Wed Nov 26 11:53:36 BEIST 2014
Errors in file /oracle/app/oracle/admin/anbob/bdump/anbob2_m000_17826222.trc:
ORA-01135: file 72 accessed for DML/query is offline
ORA-01110: data file 72: ‘/dev/rzwa_lv30_218’
Wed Nov 26 11:53:36 BEIST 2014
Errors in file /oracle/app/oracle/admin/anbob/bdump/anbob2_m000_17826222.trc:
ORA-01135: file 80 accessed for DML/query is offline
ORA-01110: data file 80: ‘/dev/rzwa_lv15_169’
Wed Nov 26 11:53:36 BEIST 2014
…
Wed Nov 26 12:03:36 BEIST 2014
Errors in file /oracle/app/oracle/admin/anbob/bdump/anbob2_m000_21497336.trc:
ORA-01135: file 113 accessed for DML/query is offline
ORA-01110: data file 113: ‘/dev/rzwa_lv30_218’
Wed Nov 26 12:03:36 BEIST 2014
Errors in file /oracle/app/oracle/admin/anbob/bdump/anbob2_m000_21497336.trc:
ORA-01135: file 131 accessed for DML/query is offline
ORA-01110: data file 131: ‘/dev/rzwa_lv15_169’
Wed Nov 26 12:03:36 BEIST 2014

# m000 trace file
——————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle/app/oracle/product/10.2.0
System name: AIX
Node name: kdzwa2
Release: 1
Version: 6
Machine: 00F80C614C00
Instance name: accta2
Redo thread mounted by this instance: 2
Oracle process number: 784
Unix process pid: 13238868, image: oracle@kdzwa2 (m000)

*** ACTION NAME:(Monitor Tablespace Thresholds) 2014-11-28 09:34:08.821
*** MODULE NAME:(MMON_SLAVE) 2014-11-28 09:34:08.821
*** SERVICE NAME:(SYS$BACKGROUND) 2014-11-28 09:34:08.821
*** SESSION ID:(3101.39170) 2014-11-28 09:34:08.821
ORA-01135: file 7 accessed for DML/query is offline
ORA-01110: data file 7: ‘/dev/rzwa_lv15_001’
ORA-01135: file 72 accessed for DML/query is offline
ORA-01110: data file 72: ‘/dev/rzwa_lv30_041’
ORA-01135: file 80 accessed for DML/query is offline

Tip:
the datafiles are part of the tablespace offline.

# no error in alert log on node1

An ORA-01135 is issued whenever Oracle attempts to access a datafile to execute a DML statement but finds out that the file is offline.

To run the below sql script on both note,To check the two nodes query results are consistent.
select ts#,name from ts$ where online$=3;

select inst_id,tablespace_id from gv$filespace_usage,ts$
where tablespace_id in (3);

and I take a errorstack event:

sqlplus / as sysdba
oradebug setmypid
alter system set events  '1135 trace name errorstack level 3';

-- wait 20 minutes and new error ora-0113  show in alert

oradebug eventdump system
alter system set events  '1135 trace name errorstack off';
oradebug tracefile_name
oradebug eventdump system

Tip:
but above show trace file not any information such as call sql or call stack.

An ORA-01135 is generally caused by:

– A DML statement being executed against a datafile that has been offlined
manually or by Oracle itself because of an I/O error.

– A rollback segment datafile going offline with active transactions in it.

– Prior to 7.1.5, bug:222852 might cause ORA-01135 on dropping a tablespace
with referential integrity constraints or disabling a foreign key constraint.

Solution:

Try to modify the offline tablespace online again or drop;

alter tablespace xx online;
-- alter tablespace xx read only;

I do that and ora-01135 error no longer write in the alert log.

打赏

微信扫一扫，打赏作者吧～