合 Oracle单实例备份集恢复到RAC集群
前言部分
导读
各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:
① 单实例环境的备份集如何恢复到rac环境(重点)
② rman恢复数据库的一般步骤
③ rac环境的简单操作
实验环境介绍
源库:11.2.0.1 RHEL6.5 单实例
目标库:11.2.0.1 RHEL6.5 rac环境
本文简介
本文基于如何将单实例的数据库备份恢复到rac环境下,至于rac环境的备份集如何恢复到单实例及rac环境的备份集恢复到rac环境的实验请参考相关文章链接部分。
实验的一些数据库环境参考如下表格:
项目 | source db | target db |
---|---|---|
db 类型 | 单实例 | rac环境 |
db version | 11.2.0.1 | 11.2.0.1 |
ORACLE_SID | orastrac | orastrac1 和 orastrac2 |
db_name | orastrac | orastrac |
主机IP地址: | 192.168.59.129 | 192.168.1.31 192.168.1.32 |
先描述下大致步骤:
- 源端创建备份集;
- 目标端安装数据库软件和集群件,并配置好共享存储(安装rac的时候一般已经配置好了);
- 复制源端备份集到目标端;
- 目录端任意节点执行正常恢复,恢复时注意要将 spfile,controlfile,datafile,redofile 等路径改到共享存储上,恢复完后这会儿仍然是个单实例数据库;
- 修改初始化参数、增加 UNDO 表空间、增加 REDOLOG 线程组,重建密钥文件,目标端任意节点执行;
- 目标端各个节点配置监听及网络服务名;
- 将新建的数据库配置到 crs,目标端任意节点执行即可。
实验部分
实验目标
将单实例的备份集成功的恢复到rac环境下,并添加数据库到crs环境。
源库操作
source库上需要做的操作主要是备份和创建测试用户。
静默创建一个单实例的测试库
首先修改归档模式,这样创建的数据库默认为归档模式,然后我们在源库上静默创建一个单实例的库,sid为orastrac,为oracle single instance to rac,关于静默安装数据库参考:
静默安装系列 | |
---|---|
【推荐】 【DBCA -SILENT】静默方式安装11gR2 oracle数据库软件 | http://blog.itpub.net/26736162/viewspace-1589769/ |
【推荐】 【DBCA -SILENT】静默安装之rac数据库安装 | http://blog.itpub.net/26736162/viewspace-1586352/ |
【推荐】 【DBCA -SILENT】静默安装如何启用归档模式 | http://blog.itpub.net/26736162/viewspace-1585925/ |
【推荐】 DBCA静默方式建库 | http://blog.itpub.net/26736162/viewspace-1448220/ |
[oracle@orcltest ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@orcltest ~]$ sqlplus -v
SQL*Plus: Release 11.2.0.1.0 Production
[oracle@orcltest ~]$ strings $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc | grep -i arch
\<archiveLogMode>false\</archiveLogMode>
[oracle@orcltest ~]$ vi $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc
\<?xml version = '1.0'?>
\<DatabaseTemplate name="General_Purpose" description="" version="11.1.0.0.0">
\<CommonAttributes>
\<option name="OMS" value="false"/>
\<option name="JSERVER" value="true"/>
\<option name="SPATIAL" value="true"/>
\<option name="IMEDIA" value="true"/>
\<option name="XDB_PROTOCOLS" value="true">
\<tablespace id="SYSAUX"/>
\</option>
\<option name="ORACLE_TEXT" value="true">
\<tablespace id="SYSAUX"/>
\</option>
\<option name="SAMPLE_SCHEMA" value="false"/>
\<option name="CWMLITE" value="true">
\<tablespace id="SYSAUX"/>
\</option>
\<option name="EM_REPOSITORY" value="true">
\<tablespace id="SYSAUX"/>
\</option>
\<option name="APEX" value="true"/>
\<option name="OWB" value="true"/>
\<option name="DV" value="false"/>
\</CommonAttributes>
\<Variables/>
\<CustomScripts Execute="false"/>
\<InitParamAttributes>
\<InitParams>
\<initParam name="db_name" value=""/>
\<initParam name="dispatchers" value="(PROTOCOL=TCP) (SERVICE={SID}XDB)"/>
\<initParam name="audit_file_dest" value="{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump"/>
\<initParam name="compatible" value="11.2.0.0.0"/>
\<initParam name="remote_login_passwordfile" value="EXCLUSIVE"/>
\<initParam name="processes" value="150"/>
\<initParam name="undo_tablespace" value="UNDOTBS1"/>
\<initParam name="control_files" value="("{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control01.ctl", \"{ORACLE_BASE}/flash_recovery_area/{DB_UNIQUE_NAME}/control02.ctl")"/>
\<initParam name="diagnostic_dest" value="{ORACLE_BASE}"/>
\<initParam name="db_recovery_file_dest" value="{ORACLE_BASE}/flash_recovery_area"/>
\<initParam name="audit_trail" value="db"/>
\<initParam name="memory_target" value="250" unit="MB"/>
\<initParam name="db_block_size" value="8" unit="KB"/>
\<initParam name="open_cursors" value="300"/>
\<initParam name="db_recovery_file_dest_size" value="" unit="MB"/>
\</InitParams>
\<MiscParams>
\<databaseType>MULTIPURPOSE\</databaseType>
\<maxUserConn>20\</maxUserConn>
\<percentageMemTOSGA>40\</percentageMemTOSGA>
\<customSGA>false\</customSGA>
\<archiveLogMode>true\</archiveLogMode>
\<initParamFileName>{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/pfile/init.ora\</initParamFileName>
\</MiscParams>
\<SPfile useSPFile="true">{ORACLE_HOME}/dbs/spfile{SID}.ora\</SPfile>
\</InitParamAttributes>
\<StorageAttributes>
\<DataFiles>
"/u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/General_Purpose.dbc" 95L, 4985C written
[oracle@orcltest ~]$ strings $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc | grep -i arch
\<archiveLogMode>true\</archiveLogMode>
[oracle@orcltest ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname orastrac -sid orastrac -sysPassword lhr -systemPassword lhr -responseFile NO_VALUE -datafileDestination /u01/app/oracle/oradata -redoLogFileSize 50 -recoveryAreaDestination /u01/app/oracle/flash_recovery_area -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 30 -totalMemory 200 -databaseType OLTP -emConfiguration NONE -automaticMemoryManagement true
Copying database files
1% complete
3% complete
10% complete
17% complete
24% complete
35% complete
Creating and starting Oracle instance
37% complete
42% complete
47% complete
52% complete
53% complete
56% complete
58% complete
Registering database with Oracle Restart
64% complete
Completing Database Creation
68% complete
71% complete
75% complete
85% complete
86% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/orastrac/orastrac.log" for further details.
[oracle@orcltest ~]$
[oracle@orcltest ~]$ ORACLE_SID=orastrac
[oracle@orcltest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 1 11:09:02 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string orastrac
db_unique_name string orastrac
global_names boolean FALSE
instance_name string orastrac
lock_name_space string
log_file_name_convert string
service_names string orastrac
SQL> create user lhr identified by lhr;
User created.
SQL> grant dba to lhr;
Grant succeeded.
SQL> create table lhr.test_rac as select * from dba_objects;
Table created.
SQL> select count(1) FROM LHR.TEST_RAC;
COUNT(1)
----------
72468
SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL>
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileorastrac.o
ra
SQL>
source库执行备份操作
备份脚本如下,注意控制文件需要最后备份:
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format '/home/oracle/rmanback/full%n%T%t%s%p.bak';
sql 'alter system archive log current';
backup archivelog all format '/home/oracle/rmanback/arch%d%T%s_%p.bak' delete input;
backup current controlfile format '/home/oracle/rmanback/ctl%d%T%s_%p.bak';
release channel c1;
release channel c2;
}
执行过程如下:
[oracle@orcltest ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jun 1 11:12:15 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORASTRAC (DBID=1317814272)
RMAN> run
2> {
3> allocate channel c1 type disk;
4> allocate channel c2 type disk;
5> backup database format '/home/oracle/rmanback/full%n%T%t%s%p.bak';
6> sql 'alter system archive log current';
7> backup archivelog all format '/home/oracle/rmanback/arch%d%T%s_%p.bak' delete input;
8> backup current controlfile format '/home/oracle/rmanback/ctl%d%T%s_%p.bak';
9> release channel c1;
10> release channel c2;
11> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=142 device type=DISK
allocated channel: c2
channel c2: SID=20 device type=DISK
Starting backup at 2015-06-01 11:12:28
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orastrac/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orastrac/users01.dbf
channel c1: starting piece 1 at 2015-06-01 11:12:29
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/orastrac/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orastrac/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orastrac/undotbs01.dbf
channel c2: starting piece 1 at 2015-06-01 11:12:29
channel c2: finished piece 1 at 2015-06-01 11:15:26
piece handle=/home/oracle/rman_back/full_ORASTRAC_20150601_881233949_2_1.bak tag=TAG20150601T111228 comment=NONE
channel c2: backup set complete, elapsed time: 00:02:59
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
including current control file in backup set
channel c2: starting piece 1 at 2015-06-01 11:15:48
channel c2: finished piece 1 at 2015-06-01 11:15:49
piece handle=/home/oracle/rman_back/full_ORASTRAC_20150601_881234138_3_1.bak tag=TAG20150601T111228 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c2: starting piece 1 at 2015-06-01 11:15:50
channel c2: finished piece 1 at 2015-06-01 11:15:51
piece handle=/home/oracle/rman_back/full_ORASTRAC_20150601_881234150_4_1.bak tag=TAG20150601T111228 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
channel c1: finished piece 1 at 2015-06-01 11:15:53
piece handle=/home/oracle/rman_back/full_ORASTRAC_20150601_881233948_1_1.bak tag=TAG20150601T111228 comment=NONE
channel c1: backup set complete, elapsed time: 00:03:24
Finished backup at 2015-06-01 11:15:53
sql statement: alter system archive log current
Starting backup at 2015-06-01 11:16:05
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=881234164
channel c1: starting piece 1 at 2015-06-01 11:16:05
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=2 STAMP=881234165
channel c2: starting piece 1 at 2015-06-01 11:16:06
channel c1: finished piece 1 at 2015-06-01 11:16:09
piece handle=/home/oracle/rman_back/arch_ORASTRAC_20150601_5_1.bak tag=TAG20150601T111605 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:04
channel c1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/ORASTRAC/archivelog/2015_06_01/o1_mf_1_5bpqmmhbp.arc RECID=1 STAMP=881234164
channel c2: finished piece 1 at 2015-06-01 11:16:10
piece handle=/home/oracle/rman_back/arch_ORASTRAC_20150601_6_1.bak tag=TAG20150601T111605 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:04
channel c2: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/ORASTRAC/archivelog/2015_06_01/o1_mf_1_6bpqmmo4t.arc RECID=2 STAMP=881234165
Finished backup at 2015-06-01 11:16:10
Starting backup at 2015-06-01 11:16:10
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 2015-06-01 11:16:11
channel c1: finished piece 1 at 2015-06-01 11:16:12
piece handle=/home/oracle/rman_back/ctl_ORASTRAC_20150601_7_1.bak tag=TAG20150601T111610 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-06-01 11:16:12
released channel: c1
released channel: c2
RMAN> list backupset;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1 Full 501.84M DISK 00:02:42 2015-06-01 11:15:11
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20150601T111228
Piece Name: /home/oracle/rman_back/full_ORASTRAC_20150601_881233949_2_1.bak
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
2 Full 1027268 2015-06-01 11:12:29 /u01/app/oracle/oradata/orastrac/sysaux01.dbf
3 Full 1027268 2015-06-01 11:12:29 /u01/app/oracle/oradata/orastrac/undotbs01.dbf
5 Full 1027268 2015-06-01 11:12:29 /u01/app/oracle/oradata/orastrac/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
2 Full 9.33M DISK 00:00:10 2015-06-01 11:15:48
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20150601T111228
Piece Name: /home/oracle/rman_back/full_ORASTRAC_20150601_881234138_3_1.bak
Control File Included: Ckp SCN: 1027334 Ckp time: 2015-06-01 11:15:38
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
3 Full 80.00K DISK 00:00:00 2015-06-01 11:15:50
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20150601T111228
Piece Name: /home/oracle/rman_back/full_ORASTRAC_20150601_881234150_4_1.bak
SPFILE Included: Modification time: 2015-06-01 11:12:28
SPFILE db_unique_name: ORASTRAC
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
4 Full 599.97M DISK 00:03:24 2015-06-01 11:15:52
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20150601T111228
Piece Name: /home/oracle/rman_back/full_ORASTRAC_20150601_881233948_1_1.bak
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1027267 2015-06-01 11:12:29 /u01/app/oracle/oradata/orastrac/system01.dbf
4 Full 1027267 2015-06-01 11:12:29 /u01/app/oracle/oradata/orastrac/users01.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
5 41.16M DISK 00:00:02 2015-06-01 11:16:07
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20150601T111605
Piece Name: /home/oracle/rman_back/arch_ORASTRAC_20150601_5_1.bak
List of Archived Logs in backup set 5
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 5 1012317 2015-06-01 11:04:50 1027356 2015-06-01 11:15:59
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
6 2.00K DISK 00:00:02 2015-06-01 11:16:07
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20150601T111605
Piece Name: /home/oracle/rman_back/arch_ORASTRAC_20150601_6_1.bak
List of Archived Logs in backup set 6
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 6 1027356 2015-06-01 11:15:59 1027365 2015-06-01 11:16:05
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
7 Full 9.33M DISK 00:00:02 2015-06-01 11:16:12
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20150601T111610
Piece Name: /home/oracle/rman_back/ctl_ORASTRAC_20150601_7_1.bak
Control File Included: Ckp SCN: 1027386 Ckp time: 2015-06-01 11:16:10
RMAN> list backupset summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
1 B F A DISK 2015-06-01 11:15:11 1 1 NO TAG20150601T111228
2 B F A DISK 2015-06-01 11:15:48 1 1 NO TAG20150601T111228
3 B F A DISK 2015-06-01 11:15:50 1 1 NO TAG20150601T111228
4 B F A DISK 2015-06-01 11:15:52 1 1 NO TAG20150601T111228
5 B A A DISK 2015-06-01 11:16:07 1 1 NO TAG20150601T111605
6 B A A DISK 2015-06-01 11:16:07 1 1 NO TAG20150601T111605
7 B F A DISK 2015-06-01 11:16:12 1 1 NO TAG20150601T111610
RMAN>
将备份传递到target库
这个方法就多了,可以采用ftp上传下载,也可以采用NFS网络文件系统,或者scp命令都可以,这里我们采用scp直接传递到rac环境的第一个节点。
源库scp操作:
[oracle@orcltest ~]$ cd rman_back/
[oracle@orcltest rman_back]$ ll
total 1189660