数据库迁移方案.doc

1、数据库迁移方案客户名称XXXX系统名称XXXX数据库名工程师XXXX用户确认XXXXX公司XXXX年XX月vJoint Contact Guide v3.0 Commercial in ConfidencePage v 文档控制此文档仅供最终用户审阅，不得向与此无关的个人或机构传阅或复制。修改记录日期作者版本修改记录XXXX-XX-XXXXXX1.0创建分发者姓名职位审阅记录姓名职位1. 概述年前完成XXXXX系统的数据库迁移工作，同时对源库进行小版本升级，有11.2.0.3升级到11.2.0.4版本。2. 迁移前准备工作步骤内容完成情况1目标库操作系统安装完成2目标库操作系统优化完成3目标库

2、RAC安装完成4目标库配置完成3. 源库备份rman target / msglog /u01/bak/rman.log append startup nomount -rman自启动一个实例RMANrestore spfile to pfile /u01/initdba.ora from /u01/bakup/xxx;注意：修改磁盘组名称，归档路径、控制文件路径，日志路径，trace文件路径、remote_listener4.3. 还原控制文件在其中一个节点上执行。4.3.1. 用pfile启动到nomount状态RMANstartup nomunt pfile=/u01/app/xx/in

3、itdba.ora;4.3.2. rman执行对控制文件的恢复RMAN restore controlfile from /HS5220/c-2006462633-20170123-03;Starting restore at 2017-02-04 12:16:56using channel ORA_DISK_1channel ORA_DISK_1: restoring control fileRMAN-00571: =RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =RMAN-00571: =RMAN-03002: failure of restore c

4、ommand at 02/04/2017 12:16:57ORA-19870: error while restoring backup piece /HS5220/c-2006462633-20170123-03ORA-19504: failed to create file +DG_DATAORA-17502: ksfdcre:4 Failed to create file +DG_DATAORA-15001: diskgroup DG_DATA does not exist or is not mountedORA-15040: diskgroup is incompleteORA-15

5、040: diskgroup is incompleteORA-15040: diskgroup is incompleteORA-15040: diskgroup is incompleteORA-15040: diskgroup is incompleteORA-15040: diskgroup is incompleteoracleora8db1 $ ls -l $ORACLE_HOME/bin/oracle-rwsr-s-x 1 oracle oinstall 239840968 3月 15 12:32 /u01/app/oracle/product/11.2.0/db_1/bin/o

6、racleoracleora8db1 $ exitlogoutrootora8db1 # su - gridgridora8db1 $ cd $ORACLE_HOME/bin/gridora8db1 bin$ setasmgidsetasmgid setasmgid0 setasmgidwrap gridora8db1 bin$ setasmgidwrap -o /u01/app/oracle/product/11.2.0/db_1/bin/oraclegridora8db1 bin$ exitlogoutrootora8db1 # su - oracleoracleora8db1 $ ls

7、-l $ORACLE_HOME/bin/oracle-rwsr-s-x 1 oracle asmadmin 239840968 3月 15 12:32 /u01/app/oracle/product/11.2.0/db_1/bin/oracleoracleora8db1 $ export ORACLE_SID=jsglrac1oracleora8db1 $ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 16 09:23:19 2017Copyright (c) 1982, 2011, Orac

8、le and/or its affiliates. All rights reserved.RMAN-00571: =RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =RMAN-00571: =RMAN-00554: initialization of internal recovery manager package failedRMAN-04005: error from target database: ORA-27140: attach to post/wait facility failedORA-27300: OS system dependen

9、t operation:invalid_egid failed with status: 1ORA-27301: OS failure message: Operation not permittedORA-27302: failure occurred at: skgpwinit6ORA-27303: additional information: startup egid = 1001 (oinstall), current egid = 1004 (asmadmin)oracleora8db1 $ ps -ef|grep ora_pmonoracle 155625 1 0 08:59 ?

10、 00:00:00 ora_pmon_jsglrac1oracle 173687 173043 0 09:24 pts/0 00:00:00 grep ora_pmonoracleora8db1 $ kill -9 155625oracleora8db1 $ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 16 09:25:18 2017Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to an idle instance.

11、SYSjsglrac1startup nomountORACLE instance started.Total System Global Area 3206836224 bytesFixed Size 2257520 bytesVariable Size 1174408592 bytesDatabase Buffers 1979711488 bytesRedo Buffers 50458624 bytesSYSjsglrac1exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64

12、bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing optionsoracleora8db1 $ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 16 09:25:37 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.c

13、onnected to target database: JSGLRAC (not mounted)RMAN restore controlfile from /backup/full_backup/jsglrac/ctl_file_0grv777r_1_1_20170315;Starting restore at 2017-03-16 09:25:57using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=1057 in

14、stance=jsglrac1 device type=DISKchannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:02output file name=+DG_DATA/jsglrac/controlfile/current.267.938769959Finished restore at 2017-03-16 09:25:59RMANRMANrestore controlfile from /u01/backup/xxx; 修改参数文件中控制文

15、件的名字4.4. 生成spfile，放在共享设备上oracleora8db1 $ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 16 09:29:11 2017Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Real Ap

16、plication Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsSYSjsglrac1create spfile=+DG_DATA from pfile;File created.rootora8db1 # su - gridgridora8db1 $ asmcmdASMCMD lsDG_DATA/DG_GI/ASMCMD cd DG_DATAASMCMD lsJSGLRAC/ASMCMD cd JSGLRACASMCMD lsCONTROLFILE/P

17、ARAMETERFILE/ASMCMD cd PARAMETERFILEASMCMD lsspfile.256.938770171ASMCMD ls -lType Redund Striped Time Sys NamePARAMETERFILE UNPROT COARSE MAR 16 09:00:00 Y spfile.256.938770171修改所有节点的pfile内容，指向spfileSPFILE=+DATA/XX/spfiledba.ora4.5. 创建口令文件$orapwd file=?/dbs/orapwdba password=oracle在所有节点上创建口令文件。注意：密码

18、要和原库一样4.6. restore数据库4.6.1. 将数据库启动到mount状态 -两个节点都要mountSQLalter database mount;在其中一个节点上执行注册备份信息到控制文件RMANcatalog start with /u01/backup/; -后面一定要添加“/”字符restore 数据文件先在源库执行：set linesize 100set linesize 160 pagesize 2000col file_name for a60select set newname for datafile |file_id| to |chr(39)|+DATA|chr(

19、39)|; from dba_data_files;生成修改数据文件名的批量语句runallocate channel d1 type disk;allocate channel d2 type disk;allocate channel d3 type disk;allocate channel d4 type disk;set newname 此处加入上面查出的批量语句restore database;SWITCH DATAFILE ALL;release channel d1;release channel d2;release channel d3;release channel d4

20、;(-恢复归档文件RMANcatalog start with /HS5220/;RMANlist backup of archivelog all;run allocate channel c1 type disk;allocate channel c2 type disk;allocate channel c3 type disk;allocate channel c4 type disk;set archivelog destination to /HS5220;restore archivelog from logseq 170171 until logseq 170226 threa

21、d 1;restore archivelog from logseq 149060 until logseq 149109 thread 2;release channel c1;release channel c2;release channel c3;release channel c4;)Recover 过程中会自动restore归档文件，同时recover归档文件4.6.2. 查看源端数据库文件存放位置信息select file#,name from v$datafile;查看目标库中文件号与文件名对应关系，并记录4.7. recover数据库注册归档信息到控制文件RMANcatalo

22、g start with /u01/archivelog/;RMANrecover database;/ * 先看归档的list信息RMANlist backup of archivelog all;这里要注意，这里涉及到2个节点的归档文件，我们要找到2个节点最后的归档文件，看下哪个归档文件的scn小，然后恢复到这个scn。RMANrun set untio scn 2986227;recover database;*/4.8. 关闭源库停应用程序。在scott用户下创建一个测试表，来验证将来是否恢复完全查看是否有大查询select count(*) from gv$session_longo

23、ps where time_remaining0;查看大事物select sum(used_ublk) from gv$transaction;查看恢复操作select * from gv$fast_start_transactions;select * from gv$fast_start_servers;清除col_usage$记录，当关闭数据库的时候就不会有大量的记录需要清理exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;alter system switch logfile;alter system switch logfile;alter

24、 system checkpoint;alter system checkpoint;srvctl stop database -d PRD -o immediatesrvctl start database d PRD o mount4.9. 拷贝源端剩下的归档和online redo从11.2.0.3开始支持本地ASM到远端ASM之间的copy。归档文件可以放在本地，也可以放在ASM磁盘组中。4.10. 再次recover我们的归档文件是从其他地方copy过来的，当前的数据库并没有相关记录。所以我们需要先将归档文件注册到控制文件中，然后才能使用。RAMNcatalog archivelog

25、 /u01/backup/xx;拷贝过来的online redo file，必须放在参数文件中指定的redo路径下。ASMCMD cp /backup/group_1.258.886258033 group_1copying /backup/group_1.258.886258033 - +dg_data/jsglrac/onlinelog/group_1RMANrecover database; - 完全恢复4.11. upgate db关闭2节点数据库,同时在节点1上修改参数-关闭归档SQLatler system set cluster_database=false scope=spfi

26、le;SQLshutdown immediate;SQLstartupSQLalter database open resetlogs;SQLshutdown immediate;SQLstartup upgrade;SQL?/rdbms/admin/catupgrd.sql - SQLshutdown immediate;SQLstartupSQL?/rdbms/admin/utlrp.sql -编译失效对象SQalter system set cluster_database=true scope=spfile;crsctl stat res t#crsctl stop cluster allalter system set remote_listener=rac12-sc