Apr 26 2010

Install 11g Release 2 Grid Infrastructure for Standalone Server on Windows 7 for Sandbox

Category: 11g,Database — ittichai @ 9:08 pm

Oracle 11g Release 2 for Windows was just released this month. With the availability of the grid infrastructure in this version, I plan to install it on my Windows 7 desktop to see what it can do even if it is just on stand alone environment.

In order for database to use Automatic Storage Management (ASM), it requires the Grid Infrastructure. In addition to ASM, Grid Infrastructure will also provide Oracle Restart to manage the Oracle processes (database, listener, and ASM).

One of the first issues I’ve encountered is the new requirement that the clusterware files (OCR & Voting) must be on ASM. I have to admit even though I’ve done ASM on Solaris and Linux before, but never on Windows. Since this is mandatory, I will give it a try. And since I will use ASM for clusterware files, I plan to use it for database data files as well.

In order to use ASM, I’m required to provide the unformatted (raw) basic disks. I plan to use the existing disks without adding new physical ones. Fortunately in Windows 7, I can use the disk management (diskmgmt.msc) tool to shrink volume and create a new logical disk from claimed space. Note that you may have multiple physical disks on your machine, but ASM supports and recognizes only logical drives on the Basic disk (not Dynamic disk). Click here if you’re interested in differences between Basic and Dynamic disks.

Once data volume is shrunk, I can create a new volume and then a logical drive. The new drive must not be formatted or having a drive letter assigned to it. Here is the guidelines from Oracle document on “create disk partitions”.

To use ASM with direct attached storage or SAN, the disks must be stamped with a header. This can be accomplished by using either asmtool (command-line version) or asmtoolg (GUI version). Since we will install Oracle grid infrastructure in interactive mode, the asmtoolg will be called during the configuration. Somehow, if I tried to launch the asmtoolg outside Oracle grid infrastructure installation, I always encountered error with no disks found. However, within the Oracle grid infrastructure installation, there is no issue.

In general, the installation went well. I’ve encountered few issues which I’ve documented them in the documents below. The snapshots of steps here are for educational purpose only.

Windows 7 – Disk Preparation for ASM

Oracle 11g R2 Grid Infrastructure for Standalone Server Installation on Windows

Oracle 11g R2 Software Installation for Single Instance Database on Windows

Oracle 11g R2 Database Creation using ASM on Windows

Tags: , , , , , , , , , ,

Apr 16 2010

Alter Tablespace Read Only Hangs

Category: Database — ittichai @ 2:36 pm

I found this tip from Oracle document while looking for a solution for session hung at the SQL prompt after issuing “alter tablespace read only”. Usually “alter tablespace read only” executes very quickly. However, the likelihood cause of waiting for tablespace to become read-only is due to existing in-flight transactions started before “alter tablespace read only” are still running.

To identify these transactions that are preventing the read-only tablespace is to first identify the “alter tablespace read only” session.

SELECT SQL_TEXT, SADDR
FROM V$SQLAREA,V$SESSION
WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS
AND SQL_TEXT LIKE 'alter tablespace%';

SQL_TEXT                                 SADDR
---------------------------------------- ----------------
alter tablespace tbs_tts1 read only      0000040634C0D8B8

Based on the identified session address and start SCN number, we can find the earlier executions before the read-only statement by querying the v$TRANSACTION order by ascending start SCN.

SELECT SES_ADDR, START_SCNB
FROM V$TRANSACTION
ORDER BY START_SCNB;

SES_ADDR         START_SCNB
---------------- ----------
0000040634C254F8 2976616884    --> Waiting on this transaction
0000040634C0D8B8 2980274305    --> alter tablespace read only
0000040634C53858 2980283454

From the session address of the blocking transaction, we then can find information about that session.

SELECT T.SES_ADDR, S.USERNAME, S.MACHINE
FROM V$SESSION S,  V$TRANSACTION T
WHERE T.SES_ADDR = S.SADDR
ORDER BY T.SES_ADDR

SES_ADDR          USERNAME              MACHINE
----------------  --------------------  ------------------
0000040634C254F8  RT_ADMIN              isdweb1

Once the session is identified, an appropriate action whether to terminate this session can be decided. In our case, it was just a run-away session. Once terminated, alter tablespace read only completed right away.

Reference: Oracle Database Administrator’s Guide 11g Release 1 (11.1) -  Making a Tablespace Read-Only

Tags: , , , , , , ,