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:1Get the value of standby_became_primary_scn from the new primary db.
Step:2Flashback the old primary db to this scn value.
Step:3Convert the primary db into physical standby, then bounce the database.
Step:4Now 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
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:1Get the value of standby_became_primary_scn from the new primary db.
Step:2Flashback the old primary db to this scn value.
Step:3Convert the primary db into physical standby, then bounce the database.
Step:4Now 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
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:3Here 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
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.
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 note2485457.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.
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.
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
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:4Find 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:13verify 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.
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
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:3Enable 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_databaseinformation’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
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:1Mount the database with old db name(standby)
Step:2 Run the nid utility (syntax: nid sys/password@CURRENT_DBNAME DBNAME=NEW_DBNAME)
Step:3Once 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
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
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';
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>#
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.
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 ------------------------------------ ----------- ------------------------------
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>
[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
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
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
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
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.
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
