可以将生产中备份的RMAN数据恢复到测试环境,这样可以验证备份数据的可用性。
简述下大致步骤:
- 在生产服务器/源服务器进行RMAN备份
- 将备份复制到测试/目的服务器,一下所有步骤在测试服务器执行:
- 确保测试服务器上安装同版本的oracle程序
- 引用必须的OS变量,设置ORACLE_BASE、ORACLE_HOME等环境变量
- 在测试库上创建init.ora文件
- 为数据文件、控制文件、归档日志、跟踪文件创建必需的目录
- 启动到nomount模式
- 进行RMAN恢复备份的控制文件
- 启动到mount模式
- 将RMAN备份中的信息catlog到控制文件中
- 恢复数据文件如果和生产环境路径不一样则需要进行重命名。
- 对数据库应用重做日志
- 为联机日志设置新的存储位置
- 打开数据库
- 添加临时文件
- 重命名数据库(重建控制文件)
下面通过案例来进行演示:
0.案例背景
生产服务器数据文件和控制文件路径:/u01/dbfile/o12c
测试服务器数据文件和控制文件的位置:/ora01/dbfile/DEBDB
测试服务器的联机重做日志位置:/ora01/oraredo/DEVDB
测试服务器的归档日志路径:/ora01/arc/DEVDB
1.在生产服务器进行RMAN备份
RMAN> backup database plus archivelog;
产生以下备份片:
rman1_bonvb2js_1_1.bk
rman1_bqnvb2k5_1_1.bk
rman1_bsnvb2p3_1_1.bk
rman_ctl_c-3423216220-20130113-06.bk
2.将RMAN的备份片复制到测试服务器
$ scp rman* oracle@DEVBOX:/ora01/rman/DEVDB
3.确认在测试服务器上安装的ORACLE数据库版本
4.设置环境变量,效果如下:
$ echo $ORACLE_SID
o12c
$ echo $ORACLE_HOME
/ora01/app/oracle/product/12.1.0.1/db_1
5.为测试数据库修改并创建参数文件
control_files='/ora01/dbfile/DEVDB/control01.ctl',
'/ora01/dbfile/DEVDB/control02.ctl'
db_block_size=8192
db_name='o12c'
log_archive_dest_1='location=/ora01/arc/DEVDB'
job_queue_processes=10
memory_max_target=300000000
memory_target=300000000
open_cursors=100
os_authent_prefix=''
processes=100
remote_login_passwordfile='EXCLUSIVE'
resource_limit=true
shared_pool_size=80M
sql92_security=TRUE
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
workarea_size_policy='AUTO'
6.为数据文件、控制文件和数据泵文件或跟踪文件创建必须目录
$ mkdir -p /ora01/dbfile/DEVDB
$ mkdir -p /ora01/oraredo/DEVDB
$ mkdir -p /ora01/arc/DEVDB
7.启动到nomount模式
$ rman target /
RMAN> startup nomount;
8.恢复生产中备份的控制文件
RMAN> restore controlfile from /ora01/rman/DEVDB/rman_ctl_c-3423216220-20130113-06.bk';
9.启动到mount模式
RMAN> alter database mount;
10.将现有RMAN备份的信息catalog到新恢复的控制文件中
10.1交叉验证
--进行交叉验证更新控制文件信息
RMAN> crosscheck backup; # Crosscheck backups
RMAN> crosscheck copy; # Crosscheck image copies and archive logs
10.2进行catalog,将测试中的备份信息写到控制文件中
RMAN> catalog start with ‘/ora01/rman/DEVDB’;
Here is some sample output:
File Name: /ora01/rman/DEVDB/rman1_bqnvb2k5_1_1.bk
File Name: /ora01/rman/DEVDB/rman1_bonvb2js_1_1.bk
File Name: /ora01/rman/DEVDB/rman_ctl_c-3423216220-20130113-06.bk
File Name: /ora01/rman/DEVDB/rman1_bsnvb2p3_1_1.bk
Do you really want to catalog the above files (enter YES or NO)?
yes
RMAN> list backup;
11.恢复数据文件将将其更改到新位置
11.1测试环境和生产环境目录相同
如果测试环境和生产环境目录相同则直接进行恢复:
RMAN>restore database;
11.2 测试环境和生产环境目录不同
大部分情况下是有区别的,则需要在生产环境sqlplus 中运行下面的sql脚本生成RMAN指令:
11.2.01在生产中生成RMAN指令
set head off feed off verify off echo off pages 0 trimspool on
set lines 132 pagesize 0
spo newname.sql
--
select 'run{' from dual;
--
select
'set newname for datafile ' || file# || ' to ' || '''' || name || '''' || ';'
from v$datafile;
--
select
'restore database;' || chr(10) ||
'switch datafile all;' || chr(10) ||
'}'
from dual;
--
spo off;
生成的内容如下:
run{
set newname for datafile 1 to '/u01/dbfile/o12c/system01.dbf';
set newname for datafile 2 to '/u01/dbfile/o12c/sysaux01.dbf';
set newname for datafile 3 to '/u01/dbfile/o12c/undotbs01.dbf';
set newname for datafile 4 to '/u01/dbfile/o12c/users01.dbf';
restore database;
switch datafile all;
}
11.2.02修改生产中生成的RMAN指令并执行
然后更改下,使其符合测试环境的情况,修改后如下:
run{
set newname for datafile 1 to '/ora01/dbfile/DEVDB/system01.dbf';
set newname for datafile 2 to '/ora01/dbfile/DEVDB/sysaux01.dbf';
set newname for datafile 3 to '/ora01/dbfile/DEVDB/undotbs01.dbf';
set newname for datafile 4 to '/ora01/dbfile/DEVDB/users01.dbf';
restore database;
switch datafile all;
}
连接RMAN,运行这个命令:
$ rman target /
RMAN> @newname.sql
部分输出:
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=790357985 file name=/ora01/dbfile/DEVDB/system01.dbf
11.2.03用report schema 检查
运行report shema,查看备恢复的数据文件位置:
RMAN> report schema;
部分输出:
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name O12C
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 500 SYSTEM *** /ora01/dbfile/DEVDB/system01.dbf
2 500 SYSAUX *** /ora01/dbfile/DEVDB/sysaux01.dbf
3 800 UNDOTBS1 *** /ora01/dbfile/DEVDB/undotbs01.dbf
4 50 USERS *** /ora01/dbfile/DEVDB/users01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 500 TEMP 500 /u01/dbfile/o12c/temp01.dbf
12.对数据库应用重做日志
RMAN> recover database;
在这个过程中,rman将会将应用备份中的所有的归档日志,直到无法找到下一个归档日志文件为止。会报一个错误:RMAN-06054: media recovery requesting unknown archived log for…
执行下面的sql来验证此时的数据文件联机状态:
select file#, status, fuzzy, error, checkpoint_change#,
to_char(checkpoint_time,'dd-mon-rrrr hh24:mi:ss') as checkpoint_time
from v$datafile_header;
13.为联机重做日志设置新的存储位置
如果测试目录和成产目录相同,这一步可以略过。
用下面的脚本在生产环境的s清理plus下执行,
set head off feed off verify off echo off pages 0 trimspool on
set lines 132 pagesize 0
spo renlog.sql
select
'alter database rename file ' || chr(10)
|| '''' || member || '''' || ' to ' || chr(10) || '''' || member || '''' ||';'
from v$logfile;
spo off;
将生成的脚本修改下,以便在测试环境应用:
alter database rename file
'/u01/oraredo/o12c/redo01a.rdo' to
'/ora01/oraredo/DEVDB/redo01a.rdo';
...
alter database rename file
'/u02/oraredo/o12c/redo03b.rdo' to
'/ora01/oraredo/DEVDB/redo03b.rdo';
运行修改的脚本:
SQL> @renlog.sql
验证:
SQL> select member from v$logfile;
/ora01/oraredo/DEVDB/redo01a.rdo
/ora01/oraredo/DEVDB/redo02a.rdo
/ora01/oraredo/DEVDB/redo03a.rdo
/ora01/oraredo/DEVDB/redo01b.rdo
/ora01/oraredo/DEVDB/redo02b.rdo
/ora01/oraredo/DEVDB/redo03b.rdo
14.打开数据库
因为没有联机日志,必须用resetlogs 打开
SQL> alter database open resetlogs;
15.维护临时文件
当库打开的时候,oracle还是按照生产原来的位置打开数据库,如果位置一样则没问题,位置不一样的话则需要重新维护:
SQL> alter database tempfile '/u01/dbfile/o12c/temp01.dbf' offline;
SQL> alter database tempfile '/u01/dbfile/o12c/temp01.dbf' drop;
SQL> alter tablespace temp add tempfile '/ora01/dbfile/DEVDB/temp01.dbf' size 100m;
执行完毕后,可在在rman中用repor shema来重新验证下。
16.重命名数据库
一般测试的数据库名不是和生产相同的,可以重建控制文件修改下数据库的名字:
用下面的sql将控制文件的脚本导出并关闭数据库:
SQL> alter database backup controlfile to trace as '/tmp/cf.sql' resetlogs;
SQL> shutdown immediate;
打开后如下:
CREATE CONTROLFILE REUSE SET DATABASE "o12c" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 4
MAXDATAFILES 1024
MAXINSTANCES 1
MAXLOGHISTORY 876
LOGFILE
GROUP 1 (
'/ora01/oraredo/DEVDB/redo01a.rdo',
'/ora01/oraredo/DEVDB/redo01b.rdo'
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
'/ora01/oraredo/DEVDB/redo02a.rdo',
'/ora01/oraredo/DEVDB/redo02b.rdo'
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
'/ora01/oraredo/DEVDB/redo03a.rdo',
'/ora01/oraredo/DEVDB/redo03b.rdo'
) SIZE 50M BLOCKSIZE 512
DATAFILE
'/ora01/dbfile/DEVDB/system01.dbf',
'/ora01/dbfile/DEVDB/sysaux01.dbf',
'/ora01/dbfile/DEVDB/undotbs01.dbf',
'/ora01/dbfile/DEVDB/users01.dbf'
CHARACTER SET AL32UTF8;
必须把reuse去掉,并设置新的数据库名字:
CREATE CONTROLFILE SET DATABASE "DEVDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 4
MAXDATAFILES 1024
MAXINSTANCES 1
MAXLOGHISTORY 876
LOGFILE
GROUP 1 (
'/ora01/oraredo/DEVDB/redo01a.rdo',
'/ora01/oraredo/DEVDB/redo01b.rdo'
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
'/ora01/oraredo/DEVDB/redo02a.rdo',
'/ora01/oraredo/DEVDB/redo02b.rdo'
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
'/ora01/oraredo/DEVDB/redo03a.rdo',
'/ora01/oraredo/DEVDB/redo03b.rdo'
) SIZE 50M BLOCKSIZE 512
DATAFILE
'/ora01/dbfile/DEVDB/system01.dbf',
'/ora01/dbfile/DEVDB/sysaux01.dbf',
'/ora01/dbfile/DEVDB/undotbs01.dbf',
'/ora01/dbfile/DEVDB/users01.dbf'
CHARACTER SET AL32UTF8;
后续还要维护参数文件:
$ cd $ORACLE_HOME/dbs
$ cp init<old_sid>.ora init<new_sid>.ora
$ cp inito12c.ora initDEVDB.ora
修改里面的db_name=’DEVDB’ 项,并设置操作系统的环境变量:
export ORACLE_SID=DEVDB
然后重建控制文件,并打开数据库就OK了,最后检查下临时文件:
SQL> startup nomount;
SQL> @/tmp/cf.sql
SQL> alter database open resetlogs;
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/ora01/dbfile/DEVDB/temp01.dbf'
SIZE 104857600 REUSE AUTOEXTEND OFF;