- 最后登录
- 2017-5-4
- 在线时间
- 81 小时
- 威望
- 999
- 金钱
- 2391
- 注册时间
- 2013-9-11
- 阅读权限
- 150
- 帖子
- 1124
- 精华
- 5
- 积分
- 999
- UID
- 1220
|
1#
发表于 2013-10-7 20:28:30
|
查看: 5163 |
回复: 2
升级11.2.0.1 到11.2.0.3 并安装PSU4 补丁
准备阶段
将dbupgdiag.sql拷贝至某一目录下并执行,如tmp目录
1)查看“List of Invalid Database Objects”部分,如果存在系统失效对象,则运行utlrp.sql进行编译。多次执行直至失效对象的数目稳定为止。若有,则记录应用相关的失效对象,以便于升级后进行对比。
SQL> @?/rdbms/admin/utlrp.sql
| set linesize132;
set pagesize300;
col object_nameformat a30;
col ownerformat a15;
selectOWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where status<>'VALID'order by 1,2,3;
记录下来升级前的失效对象(用于升版之后的失效对象数量对比)
2)查看“Duplicate Objects Owned by SYS and SYSTEM”部分,确保SYS和SYSTEM表中不包含重复对象。查询结果中如果有以下重复对象可忽略。
column object_name format a30
select object_name, object_type
from dba_objects
where object_name||object_type in
(select object_name||object_type
from dba_objects
where owner = 'SYS')
and owner = 'SYSTEM'
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
AQ$_SCHEDULES TABLE
AQ$_SCHEDULES_PRIMARY INDEX
DBMS_REPCAT_AUTH PACKAGE
DBMS_REPCAT_AUTH PACKAGE BODY
| 若除上述重复对象以外仍存在其它重复对象,则需要drop掉system用户的对应对象,需要同应用人员确认后删除。若需要删除的对象数量较多,可以采用以下方法批量删除。
Step 1:
set pause off
set heading off
set pagesize 0
set feedback off
set verify off
spool dropsys.sql
select 'DROP ' || object_type || ' SYSTEM.' || object_name || ';'
from dba_objects
where object_name||object_type in
(select object_name||object_type
from dba_objects
where owner = 'SYS')
and owner = 'SYSTEM';
spool off
exit
Step 2:
从dropsys.sql中删除无需drop的对象,如上述的AQ$_SCHEDULES对象。
Step 3:
$ sqlplus system/passwd
SQL> @dropsys
Step 4:
部分对象可能由于存在依赖关系无法删除,则需要手动处理。
| After validating theinvalid objects, re-run dbupgdiag.sql in the database once again and make surethat everything is fine.
4. 检查数据库国家字符集查询语句如下:
select value from NLS_DATABASE_PARAMETERSwhere parameter = 'NLS_NCHAR_CHARACTERSET';
如果国家字符集既不是 UTF8 也不是 AL16UTF16 则请按下列官方步骤处理:
Note 276914.1 The National Character Set in Oracle 9iand 10g.
5. 记录数据库dblink信息查询数据库中 dblink的语句如下:
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK'||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'"USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;
6. 查询源版本数据库TIMESTAMP WITH TIMEZONE 设置查询语句如下:
SQL> select TZ_VERSION fromregistry$database;
7. 收集数据字典统计信息在升级之前执行数据字典的统计信息收集,可以减少升级时所需的停机时间。执行check_stale_stats.sql,并根据执行结果提供的SQL对特定Schema执行统计信息收集。
--------执行如下存储过程
EXECUTE dbms_stats.gather_dictionary_stats;
|
8. 检查database vault组件 如果系统有database vault组件,则需要升级之前先disable掉该组件。查询该组件是否安装的语句
SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';
PARAMETER VALUE
----------------------------- -----------------------
Oracle Database Vault FALSE
value为TRUE则表示已安装,为FALSE则表示没有安装。
如果需要disable掉databasevault. 请参阅 :
Note 453903.1 - Enabling and Disabling Oracle Database Vault in UNIX
9. 备份database control配置数据(如果有) 客户环境未配置OEM database control. 该部分略过。如需操作请参阅:
Note 870877.1 How To Save Oracle Enterprise Manager Database Control Data BeforeUpgrading The Single Instance Database To Other Release ?
10.检查数据库Network ACL的使用 检查客户数据库是否使用ACL:
SQL> SELECT acl FROM dba_network_acls
2 WHERE host = 'host_name' AND lower_port IS NULL AND upper_port IS NULL;
未选定行
经检查未使用Network ACL.
11.检查数据库没有逻辑坏块的存在创建脚本如下:
Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql
SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
FROM dba_clusters
WHERE owner='SYS'
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES';
spool off
创建完成之后将运行检测脚本:
This creates a script called analyze.sql.
Now execute the following steps:
$ sqlplus "/ as sysdba"
SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql
SQL> @analyze.sql
确认运行过程中未出现运行错误,则没有逻辑坏块的存在。
12.确认所有物化视图更新已成功完成,复制操作已停止SQL> SELECT DISTINCT(TRUNC(last_refresh))FROM dba_snapshot_refresh_times;
SQL> selects.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#,bitand(s.mflags, 8) from obj$ o, sum$ s
where o.obj# = s.obj# and o.type# = 42 ANDbitand(s.mflags, 8) = 8;
13.确认没有需要恢复的数据文件 $sqlplus / as sysdba
SQL> SELECT * FROM v$recover_file;
|
14.确认没有处于备份模式的文件 $sqlplus / as sysdba
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
|
15.确认没有未完成的分布式事务 SQL> select * from dba_2pc_pending;
如果有则执行如下操作:
SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;
|
16.确认data guard是否存在检查语句:
SELECTSUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1)
FROM v$parameter
WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%';
如果有data guard配置,则要确保所有主库日志均传递到了备库端。备库启动recover模式应用归档且nodelay方式。
如果有data guard配置,升级data guard过程请查阅Oracle Online Document upgrade部分。
http://docs.oracle.com/cd/E11882_01/server.112/e25608/upgrades.htm
17.停止批量作业和cron中的job同时确保数据库在升级过程中不能自动执行job任务:
停止所有的批处理作业、定时作业等,参考MOS文档404238.1/1335741.1/ 67695.1。
检查crontab中的定时任务已经注释。
检查JOB_QUEUE_PROCESSES、AQ_TM_PROCESSES参数,设置为0。
检查是否有正在运行的JOB:
show parameter job_queue_processes
show parameter aq_tm_processes
select * from v$lock where type='JQ';
select /*+ rule */ * from dba_jobs_running;
Disable属于dbms_scheduler的作业:
Set lin 160 pages 1000
col JOB_NAME format a20
col PROGRAM_NAME format a20
col JOB_CREATOR format a8
SELECT JOB_NAME, JOB_CREATOR,PROGRAM_NAME, ENABLED,RESTARTABLE,STATE, RUN_COUNT,MAX_RUNS,FAILURE_COUNT,STOP_ON_WINDOW_CLOSE
FROM DBA_SCHEDULER_JOBS;
select'EXEC DBMS_SCHEDULER.DISABLE( '||''''||JOB_NAME||''''||');'
FROM DBA_SCHEDULER_JOBS
where enabled = 'TRUE';
====================SYS==========================================
EXEC DBMS_SCHEDULER.DISABLE( 'PURGE_LOG');
EXEC DBMS_SCHEDULER.DISABLE( 'ORA$AUTOTASK_CLEAN');
EXEC DBMS_SCHEDULER.DISABLE( 'DRA_REEVALUATE_OPEN_FAILURES');
EXEC DBMS_SCHEDULER.DISABLE( 'MGMT_CONFIG_JOB');
EXEC DBMS_SCHEDULER.DISABLE( 'MGMT_STATS_CONFIG_JOB');
EXEC DBMS_SCHEDULER.DISABLE( 'BSLN_MAINTAIN_STATS_JOB');
EXEC DBMS_SCHEDULER.DISABLE( 'RSE$CLEAN_RECOVERABLE_SCRIPT');
EXEC DBMS_SCHEDULER.DISABLE( 'SM$CLEAN_AUTO_SPLIT_MERGE');
EXEC DBMS_SCHEDULER.DISABLE( 'RLM$EVTCLEANUP');
EXEC DBMS_SCHEDULER.DISABLE( 'RLM$SCHDNEGACTION');
18.确认SYS和 SYSTEM用户的默认表空间为system SQL> SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM');
|
19.确认aud$表位于sys schema和system表空间中 SQL> SELECT owner,tablespace_name
FROM dba_tables
WHERE table_name='AUD$';
| 20.确认部分init参数满足升级需求 Adjust the values of the initialization parameters to at least the minimum values indicated by the Pre-Upgrade Information Tool. For customers with JVM installed, java_pool_size and shared_pool_size must be set to at least 250MB prior to the upgrade otherwise JVM upgrade may fail with the following error:
ORA-07445: exception encountered: core dump [qmkmgetConfig()+52] [SIGSEGV] [ADDR:0x18] [PC:0x103FFEC34] [Address not mapped to object] []
| show parameterpool;
show parameter sga;
show parameter target;
21.记录数据文件、控制文件、日志文件等文件路径 (可略) SELECT name FROM v$controlfile;
SELECT file_name FROM dba_data_files;
SELECT group#, member FROM v$logfile;
|
22.清空回收站为了避免升级过程中的ORA-00600错误以及减少升级时间,建议清空回收站。
SQL> purge DBA_RECYCLEBIN;
SQL> SELECT * FROM DBA_RECYCLEBIN;
|
23.将应用表空间置为只读模式 $sqlplus / as sysdba
SQL> alter tablespace users read only;
|
24.停应用、监听和数据库 $lsnrctl stop
shutdown immediate;
|
25.备份system/sysaux/undo/redo/controlfile 对于system/sysaux/undo/redo/controlfile等文件进行冷备份
可以使用dd或其他备份软件备份上述raw device文件
| 26.调整环境变量 1) 确认ORACLE_BASE, ORACLE_HOME, PATH, NLS_10 and LIBRARY_PATH等环境变量已经指向11.2.0.3的相应目录。
ORACLE_BASE= /Oracle/app
ORACLE_HOME= /Oracle/app/product/11.2.0.3/dbhome
PATH= /Oracle/app/product/11.2.0.3/dbhome/bin.
SHLIB_PATH=/Oracle/app/product/11.2.0.3/dbhome/lib32:
/Oracle/app/product/11.2.0.3/dbhome/rdbms/lib32
LD_LIBRRARY_PATH=/Oracle/app/product/11.2.0.3/dbhome/lib:/usr/lib:/lib
CLASSPATH=/Oracle/app/product/11.2.0.3/dbhome/jlib:
/Oracle/app/product/11.2.0.3/dbhome/rdbms/jlib:
/Oracle/app/product/11.2.0.3/dbhome/JRE:
/Oracle/app/product/11.2.0.3/dbhome/network/jlib
2) /etc/oratab文件已经指向11.2.0.3软件的路径
退出oracle环境,再次进入使环境变量生效
|
27.拷贝原init/spfile 口令文件到11.2.0.3的$ORCLE_HOME/dbs Copy init.ora/spfile and password file (orapw<sid>.ora) from 11.2.0.1 $ORACLE_HOME/dbs to 11.2.0.3 $ORACLE_HOME/dbs
|
28.拷贝网络配置文件到11.2.0.3的$ORACLE_HOME/network/admin Copy network configuration files (listener.ora, sqlnet.ora, tnsnames.ora, etc) from 11.2.0.1 $ORACLE_HOME/network/admin ( or $TNS_ADMIN) location to 11.2.0.3 $ORACLE_HOME/network/admin ( or $TNS_ADMIN) location
注意需要修改listener.ora 和tnsnames.ora中的$ORACLE_HOME路径
|
29.如果配置了DB console/DB control建议做如下调整 如果未配置db console和dbcontrol可以忽略此步骤。
If DB Console / DB Control is configured and used then also copy the following two directories and their contents from 11.2.0.1 to 11.2.0.3. (if DB Console / DB Control is not configured these directories MAY not exist).
ORACLE_HOME/<hostname_dbname>
ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_<hostname_dbname>
Specify the actual name for <hostname_dbname>
|
30.确保设置了正确的COMPATIBLE参数 Make sure the COMPATIBLE initialization parameter is properly set for Oracle Database 11g Release 2 (11.2). The Pre-Upgrade Information Tool displays a warning in the Database section if COMPATIBLE is not properly set.
例如:
compatible =11.2.0.0.0
| Show parametercompatible
Alter system set compatible =11.2.0.0.0
31.执行升级脚本catupgrd.sql 50分钟 cd /Oracle/app/product/11.2.0.3/dbhome/rdbms/admin
sqlplus / as sysdba
SQL>STARTUP UPGRADE
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL>spool off
SQL> exit
| 32.重新启动数据库到normal模式 SQL>shutdown immediate
SQL>startup
|
33.运行utlu112s.sql检验升级结果 sqlplus / as sysdba
SQL>
SQL> select * from v$version;
SQL>spool checkupgrade.log
SQL> @?/rdbms/admin/utlu112s.sql
SQL>spool off
| 查看upgrade.log和checkupgrade.log,检查是否有错误出现。如果出现状态为INVALID等错误,则在改正错误后,重新运行catupgrd.sql
34.运行catuppst.sql脚本运行catuppst.sql脚本执行一些非upgrade模式下的升级操作,如升级baseline数据等。
SQL> @?/rdbms/admin/catuppst.sql
|
35.运行utlrp.sql脚本编译失效对象 15分钟运行utlrp.sql脚本编译失效对象,确认升级后没有新增的失效对象
@?/rdbms/admin/utlrp.sql
set linesize 132;
set pagesize 300;
col object_name format a30;
col owner format a15;
select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where status<>'VALID' order by 1,2,3;
|
36.启动数据库监听
37.检查升级后,验证各组件状态 SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
SQL> exit
|
38.启动Database Vault(如果配置) 如果未使用Database Vault,可忽略该步骤,具体方法请参考:
Note 453903.1 - Enabling and Disabling Oracle Database Vault in UNIX
|
39.更新RMAN catalog库(如果有) $ rman catalog username/password@alias
RMAN> UPGRADE CATALOG;
$ rman catalog username/password@alias
RMAN> UPGRADE CATALOG;
| 40.至应用表空间为读写状态 $sqlplus / as sysdba
SQL> alter tablespace users read write;
|
41.查看升级之后的数据库autoTask查询语句如下:
Set linesize 160 pagesize1000
col CLIENT_NAME format a40
col CONSUMER_GROUP formata30
col WINDOW_GROUP formata30
select CLIENT_NAME,STATUS,CONSUMER_GROUP,WINDOW_GROUP
from dba_autotask_client;
42.建议禁用掉数据库DBRM根据其他客户处升级经验,如果不是一台服务器多个数据库实例,配置每个实例的资源使用范围,则建议禁掉DBRM(resource manager)配置:
show parameter resource
set linesize 160 pagesize1000
col WINDOW_NAME format a30
col SCHEDULE_OWNER formata20
col SCHEDULE_NAME formata20
col RESOURCE_PLAN formata35
selectWINDOW_NAME,RESOURCE_PLAN,SCHEDULE_OWNER,SCHEDULE_NAME,SCHEDULE_TYPE
fromdba_scheduler_windows;
alter system setresource_manager_plan='';
executedbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
executedbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');
executedbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN','');
executedbms_scheduler.set_attribute('TUESDAY_WINDOW','RESOURCE_PLAN','');
executedbms_scheduler.set_attribute('WEDNESDAY_WINDOW','RESOURCE_PLAN','');
executedbms_scheduler.set_attribute('THURSDAY_WINDOW','RESOURCE_PLAN','');
executedbms_scheduler.set_attribute('FRIDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','');
executedbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN','');
43.建议升级数据库TimeZone版本到14升级到11.2.0.2之后,TimeZone版本默认变为14. 才可以执行的步骤。
参考文档:
n Note 1201253.1 Actions For DSTUpdates When Upgrading To Or Applying The 11.2.0.2 Patchset
n (recommended) update to DSTv14(standard DST version of 11.2.0.2) by following Note 977512.1 Updating theRDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST from step 3a)onwards, when going to DSTv14 there is no need to apply any DST patch to the11.2.0.2 home
NOTE : If youhave run DBMS_DST package to upgrade the timezone version to a newer one, e.g., version 11 during post-upgrade of OracleDatabase Release 11.2 , before you downgrade to your previous Oracle databaserelease, you must install version 11 timezone files into the directory
of $ORACLE_HOME/ORACORE/ZONEINFO on your old Oracle database release.
For patches of new timezone version files onOracle Database Release 11.1 or older, please refer to metalink Note412160.1
Updated DST transitions and new Time Zones inOracle Time Zone File patches.
3a) check current RDBMS DST version and"DST UPGRADE STATUS".
SELECTPROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROMDATABASE_PROPERTIES
WHEREPROPERTY_NAME LIKE 'DST_%'
ORDERBY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SELECTversion FROM v$timezone_file;
VERSION
----------
4
3b) Check UPFRONT using DBMS_DST if there isaffected data that cannot be resolved automatically.
-- these steps will NOT update any data yet.
-- this alter session might speed up DBMS_DST on some db's
-- see Bug 10209691
purge dba_recyclebin;
alter session set "_simple_view_merging"=TRUE;
alter session set "_with_subquery" = materialize;
exec DBMS_DST.BEGIN_PREPARE( 14 )
如果以上出现了错误,则参考文档977512.1进行处理。
-- check for prepare status
col property_name format a30
col value format a20
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
-- output should be
-- PROPERTY_NAME VALUE
-- ------------------------------ ------------------------------
-- DST_PRIMARY_TT_VERSION <the old DST version number>
-- DST_SECONDARY_TT_VERSION <the new DST version number>
-- DST_UPGRADE_STATE PREPARE
PROPERTY_NAME VALUE
------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 14
DST_UPGRADE_STATE PREPARE
-- truncate logging tables if they exist.
TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;
-- log affected data
set serveroutput on
BEGIN
DBMS_DST.FIND_AFFECTED_TABLES(
affected_tables =>'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table =>'sys.dst$error_table' );
END;
/
-- check what tables have affected data thatcannot be resolved automatically.
-- if this gives no rows then there is noproblem at all
SELECT * FROM sys.dst$affected_tables;
-- IF previous select gives rows then you cansee what kind of problem there are inthose rows
SELECT * FROM sys.dst$error_table;
说明:
-- error_on_overlap_time is error numberORA-1883
-- error_on_nonexisting_time is error numberORA-1878
-- for an explanation of the reported dataplease see
-- "Error Handling when Upgrading TimeZone File and Timestamp with Time Zone Data"
-- For the "error_on_overlap_time"and "error_on_nonexisting_time" you do not HAVE to
-- take action on this data to upgrade theDST version, but it is advised
-- to at least to check the results AFTER theupdate.
-- end prepare window, the rows above willstay in those tables.
EXEC DBMS_DST.END_PREPARE;
-- check if this is ended
col property_name format a30
col value format a20
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
4) Do the actual RDBMS DST version update ofthe database using DBMS_DST:
Assuming all non-existing time and overlaptimes in previous step are solved or logged, so using for
DBMS_DST.UPGRADE_DATABASEerror_on_overlap_time => FALSE and error_on_nonexisting_time => FALSE);
For RAC the database should be in singleinstance mode , as required by the "startup UPGRADE".
conn / as sysdba
shutdown immediate;
startup upgrade;
set serveroutput on
-- checkif previous prepare window is ended
col property_name format a30
col value format a20
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
-- output should be
-- PROPERTY_NAME VALUE
-- ---------------------------- ------------------------------
-- DST_PRIMARY_TT_VERSION <the old DST version number>
-- DST_SECONDARY_TT_VERSION 0
-- DST_UPGRADE_STATE NONE
PROPERTY_NAME VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
purge dba_recyclebin;
-- clean used tables
TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;
-- this alter session mightspeed up DBMS_DST on some db's
-- seeBug 10209691
altersession set "_with_subquery"=materialize;
alter session set"_simple_view_merging"=TRUE;
-- start upgrade window
EXEC DBMS_DST.BEGIN_UPGRADE( 14 );
-- the message -- "An upgrade window hasbeen successfully started." will be seen
-- check if this select
col property_name format a30
col value format a20
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
-- gives this output:
-- PROPERTY_NAME VALUE
-- --------------------------- ------------------------------
-- DST_PRIMARY_TT_VERSION <the new DST version number>
-- DST_SECONDARY_TT_VERSION <the old DST version number>
-- DST_UPGRADE_STATE UPGRADE
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE UPGRADE
-- restart the database
shutdown immediate
startup
- this alter session might speed up DBMS_DSTon some db's
-- see Bug 10209691
alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;
-- now upgrade the tables who need action
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;
/
-- ouput of this will be alist of tables like:
-- Table list:SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S
-- Number of failures: 0
-- ....
-- Table list:SYSMAN.MGMT_PROV_ASSIGNMENT
-- Number of failures: 0
-- Table list:SYSMAN.MGMT_CONFIG_ACTIVITIES
-- Number of failures: 0
-- Failures:0
Table list:"SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_S"
Number of failures: 0
Table list:"SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_L"
Number of failures: 0
Table list:"SYSMAN"."AQ$_MGMT_LOADER_QTABLE_S"
Number of failures: 0
Table list:"SYSMAN"."AQ$_MGMT_LOADER_QTABLE_L"
Number of failures: 0
Table list:"SYSMAN"."MGMT_PROV_SUITE_INST_MEMBERS"
Number of failures: 0
Table list:"SYSMAN"."MGMT_PROV_STAGING_DIRS"
Number of failures: 0
Table list:"SYSMAN"."MGMT_PROV_RPM_REP"
Number of failures: 0
Table list:"SYSMAN"."MGMT_PROV_OPERATION"
Number of failures: 0
Table list:"SYSMAN"."MGMT_PROV_NET_CONFIG"
Number of failures: 0
Table list:"SYSMAN"."MGMT_PROV_IP_RANGE"
Number of failures: 0
Table list:"SYSMAN"."MGMT_PROV_DEFAULT_IMAGE"
Number of failures: 0
Table list:"SYSMAN"."MGMT_PROV_CLUSTER_NODES"
Number of failures: 0
Table list:"SYSMAN"."MGMT_PROV_BOOTSERVER"
Number of failures: 0
Table list:"SYSMAN"."MGMT_PROV_ASSIGNMENT"
Number of failures: 0
Table list:"SYSMAN"."MGMT_CONFIG_ACTIVITIES"
Number of failures: 0
Failures:0
PL/SQL proceduresuccessfully complet
-- if there where nofailures then end the upgrade.
VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'||:fail);
END;
/
-- output that will beseen:
-- An upgrade window hasbeen successfully ended.
-- Failures:0
An upgrade window has beensuccessfully ended.
Failures:0
PL/SQL proceduresuccessfully completed.
-- last checks
col property_name format a30
col value format a20
SELECT PROPERTY_NAME,SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE'DST_%'
ORDER BY PROPERTY_NAME;
-- needed output:
-- PROPERTY_NAME VALUE
------------------------------ ------------------------------
-- DST_PRIMARY_TT_VERSION<the new DST version number>
-- DST_SECONDARY_TT_VERSION0
-- DST_UPGRADE_STATE NONE
PROPERTY_NAME VALUE
------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SELECT * FROMv$timezone_file;
-- needed output:
-- FILENAME VERSION
-- ----------------------------
-- timezlrg_<newversion>.dat <new version>
FILENAME VERSION
----------------------------------------------------------------------
timezlrg_14.dat 14
44.需要留意的11.2.0.3数据库参数设置对于11.2.0.2之后的数据库新特性,以下参数需要留意:
参数名称及其设置值
| 说明
| 目的
| deferred_segment_creation = false
|
| 禁用Deferred Segment Creation特性
| parallel_execution_message_size = 32768
|
| 禁用跨实例的并行服务器
增大并行服务器所使用的消息缓存
| _undo_autotune = false
undo_retention = 3600
|
| 禁用Auto Undo
| _optimizer_adaptive_cursor_sharing = false
_optimizer_extended_cursor_sharing = none
_optimizer_extended_cursor_sharing_rel = none
|
| 禁用Adaptive Cursor Sharing特性
| _cleanup_rollback_entries = 400
|
| 加快事务的回滚/恢复
| 注:对上述参数的修改,请务必针对本数据库业务做好测试之后再执行!
11.2.0.3 回退方案
1. 首选回退方案 --将所有应用数据表空间置为read-only模式
--shutdown immediate 数据库
--拷贝system/undo/sysaux/xdb/drsys/odm/tools表空间的数据文件和controlfiles/redo文件
到备份文件系统
--启动数据库开始upgrade
如果在升级过程中出现问题需要回退,则只需将上述冷备份的数据文件恢复到原来位置即可。
然后重新创建TEMP表空间。
该方案的特点是针对软件升级失败后回退速度快、简单。
注:上述备份方案只针对此次数据库升级过程中出现问题后需要回退使用,不能保证数据库升级期间出现存储故障等原因造成的数据文件损坏,数据丢失的情况。因此建议在条件允许的情况下使用RMAN或专业备份软件在数据库升级前执行一次完整备份。
|
2. 其它回退方案 -- Online Backup 方式(RMAN 备份/恢复)
-- FLASHBACK 方式
--该方式不允许修改COMPATIBLE参数,且只支持10.2 or later
--打开数据库的flashback功能
--创建GUARANTEED RESTORE POINT还原点 --升级数据库
--如果遇到问题需要回退,则可以使用flashback闪回到之前创建的RESTORE POINT。
--回退成功后删除还原点。
--命令参考如下
SQL> CREATE RESTORE POINT upgrd GUARANTEE FLASHBACK DATABASE;
SQL> FLASHBACK DATABASE TO RESTORE POINT upgrd; SQL> SELECT * FROM V$RESTORE_POINT; SQL> DROP RESTORE POINT upgrd; |
安装PSU补丁(Patch 14275605 - 11.2.0.3.4 PSU)
1. 备份ORACLE_HOME目录 $tar cvf /backup/11.2.0.3.tar /Oracle/app/* |
2. 下载并安装最新版本的OPatch工具 You must use the OPatch utility version 11.2.0.3.0 or later to apply this patch ./opatch -versione
| 3. 升级前环境检查 确认$PATH 能够包含make, ar, ld, and nm等编译命令
which make
例如:
export PATH=$PATH:/usr/ccs/bin
|
4. 升级前补丁冲突检查 $unzip p14275605_11203_<platform>.zip $cd 14275605 $opatch prereq CheckConflictAgainstOHWithDetail -ph ./
|
5. 停应用,监听和数据库
6. 开始补丁安装 20分钟 $unzip p14275605_11203_<platform>.zip
$cd 14275605
$ /Oracle/product/11.2.0.3/dbhome_1/OPatch/opatch apply
| 全选y
7. Patch Post-Installation cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql psu apply
SQL> QUIT
|
8. 检查Patch安装日志有无报错 Check the following log files in $ORACLE_HOME/cfgtoollogs/catbundle or $ORACLE_BASE/cfgtoollogs/catbundle for any errors:
catbundle_PSU_<database SID>_APPLY_<TIMESTAMP>.log
catbundle_PSU_<database SID>_GENERATE_<TIMESTAMP>.log
|
9. 更新RMAN catalog库(如果有)如果当前数据库未配置catalog库,可以跳过此步骤
$ rman catalog username/password@alias
RMAN> UPGRADE CATALOG;
$ rman catalog username/password@alias
RMAN> UPGRADE CATALOG;
|
10.开启监听
PSU补丁回退方案
1. 确认catbundle_PSU_<databaseSID>_ROLLBACK.sql已存在 1. Verify that an $ORACLE_HOME/rdbms/admin/catbundle_PSU_<database SID>_ROLLBACK.sql file exists for each database associated with this ORACLE_HOME. If this is not the case, you must execute the steps in Section 3.3.2, "Loading Modified SQL Files into the Database" against the database before deinstalling the PSU.
|
2. 停应用,监听和数据库 lsnrctl stop
shutdown immediate;
|
3. 使用opatch回退PSU opatch rollback -id 14275605
|
4. Patch Post-Deinstallation cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle_PSU_<database SID>_ROLLBACK.sql
SQL> QUIT
|
5. 检查Patch卸载日志 The log file is found in $ORACLE_BASE/cfgtoollogs/catbundle and is named catbundle_PSU_<database SID>_ROLLBACK_<TIMESTAMP>.log
|
|
|