We can start an instance in various modes:
1. Start the instance without mounting a database. This does not allow access to the database and usually would be done only for database creation or the re-creation of control files.
2. Start the instance and mount the database, but leave it closed. This state allows for certain DBA activities, but does not allow general access to the database.
3. Start the instance, and mount and open the database. This can be done in unrestricted mode, allowing access to all users, or in restricted mode, allowing access for database administrators only.
Starting an Instance Without Mounting a Database:
You can start an instance without mounting a database. Typically, you do so only during database creation. Use the STARTUP command with the NOMOUNT option:
Use 'STARTUP NOMOUNT' command Start an instance and mount the database.
Example:
C:\Users\>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 31 13:52:36 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: sys/xxxx as sysdba
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 805306368 bytes
Fixed Size 1289996 bytes
Variable Size 213909748 bytes
Database Buffers 587202560 bytes
Redo Buffers 2904064 bytes - Database is in No mount State
To open the DB need to follow the below.
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
--------------------------------------------------------------------------------------------------------------
Starting an Instance and Mounting a Database:
We can start an instance and mount a database without opening it. To perform a specific operations. Please see below
Example : The database must be mounted but not open during the following tasks:-
1. Renaming datafiles
2. Adding, dropping, or renaming redo log files
3. Enabling and disabling redo log archiving options
4. Performing full database recovery
Use 'STARTUP MOUNT' command to Start an instance and mount the database.
Connect as sys user & execute the below
SQL> startup mount
ORACLE instance started.
Total System Global Area 805306368 bytes
Fixed Size 1289996 bytes
Variable Size 213909748 bytes
Database Buffers 587202560 bytes
Redo Buffers 2904064 bytes
Database mounted. - Database Mounted But not opened.
To open the database use below command.
SQL> alter database open;
Database altered.
-----------------------------------------------------------------------------------------------------------------
Restricting Access to a Database at Start up:
We can start an instance and mount and open a database in restricted mode. So that the database is available only to administrative personnel (not general database users).
Open it in restrict mode when You need to accomplish one of the following tasks:
1. Perform an export or import of database data
2. Perform a data load (with SQL*Loader)
3. Temporarily prevent typical users from using data
4. During certain migration and upgrade operations
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP RESTRICT
ORACLE instance started.
Total System Global Area 805306368 bytes
Fixed Size 1289996 bytes
Variable Size 213909748 bytes
Database Buffers 587202560 bytes
Redo Buffers 2904064 bytes
Database mounted.
Database opened. - Database opened in restrict mode.
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ WRITE
SQL> conn hr
Enter password:
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
Warning: You are no longer connected to ORACLE.
-------------------------------------------------------------------------------------------------------------------
NOTE: We can disable restricted session feature, using the below Alter statement.
SQL> conn sys/xxxx as sysdba
Connected.
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
System altered.
SQL> conn hr
Enter password:
Connected.
----------------------------------------------------------------------------------------------
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
System altered.
SQL> conn hr
Enter password:
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
Warning: You are no longer connected to ORACLE.
-------------------------------------------------------------------------------------------------
NOTE: Opening a database in restricted mode allows database access only to users with both the
CREATE SESSION and RESTRICTED SESSION system privilege. Only database administrators should have the RESTRICTED SESSION system privilege.
Example:
SQL> select * from dba_role_privs where grantee='HR';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
HR RESOURCE NO YES
HR CONNECT NO YES
SQL> select * from dba_role_privs where grantee='TEST';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TEST RESOURCE NO YES
TEST TEST_ROLE NO YES
TEST DBA NO YES
TEST CONNECT NO YES
SQL> conn hr
Enter password:
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
Warning: You are no longer connected to ORACLE.
SQL> conn test
Enter password:
Connected.
NOTE: Here TEST schema has DBA privileges. Hence able to login.
---------------------------------------------------------------------------------------------------------
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;
NOTE:You cannot use the RESETLOGS clause with a READ ONLY clause.
Thank You!
Comments
Post a Comment