Q. What is difference between database and instance?
A. Database is collection of physical files like datafiles, redo files and controlfiles.
Where as instance is combination of memory structure and background processes which are created when database is started.
Q. What is SGA and its components?
A. SGA is System Global Area is a group of shared memory structures.
Components:
1. Shared Pool – (a) Library Cache: SQL is parsed and validated (b) Data Dictionary Cache: It is the meta data of the database.
2. Database Buffer Cache – Used for processing of data. Works on LRU algorithm.
3. Redo Log Buffer – It holds information about changes made to the database.
4. Java Pool – It is used for the Java related programs.
5. Large Pool – It is used by the backup and recovery operations.
Q. Name some of the background processes and their use?
A. Some of the background processes are:
1. PMON: (Process Monitor) monitors and restarts any failed server process.
2. SMON: (System Monitor) it perform recovery after instance failure.
3. DBWR: (Database Writer) is responsible for writing dirty buffers from the database buffer cache to the datafiles.
4. LGWR: (Log Writer) process is responsible for writing the log buffers to the redolog files.
5. CKPT: (Checkpoint Process) process is responsible for updating file headers in the database datafiles.
Q. What is PGA?
A. The Program Global Area (PGA) is a private memory region that contains the data and control information for a server process.
Only a server process can access the PGA. Oracle Database reads and writes information in the PGA on behalf of the server process.
Q. What is SCN?
A. SCN is System Change Number, it changes whenever the database issues a COMMIT statement.
It is always incremental number.
Q. What is UNDO?
A. UNDO segment is used to hold the un-committed transactions.
Q. What is TEMP?
A. Oracle uses TEMP tablespace in order to store data used while executing database queries.
It is heavily used for table scans, joins and sorting. TEMP tablespace is cleared out once the database session completes.
Q. What is checkpoint?
A. Checkpoints keeps the database buffer cache and the database datafiles synchronized.
Datafile and controlfile headers are updated with latest SCN information.
Q. What are DDL and DML operation?
A. DDL: Data Definition Language, DDL statements are used to create database, schema, constraints, users, tables etc.
DML: Data Manipulation Language, DML statement is used to insert, update or delete the records.
Q. What is global inventory and local inventory?
A. Global inventory is the file which records information of all the Oracle Homes installed on to the server.
Where as local inventory will keep record of own oracle home.
Q. What is Listener/ Listener process?
A. It receives incoming client connection requests to the database server.
Q. What is the default port of LISTENER in Oracle?
A. 1521
Q. Where you will find listener file?
A. In ORACLE HOME network/admin directory by the name listener.ora
Q. What is tns file and where you will find it?
A. tnsnames.ora file is used to map connection information for each database running from that ORACLE Home.
Location is ORACLE HOME network/admin directory.
Q. What is parameter file?
A. Parameter file is used to start the databaase. It is in inside the dbs directory in ORACLE HOME.
It is of 2 types pfile and spfile.
-pfile is human readable.
-spfile is server parameter file which is in binary form.
Q. Database is running and someone deleted both parameter file. What will happen to database?
A. Nothing will happen, database will keep on working as normal.
But it will not start if we stop and try to start it again.
Q. How to create pfile from spfile?
A. create pfile from spfile;
create spfile from pfile;
Q. What are the stages of database startup?
A. Three stages:
NOMOUNT – Parameter file is read and instance is started.
MOUNT – Controlfile is verified at its location.
OPEN – Datafiles are validated and database is opened for read-write operation.
Q. Can I start database in MOUNT state?
A. Yes, startup mount
Q. How to check if archiving is enabled?
A. archive log list
Q. How to enable or disable archiving?
A. First start the database in mount state. Then issue below comman.
alter database archivelog; –> Enable Archiving
alter database noarchivelog; –> Disable Archiving
Now, open the database.
Q. How can we check the database open mode?
A. We can query the open_mode from v$database.
If it is in open mode it will show READ WRITE, if it is in mount state it will show MOUNTED and if database is in nomount state the query will not give any output.
Q. How to check ORA errors in database?
A. Check the database alert log file.
Q. What type of ORA error have you seen?
A. ORA-1555 Snapshot too old. It is for UNDO size.
ORA-0600 errors, we use to open SR for such ORA-0600 errors.
Q. Tablespace size has grown to warning threshold. What will you do?
A. Add space to the tablespace by resizing the datafiles.
Q. How to generate AWR (AUTOMATIC WORKLOAD REPOSITORY) Report?
A. Run awrrpt.sql file from $ORACLE_HOME/rdbms/admin
Q. How to generate ASH (ACTIVE SESSION HISTORY) report?
A. Run ashrpt.sql from $ORACLE_HOME/rdbms/admin
Q. What is restore point?
A. A restore point can be used to flash a table or the database back to the time of creation of the restore point.
Q. What is garunteed restore point?
A. A guaranteed restore point enables to flash the database back deterministically to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET