I found a bug in Oracle Unified Auditing! (Or did I?)

We just finished up the Oracle Database 12c Managing Multitenant Architecture course at ACC last night and while doing the labs we were not getting the same results that Oracle got when it created the material. One of my better students, Robert, did some googling and found some blog posts that seemed to indicate that you needed to be logged in as a user other than SYS if you wanted audit policies to be applied. This didn’t seem to make a lot of sense and I did see some SYS actions that had been audited, so I ended up creating a test case and it looked like I had found a bug! Audit policies were not being enforced even though you had turned them on. Obviously this would sort of defeat the whole purpose of auditing!

The test:

I first shut down all my oracle databases and relinked the oracle binary to enable the unified auditing option:

[oracle@multitenant ~]$ . oraenv
ORACLE_SID = [oracle] ? cdb1
The Oracle base has been set to /u01/app/oracle
[oracle@multitenant ~]$ cd $ORACLE_HOME/rdbms/lib
[oracle@multitenant lib]$ make -f ins_rdbms.mk uniaud_on ioracle

The above generates a whole bunch of output and when finished, you have enabled unified auditing. After restarting my multitenant container database and my listener, I connected to the root container and created a brand new pluggable database to verify my suspicions. I wanted to make sure that I had a completely clean slate when I did the test. Of course I used netca to create a local tns entry for my new pluggable database which I decided to call pdb_uniaud_bug and created the /u01/app/oracle/oradata/cdb1/pdb_uniaud_bug directory to hold my datafiles before I ran the below.

[oracle@multitenant ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 1 10:32:54 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

sys@CDB1> create pluggable database pdb_uniaud_bug admin user uniaud_bug_admin identified by x file_name_convert=('/u01/app/oracle/oradata/cdb1/pdbseed','/u01/app/oracle/oradata/cdb1/pdb_uniaud_bug');

Pluggable database created.

sys@CDB1> alter pluggable database pdb_uniaud_bug open read write;

Pluggable database altered.

sys@CDB1> conn sys/oracle_4U@pdb_uniaud_bug as sysdba
Connected.
sys@PDB_UNIAUD_BUG>

I’ve got my database created, let’s make the testing a bit easier by setting the db_create_file_dest parameter so I can use Oracle Managed Files when I create datafiles. (I’ve decided to create a policy to audit tablespace creation).

sys@PDB_UNIAUD_BUG> alter system set db_create_file_dest = '/u01/app/oracle/oradata/cdb1/pdb_uniaud_bug' scope=both;

System altered.

Let’s create a test user to verify that the bug happens to everybody.

sys@PDB_UNIAUD_BUG> create user test_user identified by x;

User created.

sys@PDB_UNIAUD_BUG> grant connect, create tablespace to test_user;

Grant succeeded.

In a different SQL Plus session, I’ll connect into the database as this test user.

test_user@PDB_UNIAUD_BUG>

With everything set up, let’s begin the test!

sys@PDB_UNIAUD_BUG> create audit policy audi_tablespace actions create tablespace;

Audit policy created.

sys@PDB_UNIAUD_BUG> audit policy audit_tablespace;

Audit succeeded.

sys@PDB_UNIAUD_BUG> select * from audit_unified_enabled_policies where policy_name like '%TABLESP%';

USER_NAME POLICY_NAME ENABLED_ SUCCESS FAILURE
---------- --------------- -------- ------- -------
ALL USERS AUDIT_TABLESPACE BY YES YES

OK, we’ve created the audit policy and then enabled it, and we can see that it is in effect. Let’s audit!

sys@PDB_UNIAUD_BUG> create tablespace existing_connection_sys;

Tablespace created.

sys@PDB_UNIAUD_BUG> select dbusername,action_name, object_name from unified_audit_trail where action_name like '%TABLESP%';

no rows selected

Where are my audit records? Can it be that SYS actions are not logged? Let’s try with our test user.

test_user@PDB_UNIAUD_BUG> create tablespace existing_connection_test_user;

Tablespace created.

Running the same query gave me exactly the same results! No audit records. It appears that there is a bug here! I can’t believe that Oracle let this code out of development. (Is what I was thinking…)

What if I established a new connection to the database? I’m only showing the SYS connection below, I also reconnected as test_user and created another new tablespace.

sys@PDB_UNIAUD_BUG> conn sys/oracle_4U@pdb_uniaud_bug as sysdba
Connected.
sys@PDB_UNIAUD_BUG> create tablespace new_connection_sys;

Tablespace created.

sys@PDB_UNIAUD_BUG> col object_name for a25
sys@PDB_UNIAUD_BUG> select dbusername,action_name, object_name from unified_audit_trail where action_name like '%TABLESP%';

DBUSERNAME ACTION_NAME OBJECT_NAME
------------ -------------------- -------------------------
TEST_USER CREATE TABLESPACE NEW_CONNECTION_TEST_USER
SYS CREATE TABLESPACE NEW_CONNECTION_SYS

There’s the auditing we were expecting!

It turns out that Oracle Unified Auditing policies are NOT applied to users that already have connections to a database. That seems like a bug to me!

Of course I log into Oracle Support and create a service request to get a bugged logged…

Ken from Oracle Support responded with the following:

http://docs.oracle.com/database/121/DBSEG/audit_admin.htm#DBSEG353

Statement and privilege audit options from unified audit policies that are in effect at the time a database user connects to the database remain in effect for the duration of the session. When the session is already active, setting or changing statement or privilege unified audit options does not take effect in that session. The modified statement or privilege audit options take effect only when the current session ends and a new session is created.

In contrast, changes to schema object audit options become immediately effective for current sessions.

In other words, I’d just verified what the documentation already said. To be fair, I’d read Tom Kyte’s Expert Oracle Database Architecture, and I’d read the Oracle 12c Database Concepts Guide, and I’d read and taught Oracle Education’s Oracle Database 12c Managing Multitenant Architecture, AND I’d read through some (but apparently not enough) of the Oracle 12c Security Guide and I hadn’t run across that before.

Lesson for the day: Read That Fine Manual!


Is your ODA disk shelf randomly disconnecting from your compute nodes?

I’ve been doing a lot of work with ODAs (Oracle Database Appliance) lately and I have to say that I’m VERY impressed. It’s amazing how smooth Oracle can make things when they know exactly what hardware and software will be on a machine. I can’t imagine how long it would take to patch an ILOM, BIOS, SSDs, HDs, OS, various controllers, Grid Infrastructure, and Database Software, etc. if you were doing everything by scratch. With an ODA all of that can be done by downloading two files and running a few commands.

We did run into an ‘interesting’ situation where the disk shelf would randomly disconnect from the compute nodes. A restart of ohasd would bring everything back for a while (weeks, to sometimes hours), but it was really troubling to say the least. After trying a whole bunch of things, Oracle finally asked us to take a picture of the back of our disk shelves… See those service ports below? Someone, at some point in time, had plugged Ethernet cables into those ports. And that was the issue..

Those ports are only used during initial machine configuration and should NOT be used on a running machine. What would happen is a buffer would fill up and/or get some kind of packet that it didn’t know what to do with and the controller would reset. Of course the same thing would happen to the other controller at the same time. If you lose both controllers to your disk shelf simultaneously, bad things tend to happen to your database…

As far as we can tell those cables were plugged in during installation and it took over a year before the resets happened!

The below is from an X4-2, your ODA might be a bit different.

X4-2ODA


Convert your database from a single instance to RAC using rconfig… Beware of CFS choice if you are using ACFS!

Oracle provides a really cool utility called rconfig that uses a very simple XML file as input. It assumes you’ve already got a clustered file system in place, two compute nodes (at least) with user equivalency set up (the same user exists on both systems with the same user id and typically the same password).

You can find sample rconfig templates in your $ORACLE_HOME/assistants/rconfig/sampleXML directory. If you edit the file it’s all pretty self explanatory, or at least you think so…

In the file you’ll find the following:

<!--Specify the type of storage to be used by rac database. Allowable values are CFS|ASM.
    The non-rac database should have same storage type.
    ASM credentials are no needed for conversion. --> 
 <n:SharedStorage type="ASM">

Hmmmm you think. Yes, ACFS is based on ASM. Which should I choose? Well ASM is still being used under the covers, but ACFS is the new thing that Oracle wants us to use, so I should probably update the SharedStorage type of ASM to CFS. (And yes, Oracle did misspell not as no.)

The next section says this:

<!--Specify Database Area Location to be configured for rac database.
    If this field is left empty, current storage will be used for rac database.
    For CFS, this field will have directory path. --> 
 <n:TargetDatabaseArea>+ASMDG</n:TargetDatabaseArea> 

And you may think that the Target Database Area should be something like this /u02/app/oracle/oradata/datastore/.ACFS/orcl since the file system was ACFS. This all seems to be logical…

However when you run rconfig convert.xml with the above entries YOU WILL DELETE ALL YOUR DATABASE DATA FILES! (You did take a backup, didn’t you?).

[main] [ 2015-08-05 13:24:06.174 EDT ] [StorageManagement.deleteDataArea:1774]
Deleting new Storage Location:/u02/app/oracle/oradata/datastore/.ACFS/snaps/orcl/ORCL

I’ll let you guess how (and when) I figured this out.

I’ve asked Oracle to log a bug against this, as it really does have the potential to be confusing and the consequences of the mistake are so bad. I did bet the Oracle Support engineer a $25 Kiva donation that Oracle would come back and say “Not a bug”. He didn’t take me up on it…

So what should you do? If you are using ACFS then just leave the SharedStorage type at ASM and go ahead and empty out the TargetDatabaseArea if your data files are already. Everything will convert just fine and life will be good.

Happy RAC’ing!

Update: We’ve got the first step underway! A bug has been logged. Let’s see if it makes it into the next release:

Bug 21644512 – UNCLEAR COMMENTS IN RCONFIG SAMPLE XML FILES CAN LEAD TO DELETION OF ALL DATABAS

Here’s what I’ve asked them to do:

This comment (in all the sample files):

<!–Specify the type of storage to be used by rac database. Allowable values are CFS|ASM. The non-rac database should have same storage type. ASM credentials are no needed for conversion. –>
<n:SharedStorage type=”ASM”>

Should be this:

<!–Specify the type of storage to be used by the RAC database. Allowable values are CFS|ASM. ASM should be used for both native ASM deployments and ACFS on ASM deployments. The non-RAC database should have same storage type. ASM credentials are not needed for conversion. –>
<n:SharedStorage type=”ASM”>

Two fixes above: Clarification that ACFS is NOT CFS and then fixed the spelling mistake (“not” instead of “no”) in the last sentence. If you want to be really pedantic then rac should really be RAC.


New command for today… script

I’ve been working with Linux/Unix for quite a while and today I came across something I hadn’t used in a long time… The script command.

The script command allows you to capture the output of a whole bunch of commands into a file which can be quite useful. Let’s say that you want to run a bunch of commands and send the output of those commands to someone else as a file. The script command makes it pretty easy!

Just start your script with the script command followed by a file name for your output and when you are done typing all your commands, just type exit.

The script command also adds script started and script ended times to the the start and end of your file.

See below for an example:

[richard@oraclelinux ~]$ script output_of_a_bunch_of_commands.log
Script started, file is output_of_a_bunch_of_commands.log
[richard@oraclelinux ~]$ id
uid=54323(richard) gid=54323(richard) groups=10(wheel),54323(richard)
[richard@oraclelinux ~]$ env
HOSTNAME=oraclelinux
SHELL=/bin/bash
TERM=xterm
HISTSIZE=1000
SSH_CLIENT=192.168.254.46 65291 22
SSH_TTY=/dev/pts/1
USER=richard
PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/richard/bin
MAIL=/var/spool/mail/richard
PWD=/home/richard
INPUTRC=/etc/inputrc
LANG=en_US.UTF-8
HOME=/home/richard
SHLVL=2
LOGNAME=richard
SSH_CONNECTION=192.168.254.46 65291 192.168.1.20 22
LESSOPEN=|/usr/bin/lesspipe.sh %s
DISPLAY=localhost:10.0
G_BROKEN_FILENAMES=1
_=/bin/env
[richard@oraclelinux ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
 . ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
[richard@oraclelinux ~]$ exit
exit
Script done, file is output_of_a_bunch_of_commands.log
[richard@oraclelinux ~]$

A quick look at the file shows that you’ve saved all that ouput.

[richard@oraclelinux ~]$ cat output_of_a_bunch_of_commands.log
Script started on Fri 24 Jul 2015 11:42:10 PM EDT
[richard@oraclelinux ~]$ id
uid=54323(richard) gid=54323(richard) groups=10(wheel),54323(richard)
[richard@oraclelinux ~]$ env
HOSTNAME=oraclelinux
SHELL=/bin/bash
TERM=xterm
HISTSIZE=1000
SSH_CLIENT=192.168.254.46 65291 22
SSH_TTY=/dev/pts/1
USER=richard
PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/richard/bin
MAIL=/var/spool/mail/richard
PWD=/home/richard
INPUTRC=/etc/inputrc
LANG=en_US.UTF-8
HOME=/home/richard
SHLVL=2
LOGNAME=richard
SSH_CONNECTION=192.168.254.46 65291 192.168.1.20 22
LESSOPEN=|/usr/bin/lesspipe.sh %s
DISPLAY=localhost:10.0
G_BROKEN_FILENAMES=1
_=/bin/env
[richard@oraclelinux ~]$ cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
 . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
[richard@oraclelinux ~]$ exit
exit

Script done on Fri 24 Jul 2015 11:43:05 PM EDT
[richard@oraclelinux ~]$

CTRL-r is your friend!

You already knew that the up and down arrows in a Linux terminal window allow you to navigate through previously typed commands, but did you know that you can also search through your command history?

CTRL-r is great for finding those commands that are a bit tedious to type.

When you type CTRL-R in a terminal window your whole prompt turns into this strange looking text:

(reverse-i-search)`':

As you type characters the first command in your history, starting from the most recent and working back towards the top will be displayed.

Let’s say you’ve typed the following list of commands:

sqlplus / as sysdba
rman target "'/ as sysbackup'"
ls
ls -als

At a command prompt you type CTRL-r and then type s. Your prompt will now look like this:

(reverse-i-search)`s': ls -als

And the s in the als will be highlighted. What you’ve done here is found the first s in your command history.

If you type Enter right now, you’ll rerun that command.

Things get more interesting when you start typing more letters. Instead of typing just s, type sq and you’ll get the following:

(reverse-i-search)`sq': sqlplus / as sysdba

Hitting Enter will rerun that sqlplus command.

What if you want to find something that you typed earlier than the last command that matched? Just type CTRL-r again. Each time you press CTRL-r you’ll find older and older strings that match. If you have a short search string (just s for example), then it’s not unusual to find that string in a single command multiple times. Once you’ve exhausted all the matches in one command, you’ll reach back into your history to find another that matches.

What if you want to find the last thing that you found with CTRL-r?

Start a new search (or just use backspace to remove any characters you’ve started typing) and press CTRL-r again. Let’s say that you used rm as your last successful search and retrieved the rman command from the above list of commands. You then ran a few more commands and decided to run rman again… Just type CTRL-r twice. The first CRTL-r says “I want to search through my history”, the second CTRL-r says “Remember the last successful search I used? I want that one again.”

What if you don’t want to run the command you found, but instead you just put the command at your prompt so you can edit it? Instead of hitting Enter after you find a command, just press Esc. This dumps the text of the command into your terminal at a command prompt.

Here’s where things get really cool… If you use rlwrap for various Oracle commands like sqlplus, rman, etc. (See this post to install rlwrap, and this post to configure the aliases to use with rlwrap) then CTRL-r will also work in those commands too!

Want to find the last alter user command that you ran? In sqlplus just type your good friend CTLR-r and start typing alter. You probably won’t have to actually type all the letters in alter and soon you’ll have the following:

(reverse-i-search)`al': alter user dbsnmp identified by oracle_4U account unlock;

Technically CTRL-r finds the spot in your history where the command you found lives, so once you’ve found a command, you can use the up and down arrows to cycle through the commands that are near that command. What was that command that I typed right after I did that alter user command? Just press the down arrow key and you’ve found it!

There are a few idiosyncrasies with CTRL-r. First it is case sensitive so you’ll have to remember if you used SELECT or select. Second it can be kind of easy to get it into a strange place with the characters you are searching for if you start typing characters that are not actually in your command history CTLR-r will beep at you a bunch and you may or may not be able to use the backspace key to get back to where you want to be… Just use Esc to get out of the CTRL-r search and try again.

Also you may want to give CTRL-r more history to work with. If you’d like to do this you’ll need export two different environment variables, HISTSIZE and HISTFILESIZE.

I added the following to my .bash_profile file in my home directory:

# Let's give the history command and CTRL-r more to work with!
export HISTSIZE=10000
export HISTFILESIZE=10000

CTRL-r has saved me much typing over the years, I hope it helps you too!


Thoughts on the Oracle users bashrc and bash_profile

For many, many years we’ve been clouding up the oracle user’s .bashrc and .bash_profile scripts with ‘lots of things’. Some of these things will be set automatically when we run the . oraenv command (that’s dot space oraenv). Our good old friend LD_LIBRARY_PATH gets set automatically, ORACLE_HOME gets set automatically, and the PATH is extended to search in your ORACLE_HOME/bin directory.

The below screenshot comes from a ‘new’ Oracle Linux 6 system where I’ve done a software only install of Oracle Database 12.1.0.2 and have yet to create any databases. Because I don’t have a database yet, I have to type in the path to the ORACLE_HOME when I give some random SID. Once I have a database created, that database will get added to the /etc/oratab file and the SID will be recognized and automatically set the ORACLE_HOME for me.

bashrc and bash_profile

Click the image above to get a full size picture of what’s happening.

Based on the above, I might change things a bit in this post from my 12c Install Series.


Oracle BI Publisher Desktop on 64 bit Windows with 32 bit Office

While working on a client’s machine today I ran into a kind of strange issue. They have 64 bit Windows, but have stuck with 32 bit office and no matter what I tried I couldn’t get the BI Publisher tool bar to work at all…

Every time I installed BI Publisher Desktop and went into word I would see the following situation when looking in the Word plugins:

BI Publisher Template Builder for Word is in the Inactive Application Add-Ins section

Looking at the Manage COM Add-ins details, I could see the Template Builder, but had the following error.

Load Behavior: Not loaded. A runtime error occurred during the loading of the COM Add-in.

Various blogs suggested various fixes (Like Tim Dexter’s excellent post here: https://blogs.oracle.com/xmlpublisher/entry/template_builder_woes_1 ), but they appeared to suggest that you needed MS Visual Studio to get things to work.

After a bit I opened up an SR with Oracle and eventually we figured out that you can install the following: MS Visual Studio 2010 Tools for Office Runtime to get the necessary Visual Studio parts so that the Template Builder will work.

After installing that, everything worked like it should have.

Hopefully this will help someone some day…


Helping others

Part of this series of posts: Oracle Database 12c on Oracle Linux 6

One of my students at ACC suggested that I put this document up for sale on Amazon.

Instead I’ve decided to do the following:

  • Please consider opening an account on Kiva.Kiva is a non-profit organization with a mission to connect people through lending to alleviate poverty. Leveraging the internet and a worldwide network of microfinance institutions, Kiva lets individuals lend as little as $25 to help create opportunity around the world.If you use the below link you can get $25 to lend to others to get you started. I’ll also get $25 to lend to others. Technically the $25 to lend is a limited time offer, so you and I may not get this ‘bonus’ money to lend.http://www.kiva.org/invitedby/richard4068

 

I couldn’t have done it without you… < Previous Post


I couldn’t have done it without you…

Part of this series of posts: Oracle Database 12c on Oracle Linux 6

I made this document because I’ve seen folks struggle with some of the concepts in the document. Of course this document wasn’t created in a vacuum. Various blogs, the Oracle forums, a ton of Google searches and a lot of trial and error went into this document. To those that helped me in some way with the content in this document, thanks!

 

Oracle Database 12c on Oracle Linux 6 – Step 31: Enjoy a frosty beverage! < Previous Post – Next Post > Helping others


Oracle Database 12c on Oracle Linux 6 – Step 31: Enjoy a frosty beverage!

Part of this series of posts: Oracle Database 12c on Oracle Linux 6

31-01

If you’ve made it this far, congratulations! Open a frosty beverage of choice and pat yourself on the back. You deserve it!

 

Oracle Database 12c on Oracle Linux 6 – Step 30: Install the VirtualBox guest additions in the VM < Previous Post – Next Post > I couldn’t have done it without you…