The not so AUTO ...UPGRADE ! (ORACLE Autoupgrade in Windows )

Intro

First, before anyone starts sending me print screens about their successful Autoupgrade, or flexing about how their 100+ databases were smoothly migrated to PDBs over the weekend, I am stopping you right there. This is my honest feedback on the experience I had when trying the tool on a windows environment. It is obviously not a rant but could have been after 2 lonesome and painful months of sheer struggle with Oracle support. I had even shared the issues I went through with the community in twitter but I just happened to be the lucky one to have hit that special Bug. There are enough blog posts showing you the glamourous side of Autoupgrade, let me introduce you... the Hitchcockian version.

The Plot 

It all started last summer when my client, whom had Oracle Standard edition databases all over his windows fleet, asked about available approaches and downtime to migrate Peoplesoft DBs from 12c (non CDB)  to 19c multitenant architecture. At that time, Autoupgrade hype was still kicking and I’d watched all recordings of Mike Dietrich. Although most examples were based on Linux I felt confident it’ll be a piece of cake on windows too (never trust the hype) and sold that to my client.  

My environment :

Platform	Source non CDB database SI	Target CDB SI	Hypervisor	Application
Windows server 2012	12.1.0.2 Standard Edition	19.7.0.0 Standard Edition	Yes (Vmware)	Peoplesoft 9.2

Why Choose AutoUpgrade 

Beside the glaring fact that Oracle strongly recommends it as the best way to upgrade databases to 19c I have also explored available alleys before choosing AU.
According to Oracle 19c migration white paper, below are the few methods available to upgrade or migrate to 19c databases

In my case, I needed to migrate & convert a 12c non CDB to a 19c PDB and since Full transportable tablespaces weren’t supported on Standard edition, the remaining options were:

- DBUA ==> Doesn’t seem to support NonCDB to PDB conversion (also resume after failure is not possible)
- dbupgrade ==> Not enough automated (heck, we’re in 2020)
- Datapump expdp/impdp ==> Gigantic downtime when tested with the database that was ~1TB and 80k+ tables worth 
 

The journey into BUG land

Autoupgrade option being selected, I was very exited to perform it in a real project as opposed to doing it on a vagrant lab. I started by installing the target 19c Software and patched it to 19.7 before creating a CDB using dbca, all was going as planned.

First Attempt

I decided to do POC with a production refresh using rman duplicate and then upgrade the whole thing but out of the blue, right at the finish line (95% upgrade completion) the Autoupgrade crashed on me.

The Configuration

-  Autoupgrade Config file is pretty basic as you can see below with restoration set to no

# Upgrade 1
global.autoupg_log_dir=D:\19c\AutoUpgrade\aupg_logs
upg1.dbname=FSDB
upg1.start_time=NOW
upg1.source_home=C:\Oracle\product\12.1.0.2.0\dbhome
upg1.target_home=C:\Oracle\product\19.0.0\db_home1
upg1.sid=FSDB
upg1.log_dir=D:\Backup\19c\AutoUpgrade\aupg_logs\FSDB
upg1.upgrade_node=DEVHOST     # sanitized
upg1.target_version=19.7
upg1.target_cdb=CDBTEST       # sanitized
upg1.target_pdb_name=FSPDB    
upg1.run_utlrp=yes
upg1.source_tns_admin_dir=C:\Oracle\product\12.1.0.2.0\dbhome\network\admin
upg1.timezone_upg=yes
upg1.restoration=no   # Standard edition doesn’t support guaranteed restore points 

THE STEPS

The following steps were ran while none of ORACLE_SID and ORACLE_HOME environment variables were set (important)

19c jdk	autoupgrade.jar
1.8.0_201	Build.version 19.9.2  - 2020/08/31

1. Autoupgrade analyze

C:\> java -jar autoupgrade.jar -config UP19_FSDB.cfg -mode analyze

The only reported preupgrade errors where due to a non empty Recycle bin and the presence of 2 deprecated parameters but both had fixups which I decided to perform manually anyway.  

2. Autoupgrade deploy

The environment was ready for a go so I launched the deploy phase

C:\> java -jar autoupgrade.jar -config UP19_FSDB.cfg -mode deploy 
... An hour later
upg> lsj 
+----+-------+---------+---------+-------+--------------+--------+------------+ 
|Job#|DB_NAME| STAGE   |OPERATION| STATUS| START_TIME   | UPDATED| MESSAGE    | 
+----+-------+---------+---------+-------+--------------+--------+------------+
| 107| FSDB  |DBUPGRADE|EXECUTING|RUNNING|20/09/11 12:33|19:25:12|93%Upgraded | 
+----+-------+---------+---------+-------+--------------+--------+------------+ 
upg> 
---------------------------------------------- 
Errors in database [FSDB] Stage [DBUPGRADE] 
Operation [STOPPED] Status [ERROR] 
Info [ Error: UPG-1400 UPGRADE FAILED [FSUAT] 
Cause: Database upgrade failed with errors

The upgrade phase never finished but half the catalog was upgraded while 2 components became invalid (hybrid 12/19c DB)

ERRORS

I will break down the errors into 3 categories according to the component they are linked to

1. AUD$ table & DBA_AUDIT_TRAIL

The first set of errors stemmed from two missing columns on sys.aud$ but let’s first see what exactly happened

DATABASE NAME: FSDB
CAUSE: ERROR at Line 6156 in [D:\..\FSDB\FSDB\107\dbupgrade\catupgrdxxfsdb1.log]
        REASON: ORA-00904: "CURRENT_USER": invalid identifier
        ACTION: [MANUAL]
2020-09-16 20:52:19.585 ERROR 
CAUSE: ERROR at Line 6281 in [D:\..\FSDB\FSDB\107\dbupgrade\catupgrdxxfsdb1.log]
        REASON: ORA-00904: "RLS_INFO": invalid identifier
        ACTION: [MANUAL]     

In the catupgrdfsdb0.log, the lines show that both current_user and rls$info columns were added to sys.aud$ at first. But few minutes later, the same columns magically vanished which made the 19c cataudit.sql (called by catalog.sql) script fail when recreating DBA_AUDIT_TRAIL view that’s based on aud$  

How can this be? adding columns to a table just to see them vanish few minutes later with no other ddl run on that table !

           >> Fast forward 2 months and 7 MOS engineers later <<   (yes.. it hurts) 

Explanation

The AUD$ table was actually under ADM schema and not SYS (sys.aud$ was just a synonym). But why on earth adding columns on sys.aud$ synonym didn’t raise any error (i.e ORA-00942). If we can’t run a ddl through a synonym, then an exception should be expected.

SQL> select owner, object_name, object_type from dba_objects where object_name='AUD$'; 
OWNER OBJECT_NAME  OBJECT_TYPE  
---- ------------- ------------ 
SYS   AUD$         SYNONYM
ADM   AUD$         TABLE 

ADM (SYSADM): Is what we call in Peoplesoft the administrative schema(Owner ID) which contains nearly all of the database objects and is used by PeopleSoft application to control schema objects access. 

Solution 

The fix that took 2 months to figure out was to add the columns directly to ADM.aud$ table before the autoupgrade

SQL> alter table adm.aud$ add rls$info clob; 
SQL> alter table adm.aud$ add current_user varchar2(128); 

2. CATJAVA

This issue was solved very early in the SR as the error is likely due to the missing XDK component which JAVA depends on. Note that source production database(12c) didn’t have XDK and java wasn’t used neither.

Solution 

Install Oracle XDK and recompile invalid objects before the Autoupgrade as shown below

SQL> @?\xdk\admin\initxml.sql 
SQL> @?\admin\utlrp.sql

3. DBMS_REGISTRY.TIMESTAMP not found

TIME_STAMP function had moved to dbms_registry_sys package sometime back and the internal cdend.sql script  is still referring the old statement package. Even if it is only a failed select statement it will still block the upgrade.

Solution

Replace the called function with the right package in the 19c catalog script (?\admin\cdend.sql) and resume the autoupgrade

-- Replace the below line 
SELECT dbms_registry.time_stamp('CATALOG') AS timestamp FROM DUAL; 
-- By this one  
SELECT dbms_registry_sys.time_stamp('CATALOG') AS timestamp FROM DUAL; 

Important : Don’t hesitate to  run the utlrp.sql after making changes before starting/resuming the Autoupgrade as you can always have invalid objects lingering that can block the upgrade (see below ) 

REASON: ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errors 

CONCLUSION

I wanted to share my experience so peers in the same situation won’t have to struggle with the support for months to get their databases upgraded in windows using a tool that is supposed to be an industry standard. Make no mistake, I love that I can leverage native automation for my DBA tasks but I just don’t call something AUTO when you have to add manual corrections to resolve undocumented bugs just because it’s windows. Being a beta tester is never fun but we can agree that Autoupgrade has room for improvement.
At last, it always feels good to see a successful output after too many failures

   
Thanks for reading  

Convert local listeners to Oracle Grid managed listeners

Intro

One of the advantages of Oracle grid Infrastructure is to restart all managed resources (databases, asm, listeners,…etc)  automatically whenever you (re)start the cluster service (crs) or reboot the whole server.

I recently had to patch an oda where the client still had few listeners (with non default ports) managed locally and not through grid. This means that anytime there is maintenance or a reboot those listeners would require a manual restart. I ,hence, needed to convert them into Clusterware resources so we wouldn’t worry about restarting them manually after the patch.

Downtime? 

Existing connections won’t be interrupted but new connections will wait for the database services to register back to the new listeners recreated in the Clusterware as the old ones will be stopped during the process. We obviously can’t add a listener when another listener is running with the same port.  

Use case

This was done on a single node ODA where single instance databases were deployed but still managed by the grid infrastructure software. There were 3 listeners(1522,1523,1524) defined with a non default port and a couple of instance configured to be registered to those listeners.

Service registration

The mentioned instances services were registered to the non default listeners using “local_listener” parameter. Normally, you don't have to set listener parameters, neither local_listener nor remote_listener because the background process LREG will register services with the default local and remote 1521 listener automatically. With a different port,however, local_listener must be set to point to the listeners’ addresses (i.e port 1522).

- In my case each of the instances had the local_listener set to a similar entry to the below, depending on the port.

SQL>  show parameter local_listener
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------------
local_listener                       string      (ADDRESS=(PROTOCOL=TCP)(HOST=odaprd1)
                                                 (PORT=1522))

Note: You can also resolve the LOCAL_LISTENER address to a listener alias defined in the tnsnames file on the database host

THE STEPS :
 Below steps were ran as grid user but in case of  a single ownership (oracle user) for grid and database software you can use oracle.

1. Backup  the existing listener file

[grid@odaprd] cp $GRID_HOME/network/admin/listener.ora ~/listener.ora.bkp

2. Stop the listeners in question

[grid@odaprd1]$ export ORACLE_HOME=/u01/app/18.0.0.0/grid
[grid@odaprd1]$ lsnrctl stop LISTENER_1522
[grid@odaprd1]$ lsnrctl stop LISTENER_1523
[grid@odaprd1]$ lsnrctl stop LISTENER_1524

3. Edit the listener file

[grid@odaprd] vi $ORACLE_HOME/network/admin/listener.ora

Delete the entries related to the listeners we stopped to avoid conflicts with the ones created via srvctl add listener

LISTENER_1522=(DESCRIPTION_LIST=...
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_1522=ON 
...
LISTENER_1523=(DESCRIPTION_LIST=...
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_1523=ON 
...
LISTENER_1524=(DESCRIPTION_LIST=...
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_1524=ON 

4. Add the listeners resources in the crs

Now we can add the listeners at clusterware level. Note that -p argument is likely deprecated and replaced with –endpoints 

[grid@odaprd] srvctl add listener -l LISTENER_1522 -o /u01/app/18.0.0.0/grid -p 1522
[grid@odaprd] srvctl add listener -l LISTENER_1523 -o /u01/app/18.0.0.0/grid -p 1523
[grid@odaprd] srvctl add listener -l LISTENER_1524 -o /u01/app/18.0.0.0/grid -p 1524

5.  Confirm that the changes are now effective

There are few ways to verify the status of the newly created listeners. lsnrctl status is one of them and will show the database services that were initially registered to the previous local listeners. The status can also be checked using srvctl  

[grid@odaprd] srvctl status listener -l LISTENER_1522
Listener LISTENER_1522 is enabled
Listener LISTENER_1524 is running on node(s): odaprd

[grid@odaprd] srvctl status listener -l LISTENER_1523
Listener LISTENER_1523 is enabled
Listener LISTENER_1523 is running on node(s): odaprd

[grid@odaprd] srvctl status listener -l LISTENER_1524
Listener LISTENER_1524 is enabled
Listener LISTENER_1524 is running on node(s): odaprd

Or you can run Fred Denis’ famous rac-cluster.sh script that shows you the graphical display of your grid resources

[grid@odaprd] ./rac-status.sh -e

Conclusion

It is very easy to move existing listeners defined with non default ports to the clusterware as long as the local_listener parameter is defined. Although I haven’t tried to run srvctl add listener without pre-emptively removing the old entries defined the listener.ora but I am pretty sure that would lead to a conflict and would trigger an error.