DBA Hub

📋Steps in this guide1/7

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

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

oracle 21cconfigurationintermediate
by OracleDba
19 views
1

Assumptions

This article is focused on upgrading a non-CDB database to Oracle 21c. If your starting point is a database using the multitenant architecture, you should be reading this article instead. You should probably be using the AutoUpgrade method to perform this style of upgrade. This article assumes your source database is of a version supported for direct upgrade to 21c. 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. Remember, this article is not a replacement for reading the upgrade documentation . Each upgrade has the potential to be different, depending on what options are installed.

Code/Command (click line numbers to comment):

1
19c, 18c, 12.2
2

Prerequisites

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.

Code/Command (click line numbers to comment):

1
2
yum install -y oracle-database-preinstall-21c
yum update -y
3

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. Run the root scripts when prompted. At this point you should also patch the new Oracle home, but in this case we will forgo that step to keep things simple.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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

As a root user, execute the following script(s):
        1. /u01/app/oracle/product/21.0.0/dbhome_1/root.sh
4

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. We enable the fast recovery area, Oracle Managed Files (OMF) and archivelog mode. We 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. There are two settings necessary for Replay Upgrade to work, but they are the defaults, so we don't need to set these manually.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
#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.
$

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

alter database upgrade sync on;
alter database property set upgrade_pdb_on_open='true';
5

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. You can read about those here . We switch the non-CDB instance to read-only mode, describe the database using the package, then shutdown the non-CDB instance. We can see there are no user-defined PDBs in the root container. We create a pluggable database from the non-CDB database using the "/tmp/mydb.xml" file. We are using the option, which copies the datafiles to create the new PDB. We could use the option, which would use the existing files to save space. We see the following messages in alert log. 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. We can use the view to check for errors. In the alert log we see messages to indicate the upgrade and conversion are taking place. We can see the PDB is now open in read-write mode. We save the state so the PDB opens automatically with the instance. It's worth checking for violations. They should all be from before the upgrade and conversion. Run datapatch for the PDB.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
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

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>

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

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

alter pluggable database mydb open read write;

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

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 MYDB                           READ WRITE NO
SQL>

alter pluggable database mydb save state;

column name format a10
column message format a60

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

cd $ORACLE_HOME/OPatch
./datapatch -pdbs MYDB
6

Final Steps

We need to clean up the remains of the non-CDB instance. We can use the DBCA to do this. Edit the "/etc/oratab" file and any environment files as required. If you are using APEX or ORDS, you probably want to validate them ( validate APEX , validate ORDS ).

Code/Command (click line numbers to comment):

1
2
3
4
5
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
7

Appendix

The following commands are used to rebuild the databases if you want to rerun the examples. Rebuild the CDB1 multitenant database. Rebuild the MYDB non-CDB database. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
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

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!