Friday, August 27, 2021

Upgrade Non-CDB to Oracle Database 21c (Manual)

Oracle Database 21c, Oracle Database Tutorial and Material, Oracle Database Preparation, Oracle Database Career, Database Study Materials

This article provides an overview of a manual upgrade of an existing non-CDB database to Oracle 21c.

◉ Assumptions

This article assumes your source database is of a version supported for direct upgrade to 21c.

19c, 18c, 12.2

The non-CDB architecture is desupported in Oracle 21c, so we have two choices when upgrading a non-CDB database to 21c.

- Convert the existing non-CDB database to a PDB in the current version of the database, then do a multitentant upgrade.

- Convert the non-CDB to a PDB in the new version of the database. The new PDB will be upgraded once it is opened.

In this example we are doing a manual upgrade from 19c non-CDB to 21c. The process is very similar for all supported versions.

It's important to have backups of everything before you start! Some of these steps are destructive, and if something goes wrong you have no alternative but to restore from backups and start again.

◉ Prerequisities

Make sure you have all the OS prerequisites in place by running the 21c preinstall package. It probably makes sense to update the remaining packages also.

yum install -y oracle-database-preinstall-21c

yum update -y

◉ Install 21c Software

You can read about the installation process in more detail here (OL7, OL8), but for this example we'll keep it brief. The following commands will perform a silent installation of the 21c software.

export ORACLE_HOME=$ORACLE_BASE/product/21.0.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=cdb1

export SOFTWARE_DIR=/u01/software

export ORA_INVENTORY=/u01/app/oraInventory

mkdir -p ${ORACLE_HOME}

cd $ORACLE_HOME

/bin/unzip -oq ${SOFTWARE_DIR}/LINUX.X64_213000_db_home.zip

./runInstaller -ignorePrereq -waitforcompletion -silent                        \

    -responseFile ${ORACLE_HOME}/install/response/db_install.rsp               \

    oracle.install.option=INSTALL_DB_SWONLY                                    \

    ORACLE_HOSTNAME=${ORACLE_HOSTNAME}                                         \

    UNIX_GROUP_NAME=oinstall                                                   \

    INVENTORY_LOCATION=${ORA_INVENTORY}                                        \

    SELECTED_LANGUAGES=en,en_GB                                                \

    ORACLE_HOME=${ORACLE_HOME}                                                 \

    ORACLE_BASE=${ORACLE_BASE}                                                 \

    oracle.install.db.InstallEdition=EE                                        \

    oracle.install.db.OSDBA_GROUP=dba                                          \

    oracle.install.db.OSBACKUPDBA_GROUP=dba                                    \

    oracle.install.db.OSDGDBA_GROUP=dba                                        \

    oracle.install.db.OSKMDBA_GROUP=dba                                        \

    oracle.install.db.OSRACDBA_GROUP=dba                                       \

    SECURITY_UPDATES_VIA_MYORACLESUPPORT=false                                 \

    DECLINE_SECURITY_UPDATES=true

Run the root scripts when prompted.

As a root user, execute the following script(s):

        1. /u01/app/oracle/product/21.0.0/dbhome_1/root.sh

At this point you should also patch the new Oracle home, but in this case we will forgo that step to keep things simple.

◉ Create 21c Container Database

We need to create a container database (CDB) as the destination for the resulting PDB. The following example create a CDB called "cdb1" with no PDBs.

#dbca -silent -deleteDatabase -sourceDB cdb1 -sysDBAUserName sys -sysDBAPassword SysPassword1

dbca -silent -createDatabase \

 -templateName General_Purpose.dbc \

 -gdbname cdb1 -sid cdb1 -responseFile NO_VALUE \

 -characterSet AL32UTF8 \

 -sysPassword SysPassword1 \

 -systemPassword SysPassword1 \

 -createAsContainerDatabase true \

 -numberOfPDBs 0 \

 -databaseType MULTIPURPOSE \

 -memoryMgmtType auto_sga \

 -totalMemory 1536 \

 -storageType FS \

 -datafileDestination "/u02/oracle/" \

 -redoLogFileSize 50 \

 -emConfiguration NONE \

 -ignorePreReqs

Prepare for db operation

10% complete

Copying database files

40% complete

Creating and starting Oracle instance

42% complete

46% complete

52% complete

56% complete

60% complete

Completing Database Creation

66% complete

69% complete

70% complete

Executing Post Configuration Actions

100% complete

Database creation complete. For details check the logfiles at:

 /u01/app/oracle/cfgtoollogs/dbca/cdb1.

Database Information:

Global Database Name:cdb1

System Identifier(SID):cdb1

Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb1/cdb13.log" for further details.

$

We enable the fast recovery area, Oracle Managed Files (OMF) and archivelog mode. We

sqlplus / as sysdba <<EOF

alter system set db_recovery_file_dest_size=40g;

alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';

alter system set db_create_file_dest = '/u02/oradata';

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

exit;

EOF

You will need to adjust the instance parameters to make sure the container can cope with the demands of the final PDB, but for this example we will ignore that.

◉ Create PDB From non-CDB

It would make sense to run the AutoUpgrade analyze before trying the upgrade, to make sure there aren't any issues that might need fixing before you continue. 

We switch the non-CDB instance to read-only mode, describe the database using the DBMS_PDB package, then shutdown the non-CDB instance.

export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=mydb

sqlplus / as sysdba <<EOF

shutdown immediate;

startup open read only;

begin

  dbms_pdb.describe(

    pdb_descr_file => '/tmp/mydb.xml');

end;

/

shutdown immediate;

exit;

EOF

We can see there are no user-defined PDBs in the root container.

export ORACLE_HOME=$ORACLE_BASE/product/21.0.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=cdb1

sqlplus / as sysdba

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

SQL>

We create a pluggable database from the non-CDB database using the "/tmp/mydb.xml" file. We are using the COPY option, which copies the datafiles to create the new PDB. We could use the MOVE option, which would use the existing files to save space.

create pluggable database mydb using '/tmp/mydb.xml' copy;

We see the following messages in alert log.

2021-08-21T09:59:43.993485+00:00

create pluggable database mydb using '/tmp/mydb.xml' copy

2021-08-21T09:59:44.027947+00:00

Opatch validation is skipped for PDB MYDB (con_id=4)

2021-08-21T09:59:52.674090+00:00

MYDB(4):Endian type of dictionary set to little

****************************************************************

Pluggable Database MYDB with pdb id - 4 is created as UNUSABLE.

If any errors are encountered before the pdb is marked as NEW,

then the pdb must be dropped

local undo-1, localundoscn-0x0000000000000009

****************************************************************

MYDB(4):Pluggable database MYDB pseudo opening

MYDB(4):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18

MYDB(4):Autotune of undo retention is turned on.

MYDB(4):Undo initialization recovery: Parallel FPTR complete: start:5860713 end:5860715 diff:2 ms (0.0 seconds)

MYDB(4):Undo initialization recovery: err:0 start: 5860709 end: 5860715 diff: 6 ms (0.0 seconds)

MYDB(4):[18913] Successfully onlined Undo Tablespace 2.

MYDB(4):Undo initialization online undo segments: err:0 start: 5860715 end: 5860764 diff: 49 ms (0.0 seconds)

MYDB(4):Undo initialization finished serial:0 start:5860709 end:5860768 diff:59 ms (0.1 seconds)

MYDB(4):Database Characterset for MYDB is AL32UTF8

MYDB(4):Pluggable database MYDB pseudo closing

MYDB(4):JIT: pid 18913 requesting stop

MYDB(4):Closing sequence subsystem (5860811822).

MYDB(4):Buffer Cache flush started: 4

MYDB(4):Buffer Cache flush finished: 4

Completed: create pluggable database mydb using '/tmp/mydb.xml' copy

Now we open the pluggable database in read-write mode, which triggers the upgrade of the PDB, and conversion from a non-CDB to a PDB. We do not need to run the "noncdb_to_pdb.sql" script for this operation. As you might expect, this upgrade and conversion takes some time to complete. In this example a little over 30 minutes.

alter pluggable database mydb open read write;

In the alert log we see messages to indicate the upgrade and conversion are taking place.

2021-08-21T10:03:35.400676+00:00

alter pluggable database mydb open read write

...

****************************************************************

Post plug operations are now complete.

Pluggable database MYDB with pdb id - 4 is now marked as NEW.

****************************************************************

...

MYDB(4):alter pluggable database application APP$CDB$CATALOG begin upgrade

  '19.0.0.0.0' to '21.0.0.0.0.partial' on error capture

MYDB(4):Completed: alter pluggable database application APP$CDB$CATALOG begin upgrade

  '19.0.0.0.0' to '21.0.0.0.0.partial' on error capture

...

MYDB(4):SERVER ACTION=NONCDB_TO_PDB id=: Converted non-CDB to PDB in release 21.3.0.0.0 Container=MYDB Id=4

MYDB(4):alter pluggable database application app$cdb$pdbonly$ncdbtopdb end upgrade

MYDB(4):Completed: alter pluggable database application app$cdb$pdbonly$ncdbtopdb end upgrade

2021-08-21T10:31:03.540665+00:00

MYDB(4):Finished Conversion from non-CDB on PDB Open

Violations: Type: 2, Count: 1

Pluggable database MYDB opened read write

Completed: alter pluggable database mydb open read write

We can see the PDB is now open in read-write mode.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         4 MYDB                           READ WRITE NO

SQL>

We save the state so the PDB opens automatically with the instance.

alter pluggable database mydb save state;

It's worth checking for violations. They should all be from before the upgrade and conversion.

column name format a10

column message format a60

select time, name, message from pdb_plug_in_violations order by time;

◉ Final Steps

We need to clean up the remains of the non-CDB instance. We can use the DBCA to do this.

export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=mydb

dbca -silent -deleteDatabase -sourceDB mydb -sysDBAUserName sys -sysDBAPassword SysPassword1

Edit the "/etc/oratab" file and any environment files as required.

◉ Appendix

The following commands are used to rebuild the databases if you want to rerun the examples.

Rebuild the CDB1 multitenant database.

export ORACLE_HOME=/u01/app/oracle/product/21.0.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=cdb1

#dbca -silent -deleteDatabase -sourceDB cdb1 -sysDBAUserName sys -sysDBAPassword SysPassword1

dbca -silent -createDatabase \

 -templateName General_Purpose.dbc \

 -gdbname cdb1 -sid cdb1 -responseFile NO_VALUE \

 -characterSet AL32UTF8 \

 -sysPassword SysPassword1 \

 -systemPassword SysPassword1 \

 -createAsContainerDatabase true \

 -numberOfPDBs 0 \

 -databaseType MULTIPURPOSE \

 -memoryMgmtType auto_sga \

 -totalMemory 1536 \

 -storageType FS \

 -datafileDestination "/u02/oradata/" \

 -redoLogFileSize 50 \

 -emConfiguration NONE \

 -ignorePreReqs

sqlplus / as sysdba <<EOF

alter system set db_recovery_file_dest_size=40g;

alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';

alter system set db_create_file_dest = '/u02/oradata';

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

exit;

EOF

Rebuild the MYDB non-CDB database.

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=mydb

#dbca -silent -deleteDatabase -sourceDB mydb -sysDBAUserName sys -sysDBAPassword SysPassword1

dbca -silent -createDatabase \

 -templateName General_Purpose.dbc \

 -gdbname mydb -sid mydb -responseFile NO_VALUE \

 -characterSet AL32UTF8 \

 -sysPassword SysPassword1 \

 -systemPassword SysPassword1 \

 -createAsContainerDatabase false \

 -databaseType MULTIPURPOSE \

 -memoryMgmtType auto_sga \

 -totalMemory 1536 \

 -storageType FS \

 -datafileDestination "/u02/oradata/" \

 -redoLogFileSize 50 \

 -emConfiguration NONE \

 -ignorePreReqs

sqlplus / as sysdba <<EOF

alter system set db_recovery_file_dest_size=40g;

alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';

alter system set db_create_file_dest = '/u02/oradata';

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

exit;

EOF

Source: oracle-base.com

Related Posts

0 comments:

Post a Comment