ASM block corruption with ORA-600

The last week i got some problems with the database because the server was shutdown by an outage.

I tried to startup manually when server was operating again.

SQL> startup
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             528485968 bytes
Database Buffers          314572800 bytes
Redo Buffers                5132288 bytes
ORA-00205: error in identifying control file, check alert log for more info

so, let's check alertFran.log:

ALTER DATABASE   MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '+FRA/fran/controlfile/control01.ctl'
ORA-27048: skgfifi: file header information is invalid
ORA-205 signalled during: ALTER DATABASE   MOUNT...

Checker run found 1 new persistent data failures
ORA-205 signalled during: ALTER DATABASE   MOUNT...

controlfile01.ctl is invalid so let's repair them. Two ways:

1) you have multiplixed controlfile:

$export ORACLE_SID=+ASM
$export ORACLE_HOME=/u01/app/grid
$sqlplus / as sysasm
SQL> alter database nomount;
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             528485968 bytes
Database Buffers          314572800 bytes
Redo Buffers                5132288 bytes
SQL> exit
$asmcmd
ASMCMD> rm -rf '<path_invalid_controlfile>;
ASMCMD> cp '<path_valid_controlfile>' '<path_invalid_controlfile>;

2) You haven't multiplexed controlfile, create manually one:
SQL> CREATE CONTROLFILE
     SET DATABASE Fran
     LOGFILE GROUP 1 ('+FRA/fran/onlinelog/group_1.A',
                      '+FRAN_DATA/fran/onlinelog/group_1.B'),
             GROUP 2 ('+FRA/fran/onlinelog/group_2.A',
                      '+FRAN_DATA/fran/onlinelog/group_2.B'),
             GROUP 3 ('+FRA/fran/onlinelog/group_3.A',
                      '+FRAN_DATA/fran/onlinelog/group_3.B'),
             GROUP 4 ('+FRA/fran/onlinelog/group_4.A'),
                      '+FRAN_DATA/fran/onlinelog/group_4.B')
     RESETLOGS                
     DATAFILE '+FRAN_DATA/fran/datafiles/system01.dbf' SIZE 150M,
              '+FRAN_DATA/fran/datafiles/rbs01.dbs' SIZE 512M,
              '+FRAN_DATA/fran/datafiles/temp01.dbs' SIZE 2G,
              '+FRAN_DATA/fran/datafiles/sysaux01.dbf' SIZE 30M,
              '+FRAN_DATA/fran/datafiles/user01.dbf' SIZE 100M,
              '+FRAN_DATA/fran/datafiles/test01.dbf' SIZE 512,
              '+FRAN_DATA/fran/datafiles/fran01.dbf' SIZE 1G;

**if you modified or create controlfile remember that you must modify the new path at pfile. if you create pfile from spfile, open pfile and you see something like this:
**control_files = ('+FRAN_DATA/fran/controlfile/control01.ctl','+FRA/fran/controlfile/control02.ctl');
**modify manually with vi or textpad (depends OS) changing its paths
**control_files = ('new_path/control01.ctl','new_path/control02.ctl');
**SQL> STARTUP PFILE='path_pfile';
**SQL> create spfile from pfile;

we can now try to open database.

$sqlplus / as sysdba
SQL> ALTER DATABASE OPEN;


**It should open without problem, but like my issue is different i got a new error

ORA-00338: log 3 of thread 2 is more recent than control file
ORA-00312: online log 3 thread 2: '+FRAN_DATA/Fran/onlinelog/group_3.A'
ORA-00338: log 3 of thread 2 is more recent than control file
ORA-00312: online log 3 thread 2: '+FRA/Fran/onlinelog/group_3.B'

the solution was restore/recover database from last backup until 10 minutes before the outage:
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> RUN{
  ALLOCATE CHANNEL c1 FORMAT '<path_of_backup>';
  set until time "to_date('2013-11-01:11:27:00', 'yyyy-mm-dd:hh24:mi:ss')";
  restore database;
  recover database;
    release channel c1;
 }

after restore recover finished succesfully i got a new error when we opened database (ALTER DATABASE OPEN RESETLOGS)

ORA-600 : internal error code, arguments: [krsi_al_hdr_update.15], [4294967295], [], [], [], [], [], [], [], [], [], []

this error are serious, we searched by this arguments at ORA-600 aplication of MOS (My Oracle Support) but no luck....

searching at alert.log from asm instance we found the error:

ORA-600 : internal error code, arguments: [krsi_al_hdr_update.15], [4294967295], [], [], [], [], [], [], [], [], [], []
ORA-15032: not all alterations performed
ORA-15130: diskgroup "FRA" is being dismounted
ORA-15066: offlining disk "FRA_0004" in group
"FRA" may result in a data loss
ORA-15196: invalid ASM block header [kfc.c:26076] [hard_kfbh] [11223]
[2147483648] [34 != 130]
ORA-15196: invalid ASM block header [kfc.c:26076] [hard_kfbh] [11223]
[2147483648] [34 != 130]

It is an ASM corruption block!! Well, breathe, everything has a solution in that life...


SQL> SELECT count(*) from v$database_block_corruption;

 COUNT(*)
---------
     217    <-- confirmed, 217 blocks corrupted.

solution--> move everything of this asm diskgroup to another asm diskgroup or copy them at OS level. If you have enterprise edition licence you can easily repair with "RMAN> blockrecover corruption list;" (you must need a valid backup, of course), like we have standard edition we haven't any other option....

At this ASM diskgroup we have logfiles, controlfile, backups and archivelogs. We aren't worry about backups and archivelogs because they are copying in other server every 1 hour. So we just moved logfiles and controlfile:

$sqlplus / as sysasm
*REMEMBER: I had database on mount state. So we can do it without any problem.

SQL> ALTER SYSTEM SET control_files = ('+FRAN_DATA/fran/controlfile/control01.ctl','/backup/controlfile/control02.ctl') scope=spfile;

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+FRA/fran/onlinelog/group_1.A
+FRA/fran/onlinelog/group_2.A
+FRAN_DATA/fran/onlinelog/group_2.B
+FRA/fran/onlinelog/group_3.A
+FRAN_DATA/fran/onlinelog/group_3.B
+FRA/fran/onlinelog/group_4.A
+FRAN_DATA/fran/onlinelog/group_4.B
+FRAN_DATA/fran/onlinelog/group_1.B

SQL> ALTER DATABASE RENAME FILE '+FRA/fran/onlinelog/group_1.A', '+FRAN_DATA/fran/onlinelog/group_1.B' TO '+FRAN_DATA/ASM/ONLINELOG/group_1NEW_A', '+FRAN_DATA/ASM/ONLINELOG/group_1NEW_B';

Database altered.

SQL> ALTER DATABASE RENAME FILE '+FRA/fran/onlinelog/group_2.A', '+FRAN_DATA/fran/onlinelog/group_2.B' TO '+FRAN_DATA/ASM/ONLINELOG/group_2NEW_A', '+FRAN_DATA/ASM/ONLINELOG/group_2NEW_B';

Database altered.

SQL> ALTER DATABASE RENAME FILE '+FRA/fran/onlinelog/group_3.A', '+FRAN_DATA/fran/onlinelog/group_3.B' TO '+FRAN_DATA/ASM/ONLINELOG/group_3NEW_A', '+FRAN_DATA/ASM/ONLINELOG/group_3NEW_B';

Database altered.

SQL> ALTER DATABASE RENAME FILE '+FRA/fran/onlinelog/group_4.A', '+FRAN_DATA/fran/onlinelog/group_4.B' TO '+FRAN_DATA/ASM/ONLINELOG/group_4NEW_A', '+FRAN_DATA/ASM/ONLINELOG/group_4NEW_B';

Database altered.


SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+FRAN_DATA/asm/onlinelog/group_1new_A
+FRAN_DATA/asm/onlinelog/group_2new_A
+FRAN_DATA/asm/onlinelog/group_2new_B
+FRAN_DATA/asm/onlinelog/group_3new_A
+FRAN_DATA/asm/onlinelog/group_3new_B
+FRAN_DATA/asm/onlinelog/group_4new_A
+FRAN_DATA/asm/onlinelog/group_4new_B
+FRAN_DATA/asm/onlinelog/group_1new_B

8 rows selected.

SQL> alter database open resetlogs
2 ;

Database altered.

WOW, opened!! good work!!

Now, i recommend drop and recreate diskgroup +FRA, move again logfiles and controlfile to +FRA and forgot all about corruption blocks.... for the moment....

Complete Deinstall/Remove Oracle 11gR2

Imagine that you have an Oracle database 11gR2 installed. That database is working but you don't need it anymore and need that space.

That manual is made on windows 7 64 bits but the procedure is simililar on Unix so you can use this guide.

Oracle provides an useful tool called "deinstall tool" for standalone, RAC, ASM Oracle installations. You can find Deinstall Tool under ORACLE_HOME\deinstall\. If you don't have it you can go to this link and download it:
ORACLE DOWNLOADS --> Under Oracle Database 11gR2, click "See all" and look for "Oracle De-Install Utility".

1) Deinstall tool will stop Oracle software, and removes Oracle software and configuration files on the operationg system for a specifil Oracle Home. If you are running an Oracle grid infraestructure for a standalon server, then the deinstaller prompts you to run roothas.pl script to deconfigure Oracle Restart.

2)Go to your Oracle_home --> deinstall
or Oracle_home --> grid --> deinstall if you are under grid infraestructure and execute deinstall.bat.

deinstall tool use the following sintax (if you want execute manually):

deinstall.bat -home complete path of Oracle home [-silent] [-checkonly] [-local]
[-cleanupOBase] [-paramfile complete path of input parameter property file] [-params name1=value name2=value . . .]
[-o complete path of directory for saving files] [-help]

Just answer the questions and wait until finish.

Estimate Time To Complete RMAN Execution (backup, recover, restore)

To estimate how many time any rman operation will take, you can check it with this script:

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
       ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
  AND OPNAME NOT LIKE '%aggregate%'
  AND TOTALWORK != 0
  AND SOFAR <> TOTALWORK;

For example if we are doing a full backup of database we can predict how many time we will take (approximately) :

Same will happen with restore/recover.

EMKEY problems in Oracle RAC 11gR2

Oracle database 11gR2 (RAC 2 nodes)
O.S.: Redhat 5.3


1) After do a duplicate database in another host  i was unable to start Enterprise Manager Console. I got an error about EMKEY file corruption.

[oracle@fran ~]$ emctl status emkey
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
Please enter repository password:

The Em Key is not configured properly or is corrupted in the file system and does not exist in the Management Repository. To correct the problem:
1) Copy the emkey.ora file from another OMS or backup machine to the OH/sysman/config directory.
2) Configure the emkey.ora file by running "emctl config emkey -emkeyfile <emkey.ora file location>".

2) I went to ORACLE_HOME/<host>_<sid>/sysman/config  directory and look for emkey.ora, It exists but when i tried to run "emkey config emkey -emkeyfile <emkey.ora file location>" it shows:

The Em Key could not be configured. /u01/app/oracle/product/11.2.0.3/dbhome_1/fran_orcl/sysman/config/emkey.ora is corrupted or contains an invalid Em Key.

So i replaced this emkey.ora from an older emkey.ora but it shows again the same error:

 The Em Key could not be configured. /u01/app/oracle/product/11.2.0.3/dbhome_1/fran_orcl/sysman/config/emkey.ora is corrupted or contains an invalid Em Key.

3) My next try was configure manually but again it shows an error:

[oracle@fran config]$ emctl config emkey -repos -sysman_pwd oracle -emkeyfile /u01/app/oracle/product/11.2.0.3/dbhome_1/fran_orcl/sysman/config/emkey.ora
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.

The Em Key could not be configured. The Em Key was not found in Management Repository.

4) If Em Key wasn't found in Management Repository you only have one way to resolve, recreate Repository...

You can check how to recreate repository in this blog: How To Recreate Oracle Repository

How to recreate Oracle 11gR2 Repository

Oracle Database: 11.2.0.3
OS: Redhat 5.3

1) Recreate Oracle repository is an easy job, but sometimes is problematic. Try to resolve your OEM problem without recreate it unless you waste all the others options with no luck.

2) First of all, check that database is up and running, listener must be up and running too and register with database and ORACLE_SID is set.

3) You should drop SYSMAN user, and its roles and views to avoid "CONFIG: ORA-20001: SYSMAN already exists" error. To do it connect as sysdba user and run:

drop user sysman cascade;
drop public synonym setemviewusercontext;
drop role mgmt_user;
drop public synonym mgmt_target_blackouts;
drop user mgmt_view;

3) now we can drop the repository,exit of sqlplus and run:

$ emca -deconfig dbcontrol db -repos drop

*If you are in RAC, you should drop repository with this command in every node or
$ emca -deconfig dbcontrol db -repos drop -cluster (from primary node)
** You can check the logs under $ORACLE_HOME/cfgtoollogs/emca/

Be carefull, you will be question if you want to continue but with a Warning.
———————————————————————————————————————————
WARNING : While repository is dropped the database will be put in quiesce mode.
———————————————————————————————————————————
Do you wish to continue? [yes(Y)/no(N)]: Y

That Warning means that only sys and syman users can connect and work (only for maintaince purpose)

SQL> select status,active_state from v$instance;

STATUS       ACTIVE_ST
————       ——————
OPEN           QUIESCED

To avoid this situation, you can unquiesce database with:

SQL> alter system unquiesce; 

If you need to run again in quiescing mode, run again:

SQL> Alter system quiesce restricted;

In Example:
SQL> Alter system quiesce restricted;
Sistema modificado.

SQL> select status,active_state from v$instance;
STATUS       ACTIVE_ST
------------    ---------
OPEN            QUIESCED

SQL> alter system unquiesce;
Sistema modificado.

SQL> select status,active_state from v$instance;
STATUS       ACTIVE_ST
------------    ---------
OPEN            NORMAL

4) After drop repository finish, we can create the new one:

$ emca -config dbcontrol db -repos create

*In RAC will be:

$ emca -config dbcontrol db -repos create -cluster

5) Now you can start OEM executing "emctl start dbconsole"

duplicate database 11gR2 (step by step) under linux

Sometimes you will need to clone a database, for example when you have to test something with the data of production database but you can't use production database, so the easy way will be clone production database and create a test database.

In this example, I have two databases. My original database called "FRAN" and my target database called "duplicate". I have created duplicate with dbca, it is empty. I want to clone Fran into duplicated to do my tests without modify any important data of FRAN database.

1)First of all I need to do a full backup of FRAN:

2) You should know path of datafiles, logfiles, tempfiles, dbid, and a copy of pfile (for security):

- To know where are datafiles, logfiles and tempfiles execute this query:

SQLPLUS> SELECT FILE# AS "File/Grp#", NAME

FROM   V$DATAFILE

UNION

SELECT GROUP#,MEMBER

FROM   V$LOGFILE

UNION

SELECT FILE#, NAME

FROM V$TEMPFILE;

-To make a copy of FRAN's pfile:

SQLPLUS> create pfile='<path>/PFILE_ORIGINAL' from spfile;

-To know FRAN's DBID
when we run rman, appear--> Connected to target database: <database_name>(DBID XXXXXXX)

3) copy backup database in the same directory of target server, I mean if we have in FRAN's Server the bakcup in '/backup/rman/' we should have the same directory into DUPLICATE server and copy the backup there.
If it is in the same server, don't do anything.
If not you should move between servers with  scp <backup_direcotry> <user>@<SERVER_IP/NAME>:<target_diretctory>  . In example:

$ scp /backup/rman/% oracle@DUPLICATEDB:/backup/rman/

4) To duplicate, we have to put DUPLICATEDB into NOMOUNT state:

5) Now create duplicate script, we have two choices, with exactly the same name and path of datafiles/logfiles/tempfiles, or with different name or path or both:

IMPORTANT: run the next step from SOURCE server, in that case FRAN's server.
IMPORTANT_2: databases name are stored with 8 digits, so DUPLICATE  will be DUPLICAT. I will use this alias, because if you use real name, you will get error. Also if you execute in sqlplus "select name from v$database" will show DUPLICAT, DUPLICATE doesn't show but it is the database real name. Take care with the name used, the good one is 8 digits.

 5.1) exactly the same datafiles/logfiles/tempfiles name and path


$duplicate_db> rman target system/<passwd>@FRAN auxiliary /
RMAN>

run{
duplicate target database to 'DUPLICAT' nofilenamecheck;
}


5.2) different datafiles/logfiles/tempfiles name ond path or both:

SET NEWNAME FOR DATAFILE '<ORIGINAL_PATH/DATAFILE_NAME>' TO '<NEW_PATH/NEW_DATAFILE_NAME>';

$duplicate_db> rman target system/<passwd>@FRAN auxiliary /
RMAN>

run{

  SET NEWNAME FOR DATAFILE '<ORIGINAL_PATH/ORIGINAL_DATAFILE_NAME_1>' TO '<NEW_PATH/NEW_DATAFILE_NAME_1>';

   DUPLICATE TARGET DATABASE TO ‘DUPLICAT’;

SET NEWNAME FOR DATAFILE <ORIGINAL_PATH/ORIGINAL_DATAFILE_NAME_1> TO '<NEW_PATH/NEW_DATAFILE_NAME_1>';

SET NEWNAME FOR DATAFILE <ORIGINAL_PATH/ORIGINAL_DATAFILE_NAME_1> TO '<NEW_PATH/NEW_DATAFILE_NAME_1>';

*

*

*

SET NEWNAME FOR TEMPFILE<ORIGINAL_PATH/ORIGINAL_TEMPFILE_NAME> TO '<NEW_PATH/NEW_TEMPFILE_NAME>';

SET NEWNAME FOR TEMPFILE<ORIGINAL_PATH/ORIGINAL_TEMPFILE_NAME> TO *

*

*

*

SET NEWNAME FOR LOGFILE<ORIGINAL_PATH/LOGFILE_NAME> TO '<NEW_PATH/NEW_LOGFILE_NAME>';

*

*
*

*

DUPLICATE TARGET DATABASE TO "DUPLICAT";

   }

*If you omit something, oracle will deduce that you want with the same path+name, for example it could be just one logfile or all tempfiles.

** you can add in that script: PFILE, CONTROLFILE too

6) I will choose different name and path for datafiles and tempfiles, so from Fran's server  I execute:

remember --> auxiliary will be DUPLICAT (new database) with nomount state and target is FRAN (original database) with open state.

*when duplicate finish, DUPLICAT database will be called like original database (Fran) with same DBID of Original Fran.

That's all!!

How to resolve ORA-00392 ORA-00312

I did a restore/recover from my original RAC to another RAC in another server. When i created the ASM diskgroups, I change one of their original names, exactly +FRAN_DATA for +DATA.
The restore/recover was ok but when i tried to open database with "resetlogs" option i got ORA-00392 and ORA-00312.
The solution is easy, you should clear the busy onlinelog and later change one by one all the redologs name until you can open database with resetlogs option. Something ike this:


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR en línea 1:
ORA-00392: el log 27 del thread 2 se está limpiando, operación no permitida
ORA-00312: log online 27 thread 2: '+DATA/fran/onlinelog/group_27'



SQL> alter database clear logfile group 27;

Base de datos modificada.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR en línea 1:
ORA-00349: fallo al obtener el tamaño del bloque para
'+FRAN_DATA/fran/onlinelog/group_20'
ORA-15001: diskgroup "FRAN_DATA" does not exist or is not mounted
ORA-15001: diskgroup "FRAN_DATA" does not exist or is not mounted


SQL> alter database rename file '+FRAN_DATA/fran/onlinelog/group_20' to '+DATA/fran/onlinelog/group_20';

Base de datos modificada.

SQL>  alter database open resetlogs;
 alter database open resetlogs
*
ERROR en línea 1:
ORA-00349: fallo al obtener el tamaño del bloque para
'+FRAN_DATA/fran/onlinelog/group_21'
ORA-15001: diskgroup "FRAN_DATA" does not exist or is not mounted
ORA-15001: diskgroup "FRAN_DATA" does not exist or is not mounted


SQL> alter database rename file '+FRAN_DATA/fran/onlinelog/group_21' to '+DATA/fran/onlinelog/group_21';

Base de datos modificada.

SQL> alter database open resetlogs
  2  ;
alter database open resetlogs
*
ERROR en línea 1:
ORA-00349: fallo al obtener el tamaño del bloque para
'+FRAN_DATA/fran/onlinelog/group_22'
ORA-15001: diskgroup "FRAN_DATA" does not exist or is not mounted
ORA-15001: diskgroup "FRAN_DATA" does not exist or is not mounted


SQL>  alter database rename file '+FRAN_DATA/fran/onlinelog/group_22' to '+DATA/fran/onlinelog/group_22';

Base de datos modificada.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR en línea 1:
ORA-00349: fallo al obtener el tamaño del bloque para
'+FRAN_DATA/fran/onlinelog/group_23'
ORA-15001: diskgroup "FRAN_DATA" does not exist or is not mounted
ORA-15001: diskgroup "FRAN_DATA" does not exist or is not mounted


SQL> alter database rename file '+FRAN_DATA/fran/onlinelog/group_23' to '+DATA/fran/onlinelog/group_23';

Base de datos modificada.

SQL> alter database rename file '+FRAN_DATA/fran/onlinelog/group_24' to '+DATA/fran/onlinelog/group_24';

Base de datos modificada.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR en línea 1:
ORA-00349: fallo al obtener el tamaño del bloque para
'+FRAN_DATA/fran/onlinelog/group_25'
ORA-15001: diskgroup "FRAN_DATA" does not exist or is not mounted
ORA-15001: diskgroup "FRAN_DATA" does not exist or is not mounted


SQL> alter database rename file '+FRAN_DATA/fran/onlinelog/group_25' to '+DATA/fran/onlinelog/group_25';

Base de datos modificada.

SQL> alter database rename file '+FRAN_DATA/fran/onlinelog/group_26' to '+DATA/fran/onlinelog/group_26';

Base de datos modificada.

SQL> alter database open resetlogs;

Base de datos modificada.

:)