Oracle 19c Database Upgrade From 11.2.0.4 to 19.2.0.0 Using DBUA

Description:-

There are different ways of upgrading to the latest release of Oracle database and Oracle provides multiple methods to upgrade. Few are listed below:

  • Database Upgrade Assistant (DBUA)
  • Manual Upgrade
  • Transportable Tablespaces
  • Datapump export/import
  • Oracle Streams
  • Oracle GoldenGate

Here I am going to choose DBUA to upgrade my database,

DBUA provides a graphical user interface to guide you through the upgrade of Oracle Database.

Oracle Database Releases That Support Direct Upgrade

Review the supported options for direct upgrades to the latest Oracle Database release.
You can perform a direct upgrade to the new release from the following releases:
• 11.2.0.4
• 12.1.0.1 and 12.1.0.2
• 12.2.0.1

Steps for upgrade from 11.2.0.4 to 19.2.0.0 using DBUA :-

Presteps for db upgrade :-

Take RMAN full backups before upgrade.

rman target / 

run {
allocate channel test_backup_disk1 type disk format '/u01/backup/%d_%U';
backup incremental level 0 tag 'ORCL_BEFORE_UPG'  database;
backup format  tag 'ORCL_CONTROL_FILE' current controlfile;
release channel test_backup_disk1;
}

Ensure backup is complete before upgrade.

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

no rows selected

Empty Recycle bin.

SQL> PURGE DBA_RECYCLEBIN ;

DBA Recyclebin purged.

Run Gather statistics to finish upgrade soon.

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

Remove EM Database Control :-

Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target 19.2.0.0.0 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.

[oracle@19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 14 20:56:29 2019

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


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> @emremove.sql
old 70: IF (upper('&LOGGING') = 'VERBOSE')
new 70: IF (upper('VERBOSE') = 'VERBOSE')

PL/SQL procedure successfully completed.

Remove OLAP Catalog :-

[oracle@19c ~]$ cd $ORACLE_HOME/olap/admin/

SQL> @catnoamd.sql

Purge Recyclebin :-

SQL> purge recyclebin;

Recyclebin purged.

Run the preupgrade tool.

The preupgrade.jar Pre-Upgrade Information Tool is supplied with Oracle Database 19c.
This tool has reside in new oracle home.
/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar

oracle@19c ~]$ /u01/app/oracle/product/11.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE 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: 2019-03-14T21:28:55

Run 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: 2019-03-14 21:28:51

For Source Database: UPG19C
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. mv_refresh NO Informational only.
Further action is optional.
8. pre_fixed_objects YES None.
9. tablespaces_info NO Informational only.
Further action is optional.
10. exf_rul_exists NO Informational only.
Further action is optional.
11. 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.

Run DBUA upgrade Utility :

Now run the dbua utility from 19c oracle_home location.

[oracle@19c ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@19c ~]$ export ORACLE_SID=upg19c
[oracle@19c ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@19c ~]$ dbua
  • select the Upgrade Oracle Database option, then click the “Next” button.

  • If the prerequisite checks highlight any issues, take the appropriate action to fix the issues. If all the prerequisite checks are passed, click the “Next” button.

  • Amend the upgrade options if necessary, then click the “Next” button.

  • Select the recovery options for use in the event of an upgrade failure, then click the “Next” button.

  •  If the database is using the 11g listener and you need to upgraded, use existing listener or create new 19c listener

  • If you want configure EM database express enable it and provide port number or uncheck it.

Now the database upgrade is complete and the database is ready for normal use.

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

VERSION    NAME   OPEN_MODE
---------  ------ ----------
19.0.0.0.0 UPG19C READ WRITE

SQL> SELECT * FROM v$timezone_file;

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

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
32
Catch Me On:- Hariprasath Rajaram  Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba                       FB Group:https://www.facebook.com/groups/894402327369506/              FB Page: https://www.facebook.com/dbahariprasath/?                 Twitter: https://twitter.com/hariprasathdba

Step by Step to configure Oracle 19c Data Guard Physical Standby

Environment Details

Parameters needs to configure both side for Data guard,

Primary side:
db_name:- Same name for all primary and standby database
db_unique_name:-a unique db name to uniquely identify primary and standby db because db_name is same
Log_archive_dest_1:-local archive destination
Log_archive_dest_2:-destination for standby database
Standby_file_management:-automatically  create file at standby db site

Standby Side:
db_unique_name:-it has to be different from DB_NAME parameter
db_file_name_convert:-specify the path name and datafile location of primary database datafile followed by standby location
log_file_name_convert:-specify the path name and redo logfile location of primary database redo logfile followed by standby location
log_archive_dest_1:-location of archives coming from primary database
fal_server:-to fetch archived log from primary site when log gap is detected at standby site.The parameter value is tns service name
fal_client:- to copies missing archive to standby database

Step 1:-Check FORCE LOGGING is enabled

Step 2:- Make sure primary is in archivelog mode

Step 3:- PFILE creation and copy to standby database

Step 4:-Edit the parameters and directories in created pfile

Step 5:- Parameters for primary database

Step 6:- Copy password file from primary to standby server

Step 7:- Make the respective directories for standby database

Step 8:- Keep the database in NOMOUNT stage to create standby database

Step 9:- Create spfile for standby database

Step 10:- Listener and TNS Configuration

Step 11:-Start the listener in Standby database and check the status

Step 12:-In standby side,check RMAN connection.Connect primary database as TARGET and standby database as AUXILIARY(creating new instance)

Step 13:-Active Duplication for creating standby database

Step 14:- Post check status for Standby database

Step 15:- Enable the recovery(MRP Process)

Step 16:- Check the Standby database sync status with primary

PRIMARY SIDE CONFIGURATION:-

[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 13 18:58:05 2019
Version 19.2.0.0.0

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

Connected to an idle instance.

SQL> startup 
ORACLE instance started.

Total System Global Area 2415917872 bytes
Fixed Size 8899376 bytes
Variable Size 654311424 bytes
Database Buffers 1744830464 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.

SQL> select force_logging from v$database;

FORCE_LOGGING
------------------
NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence 3
SQL> shut immediate 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2415917872 bytes
Fixed Size 8899376 bytes
Variable Size 654311424 bytes
Database Buffers 1744830464 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> create pfile from spfile;

File created.


Check the Datafiles,Log files location in Primary side:

SQL> show parameter db_name;

NAME                     TYPE            VALUE
---------------------- ----------- ------------------
db_name                  string          dbwr

SQL> show parameter db_unique_name;

NAME TYPE VALUE
---------------------- ----------- --------------------
db_unique_name            string         dbwr

SQL> select file_name from dba_data_files;

FILE_NAME
-----------------------------------------------------------------------
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/datafile/o1_mf_system_g78vt7wo_.dbf

/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/datafile/o1_mf_sysaux_g78vx8f6_.dbf

/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/datafile/o1_mf_users_g78vyq0c_.dbf

/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/datafile/o1_mf_undotbs1_g78vyor1_.dbf

Dynamically change parameter and Configure it in Primary side:

SQL> alter system set log_archive_config='dg_config=(dbwr,standby)';

System altered.

SQL> alter system set log_archive_dest_2='service=standby noaffirm async valid_for=(online_logfiles,primary_role) db_unique_name=standby';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;

System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET FAL_SERVER=STANDBY;

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

Create standby controlfile:

SQL> alter database add standby logfile ('/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/redo1.log') size 50m;

Database altered.

SQL> alter database add standby logfile ('/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/redo2.log') size 50m;

Database altered.

SQL> alter database add standby logfile ('/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/redo3.log') size 50m;

Database altered.

SQL> alter database add standby logfile ('/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/redo4.log') size 50m;

Database altered.

PRIMARY SIDE:

[oracle@ram admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

DBWR =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.doyen.com)(PORT = 1521))
)
)
[oracle@ram admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DBWR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.doyen.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dbwr)
(UR = A)
)
)

STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.doyen.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STANDBY)
(UR = A)
)
)
[oracle@ram admin]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-MAR-2019 00:43:46

Copyright (c) 1991, 2018, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias DBWR
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 15-MAR-2019 22:54:24
Uptime 0 days 1 hr. 49 min. 21 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ram/dbwr/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ram.doyen.com)(PORT=1521)))
Services Summary...
Service "dbwr" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Service "dbwrXDB" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@ram admin]$

STANDBY SIDE:

[oracle@standby ~]$ cd $ORACLE_HOME
[oracle@standby dbhome_1]$ cd network/admin/
[oracle@standby admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_STANDBY =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dbwr)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = dbwr)
)
)

STANDBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.doyen.com)(PORT = 1521))
)
)
[oracle@standby admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DBWR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.doyen.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dbwr)
(UR = A)
)
)

STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.doyen.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STANDBY)
(UR = A)
)
)

[oracle@standby admin]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-MAR-2019 00:57:37

Copyright (c) 1991, 2018, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias STANDBY
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 16-MAR-2019 00:00:08
Uptime 0 days 0 hr. 57 min. 29 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/standby/standby/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby.doyen.com)(PORT=1521)))
Services Summary...
Service "dbwr" has 1 instance(s).
Instance "dbwr", status UNKNOWN, has 1 handler(s) for this service...
Service "standby" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@standby admin]$

In standby side,check RMAN connection.Connect primary database as TARGET and standby database as AUXILIARY(creating new instance)

Active Duplication for creating standby database:

[oracle@standby ~]$ export ORACLE_SID=dbwr
[oracle@standby ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 16 00:34:57 2019
Version 19.2.0.0.0

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2415917872 bytes
Fixed Size 8899376 bytes
Variable Size 654311424 bytes
Database Buffers 1744830464 bytes
Redo Buffers 7876608 bytes
SQL> !
[oracle@standby ~]$ rman target sys/oracle@dbwr auxiliary sys/oracle@standby

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 16 00:35:46 2019
Version 19.2.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: DBWR (DBID=1335353556)
connected to auxiliary database: DBWR (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 16-MAR-19
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=39 device type=DISK

contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwdbwr' ;
}
executing Memory Script

Starting backup at 16-MAR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=121 device type=DISK
Finished backup at 16-MAR-19

contents of Memory Script:
{
restore clone from service 'dbwr' standby controlfile;
}
executing Memory Script

Starting restore at 16-MAR-19
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service dbwr
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/admin/standby/control1.ctl
output file name=/u01/app/oracle/admin/standby/control2.ctl
Finished restore at 16-MAR-19

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/admin/standby/o1_mf_temp_g78w14cc_.tmp";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/admin/standby/o1_mf_system_g78vt7wo_.dbf";
set newname for datafile 3 to
"/u01/app/oracle/admin/standby/o1_mf_sysaux_g78vx8f6_.dbf";
set newname for datafile 4 to
"/u01/app/oracle/admin/standby/o1_mf_undotbs1_g78vyor1_.dbf";
set newname for datafile 7 to
"/u01/app/oracle/admin/standby/o1_mf_users_g78vyq0c_.dbf";
restore
from non sparse from service
'dbwr' clone database
;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/admin/standby/o1_mf_temp_g78w14cc_.tmp in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 16-MAR-19
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service dbwr
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/admin/standby/o1_mf_system_g78vt7wo_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:59
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service dbwr
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/admin/standby/o1_mf_sysaux_g78vx8f6_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:09
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service dbwr
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/admin/standby/o1_mf_undotbs1_g78vyor1_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service dbwr
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/admin/standby/o1_mf_users_g78vyq0c_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 16-MAR-19

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1003020439 file name=/u01/app/oracle/admin/standby/o1_mf_system_g78vt7wo_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1003020439 file name=/u01/app/oracle/admin/standby/o1_mf_sysaux_g78vx8f6_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1003020439 file name=/u01/app/oracle/admin/standby/o1_mf_undotbs1_g78vyor1_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1003020439 file name=/u01/app/oracle/admin/standby/o1_mf_users_g78vyq0c_.dbf
............
............
............
Finished Duplicate Db at 16-MAR-19

RMAN> exit


Enable the recovery

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

Post check status for Standby database

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

NAME               DATABASE_ROLE                 OPEN_MODE
---------          ----------------          ----------------
DBWR               PHYSICAL STANDBY              MOUNTED

Check the Standby database sync status with primary:

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE#         APPLIED
----------        ---------
    9               YES
Catch Me On:- Hariprasath Rajaram

LinkedIn:   https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:   https://www.facebook.com/HariPrasathdba
FB Group:   https://www.facebook.com/groups/894402327369506/
FB Page:    https://www.facebook.com/dbahariprasath/?
Twitter:    https://twitter.com/hariprasathdba

Oracle 19c-Step by Step Manual Data Guard Switchover

Switchover Operation Procedure: 

After configuring data guard, databases would be either primary and standby database role, and we can altered these roles without loss of data or without resetting logs  called Switchover.

A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements.

Data guard Configuration Details:-

Step 1:- Check database role and database name

Primary database:-

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

NAME         OPEN_MODE           DATABASE_ROLE
--------- -------------------- ----------------
DBWR         READ WRITE           PRIMARY

Standby database:-

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

NAME        OPEN_MODE           DATABASE_ROLE
--------- -------------------- ----------------
DBWR        MOUNTED             PHYSICAL STANDBY

Precheck for Switchover:- (PRIMARY SIDE)

Before performing switchover, kindly verify the state of data guard on both the instances by following SQL queries:

Step 2:-

SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

Session altered.
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

SEQUENCE#      FIRST_TIME         NEXT_TIME           APPLIED
---------- -------------------- -------------------- ---------
3          13-MAR-2019 18:59:20 13-MAR-2019 23:55:09   NO
4          13-MAR-2019 23:55:09 14-MAR-2019 20:04:18   NO
5          14-MAR-2019 20:04:18 14-MAR-2019 20:43:17   NO
6          14-MAR-2019 20:43:17 15-MAR-2019 01:35:09   NO
7          15-MAR-2019 01:35:09 15-MAR-2019 22:57:54   NO
8          15-MAR-2019 22:57:54 16-MAR-2019 00:11:18   NO
8          15-MAR-2019 22:57:54 16-MAR-2019 00:11:18   YES
9          16-MAR-2019 00:11:18 16-MAR-2019 00:47:08   NO
9          16-MAR-2019 00:11:18 16-MAR-2019 00:47:08   YES
10         16-MAR-2019 00:47:08 16-MAR-2019 01:54:56   NO
10         16-MAR-2019 00:47:08 16-MAR-2019 01:54:56   YES

11 rows selected.

Step 3:- 

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

Note: This command will give you appropriate message about the data guard current status.

After getting confirmation on data guard smooth activity, we can instantiate switchover activity by issuing following sort of commands:

The switchover_status column of v$database can have the following values:

Not Allowed:-Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases
Session Active:- Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted
Switchover Pending:- This is a standby database and the primary database switchover request has been received but not processed.
Switchover Latent:- The switchover was in pending mode, but did not complete and went back to the primary database
To Primary:- This is a standby database, with no active sessions, that is allowed to switch over to a primary database
To Standby:- This is a primary database, with no active sessions, that is allowed to switch over to a standby database
Recovery Needed:- This is a standby database that has not received the switchover request

On Primary database:-

Step 4:

SQL> alter database commit to switchover to standby;

Database altered.

Step 5:-

shutdown immediate

startup nomount

alter database mount standby database

[oracle@ram dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 16 02:20:17 2019
Version 19.2.0.0.0

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2415917872 bytes
Fixed Size 8899376 bytes
Variable Size 654311424 bytes
Database Buffers 1744830464 bytes
Redo Buffers 7876608 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

Step 6:-

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

NAME         OPEN_MODE           DATABASE_ROLE
--------- -------------------- ----------------
DBWR         READ WRITE          PRIMARY

On Standby database:

Step 7:-alter database commit to switchover to primary;

SQL> alter database commit to switchover to primary;

Database altered.

Step 8:-

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

NAME          OPEN_MODE          DATABASE_ROLE
--------- -------------------- ----------------
DBWR          MOUNTED            PRIMARY

Switch over activity have completed successfully, our old primary database has become standby and old standby database has become primary database.

Note:-To test above switchover activity, generate multiple archive logs on primary database and verify those archive logs being transferred on standby database.

After switchover activity,

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Oracle 19c-Step by Step Manual Data Guard Failover

Failover:-

In case of worst situation with data guard primary database, or not available for production than we can activated standby database as a primary production database.

Data guard Configuration details:-

Login in to Standby Server:-

Step:1 Check the database role and open_mode
Select name,open_mode,database_role from v$database;

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

Step:2 Cancel the MRP process

[oracle@standby dbs]$ ps -ef | grep mrp
oracle 79976 1 0 03:19 ? 00:00:02 ora_mrp0_dbwr
oracle 81638 81585 0 03:43 pts/2 00:00:00 grep --color=auto mrp
[oracle@standby dbs]$ !sq
sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 16 03:43:18 2019
Version 19.2.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0

SQL> recover managed standby database cancel;
Media recovery complete.

Step 3:

The below commands will help to bring up standby as primary

alter database recover managed standby database finish;

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database recover managed standby database finish;

Database altered.

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

NAME        OPEN_MODE           DATABASE_ROLE
--------- -------------------- ----------------
DBWR        MOUNTED             PHYSICAL STANDBY

SQL> alter database activate standby database;

Database altered.

Managed recovery process has been stopped between primary and standby database and standby becomes primary database.

Step 5:

 Bounce your database and verify database name its open mode and its role.

shutdown immediate;

Startup

Step 6:

select name,open_mode,database_role from v$database;

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

Now your old standby database is become primary database

It is highly recommended to consider immediate full backup of primary database.

The original primary database can now be configured as a standby.

If Flashback Database was enabled on the primary database.If not, the whole setup process must be followed, but this time using the original primary server as the standby.

After failover activity,

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba

Oracle 19c-Step by Step Configure Snapshot Standby Database

Description:-

  • Oracle Database 11g introduced the Snapshot Standby feature in Data Guard
  • A snapshot standby database is a fully updateable standby database created by converting a physical standby database into a snapshot standby database.
  • A snapshot standby database receives and archives, but does not apply,
    redo data from a primary database.
  • The redo data received from the primary database is applied once a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

Dataguard Configuration:-

Step:1 Check both primary and standby role.

Primary:-

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME     DATABASE_ROLE     OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN            class             PRIMARY         READ WRITE

Standby:-

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS         INSTANCE_NAME    DATABASE_ROLE        OPEN_MODE
------------ ---------------- ----------------   --------------------
MOUNTED        class            PHYSICAL STANDBY     MOUNTED

Step 2:- Check the archived sequence in standby,

select thread#,max(sequence#) from v$archived_log group by thread#;

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD#     MAX(SEQUENCE#)
---------- --------------
1              37

Step 3:- Check the flashback status and db_recovery_file_set location,

select flashback_on from v$database;

show parameter db_recovery_file_dest

SQL> show parameter db_recovery_file_dest

NAME                          TYPE            VALUE
-------------------------- ----------- ------------------------------
db_recovery_file_dest       string      /u01/app/oracle/recovery_dest/
db_recovery_file_dest_size  big integer            2G


SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

Step:4 In standby side,stop the mrp process.

 alter database recover managed standby database cancel;

SQL> recover managed standby database cancel;
Media recovery complete.

Step:5 Bounce the database and keep in mount stage,

shut immediate

startup mount

SQL> shu immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 943716968 bytes
Fixed Size 8903272 bytes
Variable Size 583008256 bytes
Database Buffers 348127232 bytes
Redo Buffers 3678208 bytes
Database mounted.

Step:6 Convert to snapshot standby database

alter database convert to snapshot standby

SQL> alter database convert to snapshot standby;

Database altered.

Step:7 Open the standby database in READ/WRITE mode

alter database open

SQL> alter database open;

Database altered.

Step:8 Check the DATABASE_ROLE and OPEN_MODE,

select status,instance_name,database_role,open_mode from v$database,v$Instance

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS        INSTANCE_NAME     DATABASE_ROLE       OPEN_MODE
------------ ----------------  ----------------  --------------------
OPEN            class          SNAPSHOT STANDBY    READ WRITE

Step:9 Testing on the snapshot standby database,

create user test identified by test;
grant connect,resource to test;
conn test/test
create table t1(sno number, name varchar2(20));
insert into t1 values (1,’example’);
commit;

SQL> alter user test quota unlimited on USERS;

User altered.

SQL> conn test/test;
Connected.

SQL> create table t1(sno number, name varchar2(20));

Table created.

SQL> insert into t1 values (1,'example');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

SNO           NAME
---------- --------------------
1             example

SQL>

Step:10 Both side verify the archived sequence

Primary side
select thread#,max(sequence#) from v$archived_log group by thread#;

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD#     MAX(SEQUENCE#)
----------  --------------
1              47

Standby side

select thread#,max(sequence#) from v$archived_log group by thread#;
select process,status,sequence# from v$managed_standby;

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD#     MAX(SEQUENCE#)
---------- --------------
1               47

Step 11: In Standby Side,bounce and mount the database

shut immediate
startup mount

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

Total System Global Area 943716968 bytes
Fixed Size 8903272 bytes
Variable Size 759169024 bytes
Database Buffers 171966464 bytes
Redo Buffers 3678208 bytes
Database mounted.

Step:12 Convert the snapshot standby database to physical standby database,

alter database convert to physical standby;

SQL> alter database convert to physical standby;

Database altered.

Step:13 Bounce the database,

shut immediate
startup

SQL> shu immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 943716968 bytes
Fixed Size 8903272 bytes
Variable Size 759169024 bytes
Database Buffers 171966464 bytes
Redo Buffers 3678208 bytes
Database mounted.
Database opened.

Step:14 Check the standby database_role and mode.

select status,instance_name,database_role,open_mode from v$database,v$Instance;

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS        INSTANCE_NAME    DATABASE_ROLE      OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN          class           PHYSICAL STANDBY    READ ONLY

Step:15 Enable the MRP process
alter database recover managed standby database disconnect from session;

SQL> alter database recover managed standby database disconnect from session;

Database altered.

Step:16 After convert to physical standby database,check the table status

Note:  After convert back to Physical standby database  once again restart the MRP process.

SQL> select * from test.t1;
select * from test.t1
*
ERROR at line 1:
ORA-00942: table or view does not exist

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba

Oracle 19c Database Upgrade From 11.2.0.4 to 19.2.0.0 Using Manual Method

Description:- 

A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to the new Oracle Database release.

Oracle Database Releases That Support Direct Upgrade
Review the supported options for direct upgrades to the latest Oracle Database release.
You can perform a direct upgrade to the new release from the following releases:
• 11.2.0.4
• 12.1.0.1 and 12.1.0.2
• 12.2.0.1

Presteps for db upgrade :-

Take RMAN full backups before upgrade.

rman target / 

run {
allocate channel test_backup_disk1 type disk format '/u01/backup/%d_%U';
backup incremental level 0 tag 'ORCL_BEFORE_UPG'  database;
backup format  tag 'ORCL_CONTROL_FILE' current controlfile;
release channel test_backup_disk1;
}

Ensure backup is complete before upgrade.

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

no rows selected

Empty Recycle bin.

SQL> PURGE DBA_RECYCLEBIN ;

DBA Recyclebin purged.

Run Gather statistics to finish upgrade soon.

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

Run the preupgrade tool:

The preupgrade.jar Pre-Upgrade Information Tool is supplied with Oracle Database 18c.This tool has reside in new oracle home.

/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar

[oracle@19c ~]$ /u01/app/oracle/product/11.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE 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: 2019-03-21T21:12:31

Run 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: 2019-03-21 21:12:27

For Source Database: MIG19C
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 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.

Check Timezone version :

Check if target database’s time zone version is lower than the source database time zone version.

SQL> SELECT version FROM v$timezone_file;

VERSION
-------
14

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2 3 4

PROPERTY_NAME            VALUE
------------------------ -----
DST_PRIMARY_TT_VERSION   14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE        NONE

Upgrade Database from 11.2.0.4 to 19c :-

Shutdown the 11g database:

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Copy the parameter and password file from the 11g home to the new 19c home.

[oracle@19c ~]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileMIG19C.ora /u01/app/oracle/product/19.0.0/dbhome_1
[oracle@19c ~]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwMIG19C /u01/app/oracle/product/19.0.0/dbhome_1

Set new ORACLE_HOME for 19c location and startup database in upgrade mode :

[oracle@19c ~]$ export ORACLE_SID=MIG19C
[oracle@19c ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@19c ~]$ export PATH=/u01/app/oracle/product/19.0.0/dbhome_1:$PATH
[oracle@19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 21 21:33:54 2019

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

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1543500136 bytes
Fixed Size 8896872 bytes
Variable Size 939524096 bytes
Database Buffers 587202560 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.

Run the dbupgrade utility :

[oracle@19c bin]$ export ORACLE_SID=MIG19C

[oracle@19c bin]$ ./dbupgrade

Argument list for [/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0

catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.2.0.0.0_LINUX.X64_190204]

/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0.0/dbhome_1]
/u01/app/oracle/product/19.0.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/19.0.0/dbhome_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0.0/dbhome_1]

Analyzing file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/cfgtoollogs/upgrade20190321214707]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20190321214707/catupgrd_catcon_9390.lst]

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20190321214707/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20190321214707/catupgrd_*.lst] files for spool files, if any

Number of Cpus = 1
Database Name = MIG19C
DataBase Version = 11.2.0.4.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MIG19C/upgrade20190321214709/catupgrd_catcon_9390.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MIG19C/upgrade20190321214709/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MIG19C/upgrade20190321214709/catupgrd_*.lst] files for spool files, if any

Log file directory = [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MIG19C/upgrade20190321214709]

Components in [MIG19C]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV MGW ODM OLS RAC WK]
Parallel SQL Process Count = 4

------------------------------------------------------
Phases [0-107] Start Time:[2019_03_21 21:47:10]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [MIG19C] Files:1 [oracle@19c bin]$ export ORACLE_SID=MIG19C
[oracle@19c bin]$ ./dbupgrade

Argument list for [/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0

catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.2.0.0.0_LINUX.X64_190204]

/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0.0/dbhome_1]
/u01/app/oracle/product/19.0.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/19.0.0/dbhome_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0.0/dbhome_1]

Analyzing file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/cfgtoollogs/upgrade20190321214707]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20190321214707/catupgrd_catcon_9390.lst]

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20190321214707/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20190321214707/catupgrd_*.lst] files for spool files, if any

Number of Cpus = 1
Database Name = MIG19C
DataBase Version = 11.2.0.4.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MIG19C/upgrade20190321214709/catupgrd_catcon_9390.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MIG19C/upgrade20190321214709/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MIG19C/upgrade20190321214709/catupgrd_*.lst] files for spool files, if any

Log file directory = [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MIG19C/upgrade20190321214709]

Components in [MIG19C]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV MGW ODM OLS RAC WK]
Parallel SQL Process Count = 4

------------------------------------------------------
Phases [0-107] Start Time:[2019_03_21 21:47:10]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [MIG19C] Files:1
Time: 68s
*************** Catalog Core SQL ***************
Serial Phase #:1 [MIG19C] Files:5 Time: 48s
Restart Phase #:2 [MIG19C] Files:1 Time: 1s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [MIG19C] Files:19 Time: 25s
Restart Phase #:4 [MIG19C] Files:1 Time: 1s
************* Catalog Final Scripts ************
Serial Phase #:5 [MIG19C] Files:7 Time: 24s
***************** Catproc Start ****************
Serial Phase #:6 [MIG19C] Files:1 Time: 12s
***************** Catproc Types ****************
Serial Phase #:7 [MIG19C] Files:2 Time: 10s
Restart Phase #:8 [MIG19C] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [MIG19C] Files:67 Time: 42s
Restart Phase #:10 [MIG19C] Files:1 Time: 1s
************* Catproc Package Specs ************
Serial Phase #:11 [MIG19C] Files:1 Time: 63s
Restart Phase #:12 [MIG19C] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [MIG19C] Files:94 Time: 12s
Restart Phase #:14 [MIG19C] Files:1 Time: 1s
Parallel Phase #:15 [MIG19C] Files:120 Time: 21s
Restart Phase #:16 [MIG19C] Files:1 Time: 0s
Serial Phase #:17 [MIG19C] Files:22 Time: 3s
Restart Phase #:18 [MIG19C] Files:1 Time: 1s
***************** Catproc Views ****************
Parallel Phase #:19 [MIG19C] Files:32 Time: 28s
Restart Phase #:20 [MIG19C] Files:1 Time: 1s
Serial Phase #:21 [MIG19C] Files:3 Time: 11s
Restart Phase #:22 [MIG19C] Files:1 Time: 0s
Parallel Phase #:23 [MIG19C] Files:25 Time: 197s
Restart Phase #:24 [MIG19C] Files:1 Time: 1s
Parallel Phase #:25 [MIG19C] Files:12 Time: 106s
Restart Phase #:26 [MIG19C] Files:1 Time: 0s
Serial Phase #:27 [MIG19C] Files:1 Time: 0s
Serial Phase #:28 [MIG19C] Files:3 Time: 4s
Serial Phase #:29 [MIG19C] Files:1 Time: 0s
Restart Phase #:30 [MIG19C] Files:1 Time: 0s
*************** Catproc CDB Views **************
Serial Phase #:31 [MIG19C] Files:1 Time: 1s
Restart Phase #:32 [MIG19C] Files:1 Time: 1s
Serial Phase #:34 [MIG19C] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [MIG19C] Files:293 Time: 22s
Serial Phase #:36 [MIG19C] Files:1 Time: 0s
Restart Phase #:37 [MIG19C] Files:1 Time: 0s
Serial Phase #:38 [MIG19C] Files:6 Time: 5s
Restart Phase #:39 [MIG19C] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [MIG19C] Files:3 Time: 51s
Restart Phase #:41 [MIG19C] Files:1 Time: 1s
****************** Catproc SQL *****************
Parallel Phase #:42 [MIG19C] Files:13 Time: 117s
Restart Phase #:43 [MIG19C] Files:1 Time: 1s
Parallel Phase #:44 [MIG19C] Files:11 Time: 17s
Restart Phase #:45 [MIG19C] Files:1 Time: 0s
Parallel Phase #:46 [MIG19C] Files:3 Time: 2s
Restart Phase #:47 [MIG19C] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [MIG19C] Files:1 Time: 9s
Restart Phase #:49 [MIG19C] Files:1 Time: 1s
************** Final RDBMS scripts *************
Serial Phase #:50 [MIG19C] Files:1 Time: 20s
************ Upgrade Component Start ***********
Serial Phase #:51 [MIG19C] Files:1 Time: 1s
Restart Phase #:52 [MIG19C] Files:1 Time: 0s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [MIG19C] Files:2 Time: 587s
***************** Upgrading XDB ****************
Restart Phase #:54 [MIG19C] Files:1 Time: 1s
Serial Phase #:56 [MIG19C] Files:3 Time: 28s
Serial Phase #:57 [MIG19C] Files:3 Time: 4s
Parallel Phase #:58 [MIG19C] Files:10 Time: 3s
Parallel Phase #:59 [MIG19C] Files:25 Time: 4s
Serial Phase #:60 [MIG19C] Files:4 Time: 7s
Serial Phase #:61 [MIG19C] Files:1 Time: 0s
Serial Phase #:62 [MIG19C] Files:32 Time: 4s
Serial Phase #:63 [MIG19C] Files:1 Time: 0s
Parallel Phase #:64 [MIG19C] Files:6 Time: 6s
Serial Phase #:65 [MIG19C] Files:2 Time: 22s
Serial Phase #:66 [MIG19C] Files:3 Time: 80s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [MIG19C] Files:1 Time: 1s
Serial Phase #:69 [MIG19C] Files:1 Time: 1s
Parallel Phase #:70 [MIG19C] Files:2 Time: 56s
Restart Phase #:71 [MIG19C] Files:1 Time: 0s
Parallel Phase #:72 [MIG19C] Files:2 Time: 1s
Serial Phase #:73 [MIG19C] Files:2 Time: 1s
***************** Upgrading SDO ****************
Restart Phase #:74 [MIG19C] Files:1 Time: 0s
Serial Phase #:76 [MIG19C] Files:1 Time: 85s
Serial Phase #:77 [MIG19C] Files:2 Time: 2s
Restart Phase #:78 [MIG19C] Files:1 Time: 1s
Serial Phase #:79 [MIG19C] Files:1 Time: 19s
Restart Phase #:80 [MIG19C] Files:1 Time: 0s
Parallel Phase #:81 [MIG19C] Files:3 Time: 82s
Restart Phase #:82 [MIG19C] Files:1 Time: 1s
Serial Phase #:83 [MIG19C] Files:1 Time: 5s
Restart Phase #:84 [MIG19C] Files:1 Time: 0s
Serial Phase #:85 [MIG19C] Files:1 Time: 11s
Restart Phase #:86 [MIG19C] Files:1 Time: 0s
Parallel Phase #:87 [MIG19C] Files:4 Time: 142s
Restart Phase #:88 [MIG19C] Files:1 Time: 1s
Serial Phase #:89 [MIG19C] Files:1 Time: 1s
Restart Phase #:90 [MIG19C] Files:1 Time: 0s
Serial Phase #:91 [MIG19C] Files:2 Time: 9s
Restart Phase #:92 [MIG19C] Files:1 Time: 0s
Serial Phase #:93 [MIG19C] Files:1 Time: 1s
Restart Phase #:94 [MIG19C] Files:1 Time: 1s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [MIG19C] Files:1 Time: 31s
Restart Phase #:96 [MIG19C] Files:1 Time: 0s
*********** Final Component scripts ***********
Serial Phase #:97 [MIG19C] Files:1 Time: 2s
************* Final Upgrade scripts ************
Serial Phase #:98 [MIG19C] Files:1 Time: 24s
******************* Migration ******************
Serial Phase #:99 [MIG19C] Files:1 Time: 48s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [MIG19C] Files:1 Time: 1s
Serial Phase #:101 [MIG19C] Files:1 Time: 0s
Serial Phase #:102 [MIG19C] Files:1 Time: 44s
***************** Post Upgrade *****************
Serial Phase #:103 [MIG19C] Files:1 Time: 38s
**************** Summary report ****************
Serial Phase #:104 [MIG19C] Files:1 Time: 2s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [MIG19C] Files:1 Time: 0s
Serial Phase #:106 [MIG19C] Files:1 Time: 0s
Serial Phase #:107 [MIG19C] Files:1 Time: 32s

------------------------------------------------------
Phases [0-107] End Time:[2019_03_21 22:25:49]
------------------------------------------------------

Grand Total Time: 2320s

LOG FILES: (/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MIG19C/upgrade20190321214709/catupgrd*.log)

Upgrade Summary Report Located in:
/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MIG19C/upgrade20190321214709/upg_summary.log

Grand Total Upgrade Time: [0d:0h:38m:40s]

Startup database after upgrade completed.

SQL> startup
ORACLE instance started.

Total System Global Area 1543500136 bytes
Fixed Size                  8896872 bytes
Variable Size             939524096 bytes
Database Buffers          587202560 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.
SQL> @catuppst.sql

Session altered.


Session altered.


Session altered.


PL/SQL procedure successfully completed.


Session altered.


System altered.


PL/SQL procedure successfully completed.


Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP DBRESTART 2019-03-21 23:05:38
DBUA_TIMESTAMP DBRESTART FINISHED 2019-03-21 23:05:38
DBUA_TIMESTAMP DBRESTART NONE 2019-03-21 23:05:38

TIMESTAMP
--------------------------------------------------------------------------------
DBUA_TIMESTAMP CATUPPST STARTED 2019-03-21 23:05:38


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2019-03-21 23:05:38
DBUA_TIMESTAMP POSTUP_BGN FINISHED 2019-03-21 23:05:38
DBUA_TIMESTAMP POSTUP_BGN NONE 2019-03-21 23:05:38

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_BGN 2019-03-21 23:05:38
DBUA_TIMESTAMP CATREQ_BGN FINISHED 2019-03-21 23:05:38
DBUA_TIMESTAMP CATREQ_BGN NONE 2019-03-21 23:05:38

PL/SQL procedure successfully completed.

catrequtlmg: b_StatEvt = TRUE
catrequtlmg: b_SelProps = FALSE
catrequtlmg: b_UpgradeMode = FALSE
catrequtlmg: b_InUtlMig = FALSE

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_END 2019-03-21 23:05:39
DBUA_TIMESTAMP CATREQ_END FINISHED 2019-03-21 23:05:39
DBUA_TIMESTAMP CATREQ_END NONE 2019-03-21 23:05:39


catuppst: Dropping library DBMS_DDL_INTERNAL_LIB

PL/SQL procedure successfully completed.

catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG

PL/SQL procedure successfully completed.

catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG

PL/SQL procedure successfully completed.

catuppst: Dropping view DBA_PART_KEY_COLUMNS_V$_MIG

PL/SQL procedure successfully completed.

catuppst: Dropping view DBA_SUBPART_KEY_COLUMNS_V$_MIG

PL/SQL procedure successfully completed.

catuppst: Dropping table OBJ$MIG
catuppst: Dropping table USER$MIG
catuppst: Dropping table COL$MIG
catuppst: Dropping table CLU$MIG
catuppst: Dropping table CON$MIG
catuppst: Dropping table BOOTSTRAP$MIG
catuppst: Dropping table TAB$MIG
catuppst: Dropping table TS$MIG
catuppst: Dropping table IND$MIG
catuppst: Dropping table ICOL$MIG
catuppst: Dropping table LOB$MIG
catuppst: Dropping table COLTYPE$MIG
catuppst: Dropping table SUBCOLTYPE$MIG
catuppst: Dropping table NTAB$MIG
catuppst: Dropping table REFCON$MIG
catuppst: Dropping table OPQTYPE$MIG
catuppst: Dropping table ICOLDEP$MIG
catuppst: Dropping table VIEWTRCOL$MIG
catuppst: Dropping table ATTRCOL$MIG
catuppst: Dropping table TYPE_MISC$MIG
catuppst: Dropping table LIBRARY$MIG
catuppst: Dropping table ASSEMBLY$MIG
catuppst: Dropping table TSQ$MIG
catuppst: Dropping table FET$MIG

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2019-03-21 23:05:39
DBUA_TIMESTAMP POSTUP_END FINISHED 2019-03-21 23:05:39
DBUA_TIMESTAMP POSTUP_END NONE 2019-03-21 23:05:39

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATUPPST 2019-03-21 23:05:39
DBUA_TIMESTAMP CATUPPST FINISHED 2019-03-21 23:05:39
DBUA_TIMESTAMP CATUPPST NONE 2019-03-21 23:05:39

Session altered.

Compile Invalid objects :

SQL> @utlrp.sql

Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2019-03-21 23:07:30

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2019-03-21 23:18:13

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.
SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)
----------
0

Set COMPATIBLE parameter :

SQL> show parameter compatible;

NAME              TYPE    VALUE
----------------- -------- ---------
compatible        string  11.2.0.4.0
noncdb_compatible boolean FALSE


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

System altered.

Restart the database :

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

Total System Global Area 2516580184 bytes
Fixed Size 8660824 bytes
Variable Size 671088640 bytes
Database Buffers 1828716544 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.
SQL> show parameter compatible;

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

Compile Fixed objects stats :

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

Post-Upgrade Steps :-

Upgrade Time Zone version :

Startup database in Upgrade mode.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1543500136 bytes
Fixed Size 8896872 bytes
Variable Size 1040187392 bytes
Database Buffers 486539264 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.

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 9
l_tz_version=32
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

Shutdown and startup in normal mode :

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup
ORACLE instance started.

Total System Global Area 1543500136 bytes
Fixed Size 8896872 bytes
Variable Size 1040187392 bytes
Database Buffers 486539264 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.


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

Check Time zone version :

SQL> SELECT * FROM v$timezone_file;

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

SQL>COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20

SELECT property_name, property_value
FROM database_properties
WHERE property_name LIKE 'DST_%'
ORDER BY property_name;SQL> SQL> SQL> SQL> 2 3 4

PROPERTY_NAME              PROPERTY_VALUE
-------------------------- ---------------
DST_PRIMARY_TT_VERSION     32
DST_SECONDARY_TT_VERSION   0
DST_UPGRADE_STATE          NONE

Run postupgrade fixups :

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: 2019-03-21 21:12:30

For Source Database: MIG19C
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 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.

Check database name and version :

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

NAME      VERSION           OPEN_MODE
--------- ----------------- ----------
MIG19C    19.0.0.0.0        READ WRITE
Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Step By Step install Oracle 19cR2 Database on OEL 7.6 (64-Bit)

Basic Installation

  • Boot from the DVD. Use the up arrow to pick the “Install Oracle Linux 7.0” option and hit the return key.
  • Select the appropriate language and select the “Set keyboard to default layout for selected language” option, then click the “Continue” button.

Click the “I want to proceed.” button on the pre release software warning screen.

  • You are presented with the “Installation Summary” screen. You must complete any marked items before you can continue with the installation. Depending on your requirements, you may also want to alter the default settings by clicking on the relevant links.

 

  • Click the “Installation Destination” link.
  • If you are happy to use automatic partitioning of the whole disk, click the “Done” button to return to the previous screen.

 

 

  • If you want to modify the partitioning configuration, click the “Continue” button and work through the partitioning screens.
  • It would be advisable to click on the “Software Selection” link and pick the following options if you want a GUI console.
    • Base Environment > Server with GUI
    • Add-ons for Selected Environment > Compatibility Libraries
    • Add-ons for Selected Environment > Development Tools

    Once you have completed your selections, click the “Done” button.

  • Once you have completed your alterations to the default configuration, click the “Begin Installation” button.
  • Click the “Root Password” link.

Enter the root password and click the “Done” button.

  • Click the “User Creation” link.
  • Enter the user details and select the “Make this user administrator” option, then click the “Done” button.

Wait for the installation to complete. When prompted, click the “Reboot” button.

Click the “License Information” link. Check the “I accept the license agreement” checkbox and click the “Done” button. When you return to the previous screen, click the “Finish Configuration” button.

 

Decide if you want to use Kdump and click the “Forward” button.

Decide if you want to set up software updates and click the “Forward” button. If you chose not to register with ULN, you will need to confirm you decision also.

Click the “Forward” button.

 

  • Finish Updates Setup
  • On the sign-in screen, click on the user you want to log in as
  • Enter the password and click the “Sign In” button.
  • Accept the language by clicking the “Next” button.
  • Accept the input sources by clicking the “Next” button.
  • Click the “Start using Oracle Linux Server” button.Thank You
  • Install Vmware tools to do some internal works.

  • While installing Oracle 19c binaries on OEL 7.6. First, we need to download the software then unzip it on oracle home and start runInstaller from the software location.
  • Select a “Set Up Software Only” and click next.
  • Select on “Single instance database Installation” and click on next.
  • Select on “Enterprise Edition” and click on next .
  • Put the Oracle Base and click on next
  • Put the “Inventory Directory” and click on next
  • Select appropriate group and click on next

  • Provide the root password if you want to run the scripts automatically.
  • Check the prerequisite for installation
  • Review the summary page and click on install
  • After Installation progress completed.
  • To complete the installation on click on close.

Load bash profile then run dbca

  • Log in as the Oracle software owner.
  • Go to a command prompt.
  • Type dbca.
  • You see a splash screen and another screen with options

Select Create a Database option to create a new Db

  • Select the Create a Database option.

    You are presented with the option of using a default configuration or an advanced one.

  • If you Select the Advanced option.
  • Click Next.

You’ll see output. Optional database templates are shown:

  • General Purpose
  • Data Warehouse
  • Custom Database

 

As i selected the Typical configuration provide Location of Datafile and FRA then provide password

Set the Oracle Home and connect to Sqlplus

we are successfully completed binary installation and Database installation of Oracle 19c

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba                      FB Group:https://www.facebook.com/groups/894402327369506/             FB Page: https://www.facebook.com/dbahariprasath/?               Twitter: https://twitter.com/hariprasathdba

Oracle 19c Database Upgrade From 12.2.0.1 to 19.2.0.0 Using DBUA

Description:-

There are different ways of upgrading to the latest release of Oracle database and Oracle provides multiple methods to upgrade. Few are listed below:

  • Database Upgrade Assistant (DBUA)
  • Manual Upgrade
  • Transportable Tablespaces
  • Data Pump export/import
  • Oracle Streams
  • Oracle GoldenGate

Here I am going to choose DBUA to upgrade my database,

[oracle@standby dbs]$ export ORACLE_SID=upgr
[oracle@standby dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 21 20:02:00 2019

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2449473536 bytes
Fixed Size 8623640 bytes
Variable Size 654313960 bytes
Database Buffers 1778384896 bytes
Redo Buffers 8151040 bytes
Database mounted.
Database opened.

Ensure backup is complete before upgrade.

Empty Recycle bin.

Run Gather statistics to finish upgrade soon.

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

no rows selected


SQL> PURGE DBA_RECYCLEBIN ;

DBA_Recyclebin purged.


SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.


SQL> !
Stop the running 12c listener

lsnrctl stop LISTENER

Run preupgrade tool and fixups

Oracle strongly recommends that you run the Pre-Upgrade Information Tool before starting the upgrade with DBUA. Although DBUA runs the Pre-Upgrade Information Tool as part of the pre-requisite checks, it is good practice to run the tool ahead of time in order to analyze the database and take actions that can decrease downtime for upgrading.

Connect to 12.2.0.1 database environment as SYS,

 Pre-upgrade checks :
Run the preupgrade tool .
[oracle@standby dbs]$ java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TEXT TERMINAL
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2019-03-21T20:07:45

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
Database Name:      UPGR
Container Name:     upgr
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:  NOARCHIVELOG
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
================
None

RECOMMENDED ACTIONS
===================
1. (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
================
2. 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         490 MB     509 MB
SYSTEM         800 MB     914 MB
TEMP           131 MB     150 MB
UNDOTBS1       70 MB      439 MB

Minimum tablespace sizes for upgrade are estimates.

3. 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 UPGR
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following

SQL>@/u01/app/oracle/cfgtoollogs/upgr/preupgrade/preupgrade_fixups.sql

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

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

RECOMMENDED ACTIONS
===================
4. 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.

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

6. (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.

7. 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 UPGR
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following

SQL>@/u01/app/oracle/cfgtoollogs/upgr/preupgrade/postupgrade_fixups.sql


==================
PREUPGRADE SUMMARY
==================
/u01/app/oracle/cfgtoollogs/upgr/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/upgr/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/upgr/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups
@/u01/app/oracle/cfgtoollogs/upgr/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/u01/app/oracle/cfgtoollogs/upgr/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2019-03-21T20:07:46

Run the preupgrade_fixup.sql

[oracle@standby dbs]$ !sq
sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 21 20:13:03 2019

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


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

SQL> @/u01/app/oracle/cfgtoollogs/upgr/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: 2019-03-21 20:07:39

For Source Database: UPGR
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.      pre_fixed_objects        YES            None.
2.      tablespaces_info         NO             Informational only.
                                            Further action is optional.
3.      rman_recovery_version    NO             Informational only.
                                            Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.
UPGRADE:

At this stage, we are still connected to 12c database which is up and running.

Now run the dbua utility from 12c oracle_home location.

[oracle@standby ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@standby ~]$ export ORACLE_SID=upgr
[oracle@standby ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@standby ~]$ dbua

DBUA performs some of the checks before actually starting the database upgrade. Some of the checks can be done manually to reduce downtime for the upgrade.

DBUA provides below options:

– Upgrade timezone.

– Gather dictionary statistics before upgrade.

– Make user tablespaces read only.

– Take RMAN backup before upgrade.

– Create Restore Point for Database Flashback

– Restore database backup to rollback upgrade

– Option to execute Custom scripts before and after upgrade

– show the location of DBUA logs and Alert log files.

– Option to upgrade existing listener to 18.x home or create a new listener in 18.x target home.

select the Upgrade Oracle Database option, then click the “Next” button.

If the prerequisite checks highlight any issues, take the appropriate action to fix the issues. If all the prerequisite checks are passed, click the “Next” button.

Amend the upgrade options if necessary, then click the “Next” button.

Select the recovery options for use in the event of an upgrade failure, then click the “Next” button.

If the database is using the 12c listener, accept the defaults, so the listener will be upgraded. If you prefer to manually define a new listener, you can do that now too and click the “Next” button

Select the desired management options, then click the “Next” button

Post-upgrade checks:

Run “postupgrade_fixups.sql” to check post upgrade status.

SQL> @/u01/app/oracle/cfgtoollogs/upgr/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: 2019-03-21 20:07:45

For Source Database: UPGR
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
------ ------------------------ ---------- ----------------------------
4.     old_time_zones_exist     YES                None.
5.     dir_symlinks             YES                None.
6.     post_dictionary          YES                None.
7.     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.

Now the database upgrade is complete and the database is ready for normal use.

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

VERSION            NAME        OPEN_MODE
----------------- --------- --------------------
19.0.0.0.0         UPGR        READ WRITE

SQL> SELECT * FROM v$timezone_file;

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

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
   32
Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba                      FB Group:https://www.facebook.com/groups/894402327369506/             FB Page: https://www.facebook.com/dbahariprasath/?               Twitter: https://twitter.com/hariprasathdba