Show_user_with_top_io

SELECT ses.sid, ses.serial#, ses.osuser, ses.process, sio.physical_reads
   FROM v$session ses, v$sess_io sio
  WHERE ses.sid  = sio.sid
    AND nvl(ses.username,'SYS') not in ('SYS', 'SYSTEM')
    AND sio.physical_reads = (SELECT MAX(physical_reads)
                                FROM v$session ses2, v$sess_io sio2
                               WHERE ses2.sid = sio2.sid
                                 AND ses2.username
                                  NOT IN ('SYSTEM', 'SYS'));

Show_statistics_of_connected_users

col osuser format a10 trunc heading "OSUSER AS"
col orauser format a10 trunc
col machine format a10 trunc
col sprogram format a15 trunc
col process format a20 trunc
col server format a3 trunc
col sess_id format 9999
col proc_id format a7
--
SELECT  s.osuser osuser,
        s.username orauser,
        s.machine machine,
        s.program sprogram,
        p.program process,
        s.sid sess_id,
        p.spid proc_id,
        s.logon_time,
        s.server server
FROM    v$session s,
        v$process p
WHERE   s.paddr = p.addr
AND     type != 'BACKGROUND'
AND     p.username is not null
ORDER BY 6
/
col osuser clear
col machine clear
col orauser clear
ttitle off

Rac_balance_delta

col instance_name format a20 heading "Instance|name"
col elapsed_seconds format 999,999 Heading "Elapsed|seconds"
col cpu_ms_ps format 999,999.99 heading "CPU ms|p.s."
col db_ms_ps format 999,999.99 heading "DB time|ms p.s."
set pages 1000
set lines 80
set echo on

WITH cluster_delta as (
    SELECT instance_name, start_timestamp,end_timestamp,
     round((end_timestamp-start_Timestamp)*24*3600,2) elapsed_seconds,
           SUM(CASE WHEN stat_name = 'DB CPU'
                    THEN VALUE/1000 END) cpu_ms,
            SUM(CASE WHEN stat_name = 'DB time'
                     THEN VALUE/1000 END) db_ms
    FROM table(opsg_pkg.service_stat_report())
    JOIN gv$instance using (inst_id)
   GROUP BY instance_name,start_timestamp,end_timestamp
)
SELECT instance_name, elapsed_seconds,
       ROUND(cpu_ms / elapsed_seconds, 2) cpu_ms_ps,
       ROUND(cpu_ms / elapsed_seconds, 2) db_ms_ps
FROM cluster_delta
ORDER BY instance_name; 

Logged on Users

SET linesize 200 PAGESIZE 1000 FEED OFF;
COLUMN "sid,serial#" FORMAT A12
COLUMN "pid" FORMAT 999
COLUMN "status" FORMAT A8
COLUMN "schema" FORMAT A10
COLUMN "osuser" FORMAT A10
COLUMN "box" FORMAT A16
COLUMN "prg" FORMAT A30
--
SELECT
       ''''
    || s.sid
    || ','
    || s.serial#
    || ''';'                        "sid,serial#"
     , p.spid                        "pid"
     , s.status                      "status"
     , s.schemaname                  "schema"
     , s.osuser                      "osuser"
     , s.machine                     "box"
     , s.program                     "prg"
     , TO_CHAR(s.logon_time, 'DD.MM.YYYY HH24:MI') "logon_time"
  FROM
       v$session s,
       v$process p
WHERE s.paddr  = p.addr
ORDER BY s.username
/

Invalieds

spool show_summary_invalid_objects.lst
set pause off
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;

ttitle left 'Summary of invalid objects for User: ' sql.user -
skip 2

column object_type format a25 wrap heading 'Object|Type'
column status format a8 heading 'Status'

SELECT DISTINCT (object_type), status, COUNT(*)
FROM dba_objects
WHERE status != 'VALID'
GROUP BY owner, object_type, status;

Current Database Account

set termout off
set head off
set termout on

select 'User: '|| user || ' on database ' || global_name,
       '(Terminal='||USERENV('TERMINAL')||
       ', Session-Id='||USERENV('SESSIONID')||')'
from   global_name;

set head on feed on

Asadmin with Remote GlassFish

Asadmin with Remote GlassFish

 I've  faced issue while  working  with Amazon EC2, I'm starting to appreciate how useful theGlassFish asadmin tool is with a remote system. Here are some steps to get GlassFish set up for secure, remote access.

Step 1: Setup

After starting up a remote machine, copy the GlassFish zip file there and unzip it. At this point, I only have the standard ssh and http ports open in my firewall ("security group" on Amazon EC2). If you start with port 4848 open, then someone could access the server through the admin console in a browser before you've had a chance to change the password or admin username.

Step 2: Secure admin

With GlassFish installed, start the server, then change the admin user's password and enable secure administration. Without secure administration on, remote systems cannot talk to the server at all. Here are the commands to run locally from your ssh session:

·                     asadmin start-domain

·                     asadmin change-admin-password

After changing the admin password, you may want to run asadmin login as well to avoid having to specify the password again and again. This will only affect local access to the server (e.g. your environment while ssh'ed into the remote machine). Next:

·                     asadmin enable-secure-admin

·                     asadmin restart-domain

For more information on secure admin, see Tim Quinn's blog on the subject.

Step 3: Enable access

You can now open port 4848 in the security group/firewall so that your local asadmin client can talk to the remote server. If you want to remove the 'admin' user and create an administrative user with a different name, you can now reach the admin console at port 4848 in a browser:

1.             Log in as 'admin' user.

2.             In the left-hand panel, open Configurations, server-config, Security, Realms, admin-realm.

3.             Click Manage Users on the right and create the new user in the asadmin group.

4.             Log out as 'admin' then log in as your new user to delete the 'admin' user.

If you change the admin user, use the new user name in place of 'admin' below.

Step 4: Set up local access

With secure administration turned on, you can now access the remote machine like this (note that the port argument is only needed if you have changed the admin port):

ip-sec> asadmin --host <hostname> --user admin --port 4848 --secure list-applications
Enter admin password for user "admin">
Nothing to list.
Command list-applications executed successfully.

Of course, you may not want to specify the command line options over and over and supply the password manually each time. You can specify these environment variables instead:

·                     AS_ADMIN_HOST

·                     AS_ADMIN_PORT

·                     AS_ADMIN_SECURE (set to "true" without the quotes)

·                     AS_ADMIN_USER

·                     AS_ADMIN_PASSWORDFILE

The last one should be set to the path of a file with these contents:

AS_ADMIN_PASSWORD=<your password>

Now you can access your remote server with simple asadmin commands with no other parameters:

ip-sec}> asadmin list-applications
Nothing to list.
Command list-applications executed successfully.

Note: if you want to see all the parameters that are being used in the asadmin command, you can use the --echo option to have them printed:

Automated auto start stop oracle database RedHat Linux Booting

If you are using Oracle Clusterware 10gR2 or above for RAC or just for a single instance using ASM, the Clusterware automatically starts and stops the Oracle database instances and listeners, so the following procedures are not necessary. Where the Clusterware is not being used, these methods allow you to automate the startup and shutdown of databases on Linux.

The "su" Command

The following represents the Oracle recommended method for automating database startup and shutdown of Oracle 9i instances on Linux, but it works equally well for Oracle 10g, 11G and 12c also. It can be used on any RHEL-style distribution, including Oracle Linux, up to an including RHEL7.

Once the instance is created, edit the "/etc/oratab" file setting the restart flag for each instance to 'Y'.

PROD:/u01/app/oracle/product/11.2.0/PROD:Y

Create a file called "/etc/init.d/oracle" as the root user, containing the following code.

#!/bin/sh

#

# /etc/rc.d/init.d/dbase

# Description: Starts and stops the Oracle database and listeners

# See how we were called.

ORAHOME1=/u01/app/oracle/product/11.2.0/db_1

ORAHOME2=/u01/app/oracle/product/11.2.0/PROD

case "$1" in

  start)

        echo -n "Starting All Oracle Databases: "

        echo "----------------------------------------------------" >> /var/log/dbase.log

        date +"! %T %a %D : Starting Oracle Databases as part of system up." >> /var/log/dbase.log

        echo "----------------------------------------------------" >> /var/log/dbase.log

                echo -n "Starting Oracle Listeners:PROD"

        su - oracle -c "$ORAHOME2/bin/lsnrctl start PROD" >> /var/log/dbase.log

        echo "Done."

        su - oracle -c dbstart >> /var/log/dbase.log

        echo "Done."

                echo -n "Starting Oracle Listeners:ORCL "

        su - oracle -c "$ORAHOME1/bin/lsnrctl start ORCL" >> /var/log/dbase.log

        echo "Done."

        echo ""

        echo "----------------------------------------------------" >> /var/log/dbase.log

        date +"! %T %a %D : Finished." >> /var/log/dbase.log

        echo "----------------------------------------------------" >> /var/log/dbase.log

        echo ""

        echo "----------------------------------------------------" >> /var/log/dbase.log

        date +"! %T %a %D : Finished." >> /var/log/dbase.log

        echo "----------------------------------------------------" >> /var/log/dbase.log

                touch /var/lock/subsys/oracle

        ;;

  stop)

        echo -n "Shutting Down Oracle Listeners: "

        echo "----------------------------------------------------" >> /var/log/dbase.log

        date +"! %T %a %D : Shutting Down Oracle Databases because of system down." >> /var/log/dbase.log

        echo "----------------------------------------------------" >> /var/log/dbase.log

        su - oracle -c "$ORAHOME1/bin/lsnrctl stop ORCL" >> /var/log/dbase.log

        echo "Done. ORCL"

                su - oracle -c "$ORAHOME2/bin/lsnrctl stop PROD" >> /var/log/dbase.log

        echo "Done. PROD"

        rm -f /var/lock/subsys/oracle

        echo -n "Shutting Down All Oracle Databases: "

        su - oracle -c dbshut >> /var/log/dbase.log

        echo "Done."

        echo ""

        echo "----------------------------------------------------" >> /var/log/dbase.log

        date +"! %T %a %D : Finished." >> /var/log/dbase.log

        echo "----------------------------------------------------" >> /var/log/dbase.log

        ;;

  restart)

        echo -n "Restarting Oracle Databases: "

        echo "----------------------------------------------------" >> /var/log/dbase.log

        date +"! %T %a %D : Restarting Oracle Databases as part of system up." >> /var/log/dbase.log

        echo "----------------------------------------------------" >> /var/log/dbase.log

        su - oracle -c dbshut >> /var/log/dbase.log

        su - oracle -c dbstart >> /var/log/dbase.log

        echo "Done."

        echo -n "Restarting Oracle Listeners: ORCL"

        su - oracle -c "$ORAHOME1/bin/lsnrctl stop ORCL" >> /var/log/dbase.log

        su - oracle -c "$ORAHOME1/bin/lsnrctl start ORCL" >> /var/log/dbase.log

                 echo "Done. ORCL"

                echo -n "Restarting Oracle Listeners: PROD"

        su - oracle -c "$ORAHOME2/bin/lsnrctl stop PROD" >> /var/log/dbase.log

        su - oracle -c "$ORAHOME2/bin/lsnrctl start PROD" >> /var/log/dbase.log

        echo "Done. PROD"

        echo ""

        echo "----------------------------------------------------" >> /var/log/dbase.log

        date +"! %T %a %D : Finished." >> /var/log/dbase.log

        echo "----------------------------------------------------" >> /var/log/dbase.log

        touch /var/lock/subsys/oracle

        ;;

  *)

        echo "Usage: oracle {start|stop|restart}"

        exit 1

esac

Use the chmod command to set the privileges to 750.

chmod 750 /etc/init.d/oracle

Associate the "oracle" service with the appropriate run levels and set it to auto-start using the following command.

chkconfig --add oracle

For Multiple database please follow the steps. Edit the dbstart file on your first ORACLE_HOME as per the /etc/oratab and add the bellow lines to  add the multiple listeners.

cd $ORACLE_HOME/bin

vim dbstart   (Edit the file and add the oracle home and listener name)

#PROD

ORACLE_HOME_LISTNER=/u01/app/oracle/product/11.2.0/PROD

if [ ! $ORACLE_HOME_LISTNER ] ; then

  echo "ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener"

  echo "Usage: $0 ORACLE_HOME"

else

  LOG=$ORACLE_HOME_LISTNER/listener.log

  # Set the ORACLE_HOME for the Oracle Net Listener, it gets reset to

  # a different ORACLE_HOME for each entry in the oratab.

  export ORACLE_HOME=$ORACLE_HOME_LISTNER

  # Start Oracle Net Listener

  if [ -x $ORACLE_HOME_LISTNER/bin/tnslsnr ] ; then

    echo "$0: Starting Oracle Net Listener" >> $LOG 2>&1

    $ORACLE_HOME_LISTNER/bin/lsnrctl start PROD >> $LOG 2>&1 &

    VER10LIST=`$ORACLE_HOME_LISTNER/bin/lsnrctl version | grep "LSNRCTL for " | cut -d' ' -f5 | cut -d'.' -f1`

    export VER10LIST

  else

    echo "Failed to auto-start Oracle Net Listener using $ORACLE_HOME_LISTNER/bin/tnslsnr"

  fi

fi

same as edit the dbshut file

#PROD

# The  this to bring down Oracle Net Listener

ORACLE_HOME_LISTNER=/u01/app/oracle/product/11.2.0/PROD

if [ ! $ORACLE_HOME_LISTNER ] ; then

  echo "ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener"

  echo "Usage: $0 ORACLE_HOME"

else

  LOG=$ORACLE_HOME_LISTNER/listener.log

  # Set the ORACLE_HOME for the Oracle Net Listener, it gets reset to

  # a different ORACLE_HOME for each entry in the oratab.

  export ORACLE_HOME=$ORACLE_HOME_LISTNER

  # Stop Oracle Net Listener

  if [ -f $ORACLE_HOME_LISTNER/bin/tnslsnr ] ; then

    echo "$0: Stoping Oracle Net Listener" >> $LOG 2>&1

    $ORACLE_HOME_LISTNER/bin/lsnrctl stop PROD >> $LOG 2>&1 &

  else

    echo "Failed to auto-stop Oracle Net Listener using $ORACLE_HOME_LISTNER/bin/tnslsnr"

  fi

fi

Difference in location of Log files in Oracle EBS Release 12.1.3 and Oracle EBS Release 12.2.4

The Log files locations in Oracle EBS Release 12.1.3 and  Oracle EBS R 12.2.4  are given below:

1.Instance startup and configuration Log files are located for INST_TOP in Oracle Release 12.1.3 are below:

$INST_TOP/logs/appl/admin/log
Startup/Shutdown error message related to tech stack (10.1.2, 10.1.3 forms/reports/web)
$INST_TOP/logs/ora/ (10.1.2 & 10.1.3)
$INST_TOP/logs/ora/10.1.3/Apache/error_log[timestamp](Apache log files)
$INST_TOP/logs/ora/10.1.3/opmn/ (OC4J, oa*, opmn.log)
$INST_TOP/logs/ora/10.1.2/network/ (listener log)
$INST_TOP/apps/$CONTEXT_NAME/logs/appl/conc/log (CM log files)

2. Log files related to cloning in R12.1.3 are as below:

 Preclone log files in source instance
Database Tier – $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/(StageDBTier_MMDDHHMM.log)
Application Tier –
$INST_TOP/apps/$CONTEXT_NAME/admin/log/(StageAppsTier_MMDDHHMM.log)

Clone log files in target instance
Database Tier – $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTier_.log
Apps Tier – $INST_TOP/admin/log/ApplyAppsTier_.log

3. Patching related log files in R12.1.3 are as below:

i) Application Tier adpatch log – $APPL_TOP/admin/$SID/log/
ii) Developer (Developer/Forms & Reports 10.1.2) Patch – $ORACLE_HOME/.patch_storage
iii) Web Server (Apache) patch – $IAS_ORACLE_HOME/.patch_storage
iv) Database Tier opatch log – $ORACLE_HOME/.patch_storage


4. Autoconfig related log files in R12.1.3 are as below:

a) Database Tier Autoconfig log :
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/MMDDHHMM/adconfig.log
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/MMDDHHMM/NetServiceHandler.log


b) Application Tier Autoconfig log : 
$INST_TOP/apps/$CONTEXT_NAME/admin/log/$MMDDHHMM/adconfig.log

5.Autoconfig context file location in R12.1.3 :
$INST_TOP/apps/$CONTEXT_NAME/appl/admin/$CONTEXT_NAME.xml


6)R12.1.3 Installation Logs in R12.1.3 are as below:

 Database Tier Installation
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/.log
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTechStack_.log
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ohclone.log
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/make_.log
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/installdbf.log
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/adcrdb_.log RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDatabase_.log
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME//adconfig.log
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME//NetServiceHandler.log
Application Tier Installation
$INST_TOP/logs/.log
$APPL_TOP/admin/$CONTEXT_NAME/log/ApplyAppsTechStack.log
$INST_TOP/logs/ora/10.1.2/install/make_.log
$INST_TOP/logs/ora/10.1.3/install/make_.log
$INST_TOP/admin/log/ApplyAppsTechStack.log
$INST_TOP/admin/log/ohclone.log
$APPL_TOP/admin/$CONTEXT_NAME/log/installAppl.log
$APPL_TOP/admin/$CONTEXT_NAME/log/ApplyAppltop_.log
$APPL_TOP/admin/$CONTEXT_NAME/log//adconfig.log
$APPL_TOP/admin/$CONTEXT_NAME/log//NetServiceHandler.log
Inventory Registration:
$Global Inventory/logs/cloneActions.log
$Global Inventory/logs/oraInstall.log
$Global Inventory/logs/silentInstall.log

7) Log files related with relink,Network,OUT inventory logs for R12.1.3 are as below:
 1) Database Tier
1.1) Relink Log files :
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME /MMDDHHMM/ make_$MMDDHHMM.log
1.2) Alert Log Files :
$ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log
1.3) Network Logs :
$ORACLE_HOME/network/admin/$SID.log
1.4) OUI Logs :
OUI Inventory Logs :
$ORACLE_HOME/admin/oui/$CONTEXT_NAME/oraInventory/logs
2) Application Tier
$ORACLE_HOME/j2ee/DevSuite/log
$ORACLE_HOME/opmn/logs
$ORACLE_HOME/network/logs
Tech Stack Patch 10.1.3 (Web/HTTP Server)
$IAS_ORACLE_HOME/j2ee/forms/logs
$IAS_ORACLE_HOME/j2ee/oafm/logs
$IAS_ORACLE_HOME/j2ee/oacore/logs
$IAS_ORACLE_HOME/opmn/logs
$IAS_ORACLE_HOME/network/log
$INST_TOP/logs/ora/10.1.2
$INST_TOP/logs/ora/10.1.3
$INST_TOP/logs/appl/conc/log
$INST_TOP/logs/appl/admin/log


In EBS R12.2.4 the log files locations are as below:

1)Log files file Online patching (adop) in EBS R12.2.4 are in below location:

The adop log files are located on the non-editioned file system (fs_ne), under:

$NE_BASE/EBSapps/log/adop/<adop_session_id>/<phase>_<date>_<time>/<context_name>/log

This log directory will contain patch logs,patch worker logs.

adop(phase=fs_clone) Online pathcing filesystem cloning process related log files are found under:

$INST_TOP/admin/log


2)Log files for Autoconfig process in Oracle EBS R12.2.4 are below:

On Applicaion Tier: $INST_TOP/admin/log/<MMDDhhmm>
On Database Tier: $ORACLE_HOME/appsutil/log/<CONTEXT_NAME>/<MMDDhhmm>

3)Log files for start/stop of services from $ADMIN_SCRIPTS_HOME

In below directory we will find log files related to start/stop process of oacore, forms, apache, opmn, 
weblogic admin server/node manager:

$LOG_HOME/appl/admin/log


4)Log/Out files for Concurrent programs/managers in Oracle R12.2.4 are in below location:

Log/Out files for Oracle Release 12.2 are stored in Non-Editioned filesystem(NE).

Log files: $APPLCSF/$APPLLOG (or $NE_BASE/inst/<CONTEXT_NAME>/logs/appl/conc/log)
Out files: $APPLCSF/$APPLOUT (or $NE_BASE/inst/<CONTEXT_NAME>/logs/appl/conc/out)


5)Log files for OPMN and OHS processes in Oracle R12.2.4 are in below location:

Below directory contains log files related OPMN process(opmn.log), 
OPMN Debug logs(debug.log), HTTP Transaction logs (access.log),security settings related logs.

$IAS_ORACLE_HOME/instances/<ohs_instance>/diagnostics/logs


6)Log file for Weblogic Node Manager in Oracle R12.2.4 are in below location:

Log file is generated by Node Manager and contains data for all domains that 
are controlled by Node Manager on a given physical machine.

$FMW_HOME/wlserver_10.3/common/nodemanager/nmHome1/nodemanager.log


7)Log file for Weblogic  in Oracle R12.2.4 for Oracle Management Service are below

Initial settings for AdminServer and Domain level information is written in this log file

$EBS_DOMAIN_HOME/sysman/log


8)Log files for server processes initiated through Weblogic in Oracle R12.2.4 are in below location:
Stdout and stderr messages generated by the forms, oafm and oacore services are located 
at NOTICE severity level or higher are written by Weblogic Node Manager to below directory.

$EBS_DOMAIN_HOME/servers/<server_name>/logs/<server_name>.out