Oracle Database

Create Container database (CDB) in silent mode

Posted on January 19, 2021 by Faheem CDB Oracle 12c Oracle Database PDB 0

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

Posted on January 19, 2021 by Faheem CDB Oracle 12c Oracle Database PDB 0

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

Posted on December 31, 2020 by Faheem Oracle 12c Oracle Database RMAN 0

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

Oracle 12c Recover table from RMAN backup

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

Posted on December 31, 2020 by Faheem Oracle 12c Oracle Database RMAN 0

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:

Creation of test table and import some records
Get the current SCN for PITR recovery
Backup the database and archivelogs using RMAN
Drop the table which has to be recovered
Recover the table,
- with the same name
- with different name
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,

Recover the table with the same name
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

Posted on November 26, 2020 by Faheem Oracle 12c Oracle 19c Oracle Database 4

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:

Install Oracle 19.3.0.0 binaries
Prerequisite for Autoupgrade
Create the config file
Analyze the database
Deploy the upgrade
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…

Upgrade Oracle database manually from 12c to 19c

Posted on November 23, 2020 by Faheem Oracle 12c Oracle 19c Oracle Database 0

Manually Upgrade database from 12c to 19c

Description

In this article I will demonstrate an overview on manually upgrading Oracle database from 12.2.0.1 to 19.3.0.0 on Oracle Linux 7 64bit platform. As the source database version is 12.2.0.1, we can go for a direct upgrade to 19c.

Below Oracle versions can be directly upgraded to 19c. Refer this document

11.2.0.4
12.1.0.2
12.2.0.1
18c

Below is the high level steps:

Installing Oracle 19.3.0.0 binaries
Executing the preupgrade jar tool
Performing the preupgrade actions
Backing up the database / Create a guaranteed restore point
Upgrade the database
Perform the postupgrade actions

Environment Details:

Source 		Hostname:		new12c
		Database version:	12.2.0.1
		Database Name:		dev12c
		ORACLE_HOME:		/u01/app/oracle/product/12.2.0/db_1


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

1. Installing Oracle 19c 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. Executing the pre-upgrade jar tool

Pre-upgrade information tool is used to determine the instance readiness before upgrading the database. The pre-upgrade script will generate the fix for many issues before you upgrade to new Oracle home.

The Pre-upgrade Information Tool is available in the new release Oracle home, in path ORACLE_HOME/rdbms/admin/preupgrade.jar

Run the pre-upgrade tool

$ORACLE_BASE/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar

Make sure to run the tool from source ORACLE_HOME.

$ORACLE_BASE/product/12.2.0/db_1/jdk/bin/java -jar $ORACLE_BASE/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar DIR /u01/preupgrade

Output:

export ORACLE_SID=db12c
export ORACLE_HOME=$ORACLE_BASE/product/12.2.0/db_1

[oracle@new12c db12c]$ $ORACLE_BASE/product/12.2.0/db_1/jdk/bin/java -jar $ORACLE_BASE/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar DIR /u01/preupgrade/
==================
PREUPGRADE SUMMARY
==================
  /u01/preupgrade/preupgrade.log
  /u01/preupgrade/preupgrade_fixups.sql
  /u01/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups
@/u01/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/u01/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2020-11-17T02:21:12
[oracle@new12c db12c]$

Detailed output is generated in DIR path, in my case /u01/preupgrade/preupgrade.log

[oracle@new12c preupgrade]$ cat preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-11-17T02:21:12

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  DB12C
     Container Name:  db12c
       Container ID:  0
            Version:  12.2.0.1.0
     DB Patch Level:  No Patch Bundle applied
         Compatible:  12.2.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  26
  Database log mode:  ARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  JServer JAVA Virtual Machine           [to be upgraded]  VALID
  Oracle XDK for Java                    [to be upgraded]  VALID
  Real Application Clusters              [to be upgraded]  OPTION OFF
  Oracle Workspace Manager               [to be upgraded]  VALID
  OLAP Analytic Workspace                [to be upgraded]  VALID
  Oracle Label Security                  [to be upgraded]  VALID
  Oracle Database Vault                  [to be upgraded]  VALID
  Oracle Text                            [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID
  Oracle Java Packages                   [to be upgraded]  VALID
  Oracle Multimedia                      [to be upgraded]  VALID
  Oracle Spatial                         [to be upgraded]  VALID
  Oracle OLAP API                        [to be upgraded]  VALID

==============
BEFORE UPGRADE
==============

  REQUIRED ACTIONS
  ================
  1.  Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 4625
      MB.  Check alert log during the upgrade to ensure there is remaining free
      space available in the recovery area.

      DB_RECOVERY_FILE_DEST_SIZE is set at 4096 MB.  There is currently 4089 MB
      of free space remaining, which may not be adequate for the upgrade.

      Currently:
       Fast recovery area :  /u01/app/oracle/fast_recovery_area/db12c
       Limit              :  4096 MB
       Used               :  7169 KB
       Available          :  4089 MB

      The database has archivelog mode enabled, and the upgrade process will
      need free space to generate archived logs to the recovery area specified
      by initialization parameter DB_RECOVERY_FILE_DEST.  The logs generated
      must not overflow the limit set by DB_RECOVERY_FILE_DEST_SIZE, as that
      can cause the upgrade to not proceed.

  RECOMMENDED ACTIONS
  ===================
  2.  (AUTOFIXUP) Gather stale data dictionary statistics prior to database
      upgrade in off-peak time using:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Dictionary statistics do not exist or are stale (not up-to-date).

      Dictionary statistics help the Oracle optimizer find efficient SQL
      execution plans and are essential for proper upgrade timing. Oracle
      recommends gathering dictionary statistics in the last 24 hours before
      database upgrade.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  3.  (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.

      None of the fixed object tables have had stats collected.

      Gathering statistics on fixed objects, if none have been gathered yet, is
      recommended prior to upgrading.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  INFORMATION ONLY
  ================
  4.  To help you keep track of your tablespace allocations, the following
      AUTOEXTEND tablespaces are expected to successfully EXTEND during the
      upgrade process.

                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      SYSAUX                             470 MB       500 MB
      SYSTEM                             800 MB       912 MB
      TEMP                                32 MB       150 MB
      UNDOTBS1                            70 MB       439 MB

      Minimum tablespace sizes for upgrade are estimates.

  5.  Check the Oracle Backup and Recovery User's Guide for information on how
      to manage an RMAN recovery catalog schema.

      If you are using a version of the recovery catalog schema that is older
      than that required by the RMAN client version, then you must upgrade the
      catalog schema.

      It is good practice to have the catalog schema the same or higher version
      than the RMAN client version you are using.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database DB12C
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

    SQL>@/u01/preupgrade/preupgrade_fixups.sql

=============
AFTER UPGRADE
=============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  6.  Upgrade the database time zone file using the DBMS_DST package.

      The database is using time zone file version 26 and the target 19 release
      ships with time zone file version 32.

      Oracle recommends upgrading to the desired (latest) version of the time
      zone file.  For more information, refer to "Upgrading the Time Zone File
      and Timestamp with Time Zone Data" in the 19 Oracle Database
      Globalization Support Guide.

  7.  To identify directory objects with symbolic links in the path name, run
      $ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
      Recreate any directory objects listed, using path names that contain no
      symbolic links.

      Some directory object path names may currently contain symbolic links.

      Starting in Release 18c, symbolic links are not allowed in directory
      object path names used with BFILE data types, the UTL_FILE package, or
      external tables.

  8.  (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
      command:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Oracle recommends gathering dictionary statistics after upgrade.

      Dictionary statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. After a database
      upgrade, statistics need to be re-gathered as there can now be tables
      that have significantly changed during the upgrade or new tables that do
      not have statistics gathered yet.

  9.  Gather statistics on fixed objects after the upgrade and when there is a
      representative workload on the system using the command:

        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

      This recommendation is given for all preupgrade runs.

      Fixed object statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans.  Those
      statistics are specific to the Oracle Database release that generates
      them, and can be stale upon database upgrade.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database DB12C
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

    SQL>@/u01/preupgrade/postupgrade_fixups.sql


[oracle@new12c preupgrade]$

3. Performing the pre-upgrade actions

Run the preupgrade_fixups.sql

SQL> @/u01/preupgrade/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-11-17 02:21:02

For Source Database:     DB12C
Source Database Version: 12.2.0.1.0
For Upgrade to Version:  19.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    1.  min_recovery_area_size    NO          Manual fixup required.
    2.  dictionary_stats          YES         None.
    3.  pre_fixed_objects         YES         None.
    4.  tablespaces_info          NO          Informational only.
                                              Further action is optional.
    5.  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.

SQL> SQL>

The preupgrade_fixups.sql output lists multiple recommendations which has to be fix manually. We will fix the below recommendations manually and re-run the preupgrade_fixups.sql

    1.  min_recovery_area_size    NO          Manual fixup required.
    2.  dictionary_stats          YES         None.
    3.  pre_fixed_objects         YES         None.
    4.  tablespaces_info          NO          Informational only.
                                              Further action is optional.
    5.  rman_recovery_version     NO          Informational only.
                                              Further action is optional.

Recommendations 1 and 4 has to be fixed manually.

Here I am ignoring point No: 5 rman_recovery_verison as there is no recovery catalog configured in my environment.

Recommendation 1: min_recovery_area_size

    1.  Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 4625
      MB.  Check alert log during the upgrade to ensure there is remaining free
      space available in the recovery area.

      DB_RECOVERY_FILE_DEST_SIZE is set at 4096 MB.  There is currently 4089 MB
      of free space remaining, which may not be adequate for the upgrade.

      Currently:
       Fast recovery area :  /u01/app/oracle/fast_recovery_area/db12c
       Limit              :  4096 MB
       Used               :  7169 KB
       Available          :  4089 MB

      The database has archivelog mode enabled, and the upgrade process will
      need free space to generate archived logs to the recovery area specified
      by initialization parameter DB_RECOVERY_FILE_DEST.  The logs generated
      must not overflow the limit set by DB_RECOVERY_FILE_DEST_SIZE, as that
      can cause the upgrade to not proceed.

Action: Increase the DB_RECOVERY_FILE_DEST_SIZE parameter

sqlplus "/as sysdba"
alter system set db_recovery_file_dest_size=6000M scope=both;

Recommendation 4: tablespaces_info

  4.  To help you keep track of your tablespace allocations, the following
      AUTOEXTEND tablespaces are expected to successfully EXTEND during the
      upgrade process.

                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      SYSAUX                             470 MB       500 MB
      SYSTEM                             800 MB       912 MB
      TEMP                                32 MB       150 MB
      UNDOTBS1                            70 MB       439 MB

Action: increase the datafile size

alter database datafile '/u01/app/oracle/oradata/db12c/system01.dbf' resize 912M;
alter database datafile '/u01/app/oracle/oradata/db12c/sysaux01.dbf' resize 500M;
alter database datafile '/u01/app/oracle/oradata/db12c/undotbs01.dbf' resize 440M;
alter database tempfile '/u01/app/oracle/oradata/db12c/temp01.dbf' resize 200M;

Now re-run the preupgrade_fixups.sql

SQL> @/u01/preupgrade/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-11-17 02:21:02

For Source Database:     DB12C
Source Database Version: 12.2.0.1.0
For Upgrade to Version:  19.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    1.  dictionary_stats          YES         None.
    2.  pre_fixed_objects         YES         None.
    3.  tablespaces_info          NO          Informational only.
                                              Further action is optional.
    4.  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.

SQL> exit

4. Backing up the database / Create a guaranteed restore point

It is recommended to take a backup of database using RMAN or create a guaranteed restore point. Ensure proper failback plan in place.

Creating a guranteed restore point:

create restore point before_upgrade_19c guarantee flashback database;

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME                 GUARANTEE TIME
-------------------- --------- --------------------------------
BEFORE_UPGRADE_19C   YES        17-NOV-20 04.07.59.000000000 AM

4. Upgrading the database

Once the pre-upgrade actions are completed, shutdown the database to start the upgrade process

sqlplus /as sysdba
shutdown immediate;
exit

Copy the spfile, password file and listener file from old ORACLE_HOME to new 19c ORACLE_HOME

[oracle@new12c preupgrade]$ cp $ORACLE_HOME/dbs/orapwdb12c /u01/app/oracle/product/19.3.0/dbhome_1/dbs/
[oracle@new12c preupgrade]$ cp $ORACLE_HOME/dbs/spfiledb12c.ora /u01/app/oracle/product/19.3.0/dbhome_1/dbs/

[oracle@new12c preupgrade]$ cp $ORACLE_HOME/network/admin/listener.ora /u01/app/oracle/product/19.3.0/dbhome_1/network/admin

[oracle@new12c preupgrade-2]$ cd /u01/app/oracle/product/19.3.0/dbhome_1/dbs/
[oracle@new12c dbs]$ ls -l *db12c*
-rw-r-----. 1 oracle oinstall 3584 Nov 17 04:47 orapwdb12c
-rw-r-----. 1 oracle oinstall 3584 Nov 17 04:47 spfiledb12c.ora

Stop listener running on 11g home and start it from 19c home

[oracle@ol7-dev ~]$ lsnrctl stop

[oracle@ol7-dev ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@ol7-dev ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@ol7-dev ~]$ lsnrctl start

Start the database from 19c ORACLE_HOME and start the upgrade.

export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus /as sysdba
startup upgrade;
select name,open_mode,status from v$database, v$instance;

--output--
[oracle@ol7-dev ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@ol7-dev ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@ol7-dev ~]$
[oracle@ol7-dev ~]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 8 04:48:01 2020
Version 19.3.0.0.0

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL>
SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 1258290752 bytes
Fixed Size                  8896064 bytes
Variable Size             805306368 bytes
Database Buffers          436207616 bytes
Redo Buffers                7880704 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> select name,open_mode,status from v$database, v$instance;

NAME      OPEN_MODE            STATUS
--------- -------------------- ------------
DB12C     READ WRITE           OPEN MIGRATE
SQL> exit

Run the DB Upgrade utility

$ORACLE_HOME/bin/dbupgrade

Output:

[oracle@new12c db12c]$ $ORACLE_HOME/bin/dbupgrade

Argument list for [/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in                       c = 0
.
.
.
<output truncated>
.
.
.
------------------------------------------------------
Phases [0-107]         End Time:[2020_11_17 11:56:33]
------------------------------------------------------

Grand Total Time: 3781s

 LOG FILES: (/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/db12c/upgrade20201117105328/catupgrd*.log)

Upgrade Summary Report Located in:
/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/db12c/upgrade20201117105328/upg_summary.log

Grand Total Upgrade Time:    [0d:1h:3m:1s]
[oracle@new12c db12c]$

Check the upgrade summary log,

[oracle@new12c db12c]$ cat /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/db12c/upgrade20201117105328/upg_summary.log

Oracle Database Release 19 Post-Upgrade Status Tool    11-17-2020 11:56:0
Database Name: DB12C

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

Oracle Server                          UPGRADED      19.3.0.0.0  00:25:05
JServer JAVA Virtual Machine           UPGRADED      19.3.0.0.0  00:02:26
Oracle XDK                             UPGRADED      19.3.0.0.0  00:02:20
Oracle Database Java Packages          UPGRADED      19.3.0.0.0  00:00:29
OLAP Analytic Workspace                UPGRADED      19.3.0.0.0  00:00:22
Oracle Label Security                  UPGRADED      19.3.0.0.0  00:00:12
Oracle Database Vault                  UPGRADED      19.3.0.0.0  00:00:38
Oracle Text                            UPGRADED      19.3.0.0.0  00:01:20
Oracle Workspace Manager               UPGRADED      19.3.0.0.0  00:01:16
Oracle Real Application Clusters       UPGRADED      19.3.0.0.0  00:00:00
Oracle XML Database                    UPGRADED      19.3.0.0.0  00:03:11
Oracle Multimedia                      UPGRADED      19.3.0.0.0  00:01:34
Spatial                                UPGRADED      19.3.0.0.0  00:12:38
Oracle OLAP API                        UPGRADED      19.3.0.0.0  00:00:27
Datapatch                                                        00:07:25
Final Actions                                                    00:07:35
Post Upgrade                                                     00:00:25

Total Upgrade Time: 01:00:39

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.

Grand Total Upgrade Time:    [0d:1h:3m:1s]
[oracle@new12c db12c]$

DB is in shutdown state after the dbupgrade process. Start the database. Check the database component status.

export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus / as sysdba
startup
SELECT name, open_mode, status, version from v$database, v$instance;

Output:

output:
[oracle@new12c flashback]$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@new12c flashback]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@new12c flashback]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 17 23:50:53 2020
Version 19.3.0.0.0

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 1157627168 bytes
Fixed Size                  8895776 bytes
Variable Size            1040187392 bytes
Database Buffers          100663296 bytes
Redo Buffers                7880704 bytes
Database mounted.
Database opened.
SQL>


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

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

Execute Post-Upgrade Status Tool, utlusts.sql

@$ORACLE_HOME/rdbms/admin/utlusts.sql TEXT

SQL> @$ORACLE_HOME/rdbms/admin/utlusts.sql TEXT

Oracle Database Release 19 Post-Upgrade Status Tool    11-18-2020 00:15:2
Database Name: DB12C

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

Oracle Server                          UPGRADED      19.3.0.0.0  00:25:05
JServer JAVA Virtual Machine           UPGRADED      19.3.0.0.0  00:02:26
Oracle XDK                             UPGRADED      19.3.0.0.0  00:02:20
Oracle Database Java Packages          UPGRADED      19.3.0.0.0  00:00:29
OLAP Analytic Workspace                UPGRADED      19.3.0.0.0  00:00:22
Oracle Label Security                  UPGRADED      19.3.0.0.0  00:00:12
Oracle Database Vault                  UPGRADED      19.3.0.0.0  00:00:38
Oracle Text                            UPGRADED      19.3.0.0.0  00:01:20
Oracle Workspace Manager               UPGRADED      19.3.0.0.0  00:01:16
Oracle Real Application Clusters       UPGRADED      19.3.0.0.0  00:00:00
Oracle XML Database                    UPGRADED      19.3.0.0.0  00:03:11
Oracle Multimedia                      UPGRADED      19.3.0.0.0  00:01:34
Spatial                                UPGRADED      19.3.0.0.0  00:12:38
Oracle OLAP API                        UPGRADED      19.3.0.0.0  00:00:27
Datapatch                                                        00:07:25
Final Actions                                                    00:07:35
Post Upgrade                                                     00:00:25

Total Upgrade Time: 01:00:39

Database time zone version is 32. It meets current release needs.

SQL>

Recompile the INVALID Objects using utlrp.sql

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

5. Performing the post-upgrade actions

Connect to sqlplus and run the postupgrade_fixups.sql script

sqlplus / as sysdba
@/u01/preupgrade/postupgrade_fixups.sql


--output--SQL> @/u01/preupgrade/postupgrade_fixups.sql

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Package created.

No errors.

Package body created.

PL/SQL procedure successfully completed.

No errors.

Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2020-11-17 02:21:12

For Source Database:     DB12C
Source Database Version: 12.2.0.1.0
For Upgrade to Version:  19.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    6.  old_time_zones_exist      NO          Manual fixup recommended.
    7.  dir_symlinks              YES         None.
    8.  post_dictionary           YES         None.
    9.  post_fixed_objects        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 upgrade is not
fully complete.  To resolve the outstanding issues, start by reviewing
the postupgrade_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.


Session altered.
SQL>

Output from postupgrade_fixups.sql recommends the below,

Upgrade the timezone file version
Gather statistics on Fixed objects.

Upgrade the database timezone file

Scripts to upgrade Timezone file versions is available under ORACLE_HOME/rdbms/admin directory from Oracle 18c onwards.

To get how much TIMESTAMP WITH TIME ZONE date is there in database using stats info.
$ORACLE_HOME/rdbms/admin/utltz_countstats.sql

Get the approximate TIMESTAMP WITH TIME ZONE data there is in a database using a COUNT(*) for each table that has a TSTZ column
$ORACLE_HOME/rdbms/admin/utltz_countstar.sql

Timezone upgrade check script
$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql

Timezone aply script.
$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql

Upgrade steps:

sqlplus / as sysdba
SELECT version FROM v$timezone_file;
@$ORACLE_HOME/rdbms/admin/utltz_countstats.sql;
$ORACLE_HOME/rdbms/admin/utltz_countstar.sql;
$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql;
$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql;

--Output:
SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        26

SQL> @$ORACLE_HOME/rdbms/admin/utltz_countstats.sql

Session altered.
 .
 Amount of TSTZ data using num_rows stats info in DBA_TABLES.
 .
 For SYS tables first ...
 Note: empty tables are not listed.
 Stat date  - Owner.TableName.ColumnName - num_rows
.
.
<output truncated>
.
.
17/11/2020 - WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1
 Total numrows of non-SYS TSTZ columns is : 8
 There are in total 20 non-SYS TSTZ columns.
 Total Minutes elapsed : 0

Session altered.

SQL>

SQL> @$ORACLE_HOME/rdbms/admin/utltz_countstar.sql
Session altered.

 .
 Estimating amount of TSTZ data using COUNT(*).
 This might take some time ...
.
.
<output truncated>
.
.
WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1
 Total count * of non-SYS TSTZ columns is :  8
 There are in total 20 non-SYS TSTZ columns.
 Total Minutes elapsed : 0

Session altered.

SQL>

SQL> @$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql

Session altered.

INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
INFO: Database RDBMS DST version is DSTv26 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv32 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.

Session altered.

SQL>
SQL> @?/rdbms/admin/utltz_upg_apply.sql

Session altered.

INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv32 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 1157627168 bytes
Fixed Size                  8895776 bytes
Variable Size            1006632960 bytes
Database Buffers          134217728 bytes
Redo Buffers                7880704 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 1157627168 bytes
Fixed Size                  8895776 bytes
Variable Size            1006632960 bytes
Database Buffers          134217728 bytes
Redo Buffers                7880704 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv32 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.

Session altered.
SQL>

Check the updated timezone version

SQL> SELECT version FROM v$timezone_file;

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

1 row selected.

Gather statistics on fixed objects

Connect to sqlplus as sys user and execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS

sqlplus / as sysdba
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
exit

--output
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

SQL>

Now re-run the postupgrade_fixups.sql

sqlplus / as sysdba
 @/u01/preupgrade/postupgrade_fixups.sql
exit

--output
SQL> @/u01/preupgrade/postupgrade_fixups.sql
No errors.
No errors.
No errors.
No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2020-11-17 02:21:12

For Source Database:     DB12C
Source Database Version: 12.2.0.1.0
For Upgrade to Version:  19.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    6.  old_time_zones_exist      YES         None.
    7.  dir_symlinks              YES         None.
    8.  post_dictionary           YES         None.
    9.  post_fixed_objects        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 upgrade is not
fully complete.  To resolve the outstanding issues, start by reviewing
the postupgrade_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.
SQL>

Drop the restore point

Once the upgrade is successfull, drop the restore point otherwise at some point you will run out of space.

select name from v$restore_point;
drop restore point BEFORE_UPGRADE_19C;

--output
SQL> select name from v$restore_point;

NAME
-------------------
BEFORE_UPGRADE_19C

SQL> drop restore point BEFORE_UPGRADE_19C;

Restore point dropped.

Update 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.

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


--output:
SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;

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

Total System Global Area 1157627168 bytes
Fixed Size                  8895776 bytes
Variable Size             973078528 bytes
Database Buffers          167772160 bytes
Redo Buffers                7880704 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter compatible;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      19.0.0

Database has been successfully upgraded to 19c.

[oracle@ol7-dev ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 13 20:07:34 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 name, open_mode, version from v$database, v$instance;

NAME      OPEN_MODE            VERSION
--------- -------------------- -----------------
ORADEV    READ WRITE           19.0.0.0.0

SQL>

Hope this helps…

Error in invoking target ‘agent nmhs’ of makefile

Posted on November 16, 2020 by Faheem Oracle 11g Oracle Database 0

You may get the following error while trying to install Oracle 11g v11.2.0.4 on linux x86-64 bit machines,

Error in invoking target 'agent nmhs' of makefile '/u01/app/oracle/product/11.2.0/dbhome_1/sysman/lib/ins_emagent.mk'. See '/u01/app/oraInventory/logs/installActions2018-01-03_03-49-24PM.log' for details.

Solution:

Edit the file: $ORACLE_HOME/sysman/lib/ins_emagent.mk

Search for line:

$(MK_EMAGENT_NMECTL)

Replace it with

$(MK_EMAGENT_NMECTL) -lnnz11

Then click retry button to continue the installation.

Upgrading Oracle database from 11g to 19c

Posted on November 13, 2020 by Faheem Oracle 11g Oracle 19c Oracle Database 3

Description

In this article I will demonstrate an overview on manually upgrading Oracle database from 11.2.0.4 to 19.3.0.0 on Oracle Linux 7 64bit platform.

In this example, the source database version is 11.2.0.4. So we can go for a direct upgrade to 19c.

Below Oracle versions can be directly upgraded to 19c. Refer this document

11.2.0.4
12.1.0.2
12.2.0.1
18c

We will be covering the following steps:

Installing Oracle 19.3.0.0 binaries
Executing the preupgrade jar tool
Performing the preupgrade actions
Upgrade the database
Perform the postupgrade actions

Environment Details:

Source 		Hostname:		ol7-dev
		Database version:	11.2.0.4
		Database Name:		oradev
		ORACLE_HOME:		/u01/app/oracle/product/11.2.0/db_1


Target 		Hostname:		ol7-dev 
		Databaes Version:	19.3.0.0
		Database name:		oradev
		ORACLE_HOME:		/u01/app/oracle/product/19.3.0/dbhome_1

1. Installing Oracle 19c 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. Executing the pre-upgrade jar tool

The Pre-upgrade Information Tool is available in the new release Oracle home, in path ORACLE_HOME/rdbms/admin/preupgrade.jar

$ORACLE_BASE/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar

Run the pre-upgrade tool

Make sure to run the tool from source ORACLE_HOME.

$ORACLE_BASE/product/11.2.0/db_1/jdk/bin/java -jar $ORACLE_BASE/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar DIR /u01/preupgrade

Output:

export ORACLE_SID=oradev
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

[oracle@ol7-dev u01]$ $ORACLE_BASE/product/11.2.0/db_1/jdk/bin/java -jar $ORACLE_BASE/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar DIR /u01/preupgrade
==================
PREUPGRADE SUMMARY
==================
  /u01/preupgrade/preupgrade.log
  /u01/preupgrade/preupgrade_fixups.sql
  /u01/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups
@/u01/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/u01/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2020-11-07T22:23:28
[oracle@ol7-dev u01]$

Detailed output is generated in DIR path, in my case /u01/preupgrade/preupgrade.log

3. Performing the pre-upgrade actions

Run the preupgrade_fixups.sql

SQL> @/u01/preupgrade/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-11-07 22:23:20

For Source Database:     ORADEV
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.  em_present                NO          Manual fixup recommended.
    3.  amd_exists                NO          Manual fixup recommended.
    4.  apex_manual_upgrade       NO          Manual fixup recommended.
    5.  dictionary_stats          YES         None.
    6.  trgowner_no_admndbtrg     YES         None.
    7.  pre_fixed_objects         YES         None.
    8.  tablespaces_info          NO          Informational only.
                                              Further action is optional.
    9.  exf_rul_exists            NO          Informational only.
                                              Further action is optional.
   10.  rman_recovery_version     NO          Informational only.
                                              Further action is optional.

The preupgrade_fixups.sql output lists multiple recommendations which has to be fix manually. We will fix the below recommendations manually and re-run the preupgrade_fixups.sql

1.  parameter_min_val         NO          Manual fixup recommended.
2.  em_present                NO          Manual fixup recommended.
3.  amd_exists                NO          Manual fixup recommended.
4.  apex_manual_upgrade       NO          Manual fixup recommended.

Recommendation 1: parameter_min_val

  1.  Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums.
      This action may be done now or when starting the database in upgrade mode
      using the 19 ORACLE HOME.

       Parameter                                 Currently  19 minimum
       ---------                                 ---------  ------------------
       processes                                       150                 300

      The database upgrade process requires certain initialization parameters
      to meet minimum values.  The Oracle upgrade process itself has minimum
      values which may be higher and are marked with an asterisk.  After
      upgrading, those asterisked parameter values may be reset if needed.

Action: Increase the process parameter

sqlplus "/as sysdba"
alter system set processes=300 scope=spfile;
shutdown immediate;
startup;

Recommendation 2: Remove the EM repositor

Stop the em dbconsole

[oracle@ol7-dev u01]$ emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://ol7-dev:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...
 ...  Stopped.

Copy emremove.sql from target 19c ORACLE_HOME/rdbms/admin to source 11g ORACLE_HOME/rdbms/admin

[oracle@ol7-dev admin]$ cp /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/emremove.sql /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/

Connect to the database using SYS user and run emremove.sql

[oracle@ol7-dev admin]$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 7 22:53:58 2020

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @$ORACLE_BASE/product/11.2.0/db_1/rdbms/admin/emremove.sql
old  70:     IF (upper('&LOGGING') = 'VERBOSE')
new  70:     IF (upper('VERBOSE') = 'VERBOSE')

PL/SQL procedure successfully completed.

SQL>

Recommendation 3: Remove OLTP catalog

Connect to the database as SYS user and remove OLAP Catalog by running the 11.2.0.4.0 SQL script under $ORACLE_HOME/olap/admin/catnoamd.sql script.

SQL> @$ORACLE_HOME/olap/admin/catnoamd.sql
Synonym dropped.
Synonym dropped.
.
<output truncated>
.
Type dropped.
PL/SQL procedure successfully completed.
Role dropped.
PL/SQL procedure successfully completed.
1 row deleted.
SQL>

Recommendation 4: Upgrade Oracle Application Express (APEX) manually.

To upgrade to latest Application Express, download the latest version from the below link here

Upgrading APEX

Check the current APEX Version

SQL> Select Comp_name, status, Version
     From Dba_Registry
     where comp_id='APEX';

COMP_NAME                                STATUS       VERSION
---------------------------------------- ------------ ------------------------------
Oracle Application Express               VALID        3.2.1.00.12

SQL>

From the directory which holds the APEX unzipped software, connect to sqlplus as SYS user and run apexins.sql

cd /u01/software/db-soft/apex
sqlplus /as sysdba
SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/

Check the upgraded APEX Version

SQL> Select Comp_name, status, Version
     From Dba_Registry
     where comp_id='APEX';

COMP_NAME                                STATUS       VERSION
---------------------------------------- ------------ ------------------------------
Oracle Application Express               VALID        20.2.0.00.20

SQL>

Note: No Actions required from Recommendation 5, 6 and 7

Recommendation 8: Extend the tablespaces with minimum size requirement


  8.  To help you keep track of your tablespace allocations, the following AUTOEXTEND tablespaces are expected to successfully EXTEND during the upgrade process.

                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      SYSAUX                             550 MB       775 MB
      SYSTEM                             750 MB      1178 MB
      TEMP                                29 MB       150 MB
      UNDOTBS1                            90 MB       446 MB

Alter the datafile size:

alter database datafile '/u01/app/oracle/oradata/oradev/system01.dbf' resize 1178M;
alter database datafile '/u01/app/oracle/oradata/oradev/sysaux01.dbf' resize 775M;
alter database datafile '/u01/app/oracle/oradata/oradev/undotbs01.dbf' resize 446M;
alter database tempfile '/u01/app/oracle/oradata/oradev/temp01.dbf' resize 150M;

Now re-run the preupgrade_fixups.sql

SQL> @/u01/preupgrade/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-11-07 22:23:20

For Source Database:     ORADEV
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         YES         None.
    2.  em_present                YES         None.
    3.  amd_exists                YES         None.
    4.  apex_manual_upgrade       YES         None.
    5.  dictionary_stats          YES         None.
    6.  trgowner_no_admndbtrg     YES         None.
    7.  pre_fixed_objects         YES         None.
    8.  tablespaces_info          NO          Informational only.
                                              Further action is optional.
    9.  exf_rul_exists            NO          Informational only.
                                              Further action is optional.
   10.  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.

SQL>

4. Upgrading the database

Once the pre-upgrade actions are completed, shutdown the database to start the upgrade process

sqlplus /as sysdba
shutdown immediate;
exit

Copy the spfile, password file and listener file from old ORACLE_HOME to new 19c ORACLE_HOME

[oracle@ol7-dev ~]$ cp $ORACLE_HOME/dbs/orapworadev /u01/app/oracle/product/19.3.0/dbhome_1/dbs/
[oracle@ol7-dev ~]$ cp $ORACLE_HOME/dbs/spfileoradev.ora /u01/app/oracle/product/19.3.0/dbhome_1/dbs/

[oracle@ol7-dev ~]$ cp $ORACLE_HOME/network/admin/listener.ora /u01/app/oracle/product/19.3.0/dbhome_1/network/admin

Stop listener running on 11g home and start it from 19c home

[oracle@ol7-dev ~]$ lsnrctl stop

[oracle@ol7-dev ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@ol7-dev ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@ol7-dev ~]$ lsnrctl start

Start the database from 19c ORACLE_HOME and start the upgrade.

export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus /as sysdba
startup upgrade;

--output--
[oracle@ol7-dev ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@ol7-dev ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@ol7-dev ~]$
[oracle@ol7-dev ~]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 8 04:48:01 2020
Version 19.3.0.0.0

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL>
SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 1258290752 bytes
Fixed Size                  8896064 bytes
Variable Size             805306368 bytes
Database Buffers          436207616 bytes
Redo Buffers                7880704 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> exit

Run the DB Upgrade utility

$ORACLE_HOME/bin/dbupgrade

--output--
Argument list for [/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in                       c = 0
Do not run in                C = 0
.
.
.
<output truncated>
.
.
.
Grand Total Time: 3342s

 LOG FILES: (/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/oradev/upgrade20201108045317/catupgrd*.log)

Upgrade Summary Report Located in:
/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/oradev/upgrade20201108045317/upg_summary.log

Grand Total Upgrade Time:    [0d:0h:55m:42s]
[oracle@ol7-dev ~]$

DB is in shutdown state after the dbupgrade process. Start the database

sqlplus / as sysdba
startup
exit

5. Performing the post-upgrade actions

Connect to sqlplus and run the postupgrade_fixups.sql script

sqlplus / as sysdba
@/u01/preupgrade/postupgrade_fixups.sql


--output--
SQL> @/u01/preupgrade/postupgrade_fixups.sql

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Package created.

No errors.

Package body created.

PL/SQL procedure successfully completed.

No errors.

Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2020-11-07 22:23:25

For Source Database:     ORADEV
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
------  ------------------------  ----------  --------------------------------
   11.  old_time_zones_exist      NO          Manual fixup recommended.
   12.  dir_symlinks              YES         None.
   13.  post_dictionary           YES         None.
   14.  post_fixed_objects        NO          Informational only.
                                              Further action is optional.
   15.  upg_by_std_upgrd          YES         None.

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 upgrade is not fully complete.  To resolve the outstanding issues, start by reviewing the postupgrade_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 messagefrom the preupgrade which explains in more detail what still needs to be done.
 
PL/SQL procedure successfully completed.
 
Session altered.
 
SQL>

Output from postupgrade_fixups.sql recommeds to upgrade the time zone file version and Gather statistics on fixed objects. Below is the recommended action from preupgrade.jar output file.

RECOMMENDED ACTIONS
===================
11. Upgrade the database time zone file using the DBMS_DST package. 

The database is using time zone file version 14 and the target 19 release ships with time zone file version 32.

Oracle recommends upgrading to the desired (latest) version of the time zone file.  For more information, refer to "Upgrading the Time Zone File and Timestamp with Time Zone Data" in the 19 Oracle Database Globalization Support Guide.

  14. Gather statistics on fixed objects after the upgrade and when there is a representative workload on the system using the command:

        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

This recommendation is given for all preupgrade runs.

Fixed object statistics provide essential information to the Oracle optimizer to help it find efficient SQL execution plans.  Those statistics are specific to the Oracle Database release that generates them, and can be stale upon database upgrade.

For information on managing optimizer statistics, refer to the 11.2.0.4 Oracle Database Performance Tuning Guide.

Upgrade the database timezone file

Connect to sqlplus and start the database in upgrade mode

sqlplus / as sysdba
shutdown immediate;
startup upgrade;

Check the current timezone file version

SELECT * FROM v$timezone_file;

--output:
SQL> SELECT * FROM v$timezone_file;

FILENAME		VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_14.dat 	     14 	 0

Start the upgrade window

SET SERVEROUTPUT ON
DECLARE
l_tz_version PLS_INTEGER;
BEGIN
l_tz_version := DBMS_DST.get_latest_timezone_version;
DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
DBMS_DST.begin_upgrade(l_tz_version);
END;

--output
SQL> SET SERVEROUTPUT ON
DECLARE
l_tz_version PLS_INTEGER;
BEGIN
l_tz_version := DBMS_DST.get_latest_timezone_version;
DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
DBMS_DST.begin_upgrade(l_tz_version);
END;
/SQL>   2    3    4    5    6    7    8
l_tz_version=32
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

Start the database in normal mode

shutdown immediate;
startup;

Do the upgrade

SET SERVEROUTPUT ON
DECLARE
  l_failures   PLS_INTEGER;
BEGIN
  DBMS_DST.upgrade_database(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
  DBMS_DST.end_upgrade(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;


--output
SQL> SET SERVEROUTPUT ON
DECLARE
  l_failures   PLS_INTEGER;
BEGIN
  DBMS_DST.upgrade_database(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
  DBMS_DST.end_upgrade(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/SQL>   2    3    4    5    6    7    8    9  
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
.
.
<output truncated>
.
.
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
DBMS_DST.upgrade_database : l_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : l_failures=0

PL/SQL procedure successfully completed.

SQL>

Check the new timezone settings

SELECT * FROM v$timezone_file;

--output
SQL> SELECT * FROM v$timezone_file;

FILENAME		VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat 	     32 	 0

Gather statistics on fixed objects

Connect to sqlplus as sys user and execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS

sqlplus / as sysdba
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
exit

--output
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

SQL>

Now re-run the postupgrade_fixups.sql

sqlplus / as sysdba
 @/u01/preupgrade/postupgrade_fixups.sql
exit

--output
SQL> @/u01/preupgrade/postupgrade_fixups.sql

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Package created.

No errors.

Package body created.

PL/SQL procedure successfully completed.

No errors.

Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2020-11-07 22:23:25

For Source Database:     ORADEV
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
------  ------------------------  ----------  --------------------------------
   11.  old_time_zones_exist      YES         None.
   12.  dir_symlinks              YES         None.
   13.  post_dictionary           YES         None.
   14.  post_fixed_objects        NO          Informational only.
                                              Further action is optional.
   15.  upg_by_std_upgrd          YES         None.

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 upgrade is not fully complete.  To resolve the outstanding issues, start by reviewing the postupgrade_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 messagefrom the preupgrade which explains in more detail what still needs to be done.
 
PL/SQL procedure successfully completed.
 
Session altered.
 
SQL>

Database has been successfully upgraded to 19c.

[oracle@ol7-dev ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 13 20:07:34 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 name, open_mode, version from v$database, v$instance;

NAME      OPEN_MODE            VERSION
--------- -------------------- -----------------
ORADEV    READ WRITE           19.0.0.0.0

SQL>

Manually Upgrade APEX for 19c upgrade

Posted on November 13, 2020 by Faheem APEX Oracle Database 0

To upgrade to latest Application Express, download the latest version from the below link here

Upgrading APEX

Check the current APEX Version

SQL> Select Comp_name, status, Version
     From Dba_Registry
     where comp_id='APEX';

COMP_NAME                                STATUS       VERSION
---------------------------------------- ------------ ------------------------------
Oracle Application Express               VALID        3.2.1.00.12

SQL>

2. From the directory which holds the APEX unzipped software, connect to sqlplus as SYS user and run apexins.sql

cd /u01/software/db-soft/apex
sqlplus /as sysdba
SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/

3. check the upgraded APEX Version

SQL> Select Comp_name, status, Version
     From Dba_Registry
     where comp_id='APEX';

COMP_NAME                                STATUS       VERSION
---------------------------------------- ------------ ------------------------------
Oracle Application Express               VALID        20.2.0.00.20

SQL>

APEX has been successfully upgraded to version 20.2.0.00.20…

Remove Oracle Enterprise Manager in 19c Upgrade

Posted on November 13, 2020 by Faheem Enterprise Manager Oracle 11g Oracle 19c Oracle Database 0

For upgrading Oracle database from 11g to 19c, we need to remove the Enterprise manager repository manually. Below are the steps,

Stop the em dbconsole If database control is configured.

[oracle@ol7-dev u01]$ emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://ol7-dev:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...
 ...  Stopped.

2. Copy emremove.sql from target 19c ORACLE_HOME/rdbms/admin to source 11g ORACLE_HOME/rdbms/admin

[oracle@ol7-dev admin]$ cp /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/emremove.sql /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/

3. Connect to source database using sys user and run emremove.sql

[oracle@ol7-dev admin]$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 7 22:53:58 2020

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @$ORACLE_BASE/product/11.2.0/db_1/rdbms/admin/emremove.sql
old  70:     IF (upper('&LOGGING') = 'VERBOSE')
new  70:     IF (upper('VERBOSE') = 'VERBOSE')

PL/SQL procedure successfully completed.

SQL>

EM repository has been removed successfully…

Oracle Database

1. Set the environmental variables

2. Run dbca in silent mode

3. Check the cdb and pdb status

Recover the table with different name

Verify the recovered table

1. Create test table and import some records

2. Get the current SCN for PITR recovery

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

5. Recover the table

Recover the table table temp1 with the same name

Recover the table tab1 with different name

6. Verify the recovered tables

Description

1. Install Oracle 19.3.0.0 binaries

2. Prerequisite for Autoupgrade

Download the latest autoupgrade.jar file

3. Create the config file

4. Analyze the database

5. Deploy the upgrade

6. Post-upgrade task

Description

1. Installing Oracle 19c binaries.

2. Executing the pre-upgrade jar tool

Run the pre-upgrade tool

3. Performing the pre-upgrade actions

4. Backing up the database / Create a guaranteed restore point

4. Upgrading the database

5. Performing the post-upgrade actions

Description

1. Installing Oracle 19c binaries.

2. Executing the pre-upgrade jar tool

3. Performing the pre-upgrade actions

4. Upgrading the database

5. Performing the post-upgrade actions

Recent Posts

Categories

Visitor Count

Archives

Tags