看见书上的 alter database recover 语句就试验了一下子create tablespace abc datafile 'h:\abc.dbf' size 2m; ------创建表空间select tablespace_name,status
from dba_tablespaces; ------------显示abc表空间为 online alter tablespace abc offline immediate ;----------设置为脱机状态文档说:
OFFLINE IMMEDIATE
If you specify IMMEDIATE, then Oracle Database does not ensure that tablespace files are available and does not perform a checkpoint. You must perform media recovery on the tablespace before bringing it back online.说需要 media recovery 回复后才能设置为online状态自己只设置了数据库位归档模式 以及 log_archive_dest_1='location=h:\archivelog'alter database recover automatic tablespace abc ;结果显示
第一行出现错误
介质已经开始恢复了.--------------结果就完了.什么也没有.这种情况该怎么办呢???????
from dba_tablespaces; ------------显示abc表空间为 online alter tablespace abc offline immediate ;----------设置为脱机状态文档说:
OFFLINE IMMEDIATE
If you specify IMMEDIATE, then Oracle Database does not ensure that tablespace files are available and does not perform a checkpoint. You must perform media recovery on the tablespace before bringing it back online.说需要 media recovery 回复后才能设置为online状态自己只设置了数据库位归档模式 以及 log_archive_dest_1='location=h:\archivelog'alter database recover automatic tablespace abc ;结果显示
第一行出现错误
介质已经开始恢复了.--------------结果就完了.什么也没有.这种情况该怎么办呢???????
试试看,可以联机吗
alter tablespace abc online;
SQL> select * from v$tablespace where name='TEST'; TS# NAME INC BIG FLA ENC
---------- -------------------------------------------------- --- --- --- ---
9 TEST YES NO YESSQL>
SQL> select name,checkpoint_change#,status from v$datafile where ts#=9;NAME CHECKPOINT_CHANGE# STATUS
-------------------------------------------------- ------------------ -------
/oracle/product/oradata/luobo/test01.dbf 2908328 ONLINESQL> select name,recover,fuzzy,status,checkpoint_change# from v$datafile_header where ts#=9;NAME REC FUZ STATUS CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------- ------------------
/oracle/product/oradata/luobo/test01.dbf NO YES ONLINE 2908328SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 58
Next log sequence to archive 60
Current log sequence 60
SQL>
SQL> alter tablespace test offline;Tablespace altered.SQL> select name,checkpoint_change#,status from v$datafile where ts#=9;NAME CHECKPOINT_CHANGE# STATUS
-------------------------------------------------- ------------------ -------
/oracle/product/oradata/luobo/test01.dbf 2912584 OFFLINESQL> select name,recover,fuzzy,status,checkpoint_change# from v$datafile_header where ts#=9;no rows selectedSQL> alter tablespace test online;Tablespace altered.SQL> select name,checkpoint_change#,status from v$datafile where ts#=9;NAME CHECKPOINT_CHANGE# STATUS
-------------------------------------------------- ------------------ -------
/oracle/product/oradata/luobo/test01.dbf 2912729 ONLINESQL> select name,recover,fuzzy,status,checkpoint_change# from v$datafile_header where ts#=9;NAME REC FUZ STATUS CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------- ------------------
/oracle/product/oradata/luobo/test01.dbf NO YES ONLINE 2912729SQL> alter tablespace test offline immediate;Tablespace altered.SQL> select name,checkpoint_change#,status from v$datafile where ts#=9;NAME CHECKPOINT_CHANGE# STATUS
-------------------------------------------------- ------------------ -------
/oracle/product/oradata/luobo/test01.dbf 2912729 RECOVERSQL> select name,recover,fuzzy,status,checkpoint_change# from v$datafile_header where ts#=9;NAME REC FUZ STATUS CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------- ------------------
/oracle/product/oradata/luobo/test01.dbf YES YES OFFLINE 2912729SQL> alter tablespace test online;
alter tablespace test online
*
ERROR at line 1:
ORA-01113: file 11 needs media recovery
ORA-01110: data file 11: '/oracle/product/oradata/luobo/test01.dbf'
SQL> recover tablespace test;
Media recovery complete.
SQL> select name,checkpoint_change#,status from v$datafile where ts#=9;NAME CHECKPOINT_CHANGE# STATUS
-------------------------------------------------- ------------------ -------
/oracle/product/oradata/luobo/test01.dbf 2912770 OFFLINESQL> select name,recover,fuzzy,status,checkpoint_change# from v$datafile_header where ts#=9;NAME REC FUZ STATUS CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------- ------------------
/oracle/product/oradata/luobo/test01.dbf NO NO OFFLINE 2912770SQL> alter tablespace test online;Tablespace altered.SQL> select name,checkpoint_change#,status from v$datafile where ts#=9;NAME CHECKPOINT_CHANGE# STATUS
-------------------------------------------------- ------------------ -------
/oracle/product/oradata/luobo/test01.dbf 2912890 ONLINESQL> select name,recover,fuzzy,status,checkpoint_change# from v$datafile_header where ts#=9;NAME REC FUZ STATUS CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------- ------------------
/oracle/product/oradata/luobo/test01.dbf NO YES ONLINE 2912890SQL>
上面实验注意观察,每一步操作前后,checkpoint_change#(检查点SCN)的变化,很容易
就可以看出,immediate是没有做检查点的,所以需要recover media。对比默认offline,
通过检查点信息可以看出区别。