Thinking Out Loud

January 28, 2022

Silent Install Oracle DB 19.3

Filed under: 19c,linux,response_file,runInstaller — mdinh @ 10:16 pm

I was asked to Install Oracle 19c EE with the provided information:

DB: /app/oracle/product/19.3.0
SW: /backup/media/oracle19.3_database/LINUX.X64_193000_db_home.zip

Not knowing anything about the environment, more due diligence is required and here are the steps taken:

--- Find OS info:

oracle@oradev001: ~ $ uname -an
Linux oradev001 3.10.0-1160.31.1.el7.x86_64 #1 SMP Wed May 26 20:18:08 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux

--- Create new directory:

oracle@oradev001: ~ $ ls -ld /app/oracle/product/19.3.0
ls: cannot access /app/oracle/product/19.3.0: No such file or directory

oracle@oradev001: ~ $ mkdir -p /app/oracle/product/19.3.0
oracle@oradev001: ~ $ ls -ld /app/oracle/product/19.3.0
drwxr-xr-x 2 oracle dba 4096 Jan 26 12:44 /app/oracle/product/19.3.0

--- Extract software:

oracle@oradev001: ~ $ cd /app/oracle/product/19.3.0
oracle@oradev001: /app/oracle/product/19.3.0 $ unzip -oq /backup/media/oracle19.3_database/LINUX.X64_193000_db_home.zip; echo $?
0

--- Extract rspfmt_dbinstall_response_schema_v19.0.0 from db_install.rsp:

oracle@oradev001: /app/oracle/product/19.3.0 $ ls -l install/response/db_install.rsp
-rw-r--r-- 1 oracle dba 19932 Feb  6  2019 install/response/db_install.rsp

oracle@oradev001: /app/oracle/product/19.3.0 $ grep responseFileVersion install/response/db_install.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0

--- Find UNIX_GROUP_NAME (oinstall or dba):

oracle@oradev001: /app/oracle/product/19.3.0 $ id oracle
uid=3000(oracle) gid=3000(dba) groups=3000(dba),54329(asmadmin),54327(asmdba)

--- Find ORACLE_BASE:

oracle@oradev001: /app/oracle/product/19.3.0 $ env|grep ORACLE_BASE
ORACLE_BASE=/app/oracle

--- Find INVENTORY_LOCATION:

oracle@oradev001: /app/oracle/product/19.3.0 $ ls -l /etc/oraInst.loc
-rw-r--r-- 1 root root 47 Nov  7  2018 /etc/oraInst.loc

--- Create and copy response file to /tmp:

oracle@oradev001: /app/oracle/product/19.3.0 $ cat db_install.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=dba
INVENTORY_LOCATION=/etc/oraInst.loc
SELECTED_LANGUAGES=en
ORACLE_HOME=/app/oracle/product/19.3.0
ORACLE_BASE=/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSBACKUPDBA_GROUP=dba
oracle.install.db.OSDGDBA_GROUP=dba
oracle.install.db.OSKMDBA_GROUP=dba
oracle.install.db.OSRACDBA_GROUP=dba
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true

--- Perform executePrereqs and Ignore:

oracle@oradev001: /app/oracle/product/19.3.0 $ ./runInstaller -silent -executePrereqs -waitforcompletion -force -responseFile /tmp/db_install.rsp
Launching Oracle Database Setup Wizard...

[WARNING] [INS-13014] Target environment does not meet some optional requirements.
   CAUSE: Some of the optional prerequisites are not met. See logs for details. /app/oraInventory/logs/InstallActions2022-01-26_01-19-52PM/installActions2022-01-26_01-19-52PM.log
   ACTION: Identify the list of failed prerequisite checks from the log: /app/oraInventory/logs/InstallActions2022-01-26_01-19-52PM/installActions2022-01-26_01-19-52PM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.

--- Install software only;

oracle@oradev001: /app/oracle/product/19.3.0 $ ./runInstaller -silent -waitforcompletion -responseFile /tmp/db_install.rsp
Launching Oracle Database Setup Wizard...

[WARNING] [INS-13014] Target environment does not meet some optional requirements.
   CAUSE: Some of the optional prerequisites are not met. See logs for details. /app/oraInventory/logs/InstallActions2022-01-26_01-28-45PM/installActions2022-01-26_01-28-45PM.log
   ACTION: Identify the list of failed prerequisite checks from the log: /app/oraInventory/logs/InstallActions2022-01-26_01-28-45PM/installActions2022-01-26_01-28-45PM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
The response file for this session can be found at:
 /app/oracle/product/19.3.0/install/response/db_2022-01-26_01-28-45PM.rsp

You can find the log of this install session at:
 /app/oraInventory/logs/InstallActions2022-01-26_01-28-45PM/installActions2022-01-26_01-28-45PM.log

As a root user, execute the following script(s):
        1. /app/oracle/product/19.3.0/root.sh

Execute /app/oracle/product/19.3.0/root.sh on the following nodes:
[oradev001]


Successfully Setup Software with warning(s).
oracle@oradev001: /app/oracle/product/19.3.0 $

--- Review ignorable errors: insufficient swap and OS kernel parameter "shmall"

oracle@oradev001: /app/oracle/product/19.3.0 $ grep -C 10 INS-13014 /app/oraInventory/logs/InstallActions2022-01-26_01-28-45PM/installActions2022-01-26_01-28-45PM.log
INFO:  [Jan 26, 2022 1:29:03 PM] ------------------List of failed Tasks------------------
INFO:  [Jan 26, 2022 1:29:03 PM] *********************************************
INFO:  [Jan 26, 2022 1:29:03 PM] Swap Size: This is a prerequisite condition to test whether sufficient total swap space is available on the system.
INFO:  [Jan 26, 2022 1:29:03 PM] Severity:IGNORABLE
INFO:  [Jan 26, 2022 1:29:03 PM] OverallStatus:VERIFICATION_FAILED
INFO:  [Jan 26, 2022 1:29:03 PM] *********************************************
INFO:  [Jan 26, 2022 1:29:03 PM] OS Kernel Parameter: shmall: This is a prerequisite condition to test whether the OS kernel parameter "shmall" is properly set.
INFO:  [Jan 26, 2022 1:29:03 PM] Severity:IGNORABLE
INFO:  [Jan 26, 2022 1:29:03 PM] OverallStatus:VERIFICATION_FAILED
INFO:  [Jan 26, 2022 1:29:03 PM] -----------------End of failed Tasks List----------------

WARNING:  [Jan 26, 2022 1:29:03 PM] [WARNING] [INS-13014] Target environment does not meet some optional requirements.
   CAUSE: Some of the optional prerequisites are not met. See logs for details. /app/oraInventory/logs/InstallActions2022-01-26_01-28-45PM/installActions2022-01-26_01-28-45PM.log
   ACTION: Identify the list of failed prerequisite checks from the log: /app/oraInventory/logs/InstallActions2022-01-26_01-28-45PM/installActions2022-01-26_01-28-45PM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.

INFO:  [Jan 26, 2022 1:29:03 PM] Advice is CONTINUE
INFO:  [Jan 26, 2022 1:29:03 PM] Completed validating state <performChecks>
INFO:  [Jan 26, 2022 1:29:03 PM] Verifying route success
INFO:  [Jan 26, 2022 1:29:03 PM] Executing initialize action at state showSummary
INFO:  [Jan 26, 2022 1:29:03 PM] Completed executing initialize action at state <showSummary>
INFO:  [Jan 26, 2022 1:29:03 PM] Waiting for completion of background operations
INFO:  [Jan 26, 2022 1:29:03 PM] Completed background operations
INFO:  [Jan 26, 2022 1:29:03 PM] Waiting for completion of background operations
oracle@oradev001: /app/oracle/product/19.3.0 $

January 22, 2022

SIMPLIFY A COMPLICATED PROCESS USING SED

Filed under: automation,awk_sed_grep,shell scripting — mdinh @ 4:08 am

For every PDB, there is a perl script used to report tablespace free the that PDB.

While I am not able to change how the process was implemented, I can make it easier.

Here is the current process.

Edit the script "tablespace_free_PDB_NAME.pl".

Change the PDB name at the below SQL command:
"alter session set container=<Your PDB name_1>"
 
Rename the script to match your PDB name 
like "tablespace_free_<Your PDB name>.pl".

The above instructions work but is time consuming, not scalable, and error prone.

Here’s a demo how to simplify the process.

1. Create template temp_tablespace_free_PDB.pl:

[oracle@ol7-19-dg1 ~]$ cat temp_tablespace_free_PDB.pl
alter session set container=vPDB;

2. Export variable PDB with <Your PDB name>

[oracle@ol7-19-dg1 ~]$ export PDB=SOAP

3. Create tablespace_free_<Your PDB name>.pl

[oracle@ol7-19-dg1 ~]$ ls tablespace_free_$PDB.pl
ls: cannot access tablespace_free_SOAP.pl: No such file or directory

[oracle@ol7-19-dg1 ~]$ sed "s/vPDB/$PDB/g" temp_tablespace_free_PDB.pl > tablespace_free_$PDB.pl

[oracle@ol7-19-dg1 ~]$ ls tablespace_free_$PDB.pl
tablespace_free_SOAP.pl
[oracle@ol7-19-dg1 ~]$

[oracle@ol7-19-dg1 ~]$ cat tablespace_free_$PDB.pl
alter session set container=SOAP;
[oracle@ol7-19-dg1 ~]$

The above solution is better but far from perfect.

If there are a dozen PDBs to implement, then manual work will have to be done a dozen time.

Here is an example using array and for loop.

There are 2 PDBs: SOAP and SCUM.

Here is the template:

[oracle@ol7-19-dg1 ~]$ cat temp_tablespace_free_PDB.pl
alter session set container=vPDB;
select sysdate from dual;
[oracle@ol7-19-dg1 ~]$

1. Create script to loop through list of PDBs:

[oracle@ol7-19-dg1 ~]$ cat create_tablespace_free_PDB.sh
#!/bin/bash
array=( SOAP SCUM )
for i in "${array[@]}"
do
  echo "$i"
  export PDB=$i
  sed "s/vPDB/$PDB/g" temp_tablespace_free_PDB.pl > tablespace_free_$PDB.pl
  ls -l tablespace_free_$PDB.pl
done
exit
[oracle@ol7-19-dg1 ~]$

2. Run create_tablespace_free_PDB.sh:

[oracle@ol7-19-dg1 ~]$ ./create_tablespace_free_PDB.sh
SOAP
-rw-r--r--. 1 oracle oinstall 60 Jan 22 03:52 tablespace_free_SOAP.pl
SCUM
-rw-r--r--. 1 oracle oinstall 60 Jan 22 03:52 tablespace_free_SCUM.pl
[oracle@ol7-19-dg1 ~]$

3. Review results:

[oracle@ol7-19-dg1 ~]$ cat tablespace_free_SOAP.pl
alter session set container=SOAP;
select sysdate from dual;
[oracle@ol7-19-dg1 ~]$

[oracle@ol7-19-dg1 ~]$ cat tablespace_free_SCUM.pl
alter session set container=SCUM;
select sysdate from dual;
[oracle@ol7-19-dg1 ~]$

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

Blog at WordPress.com.