Pass Username & Password On Command Line To any utility

The goal of this small article is to answer how to pass the apps password on the command line to the utilities like sqlplus, adcgnjar, etc.

Use the following command:

$ { sleep 1s; echo apps; sleep 1s; echo appspassword;} | adcgnjar

$ { sleep 1s; echo apps; sleep 1s; echo appspassword;} | sqlplus

NOTE: The first "echo" instruction should contain the apps user name and second the password.

For example,

[ed-olraclin01@oracle ~]$ { sleep 1s; echo apps; sleep 1s; echo appspasswordHere;} | adcgnjar

[ed-olraclin01@oracle ~]$ { sleep 1s; echo apps; sleep 1s; echo appspasswordHere;} | sqlplus

SQL*Plus: Release 10.1.0.5.0 - Production on Tue Dec 21 13:18:05 2021

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

Enter user-name: Enter password:

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

[ed-olraclin01@oracle ~]$

-->Below statement will run the SQL script present in a specific directory after it connects as APPS user,

[ed-olraclin01@oracle ~]$ { sleep 1s; echo appspasswordHere;} | sqlplus APPS @$HOME/AnyScriptToBeRun.sql

Note after it connects and run the script it exits and come back to the OS command prompt. 

ERROR running adop phase=prepare

adop phase=prepare failed with below error,

message_status: ERROR

Adadmin action did not go through successfully.

*******FATAL ERROR*******

Lines #(2643-2645):

TIME    : Thu Oct  7 20:57:59 2021

FUNCTION: main::execADADMIN [ Level 1 ]

ERRORMSG: Adadmin action did not go through successfully.

List of forms in Alert :

   ALRALERT.fmx   ALRASDDL.fmx   ALRASDPG.fmx   ALRPRRPA.fmx   ALRPRSAP.fmx

   ALRRHRAA.fmx   ALRRHRAC.fmx   ALRRHRAE.fmx   ALRRHRAH.fmx   ALRSSDAO.fmx

   ALRSSRAI.fmx

Enter forms to generate, or enter 'all' [all] : abort

You should check the file

/u01/EBSTEST/apps/fs_ne/EBSapps/log/adop/17/20211007_191103/prepare/ed-olapplin1/TXK_SYNC_update/log/adadmin_20211007_203158.log

SOLUTION:

Run the following steps.

1.adop phase=abort,cleanup cleanup_mode=full (as ENV is currently in prepare failed state after     adsplice and adadmin was run on Run FS).

2. adop phase=fs_clone

3. adop phase=prepare

If full mode cleanup filed above then run the quick mode. 

adop phase=abort,cleanup cleanup_mode=quick

Sync AD tables when ADOP fails in EBS 12.2

Here we will see how to Synchronize below AD tables when adop fails on EBS 12.2 on premises or on Cloud,
FND_NODES
ADOP_VALID_NODES
FND_OAM_CONTEXT_FILES

CASE 1 ERROR:
# adop phase=prepare
*******FATAL ERROR*******
PROGRAM : (/appl/ad/12.0.0/bin/adzdoptl.pl)
TIME : Fri 10 Oct 03:00:45 2021
FUNCTION: ADOP::GlobalVars::_GetMandatoryArgs [ Level 1 ]
ERRORMSG: adop is not able to detect any valid application tier nodes in ADOP_VALID_NODES table.
Ensure autoconfig is run on all nodes.
[STATEMENT] Please run adopscanlog utility, using the command
"adopscanlog -latest=yes"
to get the list of the log files along with snippet of the error message corresponding to each log file.
adop exiting with status = 255 (Fail)

CASE 1 CAUSE: 

# adop phase=prepare
In this case, The fnd_nodes table has NULL for domain entry of this valid server.
Example Data Supporting the Conclusion

[table.applsys.fnd_nodes]
NODE     PLAT    D C A F W    NODE_NAME    SERVER_ADDRESS     DOMAIN    WEBHOST                              VIRTUAL_IP        S
12,121     226    N Y Y Y Y    NEW122APP1   ###.###.###.103     NULL        new122app1.<host.domain>       NULL               Y


CASE 2 ERROR:
# adop phase=apply / actualize_all
[APPLY PHASE]
*******FATAL ERROR*******
AutoConfig could not successfully execute the following scripts:
Directory: <EBS_BASE>/fs#/FMW_Home/webtier/perl/bin/perl -I <EBS_BASE>/fs#/FMW_Home/webtier/perl/lib/5.10.0 -I <EBS_BASE>/fs#/FMW_Home/webtier/perl/lib/site_perl/5.10.0 -I <EBS_BASE>/fs#/EBSapps/appl/au/12.0.0/perl -I <EBS_BASE>/fs#/FMW_Home/webtier/ohs/mod_perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi /<EBS_BASE>/fs#/inst/apps/<SID_hostname>/admin/install
txkGenADOPWrapper.pl INSTE8_APPLY 1
SQL> SQL> Connected.
SQL> SELECT ad_zd_adop.get_node_type('<hostname>') FROM DUAL
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.AD_ZD_ADOP", line 3049

CASE 2 CAUSE: 

# adop phase=apply / actualize_all
[table.applsys.fnd_oam_context_files]
NAME S_CONTEXTFILE S_CONTEXTTYPE S_CONTEXTNAME Version Number SERIAL APPL_TOP_NAME HOST SHARED_FILE_SYSTEM EDITION
<SID_hostname> ./<SID_hostname>.xml APPL_TOP Context <SID_hostname> 120.401.12020000.85 6999 AT_<hostname> <hostname> false run
<SID_hostname> ./<SID_hostname>.xml APPL_TOP Context <SID_hostname> 120.401.12020000.85 6999 AT_<hostname> <hostname> false run
<SID_hostname> ./<SID_hostname>.xml APPL_TOP Context <SID_hostname> 120.401.12020000.85 7001 AT_<hostname> <hostname> false patch
<SID_hostname> ./<SID_hostname>.xml Database Context <SID_hostname> 120.52.12020000.11 0 NULL <hostname> NULL database 

SOLUTION:
Test the following steps in a cloned instance first, and then migrate accordingly once the desired result is confirmed:

Note(s): 1. Due to the method required for "cleaning out" / "re-synchronizing" the following tables, it is EXPECTED / REQUIRED that the Applications have been shutdown. The only thing running should be the Database Tier.

              2. A full backup should be taken before any testing begins.

              3. The following query can be used to find out one's AD/TXK levels.

               SQL>col release_name format a14

        col abbreviation format a14
        col codelevel format a10

         select f.release_name, t.abbreviation, t.codelevel from apps.fnd_product_groups f,ad_trackable_entities t where abbreviation in ('txk','ad');

1. Backup the fnd_oam_context_files, fnd_nodes, and adop_valid_nodes tables, and if on AD/TXK 8 or higher; ad_nodes_config_status in the EBS env nodes:

      Linux#> sqlplus applsys/<pwd>

      SQLPlus#> create table fnd_oam_context_files_bkp as select * from fnd_oam_context_files;

      SQLPlus#> create table fnd_nodes_bk as select * from fnd_nodes;

        SQLPlus#> create table adop_valid_nodes_bk as select * from adop_valid_nodes;

      If on AD/TXK 8 or higher:

      SQLPlus#> create table ad_nodes_config_status_bk as select * from ad_nodes_config_status;

2. Truncate the following tables (Continue from step 1):

      SQLPlus#> truncate table fnd_oam_context_files;

      SQLPlus#> truncate table fnd_nodes;

      SQLPlus#> truncate table adop_valid_nodes;

      If on AD/TXK 8 or higher:

      SQLPlus#> truncate table ad_nodes_config_status;

 3.  Run AutoConfig on the DB tier:

Source the <RDBMS_ORACLE_HOME> home.

Linux#> cd <RDBMS_ORACLE_HOME>/appsutil/scripts/<SID>_<HOSTNAME>/

Linux#> ./adautocfg.sh

              ... Confirm Autoconfig completes successfully.

              ... If RAC, Repeat step 3 on all RAC nodes.

4.  Run Autoconfig on the run file system.

Linux#> source <EBS_BASE>/EBSapps.env run

Linux#> cd $ADMIN_SCRIPTS_HOME

Linux#> ./adautocfg.sh

      ... Confirm Autoconfig completes successfully.

      ... If more than one EBS node, repeat step 4 on all EBS nodes.

5.  Run Autoconfig on the patch file system

Before running Autoconfig on the patch file system the ebs_login trigger MUST be disabled
... After the successful completion of Autoconfig the ebs_login trigger MUST be re-enabled.

a. Disable the ebs_login trigger using the following SQL.

Linux#> sqlplus system/<pwd>

SQLPlus#> alter trigger ebs_logon disable;

 

b. At this time Run autoconfig with the patch env sourced .       

Linux#> source <EBS_BASE>/EBSapps.env patch

Linux#> cd $ADMIN_SCRIPTS_HOME

Linux#> ./adautocfg.sh

... Confirm Autoconfig completes successfully

... If more than one EBS node, repeat step 5.b on all EBS nodes.

c. Enable the ebs_login trigger using the following SQL.

Linux#> sqlplus system/<pwd>

SQLPlus#> alter trigger ebs_logon enable;

 

 6.  After Autoconfig has been run successfully on all nodes, run the following two (2) queries in order to verify the tables have been correctly populated:

SQL>    set pagesize 5
        set linesize 132
        col node_name format a15
        col server_id format a8
        col server_address format a15
        col platform_code format a4
        col webhost format a12
        col domain format a20
        col virtual_ip format a12

        select node_id, platform_code, support_db D, support_cp C, support_admin A, support_forms F, support_web W, node_name, server_id,
               server_address, domain, webhost, virtual_ip, status
          from fnd_nodes order by node_id; 

SQL>    set pagesize 5
        set linesize 132
        col NAME format A20
        col VERSION format A12
        col PATH format A110
        col STATUS format A10

        select NAME,VERSION,PATH, STATUS from FND_OAM_CONTEXT_FILES;

Setup SQLTXPLAIN / SQLT on 19c Pluggable Database for EBS R12

SQLTXPLAIN, also known as SQLT, is a tool provided by Oracle Server Technologies Center of Expertise - ST CoE. SQLT inputs one SQL statement and outputs a set of diagnostics files. These files are commonly used to diagnose SQL statements performing poorly. SQLT connects to the database and collects execution plans, Cost-based Optimizer CBO statistics, schema objects metadata, performance statistics, configuration parameters, and similar elements that influence the performance of the SQL being analyzed.

SQLT requires no license and is FREE. SQLT can use data from the Oracle Diagnostic and/or the Oracle Tuning Packs if your site has a license for them.

But setting up SQLT on a Pluggable Database Architecture for EBS 12.2 is quite tricky. Initially when I followed the straight forward Oracle documents to setup SQLT it was failing with below errors, and I figured out the trick required to setup this correctly. 

Steps to Install SQLTXPLAIN / SQLT

1. Always Download the latest setup scripts file [sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip] from MOS: SQLT Usage Instructions (Doc ID 1614107.1)

2. Copy the downloaded zip file to Database server in a temporary directory and unzip the file. A directory by name 'sqlt' will be extracted that will contain the scripts to drop, install, upgrade the SQLT. 

[oracle@ed-olraclin1:CDB1:~/tmp]$ ls -ltr

-rwxrwxrwx 1 oracle oinstall 948892 Sep 23 15:59 sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip

[oracle@ed-olraclin1:CDB1:~/tmp]$ unzip sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip

[oracle@ed-olraclin1:CDB1:~/tmp]$ ls -ltr

drwxr-xr-x 7 oracle oinstall   4096 Jun  5  2020 sqlt

-rwxrwxrwx 1 oracle oinstall 948892 Sep 23 15:59 sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip

[oracle@ed-olraclin1:CDB1:~/tmp]$ cd sqlt

[oracle@ed-olraclin1:CDB1:~/tmp/sqlt]$ ls -ltr

drwxr-xr-x 3 oracle oinstall  4096 Jun  5  2020 input

drwxr-xr-x 2 oracle oinstall  4096 Jun  5  2020 doc

drwxr-xr-x 2 oracle oinstall  4096 Jun  5  2020 run

drwxr-xr-x 7 oracle oinstall  4096 Jun  5  2020 utl

-rw-r--r-- 1 oracle oinstall 58466 Jun  5  2020 sqlt_instructions.html

-rw-r--r-- 1 oracle oinstall 39368 Jun  5  2020 sqlt_instructions.txt

drwxr-xr-x 2 oracle oinstall  4096 Sep 23 17:37 install

[oracle@ed-olraclin1:CDB1:~/tmp/sqlt]$ cd install

[oracle@ed-olraclin1:CDB1:~/tmp/sqlt/install]$ ls -ltr sqdrop.sql sqcreate.sql

-rw-r--r-- 1 oracle oinstall 1365 Oct 30  2014 sqdrop.sql

-rw-r--r-- 1 oracle oinstall 4851 Aug  4  2019 sqcreate.sql

sqdrop.sql : This script will drop/unistall the existing SQLT installed in the database. It will drop the user schemas SQLTXADMIN & SQLTXPLAIN

sqcreate.sql: This script will install SQLT in the database and will create the two schemas SQLTXADMIN & SQLTXPLAIN

3. UNINSTALL/DROP SQLT from the PDB

Run the sqdrop.sql on the PDB only if earlier you have SQLT failed to install and got any errors like below,

Error 1 in log files:

-> ORA-01927: cannot REVOKE privileges you did not grant                        

-> SQLT$STAGE: READ,WRITE access not revoked from SQLTXPLAIN due to error above 

(ignore this error)                                                             

SQLT$STAGE: READ,WRITE access granted to SQLTXADMIN                             

SQLT$STAGE: READ,WRITE access granted to SQLT_USER_ROLE                         

SQLT$STAGE: write test file tasqdirset.txt                                      

sys.sqlt$_trca$_dir_set.open_write_close: ORA-29283: invalid file operation:    

path traverses a symlink [29433]                                                

BEGIN sqlt$_trca$_dir_set; END;

*

ERROR at line 1:

ORA-20102: SQLT$STAGE: not capable of reading or writing.

ORA-06512: at "SYS.SQLT$_TRCA$_DIR_SET", line 314

ORA-06512: at "SYS.SQLT$_TRCA$_DIR_SET", line 322

ORA-06512: at "SYS.SQLT$_TRCA$_DIR_SET", line 397

ORA-06512: at line 1 

Error 2 in log files:

BEGIN
*
ERROR at line 1:
ORA-20110: User "APPS" lacks required "SQLT_USER_ROLE" role or SQLT is not properly installed. >>>>>>>>>>>>>
Review installation NN_*.log files.
ORA-06512: at line 6 

==>> So first lets connect to PDB and uninstall/drop SQLT from the PDB that was failed to be installed earlier or you just want to do a fresh install and want to drop the SQLT repository if already available on the PDB database.

SQL> ALTER SESSION SET CONTAINER=MYPDB;

Session altered.

SQL> show con_id

CON_ID

4

SQL> show pdbs

    CON_ID CON_NAME     OPEN_MODE   RESTRICTED

         4         MYPDB             READ WRITE  NO

SQL> START sqdrop.sql

sqdrop.sql script output:

... uninstalling SQLT, please wait

TADOBJ completed.

SQDOLD completed. Ignore errors from this script

SQDOBJ completed. Ignore errors from this script

SQL>

SQL> DECLARE

  2    my_count INTEGER;

  3

  4  BEGIN

  5    SELECT COUNT(*)

  6      INTO my_count

  7      FROM sys.dba_users

  8     WHERE username = 'TRCADMIN';

  9

 10    IF my_count = 0 THEN

 11      BEGIN

 12        EXECUTE IMMEDIATE 'DROP PROCEDURE sys.sqlt$_trca$_dir_set';

 13      EXCEPTION

 14        WHEN OTHERS THEN

 15          DBMS_OUTPUT.PUT_LINE('Cannot drop procedure sys.sqlt$_trca$_dir_set. '||SQLERRM);

 16      END;

 17

 18      FOR i IN (SELECT directory_name

 19                  FROM sys.dba_directories

 20                 WHERE directory_name IN ('SQLT$UDUMP', 'SQLT$BDUMP', 'SQLT$STAGE', 'TRCA$INPUT1', 'TRCA$INPUT2', 'TRCA$STAGE'))

 21      LOOP

 22        BEGIN

 23          EXECUTE IMMEDIATE 'DROP DIRECTORY '||i.directory_name;

 24          DBMS_OUTPUT.PUT_LINE('Dropped directory '||i.directory_name||'.');

 25        EXCEPTION

 26          WHEN OTHERS THEN

 27            DBMS_OUTPUT.PUT_LINE('Cannot drop directory '||i.directory_name||'. '||SQLERRM);

 28        END;

 29      END LOOP;

 30    END IF;

 31  END;

 32  /

Dropped directory SQLT$STAGE.

PL/SQL procedure successfully completed.

SQL>

SQL> WHENEVER SQLERROR CONTINUE;

SQL>

SQL> PAU About to DROP users &&tool_repository_schema. and &&tool_administer_schema.. Press RETURN to continue.

About to DROP users SQLTXPLAIN and SQLTXADMIN. Press RETURN to continue.

SQL>

SQL> DROP USER &&tool_administer_schema. CASCADE;

old   1: DROP USER &&tool_administer_schema. CASCADE

new   1: DROP USER SQLTXADMIN CASCADE

User dropped.

SQL> DROP USER &&tool_repository_schema. CASCADE;

old   1: DROP USER &&tool_repository_schema. CASCADE

new   1: DROP USER SQLTXPLAIN CASCADE

User dropped.

SQL> DROP ROLE &&role_name.;

old   1: DROP ROLE &&role_name.

new   1: DROP ROLE SQLT_USER_ROLE

Role dropped.

SQL>

SQL> SET ECHO OFF;

SQDUSR completed.

SQDROP completed.

4. Take the TNSENTRY from EBS application tier node and update the Database server tnsnames.ora file on both RAC nodes.

[oracle@ed-olraclin01:CDB1:~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora

MYPDB1=

    (DESCRIPTION_LIST=

        (LOAD_BALANCE=YES)

        (FAILOVER=YES)

        (DESCRIPTION=

                (ADDRESS=(PROTOCOL=tcp)(HOST=ed-olraclin01.oracle.com)(PORT=1521))

            (CONNECT_DATA=

                (SERVICE_NAME=ebs_MYPDB)

                (INSTANCE_NAME=CDB1)

            )

        )

        (DESCRIPTION=

                (ADDRESS=(PROTOCOL=tcp)(HOST=ed-olraclin2.oracle.com)(PORT=1521))

            (CONNECT_DATA=

                (SERVICE_NAME=ebs_MYPDB)

                (INSTANCE_NAME=CDB2)

            )

        )

    )

5. It's must to check connectivity of SYS user from both RAC nodes using this new TNSENTRY.

[oracle@ed-olraclin1:CDB1:~]$ sqlplus sys@MYPDB1 as sysdba

Enter password:

Last Successful login time: Thu Sep 23 2021 16:46:50 +12:00

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

SQL>

6. If the connection is not working then update the password file on each RAC node using orapwd utility

$ cd $ORACLE_HOME/dbs

$ orapwd password='pwd_here_in_single_quote' entries=5 file=orapwCDB1

$ orapwd password='pwd_here_in_single_quote' entries=5 file=orapwCDB2

7. Modify the SYS password from CDB database for all the containers

SQL> show con_id

con_id

1

SQL> ALTER USER SYS IDENTIFIED BY "pwd_here_in_double_quote" CONTAINER=ALL;

user altered.

9.  Note that we cannot run sqcreate.sql script successfully if the diagnostic_dest path is as symbolic link and the script will thrown this error [path traverses a symlink [29433]] in the log file as detailed in error 1 above. This is a drawback of Oracle that, if the directory is a symbolic link to ACFS mount then the script will give errors. 

SQL> show parameter diagnostic_dest

NAME                                 TYPE        VALUE

diagnostic_dest                      string      /custom_diag/mypdb

[oracle@ed-olraclin1:CDB1:/]$ ls -ltr /

lrwxrwxrwx    1 root   root         22 Feb 2021 custom_diag -> /acfsnpevol/custom_diag

SQL> SELECT name, value FROM v$parameter2 WHERE name LIKE '%dump_dest';

NAME                                       VALUE

background_dump_dest           /u01/app/oracle/product/19.11/dbhome1/rdbms/log

user_dump_dest                       /u01/app/oracle/product/19.11/dbhome1/rdbms/log

core_dump_dest                       /custom_diag/cdb/diag/rdbms/cdb/CDB1/cdump

SQL> SELECT directory_name||' '||directory_path directories FROM sys.dba_directories WHERE directory_name LIKE 'SQLT$%' OR directory_name LIKE 'TRCA$%' ORDER BY 1;

no rows selected

10. Temporarily we will modify the diagnostic_dest parameter for both instances to a non-symbolic link directory in ORACLE_BASE, and once the SQLT installation completes successfully we will re-set the value back to the symbolic link ACFS directory. 

SQL> ALTER SYSTEM SET diagnostic_dest='/u01/app/oracle' SCOPE=both SID='*';

Verify if the alert log and the trace log files are getting generated in the updated NON-ACFS directory. 

SQL> SHOW PARAMETER DIAGNOSTIC_DEST

NAME                                 TYPE        VALUE

diagnostic_dest                      string      /u01/app/oracle

You will see that the trace files and alert log file gets generated in $ORACLE_BASE directory

$ cd $ORACLE_BASE/diag/rdbms/cdb/CDB1/trace

11. Now install SQLT from DB node 1 connecting as SYS user

$ cd  $ORACLE_HOME

$ . MYPDB_ed-olraclin1.env

$ . CDB1_ed-olraclin1.env

$ echo $TNS_ADMIN/

$ cd /tmp/sqlt/install

$ sqlplus sys@MYPDB1 as sysdba

SQL> START sqcreate.sql

Optional Connect Identifier (ie: @PROD): @MYPDB1

Password for user SQLTXPLAIN: <pwd_here>

Re-enter password: <pwd_here>

Do you want to see the free space of each tablespace [YES]

or is it ok just to show the list of tablespace [NO]?

Type YES or NO [Default NO]: YES

Default tablespace [UNKNOWN]: USERS

Temporary tablespace [UNKNOWN]: TEMP1

Main application user of SQLT: APPS

"T" if you have license for Diagnostic and Tuning

"D" if you have license only for Oracle Diagnostic

"N" if you do not have these two licenses

Oracle Pack license [T]: T

Last two lines from Long output, 

SQLT users must be granted SQLT_USER_ROLE before using this tool.

SQCREATE completed. Installation completed successfully.

SQL>

12. Now revert back the diagnostic_dest parameter value to use ACFS mount,

SQL> ALTER SYSTEM SET diagnostic_dest='/custom_diag/mypdb' SCOPE=both SID='*';

SQL> select name,value from v$diag_info ;

NAME            VALUE

Diag Enabled TRUE

ADR Base /custom_diag/mypdb

ADR Home /custom_diag/mypdb/diag/rdbms/cdb/cdb2

Diag Trace /custom_diag/mypdb/diag/rdbms/cdb/cdb2/trace

Diag Alert /custom_diag/mypdb/diag/rdbms/cdb/cdb2/alert

Diag Incident /custom_diag/mypdb/diag/rdbms/cdb/cdb2/incident

Diag Cdump /custom_diag/mypdb/diag/rdbms/cdb/cdb2/cdump

Health Monitor /custom_diag/mypdb/diag/rdbms/cdb/cdb2/hm

Default Trace File /custom_diag/mypdb/diag/rdbms/cdb/cdb2/trace/cdb2_ora_220278.trc

Active Problem Count 0

Active Incident Count 0

ORACLE_HOME /u01/app/oracle/product/19.11/db_cdb_home

SQL> SELECT directory_name||' '||directory_path directories FROM sys.dba_directories WHERE directory_name LIKE 'SQLT$%' OR directory_name LIKE 'TRCA$%' ORDER BY 1;

directory_name||''||direcotry_path

SQLT$BDUMP /u01/app/oracle/diag/rdbms/cdb/cdb2/trace

SQLT$DIAG  /u01/app/oracle/diag/rdbms/cdb/cdb2/trace

SQLT$STAGE /u01/app/oracle/diag/rdbms/cdb/cdb2/trace

SQLT$UDUMP /u01/app/oracle/diag/rdbms/cdb/cdb2/trace

TRCA$INPUT1 /u01/app/oracle/diag/rdbms/cdb/cdb2/trace

TRCA$INPUT2 /u01/app/oracle/diag/rdbms/cdb/cdb2/trace

TRCA$STAGE  /u01/app/oracle/diag/rdbms/cdb/cdb2/trace

SQL> SELECT name, value FROM v$parameter2 WHERE name LIKE '%dump_dest';

NAME                             VALUE

background_dump_dest /u01/app/oracle/product/19.11/dbhome1/rdbms/log

core_dump_dest /custom_diag/cdb/diag/rdbms/cdb/CDB2/cdump

user_dump_dest /u01/app/oracle/product/19.11/dbhome1/rdbms/log

13. Now generate the report for SQL_ID from EBS Application server.

Note: While generating the sqltxtract output again the value of diagnostic_dest should not be pointing to any symbolic link to ACFS storage. Always update the diagnostic_dest parameter value as shown above to a non-symbolic link , generate the below diagnostic report and then revert back the parameter value. 

$ cd /tmp/sqlt/run

$ sqlplus APPS/<pwd_here>

SQL> START sqltxtract.sql 417b6c4a1bm8r

To monitor the progress of  the above script you can run the below statement in another session as apps user. 

SQL> SELECT * FROM SQLTXADMIN.sqlt$_log_v;

Above sqltxtract.sql script will generate sqlt_s23339_xtract_417b6c4a1bm8r.zip output file that will be used for diagnosing the slowness of that SQL_ID.

References:

SQLT Usage Instructions (Doc ID 1614107.1)

Troubleshooting SQLT Issues Community Thread (Doc ID 1521607.1)

FAQ: Common SQLT (SQLTXPLAIN) Runtime/Installation Errors (Doc ID 1670677.1)

SQLT installation fails. No error in the log file or during execution of sqcreate.sql script. (Doc ID 2339926.1)

All About the SQLT Diagnostic Tool (Doc ID 215187.1)

AF Variables and EBS Workflow Mailer with Cloud mail server

In EBS context file there are below 3 AF variables, 

1. AF_JRE_TOP
2. AF_CLASSPATH
3. AFJVAPRG.

If these variables are not set correctly then resolve it by following below steps that is referenced in note ,

Oam Applications Dashboard Collection Noclassdeffounderror (Doc ID 373386.1)

1. Check how the Concurrent Manager sees your environment variables while running a concurrent program

- Log into Applications as the System Administrator
- Navigate: Request -> Run
- Choose Single Request
- Choose "Prints Environments Variables" concurrent request
- Enter AF_CLASSPATH, AF_JRE_TOP, AFJVAPRG
- Submit program

2. Make the following change on your TEST system
- Backup your adovars.env file
- Update AF_CLASSPATH, AF_JRE_TOP, AFJVAPRG in your adovars.env using the full paths
- Bounce the Concurrent Managers
- Run the "Prints Environment Variables" Concurrent request to check the values
- Test a Java Concurrent Program.

AF variables are set for letting programs know the location of java (AFJVAPRG), JRE (AF_JRE_TOP) and the classpath (AF_CLASSPATH) on the concurrent node.
Also, note that these variables are also set on the web node.

==>Query to get the workflow notification mailer logfile to troubleshoot issues.

select fl.meaning,fcp.process_status_code,
decode(fcq.concurrent_queue_name,'WFMLRSVC','mailer container','WFALSNRSVC','listener container',fcq.concurrent_queue_name) "CONTAINER",
fcp.concurrent_process_id,os_process_id, fcp.logfile_name
from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'
and fl.lookup_type='CP_PROCESS_STATUS_CODE' and
fl.lookup_code=fcp.process_status_code
and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')
order by fcp.logfile_name;

fl.meaning,fcp.process_status_code,"CONTAINER",fcp.concurrent_process_id,os_process_id, fcp.logfile_name

Active      A                  listener containere           393796        31902        /u01/fs_ne/inst/TEST_server1/logs/appl/conc/log/FNDCPGSC393796.txt

Active      A                  mailer container              393797        31908        /u01/fs_ne/inst/TEST_server1/logs/appl/conc/log/FNDCPGSC393797.txt

AFJVAPRG - The location of the JDK or JRE executable for the concurrent processing tier.

AF_CLASSPATH - The classpath for the concurrent processing tier.

The AF_CLASSPATH variable is used by JAVA concurrent programs and must use literal (full) path values.

==>>Verify Oracle Workflow Java Mailer connection with IMAP Server,

AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Ddbcfile=$FND_SECURE/$TWO_TASK.dbc -Dserver=<literal name of SMTP Server> -Dport=587 -Dsecurity=TLS -Dtruststore=$AF_JRE_TOP/lib/security/cacerts -Daccount=<username> -Dpassword=<password> -Dconnect_timeout=120 -Ddebug=Y -Dlogfile=SMTPTLSTest.log oracle.apps.fnd.wf.mailer.Mailer

Oracle EBS Workflow Notification Mailer with Cloud E-Mail Servers

Configuring an Oracle Workflow Notification Mailer with Cloud E-Mail Servers (Doc ID 2077434.1)

Query to get the workflow notification mailer logfile to troubleshoot issues.

select fl.meaning,fcp.process_status_code,

decode(fcq.concurrent_queue_name,'WFMLRSVC','mailer container','WFALSNRSVC','listener container',fcq.concurrent_queue_name) "CONTAINER",

fcp.concurrent_process_id,os_process_id, fcp.logfile_name

from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl

where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'

and fl.lookup_type='CP_PROCESS_STATUS_CODE' and

fl.lookup_code=fcp.process_status_code

and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')

order by fcp.logfile_name;

Inbound Connection Setup

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Ddbcfile=$FND_SECURE/DEV.dbc -Dserver=mail.test.lk -Dport=143 -Dsecurity=STARTTLS -Dtruststore=$AF_JRE_TOP/lib/security/cacerts -Daccount=oracle@mail.lk -Dpassword=pass -Dconnect_timeout=120 -Djavax.net.debug=all -Ddebug=Y -Dlogfile=/tmp/imap1.log -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer > /tmp/imap2.log 2>&1

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Ddbcfile=$FND_SECURE/DEV.dbc -Dserver=mail.test.lk -Dport=143 -Dsecurity=SSL -Dtruststore=$AF_JRE_TOP/lib/security/cacerts -Daccount=oracle@mail.lk -Dpassword=pass -Dconnect_timeout=120 -Djavax.net.debug=all -Ddebug=Y -Dlogfile=/tmp/imap3.log -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer > /tmp/imap4.log 2>&1

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Ddbcfile=$FND_SECURE/DEV.dbc -Dserver=mail.test.lk -Dport=993 -Dsecurity=STARTTLS -Dtruststore=$AF_JRE_TOP/lib/security/cacerts -Daccount=oracle@mail.lk -Dpassword=pass -Dconnect_timeout=120 -Djavax.net.debug=all -Ddebug=Y -Dlogfile=/tmp/imap5.log -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer > /tmp/imap6.log 2>&1

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Ddbcfile=$FND_SECURE/DEV.dbc -Dserver=mail.test.lk -Dport=993 -Dsecurity=SSL -Dtruststore=$AF_JRE_TOP/lib/security/cacerts -Daccount=oracle@mail.lk -Dpassword=pass -Dconnect_timeout=120 -Djavax.net.debug=all -Ddebug=Y -Dlogfile=/tmp/imap7.log -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer > /tmp/imap8.log 2>&1

19c Guarantee Restore Point GRP on PDB and Flashback PDB to GRP

 Here we will see how to create a Guarantee Restore Point (GRP) on 19c Pluggable database PDB and then how to Flashback the database to that GRP point in time. 

Create Guarantee Restore Point for a Pluggable Database PDB

Login to Container Database CDB as SYS user

Enable Flashback database if it's not enabled. Also check if the database has local undo enabled.

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON

NO

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

LOCAL_UNDO_ENABLED TRUE true if local undo is enabled

SQL>  alter database flashback on;

SQL> COLUMN CURRENT_SCN FORMAT 99999999999999999999999999

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

SQL> create restore point GRP1 for pluggable database N11EBS guarantee flashback database;

SQL> column scn format 99999999999999999999

SQL> select NAME,TIME,SCN,PDB_RESTORE_POINT,GUARANTEE_FLASHBACK_DATABASE from V$RESTORE_POINT;

Flashback the database to GRP point in time 

Login to CDB as SYS user

SQL> alter pluggable database PDB1 close instances=all;

check alert log file until Pluggable database PDB1 is closed

SQL> SELECT * FROM V$PDBS;

SQL> flashback pluggable database PDB1 to restore point GRP1;

CDB1 SQL> alter pluggable database PDB1 open resetlogs; --On Instance 1

Now On Instance 2 open the database WITHOUT restorelogs

CDB2 SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN;

Now Restart all the instances using srvctl.

srvctl stop database -d CDB

srvctl start database -d CDB

srvctl status database -d CDB

Modify START_DEPENDENCIES in 19c Clusterware

While starting database if you get the below error, then you must check the resource that is missing in the START_DEPENDENCIES attribute of the Clusterware and correct the value of this attribute. 

Let's see how to troubleshoot this problem and fix it.

ERROR when starting the database Instance: 

[oracle@ed-olraclin1:TESTCDB1:~]$ srvctl start database -d TESTCDB

PRCR-1079 : Failed to start resource ora.testcdb.db

CRS-2640: Required resource 'uniform' is missing.

SQL> startup nomount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORA-39511: Start of CRS resource for instance '222' failed with error:[CRS-2640: Required resource 'uniform' is missing.

CRS-0222: Resource 'ora.testcdb.db' has dependency error.

clsr_start_resource:260 status:222

clsrapi_start_db:start_asmdbs status:222

]

Get Full list of RESOURCE Attributes and their values

[grid@ed-olraclin1:+ASM1:~]$ crsctl status resource ora.testcdb.db -f

START_DEPENDENCIES=hard(uniform:,global:uniform:ora.TEST.dg) pullup(,global:ora.TEST.dg) weak(type:ora.listener.type,global:type:ora.scan_listener.type,uniform:ora.ons,global:ora.gns)

In the above Attribute value, the uniform:, and a comma , that is highlighted are causing the database not to start, so we will correct this attribute value.

Correct the START_DEPENDENCIES Attribute value

[oracle@ed-olraclin1:TESTCDB1:~]$ /u01/19.0/grid/bin/crsctl modify resource ora.testcdb.db -attr "START_DEPENDENCIES='hard(global:uniform:ora.TEST.dg) pullup(global:ora.TEST.dg) weak(type:ora.listener.type,global:type:ora.scan_listener.type,uniform:ora.ons,global:ora.gns)'" -unsupported

Note the -unsupported option used to modify this Attribute value while this option is not required to modify any other Attribute's value. Moreover this option is not mentioned in the Syntax as shown below,

[grid@ed-olraclin1:+ASM1:~]$ crsctl modify resource -help

Usage:

  crsctl modify resource <resName> -attr "<specification>[,...]" [-f] [-delete] [-i] [-group <resourceGroupName>] [-env "env1=val1,env2=val2,..."]

     <specification>:   {<attrName>=<value> | <attrName>@<scope>=<value>}

        <scope>:   {@SERVERNAME(<server>)[@DEGREEID(<did>)] |

                       @CARDINALITYID(<cid>)[@DEGREEID(<did>)] }

where

     resName            Modify named resource

     attrName           Attribute name

     value              Attribute value

     server             Server name

     cid                Resource cardinality ID

     did                Resource degree ID

     -f                 Force option

     -delete            Delete named attribute

     -i                 Fail if request cannot be processed immediately

     -env               Override environment values for this command

     resourceGroupName  Resource group name

Now you will be able to start the database without any problem,

srvctl start database -d TESTCDB

Theory about START_DEPENDENCIES Attribute and it's value

Cannot determine the cause the value of START_DEPENDENCIES got changed in first place. It gets modified only when someone attempt to change the values.

START_DEPENDENCIES

Specifies a set of relationships that Oracle Clusterware considers when starting a resource. You can specify a space-delimited list of dependencies on several resources and resource types on which a particular resource can depend.

SYNTAX: START_DEPENDENCIES=dependency(resource_set) [dependency(resource_set)] [...]

In the above syntax, the variables are defined, as follows:

•dependency: Possible values are attraction, dispersion, exclusion, hard, pullup, and weak. You can specify each dependency only once, except for pullup, which you can specify multiple times.

•resource_set: A comma-delimited list of resource entities—either individual resources or resource types—enclosed in parentheses (), in the form of res1[, res2[, ...]], upon which the resource you are configuring depends.

Below are 2 dependency explained, 

•pullup[:always]([intermediate:][global:]{resource_name | type:resource_type})—When you specify the pullup start dependency for a resource, then this resource starts because of named resources starting.

Use the always modifier for pullup so that Oracle Clusterware starts this resource despite the value of its TARGET attribute, whether that value is ONLINE or OFFLINE. Otherwise, if you do not specify the always modifier, then Oracle Clusterware starts this resource only if the value of the TARGET attribute is ONLINE for this resource.

•hard([intermediate:][global:][uniform:]{resource_name | type:resource_type}) - Specify a hard start dependency for a resource when you want the resource to start only when a particular resource or resource of a particular type starts.

•weak([concurrent:][global:][uniform:]{resource_name | type:resource_type})—Specify a weak start dependency for a resource when you want that resource to start despite whether named resources are running, or not. An attempt to start this resource also attempts to start any resources on which this resource depends if they are not running.

For any technical queries you can email me on samiappsdba@gmail.com

Configuring Parallel Concurrent Processing PCP in EBS 12.2.x

You can follow the below steps to configure Parallel Concurrent Processing PCP in an EBS 12.2.x environment.

Step 1. Edit the applications context file via Oracle Applications Manager, and set the value of the variable APPLDCP to ON.

Set the value of s_appldcp to ON for both Nodes (ed-olapplin1 & ed-olapplin2) on both RUN & PATCH file system context files.

ed-olapplin1 & ed-olapplin2 : on Run File System

[applmgr ~]$ grep APPLDCP $CONTEXT_FILE

<APPLDCP oa_var="s_appldcp">ON</APPLDCP>

ed-olapplin1 & ed-olapplin2 : on Patch File System

[applmgr ~]$ grep APPLDCP $CONTEXT_FILE

<APPLDCP oa_var="s_appldcp">ON</APPLDCP>

Step 2. Execute AutoConfig by running the following command on all concurrent processing nodes:

[applmgr@ed-olapplin2 ~]$ echo $FILE_EDITION

Run

[applmgr@ed-olapplin2 ~]$ adautocfg.sh

ed-olapplin1:

[applmgr@ed-olapplin1 appl]$ echo $FILE_EDITION

Run

[applmgr@ed-olapplin1 appl]$ adautocfg.sh

Step 3. Check the tnsnames.ora and listener.ora configuration files, located in $INST_TOP/ora/10.1.2/network/admin. Ensure that the required FNDSM and FNDFS entries are present for all other concurrent nodes. 

Edit the tnsnames.ora file on both ed-olapplin1 & ed-olapplin2 Application nodes.

[applmgr ~]$ cd $TNS_ADMIN

[applmgr ~]$ vi tnsnames.ora

[applmgr ~]$ vi listener.ora”

Step 4. Restart the Applications listener processes on BOTH application tier node. 

[applmgr ~]$ cd $ADMIN_SCRIPTS_HOME

[applmgr ~]$ adalnctl.sh stop

[applmgr ~]$ adalnctl.sh start

Step 5. Log on to Oracle E-Business Suite Release 12 using the SYSADMIN account, and choose the System Administrator Responsibility. 

Navigate to the Install > Nodes screen, and ensure that each node in the cluster is registered.

Total 5 entries should be there.

Step 6. Verify that the Internal Monitor for each node is defined properly, with correct primary node specification, and work shift details. For example, Internal Monitor: Host1 must have primary node as host1. Also ensure that the Internal Monitor manager is activated: this can be done from Concurrent > Manager > Administrator. 

Step 7. Set the $APPLCSF environment variable on all the Concurrent Processing nodes to point to a log directory on a shared file system.

[applmgr@ed-olapplin1 scripts]$ echo $APPLCSF

/u02/erptest/fs_ne/inst/erptest_ed-olapplin1/logs/appl/conc

Step 8. Set the $APPLPTMP environment variable on all the CP nodes to the value of the shared directory path on the 19c DB RAC node servers. Set the value of APPLPTMP correctly on both Application tier nodes in the CONTEXT_FILE, then restart the database once.

[oraebs@ed-olraclin1 19cHome]$ srvctl stop database -d vision

[oraebs@ed-olraclin1 19cHome]$ srvctl start database -d vision

Step 9. Run AUTOCONFIG on both application tier nodes (first on node 2 then on node 1) on the RUN file system.

[applmgr ~]$ echo $FILE_EDITION

run

[applmgr ~]$ adautocfg.sh

Step 10. Set profile option 'Concurrent: PCP Instance Check' to OFF if database instance-sensitive failover is not required. By setting it to 'ON', a concurrent manager will fail over to a secondary Application tier node if the database instance to which it is connected becomes unavailable for some reason.

Step 11. Set Up Transaction Managers

1.Shut down the application services (servers) on all nodes

2.Shut down all the database instances cleanly in the Oracle RAC environment, using the command: 

[oraebs@ed-olraclin1 appsutil]$ srvctl stop database -d vision

[oraebs@ed-olraclin1 appsutil]$ srvctl start database -d vision

Add following parameter as below (ed-olraclin1 and ed-olraclin2)

_lm_global_posts=TRUE 

_immediate_commit_propagation=TRUE

[oraebs@ed-olraclin1 ~]$ cd $ORACLE_HOME/dbs/

[oraebs@ed-olraclin1 dbs]$ vi vision1_ed-olraclin1_ifile.ora

4.Start the instances on all database nodes, one by one.

[oraebs@ed-olraclin1 dbs]$ srvctl start database -d vision

5.Start up the application services (servers) on all nodes.

6.Log on to Oracle E-Business Suite Release 12 using the SYSADMIN account, and choose the System Administrator responsibility. Navigate to Profile > System, change the profile option ‘Concurrent: TM Transport Type' to ‘QUEUE', and verify that the transaction manager works across the Oracle RAC instance.

7.Navigate to Concurrent > Manager > Define screen, and set up the primary and secondary node names for transaction managers.

8.Restart the concurrent managers. (both Tier)

9.If any of the transaction managers are in deactivated status, activate them from Concurrent > Manager > Administrator. 

Step 12. Set Up Load Balancing on Concurrent Processing Nodes

1.Edit the applications context file through the Oracle Applications Manager interface, and set the value of Concurrent Manager TWO_TASK (s_cp_twotask) to the load balancing alias (<service_name>_balance>).

2. Execute AutoConfig by running $INST_TOP/admin/scripts/adautocfg.sh on all concurrent nodes. 

EBS Upgrade Analyzer and ICM ADOP Analyzer

During the EBS Upgrade to 12.2 version or while you are running into issues with ADOP cycle in EBS 12.2 version and you had to raise an SR with Oracle Support then always oracle will ask you to upload the subject Analyzer reports output to the SR for analysis. 

Here I have mentioned the ways you can generate this Analyzer reports from both Application and Database tiers. 

 It requires Java 1.7 and above to run ADOP Analyzer or EBS Upgrade Analyzer.

Always download the latest analyzer scripts from support.oracle.com from below notes,

E-Business Suite Upgrade Analyzer - 12.X to 12.2.X (Doc ID 2634237.1)

EBS Installation Configuration Management (ICM) Application DBA Online Patching (ADOP) Analyzer (Doc ID 2379656.1)

These are non-invasive script - the Analyzer does not perform any INSERTs, UPDATEs or DELETEs to your data; it just reports on it.

Generate Upgrade Analyzer Report:

on Application Node:

1. Source the APPS run filesystem environment

2. Run the E-Business Suite Applications Tier Upgrade Analyzer using this java command :

$ java -Danalyzer="atg_upgrade_analyzer.xml" -jar HA-UPG.jar

on Database Node:

1) Source the Database Tier environment

2) Run the E-Business Suite Database Tier Upgrade Analyzer using this java command :

$ java -Danalyzer="atg_upgrade_database_analyzer.xml" -jar HA-UPG.jar

Generate ICM ADOP Analyzer Report:

on Application Node:

1. Source the APPS run filesystem environment

2.  Unzip icm_adop_analyzer_200_x.zip in a directory with proper permissions.

3. Navigate to the directory from step 2 and change directory to HAF and run the ICM ADOP Analyzer using this command

$ cd HAF

$ sh run_analyzer.sh -Danalyzer="icm_adop_analyzer.xml"

on Database Node:

1. Source the Database Tier environment

2. Unzip icm_adop_analyzer_200_x.zip in a directory with proper permissions.

3. Navigate to the directory from step 2 and change directory to HAF and run the ADOP Database Analyzer using this command :

$ cd HAF

$ sh run_analyzer.sh -Danalyzer="adop_database_analyzer.xml"

RMAN Database Dropping and Deregistration

There are 4 syntax available to drop the database using RMAN

1. DROP DATABASE;

2. DROP DATABASE NOPROMPT;

3. DROP DATABASE INCLUDING BACKUPS;

4. DROP DATABASE INCLUDING BACKUPS NOPROMPT;

When “NOPROMPT” is specified RMAN does not prompt for the confirmation before deleting the database.

The “DROP DATABASE” command deletes the

Datafiles

Logfiles

Controlfiles

Spfile

Database has be in EXCLUSIVE RESTRICTED MODE and on MOUNT state.

Lets put the database in exclusive restricted mode from RMAN session only,

SQL> STARTUP MOUNT;

RMAN> sql 'alter system enable restricted session';

Oracle 10g/11g/12c/19c allows a database to be dropped and its entry removed from the RMAN catalog. The following statement drops the entire database and removes the database files:

RMAN> drop database;

The statement below drops the entire database, removes the database files, and deletes all backup copies of the database and the archive log files:

RMAN> drop database including backups;

The two statements above drop the database and delete the database files.  However, they do not unregister the database from the RMAN catalog. The following statement will remove the database information from the RMAN catalog:

RMAN> unregister database grid;

This drops the database and deletes database files, logfiles, spfile and controlfile and bring the database instance down. 

Useful Scripts for DBAs

 Below are few commonly used scripts by Oracle DBAs

Pick up files which have been modified in the last 1 day

#####################################################################################
#### Start of script for EBS 12.1.x
#####################################################################################
(
# pick up files which have been modified in the last 1 day only
HowManyDaysOld=1
echo "Picking up files which have been modified in the last ${HowManyDaysOld} days"
set -x
find $LOG_HOME/ora/10.1.3 -type f -mtime -${HowManyDaysOld} > m.tmp
find $LOG_HOME/appl/admin -type f -mtime -${HowManyDaysOld} >> m.tmp
find $LOG_HOME/appl/rgf -type f -mtime -${HowManyDaysOld} >> m.tmp
zip -r AppsLogFiles_`hostname`_`date '+%m%d%y'`.zip -@ < m.tmp
rm m.tmp
) 2>&1 | tee mzLogZip.out
#####################################################################################
#### End of script
#####################################################################################

List of EBS users connected to the application in last 3 hours

SELECT user_name username, 
description name, 
to_char(b.last_connect,'MM/DD/RR HH24:MI') lastconnect
FROM apps.fnd_user a, 
(SELECT MIN (first_connect) first_connect, 
MAX (last_connect) last_connect, last_updated_by user_id 
FROM apps.icx_sessions 
GROUP BY last_updated_by) b 
WHERE a.user_id = b.user_id 
AND last_connect>SYSDATE-3/12
ORDER BY 3 DESC
/

Locked objects in the DB

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.CLIENT_IDENTIFIER,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id order by b.client_identifier;

Number of User sessions connected on each application nodes

select icx.node_id,fnd.node_name,'Number of user sessions : ' || count( distinct icx.session_id) How_many_user_sessions
from icx_sessions icx, fnd_nodes fnd where icx.disabled_flag != 'Y'
 and icx.PSEUDO_FLAG = 'N'
 and icx.node_id=fnd.node_id
 and (icx.last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,icx.limit_time, 0,
 icx.limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate and counter < icx.limit_connects
 group by icx.node_id,fnd.node_name;

Tablespace Size

select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMB
from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
       from dba_free_space
       group by tablespace_name) a,
      (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
       from dba_data_files
       group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name
ORDER BY 2 DESC;

DB Link Scripts

/*Get the details about an existing dblink

Note: Please test this in test machine before use in production
*/

dblink_details.sql
set verify off
set pages 1200
set lines 132
column owner format a20
column db_link format a30
column username format a30
column host format a20
accept link_name char Prompt 'Enter Database Link Name : '
select owner, db_link, username, host , to_char(created,'MM/DD/YYYY HH24:MI:SS') creation_date
  from dba_db_links 
 where upper(db_link) like '%'||upper('&&link_name')||'%'
 order by owner
;
exit;

clone user script

clone_user.sql

set pages 0 feed off veri off lines 500
accept oldname prompt "Enter user to model new user to: "
accept newname prompt "Enter new user name: "
accept psw prompt "Enter new user's password: "
-- Create user...
select 'create user &&newname identified by &&psw'||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||' profile '||
profile||';'
from sys.dba_users
where username = upper('&&oldname');
-- Grant Roles...
select 'grant '||granted_role||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&oldname');
-- Grant System Privs...
select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');
-- Grant Table Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&newname;'
from sys.dba_tab_privs
where grantee = upper('&&oldname');
-- Grant Column Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||
'('||column_name||') to &&newname;'
from sys.dba_col_privs
where grantee = upper('&&oldname');
-- Tablespace Quotas...
select 'alter user &&newname quota '||
decode(max_bytes, -1, 'UNLIMITED', ceil(max_bytes / 1024 / 1024) || 'M') ||
' on '||tablespace_name||';'
from   sys.dba_ts_quotas
where  username = upper('&&oldname');
-- Set Default Role...
select 'alter user &&newname default role '|| granted_role ||';'
from sys.dba_role_privs
where grantee = upper('&&oldname')
and default_role = 'YES';
set pages 500 feed on veri on lines 500

Find your own session id

SQL> SELECT sid FROM v$mystat WHERE rownum = 1;

SQL> SELECT sid FROM V$SESSION WHERE audsid = userenv('sessionid');

SQL> SELECT sid FROM V$SESSION WHERE audsid = SYS_CONTEXT('userenv','sessionid');

select sys_context('USERENV','SID') from dual;

select sid from v$mystat where rownum <=1;

select to_number(substr(dbms_session.unique_session_id,1,4),'XXXX') mysid from dual;

select distinct sid from v$mystat;

For RAC Database with Instance ID

select username,inst_id, sid, serial# FROM gV$SESSION WHERE audsid = userenv('sessionid');

Datapump

DBA_DATAPUMP_JOBS 
This view will show the active Data Pump jobs, their state, degree of parallelism, and the number of sessions attached. 
col OWNER_NAME for a12
col JOB_NAME for a20
col OPERATION for a10
col JOB_MODE for a10
select * from dba_datapump_jobs;

DBA_DATAPUMP_SESSIONS
This view give gives the SADDR that assist in determining why a Data Pump session may be having problems. Join to the V$SESSION view for further information. 
SELECT * FROM DBA_DATAPUMP_SESSIONS;

V$SESSION_LONGOPS
This view helps determine how well a Data Pump export is doing. Basically gives you a progress indicator through the MESSAGE column. 
col USERNAME for a10
col opname for a20
col target_desc a20
col target_desc for a20
col MESSAGE for a50
select username,opname,target_desc,sofar,totalwork,message from V$SESSION_LONGOPS;

Frequency of Oracle log switches

Get details about how frequently redo log switching 
COL DAY FORMAT a15;
COL HOUR FORMAT a4;
COL TOTAL FORMAT 999;
SELECT TO_CHAR(FIRST_TIME,’YYYY-MM-DD’) DAY,
TO_CHAR(FIRST_TIME,’HH24') HOUR,
COUNT(*) TOTAL
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,’YYYY-MM-DD’),TO_CHAR(FIRST_TIME,’HH24')
ORDER BY TO_CHAR(FIRST_TIME,’YYYY-MM-DD’),TO_CHAR(FIRST_TIME,’HH24')
ASC;

Tablespace

-- All Tablespace in database with each datafile size

set line 1000
set pages 5000
col tablespace_name for a30
col file_name for a80
col free_space for 9999999
compute sum of total_space on report
compute sum of free_space on report
compute sum of MAX_SPACE on report
break on tablespace_name on report nodup

select c.tablespace_name,a.autoextensible,a.file_name,a.total_space,b.free_space, round(b.free_space/a.total_space *100,2) "Free%",a.max_space from (select file_id,file_name,sum(bytes)/1024/1024 total_space,sum(MAXBYTES)/1024/1024/1024 max_space,autoextensible from dba_data_files group by file_id,file_name,autoextensible) a,(select file_id,nvl(sum(bytes)/1024/1024,0) free_space from dba_free_space group by file_id) b, (select tablespace_name,file_id from dba_data_files) c where a.file_id=b.file_id(+) and a.file_id=c.file_id order by tablespace_name;

-- Tablespace Verification

set line 1000
col tablespace_name for a30
col file_name for a80
col free_space for 9999999
compute sum of total_space on report
compute sum of free_space on report
compute sum of max_space on report
break on tablespace_name on report nodup

select c.tablespace_name,a.autoextensible,a.file_name,a.total_space,b.free_space,
round(b.free_space/a.total_space *100,2) "Free%",a.max_space from 
(select file_id,file_name,nvl(sum(bytes)/1024/1024,0) total_space,nvl(sum(MAXBYTES)/1024/1024/1024,0) max_space,autoextensible from dba_data_files group by file_id,file_name,autoextensible) a,
(select file_id,nvl(sum(bytes)/1024/1024/1024,0) free_space from dba_free_space group by file_id) b,
(select tablespace_name,file_id from dba_data_files where tablespace_name='&DEFAULT_TABLESPACE' ) c
where a.file_id=b.file_id(+) and a.file_id=c.file_id order by tablespace_name;

-- TBS with Allocation Total/Physical

set linesize 132 tab off trimspool on
set pagesize 105
set pause off
set echo off
set feedb on

column "TOTAL ALLOC (MB)" format 9,999,990.00
column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
column "USED (MB)" format 9,999,990.00
column "FREE (MB)" format 9,999,990.00
column "% USED" format 990.00

select a.tablespace_name,
 a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
 a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
 nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
 (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from ( select tablespace_name,
 sum(bytes) physical_bytes,
 sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
 from dba_data_files
 group by tablespace_name ) a,
 ( select tablespace_name, sum(bytes) tot_used
 from dba_segments
 group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
--and (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
--and a.tablespace_name in ('NONEED')
--and a.tablespace_name not like 'UNDO%'
and a.tablespace_name='&tbs'
--- like 'Noneed%'
order by 1
--order by 5
/

-- check free space of particular tablespace
-- save the below script in file "free_space.sql" and then execute 
 
set lines 132
set pages 100 verify off
clear break 
clear compute
ttitle center 'Space Usage on Tablespaces' skip 1 center '*****************************************' skip 2
accept tablespace_name char prompt 'Enter Tablespace Name :'
select a.TABLESPACE_NAME,
       round(a.bytes_used/(1024*1024),2) TOTAL_SPACE_IN_MB,
       round(b.bytes_free/(1024*1024),2) FREE_SPACE_IN_MB,
       round(b.smallest/(1024*1024),2) min_size_in_MB,
       round(b.largest/(1024*1024),2) max_size_in_MB,
       round(((a.bytes_used-b.bytes_free)/a.bytes_used)*100,2) percent_used
  from
  (
  select TABLESPACE_NAME, sum(bytes) bytes_used
    from dba_data_files
   group by TABLESPACE_NAME
  ) a,
  (
  select TABLESPACE_NAME, sum(BYTES) bytes_free, min(BYTES) smallest, max(BYTES) largest
    from dba_free_space
   group by TABLESPACE_NAME
  ) b
 where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+)
   and a.tablespace_name = decode('&tablespace_name',null,a.tablespace_name,'&tablespace_name')
 order by ((a.BYTES_used-b.BYTES_free)/a.BYTES_used) desc;
exit;

eg:
SQL> @free.sql
Enter Tablespace Name :SYSTEM

                                                     Space Usage on Tablespaces
                                              *****************************************

TABLESPACE_NAME                TOTAL_SPACE_IN_MB FREE_SPACE_IN_MB MIN_SIZE_IN_MB MAX_SIZE_IN_MB PERCENT_USED
------------------------------ ----------------- ---------------- -------------- -------------- ------------
SYSTEM                                       710             9.38            .38              9        98.68

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rac2 ~]$


-- Check all tablespace size in the database
 
	 set linesize 150
     column tablespace_name format a20 heading 'Tablespace'
     column sumb format 999,999,999
     column extents format 9999
     column bytes format 999,999,999,999
     column largest format 999,999,999,999
     column Tot_Size format 999,999 Heading 'Total| Size(Mb)'
     column Tot_Free format 999,999,999 heading 'Total Free(MB)'
     column Pct_Free format 999.99 heading '% Free'
     column Chunks_Free format 9999 heading 'No Of Ext.'
     column Max_Free format 999,999,999 heading 'Max Free(Kb)'
     set echo off
     PROMPT  FREE SPACE AVAILABLE IN TABLESPACES
     select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
     sum(a.sumb/1048576) Tot_Free,
     sum(a.sumb)*100/sum(a.tots) Pct_Free,
     sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free
     from
     (
     select tablespace_name,0 tots,sum(bytes) sumb,
     max(bytes) largest,count(*) chunks
     from dba_free_space a
     group by tablespace_name
     union
     select tablespace_name,sum(bytes) tots,0,0,0 from
      dba_data_files
     group by tablespace_name) a
     group by a.tablespace_name
order by pct_free;
eg:

Tablespace
(TBS)
Name                                     File Count   Size(MB)   Free(MB)   Used(MB) Max Ext(MB)     % Free
---------------------------------------- ---------- ---------- ---------- ---------- ----------- ----------
SYSAUX                                            1        540         29        511  32767.9844 5.37037037
SYSTEM                                            1        710       9.37     700.62  32767.9844 1.32042254
UNDOTBS1                                          1         30       6.06      23.93  32767.9844 20.2083333
USERS                                             1       1024     846.37     177.62  32767.9844 82.6538086
SQL>


 

Undo Used/Free Space

col allocated for 999,999.999
col free      for 999,999.999
col used      for 999,999.999

select
    ( select sum(bytes)/1024/1024 from dba_data_files
       where tablespace_name like 'UND%' )  Toal_allocated_in_MB,
    ( select sum(bytes)/1024/1024 from dba_free_space
       where tablespace_name like 'UND%')  free_in_MB,
    ( select sum(bytes)/1024/1024 from dba_undo_extents
       where tablespace_name like 'UND%') USed_in_MB
from dual
/

eg: 
TOAL_ALLOCATED_IN_MB FREE_IN_MB USED_IN_MB
-------------------- ---------- ----------
                 110    31.0625    77.9375

TEMP TABLESPACE USAGE

script 1:
column used_MBytes     format 999,999
column free_Mbytes     format 999,999
column total_MBytes    format 999,999
column collect_time    format A15

select
   to_char(sysdate,'DD-MON-RR:HH24:MI') collect_time
     ,round(used_blocks*8192/1024/1024,0)  used_Mbytes
		,round(free_blocks*8192/1024/1024,0)  free_Mbytes
			,round(total_blocks*8192/1024/1024,0) total_Mbytes
       from
          V$sort_segment
          where
             tablespace_name like '%TEMP%'
/

eg:

COLLECT_TIME    USED_MBYTES FREE_MBYTES TOTAL_MBYTES
--------------- ----------- ----------- ------------
17-JUL-16:17:23           5          24           29

script 2:

set lines 180
col FreeSpaceGB format 999,999
col UsedSpaceGB format 999,999
col TotalSpaceGB format 999,999
col host_name format a30
col tablespace_name format a30
select tablespace_name,
(free_blocks*8)/1024 FreeSpaceMB,
(used_blocks*8)/1024 UsedSpaceMB,
(total_blocks*8)/1024 TotalSpaceMB,
i.instance_name,i.host_name
from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where tablespace_name='&TEMPTBS' and contents='TEMPORARY') and
i.inst_id=ss.inst_id;

eg:

TABLESPACE_NAME                FREESPACEMB USEDSPACEMB TOTALSPACEMB INSTANCE_NAME    HOST_NAME
------------------------------ ----------- ----------- ------------ ---------------- ----------------------
TEMP                                    24           5           29 orcl             rac1.rajasekhar.com

script 3: 

SELECT TABLESPACE_NAME, TABLESPACE_SIZE/1024/1024 as TABLESPACE_SIZE_MB, ALLOCATED_SPACE/1024/1024 as ALLOCATED_SPACE_MB, FREE_SPACE/1024/1024 as FREE_SPACE_MB FROM   dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE_MB ALLOCATED_SPACE_MB FREE_SPACE_MB
------------------------------ ------------------ ------------------ -------------
TEMP                                           30                 30            29

Script 4:

-- Temp TBS size for each datafile.

compute sum of Max on report
break on tablespace_name on report nodup
select tablespace_name,file_name,autoextensible,(maxbytes)/1024/1024/1024 Max,sum(bytes)/1024/1024/1024 from dba_temp_files where tablespace_name='&TEMP_TBS' group by tablespace_name,file_name,autoextensible,maxbytes order by tablespace_name;

Script 5: Which session using how much TEMP space

COLUMN tablespace FORMAT A25
COLUMN temp_size FORMAT A20
COLUMN sid_serial FORMAT A20
COLUMN username FORMAT A20
COLUMN program FORMAT A40
SET LINESIZE 200

SELECT
a.sid||','||a.serial# AS sid_serial,b.sql_id,b.segtype,
NVL(a.username, '(oracle)') AS username,
a.program, b.tablespace,ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size
FROM gv$session a,
gv$sort_usage b,
gv$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr and b.TABLESPACE='&TEMP_TABLESPACE'
ORDER BY b.tablespace, b.blocks
/

-- Database default temp 
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
col file_name for a45
select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_temp_files  order by file_name;
select username,default_tablespace,temporary_tablespace from dba_users order by temporary_tablespace;

Temp tablespace used by user

select 
   srt.tablespace, 
   srt.segfile#, 
   srt.segblk#, 
   srt.blocks, 
   a.sid, 
   a.serial#, 
   a.username, 
   a.osuser, 
   a.status 
from 
   v$session    a,
   v$sort_usage srt 
where 
   a.saddr = srt.session_addr 
order by 
   srt.tablespace, srt.segfile#, srt.segblk#, 
   srt.blocks;

Add temp file

select FILE_NAME, TABLESPACE_NAME from  dba_temp_files;
alter tablespace  temp  add tempfile '' size 1800M;
alter tablespace  temp  add tempfile '+DATAX' size 1800M;

resize tempfile

select FILE_NAME, TABLESPACE_NAME from  dba_temp_files;
alter database tempfile '' resize 500M;
alter database tempfile '' autoextend on maxsize 1G;

Verify Alert log


show parameter background

cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace

ls -ltr | tail
view alert_orcl.log

shift + G ---> to get the tail end...

?ORA-1652 ---- to search of the error...

shift + N ---- to step for next reported error...

Identify OS process ID based on database SID

- PID : Oracle process identifier
- SPID: Operating system process identifier

col sid format 999999
col username format a20
col osuser format a15
select a.sid, a.serial#,a.username, a.osuser, b.spid
from gv$session a, gv$process b
where a.paddr= b.addr
and a.sid='&sid'
order by a.sid;

Identify database SID based on OS Process ID

- PID : Oracle process identifier
- SPID: Operating system process identifier

col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from gv$session a, gv$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;

11. Find out Index Name and Column Name for Table

DBA_INDEXES gives just index names associated with the table and DBA_IND_COLUMNS gives the associated columns and their order.

SQL> column table_owner format a20
column table_name format a25
column index_name format a25
column column_name format a25
select owner, table_owner,TABLE_NAME,index_name
from   dba_indexes
where  table_name='COSTS';SQL> SQL> SQL> SQL>   2    3

OWNER                          TABLE_OWNER          TABLE_NAME                INDEX_NAME
------------------------------ -------------------- ------------------------- -------------------------
EOPS                           EOPS                 COSTS                     COSTS_PROD_BIX
EOPS                           EOPS                 COSTS                     COSTS_TIME_BIX
SH                             SH                   COSTS                     COSTS_TIME_BIX
SH                             SH                   COSTS                     COSTS_PROD_BIX

SQL> Select TABLE_OWNER, table_name, index_name, column_name, COLUMN_POSITION
FROM   dba_ind_columns
Where  TABLE_OWNER='SH'
AND    table_name='COSTS'
AND    INDEX_NAME='COSTS_PROD_BIX'
Order by TABLE_OWNER, table_name, column_name;  2    3    4    5    6

TABLE_OWNER          TABLE_NAME                INDEX_NAME                COLUMN_NAME               COLUMN_POSITION
-------------------- ------------------------- ------------------------- ------------------------- ---------------
SH                   COSTS                     COSTS_PROD_BIX            PROD_ID                                 1

SQL>

12. Find DB Size

Find out total file size of a Oracle database

SQL> select b.name,
( select sum(bytes)/1024/1024/1024 DF_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 TF_size from dba_temp_files ) +
  2    3    4  ( select sum(bytes)/1024/1024/1024 RLF_size from sys.v_$log ) +
  5  ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 CF_size from v$controlfile) "Size in GB"
  6  from  dual,  (select name from v$database ) b;

NAME      Size in GB
--------- ----------
CAT       5.36999512

SQL>

Find out the data occupied size for an Oracle database


SQL> SELECT name,sum(bytes)/1024/1024/1024 AS "Size in GB" FROM dba_segments,
(select name from v$database) group by name;  2

NAME      Size in GB
--------- ----------
CAT       2.37426758

SQL>

13. SQL Query to Get Hostname, IP Address and Terminal from Oracle Database

A. Using sys_context

SQL> SELECT SYS_CONTEXT ('USERENV', 'SERVER_HOST') from dual;

SYS_CONTEXT('USERENV','SERVER_HOST')
-----------------------------------------
rac2

SQL> SELECT SYS_CONTEXT('USERENV','HOST') FROM dual;

SYS_CONTEXT('USERENV','HOST')
----------------------------------------
rac2.rajasekhar.com

SQL>

B. Using v$instance

SQL> select host_name from v$instance;

HOST_NAME
---------------------------------------
rac2.rajasekhar.com

SQL>

C. UTL_INADDER.get_host_name

SQL> SELECT UTL_INADDR.get_host_name FROM dual;

GET_HOST_NAME
--------------------------------------------------------------------------------
rac2.rajasekhar.com

SQL>

D. UTL_INADDR.get_host_address

SQL> SELECT UTL_INADDR.get_host_address from dual;

GET_HOST_ADDRESS
-------------------------------------
192.168.2.102

SQL>

E. Query to find out TERMINAL

SQL> SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM dual;

SYS_CONTEXT('USERENV','TERMINAL')
-----------------------------------
pts/2

SQL>

14. Archive Log Generation Report Thread Wise Daily

SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF

SELECT TRUNC(completion_time)  "Generation Date" , thread#,count(*)
FROM gv$archived_log
GROUP BY TRUNC(completion_time),thread#
ORDER BY TRUNC(completion_time);

15. Redo generation per day

select trunc(completion_time) rundate
,count(*)  logswitch
,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)"
from gv$archived_log
group by trunc(completion_time)
order by 1;

16. Redo Generated per Hour

SELECT  Start_Date,   Start_Time,   Num_Logs,
Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),2) AS Mbytes, Vdb.NAME AS Dbname
FROM (SELECT To_Char(Vlh.First_Time, 'YYYY-MM-DD') AS Start_Date,
To_Char(Vlh.First_Time, 'HH24') || ':00' AS Start_Time, COUNT(Vlh.Thread#) Num_Logs
FROM gv$log_History Vlh 
GROUP BY To_Char(Vlh.First_Time,  'YYYY-MM-DD'),
To_Char(Vlh.First_Time, 'HH24') || ':00') Log_Hist,
gv$log Vl ,  gv$database Vdb
WHERE Vl.Group# = 1
ORDER BY Log_Hist.Start_Date, Log_Hist.Start_Time;

17. Hourly archivelog switches

SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from
v$log_history
where to_date(first_time) > sysdate -8
GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time)
/

18. Generating Service based ASH Report

-- Generating Service based ASH Report

select output from table(
DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(
   l_dbid          =>(select dbid from v$database),
   l_inst_num      =>(select 1 from v$instance), --pls. replace with appropriate instance number
   l_btime         =>to_date('28-JAN-2016 07:05:00','dd-mon-yyyy hh24:mi:ss'),
   l_etime         =>to_date('28-JAN-2016 07:15:00','dd-mon-yyyy hh24:mi:ss'),
/*   l_options       IN NUMBER    DEFAULT 0,
   l_slot_width    IN NUMBER    DEFAULT 0,
   l_sid           IN NUMBER    DEFAULT NULL,
   l_sql_id        IN VARCHAR2  DEFAULT NULL,
   l_wait_class    IN VARCHAR2  DEFAULT NULL,*/
   l_service_hash  =>(select name_hash from dba_services where name=upper(''))));

19. Verify Archive Log GAP

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;