srvctl start database fails with ORA-03113,ORA-27300,ORA-27301,ORA-27302,ORA-27303.

Yesterday I was trying to start DB services using SRVCTL on one of the new environment. I was able to start individual database instances using sqlplus, but it was giving issues for SRVCTL, I was able to start one of the two instances using SRVCTL, but not both, it was giving following error:


[oracle@uat-srv-fin2 dbs] Ora:TESTDB $ srvctl start database -d TESTDB
PRCR-1079 : Failed to start resource ora.testdb.db
CRS-5017: The resource action "ora.testdb.db start" encountered the following error:
ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 0 Serial number: 0
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0.4/grid/log/uat_srv-fin1/agent/crsd/oraagent_oracle//oraagent_oracle.log".
CRS-2674: Start of 'ora.testdb.db' on 'uat-srv-fin1' failed
CRS-2632: There are no more servers to try to place resource 'ora.testdb.db' on that would satisfy its placement policy.

After checking all RAC related init parameters, and making sure everything is right there, I had a look at DB alert log so as to investigate further and I got something useful :


Errors in file /u01/app/oracle/diag/rdbms/testdb/TESTDB1/trace/TESTDB1_dia0_28019_base_1.trc:
ORA-27506: IPC error connecting to a port
ORA-27300: OS system dependent operation:proto mismatch failed with status: 0
ORA-27301: OS failure message: Error 0
ORA-27302: failure occurred at: skgxpcon
ORA-27303: additional information: Protocol of this IPC does not match remote (192.168.52.42). SKGXP IPC libraries must be the same version. [local: RDS,remote: UDP]
Errors in file /u01/app/oracle/diag/rdbms/testdb/TESTDB1/trace/TESTDB1_lmon_28020.trc:
ORA-27550: Target ID protocol check failed. tid vers=1, type=1, remote instance number=2, local instance number=1
LMON (ospid: 28020): terminating the instance due to error 481
System state dump requested by (instance=1, osid=28020 (LMON)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/testdb/TESTDB1/trace/TESTDB1_diag_27999_20160815112931.trc
Dumping diagnostic data in directory=[cdmp_20160815112932], requested by (instance=1, osid=28020 (LMON)), summary=[abnormal instance termination].
Instance terminated by LMON, pid = 28020

So messages were pointing to RDS/UDP binary linking.

As per metalink note : ORA-27303: SKGXP IPC libraries must be the same version. [local: RDS,remote: UDP] on Exadata (Doc ID 1574772.1)

RAC instances can’t see communicate with each other. IPC error is due to mismatch in cluster interconnect protocol being in use by these 2 nodes. Node2 used UDP and Node1 uses RDS now. So due to this mismatch the other node is down. Exadata does not support mixing of UDP and RDS ports.

As a solution I did following steps so as to resolve the issue:

1. Shutdown all the instances running on this ORACLE_HOME which was configured for the instance having UDP port
– Execute as oracle user,
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk ipc_rds ioracle
2. Startup the instance using srvctl

Now I was able to start database using SRVCTL without any issues:


[oracle@uat-srv-fin2 ~] Ora:TESTDB $ srvctl start database -d TESTDB
[oracle@uat-srv-fin2 ~] Ora:TESTDB $
[oracle@uat-srv-fin2 ~] Ora:TESTDB $
[oracle@uat-srv-fin2 ~] Ora:TESTDB $ srvctl status database -d TESTDB
Instance TESTDB1 is running on node uat_srv-fin1
Instance TESTDB2 is running on node uat_srv-fin2

Hope so u will find this post very useful:-)

Cheers

Regards,

Adityanath

 

Advertisements

ORA-08103 Object no longer exists during select operation

Yesterday, application team informed us about production job failure with error: ORA-08103 Object no longer exists. It was simple select query on object with type VIEW. I tried rerunning query which ran without any issue.

I tried to investigate, if the underlying objects have been dropped when job was running. But object creation time was in the year 2014 so definitely object was not dropped & recreated and it was present during job execution.

I tried to search in metalink support & found following note explaining issue:

OERR: ORA-8103 “object no longer exists” Master Note / Troubleshooting, Diagnostic and Solution (Doc ID 8103.1)

According to note: We get this error when, tables are being dropped/truncated while a SQL statement for those tables is still in execution. In the case of an index, it might be caused by an index rebuild. In other words the object has been deleted by another session since the operation began.

I had a look into aud$ & found one of the underlying table in view was truncated at same time when job was executing select on view. So I got the culprit 🙂

But why oracle gave error: ORA-08103 Object no longer exists even when object was present???

If you check DBA_OBJECTS dictionary view, it has two columns OBJECT_ID & DATA_OBJECT_ID. Each object is assigned a unique number to recognise it in the database (OBJECT_ID). In the same manner, each object is linked to a segment. The data object id is assigned to that segment and any kind of change done on the physical segment would lead to give a change in that number (DATA_OBJECT_ID). Both the numbers are same initially but when the modifications happen on the segment, the DATA_OBJECT_ID changes. Both the OBJECT_ID and DATA_OBJECT_ID are the numbers used by oracle to denote the metadata for the object that is used within the data dictionary. Operations such as Truncate, Move, Rebuild Index, Spilt Partition etc would cause change in DATA_OBJECT_ID of the objects.

So this gives enough justification for oracle error: ORA-08103 Object no longer exists!!

Hope so u will find this post very useful:-)

Cheers

Regards,

Adityanath

Resolving ORA-1031 while connecting as “/ as sysdba”

In this post, we are discussing about issue which I faced on fresh oracle software install.


[oracle@test1.test.com ] Ora:TEST2 $ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Mon May 30 12:30:31 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges

Normally this issue points to incorrect permissions on oracle binaries OR incorrect value for SQLNET.AUTHENTICATION_SERVICES in sqlnet.ora. But in my case both were correctly set.

After checking on metalink, I found following note related to the same issue, which explains different scenarios, on of them is if the OS user is part of the OSDBA group.

Troubleshooting ORA-1031 or ORA-1017 Errors When Connecting As SYSDBA (Doc ID 730067.1)

I tried to compare file “$ORACLE_HOME/rdbms/lib/config.s” with other oracle home where sqlplus “/ as sysdba” was working fine.

Note: config file name vary from OS to OS on some OS it is config.c and on some OS it is config.s


[oracle@test1.test.com ] Ora: TEST1 $ diff config.s /u01/app/oracle/TEST2/11.2.0.4/rdbms/lib/config.s
23c23
< .ascii "oinstall\0"
---
> .ascii "\0"

I found the culprit!!! In my new installation, Library file was missing group details. After making all necessary changes, I had to relink all oracle binaries.

For binary relink, perform following steps:

– Make sure that no oracle processes running
– Login as oracle
– Make sure LD_LIBRARY_PATH and ORACLE_HOME are set properly
– $ORACLE_HOME/bin/relink all

After binary relink, sqlplus “/ as sysdba” started working as expected.

Done!!!!!!!!!!

Hope so u will find this post very useful:-)

Cheers

Regards,

Adityanath

Create Private Database Link in other Schema using SYS/SYSTEM

Scenario: Create database links owned by application user whose credentials are not shared with DBA

In Reality: For creating private database link, you will need to connect to user & then create it. If you try to create it using SYS as <owner>.<db_link_name>, Oracle will create DB LINK with name <owner>.<db_link_name> under SYS schema. Example..


SQL> create DATABASE LINK OWN1.DBLINK1 CONNECT TO test_link IDENTIFIED BY test_link USING '(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=test1.test1.com) (PORT=1606)) (CONNECT_DATA= (SERVICE_NAME=TEST1)))';
Database link created.
SQL> select OWNER,DB_LINK from dba_db_links;
OWNER    DB_LINK
-------- ---------------
SYS      OWN1.DBLINK1

Solution: Use the following script to create private database link from SYS into application schema. This will be useful to create the database links sitting under different schemas.

Contents of script:

Script Name: create_schema_dblink.sh


username=$1
db_link=$2
sqlplus /nolog <<!
spool $1_$2_create_dblink.log
conn / as sysdba
prompt " DB Link Before create"
set lines 100
col db_link for a30
select owner,db_link from dba_db_links where owner=upper('$1') and db_link=upper('$2') ;
CREATE or replace PROCEDURE $1.create_db_link AS
strg varchar2(1000);
BEGIN
strg:= 'create DATABASE LINK $2 CONNECT TO test_link IDENTIFIED BY test_link USING '''||'(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=test1.test1.com) (PORT=1606)) (CONNECT_DATA= (SERVICE_NAME=TEST1)))''' ;
EXECUTE IMMEDIATE strg;
END ;
/
execute $1.create_db_link;
drop procedure $1.create_db_link;
prompt " DB Link After create"
select owner,db_link from dba_db_links where owner=upper('$1') and db_link=upper('$2') ;
!

Script Execution:

Usage:  sh create_schema_dblink.sh  <Application Schema> <DB Link Name>


[test@test.test.com~]$ sh create_schema_dblink.sh own1 DBLINK1
SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 17 16:49:40 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> Connected.
SQL> " DB Link Before create"
SQL> 
no rows selected
SQL> 2 3 4 5 6 7
Procedure created.
SQL>
PL/SQL procedure successfully completed.
SQL>
Procedure dropped.
SQL> " DB Link After create"
SQL>
OWNER   DB_LINK
-----   ------------------------------
OWN1    DBLINK1
SQL>
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Make sure user under which db link needs to be created has ‘CREATE DATABASE LINK’ privilege, else this script will fail with following error:

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at “OWN1.CREATE_DB_LINK”, line 5
ORA-06512: at line 1

For dropping private database link owned by application schema using SYS user, use following link:

how-to-drop-other-schemas-database-link-using-sys-user

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

From last two days, I have been getting “ORA-27054: NFS file system not mounted with correct options” error while running an RMAN backup to a Sun ZFS Storage Appliance.

This error have been observed particularly, while taking controlfile backups. RMAN datafile or archivelog backups without controlfile were running fine.


RMAN-08132: WARNING: cannot update recovery area reclaimable file list
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ch01 channel at 02/15/2016 16:02:44
ORA-01580: error creating control backup file /mnt/bkp1/TEST1/TEST1_CTRL_snapcf.ctl
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 3
Additional information: 1

After searching on MOS, I found following note, which explains different mount point options for configuring the NFS mounts.

Sun ZFS Storage Appliance: Oracle Database 11g R2 NFS Mount Point Recommendations (Doc ID 1567137.1)

After discussing the same with SOLARIS team, we found that backup mount point were mounted with all recommended parameters.

So tried to search more documents on MOS and found the one:

Oracle ZFS Storage: FAQ: Exadata RMAN Backup with The Oracle ZFS Storage Appliance (Doc ID 1354980.1)

As per note “DNFS is strongly recommended when protecting an Exadata with the ZFS Storage Appliance. It is required to achieve the published backup and restore rates”.

You can confirm that DNFS is enabled by running following query on database:

select * from v$dnfs_servers;

In my case it was not enabled. DNFS to be enabled on a each database node with the following command:

$ make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk dnfs_on

dcli may be used to enable Oracle Direct NFS on all of the database nodes simultaneously:

$ dcli -l oracle -g /home/oracle/dbs_group make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk dnfs_on

The database instance should be restarted after enabling Oracle Direct NFS.

After enabling DNFS activity, my backup started running without any issues.

Done!!!!!!!!!!

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

How to Migrate characterset of 12c database using Database Migration Assistant for Unicode (DMU) Tool

This post will help you to convert characterset of database from WE8ISO8859P15 to AL32UTF8 in 12C. From 12c you will need to use DMU tool for characterset conversion instead of traditional method of CSSCAN & CSALTER. DMU is the Database Migration Assistant for Unicode . It converts the NLS_CHARACTERSET of an existing database to AL32UTF8 or UTF8.

DMU divides coversion into 4 steps:

1. Install DMU repository.
2. Scan the database.
3. Resolve the migration issues.
4. Convert the database.

Once DMU is started, you will need to create database connection as given below:

dmu1 dmu2

So as to make use of DMU, you need to create DBMS_DUMA_INTERNAL package under SYS schema. For creating mentioned package, you need to run following script.

?/rdbms/admin/prvtdumi.plb

If not you will get following error:

dmu3

Then you can complete DMU repository creation:

dmu4 dmu5 dmu6 dmu7

dmu8

dmu9

Now your database is ready for SCAN:

dmu10

dmu11

dmu12

dmu13dmu14

dmu15

Wait till scanning completes then right click the connection name and choose “Database Scan Report”

dmu16 dmu17 dmu18

Once you get results, you need to tackle invalid representations & Over column limit issues.

For invalid representations, you will need to right click a table and choose “Cleansing Editor”. The characters with issues will appear as small squares. You can edit data so as to fix invalid represenations.

For over column limit, you will need to use bulk cleansing option as shown below:

dmu22 dmu23 dmu23 dmu24 dmu25 dmu26 dmu27 dmu28

Now you will notice no migration issues observed after scanning database.

dmu29

Now your database is ready for characterset migration🙂

dmu30 dmu31 dmu32 dmu33

Done!!!!!!!!!!

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath