Oracle DBA Interview Questions & Answers

DBA JOB Duties

1) check all the Databases are up and running
2) check for any Urgent Tickets with high priority
3) check for allocated storage space
4) check all the backup jobs have executed properly
5) check for the DBSNMP running or not
6) RMAN Backup = Recovery Manager

**Savepoint**

A savepoint is a special mark inside a transaction that allows all commands that are executed after it was established to be rolled back, restoring the transaction state to what it was at the time of the savepoint.

Backup (hot and cold backup)

Hot backup will be done when the Database is Running and Archivelogmode is On.
Coldbackup will be done when the Database is shutdown and Backup Database then Start Database.

ArchiveLog Mode

So, if you wish to avoid the wrath of the CEO and angry end-users, you will want to run Oracle in ARCHIVELOG mode. In ARCHIVELOG mode, the database will make copies of all online redo logs after they are filled. These copies are called archived redo logs. The archived redo logs are created via the ARCH process. The ARCH process copies the archived redo log files to one or more archive log destination directories.

The use of ARCHIVELOG mode requires some configuration of the database. First you must put the database in ARCHIVELOG mode and you must also configure the ARCH process, and prepare the archived redo log destination directories.

There are some down sides to running the database in ARCHIVELOG mode. For example, once an online redo log has been filled, it cannot be reused until it has been archived. If Oracle cannot archive the online redo log (for example, the destination directory for the archived redo logs is filled up), it will switch to the next online redo log and keep working. At the same time, Oracle will continue to try to archive the log file.

**redo log files**

all Database transactions will be written to log files which are called redo log files

which log a history of all changes made to the database. Each redo log file consists of redo records. A redo record, also called a redo entry, holds a group of change-vectors, each of which describes or represents a change made to a single block in the database.

DATAFILES

Control files

The control files of a database store the status of the physical structure of the database. The control file is absolutely crucial to database operation. It contains (but is not limited to) the following types of information:
Database information (RESETLOGS SCN and their time stamp)
Archive log history
Tablespace and datafile records (filenames, datafile checkpoints, read/write status, offline or not)
Redo threads (current online redo log)
Database’s creation date
database name
current archive log mode
Log records (sequence numbers, SCN range in each log)
RMAN catalog
Database block corruption information
Database ID, which is unique to each DB
The location of the control files is specified through the control_files init param.

transactional files
flat files
RMAN BACKUP
oRACLE RAC

archiver
background process in oracle

steps in 2 phase commit

what are psuedo columns in Oracle,

0) OBJECT_ID
00) OBJECT_VALUE, ORA_ROWSCN (ROWS SCAN)
1) ROWNUM
2) CURRVAL
3) NEXTVAL
4) USER
5) SYSDATE

co-related sub query

DBCA – Database Configuration Assistant
Create Database