Error connecting ASM ORA-15055: unable to connect to ASM instance Fatal NI connect error 12547 ORA-12547: TNS:lost contact


This Blog is intended to resolve below errors observed in DB alert log file due connectivity issue between DB Instance & ASM disk/Instance


Errors:

Fatal NI connect error 12547, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/u01/app/grid/product/11.2.0/grid/bin/oracle)(ARGV0=oracle+ASM_asmb_remdev)(ENVS='ORACLE_HOME=/u01/app/grid/product/11.2.0/grid,ORACLE_SID=+ASM')(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))'))(enable=setuser)(CONNECT_DATA=(CID=(PROGRAM=oracle)(HOST=remedy-ebu-dev-db1)(USER=ora11g))))
TNS-12547: TNS:lost contact
    ns secondary err code: 12560
    nt main err code: 517

ORA-15055: unable to connect to ASM instance
ORA-12547: TNS:lost contact
TNS-12545: Connect failed because target host or object does not exist

ERROR: Failed to connect with connect string: (DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=stance
ORA-12547: TNS:lost contact
,ORACLE_SID=055: unable to connect to ASM instance



Cause:

$GRID_HOME/bin/oracle or $ORACLE_HOME/bin/oracle permission has got changed

ls -al $GRID_HOME/bin/oracle
-rwxr-x--x 1 grid oinstall 200678464 Feb 28 14:54 oracle

ls -al $ORACLE_HOME/bin/oracle
-rwxr-x--x 1 oracle asmadmin 228886191 Feb 28 15:41 oracle

Solution:

Change permissions as below

cd $GRID_HOME/bin
chmod 6751 oracle

cd $ORACLE_HOME/bin
chmod 6751 oracle

it should look like having sticky bit ..

ls -l $GRID_HOME/bin/oracle
-rwsr-s--x 1 grid oinstall 203974257 May 11 09:30 oracle

ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle oinstall 232399431 May 14 13:47 oracle

Restart the DB instance post changes .. DB will come up

Cross OS Platform Oracle DB Migration Using RMAN


Introduction:


Document highlights cross OS platform migration of Oracle 10g database from HP UX to Sun using RMAN convert method.  


Oracle Doc. For Reference:
Cross-Platform Migration on Destination Host Using RMAN Convert Database [ID 414878.1]

Activity Index:

On Source:

           1. Verify invalid objects on source, invalid indexes, db component status
           2. Re-open DB in read only mode
           3. Verify external references & transportable compatibility w.r.t target platform
           4. Generate transport & convert script, parameter file using RMAN
           5. Shut down database,copy datafiles & transport/convert script/pfile to Target

On Target:

 6. Prepare pfile & create controlfile 
 7. Perform RMAN convert on SYSTEM/UNDO datafiles using convert script generated on source
 8. Re-create Control file with converted datafiles
 9. Execute transport script for migration 
10. Rename datafiles,create spfile 
11. Verify invalid objects, invalid indexes, db component status

Activity Steps:

On Source:


1.Verify invalid objects on source, invalid indexes, db component status


Select count(1),object_type from dba_objects where status <> ‘VALID’ group by object_type;
Select distinct status from dba_indexes ;
select CNAME,version,status from registry$;

2.Re-open DB in read only mode


Select distinct status from v$datafile;
Select distinct status from v$backup;
Shut immediate;
Startup mount
Alter database open read only;

 3.Verify external references & transportable compatibility w.r.t target platform


Below Pl/SQL block should not throw any error

set serveroutput on
 declare
 db_ready boolean;
 begin
 db_ready := dbms_tdb.check_db('Solaris[tm] OE (64-bit)');
 end;
 /

declare
external boolean;
begin
external := dbms_tdb.check_external;
end;
/

 4.Generate transport & convert script, parameter file using RMAN


CONVERT DATABASE ON TARGET PLATFORM
CONVERT SCRIPT '/intecv7/ICT/archive01/rman_convert/convertscript.rman'
TRANSPORT SCRIPT '/intecv7/ICT/archive01/rman_convert/transportscript.sql'
FORMAT '/intecv7/ICT/archive01/rman_convert/%U';
 

 

Generates below three files.

I.   Parameter file
II. Transportscript containing create controlfile & other migration pl/sql’s  
III. Convert script contains rman convert datafile commands


 

     5. Shut down database, copy datafiles & transport/convert script/pfile to Target


Shut immediate;
 <Backup can be initiated followed by Restoration on Target node>
 Copy pfile/transportscript/convert to Target Node




On Target:
  

6. Prepare pfile & create controlfile


Modify udump/bdump/cdump/adump locations as per Target
Modify controlfile locations as specified in transportscript.sql
Verify ORACLE_HOME/ORACLE_SID

SQL>Startup nomount pfile=’ init_00ml2urk_1_0.ora’;
SQL> CREATE CONTROLFILE REUSE SET DATABASE "<db_name>” RESETLOGS  NOARCHIVELOG

..

..
  




 7. Perform RMAN convert on SYSTEM/UNDO datafiles using convert script generated on source


Keep only system/undo datafiles convert commands in convertscript.sql with new datafile locations in format



8. Re-create Control file with converted datafiles



SQL> Shut immediate



Remove current controlfile

Modify transport script with create controlfile command showing  new converted system/undo datafiles locations

SQL> startup nomount pfile=’ init_00ml2urk_1_0.ora’;
 



 9. Execute transport script for migration


  10. Rename datafiles,create spfile

    SQL> create spfile from pfile;

    Rename converted system/undo datafiles to new name  


     11. Verify invalid objects, invalid indexes, db component status

Select count(1),object_type from dba_objects where status <> ‘VALID’ group by object_type;
Select distinct status from dba_indexes ;
select CNAME,version,status from registry$;


ORA-00600 ORA-01114 ORA-27063 WARNING: failed to write mirror WARNING: IO Failed

Post related to below ORA- errors

ORA-00600: internal error code, arguments: [17183], [0x11041A2C8], [], [], [], [], [], []
ORA-01114: IO error writing block to file  (block # )
ORA-27063: number of bytes read/written is incorrect
WARNING: failed to write mirror side 0 of virtual extent  of file in group
WARNING: IO Failed.  au: diskname:

Error:



ORA-00600: internal error code, arguments: [17183], [0x11041A2C8], [], [], [], [], [], []
ORA-01114: IO error writing block to file  (block # )
ORA-01114: IO error writing block to file 201 (block # 647794)
ORA-27063: number of bytes read/written is incorrect

Mostly Seen in ASM ..

WARNING: failed to write mirror side 0 of virtual extent 5060 of file 268 in group 1
WARNING: IO Failed.  au:46079 diskname:/dev/rlv00
         rq:11a868b58 buffer:1124aa000 au_offset(bytes):933888 iosz:114688 operation:1
         status:2

Analysis:

Identifying Impacted file using file number or block# mentioned in error

select * from dba_segments where HEADER_BLOCK='647794';
select * from v$datafile  where file#=201;
select * from v$tempfile  where file#=201;

If DB files managed on ASM then .. Login to ASM instance

select * from v$ASM_FILE where file_number=268 and GROUP_NUMBER=1;

Login to DB instance , to verify file name

select * from v$tempfile where NAME like '%268%';
select * from dba_temp_files where FILE_NAME like '%268%';

AutoExtend found to set ON/YES



Resolution:

Set Autoextend off for identified file & reduce the size

alter database tempfile 1 autoextend off;
alter database tempfile 1 resize 2G;



Thats it Error will dis-appear from alert log

Oracle 10g Standard Edition Installation & Features


Introduction:

Document highlights oracle standard edition installation steps, database hardening as per SOX compliances and issues faced during a project setup along with best practices


Table of Content(TOC):

1.     Platform OS/DB Version  

2.     How to download oracle software
3.     Difference between Enterprise & Standard Edition

4.     Server Pre-requisites
5.     Software & database Installation

6.     Issues faced
7.     Practices
1.     Platform OS/DB Version

This guide describes how to install and configure Oracle Database 10g release 2 (10.2) on AIX 5L based system (64-bit)

OS Platform:                                      IBM AIX5L 5.3

DB version :                                        10.2.0.5
DB Patch Level:                                 PSU APR 2011 (11724962)
DB Software Edition:                      Standard Edition
CPU Type:                                           64-bit
HARDWARE_BITMODE:                64
Oracle OS User :                               ora10g
Oracle groups:                                  oinstall (primary), dba (secondary)
2.     How to download oracle software

Download Link for oracle 10g
Click on radio button & Accept the License:
Oracle Database 10g Release 2 (10.2.0.1.0) Enterprise/Standard Edition for AIX5L :
Download file:
Extract the file:

Oracle Note:  Differences between Enterprise, Standard and Personal Editions on Oracle 10.2 [ID 465465.1]
Features not available in Standard Edition:
I.                    High Availability
Oracle Data Guard :      
·         Standby with/without Data Guard broker configuration, logical standby
Online operations:         
·         online index maintenance/ table organization/ table redefinition
Backup and recovery:   
·         Block-level media recovery
·         Parallel backup and recovery
·         Change-aware incremental backups
·         Duplexed backup sets (backup sets can be written to multiple devices in parallel)
·         Point-in-time tablespace recovery (a tablespace can be recovered up to a specified point in time after a failure or inadvertent transaction execution)
·         Trial recovery (redo is applied in memory only but is not written to disk and is easily rolled back)
·         RMAN Backup Encryption
·         Unused Block Compression
Oracle Flashback features:
·         Oracle Flashback Table
·         Oracle Flashback Database
·         Oracle Flashback Transaction Query
·         Restore Points
II.                  Security
Oracle Advanced Security
Oracle Label Security
Virtual Private Database
Fine-grained auditing
Enterprise User Security
N-tier authentication authorization
III.                Manageability
Oracle Change Management Pack
Oracle Configuration Management Pack
Oracle Diagnostic Pack
Oracle Tuning Pack
Database Resource Manager:
·         Database Resource Manager gives Oracle Database more control over resource management decisions, circumventing problems arising from inefficient operating system management.
IV.                VLDB, Data Warehousing, Business Intelligence
Oracle Partitioning
Oracle OLAP
Oracle Data Mining
Data compression
Bitmapped index and bitmapped join index:
·         This feature provides an index type commonly used in data warehouses for columns with low cardinality, such as 'Y' or 'N', to dramatically improve performance gains in a data warehouse application.
Export transportable tablespace
Asynchronous Change Data Capture:
·         This feature provides a framework for capturing change data, publishing it, and enabling applications to subscribe to the change data in a controlled fashion. Change capture occurs asynchronously based on the information in the Oracle redo logs.
Summary management :
·         Summary management consists of mechanisms to define materialized views and dimensions, refresh and query rewrite mechanisms, and a collection of materialized view analysis and advisory functions and procedures in the DBMS_OLAP package
V.                  Parallel Operations
Parallel query
Parallel DML
Parallel index build:
·         Indexes can be created using parallel processes, significantly improving performance of the operation.
Parallel statistics gathering
Parallel Data Pump export and import
Parallel text index creation
Parallel backup and recovery
VI.                Information Integration
Oracle Streams
Advanced Replication (multimaster)
Oracle Messaging Gateway
VII.              Networking
Connection Manager
Multiprotocol connectivity
 VIII.            Content Management
Oracle Spatial
4.     Server Pre-requisites

I.                    Operating system filesets for AIX 5L
bos.adt.base
bos.adt.lib
bos.adt.libm
bos.perf.libperfstat
bos.perf.perfstat
bos.perf.proctools
xlC.aix50.rte:7.0.0.4 or later
xlC.rte:7.0.0.1 or later
II.              PL/SQL native compilation
    *   IBM XL C/C++ Enterprise Edition V7.0 for AIX PTF (7.0.0.2)
    *   gcc 3.3.2
III.             Pro*C/C++, Oracle Call Interface, Oracle C++ Call Interface, Oracle XML Developer's Kit (XDK)
    *   May 2005 XL C/C++ Enterprise Edition V7.0 for AIX PTF (7.0.0.2)
IV.             Oracle JDBC/OCI Drivers:
    *   JDK 1.4.2 (64-bit)
    *   JDK 1.3.1.11 (32-bit)
    *   JDK 1.2.2.18
V.               Oracle Messaging Gateway:
mqm.Client.Bnd
mqm.Server.Bnd
VI.              APAR:
    *  IY58143: REQUIRED UPDATE FOR AIX 5.3
    *  IY59386: libdepend.mk files are all empty
    *  IY60930: Unable to delete network routes
    *  IY66513: LDR_CNTRL turns on undesirable option when initialized with incorrect value
    *  IY70159: krtl relocation problem
    *  IY68989: eFix for write to mmapped space hangs
    *  IY64361: Exception in putdiag_no_handler() when -O is specified
    *  IY65361: May 2005 XL C Enterprise Edition V7.0 for AIX PTF
    *  IY65362: MAY 2005 XL C/C++ Enterprise Edition V7 for AIX

VII. APAR required for JDK 1.4.2 (64-bit):
* IY63533: DK 1.4.2 64-bit SR1 caix64142-20040917
VIII. APARs required for JDK 1.3.1.11 (32-bit):
* IY58350: SDK 1.3.1 32-BIT SR7P : CA131IFX-20040721A
* IY65305: JAVA142 32-BIT PTF : CA142IFX-20041203
IX. APAR required for JDK 1.2.2.18:
* IY40034: SDK 1.2.2 PTF: CA122-20030115
X. ULIMIT Settings
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) unlimied
coredump(blocks) unlimited
memory(kbytes) unlimited
5.  Software & database Installation
Step 1. Verify server pre-requisites are installed as per TOC_4
Step 2. Verify mount points for oracle binaries & for database for permissions
Step 3. Verify oracle user & primary/secondary group
Step 4. Download Software as per TOC_2 and ftp to server in bin mode
Step 5. Execute rootpre.sh present in $DOWNLOAD_PATH/rootpre/rootpre.sh as root, which will grant necessary privileges on /usr/local/bin
Step 6. Setup GUI
·         Open xming on PC
·         Enable X11 forwarding before logging into server
·         Verify xclock
Step 7. Execute  $DOWNLOAD_PATH/runInstaller.sh
Step 8. Provide below inputs
i.                    Select advance installation & software only
ii.                  Installation type as Standard Edition
iii.                Specify oracle home/base location
iv.                 Verify logs during/after installation present in $ORACLE_HOME/cfgtoollogs
Step 9. Create .profile for newly installed oracle software as below in $HOME/.profile file
ORACLE_HOME=/oracle/app/product/10.2.0/db_1
export ORACLE_HOME
ORACLE_SID=
export ORACLE_SID
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:/usr/local/lib:/lib:/usr/lib
export LD_LIBRARY_PATH
PATH=$PATH:/usr/ccs/bin:$ORACLE_HOME/bin:/usr/bin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/lib
export PATH
Step 10. Database/listener creation
i.                    Execute dbca
ii.                  Select database template as general purpose
iii.                Provide database name
iv.                 Supply sys/system password
v.                   Choose memory parameter values
vi.                 Change character set to WE8ISO8859P15(as per project)
vii.               Verify database file/controlfile/redo log locations
viii.             Verify initialization parameter values
ix.                 Click on prepare db creation script can be used for future use
x.                   Monitor database creation logs under $ORACLE_HOME/cfgtoollogs/dbca
xi.                 Verify db parameter values/character set after DB creation
xii.               Create listener using netca
xiii.             Verify tnsping $ORACLE_SID                                                                                    
6.      Issues faced
Issues faced during a project setup related to DB configuration/Standard Edition are as below.
Issue  1.  Unable to create  Bitmap indexes
Problem: Geneva Application is having a table structure which requires index to create as a Bitmap Index for low cardinality values
Error:
IMP-00003: ORACLE error 439 encountered
ORA-00439: feature not enabled: Bit-mapped indexes
Action/Workaround: B-tree Index has been create
Issue  2.  Unable to create Table Partition
Problem: Geneva Application requires tables to partitioned under XXXX_CORP schema , but receiving below error while importing schema from prod.(Enterprise Edition)
Error:
IMP-00003: ORACLE error 439 encountered
ORA-00439: feature not enabled: Partitioning
IMP-00017: following statement failed with ORACLE error 439:
Action/Workaround: Converted Partitioned tables into heap tables
7.   Best Practices
Best practices are designed based on the database configuration issues faced during application setup
1.       Keep USERS/UNDOTBS tablespace datafile autoextend on during application data loading
2.       Verify dba_sys_privs / synonyms of geneva_admin & XXXX_CORP privileges from production
3.       Select_catalog_role can be granted to geneva_admin/or any temp user to identify missing privileges & revoked later
4.       Modify Partitioned tables to heap tables
5.       Modify Bitmap indexes to b-tree indexes
6.       Sqlnet.ora INBOUND_CONNECTION_TIMEOUT parameter need to be disabled or set to higher value followed by listener bounce
Error:
java.sql.SQLException: Io exception: There is no process to read data written to a pipe.
Refer oracle Note:
JDBC Connections from E-Business Suite Application Tier Fail with "java.sql.SQLException: Io exception: There is no process to read data written to a pipe." [ID 734293.1]
7.        Java 64 bit version should be installed in server
8.       /etc/resolve.conf should not be present incase host is not present in DNS, which imposes additional check while connecting through JDBC thin client & takes additional time to connect
9.       AQ$ table & privileges need to be crosschecked
10.   Invalid Geneva_admin owned wrapped packages/procedures need to be reviewed by Convergys only
11.   Apply latest PSU patch to oracle home
12.    Take cold backup after successful configuration.

My Popular Posts