Oracle Database : Startup basics (How to start Oracle Database) And Shutdown Basics (How to Shutdown Oracle Database)


 Oracle Database: Startup basics (How to start Oracle Database) 

And 

Shutdown Basics (How to Shutdown Oracle Database)



                           Startup basics (How to start Oracle Database) 

Oracle Database and Instance :

            The Database is a set of physical operating system files. These files actually holds the user data and the metadata (or the data dictionary). Every running Oracle database is associated with (atleast) an Oracle instance. 

        The Instance refers to the set of Oracle background processes or threads and a shared memory area (SGA). An instance can mount and open at most one database in its life. A database may be mounted and opened by one or more instances (using RAC) and the number of instances mounting a single database can fluctuate over time.


Database startup :

            For any normal user to access the database, the instance has to be started up and it should mount and open the corresponding database. We term the entire steps as database startup. In short, database startup includes the following steps:

1. Start an instance.
2. Mount the database.
3. Open the database.

Database startup requires SYSDBA privilege. To start a database :

$ export ORACLE_SID=[SID of the instance]
$ export ORACLE_HOME= [location of ORACLE_HOME]
$ sqlplus / as sysdba
SQL> startup

            

    The above syntax assumes you have a pfile or spfile in the default location ($ORACLE_HOME/dbs). If you are using a non-default parameter file, the startup command is:

$ sqlplus / as sysdba

SQL> startup pfile=[file name and location]


Nomount stage :

When you issue a ‘Startup’ command, this is the first stage. ie, starting up the instance. Here,

– Oracle will read the parameter file (spfile or pfile) in the default location or in the location specified in the startup command.

– It will then allocate the memory area (SGA) and starts the background processes. Together we call it as the instance.

– Please note that no database is associated with the instance at this point. We can start the instance alone using the command:

$ sqlplus / as sysdba
SQL> startup nomount
ORACLE instance started.

Total System Global Area 304807936 bytes
Fixed Size 2221000 bytes
Variable Size 113249336 bytes
Database Buffers 184549376 bytes
Redo Buffers 4788224 bytes

            The instance is now started and the database is in nomount stage. Starting the instance in nomount stage is usually required for database creation or for creating or recovering the controlfiles.


Mount stage :

– In this phase, the instance will mount the database.

– Mounting the instance means associating the started instance with a specified database.

– For this, the instance checks the controlfiles specified under CONTROL_FILES parameter and opens it.

– It then reads the control files to find the names of the data files and the online redo log files that it will attempt to access when opening the database.

– The database is still closed and only the DBA can access it.

– This stage is normally used for maintenance operations like renaming datafiles, enabling and disabling archiving options.

– Adding, dropping, or renaming redo log files is also done in mount stage.

– For performing full database recovery database is opened in mount stage.


To mount a database:
$ sqlplus / as sysdba
SQL> startup mount

To mount a database from nomount stage:
$ sqlplus / as sysdba
SQL> alter database mount

Open stage :
– This is the final stage and here Oracle opens the online datafiles and redolog files.

– If any of the required files are not present, media recovery is required .

– It also ensures the datafiles are consistent. Incase of a normal shutdown, 
    the in-memory changes will be written to disk as part of the shutdown checkpoint.

– But if the instance crashed (or shutdown abort), then Oracle Database performs 
   the instance recovery in the next startup.


To open a database :

$ sqlplus / as sysdba
SQL> startup

To open a database from mount stage:
$ sqlplus / as sysdba
SQL> alter database open

To verify :
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE


                   Shutdown Basics (How to Shutdown Oracle Database)


Oracle Database and Instance :
            The Database is a set of physical operating system files. These files actually holds the user data and the metadata (or the data dictionary). Every running Oracle database is associated with (atleast) an Oracle instance. 

            The Instance refers to the set of Oracle background processes or threads and a shared memory area (SGA). An instance can mount and open at most one database in its life. 

            A database may be mounted and opened by one or more instances (using RAC) and the number of instances mounting a single database can fluctuate over time.


Database Shutdown :
During a database shutdown we close the database and terminates the instance.
Different Modes in Database Shutdow
There are different modes to bring down the database:
1. Shutdown immediate
2. Shutdown transactional
3. Shutdown normal
4. Shutdown abort     
        No user session will be permitted once you issue any of these Shutdown commands.
Shutdown Immediate :
– Oracle Database terminates any executing SQL statements and disconnects users.

– Active transactions are terminated and uncommitted changes are rolled back.

– Oracle then performs a checkpoint and then close the online datafiles.

$ sqlplus / as sysdba
SQL> shutdown immediate
Shutdown Transactional :
– This mode prevents users from starting new transactions, but waits for all current transactions to complete before shutting down.
– Oracle then performs a checkpoint and then close the online datafiles.
$ sqlplus / as sysdba
SQL> shutdown transactional

Shutdown Normal :
– The database waits for all connected users to disconnect before shutting down.

– It waits till all the current transactions end.

– Oracle then performs a checkpoint and then close the online datafiles.

$ sqlplus / as sysdba
SQL> shutdown abort

Different Phases in Database Shutdown :
Close the Database :
– Oracle writes the data in the SGA to the disk, updates the file headers and closes the online 
    datafiles and the redo log files.

– But the database will still be mounted.

Dismount the Database :
– After the database is closed, Oracle Database unmounts the database to disassociate it from 
    the instance.

– After a database is unmounted, Oracle Database closes the control files of the database.

– At this point, the instance remains in memory.

Shutdown the Instance :
     The last step is to remove the shared memory (SGA) and terminate the background processes. Sometimes shutdown does not cleanup the SGA or background process completely. This can cause error during the next startup. In such situation we can force a instance startup.



Location: Visakhapatnam, Andhra Pradesh, India

Comments

Post a Comment

Popular posts from this blog

Oracle Database Server Architecture: Overview

Image
  Memory Structures There are three major structures in Oracle Database server architecture: memory structures, process structures, and storage structures. A basic Oracle database system consists of an Oracle database and a database instance. The database consists of both physical structures and logical structures. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting access to logical storage structures. The instance consists of memory structures and background processes associated with that instance. Every time an instance is started, a shared memory area called the System Global Area (SGA) is allocated and the background processes are started. Processes are jobs that work in the memory of computers. A process is defined as a “thread of control” or a mechanism in an operating system that can run a series of steps. After starting a database instance, the Oracle software associates the instance with ...
Read more

Oracle E-Business Suite (EBS) - Introduction

Image
       Oracle E-Business Suite is one of Oracle Corp.'s major product lines. Also known as Oracle EBS, it is  an integrated set of business applications for automating customer relationship management (CRM), enterprise resource planning (ERP) and supply chain management (SCM) processes within organizations .   Oracle E-business Suite (EBS)  refers to a set of combined business applications that are provided by Oracle. It’s often referred to as, Oracle EBS, Oracle ERP or E-Biz. It consists of multiple Oracle enterprise applications that help businesses with various processes to do daily business operations. These applications focus on AR (Accounts Receivable), AP (Accounts Payable), financials, order entry, HR, supply chain management, enterprise resource planning, customer relationship management and many others.   The product was developed over 2 decades ago incorporating high-level Oracle technologies and industry standards. The various elements...
Read more

Why enterprises must not ignore Azure DevOps Server

                   Why enterprises must not ignore Azure DevOps Server                     Azure DevOps is the successor to Team Foundation Server (TFS) and is said to be its advanced version. It is a complete suite of software development tools that are being used on-premises. Azure DevOps Server integrates with currently integrated development environments (IDEs) and helps teams to develop and deploy cross-functional software. It provides a set of tools and services with which you can easily manage the planning and development of your software projects through testing and deployment.                Azure DevOps practices enable IT departments to augment quality, decrease cycle times, and optimize the use of resources to improve the way software is built, delivered and operated. It increases agility and enables better software d...
Read more