ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 接着Oracle的RMAN练习,恢复练习够费劲的,一天都搭进去了……

接着Oracle的RMAN练习,恢复练习够费劲的,一天都搭进去了……

原创 Linux操作系统 作者:louloueva 时间:2009-01-16 21:07:45 0 删除 编辑

因复习Oracle和AIX笔记,RMAN停滞了一天
话说昨天看自己的笔记,发现到现在已经几万字了 ◎◎
不过,有一部分是非技术语言,大多是个人用于抱怨、感慨的词句
每次温习的时间在不断增长……看来自己的记忆力还是不行
没法过目不忘啊……言归正传,开始练习

今天来试验一下数据库恢复
做试验之前,已经对各个数据库文件进行了冷备份
包括全部数据文件、控制文件、redo log、参数文件、密码文件
归档没要,不少呢,虚拟机空间不到1G了……冷恢复也不是必须要

现来走一遍全数据库在线备份,并详细叙述一些
命令就用最简单的,并加上format定义
rman>backup database format='/home/oracle/dumptest/%U';
使用此命令RMAN会以备份集形式备份数据库对象
在备份完数据文件后,还会再备份控制文件和服务器参数文件
RMAN有个关于是否自动备份控制和参数文件的参数(CONTROLFILE AUTOBACKUP)
开始看backup database的过程,以为它这个就是自动备份(其实也算)
但自己的RMAN的AUTOBACKUP是OFF的
这导致了后面利用归档recover时遇到了问题,先继续
RMAN生成两个备份集,tag由RMAN自动生成
可用RMAN> list backup;查看一下现在的备份信息

接下来该进行破坏了?没有……还有归档日志的问题
在线备份应将redo log归档,并备份归档日志,然后再备份控制文件
这样才是比较完善的备份
其实backup database可以添加plus archivelog来增加归档备份
但要注意,如果要设定format,database和archivelog后都要加
RMAN>backup database format='/home/oracle/dumptest/db_%U'
2>plus archivelog format='/home/oracle/dumptest/ar_%U';

Starting backup at 16-JAN-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=30 recid=15 stamp=676295237
input archive log thread=1 sequence=31 recid=16 stamp=676295245
input archive log thread=1 sequence=32 recid=17 stamp=676295262
input archive log thread=1 sequence=33 recid=18 stamp=676295283
input archive log thread=1 sequence=34 recid=19 stamp=676295628
channel ORA_DISK_1: starting piece 1 at 16-JAN-09
channel ORA_DISK_1: finished piece 1 at 16-JAN-09
piece handle=/home/oracle/dumptest/ar_0ek4usud_1_1 tag=TAG20090116T115348 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 16-JAN-09

Starting backup at 16-JAN-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/home/oracle/oracle/oradata/test/system01.dbf
input datafile fno=00003 name=/home/oracle/oracle/oradata/test/sysaux01.dbf
input datafile fno=00004 name=/home/oracle/oracle/oradata/test/users01.dbf
input datafile fno=00005 name=/home/oracle/oracle/oradata/test/example01.dbf
input datafile fno=00006 name=/home/oracle/oracle/oradata/test/ts_test.dbf
input datafile fno=00002 name=/home/oracle/oracle/oradata/test/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 16-JAN-09
channel ORA_DISK_1: finished piece 1 at 16-JAN-09
piece handle=/home/oracle/dumptest/db_0fk4usui_1_1 tag=TAG20090116T115353 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:46
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 16-JAN-09
channel ORA_DISK_1: finished piece 1 at 16-JAN-09
piece handle=/home/oracle/dumptest/db_0gk4ut3p_1_1 tag=TAG20090116T115353 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
Finished backup at 16-JAN-09

Starting backup at 16-JAN-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=35 recid=20 stamp=676295809
channel ORA_DISK_1: starting piece 1 at 16-JAN-09
channel ORA_DISK_1: finished piece 1 at 16-JAN-09
piece handle=/home/oracle/dumptest/ar_0hk4ut42_1_1 tag=TAG20090116T115650 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 16-JAN-09

发现RMAN进行了两次归档备份,而且,最后一次的归档,开始并未存在于数据库中
从这个过程中,可以了解到一个backup database全数据库RMAN备份的步骤
先是当前存在的归档日志备份,然后数据文件备份
再进行控制文件和服务器参数文件备份,最后在一个日志归档操作后再进行新归档日志备份
如果要把每一步分别手动执行,工作量相对提高不少(可能会在以后涉及部分备份的时候举例)
RMAN果然简化了我们许多步骤 ^_^

最后来看看刚刚生成的备份集的具体内容
RMAN> list backup;
List of Backup Sets
===================
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
9       159.50K    DISK        00:00:02     16-JAN-09
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20090116T115348
        Piece Name: /home/oracle/dumptest/ar_0ek4usud_1_1

  List of Archived Logs in backup set 9
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    30      937651     16-JAN-09 937663     16-JAN-09
  1    31      937663     16-JAN-09 937667     16-JAN-09
  1    32      937667     16-JAN-09 937678     16-JAN-09
  1    33      937678     16-JAN-09 937691     16-JAN-09
  1    34      937691     16-JAN-09 937992     16-JAN-09

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10      Full    765.95M    DISK        00:02:40     16-JAN-09
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20090116T115353
        Piece Name: /home/oracle/dumptest/db_0fk4usui_1_1
  List of Datafiles in backup set 10
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 937997     16-JAN-09 /home/oracle/oracle/oradata/test/system01.dbf
  2       Full 937997     16-JAN-09 /home/oracle/oracle/oradata/test/undotbs01.dbf
  3       Full 937997     16-JAN-09 /home/oracle/oracle/oradata/test/sysaux01.dbf
  4       Full 937997     16-JAN-09 /home/oracle/oracle/oradata/test/users01.dbf
  5       Full 937997     16-JAN-09 /home/oracle/oracle/oradata/test/example01.dbf
  6       Full 937997     16-JAN-09 /home/oracle/oracle/oradata/test/ts_test.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11      Full    7.14M      DISK        00:00:04     16-JAN-09
        BP Key: 11   Status: AVAILABLE  Compressed: NO  Tag: TAG20090116T115353
        Piece Name: /home/oracle/dumptest/db_0gk4ut3p_1_1
  Control File Included: Ckp SCN: 938070       Ckp time: 16-JAN-09
  SPFILE Included: Modification time: 16-JAN-09

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
12      53.00K     DISK        00:00:01     16-JAN-09
        BP Key: 12   Status: AVAILABLE  Compressed: NO  Tag: TAG20090116T115650
        Piece Name: /home/oracle/dumptest/ar_0hk4ut42_1_1

  List of Archived Logs in backup set 12
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    35      937992     16-JAN-09 938097     16-JAN-09
我们可以看到很详细的备份集信息

嗯,开始搞破坏吧~
直接来个狠的,删除全部数据文件,redo log,控制文件,参数文件
本来应该从简单的恢复开始,循序渐进……有机会再实践吧
现在进入SqlPlus,数据库连nomount模式都用不了
而此时RMAN压根儿就找不到相关控制文件来获取备份信息
如果自己有另外单独拷贝过参数文件,可以利用此文件启动
但我要试验的是仅有完全备份集,其它文件全部丢失的情况
这种情况下,可以有两种方式先将数据库启动到nomount
一是,手动建立一个pfile参数文件,并利用此文件启动,这种情况不多说了
二是,让RMAN启动一个它的默认实例,执行方法简单,强行在RMAN中startup nomount
rman>startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/oracle/oracle/product/10.2.0/db_1/dbs/inittest.ora'

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
(省略了后边SGA信息)
启动完成后,可以指定含有spfile文件的备份集进行恢复(恢复目标目录也可指定)
RMAN>restore spfile to '/home/oracle/dumptest/spfiletest.ora'
2>from '/home/oracle/dumptest/db_0gk4ut3p_1_1';

Starting restore at 16-JAN-09
using channel ORA_DISK_1

channel ORA_DISK_1: autobackup found: /home/oracle/dumptest/db_0gk4ut3p_1_1
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 16-JAN-09

这个时候,可以将ora文件拷贝到$ORACLE_HOME/dbs/目录下
就可以使用它来nomount数据库了(或拷到其它目录,启动时自己指定路径)
接下来继续恢复控制文件
RMAN> restore controlfile to '/home/oracle/dumptest/control01.ctl'
2> from '/home/oracle/dumptest/db_0gk4ut3p_1_1';

Starting restore at 16-JAN-09
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 16-JAN-09
然后拷贝此文件两个副本,改名为02和03的控制文件
因为各个控制文件内容是一样的
(也可用restore命令指定恢复为02、03的控制文件)
在这里插一句,如果自己不知道原先数据库控制文件的相关路径设置
可以利用刚刚恢复的spfile建立一个pfile文件,查看control_files参数值
如果丢失了其它需要的目录,也可参考相关参数来重新建立
(spfile正常情况下是没法看懂的,pfile可以直接用文本编辑器看)
sqlplus>create pfile from spfile;
将控制文件拷到相应目录后,就可以关上数据库,再重新打开到mount模式下了
有个东西,在这里提一下,DBID
只要target数据库是mount或open状态
进入RMAN的时候,就会看见提示,DBID=1969292173(这个是我test数据库的DBID)
这个参数可以帮助我们从自动备份中进行恢复操作

此时,我们已经可以参考恢复的控制文件中的备份信息进行恢复了
先来看看我们要恢复数据库都需要哪些备份文件吧
RMAN> RESTORE DATABASE PREVIEW;

Starting restore at 16-JAN-09
Starting implicit crosscheck backup at 16-JAN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 16-JAN-09

Starting implicit crosscheck copy at 16-JAN-09
using channel ORA_DISK_1
Finished implicit crosscheck copy at 16-JAN-09

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

using channel ORA_DISK_1

List of Backup Sets
===================
(省略list命令显示内容)
archive logs generated after SCN 937997 not found in repository
Media recovery start SCN is 937997
Recovery must be done beyond SCN 937997 to clear data files fuzziness
Finished restore at 16-JAN-09
此处还提示,为了数据一致性,必须将数据库recover到SCN为937997
PREVIEW命令可以增加RECALL参数,用来查找可能存放在远程的备份文件

然后来验证一下现有的备份集
RMAN> VALIDATE BACKUPSET 9,10;

using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of archive log backupset
channel ORA_DISK_1: reading from backup piece /home/oracle/dumptest/ar_0ek4usud_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/dumptest/ar_0ek4usud_1_1 tag=TAG20090116T115348
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /home/oracle/dumptest/db_0fk4usui_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/dumptest/db_0fk4usui_1_1 tag=TAG20090116T115353
channel ORA_DISK_1: validation complete, elapsed time: 00:00:55

再来验证一下备份集是否有效满足restore database的要求
RMAN> restore database validate;

Starting restore at 16-JAN-09
using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /home/oracle/dumptest/db_0fk4usui_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/dumptest/db_0fk4usui_1_1 tag=TAG20090116T115353
channel ORA_DISK_1: validation complete, elapsed time: 00:00:57
Finished restore at 16-JAN-09

嗯,没有什么问题,该开始restore操作了
RMAN>  restore database;

Starting restore at 16-JAN-09
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /home/oracle/oracle/oradata/test/system01.dbf
restoring datafile 00002 to /home/oracle/oracle/oradata/test/undotbs01.dbf
restoring datafile 00003 to /home/oracle/oracle/oradata/test/sysaux01.dbf
restoring datafile 00004 to /home/oracle/oracle/oradata/test/users01.dbf
restoring datafile 00005 to /home/oracle/oracle/oradata/test/example01.dbf
restoring datafile 00006 to /home/oracle/oracle/oradata/test/ts_test.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/dumptest/db_0fk4usui_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/dumptest/db_0fk4usui_1_1 tag=TAG20090116T115353
channel ORA_DISK_1: restore complete, elapsed time: 00:03:16
Finished restore at 16-JAN-09
此时可以看到相应目录下的数据文件已经被还原了
没完,redo log呢?而且,数据一致性还没有得到保证呢
还要执行recover操作的
先执行一次
RMAN>recover database;
因为归档日志已经删除了,会报错
unable to find archive log
archive log thread=1 sequence=35
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/16/2009 14:49:18
RMAN-06054: media recovery requesting unknown log: thread 1 seq 35 lowscn 937997
我们要先从备份集中恢复归档日志
这里就是之前提到的隐患点了,因为备份集中的控制文件是备份数据文件后备份的
并不包含最后生成并备份的归档日志信息(sequence 35)
RMAN>RESTORE ARCHIVELOG SEQUENCE 35;#将会报错
嗯……这下有点麻烦……看来,应该在backup database后再次备份控制文件
或者把自动备份功能设置为ON
这次怎么办?想起上次做异地异系统恢复用过的那个PL/SQL包了
查了一下,呵呵,也有专门用来恢复归档日志的存储过程restoreSetArchivedLog
马上去SqlPlus执行吧
SQL> DECLARE
  2  devtype varchar2(256);
  3  done boolean;
  4  BEGIN
  5  devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
  6  sys.dbms_backup_restore.restoreSetArchivedLog(destination=>'/home/oracle/dumptest/');
  7  sys.dbms_backup_restore.restoreArchivedLog(thread=>1,sequence=>35);
  8  sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/home/oracle/dumptest/ar_0hk4ut42_1_1',params=>null);
  9  sys.dbms_backup_restore.deviceDeallocate;
 10  END;
 11  /

PL/SQL procedure successfully completed.

再插一个没有说过的东西RMAN RUN块,相当于批处理(以后会找机会细说)
在恢复归档日志的时候,可以利用RUN块来设定恢复的目录
RMAN> run{
2> SET ARCHIVELOG DESTINATION TO '/home/oracle/dumptest/';
3> RESTORE ARCHIVELOG SEQUENCE 35;
4> }
上面括号内2>那行,不能单独执行,只能在RUN块中

恢复归档日志完成后,在执行不完全恢复(Oracle推荐的36归档不存在,指定为35归档)
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 938097 generated at 01/16/2009 11:56:48 needed for thread 1
ORA-00289: suggestion :
/home/oracle/oracle/flash_recovery_area/TEST/archivelog/2009_01_16/o1_mf_1_36_%u_.arc

ORA-00280: change 938097 for thread 1 is in sequence #36
Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/oracle/flash_recovery_area/TEST/archivelog/2009_01_16/1_35_671145746.dbf
ORA-00310: archived log contains sequence 35; sequence 36 required
ORA-00334: archived log:
'/home/oracle/oracle/flash_recovery_area/TEST/archivelog/2009_01_16/1_35_671145746.dbf'

然后再执行
SQL> alter database open resetlogs;
因为是不完全恢复,所以需要让Oracle重新统一SCN等信息
故需要resetlogs选项,否则会报错
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

最后还有一步需要自己来执行,恢复临时表空间
如此一来,就彻底完成了这次备份恢复的操作
但中间有些不太顺利的地方,再来执行一次吧
这次把自动备份控制和参数文件功能打开
RMAN>configure controlfile autobackup on;
顺便把自动保存的路径设置一下
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/dumptest/%F';
再来backup~(命令略)
这次在刚才最后一步,备份归档日志后,又多了一步
Starting Control File and SPFILE Autobackup at 16-JAN-09
piece handle=/home/oracle/dumptest/c-1969292173-20090116-01 comment=NONE
Finished Control File and SPFILE Autobackup at 16-JAN-09
嗯,这次应该备份的比较全面了
继续破坏,这次就只破坏数据文件吧(以后用catalog的时候再练习都破坏的)
参数文件、控制、redo log、归档日志都在,省了些事儿
进入RMAN,再转换数据库到mount下
RMAN> restore database;
RMAN> recover database;
Starting recover at 16-JAN-09
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 16-JAN-09
这次相当快(废话……那么多重要文件都没删)

先这样吧,明天再继续破坏不同的对象来练习
真够费劲的 ◎◎ 一边练习,一边看相关文档,遇到问题查资料,再记录……
折腾了差不多一天功夫,而且备份恢复试验过程不太顺利
其中有不少是因为自己没有经验和对备份恢复原理的不理解而浪费的
特别是关于最后recover需要的归档
果然……还是得实际练习练习,才能发现更多问题

今天记录了不少过程信息
对于想稍微多了解的人可以看看
而我以后温习的时候,大概不会很仔细去看这些东西了
明天除了练习丢失不同数据库文件的恢复外
可能会练习利用catalog进行备份恢复

嗯,今天就这样,休闲去啦 ^_^

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/556359/viewspace-539885/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-01-02

  • 博文量
    134
  • 访问量
    117151