dba 1 questions

1. Tell me the 10G to 11G upgradation steps?

First ask for the version , we cannot directly upgrade from 10.1.0 release to 11.1 release.  Initally need to uograde to 10.2.0.4 then we can upgrade to 11.1.0 release.

1. How will you clone the DB using Rman active cloning concept?
2. What is the use of DB_FILE_NAME_CONVERT parameter?
3. Why do you give nofilenamecheck while configuring dataguard using rman active cloning?
4. What is the difference between two catalog commands in RMAN?
5. How will you do rman merge backup?
6. What is the use of block change tracking? What kind of information will be stored in tracking file?
7. Backup strategy in your environment?
8. How will you configure dataguard in 11G?
9. What are the difference between physical standby and logical standby?
10. If there are lots of logs were missing in standby, What will you do?
11. What are the 3 modes in dataguard and explain about maximum availability?
12. Tell me few 11G features?
13. Tell me few 11G B/G processes?
14. Tell me one recovery scenario you have faced in your environment?
15. My database’s performance is very slow, what are the steps you will take to rectify?
16. Tell me few wait events you have faced in your environment and solution for it?
17. How will you change the snapshot settings?
18. What are the difference between datapump and exp/imp?
19. What is the use of transport_tablespaces in datapump and how will you use it?
20. How will you create diskgroup in ASM?
21. How will you add a new disk in diskgroup?
22. How will you find used and free space in diskgroups?
23. Stripping concept in ASM?
24. What are startup steps involves in Rac Database?
25. Failover concept in RAC?
26. What is node eviction?
27. Explain about various IP addresses in RAC?
28. What is OCR & Votedisk in RAC?
29. What is split brain syndrome?

First Round: Online Test ( 30 Questions but mark varies for each questions)

1. Migration( mostly related to endian formats) – around 5 questions.
2. In ASM during adding and removing disks performance will be down? How to increase the performance during that time?
3. How will you find the control file path?
4. In merge statement which is the order to place insert, update and delete.
5. Sequence of steps in database creation?
6. How will you increase the size of tablespace?
7. What will run to make the invalid objects as valid?
8. Where,group by, having – like this they gave four options and asked which is correct?
9. Flashback query and flashback transaction query? (around 4 flashback questions)
10. Background process related to achieving?
11. Partition – one question
12. Some questions in rman, views and parameters.

 

Second Round: Face to face technical

1. Tell me about yourself, your experience and your daily activities?
2. 11g features?
3. What is Automatic block recovery?
4. To enable automatic block recovery what will you do?
5. If a datafile is corrupted how will u see the corruption which view?
6. How will you increase the performance of block recovery?
7. What privileges are there in connect role?
8. 11g features in rman? Especially something about performance?
9. 11g features in datapump?
10. How will you check the status of rman backup?
11. Where will u find the rman log?
12. How will check the status of datapump jobs?
13. How will u export and import the user scott to another user in datapump?
14. Do u know about rman compression in 11g?
15. Which compression algorithm will be default in rman?
16. How will you see which compression algorithm is used in rman?
17. Which background process transfers the logs to standby database?
18. Which background process receives the logs in standby database?
19. Which background process apply the logs in standby database?
20. Where will you check whether archives received and applied?
21. Patching and upgradation complete steps?
22. If objects are invalid what will you do?
23. Rman cloning?
24. How will you gather statistics?
25. How will you find the row migration?
26. In which view you will find the HWM?
27. How will perform if standby database goes out of sync?
28. Do you know about data security?
29. Auditing?
30. How will you increase the size of temporary tablespace?
31. How will u find the top utilized query?
32. What is the use of crontab?
33. How will you view the scheduled cron jobs?
34. In vi editor what is the option to change the text globally?
35. How will you take a cumulative backup?
36. What is sqlnet.ora?

 

 

ORACLE DBA GENERAL INTERVIEW QUESTIONS

Hi ,
Below are some general interview questions which can be useful:

Q 1)What is tablespace and Quota,if i create any user how much quota is allocated to it?How to check the user had been granted unlimited quota for a particular tablespace?(TCS INTERVIEW QUESTION)

Ans: TABLESPACE A tablespace is a logical storage unit within an Oracle database. It is logical because a tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server. Btw, a datafile belongs to exactly one tablespace. Each table, index and so on that is stored in an Oracle database belongs to a tablespace. The tablespace builds the bridge between the Oracle database and the filesystem in which the table's or index' data is stored. There are three types of tablespaces in Oracle:
•Permanent tablespaces
•Undo tablespaces
•temporary tablespaces

Quotas on tablespaces
Users can have (space) quotas on tablespaces. This is a means to limit how much space a user uses on a tablespace. This quota can be set using alter user quota... Use DBA_TS_QUOTAS/USER_TS_QUOTAS to find out a user's quota on a tablespace as well as how much he has already occupied.

If we just create a user and query dba_ts_quotas it will result in no rows as given below:

http://forums.oracle.com/forums/thread.jspa?threadID=1046926&tstart=0
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3396797633001

If I get the value -1 in maxbytes column of DBA_TS_QUOTAS it will tell you that we had allocated unlimited quota to a particular tablespace.
Refer the above for more details.This is something interesting.

Q 2) What are batch files?(TCS interview question)
Ans:
In DOS, OS/2, and Microsoft Windows, a batch file is a text file containing a series of commands intended to be executed by the command interpreter. When a batch file is run, the shell program (usually COMMAND.COM or cmd.exe) reads the file and executes its commands, normally line-by-line. Batch files are useful for running a sequence of executables automatically and are often used by system administrators to automate tedious processes.[1] Unix-like operating systems (such as Linux) have a similar type of file called a shell script.[2]
DOS batch files have the filename extension .bat. Batch files for other environments may have different extensions, e.g. .cmd or .bat in the Microsoft Windows NT-family of operating systems and OS/2, or .btm in 4DOS and 4NT related shells. The Windows 9x family of operating systems only recognize the .bat extension

Explanation
Batch files are executed by every line being executed in order until the end is reached or something else stops it (such as the key shortcut for terminating batch processing; 'Ctrl' + 'C'). This batch file first turns off the 'echo' with ECHO OFF. This stops the display of input from the batch file and limits the display to output from commands only. Since this command is executed before the input is silenced, the @ symbol is used at the start of the command which stops that command line showing input. Then the ECHO command is used again in the form ECHO Hello World! which outputs the line Hello World!. Then the command ECHO. is used which adds the empty line below Hello World!, using the . so that the command doesn't output the input display's state (ECHO is on. or ECHO is off.) and just outputs an empty line. The . can also be used to prevent the ECHO command from confusing an attempt to output a line beginning with 'ON' or 'OFF' from changing the state of showing input. Then the PAUSE command is used which pauses execution until the user presses a key. The Press any key to continue . . . prompt is output by the command. Lastly, after the user presses a key the command ECHO ONis used which turns the prompt and input on again, so that if the file is executed from the Command Prompt, rather than Windows Explorer then when the execution ends the user can see the prompt again to use normally. After the last line is reached the batch file ends execution automatically. If it was started from the command prompt (by entering the name of the file when in its directory) then the window remains when finished, but when started from Windows Explorer the window automatically closes upon the end of execution

Q 3)How can we upgrade a database from one version to another version with zero or low downtime in 24/7 or RAC environment?
Ans: Rolling upgrade is a zero-downtime method for upgrading the Oracle software.

The term rolling upgrade refers to upgrading different databases or different instances of the same database (in a Real Application Clusters environment) one at a time, without stopping the database.

The advantage of a RAC rolling upgrade is that it enables at least some instances of the RAC installation to be available during the scheduled outage required for patch upgrades. Only the RAC instance that is currently being patched needs to be brought down. The other instances can continue to remain available. This means that the impact on the application downtime required for such scheduled outages is further minimized. Oracle's opatch utility enables the user to apply the patch successively to the different instances of the RAC installation.

Rolling upgrade is available only for patches that have been certified by Oracle to be eligible for rolling upgrades. Typically, patches that can be installed in a rolling upgrade include:
Patches that do not affect the contents of the database such as the data dictionary
Patches not related to RAC internode communication
Patches related to client-side tools such as SQL*PLUS, Oracle utilities, development libraries, and Oracle Net
Patches that do not change shared database resources such as datafile headers, control files, and common header definitions of kernel modules
Rolling upgrade of patches is currently available for one-off patches only. It is not available for patch sets.
Rolling patch upgrades are not available for deployments where the Oracle Database software is shared across the different nodes. This is the case where the Oracle home is on Cluster File System (CFS) or on shared volumes provided by file servers or NFS-mounted drives. The feature is only available where each node has its own copy of the Oracle Database software.


Q 4)What is the difference between delete and truncate(HCL interview question)?
Ans:
Deletes perform normal DML. That is, they take locks on rows, they generate redo (lots of it), and they require segments in the UNDO tablespace. Deletes clear records out of blocks carefully. If a mistake is made a rollback can be issued to restore the records prior to a commit. A delete does not relinquish segment space thus a table in which all records have been deleted retains all of its original blocks.

Truncates are DDL and, in a sense, cheat. A truncate moves the High Water Mark of the table back to zero. No row-level locks are taken, no redo or rollback is generated. All extents bar the initial are de-allocated from the table (if you have MINEXTENTS set to anything other than 1, then that number of extents is retained rather than just the initial). By re-positioning the high water mark, they prevent reading of any table data, so they have the same effect as a delete, but without all the overhead. Just one slight problem: a truncate is a DDL command, so you can't roll it back if you decide you made a mistake. (It's also true that you can't selectively truncate -no "WHERE" clause is permitted, unlike with deletes, of course).

By resetting the High Water Mark, the truncate prevents reading of any table's data, so they it has the same effect as a delete, but without the overhead. There is, however, one aspect of a Truncate that must be kept in mind. Because a Truncate is DDL it issues a COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is possible.


Q 5)When your recovery catalog gets corrupted how you recover your database?(PATNI INTERVIEW QUESTION)
Ans: I answer as per my knowledge that If you have the backup of datafiles and redolog files you can create the control file by using the create control file command and open the database with resetlogs option if required.

To be on the safer side we should backup our recovery catalog also.

The below link might be useful for backing up Recovery catalog:


http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmcatdb.htm#CIHFEIIH

Q 6)When your current redolog gets corrupted how you recover your Database?
:(** PATNI INTERVIEW QUESTION **)
Ans:
Recovery From Current Redolog Corruption
1. startup error message
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) cannot find file
5.1.2 Current Redolog Corruption
Two case:
A. shutdown normally, no transaction to recovery, recreate log group with 'alter database clear unarchived logfile group n'
B. active transaction exists. database need media recovery. log group need synchronized. there are 2 methods:
a. imcomplete recovery. keep the consistence of database. but this method requires archivelog mode and valid backup.
b. force recovery. but may cause inconsistence.
5.1.2.1 recovery with backup
1. accounting a error when open database
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) OS cannot found file
2. check V$log, and notice the current redolog corrupted
SQL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARCHIVED STATUS
---------- ---------- -------- ----------------
1 1 NO CURRENT
2 2 YES INACTIVE
3 3 YES INACTIVE
3. cannot clear the current redolog
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'
4. copy a full backup and recover database
until scn or until cnacel
recover database until cancel
select auto recover with all the valid archivelogs and redologs, and
recover database until cancel
enter cancel to do a incomplete recovery (that's need recover twice)
Example:
SQL> recover database until cancel;
Auto
...
SQL> recover database until cancel;
Cancel;
5. open database with: alter database open resetlogs
Description:
1. incomplete recovery may cause data lost of current redolog.
2. need archivelog mode and full backup.
3. backup database after recovery.
4. strongly recomment that make log mirror on different disks.
5.1.2.2 force recovery without backup
1. accounting a error
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2)
2、notice curruent redolog corrupted
SQL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARCHIVED STATUS
---------- ---------- -------- ----------------
1 1 NO CURRENT
2 2 YES INACTIVE
3 3 YES INACTIVE
3、cannot clear
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'
4、shutdown database
SQL>shutdown immediate
5、add the following parameter to init.ora
_allow_resetlogs_corruption=TRUE
6、restart database and recover with 'until cancel'
SQL>recover database until cancel;
Cancel

SQL>alter database open resetlogs;
7、full export database after open database successfully
8、shutdown database, remove init parameter
_all_resetlogs_corrupt
9、recreate database
10、import and finish recovery
11、recomment to run
ANALYZE TABLE ...VALIDATE STRUCTURE CASCADE;
Description
1. use this method only when no other method. because it may cause database inconsistent
2. cause data loss, but less than recover with backup. because the data without commit be recovered too.
3. strongly recomment run complete recreation and valication check.
4. should backup database fullly.
5. strongly recomment that make log mirror on different disks, no data loss can be accept on production environment.
Reference http://www.itpub.net/thread-126320-1-1.html


Q 7)What are the parameters responsible for generating Execution Plan? what is db_file_multiblock_read_count?
Ans: The important parameters are:
1)optimizer_mode
2)db_file_multiblock_read_count


DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.

As of Oracle Database 10g release 2, the default value of this parameter is a value that corresponds to the maximum I/O size that can be performed efficiently. This value is platform-dependent and is 1MB for most platforms.Because the parameter is expressed in blocks, it will be set to a value that is equal to the maximum I/O size that can be performed efficiently divided by the standard block size. Note that if the number of sessions is extremely large the multiblock read count value is decreased to avoid the buffer cache getting flooded with too many table scan buffers.

Even though the default value may be a large value, the optimizer will not favor large plans if you do not set this parameter. It would do so only if you explicitly set this parameter to a large value.

Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high.

The maximum value is the operating system's maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE). If you set this parameter to a value greater than the maximum, Oracle uses the maximum.

The db_file_multiblock_read_count is Operating system dependent and in turn depends on three things
a)Sequential read time
b)Scattered read speed and
c)CPU consumption
Apart from optimizer_mode & db_file_multiblock_read_count we have parameter like Optimizer_index_cost_adj,Optimizer_index_caching & Parallel_automatic_tuning which helps optimizer to generate execution plan for SQL statements.

 

 

 

 

DBA (Database Administrator) Interview Questions

1.    What are the differences between database designing and database modeling?

2.    If the large table contains thousands of records and the application is accessing 35% of the table, which method do you use: index searching or full table scan?

3.    In which situation whether peak time or off peak time you will execute the ANALYZE TABLE command. Why?

4.    How to check to memory gap once the SGA is started in Restricted mode?

5.    All the users are complaining that their application is hanging. How you will resolve this situation in OLTP?

6.    If the SQL * Plus hangs for a long time, what is the reason?

7.    Shall we create procedures to fetch more than one record?

8.    How do you increase the performance of %LIKE operator?

9.    You are regularly changing the package body part. How will you create or what will you do before creating that package?

10.  How can you see the source code of the package?

11.  Dual table explain. Is any data internally storing in dual table.

12.  Lot of users are accessing select sysdate from dual and they getting some millisecond differences. If we execute SELECT SYSDATE FROM EMP; what error will we get. Why?

13.  In exception handling we have some NOT_FOUND and OTHERS. In inner layer we have some NOT_FOUND and OTHERS. While executing which one whether outer layer or inner layer will check first?

14.  What is mutated trigger, is it the problem of locks. In single user mode we got mutated error, as a DBA how you will resolve it?

15.  Schema A has some objects and created one procedure and granted to Schema B. Schema B has the same objects like schema A. Schema B executed the procedure like inserting some records. In this case where the data will be stored whether in Schema A or Schema B?

16.  What is bulk SQL?

17.  How to do the scheduled task/jobs in Unix platform?

18.  If the entire disk is corrupted how will you and what are the steps to recover the database?

19.  How will you monitor rollback segment status?

20.  List the sequence of events when a large transaction that exceeds beyond its optimal value when an entry wraps and causes the rollback segment to expand into another extend?

21.  What is redo log file mirroring?

22.  How can we plan storage for very large tables?

23.  When will be a segment released?

24.  What are disadvantages of having raw devices?

25.  List the factors that can affect the accuracy of the estimate?

26.  What is the difference between $$DATE$$ & $$DBDATE$$? - $$DBDATE$$ retrieves the current database date$$date$$ retrieves the current operating system.

27.  How to prevent unauthorized use of privileges granted to a Role?

28.  What is a deadlock and Explain?

29.  What are the basic element of base configuration of an Oracle database?

30.  What is an index and How it is implemented in Oracle database?

31.  What is the use of redo log information?

32.  What is a schema?

33.  What is Parallel Server?

34.  What is a database instance and Explain?

35.  What is a datafile?

36.  What is a temporary segment?

37.  What are the uses of rollback segment?

 

 

Here are some of the Common Interview Questions for Oracle Database Administrator. As of now it contains 120 Questions. These questions are common for both Senior Oracle DBA or Junior DBA. I have divided the questions into 3 categories Technical Interview Questions, Unix Related questions asked to an Oracle Database Administrator (DBA) and Backup and Recovery Interview Questions commonly asked in Oracle DBA interview

1. What is an Oracle Instance?

2. What information is stored in Control File?

3. When you start an Oracle DB which file is accessed first?

4. What is the Job of  SMON, PMON processes?

5. What is Instance Recovery?

6. What is written in Redo Log Files?

7. How do you control number of Datafiles one can have in an Oracle database?

8. How many Maximum Datafiles can there be in an Oracle Database?

9. What is a Tablespace?

10. What is the purpose of  Redo Log files?

11. Which default Database roles are created when you create a Database?

12. What is a Checkpoint?

13. Which Process reads data from Datafiles?

14. Which Process writes data in Datafiles?

15. Can you make a Datafile auto extendible. If yes, how?

16. What is a Shared Pool?

17. What is kept in the Database Buffer Cache?

18. How many maximum Redo Logfiles one can have in a Database?

19. What is difference between PFile and SPFile?

20.  What is PGA_AGGREGRATE_TARGET parameter?

21. Large Pool is used for what?

22. What is PCT Increase setting?

23. What is PCTFREE and PCTUSED Setting?

24. What is Row Migration and Row Chaining?

25. What is 01555 - Snapshot Too Old error and how do you avoid it?

26. What is a Locally Managed Tablespace?

27. Can you audit SELECT statements?

28. What does DBMS_FGA package do?

29. What is Cost Based Optimization?

30. How often you should collect statistics for a table?

31. How do you collect statistics for a table, schema and Database?

32. Can you make collection of Statistics for tables automatic?

33. On which columns you should create Indexes?

34. What type of Indexes are available in Oracle?

35. What is B-Tree Index?

36. A table is having few rows, should you create indexes on this table?

37. A Column is having many repeated values which type of index you should create on this column, if you have to?

38. When should you rebuilt indexes?

39. Can you built indexes online?

40. Can you see Execution Plan of a statement.

41. A table is created with the following setting

      storage (initial 200k
                   next 200k
                   minextents 2
                   maxextents 100
                   pctincrease 40)

     What will be size of 4th extent?

42. What is DB Buffer Cache Advisor?

43. What is STATSPACK tool?

44. Can you change SHARED_POOL_SIZE online?

45. Can you Redefine a table Online?

46. Can you assign Priority to users?

47. You want users to change their passwords every 2 months. How do you enforce this?

48. How do you delete duplicate rows in a table?

49. What is Automatic Management of Segment Space setting?

50. What is the difference between DELETE and TRUNCATE statements?

51. What is COMPRESS and CONSISTENT setting in EXPORT utility?

52. What is the difference between Direct Path and Convention Path loading?

53. Can you disable and enable Primary key?

54. What is an Index Organized Table?

55. What is a Global Index and Local Index?

56. What is the difference between Range Partitioning and Hash Partitioning?

57. What is difference between Multithreaded/Shared Server and Dedicated Server?

58. Can you import objects from Oracle ver. 7.3 to 9i?

59. How do you move tables from one tablespace to another tablespace?

60. How do see how much space is used and free in a tablespace?

 

https://www.oracle-dba-online.com/oracle_dba_interview_questions.htm

https://www.wisdomjobs.com/e-university/oracle-dba-interview-questions.html

 

Oracle DBA Interview Questions
1. Explain the difference between a hot backup and a cold backup and the benefits associated with each.
A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive
log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in
archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is
occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is
typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the
database does not require being in archive log mode and thus there will be a slight performance gain as the database
is not cutting archive logs to disk.
2. You have just had to restore from backup and do not have any control files. How would you go about
bringing up this database?
I would create a text based backup control file, stipulating where on disk all the data files where and then issue the
recover command with the using backup control file clause.
3. How do you switch from an init.ora file to a spfile?
Issue the create spfile from pfile command.
4. Explain the difference between a data block, an extent and a segment.
A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of
additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are
called extents. All the extents that an object takes when grouped together are considered the segment of the database
object.
5. Give two examples of how you might determine the structure of the table DEPT.
Use the describe command or use the dbms_metadata.get_ddl package.
6. Where would you look for errors from the database engine?
In the alert log.
7. Compare and contrast TRUNCATE and DELETE for a table.
Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The
difference between the two is that the truncate command is a DDL operation and just moves the high water mark
and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a
rollback and thus take longer to complete.
8. Give the reasoning behind using an index.
Faster access to data blocks in a table.
9. Give the two types of tables involved in producing a star schema and the type of data they hold.
2
Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that
will help describe the fact tables.
10. . What type of index should you use on a fact table?
A Bitmap index.
11. Give two examples of referential integrity constraints.
A primary key and a foreign key.
12. A table is classified as a parent table and you want to drop and re-create it. How would you do this
without affecting the children tables?
Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.
13. Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and
disadvantages to each.
ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have
occurred in the database so that you can recover to any point in time. NOARCHIVELOG mode is basically the
absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time.
NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus
increases the performance of the database slightly.
14. What command would you use to create a backup control file?
Alter database backup control file to trace.
15. Give the stages of instance startup to a usable state where normal users may access it.
STARTUP NOMOUNT - Instance startup
STARTUP MOUNT - The database is mounted
STARTUP OPEN - The database is opened
16. What column differentiates the V$ views to the GV$ views and how?
The INST_ID column which indicates the instance in a RAC environment the information came from.
17. How would you go about generating an EXPLAIN plan?
Create a plan table with utlxplan.sql.
Use the explain plan set statement_id = 'tst1' into plan_table for a SQL statement
Look at the explain plan with utlxplp.sql or utlxpls.sql
18. How would you go about increasing the buffer cache hit ratio?
3
Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was
necessary then I would use the alter system set db_cache_size command.
19. Explain an ORA-01555
You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo
retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the
error message.
20. Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the
oracle products reside.
21. How would you determine the time zone under which a database was operating?
select DBTIMEZONE from dual;
22. Explain the use of setting GLOBAL_NAMES equal to TRUE.
Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE
and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are
linking.
23. What command would you use to encrypt a PL/SQL application?
WRAP
24. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a
single task. While a procedure does not have to return any values to the calling application, a function will return a
single value. A package on the other hand is a collection of functions and procedures that are grouped together based
on their commonality to a business function or application.
25. Explain the use of table functions.
Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal
table or view in a SQL statement. They are also used to pipeline information in an ETL process.
26. Name three advisory statistics you can collect.
Buffer Cache Advice, Segment Level Statistics, & Timed Statistics
27. Where in the Oracle directory tree structure are audit traces placed?
In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer
4
28. Explain materialized views and how they are used.
Materialized views are objects that are reduced sets of information that have been summarized, grouped, or
aggregated from base tables. They are typically used in data warehouse or decision support systems.
29. When a user process fails, what background process cleans up after it?
PMON
30. What background process refreshes materialized views?
The Job Queue Processes.
31. How would you determine what sessions are connected and what resources they are waiting for?
Use of V$SESSION and V$SESSION_WAIT
32. Describe what redo logs are.
Redo logs are logical and physical structures that are designed to hold all the changes made to a database and are
intended to aid in the recovery of a database.
33. How would you force a log switch?
ALTER SYSTEM SWITCH LOGFILE;
34. Give two methods you could use to determine what DDL changes have been made.
You could use Logminer or Streams
35. What does coalescing a tablespace do?
Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free
extents into large single extents.
36. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to
store those objects meant to be used as the true objects of the database.
37. Name a tablespace automatically created when you create a database.
The SYSTEM tablespace.
38. When creating a user, what permissions must you grant to allow them to connect to the database?
Grant the CONNECT to the user.
39. How do you add a data file to a tablespace?
5
ALTER TABLESPACE <tablespace_name> ADD DATAFILE <datafile_name> SIZE <size>
40. How do you resize a data file?
ALTER DATABASE DATAFILE <datafile_name> RESIZE <new_size>;
41. What view would you use to look at the size of a data file?
DBA_DATA_FILES
42. What view would you use to determine free space in a tablespace?
DBA_FREE_SPACE
43. How would you determine who has added a row to a table?
Turn on fine grain auditing for the table.
44. How can you rebuild an index?
ALTER INDEX <index_name> REBUILD;
45. Explain what partitioning is and what its benefit is.
Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.
46. You have just compiled a PL/SQL package but got errors, how would you view the errors?
SHOW ERRORS
47. How can you gather statistics on a table?
The ANALYZE command.
48. How can you enable a trace for a session?
Use the DBMS_SESSION.SET_SQL_TRACE or
Use ALTER SESSION SET SQL_TRACE = TRUE;
49. What is the difference between the SQL*Loader and IMPORT utilities?
These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies
on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be
loaded that has been produced by other utilities from different data sources just so long as it conforms to ASCII
formatted or delimited files.
50. Name two files used for network connection to a database.
TNSNAMES.ORA and SQLNET.ORA 

 

http://www.a2zinterviews.com/oracle-dba-questions/oracle-admin/oracle-admin-interview-questions_2.php

 

 http://www.dba-oracle.com/art_dba_interview.htm

http://www.vitalsofttech.com/interview-questions/

 

Here are some of the Common Interview Questions for Oracle Database Administrator. As of now it contains 120 Questions. These questions are common for both Senior Oracle DBA or Junior DBA. We have divided the questions into 3 categories Technical Interview Questions, Unix Related questions asked to an Oracle Database Administrator (DBA) and Backup and Recovery Interview Questions commonly asked in Oracle DBA interview

1. What is an Oracle Instance?

2. What information is stored in Control File?

3. When you start an Oracle DB which file is accessed first?

4. What is the Job of  SMON, PMON processes?

5. What is Instance Recovery?

6. What is written in Redo Log Files?

7. How do you control number of Datafiles one can have in an Oracle database?

8. How many Maximum Datafiles can there be in an Oracle Database?

9. What is a Tablespace?

10. What is the purpose of  Redo Log files?

11. Which default Database roles are created when you create a Database?

12. What is a Checkpoint?

13. Which Process reads data from Datafiles?

14. Which Process writes data in Datafiles?

15. Can you make a Datafile auto extendible. If yes, how?

16. What is a Shared Pool?

17. What is kept in the Database Buffer Cache?

18. How many maximum Redo Logfiles one can have in a Database?

19. What is difference between PFile and SPFile?

20.  What is PGA_AGGREGRATE_TARGET parameter?

21. Large Pool is used for what?

22. What is PCT Increase setting?

23. What is PCTFREE and PCTUSED Setting?

24. What is Row Migration and Row Chaining?

25. What is 01555 – Snapshot Too Old error and how do you avoid it?

26. What is a Locally Managed Tablespace?

27. Can you audit SELECT statements?

28. What does DBMS_FGA package do?

29. What is Cost Based Optimization?

30. How often you should collect statistics for a table?

31. How do you collect statistics for a table, schema and Database?

32. Can you make collection of Statistics for tables automatic?

33. On which columns you should create Indexes?

34. What type of Indexes are available in Oracle?

35. What is B-Tree Index?

36. A table is having few rows, should you create indexes on this table?

37. A Column is having many repeated values which type of index you should create on this column, if you have to?

38. When should you rebuilt indexes?

39. Can you built indexes online?

40. Can you see Execution Plan of a statement.

41. A table is created with the following setting

storage (initial 200k
next 200k
minextents 2
maxextents 100
pctincrease 40)

What will be size of 4th extent?

42. What is DB Buffer Cache Advisor?

43. What is STATSPACK tool?

44. Can you change SHARED_POOL_SIZE online?

45. Can you Redefine a table Online?

46. Can you assign Priority to users?

47. You want users to change their passwords every 2 months. How do you enforce this?

48. How do you delete duplicate rows in a table?

49. What is Automatic Management of Segment Space setting?

50. What is the difference between DELETE and TRUNCATE statements?

51. What is COMPRESS and CONSISTENT setting in EXPORT utility?

52. What is the difference between Direct Path and Convention Path loading?

53. Can you disable and enable Primary key?

54. What is an Index Organized Table?

55. What is a Global Index and Local Index?

56. What is the difference between Range Partitioning and Hash Partitioning?

57. What is difference between Multithreaded/Shared Server and Dedicated Server?

58. Can you import objects from Oracle ver. 7.3 to 9i?

59. How do you move tables from one tablespace to another tablespace?

60. How do see how much space is used and free in a tablespace?

 

• Backup and Recovery Interview Questions

Some of the Common Backup and Recovery Interview Questions for Oracle Database Administrator.  These questions are common for both Senior Oracle DBA or Junior DBA. We have compiled these questions based upon the feedback.

1. Which types of backups you can take in Oracle?

2. A database is running in NOARCHIVELOG mode then which type of backups you can take?

3. Can you take partial backups if the Database is running in NOARCHIVELOG mode?

4. Can you take Online Backups if the the database is running in NOARCHIVELOG mode?

5. How do you bring the database in ARCHIVELOG mode from NOARCHIVELOG mode?

6. You cannot shutdown the database for even some minutes, then in which mode you should run
the database?

7. Where should you place Archive logfiles, in the same disk where DB is or another disk?

8. Can you take online backup of a Control file if yes, how?

9. What is a Logical Backup?

10. Should you take the backup of Logfiles if the database is running in ARCHIVELOG mode?

11. Why do you take tablespaces in Backup mode?

12. What is the advantage of RMAN utility?

13. How RMAN improves backup time?

14. Can you take Offline backups using RMAN?

15. How do you see information about backups in RMAN?

16. What is a Recovery Catalog?

17. Should you place Recovery Catalog in the Same DB?

18. Can you use RMAN without Recovery catalog?

19. Can you take Image Backups using RMAN?

20. Can you use Backupsets created by RMAN with any other utility?

21. Where RMAN keeps information of backups if you are using RMAN without Catalog?

22. You have taken a manual backup of a datafile using o/s. How RMAN will know about it?

23. You want to retain only last 3 backups of datafiles. How do you go for it in RMAN?

24. Which is more efficient Incremental Backups using RMAN or Incremental Export?

25. Can you start and shutdown DB using RMAN?

26. How do you recover from the loss of datafile if the DB is running in NOARCHIVELOG mode?

27. You loss one datafile and it does not contain important objects. The important objects are there in other datafiles which are intact. How do you proceed in this situation?

28. You lost some datafiles and you don’t have any full backup and the database was running in NOARCHIVELOG mode. What you can do now?

29. How do you recover from the loss of datafile if the DB is running in ARCHIVELOG mode?

30. You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week old and backup of only this datafile which is just 1 day old. From which backup should you restore this file?

31. You loss controlfile how do you recover from this?

32. The current logfile gets damaged. What you can do now?

33. What is a Complete Recovery?

34. What is Cancel Based, Time based and Change Based Recovery?

35. Some user has accidentally dropped one table and you realize this after two days. Can you recover this table if the DB is running in ARCHIVELOG mode?

36. Do you have to restore Datafiles manually from backups if you are doing recovery using RMAN?

37. A database is running in ARCHIVELOG mode since last one month. A datafile is added to the database last week. Many objects are created in this datafile. After one week this datafile gets damaged before you can take any backup. Now can you recover this datafile when you don’t have any backups?

38. How do you recover from the loss of a controlfile if you have backup of controlfile?

39. Only some blocks are damaged in a datafile. Can you just recover these blocks if you are using RMAN?

40. Some datafiles were there on a secondary disk and that disk has become damaged and it will take some days to get a new disk. How will you recover from this situation?

41. Have you faced any emergency situation. Tell us how you resolved it?

42. At one time you lost parameter file accidentally and you don’t have any backup. How you will recreate a new parameter file with the parameters set to previous values.

 

Unix related Oracle DBA Interview Questions

In many Oracle DBA interviews  many questions are asked relating to Unix. Here I have collected some common Interviews questions asked to a DBA relating to Unix environment. These questions are mostly asked for senior Oracle DBA positions. We have compiled these questions based upon the feedback we got from many candidates who have attended interviews.

1. How do you see how many instances are running?

2. How do you automate starting and shutting down of databases in Unix?

3. You have written a script to take backups. How do you make it run automatically every week?

4. What is OERR utility?

5. How do you see Virtual Memory Statistics in Linux?

6. How do you see how much hard disk space is free in Linux?

7. What is SAR?

8. What is SHMMAX?

9. Swap partition should be how much the size of RAM?

10. What is DISM in Solaris?

11. How do you see how many memory segments are acquired by Oracle Instances?

12. How do you see which segment belongs to which database instances?

13. What is VMSTAT?

14. How do you set Kernel Parameters in Red Hat Linux, AIX and Solaris?

<p style="box-sizi