database option - RMAN

Technically, database backup is not an option with database cloud services, it is bundled into the service as it is with on premise systems. Previously, we talked about backup and restore through the database cloud console. Unfortunately, before we an talk about Data Guard and how to set it up we need to dive a little deeper into RMAN. The first step in setting up Data Guard is to replicate data between two database instances. The recommended way of doing this is with RMAN. You can do this with a backup and recover option or duplicate option. We will look primarily at the duplicate option.

The topic of RMAN is a complex and challenging subject to tackle. There are many configuration options and ways to set up backups and data sets as well as many ways to recover rows, tables, or instances. Some books on RMAN include

• Oracle Database 12c Oracle RMAN Backup & Recovery
• RMAN for Absolute Beginners
• Oracle RMAN 11g Backup and Recovery (Oracle Press)
• RMAN Recipes for Oracle Database 11g: A Problem-Solution Approach (Expert's Voice in Oracle)
• RMAN Recipes for Oracle Database 12c: A Problem-Solution Approach (Expert's Voice in Oracle)
• Oracle RMAN Database Duplication
• Oracle Backup and Recovery: Expert secrets for using RMAN and Data Pump (Oracle In-Focus) (Volume 42)
• Oracle Backup and Recovery: All about Oracle Backup and Recovery
• Oracle RMAN Pocket Reference

Fortunately, to setup Data Guard, we don't need to read all of this material but just need to know the basics. Unfortunately, we can't just click a button to make Data Guard work and automatically setup the relationships, replicate the data, and start log shipping. The key command that we need to get the backup from the primary to the standby is the RMAN command. We can execute this from the primary or the standby because RMAN provides a mechanism to remotely call the other system assuming that port 1521 is open between the two database instances

$ rman target user1/password1@system1 auxiliary user2/password2@system2

In this example user1 on system1 is going to backup the instance that it default connects to and replicates to system2 using the user2 credentials. This command can be executed on either system because we are specifically stating what the source is with the name target and what the standby is with the name auxiliary. Once we connect we can then execute

rman> duplicate target database for standby from active database;

What this will do is replicate the database on system1 and push it to system2. The command will also setup a barrier point in time so that changes to system1 are shipped from this point forward when you enable Data Guard. According to Oracle Data Guard 11gR2 Administration Beginner's Guide (Chapter 2) the output of this command should look something like

Starting Duplicate Db at 26-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
...
contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u02/app/oracle/oradata/orcl/control01.ctl';
   restore clone controlfile to  '/u02/app/oracle/flash_recovery_area/orcl/control02.ctl' from
 '/u02/app/oracle/oradata/orcl/control01.ctl';
}
executing Memory Script
....
sql statement: alter database mount standby database
...
Starting backup at 26-JUL-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
output file name=/u02/app/oracle/oradata/orcl/system01.dbf tag=TAG20120726T160751
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:04
channel ORA_DISK_1: starting datafile copy
...
sql statement: alter system archive log current
contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=789667774 file name=/u02/app/oracle/oradata/orcl/system01.dbf
...
Finished Duplicate Db at 26-JUL-12

In this example we copied the system01.dbf file from system1 across the network connection and wrote it to /u02/app/oracle/oradata/orcl/system01.dbf on system2.

Let's take a step back and talk about RMAN a little bit to understand what is going on here. If we look at Oracle RMAN Pocket Reference it details some of the benefits of using RMAN over file system copy or disk cloning to backup a database. These include

• Incremental backups that only copy data blocks that have changed since the last backup.
• Tablespaces are not put in backup mode, thus there is no extra redo log generation during online backups.
• Detection of corrupt blocks during backups.
• Parallelization of I/O operations.
• Automatic logging of all backup and recovery operations.
• Built-in reporting and listing commands.

I would add

• Compression of data as it is written
• Encryption of data as it is written
• Tiered storage of backups to disk and secondary target (cheaper disk, cloud, tape, etc)

When RMAN executes it creates a recovery catalog database which is basically a table in the sys area that records the schema within the catalog database and the
tables (and supporting objects) within the schema that contain data pertaining to RMAN backup and recovery operations performed on the target. It also stores details about the physical structure of the target database,
a log of backup operations performed on the target database’s datafiles, control files, and archived redo log files as well as
stored scripts containing frequently used sequences of RMAN commands

When we execute a backup command we create a backup set that is written to the recovery catalog. The backup set is given a tag that we can reference and restore from. If we do daily incrementals we might want to use a part of the date to create the tag. We can restore to a specific point or date in time from our incremental backups.

If we are worried about having usernames and passwords being passed in via the command line or embedded in scripts we could store this password in the database with the orapwd command. This creates a username/password pair and stores it where RMAN can easily pull it from the database. We do need to give the rmanadmin user rights to execute as SYSDBA but this is easily done with a grant command. Once we do this we can drop the username and password from the rman command and only pass in the username@system parameter. The key reason that you might want to do this is invoking the command from the command line with the password exposes the password through the ps command which can be executed by any user. Embedding the password with the orapwd command helps hide this password.

The nice thing about RMAN is that you can backup and restore parts rather than all of a database. You can execute

RMAN> backup tablespace system, user;
RMAN> backup '/u01/app/oracle/oradata/ORCL/system01.dbf';
RMAN> backup incremental level 4 cumulative database skip readonly;

which will backup the user and system tables, backup the system01.dbf file and all of the tables that it includes, and do a backup of the data that has changed since the last level 4 backup and user previous lower level backups to aggregate changes into the current backup. Note that these three commands do significantly different things. We can look at what we have backed up using the

RMAN> list backups;

to see our backup set and where they are stored. When we looked at the database backup cloud service we went through a backup and recovery.

If we had done a list backups after this backup we would have noticed that the data written to SBT_TAPE was really written to cloud storage and potentially to local disk. We can then point our standby system to this backup set and restore into our database instance. This is done by importing the catalog or registering the target when we do the backup. The registration is done with a command like

$ rman target / catalog rman_901/rman_901@rman_catalog

where we are backing up a local database signified by the "/" and adding the host rman_catalog with username rman_901 and password rman_901.

My recommendation is to look at chapter 12 of Oracle Database 12c Oracle RMAN Backup & Recovery because it details how to use the duplicate option for rman. This is key to setting up Data Guard because it replicates a table from a primary system onto a standby system just prior to starting the Data Guard services. The command could be as simple as

RMAN> duplicate target database to standby1;

This will duplicate your existing instance from your on premise to a cloud or other on premise instance identified by the label standby1. This typically correlates to an ip address of a secondary system and could be a short name like this or a fully qualified domain name. We could get more complex with something like

RMAN> duplicate target database to standby1 
pfile=/u02/app/oracle/oradata/ORCL/init.ora
log_file_name_convert=('primary','standby');

This will do the same thing that the previous command did but read the init.ora file for the ORCL instance and convert anything in the /u02/app/oracle/oradata/ORCL/primary on our existing system to /u02/app/oracle/oradata/ORCL/standby on our target standby1 system. This is an easy way to replicate data from a PDB called primary to a PDB called standby prior to setting up a Data Guard relationship. The steps recommended to create and configure an RMAN copy are

1. On your standby server, build your auxiliary database directory structures (aka your target directory)
2. On your primary server, make a copy of the target init.ora file so that it can be moved to the standby server.
3. Move the target init.ora file to the auxiliary site with scp or other software to copy files.
4. Start or restart the standby instance in NOMOUNT mode
5. Configure the listener.ora at the standby site
6. Configure the tnsnames.ora file at the primary site
7. Create a password file at the standby server
8. Move the FRA files from primary to standby
9. From the primary system, run your duplicate command within RMAN
   

You can add parameters to allow for parallel copies of the data. You probably should not compress or encrypt the data since we will be pulling it from the backup and writing it into a database. We could potentially compress the data but it will not compress the data on the target system, only compress it for transmission across the internet or local network.

In summary, we needed to dive a little deeper into RMAN than we did before. RMAN is needed to duplicate data from our primary to the target prior to log shipping. There are some complexities associated with RMAN that we exposed and the steps needed to get a secondary site ready with rman are not trivial and need an experienced operating system admin and DBA to get this working. One of the new features of provisioning a cloud database service is a checkbox to create a Data Guard replica in another data center. One of the new features of installing a 12.2.2 database instance is also rumored to have a clone to cloud with Data Guard checkbox. As you install a new on premise database or in cloud database these complex steps are done behind the scenes for you as you would expect from a platform as a service model. Amazon claims to do this with site to site replication and restarting the database in another zone if something happens but this solution requires a reconnection from your application server and forcing your users to reauthenticate and reissue commands in flight. Using Data Guard allows your application server to connect to your primary and standby databases. If the primary fails or times out, the application server automatically connects to the standby for completion of the request. All of this is dependent upon RMAN working and replicating data between two live databases so that log shipping can assume that both servers are in a known state with consistent data on both systems.