Reinstate Failed Primary into Physical Standby

REINSTATE:

After failover we have to reinstate the primary db to physical standby. For that we have to find out the scn in the new primary db- (standby_became_primary_scn parameter from v$database) where at the particular scn the standby database has became the primary database. Using this scn in our old primary db we have to flashback our db to this particular scn and then we have to convert our old primary to physical standby.

Note: To perform the reinstate process we have to ensure that the flashback must be enabled in the primary db which got crashed.

Overview Steps:

Step:1 Find out the standby_became_primary_scn value in the new primary db from v$database view.

Step:2 Flashback the old primary db to this particular scn value that is taken from the new primary db.

Step:3 Then convert the primary db to physical standby and bounce the database.

Step:4 Check for the name, open_mode, database_role it is changed to physical standby, we have performed the reinstate task successfully.

Step;5 verify whether the standby is in sync with the primary after reinstate.

Step:1 Get the value of standby_became_primary_scn from the new primary db.

Step:2 Flashback the old primary db to this scn value.

Step:3 Convert the primary db into physical standby, then bounce the database.

Step:4 Now the database_role has changed from primary to physical standby, reinstate performed successfully.

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Oracle 19c Binaries and Database installation

Here , I am going to install 19c binaries database software, but I already moved the 19c software to my VM and i also unzipped it. Just I am running the ./runInstaller file as a oracle user.

Follow the steps to install 19c database software,

Step:1 in this window choose Set up Software only option and click Next. It means that we are only installing the software.

Step:2 The second page asks for whether we are installing single instance database or RAC Installation, choose single instance db installation and click Next.

Step:3 Here we have to choose the type of the edition we are going to install,either Enterprise edition or Standard Edition 2,choose Enterprise edition and click Next.

Step:4 Then it asks for Oracle base location, choose ‘/u01/app/oracle’ as location and then click Next.

Step:5 Choose inventory location(/u01/app/oraInventory) and oraInventory group as oinstall.

Step:6 in this window select OSDBA , OSOPER , OSBACKUPDBA , OSDGDBA , OSKMDBA , OSRACDBA group as oinstall and proceed further.

Step:7 It shows for root script execution configuration ,here don’t choose any of the option go to the next window.

Step:8 This window asks to perform the prerequisite checks, for us two of the checks completed with warning. click Next.

Step:9 Yes we have completed all the steps this is the summary page of our software installation, check the details if it is correct proceed further.

Step:10 Our 19c software installation is in progress.

Step:11 When the software is in progress,you have to run the following two scripts as “root” user.

Step:12 The oracle 19c software was installed successfully.

Step:13 Previously we have installed the 19c software, we are proceeding with the database creation. Using dbca create the database, choose create a database option and click Next.

Step:14 In this window choose advanced configuration.

Step:15 choose database type as oracle single instance database and you have to choose a template for your database. here I choose general purpose or transaction processing as a template for my db.

Step:16 In this specify the global database name and Sid ,it is nothing but the name you give to your database.

Step:17 We have to choose the attributes for your database, either we can use default storage attributes or you can specify the customized attributes.

Step:18 you have to enable the Fast Recovery Area option it is used to recover your database, then click Next.

Step:19 create a new listener, enter the listener name and port number for your listener, it very important to configure a listener for your db.

Step:20 configure the oracle data vault option if you want. it helps us to protect our application data by providing powerful security controls.

Step:21 it asks for storage option either we want to use ASMM or MSMM.I am choosing Automatic Shared Memory Management and set the required SGA,PGA values.

Step:22 select use Unicode (AL32UTF8) character set and choose National character set, default language, default territory as follows,

National character set :AL16UTF16

Default language: American

Default territory: United states

Step:23 Select the management option as ‘configure Enterprise Manager (EM) database express check box.

Step;24 Create the password credentials for your db, here I use the same administrative passwords for all accounts.

Step:25 We have completed all the above mentioned configurations, we have yet one more step left to create a database. Choose the create database check box.

Step:26 check with all the configurations are correctly configured and proceed further.

Step:27 After that the database creation is in progress.

Step:28 we have successfully completed the database and we can now connect to our database. Here I have connected to my db and checked the name, open_mode of my database.

AutoUpgrade with Source and Target Database Homes on Same Server – 19c New Feature

DB Upgrade using AutoUpgrade tool:

It is a feature that is enhanced in oracle 19c and it is mainly used to automate the upgrade process.it not only automates the database upgrade but also performs the pre-upgrade steps and the post upgrade steps automatically.it just simplifies the upgrade activity, previously we have to use dbua/manual method/datapump methods to upgrade the database now we can upgrade the number of databases using AutoUpgrade utility within the short span of time.

Note : If you are doing AutoUpgrade on same server it is not necessary to run the upgrade mode you can stop with the deploy mode, our database will be upgraded.

On the other hand if you are performing AutoUpgrade on different servers it is necessary to run all the mode till upgrade mode.(i.e. analyze, fixups, deploy, upgrade)

AutoUpgrade is available for Oracle Database Enterprise Edition, and Oracle Database Standard Edition. It is not available for Oracle Database Express Edition.

AutoUpgrade Support is available for Oracle Database 12c Release 2 (12.2) and Oracle Database 18c (18.5) target homes. For both Oracle Database 12c Release 2 (12.2) and Oracle Database 18c (18.5) target homes.

Advantages of AutoUpgrade Utility:

Time saving method (completes the upgrade in a short time)
can upgrade multiple Oracle Database deployments at the same time, using a single configuration file, customized as needed for each database deployment.
preventing the issues before upgrade.
It automatically performs the preupgrade and post upgrade steps/checks.

AUTOUPGRADE PROCESSING MODES:

Generally AutoUpgrade processes in four modes, they are ,

Analyze

It performs a read only pre-upgrade analysis of databases before upgrade and detects any issues which may prevent the successful completion of upgradation.it should be run in the source database like normal operations.

Fixups

In Fixups mode it checks the same as it have done in the analyze mode, so that it can identify issues that require fixing. In Fixup Mode, the AutoUpgrade utility detects and identifies both fixes that require manual intervention and AutoUpgrade utility can clear the fix during upgrade.

Deploy

In deploy mode it confirms that our database has been successfully upgraded and it copies the sqlnet.ora, listener.ora, tnsnames.ora from source to target home.

Upgrade

An upgrade completes with either deploy or upgrade, after this we can run our post upgrade checks.in this we can run our custom scripts on the upgraded databases in accordance with the configuration file that we have created. After this the upgraded oracle database starts with a new oracle home.

AutoUpgrade utility jobs pass through a series of phases, called stages, during which specific actions are performed.

AutoUpgrade has the following stages,

SETUP
PREUPGRADE
PRECHECKS
GRP
PREFIXUPS
DRAIN
DBUPGRADE
POSTCHECKS
POSTFIXUPS
POSTUPGRADE

Overview steps:

Step:1 Check for the autoupgrade.jar file version which is already present in the database.($ORACLE_HOME/rdbms/admin/preupgrade.jar)

Step:2 Then we have to download the latest autoupgrade.jar version and then we have to share the file in /home/oracle location and check the version of the file.

Step:3 Our database which we are going to upgrade must be in archivelog mode and flashback should be enabled.

Step:4 Create a configuration file which consists of source Sid, log directory, start time of the upgrade, target home, source db version.

Step:5 Once we run the analyze mode it checks for any issues and also it analyses the source database, it will estimate the time when the upgrade will get completed, jobs that are running, status of the jobs ,once it is completed without any error we can run the fixups mode here also the same it detects and fixes the bugs that is either by manual or bugs that is fixed automatically during upgrade.

Step:6 After this the next stage is deploy and upgrade both are same which upgrades our database, it confirms that our database is upgraded successfully. here it transfers the listener.ora, tnsnames.ora files from source home to target’s home. Then we can start our upgraded database in the new oracle home.

Step:7 Then check time zone version of upgraded database.

Step:1 Check for the autoupgrade.jar version which is already present. The autoupgrade.jar file is available in the Oracle 19c database software in the $ORACLE_HOME/rdbms/admin directory.

Step:2 Then download the latest version of autoupgrade.jar from the MOS note 2485457.1

Step:3 Create the configuration files containing the parameters where the logs should be stored, dbid, database name to be upgraded, source and target version home.

Step:4 Enable the archive log mode in the database which is to be upgraded.

Step:5 Analyze the database to be upgraded using the analyze mode and fixups the issues by running fixups mode.

There are three main commands in this upgrade to check the status of the upgrade.

lsj – list the jobs which are running.

tasks – to see the tasks of the jobs and their status.

status – it gives an overall summary of the upgrade, here we can see the no of container and non-container databases, job finished successfully, job aborted and jobs which are in progress state.

Other than these there are some commands which helps you to perform the AutoUpgrade in a better way.

Running Analyze mode:

Please find the detailed logfiles mentioned below.

prechecks_hongkong.log

hongkong_checklist.log

Running fixups mode:

Please find the detailed logfiles mentioned below.

prefixups_hongkong.log

hongkong_preupgrade.log

Step:6 After these above steps are completed successfully we can proceed the upgrade process using deploy and upgrade modes.

Running deploy mode:

Our database is upgraded successfully from 12c to 19c using AutoUpgrade tool.

Please find the detailed logfiles mentioned below.

drain_hongkong.log

autoupgrade_20210309.log

autoupgrade_20210309_user.log

Here I am checking whether database is upgraded or not by running the 19c environment file and logging in to the database and also verify the name, open mode and timezone version.

Please find the detailed logfiles mentioned below.

postchecks_hongkong.log

postfixups_hongkong.log

postupgrade.log

The below mentioned logfiles contains the overall summary for AutoUpgrade.

upg_summary.log

cat phase.log

Convert Physical standby to Logical standby Database

Overview Steps:

Step:1 verify the name, open mode, roles in primary and standby databases.

Step:2 Look into the max(sequence#) value from v$thread view on both sides.

Step:3 Check for unsupported datatypes in logical standby in primary db.

Step:4 Find the tables without unique identifier in primary database.

Step;5 At Primary Database, Build a Log Miner in redo data So that sql apply can Interpret the redo changes.

Step:6 Enable the log _archive_dest_3 parameter for primary db.

Step:7 logical supplemental is automatically enabled by executing this, if not already enabled.

Step:8 Cancel recovery at Physical Standby Database.

Step:9 Create a spfile and then mount the database. In order to create logical standby at the mount stage.

Step:10 Enable the log _archive_dest_3 parameter for standby db.

Step:11 Open the database with reset log option.

Step:12 Then start the logical recovery.

Step:13 verify the database has been converted to logical standby databases.

Step:1 Check name, open_mode, database_role of primary and standby databases.

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

NAME             OPEN_MODE      DATABASE_ROLE
--------- -------------------- ----------------
CANADA           READ ONLY      PHYSICAL STANDBY

Step:2 Verify the max(sequence#) from v$thread view on both the sides, it should have same max(sequence#) value.

on primary side

SQL> select max(sequence#) from v$thread;

MAX(SEQUENCE#)
--------------
     24

on standby side

SQL> select max(sequence#) from v$thread;

MAX(SEQUENCE#)
--------------
     24

Step:3 Check for unsupported datatypes in logical standby in primary db and Find the tables without unique identifier in primary database.

Step:4 execute dbms_logstdby.build,While executing this,logical supplemental is automatically enabled,if not already enabled and also enable the log_archive_dest_3 parameter.

SQL>execute dbms_logstdby.build;

PL/SQL Procedure successfully completed.

Step:5 Cancel recovery at physical standby database.

SQL>alter database recover managed standby cancel;

Database altered

Step:6 start the db at mount stage and issue the following command to convert the physical standby to logical standby.

SQL>alter database recover to logical standby standby;

Database altered

Step:7 open the database with resetlog option.

SQL>alter database open resetlogs;

Database altered

Step:8 perform the logical recovery.

SQL>alter database start logical standby apply immediate;

Database altered

Step:9 check whether database role of db has changed from physical standby to logical standby.once it is altered it is clear that we have successfully converted our physical standby into logical standby.

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

  NAME            DATABASE_ROLE
---------     -------------------- 
 CANADA          LOGICAL STANDBY

Converting a physical standby database into snapshot standby database

What is snapshot standby database?

It allows to perform read-write operation on the standby database.ie converting the physical standby to snapshot standby database. On that, we can do all types of testing or it can be used as a development database. After our testing process is over we can convert our snapshot standby database in to physical standby database and changes done to the snapshot standby will be reverted.

A snapshot standby database receives and archives redo data but it does not apply the redo data from the primary database.

NOTE: FRA (Fast Recovery Area) must be configured in physical standby database but it is not necessary to have flashback enabled.

Overview steps:

Step:1 verify open_mode, database_role of the standby db it should be in read only with apply, physical standby

Step:2 cancel the recovery process(MRP)

Step:3 Enable the flashback_mode in standby, it can be enabled after specifying the location of the db_recovery_file_dest (db recovery area)

Step:4 Then we can convert our physical standby to snapshot standby database.

Step:1 check the open_mode, database_role of the database.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

SQL> select database_role from v$database;

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

Step:2 we must stop the redo apply process using the following command.

SQL> alter database recover managed standby database cancel;

Database altered.

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

SQL> startup mount
ORACLE instance started.
Total System Global Area     243268216 bytes
Fixed Size                     8895096 bytes
Variable Size                180355072 bytes
Database Buffers              50331648 bytes
Redo Buffers                   3686400 bytes
Database mounted.

Step:3 Enable the flashback to convert it into snapshot standby database.

SQL> alter database flashback on;

Database altered.

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE                  VALUE
--------------------------------   -----------    ------------------------------
db_recovery_file_dest              string       /u01/app/oracle/fast_recovery_area/  
db_recovery_file_dest_size         big integer            8256M

SQL> select database_role from v$database;

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

SQL> select status from v$instance;

STATUS
------------
MOUNTED

Step:5 Convert the physical standby to snapshot standby using below command.

SQL> alter database convert to snapshot standby;

Database altered

Step:6 Mount the db and open it.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database open;

Database altered.

Step:7 select the name of the restore point, gurantee_flashback_database information’s from v$restore_point view, where gurantee_flashback_database parameter tells that whether flashback log files will be kept to ensure a flashback to this point.

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;

   NAME                                                      GUA

----------------------------------------------------  ----------------
  SNAPSHOT_STANDBY_REQUIRED_02/18/2021 01:27:55              YES

Step:8 check the role of the db whether it has converted into snapshot standby.

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
SNAPSHOT STANDBY

Step:9 Now I am performing some DML operations, creating a table student and adding 100000 rows in it.

SQL> create table student(id number(5));

Table created.

SQL> begin
for i in 1 .. 100000 loop
insert into student values(1);
end loop;
end;

PL/SQL procedure successfully completed.

SQL> select count(*) from student;

COUNT(*)
----------
100000

SQL> commit;
Commit complete.

SQL> shut immediate

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

SQL> startup mount
ORACLE instance started.

Total System Global Area      243268216 bytes
Fixed Size                      8895096 bytes
Variable Size                 180355072 bytes
Database Buffers               50331648 bytes
Redo Buffers                    3686400 bytes
Database mounted.

Step:10 Converting snapshot standby db to physical standby.

SQL> alter database convert to physical standby;

Database altered.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE        OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY      MOUNTED

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> shut immediate

ORA-01109: database not open

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

Total System Global Area     243268216 bytes
Fixed Size                     8895096 bytes
Variable Size                180355072 bytes
Database Buffers              50331648 bytes
Redo Buffers                   3686400 bytes
Database mounted.
Database opened.
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

Step:11 Hence we converted our snapshot standby to physical standby, the DML operations that we have done in snapshot standby cannot be retrieved here.

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

How to change the db name using nid utility

Introduction to Nid Utility:

NID is an utility which is used to change the database internal name and dbname of a database. The utility is placed in the $ORACLE_HOME/bin directory. Prior to the nid utility we need to manually create a database and give it a new database name by recreating a control file.

Here I am going to change the db name from standby to Canada.

Overview steps:

Step:1 Mount the database with old db name(standby)

Step:2 Run the nid utility (syntax: nid sys/password@CURRENT_DBNAME DBNAME=NEW_DBNAME)

Step:3 Once you run the nid utility the name will be changed to new db name.(standby to Canada)

Step:4 Then you have to change the db_name in the parameter file.

Step:5 change the spfile to a new db name.

Step:6 Now open the database with reset logs option.

Step:1 mount the database with old db name.

Step:2 Run the nid utility

Syntax: nid sys/password@CURRENT_DBNAME DBNAME=NEW_DBNAME

Step:3 change the db_name in the parameter file.

Step:4 Rename the spfile to a new db name.

cp spfileSTANDBY.ora spfileCANADA.ora

Step:5 Open the database with resetlogs option.

Step:6 Register the database information to the listener using the command “alter system register;”

Upgrade Oracle Database Manually from 12.2 to 19c

An another way to upgrade a database from lower version to higher version is we have to manually upgrade a database (ie) we have to verify whether it meets all the pre-requests manually instead of using dbua (GUI) / datapump.

Step: 1 Create directory backup, upgrade for taking backup of listener.ora , tnsnames.ora and running the preupgrade.jar files respectively.

Backup TNS Files listener spfile and passwordfile .

[oracle@orcldbs ~]$ mkdir -p /u01/backup

[oracle@orcldbs u01]$ cd backup/
[oracle@orcldbs backup]$ ll
total 20
-rwxr-xr-x. 1 oracle oinstall 343 Jan 30 23:32 listener21013011PM3657.bak
-rwxr-xr-x. 1 oracle oinstall 555 Jan 30 23:32 listener.ora
-rwxr-xr-x. 1 oracle oinstall 191 Jan 30 23:32 sqlnet21013011PM3657.bak
-rwxr-xr-x. 1 oracle oinstall 191 Jan 30 23:32 sqlnet.ora
-rwxr-xr-x. 1 oracle oinstall 614 Jan 30 23:32 tnsnames.ora
[oracle@orcldbs backup]$

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

==================
PREUPGRADE SUMMARY
==================

  /u01/upgrade/preupgrade.log
  /u01/upgrade/preupgrade_fixups.sql
  /u01/upgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

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

After the upgrade:
Log into the database and execute the postupgrade fixups
@/u01/upgrade/postupgrade_fixups.sql

Step:2 Login to the database & Run the preupgrade_fixups.sql

Step:3 Gather the dictionary statistics it should be gathered before and after performing the upgrade.

SET ECHO ON; 
SET SERVEROUTPUT ON; 
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; 

 PL/SQL procedure successfully completed.

Step:4 Purge the Recyclebin,it gains some storage space and on other hand we have choosen the recovery options.

SQL>  PURGE DBA_RECYCLEBIN; 

DBA Recyclebin purged

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

SQL>  declare 
list_failures integer(3) :=0; 
begin 
DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE); 
end; 
/ 

PL/SQL procedure successfully completed.

Step:6 Verify archive log mode and enable it ,if not

SQL> archive log list
Database log mode                No Archive Mode
Automatic archival                  Disabled
Archive destination                /u01/app/oracle/product/12.2.0/db_1/dbs/arch
Oldest online log sequence          1
Current log sequence                1

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

SQL> startup mount
ORACLE instance started.
Total System Global Area 2432696320 bytes
Fixed Size                                8623592 bytes
Variable Size                           654314008 bytes
Database Buffers                       1761607680 bytes
Redo Buffers                              8151040 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> archive log list
Database log mode                Archive Mode
Automatic archival                  Enabled
Archive destination                /u01/app/oracle/product/12.2.0/db_1/dbs/arch
Oldest online log sequence          1
Next log sequence to archive        1
Current log sequence                1

SQL> show parameter rec

NAME                                                 TYPE           VALUE
------------------------------------ ----------- ------------------------------

buffer_pool_recycle                                 string
control_file_record_keep_time                       integer            7
db_recovery_file_dest                               string
db_recovery_file_dest_size                        big integer          0
db_recycle_cache_size                             big integer          0
db_unrecoverable_scn_tracking                       boolean          TRUE
ldap_directory_access                               string           NONE
ldap_directory_sysauth                              string            no
recovery_parallelism                                integer            0
recyclebin                                          string            on
remote_recovery_file_dest                           string

In the Above snippet db_recovery_file_dest,db_recovery_file_dest_size is not set, here I am setting the db_recovery_file_dest_size to 10g and db_recovery_file_dest=’/u01/app/oracle/fra’

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fra' scope=both;

System altered.

SQL> show parameter rec

NAME                                                 TYPE          VALUE
------------------------------------ ----------- ------------------------------

buffer_pool_recycle                                 string
control_file_record_keep_time                       integer            7
db_recovery_file_dest                                string
db_recovery_file_dest_size                         big integer        10
db_recycle_cache_size                              big integer         0
db_unrecoverable_scn_tracking                       boolean          TRUE
ldap_directory_access                               string           NONE
ldap_directory_sysauth                              string            no
recovery_parallelism                                integer            0
recyclebin                                          string            on
remote_recovery_file_dest                           string

Step:7 Create Flashback Guaranteed Restore Point in the name pre_upgrade.

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
--------- -------------------- ------------
AUSTRALIA   READ WRITE           ARCHIVELOG

SQL> show parameter db_recovery_file_dest_size

NAME                                     TYPE               VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size            big integer            10G

SQL> create restore point pre_upgrade guarantee flashback database;

Restore point created.

Step:8 Copy init and password files from 12c home to 19c home

[oracle@orcldbs ~]$ cd $ORACLE_HOME/dbs
[oracle@orcldbs dbs]$ cp spfileaustralia.ora orapwaustralia 
/u01/app/oracle/product/19.0.0/dbhome_1/dbs
oracle@orcldbs dbs]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/dbs
[oracle@orcldbs dbs]$ ls
hc_oraprod.dat  lkORAPROD        orapworaprod      spfileaustralia.ora 
init.ora        orapwaustralia  snapcf_oraprod.f  spfileoraprod.ora

Step:9 Startup DB in Upgrade mode from 19c home

[oracle@orcldbs ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 31 01:24:40 2021
Version 19.3.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 2432695144 bytes
Fixed Size                                8899432 bytes
Variable Size                           536870912 bytes
Database Buffers                       1879048192 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
--------- -------------------- --- ----------------- ------------
AUSTRALIA         READ WRITE   NO    19.0.0.0.0       OPEN MIGRATE

Step:10 Run dbupgrade, using $ORACLE_HOME/bin/dbupgrade file (or) by catupgrade(catupgrd.sql). Here starts the manual upgrade process.

Database upgrade completed.

Step:11 Startup DB from 19c home

[oracle@orcldbs whileupgrade]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 31 02:26:12 2021
Version 19.3.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 2432695144 bytes
Fixed Size                                8899432 bytes
Variable Size                           637534208 bytes
Database Buffers                       1778384896 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
--------- -------------------- --- ----------------- ------------
AUSTRALIA      READ WRITE        NO   19.0.0.0.0           OPEN

Step:-12. Run catcon.pl(it is used used to execute the command in all PDBs and CDB database) to start utlrp.sql to recompile invalid objects.

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

  COUNT(*)
----------
    2552

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

Session altered.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN                   2021-01-31 02:30:35

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                    2021-01-31 02:34:29

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






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

ERRORS DURING RECOMPILATION
---------------------------
          0
                                                 
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.

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

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

Step:13 Upgrade the time zone by running the following scripts,

SQL> @?/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> @?/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 2432695144 bytes
Fixed Size                                8899432 bytes
Variable Size                        637534208 bytes
Database Buffers             1778384896 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 2432695144 bytes
Fixed Size                                8899432 bytes
Variable Size                           637534208 bytes
Database Buffers                       1778384896 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_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv32 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.
Session altered.

SQL> SELECT version FROM v$timezone_file;

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

1 row selected.

Step:14 Run utlusts.sql ,this script reads the view dba_registry_log and displays the upgrade results for the database components.

SQL>  @?/rdbms/admin/utlusts.sql text
Oracle Database Release 19 Post-Upgrade Status Tool    01-31-2021 02:43:1
Database Name: AUSTRALIA
Component                                                        Current           Full       Elapsed Time
Name                                                              Status         Version        HH:MM:SS
Oracle Server                                                      VALID      19.3.0.0.0        00:18:34
JServer JAVA Virtual Machine                                       VALID      19.3.0.0.0        00:01:34
Oracle XDK                                                         VALID      19.3.0.0.0        00:00:47
Oracle Database Java Packages                                      VALID      19.3.0.0.0        00:00:13
OLAP Analytic Workspace                                            VALID      19.3.0.0.0        00:00:12
Oracle Label Security                                              VALID      19.3.0.0.0        00:00:06
Oracle Database Vault                                              VALID      19.3.0.0.0        00:00:20
Oracle Text                                                        VALID      19.3.0.0.0        00:00:31
Oracle Workspace Manager                                           VALID      19.3.0.0.0        00:00:27
Oracle Real Application Clusters                              OPTION OFF      19.3.0.0.0        00:00:00
Oracle XML Database                                                VALID      19.3.0.0.0        00:01:31
Oracle Multimedia                                                  VALID      19.3.0.0.0        00:00:47
Spatial                                                            VALID      19.3.0.0.0        00:05:33
Oracle OLAP API                                                    VALID      19.3.0.0.0        00:00:10
Datapatch                                                                                       00:03:27

Final Actions                                                                                   00:03:35
Post Upgrade                                                                                    00:00:17
Post Compile                                                                                    00:03:53

Total Upgrade Time: 00:39:14

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

Step:15 Drop Restore point as we have successfully completed the manual upgradation.

SQL> drop restore point PRE_UPGRADE;

Restore point dropped.

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

no rows selected

Step:16 Set COMPATIBALE parameter value to 19.0.0 from 12.2.0

SQL> show parameter COMPATIBLE

NAME                                     TYPE                  VALUE
------------------------------------ ----------- ------------------------------

compatible                              string                12.2.0
noncdb_compatible                       boolean               FALSE


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

System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started
Total System Global Area 2432695144 bytes
Fixed Size                                8899432 bytes
Variable Size                           637534208 bytes
Database Buffers                       1778384896 bytes
Redo Buffers                              7876608 bytes
Database mounted.
Database opened.

SQL> show parameter COMPATIBLE

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

Step:17 Verify DBA_REGISTRY

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; 
SQL>

COMP_ID                                             COMP_NAME                  VERSION              STATUS

------------------------------ ----------------------------------------    --------------- --------------------------------------------
CATALOG                                   Oracle Database Catalog Views        19.0.0.0.0             VALID
CATPROC                                    Oracle Database Packages and Types  19.0.0.0.0             VALID
JAVAVM                                      JServer JAVA Virtual Machine       19.0.0.0.0             VALID
XML                                         Oracle XDK                         19.0.0.0.0             VALID
CATJAVA                                     Oracle Database Java Packages      19.0.0.0.0             VALID
APS                                         OLAP Analytic Workspace            19.0.0.0.0             VALID
RAC                                        Oracle Real Application Clusters    19.0.0.0.0        OPTION OFF
XDB                                        Oracle XML Database                 19.0.0.0.0             VALID
OWM                                        Oracle Workspace Manager            19.0.0.0.0             VALID
CONTEXT                                    Oracle Text                         19.0.0.0.0             VALID
ORDIM                                      Oracle Multimedia                   19.0.0.0.0             VALID
SDO                                        Spatial                             19.0.0.0.0             VALID                                 
XOQ                                        Oracle OLAP API                     19.0.0.0.0             VALID
OLS                                        Oracle Label Security               19.0.0.0.0             VALID
DV                                         Oracle Database Vault               19.0.0.0.0             VALID







15 rows selected.

Step:18 Add TNS Entries in 19c TNS home

[oracle@orcldbs admin]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 31-JAN-2021 02:56:33
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
TNS-01106: Listener using listener name LISTENER has already been starte
[oracle@orcldbs admin]$ lsnrctl stop
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 31-JAN-2021 02:56:38
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521)))
The command completed successfully
[oracle@orcldbs admin]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 31-JAN-2021 02:56:41
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/orcldbs/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.26)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                31-JAN-2021 02:56:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orcldbs/listener/alert/log.xml
Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.26)(PORT=1521)))
Services Summary...

Service "australia" has 1 instance(s).
  Instance "australia", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@orcldbs admin]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 31-JAN-2021 02:56:50
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                31-JAN-2021 02:56:41
Uptime                    0 days 0 hr. 0 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orcldbs/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.26)(PORT=1521)))
Services Summary...
Service "australia" has 1 instance(s).
  Instance "australia", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Step:19 Edit oratab,Put an entry of current database in the vi /etc/oratab file.

[oracle@orcldbs admin]$ cat /etc/oratab | grep -i australia
australia:/u01/app/oracle/product/12.2.0/db_1:N
[oracle@orcldbs admin]$ cat /etc/oratab | grep -i australia
australia:/u01/app/oracle/product/19.0.0/dbhome_1:Y
australia:/u01/app/oracle/product/12.2.0/db_1:N

How to Create SSH Keys Using Oracle Cloud Shell

The SSH (Secure Shell) protocol is a method for secure remote login from one computer to another. SSH enables secure system administration and file transfers over insecure networks using encryption to secure the connections between endpoints. SSH keys are an important part of securely accessing Oracle Cloud Infrastructure compute instances in the cloud.

Oracle always recommend you use the Oracle Cloud Shell to interface with the OCI compute instance you will create. Oracle Cloud Shell is browser-based, does not require installation or configuration of software on your laptop, and works independently of your network setup.

To use the Cloud Shell machine, your tenancy administrator must grant the required IAM (Identity and Access Management) policy.

To start the Oracle Cloud shell, go to your Cloud console and click the cloud shell icon at the top right of the page.

Once the cloud shell has started, Choose the key name, This will be the keyname you will use to connect to any compute instances you create. Press Enter twice for no passphrase.

mkdir .ssh

cd .ssh

ssh-keygen -b 2048 -t rsa -f <<sshkeyname>>

Please find the below notes which I has done.

osamaobama@cloudshell:~ (us-phoenix-1)$ mkdir .ssh
osamaobama@cloudshell:~ (us-phoenix-1)$ cd .ssh
osamaobama@cloudshell:.ssh (us-phoenix-1)$ ssh-keygen -b 2048 -t rsa -f cloudshellkey
Generating public/private rsa key pair.
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in cloudshellkey.
Your public key has been saved in cloudshellkey.pub.
The key fingerprint is:
SHA256:VJLFZbU6v2PHzCJtWKHNEgV8UH7RGpFXny63PvXDatM osamaobama@5e3feabe028c
The key's randomart image is:
+---[RSA 2048]----+
| .+oo==+++|
| .o....=.*|
| . oo=o|
| . ..+. |
| S o* + |
| oo* o|
| =+=o|
| ooBE*|
| .=+=o|
+----[SHA256]-----+
osamaobama@cloudshell:.ssh (us-phoenix-1)$ ls
cloudshellkey cloudshellkey.pub
osamaobama@cloudshell:.ssh (us-phoenix-1)$ cat cloudshellkey.pub 
ssh-rsa AAAAB3NzaC1yc**********************************************
osamaobama@cloudshell:.ssh (us-phoenix-1)$

How to Create a Compartment in Oracle Cloud Infrastructure (OCI)

What is a compartment in OCI ?

A compartment is a collection of related resources (such as cloud networks, compute instances, or block volumes) that can be accessed only by those groups that have been given permission by an administrator in your organization.

Steps to create compartment in OCI :

Go to the Navigation menu.
Under Governance and Administration, select Identity, then Compartments.A list of the compartments in your tenancy is displayed.
Select the compartment in which you want to create your instance or create a new compartment.
To create a new compartment:
1. Click Create Compartment to create the compartment to use for creating an instance.
2. Enter the following:
  - Name: Enter a name that is unique across all compartments in your tenancy (maximum 100 characters, including letters, numbers, periods, hyphens, and underscores). For example, enter a name such as OICCompartment.
  - Description: Enter a description for this compartment.
  - Tags: Enter tags to organize and list resources based on your business needs.
3. Click Create Compartment.
  Return to the navigation pane.

Login to Oracle Cloud Infrastructure to create compartment. Select Compartment Menu by Clicking on Navigation pane, hover over Identity option and click on Compartments.

Click on the Create Compartment button. Once inside Compartments menu, you can see Create Compartment button at top. Click on it to create new compartment.

Fill in the compartment details (Enter the compartment name and description. If you want to make it a child of an existing compartment, select the relevant parent compartment, otherwise accept the default root container) and click on Create Compartment button at left bottom of the screen.

Once you should be able to see your newly created compartment in the list of compartments. when you click in create compartment button.

We can see root compartment contains one sub compartment as we named as Galaxy.By clicking the compartment we get details of compartment like OCID.

If we need to create a instance under the compartment which we created we need to select compartment as I highlighted in left corner. Then we need to create instance by clicking create instance button.

In this document we saw about how we can create compartment.

19c GRID UPGRADE

Oracle Grid Infrastructure Upgrade:

We can do upgrade Oracle Grid Infrastructure below ways:

Rolling Upgrade which involves upgrading individual nodes without stopping Oracle Grid Infrastructure on other nodes in the cluster.
Non-rolling Upgrade which involves bringing down all the nodes except one. A complete cluster outage occurs while the root script stops the old Oracle Clusterware stack and starts the new Oracle Clusterware stack on the node where you initiate the upgrade. After upgrade is completed, the new Oracle Clusterware is started on all the nodes.

Note that some services are disabled when one or more nodes are in the process of being upgraded. All upgrades are out-of-place upgrades, meaning that the software binaries are placed in a different Grid home from the Grid home used for the prior release.

When you upgrade to Oracle Grid Infrastructure 19c, you upgrade to an Oracle Standalone Cluster configuration.
You can use either Oracle ASM or a shared file system to store OCR and voting files on Oracle Standalone Cluster deployments. If storage for OCR and voting files is other than Oracle ASM on other cluster types, then you need to migrate OCR and voting files to Oracle ASM before upgrading to Oracle Grid Infrastructure 19c.

Restrictions for Oracle Grid Infrastructure Upgrades:

Oracle Grid Infrastructure upgrades are always out-of-place upgrades. You cannot perform an in-place upgrade of Oracle Grid Infrastructure to existing homes.
The same user that owned the earlier release Oracle Grid Infrastructure software must perform the Oracle Grid Infrastructure 19c upgrade.
Oracle ASM and Oracle Clusterware both run in the Oracle Grid Infrastructure home.
When you upgrade Oracle Grid Infrastructure, you upgrade to an Oracle Flex Cluster configuration.
Do not delete directories in the Grid home. For example, do not delete the directory Grid_home/OPatch. If you delete the directory, then the Grid infrastructure installation owner cannot use OPatch utility to patch the grid home, and OPatch displays the error message “‘checkdir’ error: cannot create Grid_home/OPatch”.
To upgrade existing Oracle Grid Infrastructure installations to Oracle Grid Infrastructure 19c, you must first verify if you need to apply any mandatory patches for upgrade to succeed.Oracle recommends that you use the Cluster Verification Utility tool (CVU) to check if there are any patches required for upgrading your existing Oracle Grid Infrastructure or Oracle RAC database installations. See Using CVU to Validate Readiness for Oracle Clusterware Upgrades for steps to check readiness.
The software in the 19c Oracle Grid Infrastructure home is not fully functional until the upgrade is completed. Running srvctl, crsctl, and other commands from the new Grid homes are not supported until the final rootupgrade.sh script is run and the upgrade is complete across all nodes.To manage databases in existing earlier release database homes during the Oracle Grid Infrastructure upgrade, use the srvctl from the existing database homes.
To upgrade existing Oracle Clusterware installations to Oracle Grid Infrastructure 19c cluster, your release must be greater than or equal to Oracle Grid Infrastructure 11g Release 2 (11.2.0.4).

Upgrading Shared Grid Homes

If the existing Oracle Clusterware home is a shared home, then you can use a non-shared home for the Oracle Grid Infrastructure for a cluster home for Oracle Clusterware and Oracle ASM 19c.
You can perform upgrades on a shared Oracle Clusterware home.

Check current Oracle Cluster ware installation readiness for upgrades using Cluster

Verification Utility (CVU)

From the 19c Grid Infrastructure home execute:

[oracle@rac01 grid]$ ./runcluvfy.sh stage -pre hacfg

Verifying Physical Memory ...PASSED
Verifying Available Physical Memory ...PASSED
Verifying Swap Size ...PASSED
Verifying Free Space: rac01:/usr,rac01:/etc,rac01:/sbin ...PASSED
Verifying Free Space: rac01:/var ...PASSED
Verifying Free Space: rac01:/tmp ...PASSED
Verifying User Existence: oracle ...
Verifying Users With Same UID: 1001 ...PASSED
Verifying User Existence: oracle ...PASSED
Verifying Group Existence: dba ...PASSED
Verifying Group Existence: oinstall ...PASSED
Verifying Group Membership: oinstall(Primary) ...PASSED
Verifying Group Membership: dba ...PASSED
Verifying Run Level ...PASSED
Verifying Architecture ...PASSED
Verifying OS Kernel Version ...PASSED
Verifying OS Kernel Parameter: semmsl ...PASSED
Verifying OS Kernel Parameter: semmns ...PASSED
Verifying OS Kernel Parameter: semopm ...PASSED
Verifying OS Kernel Parameter: semmni ...PASSED
Verifying OS Kernel Parameter: shmmax ...PASSED
Verifying OS Kernel Parameter: shmmni ...PASSED
Verifying OS Kernel Parameter: shmall ...PASSED
Verifying OS Kernel Parameter: file-max ...PASSED
Verifying OS Kernel Parameter: ip_local_port_range ...PASSED
Verifying OS Kernel Parameter: rmem_default ...PASSED
Verifying OS Kernel Parameter: rmem_max ...PASSED
Verifying OS Kernel Parameter: wmem_default ...PASSED
Verifying OS Kernel Parameter: wmem_max ...PASSED
Verifying OS Kernel Parameter: aio-max-nr ...PASSED
Verifying Package: kmod-20-21 (x86_64) ...PASSED
Verifying Package: kmod-libs-20-21 (x86_64) ...PASSED
Verifying Package: binutils-2.23.52.0.1 ...PASSED
Verifying Package: compat-libcap1-1.10 ...PASSED
Verifying Package: libgcc-4.8.2 (x86_64) ...PASSED
Verifying Package: libstdc++-4.8.2 (x86_64) ...PASSED
Verifying Package: libstdc++-devel-4.8.2 (x86_64) ...PASSED
Verifying Package: sysstat-10.1.5 ...PASSED
Verifying Package: gcc-c++-4.8.2 ...PASSED
Verifying Package: ksh ...PASSED
Verifying Package: make-3.82 ...PASSED
Verifying Package: glibc-2.17 (x86_64) ...PASSED
Verifying Package: glibc-devel-2.17 (x86_64) ...PASSED
Verifying Package: libaio-0.3.109 (x86_64) ...PASSED
Verifying Package: libaio-devel-0.3.109 (x86_64) ...PASSED
Verifying Package: nfs-utils-1.2.3-15 ...PASSED
Verifying Package: smartmontools-6.2-4 ...PASSED
Verifying Package: net-tools-2.0-0.17 ...PASSED
Verifying Package: compat-libstdc++-33-3.2.3 (x86_64) ...PASSED
Verifying Package: libxcb-1.11 (x86_64) ...PASSED
Verifying Package: libX11-1.6.3 (x86_64) ...PASSED
Verifying Package: libXau-1.0.8 (x86_64) ...PASSED
Verifying Package: libXi-1.7.4 (x86_64) ...PASSED
Verifying Package: libXtst-1.2.2 (x86_64) ...PASSED
Verifying Users With Same UID: 0 ...PASSED
Verifying Current Group ID ...PASSED
Verifying Root user consistency ...PASSED

Pre-check for Oracle Restart configuration was successful.

CVU operation performed: stage -pre hacfg
Date: Feb 20, 2021 09:41:39 AM
CVU home: /u02/app/oracle/product/19.3.0.0/grid/
User: oracle
[oracle@rac01 grid]$

Start the 19c Grid Infrastructure upgrade:

[oracle@rac01 grid]$ ./gridSetup.sh

In Pre-req we are facing this issue. We need to apply these two patches then only we can go ahead for 19c grid upgrade.

Update opatch version and apply patches 28553832 and 27006180.

Now, we have grid setup in directory /u02/app/grid/product/19.3.0/grid , before proceed with the installation. let’s check current Grid Infrastructure version.

[oracle@rac01 ~]$ crsctl query has releaseversion
Oracle High Availability Services release version on the local node is [12.2.0.1.0]
[oracle@rac01 ~]$ crsctl query has softwareversion
Oracle High Availability Services version on the local node is [12.2.0.1.0]

As we can see, the current version of Grid Infrastructure is 12.2.0.1.0 that is 12cR2.

We can initiate installation wizard by running below command.

Make sure you bring down your all database instance(s) before initiating upgrade. If you are trying to upgrade grid without bringing down database instance

[oracle@rac01 ~]$ /u02/app/grid/product/19.3.0/grid/./gridSetup.sh
Launching Oracle Grid Infrastructure Setup Wizard...

Select Upgrade Oracle Gird Infrastructure, and click NEXT

Select if you’ve configured EM(Cloud Control) agent on you server, else without selecting any option click NEXT.

As we can see, our Grid Infrastructure Home is by default the location where we’ve copied grid setup, verify Oracle base for Grid Infrastructure and click NEXT.

Here you can provide your root credentials which will automatically run root script during installation, optionally you can use sudo as well.

Here If we get warning for Physical Memory in prerequisite check, as Oracle recommends 8 GB of minimum physical memory for Grid Infrastructure. Here we have sufficient memory so let’s click NEXT.

Summary screen, verify your selections and modify if needed, then click NEXT.

Installation has began now.

Here we get the prompt to run root script , If we provided earlier using the credentials It will ask yes or no prompt but in our case we didn’t provided the credentials so we need to run manually here.
Once you run root.sh script below logs will run in our putty session.

[oracle@rac01:+ASM] sudo /u02/app/oracle/product/19.3.0.0/grid/rootupgrade.sh
[sudo] password for oracle:
Performing root user operation.

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u02/app/oracle/product/19.3.0.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: 
/u02/app/oracle/product/19.3.0.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
/u02/app/oracle/crsdata/rac01/crsconfig/roothas_2021-02-20_10-02-12AM.log
2021/02/20 10:02:15 CLSRSC-595: Executing upgrade step 1 of 12: 'UpgPrechecks'.
2021/02/20 10:02:19 CLSRSC-595: Executing upgrade step 2 of 12: 'GetOldConfig'.
2021/02/20 10:02:23 CLSRSC-595: Executing upgrade step 3 of 12: 'GenSiteGUIDs'.
2021/02/20 10:02:23 CLSRSC-595: Executing upgrade step 4 of 12: 'SetupOSD'.
2021/02/20 10:02:23 CLSRSC-595: Executing upgrade step 5 of 12: 'PreUpgrade'.

ASM has been upgraded and started successfully.

2021/02/20 10:03:30 CLSRSC-595: Executing upgrade step 6 of 12: 'UpgradeAFD'.
2021/02/20 10:03:31 CLSRSC-595: Executing upgrade step 7 of 12: 'UpgradeOLR'.
clscfg: EXISTING configuration version 0 detected.
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
2021/02/20 10:03:35 CLSRSC-595: Executing upgrade step 8 of 12: 'UpgradeOCR'.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node rac01 successfully pinned.
2021/02/20 10:03:37 CLSRSC-595: Executing upgrade step 9 of 12: 'CreateOHASD'.
2021/02/20 10:03:39 CLSRSC-595: Executing upgrade step 10 of 12: 'ConfigOHASD'.
2021/02/20 10:03:39 CLSRSC-329: Replacing Clusterware entries in file 
'oracle-ohasd.service'
2021/02/20 10:03:57 CLSRSC-595: Executing upgrade step 11 of 12: 'UpgradeSIHA'.
rac01 2021/02/20 10:04:39 /u02/app/oracle/crsdata/rac01/olr/backup_20210220_100439.olr 
724960844
rac01 2020/08/24 00:18:44 
/u01/app/oracle/product/12.2.0.1/grid/cdata/rac01/backup_20200824_001844.olr 1977687601
2021/02/20 10:04:39 CLSRSC-595: Executing upgrade step 12 of 12: 'InstallACFS'.
2021/02/20 10:07:49 CLSRSC-327: 
Successfully configured Oracle Restart for a standalone server.

And here installation finished.

We have successfully upgraded Oracle Grid Infrastructure 12cR2 to Oracle Grid Infrastructure 19c on our database.

Post checks:

Change ORACLE_HOME pointing to new 19c binaries in bash profile.
If your listener entry is in old ORACLE_HOME change that to new ORACLE_HOME.
Let’s now invoke SQL*Plus and check if we can login to Oracle Grid Infrastructure 19c.

Tag Archives: oracle 12c

DB Upgrade using AutoUpgrade tool:

Advantages of AutoUpgrade Utility:

AutoUpgrade has the following stages,

What is snapshot standby database?

Oracle Grid Infrastructure Upgrade:

Restrictions for Oracle Grid Infrastructure Upgrades:

Upgrading Shared Grid Homes

Start the 19c Grid Infrastructure upgrade:

Post checks: