We will create new 19c pluggable database from single noncdb 11.2.0.4 database without upgrading it first.
To be able to perform this operation, we will need som packages and scripts provided in the support note 2650115.1. After downloading those scripts, we can start..
In 11.2.0.4 database:
check, if XML is installed:
SYS@ORCL11 SQL> select comp_name,version from dba_registry;
COMP_NAME VERSION
-------------------------------------------------- ------------------------------
Oracle Database Catalog Views 11.2.0.4.0
Oracle Database Packages and Types 11.2.0.4.0
If not, install it using catqm.sql script:
SYS@ORCL11 SQL> create tablespace xdb datafile '+DSIH' size 100m autoextend on maxsize 10G;
Tablespace created.
SYS@ORCL11 SQL> @?/rdbms/admin/catqm.sql xdbb XDB TEMP YES
Compile all invalid objects and check installed components:
SYS@ORCL11 SQL> select comp_name,version from dba_registry;
COMP_NAME VERSION
-------------------------------------------------- ------------------------------
Oracle XML Database 11.2.0.4.0
Oracle Database Catalog Views 11.2.0.4.0
Oracle Database Packages and Types 11.2.0.4.0
Run the prepare_noncdb_for_plug_in.sql scipt:
You should provide pfile name, directory name, direcotry on OS where the output files will be located and the home of the 19c db.
SQL>prepare_noncdb_for_plug_in.sql 'pfile=/oracle/oramni/product/11g/dbs/initORCL11.ora' tmp '/tmp/upg' '/oracle/oramni/product/19c'
1. make sure the non-CDB is open for UPGRADE
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Database mounted.
Database opened.
2.create the dbms_pdb package
no rows selected
Package created.
No errors.
Package body created.
No errors.
3. modify non-CDB metadata in preparation for eventual ALTER PDB UPGRADE
4. create a directory for the manifest file
PL/SQL procedure successfully completed.
5. run utlrp.sql
6. reopen the non-CDB RW
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Database mounted.
Database opened.
PL/SQL procedure successfully completed.
7. run the pre-upgrade tool
==================
PREUPGRADE SUMMARY
==================
/tmp/upg/preupgrade.log
/tmp/upg/preupgrade_fixups.sql
/tmp/upg/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/tmp/upg/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/tmp/upg/postupgrade_fixups.sql
Preupgrade complete: 2020-10-29T09:49:02
8. run preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2020-10-29 09:49:00
For Source Database: ORCL11
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. parameter_min_val NO Manual fixup recommended.
2. pre_fixed_objects YES None.
3. rman_recovery_version NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
* The following steps will need to be performed manually:
* - address issues raised by the pre-upgrade tool which could not be
* handled by preupgrade_fixups.sql, most critically those characterized
* as ERROR
* NOTE: suggestions pertaining to parameters can be ignored because
* CDB parameters will be in effect when the non-CDB gets
* plugged into the CDB
* - shutdown the non-CDB and reopen it RO
* - create the Manifest, e.g.
* exec dbms_pdb.describe('tmp', '11_2_0_4_non_cdb.xml')
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and Real Application Testing options
We can see, that all steps were successful. I had to check the logfile to see, which parameters were not set adequately, but it was only the sessions parameter. This parameter is OK in the cdb database, so no need to set it up in the noncdb DB.
Open the noncdb database in read only mode:
SYS@ORCL11 SQL> startup open read only
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 805307272 bytes
Database Buffers 3456106496 bytes
Redo Buffers 12107776 bytes
Database mounted.
Database opened.
Create the manifest file (you have to provide the directory name you used in the previous script, in my case it is 'tmp'):
SYS@ORCL11 SQL> exec dbms_pdb.describe('tmp','11_2_0_4_orcl11.xml');
PL/SQL procedure successfully completed.
Check the file:
[ORCL11 oramni@exhdbadm01:/tmp/upg]> file 11_2_0_4_orcl11.xml
11_2_0_4_orcl11.xml: XML 1.0 document, ASCII text
In 11.2.0.4 database:
check, if XML is installed:
SYS@ORCL11 SQL> select comp_name,version from dba_registry;
COMP_NAME VERSION
-------------------------------------------------- ------------------------------
Oracle Database Catalog Views 11.2.0.4.0
Oracle Database Packages and Types 11.2.0.4.0
If not, install it using catqm.sql script:
SYS@ORCL11 SQL> create tablespace xdb datafile '+DSIH' size 100m autoextend on maxsize 10G;
Tablespace created.
SYS@ORCL11 SQL> @?/rdbms/admin/catqm.sql xdbb XDB TEMP YES
Compile all invalid objects and check installed components:
SYS@ORCL11 SQL> select comp_name,version from dba_registry;
COMP_NAME VERSION
-------------------------------------------------- ------------------------------
Oracle XML Database 11.2.0.4.0
Oracle Database Catalog Views 11.2.0.4.0
Oracle Database Packages and Types 11.2.0.4.0
Run the prepare_noncdb_for_plug_in.sql scipt:
You should provide pfile name, directory name, direcotry on OS where the output files will be located and the home of the 19c db.
SQL>prepare_noncdb_for_plug_in.sql 'pfile=/oracle/oramni/product/11g/dbs/initORCL11.ora' tmp '/tmp/upg' '/oracle/oramni/product/19c'
1. make sure the non-CDB is open for UPGRADE
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Database mounted.
Database opened.
2.create the dbms_pdb package
no rows selected
Package created.
No errors.
Package body created.
No errors.
3. modify non-CDB metadata in preparation for eventual ALTER PDB UPGRADE
4. create a directory for the manifest file
PL/SQL procedure successfully completed.
5. run utlrp.sql
6. reopen the non-CDB RW
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Database mounted.
Database opened.
PL/SQL procedure successfully completed.
7. run the pre-upgrade tool
==================
PREUPGRADE SUMMARY
==================
/tmp/upg/preupgrade.log
/tmp/upg/preupgrade_fixups.sql
/tmp/upg/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/tmp/upg/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/tmp/upg/postupgrade_fixups.sql
Preupgrade complete: 2020-10-29T09:49:02
8. run preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2020-10-29 09:49:00
For Source Database: ORCL11
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. parameter_min_val NO Manual fixup recommended.
2. pre_fixed_objects YES None.
3. rman_recovery_version NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
* The following steps will need to be performed manually:
* - address issues raised by the pre-upgrade tool which could not be
* handled by preupgrade_fixups.sql, most critically those characterized
* as ERROR
* NOTE: suggestions pertaining to parameters can be ignored because
* CDB parameters will be in effect when the non-CDB gets
* plugged into the CDB
* - shutdown the non-CDB and reopen it RO
* - create the Manifest, e.g.
* exec dbms_pdb.describe('tmp', '11_2_0_4_non_cdb.xml')
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and Real Application Testing options
We can see, that all steps were successful. I had to check the logfile to see, which parameters were not set adequately, but it was only the sessions parameter. This parameter is OK in the cdb database, so no need to set it up in the noncdb DB.
Open the noncdb database in read only mode:
SYS@ORCL11 SQL> startup open read only
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 805307272 bytes
Database Buffers 3456106496 bytes
Redo Buffers 12107776 bytes
Database mounted.
Database opened.
Create the manifest file (you have to provide the directory name you used in the previous script, in my case it is 'tmp'):
SYS@ORCL11 SQL> exec dbms_pdb.describe('tmp','11_2_0_4_orcl11.xml');
PL/SQL procedure successfully completed.
Check the file:
[ORCL11 oramni@exhdbadm01:/tmp/upg]> file 11_2_0_4_orcl11.xml
11_2_0_4_orcl11.xml: XML 1.0 document, ASCII text
In the 19c cdb database:
Create the new pdb from the manifest file:
SYS@ORCL19 SQL> create pluggable database orcl11 using '/tmp/upg/11_2_0_4_orcl11.xml' copy;
Pluggable database created.
SYS@ORCL19 SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 READ WRITE NO
4 ORCL11 MOUNTED
5 PLUG19_3 READ WRITE NO
Open the database in upgrade mode:
SYS@ORCL19 SQL> alter pluggable database orcl11 open upgrade;
Pluggable database altered.
SYS@ORCL19 SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 READ WRITE NO
4 ORCL11 MIGRATE YES
5 PLUG19_3 READ WRITE NO
Run the upgrade using the catctl.pl from the $ORACLE_HOME/rdbms/admin directory:
/oracle/oramni/product/19c/perl/bin/perl catctl.pl -c 'ORCL11' -d /oracle/oramni/product/19c/rdbms/admin -l /tmp/upg/orcl11 -I -i orcl11 -n 2 catupgrd.sql]
.....
......
------------------------------------------------------
Phases [0-107] End Time:[2020_10_29 10:49:32]
Container Lists Inclusion:[ORCL11] Exclusion:[NONE]
------------------------------------------------------
......
.....
Grand Total Upgrade Time: [0d:0h:21m:46s]
Check the pdbs:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 MOUNTED
4 ORCL11 MOUNTED
5 PLUG19_3 MOUNTED
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
Run the compare_source script from the root db:
SQL> alter pluggable database orcl11 open upgrade;
SQL> alter session set container=cdb$root;
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 MOUNTED
4 ORCL11 MIGRATE YES
5 PLUG19_3 MOUNTED
SQL> @?/rdbms/admin/compare_source.sql ORCL11
No errors.
Convert the noncdb to pdb using the script:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 MOUNTED
4 ORCL11 MIGRATE YES
5 PLUG19_3 MOUNTED
SQL> alter session set container=orcl11;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 ORCL11 MIGRATE YES
SQL> @?/rdbms/admin/noncdb_to_pdb.sql
Open the database in reaad write mode:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 MOUNTED
4 ORCL11 MIGRATE YES
5 PLUG19_3 MOUNTED
SQL> alter pluggable database orcl11 close immediate;
Pluggable database altered.
SQL> alter pluggable database orcl11 open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 MOUNTED
4 ORCL11 READ WRITE NO
5 PLUG19_3 MOUNTED
Check the upgraded DB:
SYS@ORCL19 SQL> show con_name
CON_NAME
------------------------------
ORCL11
SYS@ORCL19 SQL> select comp_name,version from dba_registry;
COMP_NAME VERSION
-------------------------------------------------- ------------------------------
Oracle Database Catalog Views 19.0.0.0.0
Oracle Database Packages and Types 19.0.0.0.0
Oracle Real Application Clusters 19.0.0.0.0
Oracle XML Database 19.0.0.0.0
Create the new pdb from the manifest file:
SYS@ORCL19 SQL> create pluggable database orcl11 using '/tmp/upg/11_2_0_4_orcl11.xml' copy;
Pluggable database created.
SYS@ORCL19 SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 READ WRITE NO
4 ORCL11 MOUNTED
5 PLUG19_3 READ WRITE NO
Open the database in upgrade mode:
SYS@ORCL19 SQL> alter pluggable database orcl11 open upgrade;
Pluggable database altered.
SYS@ORCL19 SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 READ WRITE NO
4 ORCL11 MIGRATE YES
5 PLUG19_3 READ WRITE NO
Run the upgrade using the catctl.pl from the $ORACLE_HOME/rdbms/admin directory:
/oracle/oramni/product/19c/perl/bin/perl catctl.pl -c 'ORCL11' -d /oracle/oramni/product/19c/rdbms/admin -l /tmp/upg/orcl11 -I -i orcl11 -n 2 catupgrd.sql]
.....
......
------------------------------------------------------
Phases [0-107] End Time:[2020_10_29 10:49:32]
Container Lists Inclusion:[ORCL11] Exclusion:[NONE]
------------------------------------------------------
......
.....
Grand Total Upgrade Time: [0d:0h:21m:46s]
Check the pdbs:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 MOUNTED
4 ORCL11 MOUNTED
5 PLUG19_3 MOUNTED
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
Run the compare_source script from the root db:
SQL> alter pluggable database orcl11 open upgrade;
SQL> alter session set container=cdb$root;
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 MOUNTED
4 ORCL11 MIGRATE YES
5 PLUG19_3 MOUNTED
SQL> @?/rdbms/admin/compare_source.sql ORCL11
No errors.
Convert the noncdb to pdb using the script:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 MOUNTED
4 ORCL11 MIGRATE YES
5 PLUG19_3 MOUNTED
SQL> alter session set container=orcl11;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 ORCL11 MIGRATE YES
SQL> @?/rdbms/admin/noncdb_to_pdb.sql
Open the database in reaad write mode:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 MOUNTED
4 ORCL11 MIGRATE YES
5 PLUG19_3 MOUNTED
SQL> alter pluggable database orcl11 close immediate;
Pluggable database altered.
SQL> alter pluggable database orcl11 open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 MOUNTED
4 ORCL11 READ WRITE NO
5 PLUG19_3 MOUNTED
Check the upgraded DB:
SYS@ORCL19 SQL> show con_name
CON_NAME
------------------------------
ORCL11
SYS@ORCL19 SQL> select comp_name,version from dba_registry;
COMP_NAME VERSION
-------------------------------------------------- ------------------------------
Oracle Database Catalog Views 19.0.0.0.0
Oracle Database Packages and Types 19.0.0.0.0
Oracle Real Application Clusters 19.0.0.0.0
Oracle XML Database 19.0.0.0.0