Drop Database Link in another schema .

We can drop Database link in another schema by a simple procedure without knowing password of the user

SQL> conn / as sysdba
SQL> create or replace procedure schema.procedurename as
begin
execute immediate  ‘drop database link  db_link_name’;
end procedurename;


SQL>exec Procedurename;

Oracle Multimedia (ORDIM) status is "LOADING" in DBA_REGISTRY

Oracle Version - 11g

Cause : Oracle Multimedia Registry Components are not properly configured .

sqlplus / as sysdab

SQL> COL COMP_NAME FOR A20
SQL> COL COMP_ID FOR A20
SQL> select comp_id,comp_name,status from dba_registry where comp_name='Oracle Multimedia';

SQL> select comp_id,comp_name,status from dba_registry where comp_name='Oracle Multimedia';

COMP_ID              COMP_NAME            STATUS
-------------------- -------------------- --------
ORDIM                Oracle Multimedia    LOADING



Action

1) Reload Registry component


SQL>conn / as sysdba

SQL> execute sys.dbms_registry.loaded('ORDIM');

2) Validate Registry Status .


SQL> execute sys.dbms_registry.valid('ORDIM');


Now we can see the status is "VALID"


SQL> select comp_id,comp_name,status from dba_registry where comp_name='Oracle Multimedia';

COMP_ID              COMP_NAME            STATUS
-------------------- -------------------- --------
ORDIM                Oracle Multimedia    VALID

Drop user failed with ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist

Cause

Table system.aq$_internet_agent_privs is missing in "SYSTEM" Schema.



Action:

1. Check if system.aq$_internet_agent_privs exist.

       SQL> conn / as sysdba

       SQL> desc system.aq$_internet_agent_privs

2. Make sure that the default tablespace of SYSTEM user is properly set to SYSTEM tablespace.

        SQL> select default_tablespace from dba_users where   username=’SYSTEM’;

      DEFAULT_TABLESPACE
      ——————————
      SYSTEM

Run the below Script as sysdba user.

      SQL> conn / as sysdba
      SQL> @$ORACLE_HOME/rdbms/admin/catqueue.sql 
      SQL> exit


3. Then execute the DROP USER command again.

4.   SQL> Drop user Username;

      User Dropped

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;

  /