Christoph's 2 Oracle Cents

The Quest for the CPU Patch

So I get an email from Oracle Support telling me that I need to patch my database with the latest and greatest Critical Patch Update: CPUJAN2012. Ok. No problem.

I log into My Oracle Support, go to the patch page and search for CPUJAN2012. What does the search return? Nothing! Ugh! So I start searching by “Product or Family”, and get 108 hits. But where is CPUJAN2012?

Well, usually when I try to find something on a web page I hit CTRL-F, but that doesn’t work on My Oracle Support! Now I’m supposed to read through all of the search results? Aaaaargh!

Why can’t Oracle just provide a link inside the notification email to the patch?

After opening and SR on this issue I was asked to look at Doc. ID 1119703.1, the Database PSU-CPU Cross-Reference List. This document actually had the link to the patch and I found it right away.

Am I missing something here? Shouldn’t locating a critical patch be a lot easier? Am I the only one not aware of document 1119703.1?

Free counters

Query up a list of files from OS directory

Ever wish you could get a list of file from your server simply by executing a SELECT statement? Here is a list of PL/SQL and Java programs that allow you to do just that in your Oracle database. For this example I used an Oracle 11.2.0.1.0 Standard Edition database on Red Hat Linux 6.

To get access to a directory on the server on which the database is installed, you need to utilize a Java stored procedure. Not being much of a Java programmer, I took an example from AskTom for that, and made a few modifications. This procedure uses the File class to retrieve directory information, and then writes the file details to a database table. I then used a pipelined function to call the Java code. The piplined function makes it possible to then use the function call in a select statement:

SELECT * FROM TABLE(dir_list_fn('/home/oracle/'));

In Tom’s example, the Java procedure writes the file list to a global temporary table. I found that I couldn’t do that with this method. Since the Java procedure executes DML and piplelined functions do not allow for DML inside them, I had to create a work-around: I had my pipelined function call another function that runs as an autonomous transaction. That function then calls the Java that does the DML. Since now the DML was one session removed from the pipelined function, I had to use a regular table to hold the file list.

The columns returned in the query are:

• FILENAME- name of the OS file/directory
• MOD_DATE – file modification date
• TYPE – File type: f = file or pipe, d = directory

Take a look at the code and try it.

-- Create objects to support SELECT statemtents against server directories.
-- Make sure that the user as the JAVAUSERPRIV
-- Christoph Ruepprich http://ruepprich.wordpress.com
-- cruepprich@gmail.com

create table DIRECTORY_LIST
(
  filename VARCHAR2(255),
  length   NUMBER,
  mod_date DATE,
  type     VARCHAR2(1)
);

CREATE OR REPLACE TYPE t_dir_list_rec AS OBJECT
(
  filename VARCHAR2(255),
  length   NUMBER,
  mod_date DATE,
  TYPE     VARCHAR2(1)
);
/

CREATE OR REPLACE TYPE t_dir_list_tab AS TABLE OF t_dir_list_rec;
/

create or replace and compile java source named "DirList" as
import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;

public class DirList
{
public static void getList(String directory)
                   throws SQLException
{
    //Thanks to asktom.oracle.com for initial code sample

    File path = new File( directory );
    String[] list = path.list();
    String element;

    File fileObject;
    long length;
    String dateStr, type;
    Date now = new Date();
    SimpleDateFormat format = new SimpleDateFormat("dd-MM-yyyy H:m:s");

    for(int i = 0; i < list.length; i++)
    {
        element = list[i];
        fileObject = new File(directory + File.separatorChar + list[i]);
        length = fileObject.length();
        if (fileObject.isDirectory()) {
          type = "d";
        } else if (fileObject.isFile()) {
          type = "f";
        } else {
          type = "?";
        }

        java.util.Date d = new java.util.Date(fileObject.lastModified());
        dateStr = format.format(d);

        #sql { INSERT INTO directory_list (filename,length, mod_date, type)
               VALUES (:element, :length, to_date(:dateStr,'dd-mm-yyyy hh24:mi:ss'), :type) };
    }
}

}
/

create or replace procedure get_dir_list( p_directory in varchar2 )
as language java
name 'DirList.getList( java.lang.String )';
/

CREATE OR REPLACE PROCEDURE call_get_dir_list(p_directory IN VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  EXECUTE IMMEDIATE 'truncate table directory_list';
  get_dir_list(p_directory);
  COMMIT;
END;
/

CREATE OR REPLACE FUNCTION dir_list_fn(p_directory IN VARCHAR2)
  RETURN t_dir_list_tab
  PIPELINED IS
  TYPE l_cur_type IS REF CURSOR;
  l_cur l_cur_type;
  l_rec directory_list%ROWTYPE;
  l_sql VARCHAR2(1000);
  x     NUMBER;
BEGIN
  l_sql := 'SELECT * FROM directory_list';
  call_get_dir_list(p_directory);

  OPEN l_cur FOR l_sql;
  LOOP
    FETCH l_cur
      INTO l_rec;
    EXIT WHEN l_cur%NOTFOUND;
    PIPE ROW(t_dir_list_rec(filename => l_rec.filename,
                            length   => l_rec.length,
                            mod_date => l_rec.mod_date,
                            TYPE     => l_rec.type));
  END LOOP;

  RETURN;

END dir_list_fn;
/

col filename for a30
col type for a5
set pagesize 30
set linesize 100
set termout on feedback on

SELECT * FROM TABLE(dir_list_fn('/')) ORDER BY filename;

Formatting a USB Drive on Linux

This is something I just don’t do often enough to remember all the steps. So here is a little reminder for how to format a USB drive.

• Insert the drive into the USB port
• Check the bottom of /var/log/messages to see which device it is. You’ll see a message that looks something like this:

   Nov 4 08:44:38 svr: scsi6 : SCSI emulation for USB Mass Storage devices
   Nov 4 08:44:44 svr: Vendor: PNY Model: USB 2.0 FD Rev: 1638
   Nov 4 08:44:44 svr: Type: Direct-Access ANSI SCSI revision: 00
   Nov 4 08:44:44 svr: SCSI device sdd: 31703040 512-byte hdwr sectors (16232 MB)
   Nov 4 08:44:44 svr: sdd: Write Protect is off
   Nov 4 08:44:44 svr: sdd: assuming drive cache: write through
   Nov 4 08:44:44 svr: SCSI device sdd: 31703040 512-byte hdwr sectors (16232 MB)
   Nov 4 08:44:44 svr: sdd: Write Protect is off
   Nov 4 08:44:44 svr: sdd: assuming drive cache: write through
   Nov 4 08:44:44 svr: sdd: sdd1

In this case it is device sdd.

• Format the device (in this case sdd) with fdisk:

  fdisk /dev/sdd

  • use ‘p’ to print the current partition table
  • use ‘d’ to delete partition(s)
  • use ‘n’ to create a new primary partition (p)
  • use ‘p’ to print the new partition table

  It should look something like this:

  Device Boot      Start         End      Blocks   Id  System
  /dev/sdd1               1        2293    15849189   83  Linux

  • use ‘w’ to write the new partition table

   

• Create a new file system on the flash drive:

  mkfs -t vfat /dev/sdd1

  In this example I use the vfat filesystem. You can choose other filesystems like ext2 or ext3. To get a list of filesystem available (maked in blue below) use:

  ls -l /sbin/mkfs*
  -rwxr-xr-x. 1 root root 10336 May 21  2011 /sbin/mkfs
  -rwxr-xr-x. 1 root root 26208 May 21  2011 /sbin/mkfs.cramfs
  -rwxr-xr-x. 5 root root 66272 Mar 29  2011 /sbin/mkfs.ext2
  -rwxr-xr-x. 5 root root 66272 Mar 29  2011 /sbin/mkfs.ext3
  -rwxr-xr-x. 5 root root 66272 Mar 29  2011 /sbin/mkfs.ext4
  -rwxr-xr-x. 5 root root 66272 Mar 29  2011 /sbin/mkfs.ext4dev
  lrwxrwxrwx. 1 root root     7 Nov  6 20:17 /sbin/mkfs.msdos -> mkdosfs
  lrwxrwxrwx. 1 root root     7 Nov  6 20:17 /sbin/mkfs.vfat -> mkdosfs

• Create a directory into which to mount the flash drive:

  mkdir /media/usb

• Mount the flash drive:

  mount /dev/sdd1 /media/usb

And that should do it.

Renaming a database as easily as 1..23

In this example I renamed a database from cmr1 to vrep. I also include steps to make sure that the datafiles, control files, and audit files reside in directories that contain the SID name.

1. Log in as sysdba.
2. Verify the database name

SYS@cmr1 AS SYSDBA>select name from v$database;

NAME
---------
CMR1

3. alter system switch logfile;
4. create pfile from spfile;
5. alter database backup controlfile to trace;
6. shutdown immediate;
7. Edit the tracefile from step 5:
   Find the line reading: Set #2. RESETLOGS case
8. Remove all lines above this line.
9. Change the line containing the database name from

   CREATE CONTROLFILE REUSE DATABASE cmr1 RESETLOGS NOARCHIVELOG

to

CREATE CONTROLFILE SET DATABASE vrep RESETLOGS NOARCHIVELOG

Note, in my case, the database is running in noarchive log mode. The corresponding line reads ARCHIVELOG otherwise.

10. Rename all directory references from cmr1 to vrep
11. Remove the line reading RECOVER DATABASE USING BACKUP CONTROLFILE.
12. Remove lines starting with # and all unneccesary comments at the end of the file.
13. Rename this file to db_rename.sql
14. Change directories to the directory where all the datafiles are.
15. Rename the directory from cmr1 to vrep (this should reflect the path from the db_rename.sql file).cd
16. Find the control files and rename them to control01.ora.cmr1.bak
17. Create an admin directory in your $ORACLE_BASE with all the sub directories:
18. mkdir /u01/app/oracle/admin/vrep/udump|bdump|dpdump|pfile
19. Rename the pfile created in step 4 to initrep.ora (init<SID>.ora).
20. Edit the pifle and change all cmr1 references to vrep.
21. Set the environment to register the new database SID. (Typically this would be in the .bash_profile. You should just have to set ORACLE_SID=vrep)
22. Log into sql*plus as sysdba and run db_name.sql
23. Also make sure to reflect the new database name in the /etc/oratab file

SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 27 14:12:44 2011

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

Connected to an idle instance.

SYS@vtgt AS SYSDBA>@db_rename
ORACLE instance started.

Total System Global Area 539848704 bytes
Fixed Size 1337748 bytes
Variable Size 411043436 bytes
Database Buffers 121634816 bytes
Redo Buffers 5832704 bytes

Control file created.

Database altered.

Tablespace altered.

SYS@vtgt AS SYSDBA>select name from v$database;

NAME
---------
VTGT

SYS@vtgt AS SYSDBA>

Troubleshooting:

Typically, if things go wrong, there may be a typo involved. Check the db_rename.sql and the init<SID>.ora file. Make sure all the paths are valid and that the directories referenced acutally exists, and that they have the correct permissions.

If the db_rename.sql fails, you may not be able to shut down the database. In that case you may want to find the right OS processes and kill them manually.

Cloudy Days Ahead

Oracle Announced the Oracle Cloud at Open World this year

For more information go to cloud.oracle.com.

Datapump API Presentation at DOUG

Yesterday, the Dallas Oracle Users Group (DOUG) was kind enough to allow me to give a presentation on the Oracle Datapump API.

In this presentation I dove into the dbms_datapump package, talked about its procedures and functions and how to use them to perform Datapump exports and imports.

I have made a copy of my presentation slides available on DropBox. There is also a zip file with some example scripts, showing how to perform various tasks using the Datapump API.I would be happy for any feedback regarding the presentation, slides, or scripts.

Installing a new login screen on RHEL

So you’re running Oracle on REHL, and you’re tired of looking at the same old, boring login screen. Or you may be running multiple VMs and want to make a visual difference between them. Here is a way to edit the GDM (Gnome Display Manager) to add a few more themes that will provide you with a different login screen, and possibly, a cool looking Oracle VM.

To get some new GDM themes go to http://art.gnome.org, select Themes>GDM Greeter, and choose download the ones you like. Once downloaded, unzip them to /usr/share/gdm/themes.

RHEL 5 comes with a number of commands that allow you to manage the available GDM themes. To test a theme and see what it looks like, you can use:

$ gdmthemetester console <themename>

Once you’ve decided on a theme, use the following command to select it:

$ gdmsetup <themename>

Finally, apply the theme:

$ gdm-restart

Have fun!