startup Database

Startup Database

Startup :- To start Oracle services we use "startup" command

Startup Stages :- There are 3 stages of startup 

Nomount=>Mount=>Open

1) startup nomount (Only parameter file will be open)



Note:- After "startup nomount" only "alter database command" is working to change the database stage. Also we can not change the stage from nomount to open , first you have to change stage from nomount to mount then you can change mount to open.

2) startup mount (parameter and control file will be open)



Note :-After mount stage we can open database by "alter database" command.

 3) startup /startup open (database will be open)

 

This command open the database.

shutdown Database

Shutdown Database

Shutdown Database :- To shutdown Oracle database we need to use "shutdown" command

Ways of shutdown database :- There are following three ways to shutdown database 

1) shutdown transnational
2) shutdown abort (recovery will ask at the time of startup)
3) shutdown immediate

1) shutdown transnational  :- It allow to all connected session to complete their session. After complete session, it will not allow to execute any query as well as it will not allow new session as well. 



2) shutdown abort :- This will not allow any query to any session as well as no guarantee to save all sessions transaction.




3) shutdown immediate :- It will not allow any new query to any session but guarantee to save all session transactions.

Oracle Architecture

Oracle Architecture

 Logical/Memory Architecture

Oracle Architecture :- Oracle system divided into two category

                      1) Logical/Memory Architecture (SGA)
                      2) Physical/File Architecture
Logical/Memory Architecture:- This is also known as SGA (System Global Area). Below are the components of memory architecture.

Shared pool:-It contains machine-language code and execution plans for frequently used SQL commands. Below are the components of shared pool
        a) Library Cache :-
        b) Data Dictionary Cache :-
        c) Result Cache :-
        d) UGA (User Global Area)

Database Buffer Cache:-It stores data values which are written later to the data files by the database writer (DBWn).
    a) Active Block :-These are blocks where DBWR processes are writing. 
    b) Dirty Block :- These are blocks whose data need to written in datafiles
    c) Free Block :- These are blocks which are used to write data .

Redo Log Buffer:-It stores a copy of the changed data from user transaction. This data is periodically written to
                 the Redo Log Files by the Log Writer (LGWR).
Large Pool:-It is a work area given for backup and recovery operations.
Java Pool:-It stores the machine-language and execution plans for Java commands used in application programs and database operations
Background Processes :- Below are the main background processes
            a) SMON

System Monitor (SMON) Responsibilities:

• Instance recovery
• Rolls forward changes in redo logs
• Opens database for user access
• Rolls back uncommitted transactions
• Coalesces free space
• Deallocates temporary segments.

               b) PMON            

Process Monitor (PMON) Cleans up after failed processes by:

• Rolling back the transaction

• Releasing locks

• Releasing other resources

• Restarting dead dispatchers

            c) MMON
            d) DBWR  :- Database writer (DBWn) process is used to write data from buffer cache to the data files and from data files to buffer cache.

DBWn writes when:

• Checkpoint occurs

• Dirty buffers reach threshold

• There are no free buffers

• Timeout occurs

• RAC ping request is made

• Tablespace OFFLINE

• Tablespace READ ONLY

• Table DROP or TRUNCATE

• Tablespace BEGIN BACKUP

                       
               e) LGWR :- Log writer (LGWR) process is used to write data from Log buffer cache to redo log files.
 

Log Writer (LGWR) writes:

• At commit

• When 1/3rd full

• When there is 1 MB of redo

• Every 3 seconds

• Before DBWn writes

            f) CKPT

Checkpoint (CKPT) Responsible for:

• Signaling DBWn at checkpoints

• Updating datafile headers with checkpoint information

• Updating control files with checkpoint information

             g) ARCH
Archiver (ARCn)

• Optional background process

• Automatically archives online redo logs when ARCHIVELOG mode is set

• Preserves the record of all changes made to the database

            h) RECO

Physical/File Architecture

Physical/File Architecture:- Below are the components of File Architecture
    1) Parameter File 
        a) spfile (server parameter file) :- This is a default file which is read by system when Oracle database is going start. After reading this file, SGA created on the RAM and system enter intonomount stage. This is binary file that's why we can not read the content of this file. For reading the content of spfile, we need to create pfile from spfile by below SQL statement.

SQL>CREATE PFILE FROM SPFILE;
 
        b) pfile (parameter file) :- This is a text file created from spfile and we can changed the value of parameter defined into file.

    2) Control File:-
    3) Redo Log File
    4) Data File
        a) Permanent datafile
        b) Undo datafile
        c) Temporary datafile
    5) Archive Log File
    6) Password File