Myth and Misconception during Oracle EBusiness Suite Cloning/Refresh

As a Oracle Apps DBA, most of us are very familiar with the Oracle EBusiness Suite cloning/refresh process. Though there may be a few changes in perspective every one follow as per the client requirement. 

Overall; in simple terms we can divide the Cloning/Refresh process of an EBusiness Suite instance into following few steps at a high level:

1) Prepare the Source environment

2) Backup and copy the File system to Target Servers

3) Extract the backups and restore on Target server

4) Configure the Target environment.

One of the Myth that many of the Oracle Apps DBAs are unaware is that, whenever we run ADPRECLONE utility on the source environment, we have to be very careful while entering the apps password. 

Most may be thinking, what will happen at the max if I give wrong apps password, it might fail and I can re-run it again. But the fact is, it will make more damage than we expect/know.

It actually shuts down the database if we pass wrong apps password during adpreclone.pl dbTier on the db node. It will cause outage to your environment and thereafter escalations, etc. Just be careful!!!

Now lets look at it on apps node (Middle Tier). Someone might ask you what will happen if you pass wrong password to adpreclone.pl appsTier on the middle Tier. 

On the Middle Tier, even if we give wrong apps password, it will still continue without any issues.

Oracle Management Cloud

Introduction to Oracle Management Cloud

We have been seeing so many products of Oracle off late. A new suite from Oracle named Oracle Management Cloud is introduced.

Please do not get confused with cloud offerings of Oracle. This is a management suite and not any Cloud solution on the first note.

What is Oracle Management Cloud then?

Oracle Management Cloud is a suite of integrated monitoring, management, and analytics cloud offerings. What makes it more unique is it supports on-premise environments, Oracle Cloud and other third party cloud environments (AWS, Microsoft Azure etc) as well. 

How does it work?

Oracle Management Cloud (Aka OMC in short) will extract monitoring data from the infrastructure using its multiple agents (Cloud Agent, Gateway Agent, APM Agent, Data Collector etc) and it collects metrics, logs from Database Server, Application server, Exadata, OEM and it analyzes existing data using machine language and thus helps to increase efficiency and maximize performance. It will further eliminate multiple information silos in end-user and infrastructure data, resolve application issues faster, and run IT like a business. 

In simple Oracle Management Cloud suite provide real-time monitoring, alerting, rapid diagnostics, operational insight, and business analytics.

What are offerings?

Oracle Application Performance Monitoring: Provides a platform for monitoring and managing applications.
Oracle Infrastructure Monitoring: Monitors the status and health of the entire IT infrastructure from a single platform and alerts administrators about issues.
Oracle Database Management: Monitors the health and performance of Autonomous Databases and on-premises Oracle Databases on a unified platform. 

Oracle Orchestration: Automates cloud and on-premises infrastructure and executes tasks by calling REST API or third-party automation framework.
Oracle IT Analytics: Provides a 360-degree insight into the performance, availability, and capacity of applications and infrastructure investments. 

Oracle Log Analytics: Monitors, aggregates, indexes, and analyzes log data from applications and infrastructure.  

How about Licensing?

Oracle Management Cloud offerings are categorized and available in three license editions, and we can select one or more license editions, based on our business requirements. 

 

Oracle Management Cloud – Standard Edition
Oracle Management Cloud – Enterprise Edition
Oracle Management Cloud – Log Analytics Edition
 

How are these licenses different?

1) Within Standard Edition, we get the following offerings:

Oracle Application Performance Monitoring: Provides a platform for monitoring and managing applications.
Oracle Infrastructure Monitoring: Monitors the status and health of the entire IT infrastructure from a single platform and alerts administrators about issues.
Oracle Database Management: Monitors the health and performance of Autonomous Databases and on-premises Oracle Databases on a unified platform.
 

Note: The data collected is retained for 14 days. 

2) Within Enterprise Edition, following are the offerings:

It includes all the offerings of Standard Edition (Oracle APM, Infrastructure Monitoring and Database Management) plus
Oracle Orchestration: Automates cloud and on-premises infrastructure and executes tasks by calling REST API or third-party automation framework.
Oracle IT Analytics: Provides a 360-degree insight into the performance, availability, and capacity of applications and infrastructure investments.
 

Note: The data collected is retained for 13 months. 

3) Within Oracle Log Analytics: Monitors, aggregates, indexes, and analyzes log data from applications and infrastructure.
 

Note : It does not include offerings from Standard and Enterprise Editions.

The most important, it is a cloud based solution. Easy and fast to configure and use.

An Insight about TKPROF

What is TKPROF?

TKProf is an Oracle database utility used to format SQL Trace output into human readable format. The TKProf executable is located in the $ORACLE HOME/bin directory.

TKPROF output can be generated from a raw SQL Trace. It formats and summarizes the diagnostic information from the raw SQL Trace.TKPROF allows you to analyse a trace file in easy way to determine where time is being spent and what query plans are being used on SQL statements.

What does a TKPROF output file contains?

TKPROF contains :

SQL ID along with SQL Text that were executed for the traced session.

• Timing information for the execution count, CPU Time, Elapsed Time, Physical Reads, Logical Reads and the total number of rows returned.
• Wait Information such as the times waited, maximum wait and total waited for each database wait event, both for each SQL and for the whole period of the trace.
• Execution Plan -  The execution plan will also contain the actual tables accessed if the SQL uses views or synonyms.

Syntax of TKPROF:

tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ]

explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.

table=schema.tablename   Use 'schema.tablename' with 'explain=' option.

print=integer    List only the first 'integer' SQL statements.

insert=filename  List SQL statements and data inside INSERT statements.

sys=no           TKPROF does not list SQL statements run as user SYS.

sort=option      Set of zero or more of the sort options

Out of all available options, most widely used options for performance tuning are exeela, fchela, prsela.

ORA-00600: internal error code, arguments: [qesdpSigError], [adaptive join resolution failed], [309], [], [], [], [], [], [], [], [], []

Issue :

Noticing below ORA-00600 errors in the database alert log.

ORA-00600: internal error code, arguments: [qesdpSigError], [adaptive join resolution failed], [309], [], [], [], [], [], [], [], [], []

Cause:

This seems to be a known bug adaptive plans feature being enabled and the error is hit when running the query involving bitmap indexes with star transformation enabled.

Fix:

It seems to be fixed in 12.2 version through bug 21156276. 

Otherwise, we can implement below workaround:

alter system set "_optimizer_adaptive_plans" = FALSE ;

How to enable trace for a database session

This article lists the commands required to trace SQL statements executed by a user or an application.

Tracing a SQL session

Start session trace

To start a SQL trace for the current session, execute:

ALTER SESSION SET sql_trace = true;

You can also add an identifier to the trace file name for later identification:

ALTER SESSION SET sql_trace = true;
ALTER SESSION SET tracefile_identifier = sampletrace;

Stop session trace

To stop SQL tracing for the current session, execute:

ALTER SESSION SET sql_trace = false;

Tracing other user's sessions

We can use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to trace problematic database sessions.

In order to enable trace for other session, first thing we need is to identify their session sid and serial#.

Enable tracing for your selected process:

ALTER SYSTEM SET timed_statistics = true;
execute dbms_system.set_sql_trace_in_session(<sid>, <serial#>, true);

Now ask the user to reproduce the problem

Disable tracing for your selected process:

execute dbms_system.set_sql_trace_in_session(<sid>, <serial#>, false);

Location of Trace File:

We can identify the trace file for a specific session using the V$SESSION and V$PROCESS views.

SELECT p.tracefile
FROM   v$session s
       JOIN v$process p ON s.paddr = p.addr
WHERE  s.sid = <sid>;

Script to check the free space / Usage of ASM Disk Group

This post provides with 2 scripts. 

First to check the Usage of each ASM disk groups. 

Second to check the free space and percent of ASM disk groups.

Usage Details of ASM Disk Groups:

SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    180
SET PAGESIZE    50000
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN group_name             FORMAT a25           HEAD 'Disk Group|Name'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (GB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (GB)'
COLUMN pct_used               FORMAT 999.99        HEAD '% Used'
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL "Grand Total: " OF total_mb used_mb ON report
SELECT
    name                                     group_name
  , state                                    state
  , total_mb/1024                                 total_mb
  , (total_mb - free_mb)/1024                     used_mb
  , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
FROM
    v$asm_diskgroup
WHERE
    total_mb != 0
ORDER BY
    Name
/

Sample Output:

Free Space in ASM Disk Groups:

SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    180
SET PAGESIZE    50000
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN group_name             FORMAT a25           HEAD 'Disk Group|Name'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (GB)'
COLUMN free_mb                FORMAT 999,999,999   HEAD 'Free Size (GB)'
COLUMN pct_free               FORMAT 999.99        HEAD '% Free'
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL "Grand Total: " OF total_mb used_mb ON report
SELECT
    name                                     group_name
  , state                                    state
  , total_mb/1024                            total_mb
  , free_mb/1024                             free_mb
  , ROUND(((free_mb / total_mb))*100, 2)      pct_free
FROM
    v$asm_diskgroup
WHERE
    total_mb != 0
ORDER BY
    Name
/

Sample Output:

Script to verify UNDO usage of active database sessions

This post provides with a script to verify the UNDO usage by active database sessions.

select se.saddr,se.inst_id,se.sid,se.module, sum(tr.used_ublk*8192/1024/1024)"Used(MB)"
from gv$session  se, gV$transaction   tr
where tr.ses_addr = se.saddr and se.inst_id=tr.inst_id
group by se.saddr,se.module,se.inst_id,se.sid
/

 

The Sample Output of the script be like: