Information that you and me can find useful in the future, concerning Oracle databases and related technologies.
Datapump, remap_schema and passwords
19 April 2012 @ 02:36 by Miguel Anjo
[Oracle 10g] [Security] [Datapump]
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
Grep lines before or after without grep
17 April 2012 @ 01:02 by Miguel Anjo
[AIX]
Here a way I used in AIX, where there is a grep without the -A and -B options:
nawk 'c-->0;$0~s{if(b)for(c=b+1;c>1;c--)
print r[(NR-c+1)%b];print;c=a}b{r[NR%b]=$0}'
b=2 a=0 s="ORA-" alert_us.log
GoldenGate replicat performance
16 April 2012 @ 05:16 by Miguel Anjo
[GoldenGate] [Oracle] [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 secYou 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).
Oracle to MySQL migration - what you should know
13 April 2012 @ 01:26 by Miguel Anjo
[MySQL] [Oracle]
A very nice presentation from Marco Tusa (Pythian) about the risk assessment of a Oracle to MySQL migration. Conclusion: think well and assess before moving an application to use MySQL instead of Oracle databases.
crs_stat and Oracle cluster resources status
11 April 2012 @ 05:38 by Miguel Anjo
[Oracle] [RAC]
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 hqbuun415But 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 %-18s\n", "HA Resource", "Target", "State";
printf "%-45s %-10s %-18s\n", "-----------", "------", "-----";}'
# 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 %-18s\n", 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
