Oracle 11g Logo

ORA-01113: file n needs media recovery- ORA-01110: data file n: ‘\SYSTEM01.data4′

After incomplete recovery of my one of the database, I have encounter “ORA-01113: file n needs media recovery” and “ORA-01110: data file n: ‘\SYSTEM01.data4′” while alter database to open mode with reset-logs. After diagnosis, I came to know that, while incomplete recovery oracle database ask for non existing archive log file, in this case provide current log file as an input to “RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;”.

In my case, Workaround for ORA-01113 & ORA-01110 are as follows:

Startup your database in mount state:

 SQL> shutdown immediate;
 ORA-01109: database not open
 Database dismounted.
 ORACLE instance shut down.
SQL> startup mount;
 ORACLE instance started.
 Total System Global Area 7917113344 bytes
 Fixed Size 2198064 bytes
 Variable Size 3976201680 bytes
 Database Buffers 3925868544 bytes
 Redo Buffers 12845056 bytes
 Database mounted.

Following query will help to retrieve name of the current redo log files, i.e. redo log belongs to active group.

SQL> SELECT MEMBER FROM V$LOG G, V$LOGFILE F WHERE G.GROUP# = F.GROUP# AND G.STATUS = 'CURRENT';
 MEMBER
 --------------------------------------------------------------------------------
 /oracle/<oracle_sid>/origlogA/log_g17m1.dbf
 /oracle/<oracle_sid>/mirrlogA/log_g17m2.dbf

Above redo files will help to recover database.

Recover your database with the help of following command, when oracle suggested for non existing archive log with full path (i.e. invalid file) Ignore it and provide above redo log file as an input.
Note: There is no file available on specified path. i.e. <oracle_sid>arch1_66606_758161962.dbf

 SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
 ORA-00279: change 1041939961 generated at 08/11/2014 16:31:04 needed for thread 1
 ORA-00289: suggestion : /oracle/<oracle_sid>/oraarch/<oracle_sid>arch1_66606_758161962.dbf
 ORA-00280: change 1041939961 for thread 1 is in sequence #66606
 Specify log: {=suggested | filename | AUTO | CANCEL}
 /oracle/<oracle_sid>/origlogA/log_g17m1.dbf
 Log applied.
 Media recovery complete.

Media recovery completed successfully, While backup with controlfile OR in case of incomplete recovery, you need to open your database with reset-logs, its recommended.

 SQL> alter database open resetlogs;
 Database altered.
SQL> SQL> select open_mode from v$database;
 OPEN_MODE
 --------------------
 READ WRITE

Database open successfully.

After database recovery, Kindly consider full database backup for future safety.

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

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