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

  1. SSH to the DB System.
    ssh -i <private_key_path> opc@<db_system_ip_address>
  2. 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 - 
  3. 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.
  4. Use the Dbstorage Commands to list the storage ID. You'll need the ID for the next step.
    dbcli list-dbstorages
    Output:
    ID                                       Type   DBUnique Name        Status
    ---------------------------------------- ------ -------------------- ----------
    9dcdfb8e-e589-4d5f-861a-e5ba981616ed     Acfs   tdetest              Configured
  5. Use the Dbstorage Commands with the storage ID from the previous step to list the DATA, RECO and REDO locations.
    dbcli describe-dbstorage --id 9dcdfb8e-e589-4d5f-861a-e5ba981616ed
    Output:
    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 
  6. 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, and db_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.

  1. On the DB system, become the oracle user:
    sudo su - oracle
  2. Create the following directory, if it does not already exist:
    mkdir /opt/oracle/dcs/commonstore/wallets/tde/<db_unique_name>
  3. Copy the ewallet.p12 file from the source database to the directory you created in the previous step.
  4. 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.)

  5. 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 from oracle:asmadmin to oracle:oinstall.

    ls -ltr /opt/oracle/dcs/commonstore/wallets/tde/<db_unique_name>
    Output:
    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.

  1. 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
  2. Change to the directory that contains the backup module opc_install.jar file.
    cd /opt/oracle/oak/pkgrepos/orapkgs/oss/<version>/
  3. 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

  1. Start the database in nomount mode.
    startup nomount
  2. 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
  3. 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;
  4. 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

  1. 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.

  2. Redirect the restore using set newname to restore the data files in OMF format and use switch 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
  3. 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;

Reset the Logs

Reset the logs.

alter database open resetlogs;

Prepare to Register the Database

Before you register the database:

  1. 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.
  2. Verify that the database has registered with the listener and the service name.
    lsnrctl services
  3. 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>
  4. 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.

  5. 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.

  6. Make sure the database is running on spfile by using the SQL*Plus command.
    SHOW PARAMETERS SPFILE
  7. (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.
  8. Copy the folder $ORACLE_HOME/sqlpatch from source database to the target database. This will enable the dbcli 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.

Note

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.
  1. 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:
    dbcli  list-dbhomes
    Output:
    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.)

  2. 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.
    $ORACLE_HOME/OPatch/opatch lsinventory
    Output:
    ...
    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
  3. Start SQL*Plus and execute the catbundle.sql script, for example:
    startup 
    connect / as sysdba
    @$ORACLE_HOME/rdbms/admin/catbundle.sql psu apply
    exit
  4. Apply the sqlpatch, using the overlay patch number from the previous step, for example:
    connect / as sysdba
    @$ORACLE_HOME/sqlpatch/24460960/postinstall.sql 
    exit
Note

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.

  1. Make sure the database files were restored in OMF format.
  2. Make sure the database is listed in the Database Commands output.
  3. 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.
  4. If you associated a backup configuration when you registered the database, run a test back up using the Backup Commands.
  5. If the restored database contains a CDB and PDBs, verify that patches have been applied to all PDBs.