Archive

Archive for January, 2010

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

January 13, 2010 Leave a comment

Symptoms

Today,  I transfer a schema from 10.2.0.3 database to 11.1.0.7 database. All tables can be transferred by expdp/impdp except of one with following when it is imported by imdp

With the Real Application Clusters option
Master table “SYSTEM”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_TABLE_01″:  system/******** parfile=imp_sgcmsqa_tab.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE failed to create with error:
ORA-00922: missing or invalid option
Failing sql is:
CREATE TABLE “CMSADM”.”VOD_METADATA_UPLOAD” (“ID” NUMBER(19,0) NOT NULL ENABLE, “UPLOAD_DATA” BLOB, “FILE_NAME” VARCHAR2(100 BYTE), “IS_LOAD” NUMBER(1,0), “IS_VALIDATE” NUMBER(1,0), “LAST_LOAD_DATE” TIMESTAMP (6), “LIBRARY_ID” VARCHAR2(30 BYTE) NOT NULL ENABLE, “IS_DELETE” NUMBER(1,0), “VERSION” NUMBER(10,0) NOT NULL ENABLE, “UPDATER_ID” NUMBER(19,0), “AUDIT_ID” NUMBER(19,0), “CREATOR_ID” NUMBER(
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39112: Dependent object type INDEX:”CMSADM”.”VOD_METADATA_UPLOAD_PK” skipped, base object type TABLE:”CMSADM”.”VOD_METADATA_UPLOAD” creation failed
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:”CMSADM”.”VOD_METADATA_UPLOAD_PK” skipped, base object type TABLE:”CMSADM”.”VOD_METADATA_UPLOAD” creation failed
Job “SYSTEM”.”SYS_IMPORT_TABLE_01″ completed with 3 error(s) at 16:27:36

Fixed

Add version option as the following impdp parfile, it is fixed.

dumpfile=EXPDMP:sgcmsqa.dmp
LOGFILE=expdmp:imp_sgcmsqa_tab.log
TABLE_EXISTS_ACTION=REPLACE
version=10.2.0.3 <– add this option
REMAP_SCHEMA=(CMSADM:CMSADM)
tables=VOD_METADATA_UPLOAD

$ impdp system/oracle parfile=imp_sgcmsqa_tab.par

Import: Release 11.1.0.7.0 – 64bit Production on Wednesday, 13 January, 2010 16:40:19

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Release 11.1.0.7.0 – 64bit Production
With the Real Application Clusters option
Master table “SYSTEM”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_TABLE_01″:  system/******** parfile=imp_sgcmsqa_tab.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “CMSADM”.”VOD_METADATA_UPLOAD”              6.111 MB      47 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “SYSTEM”.”SYS_IMPORT_TABLE_01″ successfully completed at 16:40:37

Categories: TroubleShooting

WARNING 10.0.0.0 could not be translated to a network address error 1

January 5, 2010 Leave a comment

Symptoms

1. When the database is started,  get the following warning messages in the alert log

Interface type 1 bge2 10.0.0.0 configured from OCR for use as a cluster interconnect
WARNING 10.0.0.0 could not be translated to a network address error 1
Interface type 1 fjgi1 10.0.0.0 configured from OCR for use as a cluster interconnect
WARNING 10.0.0.0 could not be translated to a network address error 1
Interface type 1 bge1 172.31.0.0 configured from OCR for use as  a public interface
Interface type 1 fjgi0 172.31.0.0 configured from OCR for use as  a public interface
WARNING: No cluster interconnect has been specified. Depending on
the communication driver configured Oracle cluster traffic
may be directed to the public interface of this machine.
Oracle recommends that RAC clustered databases be configured
with a private interconnect for enhanced security and
performance.

2.  IPMP is configured for the private interconnect and public interface

bge1: flags=201000843<UP,BROADCAST,RUNNING,MULTICAST,IPv4,CoS> mtu 1500 index 3
inet 172.31.3.124 netmask ffffff00 broadcast 172.31.3.255
groupname orapub <– public network IPMP group
bge1:1: flags=209040843<UP,BROADCAST,RUNNING,MULTICAST,DEPRECATED,IPv4,NOFAILOVER,CoS> mtu 1500 index 3
inet 172.31.3.121 netmask ffffff00 broadcast 172.31.3.255
bge1:2: flags=201040843<UP,BROADCAST,RUNNING,MULTICAST,DEPRECATED,IPv4,CoS> mtu 1500 index 3
inet 172.31.3.128 netmask ffffff00 broadcast 172.31.3.255
bge2: flags=201000843<UP,BROADCAST,RUNNING,MULTICAST,IPv4,CoS> mtu 1500 index 4
inet 10.1.1.124 netmask ffffff00 broadcast 10.1.1.255
groupname orapriv <– private interconnections IPMP group
bge2:1: flags=209040843<UP,BROADCAST,RUNNING,MULTICAST,DEPRECATED,IPv4,NOFAILOVER,CoS> mtu 1500 index 4
inet 10.1.1.121 netmask ffffff00 broadcast 10.1.1.255
fjgi0: flags=269040843<UP,BROADCAST,RUNNING,MULTICAST,DEPRECATED,IPv4,NOFAILOVER,STANDBY,INACTIVE,CoS> mtu 1500 index 5
inet 172.31.3.122 netmask ffffff00 broadcast 172.31.3.255
groupname orapub
fjgi1: flags=269040843<UP,BROADCAST,RUNNING,MULTICAST,DEPRECATED,IPv4,NOFAILOVER,STANDBY,INACTIVE,CoS> mtu 1500 index 6
inet 10.1.1.122 netmask ffffff00 broadcast 10.1.1.255
groupname orapriv

Cause

When the instance starts, it gets two NIC interfaces configured as  public network and cluster interconnect according to OCR. To fix it, we have to remove the private interconnect and public interface configuration from OCR and specific it in the database initial parameter file.

Fix

1. To remove public and cluster interconnect from OCR

# oifcfg delif -global <if-name>

2. To update spfile initial parameter

ALTER SYSTEM SET cluster_interconnects=’10.1.1.124′ SCOPE=SPFILE SID=’vstbpro1′;

ALTER SYSTEM SET cluster_interconnects=’10.1.1.123′ SCOPE=SPFILE SID=’vstbpro2′;

3. Restart instance

Categories: TroubleShooting

ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 1

January 4, 2010 1 comment

symptoms

1. Alert logs show the following ORA errors

adrci> show incidents

ADR Home = /opt/oracle/diag/rdbms/vstbpro/vstbpro1:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
——————– ———————————————————– —————————————-
197285               ORA 7445 [_lwp_kill()+8]                                    2010-01-04 14:01:59.020491 +08:00
197273               ORA 7445 [_lwp_kill()+8]                                    2010-01-04 14:04:34.095472 +08:00
197272               ORA 7445 [_lwp_kill()+8]                                    2010-01-04 14:01:32.782533 +08:00
197271               ORA 7445 [_lwp_kill()+8]                                    2010-01-04 13:59:34.266461 +08:00
197270               ORA 7445 [_lwp_kill()+8]                                    2010-01-04 13:58:31.472789 +08:00
197260               ORA 600 [12240]                                             2009-12-14 17:55:49.662613 +08:00
197221               ORA 7445 [_lwp_kill()+8]                                    2010-01-02 13:46:58.782261 +08:00
197220               ORA 7445 [_lwp_kill()+8]                                    2010-01-02 13:43:57.491617 +08:00
197212               ORA 7445 [_lwp_kill()+8]                                    2010-01-02 13:48:23.337204 +08:00
197144               ORA 700 [kgeade_is_0]                                       2010-01-02 13:44:21.351298 +08:00
197143               ORA 700 [kgeade_is_0]                                       2010-01-02 13:43:19.078170 +08:00
197142               ORA 700 [kgeade_is_0]                                       2010-01-02 13:43:16.154236 +08:00
197141               ORA 700 [kgeade_is_0]                                       2010-01-02 13:43:13.235147 +08:00

2. Query gv$session to trigger the following errors, but there is problem to query v$session

SQL>  select machine from gv$session;
select machine from gv$session
*
ERROR at line 1:
ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 1
allocated
ORA-12801: error signaled in parallel query server P002, instance 3599
ORA-00000: normal, successful completion

3. The initial value of parallel_execution_message_size are different on two instance

Cause

Parallel_execution_message_size is not the same on all nodes in a cluster, a parallel query can not spawn on all nodes parallel execution servers what is need for a query against a gv$ view.

Fix

1. Set the parallel_execution_message_size to be same on both cluster nodes and restart them to take effect immediately

2. Verify the solution by querying the gv$session after restarting. It shows that the query is successfully returned as below.

SQL> select machine from gv$session;

MACHINE
—————————————————————-
wvpsgesb01
wvpsgesb01
wvpdb08
wvpdb08
wvpsgesb01
wvpsgesb01
wvpsgesb01

Categories: TroubleShooting