Tuesday, December 20, 2011

Oracle Recovery Manager(RMAN) New features in 11g

Oracle Recovery Manager (RMAN) is the preferred method to backup database and it is included with Oracle software. Using RMAN you can backup the database to local/shared disk without any other agents. If you need to backup to tape then you need Media management library.

Please refer below link for configuring RMAN to backup database to a media manager.
http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmconfb.htm#i1006526

The restore and recovery of the database is very easy using RMAN, it will figure it out where the backups are stored and which data files need to be restored and recovered.

Oracle 11g introduced many exciting new features in Recovery Manager (RMAN). Lets start using these new features and benefit from it.

• RMAN Proactive health checks :- Now you can proactively check database block corruptions using VALIDATE DATABASE
RMAN > VALIDATE DATABASE;

You can also run the proactive check on a datafile, tablespace or block.
RMAN > VALIDATE DATAFILE 1 BLOCK 10;
RMAN > VALIDATE TABLESAPCE users;

• Database Recovery Advisor : – The data recovery advisor automatically diagnoses the corruptions and loss of data on disk and determines the corrective repair options.

The LIST FAILURE command displays any failures with priority of CRITICAL or HIGH in order of importance
RMAN> LIST FAILURE

The ADVISE FAILURE command provides repair advice for failures listed by LIST FAILURE
RMAN > ADVICE FAILURE

The REPAIR FAILURE command applies the repair scripts advised by the ADVISE FAILURE command.
RMAN>REPAIR FAILURE

You can also use PREVIEW command to see the contents of the repair before proceeding to actual repair
RMAN>REPAIR FAILURE PREVIEW

• Faster Backup Compression: The ZLIB compression is faster than original B2ZIP compression with less CPU resources

Use below command for ZLIB compression
RMAN> configure compression algorithm 'ZLIB' ;

Use below command to change compression to BZIP2
RMAN> configure compression algorithm 'bzip2';

• More backup Comcodession Choices (11gR2 only): Now we have different types of comcodession levels, i.e. LOW, MEDIUM and HIGHT and with CPU resource usage from least to highest.

Here is the example
RMAN> configure comcodession algorithm ‘medium’;

• RMAN Backups to Cloud: Now you can set your RMAN backup destination to Cloud. Amazon provides cloud computing service, but you need to use specially developed media management library.

• RMAN UNDO bypass: The RMAN backup command no longer backs up the UNDO data that is not needed for recovery. Prior to Oracle 11g, all UNDO transactions that were already committed also backed up. This backup undo optimization minimizes the backup time and storage.

• Duplicate Database from Backup (11gR2 only): Prior 11g, In order to duplicate a database using DUPLICATE TARGET DATABASE command you need connect to target database. In case if target database is down you cannot able to duplicate. From 11g, you can able to duplicate the database without connecting to target database, make sure that you have backups available on duplicate site.

Here is the example for duplicate database
connect auxiliary sys/xxxx@dup
connect catalog rman/xxx@rmancat
duplicate database 'PRD' to DUP' until time "to_date('01/01/11 10:00:00','mm/dd/yy hh24:mi:ss')"
db_file_name_convert =( “/dbs1/oradata/PRD","/dbs2/oradata/DUP")
backup location '/rman_backup' ;

Duplicate Database options
-NOREDO – Using this option no archivelogs will be applied
-UNDO_TABLESPACE – You must specify the UNDO tablesapce when you are not connected to target database.

• Set NEWNAME Flexibility while restoring Database (11gR2 only): When you are restoring database on different server with different file system structure you need to change data file path using “SET NEWNAME” command. If you have hundreds of datafiles then you need to change the path for all data files.

run
{
set newname for datafile 1 to ‘/dbs1/oradata/system01.dbf’;
set newname for datafile 2 to ‘/dbs2/oradata/users01.dbf’;
---- (Some detail removed for brevity) ----
restore database;
}

From 11g, you have flexibility using a single “SET NEWNAME” clause for all datafiles in a tablespace.

run
{
set newname for tablespace DATA to '/dbs1/oradata/data%b.dbf';
set newname for tablespace INDEX to '/dbs2/oradata/index%b.dbf';
---- (Some detail removed for brevity) ----
Restore database;
}

You can also set the path for entire database using a single command.
run
{
set newname for database to '/dbs1/oradata/%b';
restore database;
}

• TO DESTINATION Clause in BACKUP command (11gR2 only): You can specify a destination location for RMAN backups using “TO DESTINATION” clause .

RMAN> backup tablespace users to destination '/backup/rman;
You can also use this clause in ALLOCATE CHANNEL command
RMAN> run {
2> allocate channel c1 type disk to destination '/backup/rman';
backup database;
3> }

• Parallel backup of Same datafile: Now you can break the large datafiles into small sections and it reduces the backup time of large datafiles

Here is the example
run {
allocate channel c1 type disk format '/rman_backup1/%U';
allocate channel c2 type disk format '/rman_backup2/%U';
backup section size 100m
datafile 10;
}

• Automatic Block Repair (11g R2 Only): This feature automatically repairs the blocks on primary from blocks on Physical Standby. RECOVER BLOCK enhanced to repair the blocks on Primary as soon as it detects the corruption from standby when available.

• Transported Tablespace Enhancement: Prior 11g, the transported tablespace must be in read-only mode. But from 11g onwards you can transport the tablespace both in read-only and read-write mode.

• Virtual Private Catalog: The virtual catalogs are created within in same RMAN Catalog. Prior to 11g, we have only one catalog and catalog owner can able to see repository information for all databases. From 11g, you can separate the catalog database access across the departments/groups (for ex, Manufacturing, IT…etc.) using virtual catalogs feature to maximize the security.

Create a separate user for IT department to grant catalog access
SQL> create user it_user identified by xxxxx quota unlimited on users;
SQL> grant recovery_catalog_owner to it_user;

Grant catalog access on “itdb1” to virtual catalog owner “it_user”
$ rman target=/ rcvcat rman/rman@rmancat
RMAN> grant catalog for database itdb1 to it_user;

Now connect using the virtual catalog owner “it_user” and create virtual catalog
$ rman target=/ rcvcat ituser/xxxx@rmancat
RMAN> create virtual catalog;

• Merging RMAN Catalogs: If you have operations across different regions and maintaining different catalogs for each state and want to merge then you can use merge catalog feature without re-registering the databases in new catalog.
In below example we are importing rmancat2 into rmancat1
$ rman target=/ rcvcat rman/rman@rmancat1
RMAN> import catalog rman/rman@rmancat2;

• GUI interface for RMAN: Now you can use RMAN functionality through a GUI iFrom Enterprise manager

•Archive log Deletion Policy Enhancements: The archive log deletion policy has been extended in 11g for greater flexibility and protection for Dataguard environments.

Oracle 10g Syntax.
CONFIGURE ARCHIVELOG DELETION POLICY {CLEAR | TO {APPLIED ON STANDBY | NONE}}

Oracle 11g Syntax.
ARCHIVELOG DELETION POLICY {CLEAR | TO {APPLIED ON [ALL] STANDBY |BACKED UP integer TIMES TO DEVICE TYPE deviceSpecifier |NONE | SHIPPED TO [ALL] STANDBY}[ {APPLIED ON [ALL] STANDBY | BACKED UP integer TIMES TO DEVICE TYPE deviceSpecifier |NONE | SHIPPED TO [ALL] STANDBY}]...}

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com

Tuesday, December 6, 2011

RMAN Incrementally updated backups

This feature enables you to create an image of data file and roll forward the image copy backups with regularly create incremental backups of the database. The image copy is updated with all changes up to the SCN of incremental backup that was taken.

This feature reduces the restoration time especially when Recovery time Objective (RTO) is set for mission critical databases. In case of any recovery required you never have more than one day of redo logs to apply.

For example: - Run this script on a regular basis to implement an updated backup strategy
RUN {
ALLOCATE CHANNEL C1 DEVICE TYPE DISK FORMAT ‘/oradata/rman/backup.%u’;
RECOVER COPY OF DATABASE WITH TAG 'incr_backup_update';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup_update' ATABASE;
}

• The BACKUP INCREMENTAL LEVEL 1... FOR RECOVER OF COPY WITH TAG... command creates a level 0 backup of the datafile, if there is no level 0 backup. In all subsequent runts, It produce’s level1 incremental backups of data file.

• The RECOVER COPY OF DATABASE WITH TAG... command causes RMAN to apply any incremental level 1 backups to a set of datafile copies with the same tag and roll forwards the image copies.

Incremental Merge backup’s strategy is the fast and easy way to restore entire database.

If you are backing up your database using Incremental merge backup then you can recovery the entire database very fast and easily by using SWITCH command. This command will point the controlfile to the backup of the data files that are present on the disk.

Let’s simulate a database failure by removing all or few data files. Make sure your database has been successfully backed up before you remove the files.

Mount the database and use SWITCH DATABASE TO COPY command to point the database to the backups available on disk.

SYS@DBTEST>startup mount
SYS@DBTEST>switch database to copy;
SYS@DBTEST>Recovery the database;
SYS@DBTEST>Alter database open;

Please note that we didn’t restored backup from backup disk or media, we have pointed the database to backup.

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com

Friday, October 21, 2011

Webinar: Oracle Real Application Cluster

Oracle Real application cluster allows multiple database instances on different servers in the cluster against a shared database. Oracle RAC provides Reliability, Recoverability, Error Detection and Continuous operations.

Date and time: Monday, November 14th 2011 8:00am - 9:00am PST

You will get an opportunity to learn
  • What is Real Application Cluster?
  • Oracle Cluster Benefits & Components
  • Oracle Cluster Ready Services
  • Interconnect & Cache fusion
  • Oracle RAC Database & ASM
  • Transparent Application Failover (TAF)
  • Backup & Recovery
  • New Features in Oracle 11g RAC
To register for Webinar, please send an email to SatishbabuGunukula@gmail.com and reserve your spot. You will receive an email confirmation with Webinar link.

Registration is limited.

Click here for presentation link

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Thursday, October 20, 2011

Understand the Power of Oracle RMAN

Hi Everyone,

My article published in IOUG (Independent Oracle User Group) 2011 Best Practices Booklet and distributed at Oracle Open World.

Understand the Power of Oracle RMAN – Page 47
By Satishbabu Gunukula


Please find the link.
http://www.slideshare.net/satishbabugunukula/ioug-tip-book11gunukula

Hope you like the article.

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com

Friday, October 14, 2011

ORA-01187: cannot read from file 201 because it failed verification tests

You may receive this error under below circumstances.
1. when your temp file drive failed
2. when you duplicate your database.
3. When you trying to take backup controlfile to trace….etc

ORA-01187: cannot read from file 201 because it failed verification tests
ORA-01110: data file 201: '/u03/oradata/TEST/temp01.dbf'


The temp data file didn’t not pass the checks to insure it is part of the database and reads are not allowed until it is verified.

You will see the status as “ONLINE” when you check the status of the temp file using v$tempfile. But when you query DBA_TEMP_FILES’, you will receive the error.

SQL> SELECT FILE_ID,FILE_NAME, STATUS FROM DBA_TEMP_FILES;
ERROR at line 1:
ORA-01187: cannot read from file 201 because it failed verification tests
ORA-01110: data file 201: '/u03/oradata/AGX/temp01.dbf'

To resolve this issue first run below command and query DBA_TEMP_FILES.
SQL> ALTER SYSTEM CHECK DATAFILES

If you still have the issues then add a tempfile and drop the file which has issues.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘'/u03/oradata/TEST/temp02.dbf' SIZE 100M;
SQL> ALTER DATABASE TEMPFILE TEMPFILE ‘'/u03/oradata/TEST/temp01.dbf' DROP;

This should resolve your issue.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Monday, October 3, 2011

ORA-12547: TNS:lost contact

When I tried to login into oracle I have received below error
sapec4:ec4adm 61> sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 29 14:00:17 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

SQL> connect / as sysdba
ERROR:
ORA-12547: TNS:lost contact

Here is the simple solution for that

Solution1:-
1.Check “oracle:dba” should have ownership on $ORACLE_HOME/bin/sqlplus
$chown oracle:dba $ORACLE_HOME/bin/sqlplus
2.$ORACLE_HOME/bin/sqlplus should have 6751 permission
$chmod 6751 $ORACLE_HOME/bin/sqlplus

If you still have the problems try solution 2.

Solution2:-
You may receive this error if oracle binaries are not linked correctly, stop databases and relink the binaries.

$ ORACLE_HOME/bin/ relink all

If you find below errors in listener.log then problem related to limit of PROCESSES value.

TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact


Solution:- Increase the PROCESSES parameter in the init.ora to a higher value
SQL> Alter system set processes= “

After fixing TNS issue you may see below error

ORA-27140: attach to post/wait facility failed

Solution:-
$ cd $ORACLE_HOME/bin
$ chmod 6751 oracle

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Thursday, September 29, 2011

/etc/profile[57]: ulimit: pipe: is read only

You need to set the “session limits for Oracle user” before you install any Oracle Database product.

When you try to login into “oracle” user you will receive following error

test@server1$ su - oracle
/etc/profile[57]: ulimit: pipe: is read only

Check the ulimit using below command

test@server1$ulimit -a
address space limit (kbytes) (-M) unlimited
core file size (blocks) (-c) unlimited
cpu time (seconds) (-t) unlimited
data size (kbytes) (-d) unlimited
file size (blocks) (-f) unlimited
locks (-L) unlimited
locked address space (kbytes) (-l) 32
nofile (-n) 65536
nproc (-u) 16384
pipe buffer size (bytes) (-p) 4096
resident set size (kbytes) (-m) unlimited
socket buffer size (bytes) (-b) 4096
stack size (kbytes) (-s) 10240
threads (-T) not supported
process size (kbytes) (-v) unlimited

The syntax that was previously used to set the "Max user processes limitation" is changed from "ulimit -p" to "ulimit -u", which is causing the issue.

Replace the session limits in /etc/profile with below code to resolve the issue.

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -u 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com

Repair block corruptions using Data Recovery Advisor in Oracle 11g

The data recovery advisor introduced in Oracle 11g and using this tool you check the block corruptions and loss of data files and control files. The GUI feature also available from Enterprise manager.

By default RMAN checks for physically corrupted blocks automatically with every backup (backup set or image copy) it creates. You can check the logical corruption using CHECK LOGICAL command. Using VALIDATE keyword it checks all blocks and it will not create any backup.

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;
Starting backup at 28-SEP-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
. . .

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
16 OK 0 194907 2048000 1045096118
File Name: /dbs1/orcl/users.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 877717
Index 0 201038
Other 0 774338

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
21 FAILED 0 169474 2048000 1046708826
File Name: /dbs1/orcl/tools.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 847248
Index 0 358482
Other 1 672796

The above command populates the information of all the corrupted blocks in v$database_block_corruption view with and you can query this view to find all corrupted blocks.

Here we are using Data Recovery Advisor to find the possible repair options

Use LIST FAILURE command to find the any failures with a status OPEN and a priority of CRITICAL or HIGH in order of importance. You can use CHANGE FAILURE command to change the priority of a failure or close an open failure.

RMAN> LIST FAILURE
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1041 HIGH OPEN 23-SEP-11 Datafile 21: '/dbs1/orcl/tools.dbf ' contains one or more corrupt blocks

Use ADVISE FAILURE command to for repair advise based up on failures listed by LIST FAILURE command.

RMAN> ADVISE FAILURE;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1041 HIGH OPEN 23-SEP-11 Datafile 21:''/dbs1/orcl/tools.dbf ' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=319 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
1. No backup of block 986359 in file 21 was found. Drop and re-create the associated object (if possible), or use the DBMS_REPAIR package to repair the block corruption

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
1 Recover multiple corrupt blocks in datafile 21
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/home/oracle/diag/rdbms/orcl/orcl/hm/reco_3435353455.hm
In this case there are no manual actions available, if any actions available then you can use to repair the database.

Use automated repair options to fix the block corruptions and use the repair script generated by RMAN. You can run the script manually or you can use REPAIR FAILURE command.

Use REPAIR FAILURE PREVIEW command to list the contents of repair script without applying it.
RMAN> Repair failure preview;

Use REPAIR FAILURE command to apply the repair script generated by ADVISE FAILURE command and fix the block corruptions.
RMAN> REPAIR FAILURE;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/home/oracle/diag/rdbms/orcl/orcl/hm/reco_3435353455.hm

contents of repair script:
# block media recovery for multiple blocks
recover datafile 21 block 120;

The REPAIR FAILURE command promts you to confirm the preair and if you want to prevent that use NOPROMPT keyword.

RMAN> REPAIR FAILURE NO PROMPT;

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Monday, September 26, 2011

Duplicate database using RMAN

You can easily create your Development or QA databases from Production using DUPLICATE TARGET DATABASE command.

Here are the simple steps to duplicate the database.

1. Copy the source database init.ora file for auxiliary instance and replace the SID with Auxiliary Database SID (for ex, DUP)

Prodsrv$ cp $ORACLE_HOME/dbs/initPRD.ora cp $ORACLE_HOME/dbs/initDUP.ora
Replace SID=PRD with DUP in initDUP.ora

2. Set DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT to convert the datafile and redo log file names in Auxilary database init.ora file

3. Copy the auxilary database init.ora parameter from source server (for ex, Prodsrv) to Target Server (for ex, Devsrv)
Prodsrv$ scp $ORACLE_HOME/dbs/initDUP.ora oracle@Devsrv:/$ORACLE_HOME/dbs/initDUP.ora

4. Add Auxilary database TNS entry in $ORACLE_HOME/network/admin/tnsnames.ora file on source server.
DUP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DUP) )
)

5. Set ORACLE_SID and create password file for DUP database on target host
Devsrv$export ORACLE_SID=DUP
Devsrv$orapwd file=orapwDUP password=xxxxx

6. Create a static listener for auxiliary database and reload, because auxiliary database will not register itself with the listener.

(SID_DESC =
(GLOBAL_DBNAME =DUP)
(ORACLE_HOME = )
(SID_NAME = DUP)
)

7. Startup the Auxilary database instance in nomount state on target host
SYS@DUP> startup nomount

8. Run the duplicate the target database from source
Prodsrv$ rman TARGET SYS/xxxxx@PRD CATALOG rman/xxxx@rmancat AUXILIARY SYS/xxxx@DUP
RMAN> DUPLICATE TARGET DATABASE TO DUP;

9. After database is duplicated successfully, RMAN will open the database. Please check the RMAN log and Alert.log file for issues,if any.



From 11g, you can able to duplicate the database without connecting to target database. Make sure that you have backups available on duplicate site.

Here is the example for duplicate database
connect auxiliary sys/xxxx@dup
connect catalog rman/xxx@rmancat
duplicate database 'PRD' to DUP' until time "to_date('05/01/2011 14:00:00','mm/dd/yy hh24:mi:ss')"
db_file_name_convert =( “/dbs1/oradata/PRD","/dbs2/oradata/DUP")
backup location '/rman_backup' ;

Duplicate Database options
-NOREDO – Using this option no archivelogs will be applied
-UNDO_TABLESPACE – You must specify the UNDO tablesapce when you are not connected to target database.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Recover a datafile which is never backed up using RMAN

Using RMAN you can able to recover a datafile which is never backed up, the recovery will come from the combination of online redo and available archived logs.

Before you do the test make sure that no backups are running. Login into the database and create table privileges to do the test with. The database must be in archivelog mode.

$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 26 12:32:43 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------- ----------- -----------------------------
db_create_file_dest string /db1/oradata
SQL> create tablespace test_tbs;
SQL> create user testuser identified by test124 default tablespace tbs_tbs;

Connect to "TESTUSER" and create a "test_table" for testing
SQL> connect testuser/xxxxxx
SQL> create table test_table as select * from user_objects;

Now you can take the newly created tablespace offline as "SYS" user and check the status. Please note that the database should be in archivelog mode to be able to accomplish the recovery.

SQL> alter system switch logfile;
SQL> alter tablespace test_tbs offline immediate;
SQL> select tablespace_name, status from dba_tablespaces;

SQL>> select file_name,status from dba_data_files;
/db1/oradata/ORCL/01_tbs_1_snz4161s_.dbf RECOVER

Remove the datafile from the operating system location.

Remember that there is no backup of this new tablespace at this point in time. Start an RMAN session.

$ rman TARGET / CATALOG rman/rman123@rmancat
RMAN> restore tablespace test_tbs

At this restore point, the OS level the datafile still doesn't exist.

RMAN searches for redo and/or archive log during the recovery command. During this particular restore session no archive logs were applied. so it actually restored the missing data file from online redo.

RMAN> recover tablespace tbs_from_online;

After recover bring the tablespace online and and check the status.

$ sqlplus / as sysdba
SQL> alter tablespace tbs_test online;
SQL>select tablespace_name, status from dba_tablespaces;

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com

Friday, August 5, 2011

Oracle Cluster wait events

What is wait event?
An event can be anything that Oracle has to perform on behalf of a set of instructions sent by the user interface. The term “wait” is used because every time a user connects to your application, a resource is allocated to perform tasks on its behalf. The waiting comes when a session is waiting for an action, sometimes from a user and at other times from the database

Wait events for Oracle can be divided as three categories
1.Time-based event
2.System-wide event
3.Session wait

Cluster Wait events
Cluster wait events are caused by multiple nodes in the cluster needing access to the same set of data. These must be tracked down on an individual basis. Usually there are multiple statements and tables that have contention problems.

Ideally, one node would only access one subset of data in order to reduce these conflicts. If possible, different types of operations should be done on each node. For example OLAP access could be targeted to node 1 while OLTP access could be targeted to node 2.

Usually there are multiple queries attempting update the same sets of data from different nodes. In order to determine what queries is producing the contention across the nodes, do the following:

Determine your beginning and ending snapshots from DBA_HIST_SNAPSHOT. Please note that we have used 4000 as beginning snapshot and 5000 as ending snapshot.

Use below SQL query to find event id
SQL> select event_id, event, count(1) cnt from dba_hist_active_sess_history where snap_id between 4000 and 5000 and wait_class_id=3871361733 and event in (’gc cr block busy’,’gc cr multi block request’) group by event_id, event order by 3;
SQL> Select sql_id, count(1) from dba_hist_active_sess_history where snap_id between 4000 and 5000 and event_id in (results from (2) above);
SQL> Select sql_text from dba_hist_sqltext where sqlid in .;

The steps above will tell you the SQL statements that cause the data contention.
The most important wait events for RAC include various categories, such as:
Block-oriented
•gc current block 2-way
•gc current block 3-way
•gc cr block 2-way
•gc cr block 3-way
Message-oriented
•gc current grant 2-way
•gc cr grant 2-way
Contention-oriented
•gc current block busy
•gc cr block busy
•gc current buffer busy
Load-oriented
•gc current block congested
•gc cr block congested

The major wait events in Oracle RAC are:
1. gc cr request
2. gc buffer busy

In a RAC environment the buffer cache is global across all instances in the cluster and hence the processing differs when compared single-database instance. When a process needs to read data, Oracle will first check to see if it exists in the local buffer cache. If not, it will review global buffer cache to see if another instance already has the data in their buffer cache. If global buffer cache has data then it send a request the data and sends the data via interconnect to avoid disk read. We need to monitor the amount of requests going back and forth via interconnect.

gc cr request( global cache cr request) – This event specifies the time it takes to receive data from remote cache. The main reasons for this event are

RAC traffic using slow connection – Normally we will use high speed inter connect to transfer data between instances, however, sometimes oracle may not able to select the high-speed interconnect and instead route traffic over the slower public network. This significantly increases the wait time for gc cr request wait event.

The oradebug command can be used to verify which network is being used for RAC traffic:
SQL> oradebug setmypid
SQL> oradebug ipc

The oradebug command can be used to manually remaster an object
SQL> oradebug lkdebug -m pkey "object_id"

Inefficient queries - This is mainly due to queries which are not written/tuned properly. The inefficient query increases the amount of data blocks requested by an oracle session. Due to this more often a block will need to be read from a remote instance via interconnect, which causes the wait event.

gc buffer busy(global cache buffer busy) – This wait event is similar to the buffer busy wait event in a single-instance database and this event specifies the time the remote instance locally spends accessing the required data block. You will encounter this wait event due to hot blocks or inefficient queries.

Hot Blocks – You will encounter this issue when multiple sessions requesting a block that either not in buffer cache or it is in incompatible mode. Also heavy delete and inserts operations on hot rows alleviate the problem. Adjusting pctfree and/or pctused parameters will help.

Inefficient Queries – Due to inefficient queries the more blocks requested from the buffer cache the more likelihood of a session having to wait for other sessions. Tuning queries will result to access fewer blocks and will often result in less contention for the same block.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Monday, August 1, 2011

Webinar: Why Oracle Data Guard and its Benefits

Do you want to know Oracle Data Guard Architecture and its benefits and how you can protect your database from Disaster Recovery? Join the webinar

Date and time: Monday, Aug 15th 2011 8:00am - 9:00am PST

To register for this event send an email to SatishbabuGunukula@gmail.com

Please find the presentation link
http://www.slideshare.net/satishbabugunukula/data-guard-architecture-setup

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com

Sunday, July 31, 2011

How to recover Undo or Rollback Tablespace

While handing with undo tablespace you need to be extra cautious due to active transactions in the undo segments. You need to follow different approach depend upon scenario.

Scenario 1: Undo/rollback datafile damaged or dropped accidently when database is up
In this scenario the lost or damaged datafile may contain the active transactions and you cannot able to offline or drop the undo/rollback datafile.

• Startup mount
SQL> STARTUP MOUNT

• Check the status of the datfile
SQL> SELECT FILE#, NAME, STATUS FROM V$DATAFILE;
If the datafile is offline you must bring the datafile online before you recover

• Restore and Recover the datafile
$ rman TARGET / CATALOG rman/*****@rman
RMAN> Restore datafile 'fullpath_and_filename'
RMAN> Recover datafile 'fullpath_and_filename'

• Open the database.
SQL> ALTER DATABASE OPEN;

Scenario 2: Undo/rollback datafile damaged or dropped accidently when database is down

• If using automatic UNDO_MANAGMENT then comment out the parameter in init.ora file. If using rollback segments then comment out ROLLBACK_SEMGNETS parameter

• Mount the database in restricted mode
SQL> STARTUP RESTRICT MOUNT

• Offline the undo or rollback datafile and drop the file
SQL> ALTER DATABASE DATAFILE 'fullpath_and_filename' OFFLINE DROP;

• Open the database and drop the UNDO tablespace or the tablespace which contains the rollback segments
SQL> ALTER DATABASE OPEN
SQL> DROP TABLESPACE tablespace_name INCLUDING CONTENTS;

• Recreate the undo tablespace. If you are using rollback segments, recreate the rollback segment tablespace and rollback segments. Make sure to bring the rollback segments online.

If using Undo tablespace
SQL> CREATE UNDO TABLESPACE undotbs2 DATAFILE '/oradata/undotbs2_01.dbf' SIZE 100M REUSE AUTOEXTEND ON;

If using rollback segment tablespace
SQL> CREATE TABLESPACE rollback_tbs DATAFILE '/oradata/rollback_tbs01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL;
SQL> CREATE ROLLBACK SEGMENT rollback1 TABLESPACE rollback_tbs;
SQL> ALTER ROLLBACK SEGMENT rollback1 ONLINE;

• Modify the parameter file settings
If using UNDO tablespace
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=new_undo_tablespace_name

If you are using the rollback segment tablespace, include the rollback segments that you created in previous step in ROLLBACK_SEMGNETS parameter in init.ora file

• Take the database out of restricted mode.
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

Oracle 11g introduced a new feature called RMAN UNDO Bypass. The RMAN backup command no longer backs up the UNDO data that is not needed for recovery. Prior to Oracle 11g, all UNDO transactions that were already committed also backed up. This backup undo optimization minimizes the backup time and storage.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Thursday, June 30, 2011

Understand Oracle RMAN Reporting

For any company the most important asset is data and the most challenging job is to recovery the database with less downtime with out any data loss, in the event of database failure. In many situations users end up with incomplete recovery of the database with out knowing which data files backed and which data files need to backup. You should ensure that your database is backed up efficiently and should restore successfully when needed. The RMAN reporting provides effective and easy way to determine database backup for a successful recovery.

In general, a catalog view contains the metadata of all databases and you need to write a complex query to extract usable backup information, but with RMAN reporting you can obtain the same information with LIST and REPORT commands. RMAN Reporting has been enhanced from version to version and now you can determine which database files have not been backed up and you can preview the backups required for successful restore...etc.

I have seen numerous postings on Metalink and Oracle forums regarding database restore and recovery issues as they don’t know what they have backed up and what backups they need for a successfully recovery. Using RMAN reporting effectively you can prevent such situations and ending up with incomplete recovery.
As part of your backup and recovery strategy, you should periodically run the reports that indicate

1) What you have backed up.
2) Which datafiles need backups or which files have not been backed up recently.
3) Which backups RMAN would need to restore if a problem occurs.
4) Historical information about RMAN jobs

What you have backed up?

To know what you have backed up, use LIST command to get the lists of backups, copies and other objects relating to backup and recovery (for example, backups that expired, restricted)

For example, you can list backups of all files in database
RMAN> LIST BACKUP OF DATABASE;

You can list copy of specified datafile
RMAN> LIST COPY OF DATAFILE ‘/oradata/users01.dbf';

Besides backups and copies, the RMAN can list other types of data using LIST command and it supports a number of options that enables you to control how output is displayed.

For example, you can list restore points know to repository.
RMAN> LIST RESTORE POINT;

You can list the names of recovery catalog scripts created with the CREATE SCRIPT or REPLACE SCRIPT command
RMAN> LIST SCRIPT NAMES;

List all of the expired backup sets
RMAN> LIST EXPIRED BACKUP SET;

Which datafiles need backups?

To know which files need backup, Use REPORT command that analyzes the available backups in the repository and return results about which files need a backup, which files have had unrecoverable operations on them, which files are obsolete etc.

Use the REPORT NEED BACKUP command to determine which database files need backup under a specific retention policy.

For example, display objects requiring backup to satisfy a recovery window-based retention policy.
RMAN> REPORT NEED BACKUP RECOVERY WINDOW OF n DAYS;

Use REPORT UNRECOVERABLE command to determine which database files need backup when a database file has been affected by unrecoverable operation, such as insert, direct-path insert, normal media recovery cannot be used to recover the file, because an unrecoverable operation does not generate redo. You must perform either a full or incremental backup of affected datafiles after such operations, to ensure that data blocks affected by the unrecoverable operation can be recovered using RMAN.

For example, identify the datafiles affected by an unrecoverable operation.
RMAN > REPORT UNRECOVERABLE;

You should also know which backups are obsolete and can therefore delete. Because disk full can create performance problems or can cause the database to halt. Use CROSSCHECK command to update the status of backups in the RMAN repository compared to their backup status on disk or tape and run REPORT OBSOLETE to identify which backups are obsolete and are no longer needed for recovery.

For example, crosschecks all backups and copies on the disk
RMAN>CROSSCHECK BACKUP DEVICE TYPE DISK;
RMAN> CROSSCHECK BACKUP;

Identify which backups are obsolete and no longer needed for recovery
RMAN> REPORT OBSOLETE;

Delete obsolete backup information from RMAN repository.
RMAN> DELETE OBSOLETE;

This way you can effectively manage the backups and space.

Which backups RMAN would need to restore if a problem occurs?

Use RESTORE... PREVIEW to know which backups would need to restore if a problem occurs. The RESTORE…PREVIEW command does not actually restore, but identifies the backups required to complete a given restore operation based on the information in the repository. This command ensures that all required backups are available or identify the backups required for successful recovery. In case any backup is unavailable, use the CHANGE…UNAVAILABLE command to mark the backup status to UNAVAILABLE and re-run the RESTORE…PREVIEW to see the backups required to perform a restore operation with out using the unavailable backup.

You can use RESTORE…VALIDATE HEADER as an alternative to RESTORE…PREVIEW. In addition to listing of the files needed for restore and recovery, this command validates the backup file headers to determine whether the files on disk or in the media management catalog correspond to the metadata in the RMAN repository.

For example, identify the backups required to complete a database restore.
RMAN> RESTORE DATABASE PREVIEW

Use REVIEW...SUMMARY command to summarize the lengthy output.
RMAN> RESTORE DATABASE PREVIEW SUMMARY;

The RESTORE... PREVIEW RECALL can be used, in cases a restore fails due to a needed backup being stored remotely. Using this RECALL mode with media manager which supports vaulted backups simplifies the RECALL operation, by recalling the backups which are needed for restore from remote storage.

For example, to recall backups stored offsite
RMAN>RESTORE DATABASE PREVIEW RECALL;

You can also use RMAN validation to be sure they can use used for a restore (for ex, check for block corruption and missing backup sets). The RESTORE…VALIDATE command tests weather you can restore from backup and users can validate the restore of the backup control file, SYSTEM tablespace, and all archived logs.

For example, to validate RMAN backups
RMAN> RESTORE DATABASE VALIDATE;

By default VALIDATE does not check for logical corruption but to identify logical corruption use CHECK LOGICAL clause with RESTORE…VALIDATE command.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Find historical information about Oracle RMAN Jobs

In some cases, the V$ views supply information that is not available through use of the LIST and REPORT commands. You should periodically run the reports to obtain historical information about RMAN jobs. For example, you may want to know how many backup jobs have been issued, the status of each backup job (for example, whether it failed or completed), when a job started and finished, and what type of backup was performed.

The views V$RMAN_BACKUP_JOB_DETAILS and V$RMAN_BACKUP_SUBJOB_DETAILS provide details about RMAN backup jobs.

RMAN Reporting has been enhanced through V$VIEWS from version to version. Backup operations are more transparent and now DBA’s can easily embed queries to these views in SQL scripts, and generate historical reports. These reports help to analyze the issues and provide corrective action plan to prevent further failures.

The V$RMAN_OUTPUT view is an in-memory view and shows the output of all currently running and recently completed RMAN jobs.

The following query shows the output of a RMAN job

SQL> Select OUTPUT from V$RMAN_OUTPUT where SESSION_STAMP='698172913';

OUTPUT
-----------------------------------------------------------------
connected to target database: PINDBTS (DBID=3952868985)

executing command: SET SNAPSHOT
using target database control file instead of recovery catalog
snapshot control file name set to: /backup/dvtestrac01/rman/pindbts1/snapcf.f
new RMAN configuration parameters are successfully stored

allocated channel: d1
channel d1: sid=311 instance=pindbts1 devtype=DISK

sql statement: alter system archive log current

Starting backup at 21-SEP-09
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
including current control file in backupset
channel d1: starting piece 1 at 21-SEP-09

---- (Some detail removed for brevity) ----

Finished backup at 21-SEP-09
released channel: d1

95 rows selected.

The V$RMAN_STATUS view shows the status of completed and running RMAN jobs. The running RMAN job information is stored in memory and once the job is completed the information is stored in the control file.

The following query shows the status of completed, completed with errors, failed and running RMAN jobs.

SQL> select OPERATION, START_TIME, END_TIME, OBJECT_TYPE, STATUS from v$RMAN_STATUS order by START_TIME;

OPERATION START_TIME END_TIME OBJECT_TYPE STATUS
---------------- ----------------------------- ----------------------------- ----- -------------- ----- -----------------------
RMAN 09/20/2009 20:00:06 09/20/2009 20:01:21 COMPLETED
BACKUP 09/20/2009 20:00:09 09/20/2009 20:01:06 DB FULL COMPLETED
BACKUP 09/20/2009 20:01:11 09/20/2009 20:01:21 ARCHIVELOG COMPLETED
RMAN 09/20/2009 20:05:03 09/20/2009 20:05:28 COMPLETED WITH ERRORS
BACKUP 09/20/2009 20:05:16 09/20/2009 20:05:28 ARCHIVELOG FAILED
RMAN 09/20/2009 20:10:15 09/20/2009 20:10:47 RUNNING
BACKUP 09/20/2009 20:10:39 09/20/2009 20:10:47 ARCHIVELOG RUNNING

The V$BACKUP_FILES view simulates the LIST BACKUP and LIST COPY RMAN commands. Also this view provides the information used as the basis of the REPORT OBSOLETE command. Now you can get the exact physical size of the backup set from BS_BYTES column, as the BYTES column shows the size of the physical file.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Tuesday, May 31, 2011

ORA-27090: Message 27090 not found; product=RDBMS; facility=ORA

If you notice any one of these errors in Alert.log during startup then the issue is related to “aio-max-nr" kernel limit. You will see this error in 10.2.0.4

ORA-27090: Message 27090 not found; product=RDBMS; facility=ORA

ORA-27090: UNABLE TO RESERVE KERNEL RESOURCES FOR ASYNCHRONOUS DISK I/O

ORA-27090: Message 27090 not found; product=RDBMS; facility=ORA
Linux-x86_64 Error: 4: Interrupted system call

You will encounter this issue when the "aio-max-nr" kernel limit is too low.

Here is how you can check the value
#/sbin/sysctl -a |grep aio

Solution : Change the "aio-max-nr" kernel limit as per Oracle recommendation
fs.aio-max-nr= 3145728

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Friday, May 27, 2011

Flashback Data Archive (Oracle Total Recall)

Flashback Data archive is the new feature introduced in Oracle 11g. It provides the ability to track and store all transitional changes to a table over its lifetime. Flashback Data Archive (which can logically span one or more table spaces) specifies a space quota and retention period for the archive, and then creates the required tables in the archive. It allows long-term retention (for ex years) of changed data to a table or set of tables for a user-defined period of time.

The database can have multiple flashback data archives, but only a single default archive. The individual flashback archive consists of one or more tablespaces or parts of tablespace. Each flashback archive has a name, retention period and a quota on each associated tablespace.

When a DML transaction commits an operation on a flashback archive enabled table, the FBDA (Flashback Data Archiver) process stores the pre-image of the rows into a flashback archive, along with metadata of the current rows. The FBDA process is also responsible for managing the data within the flashback archives, such as purging data beyond the retention period.

To use this feature user should have below privileges
1.FLASHBACK ARCHIVE object privilege on the Flashback Data Archive that you want to use for that table
2.FLASHBACK ARCHIVE ADMINISTER system privilege to enable or disable Flashback data archive.

Creating a Flashback Data Archive:-
• CREATE FLASHBACK ARCHIVE flashback_archive TABLESPACE flashback_archive_tbs
QUOTA 15G RETENTION 5 YEARS;

Altering a Flashback Data Archive:-
You can add, modify and remove tablespaces, and change the retention, purge some or all of its data.

Add up to 2G of tablespace “flashback_archive_tbs1” to Flashback Data Archive flashback_archive1
• ALTER FLASHBACK ARCHIVE flashback_archive1 ADD TABLESPACE flashback_archive_tbs1 QUOTA 2G;

Change the maximum space that Flashback Data Archive “flashback_archive1” can use in tablespace flashback_archive_tbs1 to 5G
• ALTER FLASHBACK ARCHIVE flashback_archive1 MODIFY TABLESPACE flashback_archive_tbs1QUOTA 5G;

Remove tablespace “flashback_archive_tbs1” from Flashback Data Archive
• ALTER FLASHBACK ARCHIVE flashback_archive1 REMOVE TABLESPACE flashback_archive_tbs1;

Dropping a Flashback Data Archive:-
Remove Flashback Data Archive flashback_archive1 and all its historical data
• DROP FLASHBACK ARCHIVE flashback_archive1;

Enabling and Disabling Flashback Data Archive:-
By default, flashback archiving is disabled for any table and you need FLASHBACK ARCHIVE privilege to enable for any table. After flashback archiving is enabled for a table, you can disable it only if you either have SYSDBA or FLASHBACK ARCHIVE ADMINISTER privilege.

Create table EMP and store the historical data in the DEFAULT Flashback Data Archive
• CREATE TABLE EMP (EMPNO NUMBER(5) NOT NULL, ENAME VARCHAR2(20))
FLASHBACK ARCHIVE;

Create table EMP and store the historical data in the Flashback_Archive 1
• CREATE TABLE EMP (EMPNO NUMBER(5) NOT NULL, ENAME VARCHAR2(20))
FLASHBACK ARCHIVE Flashback_Archive1;

Enable flashback archiving for the table EMP and store data in the default Flashback Data Archive
• ALTER TABLE EMP FLASHBACK ARCHIVE;

Enable flashback archiving for the table EMP and store data in Flashback Data Archive Flashback_Archive1
• ALTER TABLE EMP FLASHBACK ARCHIVE Flashback_Archive1;

Disable flashback archiving for the table employee:
• ALTER TABLE EMP NO FLASHBACK ARCHIVE;

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com