Job Notification Package

One interesting features of the Oracle Scheduler is the job notification package.

The job notification package can be downloaded here:Oracle Scheduler

It is very easy to install, simply run the job_notification.sql script and provide the outgoing e-mail SMTP server and port number.

In order to get notified about a job’s completion, I ran the following command:

EXEC add_job_email_notification(‘RUN_SERCICE_120DAYS_JOB’,’’

Unfortunately, this command returned an error, stating that the job name was too long for the character buffer:

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Since the job name is already in place and used accross all of the environments involved, I wanted to keep the job name as is. In order to do so, the solution is the following:

Simply increase the size of the “notifier_job_name” variable from VARCHAR2(30) to VARCHAR2(60) in the “add_job_email_notification” procedure. That simple change did the trick!

There is an OTN Thread about this same issue.




Leave a comment

Control File Auto Backup Not copied to the Flashback Recovery Area (FRA)

During a migration exercise, a database was upgraded from to,
the OS was also ported from Fedora core 4 32bits to Oracle Enterprise Linux 4 64 bits.

The original database didn’t have Flashback Recovery Area configured because of the previous limited disk space, on this new box, since the disk space is not anymore an issue, the FRA has been enabled.

After the migration and the completion of the initial manual backup, it was time to test the RMAN backups.
I was surprised to realize that the copy of the controlfile was not copied in the FRA Backup structure along with the backupset. (eventhough the controlfile autobackup was setup)

In order to troubleshoot, I issued the following RMAN command:

RMAN> backup current controlfile;

The controlfile has been copied to the following directory:

Also, there was no indication of any wrong value when running the RMAN show all; statement.

So I did a quick research in metalink and I found the following solution:

By simply running the following RMAN command:
The problem was solved.

While running a new RMAN backup, the controlfile is now being copied in the right directory:

Starting Control File and SPFILE Autobackup at 08-JUL-07
piece handle=/OraArch1/ORCL/autobackup/2007_07_08/o1_mf_s_627424692_3930vocb_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 08-JUL-07

Leave a comment

Data Pump and Streams Pool

Last week, while exporting a couple of tables for a BI project using the Data Pump utility, the following error occurred:

ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_SCHEMA_01 for user SCOTT
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPV$FT_INT”, line 600
ORA-39080: failed to create queues “KUPC$C_1_20070604114151” and “KUPC$S_1_20070604114151” for Data Pump job
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPC$QUE_INT”, line 1580
ORA-00832: no streams pool created and cannot automatically create one

This database is not setup with the “Automatic Shared Memory Management” feature –> ASMM, all of the memory parameters are manually set.

You might wonder why ? Well, the reason why this database was not setup to use ASMM is that the database resides on a 32bits OS and uses RAMFS and HugePages to enable the use of 10GB of SGA. When using RAMFS and HugePages, it is required to manually set each memory parameter, I will discuss this feature in details next week.

Also, the « streams_pool_size” parameter was not setup since Oracle Streams is not used on this database, and I was surprised to see that the Data Pump utility needed a Streams Pool to actually work !

The Oracle Documentation explains the following Oracle Streams feature:Streams_pool_size Documentation“If both the STREAMS_POOL_SIZE and the SGA_TARGET initialization parameters are set to 0 (zero), then, by default, the first use of Streams in a database transfers an amount of memory equal to 10% of the shared pool from the buffer cache to the Streams pool”.

As we can see, Oracle did its homework when it comes to the use of Streams when no memory has been explicitly allocated (streams_pool_size). I am wondering why the Data Pump utility won’t work as per the same logic ? (if no streams_pool is setup, then have the Data Pump automatically grab the memory it needs from the shared_pool or the buffer_cache.) Maybe a new feature in 11g ??

Once the streams_pool_size parameter has been configured, the data pump worked just fine.

1 Comment