World of Oracle DBA

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 p

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 $ 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 form

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. 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

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 Fro

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:

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. 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

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 subseq

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 tig