"Alter Database Recover Managed Standby" Errors ORA-1157/1110/27037_1484843.1

rongshiyuan發表於2014-12-11
DatabaseError

"Alter Database Recover Managed Standby" Returns Errors ORA-1157, ORA-1110 and ORA-27037 (Doc ID 1484843.1)


In this Document

Symptoms
Changes
Cause
Solution

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 06-Aug-2014***

Symptoms

A command "alter database recover managed standby" returns errors ORA-1157, ORA-1110 and ORA-27037.

An example of the errors in the alert log:

...
Completed: ALTER DATABASE RECOVER managed standby database disconnect
...
ALTER DATABASE RECOVER database until cancel
Media Recovery Start
Fast Parallel Media Recovery enabled
Managed Standby Recovery not using Real Time Apply
...
Errors in file /oracle/diag/rdbms/myora/trace/myora_dbw0_827408.trc:
ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
ORA-01110: data file 12: '/u1/app/oracle/product/11.2.0/dbs/tbs_f04.dbf '
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
...
Media Recovery failed with error 1610
...

 

Changes

A new standby database was built from the primary database's RMAN backup.  An attempt to "recover managed standby" is failing with errors.

The problem files seem to exist in the location (path) shown in the error message.  In this example it is /u1/app/oracle/product/11.2.0/dbs/ :

[oracle@mysystem ~]$ ls -l /u1/app/oracle/product/11.2.0/dbs
total 156
...
-rw-r----- 1 oracle dba 114688 Aug 19 18:54 tbs_f04.dbf
...

 The problem files exist in the data dictionary view v$datafile too.  An example:

SQL> SELECT name FROM v$datafile;

NAME

--------------------------------------------------------------------------------

...

/u1/app/oracle/product/11.2.0/dbs/tbs_f04.dbf

...

  

Cause

The problem datafiles have one or more trailing spaces at the end of the file name.  Example: 

SQL> SELECT name
2 FROM v$datafile
3 WHERE name LIKE '% ';

NAME
--------------------------------------------------------------------------------
/u1/app/oracle/product/11.2.0/dbs/tbs_f04.dbf

Other queries to confirm one or more trailing spaces at the end of the problem file:

SQL> SELECT COUNT(*) FROM v$datafile
2 WHERE name = '/u1/app/oracle/product/11.2.0/dbs/tbs_f04.dbf';

COUNT(*)
----------
0

SQL> SELECT COUNT(*) FROM v$datafile
2 WHERE name = '/u1/app/oracle/product/11.2.0/dbs/tbs_f04.dbf ';

COUNT(*)
----------
1

  

Solution

Make sure the datafile names do not have any trailing spaces at the end of the file name.

For the existing problem files:

Take the tablespace that contains the datafiles offline. The database must be open.  Example: 

SQL> ALTER TABLESPACE users OFFLINE NORMAL;

Tablespace altered.

Rename the problem datafiles using the operating system.  Example: 

SQL> !mv '/u1/app/oracle/product/11.2.0/dbs/tbs_f04.dbf ' '/u1/app/oracle/product/11.2.0/dbs/tbs_f04.dbf';

Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames in the database.  Example:

SQL> ALTER TABLESPACE users
2 RENAME DATAFILE '/u1/app/oracle/product/11.2.0/dbs/tbs_f04.dbf '
3 TO '/u1/app/oracle/product/11.2.0/dbs/tbs_f04.dbf';

Tablespace altered.

Take the tablespace that contains the datafiles back online.  Example: 

SQL> ALTER TABLESPACE users ONLINE;

Tablespace altered.

 

 

 

Document Details

 
Rate this document Email link to this documentOpen document in new windowPrintable Page
Type:
Status:
Last Major Update:
Last Update:
PROBLEM
PUBLISHED
Aug 6, 2014
Aug 6, 2014
     
 

Related Products

 
Oracle Database - Enterprise Edition
     
 

Document References

 
No References available for this document.
     
 

Recently Viewed

 
     

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-1363970/，如需轉載，請註明出處，否則將追究法律責任。

相關文章