notes/tiddlywiki/non-CDB upgrade and convert to PDB - example.md

This example applies only if target database is in 19c version or less; direct upgrade to 21c is possible only if source database version is 12.2 or superior

In this example we will:

• restore 11.2.0.4 WEDGEPRD database (db_name=WEDGE, db_unique_name=WEDGEPRD) from a backup of another database (ZABRAK)
• upgrade WEDGEPRD to 19
• plug WEDGEPRD as container into 19 CDB ASTYPRD database (db_name=ASTY, db_unique_name=ASTYPRD)

Restore WEDGEPRD from ZABRAK backup with noopen in order to perform the database upgrade to 19:

rman auxiliary /
run
{
  allocate auxiliary channel aux01 device type disk;
  allocate auxiliary channel aux02 device type disk;
  allocate auxiliary channel aux03 device type disk;
  allocate auxiliary channel aux04 device type disk;
  allocate auxiliary channel aux05 device type disk;
  allocate auxiliary channel aux06 device type disk;
  allocate auxiliary channel aux07 device type disk;
  allocate auxiliary channel aux08 device type disk;
  allocate auxiliary channel aux09 device type disk;
  allocate auxiliary channel aux10 device type disk;
  duplicate target database to WEDGE noopen backup location '/mnt/yavin4/tmp/_oracle_/orabackup/_keep_/RAC/11.2.0.4/ZABRAK';
}

Startup database in upgrade mode:

alter database open resetlogs upgrade;

Change ORACLE_HOME to 19 and make the upgrade:

$ORACLE_HOME/bin/dbupgrade

Check if CDB is in local undo mode:

column property_name format a30
column property_value format a30

select property_name, property_value
from   database_properties
where  property_name = 'LOCAL_UNDO_ENABLED';

To change in local undo mode:

startup upgrade;
alter database local undo on;

Backup CDB:

run
{
  set nocfau;
  allocate channel ch01 device type disk format '/mnt/yavin4/tmp/_oracle_/orabackup/ASTY/%d_%U_%s_%t.bck';
  allocate channel ch02 device type disk format '/mnt/yavin4/tmp/_oracle_/orabackup/ASTY/%d_%U_%s_%t.bck';
  allocate channel ch03 device type disk format '/mnt/yavin4/tmp/_oracle_/orabackup/ASTY/%d_%U_%s_%t.bck';
  allocate channel ch04 device type disk format '/mnt/yavin4/tmp/_oracle_/orabackup/ASTY/%d_%U_%s_%t.bck';
  backup as compressed backupset incremental level 0 database section size 2G include current controlfile plus archivelog delete input;
  release channel ch01;
  release channel ch02;
  release channel ch03;
  release channel ch04;
  allocate channel ch01 device type disk format '/mnt/yavin4/tmp/_oracle_/orabackup/ASTY/%d_%U_%s_%t.controlfile';
  backup current controlfile;
  release channel ch01;
}

Restart source WEDGEPRD in read only mode, generate xml file and stop the database:

startup open read only;
exec DBMS_PDB.DESCRIBE('/mnt/yavin4/tmp/_oracle_/tmp/WEDGE.xml');

Check database compatibility with the CDB:

exec DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS;

set serveroutput on
DECLARE
compatible CONSTANT VARCHAR2(3) := 
  CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => '/mnt/yavin4/tmp/_oracle_/tmp/WEDGE.xml', pdb_name => 'WEDGEPRD') WHEN TRUE THEN 'YES' ELSE 'NO'
END;

BEGIN
  DBMS_OUTPUT.PUT_LINE('Is the future PDB compatible? ==> ' || compatible);
END;
/



col name for a10 trunc
col type for a10 trunc
col cause for a10 trunc
col time for a15 trunc
col status for a10 trunc
col action for a50 trunc
col message for a70 trunc
set lines 200

select name, cause, type, status,action,message,time from pdb_plug_in_violations;

Plug WEDGEPRD into ASTYPRD CDB (in place plugin, not a database copy):

create pluggable database WEDGEPRD using '/mnt/yavin4/tmp/_oracle_/tmp/WEDGE.xml' nocopy tempfile reuse;

-- Monitoring parallel execution servers using a different session
SELECT qcsid, qcserial#, sid, serial#
  FROM   v$px_session
  ORDER BY 1,2,3;

alter session set container=WEDGEPRD;
@?/rdbms/admin/noncdb_to_pdb.sql

Restart pluggable database and save the state:

alter pluggable database WEDGEPRD close immediate;
alter pluggable database WEDGEPRD open;
alter pluggable database WEDGEPRD save state;

The new pluggable database will use the controlfile and redologs of the CDB, so old controlfile + redolog can be deleted.