Tuesday, October 19, 2021

RMAN Restore and Recovery Scenarios




Full Database Restore


$ORACLE_HOME/bin/rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;


 

Tablespace Restore (online)

$rman target / nocatalog
RMAN> sql ‘alter tablespace users offline';
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> sql ‘alter tablespace users online';


 

Tablespace Restore (offline)

$rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> alter database open;
database opened


 

Restoring a Specific Datafile

$rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore datafile ‘/oradata/DB1/dbf/users01.dbf';
RMAN> recover datafile ‘/oradata/DB1/dbf/users01.dbf';
RMAN> alter database open;
database opened


 

Control File Restoration

Prerequisite: In your rman backup directory determine the latest control file backup.
Default Format: c-nnnnnnnnnn-nnnnnnnn-nn


$rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup nomount;
RMAN> set dbid = 1184749195
RMAN> restore controlfile from ‘/oradata/DB1/rman/c-1184749195-20060626-02′
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
database opened
Database Point-In-Time-Recovery (PITR)


Also known as time-based incomplete recovery.

$rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore database until time “to_date(’09/03/07 13:00:00′, ‘MM/DD/YY HH24:MI:SS’)”;
RMAN> recover database until time “to_date(’09/03/07 13:00:00′, ‘MM/DD/YY HH24:MI:SS’)”;
RMAN> alter database open resetlogs;
database opened


* Make sure you perform a full backup after this operation!

Restore to Another System

Prerequisites
Ideally ensure destination system configured exactly like source.
Same OS version and patch level.
Same drives (C:, D:, S: etc.).
CPU and RAM same or better.
The same version of Oracle is installed on the target system as the source.
Ensure the ORACLE_HOME and ORACLE_SID environment variables are set.
Ensure the listener is running.


Copy RMAN backupset files to the destination system rman directory.

Procedure

Restore SPFILE and Control File

$rman target / nocatalog
RMAN> set dbid 161080442
RMAN> startup nomount;
Creates the file: %ORACLE_HOME%\database\hc_db1.dat
RMAN> restore spfile from ‘R:\rman\C-161080442-20080313-00′;
Creates the file: %ORACLE_HOME%\database\SPFILEDB1.ORA
RMAN> startup force nomount
RMAN> restore controlfile from ‘R:\rman\C-161080442-20080313-00′;
RMAN> shutdown immediate
RMAN> exit



Restore and Recover the Data

$rman target / nocatalog
RMAN> startup mount;
RMAN> restore database;
For a large database this step may take some time.
RMAN> recover database;
If you do not have\need the very last log(s) you can disregard any error messages.
ORA-00310: archived log contains sequence 100; sequence 101 required…
RMAN> alter database open resetlogs;
database opened

Scenario Basis of Recovery

1-  If you lost all data files


SQL> startup mount;
RMAN> restore database;
RMAN> recover database;
SQL> alter database open;

2- If you lost a tablespace


SQL> alter tablespace users offline;
RMAN> restore tablespace users;
RMAN> recover tablespace users;
SQL> alter tablespace users online;

 if you can not offline tablespace;


$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / 
catalog_user/catalog_user_password@catalogdb
RMAN> restore tablespace users;
RMAN> recover tablespace users;
SQL> alter database open;


3- if you lost a datafile


SQL> alter database datafile '/oracle/oradata/users.dbf' offline;
RMAN> restore datafile '/oracle/oradata/users.dbf'
RMAN> recover datafile '/oracle/oradata/users.dbf'
SQL> alter database datafile '/oracle/oradata/users.dbf' online;
 
if you cannot offline datafile;

$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / 
catalog_user/catalog_user_password@catalogdb
RMAN> restore datafile '/oracle/oradata/users.dbf';
RMAN> recover datafile '/oracle/oradata/users.dbf';
SQL> alter database open;
 



4-  if you lost your controlfiles


$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup nomount;
$ rman target / 
catalog_user/catalog_user_password@catalogdb
RMAN> set dbid = 3970640872;
RMAN> restore controlfile;
SQL> alter database mount;
SQL> alter database open;

you will receive an error ORA-01589 when you open database
 ORA-01589: must use RESETLOGS or NORESETLOGS option for database open



SQL> shutdown abort;
SQL> startup mount;
$ rman target / 
catalog_user/catalog_user_password@catalogdb
RMAN> recover database;
SQL> alter database open resetlogs;
RMAN> reset database;

if you open database with resetlogs, SCN number will be zero. In this situation
 all previous backups will be invalid. You must full backup.


5- May be a special situation. You need to incomplete recovery


 A. Time-Based incomplete recovery


$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / 
catalog_user/catalog_user_password@catalogdb
RMAN> restore database until time "to_date('06/05/11 12:0:00','DD/MM/YY HH24:MI:SS')";
RMAN> recover database until time "to_date('06/05/11 12:0:00','DD/MM/YY HH24:MI:SS')";
SQL> alter database open resetlogs;
 
 


 B. SCN-Based incomplete recovery


$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / 
catalog_user/catalog_user_password@catalogdb
RMAN> restore database until scn 1000;
RMAN> recover database until scn 1000;
SQL> alter database open resetlogs;


 C. Archive log sequence based incomplete recovery


$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / 
catalog_user/catalog_user_password@catalogdb
RMAN> restore database until sequence 9923;
RMAN> recover database until sequence 9923;
SQL> alter database open resetlogs;



6-  if you need some archive logs in your backup


$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore ARCHIVELOG FROM TIME 'SYSDATE-1' UNTIL TIME 'SYSDATE';
 
OR



RMAN> restore ARCHIVELOG FROM TIME "to_date('07/11/05 00:00:01','MM/DD/YY HH24:MI:SS')
UNTIL TIME 'SYSDATE';
 


7- if your data block is corrupted you will receive an error below.

Error:
 ORA-01578: ORACLE data block corrupted (file # 8, block # 13)
 ORA-01110: data file 8: ‘/oracle/oradata/users.dbf’


for recover data block;


$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN>blockrecover datafile 8 block 13;
 
For Block-Level Media Recovery – Concept & Example (Doc ID 144911.1)

 To recover, we can give a specific backup set;


# recovery from backupset

RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 FROM BACKUPSET;

# recovery from image copy

RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19
      FROM DATAFILECOPY;

# recovery from backupset which have "FULL" tag


RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 199
       FROM TAG = FULL;
During backup or “Validate Backup” command, RMAN finds corrupted blocks and writes to V$DATABASE_BLOCK_CORRUPTION view. When the RMAN recover the corrupt block then automatically updates this view. List of all the corruption of the past, can be viewed over V$BACKUP_CORRUPTION and V$COPY_CORRUPTION views.  If you run the following command,  RMAN will recover all the corrupted blocks in view V$DATABASE_BLOCK_CORRUPTION.



  RMAN>BLOCKRECOVER CORRUPTION LIST RESTORE UNTIL TIME 'SYSDATE-10';

8- if you have a image copy backup and your datafile number 2 has problems then you

can switch datafile number2 to image copy.


RMAN>sql ‘alter database datafile 2 offline’;
RMAN>switch datafile 2 to copy;
RMAN>recover datafile 2;
RMAN>sql ‘alter database datafile 2 online’;

  

Wednesday, October 6, 2021

RAC : 11gR2 Clusterware Startup Sequence 

Here is the brief explanation that how the clusterware brings up step by step .

1. When a node of an Oracle Clusterware cluster start/restarts, OHASD is started by platform-specific means. OHASD is the root for bringing up Oracle Clusterware. OHASD has access to the OLR (Oracle Local Registry) stored on the local file system. OLR provides needed data to complete OHASD initialization.
2. OHASD brings up GPNPD and CSSDCSSD has access to the GPNP Profile stored on the local file system. This profile contains the following vital bootstrap data;
        a. ASM Diskgroup Discovery String
        b. ASM SPFILE location (Diskgroup name)
        c. Name of the ASM Diskgroup containing the Voting Files
3. The Voting Files locations on ASM Disks are accessed by CSSD with well-known pointers in the ASM Disk headers and CSSD is able to complete initialization and start or join an existing cluster.
4. OHASD starts an ASM instance and ASM can now operate with CSSD initialized and operating. The ASM instance uses special code to locate the contents of the ASM SPFILE, assuming it is stored in a Diskgroup.
5. With an ASM instance operating and its Diskgroups mounted, access to Clusterware’s OCR is available to CRSD.
6. OHASD starts CRSD with access to the OCR in an ASM Diskgroup.
7. Clusterware completes initialization and brings up other services under its control.

When Clusterware starts three files are involved.

1. OLR – Is the first file to be read and opened. This file is local and this file contains information regarding where the voting disk is stored
and information to startup the ASM. (e.g ASM DiscoveryString)

2. VOTING DISK – This is the second file to be opened and read, this is dependent on only OLR being accessible.

ASM starts after CSSD or ASM does not start if CSSD is offline (i.e voting file missing)

How are Voting Disks stored in ASM?

Voting disks are placed directly on ASMDISK. Oracle Clusterware will store the votedisk on the disk within a disk group that holds the Voting Files.
Oracle Clusterware does not rely on ASM to access the Voting Files, which means Oracle Clusterware does not need of Diskgroup to read and write on ASMDISK. It is possible to check for existence of voting files on a ASMDISK using the V$ASM_DISK column VOTING_FILE.
So, voting files not depend of Diskgroup to be accessed, does not mean that the diskgroup is not needed, diskgroup and voting file are linked by their settings.

3. OCR – Finally the ASM Instance starts and mount all Diskgroups, then Clusterware Deamon (CRSD) opens and reads the OCR which is stored on Diskgroup.

So, if ASM already started, ASM does not depend on OCR or OLR to be online. ASM depends on CSSD (Votedisk) to be online.

There is a exclusive mode to start ASM without CSSD (but it’s to restore OCR or VOTE purposes)

Wednesday, July 14, 2021

 

Upgrade Oracle Database Manually from 12.2.0.1 to 19c (NON-CDB)

Table of Contents
___________________________________________________________________________________________________

0. Check Compatibility Before Upgrading Oracle Database
1. Environment

PRE-UPGRADE TASKS

2. Backup
3. Run preupgrade script
4. View Preupgrade log
5. Minimum tablespace sizes for upgrade
6. Update INITIALIZATION PARAMETERS
7. Gather DICTIONARY STATS
8. Purge Recyclebin
9. Refresh MVs
10. Run preupgrade_fixups.sql
11. Verify archive log dest size
12. Stop LISTENER
13. Create Flashback Guaranteed Restore Point

UPGRADE TASK

14. Shutdown Database
15. Copy init and password files from 12c to 19c dbs home
16. Startup DB in Upgrade mode
17. Run dbupgrade
18. Starup DB from 19c home

POST-UPGRADE TASKS WHEN DBUA USING

19. Run catcon.pl to start utlrp.sql
20. Run postupgrade_fixups.sql
21. Upgrade Timezone
22. Run utlusts.sql
23. Run catuppst.sql
24. Re-Run postupgrade_fixups.sql
25. Reverify INVALID OBJECTS
26. Drop Restore point
27. Set COMPATIBALE parameter value to 19.0.0
28. Verify DBA_REGISTRY
29. Add TNS Entries in 19c TNS home
30. Password File – orapwCID
31. Edit oratab
32. Back Up the Database
___________________________________________________________________________________________________


0. Check Compatibility Before Upgrading Oracle Database

 


1. Environment

Hostname                   : RAC1.RAJASEKHAR.COM

Database Name              : CID

DB VERSION          : 12.2.0.1

CDB                 : NON-CDB, Single Instance

DB Home Path        : /u01/app/oracle/product/12.2.0/dbhome_1

Datafile Location   : /u01/app/oracle/oradata/CID

 

Target DB VERSION   : 19c (19.4.0.0.0)

Target DB Path             : /u01/app/oracle/product/19.0.0/dbhome_1

 

Upgrade Method            : Manual


PRE-UPGRADE TASKS


2. Backup

Database Backup scripts - I have taken already

 

Sample Backup Database Script

 

rmanbackup.sh

 

[oracle@rac1 CID]$

[oracle@rac1 CID]$ chmod 775 rmanbackup.sh

[oracle@rac1 CID]$

[oracle@rac1 CID]$ nohup ./rmanbackup.sh & <--- hit ENTER twice.

 

TNS Files

 

[oracle@rac1 ~]$ cd u01/app/oracle/product/12.2.0/dbhome_1/network/admin/

[oracle@rac1 admin]$ cp -p listener.ora sqlnet.ora tnsnames.ora /u01/app/backup/

 

PFILE/SPFILE/PASSWORD (orapwSID) FILES

 

[oracle@rac1 dbs]$ pwd

/u01/app/oracle/product/12.2.0/dbhome_1/dbs

[oracle@rac1 dbs]$ cp -p spfileCID.ora orapwCID /u01/app/backup/

 

INVALID OBJECTS

 

SQL> select count(*) from dba_objects where status='INVALID';

 

  COUNT(*)

----------

         0 <----

 

SQL>

 


3. Run preupgrade script

. oraenv  (CID)

 

/u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT

 

--- OR ---

 

/u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/CID/preupgrade

 

[oracle@rac1 ~]$ mkdir -p /home/oracle/CID/preupgrade

[oracle@rac1 ~]$

 

[oracle@rac1 ~]$ . oraenv

ORACLE_SID = [oracle] ? CID

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

[oracle@rac1 ~]$

[oracle@rac1 ~]$ /u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/CID/preupgrade

==================

PREUPGRADE SUMMARY

==================

  /home/oracle/CID/preupgrade/preupgrade.log

  /home/oracle/CID/preupgrade/preupgrade_fixups.sql

  /home/oracle/CID/preupgrade/postupgrade_fixups.sql

 

Execute fixup scripts as indicated below:

 

Before upgrade:

 

Log into the database and execute the preupgrade fixups

@/home/oracle/CID/preupgrade/preupgrade_fixups.sql

 

After the upgrade:

 

Log into the database and execute the postupgrade fixups

@/home/oracle/CID/preupgrade/postupgrade_fixups.sql

 

Preupgrade complete: 2020-01-28T20:45:05

[oracle@rac1 ~]$


4. View Preupgrade log

[oracle@rac1 ~]$ cat /home/oracle/CID/preupgrade/preupgrade.log

Report generated by Oracle Database Pre-Upgrade Information Tool Version

19.0.0.0.0 Build: 1 on 2020-01-28T20:45:05

 

Upgrade-To version: 19.0.0.0.0

 

=======================================

Status of the database prior to upgrade

=======================================

      Database Name:  CID

     Container Name:  CID

       Container ID:  0

            Version:  12.2.0.1.0

     DB Patch Level:  No Patch Bundle applied

         Compatible:  12.2.0

          Blocksize:  8192

           Platform:  Linux x86 64-bit

      Timezone File:  26

  Database log mode:  ARCHIVELOG

           Readonly:  FALSE

            Edition:  EE

 

  Oracle Component                       Upgrade Action    Current Status

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

  Oracle Server                          [to be upgraded]  VALID

  JServer JAVA Virtual Machine           [to be upgraded]  VALID

  Oracle XDK for Java                    [to be upgraded]  VALID

  Real Application Clusters              [to be upgraded]  OPTION OFF

  Oracle Workspace Manager               [to be upgraded]  VALID

  OLAP Analytic Workspace                [to be upgraded]  VALID

  Oracle Label Security                  [to be upgraded]  VALID

  Oracle Database Vault                  [to be upgraded]  VALID

  Oracle Text                            [to be upgraded]  VALID

  Oracle XML Database                    [to be upgraded]  VALID

  Oracle Java Packages                   [to be upgraded]  VALID

  Oracle Multimedia                      [to be upgraded]  VALID

  Oracle Spatial                         [to be upgraded]  VALID

  Oracle OLAP API                        [to be upgraded]  VALID

 

==============

BEFORE UPGRADE

==============

 

  REQUIRED ACTIONS

  ================

  None

 

  RECOMMENDED ACTIONS

  ===================

  1.  (AUTOFIXUP) Gather stale data dictionary statistics prior to database

      upgrade in off-peak time using:

 

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

 

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

 

      Dictionary statistics help the Oracle optimizer find efficient SQL

      execution plans and are essential for proper upgrade timing. Oracle

      recommends gathering dictionary statistics in the last 24 hours before

      database upgrade.

 

      For information on managing optimizer statistics, refer to the 12.2.0.1

      Oracle Database SQL Tuning Guide.

 

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

  ================

  3.  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                             460 MB       500 MB

      SYSTEM                             800 MB       912 MB

      TEMP                                32 MB       150 MB

      UNDOTBS1                            70 MB       439 MB

 

      Minimum tablespace sizes for upgrade are estimates.

 

  4.  Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least

      4618 MB of archived logs.  Check alert log during the upgrade that there

      is no write error to the destination due to lack of disk space.

 

      Archiving cannot proceed if the archive log destination is full during

      upgrade.

 

      Archive Log Destination:

       Parameter    :  LOG_ARCHIVE_DEST_1

       Destination  :  /u01/app/archive/CID

 

      The database has archiving enabled.  The upgrade process will need free

      disk space in the archive log destination(s) to generate archived logs to.

 

  5.  Check the Oracle Backup and Recovery User's Guide for information on how

      to manage an RMAN recovery catalog schema.

 

      If you are using a version of the recovery catalog schema that is older

      than that required by the RMAN client version, then you must upgrade the

      catalog schema.

 

      It is good practice to have the catalog schema the same or higher version

      than the RMAN client version you are using.

 

  ORACLE GENERATED FIXUP SCRIPT

  =============================

  All of the issues in database CID

  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by

  executing the following

 

    SQL>@/home/oracle/CID/preupgrade/preupgrade_fixups.sql

 

=============

AFTER UPGRADE

=============

 

  REQUIRED ACTIONS

  ================

  None

 

  RECOMMENDED ACTIONS

  ===================

  6.  Upgrade the database time zone file using the DBMS_DST package.

 

      The database is using time zone file version 26 and the target 19 release

      ships with time zone file version 32.

 

      Oracle recommends upgrading to the desired (latest) version of the time

      zone file.  For more information, refer to "Upgrading the Time Zone File

      and Timestamp with Time Zone Data" in the 19 Oracle Database

      Globalization Support Guide.

 

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

 

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

  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by

  executing the following

 

    SQL>@/home/oracle/CID/preupgrade/postupgrade_fixups.sql

 

 

[oracle@rac1 ~]$


5. Verify tablespace sizes for upgrade

** Tablespace Auto extend ON and max size also set, hence no action taken.

 

http://www.br8dba.com/asm-2/#tspace

 

 

TABLESPACE_NAME   AUT FILE_NAME                                  TOTAL_SPACE FREE_SPACE      Free%  MAX_SPACE

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

SYSAUX            YES /u01/app/oracle/oradata/CID/sysaux01.dbf           460         23       5.08 31.9999847

SYSTEM            YES /u01/app/oracle/oradata/CID/system01.dbf           800          4        .48 31.9999847

UNDOTBS1          YES /u01/app/oracle/oradata/CID/undotbs01.dbf           70          3        4.2 31.9999847

USERS             YES /u01/app/oracle/oradata/CID/users01.dbf              5          4         80 31.9999847

*****************                                                ----------- ----------            ----------

sum                                                                     1335         34            127.999939

 

SQL>

 


6. Update INITIALIZATION PARAMETERS

In this test scenario, noting to update as per preupgrade.log. Hence no action taken.


7. Gather DICTIONARY STATS

SQL> SET ECHO ON;

SQL> SET SERVEROUTPUT ON;

SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

 

PL/SQL procedure successfully completed.

 

SQL>


8. Purge Recyclebin

SQL> PURGE DBA_RECYCLEBIN;

 

DBA Recyclebin purged.

 

SQL>


9. Refresh MVs

*** Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.

 

Run the following SQL query:

 

SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

 

no rows selected

 

SQL>

 

SQL> declare

list_failures integer(3) :=0;

begin

DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);

end;

/

 

PL/SQL procedure successfully completed.

 

SQL>


10. Run preupgrade_fixups.sql

SQL> @/home/oracle/CID/preupgrade/preupgrade_fixups.sql

SQL> REM

SQL> REM    Oracle PRE-Upgrade Fixup Script

SQL> REM

SQL> REM    Auto-Generated by:       Oracle Preupgrade Script

SQL> REM                             Version: 19.0.0.0.0 Build: 1

SQL> REM    Generated on:            2020-01-28 20:45:02

SQL> REM

SQL> REM    Source Database:         CID

SQL> REM    Source Database Version: 12.2.0.1.0

SQL> REM    For Upgrade to Version:     19.0.0.0.0

SQL> REM

SQL>

SQL> REM

SQL> REM    Setup Environment

SQL> REM

SQL> SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 200;

Executing Oracle PRE-Upgrade Fixup Script

 

Auto-Generated by:       Oracle Preupgrade Script

                         Version: 19.0.0.0.0 Build: 1

Generated on:            2020-01-28 20:45:02

 

For Source Database:     CID

Source Database Version: 12.2.0.1.0

For Upgrade to Version:  19.0.0.0.0

 

Preup                             Preupgrade

Action                            Issue Is

Number  Preupgrade Check Name     Remedied    Further DBA Action

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

    1.  dictionary_stats          YES         None.

    2.  pre_fixed_objects         YES         None.

    3.  tablespaces_info          NO          Informational only.

                                              Further action is optional.

    4.  min_archive_dest_size     NO          Informational only.

                                              Further action is optional.

    5.  rman_recovery_version     NO          Informational only.

                                              Further action is optional.

 

The fixup scripts have been run and resolved what they can. However,

there are still issues originally identified by the preupgrade that

have not been remedied and are still present in the database.

Depending on the severity of the specific issue, and the nature of

the issue itself, that could mean that your database is not ready

for upgrade.  To resolve the outstanding issues, start by reviewing

the preupgrade_fixups.sql and searching it for the name of

the failed CHECK NAME or Preupgrade Action Number listed above.

There you will find the original corresponding diagnostic message

from the preupgrade which explains in more detail what still needs

to be done.

 

PL/SQL procedure successfully completed.

 

SQL>


11. Verify archive log dest size

*** Please verify free space on ALL LOG_ARCHIVE_DEST_ locations including ALL standby destinations

 

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/archive/CID

Oldest online log sequence     1

Next log sequence to archive   2

Current log sequence           2

SQL>

SQL> !df -h /u01/app/archive/CID

Filesystem      Size  Used Avail Use% Mounted on

/dev/sda5        67G   35G   33G  52% /u01 <----

 

SQL>


12. Stop LISTENER

[oracle@rac1 ~]$ ps -ef | grep tns

root        15     2  0 20:01 ?        00:00:00 [netns]

oracle    3943     1  0 20:08 ?        00:00:00 /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr LISTENER_CID -inherit

oracle   16771  3093  0 21:03 pts/1    00:00:00 grep --color=auto tns

[oracle@rac1 ~]$ lsnrctl stop LISTENER_CID

 

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 28-JAN-2020 21:03:14

 

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

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1524)))

The command completed successfully

[oracle@rac1 ~]$

[oracle@rac1 ~]$ ps -ef | grep tns

root        15     2  0 20:01 ?        00:00:00 [netns]

oracle   16812  3093  0 21:03 pts/1    00:00:00 grep --color=auto tns

[oracle@rac1 ~]$


13. Create Flashback Guaranteed Restore Point

*** NO need to enable Flashback Database from 11.2.0.1 onwards

*** Database MUST be in Archive Log mode

*** MUST NOT change the compatible parameter to higher version

 

SQL> select flashback_on from v$database;

 

FLASHBACK_ON

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

NO <-----

 

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

 

NAME      OPEN_MODE            LOG_MODE

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

CID       READ WRITE           ARCHIVELOG

 

SQL> show parameter compatible

 

NAME                                 TYPE        VALUE

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

compatible                           string      12.2.0 <----

noncdb_compatible                    boolean     FALSE

SQL>

SQL> show parameter recovery

 

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string

db_recovery_file_dest_size           big integer 0

recovery_parallelism                 integer     0

remote_recovery_file_dest            string

SQL>

SQL>!mkdir -p /u01/app/oracle/fast_recovery_area

 

SQL> alter system set db_recovery_file_dest_size=10G;

 

System altered.

 

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';

 

System altered.

 

SQL> show parameter recovery

 

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area

db_recovery_file_dest_size           big integer 10G

recovery_parallelism                 integer     0

remote_recovery_file_dest            string

SQL>

 

SQL> select * from V$restore_point;

 

no rows selected

 

SQL>

 

SQL> create restore point pre_upgrade guarantee flashback database;

 

Restore point created.

 

SQL>

 

SQL> col name for a20

col GUARANTEE_FLASHBACK_DATABASE for a10

col TIME for a60

set lines 190

select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

 

NAME                 GUARANTEE_ TIME

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

PRE_UPGRADE          YES        28-JAN-20 09.05.50.000000000 PM

 

SQL>


UPGRADE TASK


14. Shutdown Database

SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;

 

NAME                 OPEN_MODE

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

CID                  READ WRITE

 

SQL> SHUT IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>


15. Copy init and password files from 12c to 19c dbs home

[oracle@rac1 ~]$ cd /u01/app/oracle/product/12.2.0/dbhome_1/dbs

[oracle@rac1 dbs]$ ls -ltr *CID*

-rw-r-----. 1 oracle oinstall   24 Jan 28 20:08 lkCID

-rw-r-----. 1 oracle oinstall 3584 Jan 28 20:11 orapwCID <---

-rw-r-----. 1 oracle oinstall 3584 Jan 28 21:05 spfileCID.ora <---

-rw-rw----. 1 oracle oinstall 1544 Jan 28 21:07 hc_CID.dat

[oracle@rac1 dbs]$

[oracle@rac1 dbs]$ cp orapwCID spfileCID.ora /u01/app/oracle/product/19.0.0/dbhome_1/dbs/

[oracle@rac1 dbs]$

[oracle@rac1 dbs]$ ls -ltr /u01/app/oracle/product/19.0.0/dbhome_1/dbs/*CID*

-rw-r-----. 1 oracle oinstall 3584 Jan 28 21:10 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileCID.ora

-rw-r-----. 1 oracle oinstall 3584 Jan 28 21:10 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwCID

[oracle@rac1 dbs]$


16. Startup DB in Upgrade mode from 19c home

[oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1

[oracle@rac1 ~]$ export ORACLE_SID=CID

[oracle@rac1 ~]$ PATH=/u01/app/oracle/product/19.0.0/dbhome_1/bin:$PATH; export PATH

[oracle@rac1 ~]$ which sqlplus

/u01/app/oracle/product/19.0.0/dbhome_1/bin/sqlplus

[oracle@rac1 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 28 21:13:24 2020

Version 19.4.0.0.0

 

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

 

Connected to an idle instance.

 

SQL> startup upgrade;

ORACLE instance started.

 

Total System Global Area 1560277408 bytes

Fixed Size                  8896928 bytes

Variable Size             939524096 bytes

Database Buffers          603979776 bytes

Redo Buffers                7876608 bytes

Database mounted.

Database opened.

SQL>

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

 

NAME      OPEN_MODE            CDB VERSION           STATUS

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

CID       READ WRITE           NO  19.0.0.0.0        OPEN MIGRATE <---

 

SQL>


17. Run dbupgrade

You can run the upgrade using either of the following commands. The second is actually just a shorthand for the former.

 

# Regular upgrade command

[oracle@rac1 ~]$ mkdir -p /home/oracle/whileupgrade

[oracle@rac1 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin

[oracle@rac1 ~]$ nohup /u01/app/oracle/product/19.0.0/dbhome_1/perl/bin/perl catctl.pl -l /home/oracle/whileupgrade -n 4 catupgrd.sql &

 

--- OR ---

 

# Shorthand command

[oracle@rac1 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/bin/

[oracle@rac1 bin]$ ls -ltr dbupgrade

-rwxr-x---. 1 oracle oinstall 3136 Apr 17  2019 dbupgrade

[oracle@rac1 bin]$

[oracle@rac1 bin]$ nohup ./dbupgrade & <--- Hit ENTER twice

[1] 22584

[oracle@rac1 bin]$ nohup: ignoring input and appending output to ânohup.outâ

 

[oracle@rac1 bin]$ jobs -l

[1]+ 22584 Running                 nohup ./dbupgrade & <----

[oracle@rac1 bin]$

[oracle@rac1 bin]$ disown

[oracle@rac1 bin]$

[oracle@rac1 bin]$ ps -ef | grep -i catctl.pl

oracle   22589 22584  0 21:33 pts/1    00:00:03 /u01/app/oracle/product/19.0.0/dbhome_1/perl/bin/perl -I/u01/app/oracle/product/19.0.0/dbhome_1/perl/lib /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catctl.pl /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catupgrd.sql

oracle   24991  3093  0 21:40 pts/1    00:00:00 grep --color=auto -i catctl.pl

[oracle@rac1 bin]$

 

Monitor upgrade log under below location

 

[oracle@rac1 upgrade20200128213345]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/CID/upgrade20200128213345

[oracle@rac1 upgrade20200128213345]$ ls -ltr *.log

-rw-------. 1 oracle oinstall 1688451 Jan 28 21:37 catupgrd1.log

-rw-------. 1 oracle oinstall 1278535 Jan 28 21:37 catupgrd2.log

-rw-------. 1 oracle oinstall 1461585 Jan 28 21:37 catupgrd3.log

-rw-------. 1 oracle oinstall 7944713 Jan 28 21:38 catupgrd0.log

[oracle@rac1 upgrade20200128213345]$

 

tail -f catupgrd0.log

tail -f catupgrd1.log

tail -f catupgrd2.log

tail -f catupgrd3.log

 

[oracle@rac1 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/bin/

[oracle@rac1 bin]$ more nohup.out

 

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.4.0.0.0DBRU_LINUX.X64_190626]

 

 

/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/upgrade20200128213334]

 

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

 

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

 

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

 

 

Number of Cpus        = 1

Database Name         = CID

DataBase Version      = 12.2.0.1.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/CID/upgrade20200128213345/catupgrd_cat

con_22589.lst]

 

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/CID/upgrade20200128213345/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/CID/upgrade20200128213345/catupgrd_*.lst] files for spool files, if any

 

 

Log file directory = [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/CID/upgrade20200128213345]

 

Parallel SQL Process Count            = 4

Components in [CID]

    Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]

Not Installed [APEX EM MGW ODM RAC WK]

 

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

Phases [0-107]         Start Time:[2020_01_28 21:33:56]

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

***********   Executing Change Scripts   ***********

Serial   Phase #:0    [CID] Files:1    Time: 32s

***************   Catalog Core SQL   ***************

Serial   Phase #:1    [CID] Files:5    Time: 56s

Restart  Phase #:2    [CID] Files:1    Time: 1s

***********   Catalog Tables and Views   ***********

Parallel Phase #:3    [CID] Files:19   Time: 32s

Restart  Phase #:4    [CID] Files:1    Time: 1s

*************   Catalog Final Scripts   ************

Serial   Phase #:5    [CID] Files:7    Time: 24s

*****************   Catproc Start   ****************

Serial   Phase #:6    [CID] Files:1    Time: 21s

*****************   Catproc Types   ****************

Serial   Phase #:7    [CID] Files:2    Time: 14s

Restart  Phase #:8    [CID] Files:1    Time: 1s

****************   Catproc Tables   ****************

Parallel Phase #:9    [CID] Files:67   Time: 47s

Restart  Phase #:10   [CID] Files:1    Time: 1s

*************   Catproc Package Specs   ************

Serial   Phase #:11   [CID] Files:1    Time: 94s

Restart  Phase #:12   [CID] Files:1    Time: 1s

**************   Catproc Procedures   **************

Parallel Phase #:13   [CID] Files:94   Time: 19s

Restart  Phase #:14   [CID] Files:1    Time: 1s

Parallel Phase #:15   [CID] Files:121  Time: 29s

Restart  Phase #:16   [CID] Files:1    Time: 1s

Serial   Phase #:17   [CID] Files:22   Time: 7s

Restart  Phase #:18   [CID] Files:1    Time: 1s

*****************   Catproc Views   ****************

Parallel Phase #:19   [CID] Files:32   Time: 37s

Restart  Phase #:20   [CID] Files:1    Time: 1s

Serial   Phase #:21   [CID] Files:3    Time: 19s

Restart  Phase #:22   [CID] Files:1    Time: 1s

Parallel Phase #:23   [CID] Files:25   Time: 217s

Restart  Phase #:24   [CID] Files:1    Time: 2s

Parallel Phase #:25   [CID] Files:12   Time: 120s

Restart  Phase #:26   [CID] Files:1    Time: 1s

Serial   Phase #:27   [CID] Files:1    Time: 0s

Serial   Phase #:28   [CID] Files:3    Time: 6s

Serial   Phase #:29   [CID] Files:1    Time: 0s

Restart  Phase #:30   [CID] Files:1    Time: 0s

***************   Catproc CDB Views   **************

Serial   Phase #:31   [CID] Files:1    Time: 3s

Restart  Phase #:32   [CID] Files:1    Time: 1s

Serial   Phase #:34   [CID] Files:1    Time: 0s

*****************   Catproc PLBs   *****************

Serial   Phase #:35   [CID] Files:294  Time: 48s

Serial   Phase #:36   [CID] Files:1    Time: 0s

Restart  Phase #:37   [CID] Files:1    Time: 1s

Serial   Phase #:38   [CID] Files:6    Time: 8s

Restart  Phase #:39   [CID] Files:1    Time: 1s

***************   Catproc DataPump   ***************

Serial   Phase #:40   [CID] Files:3    Time: 59s

Restart  Phase #:41   [CID] Files:1    Time: 0s

******************   Catproc SQL   *****************

Parallel Phase #:42   [CID] Files:13   Time: 131s

Restart  Phase #:43   [CID] Files:1    Time: 1s

Parallel Phase #:44   [CID] Files:11   Time: 20s

Restart  Phase #:45   [CID] Files:1    Time: 1s

Parallel Phase #:46   [CID] Files:3    Time: 4s

Restart  Phase #:47   [CID] Files:1    Time: 2s

*************   Final Catproc scripts   ************

Serial   Phase #:48   [CID] Files:1    Time: 10s

Restart  Phase #:49   [CID] Files:1    Time: 0s

**************   Final RDBMS scripts   *************

Serial   Phase #:50   [CID] Files:1    Time: 5s

************   Upgrade Component Start   ***********

Serial   Phase #:51   [CID] Files:1    Time: 3s

Restart  Phase #:52   [CID] Files:1    Time: 1s

**********   Upgrading Java and non-Java   *********

Serial   Phase #:53   [CID] Files:2    Time: 382s

*****************   Upgrading XDB   ****************

Restart  Phase #:54   [CID] Files:1    Time: 2s

Serial   Phase #:56   [CID] Files:3    Time: 10s

Serial   Phase #:57   [CID] Files:3    Time: 7s

Parallel Phase #:58   [CID] Files:10   Time: 6s

Parallel Phase #:59   [CID] Files:25   Time: 10s

Serial   Phase #:60   [CID] Files:4    Time: 12s

Serial   Phase #:61   [CID] Files:1    Time: 0s

Serial   Phase #:62   [CID] Files:32   Time: 7s

Serial   Phase #:63   [CID] Files:1    Time: 0s

Parallel Phase #:64   [CID] Files:6    Time: 9s

Serial   Phase #:65   [CID] Files:2    Time: 22s

Serial   Phase #:66   [CID] Files:3    Time: 32s

****************   Upgrading ORDIM   ***************

Restart  Phase #:67   [CID] Files:1    Time: 0s

Serial   Phase #:69   [CID] Files:1    Time: 5s

Parallel Phase #:70   [CID] Files:2    Time: 45s

Restart  Phase #:71   [CID] Files:1    Time: 1s

Parallel Phase #:72   [CID] Files:2    Time: 4s

Serial   Phase #:73   [CID] Files:2    Time: 5s

*****************   Upgrading SDO   ****************

Restart  Phase #:74   [CID] Files:1    Time: 1s

Serial   Phase #:76   [CID] Files:1    Time: 63s

Serial   Phase #:77   [CID] Files:2    Time: 6s

Restart  Phase #:78   [CID] Files:1    Time: 1s

Serial   Phase #:79   [CID] Files:1    Time: 55s

Restart  Phase #:80   [CID] Files:1    Time: 0s

Parallel Phase #:81   [CID] Files:3    Time: 115s

Restart  Phase #:82   [CID] Files:1    Time: 3s

Serial   Phase #:83   [CID] Files:1    Time: 13s

Restart  Phase #:84   [CID] Files:1    Time: 1s

Serial   Phase #:85   [CID] Files:1    Time: 13s

Restart  Phase #:86   [CID] Files:1    Time: 1s

Parallel Phase #:87   [CID] Files:4    Time: 155s

Restart  Phase #:88   [CID] Files:1    Time: 1s

Serial   Phase #:89   [CID] Files:1    Time: 4s

Restart  Phase #:90   [CID] Files:1    Time: 1s

Serial   Phase #:91   [CID] Files:2    Time: 12s

Restart  Phase #:92   [CID] Files:1    Time: 1s

Serial   Phase #:93   [CID] Files:1    Time: 2s

Restart  Phase #:94   [CID] Files:1    Time: 1s

*******   Upgrading ODM, WK, EXF, RUL, XOQ   *******

Serial   Phase #:95   [CID] Files:1    Time: 17s

Restart  Phase #:96   [CID] Files:1    Time: 0s

***********   Final Component scripts    ***********

Serial   Phase #:97   [CID] Files:1    Time: 5s

*************   Final Upgrade scripts   ************

Serial   Phase #:98   [CID] Files:1    Time: 520s

*******************   Migration   ******************

Serial   Phase #:99   [CID] Files:1    Time: 3s

***   End PDB Application Upgrade Pre-Shutdown   ***

Serial   Phase #:100  [CID] Files:1    Time: 2s

Serial   Phase #:101  [CID] Files:1    Time: 0s

Serial   Phase #:102  [CID] Files:1    Time: 108s

*****************   Post Upgrade   *****************

Serial   Phase #:103  [CID] Files:1    Time: 15s

****************   Summary report   ****************

Serial   Phase #:104  [CID] Files:1    Time: 3s

***   End PDB Application Upgrade Post-Shutdown   **

Serial   Phase #:105  [CID] Files:1    Time: 2s

Serial   Phase #:106  [CID] Files:1    Time: 0s

Serial   Phase #:107  [CID] Files:1     Time: 30s

 

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

Phases [0-107]         End Time:[2020_01_28 22:20:35]

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

 

Grand Total Time: 2800s

 

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

 

Upgrade Summary Report Located in:

/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/CID/upgrade20200128213345/upg_summary.log

 

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

[oracle@rac1 bin]$

 

[oracle@rac1 upgrade20200128213345]$ pwd

/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/CID/upgrade20200128213345

[oracle@rac1 upgrade20200128213345]$ cat upg_summary.log

 

Oracle Database Release 19 Post-Upgrade Status Tool    01-28-2020 22:20:0

Database Name: CID

 

Component                               Current         Full     Elapsed Time

Name                                    Status          Version  HH:MM:SS

 

Oracle Server                          UPGRADED      19.4.0.0.0  00:18:01

JServer JAVA Virtual Machine           UPGRADED      19.4.0.0.0  00:02:29

Oracle XDK                             UPGRADED      19.4.0.0.0  00:01:12

Oracle Database Java Packages          UPGRADED      19.4.0.0.0  00:00:13

OLAP Analytic Workspace                UPGRADED      19.4.0.0.0  00:00:19

Oracle Label Security                  UPGRADED      19.4.0.0.0  00:00:11

Oracle Database Vault                  UPGRADED      19.4.0.0.0  00:00:32

Oracle Text                            UPGRADED      19.4.0.0.0  00:00:43

Oracle Workspace Manager               UPGRADED      19.4.0.0.0  00:00:37

Oracle Real Application Clusters       UPGRADED      19.4.0.0.0  00:00:01

Oracle XML Database                    UPGRADED      19.4.0.0.0  00:01:49

Oracle Multimedia                      UPGRADED      19.4.0.0.0  00:00:55

Spatial                                 LOADING      19.4.0.0.0  00:07:19

Oracle OLAP API                        UPGRADED      19.4.0.0.0  00:00:14

Datapatch                                                        00:08:34

Final Actions                                                    00:08:43

Post Upgrade                                                     00:00:12

 

Total Upgrade Time: 00:44:08

 

Database time zone version is 26. It is older than current release time

zone version 32. Time zone upgrade is needed using the DBMS_DST package.

 

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

[oracle@rac1 upgrade20200128213345]$


18. Starup DB from 19c home

[oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1

[oracle@rac1 ~]$ export ORACLE_SID=CID

[oracle@rac1 ~]$ PATH=/u01/app/oracle/product/19.0.0/dbhome_1/bin:$PATH; export PATH

[oracle@rac1 ~]$ which sqlplus

/u01/app/oracle/product/19.0.0/dbhome_1/bin/sqlplus

[oracle@rac1 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 28 22:25:18 2020

Version 19.4.0.0.0

 

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

 

Connected to an idle instance.

 

SQL> startup;

ORACLE instance started.

 

Total System Global Area 1560277408 bytes

Fixed Size                  8896928 bytes

Variable Size            1174405120 bytes

Database Buffers          369098752 bytes

Redo Buffers                7876608 bytes

Database mounted.

Database opened.

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

 

NAME      OPEN_MODE            CDB VERSION           STATUS

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

CID       READ WRITE           NO  19.0.0.0.0        OPEN <-----

 

SQL>

SQL> col COMP_ID for a10

col COMP_NAME for a40

col VERSION for a15

set lines 180

set pages 999

select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

 

COMP_ID    COMP_NAME                                VERSION         STATUS

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

CATALOG    Oracle Database Catalog Views            19.0.0.0.0      UPGRADED

CATPROC    Oracle Database Packages and Types       19.0.0.0.0      UPGRADED

JAVAVM     JServer JAVA Virtual Machine             19.0.0.0.0      UPGRADED

XML        Oracle XDK                               19.0.0.0.0      UPGRADED

CATJAVA    Oracle Database Java Packages            19.0.0.0.0      UPGRADED

APS        OLAP Analytic Workspace                  19.0.0.0.0      UPGRADED

RAC        Oracle Real Application Clusters         19.0.0.0.0      UPGRADED

XDB        Oracle XML Database                      19.0.0.0.0      UPGRADED

OWM        Oracle Workspace Manager                 19.0.0.0.0      UPGRADED

CONTEXT    Oracle Text                              19.0.0.0.0      UPGRADED

ORDIM      Oracle Multimedia                        19.0.0.0.0      UPGRADED

SDO        Spatial                                  19.0.0.0.0      LOADING

XOQ        Oracle OLAP API                          19.0.0.0.0      UPGRADED

OLS        Oracle Label Security                    19.0.0.0.0      UPGRADED

DV         Oracle Database Vault                    19.0.0.0.0      UPGRADED

 

15 rows selected.

 

SQL>


POST-UPGRADE TASKS WHEN DBUA USING


19. Run utlrp.sql

Run catcon.pl to start utlrp.sql, and to recompile any remaining invalid objects.

 

$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

 

-- OR ---

 

cd /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin

nohup sqlplus "/ as sysdba" @utlrp.sql > /home/oracle/utlrp.out 2>&1 &

 

 

SQL> select count(*) from dba_objects where status='INVALID';

 

  COUNT(*)

----------

      1413  <----

 

SQL> select count(*) from dba_objects where status='INVALID' and owner in ('SYS','SYSTEM');

 

  COUNT(*)

----------

       655 <----

 

SQL>

 

SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utlrp.sql

 

Session altered.

 

 

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN              2020-01-29 00:31:04

 

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              2020-01-29 00:36:03

 

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>

 

SQL> select count(*) from dba_objects where status='INVALID';

 

  COUNT(*)

----------

         0

 

SQL>


20. Run postupgrade_fixups.sql

SQL> @/home/oracle/CID/preupgrade/postupgrade_fixups.sql

 

Session altered.

 

 

PL/SQL procedure successfully completed.

 

 

PL/SQL procedure successfully completed.

 

 

PL/SQL procedure successfully completed.

 

 

Package created.

 

No errors.

 

Package body created.

 

 

PL/SQL procedure successfully completed.

 

No errors.

 

 

 

 

 

Package created.

 

No errors.

 

Package body created.

 

No errors.

Executing Oracle POST-Upgrade Fixup Script

 

Auto-Generated by:       Oracle Preupgrade Script

                         Version: 19.0.0.0.0 Build: 1

Generated on:            2020-01-28 20:45:05

 

For Source Database:     CID

Source Database Version: 12.2.0.1.0

For Upgrade to Version:  19.0.0.0.0

 

Preup                             Preupgrade

Action                            Issue Is

Number  Preupgrade Check Name     Remedied    Further DBA Action

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

    6.  old_time_zones_exist      NO          Manual fixup recommended.

    7.  post_dictionary           YES         None.

    8.  post_fixed_objects        NO          Informational only.

                                              Further action is optional.

 

The fixup scripts have been run and resolved what they can. However,

there are still issues originally identified by the preupgrade that

have not been remedied and are still present in the database.

Depending on the severity of the specific issue, and the nature of

the issue itself, that could mean that your database upgrade is not

fully complete.  To resolve the outstanding issues, start by reviewing

the postupgrade_fixups.sql and searching it for the name of

the failed CHECK NAME or Preupgrade Action Number listed above.

There you will find the original corresponding diagnostic message

from the preupgrade which explains in more detail what still needs

to be done.

 

PL/SQL procedure successfully completed.

 

 

Session altered.

 

SQL>


21. Upgrade Timezone

For releases (18c, 19c), the timezone upgrade scripts are included in the target ORACLE_HOME under rdbms/admin directory

 

The following scripts get delivered with Oracle Database 18c onward

 

    $ORACLE_HOME/rdbms/admin/utltz_countstats.sql

    Script to gives how much TIMESTAMP WITH TIME ZONE data there is in a database using stats info. No restart required.

   

    $ORACLE_HOME/rdbms/admin/utltz_countstar.sql

    Script to approximate how much TIMESTAMP WITH TIME ZONE data there is in a database using a COUNT(*) for each table that has a TSTZ column. This script is useful when using DBMS_DST package or the scripts of utlz_upg_check.sql and utlz_upg_apply.sql scripts.

   

    $ORACLE_HOME/rdbms/admin/utltz_upg_check.sql

    Time zone upgrade check script

   

    $ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql

    Time zone apply script. Warning: This script will restart the database and adjust time zone data.

 

[oracle@rac1 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/

[oracle@rac1 admin]$ ls -ltr utltz_countstats.sql utltz_countstar.sql utltz_upg_check.sql utltz_upg_apply.sql

-rw-r--r--. 1 oracle oinstall  8317 Feb 25  2017 utltz_countstats.sql

-rw-r--r--. 1 oracle oinstall  7423 Feb 25  2017 utltz_countstar.sql

-rw-r--r--. 1 oracle oinstall 33684 Sep  9  2017 utltz_upg_check.sql

-rw-r--r--. 1 oracle oinstall 21526 Sep  9  2017 utltz_upg_apply.sql

[oracle@rac1 admin]$

 

SQL> SELECT version FROM v$timezone_file;

 

   VERSION

----------

        26 <-----

 

SQL>

 

SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utltz_upg_check.sql

 

Session altered.

 

INFO: Starting with RDBMS DST update preparation.

INFO: NO actual RDBMS DST update will be done by this script.

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

INFO: Doing checks for known issues ...

INFO: Database version is 19.0.0.0 .

INFO: Database RDBMS DST version is DSTv26 .

INFO: No known issues detected.

INFO: Now detecting new RDBMS DST version.

A prepare window has been successfully started.

INFO: Newest RDBMS DST version detected is DSTv32 .

INFO: Next step is checking all TSTZ data.

INFO: It might take a while before any further output is seen ...

A prepare window has been successfully ended.

INFO: A newer RDBMS DST version than the one currently used is found.

INFO: Note that NO DST update was yet done.

INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.

INFO: Note that the utltz_upg_apply.sql script will

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

 

Session altered.

 

SQL>

 

SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utltz_upg_apply.sql

 

Session altered.

 

INFO: If an ERROR occurs, the script will EXIT SQL*Plus.

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

WARNING: This script will restart the database 2 times

WARNING: WITHOUT asking ANY confirmation.

WARNING: Hit control-c NOW if this is not intended.

INFO: Restarting the database in UPGRADE mode to start the DST upgrade.

Database closed.

Database dismounted.

ORACLE instance shut down.

ORACLE instance started.

 

Total System Global Area 1560277408 bytes

Fixed Size                  8896928 bytes

Variable Size            1191182336 bytes

Database Buffers          352321536 bytes

Redo Buffers                7876608 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 1560277408 bytes

Fixed Size                  8896928 bytes

Variable Size            1191182336 bytes

Database Buffers          352321536 bytes

Redo Buffers                7876608 bytes

Database mounted.

Database opened.

INFO: Upgrading all non-SYS TSTZ data.

INFO: It might take time before any further output is seen ...

INFO: Do NOT start any application yet that uses TSTZ data!

INFO: Next is a list of all upgraded tables:

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"

Number of failures: 0

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"

Number of failures: 0

Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"

Number of failures: 0

Table list: "DVSYS"."SIMULATION_LOG$"

Number of failures: 0

Table list: "DVSYS"."AUDIT_TRAIL$"

Number of failures: 0

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

An upgrade window has been successfully ended.

INFO: Your new Server RDBMS DST version is DSTv32 .

INFO: The RDBMS DST update is successfully finished.

INFO: Make sure to exit this SQL*Plus session.

INFO: Do not use it for timezone related selects.

 

Session altered.

 

SQL>

SQL> SELECT version FROM v$timezone_file;

 

   VERSION

----------

        32 <----

 

1 row selected.

 

SQL>


22. Run utlusts.sql

*** Note: utluNNNs.sql is replaced by utlusts.sql in 19c version

*** Note: In 19c Earlier version utluNNNs.sql is replaced by utlusts.sql

*** Run utlusts.sql as many times as you want, at any time after the upgrade is completed.

*** utlusts.sql reads the view called dba_registry_log and displays the upgrade results for the database components.

 

SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utlusts.sql TEXT

 

Oracle Database Release 19 Post-Upgrade Status Tool    01-29-2020 01:41:3

Database Name: CID

 

Component                               Current         Full     Elapsed Time

Name                                    Status          Version  HH:MM:SS

 

Oracle Server                             VALID      19.4.0.0.0  00:18:01

JServer JAVA Virtual Machine              VALID      19.4.0.0.0  00:02:29

Oracle XDK                                VALID      19.4.0.0.0  00:01:12

Oracle Database Java Packages             VALID      19.4.0.0.0  00:00:13

OLAP Analytic Workspace                   VALID      19.4.0.0.0  00:00:19

Oracle Label Security                     VALID      19.4.0.0.0  00:00:11

Oracle Database Vault                     VALID      19.4.0.0.0  00:00:32

Oracle Text                               VALID      19.4.0.0.0  00:00:43

Oracle Workspace Manager                  VALID      19.4.0.0.0  00:00:37

Oracle Real Application Clusters     OPTION OFF      19.4.0.0.0  00:00:01

Oracle XML Database                       VALID      19.4.0.0.0  00:01:49

Oracle Multimedia                         VALID      19.4.0.0.0  00:00:55

Spatial                                   VALID      19.4.0.0.0  00:07:19

Oracle OLAP API                           VALID      19.4.0.0.0  00:00:14

Datapatch                                                        00:08:34

Final Actions                                                    00:08:43

Post Upgrade                                                     00:00:12

Post Compile                                                     00:04:58

 

Total Upgrade Time: 00:49:07

 

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

 

SQL>


23. Run catuppst.sql

/*

In 12c:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/upgrd/oracle-database-upgrade-utilities.html#GUID-408F22C3-2AD6-4DA4-8015-F5C6149508F0

You must run this script, either through DBUA or manually, if you perform a manual upgrade.

 

DBUA automatically runs catuppst.sql. You only must run this script separately for manual upgrades.

 

Do not run this in UPGRADE mode. Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform remaining upgrade actions that do not require the database to be in UPGRADE mode. If an Oracle bundle patch or patch set update (PSU or BP) is installed in the Oracle home, then this script automatically applies that patch set update to the database.

 

Caution: If you perform a manual upgrade, and you do not run catuppst.sql, then your database suffers performance degradation over time.

*/

 

*** Actually it will run as part of upgrade. We have reviewed catupgrd0.log and below is the output... found catuppst.sql ran and don't see errors.

 

/*

Rem catuppst.sql

Rem

Rem Copyright (c) 2006, 2018, Oracle and/or its affiliates.

Rem All rights reserved.

Rem

Rem    NAME

Rem      catuppst.sql - CATalog UPgrade PoST-upgrade actions

Rem

Rem    DESCRIPTION

Rem      This post-upgrade script performs remaining upgrade actions that

Rem      do not require that the database be open in UPGRADE mode.

Rem      Automatically apply the latest PSU.

Rem

Rem    NOTES

Rem      You must be connected AS SYSDBA to run this script.

..

..

22:19:59 SQL> -- DBUA_TIMESTAMP: catuppst.sql finished

22:19:59 SQL> SELECT dbms_registry_sys.time_stamp('CATUPPST') as timestamp from dual;

 

TIMESTAMP

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

COMP_TIMESTAMP CATUPPST          2020-01-28 22:19:59

DBUA_TIMESTAMP CATUPPST      FINISHED 2020-01-28 22:19:59

DBUA_TIMESTAMP CATUPPST     NONE 2020-01-28 22:19:59

*/

 

*** If we had no errors, the "catuppst.sql" script would have been run as part of the upgrade. we need to run it manually if did have errors.

*** However can run one more time make sure no errors during execution.

 

SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catuppst.sql

 

TIMESTAMP

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

COMP_TIMESTAMP DBRESTART              2020-01-29 02:24:23

DBUA_TIMESTAMP DBRESTART     FINISHED 2020-01-29 02:24:23

DBUA_TIMESTAMP DBRESTART         NONE 2020-01-29 02:24:23

 

 

TIMESTAMP

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

DBUA_TIMESTAMP CATUPPST       STARTED 2020-01-29 02:24:23

 

TIMESTAMP

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

COMP_TIMESTAMP POSTUP_BGN             2020-01-29 02:24:23

DBUA_TIMESTAMP POSTUP_BGN    FINISHED 2020-01-29 02:24:23

DBUA_TIMESTAMP POSTUP_BGN        NONE 2020-01-29 02:24:23

 

 

TIMESTAMP

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

COMP_TIMESTAMP CATREQ_BGN             2020-01-29 02:24:23

DBUA_TIMESTAMP CATREQ_BGN    FINISHED 2020-01-29 02:24:23

DBUA_TIMESTAMP CATREQ_BGN        NONE 2020-01-29 02:24:23

 

catrequtlmg: b_StatEvt     = TRUE

catrequtlmg: b_SelProps    = FALSE

catrequtlmg: b_UpgradeMode = FALSE

catrequtlmg: b_InUtlMig    = FALSE

 

TIMESTAMP

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