1. What is the difference between database and instance

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

2. 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.
3. What do you mean by multiplexing controlfile and how to achieve this.
4. What are the different partitioning techniques in oracle db .
5. What is global and local partitioned index.

6. What are the methods of converting non-partitioned table to partitioned table and explain on this.
7. What is an ACL.

From 11g onwards, to access network packages like utl_mail,utl_http,utl_smtp, we need to have addtional privilege through ACL.

You can check the below link for usage of ACL, for sending mail using utl_mail.

how to send mail using utl_mail in oracle 11g


8. How to make password of an user non-expiry.

Password parameters are defined in profiles. So to make an password non-expiry, First we need to create a profile with password_life_time set to UNLIMITED and assign that profile to the user.(This user will inherit all the password limits of the profile)
9. How to drop a private database link, when you are not the owner.

 

How to drop a private database link From sys

 

10. What happens when we open the database in resetlog mode.

We should reset the online logs, when we do incomplete recovery or recovery through backup control file. When we do open resetlog, a new incarnation number is generated for the database. Incarnation details can be viewed in v$database_incarnation.

 

11. What is SCN in oracle?

SCN Means, system change number. SCN is the logical point in time at which changes are made to a database. Oracle assigns every committed transaction a unique SCN. The database uses these SCNs to query and track the changes. For example, if a transaction inserts a row, then the database records the SCN at which this delete occurred.

 

12. What is buffer cache?

Oracle keeps copies of database blocks in an area of the SGA known as the buffer cache. The cache may hold more than one copy of a block from different points in time, and may contain ‘dirty’ blocks – ie: blocks which have been updated but not yet flushed back to disk.

 

13. What is buffer cache hit ratio?

The buffer cache hit ratio measures how many times a required block was found in memory rather than having to execute a expensive read operation on disk to get the block.

A good buffer cache hit ratio is generally considered when it is > 80.

 

14. What is the TEMPORARY tablespace and its use?

As the name suggests, Temporary tablespaces is used for  tasks such as sort operations for users and sorting during index creation. User cannot  create objects in a temporary tablespace. By definition, the temporary tablespace holds data only for the duration of a user’s session and the temp space is released, once the session is closed.

15. Difference between pfile vs spfile.

 

The pfile is read at instance startup time to get specific instance characteristics. Any changes made the pfile would only take effect when the database is restarted.

The spfile is a server-side initialization parameter file and it permits dynamic changes without requiring you to bring down the instance.

 

16. How to check whether database is using pfile or spfile.

 

1) SQL> SELECT name,value FROM v$parameter WHERE name = ‘spfile’;

NAME VALUE
———- ————————————————–
spfile /fsys1/oracle/product/9.2.0/spfileTEST.ora
2) SQL> show parameter spfile;

 

17. What is the default block size in database? Can we use different block sizes and how?

 

Starting from oracle 10g, the default block size(DB_BLOCK_SIZE) is 8Kb.  In previous versions it was 2Kb.

To use different block size, use the parameter DB_nK_CACHE_SIZE .

For using 2K block size, set db_2k_cache_size=2G in the init pfile and bounce the database.

 

Similarly for using block size of diffent type, below can be used.

DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE

18. What happens when we open the database with resetlog?

We need to open the database using resetlog option after an incomplete database recovery. 

SYNTAX- ALTER DATABASE OPEN RESETLOGS;

This command will do below things in the database.

  1. It recreates the online redologs and reset the log sequence to 1.
  2. Updates all the datafile and online redolog files with new resetlog timestamp and scn.
  3. New incarnation number is generated.

Note – It is always recommended to take a fresh full DB backup after opening the database with resetlog.

19. What is checkpoint and when it happens?

When we do any changes to the database and commit , then modified blocks are not written to datafile directly,Instead they are written to redolog.And checkpoint is the action, when these modified(committed) blockes will be flushed to the datafile. These blocks are also known as dirty blocks.
A checkpoint number is the SCN number, at which all the dirty blocks are written to datafile(disk).
Below events make a checkpoint to occur:

1. When a redo log switch happens.
2. When DBWR writes all dirty buffers from SGA to datafiles.
3. When we execute alter system switch logfile (or) alter system checkpoint;

 

20. What is SCN in oracle?

 

SCN is system change number. Oracle assigns unique number is each committed transaction in the database. SCN value gets incremented with each transaction.SCN information is updated in controlfile.

Below queries to find SCN:

select dbms_flashback.get_system_change_number scn from dual;

select current_scn from v$database;