Restoring a Database Using a Cold Backup (Offline Backup)


In this exercise you will be restoring the database with a cold backup. It is assumed the database is in NOARCHIVELOG mode.
1. Make sure the database is shut down.
2. Copy the files on the backup media to the original location. You would copy the following files:

  • NN Database datafiles
  • NN Database control files
  • NN Database online redo logs

If the original location of the database files is not available, copy them to an alternate location. Having copied the files to an alternate location, you will likely need to execute an optional step 3 for the control files and optional step 4 for all database files and/or online redo logs. Here is an example of the copy command:

C:\Documents and Settings\Robert>copy c:\oracle\oradata\orcl\cold\*.*
c:\backup\orcl\backup1
3. (Optional) If you copied the database control files to a location other than their original location, you will need to modify the database parameter CONTROL_FILES to point to the control files in their new location.

If you are using a text-based parameter file (pfile), simply edit the file and change the CONTROL_FILES parameter value contained within that file.

If you are using a server-based parameter file (spfile), then you will need to start the database in NOMOUNT mode and change the SPFILE entry for the CONTROL_FILES parameter using the alter system command. We will have to use the scope=spfile keyword when issuing the alter system command since changing the CONTROL_FILE parameter is not supported as a dynamic change. After you have changed the parameter file (manually or using the alter system command), use the shutdown command to shut down the database (the parameter file will be reread when you open it again in the next steps). Here is an example:
SQL> startup nomount
ORACLE instance started.
Total System Global Area 397557760 bytes
Fixed Size 1333452 bytes
Variable Size 289408820 bytes
Database Buffers 100663296 bytes

Redo Buffers 6152192 bytes
SQL> alter system set control_files=’C:\ORACLE\ORADATA\ORCL\CONTROL01.CTL’,
‘C:\ORACLE\ORADATA\ORCL\CONTROL02.CTL’,
‘C:\ORACLE\ORADATA\ORCL\CONTROL03.CTL’ scope=spfile;
System altered.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.

4. (Optional) If you copied the database online redo logs or the database datafiles to a different location, you will need to indicate to Oracle that you have done so. This is so Oracle will know where the files are now so it can open them. This is known as a rename operation. (Don’t be fooled, though. It renames only the files inside of Oracle; it does not rename them on the operating system).
To rename the database files (redo log and datafiles) you must have the database mounted first. Once the database is mounted, you will issue the alter database rename file command for each database file that needs to be changed. Here is an example where we have moved the online redo logs and database datafiles from c:\oracle\oradata\orcl to c:\oracle\oradata\orclnew. You need to indicate to Oracle that you have made this change by using the alter database rename file command. This will change the pointers to the database files inside the control file so Oracle will be looking for the files in the correct location. Note that for the rest of this exercise we will assume that the files were moved to their original locations. In this example, you rename the online redo logs and then
you rename the database datafiles:
SQL> startup mount
ORACLE instance started.
Total System Global Area 397557760 bytes
Fixed Size 1333452 bytes
Variable Size 272631604 bytes
Database Buffers 117440512 bytes
Redo Buffers 6152192 bytes
Database mounted.
SQL>alter database rename file ‘c:\oracle\oradata\orcl\REDO01.LOG‘ to
‘c:\oracle\oradata\orclnew\REDO01.LOG‘;
SQL>alter database rename file ‘c:\oracle\oradata\orcl\REDO02.LOG‘ to
‘c:\oracle\oradata\orclnew\REDO02.LOG‘;

SQL>alter database rename file ‘c:\oracle\oradata\orcl\REDO03.LOG‘ to
‘c:\oracle\oradata\orclnew\REDO03.LOG‘;
SQL>alter database rename file ‘c:\oracle\oradata\orcl\SYSAUX01.DBF‘ to
‘c:\oracle\oradata\orclnew\SYSAUX01.DBF‘;
SQL>alter database rename file ‘c:\oracle\oradata\orcl\SYSTEM01.DBF‘ to
‘c:\oracle\oradata\orclnew\SYSTEM01.DBF‘;
SQL>alter database rename file ‘c:\oracle\oradata\orcl\TEMP01.DBF‘ to
‘c:\oracle\oradata\orclnew\TEMP01.DBF‘;
SQL>alter database rename file ‘c:\oracle\oradata\orcl\UNDOTBS01.DBF‘ to
‘c:\oracle\oradata\orclnew\UNDOTBS01.DBF‘;
SQL>alter database rename file ‘c:\oracle\oradata\orcl\USERS01.DBF‘ to
‘c:\oracle\oradata\orclnew\USERS01.DBF‘;
5. Now that the files are copied into place, you can start up the database:
SQL> startup
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1333592 bytes
Variable Size 348128936 bytes
Database Buffers 62914560 bytes
Redo Buffers 6107136 bytes
Database mounted.
Database opened.

That’s it. You have recovered your database! Query to your heart’s delight!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s