Adminserver startup failed with BEA-000362 Server failed. Reason: [Management:141245]Schema Validation Error in /u01/app/appqa/KUABI/fs2/FMW_Home/user_projects/domains/EBS_domain/config/config.xml

 Hi,

We have faced issue with Adminserver during it's startup failed with below error.

[****@servername scripts]$ ./adadminsrvctl.sh start

You are running adadminsrvctl.sh version 120.10.12020000.11

Enter the WebLogic Admin password:

Enter the APPS Schema password:

Starting WLS Admin Server...

Refer /u01/app/****/KUABI/fs2/inst/apps/KUABI_servername/logs/appl/admin/log/adadminsrvctl.txt for details

AdminServer logs are located at /u01/app/****/KUABI/fs2/FMW_Home/user_projects/domains/EBS_domain/servers/AdminServer/log

adadminsrvctl.sh: exiting with status 1

From adminserver log

============

Issue fixed after removing "<cache-in-app-directory>" entries from $EBS_DOMAIN_HOME/config/config.xml file.

BEA-000362 Server failed. Reason: [Management:141245

Schema Validation Error in /u01/app/****/KABAI/fs2/FMW_Home/user_projects/domains/EBS_domain/config/config.xml 

<Nov 27, 2021 7:17:20 PM CST> <Critical> <WebLogicServer> <BEA-000362> <Server failed. Reason: [Management:141245]Schema Validation Error in /u01/app/appqa/KABAI/fs2/FMW_Home/user_projects/domains/EBS_domain/config/config.xml see log for details. Schema validation can be disabled by starting the server with the command line option: -Dweblogic.configuration.schemaValidationEnabled=false>

<Nov 27, 2021 7:17:20 PM CST> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to FAILED>

<Nov 27, 2021 7:17:20 PM CST> <Error> <WebLogicServer> <BEA-000383> <A critical service failed. The server will shut itself down>

<Nov 27, 2021 7:17:20 PM CST> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to FORCE_SHUTTING_DOWN>

<Nov 27, 2021 7:17:20 PM> <FINEST> <NodeManager> <Waiting for the process to die: 32506>

<Nov 27, 2021 7:17:20 PM> <INFO> <NodeManager> <Server failed during startup so will not be restarted>

<Nov 27, 2021 7:17:20 PM> <FINEST> <NodeManager> <runMonitor returned, setting finished=true and notifying waiters>

Fix

===

Remove  "<cache-in-app-directory>" entries from $EBS_DOMAIN_HOME/config/config.xml file and start the admin server. It will up.

Thanks.

EMAGENT process_type in disco went to NONE

 Dear Folks,

We have seen one of the ias_component went down in disco. It was due to daylight savings.

When we start manually. We could see below error.

$ ./emctl start agent

a emagent.nohup

----- Sun Mar 14 06:00:04 2021::tzOffset for US/Eastern is -300(min), but agent is runnning with tzOffset -240(min)

-----

----- Sun Mar 14 06:00:04 2021::The agentTZRegion value in /u01/app/******/Oracle/Middleware/disco_inst/EMAGENT/emagent_disco_inst/sysman/config/emd.properties is not in agreement with what agent thinks it should be.Please verify your environment to make sure that TZ setting has not changed since the last start of the agent.

If you modified the timezone setting in the environment, please stop the agent and exectute 'emctl resetTZ agent' and also execute the script mgmt_target.set_agent_tzrgn(<agent_name>, <new_tz_rgn>) to get the value propagated to repository -----

--- Shared agent

----- Sun Mar 14 06:00:06 2021::tzOffset for US/Eastern is -300(min), but agent is runnning with tzOffset -240(min)

-----

----- Sun Mar 14 06:00:06 2021::trying again after waiting for 1 sec to account for daylight transition

-----

----- Sun Mar 14 06:00:06 2021::tzOffset for US/Eastern is -300(min), but agent is runnning with tzOffset -240(min)

-----

----- Sun Mar 14 06:00:06 2021::Mismatch detected between timezone in env (US/Eastern) and in /u01/app/*******/Oracle/Middleware/disco_inst/EMAGENT/emagent_disco_inst/sysman/config/emd.properties (US/Eastern). Forcing value to latter.. -----

----- Sun Mar 14 06:00:07 2021::tzOffset for US/Eastern is -300(min), but agent is runnning with tzOffset -240(min)

Cause:

The agent does not recognize the time zone format that changed recently due to daylight saving.

This parameter must be defined in the format: America/New_York

Processes in Instance: disco_inst

---------------------------------+--------------------+---------+----------+------------+----------+-----------+------

ias-component                    | process-type       |     pid | status   |        uid |  memused |    uptime | ports

---------------------------------+--------------------+---------+----------+------------+----------+-----------+------

emagent_disco_inst               | EMAGENT            |       0 | NONE     | 1735082701 |        0 |   0:00:00 | N/A

Discoverer_disco_inst            | PreferenceServer   |   19604 | Alive    | 1735082280 |    99080 | 1370:22:~ | N/A

Discoverer_disco_inst            | ServicesStatus     |   19605 | Alive    | 1735082282 |    11264 | 1370:22:~ | N/A

ohs1                             | OHS                |   19606 | Alive    | 1735082281 |   450824 | 1370:22:~ | https:8889,https:8890,http:8888

Fix

---

Go to /u01/app/*******/Oracle/Middleware/disco_inst/EMAGENT/emagent_disco_inst/sysman/config

replace value of agentTZRegion as below.

 agentTZRegion=-04:00   (here time zone is EST)

cd /u01/app/*******/Oracle/Middleware/disco_inst/bin

./opmnctl stopall

./opmnctl startall

./opmnctl status -l

Now we can see EMAGENT process goes up.

Processes in Instance: disco_inst

---------------------------------+--------------------+---------+----------+------------+----------+-----------+------

ias-component                    | process-type       |     pid | status   |        uid |  memused |    uptime | ports

---------------------------------+--------------------+---------+----------+------------+----------+-----------+------

emagent_disco_inst               | EMAGENT            |   27167 | Alive    | 1735082705 |    63856 |   0:00:09 | N/A

Discoverer_disco_inst            | PreferenceServer   |   27168 | Alive    | 1735082704 |    90588 |   0:00:09 | N/A

Discoverer_disco_inst            | ServicesStatus     |   27165 | Alive    | 1735082703 |    11264 |   0:00:09 | N/A

ohs1                             | OHS                |   27164 | Alive    | 1735082702 |   384616 |   0:00:09 | https:8889,https:8890,http:8888

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

 Dear Folks,

We have experienced an issue connecting to apps user and getting following error.

NONPROD [*****@servername ~]$ sqlplus apps/apps

SQL*Plus: Release 10.1.0.5.0 - Production on Tue Jun 29 07:56:15 2021

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:

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

descriptor

======

Checked and found service ebs_klqbi missed while checking status of listener.

NONPROD [******@servername ~]$ lsnrctl status $ORACLE_SID

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-JUN-2021 08:27:40

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=servername.domain.tcs)(PORT=1551)))

STATUS of the LISTENER

  Instance "******", status READY, has 1 handler(s) for this service...

The command completed successfully

NONPROD [********@servername ~]$

 ===========

Fix

SQL> Alter session set container=*****;

Session altered.

SQL> BEGIN

DBMS_SERVICE.start_service(service_name => 'ebs_*****');

END;

/ 2 3 4

PL/SQL procedure successfully completed.

Now we are able to connect to apps user.

Thanks.

 

AC-50480: Internal error occurred: java.lang.Exception: Error while generating listener.ora.

 Dear Followers.

        We have the below error when we ran auto-config on the database tier. 

Error from AC log file

+++++++++++++++++

Updating s_tnsmode to 'generateTNS'

UpdateContext exited with status: 0

AC-50480: Internal error occurred: java.lang.Exception: Error while generating listener.ora.

Error generating tnsnames.ora from the database, temporary tnsnames.ora will be generated using templates

Instantiating Tools tnsnames.ora

Tools tnsnames.ora instantiated

Web tnsnames.ora instantiated

adgentns.pl exiting with status 2

ERRORCODE = 2 ERRORCODE_END

.end std out.

   Result             : FAILED

Enter value for 1: Enter value for 2: ERROR:

ORA-12154: TNS:could not resolve the connect identifier specified

Unable to generate listener.ora from database

Using default listener.ora file

.end std out.

.end err out.

Error while generating listener.ora.

StackTrace:

java.lang.Exception: Error while generating listener.ora.

        at oracle.apps.ad.tools.configuration.NetServiceHandler.generateMiddleTierListener(NetServiceHandler.java:2497) 

 

Fix

==

Ran manually

NONPROD [*****@korladrekbs01 admin]$ perl  /u01/app/***/apps/apps_st/appl/ad/12.0.0/bin/adgentns.pl contextfile=$CONTEXT_FILE appspass=apps

=====================================================================================================================

Then Run AC. It should be fine.

Thanks.

ERROR: ORA-01804: failure to initialize timezone information - AUTOCONFIG

 Dear Folks.

Recently we have encountered an issue with auto-config on the database node (19c) that failed with the below error.

ERROR: ORA-01804: failure to initialize timezone information

SP2-0152: ORACLE may not be functioning properly

ERRORCODE = 1 ERRORCODE_END

Fix

==

NONPROD [*******@korladrekbs01 19.3.0]$ unset ORA_TZFILE

Change in below template file to point to correct timezone file and run auto-config.

NONPRD [****@korladrekbs01 zoneinfo]$ diff /u01/app/****/db/tech_st/19.3.0/appsutil/template/ad8idbux.env /u01/app/***/db/tech_st/19.3.0/appsutil/template/ad8idbux.env_old

140c140

<      ORA_TZFILE="$ORACLE_HOME/oracore/zoneinfo/timezlrg_32.dat"

---

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

Routine FND_DCP.REQUEST_SESSION_LOCK received a result code of 1 from the call to DBMS_LOCK.Request.

Dear Folks,

Recently, we faced an issue with concurrent managers in 11i instance . We couldn't able to start them. Gone through database bounce and cm cleanup. But neither of them fixed it.

We looked up manager log file and found below errors.

Routine &ROUTINE has attempted to start the internal concurrent manager.  The ICM is already running.  Contact you system administrator for further assistance.afpdlrq received an unsuccessful result from PL/SQL procedure or function FND_DCP.Request_Session_Lock.

Routine FND_DCP.REQUEST_SESSION_LOCK received a result code of 1 from the call to DBMS_LOCK.Request.

Possible DBMS_LOCK.Request resultCall to establish_icm failed

The Internal Concurrent Manager has encountered an error.

Fix

===

Look for object FND_CP_FNDSM locks.

SQL> sELECT v$access.sid, v$session.serial#

FROM v$session,v$access

WHERE v$access.sid = v$session.sid and v$access.object = 'FND_CP_FNDSM'

GROUP BY v$access.sid, v$session.serial#;   2    3    4

       SID    SERIAL#

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

      1105         27

       626         23

USERNAME           SID    SERIAL# STATUS

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

ACTION

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

MODULE

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

APPS              1105         27 INACTIVE

FNDSM@nchuilmrebsa01.kermar.tcs

 (TNS V1-V3)

USERNAME           SID    SERIAL# STATUS

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

ACTION

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

MODULE

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

APPS               626         23 INACTIVE

Kill above sessions with immediate option and start CM services. They will be up.

Thanks.

adop actualize_all phase didn't drop old editions.

 Dear All,

I wanted to shared an issue which was recently hit to us where old editions didn't drop well when we ran actualize_all phase.

Currently, we have below editions exists.

SQL> @$AD_TOP/sql/ADZDSHOWED.sql

=========================================================================

=                             Editions

=========================================================================

Edition Name    Type     Status   Current?

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

ORA$BASE                 ACTIVE

V_20190824_2350          ACTIVE

V_20191214_0424          ACTIVE

V_20200313_2337          ACTIVE

V_20200523_0133          ACTIVE

V_20200813_1938          ACTIVE

V_20201119_2242          ACTIVE

V_20201121_0457          ACTIVE

V_20201121_0930          ACTIVE

V_20201219_0958          ACTIVE

V_20210123_0009          ACTIVE

V_20210320_0713 OLD      ACTIVE

V_20210423_2333 RUN      ACTIVE   CURRENT

We do see actualize_all phase completed. But an issue with clean up. It went wrong and seen below errors which causes old editions didn't drop off.

    [ERROR]     ORA-04045: errors during recompilation/revalidation of APPS.KBA_GL_TIMSSAR_TXNDTLS_PKG

ORA-04052: error occurred when looking up remote object DPA_ADMIN.FYFIN_KBA_GL_TIMSS_TXN_S@DWHS_AR_INTERFACE

ORA-00604: error occurred at recursive SQL level 3

ORA-02019: connection description for remote datKBAse not found, SQL[ORA$BASE]: drop PACKAGE BODY "APPS"."KBA_GL_TIMSSAR_TXNDTLS_PKG"

    [WARNING]   Could not drop covered object [ORA$BASE] APPS.KBA_GL_TIMSSAR_TXNDTLS_PKG (PACKAGE BODY): ORA-04045: errors during recompilation/revalidation of APPS.KBA_GL_TIMSSAR_TXNDTLS_PKG

If you see above, we can clearly identify we need to drop off object manually in ORA$BASE edition.

Steps

===

SQL> Alter session set edition = ORA$BASE;

Session altered.

SQL> drop package body apps.KBA_GL_TIMSSAR_TXNDTLS_PKG;

Package body dropped.

Then run adop clean up again.

adop phase=cleanup cleanup_mode=full workers=32

Thanks.

ERROR: OPatch failed because of Path problem.Some commands below are not in your path: Command= fuser

 Dear Folks,

We have encountered a problem while applying 10.1.2 patches in EBS 12.1.2 system.

PROD [****@***** 25441839]$ opatch apply

Oracle Interim Patch Installer version 1.0.0.0.64

Copyright (c) 2011 Oracle Corporation. All Rights Reserved..

Oracle recommends you to use the latest OPatch version

and read the OPatch documentation available in the OPatch/docs

directory for usage. For information about the latest OPatch and

other support-related issues, refer to document ID 293369.1

available on My Oracle Support (https://myoraclesupport.oracle.com)

Oracle Home           : /u01/app/***/BJKAI/apps/tech_st/10.1.2

Oracle Home Inventory : /u01/app/****/BJKAI/apps/tech_st/10.1.2/inventory

Central Inventory     : /u01/app/****/BJKAI/inst/apps/BJKAINEW_nsmljba02-oel7/admin/oraInventory

   from               : /etc/oraInst.loc

OUI location          : /u01/app/****/BJKAI/apps/tech_st/10.1.2/oui

OUI shared library    : /u01/app/****/BJKAI/apps/tech_st/10.1.2/oui/lib/linux/liboraInstaller.so

Java location         : /u01/app/****/BJKAI/apps/tech_st/10.1.2/jdk/jre/bin/java

Log file location     : /u01/app/****/BJKAI/apps/tech_st/10.1.2/.patch_storage/<patch ID>/*.log

Some commands below are not in your path:

  Command= fuser

  Extra Search Path= /sbin/fuser, /usr/sbin/fuser

  Path= /u01/app/****/BJKAI/inst/apps/BJKAINEW_nsmljba02-oel7/admin/scripts:/u01/app/****/BJKAI/apps/tech_st/10.1.3/perl/bin:/u01/app/****/BJKAI/apps/tech_st/10.1.2/bin:/u01/app/****/BJKAI/apps/apps_st/appl/fnd/12.0.0/bin:/u01/app/****/BJKAI/apps/apps_st/appl/ad/12.0.0/bin:/u01/app/****/BJKAI/apps/tech_st/10.1.3/appsutil/jdk/jre/bin::/u01/app/****/BJKAI/apps/tech_st/10.1.2/bin:/usr/bin:/usr/sbin:/u01/app/****/BJKAI/apps/tech_st/10.1.3/appsutil/jdk/jre/bin:/u01/app/****/BJKAI/inst/apps/BJKAINEW_nsmljba02-oel7/admin/scripts:/u01/app/****/BJKAI/apps/tech_st/10.1.3/perl/bin:/u01/app/****/BJKAI/apps/tech_st/10.1.2/bin:/u01/app/****/BJKAI/apps/tech_st/10.1.3/appsutil/jdk/jre/bin:/u01/app/****/BJKAI/apps/tech_st/10.1.2/bin:/usr/bin:/usr/sbin:/u01/app/****/BJKAI/apps/tech_st/10.1.3/appsutil/jdk/jre/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/****/.local/bin:/home/****/bin:/u01/app/****/BJKAI/apps/tech_st/10.1.2/OPatch:/u01/app/****/BJKAI/apps/tech_st/10.1.3/appsutil/jdk/bin:/u01/app/****/BJKAI/apps/tech_st/10.1.3/ant/bin:/u01/app/****/BJKAI/apps/tech_st/10.1.2/bin:/u01/app/****/BJKAI/apps/tech_st/10.1.2/OPatch:/u01/app/****/BJKAI/apps/tech_st/10.1.2/OPatch:/u01/app/****/BJKAI/apps/tech_st/10.1.3/appsutil/jdk/bin:/u01/app/****/BJKAI/apps/tech_st/10.1.3/ant/bin:/u01/app/****/BJKAI/apps/tech_st/10.1.2/bin:/u01/app/****/BJKAI/apps/tech_st/10.1.2/OPatch

ERROR: OPatch failed because of Path problem.

This issue is being fixed by installing rpm "psmisc"

rpm -qa | grep psmisc
psmisc-22.20-16.el7.x86_64

Thanks.

Adpreclone and Adcfgclone

 Adpreclone and Adcfgclone

Adpreclone.pl script prepare the source system to be cloned by collecting information about source

system. Create a cloning stage area,generate template and driver from existing files that contain source specific hard coded value.

When you run “adpreclone.pl dbTier” on DB side

Following directories will be created in the ORACLE_HOME/appsutil/clone/Jlib, db, data where “Jlib” relates to libraries “db” will contain the techstack information, “data” will contain the information related to datafiles and required for cloning.

1) Creates driver files at ORACLE_HOME/appsutil/driver/instconf.drv

2) Converts inventory from binary to xml, the xml file is located at $ORACLE_HOME/appsutil/clone/context/db/Sid_context.xml

3) Prepare database for cloning:  This includes creating database control file script and datafile location information file at

$ORACLE_HOME/appsutil/templateadcrdbclone.sql, dbfinfo.lst

4) Generates database creation driver file at ORACLE_HOME/appsutil/clone/data/driverdata.drv

5)Copy JDBC Libraries at ORACLE_HOME/appsutil/clone/jlib/classes12.jar and appsutil

==============================

When you run “adpreclone.pl appsTier” On Apps Side

This will create stage directory at $COMMON_TOP/clone. This also run in two steps.

Techstack:  Creates template files for Oracle_iAS_Home/appsutil/template and Oracle_806_Home/appsutil/template

Creates Techstack driver files for IAS_ORACLE_HOME/appsutil/driver/instconf.drv and 806_ORACLE_HOME/appsutil/driver/instconf.drv

APPL_TOP preparation:  It will create application top driver file at $COMMON_TOP/clone/appl/driver/appl.drv-Copy JDBC libraries and $COMMON_TOP/clone/jlib/classes111.zip

what Perl adcfgclone.pl dbTechStack do?

Perl adcfgclone.pl dbTechStack will do below things.

1)Create context file

2)Register ORACLE_HOME

3)Relink ORACLE_HOME

4)Configure ORACLE_HOME

5)Start SQL*NET listener

what Perl adcfgclone.pl dbTier do?

1)Create context file

2)Register ORACLE_HOME

3)Relink ORACLE_HOME

4)Configure ORACLE_HOME

5)Recreate controlfile

6)Configure database

7)Start SQL*NET listener

==

cd $ORACLE_HOME/appsutils/clone/bin

perl adcfgclone.pl dbTier pwd=apps

This will use the templates and driver files those were created while running adpreclone.pl on source system and has been copied to target system.

Following scripts are run by adcfgclone.pl dbTier for configuring techstack

adchkutl.sh — This will check the system for ld, ar, cc, and make versions.

adclonectx.pl — This will clone the context file. This will ceate a new context file as per the details of this instance.

runInstallConfigDriver — located in $Oracle_Home/appsutil/driver/instconf.drv

Relinking $Oracle_Home/appsutil/install/adlnkoh.sh — This will relink ORACLE_HOME

For data on database side, following scripts are runDriver file $Oracle_Home/appsutil/clone/context/data/driver/data.drv

Create database adcrdb.zipAutoconfig is runControl file creation adcrdbclone.sql

==============

Run adcfgclone.pl for dbTier.

what Perl adcfgclone.pl appsTier do?

perl adcfgclone.pl appsTier will do below things.

1)Create context file

2)Register ORACLE_HOME

3)Relink ORACLE_HOME

4)Configure ORACLE_HOME

5)Create INST_TOP

6)Configure APPL_TOP

7)Start Apps Processses

==

On Application Side

cd $COMMON_TOP/clone/bin/

perl adcfgclone.pl appsTier pwd=apps

Following scripts are run by adcfgclone.pl:

Creates context file for target adclonectx.pl

Run driver files $ORACLE_HOME/appsutil/driver/instconf.drv and $IAS_ORACLE_HOME/appsutil/driver/instconf.drv

Relinking of Oracle Home $ORACLE_HOME/bin/adlnk806.sh and $IAS_ORACLE_HOME/bin/adlnkiAS.sh

At the end it will run the driver file $COMMON_TOP/clone/appl/driver/appl.drv and then runs autoconfig.

When we run adcfgclone.pl which script it will call?

It will call adclone.pl which is located at $AD_TOP/bin .

When we run perl adpreclone.pl dbTier why it requires apps password?

It requires a database connection to validate apps schema.

When do you run adpreclone on Production?

If any changes made to either TechStack,database or any patches applied.

How do we find adpreclone is run in source or not ?

 If clone directory exists under $RDBMS_ORACLE_HOME/appsutil for oracle user and $COMMON_TOP/clone for applmgr user.

When we run perl adpreclone.pl appTier why it will not prompt for apps password?

It doesn’t require db a connection.

adcfgclone on database node we had three modes

perl adcfgclone.pl dbTier

It will configure the ORACLE_HOME on the target database tier node and  recreate the controlfiles.

This is specially used in case of standby database and/or hot backups. It will take care of all the steps.

perl adcfgclone.pl dbTechStack

It will configure the ORACLE_HOME on the target database tier node only. Relink the oracle home.

perl adcfgclone.pl dbconfig

It is used to configure the database with  context file. Database should be in open mode.

adcfgclone.pl appsTier dualfs

DUALFS – new feature is introduced in the latest AD-TXK Delta 7.

This feature will create both the filesystems fs1 and fs2 during the cloning process.

Steps to run SQL Tuning Advisor from Database.

 Dear Folks,

As we already know, we can run tuning advisor again SQL_ID in OEM and implement through it. But some times, we can't implement recommendations.

We have gone through same phase in OEM where we couldn't implement SQL profile through OEM. Hence we have decided to run from DB.

Here are the steps

19c

===

ONPROD [****@servername ~]$ export ORACLE_PDB_SID=OLBUI

NONPROD [****@servername ~]$ sqlplus / as sysdba

SQL> set long 1000000000

Col recommendations for a200

SQL> SQL> DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => '2jy3n3dtmntxm',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => '2jy3n3dtmntxm_tuning_task_1',

 2  3  4  5  6  7  8  9 description => 'Tuning task for statement 2jy3n3dtmntxm');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

10  11  12 DECLARE

*

ERROR at line 1:

ORA-13780: SQL statement does not exist.

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 257

ORA-06512: at "SYS.DBMS_SQLTUNE", line 771

ORA-06512: at line 4

>>If you receive above error.Use AWR snap IDS between SQL_ID Runs.

SQL> SELECT SNAP_ID FROM DBA_HIST_SQLSTAT

WHERE SQL_ID='2jy3n3dtmntxm'

ORDER BY SNAP_ID; 2  3

  SNAP_ID

----------

   3140

   3141

   3142

   3143

   3144

   3145

   3146

   3147

8 rows selected.

SQL> declare

l_sql_tune_task_id varchar2(100);

 2  3 begin

 4  l_sql_tune_task_id := dbms_sqltune.create_tuning_task (

 begin_snap => 3140,

 end_snap => 3147,

 sql_id => '2jy3n3dtmntxm',

 scope => dbms_sqltune.scope_comprehensive,

 time_limit => 10800,

 task_name => '2jy3n3dtmntxm_tuning_task',

 description => 'tuning_in_OLBUI');

dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

end;

/ 5  6  7  8  9  10  11  12  13  14

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '2jy3n3dtmntxm_tuning_task');

PL/SQL procedure successfully completed.

Once above done. Please execute following SQL query to get recommendations.

SET LONG 10000000;

SET PAGESIZE 100000000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('2jy3n3dtmntxm_tuning_task') AS recommendations FROM dual;

SET PAGESIZE 24

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

FINDINGS SECTION (1 finding)

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

1- SQL Profile Finding (see explain plans section below)

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

  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 82.5%)

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

  - Consider accepting the recommended SQL profile.

    execute dbms_sqltune.accept_sql_profile(task_name =>

    '2jy3n3dtmntxm_tuning_task', task_owner => 'SYS', replace =>

    TRUE);

Executed same in PROD and performance was improved.

SQL> show user

USER is "SYS"

SQL> exec dbms_sqltune.accept_sql_profile(task_name =>'2jy3n3dtmntxm_tuning_task', task_owner => 'SYS', replace =>TRUE);

PL/SQL procedure successfully completed.

After implemented above recommendation, Performance was improved for SQL. 

Thanks.

adcfgclone dbTechStack failing with ouicli.pl INSTE8_APPLY 1

 Dear Folks,

Recently, we have came across issue while configuring database ( 12.1.0.2) and failing with below error.

  [APPLY PHASE]

  AutoConfig could not successfully execute the following scripts:

 Directory: /u01/app/oraki/kTAAI/db/tech_st/12.1.0.2/perl/bin/perl -I /u01/app/oraki/kTAAI/db/tech_st/12.1.0.2/perl/lib/5.14.1 -I /u01/app/oraki/kTAAI/db/tech_st/12.1.0.2/perl/lib/site_perl/5.14.1 -I /u01/app/oraki/kTAAI/db/tech_st/12.1.0.2/appsutil/perl /u01/app/oraki/kTAAI/db/tech_st/12.1.0.2/appsutil/clone

 ouicli.pl               INSTE8_APPLY       1

AutoConfig is exiting with status 1

WARNING: RC-50013: Fatal: Instantiate driver did not complete successfully.

/u01/app/oraki/kTAAI/db/tech_st/12.1.0.2/appsutil/driver/regclone.drv

1)

Checked if jre folder doesn't exist under $ORACLE_HOME/appsutil  and found it was there.

/u01/app/oraki/kTAAI/db/tech_st/12.1.0.2/appsutil/jre/bin/java

Able to get java -version output 

java version "1.7.0_45"

OpenJDK Runtime Environment (rhel-2.4.3.3.el6-x86_64 u45-b15)

OpenJDK 64-Bit Server VM (build 24.45-b08, mixed mode)

So this case doesn't suit for this issue 

2) Exported below env varaible and ran adcfg clone. But we got same issue.

export PATH=$ORACLE_HOME/perl/bin:$PATH

============

Finally, have found DB home already registered in inventory.xml file.

Hence, we have moved file and ran adcfgclone and completed successfully.

Thanks.