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.

Protect single instance with GI (without RAC-One-Node)

This is my first post since I meet Martin Nash in London 2 years ago on a Storage seminar with James Morle. He invited me on ORAganism blog and since then I wanted to start blogging but didn’t take the time to do so.
I spent one year working as a Consultant for a company called Digora where I have had great time and learnt a lot with all the different consultants, Laurent Leturget made me felt guilty not to blog ( laurent-leturgez.com ) !

Anyway, I discovered lately that it is now really simple to protect a single instance with “Cold failover” technique  through the Grid Infrastructure. Before discovering this technique, it was not this easy. There was an article on Oracle blogs talking about an equivalent technique :
https://blogs.oracle.com/xpsoluxdb/entry/clusterware_11gr2_setting_up_an_activepassive_failover_configuration

Compared to this post, the new technique can’t be much easier, there are 2 commands :

- Check the current properties of your database - dbtest here
 crsctl status resource ora.dbtest.db -p
- Change the placement to FAVORED for your database
 crsctl modify resource ora.dbtest.db -attr "PLACEMENT=favored"
- Change the list of HOSTs that can receive the instance of your database (here host1 or host2)
 crsctl modify resource ora.dbtest.db -attr "HOSTING_MEMBERS=host1 host2"
- Check back the new properties of your database - dbtest here
 crsctl status resource ora.dbtest.db -p

If you want your client connection to be handle the failover automatically and the SCAN listener to be aware of the host change, you need to modify your remote_listener parameter so that it points to the SCAN address of our GI setup

sqlplus / as sysdba 
alter system set remote_listener="host-scan:1521" scope=both;

That’s it !!

You now have a single-instance database that is protected again hardware failure. So next question is why using this technique when you can use the RAC-One Node and what are the differences ?

Reason – Cost
This is no additional cost and no extra licence compared to the RAC-One Node solution and moreover, you can use this technique with a Standard Edition database.

– Difference 1 – No RAC feature

Compared to the RAC-One Node solution, the database cannot failover smoothly to the other host (online failover). When you use RAC-One Node, if you choose in advance that you want to failover to another host (host2), you can use the following command :

srvctl relocate database -d dbtest -n host2 -v

When you launch this command, while they are some active transactions on the first host, the instance will be up and one second instance will start on the future host, so you are in the case of RAC features since both instances are up at the time, that’s why we have 2 redolog threads and 2 undo on RAC-One Node configuration.

If you want to learn more on RAC One Node, Marcin Przepiorowski has a serie on the subject (http://oracleprof.blogspot.fr/2009/12/oracle-rac-one-node-part-1.html)

If you try to use the relocate command with “poor’s man” failover solution, you’ll get this error :
   PRCD-1027 : Failed to retrieve database orcltest
   PRCD-1146 : Database orcltest is not a RAC One Node database

– Difference 2 – No native support in Cloud Control 12c

If you want to add this database to your beloved Cloud Control, you will need to add a wee extra script in case of failover so that CC12c can know it has to connect to the new host, here is an example below. If needed I wrote a script which handle this automatically (ask for it).

– Step 1 : create blackout (better but not compulsary)

emcli create_blackout -name="relocating active passive targets" -add_targets=${DB_NAME}_${INSTANCE_NAME}:oracle_database -schedule="frequency:once;duration:0:30" -reason="DB relocate"

– Step 2 – relocate the target from host1 to host2

emcli relocate_targets -src_agent=${SERVER_SOURCE}:3872 -dest_agent=${SERVER_DEST}:3872 -target_name=${DB_NAME}_${INSTANCE_NAME} -target_type=oracle_database -copy_from_src -force=yes -changed_param=MachineName:${VIP_NAME}

– Step 3 : stop blackout

emcli stop_blackout -name="relocating active passive targets"

– Difference 3 – No “Omotion like” relocation

At the beginning, RAC-One Node was named “Omotion“, it could be used almost as “Vmotion” from Vsphere. In the configuration using the Grid Infrastructure, you can only failover if you shutdown the host or shutdown the instance down on host1 and start it again on the second host :

-- From Host 1
srvctl stop database -d dbtest
-- From Host 2
srvctl start database -d dbtest -n host2

You are now ready to test this out !! This is amazing how Oracle handle this configuration, even if you delete the spfile from host1, the GI will recreate it automatically.

Hope this helps. I did not find any blog article talking about this type of configuration so this is my first contribution to the Oracle community from which I learned so much.