RMAN in Oracle SQL Developer

thatjeffsmith SQL Developer 2 Comments

Tell Others About This Story:

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.

If you don't listen to me, at least listen to Tim!

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.

Setup scripts to run on the server for you

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.

Configuring your server to be ready to run RMAN via SQL Developer

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!

An example of what gets setup on your database server

What happens when I go to run something

Not a hot backup, remember I'm not running in ARCHIVELOG mode

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.

Viewing RMAN jobs in Oracle SQL Developer

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!

Tell Others About This Story:

Comments 2

  1. Hey Jeff,

    You asked for my feedback on SQL Developer RMAN integration.
    I must admit that I didn’t spend too much time on the task as of now however this is that I think about it.

    In general I am loven it. It makes DBAs to save a bit of time and access the RMAN/Backups related information handy.
    + “Custom Backups” section under “Backup jobs” (you know how to hide things very well) allows quickly generate RMAN scripts without remembering all the syntax and tahn adjust those if necessary.

    There some detailed comments:

    GOOD:
    — I love the fact that we can quickly access RMAN jobs output and other information from SQL Developer. I will use it myself on day to day basis. It will save me connection time looking for where log files are located. Very Cool!
    — I love the fact that I can see the progress of ongoing RMAN jobs. Fantastic 🙂
    — there is an option to build custom backups. The wizards allows you quickly generate an RMAN script. Good 🙂
    — During any RMAN script generation SQL Developer allow to customize the script before saving it (RMAN tab)
    — “Output Rate (Rer Sec)” field in the “Backup Jobs” is very valuable to compare ongoing backup’s rates to previous backups’ rates.

    PLACE FOR IMPROVEMENTS:
    — Default “Create Disk Backup” option creates refreshable images copies. IMHO: Many DBAs wouldn’t expect to see it as a default option. Those who doesn’t know anything about Oracle backups left with this option only by default. Someone would assume that each run of the backup script will create any additional backup and that is false assumption in this case.
    — DELETE OBSOLETE DEVICE TYPE DISK; is put at the very end of the script by default. IMHO: It more efficient from FS space utilization to run it at very beginning.
    — We can’t monitor restoring jobs if DB ins’t opened. IMHO: This is that most of us would like to do. See how my database restore progressing even if the database isn’t opened yet. It is possible to implement. It looks like SQL Developer just looking on some views that are not available if DB ins’t opened and toughing an errors.
    — “Output Rate (Rer Sec)” field in the is very valuable. However I would add Input Rate (as indication how quick we are reading files in compressed backups case) and Estimated time of arrival if the rate will stay the same.
    — I would love to see v$session_longops view output with the time estimates for the ongoing backups somewhere in the SQL Developer backup section

    Keep your good job coming,
    Yury (15y Oracle DBA)

Leave a Reply

Your email address will not be published. Required fields are marked *