Database Backup Script

[JawwadLone]

Hi,

I want to write script file to take hot backpup of database created in oracle 8.0.5. Secondly i want to create a bat file to run that script. Can any one help me?

Regards,

 

[SantaMufasa]

Jawwad,

What operating system is your target for this backup script...*nix, Windows, Other?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LKBrwnDBA]

I'm sure you can find on the web DOS scripts to backup databases.

I recommend you use rman utility to backup your db.
An rman DOS script would look kinda like this:

set ORACLE_HOME=C:\Pgm\oracle8\
set ORACLE_SID=test2
(
echo connect target rman/rman_oracledba
echo run { allocate channel d1 type disk;
echo       backup incremental level 0  
echo       filesperset 2
echo       format 'C:\data\oracle8\BACKUP\rman_LVL0_%d.%t.%p.%c.bus'
echo       database archive log all delete input;}
)> rman.scr
rman NOCATALOG <rman.scr >rman.log

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[ddiamond]

We backup our windows database using the dos xcopy command. Is there a disadvantage to doing it this way?

 

[LKBrwnDBA]

Not unless you DO NOT shut the db!!!


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

DDiamond (and others that may read this thread later),

To clarify LKBrwnDBA's correct observation:

Using "xcopy" or some other operating-system command to duplicate your database files is only half of the equation...You must also first either

1) Gracefully shutdown your database before copying the files (This works for cold backups in either ARCHIVELOG or NOARCHIVELOG mode.) or,

2) Place each tablespace into backup mode via the command:

ALTER TABLESPACE <ts name> BEGIN BACKUP;

...which applies only if your database is in ARCHIVELOG mode.

If you do not successfully complete one of the above "preparations" for backing up, and you only copy your database files, then your Oracle backups are USELESS!!!.

(Important Note: If you choose Option 2, above, then you should/must take the subject tablespace out of backup mode with this counterpart command:

ALTER TABLESPACE <ts name> END BACKUP;

An alternative to all of this is to use Oracle's RMAN (Recovery MANager) utilities.

Let us know if this clarification generates any follow-on questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[ddiamond]

Yes, we do kick out all of the users and then gracefully shut down the database. Does the RMAN utility allow you to do a live backup without kicking everyone out?

 

[SantaMufasa]

DDiamond,

I'm not an RMAN aficionado, but even without knowing/using RMAN, I'd bet my paycheck that RMAN works just fine as I describe hot backups, below, while not using RMAN.

When you are doing a Hot Backup in Oracle, it absolutely, positively allows you to keep on truckin' with your production activities without the users having a clue that backup is occurring, and every single transaction is fully recorded (and recoverable) and all integrity remains in place for all in-flight transactions during hot backup.

Specifically, Oracle Hot Backup does not require any shutting down or interruption to service...One does not even know/notice (except for the DBA/RMAN that initiates the backup) that backups are occurring.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Thargy]

ddiamond,

as usual, Santa is spot on.

RMAN is by default capable of doing a hot backup of a running database - it's been specifically designed to do so. It takes a snapshot of your control files (and then releases them) so that it can get on without troubling users.

Since it's free and guaranteed supported by Oracle, what's your company's excuse for not using it? (tongue welded in cheek).

Regards

Tharg

Grinding away at things Oracular

 

[ddiamond]

Since it's free and guaranteed supported by Oracle, what's your company's excuse for not using it? (tongue welded in cheek).

I'm not entirely sure. I think our senior dba believes that cold backups are safer. Sounds like you guys would disagree.

 

[LKBrwnDBA]

Yes, we disagree -- RMAN backups are extremely safe and can be done with database online.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[KenCunningham]

Not disagree exactly, but just how 'senior' (and I use the term advisedly) is your DBA?

I don't mind people who aren't what they seem. I just wish they'd make their mind up.

Alan Bennett.

 

[LKBrwnDBA]

I'm not entirely sure. I think our senior dba believes that cold backups are safer. Sounds like you guys would disagree.

Or maybe he just has no clue what RMAN can do.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Thargy]

lkbrwndba,

I surmise that ignorance (or a sudded attack of FUD) are behind any reluctance to use RMAN. Admittedly it takes a bit of configuring (which isn't immediately easy to do) but it offers unique abilities, i.e. you can recover down to the granularity of individual blocks, it can verify that a backup is good, it can also backup archive logs, and automatically delete 'mouldy oldies' in line with a specified retention policy.

I suggest you ask why these free facilities are being ignored, and time, money and effort being wasted on custom scripts....

Regards

Tharg

Grinding away at things Oracular

 

[SantaMufasa]

DDiamond, IMHO, any senior DBA who believes that cold backups are safer, is "senior" in name (or perhaps salary) only. If that DBA says, "Cold backups are safer," then you are certainly within your rights to ask, "How is that? Please explain why 1) shutting down the database, 2) copying the files, then 3) bringing the database back up could be safer than 1) putting a tablespace in '...BEGIN BACKUP' mode, 2) copying the tablespace datafiles, then 3) taking the tablespace to '...END BACKUP' mode, is safer." That is not at all insubordinate...enquiring minds just want to know.

Here is an even more-fundamental question re: your database: Is the database in ARCHIVELOG mode? If the answer is, "No," then your installation is in really hot soup.

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[ddiamond]

I'll ask when our DBA comes back from vacation on Monday. I doubt we'll actually make any changes to our oracle procedures at this point because our corporate office has mandated that we migrate to db2 and retire oracle. :-(

We've been in the process of migrating for 2 years now, and I have a feeling it will be many more years before we are able to completely abandon oracle.

 

[SantaMufasa]

Sounds like management wants to move from a "2006 Motorhome" to a "Covered Wagon"...But perhaps my biases are showing.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[ddiamond]

They argue that a "Covered Wagon" is cheaper. I don't think they realize that you get what you pay for.

 

[SantaMufasa]

No question on both points ! A bicycle is certainly less expensive than a Mercedes 380 CDE, as well, but functionality tends to differ dreastically.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]