ORACLE RMAN 增量备份完整恢复测试
创建测试表空间:
SQL> create tablespace tablespace1
2datafile '/db/app/oracle/oradata/orcl/tablespace1.dbf'
3size 10m;
Tablespace created.
SQL>
创建测试用户并指定为默认表空间:
SQL> create user user1 identified by oracle default tablespace tablespace1;
User created.
SQL>
设置该测试用户权限:
SQL> grant create session,create table,unlimited tablespace to user1;
Grant succeeded.
SQL>
用新创建的用户登录:
SQL> conn user1/
Enter password:
Connected.
SQL>
建表并写入数据:
SQL> create table table1 (id int,name varchar2(30));
Table created.
SQL> insert into table1 values(0001,'Tian');
1 row created.
SQL> insert into table1 values(0002,'zhong');
1 row created.
SQL> select * from table1;
ID NAME
---------- ------------------------------
1 Tian
2 zhong
SQL> commit;
Commit complete.
配置RMAN环境变量
切换sys用户作0级备份:
利用configure命令配置RMAN环境:
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; #开启归档日志自动备份
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/db/bak/ctf/%d_ctf_bak_%F'; #指定归档日志备份位置
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET; #采用并行度为2且压缩备份集的方式备份到磁盘。
RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT'/db/bak/disk1/%d_datafile_bak_%U';
RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT'/db/bak/disk2/%d_datafile_bak_%U';
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/db/bak/ctf/%d_ctf_bak_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
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 MAXPIECESIZE 2 G;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT'/db/bak/disk1/%d_datafile_bak_%U';
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT'/db/bak/disk2/%d_datafile_bak_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/db/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default
备份全库(包括归档日志及启动参数文件等):
RMAN>
RMAN> backup incremental level 0 database plus archivelog;
Starting backup at 06-MAY-11
current log archived
using channel ORA_DISK_1
….
….(省略过程)
提示备份成功。可以再通过RMAN> list backup;查看已有备份确认(注意LV 字段全为0,为0级增量备份)。
继续往测试表中插入新数据:
SQL> insert into table1 values(3,'wang');
1 row created.
SQL> insert into table1 values(4,'newtest');
1 row created.
SQL> select * from table1;
ID NAME
---------- ------------------------------
1 Tian
2 zhong
3 wang
4 newtest
SQL> commit;
Commit complete.插入成功,并提交。
1级增量备份全库:
RMAN> backup incremental level 1 database plus archivelog;
…
…
省略备份过程细节。
提示备份成功。可以再通过RMAN> list backup;查看已有备份确认(此时LV字段包含0和1,表示目前备份中已包含有0级和1级增量备份)。
接下来开始恢复:
===============================
恢复
===============================
模拟全库数据文件,spfile文件及控制文件的丢失(物理删除):
模拟方式,操作系统层面rm命令强行删除,包括oradata目录下的数据文件和$ORACLE_HOME/dbs目录下的spfile等启动参数文件。
//注意:在操作系统层面rm删除oracle相关文件时,可以选择oracle open状态删除,也可以选择先将oracle正常shutdown后再删除。但后者在recover过程需要设置前滚until点,可以通过设置until SCN=故障发生时前一个正常恢复点。(SCN点在recover过程中会有提示)
此处为正常shutdown immediate后的恢复。
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/db/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'
SQL>此处可见已经找不到启动参数文件
首先恢复spfile文件:
RMAN> connect target /
connected to target database (not started)
RMAN> set dbid 1274918132#dbid号可通过查看备份文件得到
executing command: SET DBID
RMAN> startup nomount;#RMAN可从默认的Oracle配置参数中启动到nomount状态
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/db/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area159019008 bytes
Fixed Size1335192 bytes
Variable Size75497576 bytes
Database Buffers79691776 bytes
Redo Buffers2494464 bytes
RMAN> restore spfile from '/db/bak/ctf/ORCL_ctf_bak_c-1274918132-20110506-02'; #控制文件备份地址
Starting restore at 06-MAY-11
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /db/bak/ctf/ORCL_ctf_bak_c-1274918132-20110506-02
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 06-MAY-11
恢复参数文件成功!!!!
关闭数据库实例,重新从刚才已恢复的参数文件启动数据库实例。
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area422670336 bytes
Fixed Size1336960 bytes
Variable Size322963840 bytes
Database Buffers92274688 bytes
Redo Buffers6094848 bytes
以上参数可见原先SGA的配置和默认RMAN启动调用的参数是不同滴。。。。
接下来恢复控制文件
RMAN> restore controlfile from '/db/bak/ctf/ORCL_ctf_bak_c-1274918132-20110506-02'; #备份控制文件路径
Starting restore at 06-MAY-11
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/db/app/oracle/oradata/orcl/control01.ctl
output file name=/db/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 06-MAY-11
控制文件恢复成功~!!!!
此时就可以启动数据库到mount状态
RMAN> alter database mount;
database mounted
继续恢复数据~
RMAN> restore database;
RMAN> recover database;
RMAN>alter database open resetlogs;
以上三步无报错,提示成功~
最后检查测试的数据一致性:
SQL> select * from user1.table1;
ID NAME
---------- ------------------------------
1 Tian
2 zhong
3 wang
4 newtest
SQL>
数据恢复成功!!!!
Bruce