There are many third party tools by which you can copy files between windows and unix machine. Some common tools are winscp, SSHSecureShellClient etc. Instead of using any third party tool, you can use original unix and windows built-in tool to do the task.
The RCP (Remote CoPy) is a standard UNIX tool and a Microsoft Windows 2000 tool that copies files/folder to and from computer running the RCP service.
Before you can use the RCP tool from a Windows-based computer you must do something in your UNIX computer.
you must turn on the RCP service and update the security files to allow the Windows-based computer to connect to your unix machine over this service.
The following steps will help you.
Step 01: Turn on RCP service on UNIX Host
Turn on the RCP service on unix machine by using inetd:
1. Log on as root.
2. Edit the file /etc/Inetd.conf.
3. Uncomment the lines that start with shell and that start with exec.
4. Save the file.
5. Use ps -ef |grep inetd and determine inetd service pid. Send the Host User Profile (HUP) signal to inetd by using kill or by using pkill.
6. Run inetd again.
To turn on the RCP service by using xinetd:
1. Log on as root.
2. Edit the file /etc/Xinetd.d/rsh. You may get another file based on your linux distrinution.
3. Change the line disable to no.
4. Save the file.
5. Use ps -ef |grep inetd and determine inetd service pid. Send the HUP signal to xinetd by using kill or by using pkill.
6. Run xinetd again.
Step 02: Set the security permissions
After turning on RCP services you must set the security permissions to allow the Windows host to connect to your computer.
1. Edit the file /etc/hosts.equiv. Based on your linux distribution this file may vary. You can get like /etc/hosts.allow.
2. In the file add a line that contains the name of your Windows host.
3. Add a second line that contains the name of your Windows host and the name of a user who can access the directory that you want to transfer. Separate the two elements with a tab character.
4. Save the file.
Step 03: Transfer the files or directory
From windows computer you can transfer files or folders using rcp using following syntax.
RCP [-a | -b] [-h] [-r] [host][.user:]source [host][.user:] path\destination
For example, to copy the file index.html from the path /home/oracle on the computer unixhost as the user oracle you must use from windows computer,
rcp unixhost.oracle:/home/oracle/index.html index.html
In order to copy whole directory you need to use -r(for recursive) with rcp command.
To copy test directory under /home/oracle on unix, from windows computer issue,
rcp -r unixhost.oracle:/home/oracle/test test
Tuesday, March 31, 2009
ORA-00600 arguments: [keltnfy-ldmInit], [46], [1]
Problem Description
In oracle 10.2.0.1 while creating database with dbca it fails with message below.
ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], []
If you try to create your database manually then also the command startup nomount fails with above error message.
Cause of the Problem
In the error ldmInit indicates that the problem is related while getting host information of oracle during startup. The first argument 46 indicates the exception LDMERR_HOST_NOT_FOUND which is "gethostbyname system call fails". Oracle was unable to get host information from OS and bug fires.
Solution of the Problem
Step 01: Check permission on /etc/hosts
Note that you need read permission of all users.
Step 02: Check the contents of /etc/hosts
Open the contents of /etc/hosts and check the contents inside it.
$ less /etc/hosts
Note that the contents of this files follow following format.
A simple example,
Step 03: Check the hostname and make sure you can ping your hostname.
$ hostname
database
$ ping database
PING database (127.0.0.1) 56(84) bytes of data.
64 bytes from database (127.0.0.1): icmp_seq=0 ttl=64 time=0.057 ms
64 bytes from database (127.0.0.1): icmp_seq=1 ttl=64 time=0.050 ms
64 bytes from database (127.0.0.1): icmp_seq=2 ttl=64 time=0.041 ms
If you get the following message,
$ ping database
ping: unknown host database
then possibly you will hit above bug.
And you need to modify /etc/hosts files. In the alias section you can give the name of your machine name. If your machine name is "database" you can give /etc/hosts entry as,
And then ping database again. Make sure you are able to ping your host.
Step 04: Diagnosis DNS problem if you have DNS setup
If you have DNS setup, ping is not a tool to diagnose DNS problem. A better tool to use is nslookup, dnsquery, or dig.
$nslookup www.google.com
The forward and reverse lookup should succeed.
Step 05: Check nsswitch.conf
Make sure host lookup is also done through the /etc/hosts file and not just dns. The keyword files should come before dns.
Step 06: Check resolv.conf
Make sure nameserver with DNS name is added there.
In oracle 10.2.0.1 while creating database with dbca it fails with message below.
ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], []
If you try to create your database manually then also the command startup nomount fails with above error message.
Cause of the Problem
In the error ldmInit indicates that the problem is related while getting host information of oracle during startup. The first argument 46 indicates the exception LDMERR_HOST_NOT_FOUND which is "gethostbyname system call fails". Oracle was unable to get host information from OS and bug fires.
Solution of the Problem
Step 01: Check permission on /etc/hosts
$ ls -l /etc/hosts
-rw-r--r-- 1 root root 153 Nov 24 2007 /etc/hosts
Note that you need read permission of all users.
Step 02: Check the contents of /etc/hosts
Open the contents of /etc/hosts and check the contents inside it.
$ less /etc/hosts
Note that the contents of this files follow following format.
IP Address fully qualified hostname simple or short hostname Alias
A simple example,
$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 database localhost.localdomain localhost
Step 03: Check the hostname and make sure you can ping your hostname.
$ hostname
database
$ ping database
PING database (127.0.0.1) 56(84) bytes of data.
64 bytes from database (127.0.0.1): icmp_seq=0 ttl=64 time=0.057 ms
64 bytes from database (127.0.0.1): icmp_seq=1 ttl=64 time=0.050 ms
64 bytes from database (127.0.0.1): icmp_seq=2 ttl=64 time=0.041 ms
If you get the following message,
$ ping database
ping: unknown host database
then possibly you will hit above bug.
And you need to modify /etc/hosts files. In the alias section you can give the name of your machine name. If your machine name is "database" you can give /etc/hosts entry as,
127.0.0.1 database localhost.localdomain localhost
And then ping database again. Make sure you are able to ping your host.
Step 04: Diagnosis DNS problem if you have DNS setup
If you have DNS setup, ping is not a tool to diagnose DNS problem. A better tool to use is nslookup, dnsquery, or dig.
$nslookup www.google.com
The forward and reverse lookup should succeed.
Step 05: Check nsswitch.conf
$ cat /etc/nsswitch.conf
hosts: files dns
Make sure host lookup is also done through the /etc/hosts file and not just dns. The keyword files should come before dns.
Step 06: Check resolv.conf
$ cat /etc/resolv.conf
nameserver 4.2.2.2
Make sure nameserver with DNS name is added there.
ORA-31655: no data or metadata objects selected for job
Problem Description
You are going to do data pump export operation in order to export objects based on filtering via EXCLUDE or INCLUDE parameter of expdp.
In this example you wanted tables 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP' and all the tables starting with word CV(like 'CV', 'CV_EXPERIENCE', 'CV_EDUCATION' etc)
Your parameter file is like below.
And you invoke expdp as
expdp parfile=d:\parfile.txt
from command line. But it fails with below message on my windows PC.
As you expected 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP','CV', 'CV_EXPERIENCE', 'CV_EDUCATION' will be exported but not a single table is exported and error ORA-31655: no data or metadata objects selected for job returned.
Cause of the Problem
This problem happens because of multiple INCLUDE options was set in expdp. Note that multiple INCLUDE parameter can be given in expdp but I recommend not to do that because if multiple filters(EXCLUDE/INCLUDE) are specified , an implicit AND operation is applied to them.
In this example data pump interprets
as,
TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')" AND TABLE:"LIKE 'CV%'"
As AND operation is applied, so all tables is filtered out (because no tables is there that starts with CV and between 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP') and no tables are exported.
A bit clear example I will show you.
Your parameter file is like below.
And you invoke datapump
Note that here only one table is exported.
Because with first INCLUDE parameter,
include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')"
first 3 tables are selected
and with second INCLUDE parameter
include =TABLE:"LIKE 'ACC%' "
both output are ANDed and only one table is selected that is ACCOUNT_GROUP (which starts with word ACC.)
You are going to do data pump export operation in order to export objects based on filtering via EXCLUDE or INCLUDE parameter of expdp.
In this example you wanted tables 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP' and all the tables starting with word CV(like 'CV', 'CV_EXPERIENCE', 'CV_EDUCATION' etc)
Your parameter file is like below.
userid=smilebd/a
directory=d
dumpfile=b.dmp
include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')"
include =TABLE:"LIKE 'CV%' "
And you invoke expdp as
expdp parfile=d:\parfile.txt
from command line. But it fails with below message on my windows PC.
C:\>expdp parfile=d:\parfile.txt
Export: Release 10.1.0.4.2 - Production on Thursday, 29 January, 2009 14:53
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting "SMILEBD"."SYS_EXPORT_SCHEMA_01": parfile=d:\parfile.txt
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-31655: no data or metadata objects selected for job
Job "SMILEBD"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at 14:53:50
As you expected 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP','CV', 'CV_EXPERIENCE', 'CV_EDUCATION' will be exported but not a single table is exported and error ORA-31655: no data or metadata objects selected for job returned.
Cause of the Problem
This problem happens because of multiple INCLUDE options was set in expdp. Note that multiple INCLUDE parameter can be given in expdp but I recommend not to do that because if multiple filters(EXCLUDE/INCLUDE) are specified , an implicit AND operation is applied to them.
In this example data pump interprets
include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')"
include =TABLE:"LIKE 'CV%' "
as,
TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')" AND TABLE:"LIKE 'CV%'"
As AND operation is applied, so all tables is filtered out (because no tables is there that starts with CV and between 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP') and no tables are exported.
A bit clear example I will show you.
Your parameter file is like below.
userid=smilebd/a
directory=d
dumpfile=b.dmp
include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')"
include =TABLE:"LIKE 'CV%' "
And you invoke datapump
C:\>expdp parfile=d:\parfile.txt
Export: Release 10.1.0.4.2 - Production on Thursday, 29 January, 2009 14:54
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting "SMILEBD"."SYS_EXPORT_SCHEMA_01": parfile=d:\parfile.txt
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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
. . exported "SMILEBD"."ACCOUNT_GROUP" 11.75 KB 132 rows
Master table "SMILEBD"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SMILEBD.SYS_EXPORT_SCHEMA_01 is:
G:\B.DMP
Job "SMILEBD"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:54:38
Note that here only one table is exported.
Because with first INCLUDE parameter,
include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')"
first 3 tables are selected
and with second INCLUDE parameter
include =TABLE:"LIKE 'ACC%' "
both output are ANDed and only one table is selected that is ACCOUNT_GROUP (which starts with word ACC.)
Listener Hangs, Child listener process remains persistence
Problem Description
Some days ago in our database server we got a problem regarding listener issue. Our TNS Listener hangs. Below is the problem symtompts.
•The lsnrctl status or lsnrctl stop or lsnrctl reload does not respond. Just like it hangs after displays message connecting to ..... .
•No one from outside can connect to database.
•Local connection without listener was ok.
•Listener process takes high cpu than normal usage.
•Listener process forks. The word fork is an UNIX OS related term and it indicates listener process creates a copy of itself. The copied process is called child process and the original process is called a parent process. Due to load of the listener a child listener process is created and it remains persistent. Whenever we give ps -ef then two tnslsnr is shown as below.
$ ps -ef | grep tnslsnr
oracle 3102 1 0 Jan 01 ? 12:28 /var/opt/oracle/bin/tnslsnr LISTENER -inherit
oracle 5012 3102 0 Jan 25 ? 10:15 /var/opt/oracle/bin/tnslsnr LISTENER -inherit
From the output first one is parent listener process and second line is child listener process. For child listener process parent id is 3102.
Just killing the child process allows new connections to work until the problem reoccurs. So after seeing above and if listener hangs then do,
$kill -9 5012 3102
Cause of the Problem
This problem remains in oracle 10.1.0.3, 10.1.0.4, 10.1.0.4.2, 10.1.0.5, 10.2.0.1 and 10.2.0.2. The listener hangs if the child listener process is not closed i.e after creating child process it persists. Note that, child listener processes are not unusual, depending on traffic as well as when the OS grep snapshot is taken. However, a persistent secondary process (longer than 5 seconds) is not normal and may be a result of this referenced problem.
This listener hanging event can happen on a standalone server or on a RAC server.
Solution of the Problem
1)The issue is fixed in patchset 10.2.0.3 and in 10.2.0.4. So apply patchset.
2)Apply Patch 4518443 which is available in metalink. Download from metalink and apply on your databse server.
3)As a workaround, you can follow the following two steps if you don't like to apply patch now.
Step 01: Add the following entry in your listener.ora file.
SUBSCRIBE_FOR_NODE_DOWN_EVENT_{listener_name}=OFF
Where {listener_name} should be replaced with the actual listener name configured in the LISTENER.ORA file.
Suppose your have default listener name and it is LISTENER. Then in the listener.ora file(by default in location $ORACLE_HOME/network/admin on unix) add the following entry in a new line,
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
Step 02: Go to directory cd $ORACLE_HOME/opmn/conf , find ons.config and move it to another location. Like,
cd $ORACLE_HOME/opmn/conf
mv ons.config ons.config.bak
After completing above two steps bounce the listener.
lsnrctl stop
lsnrctl start
Alternatively, you can simply issue,
$lsnrctl reload
if database availability is important.
Note that adding the SUBSCRIBE_FOR_NODE_DOWN_EVENT_{listener_name} to listener.ora file on RAC and disabling the ONS file, will mean that FAN (fast application notification) will not be possible. So, if you have a RAC configuration, then apply the patch and do not disable ONS or FAN.
Some days ago in our database server we got a problem regarding listener issue. Our TNS Listener hangs. Below is the problem symtompts.
•The lsnrctl status or lsnrctl stop or lsnrctl reload does not respond. Just like it hangs after displays message connecting to ..... .
•No one from outside can connect to database.
•Local connection without listener was ok.
•Listener process takes high cpu than normal usage.
•Listener process forks. The word fork is an UNIX OS related term and it indicates listener process creates a copy of itself. The copied process is called child process and the original process is called a parent process. Due to load of the listener a child listener process is created and it remains persistent. Whenever we give ps -ef then two tnslsnr is shown as below.
$ ps -ef | grep tnslsnr
oracle 3102 1 0 Jan 01 ? 12:28 /var/opt/oracle/bin/tnslsnr LISTENER -inherit
oracle 5012 3102 0 Jan 25 ? 10:15 /var/opt/oracle/bin/tnslsnr LISTENER -inherit
From the output first one is parent listener process and second line is child listener process. For child listener process parent id is 3102.
Just killing the child process allows new connections to work until the problem reoccurs. So after seeing above and if listener hangs then do,
$kill -9 5012 3102
Cause of the Problem
This problem remains in oracle 10.1.0.3, 10.1.0.4, 10.1.0.4.2, 10.1.0.5, 10.2.0.1 and 10.2.0.2. The listener hangs if the child listener process is not closed i.e after creating child process it persists. Note that, child listener processes are not unusual, depending on traffic as well as when the OS grep snapshot is taken. However, a persistent secondary process (longer than 5 seconds) is not normal and may be a result of this referenced problem.
This listener hanging event can happen on a standalone server or on a RAC server.
Solution of the Problem
1)The issue is fixed in patchset 10.2.0.3 and in 10.2.0.4. So apply patchset.
2)Apply Patch 4518443 which is available in metalink. Download from metalink and apply on your databse server.
3)As a workaround, you can follow the following two steps if you don't like to apply patch now.
Step 01: Add the following entry in your listener.ora file.
SUBSCRIBE_FOR_NODE_DOWN_EVENT_{listener_name}=OFF
Where {listener_name} should be replaced with the actual listener name configured in the LISTENER.ORA file.
Suppose your have default listener name and it is LISTENER. Then in the listener.ora file(by default in location $ORACLE_HOME/network/admin on unix) add the following entry in a new line,
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
Step 02: Go to directory cd $ORACLE_HOME/opmn/conf , find ons.config and move it to another location. Like,
cd $ORACLE_HOME/opmn/conf
mv ons.config ons.config.bak
After completing above two steps bounce the listener.
lsnrctl stop
lsnrctl start
Alternatively, you can simply issue,
$lsnrctl reload
if database availability is important.
Note that adding the SUBSCRIBE_FOR_NODE_DOWN_EVENT_{listener_name} to listener.ora file on RAC and disabling the ONS file, will mean that FAN (fast application notification) will not be possible. So, if you have a RAC configuration, then apply the patch and do not disable ONS or FAN.
ORA-12557: TNS:protocol adapter not loadable
Problem Description
In my machine I had oracle 10g home , using sqlplus of 10g I could connect to an Oracle database 10g. Now I have installed a new oracle 11g home, but using sqlplus of 11g I could not connect to Oracle database 10g. Below is an example,
With 10.2g sqlplus I can connect to 10g database.
C:\>e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe shaik/a@192.168.100.160/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 26 01:54:10 2009
Copyright (c) 1982, 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
With 11g sqlplus I could not connect to oracle database 10g. It returns error message, ORA-12557: TNS:protocol adapter not loadable.
C:\>d:\app\oracle\BIN\sqlplus.exe shaik/a@192.168.100.160/tiger
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 26 01:55:00 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ERROR:
ORA-12557: TNS:protocol adapter not loadable
Cause of the Problem
The problem happens because of two ORACLE_HOME are installed on your system. As after 10g you hav e installed 11g so whenever you write sqlplus by default new 11g binaries are selected and raises ORA-12557. But working with old home 10g works fine.
Simply sqlplus does not work but 10g home location sqlplus (e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe ) works.
C:\>sqlplus.exe shaik/a@192.168.100.160/tiger
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 26 22:47:08 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ERROR:
ORA-12557: TNS:protocol adapter not loadable
Connecting to old oracle database using new binaries are not supported in oracle and error will return.
Solution of the Problem
Only setting ORACLE_HOME is not sufficient on windows environment. Because the location is taken from windows registry. So either uninstall newer oracle home or explicitly pointing to old oracle binaries will solve the problem.
Here using pointing to old home,
C:\>e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe shaik/a@192.168.100.160/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 26 01:54:10 2009
Copyright (c) 1982, 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
Alternatively you can follow below.
Step 01: Remove registry entries of new ORACLE_HOME.
To do this,
i)Type regedit on Run.
ii)Press enter and expand HKEY_LOCAL_MACHINE.
iii)Then expand SOFTWARE and then expand ORACLE tab. There you will see two oracle home. Right click on the one that you want to delete and then select delete. If prompting click yes.
Step 02: Remove any environmental variable.
i)Right click on My computer icon. Then select properties.
ii)System properties window will appear. Click on Advanced tab.
iii)Select environmental variables.
iv)Find the variable/system variable path and ORACLE_HOME. Edit or modify them so that it point to you desired sql*plus.
Usually in the PATH system variable you will get both ORACLE_HOME path. Just remove one path. Of course if you have ORACLE_HOME variable settings first delete the key.
In my machine I had oracle 10g home , using sqlplus of 10g I could connect to an Oracle database 10g. Now I have installed a new oracle 11g home, but using sqlplus of 11g I could not connect to Oracle database 10g. Below is an example,
With 10.2g sqlplus I can connect to 10g database.
C:\>e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe shaik/a@192.168.100.160/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 26 01:54:10 2009
Copyright (c) 1982, 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
With 11g sqlplus I could not connect to oracle database 10g. It returns error message, ORA-12557: TNS:protocol adapter not loadable.
C:\>d:\app\oracle\BIN\sqlplus.exe shaik/a@192.168.100.160/tiger
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 26 01:55:00 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ERROR:
ORA-12557: TNS:protocol adapter not loadable
Cause of the Problem
The problem happens because of two ORACLE_HOME are installed on your system. As after 10g you hav e installed 11g so whenever you write sqlplus by default new 11g binaries are selected and raises ORA-12557. But working with old home 10g works fine.
Simply sqlplus does not work but 10g home location sqlplus (e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe ) works.
C:\>sqlplus.exe shaik/a@192.168.100.160/tiger
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 26 22:47:08 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ERROR:
ORA-12557: TNS:protocol adapter not loadable
Connecting to old oracle database using new binaries are not supported in oracle and error will return.
Solution of the Problem
Only setting ORACLE_HOME is not sufficient on windows environment. Because the location is taken from windows registry. So either uninstall newer oracle home or explicitly pointing to old oracle binaries will solve the problem.
Here using pointing to old home,
C:\>e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe shaik/a@192.168.100.160/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 26 01:54:10 2009
Copyright (c) 1982, 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
Alternatively you can follow below.
Step 01: Remove registry entries of new ORACLE_HOME.
To do this,
i)Type regedit on Run.
ii)Press enter and expand HKEY_LOCAL_MACHINE.
iii)Then expand SOFTWARE and then expand ORACLE tab. There you will see two oracle home. Right click on the one that you want to delete and then select delete. If prompting click yes.
Step 02: Remove any environmental variable.
i)Right click on My computer icon. Then select properties.
ii)System properties window will appear. Click on Advanced tab.
iii)Select environmental variables.
iv)Find the variable/system variable path and ORACLE_HOME. Edit or modify them so that it point to you desired sql*plus.
Usually in the PATH system variable you will get both ORACLE_HOME path. Just remove one path. Of course if you have ORACLE_HOME variable settings first delete the key.
Changing a DBA user to a normal user in oracle
Many times you have granted a user DBA super role instead of giving individual privilege to a user. Later whenever you want to revoke DBA role you need to care of which privilege you need to give the user.
Before example let's take a overview about some views related to privileges and roles in oracle.
1)DBA_SYS_PRIVS describes system privileges granted to users and roles.
2)USER_SYS_PRIVS describes system privileges granted to the current user.
3)DBA_ROLE_PRIVS describes the roles granted to all users and roles in the database.
4)DBA_TAB_PRIVS describes all object grants in the database. Note that in the table the column TABLE_NAME does not display only table rather it displays any object, including tables, packages, indexes, sequences, and so on.
In this example, we want to change a DBA user named "OMS" to a normal user.
Let's see the user OMS has the available roles granted.
These roles may contain many privilege. For example the role RESOURCE contains following privileges.
Let's see the privilege assigned to user OMS.
Now let's see which tablespaces contain the objects owned by the user OMS. We need to assign quota on those tablespaces and then revoking DBA role.
The tablespaces contain objects of user OMS.
Let's see if any objects privileges granted to user OMS.
SQL> select * from dba_tab_privs where grantee='OMS';
no rows selected
Now we give privilege and assign quota to user OMS and then revoking DBA role.
Assigning privilege by,
GRANT CREATE SESSION, CREATE TRIGGER, CREATE SEQUENCE, CREATE TYPE, CREATE PROCEDURE,
CREATE CLUSTER, CREATE OPERATOR, CREATE INDEXTYPE, CREATE TABLE TO OMS;
Giving quota on the tablespaces by,
ALTER USER OMS QUOTA UNLIMITED on OMS_SPC;
ALTER USER OMS QUOTA UNLIMITED on OMS_INDX_SPC;
Now revoking DBA role by,
REVOKE DBA FROM OMS;
Before example let's take a overview about some views related to privileges and roles in oracle.
1)DBA_SYS_PRIVS describes system privileges granted to users and roles.
SQL> desc dba_sys_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
2)USER_SYS_PRIVS describes system privileges granted to the current user.
SQL> desc user_sys_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
3)DBA_ROLE_PRIVS describes the roles granted to all users and roles in the database.
SQL> desc dba_role_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)
4)DBA_TAB_PRIVS describes all object grants in the database. Note that in the table the column TABLE_NAME does not display only table rather it displays any object, including tables, packages, indexes, sequences, and so on.
SQL> desc dba_tab_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE NOT NULL VARCHAR2(30)
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
GRANTOR NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
GRANTABLE VARCHAR2(3)
HIERARCHY VARCHAR2(3)
In this example, we want to change a DBA user named "OMS" to a normal user.
Let's see the user OMS has the available roles granted.
SQL> select * from dba_role_privs where grantee='OMS';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
OMS RESOURCE NO YES
OMS JAVAUSERPRIV NO YES
OMS DBA NO YES
These roles may contain many privilege. For example the role RESOURCE contains following privileges.
SQL> select * from dba_sys_privs where grantee='RESOURCE';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE TABLE NO
8 rows selected.
Let's see the privilege assigned to user OMS.
SQL> select * from dba_sys_privs where grantee='OMS';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
OMS UNLIMITED TABLESPACE NO
Now let's see which tablespaces contain the objects owned by the user OMS. We need to assign quota on those tablespaces and then revoking DBA role.
The tablespaces contain objects of user OMS.
SQL> DEFINE owner='OMS'
SQL> select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name ||';'
from dba_tables where owner='&OWNER'
UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name
||';' from dba_indexes
where owner='&OWNER'
UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name
||';' from dba_tab_partitions
where table_owner='&OWNER'
UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name
||';' from dba_ind_partitions
where index_owner='&OWNER';
old 1: select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name ||';'
new 1: select distinct 'Alter user OMS quota unlimited on '|| tablespace_name ||';'
old 2: from dba_tables where owner='&OWNER'
new 2: from dba_tables where owner='OMS'
old 3: UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name ||';' from dba_indexes
new 3: UNION select distinct 'Alter user OMS quota unlimited on '|| tablespace_name ||';' from dba_indexes
old 4: where owner='&OWNER'
new 4: where owner='OMS'
old 5: UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name ||';' from dba_tab_partitions
new 5: UNION select distinct 'Alter user OMS quota unlimited on '|| tablespace_name ||';' from dba_tab_partitions
old 6: where table_owner='&OWNER'
new 6: where table_owner='OMS'
old 7: UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name ||';' from dba_ind_partitions
new 7: UNION select distinct 'Alter user OMS quota unlimited on '|| tablespace_name ||';' from dba_ind_partitions
old 8: where index_owner='&OWNER'
new 8: where index_owner='OMS'
'ALTERUSEROMSQUOTAUNLIMITEDON'||TABLESPACE_NAME||';'
-----------------------------------------------------------------
Alter user OMS quota unlimited on OMS_INDX_SPC;
Alter user OMS quota unlimited on OMS_SPC;
Let's see if any objects privileges granted to user OMS.
SQL> select * from dba_tab_privs where grantee='OMS';
no rows selected
Now we give privilege and assign quota to user OMS and then revoking DBA role.
Assigning privilege by,
GRANT CREATE SESSION, CREATE TRIGGER, CREATE SEQUENCE, CREATE TYPE, CREATE PROCEDURE,
CREATE CLUSTER, CREATE OPERATOR, CREATE INDEXTYPE, CREATE TABLE TO OMS;
Giving quota on the tablespaces by,
ALTER USER OMS QUOTA UNLIMITED on OMS_SPC;
ALTER USER OMS QUOTA UNLIMITED on OMS_INDX_SPC;
Now revoking DBA role by,
REVOKE DBA FROM OMS;
ORA-04062: timestamp of procedure has been changed
Problem Description
In the database I have created one procedure named a as below.
create or replace procedure a(a number) as
begin
insert into t1 values(1);
commit;
end;
/
Now after creating database link using remote database machine whenever I access this procedure "A" it executes successfully and I get value "1" in table t1. Like below in example where orastdby_m is the database link, shaik is the schema name and value 1 is the argument value though argument value is not used in the procedure.
SQL> exec shaik.a@orastdby_m(1);
PL/SQL procedure successfully completed.
Now in the source database machine I changed the procedure as below. Though you can change anything like any literal; adding space or remove space. I changed value to be inserted from 1 to 2.
create or replace procedure a(a number) as
begin
insert into t1 values(2);
commit;
end;
/
Now in the other database whenever I execute the procedure using database link it throws error ORA-04062. But subsequent execution goes ok without any error unless I change something inside the original procedure.
SQL> exec shaik.a@orastdby_m(1);
BEGIN shaik.a@orastdby_m(1); END;
*
ERROR at line 1:
ORA-04062: timestamp of procedure "SHAIK.A" has been changed
ORA-06512: at line 1
SQL> exec shaik.a@orastdby_m(1);
PL/SQL procedure successfully completed.
Problem Analysis
ORA-4062 indicates that TIMESTAMP or SIGNATURE of NAME has been changed.
When a local piece of PL/SQL references a remote package, function, or procedure, the local PL/SQL engine needs to know if the reference is still valid, or, if the remote procedure has changed.
The locally compiled PL/SQL code is dependent on the remote code. This dependency is tracked by two models either TIMESTAMPS OR SIGNATURES in oracle.
The initialization parameter REMOTE_DEPENDENCIES_MODE is responsible which method to choose. This parameter can be set to either TIMESTAMP or SIGNATURE and can be set at the instance level(By setting ALTER SYSTEM) or at the session level(By setting ALTER SESSION). This can also be set at the client side session.
Also oracle allows "runtime binding" by which client PLSQL allows to delay for the actual binding up of a reference to a SCHEMA.OBJECT.
REMOTE_DEPENDENCIES_MODE = Timestamp
If the dependency mode is set to TIMESTAMP, the local PL/SQL block can only execute the remote PL/SQL block if the timestamp on the remote procedure matches the timestamp stored in the locally compiled PL/SQL block. If the timestamps do not match, the local PL/SQL must be recompiled.
REMOTE_DEPENCIES_MODE = Signature
If the dependency mode is set to SIGNATURE, the local PL/SQL block can still execute the remote PL/SQL block if its "signature" is the same, even if the timestamp has changed.
The term "signature" basically means the interface (procedure name, parameter types or modes) is the same, even if the underlying implementation has changed.
The error "ORA-04062: timestamp of procedure has been changed" is reported if the local PL/SQL block cannot call the remote procedure, since the timestamp or signature has changed on the remote end. A local recompilation may be required to make the call.
In the case of server to server calls, the local PL/SQL block is implicitly recompiled on the next call after an ORA-4062 error. In the case of client tools to server calls, the client Form or Report usually needs to be recompiled explicitly.
Solution of the Problem
When client-side PL/SQL tools are used OR when server-side PL/SQL calls are used across database links , set REMOTE_DEPENDENCIES_MODE to SIGNATURE. This reduces the chances of the ORA-4062 errors and the need for unnecessary recompilations.
You can change in client side by,
SQL> alter session set REMOTE_DEPENDENCIES_MODE=SIGNATURE;
Session altered.
Now changing the definition of procedure "A" in the source database will not result ORA-4062 in the remote database.
In the database I have created one procedure named a as below.
create or replace procedure a(a number) as
begin
insert into t1 values(1);
commit;
end;
/
Now after creating database link using remote database machine whenever I access this procedure "A" it executes successfully and I get value "1" in table t1. Like below in example where orastdby_m is the database link, shaik is the schema name and value 1 is the argument value though argument value is not used in the procedure.
SQL> exec shaik.a@orastdby_m(1);
PL/SQL procedure successfully completed.
Now in the source database machine I changed the procedure as below. Though you can change anything like any literal; adding space or remove space. I changed value to be inserted from 1 to 2.
create or replace procedure a(a number) as
begin
insert into t1 values(2);
commit;
end;
/
Now in the other database whenever I execute the procedure using database link it throws error ORA-04062. But subsequent execution goes ok without any error unless I change something inside the original procedure.
SQL> exec shaik.a@orastdby_m(1);
BEGIN shaik.a@orastdby_m(1); END;
*
ERROR at line 1:
ORA-04062: timestamp of procedure "SHAIK.A" has been changed
ORA-06512: at line 1
SQL> exec shaik.a@orastdby_m(1);
PL/SQL procedure successfully completed.
Problem Analysis
ORA-4062 indicates that TIMESTAMP or SIGNATURE of NAME has been changed.
When a local piece of PL/SQL references a remote package, function, or procedure, the local PL/SQL engine needs to know if the reference is still valid, or, if the remote procedure has changed.
The locally compiled PL/SQL code is dependent on the remote code. This dependency is tracked by two models either TIMESTAMPS OR SIGNATURES in oracle.
The initialization parameter REMOTE_DEPENDENCIES_MODE is responsible which method to choose. This parameter can be set to either TIMESTAMP or SIGNATURE and can be set at the instance level(By setting ALTER SYSTEM) or at the session level(By setting ALTER SESSION). This can also be set at the client side session.
Also oracle allows "runtime binding" by which client PLSQL allows to delay for the actual binding up of a reference to a SCHEMA.OBJECT.
REMOTE_DEPENDENCIES_MODE = Timestamp
If the dependency mode is set to TIMESTAMP, the local PL/SQL block can only execute the remote PL/SQL block if the timestamp on the remote procedure matches the timestamp stored in the locally compiled PL/SQL block. If the timestamps do not match, the local PL/SQL must be recompiled.
REMOTE_DEPENCIES_MODE = Signature
If the dependency mode is set to SIGNATURE, the local PL/SQL block can still execute the remote PL/SQL block if its "signature" is the same, even if the timestamp has changed.
The term "signature" basically means the interface (procedure name, parameter types or modes) is the same, even if the underlying implementation has changed.
The error "ORA-04062: timestamp of procedure has been changed" is reported if the local PL/SQL block cannot call the remote procedure, since the timestamp or signature has changed on the remote end. A local recompilation may be required to make the call.
In the case of server to server calls, the local PL/SQL block is implicitly recompiled on the next call after an ORA-4062 error. In the case of client tools to server calls, the client Form or Report usually needs to be recompiled explicitly.
Solution of the Problem
When client-side PL/SQL tools are used OR when server-side PL/SQL calls are used across database links , set REMOTE_DEPENDENCIES_MODE to SIGNATURE. This reduces the chances of the ORA-4062 errors and the need for unnecessary recompilations.
You can change in client side by,
SQL> alter session set REMOTE_DEPENDENCIES_MODE=SIGNATURE;
Session altered.
Now changing the definition of procedure "A" in the source database will not result ORA-4062 in the remote database.
ORA-02082: a loopback database link must have a connection qualifier
Problem Description
You are trying to create or drop a database link to the same database name. This may be true if you have a database that was cloned from another database on a different machine and now you try to create or drop a database link with the name of the original database. Below is an example.
SQL> create database link tiger;
create database link tiger
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier
SQL> drop database link tiger;
drop database link tiger
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier
Cause of the Problem
This is an expected behavior if database global name match with the database link creation name. Now let's see the global_name of the database.
SQL> select * from global_name;
GLOBAL_NAME
---------------------------------------
TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM
We see the global name of the database is started with tiger(db_name) and the default db_domain is REGRESS.RDBMS.DEV.US.ORACLE.COM.
Now whenever we try to create a database link named TIGER (without any domain) it takes name as TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM (original database link name+ default domain) which is equivalent to global_name of the database and thus error will occur because database link name must not be equal to the global database name.
Solution of the Problem
Two different solution of this problem.
1)Change the database link name so that it is different from global database name.
SQL> create database link tiger.net connect to user_name identified by password using 'TNS_NAME';
Database link created.
2)Change the global name of the database, create/drop database link and then back to global name of the database to the original name.
i)Error when creating database link name with same of global_name.
SQL> create database link tiger connect to user_name identified by password using 'TNS_NAME';
create database link tiger connect to user_name identified by password using 'TNS_NAME'
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier
ii)Change the global database name.
SQL> alter database rename global_name to test;
Database altered.
iii)Now dropping the database link tiger will work as now it (TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM) is not same as the global name (TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM)
SQL> select * from global_name;
GLOBAL_NAME
---------------------------------------
TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM
SQL> drop database link tiger;
Database link dropped.
iv)Also creating the database link with named Tiger (by default take as TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM) will work.
SQL> create database link tiger connect to user_name identified by password using 'TNS_NAME';
Database link created.
You can check it by,
v)Back to the original global database name.
SQL> alter database rename global_name to tiger;
Database altered.
You are trying to create or drop a database link to the same database name. This may be true if you have a database that was cloned from another database on a different machine and now you try to create or drop a database link with the name of the original database. Below is an example.
SQL> create database link tiger;
create database link tiger
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier
SQL> drop database link tiger;
drop database link tiger
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier
Cause of the Problem
This is an expected behavior if database global name match with the database link creation name. Now let's see the global_name of the database.
SQL> select * from global_name;
GLOBAL_NAME
---------------------------------------
TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM
We see the global name of the database is started with tiger(db_name) and the default db_domain is REGRESS.RDBMS.DEV.US.ORACLE.COM.
Now whenever we try to create a database link named TIGER (without any domain) it takes name as TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM (original database link name+ default domain) which is equivalent to global_name of the database and thus error will occur because database link name must not be equal to the global database name.
Solution of the Problem
Two different solution of this problem.
1)Change the database link name so that it is different from global database name.
SQL> create database link tiger.net connect to user_name identified by password using 'TNS_NAME';
Database link created.
2)Change the global name of the database, create/drop database link and then back to global name of the database to the original name.
i)Error when creating database link name with same of global_name.
SQL> create database link tiger connect to user_name identified by password using 'TNS_NAME';
create database link tiger connect to user_name identified by password using 'TNS_NAME'
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier
ii)Change the global database name.
SQL> alter database rename global_name to test;
Database altered.
iii)Now dropping the database link tiger will work as now it (TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM) is not same as the global name (TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM)
SQL> select * from global_name;
GLOBAL_NAME
---------------------------------------
TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM
SQL> drop database link tiger;
Database link dropped.
iv)Also creating the database link with named Tiger (by default take as TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM) will work.
SQL> create database link tiger connect to user_name identified by password using 'TNS_NAME';
Database link created.
You can check it by,
SQL> col host for a10
SQL> set lines 140
SQL> col owner for a10
SQL> col db_link for a40
SQL> select * from dba_db_links where host='TNS_NAME';
OWNER DB_LINK USERNAME HOST CREATED
---------- ---------------------------------------- ---------------- ---------- ---------
MAXIMSG TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM USER_NAME TNS_NAME 24-JAN-09
MAXIMSG TIGER.NET USER_NAME TNS_NAME 24-JAN-09
v)Back to the original global database name.
SQL> alter database rename global_name to tiger;
Database altered.
Subscribe to:
Posts (Atom)
