Migrate Oracle 12c Non-CDB to PDB and upgrade to Oracle 19c - High Level Steps:
In this video, I will show you all, how to migrate a Non-Container Database to a Pluggable Database (12cR1 and R2 to Oracle 19c).
I will use Oracle 12c noncdb which will migrate to Oracle 19c PDB. For that purpose, I had to install Oracle 19c software with an empty container.
The installation of Oracle 19c doesn't have any specifics. So we can proceed with the main steps.
At the first place, we have to describe the noncdb database. For that purpose, we will use DBMS_PDB package which allows us to generate an XML file from a noncdb.
Now connecting to our Oracle 12c database, shutting it down and starting it in read-only mode:
Here it is 3 different versions of Databases are running on this host.
Migration Pre-checks:
=====================
[oracle@srlab ~]$ ps -ef |grep pmon
oracle 2057 1 0 18:36 ? 00:00:00 ora_pmon_SRCDB3
oracle 15510 1 0 17:15 ? 00:00:00 ora_pmon_SRDB1
oracle 30853 1 0 18:20 ? 00:00:00 ora_pmon_SRDB2
[oracle@srlab ~]$
[oracle@srlab ~]$ ps -ef |grep tns
oracle 3246 1 0 18:39 ? 00:00:00 /u02/app/oracle/product/19.3.0.0/db_3/bin/tnslsnr SRCDB3_LISTENER -inherit
oracle 11680 1 0 16:58 ? 00:00:00 /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr SRDB1_LISTENER -inherit
oracle 27119 1 0 18:08 ? 00:00:00 /u02/app/oracle/product/12.2.0.1/db_2/bin/tnslsnr SRDB2_LISTENER -inherit
Version: 12.1: SRDB1:
=====================
[oracle@srlab ~]$ env |grep ORA
ORACLE_UNQNAME=SRDB1
ORACLE_SID=SRDB1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOSTNAME=srlab.localdomain
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
SRDB1
SQL> show pdbs
SQL>
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/SRDB1/system01.dbf
/u01/oradata/SRDB1/sysaux01.dbf
/u01/oradata/SRDB1/undotbs01.dbf
/u01/oradata/SRDB1/example01.dbf
/u01/oradata/SRDB1/users01.dbf
[oracle@srlab ~]$sqlplus / as sysdba
shutdown immediate;
startup open read only;
Version: 12.2: SRDB2:
=====================
[oracle@srlab ~]$ env |grep ORA
ORACLE_UNQNAME=SRDB2
ORACLE_SID=SRDB2
ORACLE_BASE=/u02/app/oracle
ORACLE_HOSTNAME=srlab.localdomain
ORACLE_TERM=xterm
ORACLE_HOME=/u02/app/oracle/product/12.2.0.1/db_2
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
SRDB2
SQL> show pdbs
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/SRDB2/system01.dbf
/u01/oradata/SRDB2/sysaux01.dbf
/u01/oradata/SRDB2/undotbs01.dbf
/u01/oradata/SRDB2/users01.dbf
Test case setup:
================
Host server: srlab.localdomain
Non-CDB database: SRDB1 and SRDB2
Oracle version: 12.1.0.2 and 12.2.0.1
Conversion tests:
=================
Use the steps in this section to convert the non-CDB database to a CDB database.
STEP 1: PERFORM A CLEAN SHUTDOWN: 12.1.0.2:
-------------------------------------------
Perform the following steps to cleanly shut down the non-CDB database:
[oracle@srlab ~]$sqlplus / as sysdba
SQL> shutdown immediate;
STEP 2: OPEN THE DATABASE AS READ-ONLY:
---------------------------------------
SQL> startup open read only;
PERFORM A CLEAN SHUTDOWN: 12.2.0.1
----------------------------------
[oracle@srlab ~]$sqlplus / as sysdba
SQL> shutdown immediate;
STEP 2: OPEN THE DATABASE AS READ-ONLY:
---------------------------------------
After you have cleanly shutdown the database, perform the following steps to start the database in mount exclusive mode and open the database in read-only mode:
SQL> startup open read only;
STEP 3: GENERATE A PDB MANIFEST FILE:
-------------------------------------
Perform the following steps to generate a PDB manifest file from the non-CDB:
Now describe the DB using DBMS_PDB.DESCRIBE procedure. It is describing the database the same way when you are unplugging a PDB database.
BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => '/home/oracle/NonCDB121.xml');
END;
/
BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => '/home/oracle/NonCDB122.xml');
END;
/
We can see that the xml file is created and check its content:
[oracle@srlab ~]$ ls -lart NonCDB121.xml
STEP 4: SHUTDOWN THE 12c NON-CDB:
---------------------------------
Perform the following steps after Step 3 completes to shut down the non-CDB databases.
STEP 5: START THE 19c CDB:
--------------------------
If the CDB is not already running, perform the following steps to start it and to check compatibility:
Now we can proceed with creating the PDB, plug in in Oracle 19c, and then upgrade it and convert/plug it from NonCDB(pdb_descr_file) to PDBs in to 19c CDB.
[oracle@srlab ~]$ env |grep ORA
ORACLE_UNQNAME=SRCDB3
ORACLE_SID=SRCDB3
ORACLE_BASE=/u02/app/oracle
ORACLE_HOSTNAME=srlab.localdomain
ORACLE_TERM=xterm
ORACLE_HOME=/u02/app/oracle/product/19.3.0.0/db_3
[oracle@srlab ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 5 18:50:54 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
SRCDB3
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
* Chek the compatibility where the otput should be YES. If NO, you can check for violations (pdb_plug_in_violations). Have in mind that most of the cases that I've experienced with the compatibility 'NO' were related to mismatch with database components or different patch levels. *
SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/home/oracle/NonCDB121.xml')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/home/oracle/NonCDB122.xml')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
STEP 6: CHECK FOR ERRORS:
-------------------------
After the CDB database startup completes, perform the following steps to check for errors in the PDB_PLUG_IN_VIOLATIONS view:
After PDB is created, we can check for any violations with the below select statement:
SQL> select message from pdb_plug_in_violations where type like '%ERR%' and status <> 'RESOLVED';
Note: If there are any errors, fix them before proceeding.
STEP 7: CONNECT TO THE 19C CDB AND PLUG INTO THE PDBs to CDB:
-------------------------------------------------------------
Perform the following steps to connect to the CDB and plug into the PDDB12C database by using the non-CDB manifest file:
--- Creating the PDBs in to 19c CDB ---
mkdir -p /u02/oradata/SRCDB3/srdb1plug121
create pluggable database SRDB1PLUG121 using '/home/oracle/NonCDB121.xml' COPY FILE_NAME_CONVERT=('/u01/oradata/SRDB1','/u02/oradata/SRCDB3/srdb1plug121');
mkdir -p /u02/oradata/SRCDB3/srdb2plug122
create pluggable database SRDB2PLUG122 using '/home/oracle/NonCDB122.xml' COPY FILE_NAME_CONVERT=('/u01/oradata/SRDB2','/u02/oradata/SRCDB3/srdb2plug122');
Note: The following options are supported, and you can choose one based on your environment:
COPY: The datafiles of the noncdb remain intact, and the noncdb is copied to create a PDB at the new location and keep the original datafiles intact at the original location. This means that a noncdb database is still operational after the creation of the PDB.
MOVE: The datafiles of the noncdb are moved to a new location to create a PDB. In this case, the noncdb database is not available after the PDB is created.
NOCOPY: The datafiles of the noncdb are used to create a PDB2, and it uses the same location as the noncdb. In this case, the noncdb database is not available after the PDB is created.
You can use the FILE_NAME_CONVERT parameter to specify the new location of the datafiles while using either the COPY or MOVE option.
STEP 8: RUN THE CONVERSION SCRIPT:
----------------------------------
After step 7 completes successfully, perform the following steps to switch to the PDB container and run the conversion script,
$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql:
Now, as we expected to have is to execute the script noncdb_to_pdb.sql which will clean up the new PDB from things that should not be presented there.
ALTER SESSION SET CONTAINER=SRDB1PLUG121;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
STEP 9: START THE PDB AND VERIFY THE MODE:
------------------------------------------
Perform the following steps to start the PDB and verify that the mode is open:
ALTER SESSION SET CONTAINER=SRDB1PLUG121;
ALTER PLUGGABLE DATABASE SRDB1PLUG121 OPEN;
SELECT name, open_mode FROM v$pdbs;
Now we can select again for violation and check what are the messages there:
select message from pdb_plug_in_violations where type like '%ERR%' and status <> 'RESOLVED';
PDBs Upgrade Methods:
---------------------
It is time to start with the upgrade. We can do the upgrade in three ways as below. I've been tested all the ways, but here in the video will demonstrate DBUA method for the dbupgrade:
dbupgrade -c 'SRDB1PLUG121' -l /home/oracle/logs -n 2
--- or ---
cd $ORACLE_HOME/rdbms/admin ( this is important step )
$ORACLE_HOME/perl/bin/perl catctl.pl -c "SRDB1PLUG121" -l /home/oracle catupgrd.sql
------ Here you can see the output from the upgrade in the below file ------
dbupgrade.txt
--- or ---
cd $ORACLE_HOME/bin/
dbua (Regular Method)
Now it's time to login in the database and to check for any violations left:
SQL> select message from pdb_plug_in_violations where type like '%ERR%' and status <> 'RESOLVED';
Conclusion:
--------------
When you convert a non-CDB database to a CDB pluggable database, you can choose from several options depending upon the size of the database.
If the database is very large, you might use the NOCOPY option. This minimizes the amount of extra space needed and reduces the time it takes to perform the conversion operation. The NOCOPY option is risky, however, because you don't keep the original database files intact. Thus, if you need to restore the database to the previous state, you must restore the database using the backups taken before the conversion activity.
If database size is smaller, you should use the COPY option so that, if there are any issues, the fallback to the old non-CDB is straightforward because the original files are always intact.
This is the result that we were expecting in the end. Create Oracle 19c pluggable database without any violations after converting it from a non container database and upgrade from 2.1 and 12.2 versions.
Thanks,
Ramesh
Post a Comment: