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)
)
)

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

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

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