Error Description
The following queries fails with ORA-920.
Case 01:
SQL> select r.pack_id r,p.rate_id e from rate_to_send r
2 JOIN package_rate p ON(r.pack_id.p.rate_id);
JOIN package_rate p ON(r.pack_id.p.rate_id)
*
ERROR at line 2:
ORA-00920: invalid relational operator
A variant of ORA-920 is below.
Case 02:
SQL> select table_name from user_tables where (table_name, tablespace_name)=
2 (('TEST1','USERS'), ('TEST2','USERS'));
(('TEST1','USERS'), ('TEST2','USERS'))
*
ERROR at line 2:
ORA-00920: invalid relational operator
Cause of the Error
ORA-00920 is occurred if there is invalid or missing relational operator between JOIN condition or within WHERE clause.
Solution of the Problem
In the case 01, within JOIN condition no equal (=) sign is there. Relation operator is not present there and hence error occurred. Valid relational operators are =, !=, ^=, <>, >, <, >=, <=, ALL, ANY, [NOT] BETWEEN, EXISTS, [NOT] IN, IS [NOT] NULL, or [NOT] LIKE in the condition. So ensure that in the condition no other relation operator is present without these operators. Following would correct the problem.
SQL> select r.pack_id r,p.rate_id e from rate_to_send r
2 JOIN package_rate p ON(r.pack_id=p.rate_id);
no rows selected
In the case 02, same error returned because oracle does not permit to compare values in this way. If there is equal operator then on the right hand side of the operator there is needed a single value. A list of values are not permitted.
You can fix the case 02 statement by simply adding ANY clause after the equal sign.
SQL> select table_name from user_tables where (table_name, tablespace_name)=ANY
2 (('TEST1','USERS'), ('TEST2','USERS'));
TABLE_NAME
------------------------------
TEST2
TEST2 table resides under USERS tablespace.
Alternatively you can use IN clause instead of (=ANY).
SQL> select table_name from user_tables where (table_name, tablespace_name) IN
2 (('TEST1','USERS'), ('TEST2','USERS'));
TABLE_NAME
------------------------------
TEST2
Related Documents
Saturday, February 7, 2009
Friday, February 6, 2009
New features in Oracle database 11g of data pump utility
1)With release of 11g data pump client the parameter COMPRESSION is enhanced. With COMPRESSION parameter we now can also compress data. Now the value of the COMPRESSION parameter can be specified to ALL, METADATA_ONLY, DATA_ONLY and NONE by which we can compress both data and metadata, only data, only metadata, or no data during an data pump export. METADATA_ONLY is the default.
Prior to 11g only METADATA_ONLY and NONE could be specified. With now COMPRESSION=ALL, dumpfile size can be greatly reduced so no need to use any compression utility to compress the dumpfile further.
2)The parameter ENCRYPTION, ENCRYPTION_ALGORITHM, ENCRYPTION_MODE are introduced.
With ENCRYPTION parameter you can choose to encrypt both data and metadata, only data, only metadata, no data, or only encrypted columns during an export. Valid keywords of this parameter are, ALL, DATA_ONLY, METADATA_ONLY, ENCRYPTED_COLUMNS_ONLY, or NONE.
With ENCRYPTION_ALGORITHM parameter you can specify an algorithm how encryption should be done. Valid keywords for this parameter is AES128, AES192, and AES256. AES128 is the default.
With ENCRYPTION_MODE you can specify the type of security to use for performing encryption and decryption during an export. Valid keywords for this parameter is DUAL, PASSWORD, and TRANSPARENT. TRANSPARENT is the default.
3)The parameter TRANSPORTABLE helps to perform table mode export and import using the transportable method. It is similar to the TRANSPORT_TABLESPACES parameter but in this case table's data is imported rather than tablespaces.
4)The parameter PARTITION_OPTIONS specify how partitioned tables should be handled during import operations. While data pump import operation with PARTITION_OPTIONS parameter you can specify value DEPARTITION, MERGE and NONE. The default value is NONE.
5)Prior to 11g, if any dumpfile exists with the same name as to be exported on the directory then error would return and export terminated. As of 11g with REUSE_DUMPFILES parameter existing dumpfile can be overwritten during an export operation. When this parameter is set to "Y", any existing dumpfiles will be overwritten. When the default values of "N" is used, an error is issued if the dump file already exists.
6)Prior to 11g, if the table to be imported is existed in the schema with the same name, then one way is to import the table is to use REMAP_SCHEMA and then import the table in another schema. Another way is rename original table into another name and then import the table. With of 11g you can now rename table while importing. With the parameter REMAP_TABLE you can do this.
7)With the REMAP_DATA parameter you can specify a conversion function which will be automatically applied on a table's column during export and import. This technique can be used to mask sensitive data by replacing original data with random alternatives.
To syntax to use it is,
REMAP_DATA=tablename.column_name:your_desired_function
8)During import operations using the external table method, setting the DATA_OPTIONS parameter to SKIP_CONSTRAINT_ERRORS allows load operations to continue through non-deferred constraint violations, with any violations logged for future reference. Without using of it the whole operation will be rolled back.
Also setting the DATA_OPTIONS parameter to XML_CLOBS specifies that all XMLTYPE columns should be exported as uncompressed CLOBs.
9)One time automatic restart of worker process that have stopped due to certain errors.
Prior to 11g only METADATA_ONLY and NONE could be specified. With now COMPRESSION=ALL, dumpfile size can be greatly reduced so no need to use any compression utility to compress the dumpfile further.
2)The parameter ENCRYPTION, ENCRYPTION_ALGORITHM, ENCRYPTION_MODE are introduced.
With ENCRYPTION parameter you can choose to encrypt both data and metadata, only data, only metadata, no data, or only encrypted columns during an export. Valid keywords of this parameter are, ALL, DATA_ONLY, METADATA_ONLY, ENCRYPTED_COLUMNS_ONLY, or NONE.
With ENCRYPTION_ALGORITHM parameter you can specify an algorithm how encryption should be done. Valid keywords for this parameter is AES128, AES192, and AES256. AES128 is the default.
With ENCRYPTION_MODE you can specify the type of security to use for performing encryption and decryption during an export. Valid keywords for this parameter is DUAL, PASSWORD, and TRANSPARENT. TRANSPARENT is the default.
3)The parameter TRANSPORTABLE helps to perform table mode export and import using the transportable method. It is similar to the TRANSPORT_TABLESPACES parameter but in this case table's data is imported rather than tablespaces.
4)The parameter PARTITION_OPTIONS specify how partitioned tables should be handled during import operations. While data pump import operation with PARTITION_OPTIONS parameter you can specify value DEPARTITION, MERGE and NONE. The default value is NONE.
5)Prior to 11g, if any dumpfile exists with the same name as to be exported on the directory then error would return and export terminated. As of 11g with REUSE_DUMPFILES parameter existing dumpfile can be overwritten during an export operation. When this parameter is set to "Y", any existing dumpfiles will be overwritten. When the default values of "N" is used, an error is issued if the dump file already exists.
6)Prior to 11g, if the table to be imported is existed in the schema with the same name, then one way is to import the table is to use REMAP_SCHEMA and then import the table in another schema. Another way is rename original table into another name and then import the table. With of 11g you can now rename table while importing. With the parameter REMAP_TABLE you can do this.
7)With the REMAP_DATA parameter you can specify a conversion function which will be automatically applied on a table's column during export and import. This technique can be used to mask sensitive data by replacing original data with random alternatives.
To syntax to use it is,
REMAP_DATA=tablename.column_name:your_desired_function
8)During import operations using the external table method, setting the DATA_OPTIONS parameter to SKIP_CONSTRAINT_ERRORS allows load operations to continue through non-deferred constraint violations, with any violations logged for future reference. Without using of it the whole operation will be rolled back.
Also setting the DATA_OPTIONS parameter to XML_CLOBS specifies that all XMLTYPE columns should be exported as uncompressed CLOBs.
9)One time automatic restart of worker process that have stopped due to certain errors.
How to do data pump import from higher to lower version database
You sometimes might face difficulties while export data from higher database version by data pump export and them import the dumpfile into lower version database. It is more appropriate to say it - to export data from a database that has higher compatibility and then import the dumpfile into the database that has lower compatibility settings.
The general guidelines are set in the post http://arjudba.blogspot.com/2009/02/important-guideline-about-data-pump.html.
From the post,
When you decide to use data pump operation from a higher compatibility level database to a lower compatibility level database(i.e expdp from a higher compatibility level database and then impdp to a lower compatibility level database) then always while performig data pump exoprt operation always add VERSION parameter and the value of the VERSION parameter need to match the compatiblity level of the database to which you will perform data pump import.
Below is an example which will take a dump of 11.1.0.6 database using 11g data pump export client and then import that dump into 10.2.0.1 database using 10g data pump client.
In the example I have taken a data pump dumpfile of table test from schema arju of 11g database and then import it to 10g database under the same schema.
Step 01: Connect to 11.1.0.6 database compatibility and create the directory.
C:\>sqlplus arju/a@san
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Feb 6 22:58:09 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create directory e as 'E:\';
Directory created.
This directory e is a name of location E:\ drive on my windows computer. I wanted to put my data pump dumpfile into E:\ drive.
Step 02: Take a dump of 11g database using 11.1.0.6 export data pump client and while export also include version parameter so that dumpfile can be imported into 10g database.
SQL> host expdp arju/a tables=test dumpfile=test_table.dmp logfile=test_table.log directory=e VERSION=10.2
Export: Release 11.1.0.6.0 - Production on Friday, 06 February, 2009 23:00:33
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ARJU"."SYS_EXPORT_TABLE_01": arju/******** tables=test dumpfile=test_table.dmp logfile=test_table.log directory=e VERSI
ON=10.2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ARJU"."TEST" 5.007 KB 1 rows
Master table "ARJU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ARJU.SYS_EXPORT_TABLE_01 is:
E:\TEST_TABLE.DMP
Job "ARJU"."SYS_EXPORT_TABLE_01" successfully completed at 23:01:33
Step 03: Transfer the dumpfile into the D:\ drive of the machine where 10.2g database resides. On windows you can just share your E:\ drive of your 11g machine and from another machine you can just copy the dumpfile. There are many other ways to transfer file using network. If two computers does not have network connection then using pen drive you can transfer the file to the 10.2g machine.
Step 04: Connect to 10.2g database and create a directory d.
C:\>sqlplus arju/a@orcl
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Feb 6 23:05:54 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create directory d as 'D:';
Directory created.
Step 05: Using 10.2g import data pump client just import the dumpfile into 10.2g database.
SQL> host J:\oracle\product\10.2.0\db_1\BIN\impdp arju/a@orcl tables=test dumpfile=test_table.dmp logfile=test_table.log directory=d
Import: Release 10.2.0.1.0 - Production on Friday, 06 February, 2009 23:09:57
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "ARJU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ARJU"."SYS_IMPORT_TABLE_01": arju/********@orcl tables=test dumpfile=test_table.dmp logfile=test_table.log directory=d
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ARJU"."TEST" 5.007 KB 1 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ARJU"."SYS_IMPORT_TABLE_01" successfully completed at 23:10:16
Related Documents
http://arjudba.blogspot.com/2009/02/important-guideline-about-data-pump.html
http://arjudba.blogspot.com/2008/04/data-pump-exportimport-how-we-can-do-it.html
http://arjudba.blogspot.com/2009/02/ude-00018-data-pump-client-is.html
http://arjudba.blogspot.com/2008/04/exportimport-datapump-parameter-query.html
http://arjudba.blogspot.com/2008/04/improve-data-pump-performance.html
http://arjudba.blogspot.com/2009/02/how-to-take-data-pump-export-on-another.html
http://arjudba.blogspot.com/2009/02/compatibility-table-of-oracle-data-pump.html
http://arjudba.blogspot.com/2008/04/datapump-parameters-include-and-exclude.html
The general guidelines are set in the post http://arjudba.blogspot.com/2009/02/important-guideline-about-data-pump.html.
From the post,
When you decide to use data pump operation from a higher compatibility level database to a lower compatibility level database(i.e expdp from a higher compatibility level database and then impdp to a lower compatibility level database) then always while performig data pump exoprt operation always add VERSION parameter and the value of the VERSION parameter need to match the compatiblity level of the database to which you will perform data pump import.
Below is an example which will take a dump of 11.1.0.6 database using 11g data pump export client and then import that dump into 10.2.0.1 database using 10g data pump client.
In the example I have taken a data pump dumpfile of table test from schema arju of 11g database and then import it to 10g database under the same schema.
Step 01: Connect to 11.1.0.6 database compatibility and create the directory.
C:\>sqlplus arju/a@san
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Feb 6 22:58:09 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create directory e as 'E:\';
Directory created.
This directory e is a name of location E:\ drive on my windows computer. I wanted to put my data pump dumpfile into E:\ drive.
Step 02: Take a dump of 11g database using 11.1.0.6 export data pump client and while export also include version parameter so that dumpfile can be imported into 10g database.
SQL> host expdp arju/a tables=test dumpfile=test_table.dmp logfile=test_table.log directory=e VERSION=10.2
Export: Release 11.1.0.6.0 - Production on Friday, 06 February, 2009 23:00:33
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ARJU"."SYS_EXPORT_TABLE_01": arju/******** tables=test dumpfile=test_table.dmp logfile=test_table.log directory=e VERSI
ON=10.2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ARJU"."TEST" 5.007 KB 1 rows
Master table "ARJU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ARJU.SYS_EXPORT_TABLE_01 is:
E:\TEST_TABLE.DMP
Job "ARJU"."SYS_EXPORT_TABLE_01" successfully completed at 23:01:33
Step 03: Transfer the dumpfile into the D:\ drive of the machine where 10.2g database resides. On windows you can just share your E:\ drive of your 11g machine and from another machine you can just copy the dumpfile. There are many other ways to transfer file using network. If two computers does not have network connection then using pen drive you can transfer the file to the 10.2g machine.
Step 04: Connect to 10.2g database and create a directory d.
C:\>sqlplus arju/a@orcl
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Feb 6 23:05:54 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create directory d as 'D:';
Directory created.
Step 05: Using 10.2g import data pump client just import the dumpfile into 10.2g database.
SQL> host J:\oracle\product\10.2.0\db_1\BIN\impdp arju/a@orcl tables=test dumpfile=test_table.dmp logfile=test_table.log directory=d
Import: Release 10.2.0.1.0 - Production on Friday, 06 February, 2009 23:09:57
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "ARJU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ARJU"."SYS_IMPORT_TABLE_01": arju/********@orcl tables=test dumpfile=test_table.dmp logfile=test_table.log directory=d
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ARJU"."TEST" 5.007 KB 1 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ARJU"."SYS_IMPORT_TABLE_01" successfully completed at 23:10:16
Related Documents
http://arjudba.blogspot.com/2009/02/important-guideline-about-data-pump.html
http://arjudba.blogspot.com/2008/04/data-pump-exportimport-how-we-can-do-it.html
http://arjudba.blogspot.com/2009/02/ude-00018-data-pump-client-is.html
http://arjudba.blogspot.com/2008/04/exportimport-datapump-parameter-query.html
http://arjudba.blogspot.com/2008/04/improve-data-pump-performance.html
http://arjudba.blogspot.com/2009/02/how-to-take-data-pump-export-on-another.html
http://arjudba.blogspot.com/2009/02/compatibility-table-of-oracle-data-pump.html
http://arjudba.blogspot.com/2008/04/datapump-parameters-include-and-exclude.html
Thursday, February 5, 2009
Important guideline about data pump compatibility.
Before doing any data pump operation clearly know about database version, database compatibility level, data pump dumpfile set, and data pump client version. These four topics are discussed in
http://arjudba.blogspot.com/2009/02/how-to-determine-software-version-of.html
http://arjudba.blogspot.com/2009/02/how-to-determine-database-compatibility.html
Below is the data pump compatibility guidelines:
1)The compatibility level of data pump dumpfile is set by the compatibility level of the table from which you are taking dump. That means a database version 11.1.0.6 compatibility level is set to 10.2 and you if are taking dump of it then the compatibility level of the data pump dumpfile set will be set to 1.1 (If database compatibility is 10.2 then datapump dumpfile set compatibility became 1.1).
You should remember,
If database compatibility is 11.1.* then datapump dumpfile set compatibility is 2.1
If database compatibility is 10.2.* then datapump dumpfile set compatibility is 1.1
If database compatibility is 10.1.* then datapump dumpfile set compatibility is 0.1
2)When to use export datapump client (expdp), always try to use the same export datapump client version as of the database version you want to export. That is if you want to use data pump export of database version 11.1.0.6 then try to use expdp client of version 11.1.0.6. Note that upto one major version lower expdp client is supported but it is not recommended. So to export 11.1.0.6 database you can use 10g data pump client version(expdp) but it is not recommended.
3)When to use import datapump client (impdp), always try to use the same import datapump client version as of the database version t which you want to import. That is if you want to use data pump import into database version 11.1.0.6 then try to use impdp client of version 11.1.0.6. Note that upto one major version lower impdp client is supported but it is not recommended. So to import into 11.1.0.6 database you can use 10g data pump client version(impdp) but it is not recommended.
4)You can't use upper version of data pump client(expdp or impdp) in order to export data from or import data into lower database version. For example to export data from database version 10.2.0.2 you can't use 11.1.0.6 data pump client or to import data into database version 10.1.0.2 you can't use 10.2.0.1 data pump client.
5)When you decide to use data pump operation from a higher compatibility level database to a lower compatibility level database(i.e expdp from a higher compatibility level database and then impdp to a lower compatibility level database) then always while performig data pump exoprt operation always add VERSION parameter and the value of the VERSION parameter need to match the compatiblity level of the database to which you will perform data pump import. For example if you decide to use data pump export from database version 11.1.0.6 and then import into database 10.2.0.1 then while data pump export operation, with expdp add VERSION=10.2
6)You can't import a data pump dump file set if it's compatibility is higher than the database to which you are trying to import. For example, you took a data pump export of a database which has compatibility 11.1.0.6. (of course without version parameter). Then the dump file set compatibility is set to 2.1 and you can never import this data pump dump file set into database which has compatibility level 10.2 or 10.1.
7)Import data pump can always read export datapump dumpfile sets created by older versions of database. So a datapump dumpfile set with version 1.1 which was exported from database with compatibility 10.2 can easily be imported in 11.1.0.6 database version.
8)Data pump network mode through NETWORK_LINK is supported even if the other database have the different version than the one database. But note that up to one major version is supported. And through network mode export the data pump dumpfile sets compatibility is selected automatically to the database whichever is the lower compatibility between the two.
9)Data pump export client(impdp) can't read file created by the original export client (exp).
10)Data exported by data pump client(expdp) can't be readable by original import client(imp).
Related Documents
http://arjudba.blogspot.com/2009/02/how-to-do-data-pump-import-from-higher.html
http://arjudba.blogspot.com/2008/04/data-pump-exportimport-how-we-can-do-it.html
http://arjudba.blogspot.com/2009/02/ude-00018-data-pump-client-is.html
http://arjudba.blogspot.com/2008/04/exportimport-datapump-parameter-query.html
http://arjudba.blogspot.com/2009/02/how-to-determine-software-version-of.html
http://arjudba.blogspot.com/2009/02/how-to-determine-database-compatibility.html
Below is the data pump compatibility guidelines:
1)The compatibility level of data pump dumpfile is set by the compatibility level of the table from which you are taking dump. That means a database version 11.1.0.6 compatibility level is set to 10.2 and you if are taking dump of it then the compatibility level of the data pump dumpfile set will be set to 1.1 (If database compatibility is 10.2 then datapump dumpfile set compatibility became 1.1).
You should remember,
If database compatibility is 11.1.* then datapump dumpfile set compatibility is 2.1
If database compatibility is 10.2.* then datapump dumpfile set compatibility is 1.1
If database compatibility is 10.1.* then datapump dumpfile set compatibility is 0.1
2)When to use export datapump client (expdp), always try to use the same export datapump client version as of the database version you want to export. That is if you want to use data pump export of database version 11.1.0.6 then try to use expdp client of version 11.1.0.6. Note that upto one major version lower expdp client is supported but it is not recommended. So to export 11.1.0.6 database you can use 10g data pump client version(expdp) but it is not recommended.
3)When to use import datapump client (impdp), always try to use the same import datapump client version as of the database version t which you want to import. That is if you want to use data pump import into database version 11.1.0.6 then try to use impdp client of version 11.1.0.6. Note that upto one major version lower impdp client is supported but it is not recommended. So to import into 11.1.0.6 database you can use 10g data pump client version(impdp) but it is not recommended.
4)You can't use upper version of data pump client(expdp or impdp) in order to export data from or import data into lower database version. For example to export data from database version 10.2.0.2 you can't use 11.1.0.6 data pump client or to import data into database version 10.1.0.2 you can't use 10.2.0.1 data pump client.
5)When you decide to use data pump operation from a higher compatibility level database to a lower compatibility level database(i.e expdp from a higher compatibility level database and then impdp to a lower compatibility level database) then always while performig data pump exoprt operation always add VERSION parameter and the value of the VERSION parameter need to match the compatiblity level of the database to which you will perform data pump import. For example if you decide to use data pump export from database version 11.1.0.6 and then import into database 10.2.0.1 then while data pump export operation, with expdp add VERSION=10.2
6)You can't import a data pump dump file set if it's compatibility is higher than the database to which you are trying to import. For example, you took a data pump export of a database which has compatibility 11.1.0.6. (of course without version parameter). Then the dump file set compatibility is set to 2.1 and you can never import this data pump dump file set into database which has compatibility level 10.2 or 10.1.
7)Import data pump can always read export datapump dumpfile sets created by older versions of database. So a datapump dumpfile set with version 1.1 which was exported from database with compatibility 10.2 can easily be imported in 11.1.0.6 database version.
8)Data pump network mode through NETWORK_LINK is supported even if the other database have the different version than the one database. But note that up to one major version is supported. And through network mode export the data pump dumpfile sets compatibility is selected automatically to the database whichever is the lower compatibility between the two.
9)Data pump export client(impdp) can't read file created by the original export client (exp).
10)Data exported by data pump client(expdp) can't be readable by original import client(imp).
Related Documents
http://arjudba.blogspot.com/2009/02/how-to-do-data-pump-import-from-higher.html
http://arjudba.blogspot.com/2008/04/data-pump-exportimport-how-we-can-do-it.html
http://arjudba.blogspot.com/2009/02/ude-00018-data-pump-client-is.html
http://arjudba.blogspot.com/2008/04/exportimport-datapump-parameter-query.html
Wednesday, February 4, 2009
ORA-12518: TNS:listener could not hand off client connection
Error Description
With sqlplus whenever you connect to a database san it fails with error ORA-12518.
C:\>sqlplus arju/a@san
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Feb 4 20:59:26 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ERROR:
ORA-12518: TNS:listener could not hand off client connection
Cause of the Problem
ORA-12518 indicates a problem while listener handing off the client connection to the server process or dispatcher process. This problem can happen in many scenarios. Like if there is limited resource on the server computer so that it is unable to establish a new connection. Also if there is processes limit in the database initialization parameter this problem may happen. On windows if the service is unavailable then ORA-12518 problem might happen.
Solution of the Problem
Step 01: Check whether your listener version is compatible with database.
If listener version is not compatible with the database version then ORA-12518 likely to occur. The general rule is to use always higher version of listener. If you have both 9i and 10g database then always use 10g listener. If you use 9i listener then connection to 10g database may fail with 9i listener.
Step 02: Gather information from listener log file.
Make sure which version of listener you are using and check your listener.ora file as well as always check listener log file after ORA-12518 occur. By lsrnctl status command you can see log file. Check contents of the log file. Check listener parameter file and see listener version.
C:\>lsnrctl status
Below is the from listener log file,
From the log file we see No such file or directory. This windows Error appears on windows machine whenever database service is not available.
The solution is to make sure that you can connect to database as sysdba and your database service is open. You can start your database service from command propmt by,
sc start {oracle_service_name}
Alternatively, right click on my computer, then click manage> click services> find the oracle service and start then.
Step 03Check the listener service handle in block state: Issue lsnrctl services and see status information under the database service name. Check the state and see if the state is in blocked.
If state is in blocked state then connection is not possible. The state of a handler can be in blocked state if,
1)Database in startup or shutdown process.
or,
2)The initialization parameter value of PROCESSES has reached it's limit.
In this case if you are in dedicated server mode you need to increase static processes parameter.
If you are in shared server mode and your connection is refused based on limits then increase the dispatcher parameter.
Step 04 Check if local connection as sysdba is successful: Check whether you can connect to database as sys user as sysdba. If local connection is ok then check database open_state. Also check v$instance view fields like status, active_state, blocked fields.
Step 05 Check the Processes is reached it's limit:
If local connection sys as sysdba is successful then query from v$resource_limit and see the processes parameter.
If max_utilization reached the limit value then consider to increase the static processes parameter.
The summary of ORA-12518 error is it may caused by many reasons and always check you listener.log file and alert log file to solve it.
With sqlplus whenever you connect to a database san it fails with error ORA-12518.
C:\>sqlplus arju/a@san
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Feb 4 20:59:26 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ERROR:
ORA-12518: TNS:listener could not hand off client connection
Cause of the Problem
ORA-12518 indicates a problem while listener handing off the client connection to the server process or dispatcher process. This problem can happen in many scenarios. Like if there is limited resource on the server computer so that it is unable to establish a new connection. Also if there is processes limit in the database initialization parameter this problem may happen. On windows if the service is unavailable then ORA-12518 problem might happen.
Solution of the Problem
Step 01: Check whether your listener version is compatible with database.
If listener version is not compatible with the database version then ORA-12518 likely to occur. The general rule is to use always higher version of listener. If you have both 9i and 10g database then always use 10g listener. If you use 9i listener then connection to 10g database may fail with 9i listener.
Step 02: Gather information from listener log file.
Make sure which version of listener you are using and check your listener.ora file as well as always check listener log file after ORA-12518 occur. By lsrnctl status command you can see log file. Check contents of the log file. Check listener parameter file and see listener version.
C:\>lsnrctl status
LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 04-FEB-2009 20:49:32 Copyright (c) 1991, 2007, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Production Start Date 04-FEB-2009 20:47:22 Uptime 0 days 0 hr. 2 min. 12 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File F:\app\Administrator\product\11.1.0\db_1\network\admin\listener.ora Listener Log File f:\app\administrator\diag\tnslsnr\5a0c04b78203433\listener\alert\log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.4.12.233)(PORT=1521))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "orcl" has 2 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... Instance "orcl", status READY, has 1 handler(s) for this service... Service "orclXDB" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "orcl_XPT" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "san" has 1 instance(s). Instance "san", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
Below is the from listener log file,
TNS-12518: TNS:listener could not hand off client connection TNS-12560: TNS:protocol adapter error TNS-00530: Protocol adapter error 32-bit Windows Error: 2: No such file or directory
From the log file we see No such file or directory. This windows Error appears on windows machine whenever database service is not available.
The solution is to make sure that you can connect to database as sysdba and your database service is open. You can start your database service from command propmt by,
sc start {oracle_service_name}
Alternatively, right click on my computer, then click manage> click services> find the oracle service and start then.
Step 03Check the listener service handle in block state: Issue lsnrctl services and see status information under the database service name. Check the state and see if the state is in blocked.
$lsnrctl status . . Service "san" has 2 instance(s). Instance "san", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:10 refused:4276 LOCAL SERVER Instance "san", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:138 refused:0 state:ready LOCAL SERVER . .
If state is in blocked state then connection is not possible. The state of a handler can be in blocked state if,
1)Database in startup or shutdown process.
or,
2)The initialization parameter value of PROCESSES has reached it's limit.
In this case if you are in dedicated server mode you need to increase static processes parameter.
If you are in shared server mode and your connection is refused based on limits then increase the dispatcher parameter.
Step 04 Check if local connection as sysdba is successful: Check whether you can connect to database as sys user as sysdba. If local connection is ok then check database open_state. Also check v$instance view fields like status, active_state, blocked fields.
Step 05 Check the Processes is reached it's limit:
If local connection sys as sysdba is successful then query from v$resource_limit and see the processes parameter.
SQL> select * from v$resource_limit where resource_name='processes'; RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU ------------------------------ ------------------- --------------- ---------- ---------- processes 120 150 150 150
If max_utilization reached the limit value then consider to increase the static processes parameter.
The summary of ORA-12518 error is it may caused by many reasons and always check you listener.log file and alert log file to solve it.
Tuesday, February 3, 2009
How to determine the software version of the database and the Data Pump client
In order to know your oracle database version issue,
Alternatively, you can query from product_component_version.
In order to see your Export data pump client version issue from command prompt,
C:\>expdp help=y
Export: Release 10.2.0.1.0 - Production on Wednesday, 04 February, 2009 1:33:23
Copyright (c) 2003, 2005, Oracle. All rights reserved.
In order to see your Import data pump client version issue from command prompt,
C:\>impdp help=y
Import: Release 10.2.0.1.0 - Production on Wednesday, 04 February, 2009 1:32:44
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Both in case of Data Pump Export and Import first few digits show it version.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
Alternatively, you can query from product_component_version.
SQL> col product for a40
SQL> col version for a11
SQL> col status for a15
SQL> select * from product_component_version;
PRODUCT VERSION STATUS
---------------------------------------- ----------- ---------------
NLSRTL 11.1.0.6.0 Production
Oracle Database 11g Enterprise Edition 11.1.0.6.0 Production
PL/SQL 11.1.0.6.0 Production
TNS for 32-bit Windows: 11.1.0.6.0 Production
In order to see your Export data pump client version issue from command prompt,
C:\>expdp help=y
Export: Release 10.2.0.1.0 - Production on Wednesday, 04 February, 2009 1:33:23
Copyright (c) 2003, 2005, Oracle. All rights reserved.
In order to see your Import data pump client version issue from command prompt,
C:\>impdp help=y
Import: Release 10.2.0.1.0 - Production on Wednesday, 04 February, 2009 1:32:44
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Both in case of Data Pump Export and Import first few digits show it version.
How to determine database compatibility level
COMPATIBILITY allows you to use a new release of Oracle, while at the same time guaranteeing backward compatibility with an earlier release.
In order to determine your current database compatibility level you can query from view database_compatible_level.
SQL> col value for a11
SQL> col description for a50
SQL> select * from database_compatible_level;
VALUE DESCRIPTION
----------- --------------------------------------------------
11.1.0.0.0 Database will be completely compatible with this s
oftware version
Alternatively you can check your initialization parameter COMPATIBLE setting.
On Sql*plus simply issue,
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.1.0.0.0
You can also query from v$parameter,
SQL> select value from v$parameter where name='compatible';
VALUE
-----------
11.1.0.0.0
SQL> select value from v$spparameter where name='compatible';
VALUE
-----------
11.1.0.0.0
In order to determine your current database compatibility level you can query from view database_compatible_level.
SQL> col value for a11
SQL> col description for a50
SQL> select * from database_compatible_level;
VALUE DESCRIPTION
----------- --------------------------------------------------
11.1.0.0.0 Database will be completely compatible with this s
oftware version
Alternatively you can check your initialization parameter COMPATIBLE setting.
On Sql*plus simply issue,
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.1.0.0.0
You can also query from v$parameter,
SQL> select value from v$parameter where name='compatible';
VALUE
-----------
11.1.0.0.0
SQL> select value from v$spparameter where name='compatible';
VALUE
-----------
11.1.0.0.0
ORA-39001, ORA-39023: Version 9.1 is not supported.
Problem Description
While data pump export operation, you made an attempt to specify the dumpfile set version by the value of the VERSION parameter and if this value is too high or too low than the compatibility level of the database then data pump export fails with ORA-39001, ORA-39023: Version %s is not supported.
For example on a database with compatibility level 10.2 if version is set to 9.1 while exporting then error will return,
C:\>J:\oracle\product\10.2.0\db_1\BIN\expdp.exe arju/a@orcl directory=d dumpfile=tabs.dmp tables=test VERSION=9.1
Export: Release 10.2.0.1.0 - Production on Wednesday, 04 February, 2009 0:47:55
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39023: Version 9.1 is not supported.
If database compatibility is set to 11.1.0.6 and if VERSION is set higher than it then error returns.
C:\>F:\app\Administrator\product\11.1.0\db_1\BIN\expdp.exe arju/a@san tables=test directory=d dumpfile=test.dmp VERSION=11.1.0.7
Export: Release 11.1.0.6.0 - Production on Wednesday, 04 February, 2009 0:59:46
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39023: Version 11.1.0.7 is not supported.
Solution of the Problem
Give the VERSION parameter appropriately. Don't give it too high and also don't give it less then 9.2 because up to compatibility 9.2 data pump import operation can be done.
I have noticed that on 10.2.0.1 database compatibility if version is specified as 11.1 then error does not returned.
Below is an example.
C:\>J:\oracle\product\10.2.0\db_1\BIN\expdp.exe arju/a@orcl directory=d dumpfile=tabs.dmp tables=test VERSION=11.1.0
Export: Release 10.2.0.1.0 - Production on Wednesday, 04 February, 2009 0:54:52
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "ARJU"."SYS_EXPORT_TABLE_01": arju/********@orcl directory=d dumpfile=tabs.dmp tables=test VERSION=11.1.0
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ARJU"."TEST" 4.976 KB 10 rows
Master table "ARJU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ARJU.SYS_EXPORT_TABLE_01 is:
D:\TABS.DMP
Job "ARJU"."SYS_EXPORT_TABLE_01" successfully completed at 00:55:32
However if you like to import dumpfile set to a higher version then no need to set VERSION parameter because import Data Pump (impdp) can always read Export Data Pump (expdp) dumpfile sets created by older versions of the database. For example, export dumpfile sets of database 10.1.0.5 can easily be imported into database version 11.1.0.6.
In case of import dumpfile set to a lower version always set VERSION parameter.
While data pump export operation, you made an attempt to specify the dumpfile set version by the value of the VERSION parameter and if this value is too high or too low than the compatibility level of the database then data pump export fails with ORA-39001, ORA-39023: Version %s is not supported.
For example on a database with compatibility level 10.2 if version is set to 9.1 while exporting then error will return,
C:\>J:\oracle\product\10.2.0\db_1\BIN\expdp.exe arju/a@orcl directory=d dumpfile=tabs.dmp tables=test VERSION=9.1
Export: Release 10.2.0.1.0 - Production on Wednesday, 04 February, 2009 0:47:55
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39023: Version 9.1 is not supported.
If database compatibility is set to 11.1.0.6 and if VERSION is set higher than it then error returns.
C:\>F:\app\Administrator\product\11.1.0\db_1\BIN\expdp.exe arju/a@san tables=test directory=d dumpfile=test.dmp VERSION=11.1.0.7
Export: Release 11.1.0.6.0 - Production on Wednesday, 04 February, 2009 0:59:46
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39023: Version 11.1.0.7 is not supported.
Solution of the Problem
Give the VERSION parameter appropriately. Don't give it too high and also don't give it less then 9.2 because up to compatibility 9.2 data pump import operation can be done.
I have noticed that on 10.2.0.1 database compatibility if version is specified as 11.1 then error does not returned.
Below is an example.
C:\>J:\oracle\product\10.2.0\db_1\BIN\expdp.exe arju/a@orcl directory=d dumpfile=tabs.dmp tables=test VERSION=11.1.0
Export: Release 10.2.0.1.0 - Production on Wednesday, 04 February, 2009 0:54:52
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "ARJU"."SYS_EXPORT_TABLE_01": arju/********@orcl directory=d dumpfile=tabs.dmp tables=test VERSION=11.1.0
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ARJU"."TEST" 4.976 KB 10 rows
Master table "ARJU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ARJU.SYS_EXPORT_TABLE_01 is:
D:\TABS.DMP
Job "ARJU"."SYS_EXPORT_TABLE_01" successfully completed at 00:55:32
However if you like to import dumpfile set to a higher version then no need to set VERSION parameter because import Data Pump (impdp) can always read Export Data Pump (expdp) dumpfile sets created by older versions of the database. For example, export dumpfile sets of database 10.1.0.5 can easily be imported into database version 11.1.0.6.
In case of import dumpfile set to a lower version always set VERSION parameter.
ORA-39000, ORA-39142: incompatible version number 2.1 in dump file
Problem Description
You have created a dumpfile set of a database 11g(COMPATIBLE parameter was set to 11.1.0.0). Now whenever you try to import this dumpfile set to a database 10g ORA-39001, ORA-39000, ORA-39142 returned as below.
C:\>J:\oracle\product\10.2.0\db_1\BIN\impdp.exe arju/a@orcl directory=d dumpfile=test.dmp tables=test
Import: Release 10.2.0.1.0 - Production on Tuesday, 03 February, 2009 23:02:04
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 2.1 in dump file "d:\test.dmp"
A variant of this error is,
C:\>impdp arju/a DIRECTORY=d DUMPFILE=test.dmp TABLES=test
Import: Release 10.1.0.2.0 - Production on Tuesday, 03 February, 2009 22:02:04
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31619: invalid dump file "d:\test.dmp"
Cause of the Problem
You made an attempt to import of an Export Data Pump dumpfile into a lower version target database whereas, the export dumpfile was created from a higher version source database which had a higher compatibility level, plus the Export Data Pump job was done without the VERSION parameter. For example, dumpfile set was created from database 11.1.0.6(with compatibility 11.1.0.0) without specifying any VERSION parameter and then you tried to import this dumpfile set into a lower version database like 10.2.0.1.
Solution of the Problem
Before going into solution you may wish to see an example posted in UDE-00018: Data Pump client is incompatible with database version.
As you already created dumpfile set without VERSION parameter so now there is no way to import this dumpfile set into a lower version than the source database compatibility level.
If you wish to import the dumpfile set into a lower version database than the source database compatibility level then the solution is to re-create the dumpfile set with the VERSION parameter VERSION=10.2. Just append the "VERSION=10.2" words with your expdp command if you try to import the dumpfile set into a 10.2.0.1 database compatibility.
You export command should look like,
expdp arju/a@orcl directory=d dumpfile=test.dmp tables=test VERSION=10.2
If the source database does not exist then workaround you import the data into a temporary database (11.1.0.6.0 or higher), and re-create the dumpfile set with the correct VERSION parameter (i.e. VERSION=10.2.0), finally import the dumpfile set into a 10g database.
You have created a dumpfile set of a database 11g(COMPATIBLE parameter was set to 11.1.0.0). Now whenever you try to import this dumpfile set to a database 10g ORA-39001, ORA-39000, ORA-39142 returned as below.
C:\>J:\oracle\product\10.2.0\db_1\BIN\impdp.exe arju/a@orcl directory=d dumpfile=test.dmp tables=test
Import: Release 10.2.0.1.0 - Production on Tuesday, 03 February, 2009 23:02:04
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 2.1 in dump file "d:\test.dmp"
A variant of this error is,
C:\>impdp arju/a DIRECTORY=d DUMPFILE=test.dmp TABLES=test
Import: Release 10.1.0.2.0 - Production on Tuesday, 03 February, 2009 22:02:04
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31619: invalid dump file "d:\test.dmp"
Cause of the Problem
You made an attempt to import of an Export Data Pump dumpfile into a lower version target database whereas, the export dumpfile was created from a higher version source database which had a higher compatibility level, plus the Export Data Pump job was done without the VERSION parameter. For example, dumpfile set was created from database 11.1.0.6(with compatibility 11.1.0.0) without specifying any VERSION parameter and then you tried to import this dumpfile set into a lower version database like 10.2.0.1.
Solution of the Problem
Before going into solution you may wish to see an example posted in UDE-00018: Data Pump client is incompatible with database version.
As you already created dumpfile set without VERSION parameter so now there is no way to import this dumpfile set into a lower version than the source database compatibility level.
If you wish to import the dumpfile set into a lower version database than the source database compatibility level then the solution is to re-create the dumpfile set with the VERSION parameter VERSION=10.2. Just append the "VERSION=10.2" words with your expdp command if you try to import the dumpfile set into a 10.2.0.1 database compatibility.
You export command should look like,
expdp arju/a@orcl directory=d dumpfile=test.dmp tables=test VERSION=10.2
If the source database does not exist then workaround you import the data into a temporary database (11.1.0.6.0 or higher), and re-create the dumpfile set with the correct VERSION parameter (i.e. VERSION=10.2.0), finally import the dumpfile set into a 10g database.
UDE-00018: Data Pump client is incompatible with database version
Problem Description
Whenever you made an attempt to connect with a higher version Export Data Pump client(expdp) to a lower version database then UDE-00018 error occurs.
Here expdp version 11.1.0.6.0 and you tried to export database 10.2.0.1.0.
C:\>F:\app\Administrator\product\11.1.0\db_1\BIN\expdp.exe arju/a@orcl schemas=arju directory=d dumpfile=test.dmp
Export: Release 11.1.0.6.0 - Production on Tuesday, 03 February, 2009 23:09:00
Copyright (c) 2003, 2007, Oracle. All rights reserved.
UDE-00018: Data Pump client is incompatible with database version 10.2.0.1.0
Similarly, if you made an attempt to connect with a higher version Import Data Pump client to a lower version database then UDI-00018 error returns.
Here impdp version 11.1.0.6.0 and you tried to import data into database 10.2.0.1.0.
C:\>F:\app\Administrator\product\11.1.0\db_1\BIN\impdp.exe arju/a@orcl tables=test directory=d dumpfile=test.dmp
Import: Release 11.1.0.6.0 - Production on Tuesday, 03 February, 2009 23:03:11
Copyright (c) 2003, 2007, Oracle. All rights reserved.
UDI-00018: Data Pump client is incompatible with database version 10.2.0.1.0
Cause of the Problem
With the help of higher version of data pump client either impdp or expdp connect to a lower version database is not supported. If you try to do so either UDI-00018(for import) or UDE-00018 (for export) will occur.
An example for clarification
Example on this post is based on experimental issue and on windows environment. Here
TNSNAMES entry san connect to a database 11g,
TNSNAMES entry orcl connects to a database 10g,
Both 10g and 11g database contains a user named arju with password a.
In 10g database the parameter compatible is set to 10.2.0.1.0
In 11g database the parameter compatible is set to 11.1.0.0.0
Both databases are on the same computer.
Step 01: Connect to 11g database and create directory named d on to windows D: drive.
C:\>sqlplus arju/a@san
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Feb 3 22:37:20 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create directory d as 'd:';
Directory created.
Step 02: Connect to 10g database and create directory named d on to windows D: drive.
C:\>sqlplus arju/a@orcl
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Feb 3 22:37:46 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create directory d as 'd:';
Directory created.
Step 03: On 11g database create a table test and insert one row into it.
C:\>sqlplus arju/a@san
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Feb 3 22:38:17 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table test(a number);
Table created.
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
Step 04: Using 11g expdp take a dump of 11g's table test.
C:\>F:\app\Administrator\product\11.1.0\db_1\BIN\expdp.exe arju/a@san tables=test directory=d dumpfile=test.dmp
Export: Release 11.1.0.6.0 - Production on Tuesday, 03 February, 2009 22:39:43
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ARJU"."SYS_EXPORT_TABLE_01": arju/********@san tables=test directory=d dumpfile=test.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ARJU"."TEST" 5.007 KB 1 rows
Master table "ARJU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ARJU.SYS_EXPORT_TABLE_01 is:
D:\TEST.DMP
Job "ARJU"."SYS_EXPORT_TABLE_01" successfully completed at 22:40:20
Step 05: i)Using 10g impdp try to import this dump on to 10g database.
C:\>J:\oracle\product\10.2.0\db_1\BIN\impdp.exe arju/a@orcl directory=d dumpfile=test.dmp tables=test
Import: Release 10.2.0.1.0 - Production on Tuesday, 03 February, 2009 23:02:04
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 2.1 in dump file "d:\test.dmp"
ii)Using 11g impdp try to import this dump on to 10g database.
C:\>F:\app\Administrator\product\11.1.0\db_1\BIN\impdp.exe arju/a@orcl tables=test directory=d dumpfile=test.dmp
Import: Release 11.1.0.6.0 - Production on Tuesday, 03 February, 2009 23:03:11
Copyright (c) 2003, 2007, Oracle. All rights reserved.
UDI-00018: Data Pump client is incompatible with database version 10.2.0.1.0
Here is one more test whenever you try to use 11g expdp to export data from 10g,
C:\>F:\app\Administrator\product\11.1.0\db_1\BIN\expdp.exe arju/a@orcl schemas=arju directory=d dumpfile=test.dmp
Export: Release 11.1.0.6.0 - Production on Tuesday, 03 February, 2009 23:09:00
Copyright (c) 2003, 2007, Oracle. All rights reserved.
UDE-00018: Data Pump client is incompatible with database version 10.2.0.1.0
Solutions and things good to remember
1)Whenever you use expdp to export data always use the same version Export Data Pump client as the version of the database from which you are taking dump. Though, you can use up to one major version lower expdp client, but this is not recommended.
If your database version is 11.1.0.6.0 then you can use expdp tool of any of 11g or 10g but it is recommended to use 11.1.0.6.0 expdp tool.
2)Whenever you use impdp to import data always use the same version Import Data Pump client as the version of the database to which you are importing dump. Though, you can use up to one major version lower impdp client, but this is not recommended.
If your database version is 11.1.0.6.0 then you can use impdp tool of any of 11g or 10g but it is recommended to use 11.1.0.6.0 expdp tool.
3)Import Data Pump (impdp) can always read Export Data Pump (expdp) dumpfile sets created by older versions of the database. For example export dumpfile sets of database 10.1.0.5 can easily be imported into database version 11.1.0.6.
4)If you decide dumpfiles need to be imported into a lower version database, then while exporting data use the Export Data Pump parameter VERSION to match the compatibility level of the database to which you will import. For example to import dumpfile sets to a 10.2.0.1 database(where compatibility is set to 10.2.0.1) from a database 11.1.0.6(where compatibility is set to 11.1.0.0), while using data pump export client along with the command use VERSION parameter where value of VERSION need to be 10.2 i.e as a whole append "VERSION=10.2" words with your expdp command.
Related Documents
http://arjudba.blogspot.com/2009/02/important-guideline-about-data-pump.html
http://arjudba.blogspot.com/2009/02/how-to-do-data-pump-import-from-higher.html
http://arjudba.blogspot.com/2008/04/data-pump-exportimport-how-we-can-do-it.html
http://arjudba.blogspot.com/2008/04/exportimport-datapump-parameter-query.html
http://arjudba.blogspot.com/2008/04/improve-data-pump-performance.html
http://arjudba.blogspot.com/2009/02/how-to-take-data-pump-export-on-another.html
http://arjudba.blogspot.com/2009/02/compatibility-table-of-oracle-data-pump.html
http://arjudba.blogspot.com/2008/04/datapump-parameters-include-and-exclude.html
Whenever you made an attempt to connect with a higher version Export Data Pump client(expdp) to a lower version database then UDE-00018 error occurs.
Here expdp version 11.1.0.6.0 and you tried to export database 10.2.0.1.0.
C:\>F:\app\Administrator\product\11.1.0\db_1\BIN\expdp.exe arju/a@orcl schemas=arju directory=d dumpfile=test.dmp
Export: Release 11.1.0.6.0 - Production on Tuesday, 03 February, 2009 23:09:00
Copyright (c) 2003, 2007, Oracle. All rights reserved.
UDE-00018: Data Pump client is incompatible with database version 10.2.0.1.0
Similarly, if you made an attempt to connect with a higher version Import Data Pump client to a lower version database then UDI-00018 error returns.
Here impdp version 11.1.0.6.0 and you tried to import data into database 10.2.0.1.0.
C:\>F:\app\Administrator\product\11.1.0\db_1\BIN\impdp.exe arju/a@orcl tables=test directory=d dumpfile=test.dmp
Import: Release 11.1.0.6.0 - Production on Tuesday, 03 February, 2009 23:03:11
Copyright (c) 2003, 2007, Oracle. All rights reserved.
UDI-00018: Data Pump client is incompatible with database version 10.2.0.1.0
Cause of the Problem
With the help of higher version of data pump client either impdp or expdp connect to a lower version database is not supported. If you try to do so either UDI-00018(for import) or UDE-00018 (for export) will occur.
An example for clarification
Example on this post is based on experimental issue and on windows environment. Here
TNSNAMES entry san connect to a database 11g,
TNSNAMES entry orcl connects to a database 10g,
Both 10g and 11g database contains a user named arju with password a.
In 10g database the parameter compatible is set to 10.2.0.1.0
In 11g database the parameter compatible is set to 11.1.0.0.0
Both databases are on the same computer.
Step 01: Connect to 11g database and create directory named d on to windows D: drive.
C:\>sqlplus arju/a@san
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Feb 3 22:37:20 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create directory d as 'd:';
Directory created.
Step 02: Connect to 10g database and create directory named d on to windows D: drive.
C:\>sqlplus arju/a@orcl
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Feb 3 22:37:46 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create directory d as 'd:';
Directory created.
Step 03: On 11g database create a table test and insert one row into it.
C:\>sqlplus arju/a@san
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Feb 3 22:38:17 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table test(a number);
Table created.
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
Step 04: Using 11g expdp take a dump of 11g's table test.
C:\>F:\app\Administrator\product\11.1.0\db_1\BIN\expdp.exe arju/a@san tables=test directory=d dumpfile=test.dmp
Export: Release 11.1.0.6.0 - Production on Tuesday, 03 February, 2009 22:39:43
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ARJU"."SYS_EXPORT_TABLE_01": arju/********@san tables=test directory=d dumpfile=test.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ARJU"."TEST" 5.007 KB 1 rows
Master table "ARJU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ARJU.SYS_EXPORT_TABLE_01 is:
D:\TEST.DMP
Job "ARJU"."SYS_EXPORT_TABLE_01" successfully completed at 22:40:20
Step 05: i)Using 10g impdp try to import this dump on to 10g database.
C:\>J:\oracle\product\10.2.0\db_1\BIN\impdp.exe arju/a@orcl directory=d dumpfile=test.dmp tables=test
Import: Release 10.2.0.1.0 - Production on Tuesday, 03 February, 2009 23:02:04
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 2.1 in dump file "d:\test.dmp"
ii)Using 11g impdp try to import this dump on to 10g database.
C:\>F:\app\Administrator\product\11.1.0\db_1\BIN\impdp.exe arju/a@orcl tables=test directory=d dumpfile=test.dmp
Import: Release 11.1.0.6.0 - Production on Tuesday, 03 February, 2009 23:03:11
Copyright (c) 2003, 2007, Oracle. All rights reserved.
UDI-00018: Data Pump client is incompatible with database version 10.2.0.1.0
Here is one more test whenever you try to use 11g expdp to export data from 10g,
C:\>F:\app\Administrator\product\11.1.0\db_1\BIN\expdp.exe arju/a@orcl schemas=arju directory=d dumpfile=test.dmp
Export: Release 11.1.0.6.0 - Production on Tuesday, 03 February, 2009 23:09:00
Copyright (c) 2003, 2007, Oracle. All rights reserved.
UDE-00018: Data Pump client is incompatible with database version 10.2.0.1.0
Solutions and things good to remember
1)Whenever you use expdp to export data always use the same version Export Data Pump client as the version of the database from which you are taking dump. Though, you can use up to one major version lower expdp client, but this is not recommended.
If your database version is 11.1.0.6.0 then you can use expdp tool of any of 11g or 10g but it is recommended to use 11.1.0.6.0 expdp tool.
2)Whenever you use impdp to import data always use the same version Import Data Pump client as the version of the database to which you are importing dump. Though, you can use up to one major version lower impdp client, but this is not recommended.
If your database version is 11.1.0.6.0 then you can use impdp tool of any of 11g or 10g but it is recommended to use 11.1.0.6.0 expdp tool.
3)Import Data Pump (impdp) can always read Export Data Pump (expdp) dumpfile sets created by older versions of the database. For example export dumpfile sets of database 10.1.0.5 can easily be imported into database version 11.1.0.6.
4)If you decide dumpfiles need to be imported into a lower version database, then while exporting data use the Export Data Pump parameter VERSION to match the compatibility level of the database to which you will import. For example to import dumpfile sets to a 10.2.0.1 database(where compatibility is set to 10.2.0.1) from a database 11.1.0.6(where compatibility is set to 11.1.0.0), while using data pump export client along with the command use VERSION parameter where value of VERSION need to be 10.2 i.e as a whole append "VERSION=10.2" words with your expdp command.
Related Documents
http://arjudba.blogspot.com/2009/02/important-guideline-about-data-pump.html
http://arjudba.blogspot.com/2009/02/how-to-do-data-pump-import-from-higher.html
http://arjudba.blogspot.com/2008/04/data-pump-exportimport-how-we-can-do-it.html
http://arjudba.blogspot.com/2008/04/exportimport-datapump-parameter-query.html
http://arjudba.blogspot.com/2008/04/improve-data-pump-performance.html
http://arjudba.blogspot.com/2009/02/how-to-take-data-pump-export-on-another.html
http://arjudba.blogspot.com/2009/02/compatibility-table-of-oracle-data-pump.html
http://arjudba.blogspot.com/2008/04/datapump-parameters-include-and-exclude.html
Monday, February 2, 2009
How to add google search box to your site
First of All Google itself offer a free custom search engine. You can follow link www.google.com/searchcode.html and then create a custom search engine. You can add selected sites to search or manually add some keywords to confine searching within your defined keywords.
Alternatively, you can add following codes to add a custom search engine. Just copy and paste following codes in your hrml/javascript.
Below is the output of this html.
Alternatively, you can add following codes to add a custom search engine. Just copy and paste following codes in your hrml/javascript.
<form method="get" action="http://www.google.com/search">
<input type="text" name="q" size="25" maxlength="255" value="" />
<input type="submit" value="Arju's Search Test" /> <br />
<input type="checkbox" name="sitesearch"
value="http://arjudba.blogspot.com" checked /> Arju's Site<br />
</form>
Below is the output of this html.
Import error ORA-39083, ORA-02298 parent keys not found
Error Description
From live server I have taken a dump and then in another database I have imported it. All tables, indexes, functions, constraints, procedures went fine. But while importing REF_CONSTRAINT two constraints failed to create.
From the import log,
Cause of the Problem
ORA-02298 throws because it was trying to enable a foreign key constraint on a table with data populated into it but it does not find any references parent key. As to enable a constraint it must satisfy the constraint condition and it could not enable foreign key constraint, so above error arises.
There may be several scenarios behind ORA-02298 while you doing import operation.
1)The data being imported is from a database where there is more than one user. The export is being done in user/schema mode, so only that user's data is being exported. Any tables that the user does not own, but may have a referential integrity link to, are not exported.
2)The export is taken while database was in read write mode and export was taken consistent. That is if you take export with exp command then you did not use CONSISTENT=Y option. And if you take data pump export with expdp command then you did not use FLASHBACK_SCN or FLASHBACK_TIME parameter to make the dump consistent. As a result at first parent table is exported and then some data is inserted into parent as well as child table. After parent table is exported child table is exported and child table contains those rows which references are not exist inside the parent table existed in the dump file. So, in case of import while enabling constraint it could not enable because referenced parent key is not there.
Solution of the Problem
If you face problem for cause 1) then find the parent table and before exporting child tables first import the parent tables dump and then import user dumps. Although if you take full export it will solve the problem.
If you face problem for cause 2) then while taking export by using exp utility use CONSISTENT=Y option.
And if you take data pump export by using expdp utility then use FLASHBACK_SCN or FLASHBACK_TIME parameter. You can query from database for the values of these parameters and use those value. You can follow http://arjudba.blogspot.com/2008/05/export-data-to-previous-time-or-point.html in order to have an idea about usage of these parameters. To know more about FLASHBACK_SCN or FLASHBACK_TIME parameters search within this blog.
Related Documents
http://arjudba.blogspot.com/2008/05/how-to-get-scn-from-database-timestamp.html
http://arjudba.blogspot.com/2008/05/export-data-to-previous-time-or-point.html
From live server I have taken a dump and then in another database I have imported it. All tables, indexes, functions, constraints, procedures went fine. But while importing REF_CONSTRAINT two constraints failed to create.
From the import log,
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02298: cannot validate (MAXIM.HIS_DET_HISID_FK) - parent keys not found
Failing sql is:
ALTER TABLE "MAXIM"."HISTORY_DETAIL" ADD CONSTRAINT "HIS_DET_HISID_FK" FOREIGN KEY
("HISTORY_ID") REFERENCES "MAXIM"."HISTORY" ("ID") ENABLE
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02298: cannot validate (MAXIM.FK_REGIS_PHON_SUBSCRIBERS) - parent keys not found
Failing sql is:
ALTER TABLE "MAXIM"."PHONES" ADD CONSTRAINT "FK_REGIS_PHON_SUBSCRIBERS"
FOREIGN KEY ("SB_ID") REFERENCES "MAXIM"."SUBSCRIBERS" ("ID") ENABLE
Cause of the Problem
ORA-02298 throws because it was trying to enable a foreign key constraint on a table with data populated into it but it does not find any references parent key. As to enable a constraint it must satisfy the constraint condition and it could not enable foreign key constraint, so above error arises.
There may be several scenarios behind ORA-02298 while you doing import operation.
1)The data being imported is from a database where there is more than one user. The export is being done in user/schema mode, so only that user's data is being exported. Any tables that the user does not own, but may have a referential integrity link to, are not exported.
2)The export is taken while database was in read write mode and export was taken consistent. That is if you take export with exp command then you did not use CONSISTENT=Y option. And if you take data pump export with expdp command then you did not use FLASHBACK_SCN or FLASHBACK_TIME parameter to make the dump consistent. As a result at first parent table is exported and then some data is inserted into parent as well as child table. After parent table is exported child table is exported and child table contains those rows which references are not exist inside the parent table existed in the dump file. So, in case of import while enabling constraint it could not enable because referenced parent key is not there.
Solution of the Problem
If you face problem for cause 1) then find the parent table and before exporting child tables first import the parent tables dump and then import user dumps. Although if you take full export it will solve the problem.
If you face problem for cause 2) then while taking export by using exp utility use CONSISTENT=Y option.
And if you take data pump export by using expdp utility then use FLASHBACK_SCN or FLASHBACK_TIME parameter. You can query from database for the values of these parameters and use those value. You can follow http://arjudba.blogspot.com/2008/05/export-data-to-previous-time-or-point.html in order to have an idea about usage of these parameters. To know more about FLASHBACK_SCN or FLASHBACK_TIME parameters search within this blog.
Related Documents
http://arjudba.blogspot.com/2008/05/how-to-get-scn-from-database-timestamp.html
http://arjudba.blogspot.com/2008/05/export-data-to-previous-time-or-point.html
The listener supports no services
Problem Description
Whenever I issue lsnrctl status command it says "The listener supports no services" and no one from outside could not connect to database through listener.
Below is my listener.ora file entry,
Cause of the Problem
A listener is configured with one or more listening protocol addresses, information about supported services, and parameters that control its run time behavior. Upon database startup, oracle process PMON register with listener service. If after database startup if a listener is created then database has no idea about the listener because inside listener.ora there is no entry of SID_LIST_{listener_name} where {listener_name} need to be replaced by your listener name.
Suppose if you have a listener named LISTENER then you should have a corresponding SID_LIST_LISTENER entry while will define $ORACLE_HOME and ORACLE_SID of the listener.
Problem Solution
I edited the listener.ora file as below.
Then bounce the listener by,
$lsnrctl stop
$lsnrctl start
And now listener supports service and from outside world you can connect to database.
Whenever I issue lsnrctl status command it says "The listener supports no services" and no one from outside could not connect to database through listener.
$lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 02-FEB-2009 16:22:01
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 02-FEB-2009 05:07:47
Uptime 0 days 11 hr. 14 min. 14 sec
Trace Level support
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /var/opt/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /var/opt/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Listener Trace File /var/opt/oracle/oracle/product/10.2.0/db_1/network/trace/listener.trc
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
The listener supports no services
The command completed successfully
Below is my listener.ora file entry,
$ cat /var/opt/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /usr/oracle/product/11.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST =127.0.0.1)(PORT = 1521))
)
)
Cause of the Problem
A listener is configured with one or more listening protocol addresses, information about supported services, and parameters that control its run time behavior. Upon database startup, oracle process PMON register with listener service. If after database startup if a listener is created then database has no idea about the listener because inside listener.ora there is no entry of SID_LIST_{listener_name} where {listener_name} need to be replaced by your listener name.
Suppose if you have a listener named LISTENER then you should have a corresponding SID_LIST_LISTENER entry while will define $ORACLE_HOME and ORACLE_SID of the listener.
Problem Solution
I edited the listener.ora file as below.
$ cat /var/opt/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /usr/oracle/product/11.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /var/opt/oracle/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC=
(GLOBAL_DBNAME=thdb)
(ORACLE_HOME=/var/opt/oracle/oracle/product/10.2.0/db_1)
(SID_NAME=thdb)
)
)
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST =THAI_DB)(PORT = 1521))
)
)
Then bounce the listener by,
$lsnrctl stop
$lsnrctl start
And now listener supports service and from outside world you can connect to database.
$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 02-FEB-2009 16:49:06
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 02-FEB-2009 16:43:16
Uptime 0 days 0 hr. 5 min. 49 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /var/opt/oracle/oracle/product/10.2.0/db_1/network/adm in/listener.ora
Listener Log File /var/opt/oracle/oracle/product/10.2.0/db_1/network/log /listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=THAI_HP)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "thdb" has 1 instance(s).
Instance "thdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Related Documents
lsnrctl start fails with TNS-12541, TNS-12560,TNS-00511, Linux Error: 111:
Problem Description
Today while preparing a new production server we faced an interesting problem. After installing oracle database software and database no default listener is created, no sqlnet.ora file. However we manually create the listener.ora and sqlnet.ora as below.
Now if I start the listener it fails with TNS-12541, TNS-12560,TNS-00511, Linux Error: 111:Connection refused.
I tried many alternatives like changing host to IP address, localhost, hostname. But all fails. In the listener.ora file I set
TRACE_LEVEL_LISTENER = SUPPORT and I found the entry below.
Solution of the Problem
I guess the culprit is entry of /etc/hosts file. As if I set parameter host=localhost inside listener.ora it returns message localhost is not found.
Below was my /etc/hosts entry.
$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain THAI_HP
I changed this entry to below.
$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 THAI_HP localhost.localdomain localhost
And then everything goes ok.
Today while preparing a new production server we faced an interesting problem. After installing oracle database software and database no default listener is created, no sqlnet.ora file. However we manually create the listener.ora and sqlnet.ora as below.
Now if I start the listener it fails with TNS-12541, TNS-12560,TNS-00511, Linux Error: 111:Connection refused.
$lsnrctl start
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 02-FEB-2009 15:51:20
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
TNS-12541: TNS:no listener
TNS-12560: TNS: Protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=23.54.56.12)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS: Protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
I tried many alternatives like changing host to IP address, localhost, hostname. But all fails. In the listener.ora file I set
TRACE_LEVEL_LISTENER = SUPPORT and I found the entry below.
..
..
..
02-FEB-2009 05:07:47:534] nsnainit: NS Connection version: 313
[02-FEB-2009 05:07:47:534] nsnainit: inf->nsinfflg[0]: 0xd inf->nsinfflg[1]: 0xd
[02-FEB-2009 05:07:47:534] nsnainit: "or" info flags: 0xd Translations follow:
native service(s) is (are) wanted
NA disabled remotely for this connection
NA services unavailable on both processes - negotiation not needed
[02-FEB-2009 05:07:47:534] nsnainit: "or" info flags: 0xd Translations follow:
native service(s) is (are) wanted
NA disabled remotely for this connection
NA services unavailable on both processes - negotiation not needed
"and" info flags: 0xd Translations follow:
native service(s) is (are) wanted
NA disabled remotely for this connection
NA services unavailable on both processes - negotiation not needed
..
..
..
Solution of the Problem
I guess the culprit is entry of /etc/hosts file. As if I set parameter host=localhost inside listener.ora it returns message localhost is not found.
Below was my /etc/hosts entry.
$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain THAI_HP
I changed this entry to below.
$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 THAI_HP localhost.localdomain localhost
And then everything goes ok.
Sunday, February 1, 2009
Failed to shutdown DBConsole Gracefully
Problem Symptoms
While stopping dbconsole on our production server 10.2.0.1 by emctl it failed to stop with error message "Failed to shutdown DBConsole Gracefully" as below.
$ emctl stop dbconsole
TZ set to Asia/Bangkok
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.localdomain:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 10g Database Control ...
--- Failed to shutdown DBConsole Gracefully ---
failed.
Cause of the Problem
This problem may happen by two reasons as far as I faced.
Cause 01: The server has two active NIC cards and therefore two active IP addresses. DNS resolves to the IP address of eth1 and /etc/hosts lists the IP address of eth0.
In order to see the available NIC cards issue,
$ /sbin/ifconfig -a
Cause 02: Wrong entry inside /etc/hosts.
Inside the /etc/hosts the entry format is,
If I have the entry like,
for a machine named hostname database then error will possibly occur.
Solution of the Problem
If the error happened by Cause 01 then,
1)Be sure that nslookup returns the IP address of the primary network adaptor, normally eth0.
2)Set your ORACLE_SID. By export or setenv command you can set environmental variable based on your shell type.
3)Recrate your repository. You can simply do this by two consecutive comamnds.
$emca -deconfig dbcontrol db -repos drop
$emca -config dbcontrol db -repos create
If the error happen by Cause 02 then,
1)Be sure you have correct entry inside /etc/hosts in the following format.
For localhost, the entry should following for host database.
2)Set your ORACLE_SID. By export or setenv command you can set environmental variable based on your shell type.
Like,
export ORACLE_SID=test
where test is your database sid.
3)Recrate your repository. You can simply do this by two consecutive comamnds.
$emca -deconfig dbcontrol db -repos drop
$emca -config dbcontrol db -repos create
While stopping dbconsole on our production server 10.2.0.1 by emctl it failed to stop with error message "Failed to shutdown DBConsole Gracefully" as below.
$ emctl stop dbconsole
TZ set to Asia/Bangkok
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.localdomain:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 10g Database Control ...
--- Failed to shutdown DBConsole Gracefully ---
failed.
Cause of the Problem
This problem may happen by two reasons as far as I faced.
Cause 01: The server has two active NIC cards and therefore two active IP addresses. DNS resolves to the IP address of eth1 and /etc/hosts lists the IP address of eth0.
In order to see the available NIC cards issue,
$ /sbin/ifconfig -a
Cause 02: Wrong entry inside /etc/hosts.
Inside the /etc/hosts the entry format is,
IP Address fully qualified hostname simple or short hostname Alias
If I have the entry like,
127.0.0.1 localhost.localdomain database
for a machine named hostname database then error will possibly occur.
Solution of the Problem
If the error happened by Cause 01 then,
1)Be sure that nslookup returns the IP address of the primary network adaptor, normally eth0.
2)Set your ORACLE_SID. By export or setenv command you can set environmental variable based on your shell type.
3)Recrate your repository. You can simply do this by two consecutive comamnds.
$emca -deconfig dbcontrol db -repos drop
$emca -config dbcontrol db -repos create
If the error happen by Cause 02 then,
1)Be sure you have correct entry inside /etc/hosts in the following format.
For localhost, the entry should following for host database.
127.0.0.1 database localhost.localdomain localhost
2)Set your ORACLE_SID. By export or setenv command you can set environmental variable based on your shell type.
Like,
export ORACLE_SID=test
where test is your database sid.
3)Recrate your repository. You can simply do this by two consecutive comamnds.
$emca -deconfig dbcontrol db -repos drop
$emca -config dbcontrol db -repos create
Subscribe to:
Posts (Atom)
Tag Cloud
10.2g
10g
11g
11gR2
Abasa
About Oracle
Administration
Adsense
Alerts
Archival
ASM
ASP.Net
Audit
Audit Vault
Backup
Bangladesh
Block Corruption
Blogger
Browser
Bug
Business
Clone
Clusterware
Comments
Concepts
Connection
Controlfiles
Crime
CSS
Data Block
Data Dictionary
Data Guard
Data Mining
Data Pump
Data Type
Database Administration
Database Vault
DBConsole
Developer
Economics
EM
Excel
Exercise
Explain plan
Export
External Table
Facebook
Firefox
Firmware
Flashback
Forum
Functions
Games
Globalization Support
Grid Control
Hardware
History
HTML
IE
Import
Indexes
initializaion parameter
initialization parameter
Installation
Internals
Internet
Interview
isql*plus
Java
JavaScript
Job
Joins
Joke
Limitation
Linux
Listener
Logminer
Magento
Mail
Materialized View
Medical
Memory
Mobile
Money
Multimedia
MySQL
Net Services
Network
OCP
Operators
Oracle
Oracle Concepts
Oracle Recovery
OS
Others
OUI
Package
Packages
Parameters
Partitioning
Patchset
Performance
Perl
Pfile
Photos
PHP
PL/SQL
Profile
Pseudocolumns
Puzzle
Quiz
Quota
RAC
RAC Installation
Recovery
Recovery Problems
Redo Log
Reports
RMAN
Scripts
Security
SEO
Server Administration
SGA
Shell Script
Smarty
Social Marketing
Solaris
Spfile
SQL
SQL Tuning
SQL*Loader
Sql*Plus
Startup Problem
Streams
SwingBench
System Analysis
Tablespaces
Technology
Temp
TNS Error
Tools
Troubleshooting
Tuning
Undo
UNIX
Upgradation
Utilities
Version
Views
Vmware
Windows
Wordpress
XML