How
to resolve ORA-01111, ORA-01110, ORA-01157
---Standby Case if any updation on primary site like DBF then throw this error...
There
are many reasons for a file being created as UNNAMED or MISSING in the standby
database, including insufficient disk space on standby site (or) Improper
parameter settings related to file management.
STANDBY_FILE_MANAGEMENT
enables or disables automatic standby file management. When automatic standby
file management is enabled, operating system file additions and deletions on
the primary database are replicated on the standby database.
For
example if we add a data file on the Primary when parameter
STANDBY_FILE_MANAGEMENT on standby set to MANUAL , While recovery
process(MRP) is trying to apply archives, Due to that parameter setting it will
create an Unnamed file in $ORACLE_HOME/dbs and it will cause to kill MRP
process and Errors will be as below.
Errors From Alert Log file:-
Errors in file
/oracle/app/oracle/admin/CC/bdump/cc_mrp0_22294.trc:
ORA-01111: name for data
file 536 is unknown - rename to correct file
ORA-01110: data file 536:
'/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536'
ORA-01157: cannot
identify/lock data file 536 - see DBWR trace file
ORA-01111: name for data
file 536 is unknown - rename to correct file
ORA-01110: data file 536:
'/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536'
Trace File:-
MRP0: Background Media
Recovery terminated with error 1111
ORA-01111: name for data
file 536 is unknown - rename to correct file
ORA-01110: data file 536:
'/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536'
ORA-01157: cannot
identify/lock data file 536 - see DBWR trace file
ORA-01111: name for data
file 536 is unknown - rename to correct file
ORA-01110: data file 536:
'/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536'
ORA-01111: name for data
file 536 is unknown - rename to correct file
ORA-01110: data file 536:
'/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536'
ORA-01157: cannot
identify/lock data file 536 - see DBWR trace file
ORA-01111: name for data
file 536 is unknown - rename to correct file
ORA-01110: data file 536:
'/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536'
ARCH: Connecting to
console port...
Troubleshooting:-
Check
for the files needs to be recovered.
SQL> select * from
v$recover_file where error like '%FILE%';
FILE# ONLINE ONLINE_
ERROR
CHANGE# TIME
---------- -------
------- -------------------- ---------- --------------------
536 ONLINE
ONLINE FILE
MISSING
0
SQL>
Identify
on primary of data file 536(Primary Database)
SQL> select
file#,name from v$datafile where file#=536;
FILE# NAME
----------
----------------------------------------------------------------------
536 +DATA/c/datafile/undotbs9.595.750444337
SQL>
Identify
dummy file name created in (Standby)
SQL> select file#,name
from v$datafile where file#=536;
FILE# NAME
----------
----------------------------------------------------------------------
536 /oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536
SQL>
Crosscheck
that no MRP is running and STANDBY_FILE_MANAGEMENT can be enabled once after
creating file on standby
SQL> alter database
create datafile '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536'
as '+DATA3/cc/datafile/undotbs9_595_750444337';
Database altered.
SQL>
If
You are managing File system ASM with OMF, you are not allowed to create data
file, Because it will file system names and format maintained by ASM. If
you try to Create datafile as above with ASM File, You will pass with below
error.
SQL> alter database
create datafile '/u01/oracle/orahome/dbs/UNNAMED00613' as '+DATA3/cc/datafile/undotbs9_595_750444337'
*
ERROR at line 1:
ORA-01276: Cannot add file
+DATA3/cc/datafile/undotbs9_595_750444337.
File has an Oracle Managed Files file name.
Then
Run above command as shown below
SQL> alter database
create datafile '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536'
as new;
Database altered.
SQL>
Enable
STANDBY_FILE_MANAGEMENT to AUTO & Start MRP.
SQL> show parameter
standby_file_management
NAME
TYPE VALUE
------------------------------------
----------- ------------------------------
standby_file_management
string MANUAL
SQL> alter system set
standby_file_management=AUTO scope=both;
System altered.
SQL>
SQL> show parameter
standby_file_management
NAME
TYPE VALUE
------------------------------------
----------- ------------------------------
standby_file_management
string AUTO
SQL>
SQL> alter database
recover managed standby database disconnect from session;
Database altered.
SQL>
After
creating the file, MRP will start applying archives on standby database.
Note:-
Setting
STANDBY_FILE_MANAGEMENT to AUTO
causes Oracle to automatically create files on the standby database and, in
some cases, overwrite existing files. Care must be taken when setting STANDBY_FILE_MANAGEMENT
and DB_FILE_NAME_CONVERT so that existing standby
files will not be accidentally overwritten.
No comments:
Post a Comment