oracle 11.2.0.3.0升级到11.2.03.9

来源：互联网发布：bim软件安装教程编辑：程序博客网时间：2024/05/27 02:26

环境是：单实例，操作系统层面做了HA

目录

1 引言...3

2 变更目标...3

3 准备工作...3

3.1 创建DIRECTORY..3

3.2 赋权限...3

3.3 备份数据库软件...3

3.4 解压optach.3

3.5 验证补丁是否可行...3

3.6 统计无效对象...5

3.7 统计各对象情况...5

4 停机迁移...6

4.1 停数据库和监听...6

4.2 备份数据库...6

4.3 安装补丁...7

4.4 升级sqlfiles7

4.5 修改监听...8

4.6 启动监听...8

4.7 查看日志...8

4.8 升级rman.8

5 升级后的数据处理和验证...8

5.1 检查数据库版本...8

5.2 检查数据库状态...9

5.3 监听状态...9

5.4 查看是否有无效对象，进行重新编译...9

5.5 各对象情况...9

6 变更实施人员安排...9

7 迁移操作计划表...9

8 回滚方案...10

8.1 关闭数据库和监听...10

8.2 回滚...10

8.3 数据库回滚...10

1 引言

本文档的目的是描述XXXXXX数据库数据库具体升级方案。

2 变更目标

本次XXXXXX数据库升级的目标是将运行在1xXXXX上的Oracle 11.2.0.3.0 升级到11.2.0.3.9。

3 准备工作

3.1 创建DIRECTORY

----------已经创建

cd/oradata

mkdirdatabak

createdirectory dir_dpas'/oradata/databak';

3.2 赋权限

--查看目录及权限

SELECT privilege, directory_name,DIRECTORY_PATH FROM user_tab_privs t, all_directories d

WHERE t.table_name(+) = d.directory_name ORDERBY 2, 1;

3.3备份数据库软件

主机

tar -zcvf /oradata/databak/oraclehome.tar.gz /oracle/product/11.2

备机

tar -zcvf /home/oracle/oraclehome.tar.gz /oracle/product/11.2

3.4解压optach

分别在主机和备机上执行

把opatch解压到/home/oracle下

unzip p6880880_112000_Linux-x86-64.zip

unzip p17540582_112030_Linux-x86-64.zip

3.5验证补丁是否可行

------------------提前验证

分别在主机备机上执行

cd /home/oracle/17540582

/home/oracle/OPatch/opatch prereqCheckConflictAgainstOHWithDetail -ph ./

[oracle@ahdb1 ~]$ cd /home/oracle/17540582

[oracle@ahdb1 17540582]$/home/home/oracle/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

-bash: /home/home/oracle/OPatch/opatch: 没有那个文件或目录

[oracle@ahdb1 17540582]$/home/oracle/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 11.2.0.3.6

PREREQ session

Oracle Home : /oracle/product/11.2

Central Inventory : /oracle/oraInventory

from : /oracle/product/11.2/oraInst.loc

OPatch version : 11.2.0.3.6

OUI version : 11.2.0.3.0

Log file location :/oracle/product/11.2/cfgtoollogs/opatch/opatch2014-06-23_18-59-20PM_1.log

Invoking prereq"checkconflictagainstohwithdetail"

Prereq"checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

在备机上验证

[oracle@ahdb2 ~]$ cd 17540582/

[oracle@ahdb2 17540582]$ ls

13343438 13923374 14727310 16619892 17540582 README.html

13696216 14275605 16056266 16902043 patchmd.xml README.txt

[oracle@ahdb2 17540582]$ /home/oracle/OPatch/opatchprereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 11.2.0.3.6

PREREQ session

Oracle Home : /oracle/product/11.2

Central Inventory : /oracle/oraInventory

from : /oracle/product/11.2/oraInst.loc

OPatch version : 11.2.0.3.6

OUI version : 11.2.0.3.0

Log file location :/oracle/product/11.2/cfgtoollogs/opatch/opatch2014-06-23_19-08-15PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq"checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

3.6统计无效对象

create table t_object_hsr as

select OBJECT_TYPE,OBJECT_NAME

from USER_objects

where status = 'INVALID' and object_type in('VIEW','FUNCTION','PROCEDURE','TRIGGER');

3.7统计各对象情况

select owner,count(*)from dba_objectsgroupby owner

4 停机迁移

4.1 备份数据库

请确认应用在用的用户AHEBPP,BTUPAYPROVINCE,OTA

expdphr/hr DIRECTORY=dpump_dir1 LOGFILE=parallel_export.log JOB_NAME=par4_jobDUMPFILE=par_exp%u.dmp P

nohup expdp \"/ as sysdba\" schemas=OTA,AHEBPP,BTUPAYPROVINCE dumpfile = par_exp%u.dmplogfile=par_exp%u.logdirectory=dir_dpparallel=15&

selectowner,count(*)fromdba_objectsgroupbyowner

这些用户都是正在使用的

OWNER COUNT(*)

------------------------------ ----------

PATROL 88

BTUPAYPROVINCE 152

PUBLIC 3396

OUTLN 10

SYSTEM 563

ORACLE_OCM 8

OTA 30

DBSNMP 70

APPQOSSYS 5

SYS 9715

WMSYS 330

AHEBPP 322

SQL>select username from dba_users;

USERNAME

------------------------------

SYS

PATROL

HUATENG

BTUPAYPROVINCE

AHEBPP

OTA

SYSTEM

OUTLN

DBSNMP

WMSYS

APPQOSSYS

DIP

ORACLE_OCM

4.2 停数据库和监听

shutdownimmediate

listenerstop

4.3 安装补丁

分别在主机备机上执行

cd/home/oracle/17540582

/home/oracle/OPatch/opatch apply

升级备机

[oracle@ahdb217540582]$ sqlplus / as sysdba

SQL*Plus:Release 11.2.0.3.0 Production on Mon Jun 23 23:13:45 2014

Connectedto an idle instance.

SQL>exit

Disconnected

[oracle@ahdb217540582]$ /home/oracle/OPatch/opatchapply

OracleInterim Patch Installer version 11.2.0.3.6

OracleHome : /oracle/product/11.2

CentralInventory : /oracle/oraInventory

from : /oracle/product/11.2/oraInst.loc

OPatchversion : 11.2.0.3.6

OUIversion : 11.2.0.3.0

Logfile location :/oracle/product/11.2/cfgtoollogs/opatch/opatch2014-06-23_23-13-53PM_1.log

Verifyingenvironment and performing prerequisite checks...

OPatchcontinues with these patches: 16619892 16902043 17540582

Doyou want to proceed? [y|n]

UserResponded with: Y

Allchecks passed.

Pleaseshutdown Oracle instances running out of this ORACLE_HOME on the local system.

(OracleHome = '/oracle/product/11.2')

Isthe local system ready for patching? [y|n]

UserResponded with: Y

Backingup files...

Applyingsub-patch '16619892' to OH '/oracle/product/11.2'

ApplySession:Optional component(s) [ oracle.precomp.lang, 11.2.0.3.0 ] not present in the Oracle Home or a higherversion is found.

Patchingcomponent oracle.marvel, 11.2.0.3.0...

Patchingcomponent oracle.precomp.common, 11.2.0.3.0...

Patchingcomponent oracle.rdbms, 11.2.0.3.0...

Patchingcomponent oracle.rdbms.rman, 11.2.0.3.0...

Patchingcomponent oracle.rdbms.rsf, 11.2.0.3.0...

Patchingcomponent oracle.sysman.agent, 10.2.0.4.3...

Patchingcomponent oracle.sysman.console.db, 11.2.0.3.0...

Patchingcomponent oracle.sysman.repository.core, 10.2.0.4.4...

Patchingcomponent oracle.xdk, 11.2.0.3.0...

Patchingcomponent oracle.xdk.parser.java, 11.2.0.3.0...

Patchingcomponent oracle.xdk.rsf, 11.2.0.3.0...

Verifyingthe update...

Applying sub-patch '16902043' to OH'/oracle/product/11.2'

ApplySession:Optional component(s) [ oracle.idm.oid, 11.2.0.3.0 ] not present in the Oracle Home or a higherversion is found.

Patchingcomponent oracle.ldap.rsf, 11.2.0.3.0...

Patchingcomponent oracle.ldap.rsf.ic, 11.2.0.3.0...

Patchingcomponent oracle.owb.rsf, 11.2.0.3.0...

Patchingcomponent oracle.rdbms, 11.2.0.3.0...

Patchingcomponent oracle.rdbms.rsf, 11.2.0.3.0...

Patchingcomponent oracle.sysman.console.db, 11.2.0.3.0...

Verifyingthe update...

Applyingsub-patch '17540582' to OH '/oracle/product/11.2'

ApplySession:Optional component(s) [ oracle.precomp.lang, 11.2.0.3.0 ] not present in the Oracle Home or a higherversion is found.

Patchingcomponent oracle.rdbms, 11.2.0.3.0...

Patchingcomponent oracle.rdbms.rsf, 11.2.0.3.0...

Patchingcomponent oracle.sdo, 11.2.0.3.0...

Patchingcomponent oracle.ldap.rsf, 11.2.0.3.0...

Patchingcomponent oracle.precomp.common, 11.2.0.3.0...

Patchingcomponent oracle.ordim.client, 11.2.0.3.0...

Patchingcomponent oracle.rdbms.util, 11.2.0.3.0...

Patchingcomponent oracle.rdbms.dbscripts, 11.2.0.3.0...

Patchingcomponent oracle.sdo.locator, 11.2.0.3.0...

Patchingcomponent oracle.rdbms.rman, 11.2.0.3.0...

Patchingcomponent oracle.ordim.jai, 11.2.0.3.0...

Verifyingthe update...

Composite patch 17540582 successfullyapplied.

Logfile location: /oracle/product/11.2/cfgtoollogs/opatch/opatch2014-06-23_23-13-53PM_1.log

OPatchsucceeded.

升级主机

[oracle@ahdb1~]$ cd 17540582/

[oracle@ahdb117540582]$ ls

13343438 13923374 14727310 16619892 17540582 README.html

13696216 14275605 16056266 16902043 patchmd.xml README.txt

[oracle@ahdb117540582]$ /home/oracle/OPatch/opatch apply

OracleInterim Patch Installer version 11.2.0.3.6

OracleHome : /oracle/product/11.2

CentralInventory : /oracle/oraInventory

from : /oracle/product/11.2/oraInst.loc

OPatchversion : 11.2.0.3.6

OUIversion : 11.2.0.3.0

Logfile location :/oracle/product/11.2/cfgtoollogs/opatch/opatch2014-06-23_23-56-41PM_1.log

Verifyingenvironment and performing prerequisite checks...

OPatchcontinues with these patches: 16619892 16902043 17540582

Doyou want to proceed? [y|n]

UserResponded with: Y

Allchecks passed.

Pleaseshutdown Oracle instances running out of this ORACLE_HOME on the local system.

(OracleHome = '/oracle/product/11.2')

Isthe local system ready for patching? [y|n]

UserResponded with: Y

Backingup files...

Applyingsub-patch '16619892' to OH '/oracle/product/11.2'

ApplySession:Optional component(s) [ oracle.precomp.lang, 11.2.0.3.0 ] not present in the Oracle Home or a higherversion is found.

Patchingcomponent oracle.marvel, 11.2.0.3.0...

Patchingcomponent oracle.precomp.common, 11.2.0.3.0...

Patchingcomponent oracle.rdbms, 11.2.0.3.0...

Patchingcomponent oracle.rdbms.rman, 11.2.0.3.0...

Patchingcomponent oracle.rdbms.rsf, 11.2.0.3.0...

Patchingcomponent oracle.sysman.agent, 10.2.0.4.3...

Patchingcomponent oracle.sysman.console.db, 11.2.0.3.0...

Patchingcomponent oracle.sysman.repository.core, 10.2.0.4.4...

Patchingcomponent oracle.xdk, 11.2.0.3.0...

Patchingcomponent oracle.xdk.parser.java, 11.2.0.3.0...

Patchingcomponent oracle.xdk.rsf, 11.2.0.3.0...

Verifyingthe update...

Applyingsub-patch '16902043' to OH '/oracle/product/11.2'

ApplySession:Optional component(s) [ oracle.idm.oid, 11.2.0.3.0 ] not present in the Oracle Home or a higherversion is found.

Patchingcomponent oracle.ldap.rsf, 11.2.0.3.0...

Patchingcomponent oracle.ldap.rsf.ic, 11.2.0.3.0...

Patchingcomponent oracle.owb.rsf, 11.2.0.3.0...

Patchingcomponent oracle.rdbms, 11.2.0.3.0...

Patchingcomponent oracle.rdbms.rsf, 11.2.0.3.0...

Patchingcomponent oracle.sysman.console.db, 11.2.0.3.0...

Verifyingthe update...

Applying sub-patch '17540582' to OH'/oracle/product/11.2'

ApplySession:Optional component(s) [ oracle.precomp.lang, 11.2.0.3.0 ] not present in the Oracle Home or a higherversion is found.

Patchingcomponent oracle.rdbms, 11.2.0.3.0...

Patchingcomponent oracle.rdbms.rsf, 11.2.0.3.0...

Patchingcomponent oracle.sdo, 11.2.0.3.0...

Patchingcomponent oracle.ldap.rsf, 11.2.0.3.0...

Patchingcomponent oracle.precomp.common, 11.2.0.3.0...

Patchingcomponent oracle.ordim.client, 11.2.0.3.0...

Patchingcomponent oracle.rdbms.util, 11.2.0.3.0...

Patchingcomponent oracle.rdbms.dbscripts, 11.2.0.3.0...

Patchingcomponent oracle.sdo.locator, 11.2.0.3.0...

Patchingcomponent oracle.rdbms.rman, 11.2.0.3.0...

Patchingcomponent oracle.ordim.jai, 11.2.0.3.0...

Verifyingthe update...

Composite patch 17540582 successfullyapplied.

Logfile location: /oracle/product/11.2/cfgtoollogs/opatch/opatch2014-06-23_23-56-41PM_1.log

OPatchsucceeded.

4.4升级sql files

在主机上执行

[oracle@ahtest17540582]$ cd $ORACLE_HOME/rdbms/admin

[oracle@ahdb1admin]$ sqlplus / as sysdba

SQL*Plus:Release 11.2.0.3.0 Production on Tue Jun 24 00:00:34 2014

Connectedto an idle instance.

SQL>startup

ORACLEinstance started.

TotalSystem Global Area 2137886720 bytes

FixedSize 2230072 bytes

VariableSize 771754184 bytes

DatabaseBuffers 1342177280 bytes

RedoBuffers 21725184 bytes

Databasemounted.

SQL>@catbundle.sql psu apply

PL/SQLprocedure successfully completed.

Functioncreated.

PL/SQLprocedure successfully completed.

Generatingapply and rollback scripts...

Checkthe following file for errors:

/oradata/cfgtoollogs/catbundle/catbundle_PSU_ORCL_GENERATE_2014Jun24_00_01_40.log

Applyscript: /oracle/product/11.2/rdbms/admin/catbundle_PSU_ORCL_APPLY.sql

Rollbackscript: /oracle/product/11.2/rdbms/admin/catbundle_PSU_ORCL_ROLLBACK.sql

PL/SQLprocedure successfully completed.

Executingscript file...

SQL>COLUMN spool_file NEW_VALUE spool_file NOPRINT

SQL>SELECT '/oradata/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name ||'_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss','NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;

SQL>SPOOL &spool_file

SQL>exec sys.dbms_registry.set_session_namespace('SERVER')

PL/SQLprocedure successfully completed.

SQL>PROMPT Skipping EM Repository because it is not installed or versionsmismatch...

SkippingEM Repository because it is not installed or versions mismatch...

SQL>PROMPT Processing Oracle Database Packages and Types...

ProcessingOracle Database Packages and Types...

SQL>ALTER SESSION SET current_schema = sys;

Sessionaltered.

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

SQL>Rem

SQL>Rem $Header: emll/admin/scripts/execocm.sql /st_emll_11.2.0.3.0dbpsu/22013/02/05 22:41:20 davili Exp $

SQL>Rem

SQL>Rem execocm.sql

SQL>Rem

SQL>Rem NAME

SQL>Rem execocm.sql - EXECute OracleConfiguration Manager job.

SQL>Rem

SQL>Rem DESCRIPTION

SQL>Rem This script submits and runs thedatabase configuration collection

SQL>Rem job as part of databasecreation.

SQL>Rem

SQL>Rem NOTES

SQL>Rem Create directory object for useby the job to create the configuration

SQL>Rem file at.

SQL>Rem This script should be run whileconnected as "SYS".

SQL>Rem

SQL>Rem MODIFIED (MM/DD/YY)

SQL>Rem davili 02/04/13 - bug 16273291, update backportto most recent EMLL

SQL>Rem code

SQL>Rem davili 01/15/13 - Backport jsutton_bug-13561750

SQL>Rem jsutton 07/19/11 - Catch exceptions that pop up inupgrade path

SQL>Rem jsutton 07/11/11 - Fix for upgrade path

SQL>Rem jsutton 07/06/11 - XbranchMerge jsutton_bug-12710774from main

SQL>Rem jsutton 07/20/09 - Add priv grants for utl_inaddr

SQL>Rem glavash 08/20/08 - grant required prives to user

SQL>Rem dkapoor 07/31/07 - remove stats job

SQL>Rem dkapoor 05/04/07 - stop old job

SQL>Rem dkapoor 01/04/07 - drop job before creating one

SQL>Rem dkapoor 09/20/06 - give priv only if not given topublic

SQL>Rem dkapoor 09/13/06 - grant execute on dbms_scheduler

SQL>Rem dkapoor 07/26/06 - do not use define

SQL>Rem dkapoor 07/21/06 - use create_replace_dir

SQL>Rem dkapoor 06/06/06 - move directory creation afterinstalling the

SQL>Rem packages

SQL>Rem dkapoor 05/23/06 - Created

SQL>Rem

SQL>

SQL>DECLARE

2 l_vers v$instance.version%TYPE;

3 l_dirobj_priv_cnt NUMBER;

4 l_priv_cnt NUMBER;

5 l_comp_cnt NUMBER;

6 l_acl_count NUMBER;

7 l_acl_priv NUMBER;

8 l_acl_name VARCHAR2(4000);

10 BEGIN

11 BEGIN

12 select count(*) into l_priv_cnt from dba_tab_privs where

13 GRANTEE ='ORACLE_OCM' and TABLE_NAME='UTL_FILE' and

14 upper(PRIVILEGE) = 'EXECUTE';

15 IF l_priv_cnt = 0 THEN

16 -- Grant priv only if its not already given.

17 execute immediate 'GRANT EXECUTE ON SYS.UTL_FILE TO ORACLE_OCM';

18 END IF;

20 select count(*) into l_priv_cnt from dba_tab_privs where

21 GRANTEE ='ORACLE_OCM' and TABLE_NAME='DBMS_SCHEDULER' and

22 upper(PRIVILEGE) = 'EXECUTE';

23 IF l_priv_cnt = 0 THEN

24 -- Grant priv only if its not given.

25 execute immediate 'GRANT EXECUTE ON SYS.DBMS_SCHEDULER TO ORACLE_OCM';

26 END IF;

28 select count(*) into l_priv_cnt fromdba_tab_privs where

29 GRANTEE ='ORACLE_OCM' and TABLE_NAME='UTL_INADDR' and

30 upper(PRIVILEGE) = 'EXECUTE';

31 IF l_priv_cnt = 0 THEN

32 -- Grant priv only if its not given.

33 execute immediate 'GRANT EXECUTE ONSYS.UTL_INADDR TO ORACLE_OCM';

34 END IF;

36 -- need to set up ACL if DB version > 11

37 select LPAD(version,10,'0') into l_vers from v$instance;

38 -- Grant privilege to use UTL_INADDR via ACL if necessary

39 IF l_vers >= '11.0.0.0.0' THEN

40 -- check for XML DB installed

41 execute immediate 'select count(*) from dba_registry '||

42 'where COMP_NAME = ''Oracle XML Database'' and STATUS = ''VALID'''intol_comp_cnt ;

43 IF l_comp_cnt > 0 THEN

44 BEGIN

45 -- make sure DBA_NETWORK_ACLS view exists (may not in upgrade path)

46 execute immediate 'select count(*) from dba_objects where object_type=''VIEW''and object_name=''DBA_NETWORK_ACLS''' into l_comp_cnt;

47 IF l_comp_cnt > 0 THEN

48 -- check for ACL assigned tolocalhost

49 execute immediate 'selectcount(*) from dba_network_acls where host=''localhost''' into l_acl_count;

50 IF (l_acl_count = 0) THEN

51 -- create ACL and assign tolocalhost

52 execute immediate

53 'BEGIN '||

54 ' DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(''oracle-sysman-ocm-Resolve-Access.xml'',''OCM User Resolve Network Access using UTL_INADDR'', ''ORACLE_OCM'', TRUE,''resolve'');' ||

55 ' DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(''oracle-sysman-ocm-Resolve-Access.xml'',''localhost'');' ||

56 ' COMMIT;' ||

57 'END;';

58 ELSE

59 -- ACL for localhost exists

60 -- check for resolveprivilege for OCM user

61 execute immediate

62 'SELECT acl,DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, ''ORACLE_OCM'',''resolve'') ' ||

63 ' FROM dba_network_acls WHERE host =''localhost'''

64 INTO l_acl_name,l_acl_priv;

65 IF (l_acl_priv IS NULL ORl_acl_priv = 0) THEN

66 -- add resolve privilege

67 execute immediate

68 'BEGIN ' ||

69 ' DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('''||l_acl_name ||''', ''ORACLE_OCM'', TRUE, ''resolve'');' ||

70 ' COMMIT;' ||

71 'END;';

72 END IF;

73 END IF;

74 END IF;

75 EXCEPTION

76 WHEN OTHERS THEN NULL;

77 END;

78 END IF;

79 END IF;

81 -- Grant RESTRICTED SESSION

82 execute immediate 'GRANT RESTRICTED SESSION TO ORACLE_OCM';

84 ORACLE_OCM.MGMT_CONFIG_UTL.create_replace_dir_obj;

85 select count(*) into l_dirobj_priv_cnt from dba_tab_privs where GRANTEE='ORACLE_OCM' and TABLE_NAME='ORACLE_OCM_CONFIG_DIR' and upper(PRIVILEGE) ='READ';

86 IF l_dirobj_priv_cnt = 0 THEN

87 execute immediate 'GRANT READ ON DIRECTORY ORACLE_OCM_CONFIG_DIR TOORACLE_OCM';

88 END IF;

89 select count(*) into l_dirobj_priv_cnt from dba_tab_privs where GRANTEE='ORACLE_OCM' and TABLE_NAME='ORACLE_OCM_CONFIG_DIR' and upper(PRIVILEGE) ='WRITE';

90 IF l_dirobj_priv_cnt = 0 THEN

91 execute immediate 'GRANT WRITE ON DIRECTORY ORACLE_OCM_CONFIG_DIR TOORACLE_OCM';

92 END IF;

93 COMMIT;

94 EXCEPTION

95 WHEN OTHERS THEN

96 raise_application_error(-20007,SQLERRM);

97 END;

98 END;

99 /

PL/SQLprocedure successfully completed.

SQL>

SQL>-- remove old dba jobs, if exists

SQL>DECLARE

2 job_num NUMBER;

3 CURSOR job_cursor is

4 SELECT job

5 FROM dba_jobs

6 WHERE schema_user = 'ORACLE_OCM'

7 AND (what like 'ORACLE_OCM.MGMT_CONFIG.%'

8 OR what like 'ORACLE_OCM.MGMT_DB_LL_METRICS.%');

9 BEGIN

10 FOR r in job_cursor LOOP

11 sys.DBMS_IJOB.REMOVE(r.job);

12 COMMIT;

13 END LOOP;

14 END;

15 /

PL/SQLprocedure successfully completed.

SQL>

SQL>#Rem stop the job

SQL>BEGIN

2 BEGIN

3 -- call to stop the job

4 ORACLE_OCM.MGMT_CONFIG.stop_job;

5 EXCEPTION

6 WHEN OTHERS THEN

7 -- ignore any exception

8 null;

9 END;

10 END;

11 /

PL/SQLprocedure successfully completed.

SQL>

SQL>#Rem submit the job and run now

SQL>execute ORACLE_OCM.MGMT_CONFIG.submit_job;

PL/SQLprocedure successfully completed.

SQL>execute ORACLE_OCM.MGMT_CONFIG.run_now;

PL/SQLprocedure successfully completed.

SQL>

SQL>BEGIN

2 execute immediate 'REVOKE RESTRICTED SESSION FROM ORACLE_OCM';

3 EXCEPTION

4 WHEN OTHERS THEN

5 raise_application_error(-20007,SQLERRM);

6 END;

7 /

PL/SQLprocedure successfully completed.

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

SQL>Rem

SQL>Rem $Header: rdbms/admin/dbmsscnc.sql /st_rdbms_11.2.0.3.0dbpsu/1 2013/11/0604:17:31 mtiwary Exp $

SQL>Rem

SQL>Rem dbmsscn.sql

SQL>Rem

SQL>Rem NAME

SQL>Rem dbmsscnc.sql - dbms_scn packagedefinition

SQL>Rem

SQL>Rem DESCRIPTION

SQL>Rem <short description ofcomponent this file declares/defines>

SQL>Rem

SQL>Rem NOTES

SQL>Rem <other useful comments,qualifications, etc.>

SQL>Rem

SQL>Rem MODIFIED (MM/DD/YY)

SQL>Rem mtiwary 05/26/12 - Declarations and definitionsrelated to DBMS_SCN

SQL>Rem package.

SQL>Rem mtiwary 05/26/12 - Created

SQL>Rem

SQL>

SQL>Rem

SQL>Rem BEGIN SQL_FILE_METADATA

SQL>Rem SQL_SOURCE_FILE:rdbms/admin/dbmsscn.sql

SQL>Rem SQL_SHIPPED_FILE:

SQL>Rem SQL_PHASE:

SQL>Rem SQL_STARTUP_MODE: NORMAL

SQL>Rem SQL_IGNORABLE_ERRORS: NONE

SQL>Rem SQL_CALLING_FILE:

SQL>Rem END SQL_FILE_METADATA

SQL>

SQL>SET ECHO ON

SQL>SET FEEDBACK 1

SQL>SET NUMWIDTH 10

SQL>SET LINESIZE 80

SQL>SET TRIMSPOOL ON

SQL>SET TAB OFF

SQL>SET PAGESIZE 100

SQL>

SQL>CREATE OR REPLACE LIBRARY DBMS_SCN_LIB TRUSTED AS STATIC;

2 /

Librarycreated.

SQL>

SQL>CREATE OR REPLACE PACKAGE DBMS_SCN AUTHID CURRENT_USER IS

3 DBMS_SCN_API_MAJOR_VERSION CONSTANT NUMBER := 1;

4 DBMS_SCN_API_MINOR_VERSION CONSTANT NUMBER := 0;

6 PROCEDURE GetCurrentSCNParams(

7 rsl OUT number,

8 headroom_in_scn OUT number,

9 headroom_in_sec OUT number,

10 cur_scn_compat OUT number,

11 max_scn_compat OUT number);

13 -- Currently no exceptionsare thrown.

14 -- rsl - Reasonable SCN Limit as of 'now'

15 -- headroom_in_scn -Difference between current SCN and RSL

16 -- headroom_in_sec - number ofseconds it would take to reach RSL

17 -- assuminga constant SCN consumption rate associated

18 -- withcurrent SCN compatibility level

19 -- cur_scn_compat - current value of SCN compatibility

20 -- max_scn_compat - max value of SCN compatibility thisdatabase

21 -- understands

23 FUNCTION GetSCNParamsByCompat(

24 compat IN number,

25 rsl OUT number,

26 headroom_in_scn OUTnumber,

27 headroom_in_sec OUT number

28 ) RETURN boolean;

30 -- compat -- SCN compatibility value

31 -- rsl -- Reasonable SCN Limit

32 -- headroom_in_scn -- Difference between current SCN and RSL

33 -- headroom_in_sec -- number of seconds it would take to reachRSL

34 -- assuming a constant SCN consumption rate associated

35 -- withspecified database SCN compatibility

36 --

37 -- Returns False if 'compat'parameter value is invalid, and OUT parameters

38 -- are not updated.

40 PROCEDURE GetSCNAutoRolloverParams(

41 effective_auto_rollover_tsOUT DATE,

42 target_compat OUT number,

43 is_enabled OUT boolean);

45 -- effective_auto_rollover_ts -timestamp at which rollover becomes

46 -- effective

47 -- target_compat - SCN compatibility value thisdatabase

48 -- will move to, as a result of

49 -- auto-rollover

50 -- is_enabled - TRUE if auto-rolloverfeature is

51 -- currently enabled

53 PROCEDURE EnableAutoRollover;

55 PROCEDURE DisableAutoRollover;

57 ENDDBMS_SCN;

58 /

Packagecreated.

SQL>

SQL>@?/rdbms/admin/prvtscnc.plb

SQL>SET ECHO ON

SQL>SET FEEDBACK 1

SQL>SET NUMWIDTH 10

SQL>SET LINESIZE 80

SQL>SET TRIMSPOOL ON

SQL>SET TAB OFF

SQL>SET PAGESIZE 100

SQL>CREATE OR REPLACE PACKAGE BODY DBMS_SCN wrapped

2 a000000

3 1

4 abcd

5 abcd

6 abcd

7 abcd

8 abcd

9 abcd

10 abcd

11 abcd

12 abcd

13 abcd

14 abcd

15 abcd

16 abcd

17 abcd

18 abcd

19 b

20 6c0243

21 QlmAiY1dAl0ShRRHlX+HGNAfF7Mwgw23ACAVfC9A2k7VVhtmMilHXbSA4+y0szHoAcIlGGvF

22 LFznjZK7HsiO4405ad7otP6DvBJPmF/CgKv7vWxPthzol8UbWtg5Rsh0bB1IL1o27IiiL4Pp

23 ghghXIzy7qpN8ZKAqy5GoYTd+NFVjhaAPl79bXMSsYU3kLeYwwq6YrfeYIGtMvJPmD01eYTm

24 6ZHFbXW65+zhiLyd4n6gFjHiFm8ewsIUlps9n1Qmhi8+HDugSGp5JJUj8nWOq0ENurliNrJN

25 hU0xgcfHK5K6QfbtOHA/U80YLHmYL19b0SJ/rClUGJ61NxJXZGyQ5KEL4FaSdiRh+mztwHkD

26 0vUMuhwvNnlpUxmcvWlSy/43x86V3wrQNDQ+u0hWeLus6JG2IndfBYS5uYxgDImhZhepALfL

27 t71Ti3U3O8u0T7YrCu/D3Cr1ZiWOVQsf/xfYVuerG93+lzkruPtiRdV4U5PReE9tBiwb0r+Z

28 zwEKhyQwCZo3l/PypHsCJbpX2E6cQwagpSSNihdqCzJce+R5Ek7PZ6VqrwhVeOL4icI=

30 /

Packagebody created.

SQL>CREATE OR REPLACE PUBLIC SYNONYM dbms_scn FOR sys.dbms_scn;

Synonymcreated.

SQL>/

Synonymcreated.

SQL>GRANT EXECUTE ON dbms_scn TO PUBLIC;

Grantsucceeded.

SQL>/

Grantsucceeded.

SQL>PROMPT Skipping Oracle interMedia because it is not installed or versionsmismatch...

SkippingOracle interMedia because it is not installed or versions mismatch...

SQL>PROMPT Skipping Spatial because it is not installed or versions mismatch...

SkippingSpatial because it is not installed or versions mismatch...

SQL>ALTER SESSION SET current_schema = SYS;

Sessionaltered.

SQL>PROMPT Updating registry...

Updatingregistry...

SQL>INSERT INTO registry$history

2 (action_time, action,

3 namespace, version, id,

4 bundle_series, comments)

5 VALUES

6 (SYSTIMESTAMP, 'APPLY',

7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),

8 '11.2.0.3',

9 9,

10 'PSU',

11 'PSU 11.2.0.3.9');

1row created.

SQL>COMMIT;

Commitcomplete.

SQL>SPOOL off

SQL>SET echo off

Checkthe following log file for errors:

/oradata/cfgtoollogs/catbundle/catbundle_PSU_ORCL_APPLY_2014Jun24_00_01_42.log

SQL>

4.5修改监听

cd /oracle/product/11.2/network/admin

vi listener.ora

SECURE_REGISTER_LISTENER= (TCP)

LISTENER 是listener name

4.6启动监听

在主机上执行

lsnrctl start

[oracle@ahdb1 admin]$ lsnrctl start

检查监听

[oracle@ahdb1 admin]$ lsnrctl status

4.7查看日志

分别查看主机和备机

Checkthe following log files in $ORACLE_HOME/cfgtoollogs/catbundle or$ORACLE_BASE/cfgtoollogs/catbundle for any errors:

catbundle_PSU_<database SID>_APPLY_<TIMESTAMP>.log

catbundle_PSU_<database SID>_GENERATE_<TIMESTAMP>.log

4.8升级rman

本数据库没有设置rman catalog 此步不做

在主机上执行

rmantarget /

RMAN>UPGRADE CATALOG;

5 升级后的数据处理和验证

5.1检查数据库版本

在主机上检查

本次是打的补丁17540582，也就是最高版本应该是11.2.0.3.9

select a.bundle_series,a.commentsfromdba_registry_history a

[oracle@ahdb1~]$ cd OPatch/

[oracle@ahdb1OPatch]$ ./opatch lsinventory

OracleInterim Patch Installer version 11.2.0.3.6

OracleHome : /oracle/product/11.2

CentralInventory : /oracle/oraInventory

from : /oracle/product/11.2/oraInst.loc

OPatchversion : 11.2.0.3.6

OUIversion : 11.2.0.3.0

Logfile location :/oracle/product/11.2/cfgtoollogs/opatch/opatch2014-06-24_00-21-53AM_1.log

LsinventoryOutput file location :/oracle/product/11.2/cfgtoollogs/opatch/lsinv/lsinventory2014-06-24_00-21-53AM.txt

--------------------------------------------------------------------------------

InstalledTop-level Products (1):

OracleDatabase 11g 11.2.0.3.0

Thereare 1 product(s) installed in this Oracle Home.

Interimpatches (1) :

Patch 17540582 : applied on Mon Jun 23 23:59:00 CST 2014

UniquePatch ID: 16954971

Patchdescription: "Database Patch SetUpdate : 11.2.0.3.9 (17540582)"

Created on 7 Jan 2014, 02:20:21 hrs PST8PDT

Sub-patch 16902043; "Database Patch Set Update :11.2.0.3.8 (16902043)"

Sub-patch 16619892; "Database Patch Set Update :11.2.0.3.7 (16619892)"

Sub-patch 16056266; "Database Patch Set Update :11.2.0.3.6 (16056266)"

Sub-patch 14727310; "Database Patch Set Update :11.2.0.3.5 (14727310)"

Sub-patch 14275605; "Database Patch Set Update :11.2.0.3.4 (14275605)"

Sub-patch 13923374; "Database Patch Set Update :11.2.0.3.3 (13923374)"

Sub-patch 13696216; "Database Patch Set Update :11.2.0.3.2 (13696216)"

Sub-patch 13343438; "Database Patch Set Update :11.2.0.3.1 (13343438)"

Bugs fixed:

13593999, 10350832, 14138130, 12919564,13561951, 14198511, 13588248

13080778, 13804294, 16710324, 12873183,14472647, 12880299, 13369579

14409183, 13492735, 12857027, 13496884,14263036, 14263073, 13015379

16038929, 17748833, 16563678, 13732226,13866822, 13742434, 13944971

12950644, 17748831, 12899768, 13063120,13958038, 14613900, 13972394

11877623, 17088068, 13072654, 12395918,13814739, 17343514, 13649031

13981051, 12797765, 17333200, 12923168,16761566, 16279401, 13384182

13466801, 15996344, 14207163, 13724193,13642044, 11063191, 13945708

12797420, 12865902, 15869211, 13041324,14003090, 16314468, 16019955

11708510, 14637368, 13026410, 13737746,13742438, 15841373, 16347904

15910002, 16362358, 14398795, 13579992,16344871, 10400244, 14275605

13742436, 9858539, 14841812, 16338983,9703627, 13483354, 14207317

14393728, 12764337, 16902043, 14459552,14191508, 12964067, 12780983

12583611, 14383007, 14546575, 15862016,13476583, 13489024, 17748830

14088346, 13448206, 16314466, 13419660,14110275, 13430938, 13467683

14548763, 12834027, 13632809, 13377816,13036331, 14727310, 16175381

13584130, 12829021, 15862019, 12794305,14546673, 12791981, 13787482

13503598, 10133521, 12744759, 13399435,13553883, 14023636, 14762511

9095696, 14343501, 13860201, 13257247,14176879, 16014985, 12312133

14480675, 16306019, 13559697, 9706792,12974860, 12940620, 13098318

13773133, 15883525, 16794244, 13340388,13366202, 13528551, 12894807

12747437, 13454210, 12748240, 13385346,15987992, 13923995, 13582702

14571027, 12784406, 13907462, 13493847,13857111, 13035804, 16710363

13544396, 14128555, 8547978, 14226599,17478415, 17333197, 9397635

14007968, 12925089, 12693626, 14189694,12815057, 17761775, 16721594

13332439, 14038787, 11071989, 14207902,14062796, 12913474, 14390252

16314470, 13370330, 14062794, 13358781,17333202, 12960925, 9659614

14546638, 13699124, 13936424, 9797851,14301592, 16794240, 13338048

12938841, 12620823, 12656535, 12678920,14488943, 16850197, 14791477

14062792, 13807411, 16794238, 15862022,12594032, 13250244, 9761357

12612118, 14053457, 13527323, 10625145,15862020, 13910420, 12780098

13696216, 10263668, 14841558, 16794242,16944698, 15862023, 16056266

13834065, 14351566, 13723052, 13011409,14063280, 13566938, 13737888

13624984, 16024441, 17333199, 13914613, 17540582,14258925, 14222403

14755945, 13645875, 12571991, 14664355,12998795, 13719081, 14469008

14188650, 17019974, 13742433, 16368108,16314469, 12905058, 6690853

16212405, 12849688, 13742435, 13464002,13534412, 12879027, 12585543

13790109, 12535346, 16382448, 12588744,13916549, 13786142, 12847466

13855490, 13551402, 12582664, 14262913,17332800, 14695377, 12912137

13612575, 13484963, 14163397, 17437634,13772618, 16694777, 13070939

14369664, 12391034, 13605839, 16314467,16279211, 12976376, 12755231

13680405, 14589750, 13742437, 14318397,11868640, 14644185, 13326736

13596521, 13001379, 12898558, 17752121,13099577, 9873405, 16372203

16344758, 11715084, 16231699, 9547706,14040433, 12662040, 12617123

17748832, 16530565, 12845115, 16844086,17748834, 13354082, 13397104

13913630, 16462834, 12983611, 13550185,13810393, 14121009, 13065099

11840910, 13903046, 15862017, 13572659,16294378, 13718279, 13657605

14480676, 13632717, 14668670, 14063281,13420224, 13812031, 16299830

12646784, 14512189, 12755116, 13616375,17230530, 14035825, 13427062

12861463, 13092220, 15862021, 13043012,16619892, 13685544, 15862018

13499128, 13561750, 12718090, 13848402,13725395, 12401111, 12796518

13362079, 12917230, 13042639, 13923374,14220725, 12621588, 13524899

14751895, 14480674, 13916709, 14076523,15905421, 12731940, 13343438

14205448, 17748835, 14127231, 17082364,15853081, 14273397, 16844448

14467061, 12971775, 16864562, 14497307,12748538, 10242202, 14230270

16382353, 13686047, 14095982, 17333203,13591624, 14523004, 13440516

16794241, 14062795, 13035360, 13040943,13843646, 16794243, 14841409

13059165, 14062797, 12959852, 12345082,16703112, 13890080, 17333198

16450169, 12658411, 13780035, 14062793,13038684, 16742095, 13742464

14052474, 13060271, 13911821, 13457582,7509451, 13791364, 12821418

13502183, 13705338, 16794239, 15862024,13554409, 13645917, 13103913, 12772404

--------------------------------------------------------------------------------

OPatchsucceeded.

在备机上检查版本

[oracle@ahdb2OPatch]$ ./opatch lsinventory

OracleInterim Patch Installer version 11.2.0.3.6

OracleHome : /oracle/product/11.2

CentralInventory : /oracle/oraInventory

from : /oracle/product/11.2/oraInst.loc

OPatchversion : 11.2.0.3.6

OUIversion : 11.2.0.3.0

Logfile location : /oracle/product/11.2/cfgtoollogs/opatch/opatch2014-06-24_00-20-38AM_1.log

LsinventoryOutput file location :/oracle/product/11.2/cfgtoollogs/opatch/lsinv/lsinventory2014-06-24_00-20-38AM.txt

--------------------------------------------------------------------------------

InstalledTop-level Products (1):

OracleDatabase 11g 11.2.0.3.0

Thereare 1 product(s) installed in this Oracle Home.

Interimpatches (1) :

Patch 17540582 : applied on Mon Jun 23 23:16:01 CST 2014