Tuesday, June 03, 2014

Taking a Datafile Offline in NOARCHIVELOG Mode

I was reading a colleague’s copy of Oracle RMAN 11g Backup and Recovery by Robert Freeman. It said that you could not take a datafile offline if the database was in NOARCHIVELOG mode. I don’t know much about taking datafiles offline but this seemed reasonable so I gave it a try in Oracle 12.1. First I checked the database’s LOG_MODE: 

SQL> select log_mode from v$database
  2  /
 
LOG_MODE
------------
NOARCHIVELOG
 
SQL>

Then I tried to take a datafile offline but this failed, as the book had said it would: 

SQL> alter database datafile
  2  'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\USERS.DBF'
  3  offline
  4  /
alter database datafile
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media
recovery enabled
 
SQL>
 
I read somewhere in the Oracle documentation that you could do this if the tablespace was READ ONLY. This also seemed reasonable so I tried it too: 

SQL> alter tablespace users read only
  2  /
 
Tablespace altered.
 
SQL> alter database datafile
  2  'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\USERS.DBF'
  3  offline
  4  /
 
Database altered.
 
SQL> 

Of course, once I had done this, I could not change the tablespace to READ WRITE mode again: 

SQL> l
  1* alter tablespace users read write
SQL> /
alter tablespace users read write
*
ERROR at line 1:
ORA-01135: file 6 accessed for DML/query is offline
ORA-01110: data file 6:
'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\USERS.DBF'
 
SQL> 

… until I had brought the datafile ONLINE: 

SQL> alter database datafile
  2  'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\USERS.DBF'
  3  online
  4  /
 
Database altered.
 
SQL> alter tablespace users read write
  2  /
 
Tablespace altered.
 
SQL>

No comments: