Michael Dinh

Subscribe to Michael Dinh feed Michael Dinh
Michael T. Dinh, Oracle DBA
Updated: 11 hours 13 min ago

Purge Database Audit Trail Table

Wed, 2021-03-03 23:12

Segment for AUD$/FGA_LOG$ tables reside in SYSAUX tablespace and will be moved AUDIT_TBS before configuring purge.

The requirement is to purge audits older than 7 years (366*7=2562) [l_days NUMBER := 2562]

DEMO:

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 3 18:22:35 2021

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

18:22:35 SYS@DB01 AS SYSDBA> select file_name from dba_data_files where tablespace_name='SYSAUX';

FILE_NAME
--------------------------------------------------------------------------------
/oradata/db01/datafile/sysaux_01.dbf

Elapsed: 00:00:00.01
18:22:44 SYS@DB01 AS SYSDBA> create tablespace AUDIT_TBS datafile '&location/audit_tbs_01.dbf' size 5g autoextend on maxsize 30g;
Enter value for location: /oradata/db01/datafile
old   1: create tablespace AUDIT_TBS datafile '&location/audit_tbs_01.dbf' size 5g autoextend on maxsize 30g
new   1: create tablespace AUDIT_TBS datafile '/oradata/db01/datafile/audit_tbs_01.dbf' size 5g autoextend on maxsize 30g

Tablespace created.

Elapsed: 00:00:24.68
18:24:29 SYS@DB01 AS SYSDBA> @ audit.sql
18:24:37 SYS@DB01 AS SYSDBA>
18:24:37 SYS@DB01 AS SYSDBA> select owner, segment_name, segment_type, tablespace_name, round(bytes/1024/1024,2) USED_MB
18:24:37   2  from dba_segments where segment_name IN ('AUD$','FGA_LOG$') order by 2
18:24:37   3  ;

OWNER                SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME         USED_MB
-------------------- -------------------- ------------------ -------------------- ----------
SYS                  AUD$                 TABLE              SYSTEM                     2946
SYS                  FGA_LOG$             TABLE              SYSTEM                      .06

Elapsed: 00:00:00.09
18:24:37 SYS@DB01 AS SYSDBA> select MIN(TIMESTAMP), MAX(TIMESTAMP), TRUNC(systimestamp)-MIN(TIMESTAMP) diff_day, COUNT(*)
18:24:37   2  from dba_audit_trail
18:24:37   3  ;

MIN(TIMES MAX(TIMES   DIFF_DAY   COUNT(*)
--------- --------- ---------- ----------
23-SEP-14 03-MAR-21 2352.58642   20801590

Elapsed: 00:00:53.32
18:25:30 SYS@DB01 AS SYSDBA> select * from dba_audit_mgmt_config_params order by 1
18:25:30   2  ;

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                FGA AUDIT TRAIL
DB AUDIT TABLESPACE            SYSAUX               FGA AUDIT TRAIL
DB AUDIT TABLESPACE            SYSAUX               STANDARD AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 XML AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 OS AUDIT TRAIL

10 rows selected.

Elapsed: 00:00:00.01
18:25:30 SYS@DB01 AS SYSDBA> select * from dba_audit_mgmt_last_arch_ts
18:25:30   2  ;

no rows selected

Elapsed: 00:00:00.00
18:25:30 SYS@DB01 AS SYSDBA> exec dbms_audit_mgmt.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD, AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_TBS')

PL/SQL procedure successfully completed.

Elapsed: 00:09:17.79
18:34:48 SYS@DB01 AS SYSDBA> ;
  1  select * from dba_audit_mgmt_last_arch_ts
  2*
18:34:48 SYS@DB01 AS SYSDBA> exec dbms_audit_mgmt.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_FGA_STD, AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_TBS')

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.83
18:34:56 SYS@DB01 AS SYSDBA> ;
  1  select * from dba_audit_mgmt_last_arch_ts
  2*
18:34:56 SYS@DB01 AS SYSDBA>
18:34:56 SYS@DB01 AS SYSDBA> begin
18:34:56   2    dbms_audit_mgmt.INIT_CLEANUP (
18:34:56   3      audit_trail_type         => dbms_audit_mgmt.audit_trail_all,
18:34:56   4      default_cleanup_interval => 24);
18:34:56   5  end;
18:34:56   6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.21
18:35:01 SYS@DB01 AS SYSDBA>
18:35:01 SYS@DB01 AS SYSDBA> begin
18:35:01   2  if
18:35:01   3    dbms_audit_mgmt.IS_CLEANUP_INITIALIZED(dbms_audit_mgmt.AUDIT_TRAIL_ALL) then
18:35:01   4    dbms_output.put_line('******* YES *******');
18:35:01   5  else
18:35:01   6    dbms_output.put_line('******* NO *******');
18:35:01   7  end if;
18:35:01   8  end;
18:35:01   9  /
******* YES *******

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
18:35:01 SYS@DB01 AS SYSDBA>
18:35:01 SYS@DB01 AS SYSDBA> select * from dba_audit_mgmt_config_params order by 1
18:35:01   2  ;

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                FGA AUDIT TRAIL
DB AUDIT TABLESPACE            AUDIT_TBS            STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            AUDIT_TBS            FGA AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   OS AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 XML AUDIT TRAIL

14 rows selected.

Elapsed: 00:00:00.01
18:35:01 SYS@DB01 AS SYSDBA>
18:35:01 SYS@DB01 AS SYSDBA> begin
18:35:01   2    dbms_audit_mgmt.CREATE_PURGE_JOB(
18:35:01   3      audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
18:35:01   4      audit_trail_purge_interval => 24,
18:35:01   5      audit_trail_purge_name     => 'PURGE_ALL_AUDIT_TRAILS',
18:35:01   6      use_last_arch_timestamp    => TRUE);
18:35:01   7  end;
18:35:01   8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.77
18:35:02 SYS@DB01 AS SYSDBA>
18:35:02 SYS@DB01 AS SYSDBA> begin
18:35:02   2    dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(
18:35:02   3      audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
18:35:02   4      last_archive_time => SYSTIMESTAMP-2562);
18:35:02   5  end;
18:35:02   6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
18:35:02 SYS@DB01 AS SYSDBA>
18:35:02 SYS@DB01 AS SYSDBA> begin
18:35:02   2    DBMS_SCHEDULER.create_job (
18:35:02   3      job_name        => 'AUDIT_LAST_ARCHIVE_TIME',
18:35:02   4      job_type        => 'PLSQL_BLOCK',
18:35:02   5      job_action      => 'DECLARE
18:35:02   6                            l_days NUMBER := 2562;
18:35:02   7                          BEGIN
18:35:02   8                            dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-l_days);
18:35:02   9                            dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_FGA_STD, TRUNC(SYSTIMESTAMP)-l_days);
18:35:02  10                            dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_OS,      TRUNC(SYSTIMESTAMP)-l_days);
18:35:02  11                            dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_XML,     TRUNC(SYSTIMESTAMP)-l_days);
18:35:02  12                          END;',
18:35:02  13      start_date      => SYSTIMESTAMP,
18:35:02  14      repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
18:35:02  15      end_date        => NULL,
18:35:02  16      enabled         => TRUE,
18:35:02  17      comments        => 'Automatically set audit last archive time.');
18:35:02  18  end;
18:35:02  19  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
18:35:02 SYS@DB01 AS SYSDBA>
18:35:02 SYS@DB01 AS SYSDBA> select * from dba_audit_mgmt_config_params order by 1
18:35:02   2  ;

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                FGA AUDIT TRAIL
DB AUDIT TABLESPACE            AUDIT_TBS            STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            AUDIT_TBS            FGA AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   OS AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 XML AUDIT TRAIL

14 rows selected.

Elapsed: 00:00:00.00
18:35:02 SYS@DB01 AS SYSDBA> select * from dba_audit_mgmt_last_arch_ts
18:35:02   2  ;

AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ---------------------------------------------
STANDARD AUDIT TRAIL            0 26-FEB-14 06.35.02.000000 PM +00:00

Elapsed: 00:00:00.00
18:35:02 SYS@DB01 AS SYSDBA> select job_action from dba_scheduler_jobs where job_name='PURGE_ALL_AUDIT_TRAILS'
18:35:02   2  ;

JOB_ACTION
------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(15, TRUE);  END;

Elapsed: 00:00:00.17
18:35:02 SYS@DB01 AS SYSDBA> select job_action from dba_scheduler_jobs where job_name='AUDIT_LAST_ARCHIVE_TIME'
18:35:02   2  ;

JOB_ACTION
------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE
                          l_days NUMBER := 2562;
                        BEGIN
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-l_days);
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_FGA_STD, TRUNC(SYSTIMESTAMP)-l_days);
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_OS,      TRUNC(SYSTIMESTAMP)-l_days);
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_XML,     TRUNC(SYSTIMESTAMP)-l_days);
                        END;


Elapsed: 00:00:00.14
18:35:02 SYS@DB01 AS SYSDBA> commit;

Commit complete.

Elapsed: 00:00:00.00
18:35:02 SYS@DB01 AS SYSDBA>
--- audit.sql
col parameter_name for a30
col parameter_value for a20
col audit_trail for a20
col owner for a20
col segment_name for a20
col tablespace_name for a20
col last_archive_ts for a45
col job_action for a150
set lines 200 pages 100 serverout on echo on

select owner, segment_name, segment_type, tablespace_name, round(bytes/1024/1024,2) USED_MB
from dba_segments where segment_name IN ('AUD$','FGA_LOG$') order by 2
;
select MIN(TIMESTAMP), MAX(TIMESTAMP), TRUNC(systimestamp)-MIN(TIMESTAMP) diff_day, COUNT(*)
from dba_audit_trail
;
select * from dba_audit_mgmt_config_params order by 1
;
select * from dba_audit_mgmt_last_arch_ts
;
exec dbms_audit_mgmt.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD, AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_TBS')
;
exec dbms_audit_mgmt.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_FGA_STD, AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_TBS')
;

begin
  dbms_audit_mgmt.INIT_CLEANUP (
    audit_trail_type         => dbms_audit_mgmt.audit_trail_all,
    default_cleanup_interval => 24);
end;
/

begin
if
  dbms_audit_mgmt.IS_CLEANUP_INITIALIZED(dbms_audit_mgmt.AUDIT_TRAIL_ALL) then
  dbms_output.put_line('******* YES *******');
else
  dbms_output.put_line('******* NO *******');
end if;
end;
/

select * from dba_audit_mgmt_config_params order by 1
;

begin
  dbms_audit_mgmt.CREATE_PURGE_JOB(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    audit_trail_purge_interval => 24,
    audit_trail_purge_name     => 'PURGE_ALL_AUDIT_TRAILS',
    use_last_arch_timestamp    => TRUE);
end;
/

begin
  dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    last_archive_time => SYSTIMESTAMP-2562);
end;
/

begin
  DBMS_SCHEDULER.create_job (
    job_name        => 'AUDIT_LAST_ARCHIVE_TIME',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'DECLARE
                          l_days NUMBER := 2562;
                        BEGIN
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-l_days);
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_FGA_STD, TRUNC(SYSTIMESTAMP)-l_days);
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_OS,      TRUNC(SYSTIMESTAMP)-l_days);
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_XML,     TRUNC(SYSTIMESTAMP)-l_days);
                        END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Automatically set audit last archive time.');
end;
/

select * from dba_audit_mgmt_config_params order by 1
;
select * from dba_audit_mgmt_last_arch_ts
;
select job_action from dba_scheduler_jobs where job_name='PURGE_ALL_AUDIT_TRAILS'
;
select job_action from dba_scheduler_jobs where job_name='AUDIT_LAST_ARCHIVE_TIME'
;
commit;

Migrating Windows PuTTY private key to Linux

Tue, 2021-03-02 22:48

A long, long time ago, I had blog about Migrating Windows PuTTY registry to Linux

Since then I have have migrated away from PuTTY with preference for CLI.

There are posts for Migrating Windows PuTTY private key to Linux; however, they did not work for me.

This is what worked for me, much simpler, and avoid having to transfer private key from source to target.

-- At SOURCE:
Open PuTTYgen
File - Load private key
Select *.ppk
Conversions - Export OpenSSH key to id_rsa
Open text editor and copy content from id_rsa

-- At TARGET:
qadesusiwevo@pc-33d1fa:~$ cd ~/.ssh/
qadesusiwevo@pc-33d1fa:~/.ssh$ vi id_rsa

-- Copy content from source id_rsa to target 

qadesusiwevo@pc-33d1fa:~/.ssh$ vi config 
qadesusiwevo@pc-33d1fa:~/.ssh$ cat config 
SendEnv LANG TERM LOGNAME
StrictHostKeyChecking=no
GSSAPIAuthentication=no
ForwardAgent=yes
TCPKeepAlive=yes
ServerAliveCountMax=10
ServerAliveInterval=30

qadesusiwevo@pc-33d1fa:~/.ssh$ cd
qadesusiwevo@pc-33d1fa:~$ vi setup.sh 
qadesusiwevo@pc-33d1fa:~$ cat setup.sh 
cd ~/.ssh
eval `ssh-agent -s`
ssh-add
ssh-add -l
exit

qadesusiwevo@pc-33d1fa:~$ chmod 600 ~/.ssh/id_rsa 
qadesusiwevo@pc-33d1fa:~$ ./setup.sh 
Agent pid 8268
Identity added: /home/qadesusiwevo@264-1-100-0-0-xxxxxxxxxx-us-common.vela.com/.ssh/id_rsa (/home/qadesusiwevo@264-1-100-0-0-xxxxxxxxxx-us-common.vela.com/.ssh/id_rsa)
2048 SHA256:D7OPt2dPVtZ8byfpZuZTCLYpUo54z/e6ouCdmDOtsuc /home/qadesusiwevo@264-1-100-0-0-xxxxxxxxxx-us-common.vela.com/.ssh/id_rsa (RSA)

qadesusiwevo@pc-33d1fa:~$ ssh username@10.26.31.203
Warning: Permanently added '10.26.31.203' (ECDSA) to the list of known hosts.
Last login: Wed Feb 24 04:28:19 2021 from ip-10-26-31-204.us-west-2.compute.internal

_| __| )
_| ( / Amazon Linux AMI
__|_|__|

https://aws.amazon.com/amazon-linux-ami/2016.09-release-notes/
82 package(s) needed for security, out of 149 available
Run "sudo yum update" to apply all updates.
Amazon Linux version 2018.03 is available.

-bash-4.2$ hostname
ip-10-26-31-203

-bash-4.2$ exit

NOTE: Failed voting file relocation on diskgroup

Sat, 2021-02-20 18:32

After Oracle support performs maintenance for Exadata Cloud, the 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

Tue, 2021-02-16 14:13

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.

Search And Replace Email From Shell Scripts

Thu, 2021-02-04 21:27

The goal is to replace dinh@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 'dinh@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 'dinh@gmail.com' *.sh|grep sh|awk -F':' '{print $1}'|sort -u|grep -v `basename $0`)
do
  echo $infile
  sed 's/dinh@gmail.com/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 'dinh@gmail.com' *.sh|grep sh|awk -F':' '{print $1}'|sort -u|grep -v edit_email.sh

Remove Characters From String Using regexp_replace

Thu, 2021-01-28 07:38

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.

Fri, 2020-12-04 12:29

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
__ATA.cmd.push(function() { __ATA.initDynamicSlot({ id: 'atatags-26942-5fca7ffd88139', location: 120, formFactor: '001', label: { text: 'Advertisements', }, creative: { reportAd: { text: 'Report this ad', }, privacySettings: { text: 'Privacy settings', } } }); });

Upgrade DB from 11.2 to 19.8 Using dbua silent

Fri, 2020-11-20 19:58

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

Fri, 2020-11-20 07:52

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

Thu, 2020-11-12 21:42

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 - MBANZINP_XXXXRAC   - Primary database
db_unique_name - MBANZINP_XXXXRACDR - Physical standby database

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

$ $OMS_HOME/bin/emcli get_targets -targets=rac_database | grep MBANZINP
 1       Up               rac_database          MBANZINP_XXXXRAC.domain.com
 1       Up               rac_database          MBANZINP_XXXXRACDR.domain.com

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

$OMS_HOME/bin/emcli create_blackout -name="WHATEVER" -reason="switchover" \
-add_targets="MBANZINP_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

Wed, 2020-11-11 10:26

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

Wed, 2020-11-04 19:21

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.

 

 

 

Validate And Perform 19c Data Guard Switchover

Thu, 2020-10-22 13:37

Please click on link to open document:

Validate And Perform 19c Data Guard Switchover

When Upgrading DB Don’t Trust Doc Alone

Wed, 2020-10-21 19:18

What’s up Doc!

So there I was, reading documentation and planning upgrade but still not perfect.

DBUA Command-Line Syntax for Active and Silent Mode

changeUserTablespacesReadOnly does not show from help but exists in documenation.

 

$ which dbua
/app/product/19.3.0.0/bin/dbua

$ dbua -help
Usage: dbua [<flag>] [<option>]
Following are the possible flags:
-createPartialBackup – Flag to create a new offline partial RMAN backup by setting the user tablespaces in R/O mode.
-backupLocation
-disableParallelUpgrade – Flag to disable the parallel execution of database upgrade.
-executePreReqs – Flag to execute the pre-upgrade checks alone for the specified database.
-sid | -dbName
-sid
-dbName
-help – Shows this usage help.
-ignorePreReqs – Ignore error conditions in pre-upgrade checks.
-keepEvents – Flag to keep the configured database events during upgrade.
-silent – This flag allows you to carry on configuration in silent mode.
-sid | -dbName
-sid
-dbName
-skipListenersMigration – Flag to skip the listener migration process as part of the database upgrade.

Following are the possible options:
[-asmsnmpPassword – <Specify ASMSNMP user password>]
[-backupLocation – <Specify directory to backup your database before starting the upgrade>]
[-createGRP – <true | false> To create a guaranteed restore point when database is in archive log and flashback mode.]
[-createListener – <true | false> To create a listener in newer release Oracle home specify listenrName:lsnrPort.]
[-dbName – <Specify Database Name>]
[-oracleHome – <Specify the Oracle home path of the database>]
[-sysDBAUserName – <User name with SYSDBA privileges>]
[-sysDBAPassword – <Password for sysDBAUserName user name>]
[-dbsnmpPassword – <Specify DBSNMP user password>]
[-disableUpgradeScriptLogging – <true | false> This command disables the detailed log generation for running SQL scripts during the upgrade process. By default this is enabled. To enable the log generation, don’t specify this command.]
[-emConfiguration – <DBEXPRESS | CENTRAL | BOTH | NONE>]
[-dbsnmpPassword – <Specify DBSNMP user password>]
[-emPassword – <Specify EM admin user password>]
[-emUser – <Specify EM admin username to add or modify targets>]
[-emExpressPort – <Specify the port where EM Express will be configured>]
[-omsHost – <Specify EM management server host name>]
[-omsPort – <Specify EM management server port number>]
[-asmsnmpPassword – <Specify ASMSNMP user password>]
[-ignoreScriptErrors – <true | false> Specify this flag for ignoring ORA errors during custom scripts.]
[-initParam – <Specify a comma separated list of initialization parameter values of the format name=value,name=value>]
[-initParamsEscapeChar – <Specify escape character for comma when a specific initParam has multiple values. If the escape character is not specified backslash is the default escape character>]
[-excludeInitParams – <Specify a comma separated list of initialization parameters to be excluded.>]
[-keepDeprecatedParams – <true | false> To retain deprecated parameters during database upgrade.]
[-localListenerWithoutAlias – To set LOCAL_LISTENER without TNS Alias.]
[-listeners – <To register the database with existing listeners, specify listeners by comma separated listenerName:Oracle Home. Listeners from lower release home are migrated to newer release home. Specifying -listeners lsnrName1,lsnrName2 or -listeners lsnrName1:<Oracle home path>,-listeners lsnrName2:<Oracle home path>, DBUA searches specified listeners from GI home (if configured), target home and source home>]
[-localRacSid – <Specify the local System Identifier of the cluster database if the cluster database is not registered in OCR>]
[-logDir – <Specify the path to a custom log directory>]
[-newGlobalDbName – <Specify New Global Database Name. This option can only be used for Oracle Express Edition upgrade>]
[-newSid – <Specify New System Identifier. This option can only be used for Oracle Express Edition upgrades>]
[-newInitParam – <Specify a comma separated list of initialization parameter values of the format name=value,name=value. Use this option to specify parameters that are allowed only on the target Oracle home>]
[-initParamsEscapeChar – <Specify escape character for comma when a specific initParam has multiple values. If the escape character is not specified backslash is the default escape character>]
[-oracleHomeUserPassword – <Specify Oracle Home user password>]
[-pdbs – <Specify a comma separated list with the names of the pluggable databases (PDB) that will be upgraded. Specify ALL to select all or NONE to select none of the pluggable databases for upgrade>]
-sid | -dbName
-sid – <Specify System Identifier>
[-oracleHome – <Specify the Oracle home path of the database>]
[-sysDBAUserName – <User name with SYSDBA privileges>]
[-sysDBAPassword – <Password for sysDBAUserName user name>]
-dbName – <Specify Database Name>
[-oracleHome – <Specify the Oracle home path of the database>]
[-sysDBAUserName – <User name with SYSDBA privileges>]
[-sysDBAPassword – <Password for sysDBAUserName user name>]
[-pdbsWithPriority – <Specify a comma separated list of pluggable databases (PDB) to be upgraded along with its corresponding priorities (being 1 the top priority) of the format <pdb name>:<upgrade priority>,<pdb name>:<upgrade priority> >]
-sid | -dbName
-sid – <Specify System Identifier>
[-oracleHome – <Specify the Oracle home path of the database>]
[-sysDBAUserName – <User name with SYSDBA privileges>]
[-sysDBAPassword – <Password for sysDBAUserName user name>]
-dbName – <Specify Database Name>
[-oracleHome – <Specify the Oracle home path of the database>]
[-sysDBAUserName – <User name with SYSDBA privileges>]
[-sysDBAPassword – <Password for sysDBAUserName user name>]
[-performFixUp – <true | false> Enable or disable fix ups for the silent upgrade mode.]
[-postUpgradeScripts – <Specify a comma separated list of SQL scripts with their complete pathnames. These scripts will be executed at the end of the upgrade>]
[-preUpgradeScripts – <Specify a comma separated list of SQL scripts with their complete pathnames. These scripts will be executed before the upgrade>]
[-recompile_invalid_objects – <true | false> Recompile invalid objects as part of the upgrade.]
[-upgrade_parallelism – <Specify number of CPU’s to be used for parallel upgrade>]
[-upgradeTimezone – <true | false> Upgrade the timezone files of the database.]
[-upgradeXML – <Specify the path to the existing pre-upgrade XML file> This option only applies to in-place database upgrades.]
[-useExistingBackup – <true | false> To restore database using existing RMAN backup.]
[-useGRP – <Specify the name of the existing guaranteed restore point> To restore the database using a specified guaranteed restore point.]

 

Even when -createListener show as valid syntax, using -createListener is not recognized.
$ cat run_dbua.sh

 
date
/app/product/19.3.0.0/bin/dbua -silent \
-sid db01 \
-oracleHome /app/product/11.2.0.4 \
-useGRP upgrade19c \
-recompile_invalid_objects TRUE \
-upgradeTimezone TRUE \
-emConfiguration NONE \
-skipListenersMigration \
-createListener false \  --- failed
-upgrade_parallelism 8
date
exit

./run_dbua.sh: line 10: -createListener: command not found
This works.
/app/product/19.3.0.0/bin/dbua -silent -sid db01 -skipListenersMigration -oracleHome /app/product/11.2.0.4 -recompile_invalid_objects true -upgradeTimezone true -emConfiguration NONE -upgrade_parallelism 4 -createListener false

What am i missing?

Hopefully, you will have better luck than I did.

Update: there as a space from line above thanks to https://twitter.com/VincePoore


$ grep -r '[[:blank:]]$' run_dbua.sh
-skipListenersMigration \

$ grep -r '[[:blank:]]$' run_dbua.sh | wc -l
1

Followup with Database runInstaller applyRU Failed Me

Sat, 2020-10-17 22:57

This is a followup Database runInstaller applyRU Failed Me

I finally figured out my error which I should have seen from the beginning and better error reporting would have helped.

Can you guess what’s wrong?

unzip -qod $ORACLE_HOME /vagrant_software/p6880880_190000_LINUX.zip; echo $?
versus
unzip -qod $ORACLE_HOME /vagrant_software/p6880880_190000_Linux-x86-64.zip; echo $?

The wrong platform for opatch was used.
Here is what should have been deployed.

[oracle@ol7-112-dg1 ~]$ unzip -qod $ORACLE_HOME /vagrant_software/p6880880_190000_Linux-x86-64.zip; echo $?
0
[oracle@ol7-112-dg1 ~]$
[oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
29517242;Database Release Update : 19.3.0.0.190416 (29517242)
OPatch succeeded.
[oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.21
OPatch succeeded.
[oracle@ol7-112-dg1 ~]$

[oracle@ol7-112-dg1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.3.0.0/db_1
[oracle@ol7-112-dg1 ~]$

### This failed:
[oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
/u01/app/oracle/product/19.3.0.0/db_1/OPatch/opatch: line 839: [: too many arguments
/u01/app/oracle/product/19.3.0.0/db_1/OPatch/opatch: line 839: [: too many arguments
Java (1.7) could not be located. OPatch cannot proceed!
OPatch returns with error code = 1
[oracle@ol7-112-dg1 ~]$

### This works but why?
[oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch version -jdk $ORACLE_HOME/jdk
OPatch Version: 12.2.0.1.21
OPatch succeeded.
[oracle@ol7-112-dg1 ~]$

### Here is java version and noticed it's 64-Bit
[oracle@ol7-112-dg1 bin]$ $ORACLE_HOME/jdk/bin/java -version
java version "1.8.0_201"
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)
[oracle@ol7-112-dg1 bin]$

$ORACLE_HOME/runInstaller -applyRU /home/oracle/patch/31305339 should now work.

Database runInstaller applyRU Failed Me

Sat, 2020-10-17 12:24

I still remembered the cliche a manager used to tell me, “Slow and steady win the race.”

Looks like it is true with Oracle software as you never know what’s going to get.

I am trying to install 19.3 database software and apply Patch 31305339 – GI Release Update 19.8.0.0.200714 which failed misserably.

There is little to no information for the failure (at least from what I was able to ascertain).

Resolving the issue requires starting over from the beginning.

Here are the steps taken:

[oracle@ol7-112-dg1 ~]$ cat /etc/system-release
Oracle Linux Server release 7.7

[oracle@ol7-112-dg1 ~]$ ll /etc/ora*
-rw-r--r--. 1 root   root      32 Aug  8  2019 /etc/oracle-release
-rw-rw-r--. 1 oracle oinstall 790 Oct 14 19:13 /etc/oratab

[oracle@ol7-112-dg1 ~]$ echo $new_db_home
/u01/app/oracle/product/19.3.0.0/db_1

[oracle@ol7-112-dg1 ~]$ rm -rf $new_db_home

[oracle@ol7-112-dg1 ~]$ ls $new_db_home
ls: cannot access /u01/app/oracle/product/19.3.0.0/db_1: No such file or directory

[oracle@ol7-112-dg1 ~]$ echo $zip_loc
/vagrant_software

[oracle@ol7-112-dg1 ~]$ ls -l $zip_loc/LINUX.X64_193000_db_home.zip
-rwxrwxrwx. 1 vagrant vagrant 3059705302 Sep  5  2019 /vagrant_software/LINUX.X64_193000_db_home.zip

[oracle@ol7-112-dg1 ~]$ unzip -qo $zip_loc/LINUX.X64_193000_db_home.zip -d $new_db_home; echo $?; ls $new_db_home
0
addnode     crs   dbjava       dmu      hs             jdbc  md       olap     ords  plsql    rdbms          runInstaller   sqlj      ucp
apex        css   dbs          drdaas   install        jdk   mgw      OPatch   oss   precomp  relnotes       schagent.conf  sqlpatch  usm
assistants  ctx   deinstall    dv       instantclient  jlib  network  opmn     oui   QOpatch  root.sh        sdk            sqlplus   utl
bin         cv    demo         env.ora  inventory      ldap  nls      oracore  owm   R        root.sh.old    slax           srvm      wwg
clone       data  diagnostics  has      javavm         lib   odbc     ord      perl  racg     root.sh.old.1  sqldeveloper   suptools  xdk

[oracle@ol7-112-dg1 ~]$ $new_db_home/OPatch/opatch version
OPatch Version: 12.2.0.1.17

OPatch succeeded.

[oracle@ol7-112-dg1 ~]$ unzip -qo $zip_loc/p6880880_190000_LINUX.zip -d $new_db_home; echo $?
0

[oracle@ol7-112-dg1 ~]$ $new_db_home/OPatch/opatch version
/u01/app/oracle/product/19.3.0.0/db_1/OPatch/opatch: line 839: [: too many arguments
/u01/app/oracle/product/19.3.0.0/db_1/OPatch/opatch: line 839: [: too many arguments
Java (1.7) could not be located. OPatch cannot proceed!
OPatch returns with error code = 1

[oracle@ol7-112-dg1 ~]$ $new_db_home/OPatch/opatch version -jdk $new_db_home/jdk
OPatch Version: 12.2.0.1.21

OPatch succeeded.

[oracle@ol7-112-dg1 ~]$ cd $new_db_home
[oracle@ol7-112-dg1 db_1]$ ls -l /home/oracle/patch/31305339
total 132
drwxr-x---. 5 oracle oinstall     81 Jul 10 05:20 31281355
drwxr-x---. 5 oracle oinstall     62 Jul 10 05:17 31304218
drwxr-x---. 5 oracle oinstall     62 Jul 10 05:18 31305087
drwxr-x---. 4 oracle oinstall     48 Jul 10 05:20 31335188
drwxr-x---. 2 oracle oinstall   4096 Jul 10 05:18 automation
-rw-rw-r--. 1 oracle oinstall   5054 Jul 10 05:46 bundle.xml
-rw-rw-r--. 1 oracle oinstall 120878 Jul 20 19:04 README.html
-rw-r--r--. 1 oracle oinstall      0 Jul 10 05:17 README.txt

[oracle@ol7-112-dg1 db_1]$ pwd
/u01/app/oracle/product/19.3.0.0/db_1

[oracle@ol7-112-dg1 db_1]$ ./runInstaller -debug -applyRU /home/oracle/patch/31305339
Preparing the home to patch…
Applying the patch /home/oracle/patch/31305339…
OPatch command failed while applying the patch. For details look at the logs from /u01/app/oracle/product/19.3.0.0/db_1/cfgtoollogs/opatchauto/.
[oracle@ol7-112-dg1 db_1]$ cd /u01/app/oracle/product/19.3.0.0/db_1/cfgtoollogs/opatchauto
-bash: cd: /u01/app/oracle/product/19.3.0.0/db_1/cfgtoollogs/opatchauto: No such file or directory
[oracle@ol7-112-dg1 db_1]$ cd /u01/app/oracle/product/19.3.0.0/db_1/cfgtoollogs/
[oracle@ol7-112-dg1 cfgtoollogs]$ ll
total 4
drwxrwx---. 2 oracle oinstall 4096 Oct 17 16:44 oui
[oracle@ol7-112-dg1 cfgtoollogs]$

[oracle@ol7-112-dg1 db_1]$ ./runInstaller -debug -applyRU /home/oracle/patch/31305339

ERROR: The home is not clean. This home cannot be used since there was a failed OPatch execution in this home. Use a different home to proceed.

[oracle@ol7-112-dg1 db_1]$


Executing "gridSetup.sh" Fails with "ERROR: The home is not clean"(Doc ID 2279633.1)
Fails with "ERROR: The home is not clean" .

[root]# rm-Rf 
[oracle]$ unzip linuxx64_12201_grid_home.zip -d 

Database 19c Upgrade Land Mines And Resources

Sat, 2020-10-17 08:11

Here are the contents of for a tweet I started.

Hard to imagine setting SQLPATH ahd glogin.sql would affect upgrade.

That’s the only changes I made.

“ORA-04023: Object SYS.STANDARD Could Not Be Validated or Authorized” during database upgrade (Doc ID 984511.1)

Documentation does not mention having customized glogin.sql and/or SQLPATH will cause upgrade to fail. Documentation has now been updated.

You can read more about Upgrade and profile scripts

AutoUpgrade validates glogin for you before starting. It looks like any improvements are only added to AutoUgrade and not traditional methods. Not only do you need to read the documentation, you also need to read again because of changes. 

Lastly, here are Useful List of Examples which I find helpful.

Automating Index Rebuild

Wed, 2020-09-30 18:28

IMPORTANT: This is not a recommendation to rebuild indexes.

The post will outline SQL used to determine index to rebuild.

PL/SQL will be used to check table lock for the underlying index and if there is no lock, then rebuild index else skip rebuild for index.

1.Download Index Sizing and create copy index_est_proc_2.sql.org

2. Create table index_rebuild.

SQL> desc index_rebuild
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_OWNER                               NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 INDEX_NAME                                NOT NULL VARCHAR2(20)
 LEAF_BLOCKS                                        NUMBER
 TARGET_SIZE                                        NUMBER

SQL>

3. Update index_est_proc_2.sql and include the following insert into table index rebuild.

if m_leaf_estimate < &m_scale_factor * r.leaf_blocks then
  dbms_output.put_line(
    to_char(sysdate,'hh24_mi_ss') || '|table|' ||
    trim(r.table_name) || '|index|' ||
    trim(r.index_name) || '|' || 'Current Leaf blocks|' || trim(to_char(r.leaf_blocks,'999,999,999')) || '|Target size|' || 
    trim(to_char(m_leaf_estimate,'999,999,999'))
  );

  -- Insert data into table index_rebuild as well as output to terminal.
  insert into index_rebuild(table_owner,table_name,index_name,leaf_blocks,target_size)
  values
  (UPPER('&m_owner'),trim(r.table_name),trim(r.index_name),r.leaf_blocks,m_leaf_estimate);
  dbms_output.new_line;
end if;

4. Create plsql_rebuild_idx.sql

set timing on time on serveroutput on size unlimited trimsp on tab off lines 200
col TABLE_OWNER for a30
col TABLE_NAME for a30
col INDEX_NAME for a35
col USERNAME for a10
col MACHINE for a10
col MODULE for a30
-- Display current user session info.
select s.username as Username,
       s.machine as Machine,
       s.module as Module,
       s.sid as SessionID,
       p.pid as ProcessID,
       p.spid as "UNIX ProcessID"
from
v$session s, v$process p
where s.sid = sys_context ('userenv','sid')
and s.PADDR = p.ADDR
;
set echo on
-- Rebuild indexes with LEAF_BLOCKS < 16000000 and edit as required.
select * from index_rebuild where LEAF_BLOCKS < 16000000;
exit
lock table index_rebuild in EXCLUSIVE mode WAIT 120;
DECLARE
  l_sql varchar2(1000);
  l_ct  number;
BEGIN
FOR d in (
  select TABLE_OWNER, TABLE_NAME, INDEX_NAME, LEAF_BLOCKS from index_rebuild order by leaf_blocks asc
)
LOOP
  select count(*) into l_ct
  from v$locked_object a, v$session b, dba_objects c
  where b.sid = a.session_id
  and a.object_id = c.object_id
  and c.object_type='TABLE'
  and c.owner=d.TABLE_OWNER
  and c.object_name=d.TABLE_NAME
  and d.LEAF_BLOCKS < 16000000;
  IF l_ct = 0 THEN
    dbms_output.put_line( '-- Check lock for owner|table|index : ' ||d.TABLE_OWNER||'.'||d.TABLE_NAME||'.'||d.INDEX_NAME||'='||l_ct );
    l_sql := 'alter index '||d.TABLE_OWNER||'.'||d.INDEX_NAME||' rebuild online parallel 4';
    dbms_output.put_line (l_sql);
    execute immediate l_sql;
    delete from index_rebuild where TABLE_OWNER=d.TABLE_OWNER and TABLE_NAME=d.TABLE_NAME and INDEX_NAME=d.INDEX_NAME;
  END IF;
END LOOP;
END;
/
delete from index_rebuild;
commit;
exit

5. Run plsql_rebuild_idx.sql using nohup

nohup sqlplus "/ as sysdba" @ plsql_rebuild_idx.sql > plsql_rebuild_idx.log 2>&1 &

6. Review

$ cat plsql_rebuild_idx.log
nohup: ignoring input

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 24 14:13:00 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

14:13:00 SQL> select * from index_rebuild;

TABLE_OWNER                    TABLE_NAME                     INDEX_NAME           LEAF_BLOCKS TARGET_SIZE
------------------------------ ------------------------------ -------------------- ----------- -----------
XXXX                           YYYYYYYYYY1                    ZZZZZZZZZZZ_M            9721430     4328586
XXXX                           YYYYYYYYYY2                    ZZZZZZZZZZZ_MP          15865953     5848673

Elapsed: 00:00:00.00
14:13:00 SQL> lock table index_rebuild in EXCLUSIVE mode WAIT 120;

Table(s) Locked.

Elapsed: 00:00:00.00
14:13:00 SQL> DECLARE
14:13:00   2    l_sql varchar2(1000);
14:13:00   3    l_ct  number;
14:13:00   4  BEGIN
14:13:00   5  FOR d in (
14:13:00   6    select TABLE_OWNER, TABLE_NAME, INDEX_NAME, LEAF_BLOCKS from index_rebuild order by leaf_blocks asc
14:13:00   7  )
14:13:00   8  LOOP
14:13:00   9    select count(*) into l_ct
14:13:00  10    from v$locked_object a, v$session b, dba_objects c
14:13:00  11    where b.sid = a.session_id
14:13:00  12    and a.object_id = c.object_id
14:13:00  13    and c.object_type='TABLE'
14:13:00  14    and c.owner=d.TABLE_OWNER
14:13:00  15    and c.object_name=d.TABLE_NAME;
14:13:00  16    IF l_ct = 0 THEN
14:13:00  17      dbms_output.put_line( '-- Check lock for owner|table|index : ' ||d.TABLE_OWNER||'.'||d.TABLE_NAME||'.'||d.INDEX_NAME||'='||l_ct );
14:13:00  18      l_sql := 'alter index '||d.TABLE_OWNER||'.'||d.INDEX_NAME||' rebuild online parallel 4';
14:13:00  19      dbms_output.put_line (l_sql);
14:13:00  20      execute immediate l_sql;
14:13:00  21      delete from index_rebuild where TABLE_OWNER=d.TABLE_OWNER and TABLE_NAME=d.TABLE_NAME and INDEX_NAME=d.INDEX_NAME;
14:13:00  22    END IF;
14:13:00  23  END LOOP;
14:13:00  24  END;
14:13:00  25  /
-- Check lock for owner|table|index : XXXX.YYYYYYYYYY1.ZZZZZZZZZZZ_M=0
alter index XXXX.ZZZZZZZZZZZ_M rebuild online parallel 4
-- Check lock for owner|table|index : XXXX.YYYYYYYYYY2.ZZZZZZZZZZZ_MP=0
alter index XXXX.ZZZZZZZZZZZ_MP rebuild online parallel 4

PL/SQL procedure successfully completed.

Elapsed: 04:00:23.08
18:13:23 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
18:13:23 SQL> exit

7. Run index_est_proc_2.sql.org (screen output only) or index_est_proc_2.sql (screen output and insert into index_rebuild table) to determine if any more indexes are listed for rebuild.

Note: The first rebuild contained a few dozen of indexes for rebuild but was not automated.

Later, there were only 2 indexes for rebuild as shown above from real production environment before minor improvements, e.g. — Display current user session info.

Q.E.D.

19c New Feature DGMGRL validate database?

Sun, 2020-09-20 09:35

Not too long ago, I had blogged about When To Use dgmgrl / vs dgmgrl sys@tns

I believe this is New Feature for 19c (but not 100% certain) may resolved the question above?.

DEMO:
Connect using OS authentication from standby host.

ERROR:
ORA-01017: invalid username/password; logon denied

[oracle@ol7-112-dg2 ~]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sun Sep 20 14:22:13 2020
Version 19.3.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "hawk_stby"
Connected as SYSDG.
DGMGRL> validate database hawk;

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Managed by Clusterware:
    hawk:  NO
    Validating static connect identifier for the primary database hawk...

ORA-01017: invalid username/password; logon denied

    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

DGMGRL> validate database hawk_stby;

  Database Role:     Physical standby database
  Primary Database:  hawk

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Managed by Clusterware:
    hawk     :  NO
    hawk_stby:  NO
    Validating static connect identifier for the primary database hawk...

ORA-01017: invalid username/password; logon denied

    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

  Log Files Cleared:
    hawk Standby Redo Log Files:       Cleared
    hawk_stby Online Redo Log Files:   Not Cleared
    hawk_stby Standby Redo Log Files:  Available

DGMGRL>

DEMO:
Connect to primary using tns from standby host.

DGMGRL> connect sys/oracle@hawk
Connected to "hawk"
Connected as SYSDBA.
DGMGRL> validate database hawk;

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Managed by Clusterware:
    hawk:  NO
    Validating static connect identifier for the primary database hawk...
    The static connect identifier allows for a connection to database "hawk".

DGMGRL> validate database hawk_stby;

  Database Role:     Physical standby database
  Primary Database:  hawk

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Managed by Clusterware:
    hawk     :  NO
    hawk_stby:  NO
    Validating static connect identifier for the primary database hawk...
    The static connect identifier allows for a connection to database "hawk".

  Log Files Cleared:
    hawk Standby Redo Log Files:       Cleared
    hawk_stby Online Redo Log Files:   Not Cleared
    hawk_stby Standby Redo Log Files:  Available

DGMGRL>

This will at least address one example for when to use TNS vs OS authentication for DGMGRL.

Monitoring LAG Using DGMGRL Is Nice And Needs Improvements

Sat, 2020-09-19 08:53

On November 2, 2017, I had blogged about Monitoring Standby – SQLPlus or DGMGRL

Since the post, I do not recall using dgmgrl much for monitoring lag.

Almost 3 years later and 19c, let’s revisit the topic.

Here is what monitoring lag looks like from SQLPlus.
Notice BLOCK# increased which mean transfer is working.

SQL> r
  1  select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where BLOCK#>1
  4  and status not in ('CLOSING','IDLE')
  5  order by status desc, thread#, sequence#
  6*

                                        CLIENT                                                  DELAY
PID                       INST  THREAD# PROCESS      PROCESS   STATUS       SEQUENCE#   BLOCK#   MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9589                         1        1 LGWR         RFS       RECEIVING          175     8540      0
9059                         1        1 N/A          MRP0      APPLYING_LOG       175     8540      0

SQL> r
  1  select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where BLOCK#>1
  4  and status not in ('CLOSING','IDLE')
  5  order by status desc, thread#, sequence#
  6*

                                        CLIENT                                                  DELAY
PID                       INST  THREAD# PROCESS      PROCESS   STATUS       SEQUENCE#   BLOCK#   MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9589                         1        1 LGWR         RFS       RECEIVING          175     8554      0
9059                         1        1 N/A          MRP0      APPLYING_LOG       175     8554      0

SQL> 

For 19c, show configuration lag will provide info on lag and is knowing Lag is 0 seconds good enough?

[oracle@ol7-112-dg2 sql]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Sep 19 12:50:58 2020
Version 19.3.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "hawk_stby"
Connected as SYSDG.
DGMGRL> show configuration lag

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk      - Primary database
    hawk_stby - Physical standby database
                Transport Lag:      0 seconds (computed 9 seconds ago)
                Apply Lag:          0 seconds (computed 9 seconds ago)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 20 seconds ago)

DGMGRL> show configuration lag verbose

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk      - Primary database
    hawk_stby - Physical standby database
                Transport Lag:      0 seconds (computed 12 seconds ago)
                Apply Lag:          0 seconds (computed 12 seconds ago)

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = ''

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS
DGMGRL>

Using SendQEntries shows LOG_SEQ but RecvQEntries does not.

DGMGRL> show database hawk_stby RecvQEntries
STANDBY_RECEIVE_QUEUE
              STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED        FIRST_CHANGE#         NEXT_CHANGE#       SIZE (KBs)

DGMGRL> show database hawk SendQEntries
PRIMARY_SEND_QUEUE
        STANDBY_NAME       STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED        FIRST_CHANGE#         NEXT_CHANGE#       SIZE (KBs)
                          CURRENT       1047346434                1                  175  09/19/2020 12:32:29                                   2984164                                 23586

DGMGRL> show database hawk_stby RecvQEntries
STANDBY_RECEIVE_QUEUE
              STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED        FIRST_CHANGE#         NEXT_CHANGE#       SIZE (KBs)

DGMGRL>

Disable apply and compare differences between SQLPlus and DGMGRL.

DGMGRL> edit database hawk_stby set state=APPLY-OFF
> ;
Succeeded.
DGMGRL> show database hawk_stby

Database - hawk_stby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-OFF
  Transport Lag:      0 seconds (computed 8 seconds ago)
  Apply Lag:          0 seconds (computed 8 seconds ago)
  Average Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    hawk

Database Status:
SUCCESS

DGMGRL>

### From Primary:

*** gv$managed_standby ***

                                        CLIENT                                                  DELAY
PID                       INST  THREAD# PROCESS      PROCESS   STATUS       SEQUENCE#   BLOCK#   MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9030                         1        1 LNS          LNS       WRITING            180     1311      0

SQL> r
  1  select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where BLOCK#>1
  4  and status not in ('CLOSING','IDLE')
  5  order by status desc, thread#, sequence#
  6*

                                        CLIENT                                                  DELAY
PID                       INST  THREAD# PROCESS      PROCESS   STATUS       SEQUENCE#   BLOCK#   MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9030                         1        1 LNS          LNS       WRITING            180     1314      0

SQL>

### From Standby:

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       1        1 NO             179 19-SEP-2020 13:12:25         5 39.93333
       1        1 YES            174 19-SEP-2020 12:32:29

SQL> r
  1  select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where BLOCK#>1
  4  and status not in ('CLOSING','IDLE')
  5  order by status desc, thread#, sequence#
  6*

                                        CLIENT                                                  DELAY
PID                       INST  THREAD# PROCESS      PROCESS   STATUS       SEQUENCE#   BLOCK#   MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9589                         1        1 LGWR         RFS       RECEIVING          180      284      0

SQL> r
  1  select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where BLOCK#>1
  4  and status not in ('CLOSING','IDLE')
  5  order by status desc, thread#, sequence#
  6*

                                        CLIENT                                                  DELAY
PID                       INST  THREAD# PROCESS      PROCESS   STATUS       SEQUENCE#   BLOCK#   MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9589                         1        1 LGWR         RFS       RECEIVING          180      298      0

SQL>

From DGMGRL:

DGMGRL> show configuration lag

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk      - Primary database
    hawk_stby - Physical standby database
                Transport Lag:      0 seconds (computed 6 seconds ago)
                Apply Lag:          6 minutes 48 seconds (computed 6 seconds ago)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 31 seconds ago)

DGMGRL> show database hawk SendQEntries
PRIMARY_SEND_QUEUE
        STANDBY_NAME       STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED        FIRST_CHANGE#         NEXT_CHANGE#       SIZE (KBs)
                          CURRENT       1047346434                1                  180  09/19/2020 13:12:25                                   2992416                                   270

DGMGRL> show database hawk_stby RecvQEntries
STANDBY_RECEIVE_QUEUE
              STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED        FIRST_CHANGE#         NEXT_CHANGE#       SIZE (KBs)
   PARTIALLY_APPLIED       1047346434                1                  175  09/19/2020 12:32:29  09/19/2020 13:12:16              2984164              2992388            24709
         NOT_APPLIED       1047346434                1                  176  09/19/2020 13:12:16  09/19/2020 13:12:17              2992388              2992393                1
         NOT_APPLIED       1047346434                1                  177  09/19/2020 13:12:17  09/19/2020 13:12:20              2992393              2992400                2
         NOT_APPLIED       1047346434                1                  178  09/19/2020 13:12:20  09/19/2020 13:12:20              2992400              2992403                1
         NOT_APPLIED       1047346434                1                  179  09/19/2020 13:12:20  09/19/2020 13:12:25              2992403              2992416                3

DGMGRL>

APPLY-ON

DGMGRL> edit database hawk_stby set state=APPLY-ON;
Succeeded.
DGMGRL> show configuration lag

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk      - Primary database
    hawk_stby - Physical standby database
                Transport Lag:      0 seconds (computed 3 seconds ago)
                Apply Lag:          0 seconds (computed 3 seconds ago)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 41 seconds ago)

DGMGRL> /

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk      - Primary database
    hawk_stby - Physical standby database
                Transport Lag:      0 seconds (computed 4 seconds ago)
                Apply Lag:          0 seconds (computed 4 seconds ago)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 56 seconds ago)

DGMGRL> show database hawk_stby

Database - hawk_stby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 6 seconds ago)
  Apply Lag:          0 seconds (computed 6 seconds ago)
  Average Apply Rate: 1.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    hawk

Database Status:
SUCCESS

DGMGRL>

It would be nice if show configuration lag is able to provide some high level info frequently asked by management.

What is lag time, how many sequence is the standby behind, what is the apply rate, what is LOG_SEQ at primary and standby?

Pages