Oracle Database Shutdown Stages Or Mode

Hey Folks,

We will see here some basic stuff  about Oracle Database shutdown stages or modes.

Shutting Down the database :

Shut Down the database to make operating system offline backups of all physical structures and to have modified static initialization parameter take effect when restarted.

To shut down an instance, you must connect as SYSOPER or SYSDBA and use the fallowing command :

   SHUTDOWN [NORMAL | TRANSNATIONAL | IMMEDIATE | ABORT ]

    

 

SHUTDOWN OPTIONS: 

During : SHUTDOWN NORMAL or SHUTDOWN TRANSNATIONAL or SHUTDOWN IMMEDIATE 

If you use these shutdown modes options, The Database state is consistent. During the startup Process not any instance recovery required. Consistent Database is also called Clean Database. Below things happens when you use these shutdown options.

• Database buffer cache written to the data files
• Uncommitted changes rolled back 
• Resources released
• On the way to startup No any instance recovery required.

SHUTDOWN NORMAL :

Normal is the default shutdown mode. Normal database shut down proceeds with the fallowing conditions :

• No new connections can be made.
• The Oracle server waits for all users to disconnect before completing the shutdown.
• Database and redo buffers are written to disk.
• Background processes are terminated, and the SGA is removed from memory.
• Oracle Closes and dismounts the database before shutting down the instance.
• The next startup does not require an instance recovery.

 

SHUTDOWN TRANSNATIONAL :

A transnational shutdown prevents clients from losing work. A transnational database shutdown proceeds with the fallowing conditions :

• No Client can start a new transaction on this particular instance 
• A client is disconnected when the client ends the transaction that is in progress.
• When all transactions have finished, a shut down occurs immediately.
• The next start up does not require an instance recovery.

 

SHUTDOWN IMMEDIATE :  

Immediate database shutdown proceeds with the fallowing conditions :

• Current SQL statements being processed by Oracle are not completed.
• The Oracle server does not wait for the users, who are currently connected to the database, to disconnect.
• Oracle rolls back active transactions and disconnects all connected users.
• Oracle closes and dismounts the database before shutting down the instance.
• The next start up does not require an instance recovery.

 

During : SHUTDOWN ABORT or Instance Failure or STARTUP FORCE :

If you use these option during shutdown, the database state is inconsistent and instance recovery is required. Inconsistent database is also called Dirty Database. Below things happens when you use these options :

On the way during shut down :

• Modified buffers are not written to data files 
• Uncommitted changes are not rolled back

On the way to startup :

• Online redo log files used to re-apply changes
• Undo segments used to roll back uncommitted changes
• Resources released

 

SHUTDOWN ABORT :

If the NORMAL and IMMEDIATE shut down option do not work, you can abort the current database instance, Aborting an instance proceeds with the fallowing conditions :

• Current SQL statements being processed by the Oracle server are immediately terminated.
• Oracle does not wait for users currently connected to the database to disconnect.
• Database and Redo buffers are not written to disk.
• Uncommitted transactions are not rolled back.
• The instance is terminated without closing the files.
• The database is not closed or dismounted.
• The next start up requires instance recovery, Which occurs immediately. 

Note : It is not advisable to backup a database that is in an inconsistent state.   

 

Thank You !!

Oracle Database Startup Stages Or Modes

Hey Folks,

Here we will see some basics stuff about Oracle Database Startup and shutdown stages like Open database is in restrict, read-only, open and what happens if we open database in these modes and why these are requires and what basics command we use to do that. Let's start.

 

ALTER DATABASE command : 

• Change the state of the database from nomount to Mount

          ALTER DATABASE ORCL MOUNT;

• Open the database as a read-only/open  database

         ALTER DATABASE ORCL OPEN READ ONLY;

To move the Database from the NOMOUNT to  MOUNT stage or from the MOUNT to  OPEN stage, Use the ALTER Database command.

    ALTER DATABSE  {MOUNT | OPEN };

To Prevent data from being modified by user transaction, the database can be opened in read-only mode.

To Start up an Instance, Use the Fallowing Command :

    ALTER DATABSE OPEN { READ WRITE | READ ONLY };

Where :

• READ WRITE : Opens the database in read/write mode, so that users can generate online redo log file.
• READ ONLY : Restricts Users to read-only  transactions, preventing them from generating online redo log file information.

 Note : Read/Write is the default mode of operation.

Opening a Database in Restricted Mode :

• Use the STARTUP command to restrict access to a database

          STARTUP RESTRICT

• Use the ALTER SYSTEM command to place an instance in restricted mode.

           ALTER SYSTEM ENABLE RESTRICTED SESSION;

 

A restricted Session is useful, For example, When you perform structure maintenance or a database export and import. The database can be started in restricted mode so that  it is available only to users with the RESTRICTION SESSION Privilege.

The Database can also be put in restricted mode ALTER SYSTEM SQL command :

    ALTER SYSTEM [ { ENABLE/DISABLE } RESTRICTED SESSION ] 

Where : 

ENABLE RESTRICTED SESSION : Enables future logins only for users who have the RESTRICTED SESSION privilege

DISABLE RESTRICTED SESSION : Disables RESTRICTED SESSION so that the Users who do not have the privilege can log on also.

Terminate Session :

After placing an instance in restricted mode, you may want to kill all current user sessions before performing administrative tasks. this can be done by the fallowing :

    ALTER SYSTEM KILL SESSION  'Integer1', Integer2';

Where :

Integer1 : Value of the SID column in the V$SESSION view

Integer2 : Value of the SERIAL# column in the V$SESSION view

Note : The session ID and Serial number are used to uniquely identify a session. This Guarantees that the ALTER SYSTEM KILL SESSION command is applied to be the correct session even if the user logs off and a new session uses the same session ID.

 

Effect of  terminating Session :

The ALTER SYSTEM KILL SESSION command causes the background process PMON to perform the fallowing steps upon executions :

• Roll back the user's current transactions
• Release all currently help table or row locks
• Free all resources currently reserved by the users session.

Opening a Database in Read-Only Mode : 

Opening a database in read-only mode enables you to query like an open database But it Protects any potential online data content changes. While opening a database in read-only mode guarantees that datafile and redo log files are not written to, it does not restrict database recovery or operations that change the state of the database without generating redo. For example, you can take datafiles offline or bring them online since these operations do not affect data content.

• Opening a database in Read-Only mode :

          STARTUP MOUNT

          ALTER DATABASE OPEN READ ONLY;

• Can be used to
• Execute queries
• Execute disk sorts using locally managed tablespaces
• Take data files offline and online, but not tablespaces
• Perform recovery of offline data files and tablespaces 

 

A database can be opened as read-only, as long as it is not already open in read/write mode. The feature is especially useful for a standby database to offload query processing from the production database. 

If a query needs to use a temporary tablespace, for example , to do a sort operation, the current user must have a locally managed  tablespace assigned as the default temporary tablespace, otherwise the query fails.

Read-Only mode does not restrict database recovery or operations that change the database state without generating redo data. For Example, in read-only mode :

• Data files can be taken offline and online
• Recovery of offline data files and tablespaces can be performed

Disk writes to other files, such as control files, operating system audit trail, trace files, and alert log files, can continue in Read-Only mode.

Thank You !!

Processing SQL Statements, SQL query Process Mechanism

Hey Folks,

We will take a quick review here about SQL query Processing phases which is used by ORACLE internally when we execute any  SQL statement  in Oracle Database.

Processing SQL Statements :

• Connect to an Instance using :
• User Process 
• Server Process
• The Oracle Server components that are used depend on the type of SQL statement :
• Queries Return Rows
• DML statement log changes
• Commit ensures transaction recovery
• Some Oracle Server components do not participate in SQL statement Processing.

Processing a query  : Below is the different Phases  comes in Picture for Query Execution : 

• Parse : 
• Search for identical statement
• Check syntax, Object names, and Privileges
• Lock Objects used during parse
• Create and Store Executions Plan
• Bind : 
• Obtain values for variables
• Execute : 
• Process SQL statement
• Fetch:
• Return Rows to user process

 

Processing a DML Statement :

• Parse : Same as the parse phase used in processing a Query.
• Bind  : Same as the Bind Phase used for Processing a query
• Execute : Here In Execution phase some internal operations performed by an Oracle :
• If the data and undo blocks are not already in the database buffer cache, the Server Process reads them from the data files into the database buffer cache.
• The Server process places  locks on the rows that are to be modified. The Undo blocks is used to store the before image of the data, So that the DML statements can be rolled back if necessary.
• The Data blocks records the new values of the data.
•  The Server process records the before image to the undo block and updates the data block. Both of these changes are made in database buffer cache. Any changed block in the Database Buffer Cache are marked as Dirty Buffers. That is, Buffer that are not the same as the corresponding blocks on the disk.
• The Processing of a DELETE or INSERT command uses similar steps. The before image for a DELETE contains the column value in deleted row, and the before image of an INSERT contains the row Location information.

 

Processing a DDL statement :

The Execution of DDL (Data Definition Language) statements differs from the execution of  DML ( Data manipulation Language ) statements  and queries , because the success of  a DDL statement requires write access to the data dictionary. For these statements Parsing actually includes parsing, data dictionary lookup, and execution.  Transaction management, Session management ,  and System management  SQL statements are processed using the parse and execute stages. To re-execute them, simply perform another execute.

 

Thank You !!  

Startup Command Behavior, Startup Event Sequence, What happens at every stage during startup, Oracle database Internal Processing of Sequence.

Hey Folks,

We will review here some basic stuff that is Database Startup command behavior and What happens internally in Oracle when we fire the command startup. Let's Start :

Startup Command Behavior : 

• Order of Providences :
• SpfileSID.ora
• Default Spfile
• InitSID.ora
• Default Pfile

• Specified PFILE  can Override the precedence

           STARTUP PFILE = $ORACLE_HOME/dbs/initORCL.ora

• PFILE  Can Indicate to use SPFILE

          SPFILE = $ORACLE_HOME/dbs/initORCL.ora

 

Order Precedence Details :

• When the STARTUP command is used, Then the  spfileSID.ora file on the server side is used to startup the instance.
• If the spfileSID.ora file is not found ,then the default SPFILE on the server side is used to start the instance
• If the default SPFILE not found , then the initSID.ora file on the server side will be used to start the instance.

A Specified PFILE can override the use of the default SPFILE to start instance.

A PFILE can optionally contain a definition to indicate use of an SPFILE. This is the only way to start the instance with an SPFILE in a non default Location.

To start the Database with an SPFILE not in default location  :

 SPFILE =  < Full Path and filename > must be place on PFILE.

Example : In PFILE we can mention as below (For start the instance using SPFILE available at non Default Location.       

  SPFILE  = /U01/oradata/PFILE/spfileSID.ora

Parameters that Should be Specified in the Initialization Parameter File :

  

Parameter	Description
BACKGROUND_DUMP_DEST	Location Where Background Process Trace files are written (LGWR,DBWn, and so on ). Also the location for the alert log file.
COMPATIBLE	Version of the server with which this instance should be compatible
CONTROL_FILES	Name of the control files
DB_CACHE_SIZE	Specifies the size of the cache for standard block size buffers
DB_NAME	Database identifier of Eight 8 Characters or fewer. This is the only Parameter that is required when a new database is created
SHARED_POOL_SIZE	Size in the bytes of the Shared Pool
USER_DUMP_DEST	Location Where user debugging trace file are created on behalf of user process

Note : The Default Values depend on the version of the Oracle Server.

Commonly Modified Parameters :

 

Parameter	Description
IFILE	Name of another parameter file to be embedded within the current parameter file. Up to three levels of nesting is possible.
LOG_BUFFER	Number of bytes allocated to the Redo Log Buffer in the SGA
MAX_DUMP_FILE_SIZE	Maximum Size of trace files, Specified as number of Operating System Blocks
PROCESSES	Maximum Number of operation system processed that can connect Simultaneously
SQL_TRACE	Enables or Disables the SQL trace facility for every USER session.
TIMED_STATISTICS	Enables Or Disables timings in trace files and in monitor screens

Database Startup Stages  and What Action event Occur at every Stage : 

Starting Up a Database : When Starting the Database, You select the state in which it starts. The fallowing scenarios describe different stages of starting up an instance

1. NOMOUNT :

An Instance would be started in the NOMOUNT stage only during Database creation or the re-creation of control files.

Starting an instance includes the fallowing tasks :

• Reading the initialization parameter file from " $ORACLE_HOME/dbs " in the fallowing order.
• First spfileSID.ora
• If not found then, spfile.ora
• If not found then, initSID.ora

      Specifying the PFILE parameter with STARTUP overrides the default behavior.

• Allocating the SGA
• Starting the background processes
• Opening The  alertSID.log and the trace files

The database must be named with DB_NAME parameter either in the initialization parameter file or in the STARTUP command.

Command Used : SQL > startup nomount ; 

2. MOUNT :

Mounting the Database to perform Specific maintenance operations, You start an instance and mount the database but do not open the database.

For Example : The Database must be mounted but not open during the Fallowing Tasks :

• Renaming Data files
• Enabling and Disabling online redo log file archiving options
• Performing full Database recovery

Mounting a database includes the fallowing tasks :

• Associating a database with previously started instance 
• Locating and opening the control files specified in the parameter file
• Reading the control file to obtain the names and status of the data files and online redo log files. However No checks performed to verify the existence of the data files and online redo log files at this time.

Command Used : SQL > startup mount  or

                                     SQL > startup nomount;  and Then SQL > alter database mount;

3. OPEN : 

Normal Database Operation means that an instance is started and the database is mounted and open.  With normal database operation Any valid user can connect to the database and perform typical data access operations.

 Opening the database includes the fallowing tasks :

• Opening the online data files
• Opening the online redo log files

If any of the data files or online redo log files are not present when you attempt to open the database, The Oracle server returns an error.

During The final stage, the Oracle server verifies that all the data files and online redo log files can be opened and checks the consistency of the database. If necessary, The SMON background  Process initiates instance recovery.

 Command : SQL>  startup ;

STARTUP command :

To startup an instance and open the database by using below commands :

SQL> startup;

SQL> startup pfile = $ORACLE_HOME/dbs/initORCL.ora

To Start up an instance, use the fallowing option with STARTUP command when it requires:

STARTUP  [FORCE] [ RESTRICT]  [PFILE=file Path and Name]

[OPEN  [Recover] [ Database]

|MOUNT

|NOMOUNT

Note : This Is the not a complete syntax

Where : 

OPEN : Enables users to access the database. 

MOUNT : Mounts the Database for certain DBA activities but does not provide user access to the database.

NOMOUNT : Creates the SGA and starts up the background processes but does not provide access to the database

PFILE-Parfile : Enables a non-default initialization parameter file to be used to configure the instance.

FORCE : Shuts Down the current Oracle Database instance (if it is running) with SHUTDOWN mode ABORT, before restarting it.

RESTRICT : Enables only users with RESTRICTED SESSION privilege to access the database.

RECOVER : Begins media recovery when the database starts.

 

Automating Database Startup :

ON Unix : Automating Database start up and shut down can be controlled by the entries in a special operating system file. For example, oratab in the /var/opt/oracle directory.

Trouble Shooting :

If any errors are occurs while issuing the STARTUP command then First Issue the SHUTDOWN command and then again try to issue STARTUP command.

Note : STARTUP and SHUTDOWN commands are SQL*plus commands, and not SQL commands. 

Thank You !!