1. RAC on NFS / ASM on NFS

    Submitted by dmann on Thu, 07/06/2017 - 01:42

    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.

  2. RAC

    Submitted by dmann on Wed, 12/04/2013 - 16:45

    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

      
    

  

    

 


    3. 
          
  3. 

  
        
    Scheduling a Cron Job
    
    
      
    
      Submitted by dmann on Sat, 10/11/2008 - 21:39    
    
  
  
    
    
    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:


      
    

  

    

 


    4. 
          
  4. 

  
        
    Test Driving the h2 Embedded Java Database
    
    
      
    
      Submitted by dmann on Fri, 10/28/2011 - 00:11    
    
  
  
    
    
    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. 
    Tags: 
    Java
    database
      
    

  

    

 


    5. 
          
  5. 

  
        
    Creating a RAC Cluster on Virtualbox
    
    
      
    
      Submitted by dmann on Sun, 03/12/2017 - 12:07    
    
  
  
    
    
    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.
      
    

  

    

 


    6. 
          
  6. 

  
        
    ASH to the rescue... for now...
    
    
      
    
      Submitted by dmann on Fri, 02/24/2017 - 17:03    
    
  
  
    
    
    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: 
    ash
    crash
      
    

  

    

 


    7. 
          
  7. 

 


    8. 
          
  8. 

  
        
    Gnu Screen Usage
    
    
      
    
      Submitted by dmann on Fri, 01/20/2017 - 12:43    
    
  
  
    
    
    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.
      
    

  

    

 


    9. 
          
  9. 

  
        
    Oracle Enterprise Manager / Cloud Control
    
    
      
    
      Submitted by dmann on Mon, 02/27/2012 - 15:43    
    
  
  
    
    
    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

      
    

  

    

 


    10. 
          
  10. 

  
        
    Metric Extensions
    
    
      
    
      Submitted by dmann on Mon, 12/05/2016 - 17:55    
    
  
  
    
    
    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.
      
    

  

    

 


    11. 
          
  11. 

  
        
    Querying Sysman Schema
    
    
      
    
      Submitted by dmann on Fri, 11/04/2016 - 23:56    
    
  
  
    
    
    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
      
    

  

    

 


    12. 
          
  12. 

  
        
    Data Guard Status
    
    
      
    
      Submitted by dmann on Thu, 12/10/2015 - 10:51    
    
  
  
    
    
    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; 
      
    

  

    

 


    13. 
          
  13. 

  
        
    CYA Auditing
    
    
      
    
      Submitted by dmann on Wed, 07/13/2016 - 15:57    
    
  
  
    
    
    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;

--
      
    

  

    

 


    14. 
          
  14. 

  
        
    Quickie bar graph widget for use in Apex Reports...
    
    
      
    
      Submitted by dmann on Fri, 05/22/2009 - 14:34    
    
  
  
    
    
    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) 

    Tags: 
    apex
    HTML
    Application Express
    Graph
      
    

  

    

 


    15. 
          
  15. 

  
        
    Oracle IN Condition and Multi Column Subqueries
    
    
      
    
      Submitted by dmann on Fri, 02/19/2010 - 15:02    
    
  
  
    
    
    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: 
    oracle
    sql
      
    

  

    

 


    16. 
          
  16. 

  
        
    DB Links across the Enterprise, a Connectogram with d3.js (Work In Progress)
    
    
      
    
      Submitted by dmann on Mon, 07/16/2012 - 00:43    
    
  
  
    
    
    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.
      
    

  

    

 


    17. 
          
  17. 

  
        
    Long Operations Real Time Viewer - Perl
    
    
      
    
      Submitted by dmann on Sun, 03/28/2010 - 00:50    
    
  
  
    
    
    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
    Tags: 
    perl
    monitoring
      
    

  

    

 


    18. 
          
  18. 

  
        
    RecreateDBLinks.sql
    
    
      
    
      Submitted by dmann on Mon, 06/04/2012 - 14:22    
    
  
  
    
    
    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. 
    Tags: 
    sqlplus
    database links
    DDL
      
    

  

    

 


    19. 
          
  19. 

  
        
    OraPad
    
    
      
    
      Submitted by dmann on Sat, 11/26/2011 - 08:21    
    
  
  
    
    
    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. 


    Tags: 
    utilities
    Java
    OraPad
    Groovy
      
    

  

    

 


    20. 
          
  20. 

  
        
    Making ASMCMD do what you want it to do... like copy files recursively...
    
    
      
    
      Submitted by dmann on Thu, 09/15/2016 - 17:49    
    
  
  
    
    
    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: 
    asmcmd
    asm
      
    

  

    

 


    21. 
          
  21. 

  
        
    trc2sql.pl - Extract SQL Statements From Oracle 10046 Trace File
    
    
      
    
      Submitted by dmann on Fri, 10/14/2011 - 17:00    
    
  
  
    
    
    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

    Tags: 
    perl
    sql trace
      
    

  

    

 


    22. 
          
  22. 

  
        
    12c Containers
    
    
      
    
      Submitted by dmann on Thu, 08/04/2016 - 15:54    
    
  
  
    
    
    Working with PDBs
    
Set your ORACLE_SID and log into the CDB instance using normal oraenv and sqlplus "/ as sysdba" method.
      
    

  

    

 


    23. 
          
  23. 

 


    24. 
          
  24. 

  
        
    Getting Started with Vagrant on Windows
    
    
      
    
      Submitted by dmann on Tue, 07/12/2016 - 22:27    
    
  
  
    
    
    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. 

      
    

  

    

 


    25. 
          
  25. 

  
        
    Unix Shell Scripting
    
    
      
    
      Submitted by dmann on Sat, 10/11/2008 - 21:34    
    
  
  
    
    
    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.
      
    

  

    

 


    26.