Create a new Oracle database from cold backup

You will find below the process I use to create a new Oracle database (instance named NEW in this article) from a cold backup.

First, the actions to perform on the existing database:

Generate the init file for the new database. Just keep it safe somewhere, we will modify it at a later stage.

create pfile from spfile;

To avoid any further problem, force a switch logfile.

alter system switch logfile;

Generate the CREATE CONTROLFILE statement you will use on the new database:

alter database backup controlfile to trace;

It will be generated in one of the last trace file in you existing instance background_dump_dest folder. Also, it will contains other commands to execute on your new instance (create tempfile for example). Change the instance name, redo and datafile location, and check the it is SET DATABASE, not REUSE. Below an what you should get, after modifications:

STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "NEW" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    'E:\ORADATA\NEW\REDO01A.LOG',
    'E:\ORADATA\NEW\REDO01B.LOG'
  ) SIZE 25M,
  GROUP 2 (
    'E:\ORADATA\NEW\REDO02A.LOG',
    'E:\ORADATA\NEW\REDO02B.LOG'
  ) SIZE 25M,
  GROUP 3 (
    'E:\ORADATA\NEW\REDO03A.LOG',
    'E:\ORADATA\NEW\REDO03B.LOG'
  ) SIZE 25M
-- STANDBY LOGFILE
DATAFILE
  'E:\ORADATA\NEW\SYSTEM01.DBF',
  'E:\ORADATA\NEW\UNDOTBS01.DBF',
  'E:\ORADATA\NEW\SYSAUX01.DBF',
  'E:\ORADATA\NEW\TEMPBIG.DBF'
CHARACTER SET WE8ISO8859P1
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '\\SERVERNAME\FLASH_RECOVERY$\NEW\ARCHIVELOG\DATE\O1_MF_1_1_%U_.ARC';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORADATA\NEW\TEMP01.DBF'
     SIZE 536870912  REUSE AUTOEXTEND ON NEXT 16777216  MAXSIZE 3072M;
-- End of tempfile additions.
--

Shutdown the Instance

shutdown immediate;

Note: You can't restart the database before you copy the dbf and logfiles to another location, or you will have to re do the steps above.

You can now start working on the new instance:

First, create all the necessary folders (oradata, diagnostic_dest or XXX_dump_dest, etc).

Create the password file:

orapwd file=full_location/FILE.ora force=y

The command will ask for the password. Make sure you specify the full location of the file, or the command will create it in your actual location. Carefull, if you are on linux or windows, the password file FILE.ora will have a different name. If the name isn't right, you will have an ORA-01031 Insufficient privileges error while trying to connect (see this article for more details) :
- Linux: orapwd<SID>.ora
- Windows: PWD<SID>.ora

Modify the init<SID>.ora file (SID, file locations, etc.)

Then, copy the datafiles and redo files from the existing database. Once this is done, you can restart the original database to reduce the downtime to a minimum.

For Windows only, create the service:

oradim -new -sid <SID> -startmode manual -spfile
oradim -edit -sid <SID> -startmode auto -srvcstart system

If you messed up this part, remove the service and recreate it:

oradim -delete -sid <SID>

Make sure you got all the entries in the listener.ora and tnsnames.ora files.

Restart you listener (lsnrctl stop/start) so it has your new instance.

Start the new instance with nomount option, and execute the CREATE CONTROLFILE statment

Open the database.

alter database open resetlogs upgrade;

Note: The upgrade option is mandatory only if your use this method to upgrade from 10g to 11g

Then, add the TEMPFILE. You will find the statement in the trace file used for the CREATE CONTROLFILE statment (see above). Check if there is no other actions required in this trace file too.

And the last but not least, check the alert file and fix all issues that might appear.

Then, recompile all invalid objects:

@?/RDBMS/ADMIN/utlirp.sql
shutdown immediate
startup mount
alter datafile open upgrade;
-- or: startup migrate;
--recompile all invalid objects
@?/RDBMS/ADMIN/utlrp.sql

This part, only if you did this for a migration from 10g to 11g:

@catupgrd.sql
@utlrp.sql