Cold backup in Oracle

Actually, taking a cold backup in Oracle is quite simple. If you choose to undertake a cold backup by copying tables, it consists of 5 steps.

Step 1 is identifying the location of the data files. We have 3 commands for that:

select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;

We must remember these files for later copying actions.

Step 2 is making a copy of the spfile by:

create pfile from spfile;

This will create a PFILE named initSID.ora in your $ORACLE_HOME/database (Windows) or $ORACLE_HOME/dbs (Linux/Unix) directory.

Step 3 is forcing Oracle to create a checkpoint:

alter system switch logfile;

Step 4 is to stop the Oracle instance:

shutdown immediate

Step 5 is copying all files as identified in step 1 and the pfile from step 2 into a safe place.

This leads to a situation where we have a bunch of files that we can use to restore the database. Another possibility is to clone the database in another instance. I tried this by installing an Oracle instance elsewhere and copy the files to its appropiate location. However, I noticed that I had to use the exact same version of Oracle, the same folder structure and the same platform.

So I used Oracle 12 release 1 on Windows 10 with an identical folder structure as the original Oracle 12 release 1. I then copied the files from above to the new instance and voila: it worked. Two times the same Oracle instance and database.

This copying action was undertaken with both instances being shutdown. So it was a cold backup from instance 1 that was restored in instance 2.

Door tom