Oracle 11g Logo

Oracle 11g Manual Online Hot Backup

In this article I am going to cover steps 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_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 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>>

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

 SQL> alter database begin backup;
 Database altered.

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

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 * /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 /u01/app/oracle/flash_recovery_area/RTS/archivelog/2014_11_16
 [oracle@PR 2014_11_16]$ cp o1_mf_1_90_b6jys239_.arc /u01/bkup/manual_online_hot

Cross verify all backup files are in place(i.e. datafiles and archive log file (sequence 90) @backup location in order restore database.

Congratulations!! Manual Online hot backup successfully completed.

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

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