NOTE: Failed voting file relocation on diskgroup

After Oracle support performs maintenance for Exadata Cloud, there are many errors from ASM alert log.

There have been many discussions whether it’s a BUG or if alert can be ignored.

Unfortunately, BUG does not fit and cannot be ignored.

The simple solution is to check for SUCCESS after Failed as shown below.

egrep -n 'NOTE: Failed voting file relocation on diskgroup|SUCCESS: refreshed membership' /u01/app/grid/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log|grep DATAC1
1937:SUCCESS: refreshed membership for 1/0xacd2391b (DATAC1)
1972:SUCCESS: refreshed membership for 1/0xacd2391b (DATAC1)
4244:NOTE: Failed voting file relocation on diskgroup DATAC1
4250:SUCCESS: refreshed membership for 1/0x8f4036a8 (DATAC1)
9876:NOTE: Failed voting file relocation on diskgroup DATAC1
9881:SUCCESS: refreshed membership for 1/0x8f4038d9 (DATAC1)
10130:NOTE: Failed voting file relocation on diskgroup DATAC1
10135:SUCCESS: refreshed membership for 1/0x8f4038d9 (DATAC1)
11112:NOTE: Failed voting file relocation on diskgroup DATAC1
11425:NOTE: Failed voting file relocation on diskgroup DATAC1
11441:SUCCESS: refreshed membership for 1/0x8f4038d9 (DATAC1)
12410:SUCCESS: refreshed membership for 1/0x8f4038d9 (DATAC1)
13318:NOTE: Failed voting file relocation on diskgroup DATAC1
13717:NOTE: Failed voting file relocation on diskgroup DATAC1
13733:SUCCESS: refreshed membership for 1/0x8f4038d9 (DATAC1)
14703:SUCCESS: refreshed membership for 1/0x8f4038d9 (DATAC1)
15566:NOTE: Failed voting file relocation on diskgroup DATAC1
15865:NOTE: Failed voting file relocation on diskgroup DATAC1
15881:SUCCESS: refreshed membership for 1/0x8f4038d9 (DATAC1)
16836:SUCCESS: refreshed membership for 1/0x8f4038d9 (DATAC1)

Want more comfort?

$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   1b45f107ff974f1abf34ff7b005eaff1 (/dev/exadata_quorum/QD_DATAC1_QM828NAKVNXA1) [DATAC1]
 2. ONLINE   b3607b245b984f7ebfe5188c0775c44e (/dev/exadata_quorum/QD_DATAC1_QM738NAKVNXA2) [DATAC1]
 3. ONLINE   5d26163d434a4fb6bf2cb173bae3cae1 (o/192.168.136.14;192.168.136.15/DATAC1_CD_05_phx302307exdcl07) [DATAC1]
 4. ONLINE   3e1661086fed4f8bbf080db321282b23 (o/192.168.136.16;192.168.136.17/DATAC1_CD_04_phx302307exdcl08) [DATAC1]
 5. ONLINE   edac9a7822624fe4bfd59eb357d55d95 (o/192.168.136.18;192.168.136.19/DATAC1_CD_04_phx302307exdcl09) [DATAC1]
Located 5 voting disk(s).

# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     491684
         Used space (kbytes)      :      90748
         Available space (kbytes) :     400936
         ID                       : 1880339001
         Device/File Name         :    +DATAC1
                                    Device/File integrity check succeeded
                                    Device/File not configured
                                    Device/File not configured
                                    Device/File not configured
                                    Device/File not configured
         Cluster registry integrity check succeeded
         Logical corruption check succeeded

Monitor Linux Host Restart

The application is not RAC-aware and cannot handle ORA-3113, ORA-25402, or ORA-25409 properly.

Hence, there is requirement to notify the application team to restart the application when database server is restarted.

Initial implementation to monitor reboot was to use cronjob from oracle running every 5m to detect server restart.

While the implementation is effective, it’s not efficient. This was my first attempt.

The script detects if server was restarted X seconds ago by checking /proc/uptime.

If uptime is less than X seconds, then send notification server was restarted.

Here is high level example:

### Scripts accept paramenter with values for seconds
$ /home/oracle/scripts/last_reboot.sh
/home/oracle/scripts/last_reboot.sh: line 10: 1: ---> USAGE: /home/oracle/scripts/last_reboot.sh [in seconds]

### The heart of the script is to check /proc/uptime in seconds
$ egrep -o '^[0-9]+' /proc/uptime
2132607

### Scheduled cron tab to run every 5 minute to determine if server uptime is less that 540 seconds and send notification.
$ crontab -l|grep reboot
##### monitor node reboot #####
*/5 * * * * /home/oracle/scripts/last_reboot.sh 540 > /tmp/last_reboot.cron 2>&1

A more efficient implementation is to run a cronjob automatically after the server restart.

Here is high level example:

### When server is restarted, host_restart_alert.sh will be executed
[root@oracle-12201-vagrant ~]# crontab -l
@reboot su oracle -c '/home/oracle/host_restart_alert.sh' > /tmp/host_restart_alert.out 2>&1

### Here is host_restart_alert.sh
[oracle@oracle-12201-vagrant ~]$ cat host_restart_alert.sh
#!/bin/bash -x
# Script ie being called from root crontab
# uptime reports minutely and need to sleep for at least 60s after host restart
sleep 63
EMAILMESSAGE="$(hostname) was restarted `uptime -p| awk -F'up' '{print $2}'` ago at `uptime -s`"
echo $EMAILMESSAGE > /tmp/restart_$HOSTNAME.log
exit

### Comment from colleague:
### From a bash syntax perspective, it’s not wrong. It’s not great style (don’t use backticks)
printf -v EMAILMESSAGE '%s was restarted %s ago at %s' \
"$(hostname)" \
"$(uptime -p| awk -F'up' '{print $2}')" \
"$(uptime -s)"
echo $EMAILMESSAGE > /tmp/restart_$HOSTNAME.log

### Deconstructing uptime commands:
[oracle@oracle-12201-vagrant ~]$ uptime -p
up 17 hours, 28 minutes

[oracle@oracle-12201-vagrant ~]$ uptime -s
2021-02-15 18:00:51

### Deconstructing message sent:
[oracle@oracle-12201-vagrant ~]$ echo "$HOSTNAME was restarted `uptime -p| awk -F'up' '{print $2}'` ago at `uptime -s`"
oracle-12201-vagrant was restarted  17 hours, 28 minutes ago at 2021-02-15 18:00:51

### Demo:
[root@oracle-12201-vagrant ~]# date
 Tue Feb 16 14:51:18 -05 2021

[root@oracle-12201-vagrant ~]# uptime
  14:51:22 up 1 min,  1 user,  load average: 0.58, 0.23, 0.08

[root@oracle-12201-vagrant ~]# ls -l /tmp/restart
 -rw-r--r--. 1 root   root     271 Feb 16 14:51 /tmp/host_restart_alert.out
 -rw-r--r--. 1 oracle oinstall  71 Feb 16 14:51 /tmp/restart_oracle-12201-vagrant.log

[root@oracle-12201-vagrant ~]# cat /tmp/host_restart_alert.out
 sleep 63
 ++ hostname
 ++ uptime -p
 ++ awk -Fup '{print $2}'
 ++ uptime -s
 printf -v EMAILMESSAGE '%s was restarted %s ago at %s' oracle-12201-vagrant ' 1 minute' '2021-02-16 14:50:02'
 echo oracle-12201-vagrant was restarted 1 minute ago at 2021-02-16 14:50:02
 exit 

[root@oracle-12201-vagrant ~]# cat /tmp/restart_oracle-12201-vagrant.log
 oracle-12201-vagrant was restarted 1 minute ago at 2021-02-16 14:50:02
[root@oracle-12201-vagrant ~]#

Scripts were tested on Oracle Linux Server release 7.8  and 7.9.

Using sed To Search And Replace

The goal is to replace me@gmail.com with dba@gmail.com for all shell scripts.

Fortunately, all shell scripts are located from one directory; otherwise, will need to find all locations.

Check crontab to find possible directory location for shell scripts.

[vagrant@oracle-12201-vagrant ~]$ crontab -l
5 4 * * * /home/vagrant/scripts/test.sh something > /tmp/test.out 2>&1
[vagrant@oracle-12201-vagrant ~]$

[vagrant@oracle-12201-vagrant ~]$ crontab -l|grep -v '#'|grep sh|awk '{print $6}'|sort -u
/home/vagrant/scripts/test.sh
[vagrant@oracle-12201-vagrant ~]$

Check directory for shell scripts.

[vagrant@oracle-12201-vagrant scripts]$ ls -l
total 12
-rwxrwxr-x. 1 vagrant vagrant  25 Feb  4 21:15 dt.sh
-rwxrwxr-x. 1 vagrant vagrant  20 Feb  4 21:14 test.sh
[vagrant@oracle-12201-vagrant scripts]$

Check shell scripts containing emails to modify.

[vagrant@oracle-12201-vagrant scripts]$ grep 'me@gmail.com' *.sh|grep sh|awk -F':' '{print $1}'|sort -u|grep -v edit_email.sh
dt.sh
test.sh
[vagrant@oracle-12201-vagrant scripts]$

Create edit_email.sh to modify email.

[vagrant@oracle-12201-vagrant scripts]$ cat edit_email.sh
for infile in $(grep 'me@gmail.com' *.sh|grep sh|awk -F':' '{print $1}'|sort -u|grep -v `basename $0`)
do
  echo $infile
  sed 's/\bme@gmail.com\b/dba@gmail.com/g' $infile > tmp.$$
  mv tmp.$$ $infile
  chmod 755 $infile
  grep 'gmail.com' $infile
done
[vagrant@oracle-12201-vagrant scripts]$

Run edit_email.sh and verify results.

[vagrant@oracle-12201-vagrant scripts]$ ./edit_email.sh
dt.sh
echo dba@gmail.com
test.sh
export PAGER_EMAIL="dba@gmail.com"
[vagrant@oracle-12201-vagrant scripts]$

[vagrant@oracle-12201-vagrant scripts]$ grep 'me@gmail.com' *.sh|grep sh|awk -F':' '{print $1}'|sort -u|grep -v edit_email.sh

Here is an improvement for the code thanks to Jared Still

Filter basename before sort.

Use grep -il

[vagrant@oracle-12201-vagrant scripts]$ cat e.sh
for infile in $(grep 'me@gmail.com' *.sh|grep sh|awk -F':' '{print $1}'|grep -v basename $0|sort -u)
do
echo $infile
done
for infile in $(grep -il 'me@gmail.com' *.sh 2>/dev/null | grep -v $(basename $0) | sort -u )
do
echo $infile
done

Remove Characters From String Using regexp_replace

It’s probably have been half a century since I have coded PL/SQL.

So there I was, reviewing PL/SQL code and it looks rather redundant.

Did you know there is defined variable USER for PL/SQL code and using sys_context is not necessary?

Also, regexp_replace can be used to remove all characters from string.

Here is an example:

Basically, one line of code replaces all the redundant code.

I have no idea why there is a need to extract characters.

DINH099PD@ORCLPDB1 > show user
USER is "DINH099PD"
DINH099PD@ORCLPDB1 > @dinh.sql
DINH099PD@ORCLPDB1 > DECLARE
  2    -- Remove character from l_user
  3    l_user VARCHAR2(30) := regexp_replace(USER, '[[:alpha:]]|_');
  4    l_user2 varchar2(20);
  5    l_output varchar2(100);
  6  BEGIN
  7    -- Remove character from l_user2
  8    l_user2 := sys_context('USERENV', 'CURRENT_USER');
  9    l_output := replace(replace(l_user2, 'DINH', ''),'PD', '');
 10    dbms_output.put_line (l_user);
 11    dbms_output.put_line (l_output);
 12  END;
 13  /
099
099

PL/SQL procedure successfully completed.

DINH099PD@ORCLPDB1 >

DBSAT Error: Data collection was not successful.

First time running DBSAT for 19.8 RAC CDB and it failed miserably.

Create TNS entry for PDB and connect as system@PDB_service failed.

Many have tried and failed and thanks to Roy Salazar who came up with a work around.

Here is a demo for error and work around.

### Error: Data collection was not successful.
$ export TNS_ADMIN=/u01/app/grid/19.0/network/admin
$ ./dbsat collect  "/ as sysdba" dbsat_$ORACLE_SID
Setup complete.
SQL queries complete.
OS commands complete.
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
Error: Data collection was not successful.

### There should have been dbsat_$ORACLE_SID.json; however, nowrap.json exist due to error.
$ ls -l *.json
-rw-------. 1 oracle oracle 486835 Dec  5 04:34 nowrap.json

### Create report using nowrap which will create nowrap_report.zip and rename nowrap_report.zip accordingly. Unfortunately, the report contents are still named nowrap.
$ ./dbsat report nowrap
 Database Security Assessment Tool version 2.2.1 (May 2020)
 This tool is intended to assist you in securing your Oracle database
 system. You are solely responsible for your system and the effect and
 results of the execution of this tool (including, without limitation,
 any damage or data loss). Further, the output generated by this tool may
 include potentially sensitive system configuration data and information
 that could be used by a skilled attacker to penetrate your system. You
 are solely responsible for ensuring that the output of this tool,
 including any generated reports, is handled in accordance with your
 company's policies.
 DBSAT Reporter ran successfully.
 Calling /usr/bin/zip to encrypt the generated reports…
 Enter password:
 Verify password:
   zip warning: nowrap_report.zip not found or empty
   adding: nowrap_report.txt (deflated 77%)
   adding: nowrap_report.html (deflated 83%)
   adding: nowrap_report.xlsx (deflated 3%)
   adding: nowrap_report.json (deflated 81%)
 zip completed successfully.

$ ls -ltrh
 total 6.1M
 -r-xr-xr-x. 1 oracle oracle  14K May  6  2020 dbsat
 -rw-rw-r--. 1 oracle oracle 290K May  6  2020 sat_reporter.py
 -rw-rw-r--. 1 oracle oracle  61K May  6  2020 sat_collector.sql
 -rw-rw-r--. 1 oracle oracle  26K May  6  2020 sat_analysis.py
 -r-xr-xr-x. 1 oracle oracle  14K May  6  2020 dbsat.bat
 -rw-r--r--. 1 oracle oracle 4.5M Dec  3 04:33 dbsat.zip
 drwxr-xr-x. 5 oracle oracle   40 Dec  3 04:33 Discover
 -rw-------. 1 oracle oracle  26K Dec  4 09:40 sat_analysis.pyc
 drwxr-xr-x. 2 oracle oracle 4.0K Dec  4 09:40 xlsxwriter
 -rw-------. 1 oracle oracle 476K Dec  5 04:34 nowrap.json
 -rw-------. 1 oracle oracle 105K Dec  5 04:36 nowrap_report.zip

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 ~]$

 

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.