Intra-Block(In Exadata Cell) versus Inter-Block row chaining

Oracle database can store only 255 columns in a single row-piece. Anything beyond 255 columns will be stored as a separate row, chained to the main one. This kind of chaining is different from what we know as migrated/chained rows which happen either because of lack of space in current block which causes rows to migrate when updated leaving behind their new address or due to row size more than the block size itself. Both these types of chaining are inter-block row chaining.

intra-block row chaining (as i mentioned above happens due to number of columns exceeding 255) does not cause as much a performance issue as inter-block row chaining. This is because all the row contents could still be found within the same block. When there is a full table scan, DB can easily do scattered reads over this table with number of blocks spanning up to the value for parameter db_file_multiblock_read_count. For scanning rows which are chained to another block, as is the case with migrated/chained rows, DB has to do sequential reads with one block read at a time. This summarily degrades query performance.

Unlike Inter-block row chaining, Intra-block row chaining cannot be detected using ANALYZE TABLE LIST CHAINED ROWS command. However, there is a way to see to what extent the rows are chained. Look out for statistic " table fech continued row". As you access the table with 255+ columns, this value will keep increasing.

 select name, value from v$sysstat where name like 'table fetch %';

Note that if all the columns beyond 255 columns have NULL values, the row will not be chained.

Recover Dropped view's Defination

Unfortunately One of our developer had dropped one important view from the schema. And we were not taking datapump backup for that schemas. Yes we are taking the RMAN full backup but don't want to go that route if I could find easily.

Here was the solution ( assuming database have all that configuration required for "AS OF TIMESTAMP - FLASHBACK" clause )

select * from ( select view_name , text from dba_views where owner='MGR_KX' )
as of timestamp sysdate - 1 ;

and we got required result with view definition.

Cheers!!

Duplicate From Active Database Errors : ORA-17629 and ORA-17627: ORA-12154: Tns:Could Not Resolve

Duplicate From Active Database Errors : ORA-17629 and ORA-17627: ORA-12154: Tns:Could Not Resolve

The duplicate database from active failed with the following errors : 

ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server

Cause 1:

For Active Duplication you need to connect to the target and Auxiliary DB using net service name ,even if you are running RMAN at the Auxiliary DB.
The net service name to connect to the Auxiliary DB should also be configured at the target Database.

In the tnsnames.ora on the machine of the TARGET database, there are 2 services created to connect to auxiliary database that have the same "service name" but "different" configuration.

Possible Cause 2:

Mismatch of Parameters DEFAULT_DOMAIN and DB_DOMAIN

db_domain=PROD.world         # init.ora
default_domain=PROD.WORLD    # sqlnet.ora

# DEFAULT_DOMAIN defined on sqlnet.ora is 'case' sensitive and when defined must match
   DB_DOMAIN initialization parameter

Solution:

1. To be sure that the tnsnames.ora file of each machine (machine where is the source database and machine where is the auxiliary database) has the service name to connect to auxiliary database.

2. Verify that there are not duplicate service_name with different configuration in the tnsnames.ora file.

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

If you try to duplicate database using rman and get following error:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Most probably your auxiliary database (in nomount state) is the only instance works on current ORACLE_HOME. Each instance is registered in listener by PMON process, which starts only when database is at least mounted. If there is nothing registered in listener before, instance in blocked.

You can do 2 things:
1. Modify listener.ora like this:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = AUXDB)
(ORACLE_HOME = /app/oracle/product/10.2.0/)
(SID_NAME = AUXDB)
)
)
OR
2. Modify tnsnames.ora (10g only!) :

AUXDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = AUXDB)(UR=A)
)
)

Complete checklist for out-of-place manual upgrade from previous 11.2.0.N version to the latest 11.2.0.N patchset

Complete checklist for out-of-place manual upgrade from previous 11.2.0.N version to the latest 11.2.0.N patchset.  You need to perform an out of place upgrade. 1. Install 11.2.0.3 on a different home 2. run the pre-upgrade tool on the existing 11.2.0.1 database 3. peform the pre-requisities mentioned 4. Bring down the database from 11.2.0.1 and start the database in upgrade mode from 11.2.0.3 home by copying the pfile/spfile of 11.2.0.1 database to $11.2.0.3_HOME/dbs path  5. startup upgrade and run the catupgrd.sql Make sure you look out for the invalid objects. Refer *Complete checklist for out-of-place manual upgrade from previous 11.2.0.N version to the latest 11.2.0.N patchset. [ID 1276368.1] and Database Upgrade Path Reference List(Doc ID 730365.1) orhttp://docs.oracle.com/cd/E11882_01/server.112/e23633/toc.htm

Oracle GoldenGate database Schema Profile check script for Oracle DB

Oracle GoldenGate database Schema Profile check script for Oracle DB (Doc ID 1296168.1)

PURPOSE

This script is intended to query the database by schema to identify current configuration and identify any unsupported data types or types that may need special considerations for Oracle GoldenGate in an Oracle environment. This is the Oracle database profile script.  Added check for deferred constraints. Deferred constraints may cause ADD TRANDATA to select the wrong column for logging. Use KEYCOLS for tables with deferred constraints.

REQUIREMENTS

This script is to be run in sqlplus by a user that has DBA privliges.

CONFIGURING

Download or Save script file as full-schemaCheckOracle_12072011.sql

INSTRUCTIONS

Log into sqlplus as sysdba

run the script -

SQL> @full-schemaCheckOracle_12072011.sql

Review output.

Virtual Box – Shared Folders in Oracle Enterprise Linux as a guest OS and Windows as Host

 It should be work with most Linux environments, but for our case is tested on Oracle Enterprise Linux.

I was trying to install a Oracle Database in a VBOX with OEL Linux as a guest and then I realized that the external disk are no detected automatically.

Then after some research I found what we have to do. The steps below are what I did to enable it.

Pre-requisite -- VBOX Additional Guest Tool must be installed

1. In VirtualBox Add the disk/Folder to be shared. Devices → Shared Folders

lsmod   vboxvfs must be loaded in memory(see below image). If not, please verify that Guest additions were installed on the Operating System Guest. If so, please load the vboxvfs.

[root@db12c d_soft]# /sbin/modprobe vboxvfs

Job done !

VirtualBox 4.2.14 Guest Additions Building the main Guest Additions module [FAILED]

Add caption

ORA-22924: snapshot too old

ORA-22924: snapshot too old ( While Exporting LOBs)

I have migration projects to migrate one particular Schema from 10g(Windows) to 11g(Linux). And the schema size was 100gb as one of the table got LOB Columns. But while exporting this schema to 10g datapump utility , we keep hitting following errors. The investigation result requires modification in database.

ORA-31693: Table data object "HPADMIN"."T7751" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old 

I instantly checked the undo tablespace whether it was undersized or not. It was set to "auto extend on" as expected. Then I checked out the undo retention parameter-

SQL> show parameter undo_retention

It was set to default 900. I was thinking to modify it but was not so sure as oracle supporting documents were not available at hand.

Later, when I  found that the error comes for the LOB segment undo problem. When data is modified, oracle keeps a version of the data in undo segment for read consistency.

I had two options-

1. Increase PCTVERSION in LOB table columns

2. Increase RETENTION in LOB table columns

1. PCTVERSION is the percentage of all used LOB data space that can be occupied by old versions of LOB data pages. As soon as old versions of LOB data pages start to occupy more than the PCTVERSION amount of used LOB space, Oracle Database tries to reclaim the old versions and reuse them. In other words, PCTVERSION is the percent of used LOB data blocks that is available for versioning old LOB data.

PCTVERSION has a default of 10 (%), a minimum of 0, and a maximum of 100. Setting PCTVERSION to twice the default value allows more free pages to be used for old versions of data pages. Because large queries may require consistent reads of LOB columns, it may be useful to retain old versions of LOB pages. In this case, LOB storage may grow because the database will not reuse free pages aggressively. PCTVERSION=0; the space allocated for older versions of LOB data in LOBSEGMENTS can be reused by other transactions and can cause "snapshot too old" errors.

2. As an alternative to the PCTVERSION parameter, one can specify the RETENTION in CREATE TABLE or ALTER TABLE statement. Doing so, configures the LOB column to store old versions of LOB data for a parameter in the LOB storage clause of the period of time, rather than using a percentage of the table space.

DBA Querty to check LOB Parameters

select column_name, nvl(retention, 0) retention, pctversion from dba_lobs where table_name = 'TABLE_NAME';

I have planned to go with option 2 -

ALTER SYSTEM SET UNDO_RETENTION=2700 scope=both;

ALTER TABLE TABLE_NAME MODIFY LOB (COLUMN_NAME) (RETENTION);

The LOB RETENTION will take value from UNDO_RETENTION parameter.

But it didn't work, And end up with same error. So I had to go with Option 1.

ALTER TABLE TABLE_NAME MODIFY LOB (COLUMN_NAME) (PCTVERSION 20);

and guess what  ??  it works !!

Summary -

Cause: The version of the LOB value needed for the consistent read was already overwritten by another writer.

Action: Use a larger version pool/retention time.

One very important article of Tom Kyte -

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:275215756923

http://www.oracle.com/technetwork/articles/lob-performance-guidelines-128437.pdf

LOB RETENTION

In the past, one would use the PCTVERSION storage parameter for their LOB segments to reserve a percentage of storage space for read consistency of LOB segments. In Oracle 11g, you can now use the RETENTION parameter. The RETENTION parameter will use the UNDO_RETENTION parameter for determining how long to keep LOB data for read-consistency purposes. But be advised that it does not use the Undo tablespace! The LOB segment’s tablespace is used for read-consistency purposes.

When you change the UNDO_RETENTION parameter, the LOB segment’s retention value is not modified. If you query the RETENTION column of the DBA_LOBS view, you will notice the old UNDO_RETENTION value still remains after you have modified the UNDO_RETENTION parameter. To change the LOB segment’s RETENTION value to match the new UNDO_RETENTION value, do the following:

ALTER TABLE my_table MODIFY LOB (lob_column) (PCTVERSION 20);
ALTER TABLE my_table MODIFY LOB (lob_column) (RETENTION);

By momentarily changing the LOB to use PCTVERSION and then back to RETENTION, the new value of UNDO_RETENTION will be used. You need to do this for all LOB segments that you intend to modify.

Oracle database 12c New Feature Highlight

Oracle Database 12c New Feature Highlight......

                                                            

Move SPFILE from one ASM diskgroup to another (ORACLE RAC)

##
## "Move" spfile from:
##    +OLDLOC/DBNAME/spfileDBNAME.ora
##  to:
##    +NEWLOC/DBNAME/spfileDBNAME.ora
##

## NOTE:
##   in this example there are FOUR nodes:
##      rac1
##      rac2
##      rac3
##      rac4


## run all steps from first node
##
## "copy" spfile to +NEWLOC
sqlplus /nolog
connect / as sysdba
create pfile='/u01/app/oracle/admin/DBNAME/scripts/init_new.ora' from spfile='+OLDLOC/DBNAME/spfileDBNAME.ora';
create spfile='+NEWLOC/DBNAME/spfileDBNAME.ora' from pfile='/u01/app/oracle/admin/DBNAME/scripts/init_new.ora';
exit

## modify init*.ora files to point to new location
##
echo "SPFILE='+NEWLOC/DBNAME/spfileDBNAME.ora'" > /u01/app/oracle/product/10.2.0/db_1/dbs/initDBNAME1.ora
ssh rac2 "echo \"SPFILE='+NEWLOC/DBNAME/spfileDBNAME.ora'\" > /u01/app/oracle/product/10.2.0/db_1/dbs/initDBNAME2.ora"
ssh rac3 "echo \"SPFILE='+NEWLOC/DBNAME/spfileDBNAME.ora'\" > /u01/app/oracle/product/10.2.0/db_1/dbs/initDBNAME3.ora"
ssh rac4 "echo \"SPFILE='+NEWLOC/DBNAME/spfileDBNAME.ora'\" > /u01/app/oracle/product/10.2.0/db_1/dbs/initDBNAME4.ora"

## modify OCR with new SPFILE location
##
export ORACLE_HOME=$CRS_HOME
export PATH=$ORACLE_HOME/bin:$PATH
srvctl modify database -d DBNAME -p +NEWLOC/DBNAME/spfileDBNAME.ora

## bounce ALL instances to switch to new SPFILE
##
export ORACLE_HOME=$CRS_HOME
export PATH=$ORACLE_HOME/bin:$PATH
srvctl stop database -d DBNAME
srvctl start database -d DBNAME

## all services will be down because DB got bounced -- restart it
##
srvctl start service -d DBNAME



## drop old spfile
##
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/asm
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=+ASM1
sqlplus /nolog
connect / as sysdba
ALTER DISKGROUP OLDLOC DROP FILE '+OLDLOC/DBNAME/spfileDBNAME.ora';
exit

## verify "old" spfile is gone from OLDLOC
##
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/asm
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=+ASM1
asmcmd
ASMCMD> find / *spfile*
+NEWLOC/DBNAME/PARAMETERFILE/spfile.259.667587125
+NEWLOC/DBNAME/spfileDBNAME.ora

Move/Copy controlfile from file system to ASM

Current Controlfie location (using Init.ora file here)

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ cat initTEST11G.ora

*.control_files='/cluster/app/grid/product/11.2.0/db_1/dbs/control01.ctl'

Change to new location in ASM with random controlfile number, that will changed later by RMAN

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ vi initTEST11G.ora

*.control_files='+DATA/TESTDB/controlfile/current.261.772293749','+DATA/TESTDB/controlfile/current.260.772293749'

Startup database with nomount

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 26 16:55:43 2013

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

Connected to an idle instance.

SYS@TESTDB >> startup nomount;

ORACLE instance started.

Total System Global Area 3340451840 bytes

Fixed Size                  2231088 bytes

Variable Size            2499806416 bytes

Database Buffers          822083584 bytes

Redo Buffers               16330752 bytes

SYS@TESTDB >> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Connect Target db with RMAN and restore controlfile from original location

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Jun 26 16:56:24 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (not mounted)

RMAN> restore controlfile from '/cluster/app/grid/product/11.2.0/db_1/dbs/control01.ctl';

Starting restore at 26-JUN-2013 16:56:34

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=132 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=+DATA/TESTDB/controlfile/current.261.819132997

output file name=+DATA/TESTDB/controlfile/current.260.819132997

Finished restore at 26-JUN-2013 16:56:39

RMAN> exit

Recovery Manager complete.

Confirm location of new controlfile

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ s

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 26 16:57:04 2013

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, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SYS@TESTDB >> alter database mount;

Database altered.

SYS@TESTDB >> show parameter controlfile

SYS@TESTDB >> show parameter control

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time        integer     7

control_files                        string      +DATA/TESTDB/controlfile/cur

                                                 rent.261.819132997, +DATA/test

                                                 sits/controlfile/current.260.8

                                                 19132997

control_management_pack_access       string      DIAGNOSTIC+TUNING

SYS@TESTDB >> alter database open;

Database altered.

SYS@TESTDB >> exit 

Modified Init.ora files with new controlfile location that created by RMAN , and restart the database.

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ vi initTEST11G.ora

*.control_files='+DATA/TESTDB/controlfile/current.261.819132997','+DATA/TESTDB/controlfile/current.260.819132997'

SYS@TESTDB >> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@TESTDB >> startup

ORACLE instance started.

Total System Global Area 3340451840 bytes

Fixed Size                  2231088 bytes

Variable Size            2499806416 bytes

Database Buffers          822083584 bytes

Redo Buffers               16330752 bytes

Database mounted.

Database opened.

Error connecting ASM ORA-15055: unable to connect to ASM instance Fatal NI connect error 12547 ORA-12547: TNS:lost contact

Error connecting ASM ORA-15055: unable to connect to ASM instance Fatal NI connect error 12547 ORA-12547: TNS:lost contact

This Blog is intended to resolve below errors observed in DB alert log file due connectivity issue between DB Instance & ASM disk/Instance

Errors:

Fatal NI connect error 12547, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/u01/app/grid/product/11.2.0/grid/bin/oracle)(ARGV0=oracle+ASM_asmb_remdev)(ENVS='ORACLE_HOME=/u01/app/grid/product/11.2.0/grid,ORACLE_SID=+ASM')(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))'))(enable=setuser)(CONNECT_DATA=(CID=(PROGRAM=oracle)(HOST=remedy-ebu-dev-db1)(USER=ora11g))))
TNS-12547: TNS:lost contact
    ns secondary err code: 12560
    nt main err code: 517
ORA-15055: unable to connect to ASM instance
ORA-12547: TNS:lost contact
TNS-12545: Connect failed because target host or object does not exist
ERROR: Failed to connect with connect string: (DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=stance
ORA-12547: TNS:lost contact
,ORACLE_SID=055: unable to connect to ASM instance

Cause:

$GRID_HOME/bin/oracle or $ORACLE_HOME/bin/oracle permission has got changed

ls -al $GRID_HOME/bin/oracle
-rwxr-x--x 1 grid oinstall 200678464 Feb 28 14:54 oracle

ls -al $ORACLE_HOME/bin/oracle
-rwxr-x--x 1 oracle asmadmin 228886191 Feb 28 15:41 oracle

Solution:

Change permissions as below

cd $GRID_HOME/bin
chmod 6751 oracle

cd $ORACLE_HOME/bin
chmod 6751 oracle

it should look like having sticky bit ..

ls -l $GRID_HOME/bin/oracle
-rwsr-s--x 1 grid oinstall 203974257 May 11 09:30 oracle

ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle oinstall 232399431 May 14 13:47 oracle

or 

ls -l $GRID_HOME/bin/oracle*

-rwxr-x--- 1 oracle oinstall         0 Sep  5  2010 oracleO*

-rwsr-s--x 1 oracle asmadmin 228886468 Jan 11  2012 oracle*

ls -l $ORACLE_HOME/bin/oracle*

-rwxr-x--- 1 oracle oinstall         0 Sep  5  2010 oracleO*

-rwsr-s--x 1 oracle asmadmin 228886468 Jan 11  2012 oracle*

Restart the ASM and DB instance after changing permission  .. DB will come up

Listener Fails to start TNS-12557,12560,00527 , ORA-12547: TNS:lost contact -- Windows Environment

We have faced very wired issue with Oracle database 11g Listener in Windows environment. For some reason 

it's not starting up and giving  following errors. We have tried all the solutions to resolve this issue, like starting/stopping listener through different Oracle Home, configuring timeout in sqlnet.ora , amending hosts files,creating another listener through netca , even try installing new oracle client with different ORACLE_HOME, but none of this solutions helped. 

But one thing i forget to mentioned here , we have also got another Oracle product on same machine.

WEBLOGIC  10.3.3
FMW 11.1.1.3 

It mean Our Windows Registry have some many key/value for the Oracle product. And we didn't have any clue what was causing problem.

But at last we tried one very simple thing and it works. That I have mentioned in Solution part. 

And guess what , it works !! 

And therefor I guess, one good Oracle DBA hates Windows for their databases.

Errors

TNSListener fails to startup.

Errors include

ORA-12547: TNS:lost contact

TNS-12557: TNS:protocol adapter not loadable

TNS-12560: TNS:protocol adapter error

TNS-527: Protocol Adapter not loadable

Also seen with

Failed to start service, error 997.
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error

Solution

Remove the ORACLE_HOME system environment variable.
From Start button, navigate to Control Panel.  Then select the System icon and click the Advanced tab.
Click the Environment Variables button
Find the ORACLE_HOME in the system environment variables and delete it.
Re-boot the machine.

RMAN has deleted backupset but not freed up space to OS

Hi,

I am having strange issue in our production system. And it caused to fail rman subsequent backup. 
The issue was previous deleted rman backupset file still being access by other process. And to resolve this issue lsof +L1 become boon to me. 

Here I have resolve the issue.

[root@PRODB01 backupset]#
[root@PRODB01 backupset]# df -h /flash_recovery_area/
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg1
                      1.5T  1.1T  343G  77% /flash_recovery_area

[root@PRODB01 backupset]# cd /flash_recovery_area/
data_pump/  PRODDB/   lost+found/

[root@PRODB01 backupset]# cd /flash_recovery_area/

[root@PRODB01 flash_recovery_area]# du -sh *
337G    data_pump
386G    PRODDB
16K     lost+found

[root@PRODB01 flash_recovery_area]# lsof +L1 /flash_recovery_area | grep backupset
rsync     25269    root    3r   REG 253,10 396905644032     0  83329027 /flash_recovery_area/PRODDB/backupset/2013_05_28/o1_mf_nnndf_BACKUP_LIVEDB_8t9txcvw_.bkp (deleted)



[root@PRODB01 flash_recovery_area]#
[root@PRODB01 flash_recovery_area]# ps -ef | grep 25269
root      8980 14727  0 11:55 pts/6    00:00:00 grep 25269
root     25269 25242  0 May28 ?        00:27:58 rsync --verbose --archive --hard-links --force --delete --stats -progress --links --times --recursive --relative --exclude /proc --exclude /dev --exclude /mnt --exclude /rman2 --exclude /a --exclude /vol --exclude /tmp --exclude /rman --exclude /willow \ --exclude /var/spool/lpd --delete-excluded --exclude core --ignore-errors --partial --backup --backup-dir=/scorpio/scorpio9/PRODB01/28-05-13 -e ssh -v / scorpio:/scorpio/scorpio9/PRODB01/current
root     25277 25269  3 May28 ?        02:26:35 ssh scorpio rsync --server -vvblHogDtprRe.is --backup-dir /scorpio/scorpio9/PRODB01/28-05-13 --delete-excluded --force --partial --ignore-errors . /scorpio/scorpio9/PRODB01/current

[root@PRODB01 flash_recovery_area]# kill -9 25269
[root@PRODB01 flash_recovery_area]# df -h /flash_recovery_area/
 1.5T  744G  691G  52% /3parrman

Job Done ! Space revert it back !

Prereq check warning for missing rpm package libstdc++43 on SUSE 11

on SuSE 11 SP1 - 11.2.0.3 Grid Installation - ./runInstaller and prereq check is failing because compat-libstdc++-33.3.2.3-47.3 is not installed.

on SuSE 11 SP2 - 11.2.0.3 Grid Installation - ./runInstaller and prereq check is failing because the following packages are not installed:-

 Package: compat-libstdc++-33-3.2.3-47.3
 Package: libstdc++43-4.3.4_20091019-0.7.35 (s390x)
 Package: libgcc43-4.3.4_20091019-0.7.35.

This prereq issue is also seen on generic Linux SuSE 11 platforms.

Changes

SuSE 11 SP1 SP2
11.2.0.3

Cause

This is documented in unpublished Bug 13445763 and Bug 14366019
The rpm compat-libstdc++-33.3.2.3-47.3 is not available on  SuSE 11. The rpm libstdc++-33 provides the required files.  Both the 32-bit version and 64-bit versions are required.
This is the same as the 11.2.0.2 requirements for this platform.

On SuSE 11 SP2 the libstdc++43 and libgcc43 checks fail as the rpm has changed to libstdc++46 but providing both the 32-bit version and 64-bit versions of libstdc++43-devel rpms are installed - this is not a problem.

Solution

You can safely ignore these warnings - click on ignore button in the top right hand corner of the screen.

You should, however, ensure that you meet all the other rpm prereqs.  To assist you, please refer to Getting Started - 11gR2 Grid Infrastructure,SI(Single Instance), ASM and DB (IBM: Linux on System z) Document 1306465.1 - specifically the rpm validation section.

For SLES11 - you should download the attachment at the end of the above article:

- SLES 11 - 11.2. Grid Infrastructure, SIHA, DB Install

To assist further - here is the "rpm -qa" output from a SuSE 11 SP2 in-house system which resulted in a successful installation of 11.2.0.3 Grid Infrastructure.:-

zhost:/home/oracle # rpm -qa | grep libstdc
libstdc++46-32bit-4.6.1_20110701-0.13.9
libstdc++43-devel-4.3.4_20091019-0.22.17
libstdc++33-3.3.3-11.9
libstdc++46-4.6.1_20110701-0.13.9
libstdc++33-32bit-3.3.3-11.9
libstdc++43-devel-32bit-4.3.4_20091019-0.22.17
libstdc++-devel-4.3-62.198

zhost:/home/oracle # rpm -qa | grep libgcc
libgcc46-32bit-4.6.1_20110701-0.13.9
libgcc46-4.6.1_20110701-0.13.9

 which gcc
/usr/bin/gcc
zhost:/home/oracle # gcc --version
gcc (SUSE Linux) 4.3.4 [gcc-4_3-branch revision 152973]
Copyright (C) 2008 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

zhost:/home/oracle/sjc # c++ --version
c++ (SUSE Linux) 4.3.4 [gcc-4_3-branch revision 152973]
Copyright (C) 2008 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

TROUBLESHOOTING GUIDE (TSG) - ORA-20: MAXIMUM NUMBER OF PROCESSES (%S) EXCEEDED [ID 1287854.1]

Applies to:

Oracle Server - Enterprise Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Information in this document applies to any platform.

Symptoms

What are the recommended steps to diagnose an ORA-20 - maximum number of processes (%s) exceeded?

Cause

Connections to the database have caused the current number of operating system processes associated with the Oracle Instance to exceeed the PROCESSES database parameter

Solution

The typical remedy to an ORA-20 - maximum number of processes (%s) exceeded ... is to increase the PROCESSES parameter ... This is usually only a temporary solution

Here are the steps to diagnose the cause of the ORA-20

1) The first step is to gather information about the processes that exist when the ORA-20 occurs

connect ... as sysdba

set markup html on
set pagesize 30
spool processes_sessions.html

select
p.username "V$PROCESS - OS USERNAME",
p.terminal,
p.program,
s.username "V$SESSION - USERNAME",
s.command,
s.status,
s.server,
s.process,
s.machine,
s.port,
s.terminal,
s.program,
s.sid,
s.serial#,
p.spid
FROM v$session s,v$process p
WHERE p.addr=s.paddr
order by p.background desc;

spool off
exit;NOTE .. depending on version ... S.PORT may need to be removed from the query 

2) Look for patterns in the output generated in step #1 (processes_sessions.html)SAMPLE OUTPUT

V$PROCESS - OS USERNAME	TERMINAL	PROGRAM	V$SESSION - USERNAME	COMMAND	STATUS	SERVER	PROCESS	MACHINE	PORT	TERMINAL	PROGRAM
oracle	UNKNOWN	oracle@filnx10 (TNS V1-V3)	SYS	3	ACTIVE	DEDICATED	27140	filnx10	0	pts/2	sqlplus@filnx10 (TNS V1-V3)
oracle	UNKNOWN	oracle@filnx10 (TNS V1-V3)
oracle	UNKNOWN	oracle@filnx10 (Q001)		0	ACTIVE	DEDICATED	24739	filnx10	0	UNKNOWN	oracle@filnx10 (Q001)
oracle	UNKNOWN	oracle@filnx10 (VKTM)		0	ACTIVE	DEDICATED	24534	filnx10	0	UNKNOWN	oracle@filnx10 (VKTM)
oracle	UNKNOWN	oracle@filnx10 (GEN0)		0	ACTIVE	DEDICATED	24538	filnx10	0	UNKNOWN	oracle@filnx10 (GEN0)
oracle	UNKNOWN	oracle@filnx10 (DIAG)		0	ACTIVE	DEDICATED	24540	filnx10	0	UNKNOWN	oracle@filnx10 (DIAG)
oracle	UNKNOWN	oracle@filnx10 (DBRM)		0	ACTIVE	DEDICATED	24542	filnx10	0	UNKNOWN	oracle@filnx10 (DBRM)
oracle	UNKNOWN	oracle@filnx10 (DIA0)		0	ACTIVE	DEDICATED	24544	filnx10	0	UNKNOWN	oracle@filnx10 (DIA0)
oracle	UNKNOWN	oracle@filnx10 (MMAN)		0	ACTIVE	DEDICATED	24546	filnx10	0	UNKNOWN	oracle@filnx10 (MMAN)
oracle	UNKNOWN	oracle@filnx10 (DBW0)		0	ACTIVE	DEDICATED	24548	filnx10	0	UNKNOWN	oracle@filnx10 (DBW0)
oracle	UNKNOWN	oracle@filnx10 (LGWR)		0	ACTIVE	DEDICATED	24550	filnx10	0	UNKNOWN	oracle@filnx10 (LGWR)
oracle	UNKNOWN	oracle@filnx10 (CKPT)		0	ACTIVE	DEDICATED	24552	filnx10	0	UNKNOWN	oracle@filnx10 (CKPT)
oracle	UNKNOWN	oracle@filnx10 (SMON)		0	ACTIVE	DEDICATED	24554	filnx10	0	UNKNOWN	oracle@filnx10 (SMON)
oracle	UNKNOWN	oracle@filnx10 (RECO)		0	ACTIVE	DEDICATED	24556	filnx10	0	UNKNOWN	oracle@filnx10 (RECO)
oracle	UNKNOWN	oracle@filnx10 (MMON)		0	ACTIVE	DEDICATED	24558	filnx10	0	UNKNOWN	oracle@filnx10 (MMON)
oracle	UNKNOWN	oracle@filnx10 (MMNL)		0	ACTIVE	DEDICATED	24560	filnx10	0	UNKNOWN	oracle@filnx10 (MMNL)
oracle	UNKNOWN	oracle@filnx10 (QMNC)		0	ACTIVE	DEDICATED	24576	filnx10	0	UNKNOWN	oracle@filnx10 (QMNC)
oracle	UNKNOWN	oracle@filnx10 (SMCO)		0	ACTIVE	DEDICATED	26415	filnx10	0	UNKNOWN	oracle@filnx10 (SMCO)
oracle	UNKNOWN	oracle@filnx10 (W000)		0	ACTIVE	DEDICATED	26417	filnx10	0	UNKNOWN	oracle@filnx10 (W000)
oracle	UNKNOWN	oracle@filnx10 (CJQ0)		0	ACTIVE	DEDICATED	24617	filnx10	0	UNKNOWN	oracle@filnx10 (CJQ0)
oracle	UNKNOWN	oracle@filnx10 (Q000)		0	ACTIVE	DEDICATED	24737	filnx10	0	UNKNOWN	oracle@filnx10 (Q000)
oracle	UNKNOWN	oracle@filnx10 (PMON)		0	ACTIVE	DEDICATED	24530	filnx10	0	UNKNOWN	oracle@filnx10 (PMON)
oracle	UNKNOWN	oracle@filnx10 (PSP0)		0	ACTIVE	DEDICATED	24532	filnx10	0	UNKNOWN	oracle@filnx10 (PSP0)

     * If there are many V$SESSION.STATUS = INACTIVE ...

            This means that there are many user sessions that have connected but are not doing anything

            POSSIBLE SOLUTION : Enable dead connection detection (DCD) and user resource limits

                    A discussion of Dead Connection Detection, Resource Limits, V$SESSION, V$PROCESS and OS processes (Doc ID 601605.1)

     * If there are many V$SESSION.PROGRAM (and possibly many V$SESSION.STATUS = INACTIVE) entries from the same program ...

            POSSIBLE SOLUTIONS :

            1) Examine the program (often a web server) to see if the program has a setting to abandon a connection after X number of seconds and reconnect (a respawn event)

                If the program has such a setting ... a slowdown in either network or database performance should to be investigated ... as this would cause a timeout in the application .. 
                  and thus a reconnect ... setting a longer 'timeout' in the application often will resolve future occurrences of ORA-20

             2) Like the solution above ... setting DCD and Resource Limits ... often resolves these as well 

     * If there are many rows with data for V$PROCESS but no data for V$SESSION ...

            This is a problem on the operating system side and such sessions will need to be killed manually with KILL -# (UNIX / LINUX) or ORAKILL (Windows)

             This condition needs to be examined by an operating system expert (System Administrator or OS Vendor)

             WHY? - When an Oracle session is terminated ... whether by the user logging out ... a kill session by a privileged user ... or even the user reaching a resource limit set in their profile
                           Oracle SMON (or PMON) will first clean up the database resources (rollback transactions ... release locks etc)
                           Oracle then will remove the entry in V$SESSION as the session is now 'cleaned up'
                           Oracle will then request that the operating system terminate any OS processes associated with the former Oracle Session

               The Oracle Kernel cannot force the OS to terminate the processes .. this occurs at the OS level only ... all it can do is request that they be terminatedNOTE: This is a living document ... Oracle encourages comments as to other solutions / scenarios ... so please feel free to add a remark

References

NOTE:1050281.1 - Getting ORA-00020/ ORA-00018 With A High Number Of CPUs Regardless Of How High The Related DB Parameters Are Set.
NOTE:169706.1 - Oracle Database (RDBMS) on Unix AIX,HP-UX,Linux,Mac OS X,Solaris,Tru64 Unix Operating Systems Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)
NOTE:458527.1 - ORA-00020 on an ASM instance
NOTE:601605.1 - A discussion of Dead Connection Detection, Resource Limits, V$SESSION, V$PROCESS and OS processes