在前两节中,分别介绍了使用expdp/impdp迁移单实例数据库到rac环境的方法,其中包括使用表空间传输的方式,但如果在用户数量多,或者表空间数量多,又或者函数,过程,包等代码对象多的条件下,使用前两种方式迁移未免麻烦且费时,因而在本节中将简要的介绍下使用rman备份的异机恢复来实现迁移单实例数据库到rac环境,这种方式美中不足的地方是数据库需要以open resetlogs方式打开,意味着对数据库做不完全恢复,可能会丢失少量的数据!
环境介绍:
数据库的版本均为10.2.0.5,数据库字符集编码为utf8
操作系统的版本单实例数据库(源库)为rhel5.4 64 bit 数据库名:orcl.yang.com 实例名:orcl
rac(目标数据库)为ceontos4.8 64bit
一:目标数据库准备工作,rman备份的异机恢复要求数据库名要一致,因而需要先删掉原有的racdb数据库和数据文件
[oracle@rac1 ~]$ srvctl stop database -d racdb -o immediate [oracle@rac1 ~]$ crs_stat -t -v 节点2上同样需要修改/etc/oratab文件 [oracle@rac1 ~]$ export ORACLE_SID=+ASM1 [oracle@rac1 ~]$ sqlplus /nolog SQL> conn /as sysdba SQL> col state format a10 STATE REDUNDANCY TOTAL_MB FREE_MB NAME FAILGROUP 备注:前5个磁盘分别为表决盘和ocr,data磁盘组做了normal冗余 SQL> select group_number,name,state,type,total_mb,free_mb,unbalanced from v$asm_diskgroup; GROUP_NUMBER NAME STATE TYPE TOTAL_MB FREE_MB UNB |
二:源库上建一个测试表,由spfile生成pfile
SQL> show parameter name; NAME TYPE VALUE SQL> create table test1.migrate(a varchar2(20)); SQL> insert into test1.migrate a values ('successful'); SQL> commit; SQL> create pfile='/home/oracle/rman_bak/initorcl.ora' from spfile; SQL> select file_id,file_name,tablespace_name from dba_data_files order by 1; FILE_ID FILE_NAME TABLESPACE_NAME SQL> select file_id,file_name,tablespace_name from dba_temp_files order by 1; FILE_ID FILE_NAME TABLESPACE_NAME SQL> select file_name,tablespace_name from dba_temp_files; FILE_NAME TABLESPACE_NAME |
三:在源库上使用rman进行全库备份,并将备份复制到目标数据库上
[oracle@server49 ~]$ rman target / RMAN> backup incremental level 0 RMAN> backup current controlfile format '/home/oracle/rman_bak/migrate.ctl'; RMAN> list backup of database summary; List of Backups [oracle@server49 ~]$ scp -rp /home/oracle/rman_bak/ rac1:/home/oracle/ |
四:在目标数据库上执行恢复
4.1 修改初始化参数文件如下
[oracle@rac1 ~]$ cat /home/oracle/rman_bak/initorcl.ora 备注:需要格外注意的是,这个时候不能加orcl2.thread=2参数,否则后面无法将数据库启动到mount状态 |
4.2 创建相关的目录,两个节点上都应该存在相应的目录和密码文件
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/admin/orcl/{adump,bdump,cdump,udump} [oracle@rac1 ~]$ cd $ORACLE_HOME/dbs [oracle@rac1 dbs]$ echo 'spfile=+DATA/ORCL/PARAMETERFILE/spfileorcl.ora' > initorcl1.ora [oracle@rac1 dbs]$ orapwd file=orapworcl1 password=123456 [oracle@rac2 ~]$ cd $ORACLE_HOME/dbs [oracle@rac2 dbs]$ echo 'spfile=+DATA/ORCL/PARAMETERFILE/spfileorcl.ora' > initorcl2.ora [oracle@rac2 dbs]$ orapwd file=orapworcl1 password=123456 |
4.3 利用pfile生成spfile,并保存在ASM实例上,ASM磁盘组中需要存在相应的目录,否则将报错!
[oracle@rac1 ~]$ export ORACLE_SID=orcl1 SQL> conn /as sysdba SQL> startup nomount pfile='/home/oracle/rman_bak/initorcl.ora'; Total System Global Area 213909504 bytes SQL> create spfile='+DATA/ORCL/PARAMETERFILE/spfileorcl.ora' from pfile='/home/oracle/rman_bak/initorcl.ora'; ASMCMD> pwd SQL> shutdown abort; |
4.4 将节点1数据库启动到nomount状态,恢复控制文件,重新将数据库启动到mount状态
[oracle@rac1 ~]$ sqlplus /nolog SQL> conn /as sysdba Total System Global Area 213909504 bytes [oracle@rac1 ~]$ rman target / Starting restore at 2012-01-03 13:15:11 channel ORA_DISK_1: restoring control file [oracle@rac1 ~]$ export ORACLE_SID=+ASM1 SQL> shutdown abort Total System Global Area 213909504 bytes SQL> select host_name,status,thread# from gv$instance; HOST_NAME STATUS THREAD# |
4.5 使用rman进行数据库恢复操作
[oracle@rac1 ~]$ rman target / RMAN> run { RMAN> report schema; RMAN-06139: WARNING: control file is not current for REPORT SCHEMA List of Permanent Datafiles List of Temporary Files ASMCMD> pwd RMAN> recover database; starting media recovery channel ORA_DISK_1: starting archive log restore to default destination |
4.6 对在线重做日志文件进行更名,添加thread 2日志组
SQL> select group#,member from v$logfile; GROUP# MEMBER SQL> alter database rename file '/u01/app/oracle/oradata/orcl/redo01.log' to '+DATA/orcl/onlinelog/redo01.dbf'; SQL> alter database rename file '/u01/app/oracle/oradata/orcl/redo02.log' to '+DATA/orcl/onlinelog/redo02.dbf'; SQL> alter database rename file '/u01/app/oracle/oradata/orcl/redo03.log' to '+DATA/orcl/onlinelog/redo03.dbf'; SQL> alter database add logfile thread 2 group 4 '+DATA' size 50M; SQL> alter database add logfile thread 2 group 5 '+DATA' size 50M; SQL> alter database add logfile thread 2 group 6 '+DATA' size 50M; SQL> select thread#,bytes/(1024*1024),status from v$log; THREAD# BYTES/(1024*1024) STATUS 6 rows selected. |
4.7 使用open resetlogs方式打开数据库
SQL> recover database using backup controlfile until cancel; Specify log: {<RET>=suggested | filename | AUTO | CANCEL} SQL> alter database open resetlogs; SQL> select open_mode,name from gv$database; OPEN_MODE NAME SQL> select comp_name,version,status from dba_registry; COMP_NAME VERSION STATUS Oracle Workspace Manager 10.2.0.5.0 VALID COMP_NAME VERSION STATUS |
4.8 修改相关的初始化参数,创建undotbs2表空间,启用thread 2日志组
SQL> alter system set thread=1 scope=spfile sid='orcl1'; SQL> alter system set thread=2 scope=spfile sid='orcl2'; SQL> select bytes/(1024*1024) MB from dba_data_files where tablespace_name='UNDOTBS1'; MB SQL> create undo tablespace undotbs2 datafile '+DATA/ORCL/DATAFILE/undotbs02.dbf' size 30M; SQL> alter system set undo_tablespace=undotbs2 sid='orcl2'; SQL> alter database enable thread 2; |
4.9 重新启动节点1数据库实例后,再启动节点2数据库实例;并验证结果
SQL> shutdown immediate SQL> startup Total System Global Area 213909504 bytes SQL> select instance_number,instance_name,host_name ,status from gv$instance; INSTANCE_NUMBER INSTANCE_NAME HOST_NAME STATUS SQL> select * from test1.migrate; A SQL> col file_name format a40 FILE_ID FILE_NAME TABLESPACE_NAME
|
五:收尾工作
SQL> @$ORACLE_HOME/rdbms/admin/catclust.sql; [oracle@rac1 ~]$ srvctl add database -d orcl -o $ORACLE_HOME [oracle@rac1 ~]$ srvctl stop database -d orcl -o immediate |
参考文章:http://blog.csdn.net/tianlesoftware/article/details/6432415(感谢作者分享!)