RMAN in Oracle SQL Developer

I’ve already violated my oath to never use acronyms with the blog title, so for those that do not know what RMAN is, please go read the DOCS.

Recovery Manager (RMAN) is fully integrated with the Oracle database to perform a range of backup and recovery activities, including maintaining an RMAN repository of historical data about backups. You can access RMAN through the command line or through Oracle Enterprise Manager.

Hmmm, remind me to talk to the documentation folks that we now have an interface via SQL Developer!

A new feature in v3.1 of SQL Developer is a DBA interface for RMAN.

This interface has a few wizards. You can click through some screens. We make it REAL easy to shutdown your database for those that do not fully understand RMAN. I just did it, and it was awesome! I asked for Oracle to build me an encrypted backup without having a wallet setup first.

[text]
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on SQLDEV_DISK_BACKUP channel at 03/29…
ORA-19914: unable to encrypt backup
ORA-28365: wallet is not open

RMAN-00571: ===========================================================
released channel: SQLDEV_DISK_BACKUP
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

[/text]

And my database was left in a mounted state instead of an open one. Why was the database shutdown? Because I’m not running in ARCHIVELOG Mode. What is ARCHIVELOG Mode? Step away from your database and put your hands behind your head.

You see, talking about RMAN in SQL Developer quickly becomes a very tricky dance because first I have to talk about RMAN. And showing you how to do cool stuff in SQL Developer without you understanding RMAN is like giving a kid a loaded gun and walking away.

So, what am I left to do? I can’t ignore this awesome new interface in the product. People are asking me for content to help them get acquainted with the feature. So let’s try this.

I will show you some RMAN features in SQL Developer if You promise to already understand RMAN.

I know I can’t get you to sign away your liability and my culpability, but I’ll sleep a bit better knowing you had to read this at some point.

Ok, so where do we start?

You can click in SQL Developer and have an RMAN job kicked off on your server.

There are two options for generating RMAN tasks in SQL Developer. You can:

• Have scripts generated which you will run manually at your leisure
• Have OS scripts generated and ran via DBMS_SCHEDULER

For me the biggest bang for my buck is the ‘Scheduler Job’ selection. It allows me to do some clicks, and have the magic run for me on the server.

Before you can jump into creating backup jobs to be ran via this server mechanism, you need to

Configure RMAN Actions

This will create the database jobs and server directory and scripts required to kick off RMAN backup and recovery operations initiated from SQL Developer. Before you can run this, you’ll need a SCHEDULER CREDENTIAL. Instead of telling you what these are, I’ll just link to Tim’s blog post on oraclebase.com.

Basically this allows the database to login to the host OS. You’ll provide a username and password, so be sure to work closely with your server admin (if that’s not you already!) And what are we going to be doing on the server that requires a login?

Creating some shell scripts mostly. And then running them when you want to do an RMAN operation.

When you’re ready to get started, mouse-right-click on the RMAN Backup/Recovery tree item in the DBA panel.

When this is setup, you’re good to go.

Of course you may want to inspect what just got put on the server before you blindly trust us – I would!

What happens when I go to run something

When I hit ‘Apply’ a new ‘SQLDEV_JOB_XXXXXX.sh’ script will get loaded to the server. It will then get called. The work will get logged to a file of the same name (with a .LOG suffix), and you’ll see a ‘BUSY’ throbber widget in SQL Developer telling you the job is running.

When it’s finished you can check the log directly in SQL Developer.

Or if you’re impatient like me, you can remote into the server and TAIL the log file. That’s when I noticed the job failed and left my database MOUNTed but not OPENed.

Viewing your backup jobs is easy in SQL Developer. Just click on it in the browser. You can then see what happened, what’s on the server, and read the full log without having to actually find the .LOG file.

So where do we go from here?

You don’t need to see posts on specific RMAN activities, right? After reading this, let me know where the grey areas lie, and I’ll be happy to write up some How To posts. Just leave me a comment or two or 10!