Recover a Database from the OCI Classic Object Store
This article explains how to recover a database using a backup created by the Oracle Database Backup Module and stored in Oracle Cloud Infrastructure Object Storage Classic.
The following terms are used throughout this topic:
- Source database: The database backup in Object Storage Classic.
- Target database: The new database on a DB system in Oracle Cloud Infrastructure.
Prerequisites
You'll need the following:
- The service name, identity name, container, user name, and password for Oracle Cloud Infrastructure Object Storage Classic.
- The backup password if password-based encryption was used when backing up to Object Storage Classic.
- The source database ID, database name, database unique name (required for setting up storage).
- If the source database is configured with Transparent Data Encryption (TDE), you'll need a backup of the wallet and the wallet password.
- Tnsnames to setup for any database links.
- The output of
Opatch lsinventory
for the source database Oracle_home, for reference. - A copy of the sqlpatch directory from the source database home. This is required for rollback in case the target database does not include these patches.
Set 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 sudo su -
- Use the Dbstorage Commands to set up directories for DATA, RECO, and REDO storage. The following example creates 10GB of ACFS storage for the tdetest database.
dbcli create-dbstorage --dbname tdetest --dataSize 10 --dbstorage ACFS
Note
When migrating a version 11.2 database, ACFS storage must be specified. - Use the Dbstorage Commands to list the storage ID. You'll need the ID for the next step.
Output:dbcli list-dbstorages
ID Type DBUnique Name Status ---------------------------------------- ------ -------------------- ---------- 9dcdfb8e-e589-4d5f-861a-e5ba981616ed Acfs tdetest Configured
- Use the Dbstorage Commands with the storage ID from the previous step to list the DATA, RECO and REDO locations.
Output:dbcli describe-dbstorage --id 9dcdfb8e-e589-4d5f-861a-e5ba981616ed
DBStorage details ---------------------------------------------------------------- ID: 9dcdfb8e-e589-4d5f-861a-e5ba981616ed DB Name: tdetest DBUnique Name: tdetest DB Resource ID: Storage Type: Acfs DATA Location: /u02/app/oracle/oradata/tdetest RECO Location: /u03/app/oracle/fast_recovery_area/ REDO Location: /u03/app/oracle/redo/ State: ResourceState(status=Configured) Created: August 24, 2016 5:25:38 PM UTC UpdatedTime: August 24, 2016 5:25:53 PM UTC
- Note down the DATA, RECO and REDO locations. You'll need them later to set the
db_create_file_dest
,db_create_online_log_dest
, anddb_recovery_file_dest
parameters for the database.
Choose an ORACLE_HOME
Decide which ORACLE_HOME to use for the database restore and then switch to that home with the correct ORACLE_BASE, ORACLE_HOME, and PATH settings. The ORACLE_HOME must not already be associated with a database.
To get a list of existing ORACLE_HOMEs and to ensure that the ORACLE_HOME is empty, use the Dbhome Commands and the Database Commands, respectively. To create a new ORACLE_HOME, use the Dbhome Commands.
Copy the Source Database Wallets
Skip this section if the source database is not configured with TDE.
- On the DB system, become the oracle user:
sudo su - oracle
- Create the following directory, if it does not already exist:
mkdir /opt/oracle/dcs/commonstore/wallets/tde/<db_unique_name>
- Copy the ewallet.p12 file from the source database to the directory you created in the previous step.
- On the target host, make sure that
$ORACLE_HOME/network/admin/sqlnet.ora
contains the following line:ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNAME)))
Add the line if it doesn't exist in the file. (The line might not be there if this is a new home and no database has been created yet on this host.)
- Create the autologin wallet from the password-based wallet to allow auto-open of the wallet during restore and recovery operations.
For a version 12.1 or later database, use the
ADMINISTER KEY MANAGEMENT
command:$cat create_autologin_12.sh #!/bin/sh if [ $# -lt 2 ]; then echo "Usage: $0 <dbuniquename><remotewalletlocation>" exit 1; fi mkdir /opt/oracle/dcs/commonstore/wallets/tde/$1 cp $2/ewallet.p12* /opt/oracle/dcs/commonstore/wallets/tde/$1 rm -f autokey.ora echo "db_name=$1" > autokey.ora autokeystoreLog="autologinKeystore_`date +%Y%m%d_%H%M%S_%N`.log" echo "Enter Keystore Password:" read -s keystorePassword echo "Creating AutoLoginKeystore -> " sqlplus "/as sysdba" <<EOF spool $autokeystoreLog set echo on startup nomount pfile=autokey.ora ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/opt/oracle/dcs/commonstore/wallets/tde/$1' -- Keystore location IDENTIFIED BY "$keystorePassword"; shutdown immediate; EOF
Adjust the
cwallet.sso
permissions fromoracle:asmadmin
tooracle:oinstall
.
Output:ls -ltr /opt/oracle/dcs/commonstore/wallets/tde/<db_unique_name>
total 20 -rw-r--r-- 1 oracle oinstall 5680 Jul 6 11:39 ewallet.p12 -rw-r--r-- 1 oracle asmadmin 5725 Jul 6 11:39 cwallet.sso
For a version 11.2 database, use the
orapki
command:orapki wallet create -wallet wallet_location -auto_login [-pwd password]
Install the Oracle Database Backup Module
The backup module JAR file is included on the DB system but you need to install it.
- SSH to the DB system, log in as opc, and then become the oracle user.
ssh -i <path to SSH key used when launching the DB System> opc@<DB System IP address or hostname> sudo su - oracle
- Change to the directory that contains the backup module
opc_install.jar
file.cd /opt/oracle/oak/pkgrepos/orapkgs/oss/<version>/
- To install the backup module, see the command syntax described in Installing the Oracle Database Cloud Backup Module for OCI Classic in Using Oracle Database Backup Cloud Service.
Set Environment Variables
Set the following environment variables for the RMAN and SQL*Plus sessions for the database:
ORACLE_HOME=<path of Oracle Home where the database is to be restored>
ORACLE_SID=<database instance name>
ORACLE_UNQNAME=<db_unique_name in lower case>
NLS_DATE_FORMAT="mm/dd/yyyy hh24:mi:ss"
Allocate an RMAN SBT Channel
For each restore operation, allocate an SBT channel and set the SBT_LIBRARY parameter to the location of the libopc.so
file and the OPC_FILE parameter to the location of the opc_sbt.ora
file, for example:
ALLOCATE CHANNEL c1 DEVICE TYPE sbt MAXPIECESIZE 2 G FORMAT '%d_%I_%U' PARMS 'SBT_LIBRARY=/tmp/oss/libopc.so ENV=(OPC_PFILE=/<ORACLE_HOME>/dbs/opc_sbt.ora)';
For more information about these files, see Files Created When the Oracle Database Cloud Backup Module for OCI Classic is Installed in Using Oracle Database Backup Cloud Service.
Ensure Decryption is Turned On
Make sure that decryption is turned on for all the RMAN restore sessions.
set decryption wallet open identified by <keystore password>;
For more information, see Providing the Password Required to Decrypt Encrypted Backups.
Restore Spfile
The following sample shell script restores the spfile. Set the $dbID
variable to the dbid of the database being restored. By default, spfile is restored to $ORACLE_HOME/dbs/spfile<sid>.ora
.
rman target / <<EOF
spool log to "`date +%Y%m%d_%H%M%S_%N`_dbid_${dbID}_restore_spfile.log"
startup nomount
set echo on
run {
ALLOCATE CHANNEL c1 DEVICE TYPE sbt MAXPIECESIZE 2 G FORMAT '%d_%I_%U' PARMS 'SBT_LIBRARY=/tmp/oss/libopc.so ENV=(OPC_PFILE=/tmp/oss/opc_sbt.ora)';
SET DBID=$dbID;
RESTORE SPFILE FROM AUTOBACKUP;
shutdown immediate;
EOF
Set the Database Parameters
- Start the database in nomount mode.
startup nomount
- Update spfile and modify the following parameters.
-
If the database storage type is ACFS, use the DATA, RECO, and REDO locations obtained from the
dbcli describe-dbstorage
command output, as described in Set Up Storage on the DB System:alter system set db_create_file_dest='/u02/app/oracle/oradata/' scope = spfile; alter system set db_create_online_log_dest_1='/u03/app/oracle/redo' scope = spfile; alter system set db_recovery_file_dest='/u03/app/oracle/fast_recovery_area' scope = spfile;
-
If the database storage type is ASM:
alter system set db_create_file_dest='+DATA' scope = spfile; alter system set db_create_online_log_dest_1='+RECO' scope = spfile; alter system set db_recovery_file_dest='+RECO' scope = spfile;
-
Set
db_recovery_file_dest_size
is not set or is set incorrectly:alter system set db_recovery_file_dest_size=<sizeG> scope=spfile;
-
Set
audit_file_dest
to the correct value:alter system set audit_file_dest=/u01/app/oracle/admin/<db_unique_name in lower case>/adump
-
- Remove the
control_files
parameter. The Oracle Managed Files (OMF) parameters will be used to create the control file.alter system reset control_files scope=spfile;
- Restart the database in nomount mode using the newly added parameters.
shutdown immediate startup nomount
Restore the Control File
Modify the following sample shell script for your environment to restore the control file. Set the $dbID
variable to the dbid of the database being restored. Set SBT_LIBRARY to the location specified in the -libDir
parameter when you installed the Backup Module. Set OPC-PFILE to the location specified in the -configFile
parameter, which defaults to ORACLE_HOME/dbs/opcSID.ora
.
rman target / <<EOF
spool log to "`date +%Y%m%d_%H%M%S_%N`_dbid_${dbID}_restore_controlfile.log"
set echo on
run {
ALLOCATE CHANNEL c1 DEVICE TYPE sbt MAXPIECESIZE 2 G FORMAT '%d_%I_%U' PARMS 'SBT_LIBRARY=/<Backup Module libDir>/libopc.so ENV=(OPC_PFILE=/<Backup Module configFile>/opcSID.ora)';
SET DBID=$dbID;
RESTORE CONTROLFILE FROM AUTOBACKUP;
alter database mount;
}
exit;
EOF
Restore the Database
- Preview and validate the backup. The database is now mounted and RMAN should be able to locate the backup from the restored controlfile. This step helps ensure that the list of archivelogs is present and that the backup components can be restored.
In the following examples, modify SBT_LIBRARY and OPC_PFILE as needed for your environment.
rman target / <<EOF spool log to "`date +%Y%m%d_%H%M%S_%N`_restore_database_preview.log" set echo on run { ALLOCATE CHANNEL c1 DEVICE TYPE sbt MAXPIECESIZE 2 G FORMAT '%d_%I_%U' PARMS 'SBT_LIBRARY=/tmp/oss/libopc.so ENV=(OPC_PFILE=/tmp/oss/opc_sbt.ora)'; ALLOCATE CHANNEL c2 DEVICE TYPE sbt MAXPIECESIZE 2 G FORMAT '%d_%I_%U' PARMS 'SBT_LIBRARY=/tmp/oss/libopc.so ENV=(OPC_PFILE=/tmp/oss/opc_sbt.ora)'; ALLOCATE CHANNEL c3 DEVICE TYPE sbt MAXPIECESIZE 2 G FORMAT '%d_%I_%U' PARMS 'SBT_LIBRARY=/tmp/oss/libopc.so ENV=(OPC_PFILE=/tmp/oss/opc_sbt.ora)'; restore database validate header preview; }
Review the output and if there are error messages, investigate the cause of the problem.
- Redirect the restore using
set newname
to restore the data files in OMF format and useswitch datafile all
to allow the control file to update with the new data file copies.rman target / <<EOF spool log to "`date +%Y%m%d_%H%M%S_%N`_restore_database_preview.log" set echo on run { ALLOCATE CHANNEL c1 DEVICE TYPE sbt MAXPIECESIZE 2 G FORMAT '%d_%I_%U' PARMS 'SBT_LIBRARY=/tmp/oss/libopc.so ENV=(OPC_PFILE=/tmp/oss/opc_sbt.ora)'; ALLOCATE CHANNEL c2 DEVICE TYPE sbt MAXPIECESIZE 2 G FORMAT '%d_%I_%U' PARMS 'SBT_LIBRARY=/tmp/oss/libopc.so ENV=(OPC_PFILE=/tmp/oss/opc_sbt.ora)'; ALLOCATE CHANNEL c3 DEVICE TYPE sbt MAXPIECESIZE 2 G FORMAT '%d_%I_%U' PARMS 'SBT_LIBRARY=/tmp/oss/libopc.so ENV=(OPC_PFILE=/tmp/oss/opc_sbt.ora)'; set newname for database to new; restore database; switch datafile all; switch tempfile all; recover database; }
This recovery will attempt to use the last available archive log backup and then fail with an error, for example:
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 07/20/2016 12:09:02 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 22 and starting SCN of 878327
- To complete the incomplete recovery, run a recovery using the sequence number and thread number shown in the RMAN-06054 message, for example:
recover database until sequence 22 thread 1;
Prepare to Register the Database
Before you register the database:
- Make sure the database COMPATIBLE parameter value is acceptable. If the value is less than the minimum, the database cannot be registered until you upgrade the database compatibility.
- Verify that the database has registered with the listener and the service name.
lsnrctl services
- Make sure the password file was restored or created for the new database.
ls -ltr $ORACLE_HOME/dbs/orapw<oracle sid>
If the file does not exist, create it using the orapwd utility.
orapwd file=<$ORACLE_HOME/dbs/orapw<$ORACLE_SID>> password=<sys password>
- Make sure the restored database if open in read write mode.
select open_mode from v$database;
The command output should indicate read write mode. The
dbcli register-database
command will attempt to run datapatch, which requires read write mode. If there are PDBs, they should also be in read write mode to ensure that datapatch runs on them. - From oracle home on the restored database, use the following command verify the connection to SYS:
conn sys/<password>@//<hostname>:1521/<database service name>
This connection is required to register the database later. Fix any connection issues before continuing.
- Make sure the database is running on spfile by using the SQL*Plus command.
SHOW PARAMETERS SPFILE
- (Optional) If you would like to manage the database backup with the dbcli command line interface, you can associate a new or existing backup configuration with the migrated database when you register it or after you register it. A backup configuration defines the backup destination and recovery window for the database. Use the Backupconfig Commands to create, list, and display backup configurations.
- Copy the folder
$ORACLE_HOME/sqlpatch
from source database to the target database. This will enable thedbcli register-database
command to roll back any conflicting patches.Note
If you are migrating a version 11.2 database, additional steps are required after you register the database. For more information, see Roll Back Patches on a Version 11.2 Database.
Register the Database on the DB System
The Database Commands registers the restored database to the dcs-agent so it can be managed by the dcs-agent stack.
The
dbcli register-database
command is not available on 2-node RAC DB systems.
As the root user, use the dbcli register-database
command to register the database on the DB system, for example:
dbcli register-database --dbclass OLTP --dbshape odb1 --servicename tdetest --syspassword
Output:
Password for SYS:
{
"jobId" : "317b430f-ad5f-42ae-bb07-13f053d266e2",
"status" : "Created",
"message" : null,
"reports" : [ ],
"createTimestamp" : "August 08, 2016 05:55:49 AM EDT",
"description" : "Database service registration with db service name: tdetest",
"updatedTime" : "August 08, 2016 05:55:49 AM EDT"
}
Update tnsnames.ora
Check the tnsnames.ora
in the backup location, check the database links used in the cloned database, and then add any relevant connection strings to the cloned database file at $ORACLE_HOME/network/admin/tnsnames.ora
.
Roll Back Patches on a Version 11.2 Database
For version 11.2 databases, the sqlpatch application is not automated, so any interim patches (previously known as a "one-off" patches) applied to the source database that are not part of the installed PSU must be rolled back manually in the target database. After registering the database, execute the catbundle.sql
script and then the postinstall.sql
script with the corresponding PSU patch (or the overlay patch on top of the PSU patch), as described below.
Tip:
Some interim patches may include files written to the $ORACLE_HOME/rdbms/admin directory as well as the $ORACLE_HOME/sqlpatch directory. Oracle recommends that you roll back these patches in the source database using the instructions in the patch read-me prior to migrating the database to OCI environment. Contact Oracle Support if you need assistance with rolling back these patches.- On the DB System, use the
dbcli list-dbhomes
command to find the PSU patch number for the version 11.2 database home. In the following sample command output, the PSU patch number is the second number in the DB Version column:
Output:dbcli list-dbhomes
ID Name DB Version Home Location Status ------------------------------------ ----------------- ------------------------------------- ----------------------------------------- ---------- 59d9bc6f-3880-4d4f-b5a6-c140f16f8c64 OraDB11204_home1 11.2.0.4.160719 (23054319, 23054359) /u01/app/oracle/product/11.2.0.4/dbhome_1 Configured
(The first patch number, 23054319 in the example above, is for the OCW component in the database home.)
- Find the overlay patch, if any, by using the
lsinventory
command. In the following example, patch number 24460960 is the overlay patch on top of the 23054359 PSU patch.
Output:$ORACLE_HOME/OPatch/opatch lsinventory
... Installed Top-level Products (1): Oracle Database 11g 11.2.0.4.0 There are 1 products installed in this Oracle Home. Interim patches (5) : Patch 24460960 : applied on Fri Sep 02 15:28:17 UTC 2016 Unique Patch ID: 20539912 Created on 31 Aug 2016, 02:46:31 hrs PST8PDT Bugs fixed: 23513711, 23065323, 21281607, 24006821, 23315889, 22551446, 21174504 This patch overlays patches: 23054359 This patch needs patches: 23054359 as prerequisites
- Start SQL*Plus and execute the
catbundle.sql
script, for example:startup connect / as sysdba @$ORACLE_HOME/rdbms/admin/catbundle.sql psu apply exit
- Apply the sqlpatch, using the overlay patch number from the previous step, for example:
connect / as sysdba @$ORACLE_HOME/sqlpatch/24460960/postinstall.sql exit
If the source database has one-off patches installed and those patches are not part of the installed PSU in the cloud environment, then the SQL changes that correspond to those one-off patches need to be rolled back. To rollback the SQL changes, copy the
$ORACLE_HOME/sqlpatch/<patch#>/postdeinstall.sql
script from the source environment to the cloud environment and execute the postdeinstall.sql
script.
Post Restore Checklist
After the database is restored and registered on the DB system, use the following checklist to verify the results and perform any post-restore customizations.
- Make sure the database files were restored in OMF format.
- Make sure the database is listed in the Database Commands output.
- Check for the following external references in the database and update them if necessary:
- External tables: If the source database uses external tables, back up that data and migrate it to the target host.
- Directories: Customize the default directories as needed for the restored database.
- Database links: Make sure all the required TNS entries are updated in the
tnsnames.ora
file in ORACLE_HOME. - Email and URLs: Make sure any email addresses and URLs used in the database are still accessible from the DB system.
- Scheduled jobs: Review the jobs scheduled in source database and schedule similar jobs as needed in the restored database.
- If you associated a backup configuration when you registered the database, run a test back up using the Backup Commands.
- If the restored database contains a CDB and PDBs, verify that patches have been applied to all PDBs.