Cannot alter database datafile n offline immediate

From: Dino Hsu <dino1.nospam_at_ms1.hinet.net>
Date: Wed, 29 Aug 2001 22:15:17 +0800
Message-ID: <qitpot8c70oj4lgp6ce2v2n34mj32rnno6@4ax.com>

Dear all,

According to Jason Couchman, when recovering a datafile or a tablespace, we have to get them offline 'immediate' to prevent Oracle from writing sequence# to the corrupt or missing datafiles. I tried them:

SQL> alter database datafile 3 offline immediate; alter database datafile 3 offline immediate

                                  *

ERROR at line 1:
ORA-00933: SQL command not properly ended

SQL> alter tablespace users offline immediate;

Tablespace altered.

In other words, with 'immediate', I can only alter the tablespace, but not the datafile. Anyone knows why?

Furthermore, after I take something offline, I have to do recover them before I can take them back online:

SQL> alter tablespace users offline immediate;

Tablespace altered.

SQL> alter tablespace users online;
alter tablespace users online
*

ERROR at line 1:
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: 'D:\ORACLE\ORADATA\O8I_TEST\USERS01.DBF'

SQL> recover automatic tablespace users; Media recovery complete.
SQL> alter tablespace users online;

Tablespace altered.

SQL> alter database datafile 3 offline;

Database altered.

SQL> alter database datafile 3 online;
alter database datafile 3 online
*

ERROR at line 1:
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: 'D:\ORACLE\ORADATA\O8I_TEST\USERS01.DBF'

SQL> recover automatic datafile 3;
Media recovery complete.
SQL> alter database datafile 3 online;

Database altered.

I didn't do anything before I try to bring them back online, why should I do any reocvery? Thanks in advance.

Dino Received on Wed Aug 29 2001 - 09:15:17 CDT