非归档异常数据库rman备份

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

标题:非归档异常数据库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备份好之后,使用原先控制文件替换现在控制文件

此条目发表在 rman备份/恢复 分类目录。将固定链接加入收藏夹。

非归档异常数据库rman备份》有 4 条评论

  1. 惜分飞 说:

    更正数据库非归档,正常关闭,可以使用rman备份

    RMAN> alter database open;
    
    database opened
    
    RMAN> shutdown immediate
    
    database closed
    database dismounted
    Oracle instance shut down
    
    RMAN> startup mount;
    
    connected to target database (not started)
    Oracle instance started
    database mounted
    
    Total System Global Area    2488635392 bytes
    
    Fixed Size                     2230912 bytes
    Variable Size                553649536 bytes
    Database Buffers            1895825408 bytes
    Redo Buffers                  36929536 bytes
    
    RMAN> backup datafile 2;
    
    Starting backup at 16-APR-13
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=105 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=00002 name=/data/oracle/oradata/test/sysaux01.dbf
    channel ORA_DISK_1: starting piece 1 at 16-APR-13
    channel ORA_DISK_1: finished piece 1 at 16-APR-13
    piece handle=/data/o1_mf_nnndf_TAG20130416T213329_8ptns948_.bkp tag=TAG20130416T213329 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
    Finished backup at 16-APR-13
    
    

    数据库非归档,非正常关闭,启动到mount不能备份

    RMAN> shutdown abort
    
    Oracle instance shut down
    
    RMAN> startup mount
    
    connected to target database (not started)
    Oracle instance started
    database mounted
    
    Total System Global Area    2488635392 bytes
    
    Fixed Size                     2230912 bytes
    Variable Size                553649536 bytes
    Database Buffers            1895825408 bytes
    Redo Buffers                  36929536 bytes
    
    RMAN>  backup datafile 2;
    
    Starting backup at 16-APR-13
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=105 device type=DISK
    channel ORA_DISK_1: starting full datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/16/2013 21:29:25
    ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
    
  2. digdeep 说:

    这个很牛逼。尽然可以欺骗rman.

  3. 惜分飞 说:

    使用dbms_backup_restore.copydatafile命令

    select 'declare   full_name varchar2(256);   recid     number;   stamp     
    number; begin   sys.dbms_backup_restore.copydatafile('
    ||file#||','''||'/u01/oracle/oradata/'||file#||'_'
    ||SUBSTR(NAME,INSTR(NAME,'/',-1)+1)||''',full_name, recid, 
    stamp);end;' from v$datafile;
    
  4. 惜分飞 说:

    非归档模式下,dbms_backup_restore包也不能实现backup/copy类似功能

    SQL> declare   
    full_name 
    varchar2(256);  
     recid     number;  
     stamp     number; 
    begin   sys.dbms_backup_restore.copydatafile(1,
    '/u01/oracle/oradata/1_system01.dbf',full_name, recid, stamp);end;
     /
    
    *
    ERROR at line 1:
    ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
    ORA-19600: input file is datafile 1 ()
    ORA-19601: output file is datafile-copy 0 (/u01/oracle/oradata/1_system01.dbf)
    ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 1778
    ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 1728
    ORA-06512: at line 1
    
    
    SQL> archive log list;
    Database log mode              No Archive Mode
    Automatic archival             Disabled
    Archive destination            /u01/oracle/oradata/ora11g/archivelog
    Oldest online log sequence     1
    Current log sequence           1