TODO

      echo Auto Hang Detection = hang
      echo System Profiling = profile
      echo Session Tracing = session
    

TODO

      echo Generate all cpu gif files = -31
      echo Generate all memory gif files = -32
	    echo Generate all disk gif files = -33
      echo Generate all oracle gif files = -34
      echo Generate all user requested statistics gif files = -36
      echo All of the above = -31 -32 -33 -34 -36
    

    Command Summary
    
    This command starts the Lite Onboard Monitor (LTOM) in background mode.
    
    You should edit the $TOM_HOME/init/tom_deploy.properties file. Modify the
    associated input parameters for the component of LTOM you will be using.
    The tom_deploy.properties file contains a section for each component of
    the tool with some default values. Modify these parameters making sure
    you uncomment the parameter by removing the beginning #. Save your
    changes.
    
    For more information, see Note 352363.1.
    
    Input Requirements
      o Full path to the LTOM home (TOM_HOME)
      o Username for LTOM
      o Password for LTOM
    
    Output Details
      o Confirmation that LTOM was successfully started
  
    

# LTOM utility
    if [ -d %%DA.host.tom_home%% ]; then
      TOM_HOME=%%DA.host.tom_home%%
      export TOM_HOME
      cd $TOM_HOME
      
      ## Check if we already have the userid in the properties file, and
      ## if not, add it...
      IS_USERID_PRESENT=`grep DB_USERID= $TOM_HOME/init/tom_deploy.properties`
      if [ "x${IS_USERID_PRESENT}" = "x" ]; then
        echo "DB_USERID=%%tom_user%%" >> $TOM_HOME/init/tom_deploy.properties
      fi
      
      ## Add password, if not present
      IS_PASSWD_PRESENT=`grep XXRD= $TOM_HOME/init/tom_deploy.properties`
      if [ "x${IS_PASSWD_PRESENT}" = "x" ]; then
        ./startltom.sh -p << ZEN
tom
ZEN
      fi
       
      ## Now start LTOM in background, and it should not need any user/pass
      ## To do so, we have to put our command in a temporary script...
      TMPFILE=`echo $TOM_HOME/darunltom.sh`
      
      ## Clean up any old copies...
      rm -f ${TMPFILE}
      
      ## Write our commands to the temp file
      echo "# Temp file to run startltom.sh with -s param" > ${TMPFILE}
      echo "#!/bin/sh" >> ${TMPFILE}
      echo "cd $TOM_HOME" >> ${TMPFILE}
      echo "./startltom.sh -s" >> ${TMPFILE}

      ## And finally, run it, so it stays running after DA exits
      at now < ${TMPFILE} > /dev/null 2>&1
      
      ## Give the script a chance to launch LTOM
      sleep 10
      
      ## Grep to find ltom processes
      TOM_PROC=`ps -ef |grep ltom.jar | grep -v grep`
    
      ## If > 0, we succeeded
      if [ "x${TOM_PROC}" != "x" ]; then
        echo "LTOM is running..."
      else
        echo "ERROR: Failed to start LTOM."
        echo "Please make sure you have properly configured the"
        echo "tom_deploy.properties file to specify which service you desire."
        echo "Then re-try this command."
        exit 2
      fi
      
    else
      echo "ERROR: Invalid directory specified for TOM_HOME."
      echo "Please recheck the path provided and try again."
      exit 9
    fi
    
    

    Command Summary
      
    This command stops the Lite Onboard Monitor (LTOM) that is
    currently running in background mode.
    
    You must specify which service you wish to stop:
      Auto Hang Detection
      System Profiling
      Session Tracing
    
    For more information, see Note 352363.1.
    
    Input Requirements
      o Full path to the LTOM home (TOM_HOME)
      o Service name to stop - hang, profile or session

    Output Details
      o Confirmation that the LTOM process has completed
      
    

# LTOM utility
    if [ -d %%DA.host.tom_home%%/tmp ]; then
      cd %%DA.host.tom_home%%/tmp
      ## Create file with command to stop LTOM
      touch %%PREREQ_tom_cmd%%.stop
    else
      echo "ERROR: Invalid directory specified for TOM_HOME."
      echo "Please recheck the path provided and try again."
      exit 9
    fi
    
    ## 5 secs to find it, and 25 to shut it down...
    sleep 30
    
    ## Grep to find ltom processes
    TOM_PROC=`ps -ef |grep ltom | grep -v grep`
    
    ## If none, we succeeded
    if [ "x${TOM_PROC}" = "x" ]; then
      echo "Success! Found no running LTOM processes."
    else
      echo "WARNING: Still found LTOM processes running."
      echo "Check that you are choosing the correct service to shut down and try again."
      exit 2
    fi
    
    
    

    Command Summary
    
    This command runs LTOMg on a specific log file to generate graphical
    representations of the data. You should have
    already configured LTOMg to have the desired settings.
    
    LTOMg parses the LTOM System Profiler log files generated by running the 
    LTOM System Profiler. This file must be located in the 
    $TOM_HOME/recordings/profile directory.
    
    Once the data is parsed, the user is presented with a 
    command line menu which has options for displaying graphs, generating 
    binary gif files of these graphs and generating a html report of the 
    system profile.
        
    For more information, see Note 461050.1.
    
    Input Requirements
      o Full path to the LTOM home (TOM_HOME)
      o LTOM System Profiler log file to analyze (usually located in
        $TOM_HOME/recordings/profile)
      o Desired graphical output  
        
    Output Details
      o GIF files with desired graphical output
  
    

# LTOM utility
    if [ -d %%DA.host.tom_home%%/ltomg ]; then
      TOM_HOME=%%DA.host.tom_home%%
      export TOM_HOME
      cd $TOM_HOME/ltomg
      
      ## If they didn't specify what to run, run with "-31" (cpu graphics)
      if [ "x%%PREREQ_tomg_options%%" = "x" ]; then
        TOMG_OPT=-31
      else
        TOMG_OPT=%%PREREQ_tomg_options%%
      fi
      
      ## Check if file they specified exists
      if [ -r %%profile_file%% ]; then
        java -jar ltomg.jar -i %%profile_file%% $TOMG_OPT
      fi    

    else
      echo "ERROR: Invalid directory specified for the tom_base."
      echo "Please recheck the path provided and try again."
      exit 9
    fi
    
    

    Command Summary
    
    This command runs the opatch command and lists the patches that have
    been applied to this ORACLE_HOME. 
     
    Input Requirements
      o Enter the full path to the oraInst.loc file
    
    Output Details
      o Produces a report of all patches applied using opatch to this ORACLE_HOME
  
    

#!/bin/bash
      if [ -r "%%INVPTRLOC%%" ]; then
        cd ${ORACLE_HOME}/OPatch
        ./opatch lsinventory -detail -invptrloc \
          "%%INVPTRLOC%%" -patch
      else
        echo "Invalid path to oraInst.loc. Please try again."
        exit 1
      fi
    

    Command Summary
    
    This command shows the current status of Procwatcher. 
      
    Input Requirements
      o None
    
    Ouput Details
      o Current status of Procwatcher
      
    

%%DA.host.DA_PROCWATCHER_DIR%%/prw.sh stat  2>&1
    
    

    Command Summary
    
    This command starts Procwatcher.
      
    Input Requirements
      o None
    
    Ouput Details
      o Confirmation that Procwatcher was started
      
    

%%DA.host.DA_PROCWATCHER_DIR%%/prw.sh start

    Command Summary
    
    This command stops Procwatcher. 
      
    Input Requirements
      o None
    
    Ouput Details
      o Confirmation that Procwatcher was terminated
      
    

%%DA.host.DA_PROCWATCHER_DIR%%/prw.sh stop

    Command Summary
    
    This command packages Procwatcher files. 
      
    Input Requirements
      o None
    
    Ouput Details
      o Confirmation that Procwatcher files have been packaged
      
    

%%DA.host.DA_PROCWATCHER_DIR%%/prw.sh pack
    echo DA_FILE_COLLECTED=$PWD/Procwatcher_Files_.*.zip
    

 
    Command Summary
    
    Verify Oracle Listener Status:
    
    Input Requirements
      o   
    
    Ouput Details
      o lsnrctl status
    

 
#!/bin/sh
if [ "%%LSNTRNAME%%" = "all" ]; then
${ORACLE_HOME}/bin/lsnrctl <<EOF
status
services
exit
EOF
else
${ORACLE_HOME}/bin/lsnrctl <<EOF
status %%LSNTRNAME%%
services %%LSNTRNAME%%
exit
EOF
fi
    errorCode=$?    # checks if the last operation (lsnrctl) was completed successfully or not
    if [ ${errorCode} -ne 0 ]; then
      echo  "FAILURE! Listner is DOWN. ERROR  ${errorCode}" | tee -a /tmp/sqltest.txt
      echo  "Contact your DBA or refer: http://docs.oracle.com/cd/B19306_01/network.102/b14212/troublestng.htm#BABBBGGG"| tee -a /tmp/sqltest.txt
      exit ${errorCode}
    else
      echo  "SUCCESS! Listner Status okay" | tee -a /tmp/sqltest.txt
    fi
    
    # echo DA_FILE_COLLECTED=/tmp/sqltest.txt
     
    

 
    Command Summary
    
    Collection of configuration files: sqlnet.ora,listener.ora,tnsnames.ora,ldap.ora
    Collection of log files: listener.log,sqlnet.log
    Input Requirements
      o   
    
    Ouput Details
      o lsnrctl status
    

 
#!/bin/sh
if [ "%%TNS_ADMIN%%" = "" ]; then
find %%TNS_ADMIN%% -name "*.ora"  ! -path "/samples\*" -exec echo DA_FILE_COLLECTED='{}' \; 
find %%TNS_ADMIN%% -name listener.log -o -name sqlnet.log -exec echo DA_FILE_COLLECTED='{}' \; 
else 
find ${ORACLE_HOME}/network/admin -name "*.ora"  ! -path "/samples\*" -exec echo DA_FILE_COLLECTED='{}' \; 
find ${ORACLE_HOME}/network/admin -name listener.log -o -name sqlnet.log -exec echo DA_FILE_COLLECTED='{}' \; 
fi
    

TODO

      echo Tuning=T
      echo Diagnostics=D
      echo None=N
    

    Command Summary
    
    This command uses the SQL Tuning Health-Check Script.
      
    Input Requirements
      o Full path to SQLT (must be previously installed)
      o Oracle Pack License - valid values are T for Tuning, D for Diagnostics
        (includes Tuning) and N for none
      o The SQL_ID or HASH VALUE to be analyzed
    
    Ouput Details
      o An HTML file of the analysis results
        
    

# SQLT utility
       
    if [ -d %%DA.host.sqlt_dir%%/utl ]; then
      echo "Changing directory to %%DA.host.sqlt_dir%%/utl..."
      cd %%DA.host.sqlt_dir%%/utl
      
      if [ -r sqlhc.sql ]; then
        exit | sqlplus / as sysdba @sqlhc.sql %%PREREQ_os_230_lic%% %%sql_id%%
      else
        echo "ERROR: Unable to find the sqlhc.sql script in %%DA.host.sqlt_dir%%/utl"
        echo "Please verify that you are providing the correct path to the SQLT scripts."
        exit 9
      fi
    else
      echo "ERROR: Directory specified for SQLT (%%DA.host.sqlt_dir%%) is not accessible."
      echo "Please verify that you are providing the correct path to the SQLT scripts."
      exit 8
    fi
    
    

    Command Summary
    
    This command uses the SQLTXPLAIN (SQLT) XTRACT command.
      
    Input Requirements
      o Full path to SQLT (must be previously installed)
      o The SQL_ID or HASH VALUE to be analyzed
      o The password for the SQLTXPLAIN user
    
    Ouput Details
      o A zip file of the analysis results
        
    

# SQLT utility
    
    if [ -d %%DA.host.sqlt_dir%%/run ]; then
      echo "Changing directory to %%DA.host.sqlt_dir%%/run..."
      cd %%DA.host.sqlt_dir%%/run
      
      if [ -r sqltxtract.sql ]; then
        exit | sqlplus / as sysdba @sqltxtract.sql %%sql_id%% %%sqlt_pass%%
      else
        echo "ERROR: Unable to find the sqlxtract.sql script in %%DA.host.sqlt_dir%%/run"
        echo "Please verify that you are providing the correct path to the SQLT scripts."
        exit 9
      fi
    else
      echo "ERROR: Directory specified for SQLT (%%DA.host.sqlt_dir%%) is not accessible."
      echo "Please verify that you are providing the correct path to the SQLT scripts."
      exit 8
    fi
    
    

    Command Summary
    
    This command uses the SQLTXTPLAIN (SQLT) XECUTE command.
      
    Input Requirements
      o Full path to SQLT (must be previously installed)
      o The name of the file placed into the sqlt/input directory, which
        contains a single SQL statement to diagnose
      o The password for the SQLTXPLAIN user
    
    Ouput Details
      o A zip file of the analysis results
        
    

# SQLT utility
    
    if [ -d %%DA.host.sqlt_dir%%/run ]; then
      echo "Changing directory to %%DA.host.sqlt_dir%%/run..."
      cd %%DA.host.sqlt_dir%%/run
      pwd
      
      if [ -r sqltxecute.sql ]; then
        if [ -r %%sql_loc%% ]; then
          exit | sqlplus / as sysdba @sqltxecute.sql %%sql_loc%% %%sqlt_pass%%
        else
          echo "ERROR: Invalid location provided for the SQL to diagnose."
          echo "Unable to read file %%sql_loc%%."
          exit 7
        fi
      else
        echo "ERROR: Unable to find the sqltxecute.sql script in %%DA.host.sqlt_dir%%/run"
        echo "Please verify that you are providing the correct path to the SQLT scripts."
        exit 9
      fi
    else
      echo "ERROR: Directory specified for SQLT (%%DA.host.sqlt_dir%%) is not accessible."
      echo "Please verify that you are providing the correct path to the SQLT scripts."
      exit 8
    fi
    
    

    Command Summary
    
    This command uses the SQLTXPLAIN (SQLT) XTRXEC command.
      
    Input Requirements
      o Full path to SQLT (must be previously installed)
      o The SQL_ID or HASH VALUE to be analyzed
      o The password for the SQLTXPLAIN user
    
    Ouput Details
      o A zip file of the analysis results
        
    

# SQLT utility
    
    if [ -d %%DA.host.sqlt_dir%%/run ]; then
      echo "Changing directory to %%DA.host.sqlt_dir%%/run..."
      cd %%DA.host.sqlt_dir%%/run
      
      if [ -r sqltxtrxec.sql ]; then
        exit | sqlplus / as sysdba @sqltxtrxec.sql %%sql_id%% %%sqlt_pass%%
      else
        echo "ERROR: Unable to find the sqltxtrxec.sql script in %%DA.host.sqlt_dir%%/run"
        echo "Please verify that you are providing the correct path to the SQLT scripts."
        exit 9
      fi
    else
      echo "ERROR: Directory specified for SQLT (%%DA.host.sqlt_dir%%) is not accessible."
      echo "Please verify that you are providing the correct path to the SQLT scripts."
      exit 8
    fi
    
    

    Command Summary
    
    This command uses the SQLTXPLAIN (SQLT) XTRSXBY command.
      
    Input Requirements
      o Full path to SQLT (must be previously installed)
      o The SQL_ID or HASH VALUE to be analyzed
      o DB link name to use
      o The password for the SQLTXPLAIN user
    
    Ouput Details
      o A zip file of the analysis results
        
    

# SQLT utility
    
    if [ -d %%DA.host.sqlt_dir%%/run ]; then
      echo "Changing directory to %%DA.host.sqlt_dir%%/run..."
      cd %%DA.host.sqlt_dir%%/run
      
      if [ -r sqltxtrsby.sql ]; then
        exit | sqlplus / as sysdba @sqltxtrsby.sql %%sql_id%% %%db_link%% %%sqlt_pass%%
      else
        echo "ERROR: Unable to find the sqltxtrsby.sql script in %%DA.host.sqlt_dir%%/run"
        echo "Please verify that you are providing the correct path to the SQLT scripts."
        exit 9
      fi
    else
      echo "ERROR: Directory specified for SQLT (%%DA.host.sqlt_dir%%) is not accessible."
      echo "Please verify that you are providing the correct path to the SQLT scripts."
      exit 8
    fi
    
    

    Command Summary
    
    This command uses the SQLTXPLAIN (SQLT) XPLAIN command.
      
    Input Requirements
      o Full path to SQLT (must be previously installed)
      o The name of the file placed into the sqlt/input directory, which
        contains a single SQL statement to diagnose
      o The password for the SQLTXPLAIN user
    
    Ouput Details
      o A zip file of the analysis results
        
    

# SQLT utility
    
    if [ -d %%DA.host.sqlt_dir%%/run ]; then
      echo "Changing directory to %%DA.host.sqlt_dir%%/run..."
      cd %%DA.host.sqlt_dir%%/run
      
      if [ -r sqltxplain.sql ]; then
        exit | sqlplus / as sysdba @sqltxplain.sql %%sql_loc%% %%sqlt_pass%%
      else
        echo "ERROR: Unable to find the sqltxplain.sql script in %%DA.host.sqlt_dir%%/run"
        echo "Please verify that you are providing the correct path to the SQLT scripts."
        exit 9
      fi
    else
      echo "ERROR: Directory specified for SQLT (%%DA.host.sqlt_dir%%) is not accessible."
      echo "Please verify that you are providing the correct path to the SQLT scripts."
      exit 8
    fi
    
    

    Command Summary
    
    This command will diagnose high CPU usage for the given OS process ID.
    The actual mechanism is OS dependent, and currently only valid on 
    HPUX, Solaris, and Windows operating systems.
      
    Input Requirements
      o OS process ID
        If you run this command on a non-available OS, you will still 
        be prompted and an error will display
    
    Ouput Details
      o Diagnosis of high CPU utilization
        
    

    echo "Sorry, there are no special commands available for your Operating System..."
    

    Command Summary
    
    This command starts OSWatcher Black Box.
	  Additional information available in note 301137.1
      
    Input Requirements
      o directory where OSWatcher Black Box is installed
	  o interval in seconds
	  o keep log duration in hours
    
    Ouput Details
      o 
      
    

# oswbb utility
   
    OSWBB_DIR=%%DA.host.DA_OSWBB_DIR%%
    SECS=%%INTERVAL_IN_SECONDS%%
    HRS=%%KEEP_LOG_DURATION_IN_HOURS%%
    export OSWBB_DIR SECS HRS
    
    if [ -d ${OSWBB_DIR} ]; then
      cd ${OSWBB_DIR}
           
      ## Confirm the executable is present and run it!
      if [ -r "startOSWbb.sh" ]; then
        echo "Running startOSWbb.sh with parameters ${SECS} ${HRS}..."
        
        ## Now start OSWbb in background
        ## To do so, we have to put our command in a temporary script...
        TMPFILE=`echo ${OSWBB_DIR}/darunoswbb.sh`
      
        ## Clean up any old copies...
        rm -f ${TMPFILE}
      
        ## Write our commands to the temp file
        echo "# Temp file to run startOSWbb.sh" > ${TMPFILE}
        echo "#!/bin/sh" >> ${TMPFILE}
        echo "cd ${OSWBB_DIR}" >> ${TMPFILE}
        echo "./startOSWbb.sh ${SECS} ${HRS}" >> ${TMPFILE}
        
        ## Run it, so it stays running after DA exits
        at now < ${TMPFILE} > /dev/null 2>&1
      else
        echo "ERROR: Unable to find the startOSWbb.sh script in ${OSWBB_DIR}."
        echo "Please recheck the directory and/or reinstall oswbb."
        exit 2
      fi
    
    else
      echo "ERROR: Unable to find ${OSWBB_DIR}."
      echo "Please re-check the directory path provided."
      exit 3
    fi
    
    
    
  

    Command Summary
    
    This command stops OSWatcher Black Box.
    Additional information available in note 301137.1
      
    Input Requirements
      o directory where OSWatcher Black Box is installed
    
    Ouput Details
      o 
      
    

# oswbb utility
    
    if [ -d %%DA.host.DA_OSWBB_DIR%% ]; then
      cd %%DA.host.DA_OSWBB_DIR%%
           
      ## Confirm the executable is present and run it!
      if [ -r "stopOSWbb.sh" ]; then
        echo "Running stopOSWbb.sh..."
        ./stopOSWbb.sh
      else
        echo "ERROR: Unable to find the stopOSWbb.sh script in %%DA.host.DA_OSWBB_DIR%%."
        echo "Please recheck the directory and/or reinstall oswbb."
        exit 2
      fi
    
    else
      echo "ERROR: Unable to find %%DA.host.DA_OSWBB_DIR%%."
      echo "Please re-check the directory path provided."
      exit 3
    fi
    
    
  

    Command Summary
    
    This command packages OSWatcher Black Box files.
    Additional information available in note 301137.1
      
    Input Requirements
      o directory where OSWatcher Black Box is installed
    
    Ouput Details
      o 
      
    

# oswbb utility
    
    if [ -d %%DA.host.DA_OSWBB_DIR%% ]; then
      cd %%DA.host.DA_OSWBB_DIR%%
           
      ## Confirm the executable is present and run it!
      if [ -r "tarupfiles.sh" ]; then
        echo "Running tarupfiles.sh..."
        ./tarupfiles.sh
      else
        echo "ERROR: Unable to find the tarupfiles.sh script in %%DA.host.DA_OSWBB_DIR%%."
        echo "Please recheck the directory and/or reinstall oswbb."
        exit 2
      fi
    
    else
      echo "ERROR: Unable to find %%DA.host.DA_OSWBB_DIR%%."
      echo "Please re-check the directory path provided."
      exit 3
    fi
    
    
    

    Command Summary
    
    This command runs OSWatcher and allows you to grep for a given date. 
      
    Input Requirements
      o date to search for in YY.MM.DD format
	    o local hostname (without the domain)
	    o ssh password for root
    
    Ouput Details
      o A zip file containing OSWatcher activity for the date specified
  
    

SSH %%PASS%% root@%%HOST%% export LOGDIR=%%DA.host.DA_OSWATCHER_DIR%%/archive
    export OSWFILE=osw_%%HOST%%_$$.zip
    export DA_OWNER=`ps -eaf|grep da.sh|grep -v root|head -1|awk '{print $1}' `
    if [ -d $LOGDIR ]; then
      cd $LOGDIR
    else
      echo "ERROR: Invalid directory specified for OSWatcher: %%DA.host.DA_OSWATCHER_DIR%%"
      exit 1
    fi
    export OUTDIR=/tmp/da_osw
    if [ ! -d ${OUTDIR} ]; then
      mkdir ${OUTDIR}
    fi
    find . -name '*%%GREPDATE%%*' -exec zip ${OUTDIR}/${OSWFILE} {} \; 
    chown -R ${DA_OWNER} ${OUTDIR}
    echo DA_FILE_COLLECTED=${OUTDIR}/${OSWFILE}
    

    Command Summary
    
    This command runs OSWatcher and allows you to grep for a given date
	and time range on that date. 
      
    Input Requirements
      o date to search for in YY.MM.DD format
	    o time to search for in HHMM 24 hour format
	    o local hostname (without the domain)
	    o ssh password for root
    
    Ouput Details
      o A zip file containing OSWatcher activity for the date and time
	    range specified
  
    

SSH %%PASS%% root@%%HOST%% export LOGDIR=%%DA.host.DA_OSWATCHER_DIR%%/archive
    export OSWFILE=osw_%%HOST%%_$$.zip
    export DA_OWNER=`ps -eaf|grep da.sh|grep -v root|head -1|awk '{print $1}' `
    
    if [ -d $LOGDIR ]; then
      cd $LOGDIR
    else
      echo "ERROR: Invalid directory specified for OSWatcher: %%DA.host.DA_OSWATCHER_DIR%%"
      exit 1
    fi
    export OUTDIR=/tmp/da_osw
    if [ ! -d ${OUTDIR} ]; then
      mkdir ${OUTDIR}
    fi
    find . -name '*%%GREPDATE%%.%%GREPTIME%%*' -exec zip ${OUTDIR}/${OSWFILE} {} \;
    chown -R ${DA_OWNER} ${OUTDIR}
    echo DA_FILE_COLLECTED=${OUTDIR}/${OSWFILE}
     

    Command Summary
    
    This command generates a stack trace from a core dump produced by any of 
    the Oracle products on Unix platforms. For more information, see note
    1812.1.
    
    Before you can use this command, you must download the stackx
    script from "STACKX Core / Stack Trace Extraction Toolnote (Note
    362791.1). You will need to specify the location of this script.
      
    Input Requirements
      o Full path to the stackx script from note 362791.1
	    o Full path to the core file
    
    Ouput Details
      o the stack trace for the given core file
  
    

# stackx script
    
    #echo Stackx loc is %%DA.host.stackx_loc%%
    #echo Core file is %%core_file%%
   
    if [ -r %%core_file%% ]; then
      if [ -d %%stackx_loc%% ]; then
        echo "Changing directory to the stackx location..."
        cd %%DA.host.stackx_loc%%
        ./stackx %%core_file%% > ${DA_OUTPUT}/stackx_$$.log
        
        echo "DA_FILE_COLLECTED=${DA_OUTPUT}/stackx_$$.log"
      else
        echo "ERROR: Cannot read specified stackx directory: %%DA.host.stackx_loc%%."
        echo "Please re-check the directory provided for the stackx script."
        exit 2
      fi
    else
      echo "ERROR: Cannot read specified core file: %%core_file%%."
      echo "Please re-check the path and filename provided for the core file."
      echo "Also, check permissions on the file to make sure it is accessible."
      exit 3
    fi
    
    
    

TODO

      echo Light impact on the system = 1
      echo Medium impact on the system = 2
      echo Heavy impact on the system = 3
    

    Command Summary
    
    This command runs the HANGFG utility to generate diagnostic tracefiles
    during a hang. For more information, see note 362094.1.
      
    Input Requirements
      o Full path to the HANGFG utility
	    o Level of detail - 1, 2 or 3 (3 being most detailed)
    
    Ouput Details
      o HANGFG creates hang trace files in the respective udump and bdump 
        directories. In addition the following files are also generated 
        in the directory where you are running hangfg.

        hangfiles.out  - This file contains a list of all files generated 
          during the hang. This is the list of files you need to send into 
          support for analysis.
        hangfg.log  -  This file contains a log of the hangfg program. This 
          file is useful for debugging any issues that may arise along with 
          logging all actions associated with the hangfg program.
        hfiles_<MMDDYYHHMI>.tar.Z  - This file contains a compressed 
          tarball of all hang traces collected by the hangfg program. It also 
          contains the file hangfiles.out.

    
    

# HANGFG utility
    
    if [ -d %%DA.host.hangfg_dir%% ]; then
      cd %%DA.host.hangfg_dir%%
           
      ## Confirm the executable is present and run it!
      if [ -r "hangfg.sh" ]; then
        echo "Running hangfg.sh with detail level of %%PREREQ_hangfg_detail%%..."
        ./hangfg.sh %%PREREQ_hangfg_detail%%
      else
        echo "ERROR: Unable to find the hangfg.sh script in %%DA.host.hangfg_dir%%."
        echo "Please recheck the directory and/or reinstall HANGFG."
        exit 2
      fi
    
    else
      echo "ERROR: Unable to find %%DA.host.hangfg_dir%%."
      echo "Please re-check the directory path provided."
      exit 3
    fi
    
    
    

    Command Summary
    
    This test module executes the "P400RAC_lnx" rule set health checks. 
      
    Input Requirements
      o None
    
    Ouput Details
      o Produces the documentation of the "P400RAC_lnx" rules set as a report
      o It removes all previous reports for the rule set
      o The exit code is 0 when tests are successful, 1 in case of failures, or 
        2 when it detects execution errors
  
    

custom -yT hcve:P400RAC_lnx

    Command Summary
    
    Runs NET module to collect network information.
      
    Input Requirements
      o None
    
    Ouput Details
      o Shows a summary of the collection process
      o Generates a collection of files in the "NET" subdirectory under 
        the Diagnostic Assistant output directory
        
    

run_module=NET

    Command Summary
    
    Runs ONET module to collect Oracle Network information.
      
    Input Requirements
      o None
    
    Ouput Details
      o Shows a summary of the collection process
      o Generates a collection of files in the "ONET" subdirectory under 
        the Diagnostic Assistant output directory
        
    

run_module=ONET

    Command Summary
    
    Runs BR module to collect database backup and recovery information.
      
    Input Requirements
      o None
    
    Ouput Details
      o Shows a summary of the collection process
      o Generates a collection of files in the "BR" subdirectory under 
        the Diagnostic Assistant output directory
        
      

run_module=BR

    Command Summary
    
    This test module executes the "A200DB10R1_aix" rule set health checks. 
      
    Input Requirements
      o None
    
    Ouput Details
      o Produces the documentation of the "A200DB10R1_aix" rules set as a report
      o Removes all previous reports for the rule set 
      o The exit code is 0 when tests are successful, 1 in case of failures, or 
        2 when it detects execution errors
  
    

custom -yT hcve:A200DB10R1_lin

    Command Summary
    
    This test module executes the "A200DB10R1_hp" rule set health checks. 
      
    Input Requirements
      o None
    
    Ouput Details
      o Produces the documentation of the "A200DB10R1_hp" rules set as a report
      o Removes all previous reports for the rule set
      o The exit code is 0 when tests are successful, 1 in case of failures, or 
        2 when it detects execution errors
  
    

custom -yT hcve:A200DB10R1_lin

    Command Summary
    
    This test module executes the "A200DB11R1_aix" rule set health checks. 
      
    Input Requirements
      o None
    
    Ouput Details
      o Produces the documentation of the "A200DB11R1_aix" rules set as a report
      o Removes all previous reports for the rule set 
      o The exit code is 0 when tests are successful, 1 in case of failures, or 
        2 when it detects execution errors
  
    

custom -yT hcve:A200DB11R1_lin

    Command Summary
    
    This test module executes the "A200DB11R1_hp" rule set health checks. 
      
    Input Requirements
      o None
    
    Ouput Details
      o Produces the documentation of the "A200DB11R1_hp" rules set as a report
      o Removes all previous reports for the rule set 
      o The exit code is 0 when tests are successful, 1 in case of failures, or 
        2 when it detects execution errors
  
    

custom -yT hcve:A200DB11R2_lnx

    Command Summary
    
    Runs Exadata_DatabaseHang profile to collect information to diagnose database
    hanging issues.
      
    Input Requirements
      o None
    
    Ouput Details
      o Shows a summary of the collection process
      o Generates a collection of files in the "Exadata_DatabaseHang" 
        subdirectory under the Diagnostic Assistant output directory
  
    

run_profile=Exadata_DatabaseHang

    Command Summary
    
    Runs Database_Assessment profile to collect information about the entire 
    database implementation. This collection may take over 15 minutes to complete.
      
    Input Requirements
      o None
    
    Ouput Details
      o Shows a summary of the collection process
      o Generates a collection of files in the "DB_Assessment" 
        subdirectory under the Diagnostic Assistant output directory
        
    

run_profile=DB_Assessment

    Command Summary
    
    This command returns the database configuration detail. 
      
    Input Requirements
      o None
    
    Ouput Details
      o Includes: Database name, Instance name, Start time, Time format, 
        Version , Timed statistics, Open Mode, RAC instance number
        
    

connect / as sysdba
    REM SQL extracted from OCM oracle_database.xml
    set pages 10000
    prompt Database Configuration

    SELECT d.database_name "DBName",
     i.instance_name "InstanceName",
     TO_CHAR(i.startup_time, f.time_format) "StartTime",
     f.time_format "TimeFormat",
     i.version "Version",
     p.value "TimedStatistics",
     r.open_mode "OpenMode",
     i.instance_number "RACInstNum"
    FROM (select * from (SELECT name, value database_name
        FROM v$parameter
       WHERE (name = 'db_unique_name'
           OR name = 'db_name')
         AND value IS NOT NULL
       ORDER BY name DESC)
       where rownum = 1 ) d,
     v$instance i,
     v$parameter p,
     v$database r,
     (SELECT 'RRRR-MON-DD HH:MI:SS' time_format
        FROM dual) f
       WHERE p.name = 'timed_statistics'; 
    

    Command Summary
    
    This command displays archive destination
      
    Input Requirements
      o None
    
    Ouput Details
      o Archive destination (LOG_MODE)
        
    

connect / as sysdba
    REM SQL extracted from OCM oracle_database.xml
    set pages 10000
    prompt Archive Destination

    select log_mode from v$database;
	prompt Archive Destination Details
	
	show parameter archive;
    

    Command Summary
    
    This command returns the total CPU count. 
      
    Input Requirements
      o None
    
    Ouput Details
      o CPU count
        
    

connect / as sysdba
    REM SQL extracted from OCM oracle_database.xml
    set pages 10000
    prompt CPU Count

    select value from v$parameter where name='cpu_count';
    

      Command Summary
    
      This command returns valid NLS rows. 
      
      Input Requirements
        o None
      
      Ouput Details
        o NLS value + True flag
        
      

connect / as sysdba
      REM SQL extracted from OCM oracle_database.xml
      set pages 10000
      prompt NLS
  
      select value, 'TRUE' from nls_database_parameters 
        where parameter in ('NLS_CHARACTERSET','NLS_NCHARACTERSET');
		
	  prompt Default Date Format Based on SysDate:
	  select sysdate from dual;
	  
      

      Command Summary
    
      This command returns the database block size. 
      
      Input Requirements
        o None
    
      Ouput Details
        o Database block size
        
    

connect / as sysdba
      REM SQL extracted from OCM oracle_database.xml
      set pages 10000
      prompt DB Block Size
  
      select value from v$parameter where name='db_block_size';
      

      Command Summary
    
      This command will display the dump destination locations.
      
      Input Requirements
        o None
    
      Ouput Details
        o Background destination location
        o User destination location
        o Core destination location
        
      

connect / as sysdba
      REM SQL extracted from OCM oracle_database.xml
      set pages 10000
      prompt Dump Destination Locations
  
      SELECT name, value FROM v$parameter 
        WHERE name LIKE '%_dump_dest';
      

      Command Summary
    
      Produces a list of DB Component Versions. 
      
      Input Requirements
        o None
    
      Ouput Details
        o Shows a list of DB Component Versions
        
      

connect / as sysdba
      
      set pages 10000
      prompt DB Component Versions
  
      select comp_id Component, version Version from dba_registry;
      

  Command Summary
        
    This script is intended to provide a user friendly output to diagnose
    the status of the database either before (or) after upgrade. The 
    script will create a file called db_upg_diag_<sid>_<timestamp>.log. 
 
    Current version: dbupgdiag.sql 1.2

    Complete details about this script are available in MOS article 
    5566101.1.
        
  Input Requirements
    o 
            
  Ouput Details
    o 
                

connect / as sysdba
rem 
rem Script to Collect DB Upgrade/Migrate Diagnostic Information
rem (dbupgdiag.sql) [NOTE ID: 556610.1]
rem 
alter session set nls_language='American';
col TODAY	NEW_VALUE	_DATE	
col VERSION NEW_VALUE _VERSION
set termout off
select to_char(SYSDATE,'fmMonth DD, YYYY') TODAY from DUAL;
select version from v$instance;
set termout on
set echo off
set feedback off
set head off
set verify off
column output new_value dbname
SELECT value || '_' output FROM v$parameter WHERE name = 'db_name';
set linesize 150
set pages 100
set trim on
set trims on
col Compatible for a35
col comp_id for a12
col comp_name for a40
col org_version for a11
col prv_version for a11
col owner for a12
col object_name for a40
col object_type for a40
col Wordsize for a25
col Metadata for a8
col 'Initial DB Creation Info' for a35
col 'Total Invalid JAVA objects' for a45
col 'Role' for a30
col 'User Existence' for a27
col "JAVAVM TESTING" for a15
Prompt
Prompt
set feedback off head off
select LPAD('*** Start of LogFile ***',50) from dual;
select LPAD('Oracle Database Upgrade Diagnostic Utility',44)||
       LPAD(TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'),26) from dual;
Prompt
Prompt ===============
Prompt Hostname
Prompt ===============
select host_name from v$instance;
Prompt
Prompt ===============
Prompt Database Name
Prompt ===============
select name from v$database;
Prompt
Prompt ===============
Prompt Database Uptime
Prompt ===============
SELECT to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup Time" 
FROM v$instance;
Prompt
Prompt =================
Prompt Database Wordsize
Prompt =================
SELECT distinct('This is a ' || (length(addr)*4) || '-bit database') "WordSize" 
FROM v$process;
Prompt
Prompt ================
Prompt Software Version
Prompt ================
SELECT * FROM v$version;
Prompt
Prompt =============
Prompt Compatibility
Prompt =============
SELECT 'Compatibility is set as '||value Compatible 
FROM v$parameter WHERE name ='compatible';
Prompt
Prompt ================
Prompt Archive Log Mode
Prompt ================
Prompt
archive log list
Prompt
Prompt ================
Prompt Auditing Check
Prompt ================
Prompt
set head on
show parameter audit
Prompt
Prompt ================
Prompt Cluster Check
Prompt ================
show parameter cluster_database
Prompt
DOC
################################################################

 If CLUSTER_DATABASE is set to TRUE, change it to FALSE before
 upgrading the database 

################################################################
#
Prompt
Prompt ===========================================
Prompt Tablespace and the owner of the aud$ table 
Prompt ===========================================
select owner,tablespace_name from dba_extents where segment_name='AUD$' group by owner,tablespace_name;
Prompt
Prompt ============================================================================
Prompt count of records in the sys.aud$ table where dbid is null- Standard Auditing
Prompt ============================================================================
Prompt
set head off
select count(*) as Records  from sys.aud$ where dbid is null;
Prompt
Prompt
Prompt ============================================================================================
Prompt count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed
Prompt ============================================================================================
set head off
select count(*) from system.aud$ where dbid is null;
Prompt
Prompt
Prompt =============================================================================
Prompt count of records in the sys.fga_log$ when dbid is null, Fine Grained Auditing
Prompt =============================================================================
set head off
select count(*) from sys.fga_log$ where dbid is null;
Prompt
Prompt
prompt
Prompt ==========================================
Prompt Oracle Label Security is installed or not 
Prompt ==========================================
set head off
SELECT case count(schema)
WHEN 0 THEN 'Oracle Label Security is NOT installed at database level'
ELSE 'Oracle Label Security is installed '
END  "Oracle Label Security Check"
FROM dba_registry
WHERE schema='LBACSYS';
Prompt
Prompt ================
Prompt Number of AQ Records in Message Queue Tables
Prompt ================
Prompt
SET SERVEROUTPUT ON SIZE 100000
declare
   V_COUNT NUMBER;
     cursor c1 is
         select owner,queue_table from dba_queue_tables where owner in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP','WMSYS');
 begin
    for c in c1
     loop
        execute immediate 'select count(1) from ' || c.owner || '.'  || c.queue_table into v_count;
        dbms_output.put_line(c.owner || ' - ' || c.queue_table  || ' - ' || v_count);
     end loop;
 END;
 /
Prompt
Prompt ================
Prompt Time Zone version 
Prompt ================
Prompt
SELECT version from v$timezone_file;
Prompt
Prompt ================
Prompt Local Listener
Prompt ================
Prompt
select substr(value,1,50) "Local Listener" from v$parameter where name='local_listener';
Prompt
Prompt ================
Prompt Default and Temporary Tablespaces By User
Prompt ================
Prompt
set head on
COLUMN USERNAME FORMAT A28
COLUMN TEMPORARY_TABLESPACE FORMAT A22
COLUMN DEFAULT_TABLESPACE FORMAT A22
SELECT username, temporary_tablespace,default_tablespace FROM DBA_USERS;
Prompt
Prompt
Prompt ================
Prompt Component Status
Prompt ================
Prompt
SET SERVEROUTPUT ON;
DECLARE

ORG_VERSION varchar2(12);
PRV_VERSION varchar2(12);
P_VERSION VARCHAR2(10);

BEGIN 

SELECT version INTO p_version 
FROM registry$ WHERE cid='CATPROC' ;

IF SUBSTR(p_version,1,5) = '9.2.0' THEN

DBMS_OUTPUT.PUT_LINE(RPAD('Comp ID', 8) ||RPAD('Component',35)|| 
   RPAD('Status',10) ||RPAD('Version', 15));

DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-') ||RPAD(' ',35,'-')|| 
   RPAD(' ',10,'-') ||RPAD(' ',15,'-'));

FOR x in (SELECT SUBSTR(dr.comp_id,1,8) comp_id,
 SUBSTR(dr.comp_name,1,35) comp_name, 
 dr.status Status,SUBSTR(dr.version,1,15) version
 FROM dba_registry dr,registry$ r
 WHERE dr.comp_id=r.cid and dr.comp_name=r.cname
 ORDER BY 1)

LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(SUBSTR(x.comp_id,1,8),8) || 
   RPAD(SUBSTR(x.comp_name,1,35),35)||
   RPAD(x.status,10) || RPAD(x.version, 15));
END LOOP;

ELSIF SUBSTR(p_version,1,5) != '9.2.0' THEN

DBMS_OUTPUT.PUT_LINE(RPAD('Comp ID', 8) ||RPAD('Component',35)||  
   RPAD('Status',10) ||RPAD('Version', 15)||
   RPAD('Org_Version',15)||RPAD('Prv_Version',15));

DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-') ||RPAD(' ',35,'-')|| 
   RPAD(' ',10,'-')||RPAD(' ',15,'-')||RPAD(' ',15,'-')||
   RPAD(' ',15,'-'));

FOR y in (SELECT SUBSTR(dr.comp_id,1,8) comp_id,
 SUBSTR(dr.comp_name,1,35) comp_name, dr.status Status, 
 SUBSTR(dr.version,1,11) version,org_version,prv_version
 FROM dba_registry dr,registry$ r
 WHERE dr.comp_id=r.cid and dr.comp_name=r.cname
 ORDER BY 1)

LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(substr(y.comp_id,1,8), 8) || 
    RPAD(substr(y.comp_name,1,35),35)||RPAD(y.status,10) ||
    RPAD(y.version, 15)||RPAD(y.org_version,15)||RPAD(y.prv_version,15));

END LOOP;

END IF;
END;
/
SET SERVEROUTPUT OFF
Prompt
Prompt
Prompt
Prompt ======================================================
Prompt List of Invalid Database Objects Owned by SYS / SYSTEM
Prompt ======================================================
Prompt
set head on
SELECT case count(object_name)
WHEN 0 THEN 'There are no Invalid Objects'
ELSE 'There are '||count(object_name)||' Invalid objects'
END "Number of Invalid Objects"
FROM dba_objects 
WHERE status='INVALID'
AND owner in ('SYS','SYSTEM');
Prompt
DOC 
################################################################

 If there are no Invalid objects below will result in zero rows.

################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type 
FROM dba_objects 
WHERE status='INVALID' 
AND owner in ('SYS','SYSTEM')
ORDER BY owner,object_type;
set feedback off
Prompt
Prompt ================================
Prompt List of Invalid Database Objects
Prompt ================================
Prompt
set head on
SELECT case count(object_name)
WHEN 0 THEN 'There are no Invalid Objects'
ELSE 'There are '||count(object_name)||' Invalid objects'
END "Number of Invalid Objects"
FROM dba_objects 
WHERE status='INVALID'
AND owner  in 
('SYSMAN','CTXSYS','ORDSYS','MDSYS','EXFSYS','WKSYS','WKPROXY','WK_TEST','OLAPSYS','OUTLIN','TSMSYS',
'FLOWS_FILES','SI_INFORMATION_SCHMEA','ORACLE_OCM','ORDPLUGINS','DBSNMP');
Prompt
DOC
################################################################

 If there are no Invalid objects below will result in zero rows.

################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type 
FROM dba_objects 
WHERE status='INVALID' 
AND owner in ('SYSMAN','CTXSYS','ORDSYS','MDSYS','EXFSYS','WKSYS','WKPROXY','WK_TEST','OLAPSYS','OUTLIN','TSMSYS',
'FLOWS_FILES','SI_INFORMATION_SCHMEA','ORACLE_OCM','ORDPLUGINS','DBSNMP')
ORDER BY owner,object_type;
set feedback off
Prompt
Prompt ======================================================
Prompt Count of Invalids by Schema
Prompt ======================================================
Prompt
select owner,object_type,count(*) from dba_objects where status='INVALID'
group by owner,object_type order by owner,object_type ;
Prompt ==============================================================
Prompt Identifying whether a database was created as 32-bit or 64-bit
Prompt ==============================================================
Prompt
DOC 
###########################################################################

 Result referencing the string 'B023' ==> Database was created as 32-bit
 Result referencing the string 'B047' ==> Database was created as 64-bit
 When String results in 'B023' and when upgrading database to 10.2.0.3.0 
 (64-bit) , For known issue refer below articles
  
 Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While 
               Upgrading Or Patching Databases To 10.2.0.3
 Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and 
              OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6

###########################################################################
#
Prompt
SELECT SUBSTR(metadata,109,4) "Metadata",
CASE SUBSTR(metadata,109,4)
WHEN 'B023' THEN 'Database was created as 32-bit'
WHEN 'B047' THEN 'Database was created as 64-bit'
ELSE 'Metadata not Matching'
END "Initial DB Creation Info"
FROM sys.kopm$;
Prompt
Prompt ===================================================
Prompt Number of Duplicate Objects Owned by SYS and SYSTEM
Prompt ===================================================
Prompt
Prompt Counting duplicate objects ....
Prompt
SELECT count(1) 
FROM dba_objects 
WHERE object_name||object_type in 
   (SELECT object_name||object_type  
    from dba_objects 
    where owner = 'SYS') 
and owner = 'SYSTEM'; 
Prompt
Prompt =========================================
Prompt Duplicate Objects Owned by SYS and SYSTEM
Prompt =========================================
Prompt
Prompt Querying duplicate objects ....
Prompt
SELECT object_name, object_type, subobject_name, object_id 
FROM dba_objects 
WHERE object_name||object_type in 
   (SELECT object_name||object_type  
    FROM dba_objects 
    WHERE owner = 'SYS') 
AND owner = 'SYSTEM'; 
Prompt
DOC

################################################################################

 If any objects found please follow below article.
 Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
 Read the Exceptions carefully before taking actions.

################################################################################
#
Prompt
Prompt ========================
Prompt Password protected roles
Prompt ========================
Prompt
DOC

################################################################################

 In version 11.2 password protected roles are no longer enabled by default so if 
 an application relies on such roles being enabled by default and no action is
 performed to allow the user to enter the password with the set role command, it 
 is recommended to remove the password from those roles (to allow for existing 
 privileges to remain available). For more information see:

 Note 745407.1 : What Roles Can Be Set as Default for a User?

################################################################################
#
Prompt
Prompt Querying for password protected roles ....
Prompt
break on "Password protected Role"
select r.ROLE "Password protected Role",
p.grantee "Assigned by default to user"
from dba_roles r, dba_role_privs p
where r.PASSWORD_REQUIRED = 'YES' and p.GRANTED_ROLE = r.role
and p.default_role = 'YES'
and p.grantee <> 'SYS' and r.role not in
(select role from dba_application_roles);

Prompt
Prompt ================
Prompt JVM Verification
Prompt ================
Prompt
SET SERVEROUTPUT ON
DECLARE

V_CT NUMBER;
P_VERSION VARCHAR2(10);

BEGIN

-- If so, get the version of the JAVAM component
EXECUTE IMMEDIATE 'SELECT version FROM registry$ WHERE cid=''JAVAVM'' 
		   AND status <> 99' INTO p_version;

SELECT count(*) INTO v_ct FROM dba_objects
WHERE object_type LIKE '%JAVA%' AND owner='SYS';

IF SUBSTR(p_version,1,5) = '8.1.7' THEN
	IF v_ct>=6787 THEN
	DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
	ELSE
	DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
	END IF;
ELSIF SUBSTR(p_version,1,5) = '9.0.1' THEN
	IF v_ct>=8585 THEN
	DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
	ELSE
	DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
	END IF;
ELSIF SUBSTR(p_version,1,5) = '9.2.0' THEN
	IF v_ct>=8585 THEN
	DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
	ELSE
	DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
	END IF;
ELSIF SUBSTR(p_version,1,6) = '10.1.0' THEN
	IF v_ct>=13866 THEN
	DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
	ELSE
	DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
	END IF;
ELSIF SUBSTR(p_version,1,6) = '10.2.0' THEN
	IF v_ct>=14113 THEN
	DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
	ELSE
	DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
	END IF;
END IF;

EXCEPTION WHEN NO_DATA_FOUND THEN
	DBMS_OUTPUT.PUT_LINE('JAVAVM - NOT Installed. Below results can be ignored');

END;
/
SET SERVEROUTPUT OFF
Prompt
Prompt ================================================
Prompt Checking Existence of Java-Based Users and Roles
Prompt ================================================
Prompt
DOC

################################################################################

 There should not be any Java Based users for database version 9.0.1 and above.
 If any users found, it is faulty JVM.

################################################################################
#

Prompt
SELECT CASE count(username)
WHEN 0 THEN 'No Java Based Users'
ELSE 'There are '||count(*)||' JAVA based users'
END "User Existence"
FROM dba_users WHERE username LIKE '%AURORA%' AND username LIKE '%OSE%';
Prompt
DOC

###############################################################

 Healthy JVM Should contain Six Roles. 
 If there are more or less than six role, JVM is inconsistent.

###############################################################
#

Prompt
SELECT CASE count(role)
WHEN 0 THEN 'No JAVA related Roles'
ELSE 'There are '||count(role)||' JAVA related roles'
END "Role"
FROM dba_roles 
WHERE role LIKE '%JAVA%';
Prompt
Prompt Roles
Prompt
SELECT role FROM dba_roles WHERE role LIKE '%JAVA%';
set head off
Prompt
Prompt =========================================
Prompt List of Invalid Java Objects owned by SYS
Prompt =========================================
SELECT CASE count(*) 
       WHEN 0 THEN 'There are no SYS owned invalid JAVA objects'
       ELSE 'There are '||count(*)||' SYS owned invalid JAVA objects'
       END "Total Invalid JAVA objects"
FROM dba_objects 
WHERE object_type LIKE '%JAVA%' 
AND status='INVALID' 
AND owner='SYS';
Prompt
DOC

#################################################################

 Check the status of the main JVM interface packages DBMS_JAVA 
 and INITJVMAUX and make sure it is VALID.

 If there are no Invalid objects below will result in zero rows.

#################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects 
WHERE object_type LIKE '%JAVA%' 
AND status='INVALID' 
AND owner='SYS';
set feedback off
Prompt
DOC

#################################################################

 If the JAVAVM component is not installed in the database (for 
 example, after creating the database with custom scripts), the 
 next query will report the following error:

   select dbms_java.longname('foo') "JAVAVM TESTING" from dual
   *
   ERROR at line 1:
   ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier

 If the JAVAVM component is installed, the query should succeed 
 with 'foo' as result.

#################################################################
#
Prompt
set heading on
select dbms_java.longname('foo') "JAVAVM TESTING" from dual;
set heading off
Prompt 

set feedback off head off
select LPAD('*** End of LogFile ***',50) from dual;
set feedback on head on
set heading off
set heading off
set feedback off
REM select 'Upload db_upg_diag_&&dbname&&timestamp&&suffix from "&log_path" directory' 
REM from dual;
set heading on
set feedback on
Prompt

   

    Command Summary
    
    Finds how many invalid objects
      
    Input Requirements
      o None
    
    Ouput Details
      o Number of invalid objects
      o Invalid object name, types and owner

    

connect / as sysdba
       set pages 10000
       prompt Invalid Objects Count
        
        select owner,count(*) from dba_objects 
        where status !='VALID' group by owner;
		
		prompt
		prompt Invalid Objects
		select owner,object_name,object_type 
        from dba_objects 
        where status != 'VALID'; 
    

      Command Summary
    
      TBD
      
      Input Requirements
        o None
    
      Ouput Details
        o Shows a list of DB Component Versions
        
      

connect / as sysdba
      
      set pages 10000
      show parameter shared  
      show parametr dispatcher       

      

      Command Summary
    
      TBD
      
      Input Requirements
        o None
    
      Ouput Details
        o Shows a list of DB Service Names
        
      

connect / as sysdba
      SELECT inst_id, TYPE, service_name, Count(*) 
      FROM gv$session 
      GROUP BY inst_id,TYPE,service_name; 

      

      Command Summary
    
      TBD
      
      Input Requirements
        o SERVICENAME - select option 4 if not known
    
      Ouput Details
        o Shows a list of DB Component Versions
        
      

connect / as sysdba
SET SERVEROUTPUT ON
DECLARE

V_SERVICE_ID VARCHAR2(100);

BEGIN
SELECT service_id INTO V_SERVICE_ID 
FROM dba_services
WHERE name = '%%SERVICENAME%%';

SELECT name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications  
FROM dba_services 
WHERE service_id = V_SERVICE_ID;

END;

      Command Summary
    
      TBD
      
      Input Requirements
        o None
    
      Ouput Details
        o Shows a list of DB Component Versions
        
      

connect / as sysdba
      SELECT NAME "NAME", SUBSTR(NETWORK,1,23) "PROTOCOL", OWNED,
      STATUS "STATUS", (BUSY/(BUSY + IDLE)) * 100 "%TIME BUSY"
      FROM V$DISPATCHER;       
      SELECT D.NAME, Q.QUEUED, Q.WAIT, Q.TOTALQ,
      DECODE(Q.TOTALQ,0,0,(Q.WAIT/Q.TOTALQ)/100) "AVG WAIT"
      FROM V$QUEUE Q, V$DISPATCHER D
      WHERE D.PADDR = Q.PADDR;
      

      Command Summary
    
      TBD
      
      Input Requirements
        o None
    
      Ouput Details
        o Shows a list of DB Component Versions
        
      

connect / as sysdba
      set pages 10000
      SELECT NAME "NAME", PADDR, REQUESTS,  (BUSY/(BUSY + IDLE)) * 100 "%TIME BUSY", STATUS  
      FROM V$SHARED_SERVER;        
      SELECT MAXIMUM_CONNECTIONS "MAX CONN", MAXIMUM_SESSIONS "MAX SESS",      
      SERVERS_STARTED "STARTED", SERVERS_TERMINATED "TERMINATED",      
      SERVERS_HIGHWATER "HIGHWATER" 
      FROM V$SHARED_SERVER_MONITOR;
      

    Command Summary
    
    This command gathers the error stack using the OS process ID as a parameter.
      
    Input Requirements
      o OS process ID for which to generate an error stack
    
    Ouput Details
      o Error stack for the given OS process
        
    

connect / as sysdba
    REM SQL extracted from Note 1364257.1
    set pages 10000
    prompt Gather Error Stack

    ALTER SESSION SET tracefile_identifier = 'STACK_10046';
    oradebug setospid %%os_pid%%
    oradebug unlimit

    oradebug event 10046 trace name context forever,level 12

    oradebug dump errorstack 3
    oradebug dump errorstack 3
    oradebug dump errorstack 3
    oradebug tracefile_name

    

    Command Summary
    
    This command gathers the error stack using the Oracle process ID as a parameter.
      
    Input Requirements
      o Oracle process ID for which to generate an error stack
    
    Ouput Details
      o Error stack for the given Oracle process
        
    

connect / as sysdba
    REM SQL extracted from Note 1364257.1
    set pages 10000
    prompt Gather Error Stack

    ALTER SESSION SET tracefile_identifier = 'STACK_10046';
    oradebug setorapid %%ora_pid%%
    oradebug unlimit

    oradebug event 10046 trace name context forever,level 12

    oradebug dump errorstack 3
    oradebug dump errorstack 3
    oradebug dump errorstack 3
    oradebug tracefile_name

    

    Command Summary
    
    This command will terminate the trace process started by either of the
    "Gather Error Stack" commands. It is necessary to run this, so that tracing
    does not continue indefinitely.
      
    Input Requirements
      o None
    
    Ouput Details
      o Confirmation that the tracing has stopped
        
    

connect / as sysdba
    REM SQL extracted from Note 376442.1
    set pages 10000
    prompt Stop trace

    alter system set events '10046 trace name context off';
	
    

    Command Summary
    
    This command will display the available snapshots for the current DB.
    Users should identify from this list the starting snapshot and ending
    snapshot numbers, and provide them as inputs to the Run AWR and ADDM
    command.
     
    Input Requirements
      o None
    
    Output Details
      o A report of available snapshots
  
    

connect / as sysdba
    
    -- Set the dbid and inst_num
    set heading on echo off feedback off verify off underline on timing off;

    column inst_num  heading "Inst Num"  new_value inst_num  format 99999;
    column inst_name heading "Instance"  new_value inst_name format a12;
    column db_name   heading "DB Name"   new_value db_name   format a12;
    column dbid      heading "DB Id"     new_value dbid      format 9999999999 just c;

    select d.dbid        dbid
     , d.name            db_name
     , i.instance_number inst_num
     , i.instance_name   inst_name
    from v$database d,
       v$instance i;
       
    --  Set up the binds for dbid and instance_number
    variable dbid       number;
    variable inst_num   number;
    begin
      :dbid      :=  &dbid;
      :inst_num  :=  &inst_num;
    end;
    /

    column instart_fmt noprint;
    column inst_name   format a12  heading 'Instance';
    column db_name     format a12  heading 'DB Name';
    column snap_id     format 99999990 heading 'Snap Id';
    column snapdat     format a18  heading 'Snap Started' just c;
    column lvl         format 99   heading 'Snap|Level';

    -- List available snapshots
    break on inst_name on db_name on host on instart_fmt skip 1;
    set heading on;
    set timing off veri off space 1 flush on pause off termout on numwidth 10;
    set echo off feedback off pagesize 60 linesize 80 newpage 1 recsep off;
    set trimspool on trimout on define "&" concat "." serveroutput on;
    set underline on;

    ttitle off;

    select to_char(s.startup_time,'dd Mon "at" HH24:mi:ss')  instart_fmt
         , di.instance_name                                  inst_name
         , di.db_name                                        db_name
         , s.snap_id                                         snap_id
         , to_char(s.end_interval_time,'dd Mon YYYY HH24:mi') snapdat
         , s.snap_level                                      lvl
      from dba_hist_snapshot s
         , dba_hist_database_instance di
     where s.dbid              = :dbid
       and di.dbid             = :dbid
       and s.instance_number   = :inst_num
       and di.instance_number  = :inst_num
       and di.dbid             = s.dbid
       and di.instance_number  = s.instance_number
       and di.startup_time     = s.startup_time
       and s.end_interval_time >= sysdate - 3
     order by db_name, instance_name, snap_id;

    
    

    Command Summary
    
    This command runs AWR and ADDM. You must know the starting and ending
    snapshot numbers that you wish to use.  If you do not, please run the
    "Display snapshots for AWR and ADDM" command first.
     
    Input Requirements
      o Starting snapshot number
      o Ending snapshot number
    
    Output Details
      o Workload Repository report
  
    

connect / as sysdba
    
    define num_days = 3;
    define begin_snap   = %%startsnap%%;
    define end_snap   = %%endsnap%%;
    
    set heading on echo off feedback off verify off underline on timing off;

    column inst_num  heading "Inst Num"  new_value inst_num  format 99999;
    column inst_name heading "Instance"  new_value inst_name format a12;
    column db_name   heading "DB Name"   new_value db_name   format a12;
    column dbid      heading "DB Id"     new_value dbid      format 9999999999 just c;

    select d.dbid            dbid
     , d.name            db_name
     , i.instance_number inst_num
     , i.instance_name   inst_name
    from v$database d,
       v$instance i;

    --  Set up the binds for dbid and instance_number
    variable dbid       number;
    variable inst_num   number;
    begin
      :dbid      :=  &dbid;
      :inst_num  :=  &inst_num;
    end;
    /

    @${ORACLE_HOME}/rdbms/admin/awrrpt.sql %%startsnap%% %%endsnap%%
    
    

    Command Summary
    
    This command uses the DBMS_SQLDIAG package to diagnose a wrong results
    issue.
      
    Input Requirements
      o The SQL_ID or the query causing the incorrect results. If you do not
        know the SQL_ID, see the "Top SQL Statements" menu for commands to
        help you find it. If you use a query, be sure to use two single
        quotes for each quote in the query.
    
    Ouput Details
      o Diagnosis of the reason for the incorrect results and possible 
        workarounds
        
    

connect / as sysdba
    REM SQL extracted from Note 466604.1

    ---
    --- Drop first in case a previous run left one around
    ---
    BEGIN
    dbms_sqldiag.drop_diagnosis_task ( task_name => 'WR_diagnostic_task' );

    EXCEPTION WHEN OTHERS THEN
    -- If we have an error, it's because it wasn't there to drop - so just
    -- ignore it!
    dbms_output.enable(100);
    END;
    /

    set echo on lines 132 pages 999 long 20000 serveroutput on;
    declare
    l_sql_diag_task_id varchar2(100);
    begin
    --
    -- Create diagnostic task
    --
    l_sql_diag_task_id := dbms_sqldiag.create_diagnosis_task (
    %%PREREQ_sql_type%% => '%%sql%%',
    problem_type => dbms_sqldiag.PROBLEM_TYPE_WRONG_RESULTS,
    task_name => 'WR_diagnostic_task' );
    --
    -- Setup parameters for the task to give verbose output
    --
    dbms_sqltune.set_tuning_task_parameter(
    l_sql_diag_task_id,
    '_SQLDIAG_FINDING_MODE',
    DBMS_SQLDIAG.SQLDIAG_FINDINGS_FILTER_PLANS);
    end;
    /
    --
    -- Execute the task
    --
    exec dbms_sqldiag.execute_diagnosis_task ( task_name => 'WR_diagnostic_task' );
    --
    -- Generate and output the report for the task
    --
    select dbms_sqldiag.report_diagnosis_task ('WR_diagnostic_task' )
    as recommendations FROM DUAL;

    --
    -- Cleanup by dropping the task
    --
    exec dbms_sqldiag.drop_diagnosis_task ( task_name => 'WR_diagnostic_task' );
    
    
    

    Command Summary
    
    This command runs the proftab.sql script to build the tables needed for DBMS_PROFILER.
	It also runs the profload.sql script to install the DBMS_PROFILER API.
      
    Input Requirements
      o None
    
    Ouput Details
      o Creation of the DBMS_PROFILER tables and API.
        
    

connect / as sysdba
    REM SQL extracted from Note 243755.1
	
	REM Create the tables
	@?/rdbms/admin/proftab.sql
	
	REM Create the API
	@?/rdbms/admin/profload.sql
    

TODO

connect / as sysdba
      set head off
      set feedback off
      SELECT SUBSTR(run_owner, 1, 25) || ' - ' ||
        TO_CHAR(run_date, 'DD-MON-YY HH24:MI') || ' - ' ||
        SUBSTR(run_comment, 1, 30) ||  ' = ' || runid
        "OWNER - DATE - COMMENT = RUNID"
      FROM plsql_profiler_runs
      ORDER BY runid;
	    
    

    Command Summary
    
    This command generates the DBMS_PROFILER report. *BEFORE* you run this command,
	you must have already performed the following:
	
	o Start PL/SQL Profiler in your application: EXEC DBMS_PROFILER.START_PROFILER('optional comment');
    o Execute your transaction to be profiled. Calls to PL/SQL Libraries are expected.
    o Stop PL/SQL Profiler: EXEC DBMS_PROFILER.STOP_PROFILER;
	
	Once you have completed those three tasks, this command will generate your report.
      
    Input Requirements
      o The runid that you wish to review a report for
    
    Ouput Details
      o DBMS_PROFILER report
        
    

connect / as sysdba
    REM SQL extracted from Note 243755.1
	  
    SPO profiler.log
    SET DEF ^ TERM OFF ECHO ON VER OFF SERVEROUT ON SIZE 1000000;
    REM
    REM $Header: 243755.1 profiler.sql 11.4.4.5 2012/05/10 csierra $
    REM
    REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
    REM
    REM AUTHOR
    REM   csierra
    REM
    REM SCRIPT
    REM   profiler.sql
    REM
    REM DESCRIPTION
    REM   Generates an HTML report out of an execution of the PL/SQL
    REM   Profiler DBMS_PROFILER. It focuses on those calls with the
    REM   highest elapsed time.
    REM
    REM PRE-REQUISITES
    REM   1. PL/SQL Profiler tables must exist:
    REM
    REM      PLSQL_PROFILER_RUNS
    REM      PLSQL_PROFILER_UNITS
    REM      PLSQL_PROFILER_DATA
    REM
    REM      If they do not exist or are not accesible to application
    REM      user, proceed to create them connected as your application
    REM      user:
    REM
    REM      SQL> @?/rdbms/admin/proftab.sql
    REM
    REM   2. Package DBMS_PROFILER must be installed. If you need to
    REM      install it connect as SYS and execute:
    REM
    REM      SQL> @?/rdbms/admin/profload.sql
    REM
    REM   3. Create a PL/SQL Profiler run. Connect as your application
    REM      user:
    REM
    REM      SQL> EXEC DBMS_PROFILER.START_PROFILER('optional comment');
    REM      SQL> <<execute here your transaction to be profiled>>
    REM      SQL> EXEC DBMS_PROFILER.STOP_PROFILER;
    REM
    REM      Refer to Oracle® Database PL/SQL Packages and Types
    REM      Reference for information in how to use DBMS_PROFILER.
    REM
    REM   4. Use this profiler.sql script. Connected as your application
    REM      user.
    REM
    REM PARAMETERS
    REM   1. PL/SQL Profiler run_id.
    REM
    REM EXECUTION
    REM   1. Start SQL*Plus connecting as application user that executed
    REM      PL/SQL Profiler.
    REM   2. Execute script profiler.sql passing run_id.
    REM
    REM EXAMPLE
    REM   SQL> START [path]profiler.sql 3
    REM
    REM NOTES
    REM   1. For possible errors see profiler.log.
    REM
    DEF top_consumers = '5';
    DEF lines_before_and_after = '5';
    
    /**************************************************************************************************/
    
    SET TERM ON ECHO OFF;
    
    COL runid FOR 99999;
    COL run_owner FOR A25;
    COL run_date FOR A15;
    COL run_comment FOR A30;
    SELECT runid,
           SUBSTR(run_owner, 1, 25) run_owner,
           TO_CHAR(run_date, 'DD-MON-YY HH24:MI') run_date,
           SUBSTR(run_comment, 1, 30) run_comment
      FROM plsql_profiler_runs
     ORDER BY
           runid;
    
    PRO
    PRO Parameter 1:
    PRO RUNID (required)
    PRO
    DEF runid = '%%PREREQ_sql_537%%';
    PRO
    
    PRO Value passed:
    PRO ~~~~~~~~~~~~
    PRO RUNID: "^^runid."
    PRO
    SET TERM OFF;
    
    VAR runid NUMBER;
    EXEC :runid := TO_NUMBER('^^runid.');
    
    SET ECHO ON TIMI ON;
    
    DEF script = 'profiler';
    DEF method = 'PROFILER';
    
    --
    -- begin common
    --
    
    DEF mos_doc = '243755.1';
    DEF doc_ver = '11.4.4.5';
    DEF doc_date = '2012/05/02';
    DEF doc_link = 'https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=';
    DEF bug_link = 'https://support.oracle.com/CSP/main/article?cmd=show&type=BUG&id=';
    
    /**************************************************************************************************/
    
    /* -------------------------
     *
     * assembly title
     *
     * ------------------------- */
    
    -- get database name (up to 10, stop before first '.', no special characters)
    COL database_name_short NEW_V database_name_short FOR A10;
    SELECT SUBSTR(SYS_CONTEXT('USERENV', 'DB_NAME'), 1, 10) database_name_short FROM DUAL;
    SELECT SUBSTR('^^database_name_short.', 1, INSTR('^^database_name_short..', '.') - 1) database_name_short FROM DUAL;
    SELECT TRANSLATE('^^database_name_short.',
    'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 ''`~!@#$%^*()-_=+[]{}\|;:",.<>/?'||CHR(0)||CHR(9)||CHR(10)||CHR(13)||CHR(38),
    'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789') database_name_short FROM DUAL;
    
    -- get host name (up to 30, stop before first '.', no special characters)
    COL host_name_short NEW_V host_name_short FOR A30;
    SELECT SUBSTR(SYS_CONTEXT('USERENV', 'SERVER_HOST'), 1, 30) host_name_short FROM DUAL;
    SELECT SUBSTR('^^host_name_short.', 1, INSTR('^^host_name_short..', '.') - 1) host_name_short FROM DUAL;
    SELECT TRANSLATE('^^host_name_short.',
    'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 ''`~!@#$%^*()-_=+[]{}\|;:",.<>/?'||CHR(0)||CHR(9)||CHR(10)||CHR(13)||CHR(38),
    'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789') host_name_short FROM DUAL;
    
    -- get rdbms version
    COL rdbms_version NEW_V rdbms_version FOR A17;
    SELECT version rdbms_version FROM v$instance;
    
    -- get platform
    COL platform NEW_V platform FOR A80;
    SELECT UPPER(TRIM(REPLACE(REPLACE(product, 'TNS for '), ':' ))) platform FROM product_component_version WHERE product LIKE 'TNS for%' AND ROWNUM = 1;
    
    -- YYYYMMDD_HH24MISS
    COL time_stamp NEW_V time_stamp FOR A15;
    SELECT TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') time_stamp FROM DUAL;
    
    -- YYYY-MM-DD/HH24:MI:SS
    COL time_stamp2 NEW_V time_stamp2 FOR A20;
    SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS') time_stamp2 FROM DUAL;
    
    -- get run info
    COL run_owner NEW_V run_owner;
    COL run_date NEW_V run_date FOR A20;
    COL run_comment NEW_V run_comment;
    COL run_total_time NEW_V run_total_time FOR A24;
    SELECT run_owner,
           TO_CHAR(run_date, 'YYYY-MM-DD/HH24:MI:SS') run_date,
           run_comment,
           TRIM(TO_CHAR(run_total_time/1e9, '99999999999990D999999990')) run_total_time
      FROM plsql_profiler_runs
     WHERE runid = :runid;
    
    /* -------------------------
     *
     * roll up library total time
     *
     * ------------------------- */
    
    BEGIN
      FOR i IN (SELECT runid, unit_number
                  FROM plsql_profiler_units
                 WHERE runid = :runid
                   AND NVL(total_time, 0) = 0)
      LOOP
        DBMS_PROFILER.ROLLUP_UNIT(i.runid, i.unit_number);
      END LOOP;
    END;
    /
    
    /* -------------------------
     *
     * transaction begins: will rollback at the end
     *
     * ------------------------- */
    
    SAVEPOINT profiler;
    DELETE plan_table;
    
    /* -------------------------
     *
     * line# adjustments to compensate for offset reported on bug 4044091
     *
     * ------------------------- */
    
    -- borrowed from old version of profile.sql
    DECLARE
      offset NUMBER;
      CURSOR c1_triggers IS
        SELECT unit_owner, unit_name, unit_type, unit_number
          FROM plsql_profiler_units
         WHERE runid = :runid
           AND unit_type = 'TRIGGER';
    BEGIN
      FOR c1 IN c1_triggers LOOP
        SELECT NVL(MIN(line) - 1, -1)
          INTO offset
          FROM all_source
         WHERE owner = c1.unit_owner
           AND name  = c1.unit_name
           AND type  = c1.unit_type
           AND (UPPER(text) LIKE '%BEGIN%' OR UPPER(text) LIKE '%DECLARE%');
    
        IF offset > 0 THEN
          UPDATE plsql_profiler_data
             SET line# = line# + offset
           WHERE runid = :runid
             AND unit_number = c1.unit_number;
        END IF;
      END LOOP;
    END;
    /
    
    /* -------------------------
     *
     * find top consumers
     *
     * ------------------------- */
    
    -- position = row_num
    -- parent_id = parent_line#
    -- id = line#
    -- partition_id = runid
    -- object_instance = unit_number
    
    -- top consumers
    INSERT INTO plan_table (partition_id, object_instance, id, parent_id, position)
    SELECT runid, unit_number, line#, line#, ROWNUM
      FROM (SELECT /*+ NO_MERGE */
                   runid,
                   unit_number,
                   line#
              FROM plsql_profiler_data
             WHERE runid = :runid
             ORDER BY
                   total_time DESC) v
     WHERE ROWNUM <= ^^top_consumers.;
    
    -- add some lines before and after top consumers
    BEGIN
      FOR i IN (SELECT * FROM plan_table)
      LOOP
        FOR j IN 1 .. ^^lines_before_and_after.
        LOOP
          INSERT INTO plan_table (partition_id, object_instance, id, parent_id, position)
          VALUES (i.partition_id, i.object_instance, i.parent_id - j, i.parent_id, i.position);
          INSERT INTO plan_table (partition_id, object_instance, id, parent_id, position)
          VALUES (i.partition_id, i.object_instance, i.parent_id + j, i.parent_id, i.position);
        END LOOP;
      END LOOP;
    END;
    /
    
    /**************************************************************************************************/
    
    /* -------------------------
     *
     * main report
     *
     * ------------------------- */
    
    -- setup to produce report
    SET ECHO OFF FEED OFF VER OFF SHOW OFF HEA OFF LIN 2000 NEWP NONE PAGES 0 LONG 200000 LONGC 2000 SQLC MIX TAB ON TRIMS ON TI OFF TIMI OFF ARRAY 100 NUMF "" SQLP SQL> SUF sql BLO . RECSEP OFF APPI OFF AUTOT OFF;
    
    /* -------------------------
     *
     * heading
     *
     * ------------------------- */
    SPO ^^script._^^database_name_short._^^host_name_short._^^rdbms_version._^^time_stamp..html;
    
    PRO <html>
    PRO <!-- $Header: ^^mos_doc. ^^script..html ^^doc_ver. ^^doc_date. csierra $ -->
    PRO <!-- Copyright (c) 2000-2012, Oracle Corporation. All rights reserved. -->
    PRO <!-- Author: csierra -->
    PRO
    PRO <head>
    PRO <title>^^script._^^database_name_short._^^host_name_short._^^rdbms_version._^^time_stamp..html</title>
    PRO
    
    PRO <style type="text/css">
    PRO body {font:10pt Arial,Helvetica,Verdana,Geneva,sans-serif; color:black; background:white;}
    PRO a {font-weight:bold; color:#663300;}
    PRO pre {font:8pt Monaco,"Courier New",Courier,monospace;} /* for code */
    PRO h1 {font-size:16pt; font-weight:bold; color:#336699;}
    PRO h2 {font-size:14pt; font-weight:bold; color:#336699;}
    PRO h3 {font-size:12pt; font-weight:bold; color:#336699;}
    PRO li {font-size:10pt; font-weight:bold; color:#336699; padding:0.1em 0 0 0;}
    PRO table {font-size:8pt; color:black; background:white;}
    PRO th {font-weight:bold; background:#cccc99; color:#336699; vertical-align:bottom; padding-left:3pt; padding-right:3pt; padding-top:1pt; padding-bottom:1pt;}
    PRO td {text-align:left; background:#fcfcf0; vertical-align:top; padding-left:3pt; padding-right:3pt; padding-top:1pt; padding-bottom:1pt;}
    PRO td.c {text-align:center;} /* center */
    PRO td.l {text-align:left;} /* left (default) */
    PRO td.r {text-align:right;} /* right */
    PRO td.rr {text-align:right; color:crimson; background:#fcfcf0;} /* right and red */
    PRO td.rrr {text-align:right; background:crimson;} /* right and super red  */
    PRO font.n {font-size:8pt; font-style:italic; color:#336699;} /* table footnote in blue */
    PRO font.f {font-size:8pt; color:#999999;} /* footnote in gray */
    PRO </style>
    PRO
    
    PRO </head>
    PRO <body>
    PRO <h1><a target="MOS" href="^^doc_link.^^mos_doc.">^^mos_doc.</a> ^^method.
    PRO ^^doc_ver. Report: ^^script._^^database_name_short._^^host_name_short._^^rdbms_version._^^time_stamp..html</h1>
    PRO
    
    PRO <pre>
    PRO RUNID     : ^^runid.
    PRO Owner     : ^^run_owner.
    PRO Date      : ^^run_date.
    PRO Comment   : ^^run_comment.
    PRO Total Time: ^^run_total_time. (seconds)
    PRO RDBMS     : ^^rdbms_version.
    PRO Platform  : ^^platform.
    PRO </pre>
    
    /* -------------------------
     *
     * Top Consumers
     *
     * ------------------------- */
    PRO <h2>Top ^^top_consumers. Lines as per Total Time</h2>
    PRO
    
    SELECT v2.line_text
      FROM (
    SELECT pt.position,
           1 line_type,
           0 line#,
           '<h3>#'||pt.position||' Top Consumer</h3>'||CHR(10)||CHR(10)||
           'Displays ^^lines_before_and_after. lines before and after top consumer.'||CHR(10)||CHR(10)||
           '<table>'||CHR(10)||CHR(10)||
           '<tr>'||CHR(10)||
           '<th>Lib<br>#</th>'||CHR(10)||
           '<th>Type</th>'||CHR(10)||
           '<th>Owner</th>'||CHR(10)||
           '<th>Name</th>'||CHR(10)||
           '<th>Line<br>#</th>'||CHR(10)||
           '<th>Times<br>Line<br>Exec</th>'||CHR(10)||
           '<th>Total<br>Time<br>(seconds)</th>'||CHR(10)||
           '<th>Min<br>Time<br>(seconds)</th>'||CHR(10)||
           '<th>Max<br>Time<br>(seconds)</th>'||CHR(10)||
           '<th>Line Text</th>'||CHR(10)||
           '</tr>'||CHR(10) line_text
      FROM plan_table pt
     WHERE pt.id = pt.parent_id
     UNION ALL
    SELECT v.* FROM (
    SELECT /*+ NO_MERGE */
           pt.position,
           2 line_type,
           pt.id line#,
           CHR(10)||'<tr>'||CHR(10)||
           '<td class="r">'||pu.unit_number||'</td>'||CHR(10)||
           '<td nowrap>'||pu.unit_type||'</td>'||CHR(10)||
           '<td>'||DECODE(pu.unit_owner, '<anonymous>', NULL, pu.unit_owner)||'</td>'||CHR(10)||
           '<td>'||DECODE(pu.unit_name, '<anonymous>', NULL, pu.unit_name)||'</td>'||CHR(10)||
           '<td class="r">'||pt.id||'</td>'||CHR(10)|| -- line#
           '<td class="r">'||pd.total_occur||'</td>'||CHR(10)||
           '<td class="'||DECODE(pt.id, pt.parent_id, 'rr', 'r')||'">'||TO_CHAR(pd.total_time/1e9, '99999999999990D999999990')||'</td>'||CHR(10)||
           '<td class="r">'||TO_CHAR(pd.min_time/1e9, '99999999999990D999999990')||'</td>'||CHR(10)||
           '<td class="r">'||TO_CHAR(pd.max_time/1e9, '99999999999990D999999990')||'</td>'||CHR(10)||
           '<td nowrap><pre>'||
           (SELECT ds.text
              FROM dba_source ds
             WHERE ds.owner = pu.unit_owner
               AND ds.name = pu.unit_name
               AND ds.type = pu.unit_type
               AND ds.line = pt.id)||
           '</pre></td>'||CHR(10)|| -- source line_text
           '</tr>'||CHR(10) line_text
      FROM plan_table pt,
           plsql_profiler_units pu,
           plsql_profiler_data pd
     WHERE pt.id > 0 -- line# > 0
       AND pu.runid = pt.partition_id
       AND pu.unit_number = pt.object_instance
       AND pd.runid(+) = pt.partition_id
       AND pd.unit_number(+) = pt.object_instance
       AND pd.line#(+) = pt.id
     ORDER BY
           pt.position,
           pt.id) v
     UNION ALL
    SELECT pt.position,
           4 line_type,
           0 line#,
           CHR(10)||'</table>'||CHR(10)||CHR(10) line_text
      FROM plan_table pt
     WHERE pt.id = pt.parent_id) v2
     ORDER BY
           v2.position,
           v2.line_type,
           v2.line#;
    
    /* -------------------------
     *
     * Top Profiled PL/SQL Libraries
     *
     * ------------------------- */
    PRO <h2>Top ^^top_consumers. Profiled PL/SQL Libraries</h2>
    PRO
    PRO <table>
    PRO
    PRO <tr>
    PRO <th>#</th>
    PRO <th>Type</th>
    PRO <th>Owner</th>
    PRO <th>Name</th>
    PRO <th>Timestamp</th>
    PRO <th>Total Time<br>(seconds)</th>
    PRO </tr>
    PRO
    
    SELECT v.line_text FROM (
    SELECT /*+ NO_MERGE */
           unit_timestamp,
           CHR(10)||'<tr>'||CHR(10)||
           '<td class="r">'||unit_number||'</td>'||CHR(10)||
           '<td>'||unit_type||'</td>'||CHR(10)||
           '<td>'||DECODE(unit_owner, '<anonymous>', NULL, unit_owner)||'</td>'||CHR(10)||
           '<td>'||DECODE(unit_name, '<anonymous>', NULL, unit_name)||'</td>'||CHR(10)||
           '<td nowrap>'||DECODE(TO_CHAR(unit_timestamp, 'j'), '0000000', NULL, TO_CHAR(unit_timestamp, 'YYYY-MM-DD/HH24:MI:SS'))||'</td>'||CHR(10)||
           '<td class="r">'||TO_CHAR(total_time/1e9, '99999999999990D999999990')||'</td>'||CHR(10)||
           '</tr>' line_text
      FROM plsql_profiler_units
     WHERE runid = :runid
     ORDER BY
           total_time DESC) v
     WHERE ROWNUM <= ^^top_consumers.;
    
    SELECT CHR(10)||'<tr>'||CHR(10)||
           '<td></td>'||CHR(10)||
           '<td>TOTAL</td>'||CHR(10)||
           '<td></td>'||CHR(10)||
           '<td></td>'||CHR(10)||
           '<td></td>'||CHR(10)||
           '<td class="r">'||TO_CHAR(SUM(v.total_time)/1e9, '99999999999990D999999990')||'</td>'||CHR(10)||
           '</tr>'
      FROM (
    SELECT /*+ NO_MERGE */
           total_time
      FROM plsql_profiler_units
     WHERE runid = :runid
     ORDER BY
           total_time DESC) v
     WHERE ROWNUM <= ^^top_consumers.;
    
    PRO
    PRO </table>
    PRO
    
    /* -------------------------
     *
     * All Profiled PL/SQL Libraries
     *
     * ------------------------- */
    PRO <h2>All Profiled PL/SQL Libraries</h2>
    PRO
    PRO <table>
    PRO
    PRO <tr>
    PRO <th>#</th>
    PRO <th>Type</th>
    PRO <th>Owner</th>
    PRO <th>Name</th>
    PRO <th>Timestamp</th>
    PRO <th>Total Time<br>(seconds)</th>
    PRO </tr>
    PRO
    
    SELECT CHR(10)||'<tr>'||CHR(10)||
           '<td class="r">'||unit_number||'</td>'||CHR(10)||
           '<td>'||unit_type||'</td>'||CHR(10)||
           '<td>'||DECODE(unit_owner, '<anonymous>', NULL, unit_owner)||'</td>'||CHR(10)||
           '<td>'||DECODE(unit_name, '<anonymous>', NULL, unit_name)||'</td>'||CHR(10)||
           '<td nowrap>'||DECODE(TO_CHAR(unit_timestamp, 'j'), '0000000', NULL, TO_CHAR(unit_timestamp, 'YYYY-MM-DD/HH24:MI:SS'))||'</td>'||CHR(10)||
           '<td class="r">'||TO_CHAR(total_time/1e9, '99999999999990D999999990')||'</td>'||CHR(10)||
           '</tr>'
      FROM plsql_profiler_units
     WHERE runid = :runid
     ORDER BY
           unit_number;
    
    SELECT CHR(10)||'<tr>'||CHR(10)||
           '<td></td>'||CHR(10)||
           '<td>TOTAL</td>'||CHR(10)||
           '<td></td>'||CHR(10)||
           '<td></td>'||CHR(10)||
           '<td></td>'||CHR(10)||
           '<td class="r">'||TO_CHAR(SUM(total_time)/1e9, '99999999999990D999999990')||'</td>'||CHR(10)||
           '</tr>'
      FROM plsql_profiler_units
     WHERE runid = :runid;
    
    PRO
    PRO </table>
    PRO
    
    
    /* -------------------------
     *
     * footer
     *
     * ------------------------- */
    PRO
    SELECT '<!-- '||TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS')||' -->' FROM dual;
    PRO <hr size="3">
    PRO <font class="f">^^mos_doc. ^^method. ^^doc_ver. ^^time_stamp2.</font>
    PRO </body>
    PRO </html>
    
    SPO OFF;
    
    /* -------------------------
     *
     * nothing is updated to the db
     *
     * ------------------------- */
    ROLLBACK TO profiler;
    
    SET TERM ON ECHO OFF FEED 6 VER ON SHOW OFF HEA ON LIN 80 NEWP 1 PAGES 14 LONG 80 LONGC 80 SQLC MIX TAB ON TRIMS OFF TI OFF TIMI OFF ARRAY 15 NUMF "" SQLP SQL> SUF sql BLO . RECSEP WR APPI OFF SERVEROUT OFF AUTOT OFF;
    PRO
    PRO ^^method. file has been created:
    PRO ^^script._^^database_name_short._^^host_name_short._^^rdbms_version._^^time_stamp..html.
    PRO
    CL COL;
    SET DEF ON;
    UNDEFINE 1 2 script mos_doc doc_ver doc_date doc_link bug_link runid;
	  
    
    

    Command Summary
    
    This command uses the DBMS_SQLDIAG package to create a testcase. 
    If you choose to provide sql_text, remember to use 2 single quotes for 
    each one normally in your query.
      
    Input Requirements
      o Desired path to output directory
      o The sql_id or sql_text to be included in the testcase
    
    Ouput Details
      o oratcb2_000E5EA00002main.xml -	Metadatafile of the testcase
      o oratcb2_000E5EA00002sql.xml -	Optimizer relevant information
      o oratcb2_000E5EA00002ol.xml - Object (table, index, etc) related information
      o oratcb2_000E5EA00002dpexp.sql -	EXPDP-script to export the testcase
      o oratcb2_000E5EA00002dpimp.sql - IMPDP-script to import the testcase
      o oratcb2_000E5EA00002ssimp.sql - Script to import system statistics
      o oratcb2_000E5EA00002xplf.sql - Script to execute a simple explain 
                                       plan of the offending SQL, using 
                                       DBMS_SQLDIAG.EXPLAIN which is incorrect 
                                       for 10.2.0.4 as DBMS_SQLDIAG.EXPLAIN... 
                                       is not implemented.
      o oratcb2_000E5EA00002xplo.sql - Script to execute a simple explain plan 
                                       of the offending SQL. Includes the 
                                       OUTLINE Optimizer info of the execution 
                                       plan on the 'source'
      o oratcb2_000E5EA00002xpls.sql - Script to execute a simple explain plan 
                                       of the offending SQL, using EXPLAIN PLAN
      o oratcb2_000E5EA00002xpl.txt - Errorlogfile
      o oratcb2_000E5EA00002dpexp.log -	logfile of the Datadump export
      o oratcb2_000E5EA00002dpexp.dmp - Datapump export dump file of the 
                                        underlying data objects including the 
                                        rows
        
    

connect / as sysdba
    REM SQL extracted from Note 727863.1

    set echo on lines 132 pages 999 long 99999 serveroutput on;

    begin
      execute immediate 'drop directory exp_tc';
    exception when others then
      null;
    end;
    /
    
    create directory exp_tc as '%%output_dir%%';
    
    declare
      tc_out clob;
    begin
      /* Added for DA only to strip trailing semicolon */
      dbms_sqldiag.export_sql_testcase(directory=>'EXP_TC',
                                    %%PREREQ_sql_type%% =>rtrim(rtrim('%%sql%%'), ';'),
                                    testcase => tc_out);
    end;
    /
    
    prompt Review the output located in %%output_dir%%...
    
    
     

    Command Summary
    
    This command runs SQL query to find Top SQL Statements stored 
    in Cache, order by elapsed time
    
    Input Requirements
      o Number of SQL statements to list
    
    Output Details
      o List to top SQL statements
  
    

connect / as sysdba
      SELECT * FROM
      (SELECT
          elapsed_time / 1000000 "Elasped_Time (in Seconds)",
          sql_id,
          child_number,
          disk_reads,
          executions,
          first_load_time,
          last_load_time
      FROM  v$sql
      ORDER BY elapsed_time DESC)
      WHERE ROWNUM <= %%NUMSQL%%;
    

    Command Summary
    
    This command runs SQL query to list Top SQL Statements ordered 
    by Physical Disk Reads
     
    Input Requirements
      o Number of SQL statements to list
    
    Output Details
      o Disk_Reads DiskReads, Executions, SQL_ID
  
    

connect / as sysdba
    SELECT Disk_Reads DiskReads, Executions, SQL_ID, SQL_Text
    FROM
    (
       SELECT Disk_Reads, Executions, SQL_ID, LTRIM(SQL_Text) SQL_Text, 
          Operation, Options, 
          Row_Number() OVER 
             (Partition By sql_text ORDER BY Disk_Reads * Executions DESC) 
             KeepHighSQL
       FROM
       (
           SELECT Avg(Disk_Reads) OVER (Partition By sql_text) Disk_Reads, 
              Max(Executions) OVER (Partition By sql_text) Executions, 
              t.SQL_ID, sql_text, p.operation,p.options
           FROM v$sql t, v$sql_plan p
           WHERE t.hash_value=p.hash_value AND p.operation='TABLE ACCESS' 
           AND p.options='FULL' AND p.object_owner NOT IN ('SYS','SYSTEM')
           AND t.Executions > 1
       ) 
       ORDER BY DISK_READS * EXECUTIONS DESC
    )
    WHERE KeepHighSQL = 1
    AND rownum <=%%NUMSQL%%;
  

    Command Summary
    
    This command runs Oracle SQL Monitoring
      o The SQL statement must:
        * be CURRENTLY running, or
        * have executed long enough to get monitored, but recently
          enough that the DB has not lost track of it
      o Licenses are needed for 11g and higher. SQL Monitoring is a feature 
        of the Oracle Database Tuning Pack.  In addition,
        Oracle Diagnostics Pack is a prerequisite product to Oracle 
        Tuning Pack. Therefore, to use SQL Monitoring, licenses for both
        Diagnostics and Tuning Pack are required.
     
    Input Requirements
      o SQL ID to to disgnose problem
    
    Output Details
      o SQL Monitor report
  
    

connect / as sysdba
    
    prompt NOTE: If no results are shown, then the SQL statement may not be available for monitoring.
    prompt The SQL must be currently executing or run recently enough to still be tracked by the DB.
    
    SELECT distinct inst_id,sid,status,sql_id,sql_plan_hash_value,sql_child_address,sql_exec_id
    FROM gv$sql_plan_monitor
    WHERE sql_id like '%%sql_id%%' and status='EXECUTING';
    

    Command Summary
    

connect / as sysdba
      select SQL_ID, substr(SQL_TEXT,1,50) Text
      from V$SQL
      where upper(SQL_TEXT) like upper('%%%string_match%%%');
    

    Command Summary
    

connect / as sysdba
      select SQL_ID, substr(SQL_TEXT,1,50) Text
      from V$SQL
      where last_active_time > '%%active_date%%';
    

    Command Summary
    
    This command uses the DBMS_SQLDIAG package to diagnose query performance
      
    Input Requirements
      o The SQL_ID or the query causing the performance problem. If you do not
        know the SQL_ID, see the "Top SQL Statements" menu for commands to
        help you find it. If you use a query, be sure to use two single
        quotes for each quote in the query.
    
    Ouput Details
      o Diagnosis of the reason for the performance problem and possible 
        workarounds
        
    

connect / as sysdba
    REM SQL extracted from Note 1386802.1

    set echo on lines 132 pages 999 long 99999 serveroutput on;
    
    BEGIN
    ---
    --- Drop first in case a previous run left one around
    ---
    dbms_sqldiag.drop_diagnosis_task ( task_name => 'PROBLEM_TYPE_PERFORMANCE_task' );

    EXCEPTION WHEN OTHERS THEN
      -- If we have an error, it's because it wasn't there to drop 
      -- so just ignore it!
      null;
    END;
    /
    
    DECLARE
    v_sql_diag_task_id varchar2(100);
    BEGIN
    
    --
    -- Create diagnostic task
    --
    v_sql_diag_task_id := DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK (
    %%PREREQ_sql_type%% =>'%%sql%%',
    problem_type => DBMS_SQLDIAG.PROBLEM_TYPE_PERFORMANCE,
    time_limit => 900,
    task_name => 'PROBLEM_TYPE_PERFORMANCE_task' );
    
    --
    -- Setup parameters for the task to give verbose output
    --
    DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(v_sql_diag_task_id,'_SQLDIAG_FINDING_MODE',DBMS_SQLDIAG.SQLDIAG_FINDINGS_FILTER_PLANS);

    END;
    /     

    --- Execute the diagnostic task.
    ---
    BEGIN
      DBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK (
        task_name => 'PROBLEM_TYPE_PERFORMANCE_task' );
    END;
    /   
    
    --
    -- Generate and output the report for the task
    --
    SELECT DBMS_SQLDIAG.REPORT_DIAGNOSIS_TASK ('PROBLEM_TYPE_PERFORMANCE_task' ) as recommendations FROM DUAL;
    
    --
    -- Cleanup by dropping the task
    --
    BEGIN
      DBMS_SQLDIAG.DROP_DIAGNOSIS_TASK (
       task_name => 'PROBLEM_TYPE_PERFORMANCE_task' );
    END;
    /
    
    
    

    Command Summary
    
    This command uses the DBMS_SQLDIAG package to diagnose a crash during execution.
      
    Input Requirements
      o The SQL_ID or the query causing the crash. If you do not
        know the SQL_ID, see the "Top SQL Statements" menu for commands to
        help you find it. If you use a query, be sure to use two single
        quotes for each quote in the query.
    
    Ouput Details
      o Diagnosis of the reason for the crash and possible workarounds
        
    

connect / as sysdba
    
    REM SQL extracted from Note 1360120.1
    
    set echo on lines 132 pages 999 long 99999 serveroutput on;
    
    BEGIN
    ---
    --- Drop first in case a previous run left one around
    ---
    dbms_sqldiag.drop_diagnosis_task ( task_name => 'ERROR_diagnostic_task' );

    EXCEPTION WHEN OTHERS THEN
    -- If we have an error, it's because it wasn't there to drop - so just
    -- ignore it!
    dbms_output.enable(100);
    END;
    /

    DECLARE
    v_sql_diag_task_id varchar2(100);
    BEGIN
    ---
    --- Create a diagnostic task. Use any name you want under TASK_NAME argument.
    ---
    v_sql_diag_task_id := DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK (
    %%PREREQ_sql_type%% => '%%sql%%',
    problem_type => DBMS_SQLDIAG.PROBLEM_TYPE_EXECUTION_ERROR,
    time_limit => 3600,
    task_name => 'ERROR_diagnostic_task' );
    --
    -- Setup parameters for the task to give verbose output
    --
    DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(v_sql_diag_task_id,'_SQLDIAG_FINDING_MODE',DBMS_SQLDIAG.SQLDIAG_FINDINGS_FILTER_PLANS);
    END;
    /
    
    --- Execute the diagnostic task.
    ---
    EXEC DBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK ( task_name => 'ERROR_diagnostic_task' );
   
    --
    -- Generate and output the report for the task
    --
    SELECT DBMS_SQLDIAG.REPORT_DIAGNOSIS_TASK ('ERROR_diagnostic_task' ) 
    as recommendations FROM DUAL;

    --
    -- Cleanup by dropping the task
    --
    EXEC DBMS_SQLDIAG.DROP_DIAGNOSIS_TASK ( task_name => 'ERROR_diagnostic_task' );
    
    
    

TODO

      echo sql_id
      echo sql_text
    

    Command Summary
    
    This command displays
      
    Input Requirements
      o None
      
    Ouput Details
      o the database version
        
    

connect / as sysdba
    
    REM SQL extracted from OCM xmlp files
    
    set echo on lines 132 pages 999 long 99999 serveroutput on;
    column DBVersion format a15
    column DBDomain format a30

    select i.version "DBVersion", p.value "DBDomain" 
    from v$instance i, v$parameter p
    where p.name='db_domain' ;
    
    

    Command Summary
    
    This command displays
      
    Input Requirements
      o None
      
    Ouput Details
      o the value of CPU Timing Enabled
        
    

connect / as sysdba
    
    REM SQL extracted from OCM xmlp files
    
    set echo on lines 132 pages 999 long 99999 serveroutput on;
    column CPUTimingEnabled format a30

    SELECT DECODE(COUNT(*), 0, 'FALSE', 'TRUE') "CPUTimingEnabled"
      FROM v$osstat
      WHERE osstat_id = 1;
    
    

    Command Summary
    
    This command displays RAC Option information
      
    Input Requirements
      o None
      
    Ouput Details
      o the value of RAC Options
        
    

connect / as sysdba
    
    REM SQL extracted from OCM xmlp files
    
    set echo on lines 132 pages 999 long 99999 serveroutput on;
    column RAC_IN_USE format a30
    column DATAGUARDSTATUS format a30

    select parallel "RAC_IN_USE" from v$instance;
    
    select case when (((select count(*) from v$archive_dest where
    target='STANDBY') > 0) OR
    (database_role like '%STANDBY')) then initcap(database_role) 
    else ' ' end DATAGUARDSTATUS
    from v$database ;
    
    

    Command Summary
    
    This command displays the value of server manageability
      
    Input Requirements
      o None
      
    Ouput Details
      o value of server manageability
        
    

connect / as sysdba
    
    REM SQL extracted from OCM xmlp files
    
    set echo on lines 132 pages 999 long 99999 serveroutput on;
    column SERVER_MANAGEABILITY format a30

    select value SERVER_MANAGEABILITY
    from v$parameter where name='statistics_level';
    
    

    Command Summary
    
    This command displays database configuration values
      
    Input Requirements
      o None
      
    Ouput Details
      o database configuration values
        
    

connect / as sysdba
    
    REM SQL extracted from OCM xmlp files
    
    set echo on lines 132 pages 999 long 99999 serveroutput on;
    column DBName format a30
    column InstanceName format a30
    column StartTime format a25
    column TimeFormat format a25
    column TimedStatistics format a15

    SELECT d.database_name "DBName",
       i.instance_name "InstanceName",
       TO_CHAR(i.startup_time, f.time_format) "StartTime",
       f.time_format "TimeFormat",
       i.version "Version",
       p.value "TimedStatistics",
       r.open_mode "OpenMode"
  FROM (SELECT name, value database_name
          FROM v$parameter
         WHERE (name = 'db_unique_name'
             OR name = 'db_name')
           AND value IS NOT NULL
         ORDER BY name DESC) d,
       v$instance i,
       v$parameter p,
       v$database r,
       (SELECT 'DD-MON-YYYY HH:MI:SS' time_format
          FROM dual) f
 WHERE p.name = 'timed_statistics'
   AND ROWNUM = 1;

    
    

    Command Summary
    
    This command displays tablespace space used as a percentage
      
    Input Requirements
      o None
      
    Ouput Details
      o tablespace space used as a percentage
        
    

connect / as sysdba
    
    REM SQL extracted from OCM xmlp files
    
    set echo on lines 132 pages 999 long 99999 serveroutput on;
    column tablespace_name format a30

    SELECT tablespace_name, used_percent FROM sys.dba_tablespace_usage_metrics;
    
    

    Command Summary
    
    This command displays tablespace free space in MB.
      
    Input Requirements
      o None
      
    Ouput Details
      o tablespace free space in MB
        
    

connect / as sysdba
    
    REM SQL extracted from OCM xmlp files
    
    set echo on lines 132 pages 999 long 99999 serveroutput on;
    column tablespace_name format a30

    SELECT m.tablespace_name,
       m.used_percent,  
       (m.tablespace_size - m.used_space)*t.block_size/1024/1024 mb_free
    FROM  dba_tablespace_usage_metrics m, dba_tablespaces t
    WHERE t.tablespace_name = m.tablespace_name ;
    
    

    Command Summary
    
    This command displays tablespace allocation
      
    Input Requirements
      o None
      
    Ouput Details
      o tablespace allocation details
        
    

connect / as sysdba
    
    REM SQL extracted from OCM xmlp files
    
    set echo on lines 132 pages 999 long 99999 serveroutput on;
    column TABLESPACE_NAME format a30

    SELECT
      d.tablespace_name TABLESPACE_NAME,
      NVL(a.bytes / 1024 / 1024, 0) allocated_space,
      NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 used_space
    FROM
      sys.dba_tablespaces d,  
      (select tablespace_name, sum(bytes) bytes, count(file_id) count  
       from dba_data_files group by tablespace_name) a,
      (select tablespace_name, sum(bytes) bytes  
       from dba_free_space group by tablespace_name) f
    WHERE
      d.tablespace_name = a.tablespace_name(+)
      AND d.tablespace_name = f.tablespace_name(+)
      AND NOT (d.extent_management like 'LOCAL'
             AND d.contents like 'TEMPORARY')
    UNION ALL
    SELECT
      d.tablespace_name,
      NVL(a.bytes / 1024 / 1024, 0) allocated_space,
      NVL(t.bytes, 0)/1024/1024 used_space
    FROM
      sys.dba_tablespaces d,  
     (select tablespace_name, sum(bytes) bytes, count(file_id) count
       from dba_temp_files group by tablespace_name) a,
    (select  ss.tablespace_name , sum((ss.used_blocks*ts.blocksize))  bytes
        from gv$sort_segment ss, sys.ts$ ts where ss.tablespace_name = ts.name
                 group by ss.tablespace_name) t
    WHERE
      d.tablespace_name = a.tablespace_name(+)
      AND d.tablespace_name = t.tablespace_name(+)
      AND d.extent_management like 'LOCAL'
      AND d.contents like 'TEMPORARY'
    ORDER BY 1;
    
    

    Command Summary
    
    This command displays recovery area free space as a percentage
      
    Input Requirements
      o None
      
    Ouput Details
      o recovery area free space as a percentage
        
    

connect / as sysdba
    
    REM SQL extracted from OCM xmlp files
    
    set echo on lines 132 pages 999 long 99999 serveroutput on;
    column recovery_area format a30

    SELECT 'RECOVERY AREA' recovery_area,
       CASE WHEN space_limit > 0 AND space_limit >= space_used AND space_used >= space_reclaimable
            THEN 100 * (space_limit - (space_used - space_reclaimable))/space_limit
            ELSE NULL END free_space
    FROM v$recovery_file_dest
    WHERE name IS NOT NULL
      AND rownum = 1;
    
    

    Command Summary
    
    This command displays the SYSTEM tablespace number
      
    Input Requirements
      o None
      
    Ouput Details
      o SYSTEM tablespace number
        
    

connect / as sysdba
    
    REM SQL extracted from OCM xmlp files
    
    set echo on lines 132 pages 999 long 99999 serveroutput on;

    SELECT ts# "SystemTs#" FROM sys.ts$ WHERE name = 'SYSTEM' ;
    
    

    Command Summary
    
    This command displays the SYSAUX tablespace number
      
    Input Requirements
      o None
      
    Ouput Details
      o SYSAUX tablespace number
        
    

connect / as sysdba
    
    REM SQL extracted from OCM xmlp files
    
    set echo on lines 132 pages 999 long 99999 serveroutput on;

    SELECT nvl(a.ts#, -1) "SysauxTs#"
    FROM
    (select ts#,name from sys.ts$ where name = 'SYSAUX') a,
    (select 'SYSAUX' name from dual) b
    WHERE
    a.name(+) = b.name;
    
    

    Command Summary
    
    This command displays Redo Generation Rate (KB/second)
      
    Input Requirements
      o None
      
    Ouput Details
      o Redo Generation Rate (KB/second)
        
    

connect / as sysdba
    
    REM SQL extracted from OCM xmlp files
    
    set echo on lines 132 pages 999 long 99999 serveroutput on;

    select sum(s.value*512/1000) REDO from  gv$sysstat s  
    where s.name='redo blocks written';
    
    

    Command Summary
    
    This command displays Fast-Start Failover
      
    Input Requirements
      o None
      
    Ouput Details
      o Fast-Start Failover
        
    

connect / as sysdba
    
    REM SQL extracted from OCM xmlp files
    
    set echo on lines 132 pages 999 long 99999 serveroutput on;

    select * from
      ( select fs_failover_current_target target,  
        STANDBY_BECAME_PRIMARY_SCN
        scn from v$database),
      ( select max(timestamp) time from v$dataguard_status where
      message='Switchover: Complete - Database mounted as primary');
    
    

    Command Summary
    
    This command displays Deferred Transaction Count 
      
    Input Requirements
      o None
      
    Ouput Details
      o Deferred Transaction Count 
        
    

connect / as sysdba
    
    REM SQL extracted from OCM xmlp files
    
    set echo on lines 132 pages 999 long 99999 serveroutput on;
    column CPUTimingEnabled format a30

    SELECT trans_count
    FROM (SELECT 'def_trans_err' name, COUNT(*) trans_count
          FROM sys.deferror
         UNION ALL
        SELECT 'def_trans_noerr' name, COUNT(*) trans_count
          FROM sys.deftran)
    ORDER BY name;
    
    

    Command Summary
    
    This command displays feature usage
      
    Input Requirements
      o None
      
    Ouput Details
      o feature usage
        
    

connect / as sysdba
    
    REM SQL extracted from OCM xmlp files
    
    set echo on lines 132 pages 999 long 99999 serveroutput on;
    column name format a20 word
    column description format a30 word

    SELECT
            dbid               ,
            name               ,
            currently_used     ,
            detected_usages    ,
            first_usage_date   ,
            last_usage_date    ,
            version            ,
            last_sample_date   ,
            last_sample_period ,
            total_samples      ,
            aux_count          ,
            description
    FROM
            dba_feature_usage_statistics ;
    
    

    Command Summary
    
    This command displays Invalid Objects
      
    Input Requirements
      o None
      
    Ouput Details
      o Invalid Objects by owner
        
    

connect / as sysdba
    
    REM SQL extracted from OCM xmlp files
    
    set echo on lines 132 pages 999 long 99999 serveroutput on;
    column owner format a30

    SELECT /*+ all_rows */ u.name owner, count(1) invalid_count
    FROM sys.obj$ o, sys.user$ u
    WHERE u.user# = o.owner#
     AND o.status NOT IN (0,1)
     AND ( ((u.name='SYS' or u.name='PUBLIC')
             and (o.name NOT LIKE 'DBA_HIST_%' and o.name
      !='DBMS_SWRF_REPORT_INTERNAL'))
          or (u.name !='SYS' and u.name != 'PUBLIC') )
    GROUP BY u.name ;
    
    

    Command Summary
    
    This command displays database job status
      
    Input Requirements
      o None
      
    Ouput Details
      o database job status
        
    

connect / as sysdba
    
    REM SQL extracted from OCM xmlp files
    
    set echo on lines 132 pages 999 long 99999 serveroutput on;

    SELECT SUM(broken), SUM(failed)
    FROM (SELECT DECODE(broken, 'N', 0, 1) broken,
               DECODE(NVL(failures,0), 0, 0, 1) failed
            FROM dba_jobs ) ;