Total Interview Questions

Describe the following:

a) Oracle Archive log

b) Oracle catalog

 

a) Oracle Archive logs are records which help in putting up the database in event of unforeseen failure or in an event that there is a general need to perform log mining, it is important as it improves the system availability thus improving dependability, one of the features necessary to win a user confidence in system engineering, as it is clear that without this, users will often reject the system in question.

This also helps to save the organization using the system as sometimes data is usually very expensive, even more expensive than the computer system on which it is being handled and the abrupt loss may mean loss of money used in research as well as the time thus having a fall back log is very handy.

In summary, logs are simply captured and stored records.

b) Oracle catalog: These are catalogs comprising of tables and views which helps users to obtain information concerning the entire database such as the metadata as well as other tables and views also available in the database, their attributes and constraints. In these systems (the oracle systems), the available system relation catalog are usually so many that an average user just has an access to a few of them, I mean the available systems relation catalogs runs to the tunes of several hundreds.

 

Describe PCT Free.

This is a block of storage used to determine the amount of space necessary for future updates in the database so that it is left blank; it is done by a process called mentioning the PCTFREE. This is conspicuous in a situation where one record, say, Isaac can in future is updated to Isaac Kumar.

 

If for instance PCTFREE has been mentioned as 10, then oracle will have to add new rows to the block thus it allows 10 for future updates.

 

 

Describe the difference between hot back up and cold backup as well as the benefits associated with each of them.

a) Hot backup: this is the type of backup which takes place while the system is up and running, usually in the archive log mode.

Benefits of the hot backup: it is good owing that one can recover the database at any instance as this kind of backup takes place when the database is still running and up hence higher chances of capturing all instances which occur in the database at any point in time.

Also this system is convenient as the database is still available to the user to access even during the time of update.

b) Cold Backup: This is the type of backup which occurs while the database is shut down and hence does not require being in the archive log mode. It has an advantage in that it is easy to administer, both the backup and the recovery processes. Also the fact that you don’t need to put the database in the archive log mode during the backup is an advantage in its own as during such a situation, usually there is a performance gain since the database is cutting no archive logs to disk

Explain how you would go about to bring up a database in a situation whereby you are restoring from a backup and have no control files at all.

 

The following would be the recommended procedure:

• Create a text based backup control file whereby you stipulate the location of all data files in the disk.
• Issue a recover command using the control file clause.

 

Describe the term, ‘Dump Destination’; also explain in details the meaning of bdump, cdump and udump.

The term dump destination refers to the location in harboring the trace files for all the oracle processes.

 

adump–> for ASM processes

udump–> User Processes dump

bdump–>Background processes + alert_SID.log file location

cdump–> Core Processes dump

Useful information pertinent to system failures are contained in these destinations

 

Name the four performance bottlenecks that are likely to be found in a database server.

• Central processing unit (CPU) bottlenecks
• Incorrect memory structures, also known as the undersized memory structures
• Poor SQL statements,
• Poor database settings

 

Name the four errors that one can find in an alert (error) log which are capable of ruining the appetite of the DBA and also state how one can avoid them.

• Oracle internal errors
• Backup and recovery errors
• Snapshot to old errors
• Deadlock Errors

 

Is it possible for one to perform DML operations when the standby database is in read only in Standby Database?

 

There exist two kinds of standby, namely the logical standby and the physical standby.

Physical standby resembles primary database in terms of the structure.

During read only mode, it is possible to run SQL queries on it, during the other mode, i.e. the redo-apply mode, the database is not accessible to the queries.

 

Sometimes the logical standby has a different structure from that of the primary database and while in read- only mode, the SQL statements which are normally generated from redo are applied and there exist a possibility for queries to run concurrently.

 

During the other mode, i.e. the read-write mode, one is in a position to modify data in tables created in addition to the primary schema. It is worth noting that such settings can be overwritten by specifying further security options.

 

Explain what you will do in an event that a situation arises whereby an error occurs which you are not able to troubleshoot yet there are no seniors or colleagues around.

First, it will be important to find out the particular point in compilation where the error is taking place, then divide the code in to segments, check the correctness of the code line by line in the segment containing the error, actually debug the code, until we find where the problem is arising from and then try and take an appropriate action based on the characteristics of the problem in the code.

 

Assuming you are an Oracle Database Administrator (DBA), name the whole UNIX file you need to understand.

To check the process I would use
ps -ef |grep pmon
ps -ef

To check the alert log file I would use
tail -f alert.log

To check the cpu usage, I would use
top
vmstat 25

How to Convert local management tablespace into a dictionary managed tablespace and vice versa?

To convert dictionary managed tablespace into a local management tablespace, one is expected to do the following:

 

Enter the command:

exec dbms_space_admin.Tablespace_Migrate_to_Local(‘tablespace’);

But to convert the local to dictionary managed tablespace, the following becomes the procedure:

 

Enter the command:

exec dbms_space_admin.Tablespace_Migrate_FROM_Local(‘tablespace’);

State the different scenarios that lead to rebuilding of indexes.

Essentially, there are two scenarios to consider:

• Deleted entries represent more then twenty percent of the current entries.
• The depth of the index has grown more than four levels

 

It is wise noting that oracle index nodes never get physically deleted when the rows of tables are deleted, neither are entries removed from the index yet oracle does the deletion logically leaving behind the so called ‘dead nodes’ in the index tree and these deleted nodes may be handy in event that there is need for another adjacent entry. When large number of tables has been deleted, it is possible that that deleted leaf nodes will make the index full scans for longer periods thus disadvantageous.

 

What should be checked in an event that a database hangs when the developer is attempting to compile the package?

Here, what one checks is the bdump destination.

 

What to do when rectifying a situation a certain rollback segment gets corrupted?

In such a situation, one restores and then recovers the corrupted rollback segment.

 

State one of the ways of transferring a table between different schemas.

Out of the many available methods, one may use the following:

1) Export-Import

2) Create table…as SELECT or COPY

State the importance of the IMPORT IGNORE Option, also state its default setting.

This option tells the import to ignore the errors which already exist in the system. By default, the already existing tables will be skipped, else the error is ignored and the table’s data inserted, usually it has a default value of N.

State what happens in event that the DEFAULT and TEMPORARY tablespace clauses aren’t included in the CREATE USER command.

In such a situation, the user is assigned the SYSTEM tablespace which is the default and a temporary table space, this isn’t advisable since sends the user object and temporary segments to the SYSTEM tablespace leading to fragmentation and poor table placement since only objects for the data dictionary and system rollback segment should be in the SYSTEM.

 

State what transpires when the constraint name is not included in the constraint clause.

This will lead into a situation whereby the oracle system uses a default name like SYS_Cxxxx where xxxx is a number generated by the system. This habit is not advisable as it disguises the process of tracking the tables to which particular constraints belong or even the work of a given constraint.

 

Describe clearly what would transpire in an event that a tablespace clause is omitted from the primary key constraint clause.

The result of the above situation is that one will get an index which is generated automatically being placed in the default tablespace. Definitely, this occurrence has high probability of causing system performance problems since this will usually be the same tablespace for the table which is being created thus a collision.

Owing to the fact that performance of any system is one of the factors that users consider when procuring computer systems, one is highly encouraged to abstain from this habit as it contradicts with the goals of system development.

 

State what happens in event that one disables the primary key constraints and later on enables it without specifying the primary index fully.

In such a situation, the following becomes the outcome:

• All sizing information is lost.
• There is creation of the index in the default tablespace which belongs to the user.

 

It is worth noting that this information is not stored by the oracle as part of the definition of the constraint yet are stored as part of the index definition such that when the constraint is disabled, the index is dropped leading to loss of information.

 

Explain the cause of the error called ‘snapshot too old’ stating its prevention or even ways with which it can be mitigated.

This type of error arises in situations where there is large or transactions running for long having been either wrapped into their pertinent rollback space or have had their rollback space being written on by other transactions. The easiest way to mitigate or prevent this from happening is to employ the divide and rule method! Here one breaks the transactions into smaller units or increases the sizes of segments for rollback as well as there extents

How to identify invalid database object?

Check the status column of the DBE_, ALL or USER_OBJECTS as determined by the kind of permission at your disposal, i.e. whether you have permission on the view or you are using a DBA account.

State the best way of obtaining sizing data to be used in production table implementation assuming one has an example table.

Here, one analyses the table as well as the user data that has been provided in the DBA_TABLES view in order to get pertinent data for calculation including the average row length. There another way – to count the number of blocks in the table actually and ratio the number of blocks in the table to the number of blocks vis-à-vis number of expected rows, though this procedure is considered quick but dirty.

 

How to find out the number of users logged in a database at any particular time with their operating system id?

Many ways lead to this problem’s solution e.g. looking at the v$sesion or the v$process

One can also consider checking the current_login parameters found at the v$sysstat view.

For UNIX users, one can try to do “ps -ef|grep oracle|wc -l? command though this works only against a single instance of installation.

Describe the recommended way to disable and re-enable a primary key constraint.

Here one uses the ALTER TABLE command in both occasions only that when it comes to enable clause, one is expected to specify the USING INDEX and the TABLESPACE clause that are pertained to the primary key. Identify the time when it is necessary that one uses more than one database writer process stating the number that should be used at such times

 

oracle dba interview questions and answers pdf ibm oracle dba interview questions and answers