1．实验环境简介 1.1 SOA原数据库 数据库名 COMSOA 实例名 COMSOA DBID 4133565260 数据库版本 Windows 64bit 10.2.0 数据文件目录 H:\ORACLE\ORADATA\COMSOAPfile comsoa.__db_cache_size=1291845632 comsoa.__java_pool_size=16777216 comsoa.__large_pool_size=16777216 comsoa.__shared_pool_size=805306368 comsoa.__streams_pool_size=0 *.audit_file_dest=‘H:\oracle\admin\COMSOA\adump‘ *.background_dump_dest=‘H:\oracle\admin\COMSOA\bdump‘ *.compatible=‘10.2.0.1.0‘ *.control_files=‘H:\oracle\oradata\COMSOA\control01.ctl‘,‘H:\oracle\oradata\COMSOA\control02.ctl‘,‘H:\oracle\oradata\COMSOA\control03.ctl‘ *.core_dump_dest=‘H:\oracle\admin\COMSOA\cdump‘ *.db_block_size=8192 *.db_file_multiblock_read_count=16 *.db_name=‘COMSOA‘ *.db_recovery_file_dest=‘H:\oracle\flash_recovery_area‘ *.db_recovery_file_dest_size=2147483648 *.log_archive_dest_1=‘location=H:\oracle\archive\comsoa‘ *.nls_language=‘SIMPLIFIED CHINESE‘ *.nls_territory=‘CHINA‘ *.open_cursors=300 *.pga_aggregate_target=536870912 *.processes=300 *.remote_login_passwordfile=‘EXCLUSIVE‘ *.sessions=335 *.sga_target=2147483648 *.undo_management=‘AUTO‘ *.undo_tablespace=‘UNDOTBS1‘ *.user_dump_dest=‘H:\oracle\admin\COMSOA\udump‘ 1.2 待恢SOA系统 数据库名 COMSOA 实例名 COMSOA DBID 4133565260 数据库版本 Windows 32bit 10.2 数据文件目录 E:\ORASOA\ORADATA\COMSOAPfile comsoa.__db_cache_size=891845632 comsoa.__java_pool_size=16777216 comsoa.__large_pool_size=16777216 comsoa.__shared_pool_size=505306368 comsoa.__streams_pool_size=0 *.audit_file_dest=‘e:\orasoa\admin\COMSOA\adump‘ *.background_dump_dest=‘e:\orasoa\admin\COMSOA\bdump‘ *.compatible=‘10.2.0.1.0‘ *.control_files=‘e:\orasoa\oradata\COMSOA\control01.ctl‘,‘e:\orasoa\oradata\COMSOA\control02.ctl‘,‘e:\orasoa\oradata\COMSOA\control03.ctl‘ *.core_dump_dest=‘e:\orasoa\admin\COMSOA\cdump‘ *.db_block_size=8192 *.db_file_multiblock_read_count=16 *.db_name=‘COMSOA‘ *.db_recovery_file_dest=‘e:\orasoa\flash_recovery_area‘ *.db_recovery_file_dest_size=2147483648 *.dispatchers=‘(PROTOCOL=TCP) (SERVICE=COMSOAXDB)‘ *.job_queue_processes=10 *.log_archive_dest_1=‘location=e:\orasoa\archive\comsoa‘ *.nls_language=‘SIMPLIFIED CHINESE‘ *.nls_territory=‘CHINA‘ *.open_cursors=300 *.pga_aggregate_target=336870912 *.processes=300 *.remote_login_passwordfile=‘EXCLUSIVE‘ *.sessions=335 *.sga_target=1147483648 *.undo_management=‘AUTO‘ *.undo_tablespace=‘UNDOTBS1‘ *.user_dump_dest=‘e:\orasoa\admin\COMSOA\udump‘ *._system_trig_enabled=false 2 备份 2.1 备份生产系统 C:\Administrator>Set oracle_sid=comsoa C:\Administrator>rman target sys/**** 删除过期的归档日志 RMAN>crosscheck archivelog all RMAN>delete expired archivelog RMAN>backup full database format ‘h:\full_%t_%p_%s.bak’ RMAN>backup archivelog all format ‘h:\arch_%p_t_%p.bak‘; RMAN> backup current controlfile format ‘h:\contol_%p_%p_%s.bak‘; 2.2 异机还原 2.2.1 文件夹创建 创建SOA实例所需要的文件夹,包括数据文件，dump,归档目录 e:\orasoa\admin\COMSOA\adump‘ e:\orasoa\admin\COMSOA\bdump‘ e:\orasoa\admin\COMSOA\cdump‘ e:\orasoa\admin\COMSOA\updump‘ db_recovery_file_dest=‘e:\orasoa\flash_recovery_area‘ e:\orasoa\oradata\COMSOA 2.2.2 创建SOA实例 创建所需要的实例COMSOA C:\Administrator>oradim.exe -new -sid COMSOA -startmode m C:\Administrator>oradim.exe -edit -sid COMSOA -startmode a 用pfile 启动数据库到弄mount状态 SQL>STARTUP NOMOUNT PFILE=’E:\INITCOMSOA.ORA’ 2.2.3 恢复控制文件 将生产的备份文件拷贝到新的SOA系统主机中并连接到RMAN C:\Administrator >SET ORACLE_SID=COMSOA C:\Administrator >rman target / 恢复管理器: Release 10.2.0.1.0 - Production on 星期六 12月 3 14:24:44 201 连接到目标数据库: COMSOA (未装载) RMAN> set dbid=4133565260 正在执行命令: SET DBID RMAN> catalog start with ‘备份集’ 恢复控制文件 RMAN> restore controlfile from ‘e:\CONTROL.BAK_1_20111203718‘; 启动 restore 于 03-12月-11 使用通道 ORA_DISK_1 通道 ORA_DISK_1: 正在复原控制文件 通道 ORA_DISK_1: 恢复完成, 用时: 00:00:01 完成 restore 于 03-12月-11 控制文件会自动恢复到e:\orasoa\oradata\COMSOA\ 中 2.2.4 更新控制文件redolog 信息 SQL>Alter database rename file ‘H:\oracle\oradata\COMSOA\redo01.log’ TO ‘E:\oracle\oradata\COMSOA\redo01.log’ SQL>Alter database rename file ‘H:\oracle\oradata\COMSOA\redo01.log’ TO ‘E:\oracle\oradata\COMSOA\redo01.log’ SQL>Alter database rename file ‘H:\oracle\oradata\COMSOA\redo01.log’ TO ‘E:\oracle\oradata\COMSOA\redo01.log’ 2.2.5 编译无效对象 SQL>@\orasoa\oradata\comsoa\utlrp.sql SQL>alter database open resetlogs SQL>shutdown immediate SQL>startup migrate 2.2.6 重建临时表空间 SQL> select name from v$tempfile; NAME -------------------------------------------------------------------------------- H:\ORACLE\ORADATA\COMSOA\TEMP01.DBF H:\ORACLE\ORADATA\COMSOA\TBS_TMP_DXPT.ORA H:\ORACLE\ORADATA\COMSOA\TBS_TMP_WORKFLOW.ORA 删除临时表空间 SQL> drop tablespace TEMP01 including contents and datafiles; 表空间已删除。 SQL> drop tablespace tbs_tmp_dxpt including contents and datafiles; 表空间已删除。 SQL> drop tablespace tbs_tmp_workflow including contents and datafiles; 表空间已删除。 重新创建临时表空间 SQL> create temporary tablespace tbs_tmp_dxpt 2 tempfile ‘e:\orasoa\oradata\comsoa\tbs_tmp_dxpt.ora‘ size 500M; 表空间已创建。 SQL> create temporary tablespace temp01 2 tempfile ‘e:\orasoa\oradata\comsoa\temp01.dbf‘ size 500M; 表空间已创建。 SQL> create temporary tablespace tbs_tmp_workflow 2 tempfile ‘e:\orasoa\oradata\comsoa\tbs_tmp_workflow‘ size 500M; 表空间已创建。 更改用户默认临时表空间 SQL> alter database default temporary tablespace temp01 SQL>alter user lgwt temporary tablespace tbs_tmp_comsoa SQL>alter user dxpt temporary tablespace tbs_tmp_dxpt 3 恢复遭遇的问题 3.1 ORA-19751:could not create the change trackingfile 在打开数据库时， 报错误提示如下： SQL> startup ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1247876 bytes Variable Size 79693180 bytes Database Buffers 79691776 bytes Redo Buffers 7139328 bytes Database mounted. ORA-19751: could not create the change tracking file ORA-19750: change tracking file: ‘E:\ORACLE\TRACE.LOG‘ ORA-27040: file create error, unable to create file OSD-04002: unable to open file O/S-Error: (OS 5) 拒绝访问。 ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) 系统找不到指定的文件。 tracking file: ‘E:\ORACLE\TRACE.LOG‘ 在系统内无法找到。 tracking file是优化增量备份的参数，暂可以不用这个功能。 禁止BLOCK CHANGE TRACKING功能 SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING; Database altered. SQL> shutdown immediate; ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1247876 bytes Variable Size 79693180 bytes Database Buffers 79691776 bytes Redo Buffers 7139328 bytes Database mounted. Database opened. SQL> 3.1.1 关于 tracking file 1． 通过位图跟踪两次备份间变化的数据块； 2． 每次备份前进行位图切换； 3． 开发增量备份策略时，要考虑到8个位图的限制； 4． 在RAC环境中，change tracking file需要放在共享存储上； 5． 参数Change tracking file的大小和数据库的大小和enabled的redo thread的个数成正比； 6． Change tracking file的大小和数据更新的频率无关； 7． 在mount或open状态下enable change tracking； 8．Enable change tracking ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/mydir/rman_change_track.f‘ REUSE; 9．Disable change tracking ALTER DATABASE DISABLE BLOCK CHANGE TRACKING; 10．检查change tracking状态 SELECT STATUS, FILENAME FROM V$BLOCK_CHANGE_TRACKING; 11． 改变change tracking file的位置 1) 不关闭数据库的方式 SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING; SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘new_location‘; 注意：这种方式会丢失change tracking file的内容 2) 关闭数据库的方式 SQL> SELECT FILENAME FROM V$BLOCK_CHANGE_TRACKING;(确定当前的文件名) SQL> SHUTDOWN IMMEDIATE 用操作系统命令将文件move到新路径 SQL> ALTER DATABASE RENAME FILE ‘/disk1/changetracking/o1_mf_2f71np5j_.chg‘ TO ‘/disk2/changetracking/o1_mf_2f71np5j_.chg‘; SQL> ALTER DATABASE OPEN; 测试遇到问题 ORA-06544: PL/SQL: internal error, arguments: [56319], ORA-06553: PLS-801: internal error [56319]" errors while running catupgrd.sql 迁移过程中遭遇oracle bug解决办法 Unpublished BUG 5079213 - ORA-06544 [56319] DURING UPGRADE FROM 10.1.0.5 32BIT TO 10.2.0.2 64BIT 解决办法 1. Change catupgrd.sql script to always execute utlip.sql 进入oracle_home目录 % cd $ORACLE_HOME/rdbms/admin 备份原catupgrd.sql脚本 % cp catupgrd.sql catupgrd_orig.sql 编辑catupgrd.sql脚本 % vi catupgrd.sql (vi is for unix-based systems, for windows - edit as a text document) 找到 ==> @@&utlip_file 并将其修改成以下内容 ==> @@utlip.sql 2. 运行修改后的catupgrd.sql脚本 SQL> spool /tmp/upgrade_aft_wa.out SQL> set echo on SQL> @?/rdbms/admin/catupgrd.sql
本文出自 “O Record” 博客，请务必保留此出处http://evils798.blog.51cto.com/8983296/1420910