表空间脱机与数据文件脱机的区别

表空间脱机

   当表空间脱机时，表空间里所有的数据文件将自动脱机；当表空间联机时，数据文件自动联机，无需介质恢复

数据文件脱机，但再联机时需要介质恢复

 

表空间脱机操作：

SQL>
SQL> select file_name, tablespace_name, status, online_status
  2    from dba_data_files
  3   where tablespace_name = 'USERS';
 
FILE_NAME                                                      TABLESPACE_NAME                STATUS    ONLINE_STATUS
-------------------------------------------------------------- ------------------------------ --------- -------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\RMDB\USERS01.DBF              USERS                          AVAILABLE ONLINE
 
SQL> alter tablespace users  offline;
 
Tablespace altered
 
SQL>
SQL> select file_name, tablespace_name, status, online_status
  2    from dba_data_files
  3   where tablespace_name = 'USERS';
 
FILE_NAME                                                      TABLESPACE_NAME                STATUS    ONLINE_STATUS
-------------------------------------------------------------- ------------------------------ --------- -------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\RMDB\USERS01.DBF               USERS              AVAILABLE OFFLINE
 
 
SQL> SELECT * FROM RMAN.DB;
 
SELECT * FROM RMAN.DB
 
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\RMDB\USERS01.DBF'
 
SQL> alter tablespace users online;
 
Tablespace altered
 
SQL>
SQL> select file_name, tablespace_name, status, online_status
  2    from dba_data_files
  3   where tablespace_name = 'USERS';
 
FILE_NAME                                            TABLESPACE_NAME                STATUS    ONLINE_STATUS
---------------------------------------------------- ------------------------------ --------- -------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\RMDB\USERS01.DBF    USERS                          AVAILABLE ONLINE
 
SQL> SELECT * FROM RMAN.DB;
 
    DB_KEY      DB_ID HIGH_CONF_RECID LAST_KCCDIVTS HIGH_IC_RECID CURR_DBINC_KEY
---------- ---------- --------------- ------------- ------------- --------------
         1  766042369                     708796033             2              2
 
SQL>

 

数据文件脱机操作

 

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as SYS
 
 
SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\RMDB\USERS01.DBF' offline
  2  ;
 
Database altered
 
SQL>
SQL> select file_name, tablespace_name, status, online_status
  2    from dba_data_files
  3   where tablespace_name = 'USERS';
 
FILE_NAME                                          TABLESPACE_NAME                STATUS    ONLINE_STATUS
--------------------------------------------------------------------------------- --------- -------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\RMDB\USERS01.DBF  USERS                          AVAILABLE RECOVER
 
SQL> select * from rman.db;
 
select * from rman.db
 
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\RMDB\USERS01.DBF'
 
SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\RMDB\USERS01.DBF' online;
 
alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\RMDB\USERS01.DBF' online
 
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\RMDB\USERS01.DBF'
 
SQL> recover datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\RMDB\USERS01.DBF';
 完成介质恢复。
 
SQL> select file_name, tablespace_name, status, online_status
  2    from dba_data_files
  3   where tablespace_name = 'USERS';
 
FILE_NAME                                          TABLESPACE_NAME                STATUS    ONLINE_STATUS
--------------------------------------------------------------------------------- --------- -------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\RMDB\USERS01.DBF  USERS                          AVAILABLE OFFLINE
 
SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\RMDB\USERS01.DBF' online;
 
Database altered
 
SQL>

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/195110/viewspace-705665/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/195110/viewspace-705665/