/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