Oracle 12c Logo

Introduction to Data Redaction Oracle 12c feature – Data Redaction part-I

Data Redaction is oracle 12c feature, that provides ability to hide your sensitive data in real world.

Best example: While logging to your social site in front of your friends, you wouldn’t hesitate input password because you know while providing your password, It would look like *********. Right? Its nothing but part of security. You will experience the same with the Data Redaction in 12c, lets see how.

In this article, we’ll discuss on Introduction part, Use and benefits of data redaction in real world and scenario simulation to understand the topic.

Introduction:

Data Redaction enables you to change/mask/redact your real data that would be return from database queries issued by applications. Redact your real world data with the help of following types:

  1. Full Redaction:
    With this, you can redact entire column data. Redacted value returned to the querying application depends on the data type of the column. Ex: Character data type would redact to single space or NUMBER data type would redact zero.
  2. Partial Redaction:
    With this, Portion of the data would be redacted. For Ex: In your bank monthly statement email, Your account number might redacted in order to read only last 4 digit and rest might replace with ‘*’ or Big DOT.
  3. Regular Expressions:
    With this, we can redact patterns of data. For Ex: We can use regular expressions to redact land line number OR Email id, those have varying character lengths.
    Note: This type is only suitable with character data types.
  4. Random redaction:
    Each time it generates random data for each application user queries. Depending upon data type of that column.
  5. No Redaction:
    This type is available in order to test your internal operation of your already generated redacted policies, with no effect on the results fetch by application user. Useful to test policies definitions before production environment use.

When application user access data at the same time(at query execution time) oracle database redact real data and display it to user in redacted format. This feature will help you to achieve Industry rules & regulations for security purpose.

Use of Data Redaction:

Whenever you worry about your sensitive data security in order to display to nowise person. Think about data redaction. As we discussed, Redaction is nothing but masking of your real world data, Data redaction enable you to mask the data using different styles available that we discuss above.

Best real world examples:

Bank monthly statement on email OR Call center applications OR applications those are read-only.

Benefits:

Benefits in order to protect your data are as follows:

  • Various redaction methods available.
  • Best fit for those environment where data will be keep on changing.
  • Easy to create data redaction policy and mange it from central location.
  • Policies having wide variety of function conditions based on SYS_CONTEXT values.

These are about the data redaction introduction, its type and benefits, Kindly stay tune with my next article on Configuration of Data Redaction policies.

Oracle 12c Logo

Oracle 12c R1 software and database installation on Oracle Linux 7 64-bit

In this article I am going to cover installation of Oracle 12c Release-1 (i.e. 12.1.0.2.0)(64-bit) on Oracle Linux release 7 (64-bit)

Pre-requisite installation check are as follow:
Swap Size    :  Minimum 2 GB
IP Tables      :  Off
SeLinux        :  permissive

As per pre-requisite list, ensure your swap size with following Linux command, If swap memory don’t meet minimum requirement than increase it. Nice article on increasing swap size is here.

[root@OL712c ~]# free -m
 total used free shared buffers cached
Mem: 2969 2358 611 474 0 998
-/+ buffers/cache: 1359 1610
Swap: 3087 0 3087

Little more information about my environment:

[root@OL712c ~]# uname -a
Linux OL712c 3.8.13-35.3.1.el7uek.x86_64 #2 SMP Wed Jun 25 15:27:43 PDT 2014 x86_64 x86_64 x86_64 GNU/Linux
[root@OL712c ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.0 (Maipo)

Update host file entry:

192.168.17.138     OL712c

Note: OL712c is my server name with associated private IP.

Update hostname entry in “/etc/hostname” file.

OL712c

Append following lines to “/etc/sysctl.conf” file.

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

In order to reflects above changes in current Linux environment, Issue following command:

[root@localhost ~]# /sbin/sysctl -p

Append following lines to “/etc/security/limits.conf” file.

oracle   soft   nofile   1024
oracle   hard   nofile   65536
oracle   soft   nproc    2047
oracle   hard   nproc    16384
oracle   soft   stack    10240
oracle   hard   stack    32768

Stop firewall:

[root@localhost ~]# systemctl stop firewalld

Disable firewall:

[root@localhost ~]# systemctl disable firewalld
rm '/etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service'
rm '/etc/systemd/system/basic.target.wants/firewalld.service'

Set SeLinux Permissive by editing “/etc/selinux/config” file.

SELINUX=permissive

Note:
Take reboot in order to take effect.

Installed following packages with the help of YUM utility: Most of the packages are already installed while OS installation.

yum install binutils* compat-libstdc++* gcc* gcc-c++* glibc* glibc.i686* glibc-devel* ksh* libgcc* libstdc++* libstdc++-devel* libaio* libaio-devel* libXext* libXtst* libX11* libXau* libxcb * libXi* make* sysstat* unixODBC* unixODBC-devel* zlib-devel* -y

Create oracle groups and users:

[root@OL712c ~]# groupadd -g 54321 oinstall
[root@OL712c ~]# groupadd -g 54322 dba
[root@OL712c ~]# groupadd -g 54323 oper
[root@OL712c ~]# useradd -u 54321 -g oinstall -G dba,oper oracle
[root@OL712c ~]# passwd oracle

Create following directories for oracle home:

[root@OL712c ~]# mkdir -p /u01/app/oracle/product/12.1.0/
[root@OL712c ~]# chown -R oracle:oinstall /u01
[root@OL712c ~]# chmod -R 775 /u01

Download oracle 12c R1 from oracle site, here.

On GUI, open terminal prompt as a root user & issue the following command:

[root@OL712c ~]# xhost +
access control disabled, clients can connect from any host
[root@OL712c ~]# su - oracle
[oracle@OL712c ~]$ cd /opt/database/
[oracle@OL712c database]$ ll
total 24
drwxr-xr-x. 4 root root 4096 Nov 29 10:02 install
drwxrwxr-x. 2 root root 58 Jul 7 10:18 response
drwxr-xr-x. 2 root root 33 Jul 7 09:14 rpm
-rwxr-xr-x. 1 root root 8533 Jul 7 09:14 runInstaller
drwxrwxr-x. 2 root root 28 Jul 7 10:18 sshsetup
drwxr-xr-x. 14 root root 4096 Jul 7 10:19 stage
-rwxr-xr-x. 1 root root 500 Feb 6 2013 welcome.html

Run oracle universal installer and follow the steps by step screen shots as below:

[oracle@OL712c database]$ ./runInstaller

Un-check security updates check box and continue.

Oracle 12c on Oracle Linux 7 - Configure Security Updates

Oracle 12c on Oracle Linux 7 – Configure Security Updates

 

Select Installation Option, In my case i am creating as well as configuring database at a time:

Installation of Oracle 12c on Oracle Linux 7 - Select Installation Option

Installation of Oracle 12c on Oracle Linux 7 – Select Installation Option

 

Provide system class information, Select Server class if you are installing oracle on server, in my case I am using VM on my personal lappy.

Installation of Oracle 12c on Oracle Linux 7 - System Class

Installation of Oracle 12c on Oracle Linux 7 – System Class

 

Provide strong administrative password with pluggable database name, and continue.

Installation of Oracle 12c on Oracle Linux 7 - Typical Install Configuration

Installation of Oracle 12c on Oracle Linux 7 – Typical Install Configuration

Installation of Oracle 12c on Oracle Linux 7 - Create Inventory

Installation of Oracle 12c on Oracle Linux 7 – Create Inventory

Installation of Oracle 12c on Oracle Linux 7 - Perform Pre-requisite Check

Installation of Oracle 12c on Oracle Linux 7 – Perform Pre-requisite Check

 

All pre-requisite checks are succeeded in my case, It is recommended to satisfied all minimum requirements:

Installation of Oracle 12c on Oracle Linux 7 - Perform Pre-requisite Check

Installation of Oracle 12c on Oracle Linux 7 – Perform Pre-requisite Check

 

Ensure summary and continue with installation.

Installation of Oracle 12c on Oracle Linux 7 - summary

Installation of Oracle 12c on Oracle Linux 7 – summary

Installation of Oracle 12c on Oracle Linux 7 - Install Product

Installation of Oracle 12c on Oracle Linux 7 – Install Product

 

Execute following scripts as root users.

Installation of Oracle 12c on Oracle Linux 7 - Execute Configuration Script

Installation of Oracle 12c on Oracle Linux 7 – Execute Configuration Script

Oracle script

Oracle script

 

Oracle software installation is completed and now running database configuration assistant for database creation.

Installation of Oracle 12c on Oracle Linux 7 - Database Configuration Assistant

Installation of Oracle 12c on Oracle Linux 7 – Database Configuration Assistant

 

Unlock user by clicking password management button, or continue.

Installation of Oracle 12c on Oracle Linux 7 - configuration assistant

Installation of Oracle 12c on Oracle Linux 7 – configuration assistant

 

Oracle 12c installation on Oracle Linux 7 is successful.

Installation of Oracle 12c on Oracle Linux 7

Installation of Oracle 12c on Oracle Linux 7

 

Now, Export oracle environment variable by “Oracel” user to access sql prompt as below:

[oracle@OL712c ~]$ export ORACLE_SID=orcl
[oracle@OL712c ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1/
[oracle@OL712c ~]$ export PATH=$PATH:/u01/app/oracle/product/12.1.0/dbhome_1/bin/

Access SQL prompt as below:

[oracle@OL712c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 1 04:47:44 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 2923920 bytes
Variable Size 838861424 bytes
Database Buffers 402653184 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORCL READ WRITE

If you wish to start your databases on every reboot then make following changes in “/etc/oratab” file for all databases.

orcl:/u01/app/oracle/product/12.1.0/dbhome_1:Y

 

Congratulations!! You have successfully install oracle 12c on oracle Linux 7 64-bit OS.

 

Related article on Oracle 12c installation on Oracle Linux 5 & 6 as below:

Oracle 12c installation on Oracle Linux release-6 (64-bit)

Oracle 12c installation on Oracle Linux release-5 (64-bit)

 

System.Data.SqlClient.SqlError: The media loaded on X is formatted to support 1 media families, but 2 media families are expected according to the backup device specification

My one of the client wanted to full backup of his SQL Server database but getting failed with below mentioned error:

Error:

System.Data.SqlClient.SqlError: The media loaded on "H:\*******20141014.bak" is formatted to support 1 media families, but 2 media families are expected according to the backup device specification. (Microsoft.SqlServer.Smo)

Screen shot: You can see two backup media locations has been mentioned for full database backup.

System.Data.SqlClient.SqlError: The media loaded on X is formatted to support 1 media families, but 2 media families are expected according to the backup device specification. (Microsoft.SqlServer.Smo)

Root Cause:

When we add more than one backup location, than we are asking SQL Server to take backup on more than one backup location, means backup would be stripped/divide over the backup locations. You need both the stripped backup piece at the time of restore.

Solution:

To avoid above error, just replace your new backup location with listed backup locations. Means remove all old entry and add your new backup location. It will work.

Stay Tune. 🙂

Oracle Linux logo

Oracle Linux 7: rpmdb open failed

While installation of oracle database 12c release 1 on oracle Linux 7, I tried installation of below mentioned packages as a part of oracle database installation pre-requisite but getting failed with the message “Another app is currently holding the yum lock;”.

[root@localhost ~]# yum install binutils* compat-libstdc++* gcc* gcc-c++* glibc* glibc.i686* glibc-devel* ksh* libgcc* libstdc++* libstdc++-devel* libaio* libaio-devel* libXext* libXtst* libX11* libXau* libxcb * libXi* make* sysstat* unixODBC* unixODBC-devel* zlib-devel* -y
Loaded plugins: langpacks
Existing lock /var/run/yum.pid: another copy is running as pid 2386.
Another app is currently holding the yum lock; waiting for it to exit...
 The other application is: PackageKit
 Memory : 63 M RSS (464 MB VSZ)
 Started: Fri Nov 28 06:31:22 2014 - 12:00 ago
 State : Running, pid: 2386
Another app is currently holding the yum lock; waiting for it to exit...
 The other application is: PackageKit
 Memory : 63 M RSS (463 MB VSZ)
 Started: Fri Nov 28 06:31:22 2014 - 12:02 ago
 State : Uninterruptible, pid: 2386

After waiting sufficient amount of time, I decided to kill precess id. ( i.e. 2386 )

[root@localhost ~]# kill -9 2386

Process ID successfully killed but again mentioned packages installation failed with new Error: rpmdb open failed.

[root@localhost ~]# yum install binutils* compat-libstdc++* gcc* gcc-c++* glibc* glibc.i686* glibc-devel* ksh* libgcc* libstdc++* libstdc++-devel* libaio* libaio-devel* libXext* libXtst* libX11* libXau* libxcb * libXi* make* sysstat* unixODBC* unixODBC-devel* zlib-devel* -y
error: rpmdb: BDB0113 Thread/process 2386/139857582520128 failed: BDB1507 Thread died in Berkeley DB library
error: db5 error(-30973) from dbenv->failchk: BDB0087 DB_RUNRECOVERY: Fatal error, run database recovery
error: cannot open Packages index using db5 - (-30973)
error: cannot open Packages database in /var/lib/rpm
CRITICAL:yum.main:
Error: rpmdb open failed

Root Cause:

I have issued wrong command at wrong time, I have forcefully killed/aborted/failed active package installation process with process id: 2386, resulting: RPM database corrupted.
Solution is simple, just need to delete and rebuild RPM database.

Solution:
Issue the following command as a root user:

Confirm that your RPM database corrupted with following RPM command, It will prompt you same error message mentioned above.

[root@localhost ~]# rpm -qa | sort

On safer side, backup your RPM database before rebuild it:

[root@localhost ~]# cp -r /var/lib/rpm /var/tmp/rpm-backup

Following remove command will help you to remove the RPM lock files:

[root@localhost ~]# rm -fr /var/lib/rpm/__db*

Rebuild RPM database with following RPM command:

[root@localhost ~]# rpm -vv --rebuilddb

Kindly confirm that your RPM database rebuild successfully with following:

[root@localhost ~]# rpm -qa | sort

Clean your yum:

[root@localhost ~]# yum clean all
Loaded plugins: langpacks
Cleaning repos: ol7_UEKR3 ol7_latest
Cleaning up everything

After following all above steps, yum have been installed all packages smoothly.

Stay Tune. 🙂

Oracle 11g Logo

ORA-01149:cannot shutdown-file 1 has online backup set OR ORA-10873:file 1 needs to be either taken out of backup mode or media recovered

Oracle Error: While shutdown your database you may encounter following oracle error.

SQL> shutdown immediate;
ORA-01149: cannot shutdown – file 1 has online backup set
ORA-01110: data file 1: ‘/u01/app/oracle/oradata/RTS/system01.dbf’

Cause:

If you try to shutdown database when database is in hot backup mode, you will encountered above mentioned error. Kindly verify with your team mates with hot backup activity, Oracle will not allow you to shutdown your database in above scenario, i.e. shutdown, shutdown immediate, shutdown transactional OR startup force will be failed with ORA-01149 oracle error.

Root cause detection:

Following query will help you to understand which oracle tablespace datafile is in hot backup mode.

SQL> select a.tablespace_name, b.status from dba_data_files a, v$backup b where a.file_id=b.file# order by tablespace_name;
TABLESPACE_NAME                STATUS
------------------------------ ------------------
SYSAUX                         ACTIVE
SYSTEM                         ACTIVE
UNDOTBS1                       ACTIVE
USERS                          ACTIVE

Status of the all datafile seems to be active. i.e. hot backup mode.

Solution:
Issue following SQL command in order to move out database from hot backup mode:

SQL> alter database end backup;
Database altered.

Now you can gracefully shutdown your database.

 

Cause: ORA-10873

In case you force fully trying to shutdown database with “shutdown abort” OR instance crashes for some reason OR database have not been shutdown gracefully than you would encountered “ORA-10873” error while next startup of your database.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1469792256 bytes
Fixed Size 2213456 bytes
Variable Size 1040189872 bytes
Database Buffers 419430400 bytes
Redo Buffers 7958528 bytes
Database mounted.
ORA-10873: file 1 needs to be either taken out of backup mode or media recovered
ORA-01110: data file 1: '/u01/app/oracle/oradata/RTS/system01.dbf'

Solution:

Bring out your database from hot backup mode with the help of following SQL:

SQL> alter database end backup;
Database altered.

OR, you can individually take out tablespaces from hot backup mode:

SQL> alter tablespace system end backup;
Tablespace altered.

Open database:

SQL> alter database open;
Database altered.

Your database opened in READ WRITE mode.

Stay Tune. 🙂

Oracle 11g Logo

Oracle 11g Manual Online Hot Backup

In this article steps by step approach to perform online hot backup with database in open mode.

Note: It is assumed that your database already in ARCHIVELOG mode, If not than follow my one of the article to convert your database in archivelog mode.

For manual online hot backup, follow the steps mentioned below. I have simulated mentioned scenario on my test database, i.e. RTS.

Step 1>>

Verify your database is in ARCHIVELOG mode OR not.

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 88
 Next log sequence to archive 90
 Current log sequence 90
 Database is in ARCHIVELOG mode.

Step 2>>

List down the all oracle data files which you have to backup, with the help of following SQL.

 SQL> select file_id,file_name from dba_data_files;
 FILE_ID    FILE_NAME
 ---------  ----------------------------------------------
 4          /u01/app/oracle/oradata/RTS/users01.dbf
 3          /u01/app/oracle/oradata/RTS/undotbs01.dbf
 2          /u01/app/oracle/oradata/RTS/sysaux01.dbf
 1          /u01/app/oracle/oradata/RTS/system01.dbf

Step 3>>

We need to know current online log sequence number at this point, Plus all log sequence generated during backup. Because we need these archive logs in order to restore database.

 SQL> select group#, sequence#, status from v$log;
 GROUP#     SEQUENCE#  STATUS
 ---------- ---------- ----------------
 1          88         INACTIVE
 2          89         INACTIVE
 3          90         CURRENT

In my case, CURRENT log sequence number : 90, GROUP# : 3

Step 4>>

To start with hot backup, all the online datafile status should be in NOT ACTIVE mode in V$BACKUP dictionary view, that displays the backup status of all online datafiles.

select * from v$backup;
     FILE# STATUS             CHANGE#    TIME
---------- ------------------ ---------- ---------
         1 NOT ACTIVE         0
         2 NOT ACTIVE         0
         3 NOT ACTIVE         0
         4 NOT ACTIVE         0

Put your database in hot backup mode with the help of following SQL:

 SQL> alter database begin backup;
 Database altered.

Verify status of all online datafiles after begin backup mode:

SQL> Select * from v$backup;
     FILE# STATUS             CHANGE#    TIME
---------- ------------------ ---------- ---------
         1 ACTIVE             1114633    12-JUL-17
         2 ACTIVE             1114633    12-JUL-17
         3 ACTIVE             1114633    12-JUL-17
         4 ACTIVE             1114633    12-JUL-17

Step 5>>

Create backup directory in order to copy backup files to backup directory.

 [oracle@PR ~]$ mkdir -p /u01/bkup/manual_online_hot
 [oracle@PR ~]$ cd /u01/bkup/manual_online_hot
 [oracle@PR manual_online_hot]$ pwd
 /u01/bkup/manual_online_hot

It is recommended to backup your pfile or spfile by coping it to backup location.

[oracle@PR ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@PR dbs]$ cp initRTS.ora /u01/bkup/manual_online_hot/
[oracle@PR dbs]$ cp spfileRTS.ora /u01/bkup/manual_online_hot/

Step 6>>

Copy all the files (i.e. .DBF) from the database directory “/u01/app/oracle/oradata/RTS” to backup directory “/u01/bkup/manual_online_hot”.

 [oracle@PR ~]$ cd /u01/app/oracle/oradata/RTS
 [oracle@PR RTS]$ cp *.dbf /u01/bkup/manual_online_hot

Step 7>>

Take your database out from hot backup mode:

 SQL> alter database end backup;
 Database altered.

Step 8>>

Verify current log sequence number with the help of same SQL mentioned in step-3.

 SQL> select group#, sequence#, status from v$log;
 GROUP#     SEQUENCE#  STATUS
 ---------- ---------- ----------------
 1          88         INACTIVE
 2          89         INACTIVE
 3          90         CURRENT

We need the earlier log file that we identified in step-3 & all log files generated during the database backup upto the current log file.

Note: In our case, As you know this is an test environment not production database. current log file before and after backup remain same, i.e. 90. But in case of production system, it may vary and generate more log files during backup.

Step 9>>

Forcefully we need to switch the logfile in order to archive current log sequence number. i.e. 90.

 SQL> alter system switch logfile;
 System altered.
 SQL> select group#, sequence#, status from v$log;
 GROUP#     SEQUENCE#  STATUS
 ---------- ---------- ----------------
 1          91         CURRENT
 2          89         INACTIVE
 3          90         ACTIVE

Now, Current log sequence number is 91, and log sequence number will be archive to archive log location. In my case it is FRA: Flash Recovery Area.

Step 10>>

Verify log sequence number have been archived to archive log location with the help of following SQL:

SQL> select SEQUENCE#,ARCHIVED,STATUS from v$archived_log where SEQUENCE#=90;
 SEQUENCE#  ARC S
 ---------- --- -
 90         YES A

Archive log sequence 90 has archived. Some time we need to wait for ARCH background process to complete copy the last online redo log file to the archive log directory.

Step 11>>

Now, Copy all archived logs (i.e. log sequence number noted in step-3 and all archived logs generated during backup) from archived log location (i.e. FRA) to the backup location.
In our case it was only log sequence 90.

[oracle@PR ~]$ cd /home/oracle/archdir
 [oracle@PR archdir]$ cp 1_90_910140016.dbf /u01/bkup/manual_online_hot
 OR
 [oracle@PR archdir]$ cp * /u01/bkup/manual_online_hot

Step 12>>

Backup the database control file:

SQL> alter database backup controlfile to '/u01/bkup/manual_online_hot/hot_bkup_control.ctl';
Database altered.

Cross verify all backup files are in place(i.e. datafiles, controlfile and archive log files @backup location in order restore database.

Congratulations!! Manual Online hot backup successfully completed.

To restore and recover above online hot backup, kindly visit my next article on Restore Oracle hot backup

Stay Tune. 🙂

Oracle 11g Logo

Oracle 11g manual offline cold backup in Linux operating system

Executing offline cold backups in oracle is bit easy, Offline cold backup is nothing but bring your oracle database to shutdown and copy(backup) all database physical files to backup location manually. like data file, control file, log file, etc

Note: This article assumes you don’t use any tablespaces with ASM instance. For ASM instance, I strongly recommend to kindly perform your database backup with RMAN utility. RMAN: Recovery Manager.

For manual offline backup, follow the steps mentioned below. I have simulated mentioned scenario on my test database, i.e. RTS.

Step 1>>

List down the oracle data files which you have to backup, following SQL command will help you to determine file name and its location:

SQL> select file_name from dba_data_files;
 FILE_NAME
 ----------------------------------------------------------------------
 /u01/app/oracle/oradata/RTS/users01.dbf
 /u01/app/oracle/oradata/RTS/undotbs01.dbf
 /u01/app/oracle/oradata/RTS/sysaux01.dbf
 /u01/app/oracle/oradata/RTS/system01.dbf

Step 2>>

List down all online redologs and its location with following SQL command:

 SQL> select member from v$logfile;
 MEMBER
 ----------------------------------------------------------------------
 /u01/app/oracle/oradata/RTS/redo03.log
 /u01/app/oracle/oradata/RTS/redo02.log
 /u01/app/oracle/oradata/RTS/redo01.log
 3 rows selected.

Step 3>>

List down all control files and its location by following:

SQL> select name from v$controlfile;
 NAME
 ----------------------------------------------------------------------
 /u01/app/oracle/oradata/RTS/control01.ctl
 /u01/app/oracle/flash_recovery_area/RTS/control02.ctl

We have gathered all the physical file list that we’ll need for backup.

Step 4>>

Create directory on your system, In this case I am using “manual_offline_cold” for backup location, as you know this is my test environment.
Note: It’s recommended to backup this files on Tape OR External hard drive, as per your convenience other than same server directory.

 [oracle@PR ~]$ mkdir -p /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cd /u01/bkup/manual_offline_cold
 [oracle@PR manual_offline_cold]$ pwd
 /u01/bkup/manual_offline_cold

Note: It’s recommended to backup this files on Tape OR External hard drive, as per your convenience other than same server directory.

Step 5>>

Grace fully shutdown your database with following command:

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

Step 6>>

Now copy all the physical file that you have found in step number 1,2 and 3.

 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/users01.dbf /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/undotbs01.dbf /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/sysaux01.dbf /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/system01.dbf /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/redo03.log /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/redo02.log /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/redo01.log /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/control01.ctl /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/flash_recovery_area/RTS/control02.ctl /u01/bkup/manual_offline_cold

Once copy process complete, kindly verify the backup directory with all copied files.

 [oracle@PR ~]$ cd /u01/bkup/manual_offline_cold
 [oracle@PR manual_offline_cold]$ ll
 total 1376468
 -rw-r----- 1 oracle oinstall 10043392 Nov 15 23:02 control01.ctl
 -rw-r----- 1 oracle oinstall 10043392 Nov 15 23:02 control02.ctl
 -rw-r----- 1 oracle oinstall 52429312 Nov 15 23:02 redo01.log
 -rw-r----- 1 oracle oinstall 52429312 Nov 15 23:02 redo02.log
 -rw-r----- 1 oracle oinstall 52429312 Nov 15 23:01 redo03.log
 -rw-r----- 1 oracle oinstall 492838912 Nov 15 22:59 sysaux01.dbf
 -rw-r----- 1 oracle oinstall 702554112 Nov 15 23:00 system01.dbf
 -rw-r----- 1 oracle oinstall 31465472 Nov 15 22:59 undotbs01.dbf
 -rw-r----- 1 oracle oinstall 5251072 Nov 15 22:58 users01.dbf

There is no need to backup temporary datafile those are associated with temporary tablespace, because technically we don’t use to restore database.

You can compress backup files with any compression utility to manage your disk space well for log period of retention.

Step 7>>
Start your database:

 SQL> startup
 ORACLE instance started.
 Total System Global Area 1469792256 bytes
 Fixed Size 2213456 bytes
 Variable Size 1040189872 bytes
 Database Buffers 419430400 bytes
 Redo Buffers 7958528 bytes
 Database mounted.
 Database opened.

Congratulations!! Manual offline backup successfully completed.

Stay Tune. 🙂

Oracle 11g Logo

Oracle 11g Physical standby data Guard Failover steps – Active Data Guard Part-V

Oracle 11g Physical standby data Guard Failover:

In case of worst situation with data guard primary database, or not available for production than we can activated standby database as a primary production database.

Previously, we have covered: how to apply primary database redo information to standby database while standby database is in read only mode – Active Data guard feature – Data Guard Part-IV

Lets consider above scenario and bring standby database up as a primary database:
Verify database name its open mode and its role from following SQL command:

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS MOUNTED PHYSICAL STANDBY

Following sort of SQL command will help to bring up standby as primary:

SQL> alter database recover managed standby database finish;
Database altered.
SQL> alter database activate standby database;
Database altered.

Managed recovery process has been stopped between primary and standby database and standby becomes primary database.

Bounce your database and verify database name its open mode and its role:

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1469792256 bytes
Fixed Size 2213456 bytes
Variable Size 905972144 bytes
Database Buffers 553648128 bytes
Redo Buffers 7958528 bytes
Database mounted.
Database opened.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS READ WRITE PRIMARY

Note:

Now your old standby database is become primary database, it is highly recommended to consider immediate full backup of primary database.

After primary database available, you need to switch role between standby to primary database:

Stay Tune. 🙂

Script to find out total number of oracle schema objects and its size

Following script help you to find out available schema’s along with total number of objects and its size ( in MB )

SQL> set pages 999
SQL> col "size MB" format 999,999,999
SQL> col "Objects" format 999,999,999
SQL> select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1;

O/P:

Owner                          Objects      size MB
------------------------------ ------------ ------------
PRODAPP                        32,527       39,433
SYS                            30,924       5,521
SYSTEM                         604          4,468
AEX_030200                     2,406        157
XPRDB                          844          129
SYSMAN                         3,491        123
GHSYS                          1,019        29
TRXSYS                         366          19
...

Stay Tune. 🙂

Script to find out datafiles with highest Input-Output activity

Following script will find out all datafiles OR first 5 datafiles with highest Input-Output in terms of physical reads and write on datafiles along with read/write time.

SQL> col name format a50
SQL> set linesize 200
SQL> select * from ( select name,phyrds, phywrts, readtim, writetim
from v$filestat a, v$datafile b where a.file#=b.file#
order by readtim desc) where rownum < 6;

O/P:

NAME                                    PHYRDS     PHYWRTS  READTIM   WRITETIM
--------------------------------------- ---------  -------- --------  ---------
/DATA/database/prod/prod_app_data.dbf   958846     231710   55197     12248
/DATA/database/prod/system01.dbf        1113495    69047    46798     1677
/DATA/database/prod/prod_app_lob.dbf    11226086   57018    44491     469
/DATA/database/prod/prod_app_index.dbf  389124     134121   44330     6719
/DATA/database/prod/sysaux01.dbf        396043     115628   29089     5735

Stay Tune 🙂

Oracle 11g Logo

how to apply primary database redo information to standby database while standby database is in read only mode – Active Data guard feature – Active Data Guard Part-IV

Active Data Guard

Oracle 11g introduced Active Data Guard new Feature, in this standby database is allowed to be open in read only mode for query access and managed recovery also started and standby database is in sync/up-to-date with primary database.
Note:
You need licence to get benefit of this feature.

Previously, we covered How to open physical standby database in read only mode

Let me demonstrate for you to how to do it:

Verify database name, its open mode and database role by following command:

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS MOUNTED PHYSICAL STANDBY

Standby database is in mount state.

Following sort of command will use to enable active data guard feature at standby database:

Gracefully shutdown your standby database and startup with 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 1469792256 bytes
Fixed Size 2213456 bytes
Variable Size 905972144 bytes
Database Buffers 553648128 bytes
Redo Buffers 7958528 bytes
Database mounted.

Open standby database is in read only mode:

SQL> alter database open read only;
Database altered.

Following command will help you to enable active dataguard feature.

SQL> alter database recover managed standby database disconnect from session;
Database altered.

Again verify database name, its open mode and database role by following command, Your standby database is in READ ONLY WITH APPLY. It means redo apply process in place and running.

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS READ ONLY WITH APPLY PHYSICAL STANDBY

With the help of above, we can open standby database in read only mode for query access along with redo apply.

Stay Tune. 🙂

Oracle 11g Logo

How to open physical standby database in read only mode – Active Data Guard Part -III

One of the benefit of Physical standby data guard is to use standby database for query access for offloading and reporting purpose by making standby database as a read only mode. So that we can efficiently use or manage primary database resources for core purpose.

Note:
When physical standby database in read only mode, archive log transmission from primary database to standby database continues, but managed recovery is stopped. Once your database back to mount state than managed recovery will be start.

Previously, We covered procedure for Oracle 11g Data Guard Switchover and Switchback

Let me demonstrate the scenario for you, In this we will open our standby database in read-only mode for query access.

Verify database name, its open mode and database role by following command:

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS MOUNTED PHYSICAL STANDBY

Database is in mount state.

To make it online, gracefully shutdown the database and open in mount state as follows:

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1469792256 bytes
Fixed Size 2213456 bytes
Variable Size 905972144 bytes
Database Buffers 553648128 bytes
Redo Buffers 7958528 bytes
Database mounted.

Issue following command to make database in read only mode:

SQL> alter database open read only;
Database altered.

Again verify database name, its open mode and database role, your database is in READ ONLY mode.

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS READ ONLY PHYSICAL STANDBY

As we discussed, while standby database is in READ ONLY mode, archive log transfer from primary to standby database is continues, but managed recovery has been stopped.

To simulate environment, Manually i have generated archive logs at primary database and it automatically transferred to standby database while opened in READ ONLY mode.
Archive logs details on standby before generating multiple manual archive logs, Current log sequence: 61

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 59
Next log sequence to archive 0
Current log sequence 61

Archive logs details on standby after generating manual archive logs, Current log sequence: 66

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 61
Next log sequence to archive 0
Current log sequence 66

To get back to managed recovery, gracefully shutdown standby database, Startup with mount state and finally start managed recovery with the help of following sort of commands:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1469792256 bytes
Fixed Size 2213456 bytes
Variable Size 905972144 bytes
Database Buffers 553648128 bytes
Redo Buffers 7958528 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.

Verify database name, its open mode and database role by following command, Its get back to mount state.

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS MOUNTED PHYSICAL STANDBY

With above method we can open standby database in read only mode and get back to its managed recovery.

Kindly tune with me to know how to how to apply primary database redo information to standby database while database is in read only mode. Its new feature introduced in Oracle 11g.

 

Stay tune. 🙂

Oracle 11g Logo

Oracle 11g Data Guard Switchover and Switchback – Active Data Guard Part-II

In my Previous article we have covered How to configured oracle 11g Data Guard on CentOS 6.5, Now we will look into switchover and switch back activity:

After configuring data guard, databases would be either primary and standby database role, and we can altered these roles without loss of data or without resetting logs. called switchover and switch back.

Switchover:

Before performing switchover, kindly verify the state of data guard on both the instances by following SQL queries:

 SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
 SQL> SELECT sequence#, first_time, next_time, applied
 FROM v$archived_log
 ORDER BY sequence#;
 ...
 ...
 59 08-OCT-14 08-OCT-14 YES
 60 08-OCT-14 08-OCT-14 YES
 61 08-OCT-14 08-OCT-14 YES
 SQL> select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';
 DEST_NAME
 --------------------------------------------------------------------------------
 STATUS ERROR
 --------- -----------------------------------------------------------------
 LOG_ARCHIVE_DEST_2
 VALID
 SQL> select message from v$dataguard_status;

Note: This command will give you appropriate message about the data guard current status.

After getting confirmation on data guard smooth activity, we can instantiate switchover activity by issuing following sort of commands:

On Primary database:

Step-1>>
Connect to Primary database and convert primary database to standby.

 [oracle@pr ~]$ sqlplus / as sysdba
 SQL> alter database commit to switchover to standby;
 Database altered.

Step-2>>
Shutdown primary database:

SQL> shutdown immediate;

Step-3>>

Startup nomount old primary database as new standby database:

 SQL> startup nomount
 ORACLE instance started.
 Total System Global Area 972898304 bytes
 Fixed Size 2219272 bytes
 Variable Size 805307128 bytes
 Database Buffers 159383552 bytes
 Redo Buffers 5988352 bytes
 SQL> alter database mount standby database;
 Database altered.
 SQL> alter database recover managed standby database disconnect from session;
 Database altered.

Verify database role on old primary database:

 SQL> select name,open_mode,database_role from v$database;
 NAME OPEN_MODE DATABASE_ROLE
 --------- -------------------- ----------------
 RTS MOUNTED PHYSICAL STANDBY

 

On Standby database:

Step-4>>
On original standby database, Convert old standby database to primary database:

[oracle@dr ~]$ sqlplus / as sysdba

Step-5>>

Convert old standby database as primary and shutdown database:

 SQL> alter database commit to switchover to primary;
 Database altered.
 SQL> shutdown immediate;
 ORA-01109: database not open
 Database dismounted.
 ORACLE instance shut down.

Step-6>>

Startup old standby database as primary database:

 SQL> startup
 ORACLE instance started.
 Total System Global Area 972898304 bytes
 Fixed Size 2219272 bytes
 Variable Size 717226744 bytes
 Database Buffers 247463936 bytes
 Redo Buffers 5988352 bytes
 Database mounted.
 Database opened.

Verify database role on old standby database:

 SQL> select name,open_mode,database_role from v$database;
 NAME OPEN_MODE DATABASE_ROLE
 --------- -------------------- ----------------
 RTS READ WRITE PRIMARY

Switch over activity have completed successfully, our old primary database has become standby and old standby database has become primary database.

Note

To test above switchover activity, generate multiple archive logs on primary database and verify those archive logs being transferred on standby database.

 

Switchback:
To switchback, you have to follow same above mentioned 6 steps.

Stay Tune. 🙂

Oracle 11g Logo

ORA-16038-log 4 sequence# 10702 cannot be archived-ORA-19809: limit exceeded for recovery files-ORA-19804: cannot reclaim 52428800 bytes disk space from 107374182400 limit

My one of the RAC RMAN backup failed with above mentioned oracle error:

RMAN script and its error logs are as follows:

RMAN> #Script to run Daily Incremental Backup...
2> #Created 17 June 2014
3> #Author : Jignesh Jethwa
4> #Version 1.0
5>
6> run
7> {
8> backup as compressed backupset incremental level 1 database plus archivelog;
9> delete noprompt archivelog until time 'sysdate -1' backed up 1 times to device type disk;
10>
11> #Delete Obsolte Backups
12> delete noprompt force obsolete;
13> }
14>
Starting backup at 13-SEP-14
ORACLE error from target database:
ORA-16038: log 4 sequence# 10702 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 4 thread 2: '+DATA/****/onlinelog/group_4.270.818354365'
ORA-00312: online log 4 thread 2: '+DATA/****/onlinelog/group_4.271.818354367'
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=953 instance=Instance_name1 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=10701 RECID=18396 STAMP=858207615
channel ORA_DISK_1: starting piece 1 at 13-SEP-14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 09/13/2014 23:00:07
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 107374182400 limit
Recovery Manager complete.

According to above error logs, Size allocated for database recovery file destination is exceed with its current limit.

Work around for the above mentioned error is very simple, Re-size ‘db_recovery_file_dest_size’ oracle parameter and instantiate backup again, It will work.

SQL> show parameter db_recovery_file_dest;
SQL> show parameter db_recovery_file_dest_size;

Note:

If you are on RAC environment, than you have to change same parameter on both the instances, otherwise another instance will not come up after next reboot.

Stay Tune. 🙂

Bash Code Injection Vulnerability CVE-2014-7169 Affecting Linux & OS X Systems

As reported in mainstream media, Red Hat has been made aware of a vulnerability affecting all versions of the bash package as shipped with Red Hat products. This vulnerability CVE-2014-6271 could allow for arbitrary code execution. Certain services & applications allow remote unauthenticated attackers to provide environment variables, allowing them to exploit this issue.

GNU Bash through 4.3 bash43-025 processes trailing strings after certain malformed function definitions in the values of environment variables, which allows remote attackers to write to files or possibly have unknown other impact via a crafted environment, as demonstrated by vectors involving the ForceCommand feature in OpenSSH sshd, the mod_cgi & mod_cgid modules in the Apache HTTP Server, scripts executed by unspecified DHCP clients, & other situations in which setting the environment occurs across a privilege boundary from Bash execution.
NOTE: this vulnerability exists because of an incomplete fix for CVE-2014-6271.

Test your bash version is vulnerable or not with following code, If O/P of the command looks like below than your bash version is vulnerable.

 # env x='() { :;}; echo vulnerable' bash -c "echo this is a test"
 vulnerable
 this is a test

Screenshot:

Bash Code Injection Vulnerability CVE-2014-7169

Bash Code Injection Vulnerability CVE-2014-7169

Temporary Solution/Patch:
Upgrade your Bash using the yum command with ssh:

# yum upgrade bash

Congratulations!!, your bash is patched and now secured from hackers. Now rerun above code and check out O/P, It should be looks same like below:

 $ env x='() { :;}; echo vulnerable' bash -c "echo this is a test"
 bash: warning: x: ignoring function definition attempt
 bash: error importing function definition for `x'
 this is a test

Screenshot:

Bash Code Injection Vulnerability CVE-2014-7169

Bash Code Injection Vulnerability CVE-2014-7169

If you still facing same vulnerability with same O/P, than your system has been compromised with bug. Contact Red Hat Security response team.

Important Notes:

  • Your system need to be restart in order to use bash package.
  • If you face any kind of difficulties to resolve it, than raise ticket to RedHat support.
  • This patch is temporary fix, Red Hat security response team will release total fix of this vulnerability very soon. Once total fix will release I’ll update you though my post.

References:
https://access.redhat.com/articles/1200223
http://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2014-6271
https://bugzilla.redhat.com/show_bug.cgi?id=1141597

Stay Tune. 🙂

Oracle 11g Logo

Steps to configure Oracle 11g Data Guard Physical Standby – Active Data Guard Part-I

Here in this article I am going to cover steps by step approach to configure Oracle 11g Data Guard Physical Standby.

In my case, Ingredients to simulate Physical Standby data guard environment are as below:

  • 2 VM’s, Primary and DR with enough CPU and RAM in order to run oracle database.
  • Primary server configuration
    • CentOS 6.5
    • Server name: pr
    • IP: 192.168.17.131
    • Oracle 11g software plus oracle instance.
    • Oracle SID/Global_name: RTS
    • Oracle db_unique_name: RTS
  • Secondary server configuration
    • CentOS 6.5
    • Server name: dr
    • IP: 192.168.17.132
    • Oracle 11g software only.
    • Oracle SID/Global name: RTS
    • Oracle db_unique_name: RTSDR

Note:

  • Oracle version on Primary and secondary should be identical. i.e. In my case it’s 11.2.0.1.0
  • db_unique_name on standby and primary database should be different in order to used in DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter.
  • Primary and DR server should ping each other by IP as well as its server name. In order to ping with its server name, edit /etc/hosts file accordingly.

 

Primary Server Configurations:

<Step – 1 >

Enable Archive log:
Primary database is in No Archive Mode, Enable it:

 SQL> archive log list
 Database log mode No Archive Mode
 Automatic archival Disabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 1
 Current log sequence 3
 SQL> SHUTDOWN IMMEDIATE;
 SQL> STARTUP MOUNT;
 SQL> ALTER DATABASE ARCHIVELOG;
 SQL> ALTER DATABASE OPEN;
 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 1
 Next log sequence to archive 3
 Current log sequence 3

<Step – 2 >

Enable force logging with the help of following SQL command:

SQL> ALTER DATABASE FORCE LOGGING;

<Step – 3 >

Verify initialization parameters db_name and db_unique_name on primary, In my case those are set to: RTS

 SQL> show parameter db_name
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 db_name string RTS
 SQL> show parameter db_unique_name
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 db_unique_name string RTS

<Step – 4 >

Issue following command to set LOG_ARCHIVE_CONFIG parameter for data guard config.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(RTS,RTSDR)';

Ensure your above changes as below:

 SQL> show parameter LOG_ARCHIVE_CONFIG
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 log_archive_config string DG_CONFIG=(RTS,RTSDR)

<Step – 5 >

Issue following to set LOG_ARCHIVE_DEST_2, In my case i have used flash recovery area for remote archive log destination.

Note: Service and DB_UNIQUE_NAME reference the remote standby location.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=RTSDR NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RTSDR';

Ensure your above changes as below:

SQL> show parameter LOG_ARCHIVE_DEST_2
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 log_archive_dest_2 string SERVICE=rtsdr NOAFFIRM ASYNC V
 ALID_FOR=(ONLINE_LOGFILES,PRIM
 ARY_ROLE) DB_UNIQUE_NAME=rtsdr

Enable LOG_ARCHIVE_DEST_2:

SQL> alter system set log_archive_dest_state_2=enable;
SQL> show parameter log_archive_dest_state_2
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 log_archive_dest_state_2 string ENABLE

<Step – 6 > 

Set log_archive_format parameter as below:

SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
SQL> show parameter log_archive_format
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 log_archive_format string %t_%s_%r.arc

Set log_archive_max_processes parameter to 30:

SQL> alter system set log_archive_max_processes=30;
SQL> show parameter log_archive_max_processes
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 log_archive_max_processes integer 30

Set remote_login_passwordfile parameter to exclusive:

 SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
 SQL> show parameter remote_login_passwordfile
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 remote_login_passwordfile string EXCLUSIVE

<Step – 7 > 

Set fal_server and fal_client parameter for primary database:

SQL> alter system set fal_server=RTSDR;
SQL> show parameter fal_server
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 fal_server string RTSDR
SQL> alter system set fal_client='RTS';
 SQL> show parameter fal_client
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 fal_client string RTS

Set standby_file_management parameter to auto:

 SQL> alter system set standby_file_management=auto;
 SQL> show parameter standby_file_management
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 standby_file_management string AUTO

<Step – 8 > 

Configure listener file and copy it to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
Listener file:

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = RTS)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
 (SID_NAME = RTS)
 ) )
 LISTENER =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))
 )
 ADR_BASE_LISTENER = /u01/app/oracle

<Step – 9 > 

Entries for the primary and standby databases are needed in the “$ORACLE_HOME/network/admin/tnsnames.ora” files on both the servers.

 RTS =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = RTS)
 (GLOBAL_NAME = RTS)
 (UR=A)
 ) )
 RTSDR =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = dr)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = RTS)
 (GLOBAL_NAME = RTS)
 (UR=A)
 ) )

Ensure your above configuration by TNSPing utility on Primary and standby:

 [oracle@pr admin]$ tnsping RTS
 TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 03-OCT-2014 06:04:09
 Copyright (c) 1997, 2009, Oracle. All rights reserved.
 Used parameter files:
 /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
 Used TNSNAMES adapter to resolve the alias
 Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))) (CONNECT_ DATA = (SERVICE_NAME = rts) (GLOBAL_NAME = RTS)))
 OK (10 msec)
 [oracle@pr admin]$ tnsping RTSDR
 TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 03-OCT-2014 06:04:15
 Copyright (c) 1997, 2009, Oracle. All rights reserved.
 Used parameter files:
 /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
 Used TNSNAMES adapter to resolve the alias
 Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dr)(PORT = 1521))) (CONNECT_ DATA = (SERVICE_NAME = RTS) (GLOBAL_NAME = RTS)))
 OK (20 msec)

<Step – 10 > 

Backup primary database via RMAN backup utility:

 [oracle@pr admin]$ rman target /
 Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 4 02:02:11 2014
 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
 connected to target database: RTS (DBID=1421312347)
 RMAN> backup database plus archivelog;

Note: Backup location: Flash Recovery Area.

<Step – 11> 

Create Standby control file.

SQL> alter database create standby controlfile as '/u01/bkup/stndbyctrl.ctl';

<Step – 12>

Create pfile from spfile:

SQL> create pfile='/u01/bkup/initRTS.ora' from spfile;

<Step – 13>

After creating parameter file as above, edit following changes in newly created pfile:

 *.db_unique_name='RTSDR'
 *.fal_server='RTS';
 *.log_archive_dest_2='SERVICE=RTS ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RTS'

<Step – 14>

Copy parameter file to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

 [oracle@pr bkup]$ scp initRTS.ora oracle@192.168.17.132:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

Copy standby control file to DR @ location: /u01/app/oracle/oradata/RTS/control01.ctl & /u01/app/oracle/flash_recovery_area/RTS/control02.ctl

 [oracle@pr bkup]$ scp stndbyctrl.ctl oracle@192.168.17.132:/u01/app/oracle/oradata/RTS/control01.ctl
 [oracle@pr bkup]$ scp stndbyctrl.ctl oracle@192.168.17.132:/u01/app/oracle/flash_recovery_area/RTS/control02.ctl

Copy password file to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

 [oracle@pr ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
 [oracle@pr dbs]$ scp orapwRTS oracle@192.168.17.132:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

Copy Listener file to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin

 [oracle@pr bkup]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
 [oracle@pr admin]$ scp listener.ora oracle@192.168.17.132:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

Edit DR database listener file with host name. ( i.e. (HOST = dr))

And finally copy RMAN backup to DR @ flash recovery area.

 [oracle@pr ~]$ cd /u01/app/oracle/flash_recovery_area/
 [oracle@pr flash_recovery_area]$ scp -r RTS oracle@192.168.17.132:/u01/app/oracle/flash_recovery_area/

Standby/DR Server Configurations:

<Step – 15>

Startup standby database in mount state:
Set following environment variable as oracle user OR edit those in /home/oracle/.bash_profile in order to set it for every time while oracle user logged in:

 export ORACLE_SID=RTS
 export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
 export PATH=$PATH:/u01/app/oracle/product/11.2.0/dbhome_1/bin
 [oracle@DR ~]$ mkdir -p /u01/app/oracle/admin/RTS/adump

 SQL> sqlplus / as sysdba
 SQL> startup mount
 ORACLE instance started.
 Total System Global Area 972898304 bytes
 Fixed Size 2219272 bytes
 Variable Size 566231800 bytes
 Database Buffers 398458880 bytes
 Redo Buffers 5988352 bytes
 Database mounted.

<Step – 16>

Create spfile from pfile:

 SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initRTS.ora';

<Step – 17>

Start listener on standby:

[oracle@dr ~]$ lsnrctl start

<Step – 18>

Create follwoing directories on DR server, in case those are not available:
/u01/app/oracle/admin/RTS/adump
/u01/app/oracle/flash_recovery_area

<Step – 19>

Restore and recover database @ DR with RMAN backup utility.

RMAN> list backup of database summary;
 List of Backups
 ===============
 Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
 ------- -- -- - ----------- --------------- ------- ------- ---------- ---
 6 B F A DISK 05-OCT-14 1 1 NO TAG20141005T065604
RMAN> restore database;
RMAN> recover database;

Note: Recovery of database would be failed with RMAN-06054 error, We can ignore it because RMAN will ask for unknown archive log ( i.e. next archive log sequence, i.e. 10 ) who is not also available on Primary database.

Error log: RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 10 and starting SCN of 1009554

<Step – 20>

Create standby redolog file to Primary and DR for the user of switch over, It should be match the configuration of the primary server.
Note: Create one additional standby redolog file on both.

 SQL> sqlplus / as sysdba
 SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL' scope=spfile;
 SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby1.log') size 51M;
 SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby2.log') size 51M;
 SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby3.log') size 51M;
 SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby4.log') size 51M;
 SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' scope=spfile;

<Step – 21>

Start apply process @ DR.

 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE nodelay DISCONNECT FROM SESSION;

In case of you want to cancel apply process, issue following command:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

<Step – 22>

After graceful completion of above apply process, verify archive logs on Primary as well as DR.
On Primary: 

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 8
 Next log sequence to archive 10
 Current log sequence 10

On Standby:

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 8
 Next log sequence to archive 0
 Current log sequence 10

By above result, archive logs on both the databases are in sync now.

To test your data guard configuration, generate archive logs on primary site and verify it on DR site:

SQL> alter system switch logfile; //Give this command multiple times for testing.

On Primary:

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 19
 Next log sequence to archive 21
 Current log sequence 21

OR

SQL> select max(sequence#) from v$archived_log;
 MAX(SEQUENCE#)
 --------------
 20

On Standby:

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 19
 Next log sequence to archive 0
 Current log sequence 21

OR

 SQL> select max(sequence#) from v$archived_log;
 MAX(SEQUENCE#)
 --------------
 20

Cheers!! Our Data Guard configuration has been configured successfully.

Verify database roles by below mentioned SQL query:

On Primary:

 SQL> select name,open_mode,database_role from v$database;
 NAME OPEN_MODE DATABASE_ROLE
 --------- -------------------- ----------------
 RTS READ WRITE PRIMARY

On DR:

 SQL> select name,open_mode,database_role from v$database;
 NAME OPEN_MODE DATABASE_ROLE
 --------- -------------------- ----------------
 RTS MOUNTED PHYSICAL STANDBY

You can also verify total number of log sequence generated and applied on DR site, by below SQL query:

 SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
 SQL> SELECT sequence#, first_time, next_time, applied
 FROM v$archived_log
 ORDER BY sequence#;
 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
 ---------- --------- --------- ---------
 3 04-OCT-14 04-OCT-14 YES
 4 04-OCT-14 04-OCT-14 YES
 5 04-OCT-14 04-OCT-14 YES
 6 04-OCT-14 05-OCT-14 YES
 7 05-OCT-14 05-OCT-14 YES
 8 05-OCT-14 05-OCT-14 YES
 9 05-OCT-14 05-OCT-14 YES
 10 05-OCT-14 05-OCT-14 YES
 11 05-OCT-14 05-OCT-14 YES
 12 05-OCT-14 05-OCT-14 YES
 13 05-OCT-14 05-OCT-14 YES
 14 05-OCT-14 05-OCT-14 YES
 15 05-OCT-14 05-OCT-14 YES
 16 05-OCT-14 05-OCT-14 YES
 17 05-OCT-14 05-OCT-14 YES
 18 05-OCT-14 05-OCT-14 YES
 19 05-OCT-14 05-OCT-14 YES
 20 05-OCT-14 05-OCT-14 YES

In case of you are facing any kind of error than following SQL query will help you to diagnose it.

 SQL> select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';
 DEST_NAME
 --------------------------------------------------------------------------------
 STATUS ERROR
 --------- -----------------------------------------------------------------
 LOG_ARCHIVE_DEST_2
 VALID

Note: LOG_ARCHIVE_DEST_2 should be VALID in order to continue Data Guard sync.

OR

SQL> select message from v$dataguard_status;

Note: This command will give you appropriate message about the dataguard current status.

By default, for a newly created standby database, the primary database is in maximum performance mode.

Protection Mode:
Default protection mode of newly configured standby database would be maximum performance mode.
There are 3 protection modes: Maximum Availability, Maximum Performance and Maximum Protection. for more information, click me.

By above mentioned steps you can configure Data Guard in your environment, Stay tune with my next article about Data Guard Switch Over and Switch Back steps.

Oracle 11g Logo

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

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

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

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

Error log:

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

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

RMAN> DELETE EXPIRED ARCHIVELOG ALL;

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

Oracle 11g Logo

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

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

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

Error Logs:

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

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

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

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

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

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

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

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

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

SQL> shutdown

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

By above workaround, Infected node up and running smooth.

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

Stay Tune 🙂

Oracle 11g Logo

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

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

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

Default Device Type: Disk

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

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

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

Default Device Type: sbt

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

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

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

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

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

Clear Default Device Type:

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

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

This all about configuring default device type.

Stay Tune. 🙂

Oracle 11g Logo

HP Data Protector Backup Job failed with ORA-01157: cannot identify/lock data file 516 – see DBWR trace file

HP data protector daily database backup job failed after restoring database, with “ORA-01157: cannot identify/lock data file 516 – see DBWR trace file” OR “ORA-01110: data file 516: ‘/oracle//sapdata1/temp_4/temp.data4′” oracle errors.

Solution: Drop and recreate specified tempfile at desired location mentioned in error.

HP data protector backup job logs are as follows:

 BR0051I BRBACKUP 7.20 (35)
 BR0055I Start of database backup: beomxhha.anf 2014-08-13 07.30.42
 BR0484I BRBACKUP log file: /oracle/ECP/sapbackup/beomxhha.anf
 BR0477I Oracle pfile /oracle//11203/dbs/init.ora created from spfile /oracle//11203/dbs/spfile.ora
 BR0280I BRBACKUP time stamp: 2014-08-13 07.30.47
 BR0301E SQL error -1157 at location BrDbfInfoGet-1, SQL statement:
 'OPEN curs_8 CURSOR FOR'
 'SELECT TS.TABLESPACE_NAME, TS.STATUS, TS.BLOCK_SIZE, TS.CONTENTS, TS.EXTENT_MANAGEMENT, TS.BIGFILE, DF.FILE_NAME, DF.FILE_ID, NVL(DF.STATUS, ' '), NVL(DF.BYTES, 0), NVL(DF.AUTOEXTENSIBLE, 'NO'), NVL(DF.MAXBYTES, 0), NVL(DF.INCREMENT_BY, 0) FROM DBA_TABLESPACES TS, DBA_DATA_FILES DF WHERE TS.TABLESPACE_NAME = DF.TABLESPACE_NAME UNION ALL SELECT TS.TABLESPACE_NAME, TS.STATUS, TS.BLOCK_SIZE, TS.CONTENTS, TS.EXTENT_MANAGEMENT, TS.BIGFILE, TF.FILE_NAME, TF.FILE_ID * -1, NVL(TF.STATUS, ' '), NVL(TF.BYTES, 0), NVL(TF.AUTOEXTENSIBLE, 'NO'), NVL(TF.MAXBYTES, 0), NVL(TF.INCREMENT_BY, 0) FROM DBA_TABLESPACES TS, DBA_TEMP_FILES TF WHERE TS.TABLESPACE_NAME = TF.TABLESPACE_NAME ORDER BY 1, 7'
 ORA-01157: cannot identify/lock data file 516 - see DBWR trace file
 ORA-01110: data file 516: '/oracle//sapdata1/temp_4/temp.data4'
 BR0314E Collection of information on database files failed
 BR0056I End of database backup: beomxhha.anf 2014-08-13 07.30.47
 BR0280I BRBACKUP time stamp: 2014-08-13 07.30.47
 BR0054I BRBACKUP terminated with errors

Work around for the mentioned error are as follows:

Note: I would request you to consider full backup of database before doing this activity, for future safety.

Verify ‘temp.data4’ tempfile available at specified location:

ll -l /oracle//sapdata1/temp_4/temp.data4

OR verify ‘temp_4’ directory exist or not. In case of ‘temp_4’ directory not exist recreate it with mkdir linux command from oracle user.

ll -l /oracle//sapdata1/temp_4/

Verify tempfile status with the help of following query:

 select file#,status,name from v$tempfile;
 OR
 select file_name,status from dba_temp_files;

Drop mentioned tempfile and recreate it, with the help of following SQL command:

alter database tempfile '/oracle//sapdata1/temp_4/temp.data4' drop;
 OR
 alter database datafile 516 OFFLINE DROP;
alter tablespace temp add tempfile '/oracle//sapdata1/temp_4/temp.data4' size 10G autoextend on maxsize unlimited;

After dropping and recreating tempfile, HP data protector test backup run successful.

Stay Tune. 🙂