联机日志丢失或损坏的恢复测试

联机日志丢失或损坏的恢复测试

SQL> select group#,members,archived,status from v$log;

    GROUP#    MEMBERS ARC STATUS
---------- ---------- --- ----------------
         1          1 YES INACTIVE
         2          1 YES INACTIVE
         3          1 NO  CURRENT

SQL> desc v$logfile;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 GROUP#                                             NUMBER
 STATUS                                             VARCHAR2(7)
 TYPE                                               VARCHAR2(7)
 MEMBER                                             VARCHAR2(513)
 IS_RECOVERY_DEST_FILE                              VARCHAR2(3)

SQL> select group#,status,member from v$logfile;

    GROUP# STATUS
---------- -------
MEMBER
--------------------------------------------------------------------------------

         3
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG

         2
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG

         1
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG

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

删除非当前联机日志redo01.log，启动数据库
SQL> startup;
ORACLE instance started.

Total System Global Area  205520896 bytes
Fixed Size                  1289700 bytes
Variable Size             180355612 bytes
Database Buffers           16777216 bytes
Redo Buffers                7098368 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         3 CURRENT
         2 INACTIVE

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database open;

Database altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

SQL> conn hr/hr;
Connected.
SQL> desc test1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 PK                                        NOT NULL NUMBER
 COL1                                               NUMBER
 COL2                                               NUMBER

SQL> insert into test1 values(9,5,5);

1 row created.

SQL> commit;

Commit complete.

SQL> conn / as sysdba;
Connected.
SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

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

删除当前联机日志redo01.log
SQL> startup;
ORACLE instance started.

Total System Global Area  205520896 bytes
Fixed Size                  1289700 bytes
Variable Size             180355612 bytes
Database Buffers           16777216 bytes
Redo Buffers                7098368 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         3 INACTIVE
         2 INACTIVE

SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-00350: log 1 of instance orcl (thread 1) needs to be archived
ORA-00312: online log 1 thread 1:
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'

SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。

SQL> recover database until cancel;
Media recovery complete.
SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CLEARING_CURRENT
         3 INACTIVE
         2 UNUSED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-00350: log 1 of instance orcl (thread 1) needs to be archived
ORA-00312: online log 1 thread 1:
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CLEARING_CURRENT
         3 INACTIVE
         2 UNUSED

SQL> alter database open resetlogs;

Database altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 UNUSED

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 ACTIVE
         3 CURRENT

SQL> conn hr/hr;
Connected.
SQL> select * from test1;

        PK       COL1       COL2
---------- ---------- ----------
         1          1          1
         2          1          1
         3          1          1
         4         21          1
         5         22        102
         9          5          5
        34          5         90

7 rows selected.

数据丢失。

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

转载于:http://blog.itpub.net/36590/viewspace-707958/