예전 고객사에서 작업했던 내용을 꼼꼼하게 기록한 내용이다..
아마 1년도 채 안돼서 아래 작업을 했던 것 같다.. 그땐 긴장이 되었었다 ㅋㅋㅋ
11.2.0.1 에서 11.2.0.4로 manual upgrade 진행하였었다.
아래는 작업 순서이다. 이 후에도 아래 참고하여 몇번 진행했었고 문제 없이 잘 패치가 완료 되었다^^.
==================================================================
1. 기존 DB Registry, Object count, Invalid object count 확인
==================================================================
- DB registry 확인
SQL> select comp_name, version, status
2 from dba_registry;
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ --------------------------------------------
OWB 11.2.0.1.0 VALID
Oracle Application Express 3.2.1.00.10 VALID
Oracle Enterprise Manager 11.2.0.1.0 VALID
OLAP Catalog 11.2.0.1.0 VALID
Spatial 11.2.0.1.0 VALID
Oracle Multimedia 11.2.0.1.0 VALID
Oracle XML Database 11.2.0.1.0 VALID
Oracle Text 11.2.0.1.0 VALID
Oracle Expression Filter 11.2.0.1.0 VALID
Oracle Rules Manager 11.2.0.1.0 VALID
Oracle Workspace Manager 11.2.0.1.0 VALID
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ --------------------------------------------
Oracle Database Catalog Views 11.2.0.1.0 VALID
Oracle Database Packages and Types 11.2.0.1.0 VALID
JServer JAVA Virtual Machine 11.2.0.1.0 VALID
Oracle XDK 11.2.0.1.0 VALID
Oracle Database Java Packages 11.2.0.1.0 VALID
OLAP Analytic Workspace 11.2.0.1.0 VALID
Oracle OLAP API 11.2.0.1.0 VALID
18 rows selected.
- Object Count 확인
SQL> select count(*), object_type
2 from dba_objects
3 group by object_type
4 order by 1 desc;
COUNT(*) OBJECT_TYPE
---------- ----------------------------------------------------------------------------
27843 SYNONYM
22972 JAVA CLASS
6981 INDEX
5127 VIEW
4429 TABLE
2779 TYPE
1641 TRIGGER
1313 PACKAGE
1253 PACKAGE BODY
1232 LOB
1226 SEQUENCE
834 JAVA RESOURCE
328 JAVA DATA
306 FUNCTION
256 INDEX PARTITION
237 TYPE BODY
232 TABLE PARTITION
183 LIBRARY
181 PROCEDURE
55 OPERATOR
51 XML SCHEMA
36 QUEUE
25 CONSUMER GROUP
19 RULE SET
19 PROGRAM
15 JOB
13 JOB CLASS
13 EVALUATION CONTEXT
10 RESOURCE PLAN
10 CLUSTER
9 UNDEFINED
9 WINDOW
9 INDEXTYPE
7 CONTEXT
5 DATABASE LINK
4 DIRECTORY
4 SCHEDULER GROUP
3 SCHEDULE
2 JAVA SOURCE
2 DESTINATION
1 RULE
1 LOB PARTITION
1 MATERIALIZED VIEW
1 EDITION
44 rows selected.
- Invalid object count 확인
SQL> select object_name NAME,object_type TYPE,status STATUS,owner OWNER from dba_objects
where status = 'INVALID'
and owner not in ('SYS','SYSTEM')
/
*******************************************************
* *
* Invalid Object List *
* *
*******************************************************
NAME TYPE STATUS OWNER
------------------------------ -------------------- ---------------------------- ----------
MOLD_DAY_CHECK PACKAGE BODY INVALID MAXIMO
FN_AUTO_CLAC_FOR_ASSETLVL FUNCTION INVALID MAXIMO
LBSCREWVIEW VIEW INVALID MAXIMO
==================================================================
2. 신규 소프트웨어 설치
==================================================================
- 기존 오라클홈 : /KCMX/app/oracle/product/11.2.0/db_1
- 신규 오라클홈 : /KCMX/app2/oracle/product/11.2.0/db_1
- 파라미터 파일, 패스워드파일 복사
/KCMX>cp /KCMX/app/oracle/product/11.2.0/db_1/orapwKCMX /KCMX/app2/oracle/product/11.2.0/db_1/dbs/
/KCMX>cp /KCMX/app/oracle/product/11.2.0/db_1/initKCMX.ora /KCMX/app2/oracle/product/11.2.0/db_1/dbs/
- listener, tnsnames 복사
/KCMX>cp listener.ora /KCMX/app2/oracle/product/11.2.0/db_1/network/admin/
/KCMX>cp tnsnames.ora /KCMX/app2/oracle/product/11.2.0/db_1/network/admin/
==================================================================
3. 파라미터파일, 리스너 수정
==================================================================
- 파라미터파일 수정
#*.audit_file_dest='/KCMX/app/oracle/admin/KCMX/adump'
*.audit_trail='none'
*.compatible='11.2.0.0.0'
*.control_files='/KCMX/KCMX_DATA/KCMX/control01.ctl','/KCMX/KCMX_DATA/KCMX/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_files=1000
*.db_name='KCMX'
#*.diagnostic_dest='/KCMX/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=KCMXXDB)'
*.local_listener='LISTENER_KCMX'
*.open_cursors=3000
*.pga_aggregate_target=1048576000
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sessions=1655
*.sga_target=10485760000
*.undo_tablespace='UNDOTBS1'
*.log_archive_dest='/archive/KCMX/'
*.log_archive_format='KCMX_%t_%s_%r.arc'
*.nls_length_semantics=CHAR
*.open_links=10
*.open_links_per_instance=10
####Patch Set 11.2.0.4 Seting####
*.java_pool_size=500M
*.audit_file_dest='/KCMX/app2/oracle/admin/KCMX/adump'
*.diagnostic_dest='/KCMX/app2/oracle'
- 리스너 수정
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.105)(PORT = 1523))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = KCMX)
(ORACLE_HOME = /KCMX/app2/oracle/product/11.2.0/db_1)
)
)
ADR_BASE_LISTENER = /KCMX/app2/oracle
==================================================================
4. 기존 DB, 리스너 정지 -- 18시14분 정지
==================================================================
/KCMX>lsnrctl stop
LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 21-FEB-2018 18:13:37
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.9.105)(PORT=1523)))
The command completed successfully
/KCMX>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 21 18:13:50 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
==================================================================
5. 환경변수 수정
==================================================================
umask 022
export ORACLE_BASE=/KCMX/app2/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=KCMX
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export NLS10=$ORACLE_HOME/nls
export PATH=$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/sbin:/etc:.:/usr/ucb:/usr/bin:$ORACLE_HOME/OPatch
export DISPLAY=192.168.17.239:0.0
export TERM=vt220
set -o vi
PS1='$PWD>'
alias h="cd $ORACLE_HOME"
alias b="cd $ORACLE_BASE"
LANG=C
alias bdump='cd /KCMX/app2/oracle/diag/rdbms/kcmx/KCMX/trace'
==================================================================
6. Patch set 적용 18:20 ~ 18:55 (35분 소요)
==================================================================
KCMX>sqlplus / as sysdba
SQL> spool /tmp/upgrade.log
SQL> startup upgrade
SQL> set echo on
SQL> @?/rdbms/admin/catupgrd.sql;
SQL> spool off
SQL> startup
SQL> @?/rdbms/admin/catuppst.sql;
SQL> @?/rdbms/admin/utlrp.sql;
==================================================================
7. Redo log file group 추가
==================================================================
SQL>alter database add logfile group 6 ('/KCMX/KCMX_DATA/KCMX/redo06.log','/KCMX/REDO_MIRROR/redo06a.log') size 52428800;
SQL>alter database add logfile group 7 ('/KCMX/KCMX_DATA/KCMX/redo07.log','/KCMX/REDO_MIRROR/redo07a.log') size 52428800;
SQL>alter database add logfile group 8 ('/KCMX/KCMX_DATA/KCMX/redo08.log','/KCMX/REDO_MIRROR/redo08a.log') size 52428800;
SQL>alter database add logfile group 9 ('/KCMX/KCMX_DATA/KCMX/redo09.log','/KCMX/REDO_MIRROR/redo09a.log') size 52428800;
SQL>alter database add logfile group 10 ('/KCMX/KCMX_DATA/KCMX/redo10.log','/KCMX/REDO_MIRROR/redo10a.log') size 52428800;
==================================================================
8. 정합성 체크
==================================================================
- DB registry 확인 (정상)
SQL> select comp_name, version, status
2 from dba_registry;
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ --------------------------------------------
OWB 11.2.0.1.0 VALID
Oracle Application Express 3.2.1.00.10 VALID
Oracle Enterprise Manager 11.2.0.4.0 VALID
OLAP Catalog 11.2.0.1.0 OPTION OFF
Spatial 11.2.0.1.0 OPTION OFF
Oracle Multimedia 11.2.0.4.0 INVALID
Oracle XML Database 11.2.0.4.0 VALID
Oracle Text 11.2.0.4.0 VALID
Oracle Expression Filter 11.2.0.4.0 VALID
Oracle Rules Manager 11.2.0.4.0 VALID
Oracle Workspace Manager 11.2.0.4.0 VALID
Oracle Database Catalog Views 11.2.0.4.0 VALID
Oracle Database Packages and Types 11.2.0.4.0 VALID
JServer JAVA Virtual Machine 11.2.0.4.0 VALID
Oracle XDK 11.2.0.4.0 VALID
Oracle Database Java Packages 11.2.0.4.0 VALID
OLAP Analytic Workspace 11.2.0.1.0 OPTION OFF
Oracle OLAP API 11.2.0.1.0 OPTION OFF
18 rows selected.
*OLAP리소스는 SE에서 더이상 지원안함(11.2.0.4기준 OPTION OFF)
- object count 확인 (정상)
COUNT(*) OBJECT_TYPE
---------- ------------------------------
34106 SYNONYM
29062 JAVA CLASS
8251 INDEX
5311 VIEW
4481 TABLE
2905 TYPE
1642 TRIGGER
1338 PACKAGE
1277 PACKAGE BODY
1250 LOB
1227 SEQUENCE
943 JAVA RESOURCE
317 JAVA DATA
316 FUNCTION
271 INDEX PARTITION
244 TABLE PARTITION
242 TYPE BODY
202 PROCEDURE
190 LIBRARY
55 OPERATOR
51 XML SCHEMA
35 QUEUE
32 TABLE SUBPARTITION
25 CONSUMER GROUP
19 PROGRAM
19 RULE SET
15 JOB
14 JOB CLASS
12 EVALUATION CONTEXT
11 UNDEFINED
10 CLUSTER
10 RESOURCE PLAN
9 INDEXTYPE
9 WINDOW
7 CONTEXT
5 DIRECTORY
5 DATABASE LINK
4 SCHEDULER GROUP
3 LOB PARTITION
3 SCHEDULE
2 JAVA SOURCE
2 DESTINATION
1 MATERIALIZED VIEW
1 RULE
1 EDITION
45 rows selected.
- Invalid object 확인 (정상)
*******************************************************
* *
* Invalid Object List *
* *
*******************************************************
NAME TYPE STATUS OWNER
------------------------------ ---------------------------------------------------------------------------- ---------------------------- ----------
ALL_OLAP2_AW_CATALOGS SYNONYM INVALID PUBLIC
ALL_OLAP2_AW_CATALOG_MEASURES SYNONYM INVALID PUBLIC
ALL_OLAP2_AW_PHYS_OBJ SYNONYM INVALID PUBLIC
ALL_OLAP2_AW_PHYS_OBJ_PROP SYNONYM INVALID PUBLIC
ALL_OLAP2_AW_DIMENSIONS SYNONYM INVALID PUBLIC
ALL_OLAP2_AW_ATTRIBUTES SYNONYM INVALID PUBLIC
ALL_OLAP2_AW_CUBES SYNONYM INVALID PUBLIC
ALL_OLAP2_AW_CUBE_DIM_USES SYNONYM INVALID PUBLIC
ALL_AW_DIM_ENABLED_VIEWS SYNONYM INVALID PUBLIC
ALL_AW_CUBE_ENABLED_VIEWS SYNONYM INVALID PUBLIC
ALL_AW_CUBE_ENABLED_HIERCOMBO SYNONYM INVALID PUBLIC
ALL_OLAP2_AW_DIM_LEVELS SYNONYM INVALID PUBLIC
ALL_OLAP2_AW_DIM_HIER_LVL_ORD SYNONYM INVALID PUBLIC
ALL_OLAP2_AW_CUBE_MEASURES SYNONYM INVALID PUBLIC
ALL_OLAP2_AW_CUBE_AGG_SPECS SYNONYM INVALID PUBLIC
ALL_OLAP2_AW_CUBE_AGG_MEAS SYNONYM INVALID PUBLIC
ALL_OLAP2_AW_CUBE_AGG_LVL SYNONYM INVALID PUBLIC
ALL_OLAP2_AW_CUBE_AGG_OP SYNONYM INVALID PUBLIC
MRV_OLAP2_AW_DIMENSIONS SYNONYM INVALID PUBLIC
MRV_OLAP2_AW_ATTRIBUTES SYNONYM INVALID PUBLIC
MRV_OLAP2_AW_CUBES SYNONYM INVALID PUBLIC
MRV_OLAP2_AW_CUBE_MEASURES SYNONYM INVALID PUBLIC
OLAP_SYS_AW_ACCESS_CUBE_VIEW SYNONYM INVALID PUBLIC
OLAP_SYS_AW_ACCESS_DIM_VIEW SYNONYM INVALID PUBLIC
OLAP_SYS_AW_ENABLE_ACCESS_VIEW SYNONYM INVALID PUBLIC
ALL$OLAP2_AW_CATALOGS VIEW INVALID OLAPSYS
ALL$OLAP2_AW_CATALOG_MEASURES VIEW INVALID OLAPSYS
ALL$OLAP2_AW_PHYS_OBJ VIEW INVALID OLAPSYS
ALL$OLAP2_AW_PHYS_OBJ_PROP VIEW INVALID OLAPSYS
ALL$OLAP2_AW_DIMENSIONS VIEW INVALID OLAPSYS
ALL$OLAP2_AW_ATTRIBUTES VIEW INVALID OLAPSYS
ALL$OLAP2_AW_CUBES VIEW INVALID OLAPSYS
ALL$OLAP2_AW_CUBE_DIM_USES VIEW INVALID OLAPSYS
ALL$AW_DIM_ENABLED_VIEWS VIEW INVALID OLAPSYS
ALL$AW_CUBE_ENABLED_VIEWS VIEW INVALID OLAPSYS
ALL$AW_CUBE_ENABLED_HIERCOMBO VIEW INVALID OLAPSYS
ALL$OLAP2_AW_DIM_LEVELS VIEW INVALID OLAPSYS
ALL$OLAP2_AW_DIM_HIER_LVL_ORD VIEW INVALID OLAPSYS
ALL$OLAP2_AW_CUBE_MEASURES VIEW INVALID OLAPSYS
ALL$OLAP2_AW_CUBE_AGG_SPECS VIEW INVALID OLAPSYS
ALL$OLAP2_AW_CUBE_AGG_MEAS VIEW INVALID OLAPSYS
ALL$OLAP2_AW_CUBE_AGG_LVL VIEW INVALID OLAPSYS
ALL$OLAP2_AW_CUBE_AGG_OP VIEW INVALID OLAPSYS
MRAC_OLAP2_AW_DIMENSIONS_V VIEW INVALID OLAPSYS
MRAC_OLAP2_AW_ATTRIBUTES_V VIEW INVALID OLAPSYS
MRAC_OLAP2_AW_CUBES_V VIEW INVALID OLAPSYS
MRAC_OLAP2_AW_CUBE_MEASURES_V VIEW INVALID OLAPSYS
CWM2_OLAP_METADATA_REFRESH PACKAGE BODY INVALID OLAPSYS
OLAP_SYS_AW_ACCESS_DIM_VIEW VIEW INVALID OLAPSYS
OLAP_SYS_AW_ACCESS_CUBE_VIEW VIEW INVALID OLAPSYS
OLAP_SYS_AW_ENABLE_ACCESS_VIEW VIEW INVALID OLAPSYS
CWM2_OLAP_OLAPAPI_ENABLE PACKAGE BODY INVALID OLAPSYS
DBMS_AWM PACKAGE BODY INVALID OLAPSYS
==================================================================
9. 작업 종료
==================================================================
'Oracle' 카테고리의 다른 글
| oracle instance 선택 화면 출력 스크립트 (0) | 2023.07.19 |
|---|---|
| TIMESTAMP를 통해 과거 DML 수행 횟수 조회 (0) | 2023.07.17 |
| ODA Database 삭제(WEB-console(Mgmt)안될때 - Derby db사용) (0) | 2023.07.07 |
| ora-600[kdsgrp1](index/table corrupt/crash) , ora-600[6006](check corrupt and smon kill db) (0) | 2023.07.04 |
| oracle 9i to 10g upgrade 작업 (0) | 2023.07.04 |