X

Pat Shuff's Blog

  • PaaS
    June 16, 2016

database option - backup and restore

Backup and recovery abilities are arguably the most critical skills required of a database administrator. Recovery Manager (RMAN) is Oracle’s standard backup and recovery tool; every Oracle DBA should be familiar with utilizing RMAN. Some DBAs use alternate tools since RMAN is an Oracle specific tool to backup data in a database. Alternatives include Veritas Backup Exec, Comvault Sympana, Legato Networker, EMC and NetApp tools, and other packages. I am not going to list books and software packages in this blog. When I did a search on Safari Books search for rman we get 9 books published in 2016, 16 in 2015, and 20 in 2014. There are also a ton of blogs so I suggest that you go with your favorite blog and search for rman or backup. There are hundreds of different ways to backup a database and restore the database as well as optimize how much space the database takes up in cold storage.

The important things to consider when you look at backup and recovery are

  • full or incremental backups
  • backing up to disk, tape, or cloud
  • replicating data to another site with disk mirroring, Data Guard, or Golden Gate
  • hot backup or cold backup along with middleware and file system backup synchronization
  • recovery point objective and recovery time objective
  • compression, deduplication, and encryption of data at rest
  • backup windows and restore windows

It is important to note that when you purchase DBaaS, independent of any edition, you get backup done for you based on the options you select at creation. When you create a database you can opt for no backup, local backup, and full backups. The no backup can be used for development and test instances. We might just want a sandbox to play in and don't care about keeping this data so that we can restore. If we loose the data for any reason we can recreate it from our production system. When you select local backups you get incremental backups daily at 2am and a full backup Sunday morning at 2am. This gives you a seven day window for recovery so that you can restore data back to your last full backup with daily incrementals. These backups go to the /u03 file system on the instance that you create. Nothing is copied off the instance that you create so a complete failure of the system could result in potential data loss. The full backup does an incremental to /u03 daily and a full backup Sunday morning at 2am to /u03 as well. Prior to the full backup, the backup file is copied to the Cloud Object Storage Container that you created prior to creating the database. When you created the database you specify the days that you want to retain backups. If, for example, you ask for a 90 day backup you get 13 full backups copied to the object storage. If you have a Java Cloud Service connected to this database, the Java configuration and war files are also copied to this same area. The backup is automatically done for you and can be reconfigured and done manually using the cloud specific commands. Refer to the
Using Oracle Database Cloud - Database as a Service Documentation (chapter 6)
to understand how to backup using the cloud commands to keep backups in sync with the automated utilities rather than doing an rman manually.

You can generate a backup before you make a change to the database with the following command

sudo /var/opt/oracle/bkup_api/bkup_api bkup_start

This command must be executed from the opc account and not the oracle account because it needs root roles to store data and update the logs associated with cron jobs. To restore the database from the last backup you execute the following command
sudo dbaascli orec --args -latest

You can list the database backups that exist with
sudo dbaascli orec --args -list

To restore from a specific backup you execute
sudo dbaascli orec --args -pitr backup-tag

where backup-tag is the name listed with the -list command.

The dbaascli command shuts down the database, extracts and restores configuration files, prepares for recovery, performs the recovery, and restarts the database instance after recovery. You can use the rman utility to restore individual tables or tablespaces as usual but tools exist to manage the rman repository that are accessible to do scheduled backups, full backups, and restores.

One of the nice features of RMAN is that you can duplicate a database to an alternate location and restore from an alternate location. For example, you can take a backup of your on premise database and write to the Oracle Object Cloud Service. You can then create a database in the cloud using the DBaaS and load the data into this database from your backup in cloud object storage. You can also provision an on premise database, execute replication commands to configure the second database as a Data Guard replica, initiate the backup of your on premise instance, initiate the restore on your cloud instance, and configure Data Guard to maintain the log shipping, and kick off the Data Guard service on both instances. We will cover this in an upcoming blog.

Using DBaaS does get rid of the discussion of snap mirror backups. This is where you take the tablespace.dbf files, shutdown the database, copy these files to another database, and ingest the dbf file into the second database. Many users like to use this method for backup and recovery because it works for MySQL and Postgress. It is not a recommended backup and restore mechanism for an Oracle database. Synchronization issues can result if changes are being made and data is partitioned across multiple dbf files. Using tools like EMC or NetApp mirroring software work well for VMWare to clone data between data centers. This technology does not work to the cloud and data replication is more difficult if the source and target are from different hardware vendors. The Oracle database also has problems ingesting this data at times and fails to startup from mirror instances if file consistency is not maintained between the system.dbf, user.dbf, and other tablespace files.

Data compression is also a tricky issue. The Oracle database has a compression option that it uses to compress data to minimize disk usage in the tablespace files. If you take this compressed dbf file and then try to again compress it, it typically just consumes excess processor cycles and takes upto eight times as long to restore the data once it is doubly compressed. It is important to not only look at your backup window but your restore window. It might take an hour to backup and compress your data but it might take upto 8 hours to restore it. One of the key performance benefits that Oracle brings to the table is using ZFS storage as a back end for a database. Since the database knows what the ZFS storage appliance can and can't do, it can offload the compression processing and algorithms to the storage appliance. Unfortunately, this does not work for other storage vendors. The cloud database instance uses this to it's advantage so data is automatically compressed when copies are done to the object storage. The compression does not take processing power from the database engine but is offloaded to the storage engine.

You can customize the backup definition and parameters. All of the files are located in /var/opt/oracle/ocde/assistants/bkup/ and the bkup.cfg file defines how backup operates. It is important to note that there is a different command line set of tools if your database is a RAC configuration since you have to reconfigure files on both systems that comprise your cluster but they both use this directory and file to configure the backup process, timing, and procedures.

In summary, database backup is part of the database as a service on all levels. The foundation tool that is used is RMAN. There are command line api tools that allow you to backup and restore without having to learn RMAN. If you want to reconfigure and drill down into the depths and bowels of RMAN you can do this. The backups are automatically encrypted using your wallet that we talked about a couple of days ago. You can also use Enterprise Manager to run RMAN for your cloud service just like you do with your on premise database instances. The important thing to remember is that you don't need to learn RMAN unless you want to. If you deploy a database on infrastructure as a service you have to learn RMAN because it is not automated for you or configured. If you use Amazon RDS, backups are done by doing a disk clone to another zone. You can not setup Data Guard replication to the RDS instance because you don't have file system access to copy the change logs from your on premise database to the RDS storage. You can do this with EC2 and S3 but you loose the automated configurations and services that you get with database as a service. You will also need to purchase Advanced Security to do secure backups to IaaS instances from all cloud vendors but not with DBaaS or PaaS from Oracle.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.