Showing posts with label RMAN. Show all posts
Showing posts with label RMAN. Show all posts

Q&As: Fast Recovery Area (I)

(Q) What is a Fast Recovery Area? What does it contain?

  • Is an Oracle managed space that can be used to hold RMAN disk backups, control file autobackups, and archived redo logs
  • Files here are maintained by Oracle and have an OMF format
  • Disk location in which the database can store and manage files related to backup and recovery. It contains:
  • (a) multiplexed copies of current control file and online redo logs, (b) archived redo logs (c) Foreign archived redo logs (d) flashback logs (e) image copies of datafiles and control files (f) backup pieces

(Q) What does Oracle recommends about the location of the Fast Recovery Area?

  • Should be on a separate disk from the database area (database files, control file, redo logs)

(Q) How can you find out the size of the Fast Recovery Area?

SQL> show parameter db_recovery

---------------------------- ----------- ------------------------------
db_recovery_file_dest      string  /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size   big integer 4977M

(Q) What is the default size of an online log created in the Fast Recovery Area?

  • 100 Mb

(Q) Which statements about Fast Recovery Area are correct?

  • ( ) Fast Recovery Area automates management of backup-related files.
  • ( ) It minimizes the need to manually manage disk space for backup-related files
  • ( ) It balances the use of space among the different types of files
  • ( ) When you create a Fast recovery area:
  • (a) you choose a location on disk and set an upper bound for storage space. (b) you set a backup retention policy
  • ( ) RMAN retains the data file backups required to satisfy the current retention policy AND any arquived redo logs required for complete recovery of those data file backups

Q&As: Backup with Recovery Manager

(Q) Which statements about using RMAN to perform backups are correct?

  • ( ) You CAN perform incremental backups with RMAN
  • ( ) RMAN detects corrupt blocks and logs in V$DATABASE_BLOCK_CORRUPTION
  • ( ) Automatically establishes name and locations of all files to be backed up
  • ( ) Backups are recorded in the CONTROL FILE, which is the main repository of RMAN metadata.
  • ( ) Optionally, you CAN store backup metadata in a recovery catalog, which should be a schema in a database different of the one backed up.
  • ( ) The primary storage for RMAN repository information for a database is always in the control file of the database. 
  • ( ) RMAN supports proxy copy, a feature that allows a media manager to manage completely the transfer of data between disk and backup media
  • ( ) RMAN backups init parameter file
  • ( ) RMAN DOES NOT backup password and networking files.
  • ( ) RMAN supports a platform-independent language for backups

Oracle Scheduler: Use credentials for a detached job that performs cold backups

Here are the steps to create a daily detached job that performs a cold backup of an oracle 11g database.
This example is a bit more detailed than the one provided in the administration guide.

(1) Create the shell script that invokes RMAN.
  • This script will be executed by the detached job.
  • The script should be executable by the user who has backup privileges in the OS. (usually oracle).
(2) create the RMAN script
(3) Write a PL/SQL block that performs the following steps:
3.1 create a credential object with username/password of the OS user who should have permission to run the shell script.
3.2 create a program object, which should use the credential just created to run the shell script.
3.3 alter the program object setting its attribute to detached.
3.4 create a job object

(1) Create the script that invokes RMAN
  • Create a shell script that calls an RMAN script to perform a cold backup.
  • The shell script is located in $ORACLE_BASE/scripts/
  • It must be executable by the user who installed Oracle Database (typically the user oracle).
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=orcl
$ORACLE_HOME/bin/rman TARGET / @$ORACLE_HOME/scripts/coldbackup.rman trace /u01/app/oracle/backup/coldbackup.out &
exit 0

(2)Create the RMAN Script
  • Create an RMAN script that performs the cold backup and then ends the job.
  • At the end, the script should call the Scheduler routine to inform the job completion status
  • The script is located in $ORACLE_BASE/scripts/coldbackup.rman.
run {
# Shut down database for backups and put into MOUNT mode
shutdown immediate
startup mount

# Perform full database backup
backup full format "/u01/app/oracle/backup/%d_FULL_%U" (database) ;

# Open database after backup
alter database open;

# Call notification routine to indicate job completed successfully
sql " BEGIN DBMS_SCHEDULER.END_DETACHED_JOB_RUN(''sys.backup_job'', 0, null); END; ";

(3) Create credentials, detached program and job with inline schedule
Submit the following PL/SQL block:
-- 3.1 create a credential for the job to use
-- 3.2 create the program object, specifying the shell script created earlier as the external executable. Specify it as detached program.
-- 3.3 create the job, specifying the credential object created earlier.
 dbms_scheduler.create_credential('credrman', 'oracle', '');

 dbms_scheduler.create_program (
   program_name   => 'sys.backup_program',
   program_type   => 'executable',
   program_action => '/u01/app/oracle/scripts/',
   enabled        => TRUE,
   detached       => TRUE);
 dbms_scheduler.create_job (
   job_name     => 'sys.backup_job',
   program_name => 'sys.backup_program',
   repeat_interval => 'FREQ=DAILY; BYHOUR=13; BYMINUTE=20',
   credential_name => 'credrman');

You can check the status of the created job with the query below:
SQL> select owner, job_name, job_style, program_name, schedule_type, 
       to_char(start_date, 'yyyy-mm-dd hh:mi:ss') start_date_, enabled, state, 
       run_count runs, failure_count failures, 
from dba_scheduler_jobs
where job_name = 'BACKUP_JOB';

------------ ----------- ---------------- ------------- -------------------- ------- --------- ---- --------
BACKUP_JOB   REGULAR     BACKUP_PROGRAM   CALENDAR       2011-12-09 01:12:49 TRUE    SCHEDULED 0    0       

Some troubelshooting:
  • If a credential object is not created and assigned to the job, it will fail.
  • The result will be an error entry in the alert file and a trace generated:
-//- alert.log  -//-
Fri Dec 09 14:25:51 2011
Errors in file /u01/app/oracle/diag/rdbms/orcl11r2/orcl11r2/trace/orcl11r2_j000_20165.trc:
ORA-12012: error on auto execute of job 12693
ORA-27369: job of type EXECUTABLE failed with exit code: 274670

  • If a credential object is not created, Oracle will try to run the external program using the credentials specified in the $ORACLE_HOME/rdbms/admin/externaljob.ora file.
  • The credentials specified in that file (in a unix/linux installation) are user:nobody, group:nobody.
  • The consequence is that the job will likely fail with the error 'ORA-27369: job of type EXECUTABLE failed with exit code: 274670.'
  • This somewhat criptical error message suggests (as discussed here) that the problem is due to OS permissions in the execution of the external program.

SQL> select status, error#, run_duration, credential_name
     from dba_scheduler_job_run_details 
     where job_name='BACKUP_JOB';
STATUS                         ERROR#                 RUN_DURATION CREDENTIAL_NAME
------------------------------ ---------------------- ------------ ------------------ 
FAILED                         274670                 0 0:0:2.0 
SUCCEEDED                      0                      0 0:7:26.0   CREDRMAN                                                                                                                                                                                           

SQL> select log_id, operation, status, credential_name 
     from dba_scheduler_job_log 
     where job_name='BACKUP_JOB';
LOG_ID                 OPERATION                      STATUS     CREDENTIAL_NAME                                                   
---------------------- ------------------------------ ---------- ------------------ 
192                    RUN                            FAILED
194                    RUN                            SUCCEEDED  CREDRMAN                                                          

Oracle Backup and Recovery solutions

Basic solutions available for a backup and recovery strategy:

Backup techniques: comparison

Feature Recovery Manager User-managed Data Pump exp
Closed database backup Yes. Needs instance Mounted Yes No
Open database backup Need use of BEGIN/END BACKUP Need use of BEGIN/END BACKUP Requires rollback or undo segments
Incremental backup Yes No No
Corrupt block detection Indentifies corrupt blocks and logs in V$DATABASE_BLOCK_CORRUPTION No Identifies corrupt blocks in the export log.
Automatic specification of files to include in a backup Establishes name and location of all files to be backed up Files must be located and copied manually n/a
Backup repository Backups recorded in the control file. You Can store backup metadata in a recovery catalog. DBA needs to keep records. n/a
Backup to a media manager Yes. RMAN also supports proxy copy. Backup to tape is manual or controlled by media manager. Not supported.
Backup init.ora file Yes Yes No
Backup password and networking files No Yes No
Platform-independent language for backups Yes No yes

Flashback features: Logical and Physical levels

Oracle Flashback features
requires flashback data archiving. OFF for all tables (default).
Turn it on for individual tables
Operate at the logical level, using undo data.
Oracle flashback query Specify target time. Run queries. Results displayed as they would have appeared at the target time.
Oracle flashback version query View all version of rows that existed in a table within a time range
Oracle flashback transaction query View changes made by a single transaction
Oracle flashback transaction Reverse a transaction. Wow.
Oracle flashback table Recover table or set of tables to a point in time.
Oracle flashback drop Reverse the effects of a drop table.
Flashback database (RMAN: FLASHBACK DATABASE)
operates at the physical level. Uses flashback logs.
Can be used against specific data files.
More efficient than database point-in-time recovery.
Faster because does not require datafile restor.
Uses flashback logs. Not enabled by default.
Need a fast recovery area congifured.
You can set guaranteed restore points (alias for an SCN).