ORA-01113 - When Opening the Database

Hi,

I am updating post after posts today, just to make sure I update all the posts which I had to before we get into the new year

Few months back we were cloning a database, during which we had to re-create the database controlfile inorder to rename the database. After creating the controlfile when we tried to open the database with RESETLOGS it failed with below error

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

On checking the alert log we found that the UNDO tablespace that was used in our pfile was not of the same name as in the source database and hence alter database open resetlogs had failed

ORA-30012: undo tablespace 'APPS_UNDOTS1' does not exist or of wrong type

Tue Apr 03 10:01:24 PDT 2012

Error 30012 happened during db open, shutting down database

USER: terminating instance due to error 30012

Instance terminated by USER, pid = 16192

ORA-1092 signalled during: alter database open resetlogs

We then shutdown the database and made the changes to the pfile and mounted the database and tried to open the database using RESETLOGS

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

We got the above error because before the command failed the last time it had done the below (as per the alert log)

1. Reset the log sequence number
2. Recreated the new redo log files as per the new controlfile created

and probably had also completed 

1. Updating the datafiles and online redologs with the new SCN

Hence we issued the alter database open command without RESETLOGS option and it also errored with below error

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery if it was restored from backup, or END

BACKUP if it was not

ORA-01110: data file 1: '/oracle/oradata/T2C/fs01/system_01.dbf'

First we thought that database copy was wrong but the initial behaviour of the "alter database open restlogs" command and also the sanity checks we had done before issuing the command made us sure that there was no issue with the copy

As the database had abruptly shutdown during the "alter database open resetlogs" we thought that the datafile system_01.dbf was inconsistent but we werre unsure how many other files were inconsistent hence we decided to issue the alter database recover command. Below was the result of it

SQL> startup mount

ORACLE instance started.

Total System Global Area 8589934592 bytes

Fixed Size 2110032 bytes

Variable Size 5771365808 bytes

Database Buffers 2734686208 bytes

Redo Buffers 81772544 bytes

Database mounted.

SQL> alter database recover ;

Database altered.

######## alert log excerpt for the alter database recover command ##########

alter database recover

Tue Apr 03 10:36:37 PDT 2012

Media Recovery Start

parallel recovery started with 3 processes

Tue Apr 03 10:36:57 PDT 2012

Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0

Mem# 0: /oracle/oraredo/TS2COE/fs01/redo_1_01.log

Tue Apr 03 10:36:57 PDT 2012

Media Recovery Complete (TS2COE1)

Completed: alter database recover

##############################################################################

Once the recovery was completed we tried to open the database and were successful.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

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

T2C MOUNTED

SQL> alter database open;

Database altered.

-- Tanveer

ORA-00001: unique constraint (APPLSYS.FND_DATABASE_INSTANCES_U1) violated when running Autoconfig

Hi,

Issue:
Sometime back we had a activity to migrate the Front-end/Concurrent Managers hosts only to VMs and the database nodes remained the same.
As part of this activity we ran autoconfig on the database nodes, when running on the second database node we found that the apps.fnd_nodes table was not getting populated with the entries for the second node.
On checking the adconfig.log at location $ORACLE_HOME/appsutil/log/<CONTEXT_NAME>/<MMDDHHMI>, we found that the autoconfig failed to run two scripts afdbprf.sh and adcrobj.sh due to error "ORA-12154: TNS:could not resolve the connect identifier specified"

This prompted us to check if adgentns.pl has run properly and we found that adgentns.pl also has errored out with below error

java.sql.SQLException: ORA-00001: unique constraint (APPLSYS.FND_DATABASE_INSTANCES_U1) violated
ORA-06512: at "APPS.FND_APP_SYSTEM", line 507
ORA-06512: at "APPS.FND_NET_SERVICES", line 951
ORA-06512: at line 1

Troubleshooting:

We checked the index APPLSYS.FND_DATABASE_INSTANCES_U1 and found that it was for table APPLSYS.FND_DATABASES_INSTANCES.
We also checked the columns on which the index APPLSYS.FND_DATABASE_INSTANCES_U1 was built

SQL> select index_owner,index_name,table_owner,table_name,column_name from dba_ind_columns where index_name='FND_DATABASE_INSTANCES_U1';

INDEX_OWNER     INDEX_NAME   COLUMN_NAME
-------------   ----------------   --------------
APPLSYS         FND_DATABASE_INSTANCES_U1  DB_GUID
APPLSYS         FND_DATABASE_INSTANCES_U1  INSTANCE_NUMBER

This made us check the xml file on the second database node to to see if the instance number parameter (s_instNumber) was mentioned correctly, but we found that this parameter s_instNumber was having the same number as first database node.

Fix:

Once we made the corrections to the xml files on the database nodes to reflect the right value for s_instNumber, autoconfig ran successfully and also populated the apps.fnd_nodes table correctly

Afterthoughts:

All the xml files in the database nodes had s_instNumber set as first node because when earlier someone has performed the upgrade of database from 11.2.0.1 to 11.2.0.3 had copied the xml file from the first database node to other database nodes and just changed the host names only.

On closely examining the autoconfig logs run during the upgrade activity we found the same error that we had got when adgentns.pl had run.
But autoconfig had completed sucessfully with no errors as they had not truncated the apps.fnd_nodes table because the FE and database nodes has remained the same during upgrade.

-- Tanveer

ERROR: Failed to bind Launcher to port

Hi,

Recently we did a R12 cloning and faced an error when starting the forms.
The fix seemed trivial at the end but fixing it let me understand few things, which you may already be aware of.
But for those who do not know, this may be a good read

Issue: Forms fails to start with error "Failed to bind Launcher to port 9020"

Troubleshooting:
As our forms was congigured to run in SOCKET mode, we used the adformsrvctl.sh to start the forms.
On checking the socket.log we found the below error recorded

FORMS CONNECTION ACTIVITY LOG FILE
Developer:Forms/LogRecord
[Sun Dec  2 05:30:11 2012 EST]::Server Start-up Data:
        Server Log Filename: /apps/local/D3P/inst/apps/D3P_c-2/logs/ora/10.1.2/forms/socket.log
        Server Hostname: c-2
        Server Port: 9020
        Server Pool: 1
        Server Process Id: 27370
[Sun Dec  2 05:30:11 2012 EST]::ERROR: Failed to bind Launcher to port 9020

We did the initial checks to see if the port 9020 was in use by any other process on the host but found none.
As our environment was multi-tiered we tried starting the forms on another host but found the same issue.
This made us clear that the issue was with our configuration and not at hosting level

In order to try another option we wanted to start the forms on another port say 9040. Hence to accomplish this we edited the appsweb.cfg pointed by the variable $FORMS_WEB_CONFIG_FILE to reflect the new port.
But again when we started the forms service we got the same error and ironically in the log files we found that the forms was starting with the same old port 9020.

This prompted us to think that forms when starting in socket mode was not reading $FORMS_WEB_CONFIG_FILE to take the port number

Fix:
The issue got fixed when we checked the /etc/hosts and found that there was no entry added in it for the load balancer to point to the same host itself.
After making the required changes to the file /etc/hosts and reverting the port to 9020 in $FORMS_WEB_CONFIG_FILE, the forms server started on port 9020

For Eg:
127.0.0.1 localhost localhost.localdomain
173.38.5.72 c-2 c-2.abc.com
173.38.5.72 www-d3p www-d3p.abc.com

This entry is required in /etc/hosts as the load balancer will not detect the port to be up and running until the process is really started on that port.
Hence when starting the forms without the entry in /etc/hosts the request was hitting the load balancer but as the load balancer had not detected anything running on port 9020 it was unable to route the connection.

After adding this entry in the /etc/hosts when starting the forms, the start process was able to resolve www-d3p by bypassing the load balancer and reaching the host itself.

Afterthoughts:
After we were able to resolve the issue and also know the root cause for it. It was still striking us why did the forms server not start with the port 9040 even after editing $FORMS_WEB_CONFIG_FILE
Further reading of scripts revealed the below things

Forms in Socket Mode

1. The script adformsrvctl.sh has the port number and servername hardcoded in it and these values are used to start the forms server and it does not read the $FORMS_WEB_CONFIG_FILE. Incase you want to start the forms on another port, manually change port number in adformsrvctl.sh

2. The script adformsrvctl.sh sources the below env files while starting the forms server
   $APPL_TOP/APPS<CONTEXT_NAME>.env
   $ORA_CONFIG_HOME/10.1.2/forms/server/socket.env

3. $FORMS_WEB_CONFIG_FILE is used to set parameters for the client forms session during runtime. Incase you make any changes to the $FORMS_WEB_CONFIG_FILE then you need not bounce the forms service but just relaunch the forms session for changes to take effect

Forms in Servlet Mode

1. The script adformsctl.sh is used to start forms in SERVLET mode

2. The script adformsctl.sh sources the below env files while starting the forms server
   $APPL_TOP/APPS<CONTEXT_NAME>.env
   $ORA_CONFIG_HOME/10.1.3/<CONTEXT_NAME>.env
   $ORA_CONFIG_HOME/10.1.2/forms/server/default.env

3. The forms service starts using the parameters in the $FORMS_WEB_CONFIG_FILE to start and also at  runtime by the client forms. Incase you want to start the forms on another port, make a change to the $FORMS_WEB_CONFIG_FILE and bounce the services

4. If a change is made to $FORMS_WEB_CONFIG_FILE then forms need to be bounced for it to take effect at forms server and client forms level as well


-- Tanveer

ORA-27048: skgfifi: file header information is invalid

Thought of penning this post as I thought this issue was quite challenging. We were in the process of upgrading from 11.2.0.1 to 11.2.0.3 PSU 1. However, when we started the db with 11.2.0.3 code tree for upgrade we observed the following error:
startup upgrade;
In the alert log:
Errors in file /oracle/admin/TSECX/diag/rdbms/tsecx/TSECX1/trace/TSECX1_dbw0_9942.trc:
ORA-01157: cannot identify/lock data file 380 - see DBWR trace file
ORA-01110: data file 380: '/oracle/oradata/TSECX/fs01/apps_undots4_16.dbf'
ORA-27048: skgfifi: file header information is invalid

Fix:
I want to cut short the troubleshooting steps. We did too much of troubleshooting. During the troubleshooting we observed that db would come up cleanly with 11.2.0.1 code tree but with 11.2.0.3 it will throw the aforesaid error and about 7 files would show up in v$recover_File view.
We opened the db with 11.2.0.1 code tree and then resized the 7 data files to add about 250MB space to each of the 7 files. Shutdown immediate. Then started the db upgrade with 11.2.0.3 code tree and it worked!
- Aravind Kamath Posral

ORA-16572 DMON: cannot open configuration file

Hi,

This can be a interesting read.

Recently we created a Data Guard Configuration on a Non-RAC Database using cooked file system. We pro-actively started monitoring the DMON logs and the alert logs so that we can trace all the steps during this setup.

Issue:

We were getting the error in the DMON log when we set the below parameters.

alter system set dg_broker_config_file1='/oracle/oradata/CDEV/drCDEV_PRIMARY_01.dat' scope=both;

alter system set dg_broker_config_file2='/oracle/oradata/CDEV/drCDEV_PRIMARY_02.dat' scope=both;

alter system dg_broker_start=true;

Error in the DMON log was as below

DMON: >> Starting Data Guard Broker bootstrap <<

DMON: Attach state object

DMON: cannot open configuration file "/oracle/oradata/CDEV/drCDEV_PRIMARY_01.dat", retrying

DMON: cannot open configuration file "/oracle/oradata/CDEV/drCDEV_PRIMARY_01.dat"

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

DMON: Error opening /oracle/oradata/CDEV/drCDEV_PRIMARY_01.dat, error = ORA-16572

DMON: Establishing /oracle/oradata/CDEV/drCDEV_PRIMARY_02.dat as the most current file

DMON: cannot open configuration file "/oracle/oradata/CDEV/drCDEV_PRIMARY_02.dat", retrying

DMON: cannot open configuration file "/oracle/oradata/CDEV/drCDEV_PRIMARY_02.dat.dat"

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

We got this error even on the standby when we set the above parameters

Troubleshooting

From the error it was obvious that DMON was looking for the dg_broker_config file and was unable to find at the physical level.

We were sure that this file was to be created Oracle as this was not a database using the raw volumes or block devices.

Hence we checked for permission on the location and also created a file, etc., but found that everything was fine.

We are tried changing the location of the files to various mount points just to check if it was issue at the mount point level but we got the same error.

Observation

We then decided to go ahead and create the configuration using DGMGRL

On Primary database

DGMGRL> CREATE CONFIGURATION 'DR_CDEV' AS

PRIMARY DATABASE IS 'CDEV_PRIMARY'

CONNECT IDENTIFIER IS CDEV_PRIMARY_DGMGRL;

Once this command was executed we found that the dg_broker_files got created on the Primary database.

But still the files were missing on the Standby database. Encouraged by the previous finding we went ahead and added the Standby database to the configuration and when we enabled the configuration we saw that the db_broker_files got created on the Standby as well.

On Primary Database

DGMGRL> enable configuration

Regards,

Tanveer

Lesson Learnt: PRO-ACTIVENESS sometimes can be DISASTROUS :)

Find delay between Primary and Standby Database

Hi,

This is just a tip and a ready reckoner for me

To find Delay if both Standby and Primary in same timezone

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') WHEN,': standby is ' || trim(to_char(1440 * (sysdate - max (next_time)),99999.99) ||' minutes behind') LAG from v$archived_log where applied ='YES';

To find Delay if Standby and Primary in different timezone

1. On Primary get time at OS level

$ date

Tue Feb 14 15:25:08 CET 2012 ------------------------- A

2. On Standby

sqlplus / as sysdba

SQL> alter session set nls_date_format='DD-MON-YY HH24:MI';

SQL> select dbtimezone from dual; - gives timezone for database

SQL> select max(next_time) from v$archived_log where applied='YES';

MAX(NEXT_TIME)

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

14-FEB-12 14:22 --------------------------------------- B

The delay is A-B

In this case around 1 hour.

Regards,

Tanveer

ORA-12514 When doing Switchover to STANDBY Database

Hi,

Today we faced issue when switching over the PRIMARY database to STANDBY database we faced the below issue.

Issue:

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/manager

Connected.

DGMGRL> switchover to 'CV_S';

Performing switchover NOW, please wait...

Operation requires shutdown of instance "CV" on database "CV_P"

Shutting down instance "CV"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires shutdown of instance "CV" on database "CV_S"

Shutting down instance "CV"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "CV" on database "CV_P"

Starting instance "CV"...

Unable to connect to database

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.

You are no longer connected to ORACLE

Please connect again.

Unable to start instance "CV"

You must start instance "CV" manually

Operation requires startup of instance "CV" on database "CV_S"

You must start instance "CV" manually

Switchover succeeded, new primary is "CV_S"

DGMGRL>

Troubleshooting:

Metalink and other notes pointed out that the issue will occur if we miss adding the parameter GLOBAL_DBNAME = db_unique_name_DGMGRL to the listener on the primary and standby database as the DMON uses service db_unique_name_DGMGRL when starting the databases during switchover.

But when we checked the listener configuration we found that desired parameter was added and also configured in the listener

Primary Database

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

LISTENER_DG_CV =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(Host = db-d6.abc.com)(Port = 1530)(ip = first))

)

SID_LIST_LISTENER_DG_CV_db-d6 =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = CV)

(SDU = 32768)

(TDU = 32768)

(ORACLE_HOME = /oracle/10.2.0.4/CV)

(SID_NAME = CV)

)

(SID_DESC =

(GLOBAL_DBNAME = CV_P_DGMGRL)

(SDU = 32768)

(TDU = 32768)

(ORACLE_HOME = /oracle/10.2.0.4/CV)

(SID_NAME = CV)

)

)

Standby Database

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

LISTENER_DG_CV =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(Host = db2-d6.abc.com)(Port = 1530)(ip = first))

)

SID_LIST_LISTENER_DG_CV_db-d6 =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = CV)

(SDU = 32768)

(TDU = 32768)

(ORACLE_HOME = /oracle/10.2.0.4/CV)

(SID_NAME = CV)

)

(SID_DESC =

(GLOBAL_DBNAME = CV_S_DGMGRL)

(SDU = 32768)

(TDU = 32768)

(ORACLE_HOME = /oracle/10.2.0.4/CV)

(SID_NAME = CV)

)

)

Also the listener had registered the service name

Primary Side

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

$ lsnrctl status LISTENER_DG_CV_db-d6

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 29-FEB-2012 09:53:13

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=TCP)(Host=db-d6.abc.com)(Port=1530)(ip=first))

STATUS of the LISTENER

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

Alias LISTENER_DG_CV_db-d6

Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production

Start Date 28-FEB-2012 12:08:48

Uptime 0 days 21 hr. 44 min. 24 sec

Trace Level off

Security ON: Local OS Authentication

SNMP ON

Listener Parameter File /etc/listener.ora

Listener Log File /oracle/10.2.0.4/CV/network/log/listener_dg_cv_db-d6.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=173.38.0.22)(PORT=1530)))

Services Summary...

Service "CV" has 1 instance(s).

Instance "CV", status UNKNOWN, has 1 handler(s) for this service...

Service "CV_P_DGMGRL" has 1 instance(s).

Instance "CV", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

Standby Side

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

$ lsnrctl status LISTENER_DG_CV_db2-d6

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 29-FEB-2012 09:53:13

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=TCP)(Host=db2-d6.abc.com)(Port=1530)(ip=first))

STATUS of the LISTENER

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

Alias LISTENER_DG_CV_db2-d6

Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production

Start Date 28-FEB-2012 12:08:48

Uptime 0 days 21 hr. 44 min. 24 sec

Trace Level off

Security ON: Local OS Authentication

SNMP ON

Listener Parameter File /etc/listener.ora

Listener Log File /oracle/10.2.0.4/CV/network/log/listener_dg_cv_db-d6.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=173.38.0.22)(PORT=1530)))

Services Summary...

Service "CV" has 1 instance(s).

Instance "CV", status UNKNOWN, has 1 handler(s) for this service...

Service "CV_S_DGMGRL" has 1 instance(s).

Instance "CV", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

Further when ever we tried making the sqlplus session (sqlplus system/abc@CV_P_DGMGRL or sqlplus system/abc@CV_S_DGMGRL) from standby to primary or vice-versa it went perfectly fine and had no issues.

Also we had other databases in our system which had the same configuration and the switchover was working fine. When we started to compare those databases with this database having the issue we found that the parameter db_domain was set here to abc.com

It seemed like the DMON was trying to connect to service db_unique_name_DGMGRL.abc.com (db_unique_name_DGMGRL.domain.com) in the connect descriptor of the tns names it was using to start the database. It made me think that DMON constructed its own tnentry to start the database and did not use the tns entry we had made in the tnsnames.ora

Solution:

In our case

db_unique_name = CV_P on primary database

db_unique_name = CV_S on standby database

To make the failover successful we re-registered the listener with service name db_unique_name_DGMGRL.abc.com by editing the listener file as below and reloading it.

Primary Database

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

LISTENER_DG_CV =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(Host = db-d6.abc.com)(Port = 1530)(ip = first))

)

SID_LIST_LISTENER_DG_CV_db-d6 =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = CV.abc.com)

(SDU = 32768)

(TDU = 32768)

(ORACLE_HOME = /oracle/10.2.0.4/CV)

(SID_NAME = CV)

)

(SID_DESC =

(GLOBAL_DBNAME = CV_P_DGMGRL.abc.com)

(SDU = 32768)

(TDU = 32768)

(ORACLE_HOME = /oracle/10.2.0.4/CV)

(SID_NAME = CV)

)

)

$ lsnrctl reload LISTENER_DG_CV

$ lsnrctl status LISTENER_DG_CV_db-d6

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 29-FEB-2012 09:53:13

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=TCP)(Host=db-d6.abc.com)(Port=1530)(ip=first))

STATUS of the LISTENER

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

Alias LISTENER_DG_CV_db-d6

Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production

Start Date 28-FEB-2012 12:08:48

Uptime 0 days 21 hr. 44 min. 24 sec

Trace Level off

Security ON: Local OS Authentication

SNMP ON

Listener Parameter File /etc/listener.ora

Listener Log File /oracle/10.2.0.4/CV/network/log/listener_dg_cv_db-d6.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=173.38.0.22)(PORT=1530)))

Services Summary...

Service "CV.abc.com" has 1 instance(s).

Instance "CV", status UNKNOWN, has 1 handler(s) for this service...

Service "CV_P_DGMGRL.abc.com" has 1 instance(s).

Instance "CV", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

Repeated same steps on the Standby database also so that same changes and take place.

Then we brought up the database manually and now due to the initial switchover Standby database role is primary and Primary database role is Physical_Standby.

Hence to check the fix we switched over from Standby to Primary and it went perfectly fine

DGMGRL> switchover to 'CV_P';

Performing switchover NOW, please wait...

Operation requires shutdown of instance "CV" on database "CV_S"

Shutting down instance "CV"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires shutdown of instance "CV" on database "CV_P"

Shutting down instance "CV"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "CV" on database "CV_S"

Starting instance "CV"...

ORACLE instance started.

Database mounted.

Operation requires startup of instance "CV" on database "CV_P"

Starting instance "CV"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "CV_P"

DGMGRL>

Alternate Solution: Instead of reloading the listeners we can nullify the db_domain parameter on the Primary and Standby Databases. I have not tried incase you get hit by this issue you can try it.

Regards,

Tanveer

ORA-01111 and ORA-01110 on the STANDBY DATABASE

Hi,

Recently we built a STANDBY DATABASE for one of our production databases.

The STANDBY DATABASE was 10g with raw volumes

Issue Description

The recovery was stuck with the below error on the STANDBY DATABASE.

Alert Log

Errors in file /oracle/admin/CPD/bdump/CPD_mrp0_19746.trc:

ORA-01111: name for data file 178 is unknown - rename to correct file

ORA-01110: data file 178: '/oracle/product/10.2.0.4/CPD/dbs/UNNAMED00178'

ORA-01157: cannot identify/lock data file 178 - see DBWR trace file

ORA-01111: name for data file 178 is unknown - rename to correct file

ORA-01110: data file 178: '/oracle/product/10.2.0.4/CPD/dbs/UNNAMED00178'

Trace File

MRP0: Background Media Recovery terminated with error 1111

ORA-01111: name for data file 178 is unknown - rename to correct file

ORA-01110: data file 178: '/oracle/product/10.2.0.4/CPD/dbs/UNNAMED00178'

ORA-01157: cannot identify/lock data file 178 - see DBWR trace file

ORA-01111: name for data file 178 is unknown - rename to correct file

ORA-01110: data file 178: '/oracle/product/10.2.0.4/CPD/dbs/UNNAMED00178'

Troubleshooting

The error codes pointed out that it is a issue with missing file which is present in the control file but not present physically on the STANDBY host.

As this issue occurred later after we had successfully configured the STANDBY DATABASE, we were able to make out that this could have occured due to new raw volume addition on primary database which has not been provisioned on the STANDBY DATABASE side.

Hence at first we needed to find the number of files added on primary after the STANDBY DATABASE build. The below query listed the time the raw volumes were added to the database and hence we could figure out the raw volumes added to the database

set lines 150

set pages 0

col name for a75

select name,to_char(CREATION_TIME,'HH24:MI:SS MON-DD-YYYY') from v$datafile order
by creation_time;

We found that there were 2 files added to the database after we had created the STANDBY DATABASE.

Nextly we needed to find the exact raw volume which was needed by the STANDBY DATABASE to continue recovery.

From the error we were able to know that the STANDBY DATABASE needed the datafile with file number 178

We ran the below query on the primary database, which yielded us the exact name of the missing raw volume and the same query on the STANDBY side gave the psuedo name the STANDBY DATABASE had given to the missing file on the STANDBY DATABASE side. Hence confirming the exact raw volume name.

Primary Side

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

SQL> select name from v$datafile where file#=178;

NAME

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

/dev/mapper/oraCPD_data_017_4600Mp9

STANDBY Side

-------

SQL> select name from v$datafile where file#=178;

NAME

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

/oracle/product/10.2.0.4/CPD/dbs/UNNAMED00178

Note: Though there were 2 files added to the production that was missing on STANDBY DATABASE side, the recovery was stuck at the very first missing file.

Hence we needed to correct only that file but for the issue not to occur again due to the other missing raw volume you need to make sure even that

raw volume is made available on the STANDBY DATABASE side.

(We got the all the missing raw volumes present on the primary side added to the STANDBY to avoid such issues in the future when there is new raw volume addition)

Solution

1. Make sure that all the missing raw volumes are added to the STANDBY side at host level.

2. Stop Recovery on the Standby Server

recover managed standby database cancel;

3. Change the standby_file_management parameter to MANUAL on STANDBY DATABASE

alter system set standby_file_management=manual;

4. Rename the datafile on Standby side

alter database create datafile '/oracle/product/10.2.0.4/CPD/dbs/UNNAMED00178'
as '/dev/mapper/oraCPD_data_017_4600Mp9';

5. Change the standby_file_management parameter to AUTO on STANDBY DATABASE

alter system set standby_file_management=auto;

6. Restart the Recovery

alter database recover managed standby database disconnect;

Regards,

Tanveer