ITPub博客

首页 > 数据库 > Oracle > 有了bbed,丢失归档再也不怕了

有了bbed,丢失归档再也不怕了

原创 Oracle 作者:yezhizi_weixb 时间:2016-03-31 17:44:24 0 删除 编辑

[oracle@localhost pahc]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 31 17:07:36 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        /u01/oracle/app/standbylog
Oldest online log sequence     30
Next log sequence to archive   33
Current log sequence        33
SQL> /

System altered.

SQL> archive log list;
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        /u01/oracle/app/standbylog
Oldest online log sequence     31
Next log sequence to archive   34
Current log sequence        34
SQL> conn scott/scott;                
Connected.
SQL> create table test_arch(seq varchar2(20)) tablespace test_arch;

Table created.

SQL> insert into test_arch values ('sequence 34');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into test_arch values ('sequence 35');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into test_arch values ('sequence 36');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into test_arch values ('sequence 37');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into test_arch values ('sequence 38');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_arch;

SEQ
--------------------
sequence 34
sequence 35
sequence 36
sequence 37
sequence 38

删除35号归档和5号数据库文件
SQL> shutdown immediate;
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/oracle/app/oradata/pahc/test_arch.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size      2251856 bytes
Variable Size    163578800 bytes
Database Buffers    50331648 bytes
Redo Buffers      5169152 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/oracle/app/oradata/pahc/test_arch.dbf'

Recovery Manager complete.
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 31 17:19:06 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PAHC (DBID=1813560051, not open)

RMAN> restore datafile 5;

Starting restore at 31-MAR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/oracle/app/oradata/pahc/test_arch.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/app/fast_recovery_area/HCDBDG/backupset/2016_03_31/o1_mf_nnndf_TAG20160331T170823_chst87lv_.bkp
channel ORA_DISK_1: piece handle=/u01/oracle/app/fast_recovery_area/HCDBDG/backupset/2016_03_31/o1_mf_nnndf_TAG20160331T170823_chst87lv_.bkp tag=TAG20160331T170823
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 31-MAR-16

RMAN>
SQL> recover datafile 5;
ORA-00279: change 1375720 generated at 03/31/2016 17:08:23 needed for thread 1
ORA-00289: suggestion : /u01/oracle/app/arch/1_33_907601094.dbf
ORA-00280: change 1375720 for thread 1 is in sequence #33


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1375753 generated at 03/31/2016 17:08:42 needed for thread 1
ORA-00289: suggestion : /u01/oracle/app/arch/1_34_907601094.dbf
ORA-00280: change 1375753 for thread 1 is in sequence #34


ORA-00279: change 1375850 generated at 03/31/2016 17:12:10 needed for thread 1
ORA-00289: suggestion : /u01/oracle/app/arch/1_35_907601094.dbf
ORA-00280: change 1375850 for thread 1 is in sequence #35


ORA-00308: cannot open archived log '/u01/oracle/app/arch/1_35_907601094.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> select SEQUENCE#,FIRST_CHANGE#,to_char(FIRST_TIME,'yyyymmdd') ,NEXT_CHANGE#,to_char(NEXT_TIME,'yyyymmdd hh24:mi:ss') from v$archived_log where to_char(FIRST_TIME,'yyyymmdd')='20160331' order by 1;

 SEQUENCE# FIRST_CHANGE# TO_CHAR( NEXT_CHANGE# TO_CHAR(NEXT_TIME
---------- ------------- -------- ------------ -----------------
 14  1266942 20160331      1269487 20160331 11:04:41
 15  1269487 20160331      1269490 20160331 11:04:42
 16  1269490 20160331      1269493 20160331 11:04:43
 17  1269493 20160331      1269496 20160331 11:04:44
 18  1269496 20160331      1269499 20160331 11:04:45
 19  1269499 20160331      1269502 20160331 11:04:45
 20  1269502 20160331      1269505 20160331 11:04:45
 21  1269505 20160331      1269508 20160331 11:04:45
 22  1269508 20160331      1269572 20160331 11:07:15
 23  1269572 20160331      1270302 20160331 11:43:19
 24  1270302 20160331      1270334 20160331 11:44:30

 SEQUENCE# FIRST_CHANGE# TO_CHAR( NEXT_CHANGE# TO_CHAR(NEXT_TIME
---------- ------------- -------- ------------ -----------------
 25  1270334 20160331      1270359 20160331 11:45:32
 26  1270359 20160331      1270372 20160331 11:45:59
 27  1270372 20160331      1290688 20160331 11:57:15
 28  1290688 20160331      1311527 20160331 15:20:19
 29  1311527 20160331      1331873 20160331 15:26:20
 30  1331873 20160331      1353514 20160331 16:24:59
 31  1353514 20160331      1374213 20160331 16:45:29
 32  1374213 20160331      1375692 20160331 17:07:45
 33  1375692 20160331      1375753 20160331 17:08:42
 34  1375753 20160331      1375850 20160331 17:12:10
 35  1375850 20160331      1375867 20160331 17:12:52

 SEQUENCE# FIRST_CHANGE# TO_CHAR( NEXT_CHANGE# TO_CHAR(NEXT_TIME
---------- ------------- -------- ------------ -----------------
 36  1375867 20160331      1375875 20160331 17:13:10
 37  1375875 20160331      1375884 20160331 17:13:28
 38  1375884 20160331      1375894 20160331 17:13:46

25 rows selected.

SQL> col name for a50
SQL> select name,checkpoint_change# from v$datafile_header;

NAME         CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/oracle/app/oradata/pahc/system01.dbf        1375894
/u01/oracle/app/oradata/pahc/sysaux01.dbf        1375894
/u01/oracle/app/oradata/pahc/undotbs01.dbf        1375894
/u01/oracle/app/oradata/pahc/users01.dbf        1375894
/u01/oracle/app/oradata/pahc/test_arch.dbf        1375850


查看5号文件的scn和日志序列

SQL> select to_number('14fe6a','XXXXXXXXX') from dual;

TO_NUMBER('14FE6A','XXXXXXXXX')
-------------------------------
   1375850

SQL> select to_char('1375867','XXXXXXXXX') from dual;

TO_CHAR('1
----------
    14FE7B

SQL> select to_number('23','XXXXXXXX') from dual;

TO_NUMBER('23','XXXXXXXX')
--------------------------
   35

SQL> select to_char('36','XXXXXXXXX') from dual;

TO_CHAR('3
----------
 24
SQL> exit

[oracle@localhost lib]$ bbed parfile=parfile.txt
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Thu Mar 31 17:26:58 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set dba 5,1
 DBA             0x01400001 (20971521 5,1)

BBED> set offset 484
 OFFSET          484

BBED> dump
 File: /u01/oracle/app/oradata/pahc/test_arch.dbf (5)
 Block: 1                Offsets:  484 to  995           Dba:0x01400001
------------------------------------------------------------------------
 6afe1400 00000000 ea431e36 01000000 23000000 02000000 00006d0f 02000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 0d000d00 0d000100 00000000 00000000 00000000 02004001 08200e00 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 <32 bytes per line>

BBED> set offset 500
 OFFSET          500

BBED> dump
 File: /u01/oracle/app/oradata/pahc/test_arch.dbf (5)
 Block: 1                Offsets:  500 to 1011           Dba:0x01400001
------------------------------------------------------------------------
 23000000 02000000 00006d0f 02000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 0d000d00 0d000100 00000000 00000000
 00000000 02004001 08200e00 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 <32 bytes per line>

BBED> dump /v count 16
 File: /u01/oracle/app/oradata/pahc/test_arch.dbf (5)
 Block: 1       Offsets:  500 to  515  Dba:0x01400001
-------------------------------------------------------
 23000000 02000000 00006d0f 02000000 l #.........m.....

 <16 bytes per line>

BBED> modify /x 7bfe14 dba 5,1 offset 484
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/app/oradata/pahc/test_arch.dbf (5)
 Block: 1                Offsets:  484 to  499           Dba:0x01400001
------------------------------------------------------------------------
 7bfe1400 00000000 ea431e36 01000000

 <32 bytes per line>

BBED> set offset 500
 OFFSET          500

BBED> modify /x 24 dba 5,1 offset 500
 File: /u01/oracle/app/oradata/pahc/test_arch.dbf (5)
 Block: 1                Offsets:  500 to  515           Dba:0x01400001
------------------------------------------------------------------------
 24000000 02000000 00006d0f 02000000

 <32 bytes per line>

BBED> sum apply
Check value for File 5, Block 1:
current = 0xb3f4, required = 0xb3f4

BBED>
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost pahc]$ sqlplus scott/scott

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 31 17:35:08 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.
SQL> startup force
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size      2251856 bytes
Variable Size    163578800 bytes
Database Buffers    50331648 bytes
Redo Buffers      5169152 bytes
Database mounted.
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u01/oracle/app/oradata/pahc/test_arch.dbf'


SQL> recover datafile 5;
Media recovery complete.
SQL> alter database open;

Database altered.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from test_arch;

SEQ
--------------------
sequence 34
sequence 36
sequence 37
sequence 38

SQL>

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

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

注册时间:2014-02-21

  • 博文量
    9
  • 访问量
    80631