Considering size of database and complexity of migration, an RMAN backup of source database can be used for initial load of target database.
The database backup can be taken directly using RMAN. However some customers do not prefer to configure libraries to perform backup on their database server or have bandwidth limitation for direct transfer to object storage. Such customers can take the backups locally and move them directly to target.
An important factor to consider for RMAN backup is the number of channels to be used. The more the number of channels, the faster would be the backup. The number of channels are also limited by performance of underlying I/O subsystem. So it is suggested to run few backup tests to arrive at the optimum number of channels that would provide maximum speed for available I/O subsystem.
Backup compression also plays an important role. The smaller the size of compressed backup, the lesser will be the time to transfer the backup pieces target. The compression ratio that can be achieved for backup depends on underlying data in database but it is always good to use compression to reduce the size of backup pieces.
A lot of customers have restrictions to execute backup for migration on the production system. For such cases, the backup can be taken from a standby database (if available).
Start upgrade 11g to 19c manually.
Source Environment.
About Source Details
OS Details version dbname dbhome
Redhat Linux 7.6 (64 bit) 11.2.0.4 pink11 /u01/app/oracle/11g/db_1
Redhat Linux 7.6 (64 bit) 19.8.0.0 pink11 /u01/app/oracle/19c/db_1
Listed below versions that support direct upgrade.
These all versions we can upgrade directly.
11.2.0.4
12.1.0.1
12.1.0.2
12.2.0.1
Important points before upgrade.
Before the upgrade database, you must remember these points.
Take full database backup using RMAN.
The recycle bin must be empty.
Gather dictionary stats.
Run pre-upgrade.
Run pre-upgrade fixups.
Goldengate for For Ponit in time data sync up
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
1. Run preupgrade (Take backup for safe side)
[oracle@east03 2020-10-12]$ $ORACLE_HOME/jdk/bin/java -jar /home/oracle/preupgrade.jar TERMINAL TEXT
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-10-12T14:52:01
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: PINK11
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: 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 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. Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 4583
MB. Check alert log during the upgrade to ensure there is remaining free
space available in the recovery area.
DB_RECOVERY_FILE_DEST_SIZE is set at 4182 MB. There is currently 4134 MB
of free space remaining, which may not be adequate for the upgrade.
Currently:
Fast recovery area : /u01/app/oracle/fast_recovery_area
Limit : 4182 MB
Used : 48 MB
Available : 4134 MB
The database has archivelog mode enabled, and the upgrade process will
need free space to generate archived logs to the recovery area specified
by initialization parameter DB_RECOVERY_FILE_DEST. The logs generated
must not overflow the limit set by DB_RECOVERY_FILE_DEST_SIZE, as that
can cause the upgrade to not proceed.
RECOMMENDED ACTIONS
===================
2. Run 11.2.0.4.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
objects. You can view the individual invalid objects with
SET SERVEROUTPUT ON;
EXECUTE DBMS_PREUP.INVALID_OBJECTS;
13 objects are INVALID.
There should be no INVALID objects in SYS/SYSTEM or user schemas before
database upgrade.
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. (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.
6. (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
================
7. 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 540 MB 706 MB
SYSTEM 760 MB 1175 MB
TEMP 32 MB 150 MB
UNDOTBS1 200 MB 444 MB
Minimum tablespace sizes for upgrade are estimates.
8. 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.
9. 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 PINK11
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/app/oracle/11g/db/cfgtoollogs/pink11/preupgrade/preupgrade_fixups.
sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
10. (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.
11. 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.
12. 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 1 user directory objects to be checked: GGS_DDL_TRACE.
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.
13. (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.
14. 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
================
15. 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 PINK11
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/app/oracle/11g/db/cfgtoollogs/pink11/preupgrade/postupgrade_fixups
.sql
==================
PREUPGRADE SUMMARY
==================
/u01/app/oracle/11g/db/cfgtoollogs/pink11/preupgrade/preupgrade.log
/u01/app/oracle/11g/db/cfgtoollogs/pink11/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/11g/db/cfgtoollogs/pink11/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/u01/app/oracle/11g/db/cfgtoollogs/pink11/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u01/app/oracle/11g/db/cfgtoollogs/pink11/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2020-10-12T14:52:01
[oracle@east03 2020-10-12]$
2 . Run Rman Bakup
3. Start Restore in 19c target
RMAN> RESTORE CONTROLFILE FROM "/home/oracle/a/1.ctl";
Starting restore at 12-OCT-20
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/pink11/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/pink11/control02.ctl
Finished restore at 12-OCT-20
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN> catalog start with '/home/oracle/a';
RMAN> crosscheck backup;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/a/PINK11_DB_20201013_b5vcrsi7 RECID=354 STAMP=1053684295
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/a/PINK11_DB_20201013_b6vcrsi8 RECID=355 STAMP=1053684296
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/a/PINK11_DB_20201013_b7vcrsif RECID=356 STAMP=1053684304
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/a/PINK11_DB_20201013_b8vcrsih RECID=357 STAMP=1053684305
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/a/PINK11_DB_20201013_bavcrtb4 RECID=358 STAMP=1053685743
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/a/PINK11_DB_20201013_bbvcrtbb RECID=359 STAMP=1053685743
Crosschecked 6 objects
RMAN>
RMAN> list backup;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
354 34.50K DISK 00:00:00 13-OCT-20
BP Key: 354 Status: AVAILABLE Compressed: NO Tag: TAG20201013T100454
Piece Name: /home/oracle/a/PINK11_DB_20201013_b5vcrsi7
List of Archived Logs in backup set 354
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 167 1268884 13-OCT-20 1269237 13-OCT-20
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
355 Full 1.01G DISK 00:00:05 13-OCT-20
BP Key: 355 Status: AVAILABLE Compressed: NO Tag: TAG20201013T100456
Piece Name: /home/oracle/a/PINK11_DB_20201013_b6vcrsi8
List of Datafiles in backup set 355
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 1269246 13-OCT-20 NO /u01/app/oracle/oradata/pink11/system01.dbf
2 Full 1269246 13-OCT-20 NO /u01/app/oracle/oradata/pink11/sysaux01.dbf
3 Full 1269246 13-OCT-20 NO /u01/app/oracle/oradata/pink11/undotbs01.dbf
4 Full 1269246 13-OCT-20 NO /u01/app/oracle/oradata/pink11/users01.dbf
5 Full 1269246 13-OCT-20 NO /u01/app/oracle/oradata/pink11/ggate_tbs_01.dbf
6 Full 1269246 13-OCT-20 NO /u01/app/oracle/oradata/pink11/json01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
356 Full 9.58M DISK 00:00:01 13-OCT-20
BP Key: 356 Status: AVAILABLE Compressed: NO Tag: TAG20201013T100456
Piece Name: /home/oracle/a/PINK11_DB_20201013_b7vcrsif
SPFILE Included: Modification time: 13-OCT-20
SPFILE db_unique_name: PINK11
Control File Included: Ckp SCN: 1269248 Ckp time: 13-OCT-20
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
357 2.50K DISK 00:00:00 13-OCT-20
BP Key: 357 Status: AVAILABLE Compressed: NO Tag: TAG20201013T100505
Piece Name: /home/oracle/a/PINK11_DB_20201013_b8vcrsih
List of Archived Logs in backup set 357
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 168 1269237 13-OCT-20 1269253 13-OCT-20
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
358 Full 1.01G DISK 00:00:00 13-OCT-20
BP Key: 358 Status: AVAILABLE Compressed: NO Tag: TAG20201013T101812
Piece Name: /home/oracle/a/PINK11_DB_20201013_bavcrtb4
List of Datafiles in backup set 358
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 1269578 13-OCT-20 NO /u01/app/oracle/oradata/pink11/system01.dbf
2 Full 1269578 13-OCT-20 NO /u01/app/oracle/oradata/pink11/sysaux01.dbf
3 Full 1269578 13-OCT-20 NO /u01/app/oracle/oradata/pink11/undotbs01.dbf
4 Full 1269578 13-OCT-20 NO /u01/app/oracle/oradata/pink11/users01.dbf
5 Full 1269578 13-OCT-20 NO /u01/app/oracle/oradata/pink11/ggate_tbs_01.dbf
6 Full 1269578 13-OCT-20 NO /u01/app/oracle/oradata/pink11/json01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
359 Full 9.58M DISK 00:00:00 13-OCT-20
BP Key: 359 Status: AVAILABLE Compressed: NO Tag: TAG20201013T101812
Piece Name: /home/oracle/a/PINK11_DB_20201013_bbvcrtbb
SPFILE Included: Modification time: 13-OCT-20
SPFILE db_unique_name: PINK11
Control File Included: Ckp SCN: 1269581 Ckp time: 13-OCT-20
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
354 34.50K DISK 00:00:00 13-OCT-20
BP Key: 354 Status: AVAILABLE Compressed: NO Tag: TAG20201013T100454
Piece Name: /home/oracle/a/PINK11_DB_20201013_b5vcrsi7
List of Archived Logs in backup set 354
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 167 1268884 13-OCT-20 1269237 13-OCT-20
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
357 2.50K DISK 00:00:00 13-OCT-20
BP Key: 357 Status: AVAILABLE Compressed: NO Tag: TAG20201013T100505
Piece Name: /home/oracle/a/PINK11_DB_20201013_b8vcrsih
List of Archived Logs in backup set 357
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 168 1269237 13-OCT-20 1269253 13-OCT-20
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
360 222.00K DISK 00:00:00 13-OCT-20
BP Key: 360 Status: AVAILABLE Compressed: NO Tag: TAG20201013T103636
Piece Name: /home/oracle/a/PINK11_DB_20201013_bcvcrudk
List of Archived Logs in backup set 360
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 167 1268884 13-OCT-20 1269237 13-OCT-20
1 168 1269237 13-OCT-20 1269253 13-OCT-20
1 169 1269253 13-OCT-20 1289762 13-OCT-20
1 170 1289762 13-OCT-20 1290125 13-OCT-20
RMAN> restore database;
Starting restore at 13-OCT-20
using channel ORA_DISK_1
skipping datafile 1; already restored to file /u01/app/oracle/oradata/pink11/system01.dbf
skipping datafile 2; already restored to file /u01/app/oracle/oradata/pink11/sysaux01.dbf
skipping datafile 3; already restored to file /u01/app/oracle/oradata/pink11/undotbs01.dbf
skipping datafile 4; already restored to file /u01/app/oracle/oradata/pink11/users01.dbf
skipping datafile 5; already restored to file /u01/app/oracle/oradata/pink11/ggate_tbs_01.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/pink11/json01.dbf
restore not done; all files read only, offline, excluded, or already restored
Finished restore at 13-OCT-20
RMAN> recover database;
Starting recover at 13-OCT-20
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=169
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=170
channel ORA_DISK_1: reading from backup piece /home/oracle/a/PINK11_DB_20201013_bcvcrudk
channel ORA_DISK_1: piece handle=/home/oracle/a/PINK11_DB_20201013_bcvcrudk tag=TAG20201013T103636
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/19c/db/dbs/arch1_169_1048875119.dbf thread=1 sequence=169
archived log file name=/u01/app/oracle/19c/db/dbs/arch1_170_1048875119.dbf thread=1 sequence=170
RMAN> exit
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-10-12T14:52:01
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: PINK11
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: 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 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. Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 4583
MB. Check alert log during the upgrade to ensure there is remaining free
space available in the recovery area.
DB_RECOVERY_FILE_DEST_SIZE is set at 4182 MB. There is currently 4134 MB
of free space remaining, which may not be adequate for the upgrade.
Currently:
Fast recovery area : /u01/app/oracle/fast_recovery_area
Limit : 4182 MB
Used : 48 MB
Available : 4134 MB
The database has archivelog mode enabled, and the upgrade process will
need free space to generate archived logs to the recovery area specified
by initialization parameter DB_RECOVERY_FILE_DEST. The logs generated
must not overflow the limit set by DB_RECOVERY_FILE_DEST_SIZE, as that
can cause the upgrade to not proceed.
RECOMMENDED ACTIONS
===================
2. Run 11.2.0.4.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
objects. You can view the individual invalid objects with
SET SERVEROUTPUT ON;
EXECUTE DBMS_PREUP.INVALID_OBJECTS;
13 objects are INVALID.
There should be no INVALID objects in SYS/SYSTEM or user schemas before
database upgrade.
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. (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.
6. (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
================
7. 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 540 MB 706 MB
SYSTEM 760 MB 1175 MB
TEMP 32 MB 150 MB
UNDOTBS1 200 MB 444 MB
Minimum tablespace sizes for upgrade are estimates.
8. 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.
9. 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 PINK11
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/app/oracle/11g/db/cfgtoollogs/pink11/preupgrade/preupgrade_fixups.
sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
10. (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.
11. 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.
12. 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 1 user directory objects to be checked: GGS_DDL_TRACE.
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.
13. (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.
14. 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
================
15. 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 PINK11
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/app/oracle/11g/db/cfgtoollogs/pink11/preupgrade/postupgrade_fixups
.sql
==================
PREUPGRADE SUMMARY
==================
/u01/app/oracle/11g/db/cfgtoollogs/pink11/preupgrade/preupgrade.log
/u01/app/oracle/11g/db/cfgtoollogs/pink11/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/11g/db/cfgtoollogs/pink11/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/u01/app/oracle/11g/db/cfgtoollogs/pink11/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u01/app/oracle/11g/db/cfgtoollogs/pink11/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2020-10-12T14:52:01
[oracle@east03 2020-10-12]$
2 . Run Rman Bakup
cat /home/oracle/backup.sh
#!/bin/ksh
export ORACLE_SID=pink11
rman target / msglog /home/oracle/backup.log cmdfile=/home/oracle/backup.rcv
$ cat /home/oracle/backup.rcv
run
{
SET UNTIL TIME 'OCT 13 2020 11:40:00';
allocate channel ch1 type DISK ;
allocate channel ch24 type DISK ;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT '/rman_backups/bildb/%d_D_%T_%u_s%s_p%p'
plus archivelog format '/rman_backups/pink11/arch_%U';
BACKUP CURRENT CONTROLFILE FORMAT '/rman_backups/pink11/1.ctl';
release channel ch1;
release channel ch24;
}
$ nohup sh /home/oracle/backup.sh &
3. Start Restore in 19c target
RMAN> RESTORE CONTROLFILE FROM "/home/oracle/a/1.ctl";
Starting restore at 12-OCT-20
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/pink11/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/pink11/control02.ctl
Finished restore at 12-OCT-20
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN> catalog start with '/home/oracle/a';
RMAN> crosscheck backup;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/a/PINK11_DB_20201013_b5vcrsi7 RECID=354 STAMP=1053684295
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/a/PINK11_DB_20201013_b6vcrsi8 RECID=355 STAMP=1053684296
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/a/PINK11_DB_20201013_b7vcrsif RECID=356 STAMP=1053684304
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/a/PINK11_DB_20201013_b8vcrsih RECID=357 STAMP=1053684305
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/a/PINK11_DB_20201013_bavcrtb4 RECID=358 STAMP=1053685743
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/a/PINK11_DB_20201013_bbvcrtbb RECID=359 STAMP=1053685743
Crosschecked 6 objects
RMAN>
RMAN> list backup;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
354 34.50K DISK 00:00:00 13-OCT-20
BP Key: 354 Status: AVAILABLE Compressed: NO Tag: TAG20201013T100454
Piece Name: /home/oracle/a/PINK11_DB_20201013_b5vcrsi7
List of Archived Logs in backup set 354
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 167 1268884 13-OCT-20 1269237 13-OCT-20
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
355 Full 1.01G DISK 00:00:05 13-OCT-20
BP Key: 355 Status: AVAILABLE Compressed: NO Tag: TAG20201013T100456
Piece Name: /home/oracle/a/PINK11_DB_20201013_b6vcrsi8
List of Datafiles in backup set 355
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 1269246 13-OCT-20 NO /u01/app/oracle/oradata/pink11/system01.dbf
2 Full 1269246 13-OCT-20 NO /u01/app/oracle/oradata/pink11/sysaux01.dbf
3 Full 1269246 13-OCT-20 NO /u01/app/oracle/oradata/pink11/undotbs01.dbf
4 Full 1269246 13-OCT-20 NO /u01/app/oracle/oradata/pink11/users01.dbf
5 Full 1269246 13-OCT-20 NO /u01/app/oracle/oradata/pink11/ggate_tbs_01.dbf
6 Full 1269246 13-OCT-20 NO /u01/app/oracle/oradata/pink11/json01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
356 Full 9.58M DISK 00:00:01 13-OCT-20
BP Key: 356 Status: AVAILABLE Compressed: NO Tag: TAG20201013T100456
Piece Name: /home/oracle/a/PINK11_DB_20201013_b7vcrsif
SPFILE Included: Modification time: 13-OCT-20
SPFILE db_unique_name: PINK11
Control File Included: Ckp SCN: 1269248 Ckp time: 13-OCT-20
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
357 2.50K DISK 00:00:00 13-OCT-20
BP Key: 357 Status: AVAILABLE Compressed: NO Tag: TAG20201013T100505
Piece Name: /home/oracle/a/PINK11_DB_20201013_b8vcrsih
List of Archived Logs in backup set 357
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 168 1269237 13-OCT-20 1269253 13-OCT-20
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
358 Full 1.01G DISK 00:00:00 13-OCT-20
BP Key: 358 Status: AVAILABLE Compressed: NO Tag: TAG20201013T101812
Piece Name: /home/oracle/a/PINK11_DB_20201013_bavcrtb4
List of Datafiles in backup set 358
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 1269578 13-OCT-20 NO /u01/app/oracle/oradata/pink11/system01.dbf
2 Full 1269578 13-OCT-20 NO /u01/app/oracle/oradata/pink11/sysaux01.dbf
3 Full 1269578 13-OCT-20 NO /u01/app/oracle/oradata/pink11/undotbs01.dbf
4 Full 1269578 13-OCT-20 NO /u01/app/oracle/oradata/pink11/users01.dbf
5 Full 1269578 13-OCT-20 NO /u01/app/oracle/oradata/pink11/ggate_tbs_01.dbf
6 Full 1269578 13-OCT-20 NO /u01/app/oracle/oradata/pink11/json01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
359 Full 9.58M DISK 00:00:00 13-OCT-20
BP Key: 359 Status: AVAILABLE Compressed: NO Tag: TAG20201013T101812
Piece Name: /home/oracle/a/PINK11_DB_20201013_bbvcrtbb
SPFILE Included: Modification time: 13-OCT-20
SPFILE db_unique_name: PINK11
Control File Included: Ckp SCN: 1269581 Ckp time: 13-OCT-20
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
354 34.50K DISK 00:00:00 13-OCT-20
BP Key: 354 Status: AVAILABLE Compressed: NO Tag: TAG20201013T100454
Piece Name: /home/oracle/a/PINK11_DB_20201013_b5vcrsi7
List of Archived Logs in backup set 354
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 167 1268884 13-OCT-20 1269237 13-OCT-20
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
357 2.50K DISK 00:00:00 13-OCT-20
BP Key: 357 Status: AVAILABLE Compressed: NO Tag: TAG20201013T100505
Piece Name: /home/oracle/a/PINK11_DB_20201013_b8vcrsih
List of Archived Logs in backup set 357
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 168 1269237 13-OCT-20 1269253 13-OCT-20
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
360 222.00K DISK 00:00:00 13-OCT-20
BP Key: 360 Status: AVAILABLE Compressed: NO Tag: TAG20201013T103636
Piece Name: /home/oracle/a/PINK11_DB_20201013_bcvcrudk
List of Archived Logs in backup set 360
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 167 1268884 13-OCT-20 1269237 13-OCT-20
1 168 1269237 13-OCT-20 1269253 13-OCT-20
1 169 1269253 13-OCT-20 1289762 13-OCT-20
1 170 1289762 13-OCT-20 1290125 13-OCT-20
RMAN> restore database;
Starting restore at 13-OCT-20
using channel ORA_DISK_1
skipping datafile 1; already restored to file /u01/app/oracle/oradata/pink11/system01.dbf
skipping datafile 2; already restored to file /u01/app/oracle/oradata/pink11/sysaux01.dbf
skipping datafile 3; already restored to file /u01/app/oracle/oradata/pink11/undotbs01.dbf
skipping datafile 4; already restored to file /u01/app/oracle/oradata/pink11/users01.dbf
skipping datafile 5; already restored to file /u01/app/oracle/oradata/pink11/ggate_tbs_01.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/pink11/json01.dbf
restore not done; all files read only, offline, excluded, or already restored
Finished restore at 13-OCT-20
RMAN> recover database;
Starting recover at 13-OCT-20
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=169
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=170
channel ORA_DISK_1: reading from backup piece /home/oracle/a/PINK11_DB_20201013_bcvcrudk
channel ORA_DISK_1: piece handle=/home/oracle/a/PINK11_DB_20201013_bcvcrudk tag=TAG20201013T103636
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/19c/db/dbs/arch1_169_1048875119.dbf thread=1 sequence=169
archived log file name=/u01/app/oracle/19c/db/dbs/arch1_170_1048875119.dbf thread=1 sequence=170
RMAN> exit
3. Open Daabase in Upgrade
SQL> alter database open resetlogs upgrade;
Database altered.
SQL>
Database altered.
SQL>
# Regular upgrade command.
cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
# Shorthand command.
$ORACLE_HOME/bin/dbupgrade
[oracle@east01 a]$ cd /u01/app/oracle/19c/db/rdbms/admin/
[oracle@east01 admin]$ $ORACLE_HOME/bin/dbupgrade
Argument list for [/u01/app/oracle/19c/db/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.8.0.0.0DBRU_LINUX.X64_200702]
/u01/app/oracle/19c/db/rdbms/admin/orahome = [/u01/app/oracle/19c/db]
/u01/app/oracle/19c/db/bin/orabasehome = [/u01/app/oracle/19c/db]
catctlGetOraBaseLogDir = [/u01/app/oracle/19c/db]
Analyzing file /u01/app/oracle/19c/db/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20201013104144]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20201013104144/catupgrd_catcon_5487.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20201013104144/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20201013104144/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 2
Database Name = pink11
DataBase Version = 11.2.0.4.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/19c/db/cfgtoollogs/pink11/upgrade20201013104155/catupgrd_catcon_5487.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/19c/db/cfgtoollogs/pink11/upgrade20201013104155/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/19c/db/cfgtoollogs/pink11/upgrade20201013104155/catupgrd_*.lst] files for spool files, if any
Log file directory = [/u01/app/oracle/19c/db/cfgtoollogs/pink11/upgrade20201013104155]
Parallel SQL Process Count = 4
Components in [pink11]
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:[2020_10_13 10:42:10]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [pink11] Files:1 Time: 40s
*************** Catalog Core SQL ***************
Serial Phase #:1 [pink11] Files:5 Time: 26s
Restart Phase #:2 [pink11] Files:1 Time: 1s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [pink11] Files:19 Time: 10s
Restart Phase #:4 [pink11] Files:1 Time: 2s
************* Catalog Final Scripts ************
Serial Phase #:5 [pink11] Files:7 Time: 11s
***************** Catproc Start ****************
Serial Phase #:6 [pink11] Files:1 Time: 8s
***************** Catproc Types ****************
Serial Phase #:7 [pink11] Files:2 Time: 8s
Restart Phase #:8 [pink11] Files:1 Time: 2s
**************** Catproc Tables ****************
Parallel Phase #:9 [pink11] Files:67 Time: 14s
Restart Phase #:10 [pink11] Files:1 Time: 2s
************* Catproc Package Specs ************
Serial Phase #:11 [pink11] Files:1 Time: 35s
Restart Phase #:12 [pink11] Files:1 Time: 3s
************** Catproc Procedures **************
Parallel Phase #:13 [pink11] Files:94 Time: 6s
Restart Phase #:14 [pink11] Files:1 Time: 2s
Parallel Phase #:15 [pink11] Files:121 Time: 10s
Restart Phase #:16 [pink11] Files:1 Time: 2s
Serial Phase #:17 [pink11] Files:22 Time: 4s
Restart Phase #:18 [pink11] Files:1 Time: 2s
***************** Catproc Views ****************
Parallel Phase #:19 [pink11] Files:32 Time: 12s
Restart Phase #:20 [pink11] Files:1 Time: 3s
Serial Phase #:21 [pink11] Files:3 Time: 7s
Restart Phase #:22 [pink11] Files:1 Time: 2s
Parallel Phase #:23 [pink11] Files:25 Time: 69s
Restart Phase #:24 [pink11] Files:1 Time: 4s
Parallel Phase #:25 [pink11] Files:12 Time: 44s
Restart Phase #:26 [pink11] Files:1 Time: 2s
Serial Phase #:27 [pink11] Files:1 Time: 0s
Serial Phase #:28 [pink11] Files:3 Time: 3s
Serial Phase #:29 [pink11] Files:1 Time: 0s
Restart Phase #:30 [pink11] Files:1 Time: 3s
*************** Catproc CDB Views **************
Serial Phase #:31 [pink11] Files:1 Time: 2s
Restart Phase #:32 [pink11] Files:1 Time: 2s
Serial Phase #:34 [pink11] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [pink11] Files:294 Time: 14s
Serial Phase #:36 [pink11] Files:1 Time: 0s
Restart Phase #:37 [pink11] Files:1 Time: 2s
Serial Phase #:38 [pink11] Files:6 Time: 4s
Restart Phase #:39 [pink11] Files:1 Time: 3s
*************** Catproc DataPump ***************
Serial Phase #:40 [pink11] Files:3 Time: 27s
Restart Phase #:41 [pink11] Files:1 Time: 3s
****************** Catproc SQL *****************
Parallel Phase #:42 [pink11] Files:13 Time: 44s
Restart Phase #:43 [pink11] Files:1 Time: 1s
Parallel Phase #:44 [pink11] Files:11 Time: 7s
Restart Phase #:45 [pink11] Files:1 Time: 2s
Parallel Phase #:46 [pink11] Files:3 Time: 3s
Restart Phase #:47 [pink11] Files:1 Time: 3s
************* Final Catproc scripts ************
Serial Phase #:48 [pink11] Files:1 Time: 6s
Restart Phase #:49 [pink11] Files:1 Time: 2s
************** Final RDBMS scripts *************
Serial Phase #:50 [pink11] Files:1 Time: 10s
************ Upgrade Component Start ***********
Serial Phase #:51 [pink11] Files:1 Time: 2s
Restart Phase #:52 [pink11] Files:1 Time: 3s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [pink11] Files:2 Time: 201s
***************** Upgrading XDB ****************
Restart Phase #:54 [pink11] Files:1 Time: 3s
Serial Phase #:56 [pink11] Files:3 Time: 16s
Serial Phase #:57 [pink11] Files:3 Time: 5s
Parallel Phase #:58 [pink11] Files:10 Time: 5s
Parallel Phase #:59 [pink11] Files:25 Time: 6s
Serial Phase #:60 [pink11] Files:4 Time: 7s
Serial Phase #:61 [pink11] Files:1 Time: 0s
Serial Phase #:62 [pink11] Files:32 Time: 5s
Serial Phase #:63 [pink11] Files:1 Time: 0s
Parallel Phase #:64 [pink11] Files:6 Time: 9s
Serial Phase #:65 [pink11] Files:2 Time: 13s
Serial Phase #:66 [pink11] Files:3 Time: 41s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [pink11] Files:1 Time: 3s
Serial Phase #:69 [pink11] Files:1 Time: 4s
Parallel Phase #:70 [pink11] Files:2 Time: 28s
Restart Phase #:71 [pink11] Files:1 Time: 1s
Parallel Phase #:72 [pink11] Files:2 Time: 4s
Serial Phase #:73 [pink11] Files:2 Time: 5s
***************** Upgrading SDO ****************
Restart Phase #:74 [pink11] Files:1 Time: 2s
Serial Phase #:76 [pink11] Files:1 Time: 24s
Serial Phase #:77 [pink11] Files:2 Time: 5s
Restart Phase #:78 [pink11] Files:1 Time: 1s
Serial Phase #:79 [pink11] Files:1 Time: 13s
Restart Phase #:80 [pink11] Files:1 Time: 3s
Parallel Phase #:81 [pink11] Files:3 Time: 28s
Restart Phase #:82 [pink11] Files:1 Time: 2s
Serial Phase #:83 [pink11] Files:1 Time: 7s
Restart Phase #:84 [pink11] Files:1 Time: 1s
Serial Phase #:85 [pink11] Files:1 Time: 9s
Restart Phase #:86 [pink11] Files:1 Time: 2s
Parallel Phase #:87 [pink11] Files:4 Time: 44s
Restart Phase #:88 [pink11] Files:1 Time: 4s
Serial Phase #:89 [pink11] Files:1 Time: 4s
Restart Phase #:90 [pink11] Files:1 Time: 3s
Serial Phase #:91 [pink11] Files:2 Time: 8s
Restart Phase #:92 [pink11] Files:1 Time: 1s
Serial Phase #:93 [pink11] Files:1 Time: 2s
Restart Phase #:94 [pink11] Files:1 Time: 3s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [pink11] Files:1 Time: 15s
Restart Phase #:96 [pink11] Files:1 Time: 3s
*********** Final Component scripts ***********
Serial Phase #:97 [pink11] Files:1 Time: 3s
************* Final Upgrade scripts ************
Serial Phase #:98 [pink11] Files:1 Time: 288s
******************* Migration ******************
Serial Phase #:99 [pink11] Files:1 Time: 28s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [pink11] Files:1 Time: 3s
Serial Phase #:101 [pink11] Files:1 Time: 0s
Serial Phase #:102 [pink11] Files:1 Time: 40s
***************** Post Upgrade *****************
Serial Phase #:103 [pink11] Files:1 Time: 45s
**************** Summary report ****************
Serial Phase #:104 [pink11] Files:1 Time: 3s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [pink11] Files:1 Time: 2s
Serial Phase #:106 [pink11] Files:1 Time: 0s
Serial Phase #:107 [pink11] Files:1 Time: 22s
------------------------------------------------------
Phases [0-107] End Time:[2020_10_13 11:06:21]
------------------------------------------------------
Grand Total Time: 1452s
LOG FILES: (/u01/app/oracle/19c/db/cfgtoollogs/pink11/upgrade20201013104155/catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/19c/db/cfgtoollogs/pink11/upgrade20201013104155/upg_summary.log
Grand Total Upgrade Time: [0d:0h:24m:12s]
You have new mail in /var/spool/mail/oracle
4. Upgrade time zone
[oracle@east01 admin]$
SQL> startup
ORACLE instance started.
Total System Global Area 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 1241513984 bytes
Database Buffers 889192448 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> startup
ORACLE instance started.
Total System Global Area 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 1241513984 bytes
Database Buffers 889192448 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_14.dat 14 0
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_14.dat 14 0
SQL> SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.
Total System Global Area 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 1241513984 bytes
Database Buffers 889192448 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> -- Begin upgrade to the latest version.
SQL> SET SERVEROUTPUT ON
DECLARE
l_tz_version PLS_INTEGER;
BEGIN
l_tz_version := DBMS_DST.get_latest_timezone_version;
DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
DBMS_DST.begin_upgrade(l_tz_version);
END;
/
l_tz_version PLS_INTEGER;
BEGIN
l_tz_version := DBMS_DST.get_latest_timezone_version;
DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
DBMS_DST.begin_upgrade(l_tz_version);
END;
/
SQL> l_tz_version=32
An upgrade window has been successfully started.
PL/SQL procedure successfully completed.
An upgrade window has been successfully started.
PL/SQL procedure successfully completed.
SQL> SHUTDOWN IMMEDIATE;
STARTUP;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.
Total System Global Area 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 1241513984 bytes
Database Buffers 889192448 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL>
SQL> SET SERVEROUTPUT ON
DECLARE
l_failures PLS_INTEGER;
BEGIN
DBMS_DST.upgrade_database(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
DBMS_DST.end_upgrade(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/
l_failures PLS_INTEGER;
BEGIN
DBMS_DST.upgrade_database(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
DBMS_DST.end_upgrade(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/
SQL>
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "GGATE"."AQ$_QT$_OGG$RP2G_4_S"
Number of failures: 0
Table list: "GGATE"."AQ$_QT$_OGG$RP2R_5_S"
Number of failures: 0
Table list: "GGATE"."AQ$_QT$_OGG$RP2G_4_L"
Number of failures: 0
Table list: "GGATE"."AQ$_QT$_OGG$RP2R_5_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
DBMS_DST.upgrade_database : l_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : l_failures=0
PL/SQL procedure successfully completed.
SQL> SELECT * FROM v$timezone_file;
COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20
SELECT property_name, property_value
FROM database_properties
WHERE property_name LIKE 'DST_%'
ORDER BY property_name;
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "GGATE"."AQ$_QT$_OGG$RP2G_4_S"
Number of failures: 0
Table list: "GGATE"."AQ$_QT$_OGG$RP2R_5_S"
Number of failures: 0
Table list: "GGATE"."AQ$_QT$_OGG$RP2G_4_L"
Number of failures: 0
Table list: "GGATE"."AQ$_QT$_OGG$RP2R_5_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
DBMS_DST.upgrade_database : l_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : l_failures=0
PL/SQL procedure successfully completed.
SQL> SELECT * FROM v$timezone_file;
COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20
SELECT property_name, property_value
FROM database_properties
WHERE property_name LIKE 'DST_%'
ORDER BY property_name;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_32.dat 32 0
SQL>
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION 32
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
5. Gather stats
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
PL/SQL procedure successfully completed.
[oracle@east03 a]$ scp /u01/app/oracle/11g/db/cfgtoollogs/pink11/preupgrade/postupgrade_fixups.sql oracle@192.168.45.101:/home/oracle/a
oracle@192.168.45.101's password:
postupgrade_fixups.sql 100% 11KB 796.9KB/s 00:00
[oracle@east03 a]$
oracle@192.168.45.101's password:
postupgrade_fixups.sql 100% 11KB 796.9KB/s 00:00
[oracle@east03 a]$
6. Run Post upgrade scripts ( which we created on source database )
SQL> @postupgrade_fixups.sql
Session altered.
PL/SQL procedure successfully completed.
SQL> select cdb from v$database;
CDB
---
NO
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
BANNER_FULL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
BANNER_LEGACY CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 0
7. Convert non-cdb to cdb
--> create NON-CDB to CDB
Create New Container Database (CDB)
BEGIN
DBMS_PDB.DESCRIBE(
pdb_descr_file => '/home/oracle/a/pink11.xml');
END;
/
[oracle@east01 a]$ du -sk /home/oracle/a/pink11.xml
16 /home/oracle/a/pink11.xml
mkdir /u01/app/oracle/oradata/RED19/pink11/
[oracle@east01 ~]$ sp
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 13 12:54:18 2020
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
SQL> CREATE PLUGGABLE DATABASE pink11 USING '/home/oracle/a/pink11.xml'
MOVE
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/pink11/', '/u01/app/oracle/oradata/RED19/pink11/'); 2 3
Pluggable database created.
SQL> ALTER SESSION SET CONTAINER=pink11;
Session altered.
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PINK11 MOUNTED
SQL> ALTER PLUGGABLE DATABASE OPEN;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE SAVE STATE;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PINK11 READ WRITE NO
SQL>
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PINK11 MOUNTED
SQL> ALTER PLUGGABLE DATABASE OPEN;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE SAVE STATE;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PINK11 READ WRITE NO
SQL>
8 . Start goldengate from source to target Pont in time (check Rman backup script for time )
