Configure Oracle Wallet at Client location

SHAREDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = consdb121-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sharedb)
    )
  )


SQL> conn system/oracle@SHAREDB
Ú‘±‚³‚ê‚Ü‚µ‚½B

1.Create wallet on client

[oracle@consdb121n1 tmp]$
/u01/app/oracle/product/12.1.0/dbhome1/bin/mkstore -wrl /tmp -create
Oracle Secret Store Tool: ƒo[ƒWƒ‡ƒ“12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights
reserved.

password:Oracle123#
re-enter password :Oracle123#

2.Check that the file was created

[oracle@consdb121n1 admin]$ cd /tmp
[oracle@consdb121n1 tmp]$ ls -lrt *wallet*
-rw-rw-rw- 1 oracle oinstall   0  8ŒŽ  6 16:06 2015 ewallet.p12.lck
-rw------- 1 oracle oinstall  75  8ŒŽ  6 16:06 2015 ewallet.p12
-rw-rw-rw- 1 oracle oinstall   0  8ŒŽ  6 16:06 2015 cwallet.sso.lck
-rw------- 1 oracle oinstall 120  8ŒŽ  6 16:06 2015 cwallet.sso

3.

[oracle@consdb121n1 tmp]$ mkstore -wrl /tmp -createCredential
SHAREDB_TMP system
Oracle Secret Store Tool: ƒo[ƒWƒ‡ƒ“12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights
reserved.

secret/password is missing
secret/password:oracle
re-enter secret/password:oracle
oracle wallet password:Oracle123#
Create credential oracle.security.client.connect_string1

4.Check that the filesize has increased

[oracle@consdb121n1 tmp]$ ls -lrt *wallet*
-rw-rw-rw- 1 oracle oinstall   0  8ŒŽ  6 16:06 2015 ewallet.p12.lck
-rw-rw-rw- 1 oracle oinstall   0  8ŒŽ  6 16:06 2015 cwallet.sso.lck
-rw------- 1 oracle oinstall 536  8ŒŽ  6 16:10 2015 ewallet.p12
-rw------- 1 oracle oinstall 581  8ŒŽ  6 16:10 2015 cwallet.sso

5.Copy connectivity related files to /tmp on client

[oracle@consdb121n1 tmp]$ cat  /tmp/sqlnet.ora
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
  (DIRECTORY = /tmp)
  )
 )


cp /u01/app/oracle/product/12.1.0/dbhome1/network/admin/tnsnames.ora
/tmp/tnsnames.ora

[oracle@consdb121n1 tmp]$ cat /tmp/tnsnames.ora
# tnsnames.ora Network Configuration File:
/u01/app/oracle/product/12.1.0/dbhome1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

SHAREDB_TMP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = consdb121-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sharedb)
    )
  )


6.connection

export TNS_ADMIN=/tmp
sqlplus system/oracle@SHAREDB_TMP

7.connection

export TNS_ADMIN=/tmp
sqlplus /@SHAREDB_TMP

SQL*Plus: Release 12.1.0.2.0 Production on –Ø 8ŒŽ 6 16:40:38 2015

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

ÅI³íƒƒOƒCƒ“ŽžŠÔ: –Ø 8ŒŽ  06 2015 16:39:22 +09:00


Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
Production
With the Partitioning, Real Application Clusters, Automatic Storage
Management, OLAP,
Advanced Analytics and Real Application Testing options
‚ɐڑ±‚³‚ê‚Ü‚µ‚½B


8. Check if whether commenting-out SSL_CLIENT_AUTHENTICATION and SSL_VERSION is okay

[oracle@consdb121n1 tmp]$ cat /tmp/sqlnet.ora
SQLNET.WALLET_OVERRIDE = TRUE
#SSL_CLIENT_AUTHENTICATION = FALSE
#SSL_VERSION = 0

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
  (DIRECTORY = /tmp)
  )
 )

SQL*Plus: Release 12.1.0.2.0 Production on –Ø 8ŒŽ 6 16:42:29 2015

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

ÅI³íƒƒOƒCƒ“ŽžŠÔ: –Ø 8ŒŽ  06 2015 16:40:38 +09:00


Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
Production
With the Partitioning, Real Application Clusters, Automatic Storage
Management, OLAP,
Advanced Analytics and Real Application Testing options
‚ɐڑ±‚³‚ê‚Ü‚µ‚½B

Cascaded Standby Databases in Oracle 12c

Cascaded Standby Databases in Oracle 12c

There are new Possibilities for cascading Standby Databases in Oracle 12c. The main Differents between Oracle 12c and the previous Releases are:

1. Real-Time Cascading
2. Far Sync Standby Database
3. Data Guard Broker now supports cascaded Standby Database

we can only cascade a Standby Database from a Physical Standby Database.

Real-Time Cascading

It is now possible to forward Redo in Real-Time Mode from the first to the cascaded Standby Database. So the Redo Record is forwarded to the cascaded Standby Database once written into a Standby RedoLog of the first Standby Database.

Non Real-Time Cascading means that the whole Log Sequence is transferred to the terminal Standby Database(s) after a Log Switch on the Primary Database.

First of all setup a Data Guard Environment as usual to the cascading Standby Database. The Log Transport Method should be ‘SYNC’ and Standby RedoLogs must be configured on the cascading Standby Database. Once you created the cascaded Standby Database you can now setup the cascading Log Transport Services. Here are some Hints for correct Setup:

• Primary, Cascading and Cascaded Standby Database db_unique_name must be present in the dg_config of log_archive_config on all the Databases
• Setup log_archive_dest_n on the cascading Standby Database to serve the cascaded (terminal) Standby Databases using the Attribute ‘valid_for=(STANDBY_LOGFILES,STANDBY_ROLE)’
• You can toggle between Real-Time and Non Real-Time Cascading using the Log Transport Method.

ASYNC = Real-Time Cascading

SYNC = Non Real-Time Cascading

• You can only use log_archive_dest_1 until log_archive_dest_10 for Non Real-Time Cascading Destinations where all log_archive_dest_n’s can be used for Real-Time Cascading on the Cascading Standby Database
• The Cascading Standby Standby can be in any Protection Mode
• A Cascading Standby Database can serve one or multiple terminal Standby Databases
• FAL_SERVER on the cascading Standby Database should be set to the Primary or any other Standby Database served by the Primary Database directly
• FAL_SERVER on the terminal Standby Database should be set to the cascading Standby Database or the Primary Database

ORA-3136: WARNING Inbound Connection Timed Out

The "WARNING: inbound connection timed out (ORA-3136)" in the alert log indicates that the client was not able to complete  the  authentication process within the period of time specified by the parameter SQLNET.INBOUND_CONNECT_TIMEOUT. 

You might also see the errors ORA-12170 or TNS-12535 in the sqlnet.log that is generated on the server.
Check $ORACLE_HOME/network/log for this file.  This entry should contain client address from which the timeout originated and may be helpful in determining how to troubleshoot the issue.  Some applications or JDBC thin driver applications may not have these details.  The sqlnet.log file is not generated  by default in 11g and newer.  

From 10.2.0.1 onwards the default setting for the parameter SQLNET.INBOUND_CONNECT_TIMEOUT is 60 seconds.  If the client is not able to authenticate within 60 seconds, the warning would appear in the alert log and the client connection will be terminated.

The following are the most likely reasons for this error -

1. Server gets a connection request from a malicious client which is not supposed to connect to the database.  In this case the error thrown would be the expected and desirable behavior. You can get the client address for which the error was thrown in the sqlnet.log file that is local to the database.
2. The server receives a valid client connection request but the client takes a long time to authenticate more than the default 60 seconds.
3. The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified.

To understand what is causing this issue, following checks can be done 

The default value of 60 seconds is good enough in most conditions for the database server to authenticate a client connection. If it is taking longer, then it's worth checking the following items before implementing the workaround:

1. Check whether local connection on the database server is successful & quick.
2. If local connections are quick ,then check for underlying network delay with the help of your network administrator.
3. Check whether your Database performance has degraded in anyway.
4. Check alert log for any critical errors for eg, ORA-600 or ORA-7445 and get them  resolved first.
These critical errors might have triggered the slowness of the database server.

It is often necessary to increase the values for INBOUND CONNECT TIMEOUT at  both the listener and the database in order to resolve this issue.    It is usually advisable to set the database (sqlnet.ora) value slightly higher than the listener (listener.ora).    The authentication process is more demanding for the database than the listener.

To set these parameters to use values higher than the default of 60 seconds, follow these instructions and restart the listener.  There is no need to restart Oracle:

Edit the server side sqlnet.ora file and add this parameter:

SQLNET.INBOUND_CONNECT_TIMEOUT=<n>  Where <n> is the value in seconds.

E.g.:

SQLNET.INBOUND_CONNECT_TIMEOUT = 120

Edit the listener.ora file and add this parameter: 

INBOUND_CONNECT_TIMEOUT_<listenername> = <n>  Again, where <n> is the timeout value in seconds. 

For example if the listener name is LISTENER then use:

INBOUND_CONNECT_TIMEOUT_LISTENER = 110

From Oracle version 10.2.0.1 onwards the default value of INBOUND_CONNECT_TIMEOUT_<listenername> is 60 seconds. For previous releases it is zero or OFF by default.

How to check whether inbound timeout is active for the listener and database server:

For example,  INBOUND_CONNECT_TIMEOUT_<listener_name> =110

You can check whether the parameter is active or not by simply doing telnet to the listener port.
$ telnet <database server IP> <listener port>
for eg.

$ telnet 123.23.23.23 1521

The telnet session should disconnect after 110 seconds which indicates that the inbound connection timeout for the listener is active.

Alternatively, check at the LSNRCTL prompt using:

LSNRCTL>set current_listener <listener_name>
LSNRCTL>show inbound_connect_timeout

To check whether database server SQLNET.INBOUND_CONNECT_TIMEOUT is active:
Eg.

SQLNET.INBOUND_CONNECT_TIMEOUT=120

a. For Dedicated server setup, enable the support level sqlnet server tracing will show the timeout value as below:

niotns: Enabling CTO, value=120000 (milliseconds) <== 120 seconds
niotns: Not enabling dead connection detection.
niotns: listener bequeathed shadow coming to life...

b. For shared Server setup,
$ telnet <database server IP> <dispatcher port>
Example.

$ telnet 123.23.23.23  51658

The telnet session should disconnect after 120 seconds which indicates that the sqlnet.inbound_connect_timeout is active.

Oracle E-Business Suite upgrade to 12.1.3

Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.1.1) for Linux x86-64 (Doc ID 761566.1)

Oracle E-Business Suite Release 12.1.3 Readme (Doc ID 1080973.1)

 export ORACLE_SID=PROD
  export ORACLE_HOME=/scratch/EBS12.1.1/db/tech_st/11.1.0
  export PATH=$PATH:$ORACLE_HOME/bin
   . /scratch/EBS12.1.1/apps/apps_st/appl/APPSPROD_ofss2311720.env

To apply Oracle E-Business Suite Release 12.1.3, follow these steps: 1.    Use AutoPatch to apply R12.AD.B.DELTA.3 Patch 9239089, before you complete any of the other steps in this section. Do not merge this patch with any other patch. R12.AD.B.Delta.3 must be applied separately. Refer to My Oracle Support Knowledge Document 1077769.1, Oracle E-Business Suite Applications DBA Readme, Release 12.1.3 for more information about R12.AD.B.Delta.3. 2.    Use AutoPatch to apply Oracle E-Business Suite Release 12.1.3 Patch 9239090 and follow the instructions in the patch readme file. 3.    Use AutoPatch to apply the latest consolidated online help Patch 9239095 and follow the instructions in the patch readme file.

2. Installation packs * Patch 9239089(R12.AD.B.DELTA.3) * Patch 9239090 12.1.3 RELEASE UPDATE PACK * Patch 9239095 Oracle E-Business Suite Online Help for 12.1.3 Release Update Pack * Patch 9817770 POST-R12.ATG_PF.B.DELTA.3 CONSOLIDATED PATCH * Patch 9966055 1OFF:12.1.3:TRANSLATED VERSION OF FNDSCSGN NOT LAUNCHED

Step1. Run utility adadmin utility and change maintenance mode to enable Steps 2: Apply patch 9239089  (R12.AD.B.DELTA.3) Patch R12.AD.B.delta.3: Oracle Applications DBA 12.1.3 Product Release Update Pack Oracle E-Business Suite Applications DBA Readme, Release 12.1.3 (R12.AD.B.DELTA.3, Patch 9239089) (Doc ID 1077769.1) https://updates.oracle.com/Orion/Services/download?type=readme&aru=12062459 Pre-install Tasks You must shut down all Application tier services before performing the tasks in this section. Instructions for running AD Grants Run the adgrants.sql script as a user that can connect as SYSDBA to grant privileges to selected SYS objects and create PL/SQL profiler 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> Apply The Patch -- Run ADAMIN utility and enable maintenance mode Apply patch 9239089 This patch contains the following unified driver file to be applied with AutoPatch:     u9239089.drv (This is the unified driver) 1.  change to patch directory 2.  run utility adpatch and give above drv filename when ask. Apply patch 9239090 Patch 12.1.3: ORACLE E-BUSINESS SUITE 12.1.3 RELEASE UPDATE PACK https://updates.oracle.com/Orion/Services/download?type=readme&aru=12054750 Apply patch This patch contains the following unified driver file to be applied with AutoPatch:     u9239090.drv (This is the unified driver) Apply patch 9239095 Patch 9239095: Oracle E-Business Suite Online Help for 12.1.3 Release Update Pack https://updates.oracle.com/Orion/Services/download?type=readme&aru=12062250 Apply patch This patch contains the following unified driver file to be applied with AutoPatch:     u9239095.drv (This is the unified driver) Steps 5: Apply   Patch 9817770 Steps 6: Apply Patch 9966055 Steps 7: Apply patch 8919491 Step 7: Change maintenance mode Step8: start application processes

  Post-Update Steps

1.    Apply post-install Oracle E-Business Suite Applications Technology patches. (Required)

o    Apply mandatory Patch 9817770:R12.ATG_PF.B (POST-R12.ATG_PF.B.DELTA.3 CONSOLIDATED PATCH).

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

 

Patch 9817770: POST-R12.ATG_PF.B.DELTA.3 CONSOLIDATED PATCH

The following should be installed in the order shown before installing this patch.

1	R12.AD.B.delta.3	Oracle Applications DBA 12.1.3 Product Release Update Pack	(Install on Oracle Applications DBA)
2	R12.ATG_PF.B.delta.3	Oracle Applications Technology 12.1.3 Product Family Release Update Pack

Patch 9966055: 1OFF:12.1.3:TRANSLATED VERSION OF FNDSCSGN NOT LAUNCHED

Prerequisite Patches

The following should be installed in the order shown before installing this patch.

1	R12.AD.B.delta.3	Oracle Applications DBA 12.1.3 Product Release Update Pack	(Install on Oracle Applications DBA)
2	R12.ATG_PF.B.delta.3	Oracle Applications Technology 12.1.3 Product Family Release Update Pack	(Install on Oracle Applications Technology Family)

Patch R12.ATG_PF.B.delta.3: Oracle Applications Technology 12.1.3 Product Family Release Update Pack

 Prerequisite Patches

R12.AD.B.delta.3

Oracle Applications DBA 12.1.3 Product Release Update Pack

(Install on Oracle Applications DBA)

-- Run ADAMIN utility and disable maintenance mode
--Start the EBS processes
--Login and check the page

SQL> select release_name from apps.fnd_product_groups;

RELEASE_NAME

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

12.1.3

SQL>

SQL> select bug_number from ad_bugs where bug_number in ('9239089','9239090','9817770','9966055');

BUG_NUMBER

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

9239089

9239090

9817770

9966055

SQL>

   

   

   col PATCH_NAME format a10

col PATCH_TYPE format a10

col DRIVER_FILE_NAME format a15

col PLATFORM format a10

select AP.PATCH_NAME, AP.PATCH_TYPE, AD.DRIVER_FILE_NAME, AD.CREATION_DATE,  AD.PLATFORM,AL.LANGUAGE

from AD_APPLIED_PATCHES AP, AD_PATCH_DRIVERS AD, AD_PATCH_DRIVER_LANGS AL

where AP.APPLIED_PATCH_ID = AD.APPLIED_PATCH_ID

and AD.PATCH_DRIVER_ID = AL.PATCH_DRIVER_ID

PATCH_NAME PATCH_TYPE DRIVER_FILE_NAM CREATION_DATE      PLATFORM   LANG

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

9239089    PATCH-SET  u9239089.drv    12-JUN-15          LINUX      US

9239090    MAINTENANC u9239090.drv    12-JUN-15          LINUX      US

           E-PACK

9817770    ONE-OFF    u9817770.drv    12-JUN-15          LINUX      US

9966055    ONE-OFF    u9966055.drv    12-JUN-15          GENERIC    US

8919491    PATCH-SET  u8919491.drv    12-JUN-15          LINUX      US

9239095    ONE-OFF    u9239095.drv    12-JUN-15          GENERIC    US

-----Troubleshooting

  

  Application Tier Startup/Shutdown Scripts
Depending on your AD Version these will be in

OAD_TOP/admin/scripts/ SID_hostname

adalnctl.sh Apps Listener Control Script
adapcctl.sh Apache/Web Server Control Script
adcmctl.sh Concurrent Manager Control Script
addisctl.sh Discoverer Control Script
adfrmctl.sh Forms server Control Script
adrepctl.sh Report Server Control Script
adstpall.sh Stop All Middle/Application Tier
adstrtall.sh Start All Middle/Application Tier

Login page

http://ofss2311690.in.oracle.com:8005/OA_HTML/

http://ofss2311720.in.oracle.com:8001/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE

Username/Password: SYSADMIN/sysadmin

[debaranw@ssssss scripts]$  ./adapcctl.sh status You are running adapcctl.sh version 120.7.12010000.2 Checking status of OPMN managed Oracle HTTP Server (OHS) instance ... Processes in Instance: VIS_localhost.localhost.in.oracle.com ---------------------------------+--------------------+---------+--------- ias-component                    | process-type       |     pid | status ---------------------------------+--------------------+---------+--------- OC4JGroup:default_group          | OC4J:oafm          |    9813 | Alive OC4JGroup:default_group          | OC4J:forms         |    9740 | Alive OC4JGroup:default_group          | OC4J:oacore        |    9654 | Alive HTTP_Server                      | HTTP_Server        |    9595 | Alive adapcctl.sh: exiting with status 0 adapcctl.sh: check the logfile /scratch/EBS12.1.1/inst/apps/VIS_localhost/logs/appl/admin/log/adapcctl.txt for more information ... [debaranw@xxxxxx scripts]$

Resize datafiles to minimum

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a75 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
       ceil( (nvl(hwm,1)*8)/1024/1024 )  || 'm;' cmd
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
  and ceil( blocks*&&blksize/1024/1024) -
      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

Oracle Database Size

SET FEEDBACK OFF
SET HEADING OFF
SET AUTOPRINT OFF
–SET NOTIFY OFF
SET VERIFY OFF
PROMPT ……………………………………………………..

SELECT    ‘** TableSpace Usage Snap For ‘
|| NAME
|| ‘ Instance Taken On ‘
|| RTRIM (TO_CHAR (SYSDATE, ‘Day’))
|| ‘ ‘
|| TO_CHAR (SYSDATE, ‘DD-MON-YYYY HH24:MI:SS’)
|| ‘ ** ‘
FROM   v$database;
COMPUTE SUM LABEL ‘Total’ OF Tot_Spc_Alloc ON report

BREAK ON REPORT
COLUMN TSname        Format A25               Heading ‘Tablespace Name’
COLUMN TSid          Format           999     Heading ‘Tablespace#’
COLUMN Tot_Spc_Alloc Format 9,999,999,990.00  Heading ‘Total Space|Allocated’
COLUMN Tot_Spc_Free  Format   999,999,990.00  Heading ‘Total Free|Space’
COLUMN Tot_Spc_Used  Format   999,999,990.00  Heading ‘Total Space|Used’
COLUMN Pct_Used      Format           990.000 Heading ‘Percent|Used’
COLUMN Pct_Free      Format           990.000 Heading ‘Percent|Free’
COLUMN FileName      Format A65               Heading ‘File Name’
COLUMN fileid        Format 99999             Heading ‘File|ID’
SET LINESIZE 10000
SET HEADING ON
SELECT tsid, tsname, pct_used, pct_free, tot_spc_alloc, tot_spc_used, tot_spc_free
FROM   (SELECT   c.ts# tsid,
a.tsname,
(1 – (b.tot_spc_free / a.tot_spc_alloc)) * 100 pct_used,
(b.tot_spc_free / a.tot_spc_alloc) * 100 pct_free,
a.tot_spc_alloc / 1024 / 1024 tot_spc_alloc,
(a.tot_spc_alloc – b.tot_spc_free) / 1024 / 1024 tot_spc_used,
b.tot_spc_free / 1024 / 1024 tot_spc_free
FROM     (SELECT   tablespace_name tsname, SUM (BYTES) tot_spc_alloc
FROM     DBA_DATA_FILES
GROUP BY tablespace_name) a,
(SELECT   tablespace_name tsname, SUM (BYTES) tot_spc_free, MAX (BYTES) max_b2
FROM     DBA_FREE_SPACE
GROUP BY tablespace_name) b,
v$tablespace c
WHERE    a.tsname = b.tsname AND a.tsname = c.NAME
ORDER BY c.ts#);

Oracle 11g Active Sessions

Oracle 11g Sessions currently active

select   A.inst_id “Instance”,A.sid “Sid”,A.serial# “Serial No.”,p.spid “ospid”,a.schemaname “schemaname”,
        A.machine “Machine”,A.terminal “Terminal”,  A.program “Program”,
      a.event “event”,a.seconds_in_wait “seconds_in_wait”,
       a.blocking_session “blocking_session”, A.osuser ,    
       sw.p1, sw.p2, sw.p3,sw.event,
       B.sql_id “SQL ID”,
       B.sql_text “Query”,
       B.users_executing “Users” ,C.opname “Operation”,C.target “Target”,
       C.sofar “Till Now”,C.totalwork “Total Work”,C.Time_Remaining “Remaining Time”,
       C.elapsed_seconds “Time Taken”,
       C.Message “Message”,A.username “User”,A.status “Status” , sysdate “Time”,
       su.tablespace, su.contents, su.extents, su.blocks
       from   gv$session A,gv$sql B,gv$session_longops C,gv$process P ,gv$session_wait sw,v$sort_usage su
where  A.sql_address = B.address
and    a.PADDR = p.addr
and    B.users_executing > 0
and    C.inst_id(+) = A.inst_id
and    C.sid(+) = A.sid
and    C.serial#(+) = A.serial#
and    C.sql_address(+) = A.sql_address
and     sw.sid(+)= a.sid
and    a.saddr=su.session_addr
–and C.Time_Remaining >0 —-
–and a.schemaname = ‘XXX’
–and a.sid=3835;

select * from dba_hist_active_sess_history
where sample_time >sysdate-5/24;

SELECT * FROM TABLE(dbms_xplan.display_cursor(‘0txzuvqj2ujux’));
select * from table(dbms_xplan.display_awr(‘3dd7k4gars656′));

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(‘3dd7k4gars656′,null,null,’ADVANCED’));
select * from table( DBMS_XPLAN.display_cursor(‘3dd7k4gars656′, NULL,’ADVANCED ROWS ALLSTATS’));

 select
count(1),to_char(sample_time, ‘YYYY-MM-DD HH24′) sample_time,
sql_id, sql_plan_hash_value
from dba_hist_active_sess_history
where
sql_id = ‘0txzuvqj2ujux’
group by
to_char(sample_time, ‘YYYY-MM-DD HH24′),
sql_id, sql_plan_hash_value
order by sample_time;

SELECT sql_id,plan_hash_value,
SUM(elapsed_time_total)/SUM(executions_total)/1000000 avg_elapsedt_secs
FROM dba_hist_sqlstat
WHERE sql_id = ‘0txzuvqj2ujux’
GROUP BY sql_id,plan_hash_value ;

——-
——- TOP SQL from dba_hist_active_sess_history no v$active_session_history   filter by DBID
——-
select * from (select     ash.SQL_ID , ash.SQL_PLAN_HASH_VALUE Plan_hash, aud.name type,    
 sum(decode(ash.session_state,’ON CPU’,1,0))     “CPU”,   
   sum(decode(ash.session_state,’WAITING’,1,0))    –     sum(decode(ash.session_state,’WAITING’, 
   decode(wait_class, ‘User I/O’,1,0),0))    “WAIT” ,     sum(decode(ash.session_state,’WAITING’, 
   decode(wait_class, ‘User I/O’,1,0),0))    “IO” ,     sum(decode(ash.session_state,’ON CPU’,1,1))     “TOTAL”
   from dba_hist_active_sess_history ash,     audit_actions aud where SQL_ID is not NULL   — and ash.dbid=&DBID   
and ash.sql_opcode=aud.action   — and ash.sample_time > sysdate – &minutes /( 60*24)
group by sql_id, SQL_PLAN_HASH_VALUE   , aud.name order by sum(decode(session_state,’ON CPU’,1,1))   desc
) where  rownum < 10