1. Installing Golden Gate Core Binaries

    Ensure access to the oracle OS user

    vagrant ssh
    
    sudo passwd oracle
    password
    password
    
    su oracle
    

    Prepare Install Binaries

    Download 122022_fbo_ggs_Linux_x64_shiphome.zip from Oracle and copy to the C:\work\[machine name]\oracle-12.2-vagrant\12.2.0.1 directory.
  2. Accessing your environment : SQLNet/OCI

    If your environment was set up with Host networking as described in these pages then the host and guest VMs all have IP addresses in the 192.168.88.* subnet.

    Your host machine (Windows or OSX) is accessible at 192.168.88.1.

    Your VMs are assigned 192.168.88.10, 192.168.88.11, etc.

  3. Accessing your environment : SSH

    • Change to the directory where you ran "vagrant up"
    • Type "vagrant ssh"

    If you want to run a SSH client like putty.exe from your host you can enter the IP address of your VM. In this environment this would be 192.168.88.10, 192.168.88.11, etc.

    Refer to this page for a more in-depth description of the networking environment.

  4. Accessing your environment : X Windows

    If your environment was set up with Host networking as described in these pages then the IP addresses are all in the 192.168.88.* subnet.

    Your host machine (Windows or OSX) is 192.168.88.1.

    Your VMs are assigned 192.168.88.10, 192.168.88.11, etc.

  5. Managing Multiple Environments

    Now that you have your first local Vagrant/VirtualBox deployment under your belt you may find it useful enough to desire multiple distinct environments. Or maybe you are experimenting with an environment (DataGuard, GoldenGate) that requires multiple VMs.

    Using advanced Vagrantfile features you can spin up multiple VMs with one "vagrant up" deployment command. I'll cover these in later blog posts.

    By updating individual Vagrantfile config files you can ensure new deployments do not have name or port collisions in your VirtualBox environment

  6. Automatically installing Oracle 12cR2 with your Vagrant/VirtualBox deployment

    This procedure will spin up a new fresh VM Spinning up a local 12cR2 Instance with Vagrant and Virtualbox. After Vagrant, VirtualBox, and the plugin that allows them to interact are installed, we will invoke Vagrant commands to get this work done. Vagrant can also do things like mount drives and kick of shell scripts, that's where the heavy lifting will be done to kick off the installed with a silent response file. The code we will be using is contained in a project on GitHub.
  7. Basic Install and test of Vagrant and Virtualbox

    1) Install Vagrant.

    Download software at https://www.vagrantup.com/ . Accept all defaults during the install.

    2) Install VirtualBox.

    Software is available at https://www.virtualbox.org/ . Accept all defaults during the install.

    3) Install Plugin for Vagrant/Virtualbox interaction.

  8. Vagrant, VirtualBox and Oracle

    This notebook contains info about automating deployments of VMs and Oracle software with Vagrant/Virtualbox.
  9. 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

  10. AWR Warehouse Operations

    Listing databases added to an AWR Warehouse with EMCLI

    $ emcli login username=username
    Enter password
    
    Login successful
    
    $ emcli awrwh_list_src_dbs
    
    Databases uploading to the AWR Warehouse:
    Target Name               Target Type      Owner   Version          Snapshot Upload Status  Snapshots Uploaded
    db1.business.com          rac_database     DMANN   11.2.0.4.180116  Enabled                 123
    db2.business.com          rac_database     DMANN   12.1.0.2.0       Enabled                 456
    

    Adding a DB to AWR Warehouse with EMCLI

    Ensure p

  11. AWR Warehouse Queries

    Some queries to help navigate the AWR warehouse:

    Databases/Instances with info in AWR Repository

    SELECT DBID, 
           DB_NAME, 
           INSTANCE_NUMBER, 
           INSTANCE_NAME, 
           HOST_NAME 
      FROM DBA_HIST_DATABASE_INSTANCE 
    ORDER BY DB_NAME, INSTANCE_NUMBER;
    
  12. ASM Information

    ASM Dictionary Views

    v$asm_aliasLists all aliases in all currently mounted diskgroups
    v$asm_clientLists all the databases currently accessing the diskgroups
    v$asm_diskLists all the disks discovered by the ASM instance
    v$asm_diskgroupLists all the diskgroups discovered by the ASM instance
    v$asm_fileLists all files that belong to diskgroups mounted by the ASM instance
    v$asm_operationReports information about current active operations.
  13. 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.
  14. 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.

  15. 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; 
  16. 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.
  17. Presentations

    Visualization

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

  18. 12c Containers

    Working with PDBs

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

  21. 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:
  22. 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.

  23. 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.
  24. 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: