bbed-修改数据文件scn

最新推荐文章于 2024-04-28 16:11:59 发布

c.a.p

最新推荐文章于 2024-04-28 16:11:59 发布

阅读量1.2k

点赞数

分类专栏： bbed 文章标签： sql 数据库 database

本文链接：https://blog.csdn.net/renyanjie123/article/details/115182977

版权

bbed 专栏收录该内容

4 篇文章 1 订阅

订阅专栏

bbed修改数据文件scn分为两种，online数据文件和offline数据文件，当然都是修改scnbase 和rbaseq，offline下还要多修改rbabno和kcvcptim。

一、bbed修改offine数据文件scn

1、准备环境

[oracle@renCAP ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 19 19:09:21 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 RENPDB                         READ WRITE NO
SQL> alter session set container=renpdb;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 RENPDB                         READ WRITE NO

SQL> create tablespace bbed_test_tbs datafile '/data/bbed_test_tbs.dbf' size 100m;

Tablespace created.

SQL> create user bbed_user identified by  oracle default tablespace bbed_tbs;

User created.

SQL> grant dba to bbed_user;

Grant succeeded.

[oracle@renCAP ~]$ sqlplus bbed_user/oracle@RENPDB

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 19 19:20:10 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show user
USER is "BBED_USER"
 
SQL> create table bbed_test_tab (id int,name varchar2(20)) tablespace bbed_test_tbs;

Table created.

SQL>  insert into bbed_test_tab values(1,'beijing');

1 row created.

SQL> insert into bbed_test_tab values(2,'shanghai');

1 row created.

SQL> insert into bbed_test_tab values(3,'hangzhou');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from bbed_test_tab;

        ID NAME
---------- --------------------
         1 beijing
         2 shanghai
         3 hangzhou


SQL> col file_name for a120
SQL> set linesize 200 pages 200;
SQL> 
SQL> select FILE_ID,file_name from dba_data_files;

   FILE_ID FILE_NAME
---------- ------------------------------------------------------------------------------------------------------------------------
         9 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_system_gjltqowo_.dbf
        10 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_sysaux_gjltqp14_.dbf
        11 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_undotbs1_gjltqp16_.dbf
        12 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_users_gjlttzro_.dbf
        14 /data/cap_test.dbf
        15 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn66twm_.dbf
        16 /data/bbed_test_tbs.dbf

7 rows selected.

SQL> alter database datafile 16 offline;

Database altered.

SQL> alter system checkpoint;

System altered.


SQL> SELECT file#,name,status FROM v$datafile ;

     FILE# NAME                                                                             STATUS
---------- -------------------------------------------------------------------------------- -------
         9 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_system_gjltqowo_.dbf   SYSTEM
        10 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_sysaux_gjltqp14_.dbf   ONLINE
        11 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_undotbs1_gjltqp16_.dbf ONLINE
        12 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_users_gjlttzro_.dbf    ONLINE
        14 /data/cap_test.dbf                                                               ONLINE
        15 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn66twm_.dbf ONLINE
        16 /data/bbed_test_tbs.dbf                                                          RECOVER

7 rows selected.

SQL> col name for a80;
SQL> select file#,name,checkpoint_change#,last_change# from v$datafile;

     FILE# NAME                                                                             CHECKPOINT_CHANGE# LAST_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------ ------------
         9 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_system_gjltqowo_.dbf              1596670
        10 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_sysaux_gjltqp14_.dbf              1596670
        11 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_undotbs1_gjltqp16_.dbf            1596670
        12 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_users_gjlttzro_.dbf               1596670
        14 /data/cap_test.dbf                                                                          1596670
        15 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn66twm_.dbf            1596670
        16 /data/bbed_test_tbs.dbf                                                                     1596306      1596614

7 rows selected.

SQL> select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         9            1596670
        10            1596670
        11            1596670
        12            1596670
        14            1596670
        15            1596670
        16            1596306

7 rows selected.
--此时16号数据文件scn明显变小，接下来切换归档，删除归档，做无归档环境

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

[oracle@renCAP ~]$ cd /data/area/REN/archivelog/
[oracle@renCAP archivelog]$ ls
2019_06_20
[oracle@renCAP archivelog]$ rm -rf 2019_06_20/
[oracle@renCAP archivelog]$ 

我们尝试恢复数据文件online，结果可想而知，不可以
SQL>  alter database datafile 16 online;
 alter database datafile 16 online
*
ERROR at line 1:
ORA-01113: file 16 needs media recovery
ORA-01110: data file 16: '/data/bbed_test_tbs.dbf'


SQL> recover datafile 16;
ORA-00279: change 1596306 generated at 06/20/2019 07:00:38 needed for thread 1
ORA-00289: suggestion : /data/area/REN/archivelog/2019_06_20/o1_mf_1_14_gjohx13g_.arc
ORA-00280: change 1596306 for thread 1 is in sequence #14


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/data/area/REN/archivelog/2019_06_20/o1_mf_1_14_gjohx13g_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7


ORA-00308: cannot open archived log '/data/area/REN/archivelog/2019_06_20/o1_mf_1_14_gjohx13g_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7


SQL> alter database datafile 16 online;
alter database datafile 16 online
*
ERROR at line 1:
ORA-01113: file 16 needs media recovery
ORA-01110: data file 16: '/data/bbed_test_tbs.dbf'


SQL> select file#,name,checkpoint_change#,last_change# from v$datafile;

     FILE# NAME                                                                             CHECKPOINT_CHANGE# LAST_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------ ------------
         9 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_system_gjltqowo_.dbf              1596670
        10 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_sysaux_gjltqp14_.dbf              1596670
        11 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_undotbs1_gjltqp16_.dbf            1596670
        12 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_users_gjlttzro_.dbf               1596670
        14 /data/cap_test.dbf                                                                          1596670
        15 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn66twm_.dbf            1596670
        16 /data/bbed_test_tbs.dbf                                                                     1596306      1596614

7 rows selected.

SQL> select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         9            1596670
        10            1596670
        11            1596670
        12            1596670
        14            1596670
        15            1596670
        16            1596306

7 rows selected.

SQL>

测试环境以具备，数据库scn为1596670，数据文件16scn为1596306，在无备份归档情况下（offline时间太长归档丢失）使用bbed修改数据文件16scn至1596670即可。

2、bbed修改数据文件scn

停掉数据库保证scn不会变动（实际上数据库scn不在变动就可以了），数据库启动到mount

SQL> alter session set container=renpdb;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 RENPDB                         MOUNTED
SQL> 
SQL> set lines 200 pages 200

关闭数据库scn会有变动，重新确认scn

SQL> select file#,name,checkpoint_change#,last_change# from v$datafile;

     FILE# NAME                                                                             CHECKPOINT_CHANGE# LAST_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------ ------------
         9 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_system_gjltqowo_.dbf              1606451      1606451
        10 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_sysaux_gjltqp14_.dbf              1606451      1606451
        11 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_undotbs1_gjltqp16_.dbf            1606451      1606451
        12 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_users_gjlttzro_.dbf               1606451      1606451
        14 /data/cap_test.dbf                                                                          1606451      1606451
        15 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn66twm_.dbf            1606451      1606451
        16 /data/bbed_test_tbs.dbf                                                                     1596306      1596614

7 rows selected.

SQL> select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         9            1606451
        10            1606451
        11            1606451
        12            1606451
        14            1606451
        15            1606451
        16            1596306

7 rows selected.

BBED>  p kcvfhckp dba 16,1
struct kcvfhckp, 36 bytes                   @484     
   struct kcvcpscn, 8 bytes                 @484     
      ub4 kscnbas                           @484      0x00185b92
      ub2 kscnwrp                           @488      0x8000
      ub2 kscnwrp2                          @490      0x0000
   ub4 kcvcptim                             @492      0x3c491996
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500     
      struct kcvcprba, 12 bytes             @500     
         ub4 kcrbaseq                       @500      0x0000000e
         ub4 kcrbabno                       @504      0x00000002
         ub2 kcrbabof                       @508      0x0010
   ub1 kcvcpetb[0]                          @512      0x02
   ub1 kcvcpetb[1]                          @513      0x00
   ub1 kcvcpetb[2]                          @514      0x00
   ub1 kcvcpetb[3]                          @515      0x00
   ub1 kcvcpetb[4]                          @516      0x00
   ub1 kcvcpetb[5]                          @517      0x00
   ub1 kcvcpetb[6]                          @518      0x00
   ub1 kcvcpetb[7]                          @519      0x00



BBED>  p kcvfhckp dba 9,1
struct kcvfhckp, 36 bytes                   @484     
   struct kcvcpscn, 8 bytes                 @484     
      ub4 kscnbas                           @484      0x00188333
      ub2 kscnwrp                           @488      0x8000
      ub2 kscnwrp2                          @490      0x0000
   ub4 kcvcptim                             @492      0x3c492732
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500     
      struct kcvcprba, 12 bytes             @500     
         ub4 kcrbaseq                       @500      0x00000011
         ub4 kcrbabno                       @504      0x0000fbbe
         ub2 kcrbabof                       @508      0x0010
   ub1 kcvcpetb[0]                          @512      0x02
   ub1 kcvcpetb[1]                          @513      0x00
   ub1 kcvcpetb[2]                          @514      0x00
   ub1 kcvcpetb[3]                          @515      0x00
   ub1 kcvcpetb[4]                          @516      0x00
   ub1 kcvcpetb[5]                          @517      0x00
   ub1 kcvcpetb[6]                          @518      0x00
   ub1 kcvcpetb[7]                          @519      0x00

从9号文件中可以看到scnbase 0x00188333转换成10进制就是当前的scn

SQL> select to_number('00188333','xxxxxxxx') from dual;

TO_NUMBER('00188333','XXXXXXXX')
--------------------------------
                         1606451

操作平台是Linux，littale字节序，编码倒序。需要修改四处（scnbase<484>、kcvcptim<492>、rbsseq<500>、rbabno<504>）。

BBED> m /x 338318 dba 16,1 offset 484;
 File: /data/bbed_test_tbs.dbf (16)
 Block: 1                Offsets:  484 to  995           Dba:0x04000001
------------------------------------------------------------------------
 33831800 00800000 9619493c 01000000 0e000000 02000000 1000a110 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 02000004 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> m /x 3227493c dba 16,1 offset 492;
 File: /data/bbed_test_tbs.dbf (16)
 Block: 1                Offsets:  492 to 1003           Dba:0x04000001
------------------------------------------------------------------------
 3227493c 01000000 0e000000 02000000 1000a110 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 02000004 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> m /x 11  dba 16,1 offset 500;
 File: /data/bbed_test_tbs.dbf (16)
 Block: 1                Offsets:  500 to 1011           Dba:0x04000001
------------------------------------------------------------------------
 11000000 02000000 1000a110 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 02000004 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 00000000 

 <32 bytes per line>

BBED> m /x befb dba 16,1 offset 504              
 File: /data/bbed_test_tbs.dbf (16)
 Block: 1                Offsets:  504 to 1015           Dba:0x04000001
------------------------------------------------------------------------
 befb0000 1000a110 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 
 02000004 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 00000000 00000000 

 <32 bytes per line>

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

SQL> alter database datafile 16 online;

Database altered.

SQL> recover datafile 16;
Media recovery complete.
SQL> select file#,name,checkpoint_change#,last_change# from v$datafile;

     FILE# NAME                                                                             CHECKPOINT_CHANGE# LAST_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------ ------------
         9 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_system_gjltqowo_.dbf              1606451      1606451
        10 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_sysaux_gjltqp14_.dbf              1606451      1606451
        11 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_undotbs1_gjltqp16_.dbf            1606451      1606451
        12 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_users_gjlttzro_.dbf               1606451      1606451
        14 /data/cap_test.dbf                                                                          1606451      1606451
        15 /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn66twm_.dbf            1606451      1606451
        16 /data/bbed_test_tbs.dbf                                                                     1596306      1596614

7 rows selected.
--bbed修改数据文件头的scn，scn已经正确
SQL>  select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         9            1606451
        10            1606451
        11            1606451
        12            1606451
        14            1606451
        15            1606451
        16            1606451

7 rows selected.

SQL> alter database open;

Database altered.

二、bbed修改online数据文件scn

1、准备环境

首先备份16号数据文件

[oracle@renCAP ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jun 20 08:42:42 2019

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

connected to target database: REN (DBID=3556068171)

RMAN> backup datafile 16 format '/home/oracle/bbed_test_rman.bak';

Starting backup at 20-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=395 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00016 name=/data/bbed_test_tbs.dbf
channel ORA_DISK_1: starting piece 1 at 20-JUN-19
channel ORA_DISK_1: finished piece 1 at 20-JUN-19
piece handle=/home/oracle/bbed_test_rman.bak tag=TAG20190620T084330 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-JUN-19

Starting Control File and SPFILE Autobackup at 20-JUN-19
piece handle=/data/area/REN/autobackup/2019_06_20/o1_mf_s_1011429812_gjooonn7_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-JUN-19

RMAN> exit


Recovery Manager complete.
[oracle@renCAP ~]$ ls
aa  bbed  bbed_test_rman.bak  log.bbd  scripts

SQL> select * from bbed_test_tab;

        ID NAME
---------- --------------------
         1 beijing
         2 shanghai
         3 hangzhou
--其中经历很长时间没有变更此表
SQL> insert into bbed_test_tab values(4,'tianjin'); --seq 23

1 row created.

SQL> commit;

Commit complete.

SQL>  select GROUP#,STATUS,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# from v$log;

    GROUP# STATUS            SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------------- ---------- ------------- ------------
         1 INACTIVE                 22       1610172      1610175
         2 INACTIVE                 21       1610169      1610172
         3 CURRENT                  23       1610175   1.8447E+19

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> 
SQL> select GROUP#,STATUS,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# from v$log;

    GROUP# STATUS            SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------------- ---------- ------------- ------------
         1 INACTIVE                 25       1610214      1610217
         2 INACTIVE                 24       1610211      1610214
         3 CURRENT                  26       1610217   1.8447E+19

SQL> select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_system_gjltqowo_.dbf
/data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_sysaux_gjltqp14_.dbf
/data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_undotbs1_gjltqp16_.dbf
/data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_users_gjlttzro_.dbf
/data/cap_test.dbf
/data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn66twm_.dbf
/data/bbed_test_tbs.dbf

7 rows selected.

SQL> ! rm -rf /data/bbed_test_tbs.dbf

SQL>  ! ls -l /data/area/REN/archivelog/2019_06_20/ 
总用量 42636
-rw-r----- 1 oracle oinstall 39264256 6月  20 08:49 o1_mf_1_17_gjop1o2w_.arc
-rw-r----- 1 oracle oinstall  4336640 6月  20 09:05 o1_mf_1_18_gjopxzr0_.arc
-rw-r----- 1 oracle oinstall     1024 6月  20 09:05 o1_mf_1_19_gjopy0t8_.arc
-rw-r----- 1 oracle oinstall    16896 6月  20 09:05 o1_mf_1_20_gjopy3t5_.arc
-rw-r----- 1 oracle oinstall     1024 6月  20 09:05 o1_mf_1_21_gjopy3xd_.arc
-rw-r----- 1 oracle oinstall     1024 6月  20 09:05 o1_mf_1_22_gjopy8p7_.arc
-rw-r----- 1 oracle oinstall    15872 6月  20 09:05 o1_mf_1_23_gjopzk25_.arc
-rw-r----- 1 oracle oinstall     1024 6月  20 09:05 o1_mf_1_24_gjopzkhf_.arc
-rw-r----- 1 oracle oinstall     2048 6月  20 09:05 o1_mf_1_25_gjopzm9l_.arc
中途所有归档全部丢失
SQL> ! mv /data/area/REN/archivelog/2019_06_20 /data/area/REN/archivelog/20bak     

SQL> ! ls -l /data/area/REN/archivelog/
总用量 4
drwxr-x--- 2 oracle oinstall 4096 6月  20 09:05 20bak

[oracle@renCAP 2019_06_20]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jun 20 08:55:18 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set line 300 pages 1000
SQL> alter session set container=renpdb;

Session altered.

SQL> shutdown abort;
ORA-65020: pluggable database RENPDB already closed


SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 16 - see DBWR trace file
ORA-01110: data file 16: '/data/bbed_test_tbs.dbf'

数据库需要恢复16号文件，从备份恢复数据文件

[oracle@renCAP 2019_06_20]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jun 20 09:08:51 2019

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

connected to target database: REN (DBID=3556068171)

RMAN> restore datafile 16;

Starting restore at 20-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=141 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 00016 to /data/bbed_test_tbs.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/bbed_test_rman.bak
channel ORA_DISK_1: piece handle=/home/oracle/bbed_test_rman.bak tag=TAG20190620T084330
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 20-JUN-19

RMAN> exit


Recovery Manager complete.
[oracle@renCAP 2019_06_20]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jun 20 09:09:12 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter session set container=renpdb;

Session altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 16 needs media recovery
ORA-01110: data file 16: '/data/bbed_test_tbs.dbf'


SQL> recover datafile 16;
ORA-00279: change 1609017 generated at 06/20/2019 08:43:30 needed for thread 1
ORA-00289: suggestion :
/data/area/REN/archivelog/2019_06_20/o1_mf_1_17_gjop1o2w_.arc
ORA-00280: change 1609017 for thread 1 is in sequence #17

需要17号归档，该备份截止到16号归档，没有归档，无法不完全恢复，恢复失败。

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/data/area/REN/archivelog/2019_06_20/o1_mf_1_17_gjop1o2w_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7


ORA-00308: cannot open archived log
'/data/area/REN/archivelog/2019_06_20/o1_mf_1_17_gjop1o2w_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

2、bbed修改数据文件scn

SQL> 
SQL> SELECT file#,checkpoint_change# FROM v$datafile WHERE file#=16;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
        16            1610294

SQL> SELECT file#,checkpoint_change#,resetlogs_change#,checkpoint_count,checkpoint_time FROM v$datafile_header;

     FILE# CHECKPOINT_CHANGE# RESETLOGS_CHANGE# CHECKPOINT_COUNT CHECKPOIN
---------- ------------------ ----------------- ---------------- ---------
         9            1610217           1408558               51 20-JUN-19
        10            1610217           1408558               51 20-JUN-19
        11            1610217           1408558               51 20-JUN-19
        12            1610217           1408558               49 20-JUN-19
        14            1610217           1408558               50 20-JUN-19
        15            1610217           1408558               34 20-JUN-19
        16            1609017           1408558                9 20-JUN-19

7 rows selected.

SQL> SELECT sequence#,resetlogs_change#,first_change#,next_change# FROM v$archived_log WHERE sequence#>12;

 SEQUENCE# RESETLOGS_CHANGE# FIRST_CHANGE# NEXT_CHANGE#
---------- ----------------- ------------- ------------
        13           1408558       1596303      1596306
        14           1408558       1596306      1596664
        15           1408558       1596664      1596667
        16           1408558       1596667      1596670
        17           1408558       1596670      1609216
        18           1408558       1609216      1610163
        19           1408558       1610163      1610166
        20           1408558       1610166      1610169
        21           1408558       1610169      1610172
        22           1408558       1610172      1610175
        23           1408558       1610175      1610211
        24           1408558       1610211      1610214
        25           1408558       1610214      1610217

13 rows selected.

SQL>  select GROUP#,STATUS,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# from v$log;

    GROUP# STATUS            SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------------- ---------- ------------- ------------
         1 INACTIVE                 25       1610214      1610217
         3 CURRENT                  26       1610217   1.8447E+19
         2 INACTIVE                 24       1610211      1610214

从v$datafile_header中可以看到16号数据文件头scn在1609017，该scn值记录在17号归档中，由于17号以后归档全部丢失（包含17号），那么我们使用bbed修改16号文件scn到online redo（seq 26）的FIRST_CHANGE#（1610217）开始值即可，但是记录在23号归档的tianjin值肯定丢失了（没有异议）。

[oracle@renCAP bbed]$ bbed parfile=par.bbed 
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Thu Jun 20 09:21:34 2019

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

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

BBED> set file 16 ,block 1
        FILE#           16
BBED-00202: invalid parameter (,)


BBED> set file 16  block 1
        FILE#           16
        BLOCK#          1

BBED>   p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
   struct kcvcpscn, 8 bytes                 @484     
      ub4 kscnbas                           @484      0x00188d39
      ub2 kscnwrp                           @488      0x8000
      ub2 kscnwrp2                          @490      0x0000
   ub4 kcvcptim                             @492      0x3c4931b2
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500     
      struct kcvcprba, 12 bytes             @500     
         ub4 kcrbaseq                       @500      0x00000011
         ub4 kcrbabno                       @504      0x00012b3f
         ub2 kcrbabof                       @508      0x0010
   ub1 kcvcpetb[0]                          @512      0x02
   ub1 kcvcpetb[1]                          @513      0x00
   ub1 kcvcpetb[2]                          @514      0x00
   ub1 kcvcpetb[3]                          @515      0x00
   ub1 kcvcpetb[4]                          @516      0x00
   ub1 kcvcpetb[5]                          @517      0x00
   ub1 kcvcpetb[6]                          @518      0x00
   ub1 kcvcpetb[7]                          @519      0x00

需要修改两个地方，scnbase和rbaseq

SQL> select to_number('00188d39','xxxxxxxx') from dual;

TO_NUMBER('00188D39','XXXXXXXX')
--------------------------------
                         1609017 --16号文件当前scn

SQL> SELECT to_char(1610217,'xxxxxxxxxxxxx') from dual;

TO_CHAR(161021
--------------
        1891e9  --通过其他数据文件scn转换成16进制，通过bbed改成此值。

SQL> select to_number('00000011','xxxxxxxx') from dual;

TO_NUMBER('00000011','XXXXXXXX')
--------------------------------
                              17  --当前16号文件读取的归档号

SQL> SELECT to_char(26,'xxxxxxxxxxxxx') from dual;

TO_CHAR(26,'XX
--------------
            1a --由于归档全部缺失，使其直接读取最新online redo

BBED> m /x e99118 offset 484
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /data/bbed_test_tbs.dbf (16)
 Block: 1                Offsets:  484 to  995           Dba:0x04000001
------------------------------------------------------------------------
 e9911800 00800000 b231493c 01000000 11000000 3f2b0100 1000d886 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 02000004 307e1500 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> m /x 1a offset 500
 File: /data/bbed_test_tbs.dbf (16)
 Block: 1                Offsets:  500 to 1011           Dba:0x04000001
------------------------------------------------------------------------
 1a000000 3f2b0100 1000d886 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 02000004 307e1500 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> sum apply
Check value for File 16, Block 1:
current = 0x52c3, required = 0x52c3

BBED> verify
DBVERIFY - Verification starting
FILE = /data/bbed_test_tbs.dbf
BLOCK = 1


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0 --校验无坏块
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED


BBED> 

SQL> recover database;
Media recovery complete.
SQL> SELECT file#,checkpoint_change#,resetlogs_change#,checkpoint_count,checkpoint_time FROM v$datafile_header;

     FILE# CHECKPOINT_CHANGE# RESETLOGS_CHANGE# CHECKPOINT_COUNT CHECKPOIN
---------- ------------------ ----------------- ---------------- ---------
         9            1610294           1408558               54 20-JUN-19
        10            1610294           1408558               54 20-JUN-19
        11            1610294           1408558               54 20-JUN-19
        12            1610294           1408558               52 20-JUN-19
        14            1610294           1408558               53 20-JUN-19
        15            1610294           1408558               37 20-JUN-19
        16            1610294           1408558               25 20-JUN-19

7 rows selected.

SQL> alter database open;

Database altered.

恢复完毕

--end

c.a.p

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
bbed-修改数据文件scn

bbed修改数据文件scn分为两种，online数据文件和offline数据文件，当然都是修改scnbase 和rbaseq，offline下还要多修改rbabno和kcvcptim。一、bbed修改offine数据文件scn1、准备环境[oracle@renCAP ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 19 19:09:21 2019Copyright (c) 1982, 2
复制链接

扫一扫