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