This article is mainly about how to restore and recover an Oracle database from backups in multiple situations, how to perform disaster recovery with the built-in Oracle tool, and a substitute solution with simplified procedures.
Updated by Iris Lee on 2023/04/21
Data recovery 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 recovery 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 recover 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.
In Oracle database, a datafile or control file must be rebuilt to access the database for physical backup recovery, while to recover a restored datafile, one has to use online redo logs archived redo logs, records of Oracle modifications made following the backup.
You have two options in Oracle for recovering physical files:
Use RMAN to restore the Oracle database.
Operate OS utilities to restore backups, and then execute SQL*Plus RECOVER command.
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 recover the file from backup. Go to step 6 to see the recovery process.
Determine missing data file:
RMAN> VALIDATE DATABASE;
3. Preview backups for recovery.
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 ... VALIDATEtests whether RMAN can recover a given object from a backup.
VALIDATE BACKUPSET tests 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 recovery.
RMAN> ALTER DATABASE MOUNT;
5) Recover 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 recovery 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 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.
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 real-time IO backup protection.
The solution completes Oracle database recovery within 3 steps after the basic setup of the component, that is, downloading the database backup agent on the login page and installing it.
1) Select the target restore point from the expanded Oracle backups in the server.
2) Select the Oracle database instance you want to restore.
3) Set up restore strategies.
The two modes for database restoration are Override Original Database that restores data to
the production server and Create New Database.
Review the information the submit the job, and you’ll be redirected to Monitor Center > Jobs page, by clicking the job name, you could see the progress. Once the job is done, the job will be automatically deleted from the current job list.
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.
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.
Share on:
