rman备份本地测试可用性

48 阅读 0 评论 32 点赞

我是靠谱客的博主洁净保温杯，最近开发中收集的这篇文章主要介绍rman备份本地测试可用性，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

关于备份最关键但通常最被忽视的数据库管理任务是测试从备份恢复。但是有时候，你没有方便的测试系统，需要在与源数据库相同的主机上测试恢复。在这种情况下，最大的恐惧是覆盖原始数据库。这里是一个简单的过程，你可以遵循，这将不会覆盖原数据库，但任然须知任何对生产环境的变动都是有风险的。

为了确认此行为不会影响到原数据库的业务，我在另一个窗口写了个脚本，在整个过程中不停的连接原数据库查询数据：

[oracle@11g ~]$ cat test.sh
for ((i=0;i<100000;i++))
do
sqlplus -s test/oracle_4U <<EOF
desc pdba
quit
EOF
done

下面是测试过程

为新实例向oratab添加一个条目

cat >> /etc/oratab <<EOF
oracle:/u01/app/oracle/product/11.2.0/dbhome_1:N
EOF

使用新实例的最小参数集创建pfile和spfile。在这种情况下，源数据库名为“orcl”，新数据库的数据库唯一名称为“oracle”。

cat > $ORACLE_HOME/dbs/initoracle.ora <<EOF
*.db_name=orcl
*.db_unique_name=oracle
*.compatible='11.2.0.4.0'
*.db_create_file_dest=/oradata/
EOF

export ORACLE_SID=oracle

[oracle@11g backup]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 20 19:52:43 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> exit

restore控制文件

[oracle@11g backup]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Feb 20 19:53:00 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 217157632 bytes

Fixed Size 2251816 bytes
Variable Size 159384536 bytes
Database Buffers 50331648 bytes
Redo Buffers 5189632 bytes

RMAN> restore controlfile from '/backup/orcl_full_05rt2toh_1_1';

Starting restore at 20-FEB-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/ORACLE/controlfile/o1_mf_dbopmfm9_.ctl
Finished restore at 20-FEB-17

如上所示，RMAN将报告它恢复的控制文件的路径和名称。使用上面的路径和名称：
RMAN> sql "alter system set control_files=''/oradata/ORACLE/controlfile/o1_mf_dbopmfm9_.ctl'' scope=spfile";

sql statement: alter system set control_files=''/oradata/ORACLE/controlfile/o1_mf_dbopmfm9_.ctl'' scope=spfile

使用新恢复的控制文件装入数据库，并执行还原到新位置。 “set newname”命令更改RMAN将文件写入新实例的db_create_file_dest的位置。 “switch database”命令更新控制文件以反映新文件位置。还原完成后，使用介质恢复应用归档的重做日志。

RMAN> startup force mount

Oracle instance started
database mounted

Total System Global Area 217157632 bytes

Fixed Size 2251816 bytes
Variable Size 159384536 bytes
Database Buffers 50331648 bytes
Redo Buffers 5189632 bytes

RMAN> run {
2> set newname for database to new;
3> restore database;
4> }

executing command: SET NEWNAME

Starting restore at 20-FEB-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oradata/ORACLE/datafile/o1_mf_system_%u_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oradata/ORACLE/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oradata/ORACLE/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oradata/ORACLE/datafile/o1_mf_users_%u_.dbf
channel ORA_DISK_1: restoring datafile 00005 to /oradata/ORACLE/datafile/o1_mf_abctest1_%u_.dbf
channel ORA_DISK_1: reading from backup piece /backup/orcl_full_04rt2tmp_1_1
channel ORA_DISK_1: piece handle=/backup/orcl_full_04rt2tmp_1_1 tag=TAG20170220T194528
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 20-FEB-17

RMAN> switch database to copy;

datafile 1 switched to datafile copy "/oradata/ORACLE/datafile/o1_mf_system_dbopqxt5_.dbf"
datafile 2 switched to datafile copy "/oradata/ORACLE/datafile/o1_mf_sysaux_dbopqxtk_.dbf"
datafile 3 switched to datafile copy "/oradata/ORACLE/datafile/o1_mf_undotbs1_dbopqxtn_.dbf"
datafile 4 switched to datafile copy "/oradata/ORACLE/datafile/o1_mf_users_dbopqxv5_.dbf"
datafile 5 switched to datafile copy "/oradata/ORACLE/datafile/o1_mf_abctest1_dbopqxv2_.dbf"

RMAN> recover database;

Starting recover at 20-FEB-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file /oradata/ORCL/onlinelog/o1_mf_1_ctdygvtg_.log
archived log for thread 1 with sequence 6 is already on disk as file /oradata/ORCL/onlinelog/o1_mf_2_ctdygypg_.log
archived log for thread 1 with sequence 7 is already on disk as file /oradata/ORCL/onlinelog/o1_mf_3_ctdyh1oy_.log
archived log file name=/oradata/ORCL/onlinelog/o1_mf_1_ctdygvtg_.log thread=1 sequence=5
archived log file name=/oradata/ORCL/onlinelog/o1_mf_2_ctdygypg_.log thread=1 sequence=6
archived log file name=/oradata/ORCL/onlinelog/o1_mf_3_ctdyh1oy_.log thread=1 sequence=7
media recovery complete, elapsed time: 00:00:02
Finished recover at 20-FEB-17

RMAN> exit

在打开数据库之前，我们需要重新创建控制文件，以便我们不会影响到属于源数据库的任何文件。

vi control.sql

CREATE CONTROLFILE REUSE
SET DATABASE "ORACLE" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 256M BLOCKSIZE 512,
GROUP 2 SIZE 256M BLOCKSIZE 512,
GROUP 3 SIZE 256M BLOCKSIZE 512,
GROUP 4 SIZE 256M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/oradata/ORACLE/datafile/o1_mf_system_dbopqxt5_.dbf',
'/oradata/ORACLE/datafile/o1_mf_sysaux_dbopqxtk_.dbf',
'/oradata/ORACLE/datafile/o1_mf_undotbs1_dbopqxtn_.dbf',
'/oradata/ORACLE/datafile/o1_mf_users_dbopqxv5_.dbf',
'/oradata/ORACLE/datafile/o1_mf_abctest1_dbopqxv2_.dbf'
CHARACTER SET ZHS16GBK
;

下一步是使用上述脚本在新的OMF控制文件上使用resetlogs选项打开数据库：

[oracle@11g backup]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 20 20:10:29 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system reset control_files scope=spfile;

System altered.

SQL> startup force nomount
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2251816 bytes
Variable Size 159384536 bytes
Database Buffers 50331648 bytes
Redo Buffers 5189632 bytes

SQL> @control.sql

Control file created.

SQL> select value from v$parameter where name = 'control_files';

VALUE
--------------------------------------------------------------------------------
/oradata/ORACLE/controlfile/o1_mf_dbos8dyp_.ctl

SQL> alter database open resetlogs;

Database altered.

SQL> desc test.pdba
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
TIME DATE

SQL> quit

实际上这已经是个新的数据库了，只需要添加一个临时表空间就可以用，此时检查一下监听可以看到

[oracle@11g backup]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-FEB-2017 20:47:27

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=11g)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 20-FEB-2017 20:11:04
Uptime 0 days 0 hr. 36 min. 23 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/11g/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=11g)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "oracle" has 1 instance(s).
Instance "oracle", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully