环境是:单实例,操作系统层面做了HA
目 录
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
Copyright (c) 2013, Oracle Corporation. All rights reserved.
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
Copyright (c) 2013, Oracle Corporation. All rights reserved.
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_objects groupbyowner
这些用户都是正在使用的
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
Copyright(c) 1982, 2011, Oracle. All rightsreserved.
Connectedto an idle instance.
SQL>exit
Disconnected
[oracle@ahdb217540582]$ /home/oracle/OPatch/opatchapply
OracleInterim Patch Installer version 11.2.0.3.6
Copyright(c) 2013, Oracle Corporation. All rightsreserved.
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]
y
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]
y
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
Copyright(c) 2013, Oracle Corporation. All rightsreserved.
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]
y
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]
y
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
Copyright(c) 1982, 2011, Oracle. All rightsreserved.
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.
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 Copyright (c) 2006, 2013, Oracle and/or its affiliates.
SQL>Rem All rights reserved.
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);
9
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;
19
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;
27
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;
35
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;
80
81 -- Grant RESTRICTED SESSION
82 execute immediate 'GRANT RESTRICTED SESSION TO ORACLE_OCM';
83
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>
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 Copyright (c) 2012, 2013, Oracle and/or its affiliates.
SQL>Rem All rights reserved.
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
2
3 DBMS_SCN_API_MAJOR_VERSION CONSTANT NUMBER := 1;
4 DBMS_SCN_API_MINOR_VERSION CONSTANT NUMBER := 0;
5
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);
12
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
22
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;
29
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.
39
40 PROCEDURE GetSCNAutoRolloverParams(
41 effective_auto_rollover_tsOUT DATE,
42 target_compat OUT number,
43 is_enabled OUT boolean);
44
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
52
53 PROCEDURE EnableAutoRollover;
54
55 PROCEDURE DisableAutoRollover;
56
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=
29
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
Copyright(c) 2013, Oracle Corporation. All rightsreserved.
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
Copyright(c) 2013, Oracle Corporation. All rightsreserved.
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
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
5.2检查数据库状态
状态为open,正常!
select b.INSTANCE_NAME,b.STATUS,b.STARTUP_TIME from v$instance b;
5.3监听状态
监听状态正常!
lsnrct status
5.4查看是否有无效对象,进行重新编译
没有无效对象,正常!
select 'ALTER ' || OBJECT_TYPE || ' ' || OBJECT_NAME || ' COMPILE;'
from USER_objects
where status = 'INVALID' and object_type in('VIEW','FUNCTION','PROCEDURE','TRIGGER');
select count(*)from USER_objects
where status = 'INVALID' and object_type in('VIEW','FUNCTION','PROCEDURE','TRIGGER');
5.5各对象情况
升级后对应的用户的对象和升级前一致,正常情况!
6 变更实施人员安排
本次数据库迁移中所有工作由负责。
7 迁移操作计划表
工作项 | 具体内容 | 开始时间 | 结束时间 | 耗时 | |
1 | 备份数据库软件&各种准备工作 | 下载上传补丁,验证补丁,备份数据库软件,统计数据库信息(详见3) | 17:30 |
|
|
2 | 停应用 |
|
| 0:01 |
|
3 | 源库检查&停数据库 | 检查是否应用全部断开连接,必须全部断开 | 23:00 | 23: 05 | 5min |
4 | 数据库备份 | Expdp备份数据库 | 23:05 | 23:55 | 50min |
| 关闭数据库 | Shutdown immediate |
|
|
|
5 | 主机()数据库打补丁 |
| 23:51 | 0:51 | 60min |
6 | 备机()数据库打补丁 |
| 0:51 | 1:20 | 30min |
7 | 数据库升级后简单检查 |
| 1:21 | 1:30 | 10min |
8 | 起应用 |
|
|
|
|
9 | 检查&应用测试 |
|
|
| 20min |
实际的执行时间表:
序号 | 工作项 | 具体内容 | 开始时间 | 结束时间 | 耗时 |
1 | 备份数据库软件&各种准备工作 | 下载上传补丁,验证补丁,备份数据库软件,统计数据库信息(详见3) | 17:30 | ||
2 | 停应用 | 0:01 | |||
3 | 源库检查&停数据库 | 检查是否应用全部断开连接,必须全部断开 | 23:00 | 23:05 | 5min |
4 | 数据库备份 | Expdp备份数据库 | 23:05 | 23:55 | 50min |
5 | 关闭数据库 | Shutdown immediate | 23:56 | 23:57 | 2min |
6 | 主机()和备机()数据库打补丁 | 23:57 | 0:20 | 23min | |
7 | 数据库升级后检查 | 0:21 | 0:25 | 4min | |
8 | 起应用 | 0:25:00 | 1:00 | 35min | |
9 | 数据库详细验证和检查 | 0:25:00 | 1:00 | 35min |
8 回滚方案
8.1 关闭数据库和监听
shutdown immediate
lsnrctl stop
8.2 回滚
/home/oracle/Optach/opatchrollback -id 17540582
8.3 数据库回滚
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle_PSU_<database SID>_ROLLBACK.sql
SQL> QUIT
Check the log file for any errors. The logfile is found in $ORACLE_BASE/cfgtoollogs/catbundle and is named catbundle_PSU_<databaseSID>_ROLLBACK_<TIMESTAMP>.log