Upgrade Oracle database to 19C - Using conventional dbupgrade utility
Upgrade Oracle database to 19C - Using Conventional dbupgrade utility
1) Upgrade Path for Oracle 19C
Upgrade Matrix | |
Source | Target |
11.2.0.4 | 19c |
12.1.0.2 | 19c |
12.2.0.1 | 19c |
18.1 | 19c |
Intermediate upgrades needs to be carried for following releases
Indirect Upgrade Matrix | ||||
Source Database | Intermediate upgrade path | Target database | ||
12.1.0.1 | --> | 12.1.0.2/12.2.0.1 | --> | 19c |
11.2.0.1/11.2.0.2/11.2.0.3 | --> | 11.2.0.4 | --> | 19c |
11.1.0.6/11.1.0.7 | --> | 11.2.0.4 | --> | 19c |
10.2.0.2, 10.2.0.3, 10.2.0.4, 10.2.0.5 | --> | 11.2.0.4/12.1.0.2 | --> | 19c |
10.1.0.5 | --> | 11.2.0.4/12.1.0.2 | --> | 19c |
9.2.0.8 or earlier | --> | 11.2.0.4 | --> | 19c |
2) Changes and Unsupported Oracle components in Oracle 19c
- Desupport for Oracle Multimedia -
Oracle Multimedia is desupported in Oracle Database 19c, and the implementation is removed.As an alternative for image processing and conversion, Oracle recommends that you store multimedia content in SecureFiles LOBs, and use third party products, such as Piction. The ORDIM component remains in the registry and still has a VALID status. Oracle Multimedia objects and packages remain in the database. However, these objects and packages no longer function, and raise exceptions if there is an attempt made to use them. Oracle Locator is not affected by the desupport of Oracle Multimedia. - Desupport of Oracle Streams:
Starting in Oracle Database 19c (19.1), Oracle Streams is desupported. Oracle GoldenGate is the replication solution for Oracle Database.Note that Oracle Database Advanced Queuing is not deprecated, and is fully supported in Oracle Database 19c. Oracle Streams did not support features added in Oracle Database 12c (12.1) and later releases, including the multitenant architecture, LONG VARCHAR, and other new features. Oracle Streams replication functionality is superseded by GoldenGate. Preupgrade check "STREAMS_SETUP" will warn if Oracle Streams is presented. To remove Oracle streams in 18c refer to Oracle documentation, similarly refer to section "Removing an Oracle Streams Configuration" in the Oracle Streams Concepts and Administration Guide specific for the Oracle release from which you are removing.
3) Requirements and recommendations for source database
- Take a cold or hot back up of the source database.
- Disable any custom triggers that would get executed before / after DDL. You can enable it once upgrade is completed.
- Data security roles defined in a release 11g Oracle Database instance are not automatically converted to ORAS. Before you upgrade an 11g database to Oracle Database 19c, you must delete any data security roles that are defined in the 11g database. After the upgrade, you may use Analytic Workspace Manager 19c to define the data security roles again.
- If you upgrade an 11g database to Oracle Database 19c without deleting the 11g data security roles, then any data security policies that include a data security role are invalid in the Oracle Database 19c database.
- If your source version's time zone is already higher than the destination's home, then apply the matching or any higher time zone patch to your destination home before upgrade. For latest DST patch refer to Note 412160.1
- IF
APEX is installed then it is recommended to upgrade APEX in the source DB
first before upgrading DB. Refer to Note
1088970.1
Upgrade Apex:
Current Version:
-----------------
COMP_NAME STATUS VERSION
---------------------- ------------ ------------------------------
Oracle Application Express VALID 5.0.4.00.12
SQL> select count(*) from APEX_050000.WWV_FLOWS where id = 4000;
COUNT(*)
----------
1
If the query returns 0, it is a runtime only installation, and apxrtins.sql should be used for the upgrade.
If the query returns 1, this is a development install and apexins.sql should be used.
Create tablespace for apex:
--------------------------------
create tablespace apex DATAFILE 'G:\ORADATA\DEVDB\APEX.DBF' SIZE 256M REUSE AUTOEXTEND ON NEXT 128M MAXSIZE 5G;
create tablespace apex_file DATAFILE 'G:\ORADATA\DEVDB\APEX_FILE.DBF' SIZE 256M REUSE AUTOEXTEND ON NEXT 128M MAXSIZE 5G;
Backup Existing Apex directory in ORALCE HOME:
---------------------------------------------------
E:\app\Oradb\product\12.2.0\dbhome_1
Unzip new apex in oracle home.
Copy new apex from E:\app\Oradb\local\scripts\upgrade\apex to E:\app\Oradb\product\12.2.0\dbhome_1
unzip apex_20.1_en.zip in apex directory
Upgrade APEX:
--------------
run cmd as admin
set ORACLE_HOME=E:\app\Oradb\product\12.2.0\dbhome_1
set ORACLE_SID=devdb
cd E:\app\Oradb\product\12.2.0\dbhome_1\apex
sqlplus / as sysdba
spool E:\app\Oradb\local\scripts\upgrade\apex_upgrade.log
@apexins.sql APEX APEX_FILE TEMP /I/
spool off
Create an instance administrator user, if it does not exist and set their password
@apxchpwd.sql
user=apexadmin
if needed:
Create the APEX_LISTENER and APEX_REST_PUBLIC_USER users, if it does not exist
@apex_rest_config.sql
Make sure the APEX_PUBLIC_USER account is unlocked.
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY xxxx ACCOUNT UNLOCK;
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY xxxx ACCOUNT UNLOCK;
select COMP_ID, VERSION, STATUS from dba_registry where COMP_ID like 'APEX%';
SYS> select COMP_ID, VERSION, STATUS from dba_registry where COMP_ID like 'APEX%';
COMP_ID VERSIO
------------------------------ ------------------------------
STATUS
--------------------------------------------
APEX 20.1.0.00.13
VALID - No INVALID object / Components in Source
- Execute Preupgrade scripts before upgrade and review the preupgrade log for any issues.
- Execute dbupgdiag.sql (refer Note 556610.1 to download this script) and verify whether there are any INVALID components or objects owned by SYS/SYSTEM. If any, fix them before proceeding to upgrade the database. You can execute utlrp.sql multiple times to VALIDate them, if still objects are INVALID, create a service request with Oracle support.
4) Requirements and recommendations for target ORACLE_HOME
- Verify the whether your operating system is certified for 19c.
- Download and install latest RU if any
- Copy spfile or pfile from source ORACLE_HOME (under $ORACLE_HOME/dbs) to target home
- Remove any _ (underscore) parameter, obsolete and deprecated parameters in pfile
- Note min value of COMPATIBLE parameter to upgrade 19c is “11.2.0”, make sure you have COMPATIBLE parameter is set to 11.2.0 or greater
- Review patch recommendations as given in the article "Patches to apply before upgrading Oracle GI and DB to 19c (Doc ID 2539751.1)"
5) Pre-upgrade checks
- Clean up Database
Empty the recycle bin
Check for INVALID objects in SYS and SYSTEM
Check for duplicate objects in SYS and SYSTEM
Check for INVALID, mandatory, obsolete component
Note: These will be taken care by preupgrade.jar. - Check materialized view
Check the status of all materialized views (MV), and refresh any materialized views that are not fresh.
Check the size of your materialized view logs. If any materialized view logs have non-zero rows, then refresh the base table materialized views.
Check the size of direct loader logs and PMOP logs (partition maintenance operation logs). If any direct loader logs or PMOP logs have non-zero rows, then refresh the MVs indicated by the logsBefore upgrading Oracle Database, you must wait until all materialized views have completed refreshing. Run below query for Checking the mview refresh:SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8; - Schema-Only Accounts and
Upgrading EXPIRED Password Accounts
Before starting your upgrade, determine if you want to use password authenticate to default Oracle Database accounts where their passwords are in EXPIRED status, and their account is in LOCKED status
During upgrades to Oracle Database 19c, default Oracle accounts that have not had their passwords reset before upgrade (and are set to EXPIRED status), and that are also set to LOCKED status, are set to NO AUTHENTICATION after the upgrade is complete.
Because of this new feature, default accounts that are changed to schema-only accounts become unavailable for password authentication. The benefit of this feature is that administrators no longer have to periodically rotate the passwords for these Oracle Database-provided schemas. This feature also reduces the security risk of attackers using default passwords to hack into these accounts.
If you want to prevent these Oracle accounts from being set to schema-only accounts during the upgrade, then you must either set a valid strong password for the account before you start the upgrade, or set a valid strong password for these accounts after upgrade, or unlock the accounts before you log in to the upgraded Oracle Database.
After the upgrade, an administrator can also enable password authentication for schema-only accounts. However, for better security, Oracle recommends that you keep these accounts as schema only accounts. Copying Transparent Encryption Oracle Wallets
If Oracle wallet used with Transparent Data Encryption (TDE), then copy the sqlnet.ora and wallet file to the new Oracle home. You must copy the sqlnet.ora and the wallet file manually before starting the upgrade.
1. Log in as an authorized user.
2. Manually copy the sqlnet.ora file, and the wallet file, ewallet.p12, to the new release Oracle home.
Open the Oracle wallet in mount.
For example:
SQL> STARTUP MOUNT
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPENUnderstanding Password Case Sensitivity
Starting with Oracle Database 12c release 2 (12.2), the default password-based authentication protocol configuration excludes the use of the case-insensitive 10G password version. By default, the SQLNET.ORA parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12, which is an Exclusive Mode.
For greater security, Oracle recommends that you leave case-sensitive password-based authentication enabled. This setting is the default. However, you can temporarily disable case-sensitive authentication during the upgrade to new Oracle Database releases. After the upgrade, you can then decide if you want to enable the case-sensitive password-based authentication feature as part of your implementation plan to manage your password versions.
Before upgrading, Oracle recommends that you determine if this change to the default password-based authentication protocol configuration affects you. Perform the following checks:
Identify if you have accounts that use only 10G case-insensitive password authentication versions.
Identify if you have Oracle Database 11g release 2 (11.2.0.3) database or earlier clients that have not applied critical patch update CPUOct2012, or a later patch update, and have any account that does not have the case-insensitive 10G password version.
Ensure that you do not have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSE. Setting this parameter to FALSE prevents the use of the case-sensitive password versions (the 11G and 12C password versions) for authentication.
For more information refer 19c Oracle database documentationRunning Upgrades with Read-Only Tablespace
Running Upgrades with Read-Only Tablespaces
Use the Parallel Upgrade Utility with the -T option to take schema-based tablespaces offline during upgrade. Oracle Database can read file headers created in earlier releases, so you are not required to do anything to them during the upgrade. The file headers of READ ONLY tablespaces are updated when they are changed to READ WRITE. If the upgrade suffers a catastrophic error, so that the upgrade is unable to bring the tablespaces back online, then review the upgrade log files. The log files contain the actual SQL statements required to make the tablespaces available. To bring the tablespaces back online, you must run the SQL statements in the log files for the database, or run the log files for each PDB
6) Preparing the New Oracle Home for Upgrading
- Copy configuration files from the Oracle home of the database being upgraded to the new release Oracle Database Oracle home.
- If you have a password file that resides within the old environment Oracle home, then move or copy the password file to the new Oracle home. It is recommended to recreate the password file freshly to leverage the new features (if any) provided by orapwd utility.
- Remove desupported initialization parameters and adjust deprecated initialization parameters. In new releases, some parameters are desupported, and other parameters are deprecated. Remove all desupported parameters from any parameter file that starts the new Oracle Database instance. Desupported parameters can cause errors in new Oracle Database releases. Also, alter any parameter whose syntax has changed in the new release.
- If you are upgrading a cluster database, then you need to change CLUSTER_DATABASE=FALSE before starting upgrade
- Databases That Use Oracle Label
Security and Oracle Database Vault
Audit Table Preupgrade and Archive Requirements
The olspreupgrade.sql script creates a temporary table PREUPG_AUD$ in the SYS schema and moves the SYSTEM.aud$ records to SYS.PREUPG_AUD$. As a safety measure, Oracle recommends that you archive your audit trail before running the olspreupgrade.sql script. If Oracle Label Security is installed on your database, and you are upgrading from an earlier release, then you must run the OLS preprocess script before upgrading.
For Oracle Database releases earlier than 12.1 using Oracle Label Security and Oracle Database Vault, you must run the OLS preprocess script before you upgrade.
If you are upgrading from a database earlier than Oracle Database release 12.1 that uses Oracle Label Security (OLS) and Oracle Database Vault, then you must first run the OLS preprocess script, olspreupgrade.sql, to process the aud$ table contents. The OLS upgrade moves the aud$ table from the SYSTEM schema to the SYS schema. The olspreupgrade.sql script is a preprocessing script required for this move.
Running the olspreupgrade.sql script before upgrading is mandatory for upgrading databases earlier than Oracle Database release 12.1 that use Oracle Label Security and Oracle Database Vault. Once you have upgraded to Oracle Database release 12.1, you do not have to perform the OLS preprocessing procedure going forward to patch or upgrade the database.
Oracle Database Vault and Upgrades of Oracle Database Release 11.2
If Oracle Label Security is installed in the earlier release that you are upgrading, then grant the DV_PATCH_ADMIN role to SYS.
To run the OLS preprocess script on a release 11.2 database before upgrading:
1. Copy the following scripts script from the newly installed Oracle home (19c) to the Oracle home of the database that needs to be upgraded (11.2)
ORACLE_HOME/rdbms/admin/olspreupgrade.sql
ORACLE_HOME/rdbms/admin/emremove.sql
ORACLE_HOME/olap/admin/catnoamd.sql
2. Start SQL*Plus and connect as DVOWNER to the database that you want to upgrade.
3. Run the following statement:
SQL> GRANT DV_PATCH_ADMIN to SYS;
4. At the system prompt, connect SYS as SYSDBA
CONNECT SYS AS SYSDBA
5. Run the preprocess scripts for Data Vault
ORACLE_HOME/rdbms/admin/olspreupgrade.sql
ORACLE_HOME/rdbms/admin/emremove.sql
ORACLE_HOME/olap/admin/catnoamd.sql
You may continue to run your applications on the database while the preprocess scripts are running.
6. After the olspreupgrade.sql completes its run successfully, start SQL*Plus and connect to the database as DVOWNER.
7. Run the following SQL statement:
SQL> REVOKE DV_PATCH_ADMIN from SYS;
Granting the DV_PATCH_ADMIN Role to SYS for Oracle Database Vault
If Oracle Database Vault is enabled, then to perform checks for Oracle Data Vault, the upgrade process requires running three SQL scripts - olspreupgrade.sql, emremove.sql, catnoamd.sql
Start SQL*Plus and connect as DVOWNER to the database that you want to upgrade
Run the following statement:
SQL> GRANT DV_PATCH_ADMIN to SYS;
Manually remove DB control with emremove.sql
Stop/shutdown DB contro
emctl stop dbconsole
Login as sysdba - SQL>SET ECHO ON
SQL>SET SERVEROUTPUT ON
SQL>@emremove.sql >> Script located in new 12c ORACLE_HOME/rdbms/admin
7) Ensure additional check like no datafile is in backup mode ,Audit records are purged ,Gather stats are done and backup of the database information con be capture.
set time on
set timing on
set serveroutput on
set trimspool on
set lines 200 pages 2000
col comp_name for a50
@?/rdbms/admin/utlrp.sql
select comp_id,comp_name,status,version from dba_registry;
select * from sys.registry$history order by 1 desc;
select * from registry$sqlpatch;
col ACTION_TIME for a40
select PATCH_ID,DESCRIPTION,ACTION_TIME from dba_registry_sqlpatch order by 3;
select count(1),owner,object_type,status from dba_objects where status <> 'VALID' group by owner,object_type,status;
col owner for a30
col object_name for a50
select owner,object_name,object_type,status from dba_objects where status <> 'VALID' order by owner,object_name,object_type;
SELECT * FROM v$timezone_file;
SELECT tz_version FROM registry$database;
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;
SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;
SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
PURGE DBA_RECYCLEBIN;
select count(1) from FGA_LOG$;
select count(1) from AUD$;
select count(*) from unified_audit_trail;
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
select '==============Database Link backup==============' from dual;
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;
select name from v$datafile;
select name from v$tempfile;
select member name from v$logfile;
select name from v$tablespace;
select name from v$controlfile;
col grantee for a30
col granted_role for a30
SELECT grantee, granted_role FROM dba_role_privs order by grantee;
col username for a30
col password for a30
col account_status for a30
col profile for a30
col EXPIRY_DATE for a30
select username,password,account_status,profile,EXPIRY_DATE from dba_users order by profile;
col version format a12
col comp_name format a40
col status format a10
SELECT Comp_ID, Status, Version, Comp_Name FROM DBA_Registry ORDER by 1,2;
col ACTION_TIME format a30
col COMMENTS format a30
col ACTION format a10
select * From dba_registry_history;
col object_name format a30
col owner format a20
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE STATUS = 'INVALID' ORDER BY OWNER, OBJECT_NAME, OBJECT_TYPE;
col tablespace_name format a30 heading "Tablespaces|Name"
col total_space format 999,990.00 heading "Current Total|Space (Gb)"
col free_space format 999,990.00 heading "Current Free|Space (Gb)"
col pct_free format 990.00 heading "Current|% Free"
col pct_used format 990.00 heading "Current|% Used"
select t.tablespace_name, s.total_space, f.free_space, (f.free_space / s.total_space) * 100 pct_free , (s.total_space-f.free_space)/s.total_space*100 pct_used
from dba_tablespaces t, (select tablespace_name, sum(user_bytes) / 1073741824 total_space, sum(decode(autoextensible, 'YES', maxbytes, user_bytes)) / 1073741824 extensible
from dba_data_files group by tablespace_name) s, (select tablespace_name, sum(bytes) / 1073741824 free_space from dba_free_space group by tablespace_name) f
where s.tablespace_name = t.tablespace_name and f.tablespace_name = t.tablespace_name ;
8) Preupgrade step
Execute Preupgrade script from source home
Set environment variable to source home and database:
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 7 on 2020-08-09T11:38:01
Status of the database prior to upgrade
=======================================
Database Name: UATDB
Container Name: UATDB
Container ID: 0
Version: 12.2.0.1.0
DB Patch Level: WINDOWS DB BUNDLE PATCH 12.2.0.1.190416(64bit):29394003
Compatible: 12.2.0
Blocksize: 8192
Platform: Microsoft Windows x86 64-bit
Timezone File: 26
Database log mode: NOARCHIVELOG
Readonly: FALSE
Edition: EE
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Real Application Clusters [to be upgraded] OPTION OFF
Oracle Workspace Manager [to be upgraded] VALID
OLAP Analytic Workspace [to be upgraded] VALID
Oracle Label Security [to be upgraded] VALID
Oracle Database Vault [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
Oracle OLAP API [to be upgraded] VALID
BEFORE UPGRADE
==============
================
1. (AUTOFIXUP) Empty the RECYCLEBIN immediately before database upgrade.
===================
2. Run 12.2.0.1.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
objects. You can view the individual invalid objects with
EXECUTE DBMS_PREUP.INVALID_OBJECTS;
database upgrade.
setting, to configure your system to use case sensitive password
authentication by default.
authentication (the SEC_CASE_SENSITIVE_LOGON instance initialization
parameter setting is FALSE).
password-based authentication mode. Case insensitive password
authentication is not supported in Exclusive Mode. If your system needs
to use case insensitive password authentication, Exclusive Mode must be
switched off prior to the upgrade. See the Network Reference Manual
chapter about the SQLNET.ORA parameter
SQLNET.ALLOWED_LOGON_VERSION_SERVER for more details on Exclusive Mode.
empty before doing upgrade, unless you have strong business reasons not
to do so. You can use dbms_mview.refresh() to refresh the MVs except
those stale ones to be kept due to business need. If there are any stale
MVs depending on changes in sys.sumdelta$, do not truncate it, because
doing so will cause wrong results after refresh. Please refer to the
Materialized View section in MOS Note 2380601.1 for more details.
state, or which are currently being refreshed.
upgrading the database because this will clear the MV logs and the
sumdelta$ table and may reduce the upgrade time. If you choose to not
refresh some MVs, the change data for those MV's will be carried through
the UPGRADE process. After UPGRADE, you can refresh the MV's and MV
incremental refresh should work in normal cases.
upgrade in off-peak time using:
execution plans and are essential for proper upgrade timing. Oracle
recommends gathering dictionary statistics in the last 24 hours before
database upgrade.
Oracle Database SQL Tuning Guide.
================
6. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.
Tablespace Size For Upgrade
---------- ---------- -----------
SYSTEM 890 MB 1002 MB
---------
sec_case_sensitive_logon
to manage an RMAN recovery catalog schema.
than that required by the RMAN client version, then you must upgrade the
catalog schema.
than the RMAN client version you are using.
=============================
All of the issues in database UATDB
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
l
AFTER UPGRADE
=============
================
None
===================
9. (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.
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.
ships with time zone file version 32.
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.
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.
object paths used with BFILE data types, the UTL_FILE package, or
external tables.
command:
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.
representative workload on the system using the command:
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.
Oracle Database SQL Tuning Guide.
=============================
All of the issues in database UATDB
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
ql
==================
PREUPGRADE SUMMARY
==================
D:\app\Oracle\virtual\cfgtoollogs\UATDB\preupgrade\preupgrade.log
D:\app\Oracle\virtual\cfgtoollogs\UATDB\preupgrade\preupgrade_fixups.sql
D:\app\Oracle\virtual\cfgtoollogs\UATDB\preupgrade\postupgrade_fixups.sql
@D:\app\Oracle\virtual\cfgtoollogs\UATDB\preupgrade\preupgrade_fixups.sql
@D:\app\Oracle\virtual\cfgtoollogs\UATDB\preupgrade\postupgrade_fixups.sql
9) Start the upgrade
set PATH=D:\app\oracle_new\product\19.0.0\dbhome_1\bin;%PATH%
set ORACLE_BASE=D:\app\oracle_new
SQL> startup upgrade;
SQL> exit
10) Make Sure all the components are valid in database.
11)Upgrade the Time Zone File Version After Upgrading Oracle Database
SELECT tz_version FROM registry$database;
COLUMN property_value FORMAT A20
FROM database_properties
WHERE property_name LIKE 'DST_%'
ORDER BY property_name;
SELECT DBMS_DST.get_latest_timezone_version
FROM dual;
l_tz_version PLS_INTEGER;
BEGIN
l_tz_version := DBMS_DST.get_latest_timezone_version;
DBMS_DST.begin_prepare(l_tz_version);
END;
/
COLUMN property_value FORMAT A20
FROM database_properties
WHERE property_name LIKE 'DST_%'
ORDER BY property_name;
If you are using custom tables, created with the CREATE_AFFECTED_TABLE, CREATE_ERROR_TABLE, CREATE_TRIGGER_TABLE procedures, then empty those instead.
TRUNCATE TABLE sys.dst$error_table;
Find tables affected by the upgrade. Depending on your use of TIMESTAMP WITH TIME ZONE columns, you might not have any.
Check the results of the call.
SELECT * FROM sys.dst$error_table;
When you've identified the affected tables and determined you are happy to continue, you can end the prepare phase.
Individual tables or schemas can be upgraded, but in this example we use UPGRADE_DATABASE procedure to upgrade all affected tables in the database.
STARTUP UPGRADE;
Begin the upgrade to the latest version.
DECLARE
l_tz_version PLS_INTEGER;
BEGIN
SELECT DBMS_DST.get_latest_timezone_version
INTO l_tz_version
FROM dual;
DBMS_DST.begin_upgrade(l_tz_version);
END;
/
Restart the database.
STARTUP;
Do the upgrade of the database file zone file.
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;
/
The {CDB|DBA|ALL|USER}_TSTZ_TABLES views display the tables that are processed by the time zone file upgrade, and
their current upgrade status. The following examples show how they could be used for a CDB an non-CDB database.
COLUMN owner FORMAT A30
COLUMN table_name FORMAT A30
owner,
table_name,
upgrade_in_progress
FROM cdb_tstz_tables
ORDER BY 1,2,3;
COLUMN owner FORMAT A30
COLUMN table_name FORMAT A30
table_name,
upgrade_in_progress
FROM dba_tstz_tables
ORDER BY 1,2;
COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20
FROM database_properties
WHERE property_name LIKE 'DST_%'
12) Upgrading Statistics Tables
13)Check and change compatible parameter:
We would prefer to use DBUA method where all these steps are automatied
==========================================================
Please check our other blogs for Upgrade.
Comments
Post a Comment
Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !