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

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

2. 执行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;

4. 执行PURGE DBA_RECYCLEBIN 清空回收站 (老版本環境下)

SQL> PURGE DBA_RECYCLEBIN;

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

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

6. DB備份 (老版本環境下)

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

--確保沒有數據文件需要介質恢復（media recovery）或處於備份的狀態。
SQL> SELECT * FROM v$recover_file;
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
--以上語句不應該有任何返回行

8. 臨時修改DB为非归档模式及停JOB (老版本環境下)

SQL>
shutdown immediate;
startup mount;
select status from v$instance;
alter database noarchivelog;
alter database open;

9. 創建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

12. 執行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;

14. 生成參數文件

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

15. 生成密码文件

$ 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;

17. 升級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