Today I got a new
problem in one of our 3 node cluster
database .During rman cold backup my job was failing -
+ [ -z '' ]
+
page_msg=abended
+ [ -z 'RMAN- or
ORA- errors encountered. Please check
audit log' ]
--
Above it's clear
that something wrong with datafile numbre
236.
using v$recover_file checked datafile status.
SQL> select *
from v$recover_file where error like '%FILE%';
FILE# ONLINE
ONLINE_ ERROR
CHANGE# TIME
----------
------- -------
----------------------------------------------------------------- ----------
---------------
236 OFFLINE OFFLINE FILE NOT FOUND
0
-- Above is the key point "OFFLINE"
SQL> select
file#,name from v$datafile where file#=236;
FILE#
----------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
236
/d001/product/oracle/11.2.0.2/db/dbs/SHARED_DATA
SQL> show
parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
db_create_file_dest string +SHARED_DATA
As db_create_file_dest define as +SHARED_DATA ,
it shows something wrong happen during adding this datafile. common cause is
while add datafile if we given space between "add datafile ' +vale of
db_create_file_dest' this way datafile
will create inside $ORACLE_HOME/dbs'. seem same happen here for this db also .
Let's fix this
now .
SQL> select file#,name from v$datafile where
file#=236;
FILE#
----------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
236
/d001/product/oracle/11.2.0.2/db/dbs/SHARED_DATA
alter database
create datafile '/d001/product/oracle/11.2.0.2/db/dbs/SHARED_DATA' as
'+SHARED_DATA'
SQL> show
parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
db_create_file_dest string +SHARED_DATA
SQL>
SQL> alter
database create datafile '/d001/product/oracle/11.2.0.2/db/dbs/SHARED_DATA' as
'+SHARED_DATA'
2 ;
alter database
create datafile '/d001/product/oracle/11.2.0.2/db/dbs/SHARED_DATA' as
'+SHARED_DATA'
*
ERROR at line 1:
ORA-01136:
specified size of file 236 (12800 blocks) is less than original size of 131072
blocks
ORA-01110: data
file 236: '/d001/product/oracle/11.2.0.2/db/dbs/SHARED_DATA'
No problem .
SQL> alter
database create datafile '/d001/product/oracle/11.2.0.2/db/dbs/SHARED_DATA' as
new;
Database altered.
SQL> select
file#,name from v$datafile where file#=236;
FILE#
----------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
236
+SHARED_DATA/XXXX/datafile/otd.1475.832988265
Hello vinod,
ReplyDeleteThanks for this helpful post. you saved me from a lot of hassle. BTW when i faced this error, i opened SR with oracle after googling around. But it was not much of a help. Thanks again.