ORA-27100: shared memory realm already exists

You may get this error after restarting the database with improper memory management of SGA in SPFILE or from a Improper shutdown .

Scenario 
*******
startup nomount
 
ORA-27100: shared memory realm already exists

SQL> shutdown immediate
 
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

Following Steps will help to overcome this issue .
          

           1)Rename existing SPFILE in the Database folder of Oracle Home.
           2)Restart the Service .
           3) Startup database using pfile with reverting changes .
           4)Create SPFILE from PFILE .
           5)Shutdown and Startup the Database using newly created SPFILE.

 
            

ORA-39776 & ORA-00600 in IMPDP on 11.1.0.6.0

 We may have to get ORA-39776 & ORA-00600 errors while importing dump file using IMPDP on 11.1.0.6.0

ORA-02354: error in exporting/importing data
ORA-39776: fatal Direct Path API error loading table
 
ORA-00600: internal error code, arguments: [klaprs_11], [], [], [], [], [], [],

Cause : Oracle bug on version 11.1.0.6.0

Action: Export Dump file without COMPRESSION option or Apply Patchset 11.1.0.7 .

Schema Refresh Using Data pump

Term  Schema Refresh is a type of data synchronization process from Production Database to Testing  Database . Below Example shows step by step process of a Schema Refresh using Data Pump Job.

1)      Create a DBA privileged User for Schema Refresh on Destination Database;

SQL>Create user DBA_REFRESH identified by dba_refresh;

SQL>GRANT DBA TO DBA_REFRESH;

2)      Create a Public Database Link

Connect as DBA_REFRESH user

C:\>sqlplus dba_refresh/dba_refresh@Target_Service_Name

Connected.

      CREATE public DATABASE LINK REFRESH

      connect to ‘Source Schema’

      identified by Password

      using 'Connection String';

3)  Create Procedure For Schema Refresh On DBA_REFRESH user Of the Destination Database.

         CREATE OR REPLACE PROCEDURE DBA_REFRESH.refresh_schema

        ( source_schema in varchar2,

         destination_schema in varchar2,

         new_password in varchar2 default 'dba_refresh',

         network_link in varchar2 default 'REFRESH')

         as

         JobHandle   number;

         js  varchar2(9); -- COMPLETED or STOPPED

         q   varchar2(1) := chr(39);

 BEGIN  /* open a new schema level import job using a default DB     link  */

        JobHandle := dbms_datapump.open ('IMPORT',

                                                                 'SCHEMA',

                                                                     network_link);

                         

        /* restrict to the schema we want to copy */

        dbms_datapump.metadata_filter ( JobHandle,

                                                              'SCHEMA_LIST',

                                                                q||source_schema||q);

        /* remap the importing schema name to the schema we want to create */

        dbms_datapump.metadata_remap ( JobHandle,

                                                                 'REMAP_SCHEMA',

                                                                  source_schema,

                                                                  destination_schema);

 

        /* Set datapump parameters*/                           

        dbms_datapump.set_parameter ( JobHandle,

                                                             'TABLE_EXISTS_ACTION',

                                                              'REPLACE' );

                               

         /* start the job */

        dbms_datapump.start_job( JobHandle);  

        /* wait for the job to finish */

        dbms_datapump.wait_for_job( JobHandle, js);  

        end;

        /

4)  Execute created Procedure with appropriate values.

begin        

 DBA_REFRESH.refresh_schema (‘SOURCE_SCHEMA’,'DESTINATION_SCHEMA');

 end;

  /

     

ORA-12532: TNS:invalid argument

sqlplus system@orcl

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Aug 19 13:18:56 2007

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

Enter password:
ERROR:
ORA-12532: TNS:invalid argument

In this case we can ping to remote host but not able to connect database which we have created on remote host.

Cause : Listener port is closed.May be a firewall policy make the issue.

Action : We need to open Listener port for communication .By default Listener port is 1521.

Kill oracle session in OS LEVEL

Sometimes, “ALTER SYSTEM KILL SESSION” will not terminate a session, it will just mark it as “killed”. In this case you can kill the process or thread of that session at the operation system level.

In Windows

C:\ ORAKILL ORACLE_INSTANCE THREAD;

THREAD means SPID of the process .

We can simply find out THREAD(SPID) By below query.
select spid, osuser, s.program from
v$process p, v$session s where p.addr=s.paddr
Identify SPID of the particular user which you want to Kill

SQL> select spid, osuser, s.program from
v$process p, v$session s where p.addr=s.paddr;

Example

SQL> select spid, osuser, s.program from
v$process p, v$session s where p.addr=s.paddr;

SPID OSUSER PROGRAM
------------------------ ------------------------------ --------------------
8876 NICE JDBC Thin Client

In this above Example THREAD(SPID) is 8876

SQL> Select instance_name from v$instance;
INSTANCE_NAME
-------------
ORCL
C:\ ORAKILL ORCL 8876;

To kill the session on the Unix operatin system, you can use “kill” command:

KILL THREAD

In the above Example

Example : KILL 8876

If after a few minutes the process hasn’t stopped, you can force the session to terminate by using “-9″ parameter:
kill -9 THREAD
Example : KILL -9 8876
Note: Make sure that you are not killing background processes of Oracle such as DBWR, LGWR, SMON, PMON. If you kill one of them, Oracle will crash or become unstable

Checking Invalid Objects in a Schema

You can identify invalid Objects in your schema by using below query.

select * from user_objects where status='INVALID'

You can compile invalid objects in a schema one by one.

select
'ALTER ' || OBJECT_TYPE || ' ' ||
OBJECT_NAME || ' COMPILE;'
from
user_objects
where
status = 'INVALID';


Otherwise you can Compile all the objects in a schema


EXEC DBMS_UTILITY.compile_schema('Username');

ora-0155 snapshot too old error

Cause:Due to Oracle Flashback operations, Database is not able to dynamically tune the undo retention period effectively.

Action:

1)You can include CONSISTENT=Y parameter as in the case of Export(EXP).
2)Try to increase the value of UNDO_RETENTION parameter to match the length of the longest Oracle Flashback operation.
3)Schedule long running queries after your required operation.
4)Increase size of UNDO tablespace.

Cheers!!!

ORA-20185: operation disallowed on workspace 'WORKSPACE_NAME' involved in a conflict resolution session.

BEGIN DBMS_WM.removeworkspace('WORKSPACE_NAME'); END;
Error at line 3
ORA-20185: operation disallowed on workspace 'WORKSPACE_NAME' involved in a conflict resolution session
ORA-06512: at "WMSYS.LT", line 4955
ORA-06512: at line 1

Cause:Conflict on workspace.

Action:

SQL> exec dbms_wm.SetConflictWorkspace('WORKSPACE_NAME');

SQL> exec dbms_wm.commitresolve('WORKSPACE_NAME');

SQL> exec DBMS_WM.removeworkspace('WORKSPACE_NAME');

Workspace Removed


Cheers!!!

Monitoring Memory Resizing Operations with Memory_target Parameter.

To enable memory target

SQL> alter system set memory_max_target=5G scope=spfile;

SQL> alter system set memory_target=4G scope=spfile;

Turn off the SGA_TARGET and the PGA_AGGREGATE_TARGET
parameters by issuing the following ALTER SYSTEM commands:

SQL> alter system set sga_target = 0;

SQL> alter system set pga_aggregate_target = 0;

SQL>shutdown immediate;

SQL>startup


Under an automatic memory management mode, you can view the current allocations of
memory inany instance by querying the V$MEMORY_DYNAMIC_COMPONENTS
view. Querying this view provides vitalinformation to help you tune the MEMORY_TARGET parameter. Here’s how you execute a query against thisview:

SQL> select * from v$memory_target_advice order by memory_size;

Your current memory allocation is shown by the row with the MEMORY_SIZE_FACTOR value of 1 . The MEMORY_SIZE_FACTOR column shows alternate sizes of theMEMORY_TARGET
parameteras a multiple of the current MEMORY_TARGET parameter value. The
ESTD_DB_TIME column shows the timeOracle estimates it will need to complete the current workload with a specific MEMORY_TARGET value. Thus, the query results show you how much faster the database can process its work by varying the value of the
MEMORY_TARGET parameter.

ORA-04030: out of process memory while trying to allocate "N" Bytes

Cause:Oracle cannot perform requested operation with this free memory.

Action:

1)Tune pga_aggregate_target value depends on your server memory.
2)Server is windows,Increase your page file size with the help of system admin.
If you are working with UNIX platform check kernal parameters of shared memory .
3)Move to multi-threaded server.



Cheers!!!

Reclaiming Unused Index Space

We can see Reclaimable space in Schema by using below query.

SELECT'Task Name : ' || f.task_name || CHR(10) ||'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) ||'Segment Name : ' || o.attr2 || CHR(10) ||'Segment Type : ' || o.type || CHR(10) ||'Partition Name : ' || o.attr3 || CHR(10) ||'Message : ' || f.message || CHR(10) ||'More Info : ' || f.more_info || CHR(10) ||
'------------------------------------------------------' Advice FROM dba_advisor_findings f,dba_advisor_objects o,dba_advisor_executions e WHERE o.task_id = f.task_id AND o.object_id = f.object_id AND f.task_id = e.task_id AND e. execution_start > sysdate - 1 AND e.advisor_name = 'Segment Advisor' ORDER BY f.task_name;

Solution

There are a couple of effective methods for freeing up unused space associated with an index:




•Rebuilding the index
•Shrinking the index


Before you perform either of these operations, first check
USER_SEGMENTS to verify that the amount of space used corresponds with the Segment Advisor’s advice. In this example, the segment name is F_REGS_IDX1

SQL> select bytes from user_segments where segment_name = 'F_REGS_IDX1';
BYTES----------
166723584

This example uses the
ALTER INDEX...REBUILD

statement to re-organize and compact the space usedby an index:

SQL> alter index f_regs_idx1 rebuild;
Alternatively, use the
ALTER INDEX...SHRINK SPACE statement to free up unused space in an index—for example:

SQL> alter index f_regs_idx1 shrink space;

Now query

USER_SEGMENTS

again to verify that the space has been de-allocated. Here is the output forthis example:

BYTES----------
524288

The space consumed by the index has considerably decreased.

I prefer export and import is the another useful method to reclaim free space.

IMP-00038: Could not convert to environment character set's handle

Check character set of Database which export dump file has been already taken.

You check the character set with below query.

select * from v$nls_parameters where parameter in ('NLS_CHARACTERSET','NLS_LANGUAGE');

Also check the character set of DB which needs to be imported.

Check whether the characterset conversion is possible or not.

Sometimes a corrupted dumpfile cause this error.Suppose if you are trying to import using IMP from a EXPDP(Data pump Export)dumpfile Cause this error .

Cheers.

EXEC DBMS_WM.REMOVEWORKSPACE('WORKSPACE_NAME');

ora-04030 out of process memory while trying to allocate 'N' bytes.


Delete all the save points from workspace.We can see the save points from WMSYS.WM$WORKSPACE_SAVEPOINTS_TABLE.Use below query to remove save points from worskspace .


DBMS_WM.DeleteSavepoint(
workspace IN VARCHAR2,
savepoint_name IN VARCHAR2,
compress_view_wo_overwrite IN BOOLEAN DEFAULT FALSE,
auto_commit IN BOOLEAN DEFAULT TRUE,
commit_in_batches IN BOOLEAN DEFAULT FALSE,
batch_size IN VARCHAR2 DEFAULT 'PRIMARY_KEY_RANGE');


After the successful removal of save points,You can remove workspace simply by

EXEC DBMS_WM.REMOVEWORKSPACE('WORKSPACE_NAME');


Cheers!!!

Forcefull Disable Version on Versioned Table.

You can forcefully Disable Version tables that have already modified in non Live Workspace in Oracle.But all the changes made by the non Live workspace will be neglected.

SQL> EXEC DBMS_WM.DISABLEVERSIONING('TABLE_NAME',TRUE);

ORA-16179: incremental changes

ORA-16179: incremental changes to "log_archive_dest_2" not allowed with SPFILE


You can simply avoid this error by cross checking your command once again.

Use your command like this

SQL>ALTER SYSTEM SET log_archive_dest_2 = 'LOCATION=/OPT/ARC' SCOPE=SPFILE;



Cheers!!!

ORA-04031: unable to allocate N bytes of shared memory

This error can we fixed by increasing shared pool memory.

Or you can flush your shared pool memory ,If the size is already High

SQL>alter system flush shared_pool;

You can increase shared pool size(shared_pool_size) using parameter file(INIT.ORA)

Or you can dynamically increase your size by

SQL>ALTER SYSTEM SET SHARED_POOL_SIZE=1200M SCOPE=BOTH;

Last SQL statement on each SCHEMA

We can calculate late executed statement using below query.

select j.sql_text,y.username
from v$session y, v$sqlarea j
where j.hash_value = y.prev_hash_value



Cheers.

ORA-12518: TNS:listener could not hand off client connection

ORA-12518: TNS:listener could not hand off client connection

In a shared server environment .


Shutdown the dispatcher and add new dispatchers.
SQL> show parameter dispatchers
SQL> select name from v$dispatcher;
SQL> alter system shutdown immediate 'D001';
Add new dispatcher
SQL> alter system set DISPATCHERS = '(protocol=tcp)(dispatchers=4)';


In dedicated Server.


PGA is fully allocated for all processes.Check PGA memory allocation.
SQL> SELECT * FROM V$PROCESS ORDER BY PGA_ALLOC_MEM DESC

Kill unnecessary Sessions for freed PGA memory



Cheers...!!!