Oracle 11g Logo

RMAN failed to connect target database with ORA-00020: maximum number of processes (150) exceeded

Today RMAN backup failed with following error, trailing error logs itself very self-explanatory.

Error logs:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 11/14/2017 18:00:12
RMAN-12001: could not open channel ORA_DISK_1
RMAN-10008: could not create channel context
RMAN-10003: unable to connect to target database
ORA-00020: maximum number of processes (150) exceeded

Solution:

Increase number of processes from sys user with sysdba privilege.

SQL> show parameter processes;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
....
processes                            integer     150
SQL> alter system set processes=300 scope=spfile;
System altered.

Bounce the database so changes will be reflected in database instance and then you can initiate RMAN backup.

Thanks, Stay Tune. 🙂

Oracle 11g Logo

How to kill Oracle RMAN backup job

We can kill RMAN backup job with the help of 2 methods, as below:

Method I:
Alter system kill session:

First, get the SID and SERIAL# from below query:

SQL> select b.sid, b.serial#, a.spid, b.client_info
from v$process a, v$session b
where a.addr=b.paddr and client_info like 'rman%';
SID        SERIAL#    SPID         CLIENT_INFO
---------- ---------- ------------ ---------------------------------
592        12         865          rman channel=full_chanel

OR

SQL> 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
---------- ---------- ---------- ---------- ---------- ----------
592        12         1          9115569    19258880   47.33

Use the following command to kill RMAN backup job:

SQL> alter system kill session '592,12' immediate;
system altered.

Backup job killed successfully, simultaneously you will get below mentioned error log in RMAN backup logs:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 07/01/2017 00:09:15
RMAN-10038: database session for channel full_chanel terminated unexpectedly

Method II:

Directly kill RMAN job from OS level with the help of “kill -9”

[oracle@PR ~]$ ps -ef | grep rman|grep -v grep
oracle 2348 3124 3 01:28 pts/1 00:00:00 rman target /
[oracle@PR ~]$ kill -9 2348

Thanks, Stay Tune. 🙂

Oracle 11g Logo

ORA-19587: error occurred reading 512 bytes at block number 1 + ORA-27072: File I/O error

My one of the client facing below mentioned Oracle errors, On diagnosis, we come to know there are some deleted archive logs entry still exists in RMAN repository, after cleaning all the bad entries from RMAN repository(controlfile in my case), the backup was successfully completed.

Error logs:

archived log file name=/u02/oradata/flash_recovery_area/DB1/archivelog/2017_01_21/o1_mf_1_253761_d866q0o3_.arc RECID=252905 STAMP=933861305
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of crosscheck command on ORA_DISK_1 channel at 05/23/2017 12:59:27
ORA-19587: error occurred reading 512 bytes at block number 1
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 1

Solutions:

RMAN> delete force noprompt obsolete;
RMAN> delete force noprompt expired backup;
RMAN> DELETE NOPROMPT ARCHIVELOG UNTIL time 'SYSDATE-1';

In my case, the client needed only one-day archives. change last RMAN command as per your convenience.

Oracle 11g Logo

RMAN-06183: datafile or datafile copy xyz.dbf larger than MAXSETSIZE

My one of the client database backup have been failed since one week due to “RMAN-06183 datafile or datafile copy /vcboradata/oradata/datafile/system01.dbf (file number 1) larger than MAXSETSIZE”. Error itself is pretty explanatory. The size of the system01.dbf datafile had exceeded the RMAN parameter MAXSETSIZE, i.e. 15G

Error log:
RMAN>
Starting backup at 2016-11-03 10:41:39
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1008 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 2016-11-03 10:41:41
channel ORA_DISK_1: finished piece 1 at 2016-11-03 10:41:43
piece handle=/cbsrmanbackup/rman/VCBCBS/backupset/2016_11_03/o1_mf_ncnnf_TAG20161103T104140_d1okrg13_.bkp tag=TAG20161103T104140 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 2016-11-03 10:41:43
Starting Control File and SPFILE Autobackup at 2016-11-03 10:41:43
piece handle=/cbsrmanbackup/rman/VCBCBS/autobackup/2016_11_03/o1_mf_s_926937704_d1okrjqq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2016-11-03 10:41:45
Starting backup at 2016-11-03 10:41:49
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 11/03/2016 10:41:49
RMAN-06183: datafile or datafile copy /vcboradata/oradata/datafile/system01.dbf (file number 1) larger than MAXSETSIZE
Recovery Manager complete.

RMAN parameters:

RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name VCBCBS are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 31 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO 15 G;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/cbsrmanbackup/rman/snapcf_vcbcbs.f';

Confirm current size of the system01.dbf, as below, used size is 15336.1 which is greater then 15G.

RMANTBS /vcboradata/oradata/datafile/rmantbs.dbf 1000 70.9 929.06
SYSAUX /vcboradata/oradata/datafile/sysaux01.dbf 1400 880.3 519.75
SYSTEM /vcboradata/oradata/datafile/system01.dbf 15400 15336.1 63.94
UNDOTBS1 /vcboradata/oradata/datafile/undotbs01.dbf 4725 97.3 4627.75
USERS /vcboradata/oradata/datafile/users01.dbf 50 15.9 34.13

Workaround:
Increase the size of MAXPIECESIZE RMAN parameter from 15G to 33G. (You can increase it as per your convenience)
More on MAXPIECESIZE:
This parameter is use to set limits on the size of backup pieces. You can also use the MAXSETSIZE parameter on the BACKUP and CONFIGURE commands to set a limit for the size of backup sets.

RMAN> CONFIGURE MAXSETSIZE TO 33G;
old RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 15 G;
new RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 33 G;
new RMAN configuration parameters are successfully stored

After above changes, backup successfully completed.

Have a nice time ahead.

Stay Tune. 🙂

Oracle 11g Logo

RMAN-06900 RMAN-06901 ORA-19921

Received RMAN-06900,RMAN-06901 and ORA-19921 while logged in to RMAN prompt to verify daily backups. (OS:Redhat linux 6.5)

WARNING message:

[oracle@primary logs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 26 10:34:39 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: BLADE1 (DBID=3381279798)
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 128 rows exceeded
RMAN>

On further diagnosing and suggested by OERR utility, I have realized there is one old active session from Aug 23, as mentioned below.

[oracle@primary ~]$ oerr ORA 19921
19921, 00000, "maximum number of %s rows exceeded"
// *Cause: The maximum number of rows in the V$RMAN_STATUS or V$RMAN_OUTPUT
// table has been exceeded.
// *Action: Close some of existing and unused RMAN connections and sessions.

So lets find out old session with ps -ef:

[oracle@primary logs]$ ps -ef|grep rman
oracle 543 32693 0 10:50 pts/4 00:00:00 grep rman
oracle 19783 19782 47 Aug23 ? 1-07:13:42 rman target /

There is one old active session above, OS ID: 19783, since 23 aug.

[oracle@primary ~]$ date
Fri Aug 26 10:57:13 IST 2016
[oracle@primary logs]$ kill -9 19783

After successfully killed old session, we are no more facing mentioned list of warnings, as below:

[oracle@primary logs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 26 10:50:54 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: BLADE1 (DBID=3381279798)
RMAN>

Thanks,

Stay Tune. 🙂

Oracle 11g Logo

RMAN-06026: some targets not found – aborting restore, RMAN-06023: no backup or copy of datafile X found to restore

RMAN restore would failed with RMAN-06026 and RMAN-06023 after successfully restoration of controlfile. Logs are as follows:

Error log:

RMAN> restore database;
Starting restore at 15-FEB-16
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/15/2016 14:52:59
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

As per oracle error RMAN-06023 means RMAN cannot find a backup for that datafile in its repository, so I cross verified RMAN backup files physically on disk as well as into RMAN repository with following RMAN commands. The backups are marked as AVAILABLE. Everything was pretty perfect.

RMAN> list backup of database summary;

AND

RMAN> list backup of datafile 1;
RMAN> list backup of datafile 2;
RMAN> list backup of datafile 3;
RMAN> list backup of datafile 4;

OR

RMAN> list backup of archivelog sequence;

After drilled down everything, I came to know that there are some backup files in FRA that are belongs to OLD INCARNATION than available backup CURRENT INCARNATION.
The problem is here, while restoring RMAN backups, RMAN implicitly perform cross verification to FRA(If FRA defined in controlfile) and if new files encounter it will be catalog(registered) automatically. This prevents database from restoring backups that belong to old CURRENT incarnation. Resulting RMAN would not allowed to restored this case. Backup incarnation and the CURRENT incarnation in controlfile should be same.

There are 3 Solutions to this error:

First: You can move old RMAN backup files(that belongs to old incarnation) to outside FRA and begin your restoration again with restoration of controlfile.

 

Second: Disabled FRA for the temporary purpose while RMAN backup being restore.

SQL> shutdown immediate;
SQL> create pfile from spfile;
comment *.db_recovery_file_dest and *.db_recovery_file_dest_size in pfile.
SQL> startup nomount pfile='.... init.ora'

Restart the restore controlfile and then restore/recover database commands…

 

Third: Resetting incarnation to the older incarnation, as below. In my case, following work around works like a charm.

RMAN> reset database to incarnation 3;
database reset to incarnation 3

After resetting database incarnation, restore database successful.

Folks, Your valuable comments are highly appreciated.

Thank you,
Stay Tune. 🙂

DBvisit Standby

DBVisit standby configuration failed with ORA-19504, ORA-27040, RMAN-06026 and RMAN-06023

While configuring DBVisit standby disaster recovery software at one of my client end environment, when DBVisit standby configuration initiated it was smooth during backup of primary database, than transferred backup to DR server. When RMAN backup arrived at DR server and started creating standby database ( restore activity by RMAN ) DBVisit popped up with following error messages:

Error message 1:

ORA-19504: failed to create file "/RTS/prod/df/RTSapp_lob.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory

Error message 2:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/18/2015 16:06:05
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 7 found to restore

Diagnosis:

After giving hard diagnosis on above red highlighted error messages, It is came to know that it was the problem with /RTS/prod/df/ path.

On primary server there was multiple datafiles locations on multiple storage LUN’s for various tablespaces. DBVisit simply copied references from primary database server and searching exact path on secondary database server. But obviously same path would not available on secondary database server. Plus While taking RMAN backup at primary database server, RMAN recorded all path and locations while backup and try to search same path and location at secondary database server while restore activity. Which is again impossible. Due to above reasons DBVisit finally came out of configuration activity with above mentioned errors.

Solution:

As a part of solution I would have two choices available in my hand, one is to discard all secondary server configuration and start from scratch (OS, oracle DB & DBVist installation) to meed exactly same path and partition scheme of primary database linux server.

Second is to create links between two directories.

So finally I have created directory link between /RTS/prod/df/ to the new datafile location of standby database server: /home/app/datafile/ifsprod/

lrwxrwxrwx. 1 oracle oinstall 27 Jun 18 18:21 df -> /u01/app/datafile/RTSprod/

After applying above fix, Resumes DBVisit configuration and it was successfully configured.

For more details on above DBVisit error, Please have a look here.

Dbvisit Standby Database Technology message from PR
Message received from process: dbvisit_setup --csd --web --ddc prod
(Dbvisit Standby: 7.0.38.13873 Process id: 6691)
201506181606 - Error executing RMAN command:
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jun 18 16:06:02 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> 
connected to target database: prod (DBID=4119037639, not open)
using target database control file instead of recovery catalog
RMAN> 
RMAN configuration parameters for database with db_unique_name prod are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 10 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/ORACLE/app/oracle/product/11.2.0/db_1/dbs/snapcf_prod.f';
echo set off
RMAN> 
allocated channel: C_DBVISIT
channel C_DBVISIT: SID=202 device type=DISK
executing command: SET NEWNAME
Starting restore at 2015-06-18:16:06:04
channel C_DBVISIT: starting datafile backup set restore
channel C_DBVISIT: specifying datafile(s) to restore from backup set
channel C_DBVISIT: restoring datafile 00007 to /RTS/prod/df/RTSapp_lob.dbf
channel C_DBVISIT: reading from backup piece /MISC/temp_dbvisit/dbv_prod_csd_dbf_7_9vq9pgmr_1_1.rman
channel C_DBVISIT: ORA-19870: error while restoring backup piece /home/temp_dbvisit/dbv_prod_csd_dbf_7_9vq9pgmr_1_1.rman
ORA-19504: failed to create file "/RTS/prod/df/RTSapp_lob.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
failover to previous backup
released channel: C_DBVISIT
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/18/2015 16:06:05
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 7 found to restore
RMAN>
Recovery Manager complete.
Dbvisit Standby terminated.
Return code = 8002
(Tracefile required if contacting Dbvisit Standby support: /usr/local/dbvisit/standby/trace/5828_dbv_functions_prod_201506181606.trc (server:DR))
Dbvisit Standby terminated.
Return code = 66
(Tracefile required if contacting Dbvisit Standby support: /usr/local/dbvisit/standby/trace/6691_dbvisit_setup_csd_prod_201506181008.trc (server:PR))

 

Cheers!!

Stay tune. 🙂

Oracle 11g Logo

ORA-19502: write error on file “”, block number xxxxxxxx (block size=8192)

Today morning, My one of the RMAN full database backup failed with RMAN-03002, ORA-19502 and ORA-27072 error. After diagnosing the problem it was space issue only.

There is no space available for RMAN full backup.

Error log:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 08/06/2015 01:16:57
ORA-19502: write error on file "/BACKUP/PROD/weekly_full/bkup_20150606_0vq8p2h0_1_1.rman", block number 2873985 (block size=8192)
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 2873985
Additional information: 696320

Solution:

Just reclaim the storage space by setting appropriate retention to RMAN backup policy and deleted some old files that not needed for any kind of recovery.

By doing above steps, RMAN backup started successfully.

Stay Tune. 🙂

Oracle 11g Logo

How to relocate/move oracle datafiles in Oracle 11g

We can move/relocate oracle datafile to different location, In this article, we will move datafile with the help of Recovery Manager.

Consider step by step approach to move oracle datafile as below:

Step I:

Get database datafile ID those you wanted to move with the help of REPORT SCHEMA rman command as below:

 RMAN> REPORT SCHEMA;
 Report of database schema for database with db_unique_name PROD
 List of Permanent Datafiles
 ===========================
 File Size(MB) Tablespace RB segs Datafile Name
 ---- -------- -------------------- ------- ------------------------
 1 8880 SYSTEM YES /DATA1/database/prod/system01.dbf
 2 2710 SYSAUX NO /DATA1/database/prod/sysaux01.dbf
 3 17255 UNDOTBS1 YES /DATA1/database/prod/undotbs01.dbf
 4 25 USERS NO /DATA1/database/prod/users01.dbf
 5 6444 PROD_DATA NO /DATA1/database/prod/prodapp_data.dbf
 6 5634 PROD_INDEX NO /DATA1/database/prod/prodapp_index.dbf
 7 32744 PROD_LOB NO /DATA1/database/prod/prodapp_lob.dbf
 List of Temporary Files
 =======================
 File Size(MB) Tablespace Maxsize(MB) Tempfile Name
 ---- -------- -------------------- ----------- --------------------
 1 500 TEMP_NEW 32767 /DATA/database/prod/temp_01.dbf

Note: To simulate scenario, I am going to move PROD_LOB tablespace( i.e. FILE ID:7 ) from ‘/DATA1/database/prod/’ to new location ‘/NewData/prodDB/df/’.

Step II:

Offline tablespace that you want to move.

 RMAN> SQL 'ALTER TABLESPACE PROD_LOB OFFLINE';

Step III:

Copy all the datafiles to destination, In our case PROD_LOB tablespace having only one datafile.

 RMAN> COPY DATAFILE 7 TO '/NewData/prodDB/df/prodapp_lob.dbf';

Step IV:

Switch back to the new datafile copy(s)

 RMAN> SWITCH DATAFILE 7 TO COPY;

Step V:

Bring offline Tablespace to online state with the help of SQL command: ‘ALTER TABLESPACE’.

 RMAN> SQL 'ALTER TABLESPACE PROD_LOB ONLINE';

Step VI:

Ensure above changes with the help of REPORT SCHEMA rman command as below:
 RMAN> REPORT SCHEMA;
 ...
 File Size(MB) Tablespace RB segs Datafile Name
 ---- -------- -------------------- ------- ------------------------
 ...
 7 32744 PROD_LOB NO /NewData/prodDB/df/prodapp_lob.dbf

Step VII:

If Tablespace get back to online state successfuly, than old datafile is useless. Remove it.

 RMAN> HOST 'rm /DATA1/database/prod/prodapp_lob.dbf';

Cheers!! Datafile relocated successfully.

Related Article

Online Move Datafiles in Oracle 12c R1 without getting file offline

 

Oracle 11g Logo

RMAN-06059: expected archived log not found, loss of archived log compromises recoverability

While RMAN full backup, today i have encountered “RMAN-06059: expected archived log not found, loss of archived log compromises recoverability”

RMAN error.
Solution for the mentioned error is to Crosscheck your archive logs from RMAN prompt and delete those expired logs who are not needed anymore.

While crosscheck archivelogs, RMAN will marked archives as Expired in his repository (i.e. controlfile or catalog) those who are not physically present there. So we can easily delete expired archives from controlfile or catalog repository.

Error log:

RMAN> RUN
 2> {
 3> ALLOCATE CHANNEL backup1 DEVICE TYPE DISK FORMAT 'Z:\RMAN\%U';
 4> BACKUP DATABASE PLUS ARCHIVELOG;
 5> }
using target database control file instead of recovery catalog
 allocated channel: backup1
 channel backup1: SID=70 device type=DISK
Starting backup at 10-SEP-14
 current log archived
 released channel: backup1
 RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of backup plus archivelog command at 09/10/2014 18:22:35
 RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
 ORA-19625: error identifying file E:\ORACLE\--ORACLE_SID--\ORAARCH\1_35338_778192583.DBF
 ORA-27041: unable to open file
 OSD-04002: unable to open file
 O/S-Error: (OS 2) The system cannot find the file specified.
RMAN> CROSSCHECK ARCHIVELOG ALL;

Above command will give information of expired RMAN repository after verification. (i.e. in catalog or controlfile)
Above will marked archives as Expired who are not available physically and who are not required for any kind of recovery..

RMAN> DELETE EXPIRED ARCHIVELOG ALL;

Above command will delete all archive logs who are marked as expired while crosscheck.

Oracle 11g Logo

How to configure default device type for RMAN backup – RMAN Part-6

In my earlier article, I have covered How to show, change & clear RMAN persistent configuration – RMAN Part-5, In this article I am going to explain how to configure default device type i.e. Disk/sbt.

In case we haven’t provide any kind of backup destination device in RMAN script while backup, than RMAN considered Default Device Type value configured with RMAN parameters. No more configuration is required in case of RMAN parameter is pre-configured to use Disk/sbt as default device type.

Default Device Type: Disk

 RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;
 new RMAN configuration parameters:
 CONFIGURE DEFAULT DEVICE TYPE TO DISK;
 new RMAN configuration parameters are successfully stored

 RMAN> show DEFAULT DEVICE TYPE;
 RMAN configuration parameters for database with db_unique_name GALAXY are:
 CONFIGURE DEFAULT DEVICE TYPE TO DISK;

Above command specifies that, All RMAN backups should go to disk.( i.e. default )
In case of flash recovery area is enabled, than RMAN default backup location would be fast recovery area, But in case of FRA is disabled than default backup location would be system specific directory on disk.

Default Device Type: sbt

 RMAN> CONFIGURE DEFAULT DEVICE TYPE TO sbt;
 new RMAN configuration parameters:
 CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
 new RMAN configuration parameters are successfully stored

 RMAN> show DEFAULT DEVICE TYPE;
 RMAN configuration parameters for database with db_unique_name GALAXY are:
 CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';

Above command specifies that all RMAN backup should go to sbt. ( i.e. SBT_TAPE )( i.e. default )

DEVICE TYPE clause:
With the help of above command, we can override default device type with the help of BACKUP command.
Ex:

 BACKUP DEVICE TYPE DISK DATABASE;
 BACKUP DEVICE TYPE sbt DATABASE;

Clear Default Device Type:

 RMAN> CONFIGURE DEFAULT DEVICE TYPE clear;
 old RMAN configuration parameters:
 CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
 RMAN configuration parameters are successfully reset to default value

 RMAN> show DEFAULT DEVICE TYPE;
 RMAN configuration parameters for database with db_unique_name GALAXY are:
 CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

This all about configuring default device type.

Stay Tune. 🙂

Oracle 11g Logo

How to show, change & clear RMAN persistent configuration – RMAN Part-5

To perform basic backup and recovery through RMAN backup tool, RMAN provides facility to show its default configuration, change it and clearing persistent RMAN configuration.

For the sake of simplicity, we can set persistent RMAN configuration settings, so that we can control RMAN behavior.
For example, With the help of ‘SHOW’ and ‘CONFIGURE’ RMAN commands we can configure default destinations of backups, default backup retention policy, default backup device type, controlfile autobackup and so on…

Note: We can overwrite default RMAN configuration while backup.

SHOW RMAN command:

This command is used to show current value of RMAN settings, like below:

RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name GALAXY are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 7;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_galaxy.f'; # default

OR We can also use ‘SHOW’ RMAN command with configuration name to show configuration, as follows:

RMAN> show backup optimization;
RMAN configuration parameters for database with db_unique_name GALAXY are:
CONFIGURE BACKUP OPTIMIZATION ON;

OR

RMAN> show encryption algorithm;
RMAN configuration parameters for database with db_unique_name GALAXY are:
CONFIGURE ENCRYPTION ALGORITHM 'AES128';

 

CONFIGURE RMAN command:

This command is used to change the RMAN configuration, like below:

RMAN> configure controlfile autobackup clear;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN configuration parameters are successfully reset to default value
RMAN> show controlfile autobackup;
RMAN configuration parameters for database with db_unique_name GALAXY are:
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

OR

RMAN> configure backup optimization clear;
old RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
RMAN configuration parameters are successfully reset to default value
RMAN> show backup optimization;
RMAN configuration parameters for database with db_unique_name GALAXY are:
CONFIGURE BACKUP OPTIMIZATION OFF; # default

Like above examples, we can show, change and clear RMAN configurations.

Stay Tune. 🙂

Oracle 11g Logo

How to configure server parameter file & controlfile autobackup in RMAN – RMAN Part-4

In most of the worst situation like data loss and recovery, backup of recent control file and server parameter file is most valuable. RMAN supports autobackup facility to make sure these important files being backed up.

While full OR incremental backup instantiated, backup of controlfile and server parameter file automatically backed up.
Auto Backup of control file and server file taken with archivelog backups too when any changes made to database structure. If there is no changes encountered to database structure then no autobackup instantiated.

Benefits of autobackup: in case of loss current control file, recovery catalog and server parameter file, we can recover database.

Following RMAN command will help you to enable autobackup of controlfile:

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

To disable autobackup of controlfile, issue the following:

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

 

Configuring control file autobackup format.

With the help of following command we can overwrite default format controlfile autobackup:

 RMAN> configure controlfile autobackup format for device type disk to '%F_Autobackup';
 new RMAN configuration parameters:
 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F_Autobackup';
 new RMAN configuration parameters are successfully stored

Verify above changes with the help of following command:

 RMAN> show controlfile autobackup format;
 RMAN configuration parameters for database with db_unique_name GALAXY are:
 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F_Autobackup';

Configure autobackup controlfile to Automatic Storage Management:

 RMAN> configure controlfile autobackup format for device type disk to '<disk_group>/%F';

 

To clear autobackup controlfile format, issue the following:

 RMAN> configure controlfile autobackup format for device type disk clear;
 old RMAN configuration parameters:
 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F_Autobackup';
 RMAN configuration parameters are successfully reset to default value

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

How to take oracle database incremental backup with RMAN backup utility – RMAN Part-3

In my earlier article, I have covered “How to backup oracle database in Archivelog and NoArchivelog mode with RMAN backup utility”.

Here, Now I am going to explain RMAN Incremental backup terminologies and how to make incremental backups of your database.

What is Incremental backup?

Incremental backup is a backup who takes backup of only those blocks who modified ( i.e. block level changes done ) after full/incremental database backup. Incremental. backups are smaller and takes less time to backup as compared to full RMAN database backup.

While taking first incremental backup, it will be count as level 0 incremental backup, which is same in content to RMAN full backup and takes backup of all blocks in the database. Level 1 incremental backup takes backup of only those blocks who modified after earlier incremental backup.

There are two levels of incremental backup:

  • Cumulative: In which backup considered all modified blocks to backup right after last level 0 incremental backup.
  • Differential: In which backup considered all modified blocks to backup right after recent incremental backup. Default: differential.

Lets consider small scenario and you will get total idea of what is differential and what is cumulative incremental backup:
Consider we have big organizational data with huge size, whose full and incremental backup scheduled are as follows:

Sun: Full RMAN backup @ 9pm.
Mon: Differential incremental backup @ 9pm.
Tue: Differential incremental backup @ 9pm.
Wed: Cumulative incremental backup @ 9pm.

Now consider we got worst scenario of data loss:

Scenario 1:

In case of worst situation occurs on Monday after backup, If we want to recover database till Monday than RMAN will consider Sunday:full RMAN backup + Monday:differential incremental backup to recover database.

Scenario 2:

In case of worst situation occurs on Tuesday after backup, If we want to recover database till Tuesday than RMAN will consider Sunday:full RMAN backup + Monday:differential incremental backup + Tuesday:differential incremental backup to recover database.

Scenario 3:

In case of worst situation occurs on Wednesday after backup, If we want to recover it till Wednesday than RMAN will consider Sunday: full RMAN backup + Wed: Cumulative incremental backup to recover database. because cumulative backup already backed up all modified blocks changes of Monday, Tuesday & Wednesday in one time, in this case no need to apply differential backup of Monday and Tuesday.

Lets consider hands-on on how to take Incremental backups:

To take Level 0 incremental backup, issue the following command on RMAN prompt:

RMAN> backup incremental level 0 database;

To take Level 1 incremental backup, issue the following command:

RMAN> backup incremental level 1 database;

To take Level 1 cumulative incremental backup, issue the following command:

RMAN>backup incremental level 1 cumulative database;

 

With the help of above RMAN commands, you can take database incremental backups.

This is all about RMAN Incremental backup terminologies and how to make incremental backups of your database.

Stay tune. 🙂

Oracle 11g Logo

How to backup database in Archivelog and NoArchivelog mode with RMAN backup utility – RMAN Part-2

In my previous article I have covered, overview of RMAN, Various components of RMAN, and how to start and connect with database.

In this article, I am going to cover how to backup database with the help of RMAN backup utility in Archive log and NoArchive log mode.

We can backup database file with the help “backup database” RMAN command. This will backup your database files to already configured device type. i.e. Disk or SBT, default is Disk.

With RMAN, we backup database as “backup set” OR backup database as “backup as copy”. While backup, by default RMAN creates “backup sets”, backup sets consist of backup piece[s], backup piece means physical file genrated and readable by only RMAN.

With the help of “backup as copy” RMAN command, we can backup database as copy of that database, In which each files would be copy as image. Image copy are similar to copies generated with OS command like cp/copy. Usable by RMAN only. We can use “backup as copy” while database in open mode.

 

Lets Consider hands-on on Database backup in NoArchive log mode:

While database is running under NoArchive log mode, than only way to consider consistent backup is to take your database in mount state after consistant shutdown. ( i.e. ‘shutdown’ OR ‘shutdown immediate’ ) So that restoring database backup would not required any kind of recovery.

To backup your database in NoArchivelog mode ( i.e. consistent backup ), consider following:
Switch to oracle user:

[root@ol11g ~]# su - oracle

Connect to database and shutdown database consistently:

-bash-4.1$ sqlplus / as sysdba
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Start your database in mount state:

SQL> startup mount;
ORACLE instance started.
Total System Global Area 442601472 bytes
Fixed Size 2214176 bytes
Variable Size 331351776 bytes
Database Buffers 104857600 bytes
Redo Buffers 4177920 bytes
Database mounted.
SQL> exit

Connect to RMAN prompt with target database ( i.e. galaxy ) and start database backup:

-bash-4.1$ rman target /
connected to target database: GALAXY (DBID=3647455456, not open)
RMAN> backup database;

OR

RMAN> backup as copy database;

Lets Consider hands-on on Database backup in Archive log mode:
Note:
To backup your database in archive log mode, your database must be configured in archive log mode. If not, follow my one of the article to convert database in archivelog mode.

As we know, Redo logs are required at the time of recovery to open database in consitent state, thats why we called this backup as inconsistent.

To backup database along with archive log, issue the following RMAN command:

RMAN> backup database plus archivelog;

 

These is all about backup database with the help of RMAN backup utility in Archive log and NoArchive log mode.

Kindly stay tuned for my next article: How to take oracle database incremental backup with RMAN backup utility – RMAN Part-3

🙂

Oracle 11g Logo

Getting started with RMAN – Overview of RMAN, Starting and connecting to database – RMAN Part-1

Hello All, Hope you are doing well.

Many times, We always have question in our mind that from where to start learning, when we suppose to implement a new concept, isn’t it? This article is for beginners who willing to learn RMAN in depth. In this article I am going to cover overview of RMAN, Various components of RMAN, and how to start and connect with database.

Lets begin with RMAN overviews:

RMAN stands for Recovery Manager, this is client who performs not only backup and recovery for oracle database but also making DBA’s life easier in terms of automation, backup, restore and recovery of database. RMAN consist of some backup utilities and collaborated with your oracle database that combine play a role in database backup.

 

RMAN components:

Target Database:
Target database is the registered database with the RMAN, on which RMAN is performing backup and recovery operations. All operations performed and practiced ( i.e.metadata is also known as repository. ) by RMAN would be logged in database control file.

RMAN client:

RMAN client is nothing but the executable required to run RMAN and performers its desire task. RMAN client situated in ‘ORACLE_HOME/bin’. No extra efforts would be required to install this client, because this client installed automatically when oracle software being installed.

 

RMAN Optional components are as follows:

Fast Recovery Area:
Fast Recovery Area also called as FRA, this is nothing but the disk location where database can stored and manage files regarding backup and recovery. We can control FRA by specifying its location and size according to user’s convenience.

Media Manager:
Media manager is responsible in making communication between RMAN client and media like tape, etc. It also responsible for managing media while backup and recovery, media loading, media labeling and unloading.

Recovery Catalog:
Recovery catalog is one of the database schema, who holds all RMAN activity of one or more target databases.
There is a risk involved in losing all RMAN repository metadata while loss of controlfile. But with the help of recovery catalog we can reatain it.

In case of loss of database controlfile there is a risk to loss of all RMAN repository metadata.
Recovery catalog don’t overwrite older records but control file do by the time goes on.

Lets do some practical, Starting RMAN and connecting to your database.
To start RMAN, we need to instantiate RMAN client by RMAN command on OS prompt:

[root@ol11g ~]# su - oracle
 -bash-4.1$ rman 
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Oct 6 22:13:05 2006
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN>

Note: Above, we considered oracle environmental variables already set. ( ‘ORACLE_HOME’, ‘ORACLE_SID’ and ‘PATH’ )

To connect target database, issue the command “connect target /” on RMAN prompt with the help of OS authentication.

RMAN> connect target /
connected to target database: GALAXY (DBID=3647455456)

Note: GALAXY is my target database & 3647455456 is its Database ID.

We can also connect to target database with the help of user authentication:

RMAN> connect target system@galaxy
connected to target database: GALAXY (DBID=3647455456)

To quite from RMAN prompt, simply write ‘exit’ on RMAN prompt:

RMAN> exit;

 

These is all about Overview, components of RMAN and starting RMAN and connecting with database.

In my next article, I will explain how to backup database with the help of RMAN backup utility in Archive log and NoArchive log mode.

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. 🙂