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.