René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Cold backup in Oracle

Making a Cold Backup

A cold backup requires the database to be shut down. That means, as opposed to a hot backup, users cannot do anything on the database.
So, first, SHUTDOWN the database (not a shutdown abort). Then copy all necessary files to a safe place.
Those files are:
The datafiles: 
select name from v$datafile
The control files: 
select name from v$controlfile
The online redo logs:
Note, the online redo logs need not be backed up. 
select member from v$logfile
The parameter file init.ora or spfile (The parameter file cannot be found using dynamic performance views) and the password file (if used).
Also, some network configuration files such as the listener.ora, tnsnames.ora and sqlnet.ora might be backed up.
Some might also want to backup the Oracle Software along with the inventory, the oratab file and startup scripts.
Here is a shell script that shuts down the database, copies the files of the database to a safe place and then starts up the database again.
After we have copied these files to a safe place, we can safely delete the archived redo log files.

Simulating Media Failure

create some tables, and insert something into the tables. And then delete the datafiles 
create table after_backup (a number, b varchar2(40));
insert into after_backup values (1, 'before commit');
insert into after_backup values (2, 'also before commit');
commit;
insert into after_backup values (3, 'after commit');
Make sure to not commit this session until you delete the datafiles. Open another session instead and create a user or something. 
create user user_after_backup identified by pw;
grant dba to user_after_backup;
Now, crash the instance and delete the database. Open the init.ora file at the backuped place and modify its control_file initializtion parameter to point to the backed up control files. Then startup mount pfile=c:\oracle\ora81\admin\backup\initadpdb.ora the database. Note, I am using the backed up initadpdb.ora file.
After you've mounted the database, rename the datafile (so that they point to the new files): 
alter database rename file 'C:\ORACLE\ORA81\ADMIN\ADPDB\SYSTEM01.DBF' 
  to 'C:\ORACLE\ORA81\ADMIN\backup\SYSTEM01.DBF';
Note: Online redo log files should never be backed up!