Upgrade from 10.2.0.4 to 11.2.0.3.2 including Time Zone files
来源:互联网 发布:linux 删除匹配文件名 编辑:程序博客网 时间:2024/06/06 04:51
原文地址:http://www.dbspecialists.com/blog/uncategorized/upgrade-from-10204-to-112032-including-time-zone-files/
I recently upgraded a customer database from 10.2.0.4 to 11.2.0.3.2 and wanted to share the procedure that I followed. It is a pretty straightforward upgrade, similar to many others that I have done. The one thing that was new to me was upgrading the Time Zone files with the DBMS_DST package.
I used the following Metalink Notes to come up with this procedure. While most of the following steps would apply to any database, it is important that you review these documents and make sure that your particular situation is covered. For example, if you are running Database Vault, there are some required steps that are not mentioned in this document. Also, if your application uses UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages, then you will need to perform certain operations to allow continued access.
Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]
Actions For DST Updates When Upgrading To Or Applying The 11.2.0.3 Patchset [ID 1358166.1]
Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST [ID 977512.1]
Pre-Upgrade: These steps can occur prior to the actual database upgrade
Step 1) Install 11.2.0.3 (patch 10404530) into /opt/app/oracle/product/11.2.0/db_1
Step 2) Install latest version of OPatch (patch 6880880)
Step 3) Install 11.2.0.3.2 PSU (patch 13696216)
Download patch 13696216 to a staging location
cd <staging location>/13696216
opatch apply
Step 4) Run utlu112i.sql and resolve any issues.
Download this script from Note 884522.1 and run it as SYSDBA. It will list issues that need to be resolved before the upgrade can succeed. Create a modified init.ora based upon the output of this script. All of the *dump_dest parameters are replaced by the diagnostic_dest parameter. Increase tablespace size, SGA size as recommended. Copy it to the new $ORACLE_HOME/dbs
Note: if you don’t run this script prior to the upgrade, it will cause the upgrade to fail
Step 5) Run dbupgdiag.sql and resolve any issues
Step 6) Check for database dictionary corruption
This script came from Note 837570.1 and will detect any logical corruption in data dictionary objects.
Set verify offSet space 0Set line 120Set heading offSet feedback offSet pages 1000Spool analyze.sqlSELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'FROM dba_clustersWHERE owner='SYS'UNIONSELECT 'Analyze table "'||table_name||'" validate structure cascade;'FROM dba_tablesWHERE owner='SYS'AND partitioned='NO'AND (iot_type='IOT' OR iot_type is NULL)UNIONSELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'FROM dba_tablesWHERE owner='SYS'AND partitioned='YES';spool off@$ORACLE_HOME/rdbms/admin/utlvalid.sql@analyze.sql --You should get no output
Database Upgrade:
Step 1) Shutdown processes and jobs. Disable dbms_jobs and dba_scheduler jobs.
lsnrctl stopemctl stop dbconsolesqlplus / as sysdba@?/rdbms/admin/wkremov.sql --(Note: This script is only required if you-- have Ultra Search installed)spool /tmp/before_upgrade_values.txtshow parameters job_queue_processalter system set job_queue_processes=0;select job_name, enabled from dba_scheduler_jobs;select username, account_status from dba_users order by 1 asc;spool offexec dbms_scheduler.disable(<name>);purge dba_recyclebin;exec dbms_stats.gather_dictionary_stats;shutdown immediateexit;
Step 2) Take a cold backup
Seriously, take a full, cold backup of your database. It probably won’t be needed but you don’t want to bet your job on it, do you?
Step 3) change /etc/oratab and reset environment variables with oraenv
Step 4) Upgrade the Database
cd $ORACLE_HOME/rdbms/adminSQL> startup UPGRADESQL> set echo onSQL> SPOOL upgrade.logSQL> @catupgrd.sql $ sqlplus "/as sysdba"SQL> STARTUPSQL> @utlu112s.sqlSQL> @utlrp.sqlSQL> @catuppst.sql --(Note: This script runs "@catbundle psu apply" which-- will apply the PSU)SQL> @utlrp.sqlSQL> @dbupgdiag.sqlSQL> alter system set job_queue_processes=<value>SQL> exec dbms_scheduler.enable(<name>);– the next three scripts upgrade the Statspack schemaSQL> @spup102.sqlSQL> @spup1101.sqlSQL> @spup11201.sqlSQL> shutdownSQL> startupSQL> create spfile from pfile;SQL> exit
Step 5) Upgrade Time Zone files
conn / as sysdbashutdown immediate;startup upgrade;set serveroutput on -- check if previous prepare window is ended SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) valueFROM DATABASE_PROPERTIESWHERE 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 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;alter session set "_simple_view_merging"=TRUE; EXEC DBMS_DST.BEGIN_UPGRADE(14);- check if this select SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) valueFROM DATABASE_PROPERTIESWHERE 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 shutdown immediatestartup alter session set "_with_subquery"=materialize;alter session set "_simple_view_merging"=TRUE; set serveroutput onVAR numfail numberBEGINDBMS_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 a list of tables like: -- Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S-- Number of failures: 0 -- if there where no failures then end the upgrade. VAR fail numberBEGINDBMS_DST.END_UPGRADE(:fail);DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);END;/ -- output that will be seen:-- An upgrade window has been successfully ended.-- Failures:0 SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) valueFROM DATABASE_PROPERTIESWHERE 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_VERSION 0-- DST_UPGRADE_STATE NONE SELECT * FROM v$timezone_file; FILENAME VERSION——————-- ———-timezlrg_14.dat 14select TZ_VERSION from registry$database; update registry$database set TZ_VERSION = (select version FROM v$timezone_file);commit;
Step 6) Update Network config files
Change ORACLE_HOME in listener.ora and copy files to new $ORACLE_HOME/network/admin. Start the listener and check connectivity
Step 7) Modify cron jobs, profiles, etc that may have $ORACLE_HOME hard-coded.
Step 8 ) Move any Media Management Libraries to the new $ORACLE_HOME/lib
If you do RMAN backups directly to tape, you likely have a symbolic link called libobk.so in $ORACLE_HOME/lib pointing to Media Management software (Netbackup, Legato, etc). This needs to be recreated in the new $ORACLE_HOME
Step 9 ) Check for any new accounts that should be locked.
Depending on what options are installed, Oracle may create new users. Unless you are going to use that functionality, these accounts should be locked.
SQL> select username, account_status from dba_users order by 1 asc;
- Upgrade from 10.2.0.4 to 11.2.0.3.2 including Time Zone files
- Oracle 10g upgrade from 10.2.0.1 to 10.2.0.4
- Oracle 10g upgrade from 10.2.0.1 to 10.2.0.4
- QUESTION 66 To recover from the loss of the entire database, including all current control files, al
- How To Upgrade ASM from 10.2 to 11.1 (RAC)
- How To Upgrade ASM from 10.2 to 11.1 (RAC)
- Why Upgrade from Ehcache to ?
- upgrade from 12.04 to 14.04
- Upgrade Oracle RAC to 11.2.0.3 from 11.2.0.1 on Linux
- Upgrade Oracle 10g from 10.2.0.1 to 10.2.0.4 (Single Instance)
- Oracle DB Upgrade & Migrate from Single 11.1.0.7 to Oracle 11.2.0.3 RAC by rconfig
- Manual upgrade from 9i to 10g Release 2
- Time Zone
- Time Zone
- Upgrade Oracle 11.2.0.2 to 11.2.0.3
- Upgrade RAC 10.2.0.1 To 10.2.0.4
- Upgrade your skill, From 3.5 to 7.0
- Ubuntu Upgrade from 11.04 to 11.10
- 【个人机房重构】—— 将DataGridView中的数据导出到Excel表中㈣
- asp.net多条件ajax查询
- Java 7之集合类型 - Vector与Stack
- 正则表达式的使用(3)__常用的正则表达式(积累)2
- Java中HashSet详解
- Upgrade from 10.2.0.4 to 11.2.0.3.2 including Time Zone files
- Linux常用的60个命令
- html 实现文字左右滚动的方法
- Android查看当前显示哪个Activity
- 使用python3.0 编写C/S程序,初识wangpython的网络编程
- Android开发中抽象工厂模式
- Android API包名及包的功能的中文介绍
- Java中HashMap详解
- iOS中多线程原理与runloop介绍