Data restore refers to a group of techniques for restoring lost data or information, which is applied to a variety of circumstances, such as unintentional file deletion, improper hard disk or server formatting, system failures, cyberattacks, etc. So, data holders always try their best to keep the data safe. Backup and restore are the most effective and reassuring ways for data loss prevention. Click for Oracle database RMAN backup step-by-step guide.
This article is mainly about how to restore and an Oracle database from RMAN backups in multiple situations, how to perform disaster recovery with the built-in Oracle tool, and a substitute solution with simplified procedures.
Methods to restore Oracle Database from RMAN backup
In Oracle database, a datafile or control file must be restored to access the database for physical backup recovery, while to fully recover a restored datafile, online redo logs and archived redo logs must be applied to apply changes made after the backup.
You have two options in Oracle for restotinging physical files:
Use RMAN to restore the Oracle database.
Operate OS utilities to restore backups, and then execute SQL*Plus RECOVER command.
Steps to restore Oracle Database from RMAN backup
1. Start the RMAN client and connect to the target database as a user to check if there are any connection issues.
$ rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Wed Sep 28 14:10:30 2022
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DEVDB (DBID=111111)
RMAN>
2. Identify the files to restore.
The lost control file will immediately shut down the database, although some copy loss doesn’t need to restore the file from backup. Go to step 6 to see the recovery process.
Determine missing data file:
RMAN> VALIDATE DATABASE;
3. Preview backups for restore.
RESTORE DATABASE PREVIEW;
To specify information if there is too much.
RESTORE DATABASE PREVIEW SUMMARY;
4. Validate the backups to see if they are usable.
RESTORE ... VALIDATE
For testing whether RMAN can restore a given object from a backup.
VALIDATE BACKUPSET
For testing whether a backup set you specify is valid.
5. Provide passwords for the encrypted backup.
SET DECRYPTION IDENTIFIED BY password;
6. Restore the control file from RMAN backups.
You need to restore and mount the control file if all copies of it are unavailable. Run the RECOVER command, then boot the database with the RESETLOGS.
If the RMAN connects to a recovery catalog, repeat the steps for restoring the current control file. And set the DBID if the DB name is not unique.
If it doesn’t, restore a control file from autobackup.
1) Get the DBID, the number after “ctl_c-“, based on the control file name.
RMAN> SET DBID 00000;
2) Start the Oracle DB in nomount mode.
RMAN> STARTUP NOMOUNT;
3) Restore the controlfile from the RMAN autobackup.
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
4) Mount the database after restore.
RMAN> ALTER DATABASE MOUNT;
5) Restore the database and reset the online logs.
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;
7. Restore the archived redo logs (database is mounted or open).
The default location.
RMNAN> RESTORE ARCHIVELOG ALL;
To a new location.
RMNAN> SET ARCHIVELOG DESTINATION TO 'LOCATIONPATH';
RMNAN> RESTORE ARCHIVELOG ALL;
Chosen archive logs from 100 to 200,
RMNAN> RESTORE ARCHIVELOG FROM SEQUENCE 100 UNTIL SEQUENCE 200;
8. Restore Oracle database from RMAN full backup.
RMAN> RESTORE DATABASE;
9. Restore only specific tablespace (one and more).
1) Take the database offline.
ALTER TABLESPACE users OFFLINE IMMEDIATE;
2) Use “SHOW ALL” to see channels that are preconfigured, if not, use “CONFIGURE” to set up automatic channels.
3) Restore.
RMNAN> RESTORE TABLESPACE EXAMPLE;
RMNAN> RESTORE TABLESPACE EXAMPLE 1, EXAMPLE 2;
To new locations:
SET NEWNAME FOR DATAFILE 'PATH1' TO 'PATH2';
10. Restore only specific datafiles (one and more).
RMNAN> RESTORE DATAFILE 'FILENAME'
RMNAN> RESTORE DATAFILE 'FILENAME1', 'FILENAME2'
Too many command lines to list here that restore the Oracle database under different conditions, which, are the good data recovery helpers built in Oracle database, yet still, they are complex to use and may invoke errors causing headaches. Thus, there are various database solutions on the market to choose from for convenience.
How to Backup and Restore Oracle Database Easily?
RMAN backup is a popular Oracle database backup solution but it sitll not a very professional solution because it is complicated to execute backup and restore jobs and can't meet higher data protection requirements so you can choose a professional solution to replace it.
Vinchin Backup & Recovery is a versatile data solution that supports the backup and recovery of 10+ virtual machines and Oracle, MySQL, SQL Server, MariaDB, Postgres Pro, and PostgreSQL databases with automated and simplified procedures, smart strategies, user-friendly management, and anti-ransomware backup protection.
To backup the Oracle databases, you just need 4 steps to create the a backup job:
1. Select the databases you need to backup
2. Select the backup storage
3. Select the backup strategies which is plainly described
4. Submit the job
When you need to restore Oracle database, you can also create a recovery job in 4 steps via the wirzard. There are two modes for database restoration, Override Original Database that restores data to the production server and Create New Database.
In addition to the database protection, the solution also delivers advanced and compatible VM backup and recovery services for users, such as 15s instant recovery, cross-platform V2V migration across 10+ virtualization, data archiving, and more waiting for you to explore in the 60-day free trial below.
Restore Oracle databases from RMAN FAQs
1. Is it possible to restore a table from RMAN backup?
Yes, it is supported but you shoud make sure you have full backup of undo, SYSTEM, SYSAUX, and the tablespace that contains the tables or table partitions. See the detailed steps in the official Oracle guide.
2. What is the difference between restore and recover in RMAN?
Backup and recover are quite different in Oralce. Backup, still the commen concept, is restore Oracle data from backup while recover is is applying all of the transactions located in the online or offline (archived) redo and online undo segments to bring the database to a consistent state.
3. Can you perform point-in-time recovery with RMAN?
Yes, you can perform point-in-time recovery with RMAN. Just check the offical document for detailed steps.
Final Thoughts
RMAN is a great utility for Oracle database users to restore Oracle database from backup, but it can be a little tricky for non-techies, and one has to be very careful in case of data loss in the process. Use Vinchin Backup & Recovery which automates jobs easily and manages all backups in a central console. Don't miss the free trial.
