Thinking Out Loud

January 13, 2022

COMPLICATED COMMINGLED DATABASE ENVIRONMENT

Filed under: awk_sed_grep,linux,RMAN,shell scripting — mdinh @ 11:16 pm

I have been reviewing RMAN RAC backup for environment having a total of 15 non-production and production databases on the same host excluding APX and MGMTDB.

That’s not a big deal, as I have once had to managed 28 databases residing on the same host, right?

I am just too lazy and too tedious to change RMAN configuration one database at a time.

Luckily, there is a convention where non-production instances ends with T1 and production instances ends with P1.

This allows me to make the same changes to non-production and production in 2 steps.

Goal is to configure RMAN PARALLELISM 2 for NON-PROD and PARALLELISM 4 for PROD and consistent RECOVERY WINDOW OF 14 DAYS.

### Current configuration is inconsistent across databases:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4;

====================
### NON-PROD: 
====================

--- RMAN commands: cat configure.rman:
set echo on
connect target;
show all;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
show all;
exit

--- Let's make sure the instances are correct:
$ ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|grep -Ev '\P1'|sort
DB01T1
DB02T1
DB03T1

--- Make the change:
$ for db in $(ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|grep -Ev '\P1'|sort); do echo 'RMAN configure' $db; . oraenv <<< $db; rman @configure.rman; done;

====================
### PROD:
====================

--- RMAN commands: cat configure.rman:
set echo on
connect target;
show all;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4;
show all;
exit

--- Let's make sure the instances are correct:
$ ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|grep -E '\P1'|sort
DB01P1
DB02P1
DB03P1

--- Make the change:
$ for db in $(ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|grep -E '\P1'|sort); do echo 'RMAN configure' $db; . oraenv <<< $db; rman @configure.rman; done;

January 10, 2022

HOW TO LOAD BALANCE RMAN RAC DATABASE BACKUP

Filed under: awk_sed_grep,RAC,RMAN — mdinh @ 11:49 pm

First, I will share the incorrect method since it is hard coded.

CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT 'sys/passwd@inst1';
CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT 'sys/passwd@inst1';
CONFIGURE CHANNEL 3 DEVICE TYPE DISK CONNECT 'sys/passwd@inst2';
CONFIGURE CHANNEL 4 DEVICE TYPE DISK CONNECT 'sys/passwd@inst2';

The goal is to configure RMAN backup with parallel 4 and load balance.

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE CHANNEL DEVICE TYPE DISK CONNECT 'sys/***@DB_UNIQUE_NAME';

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jan 10 17:24:15 2022

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

connected to target database: DB_NAME (DBID=453022715)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name DB_UNIQUE_NAME are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/mnt/backups/DB_NAME/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK CONNECT '*';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DB_NAME_DATA/DB_UNIQUE_NAME/controlfile/snapcf_DB_NAME.f';

RMAN>

It’s that easy. Changing parallelism will automatically load balance across all nodes.

Here is an example where parallelism is configured and backup is not load balance.

All the channels are allocated to node1.

[oracle@host01 log]$ grep 'channel ORA_DISK_[1-9]: SID' backup_HAWK_level1_202201010300_Sat.log

channel ORA_DISK_1: SID=760 instance=HAWK1 device type=DISK
channel ORA_DISK_2: SID=956 instance=HAWK1 device type=DISK
channel ORA_DISK_3: SID=1139 instance=HAWK1 device type=DISK
channel ORA_DISK_4: SID=1331 instance=HAWK1 device type=DISK

channel ORA_DISK_1: SID=760 instance=HAWK1 device type=DISK
channel ORA_DISK_2: SID=956 instance=HAWK1 device type=DISK
channel ORA_DISK_3: SID=1139 instance=HAWK1 device type=DISK
channel ORA_DISK_4: SID=1331 instance=HAWK1 device type=DISK

channel ORA_DISK_1: SID=760 instance=HAWK1 device type=DISK
channel ORA_DISK_2: SID=956 instance=HAWK1 device type=DISK
channel ORA_DISK_3: SID=1139 instance=HAWK1 device type=DISK
channel ORA_DISK_4: SID=1331 instance=HAWK1 device type=DISK

[oracle@host01 log]$

Here is the correct way and let the database determine the node.

[oracle@host01 log]$ grep 'channel ORA_DISK_[1-9]: SID' backup_HAWK_level1_202201101400_Mon.log

channel ORA_DISK_1: SID=199 instance=HAWK2 device type=DISK
channel ORA_DISK_2: SID=2469 instance=HAWK1 device type=DISK
channel ORA_DISK_3: SID=196 instance=HAWK1 device type=DISK
channel ORA_DISK_4: SID=1139 instance=HAWK2 device type=DISK

channel ORA_DISK_1: SID=2469 instance=HAWK1 device type=DISK
channel ORA_DISK_2: SID=196 instance=HAWK1 device type=DISK
channel ORA_DISK_3: SID=1139 instance=HAWK2 device type=DISK
channel ORA_DISK_4: SID=199 instance=HAWK2 device type=DISK

channel ORA_DISK_1: SID=2469 instance=HAWK1 device type=DISK
channel ORA_DISK_2: SID=196 instance=HAWK1 device type=DISK
channel ORA_DISK_3: SID=1139 instance=HAWK2 device type=DISK
channel ORA_DISK_4: SID=199 instance=HAWK2 device type=DISK

[oracle@host01 log]$

January 6, 2022

Review RMAN Backup for All Databases On Host

Filed under: oracle,RMAN — mdinh @ 1:18 am

There are 15+ databases on host.

Here’s a nice and easy method to run any SQL for all databases.

[oracle@xxxxxxxxxx dinh]$ for db in $(ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|sort); do echo 'Database check' $db; . oraenv <<< $db; sqlplus -S / as sysdba @backup_review.sql; done;
Database check DEBUGX1
ORACLE_SID = [xxxxxxxxxx] ? The Oracle base remains unchanged with value /u01/app/oracle

Session altered.


NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
--------- ------------------------------ ----------------
DEBUGX    DEBUGX                         PRIMARY


DATAFILE_CT
-----------
         66


START_DATE  STATUS                       MINUTES INPUT_TYPE    OUTPUT_TYPE  INPUT_TYPE_CT OUTPUT_TYPE_CT     INPUT_MB    OUTPUT_MB
----------- ------------------------- ---------- ------------- ------------ ------------- -------------- ------------ ------------
2021-DEC-20 COMPLETED                          9 ARCHIVELOG    DISK                    22             22          902          691
2021-DEC-20 COMPLETED                          1 DB INCR       DISK                     1              1       17,563           41
2021-DEC-21 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          970          730
2021-DEC-21 COMPLETED                          1 DB INCR       DISK                     1              1       17,527           36
2021-DEC-22 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          977          732
2021-DEC-22 COMPLETED                          1 DB INCR       DISK                     1              1       17,537           38
2021-DEC-23 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          934          719
2021-DEC-23 COMPLETED                          1 DB INCR       DISK                     1              1       17,523           38
2021-DEC-24 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          937          720
2021-DEC-24 COMPLETED                          1 DB INCR       DISK                     1              1       17,507           36
2021-DEC-25 COMPLETED                          8 ARCHIVELOG    DISK                    23             23        1,024          746
2021-DEC-25 COMPLETED                          1 DB INCR       DISK                     1              1       17,507           36
2021-DEC-26 COMPLETED                          9 ARCHIVELOG    DISK                    23             23        1,040          750
2021-DEC-26 COMPLETED                          9 DB INCR       DISK                     1              1       20,617        3,515
2021-DEC-27 COMPLETED                          9 ARCHIVELOG    DISK                    22             22          906          692
2021-DEC-27 COMPLETED                          2 DB INCR       DISK                     1              1       17,528           39
2021-DEC-28 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          967          729
2021-DEC-28 COMPLETED                          1 DB INCR       DISK                     1              1       17,544           39
2021-DEC-29 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          988          736
2021-DEC-29 COMPLETED                          1 DB INCR       DISK                     1              1       17,546           38
2021-DEC-30 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          921          716
2021-DEC-30 COMPLETED                          1 DB INCR       DISK                     1              1       17,578           48
2021-DEC-31 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          945          723
2021-DEC-31 COMPLETED                          1 DB INCR       DISK                     1              1       17,517           36
2022-JAN-01 COMPLETED                          8 ARCHIVELOG    DISK                    23             23        1,016          744
2022-JAN-01 COMPLETED                          1 DB INCR       DISK                     1              1       17,530           38
2022-JAN-02 COMPLETED                          9 ARCHIVELOG    DISK                    23             23        1,009          742
2022-JAN-02 COMPLETED                          9 DB INCR       DISK                     1              1       20,627        3,514
2022-JAN-03 COMPLETED                          8 ARCHIVELOG    DISK                    22             22          903          691
2022-JAN-03 COMPLETED                          1 DB INCR       DISK                     1              1       17,567           42
2022-JAN-04 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          958          727
2022-JAN-04 COMPLETED                          1 DB INCR       DISK                     1              1       17,530           36
2022-JAN-05 COMPLETED                          5 ARCHIVELOG    DISK                    14             14          511          421
2022-JAN-05 COMPLETED                          1 DB INCR       DISK                     1              1       17,511           37
                                                                                                         ------------ ------------
sum                                                                                                           320,167       19,616

34 rows selected.

Database check RHPNEWDB1
ORACLE_SID = [XXXXXXXX] ? ORACLE_BASE environment variable is not being set since this
information is not available for the current user ID oracle.
You can set ORACLE_BASE manually if it is required.
Resetting ORACLE_BASE to its previous value or ORACLE_HOME
The Oracle base remains unchanged with value /u01/app/oracle

Session altered.


NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
--------- ------------------------------ ----------------
RHPNEWDB  RHPNEWDB_XXXXXXX               PRIMARY


DATAFILE_CT
-----------
         33


START_DATE  STATUS                       MINUTES INPUT_TYPE    OUTPUT_TYPE  INPUT_TYPE_CT OUTPUT_TYPE_CT     INPUT_MB    OUTPUT_MB
----------- ------------------------- ---------- ------------- ------------ ------------- -------------- ------------ ------------
2021-DEC-26 COMPLETED WITH WARNINGS           17 ARCHIVELOG    DISK                     5              5       18,692        6,516
2021-DEC-26 COMPLETED WITH WARNINGS          414 DB INCR       DISK                     1              1    1,183,591      182,525
2021-DEC-27 COMPLETED WITH WARNINGS           59 ARCHIVELOG    DISK                    17             17       67,941       23,384
2021-DEC-27 COMPLETED WITH WARNINGS            8 DB INCR       DISK                     1              1       18,048        3,537
2021-DEC-28 COMPLETED WITH WARNINGS           62 ARCHIVELOG    DISK                    22             22       68,189       23,623
2021-DEC-28 COMPLETED WITH WARNINGS            9 DB INCR       DISK                     1              1       19,403        3,826
2021-DEC-29 COMPLETED WITH WARNINGS           65 ARCHIVELOG    DISK                    22             22       68,835       23,807
2021-DEC-29 COMPLETED WITH WARNINGS            9 DB INCR       DISK                     1              1       19,801        3,934
2021-DEC-30 COMPLETED WITH WARNINGS           61 ARCHIVELOG    DISK                    22             22       68,416       23,680
2021-DEC-30 COMPLETED WITH WARNINGS           11 DB INCR       DISK                     1              1       22,870        4,739
2021-DEC-31 COMPLETED WITH WARNINGS           58 ARCHIVELOG    DISK                    22             22       64,633       22,392
2021-DEC-31 COMPLETED WITH WARNINGS           10 DB INCR       DISK                     1              1       20,545        4,125
2022-JAN-01 COMPLETED WITH WARNINGS           57 ARCHIVELOG    DISK                    22             22       61,385       21,477
2022-JAN-01 COMPLETED WITH WARNINGS            9 DB INCR       DISK                     1              1       17,463        3,689
2022-JAN-02 COMPLETED WITH WARNINGS           52 ARCHIVELOG    DISK                    21             21       55,319       19,407
2022-JAN-02 COMPLETED WITH WARNINGS          419 DB INCR       DISK                     1              1    1,193,071      185,202
2022-JAN-03 COMPLETED WITH WARNINGS           62 ARCHIVELOG    DISK                    17             17       68,181       23,433
2022-JAN-03 COMPLETED WITH WARNINGS           12 DB INCR       DISK                     1              1       23,149        5,028
2022-JAN-04 COMPLETED WITH WARNINGS           62 ARCHIVELOG    DISK                    22             22       68,714       23,797
2022-JAN-04 COMPLETED WITH WARNINGS            8 DB INCR       DISK                     1              1       18,222        3,418
2022-JAN-05 COMPLETED WITH WARNINGS           38 ARCHIVELOG    DISK                    16             16       41,959       14,496
                                                                                                         ------------ ------------
sum                                                                                                         3,188,427      626,035

21 rows selected.

[oracle@xxxxxxxxxx dinh]$

----- COMPLETED WITH WARNINGS
Check RMAN log file to find
RMAN-08120: warning: archived log not deleted, not yet applied by standby

for db in $(ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|sort); do echo 'Database check' $db; . oraenv <<< $db; sqlplus -S / as sysdba @backup_review.sql; done;

--- backup_review.sql
col input_mb  format 999,999,999
col output_mb format 999,999,999
col output_type for a12
col status for a25
set lines 200 pages 5000
break on report
compute SUM of input_mb on report
compute SUM of output_mb on report
break on report
alter session set nls_date_format = 'YYYY-MON-DD';
set echo on
select name, db_unique_name, database_role from v$database;
select count(*) datafile_ct from v$datafile;
select
TRUNC(start_time) start_date,
status,
round(sum(elapsed_seconds)/60) minutes,
input_type,
output_device_type output_type,
count(input_type) input_type_ct,
count(output_device_type) output_type_ct,
round(sum(input_bytes)/1024/1024) input_mb,
round(sum(output_bytes)/1024/1024) output_mb
from V$RMAN_BACKUP_JOB_DETAILS
where TRUNC(start_time)>=TRUNC(sysdate)-16
group by TRUNC(start_time), input_type, output_device_type, status
order by 1 asc, input_type asc, 2
;
/* "OUTPUT_DEVICE_TYPE (*) indicates more than one device" */
exit

December 17, 2021

OEM Log4j Vulnerability

Filed under: Grid Control — mdinh @ 12:28 am

Surprise that Oracle did not automate the solution vs manual work.

Test case below is for EM 13.5 only.

Hopefully, I did it right and would be nice to have some sort of validations.

Security Alert For CVE-2021-44228 & CVE-2021-45046 Patch Availability Document for Oracle Enterprise Manager Cloud Control (Doc ID 2828296.1)

Applies to Oracle Enterprise Manager 13.5 & 13.4 and underlying Oracle Fusion Middleware 12.2.1.4 and 12.2.1.3 products using Log4j 2.X jars 

Components impacted with Log4j version 2 jars based on EM version
EM 13.5

FMW Component on OMS Home
DB Plugin Home
FMW Component on Agent Home

=====================================================
### Patch/Mitigate FMW component on OMS Home EM 13.5
=====================================================

Note : Perform these steps on all OMS homes in case of Multi OMS setup

find /u01/app/oracle/middleware -name setupinfo.txt
find /u01/app/oracle/middleware -name portlist.ini

--- Find FMW from ORACLE_BASE
[oracle@ol7-em135 ~]$ find /u01/app/oracle -name middleware
/u01/app/oracle/middleware
[oracle@ol7-em135 ~]$

--- Navigate to location 
[oracle@ol7-em135 ~]$ cd /u01/app/oracle/middleware/oracle_common/modules/thirdparty/

--- Run the below command
[oracle@ol7-em135 thirdparty]$ zip -q -d log4j-2.11.1.jar org/apache/logging/log4j/core/lookup/JndiLookup.class

--- Verify removal of class on the LOG4J core jar
[oracle@ol7-em135 thirdparty]$ unzip -l log4j-2.11.1.jar | grep JndiLookup.class
[oracle@ol7-em135 thirdparty]$

--- Restart OMS server 
[oracle@ol7-em135 thirdparty]$ /u01/app/oracle/middleware/bin/emctl stop oms -all
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation.  All rights reserved.
Stopping Oracle Management Server...
WebTier Successfully Stopped
Oracle Management Server Successfully Stopped
AdminServer Successfully Stopped
Oracle Management Server is Down
JVMD Engine is Down

[oracle@ol7-em135 thirdparty]$ /u01/app/oracle/middleware/bin/emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up
JVMD Engine is Up

[oracle@ol7-em135 thirdparty]$ /u01/app/oracle/middleware/bin/emctl status oms
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation.  All rights reserved.
WebTier is Up
Oracle Management Server is Up
JVMD Engine is Up
[oracle@ol7-em135 thirdparty]$


==================================================
### Patch/Mitigate Agent Home
==================================================

Note: These Steps have to be performed on each agent home

--- Find Agent Binaries
[oracle@ol7-em135 ~]$ ps -ef|grep [a]gent_inst
oracle   32531     1  0 22:53 pts/0    00:00:00 /u01/app/oracle/agent/agent_13.5.0.0.0/perl/bin/perl /u01/app/oracle/agent/agent_13.5.0.0.0/bin/emwd.pl agent /u01/app/oracle/agent/agent_inst/sysman/log/emagent.nohup
[oracle@ol7-em135 ~]$

--- Navigate to location  
[oracle@ol7-em135 ~]$ cd /u01/app/oracle/agent/agent_13.5.0.0.0/oracle_common/modules/thirdparty/
[oracle@ol7-em135 thirdparty]$

--- Run the below command
[oracle@ol7-em135 thirdparty]$ zip -q -d log4j-2.11.1.jar org/apache/logging/log4j/core/lookup/JndiLookup.class
[oracle@ol7-em135 thirdparty]$

--- Verify removal of class on the LOG4J core jar
[oracle@ol7-em135 thirdparty]$ unzip -l log4j-2.11.1.jar | grep JndiLookup.class
[oracle@ol7-em135 thirdparty]$

--- Restart the Agent  
[oracle@ol7-em135 thirdparty]$ /u01/app/oracle/agent/agent_13.5.0.0.0/bin/emctl stop agent
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation.  All rights reserved.
Stopping agent ... stopped.

[oracle@ol7-em135 thirdparty]$ /u01/app/oracle/agent/agent_13.5.0.0.0/bin/emctl start agent
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation.  All rights reserved.
Starting agent .............. started.

[oracle@ol7-em135 thirdparty]$ /u01/app/oracle/agent/agent_13.5.0.0.0/bin/emctl status agent
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version          : 13.5.0.0.0
OMS Version            : 13.5.0.0.0
Protocol Version       : 12.1.0.1.0
Agent Home             : /u01/app/oracle/agent/agent_inst
Agent Log Directory    : /u01/app/oracle/agent/agent_inst/sysman/log
Agent Binaries         : /u01/app/oracle/agent/agent_13.5.0.0.0
Core JAR Location      : /u01/app/oracle/agent/agent_13.5.0.0.0/jlib
Agent Process ID       : 12927
Parent Process ID      : 12873
Agent URL              : https://ol7-em135.localdomain:3872/emd/main/
Local Agent URL in NAT : https://ol7-em135.localdomain:3872/emd/main/
Repository URL         : https://ol7-em135.localdomain:4903/empbs/upload
Started at             : 2021-12-16 23:34:05
Started by user        : oracle
Operating System       : Linux version 5.4.17-2136.300.7.el7uek.x86_64 (amd64)
Number of Targets      : 35
Last Reload            : (none)
Last successful upload                       : 2021-12-16 23:34:18
Last attempted upload                        : 2021-12-16 23:34:18
Total Megabytes of XML files uploaded so far : 0.02
Number of XML files pending upload           : 0
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 55.66%
Collection Status                            : Collections enabled
Heartbeat Status                             : Ok
Last attempted heartbeat to OMS              : 2021-12-16 23:34:11
Last successful heartbeat to OMS             : 2021-12-16 23:34:11
Next scheduled heartbeat to OMS              : 2021-12-16 23:35:13

---------------------------------------------------------------
Agent is Running and Ready
[oracle@ol7-em135 thirdparty]$

--- NOTE: thirdparty does not exists at agent_inst
[vagrant@ol7-em135 ~]$ cd /u01/app/oracle/agent/agent_inst
[vagrant@ol7-em135 agent_inst]$ ls
bin  diag  install  internal  oracle-dfw-0.tmp  sysman
[vagrant@ol7-em135 agent_inst]$


==================================================
### Patch/Mitigate DB Plug-in Home
==================================================

--- Find gc_inst
[oracle@ol7-em135 ~]$ find /u01/app/oracle -name gc_inst
/u01/app/oracle/gc_inst
[oracle@ol7-em135 ~]$ cd /u01/app/oracle/gc_inst

--- Locate log4j*.jar on your system using the following command
[oracle@ol7-em135 gc_inst]$ find . -name log4j*2.8.2*.jar -print
./user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/_WL_user/emdb/1danf1/database/jet/emsaasui/emcdbms-ui/ear/APP-INF/lib/log4j-api-2.8.2.jar
./user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/_WL_user/emdb/1danf1/database/jet/emsaasui/emcdbms-ui/ear/APP-INF/lib/log4j-core-2.8.2.jar
./user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/_WL_user/emdb/1danf1/database/jet/emsaasui/emcdbms-ui/ear/APP-INF/lib/log4j-web-2.8.2.jar
[oracle@ol7-em135 gc_inst]$

--- To identify the Log4j version use the below command 
--- (Ensure the log 4j version is indeed 2.8.2 )
[oracle@ol7-em135 gc_inst]$ unzip -p log4j-core-2.8.2.jar META-INF/MANIFEST.MF
[oracle@ol7-em135 gc_inst]$ unzip -p log4j-core-2.8.2.jar META-INF/MANIFEST.MF
[oracle@ol7-em135 gc_inst]$ unzip -p log4j-web-2.8.2.jar META-INF/MANIFEST.MF

--- Delete the following files
[oracle@ol7-em135 gc_inst]$ find . -name log4j*2.8.2*.jar -exec ls -l {} \;
-rw-r-----. 1 oracle oinstall 228154 May  4  2020 ./user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/_WL_user/emdb/1danf1/database/jet/emsaasui/emcdbms-ui/ear/APP-INF/lib/log4j-api-2.8.2.jar
-rw-r-----. 1 oracle oinstall 1407853 May  4  2020 ./user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/_WL_user/emdb/1danf1/database/jet/emsaasui/emcdbms-ui/ear/APP-INF/lib/log4j-core-2.8.2.jar
-rw-r-----. 1 oracle oinstall 32684 May  4  2020 ./user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/_WL_user/emdb/1danf1/database/jet/emsaasui/emcdbms-ui/ear/APP-INF/lib/log4j-web-2.8.2.jar
[oracle@ol7-em135 gc_inst]$

[oracle@ol7-em135 gc_inst]$ find . -name log4j*2.8.2*.jar -exec rm -fv {} \;
removed ‘./user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/_WL_user/emdb/1danf1/database/jet/emsaasui/emcdbms-ui/ear/APP-INF/lib/log4j-api-2.8.2.jar’
removed ‘./user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/_WL_user/emdb/1danf1/database/jet/emsaasui/emcdbms-ui/ear/APP-INF/lib/log4j-core-2.8.2.jar’
removed ‘./user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/_WL_user/emdb/1danf1/database/jet/emsaasui/emcdbms-ui/ear/APP-INF/lib/log4j-web-2.8.2.jar’
[oracle@ol7-em135 gc_inst]$

December 8, 2021

Find Statistics and SQL Using Index

Filed under: 11g,oracle,performance — mdinh @ 10:05 pm

Database is facing issues with enq: TX – allocate ITL entry.

Research to find Troubleshooting waits for ‘enq: TX – allocate ITL entry’ (Doc ID 1472175.1)

Index DINH.INFO_IX1 was rebuilt.

To find SQL using index DINH.INFO_IX1- https://github.com/jkstill/oracle-script-lib/blob/master/sql/find-index-sql.sql

After running find-index-sql.sql, get execution statistics and history for a SQL using Doc ID 1371778.1.

The document illustrates how to get execution statistics and history for a SQL using sql_id using Data Dictionary queries for both queries in memory and those in the AWR.

DEMO:

-- Find SQL where an index has been used

SQL> @find-index-sql.sql

old   2:    :b_index_owner := '&&v_index_owner';
new   2:    :b_index_owner := 'DINH';
old   3:    :b_index_name := '&&v_index_name';
new   3:    :b_index_name := 'INFO_IX1';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00


SQL_ID        PLAN_HASH_VALUE
------------- ---------------
272kpc0q766tx      1690776192
7wd9njg9sy9mk      1635894828
gu9t5kgmy3pp6       698638001

Elapsed: 00:00:46.75

SQL>

======================================================================
--- sql_history.sql for SQL_ID: 272kpc0q766tx using index DINH.INFO_IX1
======================================================================

SQL> @sql_history.sql

--- From Memory

Enter value for sql_id: 272kpc0q766tx
old  16: where sql_id = '&sql_id'
new  16: where sql_id = '272kpc0q766tx'

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE FIRST_LOAD_TIME      LAST_LOAD_TIME       OUTLINE_CATEGORY     SQL_PROFILE                      EXECUTIONS
------------- ------------ --------------- -------------------- -------------------- -------------------- -------------------------------- ----------
272kpc0q766tx            0      1690776192 2018-10-23/11:20:35  2021-10-21/05:55:23                                                                29
272kpc0q766tx            1      1690776192 2018-10-23/11:20:35  2021-07-29/19:55:22                                                          29268833
272kpc0q766tx            3      1690776192 2018-10-23/11:20:35  2021-12-08/08:00:26                                                               441
272kpc0q766tx            4      1690776192 2018-10-23/11:20:35  2021-05-07/05:16:48                                                              3583
272kpc0q766tx            6      1690776192 2018-10-23/11:20:35  2021-12-01/21:18:11                                                            413747
272kpc0q766tx            8      1690776192 2018-10-23/11:20:35  2021-12-01/22:07:51                                                               164
272kpc0q766tx           37      1690776192 2018-10-23/11:20:35  2021-04-24/10:51:28                                                           3394967
272kpc0q766tx           38      1690776192 2018-10-23/11:20:35  2021-04-24/16:02:52                                                             43383
272kpc0q766tx           49      1690776192 2018-10-23/11:20:35  2021-07-21/18:27:05                                                            466811
272kpc0q766tx           51      1690776192 2018-10-23/11:20:35  2021-07-21/22:27:11                                                              5238
272kpc0q766tx           54      1690776192 2018-10-23/11:20:35  2021-08-07/22:48:03                                                           3286771
272kpc0q766tx           56      1690776192 2018-10-23/11:20:35  2021-08-08/05:01:11                                                              9835
272kpc0q766tx           67      1690776192 2018-10-23/11:20:35  2021-10-02/06:52:24                                                            126753
272kpc0q766tx           68      1690776192 2018-10-23/11:20:35  2021-10-02/10:41:18                                                               138

14 rows selected.

--- From AWR

Enter value for sql_id: 272kpc0q766tx
old  15: where sql_id = '&sql_id'
new  15: where sql_id = '272kpc0q766tx'

SQL_ID           SNAP_ID PLAN_HASH_VALUE SQL_PROFILE                      EXECUTIONS_TOTAL
------------- ---------- --------------- -------------------------------- ----------------
272kpc0q766tx     353952      1690776192                                          35878169
272kpc0q766tx     355246      1690776192                                          37443248

SQL>

======================================================================
--- sql_history.sql for SQL_ID: 7wd9njg9sy9mk using index DINH.INFO_IX1
======================================================================

SQL> @sql_history.sql

--- From Memory

Enter value for sql_id: 7wd9njg9sy9mk
old  16: where sql_id = '&sql_id'
new  16: where sql_id = '7wd9njg9sy9mk'

no rows selected

--- From AWR

Enter value for sql_id: 7wd9njg9sy9mk
old  15: where sql_id = '&sql_id'
new  15: where sql_id = '7wd9njg9sy9mk'

no rows selected

SQL>

======================================================================
--- sql_history.sql for SQL_ID: gu9t5kgmy3pp6 using index DINH.INFO_IX1
======================================================================

SQL> @sql_history.sql

--- From Memory

Enter value for sql_id: gu9t5kgmy3pp6
old  16: where sql_id = '&sql_id'
new  16: where sql_id = 'gu9t5kgmy3pp6'

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE FIRST_LOAD_TIME      LAST_LOAD_TIME       OUTLINE_CATEGORY     SQL_PROFILE                      EXECUTIONS
------------- ------------ --------------- -------------------- -------------------- -------------------- -------------------------------- ----------
gu9t5kgmy3pp6            0       698638001 2018-10-23/11:20:27  2021-12-08/08:05:10                                                               268
gu9t5kgmy3pp6            1       698638001 2018-10-23/11:20:27  2021-08-07/23:19:17                                                           5343273
gu9t5kgmy3pp6            5       698638001 2018-10-23/11:20:27  2021-12-01/18:42:33                                                            190004
gu9t5kgmy3pp6           23       698638001 2018-10-23/11:20:27  2021-04-24/14:27:12                                                           5512867
gu9t5kgmy3pp6           32       698638001 2018-10-23/11:20:27  2021-07-21/19:22:08                                                           2831328
gu9t5kgmy3pp6           39       698638001 2018-10-23/11:20:27  2021-10-02/04:16:50                                                            287105

6 rows selected.

--- From AWR

Enter value for sql_id: gu9t5kgmy3pp6
old  15: where sql_id = '&sql_id'
new  15: where sql_id = 'gu9t5kgmy3pp6'

no rows selected

SQL>

UPDATE for sql_id 7wd9njg9sy9mk:

sql_id 7wd9njg9sy9mk exist from gv$sql_plan and dba_hist_sql_plan (using find-index-sql.sql)

but not from v$sql and dba_hist_sqlstat (using Doc ID 1371778.1) which is strange.

Manually checking using DBMS_XPLAN.display_cursor and dbms_xplan.display_awr shows:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'7wd9njg9sy9mk',format=>'ALLSTATS LAST +cost +bytes')); 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID: 7wd9njg9sy9mk, child number: 0 cannot be found


Elapsed: 00:00:00.00

SQL>

SQL> SELECT * FROM TABLE(dbms_xplan.display_awr('7wd9njg9sy9mk'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID 7wd9njg9sy9mk
--------------------
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

Plan hash value: 1635894828

-----------------------------------------------------------------------------------------------
| Id  | Operation           | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT    |                         |       |       |   538 (100)|          |
|   1 |  DELETE             | *******_UPDATE_INFO     |       |       |            |          |
|   2 |   COUNT STOPKEY     |                         |       |       |            |          |
|   3 |    NESTED LOOPS     |                         |    50M|   958M|   538 (100)| 00:00:07 |
|   4 |     INDEX FULL SCAN | SYS_C00137313           |    50M|   620M|     4   (0)| 00:00:01 |
|   5 |     INDEX RANGE SCAN| **************_INFO_IX1 |     1 |     7 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


21 rows selected.

Elapsed: 00:00:00.03

SQL>

December 4, 2021

How To Resolve Tablespace Created With Windows Path In Linux

Filed under: 11g,linux,oracle — mdinh @ 3:20 pm

Now I understand the rationale for not providing DBA privileges to inexperience developers.

Currently, I am uncertain if the current environment is production or not.

Here is demo for 11.2.0.4 to demonstrate the issue and resolution.

Reference:
https://docs.oracle.com/cd/E18283_01/server.112/e17120/dfiles005.htm#i1006478

[oracle@ol7-112-dg1 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 4 14:46:37 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

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u01/oradata

----------------------------------------
--- Windows path used for datafile:
----------------------------------------

SQL> create tablespace TEST datafile 'D:\Oracle\oradata\E1Local\TEST.dbf' SIZE 16M;

Tablespace created.

SQL> set lines 200
SQL> col name for a80
SQL> select file#,name from v$datafile order by 1;

     FILE# NAME
---------- --------------------------------------------------------------------------------
         1 /u01/oradata/hawk/system01.dbf
         2 /u01/oradata/hawk/sysaux01.dbf
         3 /u01/oradata/hawk/undotbs01.dbf
         4 /u01/oradata/hawk/users01.dbf
         5 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf

SQL> alter tablespace TEST offline normal;

Tablespace altered.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST                           OFFLINE

6 rows selected.

SQL> host

----------------------------------------
--- Rename datafile from OS
----------------------------------------

[oracle@ol7-112-dg1 trace]$ mv -fv /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf /u01/oradata/test.dbf
‘/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf’ -> ‘/u01/oradata/test.dbf’

[oracle@ol7-112-dg1 trace]$ exit
exit

SQL> alter tablespace TEST rename datafile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf' TO '/u01/oradata/test.dbf';

Tablespace altered.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST                           OFFLINE

6 rows selected.

SQL> alter tablespace TEST online;

Tablespace altered.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST                           ONLINE

6 rows selected.

SQL> drop tablespace TEST including contents and datafiles;

Tablespace dropped.

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

----------------------------------------
--- Alert Log: NO ERRORS!
----------------------------------------

[oracle@ol7-112-dg1 trace]$ tail -25 alert_hawk.log
LNS: Standby redo logfile selected for thread 1 sequence 126 for destination LOG_ARCHIVE_DEST_2
Sat Dec 04 14:47:17 2021
create tablespace TEST datafile 'D:\Oracle\oradata\E1Local\TEST.dbf' SIZE 16M
Completed: create tablespace TEST datafile 'D:\Oracle\oradata\E1Local\TEST.dbf' SIZE 16M
Sat Dec 04 14:48:08 2021
alter tablespace TEST offline normal
Completed: alter tablespace TEST offline normal
Sat Dec 04 14:48:08 2021
Starting background process SMCO
Sat Dec 04 14:48:08 2021
SMCO started with pid=36, OS id=5159
Sat Dec 04 14:48:19 2021
Checker run found 2 new persistent data failures
Sat Dec 04 14:49:33 2021
alter tablespace TEST rename datafile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf' TO '/u01/oradata/test.dbf'
Completed: alter tablespace TEST rename datafile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf' TO '/u01/oradata/test.dbf'
Sat Dec 04 14:49:42 2021
Checker run found 1 new persistent data failures
Sat Dec 04 14:50:00 2021
alter tablespace TEST online
Completed: alter tablespace TEST online
Sat Dec 04 14:50:33 2021
drop tablespace TEST including contents and datafiles
Deleted file /u01/oradata/test.dbf
Completed: drop tablespace TEST including contents and datafiles
[oracle@ol7-112-dg1 trace]$

----------------------------------------
--- Resolution for 19c with ASM
----------------------------------------

SQL> alter session set container=PDB;
SQL> ALTER DATABASE MOVE DATAFILE '/u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf' TO '+DATAC1';

November 6, 2021

Flashback DataGuard Primary Database

Filed under: 11g,Dataguard,dgmgrl,flashback — mdinh @ 1:26 am

Practicing flashback just in case it is needed during production deployment.

The disadvantage is commands will be different for each environment vs simple and consistent

recover managed standby database cancel and

recover managed standby database using current logfile disconnect

--------------------------------------------------
--- CHECK FLASHBACK ENABLED:
--------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

### PRIMARY:
SQL> select current_scn,name,open_mode,database_role,flashback_on from v$database;

CURRENT_SCN NAME                           OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
----------- ------------------------------ -------------------- ---------------- ------------------
    1056941 HAWK                           READ WRITE           PRIMARY          YES

SQL>

-- Enable flashback if required:
alter database flashback on;
select current_scn,name,open_mode,database_role,flashback_on from v$database;

### STANDBY:
SQL> select current_scn,name,open_mode,database_role,flashback_on from v$database;

CURRENT_SCN NAME                           OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
----------- ------------------------------ -------------------- ---------------- ------------------
    1037296 HAWK                           MOUNTED              PHYSICAL STANDBY YES

SQL>

-- Enable flashback if required:
recover managed standby database cancel; 
alter database flashback on;
select current_scn,name,open_mode,database_role,flashback_on from v$database;

--------------------------------------------------
--- CREATE GRP:
--------------------------------------------------

### PRIMARY:
SQL> create restore point RP_TEST guarantee flashback database;

Restore point created.

SQL>

--------------------------------------------------
--- CHECK GRP:
--------------------------------------------------

Depending on database version, GRP is replicated to standby.

### PRIMARY:
SQL> select scn, name, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;

       SCN NAME                           GUA
---------- ------------------------------ ---
   1057112 RP_TEST                        YES

SQL>

### STANDBY:
SQL> select scn, name, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;

no rows selected

SQL>

##################################################
### FLASHBACK TO RESTORE POINT:
##################################################

### Flashback PRIMARY:
SQL> select current_scn,name,open_mode,database_role,flashback_on from v$database;

CURRENT_SCN NAME                           OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
----------- ------------------------------ -------------------- ---------------- ------------------
    1057767 HAWK                           READ WRITE           PRIMARY          YES

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2253664 bytes
Variable Size             469765280 bytes
Database Buffers         1124073472 bytes
Redo Buffers                7319552 bytes
Database mounted.

SQL> select scn, name, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;

       SCN NAME                           GUA
---------- ------------------------------ ---
   1057112 RP_TEST                        YES

SQL> flashback database to restore point RP_TEST;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL>

### Flashback STANDBY:
SQL> recover managed standby database cancel;
ORA-16136: Managed Standby Recovery not active

--- (USE PRIMARY v$restore_point.SCN)
SQL> flashback standby database to scn &scn;
Enter value for scn: 1057112
old   1: flashback standby database to scn &scn
new   1: flashback standby database to scn 1057112

Flashback complete.

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL>

--------------------------------------------------
--- Check DG using dgmgrl:
--------------------------------------------------

DGMGRL> show configuration

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Databases:
    hawk      - Primary database
    hawk_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawk

Database - hawk

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    hawk

Database Status:
SUCCESS

DGMGRL> show database hawk_stby

Database - hawk_stby

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

Database Status:
SUCCESS

DGMGRL>

Q.E.D.

November 3, 2021

AHFCTL setresourcelimit CPU

Filed under: AHF_TFA — mdinh @ 2:54 am

It was detemrined that AHF was using too much CPU affecting RAC cluster performance.

AHF was shutdown temporarily over the weekend to prevent issues with application.

--- SHORT version to limit CPU resource:

# /opt/oracle.ahf/bin/ahfctl setresourcelimit -value 0.5
Tool: tfa, Resource: cpu, Limit value: 0.5 set successfully

# /opt/oracle.ahf/bin/ahfctl getresourcelimit
Tool: tfa, Resource: cpu, Limit value: 0.5
Tool: tfa, Resource: kmem no resource limit set
Tool: tfa, Resource: swmem no resource limit set
--- LONG version - start AHF and limit CPU resource:

# /opt/oracle.ahf/bin/ahfctl startahf -all
Starting TFA..
Waiting up to 100 seconds for TFA to be started..
. . . . . 
. . . . . 
. . . . . 
. . . . . 
. . . . . 
. . . . . 
. . . . . 
. . . . . 
. . . . . 
Successfully started TFA Process..
. . . . . 
TFA Started and listening for commands

INFO: Starting exachk scheduler in background. 

Details for the process can be found at /u01/app/grid/oracle.ahf/data/xxxxxxxxxxxxxx-21acd-9gu5j2/diag/exachk/compliance_start_251021_112642.log

# /opt/oracle.ahf/bin/ahfctl setresourcelimit -value 0.5
Tool: tfa, Resource: cpu, Limit value: 0.5 set successfully

# /opt/oracle.ahf/bin/ahfctl getresourcelimit
Tool: tfa, Resource: cpu, Limit value: 0.5
Tool: tfa, Resource: kmem no resource limit set
Tool: tfa, Resource: swmem no resource limit set

# /opt/oracle.ahf/bin/ahfctl set autodiagcollect=ON
Successfully set autodiagcollect=ON
.-------------------------------------------------.
|           xxxxxxxxxxxxxx-21acd-9gu5j2           |
+-----------------------------------------+-------+
| Configuration Parameter                 | Value |
+-----------------------------------------+-------+
| Auto Diagcollection ( autodiagcollect ) | ON    |
'-----------------------------------------+-------'

# /opt/oracle.ahf/bin/ahfctl set chaautocollect=ON
Successfully set chaautocollect=ON
.---------------------------------.
|   xxxxxxxxxxxxxx-21acd-9gu5j2   |
+-------------------------+-------+
| Configuration Parameter | Value |
+-------------------------+-------+
| chaautocollect          | ON    |
'-------------------------+-------'

# /opt/oracle.ahf/bin/ahfctl set minicollection=ON
Successfully set minicollection=ON
.-----------------------------------------------------------.
|                xxxxxxxxxxxxxx-21acd-9gu5j2                |
+---------------------------------------------------+-------+
| Configuration Parameter                           | Value |
+---------------------------------------------------+-------+
| Generation of Mini Collections ( minicollection ) | ON    |
'---------------------------------------------------+-------'

# /opt/oracle.ahf/bin/ahfctl statusahf
.--------------------------------------------------------------------------------------------------------------------.
| Host                        | Status of TFA | PID    | Port | Version    | Build ID             | Inventory Status |
+-----------------------------+---------------+--------+------+------------+----------------------+------------------+
| xxxxxxxxxxxxxx-21acd-9gu5j2 | RUNNING       | 151372 | 5000 | 21.1.4.0.0 | 21140020210628122659 | COMPLETE         |
| xxxxxxxxxxxxxx-21acd-9gu5j1 | RUNNING       |  89572 | 5000 | 21.1.4.0.0 | 21140020210628122659 | COMPLETE         |
'-----------------------------+---------------+--------+------+------------+----------------------+------------------'

------------------------------------------------------------

Master node = xxxxxxxxxxxxxx-21acd-9gu5j1

exachk daemon version = 21.1.4

Install location = /opt/oracle.ahf/exachk

Started at = Mon Oct 25 11:27:10 CDT 2021

Scheduler type = TFA Scheduler

------------------------------------------------------------
ID: exachk.autostart_client_exatier1
------------------------------------------------------------
AUTORUN_FLAGS  =  -usediscovery -profile exatier1 -syslog -dball -showpass -tag autostart_client_exatier1 -readenvconfig
COLLECTION_RETENTION  =  7
AUTORUN_SCHEDULE  =  3 2 * * 1,2,3,4,5,6
------------------------------------------------------------
------------------------------------------------------------
ID: exachk.autostart_client
------------------------------------------------------------
AUTORUN_FLAGS  =  -usediscovery -syslog -tag autostart_client -readenvconfig
COLLECTION_RETENTION  =  14
AUTORUN_SCHEDULE  =  3 3 * * 0
------------------------------------------------------------

Next auto run starts on Oct 26, 2021 02:03:00

ID:exachk.AUTOSTART_CLIENT_EXATIER1

# /opt/oracle.ahf/bin/tfactl get collect -match
.------------------------------------------------------------------------------.
|                          xxxxxxxxxxxxxx-21acd-9gu5j2                         |
+----------------------------------------------------------------------+-------+
| Configuration Parameter                                              | Value |
+----------------------------------------------------------------------+-------+
| ISA Data Gathering ( collection.isa )                                | ON    |
| collectTrm                                                           | OFF   |
| collectAllDirsByFile                                                 | ON    |
| Auto Diagcollection ( autodiagcollect )                              | ON    |
| Generation of Mini Collections ( minicollection )                    | ON    |
| chaautocollect                                                       | ON    |
| Maximum File Collection Size (MB) ( maxFileCollectionSize )          | 5120  |
| Maximum Collection Size of Core Files (MB) ( maxCoreCollectionSize ) | 500   |
| minTimeForAutoDiagCollection                                         | 300   |
'----------------------------------------------------------------------+-------'

October 22, 2021

Recover Dropped User Using Standby Database

Filed under: Dataguard — mdinh @ 12:18 am

A critical user was accidentally dropped around one week ago and will need be recovered.

Here are semi detailed steps used to flashback standby database, export user from standby, and import to primary.

============================================================
### Recover user that was deleted about week ago.
============================================================

Request Created: 14/Oct/2021 6:41 AM

DB version: 12.1.0.2.0 

==============================
### CURRENT FLASHBACK INFO:
==============================

SQL>

NAME  DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
----- -------------- --------- ----------------
ORC1  STANDBY1       MOUNTED   PHYSICAL STANDBY

 INST OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME RETENTION_TARGET FLASHBACK_MB EST_FLASHBACK_MB
----- -------------------- --------------------- ---------------- ------------ ----------------
    1 984681951010         04-OCT-2021 18:44:28  7200             1767000      895861

CURRENT_SCN
------------
985044762265

SQL>

==============================
### STOP STANDBY APPLY:
==============================

DGMGRL> edit database 'STANDBY1' set state='APPLY-OFF';
Succeeded.

DGMGRL>

========================================
### FLASHBACK USING TIMESTAMP FAILED:
========================================

SQL> shutdown abort;
SQL> startup mount restrict exclusive;

SQL> flashback database to timestamp TO_TIMESTAMP('2021-10-05 00:00:00','YYYY-MM-DD HH24:MI:SS');
flashback database to timestamp TO_TIMESTAMP('2021-10-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.
SQL>

========================================
### FLASHBACK USING SCN: 
to_timestamp can't convert to SCN properly
========================================

SQL> flashback database to scn 984681951011;

========================================
### FLASHBACK WILL EVENTUALLY FAIL:
========================================

SQL> flashback database to scn 984681951011;

flashback database to scn 984681951011
*
ERROR at line 1:
ORA-38861: flashback recovery stopped before reaching recovery target

SQL> 

========================================
### CHECK ALERT LOG:
========================================

Thu Oct 14 13:59:52 2021
Errors in file /u01/app/oracle/diag/rdbms/STANDBY1/ORC1/trace/ORC1_pr00_12838.trc:
ORA-00283: recovery session canceled due to errors
ORA-38861: flashback recovery stopped before reaching recovery target
ORA-16016: archived log for thread 1 sequence# 477183 unavailable
ORA-38861: signalled during: flashback database to scn 984681951011...

========================================
### RESTORE ARCHIVELOG from alert log:
========================================

RMAN> restore archivelog logseq 477183;

========================================
### CONTINUE FLASHBACK:
========================================

SQL> flashback database to scn 984681951011;

========================================
### DO NOT OPEN READ ONLY 
WITHOUT CANCELING REDO APPLY WITHOUT ADG.
========================================

Active Data Guard enables read-only access to a physical standby database while Redo Apply is active.

SQL> recover managed standby database cancel;
SQL> alter database open read only;

========================================
### CHECK FOR DROPPED USER:
========================================

SQL> select created, username from dba_users where username='XXX';

CREATED                    USERNAME
-------------------------- --------
2008-SEP-18 20:19:33       XXX
SQL>

========================================
### USING DATAPUMP DID NOT WORK:
========================================

How To Use DataPump Export (EXPDP) To Export From Physical Standby Database (Doc ID 1356592.1)

--- For physical standby
We can execute exp in physical standby database when it is in read only
https://dbaminds.wordpress.com/2016/01/07/perform-export-expdp-from-physical-standby-and-logical-standby/

--- Use Snapshot Standby - did not test.
https://dohdatabase.com/2021/04/22/datapump-export-from-data-guard/

========================================
### EXP WORKED: PERFORMED BY CLIENT
========================================

### From STANDBY:
$ cat exp.par
file=exp.dmp
compress=n
grants=y
indexes=y
direct=y
log=exp.log
rows=y
consistent=y
owner=schema
triggers=y
constraints=y

exp userid=system parfile=exp.par
scp exp.dmp oracle@target:/home/oracle/

### From PRIMARY:
$ cat imppar
file=exp.dmp
grants=y
indexes=y
rows=y
log=imp.log
fromuser=schema
commit=n
constraints=y
compile=y

imp userid=system parfile=imp.par

========================================
### RECOVER STANDBY TO CURRENT TIME:
========================================

SQL> recover managed standby database using current logfile parallel 4 disconnect;
Media recovery complete.
SQL>

========================================
### RESTORE ARCHIVELOG TO FRA IS BEING DELETED:
========================================

Example:
Thu Oct 14 18:02:30 2021
Deleted Oracle managed file +FRA/STANDBY1/ARCHIVELOG/2021_10_14/thread_1_seq_477816.752.1085939417

Thu Oct 14 18:02:31 2021
Deleted Oracle managed file +FRA/STANDBY1/ARCHIVELOG/2021_10_14/thread_1_seq_477794.1120.1085939341

========================================
### RESTORE ARCHIVELOG TO NEW DESTINATION:
========================================

mkdir -p /ubb1/rman/ORC1/archivelog

========================================
### CHECK ALERT LOG FOR GAP SEQUENCE:
========================================

grep 'Fetching gap sequence in thread' /u01/app/oracle/diag/rdbms/STANDBY1/ORC1/trace/alert_ORC1.log

========================================
### RESOLVE ARCHIVE GAP:
========================================

Fetching gap sequence in thread 1, gap sequence 477604-477604

RMAN> run {
set archivelog destination to '/ubb1/rman/ORC1/archivelog';
restore archivelog from sequence 477604 until sequence 477604;}2> 3>

========================================
### FOR REFERENCE: 157 gap sequences
========================================

$ grep -c 'Fetching gap sequence in thread' /u01/app/oracle/diag/rdbms/STANDBY1/ORC1/trace/alert_ORC1.log
157

========================================
### RECOVER ALL ARCHIVELOG TO SAVE TIME:
========================================
 
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
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
8723                         1        1 LGWR         RFS       RECEIVING       483532   119582      0
29047                        1        1 N/A          MRP0      APPLYING_LOG    477715  1545345      0

SQL>

========================================
### RESTORE ARCHIVELOG UNTIL SEQUENCE 483515:
========================================

RMAN> run {
set archivelog destination to '/ubb1/rman/ORC1/archivelog';
restore archivelog from sequence 477715 until sequence 483515;}2> 3>

ALTERNATIVE: 
set archivelog destination to '/ubb1/rman/ORC1/archivelog';
restore archivelog from sequence 477715 until sequence 483515;

========================================
### ENABLE REDO APPLY USING DGMGRL:
========================================

SQL> recover managed standby database cancel;
Media recovery complete.
SQL>

DGMGRL> show configuration

Configuration - linkdg

  Protection Mode: MaxPerformance
  Members:
  PRIMARY - Primary database
    STANDBY1 - Physical standby database 
      Error: ORA-16810: multiple errors or warnings detected for the database

    STANDBY2 - Physical standby database 
    SBY3     - Physical standby database 
      Warning: ORA-16532: Oracle Data Guard broker configuration does not exist

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 28 seconds ago)

DGMGRL> show database STANDBY1

Database - STANDBY1

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

Database Status:
SUCCESS

DGMGRL> edit database 'STANDBY1' set state='APPLY-ON';
Succeeded.

DGMGRL> show database STANDBY1

Database - STANDBY1

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 6.01 MByte/s
  Real Time Query:    OFF
  Instance(s):
    ORC1

Database Status:
SUCCESS

DGMGRL> validate database STANDBY1;

  Database Role:     Physical standby database
  Primary Database:  PRIMARY

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

DGMGRL> show configuration

Configuration - linkdg

  Protection Mode: MaxPerformance
  Members:
  PRIMARY - Primary database
    STANDBY1 - Physical standby database
    STANDBY2 - Physical standby database
    SBY3     - Physical standby database
      Warning: ORA-16532: Oracle Data Guard broker configuration does not exist

Fast-Start Failover: DISABLED

Configuration Status:
WARNING   (status updated 15 seconds ago)

DGMGRL>

Q.E.D.

October 12, 2021

How To Change RMAN Config For Standby DB

Filed under: RMAN — mdinh @ 9:19 pm

Here is the typical error when changing RMAN configuration for standby database.

[oracle@ol7-112-dg2 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Oct 12 21:02:07 2021
Version 19.3.0.0.0

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

connected to target database: HAWK (DBID=3331620895, not open)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWK_STBY are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_hawk.f'; # default

RMAN> configure retention policy to recovery window of 7 days;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 10/12/2021 21:03:28
RMAN-05021: this configuration cannot be changed for a BACKUP or STANDBY control file

RMAN> exit


Recovery Manager complete.
[oracle@ol7-112-dg2 ~]$

There are suggestions found from MOS. Yuck!

Steps to recreate a Physical Standby Controlfile (Doc ID 459411.1)

Step By Step Guide On How To Recreate Standby Control File When Datafiles Are On ASM And Using Oracle Managed Files (Doc ID 734862.1)

Thanks to teammate for the easy method.

[oracle@ol7-112-dg2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 12 21:04:04 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> set pages 200
SQL> col value format a50
SQL> select name, database_role from v$database;

NAME      DATABASE_ROLE
--------- ----------------
HAWK      PHYSICAL STANDBY

SQL> select * from v$rman_configuration;

no rows selected

SQL> set serveroutput on
SQL> !vi t.sql

SQL> @t.sql
SQL> DECLARE
  2     x NUMBER;
  3    BEGIN
  4      x := dbms_backup_restore.setconfig('RETENTION POLICY','TO RECOVERY WINDOW OF 7 DAYS');
  5      dbms_output.put_line('setconfig returned ' || x);
  6    END;
  7  /
setconfig returned 1

PL/SQL procedure successfully completed.

SQL> select * from v$rman_configuration;

     CONF# NAME
---------- -----------------------------------------------------------------
VALUE                                                  CON_ID
-------------------------------------------------- ----------
         1 RETENTION POLICY
TO RECOVERY WINDOW OF 7 DAYS                                0


SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@ol7-112-dg2 ~]$


[oracle@ol7-112-dg2 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Oct 12 21:08:47 2021
Version 19.3.0.0.0

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

connected to target database: HAWK (DBID=3331620895, not open)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWK_STBY are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_hawk.f'; # default

RMAN> exit


Recovery Manager complete.
[oracle@ol7-112-dg2 ~]$

IMPORTANT: Delete existing configuration before updating with another.

Here is a demonstration of what happens when configuration is not deleted.

SQL> select * from v$rman_configuration;

     CONF# NAME                 VALUE                                                  CON_ID
---------- -------------------- -------------------------------------------------- ----------
         1 RETENTION POLICY     TO RECOVERY WINDOW OF 7 DAYS                                0

SQL> @t.sql
SQL> DECLARE
  2     x NUMBER;
  3    BEGIN
  4      x := dbms_backup_restore.setconfig('RETENTION POLICY','TO REDUNDANCY 1');
  5      dbms_output.put_line('setconfig returned ' || x);
  6    END;
  7  /
setconfig returned 2

PL/SQL procedure successfully completed.

SQL> select * from v$rman_configuration;

     CONF# NAME                 VALUE                                                  CON_ID
---------- -------------------- -------------------------------------------------- ----------
         1 RETENTION POLICY     TO RECOVERY WINDOW OF 7 DAYS                                0
         2 RETENTION POLICY     TO REDUNDANCY 1                                             0

SQL>

--------------------------------------------------

RMAN> show all;

RMAN configuration parameters for database with db_unique_name HAWK_STBY are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_hawk.f'; # default

RMAN>

--------------------------------------------------

SQL> exec DBMS_BACKUP_RESTORE.DELETECONFIG(1);

PL/SQL procedure successfully completed.

SQL> select * from v$rman_configuration;

     CONF# NAME                 VALUE                                                  CON_ID
---------- -------------------- -------------------------------------------------- ----------
         2 RETENTION POLICY     TO REDUNDANCY 1                                             0

SQL>

--------------------------------------------------

RMAN> show all;

RMAN configuration parameters for database with db_unique_name HAWK_STBY are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_hawk.f'; # default

RMAN>
Next Page »

Blog at WordPress.com.