Restoring SQL database from a backup file

Restoring a database require skills. You can use commandline or GUI tool to restore a database. Please take note of this:

1. You can restore a database from a backup file (.bak file);
2. You can restore a database from a backup file to a fresh database (database will be created during the process); and
3. You can use database restore from backup as a Data Migration (DM) or Disaster Recovery (DR) plan.

Syntax for restore:

--To Restore an Entire Database from a Full database backup (a Complete Restore):
RESTORE DATABASE { database_name | @database_name_var } 
 [ FROM <backup_device> [ ,...n ] ]
 [ WITH 
   {
    [ RECOVERY | NORECOVERY | STANDBY = 
        {standby_file_name | @standby_file_name_var } 
       ]
   | ,  <general_WITH_options> [ ,...n ]
   | , <replication_WITH_option>
   | , <change_data_capture_WITH_option>
   | , <FILESTREAM_WITH_option>
   | , <service_broker_WITH options> 
   | , <point_in_time_WITH_options—RESTORE_DATABASE> 
   } [ ,...n ]
 ]
[;]

Example of restore situation:

Using comandline, type the following to restore a database from a backup file:

RESTORE DATABASE TestPatient FROM DISK = 'C:\Testbackup.BAK'
GO

Using the GUI tool is the easiest, but you have to be careful of any option you're choosing.

Step 1: Right click on "Database" menu and select "Restore Database". 
A new dialogue box will appear with various options. This wizard will guide you through the whole database restore process.
NOTE: Make sure your backup file (.bak file) has been copied to the server and you have the required privilege to open the file or write on the file location.

Step 2: This is where you specify the database name. 
If you want to create a new database for this restore process, type in the database name.

Step 3: Source
Under the "Specify source" option, choose "From device" from the radio button and click on the menu on the right corner to expand your options.

Step 4: Add file
Click on "Add" to point the location of your file

Step 5: Choose file
From the box, choose the backup file. The file usually will have a ".bak" extension. Ensure the the correct file is selected, otherwise the restore will fail.
Click "OK" to continue

Step 6: Select file
Once the file the has been selected, click "OK" to move to the next step

Step 7: Restore
At this point, check the "Restore" box to indicate the exact file the database will be restored from, and then click "OK"

Step 8: Run process
Once you click "OK", the restore process runs. You can monitor the restore process through the "Completion" rate on the left tab. It will display in percentage the rate of completion.

Step 9: Done
Once the restore is completely done, you will get a message like this: "The restore of database XXX completed successfully".

Step 10: Verify
To verify that the restore was successfully completed, click on Databases and look from the newly created database in the drop-down.

Thanks for reading!

Encrypting RMAN backup

Configuring encryption for Oracle Recovery Manager (RMAN) is always a good step in the right direction. There may be different reasons for encrypting RMAN backup.

•  For greater security of data;
• Customer requirement (e.g, most companies requires encryption for any database file containing SSN, Credit Card number, date of birth, etc); or
• Complying with laws or regulations.

 
Option 1: Use global security wallet to encrypt backup

*configure the encryption wallet.

create a directory called "Wallet" in $ORACLE_BASE/admin/$ORACLE_SID

mkdir /home/oracle/app/oracle/admin/orcl/wallet

*Issue this command as SYS:

SQL> alter system set encryption key identified by "oracle1";

*Open the wallet:

SQL> alter system open encryption wallet identified by "oracle1";

*Log in to rman to encrypt backup

rman target /

RMAN> configure encryption for database on;

RMAN> backup database;


Option 2: Configure encryption right from RMAN

Another option is to configure encryption right from RMAN. You can also decide to use both options 1 and 2 together for double protection. Option one will be global because it controls every encryption done on the database including the backup. Option 2 is only restricted to RMAN prompt alone.

*Log in to RMAN and configure Encryption

RMAN target /

RMAN> set encryption on identified by "oracle1" only;

RMAN> backup database;

Note: You don't need a wallet to implement this. You can combine this with the global wallet transparent backup if you like. That will give you dual protection
You need to specify this password during recovery

  

Removing encryption

 

RMAN> configure encryption for database off;

Note: You need to set encryption off if at any point you don't need to encrypt your backup again. Some organizations will require you to remove encryption for data or databases that are only for TEST or DEVELOPMENT purpose.

Query encryption setting

Oracle provided a view to track encryption setting configured for recovery manager (RMAN). Issue this command as SYS:

select * from V$RMAN_ENCRYPTION_ALGORITHMS;

This is the view with all the details of rman encryption modes in the database. It is essential for a DBA to understand the encryption mode and their restrictions/limitations.

Thank you for reading.