1) What is an Oracle Instance?
Database instance is a set of memory structures that manages database file. When an instance is started Oracle database allocate memory area called SGA (System Global Area).
2) What is Control File ?
It is a small binary file which stores the Physical Structure of database. Every Database consists of a Control File.
3) Which file is accessed first when Oracle Database starts?
Order is:
- spfile.ora
- pfile.ora (if spfile.ora not accessed).
4) What does PMON and SMON process do?
PMON (Process Monitor): It is responsible for recovering of process when user process fails. It also, helps in process cleanup.
SMON (System Monitor): It is responsible for the recovery of system after the failure. It also, helps in performing critical tasks as instance recovery and dead transaction recovery etc.
5) Describe Instance recovery?
Oracle database performs instance recovery when due to instance failure database is restarted and performs following actions:
- ABORT shutdown
- FORCE start-up
6) What is Tablespaces?
It allows database admin to define location in the file system where the file respective database objects can be stored.
7) What is Redo Log?
Redo Log is a recovery action which consists of two or more pre-existed files that store all the changes done to the database.
8) Define Checkpoint?
Checkpoint: It is defined as a point where if any modification takes place on database then the modification of that block is made to a memory copy of a block. Eventually when we commit block is not written but Redo Log is therefore, the system will checkpoint your modified blocks to disk.
9) Enlist the types of checkpoint in Oracle.
- Full checkpoint
- Thread checkpoint
- File checkpoint
- Parallel Query checkpoint
- Object checkpoint
- Log switch checkpoint
- Incremental checkpoint
10) Give the name of process that reads and write data in data files.
Oracle Server Process reads the block.
DBWR Process writes into the data files.
11) What is Shared Pool?
Shared pool is a memory area generated at the time of startup time in the RAM heap which is a component of SGA (System global area).
12) Differentiate between PFile and SPFile.
- PFile is a "TEXT FILE" whereas SPFile is "BINARY FILE".
- In PFile we cannot change parameter dynamically while we can in SPFile.
- PFile can be maintained by OS Editor whereas for SPFile we need a Oracle Server.
13) Enlist the various Oracle Database Object.
- Tables
- Tablespaces
- Views
- Indexes
- Synonyms
14) Give the different type of module in Oracle Forms.
- Form module
- Menu module
- Pl/SQL Library module
- Object Library module
15) Describe Stackspack tool of Oracle?
It is a package set of SQL,PL/SQL and SQL*Plus scripts which allows collection, automation, storage and view of performance of data.
16) What is an Oracle index?
An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.
17) 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.
18) What are the components of SGA?
- Library cache
- Data Dictionary cache
- Database Buffer Cache
- Redo log
- Buffer cache
- Shared Pool
19) What is a recovery catalog?
Recovery catalog is an inventory of backup taken by RMAN for the database.The size of the recovery catalog schema depends upon the number of databases monitored by the catalog.
20) What are the different stages of database startup?
Following stages are involved in the startup of database:
- NoMount: Oracle Instance is available based on the parameters defined in SPFile.
- Mount: Based on the Information from parameter control files location in spfile, it opens and reads them and available to next stage.
- Open: Datafiles, redo log files are available to the end users.
21) What are the different methods we can shutdown our database?
Following stages are involved in the startup of database:
- SHUTDOWN
- SHUTDOWN TRANSACTIONAL
- SHUTDOWN IMMEDIATE
- SHUTDOWN ABORT
22) What is ALERT log file?
Alert log file is a log file that records database-wide events which is used for trouble shooting. We can find the Log file in BACKGROUND_DUMP_DEST parameter.
23) What are different types of locks?
Different types of user locks are given as follows:
- UL Lock: Defined with dbms_lock package.
- TX Lock: Acquired once for every transaction. It is a row transaction lock.
- TM Lock: Acquired once for each object, which is being changed. It is a DML lock. The ID1 column identifies the object being modified.
24) What is large object in oracle?
Large objects (LOB’s) are exclusively used to hold large amounts of data. It can hold data in tetra bytes. Different types of LOBs include internal, external, persistent and temporary. Binary LOB’s are typically used to store graphics, video, or audio data.
25) What is a system change number (SCN)?
- SCN is a value that is incremented whenever a dirty read occurs.
- SCN is incremented whenever a deadlock occurs.
- SCN is a value that keeps track of explicit locks.
- SCN is a value that is incremented whenever database changes are made.
