Restoring and Recovering the Database on a New Host using RMAN

By -


This blog will demonstrate detailed steps to restore a database on New Host using RMAN. You can also use it to move a production database to new host as test database.
Here, we are going to use the source database with the DB_NAME=ORCL and we are going to restore this ORCL database on the test database machine.

1. Preparing to restore the database on a new host:

If you use the procedure in this section, then the DBID for the restored database equals the DBID for the original database. Do not register a test database created in this way in the same recovery catalog as the source database. Because the DBID of the two databases is the same, the metadata for the test database can interfere with RMAN's ability to restore and recover the source database. 

Record the DBID for your source database. You can find the DBID by connecting to the database with RMAN as shown below:

Take backups of all the datafiles, controlfiles, spfile and archivelog files on the source database machine.
Perform following steps on the source database machine:

Ensure that the initialization parameter file and the backups of datafiles, controlfiles and archivelogs are available on the new host machine for the restore operation. Copy all the backup files from the old host to new host using operating system utility or you can transfer it through pendrive also. If you are using operating system utility then you can perform following step to copy the files from shared folder 'backup' of Neha-pc:

When we restore the database, we are not going to have the active redo logs from the source database, so we will need the last SCN of archivelogs for the restore operation. We can find it by querying V$BACKUP_ARCHIVELOG_DETAILS.

2. Creating the instance for the test machine and creating the pfile by restoring it from spfile: 

Perform the following steps on the test database machine:
Use the oradim utility to create the instance for orcl database on the test machine. 

When we start the database in nomount mode we receive error because the parameter file is not found. This is expected because we are restoring to a new host, hence, Oracle will use a “dummy” parameter file to start the instance.

Restore and edit the server parameter file which is in the SP_FILE.BKP. Since SPFILE is a binary file we need to create the file from it and then make the initialization parameter changes.

Now that we have the SPFILE, we need to edit some of the location specific parameters in the initorcl.ora file to reflect the new host's directory structure. Following are the parameters that need to be changed:

Make sure the directories are already present.

3. Restoring the database on the New Host:

Restart the instance with the edited initialization parameter file.Then, restore the control file to whatever locations you specified in the CONTROL_FILES initialization parameter and then mount the database. After that, catalog the datafiles and archivelogs from the FULL_DATAFILES_350.bkp, ARCHIVE_LOG_356.bkp and ARCHIVE_LOG_357.bkp.

Execute the following RMAN restore and recovery script. The script does the following things:
  • SET NEWNAME : For each data file on the destination host that is restored to a different path than it had on the source host, use this command to specify the new path on the test machine. If the file systems on the destination system are set up to have the same paths as the source host, then do not use SET NEWNAME for those files restored to the same path as on the source host.
  • ALTER DATABASE RENAME FILE : For each online redo log that is to be created at a different location than it had on the source host, use this SQL
    commands to specify the path name on the destination host. If the file
    systems on the destination system are set up to have the same paths as
    the source host, then do not use ALTER DATABASE RENAME FILE for those files restored to the same path as on the source host.
  • SET UNTIL : Perform this operation to limit recovery to the end of the archived redo logs. The recovery stops with an error if no SET UNTIL command is specified.
  • RESTORE the database.
  • SWITCH DATAFILE ALL : Run the SWITCH DATAFILE ALL command so that the control file recognizes the new path names as the official new names of the data files.
  • RECOVER the database.
Following is the output of the restore-script.sql. Execute the following code in RMAN:

From the RMAN prompt, open the database with the RESETLOGS option:

If all the above steps are executed properly, then you will have the production database installed on the test machine. 

Comments

  1. Ziya6 September 2023 at 18:27

    This article which you have shared here It is an engaging and gainful article for us. Continue imparting this sort of info, software development in chennai

    ReplyDelete

Post a Comment

Popular posts from this blog

Oracle Architecture - Explained In Detailed - Administration I

By -
Image
Figures shown in these notes are from Oracle® Database Concepts 11 g Release 2 Objectives These notes introduce the Oracle server architecture.   The architecture includes physical components, memory components, processes, and logical structures. Primary Architecture Components The figure shown above details the Oracle architecture. Oracle server :   An Oracle server includes an Oracle Instance and an Oracle database .   ·         An Oracle database includes several different types of files:   datafiles, control files, redo log files and archive redo log files.   The Oracle server also accesses parameter files and password files.   ·         This set of files has several purposes.   o    One is to enable system users to process SQL statements.   o    Another is to improve system performance.   o    Still a...
Read more

Oracle Database SQL - Practise - Question - Scott Schema Examples

By -
Image
Oracle SQL Practice Questions for Students All Question are Based on Scott Schema Exhibits EMP TABLE Exhibits DEPT TABLE Exhibits SALGRADE TABLE Question 1) Display all the records in emp table? 2) Display all the records in emp table where employee belongs to deptno 10? 3) Display all the records in emp table where employee does not belong to deptno 30? 4) Display total number of records in Emp table? 5) Display emp table with salary descending order? 6) Display first five records in employee table? 7) Display all the records in emp table order by ascending deptno, descending salary? 8) Display all employees those who were joined in year 1981? 9) Display COMM in emp table. Display zero in place of null. 10) Display the records in emp table where MGR in 7698,7566 and sal should be greater then 1500 11) Display all employees where employees hired before 01-JAN-1981 12) Display all employees with...
Read more

Step by Step - How to resolve redo log file corruption using ALTER DATABASE CLEAR UNARCHIVED LOGFILE command

By -
Image
Connect to sys using sqlplus in admin command prompt Check whether your database is in archive log mode or not. Run command ARCHIVE LOG LIST. It will show you the following details: It is always preferable to have your database in archive log mode. The reason being if your database is in archive log mode you can recover from all committed changes in the event of an OS or disk failure. This query will display the path of redo log files, their group and their status. It is always recommended to have a minimum of two members in one group. As you can see I have only 1 member in group 6 whose current status is INACTIVE. I intentionally have 1 member to generate a scenario for the sake of this practical. Now go to the specified path where the redo log member of group 6 is located and open it in a notepad. It will show some symbolic data. Delete couple of lines and save it the original location which is the one we got as a result of query. Why did we do the above step i....
Read more