单实例数据库迁移到RAC环境.docx

此文档收集于网络，如有侵权请联系网站删除单实例数据库迁移到RAC环境环境介绍：数据库的版本均为.0，数据库字符集编码为AMERICAN_AMERICA.ZHS16GBK操作系统的版本单实例数据库(源库)为rhel5.8 64 bit 数据库名：db11g 实例名：db11gRAC(目标数据库)为rhel5.8 64 bit一目标数据库准备工作，rman备份的异机恢复要求数据库名要一致，因而需要先删掉原有的db11g数据库和数据文件oraclerac1 $ srvctl stop database -d db11g -o immediateoraclerac1 $ srvctl remove database -d db11g Remove the database db11g? (y/n) yoraclerac1 $ crs_stat -t -vName Type R/RA F/FT Target State Host -ora.SM1.asm application 0/5 0/0 ONLINE ONLINE rac1 ora.C1.lsnr application 0/5 0/0 ONLINE ONLINE rac1 ora.rac1.gsd application 0/5 0/0 ONLINE ONLINE rac1 ora.rac1.ons application 0/3 0/0 ONLINE ONLINE rac1 ora.rac1.vip application 0/0 0/0 ONLINE ONLINE rac1 ora.SM2.asm application 0/5 0/0 ONLINE ONLINE rac2 ora.C2.lsnr application 0/5 0/0 ONLINE ONLINE rac2 ora.rac2.gsd application 0/5 0/0 ONLINE ONLINE rac2 ora.rac2.ons application 0/3 0/0 ONLINE ONLINE rac2 ora.rac2.vip application 0/0 0/0 ONLINE ONLINE rac2修改/etc/oratab文件(节点2上同样需要)rootrac1 # tail -n 1 /etc/oratab +ASM1:/u01/app/oracle/product/10.2.0/db_1:Noraclerac1 $ export ORACLE_SID=+ASM1oraclerac1 $ asmcmdASMCMD rm -rf +DATA/DB11GASMCMD rm -rf +FRA/DB11Goraclerac1 $ sqlplus /nologSQL conn /as sysdbaConnected.SQL col state format a10SQL col name format a15SQL col failgroup format a20 SQL set line 200SQL select state,redundancy,total_mb,free_mb,name,failgroup from v$asm_disk;STATE REDUNDANCY TOTAL_MB FREE_MB NAME FAILGROUP- - - - - -NORMAL UNKNOWN 286 0NORMAL UNKNOWN 286 0NORMAL UNKNOWN 286 0NORMAL UNKNOWN 286 0NORMAL UNKNOWN 286 0NORMAL UNKNOWN 10208 10114 FRA_0000 FRA_0000NORMAL UNKNOWN 9537 9443 DATA_0001 DATA_0001NORMAL UNKNOWN 9537 9443 DATA_0000 DATA_00008 rows selected.备注：前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- - - - - - - 1 DATA MOUNTED NORMAL 19074 18886 N 2 FRA MOUNTED EXTERN 10208 10114 N二源库上建一个测试表，由spfile生成pfile SQL show parameter name;NAME TYPE VALUE- - -db_file_name_convert stringdb_name string db11gdb_unique_name string db11gglobal_names boolean FALSEinstance_name string db11glock_name_space stringlog_file_name_convert stringservice_names string db11gSQL create table test1.migrate(a varchar2(20);Table created.SQL insert into test1.migrate a values (successful);1 row created.SQL commit;Commit complete.SQL create pfile= /backup_ora/rman_bak/initdb11g.ora from spfile;SQL select file_id,file_name,tablespace_name from dba_data_files order by 1; FILE_ID FILE_NAME TABLESPACE_NAME- - - 1 /u01/app/oracle/oradata/db11g/system01.dbf SYSTEM 2 /u01/app/oracle/oradata/db11g/undotbs01.dbf UNDOTBS1 3 /u01/app/oracle/oradata/db11g/sysaux01.dbf SYSAUX 4 /u01/app/oracle/oradata/db11g/users01.dbf USERS 5 /u01/app/oracle/oradata/db11g/example01.dbf EXAMPLE 6 /u01/app/oracle/oradata/db11g/exp_rac01.dbf EXP_RAC 7 /u01/app/oracle/oradata/db11g/exp_rac_index01.dbf EXP_RAC_INDEX 8 /u01/app/oracle/oradata/db11g/exp_rac1_01.dbf EXP_RAC1 9 /u01/app/oracle/oradata/db11g/exp_rac1_index_01.dbf EXP_RAC1_INDEX9 rows selected.SQL select file_id,file_name,tablespace_name from dba_temp_files order by 1; FILE_ID FILE_NAME TABLESPACE_NAME- - - 1 /u01/app/oracle/oradata/db11g/temp01.dbf TEMPSQL select file_name,tablespace_name from dba_temp_files;FILE_NAME TABLESPACE_NAME- -/u01/app/oracle/oradata/db11g/temp01.dbf TEMP三在源库上使用rman进行全库备份，并将备份复制到目标数据库上 oracleserver49 $ rman target /Recovery Manager: Release .0 - Production on Tue Jan 3 12:10:06 2012Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database: DB11G (DBID=1299224612)RMAN backup incremental level 0 2 format /home/oracle/rman_bak/db11g_%U3 database plus archivelog4 delete all input;RMAN backup current controlfile format /home/oracle/rman_bak/migrate.ctl;RMAN list backup of database summary;List of Backups=Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag- - - - - - - - - -2 B 0 A DISK 03-JAN-12 1 1 NO TAG20120103T121503oracleserver49 $ scp -rp /home/oracle/rman_bak/ rac1:/home/oracle/四在目标数据库上执行恢复4.1 修改初始化参数文件如下*._db_cache_size=79691776*._java_pool_size=4194304*._large_pool_size=4194304*._shared_pool_size=109051904*._streams_pool_size=8388608db11g1.instance_name=db11g1db11g2.instance_name=db11g2db11g1.instance_number=1db11g2.instance_number=2*.cluster_database=true*.cluster_database_instances=2db11g1.thread=1*.compatible=.0*.control_files=+DATA/db11g/controlfile/control01.ctl,+FRA/db11g/controlfile/control02.ctl*.audit_file_dest=/u01/app/oracle/admin/db11g/adump*.core_dump_dest=/u01/app/oracle/admin/db11g/cdump*.db_block_size=8192*.db_file_multiblock_read_count=16*.db_name=db11g*.db_recovery_file_dest=+FRA*.db_recovery_file_dest_size=21474836480*.log_archive_dest_1=LOCATION=+FRA/db11g/archivelog*.dispatchers=(PROTOCOL=TCP) (SERVICE=db11gXDB)*.job_queue_processes=10*.log_archive_format=%t_%s_%r.arc*.open_cursors=300*.pga_aggregate_target=71303168*.processes=150*.remote_login_passwordfile=EXCLUSIVE*.sga_target=213909504*.undo_management=AUTOdb11g1.undo_tablespace=UNDOTBS1db11g2.undo_tablespace=UNDOTBS2备注：需要格外注意的是，这个时候不能加db11g2.thread=2参数，否则后面无法将数据库启动到mount状态4.2 创建相关的目录，两个节点上都应该存在相应的目录和密码文件 oraclerac1 $ mkdir -p /u01/app/oracle/admin/db11g/adump,bdump,cdump,udumporaclerac1 $ cd $ORACLE_HOME/dbsoraclerac1 dbs$ echo spfile=+DATA/DB11G/PARAMETERFILE/spfiledb11g.ora initdb11g1.oraoraclerac1 dbs$ orapwd file=orapwdb11g1 password=123456oraclerac2 $ cd $ORACLE_HOME/dbsoraclerac2 dbs$ echo spfile=+DATA/DB11G/PARAMETERFILE/spfiledb11g.ora initdb11g2.oraoraclerac2 dbs$ orapwd file=orapwdb11g2 password=1234564.3 利用pfile生成spfile，并保存在ASM实例上，ASM磁盘组中需要存在相应的目录，否则将报错！ oraclerac1 $ export ORACLE_SID=db11g1oraclerac1 $ sqlplus /nologSQL*Plus: Release .0 - Production on Tue Jan 3 13:00:10 2012Copyright (c) 1982, 2010, Oracle. All Rights Reserved.SQL conn /as sysdbaConnected to an idle instance.SQL startup nomount pfile=/home/oracle/rman_bak/initdb11g.ora;ORACLE instance started.Total System Global Area 213909504 bytesFixed Size 2095152 bytesVariable Size 125831120 bytesDatabase Buffers 79691776 bytesRedo Buffers 6291456 bytesSQL create spfile=+DATA/DB11G/PARAMETERFILE/spfiledb11g.ora from pfile=/home/oracle/rman_bak/initdb11g.ora;File created.ASMCMD pwd+DATA/DB11G/PARAMETERFILEASMCMD lsspfiledb11g.oraSQL shutdown abort;ORACLE instance shut down.4.4 将节点1数据库启动到nomount状态，恢复控制文件，重新将数据库启动到mount状态 oraclerac1 $ sqlplus /nologSQL*Plus: Release .0 - Production on Tue Jan 3 13:09:48 2012Copyright (c) 1982, 2010, Oracle. All Rights Reserved.SQL conn /as sysdbaConnected to an idle instance.SQL startup nomountORACLE instance started.Total System Global Area 213909504 bytesFixed Size 2095152 bytesVariable Size 125831120 bytesDatabase Buffers 79691776 bytesRedo Buffers 6291456 bytesoraclerac1 $ rman target /Recovery Manager: Release .0 - Production on Tue Jan 3 13:15:05 2012Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database: db11g (not mounted)RMAN restore controlfile from /home/oracle/rman_bak/migrate.ctl;Starting restore at 2012-01-03 13:15:11using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=153 instance=db11g1 devtype=DISKchannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:08output filename=+DATA/db11g/controlfile/control01.ctloutput filename=+FRA/db11g/controlfile/control02.ctlFinished restore at 2012-01-03 13:15:19oraclerac1 $ export ORACLE_SID=+ASM1oraclerac1 $ asmcmdASMCMD pwd+data/db11g/controlfileASMCMD lscontrol01.ctlcurrent.263.771599715ASMCMD cd +fra/db11g/controlfileASMCMD lscontrol02.ctlcurrent.260.771599717SQL shutdown abortORACLE instance shut down.SQL startup mountORACLE instance started.Total System Global Area 213909504 bytesFixed Size 2095152 bytesVariable Size 125831120 bytesDatabase Buffers 79691776 bytesRedo Buffers 6291456 bytesDatabase mounted.SQL select host_name, status, thread# from gv$instance;HOST_NAME STATUS THREAD#- - -db11g MOUNTED 14.5 使用rman进行数据库恢复操作 oraclerac1 $ rman target /Recovery Manager: Release .0 - Production on Tue Jan 3 17:29:22 2012Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database: DB11G (DBID=1299224612, not open)RMAN run 2 set newname for datafile 1 to +DATA/db11g/datafile/system01.dbf;3 set newname for datafile 2 to +DATA/db11g/datafile/undotbs01.dbf;4 set newname for datafile 3 to +DATA/db11g/datafile/sysaux01.dbf;5 set newname for datafile 4 to +DATA/db11g/datafile/users01.dbf;6 set newname for datafile 5 to +DATA/db11g/datafile/example01.dbf;7 set newname for datafile 6 to +DATA/db11g/datafile/exp_rac01.dbf;8 set newname for datafile 7 to +DATA/db11g/datafile/exp_rac_index01.dbf;9 set newname for datafile 8 to +DATA/db11g/datafile/exp_rac1_01.dbf;10 set newname for datafile 9 to +DATA/db11g/datafile/exp_rac1_index_01.dbf;11 set newname for tempfile 1 to +DATA/db11g/tempfile/temp01.dbf;12 restore database;13 switch datafile all;14 switch tempfile all;15 RMAN report schema;RMAN-06139: WARNING: control file is not current for REPORT SCHEMAReport of database schemaList of Permanent Datafiles=File Size(MB) Tablespace RB segs Datafile Name- - - - -1 440 SYSTEM * +DATA/db11g/datafile/system01.dbf2 30 UNDOTBS1 * +DATA/db11g/datafile/undotbs01.dbf3 250 SYSAUX * +DATA/db11g/datafile/sysaux01.dbf4 5 USERS * +DATA/db11g/datafile/users01.dbf5 100 EXAMPLE * +DATA/db11g/datafile/example01.dbf6 410 EXP_RAC * +DATA/db11g/datafile/exp_rac01.dbf7 300 EXP_RAC_INDEX * +DATA/db11g/datafile/exp_rac_index01.dbf8 300 EXP_RAC1 * +DATA/db11g/datafile/exp_rac1_01.dbf9 300 EXP_RAC1_INDEX * +DATA/db11g/datafile/exp_rac1_index_01.dbfList of Temporary Files=File Size(MB) Tablespace Maxsize(MB) Tempfile Name- - - - -1 0 TEMP 32767 +DATA/db11g/tempfile/temp01.dbfASMCMD pwd+data/db11g/datafileASMCMD lsEXAMPLE.268.771620011EXP_RAC.260.771620003EXP_RAC1.262.771620007EXP_RAC1_INDEX.266.771620007EXP_RAC_INDEX.261.771620003SYSAUX.267.771620009SYSTEM.297.771619999UNDOTBS1.305.771620015USERS.309.771620065example01.dbfexp_rac01.dbfexp_rac1_01.dbfexp_rac1_index_01.dbfexp_rac_index01.dbfsysaux01.dbfsystem01.dbfundotbs01.dbfusers01.dbfRMAN recover database;Starting recover at 2012-01-03 18:59:41using channel ORA_DISK_1starting media recoverychannel ORA_DISK_1: starting archive log restore to default destinationchannel ORA_DISK_1: restoring archive logarchive log thread=1 sequence=20channel ORA_DISK_1: reading from backup piece /home/oracle/rman_bak/db11g_09mvru2e_1_1channel ORA_DISK_1: restored backup piece 1piece handle=/home/oracle/rman_bak/db11g_09mvru2e_1_1 tag=TAG20120103T183453channel ORA_DISK_1: restore complete, elapsed time: 00:00:02archive log filename=+FRA/db11g/archivelog/1_20_771443882.arc thread=1 sequence=20unable to find archive logarchive log thread=1 sequence=21RMAN-00571: =RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =RMAN-00571: =RMAN-03002: failure of recover command at 01/03/2012 18:59:47RMAN-06054: media recovery requesting unknown log: thread 1 seq 21 lowscn 4793254.6 对在线重做日志文件进行更名，添加thread 2日志组 SQL select group#, member from v$logfile; GROUP# MEMBER- - 3 /u01/app/oracle/oradata/db11g/redo03.log 2 /u01/app/oracle/oradata/db11g/redo02.log 1 /u01/app/oracle/oradata/db11g/redo01.logSQL alter database rename file /u01/app/oracle/oradata/db11g/redo01.log to +DATA/db11g/onlinelog/redo01.dbf; Database altered.SQL alter database rename file /u01/app/oracle/oradata/db11g/redo02.log to +DATA/db11g/onlinelog/redo02.dbf; Database altered.SQL alter database rename file /u01/app/oracle/oradata/db11g/redo03.log to +DATA/db11g/onlinelog/redo03.dbf; Database altered.SQL alter database add logfile thread 2 group 4 +DATA size 50M;Database altered.SQL alter database add logfile thread 2 group 5 +DATA size 50M;Database altered.SQL alter database add logfile thread 2 group 6 +DATA size 50M;Database altered.SQL select thread, bytes/(1024*1024), status from v$log; THREAD# BYTES/(1024*1024) STATUS- - - 1 50 INACTIVE 1 50 ACTIVE 1 50 CURRENT 2 50 UNUSED 2 50 UNUSED 2 50 UNUSED6 rows selected.4.7 使用open resetlogs方式打开数据库 oraclerac1 $ rman target /Recovery Manager: Release .0 - Production on Thu Jul 24 21:46:20 2014Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database: DB11G (DBID=297163938)RMAN list backup;List of Archived Logs in backup set 3360Thrd Seq Low SCN Low Time Next SCN Next Time- - - - - -1 2208 103527370 2014-07-03 10:44:04 103528532 2014-07-03 11:01:28RMANrun set until sequence 2209;recover database;SQL alter database open resetlogs;Database altered.SQL select open_mode, name from gv$database;OPEN_MODE NAME- -READ WRITE DB11GSQL select comp_name,version,status from dba_registry;COMP_NAME-VERSION STATUS- -OWB.0 VALIDOracle Application Express0.08 VALIDOracle Enterprise Manager.0 VALIDCOMP_NAME-VERSION STATUS- -Oracle Ultra Search.0 VALIDOLAP Catalog.0 VALIDSpatial.0 VALIDCOMP_NAM