表空間離線與資料檔案離線的區別

表空間離線

   當表空間離線時，表空間裡所有的資料檔案將自動離線；當表空間聯機時，資料檔案自動聯機，無需介質恢復

 

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>