Testing flashback database through an application upgrade and a database upgrade

This tests upgrading V11 to V12.1 and using flashback to revert.

I used the VM image from this location - https://mikedietrichde.com/database-upgrade-hands-on-lab-oracle-18c-and-19c/hol-19c-main-index-page-oracle-database-19c-hands-on-lab/

Download the files, extract them, then use ‘Import appliance’ from the Virtualbox menu.

When the import dialogue opens, de-select the ‘USB Controller’ otherwise it will issue an error when you try to start the image.

It doesn’t contain V12.1 so I downloaded and installed that (had to remove the V19 home to have space, just ‘cd /u01/app/oracle/product’ and ‘rm -rf 19’).

Download V12.1 from inside the VM, or download it to your laptop and share the folder.

I used this to perform the upgrade - https://dbaclass.com/article/upgrade-database-from-11g-to-12c-manually/

And these instructions to flashback - https://mikedietrichde.com/2017/08/29/fallback-strategy-flashback-to-guaranteed-restore-points/

Scenario

An application upgrade followed by a database upgrade is being performed. In the event of the application upgrade being unsuccessful we want to be able to flashback to before the change – this is standard and straight forward.

We are then performing a database upgrade. In the event the database upgrade is unsuccessful we want to be able to flashback to after the application upgrade but before the database was upgraded – this should be possible, with the result that the application is upgraded but the database reverted to the previous version.

There may be a requirement for both to be backed out – so we need to test if we can flashback an upgraded database to the previous version, and then to the pre-application upgrade restore point, resulting in the state before any changes were made.

Note – it is recommended to take full backups of the database before and after the application and database upgrades just in case.

Application Upgrade

Set the environment, create a user, turn on flashback, create a table, insert data

[oracle@hol ~]$ ps -ef | grep smon

oracle 2256 1 0 Apr08 ? 00:00:00 ora_smon_UPGR

oracle 2418 1 0 Apr08 ? 00:00:00 ora_smon_FTEX

oracle 2930 1 0 Apr08 ? 00:00:00 ora_smon_DB12

oracle 3946 1 0 Apr08 ? 00:00:00 ora_smon_CDB2

oracle 5560 5484 0 00:04 pts/1 00:00:00 grep --color=auto smon

[oracle@hol ~]$ . oraenv

ORACLE_SID = [oracle] ? UPGR

ORACLE_BASE environment variable is not being set since this

information is not available for the current user ID oracle.

You can set ORACLE_BASE manually if it is required.

Resetting ORACLE_BASE to its previous value or ORACLE_HOME

The Oracle base has been set to /u01/app/oracle/product/11.2.0.4

[oracle@hol ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 9 00:04:42 2020

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> create user andy identified by andy;

User created.

SQL> grant dba to andy;

Grant succeeded.

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 60

Current log sequence 62

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 810053632 bytes

Fixed Size 2257600 bytes

Variable Size 251661632 bytes

Database Buffers 549453824 bytes

Redo Buffers 6680576 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> connect andy/andy

Connected.

SQL> create table flashback_test(col1 varchar2(10));

Table created.

SQL> insert into flashback_test values('XX');

1 row created.

SQL> commit;

Commit complete.

Create a GRP, insert new data into the table:

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@hol ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 9 00:11:01 2020

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> select * from andy.flashback_test;

COL1

----------

SQL> create restore point BEFORE_APP_UPGRADE guarantee flashback database;

Restore point created.

SQL> select * from v$restore_point;

SCN DATABASE_INCARNATION# GUA STORAGE_SIZE

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

TIME

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

RESTORE_POINT_TIME PRE

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

NAME

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

948226 1 YES 52428800

09-APR-20 12.11.08.000000000 AM

Insert new data into the table

SQL> insert into andy.flashback_test values('YY');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from andy.flashback_test;

COL1

----------

Create a new restore point to simulate a point after the app upgrade

SQL> create restore point AFTER_APP_UPGRADE guarantee flashback database;

Restore point created.

SQL> select name from v$restore_point;

NAME

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

BEFORE_APP_UPGRADE

AFTER_APP_UPGRADE

Stop and restart the database in mount mode, then flashback to the restore point

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 810053632 bytes

Fixed Size 2257600 bytes

Variable Size 251661632 bytes

Database Buffers 549453824 bytes

Redo Buffers 6680576 bytes

Database mounted.

SQL> flashback database to restore point BEFORE_APP_UPGRADE;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

Check the data in the table

SQL> select * from andy.flashback_test;

COL1

----------

Restore points are still there

SQL> select name from v$restore_point;

NAME

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

BEFORE_APP_UPGRADE

AFTER_APP_UPGRADE

You can also use the after upgrade restore point, even though you’ve flashed back to before it was created

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 810053632 bytes

Fixed Size 2257600 bytes

Variable Size 251661632 bytes

Database Buffers 549453824 bytes

Redo Buffers 6680576 bytes

Database mounted.

SQL> flashback database to restore point AFTER_APP_UPGRADE;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select * from andy.flashback_test;

COL1

----------

Flashback of the existing version successful, so would create the restore point before the application upgrade scripts are run.

Remember to drop the restore points when finished.

Perform the Database Upgrade

So the state is of a successful application upgrade, the database can now be upgraded.

Run the pre-upgrade steps

SQL> select comp_id,status from dba_registry;

COMP_ID STATUS

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

OLS VALID

XDB VALID

OWM VALID

CATALOG VALID

CATPROC VALID

SQL> $ORACLE_HOME/rdbms/admin/utlrp

Check the output

Run the pre upgrade tool

Copy the Pre-Upgrade Information Tool script preupgrd.sql and utluppkg.sql from the Oracle Database 12c Release 1 (12.1)

cp /u01/app/oracle/product/12.1.0.2/rdbms/admin/preupgrd.sql /u01/app/oracle/product/11.2.0.4/rdbms/admin/

cp /u01/app/oracle/product/12.1.0.2/rdbms/admin/utluppkg.sql /u01/app/oracle/product/11.2.0.4/rdbms/admin/

Run the first sql and check the output

SQL> @/u01/app/oracle/product/11.2.0.4/rdbms/admin/preupgrd.sql

Loading Pre-Upgrade Package...

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

Executing Pre-Upgrade Checks in UPGR...

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

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

====>> ERRORS FOUND for UPGR <<====

The following are *** ERROR LEVEL CONDITIONS *** that must be addressed

prior to attempting your upgrade.

Failure to do so will result in a failed upgrade.

1) Check Tag: OLS_SYS_MOVE

Check Summary: Check if SYSTEM.AUD$ needs to move to SYS.AUD$ before upgrade

Fixup Summary:

"Execute olspreupgrade.sql script prior to upgrade."

+++ Source Database Manual Action Required +++

2) Check Tag: PURGE_RECYCLEBIN

Check Summary: Check that recycle bin is empty prior to upgrade

Fixup Summary:

"The recycle bin will be purged."

You MUST resolve the above errors prior to upgrade

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

====>> PRE-UPGRADE RESULTS for UPGR <<====

ACTIONS REQUIRED:

1. Review results of the pre-upgrade checks:

/u01/app/oracle/product/11.2.0.4/cfgtoollogs/UPGR/preupgrade/preupgrade.log

2. Execute in the SOURCE environment BEFORE upgrade:

/u01/app/oracle/product/11.2.0.4/cfgtoollogs/UPGR/preupgrade/preupgrade_fixups.sql

3. Execute in the NEW environment AFTER upgrade:

/u01/app/oracle/product/11.2.0.4/cfgtoollogs/UPGR/preupgrade/postupgrade_fixups.sql

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

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

Pre-Upgrade Checks in UPGR Completed.

Perform the required actions

Review the log:

cat /u01/app/oracle/product/11.2.0.4/cfgtoollogs/UPGR/preupgrade/preupgrade.log

Run the pre-upgrade script, fix issues and re-run till clear

SQL> @/u01/app/oracle/product/11.2.0.4/cfgtoollogs/UPGR/preupgrade/preupgrade_fixups.sql

Pre-Upgrade Fixup Script Generated on 2020-04-09 01:41:49 Version: 12.1.0.2 Build: 006

Beginning Pre-Upgrade Fixups...

Executing in container UPGR

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

Check Tag: DEFAULT_PROCESS_COUNT

Check Summary: Verify min process count is not too low

Fix Summary: Review and increase if needed, your PROCESSES value.

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

Fixup Returned Information:

WARNING: --> Process Count may be too low

Database has a maximum process count of 79 which is lower than the

default value of 300 for this release.

You should update your processes value prior to the upgrade

to a value of at least 300.

For example:

ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE

or update your init.ora file.

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

Check Tag: OLS_SYS_MOVE

Check Summary: Check if SYSTEM.AUD$ needs to move to SYS.AUD$ before upgrade

Fix Summary: Execute olspreupgrade.sql script prior to upgrade.

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

Fixup Returned Information:

ERROR: --> SYSTEM.AUD$ (audit records) Move

Oracle requires that records in the audit table SYSTEM.AUD$ be moved

to SYS.AUD$ prior to upgrading..

The Database has 0 rows in SYSTEM.AUD$ which

will be moved during the upgrade.

The downtime during the upgrade will be affected if there are a

large number of rows to be moved.

The audit data can be moved manually prior to the upgrade by using

the script: rdbms/admin/olspreupgrade.sql which is part of the

Oracle Database 12c software.

Please refer to the Label Security Administrator guide or

the Database Upgrade guide.

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

Check Tag: PURGE_RECYCLEBIN

Check Summary: Check that recycle bin is empty prior to upgrade

Fix Summary: The recycle bin will be purged.

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

Fixup Succeeded

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

[Pre-Upgrade Recommendations]

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

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

********* Dictionary Statistics *********

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

Please gather dictionary statistics 24 hours prior to

upgrading the database.

To gather dictionary statistics execute the following command

while connected as SYSDBA:

EXECUTE dbms_stats.gather_dictionary_stats;

^^^ MANUAL ACTION SUGGESTED ^^^

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

*********** Hidden Parameters ***********

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

Please review and remove any unnecessary hidden/underscore parameters prior

to upgrading. It is strongly recommended that these be removed before upgrade

unless your application vendors and/or Oracle Support state differently.

Changes will need to be made in the init.ora or spfile.

******** Existing Hidden Parameters ********

_cursor_obsolete_threshold = 1024

^^^ MANUAL ACTION SUGGESTED ^^^

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

************* Fixup Summary ************

1 fixup routine was successful.

1 fixup routine returned INFORMATIONAL text that should be reviewed.

1 ERROR LEVEL check returned INFORMATION that must be acted on prior to upgrade.

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

====>> USER ACTION REQUIRED <<====

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

1) Check Tag: OLS_SYS_MOVE failed.

Check Summary: Check if SYSTEM.AUD$ needs to move to SYS.AUD$ before upgrade

Fixup Summary:

"Execute olspreupgrade.sql script prior to upgrade."

^^^ MANUAL ACTION REQUIRED ^^^

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

You MUST resolve the above error prior to upgrade

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

**************** Pre-Upgrade Fixup Script Complete *********************

PL/SQL procedure successfully completed.

Run any fixes

SQL> ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;

System altered.

[oracle@hol andy]$ cp /u01/app/oracle/product/12.1.0.2/rdbms/admin/olspreupgrade.sql /u01/app/oracle/product/11.2.0.4/rdbms/admin/

[oracle@hol andy]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 9 01:45:23 2020

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> @/u01/app/oracle/product/11.2.0.4/rdbms/admin/olspreupgrade.sql

Function created.

No errors.

Function created.

No errors.

Function created.

No errors.

2020-04-09 01:45:35 ******* BEGINNING OLS PRE UPGRADE SCRIPT ********

The amount of FREE space required = 131072 Bytes

Free space available on SYSTEM tablespace= 34042003456 Bytes

2020-04-09 01:45:35 ******** PROCEEDING WITH OLS PRE UPGRADE *******

Audit records successfully moved to SYS.PREUPG_AUD$

PL/SQL procedure successfully completed.

No errors.

Total number of rows in SYS.PREUPG_AUD$: 0

2020-04-09 01:45:36 ******* FINISHING OLS PRE UPGRADE SCRIPT ********

PL/SQL procedure successfully completed.

No errors.

Rerun the script

SQL> @/u01/app/oracle/product/11.2.0.4/rdbms/admin/preupgrd.sql

Loading Pre-Upgrade Package...

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

Executing Pre-Upgrade Checks in UPGR...

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

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

====>> ERRORS FOUND for UPGR <<====

The following are *** ERROR LEVEL CONDITIONS *** that must be addressed

prior to attempting your upgrade.

Failure to do so will result in a failed upgrade.

You MUST resolve the above errors prior to upgrade

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

====>> PRE-UPGRADE RESULTS for UPGR <<====

ACTIONS REQUIRED:

1. Review results of the pre-upgrade checks:

/u01/app/oracle/product/11.2.0.4/cfgtoollogs/UPGR/preupgrade/preupgrade.log

2. Execute in the SOURCE environment BEFORE upgrade:

/u01/app/oracle/product/11.2.0.4/cfgtoollogs/UPGR/preupgrade/preupgrade_fixups.sql

3. Execute in the NEW environment AFTER upgrade:

/u01/app/oracle/product/11.2.0.4/cfgtoollogs/UPGR/preupgrade/postupgrade_fixups.sql

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

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

Pre-Upgrade Checks in UPGR Completed.

Check the log – OK to proceed

************ Summary ************

0 ERRORS exist in your database.

1 WARNING that Oracle suggests are addressed to improve database performance.

2 INFORMATIONAL messages that should be reviewed prior to your upgrade.

Note - Take ddl backup of db_links:

During the upgrade to Oracle Database 12c any passwords in database links are encrypted.
To downgrade to the original release, all of the database links with encrypted passwords must be dropped prior to the downgrade.

For 12c, the new timezone is 18. So after the db upgrade is completed, we will upgrade the dst timezone from 14 to 18.

SELECT version FROM v$timezone_file;

VERSION

----------

Gather stats

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

Check Materialized Views

SQL> select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ s where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8;

no rows selected

Ensure That No Files Need Media Recovery

SQL> SELECT * FROM v$recover_file;

no rows selected

Ensure That No Files Are in Backup Mode

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

no rows selected

Resolve Outstanding Distributed Transactions

SQL> SELECT * FROM dba_2pc_pending;

no rows selected

Purge the Database Recycle Bin

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

Stop / comment out cron jobs

Disable scheduler jobs

SQL> select owner,JOB_NAME,ENABLED,state from dba_scheduler_jobs;

OWNER JOB_NAME ENABL STATE

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

SYS XMLDB_NFS_CLEANUP_JOB FALSE DISABLED

SYS SM$CLEAN_AUTO_SPLIT_MERGE TRUE SCHEDULED

SYS RSE$CLEAN_RECOVERABLE_SCRIPT TRUE SCHEDULED

SYS FGR$AUTOPURGE_JOB FALSE DISABLED

SYS BSLN_MAINTAIN_STATS_JOB TRUE SCHEDULED

SYS DRA_REEVALUATE_OPEN_FAILURES TRUE SCHEDULED

SYS HM_CREATE_OFFLINE_DICTIONARY FALSE DISABLED

SYS ORA$AUTOTASK_CLEAN TRUE SCHEDULED

SYS FILE_WATCHER FALSE DISABLED

SYS PURGE_LOG TRUE SCHEDULED

ORACLE_OCM MGMT_STATS_CONFIG_JOB TRUE SCHEDULED

ORACLE_OCM MGMT_CONFIG_JOB TRUE SCHEDULED

execute dbms_scheduler.disable('SM$CLEAN_AUTO_SPLIT_MERGE');

execute dbms_scheduler.disable('RSE$CLEAN_RECOVERABLE_SCRIPT');

etc

SQL> select owner,JOB_NAME,ENABLED,state from dba_scheduler_jobs;

OWNER JOB_NAME ENABL STATE

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

SYS XMLDB_NFS_CLEANUP_JOB FALSE DISABLED

SYS SM$CLEAN_AUTO_SPLIT_MERGE FALSE DISABLED

SYS RSE$CLEAN_RECOVERABLE_SCRIPT FALSE DISABLED

SYS FGR$AUTOPURGE_JOB FALSE DISABLED

SYS BSLN_MAINTAIN_STATS_JOB FALSE DISABLED

SYS DRA_REEVALUATE_OPEN_FAILURES FALSE DISABLED

SYS HM_CREATE_OFFLINE_DICTIONARY FALSE DISABLED

SYS ORA$AUTOTASK_CLEAN FALSE DISABLED

SYS FILE_WATCHER FALSE DISABLED

SYS PURGE_LOG FALSE DISABLED

ORACLE_OCM MGMT_STATS_CONFIG_JOB FALSE DISABLED

ORACLE_OCM MGMT_CONFIG_JOB FALSE DISABLED

Verify system and sys default tablespace.(Both should be system tablespace)

SQL> SELECT username, default_tablespace

FROM dba_users WHERE username in ('SYS','SYSTEM');

USERNAME DEFAULT_TABLESPACE

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

SYSTEM SYSTEM

SYS SYSTEM

Check whether database has any externally authenticated SSL users

SQL> SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL

AND password = 'GLOBAL';

no rows selected

Review and Remove any unnecessary hidden/underscore parameters

SQL> SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;

NAME VALUE

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

_cursor_obsolete_threshold 1024

Create a new restore point

SQL> create restore point BEFORE_DB_UPGRADE guarantee flashback database;

Restore point created.

SQL> select name from v$restore_point;

NAME

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

BEFORE_APP_UPGRADE

AFTER_APP_UPGRADE

BEFORE_DB_UPGRADE

Stop the listener

[oracle@hol andy]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 09-APR-2020 02:31:20

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

The command completed successfully

[oracle@hol andy]$ lsnrctl stat

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 09-APR-2020 02:31:24

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

TNS-12541: TNS:no listener

TNS-12560: TNS:protocol adapter error

TNS-00511: No listener

Linux Error: 111: Connection refused

Stop the database

[oracle@hol andy]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 9 02:37:58 2020

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> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Set the new ORACLE_HOME and copy the spfile and password file from the V11 home

export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2

export PATH=$ORACLE_HOME/bin:$PATH

cp -p /u01/app/oracle/product/11.2.0.4/dbs/spfileUPGR.ora /u01/app/oracle/product/12.1.0.2/dbs

cp -p /u01/app/oracle/product/11.2.0.4/dbs/orapwUPGR /u01/app/oracle/product/12.1.0.2/dbs

Start the database from the new ORACLE_HOME in upgrade mode

echo $ORACLE_HOME

/u01/app/oracle/product/12.1.0.2

cd $ORACLE_HOME/rdbms/admin

pwd

/u01/app/oracle/product/12.1.0.2/rdbms/admin

sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 9 02:40:06 2020

Connected to an idle instance.

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 813694976 bytes

Fixed Size 2929648 bytes

Variable Size 318770192 bytes

Database Buffers 486539264 bytes

Redo Buffers 5455872 bytes

Database mounted.

Database opened.

SQL> exit

Run the upgrade from the command line – this uses parallel 6

$ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l $ORACLE_HOME/diagnostics catupgrd.sql

Argument list for [catctl.pl]

SQL Process Count n = 6

SQL PDB Process Count N = 0

Input Directory d = 0

Phase Logging Table t = 0

Log Dir l = /u01/app/oracle/product/12.1.0.2/diagnostics

Script s = 0

Serial Run S = 0

Upgrade Mode active M = 0

Start Phase p = 0

End Phase P = 0

Log Id i = 0

Run in c = 0

Do not run in C = 0

Echo OFF e = 1

No Post Upgrade x = 0

Reverse Order r = 0

Open Mode Normal o = 0

Debug catcon.pm z = 0

Debug catctl.pl Z = 0

Display Phases y = 0

Child Process I = 0

catctl.pl version: 12.1.0.2.0

Oracle Base = /u01/app/oracle

Analyzing file catupgrd.sql

Log files in /u01/app/oracle/product/12.1.0.2/diagnostics

catcon: ALL catcon-related output will be written to /u01/app/oracle/product/12.1.0.2/diagnostics/catupgrd_catcon_23741.lst

catcon: See /u01/app/oracle/product/12.1.0.2/diagnostics/catupgrd*.log files for output generated by scripts

catcon: See /u01/app/oracle/product/12.1.0.2/diagnostics/catupgrd_*.lst files for spool files, if any

Number of Cpus = 2

SQL Process Count = 6

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

Phases [0-73]

Serial Phase #: 0 Files: 1 Time: 49s

Serial Phase #: 1 Files: 5 Time: 28s

Restart Phase #: 2 Files: 1 Time: 0s

Parallel Phase #: 3 Files: 18 Time: 8s

Restart Phase #: 4 Files: 1 Time: 0s

Serial Phase #: 5 Files: 5 Time: 12s

Serial Phase #: 6 Files: 1 Time: 8s

Serial Phase #: 7 Files: 4 Time: 4s

Restart Phase #: 8 Files: 1 Time: 1s

Parallel Phase #: 9 Files: 62 Time: 26s

Restart Phase #:10 Files: 1 Time: 0s

Serial Phase #:11 Files: 1 Time: 10s

Restart Phase #:12 Files: 1 Time: 0s

Parallel Phase #:13 Files: 91 Time: 7s

Restart Phase #:14 Files: 1 Time: 1s

Parallel Phase #:15 Files: 111 Time: 14s

Restart Phase #:16 Files: 1 Time: 0s

Serial Phase #:17 Files: 3 Time: 1s

Restart Phase #:18 Files: 1 Time: 0s

Parallel Phase #:19 Files: 32

You can check the log in another session window

cd /u01/app/oracle/product/12.1.0.2/diagnostics

tail -f catupgrd0.log

When complete, check the summary log

Serial Phase #:70 Files: 1 Time: 55s

Serial Phase #:71 Files: 1 Time: 0s

Serial Phase #:72 Files: 1 Time: 0s

Serial Phase #:73 Files: 1 Time: 21s

Grand Total Time: 630s

LOG FILES: (catupgrd*.log)

Upgrade Summary Report Located in:

/u01/app/oracle/product/12.1.0.2/cfgtoollogs/UPGR/upgrade/upg_summary.log

Grand Total Upgrade Time: [0d:0h:10m:30s]

cat /u01/app/oracle/product/12.1.0.2/cfgtoollogs/UPGR/upgrade/upg_summary.log

Oracle Database 12.1 Post-Upgrade Status Tool 04-09-2020 02:52:41

Component Current Version Elapsed Time

Name Status Number HH:MM:SS

Oracle Server UPGRADED 12.1.0.2.0 00:06:38

Oracle Workspace Manager VALID 12.1.0.2.0 00:00:32

Oracle Label Security VALID 12.1.0.2.0 00:00:06

Oracle XML Database VALID 12.1.0.2.0 00:00:53

Final Actions 00:00:27

Post Upgrade 00:00:53

Total Upgrade Time: 00:09:32

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10

Grand Total Upgrade Time: [0d:0h:10m:30s]

Start the database and run the Post-Upgrade Status Tool

[oracle@hol admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 9 02:57:47 2020

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 813694976 bytes

Fixed Size 2929648 bytes

Variable Size 339741712 bytes

Database Buffers 465567744 bytes

Redo Buffers 5455872 bytes

Database mounted.

Database opened.

SQL> @utlu121s.sql

PL/SQL procedure successfully completed.

CATCTL REPORT = /u01/app/oracle/product/12.1.0.2/cfgtoollogs/UPGR/upgrade/upg_summary.log

PL/SQL procedure successfully completed.

Oracle Database 12.1 Post-Upgrade Status Tool 04-09-2020 02:58:33

Component Current Version Elapsed Time

Name Status Number HH:MM:SS

Oracle Server UPGRADED 12.1.0.2.0 00:06:38

Oracle Workspace Manager VALID 12.1.0.2.0 00:00:32

Oracle Label Security VALID 12.1.0.2.0 00:00:06

Oracle XML Database VALID 12.1.0.2.0 00:00:53

Final Actions 00:00:27

Post Upgrade 00:00:53

Total Upgrade Time: 00:09:32

PL/SQL procedure successfully completed.

SQL>

SQL> --

SQL> -- Update Summary Table with con_name and endtime.

SQL> --

SQL> UPDATE sys.registry$upg_summary SET reportname = :ReportName,

2 con_name = SYS_CONTEXT('USERENV','CON_NAME'),

3 endtime = SYSDATE

4 WHERE con_id = -1;

1 row updated.

SQL> commit;

Commit complete.

Check catuppst.sql ran

cd /u01/app/oracle/product/12.1.0.2/diagnostics

cat catupgrd0.log | grep catuppst.sql

02:51:46 SQL> Rem catuppst.sql

02:51:46 SQL> Rem catuppst.sql - CATalog UPgrade PoST-upgrade actions

02:51:46 SQL> Rem BEGIN catuppst.sql

If it didn’t, run it manually.

Post upgrade steps

DST files

Download the files - Scripts to update the RDBMS DST (timezone) version in an 11gR2 or 12c database . (Doc ID 1585343.1)

Check the current status

SQL> SELECT version FROM v$timezone_file;

VERSION

----------

To update an 11gR2 or 12cR1 database to the highest RDBMS DST version included by default in that 11gR2 or 12cR1 version (most likely after an Oracle RDBMS version upgrade to 11gR2 or 12cR1) there is no need to apply any "DST patch" , you can simply run the scripts in this note.
The highest RDBMS DST version included by default is for 11.2.0.1 DSTv11 , for 11.2.0.2, 11.2.0.3 and 11.2.0.4 DSTv14, for 12.1.0.1 and 12.1.0.2 DSTv18.

Script is here

https://support.oracle.com/epmos/main/downloadattachmentprocessor?parent=DOCUMENT&sourceId=1585343.1&attachid=1585343.1:DBMS_DST_SCRIPTSV1.9&clickstream=yes

Unzip the file, copy to the ORACLE_HOME and run the SQL

Note that upg_tzv_check.sql takes no arguments, it will detect the highest installed DST patch automatically and needs no downtime, this can be executed on a live production database but it WILL purge the dba_recyclebin.

[oracle@hol DBMS_DST_scriptsV1.9]$ cp * $ORACLE_HOME/

[oracle@hol DBMS_DST_scriptsV1.9]$ cd $ORACLE_HOME

[oracle@hol 12.1.0.2]$ sqlplus / as sysdba

SQL> spool upg_tzv_check.log

SQL> @upg_tzv_check.sql

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

INFO: Database RDBMS DST version is DSTv14 .

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

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 upg_tzv_apply.sql to do the actual RDBMS DST update.

INFO: Note that the upg_tzv_apply.sql script will

INFO: restart the database 2 times WITHOUT any confirmation or prompt.

Note the database will be restarted twice when running this:

SQL> @upg_tzv_apply.sql

INFO: If an ERROR occurs the script will EXIT sqlplus.

INFO: The database RDBMS DST version will be updated to DSTv18 .

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 813694976 bytes

Fixed Size 2929648 bytes

Variable Size 318770192 bytes

Database Buffers 486539264 bytes

Redo Buffers 5455872 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 813694976 bytes

Fixed Size 2929648 bytes

Variable Size 318770192 bytes

Database Buffers 486539264 bytes

Redo Buffers 5455872 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

INFO: Total failures during update of TSTZ data: 0 .

An upgrade window has been successfully ended.

INFO: Your new Server RDBMS DST version is DSTv18 .

INFO: The RDBMS DST update is successfully finished.

INFO: Make sure to exit this sqlplus session.

INFO: Do not use it for timezone related selects.

SQL> SELECT version FROM v$timezone_file;

VERSION

----------

Run the post-upgrade script

SQL> @/u01/app/oracle/product/11.2.0.4/cfgtoollogs/UPGR/preupgrade/postupgrade_fixups.sql

Post Upgrade Fixup Script Generated on 2020-04-09 01:55:26 Version: 12.1.0.2 Build: 006

Beginning Post-Upgrade Fixups...

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

Check Tag: OLD_TIME_ZONES_EXIST

Check Summary: Check for use of older timezone data file

Fix Summary: Update the timezone using the DBMS_DST package after upgrade is complete.

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

Fixup Returned Information:

INFORMATION: --> Older Timezone in use

Database is using a time zone file older than version 18.

After the upgrade, it is recommended that DBMS_DST package

be used to upgrade the 12.1.0.2.0 database time zone version

to the latest version which comes with the new release.

Please refer to My Oracle Support note number 977512.1 for details.

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

[Post-Upgrade Recommendations]

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

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

******** Fixed Object Statistics ********

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

Please create stats on fixed objects two weeks

after the upgrade using the command:

EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

^^^ MANUAL ACTION SUGGESTED ^^^

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

************* Fixup Summary ************

1 fixup routine generated an INFORMATIONAL message that should be reviewed.

*************** Post Upgrade Fixup Script Complete ********************

PL/SQL procedure successfully completed.

Fix any issues

Database is upgraded to V12.1

Check the data in the test table

SQL> select * from andy.flashback_test;

COL1

----------

So back as after the application upgrade. At this stage the application and database have been upgraded.

Check the restore points

SQL> select name from v$restore_point;

NAME

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

BEFORE_APP_UPGRADE

AFTER_APP_UPGRADE

BEFORE_DB_UPGRADE

Stop and restart the database.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Check the alert log for errors

Note the compatible parameter is still V11 – this cannot be changed if you still want to downgrade

SQL> show parameter compat

NAME TYPE VALUE

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

compatible string 11.2.0.4.0

Try using the restore point to downgrade

Take another restore point

SQL> create restore point AFTER_DB_UPGRADE guarantee flashback database;

Restore point created.

SQL> select name from v$restore_point;

NAME

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

BEFORE_APP_UPGRADE

AFTER_APP_UPGRADE

BEFORE_DB_UPGRADE

AFTER_DB_UPGRADE

Perform the flashback to before the db upgrade

While in the V12 environment

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 813694976 bytes

Fixed Size 2929648 bytes

Variable Size 318770192 bytes

Database Buffers 486539264 bytes

Redo Buffers 5455872 bytes

Database mounted.

SQL> flashback database to restore point BEFORE_DB_UPGRADE;

Flashback complete.

Shut the database and set the environment to V11, then restart

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@hol preupgrade]$ . oraenv

ORACLE_SID = [UPGR] ? UPGR

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@hol preupgrade]$ echo $ORACLE_HOME

/u01/app/oracle/product/11.2.0.4

[oracle@hol preupgrade]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 9 04:01:11 2020

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 810053632 bytes

Fixed Size 2257600 bytes

Variable Size 318770496 bytes

Database Buffers 482344960 bytes

Redo Buffers 6680576 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

Database has been downgraded to V11.

Check the restore points

SQL> select name from v$restore_point;

NAME

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

BEFORE_APP_UPGRADE

AFTER_APP_UPGRADE

BEFORE_DB_UPGRADE

AFTER_DB_UPGRADE

Check the data in the tables

SQL> select * from andy.flashback_test;

COL1

----------

So we are at the point of the application upgrade before the database was upgraded.

Try to flashback to before the application upgrade

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 810053632 bytes

Fixed Size 2257600 bytes

Variable Size 318770496 bytes

Database Buffers 482344960 bytes

Redo Buffers 6680576 bytes

Database mounted.

SQL> flashback database to restore point BEFORE_APP_UPGRADE;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select * from andy.flashback_test;

COL1

----------

So successfully flashed back the database upgrade and the application upgrade.

Perfect.

Drop the restore points.

If the database is to remain V12, re-enable the scheduler jobs and restart the listener.

Sunday, April 19, 2020

Testing Flashback through a database upgrade

Testing flashback database through an application upgrade and a database upgrade

Scenario

Application Upgrade

Set the environment, create a user, turn on flashback, create a table, insert data

Insert new data into the table

Create a new restore point to simulate a point after the app upgrade

Stop and restart the database in mount mode, then flashback to the restore point

Check the data in the table

You can also use the after upgrade restore point, even though you’ve flashed back to before it was created

Perform the Database Upgrade

Run the pre-upgrade script, fix issues and re-run till clear

Run any fixes

Gather stats

Check Materialized Views

Ensure That No Files Need Media Recovery

Ensure That No Files Are in Backup Mode

Resolve Outstanding Distributed Transactions

Purge the Database Recycle Bin

Stop / comment out cron jobs

Disable scheduler jobs

Verify system and sys default tablespace.(Both should be system tablespace)

Check whether database has any externally authenticated SSL users

Review and Remove any unnecessary hidden/underscore parameters

Create a new restore point

Stop the listener

Stop the database

Set the new ORACLE_HOME and copy the spfile and password file from the V11 home

Start the database from the new ORACLE_HOME in upgrade mode

Run the upgrade from the command line – this uses parallel 6

You can check the log in another session window

When complete, check the summary log

Start the database and run the Post-Upgrade Status Tool

Check catuppst.sql ran

If it didn’t, run it manually.

Post upgrade steps

Check the current status

Unzip the file, copy to the ORACLE_HOME and run the SQL

Note the database will be restarted twice when running this:

Run the post-upgrade script

Fix any issues

Database is upgraded to V12.1

Check the data in the test table

Check the restore points

Stop and restart the database.

Check the alert log for errors

Try using the restore point to downgrade

Take another restore point

Perform the flashback to before the db upgrade

Shut the database and set the environment to V11, then restart

Database has been downgraded to V11.

Check the restore points

Check the data in the tables

So we are at the point of the application upgrade before the database was upgraded.

Try to flashback to before the application upgrade

So successfully flashed back the database upgrade and the application upgrade.

Drop the restore points.