The Quest for the CPU Patch
January 20, 2012 at 3:06 pm | Posted in Oracle Developement | Leave a commentSo 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?
Query up a list of files from OS directory
December 16, 2011 at 12:18 pm | Posted in Oracle Developement | Leave a commentEver 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;
How to quickly remove those pesky line numbers from copied code.
December 15, 2011 at 11:01 am | Posted in Oracle Developement | Leave a commentTags: gvim, sql, sql*plus, vi
If you’re like me, you cut & past tons of code. Especially from sites like Ask Tom. The trouble is however, that often line numbers are included in the copied text, and you have to remove those before you can paste it into your development environment.
If you use vi or gvim, you can quickly remove the line numbers and empty spaces from your code with this command:
%s/^ \{1,}[0-9]\{1,} //
Good luck!
An AWKward look at tkprof.
December 1, 2011 at 1:13 pm | Posted in Oracle Developement, Oracle Performance, Oracle Tools | Leave a commentawk '/^call/' tk.txt | head -n 1; awk '/^total/' tk.txt | sort -k 4 -nr | head -n 5
call count cpu elapsed disk query current rows total 1 0.07 332.15 13 26 51 1 total 1 0.07 332.15 13 26 51 1 total 698231 48.27 72.56 478 57114 5254875 2260618 total 15 14.42 17.73 15 9249 2226312 139033 total 12 12.77 16.87 23 10983 2084232 107561
call count cpu elapsed disk query current rows
count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call
awk 'tolower($1)~ /(^select)|(^insert)|(^update)|(^delete)|(^declare)|(^begin)/,/^$/ { print NR "\t" $0 } /^call/ { print NR "\t" $0 } /^total/ { print NR "\t" $0 "\n"}' tk.txt | less
10628 DELETE FROM SOME_TABLE
10629 WHERE
10630 STATUS = 'Q'
10631
10633 call count cpu elapsed disk query current rows
10639 total 2 0.00 0.00 0 8 0 0
10646 BEGIN some_package.some_procedure; END;
10647
10649 call count cpu elapsed disk query current rows
10655 total 1 0.07 332.15 13 26 51 1
10662 BEGIN
10663 MY_UTILITIES.PRC1;
10664 EXCEPTION
10665 WHEN OTHERS THEN
10666 LOG_ERROR(SYS_CONTEXT('USERENV'
10667 ,'SESSIONID')
10668 ,PRC1);
10669 END;
10670
10671 call count cpu elapsed disk query current rows
10677 total 2 0.00 0.00 0 0 0 1
:/332.15
Formatting a USB Drive on Linux
November 4, 2011 at 10:27 am | Posted in Oracle Developement | Leave a commentThis 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
October 27, 2011 at 2:24 pm | Posted in Oracle Developement | Leave a commentIn 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.
- Log in as sysdba.
- Verify the database name
- alter system switch logfile;
- create pfile from spfile;
- alter database backup controlfile to trace;
- shutdown immediate;
- Edit the tracefile from step 5:
Find the line reading: Set #2. RESETLOGS case - Remove all lines above this line.
- Change the line containing the database name from
CREATE CONTROLFILE REUSE DATABASE cmr1 RESETLOGS NOARCHIVELOG
- Rename all directory references from cmr1 to vrep
- Remove the line reading RECOVER DATABASE USING BACKUP CONTROLFILE.
- Remove lines starting with # and all unneccesary comments at the end of the file.
- Rename this file to db_rename.sql
- Change directories to the directory where all the datafiles are.
- Rename the directory from cmr1 to vrep (this should reflect the path from the db_rename.sql file).cd
- Find the control files and rename them to control01.ora.cmr1.bak
- Create an admin directory in your $ORACLE_BASE with all the sub directories:
- mkdir /u01/app/oracle/admin/vrep/udump|bdump|dpdump|pfile
- Rename the pfile created in step 4 to initrep.ora (init<SID>.ora).
- Edit the pifle and change all cmr1 references to vrep.
- 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)
- Log into sql*plus as sysdba and run db_name.sql
- Also make sure to reflect the new database name in the /etc/oratab file
SYS@cmr1 AS SYSDBA>select name from v$database; NAME --------- CMR1
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.
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
October 26, 2011 at 1:42 pm | Posted in Oracle Developement | Leave a commentOracle Announced the Oracle Cloud at Open World this year
For more information go to cloud.oracle.com.
Datapump API Presentation at DOUG
October 21, 2011 at 8:53 am | Posted in Oracle Developement | 2 CommentsYesterday, 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
October 11, 2011 at 8:43 am | Posted in Oracle Developement | Leave a commentSo 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!
Blog at WordPress.com. | Theme: Pool by Borja Fernandez.
Entries and comments feeds.


