DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, January 25, 2016

                      adopmnctl.sh: exiting with status 206


If we face issue while starting opmn managed services like

opmnctl startall

adopmnctl.sh: exiting with status 206

Solution:

 Step 1. cd $ORA_CONFIG_HOME/10.1.3/j2ee/oacore/persistence/
 Step 2. rm -rf *
 Step 3. Repeat the above step for oafm. 
              The path will be cd $ORA_CONFIG_HOME/10.1.3/j2ee/oafm/persistence/

  Restart middle tier:

Friday, January 22, 2016

Recover Datafile from Hot Backup
==========================


[oracle@test ~]$ cd /u01/app/oracle/oradata/proddb/

[oracle@test proddb]$ ls -l
total 1035664
-rw-r----- 1 oracle oinstall   7061504 Jul 27 10:00 control01.ctl
-rw-r----- 1 oracle oinstall   7061504 Jul 27 10:00 control02.ctl
-rw-r----- 1 oracle oinstall   7061504 Jul 27 10:00 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Jul 27 09:56 example01.dbf
-rw-r----- 1 oracle oinstall  52429312 Jul 27 09:59 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Jul 27 09:49 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Jul 27 09:49 redo03.log
-rw-r----- 1 oracle oinstall 241180672 Jul 27 09:56 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 Jul 27 09:56 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Jul 25 15:11 temp01.dbf
-rw-r----- 1 oracle oinstall  26222592 Jul 27 09:56 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Jul 27 09:56 users01.dbf


SQL> startup

SQL> alter database begin backup;

Database altered.

Let's Copy take backup of datafiles.

[oracle@test proddb]$ cp *.dbf /u01/coldbkp/
[oracle@test proddb]$

Now we can check which files is been in backup state:

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 ACTIVE                 485758 06-JAN-16
         2 ACTIVE                 485758 06-JAN-16
         3 ACTIVE                 485758 06-JAN-16
         4 ACTIVE                 485758 06-JAN-16
         5 ACTIVE                 485758 06-JAN-16


Let's close the backup state.

SQL> alter database end backup;

Database altered.

Well now I will shutdown my database end drop one datafile.

Remember in this case didn't  happens any log switch.

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

I will remove USERS01.DBF :

[oracle@test proddb]$ rm users01.dbf

And let's Start.

SQL> startup

Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/proddb/users01.dbf'

As expect  error, ok in this case my database will be in MOUNT state so I just need to restore the file manually.

[oracle@test coldbkp]$ cp users01.dbf /u01/app/oracle/oradata/proddb/

If you try to open directly.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/proddb/users01.dbf'

SQL> recover datafile 4;
Media recovery complete.

SQL> alter database open;

Database altered.

Done.

Steps to recover data file block corruption using data file from standby
===================================================


Scenario :
========

Block corruption occurs in primary datafile , we dont have recent  archivelogs and  backups


Error :
=====

We receive the below error in alert log stating the file 52 block 192655 is corrupted


ORA-01578: ORACLE data block corrupted (file # 52, block # 192655)
Reread of blocknum=192655, file=xx /xbol_data5.dbf. found same corrupt data
Reread of blocknum=192655, file=xx/xbol_data5.dbf. found same corrupt data
Reread of blocknum=192655, file=xx/xbol_data5.dbf. found same corrupt data
Reread of blocknum=192655, file=xx/xbol_data5.dbf. found same corrupt data
Reread of blocknum=192655, file=xx/xbol_data5.dbf. found same corrupt data
Reading datafile 'xx/xbol_data5.dbf' for corruption at rdba: 0x0d02f08f (file 52, block 192655)
Reread (file 52, block 192655) found same corrupt data (no logical check)

Solution :
========

Please follow the below steps as per given order to recover the datafile.

1. PRIMARY SIDE

Step 1 :   switch logfile in primary node

alter system switch logfile;

Step 2:

select status,instance_name,database_role,open_mode from v$database,v$instance;

Step 3 :

RMAN > validate check logical database;
SQL>      select * from v$database_block_corruption;

Step 4 :

select file_id,file_name,tablespace_name from dba_data_files where file_id=57;

Step 5 :

select max(sequence#) from v$archived_log;

2. ON STANDBY DATABASE

Step 1 :  

select status,instance_name,database_role,open_mode from v$database,v$instance;

Step 2:

select max(sequence#) from v$archived_log where applied='YES';

3. ON PRIMARY DATABASE

Step 1 :  

shu immediate;

Step 2:

mv xx/a_media07.dbf xx/a_media07.dbf_06Octbkp


4. ON STANDBY DATABASE

Step 1 :  

backup as copy datafile 57 format 'xx/a_media07.dbf';

Step 2:

scp xx/ a_media07.dbf oraprod@144.11.7.15:xx/a_media07.dbf

5. ON PRIMARY DATABASE

Step 1 :  

startup mount

Step 2:

catalog start with 'xx/a_media07.dbf';

Step 3 :

restore datafile 57;

Step 4 :

recover datafile 57;

Step 5 :

alter database open;

Step 6 :

backup validate check logical database;
           OR
validate check logical datafile 57;

Step 7 :

select * from v$database_block_corruption;

Step 8 :

select max(sequence#) from v$archived_log;

6. ON PRIMARY DATABASE

Step 1 :

select status,instance_name,database_role,open_mode from v$database,v$instance;

Step 2 :

select max(sequence#) from v$archived_log where applied='YES';


Thursday, January 21, 2016

ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes





Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_02":  sys/******** parfile=tab.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 62.91 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

Solution 

expdp / directory=dir1 dumpfile=test_1.dmp logfile=test.log
or

expdp / directory=dir1 dumpfile=test_1.dmp,test_21.dmp,test_23.dmp, test_24.dmp logfile=test.log parallel=4

or

expdp / directory=dir1 dumpfile=test_%U.dmp logfile=test.log parallel=4


Tuesday, January 12, 2016

To find installed products,status and patch level in oracle



set pages 300
set lines 300
column application_name format a50
column application_short_name format a10
column  status format a15
column application_id format a5
column patch_level format a15
SELECT SUBSTR(a.application_name, 1,40) product_name 
, a.application_id 
, SUBSTR(application_short_name, 1,4) short_name, 
DECODE(b.status,'I','INSTALLED', 
DECODE (b.status,'S','SHARED', 
DECODE (b.status,'N','NOTINSTALLED',b.status))) status, patch_level 
FROM fnd_application_vl a, fnd_product_installations b WHERE a.application_id = b.application_id;


Query to find concurrent programs that are running in background




set pages 1000 lines 1000 pagesize 1000;
select a.request_id ,decode(a.phase_code,'R','Running','Normal') Phase, 
to_char(a.actual_start_date,'DD-MON-RRRR HH24:MI:SS') 
Start_time , substr(b.user_concurrent_program_name,1,40) Program
from apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs_tl b
where a.concurrent_program_id = b.concurrent_program_id and 
a.status_code = 'R'
order by Start_time;

Query to find the list of concurrent programs  for n number of days



SELECT * from promon.fnd_conc_req_history  WHERE (actual_start_date) BETWEEN TO_DATE ('01-06-2015 06:00:00','DD-MM-YYYY HH24:MI:SS')AND TO_DATE ('20-06-2015 18:00:00','DD-MM-YYYY HH24:MI:SS')

Slow Performance adcfgclone.pl appsTier - txkWfClone.sh/txkWfClone.sql 

This performance issue is due to high row counts in workflow tables.

Review the number of rows in the following tables.

select count(*) from wf_resources where name = 'WF_WEB_AGENT' and language = 'US';

select count(*) from WF_ITEM_ATTRIBUTE_VALUES;

select count(*) from WF_ITEM_ATTRIBUTES;

select count(*) from wf_activity_attr_values;

select count(*) from wf_process_activities;

select count(*) from wf_activity_attributes;

select count(*) from wf_notification_attributes;

select count(*) from wf_message_attributes;

 
Run the "Purge Obsolete Workflow Runtime Data" CCM request to reduce the number of rows in the Workflow tables.



Oc4j startup common errors and solutions


Error:

While starting oc4j you will get following error as ,

(SEVERE) Failed to set the internal configuration of the OC4J JMS Server with: XMLJMSServerConfig[file: $ORACLE_HOME/j2ee/home/config/jms.xml]

Solution:

Go to cd $ORACLE_HOME/j2ee/home/persistence/home_default_group_1
rm  jms.state ,*.lock and start again ,

Error:

No Suitable jdbc driver found

Solution:

Check for ojdbc.jar files present in /oc4j/j2ee/home/applib/ if it’s not exist copy the file from somewhere and put in the location.

Error:

Invalid arguments in Call

Solution:

check in the  schema_properties.properties file for space between = and parameter e.g =TEST = test

Error:

 NullSchema_properties.properties ( No such file or directory )

Solution:

EXPORT APEX_SCHEMA_PATH in fop.sh

export APEX_SCHEMA_PATH=/home/oracle/oc4j/j2ee/home/default-web-app/WEB-INF/

Or


Check the APEX_SCHEMA_PATH to correct location 

The data that defines the flexfield on this field may be inconsistent

ERROR
-----------------------
The data that defines the flexfield on this field may be inconsistent. Inform your system administrator that the function: KeyFlexfieldDefinitionFactory.getStructureNumber could not find the structure definition for the flexfield specified by Application = SQLGL, Code = GL# and Structure number =


STEPS
-----------------------
The issue can be reproduced at will with the following steps:

1. Go to the Manage Bank Accounts window
2. Go to the Account Controls and click on Update



CAUSE

There could be multiple causes for this issue:

1. Client was at an older version of the oracle.apps.ce.bankaccount.webui.AccountSimpleCO.java 120.18.12010000.6.

2. The application of a patch has rendered the Flexfields invalids

SOLUTION

To implement the solution, please execute the following steps in a test environment first:

OPTION 1:

1. Download and review the readme and pre-requisites for patch 18088801:R12.CE.B that has the Account Controls page AccountSimpleCO.java latest version
120.18.12010000.13.

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

3. Apply patch 18088801:R12.CE.B in a test environment.

4. Confirm the following file version:
AccountSimpleCO.java version 120.18.12010000.13.

You can use the following command to find out the version:
strings -a $JAVA_TOP/oracle/apps/ce/bankaccount/webui/AccountSimpleCO.class |grep '$Header'
or go to the Bank Account Accounts Control window and click on Help About this Page to confirm the version.

5. Retest the issue.

6. If the issue is resolved, please migrate the solution as appropriate to other environments.

 (OR)

OPTION 2:

1. a. Run this command (all on one line) to recreate the incorrect Flexfields:

FNDLOAD apps/password 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afffload.lct
$CE_TOP/patch/115/import/US/ce12adf.ldt DESC_FLEX

b. Bounce the instance. Retest the issue. 

Monday, January 11, 2016

Discoverer Error : CORBA protocal :Failed to bind session using IOR:null Hint

Users getting  below error while logging to Discoverer.
===========================================================

CORBA protocal :Failed to bind session using IOR:null Hint:An administrator can futher diagnose connection problems by running the
"checkdiscover" script under /d01/app/fmw_1/discoinst_1/Discoverer/<DISCOVERER_COMPONENT_NMA/util.

Solution:
============

STEP1 :
--------

Run checkdiscoverer.sh to diagnose the connection problem as shown below:

[oradisco@oraserv util]$ cd /u03/app/fmw_1/discoinst_1/Discoverer/Discoverer_discoinst_1/util
[oradisco@oraserv util]$ ./checkdiscoverer.sh

Initializing ORB...
ORB initialized successfully.

Checking for Discoverer "ServicesStatus"...
"ServicesStatus" is running.

Checking the adminstrator set soft limit on no. of sessions ...
Current value of soft limit = 50 sessions.
Hint: Administrators can increase the soft limit on Discoverer sessions by modifying
"//ias-component[id='Discoverer_discoinst_1']/process-type[id='SessionServer']/process-set/@maxprocs" attribute in the file /d01/app/fmw_1/discoinst_1/config/OPMN/opmn/opmn.xml

Checking for Discoverer Preference Server component...
Found Discoverer Preference Server component.

Binding to Discoverer Server...
Successfully bound to Discoverer Server.

Checking that the machine has only one network card...
Yes.


STEP 2:
--------

Check the number are disco processes:

[oradisco@oraserv ~]$ ps -fu oradisco|grep pref
oradisco      1263   581  0 08:57 ?        10:00:00 /d01/app/fmw_1/disco_1/bin/dis51ws -port 0 -opmn_ping_time 20 -preferenceHost localhost -preferencePort 1602 -uid 2615203462
oradisco      1689   581  0 19:57 ?        10:00:16 /d01/app/fmw_1/disco_1/bin/dis51ws -port 0 -opmn_ping_time 20 -preferenceHost localhost -preferencePort 1602 -uid 2618923534
oradisco      2264   581  0 00:03 ?        10:00:04 /d01/app/fmw_1/disco_1/bin/dis51ws -port 0 -opmn_ping_time 20 -preferenceHost localhost -preferencePort 1602 -uid 2615467389
oradisco      2289   581  0 14:00 ?        10:00:01 /d01/app/fmw_1/disco_1/bin/dis51ws -port 0 -opmn_ping_time 20 -preferenceHost localhost -preferencePort 1602 -uid 2617310623
.............................
...............................
..................................

[oradisco@doyen ~]$ ps -fu oradisco|grep pref|wc
     78    822   8217

STEP 3:
-------

[oradisco@oraserv conf]$ grep -i maxproc opmn.xml
               <process-set id="SessionServer" minprocs="0" maxprocs="50" restart-on-death="false" parallel-requests="true"/>

Increase the Maxprocs value in opmn.xml and bounce discoverer services.

[oradisco@oraserv conf]$ grep -i maxproc opmn.xml
               <process-set id="SessionServer" minprocs="0" maxprocs="100" restart-on-death="false" parallel-requests="true"/>



Thanks.Hope it will be helpful :)

Friday, January 8, 2016

ORA-01552: cannot use system rollback segment for non-system tablespace

ERROR:ORA-01552: cannot use system rollback segment for non-system tablespace 'APEX':
***********************************************************************

SOLUTION:
************
1) SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string

2) SQL> select segment_name, status from dba_rollback_segs;

SEGMENT_NAME            STATUS
------------------------------ ----------------
SYSTEM                            ONLINE
_SYSSMU1$                      OFFLINE
_SYSSMU2$                      OFFLINE
_SYSSMU3$                      OFFLINE
_SYSSMU4$                      OFFLINE
_SYSSMU5$                      OFFLINE
_SYSSMU6$                      OFFLINE
_SYSSMU7$                      OFFLINE
_SYSSMU8$                      OFFLINE
_SYSSMU9$                      OFFLINE
_SYSSMU10$                    OFFLINE


3) Change the UNDO MANAGEMENT to AUTO by executing this command.
   SQL> alter system set undo_management=auto scope=spfile;

System altered.

4) Restart the database.

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

SQL> startup
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 780056 bytes
Variable Size 162535656 bytes
Database Buffers 373293056 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.
SQL>



5) Check the Auto Management again and make sure its AUTO now.

SQL> show parameter undo_management

NAME                            TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string              AUTO

SQL> select segment_name, status from dba_rollback_segs;

SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                                    ONLINE
_SYSSMU10_552105004$           ONLINE
_SYSSMU9_2919646627$           ONLINE
_SYSSMU8_1480661775$           ONLINE
_SYSSMU7_3244898802$           ONLINE
_SYSSMU6_4142962160$           ONLINE
_SYSSMU5_1938678686$           ONLINE
_SYSSMU4_138086688$             ONLINE
_SYSSMU3_767986848$             ONLINE
_SYSSMU2_1323822658$           ONLINE
_SYSSMU1_986632303$             ONLINE

APEX LOGIN ERROR

ERROR:

I USED CORRECT USERNAME AND PASSWORD BUT STILL I FACED LOGIN ERROR

SOLUTION:

SQL> alter session set current_schema = APEX_040200;

Session altered.

SQL> select user_name, security_group_id from wwv_flow_fnd_user;

USER_NAME
--------------------------------------------------------------------------------
SECURITY_GROUP_ID
-----------------
ADMIN
       9.3612E+14


SQL> update wwv_flow_fnd_user set security_group_id = 10 where user_name = 'ADMIN';

1 row updated.

SQL> select user_name, security_group_id from wwv_flow_fnd_user;

USER_NAME
--------------------------------------------------------------------------------
SECURITY_GROUP_ID
-----------------
ADMIN
               10



SQL>  @apxchpwd.sql
Enter a value below for the password for the Application Express ADMIN user.


Enter a password for the ADMIN user              []

Session altered.

...changing password for ADMIN

PL/SQL procedure successfully completed.

STEPS BEFORE APPLYING AD PATCH

AD Patch Applying


1. The first step is to check whether the patch is already applied.
  
   Select * from ad_applied_patches where bug_number=”patch_number”;

2. If it is not applied, we are ready to apply the patch

3. Before applying patch we need to do some pre-requisites
   
   a) Take the invalid object counts in the application.
   b) Stop all the application services first.
  
4) Download the patch from oracle support by the given patch id.

5) Create a directory in the location $APPL_TOP/patches/ and move the patch to this directory.

6) Check for the service status. Whether all the services has been stopped.
   
a) Ps -ef | grep opmn
b) Ps -ef | grep httpd
c) Ps -ef | grep oc4j
d) Ps -ef | grep FNDLIBR

7) Once all the services are stopped

8) Run the utility
     
   Adadmin

   Change the application to Maintanence mode
  


UPGRADE FROM R12.1.1 to R12.1.3

APPS Upgrade


Required patches:

p9239089_R12.AD.B_R12_LINUX.zip
p10349415_R12.AD.B_R12_GENERIC.zip (Optional)
p9239090_R12_LINUX_1of6.zip
p9239090_R12_LINUX_2of6.zip
p9239090_R12_LINUX_3of6.zip
p9239090_R12_LINUX_4of6.zip
p9239090_R12_LINUX_5of6.zip
p9239090_R12_LINUX_6of6.zip
p9239095_R12_GENERIC.zip
p9822544_R12.MSC.B_R12_GENERIC.zip(Optional)

Post update patches are :

p9966055_R12.FND.B_R12_GENERIC.zip
p9817770_R12.ATG_PF.B_R12_LINUX.zip

The following information will provide upgradation process for Oracle Applications R12.1.1 to R12.1.3.

Step 1: Run adadmin and put Maintenance Mode

Step 2: Apply Prerequisite R12.AD.B.DELTA.3 Patch 9239089
Do the following Tasks before going to apply the Patch R12.AD.B.DELTA.3 Patch 9239089
Run the adgrants.sql script as a user that can connect as SYSDBA to grant privileges to selected SYS objects and create PL/SQL profile objects.

Usage:

1.Create $ORACLE_HOME/appsutil/admin on the database server.

2.Copy adgrants.sql (UNIX) from this patch directory to $ORACLE_HOME/appsutil/admin.Or, copy adgrants_nt.sql (Windows) from this patch directory to %ORACLE_HOME%\appsutil\admin.

3.Set the environment to point to ORACLE_HOME on the database server.

4.Use SQL*Plus to run the script:

UNIX:
$ sqlplus /nolog

SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql <APPS schema name>

Step 3: Apply R12.AD.B_R12_GENERIC.zip Patch 10349415

Step 4: Apply Oracle E-Business Suite Release 12.1.3 Patch 9239090

Step 5:. Apply Oracle E-Business Suite Online Help for 12.1.3 Release Update Pack patch 9239095

Step 6:.Apply Patch 9822544 for Advanced Supply Chain Planning. (Optional).This patch fixes the issue of collecting resource requirements of OPM batches WIP warehouse does not belong to resource warehouse.


Step 7: Apply post Update Patches:

Apply mandatory Patch 9817770 (9817770:R12.ATG_PF.B [POST-R12.ATG_PF.B.DELTA.3 CONSOLIDATEDPATCH].)

–Apply mandatory Patch 9966055 (9966055:R12.FND.B [TRANSLATED VERSION OF FNDSCSGN NOT LAUNCHED].)

[notice]After you have upgraded to Oracle E-Business Suite Release 12.1.3, perform the following post-update steps to update all database tier nodes with the code level, provided by Oracle E-Business Suite Release 12.1.3[/notice]

Step 8: Run Application and Database Environment files.

@Application Tier:
·         Run AutoConfig on the APPL_TOP.

·         Run the admkappsutil.pl utility to create the file appsutil.zip in the <INST_TOP>/admin/out directory.

perl <AD_TOP>/bin/admkappsutil.pl

@Database Tier:

Copy or FTP the appsutil.zip file to the <RDBMS ORACLE_HOME>. Uncompress appsutil.zip under the <RDBMS ORACLE_HOME>.
·         cd <ORACLE_HOME>
·         unzip -o appsutil.zip
·         Run AutoConfig on the <RDBMS ORACLE_HOME>.

Step 9:. Run adpreclone.pl on the database tier and the application tier
·         perl adpreclone.pl dbTier
·         perl adpreclone.pl appsTier

Step 10: Disable Maintenance Mode using adadmin and check the version using following sql statement.

SQL> select release_name from fnd_product_groups;
RELEASE_NAME
--------------------------------------------------
12.1.3


List Database Session Waits

SET PAUSE on
SET PAUSE 'Press Return to Continue'
SET LINESIZE 300
SET PAGESIZE 60
COLUMN username FORMAT A20
COLUMN event FORMAT A25
COLUMN wait_class FORMAT A20
 
SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_class,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM   v$session_wait sw,
v$session s
WHERE  s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC
/



Gather Statistic Collection Plan

Thursday, January 7, 2016

Deinstall 11gR2 GridHome and OracleHome

PSU Patch Apply Through OEM Cloud Control 12c

Installing Oracle Management Agent using Agent Push Method

Event Monitor slave process consuming more cpu

Creating Custom Concurrent Manager in Oracle Application R12:

Step to do:

Step 1Navigate to the oracle application Home page and find
 system administartor--->Concurrent:Manager---->Define


 Step 2:

i)In the manager field type as custom manager(you can have your desired manager name).
ii)In the short name field type the short name for the custom manager
iii)In the application name field type the name of the application like test production
iv)In the Testing field select custom manager
v)In the consumer group type DEFAULT_CONSUMER_GROUP



Step 3:

Click the work shift and specify the  work shift like 24 hours,number of processes and sleep seconds



Step 4:

 In the specialization rules form specify the rules like include/exclude,type,Application etc.



Step 5:

Now navigate to the administer concurrent managers and scroll down you will find the created manager in deactivated status



Step 6:

Active the manager by clicking the activate tab at the bottom of the form




Step 7:

Now you can see that the manager is activated  with the actual and target value as same.





To Check the Hidden parameter in oracle database

SET PAGESIZE 60
SET LINESIZE 300

COLUMN ksppinm FORMAT A50
COLUMN ksppstvl FORMAT A50

SELECT
  ksppinm,
  ksppstvl
FROM
  x$ksppi a,
  x$ksppsv b
WHERE
  a.indx=b.indx
AND
  substr(ksppinm,1,1) = '_'
AND ksppinm like '%<hidden parameter name>%'
ORDER BY ksppinm
/
Updating the Oracle JDBC driver in Oracle Weblogic 12c server

Steps to do:

1. Download the new Oracle JDBC driver and move the JDBC driver file to /tmp

2. Shut down the weblogic Admin server and Managed Server

3. Login to the weblogic server

[wls12c@prod01] $ cd $WLS_HOME/Server/lib

4. Backup the old JDBC driver file.

[wls12c@prod01 lib ] $ mv ojdbc6.jar ojdbc6.bkp

5. Now move the new JDBC driver file from /tmp to $WLS_HOME/Server/lib

[wls12c@prod01 tmp ] $  mv ojdbc6.jar $WLS_HOME/Server/lib

6. Now start the Admin server and Managed Server







ORA-28040: No matching authentication protocol when running with Oracle Database 12c

Issue:

ORA-28040: No matching authentication protocol when using the Test connection or full connection in the instance.

Oracle Database 12c is fully supported as an OMR with the vcops adapter. Unless that database is running as ALLOWED_LOGON_VERSION_SERVE=12a

12a is the highest level of security supported by 12c and requires the 12.0.2 db client or jdbc running with JDK 8. See here for reference:



Solution: 

Guide customer in changing ALLOWED_LOGON_VERSION_SERVER to 12 or lower in the sqlnet.ora file:

The sqlnet.ora file is in $ORACLE_HOME/network/admin 

(Note this may not be there by default, if so default settings for the listener are used)

[oracle@]$ pwd

/apps/oracle/db12c/network/admin

[oracle@]$ ls

listener14091810AM2151.bak
listener.ora
shrept.lst
tnsnames14091810AM2151.bak
tnsnames.ora
listener14091811AM3605.bak
samples
sqlnet.ora
 tnsnames14091811AM3605.bak

[oracle@]$ cat sqlnet.ora


SQLNET.ALLOWED_LOGON_VERSION=8
(which means the from db version 8 it allows the jdbc connection to latest version)



Change SQLNET.ALLOWED_LOGON_VERSION_SERVER to 9,10,11,or 12( for particular db version)


Restart the listener:

 lsnrctl start
 lsnrctl stop

APEX UPGRADE FROM 5.0 to 5.0.1

STEP:1
              Download PATCH:21364820 for Upgrading from 5.0 to 5.0.1
 https://support.oracle.com/epmos/faces/PatchSearchResults?_adf.ctrl-state=w60shktvs_9&_afrLoop=354215820387694

STEP:2
  After downloading UNZIP the PATCH
         $unzip <PATCH>

STEP:3
  Connect tp SQL by
  Sqlplus / as sysdba

STEP-4:
  Disable the HTTP port number.
SQL>EXEC DBMS_XDB.SETHTTPPORT(0);
SQL>COMMIT;

STEP-5
 After disabling the port we have to run the @apxpatch.sql in the patch location.
     SQL>@apxpatch.sql

STEP-6
To update the images,run the @apxldimg.sql in the patch location.
SQL> @apxldimg.sql /home/oracle

STEP-7
Enable the HTTP port after updating the image.
SQL>EXEC DBMS_XDB.SETHTTPPORT(8080);
SQL>COMMIT;

STEP-8
Verify the port number by using following command.
SQL>SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

STEP-9
 All steps are over now we can open the APEX-5.0.1 using URL and PORT no.
Eg:http://192.168.1.114:8080/apex

COMMANDS:
To check APEX version:

SQL>SELECT VERSION_NO FROM APEX_RELEASE;

Steps to recover Applications context file if it is corrupted or deleted Accidentally:

Steps to recover Applications context file if it is corrupted ordeleted Accidentally:

Oracle Applications Context file is corrupted or deleted accidentally, then  need to follow the below steps to recover the Context file.

To retrieve the Applications tier context file:

 *  Run the adclonectx.pl as shown below:
 
                 perl /clone/bin/adclonectx.pl retrieve

*   On being prompted for the context file to be retrieved, select the option of retrieving the Applications tier context file that has been lost and retrieve it to the default location specified by the script.

*   The above command (adclonectx.pl can be used only when INST_TOP the is still intact. In case that has also been lost accidentally, the Applications tier context file may be retrieved as follows:

Execute the following command on the Database tier:

            perl /appsutil/clone/bin/adclonectx.pl retrieve 


On being prompted for the context file to be retrieved, select the option of retrieving the Applications tier context file that has been lost.

While confirming the location for the context file, set it to any existing directory with write permission.

Once the context file has been generated in the specified location, move it to the location specified for the context file in the context variable 's_contextfile'.

To retrieve the Database tier context file:  


Execute the following command on the Database tier:

       perl /appsutil/clone/bin/adclonectx.pl retrieve

On being prompted for the context file to be retrieved, select the Database tier context file and retrieve it to the default location specified by the script.








Critical notification alert received for heap usage metric for the Particual target in OEM 12c.

Reason: 
Oracle Enterprise Manager 12c heap max value is less

Message Alert:
Host=prod01.domain.local
Target type=Oracle WebLogic Server
Target name=/EMGC_GCDomain/GCDomain/EMGC_OMS1
Categories=Capacity
Message=The heap usage is 86%. 
Severity=Warning
Event reported time=Jan 4, 2016 7:46:26 AM EST
Operating System=Linux
Platform=x86_64
Event Type=Metric Alert
Event name=jvm:heapUsedPercentage.value 
Metric Group=JVM Metrics
Metric=Heap Usage (%)
Metric value=86
Key Value=
Rule Name=prod01 Incident Management,EVENTRULE1
Rule Owner=SYSMAN

Steps to do:

1. Get the values of below parameters.

$ emctl get property -name OMS_HEAP_MIN
$ emctl get property -name OMS_HEAP_MAX
$ emctl get property -name OMS_PERMGEN_MIN
$ emctl get property -name OMS_PERMGEN_MAX

2. Increase the OMS_HEAP_MAX parameter value

$ emctl set propery -name OMS_HEAP_MAX -value 3000M

3. Bounce the Oracle Enterprise Manager 12c

$ emctl stop  oms -all
$ emctl start oms