Is it possible to restore previous version RMAN database backup to a later version? Depending upon the supported upgrade paths, the lowest version I managed to restore to 19c (19.14) was 11g R2 (11.2.0.4)
Please note, my final goal is to convert this NON CDB database to PDB and attach to an existing CDB.
What you need?
- Intact RMAN online backup
- Enough memory and storage space available on your computer.
- Time and patience
- Oracle support access, for many of the documents mentioned later in the article.
Scenario (fictitious): I’m given a full RMAN backup without any other details. I don’t know the source server details other than that it was windows & the version of the database release was 11.2.0.4.
Step#1
Using DBCA created a new non CDB database with the target database name, eg “KAZEMA”. After shutting down database, stopped listener keeping Windows Service for Oracle database running. Physically deleted all data files including control, redo logs.
(You can create an empty instance using ORADIM. That means ORADIM will create Windows services necessary for Oracle. You will have to create the necessary folders. Not just that, you need create a parameter file (initDATABASENAME.ora) to start the instance. Refer the below example)
D:\Oracle19c>oradim -new -sid KAZEMA -syspwd Oracle123 -startmode auto
Enter password for Oracle service user:
OPW-00029: Password complexity failed for SYS user : Password must contain at least 1 special character.
Instance created.Sample parameter file (initKAZEMA.ora)
kazema.__data_transfer_cache_size=0
kazema.__db_cache_size=1509949440
kazema.__inmemory_ext_roarea=0
kazema.__inmemory_ext_rwarea=0
kazema.__java_pool_size=0
kazema.__large_pool_size=33554432
kazema.__oracle_base='D:\Oracle19c\db'#ORACLE_BASE set from environment
kazema.__pga_aggregate_target=1073741824
kazema.__sga_target=2147483648
kazema.__shared_io_pool_size=117440512
kazema.__shared_pool_size=469762048
kazema.__streams_pool_size=0
kazema.__unified_pga_pool_size=0
*.audit_file_dest='D:\Oracle19c\db\admin\KAZEMA\adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='D:\Oracle19c\db\oradata\KAZEMA\CONTROLFILE\control01.ctl','D:\Oracle19c\db\oradata\KAZEMA\CONTROLFILE\control02.ctl'
*.db_block_size=8192
*.db_name='KAZEMA'
*.diagnostic_dest='D:\Oracle19c\db'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=KAZEMAXDB)'
*.local_listener='LISTENER_KAZEMA'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1024m
*.processes=640
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2048m
*.undo_tablespace='UNDOTBS1'
*.max_string_size='STANDARD'Considering the fact that my target database name is “KAZEMA”, I have modified a copy of parameter file that I had. One of the most important factor you should pay attention here is to create all the physical paths mentioned in the parameter file. Example D:\Oracle19c\db\admin\KAZEMA\adump, D:\Oracle19c\db\oradata\KAZEMA\CONTROLFILE. Failing to find these physical paths might cause errors or your database will not start at all.
D:\Oracle19c\db\admin\KAZEMA has few more folders, like shown in the image. Please create them manually before starting the database.
Add *.max_string_size=’STANDARD’ to the parameter file, necessary for the upgrade. You will have to change this parameter once again after upgrade completes. We will discuss about it later in the article.
SQL> startup nomount (pfile=D:\Oracle19c\database\initKAZEMA.ora # if instance was created using ORADIM)
ORACLE instance started.
Total System Global Area 2147482424 bytes
Fixed Size 9030456 bytes
Variable Size 503316480 bytes
Database Buffers 1627389952 bytes
Redo Buffers 7745536 bytesFrom another elevated command prompt started RMAN & restored control file from backup that I had in “D:\BAHRMAN” folder.
D:\BAHRMAN>set ORACLE_SID=KAZEMA
D:\BAHRMAN>rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Mar 23 09:52:14 2022
Version 19.14.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
connected to target database: KAZEMA (DBID=964058362, not open)
RMAN>restore controlfile from 'D:\BAHRMAN\CTL_C-964058362-20210927-02';
RMAN>alter database mount;
RMAN>report schema; #This will populate the data file details
Shutdown the database now. Restart the database in “nomount” mode using parameter file.
Once database shutdown and restarted, exit RMAN and connect again. We’ll duplicate database using the RMAN backup now using “NOOPEN” switch that is supported from versions 12.
D:\BAHRMAN>rman auxiliary /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Mar 23 09:56:42 2022
Version 19.14.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: KAZEMA (not mounted)
run
{
ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c3 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c4 DEVICE TYPE disk;
duplicate database to "KAZEMA" NOOPEN backup location 'D:\BAHRMAN' nofilenamecheck
db_file_name_convert=('D:\ORACLE\ORADATA\KAZEMA\','D:\Oracle19c\db\oradata\KAZEMA\DATAFILE\')
LOGFILE
GROUP 1 (
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo01a.log',
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo01b.log'
) SIZE 1200M ,
GROUP 2 (
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo02a.log',
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo02b.log'
) SIZE 1200M ,
GROUP 3 (
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo03a.log',
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo03b.log'
) SIZE 1200M ,
GROUP 4 (
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo04a.log',
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo04b.log'
) SIZE 1200M ;
# 19c & later, no need to exclusively release channels. If you restoring to other versions, make sure to release the channels.
# https://www.thegeekdiary.com/oracle-database-19c-rman-06012-channel-d1-not-allocated/
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}
allocated channel: c1
channel c1: SID=498 device type=DISK
allocated channel: c2
channel c2: SID=619 device type=DISK
allocated channel: c3
channel c3: SID=742 device type=DISK
allocated channel: c4
channel c4: SID=862 device type=DISK
Starting Duplicate Db at 23-MAR-22
searching for database ID
found backup of database ID 964058362
contents of Memory Script:
.....Removed........
datafile 9 switched to datafile copy
input datafile copy RECID=8 STAMP=1100081253 file name=D:\ORACLE19C\DB\ORADATA\KAZEMA\DATAFILE\LC01.DBF
datafile 10 switched to datafile copy
input datafile copy RECID=9 STAMP=1100081253 file name=D:\ORACLE19C\DB\ORADATA\KAZEMA\DATAFILE\BAHAR01.DBF
Leaving database unopened, as requested
Finished Duplicate Db at 23-MAR-22
#You will see the below errors for 19c and later while trying to release the channels exclusively. Remove the release channel instructions from the RMAN scripts to avoid seeing these messages.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of release command at 03/23/2022 10:07:32
RMAN-06012: channel: c1 not allocated
RMAN>Ignore errors by the end of RMAN process, if they are related to releasing channels. If there are errors of other nature, you must resolve them. Now we will open the database resetting logs and ready to upgrade.
D:\Oracle19c>sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 23 10:09:51 2022
Version 19.14.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0
SQL> alter database open resetlogs upgrade;
Database altered.As we have restored a RMAN backup, it is obvious that the source database had archive logs enabled. Let us disable archive logging from our new database. Shutdown the database and open it in mount mode.
SQL> startup mount pfile=D:\Oracle19c\database\initKAZEMA.ora
ORACLE instance started.
Total System Global Area 2147482424 bytes
Fixed Size 9030456 bytes
Variable Size 503316480 bytes
Database Buffers 1627389952 bytes
Redo Buffers 7745536 bytes
SQL> alter database noarchivelog;SQL> shutdown immediate;
SQL> startup upgrade pfile=D:\Oracle19c\database\initKAZEMA.oraNow switch to %ORACLE_HOME%\perl\bin folder and call catctl.pl to upgrade the database.
D:\Oracle19c\perl\bin>perl D:\Oracle19c\rdbms\admin\catctl.pl -n 4 -l D:\Oracle19c\ D:\Oracle19c\rdbms\admin\catupgrd.sqlThis is a long process for many reasons. First of all, no prerequisites were executed and recommended actions were performed against the source 11G database for 19c upgrade. This will cause upgrade process to fix prerequisites during upgrade & brace for few errors. Without MAX_STRING_SIZE set as ‘STANDARD’ for 11g, upgrade will fail. I have started the database instance using parameter file ONLY for this reason. I’m not sure whether RMAN restore crated SPFILE copies this value. So, I didn’t take chances.
Argument list for [D:\Oracle19c\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 = D:\Oracle19c\
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 4
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.14.0.0.0DBRU_WINDOWS.X64_211224.3]
D:\Oracle19c\rdbms\admin\orahome.exe = [D:\Oracle19c]
D:\Oracle19c\bin\orabasehome.exe = [D:\Oracle19c]
catctlGetOraBaseLogDir = [D:\Oracle19c]
Analyzing file D:\Oracle19c\rdbms\admin\catupgrd.sql
Log file directory = [D:\Oracle19c]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [D:\Oracle19c\catupgrd_catcon_8056.lst]
catcon::set_log_file_base_path: catcon: See [D:\Oracle19c\catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [D:\Oracle19c\catupgrd_*.lst] files for spool files, if any
Number of Cpus = 8
Database Name = KAZEMA
DataBase Version = 11.2.0.4.0
Parallel SQL Process Count = 4
Components in [KAZEMA]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV MGW ODM OLS RAC WK]
DataBase Version = 11.2.0.4.0
------------------------------------------------------
Phases [0-107] Start Time:[2022_03_23 10:18:27]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [KAZEMA] Files:1 Time: 273s
*************** Catalog Core SQL ***************
Serial Phase #:1 [KAZEMA] Files:5 Time: 31s
Restart Phase #:2 [KAZEMA] Files:1 Time: 1s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [KAZEMA] Files:19 Time: 11s
Restart Phase #:4 [KAZEMA] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [KAZEMA] Files:7 Time: 14s
***************** Catproc Start ****************
Serial Phase #:6 [KAZEMA] Files:1 Time: 11s
***************** Catproc Types ****************
Serial Phase #:7 [KAZEMA] Files:2 Time: 6s
Restart Phase #:8 [KAZEMA] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [KAZEMA] Files:70 Time: 11s
Restart Phase #:10 [KAZEMA] Files:1 Time: 1s
************* Catproc Package Specs ************
Serial Phase #:11 [KAZEMA] Files:1 Time: 42s
Restart Phase #:12 [KAZEMA] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [KAZEMA] Files:95 Time: 4s
Restart Phase #:14 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:15 [KAZEMA] Files:122 Time: 6s
Restart Phase #:16 [KAZEMA] Files:1 Time: 0s
Serial Phase #:17 [KAZEMA] Files:25 Time: 2s
Restart Phase #:18 [KAZEMA] Files:1 Time: 0s
***************** Catproc Views ****************
Parallel Phase #:19 [KAZEMA] Files:32 Time: 8s
Restart Phase #:20 [KAZEMA] Files:1 Time: 0s
Serial Phase #:21 [KAZEMA] Files:3 Time: 8s
Restart Phase #:22 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:23 [KAZEMA] Files:25 Time: 157s
Restart Phase #:24 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:25 [KAZEMA] Files:12 Time: 96s
Restart Phase #:26 [KAZEMA] Files:1 Time: 0s
Serial Phase #:27 [KAZEMA] Files:1 Time: 0s
Serial Phase #:28 [KAZEMA] Files:4 Time: 2s
Serial Phase #:29 [KAZEMA] Files:1 Time: 0s
Restart Phase #:30 [KAZEMA] Files:1 Time: 1s
*************** Catproc CDB Views **************
Serial Phase #:31 [KAZEMA] Files:1 Time: 0s
Restart Phase #:32 [KAZEMA] Files:1 Time: 0s
Serial Phase #:34 [KAZEMA] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [KAZEMA] Files:297 Time: 19s
Serial Phase #:36 [KAZEMA] Files:1 Time: 0s
Restart Phase #:37 [KAZEMA] Files:1 Time: 1s
Serial Phase #:38 [KAZEMA] Files:10 Time: 3s
Restart Phase #:39 [KAZEMA] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [KAZEMA] Files:3 Time: 40s
Restart Phase #:41 [KAZEMA] Files:1 Time: 0s
****************** Catproc SQL *****************
Parallel Phase #:42 [KAZEMA] Files:13 Time: 91s
Restart Phase #:43 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:44 [KAZEMA] Files:11 Time: 7s
Restart Phase #:45 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:46 [KAZEMA] Files:3 Time: 1s
Restart Phase #:47 [KAZEMA] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [KAZEMA] Files:1 Time: 9s
Restart Phase #:49 [KAZEMA] Files:1 Time: 0s
************** Final RDBMS scripts *************
Serial Phase #:50 [KAZEMA] Files:1 Time: 15s
************ Upgrade Component Start ***********
Serial Phase #:51 [KAZEMA] Files:1 Time: 0s
Restart Phase #:52 [KAZEMA] Files:1 Time: 1s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [KAZEMA] Files:2 Time: 363s
***************** Upgrading XDB ****************
Restart Phase #:54 [KAZEMA] Files:1 Time: 0s
Serial Phase #:56 [KAZEMA] Files:3 Time: 22s
Serial Phase #:57 [KAZEMA] Files:3 Time: 1s
Parallel Phase #:58 [KAZEMA] Files:10 Time: 2s
Parallel Phase #:59 [KAZEMA] Files:25 Time: 3s
Serial Phase #:60 [KAZEMA] Files:4 Time: 4s
Serial Phase #:61 [KAZEMA] Files:1 Time: 0s
Serial Phase #:62 [KAZEMA] Files:32 Time: 2s
Serial Phase #:63 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:64 [KAZEMA] Files:6 Time: 5s
Serial Phase #:65 [KAZEMA] Files:2 Time: 14s
Serial Phase #:66 [KAZEMA] Files:3 Time: 59s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [KAZEMA] Files:1 Time: 0s
Serial Phase #:69 [KAZEMA] Files:1 Time: 1s
Parallel Phase #:70 [KAZEMA] Files:2 Time: 25s
Restart Phase #:71 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:72 [KAZEMA] Files:2 Time: 1s
Serial Phase #:73 [KAZEMA] Files:2 Time: 1s
***************** Upgrading SDO ****************
Restart Phase #:74 [KAZEMA] Files:1 Time: 0s
Serial Phase #:76 [KAZEMA] Files:1 Time: 29s
Serial Phase #:77 [KAZEMA] Files:2 Time: 1s
Restart Phase #:78 [KAZEMA] Files:1 Time: 1s
Serial Phase #:79 [KAZEMA] Files:1 Time: 14s
Restart Phase #:80 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:81 [KAZEMA] Files:3 Time: 28s
Restart Phase #:82 [KAZEMA] Files:1 Time: 0s
Serial Phase #:83 [KAZEMA] Files:1 Time: 4s
Restart Phase #:84 [KAZEMA] Files:1 Time: 0s
Serial Phase #:85 [KAZEMA] Files:1 Time: 8s
Restart Phase #:86 [KAZEMA] Files:1 Time: 1s
Parallel Phase #:87 [KAZEMA] Files:4 Time: 56s
Restart Phase #:88 [KAZEMA] Files:1 Time: 0s
Serial Phase #:89 [KAZEMA] Files:1 Time: 1s
Restart Phase #:90 [KAZEMA] Files:1 Time: 0s
Serial Phase #:91 [KAZEMA] Files:2 Time: 7s
Restart Phase #:92 [KAZEMA] Files:1 Time: 0s
Serial Phase #:93 [KAZEMA] Files:1 Time: 0s
Restart Phase #:94 [KAZEMA] Files:1 Time: 1s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [KAZEMA] Files:1 Time: 18s
Restart Phase #:96 [KAZEMA] Files:1 Time: 0s
*********** Final Component scripts ***********
Serial Phase #:97 [KAZEMA] Files:1 Time: 2s
************* Final Upgrade scripts ************
Serial Phase #:98 [KAZEMA] Files:1 Time: 61s
******************* Migration ******************
Serial Phase #:99 [KAZEMA] Files:1 Time: 30s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [KAZEMA] Files:1 Time: 1s
Serial Phase #:101 [KAZEMA] Files:1 Time: 0s
Serial Phase #:102 [KAZEMA] Files:1 Time: 80s
***************** Post Upgrade *****************
Serial Phase #:103 [KAZEMA] Files:1 Time: 49s
**************** Summary report ****************
Serial Phase #:104 [KAZEMA] Files:1 Time: 0s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [KAZEMA] Files:1 Time: 1s
Serial Phase #:106 [KAZEMA] Files:1 Time: 0s
Serial Phase #:107 [KAZEMA] Files:1 Time: 41s
------------------------------------------------------
Phases [0-107] End Time:[2022_03_23 10:48:42]
------------------------------------------------------
Grand Total Time: 1847s
*** WARNING: ERRORS FOUND DURING UPGRADE ***
1. Evaluate the errors found in the upgrade logs
and determine the proper action.
2. Rerun the upgrade when the problem is resolved
REASON:
ERRORS FOUND: During Upgrade
FILENAME: D:\Oracle19c\catupgrd0.log AT LINE NUMBER: 1124126
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR = [ORA-13509: error encountered during updates to a AWR table ORA-12899: value too large for column ORA-12899: value too large for column "SYS"."OBJ$"."SUBNAME" (actual: 31, maximum: 30)
(actual: , maximum: )
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 524
ORA-06512: at line 3
]
STATEMENT = [BEGIN
/* Perform the required AWR catalog operations for this release */
dbms_swrf_internal.refresh_catalog;
END;]
------------------------------------------------------
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR = [ORA-12899: value too large for column ORA-12899: value too large for column "SYS"."OBJ$"."SUBNAME" (actual: 31, maximum: 30)
(actual: , maximum: )
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 524
ORA-06512: at line 3
]
STATEMENT = [as above]
------------------------------------------------------
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR = ["SYS"."OBJ$"."SUBNAME" (actual: 31, maximum: 30) (actual: , maximum: )
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 524
ORA-06512: at line 3
]
STATEMENT = [as above]
------------------------------------------------------
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR = [(actual: , maximum: ) ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 524
ORA-06512: at line 3
]
STATEMENT = [as above]
------------------------------------------------------
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR = [ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 524 ORA-06512: at line 3
]
STATEMENT = [as above]
------------------------------------------------------
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR = [ORA-06512: at line 3]
STATEMENT = [as above]
------------------------------------------------------
LOG FILES: (D:\Oracle19c\catupgrd*.log)
Upgrade Summary Report Located in:
D:\Oracle19c\upg_summary.log
Grand Total Upgrade Time: [0d:0h:30m:47s]I didn’t remove lines from the output to give you an overview of what happened during the initial “catupgrd.sql” call without MAX_STRING_SIZE set as “STANDARD“. As you can see, it has generated few errors & we need to fix them.
ERROR = [ORA-12899: value too large for column ORA-12899: value too large for column “SYS”.”OBJ$”.”SUBNAME” (actual: 31, maximum: 30) is pretty easy to fix. This is mainly due to strings lengths & follow the below steps to fix it.
Move to %ORACLE_HOME%\rdbms\admin folder.
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
alter system set MAX_STRING_SIZE=EXTENDED;
@utl32k.sql
SHUTDOWN IMMEDIATE;
STARTUP; Once the script runs successfully, shutdown the database once again and start in upgrade mode. Rerun the “catupgrd.sql” as instructed above. Let the upgrade finish. Insure there are no errors. A successfully finished upgrade process will look like below. I mentioned earlier, this process could be pretty time taking based on the size of the database you are upgrading. My entire database was less than 5GB in size & I’ve NVMe+SSD based work laptop running i7 10th generation processor & 32GB memory. The waiting was frustrating.
Serial Phase #:101 [KAZEMA] Files:1 Time: 0s
Serial Phase #:102 [KAZEMA] Files:1 Time: 78s
***************** Post Upgrade *****************
Serial Phase #:103 [KAZEMA] Files:1 Time: 23s
**************** Summary report ****************
Serial Phase #:104 [KAZEMA] Files:1 Time: 1s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [KAZEMA] Files:1 Time: 1s
Serial Phase #:106 [KAZEMA] Files:1 Time: 0s
Serial Phase #:107 [KAZEMA] Files:1 Time: 36s
------------------------------------------------------
Phases [0-107] End Time:[2022_03_23 15:15:34]
------------------------------------------------------
Grand Total Time: 1209s
LOG FILES: (D:\Oracle19c\catupgrd0\catupgrd*.log)
Upgrade Summary Report Located in:
D:\Oracle19c\catupgrd0\upg_summary.log
Grand Total Upgrade Time: [0d:0h:20m:9s]I came across the errors while I called the upgrade and went ahead and fixed them. Kept the same in the article so that the search engine could pick up the errors for someone else out there. Now it is time for to recompile all invalid objects after upgrade.
SQL>@utlrp.sqlUnless you have a huge number of PL/SQL libraries and other, this should complete in few minutes. A count for invalid objects will be provided to you & you can proceed to fix them once after a database restart. Keep a tight watch on the alert log file and address additional errors, if any.
Finally create SPFILE from current parameter file, shutdown and restart the database. You should be good to go now. Check for the Temporary files, Redo Log files and other vitals.
These invalid objects are from custom schema & could be fixed/dropped if not needed. Make sure there are no system level invalid objects.
Another area of interest could be OLAP.
Check and confirm OLAP objects are not invalid. OLAP catalog was deprecated after 11g. You may proceed removing it if needed.
Reference articles
- https://shivanandarao-oracle.com/2015/09/16/rman-restore-backup-of-lower-version-database-to-a-higher-version/
- Upgrade using RMAN Duplicate with Noopen clause and Backup Location (Doc ID 2022820.1)
- How to Convert Non-CDB to PDB Database on same local host machine in 12c – Testcase (Doc ID 2012448.1)
- Utlu122s.sql Fails with ‘ORA-13509: error encountered during updates to a AWR table’ During Upgrade to 12c From 11g (Doc ID 2524115.1)
- How to Increase the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in 12C Database using MAX_STRING_SIZE ? (Doc ID 1570297.1)
- 12.2 : Database upgrade fails with error “SELECT TO_NUMBER(’32K_MIGRATION_NOT_COMPLETED’)” (Doc ID 2344458.1)
Footnote: For a PRODUCTION environment, please follow standard procedures like running upgrade check, prerequisites etc.