Local Yum Repos for Oracle Linux

At least one person asked me why I did this, so I’ll start by explaining the motivation for setting up my own mirror of Yum repositories freely available on public-yum.oracle.com.

It comes down to 5 main reasons:

1. Wanting my Oracle Linux 6 installation can take advantage of the “latest” repositories
2. Wanting the ability to update to a consistent version by using repositories I control
3. Reducing the amount of data I download over the internet
4. A desire to learn how to set up a Yum repository [mirror]
5. Making my updates faster as they only have to retrieve packages from the LAN

When I first looked into setting up a Yum mirror I found a number of articles covering how to do so via rsync and I then found this post where one of the comments suggests that allowing rsync access to public-yum.oracle.com would be nice. This made me realised that the rsync approach wasn’t going to work for the Oracle Linux repositories (it seems the suggestion was well received so this may change in the future). I also found a OTN article covering “How to Create a Local Yum Repository for Oracle Linux“. I eagerly started to read and quickly hit a snag for me in the prerequisites section:

Have valid customer support identifier (CSI)

I don’t have a CSI. My customers all have CSIs, because they run Oracle in production. I don’t have a CSI as I only run the OTN versions of Oracle software in my lab so that I can test out things I don’t have opportunity, access or time to test on client sites.

Anyway, with a little bit of reading around I found a way to create local mirrors of the Oracle Linux 6 Latest and Oracle UEK Latest repositories.

What follows was carried out on a VM, but there is no reason why any of this won’t work equally on a physical host. If you encounter any problem replicating what I’ve done here then please comment and I’ll gladly try to help.

1) Allocating Storage

You’re going to need a reasonable amount of storage for this. My “repos” file system currently holds 24G of data and that is just for Oracle Linux 6 Latest and Oracle UEK Latest. I created a dedicated file system for my repositories on a LVM volume, but won’t cover that here. Allocate the storage as you see fit, but you’re going to want at least the 24G quoted.

2) Create Directory For Repos

As mentioned above, I have a dedicated file system for my repositories. It’s mounted under /repos and I’ll include that in all the code listings that follow. If you chose to use a different directory structure then clearly you’ll need to make the required changes.

# mkdir -p /repos/x86_64/

3) Install yum-utils

yum-utils includes a couple of commands you’re going to need for this, reposync and createrepo.

# yum -y install yum-utils

4) Setup Repositories

Follow the instructions on public-yum.oracle.com in order to set up the Oracle repositories.

By default reposync will create a local copy of all your enabled repositories, but it is also possible to specify the name of the repo[s] you want to sync on the command line using the “r” or “repoid” flag. I use this option as I want to have my local repositories enabled on all my Oracle Linux 6 hosts, including the repository machine, but only want reposync to run for the public-yum.oracle.com repositories I want to mirror locally. This means that I do not enable any of the repositories in the public-yum-ol6.repo file downloaded from Oracle and create a new .repo file for my local repositories that I can distribute to all machines.

5) Run reposync

Running reposync is as simple as the command below:

# /usr/bin/reposync --repoid=ol6_UEK_latest --repoid=ol6_latest -p /repos/x86_64

6) Run createrepo

Once the repositories are downloaded to the local file system you need to run createrepo in order to create the repository metadata:

# createrepo /repos/x86_64/ol6_UEK_latest/getPackage/
# createrepo /repos/x86_64/ol6_latest/getPackage/

The “update” option for createrepo looked attractive in the man page, but whenever I used it the process was killed by the OOM Killer and I haven’t investigated in detail.

7) Allowing Web Access

In order to make use of the repositories they need to be exposed to the machines requiring access. HTTP is as good a way as any for my purposes, so I installed Apache (yum -y install httpd), ensured it would restart on reboot (chkconfig httpd on) and created symbolic links to my repositories:

# cd /var/www/html/repo/OracleLinux/OL6
# ln -s /repos/x86_64/ol6_UEK_latest/getPackage/ ./UEK/latest/x86_64
# ln -s /repos/x86_64/ol6_latest/getPackage/ ./latest/x86_64

8) Script for Updating Mirrors

Once I’d got it working I created a very simple shell script to allow me update whenever I appropriate:

#!/bin/bash
LOG_FILE=/repos/logs/repo_cron_$(date +%Y.%m.%d).log
/usr/bin/reposync --repoid=ol6_UEK_latest --repoid=ol6_latest -p /repos/x86_64 >> $LOG_FILE 2>&1
/usr/bin/createrepo /repos/x86_64/ol6_UEK_latest/getPackage/ >> $LOG_FILE 2>&1
/usr/bin/createrepo /repos/x86_64/ol6_latest/getPackage/ >> $LOG_FILE 2>&1

It’s then just a matter of pointing my Oracle Linux 6 installations at my local repository.

For reference my repo file is as follows (with hostnames removed)

[ol6_latest_local]
name=Oracle Linux $releasever Latest ($basearch)
baseurl=http://<hostname removed>/repo/OracleLinux/OL6/latest/$basearch/
gpgkey=http://<hostname removed>/RPM-GPG-KEY-oracle-ol6
gpgcheck=1
enabled=1

[ol6_UEK_latest_local]
name=Latest Unbreakable Enterprise Kernel for Oracle Linux $releasever ($basearch)
baseurl=http://<hostname removed>/repo/OracleLinux/OL6/UEK/latest/$basearch/
gpgkey=http://<hostname removed>/RPM-GPG-KEY-oracle-ol6
gpgcheck=1
enabled=1

Column Encryption tricks

This story starts when someone ask me to Look on Transparent Data Encryption issue. We discover that we could not do Partition Exchange due to the difference in encryption keys on the involved table columns. It have clear common sense to bring them in sync thus we happy trying to implement Oracle Recommendation (Unable To exchange A Partition With A Table If It Has An Encrypted Column [ID 958728.1]) using the following scripts.

ALTER TABLE TESTTBL MODIFY (COL1 decrypt);
ALTER TABLE TESTTBL MODIFY (COL1 encrypt using '3DES168' identified by "MyKey2013");
ALTER TABLE TESTTBL MODIFY (COL2 decrypt);
ALTER TABLE TESTTBL MODIFY (COL2 encrypt using '3DES168' identified by "MyKey2013");

But it does not change the situation. Why keys are different and how to check it. Officially oracle does not provide any information about encrypting keys but… the following query returns it to me.

SELECT u.name OWNER, o.name TABLE_NAME, c.name COLUMN_NAME,
          case e.ENCALG when 1 then '3 Key Triple DES 168 bits key'
                        when 2 then 'AES 128 bits key'
                        when 3 then 'AES 192 bits key'
                        when 4 then 'AES 256 bits key'
                        else 'Internal Err'
          end KEY_TYPE,
          decode(bitand(c.property, 536870912), 0, 'YES', 'NO'),
          case e.INTALG when 1 then 'SHA-1'
                        when 2 then 'NOMAC'
                        else 'Internal Err'
          end KEY_ALG,
          e.COLKLC as KEY_VAL
   from sys.user$ u, sys.obj$ o, sys.col$ c, sys.enc$ e
   where e.obj#=o.obj# and o.owner#=u.user# and bitand(flags, 128)=0 and
         e.obj#=c.obj# and bitand(c.property, 67108864) = 67108864
ORDER BY 1,2,3;

Actually it is not THE KEYS but the keys encrypted by master key that I do not know, but for our purpose when we just compare the keys it provide required information.

EPANI	TESTTBL	COL1	3 Key Triple DES 168 bits key	YES	SHA-1
4177414141414141414141414141414141414141414141462F5955334D6532392B54453450566747626F4F7570635A51454162786335394A4A524D4E30576335366370344F6D5A364A37515365544F7A6C4B4C534C77633D
EPANI	TESTTBL	COL2	3 Key Triple DES 168 bits key	YES	SHA-1
4177414141414141414141414141414141414141414141462F5955334D6532392B54453450566747626F4F7570635A51454162786335394A4A524D4E30576335366370344F6D5A364A37515365544F7A6C4B4C534C77633D
EPANI	ARCHTESTTBL	COL1	3 Key Triple DES 168 bits key	YES	SHA-1
4177414141414141414141414141414141414141414141536576676A5A79514B544B50592F3257762F3359726A6D6A63525747634A6F4B53754645665A7139376677336C394E306D3071706C6667306B6564586233524D3D
EPANI	ARCHTESTTBL	COL2	3 Key Triple DES 168 bits key	YES	SHA-1
4177414141414141414141414141414141414141414141536576676A5A79514B544B50592F3257762F3359726A6D6A63525747634A6F4B53754645665A7139376677336C394E306D3071706C6667306B6564586233524D3D

Now we see that encryption keys are still different. Lets do it one more time running one command at the time.

ALTER TABLE TESTTBL MODIFY (COL1 decrypt);

EPANI	TESTTBL	COL2	3 Key Triple DES 168 bits key	YES	SHA-1
4177414141414141414141414141414141414141414141462F5955334D6532392B54453450566747626F4F7570635A51454162786335394A4A524D4E30576335366370344F6D5A364A37515365544F7A6C4B4C534C77633D
EPANI	ARCHTESTTBL	COL1	3 Key Triple DES 168 bits key	YES	SHA-1
4177414141414141414141414141414141414141414141536576676A5A79514B544B50592F3257762F3359726A6D6A63525747634A6F4B53754645665A7139376677336C394E306D3071706C6667306B6564586233524D3D
EPANI	ARCHTESTTBL	COL2	3 Key Triple DES 168 bits key	YES	SHA-1
4177414141414141414141414141414141414141414141536576676A5A79514B544B50592F3257762F3359726A6D6A63525747634A6F4B53754645665A7139376677336C394E306D3071706C6667306B6564586233524D3D

We got 3 rows. The column was decrypted successfully.
Lets run the second command from our script.

ALTER TABLE TESTTBL MODIFY (COL1 encrypt using '3DES168' identified by "MyKey2013");

The command has run successfully but what we have in a key table?

EPANI	TESTTBL	COL1	3 Key Triple DES 168 bits key	YES	SHA-1
4177414141414141414141414141414141414141414141462F5955334D6532392B54453450566747626F4F7570635A51454162786335394A4A524D4E30576335366370344F6D5A364A37515365544F7A6C4B4C534C77633D
EPANI	TESTTBL	COL2	3 Key Triple DES 168 bits key	YES	SHA-1
4177414141414141414141414141414141414141414141462F5955334D6532392B54453450566747626F4F7570635A51454162786335394A4A524D4E30576335366370344F6D5A364A37515365544F7A6C4B4C534C77633D
EPANI	ARCHTESTTBL	COL1	3 Key Triple DES 168 bits key	YES	SHA-1
4177414141414141414141414141414141414141414141536576676A5A79514B544B50592F3257762F3359726A6D6A63525747634A6F4B53754645665A7139376677336C394E306D3071706C6667306B6564586233524D3D
EPANI	ARCHTESTTBL	COL2	3 Key Triple DES 168 bits key	YES	SHA-1
4177414141414141414141414141414141414141414141536576676A5A79514B544B50592F3257762F3359726A6D6A63525747634A6F4B53754645665A7139376677336C394E306D3071706C6667306B6564586233524D3D

We got 4 rows but the encryption key has not changed. At this point we can put our attention that for all columns in the same table the encryption key is the same, even for those which was encrypted without “identified by” clause.

Now we try to decrypt both columns at once and then encrypt both columns back.

ALTER TABLE TESTTBL MODIFY (COL1 decrypt);
ALTER TABLE TESTTBL MODIFY (COL2 decrypt);
ALTER TABLE TESTTBL MODIFY (COL1 encrypt using '3DES168' identified by "MyKey2013");
ALTER TABLE TESTTBL MODIFY (COL2 encrypt using '3DES168' identified by "MyKey2013");

And check the status of encrypt keys in a storage.

EPANI	TESTTBL	COL1	3 Key Triple DES 168 bits key	YES	SHA-1
4177414141414141414141414141414141414141414142622B726E53615843627A437379797646783333745031517833496542486D514D55765138724A4E4967525A7248534B706C735148756D454C745243597A6C6B6B3D
EPANI	TESTTBL	COL2	3 Key Triple DES 168 bits key	YES	SHA-1
4177414141414141414141414141414141414141414142622B726E53615843627A437379797646783333745031517833496542486D514D55765138724A4E4967525A7248534B706C735148756D454C745243597A6C6B6B3D
EPANI	ARCHTESTTBL	COL1	3 Key Triple DES 168 bits key	YES	SHA-1
4177414141414141414141414141414141414141414141536576676A5A79514B544B50592F3257762F3359726A6D6A63525747634A6F4B53754645665A7139376677336C394E306D3071706C6667306B6564586233524D3D
EPANI	ARCHTESTTBL	COL2	3 Key Triple DES 168 bits key	YES	SHA-1
4177414141414141414141414141414141414141414141536576676A5A79514B544B50592F3257762F3359726A6D6A63525747634A6F4B53754645665A7139376677336C394E306D3071706C6667306B6564586233524D3D

As you see in resultset, the columns reencrypted using new key.

Explanation.Oracle use single key to encrypt all columns in a single table and until you fully decrypt all columns in a table the newly encrypted columns would still use the old keys. I fully understand logic behind it, but it would have more sense get raise an error when you try to encrypt column using specific derivation key but could not do it, rather silently encrypt column using different key.

DNS Slave Setup Doh!

I recently found myself wanting to set up a DNS slave for the DNS server I run in my lab environment; and taking the view that it can’t be that hard I jumped into achieving that goal. It was pretty straightforward and this post is just a few references and hopefully enough information on the error messages I encountered (due to misconfiguration) to bring someone here that has made the same mistake. The existing DNS (master) server runs on Oracle Linux 6 and I wanted to setup a slave on Ubuntu 12.04. The site that I found most useful as a reference for someone that hadn’t done this before was www.server-world.info. Not a site I’m aware of visiting before, but it seems like a great reference from what I’ve looked at so far.

After setting things up I found I was getting the following messages in /var/log/syslog on the Ubuntu (slave) machine:

Feb 10 10:36:26 <hostname> named[4035]: running
Feb 10 10:36:26 <hostname> named[4035]: zone <zone file 1>/IN: Transfer started.
Feb 10 10:36:26 <hostname> named[4035]: transfer of '<zone file 1>/IN' from 192.168.1.3#53: failed to connect: host unreachable
Feb 10 10:36:26 <hostname> named[4035]: transfer of '<zone file 1>/IN' from 192.168.1.3#53: Transfer completed: 0 messages, 0 records, 0 bytes, 0.001 secs (0 bytes/sec)
Feb 10 10:36:27 <hostname> named[4035]: zone <zone file 2>/IN: refresh: skipping zone transfer as master 192.168.1.3#53 (source 0.0.0.0#0) is unreachable (cached)
Feb 10 10:36:27 <hostname> named[4035]: zone <zone file 3>/IN: refresh: skipping zone transfer as master 192.168.1.3#53 (source 0.0.0.0#0) is unreachable (cached)
Feb 10 10:36:27 <hostname> named[4035]: zone <zone file 4>/IN: refresh: skipping zone transfer as master 192.168.1.3#53 (source 0.0.0.0#0) is unreachable (cached)
Feb 10 10:36:27 <hostname> named[4035]: zone <zone file 5>/IN: refresh: skipping zone transfer as master 192.168.1.3#53 (source 0.0.0.0#0) is unreachable (cached)
Feb 10 10:36:27 <hostname> named[4035]: zone <zone file 6>/IN: refresh: skipping zone transfer as master 192.168.1.3#53 (source 0.0.0.0#0) is unreachable (cached)

While investigating I found myself reading the following articles:

• http://ubuntuforums.org/showthread.php?t=1805880 – Very similar errors apparently caused by missing reverse DNS for the slave name server
• http://ubuntuforums.org/showthread.php?t=767138 – Different errors, but one I hit along the way as a result of attempting to mix and match Red Hat configuration with Ubuntu configuration

I’ve included them here in case they are applicable to anyone else’s issues.

The last thing I read on the subject was http://www.mail-archive.com/bind-users@lists.isc.org/msg03151.html. The letters TCP jumped out at me. I run iptables on the Oracle Linux 6 host (DNS master) and it was fresh in my mind that I had port 53 open for UDP traffic for DNS lookup. I knew DNS lookups worked against that host as I’d been testing from various locations minutes before. It had to be worth a quick try to see if it was something so simple. It was! I’d been able to do DNS lookup on the master DNS from the slave as port 53 was open for UDP traffic, but as I’d just learnt: zone transfers are carried out using TCP as covered on Wikipedia.

Shutdown Abort (When Ready)

When I was doing some testing of service failover I ran into something that I think is interesting behaviour. If I issue an “abort” command I expect an abort, not a bit of tidying up before aborting, which is what I found the following command doing:

srvctl shutdown instance -d <database name> -i <instance name> -o abort

Alert log from “shutdown abort” of instance via srvctl

2012-07-18 10:34:53.067000 +01:00
ALTER SYSTEM SET service_names='DB_TST_SVC2','DB_TST_SVC3','DB_TST_SVC5','DB_TST_SVC4' SCOPE=MEMORY SID='DB_TST1';
ALTER SYSTEM SET service_names='DB_TST_SVC2','DB_TST_SVC5','DB_TST_SVC4' SCOPE=MEMORY SID='DB_TST1';
ALTER SYSTEM SET service_names='DB_TST_SVC5','DB_TST_SVC4' SCOPE=MEMORY SID='DB_TST1';
ALTER SYSTEM SET service_names='DB_TST_SVC5' SCOPE=MEMORY SID='DB_TST1';
ALTER SYSTEM SET service_names='DB_TST' SCOPE=MEMORY SID='DB_TST1';
2012-07-18 10:34:54.145000 +01:00
Shutting down instance (abort)
License high water mark = 7
USER (ospid: 3008): terminating the instance
2012-07-18 10:34:55.158000 +01:00
Instance terminated by USER, pid = 3008
Instance shutdown complete

Alert log from “shutdown abort” of instance via SQL*Plus

2012-07-18 10:41:02.663000 +01:00
Shutting down instance (abort)
License high water mark = 8
USER (ospid: 19176): terminating the instance
Instance terminated by USER, pid = 19176
2012-07-18 10:41:03.812000 +01:00
Instance shutdown complete

The tests were done using Oracle 11.2.0.2

This probably isn’t going to change anyone’s life, but no harm in knowing it

Deterministic Service Failover

Until some testing about 6 months ago I was under the impression that if a database service was defined as being “preferred” on 1 instance of a RAC database and “available” on N other instances (where N is greater than 1); and the preferred instance is killed then the instance that the service moves to is not deterministic, i.e., Oracle picks one of the available instances to start the service on in a unpredictable way. I cannot work out what gave me this impression, so will just have to put it down to an assumption I made at some point in the past and never validated. From conversations with other DBAs I was not alone.

Anyway, I now believe that the failover it deterministic as demonstrated below.

Creating some test services

$ srvctl add service -d DB_TST -s DB_TST_SVC1 -r "DB_TST1" -a "DB_TST2,DB_TST3,DB_TST4"
$ srvctl add service -d DB_TST -s DB_TST_SVC2 -r "DB_TST1" -a "DB_TST3,DB_TST4,DB_TST2"
$ srvctl add service -d DB_TST -s DB_TST_SVC3 -r "DB_TST1" -a "DB_TST4,DB_TST2,DB_TST3"

Verifying the state of the services

$ srvctl status service -d DB_TST
Service DB_TST_SVC1 is running on instance(s) DB_TST1
Service DB_TST_SVC2 is running on instance(s) DB_TST1
Service DB_TST_SVC3 is running on instance(s) DB_TST1

Killing instance 1

At this point I killed instance 1 using “shutdown abort” in SQL*Plus.

Checking the status of the services

$ srvctl status service -d DB_TST
Service DB_TST_SVC1 is running on instance(s) DB_TST2
Service DB_TST_SVC2 is running on instance(s) DB_TST3
Service DB_TST_SVC3 is running on instance(s) DB_TST4

As you can see the service failed over to the instance based on the order the instances are defined in the “available” list. I did a few more tests to convince myself that this wasn’t just a one off, but won’t fill this post with the output.

If you’re reading this and thinking, “Yes, that was obvious.” Sorry!

During the testing I ran into something else that appears very trivial, but worth being aware of… When is shutdown abort not an immediate abort.

Advanced Compression: Visualising Insert Overhead

There is a My Oracle Support note, “Master Note for OLTP Compression [ID 1223705.1]“, which talks about the overheads of “COMPRESS FOR ALL OPERATIONS”. It’s a great note and, for a very artificial test, quotes overheads of around 25% CPU, 100% redo and 100% undo when inserting data via conventional methods. The purpose of this post is to visualise when the extra resources are used.

My test uses a SQL*Plus session that repeatedly inserts a single row into a table with “COMPRESS FOR ALL OPERATIONS” enabled. After each insert I use the SQL*Plus HOST command to capture redo and undo statistics for my session. I struggled to measure CPU for each insert. The “CPU used by this session” statistic was not granular enough for my single row inserts and the CPU tracked by SQL Trace was being rounded to 0ms or 1ms for each insert. However the execute elapsed attribute from SQL Trace did give me good granularity. Obviously SQL Trace has it’s own overheads as I’ve talked about before but I don’t think they are important in this case as I’m just after a visualisation of when extra resources are used.

The shell and SQL scripts used to capture statistics are at the foot of this post.

My SQL*Plus control session looks a little like this:

sqlplus neil
create table compteststats
(	testid		varchar2(10)
,	snapid		number
,	statname	varchar2(64)
,	statval		number);

create table compobj
(	col1 varchar2(30)
,	col2 number
,	col3 varchar2(30)
,	col4 varchar2(30)
,	col5 number
,	col6 varchar2(30))
compress for all operations;

exec dbms_application_info.set_module('TESTSESS','TESTSESS');
exec dbms_monitor.session_trace_enable

--capture starting values
host ./inscompstat.sh TEST1

insert into compobj values (lpad('w',30,'w'),1,lpad('x',30,'x'),lpad('y',30,'y'),2,lpad('z',30,'z'));
host ./inscompstat.sh TEST1

insert into compobj values (lpad('w',30,'w'),1,lpad('x',30,'x'),lpad('y',30,'y'),2,lpad('z',30,'z'));
host ./inscompstat.sh TEST1

-- repeat above insert/host combination until the first block is full and we move to a second

exit

I was then able to report redo and undo statistics using the SQL below

select snapid row#
,	max(decode(statname,'redo size',delta,0)) redo_size
,	max(decode(statname,'undo change vector size',delta,0)) undo_size
,	max(decode(statname,'HSC OLTP positive compression',delta,0)) HSC_OLTP_positive_comp
,	max(decode(statname,'HSC OLTP Compressed Blocks',statval,0)) HSC_OLTP_Compressed_Blocks
from (
	select testid,snapid,statname, statval
	,statval-lag(statval) over (partition by testid,statname order by snapid) delta
	from compteststats
	where testid = 'TEST2'
	and snapid > 0
)
group by snapid
order by snapid;

And I captured the elapsed time for each insert from the SQL Trace file using the grep/awk combo below. I then combined these values with the redo/undo figures in a spreadsheet.

grep "^EXEC #139771649698992" orcl1_ora_6987.trc | awk -F[,=] '{print $4}'

The chart produced from the spreadsheet looks like this, you can click to enlarge but get the gist from the small version.

The number of inserts is plotted on the X axis and the Y axis is either the value from v$sesstat or elapsed micro seconds from the trace file. The values are not important. What I found interesting was being able to visualise the life of the block. You can see the spikes in redo and undo each time the block is (re)compressed and as the block gradually fills up the compressions become more frequent until we get a lull in the compressions as we start to fill a second data block. The last spike on the chart is first compression of the second block.

Excerpts from my data is below. You can see the “HSC OLTP positive compression” has a delta of 1 for each spike demonstrating that the spike is caused by block compression.

      ROW#  REDO_SIZE  UNDO_SIZE HSC_OLTP_POSITIVE_COMP HSC_OLTP_COMPRESSED_BLOCKS
---------- ---------- ---------- ---------------------- --------------------------
         1        604        112                      0                          0
         2        432         68                      0                          0
         3        432         68                      0                          0
         4        432         68                      0                          0
         5        432         68                      0                          0
         6        432         68                      0                          0
         7        432         68                      0                          0
         8        432         68                      0                          0
...
        51        432         68                      0                          0
        52        432         68                      0                          0
        53       9092       8320                      1                          1
        54        420         68                      0                          1
        55        420         68                      0                          1
...
        97        420         68                      0                          1
        98        420         68                      0                          1
        99       9152       8320                      1                          1
       100        420         68                      0                          1
       101        420         68                      0                          1
...
       613        432         68                      0                          1
       614        432         68                      0                          1
       615       9092       8320                      1                          2
       616        420         68                      0                          2
       617        420         68                      0                          2

As stated previously – here are my scripts.

inscompstat.sh

sqlplus -S neil/neil <<SQLINP
@inscompstat $1
exit
SQLINP

inscompstat.sql

set verify off
insert into compteststats
(testid,snapid,statname,statval)
select '&1', c.maxsnap, s.name, s.value
from (
        select n.name, s.value
        from v$session u, v$sesstat s, v$statname n
        where u.sid = s.sid
        and s.statistic# = n.statistic#
        and u.module = 'TESTSESS'
        and n.name in ( 'redo size'
                ,       'undo change vector size'
                ,       'HSC OLTP positive compression'
                ,       'HSC OLTP Compressed Blocks')
        ) s
,       (select nvl(max(snapid),-1)+1 maxsnap
        from compteststats
        where testid = '&1') c
;

Compression Advisory: DBMS_COMPRESSION

I’ve been doing a lot of testing with COMPRESS FOR ALL OPERATIONS (OLTP compression) recently and spent a bit of time with the Compression Advisor. This post documents a few items I noticed along the way.

I picked up instructions on how to run the Compression Advisor from My Oracle Support note “Compression Advisory in 11GR2: Using DBMS_COMPRESSION [ID 762974.1]“. The note advises me to create a new tablespace and informs me that two temporary segments are created.

Please create a tablespace before running compression advisor as it creates two temporary tables and will do significant amount of I/O traffic against them.

Below is a sneak preview of the temporary segments that will be created.

create table "ACME".DBMS_TABCOMP_TEMP_UNCMP tablespace "SCRATCH" nologging
 as select /*+ DYNAMIC_SAMPLING(0) FULL("ACME"."ACCS") */ *
 from "ACME"."ACCS"  sample block( 99) mytab

create table "ACME".DBMS_TABCOMP_TEMP_CMP organization heap 
 tablespace "SCRATCH" compress for all operations nologging
 as select /*+ DYNAMIC_SAMPLING(0) */ *
 from "ACME".DBMS_TABCOMP_TEMP_UNCMP mytab

DBMS_COMPRESSION runs using invoker rights so the privileges of the user I log on as are relevant.

select procedure_name, authid 
from dba_procedures 
where owner = 'SYS' 
and object_name = 'DBMS_COMPRESSION';

PROCEDURE_NAME                 AUTHID
------------------------------ ------------
INCREMENTAL_COMPRESS           CURRENT_USER
GET_COMPRESSION_TYPE           CURRENT_USER
GET_COMPRESSION_RATIO          CURRENT_USER
                               CURRENT_USER

Taking all of the above into account I’ve created a new SCRATCH tablespace for my temporary segments and will run the analysis logged in as myself and not the schema user who owns the segment I want to compress. As we at ORAganism are so security minded I am not permitted to have the standard “dba” role and have a cut down one with only the permissions I need.

create tablespace scratch datafile '+data' size 50m;

create role nj_dba;
create user nj identified by nj;
grant create session, create any table, drop any table, select any table to nj_dba;
grant execute on sys.dbms_monitor to nj_dba;
grant nj_dba to nj;

Also according to note “ORA-01536: space quota exceeded for tablespace along with DBMS_COMPRESSION.GET_COMPRESSION_RATIO [ID 970324.1]” I need to give the owner of the segment being analysed a quota on my scratch tablespace.

To get rid of the errors, the user specified as the second parameter in the DBMS_COMPRESSION.GET_COMPRESSION_RATIO need to have … some QUOTA over the scratch tablespace specified by the first parameter.

Therefore:

alter user acme quota unlimited on scratch;

I have created my script to call the advisor, dbms_comp.sql, as below.

set serveroutput on
set feedback on
set verify off

declare
	blkcnt_cmp BINARY_integer;
	blkcnt_uncmp BINARY_integer;
	row_cmp BINARY_integer;
	row_uncmp BINARY_integer;
	cmp_ratio number;
	comptype_str varchar2(60);
begin
	dbms_compression.get_compression_ratio(
		scratchtbsname => upper('&3.')
	,	ownname => upper('&1.')
	,	tabname => upper('&2.')
	,	partname => null
	,	comptype => dbms_compression.comp_for_oltp
	,	blkcnt_cmp => blkcnt_cmp
	,	blkcnt_uncmp => blkcnt_uncmp
	,	row_cmp => row_cmp
	,	row_uncmp => row_uncmp
	,	cmp_ratio => cmp_ratio
	,	comptype_str => comptype_str
	,	subset_numrows => &4.
	);
	DBMS_OUTPUT.PUT_LINE('Block count compressed = ' || blkcnt_cmp);
	DBMS_OUTPUT.PUT_LINE('Block count uncompressed = ' || blkcnt_uncmp);
	DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || row_cmp);
	DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || row_uncmp);
	--DBMS_OUTPUT.PUT_LINE('Compression type = ' ||comptype_str);
	DBMS_OUTPUT.PUT_LINE('Compression ratio = '||round(blkcnt_uncmp/blkcnt_cmp,1)||' to 1');
	DBMS_OUTPUT.PUT_LINE('Compression % benefit = '||round((blkcnt_uncmp-blkcnt_cmp)/blkcnt_uncmp*100,1));
	--DBMS_OUTPUT.PUT_LINE('Compression ratio org= '||cmp_ratio);
end;
/
set verify on

So we should be good to go:

conn nj/nj
@orcl> @dbms_comp acme accs scratch 200000
declare
*
ERROR at line 1:
ORA-20000: Compression Advisor tablespace "SCRATCH" does not exist
ORA-06512: at "SYS.PRVT_COMPRESSION", line 776
ORA-06512: at "SYS.DBMS_COMPRESSION", line 214
ORA-06512: at line 9

With SQL Trace enabled I see the following in the trace file.

PARSING IN CURSOR #139655103675152 len=64 dep=1 uid=37 oct=3 lid=37 tim=1357852093619559 hv=814796271 ad='67de3b40' sqlid='g6pggnss91mgg'
SELECT count(*) FROM user_tablespaces WHERE tablespace_name= :b1
END OF STMT
...
BINDS #139655103675152:
 Bind#0
  ...
  value="SCRATCH"

Even though the temporary segments are created in the same schema as the object being analysed my own account also needs a quota on the scratch tablespace. No segments are created in my schema but I need the quota in order to get past the check on “user_tablespaces”.

alter user nj quota unlimited on scratch;

Let’s try again:

@orcl> @dbms_comp acme accs scratch 200000
declare
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.PRVT_COMPRESSION", line 776
ORA-06512: at "SYS.DBMS_COMPRESSION", line 214
ORA-06512: at line 9

I couldn’t find anything about this in M.O.S or find any evidence from SQL Trace. However the power of trial and error tracked the missing privilege down to “ANALYZE ANY” (I could have granted OEM_MONITOR to provide this privilege but we’re all about the principle of least privilege today).

grant ANALYZE ANY to NJ_DBA;

Let’s try again… again:

@orcl> @dbms_comp acme accs scratch 200000
Block count compressed = 1612
Block count uncompressed = 1632
Row count per block compressed = 123
Row count per block uncompressed = 121
Compression ratio = 1 to 1
Compression % benefit = 1.2

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.82

Sadly my table is not going to benefit from compression but it’s a success nonetheless. So the full list of required privileges is:

grant CREATE SESSION to NJ_DBA;
grant ANALYZE ANY to NJ_DBA;
grant CREATE ANY TABLE to NJ_DBA;
grant DROP ANY TABLE to NJ_DBA;
grant SELECT ANY TABLE to NJ_DBA;

You also need a quota for your schema user and an unnecessary quota for the executing user.

If you use VPD then don’t forget to take that into account.

And one last point, the temporary tables are created in thje target schema and the names are not session/object specific so don’t try to run Compress Advisor calls in parallel:

@orcl> @dbms_comp acme accs scratch 200000
declare
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
ORA-06512: at "SYS.PRVT_COMPRESSION", line 776
ORA-06512: at "SYS.DBMS_COMPRESSION", line 214
ORA-06512: at line 9