What are the Daily a DBA do ?

• Ensure that previous night's backup is complete and there are no RMAN errors in the backup logs.

• Ensure that any exports which are part of the backup are complete and the dump files compressed.

• Check the alert log for any ORA - TNS - errors.. and also for messages like ‘Checkpoint not complete’ etc.

• Ensure that the CRON job for truncating, saving and renaming alert logs is working - verify the same.

• Ensure that the archive redo log files are compressed and have been deleted. Only files for current and previous day should be present. Also Check the Archive destination status.

• All tablespaces should be less than 95% full.

- Run the coalesce command on all tablespaces to reduce fragmentation.

- Ensure that space in the TEMP tablespace is released and is 100% free at the beginning of the day.

• Enough contiguous free space is available in all tablespaces for objects to extend if required.

• Backup the control file to trace..  so that every day we have an outline of the files and their locations for each database.

• No objects are within 5 extents of the MAXEXTENTS storage parameter.

• All core dumps are deleted from the $CDUMP area.

• All *.trc files are deleted from the $UDUMP area, periodically

• Check the machine for any disks (mount point) 100% full or nearing that value. If a disk has filled up, then use the 'find' command (LINUX) to determine files which have been recently created/modified.

Ensure that all *.dmp files are in their proper locations and large *.dmp files have been compressed.

• Truncate the listener.log file in the $ORACLE_HOME/network/log location if the listener log has increased to a size > than 500 MB. Ensure the space is released, otherwise 'reload' listener.

• Run the 'recently created/modified objects' report to ensure that no un-authorized object creation/modification is taking place.

• Ensure that there are no DBMS_JOBS with the status of ‘failed’ or ‘broken’.  

Also last refresh times of all running jobs should be current.

• Check to ensure that no objects exist in the database with the status 'INVALID'

See Also

Server Maintenance Tasks for a DBA

General Tips to Prepare for an Oracle DBA Job Interview  *HOT*

DBA 1+ year Interview

 See Also : DBA Interview for 2+

Q. What is the difference between process and thread.

Creation of new process requires new resources and Address space whereas the thread can be created

in the same address space of the process which not only saves space and resources but are also easy to

create and delete, and many threads can exists in a process.

Q. What is main purpose of CHECKPOINT?

A Checkpoint is a database event, which synchronizes the data blocks in memory with the datafiles on disk.

A checkpoint has 2 purposes:

-1. to establish data consistency

-2. Enable faster database recovery.

Q. What is PGA ?

PGA - Program Global Area

or the Process Global Area is a memory region that contains

data and control information for a single

server process or a single background process.

The PGA is allocated when a process is created and de-allocated when the process is terminated.

PGA is an area that is used by only one process.

Q. Different type of Database Failures

Statement failure failed SQL is automatically rolled back and an error is returned to user.

User Process failure abnormal disconnect PMON detects and rollsback and releases locks.

User Error (drop table, data) DBA is required to recover data (import or incomplete recovery)

Media Failure Loss or corruption of files DBA needs to apply appropriate recovery.

Instance Failure Abnormal shutdown Instance simply needs restarted, SMON auto

recovers by:

 -Rolling forward changes in the redo log not recorded in the data files before Open of database.

 -Rollbacks can occur after the database is open, when block data is requested.

Q. When should more than one DB writer process be used? How many

should be used?

If the UNIX system being used is capable of asynchronous IO then only one is required,

if the system is not capable of asynchronous IO then up to twice the number of disks used by Oracle number of DB writers should be specified by use of the db_writers initialization parameter

Q.What is the use of the RESUMABLE parameter in EXPORT?

The RESUMABLE parameter allows the export to

1. Suspend if a space allocation issue occurs

2. Wait until the space allocation issue is solved

3. Then resume and therefore not to abort, provided a timeout is set appropriately.

Q. Name three advisory statistics you can collect.

Buffer Cache Advice, Segment Level Statistics, & Timed Statistics.

Q. 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.

Tell me the two methods you could use to determine what DDL changes have been made.

Logminer or Streams

Q. Locally Managed TEMP Tablespace is 100% FULL and there is no space available to

add datafile to increase TEMP tablespace. What can you do that might free up TEMP space?

A.) You can try one of the following to free up TEMP space

1. Issue ALTER TABLESPACE PCTINCREASE 1 followed by

ALTER TABLESPACE PCTINCREASE 0 command;

2. Close some of the idle sessions connected to the database

Interviewer may ask you - - ‘’Do you have something to Ask...?’’

Following are the Questions a DBA can Ask to the Interviewer

1.      What processes do you follow while implementing changes in production?

2.      Beside the DBAs and system administrators, who has access to the "Oracle"

operating system account?

3.      How often is the oracle operating system account password changed?

4.      Are the DBAs co-located with the teams they support? How is capacity planning performed?

Oracle Architecture

These are the basic descriptions of the oracle internals, don’t worry if you don’t grasp or

understood this immediately, most DBA’s take a few years to understand this. And in the real world, your clients or managers usually don’t ask you what’s the meaning of a oracle term. So don’t get discouraged! Would suggest to browse thru this and proceed to Further Tasks (Installing and creating the oracle database), where the fun stuff begins.

Main parts of the Oracle Architecture

1. Memory

- this is what inhabit the RAM on the computer, these structures only exist when the software is running

2. Processes

- exists when the instance is running

3. Files and Structures

- files associated with the database exist all the time, as long as the database is created Memory

                1. SGA(System Global Area)

                - this contains SQL statements and data

                2. Database Buffer Cache

                - contains data that comes from the files on disk, and because accessing data from       disk is slower that accessing from memory, the DB buffer cache main purpose is to           cache the data in memory

                3. Redo Log Buffer

                - stores every SQL statement that changes data

                4. Large Pool

                - this is used by Oracle Recovery Manager(this is the backup/restore tool of oracle)

                - when large pool is not configured it will get memory from the shared pool, which     could result in poor SQL execution Database Physical Structures

Files

1. Redo Log Files

- stores all the information needed to recover lost data

- has the extension .LOG(best practice)

2. Archive Log Files

- stores redo log copies to disk, used to restore a database from almost any type of failure

- has the extension .ARC(best practice)

3. Server and Initialization Files

- this is where the database parameter is configured, like memory size, db/instance name, archiving parameters, processes and many more

- has the extension .ORA(best practice)