ASM Disk Header Issue.. correct it using Kfed repair utility

when you see below error in the ASM ... this means the header of the ASM raw disk got corrupted or
at this time you can still mount....
we had  this issue even everything use to be up and working normal with no issues... but when tried to check the status of the disks it use to complain....so wanted to correct it before something goes wrong.... this issue might be happened due to a reboot or something happens when ASM lost its Header blocks...

do following checks... and confirm...

ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DAT" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DAT"

SQL> select group_number,name,type,state from v$asm_diskgroup;

GROUP_NUMBER NAME                           TYPE   STATE
------------ ------------------------------ ------ -----------
           1 oracle1212_12                  EXTERN 
           2 oracle12_12                    EXTERN MOUNTED
           3 oracle12IR_12                  EXTERN MOUNTED


set lines 250
set pages 40
col path format a30
col hstat format a25
col diname format a40
col dgname format a30
SQL> select di.name diname, dg.name dgname, di.header_status hstat, path from v$asm_disk di, v$asm_diskgroup dg where di.group_number = dg.group_number(+)
order by di.name;  2

DINAME                       DGNAME            HSTAT                PATH
--------------------      ----------------  ---------------    --------------------------
oracle1212_12_0000         oracle_12LOGS                      /oracle/raw2
oracle12_12_0001           oracle12a_12       MEMBER          /oracle/raw29
oracle1212_12_0002         oracleR_12         MEMBER          /oracle/raw30


$:/home> $ORACLE_HOME/bin/kfod status=TRUE asm_diskstring='/oracle/raw*' disk=all dscvgroup = true;
--------------------------------------------------------------------------------
 Disk          Size Header           Path                  Disk Group    User     Group
================================================================================   
   1:     104568 Mb Provisioned   /oracle/raw2            oracle_12LOGS  oracle   dba
   2:     104568 Mb MEMBER        /oracle/raw29           oracle12a_12   oracle   dba
   3:     104568 Mb MEMBER        /oracle/raw30           oracleR_12     oracle   dba

now i know which asm disk header got corrupted..... now i will run the KFED repair operation to correct the header blocks...

$> kfed repair /oracle/raw2 

once this is done.... try to mount the ASM disk

SQL>alter diskgroup oracle_12LOGS;

this will mount the disk... now startup the instance and check.

Thank you.....

How to Get DDL of the Procedure or any object in oracle

use below sql to get the DDL of a specific object:

set long 32000

set long 90000

set heading off;

set echo off;

set pages 1000;

SQL> spool object_name.sql

SQL> select dbms_metadata.get_ddl('object_type','object_name','Owner of the object') from dual;

SQL> spool off

Thanks...

ICMP host xx.y.xxx.xyz unreachable - admin prohibited, length 68 (linux)

run tcp dump on the server that is having an issue....  run below command and try to connect from other servers and see....


> tcpdump -r ensXXX -n host xx.y.xxx.xyz and  not port ssh and not port domain


if you see this below message then

error:- ICMP host xx.y.xxx.xyz unreachable - admin prohibited, length 68 (linux).

solution:-

. make sure firewall rules are setup between the servers you want to listen...
. make sure the firewalld process is shutoff or enable it with the ports that  you want to listen on.. and try again....

Import Data into table using Toad(sql*loader)

1) Remove the first line (which usually has the column names mentioned) and save the file in the .csv (delimited) format.
2) And make sure the table is created(if it is new) in the database where you are loading this csv file.
3) Make sure you have same number of columns in file and table.

4) Open Toad and connect to the database/schema that you want to load this data in to the table.
5) In Toad -> Database tab -> SQL *Loader Wizard
6) since, we are doing first time. check -> Build control file and Specify fields and then click Next.
7) click Add 
8) load the Input file (that is in csv format). -> choose file of type to -> All files.
9) click OK.
10)click next.
11) make sure you see the columns and data format that need to be here... if not go back and load the corrected file.
12) click next.
13) Under tables tab, click ADD -> select schema name -> table name-> and load method type(insert).
14) click next after confirming the fields.
15) click next.
16) specify the control file and log file name... and the location to be created so that it can be used if you have to do the same thing again.
17) click next.
18) check -> just build the control file and click FINISH. this will create control file for this task.
19) Now check -> Execute now and click FINISH. This will load the data in to the tab le.
20) validate the data in the table. you should be all set.


Hope this helps!!!!!!!!!!!!!!!!!!!!!!!

copy sql profiles from one env/db to another env/db

In this post we are trying to move the sql profiles that we created in one environment to another. as we now we cretae these sql profiles to make the sql performance better. And once this is tested we want to use the same profile to all env....

to do this we need to do the below steps...

1. Creating a staging table to store the SQL Profiles

exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>'SQL_PROFILES_TT',schema_name=>'schema_name');

2) Packing the SQL Profiles to the staging table

SELECT name FROM dba_sql_profiles;  ---once you have created the profile with OEM/db.

SYS_SQLPROF_013c88540c760000'

EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_TT',profile_name=>'SYS_SQLPROF_013c88540c760000');

3. Export table using “expdp” tool from the source database

4. Export table using “impdp” tool to the destination database

5)  SQL> conn / as sysdba

Connected.

SQL> grant administer sql management object to schema_name;

Grant succeeded.

Note: you will get below error if you dont have not given the   "administer sql management object " privilege...

SQL> conn schema_name/passd

Connected.

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE => TRUE,staging_table_name=> 'SQL_PROFILES_TT');

BEGIN DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE => TRUE,staging_table_name=> 'SQL_PROFILES_TT'); END;

*

ERROR at line 1:

ORA-38171: Insufficient privileges for SQL management object operation

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.DBMS_SMB", line 83

ORA-06512: at "SYS.DBMS_SQLTUNE", line 7657

ORA-06512: at "SYS.DBMS_SQLTUNE", line 6349

ORA-06512: at line 1

SQL> conn schema_name/passd

Connected.

SQL> show user;

USER is "schema_name"

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE => TRUE,staging_table_name=> 'SQL_PROFILES_TT');

PL/SQL procedure successfully completed.

SQL> conn / as sysdba

Connected.

SQL> select name from dba_sql_profiles;

NAME

------------------------------

SYS_SQLPROF_010c760000

Hope this helps....

using two different locations in expdp/impdp

sometimes we want to use a differnet location for logfile different to that of dumpfile location. to do that we need to create 2 directory locations one for dumpfile and one for log file. this is same for expdp/impdp

create or replace directory dpdir_dmp as 'tmp/exp';
create or replace directory dpdir_log as '/tmp/log';

expdp username/paswd directory=dpdir_dmp dumpfile=exp_backup_user.dmp logfile=dpdir_log:exp.log full=y


hope this helps...

expdp ORA-31633 while running the export script...

when ever we come across the "expdp ORA-31633" error... this means we need to remove the outstanding jobs that are already in there or using that table....

"ORA-00955: name is already used by an existing object"

we need to find the tables that are already been there and are not dropped once the expdp/impdp jobs are performed.(usually these are gone if the jobs exits normally without any errors/interruptions..)

select owner_name, job_name, operation, job_mode,state, attached_sessions from   dba_datapump_job  where  job_name not like 'BIN$%  order  by 1, 2;

If you see the tables... drop them.... and then try the expdp job again. This time it should work...

Hope this helps...