ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 表空间脱机与数据文件脱机的区别

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

原创 Linux操作系统 作者:denglt 时间:2011-08-24 09:28:22 0 删除 编辑
表空间脱机
   当表空间脱机时,表空间里所有的数据文件将自动脱机;当表空间联机时,数据文件自动联机,无需介质恢复
数据文件脱机,但再联机时需要介质恢复
 
表空间脱机操作:
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-04

  • 博文量
    118
  • 访问量
    722623