Category: ORA- ERRORS

“ORA-12505, TNS:listener does not currently know of SID given in connect descriptor” from ORMB ouafDatabasePatch

ouafDatabasePatch.cmd requires Instance Name ( SID ) instead of Service Name.

SYMPTOMS

When  installing Rollup Pack for Oracle Utilities Application Framework Version 4.3.0.1.0, ran ouafDatabasePatch.cmd command , and got the following errors:

D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>set TOOLSBIN=D:\dbpatch_tools\bin
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>set JAVA_HOME=D:\java
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>ouafDatabasePatch.cmd
"CMDLINE::: D:\java\bin\java.exe -cp D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\dbpatch_tools\lib\*;D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\dbpatch_tools\config com.oracle.ouaf.database.patch.OUAFPatch"

Enter the target database type (O/M/D) [O]: O
Enter the username that owns the schema: CISADM
Enter the password for the CISADM user: XXXXXX
Enter the name of the Oracle Database Connection String: ractest-scan.ractest.local:1521:ORMB

Couldn't connect to database ORACLE ractest-scan.ractest.local:1521:ORMB CISADM : java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

In connection string “ractest-scan.ractest.local:1521:ORMB”, ORMB is a service name instead of instance name.

SOLUTION

1)Apply patch 22505470: PATCHES APPEND EXTRA SPACE TO STRINGS AND ADD DBSERVICE CONNECTION SUPPORT.

2) Replace D:\dbpatch_tools\lib with new ones.

OR

2) Add below parameter into LISTENER.ora , and bounce/reload the listener.

USE_SID_AS_SERVICE_listener=on

ORA-39083: Object type TYPE failed to create with error

Using “transform=OID:n” option to avoid ORA-39083 in DataPump.

SYMPTOMS

While importing an ORMB schema from default installation to a new schema for another new ORMB environment, ORA-39083 error occurs:

$impdp directory=DUMP_DIR dumpfile=ORMB.dmp                     \ REMAP_TABLESPACE=CISTS_01:CISADM_1 SCHEMAS=CISADM,CISUSER,      \
CISOPR,CISREAD REMAP_SCHEMA=CISADM:CISADM_1, CISUSER:CISUSER_1, \
CISOPR:CISOPR_1,CISREAD:CISREAD_1,CIS_USER:CIS_USER_1,          \
CIS_READ:CIS_READ_1 logfile=imp_cisadm_2_cisadm_1.log  
...
..
.
CREATE TYPE "CISADM"."TY_RMB_DELETE_BILL_TAB"   OID '40ACB20E52264074E053530F040AF48D' AS TABLE OF "CISADM"."TY_RMB_DELETE_BILL_DTL"
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal

SOLUTION

Add “transform=OID:n” as an impdp parameter.

$impdp directory=DUMP_DIR transform=OID:n dumpfile=ORMB.dmp       \
REMAP_TABLESPACE=CISTS_01:CISADM_1 SCHEMAS=CISADM,CISUSER,CISOPR, \
CISREAD REMAP_SCHEMA=CISADM:CISADM_1,CISUSER:CISUSER_1,           \
CISOPR:CISOPR_1, CISREAD:CISREAD_1,CIS_USER:CIS_USER_1,           \
CIS_READ:CIS_READ_1 logfile=imp_cisadm_2_cisadm_1.log

ORA-01623: log 31 is current log for instance RACTEST3 (thread 3) – cannot drop

Disable thread before dropping the online redo logfiles.

Three nodes RAC database was migrated to two nodes RAC database, while trying to drop online redo logs of thread 3,  the errors are as below:

SQL> alter database drop logfile group 31;
alter database drop logfile group 31
*
ERROR at line 1:
ORA-01623: log 31 is current log for instance RACTEST3 (thread 3) - cannot drop
ORA-00312: online log 31 thread 3: '+DATA1/ractest/onlinelog/group_31.409.924815767'
ORA-00312: online log 31 thread 3: '+FRA/ractest/onlinelog/group_31.262.924815769'

SOLUTION

Disable thread 3 by :

SQL> alter database disable thread 3;

Database altered.

Then drop the redo logs successfully.

SQL> alter database drop logfile group 31;

Database altered.

SQL> alter database drop logfile group 32;

Database altered.

SQL> alter database drop logfile group 33;
Database altered.

...
..
.

ORA-02475: maximum cluster chain block count of 65534 has been exceeded

For 8k block size tablespace, maximum cluster chain block count is 65534. Otherwise bigger block size tablespace is recommended.

This article demonstrates how to reproduce  ORA-02475  error , and explains why it happens and how to resolve this issue.

  1. Create cluster tables and indexes.
SQL> CREATE CLUSTER emp_dept (deptno NUMBER(3)) TABLESPACE users;

Cluster created.

SQL> CREATE TABLE dept 
     (
        deptno NUMBER(3) PRIMARY KEY,
        deptname VARCHAR2(15) NOT NULL
     )
     CLUSTER emp_dept (deptno);

Table created.

SQL> CREATE TABLE emp 
     (
        empno NUMBER(10) PRIMARY KEY,
        ename VARCHAR2(15) NOT NULL,
       deptno NUMBER(3) REFERENCES dept
     )
     CLUSTER emp_dept (deptno);

Table created.

SQL> CREATE INDEX emp_dept_index
         ON CLUSTER emp_dept
         TABLESPACE users ;

Index created.

SQL> insert into dept values ( 1,'DEPT1 1');

1 row created.

SQL> insert into dept values (2,'DEPT1 2');

1 row created.

SQL> insert into dept values (3, 'DEPT1 3');

1 row created.

SQL> commit;

Commit complete.

2.  Insert records into cluster table EMP until gets ORA-02475 error.

SQL> set serveroutput on;
begin
    for i in 1 .. 1000000000
    loop
       begin
           insert into emp values (i,'emp '||i, 3);
           commit;
       exception
       when others
       then
           dbms_output.put_line( SQLERRM );
          exit;
       end;
   end loop;
end;
/

ORA-02475: maximum cluster chain block count of 65534 has been exceeded

PL/SQL procedure successfully completed.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

ORA-00845: MEMORY_TARGET not supported on this system

Make sure Oracle Databases are configured by using ASMM, AMM or HUGE PAGES correctly to avoid performance issues.

SYMPTOM

When start up a database, Database failed to be up with ORA-00845 error.

[oracle@racnode1 ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 15 21:08:17 2015

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

SQL> connect / as sysdba
Connected to an idle instance.

SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system

The alert log has messages as below:

Sun Mar 15 21:08:31 2015
WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 1073741824 bytes. /dev/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 659394560 and used is 499130368 bytes. Ensure that the mount point is /dev/shm for this directory.

SOLUTION

Please confirm that ORACLE_HOME is set correctly. This error sometimes happens when ORACLE_HOME is not set correctly.

Make sure that the /dev/shm size is configured large enough, like in:

# mount -t tmpfs shmfs -o size=2g /dev/shm

In this case, the size of the shared memory device is configured to be 2GB.

In order to make the same change persistent across system reboots, add an entry for this to the /etc/fstab mount table:

shmfs /dev/shm tmpfs size=2g 0 0

NOTE:

  1. You should check with your System Administrator what the “best” size for /dev/shm is, based on what has been reported in the alert file.
  2. Also, many best practices now suggest disabling AMM especially in Exa* Engineered boxes that have larger memory capability, and can use Huge / Large pages.
    This is because AMM and Huge / Large pages are mutually exclusive and overall performance will be better using Huge pages.