ORA-16053: DB_UNIQUE_NAME is not in the Data Guard Configuration

Cause : DB_UNIQUE_NAME is not in the Data Guard Configuration. We can check current DB_UNIQUE_NAME in the configuration.

SQL> show parameter log_archive_config;

The list of valid DB_ UNIQUE_NAMEs can be seen with the V$DATAGUARD_CONFIG view. This problem can also occur when specifying a non-standby destination with an DB_ UNIQUE_NAME attribute that does not match the DB_UNIQUE_NAME initialization parameter for the current instance.

Action:

Amend the   DB_UNIQUE_NAME in LOG_ARCHIVE_CONFIG parameter . Below example shows  physical standby database as ORCL_STBY

1) ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL, ORCL_STBY)';

2)  Re – enable Standby Archive Location Parameter;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE= ORCL_STBY  REOPEN=5 NOAFFIRM  ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME= ORCL_STBY;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;

3) Check that status of the  standby destination.

SQL>  select dest_name,status,error from v$archive_dest_status where dest_id=2;

Cheers !!!

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