Oracle 10g database restore/recovery - Test your tape backup Part 2: Same dbid

It is very critical to have solid backup and recovery procedures to protect your database. You should test the procedure periodically. This article provides step-by-step instructions on how to test your tape backup by restoring the database to a new host using Oracle RMAN. The DBID and ORACLE_SID of the restored database will be the same as the ones for the original (source) database.

My settings in this case are:

- The Database version is 10.2.0.3;
- The source database is called TARG;
- Weekly hot whole database backup of TARG is taken to disk by RMAN and then copied to tape. The latest whole database backup of TARG is used for restore/recovery in this example;
- I use Flash Recovery Area to store backup related files;
- The directory structure of two systems are identical;
- No recovery catalog is used. If you use recovery catalog, NEVER connect RMAN to the recovery catalog, otherwise the restored database is registered automatically in the recovery catalog.

1 Before you get started:

1) Make sure the operating system on the source and new servers are the same;

2) Make sure the same Oracle software release is used on the source and restored databases.

3) Record the DBID for the source database. Get DBID. In order to start the oracle db restore you need to know Oracle Database ID to start with. You can get DBID from the control file backup, or by connecting to RMAN.
$rman
Recovery Manager: Release 10.2.0.3.0 - Production on Thu Jan 31 15:41:00 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target <user>/<password>@TARG
connected to target database: TARG (DBID=123456789)

4) Make sure you have the necessary backups of the source database. You will need data file backup, Control file & SPFILE autobackup, and backups of archived log required to recover the datafiles.

5) Exam your RMAN configuration settings of source database.
Login to source database as a user with sysdba privilege and show RMAN configurations. Note control file autobackup is ON.
RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO \%F\; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM \AES128\; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO \<path>\SNCFTARG.ORA\; # default

2 If Oracle Database software is not installed on the new host, install it without the starter database.
Note down Oracle Home Directory. Install required patches if necessary.

3 Set ORACLE_HOME and ORACLE_SID on the new host to the same value on source host.
You can either locally do it every time by set commands or set it under environment variable.

4 Copy Control file & SPFILE autobackup, database backup files and archive logs backup files to locations from where you are restoring.

5 Copy the password file over to the new host.

On Windows, copy it to %Oracle_home%\database.

On UNIX, copy it to $Oracle_home/dbs.

6 Copy tnsnames.ora to new host. Edit it with right server name and other information.
On Windows, copy it to %ORACLE_HOME%\network\admin.

On UNIX, copy it to $ORACLE_HOME/network/admin.

7 Create data directories on the new host where you want to store new data files.
Create directories for controlfile, datafile and onlinelog on the new host.

8 Create following dump directories for the restored database.
Create adump, bdump, cdump, udump, dpdump directories on the new host.

9 On Windows start the Oracle Services.
Run the following command on Windows:
$oradim –new –sid TARG –startmode auto

Now go to control panel>administrative tools, open services and see whether oracle services for TARG is running.

Change the Oracle DB Server account from local system to a user with administrative account. After the change, stop and start this Oracle DB Services.

(Note: The following commands will be run on the new host)

10 On the new host, invoke RMAN from command prompt and startup nomount.
$RMAN target / NOCATALOG
RMAN > SET DBID 123456789;
RMAN > Startup nomount

Hereyou will see error message like:

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file \<path>\INITTARG.ORA\
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
……….

This is fine because the PFILE has not been restored from the auto backup yet. It will be restored at next step. The instance is started with a dummy file.

11 Restore pfile from the control file and spfile auto backup.
- On Windows
RMAN> restore spfile to pfile \<Oracle_home>\database\initTARG.ora\ from ‘<path and name of the Control file & SPFILE autobackup file>\;

- On UNIX
RMAN> restore spfile to pfile \<Oracle_home>/dbs/initTARG.ora\ from \<path and name of the SPFILE and control file auto backup file>\;

The output is like this:
Starting restore at 31-JAN-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=37 devtype=DISK

channel ORA_DISK_1: autobackup found: <here you will see the path and name of the SPFILE and control file auto backup file >
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 31-JAN-08

12 Make necessary changes to parameter file.
Necessary changes include locations for dump files, locations for control file, etc..

13 Restart the instance using the edited pfile.
- On Windows:
RMAN> startup force nomount pfile=\<Oracle_home>\database\initTARG.ora\;

- On UNIX
RMAN> startup force nomount pfile=\<Oracle_home>/dbs/initTARG.ora\;

14 Restore control file from control file auto backup.
RMAN> restore controlfile from \<path + name of the Control file & SPFILE autobackup>\;

Starting restore at 31-JAN-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=<you will see the path and name of the control file here>
output filename=<multiplexing - you will see the path and name of the control file here>
Finished restore at 31-JAN-08

15 Mount the database and Restore and Recover database.
RMAN>alter database mount;
RMAN>restore database;
RMAN>recover database;
--Open resetlogs on the restored database
RMAN >alter database open resetlogs;

If the new host has different directory structure from the source host, you have to run ‘SET NEWNAME’ commands before performing restore and recovery. Refer to the Reference for details.

16 Login to database using sqlplus as sysdba, and create spfile from pfile.
- On Windows:
SQL>Create spfile from pfile =\<Oracle_home>\database\initTARG.ora\
-- Restart the restored instance using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;

- On UNIX:
SQL>Create spfile from pfile =\<Oracle_home>/dbs/initTARG.ora\
-- Restart the restored instance using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;

17 Test the restored database through EM and command line if you want to.
To create DB console for the restored database:

- On Windows
$cd %ORACLE_HOME%\bin
$emca –config dbcontrol db

- On UNIX
$cd $ORACLE_HOME/bin
$emca –config dbcontrol db

Enter SID, password for users sys, sysman and dbsnmp when prompted.

18 Drop the restored database if you want to.
The database must be mounted exclusive and not open, and started in RESTRICT mode to be dropped.
RMAN>startup force nomount
RMAN> SQL \ALTER SYSTEM ENABLE RESTRICTED SESSION\;
RMAN>DROP DATABASE;

Reference:
Oracle® Database Backup and Recovery Advanced User’s Guide, 10g Release 2 (10.2), B14191-02
 

Thanks Haili Jiao. it's very for me.

Nice Article very helpful for new DBAs

Post Your CommentAbout author:
Oracle 10g database restore/recovery - Test your tape backup Part 2: Same dbid

It is very critical to have solid backup and recovery procedures to protect your database. You should test the procedure periodically. This article provides step-by-step instructions on how to test your tape backup by restoring the database to a new host using Oracle RMAN. The DBID and ORACLE_SID of the restored database will be the same as the ones for the original (source) database.

My settings in this case are:

- The Database version is 10.2.0.3;
- The source database is called TARG;
- Weekly hot whole database backup of TARG is taken to disk by RMAN and then copied to tape. The latest whole database backup of TARG is used for restore/recovery in this example;
- I use Flash Recovery Area to store backup related files;
- The directory structure of two systems are identical;
- No recovery catalog is used. If you use recovery catalog, NEVER connect RMAN to the recovery catalog, otherwise the restored database is registered automatically in the recovery catalog.

1 Before you get started:

1) Make sure the operating system on the source and new servers are the same;

2) Make sure the same Oracle software release is used on the source and restored databases.

3) Record the DBID for the source database. Get DBID. In order to start the oracle db restore you need to know Oracle Database ID to start with. You can get DBID from the control file backup, or by connecting to RMAN.
$rman
Recovery Manager: Release 10.2.0.3.0 - Production on Thu Jan 31 15:41:00 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target /@TARG
connected to target database: TARG (DBID=123456789)

4) Make sure you have the necessary backups of the source database. You will need data file backup, Control file & SPFILE autobackup, and backups of archived log required to recover the datafiles.

5) Exam your RMAN configuration settings of source database.
Login to source database as a user with sysdba privilege and show RMAN configurations. Note control file autobackup is ON.
RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO \%F\; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM \AES128\; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO \\SNCFTARG.ORA\; # default

2 If Oracle Database software is not installed on the new host, install it without the starter database.
Note down Oracle Home Directory. Install required patches if necessary.

3 Set ORACLE_HOME and ORACLE_SID on the new host to the same value on source host.
You can either locally do it every time by set commands or set it under environment variable.

4 Copy Control file & SPFILE autobackup, database backup files and archive logs backup files to locations from where you are restoring.

5 Copy the password file over to the new host.

On Windows, copy it to %Oracle_home%\database.

On UNIX, copy it to $Oracle_home/dbs.

6 Copy tnsnames.ora to new host. Edit it with right server name and other information.
On Windows, copy it to %ORACLE_HOME%\network\admin.

On UNIX, copy it to $ORACLE_HOME/network/admin.

7 Create data directories on the new host where you want to store new data files.
Create directories for controlfile, datafile and onlinelog on the new host.

8 Create following dump directories for the restored database.
Create adump, bdump, cdump, udump, dpdump directories on the new host.

9 On Windows start the Oracle Services.
Run the following command on Windows:
$oradim –new –sid TARG –startmode auto

Now go to control panel>administrative tools, open services and see whether oracle services for TARG is running.

Change the Oracle DB Server account from local system to a user with administrative account. After the change, stop and start this Oracle DB Services.

(Note: The following commands will be run on the new host)

10 On the new host, invoke RMAN from command prompt and startup nomount.
$RMAN target / NOCATALOG
RMAN > SET DBID 123456789;
RMAN > Startup nomount

Hereyou will see error message like:

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file \\INITTARG.ORA\
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
……….

This is fine because the PFILE has not been restored from the auto backup yet. It will be restored at next step. The instance is started with a dummy file.

11 Restore pfile from the control file and spfile auto backup.
- On Windows
RMAN> restore spfile to pfile \\database\initTARG.ora\ from ‘\;

- On UNIX
RMAN> restore spfile to pfile \/dbs/initTARG.ora\ from \\;

The output is like this:
Starting restore at 31-JAN-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=37 devtype=DISK

channel ORA_DISK_1: autobackup found:
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 31-JAN-08

12 Make necessary changes to parameter file.
Necessary changes include locations for dump files, locations for control file, etc..

13 Restart the instance using the edited pfile.
- On Windows:
RMAN> startup force nomount pfile=\\database\initTARG.ora\;

- On UNIX
RMAN> startup force nomount pfile=\/dbs/initTARG.ora\;

14 Restore control file from control file auto backup.
RMAN> restore controlfile from \\;

Starting restore at 31-JAN-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=
output filename=
Finished restore at 31-JAN-08

15 Mount the database and Restore and Recover database.
RMAN>alter database mount;
RMAN>restore database;
RMAN>recover database;
--Open resetlogs on the restored database
RMAN >alter database open resetlogs;

If the new host has different directory structure from the source host, you have to run ‘SET NEWNAME’ commands before performing restore and recovery. Refer to the Reference for details.

16 Login to database using sqlplus as sysdba, and create spfile from pfile.
- On Windows:
SQL>Create spfile from pfile =\\database\initTARG.ora\
-- Restart the restored instance using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;

- On UNIX:
SQL>Create spfile from pfile =\/dbs/initTARG.ora\
-- Restart the restored instance using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;

17 Test the restored database through EM and command line if you want to.
To create DB console for the restored database:

- On Windows
$cd %ORACLE_HOME%\bin
$emca –config dbcontrol db

- On UNIX
$cd $ORACLE_HOME/bin
$emca –config dbcontrol db

Enter SID, password for users sys, sysman and dbsnmp when prompted.

18 Drop the restored database if you want to.
The database must be mounted exclusive and not open, and started in RESTRICT mode to be dropped.
RMAN>startup force nomount
RMAN> SQL \ALTER SYSTEM ENABLE RESTRICTED SESSION\;
RMAN>DROP DATABASE;

Haili Jiao works as Oracle DBA at Baltimore, Maryland, USA. She is an Oracle 10g Certified Professional and 9i Certified Associate. s:

Haili Jiao works as Oracle DBA at Baltimore, Maryland, USA. She is an Oracle 10g Certified Professional and 9i Certified Associate.