linux下oracle 11.2.0.1升级到11.2.0.4 linux下oracle 11.2.0.1升级到11.2.0.4
11.2.0.4 补丁号： 13390677，直接在mos中搜便可。
新特性：http://docs.oracle.com/cd/E11882_01/server.112/e41360/chapter1_11204.htm#NEWFT379
一共有7个文件，升级DB只须要1和2， 下载1和2便可：
1&2 db
3 grid
4 client
5 gateways
6 examples
7 deinstall
unzip解压
查看数据库信息
清空回收站
查看无效对象
备份数据库关闭数据库，关闭监听
修改环境变量中指定11.2.0.4，并用source生效
运行，Patchset 中的runInstaller升级软件
运行root脚本
不须要配置监听
运行升级后的脚本
根据检查，增大system表空间
收集数据字典状态
输出省略
大概15min
里面有shutdown命令
create spfile from memory
startup
@utlu112s.sql
SQL> @?/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 01-07-2014 20:27:23
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.4.0 00:16:14
Final Actions
. 00:00:01
Total Upgrade Time: 00:16:16
PL/SQL procedure successfully completed.
失效对象：
查看升级后的版本信息：
新特性：http://docs.oracle.com/cd/E11882_01/server.112/e41360/chapter1_11204.htm#NEWFT379
一共有7个文件，升级DB只须要1和2， 下载1和2便可：
1&2 db
3 grid
4 client
5 gateways
6 examples
7 deinstall
unzip解压
[oracle@localhost u02]$ ls -l
total 2466108
-rw-r--r-- 1 oracle dba 1380374377 Jan 7 17:34 p13390677_112040_LINUX_1of7.zip
-rw-r--r-- 1 oracle dba 1142436991 Jan 7 17:27 p13390677_112040_LINUX_2of7.zip
[oracle@localhost u02]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 7 15:54:51 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1336932 bytes
Variable Size 314575260 bytes
Database Buffers 96468992 bytes
Redo Buffers 6103040 bytes
Database mounted.
Database opened.
total 2466108
-rw-r--r-- 1 oracle dba 1380374377 Jan 7 17:34 p13390677_112040_LINUX_1of7.zip
-rw-r--r-- 1 oracle dba 1142436991 Jan 7 17:27 p13390677_112040_LINUX_2of7.zip
[oracle@localhost u02]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 7 15:54:51 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1336932 bytes
Variable Size 314575260 bytes
Database Buffers 96468992 bytes
Redo Buffers 6103040 bytes
Database mounted.
Database opened.
查看数据库信息
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string ORASQL
db_unique_name string ORASQL
global_names boolean FALSE
instance_name string orasql
lock_name_space string
log_file_name_convert string
service_names string ORASQL
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string ORASQL
db_unique_name string ORASQL
global_names boolean FALSE
instance_name string orasql
lock_name_space string
log_file_name_convert string
service_names string ORASQL
清空回收站
SQL> purge DBA_RECYCLEBIN;
DBA Recyclebin purged.
SQL> select count(*) from DBA_RECYCLEBIN;
COUNT(*)
----------
0
DBA Recyclebin purged.
SQL> select count(*) from DBA_RECYCLEBIN;
COUNT(*)
----------
0
查看无效对象
SQL> select count(*) from dba_objects where status<>'VALID';
COUNT(*)
----------
0
COUNT(*)
----------
0
备份数据库关闭数据库，关闭监听
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost u02]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 07-JAN-2014 16:09:28
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
[oracle@localhost u02]$
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost u02]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 07-JAN-2014 16:09:28
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
[oracle@localhost u02]$
修改环境变量中指定11.2.0.4，并用source生效
export ORACLE_BASE=/u02
export ORACLE_HOME=/u02/product/database
export ORACLE_HOME=/u02/product/database
运行，Patchset 中的runInstaller升级软件
[oracle@localhost u02]$ cd database/
[oracle@localhost database]$ ls
install response runInstaller stage
readme.html rpm sshsetup welcome.html
[oracle@localhost database]$ ./runInstaller
[oracle@localhost database]$ ls
install response runInstaller stage
readme.html rpm sshsetup welcome.html
[oracle@localhost database]$ ./runInstaller
运行root脚本
[oracle@localhost ~]$ su -
Password:
[root@localhost ~]# cd /u02/product/database/
[root@localhost database]# sh root.sh
Performing root user operation for Oracle 11g
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u02/product/database
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
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 script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
[root@localhost database]#
Password:
[root@localhost ~]# cd /u02/product/database/
[root@localhost database]# sh root.sh
Performing root user operation for Oracle 11g
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u02/product/database
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
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 script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
[root@localhost database]#
不须要配置监听
运行升级后的脚本
[oracle@localhost ~]$ export ORACLE_HOME=/oracle/product/database/
[oracle@localhost ~]$ $ORACLE_HOME/bin/sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 7 19:09:38 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1336932 bytes
Variable Size 314575260 bytes
Database Buffers 96468992 bytes
Redo Buffers 6103040 bytes
Database mounted.
Database opened.
SQL> @/u02/product/database/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 01-07-2014 19:12:02
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: ORASQL
--> version: 11.2.0.1.0
--> compatible: 11.2.0
--> blocksize: 8192
--> platform: Linux IA (32-bit)
--> timezone file: V11
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
WARNING: --> SYSTEM tablespace is not large enough for the upgrade.
.... currently allocated size: 400 MB
.... minimum required size: 442 MB
.... increase current size by: 42 MB
.... tablespace is NOT AUTOEXTEND ENABLED.
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 449 MB
--> TEMPTS1 tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
--> UNDOTBS2 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 32-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
WARNING: --> "memory_target" needs to be increased to at least 436 MB
.
--> If Target Oracle is 64-Bit, refer here for Update Parameters:
WARNING: --> "memory_target" needs to be increased to at least 620 MB
.
**********************************************************************
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]
**********************************************************************
-- 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
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
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 11.2.0.1.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle bin is empty.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
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.
**********************************************************************
[oracle@localhost ~]$ $ORACLE_HOME/bin/sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 7 19:09:38 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1336932 bytes
Variable Size 314575260 bytes
Database Buffers 96468992 bytes
Redo Buffers 6103040 bytes
Database mounted.
Database opened.
SQL> @/u02/product/database/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 01-07-2014 19:12:02
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: ORASQL
--> version: 11.2.0.1.0
--> compatible: 11.2.0
--> blocksize: 8192
--> platform: Linux IA (32-bit)
--> timezone file: V11
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
WARNING: --> SYSTEM tablespace is not large enough for the upgrade.
.... currently allocated size: 400 MB
.... minimum required size: 442 MB
.... increase current size by: 42 MB
.... tablespace is NOT AUTOEXTEND ENABLED.
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 449 MB
--> TEMPTS1 tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
--> UNDOTBS2 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 32-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
WARNING: --> "memory_target" needs to be increased to at least 436 MB
.
--> If Target Oracle is 64-Bit, refer here for Update Parameters:
WARNING: --> "memory_target" needs to be increased to at least 620 MB
.
**********************************************************************
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]
**********************************************************************
-- 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
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
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 11.2.0.1.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle bin is empty.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
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.
**********************************************************************
根据检查，增大system表空间
SQL> alter database datafile '/oracle/oradata/orasql/datafile/system01.dbf' resize 450m;
收集数据字典状态
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
startup UPGRADE pfile=‘/oracle/product/database/dbs/intorasql.ora’
@catupgrd.sql
startup UPGRADE pfile=‘/oracle/product/database/dbs/intorasql.ora’
@catupgrd.sql
输出省略
大概15min
里面有shutdown命令
create spfile from memory
startup
@utlu112s.sql
SQL> @?/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 01-07-2014 20:27:23
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.4.0 00:16:14
Final Actions
. 00:00:01
Total Upgrade Time: 00:16:16
PL/SQL procedure successfully completed.
失效对象：
@?/rdbms/admin/utlrp.sql
查看升级后的版本信息：
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1365040 bytes
Variable Size 306187216 bytes
Database Buffers 104857600 bytes
Redo Buffers 6074368 bytes
Database mounted.
Database opened.
SQL> show parameter golden
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean FALSE
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orasql OPEN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1365040 bytes
Variable Size 306187216 bytes
Database Buffers 104857600 bytes
Redo Buffers 6074368 bytes
Database mounted.
Database opened.
SQL> show parameter golden
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean FALSE
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orasql OPEN