This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

Tag: Oracle

HowTo: Recover Oracle DB with ORA-01547 after RESETLOGS Failed

Scenario: You have restored a database from a backup but part way through recovery, you didn’t have all the archive redo logs.
Your recovery is an automated script that then progressed on to OPEN RESTLOGS, which failed.

Now you have all the archive logs and you want to re-run recovery, but you get ORA-01547 plus ORA-01194.

If you are certain that you now have all the archive logs (maybe you forgot to copy all of them), you need to restart recovery:

SQL> shutdown abort;
SQL> startup mount exclusive;
SQL> recover automatic database using backup controlfile until cancel;

You now get the error:

ORA-00279: change 5996813573990 generated at 01/30/2013 18:38:25 needed for thread 1
ORA-00289: suggestion : /oradata/PROD/archive/PROD_806092582_1_1.arc
ORA-00280: change 5996813573990 for thread 1 is in sequence #1
ORA-00278: log file '/oradata/PROD/archive/PROD_806092582_1_1.arc' no longer needed for this recovery
ORA-00308: cannot open archived log '/oradata/PROD/archive/PROD_806092582_1_1.arc'
ORA-27037: unable to obtain file status
HPUX-ia64 Error: 2: No such file or directory
Additional information: 3

The database is looking for redo information generated during the previous recovery session.
During a recovery, redo information is generated because the database is replaying the undo records of transactions to be rolled back, but these records may need to be rolled back. So you get redo records.

Unfortunately, because your database is in a precarious state, it doesn’t quite know where to look for the redo records.
So, all you need to do is tell it where the redo logs are located (your database probably won’t have performed a log switch yet).

First, cancel the recovery:

SQL> CANCEL

For checking purposes, it’s good to confirm that only SYSTEM tablespace is needing recovery by querying the current SCNs across all datafiles:

SQL> select distinct fhscn SCN from x$kcvfh;

SCN
----------------
5996813573990

Again, for checking purposes, you can see that one of the datafiles has a status of != 4. (4= consistent):

SQL> select distinct fhsta STATUS from x$kcvfh;

STATUS
----------
4
8196

You can see that this is the SYSTEM datafile (filenumber =1):

SQL> select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE from x$kcvfh where fhsta = 8196;

FILENUMBER STATUS SCN SEQUENCE
---------- ---------- ---------------- ----------
1 8196 5996813573990 1

To be able to provide the first redo log to the recovery process, you should query your log members.
I have found that usually, the recovery only needs the very first log member listed when you run the following query (I guess this is the “active” log member at the time the RESETLOGS was called):

SQL> select member from v$logfile;

MEMBER
—————————————————-
/oradata/PROD/log_a/log4a_PROD.log            <<– This one.
/oradata/PROD/log_b/log4b_PROD.log
/oradata/PROD/log_a/log3a_PROD.log
/oradata/PROD/log_b/log3b_PROD.log
/oradata/PROD/log_a/log2a_PROD.log
/oradata/PROD/log_b/log2b_PROD.log
/oradata/PROD/log_a/log1a_PROD.log
/oradata/PROD/log_b/log1b_PROD.log

8 rows selected.

Now you can call the recovery process again.

This time, when you are prompted for the archive log name, enter the redo log member name & path you got from the previous SQL:
SQL> recover automatic database using backup controlfile until cancel;
 
ORA-00279: change 5996813573990 generated at 01/30/2013 18:38:25 needed for thread 1
ORA-00289: suggestion : /oradata/PROD/archive/PROD_806092582_1_1.arc
ORA-00280: change 5996813573990 for thread 1 is in sequence #1
ORA-00278: log file '/oradata/PROD/archive/PROD_806092582_1_1.arc' no longer needed for this recovery
ORA-00308: cannot open archived log '/oradata/PROD/archive/PROD_806092582_1_1.arc'
ORA-27037: unable to obtain file status
HPUX-ia64 Error: 2: No such file or directory
Additional information: 3

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/oradata/PROD/log_a/log4a_PROD.log

Log applied.
Media recovery complete.

Now open your database:

SQL> alter database open resetlogs;
 
Database altered.

SQL> exit

You should now run an immediate FULL backup using your preferred method.

Oracle Doc: EBS R12 Upgrade/Patch Forms and Reports

There’s a nice Oracle doc on how to upgrade the Oracle AS 10g Forms & Reports component of an Oracle EBS R12 instance:
Upgrading OracleAS 10g Forms and Reports in Oracle E-Business Suite Release 12 [ID 437878.1]
Once you’ve read it, you’ll realise what a mammoth task it is.
It’s interesting that out-of-the-box, the Forms & Reports upgrades don’t work with EBS, you need the interoperability patches to be installed ontop.

HowTo: TNSPING without DEFAULT_DOMAIN

Scenario: You want to tnsping an Oracle net service name, but your sqlnet.ora has a DEFAULT_DOMAIN configured.
This means tnsping automatically adds your DEFAULT_DOMAIN onto the end of the service name you want to tnsping.

> tnsping mynetservice

TNS Ping Utility for HPUX: Version 11.2.0.3.0 - Production on 18-APR-2013 18:05:03
Copyright (c) 1997, 2011, Oracle. All rights reserved.

Used parameter files:
/oracle/SID/112_64/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name

In the sqlnet.ora, you have something like:

NAMES.DEFAULT_DOMAIN = WORLD

All you need to do to ping a net service name without the DEFAULT_DOMAIN, is to append a ‘.’ to the net service name like so:

> tnsping mynetservice.     <<- Note the dot.

TNS Ping Utility for HPUX: Version 11.2.0.3.0 - Production on 18-APR-2013 18:05:03
Copyright (c) 1997, 2011, Oracle. All rights reserved.

Used parameter files:
/oracle/SID/112_64/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))) (CONNECT_DATA = (SERVER=DEDICATED) (SERVICE_NAME = mynetservice)))

OK (140 msec)

Basta cosi

APP-FND-01630 DBC File Oracle EBS R12

If you receive error APP-FND-01630 “Cannot open file xxxx” in a popup window whilst working on an Oracle R12 E-Business Suite instance:

If you get the the administrator to add read permissions for everyone, for the DBC file under $FND_SECURE.
As the applmgr user, change to the $FND_SECURE directory and add permissions to the dbc file:

chmod g+r,o+r <file>.dbc

No restart of EBS is required.
This should fix the issue.