DOYENSYS Knowledge Portal




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




Thursday, October 4, 2018

Script to find multiple plan hash values for the same SQL ID

select 
  SQL_ID 
, PLAN_HASH_VALUE 
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60)  ELA_MINS
from DBA_HIST_SQLSTAT 
where SQL_ID in (
'&sqlid') 
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS;

Output Post Processor is Down with Actual Process is 0 And Target Process is 1

If you see OPP is Down with Actual Process is 0 And Target Process is 1 then do the following

1. Shutdown concurrent server via command adcmctl.sh

2. To ensure concurrent manager down; check there is no FNDLIBR process running.
ps -ef | grep applmgr | grep FNDLIBR

3. Run adadmin to relink FNDSVC executable.

a. Invoke adadmin from command prompt
b. Choose option 2 (2. Maintain Applications Files menu)
c. Choose option 1 (1. Relink Applications programs )
d. Then type “FND” When prompted; ( Enter list of products to link (‘all’ for all                                       products) [all] : FND )
e. Ensure adrelink is exiting with status 0

4. Start Concurrent Managers using adcmctl.sh

Oracle EBS R12.2 weblogic server tips

1) Verify weblogic processes.

ps -ef|grep weblogic.Server
ps -ef|grep -i startWebLogic.sh


2) You can find the weblogic port from the below tag of context file.
$grep s_wls_adminport $CONTEXT_FILE


3) Verify weblogic port to see if the application is running.
netstat -anp|grep <weblogic_port>


4) Login to application to see if weblogic server is up and all the components are running.

http(s)://hostname:7001/console
http(s)://hostname:7001/em

5) How to find version of weblogic server:

(i).   set EBS target environment (RUN/PATCH)
(ii).  cd $FMW_HOME
(iii). grep for version.
[appltest@Hostname FMW_Home]$ grep -i version registry.xml
<?xml version="1.0" encoding="UTF-8"?><bea-product-information xmlns:fo="http://www.w3.org/1999/XSL/Format" xmlns:gpr="http://www.bea.com/ns/cie/gpr">
        <component name="Common Infrastructure Engineering" version="7.4.0.0" InstallDir="">
        <component name="WebLogic Server" version="10.3.6.0" InstallDir="/u01/oracle/TEST/fs1/FMW_Home/wlserver_10.3">
        <component name="Oracle Configuration Manager" version="10.3.5.0" InstallDir="">

Oracle EBS R12.2.x some cloning issues and fixes

1. Oracle pre-clone fails with the following error.
======================================================

START: Creating WLS config archive.
Script Executed in 1321 milliseconds, returning status 255
ERROR: Script failed, exit code 255

FIX
=====
To implement the solution, please execute the following steps:
1. Stop all services from patch and run editions
    You can use EBSapps.env to ensure that you are on the correct file edition
2. Restart your environment
3. Rerun adpreclone




2.clone files with below error:
==================================

#############################################################
Started ApplyAppsTier at Mon Aug 31 15:13:39 EDT 2015
Version:
        ApplyAppsTier.java      :       120.11.12020000.6
#############################################################
Found the directory : /u01/oracle/TEST/fs1/FMW_Home
Exiting cloning as FMW Home already exists..

FIX:
=====
remove or move  FMW_Home and rerun rapid clone






3.Clone fails with following error.
======================================

#############################################################
Started ApplyAppsTier at Mon Aug 31 15:18:25 EDT 2015
Version:
        ApplyAppsTier.java      :       120.11.12020000.6
#############################################################
Below Oracle Homes are already registered in the global inventory:
/u01/oracle/TEST/fs1/FMW_Home/Oracle_EBS-app1
/u01/oracle/TEST/fs1/FMW_Home/webtier
/u01/oracle/TEST/fs1/FMW_Home/oracle_common

FIX:
====
Make sure EBS environment is unset before running rapid clone. 

Disabling Concurrent Managers and Check Internal concurrent manager status.

1.Disabling Concurrent Managers.

Description:

Sometimes we have to disable some Concurrent Managers.
we are create a new single instance CLONE from a E-Business Suite.
We can do it from the forms of the application,

a) How to find if Concurrent managers are enabled:

SELECT CONCURRENT_QUEUE_NAME, enabled_flag, node_name 
FROM applsys.FND_CONCURRENT_QUEUES 
WHERE CONCURRENT_QUEUE_NAME LIKE '%<<Concurrent manager name>>%' AND enabled_flag = 'Y';

b) To disable one Concurrent manager using UPDATE query.

UPDATE applsys.FND_CONCURRENT_QUEUES SET enabled_flag = 'N' 
WHERE CONCURRENT_QUEUE_NAME = '<<Concurrent manager name>>';

2.Internal Concurrent Manager status.

Description:

We can find the status of Internal Concurrent Manager status with this script.
If it is down, then we need to take steps to turn it back on.

Prerequisites:

You must run it as user applsys or sys.

a) Query for check Internal concurrent manager status.

SELECT DECODE(RUNNING_PROCESSES, 1, 'UP', 'DOWN') STATUS 
FROM APPLSYS.FND_CONCURRENT_QUEUES
WHERE CONCURRENT_QUEUE_NAME = 'FNDICM';

Tuning SQL Statements Using SQL Tuning Advisor (without Enterprise Manager)

Below are the steps to use SQL Tuning Advisor for particular SQL.

1) Create tuning task using SQL_ID
2) Execute tuning task
3) Generate report for the tuning task

STEP 1:

CREATE tuning tasks for SQL_ID picked from <<<<AWR>>>>>

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 11716,
                          end_snap    => 11717,
                          sql_id      => 'f4am5zd2tc2ys',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 6000,
                          task_name   => 'dyfp8d71pjym8_tuning_task',
                          description => 'Tuning task for statement f4am5zd2tc2ys in AWR.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

CREATE tuning tasks for SQL_ID for running session  <<<<<Cursor Cache>>>>>


DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => 'f4am5zd2tc2ys',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 6000,
                          task_name   => 'f4am5zd2tc2ys_tuning_task',
                          description => 'Tuning task for statement f4am5zd2tc2ys');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

STEP 2:

SELECT task_name, status FROM dba_advisor_log WHERE task_name like 'f4am5zd2tc2ys_tuning_task%';


EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'f4am5zd2tc2ys_tuning_task');

STEP 3:

SET lines 200 pages 1000
SET LONG 999999999
SET longchunksize 200

SELECT DBMS_SQLTUNE.report_tuning_task('f4am5zd2tc2ys_tuning_task') from dual;


STEP 4: To drop the sql tuning tasks (OPTIONAL)



EXEC DBMS_SQLTUNE.drop_tuning_task(task_name => 'f4am5zd2tc2ys_tuning_task');

APEX INSTALLATION WITH HTTP(OHS) SERVER


APEX INSTALLATION WITH HTTP(OHS) SERVER

Description:

1.We have to install first apex in our database after we have to install http(OHS) server:

1.Apex Installation:

Unzip Apex files in specific PATH.

/u01/htp_new/apex_4.2.5_en.zip
UNZIP apex_4.2.5_en.zip 


  • You unzipped this zip file it can contain a many .sql files in a directory called apex.

Goto apex directory

* /u01/htp_new/apex  


  • login as sysdba to database.


create tablespace apex:

CREATE TABLESPACE TAPEX DATAFILE '/u01/app/oracle/oradata/HTTPNEW/APEX.dbf' SIZE 1000M;

Increase the system tablespace size:

alter database datafile '/u01/app/oracle/oradata/HTTPNEW/system01.dbf' resize 2000M;


Check which version is currently installed:

set lines 180
COL comp_name FOR A30
SELECT comp_name, version, status FROM dba_registry WHERE comp_id='APEX';

COMP_NAME                      VERSION             STATUS
------------------------------ ------------------------------ -----------
Oracle Application Express     3.2.1.00.12         VALID


install apex software:
-----------------------
@apexins.sql TAPEX TAPEX TEMP /i/ 
  

  •  Define the virtual image directory as /i/ for future updates.


Change the admin password for admin:
-------------------------------------
again login as sysdba from apex folder.

@apxchpwd 


  • for change the admin password the password contains the following characters:(!"#$%&()"*+,-/:;)


Check again the apex version in registry:
------------------------------------------
set lines 180
COL comp_name FOR A30
SELECT comp_name, version, status FROM dba_registry WHERE comp_id='APEX';

COMP_NAME                      VERSION              STATUS
------------------------------ ------------------------------  -----------
Oracle Application Express     4.2.5.0.0            VALID


Unlock the APEX_PUBLIC_USER ACCOUNT:

ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;

apex installation completed.


2.HTTP INSTALLATION:


COPY THE SOFTWARE IN THE SPECIFIC FOLDER AND RUN IT USING ./runInstaller.

./runInstaller

oracle_home set to /home/oracle/Middleware  (0r) set /u01/app/oracle/Middleware
ohs component name: ohs1
instance_name: instance1

after run the ./runInstaller the GUI Mode:
and make the steps are possible.

After installation the finishing stage provide a some Url's:

1.HTTP SERVER URL--http://Apex2020.doyen.com:7778
2.HTTP SERVER SSL URL---http://Apex2020.doyen.com:4444

Check if its working properly after installation:

cd /home/oracle/Middleware/Oracle_WT1/instances/instance1/bin


  • the bin folder contains the opmnctl program/


now run the opmnctl command:
@bin]./opmnctl status ---the following alive processes are execitables:

Processes in Instance: instance1
---------------------------------+--------------------+---------+------------------
ias-component                    | process-type       |     pid | status  
---------------------------------+--------------------+---------+-------------------
webcache1                        | WebCache-admin     |   12370 | Alive   
webcache1                        | WebCache                |   12369 | Alive   
ohs1                                   | OHS                          |   12368 | Alive   

note:
The following 2 commamnds are used to bounce the HTTP server components:

@bin]./opmnctl stopall ---is used to stop all the components.
opmnctl stopall: stopping opmn and all managed processes...

@bin]./opmnctl startall-- is used to start all the components.
opmnctl startall: starting opmn and all managed processes...


configure ohs for APEX:(HTTP SERVER)

We have to change the following parameters in dads.conf file

these are,

Alias /i/
PlsqlDatabaseConnectString
PlsqlDatabasePassword

copy the image folder from apex folder to Oracle_WT1/images

cp /u01/htp_new/apex/images /home/oracle/Middleware/Oracle_WT1/images  


The dads.config stored in the following directory path:

vi /home/oracle/Middleware/Oracle_WT1/instances/instance1/config/OHS/ohs1/mod_plsql/dads.conf

Alias /i/ "/home/oracle/Middleware/Oracle_WT1/images/"

<Location /pls/apex>
   Order deny,allow
   PlsqlDocumentPath docs
   AllowOverride None
   PlsqlDocumentProcedure         wwv_flow_file_mgr.process_download
   PlsqlDatabaseConnectString     192.168.112.147:1521:HTTPNEW TNSFormat 
   PlsqlNLSLanguage                     AMERICAN_AMERICA.AL32UTF8 ---------------------Character set
   PlsqlAuthenticationMode            Basic
   SetHandler                                  pls_handler
   PlsqlDocumentTablename          wwv_flow_file_objects$
   PlsqlDatabaseUsername            APEX_PUBLIC_USER -------------------------------Apex username
   PlsqlDefaultPage                        apex 
   PlsqlDatabasePassword             ************  ------------------------------------password
   PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize
   Allow from all
</Location>

-------------------------------------------------------------------------
After the change we have to bounce our http components:

@bin]./opmnctl stopall ---is used to stop all the components.
opmnctl stopall: stopping opmn and all managed processes...

@bin]./opmnctl startall-- is used to start all the components.
opmnctl startall: starting opmn and all managed processes...

@bin]./opmnctl status ---the following alive processes are executables:

Processes in Instance: instance1
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status  
---------------------------------+--------------------+---------+---------
webcache1                        | WebCache-admin     |   12370 | Alive   
webcache1                        | WebCache                |   12369 | Alive   
ohs1                                  | OHS                          |   12368 | Alive   

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

Finally We Login to apex console:

http://<HOSTNAME>:7778/pls/apex.



  

How to Generate auth token in Oracle public cloud

Go To compute console ->


Click Identity->Users


Click on username and then Auth tokens -> Generate token

Enter description and then “Generate Token”

And copy the value immediately and safe it as you cant get the same key again.

Query to check backup status

col dbsize_mbytes      for 99,999,990.00 justify right head "DBSIZE_MB"
col input_mbytes       for 99,999,990.00 justify right head "READ_MB"
col output_mbytes      for 99,999,990.00 justify right head "WRITTEN_MB"
col output_device_type for a10           justify left head "DEVICE"
col complete           for 990.00        justify right head "COMPLETE %" 
col compression        for 990.00        justify right head "COMPRESS|% ORIG"
col est_complete       for a20           head "ESTIMATED COMPLETION"
col recid              for 9999999       head "ID"

select recid
     , output_device_type
     , dbsize_mbytes
     , input_bytes/1024/1024 input_mbytes
     , output_bytes/1024/1024 output_mbytes
     , (output_bytes/input_bytes*100) compression
     , (mbytes_processed/dbsize_mbytes*100) complete
     , to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete
  from v$rman_status rs
     , (select sum(bytes)/1024/1024 dbsize_mbytes from v$datafile) 
 where status='RUNNING'
   and output_device_type is not null
/

ORA-03113: end-of-file on communication channel on DB startup


 
ORA-03113 could be signaled for any of these scenarios:


  •  Server machine crashed
  • Your server process was killed at O/S level
  •  Network problems
  •  Oracle internal errors / aborts on the server
  • Client incorrectly handling multiple connections

                
[oracle@RHEL ~]$ . rhel.env
[oracle@RHEL ~]$ sqlplus / as sysdba
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 4 09:44:17 2018

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                                  2255832 bytes
Variable Size                           234882088 bytes
Database Buffers    381681664 bytes
Redo Buffers                             7507968 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 3727
Session ID: 125 Serial number: 5



We can use Automatic Diagnostic Repository (ADRCI) OS command:

[oracle@RHEL ~]$ adrci

ADRCI: Release 11.2.0.4.0 - Production on Sat Aug 4 09:46:57 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"

Show all Homes:

adrci> show homes
ADR Homes:
diag/rdbms/rhel3/RHEL3
diag/rdbms/actcl/ACTCL
diag/rdbms/rhel2/RHEL2
diag/rdbms/intercl/ITERCL
diag/rdbms/intercl/INTERCL
diag/rdbms/bkpcl/BKPCL
diag/rdbms/rhelext/RHELEXT
diag/clients/user_oracle/host_2186336506_80
diag/clients/user_oracle/host_1270808414_80
diag/tnslsnr/RHELEXT/intercl
diag/tnslsnr/RHELEXT/rhelext
diag/tnslsnr/RHEL/rhel3
diag/tnslsnr/RHEL/rhel
diag/tnslsnr/RHEL/actcl
diag/tnslsnr/RHEL/rhel2
diag/tnslsnr/RHEL/intercl
diag/tnslsnr/RHEL/bkpcl

Set the Alertlog File Home Location

adrci> set home diag/rdbms/rhel3/RHEL3

Check the Alert Log File:

adrci> show alert -tail 100
2018-08-04 09:44:20.572000 +05:30
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_3
System name:Linux
Node name:RHEL.localhost.com
Release:2.6.32-431.el6.x86_64
Version:#1 SMP Sun Nov 10 22:19:54 EST 2013
Machine:x86_64
VM name:VMWare Version: 6
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/dbhome_3/dbs/spfileRHEL3.ora
System parameters with non-default values:
  processes                = 150
  sga_target               = 600M
  control_files            = "/u01/app/oracle/control05.ctl"
  db_block_size            = 8192
  compatible               = "11.2.0.4.0"
  log_archive_format       = "%t_%s_%r.dbf"
  db_recovery_file_dest    = "/u01/app/oracle/fast_recovery_area"
  db_recovery_file_dest_size= 4086M
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=RHEL3XDB)"
  shared_servers           = 5
  local_listener           = "LISTENER_RHEL3"
  audit_file_dest          = "/u01/app/oracle/admin/RHEL3/adump"
  audit_trail              = "DB"
  db_name                  = "RHEL3"
  open_cursors             = 300
  pga_aggregate_target     = 199M
  diagnostic_dest          = "/u01/app/oracle"
PMON started with pid=2, OS id=3679
PSP0 started with pid=3, OS id=3681
2018-08-04 09:44:21.684000 +05:30
VKTM started with pid=4, OS id=3683 at elevated priority
GEN0 started with pid=5, OS id=3687
VKTM running at (1)millisec precision with DBRM quantum (100)ms
DIAG started with pid=6, OS id=3689
DBRM started with pid=7, OS id=3691
DIA0 started with pid=8, OS id=3693
MMAN started with pid=9, OS id=3695
DBW0 started with pid=10, OS id=3697
LGWR started with pid=11, OS id=3699
CKPT started with pid=12, OS id=3701
SMON started with pid=13, OS id=3703
RECO started with pid=14, OS id=3705
MMON started with pid=15, OS id=3707
MMNL started with pid=16, OS id=3709
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 5 shared server(s) ...
ORACLE_BASE from environment = /u01/app/oracle
ALTER DATABASE   MOUNT
2018-08-04 09:44:25.929000 +05:30
Successful mount of redo thread 1, with mount id 1943908661
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
ALTER DATABASE OPEN
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=24, OS id=3729
2018-08-04 09:44:27.161000 +05:30
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
ARC1 started with pid=25, OS id=3731
ARC2 started with pid=26, OS id=3733
ARC3 started with pid=27, OS id=3735
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Errors in file /u01/app/oracle/diag/rdbms/rhel3/RHEL3/trace/RHEL3_ora_3727.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4284481536 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to '/u01/app/oracle/fast_recovery_area/RHEL3/archivelog/2018_08_04/o1_mf_1_97_%u_.arc'
Errors in file /u01/app/oracle/diag/rdbms/rhel3/RHEL3/trace/RHEL3_ora_3727.trc:
ORA-16038: log 1 sequence# 97 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/RHEL3/redo01.log'
USER (ospid: 3727): terminating the instance due to error 16038
System state dump requested by (instance=1, osid=3727), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/rhel3/RHEL3/trace/RHEL3_diag_3689_20180804094427.trc
Dumping diagnostic data in directory=[cdmp_20180804094427], requested by (instance=1, osid=3727), summary=[abnormal instance termination].
Instance terminated by USER, pid = 3727

 
SQL> startup nomount

ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                                  2255832 bytes
Variable Size                           234882088 bytes
Database Buffers    381681664 bytes
Redo Buffers                             7507968 bytes

SQL> alter system set db_recovery_file_dest_size = 12G scope=both;

System altered.

SQL> shut immediate
ORA-01507: database not mounted


ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                                  2255832 bytes
Variable Size                           234882088 bytes
Database Buffers    381681664 bytes
Redo Buffers                             7507968 bytes
Database mounted.
Database opened.