Upgrade DB from 11.2 to 19.8 Using dbua silent

There was a debate as to whether the parameter -useGRP UPGRADE19C for dbua is necessary where UPGRADE19C is the name for the restore point created prior to upgrading the database.

Although it’s not necessary, it is beneficial for dbua to automate the restore process.

When -useGRP UPGRADE19C is used, restore.sh is created to restore the database using guarantee restore point specified.

If -useGRP is not used, then dbua will not create restore.sh script. While I have not personally tested this, I did check for restore.sh script for a recent upgrade and did not find one.

Why not use dbua to its full potential?

DEMO:

### 11.2 database:
 [oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
 There are no Interim patches installed in this Oracle Home.

 [oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory|grep Database
 Oracle Database 11g                                                  11.2.0.4.0
 [oracle@ol7-112-dg1 ~]$

### 19c database:
 [oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
 31305087;OCW RELEASE UPDATE 19.8.0.0.0 (31305087)
 31281355;Database Release Update : 19.8.0.0.200714 (31281355)
 OPatch succeeded.
 [oracle@ol7-112-dg1 ~]$

### Copy emremove.sql from 19c to 11.2 DB home:
 cp -fv /u01/app/oracle/product/19.3.0.0/db_1/rdbms/admin/emremove.sql /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin

### Remove EM and OLAP:
 set echo on serveroutput on
 @?/rdbms/admin/emremove.sql
 @?/olap/admin/catnoamd.sql
 @?/rdbms/admin/utlrp.sql

### Create guarantee restore point UPGRADE19C:
 [oracle@ol7-112-dg1 ~]$ sqlplus / as sysdba
 SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 21 00:23:56 2020
 Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options

 SQL> @/sf_working/sql/restore_point_upgrade19c.sql
 SQL> drop restore point UPGRADE19C;
 drop restore point UPGRADE19C
 *
 ERROR at line 1:
 ORA-38780: Restore point 'UPGRADE19C' does not exist.

 SQL> alter system set db_recovery_file_dest_size=1m scope=both sid='*';
 System altered.

 SQL> alter system set db_recovery_file_dest_size=9000m scope=both sid='*';
 System altered.

 SQL> select sum(flashback_size)/1024/1024/1024 gb from v$flashback_database_log;
         GB
"----------"

 SQL> select flashback_on from v$database;
 FLASHBACK_ON
"----------"
 NO

 SQL> create restore point UPGRADE19C guarantee flashback database;
 Restore point created.

 SQL> select flashback_on from v$database;
 FLASHBACK_ON
"------------------"
 RESTORE POINT ONLY

 SQL> select name, time, guarantee_flashback_database from v$restore_point order by 1,2;
 NAME                           TIME                                     GUA
"------------------------------ ---------------------------------------- ---"
 UPGRADE19C                     21-NOV-20 12.24.19.000000000 AM          YES

 SQL> select sum(flashback_size)/1024/1024/1024 gb from v$flashback_database_log;
         GB
 .048828125

 SQL> exit
 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 [oracle@ol7-112-dg1 ~]$

### Upgrade DB using dbua silent: -useGRP UPGRADE19C
 [oracle@ol7-112-dg1 ~]$ echo $ORACLE_SID $ORACLE_HOME
 testdb /u01/app/oracle/product/11.2.0.4/dbhome_1
 [oracle@ol7-112-dg1 ~]$ ./run_dbua.sh

 /u01/app/oracle/product/19.3.0.0/db_1/bin/dbua -silent \
 -sid testdb \
 -oracleHome /u01/app/oracle/product/11.2.0.4/dbhome_1 \
 -useGRP UPGRADE19C \
 -recompile_invalid_objects TRUE \
 -upgradeTimezone TRUE \
 -emConfiguration NONE \
 -skipListenersMigration \
 -createListener FALSE \
 -upgrade_parallelism 8 
 Logs directory:  /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM
 Performing Pre-Upgrade Checks…
 PRE- and POST- FIXUP ACTIONS
 /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/upgrade.xml
 /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/preupgrade_fixups.sql
 /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/postupgrade_fixups.sql
 [WARNING] [DBT-20060] One or more of the pre-upgrade checks on the database have resulted into warning conditions that require manual intervention. It is recommended that you address these warnings as suggested before proceeding.
    ACTION: Refer to the pre-upgrade results location for details: /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb
 12% complete
 15% complete
 25% complete
 77% complete
 87% complete
 Database upgrade has been completed successfully, and the database is ready to use.
 100% complete
 [oracle@ol7-112-dg1 ~]$

### DBUA Logs:
 [oracle@ol7-112-dg1 ~]$ ls -l /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/
 total 76340
 -rw-r-----. 1 oracle oinstall        0 Nov 21 00:27 Backup.log
 -rw-r-----. 1 oracle oinstall 48860899 Nov 21 01:03 catupgrd0.log
 -rw-r-----. 1 oracle oinstall  6740107 Nov 21 01:03 catupgrd1.log
 -rw-r-----. 1 oracle oinstall  3759694 Nov 21 01:03 catupgrd2.log
 -rw-r-----. 1 oracle oinstall  5391694 Nov 21 01:03 catupgrd3.log
 -rw-r-----. 1 oracle oinstall  2974948 Nov 21 01:03 catupgrd4.log
 -rw-r-----. 1 oracle oinstall  2127696 Nov 21 01:03 catupgrd5.log
 -rw-r-----. 1 oracle oinstall  3975631 Nov 21 01:03 catupgrd6.log
 -rw-r-----. 1 oracle oinstall  3411705 Nov 21 01:03 catupgrd7.log
 -rw-------. 1 oracle oinstall      528 Nov 21 00:28 catupgrd_catcon_7841.lst
 -rw-r-----. 1 oracle oinstall        0 Nov 21 00:54 catupgrd_datapatch_upgrade.err
 -rw-r-----. 1 oracle oinstall     1306 Nov 21 01:01 catupgrd_datapatch_upgrade.log
 -rw-r-----. 1 oracle oinstall    38676 Nov 21 01:03 catupgrd_stderr.log
 -rw-r-----. 1 oracle oinstall        1 Nov 21 00:27 checksBuffer.tmp
 -rw-r-----. 1 oracle oinstall    41134 Nov 21 00:27 components.properties
 -rwxr-xr-x. 1 oracle oinstall      320 Nov 21 00:27 createSPFile_testdb.sql
 -rw-r-----. 1 oracle oinstall    15085 Nov 21 00:27 dbms_registry_extended.sql
 -rwxr-xr-x. 1 oracle oinstall      120 Nov 21 00:27 grpOpen_testdb.sql
 -rw-r-----. 1 oracle oinstall      942 Nov 21 00:27 init.ora
 -rw-r-----. 1 oracle oinstall       69 Nov 21 00:28 Migrate_Sid.log
 drwxr-x---. 3 oracle oinstall       21 Nov 21 00:27 oracle
 -rw-r-----. 1 oracle oinstall    10409 Nov 21 01:04 Oracle_Server.log
 -rw-r-----. 1 oracle oinstall    14051 Nov 21 00:27 parameters.properties
 -rw-r-----. 1 oracle oinstall     8580 Nov 21 00:27 postupgrade_fixups.sql
 -rw-r-----. 1 oracle oinstall      301 Nov 21 01:10 PostUpgrade.log
 -rw-r-----. 1 oracle oinstall     7884 Nov 21 00:27 preupgrade_driver.sql
 -rw-r-----. 1 oracle oinstall     8514 Nov 21 00:27 preupgrade_fixups.sql
 -rw-r-----. 1 oracle oinstall      443 Nov 21 00:28 PreUpgrade.log
 -rw-r-----. 1 oracle oinstall    99316 Nov 21 00:27 preupgrade_messages.properties
 -rw-r-----. 1 oracle oinstall   457732 Nov 21 00:27 preupgrade_package.sql
 -rw-r-----. 1 oracle oinstall     1464 Nov 21 00:27 PreUpgradeResults.html
 -rwxr-xr-x. 1 oracle oinstall       42 Nov 21 00:27 shutdown_testdb.sql
 -rw-r-----. 1 oracle oinstall    94342 Nov 21 01:10 sqls.log
 -rwxr-xr-x. 1 oracle oinstall       35 Nov 21 00:27 startup_testdb.sql
 -rwxr-xr-x. 1 oracle oinstall     2070 Nov 21 00:27 testdb_restore.sh
 drwxr-x---. 3 oracle oinstall       24 Nov 21 00:27 upgrade
 -rw-r-----. 1 oracle oinstall     5287 Nov 21 01:10 UpgradeResults.html
 -rw-r-----. 1 oracle oinstall     2920 Nov 21 01:09 UpgradeTimezone.log
 -rw-r-----. 1 oracle oinstall    11264 Nov 21 00:27 upgrade.xml
 -rw-r-----. 1 oracle oinstall     1583 Nov 21 01:04 upg_summary_CDB_Root.log
 -rw-r-----. 1 oracle oinstall      115 Nov 21 01:07 Utlprp.log
 [oracle@ol7-112-dg1 ~]$

### Script testdb_restore.sh:
 [oracle@ol7-112-dg1 sql]$ cat /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/testdb_restore.sh
 !/bin/sh

-- Run this Script to Restore Oracle Database Instance testdb
 echo -- Bringing up the database from the source oracle home
 ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1; export ORACLE_HOME
 LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.4/dbhome_1/lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH
 ORACLE_SID=testdb; export ORACLE_SID
 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/sqlplus /nolog @/u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/shutdown_testdb.sql
 echo -- Bringing down the database from the new oracle home
 ORACLE_HOME=/u01/app/oracle/product/19.3.0.0/db_1; export ORACLE_HOME
 LD_LIBRARY_PATH=/u01/app/oracle/product/19.3.0.0/db_1/lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH
 ORACLE_SID=testdb; export ORACLE_SID
 /u01/app/oracle/product/19.3.0.0/db_1/bin/sqlplus /nolog @/u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/shutdown_testdb.sql
 echo -- Removing database instance from new oracle home …
 echo You should Remove this entry from the /etc/oratab: testdb:/u01/app/oracle/product/19.3.0.0/db_1:N
 echo -- Bringing up the database from the source oracle home
 unset LD_LIBRARY_PATH; unset LD_LIBRARY_PATH_64; unset SHLIB_PATH; unset LIB_PATH
 LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.4/dbhome_1/lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH
 ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1; export ORACLE_HOME
 ORACLE_SID=testdb; export ORACLE_SID
 rm /u01/app/oracle/product/19.3.0.0/db_1/dbs/spfiletestdb.ora
 echo You should Add this entry in the /etc/oratab: testdb:/u01/app/oracle/product/11.2.0.4/dbhome_1:Y
 cd /u01/app/oracle/product/11.2.0.4/dbhome_1
 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/sqlplus /nolog @/u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/createSPFile_testdb.sql
 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/sqlplus /nolog @/u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/grpOpen_testdb.sql
 RESTORE_RESULT=$?
 echo -- Execution of restore script for the database TESTDB completed.
 exit $(($RESTORE_RESULT|$?))
 [oracle@ol7-112-dg1 sql]$

### grpOpen_testdb.sql: flashback database to restore point UPGRADE19C;
 [oracle@ol7-112-dg1 ~]$ grep -i upgrade19c /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/*.sql
 /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/grpOpen_testdb.sql:flashback database to restore point UPGRADE19C;
 [oracle@ol7-112-dg1 ~]$

### Restore database back to 11.2:
 [oracle@ol7-112-dg1 ~]$ /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/testdb_restore.sh
 -- Bringing up the database from the source oracle home
 SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 21 04:28:33 2020
 Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 Connected to an idle instance.
 ORACLE instance shut down.
 Disconnected
 -- Bringing down the database from the new oracle home
 SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 21 04:28:33 2020
 Version 19.8.0.0.0
 Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 Connected.
 ORACLE instance shut down.
 Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
 Version 19.8.0.0.0
 -- Removing database instance from new oracle home …
 You should Remove this entry from the /etc/oratab: testdb:/u01/app/oracle/product/19.3.0.0/db_1:N
 -- Bringing up the database from the source oracle home
 You should Add this entry in the /etc/oratab: testdb:/u01/app/oracle/product/11.2.0.4/dbhome_1:Y
 SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 21 04:28:38 2020
 Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 Connected to an idle instance.
 ORACLE instance started.
 Total System Global Area 1603411968 bytes
 Fixed Size                  2253664 bytes
 Variable Size             520096928 bytes
 Database Buffers         1073741824 bytes
 Redo Buffers                7319552 bytes
 File created.
 ORA-01507: database not mounted
 ORACLE instance shut down.
 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 21 04:28:46 2020
 Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 Connected to an idle instance.
 ORACLE instance started.
 Total System Global Area 1603411968 bytes
 Fixed Size                  2253664 bytes
 Variable Size             520096928 bytes
 Database Buffers         1073741824 bytes
 Redo Buffers                7319552 bytes
 Database mounted.
 Flashback complete.
 Database altered.
 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 -- Execution of restore script for the database TESTDB completed.
 [oracle@ol7-112-dg1 ~]$

 

Advertisement

Create 19c Database In Archive Mode Using dbca silent

There were discussions on Twitter about BUG for not being able to create database in Archive Mode using dbca silent and piqued my interest.

Interesting. What version of dbca?

— Cumulus (@mdinh235) November 20, 2020

Here is a quick and dirty test case to demonstrate it was successful for my environment.

Using response file with dbca does not work per Twitter thread but from CLI does.

 --- DB patch level:
 [oracle@ol7-19-lax1 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
 31305087;OCW RELEASE UPDATE 19.8.0.0.0 (31305087)
 31281355;Database Release Update : 19.8.0.0.200714 (31281355)
 OPatch succeeded.
 
 --- Create database in archivelog mode:
 [oracle@ol7-19-lax1 ~]$ dbca -silent   \
   -createDatabase                      \
   -responseFile NO_VALUE               \
   -templateName General_Purpose.dbc    \
   -sid testdb                          \
   -gdbname TESTDB                      \
   -characterSet AL32UTF8               \
   -sysPassword Oracle_4U               \
   -systemPassword Oracle_4U            \
   -createAsContainerDatabase FALSE     \
   -databaseType MULTIPURPOSE           \
   -automaticMemoryManagement FALSE     \
   -totalMemory 2048                    \
   -datafileDestination +DATA           \
   -recoveryAreaDestination +RECO       \
   -redoLogFileSize 50                  \
   -emConfiguration NONE                \
   -sampleSchema FALSE                  \
   -enableArchive TRUE                  \
   -ignorePreReqs
   Prepare for db operation
   10% complete
   Registering database with Oracle Restart
   14% complete
   Copying database files
   43% complete
   Creating and starting Oracle instance
   45% complete
   49% complete
   53% complete
   56% complete
   62% complete
   Completing Database Creation
   68% complete
   70% complete
   71% complete
   Executing Post Configuration Actions
   100% complete
   Database creation complete. For details check the logfiles at:
    /u01/app/oracle/cfgtoollogs/dbca/TESTDB.
   Database Information:
   Global Database Name:TESTDB
   System Identifier(SID):testdb
   Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/TESTDB/TESTDB0.log" for further details. 
 
 --- Logs for dbca:
 [oracle@ol7-19-lax1 ~]$ ls -l /u01/app/oracle/cfgtoollogs/dbca/TESTDB/
 total 21508
 -rw-r-----. 1 oracle oinstall    12131 Nov 20 13:20 cloneDBCreation.log
 -rw-r-----. 1 oracle oinstall      784 Nov 20 13:07 CloneRmanRestore.log
 -rw-r-----. 1 oracle oinstall     1820 Nov 20 13:21 lockAccount.log
 -rw-r-----. 1 oracle oinstall     3578 Nov 20 13:24 postDBCreation.log
 -rw-r-----. 1 oracle oinstall     1436 Nov 20 13:21 postScripts.log
 -rw-r-----. 1 oracle oinstall        0 Nov 20 13:06 rmanUtil
 -rw-r-----. 1 oracle oinstall 18726912 Nov 20 13:07 tempControl.ctl
 -rw-r-----. 1 oracle oinstall      843 Nov 20 13:24 TESTDB0.log
 -rw-r-----. 1 oracle oinstall      843 Nov  6 05:14 TESTDB.log
 -rw-r-----. 1 oracle oinstall  1635418 Nov  6 05:14 trace.log_2020-11-06_04-58-10AM
 -rw-r-----. 1 oracle oinstall  1619098 Nov 20 13:24 trace.log_2020-11-20_01-05-35PM
 
 --- /etc/oratab is automatically updated:
 [oracle@ol7-19-lax1 ~]$ tail /etc/oratab
 Backup file is  /u01/app/oracle/product/19.0.0/dbhome_1/srvm/admin/oratab.bak.ol7-19-lax1.oracle line added by Agent
 +ASM1:/u01/app/19.0.0/grid:N
 hawk1:/u01/app/oracle/product/19.0.0/dbhome_1:N
 testdb:/u01/app/oracle/product/19.0.0/dbhome_1:N
 
 --- Confirm Archive Mode:
 [oracle@ol7-19-lax1 ~]$ . oraenv <<< testdb
 ORACLE_SID = [hawk1] ? The Oracle base remains unchanged with value /u01/app/oracle
 [oracle@ol7-19-lax1 ~]$ sqlplus / as sysdba
 SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 20 13:25:09 2020
 Version 19.8.0.0.0
 Copyright (c) 1982, 2020, Oracle.  All rights reserved.
 Connected to:
 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
 Version 19.8.0.0.0
 OL7-19-LAX1:(SYS@TESTDB:PRIMARY> archive log list
 Database log mode              Archive Mode
 Automatic archival             Enabled
 Archive destination            USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence     31
 Next log sequence to archive   33
 Current log sequence           33
 OL7-19-LAX1:(SYS@TESTDB:PRIMARY> exit
 Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
 Version 19.8.0.0.0
 
 --- Database automatically registered with cluster:
 [oracle@ol7-19-lax1 ~]$ srvctl config database -d testdb
 Database unique name: TESTDB
 Database name: TESTDB
 Oracle home: /u01/app/oracle/product/19.0.0/dbhome_1
 Oracle user: oracle
 Spfile: +DATA/TESTDB/PARAMETERFILE/spfile.315.1056983141
 Password file:
 Domain:
 Start options: open
 Stop options: immediate
 Database role: PRIMARY
 Management policy: AUTOMATIC
 Server pools:
 Disk Groups: DATA,RECO
 Mount point paths:
 Services:
 Type: SINGLE
 OSDBA group: dba
 OSOPER group: oper
 Database instance: testdb
 Configured nodes: ol7-19-lax1
 CSS critical: no
 CPU count: 0
 Memory target: 0
 Maximum memory: 0
 Default network number for database services:
 Database is administrator managed
 [oracle@ol7-19-lax1 ~]$
 
 --- Delete database:
 [oracle@ol7-19-lax1 ~]$ dbca -silent -deleteDatabase -sourceDB testdb
 Enter SYS user password:
 [WARNING] [DBT-19202] The Database Configuration Assistant will delete the Oracle instances and datafiles for your database. All information in the database will be destroyed.
 Prepare for db operation
 32% complete
 Connecting to database
 35% complete
 39% complete
 42% complete
 45% complete
 48% complete
 52% complete
 65% complete
 Updating network configuration files
 68% complete
 Deleting instance and datafiles
 84% complete
 100% complete
 Database deletion completed.
 Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/TESTDB/TESTDB1.log" for further details.
 [oracle@ol7-19-lax1 ~]$

Using emcli to create blackout for rac_database

It’s not possible to create blackout for RAC databases using emctl.

Oracle Enterprise Manager Cloud Control 13c Release 4
 Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
 Blackout start Error : Command-line blackouts on targets spanning multiple agents are not supported.

12c Cloud Control Blackouts: Steps to Create Blackouts from Console UI / emctl / emcli (Doc ID 1386134.1)

It is also not possible to use emctl for creating blackouts against composite targets like Cluster, Cluster Database, Fusion Middleware (FMW) Domains, E-Biz Suite, etc or against Multi-Agent targets such as PDB. since these targets span multiple hosts and the blackout details cannot be propagated to the agents on the other nodes.

For composite targets, the blackout has to be created via the Console UI or the emcli.

https://docs.oracle.com/en/enterprise-manager/cloud-control/enterprise-manager-cloud-control/13.4/emcli/create_blackout.html

Example: creating blackout for RAC database (primary and standby)

db_unique_name - DBAZIP_XXXXRAC   - Primary database
db_unique_name - DBAZIP_XXXXRACDR - Physical standby database

$ $OMS_HOME/bin/emcli login -username=sysman

$ $OMS_HOME/bin/emcli get_targets -targets=rac_database | grep DBAZIP
 1       Up               rac_database          DBAZIP_XXXXRAC.domain.com
 1       Up               rac_database          DBAZIP_XXXXRACDR.domain.com

$OMS_HOME/bin/emcli create_blackout -name="WHATEVER" -reason="switchover" \
-add_targets="DBAZIP_XXXXRAC.domain.com:rac_database" -propagate_targets -schedule="duration:1:00"

$OMS_HOME/bin/emcli create_blackout -name="WHATEVER" -reason="switchover" \
-add_targets="DBAZIP_XXXXRACDR.domain.com:rac_database" -propagate_targets -schedule="duration:1:00"

-propagate_targets
When you specify this option, a blackout for a target of type "host" applies the blackout to all targets on the host, including the Agent. 
This is equivalent to nodelevel in the emctl command. 
Regardless of whether you specify this option, a blackout for a target that is a composite or a group applies the blackout to all members of the composite or group.

Other useful commands:
$OMS_HOME/bin/emcli get_blackouts
$OMS_HOME/bin/emcli get_blackout_targets -name="WHATEVER"
$OMS_HOME/bin/emcli get_blackout_details -name="WHATEVER"
$OMS_HOME/bin/emcli stop_blackout -name="WHATEVER"
$OMS_HOME/bin/emcli delete_blackout -name="WHATEVER"

Troubleshooting ORA-01017 from DGMGRL validate

I was reviewing new 19c Data Guard implementation.

DGMGRL validate is my favorite command to detect any issues.

Using validate, shows issue for one of the databases in Data Guard Configuration.

Apologizes for the funky format, since wordpress changed layout, it’s becoming more difficult to use, but I digress.

--- Here are the errors:

DGMGRL> validate network configuration for all;
ORA-01017: invalid username/password; logon denied

DGMGRL> validate static connect identifier for all;
ORA-01017: invalid username/password; logon denied

--- Check TNS from $DB_HOME:

$ cat tnsnames.ora

FALCON =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.71.242)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FALCON)
)
)

--- Connect using TNS from above SUCCEED.

$ sqlplus sys@FALCON as sysdba

--- Connect using static connect identifier FAILED.

$ sqlplus sys@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.71.242)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=FALCON_DGMGRL)(INSTANCE_NAME=FALCON)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))' as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 10 14:00:29 2020
Version 19.8.0.0.0

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

ORA-01017: invalid username/password; logon denied

--- Check password file from DB_HOME to find file size is different comparing to other host.
--- Create copy for password file and copy from other host to this host.
--- What's strange is if password file was an issue, then why did sqlplus sys@FALCON as sysdba succeeded?
--- Regardless, I like to keep them the same.

$ ls -l orapw*
-rw-r-----. 1 oracle oinstall 4608 Nov 10 14:11 orapwFALCON
-rw-r-----. 1 oracle oinstall 1536 Nov 9 16:45 orapwFALCON.bak

--- Check listener.ora from $GI_HOME and there's there's the problem.
ORACLE_HOME is set to GI vs DB home.

$ diff listener.ora listener.ora.bak
16c16
< (ORACLE_HOME = /u01/product/19c)
---
> (ORACLE_HOME = /u01/grid/19c)

 

Thinking out loud here.

Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)

Note: Static “_DGMGRL” entries are no longer needed as of Oracle Database 12.1.0.2 in Oracle Data Guard Broker configurations.

Since database was upgraded from 11.2 to 19c, existing static connect identifier was carried forward.

It might be better to remove existing configuration and create new configuration without _DGMGL; otherwise, may encounter the same issue for next upgrade to 20c.

Comparing datafiles from ASM DiskGroup

A long time ago, I had blogged about SQL Versus ASMCMD

You may be thinking that I must be crazy for wanting to compare datafiles.

Here’s the story. Oracle database is running on Amazon EBS volume.

The objective is to shutdown existing standby database, take a snapshot of EBS volume, create new volume from snapshot, and present the volume to 2 new standby hosts.

The 2 standby hosts will be added to existing Data Guard configuration.

One of the new standby hosts will be converted to primary database and upgraded to 19c using dbua.

Before taking snapshot, standby database was shutdown and checked for no opened files from ASM diskgroup.

$ asmcmd lsof -G DATA
DB_Name Instance_Name Path
$ asmcmd lsof -G REDO
DB_Name Instance_Name Path
$ asmcmd lsof -G FRA
DB_Name Instance_Name Path
$ asmcmd lsof -G BACKUP
DB_Name Instance_Name Path

So why is it necessary to compare datafiles?

There were issues with mounting the new volume from snapshots and wanted to verify there were no lurking issues.

After all, the 2 new standby databases should be identical.

There are 583 datafiles and how would you have compared them?

$ sdiff -iEZbWBs -w 100 ora01-prod_DATA.txt ora02-prod_DATA.txt
[oracle@ora01-prod ~]$ asmcmd ls DATA/*/ | [oracle@ora02-prod ~]$ asmcmd ls DATA/*/*/*
[oracle@ora01-prod ~]$ | [oracle@ora02-prod ~]$

$ wc -l ora01-prod_DATA.txt; wc -l ora02-prod_DATA.txt
583 ora01-prod_DATA.txt
583 ora02-prod_DATA.txt

I used asmcmd ls DATA/*/*/* , copy output to text file, and compare the text file.

Q.E.D.