How to Drop ASM Datagroup while not mounted and disks corrupted

In a test environment we accidentally corrupt asm disks and reset their headers. When I query V$ASM_DISK view, I saw that disks were discovered as "CANDIDATE". It was like newly added disks available for ASM. The problem was, my datagroup which was using these disks, was exists but i could not drop it. That was because a diskgroup has to be mounted in order to be dropped, but its disks were corrupted so i could not mount it:

SQL> drop diskgroup DATA;
drop diskgroup DATA
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15001: diskgroup "DATA" does not exist or is not mounted


SQL> ALTER DISKGROUP DATA mount;
ALTER DISKGROUP DATA mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DATA" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA"

How can i drop/delete datagroup which i can not mount?

Solution is to create a new datagroup, add all disks to new datagroup. At the end, old datagroup disappears... Then you may drop newly added diskgroup.

create diskgroup DATA_DROP external redundancy disk '/dev/rhdisk#' force;
alter diskgroup DATA_DROP add disk '/dev/rhdisk#';
...
DATA disappears when i add all its disks to DATA_DROP...
...
drop diskgroup DATA_DROP including contents;

now, DATA diskgroup has gone and all its disks are in "FORMER" status.

ODI: connection to repository failed

If connection string for the repository database has been changed, you may face following error while connecting to repository altough you have changed url parameter of client tool:

java.sql.SQLException: Io Exception: The Network Adapter could not establish the connection
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:274)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:328)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:361)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:151)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:595)
at com.sunopsis.sql.SnpsConnection.u(SnpsConnection.java)
at com.sunopsis.sql.SnpsConnection.c(SnpsConnection.java)
at com.sunopsis.sql.h.run(h.java)

This is because, altough tool connects to repository successfully, it retrieves repository connection string from repository database and reconnects using it (or tries to open a new connection with it and fails). As a result, you can not connect and correct repository connection parameter.

I solved this problem by manually logging into repository database and updating repository connection  parameter:

select TXT from snp_mtxt_part where i_txt=( select I_TXT_JAVA_URL from SNP_CONNECT where CON_NAME='MY_PROD_WORK_REP_NAME')

update snp_mtxt_part set TXT='{correct repository connection string}' where i_txt=....;

Oracle 11GR2 SCAN, ORA-12502: TNS:listener received no CONNECT_DATA from client

While local_listener parameter of RAC database was something like this:

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node-vip)(PORT=1521))))

connecting to database using SCAN listener gave following error (after a short period of hang time)

ORA-12502: TNS:listener received no CONNECT_DATA from client

After setting local_listener as:

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node-vip.mycompany.com)(PORT=1521))))

problem resolved...

ORA-39126 and ORA-01017 while executing dbms_datapump import over database link

I hit this problem when executing DBMS_DATAPUMP IMPORT operation over database link. Here is the full error message:

Starting "SYS"."import_MYTASK":
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.POST_MT_INIT [SELECT SYS.KUPM$MCP.GET_ENDIANNESS@MYDBLINK FROM SYS.DUAL]
ORA-04052: error occurred when looking up remote object SYS.KUPM$MCP@MYDBLINK
ORA-00604: error occurred at recursive SQL level 3
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from MYDBLINK
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8164
----- PL/SQL Call Stack -----
object line object
handle number name
70000058f38e5b8 19028 package body SYS.KUPW$WORKER
70000058f38e5b8 8191 package body SYS.KUPW$WORKER
70000058f38e5b8 2072 package body SYS.KUPW$WORKER
70000058f38e5b8 1438 package body SYS.KUPW$WORKER
70000058e31ea78 2 anonymous block
Job "SYS"."import_MYTASK" stopped due to fatal error at 09:33:35

Database link was working fine when i query manually. Restarting database solved the problem however i dont know what was the cause...

Recover Database with allow corruption

Here is an example of block corruption faced during database recovery:

recover database;

RA-00600: internal error code, arguments: [3020], [48], [41103361], [41103361], [], [], [], [], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 48, block# 41103361)
ORA-10564: tablespace MYTS
ORA-01110: data file 48: '+MYDISC/mydb/datafile/myts.14714.699899641'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 323837
Errors in file /mytracepath/mydb_pr06_4728.trc:

You can skip recovery of this corrupted block by running following command:

recover database datafile '+MYDISC/mydb/datafile/myts.14714.699899641' allow 1 corruption;

If there are more then 1 corruption, you may repeat this step or run command with allow many corruption. However, dont forget to note corrupted file and block numbers. After opening database, we will use following query to find out to which segment corrupted block belogns to:

SELECT tablespace_name, segment_type, owner, segment_name

FROM dba_extents WHERE file_id = 48 and 41103361 between block_id AND block_id + blocks - 1;

Log Miner

These are steps to use log miner:

1-) Execute following PL/SQL block as sys user (note that first dbms_logmnr.add_logfile procedure is called with dbms_logmnr.new argument while others are called with dbms_logmnr.addfile argument):

begin
sys.dbms_logmnr.add_logfile (logfilename => '{oracle log file name}',options=>sys.dbms_logmnr.new);
sys.dbms_logmnr.add_logfile (logfilename => '{oracle log file name}',options=>sys.dbms_logmnr.addfile);
...
sys.DBMS_LOGMNR.START_LOGMNR(options => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
end;

2-) You can select log content using following query:

select * from V$LOGMNR_CONTENTS;

3-) Because content is read from file sequencely, it is very slow. If you are planning to query it multiple times, i strongly suggest you to insert log miner data to a local table and create an index on timestamp column. Requested storage for local table and its index is usually at around 4*(log size)*(number of log files).

create table LOGMNR TABLESPACE TOOLS as select * from V$LOGMNR_CONTENTS;
CREATE INDEX LOGMNR_IX1 ON LOGMNR ("TIMESTAMP") TABLESPACE TOOLS ;

4-) After you finish with log miner, execute following query:

begin
sys.dbms_logmnr.end_logmnr;
end;