4 4 3:Oracle Backup

From SEPsesam
Jump to: navigation, search
Draft.png WORK IN PROGRESS
This page is a draft. Treat the information on this page with caution as it may be incomplete.

Copyright © SEP AG 1999-2020. All rights reserved.

Any form of reproduction of the contents or parts of this manual is allowed only with the express written permission from SEP AG. When compiling and designing user documentation SEP AG uses great diligence and attempts to deliver accurate and correct information. However, SEP AG cannot issue a guarantee for the contents of this manual.

Docs latest icon.png Welcome to the latest SEP sesam documentation version 4.4.3/4.4.3 Beefalo V2. For previous documentation version(s), check Oracle archive.


Overview

The procedures and features explained herein provide only information about the SEP sesam-specific parts of Oracle backup. For detailed information on Oracle-specific backup and recovery, see Oracle database documentation.

The SEP sesam backup module for Oracle integrates Oracle Recovery Manager (RMAN) with SEP sesam to efficiently back up and restore Oracle databases online. It supports Oracle single-instance configurations and multi-instance configurations – Real Application Clusters (RAC), Oracle Server configurations that use Automatic Storage Management (ASM) as well as Oracle Data Guard environment. ASM may also be used as a backup destination for RMAN backup to DISK. When using RMAN in a Data Guard environment, a recovery catalog is required to back up a physical standby database and store information about Oracle databases.

Oracle provides a Media Management Layer (MML) for saving Oracle databases to SEP sesam Server. The MML API gives the Recovery Manager (RMAN) access to the backup application. For Oracle, SEP sesam represents a media management utility. It handles media and devices and loads, unloads and labels media.

RMAN manages the target database (the database containing the control files, data files and optional archived redo logs that are being backed up or restored) and all Oracle server processes on the target database. In case of using ASM, keep in mind that this is a filesystem and volume manager that also need to be backed up regularly.

Step-by-step overview

  1. Install SEP sesam Client on the target Oracle server
  2. Configure the Oracle extension on the target Oracle server
  3. Add the Oracle client to SEP sesam environment
  4. Set remote command permissions
  5. Configure Oracle backup in the SEP sesam environment
  6. Create an Oracle backup task, a schedule, and a linked command event
  7. Run RMAN backup
  8. Validate backups using RMAN crosscheck command
  9. Test the Oracle extension using sbttest

Configuring Oracle backup in the SEP sesam environment

For Oracle backups, SEP sesam Server acts as a media manager. It handles media and provides scheduling, media management and monitoring for Oracle backups and restores. Oracle RMAN controls the Oracle server processes and is responsible for maintaining the information in the recovery catalog and control file for performing Oracle backups and restores.

For general information on SEP sesam backup configuration and prerequisites, see Standard Backup Procedure. This section deals only with Oracle-specific information.

Create an Oracle backup task, a schedule, and a linked command event

To configure a backup of Oracle database, first create a task for the Oracle client.

  1. From Main Selection -> Tasks -> By Clients, select the Oracle client then click New Backup Task. The New Backup Task window opens.
  2. Enter the Task name.
  3. From the Task type drop-down list, select Oracle.
  4. Under Source, enter the name of the Oracle database and click OK to create the task.
  5. Bck task-Oracle Beefalo V2.jpg

  6. You must first create a schedule to be able to start the command sbc_oracle_rman on the Oracle server remotely.
  7. From Main Selection -> Scheduling -> Schedules, click New Schedule. The New Schedule window opens. Configure your schedule and click OK. For details, see Creating a Schedule.
  8. In the Schedules window, right-click the newly-created schedule and select New Command Event. The New Command Event window opens.
  9. Create a new command event as follows:
    • Priority: Optionally, define a priority for the command event. SEPuler always executes schedules with the highest priority first. The default priority level is 1, which is the lowest priority. The highest priority level is 99. The only exceptions are schedules with priority 0, which override all other priorities and are always executed. For details, see Setting Event Priorities.
    • Blocking date: Optionally, enable the blocking date. This option should be used together with high priority for special events. If checked, it will block events of the same type of a lower priority, ensuring that the command event is processed when other command events are also scheduled at the same time. For details, see Blocking Events.
    • Name: Enter the name of the command event or search for and select an already existing command event. Searching for command by clicking the Choose (previously Search) button opens a new window, where you can add a new command event, copy an already existing command event, select which existing command event you want to use again by clicking the Select (previously Choose) button, and change or delete an existing command event. Note that if you have selected an already existing command, all other values are set automatically (Name, Client, Command, etc.), however you can modify them. Otherwise, you have to specify these values manually.
    • Client: From the drop-down list, select the host name of the Oracle server.
    • User: Enter a valid user name of a user responsible for processing the command with appropriate rights on the Oracle server.
    • Retention time (available in v. ≥ 4.4.3 Beefalo): Specify for how long (in days) the command event results and logs will be kept.
    • Command: Enter the full command that will be executed on the Oracle server. For command arguments, see section sbc_oracle_rman command.
    • Add to command (available in v. ≥ 4.4.3 Beefalo): Optionally, add additional parameters to command.
  10. Click OK to create a new command event.
  11. Command event-Oracle Beefalo V2.jpg

sbc_oracle_rman command

SEP SBC Oracle client module is a script that can be called from SEP sesam as a command event. This wrapper script creates an RMAN script with the given arguments and executes RMAN with the generated script. The usage of a script is platform dependent - the regular Windows shell scripting requires to specify the given parameters in the precise order when calling the script.

sbc_oracle_rman arguments

  -b: Backup operation. By default, this is a full, online, single channel backup.
  -r: Restore operation. By default, this is a full online, single channel restore.
      NOTE:
      Before restoring an Oracle database, it must be set to the required state,
      e.g., "SHUTDOWN; STARTUP MOUNT;"
      After restore an "ALTER DATABASE OPEN RESETLOGS;" might be necessary.

  -l <copy|full|diff|incr|arch>: Backup level COPY, FULL, DIFF, INCR and ARCHIVELOG.
     Backup level may be combined with ARCHIVELOG backup,
     e.g., -l copy_arch

  -o {option}: The following options are supported:

     delete                    Delete archive logs after backup.
     delete_later              Delete archive logs when successfully backed up 3 times.
     offline                   WARNING: This option will automatically SHUT DOWN
                                        the database before the backup and START it UP after the backup!

     controlfile               Restore control file with RMAN catalog.
     controlfile_DBID={DBID}   Restore control file without RMAN catalog. DBID is required.
     recover                   Recover after restore.
     until={YYYYMMDD-HH:MM:SS} Point-in-time recovery with specified time.

  -T <target database connect string>:  default is ORACLE_SID
  -R <recovery catalog connect string>: default is NoCatalog
  -C <SEP_sesam_Server>: SEP sesam Server host name
  -S <tape_server>: tape server host name
  -j <SEP_sesam_job>: the name of the SEP sesam job, under which the backup is running
  -v [0|1]: debug on (set -x)
  -m <media_pool_name>: the name of the SEP sesam media pool, used for backup
  -p <number> the number of backup channels
     <tabel_space> a single table space to be backed up or restored
                  If it is not specified, a backup/restore of the whole database is performed.

Usage on Linux/Unix

 oracle@orax10:~/product/10.2/db_1/sob> ./sbc_oracle_rman.sh
 Usage: ./sbc_oracle_rman.sh -b|r <-l level> -T <target_DBS> -R <Catalog> -S <SEP_sesam_Server> \
                             -j <SEP_sesam_job> -m <media_pool_name> -o <option> -p <number of streams> <table_space>

For the backup and restore examples on Linux/Unix, see the below section Examples.

Usage on Windows

The only difference between the Windows and SEP sesam Linux/Unix Oracle extensions is that the regular Windows shell scripting has no possibility to all parameter by ARGV tags. Consequently, the given parameters must be specified in a precise order when calling the script.

.\sbc_oracle_rman.cmd b full <user>/<password>@<sid> <user>/<password<@<RCAT> <SEP_sesam_Server> <SEP_sesam_job> <SEP_sesam_drive_number><media_pool_name> [<trace_level>] [<table_space>]

If a user is authorized via oper system login then <sid> is sufficient. If no Recovery Catalog RCAT was used then 'nocatalog' must be specified to use the target database controlfile.

.\sbc_oracle_rman.cmd b full <sid> nocatalog <SEP_sesam_server> <SEP_sesam_job> <SEP_sesam_drive_number> <media_pool_name> [<trace_level>] [<table_space>]

For the full backup and restore examples on Windows, see the below section Examples.

Embedding a plain-text password (optional)

To avoid entering a plain text password in the remote command, do one of the following:

  • Embed the password into the script sbc_oracle_rman.sh.
  • Instead of using SEP sesam command event, execute a RMAN backup script with modified environment variables (SESAM_SERVER, SESAM_JOB, etc.). Make sure that you are granted privileges to access the database. The backup command should be issued from the Oracle Enterprise Manager scheduler.

If the Oracle Recovery Catalog database is used, the calling parameters will not use the password of the Oracle database. In such an event, the sbc_oracle_rman.sh/.cmd will be called with the password of the RMAN user for the Recovery Catalog database.

Enabling ARCHIVELOG mode of the Oracle database

To perform an online backup, the Oracle database must operate in ARCHIVELOG mode. Oracle recommends that you shut down the database and perform an offline backup to protect against any problems before switching from NOARCHIVELOG mode. This will be the final backup of the database in NOARCHIVELOG mode and can be used if anything goes wrong during the change to ARCHIVELOG mode.

To change the database archiving mode, you must be connected to the database with administrator privileges (AS SYSDBA).

  1. Start sqlplus from the console with Oracle database admin credentials. #> sqlplus "/as sysdba"
  2. Check the archiving online backup mode. #> select name, log_mode from v$database; NAME LOG_MODE --------- ------------ MYDB ARCHIVELOG
  3. If the database log mode shows ARCHIVELOG, you can perform an Oracle RMAN online backup. If it shows NOARCHIVELOG mode, proceed as follows:

  4. Shutdown the database MYDB. #> shutdown immediate;
  5. Start the database and set it to Mount status. To change the archive mode, the database must be mounted but not open. #> startup mount;
  6. Switch to ARCHIVELOG mode. #> alter database archivelog;
  7. Bring the database online. #> alter database open;

Check the archiving mode again (see step 2). By enabling ARCHIVELOG mode, online redo log files are archived before they are reused. It is recommended that you specify the location for these archive logs to avoid storing them in random locations. Note that if your archive log destination runs out of space, the database will shut down. For details, see Oracle documentation Specifying Archive Destinations.

Running RMAN backup

This section explains only the SEP sesam-specific part of Oracle RMAN. For details about RMAN and setting up a correct backup strategy, see Oracle Database Documentation.

The following example script performs a full database backup:

oracle@oraclesrv:/product/oracle/bin> cat ora_full.rman
connect target
run{
allocate channel t1 type ’SBT’ parms ’ENV=(SESAM_SERVER=backup,SESAM_JOB=oracle_full,SESAM_POOL=DISK)’;
backup database
format ’O%d_%u.dat’;
filesperset 100;
release channel t1;
}

Explanation of the script:

  1. Log in to the target database (the database you want to back up).
  2. Open a backup channel. For parallel backups, additional channels can be allocated.
    SEP sesam specific parameters
    SESAM_SERVER (*) Name of the SEP sesam Server. Optionally, specify a port range (e.g., bcksrv:11001@11003-11007).
    SESAM_JOB (*) Task name for Oracle backup. You should create a task before setting up RMAN backup. See Creating a backup task type Oracle.
    SESAM_POOL (*) Media pool name.
    SESAM_DRIVE Drive number.
    SESAM_TAPE_SERVER Name of the SEP sesam tape server. Optionally, specify a port range (e.g., bcksrv:11001@11003-11007).
    SOB_TRACE Trace level (max. 3).
    SOB_LOGFILE Name of the log file. If no log file is specified, trace messages are written to sbtio.log.

    RMAN specific parameters

    SBT_LIBRARY=<path> Path to the SBT library file (libobk.so) on the system. Use on AIX systems, if the library cannot be found automatically. The library file libobk.so resides in <sesam_install>/bin/sesam.
    Parameter must NOT be part of the ENV= variable. Example: allocate channel t1 type ’SBT’ parms='SBT_LIBRARY=/opt/sesam/bin/sesam/libobk.so ENV=(...)'. See: Oracle Documentation
    • (*) = mandatory parameters
    • Format identifier. The format specifier must result in a unique ID. Both Oracle and SEP sesam use this ID to identify the correct saveset during restore. Oracle guarantees that the combination of%d%u is unique.
  3. Include 100 files in one saveset.
  4. Release the backup channel.

When this script is executed with rman cmdfile ora full.rman, the following appears:

oracle@oraclesrv:\~/product/oracle/bin> ./rman cmdfile ora_full.rman
Recovery Manager: Release 9.2.0.1.0 - ProductionCopyright (c) 1995
RMAN>
connected to target database: MYDB (DBID=2383580887)
RMAN>
connected to recovery catalog database
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14>
allocated channel: t1
channel t1: sid=16 devtype=SBT_TAPE
channel t1: MMS Version 1.7.0.1
Starting backup at 30-JUL-19
channel t1: starting full datafile backupset
channel t1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00001 name=/opt/oracle/oradata/mydb/system01.dbf
input datafile fno=00002 name=/opt/oracle/oradata/mydb/undotbs01.dbf
input datafile fno=00005 name=/opt/oracle/oradata/mydb/example01.dbf
input datafile fno=00010 name=/opt/oracle/oradata/mydb/xdb01.dbf
input datafile fno=00006 name=/opt/oracle/oradata/mydb/indx01.dbf
input datafile fno=00009 name=/opt/oracle/oradata/mydb/users01.dbf
input datafile fno=00003 name=/opt/oracle/oradata/mydb/cwmlite01.dbf
input datafile fno=00004 name=/opt/oracle/oradata/mydb/drsys01.dbf
input datafile fno=00007 name=/opt/oracle/oradata/mydb/odm01.dbf
input datafile fno=00008 name=/opt/oracle/oradata/mydb/tools01.dbf
channel t1: starting piece 1 at 30-JUL-19
channel t1: finished piece 1 at 30-JUL-19
piece handle=b_7_1_MYDB.dat comment=API Version 1.1,MMS Version 1.7.0.1
channel t1: backup set complete, elapsed time: 00:07:47
Finished backup at 30-JUL-19
RMAN>
Recovery Manager complete.

Backing up CDBs and PDBs

SEP sesam supports Oracle multitenant architecture that enables you to manage multiple databases within a single instance. With multitenant architecture the Oracle databases (v. ≥ 12c) function as a multitenant container databases (CDBs) which include zero, one or more customer-created pluggable databases (PDBs). The PDB is a portable collection of schemas, schema objects, and non-schema objects that appears to an Oracle Net client as a traditional Oracle database (non-CDB). For more information on multitenant architecture and setup of CDBs and PDBs, see About the Multitenant Architecture.

By using the multitenant architecture, you can back up and restore a complete multitenant container database (CDB), the root, or one or more pluggable databases. The RMAN commands used for backing up and recovering CDBs and PDBs are very similar as for traditional Oracle databases (non-CDBs), with minor differences in the syntax. Note that you can perform the same backup and restore operations on non-CDBs as on CDBs and PDBs. For details on how to back up and restore CDBs and PDBs, see Oracle documentation.

PDB backup and restore examples:

  • backing up a single PDB
  • run{
    allocate channel t1 type ’SBT’ parms ’ENV=(SESAM_SERVER=backup,SESAM_JOB=oracle_full,SESAM_POOL=DISK)’;
    BACKUP PLUGGABLE DATABASE pdbname;
    release channel t1;
    }
    
  • backing up multiple PDBs
  • run{
    allocate channel t1 type ’SBT’ parms ’ENV=(SESAM_SERVER=backup,SESAM_JOB=oracle_full,SESAM_POOL=DISK)’;
    BACKUP PLUGGABLE DATABASE pdbname1, pdbname2;
    release channel t1;
    }
    
  • validating PDB for the restore
  • run{
    allocate channel t1 type ’SBT’ parms ’ENV=(SESAM_SERVER=backup,SESAM_JOB=oracle_full,SESAM_POOL=DISK)’;
    RESTORE PLUGGABLE DATABASE pdbname VALIDATE;
    release channel t1;
    }
    
  • restoring PDB
  • run{
    allocate channel t1 type ’SBT’ parms ’ENV=(SESAM_SERVER=backup,SESAM_JOB=oracle_full,SESAM_POOL=DISK)’;
    ALTER PLUGGABLE DATABASE pdbname CLOSE;
    RESTORE PLUGGABLE DATABASE pdbname;
    RECOVER PLUGGABLE DATABASE pdbname;
    ALTER PLUGGABLE DATABASE pdbname OPEN;
    release channel t1;
    }
    

Validating backups using RMAN crosscheck command

With the RMAN crosscheck command, you can crosscheck all Oracle database backups and copies and thus ensure that the files are in place and ready for restore. The crosscheck command synchronizes and checks the validity of the Oracle backups in SEP sesam according to the recovery catalog.

Objects that do not exist on disk or tape, are marked as expired in the RMAN repository. This affects the whole saveset: If an object that is a part of a backup is not found, the whole backup is marked as expired. You can delete all expired backups from RMAN with the command delete expired, as shown in the following example.

If you have not configured automatic channels for media manager, you must run allocate channel for maintenance before crosscheck. Then run crosscheck backup device type disk 'SBT'.

Example

allocate channel for maintenance type 'SBT' parms
'ENV=(SESAM_SERVER=sesam,SESAM_JOB=job,SESAM_POOL=pool)';
crosscheck backup;
delete expired;

Enabling disaster recovery with autobackup

Information sign.png Note
RMAN can be configured to automatically back up the control file and server parameter file, enabling smoother disaster recovery of a complete Oracle database. It is recommended that this option is always enabled.

If no recovery database catalog is configured and the database keeps track of backups using a control file, the Oracle administrator must enable the CONTROL FILE AUTOBACKUP option.

  1. Using RMAN, check that the CONTROL FILE AUTOBACKUP option is enabled.
  2. RMAN> show controlfile autobackup;
    RMAN configuration parameters for database with db_unique_name XE are:
    CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
    
  3. If the autobackup option is off, enable it.
  4. RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
    

The autobackup option enables RMAN to recover the database even if the current control file, catalog and server parameter file are lost. During Oracle backup, a special saveset for the control file is created on the SEP sesam Server. The control file provides database consistency information used for recovery and can be used to restore database information on a freshly installed or different system.

Testing the Oracle extension using sbttest

You can check and diagnose the media management API with the help of the sbttest Oracle diagnostic tool. This utility performs a simple test of the media management software to see if it is working properly. Functionality testing of libobk.so is carried out without interfering with running databases.

  1. Set the following environment variables:
    • SESAM SERVER=<name of SEP sesam Server>
    • SESAM JOB=<job name of Oracle backup> Name of an already configured task on SEP sesam Server with task type Oracle
    • SESAM POOL=<media pool name> Name of an already configured media pool on SEP sesam Server
  2. Execute sbttest. The output should be similar to the one below.
oracle@oraclesrv:~/product/10g/lib> export SESAM_SERVER=backsrv
oracle@oraclesrv:~/product/10g/lib> export SESAM_JOB=oracle_test
oracle@oraclesrv:~/product/10g/lib> export SESAM_POOL=DISK
oracle@oraclesrv:~/product/10g/lib> sbttest test1 -trace sbttest.log
The sbt function pointers are loaded from libobk.so library.
-- sbtinit succeeded
-- sbtinit (2nd time) succeeded
Note: This SBT library does not handle version 2.0 of SBT.
sbtinit: Media manager is version 2.3.1.1
sbtopen for output successful
sbtwrite successful, wrote 100 buffers
sbtclose successful after sbtwrite
sbtinfo successful
file t1 is on volume 5:3
sbtopen for input successful
file was created by this program; seed=27600, bufsize=16384, bufcount=100
sbtread successful, read 100 buffers
sbtclose successful after sbtread
sbtremove successful
*** The SBT API test was successful ***

Note that sbttest may be successful even though the server and the media management layer are not properly configured. In such case, the actual backup will fail. If the test fails, you can check SEP sesam-specific trace messages in sbttest.log.

Examples

Unix/Linux

Example 1 
Level 0 backup of the SID orcl to the SEP sesam Server smsrv to the already configured task orax10_DB-orcl to media pool VTD1 with recovery catalog RCVCAT.
 /opt/oracle/product/10.2/db_1/sob/sbc_oracle_rman.sh -b -l copy -T orcl -R sys/password@RCVCAT -C smsrv -j orax10_DB-orcl -m VTD1
Example 2 
Archive log backup of SID prod to SEP sesam Server backupsrv to the already configured task dbsrv_ora-prod to mediapool DAY, using SEP sesam drive 5 and not using an Oracle recovery catalog.
 /opt/oracle/product/10.2/db_1/sob/sbc_oracle_rman.sh -b -l arch -T prod -C backupsrv -j dbsrv_ora-prod -m DAY -d 5
Example 3 
Full restore of the database prod with automatic recovery from SEP sesam Server smsrv and sesam task orax10_DB-prod from media pool DISK.
  1. Shut down the database with Oracle management tools.
  2.  SQL> shutdown immediate
    
  3. Mount the database.
  4.  SQL> startup mount
    
  5. Restore the database.
  6.  # /opt/oracle/product/10.2/db_1/sob/sbc_oracle_rman.sh -r -T prod -o recover -C smsrv -j orax10_DB-prod -m DISK
    
  7. Open the newly restored database.
  8.  SQL> alter database open;
    
Example 4 
Point-in-time-recovery to the 2nd of February 2017 of database fanta with automatic recovery. Connecting to the already configured SEP sesam task dbserver_ora-fanta in the SEP sesam GUI and using the recovery catalog RCAT.
  1. Shut down the database with Oracle management tools.
  2.  SQL> shutdown immediate
    
  3. Mount the database.
  4.  SQL> startup mount
    
  5. Restore the database.
  6.  # /opt/oracle/product/10.2/db_1/sob/sbc_oracle_rman.sh -r -T fanta -R sys/password@RCAT -o recover,until=20170202-16:31:00 -C sesamsrv -j dbserver_ora-fanta -m Month
    
  7. Open the newly restored database.
  8.  SQL> alter database open resetlogs;
    

Windows

Example 1 
Full backup with recovery catalog to SEP sesam Server backupsrv with SEP sesam backup task type Oracle oracle_db_orcl to media pool ltopool to SEP sesam drive 2.
.\sbc_oracle_rman.cmd b full oracle/password@orcl oracle/password@rcvcat backupsrv oracle_db_orcl 2 ltopool 2
Example 2 
Full backup as above without recovery catalog.
.\sbc_oracle_rman.cmd b full oracle/password@orcl nocatalog backupsrv oracle_db_orcl 2 ltopool 2
Example 3
Full restore to DB 'reco' without recovery catalog.
.\sbc_oracle_rman.cmd r full 'reco' nocatalog backupsrv oracle_db_orcl 2 ltopool