美文网首页
ORACLE12.2 升級之路 之 05(10.2.0.5本機升

ORACLE12.2 升級之路 之 05(10.2.0.5本機升

作者: 轻飘飘D | 来源:发表于2021-07-23 16:00 被阅读0次
    1. 版本查詢
    SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0  Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
    1. 执行utlu112i.sql进行升级前检查(老版本環境下)
    建議先停 正在運行的導數據的JOB
show parameters job_queue_processes;
alter system set job_queue_processes=0 scope=both;
show parameters job_queue_processes;

SQL> spool check_utlu112i.info
SQL>  @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlu112i.sql
SQL> spool off

    3.运行utlrp.sql 脚本，重新编译无效对象(老版本環境下)

    SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlrp.sql

查看失效对象
SQL> select count(*) from dba_invalid_objects;
    1. 执行PURGE DBA_RECYCLEBIN 清空回收站 (老版本環境下)
    SQL> PURGE DBA_RECYCLEBIN;

    5.在升级前收集字典统计信息，否则预升级工具( utlu102i.sql)会花费更长时间
    (老版本環境下)

    SQL> EXECUTE dbms_stats.gather_dictionary_stats;
    1. DB備份 (老版本環境下)

    7.檢查media recovery (老版本環境下)

    --確保沒有數據文件需要介質恢復（media recovery）或處於備份的狀態。
SQL> SELECT * FROM v$recover_file;
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
--以上語句不應該有任何返回行
    1. 臨時修改DB为非归档模式及停JOB (老版本環境下)
    SQL>
shutdown immediate;
startup mount;
select status from v$instance;
alter database noarchivelog;
alter database open;
    1. 創建11G的參數文件 (老版本環境下)
    SQL> show parameters db_recovery_file_dest;
db_recovery_file_dest            string        /u041/flash_recovery_area
db_recovery_file_dest_size       big integer   400G

SQL> Create pfile from spfile;

cp initMPACC.ora /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initMPACC.ora


    mkdir /u041/fast_recovery_area

mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump 
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/dpdump 
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump 
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/pfile

注意下sga(60%)和pga(20%)的设定是否符合目的机器的配置
*.sga_target=629145600
*.pga_aggregate_target=25165824

删除*.background_dump_dest和*.user_dump_dest

加上
*.diagnostic_dest='/u01/app/oracle'

修改
*.db_recovery_file_dest='/u041/fast_recovery_area'
*.compatible='11.2.0.4.0'

    10.開始升級

    [oracle@ACC_EMC_16 ~]$ echo $ORACLE_SID
MPACC
[oracle@ACC_EMC_16 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/10.2.0/db_1
#關閉DB
SQL>
shutdown immediate;

export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1/
export PATH=$ORACLE_HOME/bin:$PATH

[oracle@ACC_EMC_16 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.4/dbhome_1/

[oracle@ACC_EMC_16 ~]$ sqlplus / as sysdba
SQL> startup upgrade;

    11.upgrade模式下运行脚本：catupgrd.sql(約半小時)

    SQL> SPOOL /home/oracle/upgrade_112.log
#重建数据字典
SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/catupgrd.sql
    1. 執行utlu112s.sql腳本(这个脚本显示升级过程的一个摘要。不需要在upgrade 模式下。)
    sqlplus / as sysdba;

SQL> startup;
SQL> exec dbms_stats.gather_dictionary_stats;

#编译无效对象
SQL> select count(*) from dba_invalid_objects;
SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlrp.sql

查看失效对象
SQL> select count(*) from dba_invalid_objects;

#检查升级后状态
#查看版本
SQL>select * from v$version;

运行升级后检查脚本
SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlu112s.sql

    13.检查数据库状态

    col comp_name for a30
col version for a20
col status for a10
set linesize 200
select comp_name,version, status from dba_registry;

set line 150
col ACTION_TIME for a30
col ACTION for a15
col NAMESPACE for a9
col VERSION for a10
col BUNDLE_SERIES for a5
col COMMENTS for a30
select * from dba_registry_history;
    1. 生成參數文件
    SQL> startup mount pfile='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initMPACC.ora';

SQL> create spfile from pfile;
SQL> shutdown immediate;
SQL>  startup;

如遇以下錯誤則如下解決 然再次生成參數
ORA-00214: control file '/u01/app/oracle/oradata/MPACC/control01.ctl' 
version 2126 inconsistent with file
'/u01/app/oracle/oradata/MPACC/control03.ctl' version 2046

SQL> shutdown immediate;
rm /u01/app/oracle/oradata/MPACC/control03.ctl

cp /u01/app/oracle/oradata/MPACC/control01.ctl /u01/app/oracle/oradata/MPACC/control03.ctl
    1. 生成密码文件
    $ orapwd file='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwMPACC' password=123456 entries=10 force=y

#设置用户密码无限次尝试登录
alter profile default limit failed_login_attempts unlimited;
#设置用户密码不过期：
alter profile default limit password_life_time unlimited;
#查看配置的参数
select profile,RESOURCE_NAME,resource_type,limit from dba_profiles where 
RESOURCE_NAME in('FAILED_LOGIN_ATTEMPTS','PASSWORD_LIFE_TIME') and profile='DEFAULT';

    16.修改compatible参数

    SQL> ALTER SYSTEM SET COMPATIBLE = '11.2.0.4' SCOPE=SPFILE;
SQL> shutdown immediate
SQL> startup

SQL> show parameter comp
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offload_compaction              string      ADAPTIVE
compatible                           string      11.2.0.4
nls_comp                             string      BINARY
plsql_v2_compatibility               boolean     FALSE

#檢查相關業務處理正常後（MAC校驗）
show parameters job_queue_processes;
alter system set job_queue_processes=50 scope=both;
show parameters job_queue_processes;
    1. 升級TIMEZONE時區
    SQL> SELECT version FROM v$timezone_file; 
VERSION
----------
4

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

10.2.0.4、5 timezone是4 一个典型的输出是：
PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION        4
DST_SECONDARY_TT_VERSION      0
DST_UPGRADE_STATE              NONE

然后开始准备工作：
exec DBMS_DST.BEGIN_PREPARE(14);

接着检查准备状态：
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

一个典型的输出是：

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION        4
DST_SECONDARY_TT_VERSION      14
DST_UPGRADE_STATE              PREPARE

准备升级工作：
执行脚本：
SQL>
TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

SQL> 
BEGIN
    DBMS_DST.FIND_AFFECTED_TABLES
    (affected_tables => 'sys.dst$affected_tables',
    log_errors => TRUE,
    log_errors_table => 'sys.dst$error_table');
    END; 
/

SQL> SELECT * FROM sys.dst$affected_tables;
no rows selected

SQL>SELECT * FROM sys.dst$error_table;
no rows selected

SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';
no rows selected

SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';
no rows selected

SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');
no rows selected

执行脚本：

-- end prepare window, the rows above will stay in those tables.

EXEC DBMS_DST.END_PREPARE;

-- check if this is ended

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

一个典型的输出是：
PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION        4
DST_SECONDARY_TT_VERSION      0
DST_UPGRADE_STATE              NONE

2）真正开始升级Timezone  =================================================================================

conn / as sysdba

shutdown immediate;
startup upgrade;

set serveroutput on
purge dba_recyclebin;

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

alter session set "_with_subquery"=materialize;
EXEC DBMS_DST.BEGIN_UPGRADE(14);

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

一个典型的输出是：

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION        14
DST_SECONDARY_TT_VERSION      4
DST_UPGRADE_STATE              UPGRADE

下面这条语句应该没有返回结果：

SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

no rows selected

重启数据库：

shutdown immediate
startup

升级相关的table：执行脚本：

alter session set "_with_subquery"=materialize;

set serveroutput on
VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/


如果没有错误，则结束升级：

VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/

最后一次检查：
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

典型输出是：

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION        14
DST_SECONDARY_TT_VERSION      0
DST_UPGRADE_STATE              NONE

SELECT * FROM v$timezone_file;
FILENAME                VERSION
-------------------- ----------
timezlrg_14.dat              14

    相关文章

      网友评论

          本文标题：ORACLE12.2 升級之路 之 05(10.2.0.5本機升

          本文链接：https://www.haomeiwen.com/subject/zlpumltx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|ORACLE12.2 升級之路 之 05(10.2.0.5本機升|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！