Yearly Archives: 2012

Oracle 11g default passwords and hashes

This entry was posted in Oracle Security on by

Today I was asked to get the list of Oracle default password hashes. Immediately thought about Pete Finigan website which talks about all Oracle security issues.

Quickly I found this page with the list of hashes, but I was not convinced this would work on Oracle 11.1 and 11.2. This because with the latest Oracle versions the information can be in different columns (password, spare4 from sys.user$).

But then looking at MOS, I found the note Script to Check for Default Passwords Being Used for Common Usernames [ID 227010.1] where an interesting snipped is found:

The default password checker no longer exists in 11g, instead there is a
table in the data dictionary SYS.DEFAULT_PWD$ which contains the default
passwords and there is a view DBA_USERS_WITH_DEFPWD which shows which users
on the system are still using default passwords.

So it was now important to see if on my databases all the accounts which still have the defaults are locked (or better, deleted). A simple sql gave the answer:

SQL> select username , account_status
  2  from DBA_USERS_WITH_DEFPWD
  3  natural join dba_users;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
APPQOSSYS                      EXPIRED & LOCKED
CTXSYS                         EXPIRED & LOCKED
EXFSYS                         EXPIRED & LOCKED
MDDATA                         EXPIRED & LOCKED
MDSYS                          EXPIRED & LOCKED
OLAPSYS                        EXPIRED & LOCKED
ORACLE_OCM                     EXPIRED & LOCKED
ORDDATA                        EXPIRED & LOCKED
ORDPLUGINS                     EXPIRED & LOCKED
ORDSYS                         EXPIRED & LOCKED
OUTLN                          EXPIRED & LOCKED
OWBSYS                         EXPIRED & LOCKED
SCOTT                          EXPIRED & LOCKED
SI_INFORMTN_SCHEMA             EXPIRED & LOCKED
SPATIAL_CSW_ADMIN_USR          EXPIRED & LOCKED
SPATIAL_WFS_ADMIN_USR          EXPIRED & LOCKED
WMSYS                          EXPIRED & LOCKED
XDB                            EXPIRED & LOCKED
XS$NULL                        EXPIRED & LOCKED

19 rows selected.

Datapump, remap_schema and passwords 2

This entry was posted in Datapump Oracle Oracle 10g Security on by

Something that is quite logical after you think about, but surprises you at first.

When you do expdp/impdp with the remap_schema parameter, the password of the created user on the target database will not be a valid one.

This because on Oracle 10g the password hash is calculated using the “username” as a seed. When you import a user with impdp, the password hash is not recalculated. So when the user changes (by the remap_schema parameter), the new schema password will not work.

Clean environment: 

SQL> select username, password
from dba_users 
where username='T1';
no rows selected

Create user T1 with simple password…

SQL> grant create session to t1 identified by t1;
Grant succeeded.

Check password hash_values

SQL> select username, password 
from dba_users 
where username='T1';
USERNAME                       PASSWORD
------------------------------ ------------------------------
T1                             2A6EC3E5F234DF52

We can connect:

SQL> connect t1/t1
Connected.

Run the export…

$expdp schemas=t1
Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 19 April, 2012 11:16:30
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA schemas=t1
...

Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:17:00

And import with remap_schema:

$impdp remap_schema=t1:t2
Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 19 April, 2012 11:18:30
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA remap_schema=t1:t2
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 11:18:35

We see now that the password hash of the two users is the same:

SQL> select username, password from dba_users where username in ('T1','T2');
USERNAME                       PASSWORD
------------------------------ ------------------------------
T1                             2A6EC3E5F234DF52
T2                             2A6EC3E5F234DF52

But connecting with the original password does not work:

SQL> connect t2/t1
ERROR:
ORA-01017: invalid username/password; logon denied

GoldenGate replicat performance

This entry was posted in GoldenGate Oracle Performance on by

Today at a client I had a old abended replicat process that I decided to give life to. So I solved the problem and start it.

The environment is using GoldenGate 11.1.1.1.2 against Oracle 10.2.0.4 database with ASM, on AIX. The replicat process replicates all operations of a 700+ tables schema. There were other replicats running at the same time writing to other schemas.

Then I decided to see the performance of GoldenGate. With the query:

GGSCI> stats r_xxxx TOTALSONLY * REPORTRATE sec

You get the detailed statistics of operations per second since the start of the process, since the beginning of the day and last hour for all tables replicated by that process. For instance:

*** Hourly statistics since 2012-04-16 13:00:00 ***
        Total inserts/second:                     1397.76
        Total updates/second:                     1307.46
        Total deletes/second:                      991.50
        Total discards/second:                       0.00
        Total operations/second:                  3696.71

So here we can see it is doing a bit more than 3500 operations per second, divided quite evenly between inserts, updates and deletes.

As usually the GoldenGate is used for realtime replication and there are no big operations, the client does not use performance related parameters. But this time I decided to play with them.

After adding both: BATCHSQL and INSERTAPPEND to the parameter file of the replicat process, the results were the following (after more than 10 minutes running) and performance is still increasing:

*** Hourly statistics since 2012-04-16 13:43:17 ***
        Total inserts/second:                     2174.92
        Total updates/second:                     2540.20
        Total deletes/second:                     1636.16
        Total discards/second:                       0.00
        Total operations/second:                  6351.28

We see the performance increased by 90% !

I got interested to see if the BATCHSQL parameter only by itself could make the difference. So I removed the INSERTAPPEND parameter (which only influences the inserts anyway). Here are the results after more than 10 minutes.

*** Hourly statistics since 2012-04-16 14:00:00 ***
        Total inserts/second:                     2402.21
        Total updates/second:                     2185.24
        Total deletes/second:                     1742.43
        Total discards/second:                       0.00
        Total operations/second:                  6329.88

Yep, seems the system of my client in certain situations benefits mostly of the BATCHSQL parameter.

For those who don’t know, “in BATCHSQL mode, Replicat organizes similar SQL statements into batches within a memory queue, and then it applies each batch in one database operation. A batch contains SQL statements that affect the same table, operation type (insert, update, or delete), and column list.” (in GoldenGate Reference Guide).

crs_stat and Oracle cluster resources status

This entry was posted in Oracle RAC on by

One script output that Oracle to my knowledge did not improve yet was the “crs_stat -t”. On 11.2 there is the “./crsctl status resource -t“, but the output is quite long.

The usual output is like this:

hqbuun415:/oracle/app/product/11.1/crs/bin:crs:$crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....ISP1.cs application    ONLINE    ONLINE    hqbuun415
ora....ss2.srv application    ONLINE    ONLINE    hqbuun415
ora....ISP2.cs application    ONLINE    ONLINE    hqbuun414
ora....ss1.srv application    ONLINE    ONLINE    hqbuun414
ora....OAFO.cs application    ONLINE    ONLINE    hqbuun414
ora....ss1.srv application    ONLINE    ONLINE    hqbuun414
ora....M3PL.cs application    ONLINE    ONLINE    hqbuun415
ora....ss2.srv application    ONLINE    ONLINE    hqbuun415
ora....MALF.cs application    ONLINE    ONLINE    hqbuun414
ora....ss1.srv application    ONLINE    ONLINE    hqbuun414
ora....MAVE.cs application    ONLINE    ONLINE    hqbuun415
ora....ss2.srv application    ONLINE    ONLINE    hqbuun415
ora....WMEA.cs application    ONLINE    ONLINE    hqbuun414
ora....ss1.srv application    ONLINE    ONLINE    hqbuun414
ora....WMSS.db application    ONLINE    ONLINE    hqbuun414
ora....s1.inst application    ONLINE    ONLINE    hqbuun414
ora....s2.inst application    ONLINE    ONLINE    hqbuun415
ora....SM1.asm application    ONLINE    ONLINE    hqbuun414
ora....14.lsnr application    ONLINE    ONLINE    hqbuun414
ora....414.gsd application    ONLINE    ONLINE    hqbuun414
ora....414.ons application    ONLINE    ONLINE    hqbuun414
ora....414.vip application    ONLINE    ONLINE    hqbuun414
ora....SM2.asm application    ONLINE    ONLINE    hqbuun415
ora....15.lsnr application    ONLINE    ONLINE    hqbuun415
ora....415.gsd application    ONLINE    ONLINE    hqbuun415
ora....415.ons application    ONLINE    ONLINE    hqbuun415
ora....415.vip application    ONLINE    ONLINE    hqbuun415

But on Metalink (My Oracle Support) note 259301.1 we can find the magic script (which works still with Oracle 11.2):

--------------------------- Begin Shell Script -------------------------------

#!/usr/bin/ksh
#
# Sample 10g CRS resource status query script
#
# Description:
#    - Returns formatted version of crs_stat -t, in tabular
#      format, with the complete rsc names and filtering keywords
#   - The argument, $RSC_KEY, is optional and if passed to the script, will
#     limit the output to HA resources whose names match $RSC_KEY.
# Requirements:
#   - $ORA_CRS_HOME should be set in your environment 

RSC_KEY=$1
QSTAT=-u
AWK=/usr/xpg4/bin/awk    # if not available use /usr/bin/awk

# Table header:echo ""
$AWK 
  'BEGIN {printf "%-45s %-10s %-18sn", "HA Resource", "Target", "State";
          printf "%-45s %-10s %-18sn", "-----------", "------", "-----";}'

# Table body:
$ORA_CRS_HOME/bin/crs_stat $QSTAT | $AWK 
 'BEGIN { FS="="; state = 0; }
  $1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};
  state == 0 {next;}
  $1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
  $1~/STATE/ && state == 2 {appstate = $2; state=3;}
  state == 3 {printf "%-45s %-10s %-18sn", appname, apptarget, appstate; state=0;}'
--------------------------- End Shell Script -------------------------------

This script makes a clear output as:

hqbuun414:/oracle/app/product/11.1/crs/bin:crs:$./crsstat
HA Resource                                   Target     State
-----------                                   ------     -----
ora.WMS01_WMSS.EISP1.cs                       ONLINE     ONLINE on hqbuun415
ora.WMS01_WMSS.EISP1.wmss2.srv                ONLINE     ONLINE on hqbuun415
ora.WMS01_WMSS.EISP2.cs                       ONLINE     ONLINE on hqbuun414
ora.WMS01_WMSS.EISP2.wmss1.srv                ONLINE     ONLINE on hqbuun414
ora.WMS01_WMSS.NESOAFO.cs                     ONLINE     ONLINE on hqbuun414
ora.WMS01_WMSS.NESOAFO.wmss1.srv              ONLINE     ONLINE on hqbuun414
ora.WMS01_WMSS.WM3PL.cs                       ONLINE     ONLINE on hqbuun415
ora.WMS01_WMSS.WM3PL.wmss2.srv                ONLINE     ONLINE on hqbuun415
ora.WMS01_WMSS.WMALF.cs                       ONLINE     ONLINE on hqbuun414
ora.WMS01_WMSS.WMALF.wmss1.srv                ONLINE     ONLINE on hqbuun414
ora.WMS01_WMSS.WMAVE.cs                       ONLINE     ONLINE on hqbuun415
ora.WMS01_WMSS.WMAVE.wmss2.srv                ONLINE     ONLINE on hqbuun415
ora.WMS01_WMSS.WMEA.cs                        ONLINE     ONLINE on hqbuun414
ora.WMS01_WMSS.WMEA.wmss1.srv                 ONLINE     ONLINE on hqbuun414
ora.WMS01_WMSS.db                             ONLINE     ONLINE on hqbuun414
ora.WMS01_WMSS.wmss1.inst                     ONLINE     ONLINE on hqbuun414
ora.WMS01_WMSS.wmss2.inst                     ONLINE     ONLINE on hqbuun415
ora.hqbuun414.ASM1.asm                        ONLINE     ONLINE on hqbuun414
ora.hqbuun414.LISTENER_HQBUUN414.lsnr         ONLINE     ONLINE on hqbuun414
ora.hqbuun414.gsd                             ONLINE     ONLINE on hqbuun414
ora.hqbuun414.ons                             ONLINE     ONLINE on hqbuun414
ora.hqbuun414.vip                             ONLINE     ONLINE on hqbuun414
ora.hqbuun415.ASM2.asm                        ONLINE     ONLINE on hqbuun415
ora.hqbuun415.LISTENER_HQBUUN415.lsnr         ONLINE     ONLINE on hqbuun415
ora.hqbuun415.gsd                             ONLINE     ONLINE on hqbuun415
ora.hqbuun415.ons                             ONLINE     ONLINE on hqbuun415
ora.hqbuun415.vip                             ONLINE     ONLINE on hqbuun415

Who is using the DB Link?

This entry was posted in DB Links Oracle on by

On the magic web I found this script at Jared Still’s blog. It allows to see which database links are being used and by whom. Information sometimes useful (needs to be run as SYSDBA):

-- who is querying via dblink?
-- Courtesy of Tom Kyte, via Mark Bobak
-- this script can be used at both ends of the database link
-- to match up which session on the remote database started
-- the local transaction
-- the GTXID will match for those sessions
-- just run the script on both databases

Select /*+ ORDERED */
substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10)      "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,
s2.username,
substr(
   decode(bitand(ksuseidl,11),
      1,'ACTIVE',
      0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),
      2,'SNIPED',
      3,'SNIPED',
      'KILLED'
   ),1,1
) "S",
substr(w.event,1,10) "WAITING"
from  x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w, v$session s2
where  g.K2GTDXCB =t.ktcxbxba
and   g.K2GTDSES=t.ktcxbses
and  s.addr=g.K2GTDSES
and  w.sid=s.indx
and s2.sid = w.sid

Check which Oracle PSU are installed

This entry was posted in OPatch Oracle Patchset Update PSU on by

With latest version of OPatch:

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i ‘DATABASE PSU’

or

set lines 255

col action_time for a40

col action for a8

col namespace for a7

col version for a11

col comments for a40

select substr(ACTION_TIME,1,40) action_time,substr(ACTION,1,8) action,substr(NAMESPACE,1,7) namespace,

substr(VERSION,1,11) version,ID,substr(COMMENTS,1,40) comments,BUNDLE_SERIES

from registry$history;

To install latest OPatch, after download (make sure you only have OPatch zip for the correct Oracle version: 10.2, 11.1, 11.2):

  • mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch.old
  • unzip p6880880* -d $ORACLE_HOME

ORA-14450, Global temporary tables and on commit preserve rows

This entry was posted in Oracle on by

— Clean environment
 drop table gtt;
 drop table gtt
            *
ERROR at line 1:
ORA-00942: table or view does not exist

— Global temporary – preserve rows
create global temporary table gtt ( x int ) on commit preserve rows;
Table created.

— Keeps information until the end of the user session
select duration from user_tables where table_name = ‘GTT’;
DURATION
—————
SYS$SESSION

— Insert one row
insert into gtt values(1);
1 row created.

— Creates a TO lock
select sid, type, id1, id2, lmode from v$lock where id1=(select object_id from all_objects where object_name=’GTT’);
       SID TY        ID1        ID2      LMODE
———- — ———- ———- ———-
      4343 TO     403115          1          3

— Commit transaction
commit;
Commit complete.

— TO lock continues…
select sid, type, id1, id2, lmode from v$lock where id1=(select object_id from all_objects where object_name=’GTT’);
       SID TY        ID1        ID2      LMODE
———- — ———- ———- ———-
      4343 TO     403115          1          3

— Any user that tries to change the table
— Will receive: ORA-14450: attempt to access a transactional temp table already in use
alter table gtt add (y int);
alter table gtt add (y int)
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use

— Only after doing truncate
truncate table gtt;
Table truncated.

— TO lock disappears
select * from v$lock where id1=(select object_id from all_objects where object_name=’GTT’);
no rows selected

— With a global temporary table delete rows
drop table gtt;
Table dropped.

create global temporary table gtt ( x int ) on commit delete rows;
Table created.

— The duration is at transaction level
select duration from user_tables where table_name = ‘GTT’;
DURATION
—————
SYS$TRANSACTION

— Insert one row
insert into gtt values(1);
1 row created.

— Creates a TO lock
select sid, type, id1, id2, lmode from v$lock where id1=(select object_id from all_objects where object_name=’GTT’);
       SID TY        ID1        ID2      LMODE
———- — ———- ———- ———-
      4343 TO     403116          1          3

— That disappears as soon as you commit the transaction
commit;
Commit complete.

select sid, type, id1, id2, lmode from v$lock where id1=(select object_id from all_objects where object_name=’GTT’);
no rows selected

— Clean up environment
drop table gtt;
Table dropped.

New Oracle GoldenGate 11.2

This entry was posted in GoldenGate Oracle on by

Oracle released few weeks ago the new version of GoldenGate 11.2.1.0.0. The biggest novelty is the “integrated capture mode”, meaning that it starts working together with the internal log miner functionality.

This means no extra configuration is needed when extracting from an Oracle RAC or using ASM.

Soon I’ll be testing this new feature for a client.

Meanwhile, I leave you with a snippet of the “Release Notes”, page 6.