Remote debugging with SQL Developer

For all of us who use SQL Developer, we know how to debug with SQL Developer. Compile for debug, breakpoint and go. However folks are confused by what “remote debugging” is and how it works. At its most basic it allows us to run a procedure in a session and debug it from another session.

Let’s say we have a simple function like this

demo@ORA12C> create or replace function foo(x int)

  2  return number as

  3  begin

  4     if mod(x,2) = 0 then

  5             return x+2;

  6     else

  7             return x+1;

  8     end if;

  9  end;

 10  /

Function created. 

 

We compile that function for debug as normal like this.

 

demo@ORA12C> alter function foo compile debug; 

 

Function altered. 

 

Now for remote debugging, we want to go to another session and run this function from there, for clarity we can do it with SQL*Plus, before that however we need to switch on the remote debugger listener, so that we can attach to a session.  

 

So right click the connection from the SQL Developer and say “Remote Debug”, which will pop up a little window like this.

 

We are running the database server on this same machine, so we don’t need to add any other information, but if you are connecting to database running on a different machine, add that host name to this LOCAL ADDRESS field and choose the appropriate port.  

When you click OK on this, Run manager is show with the listener details.

 

Now from SQL*Plus we run our function like this.

 

Once we run the anonymous block, the remote debugger kicks in and we stop at the appropriate breakpoint in the code.

From there you can debug the code further in the SQL Developer. This works for all the application (Java,.Net,C,C++,Phyton,R,Ruby etc) calling PL/SQL code.

Online TDE in 12.2

One of the new exciting security feature introduced in Oracle 12cR2 is the ability to encrypt Tablespaces “online”.

 

In the past implementing TDE requires creating a new encrypted tablespace and then moving the data from original tablespace to the encrypted tablespace, which means a down time of your systems unless you use active data guard which minimizes TDE conversion implementation.

 

 The following are the steps to be followed for online TDE.

 

o   Configuring a key store.

o   Creating a key store

o   Open the key store

o   Setup the TDE master encryption key

o   Encrypt your data

 

Key store is a container that stores the TDE master encryption key in software key store (regular file system/ ASM file system/ ASM disk group) or Hardware security module (HSM) or Oracle Key vault key store.

 

Oracle database checks the sqlnet.ora file on the database server for the directory location of the keystore. Whether it is a software/HSM/Oracle vault key store.

 

o   It attempts to use the keystore in the location specified by the parameter (ENCRYPTION_WALLET_LOCATION) in the sqlnet.ora file

o   If the ENCRYPTION_WALLET_LOCATION parameter is not set, then it attempts to use the keystore in the location that is specified by the parameter WALLET_LOCATION in the sqlnet.ora file

o   If the WALLET_LOCATION parameter is also not set, then database look for the keystore in the default location available in the V$ENCRYPTION_WALLET

 

demo@ORA12C> conn syskm/Password-1@ora12c as SYSKM

Connected.

syskm@ORA12C> column wrl_parameter new_val f

syskm@ORA12C> select wrl_parameter from v$encryption_wallet;

 

WRL_PARAMETER

--------------------------------------------------------------------------------------

D:\APP\VNAMEIT\VIRTUAL\ADMIN\ORA12C\WALLET

 

syskm@ORA12C> administer key management create keystore '&f' identified by foobar;

old   1: administer key management create keystore '&f' identified by foobar

new   1: administer key management create keystore 'D:\APP\VNAMEIT\VIRTUAL\ADMIN\ORA12C\WALLET' identified by foobar

administer key management create keystore 'D:\APP\VNAMEIT\VIRTUAL\ADMIN\ORA12C\WALLET' identified by foobar

*

ERROR at line 1:

ORA-46633: creation of a password-based keystore failed

 

The wallet location doesn’t exists, so we need to create the specific directories at that location.

 

syskm@ORA12C> $mkdir D:\APP\VNAMEIT\VIRTUAL\admin\ORA12c\WALLET

 

syskm@ORA12C> administer key management create keystore '&f' identified by foobar;

old   1: administer key management create keystore '&f' identified by foobar

new   1: administer key management create keystore 'D:\APP\VNAMEIT\VIRTUAL\ADMIN\ORA12C\WALLET' identified by foobar

 

keystore altered.

 

 

Then open the keystore and setup the master key in this keystore.

 

syskm@ORA12C> administer key management set keystore open identified by foobar;

 

keystore altered.

 

syskm@ORA12C> administer key management set key identified by foobar WITH BACKUP;

 

keystore altered.

 

 

After you run these commands, the eWallet.p12 file, which is the keystore, appears in the keystore location.

 

syskm@ORA12C> $dir &f

 Volume in drive D is DATA

 Volume Serial Number is 50D3-A8AC

 

 Directory of D:\APP\VNAMEIT\VIRTUAL\ADMIN\ORA12C\WALLET

 

01/18/2018  05:50 PM    <DIR>          .

01/18/2018  05:50 PM    <DIR>          ..

01/18/2018  05:50 PM             3,848 ewallet.p12

01/18/2018  05:50 PM             2,400 ewallet_2018011812204795.p12

               2 File(s)          6,248 bytes

               2 Dir(s)  228,193,591,296 bytes free

 

 

With this keystore defined, we are ready to test our Encryption process.

 

First let’s create the tablespace with no Encryption defined, and grant the tablespace quota to application schema.

 

syskm@ORA12C> conn rajesh/oracle@ora12c

Connected.

rajesh@ORA12C> create tablespace ts_clear

  2  datafile 'D:\APP\VNAMEIT\VIRTUAL\ORADATA\ORA12C\ts_clear_01.dbf' size 100M ,

  3     'D:\APP\VNAMEIT\VIRTUAL\ORADATA\ORA12C\ts_clear_02.dbf' size 100M;

 

Tablespace created.

 

rajesh@ORA12C> alter user demo quota unlimited on ts_clear;

 

User altered.

 

 

Now let’s create a couple of objects on this newly created tablespace.

 

 

rajesh@ORA12C> conn demo/demo@ora12c

Connected.

demo@ORA12C> create table t

  2  tablespace ts_clear

  3  as

  4  select * from all_users;

 

Table created.

 

demo@ORA12C> create index t_idx on t(lower(username))

  2  tablespace ts_clear;

 

Index created.

 

 

Since this new tablespace is not yet encrypted, we could see the data remain in clear piece of text in their data files.

 

demo@ORA12C> conn rajesh/oracle@ora12c

Connected.

rajesh@ORA12C> alter system checkpoint;

 

System altered.

 

rajesh@ORA12C> $find /I "scott" D:\APP\VNAMEIT\VIRTUAL\ORADATA\ORA12C\ts_clear_*.dbf

 

---------- D:\APP\VNAMEIT\VIRTUAL\ORADATA\ORA12C\TS_CLEAR_01.DBF

        ♣SCOTT♥┬☻

♣scott♠☻Ç

 

---------- D:\APP\VNAMEIT\VIRTUAL\ORADATA\ORA12C\TS_CLEAR_02.DBF

 

 

Now the interesting part is online Tablespace encryption conversion: we can run the below ALTER TABLESPACE command to encrypt an existing tablespace.

 

rajesh@ORA12C> alter tablespace ts_clear

  2  encryption ONLINE

  3  using 'AES192'

  4  encrypt file_name_convert=('ts_clear_01.dbf','ts_clear_enc_01.dbf',

  5     'ts_clear_02.dbf','ts_clear_enc_02.dbf') ;

 

Tablespace altered.

 

Once encrypted no more clear piece of text in their data files.

 

rajesh@ORA12C> $find /I "scott" D:\APP\VNAMEIT\VIRTUAL\ORADATA\ORA12C\ts_clear_*.dbf

 

---------- D:\APP\VNAMEIT\VIRTUAL\ORADATA\ORA12C\TS_CLEAR_ENC_01.DBF

 

---------- D:\APP\VNAMEIT\VIRTUAL\ORADATA\ORA12C\TS_CLEAR_ENC_02.DBF

 

rajesh@ORA12C>

Sequence.nextval in 12c comparision with triggers

One of the cool thing in 12c is that the ability to default a sequence.nextval to a column.

While prior to 12c, we could achieve the same using a simple trigger in place.

Here is a simple test case to show – how things got improved in 12c by defaulting sequence.nextval to a column.

demo@ORA12C> create table t1(x int,y varchar2(300));

 

Table created.

 

demo@ORA12C> create sequence s;

 

Sequence created.

 

demo@ORA12C> create table t2(x int default s.nextval,y varchar2(300));

 

Table created.

 

demo@ORA12C> create or replace trigger t1_trig

  2  before insert on t1

  3  for each row

  4  begin

  5     if :new.x is null then

  6             :new.x := s.nextval;

  7     end if;

  8  end;

  9  /

 

Trigger created.

 

So we created two tables

o   Table T1 with trigger in place to populate the column X with sequence.nextval

o   Table T2 with sequence.nextval defaulted to the column X

 

demo@ORA12C> insert into t1(y) select object_name from big_table;

 

1000000 rows created.

 

demo@ORA12C> insert into t2(y) select object_name from big_table;

 

1000000 rows created.

 

demo@ORA12C> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

 

Tkprof shows this.

 

insert into t1(y) select object_name from big_table

 

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1    150.21     151.03      18570      32498      54095     1000000

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        2    150.21     151.04      18570      32498      54095     1000000

 

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 108 

Number of plan statistics captured: 1

 

Rows (1st) Rows (avg) Rows (max)  Row Source Operation

---------- ---------- ----------  ---------------------------------------------------

         0          0          0  LOAD TABLE CONVENTIONAL  T1 (cr=82511 pr=18570 pw=0 time=243474242 us starts=1)

   1000000    1000000    1000000   TABLE ACCESS FULL BIG_TABLE (cr=24709 pr=18570 pw=0 time=743301 us starts=1 cost=5125 size=38000000 card=1000000)

 

 

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  direct path read                                3        0.01          0.04

  log file sync                                   1        0.00          0.00

  SQL*Net message to client                       1        0.00          0.00

  SQL*Net message from client                     1        0.00          0.00

********************************************************************************

 

insert into t2(y) select object_name from big_table

 

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1     12.44      12.96      18570      26527     105359     1000000

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        2     12.44      12.96      18570      26527     105359     1000000

 

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 108 

Number of plan statistics captured: 1

 

Rows (1st) Rows (avg) Rows (max)  Row Source Operation

---------- ---------- ----------  ---------------------------------------------------

         0          0          0  LOAD TABLE CONVENTIONAL  T2 (cr=76539 pr=18570 pw=0 time=82169362 us starts=1)

   1000000    1000000    1000000   SEQUENCE  S (cr=68585 pr=18570 pw=0 time=316161124 us starts=1)

   1000000    1000000    1000000    TABLE ACCESS FULL BIG_TABLE (cr=18574 pr=18570 pw=0 time=519706 us starts=1 cost=5125 size=38000000 card=1000000)

 

 

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  PGA memory operation                            1        0.00          0.00

  direct path read                                4        0.05          0.08

  SQL*Net message to client                       1        0.00          0.00

  SQL*Net message from client                     1        0.00          0.00

 

A comparison from runstats tool report the same.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

 

demo@ORA12C> truncate table t1;

 

Table truncated.

 

demo@ORA12C> truncate table t2;

 

Table truncated.

 

demo@ORA12C> exec runstats_pkg.rs_start;

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> insert into t1(y) select object_name from big_table;

 

1000000 rows created.

 

demo@ORA12C> exec runstats_pkg.rs_middle;

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> insert into t2(y) select object_name from big_table;

 

1000000 rows created.

 

demo@ORA12C> exec runstats_pkg.rs_stop(1000);

Run1 ran in 3595 cpu hsecs

Run2 ran in 624 cpu hsecs

run 1 ran in 576.12% of the time

 

Name                                  Run1        Run2        Diff

STAT...redo size                86,105,192  86,103,620      -1,572

LATCH.active service list            2,919         918      -2,001

STAT...recursive cpu usage           2,465         212      -2,253

STAT...Elapsed Time                  3,672       1,167      -2,505

STAT...DB time                       3,674       1,165      -2,509

STAT...CPU used when call star       3,596         622      -2,974

STAT...CPU used by this sessio       3,596         622      -2,974

STAT...dirty buffers inspected       2,519       6,232       3,713

LATCH.simulator hash latch           8,922      13,084       4,162

LATCH.simulator lru latch            3,766       8,244       4,478

LATCH.JS queue state obj latch       7,560       2,394      -5,166

LATCH.cache buffers lru chain        4,504      10,010       5,506

LATCH.enqueue hash chains          317,189     306,351     -10,838

LATCH.object queue header oper      28,528      41,116      12,588

LATCH.checkpoint queue latch         9,486      22,116      12,630

LATCH.cache buffers chains         867,666     884,259      16,593

STAT...session uga memory           65,432          56     -65,376

STAT...session pga memory max       65,536           0     -65,536

STAT...session pga memory         -131,072     -65,536      65,536

STAT...logical read bytes from########################    -122,880

STAT...session uga memory max      192,584      65,424    -127,160

STAT...session cursor cache hi   1,050,001      50,003    -999,998

STAT...execute count             1,050,006      50,006  -1,000,000

STAT...opened cursors cumulati   1,050,006      50,006  -1,000,000

STAT...calls to get snapshot s   1,100,086     100,084  -1,000,002

STAT...recursive calls           1,050,090      50,084  -1,000,006

LATCH.shared pool                1,001,123         687  -1,000,436

STAT...Effective IO time           306,332   5,162,302   4,855,970

STAT...file io wait time        36,925,506  20,782,391 -16,143,115

 

Run1 latches total versus runs -- difference and pct

        Run1        Run2        Diff       Pct

   5,623,724   4,660,079    -963,645    120.68%

 

PL/SQL procedure successfully completed.

 

demo@ORA12C>

demo@ORA12C>

 

So when you upgrade/migrate to 12c, think about revisiting those triggers to default values if possible, since they yield more benefits to performance (in-terms of CPU and number of recursive calls).