Recovering a Database from Object Storage
This topic is not applicable to Exadata DB systems.
This topic explains how to recover a database from a backup stored in Object Storage. The service is a secure, scalable, on-demand storage solution in Oracle Cloud Infrastructure. For information on using Object Storage as a backup destination, see Backing Up a Database to Oracle Cloud Infrastructure Object Storage.
You can recover a database using the Console, API, or by using RMAN.
Required IAM Policy
To use Oracle Cloud Infrastructure, you must be granted security access in a policy by an administrator. This access is required whether you're using the Console or the REST API with an SDK, CLI, or other tool. If you get a message that you don’t have permission or are unauthorized, verify with your administrator what type of access you have and which compartment you should work in.
If you're new to policies, see Getting Started with Policies and Common Policies.
Prerequisites
The DB system requires access to the Oracle Cloud Infrastructure Object Storage service, including connectivity to the applicable Swift endpoint for Object Storage. Oracle recommends using a service gateway with the VCN to enable this access. For more information, see these topics:
- Network Setup for DB Systems: For information about setting up your VCN for the DB system, including the service gateway.
- Can I use Oracle Cloud Infrastructure Object Storage as a destination for my on-premises backups?: For information about the Swift endpoints to use.
Using the Console
You can use the Console to restore the database from a backup in the Object Storage that was created by using the Console or the API. You can restore to the last known good state of the database, or you can specify a point in time or an existing System Change Number (SCN). You can also create a new database by using a standalone backup.
The list of backups you see in the Console does not include any unmanaged backups (backups created directly by using RMAN
or dbcli
).
Restoring a database with Data Guard enabled is not supported. You must first remove the Data Guard association by terminating the standby database before you can restore the database.
Restoring an Existing Database
- Open the navigation menu. Under Oracle Database, click Bare Metal, VM, and Exadata.
-
Choose your Compartment.
A list of DB systems is displayed.
-
Find the DB system where the database is located, and click the system name to display details about it.
A list of databases is displayed.
-
Find the database you want to restore, and click its name to display details about it.
A list of backups is displayed in the default view of the database details. You can also access the list of backups for a database by clicking on Backups under Resources.
- Click Restore.
-
Select one of the following options, and then click Restore Database:
- Restore to the latest: Restores the database to the last known good state with the least possible data loss.
- Restore to the timestamp: Restores the database to the timestamp specified.
-
Restore to System Change Number (SCN): Restores the database using the SCN specified. This SCN must be valid.
Tip
You can determine the SCN number to use either by accessing and querying your database host, or by accessing any online or archived logs.
-
Confirm when prompted.
If the restore operation fails, the database will be in a "Restore Failed" state. You can try restoring again using a different restore option. However, Oracle recommends that you review the
RMAN
logs on the host and fix any issues before reattempting to restore the database.
- Open the navigation menu. Under Oracle Database, click Bare Metal, VM, and Exadata.
-
Choose your Compartment.
A list of DB systems is displayed.
-
Find the DB system where the database is located, and click the system name to display details about it.
A list of databases is displayed.
- Find the database you want to restore, and click its name to display details about it.
- Click Restore.
- In the Restore Database dialog, select Restore to the latest, Restore to timestamp, or Restore to System Change Number (SCN). Specify a timestamp or System Change Number if you are using an option that requires either.
- Click Restore Database.
Creating a New Database from a Backup
You can use a backup to create a database in an existing DB system or to launch a new DB system. See the following procedures for more information:
Using the API
For information about using the API and signing requests, see REST APIs and Security Credentials. For information about SDKs, see Software Development Kits and Command Line Interface.
Use these API operations to recover a database:
- ListBackups
- GetBackup
- RestoreDatabase
- CreateDbHome - For creating a DB system database from a standalone backup.
For the complete list of APIs for the Database service, see Database Service API.
Using an RMAN Backup
This topic explains how to recover a Recovery Manager (RMAN) backup stored in Object Storage.
Prerequisites
You'll need the following:
- A new DB system to restore the database to (see assumptions below). For more information, see Creating Bare Metal and Virtual Machine DB Systems.
- The Oracle Database Cloud Backup Module must be installed on the DB system. For more information, see Installing the Backup Module on the DB System.
Assumptions
The procedures below assume the following:
- A new DB system has been created to host the restored database and no other database exists on the new DB system. It is possible to restore to a DB system that has existing databases, but that is beyond the scope of this topic.
-
The original database is lost and all that remains is the latest RMAN backup. For virtual machine DB systems, the procedure assumes the DB system (inclusive of the database) no longer exists.
Caution
Any data not included in the most recent backup will be lost.
- The Oracle Wallet and/or encryption keys used by the original database at the time of the last backup is available.
- The RMAN backup contains a copy of the control file and spfile as of the most recent backup as well as all of the datafile and archivelog backups needed to perform a complete database recovery.
- An RMAN catalog will not be used during the restore.
Setting Up Storage on the DB system
-
SSH to the DB System.
ssh -i <private_key_path> opc@<db_system_ip_address>
-
Log in as opc and then sudo to the root user. Use
sudo su -
with a hyphen to invoke the root user's profile, which will set the PATH to the dbcli directory (/opt/oracle/dcs/bin
).login as: opc [opc@dbsys ~]$ sudo su -
-
You can use an existing empty database home or create a new one for the restore. Use the applicable commands to help you complete this step.
If you will be using an existing database home:
-
Use the Dbhome Commands command to list the database homes.
[root@dbsys ~]# dbcli list-dbhomes ID Name DB Version Home Location ---------------------------------------- -------------------- ---------- --------------------------------------------- 2e743050-b41d-4283-988f-f33d7b082bda OraDB12102_home1 12.1.0.2 /u01/app/oracle/product/12.1.0.2/dbhome_1
-
Use the Database Commands command to ensure the database home is not associated with any database.
If necessary, use the Dbhome Commands command to create a database home for the restore.
-
-
Use the Dbstorage Commands to set up directories for DATA, RECO, and REDO storage. The following example creates 10GB of ACFS storage for the rectest database.
[root@dbsys ~]# dbcli create-dbstorage --dbname rectest --dataSize 10 --dbstorage ACFS
Note
When restoring a version 11.2 database, ACFS storage must be specified.
Performing the Database Restore and Recovery
-
SSH to the DB system, log in as opc, and then become the oracle user.
sudo su - oracle
-
Create an entry in
/etc/oratab
for the database. Use the same SID as the original database.db1:/u01/app/oracle/product/12.1.0.2/dbhome_1:N
-
Set the ORACLE_HOME and ORACLE_SID environment variables using the oraenv utility.
. oraenv
-
Obtain the DBID of the original database. This can be obtained from the file name of the
controlfile
autobackup on the backup media. The file name will include a string that contains the DBID. The typical format of the string isc-DDDDDDDDDDDD-YYYYMMDD-NN
whereDDDDDDDDDDDD
is the DBID,YYYYMMDD
is the date the backup was created, andNN
is a sequence number to make the file name unique. The DBID in the following examples is 1508405000. Your DBID will be different.Use the following curl syntax to perform a general query of Object Storage. The parameters in red are the same parameters you specified when installing the backup module as described in Installing the Backup Module on the DB System.
curl -u '<user_ID>.com:<auth_token>' -v https://swiftobjectstorage.<region_name>.oraclecloud.com/v1/<object_storage_namespace>
See Regions and Availability Domains to look up the region name.
For example:
curl -u 'djones@mycompany.com:1cnk!d0++ptETd&C;tHR' -v https://swiftobjectstorage.<region_name>.oraclecloud.com/v1/myobjectstoragenamespace
To get the DBID from the control file name, use the following syntax:
curl -u '<user_id>.com:<auth_token>' -v https://swiftobjectstorage.<region_name>.oraclecloud.com/v1/<object_storage_namespace>/<bucket_name>?prefix=sbt_catalog/c-
For example:
curl -u 'djones@mycompany.com:1cnk!d0++ptETd&C;tHR' -v https://swiftobjectstorage.<region_name>.oraclecloud.com/v1/myobjectstoragenamespace/dbbackups/?prefix=sbt_catalog/c-
In the sample output below, 1508405000 is the DBID.
{ "bytes": 1732, "content_type": "binary/octet-stream", "hash": "f1b61f08892734ed7af4f1ddaabae317", "last_modified": "2016-08-11T20:28:34.438000", "name": "sbt_catalog/c-1508405000-20160811-00/metadata.xml" }
-
Run RMAN and connect to the target database. There is no need to create a
pfile
orspfile
or use a backupcontrolfile
. These will be restored in the following steps. Note that the target database is(not started)
. This is normal and expected at this point.rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jun 22 18:36:40 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database (not started)
-
Set the DBID using the value obtained above.
RMAN> set dbid 1508405000; executing command: SET DBID
-
Run the
STARTUP NOMOUNT
command. If the server parameter file is not available, RMAN attempts to start the instance with a dummy server parameter file. The ORA-01078 and LRM-00109 errors are normal and can be ignored.RMAN> STARTUP NOMOUNT startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/initdb1.ora' starting Oracle instance without parameter file for retrieval of spfile Oracle instance started Total System Global Area 2147483648 bytes Fixed Size 2944952 bytes Variable Size 847249480 bytes Database Buffers 1254096896 bytes Redo Buffers 43192320 bytes
-
Restore the server parameter file from autobackup.
The SBT_LIBRARY is the same library specified with the
-libDir
parameter when the Backup Module was installed, for example/home/oracle/lib/
.The OPC_PFILE is the same file specified with the
-configfile
parameter when the Backup Module was installed, for example/home/oracle/config
.set controlfile autobackup format for device type sbt to '%F'; run { allocate channel c1 device type sbt PARMS 'SBT_LIBRARY=/home/oracle/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/config)'; restore spfile from autobackup; }
-
Create the directory for audit_file_dest. The default is
/u01/app/oracle/admin/$ORACLE_SID/adump
. You can see the setting used by the original database by searching the spfile for the string,audit_file_dest
.strings ${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora | grep audit_file_dest *.audit_file_dest='/u01/app/oracle/admin/db1/adump' mkdir -p /u01/app/oracle/admin/db1/adump
-
If block change tracking was enabled on the original database, create the directory for the block change tracking file. This will be a directory under
db_create_file_dest
. Search thespfile
for the name of the directory.strings ${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora | grep db_create_file_dest *.db_create_file_dest='/u02/app/oracle/oradata/db1' mkdir -p /u02/app/oracle/oradata/db1/<$ORA_UNQNAME if available or database name>/changetracking
-
Restart the instance with the restored server parameter file.
STARTUP FORCE NOMOUNT;
-
Restore the controlfile from the RMAN autobackup and mount the database.
set controlfile autobackup format for device type sbt to '%F'; run { allocate channel c1 device type sbt PARMS 'SBT_LIBRARY=/home/oracle/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/config)'; restore controlfile from autobackup; alter database mount; }
-
Restore and recover the database.
RESTORE DATABASE; RECOVER DATABASE;
-
RMAN will recover using archived redo logs until it can't find any more. It is normal for an error similar to the one below to occur when RMAN has applied the last archived redo log in the backup and can't find any more logs.
unable to find archived log archived log thread=1 sequence=29 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 06/28/2016 00:57:35 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 29 and starting SCN of 2349563
-
Open the database with resetlogs.
ALTER DATABASE OPEN RESETLOGS;
The recovery is complete. The database will have all of the committed transactions as of the last backed up archived redo log.