oracle 9.2.0.8 EE에서 10g 10.2.0.5 SE로 upgrade 작업을 진행했었다.
1년전쯤 했던건데 작업 이력이 남아있어 다시 정리해보려한다
AS-IS > AIX 5.3 ORACLE DATABASE 9.2.0.8 EE
TO-BE > AIX 6.1 ORACLE DATABASE 10.2.0.5 SE
1.
============================================================================================
AS-IS DB shutdown 및 TO-BE로 경로 그대로 Cold backup
(/oracle ($ORACLE_HOME), archive, datafile 전부 경로 그대로 to-be로 copy)
============================================================================================
2.
============================================================================================
TO-BE에서 정상적으로 DB가 기동되는지 확인 (oracle 계정의 .profile을 as-is profile로 변경)
============================================================================================
3.
============================================================================================
TO-BE에서 레지스트리 조회 및 10g 업그레이드 전 사전 sql 체크 (10g설치시 $oracle_home/rdbms/admin 에 존재)
============================================================================================
SQL> select COMP_NAME,version,status from dba_registry;
COMP_NAME VERSION STATUS
-------------------- ---------- ----------------------
Oracle9i Catalog Views 9.2.0.8.0 VALID
Oracle9i Packages and Types 9.2.0.8.0 VALID
Oracle Workspace Manager 9.2.0.1.0 VALID
JServer JAVA VirtualMachine 9.2.0.8.0 VALID
Oracle XDK for Java 9.2.0.10.0 VALID
Oracle9i Java Packages 9.2.0.8.0 VALID
SQL> @/oracle/app/product/10.2.0/rdbms/admin/utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility 03-26-2022 16:03:26
.
**********************************************************************
Database:
**********************************************************************
--> name: orcl
--> version: 9.2.0.8.0
--> compatible: 9.2.0.1.0
--> blocksize: 8192
.
**********************************************************************
Logfiles: [make adjustments in the current environment]
**********************************************************************
--> The existing log files are adequate. No changes are required.
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 778 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 2457 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
*."_gby_hash_aggregation_enabled" = false
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: --> "streams_pool_size" is not currently defined and needs a value of
at least 50331648
WARNING: --> "session_max_open_files" needs to be increased to at least 20
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
--> "log_archive_start"
.
**********************************************************************
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
...The 'JServer JAVA Virtual Machine' JAccelerator (NCOMP)
...is required to be installed from the 10g Companion CD.
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle XML Database [install]
--> Oracle Workspace Manager [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Passwords exist in some database links.
.... Passwords will be encrypted during the upgrade.
.... Downgrade of database links with passwords is not supported.
WARNING: --> Deprecated CONNECT role granted to some user/roles.
.... CONNECT role after upgrade has only CREATE SESSION privilege.
WARNING: --> Database contains stale optimizer statistics.
.... Refer to the 10g Upgrade Guide for instructions to update
.... statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... WMSYS
WARNING: --> Database contains INVALID objects prior to upgrade.
.... USER hhh has 10 INVALID objects.
.... USER ggg has 1 INVALID objects.
.... USER aaa has 923 INVALID objects.
.... USER bbb has 7 INVALID objects.
.... USER ccc has 43 INVALID objects.
.... USER ddd has 627 INVALID objects.
.... USER fff has 24 INVALID objects.
.... USER SYS has 13 INVALID objects.
.
**********************************************************************
SYSAUX Tablespace:
[Create tablespace in the Oracle Database 10.2 environment]
**********************************************************************
--> New "SYSAUX" tablespace
.... minimum required size for database upgrade: 500 MB
.
PL/SQL procedure successfully completed.
SQL> @/oracle/app/product/10.2.0/rdbms/admin/utltzuv2.sql
There are no time zone version changes for Release 9.0.1 or 9.2.0. Customers
with extended maintenance support can be always provided with new time zone
version files and the updated 9i style script if needed.
PL/SQL procedure successfully completed.
.
**********************************************************************
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
...The 'JServer JAVA Virtual Machine' JAccelerator (NCOMP)
...is required to be installed from the 10g Companion CD.
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> Oracle Data Mining [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] UPGRADED
--> OLAP Catalog [upgrade] VALID
--> Oracle OLAP API [upgrade] UPGRADED
--> Oracle interMedia [upgrade] VALID
...The 'Oracle interMedia Image Accelerator' is
...required to be installed from the 10g Companion CD.
--> Spatial [upgrade] VALID
--> Oracle Ultra Search [upgrade] VALID
... To successfully upgrade Ultra Search, install it from
... the 10g Companion CD.
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Passwords exist in some database links.
.... Passwords will be encrypted during the upgrade.
.... Downgrade of database links with passwords is not supported.
WARNING: --> Deprecated CONNECT role granted to some user/roles.
.... CONNECT role after upgrade has only CREATE SESSION privilege.
WARNING: --> Database contains stale optimizer statistics.
.... Refer to the 10g Upgrade Guide for instructions to update
.... statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... XDB
.... WMSYS
.... ODM
.... OLAPSYS
.... MDSYS
.... WKSYS
WARNING: --> Database contains INVALID objects prior to upgrade.
.... USER DC has 9 INVALID objects.
.... USER GDS has 11 INVALID objects.
.... USER HP has 9 INVALID objects.
.... USER IM has 3 INVALID objects.
.... USER LD has 7 INVALID objects.
.... USER XDB has 33 INVALID objects.
.
**********************************************************************
SYSAUX Tablespace:
[Create tablespace in the Oracle Database 10.2 environment]
**********************************************************************
--> New "SYSAUX" tablespace
.... minimum required size for database upgrade: 500 MB
4.
============================================================================================
필요한 정보 check 및 생성/변경
============================================================================================
SQL> create tablespace SYSAUX datafile '/oradata/sysaux01.dbf'
size 1000M reuse
extent management local
segment space management auto
online;
WARNING: --> "streams_pool_size" is not currently defined and needs a value of
at least 50331648
WARNING: --> "session_max_open_files" needs to be increased to at least 20
참조하여 부족할 시 init file (초기화파라미터)에 변경 한다. 그리고 최소 tablespace size 가 부족할 시 오류가 날 수 있으니 반드시 체크
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 778 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 2457 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
모두 변경을 하였다면 수정한 init file과 password 파일을 신규 10g $ORACLE_HOME에 copy한다
cp /oracle/app/oracle/product/9.2.0/dbs/initorcl.ora /oracle/app/product/10.2.0/dbs/
cp /oracle/app/oracle/product/9.2.0/dbs/orapworcl /oracle/app/product/10.2.0/dbs/
5.
============================================================================================
DB 종료 및 upgrade 진행
============================================================================================
9i DB를 종료한다.
SQL> shut immediate
SQL> exit
.profile 의 export ORACLE_BASE , HOME 10g 정보로 수정 (에러시 참고를 위해 spool 진행)
SQL> startup upgrade
SQL>spool upgrd_0326_ORCL.log
SQL>@?/rdbms/admin/catupgrd.sql
SQL> spool off
컴파일 진행
SQL>@?/rdbms/admin/utlrp.sql
변경된 정보 확인
SQL> @?/rdbms/admin/utlu102s.sql
Oracle Database 10.2 Upgrade Status Utility 03-26-2022 15:52:59
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.5.0 00:23:21
JServer JAVA Virtual Machine VALID 10.2.0.5.0 00:01:39
Oracle XDK VALID 10.2.0.5.0 00:00:35
Oracle Database Java Packages VALID 10.2.0.5.0 00:00:10
Oracle Text VALID 10.2.0.5.0 00:00:12
Oracle XML Database VALID 10.2.0.5.0 00:00:34
Oracle Workspace Manager VALID 10.2.0.5.0 00:00:19
Oracle Data Mining OPTION OFF 9.2.0.8.0 00:00:00
OLAP Analytic Workspace OPTION OFF 9.2.0.8.0 00:00:00
OLAP Catalog OPTION OFF 9.2.0.8.0 00:00:00
Oracle OLAP API OPTION OFF 9.2.0.8.0 00:00:00
Oracle interMedia VALID 10.2.0.5.0 00:01:49
Spatial OPTION OFF 9.2.0.8.0 00:00:00
SQL> select COMP_NAME,version,status from dba_registry;
COMP_NAME VERSION STATUS
------------------------- ---------- ----------
Oracle XML Database 10.2.0.5.0 VALID
Oracle Workspace Manager 10.2.0.5.0 VALID
Oracle Database Catalog V 10.2.0.5.0 VALID
iews
Oracle Database Packages 10.2.0.5.0 VALID
and Types
JServer JAVA Virtual Mach 10.2.0.5.0 VALID
ine
Oracle XDK 10.2.0.5.0 VALID
Oracle Database Java Pack 10.2.0.5.0 VALID
ages
5.
============================================================================================
작업 종료
============================================================================================
문제없이 한번에 완료되었다..
그때 작업시간을 보니 cold backup을 옮기는데 4-5시간 소요됐다. 전날 야간에 DB내리고 새벽에 서버업체에서 COPY를 걸어줬고 다음날 오전에 완료되었다고 하여 작업을 진행하였다..
upgrade 작업 시간은 이렇게 적힌걸 보니 뭐 .. 금방끝났었다. 총 내가 한건 1시간 남짓..?
exp/imp는 o/s가 달라졌을때 써야지ㅠㅠ 사실 이번주 금요일에 비슷한 사이트 업그레이드하는데 o/s 가 달라져서 exp/imp로 가야하고.. 테스트이관을 해봤는데 대략 10시간 걸렸다..
홀리쉣
'Oracle' 카테고리의 다른 글
| 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 |
| timestamp 이용하여 테이블 복구(Table recovery using timestamp) (0) | 2023.07.04 |
| log file parallel write wait event (0) | 2023.07.03 |
| archive log 보관 주기 설정(Set the archive log retention period) (0) | 2023.07.01 |