Sean's Oracle Share

March 16, 2010

conditional login.sql in the SQLPATH

Filed under: sqlplus — Sean Molloy @ 3:57 am

 

Further to my post on Self-inflicted unable to startup or shutdown database from OEM, I wondered if there might be a way to make the login.sql conditional. 

Background: 

The windows registry HKey_Local_Machine\ORACLE\Key_OraDb11g_home1 key has a Value field named SQLPATH 

When you run a file from insied sqlplus using @ or “start”, sqlplus will search any folders listed in sqlpath for the file. 

Also on starting sqlplus, it will check folders listed in sqlplus for any file named “login.sql”. If it finds a file, then this gets run before a prompt is presented. Also if any “connect /” is done in sqlplus, then a login.sql is again run. 

Problem: 

If my pc does a sqlplus/nolog then I do not want my login.sql to do anything (because there is no connection) 

Likewise once someone is connected, if they do a “connect sys/password as sysdba”, I do not want my login.sql to do anything (namely because this blocks OEM from starting/shutting down my database) 

Otherwise for any non-sys/non-system connect, I do want a login.sql to be run. 

Solution: 

In sqlplus there are some predefined variables. 

You can see these by typing “define”. The following is what you get after a sqlplus/nolog 

SQL> define
DEFINE _DATE        = "15-MAR-10" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "" (CHAR)
DEFINE _USER        = "" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1101000700" (CHAR)
DEFINE _EDITOR        = "Notepad" (CHAR) 

 

After connect as test, you get

SQL> define _user
DEFINE _USER           = "TEST" (CHAR)  

So I created the following files: 

“login.sql” with one line:

@start_&_user..sql

“start_.sql”  and “start_SYS.sql” and “start_SYSTEM.sql” are empty files 

start_TEST.sql with any number of lines to do something useful such as


@@init.sql

This works as planned for “sqlplus/nolog”, “connect sys…” and “connect test/…”
Of course, a big downside here is that for each username you need a file named “start_username.sql”

Danger: 

With any login.sql script you need to worry about someone adding lines to your start_SYS.sql such as

grant dba to bad_guy;

March 10, 2010

Installing OEL 5 on Win 7 Virtual PC

Filed under: OEL — Sean Molloy @ 4:12 am

 

I wanted to setup a virtualized linux environment on my 64 bit windows 7 laptop. (OEL 5 is Oracle Enterprise Linux 5).

I looked at a few alternatives:

  • Virtual PC on Win 7: only supports 32bit OSs and requires hardware virtualization, Linux not supported
  • Virtual PC 2007: Supports 32/64 bit OSs, does not require hardware vitualization, Linux supported
  • Vmware Server: Supports 32/64 bit, linux supported, most popular

I decided to try Virtual PC on Win 7, mostly because my laptop has the hardware virtualization. Also vmware website was not taking registrations last weekend. Also wanted to see if the lack of linux support would cause any issues.

Today, I have discovered Oracle VM VirtualBox. VirtualBox came over with the Sun acquisition and it seems this will be well supported in the future. See http://www.virtualization.info/2010/01/oracle-vm-30-and-virtualbox-roadmaps.html

If I was starting again I would try Oracle VM VirtualBox. But since I have used Virtual PC on win 7 and my experiences may be of benefit to others, I will describe them.

Does your laptop have Hardware Virtualization?

Here’s a link to the Intel utility to run to see if your CPU supports Hardware Virtualization:

http://www.intel.com/support/processors/tools/piu/

Here’s a link to Microsoft’s instructions to configuring the BIOS to enable Hardware Virtualization:

http://www.microsoft.com/windows/virtual-pc/support/configure-bios.aspx

Steps taken

1. Downloaded “XP mode” and “Virtual PC” from http://www.microsoft.com/windows/virtual-pc/download.aspx

2. Installed “XP mode”. Installed “Virtual PC”. It is not necessary to configure “XP mode”, before using “Virtual PC”

3. Downloaded OEL 5 update 4 dvd iso from http://edelivery.oracle.com/linux

4. Ran the “create virtual machine” wizard. Options 1024MB ram, use computer network selected, named virtual hard disk.

I left the Virtual hard disk with its Total Disk size set to 130GB and dynamic type. My budget is 20GB. OEL 5 does not seem to grab the full size, but supports the dynamic nature of the Virtual hard disk file.

5. Changed the vm settings to point the DVD drive to the OEL 5 dvd iso file.

6. Started the vm

7. Selected default Graphical mode. This resulted in:

8. The issue is that Virtual PC supports 16 bit color, but not 24 bit. The linux install uses 24 bit.

The solution I chose was to do the install in text mode. So after restarting the vm, I typed “linux text”

9 I skipped the media test and chose the following options:

language: english
Keyboard: US
initialize drive
Use GRUB Boot Loader
no boot options
no GRUB password
/dev/hda1 for boot loader
eth0 parameters
gateway 192.168.1.1
no primary, secondary DNS ip
hostname: oel5
timezone: Chicago (counld not find St. Paul or Minneapolis!!)
Root password:
Options: “Software Development” and Customize
Defaults + unselected Games, selected Legacy SW development, Server config tools, system tools, Windows File Server, unselected X Software Development, text-based internet, office/productivity, gnome software development.

10. The installer formatted the virtual disk and did the install in 25 minutes.

The install grew the Virtual Hard Disk to 5GB

11. I changed the vm setting for network to “Shared networking”

Post-install issues

After logging in as root and entering “startx” to see the gnome desktop, I had a few issues.

No Mouse:

    The mouse was not working. The solution was to  Edit /boot/grub/menu.lst and add “i8042.noloop” to the end of the “kernal” line. See http://arcanecode.com/2007/04/23/ubuntu-704-and-virtual-pc-2007-no-mouse-issue/#comment-8249

800×600 Screen Resolution:

  My desktop resolution was set to 800×600 and this was too small. The only available resolutions were 800×600 and 640×480. This was the most painful issue to resolve.

It involved adding some entries to /etc/X11/xorg.conf

I have highlighted the additions/changes (highlights visible at the website)


Section "Device"
 Identifier  "Videocard0"
 Driver      "vesa"
EndSection

Section "Monitor"
 Identifier "WXGA+ Inspiron"
 HorizSync 31.5-90.0
 VertRefresh 60.0-60.0
 Option "DPMS"
EndSection

Section "Screen"
 Identifier "Screen0"
 Device     "Videocard0"
 Monitor    "WXGA+ Inspiron"
 DefaultDepth     16
 SubSection "Display"
  Viewport   0 0
  Depth     16
  Modes "1280x1024" "1024x768" "800x600" "640x480"
 EndSubSection
 SubSection "Display"
  Viewport   0 0
  Depth     24
  Modes "1280x1024" "1024x768" "800x600" "640x480"
 EndSubSection
EndSection

These changes made it possible to achieve a 1280×1024 resolution

Lastly, I also needed to set a static ip address.

Future

  • I need to configure samba or ftp to allow the exchange of files between the vm and the win 7 host. Unfortunately I cannot copy/paste between the two. So I have had to use yahoo mail to exchange data
  • My main goal is to install ASM and 11g R2

March 3, 2010

Failing to install the Jan2010 CPU using OEM on Windows

Filed under: CPU,OEM,opatch — Sean Molloy @ 4:37 am

 

  Problem:
Attempting to install the Jan2010 using OEM but failing

  Environment:
Oracle 11g r1
Win 7 64 bit

  Investigation:

The log file is reporting:
———————————————————————
Running prerequisite checks…
Prerequisite check “CheckActiveFilesAndExecutables” failed.
The details are:
Following files are active :
C:\app\sean\product\11.1.0\db_1\bin\oraclient11.dll
C:\app\sean\product\11.1.0\db_1\bin\orageneric11.dll
C:\app\sean\product\11.1.0\db_1\bin\oracommon11.dll
C:\app\sean\product\11.1.0\db_1\bin\orapls11.dll
C:\app\sean\product\11.1.0\db_1\bin\orahasgen11.dll
C:\app\sean\product\11.1.0\db_1\bin\oraocr11.dll
C:\app\sean\product\11.1.0\db_1\bin\oran11.dll
C:\app\sean\product\11.1.0\db_1\bin\oranro11.dll
C:\app\sean\product\11.1.0\db_1\bin\orantcp11.dll
C:\app\sean\product\11.1.0\db_1\bin\oraxml11.dll
C:\app\sean\product\11.1.0\db_1\bin\oracore11.dll
C:\app\sean\product\11.1.0\db_1\bin\orannzsbb11.dll
C:\app\sean\product\11.1.0\db_1\bin\orasql11.dll
C:\app\sean\product\11.1.0\db_1\bin\nmo.exe
C:\app\sean\product\11.1.0\db_1\bin\oranls11.dll
C:\app\sean\product\11.1.0\db_1\bin\oraunls11.dll
ApplySession failed during prerequisite checks: Prerequisite check “CheckActiveFilesAndExecutables” failed.
System intact, OPatch will not attempt to restore the system

OPatch failed with error code = 74
—————————————————————————-
Note: 15 dlls and nmo.exe above.

I used Process Explorer to see which process was using the Dlls.  http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx

A process named “wmiprvse.exe” had the dlls open.

From http://en.wikipedia.org/wiki/Windows_Management_Instrumentation :
  ”  The purpose of WMI is to define a non-proprietary set of environment-independent specifications which allow management information to be shared between management applications. ”

From some scattered references on Metalink, it seems that Oracle does interact with WMI on windows.

For my second attempt, I could have turned off the “Windows Management Instrumentation” service. As I was testing on my own machine, I decided to kill the wmiprvse process which had the Dlls open.

The second attempt again using OEM failed as the nmo.exe process was locked and this again caused the “CheckActiveFilesAndExecutables” failure.

This time I tried an old win3.1 trick. I renamed nmo.exe to nmo.exe_dis. So nmo.exe could be replaced.

The third attempt using OEM failed. OEM did not get as far as checking for pre-requisites. It seems that OEM needed the nmo.exe executable file.
nmo.exe seems to be Oracle Network Manager Objects. This is part of the Oracle networking stack

I read a post on otn which said that Oracle had not guaranteed that OEM could install all patch sets. Some CPU (Critical Patch Update) would need to be installed via opatch. Unfortunately I did not save the url.

Attempt four used opatch. I shutdown the oracle related services. The attempt failed again for “CheckActiveFilesAndExecutables”. This time, onmly the dlls were listed (not nmo.exe).

So attempt five using opatch involved killing the wmiprvse.exe which was locking the DLLs and it did work. The CPU (Critical Patch Update) was installed.

The Readme for the Jan2010 CPU descibes installing using opatch. I did not see any reference to using OEM. I decided to try OEM just as an exercise. I am impressed with the way OEM shutdown the dtabase and services during my attempts.
    My Learnings:

 1. OEM cannot install all CPUs (Critical Patch Update). Sometimes opatch needs to be invoked at the command-line to get around nmo.exe being active.
 2. WMI impacts by loadings some of the oracle dlls. I have not seen any instructions about how to avoid this. Shutting down the “Windows Management Instrumentation” service for the duration of the install would probably work.

Note all of the above was tested on my test installation. If it was my employer’s database, the a lot more care would be applied.

Blog at WordPress.com.