- 版本查詢
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
- 执行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;
- 执行PURGE DBA_RECYCLEBIN 清空回收站 (老版本環境下)
SQL> PURGE DBA_RECYCLEBIN;
5.在升级前收集字典统计信息，否则预升级工具( utlu102i.sql)会花费更长时间
(老版本環境下)
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
- DB備份 (老版本環境下)
7.檢查media recovery (老版本環境下)
--確保沒有數據文件需要介質恢復（media recovery）或處於備份的狀態。
SQL> SELECT * FROM v$recover_file;
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
--以上語句不應該有任何返回行
- 臨時修改DB为非归档模式及停JOB (老版本環境下)
SQL>
shutdown immediate;
startup mount;
select status from v$instance;
alter database noarchivelog;
alter database open;
- 創建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
- 執行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;
- 生成參數文件
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
- 生成密码文件
$ 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;
- 升級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
