ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle中有备份丢失当前重做日志组的恢复

oracle中有备份丢失当前重做日志组的恢复

原创 Linux操作系统 作者:DBA_oracle_java 时间:2013-09-11 11:13:04 0 删除 编辑

oracle中有备份丢失当前重做日志组的恢复

1.list backup;

RMAN> list backup;

 

 

List of Backup Sets

===================

 

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

230     Incr 0  99.05M     DISK        00:00:35     11-SEP-13     

        BP Key: 230   Status: AVAILABLE  Compressed: NO  Tag: SZSCDB_LEVEL0_BACKUP

        Piece Name: /RECO/rman/szscdb_level0_488_1_825836483.bak

  List of Datafiles in backup set 230

  File LV Type Ckp SCN    Ckp Time  Name

  ---- -- ---- ---------- --------- ----

  6    0  Incr 2563075    11-SEP-13 /ORADATA/szscdb/undotbs02.dbf

  7    0  Incr 2563075    11-SEP-13 /ORADATA/szscdb/example02.dbf

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

231     Incr 0  72.59M     DISK        00:01:12     11-SEP-13     

        BP Key: 231   Status: AVAILABLE  Compressed: NO  Tag: SZSCDB_LEVEL0_BACKUP

        Piece Name: /RECO/rman/szscdb_level0_487_1_825836483.bak

  List of Datafiles in backup set 231

  File LV Type Ckp SCN    Ckp Time  Name

  ---- -- ---- ---------- --------- ----

  4    0  Incr 2563073    11-SEP-13 /ORADATA/szscdb/users01.dbf

  5    0  Incr 2563073    11-SEP-13 /ORADATA/szscdb/example01.dbf

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

232     Incr 0  482.41M    DISK        00:02:15     11-SEP-13     

        BP Key: 232   Status: AVAILABLE  Compressed: NO  Tag: SZSCDB_LEVEL0_BACKUP

        Piece Name: /RECO/rman/szscdb_level0_486_1_825836483.bak

  List of Datafiles in backup set 232

  File LV Type Ckp SCN    Ckp Time  Name

  ---- -- ---- ---------- --------- ----

  2    0  Incr 2563072    11-SEP-13 /ORADATA/szscdb/sysaux01.dbf

  3    0  Incr 2002543    27-AUG-13 /ORADATA/szscdb/corrupt01.dbf

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

233     Incr 0  655.81M    DISK        00:02:41     11-SEP-13     

        BP Key: 233   Status: AVAILABLE  Compressed: NO  Tag: SZSCDB_LEVEL0_BACKUP

        Piece Name: /RECO/rman/szscdb_level0_485_1_825836483.bak

  List of Datafiles in backup set 233

  File LV Type Ckp SCN    Ckp Time  Name

  ---- -- ---- ---------- --------- ----

  1    0  Incr 2563070    11-SEP-13 /ORADATA/szscdb/system01.dbf

 

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

234     52.43M     DISK        00:00:21     11-SEP-13     

        BP Key: 234   Status: AVAILABLE  Compressed: NO  Tag: TAG20130911T070412

        Piece Name: /RECO/rman/szscdb_arc_492_1_825836656.bak

 

  List of Archived Logs in backup set 234

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    30      2511482    10-SEP-13 2512020    10-SEP-13

  1    32      2512132    10-SEP-13 2512878    10-SEP-13

  1    33      2512878    10-SEP-13 2512881    10-SEP-13

  1    34      2512881    10-SEP-13 2512886    10-SEP-13

  1    35      2512886    10-SEP-13 2513113    10-SEP-13

  1    36      2513113    10-SEP-13 2513116    10-SEP-13

  1    37      2513116    10-SEP-13 2513121    10-SEP-13

  1    38      2513121    10-SEP-13 2513126    10-SEP-13

  1    39      2513126    10-SEP-13 2513130    10-SEP-13

  1    40      2513130    10-SEP-13 2513323    10-SEP-13

  1    41      2513323    10-SEP-13 2513326    10-SEP-13

  1    42      2513326    10-SEP-13 2513331    10-SEP-13

  1    43      2513331    10-SEP-13 2513335    10-SEP-13

  1    44      2513335    10-SEP-13 2513339    10-SEP-13

  1    45      2513339    10-SEP-13 2513343    10-SEP-13

  1    46      2513343    10-SEP-13 2513347    10-SEP-13

  1    47      2513347    10-SEP-13 2513351    10-SEP-13

  1    48      2513351    10-SEP-13 2513359    10-SEP-13

  1    49      2513359    10-SEP-13 2513364    10-SEP-13

  1    50      2513364    10-SEP-13 2514058    10-SEP-13

  1    51      2514058    10-SEP-13 2517518    10-SEP-13

 

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

235     281.57M    DISK        00:02:14     11-SEP-13     

        BP Key: 235   Status: AVAILABLE  Compressed: NO  Tag: TAG20130911T070412

        Piece Name: /RECO/rman/szscdb_arc_489_1_825836655.bak

 

  List of Archived Logs in backup set 235

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    1       2441905    10-SEP-13 2442346    10-SEP-13

  1    2       2442346    10-SEP-13 2442349    10-SEP-13

  1    3       2442349    10-SEP-13 2448177    10-SEP-13

  1    4       2448177    10-SEP-13 2453427    10-SEP-13

  1    5       2453427    10-SEP-13 2458931    10-SEP-13

  1    6       2458931    10-SEP-13 2459881    10-SEP-13

  1    7       2459881    10-SEP-13 2459898    10-SEP-13

  1    9       2460121    10-SEP-13 2460141    10-SEP-13

  1    10      2460141    10-SEP-13 2461907    10-SEP-13

  1    11      2461907    10-SEP-13 2467435    10-SEP-13

  1    12      2467435    10-SEP-13 2472936    10-SEP-13

 

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

236     279.20M    DISK        00:02:17     11-SEP-13     

        BP Key: 236   Status: AVAILABLE  Compressed: NO  Tag: TAG20130911T070412

        Piece Name: /RECO/rman/szscdb_arc_490_1_825836656.bak

 

  List of Archived Logs in backup set 236

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    13      2472936    10-SEP-13 2478476    10-SEP-13

  1    14      2478476    10-SEP-13 2479825    10-SEP-13

  1    15      2479825    10-SEP-13 2479899    10-SEP-13

  1    16      2479899    10-SEP-13 2479959    10-SEP-13

  1    17      2479959    10-SEP-13 2480526    10-SEP-13

  1    18      2480526    10-SEP-13 2480809    10-SEP-13

  1    19      2480809    10-SEP-13 2480812    10-SEP-13

  1    20      2480812    10-SEP-13 2480817    10-SEP-13

  1    21      2480817    10-SEP-13 2484569    10-SEP-13

  1    22      2484569    10-SEP-13 2490074    10-SEP-13

 

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

237     793.00K    DISK        00:00:07     11-SEP-13     

        BP Key: 237   Status: AVAILABLE  Compressed: NO  Tag: TAG20130911T070412

        Piece Name: /RECO/rman/szscdb_arc_494_1_825836795.bak

 

  List of Archived Logs in backup set 237

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    1       2537670    11-SEP-13 2539014    11-SEP-13

  1    2       2539014    11-SEP-13 2539017    11-SEP-13

  1    3       2539017    11-SEP-13 2539022    11-SEP-13

 

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

238     625.00K    DISK        00:00:11     11-SEP-13     

        BP Key: 238   Status: AVAILABLE  Compressed: NO  Tag: TAG20130911T070412

        Piece Name: /RECO/rman/szscdb_arc_495_1_825836800.bak

 

  List of Archived Logs in backup set 238

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    1       2539023    11-SEP-13 2539354    11-SEP-13

  1    2       2539354    11-SEP-13 2539357    11-SEP-13

  1    3       2539357    11-SEP-13 2559375    11-SEP-13

  1    4       2559375    11-SEP-13 2559906    11-SEP-13

  1    5       2559906    11-SEP-13 2559910    11-SEP-13

  1    7       2559943    11-SEP-13 2559951    11-SEP-13

  1    8       2559951    11-SEP-13 2560347    11-SEP-13

 

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

239     2.99M      DISK        00:00:10     11-SEP-13     

        BP Key: 239   Status: AVAILABLE  Compressed: NO  Tag: TAG20130911T070412

        Piece Name: /RECO/rman/szscdb_arc_496_1_825836809.bak

 

  List of Archived Logs in backup set 239

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    1       2560347    11-SEP-13 2561482    11-SEP-13

  1    2       2561482    11-SEP-13 2562175    11-SEP-13

  1    3       2562175    11-SEP-13 2563183    11-SEP-13

 

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

240     316.22M    DISK        00:03:00     11-SEP-13     

        BP Key: 240   Status: AVAILABLE  Compressed: NO  Tag: TAG20130911T070412

        Piece Name: /RECO/rman/szscdb_arc_491_1_825836656.bak

 

  List of Archived Logs in backup set 240

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    23      2490074    10-SEP-13 2495259    10-SEP-13

  1    24      2495259    10-SEP-13 2499356    10-SEP-13

  1    25      2499356    10-SEP-13 2499415    10-SEP-13

  1    26      2499415    10-SEP-13 2499469    10-SEP-13

  1    27      2499469    10-SEP-13 2501193    10-SEP-13

  1    28      2501193    10-SEP-13 2505971    10-SEP-13

  1    29      2505971    10-SEP-13 2511482    10-SEP-13

 

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

241     159.12M    DISK        00:01:36     11-SEP-13     

        BP Key: 241   Status: AVAILABLE  Compressed: NO  Tag: TAG20130911T070412

        Piece Name: /RECO/rman/szscdb_arc_493_1_825836750.bak

 

  List of Archived Logs in backup set 241

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    52      2517518    10-SEP-13 2521239    10-SEP-13

  1    53      2521239    10-SEP-13 2526706    10-SEP-13

  1    54      2526706    10-SEP-13 2529258    10-SEP-13

  1    55      2529258    10-SEP-13 2529262    10-SEP-13

  1    56      2529262    10-SEP-13 2529466    10-SEP-13

  1    57      2529466    10-SEP-13 2529537    10-SEP-13

  1    58      2529537    10-SEP-13 2529620    10-SEP-13

  1    59      2529620    10-SEP-13 2529660    10-SEP-13

  1    60      2529660    10-SEP-13 2529667    10-SEP-13

  1    61      2529667    10-SEP-13 2530086    10-SEP-13

  1    62      2530086    10-SEP-13 2530090    10-SEP-13

  1    63      2530090    10-SEP-13 2530096    10-SEP-13

  1    64      2530096    10-SEP-13 2530103    10-SEP-13

  1    65      2530103    10-SEP-13 2530107    10-SEP-13

  1    66      2530107    10-SEP-13 2530111    10-SEP-13

  1    67      2530111    10-SEP-13 2530116    10-SEP-13

  1    68      2530116    10-SEP-13 2530120    10-SEP-13

  1    69      2530120    10-SEP-13 2530125    10-SEP-13

  1    71      2533285    10-SEP-13 2537593    10-SEP-13

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

242     Full    9.52M      DISK        00:00:05     11-SEP-13     

        BP Key: 242   Status: AVAILABLE  Compressed: NO  Tag: TAG20130911T070728

        Piece Name: /RECO/rman/c-2431636630-20130911-03.bak

  SPFILE Included: Modification time: 11-SEP-13

  SPFILE db_unique_name: SZSCPDB

  Control File Included: Ckp SCN: 2563303      Ckp time: 11-SEP-13

 

2.查看归档模式

 

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /RECO/arch/szscdb

Oldest online log sequence     2

Next log sequence to archive   4

Current log sequence           4

 

 

3.查看当前日志组

SQL> SELECT l.GROUP#,l.ARCHIVED,l.STATUS,lf.MEMBER

  2  FROM V$LOG l,V$LOGFILE lf

  3  WHERE l.GROUP#=lf.GROUP#

  4  ORDER BY l.GROUP#;

 

    GROUP# ARC STATUS           MEMBER

---------- --- ---------------- ------------------------------

         1 YES INACTIVE         /ORADATA/szscdb/redo01.log

         1 YES INACTIVE         /ORADATA/szscdb/redo01a.log

         2 NO  CURRENT          /ORADATA/szscdb/redo02a.log

         2 NO  CURRENT          /ORADATA/szscdb/redo02.log

         3 YES INACTIVE         /ORADATA/szscdb/redo03a.log

         3 YES INACTIVE         /ORADATA/szscdb/redo03.log

 

6 rows selected.

 

4.模拟丢失当前日志组所有成员

SQL> !mv /ORADATA/szscdb/redo02* /tmp

 

 

5.关闭数据库

SQL> shutdown abort;

 

6.启动到mount状态

SQL> startup mount;

 

SQL> SELECT l.GROUP#,l.ARCHIVED,l.STATUS,lf.MEMBER

  2  FROM V$LOG l,V$LOGFILE lf

  3  WHERE l.GROUP#=lf.GROUP#

  4  ORDER BY l.GROUP#;

 

    GROUP# ARC STATUS           MEMBER

---------- --- ---------------- ------------------------------

         1 YES INACTIVE        /ORADATA/szscdb/redo01.log

         1 YES INACTIVE        /ORADATA/szscdb/redo01a.log

         2 NO  INACTIVE        /ORADATA/szscdb/redo02a.log

         2 NO  INACTIVE        /ORADATA/szscdb/redo02.log

         3 YES CURRENT         /ORADATA/szscdb/redo03a.log

         3 YES CURRENT         /ORADATA/szscdb/redo03.log

 

6 rows selected.

 

###查询确认丢失的日志为当前组日志

 

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: '/ORADATA/szscdb/redo03.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 3 thread 1: '/ORADATA/szscdb/redo03a.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

 

 

SQL> alter database clear logfile group 3;

alter database clear logfile group 3

*

ERROR at line 1:

ORA-01624: log 3 needed for crash recovery of instance szscdb (thread 1)

ORA-00312: online log 3 thread 1: '/ORADATA/szscdb/redo03a.log'

ORA-00312: online log 3 thread 1: '/ORADATA/szscdb/redo03.log'

 

SQL> !rman target /

 

RMAN> restore database;

 

SQL> recover database until cancel

ORA-00279: change 2563183 generated at 09/11/2013 07:04:12 needed for thread 1

ORA-00289: suggestion : /RECO/arch/szscdb/1_4_825835374.arc

ORA-00280: change 2563183 for thread 1 is in sequence #4

 

 

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

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 open resetlogs;

 

Database altered.

注意,这种情况是一致性的不完全恢复,不需要设置隐含参数。

QQ交流群;300392987

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

请登录后发表评论 登录
全部评论

注册时间:2013-07-02

  • 博文量
    31
  • 访问量
    116900