I did blog about this topic a while back.
Theory is nice but it’s a big difference when you do it the first time with a non-lab deployment at a customer with a real Multitenant database with almost 100 PDBs.Then you learn that neither the documentation nor my previous blog post contain all the necessary steps.
My test setup
I have a simple deployment of just 5 PDBs. But it will showcase with my limited compute capabilities how the process should work.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 READ WRITE NO
6 PDB4 READ WRITE NO
7 PDB5 READ WRITE NO
Run preupgrade,jar
First of all download the newest version of preupgrade.jar from MOS Note:884522.1 and copy it to your system. Execute it with all PDBs in OPEN mode. It will generate consolidated logs and sql files. The files will be written into $ORACLE_BASE/cfgtoollogs/CDB1/preupgrade.
The output does look like:
$ java -jar /home/oracle/preupgrade.jar TEXT TERMINAL Report generated by Oracle Database Pre-Upgrade Information Tool Version 12.2.0.1.0 Upgrade-To version: 12.2.0.1.0 ======================================= Status of the database prior to upgrade ======================================= Database Name: CDB1 Container Name: CDB$ROOT Container ID: 1 Version: 12.1.0.2.0 Compatible: 12.1.0.2.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone File: 18 Database log mode: NOARCHIVELOG Readonly: FALSE Edition: EE Oracle Component Upgrade Action Current Status ---------------- -------------- -------------- Oracle Server [to be upgraded] VALID Real Application Clusters [to be upgraded] OPTION OFF Oracle XML Database [to be upgraded] VALID ============== BEFORE UPGRADE ============== Run /preupgrade_fixups_CDB_ROOT.sql to complete all of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'. REQUIRED ACTIONS ================ + Adjust TABLESPACE SIZES as needed. Auto 12.2.0.1.0 Tablespace Size Extend Min Size Action ---------- ---------- -------- ---------- ------ SYSAUX 550 MB ENABLED 647 MB None SYSTEM 700 MB ENABLED 758 MB None TEMP 20 MB ENABLED 150 MB None UNDOTBS1 475 MB ENABLED 400 MB None Note that 12.2.0.1.0 minimum sizes are estimates. If you plan to upgrade multiple pluggable databases concurrently, then you must ensure that the UNDO tablespace size is equal to at least the number of pluggable databases that you upgrade concurrently, multiplied by that minimum. Failing to allocate sufficient space can cause the upgrade to fail. + Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums. Parameter 12.2.0.1.0 minimum --------- ------------------ sga_target* 2260729856 * These minimum memory/pool sizes are recommended for the upgrade process RECOMMENDED ACTIONS =================== + (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 12.1.0.2 Oracle Database SQL Tuning Guide. ============= AFTER UPGRADE ============= Run /postupgrade_fixups_CDB_ROOT.sql to complete all of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'. REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS =================== + Upgrade the database time zone version using the DBMS_DST package. The database is using timezone datafile version 18 and the target 12.2.0.1.0 database ships with timezone datafile version 26. Oracle recommends using the most recent timezone data. For further information, refer to My Oracle Support Note 1585343.1. + (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. Report generated by Oracle Database Pre-Upgrade Information Tool Version 12.2.0.1.0 Upgrade-To version: 12.2.0.1.0 ======================================= Status of the database prior to upgrade ======================================= Database Name: CDB1 Container Name: PDB$SEED Container ID: 2 Version: 12.1.0.2.0 Compatible: 12.1.0.2.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone File: 18 Database log mode: NOARCHIVELOG Readonly: TRUE Edition: EE Oracle Component Upgrade Action Current Status ---------------- -------------- -------------- Oracle Server [to be upgraded] VALID Real Application Clusters [to be upgraded] OPTION OFF Oracle XML Database [to be upgraded] VALID ============== BEFORE UPGRADE ============== Run /preupgrade_fixups_PDB_SEED.sql to complete all of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'. REQUIRED ACTIONS ================ + Adjust TABLESPACE SIZES as needed. Auto 12.2.0.1.0 Tablespace Size Extend Min Size Action ---------- ---------- -------- ---------- ------ SYSAUX 355 MB ENABLED 542 MB None SYSTEM 210 MB ENABLED 561 MB None TEMP 20 MB ENABLED 150 MB None Note that 12.2.0.1.0 minimum sizes are estimates. If you plan to upgrade multiple pluggable databases concurrently, then you must ensure that the UNDO tablespace size is equal to at least the number of pluggable databases that you upgrade concurrently, multiplied by that minimum. Failing to allocate sufficient space can cause the upgrade to fail. RECOMMENDED ACTIONS =================== + (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 12.1.0.2 Oracle Database SQL Tuning Guide. ============= AFTER UPGRADE ============= Run /postupgrade_fixups_PDB_SEED.sql to complete all of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'. REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS =================== + Upgrade the database time zone version using the DBMS_DST package. The database is using timezone datafile version 18 and the target 12.2.0.1.0 database ships with timezone datafile version 26. Oracle recommends using the most recent timezone data. For further information, refer to My Oracle Support Note 1585343.1. + (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. Report generated by Oracle Database Pre-Upgrade Information Tool Version 12.2.0.1.0 Upgrade-To version: 12.2.0.1.0 ======================================= Status of the database prior to upgrade ======================================= Database Name: CDB1 Container Name: PDB1 Container ID: 3 Version: 12.1.0.2.0 Compatible: 12.1.0.2.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone File: 18 Database log mode: NOARCHIVELOG Readonly: FALSE Edition: EE Oracle Component Upgrade Action Current Status ---------------- -------------- -------------- Oracle Server [to be upgraded] VALID Real Application Clusters [to be upgraded] OPTION OFF Oracle XML Database [to be upgraded] VALID ...
I cut off here as the information will repeated as my PDBs are all generated from the PDB$SEED right now. But it’s important to understand that the log and the sql files are consolidated together.
Important:
When you see the tag (AUTOFIXUP) that means “we’ll take care”. The action will be execute by either the preupgrade_fixups.sql or the postupgrade_fixups.sql. No action required from your side.
In $ORACLE_BASE/cfgtoollogs/CDB1/preupgrade you’ll find also the files for each container in case you will need them.
-rw-r--r--. 1 oracle dba 3246 May 9 11:50 preupgrade_fixups_CDB_ROOT.sql -rw-r--r--. 1 oracle dba 5534 May 9 11:50 postupgrade_fixups_CDB_ROOT.sql -rw-r--r--. 1 oracle dba 4346 May 9 11:50 preupgrade_CDB_ROOT.log -rw-r--r--. 1 oracle dba 3246 May 9 11:50 preupgrade_fixups_PDB_SEED.sql -rw-r--r--. 1 oracle dba 5534 May 9 11:50 postupgrade_fixups_PDB_SEED.sql -rw-r--r--. 1 oracle dba 3937 May 9 11:50 preupgrade_PDB_SEED.log -rw-r--r--. 1 oracle dba 3230 May 9 11:50 preupgrade_fixups_PDB3.sql -rw-r--r--. 1 oracle dba 5518 May 9 11:50 postupgrade_fixups_PDB3.sql -rw-r--r--. 1 oracle dba 3926 May 9 11:50 preupgrade_PDB3.log -rw-r--r--. 1 oracle dba 3230 May 9 11:50 preupgrade_fixups_PDB2.sql -rw-r--r--. 1 oracle dba 5518 May 9 11:50 postupgrade_fixups_PDB2.sql -rw-r--r--. 1 oracle dba 3926 May 9 11:50 preupgrade_PDB2.log -rw-r--r--. 1 oracle dba 3230 May 9 11:50 preupgrade_fixups_PDB1.sql -rw-r--r--. 1 oracle dba 5518 May 9 11:50 postupgrade_fixups_PDB1.sql -rw-r--r--. 1 oracle dba 3926 May 9 11:50 preupgrade_PDB1.log -rw-r--r--. 1 oracle dba 3230 May 9 11:50 preupgrade_fixups_PDB4.sql -rw-r--r--. 1 oracle dba 5518 May 9 11:50 postupgrade_fixups_PDB4.sql -rw-r--r--. 1 oracle dba 3926 May 9 11:50 preupgrade_PDB4.log -rw-r--r--. 1 oracle dba 3230 May 9 11:50 preupgrade_fixups_PDB5.sql -rw-r--r--. 1 oracle dba 5518 May 9 11:50 postupgrade_fixups_PDB5.sql -rw-r--r--. 1 oracle dba 3926 May 9 11:50 preupgrade_PDB5.log -rw-r--r--. 1 oracle dba 27913 May 9 11:50 preupgrade.log -rw-r--r--. 1 oracle dba 12739 May 9 11:50 preupgrade_fixups.sql -rw-r--r--. 1 oracle dba 19669 May 9 11:50 postupgrade_fixups.sql
Please don’t delete any of the files named written by preupgrade.jar as they all are necessary to progress the actions later on.
Pre Upgrade
From the above output the only real action I will have to do is to change the value of SGA_TARGET and then execute the preupgrade_fixups.sql There’s no need to execute it by each PDB as the preupgrade_fixups.sql is a consolidated file having all actions for each PDB.
SQL> alter system set sga_target=2500M scope=spfile; System altered.
To execute preupgrade_fixups.sql now catcon.pl must be used – and (even though this is not documented in detail at the moment) you should leverage your compute power by specifying a parallel degree matching your CPU_COUNT. In my example I use -n 4 to have 4 workers execute the task.
$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \
-n 4 \
-d /u01/app/oracle/cfgtoollogs/CDB1/preupgrade \
-l /home/oracle/mike \
-b preupgrade_fixups \
preupgrade_fixups.sql
catcon: ALL catcon-related output will be written to /home/oracle/mike/preupgrade_fixups_catcon_22960.lst
catcon: See /home/oracle/mike/preupgrade_fixups*.log files for output generated by scripts
catcon: See /home/oracle/mike/preupgrade_fixups_*.lst files for spool files, if any
catcon.pl: completed successfull
Once this step has been completed successfully, I will SHUTDOWN IMMEDIATE the database, copy the spfile over, generate a new password file in the destination Oracle home and then progress the upgrade.
Multitenant database upgrade
Once I have copied the spfile and created a password file I can startup my database in UPGRADE mode and startup all PDBs in UPGRADE mode as well.
SQL*Plus: Release 12.2.0.1.0 Production on Tue May 9 13:37:28 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 2634022912 bytes
Fixed Size 8796240 bytes
Variable Size 520095664 bytes
Database Buffers 2097152000 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open upgrade;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MIGRATE YES
3 PDB1 MIGRATE YES
4 PDB2 MIGRATE YES
5 PDB3 MIGRATE YES
6 PDB4 MIGRATE YES
7 PDB5 MIGRATE YES
SQL> exit
Then I will kick-off the upgrade. You can leverage again the full CPU power of your system. In my case, as I have only two CPUs I will overload it a bit risking contention just to demonstrate some of the capabilities of catctl.pl.
The CDB$ROOT will be upgraded always first. With the -M option you can decide that it will stay for the entire upgrade, until the last PDB has been finished upgrading, in UPGRADE mode – or you let it on the default as I do meaning that the CDB$ROOT and each PDB become available as soon as they are upgraded. The -M option is suppose to generate a faster overall upgrade as it lowers potential contention in the dictionary of the CDB$ROOT. But you will see only a difference when you bring load to the database while PDBs get upgraded.
As I defined -n 6 here my CDB$ROOT will be upgraded by 6 workers. Once the PDBs get progressed, then 2 workers will take care on each PDB. And as -n 6 is set, 6 workers all together will be up meaning a maximum of 3 PDBs can be processed at one time.
Once a PDB has been upgraded the workers go on with the next accessible PDB based on the CON_ID. You can define also priority lists. Please see the documentation for further information.
$ cd /u01/app/oracle/product/12.2.0.1/rdbms/admin/
[CDB2] oracle@localhost:/u01/app/oracle/product/12.2.0.1/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l /home/oracle/mike catupgrd.sql
Argument list for [catctl.pl]
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 = /home/oracle/mike
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 6
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: [12.2.0.1.0]
STATUS: [production]
BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170125]
/u01/app/oracle/product/12.2.0.1/rdbms/admin/orahome = [/u01/app/oracle/product/12.2.0.1]
/u01/app/oracle/product/12.2.0.1/bin/orabasehome = [/u01/app/oracle/product/12.2.0.1]
catctlGetOrabase = [/u01/app/oracle/product/12.2.0.1]
Analyzing file /u01/app/oracle/product/12.2.0.1/rdbms/admin/catupgrd.sql
Log file directory = [/home/oracle/mike]
catcon: ALL catcon-related output will be written to [/home/oracle/mike/catupgrd_catcon_9755.lst]
catcon: See [/home/oracle/mike/catupgrd*.log] files for output generated by scripts
catcon: See [/home/oracle/mike/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 2
Database Name = CDB1
DataBase Version = 12.1.0.2.0
Parallel SQL Process Count (PDB) = 2
Parallel SQL Process Count (CDB$ROOT) = 6
Concurrent PDB Upgrades = 3
Generated PDB Inclusion:[PDB$SEED PDB1 PDB2 PDB3 PDB4 PDB5]
Components in [CDB$ROOT]
Installed [CATALOG CATPROC XDB]
Not Installed [APEX APS CATJAVA CONTEXT DV EM JAVAVM MGW ODM OLS ORDIM OWM RAC SDO WK XML XOQ]
------------------------------------------------------
Phases [0-115] Start Time:[2017_05_09 13:40:30]
Container Lists Inclusion:[CDB$ROOT] Exclusion:[NONE]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [CDB$ROOT] Files:1 Time: 33s
*************** Catalog Core SQL ***************
Serial Phase #:1 [CDB$ROOT] Files:5 Time: 55s
Restart Phase #:2 [CDB$ROOT] Files:1 Time: 0s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [CDB$ROOT] Files:19
While the upgrade is running you won’t see a correct ordered output anymore as there’s no guarantee that each PDB will get upgrade at time X in phase Y. Especially when you have many PDBs of different sizes and contents you may see PDB1 in phase 49 whereas PDB2 is already in phase 109.
*************** Catproc DataPump *************** Serial Phase #:40 [PDB1] Files:3 Time: 5s Restart Phase #:39 [PDB$SEED] Files:1 Time: 1s *************** Catproc DataPump *************** Serial Phase #:40 [PDB$SEED] Files:3 Time: 1s *************** Catproc DataPump *************** Serial Phase #:40 [PDB2] Files:3 Time: 103s Restart Phase #:41 [PDB2] Files:1 Time: 1s ****************** Catproc SQL ***************** Parallel Phase #:42 [PDB2] Files:13 Time: 104s Restart Phase #:41 [PDB$SEED] Files:1 Time: 0s ****************** Catproc SQL ***************** Parallel Phase #:42 [PDB$SEED] Files:13 Time: 105s Restart Phase #:41 [PDB1] Files:1 Time: 1s ****************** Catproc SQL *****************
After the upgrade is finished, the consolidated upg_summary.log will tell you all the details about duration of each upgrade for each PDB. You may recognize that the PDB$SEED will take always longer than any other upgrade. This is because the PDB$SEED gets recompiled as part of the upgrade already as it is untouchable. All other containers will need to get recompiled after the upgrade is finished.
Post Upgrade
Final steps are now the recompilation and the postupgrade_fixups.sql.
The status of my database after upgrade and before recompilation:
SQL*Plus: Release 12.2.0.1.0 Production on Tue May 9 16:11:51 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
5 PDB3 MOUNTED
6 PDB4 MOUNTED
7 PDB5 MOUNTED
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 READ WRITE NO
6 PDB4 READ WRITE NO
7 PDB5 READ WRITE NO
That does look good at first sight. But it isn’t actualy.
If you check the component’s STATUS in CDB_REGISTRY you’ll see that it is UPGRADED – thus it is not finished yet.
SQL> alter session set "_exclude_seed_cdb_view"=false;
Session altered.
SQL> select con_id, comp_id, status from cdb_registry order by 1,2;
CON_ID COMP_ID STATUS
---------- ---------- ----------
1 CATALOG UPGRADED
1 CATPROC UPGRADED
1 RAC UPGRADED
1 XDB UPGRADED
2 CATALOG VALID
2 CATPROC VALID
2 RAC OPTION OFF
2 XDB VALID
3 CATALOG UPGRADED
3 CATPROC UPGRADED
3 RAC UPGRADED
3 XDB UPGRADED
4 CATALOG UPGRADED
4 CATPROC UPGRADED
4 RAC UPGRADED
4 XDB UPGRADED
5 CATALOG UPGRADED
5 CATPROC UPGRADED
5 RAC UPGRADED
5 XDB UPGRADED
6 CATALOG UPGRADED
6 CATPROC UPGRADED
6 RAC UPGRADED
6 XDB UPGRADED
7 CATALOG UPGRADED
7 CATPROC UPGRADED
7 RAC UPGRADED
7 XDB UPGRADED
The recompilation is done with utlrp.sql but need to be executed with catcon.pl.
[CDB2] oracle@localhost:/u01/app/oracle/product/12.2.0.1/rdbms/admin $ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0.1/rdbms/admin/utlrp_catcon_5538.lst] catcon: See [/u01/app/oracle/product/12.2.0.1/rdbms/admin/utlrp*.log] files for output generated by scripts catcon: See [/u01/app/oracle/product/12.2.0.1/rdbms/admin/utlrp_*.lst] files for spool files, if any catcon.pl: completed successfully
Afterwards check if all the PDBs are OPEN UNRESTRICTED now.
SQL*Plus: Release 12.2.0.1.0 Production on Tue May 9 20:02:13 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 READ WRITE NO
6 PDB4 READ WRITE NO
7 PDB5 READ WRITE NO
As final step the postupgrade_fixups.sql needs to be executed:
$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 4 -d /u01/app/oracle/cfgtoollogs/CDB1/preupgrade -l /home/oracle/mike -b postupgrade_fixups postupgrade_fixups.sql catcon: ALL catcon-related output will be written to [/home/oracle/mike/postupgrade_fixups_catcon_28596.lst] catcon: See [/home/oracle/mike/postupgrade_fixups*.log] files for output generated by scripts catcon: See [/home/oracle/mike/postupgrade_fixups_*.lst] files for spool files, if any catcon.pl: completed successfully [CDB2] oracle@localhost:/u01/app/oracle/product/12.2.0.1/rdbms/admin
And a final check:
SQL> select con_id, owner, object_type, object_name from cdb_objects where status='INVALID' order by 1; no rows selected
All fine – database is upgraded. Final action could be to adjust the time zone but regarding this topic I may produce another blog post soon.
Please note that with Oracle Database 12.2 we offer the “dbupgrade” utility as well which simplifies the call to catctl.pl. See the documentation and Mouhamadou Diaw’s blog post.
–Mike
Thanks.
Great job!
Minor typo
In $ORACLE_BASE/cfgtoollos/CDB1/preupgrade you’ll find also the files for each container in case you will need them.
Should be
In $ORACLE_BASE/cfgtoollogs/CDB1/preupgrade…
Eagle eye!!! Awesome – thanks 🙂
Mike
Mike,
I think I might misunderstand something. You state that the only real action you have to take is to fix the SGA_TARGET. What about all the notices that your tablespace sizes are basically to small?
REQUIRED ACTIONS
================
+ Adjust TABLESPACE SIZES as needed.
Auto 12.2.0.1.0
Tablespace Size Extend Min Size Action
———- ———- ——– ———- ——
SYSAUX 355 MB ENABLED 542 MB None
SYSTEM 210 MB ENABLED 561 MB None
TEMP 20 MB ENABLED 150 MB None
If you do have to deal with those, what do you do about the PDB$SEED?
Tim,
I know that this is misleading – that’s why we change this in a newer version of the preupgrade.jar and make it an “information only” and are more precise in wording.
The column “ACTION” is the one which counts. It says “NONE” for all your tablespaces. Therefore no action necessary. Reason for this is that your tablespaces (or mine) are on AUTOEXTEND=ON – i.e. they can extend to the size required during the upgrade. Right now, they are too small – but we’ll extend them and therefore no action needed.
Cheers,
Mike
Thanks that helps out a lot.
Hi. Running preupgrade.jar to upgrade-to 12.2 would not work if you have secured your SYSDBA operating system account with password. Error message is:
ERROR – Unable to run sqlplus due to:
ERROR:
ORA-01017: invalid username/password; logon denied
I could not find any solution to this matter, sure will have to open an iTAR.
Regards.
Claudio,
at first preupgrade.jar is not run from SQL*Plus.
And from your message I’d assume that you have a general problem. Please see if this MOS note solves it for you:
MOS Note:730067.1 (Troubleshooting ORA-1031 or ORA-1017 Errors When Connecting As SYSDBA)
https://support.oracle.com/epmos/faces/DocumentDisplay?id=730067.1
Mike
As I do more testing on this I have another idea too. I think it’s a good idea to run this and maybe against all pluggables in advance:
Maybe the preupgrade will catch everything, but maybe it won’t.
select * from pdb_plug_in_violations where name =’PDB$SEED’;
Would welcome you thoughts and feedback.
Michael,
not sure if I got your point.
Are you proposing that preupgrade.jar should check pdb_plug_in_violations of the PDB$SEED as well?
Why that?
Thanks,
Mike
No. I’m saying before starting any upgrade on a container and pluggables you might start by checking for violations before you even run preupgrade.
Thanks
Michael,
what would be the benefit to check the plug_in_violations beforehand?
This view is populated when you do a plug_compatibility_check or when you attempt to plugin a PDB.
What is the relevance for a database upgrade?
Especially as the view is not well maintained and may contain a ton of useless information (please search for PDB_PLUG_IN_VIOLATIONS on the blog).
Cheers,
Mike
Because if the Seed has an issue it will cause the upgrade to fail. The seed generally won’t open in restrict to show it has an issue. Preupgrade might catch, but it might not. Just a thought.
Really like you blog.
Hi Michael,
understood – I will discuss this with the team.
Thanks,
Mike
Hi Mike,
Can you clarify or confirm what the documentation seems to say, and perhaps suggest a workaround if this is correct?
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/upgrd/using-preupgrade-information-tool-for-oracle-database.html#GUID-C4941221-8DC4-4DF8-A773-9F8B3FECF7D0
It certainly seems to imply that both the older and newer version Oracle homes have to be available, and we have to run the preupgrade tool from the older version home.
Our migration strategy involves restoring either an 11.2.0.4 or 12.1.0.2 directory to a 12.2.0.1 server and upgrading in place. We’ve used a variant of this strategy dating back to when we were upgrading to 11.2. Obviously, we don’t have the older home in this case – do we, in that case, skip the preupgrade.jar? Or is there some way to run it? Our attempts are running into
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/preupgrade.jar TERMINAL TEXT
ERROR –
ERROR – Unable to run sqlplus due to:
ERROR:
ORA-03114: not connected to ORACLE
ERROR:
ORA-03114: not connected to ORACLE
Thanks,
stephan
Hi Stephan,
you need to run preupgrade.jar on the source home in the previous environment. If the new home is not present on the old environment, you just copy in preupgrade.jar from MOS 884522.1.
If you have not done this step, you “could” restore the database to the new environment and start it in UPGRADE mode – but in this case you can’t run preupgrade.jar successfully anymore. You should skip it and trust your senses, meaning you may have to revert to a Guaranteed Restore Point if something failed.
Cheers,
Mike
Hi Mike,
That’s a great article.
Does the step remain same if our source database is RAC?
Yes, it does apply to RAC systems of course, too.
Cheers,
Mike
I am wondering why Oracle dbua is showing this:
Container Name : PDB$SEED
Cause : None of the fixed object tables have had stats collected.
Action : Gather statistics on fixed objects prior to the upgrade using the command: EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
Upgrading from 12.1 to 19
🙂
Hi Eugene,
please use AutoUpgrade. I refuse to answer DBUA questions since this is a tool from the dark ages to me with a ton of flaws never cured.
AU does it all right – DBUA does it not right.
Cheers
Mike