How to perform a simple full database backup

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Fri, 4 Apr 2003 11:46:50 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA7038380CD@lnewton.leeds.lfs.co.uk>

Hi Graham,

you'll want the 'cold backup' method then !

	select name from v$datafile
	union
	select member from v$logfile
	union
	select name from v$controlfile
	order by 1;

That will get you a list of all the files that the database needs, except the password file and the initSID.ora but as I assume you will simply restore the entire system back from whenece it came, you won't need to bother with those. For a production cold backup, I'd add them to the list.

Next up shutdown the database. A clean shutdown is best as it will remove the need to recover on next startup (and after a restore). How you do this depened on your version of Oracle - which you never mentioned :o)

Oracle 8i and above :

	sqlplus /nolog
	connect / as sysdba
	shutdown

Wait for everything to come down. This works if all the users are not logged in. However, if some are and can't/won't/refuse to logout, do this :

shutdown immediate

which might take some time to rollback any uincommitted transactions, but it will sort the users out :o)

	shutdown abort 
	startup
	shutdown

will kill everything NOW, then the startup will recover and the shutdown gives you a clean system to backup.

exit gets you out of sqlplus, but you can stay in it while the backup is running.

Up to Oracle 8i :

	svrmgr
	connect internal
	shutdown

etc as above.

Once closed, you simply use the OS to copy the all the files listed above to a location known as 'elsewhere'. You now have a cold backup. When the time comes to regress back, simply shutdown (you can even shutdown abort !) and restore all the files from 'elsewhere' to the original location(s). Then startup again.

using the same tool as you did to shutdown, simply :

startup

HTH Cheers,
Norman.

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

-----Original Message-----
From: graham.parsons_at_reflective.com (Graham Parsons) [mailto:graham.parsons_at_reflective.com]
Posted At: Friday, April 04, 2003 11:07 AM Posted To: server
Conversation: How to perform a simple full database backup Subject: How to perform a simple full database backup

<SNIP>

I'm looking for the simplest method - simple to perform and also simple for me to get up to speed with.

<SNIP>
Received on Fri Apr 04 2003 - 04:46:50 CST