Oracle DBA Tutorial01 - Starting up and shutting down Database

About Initialization Parameters and Startup

When you issue the SQL*Plus STARTUP command, the database attempts to read the initialization parameters from an SPFILE in a platform-specific default
location[$ORACLE_HOME/dbs]. If it finds no SPFILE, it searches for a text initialization parameter file.

In the platform-specific default location, Oracle Database locates your initialization parameter file by examining filenames in the following order:
  1. spfile$ORACLE_SID.ora
  2. spfile.ora
  3. init$ORACLE_SID.ora
If you (or the Database Configuration Assistant) created a server parameter file, but you want to override it with a text initialization parameter file, you can specify the PFILE clause of the STARTUP command to identify the initialization parameter file.
STARTUP PFILE = /u01/oracle/dbs/init.ora



Starting Up with a Non-Default Server Parameter File
1. Create a one-line text initialization parameter file that contains only the SPFILE
parameter. The value of the parameter is the non-default server parameter file
location.
For example, create a text initialization parameter file /u01/oracle/dbs/spf_init.ora that contains only the following parameter:
SPFILE = /u01/oracle/dbs/test_spfile.ora

2. Start up the instance pointing to this initialization parameter file.
STARTUP PFILE = /u01/oracle/dbs/spf_init.ora



Steps to Start a oracle database

  1. SQLPLUS /NOLOG
  2. CONNECT username AS SYSDBA
Starting an Instance, and Mounting and Opening a Database
3. STARTUP
Starting an Instance Without Mounting a Database
3. STARTUP NOMOUNT
Starting an Instance and Mounting a Database
You can start an instance and mount a database without opening it for maintenance activity.
3. STARTUP MOUNT
Restricting Access to an Instance at Startup
You can start an instance, and optionally mount and open a database, in restricted mode so that the instance is available only to administrative personnel. when the instance is in restricted mode, a database administrator cannot access the instance remotely through an Oracle Net listener.
      1. STARTUP RESTRICT
Disable restricted mode → ALTER SYSTEM DISABLE RESTRICTED SESSION;


Forcing an Instance to Start
Used in the following scenarios =
1. You cannot shut down the current instance with the SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN TRANSACTIONAL commands.
2. You experience problems when starting an instance.
3. STARTUP FORCE
If an instance is running, STARTUP FORCE shuts it down with mode ABORT before restarting it.

Starting an Instance, Mounting a Database, and Starting Complete Media Recovery
          1. STARTUP OPEN RECOVER



Other Important things to know during oracle restart->
To mount a database to a previously started, but not opened instance, use the SQLstatement ALTER DATABASE with the MOUNT clause as follows →
ALTER DATABASE MOUNT;

Opening a closed database ALTER DATABASE OPEN;

The following statement opens a database in read-only mode →
ALTER DATABASE OPEN READ ONLY;
You can also open a database in read/write mode as follows →
ALTER DATABASE OPEN READ WRITE;


Restricting Access to an Open Database
To place an instance in restricted mode, where only users with administrative privileges can access it, use the SQL statement ALTER SYSTEM with the ENABLE RESTRICTED SESSION clause. After placing an instance in restricted mode, you should consider killing all current user sessions before performing any administrative
tasks. To lift an instance from restricted mode, use ALTER SYSTEM with the DISABLE RESTRICTED SESSION clause.




Shutting Down Oracle Database

Shutting Down with the NORMAL Clause
SHUTDOWN NORMAL

Shutting Down with the IMMEDIATE Clause
Use immediate database shutdown only in the following situations →
To initiate an automated and unattended backup
When a power shutdown is going to occur soon
When the database or one of its applications is functioning irregularly and you cannot contact users to ask them to log off or they are unable to log off
SHUTDOWN IMMEDIATE

  • No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
  • Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name.)
  • Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly rolls back active transactions and disconnects all connected users.

Shutting Down with the TRANSACTIONAL Clause
When you want to perform a planned shutdown of an instance while allowing active transactions to complete first, use the SHUTDOWN command with the TRANSACTIONALclause:
SHUTDOWN TRANSACTIONAL


Shutting Down with the ABORT Clause
You can shut down a database instantaneously by aborting the database instance.
  • You need to shut down the database instantaneously (for example, if you know a power shutdown is going to occur in one minute).
  • You experience problems when starting a database instance.

SHUTDOWN ABORT


Quiescing a Database
Occasionally you might want to put a database in a state that allows only DBA transactions, queries, fetches, or PL/SQL statements. Such a state is referred to as a quiesced state.

ALTER SYSTEM QUIESCE RESTRICTED → take the database in QUIESCE mode

ALTER SYSTEM UNQUIESCEtake the database out from QUIESCE mode

View the Database state
You can query the ACTIVE_STATE column of the V$INSTANCE view to see the current state of an instance.


ALTER SYSTEM SUSPEND statement halts all input and output (I/O) to datafiles (file header and file data) and control files.
ALTER SYSTEM RESUME → statement to resume normal database operations.
The suspend/resume feature is not a suitable substitute for normal shutdown
operations, because copies of a suspended database can contain uncommitted updates.








1 comment :

sathish said...

Nice Article..