Today I was looking for a way to test RMAN tape backup on a machine without a tape device. I barely remembered that I somewhere read that RMAN supports that. With some cross search on different keywords I finally found Metalink support note 312737.1 “RMAN and Specific Media Managers Environment Variables.” .

Here is the output from my backup test on Windows 2003 (x86) with Oracle 10.2.0.2:

RMAN> run {
2>  allocate channel c1 device type 'SBT_TAPE'
3>   parms="SBT_LIBRARY=oracle.disksbt,ENV=(BACKUP_DIR=D:\BACKUP)";
4>  backup database;
5> }

allocated channel: c1
channel c1: sid=27 devtype=SBT_TAPE
channel c1: WARNING: Oracle Test Disk API

Starting backup at 23.03.10
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=E:\ORADATA\ORA5\DATA\DATA01\SYSTEM01.DBF
input datafile fno=00004 name=E:\ORADATA\ORA5\DATA\DATA01\USERS01.DBF
input datafile fno=00002 name=E:\ORADATA\ORA5\DATA\DATA01\UNDOTBS01.DBF
input datafile fno=00003 name=E:\ORADATA\ORA5\DATA\DATA01\SYSAUX01.DBF
channel c1: starting piece 1 at 23.03.10
channel c1: finished piece 1 at 23.03.10
piece handle=01l99etn_1_1 tag=TAG20100323T094735 comment=API Version 2.0,MMS Version 8.1.3.0
channel c1: backup set complete, elapsed time: 00:05:15
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel c1: starting piece 1 at 23.03.10
channel c1: finished piece 1 at 23.03.10
piece handle=02l99f7i_1_1 tag=TAG20100323T094735 comment=API Version 2.0,MMS Version 8.1.3.0
channel c1: backup set complete, elapsed time: 00:00:05
Finished backup at 23.03.10
released channel: c1

RMAN> list backup;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    1.26G      SBT_TAPE    00:05:13     23.03.10
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20100323T094735
        Handle: 01l99etn_1_1   Media:
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time Name
  ---- -- ---- ---------- -------- ----
  1       Full 26436829   23.03.10 E:\ORADATA\ORA5\DATA\DATA01\SYSTEM01.DBF
  2       Full 26436829   23.03.10 E:\ORADATA\ORA5\DATA\DATA01\UNDOTBS01.DBF
  3       Full 26436829   23.03.10 E:\ORADATA\ORA5\DATA\DATA01\SYSAUX01.DBF
  4       Full 26436829   23.03.10 E:\ORADATA\ORA5\DATA\DATA01\USERS01.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    7.00M      SBT_TAPE    00:00:04     23.03.10
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20100323T094735
        Handle: 02l99f7i_1_1   Media:
  Control File Included: Ckp SCN: 26436829     Ckp time: 23.03.10
  SPFILE Included: Modification time: 22.03.10

RMAN>

Needless to say that Oracle Test Disk API is not meant to be used in production, thought I found some posts on the net in which some folks are using oracle.disksbt API as a workaround for backup of recovery area to disk; thus avoiding the limitation of mandatory usage of SBT channel for backup of recovery area. I certainly don’t recommend abusing this test API for such workaround.

Until Oracle ships a patch for recently reported bug related to some DBMS packages, I decided to follow recommendation on Miladin Modrakovic blog and revoked execute privileges from the public:

revoke execute on DBMS_JVM_EXP_PERMS from public;
revoke execute on DBMS_JAVA from public;
revoke execute on DBMS_JAVA_TEST from  public;

I was utterly surprised when I found out today that OracleXE installs and runs on Windows 7 Enterprise (32-bit) flawlessly. [Note: I run Windows 7 as a guest inside VirtualBox machine with merely 756MB of RAM].

Can’t even remember when was the last time when I worked with XE. Based on my recent experience with Oracle10g R2 installation on my Windows7 notebook I certainly didn’t expect that OracleXE would install without a hitch, but I’m glad that it did! I decided to test OracleXE on Windows 7 for the purpose of small in-house project, nothing spectacular but nevertheless the care should be taken to properly protect OS and XE database.

I installed OracleXE for Western Europe with LATIN1 and then changed the code page to Eastern Europe LATIN2, more precisely to EE8MSWIN1250 as I already wrote once. I don’t need nor want unicode for this particular project.

Of course, first thing every self-respecting DBA should do after Oracle XE installation is this:

When deployed to production adding at least one additional copy of control file plus redo log mirroring is also something it must be done, unless it’s a “toy” database with worthless data.

There is no such thing as 64-bit OracleHTTP server (yet), that’s why you must download and install 32-bit OracleHTTP.

1) Download 32-bit Oracle10g R2 Companion disk for Windows
2) Start OUI from Companion disk (directory) and proceed as:

• Next -> select “Oracle Database 10g Companion Products 10.2.0.1
• Next -> select new Oracle home, such as D:\ORACLE\ORAHTTP
• Next -> select Apache Standalone 10.1.2.0.0
• Next -> check that Product Prerequisite Check is OK and proceed with install
• Next -> check that Oracle HTTP is properly installed trying to connect to http://yourserver:7777

3) Configure DAD (assuming you installed Oracle HTTP in D:\ORACLE\ORAHTTP

• open dads.conf file in D:\ORACLE\ORAHTTP\Apache\modplsql\conf and add your application specific DAD. Sample file:

# ============================================================================ 
#                     mod_plsql DAD Configuration File                         
# ============================================================================ 
# 1. Please refer to dads.README for a description of this file                
# ============================================================================ 
# Note: This file should typically be included in your plsql.conf file with 
# the "include" directive.
# Hint: You can look at some sample DADs in the dads.README file
# ============================================================================ 

AddType text/xml		xbl
AddType text/x-component	htc
Alias /i/ "D:\oracle\ORAHTTP\Apache\Apache\images/"

<Location /yourApp>
  SetHandler pls_handler
  Order deny,allow
  Allow from all
  AllowOverride None     
  PlsqlDatabaseConnectString    yourserver:1521:orcl
  PlsqlAuthenticationMode       Basic
  PlsqlDefaultPage              !yourApp.menu.app
  PlsqlDocumentTablename        htmldocs
  PlsqlDocumentPath             PF
  PlsqlDocumentProcedure        htmldocs.docproc
  PlsqlNLSLanguage              SLOVENIAN_SLOVENIA.EE8MSWIN1250
</Location>

• now open plsql.conf in the same directory as dads.conf and check that dads.conf is included with the statement such as:

include D:\ORACLE\ORAHTTP\Apache\modplsql\conf\dads.conf

4) Change Oracle HTTP default port (optional)

• open http.conf in D:\ORACLE\ORAHTTP\Apache\Apache\conf and change 7777 to 80:

Port 80
Listen 80

5) Restart the HTTP server and check your url

• go to D:\ORACLE\ORAHTTP\opmn\bin and restart the service:

D:\ORACLE\ORAHTTP\opmn\bin>opmnctl status

Processes in Instance: standalone
-------------------+--------------------+---------+---------
ias-component      | process-type       |     pid | status
-------------------+--------------------+---------+---------
HTTP_Server        | HTTP_Server        |    1844 | Alive
LogLoader          | logloaderd         |     N/A | Down
dcm-daemon         | dcm-daemon         |     N/A | Down

D:\ORACLE\ORAHTTP\opmn\bin>opmnctl stopall
opmnctl: stopping opmn and all managed processes...

D:\ORACLE\ORAHTTP\opmn\bin>opmnctl startall
opmnctl: starting opmn and all managed processes...

• point your browser to http://yourserver/yourApp and you should be prompted to logon

6) Troubleshooting

• You should always start troubleshooting Oracle HTTP errors with Apache logs at:

  D:\ORACLE\ORAHTTP\Apache\Apache\logs
  

Jonathan Lewis pointed to an old blog from Arup Nanda, where he describes how he used sqlplus undocumented option (-prelim) to connect to “hanged” Oracle instance. Since I was not aware of this command I decided to try this out with 10g on Windows.

I tried this on Windows 2003:

C:\ORACLE>sqlplus -prelim /nolog

SQL*Plus: Release 10.2.0.2.0 - Production on Tor Avg 11 13:48:35 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

SQL> connect / as sysdba
Prelim connection established
SQL> oradebug setmypid
Statement processed.
SQL> oradebug hanganalyze 12
Hang Analysis in C:\oracle\admin\oraT\udump\oraT_ora_3336.trc
SQL>

and sure enough it can also be done in another way, described by Virag Sharma:

C:\ORACLE>sqlplus /nolog

SQL*Plus: Release 10.2.0.2.0 - Production on Tor Avg 11 13:40:35 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

SQL> set _prelim on
SQL> connect / as sysdba
Prelim connection established
SQL> oradebug setmypid
Statement processed.
SQL> oradebug hanganalyze 12
Hang Analysis in c:\oracle\admin\oraT\udump\oraT_ora_3812.trc
SQL>

Miladin Modrakovic also blogged about sqlplus -prelim option.

I can’t even remember when was the last time I have to troubleshoot (seemingly) hanged Oracle instance, but it’s good to know that Oracle gave us an option in 10g to knock on the back door and ask for a dump of SGA structures.