ORACLE8i升级9207实施手册.doc

精品文档XX国税综合征管软件查询数据库ORACLE 8174 UPGRADE 9207 实施手册目录1.确定升级方式32.备份旧库XX查询机未做备份33.原库停应用、停计划任务34.安装ORACLE 9201,打补丁至920745.将原数据库HB_CTAIS从8.1.7.4升级到9.2.0.746.应用ORACLE9i新特性57.动数据库JOB计划任务88.启动OS级与数据库关联的脚本计划任务89.ORACLE817升9207实施完成8写在文尾：9附1：列表A10附2：列表B111. 确定升级方式对原数据库进行手动升级，直接将ORACLE 817数据库升级到9207数据库。2. 备份旧库XX查询机未做备份对原数据库实施冷全备，即在数据库关闭状态下，对数据库实施一致性的全备份。3. 原库停应用、停计划任务1) 检查失效对象信息收集CREATE TABLE SCS_INVALIDASSELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS = INVALID;附件留存：select * from scs_invalid;2) 停数据库JOB计划任务-DROP TABLE SCS_JOB;CREATE TABLE SCS_JOBASSELECT A.JOB, A.SCHEMA_USER FROM DBA_JOBS A WHERE A.BROKEN = N;DECLARE CURSOR CU_JOB IS SELECT A.JOB,A.SCHEMA_USER FROM SCS_JOB A;BEGIN FOR CUR_JOB IN CU_JOB LOOP SYS.DBMS_IJOB.BROKEN(CUR_JOB.JOB,TRUE); END LOOP; COMMIT;END;/附件留存：select * from scs_job;3) 停操作系统级与数据库关联的脚本计划任务crontab -e注： 停止与数据库相关的脚本，客户有时喜欢在这里订制计划任务，如statspack4) 检查数据库system与回滚表空间SELECT SUM(BYTES)/1024/1024 M FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME=SYSTEM;SELECT SUM(BYTES)/1024/1024 M FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME=%RBS%;注：剩余空间需超过150M，可以进行升级。5) 停数据库、停监听sqlplus /as sysdbashutdown immediatelsnrctl stop4. 安装ORACLE 9201,打补丁至92071) 更改OS用户oracle的环境变量，如下：su - oraclecd cp .profile .profile.backvi .profileORACLE_BASE=/oracle/ora92; export ORACLE_BASEORACLE_HOME=/oracle/ora92; export ORACLE_HOME2) FTP上传介质文件介质文件大小合计：2.3G,介质文件解压预计：1G,ORACLE9i软件安装需求：2G,请系统管理员协助，保证主机系统有足够的文件空间使用。3) 安装软件ORACLE 9201su - oraclegunzip server_9201_hpunix64_disk1.cpio.gzcpio -idm server_9201_hpunix64_disk1.cpioexport DISPLAY=XXX.XXX.XXX.XXX:0.0 操作台IP地址：操作要规范，必需输入此命令installer注： 安装至/oracle/ora92目录下,仅安装软件。 安装交互过程中，如果弹出“选择JDK 1.3 路径”，HP路径为“/opt/java1.3”4) 打补丁至9207直接打补丁至9207，注意ORA_HOME路径选择为新安装的9201路径。5. 将原数据库HB_CTAIS从8.1.7.4升级到9.2.0.71) 复制原库的密码文件、TNS文件、监听文件至新软件目录下cp ./orapwHB_CTAIS $ORACLE_HOME/dbs/orapwHB_CTAIScp ./network/admin/tnsnames.ora $ORACLE_HOME/network/admin/tnsnames.oracp ./network/admin/listener.ora $ORACLE_HOME/network/admin/listener.ora复制完成后，打开TNS文件与监听文件，更改其中相应的路径ORACLE9207相关路径。2) 复制原库初始化参数文件至新库软件地址进行更改cp ./initHBCTAIS.ora $ORACLE_HOME/dbs/initHBCTAIS.ora对原库参数文件的副本进行修改设置参数 _SYSTEM_TRIG_ENABLED =FALSE 设置参数 OPTIMIZER_MODE = CHOOSE设置参数 MAX_ENABLED_ROLES 148确定USER_DUMP_DEST、BACKGROUND_DUMP_DEST 、CORE_DUMP_DEST地址确定shared_pool_size、large_pool_size大于150M注释参数JOB_QUEUE_PROCESSES 注释参数AQ_TM_PROCESSES 注释参数SNAPSHOT_REFRESH_? 注释参数 RESOURCE_MANAGER_PLAN注释所有附1：列表A中出现的参数替换所有附2：列表B中出现的参数3) 在ORACLE9i环境下，启动原库HB_CTAIS至MIGRATE状态下sqlplus /as sysdbastartup migrate;spool /oracle/log/upgrade.Log 打印日志：操作要规范，必需输入此命令4) 应用升级脚本$ORACLE_HOME/rdbms/admin/u0801070.sql5) 检查升级情况SELECT comp_name, version, status FROM dba_registry;SELECT * FROM V$VERSION;6) 其他组件升级如果有的话，比如安装了JAVA东东$ORACLE_HOME/rdbms/admin/cmpdbmig.sql 检查各组件升级情况SELECT comp_name, version, status FROM dba_registry;spool off注：如用户无其他组件，运行此句后，ORACLE会直接跳过不执行。7) 重编译数据库失效对象shutdown immediatestartup restrict$ORACLE_HOME/rdbms/admin/utlrp.sqlshutdown immediate8) 启动数据库监听lsnrctlstartstatus6. 应用ORACLE9i新特性1) 更改初始化参数文件，并转换为spfile恢复JOB_QUEUE_PROCESSES去除参数 _SYSTEM_TRIG_ENABLED =FALSE 去除参数 db_block_buffers=nn增加参数 db_cache_size=NN修改参数 compatible=9.2.0.0.0startupcreate spfile from pfile;create pfile from spfile;shutdown immediatestartup2) 自动UNDO管理 删除原RBS、RBS1表空间。-ALTER ROLLBACK SEGMENT RBS0 OFFLINESELECT ALTER ROLLBACK SEGMENT | A.SEGMENT_NAME | OFFLINE; FROM DBA_ROLLBACK_SEGS A WHERE A.TABLESPACE_NAME LIKE RBS%DROP TABLESPACE RBS INCLUDING CONTENTS;DROP TABLESPACE RBS1 INCLUDING CONTENTS; 新建一个UNDO表空间。create undo tablespace undotbs datafile /dev/chaxun_datavg/rlvchaxun08g05 size 8000m, /dev/chaxun_datavg/rlvchaxun08g06 size 8000m, /dev/chaxun_datavg/rlvchaxun08g07 size 8000m, /dev/chaxun_datavg/rlvchaxun08g08 size 8000m, /dev/chaxun_datavg/rlvchaxun08g09 size 8000m, /dev/chaxun_datavg/rlvchaxun08g10 size 8000m, /dev/chaxun_datavg/rlvchaxun08g11 size 8000m, /dev/chaxun_datavg/rlvchaxun08g12 size 8000m extent management local; 修改初始化参数shutdown immediaterm $ORACLE_HOME/dbs/spfile*vi $ORACLE_HOME/dbs/initHB_CTAIS.ora注释参数：rollback_segments加入参数：undo_management=auto加入参数：undo_tablespace=undotbs 重启数据库startupcreate spfile from pfile;3) 将表空间由“字典管理”更改为“本地管理”SELECT A.TABLESPACE_NAME, A.EXTENT_MANAGEMENT FROM DBA_TABLESPACES A; 将非SYSTEM、UNDO、TEMP表空间转换为“本地管理”模式-EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(TB_CTAIS);SELECT EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL( | A.TABLESPACE_NAME | ); FROM DBA_TABLESPACES A WHERE A.EXTENT_MANAGEMENT = DICTIONARY; 将临时表空间“TS_TMP”转换为“本地管理”模式-新建临时表空间“TEMP”，并设为系统默认临时表空间CREATE TEMPORARY TABLESPACE TEMP TEMPFILE /oracle/log/TEMP.dbf SIZE 40M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;-删除原临时表空间DROP TABLESPACE TS_TMP INCLUDING CONTENTS;-重建原临时表空间“TS_TMP”为“本地管理”模式，并设为系统默认临时表空间CREATE TEMPORARY TABLESPACE TS_TMP TEMPFILE /dev/chaxun_datavg/rlvchaxun08g01 SIZE 8000M, /dev/chaxun_datavg/rlvchaxun08g02 SIZE 8000M, /dev/chaxun_datavg/rlvchaxun08g03 SIZE 8000M, /dev/chaxun_datavg/rlvchaxun08g04 SIZE 8000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TS_TMP;-删除临时表空间“TEMP”DROP TABLESPACE TS_TMP INCLUDING CONTENTS; 将系统表空间“SYSTEM”转换为“本地管理”模式-启动数据库至RESTRICT状态startup restrict-让非SYSTEM、UNDO、TEMP表空间只读-ALTER TABLESPACE TB_NAME READ ONLY;SELECT ALTER TABLESPACE | TABLESPACE_NAME | READ ONLY;, STATUS FROM DBA_TABLESPACES WHERE EXTENT_MANAGEMENT = DICTIONARY;-将表空间全部转换为本地管理EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(SYSTEM);4) 重启数据库shutdown immediatestartup7. 动数据库JOB计划任务DECLARE CURSOR CU_JOB IS SELECT A.JOB,A.SCHEMA_USER FROM SCS_JOB A;BEGIN FOR CUR_JOB IN CU_JOB LOOP DBMS_IJOB.RUN(CUR_JOB.JOB); END LOOP; COMMIT;END;/8. 启动OS级与数据库关联的脚本计划任务9. ORACLE817升9207实施完成写在文尾：CTAIS系统查询库，是通过使用物化视图实现数据同步的。查询库在成功升到9207后，需跟踪一下物化视图刷新情况,确定是否正常刷新数据。XX国税出现一情况，很多物化视图第一次无法刷新，但第二次再刷，即可顺利完成。相关日志摘录如下Connected to:Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.7.0 - ProductionDECLARE*ERROR at line 1:ORA-20999: HD_DSQC refresh failed:-12008ORA-12008: error in materialized viewrefresh pathORA-02019: connection description for remote database not foundORA-02063: preceding line from HBCTAISORA-06512: at line 14Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.7.0 - Production经查，此现象与“AIX5L Based Systems (64-bit)系统上ORACLE 9.2.0.4.0的BUG：3464697”有雷同表现。处理办法：重新手工运行，刷新失败的物化视图对象至成功即可。附1：列表A_average_dirties_half_life _lm_statistics allow_partial_sn_results always_anti_join always_semi_join arch_io_slaves b_tree_bitmap_plans backup_disk_io_slaves cache_size_threshold cleanup_rollback_entries close_cached_open_cursors compatible_no_recovery complex_view_merging db_block_checkpoint_batch db_block_lru_extended_statistidb_block_lru_latches db_block_lru_statistics db_block_max_dirty_target db_file_simultaneous_writes delayed_logging_block_cleanoutdiscrete_transactions_enabled distributed_lock_timeout distributed_recovery_connectiodistributed_transactionsfast_full_scan_enabled freeze_DB_for_fast_instance_regc_defer_time gc_latches gc_lck_procs gc_releasable_locks gc_rollback_locks hash_multiblock_io_count instance_nodeset job_queue_interval job_queue_keep_connections large_pool_min_alloc lgwr_io_slaves lm_locks lm_procs lm_procs lm_ress lock_sga_areas log_block_checksum log_files log_simultaneous_copies log_small_entry_max_size ogms_home ops_admin_group ops_interconnects optimizer_percent_parallel optimizer_search_limit parallel_default_max_instancesparallel_min_message_pool parallel_server_idle_time parallel_transaction_resource_push_join_predicate row_cache_cursors sequence_cache_entries sequence_cache_hash_buckets shared_pool_reserved_min_allocsnapshot_refresh_interval snapshot_refresh_keep_connectisnapshot_refresh_processes sort_direct_writes sort_multiblock_read_count sort_read_fac sort_spacemap_size sort_write_buffer_size sort_write_buffers spin_count temporary_table_locks text_enable use_ismdblink_encrypt_loginlog_archive_bufferslog_archive_buffer_size附2：列表BDRS_START 替换为 DG_BROKER_STARTMTS_CIRCUITS 替换为 CIRCUITSMTS_DISPATCHERS 替换为 DISPATCHERSMTS_MAX_DISPATCHERS 替换为 MAX_DISPATCHERSMTS_MAX_SERVERS 替换为 MAX_SHARED_SERVERSMTS_SERVERS 替换为 SHARED_SERVERSMTS_SESSIONS 替换为 SHARED_SERVER_SESSIONSPARALLEL_SERVER 替换为 CLUSTER_DATABASEPARALLEL_SERVER_INSTANCES 替换为 CLUSTER_DATABASE_INSTANCE可修改