Author Archives: alesk
Pushing the limits of Windows
Mark Russinovich started a blog post series under the title Pushing the limits of Windows – a must read if you’re professional sysadmin/dba working with Windows OS. He started the series with physical memory and will continue with other essential resources. I hope he’ll cover Large Pages memory support in one of his future posts.
In addition to Mark’s blog I would like to mark down two links mentioned in his blog:
- Memory Limits for Windows Releases
- Alex Ionescu’s blog and his tool, meminfo (unfortunately available only for Vista and Windows 2008)
rlwrap + sqlplus
RLWRAP is one of those low profile tools that are really appreciated when you realize that you’re working on the server/workstation that don’t have them installed. On Windows OS, cmdhere is one such tool that is installed as soon as I logon more than once or twice on the same box. I think rlwrap is one such tool that should be installed on every Linux box – especially if you’re an Oracle DBA/developer working on command line with classic tools, such as sqlplus.
I’m not really spending that much time on Linux command line, but when I do, it’s almost always with Oracle sqlplus and I hate the fact that I could not retrieve previous command(s) by simply pressing the Up arrow key in a way that I’m used to do on Windows command prompt. Thanks to Catherine Devlin presentation I found out about rlwrap – elegant “workaround” (not that this tool is that new, on contrary nowadays it’s really an oldie – it’s just that I was not aware of it; you’ll find plenty of other posts out there on blogosphere – this note is solely for my own bookkeeping!). All we need to do is to install rlwrap that will handle command line history for tools such as sqlplus, rman etc.
After you download rlwrap from: http://utopia.knoware.nl/~hlub/uck/rlwrap/, you can install the tool as root with familiar steps:
# gunzip rlwrap-0.30.tar.gz # tar -xvf rlwrap-0.30.tar # cd rlwrap-0.30 # ./configure # make install
All you need to do is to run sqlplus or rman with read line wrapper:
$ rlwrap sqlplus /nolog or $ rlwrap rman target / nocatalog
SIOUG 2008
If I was enthusiastic about SIOUG 2007, I can’t say the same for this year event, SIOUG 2008 (21-24th September 2008).
It doesn’t bother me so much the place of the event (yet again, standard-unimaginative-dull Bernardine near Portorož…if you like tons and tons of concrete along the sea coast, you’ll probably enjoy both, Bernardin and Portorož.:-), as the noticeable lack of prominent foreign speakers. Yeah, I know – SIOUG 2007 will be hard to beat, especially when SIOUG is competing with Oracle Open Word, which will take place in the same week of September (grin).
With one or two presentations that I would like to attend on Monday and Tuesday I could not justify wasting two full days there – I sincerely tried to put together a full day agenda for the Monday and Tuesday, without a success. The content that would sparkle my interest just isn’t there. The last day of the conference is more promising. If I’ll attend SIOUG 2008 at all, I’ll book the last day of the conference – to listen to Jože, Riyaj and Jurij.
How to extract OLE object from MS Access database
Years ago we developed simple application in MS Access for keeping different requests from the users in one place, we chose MS Access as a front end and one existing MySQL server for the back-end (our decision to use MySQL was a deliberate one – anyway, it well might be any other RDBMS, it’s not important fact in the context of this memo).
Basically the design of the application was very simple, when someone from DBA team received an e-mail request from the user he/she entered some meta information in the database plus Word document that was attached to the message by simply copy/pasting word document in MS Access. The inserted document was stored in MySQL blob field as OLE document (doc with some binary meta data envelope). Here is the screenshot:
The small preview image of the “inserted” Word document for example is stored in MySQL BLOB field as part of the OLE envelope. As long as we’re happy with the MS Access as a front end we can always extract the doc by double clicking the preview image or by right clicking the image and selecting Open from the context menu.
We plan to rewrite the application, most likely with Oracle Apex on top of Oracle XE. We faced the problem of how to migrate our data from MySQL to Oracle XE. Word documents stored in blob fields are really not true (binary) .doc files any more, but OLE objects (some binary header plus doc). We thought that finding official documentation about OLE object header would be easy and that all we’ll have to do is to cut off the OLE header from each document file stored in blob field. We’re wrong.
To make long story short, we found the solution on Stephen Lebens site. He wrote MS Access application that can extract (any) OLE content from the mdb file and save the document in native format. You can download ExtractInventoryOLE.zip from here.
First we imported table with OLE objects from MySQL to local mdb file and then extracted the word documents with above application. Several hundreds Word documents where dumped without a hitch in less than a minute.
Here is a screenshot from ExtractInventoryOLE to get a filling:
You can select unique field (in our case it was a primary key for the table; a numeric sequence) that is used to name the documents. The good news is that you can extract the OLE objects even if OLE Server that was used to insert the document is not present/installed on the machine (for example MS Word, Excel etc.).
Protecting passwords with Oracle Wallet
Someone on dba-Village forum asked about how to protect username and password for connecting to RMAN catalog database. Since I’m strongly against using remote os authentication (remote_os_auth=true) in real life production environment, I would probably choose Oracle Wallet for storing credentials for connecting to rman catalog.
Note: With Oracle Wallet implementation we’re relying on file system permissions that protects our wallet; it’s not perfect but I’m happy to trade this “risk” with (in my humble opinion) much riskier approach of using remote os authentication. Anyone with access to the wallet that has auto-login feature turned On, can connect as user stored in the wallet without a password! Approach described in this post should be used seldom and with care.
Here is a mini How-to (I was using Windows 2003 and Oracle11g):
########################################### 1) Create Oracle wallet - the result of this step is directory D:\oracle\rmancat_wallet with two files: cwallet.sso and ewallet.p12 . ########################################### cmd> mkstore -wrl D:\oracle\rmancat_wallet -create Enter password: mysecret PASSWORD_POLICY : Passwords must have a minimum length of eight characters and contain alphabetic characters combined with numbers or special characters. Enter password: mysecret1 Enter password again: mysecret1 #################################################### 2) Adding database user credentials to this wallet ORA11 .... TNS alias for RMAN catalog database rmancat .... database user test .... password for rmancat #################################################### mkstore -wrl D:\oracle\rmancat_wallet -createCredential ora11 rmancat test Enter wallet password: Create credential oracle.security.client.connect_string1 ####################################### 3) Configure sqlnet.ora at client side ####################################### WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=D:\oracle\rmancat_wallet))) SQLNET.WALLET_OVERRIDE = TRUE ######################################## 4) Test connection ######################################## D:\ORACLE>sqlplus /@ora511 SQL*Plus: Release 11.1.0.6.0 - Production on ╚et Jul 10 13:58:00 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> show user USER is "RMANCAT" ######################################## 5) Additional security checks ######################################## I would recommend to save sqlnet.ora and tnsnames.ora separately from the common oracle home, for example we can copy both files to the wallet directory (in our example D:\ORACLE\rmancat_wallet). Make sure that only user executing rman backup has read permissions on this directory. By default Oracle changes permissions only on files cwallet.sso and ewallet.p12, leaving directory permission to be inhereted from the parent (at least that's the case on Windows, I'm not sure about Linux/Unix)! Don't forget to setup environment variable TNS_ADMIN pointing to your wallet directory at the beggining of backup script. ############################## 6) Various handy commands ############################## >> ------------------------------ >> List the content of the Wallet >> ------------------------------ cmd> mkstore -wrl D:\oracle\rmancat_wallet -listCredential Enter wallet password: List credential (index: connect_string username) 1: ora11 rmancat >> ------------------------------------------------- >> Modify credential stored in the wallet >> ------------------------------------------------- cmd> mkstore -wrl D:\oracle\rmancat_wallet -modifyCredential ora11 rmancat newpassword Enter wallet password: mysecret1 Modify credential Modify 1 >> ----------------------------------- >> Deleting credential from the wallet >> ----------------------------------- cmd> mkstore -wrl D:\oracle\rmancat_wallet -deleteCredential ora11 Enter wallet password: Delete credential Delete 1 >> ------------------------------------ >> Adding credential >> ------------------------------------ Remember, you can have only ONE user per TNS alias stored in the wallet. If you need to store two users (schemas) for one TNS, then you'll have to use two wallets! For example, this is possible: > mkstore -wrl D:\oracle\rmancat_wallet -createCredential ora11 rmancat test > mkstore -wrl D:\oracle\rmancat_wallet -createCredential ora12 scott tiger If you try to add second account for the same TNS alias, you'll get error: > mkstore -wrl D:\oracle\rmancat_wallet -createCredential ora12 scott2 tiger Create credential Secret Store error occured: oracle.security.pki.OracleSecretStoreException: Credential already exists In this case you'll need to create new wallet for scott2. ################## 7) References ################## Metalink Note:340559.1 "Using The Secure External Password Store" Chapter 9 Secure External Password Store: http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/cnctslsh.htm#g1033548
dbaportal.eu 
You must be logged in to post a comment.