désert/Oracle/Interview

What is the difference between database and instance

• Oracle database is consists of physical files like datafile, redo logs, tempfile, undo file and controlfiles.
• Whereas Oracle instance is consists of SGA(shared global area) and different background processes.

What is an Oracle index?

• A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space.By default, Oracle creates B-tree indexes.

What are the Common Oracle DBA Tasks?

Explain Oracle Grid Architecture?

The Oracle grid architecture pools large numbers of servers, storage, and networks into a flexible, on-demand computing resource for enterprise computing needs. The grid computing infrastructure continually analyzes demand for resources and adjusts supply accordingly.
For example, you could run different applications on a grid of several linked database servers. When reports are due at the end of the month, the database administrator could automatically provision more servers to that application to handle the increased demand.
Grid computing uses sophisticated workload management that makes it possible for applications to share resources across many servers. Data processing capacity can be added or removed on demand, and resources within a location can be dynamically provisioned. Web services can quickly integrate applications to create new business processes.

== What are the different phases of database startup?

• Startup nomount:In this phase, the database reads the initialization parameters (pfile/spfile).If any invalid parameter defined in the pfile , then it will throw error.
• Startup mount:In this phase the database checks the consistency of control file which records all the physical structures of the datase files.
• Startup open:During this phase the database tries to start in open mode i.e (read,write) for end users. Here it will check the consistency of the the datafiles and redologs . In case of any inconsistency it will try to recover the database from redologs.

What are the different states of a redolog member and its significance

We can find the status of redologs from v$log.

select group#,status from v$log;

Different status of redolog are:

UNUSED– Online redo log has never been used. This is the status of a redo log that was newly added, or just after a RESETLOGS, and not being used yet.
CURRENT– Means this redo log is currently getting written. This implies that the redo log is active.
ACTIVE– Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
CLEARING– Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
CLEARING CURRENT- Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
INACTIVE– Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.

What is Rollback Segment?

• Ans. A Database contains one or more Rollback Segments to temporarily store "undo" information.

What are the different type of Segments ?

1. Data Segment(for storing User Data)
2. Index Segment (for storing index)
3. Rollback Segment and Temporary Segment.

How do you recover the database if you lost one of the controlfile in the database?

How do you recover the database if you lost all of the controlfile in the database?

How can you rebuild an index?

What is SCN (System Change Number) ?

• The system change number (SCN) is an ever-increasing value that uniquely identifies a committed version of the database at a point in time. Every time a user commits a transaction Oracle records a new SCN in redo logs.
• Oracle uses SCNs in control files datafile headers and redo records. Every redo log file has both a log sequence number and low and high SCN. The low SCN records the lowest SCN recorded in the log file while the high SCN records the highest SCN in the log file