表空间脱机
当表空间脱机时,表空间里所有的数据文件将自动脱机;当表空间联机时,数据文件自动联机,无需介质恢复
数据文件脱机,但再联机时需要介质恢复
表空间脱机操作:
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>
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>
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/