반응형
사전 주요 파일 백업
[oracle@ol7 DATASYNCXML]$ id
uid=54321(oracle) gid=54322(dba) groups=54322(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@ol7 DATASYNCXML]$ pwd
/home/oracle/DATASYNCXML
[oracle@ol7 DATASYNCXML]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.4
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 30 13:13:37 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0.4/dbs/spfileTST.ora
SQL>
SQL> create pfile='/home/oracle/DATASYNCXML/initTST.ora_11g' from spfile;
File created.
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/home/oracle/DATASYNCXML/TST_cr_controlfile.sql_11g';
Database altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol7 11.2.0.4]$ cd $ORACLE_HOME/network/admin
[oracle@ol7 admin]$ ls
listener.ora samples shrept.lst
[oracle@ol7 admin]$ cp *.ora /home/oracle/DATASYNCXML
[oracle@ol7 admin]$ cd $ORACLE_HOME/dbs
[oracle@ol7 dbs]$ ls
hc_TST.dat init.ora initTST.ora lkTST orapwTST spfileTST.ora
[oracle@ol7 dbs]$ cp spfileTST.ora /home/oracle/DATASYNCXML/spfileTST.ora_11g
[oracle@ol7 dbs]$ cp orapwTST /home/oracle/DATASYNCXML/orapwTST_11g
[oracle@ol7 dbs]$ cd /home/oracle/DATASYNCXML/
[oracle@ol7 DATASYNCXML]$ ls
initTST.ora_11g listener.ora orapwTST_11g spfileTST.ora_11g TST_cr_controlfile.sql_11g
[oracle@ol7 DATASYNCXML]$preupgrade_fixups 수행
[oracle@ol7 DATASYNCXML]$ id
uid=54321(oracle) gid=54322(dba) groups=54322(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@ol7 DATASYNCXML]$ pwd
/home/oracle/DATASYNCXML
[oracle@ol7 DATASYNCXML]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.4
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ $ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/19.0.0.0/rdbms/admin/preupgrade.jar TERMINAL TEXT
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 13 on 2022-09-30T14:52:28
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: TST
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: PSU 11.2.0.4.201020
Compatible: 11.2.0.4.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 14
Database log mode: ARCHIVELOG
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
================
1. (AUTOFIXUP) Empty the RECYCLEBIN immediately before database upgrade.
The database contains 5 objects in the recycle bin.
The recycle bin must be completely empty before database upgrade.
RECOMMENDED ACTIONS
===================
2. 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.
3. 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.
4. 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.
5. 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.
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 to the upgrade using
the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
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. Here are ALL the components in this database registry:
Component Current Current Original Previous Component
CID Version Status Version Version Schema
--------- ----------- ----------- ----------- ----------- -----------
AMD 11.2.0.4.0 VALID OLAPSYS
APEX 3.2.1.00.12 VALID APEX_030200
APS 11.2.0.4.0 VALID SYS
CATALOG 11.2.0.4.0 VALID SYS
CATJAVA 11.2.0.4.0 VALID SYS
CATPROC 11.2.0.4.0 VALID SYS
CONTEXT 11.2.0.4.0 VALID CTXSYS
EM 11.2.0.4.0 VALID SYSMAN
EXF 11.2.0.4.0 VALID EXFSYS
JAVAVM 11.2.0.4.0 VALID SYS
ORDIM 11.2.0.4.0 VALID ORDSYS
OWB 11.2.0.4.0 VALID OWBSYS
OWM 11.2.0.4.0 VALID WMSYS
RUL 11.2.0.4.0 VALID EXFSYS
SDO 11.2.0.4.0 VALID MDSYS
XDB 11.2.0.4.0 VALID XDB
XML 11.2.0.4.0 VALID SYS
XOQ 11.2.0.4.0 VALID SYS
Review the information before upgrading.
10. 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 600 MB 747 MB
SYSTEM 750 MB 1186 MB
Minimum tablespace sizes for upgrade are estimates.
11. 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.
12. Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least
4950 MB of archived logs. Check alert log during the upgrade that there
is no write error to the destination due to lack of disk space.
Archiving cannot proceed if the archive log destination is full during
upgrade.
Archive Log Destination:
Parameter : LOG_ARCHIVE_DEST_1
Destination : /log/arch/
The database has archiving enabled. The upgrade process will need free
disk space in the archive log destination(s) to generate archived logs to.
13. 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.
14. Here is a count of invalid objects by users:
User Name Number of INVALID Objects
--------------------------- -------------------------
None None
Review the information before upgrading.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database TST
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/app/oracle/cfgtoollogs/TST/preupgrade/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
15. 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.
16. (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.
17. 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
================
18. 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 TST
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/app/oracle/cfgtoollogs/TST/preupgrade/postupgrade_fixups.sql
==================
PREUPGRADE SUMMARY
==================
/u01/app/oracle/cfgtoollogs/TST/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/TST/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/TST/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/TST/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u01/app/oracle/cfgtoollogs/TST/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2022-09-30T14:52:29
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 30 14:57:46 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @/u01/app/oracle/cfgtoollogs/TST/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 13
Generated on: 2022-09-30 14:52:25
For Source Database: TST
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. purge_recyclebin YES None.
2. parameter_min_val NO Manual fixup recommended.
3. em_present NO Manual fixup recommended.
4. amd_exists NO Manual fixup recommended.
5. apex_manual_upgrade NO Manual fixup recommended.
6. dictionary_stats YES None.
7. trgowner_no_admndbtrg YES None.
8. pre_fixed_objects YES None.
9. component_info NO Informational only.
Further action is optional.
10. tablespaces_info NO Informational only.
Further action is optional.
11. exf_rul_exists NO Informational only.
Further action is optional.
12. min_archive_dest_size NO Informational only.
Further action is optional.
13. rman_recovery_version NO Informational only.
Further action is optional.
14. invalid_all_obj_info NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol7 DATASYNCXML]$Manual fixup 진행 및 DB Shutdown
[oracle@ol7 DATASYNCXML]$ id
uid=54321(oracle) gid=54322(dba) groups=54322(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@ol7 DATASYNCXML]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.4
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 30 15:06:25 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 150
SQL>
SQL> alter system set processes=300 scope=spfile sid='*';
System altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ cp /u01/app/oracle/product/19.0.0.0/rdbms/admin/emremove.sql $ORACLE_HOME/rdbms/admin/emremove.sql
[oracle@ol7 DATASYNCXML]$ cd $ORACLE_HOME/rdbms/admin/
[oracle@ol7 admin]$ pwd
/u01/app/oracle/product/11.2.0.4/rdbms/admin
[oracle@ol7 admin]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 30 15:12:54 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> @emremove.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/emremove.sql /main/5 2017/05/28 22:46:05 stanaya Exp $
SQL> Rem
SQL> Rem emremove.sql
~~~~
Dropping synonym : MGMT$ALERT_CURRENT ...
Dropping synonym : MGMT$ALERT_ANNOTATIONS ...
Dropping synonym : EMD_MNTR ...
Dropping synonym : ECM_UTIL ...
Finished phase 5
Starting phase 6 : Dropping Oracle Enterprise Manager related other roles ...
Process DBSNMP user
Done processing DBSNMP user
Finished phase 6
The Oracle Enterprise Manager related schemas and objects are dropped.
Do the manual steps to studown the DB Control if not done before running this
script and then delete the DB Control configuration files
PL/SQL procedure successfully completed.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol7 admin]$
[oracle@ol7 admin]$ ls -al catnoamd.sql
-rw-r--r--. 1 oracle dba 11916 Apr 13 2013 catnoamd.sql
[oracle@ol7 admin]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 30 15:23:42 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @catnoamd.sql
Synonym dropped.
~~~~~~~~~~
Role dropped.
PL/SQL procedure successfully completed.
1 row deleted.
SQL>
SQL> PURGE DBA_RECYCLEBIN ;
DBA Recyclebin purged.
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol7 admin]$11g 설정 파일을 19c로 복사
[oracle@ol7 ~]$ id
uid=54321(oracle) gid=54322(dba) groups=54322(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@ol7 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.4
[oracle@ol7 ~]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-SEP-2022 15:28:17
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7.localdomain)(PORT=1521)))
The command completed successfully
[oracle@ol7 ~]$ cd DATASYNCXML/
[oracle@ol7 DATASYNCXML]$ ls
initTST.ora_11g listener.ora orapwTST_11g spfileTST.ora_11g TST_cr_controlfile.sql_11g
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/
[oracle@ol7 DATASYNCXML]$ ls
initTST.ora_11g listener.ora orapwTST_11g spfileTST.ora_11g TST_cr_controlfile.sql_11g
[oracle@ol7 DATASYNCXML]$ cp listener.ora $ORACLE_HOME/network/admin
[oracle@ol7 DATASYNCXML]$ cp spfileTST.ora_11g $ORACLE_HOME/dbs/spfileTST.ora
[oracle@ol7 DATASYNCXML]$ cp orapwTST_11g $ORACLE_HOME/dbs/orapwTST
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ id
uid=54321(oracle) gid=54322(dba) groups=54322(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@ol7 DATASYNCXML]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.0.0.0
[oracle@ol7 DATASYNCXML]$ orapwd file=/u01/app/oracle/product/19.0.0.0/dbs/orapwTST force=y format=12
Enter password for SYS:
[oracle@ol7 DATASYNCXML]$ ls -al /u01/app/oracle/product/19.0.0.0/dbs
total 16
drwxr-xr-x. 2 oracle dba 59 Sep 30 15:41 .
drwxr-xr-x. 70 oracle dba 4096 Sep 30 15:40 ..
-rw-r--r--. 1 oracle dba 3079 May 14 2015 init.ora
-rw-r-----. 1 oracle dba 2048 Sep 30 15:41 orapwTST
-rw-r-----. 1 oracle dba 2560 Sep 30 15:31 spfileTST.ora
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 30-SEP-2022 15:42:36
Copyright (c) 1991, 2022, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19.0.0.0/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0.0/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ol7/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 30-SEP-2022 15:42:36
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0.0/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ol7/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@ol7 DATASYNCXML]$Oracle DB 19c Upgrade
[oracle@ol7 DATASYNCXML]$ id
uid=54321(oracle) gid=54322(dba) groups=54322(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@ol7 DATASYNCXML]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.0.0.0
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 30 15:43:49 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1660940992 bytes
Fixed Size 8897216 bytes
Variable Size 956301312 bytes
Database Buffers 687865856 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ $ORACLE_HOME/bin/dbupgrade
Argument list for [/u01/app/oracle/product/19.0.0.0/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.16.0.0.0DBRU_LINUX.X64_220701]
/u01/app/oracle/product/19.0.0.0/rdbms/admin/orahome = [/u01/app/oracle/product/19.0.0.0]
/u01/app/oracle/product/19.0.0.0/bin/orabasehome = [/u01/app/oracle/product/19.0.0.0]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0.0.0]
Analyzing file /u01/app/oracle/product/19.0.0.0/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20220930154446]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20220930154446/catupgrd_catcon_11869.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20220930154446/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20220930154446/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 2
Database Name = TST
DataBase Version = 11.2.0.4.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0.0/cfgtoollogs/TST/upgrade20220930154447/catupgrd_catcon_11869.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0.0/cfgtoollogs/TST/upgrade20220930154447/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0.0/cfgtoollogs/TST/upgrade20220930154447/catupgrd_*.lst] files for spool files, if any
Log file directory = [/u01/app/oracle/product/19.0.0.0/cfgtoollogs/TST/upgrade20220930154447]
Parallel SQL Process Count = 4
Components in [TST]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV EM MGW ODM OLS RAC WK]
DataBase Version = 11.2.0.4.0
------------------------------------------------------
Phases [0-107] Start Time:[2022_09_30 15:44:48]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [TST] Files:1 Time: 60s
*************** Catalog Core SQL ***************
Serial Phase #:1 [TST] Files:5 Time: 44s
Restart Phase #:2 [TST] Files:1 Time: 0s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [TST] Files:19 Time: 23s
Restart Phase #:4 [TST] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [TST] Files:7 Time: 17s
***************** Catproc Start ****************
Serial Phase #:6 [TST] Files:1 Time: 10s
***************** Catproc Types ****************
Serial Phase #:7 [TST] Files:2 Time: 7s
Restart Phase #:8 [TST] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [TST] Files:70 Time: 25s
Restart Phase #:10 [TST] Files:1 Time: 1s
************* Catproc Package Specs ************
Serial Phase #:11 [TST] Files:1 Time: 54s
Restart Phase #:12 [TST] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [TST] Files:95 Time: 6s
Restart Phase #:14 [TST] Files:1 Time: 0s
Parallel Phase #:15 [TST] Files:122 Time: 10s
Restart Phase #:16 [TST] Files:1 Time: 0s
Serial Phase #:17 [TST] Files:25 Time: 2s
Restart Phase #:18 [TST] Files:1 Time: 1s
***************** Catproc Views ****************
Parallel Phase #:19 [TST] Files:32 Time: 14s
Restart Phase #:20 [TST] Files:1 Time: 0s
Serial Phase #:21 [TST] Files:3 Time: 9s
Restart Phase #:22 [TST] Files:1 Time: 1s
Parallel Phase #:23 [TST] Files:25 Time: 166s
Restart Phase #:24 [TST] Files:1 Time: 0s
Parallel Phase #:25 [TST] Files:12 Time: 102s
Restart Phase #:26 [TST] Files:1 Time: 0s
Serial Phase #:27 [TST] Files:1 Time: 0s
Serial Phase #:28 [TST] Files:4 Time: 2s
Serial Phase #:29 [TST] Files:1 Time: 0s
Restart Phase #:30 [TST] Files:1 Time: 0s
*************** Catproc CDB Views **************
Serial Phase #:31 [TST] Files:1 Time: 1s
Restart Phase #:32 [TST] Files:1 Time: 0s
Serial Phase #:34 [TST] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [TST] Files:297 Time: 18s
Serial Phase #:36 [TST] Files:1 Time: 0s
Restart Phase #:37 [TST] Files:1 Time: 0s
Serial Phase #:38 [TST] Files:10 Time: 4s
Restart Phase #:39 [TST] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [TST] Files:3 Time: 37s
Restart Phase #:41 [TST] Files:1 Time: 0s
****************** Catproc SQL *****************
Parallel Phase #:42 [TST] Files:13 Time: 104s
Restart Phase #:43 [TST] Files:1 Time: 0s
Parallel Phase #:44 [TST] Files:11 Time: 8s
Restart Phase #:45 [TST] Files:1 Time: 0s
Parallel Phase #:46 [TST] Files:3 Time: 1s
Restart Phase #:47 [TST] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [TST] Files:1 Time: 9s
Restart Phase #:49 [TST] Files:1 Time: 0s
************** Final RDBMS scripts *************
Serial Phase #:50 [TST] Files:1 Time: 18s
************ Upgrade Component Start ***********
Serial Phase #:51 [TST] Files:1 Time: 1s
Restart Phase #:52 [TST] Files:1 Time: 0s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [TST] Files:2 Time: 307s
***************** Upgrading XDB ****************
Restart Phase #:54 [TST] Files:1 Time: 1s
Serial Phase #:56 [TST] Files:3 Time: 18s
Serial Phase #:57 [TST] Files:3 Time: 2s
Parallel Phase #:58 [TST] Files:10 Time: 1s
Parallel Phase #:59 [TST] Files:25 Time: 3s
Serial Phase #:60 [TST] Files:4 Time: 6s
Serial Phase #:61 [TST] Files:1 Time: 0s
Serial Phase #:62 [TST] Files:32 Time: 2s
Serial Phase #:63 [TST] Files:1 Time: 0s
Parallel Phase #:64 [TST] Files:6 Time: 5s
Serial Phase #:65 [TST] Files:2 Time: 14s
Serial Phase #:66 [TST] Files:3 Time: 52s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [TST] Files:1 Time: 0s
Serial Phase #:69 [TST] Files:1 Time: 1s
Parallel Phase #:70 [TST] Files:2 Time: 28s
Restart Phase #:71 [TST] Files:1 Time: 0s
Parallel Phase #:72 [TST] Files:2 Time: 1s
Serial Phase #:73 [TST] Files:2 Time: 0s
***************** Upgrading SDO ****************
Restart Phase #:74 [TST] Files:1 Time: 0s
Serial Phase #:76 [TST] Files:1 Time: 30s
Serial Phase #:77 [TST] Files:2 Time: 1s
Restart Phase #:78 [TST] Files:1 Time: 0s
Serial Phase #:79 [TST] Files:1 Time: 12s
Restart Phase #:80 [TST] Files:1 Time: 1s
Parallel Phase #:81 [TST] Files:3 Time: 28s
Restart Phase #:82 [TST] Files:1 Time: 1s
Serial Phase #:83 [TST] Files:1 Time: 3s
Restart Phase #:84 [TST] Files:1 Time: 0s
Serial Phase #:85 [TST] Files:1 Time: 7s
Restart Phase #:86 [TST] Files:1 Time: 1s
Parallel Phase #:87 [TST] Files:4 Time: 74s
Restart Phase #:88 [TST] Files:1 Time: 0s
Serial Phase #:89 [TST] Files:1 Time: 1s
Restart Phase #:90 [TST] Files:1 Time: 0s
Serial Phase #:91 [TST] Files:2 Time: 7s
Restart Phase #:92 [TST] Files:1 Time: 1s
Serial Phase #:93 [TST] Files:1 Time: 0s
Restart Phase #:94 [TST] Files:1 Time: 0s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [TST] Files:1 Time: 17s
Restart Phase #:96 [TST] Files:1 Time: 1s
*********** Final Component scripts ***********
Serial Phase #:97 [TST] Files:1 Time: 2s
************* Final Upgrade scripts ************
Serial Phase #:98 [TST] Files:1 Time: 55s
******************* Migration ******************
Serial Phase #:99 [TST] Files:1 Time: 28s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [TST] Files:1 Time: 1s
Serial Phase #:101 [TST] Files:1 Time: 0s
Serial Phase #:102 [TST] Files:1 Time: 30s
***************** Post Upgrade *****************
Serial Phase #:103 [TST] Files:1 Time: 24s
**************** Summary report ****************
Serial Phase #:104 [TST] Files:1 Time: 1s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [TST] Files:1 Time: 0s
Serial Phase #:106 [TST] Files:1 Time: 0s
Serial Phase #:107 [TST] Files:1 Time: 53s
------------------------------------------------------
Phases [0-107] End Time:[2022_09_30 16:11:03]
------------------------------------------------------
Grand Total Time: 1575s
LOG FILES: (/u01/app/oracle/product/19.0.0.0/cfgtoollogs/TST/upgrade20220930154447/catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/19.0.0.0/cfgtoollogs/TST/upgrade20220930154447/upg_summary.log
Grand Total Upgrade Time: [0d:0h:26m:15s]
[oracle@ol7 DATASYNCXML]$Upgrade 후속 작업 및 Upgrade 확인
[oracle@ol7 DATASYNCXML]$ id
uid=54321(oracle) gid=54322(dba) groups=54322(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@ol7 DATASYNCXML]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.0.0.0
[oracle@ol7 DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 30 16:14:34 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1660940992 bytes
Fixed Size 8897216 bytes
Variable Size 1124073472 bytes
Database Buffers 520093696 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL>
SQL> @/u01/app/oracle/cfgtoollogs/TST/preupgrade/postupgrade_fixups.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Package created.
No errors.
Package body created.
PL/SQL procedure successfully completed.
No errors.
Package created.
No errors.
Package body created.
No errors.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 13
Generated on: 2022-09-30 14:52:28
For Source Database: TST
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
15. old_time_zones_exist NO Manual fixup recommended.
16. post_dictionary YES None.
17. post_fixed_objects NO Informational only.
Further action is optional.
18. upg_by_std_upgrd YES None.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
Session altered.
SQL>
SQL> select BANNER from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL>
SQL>
SQL>
SQL> SET LINESIZE 400
SQL> COLUMN ACTION_TIME FORMAT A20
SQL> COLUMN ACTION FORMAT A10
SQL> COLUMN STATUS FORMAT A10
SQL> COLUMN DESCRIPTION FORMAT A60
SQL> COLUMN VERSION FORMAT A10
SQL> SELECT TO_CHAR(ACTION_TIME, 'YYYYMMDD HH24:MI:SS') AS ACTION_TIME,ACTION,STATUS,DESCRIPTION,SOURCE_VERSION
2 ,TARGET_VERSION,PATCH_ID FROM DBA_REGISTRY_SQLPATCH ORDER BY ACTION_TIME;
ACTION_TIME ACTION STATUS DESCRIPTION SOURCE_VERSION TARGET_VERSION PATCH_ID
-------------------- ---------- ---------- ------------------------------------------------------------ --------------- --------------- ----------
20220930 16:08:39 APPLY SUCCESS Database Release Update : 19.16.0.0.220719 (34133642) 19.1.0.0.0 19.16.0.0.0 34133642
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /log/arch/
Oldest online log sequence 139
Next log sequence to archive 141
Current log sequence 141
SQL>
SQL>
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.4.0
noncdb_compatible boolean FALSE
SQL>
SQL>
SQL> alter system set compatible='19.0.0' scope=spfile sid='*';
System altered.
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 30 16:46:42 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1660940992 bytes
Fixed Size 8897216 bytes
Variable Size 1090519040 bytes
Database Buffers 553648128 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 19.0.0
noncdb_compatible boolean FALSE
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@ol7 DATASYNCXML]$반응형