Monthly Archives: April 2012


Datapump, remap_schema and passwords 2

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

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

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?

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