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

 

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.

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

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

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

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

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

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

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

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

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

 

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

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.

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

 

Configure Data guard physical Standby

Oracle Dataguard physical Standby configuration in 19c:

Overview steps:

Step:1 we have to enable the archive log mode ,flashback  on primary databases.

Step:2 Then enable the force logging option.By enabling the force logging option it helps us to capture all the changes made in the database and available for recovery in the redo logs.

Step:3 Add the redolog files for standby database,it is required when you are configuring the standby for maximum protection.

Step:4 Add the listener.ora and tnsnames.ora entries in both primary and standby sides.

Step:5 Change the some set of parameters in primary side,you can change these parameters either by sql or directly make changes in the pfile.

Step:6 Move the password file,pfile from primary to standby using scp.(we can also create the pfile in standby side also by having a single parameter db_name in it)

Step:7 create the directory structure in standby similar to the primary side.

Step:8 Start the standby side database in nomount using the pfile.

Step:9 Connect to the RMAN and duplicate the primary database using the command

duplicate target database for standby from active database dorecover nofilenamecheck;

Step:10 Now our standby database is ready for read only purpose.

Step:11 Then connect to the standby database and start the MRP process using alter database recover managed standby database using current logfile disconnect; This command is used to apply the log files from primary to standby.

Step:12 Verify the current log sequence in both primary and standby sides.

Do the following set of changes in primary side:

Oracle Active Data Guard:

Active Data Guard is a licensed option for Oracle Database Enterprise Edition. … Data Guard automatically synchronizes the primary database and all standby databases by transmitting primary database redo – the information used by every Oracle Database to protect transactions – and applying it to the standby database.

Difference between Oracle Data Guard and Active Data Guard?

Oracle Active Data Guard provides the best data protection and availability for Oracle Database. Active Data Guard enables the offloading of read-only operations, backups, and so on, to an up-to-date physical standby database while also providing disaster protection.

Step:1 Enable the archive log  mode in the primary database.

SQL> alter database archivelog;

Database altered
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5

Step:2 Enable the force logging.

SQL>alter database force logging;

Database altered

Step:3 Flashback should be enabled in primary db.

ALTER DATABASE FLASHBACK ON;

Database altered

Check the archive log,force_logging,flashback is enabled.

SQL> select name,force_logging,flashback_on,log_mode from v$database;

   NAME            FORCE_LOGGING       FLASHBACK_ON    LOG_MODE       
--------- ---------------------------  ------------   -------------
CANADA                  YES                  YES       ARCHIVELOG

Step:4 Add the Standby redo log files for standby side.

SQL> alter database add standby logfile thread 1 
group 4('/u01/app/oracle/oradata/redo04.log')size 50m;

Database altered.

SQL> alter database add standby logfile thread 1 
group 5('/u01/app/oracle/oradata/redo05.log')size 50m;

Database altered.

SQL> alter database add standby logfile thread 1 
group 6('/u01/app/oracle/oradata/redo06.log')size 50m;

Database altered.

SQL> alter database add standby logfile thread 1 
group 7('/u01/app/oracle/oradata/redo07.log')size 50m;

Database altered.

Step:5  set the log archive config parameter.

SQL> alter system set log_archive_config='DG_config=(canada,standby)';

System altered.
SQL> show parameter log_archive_config

  NAME                                TYPE                VALUE
--------------------------------- ----------- ------------------------------
log_archive_config                  string      DG_config=(canada,standby)
SQL> show parameter log_archive_dest_2

NAME                                TYPE               VALUE
------------------------------- ----------- ---------------------------
log_archive_dest_2                string    SERVICE=standby NOAFFIRM ASYNC
                                  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
                                                 DB_UNIQUE_NAME=standby
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string

NAME                                TYPE               VALUE
-------------------------------- ----------- -----------------------
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string


Step:6 Set the log archive format = ‘%t_%s_%r.arc ,it determines the name of the archive log file it comes into role when archive log is enabled.

SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

System altered.

Step:7 configure the log_archive_process to 30 ,it prevents to avoid any runtime overhead of invoking additional ARCn process,you can set the LOG_ARCHIVE_MAX_PROCESSES

SQL> alter system set log_archive_max_processes=30;

System altered.

Step:8 register the fal_server and fal_client as standby(standby) and physical(primary) databases respectively.

SQL> alter system set fal_server=standby;

System altered.

SQL> alter system set fal_client=physical;

System altered.

Step:9 The standby_file_management initialization parameter plays an important role in the recovery process.

SQL> alter system set standby_file_management=auto;

System altered.

Step:10 change the remote_login_password file as exclusive.

SQL> show parameter remote_login_passwordfile

    NAME                                TYPE                 VALUE
------------------------------------ ----------- ------------------------
remote_login_passwordfile              string              EXCLUSIVE

Step:11 db_file_name_convert and log_file_name_convert, converts the filename of a new datafile and logfile respectively on the primary database to a filename on the standby database.

SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/canada/'
,'/u01/app/oracle/oradata/standby/' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/canada/'
,'/u01/app/oracle/oradata/standby/' scope=spfile;

System altered.

Step:12 Configure the tns and listener files on both primary and standby databases.

listener entry in the primary database.

tnsnames entry in the primary database.

listener entry in the standby database.

tnsnames entry in the standby database.

Step:13  ping your primary and standby databases from one to another.

Step:14 create the similar directory structures in standby side as of in primary.

/u01/app/oracle/admin/standby/adump

/u01/app/oracle/oradata/standby

/u01/app/oracle/fast_recovery_area/standby

Step:15 Transfer the password files,pfiles  to the standby side through scp.

Step:16 create the initcanada.ora file containing the single parameter db_name=canada in the standby side it is useful to start our database in no mount stage.

Step:17 start the db in nomount stage and connect to the rman to duplicate the target database.

Step:18 Duplicate the target database using the following command,

duplicate target database for standby from active database 
dorecover nofilenamecheck;
[oracle@localhost admin]$ 
rman target sys/Pass#1234@canada auxiliary sys/Pass#1234@standby

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Feb 17 07:28:58 2021
Version 19.3.0.0.0

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

connected to target database: CANADA (DBID=215384084)
connected to auxiliary database: CANADA (not mounted)

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

Starting Duplicate Db at 17-FEB-21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 device type=DISK
current log archived

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

Starting backup at 17-FEB-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=112 device type=DISK
Finished backup at 17-FEB-21

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

Starting restore at 17-FEB-21
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:09
output file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/cntrlcanada.dbf
Finished restore at 17-FEB-21

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

sql statement: alter database mount standby database
RMAN-05158: WARNING: auxiliary (datafile) file name 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_system_j2qt47mc_.dbf 
conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_sysaux_j2qt5yyz_.dbf 
conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_undotbs1_j2qt6r4g_.dbf 
conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_users_j2qt6s6x_.dbf 
conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (tempfile) file name 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_temp_j2qt8zx2_.tmp 
conflicts with a file used by the target database

contents of Memory Script:
{
set newname for tempfile 1 to 
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_temp_j2qt8zx2_.tmp";
switch clone tempfile all;
set newname for datafile 1 to 
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_system_j2qt47mc_.dbf";
set newname for datafile 3 to 
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_sysaux_j2qt5yyz_.dbf";
set newname for datafile 4 to 
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_undotbs1_j2qt6r4g_.dbf";
set newname for datafile 7 to 
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_users_j2qt6s6x_.dbf";
restore
from nonsparse from service 
'canada' clone database
;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_temp_j2qt8zx2_.tmp in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 17-FEB-21
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_system_j2qt47mc_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:09
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_sysaux_j2qt5yyz_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:37
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_undotbs1_j2qt6r4g_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_users_j2qt6s6x_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 17-FEB-21

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
restore clone force from service 'canada' 
archivelog from scn 2091702;
switch clone datafile all;
}
executing Memory Script

Starting restore at 17-FEB-21
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=10
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=11
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 17-FEB-21

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1064734612 file name=
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_system_j2qt47mc_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1064734613 file name=
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_sysaux_j2qt5yyz_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1064734613 file name=
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_undotbs1_j2qt6r4g_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1064734613 file name=
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_users_j2qt6s6x_.dbf

contents of Memory Script:
{
set until scn 2092999;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 17-FEB-21
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 10 is already on disk as file 
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_10_1064697431.dbf
archived log for thread 1 with sequence 11 is already on disk as file 
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_11_1064697431.dbf
archived log file name=
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_10_1064697431.dbf 
thread=1 sequence=10
archived log file name=
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_11_1064697431.dbf 
thread=1 sequence=11
media recovery complete, elapsed time: 00:00:03
Finished recover at 17-FEB-21

contents of Memory Script:
{
delete clone force archivelog all;
}
executing Memory Script

released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=112 device type=DISK
deleted archived log
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/
dbs/arch1_10_1064697431.dbf RECID=1 STAMP=1064734609
deleted archived log
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/
dbs/arch1_11_1064697431.dbf RECID=2 STAMP=1064734611
Deleted 2 objects

Finished duplicate at 17-FEB-21

Yes,we have created the physical standby database successfully.

Step:19 Then mount the database and open the database in the read only mode.check the name,open_mode,database_role in the standby database,in the role it should have the value as physical_standby.

Step:20 Check for the max(sequence#) from the v$archived_log view in both primary and standby databases.

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

MAX(SEQUENCE#)
--------------
       11

Step:21 Apply the log files using MRP process.

SQL> alter database recover managed standby database using current 
logfile disconnect;


Database altered.

Step:22 check for the sequence no which is applied in standby.

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

SEQUENCE#    FIRST_TIM  NEXT_TIME APPLIED
----------   --------- --------- ---------
     10      17-FEB-21 17-FEB-21    YES
     11      17-FEB-21 17-FEB-21    YES

Step:23 We can check the difference in sequence using the following query.

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", 
APPL.SEQUENCE# "Last Sequence Applied", 
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM 
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) 
IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, 
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) 
IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL 
WHERE 
ARCH.THREAD# = APPL.THREAD# 
ORDER BY 1; 
SQL> 

 Thread    Last Sequence Received   Last Sequence Applied  Difference
---------- ----------------------    --------------------- ----------
  1                 11                         11                 0

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