使用flashback快速解決當前日誌檔案丟失

SQL> select flashback_on,force_logging from v$database;

FLASHBACK_ON       FOR
------------------ ---
YES                YES

SQL> show parameter db_recovery_file;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oracle/flash_recovery_area
db_recovery_file_dest_size           big integer 2G
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

SQL> alter database open;

Database altered.

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
     638023

SQL>  alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> conn ctais2/oracle;
Connected.
SQL>  truncate table test;

Table truncated.

SQL>  select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          3   52428800          1 YES INACTIVE                638029 01-APR-09
         3          1          4   52428800          1 NO  CURRENT                 638032 01-APR-09

--破環日誌檔案

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM ARCHIVE LOG CURRENT
*
ERROR at line 1:
ORA-16038: log 3 sequence# 4 cannot be archived
ORA-00312: online log 3 thread 1: '/oracle/oradata/logminer/redo03.log'

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  205520896 bytes
Fixed Size                  1266608 bytes
Variable Size              79694928 bytes
Database Buffers          121634816 bytes
Redo Buffers                2924544 bytes
Database mounted.

SQL> flashback database to scn 638031;  （638032-1）

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> select count(*) from ctais2.test;

  COUNT(*)
----------
      2000

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  205520896 bytes
Fixed Size                  1266608 bytes
Variable Size              79694928 bytes
Database Buffers          121634816 bytes
Redo Buffers                2924544 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-591191/，如需轉載，請註明出處，否則將追究法律責任。