Friday, May 16, 2025

Oracle Documentation and Advantages of Reading Other Resources

After I published the first post of the series (#1, #2) I received a private request to talk a bit more about 11.2-to-19c upgrade. Considering that frankly speaking that's the most common upgrade case nowadays (well, one long-term release to the other one!) I can definitely understand the importance of real-world experience for anybody who plans to do the same.

So, here is the story - or to be precise two stories.
  1. Production/Stage/External Test - these ones were handled by our partner DBAs, but the idea was pretty straightforward: copy cold backup to the new environment + run in-place upgrade (non-CDB). Same OS (Linux-to-Linux), preserving of character sets etc. I.e. everything is well-documented and clear. So, nothing much to write about.
  2. Development and Internal Test - that one was a real head-scratcher that I'd like to share with everybody. The task was as follows:
    • Starting points: Windows 2008 Server with 11.2.0.4 database and Win1252 charset, Windows 2012 Server with 19c database win Win1252 charset (non-CDB). Each DB is about 200 GB
    • Required destination: Amazon AWS Linux instance with 19c database with two environments as PDBs that would allow creating ALUTF32 PDBs if needed.
As you can see, a lot of transformation and less-then-common transfer path. One of the reasons - you just can't install 19c database to run in-place upgrade using AutoUpgrade and other nice things on Windows 2008 server. So, with the help of our system administrators I came up with the plan that eventually lead to success. And that's where "other-then-documentation" resources really helped - some pieces of knowledge I took from Tim Hall's Oracle-Base, some pieces are from Mike Dietrich, some pieces from other people just posting their ideas and experiences in their blogs and various online resources. Overall, without the community I would experiment forever! And that's why I like so much to be part of this knowledge-sharing environment - and try to contribute myself.

I started from the bottom -  discussed with sysadmins a good and reliable way to moving something to AWS. The answer I've got was interesting: there was a simple  process to port existing virtual machine from our internal cloud to AWS. That made my life much simple - it meant that we could build a local VM, play with it as much as needed (with a very easy way to create multiple saverpoints/restore to any point etc.) and eventually declare it ready to be transferred. Ok, admins got Oracle Linux ISO and made me in vSphere a new server - now I've got a destination.

Next step was to see whether my understanding of OS internal was true and it was possible to move PDB from Windows to Linux without any major hustle... To do that I first converted my 19c instance from non-CDB to a PDB within newly created PDB. That was just a couple of commands:

-- non-CDB instance
shutdown;
startup open read only
/
BEGIN
  DBMS_PDB.DESCRIBE(
    pdb_descr_file => 'f:\oradata\test01.xml');
END;
/
shutdown immediate
/

-- CDB instance
CREATE PLUGGABLE DATABASE test01 
using 'f:\oradata\test01.xml'
NOCOPY 
tempfile reuse 
/
ALTER SESSION SET CONTAINER=test01;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql;
ALTER PLUGGABLE DATABASE OPEN;

Now it was time to move PDB to Linux box, that already had CDB configured as AL32UTF... And after not finding the answer on whether file_name_convert operator can change Windows-style file name to Linux-style I just went into moveText.xml file and manually updated all locations to which I transferred  PDB files (btw, later I tested the operator - and I couldn't make it do what I wanted, so, hint: sometimes manual approach is your choice ;) )

-- Windows CDB
ALTER PLUGGABLE DATABASE test01 CLOSE;
ALTER PLUGGABLE DATABASE test01 UNPLUG INTO 'F:\moveTest.xml';

-- Linux CDB
CREATE PLUGGABLE DATABASE test01 
using '/home/oracle/test01_updated.xml' -- Linux locations
NOCOPY 
tempfile reuse 
/
ALTER SESSION SET CONTAINER=test01;
ALTER PLUGGABLE DATABASE OPEN;

The last command finished - but with the warning. It was time to review existing violations

select *
from pdb_plug_in_violations
where name = 'TEST01'
and status !='RESOLVED'

The most only errors I found was the mismatch of patches - and that was simply fixed by running datapatch - verbose on the Linux side: the system easily rolled back all of the incorrect patches within PDB and placed what was needed. Seemingly the system was ready to operate - I could query data, run some sample procedures... But the application didn't start! After some digging I found a somewhat unexpected reason - even though you could move Windows PDB to Linux, if you are using PL/SQL objects with Native compilation - you have to recompile everything (well, makes sense, different platforms = different low-level code!). The following loop did that:

 begin
    for c in (
        select owner, name, type
        from dba_plsql_object_settings
        where plsql_code_type !='INTERPRETED'
        )
    loop
        begin
            if c.type = 'PACKAGE BODY' then
                execute immediate 'alter '||c.type||' '                                   ||c.owner||'.'||c.name||' compile body';
            else
                execute immediate 'alter '||c.type||' '                                   ||c.owner||'.'||c.name||' compile';
            end if;
        exception
            when others then null;
        end;
    end loop;
end;

That was easy enough - so I decided to replicate exactly the same pattern for the main development database (that was 11.2.0.4). So, in my Test database I dropped PDB Test01 with all datafiles (I had to do it because Dev files names overlapped with Test01 and I didn't want to shuffle anything around) and used good-old-and tried approach:
  1. Original DB
    • Create PFILE from SPFILE
    • Run preupgrade on the source and follow its recommendations
    • Shutdown the instance
    • Transfer files to the new location preserving the file structure (as for the cold backup):
      • ORADATA\ora11g folder
      • FAST_RECOVERY AREA\ora11g folder with all corresponding directories
      • PFILE
      • Control files from other locations (btw, you can integrate them into the same folder by modifying PFILE)
  2. Server with 19c
    • Create new instance:
      • orapwd file=? password=? format=12 (lower format if you want simple password for now)
      • oradim
      • startup upgrade
    • Upgrade via dbupgrade utility
    • Run post-fixes, including DST upgrade
    • Recompile whatever needed and resolve obscure cases if exist (Java classes, SDO_Geometry etc.)
Now I was pretty exactly where I started with Test environment - non-CDB instance + empty container. So, the following steps are exactly above: convert instance into PDB, plug it in as Dev01, upgrade, fix PL/SQL native-compiled objects. That's all, folks - two environments became one on the different platform! After that it was just lift-and-shift to AWS - and we've got our new environment.

Summary: if you can use somebody else's experience - use it! Oracle documentation, especially about upgrades is huge, but sometimes you may be looking for something very specific and it may be hard to detect exactly your use-case in the superset of the knowledge. 

P.s. VM Snapshots are your saviors (that's why I love the cloud). You can't imagine how many various issues I've got in process - and without snapshot recovery it would've take wa-a-a-a-y longer to do anything meaningful.

No comments: