Wednesday, 15 November 2017

Internal Server Error

Intermittent Login issue R12.1 - 500 Internal Server Error

Problem Description:

Users are unable to get application login page.
Users are frequently getting below error while accessing the EBS application R12.1.3 URL.

Internal Server Error

The server encountered an internal error or misconfiguration and was unable to complete your request.
Please contact the server administrator, ohs_admin@acs.net and inform them of the time the error occurred, and anything you might have done that may have caused the error.
More information about this error may be available in the server error log.

Cause:

This issue occurred due to low oacore heap size.

Solution:

Increase the oacore heap size and restart application services.

Change the parameters in $ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml
of both form nodes as shown below:

From:
-Xmx512M -Xms128M -XX:MaxPermSize=160M

To:
-Xmx1024M -Xms128M -XX:MaxPermSize=160M


Reference:

JVM: Guidelines to setup the Java Virtual Machine in Apps E-Business Suite 11i and R12 (Doc ID 362851.1)


Tuesday, 14 November 2017

How to run Gather Statistics for a Schema in R12.1.3

How to run Gather Schema Statistics concurrent program

1. Log on to Oracle Applications with
    Responsibility = System Administrator

2. Submit Request Window
    Navigate to: Concurrent > Requests

3. Query for the Gather Schema Statistics

4. Enter the appropriate parameters. This can be run for specific schemas by specifying the schema name or entering  ‘ALL’  to gather statistics for every schema in the database

5. Submit the Gather Schema Statistics program


Parameters :

Schema Name:
  Schema for which statistics are to be gathered. Specify ALL for all Oracle Applications schemas

Percent:
  The sampling percentage. If left blank, the default value of 10 is used. The valid range is from 0 to 100

Degree: 
 The degree of parallelism to be used for gathering statistics. If a Degree is not provided, it defaults to the minimum of parallel_max_servers and cpu_count.

Backup Flag:  NO BACKUP is used,
 then the GATHER_SCHEMA_STATS procedure will not backup the current statistics. This way the GATHER_SCHEMA_STATS procedure will run faster.

Restart Request ID: 
 In the case where the Gather Schema Statistics run fails due to whatever reasons, the concurrent request can be re-submitted and it will pick up where the failed run left off, if you provide the concurrent request_id of the failed run.
History Mode:  Last Run – History records for each object are maintained only for the last gather statistics run. Each subsequent run will overwrite the previous history record for the object. This is the default behavior

Gather Options:  
GATHER: All tables and indexes of the schema schema name are selected for stats gathering. This is the default

Modifications Threshold:  Applicable only to GATHER AUTO and LIST AUTO Options

Invalidate Dependent Cursors:  This flag indicates whether cursors dependent on the table being analyzed should be invalidated or not. By default, dependent cursors are invalidated.

Monday, 30 October 2017

How to check network latency in oracle R12.1.3


1. Navigate System Administrator Responsibility.
2. Navigate to Application
     Click on Network Test.




3. Click the ‘Clear Old Test Data’ button to purge previous test results from the database.

4. Specify the number of Trials and the Iterations for each trial for both the latency and bandwidth blocks. The default settings for both are 5 trials of 100 iterations each.

5. Select the Run Test button to perform the test.

Latency test:- 
It examines the time taken for a single packet to make a round trip from the client side application to the server.

 Bandwidth test:-
It examines the data rate to see how many bytes per second the network can transfer from the server to the client.




Note :- Oracle Recommends Network latency should be low and bandwidth should be high.

Tuesday, 17 October 2017

ORA-32004

Facing below issue on Oracle 12c database.

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

solution:-

Down Database.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Edit PFILE:-

Go to PFILE location.
[sujeet@oracle ~]$ cd $ORACLE_HOME/dbs


[sujeet@oracle dbs]$ vi initORCL.ora

commit below parameter.
#sec_case_sensitive_logon = FALSE

save pfile.

start database with pfile.

SQL> startup pfile=/apps/dba/db/tech_st/12.1.0.2/dbs/initORCL.ora
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2926472 bytes
Variable Size             872417400 bytes
Database Buffers         1258291200 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

SQL> create spfile from pfile;

File created.


SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down


SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2926472 bytes
Variable Size             872417400 bytes
Database Buffers         1258291200 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

Issue resolved.


Thursday, 12 October 2017

Upgrade Timezone version 10 to 18 in oracle

Upgrading timezone_file version manually during Oracle 12c upgrade

Some of us might have faced a situation to upgrade timezone_file version manually during Oracle 12c upgrade. Here are the steps to follow in Windows.

When you upgrade Oracle to 121020, you will have have an option in DBUA to upgrade the timezone data.





If you don’t check it, then, when you run “post_upgrade_checks.sql”, it will give you the below warning message.

**********************************************************************
Check Tag:     OLD_TIME_ZONES_EXIST
Check Summary: Check for use of older timezone data file
Fix Summary:   Update the timezone using the DBMS_DST package after upgrade is complete.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> Older Timezone in use
     Database is using a time zone file older than version 18.
     After the upgrade, it is recommended that DBMS_DST package
     be used to upgrade the 12.1.0.2.0 database time zone version
     to the latest version which comes with the new release.
     Please refer to My Oracle Support note number 977512.1 for details.

**********************************************************************

upgrade it manually

Upgrade Time zone version 10 to 18.

SQL> SELECT version FROM v$timezone_file;
  
  VERSION
----------
        10

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

PROPERTY_NAME                                                             VALUE
------------------------------------------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION                                             10
DST_SECONDARY_TT_VERSION                                        0
DST_UPGRADE_STATE                                                       NONE

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup upgrade
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size                  2926472 bytes
Variable Size             889194616 bytes
Database Buffers         1241513984 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

SQL> EXEC DBMS_DST.BEGIN_UPGRADE (18);

PL/SQL procedure successfully completed.

SQL> shut immediate

SQL> startup

SQL> SELECT version FROM v$timezone_file;

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


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.