归档数据库中的不可恢复操作

归档数据库中的不可恢复操作（无重做日志），需要及时发现，及时备份，避免介质恢复时产生逻辑坏块。

规则：
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.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-756996/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-756996/