1. Enabling/Disabling/Deleting a SQL Profile

    Submitted by dmann on Thu, 05/07/2009 - 15:56

    CDB and PDB Scope

    It looks like PDBs and CDBs can own SQL Profiles. Use DBA_SQL_PROFILES view in a PDB and CDB_SQL_PROFILES view in the CDB to review SQL profiles.

    If you log into the CDB with Cloud Control then it looks like all profile work will be done on the CDB level. Just be aware of this - if you create a SQL Profile in a PDB manually using a script like coe_xfr_sql_profile.sql, you may not be able to administrate it with the Cloud Control GUI.

  3. Golden Gate abends due to zeroed out records in trail file...

    Submitted by dmann on Wed, 02/06/2019 - 16:10

    If you are getting odd abends on your Golden Gate replicats due to trail file corruption, make sure you visit your filesystem options for the filesystem where the manager process writes the trail files.

    Long story short is that we were finding large swaths of Zeroes appearing in our trail files. Using logdump I could see the header of the trail file was OK, but somewhere down the line a valid record would be followed by straight up zeroes. Lots of them. I verified this using logdump and a hex editor.

    Tags: 
    Golden Gate
    NFS

  4. Algorithm preference in SQLNET.ENCRYPTION_TYPES_SERVER

    Submitted by dmann on Thu, 10/04/2018 - 17:24
    If you have clients connecting to a server and the clients don't specify a specific encryption algorithm in SQLNET.ENCRYPTION_TYPES_CLIENT, be aware that the order of the algorithms listed in SQLNET.ORA on the server side matters... If you don't specify SQLNET.ENCRYPTION_TYPES_CLIENT from a 12.2 client configuration, a SQLNet client side trace shows the following possibilities, the choices are listed from most secure to least secure: 
    (3775811968) [04-OCT-2018 19:47:44:284] nam_gic: Counting # of items in "SQLNET.ENCRYPTION_TYPES_CLIENT" parameter
(3775811968) [04-O
    Tags: 
    oracle
    encryption
    aes256
    3des168

  5. 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

  6. Connection encryption is killing my performance! Help!

    Submitted by dmann on Mon, 10/22/2018 - 23:11
    I find Encryption is blamed for a lot of performance issues so wanted to find out for myself how much of a performance hit we might be taking for common operations. There ain't no free lunch. Adding any extra processing or manipulation to an operation is going to add time to that operation.

    Where can encryption be enabled when working with an Oracle database?

    For Oracle there are two places to introduce encryption:

    Tags: 
    ASO
    TDE
    encryption
    aes256
    3des168

  7. emcli, python, jython, and module not found (ImportError No Module Named n)

    Submitted by dmann on Thu, 06/28/2018 - 12:40

    I've been diving into more OEM Automation and collecting information here.

    While the Oracle example scripts are a great way to find out how to interact with OEM via the emcli and python/jython interface, there doesn't seem to be much info about how this all gets done. You feed a Python script to emcli and magic happens right?

    Adeesh Fulay has a great intro article here.

    Tags: 
    emcli
    jython
    python

  8. Need to audit changes? Can't change source database at all? This may work...

    Submitted by dmann on Mon, 04/12/2010 - 12:42
    If you want to record copies of changed records on a target system you can do this with Golden Gate Software and not incur much in the way of source side resource drain.
    Tags: 
    Golden Gate
    Auditing

  9. Automatically installing Oracle 18c with your Vagrant/VirtualBox deployment

    Submitted by dmann on Thu, 07/26/2018 - 20:19
    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. Check out the Readme at https://github.com/dmann99 This is based on code by totalamateurhour.

  10. More disk space...

    Submitted by dmann on Fri, 07/06/2018 - 15:20

    Method 1 : Add a u01 on /dev/sdb

    If you need more disk space it looks like the EL Linux VMs were supplied with an extra unmounted disk. It is available at /dev/sdb but not partitioned.

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

    Submitted by dmann on Thu, 05/10/2018 - 11:03
    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.

  12. Basic Install and test of Vagrant and Virtualbox

    Submitted by dmann on Thu, 05/10/2018 - 10:37

    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.

  13. Installing Golden Gate Core Binaries

    Submitted by dmann on Wed, 05/16/2018 - 15:46

    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.

  14. Accessing your environment : SQLNet/OCI

    Submitted by dmann on Thu, 05/10/2018 - 11:37

    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.

  15. Accessing your environment : SSH

    Submitted by dmann on Thu, 05/10/2018 - 11:35
    • 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.

  16. Accessing your environment : X Windows

    Submitted by dmann on Thu, 05/10/2018 - 11:36

    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.

  17. Managing Multiple Environments

    Submitted by dmann on Thu, 05/10/2018 - 11:28

    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

  18. Vagrant, VirtualBox and Oracle

    Submitted by dmann on Thu, 05/10/2018 - 10:33
    This notebook contains info about automating deployments of VMs and Oracle software with Vagrant/Virtualbox.

  19. AWR Warehouse Operations

    Submitted by dmann on Fri, 04/13/2018 - 12:20

    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

  20. AWR Warehouse Queries

    Submitted by dmann on Tue, 04/10/2018 - 16:57
    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;

  21. ASM Information

    Submitted by dmann on Tue, 09/03/2013 - 15:07

    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.

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

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

  24. 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;

  25. 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.