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.

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

3. 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 UNQUIESCE → take 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.