Miladin Modrakovic's Blog: Oraclue

Oracle internals, debugging and undocumented features

Dbms_xplan by Granville Bonyata

I  am extremly busy these days and do not have any time to blog.That’s why my friend Granville Bonyate decided to write few small posts.Here is the first on in series.

 

Having worked with Oracle for many (twenty!) years, I sometimes find new features released several years ago that I didn’t catch at the time. Most recently, it would be that 10g added the display_cursor function to dbms_xplan. Most usefully, you can pass in the Sql-Id (from v$sql) and get the formatted execution plan for a SQL statement: Select * from table(dbms_xplan.display_cursor(‘d5dj7zctjwy1y’)); More information here:

http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_xplan.htm

 

Very useful when you’re at a site that doesn’t have TOAD or OEM.

Granville Bonyata

 

Resolving Oracle specific errors ( ORA-01031, ORA-00942 etc.)

I lost a count how many times I run into ORA-01031 error .Since I am the one executing the query I can see which SQL statement I am running and then focus on specific objects to find missing privilages.

It gets more difficult when end-user get this error.Thanks to Oracle events this is easy to resolve.

Normally I would just enable errrostack tracing on level 3 for specific error and get a user SQL statement.Once I get it I can quickly resolve permission issue .

To get errorstack for ORA-01031 I would run alter statement bellow:

alter system set events ‘1031 trace name errorstack level 3;

and then wait for error to happen .It will be recorder into database alert log file .From there I can find trace file located in dump destination.So here is my file:

*** 2011-10-25 10:13:28.025
*** SESSION ID:(84.2771) 2011-10-25 10:13:28.025
*** CLIENT ID:() 2011-10-25 10:13:28.025
*** SERVICE NAME:(test.world) 2011-10-25 10:13:28.025
*** MODULE NAME:() 2011-10-25 10:13:28.025
*** ACTION NAME:() 2011-10-25 10:13:28.025

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
—– Error Stack Dump —–
ORA-01031: insufficient privileges
—– SQL Statement (None) —–
Current SQL information unavailable – no cursor.

—– Call Stack Trace —–
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
——————– ——– ——————– —————————

However errorstack did not produce SQL statement what means I do not know which object is causing this error.

Solution is  to add more diagnostic events to generate additional information.

Dumping library cache with level 10 I will be able to capture it.So here is my statement:
alter system set events ‘1031 trace name errorstack level 3; name library_cache level 10′;

Notice that I am running two different actions using one command.

 again this my trace:
*** 2011-10-25 09:31:30.751
*** SESSION ID:(245.633) 2011-10-25 09:31:30.751
*** CLIENT ID:() 2011-10-25 09:31:30.751
*** SERVICE NAME:(test.world) 2011-10-25 09:31:30.751
*** MODULE NAME:() 2011-10-25 09:31:30.751
*** ACTION NAME:() 2011-10-25 09:31:30.751

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
—– Error Stack Dump —–
ORA-01031: insufficient privileges
—– SQL Statement (None) —–
Current SQL information unavailable – no cursor.

—– Call Stack Trace —–
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)

Part above comes from errorstack ..  However if you search your trace file for word “library” you will get first occurence for  library dump  and see objects in question ( TEST.TEST_QUEUE ) in my case.
proc=0x25419d9e8, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8476, pg=0

LibraryObjectLock:  Address=0x25a903f18 Handle=0x259a47d80 Mode=N CallPin=0x25a903d18 CanBeBrokenCount=2 Incarnation=1 ExecutionCount=0

User=0x257202818 Session=0x257202818 ReferenceCount=1 Flags=PNC/[0002] SavepointNum=d1
LibraryHandle:  Address=0x259a47d80 Hash=150a21c LockMode=N PinMode=S LoadLockMode=0 Status=VALD
ObjectName:  Name=TEST.TEST_QUEUE
FullHashValue=93a71dce5afcd4f5d2a394790150a21c Namespace=QUEUE(10) Type=QUEUE(24) Identifier=105660 OwnerIdn=116
To disable tracing simple run command

alter system set events ‘1031 trace name errorstack off; name library_cache off';

Of course you can use method for other oracle errors  like  ORA-00942 .Just change error number.

e.g.

alter system set events ‘942 trace name errorstack level 3; name library_cache level 10′;

Disabling Oracle Streams Propagation when nothing else works

It’s been a while since I  wrote any post .. I need vacation too :)

Here is very short one but useful tip for folks that are using Oracle Streams.

I know everyone is talking about GoldenGate but trust  me Oracle Streams are good solution if  you do not have budget for GoldenGate.Oracle is working very hard to implement best of Streams into GG.

Anyway this tip shows how to disable Oracle Streams propagation when nothing else is working.At least in my case..

I was trying to stop Oracle Streams propagation process but for some reason nothing did not work..  Finally I realized that Oracle Streams propagation process is related to Oracle jobs and by  quickly disabling (  remember there are other jobs that runs ) oracle scheduler I was able to stop Streams propagation process .

First disable scheduler:

SQL> exec dbms_scheduler.set_scheduler_attribute(‘SCHEDULER_DISABLED’,’TRUE’);
PL/SQL procedure successfully completed.

Stop propagation:

SQL> exec dbms_propagation_adm.stop_propagation(‘TEST_PROPAGATION’, true);

PL/SQL procedure successfully completed.

and enabling scheduler again.

SQL>  exec dbms_scheduler.set_scheduler_attribute(‘SCHEDULER_DISABLED’,’FALSE’);

Export / Import data over db link – using dbms_datapump and dbms_file_transfer packages

— Datapump FULL MODE EXPORT

DECLARE
h1                NUMBER;       — data pump job handle
job_state         VARCHAR2(30); — job state
status            ku$_Status; — data pump status
job_not_exist     exception;
pragma            exception_init(job_not_exist, -31626);
BEGIN

h1 := dbms_datapump.open(operation=>’EXPORT’,job_mode=>’FULL’,
remote_link=>”,
job_name=>NULL, version=>’COMPATIBLE’);

dbms_datapump.add_file(
handle=>h1,
filename=>’test.dmp’,
directory=>’DPUMP_DIR’,
filetype=>dbms_datapump.ku$_file_type_dump_file);
dbms_datapump.add_file(
handle=>h1,
filename=>’test.log’,
directory=>’DPUMP_DIR’,
filetype=>dbms_datapump.ku$_file_type_log_file);

dbms_datapump.start_job(h1);

job_state := ‘UNDEFINED';
BEGIN
WHILE (job_state != ‘COMPLETED’) AND (job_state != ‘STOPPED’) LOOP
status := dbms_datapump.get_status(
handle => h1,
mask => dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,
timeout => -1);
job_state := status.job_status.state;
dbms_lock.sleep(10);
END LOOP;
EXCEPTION WHEN job_not_exist THEN
dbms_output.put_line(‘job finished’);
END;

— Transfer dump file to the destination directory
dbms_file_transfer.put_file(
source_directory_object => ‘”DPUMP_DIR”‘,
source_file_name => ‘test.dmp’,
destination_directory_object => ‘”DPUMP_DIR”‘,
destination_file_name => ‘test.dmp’,
destination_database => ‘test.world’);

COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/

PROMPT Connecting as  administrator to site 2
CONNECT username/password
— Datapump FULL MODE IMPORT

DECLARE
h1                NUMBER;       — data pump job handle
job_state         VARCHAR2(30); — job state
status            ku$_Status; — data pump status
job_not_exist     exception;
pragma            exception_init(job_not_exist, -31626);
BEGIN

h1 := dbms_datapump.open(operation=>’IMPORT’,job_mode=>’FULL’,
remote_link=>”,
job_name=>NULL, version=>’COMPATIBLE’);

dbms_datapump.set_parameter(h1, ‘STREAMS_CONFIGURATION’, 0);

dbms_datapump.add_file(
handle=>h1,
filename=>’test.dmp’,
directory=>’DPUMP_DIR’,
filetype=>dbms_datapump.ku$_file_type_dump_file);
dbms_datapump.add_file(
handle=>h1,
filename=>’test.log’,
directory=>’DPUMP_DIR’,
filetype=>dbms_datapump.ku$_file_type_log_file);

dbms_datapump.start_job(h1);

job_state := ‘UNDEFINED';
BEGIN
WHILE (job_state != ‘COMPLETED’) AND (job_state != ‘STOPPED’) LOOP
status := dbms_datapump.get_status(
handle => h1,
mask => dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,
timeout => -1);
job_state := status.job_status.state;
dbms_lock.sleep(10);
END LOOP;
EXCEPTION WHEN job_not_exist THEN
dbms_output.put_line(‘job finished’);
END;
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/

Oracle GoldenGate and Encrypted Tablespaces (TDE) in 11.2

To make GG work with encrypted tablespaces  you have to apply database patch:

Patch  10395645 for Oracle 11.2.0.2.

mkstore -wrl ./ -createEntry ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG

Supply shared secret for GG ( not wallet password).Last step is to supply wallet password.Copy ewallet.p12 file to other nodes ( if running RAC but not sharing wallet location ).
Do not forget standby databases if you have them.
If you like to see shared secret for GG you can run:

 mkstore -wrl  /u01/app/oracle/wallet -viewEntry ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG
Create package  dbms_internal_clkm.Package code get created by running script prvtclkm.plb  located under GG home .Once package is created grant execute privileget to GG user:

grant execute on  on thedbms_internal_clkm to gguser;

This package has only one procedure  called GET_KEY with following parameters:

SQL> desc dbms_internal_clkm
PROCEDURE GET_KEY
 Argument Name                  Type                    In/Out Default?
 —————————— ———————– —— ——–
 CLIENT                         VARCHAR2                IN
 MASTER_KEY_ID                  VARCHAR2                IN
 WRAPPED_KEY                    VARCHAR2                IN
 FLAGS                          BINARY_INTEGER          IN
 KEY                            VARCHAR2                OUT
Encrypt the Shared secret key
GGSCI> ENCRYPT PASSWORD “shared key”
Add an entry to the Extract parameter file to decrpt the new shared password

DBOPTIONS  DECRYPTPASSWORD “SHARED KEY”
Close and open wallet to clear caches.If you have wallet enabled with auto login ( file cwallet.sso ) than disable it temporary to close and open wallet.

Follow

Get every new post delivered to your Inbox.

Join 49 other followers