Thinking Out Loud

June 3, 2020

GoldenGate – Supplemental Logging Is A Mess

Filed under: 19c,GoldenGate — mdinh @ 3:22 am

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

May 27, 2020

SQL Versus ASMCMD

Filed under: 18c,ASM — mdinh @ 4:10 am

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

May 19, 2020

Rename Table During DataPump Import

Filed under: 18c,DataPump — mdinh @ 11:07 pm

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

 

May 18, 2020

ACFS Mystery

Filed under: 12.2,ACFS — mdinh @ 10:49 pm

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?

May 14, 2020

RMAN Restore From Tape

Filed under: 11g,Recovery,RMAN — mdinh @ 3:56 am

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.
$

May 12, 2020

Testing RMAN Restore Validate From Tape

Filed under: RMAN — mdinh @ 1:17 pm

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$

May 10, 2020

nohup vs screen

Filed under: linux,shell scripting — mdinh @ 8:12 pm

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.

May 6, 2020

Notes on SQL Profiles vs SQL Plan Baselines

Filed under: performance — mdinh @ 10:48 pm

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

May 2, 2020

glogin.sql for Data Guard Environment

Filed under: Dataguard — mdinh @ 10:19 pm

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

When To Use dgmgrl / vs dgmgrl sys@tns

Filed under: 12c,Dataguard,dgmgrl — mdinh @ 3:41 pm

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.

 

Next Page »

Create a free website or blog at WordPress.com.