Tuesday, October 16, 2012

How to Open a Database in READ ONLY Mode

This was tested on Oracle 11.2.0.2.7. You can open a database in READ ONLY mode as follows: 

SQL> startup open read only;
ORACLE instance started.
 
Total System Global Area  522092544 bytes
Fixed Size                  2159904 bytes
Variable Size             314575584 bytes
Database Buffers          197132288 bytes
Redo Buffers                8224768 bytes
Database mounted.
Database opened.
SQL>
 
You can check how a database was opened like this:
 
SQL> select open_mode from v$database;
 
OPEN_MODE
--------------------
READ ONLY
 
SQL>
 
When a database is open in READ ONLY mode, you cannot create tables in it:
 
SQL> create table andrews_table (col1 number);
create table andrews_table (col1 number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
 
SQL>
 
You cannot update tables either:
 
SQL> insert into another_table
  2  select * from another_table;
insert into another_table
            *
ERROR at line 1:
ORA-16000: database open for read-only access
 
SQL> update another_table
  2  set col1 = 2;
update another_table
       *
ERROR at line 1:
ORA-16000: database open for read-only access
 
SQL> delete another_table;
delete another_table
       *
ERROR at line 1:
ORA-16000: database open for read-only access
 
SQL>
 
You cannot even drop a table. I wanted to try this out because, strangely enough, you CAN drop tables from READ ONLY tablespaces:
 
SQL> drop table another_table;
drop table another_table
*
ERROR at line 1:
ORA-16000: database open for read-only access
 
SQL>
 
Bouncing the database returns it to READ WRITE mode:
 
SQL> startup force
ORACLE instance started.
 
Total System Global Area  522092544 bytes
Fixed Size                  2159904 bytes
Variable Size             314575584 bytes
Database Buffers          197132288 bytes
Redo Buffers                8224768 bytes
Database mounted.
Database opened.
SQL> select open_mode from v$database;
 
OPEN_MODE
--------------------
READ WRITE
 
SQL>

No comments: