How to recover and open the database if the archive log required for recovery is missing


Last week, I had one interesting recovery scenario at client side; we had to recover one of our development databases from old backup.
As part of recovery process, our restore went fine and also were able to re-create controlfile. During recovery, it asked for Archive logs. We checked with our Unix team for required archivelogs and found out they don’t have required archive logs.

It was critical for us to recover database because of some project deadline.

Error:

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 9867098396261 generated at 03/21/2008 13:37:44 needed for
thread 1
ORA-00289: suggestion : /arcredo/XSCLFY/log1_648355446_2093.arc
ORA-00280: change 9867098396261 for thread 1 is in sequence #2093

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u100/oradata/XSCLFY/SYSTEM01_SCLFY.dbf’
ORA-01112: media recovery not started

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u100/oradata/XSCLFY/SYSTEM01_SCLFY.dbf’

After doing some research, I found out one hidden parameter (_ALLOW_RESETLOGS_CORRUPTION=TRUE) will allow us to open database even though it’s not properly recovered.

We forced open the database by setting the _ALLOW_RESETLOGS_CORRUPTION=TRUE. It allows us to open database but instance crashed immediately after open. I checked the alert.log file and found out we have undo tablespace corruption.

Alert log shows below error

Errors in file /u01/XSCLFYDB/admin/XSCLFY/udump/xsclfy_ora_9225.trc:
ORA-00600: internal error code, arguments: [4194], [17], [9], [], [], [], [], []
Tue Mar 25 12:45:55 2008
Errors in file /u01/XSCLFYDB/admin/XSCLFY/bdump/xsclfy_smon_24975.trc:
ORA-00600: internal error code, arguments: [4193], [53085], [50433], [], [], [], [], []
Doing block recovery for file 433 block 13525
Block recovery from logseq 2, block 31 to scn 9867098416340

To resolve undo corruption issue, I changed undo_management to “Manual” in init.ora. Now it allowed us to open database successfully. Once database was up and running, I created new undo tablespace and dropped old corrupted undo tablespace. I changed back the undo_management to “Auto” and undo_tablespace to “NewUndoTablespace”.

It resolved our issue and database was up and running without any issue.

_ALLOW_RESETLOGS_CORRUPTION=TRUE allows database to open without consistency checks. This may result in a corrupted database. The database should be recreated.

As per Oracle Metalink, there is no 100% guarantee that setting _ALLOW_RESETLOGS_CORRUPTION=TRUE will open the database. However, once the database is opened, then we must immediately rebuild the database. Database rebuild means doing the following, namely: (1) perform a full-database export, (2) create a brand new and separate database, and finally (3) import the recent export dump. This option can be tedious and time consuming, but once we successfully open the new database, then we expect minimal or perhaps no data loss at all. Before you try this option, ensure that you have a good and valid backup of the current database.Solution:

1) Set _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.
2) Startup Mount
3) Recover database
4) Alter database open resetlogs.
5) reset undo_management to “manual” in init.ora file.
6) startup database
7) Create new undo tablespace
changed undo_management to “AUTO” and undo_tablespace to “NewTablespace”
9) Bounce database.

You are welcome to leave a comment....

Finding XML Publisher Version in R12

1) The following SQL statement will show which patches and thus with version of Oracle XML Publisher is installed on an Oracle E-Business Suite Release 12.0 and 12.1 environment:

SELECT DECODE (bug_number, '4440000', '5.6.3') PATCH, bug_number
FROM ad_bugs WHERE bug_number IN('4440000');
PATCH BUG_NUMBER
----- ------------------------------
5.6.3 4440000

2) The version of a specific Java Class has a one to one relationship with the currently installed version of Oracle XML Publisher. This method is suitable for both Oracle E-Business Suite 11 and Release 12.

$ adident Header $OA_JAVA/oracle/apps/xdo/common/MetaInfo.class:
$Header MetaInfo.java 120.6 2006/08/17 01:25:30 bgkim noship $


Metainfo.class XML Publisher Patch Reference Included in ... R12
120.6 5.6.3 4440000 Note 422508.1

OPP jobs failing with time out

Log file : The Output Post-processor is running but has not picked up this request. No further attempts will be made to post-process this request, and the request will be marked with Warning status.Setting the profile option Concurrent: OPP Response Timeout to a higher value may be necessary.

Solution:
Increase the value of following profiles:
Concurrent:OPP Process Timeout
Concurrent:OPP Response Timeout

Session Time out Setting in R12

Many occassions we have seen end users reporting session timed out even though they were working on. This is for sure its an issue from client end and JRE version which client is running on is the culprit for the same. Try to have the same version of JRE which is running on your server or latest one.
Even if this doesn't help try at client end following setting with running JRE:
1. Navigate to: Start > Control Panel > Java > Advanced > Java Plug-in
2. De-select the option "Enable the next-generation Java Plug-in "
3 Restart the browser
4-Retest the issue

Next if you want to increase timeout setting from server end. Do the following:
1. Change profile option ICX: Session Timeout to 60 min from 30, which is default.
2. change s_oc4j_sesstimeout value to 60 again in context file and run adconfig.
3. Bounce the apps services and check.

 RW-50015 : Installation Error On Oracle EBS R12

RW-50015: Error: - HTTP Listener is not responding. The service might not have started on the port yet. Please check the service and use the retry button.

the Rapid Install window will show many errors for HTTP.

Checking status of OPMN managed Oracle HTTP Server (OHS) instance ...

Processes in Instance: PROD_ebs.ebs.sandiego.com

    -------------------+--------------------+---------+---------
    ias-component | process-type | pid | status
    -------------------+--------------------+---------+---------
    OC4J | oafm | 21168 | Alive
    OC4J | forms | 21078 | Alive
    OC4J | oacore | 20943 | Alive
    HTTP_Server | HTTP_Server | N/A | Down


Download and apply the patch 6078836 from OracleMetaLink to fix an issue with the Oracle HTTP Server bundled with the E-Business Suite technology stack. We unzip the patch:

    [oracle@ebs oracle]$ unzip p6078836_101330_LINUX.zip
    Archive: p6078836_101330_LINUX.zip
    creating: 6078836/
    inflating: 6078836/libdb.so.2
    inflating: 6078836/README.txt

    [oracle@ebs oracle]$ cd 6078836/
    [oracle@ebs 6078836]$ ls
    libdb.so.2 README.txt

    Then we copy to /usr/lib.

    [root@ebs 6078836] $ cp libdb.so.2 /usr/lib

    [root@ebs lib]# cd /usr/lib

    [root@ebs lib]# pwd
    /usr/lib


    [root@ebs lib]# ls libdb.so*
    libdb.so libdb.so.2
    

Processes in Instance: PROD_ebs.ebs.sandiego.com

    -------------------+--------------------+---------+---------
    ias-component | process-type | pid | status
    -------------------+--------------------+---------+---------
    OC4J | oafm | 21168 | Alive
    OC4J | forms | 21078 | Alive
    OC4J | oacore | 20943 | Alive
    HTTP_Server | HTTP_Server | 28519 | Alive .

We then re-start rapidwiz

You are welcome to leave a comment.

Oracle Apps R12 (12.1.1) Installation Issue – RW-50004 , RW-50010

Technology Stack changes in 12.1.1

1. Default database version is 11g  (10g in 12.0.X ; 9i in 11i)

2. Default forms and report version is 10.1.2.3 (10.1.2.2 in 12.0.4 ; 8.0.6 in 11i)

3. Default Web Server version is 10.1.3.4 (10.1.3.0.X in 12.0.X ; 9i/1.0.2.2.2 in 11i).

Disk Space for 12.1.1

Vision Database 194 GB

Application Tier 30 GB

Installation Issue   Oracle Apps Installation is 5 step process

Step1 & Step 2 are database tier related

Step 1 – 0-20% - Oracle Database Software Installation including registration of DB ORACLE_HOME in inventory. To know more about inventory click here

Step 2 - 20-40% - Oracle Database creation including copy of data files from staging area to data top

Install log file for 0-40% is located at $INSTALL_BASE/ db/ tech_st/ 11.1.0/appsutil/ log/<context_name>/MMDDHHMM.log 

Step 3, 4 and 5 are application tier related

Step 3 – 40-60% – Oracle Technology Stack Installation (10.1.2 and 10.1.3 ORACLE HOME) for Application Tier

Step 4 – 60-80%  – APPL_TOP and PRODUCT Top setup

Step 5- 80-100% – Configuration, Startup and Checks

Install log file for 60-100% is located at $INSTALL_BASE/ inst/ apps/ <context_name>/ logs/ MMDDHHMM.log

At around 37% Installer failed with message in log file at  $Install_Base/db/ tech_st/ 11.1.0/ appsutil/ log/ $SID_HOSTNAME/ MMDDHHMM.log ->  RW-50010: Error: – script has returned an error RW-50004: Error code received when running external process. Check log file for details.

after several unsuccessful attempts I looked at /var/log/messages and found messages like

Out of Memory: Killed process 2592 (ovisr12) .

OOM-KILLER - The OOM killer, a feature enabled by default (on Linux) to protect system from hang by terminating processes using high memory.

If kernel cannot find memory to allocate when it’s needed, it puts in-use user data pages on the swap-out queue, to be swapped out. If the Virtual Memory (VM) cannot allocate memory and cannot swap out in-use memory, the Out-of-memory killer may begin killing current user-space processes. It will sacrifice one or more processes in order to free up memory for the system when all else fails.

Solution : 

Turn oom-killer off:

  # echo “0″ > /proc/sys/vm/oom-kill   (turn off)

  # echo “1″ > /proc/sys/vm/oom-kill    (turn on) More information hereThis issue is also reported in Metalink Note # 460790.1

Other possible reasons for RW-50004/RW-50010

1. If RW-50004/10 @ ORACLE_HOME registration with inventory (DB_HOME or Oracle Application Server 10.1.2 & 10.1.3 HOME); check privileges to oraInventory location

or

If this is re-install (from previous failed installation), check ORACLE_HOME is not already registered in Inventory>

2. Corrupt media file – For error like AC-00023: Error: Exception: java. io. File NotFound Exception with RW-50004/10
You are welcome to leave a comment.