Oracle 11g Logo

How to Backup Oracle RAC 11gR2 Database with RMAN Backup Utility with the help of DBMS_SCHEDULER – Part-I – RMAN Full Database Backup

In this article, I will cover configuration of RMAN Full Database backup in Oracle RAC 11gR2 environment.

RMAN backup in Oracle 11gR2 RAC is exactly same like RMAN backup in Oracle 11gR2 single node.
The only difference is: Typically, in case of Oracle single node database, we will schedule RMAN scripts with the help of CRON job and it will run according to our convenience, but in case of Oracle RAC if we schedule RMAN script and if unfortunately that RAC node goes down ( where we configured RMAN scripts ), then RMAN backup won’t run obviously.

So, Same strategy will not be work in Oracle RAC node. For RMAN consistent backups use dbms_scheduler & we need to place RMAN scripts in shared directory. ( Or in my case, I have created identical scripts on both cluster node’s )

Note:

  • In my case I am using FRA for backup as well as snapshot controlfile.
  • ACFS mounted directory will also do for the same configuration.

Before starting configuration you need to ready your RMAN full database backup script.
In my case: I am calling “Weekly_full.rcv” RMAN script through “Weekly_full.sh” shell executable file after setting Oracle environment variables.

Note: Same script has been copied to all possible Oracle Cluster Node with same name & path.

[oracle@database1 Scripts]$ cat Weekly_full.sh
#!/bin/bash
#Script to run weekly full backup.
#Created 17 June 2014
#Author : Jignesh Jethwa
#Version 1.0
#Set Environment Variable
export ORACLE_BASE=/u01/app/oracle;
export ORACLE_SID=kucb1
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1;
export PATH=$PATH:$ORACLE_HOME/bin
rman target / @/home/oracle/RMAN/Scripts/Weekly_full.rcv
exit

RMAN Full database script: ( .rcv )

[oracle@database1 Scripts]$ cat Weekly_full.rcv
#Script to run weekly full backup
#Created 17 June 2014
#Author : Jignesh Jethwa
#Version 1.0
run
{
#Database Full Backup
backup as compressed backupset full database plus archivelog;
delete noprompt archivelog until time 'sysdate - 1' backed up 1 times to device type disk;
#Delete Obsolte Backups
delete noprompt force obsolete;
}

Note:

Don’t forget to allow executable permissions to RMAN & Shell scripts ( i.e. .sh & .rcv files )

Lets start with the configuration part:

Logged in to RMAN prompt and issue the following commands in order to optimize RMAN backup & automatic controlfile backup after every backup.

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

DBMS_SCHEDULER:

Here we are using DBMS_SCHEDULER instead of DBMS_JOB, because DBMS_SCHEDULER is RAC aware.

Before jump into real DBMS_SCHEDULER configuration, we need to focus on an important thing, That:

Both RAC nodes local time zone must be identical with DBMS_SCHEDULER default time.

On all RAC node, Ensure local time zone and set it accordingly.

ln -s /usr/share/zoneinfo/Asia/Calcutta localtime

On SQL prompt as sysdba, configure default time zone for DBMS_SCHEDULER.

SQL> exec dbms_scheduler.set_scheduler_attribute ('DEFAULT_TIMEZONE', 'Asia/Calcutta');
PL/SQL procedure successfully completed.

Ensure default DBMS_SCHEDULER time zone value with following query:

SQL> select value from dba_scheduler_global_attribute where attribute_name = 'DEFAULT_TIMEZONE';
VALUE
--------------------------------------------------------------------------------
Asia/Calcutta

Now we need to create credential so that are assigned to DBMS_SCHEDULER jobs so that they can authenticate with a local/remote host operating system or a remote Oracle database.

SQL> exec dbms_scheduler.create_credential(credential_name => 'Oracle', username => 'Oracle', password => '********');
PL/SQL procedure successfully completed.

Now its time to create DBMS_SCHEDULER job for RMAN Full backup, Here in this procedure I am going to create “RMAN_FULL_BACKUP” job with required attributes.

begin
dbms_scheduler.create_job(
job_name            => 'RMAN_FULL_BACKUP',
job_type            => 'EXECUTABLE',
job_action          => '/bin/sh',
number_of_arguments => 1,
start_date          => SYSTIMESTAMP,
credential_name     => 'Oracle',
auto_drop           => FALSE,
enabled             => FALSE);
end;
/
PL/SQL procedure successfully completed.

Set argument_position & argument_value ( i.e. Path of the RMAN script ) for the same job:

begin
dbms_scheduler.set_job_argument_value(
job_name            => 'RMAN_FULL_BACKUP',
argument_position   =>  1,
argument_value      => '/home/oracle/RMAN/Scripts/Weekly_full.sh');
end;
/
PL/SQL procedure successfully completed.

Set start_date for the same job, In my case “RMAN_FULL_BACKUP” job will execute every week on sunday @11pm, so job start date and its first run timing would  according to my convenience.

begin
dbms_scheduler.set_attribute(
name      => 'RMAN_FULL_BACKUP',
attribute => 'start_date',
value     => trunc(sysdate)+23/24);
end;
/
PL/SQL procedure successfully completed.

Test your backup job manually in SQL prompt by instantiating “RMAN_FULL_BACKUP” job.

SQL> exec dbms_scheduler.run_job('RMAN_FULL_BACKUP');
PL/SQL procedure successfully completed.

Verify running RMAN backup status by issuing following SQL query, It will show you RMAN backup details with start time & end time.

select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

In case of any error while test run, you can make sure details of error by issuing the following query, OR You can also query to “dba_scheduler_job_run_details” dictionary view for more details.

select JOB_NAME,STATUS,STATE,ERROR#,CREDENTIAL_NAME from dba_scheduler_job_run_details where CREDENTIAL_NAME like 'RMAN%';

After successfully completion of test run, Enable & schedule it by following procedure by setting value to ‘repeat_interval’ parameter, In my case “RMAN_FULL_BACKUP” job will execute every week on Sunday @11pm.

begin
dbms_scheduler.set_attribute(
name      => 'RMAN_FULL_BACKUP',
attribute => 'repeat_interval',
value     => 'freq=daily;byday=sun;byhour=23');
dbms_scheduler.enable( 'RMAN_FULL_BACKUP' );
end;
/
PL/SQL procedure successfully completed.

Ensure dbms_scheduler job details by issuing the following query OR you can also query to “dba_scheduler_jobs”.

SQL> select job_name,enabled,owner, state from dba_scheduler_jobs where job_name in ('RMAN_FULL_BACKUP');

Keep your eye on behavior of dbms_scheduler job by issuing the following query:

SQL> select job_name,RUN_COUNT,LAST_START_DATE,NEXT_RUN_DATE from dba_scheduler_jobs where job_name in ('RMAN_FULL_BACKUP');

Important Note:
DBMS_SCHEDULER is smart enough to start backup on the node where the last backup was successfully executed.

Cheers!! We have successfully configured full database backup of Oracle RAC 11gR2 database with RMAN Backup Utility with the help of DBMS_SCHEDULER

In my next article I will cover, How to configure Oracle RAC Incremental database backup with RMAN with the help of DBMS_SCHEDULER.

Next couple of articles are about , How to configure Oracle RAC Transactional/Archive database backup with RMAN with the help of DBMS_SCHEDULER. And Delete Obsolete backup of Oracle RAC 11gR2 database with RMAN Backup Utility with the help of DBMS_SCHEDULER.

Stay tune. ūüôā

Oracle 12c Logo

ORA-19573: cannot obtain exclusive enqueue for datafile 3

While database restore, I got oracle error “ORA-19573: cannot obtain exclusive enqueue for datafile 3“. After diagnosis I came to know my database is in open mode (It was my mistake), and RMAN restore operation is trying to overwrite currently active version of datafile-3, so after database has been altered in mounted state, Restore database has been¬†successful.
Note:

  • Also close the duplicate session from database where we trying to restore database.

Lets consider following hands on, In this I have tried to simulate same scenario:

// Trying to restore database while database is in open mode, It will throw an error: RMAN-03002, ORA-19870 & ORA-19573.
RMAN> restore database;

Starting restore at 18-JUN-14
using channel ORA_DISK_1

skipping datafile 5; already restored to file /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_9pgdjoln_.dbf
skipping datafile 7; already restored to file /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_9pgdjolj_.dbf
skipping datafile 13; already restored to file /u01/app/oracle/oradata/ORCL/FA0A923A8CCD0B2FE043CE6A7C736473/datafile/o1_mf_system_9qxm7bg1_.dbf
skipping datafile 14; already restored to file /u01/app/oracle/oradata/ORCL/FA0A923A8CCD0B2FE043CE6A7C736473/datafile/o1_mf_sysaux_9qxm7bmq_.dbf
skipping datafile 15; already restored to file /u01/app/oracle/oradata/ORCL/FA0A923A8CCD0B2FE043CE6A7C736473/datafile/o1_mf_users_9qxm8yd0_.dbf
skipping datafile 16; already restored to file /u01/app/oracle/oradata/ORCL/FA0AA072E8D30DA9E043CE6A7C73E432/datafile/o1_mf_system_9qxmgro6_.dbf
skipping datafile 17; already restored to file /u01/app/oracle/oradata/ORCL/FA0AA072E8D30DA9E043CE6A7C73E432/datafile/o1_mf_sysaux_9qxmgro0_.dbf
skipping datafile 18; already restored to file /u01/app/oracle/oradata/ORCL/FA0AA072E8D30DA9E043CE6A7C73E432/datafile/o1_mf_users_9qxmjvts_.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCL/datafile/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_9pgd6f0w_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_9pgdh77f_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_9pgdh615_.dbf
channel ORA_DISK_1: restoring datafile 00019 to /u01/app/oracle/oradata/ORCL/datafile/undo2.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/jignesh/1mpb5857_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/18/2014 12:54:43
ORA-19870: error while restoring backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/jignesh/1mpb5857_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 3
// Shutdown database & startup in mount state, as follows:  ( Note : We can startup and shutdown database from RMAN prompt )
RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 3206836224 bytes
Fixed Size 2293496 bytes
Variable Size 1879048456 bytes
Database Buffers 1308622848 bytes
Redo Buffers 16871424 bytes

// Now issue the following to restore database.
RMAN> restore database;
RMAN restoration continue…

// Open database after restore complete:
RMAN> alter database open;

Statement processed

RMAN> exit

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ WRITE

 

Cheers!! Your database has been restored successfully and ready to use.

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 11g Logo

Point in time recovery using RMAN

If you want to recover your database to the exact date/time in the past, use RMAN point in time recovery.

RMAN database point-in-time recovery (DBPITR) restores the db from RMAN backups.

RMAN will be consider all ( required ) backups (full, incremental, transectional) to restore or roll forward to the desire time. Continue reading

Oracle 12c Logo

Database recovery on loss of all online redo log files with the help of RMAN

According to standard practice, we should consider multiplexing of online redo log files to avoid such a scenarios, Each log file group should have more than/at least 2 log file members & location of all group on different physical disk. ( In case of worst situation with disk 1 then database would be recovery with the help of disk 2 – Online redo log file )

Single current online redo log file is sufficient to restore the entire database & do an incomplete recovery. Continue reading

Oracle 11g Logo Oracle 11g Logo

Recover datafile & controlfile using RMAN data recovery advisor

In case of worst situation with datafile & controlfile, we can repair/restore it with the help of RMAN data recovery advisor.

Note:
We can use Data Recovery Advisor with the help of Oracle Enterprise Manager, Grid Control & RMAN command prompt.

Lets consider hands-on on on RMAN repair advisor with the help of following case studies: Continue reading

Oracle 11g Logo

Block Change Tracking

How to enable Block Change Tracking for RMAN incremental backup performance gain.

Prior to Oracle 10g, RMAN incremental backup has to scan whole datafile & read each and every single block to identified changed blocks, in short RMAN incremental backup is as equal as RMAN full backup. Continue reading

Oracle 11g Logo

How to reset RMAN saved configuration

To clear the saved configuration in RMAN, please consider following examples for better understanding:

Connect to RMAN prompt:

[oracle@oracle ~]$ rman target / catalog recoveryman/recoveryman@catalogdb

Recovery Manager: Release 11.2.0.1.0 – Production on Thu Jan 9 07:09:36 2014 Continue reading

Oracle 11g Logo

How to check RMAN backup status and timings

This script will be run in the target( registered ) database, not in the catalog ( Repository ) database.

Login as sysdba and issue the following script:

This script will report on all currently running RMAN backups like full, incremental & archivelog backups:

SQL> col STATUS format a9
SQL> col hrs format 999.99
SQL> select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
SQL> /
SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS
 ----------- ------------- --------- -------------- -------------- -------
 29          DB FULL       RUNNING   01/07/14 10:28 01/07/14 10:28 .00
SQL> /
SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS
 ----------- ------------- --------- -------------- -------------- -------
 29          DB FULL       RUNNING   01/07/14 10:28 01/07/14 10:28 .01
SQL> /
SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS
 ----------- ------------- --------- -------------- -------------- -------
 29          DB FULL       COMPLETED 01/07/14 10:28 01/07/14 10:29 .03

Above script will give you RMAN backup status along with start and stop timing.

OR

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, 
ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;

 SID       SERIAL#    CONTEXT    SOFAR      TOTALWORK  %COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
 18        29         1          9115569    19258880   47.33

Above script will give you SID, Total Work, Sofar & % of completion.

You can also check historical backup status with the help of following script:

set linesize 500 pagesize 2000
col Hours format 9999.99
col STATUS format a10
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_start_time,
to_char(END_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_end_time,
elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
SESSION_KEY INPUT_TYPE    STATUS     RMAN_BKUP_START_TIM RMAN_BKUP_END_TIME  HOURS
----------- ------------- ---------- ------------------- ------------------- --------
 137764     DB FULL       COMPLETED  06-06-2017 02:00:32 06-06-2017 04:12:13 2.19
 137770     ARCHIVELOG    COMPLETED  06-06-2017 04:00:29 06-06-2017 04:01:05 .01
 137778     ARCHIVELOG    COMPLETED  06-06-2017 06:00:27 06-06-2017 06:00:35 .00
 137782     ARCHIVELOG    COMPLETED  06-06-2017 08:00:32 06-06-2017 08:03:36 .05
 137786     ARCHIVELOG    COMPLETED  06-06-2017 10:00:30 06-06-2017 10:02:03 .03
 137790     ARCHIVELOG    COMPLETED  06-06-2017 12:00:30 06-06-2017 12:02:34 .03
 137794     ARCHIVELOG    COMPLETED  06-06-2017 14:00:30 06-06-2017 14:02:58 .04
 . .. ...

*****

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have an easy life ahead.

Oracle 11g Logo

How to configure Oracle RMAN backup for the first time

RMAN is a oracle utility to backup, restore & recovery of database.

The following Steps will be demonstrated the configuration of oracle RMAN backup (for first time configuration)

Lets assume the database is in NOARCHIVELOG mode, by default the database is in NOARCHIVELOG mode, we need to change it to ARCHIVELOG mode for RMAN backup configuration. Continue reading