Grants to retrieve SQL_MONITOR

A quick post as I have been struggling to get the full list of object level grants to be able to retrieve the output of DBMS_SQLTUNE.REPORT_SQL_MONITOR.

1 – How to investigate the annoying ORA-00942: table or view does not exist error ?

Set the event 942 at the instance or PDB level :

SQL> alter system set events '942 trace name errorstack level 3';

When you get the error, open the tracefile and you’ll get the problematic SQL.

2 – So here is the list of GRANTS needed to call with a wee testcase : SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>’dhpn35zupm8ck’, type=>’EM’, report_level => ‘ALL’) as report FROM dual;

PS: Tested on 12.1.0.2 on linux.


create user test_sqlmon identified by test_sqlmon;

-- Minimum 
grant create session                          TO test_sqlmon;

-- Extra grants
GRANT SELECT ON GV_$SQL                       TO test_sqlmon;
GRANT SELECT ON GV_$SQL_OPTIMIZER_ENV         TO test_sqlmon;
GRANT SELECT ON GV_$SYS_OPTIMIZER_ENV         TO test_sqlmon;
GRANT SELECT ON V_$SYS_OPTIMIZER_ENV         TO test_sqlmon;
GRANT SELECT ON GV_$SQL_MONITOR               TO test_sqlmon;
GRANT SELECT ON V_$SQL_MONITOR               TO test_sqlmon;
GRANT SELECT ON GV_$SQL_MONITOR_STATNAME      TO test_sqlmon;
GRANT SELECT ON V_$SQL_MONITOR_STATNAME       TO test_sqlmon;
GRANT SELECT ON GV_$SQL_PLAN_MONITOR          TO test_sqlmon;
GRANT SELECT ON V_$SQL_PLAN_MONITOR            TO test_sqlmon;
GRANT SELECT ON GV_$SESSION_LONGOPS           TO test_sqlmon;
GRANT SELECT ON GV_$INSTANCE                  TO test_sqlmon;
GRANT SELECT ON V_$INSTANCE                  TO test_sqlmon;
GRANT SELECT ON GV_$DATABASE                  TO test_sqlmon;
GRANT SELECT ON V_$DATABASE                  TO test_sqlmon;
GRANT SELECT ON GV_$ACTIVE_SESSION_HISTORY    TO test_sqlmon;
GRANT SELECT ON V_$ACTIVE_SESSION_HISTORY    TO test_sqlmon;
GRANT SELECT ON GV_$TIMER                     TO test_sqlmon;
GRANT SELECT ON V_$TIMER                     TO test_sqlmon;
GRANT SELECT ON GV_$SQL_PLAN                  TO test_sqlmon;
GRANT SELECT ON V_$SQL_PLAN                   TO test_sqlmon;
GRANT SELECT ON GV_$ASH_INFO                  TO test_sqlmon;
GRANT SELECT ON DBA_PROCEDURES                TO test_sqlmon;
GRANT SELECT ON GV_$SQLAREA_PLAN_HASH		      TO test_sqlmon;
GRANT SELECT ON V_$SESSION                    TO test_sqlmon;
GRANT SELECT ON V_$PARAMETER                    TO test_sqlmon;
GRANT SELECT ON V_$PROCESS                    TO test_sqlmon;

==> Connect with the "test_sqlmon" user, try => you'll get something to save to a file or inside a table for further analysis !

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>'dhpn35zupm8ck', type=>'EM', report_level => 'ALL') as report  FROM dual;

****/***

-- cleanup
DROP user test_sqlmon cascade;

Hope this helps.

3 – Disable the event

SQL> alter system set events '942 trace name errorstack off';

Oracle 12c PDB snapshot copy on regular filesystem

A month ago I was at Paris Oracle meetup with 4 great presentations from 2 cool Swiss guys that I knew from their really good blogs : http://www.meetup.com/parisoracle/events/224181879/
– Ludovico Caldara
– Franck Pachot

After the first presentation by Ludovico Caldara about “Oracle ACFS and Oracle Database: the perfect match ?”, I almost ruined the presentation when I said that you don’t need ACFS, Direct-NFS (CloneDB) or other Copy-On-Write filesystem to do this as it is available on the latest Oracle 12c.. working on “conventional” filesystem.

So I promise to write about it.. It is a quite simple post, this is just to “advertise” the feature. I didn’t had the time to look more into it when I discover it. But I am sure, some people will look more at it.

Test : Oracle 12.1.0.2 on RedHat 6.6 (filesystem ext4).
Pre-requisite : CLONEDB initialization parameter is set to TRUE

@pdb
Show PDB information

 CON_ID DBID CON_UID GUID NAME OPEN_MODE RES OPEN_TIME CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
---------- ---------- ---------- -------------------------------- ------------------------------ ---------- --- --------------------------------------------------------------------------- ---------- ---------- ---------- -------- ----------------------
 2 3587124922 3587124922 0ED34F67D0F81298E053ED22070A751B PDB$SEED READ ONLY NO 30-APR-15 03.41.46.723 PM +02:00 1594330 838860800 8192 ENABLED 0
 3 2362709155 2362709155 0ED37F0B74A72742E053ED22070A2DCA DB12C_PDB1 READ WRITE NO 30-APR-15 03.41.47.153 PM +02:00 1792203 2116812800 8192 ENABLED 0
 

Let’s use the PDB called DB12C_PDB1, and create a snapshot from it.

First get a consistent (Closed) PDB to use a source. Then re-opened it as Read-Only.

SQL> alter pluggable database exit close; 

SQL> alter pluggable database DB12C_PDB1 open read only; 

Now, let’s create the directory that will host the sparsed files for the snapshot copies PDB.


SQL> ! mkdir -p /u01/app/oracle/oradata/DB12C/DB12C_JCD1

Here we go, we create the snapshot copy !


SQL> create pluggable database DB12C_JCD1 from DB12C_PDB1 snapshot copy file_name_convert=('/u01/app/oracle/oradata/DB12C/DB12C_PDB1','/u01/app/oracle/oradata/DB12C/DB12C_JCD1');

Pluggable database created.

Then we open the target PDB.

SQL> alter pluggable database DB12C_JCD1 open;

Pluggable database altered.

SQL> alter session set container=DB12C_JCD1;

Now let’s check the datafiles on the ext4 filesystem and check the space actually used (sparse files).

SQL> @datafile %

Show Tablespace information for TBS matching %%%

 ID# filename                                                                   Tablespace              TotalMB     UsedMB     FreeMB  MaxsizeMB     Blocks    Extents Status     AutoExt       Incr
---- -------------------------------------------------------------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------- ----------
  81 /u01/app/oracle/oradata/DB12C/DB12C_JCD1/APPDATA01.DBF                     APPDATA                     100          1         99      32767      12800          1 A          Y            65536
  82 /u01/app/oracle/oradata/DB12C/DB12C_JCD1/APPDATA02.DBF                     APPDATA                     100          1         99      32767      12800          1 A          Y            65536
  83 /u01/app/oracle/oradata/DB12C/DB12C_JCD1/APPDATA03.DBF                     APPDATA                     100          1         99      32767      12800          1 A          Y            65536
  84 /u01/app/oracle/oradata/DB12C/DB12C_JCD1/APPINDEXES01.DBF                  APPINDEXES                  100          1         99      32767      12800          1 A          Y            65536
  85 /u01/app/oracle/oradata/DB12C/DB12C_JCD1/APPINDEXES02.DBF                  APPINDEXES                  100          1         99      32767      12800          1 A          Y            65536
  74 /u01/app/oracle/oradata/DB12C/DB12C_JCD1/sysaux01.dbf                      SYSAUX                      580        543         36      32767      74240          1 A          Y             1280
  73 /u01/app/oracle/oradata/DB12C/DB12C_JCD1/system01.dbf                      SYSTEM                      410        405          4      32767      52480          2 A          Y             1280
  75 /u01/app/oracle/oradata/DB12C/DB12C_JCD1/DB12C_PDB1_users01.dbf            USERS                         8          7          1      32767       1120          2 A          Y              160

13 rows selected.

Show Temporary Tablespace information for TBS matching %%%

 ID# filename                                                                         Tablespace              TotalMB     UsedMB     FreeMB  MaxsizeMB     Blocks    Extents Status     AutoExt       Incr Used
---- -------------------------------------------------------------------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------- ---------- ------------------------------
   4 /u01/app/oracle/oradata/DB12C/DB12C_JCD1/pdbseed_temp01DB12C.dbf        TEMP                         20         20          0      32767       2560          1 ONLINE     Y               80 |####################|

Let’s check the “actual” space used on the filesystem using “du”. You’ll notice that only a few kilobytes are used on the filesystem. Of course, it will increase as you modify your snapshot PDB !

SQL> ! du -k /u01/app/oracle/oradata/DB12C/DB12C_JCD1/*

16      /u01/app/oracle/oradata/DB12C/DB12C_JCD1/APPDATA01.DBF
16      /u01/app/oracle/oradata/DB12C/DB12C_JCD1/APPDATA02.DBF
16      /u01/app/oracle/oradata/DB12C/DB12C_JCD1/APPDATA03.DBF
16      /u01/app/oracle/oradata/DB12C/DB12C_JCD1/APPINDEXES01.DBF
16      /u01/app/oracle/oradata/DB12C/DB12C_JCD1/APPINDEXES02.DBF
16      /u01/app/oracle/oradata/DB12C/DB12C_JCD1/DB12C_PDB1_users01.dbf
56      /u01/app/oracle/oradata/DB12C/DB12C_JCD1/pdbseed_temp01DB12C.dbf
16      /u01/app/oracle/oradata/DB12C/DB12C_JCD1/sysaux01.dbf
172     /u01/app/oracle/oradata/DB12C/DB12C_JCD1/system01.dbf

Now let’s check the real space used on the original PDB.

SQL> alter session set container=DB12C_PDB1;

SQL> @datafile %

Show Tablespace information for TBS matching %%%

 ID# filename                                                                   Tablespace              TotalMB     UsedMB     FreeMB  MaxsizeMB     Blocks    Extents Status     AutoExt       Incr
---- -------------------------------------------------------------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------- ----------
  16 /u01/app/oracle/oradata/DB12C/DB12C_PDB1/APPDATA01.DBF                     APPDATA                     100          1         99      32767      12800          1 A          Y            65536
  17 /u01/app/oracle/oradata/DB12C/DB12C_PDB1/APPDATA02.DBF                     APPDATA                     100          1         99      32767      12800          1 A          Y            65536
  18 /u01/app/oracle/oradata/DB12C/DB12C_PDB1/APPDATA03.DBF                     APPDATA                     100          1         99      32767      12800          1 A          Y            65536
  19 /u01/app/oracle/oradata/DB12C/DB12C_PDB1/APPINDEXES01.DBF                  APPINDEXES                  100          1         99      32767      12800          1 A          Y            65536
  20 /u01/app/oracle/oradata/DB12C/DB12C_PDB1/APPINDEXES02.DBF                  APPINDEXES                  100          1         99      32767      12800          1 A          Y            65536
   9 /u01/app/oracle/oradata/DB12C/DB12C_PDB1/sysaux01.dbf                      SYSAUX                      580        543         36      32767      74240          1 A          Y             1280
   8 /u01/app/oracle/oradata/DB12C/DB12C_PDB1/system01.dbf                      SYSTEM                      410        405          4      32767      52480          2 A          Y             1280
  10 /u01/app/oracle/oradata/DB12C/DB12C_PDB1/DB12C_PDB1_users01.dbf            USERS                         8          7          1      32767       1120          2 A          Y              160

13 rows selected.

Show Temporary Tablespace information for TBS matching %%%

 ID# filename                                                                Tablespace              TotalMB     UsedMB     FreeMB  MaxsizeMB     Blocks    Extents Status     AutoExt       Incr Used
---- ----------------------------------------------------------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------- ---------- ------------------------------
   3 /u01/app/oracle/oradata/DB12C/DB12C_PDB1/pdbseed_temp01DB12C.dbf        TEMP                         20         20          0      32767       2560          1 OFFLINE    Y               80 |####################|

And on the filesystem.

! du -k /u01/app/oracle/oradata/DB12C/DB12C_PDB1/*
102412  /u01/app/oracle/oradata/DB12C/DB12C_PDB1/APPDATA01.DBF
102412  /u01/app/oracle/oradata/DB12C/DB12C_PDB1/APPDATA02.DBF
102412  /u01/app/oracle/oradata/DB12C/DB12C_PDB1/APPDATA03.DBF
102412  /u01/app/oracle/oradata/DB12C/DB12C_PDB1/APPINDEXES01.DBF
102412  /u01/app/oracle/oradata/DB12C/DB12C_PDB1/APPINDEXES02.DBF
8968    /u01/app/oracle/oradata/DB12C/DB12C_PDB1/DB12C_PDB1_users01.dbf
1024    /u01/app/oracle/oradata/DB12C/DB12C_PDB1/pdbseed_temp01DB12C.dbf
593932  /u01/app/oracle/oradata/DB12C/DB12C_PDB1/sysaux01.dbf
419852  /u01/app/oracle/oradata/DB12C/DB12C_PDB1/system01.dbf

That’s it for the demo, really simple. I am sure Ludovico Caldara will be eager to investigate more on the internals of this nice feature that not many people seem to know about.

Hope this helps.

Slow JDBC Connections, STRACE and Random Numbers

I was involved in an interesting problem in the office recently. The problem is already documented in other posts such as:

Oracle 11g JDBC driver hangs blocked by /dev/random – entropy pool empty
Oracle JDBC Intermittent Connection Issue
MOS note “Adbldxml.pl Hangs After Upgrade To 11.2.0.3 (Doc ID 1585759.1)”

Essentially the problem is that /dev/random will block when there is insuffient entropy and JDBC uses /dev/random when negotiating a connection. I’ll not go back over information from the web pages above, please have a quick read of one of them.

What I wanted to do was demonstrate the issue using a Java program from the above MOS note and show evidence of the issue and delays. All the program does is connect to a database using a JDBC thin driver and “SELECT USER FROM dual”, I’ll run it three times as below:

time java oraConn &
time java oraConn &
time java oraConn &

and this is the output (“NJ” is my database account name):

NJ
real    0m2.001s
user    0m1.093s
sys     0m0.094s

NJ
real    0m39.051s
user    0m1.163s
sys     0m0.089s

NJ
real    0m44.316s
user    0m1.162s
sys     0m0.087s

2 seconds for the first connection and 39 and 44 seconds for the others! Not good. We can use “strace” to find out what the processes are doing during these delays.

strace -t -o runjv1.trc -f java oraConn &
strace -t -o runjv2.trc -f java oraConn &
strace -t -o runjv3.trc -f java oraConn &

This is what I see in my strace output for one of the delayed sessions, repeated timeouts, many more than displayed below:

tail -f runjv1.trc
...
2755  21:02:02 futex(0x7f1c440b1054, FUTEX_WAIT_BITSET_PRIVATE, 1, {3390, 520708960}, ffffffff) = -1 ETIMEDOUT (Connection timed out)
2755  21:02:02 futex(0x7f1c440b1028, FUTEX_WAKE_PRIVATE, 1) = 0
2755  21:02:02 futex(0x7f1c440b1054, FUTEX_WAIT_BITSET_PRIVATE, 1, {3390, 571177374}, ffffffff) = -1 ETIMEDOUT (Connection timed out)
2755  21:02:02 futex(0x7f1c440b1028, FUTEX_WAKE_PRIVATE, 1) = 0
2755  21:02:02 futex(0x7f1c440b1054, FUTEX_WAIT_BITSET_PRIVATE, 1, {3390, 621626926}, ffffffff) = -1 ETIMEDOUT (Connection timed out)
2755  21:02:02 futex(0x7f1c440b1028, FUTEX_WAKE_PRIVATE, 1) = 0
2755  21:02:02 futex(0x7f1c440b1054, FUTEX_WAIT_BITSET_PRIVATE, 1, {3390, 672072270}, ffffffff) = -1 ETIMEDOUT (Connection timed out)
2755  21:02:02 futex(0x7f1c440b1028, FUTEX_WAKE_PRIVATE, 1) = 0
2755  21:02:02 futex(0x7f1c440b1054, FUTEX_WAIT_BITSET_PRIVATE, 1, {3390, 722524322}, ffffffff) = -1 ETIMEDOUT (Connection timed out)
2755  21:02:02 futex(0x7f1c440b1028, FUTEX_WAKE_PRIVATE, 1) = 0
2755  21:02:02 futex(0x7f1c440b1054, FUTEX_WAIT_BITSET_PRIVATE, 1, {3390, 772872653}, ffffffff) = -1 ETIMEDOUT (Connection timed out)
2755  21:02:02 futex(0x7f1c440b1028, FUTEX_WAKE_PRIVATE, 1) = 0
2755  21:02:02 futex(0x7f1c440b1054, FUTEX_WAIT_BITSET_PRIVATE, 1, {3390, 823298750}, ffffffff) = -1 ETIMEDOUT (Connection timed out)
...

If I scroll up to the start of the block of timeouts I see this:

2734  21:01:30 read(15,  <unfinished ...>
2755  21:01:30 <... futex resumed> )    = -1 ETIMEDOUT (Connection timed out)
2755  21:01:30 futex(0x7f1c440b1028, FUTEX_WAKE_PRIVATE, 1) = 0
2755  21:01:30 futex(0x7f1c440b1054, FUTEX_WAIT_BITSET_PRIVATE, 1, {3359, 26945244}, ffffffff) = -1 ETIMEDOUT (Connection timed out)

We see the “read” on file descriptor 15 executed by PID 2734 on line 1 above. What are we reading from when reading descriptor 15? Further up the trace file we see:

2734  21:01:30 open("/dev/random", O_RDONLY) = 15
2734  21:01:30 fstat(15, {st_mode=S_IFCHR|0666, st_rdev=makedev(1, 8), ...}) = 0
2734  21:01:30 fcntl(15, F_GETFD)       = 0
2734  21:01:30 fcntl(15, F_SETFD, FD_CLOEXEC) = 0

“/dev/random”! Nice, this agrees with the referenced posts.

We can monitor the available entropy by running this shell snippet during a re-test:

while [ 1 ];do
date
cat /proc/sys/kernel/random/entropy_avail
sleep 1
done

And this is what we see, the available entropy drops causing reads on /dev/random to block. There are a few solutions in the referenced blog and MOS notes.

Wed Aug 19 21:28:07 BST 2015
161
Wed Aug 19 21:28:08 BST 2015
174
Wed Aug 19 21:28:09 BST 2015
134
Wed Aug 19 21:28:10 BST 2015
38
Wed Aug 19 21:28:11 BST 2015
50
Wed Aug 19 21:28:13 BST 2015
61
Wed Aug 19 21:28:14 BST 2015
62
Wed Aug 19 21:28:15 BST 2015
8
Wed Aug 19 21:28:16 BST 2015
21
Wed Aug 19 21:28:17 BST 2015
22
Wed Aug 19 21:28:18 BST 2015
35
Wed Aug 19 21:28:19 BST 2015
37
Wed Aug 19 21:28:20 BST 2015
49
Wed Aug 19 21:28:21 BST 2015
63
Wed Aug 19 21:28:22 BST 2015
0
Wed Aug 19 21:28:23 BST 2015
1
Wed Aug 19 21:28:24 BST 2015
17
Wed Aug 19 21:28:25 BST 2015
36
Wed Aug 19 21:28:26 BST 2015
37

I fully appreciate I am going over work covered by others but I thought the “strace” analysis added to other posts. Also it doesn’t hurt to highlight this issue again as I’m really surprised this is not more widely known as the use of virtual machines is so prevalent these days. I presume connection pools hide this from us most of the time.

Non-working logon trigger on Data Guard standby database

Today I did something very dumb when working with a vendor to trace and issue and rendered our Data Guard physical standby database unusable. I added an AFTER LOGON ON DATABASE trigger which collected information about a session and wrote it to a table. You can see the problem already: standby databases are not writable and so this is not going to work.

Very soon users connecting to the standby were complaining:

ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
ORA-06512: at line 2
ORA-02063: preceding 3 lines from SERVER_RO
00604. 00000 -"error occurred at recursive SQL level %s"
*Cause:An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action: If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.

Realising my mistake I dropped the trigger but this didn’t make the problem go away on the standby.

A quick look in the alert log on the primary and there was a logon failure related to Data Guard:

Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
PING[ARCl]: Heartbeat failed to connect to standby 'SERVER2'. Error is 16191.

The secondary’s alert log didn’t have anything interesting in it.

I checked the SYS passwords on both nodes in case an underlying problem had suddenly occurred but all was ok.

MOS to the rescue! I checked REDO_TRANSPORT_USER (we don’t use it) and SEC_CASE_SENSITIVE_LOGON (we do use it but it was working ok before so I didn’t want to change it).

In this situation tracing is your friend and MOS note 1368170.1, Troubleshooting ORA-16191 and ORA-1017/ORA-1031 in Data Guard Log Transport Services or Data Guard Broker, recommends tracing the error on the primary.

alter system set events '16191 trace name errorstack level 10';

This generated a trace file, /ora/diag/rdbms/server1/SERVER1/trace/SERVER1_arc1_13924.trc, containing:

*** 2015-08-26 12:26:56.914 939 krsu.c
Error 16191 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'SERVER2'
Error 16191 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'SERVER2'
*** 2015-08-26 12:26:56.914 2917 krsi.c
krsi_dst_fail: dest:2 err:16191 force:0 blast:1
kcrrwkx: unknown error:16191

*** 2015-08-26 12:27:30.677
OCISessionBegin failed -1
.. Detailed OCI error val is 604 and errmsg is 'ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
ORA-06512: at line 2
'
OCISessionBegin failed. Error -1
.. Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied
'

*** 2015-08-26 12:27:31.737
OCISessionBegin failed. Error -1
.. Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied
'
OCISessionBegin failed. Error -1
.. Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied
'
*** 2015-08-26 12:27:31.767 4338 krsh.c
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------

Enough tracing so trace off:

alter system set events '16191 trace name context off';

Looking at the output we have a big clue here, as it looks like the error the users were getting:

ORA-16000: database open for read-only access

Why are we still getting that when I’ve dropped the trigger on the primary database? It is immediately apparent that I have broken Data Guard and my drop is in a log file awaiting shipping to the standby. This is a catch 22: I can’t apply the log because the trigger won’t let Data Guard authenticate and Data Guard can’t authenticate because of the presence of the trigger.

Two questions sprang to my mind:

  1. Can I disable the trigger?
  2. Why is the trigger affecting the SYS user?

I can’t workaround the trigger in any documented way because any attempt to do so on the physical standby is prevented by its read-only status, for example:

SQL> grant ADMINISTER DATABASE TRIGGER to appuser;
grant ADMINISTER DATABASE TRIGGER to appuser
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

Here user SYS is connecting with a password not “as sysdba” and so we are hitting the logon trigger.

Luckily help is at hand with a system parameter I can change:

alter system set "_system_trig_enabled"=FALSE;

My fellow York resident, Pete Finnigan, has more detail on this here.

I can then restart the recovery with:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

And of course not forgetting to re-enable system triggers:

alter system set "_system_trig_enabled"=TRUE;

It should be pointed out that this method is nice because it doesn’t involve restarting the standby and therefore interrupting any running reports but if you don’t like undocumented parameters or you have other system triggers you depend on you can instead:

  • Shut down the standby.
  • Restart the standby in MOUNT mode. This means that any triggers will not fire.
  • Start the recovery.
  • If the trigger has been dropped on the primary it should replicate to the secondary.
  • Open the database.

UKOUG Tech14 Slides: Testing Jumbo Frames for RAC

Just a quick post to link to the slides from my presentation at the UKOUG Tech14 conference. The slides do not appear to be visible on the Super Sunday site, hence this post. The presentation was called “Testing Jumbo Frames for RAC”, the emphasis as much on the testing as on Jumbo Frames.

Abstract:

A discussion on the usage of Jumbo Frames for the RAC interconnect. We’ll cover background information and how this can be tested at the Oracle level and in the Operating system. We’ll also discuss how testing initially failed and why it’s interesting.

This is a topic that enters the realm of network and Unix admin’s but the presentation is aimed at DBAs who want to know more and want to know how to use Operating System tools to investigate further.

Slides:

http://www.osumo.co.uk/presentations/Jumbo%20Frames-Tech14-Public.pdf

jumbo-comic2

The conference itself was another success with particular highlights for me being James Morle talking about SAN replication vs DataGuard, Ludovico Caldara talking about PDBs with MAA, Richard Foote because I was like a silly little fanboi and Bryn Llewellyn purely for the way he weaves the English language in to something beautiful on the ears. All of my programs will have “prologues” and “epilogues” from now on, “headers” and “footers” are so passe🙂

Equal to the presentations was the social side of things too. I do enjoy hanging around in pubs and talking shop.

ORA-64359: INMEMORY clause may not be specified for virtual columns

The title above gives you immediate knowledge of the end result of this post. If you want to know the whys and wherefores then by all means read on.

I was on a call yesterday listening to a presentation on Database 12c new features. The In-Memory Column Store received a lot of interest, as did the new JSON functionality.

One person on the call asked an interesting question, it was something along the lines of:

“If we have a table containing a JSON document can we use the In-Memory Column Store to optimise reports on the JSON attributes”

After some discussion it was decided that the best way to do that is to store the required JSON attributes in dedicated regular columns and report on those. After the call I thought about this some more and wondered if we can:

a) Expose JSON attributes as virtual columns and…
b) Utilise the In-Memory Column Store to report on those virtual columns

I thought these tests were worth executing. Here they are:

a) Can we expose JSON attributes as virtual columns

In order to set up the test I create a table with a JSON column and a virtual column using the function JSON_VALUE to retrieve a scalar value for an attribute called “Name”.

SQL> CREATE TABLE json_docs
(   id          NUMBER (10) NOT NULL
,   date_loaded DATE
,   doc         CLOB
    CONSTRAINT ensure_json CHECK (doc IS JSON)
,   doc_name AS (JSON_VALUE(doc,'$.Name'))
);

Table created.

SQL> desc json_docs
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(10)
 DATE_LOADED                                        DATE
 DOC                                                CLOB
 DOC_NAME                                           VARCHAR2(4000)

Looks promising. Let’s add some data.

insert into json_docs (id, date_loaded, doc)
values (1,sysdate,'{"Name": "Neil", "Email": "neil@email.com", "Job": "DBA"}');
insert into json_docs (id, date_loaded, doc)
values (2,sysdate,'{"name": "Bob", "Email": "bobl@email.com", "Job": "DBA"}');
commit;

set lines 120
column doc format a60
column doc_name format a10
select * from json_docs;

 ID DATE_LOAD DOC                                                          DOC_NAME
--- --------- ------------------------------------------------------------ --------
  1 13-NOV-14 {"Name": "Neil", "Email": "neil@email.com", "Job": "DBA"}    Neil
  2 13-NOV-14 {"name": "Bob", "Email": "bobl@email.com", "Job": "DBA"}

And there we have it. When the attribute “Name” is present at the top level of the JSON document then we can show it as a dedicated virtual column. I’m not suggesting this is a good idea but… it works.

b) Can we utilise the In-Memory Column Store to report on virtual columns

First I’ll add more data to the JSON_DOCS table.

delete json_docs;
insert into json_docs (id,date_loaded,doc)
select rownum, sysdate, '{"Name": "'||object_name||'", "Type": "'||object_type||'"}'
from all_objects
where rownum <= 500;
commit;

And set the table to be INMEMORY, excluding the JSON document column.

SQL> show parameter inmemory_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_size                        big integer 252M

SQL> ALTER TABLE json_docs INMEMORY NO INMEMORY(doc);

Next query the data to kick of a load to the “In-Memory Area”

select count(*) from json_docs;
column segment_name format a30
select segment_name,populate_status,bytes_not_populated from v$im_segments order by 1;

SEGMENT_NAME                   POPULATE_ BYTES_NOT_POPULATED
------------------------------ --------- -------------------
JSON_DOCS                      COMPLETED                   0

That looks promising. So let’s check which columns are cached.

select TABLE_NAME,COLUMN_NAME,INMEMORY_COMPRESSION from V$IM_COLUMN_LEVEL;

TABLE_NAME      COLUMN_NAME      INMEMORY_COMPRESSION
--------------- ---------------- ---------------------
JSON_DOCS       ID               DEFAULT
JSON_DOCS       DATE_LOADED      DEFAULT
JSON_DOCS       DOC              NO INMEMORY

Not the virtual column… Can we force it:

ALTER TABLE json_docs INMEMORY (date_loaded, doc_name) NO INMEMORY (doc) ;

*
ERROR at line 1:
ORA-64359: INMEMORY clause may not be specified for virtual columns

Nope. And probably for good reasons.

Spreading Out Data With MINIMIZE RECORDS_PER_BLOCK

This post is part of a series on why sometimes we don’t need to pack data in, we need to spread it out. In the previous post I discussed some old-school methods to achieve this. This post talks about a technique that has been available for many Oracle versions so is also pretty old school but I thought it was worth a post all to itself. The technique uses the ALTER TABLE clause “MINIMIZE RECORDS_PER_BLOCK”.

Documentation

MINIMIZE RECORDS_PER_BLOCK

I have included a link to Oracle documentation above but one of the first things to highlight is this feature is light on official documentation. In fact the above page and a brief reference in the SQL Language Quick Reference are just about all there is. Here is a quote from the above link, the emphasis is mine.

“The records_per_block_clause lets you specify whether Oracle Database restricts the number of records that can be stored in a block. This clause ensures that any bitmap indexes subsequently created on the table will be as compressed as possible.”

There is no example in the official documentation but there is a very nice example on Richard Foote’s blog titled “Bitmap Indexes & Minimize Records_Per_Block (Little Wonder)”, and he works for Oracle so perhaps it counts as official documentation!

In the documentation above we have a mention of “restricts the number of records that can be stored in a block” but no clear guidance that it can be used for the reason we desire, to spread out data. If I can’t find what I want in the Oracle documentation my next port of call is My Oracle Support and there is a note describing how we might use this clause for our purpose.

Oracle Performance Diagnostic Guide (OPDG) [ID 390374.1]

In essence the note describes a “hot block” issue and a solution which is to spread data out. Suggested methods are to utilise PCTFREE or the table option, MINIMIZE RECORDS_PER_BLOCK. The note also goes through steps to highlight how to achieve our goal. Which leads us on to the next section.

How To [Mis]use MINIMIZE RECORDS_PER_BLOCK To Spread Out Data

The basic set of steps when using this clause to spread out data is:

  1. Temporarily remove data from the table, typically with CTAS or Datapump/Export followed by truncate or delete
  2. Insert the desired number of dummy records any data block should hold
  3. Restrict the number of records that can be stored in any block to the maximum number currently held
  4. Delete the dummy rows
  5. Reinstate the application data

Here is an example.

In the first post in the series I introduced the PROCSTATE table with all rows of data in a single block.

select dbms_rowid.rowid_block_number(rowid) blockno
,      count(*)
from procstate
group by dbms_rowid.rowid_block_number(rowid);
 
   BLOCKNO   COUNT(*)
---------- ----------
    239004         12

First let’s copy the data elsewhere and truncate the table.

SQL> create table procstate_store
  2  as select * from procstate;

Table created.

SQL> truncate table procstate;

Table truncated.

Next we insert a dummy row and restrict the number of rows any block will hold.

SQL> insert into procstate
  2  select * from procstate_store
  3  where rownum = 1;

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from procstate;

  COUNT(*)
----------
         1

SQL> alter table procstate minimize records_per_block;

Table altered.

Finally we delete the dummy row and re-insert the original data.

SQL> truncate table procstate;

Table truncated.

SQL> insert into procstate
  2  select * from procstate_store;

12 rows created.

SQL> commit;

Commit complete.

And we should have only one row per block right? Wrong!

SQL> select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) blockno, count(*)
  2  from procstate group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid);

   BLOCKNO   COUNT(*)
---------- ----------
    278668          2
    278669          2
    278670          2
    278667          2
    278688          2
    278671          2

6 rows selected.

Two rows in each block… but that’s still a good result and using the same test as in the previous post I can see a huge reduction in contention when running my primitive test case. Original results on the left and new results with two rows per block on the right.

Contention  11g normal heap table chart-11g-mrpb

Under The Covers

In this section we’ll dig a bit deeper in to how this works and perhaps get some insight into why the example above resulted in two rows per block.

When MINIMIZE RECORDS_PER_BLOCK is used it manipulates a property in SYS.TAB$ in the SPARE1 column. This property is known as the Hakan Factor (no I don’t know why either but I do notice there is a Mr Hakan Jacobsson listed as an author of the Performance Tuning Guide and Data Warehousing Guide… who knows). Below is a query showing the Hakan Factor for a simple table stored in a tablespace using 8KB blocks.

select spare1 from sys.tab$ where obj# = 18013;

    SPARE1
----------
       736

The Hakan Factor is set by default for all heap tables, or more correctly, for all table segments of heap tables. Below is a table showing how it changes as block size changes. It makes sense that larger blocks can hold more rows.

BLOCK_SIZE  SPARE1
----------  ------
       4KB     364
       8KB     736
      16KB    1481
      32KB    2971

After a minimize operation with only a single row in a table it would be reasonable to expect SPARE1 to be set to “1”. So let’s check the value stored after a MINIMIZE operation on the PROCSTATE table.

    SPARE1
----------
     32769

This is because the MINIMIZE operation sets a flag in the 16th bit of the Hakan factor. We can see this using the BITAND SQL function in a query like the one below. This query uses BITAND to check if a specific bit is set in a number. So the increasing powers of 2 have been passed in. I have then used the LEAST() or GREATEST() functions to convert the result to a “1” or “0”.

select spare1
,      least(1,BITAND(spare1,32768)) c32k
,      least(1,BITAND(spare1,16384)) c16k
,      least(1,BITAND(spare1,8192)) c8k
,      least(1,BITAND(spare1,4096)) c4k
,      least(1,BITAND(spare1,2048)) c2k
,      least(1,BITAND(spare1,1024)) c1k
,      least(1,BITAND(spare1,512)) c512
,      least(1,BITAND(spare1,256)) c256
,      least(1,BITAND(spare1,128)) c128
,      least(1,BITAND(spare1,64)) c64
,      least(1,BITAND(spare1,32)) c32
,      least(1,BITAND(spare1,16)) c16
,      least(1,BITAND(spare1,8)) c8
,      least(1,BITAND(spare1,4)) c4
,      least(1,BITAND(spare1,2)) c2
,      greatest(0,BITAND(spare1,1)) c1
from sys.tab$
where obj# = (select obj# from sys.obj$
              where name = 'PROCSTATE');

-- After MINIMIZE with 1 row in PROCSTATE                                 ( decimal 1 )
SPARE1 C32K C16K  C8K  C4K  C2K  C1K C512 C256 C128  C64  C32  C16   C8   C4   C2   C1
------ ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
 32769    1    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1

-- After MINIMIZE with 2 rows in PROCSTATE                                ( decimal 1 )
 32769    1    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1

-- After MINIMIZE with 3 rows in PROCSTATE                                ( decimal 2 )
 32770    1    0    0    0    0    0    0    0    0    0    0    0    0    0    1    0

-- After MINIMIZE with 4 rows in PROCSTATE                                ( decimal 3 )
 32771    1    0    0    0    0    0    0    0    0    0    0    0    0    0    1    1

-- After MINIMIZE with 5 rows in PROCSTATE                                ( decimal 4 )
 32771    1    0    0    0    0    0    0    0    0    0    0    0    0    1    0    0

-- etc etc

Notice the 32K flag is set for all numbers and the lower bits specify the row limit. There is an anomaly for a single row and then the values lag one behind the desired row limit. For example with three rows in the table the Hakan factor has the MINIMIZE flag in the 16th bit and binary “10” (decimal 2) stored. So with a default Hakan factor of 736 the limit is actually set at 737 rows. I can only think that the anomaly where 1 row and 2 rows have the same value stored are down to this feature being in place to optimise bitmap indexes and the difference between one and two rows is irrelevant. Or perhaps storing a zero was ugly and a compromise was made, we’ll never know.

It is worth noting at this point that SPARE1 is a multi use column and a COMPRESS operation will set a flag in the 18th bit. I have not seen a case where the 13th – 15th bits are used. Perhaps they are left for future proofing MINIMIZE from block sizes above 32KB (should Oracle ever decide to implement them). Anyway, back on track…

When “MINIMIZE RECORDS_PER_BLOCK” is executed the Oracle process full scans the table and uses the undocumented SYS_OP_RPB() function to retrieve the row number of every row within its block. e.g.

select max(sys_op_rpb(rowid)) from procstate;

The SYS_OP_RPB() function returns the same result as the documented DBMS_ROWID.ROWID_ROW_NUMBER() function. Output from a table containing four records is below:

select rownum
, dbms_rowid.rowid_relative_fno(rowid) file_no
, dbms_rowid.rowid_block_number(rowid) blk_no
, dbms_rowid.rowid_row_number(rowid) row_no
, sys_op_rpb(rowid) row_rno2
from haktest;

    ROWNUM    FILE_NO     BLK_NO     ROW_NO   ROW_RNO2
---------- ---------- ---------- ---------- ----------
         1         11      18676          0          0
         2         11      18676          1          1
         3         11      18676          2          2
         4         11      18676          3          3

Notice the numbering starts at 0, this explains why the value stored in TAB$.SPARE1 is one less to our eyes than the actual limit. This is true of a block dump too, the numbering of rows in a data block starts at 0.

Finally, to return a table to its default setting the ALTER TABLE NOMINIMIZE clause is used.

Summary

In summary the MINIMIZE clause is not very well documented and definitely not very well exposed in the data dictionary. It is intended for use with bitmap indexes but can be also be used to spread data out in a similar way to PCTFREE but by managing the number of rows rather than amount of free space. I have used MINIMIZE in the past but now that I am older and less excitable I would probably stick to other methods of achieving my goals (e.g. PCTFREE or partitioning).

In the next post in the series we’ll look at some newer ways we can spread out data.