Backup and recovery scenarios

Now is the time to play with some backup and recovery scenarios. Due to the limitations of pages in this book, we will cover only a few scenarios. Remember that you have many scenarios available in the chapters of this book.

Active duplication of a database to a different server with the same structure (non-OMF and non-ASM)

When executing an active duplication, RMAN automatically copies the server parameter file to the destination host from the source, restarts the auxiliary instance using the server parameter file, copies all necessary database files and archived redo logs over the network to the destination host, and recovers the destination database. Finally, RMAN will open the destination (target) database with the RESETLOGS option to be able to create all online redo logs.

The following steps are required:

1. Create a new virtual machine using Oracle database 12.1.0.1 and Oracle Linux 6.4 (without creating a database). Set up the virtual machine with a proper IP and HOSTNAME.

2. Follow the Oracle installation guide for Linux (http://docs.oracle.com/cd/E16655_01/install.121/e17720/toc.htm), and prepare this machine for the installation of the Oracle database. When creating the Oracle user, please create it to use the password oracle.

3. Clone the newly created virtual machine and give to it a new IP and HOSTNAME. It will be used as the auxiliary server (target).

4. Create a non-CDB database called (in the Virtual Machine created in step 1) orcl using DBCA. This will be the source database.

5. If the source database is open, archiving must be enabled. If the source database is not open, the database does not require instance recovery.

6. In the auxiliary server, create the same directory structure used by the source database created in step 3. For example:

   • mkdir /u01/app/oracle/fast_recovery_area

   • mkdir /u01/app/oracle/fast_recovery_area/orcl

   • mkdir /u01/app/oracle/admin/orcl/adump

   • mkdir /u01/app/oracle/oradata/orcl

The steps for the active duplication will be:

1. In the auxiliary server (target), create a new password file to be used by the cloned database (connected as the OS user Oracle).

   $ orapwd file=/u01/app/oracle/product/12.1/db_1/dbs/orapworcl password=oracle entries=10
   

2. Add the auxiliary database information to the source database TNSNAMES.ORA file (located at $ORACLE_HOME/network/admin). For example:

   ORCL_DEST = 
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP) (HOST=172.28.10.62) (PORT = 1521)
       (CONNECT_DATA)
         (SERVER = DEDICATED)
         (SERVICE_NAME = orcl)
       )
     )
   

   Note

   Remember to replace the example IP address used above for the correct IP of your virtual machine (auxiliary server).

3. Create the LISTENER.ORA file in the auxiliary server. For example:

   SID_LIST_LISTENER =
     (SID_LIST =
       (SID_DESC =
         (ORACLE_HOME = /u01/app/oracle/product/12.1/db_1)
         (SID_NAME = orcl)
       )
     )
   LISTENER = 
     (DESCRIPTION_LIST =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP) (HOST = 172.28.10.62) (PORT = 1521))
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))
       )
     )
   ADR_BASE_LISTENER = /u01/app/oracle
   

   Note

   Remember to replace the example IP address used above for the correct IP of your virtual machine (auxiliary server)

4. Add source and auxiliary databases to the auxiliary TNSNAMES.ORA file. For example:

   LISTENER_ORCL =
     (ADDRESS = (PROTOCOL = TCP) (HOST = 172.28.10.62) (PORT = 1521))
   ORCL = 
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP) (HOST = 172.29.62.11) (PORT = 1521))
       (CONNECT_DATA = 
         (SERVER = DEDICATED)
         (SERVICE_NAME = orcl)
       )
     )
   
   ORCL_DEST = 
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP) (HOST = 172.28.10.62) (PORT = 1521))
       (CONNECT_DATA = 
         (SERVER = DEDICATED)
         (SERVICE_NAME = orcl)
       )
     )
   

   Note

   Remember to replace example IP addresses by the ones you used on your virtual machines.

5. Create a basic PFILE for the auxiliary database. To make it easy, create a PFILE from SPFILE in the source database and copy it to the auxiliary database.

6. Using SQL*Plus, start up the auxiliary database in NOMOUNT mode; for example:

   SQL> STARTUP NOMOUNT
   

7. Start the LISTENER for the auxiliary database; for example:

   $ lsnrctl start
   

8. In the auxiliary server, start your RMAN session connecting to the source database and the auxiliary database; for example:

   $ rman TARGET sys/oracle@orcl auxiliary sys/oracle@orcl_dest
   

9. Run the DUPLICATE command to make the magic happen, for example:

   RMAN> DUPLICATE DATABASE TO ORCL
   FROM ACTIVE DATABASE 
   SPFILE
   NOFILENAMECHECK;
   

   Note

   RMAN uses the pull method (using backup sets) by default.

You can also use COMPRESSED BACKUPSET when performing an active duplication. In this case the DUPLICATE command would be:

DUPLICATE TARGET DATABASE TO orcl
FROM ACTIVE DATABASE
PASSWORD FILE
USING COMPRESSED BACKUPSET;

By default, when the active duplication is completed, the new database will be open; if you do not want the database to be open, after the duplication, please use the following command:

RMAN> DUPLICATE DATABASE TO ORCL
FROM ACTIVE DATABASE 
SPFILE
NOFILENAMECHECK
NOOPEN;

Duplicating a PDB

If you want to duplicate a PDB, please create a multitenant container database called orcl with a PDB called pdborcl using DBCA, and follow all steps in the previous scenario and replace the DUPLICATE command with the following one:

RMAN> DUPLICATE TARGET DATABASE TO orcl
PLUGGABLE DATABASE pdborcl
FROM ACTIVE DATABASE
PASSWORD FILE
SPFILE
NOFILENAMECHECK;

Note

The root and seed database are automatically included in the duplication. The auxiliary instance must have been started with an initialization parameter file that includes the declaration enable_pluggable_database=TRUE.

ASM backup and restore

Perform the following steps:

1. Take an RMAN backup of the USERS tablespace:

     RMAN> BACKUP TABLESPACE users;
   

2. Create a new directory called abc in the disk group DATA. Once you create the directory, create an alias called +DATA/abc/users.f. This alias will point to the ASM datafile in which the USERS tablespace is stored:

     ASMCMD> mkdir +DATA1/abc
     ASMCMD> mkalias TBSJFV.354.323232323   +DATA1/abc/users.f
   

3. Backup the ASM metadata for the DATA disk group:

     ASMCMD> md_backup –g  data1
   

   The md_backup command will produce a restore script named ambr_backup_intermediate_file in the current directory. You'll need this file to perform the restore operation later.

4. Drop the disk group DATA to simulate the failure. You can use the dismount force clause to dismount the disk group and then force drop it:

   SQL> ALTER DISKGROUP data1 DISMOUNT FORCE;
   SQL> DROP DISKGROUP data1 FORCE INCLUDING CONTENTS;
   

5. Edit the ambr_backup_intermideate_file to remove the au_size entry. Once you make the change and save the restore file, run the md_restore command to restore the ASM metadata for the dropped disk group:

     ASMCMD> md_restore –b ambr_backup_intermediate_file –t full –g data
   

6. Once you restore the ASM metadata for the disk group, you must restore the USERS tablespace that was in the dropped disk group. You can use the backup that you made earlier of the USERS tablespace for this:

     RMAN> RESTORE TABLESPACE users;
   

Recovering from the loss of the SYSTEM tablespace

We are running this scenario with the assumption that you have a current backup of your database and all archived redo log files since your last backup are available. To recover your SYSTEM tablespace, please follow these steps:

1. Connect to RMAN:

   $ rman target / 
   

2. Start your DB in mount mode and restore your SYSTEM tablespace:

   RMAN> STARTUP MOUNT; 
   RMAN> RESTORE TABLESPACE SYSTEM;
   

3. Recover and open your DB:

   RMAN> RECOVER TABLESPACE SYSTEM; 
   RMAN> ALTER DATABASE OPEN; 
   

Note

If you do not have a current backup of your database and all archive redo log files are unavailable, you should perform a point-in-time recovery of your database and open it using the RESETLOGS option.

Recovering a lost datafile using an image from an FRA

We are running this scenario with the assumption that you have a current image copy of the datafile 7 on the FRA. To recover your datafile 7 from the FRA, perform the following steps:

1. Create a copy backup of your database.

2. Let's first put the datafile offline to simulate that we lost the datafile:

   SQL> ALTER DATABASE DATAFILE 7 OFFLINE;
   

3. Now let's do the trick; we will switch to the copy of the datafile available on our FRA:

   $ rman target /
   RMAN> SWITCH DATAFILE 7 TO COPY;
   RMAN> RECOVER DATAFILE 7;
   

4. All you need to do now is to put the datafile online and you are ready to go, without losing your time waiting for a backup to be retrieved from tape.

   RMAN> ALTER DATABASE DATAFILE 7 ONLINE;
   

   Note

   Remember to switch from datafile copy in the FRA to disk again; if not, you will have issues again.

   $ rman target /
   RMAN> BACKUP AS COPY DATAFILE 7 FORMAT '/Data/data/test3_tbs_01.dbf';
   RMAN> SWITCH DATAFILE 7 TO COPY;
   RMAN> RECOVER DATAFILE 7;
   RMAN> ALTER DATABASE DATAFILE 7 ONLINE;