ORACLE8i升級9207實施手冊

1、XX國稅綜合征管軟件查詢數(shù)據(jù)庫ORACLE 8174 UPGRADE 9207 實施手冊目錄1.確定升級方式32.備份舊庫XX查詢機未做備份33.原庫停應(yīng)用、停計劃任務(wù)34.安裝ORACLE 9201,打補丁至920745.將原數(shù)據(jù)庫HB_CTAIS從升級到46.應(yīng)用ORACLE9i新特性57.動數(shù)據(jù)庫JOB計劃任務(wù)88.啟動OS級與數(shù)據(jù)庫關(guān)聯(lián)的腳本計劃任務(wù)89.ORACLE817升9207實施完成8寫在文尾：9附1：列表A10附2：列表B111. 確定升級方式對原數(shù)據(jù)庫進行手動升級，直接將ORACLE 817數(shù)據(jù)庫升級到9207數(shù)據(jù)庫。2. 備份舊庫XX查詢機未做

2、備份對原數(shù)據(jù)庫實施冷全備，即在數(shù)據(jù)庫關(guān)閉狀態(tài)下，對數(shù)據(jù)庫實施一致性的全備份。3. 原庫停應(yīng)用、停計劃任務(wù)1) 檢查失效對象信息收集CREATE TABLE SCS_INVALIDASSELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS = INVALID;附件留存：select * from scs_invalid;2) 停數(shù)據(jù)庫JOB計劃任務(wù)-DROP TABLE SCS_JOB;CREATE TABLE SCS_JOBASSELECT A.JOB, A.SCHEMA_USER FROM DBA_JOBS A WHERE A.BROKEN = N;

3、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) 停操作系統(tǒng)級與數(shù)據(jù)庫關(guān)聯(lián)的腳本計劃任務(wù)crontab -e注： 停止與數(shù)據(jù)庫相關(guān)的腳本，客戶有時喜歡在這里訂制計劃任務(wù)，如statspack4) 檢查數(shù)據(jù)庫system與回滾表空間SELECT SUM(BYTES)/

4、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) 停數(shù)據(jù)庫、停監(jiān)聽sqlplus /as sysdbashutdown immediatelsnrctl stop4. 安裝ORACLE 9201,打補丁至92071) 更改OS用戶oracle的環(huán)境變量，如下：su - oraclecd cp .profile .profi

5、le.backvi .profileORACLE_BASE=/oracle/ora92; export ORACLE_BASEORACLE_HOME=/oracle/ora92; export ORACLE_HOME2) FTP上傳介質(zhì)文件介質(zhì)文件大小合計：2.3G,介質(zhì)文件解壓預(yù)計：1G,ORACLE9i軟件安裝需求：2G,請系統(tǒng)管理員協(xié)助，保證主機系統(tǒng)有足夠的文件空間使用。3) 安裝軟件ORACLE 9201su - oraclegunzip server_9201_hpunix64_disk1.cpio.gzcpio -idm server_9201_hpunix64_disk1.cpi

6、oexport DISPLAY=XXX.XXX.XXX.XXX:0.0 操作臺IP地址：操作要規(guī)范，必需輸入此命令installer注：1 安裝至/oracle/ora92目錄下,僅安裝軟件。2 安裝交互過程中，如果彈出“選擇JDK 1.3 路徑”，HP路徑為“/opt/java1.3”4) 打補丁至9207直接打補丁至9207，注意ORA_HOME路徑選擇為新安裝的9201路徑。5. 將原數(shù)據(jù)庫HB_CTAIS從升級到1) 復(fù)制原庫的密碼文件、TNS文件、監(jiān)聽文件至新軟件目錄下cp ./orapwHB_CTAIS $ORACLE_HOME/dbs/orapwHB_

7、CTAIScp ./network/admin/tnsnames.ora $ORACLE_HOME/network/admin/tnsnames.oracp ./network/admin/listener.ora $ORACLE_HOME/network/admin/listener.ora復(fù)制完成后，打開TNS文件與監(jiān)聽文件，更改其中相應(yīng)的路徑ORACLE9207相關(guān)路徑。2) 復(fù)制原庫初始化參數(shù)文件至新庫軟件地址進行更改cp ./initHBCTAIS.ora $ORACLE_HOME/dbs/initHBCTAIS.ora對原庫參數(shù)文件的副本進行修改設(shè)置參數(shù) _SYSTEM_TRIG_

8、ENABLED =FALSE 設(shè)置參數(shù) OPTIMIZER_MODE = CHOOSE設(shè)置參數(shù) MAX_ENABLED_ROLES 148確定USER_DUMP_DEST、BACKGROUND_DUMP_DEST 、CORE_DUMP_DEST地址確定shared_pool_size、large_pool_size大于150M注釋參數(shù)JOB_QUEUE_PROCESSES 注釋參數(shù)AQ_TM_PROCESSES 注釋參數(shù)SNAPSHOT_REFRESH_? 注釋參數(shù) RESOURCE_MANAGER_PLAN注釋所有附1：列表A中出現(xiàn)的參數(shù)替換所有附2：列表B中出現(xiàn)的參數(shù)3) 在ORACLE9

9、i環(huán)境下，啟動原庫HB_CTAIS至MIGRATE狀態(tài)下sqlplus /as sysdbastartup migrate;spool /oracle/log/upgrade.Log 打印日志：操作要規(guī)范，必需輸入此命令4) 應(yīng)用升級腳本$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

10、.sql 檢查各組件升級情況SELECT comp_name, version, status FROM dba_registry;spool off注：如用戶無其他組件，運行此句后，ORACLE會直接跳過不執(zhí)行。7) 重編譯數(shù)據(jù)庫失效對象shutdown immediatestartup restrict$ORACLE_HOME/rdbms/admin/utlrp.sqlshutdown immediate8) 啟動數(shù)據(jù)庫監(jiān)聽lsnrctlstartstatus6. 應(yīng)用ORACLE9i新特性1) 更改初始化參數(shù)文件，并轉(zhuǎn)換為spfile恢復(fù)JOB_QUEUE_PROCESSES去除參數(shù) _

11、SYSTEM_TRIG_ENABLED =FALSE 去除參數(shù) db_block_buffers=nn增加參數(shù) db_cache_size=NN修改參數(shù) compatible=.0startupcreate spfile from pfile;create pfile from spfile;shutdown immediatestartup2) 自動UNDO管理1 刪除原RBS、RBS1表空間。-ALTER ROLLBACK SEGMENT RBS0 OFFLINESELECT ALTER ROLLBACK SEGMENT | A.SEGMENT_NAME | OFFLINE;

12、 FROM DBA_ROLLBACK_SEGS A WHERE A.TABLESPACE_NAME LIKE RBS%DROP TABLESPACE RBS INCLUDING CONTENTS;DROP TABLESPACE RBS1 INCLUDING CONTENTS;2 新建一個UNDO表空間。create undo tablespace undotbs datafile /dev/chaxun_datavg/rlvchaxun08g05 size 8000m, /dev/chaxun_datavg/rlvchaxun08g06 size 8000m, /dev/chaxun_data

13、vg/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;3 修改初始化參數(shù)shutdown

14、 immediaterm $ORACLE_HOME/dbs/spfile*vi $ORACLE_HOME/dbs/initHB_CTAIS.ora注釋參數(shù)：rollback_segments加入?yún)?shù)：undo_management=auto加入?yún)?shù)：undo_tablespace=undotbs4 重啟數(shù)據(jù)庫startupcreate spfile from pfile;3) 將表空間由“字典管理”更改為“本地管理”SELECT A.TABLESPACE_NAME, A.EXTENT_MANAGEMENT FROM DBA_TABLESPACES A;1 將非SYSTEM、UNDO、TEMP表

15、空間轉(zhuǎn)換為“本地管理”模式-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;2 將臨時表空間“TS_TMP”轉(zhuǎn)換為“本地管理”模式-新建臨時表空間“TEMP”，并設(shè)為系統(tǒng)默認(rèn)臨時表空間CREATE TEMPORARY TABLESPACE

16、 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”為“本地管理”模式，并設(shè)為系統(tǒng)默認(rèn)臨時表空間CREATE TEMPORARY TABLESPACE TS_TMP TEMPFILE /dev/chaxun_datavg/rlvchaxun08g0

17、1 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;3

18、將系統(tǒng)表空間“SYSTEM”轉(zhuǎn)換為“本地管理”模式-啟動數(shù)據(jù)庫至RESTRICT狀態(tài)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;-將表空間全部轉(zhuǎn)換為本地管理EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOC

19、AL(SYSTEM);4) 重啟數(shù)據(jù)庫shutdown immediatestartup7. 動數(shù)據(jù)庫JOB計劃任務(wù)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級與數(shù)據(jù)庫關(guān)聯(lián)的腳本計劃任務(wù)9. ORACLE817升9207實施完成寫在文尾：CTAIS系統(tǒng)查詢庫，是通過使用物化視圖實現(xiàn)數(shù)據(jù)同步的。查詢庫在成功升到9207后，需跟蹤

20、一下物化視圖刷新情況,確定是否正常刷新數(shù)據(jù)。XX國稅出現(xiàn)一情況，很多物化視圖第一次無法刷新，但第二次再刷，即可順利完成。相關(guān)日志摘錄如下Connected to:Oracle9i Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release .0 - ProductionDECLARE*ERROR at line 1:ORA-20999: HD_DSQC refresh failed:-

21、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 .0 - 64bit ProductionWith the Partitioning, OLAP and Oracle Dat

22、a Mining optionsJServer Release .0 - Production經(jīng)查，此現(xiàn)象與“AIX5L Based Systems (64-bit)系統(tǒng)上ORACLE .0的BUG：3464697”有雷同表現(xiàn)。處理辦法：重新手工運行，刷新失敗的物化視圖對象至成功即可。附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 b

23、ackup_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_

24、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_nodese

25、t 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_p