just do it
分类： Oracle
2014-08-07 10:13:38
环境：
主机：SUN E6500
操作系统：solaris10u7 sparc 64bit
数据库：oracle9i（9206）
升级步骤：
用静默方式安装：
修改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 #
$ 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
安装oracle11g到新的目录：
ORACLE_BASE=/orahome/ora11g
ORACLE_HOME= /orahome/ora11g /product/11.2.0
静默安装oracle11g：
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=
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
./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完成。
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;
文件已创建。
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'
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
修改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
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
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> @
SQL> @
SQL> @
SQL> @
SQL> @
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)
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;
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
