ORA-27047: Unable to read the header block of file

We were performing 11i cloning but unfortunately the adcfgclone.pl failed on target while controlfile creation with the following error

• Creating the control file may report error ORA-27047 when OS block header is invalid for a particular datafile:

ORA-27047 : unable to read the header block of file.

Example taken from HPUX:

ORA-1503: CREATE CONTROLFILE failed
ORA-1565: error in identifying file '/oradata/users.dbf'
ORA-27047: unable to read the header block of file
HP-UX Error: 22: Invalid argument
Additional information: 2

• DBVerify does not report a corruption in the datafile.
  

Cause

OS block in the datafile is corrupted.

• The corruption is identified by the Oracle utility dbfsize:

$ dbfsize /oradata/users.dbf
Header block magic number is bad

For complete information follow metalink note ref: 360032.1

Compiling Invalid objects

DBMS_DDL.ALTER_COMPILE

Definition
This procedure is equivalent to the following SQL statement:
ALTER PROCEDUREFUNCTIONPACKAGE [.] COMPILE [BODY]

Syntax
Exec dbms_ddl.alter_compile ( type , schema, name);
Type : Must be either PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY or TRIGGER.
Schema : Database Username

Name : Objects name
Example
SQL> exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST');

PL/SQL procedure successfully completed.

DBMS_UTILITY.COMPILE_SCHEMA

Definition
This procedure compiles all procedures, functions, packages, and triggers in the specified schema.

Syntax
Exec dbms_utility.compile_schema ( schema,compile all)

Schema : Database Username
Compile All : Object type ( procedure, function, packages,trigger)

Example
SQL> exec dbms_utility.compile_schema('SCOTT');

PL/SQL procedure successfully completed.

UTL_RECOMP

Definition
This script is particularly useful after a major-version upgrade that typically invalidates all PL/SQL and Java objects.

Syntax
Exec UTL_RECOMP.RECOMP_SERIAL ();

Example
SQL> Exec UTL_RECOMP.RECOMP_SERIAL ();

PL/SQL procedure successfully completed.

Note: Required SYS user to run this package.

UTLRP.SQL scripts

Definition
Recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, and types.

Syntax
Located: $ORACLE_HOME/rdbms/admin

Example
SQL> @c:\oracle\product\10.1.0\db_1\rdbms\admin\UTLRP.SQL

TIMESTAMP
-----------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2007-08-04 12:47:21

PL/SQL procedure successfully completed.

TIMESTAMP
-----------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2007-08-04 12:47:26

PL/SQL procedure successfully completed.

Note: Required SYS user to run this script.
Recommended: After upgrade or migrate database.

Best Approach is manually recompiling all Invalid Objects

Spool recompile.sql
Select 'alter 'object_type' 'object_name' compile;'
From user_objects
Where status <> 'VALID'
And object_type IN ('VIEW','SYNONYM',
'PROCEDURE','FUNCTION',
'PACKAGE','TRIGGER');
Spool off
@recompile.sql

Note: VIEW,SYNONYM,PROCEDURE,PACKAGE,FUNCTION,TRIGGER

Spool pkg_body.sql
Select 'alter package 'object_name' compile body;'
From user_objects
where status <> 'VALID'
And object_type = 'PACKAGE BODY';
Spool off
@pkg_body.sql

Spool undefined.sql
select 'alter materizlized view 'object_name' compile;'
From user_objects
where status <> 'VALID'
And object_type ='UNDEFINED';
Spool off
@undefined.sql

Spool javaclass.sql
Select ‘alter java class 'object_name' resolve;'
from user_objects
where status <> 'VALID'
And object_type ='JAVA CLASS';
Spool off
@javaclass.sql

Spool typebody.sql
Select ‘alter type 'object_name' compile body;'
From user_objects
where status <> 'VALID'
And object_type ='TYPE BODY';
Spool off
@typebody.sql

Spool public_synonym.sql
Select 'alter public synonym 'object_name' compile;'
From user_objects
Where status <> 'VALID'
And owner = ‘PUBLIC’
And object_type = 'SYNONYM';
Spool off
@public_synonym.sql

Troubleshooting listener issues in RAC

The backup kept failing to make a connection to the database using EBR_RACDB tns string.
This was because the service was not activated in CRS stack. First create the service and
add the service as follows and this will resolve the issue.

run this command to see if all service is running.

$ORA_CRS_HOME/bin>crsstat -t

HA Resource Target State
------------------------------- --------- -----------------
ora.RACDB.RACDB1.RACDB1.srv ONLINE ONLINE on node1
ora.RACDB.RACDB1.cs ONLINE ONLINE on node1
ora.RACDB.RACDB1.inst ONLINE ONLINE on node1
ora.RACDB.RACDB2.RACDB2.srv ONLINE ONLINE on node2
ora.RACDB.RACDB2.cs ONLINE ONLINE on node2
ora.RACDB.RACDB2.inst ONLINE ONLINE on node2

If any of the above services appear offline or doesnt appear at all. Then add the services to the CRS stack and start the services.

srvctl add service -d RACDB -s RACDB1 -r RACDB -a RACDB1 -P NONE

srvctl start service -d RACDB -s RACDB1 -i RACDB1

srvctl add service -d RACDB -s RACDB2 -r RACDB -a RACDB2 -P NONE

srvctl start service -d RACDB -s RACDB2 -i RACDB2

Once all the services are up and running check the connectivity.

node1:RACDB:/ora/admin>rman target ebr@ebr_RACDB

node1:RACDB1:/ora/admin>rman target ebr@EBR_RACDB

Recovery Manager: Release 10.2.0.3.0 - Production on Tue Feb 16 20:51:33 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

target database Password: <-- feed it with the password
connected to target database: RACDB (DBID=1894566972)

Oracle RAC configuration for EBR.

We have decided to go with EBR as the backup startegy for RAC. This is bit tricky we have to first register the EBR VIP into the CRS Stack and then start the resources for the EBR to actually backup the database. First let us see few characteristics of EBR_VIP as follows:

The EBR_VIP is required to be configured on only one node of the cluster
• The EBR_VIP can be moved to any node in the cluster providing each node has NICs
configured on the EBR network
• The EBR_VIP exists on only one node in the cluster at any time
• The EBR_VIP IP address is provided in the EBR Client Design document, provided by
the EBR Team
• The EBR backup server only communicates with the node on which the EBR_VIP
resides, hence the entire backup or restore datastream is via this node
• The EBR_VIP is configured in DNS with the name provided in the Client EBR Design
document. This name is the Netbackup Client Name.


Creating EBR VIP
------------------------------------------------

crs_profile –create ebr_vip1 -t application \
-a $CRS_HOME/bin/usrvip \
-o oi=nxge6,ov=10.106.194.87,on=255.255.252.0

• ebr_vip1 is the name assigned to the new CRS resource

• $CRS_HOME is the home directory for the Oracle Clusterware installation.

• nxge6 is the name of the public network adapter

• 10.106.194.87 is the EBR_VIP1 cluster IP address that will be used by EBR to

• connect regardless of the node it is running on.

• 255.255.252.0 is the netmask for the public IP address

Registering the EBR VIP in CRS stack
-----------------------------------------
crs_register ebr_vip1

as root user
-----------------------------------------
./crs_setperm ebr_vip1 –o root

./crs_setperm ebr_vip1 –u user:oracle:r-x

as oracle user start the ebr_vip1
----------------------------------------
crs_start ebr_vip1

check the resource is started
---------------------------------------
crs_stat | grep ebr_vip1

If there are two databases register EBR VIP per database create the EBR_VIP2 same as the above.

Check the resource has been started:

cd $ORA_CRS_HOME/bin

crs_stat | grep ebr_vip1

Once the VIP is registered create the TNS alias entries Add the service names to tnsnames.ora on both the nodes If its is two node RAC configuration as follows:

Node1:

EBR_RACDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1-oravip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node2-oravip)(PORT = 1521))
(LOAD_BALANCE=OFF)

)
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = RACDB1))
)

Node2:

EBR_RACDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1-oravip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node2-oravip)(PORT = 1521))
(LOAD_BALANCE=OFF)

)
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = RACDB2))
)

Create the EBR database user and grant necessary privileges
--------------------------------------------------------------
create user EBR identified by
2 default tablespace USERS
3 temporary tablespace TEMP;

grant SYSDBA to EBR;
grant create, Alter session privilege to EBR;

Now test the connectivity on both the nodes as follows:

Node1-> sqlplus EBR/password@EBR_RACDB as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Feb 21 14:19:46 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining
options

Oracle Configuration Manager

Today i have encountered some errors in the Alert log regarding ORACLE OCM.

ORA-12012: error on auto execute of job 32184
ORA-06550: line ORA-06550: line 1, column 476:
PLS-00201: identifier 'ORACLE_OCM.MGMT_DB_LL_METRICS' must be declared
ORA-06550: line 1, column 476:
PL/SQL: Statement ignored
, column :

Found out that OCM was installed along with the binaries. As we are not using OCM in any of our environments the solution to this problem is as follows:

Steps to uninstall OCM from DB tier:
-------------------------------------------------

1- Set your ORACLE_HOME
export ORACLE_HOME=

2- From your rdbms Oracle Home please connect using sqlplus as sysdba user to your database and run the following commands:

SQL> @ccr/admin/scripts/dropocm.sql;

3- To stop the Scheduler and remove the service or the crontab entry, enter the following command:

$ORACLE_HOME/ccr/bin/deployPackages -d /ccr/inventory/core.jar

4- Delete the ccr directory by entering the following command:

$rm -rf /ccr

5- Verify no services or cron entry. Run the following commands to confirm:
a- ps -ef|grep ccr|grep -v grep
b- crontab -l|grep ccr
c- crontab -l

If you dont find the admin directory under $ORACLE_HOME/ccr then follow the below solution.

Login to that DB with sys and confirm that you have :
"ORACLE_OCM" user exist or not ?
"ORACLE_OCM_CONFIG_DIR" directory exist or not ?

If this user and directory exist In order to manually remove the ORACLE_OCM user and associated objects from database, run:

SQL> drop user ORACLE_OCM cascade;
SQL> drop directory ORACLE_OCM_CONFIG_DIR;

Then also remove the $ORACLE_HOME/ccr directory.

This resolved the issue and couldn't find any more errors in the alert log.

First Post 21/02/2011

Today I felt like creating a blog for myself an Online Oracle journal with all the issues that I have come across and the workarounds/ Solutions I have implemented to overcome them. Here goes some of my experiences through this Blog. This is very much useful to me when I want to go back and see what the issues I have encountered. Instead of searching my desktop folders and don't find the correct resource and scratching my head for not able to recollect what I have implemented.