Chinaunix首页 | 论坛 | 博客
  • 博客访问: 75456
  • 博文数量: 37
  • 博客积分: 2000
  • 博客等级: 大尉
  • 技术积分: 386
  • 用 户 组: 普通用户
  • 注册时间: 2009-11-11 11:33
文章分类

全部博文(37)

文章存档

2011年(1)

2010年(30)

2009年(6)

我的朋友

分类: LINUX

2010-04-09 16:00:25

环境:AIX 5.2,数据库oracle 9。2,使用controlfile备份,模拟数据文件、控制文件、redo文件全部丢失,使用dbms_backup_restore pl/sql包进行数据库恢复.
1、查看备份参数
# su - oracle
HB130000 oracle$rman target /
 
Recovery Manager: Release 9.2.0.1.0 - 64bit Production
 
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
 
connected to target database: HB130000 (DBID=2380174037)
 
RMAN> show all;
 
using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/rmanbackup/hb130000_ctl_%F.bak';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/oradata/%d_%s_%t.bak';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/oracle/app/oracle/product/9.2.0.1/dbs/snapcf_hb130000.f'; # default
 
RMAN> list backup;
 
2full备份数据库
 
RMAN> backup database;
 
Starting backup at 19-APR-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=16 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00005 name=/oradata/hb130000/example01.dbf
input datafile fno=00001 name=/oradata/hb130000/system01.dbf
input datafile fno=00013 name=/oradata/hb130000/CA.dbf
input datafile fno=00002 name=/oradata/hb130000/undotbs01.dbf
input datafile fno=00020 name=/oradata/hb130000/catalog_rman.dbf
input datafile fno=00014 name=/oradata/hb130000/QUEST.dbf
input datafile fno=00009 name=/oradata/hb130000/users01.dbf
input datafile fno=00015 name=/oradata/hb130000/FOGLIGHT.dbf
input datafile fno=00017 name=/oradata/hb130000/GDSS.dbf
input datafile fno=00018 name=/oradata/hb130000/FPJKDB.dbf
input datafile fno=00011 name=/oradata/hb130000/HB1300002005.DBF
input datafile fno=00010 name=/oradata/hb130000/xdb01.dbf
input datafile fno=00021 name=/oradata/hb130000/oem_repository.dbf
input datafile fno=00006 name=/oradata/hb130000/indx01.dbf
input datafile fno=00003 name=/oradata/hb130000/cwmlite01.dbf
input datafile fno=00004 name=/oradata/hb130000/drsys01.dbf
input datafile fno=00007 name=/oradata/hb130000/odm01.dbf
input datafile fno=00012 name=/oradata/hb130000/GFB.DBF
input datafile fno=00008 name=/oradata/hb130000/tools01.dbf
input datafile fno=00016 name=/oradata/hb130000/gwd.dbf
input datafile fno=00019 name=/oradata/hb130000/tgggg.dbf
channel ORA_DISK_1: starting piece 1 at 19-APR-06
channel ORA_DISK_1: finished piece 1 at 19-APR-06
piece handle=/oradata/HB130000_11_588180388.bak comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
Finished backup at 19-APR-06
 
3、备份归档日志
 
RMAN> backup archivelog all delete input;
 
Starting backup at 19-APR-06
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=22 recid=32 stamp=588171491
input archive log thread=1 sequence=23 recid=33 stamp=588173034
input archive log thread=1 sequence=24 recid=34 stamp=588173040
input archive log thread=1 sequence=25 recid=35 stamp=588173040
input archive log thread=1 sequence=26 recid=36 stamp=588173045
input archive log thread=1 sequence=27 recid=37 stamp=588180556
channel ORA_DISK_1: starting piece 1 at 19-APR-06
channel ORA_DISK_1: finished piece 1 at 19-APR-06
piece handle=/oradata/HB130000_12_588180557.bak comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/oradata/rmanbackup/archive/hb130000_1_22.dbf recid=32 stamp=588171491
archive log filename=/oradata/rmanbackup/archive/hb130000_1_23.dbf recid=33 stamp=588173034
archive log filename=/oradata/rmanbackup/archive/hb130000_1_24.dbf recid=34 stamp=588173040
archive log filename=/oradata/rmanbackup/archive/hb130000_1_25.dbf recid=35 stamp=588173040
archive log filename=/oradata/rmanbackup/archive/hb130000_1_26.dbf recid=36 stamp=588173045
archive log filename=/oradata/rmanbackup/archive/hb130000_1_27.dbf recid=37 stamp=588180556
Finished backup at 19-APR-06
 
4、查看controlfile备份
 
RMAN> list backup of controlfile;
 
using target database controlfile instead of recovery catalog
 
List of Backup Sets
===================
 
BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10      Full    1M         DISK        00:00:50     19-APR-06
        BP Key: 10   Status: AVAILABLE   Tag: TAG20060419T152628
        Piece Name: /oradata/HB130000_11_588180388.bak
 Controlfile Included: Ckp SCN: 65670562     Ckp time: 19-APR-06
 
RMAN> exit
 
 
Recovery Manager complete.
 
5、通过试图查看数据库数据文件、控制文件、日志文件路径信息
 
SQL> select name from v$datafile;
 
NAME
--------------------------------------------------------------------------------
/oradata/hb130000/system01.dbf
/oradata/hb130000/undotbs01.dbf
/oradata/hb130000/cwmlite01.dbf
/oradata/hb130000/drsys01.dbf
/oradata/hb130000/example01.dbf
/oradata/hb130000/indx01.dbf
/oradata/hb130000/odm01.dbf
/oradata/hb130000/tools01.dbf
/oradata/hb130000/users01.dbf
/oradata/hb130000/xdb01.dbf
/oradata/hb130000/HB1300002005.DBF
 
NAME
--------------------------------------------------------------------------------
/oradata/hb130000/GFB.DBF
/oradata/hb130000/CA.dbf
/oradata/hb130000/QUEST.dbf
/oradata/hb130000/FOGLIGHT.dbf
/oradata/hb130000/gwd.dbf
/oradata/hb130000/GDSS.dbf
/oradata/hb130000/FPJKDB.dbf
/oradata/hb130000/tgggg.dbf
/oradata/hb130000/catalog_rman.dbf
/oradata/hb130000/oem_repository.dbf
 
21 rows selected.
 
SQL> select * from v$logfile;
 
    GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
         1         ONLINE
/oracle/oracle/app/oracle/product/9.2.0.1/dbs/hb130000/redo01.log
 
         1         ONLINE
/oradata/hb130000/redo01.rdo
 
         3         ONLINE
/oracle/oracle/app/oracle/product/9.2.0.1/dbs/hb130000/redo03.log
 
 
    GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
         3         ONLINE
/oradata/hb130000/redo03.rdo
 
         2         ONLINE
/oracle/oracle/app/oracle/product/9.2.0.1/dbs/hb130000/redo02.log
 
         2         ONLINE
/oradata/hb130000/redo02.rdo
 
 
6 rows selected.
 
SQL> select * from v$controlfile;
 
STATUS
-------
NAME
--------------------------------------------------------------------------------
 
/oracle/oracle/app/oracle/product/9.2.0.1/dbs/hb130000/control01.ctl
 
 
/oracle/oracle/app/oracle/product/9.2.0.1/dbs/hb130000/control02.ctl
 
 
/oradata/hb130000/control03.ctl
 
 
6、删除所有数据文件、控制文件和redo文件
 
SQL>
HB130000 oracle$cd hb130000
HB130000 oracle$ls
CA.dbf                HB1300002005.DBF example01.dbf         redo03.rdo            tools01.dbf
FOGLIGHT.dbf          catalog_rman.dbf      gwd.dbf               spfilehb130000.ora    undotbs01.dbf
FPJKDB.dbf            control01.ctl         indx01.dbf            system01.dbf          users01.dbf
FSIDATA.dbf           control02.ctl         odm01.dbf             temp01.dbf            xdb01.dbf
GDSS.dbf              control03.ctl         oem_repository.dbf    temp02.dbf
GFB.DBF               cwmlite01.dbf         redo01.rdo            temp03.dbf
QUEST.dbf             drsys01.dbf           redo02.rdo            tgggg.dbf
HB130000 oracle$rm *
HB130000 oracle$pwd
/oradata/hb130000
HB130000 oracle$cd $ORACLE_HOME/dbs
HB130000 oracle$cd hb130000
HB130000 oracle$ls
control01.ctl control02.ctl redo01.log     redo02.log     redo03.log
HB130000 oracle$rm *
HB130000 oracle$
 
 
7shutdown abort数据库,重新启动数据库
 
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area 420970696 bytes
Fixed Size                   741576 bytes
Variable Size             385875968 bytes
Database Buffers           33554432 bytes
Redo Buffers                 798720 bytes
ORA-00205: error in identifying controlfile, check alert log for more info
 
8、使用dbms_backup_restore包恢复控制文件
 
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.restoresetdatafile;
 7 sys.dbms_backup_restore.restorecontrolfileto(cfname=>'/oradata/controlfile.ctl');
 8 sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'/oradata/HB130000_11_588180388.bak',params=>null);
 9 sys.dbms_backup_restore.devicedeallocate;
 10 end;
 11 /
 
PL/SQL procedure successfully completed.
SQL> host ls /oradata/
controlfile.ctl            guowendong.log             ora9
 
SQL>
 
9、使用dbms_backup_restore包恢复数据文件。
 
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.restoresetdatafile;
 7 sys.dbms_backup_restore.restoredatafileto(dfnumber=>01,toname=>'/oradata/hb130000/system01.dbf');
 8   sys.dbms_backup_restore.restoredatafileto(dfnumber=>02,toname=>'/oradata/hb130000/undotbs01.dbf');
 9   sys.dbms_backup_restore.restoredatafileto(dfnumber=>03,toname=>'/oradata/hb130000/cwmlite01.dbf');
 10 sys.dbms_backup_restore.restoredatafileto(dfnumber=>04,toname=>'/oradata/hb130000/drsys01.dbf');
 11 sys.dbms_backup_restore.restoredatafileto(dfnumber=>05,toname=>'/oradata/hb130000/example01.dbf');
 12   sys.dbms_backup_restore.restoredatafileto(dfnumber=>06,toname=>'/oradata/hb130000/indx01.dbf');
 13   sys.dbms_backup_restore.restoredatafileto(dfnumber=>07,toname=>'/oradata/hb130000/odm01.dbf');
 14 sys.dbms_backup_restore.restoredatafileto(dfnumber=>08,toname=>'/oradata/hb130000/tools01.dbf');
 15   sys.dbms_backup_restore.restoredatafileto(dfnumber=>09,toname=>'/oradata/hb130000/users01.dbf');
 16   sys.dbms_backup_restore.restoredatafileto(dfnumber=>10,toname=>'/oradata/hb130000/xdb01.dbf');
 17    sys.dbms_backup_restore.restoredatafileto(dfnumber=>11,toname=>'/oradata/hb130000/HB1300002005.DBF');
 18   sys.dbms_backup_restore.restoredatafileto(dfnumber=>12,toname=>'/oradata/hb130000/GFB.DBF');
 19   sys.dbms_backup_restore.restoredatafileto(dfnumber=>13,toname=>'/oradata/hb130000/CA.dbf');
 20   sys.dbms_backup_restore.restoredatafileto(dfnumber=>14,toname=>'/oradata/hb130000/QUEST.dbf');
 21 sys.dbms_backup_restore.restoredatafileto(dfnumber=>15,toname=>'/oradata/hb130000/FOGLIGHT.dbf');
 22   sys.dbms_backup_restore.restoredatafileto(dfnumber=>16,toname=>'/oradata/hb130000/gwd.dbf');
 23   sys.dbms_backup_restore.restoredatafileto(dfnumber=>17,toname=>'/oradata/hb130000/GDSS.dbf');
 24   sys.dbms_backup_restore.restoredatafileto(dfnumber=>18,toname=>'/oradata/hb130000/FPJKDB.dbf');
 25   sys.dbms_backup_restore.restoredatafileto(dfnumber=>19,toname=>'/oradata/hb130000/tgggg.dbf');
 26 sys.dbms_backup_restore.restoredatafileto(dfnumber=>20,toname=>'/oradata/hb130000/catalog_rman.dbf');
 27   sys.dbms_backup_restore.restoredatafileto(dfnumber=>21,toname=>'/oradata/hb130000/oem_repository.dbf');
 28   sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'/oradata/HB130000_11_588180388.bak',params=>null);
 29   sys.dbms_backup_restore.devicedeallocate;
 30   end;
 31 /
 
PL/SQL procedure successfully completed.
 
 
10、把控制文件cp到原路径
 
HB130000 oracle$cd /oradata/
HB130000 oracle$ls
controlfile.ctl            guowendong.log             ora9                      
HB130000oracle$cp controlfile.ctl /oracle/oracle/app/oracle/product/9.2.0.1/dbs/hb130000/control01.ctl
HB130000oracle$cp controlfile.ctl /oracle/oracle/app/oracle/product/9.2.0.1/dbs/hb130000/control02.ctl
HB130000 oracle$cp controlfile.ctl /oradata/hb130000/control03.ctl
 
11mount 数据库
 
SQL> startup nomount;
ORACLE instance started.
 
Total System Global Area 420970696 bytes
Fixed Size                   741576 bytes
Variable Size             385875968 bytes
Database Buffers           33554432 bytes
Redo Buffers                 798720 bytes
SQL> alter database mount;
 
Database altered.
 
 
12、使用dbms_backup_restore包恢复归档日志
 
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=>'/oradata/rmanbackup/archive');
 7 sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>22);
 8 sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>23);
 9 sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>24);
 10 sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>25);
 11 sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>26);
 12 sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>27);
 13 sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'/oradata/HB130000_12_588180557.bak',params=>null);
 14 sys.dbms_backup_restore.devicedeallocate;
 15 end;
 16 /
 
PL/SQL procedure successfully completed.
 
SQL>
 
 
13、使用controlfile恢复数据库,resetlogs方式打开数据库
 
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 65670562 generated at 04/19/2006 13:24:05 needed for thread 1
ORA-00289: suggestion : /oradata/rmanbackup/archive/hb130000_1_27.dbf
ORA-00280: change 65670562 for thread 1 is in sequence #27
 
 
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 65671036 generated at 04/19/2006 15:29:16 needed for thread 1
ORA-00289: suggestion : /oradata/rmanbackup/archive/hb130000_1_28.dbf
ORA-00280: change 65671036 for thread 1 is in sequence #28
ORA-00278: log file '/oradata/rmanbackup/archive/hb130000_1_27.dbf' no longer
needed for this recovery
 
 
ORA-00308: cannot open archived log
'/oradata/rmanbackup/archive/hb130000_1_28.dbf'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
 
 
SQL> alter database open resetlogs;
 
Database altered.
感谢原创作者,...
阅读(755) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~