1. Data Guard Status

    Standby - Difference between SCN Timestamp and SYSTIMESTAMP of DB instance

    SELECT TO_CHAR(SCN_TO_TIMESTAMP(CURRENT_SCN), 'DD-MON-YYYY HH24:MI SSxFF') as LAST_COMMIT_TIME, 
    TO_CHAR(SYSTIMESTAMP, 'DD-MON-YYYY HH24:MI SSxFF') as CURRENT_DB_TIME 
    FROM V$DATABASE;
    

    Standby - Seconds Between SCN Timestamp and SYSTIMESTAMP of DB instance

    SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI'), 
           INST_ID, 
           ( CAST( SYSTIMESTAMP AS DATE ) - CAST( SCN_TO_TIMESTAMP(CURRENT_SCN) AS DATE ) ) * 86400 AS SECONDS_LAG
    FROM GV$DATABASE; 
  2. Presentations

    Visualization

    10/2012 @ East Coast Oracle Users Group - Explore New Data Dimensions with Application Express and Free/Open Source Graphing Libraries - PDF

  3. Gnu Screen Usage

    Gnu Screen is a helpful Linux utility which functions as a virtual terminal manager. With it you can manage multiple terminal sessions and detach/reconnect to sessions as needed in your workflow. The virtual terminals will continue to run and insulate you from unexpected disconnections and logouts.
  4. 12c Containers

    Working with PDBs

    Set your ORACLE_SID and log into the CDB instance using normal oraenv and sqlplus "/ as sysdba" method.
  5. RAC on NFS / ASM on NFS

    Why?

    I need to create a RAC cluster at a client site where they only have a NFS file server device.

    To ASM or not ASM?

    Options

    direct NFS

    Option 2 : ASM on NFS

    You can make use of ASM with NFS by creating shared files on NFS that will act as Raw devices to ASM.
  6. RAC

    Server Status/Startup/Shutdown

    # Status
    srvctl status database -d <dbname>
    srvctl config database -d <dbname>
    
    
    # Shutdown
    srvctl stop database -d <dbname>
    srvctl stop database -d <dbname> -i <instance> -o immediate
    
    # Startup
    srvctl start database -d <dbname>
    srvctl start database -d <dbname> -i <instance> -o immediate
    

    CRS Commands

    crsctl status resource
    
    crsctl status type
    
    crsctl status serverpool
    
    crsctl status server
    
    
    

    Recreate Srvctl Entry

  7. Scheduling a Cron Job

    Note that if you are wanting to schedule a script to run with Cron and execute SQLPLUS, you may have to make the script aware of the user environment variables like this:
    #!/bin/sh
    if [ -f ~/.bashrc ]; then
    . ~/.bashrc
    fi
    sqlplus -S perfstat/perfstat @query.sql > outfile.txt
    
    To run a script with Cron automatically: 1) Log in to the OS as the user you want to execute the job. 2) Run "crontab -e" to edit the crontab configuration file 3) Add a line to the configuration file to specify the info about the job. The crontab file is organized like this:
  8. Test Driving the h2 Embedded Java Database

    I'm starting work on some DB tuning utilities written in Java. I am running the utilities on my local machine against remote servers and I needed a way to hold some data locally. The volume of data I plan on storing is more than I would feel comfortable keeping in local properties or XML files and I didn't want to deal with setting up an Oracle XE instance just to service my lightweight utilities so I checked into a bunch of embedded databases. Embedded databases are databases that I can wrap into my application.

  9. Creating a RAC Cluster on Virtualbox

    Create new Virtualbox VM

    • Adaptor 1 - Nat, ipv4 DHCP, ipv6 disabled, Connected checkbox
    • Set Optical drive to Linux 6.7 ISO
    • Create 30gb IDE drive, sparsely populated

    Start VM for Initial Install

    The ISO image will be used for boot up and installation will begin.
    • Skip Media check
    • Select language, click next.
    • Select keyboard, click next.
    • Select Basic Storage Devices, click next.
  10. ASH to the rescue... for now...

    After 4 unexplained crashes and a stalled Sev 1 SR I was pulling my hair out. Particularly vexing was the fact that a user was running a distributed query which was crashing a remote database. I isolated the 4 crash times for the remote database and I wanted to see what users and SQLs were running on the local database up until the crash time. So for a crash happening around 11:16 I would use the following query.
    Tags: 
  11. Oracle Enterprise Manager / Cloud Control

    Environment Overview

    To show info about currently running OMS including URLS, ports, and paths:
    cd $OMS_HOME/bin
    ./emctl status oms -details
    

    Reset Weblogic Admin Password

    12c , 13c Cloud Control: Steps for Modifying the Password for Weblogic and Nodemanager User Accounts in the Enterprise Manager Installation (Doc ID 1450798.1)

    Reset Weblogic Admin Server Password

  12. Metric Extensions

    Why use a Metric Extension?

    The Metric Extension is the evolution of the User Defined Metric. Regular UDMs can be used to gather and store data from targets. Repository-side Metric Extensions let you define new metrics/alerts based on information that is in the EM SYSMAN repository schema.

    Creating a Metric Extension

    In this example we will create a Metric Extension for Data Guard Lag time.
  13. Querying Sysman Schema

    Target Type Summaries

    -- Target Types by Count Descending
    select target_type, count(*) from mgmt_targets group by target_type order by 2 desc;
    
    -- Target Types by Name
    select target_type, count(*) from mgmt_targets group by target_type order by 1;
    

    References

    Sagar Patil article on Querying SYSMAN schema
  14. CYA Auditing

    Enable Auditing

    -- Run as SYSDBA
    
    -- Enable Auditing
    ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE;
    SHUTDOWN IMMEDIATE
    STARTUP
    

    Set Auditing Configuration

    -- Reset auditing
    -- Statements
    NOAUDIT ALL; 
    -- Privileges
    NOAUDIT ALL PRIVILEGES; 
     -- Objects
    NOAUDIT ALL ON DEFAULT;
    -- Network
    NOAUDIT NETWORK;
    
    
    
    -- Turn on session auditing LOGON / LOGOFF
    AUDIT SESSION;
    
    
    -- User changes
    AUDIT USER;
    
    
    -- Audit role creation/grants
    AUDIT ROLE;
    AUDIT GRANT ANY ROLE BY ACCESS;
    AUDIT ALTER, GRANT ON DEFAULT;
    AUDIT SYSTEM GRANT;
    
    --
  15. Quickie bar graph widget for use in Apex Reports...

    I'm trying not to scare my users off with endless rows and columns of numbers. I have an app that analyzes memory usage and swap space usage of some of our servers. Instead of throwing all kinds of computer sciency '65535' and '32767' numbers at my user I decided to create a small function to return a bar graph widget. Here is an example of the graph in use: Here is a function that will return the HTML for the widget:
    CREATE OR REPLACE FUNCTION GetGraph (p_value IN NUMBER, p_total IN NUMBER) 
    
  16. Oracle IN Condition and Multi Column Subqueries

    I keep coming across a construct in some legacy SQL that has been causing all kinds of performance issues for us. I guess you could call it using the IN condition with multi-column subqueries. I located the syntax for the IN condition here but it doesn't really get into much detail about using this construct. Here is an example of the subquery (it returns the lowest salary and department_id for each department):
    Tags: 
  17. DB Links across the Enterprise, a Connectogram with d3.js (Work In Progress)

    The Idea

    I have been seeing a lot of chord and connectogram types of visualisations and an idea immediately popped into my mind. I have a homegrown system that collects information from 500+ databases every evening. We constantly struggle to keep up with Database Link requests between databases.
  18. Long Operations Real Time Viewer - Perl

    Purpose

    Monitor Long Operations inside an Oracle Database using Perl/DBI.

    This can be useful for monitoring Table Scans, Index Rebuilds, and Exports.

    Source Code

    https://github.com/dmann99/oscripts/blob/master/longops-perl/longops.pl

    Shell Script Example

    https://github.com/dmann99/oscripts/blob/master/longops-perl/longops.sh
  19. RecreateDBLinks.sql

    Purpose:

    RecreateDBLinks.sql is a script that will extract DB Links DDL (non SYS or SYSTEM links) and output a script that can be used to recreate those links at a later time or on a different database. It handles dealing with encrypted passwords and with logging on as the owninguser to create private database links.

    It is presumed that non DBAs should not have CREATE DATABASE link. You can change this behavior by commenting out the REVOKE lines near the end of the script.

  20. OraPad

    OraPad is an Oracle utility program I am writing as a Tuning Scratchpad. I need a place to coordinate my activities when I am in the thick of a tuning issue. Right now it seems like I am using multiple tools and I would like a central place to organize my tuning information and thoughts. Bonus if we are able to wrap some functionality to cut out extra tools required to do the tuning investigation.
  21. Making ASMCMD do what you want it to do... like copy files recursively...

    To troubleshoot a problem I needed to copy a database's worth of files out of an ASM diskgroup and onto the filesystem. I searched around and checked help for ASMCMD assuming there was a recursive option. I found references to one in some places on the interwebs, even official Oracle references to it like Unable To Copy Directory Using ASMCMD Cp -r Command (Doc ID 829040.1) but struck out when trying to do this on 11g and 12c ASM installs.

    Tags: 
  22. trc2sql.pl - Extract SQL Statements From Oracle 10046 Trace File

    Purpose:

    This Perl script will extract the SQL Statements out of an Oracle 10046 Trace File.

    Usage:

    trc2sql.pl  output.sql
    

    Source Code

    trc2sql.pl :
    #!/usr/bin/perl
    
    # David Mann
    # ba6.us
    # Extract SQL from an Oracle SQL Trace File (10046)
    #
    # Usage:
    # Data is read from STDIN
    # trc2sql.pl  output.sql
    #
    # Change log:
    # 13-OCT-2011 : Release
    
    $InSQL = 0;
    while () {
      $MyLine = $_;
      $LineNum = $LineNum + 1;
    
      # If END OF STMT found, stop printing
    
  23. Getting Started with Vagrant on Windows

    Software Tool Installs

    1) Install Cygwin. This is a Unix-like environment that can be run from Windows. Proceed to https://cygwin.com/install.html and run the installer for the 32 or 64 bit version of Cygwin that matches your Windows version. Install for all users. Accept the default packages and add "OpenSSH Client/Server", Curl, and WGet packages as well.

    2) Install Vagrant. Accept all defaults.

    3) Install VirtualBox. Accept all defaults.