Category Archives: Oracle备份恢复

impdp报ORA-00904: “ORIGINAL_OBJECT_NAME”: invalid identifier

Posted on by

发现问题
impdp导入数据库不成功,一直在报ORA-00904: “ORIGINAL_OBJECT_NAME”: invalid identifier错误

[oracle@back1 backup]$ impdp username/password  schemas=center_admin dumpfile=center_admin20120427.dmp 
> logfile=center_admin20120427.log directory=impdir parallel=10 job_name=center_admin08;

Import: Release 11.1.0.6.0 - 64bit Production on Friday, 27 April, 2012 21:35:06

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-31694: master table "USERNAME"."CENTER_ADMIN08" failed to load/unload
ORA-02354: error in exporting/importing data
ORA-02373: Error parsing insert statement for table "USERNAME"."CENTER_ADMIN08".
ORA-00904: "ORIGINAL_OBJECT_NAME": invalid identifier

分析问题
看到这个错误,我第一个感觉根据ORA-31694,怀疑是没有创建相关用户,或者是该用户无权限权限CENTER_ADMIN08表.等我登陆目标数据库查看时候发现该用户存在,并且已经授予了DBA权限,所以不存在是用户相关问题导致.ORA-02354错误我怀疑是expdp导出来的文件在传输过程中发生意外(如使用ftp传输未使用二进制模式),当我使用md5sum命令检查发现两边一致,证明该文件传输正常.目标端不能检查明显故障,怀疑导出文件本身存在问题检查导出文件日志

[oracle@fcdb2 backup]$ more center_admin20120427.log
;;; 
Export: Release 11.1.0.7.0 - 64bit Production on Friday, 27 April, 2012 17:32:30

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "USERNAME"."CENTER_ADMIN08":USERNAME/**** schemas=center_admin directory=expdir dumpfile=center_admin20120427.dmp
er_admin20120427.log parallel=10 job_name=center_admin08 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 158.2 GB

发现新大陆,两边的数据库版本不一致,源端版本是11.1.0.7,目标端版本是11.1.0.6.这个时候我对问题的定位是可能版本兼用问题(毕竟是高版本到低版本)或者是bug.先查询datapump版本之间兼容性列表

     Version      Written by     Can be imported into Target:  
   Data Pump   database with      10gR1      10gR2      11gR1      11gR2  
Dumpfile Set   compatibility   10.1.0.x   10.2.0.x   11.1.0.x   11.2.0.x
------------ --------------- ---------- ---------- ---------- ----------
         0.1          10.1.x  supported  supported  supported  supported
         1.1          10.2.x         no  supported  supported  supported
         2.1          11.1.x         no         no  supported  supported 
         3.1          11.2.x         no         no         no  supported

这里可以看出11.1.0.7和11.1.0.6之间是相互兼容的,不应该会存在上述问题,那么现在对于该问题的解释很可能是bug导致,继续查询资料发现[ID 752374.1]描述的正是该问题.

解决问题

1. Apply 11.1.0.7 Patch:6890831 on the target database.

2. Workaround this issue by re-running an expdp from the 11.1.0.7 database with an additional 
parameter VERSION=10.2. This will create a new dump file compatible to be imported into 10gR2, 
which will also import successfully into 11.1.0.6.

Unfortunately, please be aware that the new 11g specific features will not be 
exported if expdp is run from 11.1.0.7 with the parameter VERSION=10.2. 
If only normal objects and features are involved, this could be a good workaround.

恢复备份控制文件避免resetlogs方式打开数据库

Posted on by

在很多时候,我们需要使用备份控制文件恢复数据库,在恢复完成后,准备打开库,很多人知道这个时候如果要打开这个库,需要使用resetlogs操作,虽然在oracle 10g及其以后版本中在恢复的时候可以跨越resetlogs操作,但是很多时候大家还是希望使用备份的控制文件能够正常的open一个库,而不是resetlogs.这里通过实验展示使用备份控制文件正常open库的过程,整体思路是:先使用备份控制文件正常恢复数据库,然后重建该控制文件,继而可以正常open库

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> alter database backup controlfile to '/tmp/controlfile.bak';

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

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

--替换备份的控制文件

SQL> startup mount;      
ORACLE instance started.

Total System Global Area  353441008 bytes
Fixed Size                   451824 bytes
Variable Size             184549376 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes
Database mounted.

SQL> recover database using backup controlfile;
ORA-00279: change 12286827844770 generated at 04/12/2012 00:21:54 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/xifenfei/archive/1_4.dbf
ORA-00280: change 12286827844770 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 12286827844772 generated at 04/12/2012 00:21:55 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/xifenfei/archive/1_5.dbf
ORA-00280: change 12286827844772 for thread 1 is in sequence #5
ORA-00278: log file '/u01/oracle/oradata/xifenfei/archive/1_4.dbf' no longer
needed for this recovery


ORA-00279: change 12286827844776 generated at 04/12/2012 00:21:58 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/xifenfei/archive/1_6.dbf
ORA-00280: change 12286827844776 for thread 1 is in sequence #6
ORA-00278: log file '/u01/oracle/oradata/xifenfei/archive/1_5.dbf' no longer
needed for this recovery


ORA-00308: cannot open archived log
'/u01/oracle/oradata/xifenfei/archive/1_6.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL>  recover database using backup controlfile;
ORA-00279: change 12286827844776 generated at 04/12/2012 00:21:58 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/xifenfei/archive/1_6.dbf
ORA-00280: change 12286827844776 for thread 1 is in sequence #6


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/xifenfei/redo02.log
ORA-00310: archived log contains sequence 3; sequence 6 required
ORA-00334: archived log: '/u01/oracle/oradata/xifenfei/redo02.log'


SQL>  recover database using backup controlfile;
ORA-00279: change 12286827844776 generated at 04/12/2012 00:21:58 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/xifenfei/archive/1_6.dbf
ORA-00280: change 12286827844776 for thread 1 is in sequence #6


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/xifenfei/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
--提示需要resetlogs

SQL> alter database backup controlfile to trace as '/tmp/1.txt';

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
Total System Global Area  353441008 bytes
Fixed Size                   451824 bytes
Variable Size             184549376 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes

--重建控制文件
--自动启动到mount状态

--数据库直接open成功
SQL> alter database open;

Database altered.

重建 Datapump Utility EXPDP/IMPDP

Posted on by

因为数据库内部错误,数据字典不一致等原因导致 DataPump不能被正常使用,这个时候可以尝试着通过重建 DataPump来解决问题
sysdba登录数据库

SQL> connect / as sysdba

For Oracle version 10.1

1. Catdp.sql orders the installation of all its components including 
   the Metadata API which was previously installed separately.
   By default catproc.sql invoke this script.
SQL >@ $ORACLE_HOME/rdbms/admin/catdp.sql

2. dbmspump.sql will create DBMS procedures for dataPUMP
SQL >@ $ORACLE_HOME/rdbms/admin/dbmspump.sql

For Oracle version 10.2

1. Catdph.sql will Re-Install DataPump types and views
SQL >@ $ORACLE_HOME/rdbms/admin/catdph.sql 

Use this code to verify if XDB is installed:
SQL> select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)
     comp_id,substr(version,1,12) version,status 
     from dba_registry where comp_id='XDB';

Sample output if XDB installed,
Oracle XML Database            XDB        -version-   VALID

Note: If XDB is installed, then it is required to run "catmetx.sql" script also.
SQL> @ $ORACLE_HOME/rdbms/admin/catmetx.sql


2. prvtdtde.plb will Re-Install tde_library packages
SQL >@ $ORACLE_HOME/rdbms/admin/prvtdtde.plb 


3. Catdpb.sql will Re-Install DataPump packages
SQL >@ $ORACLE_HOME/rdbms/admin/catdpb.sql 


4.Dbmspump.sql will Re-Install DBMS DataPump objects
SQL >@ $ORACLE_HOME/rdbms/admin/dbmspump.sql 


5. To recompile  invalid objects, if any
SQL >@ $ORACLE_HOME/rdbms/admin/utlrp.sql

For Oracle version 11g

1. Catproc.sql 
SQL >@ $ORACLE_HOME/rdbms/admin/catproc.sql 

2. To recompile invalid objects, if any
SQL >@ $ORACLE_HOME/rdbms/admin/utlrp.sql

参考:How To Reload Datapump Utility EXPDP/IMPDP [ID 430221.1]

利用scn增量备份实现数据库增量恢复

Posted on by

数据库版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

全备数据库

RMAN> backup as compressed backupset database  format '/u01/oracle/oradata/tmp/ora11g_0_%U';

Starting backup at 18-APR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/oracle/oradata/ora11g/system01.dbf
input datafile file number=00002 name=/u01/oracle/oradata/ora11g/sysaux01.dbf
input datafile file number=00003 name=/u01/oracle/oradata/ora11g/undotbs01.dbf
input datafile file number=00004 name=/u01/oracle/oradata/ora11g/users01.dbf
input datafile file number=00006 name=/u01/oracle/oradata/ora11g/xifenfei02.dbf
channel ORA_DISK_1: starting piece 1 at 18-APR-12
channel ORA_DISK_1: finished piece 1 at 18-APR-12
piece handle=/u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1 tag=TAG20120418T234958 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 18-APR-12
channel ORA_DISK_1: finished piece 1 at 18-APR-12
piece handle=/u01/oracle/oradata/tmp/ora11g_0_08n8p93h_1_1 tag=TAG20120418T234958 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-APR-12

创建测试数据库验证恢复标准

SQL> conn chf/xifenfei
Connected.
SQL> drop table xifenfei purge;

Table dropped.

SQL> create table xifenfei as
  2  select * from dba_objects;

Table created.

SQL> insert into xifenfei
  2  select * from dba_objects;

74534 rows created.

SQL> /       

74534 rows created.

SQL> /

74534 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from xifenfei;

  COUNT(*)
----------
    298136

SQL> create table xifenfei01 as    
  2  select * from dba_objects;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> create table chf.xifenfei02 as
  2  select * from dba_objects;

Table created.

SQL> alter system switch logfile;

System altered.

异机恢复库

RMAN> restore controlfile from '/u01/oracle/oradata/tmp/ora11g_0_08n8p93h_1_1';

Starting restore at 19-APR-12
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oracle/oradata/ora11g/control01.ctl
output file name=/u01/oracle/oradata/ora11g/control02.ctl
Finished restore at 19-APR-12

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    262.79M    DISK        00:01:08     18-APR-12      
        BP Key: 7   Status: AVAILABLE  Compressed: YES  Tag: TAG20120418T234958
        Piece Name: /u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1
  List of Datafiles in backup set 7
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/system01.dbf
  2       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/sysaux01.dbf
  3       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/undotbs01.dbf
  4       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/users01.dbf
  6       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/xifenfei02.dbf
--这里可以发现备份时的scn,增量备份时取这里的最小scn为起点


--为了排除影响,删除所有归档日志记录
RMAN> delete archivelog all;

RMAN> list archivelog all;

specification does not match any archived log in the repository

RMAN> restore database;

Starting restore at 19-APR-12
using channel ORA_DISK_1

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 00001 to /u01/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oracle/oradata/ora11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/oracle/oradata/ora11g/xifenfei02.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1
channel ORA_DISK_1: piece handle=/u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1 tag=TAG20120418T234958
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:36
Finished restore at 19-APR-12

[oracle@xifenfei oradata]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 19 00:54:42 2012

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


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

SQL> select file#,to_char(checkpoint_change#,'9999999999999999') "SCN",
  2  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN"
  3  from v$datafile_header;

     FILE# SCN                                RESETLOGS SCN
---------- ---------------------------------- ----------------------------------
         1           1155510                             787897
         2           1155510                             787897
         3           1155510                             787897
         4           1155510                             787897
         6           1155510                             787897

SQL> select file#,to_char(checkpoint_change#,'999999999999999') "SCN",
  2  to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;

     FILE# SCN                              STOP_SCN
---------- -------------------------------- --------------------------------
         1          1155510
         2          1155510
         3          1155510
         4          1155510
         6          1155510

基于scn增量备份

RMAN> BACKUP INCREMENTAL FROM SCN 1155510 DATABASE
2> FORMAT '/u01/oracle/oradata/tmp/ora11_scn_%U' tag 'XIFENFEI';

Starting backup at 19-APR-12

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK
backup will be obsolete on date 26-APR-12
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/oracle/oradata/ora11g/system01.dbf
input datafile file number=00002 name=/u01/oracle/oradata/ora11g/sysaux01.dbf
input datafile file number=00003 name=/u01/oracle/oradata/ora11g/undotbs01.dbf
input datafile file number=00004 name=/u01/oracle/oradata/ora11g/users01.dbf
input datafile file number=00006 name=/u01/oracle/oradata/ora11g/xifenfei02.dbf
channel ORA_DISK_1: starting piece 1 at 19-APR-12
channel ORA_DISK_1: finished piece 1 at 19-APR-12
piece handle=/u01/oracle/oradata/tmp/ora11_scn_0bn8pbsd_1_1 tag=XIFENFEI comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

using channel ORA_DISK_1
backup will be obsolete on date 26-APR-12
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 19-APR-12
channel ORA_DISK_1: finished piece 1 at 19-APR-12
piece handle=/u01/oracle/oradata/tmp/ora11_scn_0cn8pbtq_1_1 tag=XIFENFEI comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-APR-12

增量恢复

RMAN> catalog start with '/u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1';

searching for all files that match the pattern /u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1

List of Files Unknown to the Database
=====================================
File Name: /u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1

RMAN> recover database;

Starting recover at 19-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/oracle/oradata/ora11g/system01.dbf
destination for restore of datafile 00002: /u01/oracle/oradata/ora11g/sysaux01.dbf
destination for restore of datafile 00003: /u01/oracle/oradata/ora11g/undotbs01.dbf
destination for restore of datafile 00004: /u01/oracle/oradata/ora11g/users01.dbf
destination for restore of datafile 00006: /u01/oracle/oradata/ora11g/xifenfei02.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/ora11_scn_0bn8pbsd_1_1
channel ORA_DISK_1: piece handle=/u01/oracle/oradata/tmp/ora11_scn_0bn8pbsd_1_1.bak tag=XIFENFEI
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

starting media recovery

--这里表现出来的是利用归档日志恢复，实质是基于scn增量备份的备份集恢复
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_49_777766629.dbf thread=1 sequence=49
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_50_777766629.dbf thread=1 sequence=50
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_51_777766629.dbf thread=1 sequence=51
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_52_777766629.dbf thread=1 sequence=52
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_53_777766629.dbf thread=1 sequence=53
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_54_777766629.dbf thread=1 sequence=54
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_55_777766629.dbf thread=1 sequence=55
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_56_777766629.dbf thread=1 sequence=56
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_57_777766629.dbf thread=1 sequence=57
unable to find archived log
archived log thread=1 sequence=58
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/19/2012 00:55:48
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 58 and starting SCN of 1157379

SQL> select file#,to_char(checkpoint_change#,'9999999999999999') "SCN",
  2  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN"
  3  from v$datafile_header;

     FILE# SCN                                RESETLOGS SCN
---------- ---------------------------------- ----------------------------------
         1           1157379                             787897
         2           1157379                             787897
         3           1157379                             787897
         4           1157379                             787897
         6           1157379                             787897

SQL> select file#,to_char(checkpoint_change#,'999999999999999') "SCN",
  2  to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;

     FILE# SCN                              STOP_SCN
---------- -------------------------------- --------------------------------
         1          1157379
         2          1157379
         3          1157379
         4          1157379
         6          1157379

SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from chf.xifenfei;

  COUNT(*)
----------
    298136

SQL> select TABLE_NAME from dba_tables where table_name LIKE 'XIFENFEI%';

TABLE_NAME
------------------------------------------------------------
XIFENFEI02
XIFENFEI01
XIFENFEI

使用rman基于scn实现数据库增量恢复是在dg中修复gap的时候常见的方法,其实该方法也可以使用常规的增量恢复,通过人工控制,实现数据库的某种特殊的业务需求(特殊的数据迁移).处理思路主要是获得备库的数据文件最小scn(这个scn可能是通过全备恢复或者增量恢复产生),然后基于该SCN实现数据库增量备份,然后利用该备份进行增量恢复.

手工修复ASM DISK HEADER 异常

Posted on by

今天有网友对asm中的磁盘做了fdisk操作,导致asm disk异常,通过手工修复ASM DISK HEADER 解决该问题,这里通过实验重现,提醒大家操作asm中的硬盘分区需要慎重,平时对ASM DISK HEADER 做好备份
初始化信息

SQL> select * from v$version;

BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL>  show parameter instance_name;

NAME                                 TYPE       VALUE
------------------------------------ ---------- ----------------
instance_name                        string     +ASM1

SQL> select group_number,DISK_NUMBER,PATH,HEADER_STATUS from v$asm_disk ;

GROUP_NUMBER DISK_NUMBER PATH                           HEADER_STATUS
------------ ----------- ------------------------------ ------------------------
           1           1 /dev/oracleasm/disks/VOL2      MEMBER
           1           0 /dev/oracleasm/disks/VOL1      MEMBER
           2           1 /dev/oracleasm/disks/VOL4      MEMBER
           2           0 /dev/oracleasm/disks/VOL3      MEMBER

[grid@rac1 ~]$ /etc/init.d/oracleasm  querydisk -d VOL3
Disk "VOL3" is a valid ASM disk on device [8,17]
[grid@rac1 ~]$ ll /dev |grep 8,|grep 17
brw-r----- 1 root disk     8,  17 Apr 17 11:37 sdb1
[grid@rac1 ~]$ /etc/init.d/oracleasm  querydisk -d VOL4
Disk "VOL4" is a valid ASM disk on device [8,18]
[grid@rac1 ~]$ ll /dev |grep 8,|grep 18
brw-r----- 1 root disk     8,  18 Apr 17 11:37 sdb2

备份ASM DISK HEADER

[root@rac1 backup_asmheader]# dd if=/dev/sdb1 of=vol3header.dd bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 0.000143581 seconds, 28.5 MB/s
[root@rac1 backup_asmheader]# dd if=/dev/sdb2 of=vol4header.dd bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 0.000147727 seconds, 27.7 MB/s

破坏ASM DISK HEADER

[root@rac1 ~]# dd if=/dev/zero of=/dev/sdb1 bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 4.4421e-05 seconds, 92.2 MB/s

[grid@rac1 ~]$ kfed read /dev/oracleasm/disks/VOL3
kfbh.endian:                          0 ; 0x000: 0x00
kfbh.hard:                            0 ; 0x001: 0x00
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt:                          0 ; 0x003: 0x00
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
kfbh.check:                           0 ; 0x00c: 0x00000000
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
B4C83200 00000000 00000000 00000000 00000000  [................]
  Repeat 255 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]


SQL> select group_number,DISK_NUMBER,PATH,HEADER_STATUS from v$asm_disk ;

GROUP_NUMBER DISK_NUMBER PATH                           HEADER_STATUS
------------ ----------- ------------------------------ ------------------------
           1           1 /dev/oracleasm/disks/VOL2      MEMBER
           1           0 /dev/oracleasm/disks/VOL1      MEMBER
           2           1 /dev/oracleasm/disks/VOL4      MEMBER
           2           0 /dev/oracleasm/disks/VOL3      CANDIDATE

remount diskgroup

SQL> alter diskgroup  xifenfei dismount;

Diskgroup altered.

SQL> alter diskgroup xifenfei mount;
alter diskgroup xifenfei mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "XIFENFEI" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup
"XIFENFEI"

查看同一DISKGROUP中其他磁盘kfed

[grid@rac1 ~]$ kfed read /dev/oracleasm/disks/VOL4
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:              2147483649 ; 0x008: disk=1
kfbh.check:                   349717291 ; 0x00c: 0x14d8432b
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:     ORCLDISKVOL4 ; 0x000: length=12
kfdhdb.driver.reserved[0]:    877416278 ; 0x008: 0x344c4f56
kfdhdb.driver.reserved[1]:            0 ; 0x00c: 0x00000000
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                186646528 ; 0x020: 0x0b200000
kfdhdb.dsknum:                        1 ; 0x024: 0x0001
kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:           XIFENFEI_0001 ; 0x028: length=13
kfdhdb.grpname:                XIFENFEI ; 0x048: length=8
kfdhdb.fgname:            XIFENFEI_0001 ; 0x068: length=13
kfdhdb.capname:                         ; 0x088: length=0
kfdhdb.crestmp.hi:             32967790 ; 0x0a8: HOUR=0xe DAYS=0x3 MNTH=0x3 YEAR=0x7dc
kfdhdb.crestmp.lo:           2015933440 ; 0x0ac: USEC=0x0 MSEC=0x22d SECS=0x2 MINS=0x1e
kfdhdb.mntstmp.hi:             32969260 ; 0x0b0: HOUR=0xc DAYS=0x11 MNTH=0x4 YEAR=0x7dc
kfdhdb.mntstmp.lo:           3109835776 ; 0x0b4: USEC=0x0 MSEC=0x315 SECS=0x15 MINS=0x2e
kfdhdb.secsize:                     512 ; 0x0b8: 0x0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.mfact:                    113792 ; 0x0c0: 0x0001bc80
kfdhdb.dsksize:                    3788 ; 0x0c4: 0x00000ecc
kfdhdb.pmcnt:                         2 ; 0x0c8: 0x00000002
kfdhdb.fstlocn:                       1 ; 0x0cc: 0x00000001
kfdhdb.altlocn:                       2 ; 0x0d0: 0x00000002
kfdhdb.f1b1locn:                      0 ; 0x0d4: 0x00000000
kfdhdb.redomirrors[0]:                0 ; 0x0d8: 0x0000
kfdhdb.redomirrors[1]:                0 ; 0x0da: 0x0000
kfdhdb.redomirrors[2]:                0 ; 0x0dc: 0x0000
kfdhdb.redomirrors[3]:                0 ; 0x0de: 0x0000
kfdhdb.dbcompat:              168820736 ; 0x0e0: 0x0a100000
kfdhdb.grpstmp.hi:             32967790 ; 0x0e4: HOUR=0xe DAYS=0x3 MNTH=0x3 YEAR=0x7dc
kfdhdb.grpstmp.lo:           2015746048 ; 0x0e8: USEC=0x0 MSEC=0x176 SECS=0x2 MINS=0x1e
kfdhdb.vfstart:                       0 ; 0x0ec: 0x00000000
kfdhdb.vfend:                         0 ; 0x0f0: 0x00000000
kfdhdb.spfile:                        0 ; 0x0f4: 0x00000000
kfdhdb.spfflg:                        0 ; 0x0f8: 0x00000000
kfdhdb.ub4spare[0]:                   0 ; 0x0fc: 0x00000000
kfdhdb.ub4spare[1]:                   0 ; 0x100: 0x00000000
kfdhdb.ub4spare[2]:                   0 ; 0x104: 0x00000000
kfdhdb.ub4spare[3]:                   0 ; 0x108: 0x00000000
kfdhdb.ub4spare[4]:                   0 ; 0x10c: 0x00000000
kfdhdb.ub4spare[5]:                   0 ; 0x110: 0x00000000
kfdhdb.ub4spare[6]:                   0 ; 0x114: 0x00000000
kfdhdb.ub4spare[7]:                   0 ; 0x118: 0x00000000
kfdhdb.ub4spare[8]:                   0 ; 0x11c: 0x00000000
kfdhdb.ub4spare[9]:                   0 ; 0x120: 0x00000000
kfdhdb.ub4spare[10]:                  0 ; 0x124: 0x00000000
kfdhdb.ub4spare[11]:                  0 ; 0x128: 0x00000000
kfdhdb.ub4spare[12]:                  0 ; 0x12c: 0x00000000
kfdhdb.ub4spare[13]:                  0 ; 0x130: 0x00000000
kfdhdb.ub4spare[14]:                  0 ; 0x134: 0x00000000
kfdhdb.ub4spare[15]:                  0 ; 0x138: 0x00000000
kfdhdb.ub4spare[16]:                  0 ; 0x13c: 0x00000000
kfdhdb.ub4spare[17]:                  0 ; 0x140: 0x00000000
kfdhdb.ub4spare[18]:                  0 ; 0x144: 0x00000000
kfdhdb.ub4spare[19]:                  0 ; 0x148: 0x00000000
kfdhdb.ub4spare[20]:                  0 ; 0x14c: 0x00000000
kfdhdb.ub4spare[21]:                  0 ; 0x150: 0x00000000
kfdhdb.ub4spare[22]:                  0 ; 0x154: 0x00000000
kfdhdb.ub4spare[23]:                  0 ; 0x158: 0x00000000
kfdhdb.ub4spare[24]:                  0 ; 0x15c: 0x00000000
kfdhdb.ub4spare[25]:                  0 ; 0x160: 0x00000000
kfdhdb.ub4spare[26]:                  0 ; 0x164: 0x00000000
kfdhdb.ub4spare[27]:                  0 ; 0x168: 0x00000000
kfdhdb.ub4spare[28]:                  0 ; 0x16c: 0x00000000
kfdhdb.ub4spare[29]:                  0 ; 0x170: 0x00000000
kfdhdb.ub4spare[30]:                  0 ; 0x174: 0x00000000
kfdhdb.ub4spare[31]:                  0 ; 0x178: 0x00000000
kfdhdb.ub4spare[32]:                  0 ; 0x17c: 0x00000000
kfdhdb.ub4spare[33]:                  0 ; 0x180: 0x00000000
kfdhdb.ub4spare[34]:                  0 ; 0x184: 0x00000000
kfdhdb.ub4spare[35]:                  0 ; 0x188: 0x00000000
kfdhdb.ub4spare[36]:                  0 ; 0x18c: 0x00000000
kfdhdb.ub4spare[37]:                  0 ; 0x190: 0x00000000
kfdhdb.ub4spare[38]:                  0 ; 0x194: 0x00000000
kfdhdb.ub4spare[39]:                  0 ; 0x198: 0x00000000
kfdhdb.ub4spare[40]:                  0 ; 0x19c: 0x00000000
kfdhdb.ub4spare[41]:                  0 ; 0x1a0: 0x00000000
kfdhdb.ub4spare[42]:                  0 ; 0x1a4: 0x00000000
kfdhdb.ub4spare[43]:                  0 ; 0x1a8: 0x00000000
kfdhdb.ub4spare[44]:                  0 ; 0x1ac: 0x00000000
kfdhdb.ub4spare[45]:                  0 ; 0x1b0: 0x00000000
kfdhdb.ub4spare[46]:                  0 ; 0x1b4: 0x00000000
kfdhdb.ub4spare[47]:                  0 ; 0x1b8: 0x00000000
kfdhdb.ub4spare[48]:                  0 ; 0x1bc: 0x00000000
kfdhdb.ub4spare[49]:                  0 ; 0x1c0: 0x00000000
kfdhdb.ub4spare[50]:                  0 ; 0x1c4: 0x00000000
kfdhdb.ub4spare[51]:                  0 ; 0x1c8: 0x00000000
kfdhdb.ub4spare[52]:                  0 ; 0x1cc: 0x00000000
kfdhdb.ub4spare[53]:                  0 ; 0x1d0: 0x00000000
kfdhdb.acdb.aba.seq:                  0 ; 0x1d4: 0x00000000
kfdhdb.acdb.aba.blk:                  0 ; 0x1d8: 0x00000000
kfdhdb.acdb.ents:                     0 ; 0x1dc: 0x0000
kfdhdb.acdb.ub2spare:                 0 ; 0x1de: 0x0000

通过VOL4 kfed修改出VOL3 kfed

[grid@rac1 ~]$ cat vol3.txt
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: blk=0
*kfbh.block.obj:              2147483648 ; 0x008: disk=0
kfbh.check:                   332940500 ; 0x00c: 0x13d844d4
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
*kfdhdb.driver.provstr:     ORCLDISKVOL3 ; 0x000: length=12
*kfdhdb.driver.reserved[0]:    860639062 ; 0x008: 0x334c4f56
kfdhdb.driver.reserved[1]:            0 ; 0x00c: 0x00000000
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                186646528 ; 0x020: 0x0b200000
*kfdhdb.dsknum:                        0 ; 0x024: 0x0000
kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
*kfdhdb.dskname:           XIFENFEI_0000 ; 0x028: length=13
kfdhdb.grpname:                XIFENFEI ; 0x048: length=8
*kfdhdb.fgname:            XIFENFEI_0000 ; 0x068: length=13
kfdhdb.capname:                         ; 0x088: length=0
kfdhdb.crestmp.hi:             32967790 ; 0x0a8: HOUR=0xe DAYS=0x3 MNTH=0x3 YEAR=0x7dc
kfdhdb.crestmp.lo:           2015933440 ; 0x0ac: USEC=0x0 MSEC=0x22d SECS=0x2 MINS=0x1e
kfdhdb.mntstmp.hi:             32969260 ; 0x0b0: HOUR=0xc DAYS=0x11 MNTH=0x4 YEAR=0x7dc
kfdhdb.mntstmp.lo:           3109835776 ; 0x0b4: USEC=0x0 MSEC=0x315 SECS=0x15 MINS=0x2e
kfdhdb.secsize:                     512 ; 0x0b8: 0x0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.mfact:                    113792 ; 0x0c0: 0x0001bc80
*kfdhdb.dsksize:                    2353 ; 0x0c4: 0x00000931
kfdhdb.pmcnt:                         2 ; 0x0c8: 0x00000002
kfdhdb.fstlocn:                       1 ; 0x0cc: 0x00000001
kfdhdb.altlocn:                       2 ; 0x0d0: 0x00000002
*kfdhdb.f1b1locn:                      2 ; 0x0d4: 0x00000002
kfdhdb.redomirrors[0]:                0 ; 0x0d8: 0x0000
kfdhdb.redomirrors[1]:                0 ; 0x0da: 0x0000
kfdhdb.redomirrors[2]:                0 ; 0x0dc: 0x0000
kfdhdb.redomirrors[3]:                0 ; 0x0de: 0x0000
kfdhdb.dbcompat:              168820736 ; 0x0e0: 0x0a100000
kfdhdb.grpstmp.hi:             32967790 ; 0x0e4: HOUR=0xe DAYS=0x3 MNTH=0x3 YEAR=0x7dc
kfdhdb.grpstmp.lo:           2015746048 ; 0x0e8: USEC=0x0 MSEC=0x176 SECS=0x2 MINS=0x1e
kfdhdb.vfstart:                       0 ; 0x0ec: 0x00000000

后续处理

--导入kfed
[grid@rac1 ~]$ kfed merge /dev/oracleasm/disks/VOL3 text=vol3.txt

--MOUNT diskgroup
SQL> alter diskgroup xifenfei mount;

Diskgroup altered.