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 !!
