/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

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

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