Automating Oracle Snapshots

[GraceO]

We've been using OnlineJFS snapof= to create snapshots to backup our Oracle filesystems for a few months now and things have been going smoothly. (Much thanks to A. Clay Stephenson - ThreadId=1127455) We have not automated the process for fear of killing a job or process prematurely when the database is shutdown for the snapshot.

Does anyone have a script to check if there are any users running jobs/processes on the database before doing a shutdown? Is there a 'nice' way to shutdown?

We are running HP-UX 11i v2 (B.11.23) on an rp3440 server.

Any help would be greatly appreciated!

[Ben Dehner]

After reviewing the previous thread, I don't agree with the backup method you have chosen. With all due respect to Clay, I have problems with doing any shutdown of the database for normal backup purposes, however brief. Part of this aversion has to do with the questions you now have.

First, is it necessary to do a database shutdown? For the 9i and higher databases, you can do 'alter database begin backup' and 'alter database end backup'. This will quiesce the data files, so the OS can grab a point-in-time consistent snapshot of the data. For the 8i databases, you can write a script that does an 'alter tablespace begin/end backup' for each tablespace. If this is possible, that would be about the easiest solution.

For a database shutdown, there isn't really a "nice" way; there are only less rude ways. The nicest is "shutdown normal" -- the database won't shutdown until all user sessions disconnect. But if there is an application service, monitoring agent or whatnot that maintains a persistent connection, this won't work, and translates into "shutdown never".

Another option is to do a "shutdown transactional" on the database. This waits for a session to do a 'commit' or 'rollback' on a a transaction before booting them off the system. However, depending on the commit processing done at the application level, this is not risk free. Also, if the user processes are simply running reports and not doing a transaction, they may be out of luck.

As for finding user processes, there are a couple of options. They will make life a little more difficult, because your scripts will need to be able to query the database and interpret the results. First, you can look in dba_jobs_running to see if there are any currently scheduled DBMS_JOBS that are running. You can prevent new ones from starting by shutting down the scheduler with 'alter system set job_queue_processes = 0 SCOPE=MEMORY', but that won't stop already running ones.

For other user sessions, You can query into the v$session view; 'select username, osuser ... from v$session where last_call_et < 60' will show you sessions that have had any activity in the last minute. You'll need to fine tune the query to omit some of the server processes that always show activity.

[GraceO]

Thanks for your views, Ben.

I guess a should give more background. I am not the Oracle DBA so I'm lost when it comes to whether or not I have to actually shutdown the database. Maybe Clay is around to answer that question.

I would like to do things the right way, and from what I read, we should be using RMAN, but the Oracle DBA here is not ready to implement it. So, as system administrator on the HPUX side, I'm just trying to make sure we have a viable backup in case of a total server crash, naural disaster, etc.

I do weekly Ignite backups for everything other than the Oracle filesystems. I backup the Oracle filesystems once a month. Oracle exports are created and backed up daily. In case of a server crash, I plan to restore the image kept with Ignite, restore the Oracle filesystems, then import the latest export. (If this plan has holes, please, ANYBODY, respond!)

Currently, I coordinate with the Oracle DBA to bring down the databases before I do the snapshot. It just takes a few minutes, but it does happen after hours, on the weekend, and so it would be nice to automate it.

I will have to find out whether 'alter database begin backup' will work with the snapshot. We have both 9i and 10g databases, on different servers.

If I can't use the 'alter database begin backup' I will have to go back to creating a script and yes that would make life more difficult!

Grace

[Hein van den Heuvel]

Grace >> I guess a should give more background. I am not the Oracle DBA so I'm lost when it comes to whether or not I have to actually shutdown the database.

That's so silly, but so common.
What is the point in creating a backup if you do no know whether it is valid.
When is the last time you tried a restore?
This is NOT soemthing you want to do independent as a SA, but a joint effort with you DBA. Why ask random, possibly well meaning, possibly even knowledgable, but still random folks in a forum when you have a DBA to work with? A neck to strange so to speak!

Grace>> I would like to do things the right way, and from what I read, we should be using RMAN, but the Oracle DBA here is not ready to implement it.

That's so silly, but again so common.
So what is the alternative the DBA outlined?
Sounds like the DBA needs a kick in the derriere.

Grace>> So, as system administrator on the HPUX side, I'm just trying to make sure we have a viable backup in case of a total server crash, naural disaster, etc.

You can not accomplish that withough DBA skills involved.

>> backup the Oracle filesystems once a month.
>> Oracle exports are created and backed up daily.

Do you really mean EXPORTS? Like for schemas?
In that case that monthly 'cold' backup is almost useless. You might be better of coming up with the scripts to create a fresh, empty, DB and how to imports those exports. That's a perfect thing to be pracicing on an other machine.

>> In case of a server crash, I plan to restore the image kept with Ignite, restore the Oracle filesystems, then import the latest export. (If this plan has holes, please, ANYBODY, respond!)

The words I am missing, the hole if you like, are ARCHIVE LOGS.
Are those being made and gathered? That's what you want to apply to bring a a cold backup back to live.

The other hole is in the 'I plan' itself.

Hopefully it is more than a plan. It should have been done, in a throw away environment, already. You may find that all the backups made to date are useless. Please reasure us that the plan has been excercised already ?!

Good luck!
Hein.

[GraceO]

Hein, I will make no comments about our DBA. 'Silly' though my predicament may be, it is what it is...I don't have a DBA to give me the answers so I asked here.

Does anyone else out there have any suggestions or comments?

[Ben Dehner]

Contrary to my previous post, it might be that cold backups are your best recourse.

Optimally, you should be using RMAN hot backups plus archive logs. However, RMAN backups can be tricky to set up, and really need to be done by the DBA. If you can't do RMAN right now, well, you can't. One advantage that cold backups do have is that you are guaranteed a consistent state for all of the data files, so the backup can be used to restore and restart the database. Any sort of warm backup (such "alter database begin backup") may require some additional Oracle recovery from redo logs.

As for the Oracle exports, what you are effectively doing is a version of a full backup. The problem with exports is that they do not contain the structure of the database, such as the tablespaces and datafile locations. The restore process would require using the cold backup to restore the database structure, then having the DBA go in and drop all of the schemas and re-import them. While workable, this is a painful process.

The other issue, as Hein mentioned, is the apparent lack of archive logs. If something crashes, you can only restore up to the last full backup/export. Any transactions or activity that hit the system afterwards will be lost. Depending on your enterprise, this data loss may or may not be acceptable. But you cannot apply archive logs after a database import. You can only apply archive logs to the restored full backup.

In answer to your original question, I would go to your management and request blessing for an official "down time". Just script the database shutdown, and notify the users that the database will be unavailable at a certain time over the weekend.

[TwoProc · Accepted solution]

Well, I'll say that there's nothing terribly wrong with going without RMAN backups, except your MTTR (mean time to recovery) goes way up. But it is doable quite easily. However, using exports is pretty risky as there can be, and usually are bugs in the export/import utility as a standalone backup method. But, used in conjunction with other backup methods, it's quite valid - if you've got the time to recover.

1st) Consider turning on archive logs, and if you need to restore you can just re-apply those to your monthly cold backup and you won't need your exports. Get these off to tape regularly. You can force a switch of a current redo log off to archives whenever you want to force archive log backups with:
SQL > alter database switch logfile

I tend to issue one of these per redo log, it's overkill but there's no way you haven't gotten everything out of the redo logs.

Then just run a tape backup of everthing in the archive log directory. Then, after verifying your backup is good on tape (pick your fav method) then you can delete the archive logs sent to tape. Way back when (before RMAN), I removed all archive log files that had made it to two backup tapes.

2cnd) Secondly, I recommend daily hot backups of every database file in the server - this will take quite a bit of scripting, but it is VERY EASY and just plain and simple repetition.

in a sql script for EACH and EVERY tablespace (including sys, etc).

alter tablespace MYTABLESPACE begin backup;

Now, backup the files to tape:
host 'tar cvf /dev/rmt/0m /u5/oradata/PROD/MYTABLESPACE*.dbf';
(or whatever command you want to use on ALL of the datafiles belonging to the tablespace that you just put into backup mode)

then take that tablespace out of backup mode.

alter tablespace MYTABLESPACE end backup;

repeat for ALL tablespaces in the database.

From a hot backup like above, your dba would only need to apply the archive logs accumulated during the day to get you back to a point in time recovery, AND your MTTR would be fairly small. You would want to run the hot backups at the least busy time during the day. Even better if you could run them twice a day.

3) Backup your controlfile to a readable text file just after or before your hot backups (doesn't matter which really). This way, when you restore from backup, you can recreate the controlfile that knows about all of your datafiles at any point in time easily.

SQL > alter database backup controlfile to trace;

This will create a .trc file in your "bdump" directory, go find it and get it off to tape as well. Your dba would have to edit it, and rename it as a .sql script to be run when you have to do a recovery.

This method is manageble if your database isn't too huge, but requires some simple manual steps for your DBA to identify and be able to carry out during recovery. To be truthful, this is all stuff the DBA should be providing for you , or better yet, be doing and just asking you for the tape drive resources and access to to the cron job space to make it run. However this, like RMAN takes time to set up, but it is quite a bit easier than RMAN to set up.



Good Luck!

[GraceO]

As far as whether this plan has been tested, the answer is 'not totally'. We have had to 'import an export' after a user error, and it took a while to restore. We lost a days worth of work, so that added to the mess. We were able to recover, but it was not a full system crash.

We will be getting a test server soon, so I can fully test a restore to this server to find all the holes in the current backup plan.

Our DBA will be implementing archive logging, but I don't know when that's going to happen, so I'm kinda stuck in this situation. From the comments thus far, I can approach management and say that this backup plan cannot be done by me alone and really the DBA must be involved.

Thank you all for taking the time to explain alternate methods and for identifying 'holes' in our plan.

[Yogeeraj_1]

Hi Grace,

Since you will be getting a "test server" soon, the only simple solution that i can recommend is configure RMAN and implement a simple standby database solution.

What version of oracle are you using?

With standby database scheme, you can reduce the mttr considerably.

Configuration of RMAN is not as complicated that it may seem to a non-dba.

if you need any further assistance, please do let us know.

>Does anyone have a script to check if there are any users running jobs/processes on the database before doing a shutdown? Is there a 'nice' way to shutdown?
With RMAN, you will no longer concerned about these issues. Btw, to check any running sessions, you will have to run sqlplus and execute the following sql statement: "select username from v$session;"

hope this helps

kind regards
yogeeraj

[GraceO]

Yogeeraj,

The versions of Oracle we use are 9i and 10g. With the test server on the way, I can try different things, thankfully. RMAN seems like such a big production!

Grace