|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-05
You can alter the availability of a database. You may want to do this in order to restrict access for maintenance reasons or to make the database read only. The following sections explain how to alter the availability of a database:
When you need to perform specific administrative operations, the database must be started and mounted to an instance, but closed. You can achieve this scenario by starting the instance and mounting the database.
ALTER DATABASE MOUNT;
See Also:"Starting an Instance and Mounting a Database" for a list of operations that require the database to be mounted and closed (and procedures to start an instance and mount a database in one step)
ALTER DATABASE OPEN;
After executing this statement, any valid Oracle Database user with the
CREATE SESSION system privilege can connect to the database.
Opening a database in read-only mode enables you to query an open database while eliminating any potential for 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.
If a query against a database in read-only mode uses temporary tablespace, for example to do disk sorts, then the issuer of the query must have a locally managed tablespace assigned as the default temporary tablespace. Otherwise, the query will fail. This is explained in "Creating a Locally Managed Temporary Tablespace".
ALTER DATABASE OPEN READ ONLY;
You can also open a database in read/write mode as follows:
ALTER DATABASE OPEN READ WRITE;
However, read/write is the default mode.
Note:You cannot use the
RESETLOGSclause with a
An application must not write database objects while executing against a read-only database. For example, an application writes database objects when it inserts, deletes, updates, or merges rows in a database table, including a global temporary table. An application writes database objects when it manipulates a database sequence. An application writes database objects when it locks rows, when it runs
EXPLAIN PLAN, or when it executes DDL. Many of the functions and procedures in Oracle-supplied PL/SQL packages, such as
DBMS_SCHEDULER, write database objects. If your application calls any of these functions and procedures, or if it performs any of the preceding operations, your application writes database objects and hence is not read-only.
When executing on a read-only database, you must commit or roll back any in-progress transaction that involves one database link before you use another database link. This is true even if you execute a generic
SELECT statement on the first database link and the transaction is currently read-only.
You cannot compile or recompile PL/SQL stored procedures on a read-only database. To minimize PL/SQL invalidation because of remote procedure calls, use
REMOTE_DEPENDENCIES_MODE=SIGNATURE in any session that does remote procedure calls on a read-only database.
You cannot invoke a remote procedure (even a read-only remote procedure) from a read-only database if the remote procedure has never been called on the database. This limitation applies to remote procedure calls in anonymous PL/SQL blocks and in SQL statements. You can either put the remote procedure call in a stored procedure, or you can invoke the remote procedure in the database prior to it becoming read only.
See Also:Oracle Database SQL Language Reference for more information about the
To place an already running instance in restricted mode, use the SQL statement
ALTER SYSTEM with the
ENABLE RESTRICTED SESSION clause. After this statement successfully completes, only users with the
SESSION privilege can initiate new connections. Users connecting as
SYSDBA or connecting with the
DBA role have this privilege.
Placing a running instance in restricted mode has the following affect on current sessions:
In a single-instance environment without Oracle Restart, no user sessions are terminated or otherwise affected. Therefore, after placing an instance in restricted mode, consider killing (terminating) all current user sessions before performing administrative tasks.
In a single-instance environment with Oracle Restart, any database services that are being managed by Oracle Restart go offline, and any sessions connected to those services are killed (terminated). The standard database service for the instance, named
DB_DOMAIN, does not go offline because it is not managed by Oracle Restart.
In an Oracle Real Application Clusters environment, any database services that are running on the instance and managed by Oracle Clusterware go offline for that instance, and any sessions connected to those services at that instance are killed. The standard database service for the instance (
DB_DOMAIN) does not go offline.
To lift an instance from restricted mode, use
ALTER SYSTEM with the
DISABLE RESTRICTED SESSION clause.