分类： Oracle
2010-03-26 21:00:36
冷备份[升级必须关闭数据库，所以使用冷备份即可]所有数据文件、控制文件、redo log文件同时关闭数据库:
1、查询位置：
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
SQL> show parameters control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
oradata/orcl/control02.ctl, /u
01/app/oracle/oradata/orcl/con
trol03.ctl
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo01.log
2、查看空间大小：
[oracle@localhost ~]$ du /u01/app/oracle/oradata/orcl -h
1.4G /u01/app/oracle/oradata/orcl
3、保证有足够的临时表空间:
查询临时表空间状态:
SQL> select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
FILE_SIZE AUT
---------- ---
TEMP
/u01/app/oracle/oradata/orcl/temp01.dbf
100 YES扩 展临时表空间：
方法一、增大临时文件大小：
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 100m;
Database altered.
方法 二、将临时数据文件设为自动扩展：
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' autoextend on next 5m maxsize unlimited;
Database altered.
方 法三、向临时表空间中添加数据文件：
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 100m;
Tablespace altered.
4、关闭所有相关进程：
$ sqlplus / as sysdba
SQL>shutdown immediate
$ emctl stop dbconsole
$ isqlplusctl stop
$ lsnrctl stop
使用
ps –Af|grep oracle检查是否还有相关进程
5、找到空闲的分区，拷贝文件进行备份：
[oracle@localhost ~]$ cp -r /u01/app/oracle/oradata/orcl orcl_bak
上传patchset(p6810189_10204_Linux-x86.zip)、解压缩
启动xterm执行./runInstall，一路Next
切换至root用户执行root.sh
[root@localhost ~]# sh /u01/app/oracle/product/10.2.0/db_1/root.sh
Running Oracle10 root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/10.2.0/db_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying coraenv to /usr/local/bin ...
Entries will be added to the /etc/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.
以上为patchset安装，之后进行安装后的升级，如果不执行之后的操作，startup将会报如下错误：ORA-01092: ORACLE instance terminated. Disconnection forced
使用dbua自动升级：
1、启动监听：
$ lsnrctl start
2、执行升级助手：
$ dbua
手工升级：
1、 SQL> startup upgrade启动数据库
2、 SQL> SPOOL upgrade_info.log
3、 执行@$ORACLE_HOME/rdbms/admin/utlu102i.sql 收集升级信息
4、 SQL> SPOOL OFF保存至文件
Oracle Database 10.2 Upgrade Information Utility 02-23-2010 14:24:21
.
**********************************************************************
Database:
**********************************************************************
--> name: ORCL
--> version: 10.2.0.1.0
--> compatible: 10.2.0.1.0
--> blocksize: 8192
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]//表空间大小是否足够
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 488 MB
.... AUTOEXTEND additional space required: 8 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 402 MB
.... AUTOEXTEND additional space required: 372 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 245 MB
.... AUTOEXTEND additional space required: 15 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
.... AUTOEXTEND additional space required: 37 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 69 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
//需更新的参数
**********************************************************************
WARNING: --> "pga_aggregate_target" needs to be increased to at least 25165824
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
//需重命名的参数
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
//需废弃的参数
**********************************************************************
-- No obsolete parameters found. No changes are required
.
**********************************************************************
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 Java Packages [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> Oracle Data Mining [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Rule Manager [upgrade] VALID
.
PL/SQL procedure successfully completed.
5、 启动监听：[oracle@localhost Disk1]$ lsnrctl start
6、 进入sqlplus
SQL> startup upgrade
SQL> SPOOL patch.log
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql --升级脚本[刷新数据字典]
SQL> SPOOL OFF
7、 重启oracle
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
8、 SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
tips:utlrp.sql脚本可以在数据库运行的状态下执行以编译、数据库中的invalid对象.
oracle建议在对数据库进行迁移、升级、降级后都运行一遍utlrp.sql以编译无效对象。
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2010-02-23 16:14:00
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2010-02-23 16:15:43
PL/SQL procedure successfully completed.
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.
9、 查询sys.dba_registry视图，看组件的status是否是valid
SQL> select comp_name, version, status from sys.dba_registry;
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
Oracle Database Catalog Views
10.2.0.4.0 VALID
Oracle Database Packages and Types
10.2.0.4.0 VALID
Oracle Workspace Manager
10.2.0.4.3 VALID
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
JServer JAVA Virtual Machine
10.2.0.4.0 VALID
Oracle XDK
10.2.0.4.0 VALID
Oracle Database Java Packages
10.2.0.4.0 VALID
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
Oracle Expression Filter
10.2.0.4.0 VALID
Oracle Data Mining
10.2.0.4.0 VALID
Oracle Text
10.2.0.4.0 VALID
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
Oracle XML Database
10.2.0.4.0 VALID
Oracle Rule Manager
10.2.0.4.0 VALID
Oracle interMedia
10.2.0.4.0 VALID
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
OLAP Analytic Workspace
10.2.0.4.0 VALID
Oracle OLAP API
10.2.0.4.0 VALID
OLAP Catalog
10.2.0.4.0 VALID
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
Spatial
10.2.0.4.0 VALID
Oracle Enterprise Manager
10.2.0.4.0 VALID
17 rows selected.
10、 emca -upgrade db --更新EM资料库
tips:使用命令行工具emca可以创建，修改，重建或者删除dbcontrol的配置。而使用命令行工具emctl可以启动/停 止EM console服务，察看服务状态等。
11、 执行changePerm.sh脚本[可选，此脚本用于不属于dba的组执行ORACLE_HOME目录下的可执行程序，有安全隐含，建议还是不要执 行。]
12、 如果安装有Oracle Database Vault[配置工具dvca,Oracle的一款数据库安全产品]，则执行Oracle Database Vault的升级
有用的命令：
1、mount -t iso9660 /dev/cdrom /media/cdrom
2、安装VIM：安装vim-common和vim-enhanced两个软件包
3、配置RedHat为静态IP:
#netconfig
重启网卡(远程控制的慎用)
#ifconfig eth0 down
#ifconfig eth0 up
4、redhat查询软件包是否安装：
rpm –q 包名
-EOF-
