A collection of tutorials, code and tools to help you get better using Oracle
17 July 2014
I recently needed to move a development database from one machine to another, and I thought I would tackle the problem by taking a cold backup and doing a full 'disaster recovery' on the new server.
You can probably do this job much more easily by using RMAN to clone the database, but I decided to do it this way as an exercise.
This guide assumes a database in NOARCHIVELOG mode. If you take a full cold backup, the steps are probably largely the same for a database in ARCHIVE log mode.
It also assumes you are restoring to a server where the file layout is different from the original server, just to make things more difficult!
To take a cold backup of the database:
rman target /
shutdown immediate
startup mount
run
{
allocate channel ch1 device type disk format '/dborafiles/apex01_backup/apex01_%u_%p_%c';
backup as compressed backupset incremental level 0 tag="Apex01_fullDB" database;
}
list backup tag="Apex_fullDB";
Capture the log output, and there are a few key pieces it is useful to keep:
connected to target database: APEX01 (DBID= *1643573941*) <==== Save this ID number
Starting backup at 16-JUL-14
channel ch1: starting compressed incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00003 name=/dborafiles/apex01/datafiles/undo.dbf
input datafile file number=00002 name=/dborafiles/apex01/datafiles/sysaux.dbf
input datafile file number=00005 name=/dborafiles/apex01/datafiles/apex_user_01.dbf
input datafile file number=00006 name=/dborafiles/apex01/datafiles/apex_files_01.dbf
input datafile file number=00001 name=/dborafiles/apex01/datafiles/system.dbf
input datafile file number=00004 name=/dborafiles/apex01/datafiles/users_01.dbf
channel ch1: starting piece 1 at 16-JUL-14
channel ch1: finished piece 1 at 16-JUL-14
piece handle=/dborafiles/apex01_backup/apex01_01pdgmuo_1_1 tag=APEX01_FULLDB comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:55
channel ch1: starting compressed incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 16-JUL-14
channel ch1: finished piece 1 at 16-JUL-14
piece handle=/dborafiles/apex01_backup/apex01_02pdgn0f_1_1 tag=APEX01_FULLDB comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-JUL-14
released channel: ch1
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Incr 0 267.04M DISK 00:00:48 16-JUL-14
BP Key: 1 Status: AVAILABLE Compressed: YES Tag: APEX01_FULLDB
Piece Name: /dborafiles/apex01_backup/apex01_01pdgmuo_1_1
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 344249820984 16-JUL-14 /dborafiles/apex01/datafiles/system.dbf
2 0 Incr 344249820984 16-JUL-14 /dborafiles/apex01/datafiles/sysaux.dbf
3 0 Incr 344249820984 16-JUL-14 /dborafiles/apex01/datafiles/undo.dbf
4 0 Incr 344249820984 16-JUL-14 /dborafiles/apex01/datafiles/users_01.dbf
5 0 Incr 344249820984 16-JUL-14 /dborafiles/apex01/datafiles/apex_user_01.dbf
6 0 Incr 344249820984 16-JUL-14 /dborafiles/apex01/datafiles/apex_files_01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Incr 0 1.03M DISK 00:00:02 16-JUL-14
BP Key: 2 Status: AVAILABLE Compressed: YES Tag: APEX01_FULLDB
Piece Name: /dborafiles/apex01_backup/apex01_02pdgn0f_1_1 <=========== piece name
SPFILE Included: Modification time: 16-JUL-14 <=========== spfile file included
SPFILE db_unique_name: APEX01
Control File Included: Ckp SCN: 344249820984 Ckp time: 16-JUL-14 <=========== control file included
Now you can copy the backup to the new server using scp, rsync etc. Note that the restore process is slightly easier if you place the backup files in the exact same location on the new server as they were created on the old server.
To create a restore of the old database (with the same DBID and SID), you need the DBID that was captured above.
First set the Oracle SID to be the same as the old database, APEX01 in this case, and connect to RMAN
Notice the DBID and SPFILE and Control File reference the data that was captured above.
export ORACLE_SID=APEX01
rman target / NOCATALOG
set DBID=1643573941
startup nomount
RMAN will complain that it cannot find the parameter file, but it will go ahead and start and Oracle instance without a parameter file:
RMAN>
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initAPEX01.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
As we have started RMAN without a recovery catalog, we need to tell it where to find the backup at.
Also, because the file layout of the new server is different from the old one, we need to perform a few extra steps to change the file locations.
RUN
{
allocate channel ch1 device type disk format '/oraworkspace/apex01_backup/apex01_%u_%p_%c';
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/tmp/%F';
RESTORE SPFILE
TO PFILE '?/dbs/initAPEX01.ora'
from '/oraworkspace/apex01_backup/apex01_02pdgn0f_1_1';
SHUTDOWN ABORT;
}
This command points RMAN at our backup set (using the same format as the backup used). Then it restores the SPFILE to a plain text pfile so we can edit it. Notice the full path to the backup piece (which we captured above) is included in the command.
Now edit the spfile and change any paths that referenced files on the old server to the new server paths. In my case, I had to edit the control file paths and the diag_dest parameters.
Next, we need to restore the control files:
startup force nomount pfile='?/dbs/initAPEX01.ora';
RUN
{
allocate channel ch1 device type disk format '/oraworkspace/apex01_backup/apex01_%u_%p_%c';
RESTORE CONTROLFILE FROM '/oraworkspace/apex01_backup/apex01_02pdgn0f_1_1';
ALTER DATABASE MOUNT;
}
Again, we tell RMAN which backup piece to find the controlfile in.
This step is easiest if you still have access to the original database. You need to get a list of all the datafiles, tempfiles and redo log files from the old database, and rename then to the new path. If the old database is no longer available, I found that RMAN prints an error for each file, so it is possible to grab them from the error message and go from there.
My database didn't have too many datafiles, so to complete the restore I ran the following:
run
{
allocate channel ch1 device type disk format '/oraworkspace/apex01_backup/apex01_%u_%p_%c';
catalog start with '/oraworkspace/apex01_backup/apex01_01pdgmuo_1_1';
set newname for datafile 1 to '/oraworkspace/apex01/system.dbf';
set newname for datafile 2 to '/oraworkspace/apex01/sysaux.dbf';
set newname for datafile 3 to '/oraworkspace/apex01/undo.dbf';
set newname for datafile 4 to '/oraworkspace/apex01/users_01.dbf';
set newname for datafile 5 to '/oraworkspace/apex01/apex_user_01.dbf';
set newname for datafile 6 to '/oraworkspace/apex01/apex_files_01.dbf';
set newname for tempfile 1 to '/oraworkspace/apex01/temp.dbf';
SQL "ALTER DATABASE RENAME FILE ''/dborafiles/apex01/redo/redo1.log'' TO ''/oraworkspace/apex01/redo1.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/dborafiles/apex01/redo/redo2.log'' TO ''/oraworkspace/apex01/redo2.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/dborafiles/apex01/redo/redo3.log'' TO ''/oraworkspace/apex01/redo3.log'' ";
restore database;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
}
This renames each of my datafiles to a new location, and also renames my redo log files.
Note, that if all files are going to the exact same location as the old files, the newname, SQL and SWITCH DATAFILE command should not be required.
Also, if the rman backup files are in the same location on the new server as they were created on the old server, the 'catalog start with' command should not be required either.
At this point, the database is ready to open
RMAN> alter database open resetlogs;
You will probably also want to create a spfile from the database pfile:
SQL> create spfile from pfile;
Job done!