Autoconfig On Db Tier Fails With Error – SP2-1503: Unable to initialize Oracle call interface After DB Upgrade

Oracle Applications 11.5.10.2 on Solaris 5.10 64bit SPARC

Oracle Application Database 10gR2 (10.2.0.4) upgraded to Oracle Database 11.2.0.3 using Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0) [ID 881505.1].

Issue encountered while executing adconfig in the end:

$>./adconfig.sh
Enter the full path to the Context file: /oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/appsutil/myinstance_myserver.xml
Enter the APPS user password:
The log file for this session is located at: /oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/appsutil/log/myinstance_myserver/07300952/adconfig.log

AutoConfig is configuring the Database environment...

AutoConfig will consider the custom templates if present.
Using ORACLE_HOME location : /oracle01/myinstance/r12/db/product/11.2.0/dbhome_1
Classpath                   : /oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/jdk/jre/lib/rt.jar:/oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/jdk/lib/dt.jar:/oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/jdk/lib/tools.jar:/oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/jdbc/lib/ojdbc5.jar:/oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/appsutil/java/xmlparserv2.zip:/oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/appsutil/java:/oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/jlib/netcfg.jar

Using Context file          : /oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/appsutil/myinstance_myserver.xml

Context Value Management will now update the Context file

Updating Context file...COMPLETED

Attempting upload of Context file and templates to database...COMPLETED

Updating rdbms version in Context file to db112
Updating rdbms type in Context file to 64 bits
Configuring templates from ORACLE_HOME ...

AutoConfig completed with errors.

adconfig.log
============

[AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution.  Errors are grouped by directory and phase.
The report format is:
    

[PROFILE PHASE]
AutoConfig could not successfully execute the following scripts:
Directory: /oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/appsutil/install/myinstance_myserver
afdbprf.sh              INSTE8_PRF         1

[APPLY PHASE]
AutoConfig could not successfully execute the following scripts:
Directory: /oracle01/myinstance/r12/db/product/11.2.0/dbhome_1/appsutil/install/myinstance_myserver
adcrobj.sh              INSTE8_APPLY       1
AutoConfig is exiting with status 2

Cause:
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

Solution:

The issue is related to ORA_TZFILE env variable in 11GR2 this env variable is no longer needed.

In Oracle Database server to fix this issue Unset the env varaible ORA_TZFILE and try to connect.

In Oracle Applications Database to fix this issue Perform the below.

The ORA_TZFILE is set while at 11gR2 this should not be set.

The  $ORACLE_HOME/appsutil/template/ad8idbux.env is from the wrong version  (ad8idbux.env 120.8)

The wrong file includes the  following:

ORA_TZFILE="$ORACLE_HOME/oracore/zoneinfo/timezlrg.dat"

export  ORA_TZFILE

While for 11gr2 it should be:

if test "%s_database%" =  "db112" ;  then

ORA_TZFILE=""

else

ORA_TZFILE="$ORACLE_HOME/oracore/zoneinfo/timezlrg.dat"

fi

export  ORA_TZFILE

Source the environment variable and executed autoconfig in Database Node, completed without issue

on your DB Tier:

cd $ORACLE_HOME/oracore/zoneinfo

ln -s timezone_14.dat timezone.dat

ln -s timezlrg_14.dat timezlrg.dat

*where 14 is the DST version you have upgraded to post 11gR2 upgrade.

DST - Oracle - Morocco

I was looking at Daylight Savings Time patches that need to be applied to an oracle Database and noticed how Morocco is driving oracle Crazy :) !
People of Morocco, please make your dawm mind about when DST needs to take effect, you are driving Oracle support crazy with all the patches; and driving every DBA to the wall wanting to keep up with you !!

Complying with Daylight Saving Time (DST) and Time Zone Rule Changes in E-Business Suite 12 (Doc ID 563019.1)

How to quickly restore to a clean database using Oracle’s restore point

Problem:
----------------------------------------------------------------------------------------------------------Often while conducting benchmarking tests, it is required to load a clean database before the start of a new run. One way to ensure a clean database is to recreate the entire database before each test run, but depending on the size of it, this approach may be very time consuming or inefficient.

Solution:
----------------------------------------------------------------------------------------------------------This article describes how to use Oracle’s flashback feature to quickly restore a database to a state that existed just before running the workload. More specifically, this article describes steps on how to use the ‘guaranteed restore points’.

Restore point: Restore point is nothing but a name associated with a timestamp or an SCN of the database. One can create either a normal restore point or a guaranteed restore point. The difference between the two is that guaranteed restore point allows you to flashback to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter i.e. it is always available (assuming you have enough space in the flash recovery area).

NOTE: In this article Flashback logging was not turned ON.

Guaranteed Restore point:

Prerequisites: Creating a guaranteed restore point requires the following prerequisites:

• The user must have the SYSDBA system privileges
• Must have created a flash recovery area
• The database must be in ARCHIVELOG mode

Create a guaranteed restore point:After you have created or migrated a fresh database, first thing to do is to create a guaranteed restore point so you can flashback to it each time before you start a new workload. The steps are as under:

1. $> su – oracle
2. $> sqlplus / as sysdba;
3. Find out if ARCHIVELOG is enabled
   SQL> select log_mode from v$database;
   If step 3 shows that ARCHIVELOG is not enabled then continue else skip to step 8 below.
4. SQL> shutdown immediate;
5. SQL> startup mount;
6. SQL> alter database archivelog;
7. SQL> alter database open;
8. SQL> create restore point CLEAN_DB guarantee flashback database;
   where CLEAN_DB is the name given to the guaranteed restore point.

Viewing the guaranteed restore point
SQL> select * from v$restore_point;

Verify the information about the newly created restore point. Also, note down the SCN# for reference and we will refer to it as “reference SCN#”

Flashback to the guaranteed restore pointNow, in order to restore your database to the guaranteed restore point, follow the steps below:

1. $> su – oracle
2. $> sqlplus / as sysdba;
3. SQL> select current_scn from v$database;
4. SQL> shutdown immediate;
5. SQL> startup mount;
6. SQL> select * from v$restore_point;
7. SQL> flashback database to restore point CLEAN_DB;
8. SQL> alter database open resetlogs;
9. SQL> select current_scn from v$database;

Compare the SCN# from step 9 above to the reference SCN#.

NOTE: The SCN# from step 9 above may not necessarily be the exact SCN# as the reference SCN# but it will be close enough. 

How to find ICM log on Oracle Apps 11i and R12

I keep forgetting this ALL the time, so I thought I note it down on the intrawebs.

All Concurrent Mangers log files are located in the $APPLCSF/$APPLLOG location.
The following bellow options are available to get the latest log files.

Option1:

Login to Apps User and source environment file
cd $APPLCSF/$APPLLOG
ls -lrt *$TWO_TASK*
Internal Concurrent Manager Log:_MMDD.mgr

Here you can see other list of log files which are related to concurrent mangers.The name conversation for the concurrent manger log files are
Standard manager log: w.mgr
Transaction manager log: t.mgr
Conflict Resolution manager log: c.mgr
*Where: is the concurrent process id of the manager

Option2:

Log-in to the Apps User
Run Source environment file
Connect to sqlplus and run this query

SELECT 'ICM_LOG_NAME=' || fcp.logfile_name
FROM fnd_concurrent_processes fcp, fnd_concurrent_queues fcq
WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND fcp.queue_application_id = fcq.application_id
AND fcq.manager_type = '0'
AND fcp.process_status_code = 'A';

Sample Output:
'ICM_LOG_NAME='||FCP.LOGFILE_NAME
-------------------------------------------------------------
ICM_LOG_NAME=/applprod/comndev/admin/log/DEV_moon1/DEV_0426.mgr

Option3:

Log in to Sysadmin Responsibility
Go to ->Concurrent -> Manager -> Administer -Processes -> Click button Internal Manager Log.

ORA-13516: AWR Operation failed or ORA-20200: Database/Instance nnnnnn does not exist in DBA_HIST_DATABASE_INSTANCE

When running $ORACLE_HOME/rdbms/admin/awrrpt.sql

The process fails with an ORA error.

I have attempted to re-add the target to grid control but still seeing the same issue. - after restarting the agent as well.

I am not getting prompted to select the snap ids (nor days...)

Using 3280118196 for database Id

Using 1 for instance number

declare

*

ERROR at line 1:

ORA-20200: Database/Instance 3280118196/1 does not exist in

DBA_HIST_DATABASE_INSTANCE

ORA-06512: at line 23

Trying the following also fails:

prod1db SQL> BEGIN

2 DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

3 END;

4 /

BEGIN

*

ERROR at line 1:

ORA-13516: AWR Operation failed: AWR Schema not initialized

ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 14

ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 37

ORA-06512: at line 2

The DBID is not found in the table in question:

prod1db SQL> select dbid from v$database;

DBID

----------

3280118196

prod1db SQL> select distinct DBID from DBA_HIST_DATABASE_INSTANCE;

DBID

----------

1902548339

2322143645

3283087540

3853371416

This is a single instance database on a RAC cluster.

select nam.ksppinm name, val.KSPPSTVL, nam.ksppdesc description

 from x$ksppi nam, x$ksppsv val

 where nam.indx = val.indx AND

 nam.ksppinm = '_awr_restrict_mode'

 order By 1

This show AWR is NOT running in restricted mode.

Solution

 Note:782974.1 How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository 

Thanks

Performance - AWR Compare Periods

On a fairly standard Reporting System (11gR1) , made a small change on the application side (meant to flood the DB with requests) and needed to see the impact on the Workload in the DB.

Needed to compare AWR reports generated from 2 different periods.

1. before the Change
2. After the Change.

Here is what you can do:

as DBA simply run : @$ORACLE_HOME/rdbms/admin/awrddrpt.sql 

Grab output and analize.

Or 

select snap_id, end_interval_time 
from dba_hist_snapshot 
where end_interval_time > trunc(sysdate-1) 
order by snap_id; 

8945                                   28/07/2009 12:00:18.555 AM          
8946                                   28/07/2009 1:00:30.647 AM           
8947                                   28/07/2009 2:00:42.740 AM           
8948                                   28/07/2009 3:00:55.045 AM           
8949                                   28/07/2009 4:00:07.050 AM           
8950                                   28/07/2009 5:00:19.198 AM           
8951                                   28/07/2009 6:00:31.596 AM           
8952                                   28/07/2009 7:00:43.751 AM          

select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(3565443845,3,30832,30843,3565443845,3,31000,31011));

Creating Tuning Task with SQL_ID

No Access to OEM, hence SQL Tuning Advisor In command line:

Grab sql_id from v$sql...
Then:

SET SERVEROUTPUT ON
declare
stmt_task VARCHAR2(100);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '49kur02wcrkkk', time_limit => 60);
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
end;
/

task_id: TASK_828086

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_828086');

while using DBMS_SQLTUNE.EXECUTE_TUNING_TASK ... can check status in DBA_ADVISOR_LOG view,

SELECT * FROM DBA_ADVISOR_LOG where task_name = 'TASK_828086'; --'TASK_828076';

SELECT task_name, status FROM DBA_ADVISOR_LOG;

when status=COMPLETED ... and then got recommendations!!!

SET LINESIZE 200
SELECT DBMS_SQLTUNE. REPORT_TUNING_TASK('TASK_828072') AS recommendations FROM dual;

Findings reports example:

GENERAL INFORMATION SECTION

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

Tuning Task Name   : TASK_828086

Tuning Task Owner  : XXX_SYS

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 60

Completion Status  : COMPLETED

Started at         : 06/27/2013 15:06:35

Completed at       : 06/27/2013 15:08:41

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

Error: ORA-13639: The current operation was interrupted because it timed out.

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

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

Schema Name: NOETIX_SYS

SQL ID     : 49kur02wcrkkk

SQL Text   : SELECT GLX0_ALL_BALANCES.PERIOD_NAME,

                      GLX0_ALL_BALANCES.SET_OF_BOOKS_ID,

Oracle Database 12c is here !

The long waited Oracle Database 12c has finally come. 

You can download the software from the Oracle Software Delivery Cloud for the three OS versions:

1.Oracle Database 12c Release 1 (12.1.0.1.0) Media Pack for Linux x86-64 :

https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=16496132

2. Oracle Database 12c Release 1 (12.1.0.1.0) Media Pack for Oracle Solaris on x86-64 (64-bit)

https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=16496136

3. Oracle Database 12c Release 1 (12.1.0.1.0) Media Pack for Oracle Solaris on SPARC (64-bit)

https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=16496134