Oracle 11g Logo

ORA-01105: mount is incompatible with mounts by other instances – ORA-19808: recovery destination parameter mismatch

After reboot of both the RAC nodes, we encountered “ORA-01105: mount is incompatible with mounts by other instances” & “ORA-19808: recovery destination parameter  mismatch” oracle error, after diagnosis i came to know about the size of the db recovery parameter, i.e. db_recovery_file_dest_size is mismatch over the nodes.

After setting identical size of the db recovery file destination, infected node up and running smooth as expected.

Error Logs:

 SQL> startup;
 ORACLE instance started.
 Total System Global Area 4275781632 bytes
 Fixed Size 2235208 bytes
 Variable Size 1694500024 bytes
 Database Buffers 2566914048 bytes
 Redo Buffers 12132352 bytes
 ORA-01105: mount is incompatible with mounts by other instances
 ORA-19808: recovery destination parameter mismatch

DB recovery parameter ‘db_recovery_file_dest_size’ verification on 2nd instance by issuing the following command:

 SQL> show parameter recovery
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 db_recovery_file_dest string +DATA
 db_recovery_file_dest_size big integer 150G
 recovery_parallelism integer 0

DB recovery parameter ‘db_recovery_file_dest_size’ verification on 1st instance by issuing the following command:

 SQL> show parameter recovery
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 db_recovery_file_dest string +DATA
 db_recovery_file_dest_size big integer 100G
 recovery_parallelism integer 0

According to above verification logs, highlighted area mismatched over the RAC nodes.

Workaround: Set ‘db_recovery_file_dest_size’ at 2nd instance to 100GB, as follows:

SQL> alter system set db_recovery_file_dest_size = 100G scope=spfile;
 System altered.

Bounce 2nd instance by normal shutdown and restart, as follows:

SQL> shutdown

 ORA-01507: database not mounted
 
 ORACLE instance shut down.
SQL> startup
 ORACLE instance started.
 Total System Global Area 4275781632 bytes
 Fixed Size 2235208 bytes
 Variable Size 1694500024 bytes
 Database Buffers 2566914048 bytes
 Redo Buffers 12132352 bytes
 Database mounted.
 Database opened.

By above workaround, Infected node up and running smooth.

 SQL> select inst_name from v$active_instances;
 INST_NAME
 ------------------------------------------------------------
 database1:>>Instance_I<<
 database2:>>Instance_II<<

Stay Tune 🙂

Oracle 11g Logo

ORA-27369: job of type EXECUTABLE failed with exit code: Input/output error & ORA-06512

After creating DBMS scheduler external job for Oracle RAC RMAN full database backup, I have encountered ORA-27369 & ORA-06512 oracle error while manual test run.

SQL> exec dbms_scheduler.run_job('RMAN_FULL_BACKUP');
BEGIN dbms_scheduler.run_job('RMAN_FULL_BACKUP'); END;
*
ERROR at line 1:
ORA-27369: job of type EXECUTABLE failed with exit code: Input/output error
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1

After diagnosing “ADDITIONAL_INFO” column from “ALL_SCHEDULER_JOB_RUN_DETAILS” dictionary view with the help of following query, Than i realize something went wrong with launching external job due to “login executable not setuid-root”.

SQL> select job_name, additional_info from ALL_SCHEDULER_JOB_RUN_DETAILS where job_name='RMAN_FULL_BACKUP';
...
...
JOB_NAME
-----------------------------------------------------------------
ADDITIONAL_INFO
----------------------------------------------------------------------------------------------------
EXTERNAL_LOG_ID="job_173557_54754",
USERNAME="oracle"
RMAN_FULL_BACKUP
EXTERNAL_LOG_ID="job_197309_55531",
ORA-27369: job of type EXECUTABLE failed with exit code: Input/output error
STANDARD_ERROR="Launching external job failed: Login executable not setuid-root"

Solution:

Run root.sh script file on Database home with the help of root user, as follows:

-bash-4.1# whoami
root
-bash-4.1# cd /u01/app/oracle/product/11.2.0/db_1
-bash-4.1# ./root.sh
Performing root user operation for Oracle 11g
The following environment variables are set as:
 ORACLE_OWNER= oracle
 ORACLE_HOME= /u01/app/oracle/product/11.2.0/db_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
-bash-4.1#

After performing above solution, my DBMS scheduler external job ran successful.

Stay tune. 🙂

Oracle 11g Logo

ORA-00245: control file backup failed; target is likely on a local file system- RMAN-08132: WARNING: cannot update recovery area reclaimable file list

ORA-00245: control file backup failed; target is likely on a local file system — RMAN-08132: WARNING: cannot update recovery area reclaimable file list.

On Oracle 11gR2 RAC, I have encountered ORA-00245 & RMAN-08132 error while deleting archive logs manually from RMAN prompt, After diagnosis i realize snapshot control file backup location not being set to shared location. i.e. Shared Disk group.

In RAC environment, Snapshot control file backup location should be on shared disk group so that it can be accessible to all RAC nodes.

Work around for the mentioned error:

Verify snapshot control file location by RMAN prompt as with the help of below command:

RMAN> show snapshot controlfile name;

Configure it to the shared disk group as below:

[oracle@db2 bin]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jul 29 01:30:42 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: PDB (DBID=518531946)

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+data/snapcf.f';

using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/snapcf_PROD021.f';
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+data/snapcf.f';
new RMAN configuration parameters are successfully stored

Note:
+data is my shared Disk group.

After setting Snapshot controlfile backup location to shared disk group, deletion of archivelog from RMAN prompt is smooth.

Stay tune. 🙂

Oracle 11g Logo

How to Backup Oracle RAC 11gR2 Database with RMAN Backup Utility with the help of DBMS_SCHEDULER – Part-IV – RMAN Delete Obsolete Backup

In my previous articles, I have covered step by step guide to configure:

Above articles contains: WHY to choose DBMS_SCHEDULER over traditional RMAN backups configuration with CRON jobs, FULL, INCREMENTAL & TRANSACTIONAL Oracle RAC database backed up with DBMS_SCHEDULER.

Apart from RMAN backup configuration, House keeping job also required in RMAN after configuring FULL, INCREMENTAL & TRANSACTIONAL log RMAN backups.

In this article, I will cover what is “Delete Obsolete” command in RMAN, how it works and how to configure it with DBMS_SCHEDULER.

What is “Delete Obsolete” command & How it works:
Delete Obsolete command help to delete those archive log who are no longer needed for any kind of RMAN recovery. We can delete archive logs files that are obsolete ( Not needed for recovery ) according to default retention policy. In case any archive log file has been marked as obsolete by RMAN, than it will be removed from backup media ( Disk/Tape ), also removed entry from recovery catalog database, and marked as deleted in the control file.

Instead of theory, now we would jump into configuration part:

Script for Delete Obsolete backup:
In my case: I am calling “Delete_Obsolete.rcv” RMAN script through “Delete_Obsolete.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 Delete_Obsolete.sh
#!/bin/bash
#script to delete obsolete 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/Delete_Obsolete.rcv
exit

Delete obsolete archive log backup script: ( .rcv )
[oracle@database1 Scripts]$ cat Delete_Obsolete.rcv
#Delete Obsolte Backups

run
{
delete noprompt force obsolete;
}

Create DBMS_SCHEDULER job for RMAN Delete obsolete archive log backup, Here in this procedure I am going to create “RMAN_DELETE_OBSOLETE_BACKUP” job with required attributes.

begin
dbms_scheduler.create_job(
job_name            => 'RMAN_DELETE_OBSOLETE_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 Delete obsolete archive log backup script ) for the same job:

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

Set start_date for the same job, In my case “RMAN_DELETE_OBSOLETE_BACKUP” job will execute every day @ every 1 hour, so job start date and its first run timing would  according to my convenience.

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

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

begin
dbms_scheduler.run_job('RMAN_DELETE_OBSOLETE_BACKUP');
end;
/
PL/SQL procedure successfully completed.

In case of any error while test run, you can ensure 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_DELETE_OBSOLETE_BACKUP” job will execute every day @ every 1 hour.

begin
dbms_scheduler.set_attribute(
name      => 'RMAN_DELETE_OBSOLETE_BACKUP',
attribute => 'repeat_interval',
value     => 'freq=hourly;interval=1');
dbms_scheduler.enable( 'RMAN_DELETE_OBSOLETE_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” data dictionary view.

SQL> select job_name,enabled,owner, state from dba_scheduler_jobs where job_name in ('RMAN_DELETE_OBSOLETE_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_DELETE_OBSOLETE_BACKUP');

Cheers!! Finally we have successfully configured Delete Obsolete backup of Oracle RAC 11gR2 database with RMAN Backup Utility with the help of DBMS_SCHEDULER.

In my previous articles, I have covered step by step guide to configure:

Stay tune. 🙂

Oracle 11g Logo

How to Backup Oracle RAC 11gR2 Database with RMAN Backup Utility with the help of DBMS_SCHEDULER – Part-III – RMAN Transactional log/Archive Database Backup

In my previous articles, I have covered:

Above articles contains: WHY to choose DBMS_SCHEDULER over traditional RMAN backups configuration with CRON jobs, FULL Oracle RAC database backup & INCREMENTAL Oracle RAC database backed with DBMS_SCHEDULER.

Now we directly jump into configuration part of Transactional log/Archive backup.

Script for the RMAN Transactional log/Archive database backup:
In my case: I am calling “Transactional_log.rcv” RMAN script through “Transactional_log.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 Transactional_log.sh
#!/bin/bash
#Set Envirnoment 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/Transactional_log.rcv
exit

 

RMAN Transactional log/Archive database backup script: ( .rcv )

[oracle@database1 Scripts]$ cat Transactional_log.rcv
#script to backup transaction logs
#Created 17 June 2014
#Author : Jignesh Jethwa
#Version 1.0
run
{
backup as compressed backupset archivelog all;
#Delete Obsolte Backups
delete noprompt force obsolete;
}

 

Create DBMS_SCHEDULER job for RMAN Transactional log/Archive backup, Here in this procedure I am going to create “RMAN_TRANS_BACKUP” job with required attributes.
begin
dbms_scheduler.create_job(
job_name => 'RMAN_TRANS_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 Transactional log/Archive backup script ) for the same job:
begin
dbms_scheduler.set_job_argument_value(
job_name => 'RMAN_TRANS_BACKUP',
argument_position => 1,
argument_value => '/home/oracle/RMAN/Scripts/Transactional_log.sh');
end;
/
PL/SQL procedure successfully completed.

Set start_date for the same job, In my case “RMAN_TRANS_BACKUP” job will execute every day @ every 15 minute, so job start date and its first run timing would according to my convenience.
begin
dbms_scheduler.set_attribute(
name => 'RMAN_TRANS_BACKUP',
attribute => 'start_date',
value => trunc(sysdate)+16/24);
end;
/
PL/SQL procedure successfully completed.

Test your backup job manually in SQL prompt by instantiating “RMAN_TRANS_BACKUP” job.
begin
dbms_scheduler.run_job('RMAN_TRANS_BACKUP');
end;
/
PL/SQL procedure successfully completed.

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_TRANS_BACKUP” job will execute every day @ every 15 minute.
begin
dbms_scheduler.set_attribute(
name => 'RMAN_TRANS_BACKUP',
attribute => 'repeat_interval',
value => 'freq=minutely;interval=15');
dbms_scheduler.enable( 'RMAN_TRANS_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_TRANS_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_TRANS_BACKUP');

Cheers!! Finally we have successfully configured Transactional log/Archive database backup of Oracle RAC 11gR2 database with RMAN Backup Utility with the help of DBMS_SCHEDULER.

In my previous articles, I have covered step by step guide to configure:

And Next article will be about:

Stay tune. 🙂

Oracle 11g Logo

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

In my previous article, I have covered step by step guide to configure full database backup of Oracle RAC 11gR2 database with RMAN Backup Utility with the help of DBMS_SCHEDULER.

Previously, We have discussed WHY to choose DBMS_SCHEDULER over traditional RMAN backups configuration with CRON jobs.

Lets consider following step by step guide:

Script for the RMAN Incremental database backup:
In my case: I am calling “daily_incrmental.rcv” RMAN script through “daily_incrmental.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 daily_incrmental.sh
#!/bin/bash
#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/daily_incrmental.rcv
exit

RMAN Incremental database backup script: ( .rcv )

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

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

begin
dbms_scheduler.create_job(
job_name => 'RMAN_INC_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 Incremental backup script ) for the same job:
begin
dbms_scheduler.set_job_argument_value(
job_name => 'RMAN_INC_BACKUP',
argument_position => 1,
argument_value => '/home/oracle/RMAN/Scripts/daily_incrmental.sh');
end;
/
PL/SQL procedure successfully completed.

 

Set start_date for the same job, In my case “RMAN_INC_BACKUP” job will execute daily instead of sunday @11pm, so job start date and its first run timing would according to my convenience.
begin
dbms_scheduler.set_attribute(
name => 'RMAN_INC_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_INC_BACKUP” job.

begin
dbms_scheduler.run_job('RMAN_INC_BACKUP');
end;
/
PL/SQL procedure successfully completed.

 

Verify running RMAN backup status by issuing following SQL query, It will shows 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 ensure 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_INC_BACKUP” job will execute daily instead of Sunday @11pm.

begin
dbms_scheduler.set_attribute(
name => 'RMAN_INC_BACKUP',
attribute => 'repeat_interval',
value => 'freq=daily;byday=mon,tue,wed,thu,fri,sat;byhour=23');
dbms_scheduler.enable( 'RMAN_INC_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_INC_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_INC_BACKUP');

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

In my earlier article, I have covered step by step guide to configure full database backup of Oracle RAC 11gR2 database with RMAN Backup Utility with the help of DBMS_SCHEDULER.

In my next articles, I will cover, How to configure Oracle RAC Transactional log/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 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 11g Logo

How to add disk to ASM diskgroup in Oracle 11g

Following step by step guide will help you to add disk to oracle ASM diskgroup.

// Following query will identify the Diskgroup to add disk:

SQL> select group_number, name from v$asm_diskgroup;
GROUP_NUMBER NAME
------------ ------------------------------
1 DATA

// Following query will identify the candidate disk:

SQL> SELECT MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,TOTAL_MB,FREE_MB,NAME,PATH,LABEL FROM V$ASM_DISK;

MOUNT_S HEADER_STATU MODE_ST STATE TOTAL_MB FREE_MB NAME PATH LABEL
------- ------------ ------- -------- ---------- ---------- ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------
CACHED MEMBER ONLINE NORMAL 10240 6528 DATA_0002 /dev/oracleasm/disks/OCR
CACHED MEMBER ONLINE NORMAL 307200 196112 DATA_0000 /dev/oracleasm/disks/ARCHIVE
CACHED MEMBER ONLINE NORMAL 307200 196141 DATA_0001 /dev/oracleasm/disks/DATA

// With the help of createdisk oracle utility, we will create oracle ASM disk. ( as root user ) Following command will create ASM disk:

[root@database1 ~]# /usr/sbin/oracleasm createdisk RMAN /dev/mapper/mpathg
Writing disk header: done
Instantiating disk: done

Note:
RMAN is the name of newly created ASM disk with path ‘/dev/mapper/mpathg’.

// Scan all oracle ASM disk after addition:

[root@database1 ~]# /usr/sbin/oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...

// Ensure all oracle ASM disk after scanning as follow:

[root@database1 ~]# /usr/sbin/oracleasm listdisks
ARCHIVE
DATA
OCR
 RMAN

OR

[oracle@database1 ~]$ cd /dev/oracleasm/disks/
[oracle@database1 disks]$ ls
ARCHIVE DATA OCR RMAN

// Now Add the newly created oracle ASM disk to existing ASM diskgroup with the help of following query:

[oracle@database1 disks]$ grid_env
[oracle@database1 disks]$ sqlplus "/ as sysasm"

SQL> ALTER DISKGROUP DATA ADD DISK '/dev/oracleasm/disks/RMAN' NAME DATA_0003 REBALANCE POWER 11;

Diskgroup altered.

// Ensure changes by issue following query:

SQL> SELECT MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,TOTAL_MB,FREE_MB,NAME,PATH,LABEL FROM V$ASM_DISK;
MOUNT_S HEADER_STATU MODE_ST STATE TOTAL_MB FREE_MB NAME PATH LABEL
------- ------------ ------- -------- ---------- ---------- ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------
CACHED MEMBER ONLINE NORMAL 307200 202544 DATA_0000 /dev/oracleasm/disks/ARCHIVE
CACHED MEMBER ONLINE NORMAL 10240 6743 DATA_0002 /dev/oracleasm/disks/OCR
CACHED MEMBER ONLINE NORMAL 307200 202578 DATA_0001 /dev/oracleasm/disks/DATA
CACHED MEMBER ONLINE NORMAL 307200 292732 DATA_0003 /dev/oracleasm/disks/RMAN

By above method, we can add disk to oracle ASM diskgroup.

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

Thanking you.

Have a nice time. 🙂