模拟带库实现rman远程备份

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：模拟带库实现rman远程备份

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

rman通过模拟带库备份到远程机器,需要先配置用户ssh到目标机器
linux平台oracle备份

RMAN> RUN {
2> ALLOCATE CHANNEL S1 DEVICE TYPE sbt
3> PARMS='SBT_LIBRARY=/tmp/rman_sync.so,ENV=(
4> OB_SERVER=www.xifenfei.com,
5> OB_USER=root,
6> OB_PORT=22123,
7> OB_PATH=/tmp)';
8> BACKUP CURRENT CONTROLFILE FORMAT 'ctl_%U';
9> }

allocated channel: S1
channel S1: SID=125 device type=SBT_TAPE
channel S1: SBT/SSH2-SFTP

Starting backup at 22-FEB-24
channel S1: starting full datafile backup set
channel S1: specifying datafile(s) in backup set
including current control file in backup set
channel S1: starting piece 1 at 22-FEB-24
channel S1: finished piece 1 at 22-FEB-24
piece handle=ctl_012jq4gg_1_1 tag=TAG20240222T190344 comment=API Version 2.0,MMS Version 1.0.9.0
channel S1: backup set complete, elapsed time: 00:00:25
Finished backup at 22-FEB-24
released channel: S1

RMAN> list backup;

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    9.50M      SBT_TAPE    00:00:24     22-FEB-24
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20240222T190344
        Handle: ctl_012jq4gg_1_1   Media: 0B2B9255
  Control File Included: Ckp SCN: 4438494      Ckp time: 22-FEB-24

win平台oracle备份

RMAN> RUN {
2>  ALLOCATE CHANNEL S1 DEVICE TYPE sbt
3>  PARMS='SBT_LIBRARY=D:\TEMP\rman_sync.dll,
4>  ENV=(
5>  OB_SERVER=www.xifenfei.com,
6>  OB_USER=root,
7>  OB_PORT=22123,
8>  OB_PATH=/tmp/,
9>  OB_PUBLIC_KEY=C:\Users\XIFENFEI\.ssh\id_rsa.pub,
10>  OB_SECRET_KEY=C:\Users\XIFENFEI\.ssh\id_rsa,
11>  OB_LOGFILE=d:\xifenfei.log)';
12>  BACKUP CURRENT CONTROLFILE FORMAT 'ctl_%U';
13>  }

分配的通道: S1
通道 S1: SID=156 设备类型=SBT_TAPE
通道S1: SBT/SSH2-SFTP

启动 backup 于 23-2月 -24
通道 S1: 正在启动全部数据文件备份集
通道 S1: 正在指定备份集内的数据文件
备份集内包括当前控制文件
通道 S1: 正在启动段 1 于 23-2月 -24
通道 S1: 已完成段 1 于 23-2月 -24
段句柄=ctl_012jt7mr_1_1 标记=TAG20240223T231642 注释=API Version 2.0,MMS Version 1.0.9.0
通道 S1: 备份集已完成, 经过时间:00:00:07
完成 backup 于 23-2月 -24
释放的通道: S1

RMAN> list backup;


备份集列表
===================


BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
1       Full    9.50M      SBT_TAPE    00:00:05     23-2月 -24
        BP 关键字: 1   状态: AVAILABLE  已压缩: NO  标记: TAG20240223T231642
        句柄: ctl_012jt7mr_1_1   介质: 0B2B9255
  包括的控制文件: Ckp SCN: 14544437     Ckp 时间: 23-2月 -24

使用rman from service 搭建dataguard

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：使用rman from service 搭建dataguard

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

从oracle 12c开始提供了rman通过from service方式搭建dg,使用12c长期支持版19c(并打上最新的patch)
配置dataguard相关参数

alter system set db_unique_name='XIFENFEI' scope=spfile;
alter system set service_names='XIFENFEI';
alter system set log_archive_config='dg_config=(XIFENFEI,XIFENFEIDG)';
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=XIFENFEI';
alter system set log_archive_dest_2='service=XIFENFEIDG lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=XIFENFEIDG';
alter system set standby_file_management=auto;
alter system set db_file_name_convert='/u01/app/oracle/oradata/XIFENFEI/','/u01/app/oracle/oradata/XIFENFEI/' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/XIFENFEI/','/u01/app/oracle/oradata/XIFENFEI/' scope=spfile;
alter system set fal_server=XIFENFEIDG;

配置tnsnames.ora

XIFENFEI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.238)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = XIFENFEI)
    )
  )

XIFENFEIDG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.124)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = XIFENFEI)
    )
  )

拷贝主库密码文件到备库

[oracle@primary ~]$ scp $ORACLE_HOME/dbs/orapwXIFENFEI 192.168.0.124:$ORACLE_HOME/dbs/
The authenticity of host '192.168.0.124 (192.168.0.124)' can't be established.
ECDSA key fingerprint is SHA256:NI2952z4Bqc3M/B+AK7EJRiJNauROIyluvu1l4NSTX0.
ECDSA key fingerprint is MD5:1d:64:dd:ef:1c:ad:ed:cf:70:22:2d:4d:7c:90:5e:5e.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.0.124' (ECDSA) to the list of known hosts.
oracle@192.168.0.124's password: 
orapwXIFENFEI                                                                   100% 2048     6.6MB/s   00:00    
[oracle@primary ~]$

备库启动到nomount状态

[oracle@standby ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 13 20:32:34 2021
Version 19.10.0.0.0

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

Connected to an idle instance.

SQL> create spfile from pfile='/tmp/pfile';

File created.

SQL> startup nomount pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area 4294963264 bytes
Fixed Size                  8904768 bytes
Variable Size             805306368 bytes
Database Buffers         3472883712 bytes
Redo Buffers                7868416 bytes
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

rman from service方式创建standby ctl和还原数据文件

[oracle@standby ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 13 20:34:37 2021
Version 19.10.0.0.0

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

connected to target database: XIFENFEI (not mounted)

RMAN> restore standby controlfile from service XIFENFEI;

Starting restore at 13-MAR-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service XIFENFEI
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/XIFENFEI/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/XIFENFEI/control02.ctl
Finished restore at 13-MAR-21

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN> restore database from service XIFENFEI;

Starting restore at 13-MAR-21
Starting implicit crosscheck backup at 13-MAR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK
Finished implicit crosscheck backup at 13-MAR-21

Starting implicit crosscheck copy at 13-MAR-21
using channel ORA_DISK_1
Finished implicit crosscheck copy at 13-MAR-21

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service XIFENFEI
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/XIFENFEI/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service XIFENFEI
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/XIFENFEI/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service XIFENFEI
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/XIFENFEI/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service XIFENFEI
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/XIFENFEI/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 13-MAR-21
[/sehll]
备库启动mrp进程
1
1
2021-03-13T20:54:08.075418+08:00
Attempt to start background Managed Standby Recovery process (XIFENFEI)
Starting background process MRP0
2021-03-13T20:54:08.086269+08:00
MRP0 started with pid=56, OS id=8182 
2021-03-13T20:54:08.087276+08:00
Background Managed Standby Recovery process started (XIFENFEI)
2021-03-13T20:54:13.104757+08:00
 Started logmerger process
2021-03-13T20:54:13.112058+08:00

IM on ADG: Start of Empty Journal 

IM on ADG: End of Empty Journal 
PR00 (PID:8188): Managed Standby Recovery starting Real Time Apply
2021-03-13T20:54:13.205668+08:00
Parallel Media Recovery started with 4 slaves
2021-03-13T20:54:13.216576+08:00
Stopping change tracking
PR00 (PID:8188): Media Recovery Waiting for T-1.S-25 (in transit)
2021-03-13T20:54:13.269138+08:00
Recovery of Online Redo Log: Thread 1 Group 12 Seq 25 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/XIFENFEI/s_redo12.log

至此dataguard基本上搭建完成

rman备份到win共享目录

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：rman备份到win共享目录

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在win环境中数据库备份到异地,相对来说没有linux的nfs方便(可能nfs使用多了比较熟悉),以前写过一篇文章（windows rman自动备份并传输到远程服务器处理方法）,通过本地备份,然后拷贝到远程共享目录实现,相对来说该方案比较繁琐,这次尝试直接备份到共享目录
1. 服务配置
20190926221926

oracle数据库服务和监听服务配置使用此账户的方式登录(而不是默认的本地系统账号)

2.在目标服务器中配置共享
20190926222029

主要两台win服务器登录用户名和密码需要一致,最好也是数据库安装用户

3.数据库备份脚本
20190926222130

备份脚本路径使用\\方式而不能使用别名盘符

4.数据库备份计划任务
20190926222951

运行任务时请使用下列用户选择ORA_DBA

恢复没有控制文件备份的rman数据文件备份

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：恢复没有控制文件备份的rman数据文件备份

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

最近有朋友咨询,只有rman备份数据文件,无控制文件备份的问题,这里进行分析验证
只有数据文件的rman备份
这里只有数据文件的备份，没有任何控制文件的备份

C:\Documents and Settings\Administrator>e:
E:\>dir
 驱动器 E 中的卷是 新加卷
 卷的序列号是 68FB-8527
 E:\ 的目录
2018-03-29  10:53     6,163,873,792 FULL_20180329_01SV08N2_1_1.RMAN
2018-03-29  10:54     1,050,558,464 FULL_20180329_02SV0918_1_1.RMAN
2018-03-29  10:56     2,167,414,784 FULL_20180329_03SV092B_1_1.RMAN
2018-03-29  11:07     1,034,625,024 FULL_20180329_05SV09QQ_1_1.RMAN
2018-03-29  11:07         1,376,256 FULL_20180329_06SV09RJ_1_1.RMAN
2018-03-29  11:08         3,104,768 FULL_20180329_07SV09TP_1_1.RMAN

还原system01.dbf

SQL> startup nomount
ORACLE 例程已经启动。
Total System Global Area 2137886720 bytes
Fixed Size                  2177456 bytes
Variable Size            1224738384 bytes
Database Buffers          905969664 bytes
Redo Buffers                5001216 bytes
SQL> @restore_datafile.sql system01.dbf
Trying : E:/FULL_20180329_02SV0918_1_1.RMAN From : DISK
Data File : Found
------------------------
RESTORED Data File To: D:\oradata\orcl\system01.dbf
PL/SQL 过程已成功完成。

重建控制文件

SQL> startup nomount pfile=e:/pfile.txt
ORACLE 例程已经启动。
Total System Global Area 2137886720 bytes
Fixed Size                  2177456 bytes
Variable Size            1224738384 bytes
Database Buffers          905969664 bytes
Redo Buffers                5001216 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 50
  3      MAXLOGMEMBERS 5
  4      MAXDATAFILES 100
  5      MAXINSTANCES 1
  6      MAXLOGHISTORY 226
  7  LOGFILE
  8    GROUP 1 'D:\oradata\orcl\redo01.log'  SIZE 100M,
  9    GROUP 2 'D:\oradata\orcl\redo02.log'  SIZE 100M,
 10    GROUP 3 'D:\oradata\orcl\redo03.log'  SIZE 100M
 11  DATAFILE
 12    'D:\oradata\orcl\system01.dbf'
 13  CHARACTER SET ZHS16GBK
 14  ;
控制文件已创建。

注册备份集

E:\>rman target /
恢复管理器: Release 11.2.0.1.0 - Production on 星期四 3月 29 15:46:17 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
已连接到目标数据库: ORCL (DBID=1449113534, 未打开)
RMAN> catalog start with 'e:/*.rman';
使用目标数据库控制文件替代恢复目录
搜索与样式 e:/*.rman 匹配的所有文件
数据库未知文件的列表
=====================================
文件名: E:\FULL_20180329_01SV08N2_1_1.RMAN
文件名: E:\FULL_20180329_02SV0918_1_1.RMAN
文件名: E:\FULL_20180329_03SV092B_1_1.RMAN
文件名: E:\FULL_20180329_05SV09QQ_1_1.RMAN
文件名: E:\FULL_20180329_06SV09RJ_1_1.RMAN
文件名: E:\FULL_20180329_07SV09TP_1_1.RMAN
是否确实要将上述文件列入目录 (输入 YES 或 NO)? yes
正在编制文件目录...
目录编制完毕
已列入目录的文件的列表
=======================
文件名: E:\FULL_20180329_01SV08N2_1_1.RMAN
文件名: E:\FULL_20180329_02SV0918_1_1.RMAN
文件名: E:\FULL_20180329_03SV092B_1_1.RMAN
文件名: E:\FULL_20180329_05SV09QQ_1_1.RMAN
文件名: E:\FULL_20180329_06SV09RJ_1_1.RMAN
文件名: E:\FULL_20180329_07SV09TP_1_1.RMAN
RMAN> list backup;
备份集列表
===================
BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
1       Full    5.74G      DISK        00:00:00     29-3月 -18
        BP 关键字: 1   状态: AVAILABLE  已压缩: NO  标记: TAG20180329T104802
段名:E:\FULL_20180329_01SV08N2_1_1.RMAN
  备份集 1 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  5       Full 96384627   29-3月 -18
BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
2       Full    1001.88M   DISK        00:00:00     29-3月 -18
        BP 关键字: 2   状态: AVAILABLE  已压缩: NO  标记: TAG20180329T104802
段名:E:\FULL_20180329_02SV0918_1_1.RMAN
  备份集 2 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  1       Full 96384627   29-3月 -18 D:\ORADATA\ORCL\SYSTEM01.DBF
BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
3       Full    2.02G      DISK        00:00:00     29-3月 -18
        BP 关键字: 3   状态: AVAILABLE  已压缩: NO  标记: TAG20180329T104802
段名:E:\FULL_20180329_03SV092B_1_1.RMAN
  备份集 3 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  6       Full 96384627   29-3月 -18
BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
4       Full    986.69M    DISK        00:00:00     29-3月 -18
        BP 关键字: 4   状态: AVAILABLE  已压缩: NO  标记: TAG20180329T110706
段名:E:\FULL_20180329_05SV09QQ_1_1.RMAN
  备份集 4 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  2       Full 96384627   29-3月 -18
BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
5       Full    1.30M      DISK        00:00:00     29-3月 -18
        BP 关键字: 5   状态: AVAILABLE  已压缩: NO  标记: TAG20180329T110706
段名:E:\FULL_20180329_06SV09RJ_1_1.RMAN
  备份集 5 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  4       Full 96384627   29-3月 -18
BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
6       Full    2.95M      DISK        00:00:00     29-3月 -18
        BP 关键字: 6   状态: AVAILABLE  已压缩: NO  标记: TAG20180329T110841
段名:E:\FULL_20180329_07SV09TP_1_1.RMAN
  备份集 6 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  3       Full 96384627   29-3月 -18

还原所有数据文件

SQL> @restore_datafile all_file
Restoring All Data Files :
--------------------------
Attempting To Restore :D:\oradata\orcl\1.dbf
------------------------
Trying : E:/FULL_20180329_01SV08N2_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_02SV0918_1_1.RMAN From : DISK
Data File : Found
------------------------
RESTORED Data File To: D:\oradata\orcl\1.dbf
Attempting To Restore :D:\oradata\orcl\2.dbf
------------------------
Trying : E:/FULL_20180329_01SV08N2_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_02SV0918_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_03SV092B_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_05SV09QQ_1_1.RMAN From : DISK
Data File : Found
------------------------
RESTORED Data File To: D:\oradata\orcl\2.dbf
Attempting To Restore :D:\oradata\orcl\3.dbf
------------------------
Trying : E:/FULL_20180329_01SV08N2_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_02SV0918_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_03SV092B_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_05SV09QQ_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_06SV09RJ_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_07SV09TP_1_1.RMAN From : DISK
Data File : Found
------------------------
RESTORED Data File To: D:\oradata\orcl\3.dbf
Attempting To Restore :D:\oradata\orcl\4.dbf
------------------------
Trying : E:/FULL_20180329_01SV08N2_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_02SV0918_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_03SV092B_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_05SV09QQ_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_06SV09RJ_1_1.RMAN From : DISK
Data File : Found
------------------------
RESTORED Data File To: D:\oradata\orcl\4.dbf
Attempting To Restore :D:\oradata\orcl\5.dbf
------------------------
Trying : E:/FULL_20180329_01SV08N2_1_1.RMAN From : DISK
Data File : Found
------------------------
RESTORED Data File To: D:\oradata\orcl\5.dbf
Attempting To Restore :D:\oradata\orcl\6.dbf
------------------------
Trying : E:/FULL_20180329_01SV08N2_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_02SV0918_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_03SV092B_1_1.RMAN From : DISK
Data File : Found
------------------------
RESTORED Data File To: D:\oradata\orcl\6.dbf
PL/SQL 过程已成功完成。

重建控制文件并open数据库

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 50
  3      MAXLOGMEMBERS 5
  4      MAXDATAFILES 100
  5      MAXINSTANCES 1
  6      MAXLOGHISTORY 226
  7  LOGFILE
  8    GROUP 1 'D:\oradata\orcl\redo01.log'  SIZE 100M,
  9    GROUP 2 'D:\oradata\orcl\redo02.log'  SIZE 100M,
 10    GROUP 3 'D:\oradata\orcl\redo03.log'  SIZE 100M
 11  DATAFILE
 12    'D:\oradata\orcl\1.dbf',
 13    'D:\oradata\orcl\2.dbf',
 14    'D:\oradata\orcl\3.dbf',
 15    'D:\oradata\orcl\4.dbf',
 16    'D:\oradata\orcl\5.dbf',
 17    'D:\oradata\orcl\6.dbf'
 18  CHARACTER SET ZHS16GBK
 19  ;
控制文件已创建。
SQL> alter database open resetlogs;
数据库已更改。

这里本质就是通过oracle内部包,直接还原数据文件,这个模拟的是基础的情况,在实际的恢复中,由于只有数据文件的备份,可能文件不一致,还需要通过一些非常规方法对数据库进行强制打开

ORA-00230: operation disallowed: snapshot control file enqueue unavailable

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-00230: operation disallowed: snapshot control file enqueue unavailable

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

rman 备份控制文件报ORA-00230: operation disallowed: snapshot control file enqueue unavailable错误

db1:/home/oracle>$rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Wed Jun 10 16:00:08 2015
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: ORCL (DBID=1150889877)
RMAN> backup current controlfile format '/tmp/xifenfei.ctl';
Starting backup at 10-JUN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=628 instance=orcl1 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
waiting for snapshot control file enqueue
waiting for snapshot control file enqueue
waiting for snapshot control file enqueue
waiting for snapshot control file enqueue
waiting for snapshot control file enqueue
cannot make a snapshot control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/10/2015 16:03:10
ORA-00230: operation disallowed: snapshot control file enqueue unavailable

查看持有CF enqueue会话

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> SELECT s.SID, USERNAME AS "User", PROGRAM, MODULE,
  2  ACTION, LOGON_TIME "Logon"
  3  FROM V$SESSION s, V$ENQUEUE_LOCK l
  4  WHERE l.SID = s.SID
  5  AND l.TYPE = 'CF'
  6  AND l.ID1 = 0
  7  AND l.ID2 = 2;
       SID User
---------- ------------------------------
PROGRAM
------------------------------------------------
MODULE
------------------------------------------------
ACTION                           Logon
-------------------------------- ------------
       648 SYS
rman@db1 (TNS V1-V3)
backup full datafile
0000152 STARTED111               03-JUN-15

kill相关session

SQL> select spid from v$process where addr in(select paddr from v$session where sid=648);
SPID
------------
40108238
SQL> !ps -ef|grep 40108238
  oracle 39125244 65011720   0 15:59:27  pts/0  0:00 grep 40108238
  oracle 40108238        1   0   Jun 03      -  1:18 oracleorcl1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
db1:/home/oracle>$kill -9 40108238

再次测试备份控制文件–OK

db1:/home/oracle>$rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Wed Jun 10 16:05:06 2015
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: ORCL (DBID=1150889877)
RMAN> backup current controlfile format '/tmp/xifenfei.ctl';
Starting backup at 10-JUN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=632 instance=orcl1 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 10-JUN-15
channel ORA_DISK_1: finished piece 1 at 10-JUN-15
piece handle=/tmp/xifenfei.ctl tag=TAG20150610T160516 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 10-JUN-15

windows rman自动备份并传输到远程服务器处理方法

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：windows rman自动备份并传输到远程服务器处理方法

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在linux中，要使用rman备份后传输到远程服务器上,可以选择ftp,scp,nfs等方式实现,在win主机上可以配置ftp或者共享实现.linux的解决方法已经很多,这里重点提供win上面实现rman备份且传输到远程服务器的解决方法,简单实现异地备份方法:
1.win配置共享目录，而且设置远程服务器有写权限,如果省事可以配置everyone有读写权限
2.创建相关备份目录,这里主要是rmanfile,rmanscript,rmanlog
3.编写rman备份脚本

CONFIGURE RETENTION POLICY TO REDUNDANCY = 7;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
backup  as compressed backupset database format 'E:\backup_db\rmanfile\full_%T_%U.rman';
sql 'alter system archive log current';
backup  as compressed backupset archivelog  all format 'E:\backup_db\rmanfile\arch_%T_%U.rman'  delete input;
DELETE noprompt OBSOLETE;
crosscheck backup;
delete noprompt expired backup;
backup  format 'E:\backup_db\rmanfile\ctl_%T_%U.rman' current controlfile;
backup spfile format 'E:\backup_db\rmanfile\spfile_%T_%U.rman' ;
exit;

4.调用rman备份脚本

rman target / cmdfile=E:\backup_db\scriptfile\backup_db.rman
log=E:\backup_db\logfile\rmanlog_%date:~0,4%%date:~5,2%%date:~8,2%.log

5.拷贝到远程脚本
需要注意是按照备份集中的日期作为标记来删除的，也就是说，一次备份最好不要跨天

copy /y e:\backup_db\rmanfile\*_%date:~0,4%%date:~5,2%%date:~8,2%_*.RMAN \\192.168.13.40\oracle_backup

6.删除远程服务器N天前备份脚本
需要注意是按照备份集中的日期作为标记来删除的，也就是说，一次备份最好不要跨天

@echo off
set DaysAgo=5
call :DateToDays %date:~0,4% %date:~5,2% %date:~8,2% PassDays
set /a PassDays-=%DaysAgo%
call :DaysToDate %PassDays% DstYear DstMonth DstDay
del \\192.168.13.40\oracle_backup\*_%DstYear%%DstMonth%%DstDay%_*.RMAN
goto :eof
:DateToDays %yy% %mm% %dd% days
setlocal ENABLEEXTENSIONS
set yy=%1&set mm=%2&set dd=%3
if 1%yy% LSS 200 if 1%yy% LSS 170 (set yy=20%yy%) else (set yy=19%yy%)
set /a dd=100%dd%%%100,mm=100%mm%%%100
set /a z=14-mm,z/=12,y=yy+4800-z,m=mm+12*z-3,j=153*m+2
set /a j=j/5+dd+y*365+y/4-y/100+y/400-2472633
endlocal&set %4=%j%&goto :EOF
:DaysToDate %days% yy mm dd
setlocal ENABLEEXTENSIONS
set /a a=%1+2472632,b=4*a+3,b/=146097,c=-b*146097,c/=4,c+=a
set /a d=4*c+3,d/=1461,e=-1461*d,e/=4,e+=c,m=5*e+2,m/=153,dd=153*m+2,dd/=5
set /a dd=-dd+e+1,mm=-m/10,mm*=12,mm+=m+3,yy=b*100+d-4800+m/10
(if %mm% LSS 10 set mm=0%mm%)&(if %dd% LSS 10 set dd=0%dd%)
endlocal&set %2=%yy%&set %3=%mm%&set %4=%dd%&goto :EOF

7.配置计划任务,让定时执行相关脚本

ORACLE 12C RMAN 功能增强

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORACLE 12C RMAN 功能增强

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在ORACLE 12C中对rman的功能有了不少增强,在以前的文章中写过RMAN RECOVER TABLE功能,这里另外补充rman增强的两个小功能(sql语句和数据文件分割)
数据库版本

SQL>  select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                        0
TNS for Linux: Version 12.1.0.1.0 - Production                                            0
NLSRTL Version 12.1.0.1.0 - Production                                                    0

rman对sql语句支持增强

[oracle@xifenfei tmp]$ rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Sat Jun 1 14:07:50 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
connected to target database: CDB (DBID=1922813718)
RMAN> select sysdate from dual;
using target database control file instead of recovery catalog
SYSDATE
---------
01-JUN-13
RMAN> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Statement processed
RMAN>  select sysdate from dual;
SYSDATE
-------------------
2013-06-01 14:16:48
RMAN> desc v$log
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GROUP#                                             NUMBER
 THREAD#                                            NUMBER
 SEQUENCE#                                          NUMBER
 BYTES                                              NUMBER
 BLOCKSIZE                                          NUMBER
 MEMBERS                                            NUMBER
 ARCHIVED                                           VARCHAR2(3)
 STATUS                                             VARCHAR2(16)
 FIRST_CHANGE#                                      NUMBER
 FIRST_TIME                                         DATE
 NEXT_CHANGE#                                       NUMBER
 NEXT_TIME                                          DATE
 CON_ID                                             NUMBER

这里看到rman只是sql语句中的select和desc用法

rman分割数据文件增强

RMAN>  CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
RMAN> backup incremental level 1 section size 30M datafile 1 format '/tmp/system_%U.rman';
Starting backup at 01-JUN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=269 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=24 device type=DISK
no parent backup or copy of datafile 1 found
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/cdb/system01.dbf
backing up blocks 1 through 3840
channel ORA_DISK_1: starting piece 1 at 01-JUN-13
channel ORA_DISK_2: starting incremental level 1 datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/cdb/system01.dbf
……………………
backing up blocks 96001 through 99840
channel ORA_DISK_3: starting piece 26 at 01-JUN-13
channel ORA_DISK_1: finished piece 24 at 01-JUN-13
piece handle=/tmp/system_02ob3pg1_24_1.rman tag=TAG20130601T144518 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/cdb/system01.dbf
backing up blocks 99841 through 101120
channel ORA_DISK_1: starting piece 27 at 01-JUN-13
channel ORA_DISK_2: finished piece 25 at 01-JUN-13
piece handle=/tmp/system_02ob3pg1_25_1.rman tag=TAG20130601T144518 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_3: finished piece 26 at 01-JUN-13
piece handle=/tmp/system_02ob3pg1_26_1.rman tag=TAG20130601T144518 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:06
channel ORA_DISK_1: finished piece 27 at 01-JUN-13
piece handle=/tmp/system_02ob3pg1_27_1.rman tag=TAG20130601T144518 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 01-JUN-13

备份文件情况

[oracle@xifenfei tmp]$ ll -ltr system*
-rw-r----- 1 oracle dba 14761984 Jun  1 14:45 system_02ob3pg1_1_1.rman
-rw-r----- 1 oracle dba  9535488 Jun  1 14:45 system_02ob3pg1_2_1.rman
-rw-r----- 1 oracle dba 16973824 Jun  1 14:45 system_02ob3pg1_4_1.rman
-rw-r----- 1 oracle dba 18284544 Jun  1 14:45 system_02ob3pg1_3_1.rman
-rw-r----- 1 oracle dba 12804096 Jun  1 14:45 system_02ob3pg1_5_1.rman
-rw-r----- 1 oracle dba 29163520 Jun  1 14:45 system_02ob3pg1_6_1.rman
-rw-r----- 1 oracle dba 31326208 Jun  1 14:46 system_02ob3pg1_7_1.rman
-rw-r----- 1 oracle dba 30851072 Jun  1 14:46 system_02ob3pg1_8_1.rman
-rw-r----- 1 oracle dba 30801920 Jun  1 14:46 system_02ob3pg1_9_1.rman
-rw-r----- 1 oracle dba 23977984 Jun  1 14:46 system_02ob3pg1_11_1.rman
-rw-r----- 1 oracle dba 28508160 Jun  1 14:46 system_02ob3pg1_10_1.rman
-rw-r----- 1 oracle dba 30277632 Jun  1 14:46 system_02ob3pg1_12_1.rman
-rw-r----- 1 oracle dba 31498240 Jun  1 14:46 system_02ob3pg1_13_1.rman
-rw-r----- 1 oracle dba 31498240 Jun  1 14:47 system_02ob3pg1_14_1.rman
-rw-r----- 1 oracle dba 31498240 Jun  1 14:47 system_02ob3pg1_15_1.rman
-rw-r----- 1 oracle dba 30507008 Jun  1 14:47 system_02ob3pg1_17_1.rman
-rw-r----- 1 oracle dba 30834688 Jun  1 14:47 system_02ob3pg1_16_1.rman
-rw-r----- 1 oracle dba 31498240 Jun  1 14:47 system_02ob3pg1_18_1.rman
-rw-r----- 1 oracle dba 30244864 Jun  1 14:47 system_02ob3pg1_19_1.rman
-rw-r----- 1 oracle dba 29016064 Jun  1 14:47 system_02ob3pg1_20_1.rman
-rw-r----- 1 oracle dba 29212672 Jun  1 14:47 system_02ob3pg1_21_1.rman
-rw-r----- 1 oracle dba 30728192 Jun  1 14:47 system_02ob3pg1_22_1.rman
-rw-r----- 1 oracle dba 29384704 Jun  1 14:47 system_02ob3pg1_23_1.rman
-rw-r----- 1 oracle dba 26566656 Jun  1 14:47 system_02ob3pg1_24_1.rman
-rw-r----- 1 oracle dba 24928256 Jun  1 14:48 system_02ob3pg1_25_1.rman
-rw-r----- 1 oracle dba 19324928 Jun  1 14:48 system_02ob3pg1_26_1.rman
-rw-r----- 1 oracle dba  6291456 Jun  1 14:48 system_02ob3pg1_27_1.rman

在12C之前的版本，ORACLE 11GR2只是对于全备的备份集备份(非增量,非copy备份方式)方式支持数据文件分割备份功能,对于11.2之前的版本均不支持该功能.在12C中rman可以支持对于全备,增量备份,copy备份全部支持分割数据文件备份(CONTROLFILE,SPFILE不支持)

跳过rman坏块恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：跳过rman坏块恢复

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在有些情况下,我们仅有一份rman备份,而这个时候rman 备份有出现坏块,使得我们的还原/恢复工作无法继续下去,导致数据大量丢失.我们可以通过设置event 19548/19549来跳过坏块,最大程度抢救数据
rman备份数据文件

C:\Users\XIFENFEI>rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jun 6 20:31:19 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: XIFENFEI (DBID=1422012639)
RMAN> backup tablespace users format 'f:/users_bak.rman';
Starting backup at 06-JUN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=197 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=00004 name=E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 06-JUN-13
channel ORA_DISK_1: finished piece 1 at 06-JUN-13
piece handle=F:\USERS_BAK.RMAN tag=TAG20130606T203154 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 06-JUN-13

切换归档日志

SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            E:\oracle\product\11.2.0\dbhome_1\RDBMS
Oldest online log sequence     95
Next log sequence to archive   97
Current log sequence           97

重命名数据文件

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--------------------------------------
e:\oracle\oradata\XIFENFEI>move USERS01.DBF USERS01_bak.DBF
移动了         1 个文件。
--------------------------------------
SQL> startup
ORACLE instance started.
Total System Global Area  418484224 bytes
Fixed Size                  1385052 bytes
Variable Size             327159204 bytes
Database Buffers           83886080 bytes
Redo Buffers                6053888 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF'

破坏备份集
破坏前

破坏后

这里很明显,我通过ue把rman备份集中的T修改为了A,肯定破坏了文件,使之出现坏块

rman还原数据文件

C:\Users\XIFENFEI>rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jun 6 21:02:41 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: XIFENFEI (DBID=1422012639, not open)
RMAN> restore datafile 4;
Starting restore at 06-JUN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 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 00004 to E:\ORACLE\ORADATA\XIFENFEI\USERS
01.DBF
channel ORA_DISK_1: reading from backup piece F:\USERS_BAK.RMAN
channel ORA_DISK_1: ORA-19870: error while restoring backup piece F:\USERS_BAK.R
MAN
ORA-19612: datafile 4 not restored due to missing or corrupt data
failover to previous backup
creating datafile file number=4 name=E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF
Finished restore at 06-JUN-13

这里可以清晰的看到rman报ORA-19612错误，restore 失败,alert日志为:

Thu Jun 06 21:02:31 2013
ALTER DATABASE OPEN
Errors in file E:\ORACLE\diag\rdbms\xifenfei\xff\trace\xff_dbw0_7400.trc:
ORA-01157: ????/?????? 4 - ??? DBWR ????
ORA-01110: ???? 4: 'E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF'
ORA-27041: ??????
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。
Errors in file E:\ORACLE\diag\rdbms\xifenfei\xff\trace\xff_ora_4272.trc:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF'
ORA-1157 signalled during: ALTER DATABASE OPEN...
Thu Jun 06 21:02:33 2013
Checker run found 1 new persistent data failures
Thu Jun 06 21:03:23 2013
Corrupt block 101 found during reading backup piece, file=F:\USERS_BAK.RMAN, corr_type=3
Reread of blocknum=101, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=101, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=101, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=101, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=101, file=F:\USERS_BAK.RMAN, found same corrupt data
Continuing reading piece F:\USERS_BAK.RMAN, no other copies available.

rman备份集有坏块，导致rman还原无法正常进行下去,还原后的数据文件大小

观察已经正常还原出来数据文件情况

SQL> select CHECKPOINT_CHANGE#,file# from v$datafile_header;
CHECKPOINT_CHANGE#      FILE#
------------------ ----------
           1571582          1
           1571582          2
           1571582          3
             18379          4
           1571582          5
           1571582          6
           1571582          7
SQL> recover database datafile 4 ;
ORA-00274: illegal recovery option DATAFILE
SQL> recover datafile 4;
ORA-00279: change 18379 generated at 01/20/2013 17:13:56 needed for thread 1
ORA-00289: suggestion :
E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000001_0805223583.0001
ORA-00280: change 18379 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

rman只是还原了很小的一部分文件,做恢复提示需要从归档日志seq 1开始(某些情况可能需要其他归档,总之不是正常情况),证明rman还原异常

设置event事件还原

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup pfile='e:/pfile.txt' mount;
ORACLE instance started.
Total System Global Area  418484224 bytes
Fixed Size                  1385052 bytes
Variable Size             327159204 bytes
Database Buffers           83886080 bytes
Redo Buffers                6053888 bytes
Database mounted.
SQL> show parameter event;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
event                                string      19548 trace name context forev
                                                 er, 19549 trace name context f
                                                 orever

Event 19548:This will attempt to restore content of the corrupted block if it is possible.
Event 19549:This will suppress erroring out during restore

rman还原数据文件

RMAN> restore datafile 4;
Starting restore at 06-JUN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 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 00004 to E:\ORACLE\ORADATA\XIFENFEI\USERS
01.DBF
channel ORA_DISK_1: reading from backup piece F:\USERS_BAK.RMAN
channel ORA_DISK_1: piece handle=F:\USERS_BAK.RMAN tag=TAG20130606T203154
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 06-JUN-13

这里证明数据库rman有坏块通过rman还原成功,alert日志提示如下

Thu Jun 06 21:29:53 2013
WARNING: The block that appears to be block number 100
         in file 4 is corrupt in backup piece F:\USERS_BAK.RMAN.
         Such blocks would usually be formatted as empty
         in the restored file, but event 19548 has been
         set to include the block as-is in the restored
         file.
Corrupt block 102 found during reading backup piece, file=F:\USERS_BAK.RMAN, corr_type=-2
Reread of blocknum=102, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=102, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=102, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=102, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=102, file=F:\USERS_BAK.RMAN, found same corrupt data
Continuing reading piece F:\USERS_BAK.RMAN, no other copies available.
…………
Corrupt block 258 found during reading backup piece, file=F:\USERS_BAK.RMAN, corr_type=-2
Reread of blocknum=258, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=258, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=258, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=258, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=258, file=F:\USERS_BAK.RMAN, found same corrupt data
Continuing reading piece F:\USERS_BAK.RMAN, no other copies available.
WARNING: some data in the backup of file 4 was missing
         or corrupt.  Event 19549 has been set to allow
         the file to be restored anyway.
           backup header block count: 5369
           backup actual block count: 5212
              backup header checksum: -218250743
              backup actual checksum: 1442665538
Full restore complete of datafile 4 E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF.  Elapsed time: 0:00:25
  checkpoint is 1570136
  last deallocation scn is 1508457

这里rman还原依然遇到很多坏块,但是均跳过坏块,还是完整的恢复出来的数据文件(大小)

rman还原数据文件

RMAN> recover datafile 4;
Starting recover at 06-JUN-13
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 94 is already on disk as file E:\ORACLE\
PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000094_0805223583.0001
archived log for thread 1 with sequence 95 is already on disk as file E:\ORACLE\
PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000095_0805223583.0001
archived log for thread 1 with sequence 96 is already on disk as file E:\ORACLE\
PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000096_0805223583.0001
archived log file name=E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000094_080
5223583.0001 thread=1 sequence=94
media recovery complete, elapsed time: 00:00:00
Finished recover at 06-JUN-13

这里可以明显的看到在recover过程中数据库应用的是备份后的所有归档,数据文件是正常被还原出来(坏块除外)

查询对象

SQL> alter database open;
Database altered.
SQL> conn test/test
Connected.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
STB101                         TABLE
SQL> select count(*) from stb101;
select count(*) from stb101
                     *
ERROR at line 1:
ORA-08103: object no longer exists

dbv检查坏块

e:\oracle\oradata\XIFENFEI>dbv file=USERS01.DBF
DBVERIFY: Release 11.2.0.3.0 - Production on Thu Jun 6 23:59:49 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF
Page 100 is marked corrupt
Corrupt block relative dba: 0x01000064 (file 4, block 100)
Bad check value found during dbv:
Data in bad block:
 type: 30 format: 2 rdba: 0x01000064
 last change scn: 0x0000.00004890 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x48901e01
 check value in block header: 0x8311
 computed block checksum: 0x20
DBVERIFY - Verification complete
Total Pages Examined         : 12320
Total Pages Processed (Data) : 4952
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 7069
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 298

证明设置了event之后，rman确实跳过了备份集中的坏块,而且是直接还原了坏块内容,证明了event 19548和19549作用

补充说明
在非特殊情况下强烈不建议设置相关event跳过rman中的坏块来还原/恢复数据库,这样将对数据的丢失,甚至数据库是否可以正常open不好评估,rman备份重要,确保rman备份可用也很重要.

非归档异常数据库rman备份

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：非归档异常数据库rman备份

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

最近在数据库恢复中遇到一个案例：xx单位1.5T oracle 10.2.0.4(redhat 4.5)，因为异常关闭操作系统，导致数据库不能启动，需要帮忙恢复。该数据库为非归档模式，使用裸设备,一个裸设备文件大小(35G)，数据库文件大小4-30G都有，现在客户要求我们不能对现有环境进行任何操作，需要克隆一份数据库出来，然后在克隆的库上进行数据库恢复操作.数据库环境的克隆最好的方法就是使用rman来完成,但是该数据库为非归档模式,无法直接使用rman进行备份操作.最后采取dd的方式处理(需要注意dd文件大小为block_size*(v$datafile.blocks+1)+v$datafile.offset).因为不能使用rman的一条命令处理,心里一直不舒服,在家里实验,终于还是确定可以通过重建控制文件的方法来欺骗rman是归档模式,来实现rman完成类似工作.
数据库非非归档模式

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> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/oracle/oradata/ora11g/archivelog
Oldest online log sequence     7
Current log sequence           9

非归档模式尝试rman 备份

RMAN> backup database format '/u01/oracle/oradata/orall1g_%U';
Starting backup at 22-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 01/22/2013 16:10:49
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
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
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 22-JAN-13
channel ORA_DISK_1: finished piece 1 at 22-JAN-13
piece handle=/u01/oracle/oradata/orall1g_13o02k8a_1_1 tag=TAG20130122T161048 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

rman的backup or copy命令不能在非归档模式下执行

尝试修改数据库为归档模式

SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

数据库非正常关闭,不能修改归档模式

重建控制文件

SQL> alter database backup controlfile to trace as '/tmp/ctl';
Database altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
--备份当前控制文件(保留控制文件现场)
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area  313860096 bytes
Fixed Size                  1344652 bytes
Variable Size             285215604 bytes
Database Buffers           20971520 bytes
Redo Buffers                6328320 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u01/oracle/oradata/ora11g/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/u01/oracle/oradata/ora11g/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/u01/oracle/oradata/ora11g/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12    '/u01/oracle/oradata/ora11g/system01.dbf',
 13    '/u01/oracle/oradata/ora11g/sysaux01.dbf',
 14    '/u01/oracle/oradata/ora11g/users01.dbf',
 15    '/u01/oracle/oradata/ora11g/dbfs01.dbf',
 16    '/u01/oracle/oradata/ora11g/tts_xifenfei02.dbf',
 17    '/u01/oracle/oradata/ora11g/tts_xifenfei01.dbf',
 18    '/u01/oracle/oradata/ora11g/system02.dbf',
 19    '/u01/oracle/oradata/ora11g/czum01.dbf',
 20    '/u01/oracle/oradata/ora11g/undotbs02.dbf'
 21  CHARACTER SET ZHS16GBK
 22  ;
Control file created.

数据库已经变为归档模式

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Disabled
Archive destination            /u01/oracle/oradata/ora11g/archivelog
Oldest online log sequence     7
Next log sequence to archive   7
Current log sequence           9

归档模式尝试rman备份

RMAN> backup datafile 1 format '/u01/oracle/oradata/system01_%U';
Starting backup at 22-JAN-13
using channel ORA_DISK_1
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
channel ORA_DISK_1: starting piece 1 at 22-JAN-13
channel ORA_DISK_1: finished piece 1 at 22-JAN-13
piece handle=/u01/oracle/oradata/system01_02o02kl7_1_1 tag=TAG20130122T161742 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 22-JAN-13
RMAN-06497: WARNING: control file is not current, control file AUTOBACKUP skipped

因为现在的控制文件是新创建的，不能算是数据库的当前控制文件,所以未被rman自动备份(很好理解，重建控制文件后，我们做恢复都要使用using backup controlfile命令)

总结说明
1.数据库为非归档模式,不能使用rman的backup和copy命令来备份
2.因为数据库为非正常关闭不能直接修改为归档模式
3.通过重建控制文件修改数据库(注意备份)为归档模式实现rman正常备份
4.当rman备份好之后，使用原先控制文件替换现在控制文件

关于blockrecover 解决坏块相关测试与总结

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：关于blockrecover 解决坏块相关测试与总结

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

悲剧的客户因为IBM p系列小机更换电源导致主机直接掉电,起来后发现数据库出现不少坏块,而且还有部分坏块中含有回滚事务,导致alert日志一直报smon回滚遇到坏块错误，该数据库版本是9.2.0.8 RAC,根据客户的备份情况,为了减少对业务的影响,决定使用blockrecover对其处理.这里通过10g数据库大概模拟出现含事务坏块的情况以及处理过程,重现了我们在处理的时候不确定的一些知识.
创建测试表

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> conn /as sysdba
Connected.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/XFF/archivelog
Oldest online log sequence     231
Next log sequence to archive   233
Current log sequence           233
SQL> conn chf/xifenfei
Connected.
SQL> create table t_xifenfei
  2  as
  3  select * from dba_objects where rownum<10;
Table created.
SQL> select rowid,
  2  dbms_rowid.rowid_relative_fno(rowid) rel_fno,
  3  dbms_rowid.rowid_block_number(rowid) block
  4  from chf.t_xifenfei;
ROWID                 REL_FNO      BLOCK
------------------ ---------- ----------
AAANIqAAEAAAAAcAAA          4         28
AAANIqAAEAAAAAcAAB          4         28
AAANIqAAEAAAAAcAAC          4         28
AAANIqAAEAAAAAcAAD          4         28
AAANIqAAEAAAAAcAAE          4         28
AAANIqAAEAAAAAcAAF          4         28
AAANIqAAEAAAAAcAAG          4         28
AAANIqAAEAAAAAcAAH          4         28
AAANIqAAEAAAAAcAAI          4         28
9 rows selected.

当前的seq是233(也就是说我在233归档上创建了t_xienfei表)

dbv检查block

[oracle@xifenfei ~]$ dbv file='/u01/oracle/oradata/XFF/users01.dbf'
DBVERIFY: Release 10.2.0.4.0 - Production on Sun Jan 20 22:16:16 2013
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/XFF/users01.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 1280
Total Pages Processed (Data) : 904
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 38
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 44
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 294
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 3224018224 (2860.3224018224)

证明无任何坏块

切换归档

SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/XFF/archivelog
Oldest online log sequence     233
Next log sequence to archive   235
Current log sequence           235

现在已经切换seq到235

rman备份我们需要测试block(file 4 block 28)对应的数据文件

RMAN>  backup datafile 4 format '/u01/oracle/oradata/xff_4.rman';

具体见:rman制造坏块,bbed修复坏块

模拟数据库进行其他操作

SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/XFF/archivelog
Oldest online log sequence     235
Next log sequence to archive   237
Current log sequence           237
SQL> conn chf/xifenfei
Connected.
SQL> create table t_xff
  2  as
  3  select * from dba_objects;
Table created.
SQL> alter system switch logfile;
System altered.

主要是为了模拟对其他block操作,对于block 28的恢复影响

对block 28进行操作

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/XFF/archivelog
Oldest online log sequence     237
Next log sequence to archive   239
Current log sequence           239
SQL> update chf.t_xifenfei set object_name='www.xifenfei.com';
9 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.

在seq为239的时候对block 28进行了一次update操作

模拟其他业务操作

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/XFF/archivelog
Oldest online log sequence     240
Next log sequence to archive   242
Current log sequence           242
SQL> delete from chf.t_xff ;
50491 rows deleted.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/XFF/archivelog
Oldest online log sequence     242
Next log sequence to archive   244
Current log sequence           244
SQL>  alter system switch logfile;
System altered.
SQL> /
System altered.

这里可以知道在seq为246的时候做了备份归档操作

备份归档操作

RMAN> backup archivelog all format '/u01/oracle/oradata/xff_arch_%U' delete input;

模拟继续操作

SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/XFF/archivelog
Oldest online log sequence     248
Next log sequence to archive   250
Current log sequence           250
SQL> update chf.t_xifenfei set object_name='www.orasos.com' where rownum<5;
4 rows updated.

这里可以发现,在seq为250的时候我们再次对block 28进行了操作

使用rman制造坏块

RMAN> BLOCKRECOVER DATAFILE 4 block 28 clear;

dbv检查坏块

[oracle@xifenfei ~]$ dbv file='/u01/oracle/oradata/XFF/users01.dbf'
DBVERIFY: Release 10.2.0.4.0 - Production on Sun Jan 20 23:01:24 2013
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/XFF/users01.dbf
Page 28 is influx - most likely media corrupt
Corrupt block relative dba: 0x0100001c (file 4, block 28)
Fractured block found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x0100001c
 last change scn: 0x0b2c.c02ab081 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xb0c4a6ea
 check value in block header: 0x393f
 computed block checksum: 0xc917
DBVERIFY - Verification complete
Total Pages Examined         : 1280
Total Pages Processed (Data) : 903
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 38
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 47
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 291
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 1
Highest block SCN            : 3224022228 (2860.3224022228)

强制kill数据库

[oracle@xifenfei ~]$ ps -ef|grep pmon
oracle    9744  9638  0 23:03 pts/1    00:00:00 grep pmon
oracle   32156     1  0 14:17 ?        00:00:10 ora_pmon_XFF
[oracle@xifenfei ~]$ kill -9 32156
[oracle@xifenfei ~]$ ps -ef|grep pmon
oracle    9751  9638  0 23:03 pts/1    00:00:00 grep pmon

为了模拟含事务的block出现坏块

启动数据库

SQL> startup mount
ORACLE instance started.
Total System Global Area  306184192 bytes
Fixed Size                  1267164 bytes
Variable Size             109054500 bytes
Database Buffers          188743680 bytes
Redo Buffers                7118848 bytes
Database mounted.
SQL> ALTER DATABASE OPEN;
Database altered.

数据库启动正常

查询坏块

SQL> select count(*) from chf.t_xifenfei;
select count(*) from chf.t_xifenfei
                         *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 28)
ORA-01110: data file 4: '/u01/oracle/oradata/XFF/users01.dbf'

alert日志

Sun Jan 20 23:04:37 2013
SMON: enabling tx recovery
Sun Jan 20 23:04:37 2013
Database Characterset is ZHS16GBK
Sun Jan 20 23:04:37 2013
Hex dump of (file 4, block 28) in trace file /u01/oracle/admin/XFF/bdump/xff_smon_9775.trc
Corrupt block relative dba: 0x0100001c (file 4, block 28)
Fractured block found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x0100001c
 last change scn: 0x0b2c.c02ab081 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xb0c4a6ea
 check value in block header: 0x393f
 computed block checksum: 0xc917
Reread of rdba: 0x0100001c (file 4, block 28) found same corrupted data
ORACLE Instance XFF (pid = 8) - Error 1578 encountered while recovering transaction (9, 37) on object 53802.
Sun Jan 20 23:04:38 2013
Errors in file /u01/oracle/admin/XFF/bdump/xff_smon_9775.trc:
ORA-01578: ORACLE data block corrupted (file # 4, block # 28)
ORA-01110: data file 4: '/u01/oracle/oradata/XFF/users01.dbf'

通过试验步骤和alert日志可以发现因为block有事务,但是被标记为了坏块,所以smon无法回滚该事务,从而出现alert中类似提示

继续切换归档

SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/XFF/archivelog
Oldest online log sequence     252
Next log sequence to archive   254
Current log sequence           254

移走归档

[oracle@xifenfei archivelog]$ ls -l
total 2224
-rw-r----- 1 oracle oinstall  360960 Jan 20 22:59 1_247_792679299.dbf
-rw-r----- 1 oracle oinstall    1024 Jan 20 22:59 1_248_792679299.dbf
-rw-r----- 1 oracle oinstall 1630208 Jan 20 23:04 1_249_792679299.dbf
-rw-r----- 1 oracle oinstall  249344 Jan 20 23:09 1_250_792679299.dbf
-rw-r----- 1 oracle oinstall    1024 Jan 20 23:09 1_251_792679299.dbf
-rw-r----- 1 oracle oinstall    4608 Jan 20 23:09 1_252_792679299.dbf
-rw-r----- 1 oracle oinstall    1024 Jan 20 23:09 1_253_792679299.dbf
[oracle@xifenfei archivelog]$ mkdir bak
[oracle@xifenfei archivelog]$ mv *.dbf bak
[oracle@xifenfei archivelog]$ ll
total 4
drwxr-xr-x 2 oracle oinstall 4096 Jan 20 23:11 bak

为了重现,当我们使用blockrecover恢复的时候,如果缺少归档会怎么样

尝试blockrecover

RMAN> BLOCKRECOVER DATAFILE 4 block 28;
Starting blockrecover at 20-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=135 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=126 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=125 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=124 devtype=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/xff_4.rman
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/u01/oracle/oradata/xff_4.rman tag=TAG20130120T222333
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 01/20/2013 23:11:41
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 253 lowscn 12286830510311 found to restore
RMAN-06025: no backup of log thread 1 seq 252 lowscn 12286830510307 found to restore
RMAN-06025: no backup of log thread 1 seq 251 lowscn 12286830510305 found to restore
RMAN-06025: no backup of log thread 1 seq 250 lowscn 12286830509979 found to restore
RMAN-06025: no backup of log thread 1 seq 249 lowscn 12286830489543 found to restore
RMAN-06025: no backup of log thread 1 seq 248 lowscn 12286830489541 found to restore
RMAN-06025: no backup of log thread 1 seq 247 lowscn 12286830489279 found to restore

alert日志记录

Sun Jan 20 23:11:38 2013
alter database recover datafile list clear
Sun Jan 20 23:11:38 2013
Completed: alter database recover datafile list clear
Sun Jan 20 23:11:38 2013
Starting block media recovery
Sun Jan 20 23:11:39 2013
Media Recovery Log /u01/oracle/oradata/XFF/archivelog/1_235_792679299.dbf
Sun Jan 20 23:11:41 2013
alter database recover cancel
Sun Jan 20 23:11:41 2013
Media Recovery Canceled
Completed: alter database recover cancel

blockrecover恢复途中或者异常终止,dbv检测

[oracle@xifenfei ~]$ dbv file=/u01/oracle/oradata/XFF/users01.dbf
DBVERIFY: Release 10.2.0.4.0 - Production on Sun Jan 20 23:18:29 2013
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/XFF/users01.dbf
Page 28 is marked corrupt
Corrupt block relative dba: 0x0100001c (file 4, block 28)
Bad check value found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x0100001c
 last change scn: 0x0b2c.c02b0248 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x02480601
 check value in block header: 0x13fc
 computed block checksum: 0x663b
DBVERIFY - Verification complete
Total Pages Examined         : 1280
Total Pages Processed (Data) : 903
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 38
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 47
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 291
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 3224022228 (2860.3224022228)

在blockrecover处理过程，或者处理失败的后,block依然是坏块，如果遇到这类情况,没有事务可以直接指定seq/scn/time的方法来恢复,如果有事务,需要指定恢复时间点过该事务的时间点,让smon能够正常回滚,从而使得smon进程正常工作

还原归档后继续测试

[oracle@xifenfei archivelog]$ mv bak/* ./
RMAN> BLOCKRECOVER DATAFILE 4 block 28;
starting media recovery
archive log thread 1 sequence 247 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_247_792679299.dbf
…………
media recovery complete, elapsed time: 00:00:01
Finished blockrecover at 20-JAN-13
[oracle@xifenfei ~]$ dbv file=/u01/oracle/oradata/XFF/users01.dbf
DBVERIFY: Release 10.2.0.4.0 - Production on Sun Jan 20 23:15:43 2013
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/XFF/users01.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 1280
Total Pages Processed (Data) : 904
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 38
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 47
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 291
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 3224023169 (2860.3224023169)

>
blockrecover处理alert日志

Sun Jan 20 23:15:01 2013
Media Recovery Log /u01/oracle/oradata/XFF/archivelog/1_250_792679299.dbf
Sun Jan 20 23:15:01 2013
Media Recovery Log /u01/oracle/oradata/XFF/archivelog/1_251_792679299.dbf
Sun Jan 20 23:15:01 2013
Recovery of Online Redo Log: Thread 1 Group 3 Seq 252 Reading mem 0
  Mem# 0: /u01/oracle/oradata/XFF/redo03.log
Sun Jan 20 23:15:01 2013
Recovery of Online Redo Log: Thread 1 Group 1 Seq 253 Reading mem 0
  Mem# 0: /u01/oracle/oradata/XFF/redo01.log
Sun Jan 20 23:15:01 2013
Recovery of Online Redo Log: Thread 1 Group 2 Seq 254 Reading mem 0
  Mem# 0: /u01/oracle/oradata/XFF/redo02.log
Sun Jan 20 23:15:02 2013
Completed block media recovery

补充说明
1.在9i中使用blockrecover会在Archive destination中生产block的备份文件,类似469_519791_3063_2442393528.bkd(file 469 block 519791),需要注意Archive destination目录结尾需要”/”,不然可能出现直接写入和该目录并列的Archive destination+blockrecover产生文件;10g中不生成该文件;如果该block在9i中未备份,也不会在生产相关文件,而是利用归档恢复.
2.blockrecover需要还原从该block从备份之后的所有归档(如果被备份起来需要还原出来),如果缺少归档可能导致恢复失败,包括基于scn/seq/time的等
3.blockrecover在执行过程中或者执行异常终止,该block依然是坏块,不会对其他block产生影响.在使用blockrecover出现异常终止后的block修复,在后续blog中提供解决方法
4.对于不含事务的坏块,如果数据允许丢失可以通过设置event跳过坏块(特殊block除外),然后重建对象;对于含event的block 坏块,建议使用blockrecover处理或者直接恢复数据文件,如果没有备份,考试使用event跳过事务回滚,然后屏蔽坏块处理