Using the Secure External Password Store with Instant Client

There are several articles of how to use Secure External Password Store, e.g. Using The Secure External Password Store [ID 340559.1] and official doc Managing the Secure External Password Store for Password Credentials or Oracle’s White Paper Secure External Password Store. However, the main utility mkstore is not a part of Instant Client package. So how to use it?

I guess it is possible to create and edit wallet on the one of the server machines where all the commands needed definitely present and then copy it to the local machine, but let’s try another way.

mkstore actually is a shell script file located as bin/mkstore.bat on Windows and bin/mkstore on Unix in a standard Oracle Client installation. So copy it to our Instant Client directory, make some edits and copy other java libraries which could be necessary to run mkstore. Finally I’ve got it as such (for Windows client)


@ECHO OFF
SETLOCAL

SET ORACLE_HOME=%HOMEPATH%\Apps\instantclient_11_2

REM Get the command line arguments
set args=
:loop
 if !%1==! goto :done
 set args=%args% %1
 shift
 goto :loop
:done

REM Classpath for mkstore, copy these libraries from full installation

SET CP=%ORACLE_HOME%\oraclepki.jar
SET CP=%CP%;%ORACLE_HOME%\osdt_core.jar
SET CP=%CP%;%ORACLE_HOME%\osdt_cert.jar

java -classpath %CP% oracle.security.pki.OracleSecretStoreTextUI %args%

endlocal

All other action should be done as usual: create a wallet location and the wallet itself, edit sqlnet.ora, create credentials and so on. It worked for me.
A look to Unix version of mkstore suggests to add two more libraries: encryption/jlib/ojpse.jar and ldap/test/lib/emma.jar; these could be useful for cases I’m not able to test.

Posted in Instant Client, Oracle

`Dirty` trick from Linux vendors

Today I wasted several hours to troubleshoot an installation problem on OEL 6.3: GI was unable to install in silent mode due to error ‘File “/etc/resolv.conf” is not consistent across nodes’. I tried almost everything and even checked an md5 hash of the files.

At last the reason has been found on the OS vendor’s side and described in MOS document PRVF-5637 : DNS response time could not be checked on following nodes [ID 1480242.1] – in RHEL/OEL 6.3 the command nslookup returns exit code “1” instead of “0” in case of failure. Thus the issue has been successfully workaround by the following trick (as root on every node)

# mv /usr/bin/nslookup /usr/bin/nslookup.orig
# echo '#!/bin/bash
> /usr/bin/nslookup.orig $*
> exit 0' > /usr/bin/nslookup
# chmod a+x /usr/bin/nslookup

The last question is: according to POSIX a command can return 0 in case of success and 1 to 255 in case of failure, so OS vendors did a right thing. So why this small move to expected behavior have broken the installer?

Tagged with:
Posted in Oracle

How to forbid maintenance tasks to run during monthly reporting period

It’s a common practice for many companies to have kind of monthly reporting period near the first days of a month, when all parties work hard to provide reports in time. This time could be very strict in term of performance, so any other tasks such as statistics gathering would be harmful. So DBA should be smart (and lazy) enough to turn the AutoTask off. Rather than creating complex scheduling rules we will define a schedule declaratively.
As it is stated in an official documentation set, AutoTask schedules automatic maintenance tasks to run in a set of Oracle Scheduler windows known as maintenance windows. Next 22 days of maintenance windows are seen in dba_autotask_schedule

SQL > select window_name, start_time
  2   from dba_autotask_schedule order by start_time;

WINDOW_NAME                    START_TIME
------------------------------ ---------------------------------------------------
...
TUESDAY_WINDOW                 26-FEB-13 10.00.00.600000 PM +02:00
WEDNESDAY_WINDOW               27-FEB-13 10.00.00.600000 PM +02:00
THURSDAY_WINDOW                28-FEB-13 10.00.00.600000 PM +02:00
FRIDAY_WINDOW                  01-MAR-13 10.00.00.600000 PM +02:00
SATURDAY_WINDOW                02-MAR-13 06.00.00.600000 AM +02:00
SUNDAY_WINDOW                  03-MAR-13 06.00.00.600000 AM +02:00
MONDAY_WINDOW                  04-MAR-13 10.00.00.600000 PM +02:00
TUESDAY_WINDOW                 05-MAR-13 10.00.00.600000 PM +02:00
...

So we don’t want to open maintenance windows from February 28 to March 3.
Let’s create a schedule which covers first 3 days of a month

SQL> exec  DBMS_SCHEDULER.CREATE_SCHEDULE( -
>     schedule_name => 'monthly_reporting', -
>     repeat_interval => 'FREQ=monthly;BYMONTHDAY=-1,1,2,3', -
>     comments => 'Schedule for monthly reporting period' -
>   );

PL/SQL procedure successfully completed.

By the way, this schedule could be used also to set an appropriate resource management plan for this period.

Then one could exclude this period from autotask windows as such

SQL> begin
  2    for w in (select window_name,
  3                     repeat_interval
  4               from dba_scheduler_windows
  5               where window_name not like 'WEEK%')
  6    loop
  7      dbms_scheduler.set_attribute(
  8        name      => w.window_name,
  9        attribute => 'repeat_interval',
 10        value     => w.repeat_interval || ';exclude=monthly_reporting'
 11      );
 12    end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.

And then check that autotask schedule has changed accordingly

SQL > select window_name, start_time
  2  from dba_autotask_schedule
  3  where start_time between date'2013-03-01' - 3
  4                       and date'2013-03-01' + 5
  5  order by start_time;

WINDOW_NAME                    START_TIME
------------------------------ ------------------------------------------
TUESDAY_WINDOW                 26-FEB-13 10.00.00.100000 PM +02:00
WEDNESDAY_WINDOW               27-FEB-13 10.00.00.100000 PM +02:00
MONDAY_WINDOW                  04-MAR-13 10.00.00.100000 PM +02:00
TUESDAY_WINDOW                 05-MAR-13 10.00.00.100000 PM +02:00

There is no maintenance window to open in first three days as required.

Posted in Oracle, Scheduler

ORA-8103 and DDL on a trigger

Once upon a time i was contacted by one of internal customers with a strange situation: some job were failing with error ORA-8103 sometimes. The error itself is not a big deal; there are a lot of documents which describe this error, its reasons and recipes to solve: OERR: ORA-8103 “object no longer exists” / Troubleshooting, Diagnostic and Solution [ID 8103.1] or a wonderful Tanel Poder’s article. However, the case sounded fantastic: the error fired inside of a job which ran a query on a table, while other job disabled a trigger on that table.

I created a test case on that database which fully proved the case, once again: enable/disable on a table’s trigger lead to fail with ORA-8103 a query on those table independently on the state of the trigger.

The test case itself:


0. Drop table from previous run
SQL > drop table t purge;

Table dropped.

1. Create table
SQL > create table t(
    2   id number,
    3   ddate date,
    4   vvalue varchar2(4000)
    5 ) tablespace users;

Table created.

2. Populate it with a test data
SQL > begin
    2   for i in 1..100000
    3   loop
    4     insert into t values(i,sysdate + 100*dbms_random.normal, dbms_random.string('A', 4000));
    5   end loop;
    6   commit;
    7 end;
    8 /

PL/SQL procedure successfully completed.

3. Create a test dummy trigger
SQL > create or replace trigger t_trig
    2 before update on t
    3 for each row
    4 begin
    5   null;
    6 end;
    7 /

Trigger created.

4. Run the query in one session and run <em>alter trigger t_trig disable</em> in other session
while select is running.
SQL > select count(*) from t where vvalue like 'A%Z';
select count(*) from t where vvalue like 'A%Z'
*
ERROR at line 1:
ORA-08103: object no longer exists

The issue hase been reproduced on a several installations of 10.2.0.5 + Patch 14 (13460967 for 32bit and 13460968 for 64bit) on Windows machines. The patch is important: I couldn’t reproduce it with other versions.
There is no happy end here: I’ve opened SR and they was not able to create a bug for it because of current support status of 10gR2.
My customer solved the issue by himself: he just corrected the jobs (query on the table and DDL on its trigger) not to run at the same time.

UPD. Many thanks to pravednik who helped me a lot with testing of this issue.

Posted in ORA-8103, Oracle, trigger

Bombing a database with Public Synonym

Let’s imagine we have some procedure which depends in public synonym, say – USER_TABLES:

SQL> create or replace procedure system.tstproc
is
  cnt number;
begin
  select count(table_name) into cnt
  from user_tables ;
end;
/

Procedure created.

SQL> select object_name, object_type, status, last_ddl_time from dba_objects where object_name = 'TSTPROC';

OBJECT_NAME      OBJECT_TYPE         STATUS  LAST_DDL_TIME
---------------- ------------------- ------- -------------------
TSTPROC          PROCEDURE           VALID   2012.08.29:22:28:16

SQL> grant execute on system.tstproc to scott;

Grant succeeded.

SQL>

Connect as scott and check that it depends on the synonym:

SQL> select * from all_dependencies
  2  where name = 'TSTPROC'
  3  and referenced_type = 'SYNONYM';

OWNER        NAME         TYPE               REFERENCED_OWNER               REFERENCED_NAME  REFERENCED_TYPE    REFERENCED_LINK_NA DEPE
------------ ------------ ------------------ ------------------------------ ---------------- ------------------ ------------------ ----
SYSTEM       TSTPROC      PROCEDURE          PUBLIC                         USER_TABLES      SYNONYM                               HARD

SQL>

And do the trick (as scott)

SQL> create table user_tables as select 1 as N from dual;

Table created.

SQL> select object_name, status, last_ddl_time from all_objects where object_name = 'TSTPROC';

OBJECT_NAME      OBJECT_TYPE         STATUS  LAST_DDL_TIME
---------------- ------------------- ------- -------------------
TSTPROC          PROCEDURE           INVALID 2012.08.29:22:28:16

SQL> exec system.tstproc;

PL/SQL procedure successfully completed.

SQL> select object_name, status, last_ddl_time from all_objects where object_name = 'TSTPROC';

OBJECT_NAME      OBJECT_TYPE         STATUS  LAST_DDL_TIME
---------------- ------------------- ------- -------------------
TSTPROC          PROCEDURE           VALID   2012.08.29:22:33:06

SQL>

The procedure becomes invalid while creating object with the same name as public synonym on which procedure depends. It successfully gets valid next time the procedure called.
This behavior documented as Bug 10213073: INVALIDATION OF OBJECT WHEN OBJECT IS CREATED WITH SAME NAME AS PUBLIC SYNONYM. Patch exists for all PSUs of 11.2.0.2 and is online-applicable for Solaris and Linux (however, in my test only offline patching fixed this behavior for Solaris SPARC 64).

And the main thing. If a production database has kind of ‘after logon’ trigger or similar (which is very popular as a preventive solution for restricting users access to production schemas) and it depends on any of the public synonyms (dba_tab_privs, ora_sysevent etc), a tough could take the trigger into INVALID state and the database will become unavailable for new connections for some time – the trigger must be compiled manually by DBA.

UPD: The f ix for Bug 10213073 has been included into the patch 14275621 which is an October 2012 PSU (PSU8) so anyone who follows Oracle’s patching policy should be out of attack now. Don’t you?

Posted in Oracle, trigger

When DML becomes DDL

Some time ago I was contacted by one of our developers (smile and wave, yes, you!) with very interesting issue on (fortunately) development database. The next is almost a quote:


insert into SOMETABLE (CODE,...) values ('CODEVALUE', ...);

insert into SOMETABLE (CODE,
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-20033: USR has no privileges to DDL any object in this schema...

Ok, 20033 is user-defined exception at recursive level 2… this might be a trigger. And that’s it, I’ve found a ‘DDL prevention trigger’ which I have installed several months ago just for testing. The trigger was:

create or replace trigger system.ddl_prevent_tr
before create or alter or drop or rename
on database
when (
  sys.dictionary_obj_owner in ('APPOWN')
)
begin
  if ora_sysevent in ('CREATE','DROP') and
    sys.dictionary_obj_owner in ('APPOWN') and
    upper(user) not in ('APPOWN') and
    upper(sys.dictionary_obj_type) in ('PROCEDURE','PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER','TABLE')
  then
    raise_application_error(-20033,Upper(USER)||' has no privileges to DDL any object in this schema...');
  end if;
end;
/

That’s it but… the trigger is defined as ‘before create or alter or drop or rename’, where DDL came from into our insert? The table SOMETABLE doesn’t belong to APPOWN schema, and calling user isn’t APPOWN also.

After several minutes of stupor I’ve get an idea. To prove it I did the same insert and got the same error, but under 10046 trace. The tracefile produced was a bit huge.

Search backward from the end of the file shows some piece of cake:


EXEC #47408502285272:c=3000,e=2397,p=0,cr=32,cu=0,mis=0,r=0,dep=2,og=1,plh=0,tim=1343647204457842
ERROR #1:err=20033 tim=1343647204457853
CLOSE #47408502285272:c=0,e=10,dep=2,type=0,tim=1343647204457898
EXEC #47408502569656:c=174973,e=174353,p=299,cr=3161,cu=4,mis=0,r=0,dep=1,og=4,plh=0,tim=1343647204457919
ERROR #11:err=604 tim=1343647204457930
kdpurtab: err=604 goc1=604 goc2=20033 goc3=6512
CLOSE #47408502569656:c=0,e=2,dep=1,type=0,tim=1343647204457981

6th line is our error. Some additional search through the tracefile shows that cursor #47408502285272 is our trigger, and #47408502569656 is (tadaaa!)

drop table "APPOWN"."BIN$sdbtwxw2q6DgQAEKFjxyWw==$0" purge

Futher ‘reading’ of the tracefile showed the story:

  1. During insert Oracle didn’t find free space in the table segments and made a decision to allocate a next extent.
  2. Then it didn’t find enough contiguous set of blocks for the new extent and tried to free up some space from recyclebin and this time it was an object “APPOWN”.”BIN$sdbtwxw2q6DgQAEKFjxyWw==$0″
  3. It issued a drop operation for it which is definitely a DDL and object is owned by APPOWN and therefore has been caught by our trigger. That’s it.

The trigger has been fixed by adding additional condition: ora_dict_obj_name not like ‘BIN$%’

Tagged with: ,
Posted in Oracle

Moving forward to 11gR2: caveats and workarounds

This post is a small summary of all the issues we hit during several upgrades. I hope to update it (actually not, you know).

1. Don’t loose your ASM diskgroup. If you have diskgroup with AU>1M read this first: “Alert: Querying V$ASM_FILE Gives ORA-15196 After ASM Was Upgraded From 10gR2 To 11gR2 with an AU size > 1M” [ID 1145365.1]. In my case I was lucky to salvage the diskgroup by running ALTER DISKGROUP DATA CHECK ALL REPAIR twice.

2. Remove ORA_CRS_HOME from your environment prior to upgrade, otherwise the message “ORA-29780: unable to connect to GPnP daemon [CLSGPNP_ERR]” will become your friend. Does anybody need it?

3. Check your environment for LD_LIBRARY_PATH before doing install/uninstall of XDB (XDB is required for ACL). Even you haven’t configured it into .profile – oraenv is smart enough to set it.

4. WMSYS.WM_CONCAT. It is undocumented internal function that Oracle changed to return CLOB instead of VARCHAR2. So read the document “Problem with WMSYS.WM_CONCAT Function after Upgrading [ID 1300595.1]” first and don’t get surprised if you’re using such undocumented things.

5. To be continued…

Good luck in patching!

Posted in Uncategorized
Oracle ... as usual

Oracle by Laurent Leturgez

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

xt-r.com

Just another oracle developer

Coskan's Approach to Oracle

What I learned about Oracle

Oracle Scratchpad

Just another Oracle weblog

Oleksandr Denysenko's Blog

be prepared for The Future...