环境：
主机：SUN E6500
操作系统：solaris10u7 sparc 64bit
数据库：oracle9i（9206）
升级步骤：
1. Oracle 升级9206—>9208 ： 1) 安装oracle9208 patch 用静默方式安装： 修改response静默安装文件，主要修改以下三条： UNIX_GROUP_NAME="dba" ORACLE_HOME="/orahome/oracle/product/9.2.0" ORACLE_HOME_NAME=" ora9ibims " ORACLE_HOME_NAME可以从../oraInventory/ContentsXML/inventory.xml文件中 HOME NAME获取。 bash-3.00$ grep ORACLE_HOME bims.rsp //bims.rsp为修改后的response文件
#Name : ORACLE_HOME
#Example: ORACLE_HOME = "C:\OHOME1"
ORACLE_HOME="/orahome/oracle/product/9.2.0"
#Name : ORACLE_HOME_NAME
#Example: ORACLE_HOME_NAME = "OHOME1"
ORACLE_HOME_NAME="ora9ibims"
bash-3.00$ grep UNIX_GROUP_NAME bims.rsp
#Name : UNIX_GROUP_NAME
#Example: UNIX_GROUP_NAME = "install"
UNIX_GROUP_NAME="dba" #------------------------------------------------------------------------------- #Name : FROM_LOCATION #Datatype : String #Description: Complete path to the products.xml. #Example: FROM_LOCATION = "../stage/products.xml" #------------------------------------------------------------------------------- FROM_LOCATION="/orahome/software/Disk1/stage/products.xml" 执行静默安装： bash-3.00$ cd /orahome/software/Disk1 bash-3.00$ ./runInstaller -silent -responseFile /orahome/software/Disk1/response/bims.rsp 安装完成后用root用户执行roo.sh文件完成安装： root@ts-db1 # /orahome/oracle/product/9.2.0/root.sh Running Oracle9 root.sh script... The following environment variables are set as: ORACLE_OWNER= oracle ORACLE_HOME= /orahome/oracle/product/9.2.0 Enter the full pathname of the local bin directory: [/usr/local/bin]: Creating /usr/local/bin directory... Copying dbhome to /usr/local/bin ... Copying oraenv to /usr/local/bin ... Copying coraenv to /usr/local/bin ... Creating /var/opt/oracle/oratab file... Adding entry to /var/opt/oracle/oratab file... Entries will be added to the /var/opt/oracle/oratab file as needed by Database Configuration Assistant when a database is created Finished running generic part of root.sh script. Now product-specific root actions will be performed. rm: /orahome/oracle/product/9.2.0/rdbms/filemap is a directory root@ts-db1 #
2) 更新数据字典,编译失效对象 $ sqlplus /nolog
SQL> CONNECT /AS SYSDBA
SQL> STARTUP SQL> SHOW PARAMETER SHARED_POOL_SIZE SQL> SHOW PARAMETER JAVA_POOL_SIZE SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;
SQL> shutdown immediate
SQL> STARTUP MIGRATE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF
SQL> SPOOL OFF
SQL> shutdown immediate
SQL> STARTUP
SQL> select comp_id,status,version from dba_registry;
检查是否有无效的对象。
Sql>select object_name,status from dba_objects where object_Name in ('SYS','SYSTEM') and status = 'INVALID';
如果有invalid对象则运行以下的脚本。
SQL> @?/rdbms/admin/utlrp.sql SQL> select comp_id,status,version from dba_registry; COMP_ID STATUS VERSION ------------------------------ ----------- ------------------ CATALOG VALID 9.2.0.8.0 CATPROC VALID 9.2.0.8.0 OWM VALID 9.2.0.1.0 JAVAVM VALID 9.2.0.8.0 XML VALID 9.2.0.10.0 CATJAVA VALID 9.2.0.8.0 ORDIM VALID 9.2.0.8.0 SDO VALID 9.2.0.8.0 CONTEXT VALID 9.2.0.8.0 XDB VALID 9.2.0.8.0 WK VALID 9.2.0.8.0 COMP_ID STATUS VERSION ------------------------------ ----------- ------------------ ODM VALID 9.2.0.8.0 APS UPGRADED 9.2.0.8.0 XOQ UPGRADED 9.2.0.8.0 AMD VALID 9.2.0.8.0 已选择15行。 3) 检查完成
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='64M' SCOPE=spfile; （还原设置）
SQL> select comp_id,status,version from dba_registry;
查一下表，查一下alert日志
启动监听：lsnrctl start
检查监听：lsnrctl status
2. Oracle升级9208à11.2.0.4 安装oracle11g到新的目录： ORACLE_BASE=/orahome/ora11g ORACLE_HOME= /orahome/ora11g /product/11.2.0 静默安装oracle11g： 1) 修改应答文件 bash-3.00$ more ts-db-install.rsp | grep -v "#" oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0 oracle.install.option=INSTALL_DB_SWONLY ORACLE_HOSTNAME= UNIX_GROUP_NAME=dba INVENTORY_LOCATION=/orahome/ora11g/oraInventory SELECTED_LANGUAGES=en,zh_CN,zh_TW ORACLE_HOME=/orahome/ora11g /product/11.2.0 ORACLE_BASE=/orahome/ora11g oracle.install.db.InstallEdition=EE oracle.install.db.EEOptionsSelection=false oracle.install.db.optionalComponents=oracle.rdbms.partitioning:11.2.0.4.0,oracle.oraolap:11.2.0.4.0,oracle.rdbms.dm:11.2.0.4.0,oracle.rdbms.dv:11.2.0.4.0,oracle.rdbms.lbac:11.2.0.4.0,oracle.rdbms.rat:11.2.0.4.0 oracle.install.db.DBA_GROUP=dba oracle.install.db.OPER_GROUP= oracle.install.db.CLUSTER_NODES= oracle.install.db.isRACOneInstall= oracle.install.db.racOneServiceName= oracle.install.db.config.starterdb.type= oracle.install.db.config.starterdb.globalDBName= oracle.install.db.config.starterdb.SID= oracle.install.db.config.starterdb.characterSet=ZHS16GBK oracle.install.db.config.starterdb.memoryOption=true oracle.install.db.config.starterdb.memoryLimit= oracle.install.db.config.starterdb.installExampleSchemas=false oracle.install.db.config.starterdb.enableSecuritySettings=true oracle.install.db.config.starterdb.password.ALL= oracle.install.db.config.starterdb.password.SYS= oracle.install.db.config.starterdb.password.SYSTEM= oracle.install.db.config.starterdb.password.SYSMAN= oracle.install.db.config.starterdb.password.DBSNMP= oracle.install.db.config.starterdb.control=DB_CONTROL oracle.install.db.config.starterdb.gridcontrol.gridControlServiceURL= oracle.install.db.config.starterdb.automatedBackup.enable=false oracle.install.db.config.starterdb.automatedBackup.osuid= oracle.install.db.config.starterdb.automatedBackup.ospwd= oracle.install.db.config.starterdb.storageType= oracle.install.db.config.starterdb.fileSystemStorage.dataLocation= oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation= oracle.install.db.config.asm.diskGroup= oracle.install.db.config.asm.ASMSNMPPassword= MYORACLESUPPORT_USERNAME= MYORACLESUPPORT_PASSWORD= SECURITY_UPDATES_VIA_MYORACLESUPPORT= DECLINE_SECURITY_UPDATES=true PROXY_HOST= PROXY_PORT= PROXY_USER= PROXY_PWD= PROXY_REALM= COLLECTOR_SUPPORTHUB_URL= oracle.installer.autoupdates.option= oracle.installer.autoupdates.downloadUpdatesLoc= AUTOUPDATES_MYORACLESUPPORT_USERNAME= AUTOUPDATES_MYORACLESUPPORT_PASSWORD= 2) 检查包： root@ts-db1 # pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibC SUNWlibm SUNWlibms SUNWsprot SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs system SUNWarc Lint Libraries (usr) system SUNWbtool CCS tools bundled with SunOS system SUNWhea SunOS Header Files system SUNWi15cs X11 ISO8859-15 Codeset Support system SUNWi1cs X11 ISO8859-1 Codeset Support system SUNWi1of ISO-8859-1 (Latin-1) Optional Fonts system SUNWlibC Sun Workshop Compilers Bundled libC system SUNWlibm Math & Microtasking Library Headers & Lint Files (Usr) system SUNWlibms Math & Microtasking Libraries (Usr) system SUNWsprot Solaris Bundled tools system SUNWtoo Programming Tools 3) 开始静默安装： ./runInstaller -silent -force -noconfig -responseFile /tmp/database/response/ts-db-install.rsp Starting Oracle Universal Installer... Checking Temp space: must be greater than 180 MB. Actual 17597 MB Passed Checking swap space: must be greater than 150 MB. Actual 17903 MB Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2014-07-29_06-28-28PM. Please wait ...bash-3.00$ [WARNING] [INS-32055] The Central Inventory is located in the Oracle base. CAUSE: The Central Inventory is located in the Oracle base. ACTION: Oracle recommends placing this Central Inventory in a location outside the Oracle base directory. [WARNING] [INS-13014] Target environment do not meet some optional requirements. CAUSE: Some of the optional prerequisites are not met. See logs for details. /tmp/OraInstall2014-07-29_06-28-28PM/installActions2014-07-29_06-28-28PM.log ACTION: Identify the list of failed prerequisite checks from the log: /tmp/OraInstall2014-07-29_06-28-28PM/installActions2014-07-29_06-28-28PM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually. You can find the log of this install session at: /orahome/ora11g/oraInventory/logs/installActions2014-07-29_06-28-28PM.log bash-3.00$ bash-3.00$ The installation of Oracle Database 11g was successful. Please check '/orahome/ora11g/oraInventory/logs/silentInstall2014-07-29_06-28-28PM.log' for more details. As a root user, execute the following script(s): 1. /orahome/ora11g/oraInventory/orainstRoot.sh 2. /orahome/ora11g/product/11.2.0/root.sh Successfully Setup Software. 切换到root用户执行以上脚本后，安装oracle11g完成。 4) 创建要升级库的pfile文件： bash-3.00$ sqlplus /nolog SQL*Plus: Release 9.2.0.8.0 - Production on 星期二 7月 29 19:27:38 2014 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL> conn /as sysdba 已连接。 SQL> create pfile from spfile; 文件已创建。 5) 将参数文件和口令文件拷贝到ora11g相应目录： bash-3.00$ cp -rp initbims.ora /orahome01 bash-3.00$ cp -rp orapwbims /orahome01 bash-3.00$ cd /orahome/ora11g/product/11.2.0/dbs/ bash-3.00$ ls init.ora bash-3.00$ cp -rp /orahome01/initbims.ora . bash-3.00$ cp -rp /orahome01/orapwbims . bash-3.00$ ls init.ora initbims.ora orapwbims bash-3.00$ ls -l total 14 -rw-r--r-- 1 oracle dba 2851 May 15 2009 init.ora -rw-r--r-- 1 oracle dba 1520 Jul 29 19:27 initbims. -rwSr----- 1 oracle dba 1536 Jan 20 2010 orapwbims SQL> @utlu112i.sql Oracle Database 11.2 Pre-Upgrade Information Tool 07-29-2014 19:14:34 Script Version: 11.2.0.4.0 Build: 001 . ********************************************************************** Database: ********************************************************************** --> name: BIMS --> version: 9.2.0.8.0 --> compatible: 9.2.0.0.0 --> blocksize: 8192 --> timezone file: V1 . ********************************************************************** Logfiles: [make adjustments in the current environment] ********************************************************************** --> The existing log files are adequate. No changes are required. . ********************************************************************** Tablespaces: [make adjustments in the current environment] ********************************************************************** --> SYSTEM tablespace is adequate for the upgrade. .... minimum required size: 885 MB --> UNDOTBS1 tablespace is adequate for the upgrade. .... minimum required size: 400 MB --> TEMP tablespace is adequate for the upgrade. .... minimum required size: 60 MB --> CWMLITE tablespace is adequate for the upgrade. .... minimum required size: 18 MB --> DRSYS tablespace is adequate for the upgrade. .... minimum required size: 10 MB --> EXAMPLE tablespace is adequate for the upgrade. .... minimum required size: 24 MB --> ODM tablespace is adequate for the upgrade. .... minimum required size: 6 MB --> XDB tablespace is adequate for the upgrade. .... minimum required size: 146 MB . ********************************************************************** Update Parameters: [Update Oracle Database 11.2 init.ora or spfile] Note: Pre-upgrade tool was run on a lower version 64-bit database. ********************************************************************** --> If Target Oracle is 32-Bit, refer here for Update Parameters: WARNING: --> "compatible" must be set to at least 10.1.0 . --> If Target Oracle is 64-Bit, refer here for Update Parameters: WARNING: --> "compatible" must be set to at least 10.1.0 . ********************************************************************** Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile] ********************************************************************** -- No renamed parameters found. No changes are required. . ********************************************************************** Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile] ********************************************************************** --> hash_join_enabled 10.1 OBSOLETE --> log_archive_start 10.1 DEPRECATED --> remote_archive_enable 10.2 DEPRECATED --> standby_archive_dest 11.1 DEPRECATED --> background_dump_dest 11.1 DEPRECATED replaced by "diagnostic_de --> user_dump_dest 11.1 DEPRECATED replaced by "diagnostic_de --> remote_archive_enable 11.1 OBSOLETE . ********************************************************************** Components: [The following database components will be upgraded or installed] ********************************************************************** --> Oracle Catalog Views [upgrade] VALID --> Oracle Packages and Types [upgrade] VALID --> JServer JAVA Virtual Machine [upgrade] VALID --> Oracle XDK for Java [upgrade] VALID --> Oracle Workspace Manager [upgrade] VALID --> OLAP Analytic Workspace [upgrade] UPGRADED --> OLAP Catalog [upgrade] VALID --> Oracle Text [upgrade] VALID --> Oracle XML Database [upgrade] VALID --> Oracle Java Packages [upgrade] VALID --> Oracle interMedia [upgrade] VALID --> Spatial [upgrade] VALID --> Data Mining [upgrade] VALID --> Oracle Ultra Search [upgrade] VALID --> Oracle OLAP API [upgrade] UPGRADED . ********************************************************************** Miscellaneous Warnings ********************************************************************** WARNING: --> Passwords exist in some database links. .... Passwords will be encrypted during the upgrade. .... Downgrade of database links with passwords is not supported. WARNING: --> Deprecated CONNECT role granted to some user/roles. .... CONNECT role after upgrade has only CREATE SESSION privilege. WARNING: --> Database is using a timezone file older than version 14. .... After the release migration, it is recommended that DBMS_DST package .... be used to upgrade the 9.2.0.8.0 database timezone version .... to the latest version which comes with the new release. WARNING: --> Database contains INVALID objects prior to upgrade. .... The list of invalid SYS/SYSTEM objects was written to .... registry$sys_inv_objs. .... The list of non-SYS/SYSTEM objects was written to .... registry$nonsys_inv_objs. .... Use utluiobj.sql after the upgrade to identify any new invalid .... objects due to the upgrade. .... USER BILL has 6 INVALID objects. .... USER DEVELOPER has 2 INVALID objects. .... USER OEM_BIMS has 1 INVALID objects. .... USER PERFSTAT has 1 INVALID objects. .... USER TRAIN has 37 INVALID objects. WARNING: --> log_archive_format must be updated. .... As of 10.1, log_archive_format requires a %r format qualifier .... be present in its format string. Your current setting is: .... log_archive_format='%t_%s.arc'. .... Archive Logging is currently ON, and failure to add the %r to the .... format string will prevent the upgraded database from starting up. WARNING: --> JOB_QUEUE_PROCESS value must be updated .... Your current setting of "10" is too low. .... Starting with Oracle Database 11g Release 2 (11.2), setting .... JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and .... DBMS_JOB jobs to not run. Previously, setting JOB_QUEUE_PROCESSES .... to 0 caused DBMS_JOB jobs to not run, but DBMS_SCHEDULER jobs were .... unaffected and would still run. This parameter must be updated to .... a value greater than 24 (default value is 1000) prior to upgrade. .... Not doing so will affect the running of utlrp.sql after the upgrade . ********************************************************************** Recommendations ********************************************************************** Oracle recommends gathering dictionary statistics prior to upgrading the database. To gather dictionary statistics execute the following commands while connected as SYSDBA: EXECUTE dbms_stats.gather_schema_stats('DBSNMP',options=>'GATHER' ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt=>'FOR ALL COLUMNS SIZE AUTO' ,cascade=>TRUE); EXECUTE dbms_stats.gather_schema_stats('OUTLN',options=>'GATHER' ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt=>'FOR ALL COLUMNS SIZE AUTO' ,cascade=>TRUE); EXECUTE dbms_stats.gather_schema_stats('ORDPLUGINS',options=>'GATHER' ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt=>'FOR ALL COLUMNS SIZE AUTO' ,cascade=>TRUE); EXECUTE dbms_stats.gather_schema_stats('ORDSYS',options=>'GATHER' ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt=>'FOR ALL COLUMNS SIZE AUTO' ,cascade=>TRUE); EXECUTE dbms_stats.gather_schema_stats('WKSYS',options=>'GATHER' ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt=>'FOR ALL COLUMNS SIZE AUTO' ,cascade=>TRUE); EXECUTE dbms_stats.gather_schema_stats('XDB',options=>'GATHER' ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt=>'FOR ALL COLUMNS SIZE AUTO' ,cascade=>TRUE); EXECUTE dbms_stats.gather_schema_stats('CTXSYS',options=>'GATHER' ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt=>'FOR ALL COLUMNS SIZE AUTO' ,cascade=>TRUE); EXECUTE dbms_stats.gather_schema_stats('MDSYS',options=>'GATHER' ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt=>'FOR ALL COLUMNS SIZE AUTO' ,cascade=>TRUE); EXECUTE dbms_stats.gather_schema_stats('WMSYS',options=>'GATHER' ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt=>'FOR ALL COLUMNS SIZE AUTO' ,cascade=>TRUE); EXECUTE dbms_stats.gather_schema_stats('SYSTEM',options=>'GATHER' ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt=>'FOR ALL COLUMNS SIZE AUTO' ,cascade=>TRUE); EXECUTE dbms_stats.gather_schema_stats('SYS',options=>'GATHER' ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt=>'FOR ALL COLUMNS SIZE AUTO' ,cascade=>TRUE); ********************************************************************** Oracle recommends removing all hidden parameters prior to upgrading. To view existing hidden parameters execute the following command while connected AS SYSDBA: SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' Changes will need to be made in the init.ora or spfile. ********************************************************************** Oracle recommends reviewing any defined events prior to upgrading. To view existing non-default events execute the following commands while connected AS SYSDBA: Events: SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2 WHERE UPPER(name) ='EVENT' AND isdefault='FALSE' Trace Events: SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2 WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE' Changes will need to be made in the init.ora or spfile. ********************************************************************** ********************************************************************** SYSAUX Tablespace: [Create tablespace in the Oracle Database 11.2 environment] ********************************************************************** --> New "SYSAUX" tablespace .... minimum required size for database upgrade: 500 MB . SQL> spool off SQL> exit 根据以上脚本输出做相应修改，注释掉11g的过期参数： bash-3.00$ more initbims.ora ####/*._log_archive_callout='LOCAL_FIRST=TRUE' *.aq_tm_processes=1 *.archive_lag_target=1800 ####*.background_dump_dest='/orahome/oracle/admin/bims/bdump' *.compatible='11.2.0.4.0' *.control_files='/orahome/oracle/product/9.2.0/oradata/bims/control01.ctl','/orahome/oracle/product/9.2.0/oradata/bims/control02.ctl ','/orahome/oracle/product/9.2.0/oradata/bims/control03.ctl' *.core_dump_dest='/orahome/oracle/admin/bims/cdump' *.db_block_size=8192 *.db_cache_size=3221225472 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_files=350 *.db_name='bims' *.dispatchers='(PROTOCOL=TCP) (SERVICE=bimsXDB)' ####*.event='10262 trace name context forever, level 90000' *.fal_client='' *.fal_server='' *.fast_start_mttr_target=300 ####*.hash_join_enabled=TRUE *.instance_name='bims' *.java_pool_size=157286400 *.job_queue_processes=10 *.large_pool_size=134217728 *.log_archive_dest_1='LOCATION=/archivelog MANDATORY' *.log_archive_dest_state_1='ENABLE' *.log_archive_format='%r_%t_%s.arc' ####/*.log_archive_start=true *.open_cursors=300 *.open_links=100 *.pga_aggregate_target=5242880000 *.processes=300 *.query_rewrite_enabled='FALSE' ####*.remote_archive_enable='true' *.remote_login_passwordfile='EXCLUSIVE' *.session_cached_cursors=100 *.shared_pool_size=805306368 *.sort_area_size=524288 ####*.standby_archive_dest='/archivelog' *.standby_file_management='AUTO' *.star_transformation_enabled='FALSE' *.timed_statistics=TRUE *.undo_management='AUTO' *.undo_retention=56700 *.undo_tablespace='UNDOTBS1' ####*.user_dump_dest='/orahome/oracle/admin/bims/udump' 6) 升级前检查 a) 在升级之前，确保所有的组件和对象都是valid：
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name; --针对组件
select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status='INVALID' order by owner,object_type; --针对对象
如果有invalid的对象，运行utlrp.sql重新编译对象。 b) 确保sys和system下没有重复的对象：
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'; 上面这条语句只能返回以下4条记录：
OBJECT_NAME OBJECT_TYPE
---------------------------------------- ---------------
DBMS_REPCAT_AUTH PACKAGE BODY
DBMS_REPCAT_AUTH PACKAGE
AQ$_SCHEDULES_PRIMARY INDEX
AQ$_SCHEDULES TABLE 7) 开始升级数据字典 修改ORACLE_HOME和ORACLE_BASE目录： bash-3.00$ export ORACLE_HOME=/orahome/ora11g/product/11.2.0/ bash-3.00$ export ORACLE_BASE=/orahome/ora11g/ bash-3.00$ cd /orahome/ora11g/product/11.2.0/bin/ bash-3.00$ ./sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production on 星期二 7月 29 19:49:11 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> conn /as sysdba 已连接到空闲例程。 SQL> startup upgrade ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE 例程已经启动。 Total System Global Area 4344029184 bytes Fixed Size 2188488 bytes Variable Size 1107303224 bytes Database Buffers 3221225472 bytes Redo Buffers 13312000 bytes 数据库装载完毕。 数据库已经打开。 SQL> spool catupgrd.log SQL> @?/rdbms/admin/catupgrd.sql SQL> startup SQL> @?/rdbms/admin/utlu112s.sql SQL> @?/rdbms/admin/catuppst.sql 8) 编译无效对象 SQL> @?/rdbms/admin/utlrp.sql 编译完成后，检查是否有无效对象： select comp_name,version,status from dba_registry; 对编译后无效对象的处理（XDB和ORDIM组件）： -- Check status of XDB SQL> select comp_name, version, status 2 from dba_registry 3 where comp_id = 'XDB'; COMP_NAME VERSION STATUS ---------------------------------------- ------------------------------ ---------------------- Oracle XML Database 11.2.0.4.0 INVALID -- Check for invalid objects owned by XDB SQL> col OBJECT_NAME for a40 SQL> r 1 select owner, object_name, object_type, status 2 from dba_objects 3 where status = 'INVALID' 4* and owner = 'XDB' OWNER OBJECT_NAME OBJECT_TYPE STATUS ------------------------------ ---------------------------------------- ------------------- ------- XDB RESOURCE_VIEW VIEW INVALID XDB PATH_VIEW VIEW INVALID XDB XDB_RV_TRIG TRIGGER INVALID XDB XDB_PV_TRIG TRIGGER INVALID XDB XDB$ACL_PKG_INT PACKAGE INVALID XDB XDB$ACL_PKG_INT PACKAGE BODY INVALID XDB DBMS_RESCONFIG PACKAGE BODY INVALID XDB FUNCSTATS TYPE INVALID XDB FUNCSTATS TYPE BODY INVALID XDB PATH OPERATOR INVALID XDB DEPTH OPERATOR INVALID XDB ABSPATH OPERATOR INVALID XDB ALL_PATH OPERATOR INVALID Oracle 11.2 - XDB Removal and Reinstall
（1） XDB Removal
The catnoqm.sql script drops XDB.
SQL> spool xdb_removal.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup
SQL> @?/rdbms/admin/catnoqm.sql
SQL> spool off;
（2）XDB Installation
The catqm.sql script requires the following parameters be passed to it whenrun:
A. XDB user password
B. XDB user default tablespace
(Any tablespace other than SYSTEM, UNDO and TEMP canbe specified.
The specified tablespace must already exist prior torunning the script.)
C. XDB user temporary tablespace
D. SecureFiles = YES or NO
(If YES is specified, the XDB repository will useSecureFile storage.
If NO is specified, LOBS will beused.
To use SecureFiles, compatibility must beset to 11.2.
The tablespace specified for the XDBrepository must be using
Automatic Segment Space Management (ASSM)for SecureFiles to be used.)
Therefore the syntax to run catqm.sql is the following:
SQL> catqm.sql A B C D
For Example:
SQL> @?/rdbms/admin/catqm.sql XDB XDB TEMP YES
## IMPORTANT: You must shutdown and restart the database between removal andreinstall ##
SQL> spool xdb_install.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup;
SQL> @?/rdbms/admin/catqm.sql <XDB pwd> <XDB default tbs><XDB temporary tbs> <SecureFiles = YES/NO>
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off Verify XDB Installation –验证XDB 安装
spool xdb_status.txt
set echo on;
connect / as sysdba
set pagesize 1000
col comp_name format a36
col version format a12
col status format a8
col owner format a12
col object_name format a35
col name format a25
-- Check status of XDB
select comp_name, version, status
from dba_registry
where comp_id = 'XDB';
-- Check for invalid objects owned by XDB
select owner, object_name, object_type, status
from dba_objects
where status = 'INVALID'
and owner = 'XDB';
spool off; Reload ORDIM 组件
在前面提到Oracle Multimedia（ORDIM）组件需要使用XDB组件，在我们重建XDB组件之后需要Reload 一些ORDIM组件。 Oracle 11g 版本
OracleMultimedia requires Oracle XML Database (=XDB) to be installed. In cases whereOracle Multimedia is not in use and XDB has been reinstalled, the below stepswill assist to reinstall the part of Oracle Multimedia that uses XDB. --在进行如下操作之前，记得先备份： （1）Run SQL*Plus and connect as SYSDBA: SQL> alter session setcurrent_schema="ORDSYS";
SQL> @<ORACLE_HOME>/ord/im/admin/imxreg.sql;
SQL> @<ORACLE_HOME>/ord/im/admin/impbs.sql;
SQL> @<ORACLE_HOME>/ord/im/admin/impvs.sql;
SQL> @<ORACLE_HOME>/ord/im/admin/imtyb.sql;
SQL> @<ORACLE_HOME>/ord/im/admin/implb.sql;
SQL> @<ORACLE_HOME>/ord/im/admin/imxrepos.sql; （2）Now run validate_ordim connected asSYSDBA to verify if Oracle Multimedia is valid: SQL> set serveroutput on
SQL> exec validate_ordim;
SQL> select status, version from DBA_REGISTRY where comp_id = 'ORDIM'; This shouldshow, that Oracle Multimedia is VALID and at the same version as the database. 重建XDB组件后，还有无效的XDB对象
如果我们重建XDB组件后，还有有无效的XDB对象，处理方法下： Invalid XDB Objects After XDB Install [ID429551.1] 当我们重建了XDB后，仍然有一些XDB 对象无效，比如： DBMS_XMLDOM
DBMS_XMLPARSER
DBMS_XMLSCHEMA 当我们尝试编译时，又报如下错误： PLS-00201: identifier 'DBMS_LOB'
or
PLS-00201: identifier 'UTL_FILE' must be declared XDB lacks execute permissions onDBMS_LOB and UTL_FILE package. This is a permission granted bydefault. --导致这个这个问题的原因是因为XDB 在DBMS_LOB和 UTL_FILE包上没有执行权限。 解决方法有两种： （1）方法一 SQL> CONN / AS SYSDBA (Connect asSYSDBA)
SQL> DESC DBMS_LOB
SQL> DESC UTL_FILE
SQL> GRANT EXECUTE ON DBMS_LOB TO XDB;
SQL> GRANT EXECUTE ON UTL_FILE TO XDB;
SQL> @?/rdbms/admin/utlrp.sql -- Run this afew times
SQL> SELECT * FROM DBA_ERRORS;
SQL> SELECT COMP_ID, COMP_NAME, STATUS FROM DBA_REGISTRY;
SQL> SELECT OBJECT_NAME, OBJECT_TYPE, OWNER, STATUS FROM DBA_OBJECTS WHEREOWNER = 'XDB' AND STATUS != 'VALID'; （2）方法二： a) Grant following privileges to XDBuser SQL> GRANTEXECUTE ON DBMS_LOB TO XDB;
SQL> GRANT EXECUTE ON UTL_FILE TO XDB; b) Reload the XDB Component (usingxdbrelod.sql).
- Refer Master Note for Oracle XMLDatabase (XDB) Installation (Doc ID 1292089.1) 9) Timezone的升级 a) 升级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; b) 完成升级查询： SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 498016256 bytes Fixed Size 1345828 bytes Variable Size 364906204 bytes Database Buffers 125829120 bytes Redo Buffers 5935104 bytes Database mounted. Database opened. SQL> alter session set "_with_subquery"=materialize; Session altered. SQL> set serveroutput on SQL> VAR numfail number SQL> BEGIN 2 DBMS_DST.UPGRADE_DATABASE(:numfail, 3 parallel => TRUE, 4 log_errors => TRUE, 5 log_errors_table => 'SYS.DST$ERROR_TABLE', 6 log_triggers_table => 'SYS.DST$TRIGGER_TABLE', 7 error_on_overlap_time => FALSE, 8 error_on_nonexisting_time => FALSE); 9 DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail); 10 END; 11 / SQL> VAR fail number SQL> BEGIN 2 DBMS_DST.END_UPGRADE(:fail); 3 DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail); 4 END; 5 / SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value 2 FROM DATABASE_PROPERTIES 3 WHERE PROPERTY_NAME LIKE 'DST_%' 4 ORDER BY PROPERTY_NAME; PROPERTY_NAME VALUE ------------------------------ ------------------------------ DST_PRIMARY_TT_VERSION 14 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE SQL> SELECT * FROM v$timezone_file; FILENAME VERSION -------------------- ---------- timezlrg_14.dat 14
| 
QQ群⑧：
窥视卡
雷达卡
发表于 2014-8-14 09:52:23
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡