Sunday, 22 June 2014

ORA 12203 "TNS:unable to connect to destination"

Error Cause: 

 Invalid TNS address supplied or destination is not listening.
        This error can also occur because of underlying network transport
        problems.

Action Plan: 

Verify that the service name you entered on the command line
        as correct. Ensure that the listener is running at the remote node and
        that the ADDRESS parameters specified in TNSNAMES.ORA are correct.
        Finally, check that all Interchanges needed to make the connection are
        up and running.
1) Verify that you have entered the net service name you wish to reach correctly.
     2) Verify that the net service name ADDRESS parameters in the connect
        descriptor of your TNSNAMES.ORA file are correct.
     3) Verify that your TNSNAMES.ORA file is stored in the correct directory.
     4) Verify that the listener on the remote node has started and is running. If not,
        start the listener by using the Listener Control Utility.
     5) If you are connecting from a login box, verify that you are not placing an "@"
        symbol before your connect net service name.
Explanation:

        A client tool could not establish a connection over SQL*Net 
        to the named destination.

Note:

        Check for a client side log file (sqlnet.log) which reports the
        full error stack. The underlying error/s on the error stack may
        help identify the cause of the problem.

        If this is Windows NT and you get this when you go into Server Manager
        and also when you try and do connect internal make sure there is a
        service called OracleServiceSID and that it is started. If it does not
        exist use the ORADIM utility to create it.
    
        (Also check that your SID is set correctly in the registry.)
 
     

Friday, 20 June 2014

SID is already exist

I tried to create database, but I've got an error message that "SID is already exist". 

Window OS:-

just remove old sid by

$ oradim -delete -sid sidname

ORADIM is an Oracle utility that exists only on Windows: you cannot use it on UNIX systems such as AIX.


Cause: You have probably previously created database with the same name and have removed it but the traces of it still remain 

Linux OS :-

Open /etc/oratab file in edit mode and remove the line containing SID that is causing an error message.

 Another place to look would be $ORACLE_BASE/oraInventory/ContentsXML/inventory.xml - this file could also contain an entry with the offending SID..




Thursday, 19 June 2014

ORA-39071: Value for EXCLUDE is badly formed

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
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00920: invalid relational operator



Solution:-

This is SHELL issue,
solution is to use format : "exclude=TABLE:\"IN ('DATA')\""

Thursday, 12 June 2014

How to Refresh Database from PROD to TEST.




Database refresh is performed from Source Database to Target Database .

There are two types of Database Refresh.
           .  
Cold Refresh
Cold Database refresh is done from staging to Development or Vice-verse (Source will be down during the refresh since archive log is not enabled and we cannot put the database in hot backup mode).

Hot Refresh

Hot database refresh is done from Production databases to staging or development databases.( Source database will be in archive log mode and database will be up and running during the refresh).


How to Refresh Database from PROD to TEST Database.???

Steps:-1

Check Both Server Parameter and compare it. We need to collect below Parameter.

Server Parameter
PROD server (Source)
Test server (Target)
IP/Host
119.5.9.90/PROD.oracle.com
119.5.9.124/ TEST.oracle.com
Database name
IDEA

AIRTEL
Database size
89.2314453 GB
114.29248 GB

OS
SunOS

SunOS
Mount Point space
df -h
df -h

Database version
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production











Steps:-2

How to check upper Server Parameter??

Use below command. Check both server Parameter Source as well as Target.


Check Database name:-

SQL> select name from v$database;

NAME
---------
IDEA

Check Host name:-

oracle@PROD:~$ hostname

PROD.oracle.com

Check Database size:-

SQL> select sum(bytes / (1024*1024*1024)) "DB Size in GB" from dba_data_files;

DB Size in GB
-------------
   89.2314453

Check OS version:-

oracle@PROD:~$ uname

SunOS

oracle@drbrac1p:~$ uname -a

SunOS PROD.oracle.com 5.11 11.1 i86pc i386 i86pc


Steps:-3

Find IMP directory after that give read write permission.

SQL> select * from dba_directories;

SQL> grant read,write on directory FPROD2P_IMP to system;

Grant succeeded.

Steps:-4

Run on PROD Database (Export Full Database)


$ expdp system/SYSfprod2p#1944 full=y directory=PROD_IMP  dumpfile=PROD_exp_12062014.dmp logfile=PROD_exp_12062014.log



Steps:-5

Verify the Data in Source and Target Databases.

Note: In oracle 11g version: 11.2.0.1.0 there are about 44 distinct object_types comparing to previous versions this number is huge.


SQL> select *from v$version;

SQL> select distinct object_type from dba_objects;

SQL> select count(*) from dba_tables;

SQL> select count(*) from dba_users;



Steps:-6

Copy .dmp  file source to Target.

oracle@PROD:/IDEA/Backups$ scp -r PROD_exp_12062014.dmp oracle@119.5.9.124:/AIRTEL/Backups



Wednesday, 11 June 2014

How to Tune Library Cache in Oracle 11g.


Library Cache Tuning
Library Cache is used to store SQL statements and PL/SQL block that are to be shared by users. Those stored SQL statements and PL/SQL block are managed within Library Cache according to the least recently used (LRU) algorithm. Library cache prevents reparsing of the statements. The diagonostic information about the tuning Library Cache can be queried on views as v$librarycache, v$sqlarea, v$sql , v$sqltext, v$db_object_cache. While these views are affected by the parameter like
 SHARED_POOL_SIZE,
OPEN_CURSORS,
SESSION_CACHED_CURSORS,
CURSOR_SPACE_FOR_TIME,
CURSOR_SHARING,
SHARED_POOL_RESERVED_SIZE.

Some of important column from v$librarycache.
  • GETS: Show the total number of requests for information on the corresponding item
  • PINS: Show the number of executions of SQL statements or procedures
  • RELOADS: Show the number of times, during the execution phase, that the shared SQL area containing the parsed representation of the statement is aged out of the library cache to make room for another statement. The oracle server implicitly reloads the statement and parses it again.
  • INVALIDATIONS: show the number of statements that have been made invalid due to the modification of a dependent object. Invalidations also cause reloads.
Tuning Tips
The get hit ratio of the v$librarycache view should be in the high 90s in OLTP. If not so, improve efficiency of your application code or increase the size of shared pool
SQL> SELECt get hitratio
      FROM v$librarycache
     WHERE namespace='SQL AREA';
Library Cache Reloads should be less than 1% of the pins, if the reloads to pin is greater than 1%, increase the value of the SHARED_POOL_SIZE.


SQL> SELECT SUM(pins) as "Executions",
  2              SUM(reloads) as  "Cache Misses",
  3              SUM(reloads) /SUM(pins)  as "Hit Ratio"
  4         FROM v$librarycache;

Executions Cache Misses  Hit Ratio
---------- ------------ ---------- ---------------
   6348913             26666         .004200089



Collecting Library Cache Statistics

to collect statistics query the V$LIBRARYCACHE table over a period of time with this query:

SELECT SUM(pins) "Executions",
   SUM(reloads) "Cache Misses while Executing"
   FROM v$librarycache;

Executions Cache Misses while Executing
---------- ----------------------------
    320871                          549


If the library cache ratio is over 1t,
You should add to the shared_pool_size.
OR
If below parameter already set than need to set next Parameter.
cursor_sharing= similar
open_cursors= 3000
session_cached_cursors= 100
OR
CURSOR_SHARING=FORCE
SESSION_CACHED_CURSOR=300
Certaily you can dynamicaly do CURSOR_SHARING=FORCE
ALTER SYSTEM SET CURSOR_SHARING=FORCE SCOPE=BOTH;
v$sesstat also provides a statistic to monitor the number of cursors each session has in its session cursor cache.
Session cached cursors, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = ‘session cursor cache count’;

Session cached cursor usage.
select a.value curr_cached, p.value max_cached, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s, v$parameter2 p
where a.statistic# = b.statistic#  and s.sid=a.sid
and p.name=’session_cached_cursors’
and b.name = ‘session cursor cache count

Here is a script for measuring the library cache hit ratio:
SELECT 'Buffer Cache' NAME,
ROUND ( (congets.VALUE + dbgets.VALUE - physreads.VALUE)
* 100
/ (congets.VALUE + dbgets.VALUE),
2
) VALUE
FROM v$sysstat congets, v$sysstat dbgets, v$sysstat physreads
WHERE congets.NAME = 'consistent gets'
AND dbgets.NAME = 'db block gets'
AND physreads.NAME = 'physical reads'
UNION ALL
SELECT 'Execute/NoParse',
DECODE (SIGN (ROUND ( (ec.VALUE - pc.VALUE)
* 100
/ DECODE (ec.VALUE, 0, 1, ec.VALUE),
2
)
),
-1, 0,
ROUND ( (ec.VALUE - pc.VALUE)
* 100
/ DECODE (ec.VALUE, 0, 1, ec.VALUE),
2
)
)
FROM v$sysstat ec, v$sysstat pc
WHERE ec.NAME = 'execute count'
AND pc.NAME IN ('parse count', 'parse count (total)')
UNION ALL
SELECT 'Memory Sort',
ROUND ( ms.VALUE
/ DECODE ((ds.VALUE + ms.VALUE), 0, 1, (ds.VALUE + ms.VALUE))
* 100,
2
)
FROM v$sysstat ds, v$sysstat ms
WHERE ms.NAME = 'sorts (memory)' AND ds.NAME = 'sorts (disk)'
UNION ALL
SELECT 'SQL Area get hitrate', ROUND (gethitratio * 100, 2)
FROM v$librarycache
WHERE namespace = 'SQL AREA'
UNION ALL
SELECT 'Avg Latch Hit (No Miss)',
ROUND ((SUM (gets) - SUM (misses)) * 100 / SUM (gets), 2)
FROM v$latch
UNION ALL
SELECT 'Avg Latch Hit (No Sleep)',
ROUND ((SUM (gets) - SUM (sleeps)) * 100 / SUM (gets), 2)
FROM v$latch;

Cursor related views

SELECT view_name
FROM dba_views
WHERE view_name LIKE '%CURSOR%'
ORDER BY 1;
SQL> select name from v$database;
NAME
---------
idea
SQL> show parameter cursor
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_bind_capture_destination      string                           memory+disk
cursor_sharing                                        string                           FORCE
cursor_space_for_time                        boolean                          FALSE
open_cursors                                            integer                          600
session_cached_cursors                          integer                          50
SQL>
SQL> SELECT view_name
  2  FROM dba_views
  3  WHERE view_name LIKE '%CURSOR%'
  4  ORDER BY 1;
VIEW_NAME
------------------------------
GV_$OPEN_CURSOR
GV_$SESSION_CURSOR_CACHE
GV_$SQL_CURSOR
GV_$SQL_SHARED_CURSOR
GV_$SYSTEM_CURSOR_CACHE
V_$OPEN_CURSOR
V_$SESSION_CURSOR_CACHE
V_$SQL_CURSOR
V_$SQL_SHARED_CURSOR
V_$SYSTEM_CURSOR_CACHE

10 rows selected.

SQL> SELECT 'Buffer Cache' NAME,
  2  ROUND ( (congets.VALUE + dbgets.VALUE - physreads.VALUE)
  3  * 100
  4  / (congets.VALUE + dbgets.VALUE),
  5  2
  6  ) VALUE
  7  FROM v$sysstat congets, v$sysstat dbgets, v$sysstat physreads
  8  WHERE congets.NAME = 'consistent gets'
  9  AND dbgets.NAME = 'db block gets'
 10  AND physreads.NAME = 'physical reads'
 11  UNION ALL
 12  SELECT 'Execute/NoParse',
 13  DECODE (SIGN (ROUND ( (ec.VALUE - pc.VALUE)
 14  * 100
 15  / DECODE (ec.VALUE, 0, 1, ec.VALUE),
 16  2
 17  )
 18  ),
 19  -1, 0,
 20  ROUND ( (ec.VALUE - pc.VALUE)
 21  * 100
 22  / DECODE (ec.VALUE, 0, 1, ec.VALUE),
 23  2
 24  )
 25  )
 26  FROM v$sysstat ec, v$sysstat pc
 27  WHERE ec.NAME = 'execute count'
 28  AND pc.NAME IN ('parse count', 'parse count (total)')
 29  UNION ALL
 30  SELECT 'Memory Sort',
 31  ROUND ( ms.VALUE
 32  / DECODE ((ds.VALUE + ms.VALUE), 0, 1, (ds.VALUE + ms.VALUE))
 33  * 100,
 34  2
 35  )
 36  FROM v$sysstat ds, v$sysstat ms
 37  WHERE ms.NAME = 'sorts (memory)' AND ds.NAME = 'sorts (disk)'
 38  UNION ALL
 39  SELECT 'SQL Area get hitrate', ROUND (gethitratio * 100, 2)
 40  FROM v$librarycache
 41  WHERE namespace = 'SQL AREA'
 42  UNION ALL
 43  SELECT 'Avg Latch Hit (No Miss)',
 44  ROUND ((SUM (gets) - SUM (misses)) * 100 / SUM (gets), 2)
 45  FROM v$latch
 46  UNION ALL
 47  SELECT 'Avg Latch Hit (No Sleep)',
 48  ROUND ((SUM (gets) - SUM (sleeps)) * 100 / SUM (gets), 2)
 49  FROM v$latch;

NAME                          VALUE
------------------------ ----------
Buffer Cache                  86.11
Execute/NoParse               65.93
Memory Sort                   99.99
SQL Area get hitrate          73.85
Avg Latch Hit (No Miss)         100
Avg Latch Hit (No Sleep)        100

6 rows selected.
SQL> show parameter sga_

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- -------------
sga_max_size                         big integer                      10G
sga_target                           big integer                      0
SQL> show parameterpga_
SP2-0735: unknown SHOW option beginning "parameterp..."
SQL> show parameter pga_

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- -------------
pga_aggregate_target                 big integer                      8G
SQL> show parameter memory

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- -------------
hi_shared_memory_address             integer                          0
memory_max_target                    big integer                      20G
memory_target                        big integer                      18G
shared_memory_address                integer                          0

Calculating the Buffer Cache Hit Ratio

SELECT NAME, VALUE
  FROM V$SYSSTAT
WHERE NAME IN ('db block gets from cache', 'consistent gets from cache', 'physical reads cache');


Calculate the cache hit ratio for the library cache with the following query:

    Select sum(pinhits) / sum(pins) "Hit Ratio",
        sum(reloads) / sum(pins) "Reload percent"
    From v$librarycache
    Where namespace in
    ('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER');

-- The hit ratio should be above 85 percent.
-- The reload percent should be very low, 2% or less.
-- If this is not the case, increase the initialisation parameter SHARED_POOL_SIZE.
-- OPEN_CURSORS may also need to increase.

You can monitor the library cache in a couple of ways. The first method is to execute the STATSPACK report. The second is to use the V$LIBRARYCACHE view.

Thursday, 5 June 2014

ORA-00602: internal programming exception

My instance got crash after that I have check my Alert log got below error.


 " ORA-07445: exception encountered: core dump [ktcnDumpQueryinvHT()+74] [SIGSEGV] [ADDR:0x2E77196] [PC:0x2E77196] [Stack Overflow] []
ORA-00602: internal programming exception" 

 Solution :-

Database: 11g Release 2
Error code: ORA-00602
Description: internal programming exception
Cause:Internal programming exception
Action: Report as bug
Same error message for other database releases

Database: 10g Release 1
Error code: ORA-00602
Description: internal programming exception
Cause:Internal programming exception
Action:Report as bug

Database: 10g Release 2
Error code: ORA-00602
Description: internal programming exception
Cause:Internal programming exception
Action:Report as bug
Database: 11g Release 1
Error code: ORA-00602
Description: internal programming exception
Cause:Internal programming exception
Action:Report as bug

Database: 12c Release 1
Error code: ORA-00602
Description: internal programming exception
Cause:Internal programming exception

Action:Report as bug

Tuesday, 3 June 2014

Oracle Enterprise Manager 12cR2 Cloud Management API



What is OEM 12cR2 Cloud Management Application Plug-in(API)..????

Cloud computing is a style of computing in which dynamically salable and deployed resources are provided as a service over the network. Users need not have knowledge of, expertise in, or control over the underlying infrastructure in the cloud that supports the services rendered to the users. As enterprises (companies, governments, and other organizations) integrate their existing IT infrastructures and IT resources with the sharable cloud paradigm, it is imperative for cloud enablers to provide a uniform API that these enterprises can use to tailor the cloud to their business processes and economic models.