Michael Dinh

Subscribe to Michael Dinh feed Michael Dinh
Michael T. Dinh, Oracle DBA
Updated: 14 hours 42 min ago

GoldenGate – Supplemental Logging Is A Mess

Tue, 2020-06-02 22:22

I was tasked to find supplemental logging details for Oracle database used with GoldenGate.

Note: this is not a pluggable database.

With ADD TRANDATA, use dba_log_groups and dba_log_group_columns.

With ADD SCHEMATRANDATA, use select * from table(logmnr$always_suplog_columns( SCHEMA, TABLE ));

Basically, one would need to run the query with logmnr pipeline function for all the tables in the schema.

Here is one process I used.

Create info_schematrandata.prm

$ cat info_schematrandata.prm
dblogin USERID ggs, PASSWORD *
info schematrandata *

Run ggsci using info_schematrandata.prm (full path is required)

$ ggsci paramfile /home/oracle/working/dinh/info_schematrandata.prm > info_schematrandata.log

Here is example for results (actual contains 12 schemas)

$ grep -i enable info_schematrandata.log
2020-06-01 05:19:35  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema "SCOTT".
2020-06-01 05:19:35  INFO    OGG-01981  Schema level supplemental logging is enabled on schema "SCOTT" for all columns.

After finding the schemas, use logmnr pipeline function to find all the details.

select * from table(logmnr$always_suplog_columns('SCOTT','EMP')) order by intcol;
select * from table(logmnr$always_suplog_columns('SCOTT','BONUS')) order by intcol;
select * from table(logmnr$always_suplog_columns('SCOTT','DEPT')) order by intcol;

You can find demo with logmnr$always_suplog_columns at: GoldenGate 12c Features Found in 11.2.1.0.21 ???

References:

OGG: How To Log All Columns With Add Schematrandata To Get NOCOMPRESSUPDATES (Doc ID 1413142.1)

ADD SCHEMATRANDATA does not allow columns to be specified.
This enables logging of Primary Key columns only.
By default, updates are compressed.
In order to log all columns ADD TRANDATA would have to be used.
The ADD TRANDATA can be used in conjunction with ADD SCHEMATRANDATA to specify the non-primary key columns.

How to Check Supplemental Logging When ADD SCHEMATRANDATA is Enabled (Doc ID 1537837.1)

It is not listed in dba_log_groups or dba_log_group_columns.
select * from table(logmnr$always_suplog_columns( SCHEMA, TABLE ));

Effects of ADD TRANDATA and ADD SCHEMATRANDATA on an Oracle databases’ Supplemental Logging (Doc ID 2070331.1)

Some useful commands from ggsci:

INFO TRANDATA [container.]owner.table (info trandata *) did not work
INFO SCHEMATRANDATA schema            (info schematrandata *)
LIST TABLES table                     (list tables SCOTT.*)

Note to self:

$ cat list_table.prm
dblogin USERID ggs, PASSWORD *
list tables SCOTT.*

$ ggsci paramfile /home/oracle/working/dinh/list_table.prm > list_table.log

$ grep '\.' list_table.log | egrep -iv 'found|ggsci'| grep -A 10000 "Successfully logged into database."|grep -v database > table.log

$ cat table.log
SCOTT.EMP
SCOTT.BONUS
SCOTT.DEPT

$ cat read.sh
#!/bin/bash
IFS="."
while read f1 f3
do
echo "select * from table(logmnr\$always_suplog_columns('$f1','$f3')) order by intcol;"
done < /home/oracle/working/dinh/table.log
exit

$ ./read.sh > /tmp/suplog.sql

$ head /tmp/suplog.sql
select * from table(logmnr$always_suplog_columns('SCOTT','EMP')) order by intcol;
select * from table(logmnr$always_suplog_columns('SCOTT','BONUS')) order by intcol;
select * from table(logmnr$always_suplog_columns('SCOTT','DEPT')) order by intcol;

$ cat suplog.sql
set numw 8 lines 200 timing off echo off pages 10000 trimsp on tab off
column NAME_COL_PLUS_SHOW_PARAM format a30
column VALUE_COL_PLUS_SHOW_PARAM format a65 wrap
col owner for a20
col table_name for a20
col column_name for a30
col log_group_type for a20
col column_list for a80
col log_group_name for a30
col table_name for a30
spool Database_Supplemental_Logging_Details.log
pro ******** Database ********
SELECT
name,db_unique_name,open_mode,database_role,remote_archive,switchover_status,dataguard_broker,primary_db_unique_name
FROM v$database
;
pro ******** Database Supplemental Logging ********
SELECT
supplemental_log_data_min MIN,
supplemental_log_data_pk PK,
supplemental_log_data_ui UI,
supplemental_log_data_fk FK,
supplemental_log_data_all "ALL"
FROM v$database
;
pro ******** Table Supplemental Logging ********
pro
pro ******** GoldenGate: ADD TRANDATA ********
SELECT
g.owner, g.table_name, g.log_group_name, g.log_group_type,
DECODE(always,'ALWAYS','Unconditional',NULL,'Conditional') always,
LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.POSITION) column_list
FROM dba_log_groups g, dba_log_group_columns c
WHERE g.owner = c.owner(+)
AND g.log_group_name = c.log_group_name(+)
AND g.table_name = c.table_name(+)
GROUP BY g.owner, g.log_group_name, g.table_name, g.log_group_type, DECODE(always,'ALWAYS','Unconditional',NULL,'Conditional')
ORDER BY g.owner, g.log_group_name, g.table_name, g.log_group_type
;
pro ******** Schema Supplemental Logging ********
pro
pro ******** GoldenGate: ADD SCHEMATRANDATA ********
@/tmp/suplog.sql
exit

SQL Versus ASMCMD

Tue, 2020-05-26 23:10

This is a set of scripts to help on Oracle ASM Monitoring

Reviewing the SQL looks rather complicated especially when trying to find information for specific Disk Group.

Use one command line to retrieve data or create complex SQL to do the same?


Size_MB  is Bytes/1e6
Alloc_MB is Space/1e6

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> @asm.sql

                                                                                                                                                              System
ASM File Name / Volume Name / Device Name                                                  Size_MB           Alloc_MB File Type          Creation Date        Created?
------------------------------------------------------------------------------- ------------------ ------------------ ------------------ -------------------- --------
+ORCL_SID_DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.311.990946061                                     0                  1      PARAMETERFILE 31-OCT-2018 06:47:40    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/Backup.313.990988403                                43                 50        CONTROLFILE 31-OCT-2018 18:33:22    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/Current.257.990939051                                0                  8        CONTROLFILE 31-OCT-2018 04:50:50    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/Current.258.990939051                                0                  8        CONTROLFILE 31-OCT-2018 04:50:50    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/current.256.990939051                               43                 50        CONTROLFILE 31-OCT-2018 04:50:51    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/AUDIT_TBS.264.996655365                            32,212             32,247           DATAFILE 04-JAN-2019 08:42:44    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/AUDIT_TBS.319.996656095                             1,074              1,076           DATAFILE 04-JAN-2019 08:54:55    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/AVAIL.277.990939537                                   701                703           DATAFILE 31-OCT-2018 04:58:56    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EUR_ORDER.271.990939353                            32,212             32,247           DATAFILE 31-OCT-2018 04:55:53    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EUR_ORDER.316.992686897                               315                317           DATAFILE 20-NOV-2018 10:21:37    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EUR_PDA.321.1007139241                             11,035             11,037           DATAFILE 01-MAY-2019 16:54:01    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EZFETCH_IE.270.990939353                            3,821              3,823           DATAFILE 31-OCT-2018 04:55:53    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EZFETCH_UK.269.990939199                           33,521             33,555           DATAFILE 31-OCT-2018 04:53:18    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EZFETCH_UK.279.990939547                            6,946              6,948           DATAFILE 31-OCT-2018 04:59:06    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_ESS.284.990939589                          105                107           DATAFILE 31-OCT-2018 04:59:49    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_IAS_OPSS.285.990939591                     126                128           DATAFILE 31-OCT-2018 04:59:50    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_IAS_UMS.287.990939593                      703                705           DATAFILE 31-OCT-2018 04:59:53    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_IAU.289.990939595                           63                 65           DATAFILE 31-OCT-2018 04:59:55    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_MDS.282.990939585                          734                736           DATAFILE 31-OCT-2018 04:59:45    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_SOAINFRA.290.1010082787                 32,212             32,247           DATAFILE 04-JUN-2019 18:33:07    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_SOAINFRA.292.1010082785                 32,212             32,247           DATAFILE 04-JUN-2019 18:33:04    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_SOAINFRA.314.1010082781                 32,212             32,247           DATAFILE 04-JUN-2019 18:33:00    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_SOAINFRA.318.1010082769                 32,212             32,247           DATAFILE 04-JUN-2019 18:32:48    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_SOAINFRA.326.1013130689                 13,342             13,344           DATAFILE 09-JUL-2019 01:11:28    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_STB.288.990939595                           10                 12           DATAFILE 31-OCT-2018 04:59:54    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_WLS.286.990939591                           63                 65           DATAFILE 31-OCT-2018 04:59:51    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/IFW_DT_TBS.280.990939551                           12,885             12,887           DATAFILE 31-OCT-2018 04:59:10    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/IFW_IDX_TBS.281.990939585                           1,074              1,076           DATAFILE 31-OCT-2018 04:59:45    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/KOMPASS.274.990939395                              34,360             34,361           DATAFILE 31-OCT-2018 04:56:35    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/KOMPASS.324.1028374119                             13,627             13,629           DATAFILE 30-DEC-2019 11:28:39    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/MBSYNC_UK_MCD.291.990939597                        25,166             25,200           DATAFILE 31-OCT-2018 04:59:57    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/PDA_STG.267.990939163                              34,360             34,361           DATAFILE 31-OCT-2018 04:52:42    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/PDA_STG.278.990939539                              33,580             33,589           DATAFILE 31-OCT-2018 04:58:59    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/PDA_STG.325.1014480263                              6,107              6,109           DATAFILE 24-JUL-2019 16:04:23    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/PERFSTAT.320.1001228979                             2,961              2,963           DATAFILE 26-FEB-2019 07:09:39    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/SYSAUX.260.990939059                               34,341             34,361           DATAFILE 31-OCT-2018 04:50:59    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/SYSAUX.268.990939163                                9,026              9,028           DATAFILE 31-OCT-2018 04:52:43    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/SYSAUX.275.990939395                               32,212             32,247           DATAFILE 31-OCT-2018 04:56:35    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/SYSTEM.259.990939059                               11,996             11,998           DATAFILE 31-OCT-2018 04:50:58    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/UNDOTBS1.261.990939059                             34,360             34,361           DATAFILE 31-OCT-2018 04:50:59    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/UNDOTBS1.283.1041158311                             4,295              4,297           DATAFILE 23-MAY-2020 10:38:30    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/UNDOTBS2.263.990939063                             34,360             34,361           DATAFILE 31-OCT-2018 04:51:03    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/UNDOTBS2.323.1041061103                            34,359             34,361           DATAFILE 22-MAY-2020 07:38:23    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/USERS.262.990939059                                     7                  7           DATAFILE 31-OCT-2018 04:50:59    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/WEBMDATA.272.990939357                             27,259             27,281           DATAFILE 31-OCT-2018 04:55:57    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/WEBMINDX.273.990939393                              1,074              1,076           DATAFILE 31-OCT-2018 04:56:32    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAGUARDCONFIG/ORCL_SID_PHX1RAC.312.990949397                   0                  1    DATAGUARDCONFIG 31-OCT-2018 07:43:17    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_1.293.990939821                              1,074              1,076          ONLINELOG 31-OCT-2018 05:03:41    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_10.300.990939985                             1,074              1,076          ONLINELOG 31-OCT-2018 05:06:25    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_11.301.990940017                             1,074              1,076          ONLINELOG 31-OCT-2018 05:06:56    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_12.302.990940049                             1,074              1,076          ONLINELOG 31-OCT-2018 05:07:28    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_13.303.990940081                             1,074              1,076          ONLINELOG 31-OCT-2018 05:08:01    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_14.304.990940083                             1,074              1,076          ONLINELOG 31-OCT-2018 05:08:02    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_2.294.990939853                              1,074              1,076          ONLINELOG 31-OCT-2018 05:04:12    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_21.305.990940085                             1,074              1,076          ONLINELOG 31-OCT-2018 05:08:04    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_22.306.990940115                             1,074              1,076          ONLINELOG 31-OCT-2018 05:08:35    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_23.307.990940179                             1,074              1,076          ONLINELOG 31-OCT-2018 05:09:38    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_24.308.990940241                             1,074              1,076          ONLINELOG 31-OCT-2018 05:10:40    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_25.309.990940273                             1,074              1,076          ONLINELOG 31-OCT-2018 05:11:12    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_26.310.990940273                             1,074              1,076          ONLINELOG 31-OCT-2018 05:11:13    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_3.295.990939915                              1,074              1,076          ONLINELOG 31-OCT-2018 05:05:15    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_4.296.990939917                              1,074              1,076          ONLINELOG 31-OCT-2018 05:05:17    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_5.297.990939919                              1,074              1,076          ONLINELOG 31-OCT-2018 05:05:19    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_6.298.990939951                              1,074              1,076          ONLINELOG 31-OCT-2018 05:05:51    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_9.299.990939953                              1,074              1,076          ONLINELOG 31-OCT-2018 05:05:53    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/TEMPFILE/FMWEURPD12_IAS_TEMP.317.991419415                  18,822             18,824           TEMPFILE 05-NOV-2018 18:16:55    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/TEMPFILE/NA_ARCHIVE_TEMP.322.1007140587                     10,737             10,740           TEMPFILE 01-MAY-2019 17:16:26    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/TEMPFILE/TEMP.315.991419415                                 34,359             34,361           TEMPFILE 05-NOV-2018 18:16:54    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/dr1ORCL_SID.dat                                                  0                  1    DATAGUARDCONFIG 31-OCT-2018 07:43:17    N
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/snapcf_ORCL_SID.f                                               43                 50        CONTROLFILE 31-OCT-2018 18:33:22    N
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/spfileORCL_SID.ora                                               0                  1      PARAMETERFILE 31-OCT-2018 06:47:40    N
                                                                                ------------------ ------------------
                                                                                           736,613            737,115

                                                                                ------------------ ------------------
Grand Total:                                                                               736,613            737,115

71 rows selected.

SQL>

================================================================================

$ asmcmd lsdg -g ORCL_SID_DATA
Inst_ID  State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
      1  MOUNTED  EXTERN  N         512             512   4096  1048576    786492    83442                0           83442              0             N  ORCL_SID_DATA/
      2  MOUNTED  EXTERN  N         512             512   4096  1048576    786492    83442                0           83442              0             N  ORCL_SID_DATA/

================================================================================

$ asmcmd ls -lsh ORCL_SID_DATA/*/*
Type             Redund  Striped  Time             Sys  Block_Size   Blocks        Bytes        Space  Name

+ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/:
CONTROLFILE      UNPROT  FINE     MAY 26 22:00:00  Y           16K     2635        41.2M          48M  Backup.313.990988403
CONTROLFILE      UNPROT  FINE     OCT 31 2018      Y            2K        1           2K           8M  Current.257.990939051
CONTROLFILE      UNPROT  FINE     OCT 31 2018      Y            2K        1           2K           8M  Current.258.990939051
CONTROLFILE      UNPROT  FINE     MAR 11 16:00:00  Y           16K     2635        41.2M          48M  current.256.990939051

+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/:
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  AUDIT_TBS.264.996655365
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   131073           1G           1G  AUDIT_TBS.319.996656095
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    85625       668.9M         670M  AVAIL.277.990939537
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  EUR_ORDER.271.990939353
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    38401         300M         302M  EUR_ORDER.316.992686897
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1347073        10.3G        10.3G  EUR_PDA.321.1007139241
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   466433         3.6G         3.6G  EZFETCH_IE.270.990939353
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4091905        31.2G        31.3G  EZFETCH_UK.269.990939199
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   847873         6.5G         6.5G  EZFETCH_UK.279.990939547
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    12801         100M         102M  FMWEURPD12_ESS.284.990939589
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    15361         120M         122M  FMWEURPD12_IAS_OPSS.285.990939591
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    85761         670M         672M  FMWEURPD12_IAS_UMS.287.990939593
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K     7681          60M          62M  FMWEURPD12_IAU.289.990939595
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    89601         700M         702M  FMWEURPD12_MDS.282.990939585
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.290.1010082787
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.292.1010082785
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.314.1010082781
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.318.1010082769
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1628673        12.4G        12.4G  FMWEURPD12_SOAINFRA.326.1013130689
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K     1281          10M          11M  FMWEURPD12_STB.288.990939595
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K     7681          60M          62M  FMWEURPD12_WLS.286.990939591
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1572865          12G          12G  IFW_DT_TBS.280.990939551
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   131073           1G           1G  IFW_IDX_TBS.281.990939585
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  KOMPASS.274.990939395
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1663489        12.7G        12.7G  KOMPASS.324.1028374119
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3072001        23.4G        23.5G  MBSYNC_UK_MCD.291.990939597
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  PDA_STG.267.990939163
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4099073        31.3G        31.3G  PDA_STG.278.990939539
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   745473         5.7G         5.7G  PDA_STG.325.1014480263
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   361473         2.8G         2.8G  PERFSTAT.320.1001228979
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4192001          32G          32G  SYSAUX.260.990939059
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1101825         8.4G         8.4G  SYSAUX.268.990939163
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  SYSAUX.275.990939395
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1464321        11.2G        11.2G  SYSTEM.259.990939059
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  UNDOTBS1.261.990939059
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   524289           4G           4G  UNDOTBS1.283.1041158311
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  UNDOTBS2.263.990939063
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194177          32G          32G  UNDOTBS2.323.1041061103
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K      801         6.3M           7M  USERS.262.990939059
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3327489        25.4G        25.4G  WEBMDATA.272.990939357
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   131073           1G           1G  WEBMINDX.273.990939393

+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAGUARDCONFIG/:
DATAGUARDCONFIG  UNPROT  COARSE   MAY 19 03:00:00  Y            4K        3          12K           1M  ORCL_SID_PHX1RAC.312.990949397

+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/:
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_1.293.990939821
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_10.300.990939985
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_11.301.990940017
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_12.302.990940049
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_13.303.990940081
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_14.304.990940083
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_2.294.990939853
ONLINELOG        UNPROT  COARSE   MAY 26 21:00:00  Y           512  2097153           1G           1G  group_21.305.990940085
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_22.306.990940115
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_23.307.990940179
ONLINELOG        UNPROT  COARSE   MAY 26 20:00:00  Y           512  2097153           1G           1G  group_24.308.990940241
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_25.309.990940273
ONLINELOG        UNPROT  COARSE   MAY 26 20:00:00  Y           512  2097153           1G           1G  group_26.310.990940273
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_3.295.990939915
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_4.296.990939917
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_5.297.990939919
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_6.298.990939951
ONLINELOG        UNPROT  COARSE   MAY 26 21:00:00  Y           512  2097153           1G           1G  group_9.299.990939953

+ORCL_SID_DATA/DB_UNKNOWN/PARAMETERFILE/:
PARAMETERFILE    UNPROT  COARSE   MAY 26 22:00:00  Y           512       17         8.5K           1M  SPFILE.311.990946061

+ORCL_SID_DATA/ORCL_SID_PHX1RAC/TEMPFILE/:
TEMPFILE         UNPROT  COARSE   MAR 11 16:00:00  Y            8K  2297601        17.5G        17.5G  FMWEURPD12_IAS_TEMP.317.991419415
TEMPFILE         UNPROT  COARSE   MAR 11 16:00:00  Y            8K  1310721          10G          10G  NA_ARCHIVE_TEMP.322.1007140587
TEMPFILE         UNPROT  COARSE   MAR 11 16:00:00  Y            8K  4194177          32G          32G  TEMP.315.991419415
DATAGUARDCONFIG  UNPROT  COARSE   MAY 19 03:00:00  N            4K        3          12K           1M  dr1ORCL_SID.dat => +ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAGUARDCONFIG/ORCL_SID_PHX1RAC.312.990949397
CONTROLFILE      UNPROT  FINE     MAY 26 22:00:00  N           16K     2635        41.2M          48M  snapcf_ORCL_SID.f => +ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/Backup.313.990988403
PARAMETERFILE    UNPROT  COARSE   MAY 26 22:00:00  N           512       17         8.5K           1M  spfileORCL_SID.ora => +ORCL_SID_DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.311.990946061

================================================================================

$ asmcmd ls -lsh ORCL_SID_DATA/*/*/*
Type             Redund  Striped  Time             Sys  Block_Size   Blocks        Bytes        Space  Name
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  AUDIT_TBS.264.996655365
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   131073           1G           1G  AUDIT_TBS.319.996656095
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    85625       668.9M         670M  AVAIL.277.990939537
CONTROLFILE      UNPROT  FINE     MAY 26 22:00:00  Y           16K     2635        41.2M          48M  Backup.313.990988403
CONTROLFILE      UNPROT  FINE     OCT 31 2018      Y            2K        1           2K           8M  Current.257.990939051
CONTROLFILE      UNPROT  FINE     OCT 31 2018      Y            2K        1           2K           8M  Current.258.990939051
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  EUR_ORDER.271.990939353
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    38401         300M         302M  EUR_ORDER.316.992686897
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1347073        10.3G        10.3G  EUR_PDA.321.1007139241
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   466433         3.6G         3.6G  EZFETCH_IE.270.990939353
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4091905        31.2G        31.3G  EZFETCH_UK.269.990939199
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   847873         6.5G         6.5G  EZFETCH_UK.279.990939547
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    12801         100M         102M  FMWEURPD12_ESS.284.990939589
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    15361         120M         122M  FMWEURPD12_IAS_OPSS.285.990939591
TEMPFILE         UNPROT  COARSE   MAR 11 16:00:00  Y            8K  2297601        17.5G        17.5G  FMWEURPD12_IAS_TEMP.317.991419415
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    85761         670M         672M  FMWEURPD12_IAS_UMS.287.990939593
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K     7681          60M          62M  FMWEURPD12_IAU.289.990939595
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    89601         700M         702M  FMWEURPD12_MDS.282.990939585
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.290.1010082787
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.292.1010082785
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.314.1010082781
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.318.1010082769
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1628673        12.4G        12.4G  FMWEURPD12_SOAINFRA.326.1013130689
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K     1281          10M          11M  FMWEURPD12_STB.288.990939595
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K     7681          60M          62M  FMWEURPD12_WLS.286.990939591
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1572865          12G          12G  IFW_DT_TBS.280.990939551
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   131073           1G           1G  IFW_IDX_TBS.281.990939585
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  KOMPASS.274.990939395
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1663489        12.7G        12.7G  KOMPASS.324.1028374119
DATAGUARDCONFIG  UNPROT  COARSE   MAY 19 03:00:00  Y            4K        3          12K           1M  MBEURINP_SPMBRAC.312.990949397
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3072001        23.4G        23.5G  MBSYNC_UK_MCD.291.990939597
TEMPFILE         UNPROT  COARSE   MAR 11 16:00:00  Y            8K  1310721          10G          10G  NA_ARCHIVE_TEMP.322.1007140587
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  PDA_STG.267.990939163
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4099073        31.3G        31.3G  PDA_STG.278.990939539
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   745473         5.7G         5.7G  PDA_STG.325.1014480263
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   361473         2.8G         2.8G  PERFSTAT.320.1001228979
PARAMETERFILE    UNPROT  COARSE   MAY 26 22:00:00  Y           512       17         8.5K           1M  SPFILE.311.990946061
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4192001          32G          32G  SYSAUX.260.990939059
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1101825         8.4G         8.4G  SYSAUX.268.990939163
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  SYSAUX.275.990939395
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1464321        11.2G        11.2G  SYSTEM.259.990939059
TEMPFILE         UNPROT  COARSE   MAR 11 16:00:00  Y            8K  4194177          32G          32G  TEMP.315.991419415
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  UNDOTBS1.261.990939059
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   524289           4G           4G  UNDOTBS1.283.1041158311
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  UNDOTBS2.263.990939063
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194177          32G          32G  UNDOTBS2.323.1041061103
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K      801         6.3M           7M  USERS.262.990939059
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3327489        25.4G        25.4G  WEBMDATA.272.990939357
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   131073           1G           1G  WEBMINDX.273.990939393
CONTROLFILE      UNPROT  FINE     MAR 11 16:00:00  Y           16K     2635        41.2M          48M  current.256.990939051
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_1.293.990939821
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_10.300.990939985
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_11.301.990940017
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_12.302.990940049
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_13.303.990940081
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_14.304.990940083
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_2.294.990939853
ONLINELOG        UNPROT  COARSE   MAY 26 21:00:00  Y           512  2097153           1G           1G  group_21.305.990940085
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_22.306.990940115
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_23.307.990940179
ONLINELOG        UNPROT  COARSE   MAY 26 20:00:00  Y           512  2097153           1G           1G  group_24.308.990940241
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_25.309.990940273
ONLINELOG        UNPROT  COARSE   MAY 26 20:00:00  Y           512  2097153           1G           1G  group_26.310.990940273
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_3.295.990939915
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_4.296.990939917
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_5.297.990939919
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_6.298.990939951
ONLINELOG        UNPROT  COARSE   MAY 26 21:00:00  Y           512  2097153           1G           1G  group_9.299.990939953
__ATA.cmd.push(function() { __ATA.initDynamicSlot({ id: 'atatags-26942-5ecded202cec1', location: 120, formFactor: '001', label: { text: 'Advertisements', }, creative: { reportAd: { text: 'Report this ad', }, privacySettings: { text: 'Privacy settings', } } }); });

Rename Table During DataPump Import

Tue, 2020-05-19 18:07

At source perform export for tables=SCOTT.SALES, SCOTT.ORDERS

$ expdp \"/ as sysdba\" directory=DATA_PUMP_DIR tables=SCOTT.SALES,SCOTT.ORDERS  dumpfile=SCOTT.dmp logfile=SCOTT.log

Preference is to use parameter file vs having to take into consideration and dealing with slash.

Import will rename table SALES to SALES_BACKUP and ORDERS to ORDERS_BACKUP for SCOTT schema.

$ cat impdp_table.par
userid="/ as sysdba"
directory=DATA_PUMP_DIR
dumpfile=SCOTT.dmp
remap_table=SCOTT.SALES:SALES_BACKUP
remap_table=SCOTT.ORDERS:ORDERS_BACKUP
table_exists_action=SKIP
$ impdp parfile=impdp_table.par

Import: Release 18.0.0.0.0 - Production on Tue May 19 11:37:51 2020
Version 18.6.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA parfile=impdp_table.par

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."ORDERS_BACKUP"  11.02 GB 84493879 rows
. . imported "SCOTT"."SALES_BACKUP"   44.60 MB  366568 rows

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

ORA-31684: Object type INDEX:"SCOTT"."ORDERS_PK" already exists
ORA-31684: Object type INDEX:"SCOTT"."ORDERS_INDX2" already exists
ORA-31684: Object type INDEX:"SCOTT"."ORDERS_INDX1" already exists
ORA-31684: Object type INDEX:"SCOTT"."SALES_PK" already exists
ORA-31684: Object type INDEX:"SCOTT"."SALES_INDX1" already exists

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

ORA-31684: Object type CONSTRAINT:"SCOTT"."ORDERS_PK" already exists
ORA-31684: Object type CONSTRAINT:"SCOTT"."SALES_PK" already exists

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "SYS"."SYS_IMPORT_FULL_01" completed with 7 error(s) at Tue May 19 11:39:43 2020 elapsed 0 00:01:50

If the intention is to TRUNCATE or MERGE SCOTT.SALES AND SCOTT.ORDERS using BACKUP, then errors can be ignored.

If not, then failed constraints and indexes will need to be created accordingly.

Oracle Data Pump Import – REMAP_TABLE

 

ACFS Mystery

Mon, 2020-05-18 17:49

From ACFS Support On OS Platforms (Certification Matrix). (Doc ID 1369107.1)

ACFS and AFD 12.2.0.1 Supported Platforms
Vendor          :  RedHat Linux 
Version         : 7 
Update / Kernel : Update 6 3.10.0-957 and later / 3.10.0 Red Hat kernels X86_64	
ACFS Bug or PSU	: 12.2.0.1.191015 (Base Bug 29963428)

Where: “Base” (at the “Bug or PSU” column) stands for the "12.2.0.1 Grid Infrastructure" release

Check current environment:

$ $ORACLE_HOME/OPatch/opatch lspatches
26928563;TOMCAT RELEASE UPDATE 12.2.0.1.0(ID:170711) (26928563)
26925644;OCW RELEASE UPDATE 12.2.0.1.0(ID:171003) (26925644)
26839277;DBWLM RELEASE UPDATE 12.2.0.1.0(ID:170913) (26839277)
26710464;Database Release Update : 12.2.0.1.171017 (26710464)
26247490;ACFS Interim patch for 26247490

OPatch succeeded.

$ $ORACLE_HOME/OPatch/opatch lsinventory|grep 29963428
$ $ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed|grep 29963428

$ uname -r
3.10.0-1062.12.1.el7.x86_64

ACFS should not be supported since 29963428 has not been applied, but  it is.


# rpm -q kernel --last
kernel-3.10.0-1062.12.1.el7.x86_64            Mon 11 May 2020 14:39:40
kernel-3.10.0-957.5.1.el7.x86_64              Thu 31 Oct 2019 16:26:21
kernel-3.10.0-862.2.3.el7.x86_64              Mon 28 May 2018 11:27:22

# lsmod | grep oracle
oracleacfs 4626640 2
oracleadvm 776776 8
oracleoks 662840 2 oracleacfs,oracleadvm

# modinfo oracleoks
filename: /lib/modules/3.10.0-1062.12.1.el7.x86_64/weak-updates/usm/oracleoks.ko
author: Oracle Corporation
license: Proprietary
rhelversion: 7.4
srcversion: C5110F596402987AF02F894
depends:
vermagic: 3.10.0-693.el7.x86_64 SMP mod_unload modversions
signer: Oracle Linux RHCK Module Signing Key
sig_key: DD:99:5B:15:5C:19:B3:A7:C3:EF:77:07:B9:69:E2:5F:96:39:66:6E
sig_hashalgo: sha256

# acfsdriverstate version
ACFS-9325: Driver OS kernel version = 3.10.0-693.el7.x86_64(x86_64).
ACFS-9326: Driver Oracle version = RELEASE.
ACFS-9212: Driver build version = 12.2.0.1 (ACFSRU)..

# acfsdriverstate installed
ACFS-9203: true

# acfsdriverstate supported
ACFS-9200: Supported

# acfsroot version_check
ACFS-9316: Valid ADVM/ACFS distribution media detected at: '/a01/app/grid/12.2.0/usm/install/Oracle/EL7/x86_64/3.10.0-693/3.10.0-693-x86_64/bin'

# crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [12.2.0.1.0]

# acfsutil registry
Mount Object:
  Device: /dev/asm/shared-57
  Mount Point: /shared
  Disk Group: shared
  Volume: shared
  Options: none
  Nodes: all
  Accelerator Volumes:

# acfsutil info fs
/shared
    ACFS Version: 12.2.0.1.0
    on-disk version:       46.0
    compatible.advm:       12.2.0.0.0
    ACFS compatibility:    12.2.0.0.0
    flags:        MountPoint,Available
    mount time:   Thu May 14 23:16:59 2020
    mount sequence number: 0
    allocation unit:       4096
    metadata block size:   4096
    volumes:      1
    total size:   987842478080  ( 920.00 GB )
    total free:   849604771840  ( 791.26 GB )
    file entry table allocation: 42336256
    primary volume: /dev/asm/shared-57
        label:
        state:                 Available
        major, minor:          252, 29185
        logical sector size:   512
        size:                  987842478080  ( 920.00 GB )
        free:                  849604771840  ( 791.26 GB )
        metadata read I/O count:         80140
        metadata write I/O count:        269
        total metadata bytes read:       328335360  ( 313.12 MB )
        total metadata bytes written:    1187840  (   1.13 MB )
        ADVM diskgroup:        shared
        ADVM resize increment: 536870912
        ADVM redundancy:       unprotected
        ADVM stripe columns:   8
        ADVM stripe width:     1048576
    number of snapshots:  0
    snapshot space usage: 0  ( 0.00 )
    replication status: DISABLED
    compression status: DISABLED

$ crsctl stat res -t -w "TYPE = ora.volume.type"
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.shared.shared.advm
               ONLINE  ONLINE       xxxxxxx-01               STABLE
               ONLINE  ONLINE       xxxxxxx-02               STABLE
--------------------------------------------------------------------------------

$ crsctl stat res -t -w "TYPE = ora.acfs.type"
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.shared.shared.acfs
               ONLINE  ONLINE       lp-ora1-rh               mounted on /shared,STABLE
               ONLINE  ONLINE       lp-ora2-rh               mounted on /shared,STABLE
--------------------------------------------------------------------------------

Do you see what’s wrong and why ACFS is supported?

__ATA.cmd.push(function() { __ATA.initDynamicSlot({ id: 'atatags-26942-5ec3117d25de0', location: 120, formFactor: '001', label: { text: 'Advertisements', }, creative: { reportAd: { text: 'Report this ad', }, privacySettings: { text: 'Privacy settings', } } }); });

RMAN Restore From Tape

Wed, 2020-05-13 22:56

In my previous post, Testing RMAN Restore Validate From Tape , now I will perform actual restore.

There is requirement to test backup to the new tape library before implementing in production by restoring the database from tape backup.

Database resides on file system. Instead of drop database, shutdown abort, delete data files and control files from disk.

Archived logs resides in FRA and was not deleted which resulted in complete recovery without having to use set until time.

set until time "to_date('2020-05-13 12:10:00','YYYY-MM-DD HH24:MI:SS')";

This is a condense output for restore.

$ grep -i dbid rman_archbackup_HAWKEYE_arch_Wed_202005131210.log
connected to target database: HAWKEYE (DBID=2937483440)

$ rman @ restore.rman

Recovery Manager: Release 11.2.0.4.0 - Production on Wed May 13 15:37:09 2020

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

RMAN> set echo on
2> connect target;
3> run {
4> allocate channel ch1 type 'SBT_TAPE' parms 'SBT_LIBRARY=/opt/libddboostora.so,SBT_PARMS=(CONFIG_FILE=/opt/idpa_ddbea.config)';
5> allocate channel ch2 type 'SBT_TAPE' parms 'SBT_LIBRARY=/opt/libddboostora.so,SBT_PARMS=(CONFIG_FILE=/opt/idpa_ddbea.config)';
6> allocate channel ch3 type 'SBT_TAPE' parms 'SBT_LIBRARY=/opt/libddboostora.so,SBT_PARMS=(CONFIG_FILE=/opt/idpa_ddbea.config)';
7> set dbid 2937483440;
8> restore controlfile from autobackup;
9> alter database mount;
10> restore database;
11> recover database;
12> alter database open resetlogs;
13> release channel ch1;
14> release channel ch2;
15> release channel ch3;
16> }
17> exit
echo set on

connected to target database: HAWKEYE (not mounted)

using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=209 device type=SBT_TAPE
channel ch1: Database Application Agent Oracle v19.4.0.0

allocated channel: ch2
channel ch2: SID=217 device type=SBT_TAPE
channel ch2: Database Application Agent Oracle v19.4.0.0

allocated channel: ch3
channel ch3: SID=225 device type=SBT_TAPE
channel ch3: Database Application Agent Oracle v19.4.0.0

executing command: SET DBID

Starting restore at 2020-05-13 15:37:11
channel ch1: looking for AUTOBACKUP on day: 20200513
channel ch1: AUTOBACKUP found: c-2937483440-20200513-01
channel ch2: looking for AUTOBACKUP on day: 20200513
channel ch2: skipped, AUTOBACKUP already found
channel ch3: looking for AUTOBACKUP on day: 20200513
channel ch3: skipped, AUTOBACKUP already found
channel ch1: restoring control file from AUTOBACKUP c-2937483440-20200513-01
channel ch1: control file restore from AUTOBACKUP complete
output file name=/oradata/HAWKEYE/controlfile/control_01.ctl
output file name=/orafra/HAWKEYE/HAWKEYE/controlfile/control_02.ctl
Finished restore at 2020-05-13 15:37:21

database mounted

Starting restore at 2020-05-13 15:37:26

Starting implicit crosscheck backup at 2020-05-13 15:37:26
Finished implicit crosscheck backup at 2020-05-13 15:37:27

Starting implicit crosscheck copy at 2020-05-13 15:37:27
Finished implicit crosscheck copy at 2020-05-13 15:37:27

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================

channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: reading from backup piece 8juvv2b1_1_1
channel ch1: piece handle=8juvv2b1_1_1 tag=LEVEL0
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:17:55
Finished restore at 2020-05-13 15:55:24

Starting recover at 2020-05-13 15:55:24

channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: reading from backup piece a6v01jp8_1_1
channel ch1: piece handle=a6v01jp8_1_1 tag=LEVEL1
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:25

starting media recovery

channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
channel ch1: reading from backup piece a7v01k17_1_1

channel ch2: starting archived log restore to default destination
channel ch2: restoring archived log
channel ch2: reading from backup piece a9v023is_1_1

channel ch3: starting archived log restore to default destination
channel ch3: restoring archived log
channel ch3: reading from backup piece aav023it_1_1

channel ch1: piece handle=a7v01k17_1_1 tag=LEVEL1
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01

channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
channel ch1: reading from backup piece abv023iu_1_1

channel ch2: piece handle=a9v023is_1_1 tag=ARCH
channel ch2: restored backup piece 1
channel ch2: restore complete, elapsed time: 00:00:02

channel ch2: starting archived log restore to default destination
channel ch2: restoring archived log
channel ch2: reading from backup piece acv023iv_1_1

channel ch3: piece handle=aav023it_1_1 tag=ARCH
channel ch3: restored backup piece 1
channel ch3: restore complete, elapsed time: 00:00:02

media recovery complete, elapsed time: 00:00:05

Finished recover at 2020-05-13 15:56:56

database opened

released channel: ch1

released channel: ch2

released channel: ch3

Recovery Manager complete.
$
$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed May 13 16:00:32 2020

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

connected to target database: HAWKEYE (DBID=2937483440)</pre>
RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 HAWKEYE 2937483440 PARENT 6278186746 2017-05-22 10:50:52
2 2 HAWKEYE 2937483440 PARENT 6278189261 2017-05-22 11:05:05
3 3 HAWKEYE 2937483440 CURRENT 6747789335 2020-05-13 15:56:56

RMAN> exit

Recovery Manager complete.
$

Testing RMAN Restore Validate From Tape

Tue, 2020-05-12 08:17

Short and simple method to validate backup.

oracle@hawk:/home/oracle$ rman checksyntax @ restore_validate_idpa_ddbea.rman

Recovery Manager: Release 11.2.0.4.0 - Production on Tue May 12 07:24:57 2020

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

RMAN> set echo on
2> connect target;
3> show all;
4> list backup summary;
5> report schema;
6> run {
7> allocate channel ch1 type 'SBT_TAPE' parms 'SBT_LIBRARY=/opt/libddboostora.so,SBT_PARMS=(CONFIG_FILE=/opt/idpa_ddbea.config)';
8> allocate channel ch2 type 'SBT_TAPE' parms 'SBT_LIBRARY=/opt/libddboostora.so,SBT_PARMS=(CONFIG_FILE=/opt/idpa_ddbea.config)';
9> allocate channel ch3 type 'SBT_TAPE' parms 'SBT_LIBRARY=/opt/libddboostora.so,SBT_PARMS=(CONFIG_FILE=/opt/idpa_ddbea.config)';
10> crosscheck backup;
11> restore validate spfile;
12> restore validate controlfile;
13> restore database preview;
14> restore validate database;
15> recover database preview;
16> }
17> exit
The cmdfile has no syntax errors

Recovery Manager complete.
oracle@hawk:/home/oracle$
__ATA.cmd.push(function() { __ATA.initDynamicSlot({ id: 'atatags-26942-5ebad88652a48', location: 120, formFactor: '001', label: { text: 'Advertisements', }, creative: { reportAd: { text: 'Report this ad', }, privacySettings: { text: 'Privacy settings', } } }); });

nohup vs screen

Sun, 2020-05-10 15:12

While I have played a little with screen , my preference is nohup since the output from screen is so ugly.

With that being said, it would be nice for application designs to be resumable, e.g. Shocking opatchauto resume works after auto-logout

There were discussions about running many SQLs where each SQL is run manually, check for error before running the next SQL; hence, screen was used.

Why not trap for error and exit vs manually checking?

[oracle@ol7-121-dg3 ~]$ cat error.sql
WHENEVER SQLERROR EXIT SQL.SQLCODE
set echo on
select sysdate from dual;
select * from notable;
select database_role from v$database;
exit

[oracle@ol7-121-dg3 ~]$ sqlplus / as sysdba @ error.sql

SQL*Plus: Release 12.1.0.2.0 Production on Sun May 10 17:48:46 2020

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

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

OL7-121-DG3:(SYS@hawkc:PRIMARY> select sysdate from dual;

SYSDATE
-------------------
2020-05-10 17:48:46
OL7-121-DG3:(SYS@hawkc:PRIMARY> select * from notable;
select * from notable
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@ol7-121-dg3 ~]$

Another reason for using screen is to run stored procedure.

But this can be performed using nohup too as demonstrated.

[oracle@ol7-121-dg3 ~]$ export filename=test
[oracle@ol7-121-dg3 ~]$ cat > $filename.sql < set serverout on echo on
> exec dbms_output.put_line('test');
> exit
> EOF

[oracle@ol7-121-dg3 ~]$ ls -l $filename.sql
-rw-r--r--. 1 oracle oinstall 65 May 10 17:30 test.sql
[oracle@ol7-121-dg3 ~]$ cat $filename.sql
set serverout on echo on
exec dbms_output.put_line('test');
exit

[oracle@ol7-121-dg3 ~]$ nohup sqlplus "/ as sysdba" @ $filename.sql > $filename.log 2>&1 &
[1] 8422

[oracle@ol7-121-dg3 ~]$
[1]+  Done                    nohup sqlplus "/ as sysdba" @ $filename.sql > $filename.log 2>&1

[oracle@ol7-121-dg3 ~]$ ls -l $filename.*
-rw-r--r--. 1 oracle oinstall 616 May 10 17:30 test.log
-rw-r--r--. 1 oracle oinstall  65 May 10 17:30 test.sql

[oracle@ol7-121-dg3 ~]$ cat $filename.log
nohup: ignoring input

SQL*Plus: Release 12.1.0.2.0 Production on Sun May 10 17:30:53 2020

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

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

OL7-121-DG3:(SYS@hawkc:PRIMARY> exec dbms_output.put_line('test');
test
OL7-121-DG3:(SYS@hawkc:PRIMARY> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@ol7-121-dg3 ~]$

It’s possible I will adopt screen at one point; however, my preference is still nohup.

Notes on SQL Profiles vs SQL Plan Baselines

Wed, 2020-05-06 17:48

It’s possible to have both SQL Profiles and SQL Plan Baselines.

SQL PROFILES    : Adjust Optimizer cardinality estimations
SPM BASELINES : Direct SQL to follow specific execution plan

Researh on SQL Profiles and SQL Plan Baselines

What is the difference between SQL Profiles and SQL Plan Baselines? (June 17, 2017)
Nice table comparision between SQL Profiles and Plan Baselines
https://sateeshv-dbainfo.blogspot.com/2017/06/what-is-difference-between-sql-profiles.html

What is the difference between SQL Profiles and SQL Plan Baselines?
https://blogs.oracle.com/optimizer/what-is-the-difference-between-sql-profiles-and-sql-plan-baselines

Using SQL Plan Management to Control SQL Execution Plans
https://blogs.oracle.com/optimizer/using-sql-plan-management-to-control-sql-execution-plans

SQL Plan Management (Part 1 of 4) Creating SQL plan baselines
https://blogs.oracle.com/optimizer/sql-plan-management-part-1-of-4-creating-sql-plan-baselines

SQL Plan Management (Part 2 of 4) SPM Aware Optimizer
https://blogs.oracle.com/optimizer/sql-plan-management-part-2-of-4-spm-aware-optimizer

SQL Plan Management (Part 3 of 4): Evolving SQL Plan Baselines
https://blogs.oracle.com/optimizer/sql-plan-management-part-3-of-4:-evolving-sql-plan-baselines

SQL Plan Management (Part 4 of 4): User Interfaces and Other Features (February 2, 2009)
https://blogs.oracle.com/optimizer/sql-plan-management-part-4-of-4:-user-interfaces-and-other-features

New SQL Plan Management White Paper (January 30, 2017)
https://blogs.oracle.com/optimizer/new-sql-plan-management-white-paper

Upgrade to Oracle Database 12c and Avoid Query Regression (December 3, 2015)
https://blogs.oracle.com/optimizer/upgrade-to-oracle-database-12c-and-avoid-query-regression

Repairing SQL Performance Regression with SQL Plan Management (October 15, 2019)
https://blogs.oracle.com/optimizer/repairing-sql-performance-regression-with-sql-plan-management

glogin.sql for Data Guard Environment

Sat, 2020-05-02 17:19

I got tired for seeing for db_name vs db_unique_name for sql prompt connecting with sqlplus for Data Guard environment.

Update $ORACLE_HOME/sqlplus/admin/glogin.sql

column NAME_COL_PLUS_SHOW_PARAM format a40 wrap
column VALUE_COL_PLUS_SHOW_PARAM format a80 wrap
set lines 200 echo on trimsp on tab off pages 10000 serverout on size 1000000 feedback off verify off term off echo off arraysize 5000
define _pr="SQL> "
column pr new_value _pr
select UPPER(SYS_CONTEXT('USERENV','SERVER_HOST'))||':(&_USER@'||SYS_CONTEXT('USERENV','DB_UNIQUE_NAME')||':'||SYS_CONTEXT('USERENV','DATABASE_ROLE')||'> '
pr from dual;
set sqlprompt "&_pr"
column pr clear
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
set termout on

Test:

[oracle@ol7-121-dg3 ~]$ . oraenv <<< hawk
ORACLE_SID = [hawk] ? The Oracle base remains unchanged with value /u01/app/oracle

[oracle@ol7-121-dg3 ~]$ sysresv|tail -1
Oracle Instance alive for sid "hawk"

[oracle@ol7-121-dg3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat May 2 22:16:36 2020

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


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

OL7-121-DG3:(SYS@hawkc:PRIMARY> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@ol7-121-dg3 ~]$

__ATA.cmd.push(function() { __ATA.initDynamicSlot({ id: 'atatags-26942-5eadf1f2e56f7', location: 120, formFactor: '001', label: { text: 'Advertisements', }, creative: { reportAd: { text: 'Report this ad', }, privacySettings: { text: 'Privacy settings', } } }); });

When To Use dgmgrl / vs dgmgrl sys@tns

Sat, 2020-05-02 10:41

There’s been discussion on twitter about using dgmgrl / vs dgmgrl sys@tns.

For the most part, I typically use dgmgrl sys@tns only for switch over and fail over.

Update: Should use dgmgrl sys@tns when there is an update to the database role, i.e. switch over, fail over, convert

Even Oracle’s documentation (Doc ID 278641.1) uses dgmgrl /

================================================================================
ORA-1031 Insufficient Privileges During Switchover via DGMGRL (Doc ID 740327.1)
================================================================================

CAUSE
Customer connected to database using o/s authentication from dgmgrl that resulted in ORA-01031
DGMGRL> connect /

SOLUTION
When we perform switchover using DGMGRL that requires database restarts, one must connect to dgmgrl by explicitly specifying a valid SYSDBA username/password and not using the OS Authentication.
DG Broker cannot complete a switchover using ‘connect /’ since it doesn’t have the credentials to restart the standby after shutting it down.

DGMGRL> connect sys/<password>
DGMGRL> connect sys/<pwd>@<connect string>

================================================================================
DGMGRL>CONVERT TO PHYSICAL STANDBY Fails With ORA-01031 (Doc ID 2398886.1)
================================================================================

CAUSE
The connection to the database through dgmgrl utility was not using the TNS_ALIAS
dgmgrl /

SOLUTION
Connect to the primary database through dgmgrl in order to have the convert command successfully executed:
dgmgrl sys@<primary tns_alias>

================================================================================
How do you apply a Patchset,PSU or CPU in a Data Guard Physical Standby configuration (Doc ID 278641.1)
================================================================================

1. Disable REDO Transport on Primary
1.1 Disable log shipping using DGMGRL.

If DG broker in place it is mandatory to disable log shipping via DG broker.
DGMGRL> connect /

Still do not know when dgmgrl sys@tns is absolutely necessary.

dgmgrl / works fine for monitoring, editing configuration, and modifying transport/apply.

In summary, use what makes you comfortable.

 

AutoUpgrade What I Did Not Know

Wed, 2020-04-29 12:51

Deploy and Upgrade Postupgrade Checks and Fixes

After an upgrade completes with either Deploy or Upgrade modes, AutoUpgrade performs postupgrade checks.
It provides a process where you can enable your custom scripts to be run on each of the upgraded databases,
in accordance with the configuration instructions you provide in the AutoUpgrade configuration file,
and also can run automatic postupgrade fixups as part of the postupgrade process.

In Deploy mode, AutoUpgrade also confirms that the upgrade has succeeded,
and moves database files such as sqlnet.ora, tnsname.ora, and listener.ora
from the source home to the target home.

After these actions are complete, the upgraded Oracle Database release is started in the new Oracle home.

Reference: About Oracle Database AutoUpgrade

Environments:

Source: Database Apr 2019 Release Update : 12.2.0.1.190416 (29314339)
Target: Database Release Update : 19.3.0.0.190416 (29517242)

$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -version
build.hash 6010a62
build.version 19.8.1
build.date 2020/04/01 16:18:24
build.max_target_version 19
build.type production

Why is moving sqlnet.ora, tnsnames.ora, and listener.ora from the source home to the target home a problem?

There were multiple databases running from the same home and created application issues.

Ironically, I just recently had discussion about using TNS_ADMIN to store sqlnet.ora, tnsname.ora, and listener.ora in central locations vs ORACLE_HOME.

Now curious if this will cause AutoUgrade to fail?

Overview of Local Naming Parameters

By default, the tnsnames.ora file is located in the ORACLE_HOME/network/admin directory.
Oracle Net will check the other directories for the configuration file.

For example, the order checking the tnsnames.ora file is as follows:

The directory specified by the TNS_ADMIN environment variable.
If the file is not found in the directory specified, then it is assumed that the file does not exist.
If the TNS_ADMIN environment variable is not set, then Oracle Net checks the ORACLE_HOME/network/admin directory.

Reference: 6.1 Overview of Local Naming Parameters

Thanks to a super smart team member (who wants to be anonymous) for sharing.

Playing With lsof

Wed, 2020-04-29 07:53

lsof – list open files

This is a continuation of How To Check ORACLE_HOME Is Being Used

Check for count of open files:

[root@db-fs-1 ~]# lsof +D /u01/app/oracle/11.2.0.4/db1|wc -l
550

[root@db-fs-1 ~]# lsof +D /u01/app/ggs|wc -l
56
[root@db-fs-1 ~]#

Shutdown GoldenGate and Kill pmon

[root@db-fs-1 ~]# . oraenv <<< hawk
ORACLE_SID = [root] ? The Oracle base has been set to /u01/app/oracle
[root@db-fs-1 ~]# cd /u01/app/ggs/
[root@db-fs-1 ggs]# ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct 17 2019 23:13:12
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.



GGSCI (db-fs-1) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:06


GGSCI (db-fs-1) 2> stop *

Sending STOP request to EXTRACT EXT1 ...
Request processed.


GGSCI (db-fs-1) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EXT1        00:00:00      00:00:05


GGSCI (db-fs-1) 4> stop mgr !

Sending STOP request to MANAGER ...
Request processed.
Manager stopped.


GGSCI (db-fs-1) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
EXTRACT     STOPPED     EXT1        00:00:00      00:00:17


GGSCI (db-fs-1) 6> exit

[root@db-fs-1 ggs]# ps -ef|grep [p]mon
oracle   17864     1  0 Apr28 ?        00:00:08 ora_pmon_hawk
[root@db-fs-1 ggs]# kill -9 17864

Check for count of open files:

[root@db-fs-1 ggs]# lsof +D /u01/app/oracle/11.2.0.4/db1|wc -l
15

[root@db-fs-1 ggs]# lsof +D /u01/app/ggs|wc -l
6
[root@db-fs-1 ggs]#

Check for open files:
cwd – some process is in directory /u01/app/ggs
DUH! That would be me.

[root@db-fs-1 ggs]# lsof +D /u01/app/ggs
COMMAND   PID USER   FD   TYPE DEVICE SIZE/OFF    NODE NAME
bash    29106 root  cwd    DIR  249,2     4096 1443287 /u01/app/ggs
lsof    29913 root  cwd    DIR  249,2     4096 1443287 /u01/app/ggs
lsof    29914 root  cwd    DIR  249,2     4096 1443287 /u01/app/ggs

[root@db-fs-1 ggs]# lsof +D /u01/app/oracle/11.2.0.4/db1
COMMAND PID   USER   FD   TYPE DEVICE SIZE/OFF    NODE NAME
tnslsnr 876 oracle  txt    REG  249,2   972792  918631 /u01/app/oracle/11.2.0.4/db1/bin/tnslsnr
tnslsnr 876 oracle  mem    REG  249,2   241443  923131 /u01/app/oracle/11.2.0.4/db1/lib/libclsra11.so
tnslsnr 876 oracle  mem    REG  249,2 17647384  923129 /u01/app/oracle/11.2.0.4/db1/lib/libhasgen11.so
tnslsnr 876 oracle  mem    REG  249,2  3354840  923133 /u01/app/oracle/11.2.0.4/db1/lib/libocrb11.so
tnslsnr 876 oracle  mem    REG  249,2  1612720  923132 /u01/app/oracle/11.2.0.4/db1/lib/libocr11.so
tnslsnr 876 oracle  mem    REG  249,2   156012  923134 /u01/app/oracle/11.2.0.4/db1/lib/libocrutl11.so
tnslsnr 876 oracle  mem    REG  249,2    12787  919774 /u01/app/oracle/11.2.0.4/db1/lib/libskgxn2.so
tnslsnr 876 oracle  mem    REG  249,2    59021  920648 /u01/app/oracle/11.2.0.4/db1/lib/libnque11.so
tnslsnr 876 oracle  mem    REG  249,2  7996693  923239 /u01/app/oracle/11.2.0.4/db1/lib/libnnz11.so
tnslsnr 876 oracle  mem    REG  249,2 53775272  920645 /u01/app/oracle/11.2.0.4/db1/lib/libclntsh.so.11.1
tnslsnr 876 oracle  mem    REG  249,2   148728  919817 /u01/app/oracle/11.2.0.4/db1/lib/libons.so
tnslsnr 876 oracle    3r   REG  249,2    52224 1057224 /u01/app/oracle/11.2.0.4/db1/rdbms/mesg/diaus.msb
tnslsnr 876 oracle    5r   REG  249,2    12288  920670 /u01/app/oracle/11.2.0.4/db1/network/mesg/nlus.msb
tnslsnr 876 oracle    7r   REG  249,2    47104  919729 /u01/app/oracle/11.2.0.4/db1/network/mesg/tnsus.msb
[root@db-fs-1 ggs]#

Kill tns, change directory, and check for open files:

[root@db-fs-1 ggs]# ps -ef|grep tns
root        22     2  0 Apr28 ?        00:00:00 [netns]
oracle     876     1  0 Apr28 ?        00:00:03 /u01/app/oracle/11.2.0.4/db1/bin/tnslsnr LISTENER -inherit
root     29944 29106  0 14:36 pts/0    00:00:00 grep --color=auto tns

[root@db-fs-1 ggs]# kill -9 876

[root@db-fs-1 ggs]# lsof +D /u01/app/oracle/11.2.0.4/db1
[root@db-fs-1 ggs]# cd

[root@db-fs-1 ~]# lsof +D /u01/app/ggs|wc -l
0

[root@db-fs-1 ~]# lsof +D /u01/app/oracle/11.2.0.4/db1|wc -l
0
[root@db-fs-1 ~]#

Copy Or Move File And Append Date Using Bash

Tue, 2020-04-28 23:15

bash version 4.2.46(2)-release

[ggs@db-fs-1 ~]$ echo "$SHELL"
/bin/bash
[ggs@db-fs-1 ~]$

[ggs@db-fs-1 ~]$ echo $BASH_VERSION
4.2.46(2)-release
[ggs@db-fs-1 ~]$

Demo 1:

[ggs@db-fs-1 ~]$ touch foo.log
[ggs@db-fs-1 ~]$ file=foo.log
[ggs@db-fs-1 ~]$
[ggs@db-fs-1 ~]$ ls -l $file*; cp -fv ${file%%.*}.{${file#*.},${file#*.}."$(date +%Y%m%d)"}; ls -l $file*
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:49 foo.log
‘foo.log’ -> ‘foo.log.20200429’
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:49 foo.log
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:50 foo.log.20200429
[ggs@db-fs-1 ~]$

Demo 2:

[ggs@db-fs-1 ~]$ touch archive.tar.gz
[ggs@db-fs-1 ~]$ file=archive.tar.gz
[ggs@db-fs-1 ~]$
[ggs@db-fs-1 ~]$ ls -l $file*; cp -fv ${file%%.*}.{${file#*.},${file#*.}."$(date +%Y%m%d)"}; ls -l $file*
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:48 archive.tar.gz
‘archive.tar.gz’ -> ‘archive.tar.gz.20200429’
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:48 archive.tar.gz
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:49 archive.tar.gz.20200429
[ggs@db-fs-1 ~]$

Demo 3: Rotate ggserr.log (only required because logrotate is not configured)

ggs@db-fs-1 ggs]$ file=ggserr.log
[ggs@db-fs-1 ggs]$ ls -l $file*; cp -fv ${file%%.*}.{${file#*.},${file#*.}."$(date +%Y%m%d)"}; ls -l $file*
-rw-r----- 1 ggs oinstall 534 Apr 29 06:04 ggserr.log
‘ggserr.log’ -> ‘ggserr.log.20200429’
-rw-r----- 1 ggs oinstall 534 Apr 29 06:04 ggserr.log
-rw-r----- 1 ggs oinstall 534 Apr 29 06:05 ggserr.log.20200429

[ggs@db-fs-1 ggs]$ cat /dev/null > ggserr.log
[ggs@db-fs-1 ggs]$ ls -l $file*
-rw-r----- 1 ggs oinstall   0 Apr 29 06:05 ggserr.log
-rw-r----- 1 ggs oinstall 534 Apr 29 06:05 ggserr.log.20200429
[ggs@db-fs-1 ggs]$

Demo 4:

[ggs@db-fs-1 ~]$ file=archive.tar.gz
[ggs@db-fs-1 ~]$ ls -l $file*; mv -fv ${file%%.*}.{${file#*.},${file#*.}."$(date +%Y%m%d)"}; ls -l $file*
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:48 archive.tar.gz
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:49 archive.tar.gz.20200429
‘archive.tar.gz’ -> ‘archive.tar.gz.20200429’
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:48 archive.tar.gz.20200429
[ggs@db-fs-1 ~]$

Why is this even important?

Consistent and mindless effort but need to remember syntax.

GoldenGate 19.1.0.0.4 Silent Install

Fri, 2020-04-24 19:22

GoldenGate install is so easy; don’t bother with GUI install.

Download Oracle GoldenGate 19.1.0.0.4 for Oracle on Linux x86-64 : 191004_fbo_ggs_Linux_x64_shiphome.zip

Create copy of response file:

[ggs@db-fs-1 response]$ pwd
/u01/app/oracle/fbo_ggs_Linux_x64_shiphome/Disk1/response

[ggs@db-fs-1 response]$ ls -l
total 12
-rwxr-xr-x 1 ggs oinstall  209 Apr 24 14:07 oggcore_11g_db.rsp
-rwxrwxr-x 1 ggs oinstall 4439 Jan 23  2019 oggcore.rsp
[ggs@db-fs-1 response]$

Create response file for install: just 5 lines and doesn’t get any easier than this.

[ggs@db-fs-1 response]$ cat oggcore_11g_db.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v19_1_0
INSTALL_OPTION=ORA11g
SOFTWARE_LOCATION=/u01/app/ggs
INVENTORY_LOCATION=/u01/app/oraInventory
UNIX_GROUP_NAME=oinstall
[ggs@db-fs-1 response]$

Install Goldengate: (Note: relative path is not supported for response file]

[ggs@db-fs-1 response]$ cd ../
[ggs@db-fs-1 Disk1]$ ls
install  response  runInstaller  stage

[ggs@db-fs-1 Disk1]$ ./runInstaller -silent -showProgress -waitforcompletion -responseFile response/oggcore_11g_db.rsp
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 44288 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 2047 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2020-04-24_02-08-58PM. Please wait ...
[FATAL] [INS-10101] The given response file response/oggcore_11g_db.rsp is not found.
   CAUSE: The given response file is either not accessible or do not exists.
   ACTION: Give a correct response file location. (Note: relative path is not supported)
[ggs@db-fs-1 Disk1]$


[ggs@db-fs-1 Disk1]$ ./runInstaller -silent -showProgress -waitforcompletion -responseFile /tmp/oggcore_11g_db.rsp
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 28928 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 17407 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2020-04-24_02-23-29PM. Please wait ...

You can find the log of this install session at:
 /u01/app/oraInventory/logs/installActions2020-04-24_02-23-29PM.log

Prepare in progress.
..................................................   10% Done.

Prepare successful.

Copy files in progress.
..................................................   36% Done.
..................................................   54% Done.
..................................................   77% Done.
..................................................   82% Done.
..................................................   88% Done.
....................
Copy files successful.

Link binaries in progress.
..........
Link binaries successful.

Setup files in progress.
..................................................   93% Done.
..................................................   95% Done.
..................................................   96% Done.
..................................................   98% Done.
..................................................   99% Done.

Setup files successful.

Setup Inventory in progress.

Setup Inventory successful.
..................................................   95% Done.
Successfully Setup Software.
..................................................   100% Done.

Finish Setup successful.
The installation of Oracle GoldenGate Core was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2020-04-24_02-23-29PM.log' for more details.
[ggs@db-fs-1 Disk1]$

GoldenGate Monitoring With User Separation

Fri, 2020-04-24 17:10

Typically, GoldenGate monitoring is performed by GoldenGate user.

However, there may be circumstances where monitoring is performed by another user and this is test case for such scenario.

I did not want to create yet another user; hence, GoldenGate will be monitored by user oracle.

GoldenGate 19.1.0.0.4 with Database 11.2.0.4.

Both users (ggs and oracle) belong to the same primary group oinstall:

[ggs@db-fs-1 ggs]$ id ggs
uid=54322(ggs) gid=54321(oinstall) groups=54321(oinstall),54322(dba)

[ggs@db-fs-1 ggs]$ id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54318(asmdba),54320(asmadmin),54322(dba),54323(backupdba),54324(oper),54325(dgdba),54326(kmdba)
[ggs@db-fs-1 ggs]$

Permission for GoldenGate directories are 755:

[ggs@db-fs-1 ggs]$ ls -ld dir*
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 15:21 dirchk
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 15:12 dircrd
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 16:24 dirdat
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:52 dirdef
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 15:13 dirdmp
drwxr-xr-x 3 ggs oinstall 4096 Apr 24 14:25 diretc
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:25 dirout
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 16:24 dirpcs
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 15:20 dirprm
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 16:24 dirrpt
drwxr-xr-x 4 ggs oinstall 4096 Apr 24 14:25 dirsca
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:52 dirsql
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 16:24 dirtmp
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:52 dirwlt
[ggs@db-fs-1 ggs]$

Check lag as user ggs – no issues:

[ggs@db-fs-1 ggs]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct 17 2019 23:13:12
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.



GGSCI (db-fs-1) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:02


GGSCI (db-fs-1) 2> info ext1

EXTRACT    EXT1      Last Started 2020-04-24 16:24   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Process ID           18886
Log Read Checkpoint  Oracle Redo Logs
                     2020-04-24 17:12:08  Seqno 374, RBA 14901248
                     SCN 0.1622637 (1622637)


GGSCI (db-fs-1) 3> lag ext1

2020-04-24 17:12:26  INFO    OGG-14054  Lag from heartbeat table requires DBLOGIN.

Sending GETLAG request to EXTRACT EXT1 ...
Last record lag 2 seconds.
At EOF, no more records to process


GGSCI (db-fs-1) 4> dblogin useridalias gguser
Successfully logged into database.

GGSCI (db-fs-1 as gguser@hawk) 5> lag ext1

Sending GETLAG request to EXTRACT EXT1 ...
Last record lag 2 seconds.
At EOF, no more records to process


GGSCI (db-fs-1 as gguser@hawk) 6> exit
[ggs@db-fs-1 ggs]$

Check lag as user oracle – issue with permissions and extract is not visible:

[oracle@db-fs-1 ggs]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct 17 2019 23:13:12
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.



GGSCI (db-fs-1) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (db-fs-1) 2> sh ls -l /u01/app/ggs/dirchk/*

-rwxr-xr-x 1 ggs oinstall  2048 Apr 24 15:21 /u01/app/ggs/dirchk/EXT1.cpb
-rwxr-xr-x 1 ggs oinstall 20480 Apr 24 17:14 /u01/app/ggs/dirchk/EXT1.cpe


GGSCI (db-fs-1) 3> sh chmod 775 /u01/app/ggs/dirchk/*

chmod: changing permissions of ‘/u01/app/ggs/dirchk/EXT1.cpb’: Operation not permitted
chmod: changing permissions of ‘/u01/app/ggs/dirchk/EXT1.cpe’: Operation not permitted


GGSCI (db-fs-1) 4> sh ls -l /u01/app/ggs/dirchk/*

-rwxrwxr-x 1 ggs oinstall  2048 Apr 24 15:21 /u01/app/ggs/dirchk/EXT1.cpb
-rwxrwxr-x 1 ggs oinstall 20480 Apr 24 17:14 /u01/app/ggs/dirchk/EXT1.cpe

GGSCI (db-fs-1) 5>

Change permission for dirchk to 775:

ggs@db-fs-1 ggs]$ chmod 775 /u01/app/ggs/dirchk/*

Check lag as user oracle – issue with permissions, extract appears, lag check failed:

GGSCI (db-fs-1) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:07


GGSCI (db-fs-1) 6> lag ext1

2020-04-24 17:14:58  INFO    OGG-14054  Lag from heartbeat table requires DBLOGIN.

Sending GETLAG request to EXTRACT EXT1 ...

2020-04-24 17:14:58  ERROR   OGG-15161  Could not initialize the connection with EXTRACT EXT1 (Permission denied).


GGSCI (db-fs-1) 7> dblogin useridalias gguser
Successfully logged into database.

GGSCI (db-fs-1 as gguser@hawk) 8> lag ext1

Sending GETLAG request to EXTRACT EXT1 ...

******************************************************************************************************************************
***** 2020-04-24 17:16:34  ERROR   OGG-15161  Could not initialize the connection with EXTRACT EXT1 (Permission denied). *****
******************************************************************************************************************************

GGSCI (db-fs-1 as gguser@hawk) 9>

Change permissions for all GoldenGate directories to 775:

[ggs@db-fs-1 ggs]$ chmod 775 -R dir*

Check lag using as user oracle – works perfectly:

GGSCI (db-fs-1 as gguser@hawk) 10> lag ext1

Sending GETLAG request to EXTRACT EXT1 ...
Last record lag 2 seconds.
At EOF, no more records to process


GGSCI (db-fs-1 as gguser@hawk) 11>

You might be thinking about least privileges principle.

Change permissions for GoldenGate directories (dirchk and dirtmp) to 775:

[ggs@db-fs-1 ggs]$ chmod 775 -R /u01/app/ggs/dirchk/*
[ggs@db-fs-1 ggs]$ chmod 775 -R /u01/app/ggs/dirtmp/*

[ggs@db-fs-1 ggs]$ ls -ld dir*
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 15:21 dirchk
drwxrwxr-x 2 ggs oinstall 4096 Apr 24 15:12 dircrd
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 16:24 dirdat
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:52 dirdef
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 15:13 dirdmp
drwxr-xr-x 3 ggs oinstall 4096 Apr 24 14:25 diretc
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:25 dirout
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 16:24 dirpcs
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 15:20 dirprm
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 16:24 dirrpt
drwxr-xr-x 4 ggs oinstall 4096 Apr 24 14:25 dirsca
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:52 dirsql
drwxrwxr-x 2 ggs oinstall 4096 Apr 24 16:24 dirtmp
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:52 dirwlt
[ggs@db-fs-1 ggs]$

Check lag as user oracle – SUCCESS:

[oracle@db-fs-1 ggs]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct 17 2019 23:13:12
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.



GGSCI (db-fs-1) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:02


GGSCI (db-fs-1) 2> lag *

2020-04-25 00:02:13  INFO    OGG-14054  Lag from heartbeat table requires DBLOGIN.

Sending GETLAG request to EXTRACT EXT1 ...
Last record lag 1 seconds.
At EOF, no more records to process


GGSCI (db-fs-1) 3> sh ls -l /u01/app/ggs/dirchk

total 28
-rwxrwxr-x 1 ggs oinstall  4096 Apr 24 20:24 EXT1.cpb
-rwxrwxr-x 1 ggs oinstall 20480 Apr 25 00:02 EXT1.cpe


GGSCI (db-fs-1) 4> sh ls -l /u01/app/ggs/dirtmp

total 0
srwxrwxr-x 1 ggs oinstall 0 Apr 24 16:24 EXT1.s


GGSCI (db-fs-1) 5> exit
[oracle@db-fs-1 ggs]$

Q.E.D.

How To Create New Standby Using Active Standby Duplication From Existing Standby

Fri, 2020-04-17 15:46

Basically, my insanity is from my experience as Senior Tax Advisor and investment background.

Planning and organization will simplify processes in the long run.

I will share my thoughts for implementing Data Guard having two standby databases.

Data Guard configurations will be for the following hosts: LAX, PHX, SLC

  1. ORACLE_SID and db_name will be the same value.
  2. db_unique_name and service_names will be the same value.
  3. Listener will be LISTENER.
  4. TNS entries (alias) will be created for service_names and listener.
  5. Data Guard Broker is being used with Oracle Restart
Hostname: LAX (PRIMARY) : OEL6

Option #1: Using alphabet for configurations

ORACLE_SID(db_name)/db_unique_name(service_names): HAWK/HAWKA
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_HAWKA

dg_config=(HAWKA,HAWKB,HAWKC)
fal_server='HAWKB,HAWKC'

Option #2: Using hostname for configurations

ORACLE_SID(db_name)/db_unique_name: HAWK/LAX
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_LAX

dg_config=(LAX,PHX,SLC)
fal_server='PHX,SLC'
Hostname: PHX (CURRENT STANDBY) : OEL6

Option #1: Using alphabet for configurations

ORACLE_SID(db_name)/db_unique_name: HAWK/HAWKB
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_HAWKB

dg_config=(HAWKA,HAWKB,HAWKC)
fal_server='HAWKA,HAWKC'

Option #2: Using hostname for configurations

ORACLE_SID(db_name)/db_unique_name: HAWK/PHX
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_PHX

dg_config=(LAX,PHX,SLC)
fal_server='LAX,SLC'
Hostname: SLC (NEW STANDBY) : OEL7

Option #1: Using alphabet for configurations

ORACLE_SID(db_name)/db_unique_name: HAWK/HAWKC
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_HAWKC

dg_config=(HAWKA,HAWKB,HAWKC)
fal_server='HAWKB,HAWKC'

Option #2: Using hostname for configurations

ORACLE_SID(db_name)/db_unique_name: HAWK/SLC
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_SLC

dg_config=(LAX,PHX,SLC)
fal_server='LAX,PHX'

I know what you are thinking. Why did I name local_listener when I specifically blogged Why Name Listener?!

I did so for consistency and to avoid having to make too many changes.

As stated from blog post, local_listener=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521)) would be better since the only change required would be tns entry for listener alias.

Semi off topic: I still remembered from my martial art teacher, “Expected the unexpected.”

How does this relate to Data Guard and creating standby databases?

Always create database having db_name different from db_unique_name.

One day when there is a requirement to create standby database, environment will be configured with good naming convention. “Expect the unexpected.”

The demonstration will be using Option #2.

One downside, what happens when there is a new host added for the same location?
Use PHX2?
Me being as anal as I am, that’s going to be disturbing.
May be suffix all locations with numeric values?
You decide.

CONFIGURE ARCHIVELOG DELETION TO NONE

There are 1059 datafiles and 5 tempfiles for database duplication and don’t want archivelog to be deleted before second standby is duplicated.

Recovery Manager: Release 12.1.0.2.0 – Production on Mon Apr 13 19:06:26 2020
Finished Duplicate Db at 2020-APR-14 03:42:51

LAX: CONFIGURE ARCHIVELOG DELETION AND DB_UNIQUE_NAME
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
RMAN> CONFIGURE DB_UNIQUE_NAME 'SLC' CONNECT IDENTIFIER 'SLC';
PHX: CONFIGURE ARCHIVELOG DELETION
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
SLC : BUG 19504946 – FLASH CACHE DOESN’T WORK IN OEL7

Apply patch as required.

SLC : Create pfile and startup nomount database

Since Database Smart Flash Cache was configured at PHX (CURRENT STANDBY) and not at SLC (NEW STANDBY),
need to disable db_flash_cache_size and created dummy db_flash_cache_file for the time being until configured.

$ cat initHAWK.ora
*.db_name='HAWK'
*.db_flash_cache_size='0'
*.db_flash_cache_file='+DATA/flashfile.dat'
FOR ALL HOSTS : Verify TNS entries using tnsping from DB_HOME
tnsping LAX; tnsping PHX; tnsping SLC
tnsping LISTENERS_LAX; tnsping LISTENER_PHX; tnsping LISTENER_SLC
FOR ALL HOSTS : Verify connect using TNS as SYSDBA
sqlplus sys@LAX as sysdba
sqlplus sys@PHX as sysdba
sqlplus sys@SLC as sysdba
PHX : Perform RMAN Active DB Duplication
$ rman checksyntax @ duplicate.rman

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Apr 13 19:06:15 2020

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

RMAN> set echo on
2> connect target *
3> connect auxiliary *
4> run {
5> allocate channel c1 type disk maxopenfiles 1;
6> allocate channel c2 type disk maxopenfiles 1;
7> allocate channel c3 type disk maxopenfiles 1;
8> allocate auxiliary channel a1 type disk;
9> allocate auxiliary channel a2 type disk;
10> allocate auxiliary channel a3 type disk;
11> duplicate target database for standby from active database
12> spfile
13> parameter_value_convert 'phx','slc','PHX','SLC'
14> set db_unique_name='SLC'
15> set db_flash_cache_size='0'
16> set db_flash_cache_file='+DATA/flashfile.dat'
17> set pga_aggregate_limit='24159191040'
18> set pga_aggregate_target='11596411699'
19> set sga_max_size='32G'
20> set fal_server='LAX,PHX'
21> set local_listener='LISTENER_SLC'
22> nofilenamecheck;
23> }
24> exit
The cmdfile has no syntax errors

Recovery Manager complete.

$ nohup rman @ duplicate.rman > /tmp/duplicate_rman.log 2>&1 &
PHX : Add 2nd Standby (SLC) to DG Broker Configuration

Better not to use dgmgrl / as it may be problematic.

$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@PHX
DGMGRL> add database SLC as connect identifier is SLC;
DGMGRL> enable configuration
DGMGRL> show configuration
DGMGRL> show database SLC
DGMGRL> exit
SLC : Move spfile to ASM
$ echo "+DATA/spfile$ORACLE_SID.ora" > init$ORACLE_SID.ora

SQL> create pfile='/tmp/init@.ora' from spfile;
File created.

SQL> ! ls -l /tmp/init*
-rw-r--r-- 1 oracle oinstall 9511 Apr 14 17:33 /tmp/init$ORACLE_SID.ora

SQL> create spfile='+DATA/spfile$ORACLE_SID.ora' from pfile='/tmp/init$ORACLE_SID.ora';
File created.

SQL>
SLC : Add database to Oracle Oracle Restart
$ export ORACLE_SID=HAWK
$ export ORACLE_UNQNAME=SLC

$ srvctl add database -dbname $ORACLE_SID -db $ORACLE_UNQNAME -instance $ORACLE_SID -oraclehome $ORACLE_HOME \
  -spfile +DATA/spfile$ORACLE_SID.ora -pwfile $ORACLE_HOME/dbs/orapw$ORACLE_SID \
  -role PHYSICAL_STANDBY -diskgroup "FRA,DATA" -startoption MOUNT -stopoption IMMEDIATE

$ srvctl config database -d $ORACLE_UNQNAME
$ srvctl start database -d $ORACLE_UNQNAME
$ srvctl status database -d $ORACLE_UNQNAME -v

$ ps -ef|grep [p]mon
PHX/SLC : Review resource
crsctl stat res -t -w "TYPE = ora.database.type"
crsctl stat res -t -w "TYPE = ora.service.type"
LAX: CONFIGURE ARCHIVELOG DELETION
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;
PHX/SLC: CONFIGURE ARCHIVELOG DELETION
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
REFERENCES:

What’s New in Oracle Data Guard?

New 11.2 Features Common to Redo Apply and SQL Apply
The FAL_CLIENT database initialization parameter is no longer required.

Data Guard Broker 12c Release 1 (12.1.0.2)

The following database initialization parameters are controlled by broker configurable properties.
Therefore, you should not set these parameters manually:

ARCHIVE_LAG_TARGET
DB_FILE_NAME_CONVERT
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST_STATE_n
LOG_ARCHIVE_FORMAT
LOG_ARCHIVE_MAX_PROCESSES
LOG_ARCHIVE_MIN_SUCCEED_DEST
LOG_ARCHIVE_TRACE
LOG_FILE_NAME_CONVERT
STANDBY_FILE_MANAGEMENT

Configuring RMAN in a Data Guard Environment

CONFIGURE DB_UNIQUE_NAME defines a connection to a physical standby database and implicitly registers the new database.

  New standby databases are also automatically registered when RMAN connects as TARGET to a standby database for the first time.

CONFIGURE FOR DB_UNIQUE_NAME configures settings for a database in the Data Guard environment.

Q.E.D.

How To Check ORACLE_HOME Is Being Used

Thu, 2020-04-16 08:08

At a high level, seems pretty simple doesn’t it?

Investigating usage for /u01 to free space:

$ df -h /u01
Filesystem                    Size  Used Avail Use% Mounted on
/dev/mapper/VGExaDb-LVDbOra1   20G   15G  4.3G  77% /u01

Find five largest directories for u01:

# du -a /u01 | sort -n -r | head -n 5
61379684        /u01
61379660        /u01/app
30698332        /u01/app/oracle
30698308        /u01/app/oracle/product
16096196        /u01/app/18.1.0.0

# du -a /u01/app/oracle/product | sort -n -r | head -n 5
30698308        /u01/app/oracle/product
8913108 /u01/app/oracle/product/12.1.0.2
8913104 /u01/app/oracle/product/12.1.0.2/dbhome_1
8569260 /u01/app/oracle/product/12.2.0.1
8569256 /u01/app/oracle/product/12.2.0.1/dbhome_1

Different versions of unused ORACLE_HOME from u01:

$ ls -l /u01/app/oracle/product
drwxrwxr-x 3 grid   oinstall 4096 Apr  2  2019 11.2.0.4
drwxrwxr-x 3 grid   oinstall 4096 Apr  2  2019 12.1.0.2
drwxrwxr-x 3 grid   oinstall 4096 Apr  2  2019 12.2.0.1
drwxrwxr-x 3 grid   oinstall 4096 Apr  2  2019 18.1.0.0
drwxr-xr-x 3 oracle oinstall 4096 Apr  3  2019 java

Check inventory and oratab for any reference to u01:

$ grep u01 /u01/app/oraInventory/ContentsXML/inventory.xml
home crs='true' /='' loc='/u01/app/18.1.0.0/grid' name='OraGI18Home1' idx='1' type='O'

$ grep u01 /etc/oratab
+ASM1:/u01/app/18.1.0.0/grid:N          # line added by Agent

Check for processes running from specific home:
Note: 11.2.0.4 show process because currently in the directory.

$ pwd
/u01/app/oracle/product/11.2.0.4

$ lsof +D /u01/app/oracle/product/11.2.0.4
COMMAND    PID   USER   FD   TYPE DEVICE SIZE/OFF   NODE NAME
bash    115430 oracle  cwd    DIR  249,2     4096 524298 /u01/app/oracle/product/11.2.0.4
lsof    172485 oracle  cwd    DIR  249,2     4096 524298 /u01/app/oracle/product/11.2.0.4
lsof    172488 oracle  cwd    DIR  249,2     4096 524298 /u01/app/oracle/product/11.2.0.4

$ lsof +D /u02/app/oracle/product/12.2.0/dbhome_5
$ lsof +D /u02/app/oracle/product/12.2.0/dbhome_17
COMMAND      PID   USER   FD   TYPE  DEVICE SIZE/OFF     NODE NAME
oraagent. 273460 oracle  mem    REG 202,112     1544 25693540 /u02/app/oracle/product/12.2.0/dbhome_17/dbs/hc_DATABASE52.dat
oraagent. 273460 oracle  209r   REG 202,112     1544 25693540 /u02/app/oracle/product/12.2.0/dbhome_17/dbs/hc_DATABASE52.dat

$ cd
$ lsof +D /u01/app/oracle/product/11.2.0.4
$

Suddenly, I remembered srvctl config all which I had blogged about.

$ . oraenv <<< +ASM1
ORACLE_SID = [+ASM1] ? The Oracle base has been set to /u01/app/grid

$ srvctl config all

Oracle Clusterware configuration details
========================================

Oracle Clusterware basic information
------------------------------------
  Operating         Linux
  system
  Name              EXAPHXAD218-039
  Class             STANDALONE
  Cluster nodes     hostname-node1, hostname-node2
  Version           18.0.0.0.0
  Groups            SYSOPER:asmoper SYSASM:asmadmin SYSRAC:asmadmin SYSDBA:asmdba
  Cluster home      /u01/app/18.1.0.0/grid
  OCR locations     +DATAC1
  Voting disk       /dev/exadata_quorum/QD_DATAC1_EF42DA09,
  locations         /dev/exadata_quorum/QD_DATAC1_A4242108, o, o, o

Cluster network configuration details
-------------------------------------
  Interface name  Type  Subnet            Classification
  bondeth0        IPV4  10.157.39.0/26    PUBLIC
  clib0           IPV4  192.168.132.0/22  PRIVATE, ASM
  clib1           IPV4  192.168.132.0/22  PRIVATE, ASM

SCAN configuration details
--------------------------

SCAN "hostname-node-scan.oraclevcn.com" details
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  Name                 hostname-node-scan.oraclevcn.com
  IPv4 subnet          10.157.39.0/26
  DHCP server type     static
  End points           TCP:1521

  SCAN listeners
  --------------
  Name        VIP address
  LISTENER    10.157.39.15


ASM configuration details
-------------------------
  Mode             remote
  Password file    +DATAC1
  SPFILE           +DATAC1

  ASM disk group details
  ++++++++++++++++++++++
  Name    Redundancy
  DATAC1  HIGH
  RECOC1  HIGH

Database configuration details
==============================

Database "ora.database3_pho.db" details
--------------------------------------
  Name               ora.database3_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_21

Database "ora.database2_pho.db" details
-------------------------------------
  Name               ora.database2_pho.db
  Type               RAC
  Version            18.0.0.0.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/18.0.0.0/dbhome_4

Database "ora.database3_pho.db" details
-------------------------------------
  Name               ora.database3_pho.db
  Type               RAC
  Version            18.0.0.0.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/18.0.0.0/dbhome_2

Database "ora.database4_pho.db" details
-------------------------------------
  Name               ora.database4_pho.db
  Type               RAC
  Version            18.0.0.0.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/18.0.0.0/dbhome_3

Database "ora.database5_pho.db" details
-------------------------------------
  Name               ora.database5_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_17

Database "ora.database6_pho.db" details
-------------------------------------
  Name               ora.database6_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_25

Database "ora.database7_pho.db" details
--------------------------------------
  Name               ora.database7_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_19

Database "ora.database8_pho.db" details
-----------------------------------
  Name               ora.database8_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_3

Database "ora.database9_pho.db" details
-----------------------------------
  Name               ora.database9_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_20

Database "ora.databasea_pho.db" details
-----------------------------------
  Name               ora.databasea_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_26

Database "ora.databaseb_pho.db" details
--------------------------------------
  Name               ora.databaseb_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_18

Database "ora.databasec_pho.db" details
-----------------------------------
  Name               ora.databasec_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_4

Database "ora.databased_pho.db" details
-----------------------------------
  Name               ora.databased_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_22

Database "ora.databasec_pho.db" details
-----------------------------------
  Name               ora.wus3p_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_23

Database "ora.databased_pho.db" details
--------------------------------------
  Name               ora.databased_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_8

Database "ora.databasee_pho.db" details
-----------------------------------
  Name               ora.databasee_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_24
$

By the way, this is Oracle Database Exadata Cloud Service from what I was informed.

These are actual number of databases.

Looks to be in Phoenix region base on pho suffix in the name?

How does one tell if on cloud or on premise?

My guess is oraclevcn.com from hostname.

Using sshUserSetup.sh for Passwordless ssh

Sun, 2020-04-05 07:03

Quick short and sweet. I am creating POC for Dataguard with multiple standby configuration using/hacking vagrant virtual box.

Being as lazy as I am and not liking to have to enter password, use sshUserSetup.sh

[oracle@ol7-121-dg1 ~]$ cd /u01/software/database/sshsetup/
[oracle@ol7-121-dg1 sshsetup]$


[oracle@ol7-121-dg1 sshsetup]$ ./sshUserSetup.sh -h
Please specify a valid and existing cluster configuration file.
Either user name or host information is missing
Usage ./sshUserSetup.sh -user <user name> [ -hosts "<space separated hostlist>" | -hostfile <absolute path of cluster configuration file> ] [ -advanced ]  [ -verify] [ -exverify ] [ -logfile <desired absolute path of logfile> ] [-confirm] [-shared] [-help] [-usePassphrase] [-noPromptPassphrase]
[oracle@ol7-121-dg1 sshsetup]$


[oracle@ol7-121-dg1 sshsetup]$ ./sshUserSetup.sh -user oracle -hosts "ol7-121-dg1 ol7-121-dg2 ol7-121-dg3" -noPromptPassphrase
The output of this script is also logged into /tmp/sshUserSetup_2020-04-05-11-53-56.log
Hosts are ol7-121-dg1 ol7-121-dg2 ol7-121-dg3
user is oracle
Platform:- Linux
Checking if the remote hosts are reachable
PING ol7-121-dg1.localdomain (192.168.56.101) 56(84) bytes of data.
64 bytes from ol7-121-dg1.localdomain (192.168.56.101): icmp_seq=1 ttl=64 time=0.016 ms
64 bytes from ol7-121-dg1.localdomain (192.168.56.101): icmp_seq=2 ttl=64 time=0.019 ms
64 bytes from ol7-121-dg1.localdomain (192.168.56.101): icmp_seq=3 ttl=64 time=0.036 ms
64 bytes from ol7-121-dg1.localdomain (192.168.56.101): icmp_seq=4 ttl=64 time=0.045 ms
64 bytes from ol7-121-dg1.localdomain (192.168.56.101): icmp_seq=5 ttl=64 time=0.041 ms

--- ol7-121-dg1.localdomain ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4293ms
rtt min/avg/max/mdev = 0.016/0.031/0.045/0.012 ms
PING ol7-121-dg2.localdomain (192.168.56.102) 56(84) bytes of data.
64 bytes from ol7-121-dg2.localdomain (192.168.56.102): icmp_seq=1 ttl=64 time=0.333 ms
64 bytes from ol7-121-dg2.localdomain (192.168.56.102): icmp_seq=2 ttl=64 time=0.657 ms
64 bytes from ol7-121-dg2.localdomain (192.168.56.102): icmp_seq=3 ttl=64 time=0.547 ms
64 bytes from ol7-121-dg2.localdomain (192.168.56.102): icmp_seq=4 ttl=64 time=0.539 ms
64 bytes from ol7-121-dg2.localdomain (192.168.56.102): icmp_seq=5 ttl=64 time=0.514 ms

--- ol7-121-dg2.localdomain ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4310ms
rtt min/avg/max/mdev = 0.333/0.518/0.657/0.104 ms
PING ol7-121-dg3.localdomain (192.168.56.103) 56(84) bytes of data.
64 bytes from ol7-121-dg3.localdomain (192.168.56.103): icmp_seq=1 ttl=64 time=0.356 ms
64 bytes from ol7-121-dg3.localdomain (192.168.56.103): icmp_seq=2 ttl=64 time=0.554 ms
64 bytes from ol7-121-dg3.localdomain (192.168.56.103): icmp_seq=3 ttl=64 time=0.463 ms
64 bytes from ol7-121-dg3.localdomain (192.168.56.103): icmp_seq=4 ttl=64 time=0.362 ms
64 bytes from ol7-121-dg3.localdomain (192.168.56.103): icmp_seq=5 ttl=64 time=0.472 ms

--- ol7-121-dg3.localdomain ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4517ms
rtt min/avg/max/mdev = 0.356/0.441/0.554/0.076 ms
Remote host reachability check succeeded.
The following hosts are reachable: ol7-121-dg1 ol7-121-dg2 ol7-121-dg3.
The following hosts are not reachable: .
All hosts are reachable. Proceeding further...
firsthost ol7-121-dg1
numhosts 3
The script will setup SSH connectivity from the host ol7-121-dg1.localdomain to all
the remote hosts. After the script is executed, the user can use SSH to run
commands on the remote hosts or copy files between this host ol7-121-dg1.localdomain
and the remote hosts without being prompted for passwords or confirmations.

NOTE 1:
As part of the setup procedure, this script will use ssh and scp to copy
files between the local host and the remote hosts. Since the script does not
store passwords, you may be prompted for the passwords during the execution of
the script whenever ssh or scp is invoked.

NOTE 2:
AS PER SSH REQUIREMENTS, THIS SCRIPT WILL SECURE THE USER HOME DIRECTORY
AND THE .ssh DIRECTORY BY REVOKING GROUP AND WORLD WRITE PRIVILEDGES TO THESE
directories.

Do you want to continue and let the script make the above mentioned changes (yes/no)?
yes

The user chose yes
User chose to skip passphrase related questions.
Creating .ssh directory on local host, if not present already
Creating authorized_keys file on local host
Changing permissions on authorized_keys to 644 on local host
Creating known_hosts file on local host
Changing permissions on known_hosts to 644 on local host
Creating config file on local host
If a config file exists already at /home/oracle/.ssh/config, it would be backed up to /home/oracle/.ssh/config.backup.
Creating .ssh directory and setting permissions on remote host ol7-121-dg1
THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR group AND others ON THE HOME DIRECTORY FOR oracle. THIS IS AN SSH REQUIREMENT.
The script would create ~oracle/.ssh/config file on remote host ol7-121-dg1. If a config file exists already at ~oracle/.ssh/config, it would be backed up to ~oracle/.ssh/config.backup.
The user may be prompted for a password here since the script would be running SSH on host ol7-121-dg1.
Warning: Permanently added 'ol7-121-dg1,192.168.56.101' (ECDSA) to the list of known hosts.
oracle@ol7-121-dg1's password:
Done with creating .ssh directory and setting permissions on remote host ol7-121-dg1.
Creating .ssh directory and setting permissions on remote host ol7-121-dg2
THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR group AND others ON THE HOME DIRECTORY FOR oracle. THIS IS AN SSH REQUIREMENT.
The script would create ~oracle/.ssh/config file on remote host ol7-121-dg2. If a config file exists already at ~oracle/.ssh/config, it would be backed up to ~oracle/.ssh/config.backup.
The user may be prompted for a password here since the script would be running SSH on host ol7-121-dg2.
Warning: Permanently added 'ol7-121-dg2,192.168.56.102' (ECDSA) to the list of known hosts.
oracle@ol7-121-dg2's password:
Done with creating .ssh directory and setting permissions on remote host ol7-121-dg2.
Creating .ssh directory and setting permissions on remote host ol7-121-dg3
THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR group AND others ON THE HOME DIRECTORY FOR oracle. THIS IS AN SSH REQUIREMENT.
The script would create ~oracle/.ssh/config file on remote host ol7-121-dg3. If a config file exists already at ~oracle/.ssh/config, it would be backed up to ~oracle/.ssh/config.backup.
The user may be prompted for a password here since the script would be running SSH on host ol7-121-dg3.
Warning: Permanently added 'ol7-121-dg3,192.168.56.103' (ECDSA) to the list of known hosts.
oracle@ol7-121-dg3's password:
Done with creating .ssh directory and setting permissions on remote host ol7-121-dg3.
Copying local host public key to the remote host ol7-121-dg1
The user may be prompted for a password or passphrase here since the script would be using SCP for host ol7-121-dg1.
oracle@ol7-121-dg1's password:
Done copying local host public key to the remote host ol7-121-dg1
Copying local host public key to the remote host ol7-121-dg2
The user may be prompted for a password or passphrase here since the script would be using SCP for host ol7-121-dg2.
oracle@ol7-121-dg2's password:
Done copying local host public key to the remote host ol7-121-dg2
Copying local host public key to the remote host ol7-121-dg3
The user may be prompted for a password or passphrase here since the script would be using SCP for host ol7-121-dg3.
oracle@ol7-121-dg3's password:
Done copying local host public key to the remote host ol7-121-dg3
cat: /home/oracle/.ssh/known_hosts.tmp: No such file or directory
cat: /home/oracle/.ssh/authorized_keys.tmp: No such file or directory
SSH setup is complete.

------------------------------------------------------------------------
Verifying SSH setup
===================
The script will now run the date command on the remote nodes using ssh
to verify if ssh is setup correctly. IF THE SETUP IS CORRECTLY SETUP,
THERE SHOULD BE NO OUTPUT OTHER THAN THE DATE AND SSH SHOULD NOT ASK FOR
PASSWORDS. If you see any output other than date or are prompted for the
password, ssh is not setup correctly and you will need to resolve the
issue and set up ssh again.
The possible causes for failure could be:
1. The server settings in /etc/ssh/sshd_config file do not allow ssh
for user oracle.
2. The server may have disabled public key based authentication.
3. The client public key on the server may be outdated.
4. ~oracle or ~oracle/.ssh on the remote host may not be owned by oracle.
5. User may not have passed -shared option for shared remote users or
may be passing the -shared option for non-shared remote users.
6. If there is output in addition to the date, but no password is asked,
it may be a security alert shown as part of company policy. Append the
additional text to the <OMS HOME>/sysman/prov/resources/ignoreMessages.txt file.
------------------------------------------------------------------------
--ol7-121-dg1:--
Running /usr/bin/ssh -x -l oracle ol7-121-dg1 date to verify SSH connectivity has been setup from local host to ol7-121-dg1.
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE, IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but being prompted for a password is ERROR.
Sun Apr  5 11:54:28 UTC 2020
------------------------------------------------------------------------
--ol7-121-dg2:--
Running /usr/bin/ssh -x -l oracle ol7-121-dg2 date to verify SSH connectivity has been setup from local host to ol7-121-dg2.
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE, IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but being prompted for a password is ERROR.
Sun Apr  5 11:54:28 UTC 2020
------------------------------------------------------------------------
--ol7-121-dg3:--
Running /usr/bin/ssh -x -l oracle ol7-121-dg3 date to verify SSH connectivity has been setup from local host to ol7-121-dg3.
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE, IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but being prompted for a password is ERROR.
Sun Apr  5 11:54:28 UTC 2020
------------------------------------------------------------------------
SSH verification complete.
[oracle@ol7-121-dg1 sshsetup]$

I wonder if Tim reads my blog?

__ATA.cmd.push(function() { __ATA.initDynamicSlot({ id: 'atatags-26942-5e8a477d8cadf', location: 120, formFactor: '001', label: { text: 'Advertisements', }, creative: { reportAd: { text: 'Report this ad', }, privacySettings: { text: 'Privacy settings', } } }); });

Why Name Listener?!

Sun, 2020-04-05 06:48

May be I am too naive to know, If you have reason, then please share.

With the following configuration, environment can be easily migrated/duplicated with minimal or no change.
The only change would be port for local_listener if port# changed.

Also, easier to use and maintain.

local_listener=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1591))
lsnrctl status
tnsnames.ora entry ***not*** required

With the following configuration, environment can be migrated/duplicated requiring multiple changes.

local_listener=LISTENER_NAME
lsnrctl status LISTENER_NAME
tnsnames.ora entry required for LISTENER_NAME

If listener is named, then does every environment has different names?

Security will mostly likely be used for justification but I don’t see it.

Pages