redoblog

September 16, 2010

VirtualBox and shareable disks

Filed under: Virtualization — redoblog @ 1:24 pm

Now that Oracle has taken ownership of VirtualBox, here’s one of the coolest new feaures. You can now create shareable disks. This means that you can create a Virtualized RAC environment on any OS that supports VirtualBox.

To be able to use this feature, you will need to run at least Version 3.2.8 of VirtualBox which is downloadable from http://www.virtualbox.org

You can see review the 3.2.8 changelog entry here: http://www.virtualbox.org/wiki/Changelog. More details are available at http://www.virtualbox.org/manual/ch05.html#hdimagewrites.

Tim Hall has posted an excellent article on setting up a Virtualized 11.2 RAC on Linux. I’m currently building out a Virtualized 11.2 RAC on 64-bit Windows 7 and will be posting notes on this soon.

Create a shareable disk:

VBoxManage.exe createhd --filename --size --format VDI --variant Fixed --type shareable --remember

Attach a disk to a guest:

VBoxManage.exe storageattach --storagectl "SATA Controller" --port --device --type hdd --medium

Note: The online documentation does not show the added "shareable" parameter at: http://www.virtualbox.org/manual/ch08.html#vboxmanage-createvdi . You can however see this by using the built-in syntax help for the command.

March 14, 2010

Oracle Wallet

Filed under: Connectivity — redoblog @ 10:31 pm

Introduction:

In my last posting I described a method used to hide passwords. A better description would be to obscure passwords. Methods relying on the passwords existing in plain text on a file system seem prone to eventual, accidental or even deliberate discovery by a user. The problem remains of how to distribute passwords securely to end users and client applications. To solve this problem one option is to use an Oracle Wallet external password store.

An Oracle Wallet is as the name implies. Just as you would store credentials in a wallet, you store your username and password in an Oracle Wallet. The advantage however is that the contents of the wallet are not readable. If the wallet is stolen, you can simply change your user password and generate a new wallet thus rendering the stolen wallet unusable.

Wallet Creation – mkstore:

To create an Oracle Wallet the “mkstore” utility is used which can be found under your $ORACLE_HOME/bin directory:

[oracle@magneto ~]$ echo $ORACLE_HOME
/u01/oracle/11201

[oracle@magneto ~]$ which mkstore
/u01/oracle/11201/bin/mkstore

The options to mkstore under 11.2.0.1 are plentiful, we'll be looking at:

    [-wrl wrl]
    [-create]
    [-delete]
    [-list]
    [-createEntry alias secret]
    [-viewEntry alias]
    [-modifyEntry alias secret]
    [-deleteEntry alias]
    [-help]

Wallet Directory:

Multiple wallets may be created, however each should be in it's own directory. I like to create one parent directory named "wallets" with sub-directories beneath for each wallet. A symlink is then used to point to the wallet of interest. I can then easily move between wallets by changing my symlink.

[oracle@magneto wallets]$ pwd
/home/oracle/wallets

[oracle@magneto wallets]$ ls -al
total 20
drwxr-xr-x 5 oracle dba 4096 Mar 14 11:32 .
drwx------ 22 oracle dba 4096 Mar 14 11:31 ..
lrwxrwxrwx 1 oracle dba 8 Mar 14 11:32 current -> redoblog
drwxr-xr-x 2 oracle dba 4096 Mar 14 11:32 redoblog
drwxr-xr-x 2 oracle dba 4096 Feb 16 11:31 wallet1
drwxr-xr-x 2 oracle dba 4096 Feb 16 11:32 wallet2

A simple wrapper script could be written that points the symlink to the desired wallet directory.

Initial Wallet Creation:

To create a wallet, use "mkstore" with the "-wrl" option to point to your directory and the "-create" option to create the wallet. You will be prompted for a password for the wallet.

[oracle@magneto wallets]$ mkstore -wrl ~/wallets/redoblog -create
Oracle Secret Store Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter password: *******************

Enter password again: *******************

#You can see that the wallet files now exist:
[oracle@magneto wallets]$ ls -al ~/wallets/redoblog
total 16
drwxr-xr-x 2 oracle dba 4096 Feb 16 11:37 .
drwxr-xr-x 5 oracle dba 4096 Feb 16 11:32 ..
-rw------- 1 oracle dba 3589 Feb 16 11:37 cwallet.sso
-rw------- 1 oracle dba 3512 Feb 16 11:37 ewallet.p12

Add Default Wallet Entries:

We now add a default username and default user password to the wallet. The default username and password are used whenever the wallet is used. To do this we use the "-wrl" and "-createEntry" options.

# Create the default username entry

[oracle@magneto wallets]$ mkstore -wrl ~/wallets/redoblog -createEntry oracle.security.client.default_username SCOTT;
Oracle Secret Store Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: *********************

# Create the default username password entry

[oracle@magneto wallets]$ mkstore -wrl ~/wallets/redoblog -createEntry oracle.security.client.default_password TIGER;
Oracle Secret Store Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: *********************

List Wallet Entries:

We now list the entries in the wallet using the mkstore "-list" option:

[oracle@magneto wallets]$ mkstore -wrl ~/wallets/redoblog -list
Oracle Secret Store Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: ********************

Oracle Secret Store entries:
oracle.security.client.default_password
oracle.security.client.default_username

Configure the Wallet for use - sqlnet.ora:

Now that we have a wallet, we need to configure it for use. The following entries are added to the sqlnet.ora file:

# sqlnet.ora wallet reference
# Note the use of the symlink directory which I can re-direct to any desired wallet
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =(DIRECTORY = /home/oracle/wallets/current))
)
SQLNET.WALLET_OVERRIDE = TRUE

Connect using the wallet:

We are now ready to connect using the wallet, connections of the form USER@TNS_ENTRY/PASSWORD are now changed to /@TNS_ENTRY. The default username and password in the wallet are used in the connection.

# Traditional connection:

[oracle@magneto sqlnet]$ sqlplus scott@fsfodb/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 16 11:52:51 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> show user
USER is "SCOTT"

# Wallet connection

[oracle@magneto sqlnet]$ sqlplus /@fsfodb

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 16 11:53:21 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> show user
USER is "SCOTT"

February 24, 2010

Hiding Passwords – Security by Obscurity

Filed under: Connectivity — redoblog @ 9:00 pm

I’ve written quite a lot of scripts over the last twenty years and have always tried to secure my database usernames and passwords. I’ve made use of directories with 0400 and 0440 permissions, making the directories only readable by a certain group. Then I’d add the group id to the “run as” user of my script. I’ve gone a little further as well and tried to hide my passwords by obscurity, doing things such as making my files “.passwords” (well, not that name, but I need to be careful how much I give away, after all, this is a blog!).

So if for example I create a file called “/home/ahbaidg/.hidden/.passwords” the file’s contents would look like this:

# File: .passwords
# Notes: Comments cause lines to be ignored
# Entry format: APPID:DBNAME:DBUSER:DBPASS
# APPID – is an application identifier
# DBNAME – is the TNS Names entry to connect to the database
# DBUSER – is the username to connect with
# DBPASS – is the password to use
#
# Billing App entries for my read only reporting user against PRODDB database
BILLINGREPORTS:PRODDB:BILLRO:0r4cle
#
# Reservation App entries for my read only user against RESERVS database
RESRO:RESERVS:RESRO:d4t4b4s3

I’d protect the file from world readability and use “chmod 0400 .passwords” so only my app could read it.

Then in my shell scripts I’d grep through the file and use awk to parse it like so:

#!/bin/bash
export ACCOUNTS=/home/ahbaidg/.hidden/.passwords
export MYAPP=BILLINGREPORTS

DBNAME=`cat $ACCOUNTS | grep -v ^# | grep ^$MYAPP | awk -F: '{print $2}'`
DBUSER=`cat $ACCOUNTS | grep -v ^# | grep ^$MYAPP | awk -F: '{print $3}'`
DBPASS=`cat $ACCOUNTS | grep -v ^# | grep ^$MYAPP | awk -F: '{print $4}'`

The remainder of the script would then connect to the database doing something like:

echo $DBPASS | sqlplus-s -R 3 -L $DBUSER@$DBNAME

The password is in a readable file and can eventually be read if someone had the right privileges on the host. My approach was to hide the password by obscuring it. With sufficient privileges, the password is in plain text and remains readable. How can this be made more secure? One way is to use an Oracle Wallet. A wallet can be distributed to users without sharing the user password and at the same time storing the passwords in an encrypted store.

In my next post I’ll go into how to setup an Oracle Wallet.

Stay Tuned!

November 21, 2009

Free ISO Recorder for Windows

Filed under: Free, Utilities — redoblog @ 12:46 pm

These days an entire OS can be downloaded on a CD or DVD image. These images are typically in the ISO format. You can then mount the ISO file on your Linux OS for viewing using “mount -o loop /tmp/dir1 myisofile.iso” or burn it with a free tool such as cdrecord at the command line or use Brasero for a GUI front end. Windows users generally have to get something like Nero, or Toast of which neither are free. Recently I did some searching and found ISO Recorder which is free! You can download it from http://isorecorder.alexfeinman.com/isorecorder.htm. Now you can get your ISO images burned to disk for free on your windows host. After installing, you simply right click on an ISO image and select “Copy image to CD/DVD”. Happy Burning!

While the product is free, I suggest you donate at least $10 to the author of ISO Recorder if you find the product useful.

November 14, 2009

Using DBA_HIST tables to solve an INI_TRANS enqueue

Filed under: Trouble Shooting — redoblog @ 5:20 am

It’s been a fun and highly educational week for me. I’d like to say thank you to the folks at NoCOUG for putting together this year’s Training Day seminar. I feel privileged that I could attend this seminar which in my opinion was a definite bang for each buck and minute spent. The seminar was on Advanced Troubleshooting, conducted by none other than Tanel Poder.

A developer had experienced what he felt must definitely be a database issue two days ago. He listed the problem  as a transient database problem that must be the db since the app could not be to blame! The developer was insisting that a SQL statement running a SELECT statement was at fault.

What were the sessions doing? What wait events were generated? What SQL was being used?  It hadn’t been a day proper of digesting all the info Tanel shared and I got to apply it (albeit at 3am).

Note: You need the Diagnostic Pack license to use the DBA_HIST* tables, if you don’t have a license visit ashmasters.com for a free implementation.

Here’s how the problem was identified:

1) Got a timestamp from the developer

2) Queried V$ACTIVE_SESSION_HISTORY , the data had already aged out so I decided to use the DBA_HIST tables.

3) queried DBA_HIST_SNAPSHOT using the time range the developer provided to get the SNAP_IDs to use, I didn’t want to have to join just to save some typing and the window was small enough to get one SNAP_ID.

select SNAP_ID from DBA_HIST_SNAPSHOT
where BEGIN_INTERVAL_TIME >= to_date('11-10-2009 17:00:00','MM-DD-YYYY HH24:MI:SS') and
      END_INTERVAL_TIME <= to_date('11-10-2009 18:30:00','MM-DD-YYYY HH24:MI:SS');

SNAP_ID
-------
281

4) I’ve successfully pushed for develeopers to use DBMS_APPLICATION_INFO to set session MODULE, CLIENT_INFO and ACTION, using this with the SNAP_ID I could then extract the top wait events for my specific sessions using MODULE in the following query:

select EVENT, SUM(TIME_WAITED) from DBA_HIST_ACTIVE_SESS_HISTORY
where SNAP_ID = 281 and MODULE = 'Component 10'
group by event 
order by 2 asc, 1 desc;

EVENT                           SUM(TIME_WAITED)
------------------------------- ----------------
                                               1
buffer busy waits                            190
SQL*Net more data to client                 3219
enq: TX - index contention                 22776
log file sync                              60272
db file sequential read                  2032567
read by other session                    3580430

Of all the events returned I knew that I could ignore the last three since our app produces very high data churn and consequently a lot of redo. This left “enq: TX – index contention” as the next event.  This is produced when several sessions are trying to get an ITL entry to UPDATE rows in the same block. Remember, the developer said it was a SELECT that was misbehaving. Since this was an enqueue, it clearly could not be a SELECT statement doing this.

5) I then got more info on the session to see what the sqlid and blocking session were:

select SQL_ID, EVENT, TIME_WAITED,
       P1TEXT||': '||to_char(P1) as P1,
       P2TEXT||': '||to_char(P2) as P2,
       P3TEXT||': '||to_char(P3) as P3,
       BLOCKING_SESSION
from DBA_HIST_ACTIVE_SESS_HISTORY
where SNAP_ID  = 281 and
      MODULE = 'Component 10' and EVENT = 'enq: TX - index contention';

SQL_ID        EVENT                                    TIME_WAITED BLOCKING_SESSION
------------- ---------------------------------------- ----------- ----------------
P1
-------------------------
P2
-------------------------
P3
-------------------------


49717m971cn43 enq: TX - index contention                     22466             3710
name|mode: 1415053316
usn<<16 | slot: 720925
sequence: 2949230

/* Note the SQL_ID was:  49717m971cn43 */

6) I then decided to look at what segments were being used and had ITL waits greater than zero in the same window:

select  OBJ#, sum(ITL_WAITS_TOTAL)
from DBA_HIST_SEG_STAT
where snap_id = 22704
group by obj#
having sum(ITL_WAITS_TOTAL) > 0
order by 2 asc, 1 desc;

OBJ#       SUM(ITL_WAITS_TOTAL)
---------- --------------------
280241                        1
348216                        7
360381                       12
360378                       18
279246                       24
360379                      456

7) The very last OBJ# turned out to be the Primary Key Index on a table.

select OBJECT_NAME from DBA_OBJECTS
     where OBJECT_ID = 360379;

OBJECT_NAME
------------------
PK_COMPONENT_TAB

8 ) I then looked at the SQL_ID obtained in step 5 above:  49717m971cn43

select SQL_ID, SQL_TEXT from DBA_HIST_SQLTEXT
where SQL_ID = '49717m971cn43';

SQL_ID
---------
49717m971cn43 

SQL_TEXT
--------
UPDATE COMPONENT_TAB SET CODEC = -1 WHERE rowid IN (:r0, :r1, :r2, :r3, :r4
, :r5, :r6, :r7, :r8, :r9, :r10, :r11, :r12, :r13, :r14, :r15, :r16, :r17, :r18,
:r19, :r20, :r21, :r22, :r23, :r24, :r25, :r26, :r27, :r28, :r29, :r30, :r31, :
r32, :r33, :r34, :r35, :r36, :r37, :r38, :r39, :r40, :r41, :r42, :r43, :r44,
:r45, :r46, :r47, :r48, :r49, :r50, :r51, :r52, :r53,

.....snipped for brevity ....

:r498, :r499, :r500);

9) I then checked SQL_IDs for other instances of the same component and they were running the exact same SQL.

10) Solution:

  1. Many instances of the same SQL update statement were running.
  2. They all had a large range of rows to update and these sets overlapped, naturally these produced enqueues since these updates update the primary key column and hence the index.
  3. INI_TRANS was set to 5, and we could not raise it to 500, the developer was asked to be less aggressive on the range of rows, or allow each component to update disticnt rows.

NoCOUG / Tanels’ training provided the time and additional insight needed to think things through. Thanks Tanel, thanks NoCOUG and lastly, thank you to my employer for supporting me in my attendance.

September 15, 2009

Reset the APEX admin user password using $ORACLE_HOME/apex/apxconf.sql

Filed under: APEX — redoblog @ 2:53 pm

In playing around with Oracle VM, one of the templates they provide is an ASM backed 11g database, with APEX fully installed. I figured this would be an easy way to poke around in APEX a bit. I had changed the password and could not recall what it was.

I found the script under the $ORACLE_HOME/apex directory named “apxconf.sql” , I set my ORACLE_HOME and ORACLE_SID parameters and was able to successfully change the ADMIN password.

To find your ORACLE_HOME look at your /etc/oratab:

[oracle@oravm2 ~]$ cat /etc/oratab
orcl:/u01/app/oracle/product/11.1.0/db_1:W
+ASM:/u01/app/oracle/product/11.1.0/db_1:Y

And here’s the script being run:

[oracle@oravm2 apex]$ sqlplus “/ as sysdba”

SQL*Plus: Release 11.1.0.6.0 – Production on Tue Sep 15 14:44:28 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @apxconf.sql

PORT
———-
8080

Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user.
Default values are in brackets [ ].
Press Enter to accept the default value.

Enter a password for the ADMIN user [] spreadsheet
Enter a port for the XDB HTTP listener [ 8080] 9090
…changing HTTP Port
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Session altered.
…changing password for ADMIN
PL/SQL procedure successfully completed.
Commit complete.

Done!

September 7, 2009

Running Oracle on EC2

Filed under: AWS, Virtualization — redoblog @ 6:22 pm

Recently, I’ve been playing around with Oracle on EC2. I’ve also been talking with various folks about setting up EC2 instances for Database or any other use. What I’ve found is that folks are interested, but do not know how to create an EC2 instance. It sounds daunting at first, and probably is if you’re not used to the shell prompt and command line tools. The folks at Amazon Web Services have done an amazing job creating a beautiful web based console which makes it very easy to create an EC2 instance.

I gave a presentation on running Oracle on EC2 at the last NoCOUG conference. It covers the setup of an EC2 instance as well as the key points to install Oracle on an EC2 instance. Check it out, you’ll be running an EC2 instance in no time.

If you live in the Bay Area and are starved for Oracle education, you just can’t beat the Northern California Oracle User Group (NoCOUG). For a very affordable rate for an individual, you get four conferences, quarterly subscriptions to the NoCOUG Journal and a well prepared lunch at each quarterly conference. The real deal is the level of technical presentations, presenters from Oracle, industry experts such as Tom Kyte, Cary Milsap and our very own Iggy Fernandez. I just can’t say enough about it, check it out here.

Lastly, I just setup Oracle’s VM Manager and Server… I’ll blog more on this later, stay tuned!

June 10, 2009

EC2 anyone?

Filed under: AWS, Virtualization — redoblog @ 1:56 am

Last week I attended a one day AWS Developer’s Bootcamp. AWS is Amazon’s Web Services.  Next to my NoCOUG membership this was an excellent deal. The cost of the seminar was $225 and covered pretty much all of the AWS service offerings. Here’s a short list of what was covered:

  • AWS Credentials which are needed to setup and manage your AWS services
  • S3, or Simple Storage Service which is a simple bucket based storage service
  • SimpleDB, Amazon’s very simple db
  • SQS, Amazon’s Simple Queue Service
  • EC2, or Elastic Cloud Computing which is a way to have virtual hosts in Amazon’s Data Centers
  • Elastic IP which allows you to add a static IP to your EC2 host
  • Amazon Cloud Front which does a magnificent job of ensuring traffic takes the most efficient route to your EC2 instances
  • Elastic Map Reduce which allows you to setup and run a virtual Hadoop cluster as needed

The tools provided by Amazon also makes these services very easy to use. APIs are provided in languages such as C#, Java, PHP, Perl, Ruby and Python. The EC2 console at http://console.aws.amazon.com makes managing your EC2 instances and devices very easy. At this point I’m considering setting up my Data Guard examples in EC2, this way you can deploy an instance and you’d have a working example!

If you’d like to check out AWS and what it can do for you, visit the AWS website and sign up for a free tour in a city near you at http://aws.amazon.com/startupproject/

You can also search the events for the next Developer Bootcamp coming to  city near you.

Data Guard on EC2 coming soon!

May 28, 2009

Build a VMware Host for under $600

Filed under: Virtualization — redoblog @ 12:32 pm

Here’s a list of parts that I gathered off of tigerdirect.com which you can use to build yourself a pretty decent box for virtualization. You’re ending up with a box that has a four core cpu, eight Gigabytes of RAM and 1.5 Terabytes of disk. I didn’t pay too much attention to the video card as you’ll most likely access the box remotely over a LAN. Here are the specs:

  1. AMD Phenom 9500 x4 CPU and Asus M3N78 Motherboard here for $189.99
  2. 8 GB of Corsair DDR2 RAM (Get 2 kits) here for $46.99 x 2 = $93.98
  3. Seagate 1.5 TB hard drive here for $129.99
  4. Xion case & Power Supply here for $69.99
  5. Radeon Video Card here for $29.99
  6. Shipping and handling estimates at $45.00

Grand total: $588.94

You can cut corners and re-use hardware you may have, even so think about the cost of a single day of Oracle Training, that’s about $1500 the last time I checked. With this you walk away with tools to train yourself repeatedly. For me this has proven to be an invaluable option.

Last point, for the OS I recommend Ubuntu Server 8.04 64-bit AMD edition and apply the updates. VMware Server 2.0 installs flawlessly on this platform.

Good Luck!

May 26, 2009

Data Guard on VMware

Filed under: Data Guard, Virtualization — redoblog @ 9:35 pm

I can’t say enough about Virtualization. With today’s lower prices for hardware, the size of a box you can build for Virtualization is amazing. Just this week I saw a 1.5 TeraByte Disk for $130! You can also get 4GB of RAM for under $50, and a decent AMD Phenom Processor and MBoard combo for under $200. Throw in a case and power supply for $100. Last thing is a video card (some motherboards have on board video and you may not need this) and you’ve got yourself a host beefy enough to build a solid Virtual Machine box sufficient for testing any lab configuration.

I’ll post a list of hardware you can buy to build a decent Virtualization Server soon. For now I’ve started to document my VMware setup at http://ocpdba.net/dataguard/vmwaresetup/index.php. I’ll be updating this as I go along, so check back often.

Older Posts »

The Shocking Blue Green Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.