Oracle DBA - Interview Questions

Oracle DBA - Interview Questions




1. How do you kill a session from the database?

Ans: alter system kill 'sid,serial#'

Usage : alter system kill '9,8' (Get the info from v$session

2. How do you know whether the process is Server Side Process?

Ans. By seeing the process in ps-ef as oracle+sid

eg: suppose the sid is prod, then the server process is refered as server side process and local=no

3. Daily Activities of a Oracle DBA?

Ans: 1. Check the Databse availability

2. Check the Listerner availability

3. check the alert log filie for errors

4. monitoring space availablilty in tablespaces

5. monitoring mount point (see capacity planning document)

6. Validate Database backup or Archive backup

7. Find objects which is going to reach max extents

8. Database Health check

9. CPU, Processor, Memory usage

4. Where do you get all hidden parameters ?

Ans: In the table x$ksppi

5. How do you see the names from that table?

Ans:select ksppinm,ksppdesc from x$ksppi where substr(ksppinm,1,1)='_'

6. How do increase the count of datafiles?

Ans: Generate the control file syntax from the existing control file and recreate the control file by changing the parameter MAXDATAFILES = yourdesired size

Procedure:

1. open the database

2. Generate the control file change the maxdatafiles

3. open the db in nomount

4. execute the syntax with noresetlogs

5. alter databse open

7. What is the init parameter to make use of profile?

Ans: resource_limits=true

8. How do you know whether the parameter is dynamic or static?

Ans: By going v$parameter and check the fields isses_modifiable and issys_modifible

9. What is the package and procedure name to conver dmt to lmt and vice versa?

Ans: exec dbms_space_admin.tablespace_migrate_from_local("gtb")

exec dbms_space_admin.tablespace_migrate_to_local("gtb1")

10. What is the use of nohup?

Ans: The execution of a specific task is performed in the server side with out any interupting

Usage : nohup cp -r * /tmp/. &

11. Where alert log is stored? What is the parameter?

Ans: in bdump. parameter is background_dump_dest

11. Where trace file are stored? What is the parameter?

Ans: in udump. parameter is user_dump_dest

12. Common Oracle Errors ……

1) ORA-01555 : Snapshot Too Old

2) ORA-01109 : Database Not Mounted

3) ORA-01507 : Database Not Open

4) ORA-01801 : Database already In startup mode.

5) ORA-600 : Internal error code for oracle program

Usage : oerr ORA 600

13. How do you enable traceing while you are in the database?

Ans : alter session set sql_trace=true;

14. If you want to enable tracing in remote system? what will u do?

Ans: exec dbms_system.SET_SQL_TRACE_IN_SESSION(9,3,TRUE);

15. Which role you grant to rman user while configuring rman user

Ans: recover_catalog_owner

16. Where do you get to know the version of your oracle software and what is your version?

Ans: from v$version(field is banner) and the version is 9.2.0.1.0

17. What is the parameters to set in the init.ora if you create db using OMF(Oracle Managed Files)?

Ans: db_create_file_dest=

db_create_online_log_dest_1=

18. What is a runaway session?

Ans: you killed a session in the database but it still remains in the os level and vice versa. Its called runaway session. If runaway session is there cpu consumes more usage.

19. How do you know whether the specific tablespace is in begin backup mode?

Ans: select status from v$backup. if it is active it means it is in begin backup mode

20. If you want to maintain one more archive destination which parameter you have to set ?

Ans:Its a dynamic parameter you have to set log_archive_duplex_dest=

Usage : alter system set log_archive_duplex_dest=

21. How do you know the create syntax of your function/procedure/index/synonym ?

Ans: using function dbms_metadata.get_ddl(object type,object name,owner)

22. What is the password you have to set in the init.ora to enable remote login ?

Ans: remote_login_password_file=exclusive

23.How do u set crontab to delete 5 days old trace files at daily 10'0 clock?

Ans: crontab -e

0 10 * * * /usr/bin/find /u001/admin/udump -name "*.trc" -mtime +5 -exec rm -rf {} \;
save and exit (wq!)

24.How do u know when system was last booted?

Ans: 3 ways 1.uptime cmd

2.top

3.who -b

4.w

24.How do u know load on system?

Ans: 1.w

2.top

25. How do you know when the process is started

Ans : Using ps -ef grep process name

26. Tell me the location of Unix/Solaris log messages stored?

Ans:/var/log/messages(Unix)

/var/adm/messages (solaris)

27.How do you take backup of a controlfile?

Ans: alter database backup controlfille to destination (Database should be open)
file will be save in the your destination

28. What is the parameter to set the user trace enabiling?

Ans: sql_trace = true

29. How do you know whether archive log mode is enable or not?

Ans: issue command 'archive log list' at sqlplus prompt

30. Where do you get the information of quotas?

Ans: dba_ts_quotas view

31. How do you know how much archives are generated ?

Ans: using the view v$log_history

32. What is a stale?

Ans: The redolog file which has not been used yet

33. How do you read the binary file?

Ans: using strings -a

usage : strings -a filename

34.How do you read control file?

Ans: using command tkprof

Usage: tkprof contrl.ctl ctrol2.txt

35. How do you send the data to tape?

Ans: using 1. tar -cvf or 2. cpio

36. How do you connect to db and startup and shutdown the db without having dba group?

Ans: using remote_login_passwordfile

37. When will you take Cold back up especially?

Ans: during upgradation and migration

38. How do you enable/disable debugging mode in unix?

Ans: set -x and set +x

39. How do you get the create syntax of a table or index or function or procedure?

Ans: select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

40. How do you replace a string in vi editor?

Ans: %s/name/newname

41. When ckpt occurs?

Ans: 1. for every 3 seconds

2. when 1/3rd of DB buffer fills

3. when log swtich occurs

4. when databse shuts down

42. In which file oracle inventory information is available?

Ans : /etc/oraInst.loc

42. Where all oracle homes and oracle sid information available?

Ans: /etc/oratab

43. What is the environment variable to set the location of the listener.ora

Ans: TNS_ADMIN

44. How do you know whether listener is running or not

Ans: ps -ef grep tns

45. What are the two steps involved in instace recovery?

Ans: 1. Roll forward (redofiles data to datafiles)

2. Roll backward (undo files to datafiles).

46. If you delete the alert log fle what will happen?

Ans: New alert log will be created automatically

57. How do you create a table in another tablespace name?

Ans: create table xyz (a number) tablespace system

58. What are the modes/options in incomplete recovery?

Ans: cancel based, change based, time based

59. How do you create an alias?

Ans: alias bdump='cd $ORACLE_HOME/rdbms/admin'

60. Types of trace files?

Ans: 1. trace files generated by database (bdump)

2. trace files generated by user(udump)

61. How do you find the files whose are more than 500k?

Ans : fnd . -name "*" -size +500k

62. Where do you configure your hostname in linux?

Ans: vi /etc/hosts

63. What are the types of segments?

Ans: Data Segment,Undo Segment,Temporary segment,Index Segment

64. What is a synonym and different types of synonyms?

Ans: A synonym is an alias for a table,view, sequence or program unit.

1. Public synonym

2. private synonym

65. What are mandatory background processes in Oracle Database?

Ans: smon,pmon,ckpt,dbwr,lgwr

66. How do you make your redolog group inactive?

Ans: alter system switch logfile;

67. How do you drop a tablespace?

Ans: drop tablespace ts1 including contents and datafiles;

68. What is the parameter allows you to create max no. of groups?

Ans: maxlogfiles=

69. What is the paramter allows to create max no. of members in a group?

Ans: maxlognumbers=

70. What Controlfile contains?

Ans: Database name

Database creation time stamp

Address of datafiles and redolog files

Check point information

SCN information

71. What are the init parameters you have to set to make use of undo management?

Ans: undo_tablespace= undotbs1

undo_management= auto

undo_retention= time in minutes

comment rollback_segment

73. How do you enable monitoring of a table?

Ans: alter table tablename monitoring

74. How do you disable monitoring of a table?

Ans: alter table tablename no monitoring

75. How do you check the status of the table whether monitoring or not?

Ans: select table_name, monitoring from dba_table where owner='scott;

76. In which table records of monitoirg will be stored?

Ans: dba_tab_modificaitons

77. How do you get you demo files get created in your user?

Ans: ?/sqlplus/demo/demobld.sql execute this script on which user you want

78. What is netstat? and its Usage?

Ans: it is a utility to know the port numbers availability. Usage: netstat -na grep port number

79.How do you make use of stats pack?

Ans: 1. spcreate.sql (?/rdbms/admin/) -- to create statspack

2. execute statspack.snap -- to collect datbase snaps

3. spreport.sql-- to generate reports(in your current directory)

4. spauto.sql----to schedule a job

5. sppurge.sql---to delete statistics

6. spdrop.sql----to drop the statistics