rman备份到win共享目录

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

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

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

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


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

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

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

3.数据库备份脚本
20190926222130

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

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

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

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坏块恢复

在有些情况下,我们仅有一份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备份

最近在数据库恢复中遇到一个案例: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 解决坏块相关测试与总结

悲剧的客户因为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.orasos.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跳过事务回滚,然后屏蔽坏块处理