1. 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.
  2. RAC

    Scan Listener Status
    $srvctl config scan_listener
    SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
    SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
    SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521
    $srvctl config scan
    SCAN name: prod-scan.example.com, Network: 1/10.2.130.0/255.255.255.0/lan900
    SCAN VIP name: scan1, IP: /prod-scan.example.com/10.2.130.90
    SCAN VIP name: scan2, IP: /prod-scan.example.com/10.2.130.92
    SCAN VIP name: scan3, IP: /prod-scan.example.com/10.2.130.91
    
  3. 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: 
  4. 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.
  5. 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

  6. 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:
  7. 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.
  8. 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
  9. 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; 
  10. 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;
    
    --
  11. 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) 
    
  12. 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: 
  13. 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.
  14. 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
  15. 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.

  16. 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.
  17. 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: 
  18. 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
    
  19. 12c Containers

    Working with PDBs

    Set your ORACLE_SID and log into the CDB instance using normal oraenv and sqlplus "/ as sysdba" method.
  20. 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.

  21. Unix Shell Scripting

    Finding file counts

    For Linux:
    cd $ORACLE_BASE/admin
    
    -- For Linux
    find -maxdepth 2 -type d | while read dir; do 
        count=$(find "$dir" -maxdepth 2 -iname \*.aud | wc -l)
        echo "$count ; $dir"
    done | grep adump | sort -n
    

    Getting around too many arguments:

    This method is scalable, does not have issues with argument list limits, and works well for millions of files. This example looks for files that match *.aud.
  22. ORA-01440 trying to convert FLOATs to NUMBERs

    I recently came across a legacy database that was using Oracle FLOAT datatype for Primary and Foreign Keys. As the database needed some attention I was asked to get data types in order and get rid of the FLOATs in favor of NUMBER data types.

  23. Python and Ldap

    Install Ldap

    C:\Users\dmann2\Downloads>pip install ldap3
    Collecting ldap3
      Downloading ldap3-1.2.2-py2.py3-none-any.whl (311kB)
        100% |################################| 317kB 1.6MB/s
    Collecting pyasn1>=0.1.8 (from ldap3)
      Downloading pyasn1-0.1.9-py2.py3-none-any.whl
    Installing collected packages: pyasn1, ldap3
    Successfully installed ldap3-1.2.2 pyasn1-0.1.9
    

    Test it

    Fire up python and:
    >>> from ldap3 import Server, Connection, ALL
    >>> server = Server('ipa.demo1.freeipa.org')
    >>>