1. 개요
11.2.0.4 버전에서 19c(19.6)으로 업그레이드하는 작업입니다.
업그레이드를 수행한 VM 환경(Virtual Box 사용)은 다음과 같습니다.
| CPU | 2 Core를 VM에 할당 | OS | Oracle Linux 7.6 |
| Memory | 2048MB | Source DB | 11.2.0.4 (별도 패치 안함) |
| Disk | 30GB (기본 DB만 생성) | Target DB | 19.6 (19.3에 RU 적용) |
2. 사전 조사
사전 조사 preupgrade.jar 실행
[oracle@orcl ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1
[oracle@orcl ~]$ $ORACLE_BASE/product/19c/db_1/jdk/bin/java -jar $ORACLE_BASE/product/19c/db_1/rdbms/admin/preupgrade.jar TERMINAL TEXT
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-03-05T10:31:31
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: ORCL
Container Name: Not Applicable in Pre-12.1 database
Container ID: Not Applicable in Pre-12.1 database
Version: 11.2.0.4.0
DB Patch Level: No Patch Bundle applied
Compatible: 11.2.0.4.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 14
Database log mode: NOARCHIVELOG
Readonly: FALSE
Edition: EE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Oracle Workspace Manager [to be upgraded] VALID
OLAP Analytic Workspace [to be upgraded] VALID
Oracle Enterprise Manager Repository [to be upgraded] VALID
Oracle Text [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Oracle Multimedia [to be upgraded] VALID
Oracle Spatial [to be upgraded] VALID
Expression Filter [to be upgraded] VALID
Rule Manager [to be upgraded] VALID
Oracle OLAP API [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
1. Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums.
This action may be done now or when starting the database in upgrade mode
using the 19 ORACLE HOME.
Parameter Currently 19 minimum
--------- --------- ------------------
processes 150 300
The database upgrade process requires certain initialization parameters
to meet minimum values. The Oracle upgrade process itself has minimum
values which may be higher and are marked with an asterisk. After
upgrading, those asterisked parameter values may be reset if needed.
2. Remove the EM repository.
- Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target
19 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.
Step 1: If database control is configured, stop EM Database Control,
using the following command
$> emctl stop dbconsole
Step 2: Connect to the database using the SYS account AS SYSDBA
SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql
Without the set echo and serveroutput commands, you will not be able to
follow the progress of the script.
The database has an Enterprise Manager Database Control repository.
Starting with Oracle Database 12c, the local Enterprise Manager Database
Control does not exist anymore. The repository will be removed from your
database during the upgrade. This step can be manually performed before
the upgrade to reduce downtime.
3. Remove OLAP Catalog by running the 11.2.0.4.0 SQL script
$ORACLE_HOME/olap/admin/catnoamd.sql script.
The OLAP Catalog component, AMD, exists in the database.
Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is
desupported and will be automatically marked as OPTION OFF during the
database upgrade if present. Oracle recommends removing OLAP Catalog
(OLAP AMD) before database upgrade. This step can be manually performed
before the upgrade to reduce downtime.
4. Upgrade Oracle Application Express (APEX) manually before the database
upgrade.
The database contains APEX version 3.2.1.00.12. Upgrade APEX to at least
version 18.2.0.00.12.
Starting with Oracle Database Release 18, APEX is not upgraded
automatically as part of the database upgrade. Refer to My Oracle Support
Note 1088970.1 for information about APEX installation and upgrades.
5. Please make sure that all the MVs are refreshed and sys.sumdelta$ becomes
empty before doing upgrade, unless you have strong business reasons not
to do so. You can use dbms_mview.refresh() to refresh the MVs except
those stale ones to be kept due to business need. If there are any stale
MVs depending on changes in sys.sumdelta$, do not truncate it, because
doing so will cause wrong results after refresh. Please refer to the
Materialized View section in MOS Note 2380601.1 for more details.
There are one or more materialized views in either stale or invalid
state, or which are currently being refreshed.
Oracle recommends that all materialized views (MV's) are refreshed before
upgrading the database because this will clear the MV logs and the
sumdelta$ table and may reduce the upgrade time. If you choose to not
refresh some MVs, the change data for those MV's will be carried through
the UPGRADE process. After UPGRADE, you can refresh the MV's and MV
incremental refresh should work in normal cases.
6. (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Dictionary statistics do not exist or are stale (not up-to-date).
Dictionary statistics help the Oracle optimizer find efficient SQL
execution plans and are essential for proper upgrade timing. Oracle
recommends gathering dictionary statistics in the last 24 hours before
database upgrade.
For information on managing optimizer statistics, refer to the 11.2.0.4
Oracle Database Performance Tuning Guide.
7. (AUTOFIXUP) Directly grant ADMINISTER DATABASE TRIGGER privilege to the
owner of the trigger or drop and re-create the trigger with a user that
was granted directly with such. You can list those triggers using: SELECT
OWNER, TRIGGER_NAME FROM DBA_TRIGGERS WHERE
TRIM(BASE_OBJECT_TYPE)='DATABASE' AND OWNER NOT IN (SELECT GRANTEE FROM
DBA_SYS_PRIVS WHERE PRIVILEGE='ADMINISTER DATABASE TRIGGER').
There is one or more database triggers whose owner does not have the
right privilege on the database.
The creation of database triggers must be done by users granted with
ADMINISTER DATABASE TRIGGER privilege. Privilege must have been granted
directly.
8. (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.
None of the fixed object tables have had stats collected.
Gathering statistics on fixed objects, if none have been gathered yet, is
recommended prior to upgrading.
For information on managing optimizer statistics, refer to the 11.2.0.4
Oracle Database Performance Tuning Guide.
INFORMATION ONLY
================
9. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.
Min Size
Tablespace Size For Upgrade
---------- ---------- -----------
SYSAUX 520 MB 762 MB
SYSTEM 750 MB 1180 MB
TEMP 29 MB 150 MB
UNDOTBS1 110 MB 446 MB
Minimum tablespace sizes for upgrade are estimates.
10. Run $ORACLE_HOME/rdbms/admin/catnoexf.sql located in the new Oracle
Database Oracle home to remove both EXF and RUL.
Expression Filter (EXF) or Rules Manager (RUL) exist in the database.
Starting with Oracle Database release 12.1, the Expression Filter (EXF)
and Database Rules Manager (RUL) features are desupported, and are
removed during the upgrade process. This step can be manually performed
before the upgrade to reduce downtime.
11. Check the Oracle Backup and Recovery User's Guide for information on how
to manage an RMAN recovery catalog schema.
If you are using a version of the recovery catalog schema that is older
than that required by the RMAN client version, then you must upgrade the
catalog schema.
It is good practice to have the catalog schema the same or higher version
than the RMAN client version you are using.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database ORCL
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
12. (AUTOFIXUP) If you use the -T option for the database upgrade, then run
$ORACLE_HOME/rdbms/admin/utluptabdata.sql after the upgrade is complete,
to VALIDATE and UPGRADE any user tables affected by changes to
Oracle-Maintained types.
There are user tables dependent on Oracle-Maintained object types.
If the -T option is used to set user tablespaces to READ ONLY during the
upgrade, user tables in those tablespaces, that are dependent on
Oracle-Maintained types, will not be automatically upgraded. If a type is
evolved during the upgrade, any dependent tables need to be re-validated
and upgraded to the latest type version AFTER the database upgrade
completes.
13. Upgrade the database time zone file using the DBMS_DST package.
The database is using time zone file version 14 and the target 19 release
ships with time zone file version 32.
Oracle recommends upgrading to the desired (latest) version of the time
zone file. For more information, refer to "Upgrading the Time Zone File
and Timestamp with Time Zone Data" in the 19 Oracle Database
Globalization Support Guide.
14. Recreate directory objects to remove any symbolic links from directory
paths. To identify paths that contain symbolic links before upgrading,
use OS commands like UNIX file or WINDOWS dir. After upgrading, run
$ORACLE_HOME/rdbms/admin/utldirsymlink.sql to identify directory objects
with symbolic links in the path.
Found 5 user directory objects to be checked: DATA_FILE_DIR,
LOG_FILE_DIR, MEDIA_DIR, SS_OE_XMLDIR, SUBDIR.
Starting in Release 18c, symbolic links are not allowed in directory
object paths used with BFILE data types, the UTL_FILE package, or
external tables.
15. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Oracle recommends gathering dictionary statistics after upgrade.
Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a database
upgrade, statistics need to be re-gathered as there can now be tables
that have significantly changed during the upgrade or new tables that do
not have statistics gathered yet.
16. Gather statistics on fixed objects after the upgrade and when there is a
representative workload on the system using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
This recommendation is given for all preupgrade runs.
Fixed object statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. Those
statistics are specific to the Oracle Database release that generates
them, and can be stale upon database upgrade.
For information on managing optimizer statistics, refer to the 11.2.0.4
Oracle Database Performance Tuning Guide.
INFORMATION ONLY
================
17. Check the Oracle documentation for the identified components for their
specific upgrade procedure.
The database upgrade script will not upgrade the following Oracle
components: OLAP Catalog,OWB
The Oracle database upgrade script upgrades most, but not all Oracle
Database components that may be installed. Some components that are not
upgraded may have their own upgrade scripts, or they may be deprecated or
obsolete.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database ORCL
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
==================
PREUPGRADE SUMMARY
==================
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2020-03-05T10:31:32
출처: https://ltrigger.tistory.com/entry/오라클-Database-Silent-Upgrade-11204-to-19x [트리거와 함께:티스토리]'DATABASE > ORACLE' 카테고리의 다른 글
| 오라클 엔터프라이즈 매니저(Oracle Enterprise Manager) (0) | 2022.10.13 |
|---|---|
| [오라클] 업그레이드 #4 - Oracle database upgrade (11.2.x to 19.x) (0) | 2022.10.10 |
| [오라클] 업그레이드 #3 - Oracle database upgrade (11.2.x to 19.x) (0) | 2022.10.10 |
| [오라클] 업그레이드 #2 - Oracle database upgrade (11.2.x to 19.x) (0) | 2022.10.10 |
댓글