Thursday 17 June 2021

Patch - Applying patch for upgrading database time zone from 32 to 35

OCI autonomous database having time zone 35 and on premises database having 32timezone. We face error on impdp.

This blog is about applying patch and upgrading time zone.

Error: ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 35 into a target database with TSTZ version 32.

On Premises database name : PDBTEST (standalone DB)

OCI Autonomous database name: ORCLADB 

  •  Importing impdp dump  from autonomous database to on premesis database

 D:\test\WINDOWS.X64_193000_db_home\bin>impdp \"sys/system123@pdbtest as sysdba\" directory=STAGING_DIR schemas=soe remap_schema=soe:soe2 dumpfile=adsoe01.dmp,adsoe02.dmp transform=segment_attributes:n encryption_pwd_prompt=yes
Import: Release 19.0.0.0.0 - Production on Tue Jun 8 15:43:26 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Encryption Password:
ORA-39002: invalid operation
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 35 into a target database with TSTZ version 32.


  • On premises database timezone 

 SQL> SELECT * FROM V$TIMEZONE_FILE;
FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat              32          0
SQL>
  • Autonomous database timezone

 SQL> SELECT * FROM V$TIMEZONE_FILE;
FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_35.dat              35          0
  • Check doc id for patch information 

NOTE: Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1)

Note 2676922.1 Applying the DSTv35 update for the Oracle Database

  • Set pre requestors for pre patch Apply on database

 

C:\Windows\system32>SET ORACLE_HOME=D:\test\WINDOWS.X64_193000_db_home
C:\Windows\system32>SET PATH=D:\test\WINDOWS.X64_193000_db_home\OPatch;%PATH%
C:\Windows\system32>SET ORACLE_SID=PDBTEST
C:\Windows\system32>SET PATH=%ORACLE_HOME%\perl\bin;%PATH%
C:\Windows\system32>cd D:\test\WINDOWS.X64_193000_db_home\OPatch
C:\Windows\system32>D:
D:\test\WINDOWS.X64_193000_db_home\OPatch>opatch version
OPatch Version: 12.2.0.1.15
OPatch succeeded.

C:\Users\varun\OneDrive\Desktop\oci\p31335037_190000_MSWIN-x86-64>opatch prereq CheckConflictAgainstOHWithDetail -ph ./31335037
Oracle Interim Patch Installer version 12.2.0.1.15
Copyright (c) 2021, Oracle Corporation.  All rights reserved.
PREREQ session
Oracle Home       : D:\test\WINDOWS.X64_193000_db_home
Central Inventory : C:\Program Files\Oracle\Inventory
   from           :
OPatch version    : 12.2.0.1.15
OUI version       : 12.2.0.7.0
Log file location : D:\test\WINDOWS.X64_193000_db_home\cfgtoollogs\opatch\opatch2021-06-08_16-50-57PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

C:\Users\varun\OneDrive\Desktop\oci\p31335037_190000_MSWIN-x86-64>cd 31335037
C:\Users\varun\OneDrive\Desktop\oci\p31335037_190000_MSWIN-x86-64\31335037>opatch apply -analyze
Oracle Interim Patch Installer version 12.2.0.1.15
Copyright (c) 2021, Oracle Corporation.  All rights reserved.
Oracle Home       : D:\test\WINDOWS.X64_193000_db_home
Central Inventory : C:\Program Files\Oracle\Inventory
   from           :
OPatch version    : 12.2.0.1.15
OUI version       : 12.2.0.7.0
Log file location : D:\test\WINDOWS.X64_193000_db_home\cfgtoollogs\opatch\opatch2021-06-08_16-52-14PM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   31335037

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
You are calling OPatch with -ocmrf option while this OPatch is generic, not being bundled with OCM. The -ocmrf option is being deprecated. Please remove it while calling OPatch.
Backing up files...
Applying interim patch '31335037' to OH 'D:\test\WINDOWS.X64_193000_db_home'
Users request no RAC file generation.  Do not create MP files.
Skip patching component oracle.oracore.rsf, 19.0.0.0.0 and its actions.
The actions are reported here, but are not performed.
ApplySession skipping inventory update.
Patch 31335037 successfully applied.
Log file location: D:\test\WINDOWS.X64_193000_db_home\cfgtoollogs\opatch\opatch2021-06-08_16-52-14PM_1.log
OPatch succeeded.
C:\Users\varun\OneDrive\Desktop\oci\p31335037_190000_MSWIN-x86-64\31335037>opatch apply
Oracle Interim Patch Installer version 12.2.0.1.15
Copyright (c) 2021, Oracle Corporation.  All rights reserved.
Oracle Home       : D:\test\WINDOWS.X64_193000_db_home
Central Inventory : C:\Program Files\Oracle\Inventory
   from           :
OPatch version    : 12.2.0.1.15
OUI version       : 12.2.0.7.0
Log file location : D:\test\WINDOWS.X64_193000_db_home\cfgtoollogs\opatch\opatch2021-06-08_16-56-04PM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   31335037
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...
Applying interim patch '31335037' to OH 'D:\test\WINDOWS.X64_193000_db_home'
Patching component oracle.oracore.rsf, 19.0.0.0.0...
Patch 31335037 successfully applied.
Log file location: D:\test\WINDOWS.X64_193000_db_home\cfgtoollogs\opatch\opatch2021-06-08_16-56-04PM_1.log
OPatch succeeded.
  • Shutdown database and upgrade time zone window.
 

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Warning: You are no longer connected to ORACLE.
SQL> exit
C:\Users\varun\OneDrive\Desktop\oci\p31335037_190000_MSWIN-x86-64\31335037>sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 8 17:25:08 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> STARTUP UPGRADE;
ORACLE instance started.
Total System Global Area 5133824504 bytes
Fixed Size                  9277944 bytes
Variable Size             989855744 bytes
Database Buffers         4127195136 bytes
Redo Buffers                7495680 bytes
Database mounted.
Database opened.
SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2   l_tz_version PLS_INTEGER;
  3  BEGIN
  4   SELECT DBMS_DST.get_latest_timezone_version
  5   INTO l_tz_version
  6   FROM DUAL;
  7   DBMS_OUTPUT.PUT_LINE('l_tz_version=' || l_tz_version);
  8   DBMS_DST.BEGIN_UPGRADE(l_tz_version);
  9  END;
 10  /
l_tz_version=35
An upgrade window has been successfully started.
PL/SQL procedure successfully completed.
  • Shutdown database again startup normal mode , Upgrade  and check the timezone
 

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 5133824504 bytes
Fixed Size                  9277944 bytes
Variable Size             989855744 bytes
Database Buffers         4127195136 bytes
Redo Buffers                7495680 bytes
Database mounted.
Database opened.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2   l_failures PLS_INTEGER;
  3  BEGIN
  4   DBMS_DST.UPGRADE_DATABASE(l_failures);
  5   DBMS_OUTPUT.PUT_LINE('DBMS_DST.upgrade_database : l_failures=' || l_failures);
  6   DBMS_DST.End_UPGRADE(l_failures);
  7   DBMS_OUTPUT.PUT_LINE('DBMS_DST.end_upgrade : l_failures=' || l_failures);
  8  END;
  9  /
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
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
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>
SQL> SELECT * FROM v$timezone_file;
FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_35.dat              35          0
SQL>

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
35

DST_SECONDARY_TT_VERSION
0
DST_UPGRADE_STATE
NONE

Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)