Oracle数据库升级-课堂笔记
- ：Oracle 数据库升级介绍
1.1 版本发展历史
1.2 升级线路
1.3 方法和类型
升级有两种方法：
1）利用DBUA进行升级
2）利用sqlplus执行补丁脚本进行升级
补丁类型分为三种
1）Interim（临时补丁程序） 精准版本修复，需要MetaLink精准定位问题。由于缺少基础测试，可能出现冲突。
2）CPU（Critical Patch Update关键补丁更新） 累计的补丁，每三个月发布一次，针对安全漏洞。
3）PSU（Patch Sets Update补丁集更新） 可靠的，被经常下载的累计补丁，每三个月发布一次，修复bug。
1.4 版本查看及说明
SQL> select * from v$version;
BANNER
1 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
2 PL/SQL Release 10.2.0.1.0 - Production
3 CORE 10.2.0.1.0 Production
4 TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
5 NLSRTL Version 10.2.0.1.0 – Production
以本课升级过程为例：
Oracle版本号构成：5位，小数点隔开
主版本号.次版本号(1或2).中间件的版本号(0).组件的版本号.平台版本号
例如：Oracle 10.2.0.4.4
1. Major Database Release Number
第一个数字位，它代表的是一个新版本软件，也标志着一些新的功能。如11g，10g。
2. Database Maintenance Release Number
第二个数字位，代表一个maintenance release 级别，也可能包含一些新的特性。
3. Fusion Middleware Release Number
第三个数字位，反应Oracle 中间件（Oracle Fusion Middleware）的版本号。
4. Component-Specific Release Number
第四个数字位，主要是针对组件的发布级别。不同的组件具有不同的号码。 比如Oracle 的patch包。
5. Platform-Specific Release Number
第五个数字位，这个数字位标识一个平台的版本。通常表示patch 号。
在oracle 9.2 版本之后， oracle 的maintenance release number 是在第二数字位更改。 而在之前，是在第三个数字位。
1.5 如何下载补丁
应从MOS下载最新版本的Opatch。
1.6 升级流程
1)备份当前oracle安装目录
2)备份当前oracle数据库
3)调整shared pool和java pool------>加快升级速度
4)调整system,undo，默认表空间
5)关库
6)上传补丁包
7)升级软件
8)升级数据字典
9)检测升级后情况，重新修改兼容性参数
- ：升级范例
2.1升级前准备
1）计划好停机时间
2）做好数据库及软件目录的备份
3）估算升级时间及失败回退的时间
一） 做备份
1）RMAN冷备数据---备份数据库（本例因空间不足，以虚机备份替代）
rman target /
run{
shutdown immediate;
startup mount;
backup database;
sql'alter database open';
}
2）备份$ORACLE_HOME目录（备份Oracle 软件，省略）
cp -R db_1 db_1.bk
二）调整SGA, （share pool 和java pool）
[root@rh55 ~]# free -m
total used free shared buffers cached
Mem: 1770 579 1190 0 22 431
-/+ buffers/cache: 125 1644
Swap: 2047 0 2047
10:20:07 SYS@ prod>show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 260M
sga_target big integer 260M
10:31:10 SYS@ prod>create pfile from spfile; 备份一份spfile
10:26:16 SYS@ prod>alter system set sga_max_size=1024m scope=spfile;
10:27:16 SYS@ prod>alter system set sga_target=1024m scope=spfile;
10:27:38 SYS@ prod>alter system set shared_pool_size=300m scope=spfile;
10:29:19 SYS@ prod>alter system set java_pool_size=300m scope=spfile;
10:27:25 SYS@ prod>startup force
三） 调整表空间
查一下如果system, sysaux, undo表空间是自动扩展的，就不需要调整表空间尺寸了。
10:34:26 SYS@ prod>select TABLESPACE_NAME,AUTOEXTENSIBLE from dba_data_files;
2.2 CPU方式补丁升级
10.2.0.1.0---->10.2.0.4.0
一）关闭数据库
按计划停机，时间应该包括升级时间+升级失败的恢复
10:21:25 SYS@ prod>shutdown immediate
一定要退出sqlplus！！！
关闭listener.
- 上传补丁
以root用户将三个补丁上传到/home/oracle下
[root@rh55 ~]# cd /home/oracle
[root@rh55 oracle]# ll *.zip
-rw-r--r-- 1 root root 1053748381 May 7 15:05 p6810189_10204_Linux-x86.zip CPU补丁
-rw-r--r-- 1 root root 27412455 May 7 15:05 p6880880_102000_LINUX.zip 高版本opatch程序
-rw-r--r-- 1 root root 17960702 May 7 15:05 p9352164_1020404_Linux-x86.zip PSU补丁
[root@rh55 oracle]# unzip p6810189_10204_Linux-x86.zip
生成Disk1文件夹
三）升级Oracle软件
[root@rh55 ~]# /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@rh55 ~]#
正常退出安装界面
[root@rh55 ~]# su - oracle
[oracle@rh55 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 5 11:33:00 2016
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
- 升级数据字典
1）进入upgrade状态
SQL> startup upgrade
SQL> spool '/home/oracle/up.log' 将升级过程记录在up.log日志中
SQL> @?/rdbms/admin/catupgrd.sql
此过程耗时较长（20分钟）
SQL> shutdown immediate 正常关闭，
SQL> startup 启动数据库到正常状态
ORACLE instance started.
Database opened
2）跑一下重编译脚本
SQL> @?/rdbms/admin/utlrp.sql
ERRORS DURING RECOMPILATION
---------------------------
0
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> spool off
3）验证：查看Oracle组件各个版本
SQL> set linesize 120;
SQL> col comp_name for a50;
SQL> select COMP_NAME,VERSION,STATUS from dba_registry;
SQL>
4）确认应用没有问题后，兼容性参数设置为10.2.0.4.0
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.1.0
SQL> alter system set compatible='10.2.0.4.0' scope=spfile;
SQL> startup force
CPU补丁升级完毕
2.3 PSU方式补丁升级
10.2.0.4.0---->10.2.0.4.4
一）了解opatch工具
[oracle@rh55 ~]$ cd $ORACLE_HOME
[oracle@rh55 db_1]$ cd OPatch/
[oracle@rh55 OPatch]$ ll opatch
-rwxr-xr-x 1 oracle oinstall 6315 Nov 23 2007 opatch
[oracle@rh55 OPatch]$ ./opatch version 查看一下 opatch工具的版本
Invoking OPatch 10.2.0.4.2
OPatch Version: 10.2.0.4.2
OPatch succeeded.
发现现有的opatch工具的版本<10.2.0.4.4（PSU补丁），此工具版本过低，不能用。
新的opatch工具也是从MOS网站的补丁集中获取的
[oracle@rh55 OPatch]$ cd 回到/home/oracle下
[oracle@rh55 ~]$ unzip p6880880_102000_LINUX.zip 使用oracle 用户解压这个文件
[oracle@rh55 ~]$ cd OPatch 进入新生成的OPatch目录下
[oracle@rh55 OPatch]$ ./opatch version 显示新的opatch程序版本
Invoking OPatch 10.2.0.5.1
OPatch Version: 10.2.0.5.1
OPatch succeeded.
[oracle@rh55 OPatch]$ cd
[oracle@rh55 ~]$unzip p9352164_1020404_Linux-x86.zip 解压后生成了9352164文件夹
注意：在9352164文件夹中，有一个README.html文件，它是一个做PSU的指导说明。
升级前要研究一下这个html文件，你可以下载到win7，也可以配置linux提供http服务（这步已经配置好了）
[root@rh55 ~]# service httpd start 启动http服务
[root@rh55 ~]# cp /home/oracle/9352164/README.html /var/www/html 把html文件放到指定目录下
然后从win7浏览器查看补丁包的安装帮助文档
二）升级Oracle软件
SQL> shutdown immediate;
SQL> exit 别忘退出Sqlplus
使用新的opatch程序打补丁
[oracle@rh55 ~]$cd 9352164 先进入9352164
[oracle@rh55 9352164]$ /home/oracle/OPatch/opatch apply 再执行opatch
Invoking OPatch 10.2.0.5.1
Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/10.2.0/db_1
Central Inventory : /u01/app/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.5.1
OUI version : 10.2.0.4.0
OUI location : /u01/app/oracle/product/10.2.0/db_1/oui
Log file location : /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2016-05-05_15-03-07PM.log
Patch history file: /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt
ApplySession applying interim patch '9352164' to OH '/u01/app/oracle/product/10.2.0/db_1'
Running prerequisite checks...
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name: 此处回车
You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: y
OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/10.2.0/db_1')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '9352164' for restore. This might take a while...
.............
Return Code = 0
The local system has been patched and can be restarted.
OPatch succeeded.
[oracle@rh55 9352164]$
- 升级数据字典
[oracle@rh55 9352164]$ sqlplus / as sysdba
SQL> startup
Database opened.
1）运行PSU脚本
SQL> @?/rdbms/admin/catbundle.sql psu apply
Check the following log file for errors:
/u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/catbundle/catbundle_PSU_PROD_APPLY_2016May05_15_13_55.log
SQL>
2）重新编译无效的对象
SQL> @?/rdbms/admin/utlrp.sql
ERRORS DURING RECOMPILATION
---------------------------
0
1 row selected.
PL/SQL procedure successfully completed.
四）一个特殊补丁
补丁2008 仅执行一次，这个补丁oracle软件自带，不需要上传。
SQL> @?/cpu/view_recompile/recompile_precheck_jan2008cpu.sql 先检查一下
Running precheck.sql...
Number of views to be recompiled :2054
-----------------------------------------------------------------------
Number of objects to be recompiled :4128
Please follow the README.txt instructions for running viewrecomp.sql
PL/SQL procedure successfully completed.
SQL> shutdown immediate;
SQL> startup upgrade 进入upgrade模式
SQL> @?/cpu/view_recompile/view_recompile_jan2008cpu.sql
1 row selected.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
1 row created.
Commit complete.
No. of Invalid Objects is :80
Please refer to README.html to for instructions on validating these objects
PL/SQL procedure successfully completed.
Logfile for the current viewrecomp.sql session is : vcomp_PROD_05May2016_15_30_03.log
SQL>
SQL> shutdown immediate; 干净的关闭数据库
SQL> startup 正常启动数据库
[oracle@rh55 OPatch]$ pwd
/home/oracle/OPatch 使用新版本的opatch
[oracle@rh55 OPatch]$ ./opatch lsinventory -bugs_fixed |grep -i 'database' 验证升级后版本
Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 3 10.2.0.4.0
STANDBY DATABASE
5934363 9352164 Thu May 05 15:08:03 CST 2016 CREATE CTXCAT INDEX IN RAC DATABASE HANG
6733655 9352164 Thu May 05 15:08:03 CST 2016 ODE DATABASE; ONE INSTANCE CRASHED AFTER HITTING
6799205 9352164 Thu May 05 15:08:03 CST 2016 APPSST 10G GSI ;- CLUSTER DATABASE HANG AND CAN
NOT CONNECT TO DATABASE
6919819 9352164 Thu May 05 15:08:03 CST 2016 FLASHBACK DATABASE DID NOT CORRECTLY FLASHBACK
6954722 9352164 Thu May 05 15:08:03 CST 2016 DATABASE INSTANCE GOT TERMINATED WITH
DURING OPEN DATABASE
7044551 9352164 Thu May 05 15:08:03 CST 2016 ALTER DATABASE BACKUP CONTROLFILE TO TRACE HANGS
7299153 9352164 Thu May 05 15:08:03 CST 2016 RAC DATABASE AND ASM HANG
DATABASE TO BE REINSTATED IS UNKNOWN
7477934 9352164 Thu May 05 15:08:03 CST 2016 READ ONLY OR ACTIVE PHYSICAL STANDBY DATABASES
7573151 9352164 Thu May 05 15:08:03 CST 2016 DIFFERENT RESULT IN DIFFERNT NODE OF RAC DATABASE
7592346 9352164 Thu May 05 15:08:03 CST 2016 CPUJAN2009 DATABASE 10.2.0.4
8290506 9352164 Thu May 05 15:08:03 CST 2016 CPUAPR2009 DATABASE 10.2.0.4
8310931 9352164 Thu May 05 15:08:03 CST 2016 ORA-600 [KTPRHTNEW6] LEADING TO DATABASE CRASH
8354686 9352164 Thu May 05 15:08:03 CST 2016 ORA-600 [KSS_GET_TYPE NULL SO] IN PRIMARY DATABASE
8534387 9352164 Thu May 05 15:08:03 CST 2016 CPUJUL2009 DATABASE 10.2.0.4
8576156 9352164 Thu May 05 15:08:03 CST 2016 DATABASE PSU 10.2.0.4.1 (INCLUDES CPUJUL2009)
8833280 9352164 Thu May 05 15:08:03 CST 2016 DATABASE PSU 10.2.0.4.2 (INCLUDES CPUOCT2009)
8836308 9352164 Thu May 05 15:08:03 CST 2016 CPUOCT2009 DATABASE 10.2.0.4
9119226 9352164 Thu May 05 15:08:03 CST 2016 CPUJAN2010 DATABASE 10.2.0.4
9119284 9352164 Thu May 05 15:08:03 CST 2016 DATABASE PSU 10.2.0.4.3 (INCLUDES CPUJAN2010)
9352164 9352164 Thu May 05 15:08:03 CST 2016 DATABASE PSU 10.2.0.4.4 (INCLUDES CPUAPR2010)
9352191 9352164 Thu May 05 15:08:03 CST 2016 CPUAPR2010 DATABASE 10.2.0.4
SQL> set wrap off
SQL> select ACTION_TIME,VERSION,comments from registry$history;
ACTION_TIME VERSION COMMENTS
------------------------------ ------------------------------ --------------------------------------------------
05-MAY-16 11.59.42.873685 AM 10.2.0.4.0 Upgraded from 10.2.0.1.0
05-MAY-16 03.14.53.956043 PM 10.2.0.4 PSU 10.2.0.4.4
05-MAY-16 03.31.05.096163 PM view recompilation
*****做个虚机快照*****
2.4 升级到11g
10.2.0.4.4---->11.2.0.1.0
- 升级方法
in-place: 用11g的软件覆盖10g （节省磁盘空间，但回退慢）
out-place:将11g软件安装在单独的目录下，10g的软件保留（占用磁盘空间，回退快）
二）安装11g软件
1）上传11g的软件（如果有Oracle11g镜像文件，也可以mount它，省的占空间了）
2）编辑11g要求的核心参数文件sysctl.conf
在/etc/sysctl.conf文件中加入下列行：（最后七行是10g参数可以删掉，其实不删也可以，因为重复的设置以最后一次为准）
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
运行下列命令使修改的核心参数立即生效
# sysctl -p
3）设置Shell Limits(系统资源限制),提高软件的运行效率
在/etc/security/limits.conf文件中加入下列红色行：（10g参数可以删去）
oracle soft nofile 65536
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384
4）yum 安装四个包
yum配置文件已经做好了，直接插入ISO镜像光盘安装即可。
[root@rh55]#mount /dev/cdrom /media ---挂载磁盘安装软件包
[root@rh55]# yum install -y libaio*
[root@rh55]# yum install -y sysstat*
[root@rh55]# yum install -y unixODBC-2*
[root@rh55]# yum install -y unixODBC-devel*
- 为安装11g软件，需要添加11g的$ORACLE_HOME
[root@rh55 ~]# su - oracle
[oracle@rh55]$ mkdir -p /u01/app/oracle/product/11.2.0/db_1
[oracle@rh55 ~]$ vi .bash_profile
#export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1 屏蔽掉10g的
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1 添加11 g的
软件安装结束
三）准备升级
1）检查升级要求
[oracle@rh55 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@rh55 admin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/rdbms/admin
依据脚本
[oracle@rh55 admin]$ ll utlu112i*
-rw-r--r-- 1 oracle oinstall 185744 Jul 23 2009 utlu112i.sql 此脚本的信息很重要，它给出了升级前的一些要求
[oracle@rh55 admin]$ cp utlu112i.sql /tmp 备份一下用于后面的分析
要执行utlu112i.sql脚本，需要在10g的环境下
[oracle@rh55 ~]$ vi .bash_profile
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
#export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
[oracle@rh55 ~]$ source .bash_profile
[oracle@rh55 ~]$ env |grep ORA
ORACLE_SID=prod
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
[oracle@rh55 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 5 18:54:59 2016
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> @/tmp/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 05-05-2016 18:59:21
**********************************************************************
Database:
**********************************************************************
--> name: PROD
--> version: 10.2.0.4.0
--> compatible: 10.2.0.4.0
--> blocksize: 8192
--> platform: Linux IA (32-bit)
--> timezone file: V4
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 755 MB
.... AUTOEXTEND additional space required: 245 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 346 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 493 MB
.... AUTOEXTEND additional space required: 203 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.... AUTOEXTEND additional space required: 30 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 63 MB
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No update parameter changes are required.
**********************************************************************
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]
**********************************************************************
--> background_dump_dest 11.1 DEPRECATED replaced by
"diagnostic_dest"
--> user_dump_dest 11.1 DEPRECATED replaced by
"diagnostic_dest"
--> core_dump_dest 11.1 DEPRECATED replaced by
"diagnostic_dest"
**********************************************************************
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] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [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
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 11. 时区文件版本必须是11但我们现在的版本是4，可以在数据库升级完成后再升级这个文件
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.4.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains schemas with stale optimizer statistics. 收集统计信息
.... Refer to the Upgrade Guide for instructions to update
.... schema statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... WMSYS
.... OLAPSYS
.... SYSMAN
.... CTXSYS
.... XDB
.... MDSYS
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 MDSYS has 6 INVALID objects.
.... USER PUBLIC has 22 INVALID objects.
.... USER EXFSYS has 1 INVALID objects.
.... USER XDB has 1 INVALID objects.
.... USER SYS has 53 INVALID objects.
WARNING: --> Database contains schemas with objects dependent on network
packages.
.... Refer to the Upgrade Guide for instructions to configure Network ACLs.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
WARNING:--> recycle bin in use.
.... Your recycle bin is turned on and it contains
.... 53 object(s). It is REQUIRED
.... that the recycle bin is empty prior to upgrading
.... your database.
.... The command: PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
PL/SQL procedure successfully completed.
2）生成analyze分析脚本
[oracle@rh55 ~]$ vi myanalyze.sql
set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool /home/oracle/analyze.sql
SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
FROM dba_clusters
WHERE owner='SYS'
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES';
spool off
需要删除外部表，因为外部表不可以analyze
10:09:45 SYS@ prod>select owner,table_name from dba_external_tables;
OWNER TABLE_NAME
------------------------------ ------------------------------
SH SALES_TRANSACTIONS_EXT SH用户的外部表不需要analyze
SYS ALERT_LOG
SQL> select table_name from dba_tables where table_name='ALERT_LOG';
TABLE_NAME
------------------------------
ALERT_LOG
SQL> drop table alert_log;
Table dropped.
SQL> @/home/oracle/myanalyze.sql 执行这个脚本，生成analyze.sql
[oracle@rh55 ~]$ ll analyze.sql
-rw-r--r-- 1 oracle oinstall 86032 May 5 19:28 analyze.sql 这是生成的analyze语句的脚本
- 执行analyze分析脚本
SQL> @/home/oracle/analyze.sql
会有一些报错，可忽略。
4）检查并重编无效组件：
SQL> col comp_name for a50;
SQL> select substr(comp_name,1,40) comp_name,status,substr(version,1,10) version from dba_registry order by comp_name; 查看安装的组件是否都有效
SQL> col object_name for a40;
SQL>select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type,status
from dba_objects where status='INVALID' order by owner,object_type; 查看那些数据库对象是无效的
SQL> @?/rdbms/admin/utlrp.sql 重新编译无效的组件和对象
运行后再次检查无效的组件和对象
- 清空回收站
SQL> select count(*) from dba_recyclebin;
53
SQL> purge dba_recyclebin;
SQL>
- 使用DBUA升级
需要在11g的环境下做，但10g的库不要关闭
[oracle@rh55 ~]$ vi .bash_profile
#export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
[oracle@rh55 ~]$ source .bash_profile
[oracle@rh55 ~]$ env |grep ORA
ORACLE_SID=prod
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@rh55 ~]$
10g 的库不要关闭，现在要收集库信息
仍有一些告警，忽略。
这个过程较长，约1小时。
[oracle@rh55 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu May 5 21:33:53 2016
SQL> select * from v$version;
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select * from scott.dept;
- 升级时区文件
timezone file:时区文件 ----时区文件以及字符集用来保证国际化访问
有一些操作如可表空间传输，要注意数据库版本，时区版本，需要对时区进行升级
1）查询时区文件状态
SQL> select version from v$timezone_file; 查询时区版本
VERSION
----------
4
11.2.0.1.0---最大时区版本支持到11
11.2.0.3.0---最大时区版本支持到14
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; 检查时区情况
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> EXEC DBMS_DST.BEGIN_PREPARE(14); 预升级，测试一下
BEGIN DBMS_DST.BEGIN_PREPARE(14); END;
*
ERROR at line 1:
ORA-30094: failed to find the time zone data file for version 14 in $ORACLE_HOME/oracore/zoneinfo
此目录中有11.2.0.1.0版的所有时区文件
ORA-06512: at "SYS.DBMS_DST", line 57
ORA-06512: at "SYS.DBMS_DST", line 1076
ORA-06512: at line 1
[oracle@rh55 ~]$ cd $ORACLE_HOME/oracore/zoneinfo
[oracle@rh55 zoneinfo]$ ll 看到最大时区文件支持到11
SQL> EXEC DBMS_DST.BEGIN_PREPARE(11);
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 11
DST_UPGRADE_STATE PREPARE
如果要升级到更高版本需要拷贝时区文件到相应目录
2）查找与升级时区有影响的表
BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/
没有任何信息，不需要做下面的内容
TRUNCATE TABLE SYS.DST$TRIGGER_TABLE; 如果有，就删除有影响的表
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;
SELECT * FROM sys.dst$affected_tables;
SELECT * FROM sys.dst$error_table;
SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';
SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');
SQL> exec dbms_dst.end_prepare; 停止时区预备阶段
PL/SQL procedure successfully completed.
这个和EXEC DBMS_DST.BEGIN_PREPARE()是一对
3）开始升级时区文件
SQL> startup upgrade
SQL>EXEC DBMS_DST.BEGIN_UPGRADE(11); 一定要在upgrade模式下做。
PL/SQL procedure successfully completed.
SQL>SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION 11
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE UPGRADE
4）执行DBMS_DST包，升级相关的表
SQL> startup
SQL>var numfail number;
SQL>
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
5）执行DBMS_DST包，结束升级
SQL> var fail number
SQL>
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
SQL>SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; 检验是否升级成功
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION 11
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> select version from v$timezone_file;
VERSION
----------
11
SQL>show parameter comp 检查完所有应用后，修改兼容性参数
SQL>alter system set compatible='11.2.0.1.0' scope=spfile;
SQL>startup force;
6）验证应用在新版本下是否工作正常
7）可选性的删除老版本软件
[root@rh55 ~]# cd /u01/app/oracle/product
[root@rh55 product]# ll
total 8
drwxrwx--- 3 oracle oinstall 4096 Jul 6 2012 10.2.0
drwxr-xr-x 3 oracle oinstall 4096 May 5 18:19 11.2.0
[root@rh55 product]# rm -rf 10.2.0
[root@rh55 product]#
2.5滚动升级介绍
对于7*24小时需要进行不停机的滚动升级
利用DG逻辑备库进行滚动升级
通过数据迁移进行滚动升级
注意RAC的升级，首先要升级CRS，将业务转移到另一个节点，再升级另一个节点的CRS，可以实现滚动升级
然后再升级数据库软件，最后升级数据库
-----完-----
Shell 编程--课堂笔记
- ：基础实践
创建一个目录/practice/shell存放所有练习的shell脚本
练习1
1）题目：数值运算（加法和除法）（practice1.sh）
2）脚本
[root@cuug shell]# vi practice1.sh
#!/bin/bash
a=10
b=20
c=30
value1=`expr $a + $b + $c`
echo "value1 = $value1"
value2=`expr $c / $b`
echo "value2 = $value2"
#shell中的除法做的是整除
- 测试
[root@cuug shell]# sh practice1.sh
value1 = 60
value2 = 1
练习2
- 题目：分支结构if（比较两个数值）（practice2.sh）
- 脚本
[root@cuug shell]# vi practice2.sh
if [ $# -ne 2 ]; then
#$#就是一共有的参数的个数
echo "Not enough parameters"
exit 1
fi
if [ $1 -eq $2 ]; then
echo "$1 = $2"
elif [ $1 -lt $2 ]; then
echo "$1 < $2"
elif [ $1 -gt $2 ]; then
echo "$1 > $2"
fi
- 测试
[root@cuug shell]# sh practice2.sh
Not enough parameters
[root@cuug shell]# sh practice2.sh 2 1
2 > 1
[root@cuug shell]# sh practice2.sh 1 2
1 < 2
[root@cuug shell]# sh practice2.sh 2 2
2 = 2
练习3
1）题目：读取用户输入（practice3.sh）
2）脚本
[root@cuug shell]# vi paratice3.sh
#!/bin/bash
echo -e "p1 = \c"
#-e 和后面\c配合，代表转移符。表示光标停在此处
read p1
echo -e "p2 = \c"
read p2
echo -e "p3 = \c"
read p3
echo "p1 = $p1, p2 = $p2, p3 = $p3"
- 验证
[root@cuug shell]# sh practice3.sh
p1 = 1
p2 = 2
p3 = 3
p1 = 1, p2 = 2, p3 = 3
练习4
1）题目：参数的shift（对所有的位置参数求和）（practice4.sh）
2）脚本
[root@cuug shell]# vi practice4.sh
if [ $# -le 0 ]; then
echo "Not enough parameters!"
exit 1
fi
sum=0
while [ $# -gt 0 ]
do
sum=`expr $sum + $1`
shift
done
echo "sum = $sum"
- 验证
[root@cuug shell]# sh practice4.sh
Not enough parameters!
[root@cuug shell]# sh practice4.sh 1
sum = 1
[root@cuug shell]# sh practice4.sh 1 2
sum = 3
[root@cuug shell]# sh practice4.sh 1 2 3 4 5 6 7 8 9 10 11
sum = 66
练习5
1）题目：函数测试（practice5.sh）当在一个脚本中重复使用一段代码时，可以将该段代码抽象成函数，随时调用
2）脚本
[root@cuug shell]#vi practice5.sh
#!/bin/bash
num=123
echo "num = $num"
#先显示一下外部的num=123
#funcation definition
#本地定义一个函数，这个函数没有形参
fun1()
(
num1=321
num=`expr $num + 100`
echo "num = $num"
#$num会将外部的123传入进来，使得函数体内num=223
)
#calling function
fun1
echo "num = $num"
echo "num1 = $num1"
#函数调用完后，外部的num还是123，因为函数体内的变量是局部变量。
#function definition
fun2()
(
echo $1
echo $2
)
#calling function
fun2 para1 para2
#fun2虽然没有形参，但也可以在此定义实参para1 para2，
- 验证
[root@cuug shell]# sh practice5.sh
num = 123
num = 223
num = 123
para1
para2
练习6
1）题目：环境变量（practice6.sh）
2）脚本
[root@rh55 shell]# vi practice6.sh
#!/bin/bash
var1="abcdefg"
var2=12345
echo "var1 = $var1"
echo "var2 = $var2"
echo $HOME
#$HOME是当前登录用户的家目录
echo $PATH
- 验证
[root@rh55 shell]# sh practice6.sh
var1 = abcdefg
var2 = 12345
/root
/usr/lib/qt-3.3/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
练习7
1）题目：位置变量（practice7.sh）
2）脚本
[root@cuug shell]#vi proctice7.sh
#!/bin/bash
echo "\$0 is $0"
echo "\$1 is $1"
echo "\$2 is $2"
echo "\$3 is $3"
echo "\$# is $#"
echo "PID is: $$"
echo "Content is $*"
- 验证
[root@cuug shell]# sh practice7.sh a b c
$0 is practice7.sh
$1 is a
$2 is b
$3 is c
$# is 3
PID is: 3139
"Content is a b c"
练习8
1）题目：文件类型判断（practice8.sh）
2）基础知识
-b file 若文件存在且是一个块设备文件，则为真
-c file 若文件存在且是一个字符设备文件，则为真
-o 逻辑或-
-a 逻辑与
-d file 若文件存在且是一个目录，则为真
-f file 若文件存在且是一个常规文件，则为真
-x file 若文件存在且可执行，则为真
-L file 测试是否为符号连接（Link）文件
3）脚本
[root@cuug shell]# vi proctice8.sh
#!/bin/bash
echo -e "Please input a filename: \c"
read filename
if [ -d $filename ]; then
echo "The $filename is a directory"
elif [ -L $filename ]; then
echo "The $filename is link file!"
elif [ -f $filename ]; then
echo "The $filename is a common file!"
elif [ -c $filename -o -b $filename ]; then
echo "Thie $filename is a device file!"
else
echo "This $filename is unknow!"
fi
- 验证
[root@cuug shell]# sh practice8.sh
Please input a filename: /etc
The /etc is a directory
[root@cuug shell]#sh practice8.sh
Please input a filename: /practice/shell/practice8.sh
The /practice/shell/practice8.sh is a common file!
[root@cuug shell]# sh practice8.sh
Please input a filename: /dev/cdrom
The /dev/cdrom is link file!
练习9
1）题目：while循环，输出1--5这5个数的平方（practice9.sh）
2）脚本
[root@cuug shell]#vi practice9.sh
#!/bin/bash
num=1
while [ $num -le 5 ]
do
square=`expr $num \* $num`
echo $square
num=`expr $num + 1`
done
验证
[root@cuug shell]# sh practice9.sh
1
4
9
16
25
练习10
1）题目：for循环，输出所有参数的和（practice10.sh）
2）脚本
[root@cuug shell]# vi practice10.sh
#!/bin/bash
sum=0
for i in $*
do
sum=`expr $sum + $i`
done
echo "sum =$sum"
- 验证
[root@cuug shell]#sh practice10.sh 1 2 3 4 5 6 7 8 9 10
sum =55
练习11
1）题目：case分支结构（practice11.sh）（完成一个改进版，做成一个反复循环，增加exit选项）
2）脚本
[root@cuug shell]# vi practice11.sh
#!/bin/bash
flag=0
while [ $flag = 0 ]
do
echo "Please select your operation"
echo "*****************************"
echo "1------------copy"
echo "2------------delete"
echo "3------------backup"
echo "0------------exit"
echo "*****************************"
read operation
case $operation in
1)
echo "Your operation is copy!"
;;
2)
echo "Your operation is delete!"
;;
3)
echo "Your operation is backup!"
;;
0) flag=1
;;
*)
echo "Your operation is invalid!"
;;
esac
done
- 验证
[root@cuug shell]#sh practice12.sh
Please select your operation
*****************************
1------------copy
2------------delete
3------------backup
0------------exit
*****************************
2
Your operation is delete!
Please select your operation
*****************************
1------------copy
2------------delete
3------------backup
0------------exit
*****************************
0
练习12
1）题目：输入一个目录，列出其中的有可执行权限文件
2）脚本
root@bjcuug shell]# vi practice12.sh
#!/bin/bash
if [ $# -gt 1 ]; then
echo "Too many parameters!"
exit 1
fi
if [ $# -eq 0 ]; then
echo "Too few parameters!"
exit 1
fi
if [ ! -d $1 ]; then
echo "Usage: $0 directory"
exit 1
fi
cd $1
for i in `ls $1`
do
if [ -x $i -a ! -d $i ]; then
echo "$i"
fi
done
- 测试
[root@cuug shell]# ll
total 48
drwxr-xr-x 2 root root 4096 May 30 15:18 abc
-rw-r--r-- 1 root root 79 May 30 14:58 practice10.sh
-rw-r--r-- 1 root root 482 May 30 15:12 practice11.sh
-rwxr--r-- 1 root root 126 May 30 10:02 practice1.sh
-rwxr--r-- 1 root root 210 May 30 10:27 practice2.sh
-rwxr--r-- 1 root root 151 May 30 10:38 practice3.sh
-rw-r--r-- 1 root root 49 May 30 11:05 practice4.sh
-rw-r--r-- 1 root root 421 May 30 11:59 practice5.sh
-rw-r--r-- 1 root root 102 May 30 14:19 practice6.sh
-rw-r--r-- 1 root root 210 May 30 14:23 practice7.sh
-rw-r--r-- 1 root root 397 May 30 14:46 practice8.sh
-rw-r--r-- 1 root root 113 May 30 14:53 practice9.sh
[root@cuug shell]# sh practice12.sh fefef
Usage: practice12.sh directory
[root@cuug shell]# sh practice12.sh /dev /tmp
Too many parameters!
[root@cuug shell]# sh practice12.sh
Too few parameters!
[root@cuug shell]# sh practice12.sh /etc/hosts
Usage: practice12.sh directory
[root@cuug shell]# sh practice12.sh /shell
practice1.sh
practice2.sh
practice3.sh
- ：高级应用
练习1：配置邮件服务器
1）查看相关服务包
[root@cuug ~]# rpm -qa |grep sendmail 查看邮件服务发生包
sendmail-8.13.8-8.1.el5_7
[root@cuug ~]# rpm -qa |grep dovecot 查看邮件服务接收包
2）缺省安装的包不全，删掉重装
[root@cuug ~]# rpm -e sendmail-8.13.8-8.1.el5_7
error: Failed dependencies:
/usr/sbin/sendmail is needed by (installed) redhat-lsb-4.0-2.1.4.el5.i386
smtpdaemon is needed by (installed) mdadm-2.6.9-5.el5.i386
smtpdaemon is needed by (installed) fetchmail-6.3.6-4.el5.i386
smtpdaemon is needed by (installed) mutt-1.4.2.2-6.el5.i386
有依赖关系，加个参数再删
[root@cuug ~]#rpm -e sendmail --nodeps
使用yum安装，配置文件已经做好了，见/etc/yum.repos.d/yum.repo
[root@cuug ~]#mount /dev/cdrom /media
mount: block device /dev/cdrom is write-protected, mounting read-only
[root@cuug ~]#yum install sendmail* -y
[root@cuug ~]# yum install dovecot* -y
[root@cuug ~]#rpm -qa |grep sendmail
sendmail-devel-8.13.8-8.1.el5_7
sendmail-cf-8.13.8-8.1.el5_7
sendmail-doc-8.13.8-8.1.el5_7
sendmail-8.13.8-8.1.el5_7
[root@cuug ~]# rpm -qa |grep dovecot
dovecot-1.0.7-8.el5_9.1
3）修改sendmail和dovecot的配置文件
[root@cuug ~]# cd /etc/mail
[root@cuug mail]# ll sendmail*
-rw-r--r-- 1 root root 58296 May 11 22:13 sendmail.cf
-rw-r--r-- 1 root root 58296 May 11 22:11 sendmail.mc
[root@cuug mail]# m4 sendmail.cf > sendmail.mc
M4是一个宏命令，将输入拷贝到输出，与cp不同，宏可以是内嵌的也可以是用户调用的，除了可以展开宏，M4还有一些内嵌函数，用来引用文件，测试Unix
[root@cuug mail]#vi sendmail.mc
使用/127.0.0.1找到下面行
O DaemonPortOptions=Port=smtp,Addr=127.0.0.1, Name=MTA
改为
O DaemonPortOptions=Port=smtp,Addr=0.0.0.0, Name=MTA
这个意思是发送邮件可以让任何客户端接收
4）修改主机名以域名命名
邮件服务器主机解析名称通常使用域名命名方法，这里要将主机名改一下
[root@cuug mail]# vi /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost cuug.net
192.168.3.88 cuug.net
[root@cuug mail]# vi /etc/sysconfig/network
NETWORKING=yes
NETWORKING_IPV6=no
HOSTNAME=cuug.net
- 测试邮件服务器
发送端启动 sendmail服务
[root@cuug mail]# service sendmail start
Starting sendmail: [ OK ]
Starting sm-client: [ OK ]
[root@cuug mail]# service sendmail status
sendmail (pid 2541) is running...
[root@cuug mail]# netstat -an |grep :25
tcp 0 0 0.0.0.0:25 0.0.0.0:* LISTEN 5
Sendmail服务对应的端口是25，状态是LISTEN，说明sendmail已经开启了
客户端接收dovecot服务
[root@cuug mail]# vi /etc/dovecot.conf
# If you only want to use dovecot-auth, you can set this to "none".
protocols = imap imaps pop3 pop3s 将这句前面的#号去掉
[root@cuug mail]# service dovecot start
Starting Dovecot Imap: [ OK ]
[root@cuug ~]# service dovecot status
dovecot (pid 2656) is running...
[root@cuug mail]# netstat -an |grep 110
tcp 0 0 0.0.0.0:110 0.0.0.0:* LISTEN
unix 3 [ ] STREAM CONNECTED 9110
[root@cuug mail]# netstat -an |grep 143
tcp 0 0 0.0.0.0:143 0.0.0.0:* LISTEN
测试发送邮件
[root@cuug mail]# mail -s "root's mail" oracle root 发送邮件给oracle，“root’s mail”是邮件名称,--s是subject的意思
hello oracle! Ctl+D结束
Cc: 回车结束
[root@cuug mail]# su - oracle
[oracle@cuug ~]$ mail
Mail version 8.1 6/6/93. Type ? for help.
"/var/spool/mail/oracle": 1 message 1 new
>N 1 root@localhost.local Wed May 11 22:27 16/669 "root's mail"
& 1 根据编号选，看邮件内容
Message 1:
From root@localhost.localdomain Wed May 11 22:27:57 2016
Date: Wed, 11 May 2016 22:27:57 +0800
From: root <root@localhost.localdomain>
To: oracle@localhost.localdomain
Subject: root's mail
hello oracle!
& q回车退出
查看已阅读过的邮件
[oracle@cuug ~]$mail -f $HOME/mbox
练习2：监控数据库停机，发邮件
[oracle@cuug script]$vi chkinstance.sh
#!/bin/bash
ORACLE_BASE=/u01
ORACLE_HOME=$ORACLE_BASE/oracle
ORACLE_SID=prod
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH
###################################################################
## chkinstance.sh ##
###################################################################
ORATAB=/etc/oratab
muser='root,oracle'
echo "`date` "
echo "Oracle Database(s) Status `hostname`"
db=`egrep -i ":Y|:N" $ORATAB | awk -F: '{print $1}' | grep -v "^#" | grep -v "^*"`
#这一步完成了有几个库的删选
pslist="`ps -ef | grep pmon`"
for i in $db
do
echo "$pslist" | grep "ora_pmon_$i" > /dev/null 2>&1
#执行成功$?为0，否则$?为非0。/dev/null表示空，&1表示后台运行不显示输出
if [ $? -ne 0 ]; then
echo "Oracle Instance - $i: Down"|tee mail.txt
mail -s "Instance is down" $muser < mail.txt
#mail.txt里的内容反送到$muser
else
echo "Oracle Instance - $i: Up"
fi
done
[oracle@cuug script]$ sh chkinstance.sh
Sun May 29 23:24:14 CST 2016
Oracle Instance - prod: Down
[oracle@cuug script]$ mail
Mail version 8.1 6/6/93. Type ? for help.
"/var/spool/mail/oracle": 1 message 1 new
>N 1 oracle@localhost.loc Sun May 29 23:24 16/672 "Instance is down"
&
练习3：监控磁盘空间，发邮件
[oracle@cuug script]$vi chkarch.sh
#!/bin/bash
ORACLE_BASE=/u01
ORACLE_HOME=$ORACLE_BASE/oracle
ORACLE_SID=prod
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH
######################################################################
## clean_arch.sh ##
######################################################################
fsdsk="/dev/sda2"
muser="root,oracle"
# $ df -k | grep $fsdsk
#Filesystem kbytes used avail capacity Mounted on
#/dev/vx/dsk/proddg/archive 71123968 30210248 40594232 43% /dsk4
du -sh /u01/arch > dfk.result
archive_capacity=`cat dfk.result|awk -F" " '{ print $1 }'`
#archive_capacity=`cat dfk.result|awk -F" " '{ print $4 }'|sed -e 's/%//'`
if [ "$archive_capacity" -gt 1500 ]; then
echo "Need Clear Arch Filesystem" >mtxt
mailx -s "Arch filesystem is full" $muser <mtxt
# try one of the following option depend on your need
arch_dir=/u01/arch
for name in `find $arch_dir -type f -mtime +2`
do
mv $name /home/oracle/backup
done
else
echo 'Filesystem capacity is free ! '
fi
rm mtxt >/dev/null 2>&1
rm dfk.result >/dev/null 2>&1
----完----
