total abuse of technology

I had a (for my environment) unusual request:
After the migration of a Repository Database from 9i to latest 10g I was asked to keep a backup of the old DB for at least 3 years.
This does not sound very unusual, but it's not that simple in our environment. We do only keep backups for weeks to some month, worst case. I also cannot just backup the datafiles at all: The old database run on Solaris, but we are switching to Linux right now. With just some bad luck I would not have any system to restore (or open) this database backup at all.
This brought me to another solution; in my point of view it was not worth to write a blog about it, but I was asked by Leighton L. Nelson and so I write:

1. export of the full database
   I run a simple export of the database. There is no expdp in 9i, so the choice was easy.
   
   
2. compress the files
   the dump (and the logfile!) where tared together and compressed. Just to save space.
   
   
3. prepare a proper store
   As mentioned above, there is no dedicated system for this purpose. So I had to prepare a place where the dump is safe. As a DBA, of course I know a good place to store data: A database!
   First a DBFS came to my mind. But the DB is in Version 10.2 - no DBFS.
   But it's quite simple to do the important steps manually:
   

   create tablespace old_dump datafile '+<DG>' autoextend on;
   create user old_dump identified by dump_old1 default tablespace old_dump;
   GRANT CONNECT, CREATE SESSION,  CREATE TABLE to old_dump;
   alter user old_tech_dump quota unlimited on old_dump;
   
   connect old_dump/dump_old1
   
   create table old_dump_store 
   (id integer primary key, description VARCHAR(2000), file_store BLOB) 
   LOB (file_store) STORE AS  SECUREFILE 
   (TABLESPACE old_dump DISABLE STORAGE IN ROW   NOCACHE LOGGING);
   

   
   
4. insert the dump (and some metadata)
   
   There is a nice way in SQL Developer to load a file to a BLOB. It's just so simple.
   At last some words in the comment field are worth - so everyone knows what's inside the BLOB.
   

It still might sound strange to save the dump of an old database into it's descendant. But at the end: do you know a better place to store data than a database?

restore DBMS_SCHEDULER.CREATE_CREDENTIAL cleartext password

If you want to use Oracle file watcher, you need to Create a Credential. As there a password needs to be stored in the database, Oracle tries to save it in a secure way. But as the password must be decrypted for the purpose to login on the file watchers agent side, it is not safe at all:
The credentials are stored with DBMS_SCHEDULER.CREATE_CREDENTIAL. Here an example:

exec DBMS_SCHEDULER.CREATE_CREDENTIAL(
  credential_name => 'local_credential',
  username => 'oracle',  password => 'welcome1');
exec DBMS_SCHEDULER.CREATE_CREDENTIAL(
  credential_name => 'local_credential2',
  username => 'oracle2', password => 'welcome1');


It's quite easy to see the values again:

select o.object_name credential_name, username, password
 FROM SYS.SCHEDULER$_CREDENTIAL c, DBA_OBJECTS o
 WHERE c.obj# = o.object_id;

CREDENTIAL_NAME    USERNAME PASSWORD
------------------ -------- ------------------------------------
LOCAL_CREDENTIAL   oracle   BWVYxxK0fiEGAmtiKXULyfXXgjULdvHNLg==
LOCAL_CREDENTIAL2  oracle2  BWyCCRtd8F0zAVYl44IhvVcJ2i8wNUniDQ==


At least the password is somehow encrypted, and even the password was welcome1 for both credentials, the encrypted string is not identical.

Nothing to blame here, but I mentioned, the password can be decrypted. So let's do so:

SELECT u.name CREDENTIAL_OWNER, O.NAME CREDENTIAL_NAME, C.USERNAME,
  DBMS_ISCHED.GET_CREDENTIAL_PASSWORD(O.NAME, u.name) pwd
FROM SYS.SCHEDULER$_CREDENTIAL C, SYS.OBJ$ O, SYS.USER$ U
WHERE U.USER# = O.OWNER#
  AND C.OBJ#  = O.OBJ# ;

CREDENTIAL_OWNER CREDENTIAL_NAME      USERNAME PWD
---------------- -------------------- -------- --------
SYS              LOCAL_CREDENTIAL     oracle   welcome1
SYS              LOCAL_CREDENTIAL2    oracle2  welcome1


Can you see it? It's there. Try it at home!
I don't blame anyone here. It's hard to store anything really safe in case you need to decrypt it also.
But don't expect your password save, if you store it with DBMS_SCHEDULER.CREATE_CREDENTIAL.
Maybe it's slightly to easy to use DBMS_ISCHED.GET_CREDENTIAL_PASSWORD (ok, only SYS can do so) but even it might be slightly more difficult in the future, the basic problem will still exist.

some tracing events in DBMS_SCHEDULER

I currently have the fun to review DBMS_SCHEDULER. As I'm always interested in ways to trace anything, to dig deeper in case of problems, I searched for ways to trace it.
As I did not find a collected list of events anywhere, I start them here. It's by far not a complete list, so feel free to discuss and contribute, if you want!

event 10862

resolve default queue owner to current user in enqueue/dequeue

Cause: resolve default queue owner to current user in enqueue/dequeue.

Action: turn on if client wish to resolve the default queue owner to the current user. If not turned on, the default queue owner will be resolved to the login user.

This event is not checked the way you might imagine. Just in the area of REMOVE_JOB_EMAIL_NOTIFICATION if it's 0, it's set to 1 for a call of DBMS_AQADM.REMOVE_SUBSCRIBER and set to 0 afterwards.

27401

scheduler tracing event

• bit 0x10000 - Logging e-mail to table and returning
  bitand( ,65536)
  logs informations about sending emails into table sys.scheduler$_sent_emails
  
• bit 0x20000 - start DBMS_DEBUG_JDWP.CONNECT_TCP in file watcher
  bitand( ,131072)
  starts DBMS_DEBUG_JDWP.CONNECT_TCP on localhost, port 4444
  I'm not sure if I like this event. In general I don't want any software opening connections without my knowing. And I could not find this documented anywhere.
  Is it fair to call this a backdoor?
  
• bit 0x40000 - starts tracing in file watcher
  bitand( ,262144)
  logs informations about file watcher into trace file
  

27402

scheduler tracing event

• bit 0x40 - starts tracing about emails
  bitand( ,64)
  similar to event 27401 bit 0x10000, but tracefile instead of table
  
• bit 0x80 - starts tracing about emails
  bitand( ,128)
  logs information about email jobs into trace file
  
• bit 0x100 - starts tracing in chains
  bitand( ,256)
  logs information about chains into trace file

I guess there is at least also a bit 0x200, but could not prove it right now.

27403

scheduler stop job event

I did not find anything about it yet. comments are most welcome!

get your traces - yourself

I'd like to mention a small peace of software. It's called MrTrace and available in Version 2.0.0.43 right now. For me it's a tool to save time. So what is it doing at all?
MrTrace is a plugin for Oracles SQL Developer to access tracefiles via SQL Developer. It's previous version could only access the tracefile for the statement you just executed. But since version 2 you anyone with the right permissions access any tracefile in the trace directory.

For a DBA it does not sound spectacular to access tracefiles, but it can be quite annoying to get and distribute tracefiles for developers. In my current company there is no OS-access to databae servers for anyone except DBAs and OS-admins. this means someone must copy over the traces to make them accessible for others. It's not a complex task, but it's disturbing.
With MrTrace I can grant anyone who knows how to use SQL Developer permissions to access trace files. So it saves a lot of time, for me and the developer. At a price of less than us$50 it should amortize in no time, if you have a diligent developer.

A list of my very private findings:

PROs:

• it's easy to install (on the client side) 
• it's not OS-dependent - you can apply it on any client-OS where you can start SQL Developer
• the installation script for the database user and objects is not wrapped. So you can review and even change this part of the software. (in my case, we have a PASSWORD_VERIFY_FUNCTION enforced in my company. I needed to edit the installation script to create the user MRTRACE.
• The support of Method R is great! I had the joy to participate in their beta program for version 2 of MrTrace. We had some nice conversations.  

CONs:

• You need SQL Developer for the client side. - No big deal for me, but in some companies that might be a problem.
  
• MrTrace needs java to do some tasks. Unfortunately there is no method to list the content of a directory, so java is needed. 
• OS-commands like ls,  find and xargs are used. There is nothing bad about these commands, but I don't see anything they do what cannot be done in java directly. So for me it increases complexity without a need. 

And no, I am not an employee of Method R, the only relation is the software license I bought myself.