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

Oracle 11gR2 software and database installation on Oracle Linux 6.5

Here we are installing Oracle 11g Release-2 (11.2)(64-bit) on Oracle Linux release-6 (64-bit)

Kindly have a look on Pre-requisites before proceeding for oracle 11gR2 installation.

Memory requirements:

  • Minimum 1 GB RAM, Recommended 2 GB or more than that.
  • Following commands will helpyou to determine RAM of your system:
    • # grep MemTotal /proc/meminfo
    • # free -m // shows RAM + SWAP
  • If RAM of your system does not meet minimum requirement than increase it.
  • SWAP partition requirement for oracle installation is only depend on RAM installed in your system, according to oracle docs.
    1. If your RAM between 1 GB to 2 GB than your swap partition size should be 1.5 times the size of the RAM.
    2. If your RAM between 2 GB to 16 GB than your swap partition size should be equal to the size of the RAM.
    3. If your RAM more than 16 GB than your swap partition size should be 16 GB.

Disk space requirement:

  • /tmp directory size would be at least 1 GB.
  • Determine available disk space of /tmp directory with following command:
    • # df -h /tmp
  • Minimum requirement of total disk space of the system would directly depend upon size of your database, Oracle installation files take maximum 4.5 GB to 4.7GB of disk space.
  • Determine total diskc space available with following:
    • # df -h

Verify your system architecture with following command:

# uname -m

Update host file “/etc/hosts” with fully qualified name like below:

Ex:
 115.xxx.xxx.xxx ol6.localdomain ol6

Note:
In my case “ol6.localdomain” is fully qualified name with machine name.

Update following entries in “/etc/sysctl.conf” file:

 fs.aio-max-nr = 1048576
 fs.file-max = 6815744
 kernel.shmall = 2097152
 kernel.shmmax = 536870912
 kernel.shmmni = 4096
 kernel.sem = 250 32000 100 128
 net.ipv4.ip_local_port_range = 9000 65500
 net.core.rmem_default = 262144
 net.core.rmem_max = 4194304
 net.core.wmem_default = 262144
 net.core.wmem_max = 1048576

To change kernel parameter, run following:

 # /sbin/sysctl -p
 OR
 # sysctl -p

Update following entries in “/etc/security/limits.conf” file:

 oracle soft nproc 16384
 oracle hard nproc 16384
 oracle soft nofile 4096
 oracle hard nofile 65536
 oracle soft stack 10240

Install following packages by yum or rpm:

 binutils-2.20.51.0.2-5.11.el6 (x86_64)
 compat-libcap1-1.10-1 (x86_64)
 compat-libstdc++-33-3.2.3-69.el6 (x86_64)
 compat-libstdc++-33-3.2.3-69.el6.i686
 gcc-4.4.4-13.el6 (x86_64)
 gcc-c++-4.4.4-13.el6 (x86_64)
 glibc-2.12-1.7.el6 (i686)
 glibc-2.12-1.7.el6 (x86_64)
 glibc-devel-2.12-1.7.el6 (x86_64)
 glibc-devel-2.12-1.7.el6.i686
 ksh
 libgcc-4.4.4-13.el6 (i686)
 libgcc-4.4.4-13.el6 (x86_64)
 libstdc++-4.4.4-13.el6 (x86_64)
 libstdc++-4.4.4-13.el6.i686
 libstdc++-devel-4.4.4-13.el6 (x86_64)
 libstdc++-devel-4.4.4-13.el6.i686
 libaio-0.3.107-10.el6 (x86_64)
 libaio-0.3.107-10.el6.i686
 libaio-devel-0.3.107-10.el6 (x86_64)
 libaio-devel-0.3.107-10.el6.i686
 make-3.81-19.el6
 sysstat-9.0.4-11.el6 (x86_64)
 unixODBC-2.2.14-11.el6 (x86_64) or later
 unixODBC-2.2.14-11.el6.i686 or later
 unixODBC-devel-2.2.14-11.el6 (x86_64) or later
 unixODBC-devel-2.2.14-11.el6.i686 or later

Note:
Determine required packages are installed or not with the help of following command:

# rpm -q package_name

Create oracle groups and users:

 groupadd -g 501 oinstall
 groupadd -g 502 dba
 groupadd -g 503 oper
 groupadd -g 504 asmadmin
 groupadd -g 506 asmdba
 groupadd -g 505 asmoper
 useradd -u 502 -g oinstall -G dba,asmdba,oper oracle
 passwd oracle

Set SELINUX flag ‘disable’ in “/etc/selinux/config” file.

 SELINUX=disabled

Restart server after setting SELINUX disabled.

Create directory for oracle software to be installed:

 # mkdir -p /u01/app/
 # chown -R oracle:oinstall /u01/app/
 # chmod -R 775 /u01/app/

Disable IP tables:

 # service iptables stop
 # chkconfig iptables off //This command will make changes persistent over reboot.

Configure VNC server for GUI, Nice article here.

Oracle software download link.

 

Unzip installer and continue..

On GUI, open terminal prompt as a root user & issue the following command:
 xhost +
 su – oracle

Run universal installer and follow the steps by step installation of oracle 11gR2:

 ./runIstaller

And follow the steps mentioned below:

Oracle 11gR2 installation on Oracle Linux 6.5 - Configure Security Updates

Oracle 11gR2 installation on Oracle Linux 6.5 – Configure Security Updates

 

You can directly create and configure a database by selecting first radio button, in my case first i will install database software only and than database.

Oracle 11gR2 installation on Oracle Linux 6.5 - Select Installation Option

Oracle 11gR2 installation on Oracle Linux 6.5 – Select Installation Option

 

Select single instance database installation radio button and continue, as we are installing single instance database:

Oracle 11gR2 installation on Oracle Linux 6.5 - Node selection

Oracle 11gR2 installation on Oracle Linux 6.5 – Node selection

 

Select Product Languages according to your convenience, in my case it is English, default language.

Oracle 11gR2 installation on Oracle Linux 6.5 - Select Product Languages

Oracle 11gR2 installation on Oracle Linux 6.5 – Select Product Languages

 

Select database edition as per your requirement, in my case it would be Standard Edition.

Oracle 11gR2 installation on Oracle Linux 6.5 - Select database edition

Oracle 11gR2 installation on Oracle Linux 6.5 – Select database edition

 

Provide path of oracle base and continue, In my case its default.

Oracle 11gR2 installation on Oracle Linux 6.5 - specify installation location

Oracle 11gR2 installation on Oracle Linux 6.5 – specify installation location

 

Provide ‘Inventory Directory’, ‘OraInventory group’ and continue.

Oracle 11gR2 installation on Oracle Linux 6.5 - Create Inventory

Oracle 11gR2 installation on Oracle Linux 6.5 – Create Inventory

 

Select ‘Database Administrator Group’ and ‘Database Operator Group’ and continue.

Oracle 11gR2 installation on Oracle Linux 6.5 - Privilege Operation System Group

Oracle 11gR2 installation on Oracle Linux 6.5 – Privilege Operation System Group

 

If your system already installed latest version of mentioned packages, then you can ignore and continue. In my case, latest version available.

Oracle 11gR2 installation on Oracle Linux 6.5 - Perform Prerequisite Check

Oracle 11gR2 installation on Oracle Linux 6.5 – Perform Prerequisite Check

Oracle 11gR2 installation on Oracle Linux 6.5 - Summary

Oracle 11gR2 installation on Oracle Linux 6.5 – Summary

Oracle 11gR2 installation on Oracle Linux 6.5 - Install Product

Oracle 11gR2 installation on Oracle Linux 6.5 – Install Product

 

Execute mentioned configuration script as root user and continue.

Oracle 11gR2 installation on Oracle Linux 6.5 - Execute Configuration Scripts

Oracle 11gR2 installation on Oracle Linux 6.5 – Execute Configuration Scripts

Software installation completed successfully.

Now database installation, for that i have instantiated DBCA from oracle_home/bin directory.

Oracle 11gR2 installation on Oracle Linux 6.5

Oracle 11gR2 installation on Oracle Linux 6.5

Oracle 11gR2 installation on Oracle Linux 6.5 - Create database

Oracle 11gR2 installation on Oracle Linux 6.5 – Create database

Oracle 11gR2 installation on Oracle Linux 6.5 - General purpose or Transaction Processing

Oracle 11gR2 installation on Oracle Linux 6.5 – General purpose or Transaction Processing

 

Provide ‘Global Database Name’, ‘SID’, and continue.

Oracle 11gR2 installation on Oracle Linux 6.5 - Database Identification

Oracle 11gR2 installation on Oracle Linux 6.5 – Database Identification

 

Checked ‘Configuration Enterprise Manager’ check box in case of you wanted to configure it, In my case, its not my requirement.

Oracle 11gR2 installation on Oracle Linux 6.5 - Management Option

Oracle 11gR2 installation on Oracle Linux 6.5 – Management Option

 

Provide SYS and SYSTEM user password, otherwise provide common password for the both user and continue.

Oracle 11gR2 installation on Oracle Linux 6.5 - Database Credentials

Oracle 11gR2 installation on Oracle Linux 6.5 – Database Credentials

 

Select Database file locations according to your requirements, in my case i am using default option, i.e. From Template.

Oracle 11gR2 installation on Oracle Linux 6.5 - Database File Location

Oracle 11gR2 installation on Oracle Linux 6.5 – Database File Location

 

Provide Flash Recovery Area path and its size according to your convenience. You can also enable Archive log mode.

Oracle 11gR2 installation on Oracle Linux 6.5 - Recovery Configuration

Oracle 11gR2 installation on Oracle Linux 6.5 – Recovery Configuration

Oracle 11gR2 installation on Oracle Linux 6.5 - Database Content

Oracle 11gR2 installation on Oracle Linux 6.5 – Database Content

 

Configure memory, sizing, character Sets and connection mode according to your convenience and continue.

Oracle 11gR2 installation on Oracle Linux 6.5 - Initialization Parameters

Oracle 11gR2 installation on Oracle Linux 6.5 – Initialization Parameters

 

Verify controlfile multiplexing, datafiles and redo log group and continue.

Oracle 11gR2 installation on Oracle Linux 6.5 - Database Storage

Oracle 11gR2 installation on Oracle Linux 6.5 – Database Storage

Oracle 11gR2 installation on Oracle Linux 6.5 - Creation Option

Oracle 11gR2 installation on Oracle Linux 6.5 – Creation Option

Oracle 11gR2 installation on Oracle Linux 6.5 - Database Configuration Assistant

Oracle 11gR2 installation on Oracle Linux 6.5 – Database Configuration Assistant

 

Cheers!! Oracle software and database creation successfully completed.

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

Oracle Backup and Recovery Solutions

In my earlier article I have covered “Introduction to Oracle backup and recovery” concept, where in this article I am going to explain various backup and recovery solutions are available while implementing backup and recovery strategy in Oracle.
We have below solutions available while implementing backup and recovery strategy.

Recovery Manager:

Recovery Manager is also called as RMAN, This is highly recommended and very easy to manage backup tool for oracle backup and recovery, RMAN offers HA & DR concerns with backup, restore and recovery of database.
RMAN performs a range of backup and recovery of database and fully integrated with Oracle database. RMAN have ‘RMAN repository’ who holds historical data of various backups. RMAN can be accessed with either Oracle Enterprise Manager called OEM or RMAN command line utility.

User Managed Backup and Recovery:

This kind of backup and recovery will be performed by user with the help of OS commands and SQL* Plus recovery commands. In this, user who performing backups and recovery have responsible for when & how backup and recovery will be carried out. The ultimate goal is to recovery database with no and minimal data loss in case of worst situations.

Incremental Backup:

Incremental backup takes backup of only those blocks who modified after full database backup. Every time considering full database backup is not feasible, resulting RMAN provided incremental backup to take backup of only modified data i.e. blocks.
Incremental backup size is more compact compared to full backup size, faster recovery. Incremental backup performs sequential search on every input data files to find out modified blocks after full database backup and mark for incremental backup. We can also improve incremental backup performance by enabling “Block Change Tracking” feature.
I have elaborated more about “Block Change Tracking” in my one of the article, please have a look here.

Block Media Recovery:

With the help of “RECOVER BLOCK” command we can recover/repair a data file corrupted blocks up-to certain extent without taking datafile or database offline.

Binary Compression:

We can compressed and reduce the data in backup set size with the help of Binary Compression algorithm.

Encrypted Backup:

For security reasons, RMAN Backup Encryption feature help us to take backups in encrypted format. To avail this feature, your database need to be configured with Advanced Security Option.
RMAN offers three modes of encryption, named as Transparent, Password-protected, and dual-mode. RMAN encrypts backup sets at the time of backup and decrypt it while restore.

Automatic Database Duplication:

We can easily make a copy of our database, with great support of storage configuration. Direct duplication between Automatic Storage Manager (ASM) databases also possible.

Cross Platform Data Conversion:

We can backup and restore database over the platforms, physical backups are platform independent.

 

Stay Tune. 🙂

Oracle 11g Logo

Introduction to Oracle backup and recovery

Backup is nothing but the copy of your real database that you will use to reconstruct your data means recovery.

Now a day’s information and data is soul of organizations. As a responsible DBA it’s your primary duty to devise, configure, and manage a backup & recovery strategy. The primary purpose of a backup & recovery is to recovery of your database in case of worst situation like data loss in any sense.

Basically Backup Database Administrator having following responsibilities/task:

  • Plan a zero impact database backup strategy.
  • Implement it on test server before actual production environment.
  • Configure production environment for backup & recovery.
  • Setting up proper backup schedule.
  • Monitor and manage the backup & recovery.
  • Troubleshooting backup issues.
  • In case of data lost, recover database.
  • RMAN – Recovery Manager
  • User Managed backup
  • Data Pump Export-Import

Types of oracle backups available

  • RMAN – Recovery Manager
  • User Managed backup
  • Data Pump Export-Import

Note: In my next article, I will explore about the types of oracle backups in depth.

 

Let’s understand backup and recovery need in-depth with Data Protection, Data Prevention & Data Transfer.

Data Protection

As responsibility of backup DBA, your first job is to make backup available and monitoring it for the data protection. There is two types of backups, one is Physical backup and another is Logical backup.

Physical backup terms as physical database files like data files, control files and archived redo log files, or we can say that physical backups are the foundation of backup and recovery. And Logical backup terms as logical data like tables, stored procedures, views, etc.  Oracle Data pump is used to backup logical data to binary files. We can say that Logical backups are useful supplement to physical backups.

In following case, exclusively need DBA intervention:

If Media Failure occurs:

Media failure is nothing but the problem with physical files on physical disk block read/write.

 

If User Error occurs:

User error is nothing but any manual mistake done by user like any record or table deleted unfortunately, OR error in application logic OR it could be anything done by users.

Proper user, security and privilege management and training can prevent these kind of user errors. Your smart backup strategy will decide and recover your database in case of user errors.

 

If Application Error occurs:

Many times inappropriate developed application software malfunctions corrupt data blocks/media corruption. Database unable to recognize physical blocks and checksum also invalid.

This could be recovery with the help of Block Media Recovery.

 

Data Preservation:

Data preservation is nothing but the data protection, but this data is used for the different purpose.

You may come across the case where client requires or demands data of 10 OR 15 years old. This is not the part of disaster recovery or anything else. This types of backups also called as archival backup.

Data Transfer:

Many times we need to take database backup and move it to another location for x y z reasons.

 

This is all about the small introduction to Oracle backup and recovery, In my next article I will cover “Various Oracle Backup and Recovery Solutions available”

Stay tune. 🙂

Oracle 11g Logo

How to Create TEMPORARY tablespace and drop existing temporary tablespace in oracle 11g

In this article, I will explain you the step by step guide to create new TEMP tablespace and drop existing temporary tablespace.

While doing this activity, existing temporary tablespace may have existing live sessions, due to same oracle won’t let us to drop existing temporary tablespace. Resulting, we need to kill existing session before dropping temporary tablespace.

Following query will give you tablespace name and datafile name along with path of that data file.

SQL> select FILE_NAME,TABLESPACE_NAME from dba_temp_files;

Following query will create temp tablespace named: ‘TEMP_NEW’ with 500 MB size along with auto-extend and maxsize unlimited.

SQL> CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE '/DATA/database/ifsprod/temp_01.dbf' SIZE 500m autoextend on next 10m maxsize unlimited;

Following query will help you to alter database for default temporary tablespace. ( i.e. Newly created temp tablespce: ‘TEMP_NEW’ )

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;

Retrieve ‘SID_NUMBER’ & ‘SERIAL#NUMBER’ of existing live session’s who are using old temporary tablespace ( i.e. TEMP ) and kill them.

SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

Provide above inputs to following query, and kill session’s.

SQL> alter system kill session 'SID_NUMBER, SERIAL#NUMBER';

For example:

SQL> alter system kill session '59,57391';

Now, we can drop old temporary tablespace without any trouble with following:

SQL> DROP TABLESPACE old_temp_tablespace including contents and datafiles;

 

Contents and datafiles are deleted successfully.
If you wish to continue with old temporary tablespace name, i.e. ‘TEMP’ then follow same step mentioned above to recreate temp tablespace with old name.

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 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 12c Logo

DBVERIFY: Offline Oracle Database Verification Utility For Datafile And segment

DBVERIFY is command-line utility ( external ) that used to performs physical data structure integrity check like data files, where it verifies data blocks corruption OR Segment corruption of data files.
We can use this utility to verify database backup file integrity check, i.e. Backup files are restorable or not.
This utility also called offline database verification utility, because we can verify/perform online as well as offline database integrity check. As we can perform it offline this utility is significantly faster.

Note:
DBVERIFY utility are limited to cache-managed blocks. ( data blocks )
Only used to verify data files, NOT with control files OR redo log files. Continue reading

Oracle 12c Logo

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

Prior to Oracle 12c, moving datafiles to another location needs downtime ( Need to Offline datafiles ) but from Oracle 12c we can accomplish same with ALTER DATABASE command.

Syntax:
ALTER DATABASE MOVE DATAFILE ( ‘filename’ | ‘ASM_filename’ | file_number )
[ TO ( ‘filename’ | ‘ASM_filename’ ) ] [ REUSE ] [ KEEP ]

Note:

  • Source file would be specified with either filename, ASM_filename or file_number.
  • Destination file should be specified by filename only.
  • REUSE: New file should create even if it already exists.
  • KEEP: Original copy of the datafile should be retained.

 

// Online move datafile with the help of ALTER DATABASE command + File name.
SQL> select FILE_NAME, FILE_ID, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME=’SYSTEM’;

FILE_NAME FILE_ID TABLESPACE_NAME
—————————————————- ———- ———————–
/u01/app/oracle/oradata/ORCL/datafile/system01.dbf 1 SYSTEM

SQL> ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/ORCL/datafile/system01.dbf’ TO ‘/tmp/system01.dbf’;

Database altered.

// Ensure your changes by issuing same above query, In FILE_NAME column kindly observed the location of datafile has been changed to ‘/tmp/…’
SQL> select FILE_NAME, FILE_ID, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME=’SYSTEM’;

FILE_NAME FILE_ID TABLESPACE_NAME
—————————————————- ———- ———————–
/tmp/system01.dbf 1 SYSTEM

// Online move datafile with the help of ALTER DATABASE command + File number.
SQL> ALTER DATABASE MOVE DATAFILE 1 TO ‘/u01/app/oracle/oradata/ORCL/datafile/system01.dbf’;

Database altered.

// Ensure your changes by issuing same above query:
SQL> select FILE_NAME, FILE_ID, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME=’SYSTEM’;

FILE_NAME FILE_ID TABLESPACE_NAME
—————————————————- ———- ———————–
/u01/app/oracle/oradata/ORCL/datafile/system01.dbf 1 SYSTEM

 

Cheers!! Now we can move our datafile without downtime with Oracle 12c.

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

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

Thanking you.

Have a easy life ahead.

Oracle 12c Logo

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3072M – Oracle 12c

Today morning, I got new excitement with oracle error: ORA-00838. Error message itself self-explanatory.

Cause: Existing MEMORY_TARGET value not enough to start database.

Solution: Increase MEMORY_TARGET value.

 

Consider following workaround:

[oracle@12c bin]$ sqlplus “/ as sysdba” Continue reading

Oracle 12c Logo

ORA-38707: Media recovery is not enabled

Oracle Error: ORA-38707: Media recovery is not enabled

This error usually occurred when Media recovery is not enabled means database is not in archivelog mode. ( i.e. NoArchivelog mode ) After enabling it, you won’t face same error any more.

This tutorial will guide you to enable archivelog. Continue reading

Oracle 12c Logo

How to Configure Flashback Database in oracle 12c

Issue the following steps to configure your database with flashback recovery:

// Ensure flashback recovery settings by querying ‘v$database’ dictionary view, as follows:
SQL> select flashback_on from v$database;
FLASHBACK_ON
——————
NO

// Set db_recovery_file_dest system parameter for flashback recovery file destination:
[oracle@12c ~]$ sqlplus “/ as sysdba”
SQL> alter system set db_recovery_file_dest=’/u01/flash_recovery’ scope=both; Continue reading

Oracle 12c Logo

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

Today, I came across the “ORA-00265: instance recovery required, cannot set ARCHIVELOG mode” while converting database into archive log mode.

This error usually caused when database crashed unfortunately or we shutdown database with the help of database shutdown command as: shutdown abort, startup force mount or shutdown abort. These types of command will required instance recovery in next startup. In short it need clean database startup.

In my case, i am trying to alter database in archive log mode with “startup force mount” command:
SQL> startup force mount
ORACLE instance started.

Total System Global Area 1503199232 bytes
Fixed Size 2288584 bytes
Variable Size 905970744 bytes
Database Buffers 587202560 bytes
Redo Buffers 7737344 bytes
Database mounted.
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

 

// Solution:

SQL> select open_mode from v$database;

OPEN_MODE
——————–
MOUNTED

// Open your database and shutdown it normally, as follow:

SQL> alter database open;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

// Now startup database in mount state:
SQL> startup mount
ORACLE instance started.

Total System Global Area 1503199232 bytes
Fixed Size 2288584 bytes
Variable Size 905970744 bytes
Database Buffers 587202560 bytes
Redo Buffers 7737344 bytes
Database mounted.

// Now issue following to convert database into archivelog mode:
SQL> alter database archivelog;

Database altered.

// Now open database:
SQL> alter database open;

Database altered.

Database has been configured in archivelog without oracle error:ORA-00265, because this time we shutdown our database in normal way.

 

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

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

Thanking you.

Have a easy life ahead.

Oracle 12c Logo

How to configure oracle 12c database in archivelog mode

Connect database as sys user to configure it in archivelog mode.

[oracle@12c ~]$ sqlplus “/ as sysdba”

// Archive log list: Database is in ‘No Archive Mode’.

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/arch
Oldest online log sequence 140
Current log sequence 142

// Shutdown database:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

// Startup your database in mount state:

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1503199232 bytes
Fixed Size 2288584 bytes
Variable Size 905970744 bytes
Database Buffers 587202560 bytes
Redo Buffers 7737344 bytes
Database mounted.

// Configure your database in archivelog mode by issue the following:

SQL> alter database archivelog;

Database altered.

// Alter database in open mode:

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ WRITE

// Database has been successfully configured in archive log mode. Issue the following:

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch
Oldest online log sequence 142
Next log sequence to archive 144
Current log sequence 144

 

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

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

Thanking you.

Have a easy life ahead.

Oracle 12c Logo

ORA-65096: invalid common user or role name and ORA-65049: creation of local user or role is not allowed in CDB$ROOT

This error usually occurs due to we are trying to create user ( common user ) under root container. In oracle 12c there is two type of users: common user and local user.

Common users belongs to CBD’s as well as current and future PDB’s. It means it can performed operation in Container or Pluggable according to Privileges assigned. For more information about common user.

Local users is purely database that belongs to only single PDB. This user may have administrative privileges but this only belongs to that PDB. For more information about local user.

// Consider following example in which i am trying to create common user in container root. Continue reading

Oracle 11g Logo

ORA-00845: MEMORY_TARGET not supported on this system

Today morning, My one of the friend reported me oracle error: “ORA-00845: MEMORY_TARGET not supported on this system” He has installed Oracle 11g R2 & not able to startup newly created database on newly installed oracle.

After diagnosis, I came to know it was the problem with ‘tmpfs’ with low space available.

This error pops up due to we are using Automatic Memory Management (AMM) feature of Oracle 11g R2 on shared memory filesystem (shmfs) which is found less than required.

Solutions for the mentioned error:

  • Increase value of oracle parameter: ‘memory_max_target’ & ‘memory_target’ and try to startup database.
  • If above not working than delete ‘memory_target’ parameter from parameter file & try to startup database.
  • If above not working than increase ‘tmpfs’ space.

Continue reading