Oracle 12c

Clone a pluggable database (PDB) within a CDB in Oracle 12c

clone a pdb within a cdb

Oracle has introduced new Multitenant Option from Oracle 12cR1 and this option allows a single container database (CDB) to host multiple pluggable databases.

Refer here for more details on Oracle Multitenant option.

Related articles:

Create Container Database (CDB) in Oracle 12c using DBCA
Create Container database (CDB) in silent mode
Drop a container database in silent mode
Create a pluggable database manually in Oracle 12c

In this post I will describe how to clone a pluggable database (PDB) within the same CDB.

Here Cloning a new PDB pdb3 from pdb2 within CDB cdb2, we will cover the following steps:

  1. Create a directory to store new PDB datafiles
  2. Set PDB2 (source pdb) in Open Read only mode
  3. Clone PDB from PDB2 to PDB3
  4. Check the newly created PDB status
  5. Open the PDB database
1. Create a directory to store new PDB Datafiles
mkdir /u01/app/oracle/oradata/cdb2/pdb3
2. Set pdb2(source PDB) in Open Read only mode
sqlplus "/as sysdba"
alter pluggable database pdb2 close;
alter pluggable database pdb2 open read only;

o/p:

[oracle@cdb12c /]$ . oraenv
ORACLE_SID = [cdb2] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@cdb12c /]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 18 15:51:40 2021
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter pluggable database pdb2 close;
Pluggable database altered.
SQL> alter pluggable database pdb2 open read only;
Pluggable database altered.
SQL>
3. Clone PDB from PDB2 to PDB3
create pluggable database pdb3 from pdb2
create_file_dest='/u01/app/oracle/oradata/cdb2/pdb3';

Output:

SQL> create pluggable database pdb3 from pdb2
  2  create_file_dest='/u01/app/oracle/oradata/cdb2/pdb3';

Pluggable database created.

SQL>
4. Check the newly created pdb status

Status of the PDBs can be checked using v$pdbs view,

select name, open_mode from v$pdbs;
show pdbs;

SQL> select name, open_mode from v$pdbs;

NAME            OPEN_MODE
--------------- ----------
PDB$SEED        READ ONLY
PDB1            READ WRITE
PDB2            READ ONLY
PDB3            MOUNTED  -->newly created pdb

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ ONLY  NO
         5 PDB3                           MOUNTED
SQL>

5. Open the PDB database in read-write mode
--Open source pdb if required:
alter pluggable database pdb2 close;
alter pluggable database pdb2 open;

--Open newly created pdb:
alter pluggable database pdb3 open;

Output:
SQL> alter pluggable database pdb2 close;
Pluggable database altered.
SQL> alter pluggable database pdb2 open;
Pluggable database altered.

SQL> alter pluggable database pdb3 open;
Pluggable database altered.
SQL>

We have successfully cloned PDB3 from PDB2 database and opened it in read-write mode.

Hope this helps…

Create a pluggable database manually in Oracle 12c

create pdb manually

Oracle has introduced new Multitenant Option from Oracle 12cR1 and this option allows a single container database (CDB) to host multiple pluggable databases.

Related articles:

Create Container Database (CDB) in Oracle 12c using DBCA
Create Container database (CDB) in silent mode
Drop a container database in silent mode

In this post I will describe how to create a pluggable database (PDB) from seed. We will cover the following steps:

  1. Create a directory to store new PDB datafiles
  2. Create PDB database using SQL*PLUS
  3. Check the newly created PDB status
  4. Open the PDB database
1. Create a directory to store new PDB Datafiles
mkdir /u01/app/oracle/oradata/cdb2/pdb2
2. Create PDB database using SQL*PLUS
CREATE PLUGGABLE DATABASE pdb2 
ADMIN USER admin_user IDENTIFIED BY Oracle_#123
CREATE_FILE_DEST='/u01/app/oracle/oradata/cdb2/pdb2';

Output:

SQL> CREATE PLUGGABLE DATABASE pdb2
  2  ADMIN USER admin_user IDENTIFIED BY Oracle_#123
  3  CREATE_FILE_DEST='/u01/app/oracle/oradata/cdb2/pdb2';

Pluggable database created.

SQL>
3. Check the newly created pdb status

Status of the PDBs can be checked using v$pdbs view,

select name, open_mode from v$pdbs;
show pdbs;

SQL> select name, open_mode from v$pdbs;

NAME            OPEN_MODE
--------------- ----------
PDB$SEED        READ ONLY
PDB1            READ WRITE
PDB2            MOUNTED

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           MOUNTED
SQL>
4. Open the PDB database in read-write mode
alter pluggable database pdb2 open;
select name, open_mode from v$pdbs;

Output:

SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME            OPEN_MODE
--------------- ----------
PDB$SEED        READ ONLY
PDB1            READ WRITE
PDB2            READ WRITE

We have created a new PDB pdb2 from seed database and opened it in read-write mode.

Hope this helps…

Create Container database (CDB) in silent mode

create cdb in silent mode

Oracle has introduced new Multitenant Option from Oracle 12cR1 and this option enables an Oracle database to function as a container database(CDB). A CDB includes zero, one, or many pluggable databases. A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB. All Oracle databases before Oracle Database 12c were non-CDBs.

Refer here for more details on Oracle Multitenant option.

Refer here to create the database using DBCA in graphical mode.

In this article I will demonstrate an overview of creating a CDB and PDB database using DBCA in silent mode

1. Set the environmental variables

export ORACLE_SID=cdb2
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin

2. Run dbca in silent mode

dbca -silent -createDatabase -templateName General_Purpose.dbc  -gdbname  cdb2 -sid cdb2 -characterSet AL32UTF8 -sysPassword Oracle_#123 -systemPassword Oracle_#123 -storageType FS -datafileDestination "/u01/app/oracle/oradata" -createAsContainerDatabase true -numberOfPDBs 1 -pdbName pdb1 -pdbAdminPassword Oracle_#123 -databaseType MULTIPURPOSE -memoryMgmtType auto_sga -memoryPercentage 20 -redoLogFileSize 50 -emConfiguration NONE

Output:

[oracle@cdb12c /]$ dbca -silent -createDatabase -templateName General_Purpose.dbc  -gdbname  cdb2 -sid cdb2 -characterSet AL32UTF8 -sysPassword Oracle_#123 -systemPassword Oracle_#123 -storageType FS -datafileDestination "/u01/app/oracle/oradata" -createAsContainerDatabase true -numberOfPDBs 1 -pdbName pdb1 -pdbAdminPassword Oracle_#123 -databaseType MULTIPURPOSE -memoryMgmtType auto_sga -memoryPercentage 20 -redoLogFileSize 50 -emConfiguration NONE
Copying database files
1% complete
13% complete
25% complete
Creating and starting Oracle instance
26% complete
30% complete
31% complete
35% complete
38% complete
39% complete
41% complete
Completing Database Creation
42% complete
43% complete
44% complete
46% complete
49% complete
50% complete
Creating Pluggable Databases
55% complete
75% complete
Executing Post Configuration Actions
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb2/cdb2.log" for further details.

3. Check the cdb and pdb status

[oracle@cdb12c oradata]$ . oraenv
ORACLE_SID = [cdbdev] ? cdb2
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@cdb12c oradata]$ sqlplus
Enter user-name: /as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
CDB2      READ WRITE
SQL>
SQL> select name, open_mode from v$pdbs;

NAME            OPEN_MODE
--------------- ----------
PDB$SEED        READ ONLY
PDB1            READ WRITE

SQL>
SQL> select name, pdb from v$services;

NAME            PDB
--------------- ---------------
SYS$BACKGROUND  CDB$ROOT
SYS$USERS       CDB$ROOT
cdb2            CDB$ROOT
pdb1            PDB1
cdb2XDB         CDB$ROOT

SQL>

Hope this helps…

Create Container Database (CDB) in Oracle 12c using DBCA

Create cdb using dbca

Oracle has introduced new Multitenant Option from Oracle 12cR1 and this option enables an Oracle database to function as a container database(CDB). A CDB includes zero, one, or many pluggable databases. A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB. All Oracle databases before Oracle Database 12c were non-CDBs.

Refer here for more details on Oracle Multitenant option.

In this article I will demonstrate an overview of creating a CDB and PDB database using DBCA.

Refer here to Create Container database (CDB) in silent mode

Here I am using VNC viewer for database creation in graphical mode. Refer the link to Install and configure VNC Server on Linux 7

Create CDB using DBCA

Select Create a database and click next

Select Advanced Configuration

Select Oracle Single instance database, leave the template section with default one selected and click on Next

Provide the details as per your requirement,
Global database name:
SID:
Select Create as Container datbase
Number of PDBs:
PDB Name:

Provide the Datafile storage type as per your environment

Specify the Recovery files storage type, Flash Recover Area and Size and click Next

Select the listener if already have one or Create a new one as below

Choose Automatic memory management and provide the Memory Target Size,

Provide the processes Count

Choose the Character set and click on Next

Select Configure Enterprise management option and provide the EM express port. If EM cloud control is in place, provided the details. Then click on Next

Provide the Password and click on Next

Select Create database and click next

Verify the parameters and click on Finish to start the DB creation.

Database has been successfully created, click on close

Connect to database and verify,

[oracle@cdb12c /]$ . oraenv
ORACLE_SID = [cdbdev] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@cdb12c /]$
[oracle@cdb12c /]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jan 17 20:47:35 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name, open_mode, cdb from v$database;

NAME      OPEN_MODE            CDB
--------- -------------------- ---
CDBDEV    READ WRITE           YES

SQL>

Check the PDB stauts

SQL> col name for a15
SQL> select name, open_mode from v$pdbs;

NAME            OPEN_MODE
--------------- ----------
PDB$SEED        READ ONLY
PDB1            READ WRITE

SQL> select name, pdb, network_name from v$services;

NAME                   PDB             NETWORK_NAME
--------------------   --------------- -------------------------
pdb1.localdomain       PDB1             pdb1.localdomain
SYS$BACKGROUND         CDB$ROOT
SYS$USERS              CDB$ROOT
cdbdevXDB              CDB$ROOT         cdbdevXDB
cdbdev.localdomain     CDB$ROOT         cdbdev.localdomain

SQL>

Container database CDBDEV and Pluggable database PDB1 created successfully.

Hope this helps…

Recover table to new name from RMAN backup in Oracle 12c

Oracle has introduced a new feature from Version 12c which will enable us to recover tables from RMAN backups using RECOVER TABLE command. In this article I will demonstrate how to recover a table with new name to a specified point in time using RMAN.

Refer the below link to get detailed steps on Recover table from RMAN backup on Oracle 12

Recover the table with different name

Connect to RMAN and run the RECOVER TABLE command with remap table,

rman target /

recover table faheem.tab1 until scn 1699874 
auxiliary destination '/home/oracle/backup'
remap table 'FAHEEM'.'TAB1':'TAB1_09102020';

Click here to see the log for the above command.

Verify the recovered table

conn faheem/faheem
SQL> select * from tab;

TNAME                     TABTYPE  CLUSTERID
------------------------- ------- ----------
TAB1                      TABLE
TAB1_09102020             TABLE

Hope this helps…

Oracle 12c Recover table from RMAN backup

Oracle has introduced a new feature from Version 12c which will enable us to recover tables from RMAN backups using RECOVER TABLE command. In this article I will demonstrate how to recover tables to a specified point in time using RMAN.

Refer the below URL to get more information,

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/bradv/rman-recovering-tables-partitions.html

Below informations are required to recover tables from an RMAN backup,

  • Names of the table that has to be recovered
  • Point in time/SCN to which the table has to be recovered
  • Whether the recovered tables must be imported into the target database

Below are the steps which I am testing here:

  1. Creation of test table and import some records
  2. Get the current SCN for PITR recovery
  3. Backup the database and archivelogs using RMAN
  4. Drop the table which has to be recovered
  5. Recover the table,
    • with the same name
    • with different name
  6. Verify the recovered tables

1. Create test table and import some records

Connect to database schema to create two tables tab1 and temp1 and insert some records

SQL> conn faheem/faheem
Connected.

create table faheem.tab1 (id number(7), name varchar2(20), name_ar varchar2(30));

insert into faheem.tab1(id, name, name_ar)
select rownum, 'Employee ' || to_char(rownum), 'اسم'|| to_char(rownum)
from dual
connect by level <= 100000;
commit;

create table temp1 as select * from tab1;
2. Get the current SCN for PITR recovery
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1699874

3. Backup the database and archivelogs using RMAN

RMAN> run
{
BACKUP AS COMPRESSED BACKUPSET DATABASE 
TAG 'FULLDB'
format '/home/oracle/rmanbackup/db2/db_level1_%t_%s_p%p';
BACKUP AS COMPRESSED BACKUPSET 
ARCHIVELOG ALL NOT BACKED UP 1 TIMES 
TAG 'ARCHBKP'
format '/home/oracle/rmanbackup/db2/al_%t_%s_p%p';
backup current controlfile
TAG 'CTL'
format '/home/oracle/rmanbackup/db2/cf_%t_%s_p%p';
}

4. Drop the tables which has to be recovered and purge recyclebin

SQL> conn faheem/faheem
Connected.
SQL> drop table temp1;
Table dropped.

SQL> drop table tab1;
Table dropped.

SQL> select * from tab;

TNAME                          TABTYPE     CLUSTERID
------------------------------ ---------   ---------
BIN$sUOvFIk8I2TgU9E4qMCInw==$0 TABLE

BIN$sUOvFIk9I2TgU9E4qMCInw==$0 TABLE

SQL> purge recyclebin;
Recyclebin purged.

SQL> select * from tab;
no rows selected

SQL>

5. Recover the table

Here I am testing the table recovery with two ways,

  1. Recover the table with the same name
  2. Recover the table with different name

Recover the table table temp1 with the same name

Connect to RMAN and run the RECOVER TABLE command

rman target /	

recover table faheem.temp1 until scn 1699874
auxiliary destination '/home/oracle/backup';

Click here to see the logflie

Recover the table tab1 with different name

Connect to RMAN and run the RECOVER TABLE command with remap table,

rman target /

recover table faheem.tab1 until scn 1699874 
auxiliary destination '/home/oracle/backup'
remap table 'FAHEEM'.'TAB1':'TAB1_09102020';

Click here to see the log for the above comand.

6. Verify the recovered tables

conn faheem/faheem
SQL> select * from tab;

TNAME                     TABTYPE  CLUSTERID
------------------------- ------- ----------
TEMP1                     TABLE
TAB1_09102020             TABLE

Hope this helps…

Upgrade to Oracle 19c using Autoupgrade utility

Oracle 19c Autoupgrade

Oracle Database Autoupgrade Utility is a new feature designed in Oracle 19c to automate the Upgrade process which Identifies issues before upgrade, Performs Preupgrade actions, Deploying the upgrades and Performs Post upgrade actions . You can upgrade multiple databases at the same time using a single configuration file.

Refer the below links to get more information on Oracle 19c Autoupgrade Utility,

https://mikedietrichde.com/2019/04/29/the-new-autoupgrade-utility-in-oracle-19c/

https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/using-autoupgrade-oracle-database-upgrades.html#GUID-71883C8C-7A34-4E93-8955-040CB04F2109

Description

In this article I will demonstrate an overview on upgrading Oracle database from 12.2.0.1 to 19.3.0.0 using Oracle Database Autoupgrade utility.

Below are the High level steps:

  1. Install Oracle 19.3.0.0 binaries
  2. Prerequisite for Autoupgrade
  3. Create the config file
  4. Analyze the database
  5. Deploy the upgrade
  6. Post upgrade task

Environment Details:

Source 		Hostname:		new19c
		Database version:	12.2.0.1
		Database Name:		cdbdev
		ORACLE_HOME:		/u01/app/oracle/product/12.2.0/db_1/


Target 		Hostname:		new19c 
		Databaes Version:	19.3.0.0
		Database name:		cdbdev
		ORACLE_HOME:		/u01/app/oracle/product/19.3.0/dbhome_1

Source DB Details

SQL> select name, open_mode, version, status from v$database, v$instance;

NAME      OPEN_MODE            VERSION           STATUS
--------- -------------------- ----------------- ------------
CDBDEV    READ WRITE           12.2.0.1.0        OPEN

1. Install Oracle 19.3.0.0 binaries

I have already completed the installation of 19c, Refer here for step by step installation of Oracle 19.3.0.0 on Linux 7 64bit.

2. Prerequisite for Autoupgrade

Download the latest autoupgrade.jar file

Autoupgrade utility autoupgrade.jar file exists by default under $ORACLE_HOME/rdbms/admin directory from Oracle 19.3 release onwards, however Oracle strongly recommends to download the latest AutoUpgrade version before doing the upgrade. Click here to download the latest version.

Replace the autoupgrade.jar with the latest version downloaded

[oracle@new19c ~]$ mv $ORACLE_HOME/rdbms/admin/autoupgrade.jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar-bkp
[oracle@new19c ~]$ cp /tmp/autoupgrade.jar $ORACLE_HOME/rdbms/admin/
[oracle@new19c ~]$
[oracle@new19c ~]$ $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -version  build.hash e84c9c2
build.version 19.10.0
build.date 2020/10/23 10:36:46
build.max_target_version 19
build.supported_target_versions 12.2,18,19
build.type production

Java version

Java version should be 8 or later, which is available by default in Oracle Database homes from release 12.1.0.2 and latest.

[oracle@new19c temp]$ $ORACLE_HOME/jdk/bin/java -version
java version "1.8.0_201"
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)
[oracle@new19c temp]$

3. Create the config file

Create a directory to hold all upgrade config and log files.

[oracle@new19c ~]$ mkdir /u01/19c-autoupg
[oracle@new19c ~]$ cd /u01/19c-autoupg

Create the sample config file

cd /u01/19c-autoupg
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/jdk/bin
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -create_sample_file config

--output
[oracle@new19c ~]$ cd /u01/19c-autoupg
[oracle@new19c 19c-autoupg]$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@new19c 19c-autoupg]$ export PATH=$PATH:$ORACLE_HOME/jdk/bin
[oracle@new19c 19c-autoupg]$ $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -create_sample_file config
Created sample configuration file /u01/19c-autoupg/sample_config.cfg

Modify the config file

Copy the sample config file and make the necessary changes as per the database environment.

cd /u01/19c-autoupg
cp sample_config.cfg cdbdev_db_config.cfg
vi cdbdev_db_config.cfg

This is the config file I used for upgrade:

[oracle@new19c 19c-autoupg]$ cat cdbdev_db_config.cfg
global.autoupg_log_dir=/u01/19c-autoupg/upg_logs
#
# Database cdbdev
#
upg1.dbname=cdbdev
upg1.start_time=NOW
upg1.source_home=/u01/app/oracle/product/12.2.0/db_1/
upg1.target_home=/u01/app/oracle/product/19.3.0/dbhome_1
upg1.sid=cdbdev
upg1.log_dir=/u01/19c-autoupg/upg_logs/cdbdev
upg1.upgrade_node=new19c
upg1.target_version=19.3
upg1.run_utlrp=yes
upg1.timezone_upg=yes

4. Analyze the database

Autoupgrade Analyze mode checks your database to see if it is ready for the upgrade. This will reads data from the database and does not perform any updates.

Execute autoupgrade in analyze mode with the below syntax,

export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/jdk/bin
cd /u01/19c-autoupg
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config cdbdev_db_config.cfg -mode ANALYZE

Output

[oracle@new19c 19c-autoupg]$ $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config cdbdev_db_config.cfg -mode ANALYZE
AutoUpgrade tool launched with default options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be analyzed
Type 'help' to list console commands
upg> lsdg
Unrecognized cmd: lsdg
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME| UPDATED|                     MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
| 100| cdbdev|PRECHECKS|PREPARING|RUNNING|20/11/19 03:27|03:27:28|Loading database information|
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
Total jobs 1

upg> Job 100 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished successfully     [1]
Jobs failed                    [0]
Jobs pending                   [0]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 100 for cdbdev

[oracle@new19c 19c-autoupg]$

We can monitor, manage and control the jobs from the autoupgrade console. Example:

lsj – to list the jobs
status – to show the job status
tasks – shows the tasks executing

All Analyze logs are created under autoupg_log_dir

[oracle@new19c 100]$ cd /u01/19c-autoupg/upg_logs/cdbdev/cdbdev/100/prechecks/
[oracle@new19c prechecks]$ ls -l
total 536
-rwx------. 1 oracle oinstall   5051 Nov 19 03:28 cdbdev_checklist.cfg
-rwx------. 1 oracle oinstall  18050 Nov 19 03:28 cdbdev_checklist.json
-rwx------. 1 oracle oinstall  17101 Nov 19 03:28 cdbdev_checklist.xml
-rwx------. 1 oracle oinstall  36704 Nov 19 03:28 cdbdev_preupgrade.html
-rwx------. 1 oracle oinstall  17649 Nov 19 03:28 cdbdev_preupgrade.log
-rwx------. 1 oracle oinstall 158030 Nov 19 03:28 prechecks_cdb_root.log
-rwx------. 1 oracle oinstall 140241 Nov 19 03:28 prechecks_pdbdev.log
-rwx------. 1 oracle oinstall 139243 Nov 19 03:28 prechecks_pdb_seed.log
[oracle@new19c prechecks]$ 

We can review the html file (cdbdev_preupgrade.html) which will list all precheck Errors, warnings and recommendations.

5. Deploy the upgrade

Autoupgrade Deploy mode performs the actual upgrade of the database from preugrade source database analysis to post-upgrade checks.

Note: Before deploying the upgrade, you must have a backup plan in place.

Execute the autoupgrade in DEPLOY mode using the below syntax,

export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1export PATH=$PATH:$ORACLE_HOME/jdk/bincd /u01/19c-autoupg$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config cdbdev_db_config.cfg -mode DEPLOY

Once the upgrade process is started consider monitoring the logs to see the progress of the upgrade. Autoupgrade logs are available under,

/u01/19c-autoupg/upg_logs/cdbdev/cdbdev/101/dbupgrade

Output

[oracle@new19c 19c-autoupg]$ $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config cdbdev_db_config.cfg -mode DEPLOY
AutoUpgrade tool launched with default options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be processed
Type 'help' to list console commands

upg> tasks
+--+-------------+-------------+
|ID|         NAME|         Job#|
+--+-------------+-------------+
| 1|         main|      WAITING|
|35|     jobs_mon|      WAITING|
|36|      console|     RUNNABLE|
|37| queue_reader|      WAITING|
|38|        cmd-0|      WAITING|
|54|job_manager-0|      WAITING|
|56|   event_loop|TIMED_WAITING|
|57|   bqueue-101|      WAITING|
|61|     quickSQL|     RUNNABLE|
+--+-------------+-------------+
upg>
upg> logs
AutoUpgrade logs folder [/u01/19c-autoupg/upg_logs/cfgtoollogs/upgrade/auto]
logs folder [cdbdev][/u01/19c-autoupg/upg_logs/cdbdev/cdbdev]

upg> lsj
+----+-------+---------+---------+--------+--------------+--------+----------------------------+
|Job#|DB_NAME|    STAGE|OPERATION|  STATUS|    START_TIME| UPDATED|                     MESSAGE|
+----+-------+---------+---------+--------+--------------+--------+----------------------------+
| 101| cdbdev|PREFIXUPS|EXECUTING|FINISHED|20/11/19 03:46|03:48:44|Loading database information|
+----+-------+---------+---------+--------+--------------+--------+----------------------------+
Total jobs 1

upg> lsj
+----+-------+-----+---------+-------+--------------+--------+----------------------+
|Job#|DB_NAME|STAGE|OPERATION| STATUS|    START_TIME| UPDATED|               MESSAGE|
+----+-------+-----+---------+-------+--------------+--------+----------------------+
| 101| cdbdev|DRAIN|EXECUTING|RUNNING|20/11/19 03:46|03:48:52|Shutting down database|
+----+-------+-----+---------+-------+--------------+--------+----------------------+
Total jobs 1

upg> status
---------------- Config -------------------
User configuration file    [/u01/19c-autoupg/cdbdev_db_config.cfg]
General logs location      [/u01/19c-autoupg/upg_logs/cfgtoollogs/upgrade/auto]
Mode                       [DEPLOY]
DB upg fatal errors        ORA-00600,ORA-07445
DB Post upgrade abort time [60] minutes
DB upg abort time          [1440] minutes
DB restore abort time      [120] minutes
DB GRP abort time          [3] minutes
------------------------ Jobs ------------------------
Total databases in configuration file [1]
Total Non-CDB being processed         [0]
Total CDB being processed             [1]
Jobs finished successfully            [0]
Jobs finished/aborted                 [0]
Jobs in progress                      [1]
Jobs stage summary
    Job ID: 101
    DB name: cdbdev
        SETUP             <1 min
        GRP               <1 min
        PREUPGRADE        <1 min
        PRECHECKS         <1 min
        PREFIXUPS         1 min
        DRAIN             <1 min
        DBUPGRADE         12 min (IN PROGRESS)
------------ Resources ----------------
Threads in use                        [32]
JVM used memory                       [115] MB
CPU in use                            [13%]
Processes in use                      [18]

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME| UPDATED|             MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------------------+
| 101| cdbdev|DBUPGRADE|EXECUTING|RUNNING|20/11/19 03:46|04:38:42|70%Upgraded CDB$ROOT|
+----+-------+---------+---------+-------+--------------+--------+--------------------+
Total jobs 1

upg> /
+----+-------+---------+---------+-------+--------------+--------+--------------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME| UPDATED|             MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------------------+
| 101| cdbdev|DBUPGRADE|EXECUTING|RUNNING|20/11/19 03:46|06:31:20|95%Upgraded PDB$SEED|
+----+-------+---------+---------+-------+--------------+--------+--------------------+
Total jobs 1

upg> /
+----+-------+----------+---------+-------+--------------+--------+-------------+
|Job#|DB_NAME|     STAGE|OPERATION| STATUS|    START_TIME| UPDATED|      MESSAGE|
+----+-------+----------+---------+-------+--------------+--------+-------------+
| 101| cdbdev|POSTFIXUPS|EXECUTING|RUNNING|20/11/19 03:46|06:38:37|Remaining 1/9|
+----+-------+----------+---------+-------+--------------+--------+-------------+
Total jobs 1

upg> /
+----+-------+----------+---------+-------+--------------+--------+----------------------------+
|Job#|DB_NAME|     STAGE|OPERATION| STATUS|    START_TIME| UPDATED|                     MESSAGE|
+----+-------+----------+---------+-------+--------------+--------+----------------------------+
| 101| cdbdev|POSTFIXUPS|EXECUTING|RUNNING|20/11/19 03:46|06:43:37|Loading database information|
+----+-------+----------+---------+-------+--------------+--------+----------------------------+
Total jobs 1

upg> /
+----+-------+-----------+---------+-------+--------------+--------+---------------------+
|Job#|DB_NAME|      STAGE|OPERATION| STATUS|    START_TIME| UPDATED|              MESSAGE|
+----+-------+-----------+---------+-------+--------------+--------+---------------------+
| 101| cdbdev|POSTUPGRADE|EXECUTING|RUNNING|20/11/19 03:46|06:45:16|Creating final SPFILE|
+----+-------+-----------+---------+-------+--------------+--------+---------------------+
Total jobs 1

upg> /
+----+-------+-----------+---------+-------+--------------+--------+----------+
|Job#|DB_NAME|      STAGE|OPERATION| STATUS|    START_TIME| UPDATED|   MESSAGE|
+----+-------+-----------+---------+-------+--------------+--------+----------+
| 101| cdbdev|POSTUPGRADE|EXECUTING|RUNNING|20/11/19 03:46|06:46:01|Restarting|
+----+-------+-----------+---------+-------+--------------+--------+----------+
Total jobs 1

upg> Job 101 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished successfully     [1]
Jobs failed                    [0]
Jobs pending                   [0]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 101 for cdbdev

---- Drop GRP at your convenience once you consider it is no longer needed ----
Drop GRP from cdbdev: drop restore point AUTOUPGRADE_9212_CDBDEV122010

[oracle@new19c 19c-autoupg]$

Check the upg_summary.log

Oracle Database Release 19 Post-Upgrade Status Tool    11-19-2020 06:30:0
Container Database: CDBDEV
[CON_ID: 2 => PDB$SEED]

Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS

Oracle Server                             VALID      19.3.0.0.0  00:34:10
JServer JAVA Virtual Machine              VALID      19.3.0.0.0  00:02:25
Oracle XDK                                VALID      19.3.0.0.0  00:01:19
Oracle Database Java Packages             VALID      19.3.0.0.0  00:00:10
OLAP Analytic Workspace                   VALID      19.3.0.0.0  00:00:55
Oracle Label Security                     VALID      19.3.0.0.0  00:00:11
Oracle Database Vault                     VALID      19.3.0.0.0  00:03:00
Oracle Text                               VALID      19.3.0.0.0  00:00:42
Oracle Workspace Manager                  VALID      19.3.0.0.0  00:00:52
Oracle Real Application Clusters     OPTION OFF      19.3.0.0.0  00:00:00
Oracle XML Database                       VALID      19.3.0.0.0  00:03:32
Oracle Multimedia                         VALID      19.3.0.0.0  00:00:46
Spatial                                   VALID      19.3.0.0.0  00:09:15
Oracle OLAP API                           VALID      19.3.0.0.0  00:00:26
Datapatch                                                        00:04:50
Final Actions                                                    00:05:11
Post Upgrade                                                     00:02:06
Post Compile                                                     00:11:29

Total Upgrade Time: 01:17:50 [CON_ID: 2 => PDB$SEED * ]
Asterisks denotes compilation time has been included during the upgrade process.

Database time zone version is 26. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.


Upgrade Times Sorted In Descending Order

Total Upgrade Time: 01:17:50 [CON_ID: 2 => PDB$SEED * ]
Total Upgrade Time: 01:06:31 [CON_ID: 1 => CDB$ROOT]
Total Upgrade Time: 01:06:12 [CON_ID: 3 => PDBDEV]
Grand Total Upgrade Time:    [0d:2h:42m:43s]
[oracle@new19c dbupgrade]$

Timezone file upgrade and database recompilation has already completed by the autoupgrade utility as the below values are adjusted as “yes” in the config file,

upg1.run_utlrp=yes =yes  # yes(default) to run utlrp as part of upgrade
upg1.timezone_upg=yes # yes(default) to upgrade timezone if needed

Check the Timezone version

SQL> SELECT VERSION FROM V$TIMEZONE_FILE;

   VERSION
----------
        32

SQL>

Check the db details

SQL> select name, open_mode, version, status from v$database, v$instance;

NAME      OPEN_MODE            VERSION           STATUS
--------- -------------------- ----------------- ------------
CDBDEV    READ WRITE           19.0.0.0.0        OPEN

SQL>

6. Post-upgrade task

Once the upgrade is successful and all testing is done, drop the restore point.

Drop the Guaranteed restore point

SQL> select name from v$restore_point;
NAME
------------------------------
AUTOUPGRADE_9212_CDBDEV122010
SQL>
SQL> drop restore point AUTOUPGRADE_9212_CDBDEV122010;
Restore point dropped.
SQL>

Change the compatible parameter

Note: After the upgrade, database has to be tested properly before updating the compatible parameter. Once the parameter is updated database cannot be downgraded.

show parameter compatible
alter system set compatible='19.0.0' scope=spfile;
shutdown immediate;
startup;
show parameter compatible

--output
SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.2.0
noncdb_compatible                    boolean     FALSE
SQL> alter system set compatible='19.0.0' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1560278096 bytes
Fixed Size                  9135184 bytes
Variable Size             973078528 bytes
Database Buffers          570425344 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.
SQL> 
SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      19.0.0
noncdb_compatible                    boolean     FALSE
SQL>

It’s Done. Database is successfully upgraded from 12c to 19c.

Thank You for reading my post.

Hope this helps…