Oracle Database 升级（10.2.0.1 -- 10.2.0.4)

最新推荐文章于 2023-03-03 08:49:58 发布

weixin_33724059

最新推荐文章于 2023-03-03 08:49:58 发布

阅读量136

点赞数

文章标签： java 数据库操作系统

原文链接：http://blog.51cto.com/626166658/929302

版权

首先感谢博主“客居天涯”，原文地址“http://tiany.blog.51cto.com/513694/846066”。

1、检查当前版本

05:39:08 SQL> select * from v$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

2、检查磁盘空间（system 必须要最少10m）
05:46:49 SQL>
select a.tablespace_name, round(a.total_size) "total_size(mb)",
round(a.total_size) - round(b.free_size,3) "unsed_size(mb)",
round(b.free_size,3) "free_size(mb)",
round(b.free_size/total_size *100,2) ||'%' free_rate
from
(select tablespace_name,sum(bytes) /1024/1024 total_size
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,sum(bytes)/1024/1024 free_size
from dba_free_space
05:51:11 11 group by tablespace_name) b
05:51:11 12 where a.tablespace_name=b.tablespace_name(+);

TABLESPACE_NAME total_size(mb) unsed_size(mb) free_size(mb) FREE_RATE
-------------------- -------------- -------------- ------------- -----------------------------------------
UNDOTBS1 85 14.437 70.563 83.01%
SYSAUX 260 248.312 11.688 4.5%
LXTBS1 50 .125 49.875 99.75%
USERS 10 5.75 4.25 42.5%
SYSTEM 490 482.062 7.938 1.62%
EXAMPLE 100 68.25 31.75 31.75%

6 rows selected.

-------添加磁盘空间

05:51:23 SQL> alter database datafile 1 resize 800m;

Database altered.

05:52:27 SQL> alter database datafile 4 resize 100m;

Database altered.

05:52:49 SQL> alter database datafile 2 resize 400m;

Database altered.

select a.tablespace_name, round(a.total_size) "total_size(mb)",
round(a.total_size) - round(b.free_size,3) "unsed_size(mb)",
round(b.free_size,3) "free_size(mb)",
round(b.free_size/total_size *100,2) ||'%' free_rate
from
(select tablespace_name,sum(bytes) /1024/1024 total_size
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,sum(bytes)/1024/1024 free_size
05:53:49 10 from dba_free_space
05:53:51 11 group by tablespace_name) b
05:53:58 12 where a.tablespace_name=b.tablespace_name(+);

TABLESPACE_NAME total_size(mb) unsed_size(mb) free_size(mb) FREE_RATE
-------------------- -------------- -------------- ------------- -----------------------------------------
UNDOTBS1 400 14.437 385.563 96.39%
SYSAUX 260 248.312 11.688 4.5%
LXTBS1 50 .125 49.875 99.75%
USERS 100 5.75 94.25 94.25%
SYSTEM 800 482.062 317.938 39.74%
EXAMPLE 100 68.25 31.75 31.75%

6 rows selected.

05:54:04 SQL> col name for a50
05:54:35 SQL> select file#,name from v$datafile;

FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/prod/system01.dbf
2 /u01/app/oracle/oradata/prod/undotbs01.dbf
3 /u01/app/oracle/oradata/prod/sysaux01.dbf
4 /u01/app/oracle/oradata/prod/users01.dbf
5 /u01/app/oracle/oradata/prod/example01.dbf
6 /u01/app/oracle/oradata/prod/lxtbs1.dbf

6 rows selected.

05:54:50 SQL> alter database datafile 3 resize 400m;

Database altered.

select a.tablespace_name, round(a.total_size) "total_size(mb)",
round(a.total_size) - round(b.free_size,3) "unsed_size(mb)",
round(b.free_size,3) "free_size(mb)",
round(b.free_size/total_size *100,2) ||'%' free_rate
from
(select tablespace_name,sum(bytes) /1024/1024 total_size
from dba_data_files
group by tablespace_name) a,
05:55:58 9 (select tablespace_name,sum(bytes)/1024/1024 free_size
05:55:59 10 from dba_free_space
05:56:05 11 group by tablespace_name) b
05:56:11 12 where a.tablespace_name=b.tablespace_name(+);

TABLESPACE_NAME total_size(mb) unsed_size(mb) free_size(mb) FREE_RATE
-------------------- -------------- -------------- ------------- -----------------------------------------
UNDOTBS1 400 14.437 385.563 96.39%
SYSAUX 400 248.312 151.688 37.92%
LXTBS1 50 .125 49.875 99.75%
USERS 100 5.75 94.25 94.25%
SYSTEM 800 482.062 317.938 39.74%
EXAMPLE 100 68.25 31.75 31.75%

6 rows selected.

05:56:18 SQL>

3、调整内存参数（share pool和java pool 加快升级速度）

05:44:55 SQL> show parameter sga;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 492M
sga_target big integer 492M
05:45:00 SQL> select name,bytes/1024/1024 from v$sgainfo;

NAME BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size 1.1638031
Redo Buffers 2.8359375
Buffer Cache Size 328
Shared Pool Size 124
Large Pool Size 12
Java Pool Size 24
Streams Pool Size 0
Granule Size 4
Maximum SGA Size 492
Startup overhead in Shared Pool 36
Free SGA Memory Available 0

11 rows selected.

05:45:24 SQL> alter system set java_pool_size =100m;

System altered.

05:45:53 SQL> select name,bytes/1024/1024 from v$sgainfo;

NAME BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size 1.1638031
Redo Buffers 2.8359375
Buffer Cache Size 252
Shared Pool Size 124
Large Pool Size 12
Java Pool Size 100
Streams Pool Size 0
Granule Size 4
Maximum SGA Size 492
Startup overhead in Shared Pool 36
Free SGA Memory Available 0

11 rows selected.

05:45:55 SQL>

4、为数据库做冷备份

RMAN> run {
2> shutdown immediate;
3> startup mount;
4> allocate channel c1 type disk;
5> allocate channel c2 type disk;
6> backup full tag='db_full_bak' database format '/disk1/rman/prod/cold_bak/%d_%s.bak';
7> alter database open;
8> }

RMAN> list backup;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10 Full 378.33M DISK 00:01:29 27-APR-12
BP Key: 10 Status: AVAILABLE Compressed: NO Tag: DB_FULL_BAK
Piece Name: /disk1/rman/prod/cold_bak/PROD_10.bak
List of Datafiles in backup set 10
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1481218 27-APR-12 /u01/app/oracle/oradata/prod/system01.dbf
4 Full 1481218 27-APR-12 /u01/app/oracle/oradata/prod/users01.dbf
6 Full 1481218 27-APR-12 /u01/app/oracle/oradata/prod/lxtbs1.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11 Full 309.84M DISK 00:01:28 27-APR-12
BP Key: 11 Status: AVAILABLE Compressed: NO Tag: DB_FULL_BAK
Piece Name: /disk1/rman/prod/cold_bak/PROD_11.bak
List of Datafiles in backup set 11
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 1481218 27-APR-12 /u01/app/oracle/oradata/prod/undotbs01.dbf
3 Full 1481218 27-APR-12 /u01/app/oracle/oradata/prod/sysaux01.dbf
5 Full 1481218 27-APR-12 /u01/app/oracle/oradata/prod/example01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
12 Full 7.11M DISK 00:00:01 27-APR-12
BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20120427T060513
Piece Name: /u01/app/oracle/flash_recovery_area/PROD/autobackup/2012_04_27/o1_mf_s_781682594_7smkntpy_.bkp
Control File Included: Ckp SCN: 1481218 Ckp time: 27-APR-12
SPFILE Included: Modification time: 27-APR-12

5、关闭数据库实例及相关进程

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN>

6、讲补丁包上传到oracle server ，解压、安装

[oracle@RH4 ~]$ unzip p6810189_10204_Linux-x86.zip //解压后生成Disk1 目录

7、以图形界面进入Oracle server

[oracle@RH4 ~]$cd Disk1
[oracle@RH4 Disk1]$ ./runInstaller

---按照提示升级

[oracle@RH4 ~]$ su -
Password:
[root@RH4 ~]# /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.
[root@RH4 ~]#

8、升级完成后以startup upgrade 方式打开库

[oracle@RH4 ~]$ !sql
sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 27 06:19:46 2012

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 515899392 bytes
Fixed Size 1268412 bytes
Variable Size 247465284 bytes
Database Buffers 264241152 bytes
Redo Buffers 2924544 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

[oracle@RH4 ~]$ tail -f /u01/app/oracle/admin/prod/bdump/alert_prod.log
SMON: enabling cache recovery
Fri Apr 27 06:19:58 2012
Errors in file /u01/app/oracle/admin/prod/udump/prod_ora_10440.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Fri Apr 27 06:19:58 2012
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 10440
ORA-1092 signalled during: ALTER DATABASE OPEN...

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 515899392 bytes
Fixed Size 1268412 bytes
Variable Size 247465284 bytes
Database Buffers 264241152 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.

9、升级数据字典
SQL>spool /home/oracle/patch.log

SQL>@?/rdbms/admin/catupgrd.sql

SQL>spool off;

10、重编译失效对象：

sql>shutdown immediate

sql>startup

SQL>@?/rdbms/admin/utlrp.sql

11、升级后的检测

SQL>select comp_name,version,status from sys.dba_registry;

12、检查组件的升级情况

SQL>select * from utl_recomp_errors;

13、修改兼容性参数