3.2 Altering Database Availability

«« Previous
Next »»

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.
  • Mounting a Database to an Instance
When you 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.

To mount a database to a previously started, but not opened instance, use the SQL statement ALTER DATABASE with the MOUNT clause as follows:
ALTER DATABASE MOUNT;
  • Opening a Closed Database
When a database is mounted but closed, you can make it available for general use by opening it.

To open a mounted database, use the ALTER DATABASE SQL statement with the OPEN clause:
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
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 data files 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 data files 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".

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;

However, read/write is the default mode.

Note: You cannot use the RESETLOGS clause with a READ ONLY clause.

Limitations of a Read-only Database
  1. 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.
  2. 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.
  3. 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.
  4. 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 before it becomes read only.
  • Restricting Access to an Open Database
When a database is in restricted mode, only users with the RESTRICTED SESSION privilege can initiate new connections. Users connecting as SYSDBA or connecting with the DBA role have this privilege.

To place an already running instance in restricted mode:

1. Run the SQL statement ALTER SYSTEM with the ENABLE RESTRICTED SESSION clause.

When you place a running instance in restricted mode, 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.

To lift an instance from restricted mode, use ALTER SYSTEM with the DISABLE RESTRICTED SESSION clause.

«« Previous
Next »»