• 博客访问: 446612
  • 博文数量: 193
  • 用 户 组: 普通用户
  • 注册时间: 2010-05-18 15:56
个人简介

暂无介绍

文章分类
文章存档

2014年(1)

2013年(3)

2012年(59)

2011年(98)

2010年(32)

我的朋友

分类: Linux操作系统

2011-07-29 14:42:30

基于catalog 的RMAN 备份与恢复
分类: RMAN 备份恢复 276人阅读 评论(0) 收藏 举报

--=================================

-- 基于catalog RMAN 备份与恢复

--=================================

 

    RMAN的备份与恢复存储仓库的数据通常存放于控制文件或恢复目录中,本文主要讲述基于catalog的备份与恢复。

    关于catalog的创建请参考:RMAN catalog的创建和使用

 

    catalog方式的RMAN备份与恢复只不过是将备份恢复信息数据放在catalog目录内,普通的rman方式则是存放在控制文件中

    catalog方式可以存储常用或特定的备份与恢复的脚本

    有关catalog方式RMAN存储脚本请参考:基于 catalog 创建RMAN存储脚本

   

一、基于catalog来备份数据库(目标数据库orcl,恢复目录数据库asmdb)   

    首先基于catalog创建备份脚本

    --连接到RMAN

        [oracle@oradb ~]$ rman target sys/redhat@orcl catalog rman/rman@asmdb

        connected to target database: ORCL (DBID=1263182651)

        connected to recovery catalog database 

   

    --创建全局删除废弃备份的脚本

        RMAN> create global script. global_del_obso  comment 'A script. for obsolete backup and delete it' {

        2> allocate channel ch1 device type disk;

        3> delete obsolete recovery window of 7 days; 

        4> release channel ch1;

        5> }

 

        new incarnation of database registered in recovery catalog

        starting full resync of recovery catalog

        full resync complete

        created global script. global_del_obso  

   

    --创建全局归档日志的备份脚本

        RMAN> create global script. global_arch

        2> comment ' A script. for archivelog and delete it '

        3> {

        4>   allocate channel ch1 device type disk;

        5>   sql " alter system archive log current";

        6>   set limit channel ch1 readrate=10240;

        7>   set limit channel ch1 kbytes=2048000;

        8>   backup as compressed backupset archivelog all delete input

        9>   format='/u01/bk/rmbk/arch_%d_%U'

        10>  tag='Archbk';

        11>  release channel ch1;

        12> }

 

        created global script. global_arch

 

    --创建全局0级增量备份脚本  

        RMAN> create global script. global_inc0

        2> comment ' A script. for backup database using incremental level 0'

        3> {

        4>   execute global script. global_del_obso;

        5>   allocate channel ch1 device type disk;

        6>   set limit channel ch1 readrate=10240;

        7>   set limit channel ch1 kbytes=2048000;

        8>   backup as compressed backupset incremental level 0 database

        9>   format='/u01/bk/rmbk/inc0_%d_%U'

        10>  tag='Inc0';

        11>  release channel ch1;

        12>  execute global script. global_arch;

        13> }

 

        created global script. global_inc0

   

    --列出已经创建的全局脚本

        RMAN> list global script. names;

 

        List of Stored Scripts in Recovery Catalog

 

            Global Scripts

 

               Script. Name

               Description

               -----------------------------------------------------------------------

               global_arch

                A script. for archivelog and delete it

 

               global_del_obso

                A script. for obsolete backup and delete it

 

               global_inc0

                A script. for backup database using incremental level 0

   

    --启用控制文件的自动备份功能

        RMAN> configure controlfile autobackup on;

        RMAN> configure controlfile autobackup format for device type disk to '/u01/bk/rmbk/auto_ctl_%d_%f';

   

    --删除以前的备份

        RMAN> delete noprompt backupset;

   

    --备份前验证归档日志是否存在

        RMAN> crosscheck archivelog all;

       

    --删除无效的归档日志信息

        RMAN> delete noprompt expired archivelog all;

       

    --使用0级增量备份数据库

        RMAN> run { execute global script. global_inc0;}

 

    --查看刚刚完成的备份情况

        RMAN> list backupset summary;

        RMAN> list backupset ;

        RMAN> list backup of controlfile ;

        RMAN> list backup of archivelog all;

        RMAN> list backup of database;

        RMAN> list backup of datafile n ;

       

二、恢复操作       

    1.非系统表空间的恢复步骤

        a. alter datafile n offline immediate; | alter tablespace tbs_name offline immediate;

        b. restore

        c. recover

        d. alter datafile n online; | alter tablespace tbs_name online

       

        --删除非系统表空间users的数据文件(数据库位于open 状态) 

            [oracle@oradb orcl]$ pwd

            /u01/oracle/oradata/orcl

            [oracle@oradb orcl]$ rm users01.dbf    

 

            lion@ORCL> select * from tb2;

            select * from tb2

                          *

            ERROR at line 1:

            ORA-01116: error in opening database file 4

            ORA-01110: data file 4: '/u01/oracle/oradata/orcl/users01.dbf'

            ORA-27041: unable to open file

            Linux Error: 2: No such file or directory

            Additional information: 3

 

            RMAN> run {

            2> allocate channel ch1 device type disk;

            3> sql " alter tablespace users offline immediate "; 

            4> restore tablespace users;

            5> recover tablespace users;

            6> sql " alter tablespace users online ";

            7> }

 

            lion@ORCL> select * from tb2;

 

                    ID NAME

            ---------- ---------------

                     1 Robinson

 

            也可以使用下面的恢复方式来完成恢复     

            RMAN> run {

            2> allocate channel ch1 device type disk;

            3> sql " alter database datafile 4 offline ";

            4> restore datafile 4;

            5> recover datafile 4;

            6> sql " alter database datafile 4 online ";

            7> }

 

        如果介质恢复时,需要用的日志尚未归档,需要指定联机重做日志文件所在的位置       

        注意

        表空间几种不同的脱机方式

            offline normal :

                表空间脱机的缺省方式,将位于SGA中且与该表空间所有的数据文件相关的数据块(blocks)写入到数据文件之后再进行脱机

                再次联机时不需要做介质恢复。

            offline temporary :

                实施检查点进程,即同样将SGA中且与该表空间所有的数据文件相关的数据块(blocks)写入到数据文件之后再进行脱机

                不保证所有的数据能够写入到数据文件。再次联机时要做介质恢复。

            offline immediate :

                不保证表空间的数据可用,也不实施检查点进程而直接脱机,再次联机时要做介质恢复。

               

            offline temporary offline immediate 脱机方式不适用于数据文件

       

            lion@ORCL> alter database datfile 4 offline immediate;

            alter database datfile 4 offline immediate

                                   *

            ERROR at line 1:

            ORA-02231: missing or invalid option to ALTER DATABASE

 

 

            lion@ORCL> alter database datfile 4 offline temporary;

            alter database datfile 4 offline temporary

                                   *

            ERROR at line 1:

            ORA-02231: missing or invalid option to ALTER DATABAS

       

            更多关于表空间与数据文件的管理,请参考:Oracle 表空间与数据文件

        对于表空间存在多个数据文件的情况,而单个或较少的数据文件受损,应尽可能使用第二种方式来恢复.resotre & recover datafile n

 

    2.UNDO表空间的恢复

        关于UNDO表空间的管理请参考:Oracle 回滚(ROLLBACK)和撤销(UNDO)

        恢复步骤(undo丢失后)

            alter database datafile 2 online;

            alter database datafile 2 offline;

            select * from v$recover_file;

            restore datafile 2;   --RMAN 完成

             

            recover datafile 2 ; 

            alter database datafile 2 online;

           

           

        --首先做一些操作,将数据填充到undo表空间

            lion@ORCL> select * from tb2;

 

                    ID NAME

            ---------- ---------------

                     1 Jack

 

            lion@ORCL> insert into tb2 select 2,'Jackson' from dual;

 

            lion@ORCL> commit;

 

            lion@ORCL> delete from tb2 where id=1;

 

            lion@ORCL> select * from tb2;

 

                    ID NAME

            ---------- ---------------

                     2 Jackson

                     

            lion@ORCL> ho rm $ORACLE_BASE/oradata/orcl/undotbs01.dbf

 

        注意:undo表空间不能被offline,也不能被readonly

            使用alter database datafile 2 online | offline强制执行该步骤,以告知oracle undo表空间被损坏,执行后会话被终止,如下

 

            lion@ORCL> alter database datafile 2 online;

            alter database datafile 2 online

            *

            ERROR at line 1:

            ORA-01116: error in opening database file 2

            ORA-01110: data file 2: '/u01/oracle/oradata/orcl/undotbs01.dbf'

            ORA-27041: unable to open file

            Linux Error: 2: No such file or directory

            Additional information: 3

 

            lion@ORCL> alter database datafile 2 offline;

            ERROR:

            ORA-03114: not connected to ORACLE

 

            alter database datafile 2 offline

            *

            ERROR at line 1:

            ORA-00603: ORACLE server session terminated by fatal error

   

        --接下面再来进行恢复

 

            RMAN> run {

            2> allocate channel ch1 device type disk;

            3> restore datafile 2;

            4> recover datafile 2;}

 

            starting media recovery            --还原被成功执行,介质恢复失败,且会话被终止

            media recovery failed

            ORA-00603: ORACLE server session terminated by fatal error

 

        --重新登录到数据库服务器并查看v$recover_file视图以及进行介质恢复

            sys@ORCL> select * from v$recover_file;

 

                 FILE# ONLINE  ONLINE_ ERROR              CHANGE# TIME

            ---------- ------- ------- --------------- ---------- ---------

                     2 OFFLINE OFFLINE UNKNOWN ERROR       493982 23-NOV-10

         

            sys@ORCL> recover datafile 2;    --进行介质恢复,恢复后可以看到提示回话被终止

            ORA-00603: ORACLE server session terminated by fatal error

 

            sys@ORCL> conn / as sysdba   --再次连接

            Connected.

            sys@ORCL> select name,status from v$datafile where name like '%undo%';  --undo表空间已经处于offline状态

 

            NAME                                          STATUS

            --------------------------------------------- -------

            /u01/oracle/oradata/orcl/undotbs01.dbf        OFFLINE

 

            sys@ORCL> alter database datafile 2 online;

 

            Database altered.

 

            sys@ORCL> select * from lion.tb2;

                    ID NAME

            ---------- ---------------------------------------------

                     2 Jackson

 

        可以看到表中ID为的记录已经丢失,事实上在undo的数据文件丢失前,该事务并没有提交,由此可以推断,该事务进行了隐式提交.

                     

    3.系统表空间的恢复(system ,sysaux)

        系统表空间只能在Mount状态下来完成恢复,步骤如下

            startup mount;

            restore datafile 1;

            recover datafile 1;

            alter database open;

        --创建新表tb3,并插入记录。其数据字典信息则位于system表空间,数据内容位于users表空间

            lion@ORCL> create table tb3 tablespace users as select * from tb2;

 

            lion@ORCL> insert into tb3 select 1,'Johnson' from dual;

 

            lion@ORCL> commit;

 

            lion@ORCL> select * from tb3 order by id;

 

                    ID NAME

            ---------- ---------------------------------------------

                     1 Johnson

                     2 Jackson

        --删除表空间system01.dbf  sysaux01.dbf           

            sys@ORCL> ho rm $ORACLE_BASE/oradata/orcl/system01.dbf

            sys@ORCL> ho rm $ORACLE_BASE/oradata/orcl/sysaux01.dbf     

            sys@ORCL> startup mount force; 

            RMAN> run {

            2> allocate channel ch1 device type disk;

            3> restore  datafile 1,3;

            4> recover  datafile 1,3;

            5> alter database open;

            6> release channel ch1;}

 

            sys@ORCL> select * from lion.tb3 order by id;  --成功恢复后表tb3也被恢复

 

                    ID NAME

            ---------- ---------------------------------------------

                     1 Johnson

                     2 Jackson

                     

    4.控制文件的恢复

        步骤

            connect to target db and catalog(nocatalog) db

            startup nomount

            restore controlfile [from autobackup]

            alter database mount

            recover database

            alter database open resetlogs

           

        由于控制文件采取了自动备份策略,因此在每次备份或重大系统结果发生变化时,控制文件将被自动备份

           

            sys@ORCL> ho rm $ORACLE_BASE/oradata/orcl/*.ctl                --删除所有的控制文件*/

           

            sys@ORCL> select file#,status from v$datafile;   --查看v$datafile视图时,系统已检测到错误发生

            select file#,status from v$datafile

                                     *

            ERROR at line 1:

            ORA-00210: cannot open the specified control file

            ORA-00202: control file: '/u01/oracle/oradata/orcl/control01.ctl'

            ORA-27041: unable to open file

            Linux Error: 2: No such file or directory

            Additional information: 3

           

            sys@ORCL> shutdown abort;

           

            --重新连接到RMAN,注意连接target时使用/,否则提示TNS无法解析

            [oracle@oradb dbs]$ uniread rman target / catalog rman/rman@asmdb  

 

            connected to target database: orcl (not started)

            connected to recovery catalog database     

           

            RMAN> startup nomount;

            RMAN> run {

            2> allocate channel ch1 device type disk;

            3> restore controlfile;

            4> sql " alter database mount ";

            5> recover database;

            6> sql " alter database open resetlogs ";

            7> release channel ch1;}

             

        注:在此处有可能不要介质恢复,如果提示需要介质恢复,直接在RMANSQLPlus下执行recover database即可

            使用open resetlogs之后,一个新的incarnation将被生成,再此建议立即全备数据库。

       

        更多关于控制文件

            Oracle 控制文件(CONTROLFILE)

            Oracle 控制文件的备份与恢复

   

    5.联机重做日志文件的恢复(online redo log )

        当数据库置为mount状态,且将要转换为open状态时,数据文件,联机日志文件被打开,因此联机日志的丢失可以在mount状态完成

        恢复步骤

            a. 启动到mount状态(startup mount force)

            b. 还原数据库(restore database)

            c. 恢复数据库(recover database)

       

        下面对删除日志并进行恢复

            lion@ORCL> select * from tb2;

 

                    ID NAME

            ---------- ---------------

                     2 Jackson

 

            lion@ORCL> select current_scn from v$database;   --查看数据库当前的SCN

 

            CURRENT_SCN

            -----------

                1020638

 

            lion@ORCL> insert into tb2 select 1,'Johnson' from dual;   --为表tb2新增一条记录

 

            lion@ORCL> commit;

 

            lion@ORCL> select current_scn from v$database;             --数据库当前的SCN发生了变化为

 

            CURRENT_SCN

            -----------

                1020685

               

            lion@ORCL> select file#, checkpoint_change# from v$datafile_header;  --数据文件头部的checkpoint_change

 

                 FILE# CHECKPOINT_CHANGE#

            ---------- ------------------

                     1            1020368

                     2            1020368

                     3            1020368

                     4            1020368

                     5            1020368

                     6            1020368      

                     

            lion@ORCL> ho rm -f $ORACLE_BASE/oradata/orcl/*.log      --删除所有的日志文件  */

                       

            lion@ORCL> insert into tb2 select 2,'wilson' from dual;    --为表插入新记录

 

            lion@ORCL> commit;                                        

 

            lion@ORCL> select current_scn from v$database;            --数据库当前的SCN发生了变化为

 

            CURRENT_SCN

            -----------

                1020708        

 

            lion@ORCL> alter system archive log current;              --对日志进行归档时提示错误发生

            alter system archive log current

            *

            ERROR at line 1:

            ORA-16038: log 1 sequence# 1 cannot be archived

            ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/orcl/redo01.log'

               

            lion@ORCL> conn / as sysdba

 

            sys@ORCL> startup mount force;

 

            [oracle@oradb ~]$ uniread rman target / catalog rman/rman@asmdb    --退出RMAN后并重新连接

            RMAN> run {

            2> allocate channel ch1 device type disk;

            3> restore database;

            4> recover database;

            5> release channel ch1;}

 

            RMAN-06054: media recovery requesting unknown log: thread 1 seq 1 lowscn 1020365

 

            sys@ORCL> recover database until cancel;                          --回到SQLPlus直接使用until cancel来进行恢复

 

            sys@ORCL> alter database open resetlogs;                          --执行opensetlogs打开数据库

 

            sys@ORCL> select * from lion.tb2;                          --在日志未完成自动归档前,删除日志的后数据全部丢失

 

                    ID NAME

            ---------- ---------------

                     2 Jackson

                     

        关于单个日志文件丢失或日志文件组受损,请参考:Oracle 联机重做日志文件(ONLINE LOG FILE)     

       

    6.所有数据全部丢失的处理

        步骤

            a.启动到nomount状态(startup nomount)

            b.还原控制文件(restore controlfile from autobackup)

            c.还原数据(restore database)

            d.将数据切换到mount状态(alter database mount)

            e.恢复数据库(recover database using backup controlfile until cancel)

            f.使用open resetlogs打开数据库(alter database open resetlogs)

           

        --下面演示数据文件、日志文件、控制文件全部丢失的处理

            sys@ORCL> ho rm $ORACLE_BASE/oradata/orcl/*           --删除所有的数据文件、日志文件、控制文件*/

 

            sys@ORCL> startup nomount ;

           

            [oracle@oradb ~]$ uniread rman target / catalog rman/rman@asmdb

            RMAN> startup nomount;     

            RMAN> restore controlfile from autobackup; 

            RMAN> run {

            2> allocate channel ch1 device type disk;

            3> restore database ;

            4> sql " alter database mount ";

            5> recover database ;}

 

            RMAN-06004: ORACLE error from recovery catalog database: RMAN-20003: target database incarnation not found in

                recovery catalog

 

            idle> recover database using backup controlfile until cancel;    --使用SQLPlus来完成恢复操作

 

            idle> alter database open resetlogs;

阅读(1994) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册