ORACLE EXP/IMP &DATAPUMP INTERVIEW QUESTIONS

ORACLE EXP/IMP & DATAPUMP INTERVIEW QUESTIONS

1. How can i make export faster?
   1. By using DIRECT=Y and BUFFER parameters

2. Which process is responsible for writing data into dumpfile?
   1. Which process is responsible for writing data into dumpfile?

3. What is the use of consistent=y parameter in export?
   1. It will take consistent values while taking export of a table

4. What the parameter COMPRESS will do during export?
   1. During import, It will put entire data in a single extent

5. How can we confirm that export dumpfile is valid?
   1. By using SHOW=Y option during import

6. If you got a dumpfile to import and don’t know the fromuser, how you will get  that information?
   1. We can check that in export log file. If not, we can do import with SHOW=Y which generates a log file. Fromuser will be there in that log file

7. What precautions you will take t perform a schema exp/imp between a prod and  dev database?
   1. We need to check if already user existing in dev database
   2. If so, drop the user (take DDL and permissions info well before) or drop all the objects

8. What are the advantages of datapump over exp/imp?
   1. It is faster than exp/imp as we can use parallel and other options
   2. List all other from your class notes

9. Can we import a 11g dumpfile into 10g database using datapump? If so, is it also  possible between 10g and 9i?
   1. Yes we can import from 11g to 10g using VERSION option. This is not possible between 10g and 9i as datapump is not there in 9i

10. We exported a table and imported into dev database. After checking we found  table is residing in SYSTEM tablespace. What could be the reason?
    1. The user is having RESOURCE role assigned. If we assign RESOURCE role, it will give some quota on SYSTEM tablespace which is dangerous

11. What you will do when export is running slow?
    1. We need to skip taking export of indexes, use BUFFER and DIRECT parameters

12. Import process is running slow, what can you do now to make it faster?
    1. We can skip importing indexes as it is the most time taking job during import

13. You are observing undo tablespace error during import, how you will avoid it?
1. We can use COMMIT=Y option

ORACLE NETWORKING INTERVIEW QUESTIONS

1. What is the importance of SQLNET.ORA file?
   1. It helps in choosing the type of connection to be established from client to server

2. What is the difference between TNSNAMES.ORA and SQLNET.ORA file?
   1. Tnsnames.ora will specify the entries on to which database to connect where as sqlnet.ora will tell which type of connection can be established

3. How to ensure users are connecting to shared server architecture?
   1. By specifying SERVER=SHARED in TNS entry

4. While user trying to connect as sysdba on windows machine, he is getting  “insufficient privileges” error. What could be the reason?
   1. The OS username is not added to ORA_DBA group
      5.In a server, we have 12 oracle 10g databases. How many listeners will be created  by default?

1. Only one. But it is always preferred to have separate listeners

6. How to check listener status at OS level?
   a.ps –ef | grep tns

7. What are the troubleshoot steps you follow when user complains about  database connectivity problem?
   1. Checking tns entry
   2. Checking listener status
   3. Do tnsping from client
   4. Based on the above results, we can analyze where the problem is

8. 100 users connected to the database. At that time DBA stopped listener, what  happens to those connections?
   1. Nothing will happen as they are already connected

9. How will you check if a port number can be used or not while configuring a  listener?
   1. Netstat

10. What is database link and what is its use?
    1. It is a object used to pull remote database’s data to local database

11. What is the difference between snapshot and materialized views?
    1. Snapshot won’t have log mechanism by which refresh time will increase day by day which is avoided inmaterialized views

12. What is the difference between normal view and materialized view?
    1. Normal view is just a stored query and it doesn’t contain any data. Materialized view will store the data

13. If a user complains that MV refresh is running slow, how you will react to that  problem?
    1. First we will check if MV log size is more than the table. If so, we need to drop and recreate only MV log
    2. Sometimes it may happen due to many materialized views on a single table, in such case we need to identify which are not in use

14. Which background process helps in MV refresh?
    1. Job queue cordinator (CJQ)

15. What is distributed database management system?
    1. Having data in multiple databases and combining them into a pool is called DDMS

16. What is two phase commit? Which env will use it?
    1. It is used in DDMS by which a transaction should be commited on both the databases, if not transaction will be rolled back

17. When to use database triggers?
1. In case we want to track some changes in the database

 

1. Explain the steps of installing oracle 10g on a linux machine

   1. Refer to the installation guide in class notes

2. What is the purpose of adding oinstall and dba groups?

   1. oinstall – to provide the user to install any oracle software

   2. dba – allows to administer the database

3. Is it compulsory that we need to give group names as oinstall and dba? Or can we give any other name?

   1. We can give any name, but those are oracle standards

4. What are kernel parameters and why to set them?

   1. They will define the memory allocation from physical memory to Oracle database

5. What is the difference in installing 10g and 11g on linux?

   1. 11g allows to set kernel parameter later whereas in 10g we need to do it before

6. What is Oracle inventory?

   1. It is a location which provides the oracle product information which are installed on a particular host

7. Why to run orainstRoot.sh and root.sh scripts at the end of installation?

   1. orainstRoot.sh will change the permissions for oraInventory and root.sh will create oratab file

8. When I am trying SQLPLUS, its throwing command not found error. How you will handle it?

   1. We need to set oracle bin directory in PATH environment variable

9. Where you will set environment variables in linux? Why to set them?

   1. We need to set in .bash_profile file. Setting them will help in easy access to commands

10. How you will set environment variables in windows?

    1. We can set in advanced tab in mycomputer properties

11. When trying to connect to the database, I am getting “net service name is incorrectly specified” error. What is the reason?

    1. ORACLE_SID is not set

12. How to check how many databases are up and running at OS level?

    1. ps –ef | grep smon. Even though this is the generic command that is used, this is not the perfect answer as it will give output even if instance is started. So the correct answer would be ps –ef | grep oracle. In the output, we need to see for processes which says LOCAL=NO which means someone connected to the database from remote machine

13. How to check the version and bit version of oracle software?

    1. SQL> select * from v$version;

14. How to check how many databases are existing on the server?

    1. Using oratab file. But this may not give perfect answer if manually created databases are not updated

15. How many databases can we create on a single server?

    1. Unlimited until cpu and memory configuration are supported

16. How many databases are there in your environment and categorize them?

    1. Tell any value between 70 to 100. Out of that say 35 are prod and rest are dev and test

17. What is the highest size of the databases you are maintaining?

    1. Tell any value between 400 to 700 GB

18. How many servers are there in your project? Which platforms you are comfortable to work in?

    1. Tell any value between 30 to 50. Platforms are all unix and windows flavours

19. Can we change the block size after database creation?

    1. No, we cannot

20. What are the 3 important steps after database created manually?

    1. Executing catalog.sql script

    2. Updating oratab file

    3. Executing catproc.sql script

21. How to see background process at DB level?

    1. SQL> select program from v$session;

22. How to start the database if pfile is not in default location?

    1. SQL> startup pfile=’location of pfile’

23. I am creating a database manually and while executing script, I am getting  “ORACLE  INSTANCE TERMINATED, DISCONNECTION FORCED” error. What  went wrong and how you will troubleshoot?

    1. This is due to UNDO tablespace name mismatch between create database script and pfile. Once fixed, problem will be resolved

24. What is the difference between ORACLE_BASE and ORACLE_HOME?

    1. ORACLE_BASE is the location where we can install oracle software and ORACLE_HOME is the database home location

1. How to automate database startup during linux bootupbshut and dbstart scripts

   25.What is your strategy to delete old traces?

1. We will delete them every 3 months except alert log. We will take backup of alertlog and will trim the file

   26.How to check RAM size at OS level?

1. We can user free or top command

   27.How to check the size of a single directory?

1. du –sk*

1. It is used to know the IO and memory statistics

6. 28.While starting the database, I am getting “END OF FILE ON COMMUNICATION  CHANNEL” error. You are the only user connected to the database. What could  be the reason for this?

   1. If a particular mount point is full, in that case we will get that error

7. 29.What are different types of SCN available?

   1. We have TOP, START, STOP, BLOCK SCN etc

8. 30.How many types of checkpoints are there?
   1. We have nearly 10 checkpoints which helps in making data permanent in different stages of transaction

9. 31.What is the use of oratab file?
   1. To identify how many databases existing on a server

10. 32.How do you check if oracle installed on a server?

    1. By checking orainventory location

11. 33.If I have 5 databases on a server, how to find which version they are?
    1. By checking oratab file
    2.