歸檔資料庫中的不可恢復操作

歸檔資料庫中的不可恢復操作（無重做日誌），需要及時發現，及時備份，避免介質恢復時產生邏輯壞塊。

規則：
1，如果資料庫force logging，則所有操作有重做日誌；
2，可以使用v$datafile.unrecoverable_change#檢視不可恢復操作scn；如果資料庫為非歸檔模式，該列不更新。

--版本
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

--歸檔模式
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archive/orcl/
Oldest online log sequence     72
Next log sequence to archive   74
Current log sequence           74

SQL> create tablespace test_tbs datafile '/u01/app/oracle/oradata/orcl/test_tbs01.dbf' size 10m;

Tablespace created.

SQL> select d.name,
       unrecoverable_change# urecc,
       to_char(unrecoverable_time, 'YYYYMMDDHH24MISS') urect
  from v$datafile d,v$tablespace t
  where d.TS#=t.TS#
  and t.name='TEST_TBS';  2    3    4    5    6 

NAME                                URECC URECT
------------------------------ ---------- --------------------
/u01/app/oracle/oradata/orcl/t          0
est_tbs01.dbf

SQL> create table test tablespace test_tbs
  2  as
  3  select * from user_objects;

Table created.

SQL> select d.name,
       unrecoverable_change# urecc,
       to_char(unrecoverable_time, 'YYYYMMDDHH24MISS') urect
  from v$datafile d,v$tablespace t
  where d.TS#=t.TS#
  and t.name='TEST_TBS';  2    3    4    5    6 

NAME                                URECC URECT
------------------------------ ---------- --------------------
/u01/app/oracle/oradata/orcl/t          0
est_tbs01.dbf

SQL> insert /*+ append */ into test select * from test;

6648 rows created.

SQL> commit;

Commit complete.

SQL> select d.name,
       unrecoverable_change# urecc,
       to_char(unrecoverable_time, 'YYYYMMDDHH24MISS') urect
  from v$datafile d,v$tablespace t
  where d.TS#=t.TS#
  and t.name='TEST_TBS';  2    3    4    5    6 

NAME                                URECC URECT
------------------------------ ---------- --------------------
/u01/app/oracle/oradata/orcl/t          0
est_tbs01.dbf

SQL> alter table test nologging;

Table altered.

SQL> insert /*+ append */ into test select * from test where rownum<1000;

999 rows created.

SQL> commit;

Commit complete.

SQL> select d.name,
       unrecoverable_change# urecc,
       to_char(unrecoverable_time, 'YYYYMMDDHH24MISS') urect
  from v$datafile d,v$tablespace t
  where d.TS#=t.TS#
  and t.name='TEST_TBS';  2    3    4    5    6 

NAME                                URECC URECT
------------------------------ ---------- --------------------
/u01/app/oracle/oradata/orcl/t          0
est_tbs01.dbf

--為什麼呢
--資料庫當前位force logging：
SQL> select log_mode,force_logging from v$database;

LOG_MODE     FOR
------------ ---
ARCHIVELOG   YES

SQL> alter database no force logging;

Database altered.

SQL> select d.name,
       unrecoverable_change# urecc,
       to_char(unrecoverable_time, 'YYYYMMDDHH24MISS') urect
  from v$datafile d,v$tablespace t
  where d.TS#=t.TS#
  and t.name='TEST_TBS';
  2    3    4    5    6 
NAME                                URECC URECT
------------------------------ ---------- --------------------
/u01/app/oracle/oradata/orcl/t          0
est_tbs01.dbf

SQL> insert /*+ append */ into test select * from test where rownum<1000;

999 rows created.

SQL> select d.name,
       unrecoverable_change# urecc,
       to_char(unrecoverable_time, 'YYYYMMDDHH24MISS') urect
  from v$datafile d,v$tablespace t
  where d.TS#=t.TS#
  and t.name='TEST_TBS';
  2    3    4    5    6 
NAME                                URECC URECT
------------------------------ ---------- --------------------
/u01/app/oracle/oradata/orcl/t     267682 20130325114911
est_tbs01.dbf

SQL> commit;

Commit complete.