Wednesday, 20 September 2017

Enable Trace for Concurrent Programs in Oracle Apps R12.1.3

Tracing the Concurrent program is very good option to Debug any issue while running the concurrent program.


Agenda :

 To enable the Trace for Concurrent Programs
 To Debug the issues in Concurrent Programs or its dependent objects.

Steps :

 1. Goto Concurrent Program Definition

Application Developer –> Concurrent –> Program –> check Enable Trace

save it.

Query your concurrent program and check the Enable Trace check box at the bottom of the screen.

2. Set the Profile Concurrent: Allow Debugging to YES.

save it.

3. Navigate to the Responsibility from where you are running the program.

4. Before Submitting the Concurrent Program in Submit Request screen there will be an option like Debug Options click on that button.

5. In the Debug Options select the SQL Trace and enable Trace with binds.

6. Submit the Concurrent Program and note down the Request id.

7.Trace file name will always be post fixed with oracle_process_id.To get the oracle_process_id use the below SQL
 select oracle_process_id from fnd_concurrent_requests where request_id= Request_id;

8.And the Trace file path can be derived using the below

SQL> Select * from v$parameter where name=’user_dump_dest’;

Trace file location:-
/apps/sonu/db/tech_st/11.1.0/admin/SONU_oracle/diag/rdbms/sonu/SONU/trace

How ro convert .trc to .out file :-
tkprof ACSDEV_ora_15529_SEKARS.trc ACSDEV_ora_15529_SEKARS.out  explain=apps/apps  sys=no sort=PRSDSK,EXEDSK,FCHDSK,EXECPU,FCHCPU


9. Get the trace file to your local machine.Understanding the raw trace is very complex so use
TKPROF utility to make the Trace file readable.

10. Open the Command Prompt and run the below command
 TKPROF < Trace File_Name.trc> <Output_File_Name.out> SORT=fchela

11.A new outfile will be generated with the name given in the above command.
Analyse the Output file to know the answers for your problem.


Sql queries to find the log files by Request id


select req.oracle_process_id tracename, req.request_id, req.requested_by,usr.user_name,
prog.user_concurrent_program_name, req.request_date, req.phase_code, req.status_code, req.logfile_name ,
req.outfile_name, dest.value as user_dump_dest from apps. fnd_concurrent_requests req, gv$parameter dest,
apps.fnd_concurrent_programs_vl prog, apps.fnd_user usr

where  req. concurrent_program_id = prog.concurrent_program_id

and req.requested_by = usr.user_id

–and request_id like '17352717'

and dest.name= 'user_dump_dest'

and request_id like & Request_id;


Sql query find the log files by username


select req.oracle_process_id tracename, req.request_id, req.requested_by,usr.user_name,
prog.user_concurrent_program_name, req.request_date,  req.phase_code, req.status_code, req.logfile_name ,
req.outfile_name, dest.value as user_dump_dest

from apps. fnd_concurrent_requests req, gv$parameter dest, apps.fnd_concurrent_programs_vl prog, apps.fnd_user usr

where  req. concurrent_program_id = prog.concurrent_program_id

and req.requested_by = usr.user_id

–and request_id like '17352717'

and dest.name= ‘user_dump_dest’

and usr.user_name like ‘sujeet%jha%’

order by request_date desc



SQL> select oracle_process_id from fnd_concurrent_requests where request_id = &Request_id;

 SQL> select * from apps. fnd_concurrent_requests req,gv$parameter dest , apps .fnd_concurrent_programs_vl prog
where  req. concurrent_program_id = prog.concurrent_program_id and request_id like '17352717' and dest.name= ‘user_dump_dest’

Tuesday, 5 September 2017

How to add employee detail in oracle apps R12

Employee creation and user mapping
Employee creation, Employee role assignments and user mapping:

Issue reason:
       
Some times we are getting permission issue while accessing some of the module/responsibility like
 'iReceivable, Service, Credit management Credit analyst' etc. For that we have to create new employee,import that employee,
 and assign roles (Not mandatory) finally map that employee into Application login user "Person field".

Creating an employee
An employee is the most important entity in an organization. Before creating an employee, the HR officer must know the date from which the employee will be active in the organization. In Oracle terminology, you can call it the employee's hire date. Apart from this, the HR officer must know basic details of the employee such as first name, last name, date of birth, and so on.

Navigation: Global HRMS Manager -->People -->Enter and Maintain.





Select new and enter employee details like name,gender from action tab select "Create Employment
" option for employee creation. And enter identification employee number and save it , then enter the address details.





 Import the Employee:

CRM Administrator --> Resource Manager -->import Resource

From report category page enter your employee name and search employee.
 After finding employee select the employee and click start import button. and click ok Finally select save resource button.

You want to assign any role for this employee means click details button and assign the required roles.
(Credit analyst, credit manager, collection agent, collection manager etc..)

Employee Mapping:

If employee creation process is done, map this employee with application user.
Navigation:  System administrator -->Security --> user --> Define

Query the application user and select this employee in person field, and save it.  

Friday, 1 September 2017

ORA-32006

ORA-32006: SEC_CASE_SENSITIVE_LOGON initialization parameter has been deprecated
ORA-32006: PARALLEL_AUTOMATIC_TUNING Initialization Parameter Has Been Deprecated

SQL> startup 
ORA-32004: obsolete and/or deprecated parameter(s) specified 
ORACLE instance started. 

Total System Global Area 167772160 bytes 
Fixed Size 1247900 bytes 
Variable Size 75498852 bytes 
Database Buffers 88080384 bytes 
Redo Buffers 2945024 bytes 
Database mounted. 
Database opened. 

SYMPTOMS

After setting the database initialization parameter: parallel_automatic_tuning='TRUE' in the initialization file: <init>.ora, the database startup returns:

ORA-32006: PARALLEL_AUTOMATIC_TUNING initialization parameter has been deprecated
The database starts as per normal.
The parameter is set as required by the OWB installation documentation.

CAUSE

The PARALLEL_AUTOMATIC_TUNING parameter has caused the error.

This parameter is no longer required. Refer to:
Warehouse Builder 10g Release 1 Installation Addendum section 1.3.2 PARALLEL_AUTOMATIC_TUNING Parameter:

"Section A.2 (page A-3) instructs you to set the PARALLEL_AUTOMATIC_TUNING parameter to TRUE. This parameter is deprecated in Oracle Database 10g.
Do not set the PARALLEL_AUTOMATIC_TUNING parameter in Oracle Database 10g."

SOLUTION

The parameter should not be set and can be removed from the database initialization file(PFILE).
Start database with Pfile.
Create spfile from Pfile.

Note:-

Security parameters in 11G and 12C

There are 5 parameters that are all prefixed with ‘sec’ in an 11g and 12c database.
Actually that is a lie because one is now deprecated in 12c. They are all, as you might guess related to security.

SEC_CASE_SENSITIVE_LOGON                                 TRUE in 11GR1 , 11GR2, DEPRECATED IN 12C
SEC_MAX_FAILED_LOGIN_ATTEMPTS                 default 11GR1,11GR2=10, 12c=3
SEC_PROTOCOL_ERROR_FURTHER_ACTION default is  CONTINUE in 11GR1, 11GR2, drop, 3 in 12c
SEC_PROTOCOL_ERROR_TRACE_ACTION        default is TRACE 11GR1,11GR2, 12c
SEC_RETURN_SERVER_RELEASE_BANNER           default is FALSE in 11GR1, 11GR2, TRUE in 12c

Thursday, 31 August 2017

ORA-09925


ORA-09925: Unable to create audit trail file

I was received this error message when I was started my database.

[oracle@sonu ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 31 09:22:44 2017

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

ERROR:
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 30: Read-only file system
Additional information: 9925
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 30: Read-only file system
Additional information: 9925

CAUSE

The filesystem inodes are exhausted.

Solution:-

 I rebooted my machine and got following messages which pointed to some
errors encountered during filesystem check and instructed to run fsck manually.

[root@sonu ~]# init 6

Checking filesystems

/: UNEXPECTED INCONSISTENCY; RUN fsck MANUALLY.
(i.e., without -a or -p options)
*** An error occurred during the filesystem check.
*** Dropping you to a shell; the system will reboot
*** when you leave the shell.
Give root password for maintenance (or type Control-D to continue):

– I entered password for root to initiate filesystem check.
As a result I was prompted multiple no. of times to allow fixing of  various filesystem errors.

(Repair filesystem) 1 # fsck
Fix(y)?
- After all the errors had been fixed, filesystem check was restarted

Restarting e2fsck from the beginning...

/: ***** FILE SYSTEM WAS MODIFIED *****
/: ***** REBOOT LINUX *****
- After filesystem had been finally checked to be correct, I exited for reboot to continue.

(Repair filesystem) 2 # exit

– After the reboot, I could successfully connect to my database as sysdba .

[oracle@sonu ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 12 09:21:52 2014

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

Connected to an idle instance.

[oracle@sonu ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 31 10:24:43 2017

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             549453928 bytes
Database Buffers          515899392 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.

SQL> select name from v$database;

NAME
---------
sujeet

Issue resolved.

Wednesday, 23 August 2017

Agent and Host Targets not Shown in OEM


EM 12c, EM 13c : Agent and Host Targets not Shown in the Enterprise Manager Cloud Control Targets List after Agent Installation (Doc ID 1934665.1)


GOAL

Agent and Host targets are not shown in the EM 12c console after a successful Agent installation.
 This note describes how to manually add them.

SOLUTION

1) Run the following command to verify that the Agent is running and can upload to the OMS:

cd <AGENT_HOME>/bin
emctl status agent
emctl upload

2) Run the following command to verify the Host and Agent targets have been registered on the Agent:

cd <AGENT_HOME>/bin
emctl config agent listtargets

3) If the output from the above command shows no targets, run the following query on the repository database to confirm that the targets are present in the repository.

select ENTITY_NAME, ENTITY_TYPE, MANAGE_STATUS, PROMOTE_STATUS from EM_MANAGEABLE_ENTITIES where emd_url like '%<Agent Host>%';

4) If the output from the above query returns no rows, run the following command from the the Agent host to add the targets registered with the Agent:

cd <AGENT_HOME>/bin
emctl config agent addInternaltargets

Review the All Targets, Host targets or Agent targets lists for new target inclusion.

Thursday, 10 August 2017

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2

CAUSE

DBA_TEMP_FREE_SPACE for tablespace_name = 'TEMP2' shows:

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
TEMP2                          32212254720           1441792        32210812928

--although free space exists, still the temp segment cannot be extended

There is only one file (which is having .tmp extension) existing for the tablespace:

FILE_NAME                                                                        used MB Max MB AUTOEXTENSIBLE
/u01/data/UAT/datafile/o1_mf_temp2_c519yq1q_.tmp 30720       0     NO



If the database version is higher than 9i , then should have tablespace datafiles as .dbf, not as .tmp  So a new .dbf datafile needs to be added for the tablespace.


SOLUTION

 Add a new datafile for the tablespace either from command line:

ALTER TABLESPACE TEMP2 ADD TEMPFILE '/u01/data/UAT/datafile/temp02.dbf' SIZE 2000M;

Or by using Enterprise Manager Console.

Tuesday, 1 August 2017

How to add database in OEM 13c

Navigate to the "Add Targets Manually" screen using the menu at the top-right of the screen

  (Setup > Add Target > Add Targets Manually).









Click the "Install Agent on Host" button.





Click the "+ Add" button.

Enter the host and platform, then click the "Next" button.





Create directory on database server

[root@sujeet dba]# mkdir OEM13C_AGENT

[root@sujeet dba]# chown oradba:dba OEM13C_AGENT

[root@sujeet dba]# chmod 777 OEM13C_AGENT

[root@sujeet dba]# pwd

/apps/ebsdba







click on



click on NEXT



Click on DEPLOY AGENT TAB


When Agent install successfully than 

Navigate to the "Add Targets Manually" screen using the menu at the top-right of the screen

  (Setup > Add Target > Add Targets Manually).






Click on ADD TARGET DECLARATIVELY




Enter Host name and click on add tab.





Enter Target name:-  SID NAME.
Database system:- Database OS USER NAME.
Monitor user password:- dbsnmp/*******
Role:- NORMAL
Oracle Home path:- 
Listener Machine name:- 
Port:-
SID:-

Click on Next.

Click on summit.



click on close.

DATABASE HOST ADD SUCCESSFULLY DONE ON OEM13C.













Tuesday, 25 July 2017

there are no active responsibilities available for this user oracle apps r12

There are no Active Responsibilities available for this User


Solution:-

1. Login with sysadmin.

2. Query up the user (that is having the issue) and ensure responsibilities not showing up are NOT end dated. 

3. END DATE the USER (not the responsibility) and save the record. 

4. UN-END DATE the USER and save the record.

5 Request Name = Workflow Directory Services User/Role Validation
Batch Size = 10000
User name = SJHA
Fix dangling users = Yes
Add missing user/role assignments = Yes
Update WHO columns in WF tables = YES
Click "OK" and "Submit".

6. Clear the Cache

7. Missing responsibilities now appear.

Issue resolved.


Monday, 3 July 2017

Argument list too long

EM12c, EM13c: Cloud Control Agent Installation On RHEL 7 Machine
Fails With "/bin/uname: Argument list too long" (Doc ID 2281440.1)

Error find from BI log file.

 Enterprise Manager Cloud Control running the command "/apps/middleware/bin/emctl stop oms -all -force -debug". 

APPLIES TO:

Enterprise Manager Base Platform - Version 12.1.0.4.0 and later
Information in this document applies to any platform.
SYMPTOMS

Agent installation on RHEL 7 machine using push method from OEM cloud control or using silent
 method fails at agent deployment with following error in agentDeploy_<timestamp>.log:

================
Cloning the agent home...
INFO: /opt/app/OracleHomes/agent_13.1.0.0.0/bin/emctl: line 287: /usr/bin/uname: Argument list too long
INFO: /opt/app/OracleHomes/agent_13.1.0.0.0/bin/emctl: line 288: /usr/bin/uname: Argument list too long
INFO: /opt/app/OracleHomes/agent_13.1.0.0.0/bin/emctl: line 475: /opt/app/OracleHomes/agent_13.1.0.0.0/perl/bin/perl:
 Argument list too long
================

CAUSE

Issue is due to stack guard page added to Linux kernel as mentioned by Red Hat in the following link:
https://access.redhat.com/security/vulnerabilities/stackguard?sc_cid=70160000000gzltAAA&


SOLUTION

 Perform any of the following steps to resolve the issue:


Workaround 1:
==========


Kindly follow the below steps. 
[+] Backup <MIDDLEWARE_HOME>/bin/commonenv [+] Increase the stack thread size (EM_THREAD_STACK_SIZE) in the commonenv file as described below: 
from ===================================== if [ "$uname" = "Linux" ] ; then if [ "$EM_THREAD_STACK_SIZE" = "" ] ; then EM_THREAD_STACK_SIZE=3072 fi ulimit -S -s $EM_THREAD_STACK_SIZE 
if [ "$EM_DONT_CHANGE_CORE_SIZE" = "" ] ; then ulimit -S -c hard fi fi ===================================== 
TO 
===================================== if [ "$uname" = "Linux" ] ; then if [ "$EM_THREAD_STACK_SIZE" = "" ] ; then EM_THREAD_STACK_SIZE=8192 fi ulimit -S -s $EM_THREAD_STACK_SIZE 
if [ "$EM_DONT_CHANGE_CORE_SIZE" = "" ] ; then ulimit -S -c hard fi fi 
===================================== 
[+] In bash profile of the operating system user (user performing the OMS installation), set the following parameter: 

$ export EM_THREAD_STACK_SIZE=8192 

[+] If the UI is still open, then please click on "Retry" and continue with the installation.
 
[+] If you accidentally exit the installer before clicking Retry, then do NOT restart the installer to reach the same screen; instead, invoke the runConfig.sh script from the Oracle home to rerun the Configuration Assistant in silent mode. For Microsoft Windows platforms, invoke runConfig.bat script. 

$<ORACLE_HOME>/oui/bin/runConfig.sh ORACLE_HOME=<absolute_path_to_Oracle_home> MODE=perform ACTION=configure COMPONENT_XML={encap_oms.1_0_0_0_0.xml} 



a. In bash profile of the operating system user (user performing the agent installation .bash_profile, .bash_rc), 
set the following parameter:
      $ export EM_THREAD_STACK_SIZE=8192


b. Retry the agent installation



Workaround 2:
==========
As agent home will be created already as part of the agent installation process, so run the following command to
 complete the agent installation:


a. In bash profile of the operating system user (user performing the agent installation .bash_profile, .bash_rc),
set the following parameter:
         $ export EM_THREAD_STACK_SIZE=8192

b. Rename the agent instance (agent_inst) folder if exists

c. Configure the agent by running the following command:
            $ cd <AGENT HOME>/sysman/install
            $ ./agentDeploy.sh AGENT_BASE_DIR=<agent base directory> AGENT_INSTANCE_HOME=<agent instance home> OMS_HOST=<oms hostname> EM_UPLOAD_PORT=<oms upload port> > AGENT_REGISTRATION_PASSWORD=<agent registration password> -configOnly

Wednesday, 7 June 2017

Changing National Character Set AL16UTF16 to UTF8

Changing National Character Set AL16UTF16 to UTF8 on 12c PDB

The national character set is used for data that is stored in table columns of the types NCHAR, NVARCHAR2, and NCLOB.
In contrast, the database character set is used for data stored in table columns of the types CHAR, VARCHAR2 and CLOB.
Like the database character set, the national character set is defined when the database is initially
created and can usually no longer be changed, at least not easily or without involving quite a lot of work  (export, recreate database, import).
Except when creating the database, where the national character set is defined explicitly,


You require SYSDBA authorization to change the national character set.
Changing the national character set means changing an Oracle Dictionary entry, but no data is changed.

Connect PDB as sysdba

[oracle@CDB ~]$ sqlplus sys/password@SID as sysdba

SQL> Select property_value from database_properties
     where upper(property_name) = 'NLS_NCHAR_CHARACTERSET';

SQL> Select owner, table_name, column_name
     from dba_tab_columns
     where (data_type = 'NCHAR' or data_type = 'NVARCHAR2' or data_type = 'NCLOB') and
     owner != 'SYS' and owner != 'SYSTEM';

SQL> select * from v$nls_parameters where parameter like '%CHARACTERSET%';

PARAMETER
----------------------------------------------------------------
VALUE                                                                CON_ID
---------------------------------------------------------------- ----------
NLS_CHARACTERSET
AL32UTF8                                                                  0
NLS_NCHAR_CHARACTERSET
AL16UTF16                                                                 0

SQL> select * from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';

PARAMETER                                                VALUE
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET                   AL16UTF16



Note: If there are no table columns of the types NCHAR, NVARCHAR2 or NCLOB on the database, you can change the national character set without encountering any problems. However, if the database contains tables with NCHAR data type columns, you should perform a check to see whether these columns also contain data.

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT

SQL> STARTUP RESTRICT;
Pluggable Database opened.

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

System altered.

SQL> update props$
set value$='UTF8'
where name in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET'); 

2 rows updated.

SQL> commit;

Commit complete.

SQL> SHUTDOWN IMMEDIATE;
Pluggable Database closed.

SQL> ALTER DATABASE OPEN ;
Database altered.

SQL> select * from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';

PARAMETER                                VALUE
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET                    UTF8

Monday, 5 June 2017

ORA-28374


When I am trying to create tablespace on pluggable databases (PDB) facing below issue.

SQL Error: ORA-28374: typed master key not found in wallet
28374. 0000 -  "typed master key not found in wallet"

*Cause:    You attempted to access encrypted tablespace or redo logs with
           a typed master key not existing in the wallet.

*Action:   Copy the correct Oracle Wallet from the instance where the tablespace
           was created.


Solution

Login with CDB and follow below steps.

[oracle@JCDB ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 5 15:53:48 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c EE High Perf Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options


SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

SQL> administer key management set keystore close;

keystore altered.

SQL> select status, wallet_type, con_id from v$encryption_wallet;

STATUS                         WALLET_TYPE              CON_ID
------------------------------ -------------------- ----------
OPEN                           AUTOLOGIN                     0

SQL> select '!rm ' || wrl_parameter || 'cwallet.sso' from v$encryption_wallet;

'!RM'||WRL_PARAMETER||'CWALLET.SSO'
--------------------------------------------------------------------------------
!rm /u01/app/oracle/admin/CDB/tde_wallet/cwallet.sso

SQL> !rm /u01/app/oracle/admin/CDB/tde_wallet/cwallet.sso

SQL> administer key management set keystore close;

keystore altered.

SQL> select status, wallet_type, con_id from v$encryption_wallet;

STATUS                         WALLET_TYPE              CON_ID
------------------------------ -------------------- ----------
CLOSED                         UNKNOWN                       0


SQL> administer key management set keystore open identified by sys_password container=all;

keystore altered.

SQL> administer key management set key identified by sys_password with backup;

keystore altered.

SQL> administer key management set key identified by sys_password with backup container=all;

keystore altered.


Retest issue.


SQL> alter session set container=PDB;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB

SQL> CREATE TABLESPACE tablespace-name DATAFILE '/u02/app/oracle/oradata/CDB/data01.dbf' size 1024M;

Tablespace created.


issue resolved.

ORA-29874: warning in the execution of ODCIINDEXALTER routine

R12 E-Business Suite Autoconfig Fails On Script jtfictx.sh With Errors ORA-29874: warning in the execution of ODCIINDEXALTER routine And DRG-100595 and DRG-11000 After An Upgrade To 12.1.3 And RDBMS 12.1.0.1 (Doc ID 2223844.1) To BottomTo Bottom


APPLIES TO:

Oracle Applications DBA - Version 12.1.3 and later
Information in this document applies to any platform.
SYMPTOMS

E-Business Suite R12 Applications DBA, Upgrade Related issues

 After an upgrade from R12.0.6 to R12.1.3 including a database migration from 11.2.0.3 to 12.1.0.1,  AutoConfig fails with the following error:

ERROR
-----------------------
jtfictx.sh started at Mon Jan 16 09:00:00 2017


SQL*Plus: Release 10.1.0.5.0 - Production on Mon Jan 16 09:00:00 2017

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

Enter value for 1: Enter value for 2: Enter value for 3: Connected.
DECLARE
*
ERROR at line 1:
ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1,
DRG-10595: ALTER INDEX JTF_AMV_ITEMS_URL_CTX failed
DRG-11000: invalid keyword SYNC
ORA-06512: at "SYS.DBMS_SQL", line 1120
ORA-06512: at line 96


Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
jtfictx.sh exiting with status 1
ERRORCODE = 1 ERRORCODE_END
.end std out.
...
...
[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: /prodapps/PROD/inst/apps/UPDB8_oupapp/admin/install
  jtfictx.sh INSTE8_PRF 1


AutoConfig is exiting with status 1

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Upgrade R12.0.6 to R12.1.3
2. Upgrade database from 11.2.0.3 to 12.1.0.1.
3. Run AutoConfig on DB Tier and then run AutoConfig on AP Tier.



CHANGES

 Upgraded EBS from R12.0.6 to R12.1.3 and Database from 11.2.0.3 to 12.1.0.1

CAUSE

One of the mandatory patches for the 12.1.3 upgrade was not applied:

Patch 16289505 - INTEROPERABILITY PATCH FOR EBS 12.1.3 ON RDBMS 12C (12.1.0.1)

The case is discussed in the following bug:

BUG 22235648 AFTER 12C DATABASE UPGRADE ISSUE WITH AUTOCONFIG - JTFICTX.SH EXITING WITH STATUS,

Per the above bug the following SQL statement should have been replaced with call to ad_ctx_ddl.sync_index in jtfirip_ux.sql 120.4.12010000.4:

  ALTER INDEX REBUILD ON LINE PARAMETERS('sync')

This file should have been provided with patch 16289505 when upgrading DB to 12.1.0.1 as stated in the following document:
Note 1524398.1 - Interoperability Notes EBS 12.0 or 12.1 with RDBMS 12cR1


SOLUTION

To resolve the issue test the following steps in a development instance and then migrate accordingly:

1. Download Patch 16289505 along with any required prerequisite patches.

2. Apply Patch 16289505 along with any required prerequisite patches per the readme instructions.

3. Restart the instance to ensure the changes are successful.

4. Retest AutoConfig and confirm the error is resolved.

Saturday, 3 June 2017

ORA-06598: insufficient INHERIT PRIVILEGES privilege

12c prevents this with this new feature:
INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges.

12c introduced this new piece of functionality that initially looks a little odd but actually does make sense when you follow it through, i would imagine there are limited cases though where this specific functionality is required.

What it essentially allows only is relevant  for invokers rights procedures where you might want a certain piece of the code block to have rights that the invoker might not have and you don't want to give them that right directly - you only want it to be valid when they run that code block. This new feature allows you to do that - with me so far?

Ok lets do a quick demo, we'll create a c##test user which will own the plsql procedure and a c##rich user who we will let invoke it but we don't actually want that user to have any additional rights - just be able to run the few lines of plsql with elevated rights. Now you could argue here - why bother with invokers rights then just run with definers rights - and in the simple case below that is true - but I'm sure there are more complex other cases where this new feature is useful.

Anyway lets set a few things up - starting with the user

Re-create grants and synonyms for Apps Schema using adadmin utility

Facing below error
sqlplus -s APPS/***** @/apps/ebs/apps/apps_st/appl/ad/12.0.0/patch/115/sql/adgrnctx.sql &systempwd &un_fnd CTXSYS &un_jtf JTF

Connected.
Connected.
User altered.
declare
*
ERROR at line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "SYSTEM.AD_DDL", line 165
ORA-06512: at line 160

Solution: -

12c prevents this with this new feature:
INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges
But if SYS trusts x, the following can be done:

SQL> grant inherit privileges on user sys to system;
Grant succeeded.

This will allow x to inherit the privileges of SYS when running code like the above.

SQL> grant INHERIT PRIVILEGES ON USER sys to public;
Grant succeeded.

SQL> grant INHERIT PRIVILEGES ON USER system to apps;
Grant succeeded.

SQL> grant dba to system;
Grant succeeded.

SQL> grant dba to apps;

Grant succeeded.

ORA-01804

Autoconfig Failed On 12c Database With "ORA-01804: failure to initialize timezone information"
(Doc ID 1908836.1)

Oracle Applications DBA - Version 12.1.0.2 to 12.1.3 Information in this document applies to any platform.

SYMPTOMS

On Oracle Applications 11.5.10.CU2, OR 12.0.6 OR 12.1.3 version, when attempting to execute autoconfig on the database tier,

the following error occurs:

ORA-01804: failure to initialize timezone information

SP2-0152: ORACLE may not be functioning properly


The issue can be reproduced at will with the following steps:
1. Autoconfig run on the database tier.
2. The error occurs.

CHANGES

Newly upgraded 12c Database.

CAUSE

The issue is caused by 12c Timezone Specification file code was not added in following files:

ad8idbux.env
ad8idbnt.env
adlnkoh.sh

---------------------In ad8idbux.env file---------------------

if test "%s_database%" = "db112" -o "%s_database%" = "db121" ; then
     ORA_TZFILE=""
else
     ORA_TZFILE="$ORACLE_HOME/oracore/zoneinfo/timezlrg.dat"
fi
export ORA_TZFILE
---------------------In ad8idbux.env file---------------------



---------------------In ad8idbnt.env file---------------------

IF "%s_database%" == "db121" (
     set ORA_TZFILE=
)
---------------------In ad8idbnt.env file---------------------

This issue has been fixed in ad8idbux.env 120.8.12010000.3 or Higher, and in ad8idbnt.env 120.4.12010000.5 or
Higher.

SOLUTION

To implement the solution, please execute the following steps:

1. Download and review the read me and pre-requisites for Patch 16289505.

2. Ensure that you have taken a backup of your system before applying the recommended patch.

3. Apply the patch in a test environment.

4. Confirm the following file versions(database node):
       $ORACLE_HOME/appsutil/template/ad8idbux.env     120.8.12010000.3
       $ORACLE_HOME/appsutil/template/ad8idbnt.env     120.4.12010000.5
       $ORACLE_HOME/appsutil/template/adlnkoh.sh         120.16.12010000.11
You can use the commands like the following:
       strings -a $ORACLE_HOME/appsutil/template/ad8idbux.env | grep Header
       strings -a $ORACLE_HOME/appsutil/template/ad8idbnt.env | grep Header
       strings -a $ORACLE_HOME/appsutil/template/adlnkoh.sh | grep Header

5. Retest the issue.

6. Migrate the solution as appropriate to other environments.

OR try below steps also issue resolved.

Database:- 12.1.0.2 or Apps:- R12.1.3

SQL> conn / as sysdba
Connected.
SQL> SELECT version FROM v$timezone_file;

VERSION
----------
18



Symptoms

 Running AutoConfig on a database tier of E-Business Suite 12.1.3 results in the following error:
ORA-01804: failure to initialize timezone information

SP2-0152: ORACLE may not be functioning properly

Cause

 This issue is caused by the current AutoConfig template file that points to the wrong file.

Solution


1. This is resolved by editing the template ad8idbux.env in location .../oracle/db/tech_st/11.2.0/appsutil/template changing:

From:
set ORA_TZFILE=%s_db_oh%\oracore\zoneinfo\timezlrg.dat

To:
set ORA_TZFILE=%s_db_oh%\oracore\zoneinfo\timezlrg_18.dat

2. Running AutoConfig after this change means the environment file is created with the ORA_TZFILE value as timezlrg_14.dat and AutoConfig completes successfully.


Issue resolved.

Friday, 2 June 2017

UnsatisfiedLinkError exception loading native library: njni11

When running Autoconfig for Oracle database 12.1.0.2.0 with R12.1.3 i get the 

error:
UnsatisfiedLinkError exception loading native library: njni11
java.lang.UnsatisfiedLinkError: /lib/libnjni11.so: ld.so.1: java: fatal: /lib/libnjni11.so: wrong ELF class: ELFCLASS64 (Possible cause: architecture word width mismatch)
/lib/libnjni11.so: wrong ELF class: ELFCLASS64 (Possible cause: architecture word width mismatch)


Solution 

1. Apply patch latest TXK patch. For R12.1.3 Patch 7651166 

2. Migrate the code from the APPS-Tier to the DB-Tier via the appsutil.zip 

3. Check that appsutil has all the required subdirectories (for example scripts, if not copy them from the old appsutil!) 

4. Create the Database Context File again
perl $ORACLE_HOME/appsutil/bin/adbldxml.pl
template=$ORACLE_HOME/appsutil/template/adxdbctx.tmp
out=/u01/clone/db/tech_st/11.2.0/appsutil/CLONE_ebs6.xml

5. Run Database Autoconfig

Oracle Support says that ORACLE_HOME 11.2 does not contain 32-bit libraries, but Autoconfig still uses a 32-bit Java version. You must use 64-bit Java. 

Friday, 19 May 2017

Oracle row count for all tables in schema

Count each number of table rows in specific schema.



Oracle ACE Laurent Schneider has a more elegant solution for counting tables, using dbms_xmlgen to store the row counts for multiple tables in a single SQL query list:

SQL> set pages 999;
SQL> col count format 999,999,999;
SQL> spool /home/oracle/row_count.txt
SQL> select
   table_name,
   to_number(
   extractvalue(
      xmltype(
         dbms_xmlgen.getxml('select count(*) c from '||table_name))
    ,'/ROWSET/ROW/C')) count
from
   user_tables
order by
   table_name;

SQL> spool off;

Output like this

TABLE_NAME                      COUNT
------------------------------ ------
DEPT                                4
EMP                                14
BONUS                               0
SALGRADE                            5

Thursday, 20 April 2017

UNDO tablespace keeps growing in Oracle

The UNDO tablespace size is governed by the the undo_retention parameter.  f its non-default and is set to a high value in order to enable a large flash recover area.  Undo table-space will tend to keep the old data till that time period. Also check out that whether you have any transactions which are not getting committed/rolled back as if this is not happening than your table-space will keep on growing.

Undo data is managed within the UNDO table-space using UNDO segments that are automatically created and maintained by Oracle. Every Oracle Database must have a method of maintaining "before images" of rows that is used to roll back changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed.  UNDO rows are use for several purposes.  We also see these reasons for UNDO:
  • Transaction rollback:   UNDO is the mechanism by which you can undo your changes before a commit with the ROLLBACK command. Also, if a process is abnormally terminated UNDO will be used to clean up any uncommitted transactions.

  • Flashback. Oracle 10g and beyond has Flashback Database. Flashback Query and Flashback Table (but not dropped table) utilize UNDO.

  • Read consistency:  If you start a query at 9AM and it takes 3 hours, are the results you see from 9AM or noon? The answer is 9AM, the moment you pressed the enter key. Oracle accomplishes this through read consistency, which pulls the data you require from current data blocks or consistent read blocks, which is UNDO. If you do not have enough UNDO to satisfy your long running queries, you might get a ORA-01555 error.

  • Transaction Recovery: If your instance is abnormally terminated (kill -9 pmon, shutdown abort), SMON will handle recovery on instance startup. This involves four phases: mount the database, roll forward all redo since the last checkpoint to the data-files, open database, and rollback all non-committed transactions.
Hence, if the UNDO tablespace has a high undo_retention then it will eat up all of your disk space in the UNDO tablespace. I suggest that you take a look at your UNDO adviser within OEM and see what it recommends for an UNDO table-space size for your database.
There are some views that show information related to UNDO activity:
  • V$UNDOSTAT: histogram-like view that shows statistics for 10-minute intervals.
  • V$TRANSACTION: present time view providing information on current transactions.
  • V$SESSTAT: individual session statistics, which includes one for undo usage.
You can query the v$undostat and the v$transaction view to see the actual usage of your UNDO table-space.

You can also run this query to see UNDO usage:

select
   a.sid,
   b.name,
   a.value

from
   v$sesstat  a,
   v$statname b

where
   a.statistic# = b.statistic#

and
   a.statistic# = 176
order by
   a.value DESC;


We also have this query to measure growing UNDO tablespace usage:

colusername FORMAT A15

SELECT s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    t.xidusn = r.usn
AND    rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;


In a nutshell, the undo_retention parameter is used to throttle a growing UNDO tablespace.

Managing the UNDO TABLESPACE

Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:

Roll back transactions when a ROLLBACK statement is issued
Recover the database
Provide read consistency
Analyze data as of an earlier point in time by using Flashback Query
Recover from logical corruptions using Flashback features
Earlier releases of Oracle Database used rollback segments to store undo. Oracle 9i introduced automatic undo management, which simplifies undo space management by eliminating the complexities associated with rollback segment management. Oracle strongly recommends that you use undo tablespace to manage undo rather than rollback segments.

Switching to Automatic Management of Undo Space

To go for automatic management of undo space set the following parameter.

Steps:-

1    If you have not created an undo tablespace at the time of creating a database then, create an undo tablespace by typing the following command

SQL>create undo tablespace myundo datafile
         ‘/u01/oracle/ica/undo_tbs.dbf’ size 500M
                        autoextend ON next 5M ;

When the system is first running in the production environment, you may be unsure of the space requirements of the undo tablespace. In this case, you can enable automatic extension for datafiles of the undo tablespace so that they automatically increase in size when more space is needed

2.    Shutdown the Database and set the following parameters in parameter file.
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=myundo

3.    Start the Database.
Now Oracle Database will use Automatic Undo Space Management.

Calculating the Space Requirements For Undo Retention

You can calculate space requirements manually using the following formula:

UndoSpace = UR * UPS + overhead

where:

UndoSpace is the number of undo blocks
UR is UNDO_RETENTION in seconds. This value should take into consideration long-running queries and any flashback requirements.
UPS is undo blocks for each second
overhead is the small overhead for metadata (transaction tables, bitmaps, and so forth)
As an example, if UNDO_RETENTION is set to 3 hours, and the transaction rate (UPS) is 100 undo blocks for each second, with a 8K block size, the required undo space is computed as follows:

(3 * 3600 * 100 * 8K) = 8.24GBs

To get the values for UPS, Overhead query the V$UNDOSTAT view. By giving the following statement

SQL> Select * from V$UNDOSTAT;



Altering UNDO Tablespace

If the Undo tablespace is full, you can resize existing datafiles or add new datafiles to it

The following example extends an existing datafile

SQL> alter database datafile ‘/u01/oracle/ica/undo_tbs.dbf’ resize 700M

The following example adds a new datafile to undo tablespace

SQL> ALTER TABLESPACE myundo ADD DATAFILE '/u01/oracle/ica/undo02.dbf' SIZE 200M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

Dropping an Undo Tablespace

Use the DROP TABLESPACE statement to drop an undo tablespace. The following example drops the undo tablespace undotbs_01:

SQL> DROP TABLESPACE myundo;

An undo tablespace can only be dropped if it is not currently used by any instance. If the undo tablespace contains any outstanding transactions (for example, a transaction died but has not yet been recovered), the DROP TABLESPACE statement fails.

Switching Undo Tablespaces

You can switch from using one undo tablespace to another. Because the UNDO_TABLESPACE initialization parameter is a dynamic parameter, the ALTER SYSTEM SET statement can be used to assign a new undo tablespace.

The following statement switches to a new undo tablespace:

ALTER SYSTEM SET UNDO_TABLESPACE = myundo2;

Assuming myundo is the current undo tablespace, after this command successfully executes, the instance uses myundo2 in place of myundo as its undo tablespace.

Viewing Information about Undo Tablespace

To view statistics for tuning undo tablespace query the following dictionary

SQL>select * from v$undostat;

To see how many active Transactions are there and to see undo segment information give the following command

SQL>select * from v$transaction;

To see the sizes of extents in the undo tablespace give the following query

SQL>select * from DBA_UNDO_EXTENTS;