Thinking Out Loud

June 5, 2021

Data Pump Compatible Version

Filed under: 12.2,18c,DataPump — mdinh @ 12:51 am

Import failed as shown below:

Import: Release 18.0.0.0.0 - Production on Fri Jun 4 13:07:19 2021
Version 18.6.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39358: Export dump file version 18.0.0.0 not compatible with target version 12.1.0.2.0

Here are the compatible settings for source and target.

Source is 18.6.0.0.0 with compatible=18.0.0.0
Target is 18.6.0.0.0 with compatible=12.1.0.2

Run export with version.

expdp version=12.1.0.2

That’s all folks.

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

 

April 16, 2020

How To Check ORACLE_HOME Is Being Used

Filed under: 18c,Grid Infrastructure,Oracle_Cloud — mdinh @ 1:08 pm

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.

March 28, 2020

Silent Install 11.2.0.4 DB Software With GI 18c On OEL 7.7

Filed under: 11g,18c,Grid Infrastructure,OEL7,runInstaller — mdinh @ 8:45 pm

Just some note:

One good thing about GUI install is that it allows one to fix any issues and retry and not so much with silent install

================================================================================
Requirements for Installing Oracle 11.2.0.4 RDBMS on OL7 or RHEL7 64-bit (x86-64) (Doc ID 1962100.1)

PRVF-4037 : CRS is not installed on any of the nodes (Doc ID 1316815.1)

Installation of Oracle 11.2.0.4 Database Software on OL7 fails with 'Error in invoking target 'agent nmhs' of makefile ' &
"undefined reference to symbol 'B_DestroyKeyObject'" error (Doc ID 1965691.1)
================================================================================

================================================================================
### First install attempt without -ignorePrereq
================================================================================

$ ./runInstaller -ignorePrereq

Note that the above command does not perform any pre-requisite checks.
Hence, ensure that all the software requirements documented in the install guide are fulfilled before executing the installer using the above option.

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

[oracle@ol7-183-rac1 ~]$ ./install_db_software.sh

+ /u01/app/oracle/software/database/runInstaller -force -silent -waitforcompletion
-responseFile /u01/app/oracle/software/database/response/db_install.rsp
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=ol7-183-rac1.localdomain
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
SELECTED_LANGUAGES=en ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.EEOptionsSelection=false
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oper
oracle.install.db.CLUSTER_NODES=ol7-183-rac1,ol7-183-rac2
oracle.installer.autoupdates.option=SKIP_UPDATES
oracle.install.db.isRACOneInstall=false
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 25005 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 17391 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2020-03-26_04-15-06PM. Please wait ...

[FATAL] [INS-13013] Target environment do not meet some mandatory requirements.
CAUSE: Some of the mandatory prerequisites are not met. See logs for details. /u01/app/oraInventory/logs/installActions2020-03-26_04-15-06PM.log
ACTION: Identify the list of failed prerequisite checks from the log: /u01/app/oraInventory/logs/installActions2020-03-26_04-15-06PM.log.
Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
[oracle@ol7-183-rac1 ~]$

================================================================================
### Review types of errors
================================================================================

[oracle@ol7-183-rac1 ~]$ grep -e '[[:upper:]]: ' /u01/app/oraInventory/logs/installActions2020-03-26_04-15-06PM.log |cut -d ":" -f1 |sort -u
ACTION
CAUSE
INFO
SEVERE
WARNING
[oracle@ol7-183-rac1 ~]$

================================================================================
### Review List of failed Tasks
================================================================================

[oracle@ol7-183-rac1 ~]$ grep -A100 "List of failed Tasks" /u01/app/oraInventory/logs/installActions2020-03-26_04-15-06PM.log
INFO: ------------------List of failed Tasks------------------
INFO: *********************************************
INFO: Package: pdksh-5.2.14: This is a prerequisite condition to test whether the package "pdksh-5.2.14" is available on the system.
INFO: Severity:IGNORABLE
INFO: OverallStatus:VERIFICATION_FAILED
INFO: *********************************************
INFO: CRS Integrity: This test checks the integrity of Oracle Clusterware stack across the cluster nodes.
INFO: Severity:CRITICAL
INFO: OverallStatus:OPERATION_FAILED
INFO: *********************************************
INFO: Cluster Manager Integrity: This test checks the integrity of cluster manager across the cluster nodes.
INFO: Severity:CRITICAL
INFO: OverallStatus:OPERATION_FAILED
INFO: *********************************************
INFO: Node Application Existence: This test checks the existence of Node Applications on the system.
INFO: Severity:CRITICAL
INFO: OverallStatus:OPERATION_FAILED
INFO: *********************************************
INFO: Clock Synchronization: This test checks the Oracle Cluster Time Synchronization Services across the cluster nodes.
INFO: Severity:CRITICAL
INFO: OverallStatus:OPERATION_FAILED
INFO: *********************************************
INFO: Database Clusterware Version Compatibility: This test ensures that the Database version is compatible with the CRS version.
INFO: Severity:CRITICAL
INFO: OverallStatus:OPERATION_FAILED
INFO: -----------------End of failed Tasks List----------------
INFO: Adding ExitStatus PREREQUISITES_NOT_MET to the exit status set
SEVERE: [FATAL] [INS-13013] Target environment do not meet some mandatory requirements.
CAUSE: Some of the mandatory prerequisites are not met. See logs for details. /u01/app/oraInventory/logs/installActions2020-03-26_04-15-06PM.log
ACTION: Identify the list of failed prerequisite checks from the log: /u01/app/oraInventory/logs/installActions2020-03-26_04-15-06PM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
INFO: Advice is ABORT
INFO: Adding ExitStatus INVALID_USER_INPUT to the exit status set
INFO: Completed validating state {performChecks}
INFO: Terminating all background operations
INFO: Terminated all background operations
INFO: Finding the most appropriate exit status for the current application
INFO: Exit Status is -3
INFO: Shutdown Oracle Database 11g Release 2 Installer
[oracle@ol7-183-rac1 ~]$

================================================================================
### Search for "Error Message"
================================================================================

[oracle@ol7-183-rac1 ~]$ grep -i 'error message' /u01/app/oraInventory/logs/installActions2020-03-26_04-15-06PM.log
INFO: Error Message:PRVF-7532 : Package "pdksh" is missing on node "ol7-183-rac2"
INFO: Error Message:PRVF-7532 : Package "pdksh" is missing on node "ol7-183-rac1"
INFO: Error Message:PRVF-4037 : CRS is not installed on any of the nodes
INFO: Error Message:PRVF-4037 : CRS is not installed on any of the nodes
INFO: Error Message:PRVF-4037 : CRS is not installed on any of the nodes
INFO: Error Message:PRVF-4037 : CRS is not installed on any of the nodes
INFO: Error Message:PRVF-4037 : CRS is not installed on any of the nodes
[oracle@ol7-183-rac1 ~]$

================================================================================
PRVF-4037 : CRS is not installed on any of the nodes (Doc ID 1316815.1)
The bug is fixed in 11.2.0.3, the workaround is to update GI home with CRS="true" flag.
================================================================================

================================================================================
### Check inventory for GI RAC install
================================================================================

[oracle@ol7-183-rac1 ContentsXML]$ cat inventory.xml
(?xml version="1.0" standalone="yes" ?)
(!-- Copyright (c) 1999, 2020, Oracle and/or its affiliates.
All rights reserved. --)
(!-- Do not modify the contents of this file by hand. --)
(INVENTORY)
(VERSION_INFO)
(SAVED_WITH)12.2.0.4.0(/SAVED_WITH)
(MINIMUM_VER)2.1.0.6.0(/MINIMUM_VER)
(/VERSION_INFO)
(HOME_LIST)
(HOME NAME="OraGI18Home1" LOC="/u01/app/18.0.0/grid" TYPE="O" IDX="1" CRS="true"/)
(/HOME_LIST)
(COMPOSITEHOME_LIST)
(/COMPOSITEHOME_LIST)
(/INVENTORY)

================================================================================
### UPDATE inventory for GI RAC install
================================================================================

[oracle@ol7-183-rac1 ContentsXML]$ . oraenv {{{ +ASM1
ORACLE_SID = [cdbrac1] ? The Oracle base remains unchanged with value /u01/app/oracle

[oracle@ol7-183-rac1 ContentsXML]$ export GRID_HOME=$ORACLE_HOME

[oracle@ol7-183-rac1 ContentsXML]$ $GRID_HOME/oui/bin/runInstaller -silent -ignoreSysPrereqs -updateNodeList ORACLE_HOME=$GRID_HOME "CLUSTER_NODES={ol7-183-rac1,ol7-183-rac2}" CRS=true
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 17391 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
'UpdateNodeList' was successful.

================================================================================
### VERIFY inventory for GI RAC install
================================================================================

[oracle@ol7-183-rac1 ContentsXML]$ cat inventory.xml
(?xml version="1.0" standalone="yes" ?)
(!-- Copyright (c) 1999, 2020, Oracle and/or its affiliates.
All rights reserved. --)
(!-- Do not modify the contents of this file by hand. --)
(INVENTORY)
(VERSION_INFO)
(SAVED_WITH)12.2.0.4.0(/SAVED_WITH)
(MINIMUM_VER)2.1.0.6.0(/MINIMUM_VER)
(/VERSION_INFO)
(HOME_LIST)
(HOME NAME="OraGI18Home1" LOC="/u01/app/18.0.0/grid" TYPE="O" IDX="1" CRS="true")
(NODE_LIST)
(NODE NAME="ol7-183-rac1"/)
(NODE NAME="ol7-183-rac2"/)
(/NODE_LIST)
(/HOME)
(/HOME_LIST)
(COMPOSITEHOME_LIST)
(/COMPOSITEHOME_LIST)
(/INVENTORY'
[oracle@ol7-183-rac1 ContentsXML]$

================================================================================
### Retry Install
================================================================================

[oracle@ol7-183-rac1 ~]$ cat install_db_software.sh
#!/bin/sh -x
/u01/app/oracle/software/database/runInstaller -force \
-silent -waitforcompletion -ignorePrereq \
-responseFile /u01/app/oracle/software/database/response/db_install.rsp \
oracle.install.option=INSTALL_DB_SWONLY \
ORACLE_HOSTNAME=ol7-183-rac1.localdomain \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=/u01/app/oraInventory \
SELECTED_LANGUAGES=en \
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1 \
ORACLE_BASE=/u01/app/oracle \
oracle.install.db.InstallEdition=EE \
oracle.install.db.EEOptionsSelection=false \
oracle.install.db.DBA_GROUP=dba \
oracle.install.db.OPER_GROUP=oper \
oracle.install.db.CLUSTER_NODES=ol7-183-rac1,ol7-183-rac2 \
oracle.installer.autoupdates.option=SKIP_UPDATES \
oracle.install.db.isRACOneInstall=false \
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
DECLINE_SECURITY_UPDATES=true
[oracle@ol7-183-rac1 ~]$

[oracle@ol7-183-rac1 ~]$ ./install_db_software.sh
+ /u01/app/oracle/software/database/runInstaller -force -silent -waitforcompletion -ignorePrereq
-responseFile /u01/app/oracle/software/database/response/db_install.rsp
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=ol7-183-rac1.localdomain
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
SELECTED_LANGUAGES=en
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.EEOptionsSelection=false
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oper
oracle.install.db.CLUSTER_NODES=ol7-183-rac1,ol7-183-rac2
oracle.installer.autoupdates.option=SKIP_UPDATES
oracle.install.db.isRACOneInstall=false
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 24578 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 17391 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2020-03-26_05-17-28PM. Please wait ...

You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2020-03-26_05-17-28PM.log

The installation of Oracle Database 11g was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2020-03-26_05-17-28PM.log' for more details.

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

Execute /u01/app/oracle/product/11.2.0.4/dbhome_1/root.sh on the following nodes:
[ol7-183-rac1, ol7-183-rac2]

Successfully Setup Software.
[oracle@ol7-183-rac1 ~]$

[root@ol7-183-rac1 ~]# /u01/app/oracle/product/11.2.0.4/dbhome_1/root.sh
Check /u01/app/oracle/product/11.2.0.4/dbhome_1/install/root_ol7-183-rac1.localdomain_2020-03-26_17-44-13.log for the output of root script
[root@ol7-183-rac1 ~]#

[root@ol7-183-rac2 ~]# /u01/app/oracle/product/11.2.0.4/dbhome_1/root.sh
Check /u01/app/oracle/product/11.2.0.4/dbhome_1/install/root_ol7-183-rac2.localdomain_2020-03-26_17-44-55.log for the output of root script
[root@ol7-183-rac2 ~]#

================================================================================
### FROM silentInstall*.log - Known Issues - (Doc ID 1965691.1)
================================================================================

[oracle@ol7-183-rac1 ~]$ cat /u01/app/oraInventory/logs/silentInstall2020-03-26_05-17-28PM.log
silentInstall2020-03-26_05-17-28PM.log
sNativeVolName:/u01/app/oracle/product/11.2.0.4/dbhome_1/
m_asNodeArray:ol7-183-rac1,ol7-183-rac2
m_sLocalNode:ol7-183-rac1
sNativeVolName:/tmp/
m_asNodeArray:ol7-183-rac1,ol7-183-rac2
m_sLocalNode:ol7-183-rac1
Error in invoking target 'agent nmhs' of makefile '/u01/app/oracle/product/11.2.0.4/dbhome_1/sysman/lib/ins_emagent.mk'. See '/u01/app/oraInventory/logs/installActions2020-03-26_05-17-28PM.log' for details.
sNativeVolName:/u01/app/oracle/
m_asNodeArray:ol7-183-rac1,ol7-183-rac2
m_sLocalNode:ol7-183-rac1
sNativeVolName:/u01/app/oraInventory/
m_asNodeArray:ol7-183-rac1,ol7-183-rac2
m_sLocalNode:ol7-183-rac1
The installation of Oracle Database 11g was successful.
[oracle@ol7-183-rac1 ~]$

================================================================================
### Check installActions*.log
================================================================================

[oracle@ol7-183-rac1 ~]$ grep -e '[[:upper:]]: ' /u01/app/oraInventory/logs/installActions2020-03-26_05-17-28PM.log |cut -d ":" -f1 |sort -u
INFO
WARNING
[oracle@ol7-183-rac1 ~]$

================================================================================
### Check inventory for DB RAC install
================================================================================

[oracle@ol7-183-rac1 ContentsXML]$ cat inventory.xml
{?xml version="1.0" standalone="yes" ?}
{!-- Copyright (c) 1999, 2013, Oracle and/or its affiliates.
All rights reserved. --}
{!-- Do not modify the contents of this file by hand. --}
{INVENTORY}
{VERSION_INFO}
{SAVED_WITH}11.2.0.4.0{/SAVED_WITH}
{MINIMUM_VER}2.1.0.6.0{/MINIMUM_VER}
{/VERSION_INFO}
{HOME_LIST}
{HOME NAME="OraGI18Home1" LOC="/u01/app/18.0.0/grid" TYPE="O" IDX="1" CRS="true"}
{NODE_LIST}
{NODE NAME="ol7-183-rac1"/}
{NODE NAME="ol7-183-rac2"/}
{/NODE_LIST}
{/HOME}
{HOME NAME="OraDb11g_home1" LOC="/u01/app/oracle/product/11.2.0.4/dbhome_1" TYPE="O" IDX="2"}
{NODE_LIST}
{NODE NAME="ol7-183-rac1"/}
{NODE NAME="ol7-183-rac2"/}
{/NODE_LIST}
{/HOME}
{/HOME_LIST}
{COMPOSITEHOME_LIST}
{/COMPOSITEHOME_LIST}
{/INVENTORY}
[oracle@ol7-183-rac1 ContentsXML]$

================================================================================
### cluvfy comp healthcheck
================================================================================

[oracle@ol7-183-rac1 cvu]$ . oraenv <<< +ASM1
ORACLE_SID = [cdbrac1] ? The Oracle base remains unchanged with value /u01/app/oracle

[oracle@ol7-183-rac1 ~]$ cluvfy comp software

Verification of Health Check was unsuccessful.
Checks did not pass for the following nodes:
ol7-183-rac2,ol7-183-rac1

Failures were encountered during execution of CVU verification request "Health Check".

Verifying Physical Memory ...FAILED
ol7-183-rac2: PRVF-7530 : Sufficient physical memory is not available on node
"ol7-183-rac2" [Required physical memory = 8GB (8388608.0KB)]

ol7-183-rac1: PRVF-7530 : Sufficient physical memory is not available on node
"ol7-183-rac1" [Required physical memory = 8GB (8388608.0KB)]

Verifying Ethernet Jumbo Frames ...FAILED
ol7-183-rac2: PRVE-0293 : Jumbo Frames are not configured for interconnects
"eth2" on node "ol7-183-rac2.localdomain". [Expected="eth2=9000";
Found="eth2=1500"]

ol7-183-rac1: PRVE-0293 : Jumbo Frames are not configured for interconnects
"eth2" on node "ol7-183-rac1.localdomain". [Expected="eth2=9000";
Found="eth2=1500"]

CVU operation performed:      Health Check
Date:                         Mar 26, 2020 6:07:08 PM
CVU home:                     /u01/app/18.0.0/grid/
User:                         oracle
[oracle@ol7-183-rac1 cvu]$

January 28, 2020

Use srvctl stop home When Stopping Many Database Instances

Filed under: 12c,18c,19c,srvctl — mdinh @ 6:13 pm

=============================================================================
### Stops all Oracle clusterware resources that run from the Oracle home.
=============================================================================

Usage: srvctl stop home -oraclehome  -statefile  -node  [-stopoption ] [-force]
    -oraclehome              Oracle home path
    -statefile         Specify a file path for the 'srvctl stop home' command to store the state of the resources
    -node               Node name
    -stopoption      Stop options for the database. Examples of shutdown options are NORMAL, TRANSACTIONAL, IMMEDIATE, or ABORT.
    -force                         Force stop
    -help                          Print usage
[oracle@ol7-19-rac1 ~]$

=============================================================================
### Check ALL DB status running from same ORACLE_HOME for NODE
=============================================================================

[oracle@ol7-19-rac1 ~]$ srvctl status database -d cdbrac -v
Instance cdbrac1 is running on node ol7-19-rac1. Instance status: Open.
Instance cdbrac2 is running on node ol7-19-rac2. Instance status: Open.

[oracle@ol7-19-rac1 ~]$ srvctl status home -o $ORACLE_HOME -s $ORACLE_HOME/statushome.txt -node ol7-19-rac1
Database cdbrac is running on node ol7-19-rac1

[oracle@ol7-19-rac1 ~]$ cat $ORACLE_HOME/statushome.txt
db-cdbrac
[oracle@ol7-19-rac1 ~]$

=============================================================================
### STOP ALL DB running from same ORACLE_HOME for NODE
=============================================================================

[oracle@ol7-19-rac1 ~]$ srvctl stop home -o $ORACLE_HOME -s $ORACLE_HOME/stophome.txt -node ol7-19-rac1

[oracle@ol7-19-rac1 ~]$ cat $ORACLE_HOME/stophome.txt
db-cdbrac

[oracle@ol7-19-rac1 ~]$ srvctl status database -d cdbrac -v
Instance cdbrac1 is not running on node ol7-19-rac1
Instance cdbrac2 is running on node ol7-19-rac2. Instance status: Open.
[oracle@ol7-19-rac1 ~]$

=============================================================================
### START ALL DB running from same ORACLE_HOME for NODE
=============================================================================

[oracle@ol7-19-rac1 ~]$ srvctl start home -o $ORACLE_HOME -s $ORACLE_HOME/stophome.txt -node ol7-19-rac1

[oracle@ol7-19-rac1 ~]$ srvctl status database -d cdbrac -v
Instance cdbrac1 is running on node ol7-19-rac1. Instance status: Open.
Instance cdbrac2 is running on node ol7-19-rac2. Instance status: Open.
[oracle@ol7-19-rac1 ~]$

UPDATE: Mar 11, 2020 – Remove hard coding. Status works on GI as shown.

srvctl status home -o $ORACLE_HOME -s ~/statushome.txt -node $(hostname -s)
srvctl stop home -o $ORACLE_HOME -s ~/stophome.txt -node $(hostname -s)
srvctl start home -o $ORACLE_HOME -s ~/stophome.txt -node $(hostname -s)

[oracle@ol7-19-rac1 ~]$ . oraenv <<< +ASM1
ORACLE_SID = [+ASM1] ? The Oracle base remains unchanged with value /u01/app/oracle

[oracle@ol7-19-rac1 ~]$ srvctl status home -o $ORACLE_HOME -s ~/statushome.txt -node $(hostname -s)
Disk Group ora.CRS.dg is running on ol7-19-rac1
Disk Group ora.DATA.dg is running on ol7-19-rac1
Disk Group ora.RECO.dg is running on ol7-19-rac1
Listener ASMNET1LSNR_ASM is running on node ol7-19-rac1
Listener LISTENER is running on node ol7-19-rac1
SCAN listener ora.LISTENER_SCAN1.lsnr is running on node ol7-19-rac1
SCAN VIP ora.scan1.vip is running on node ol7-19-rac1
ONS daemon is running on node: ol7-19-rac1
VIP ora.ol7-19-rac1.vip is running on node: ol7-19-rac1
Network is running on node: ol7-19-rac1
[oracle@ol7-19-rac1 ~]$

November 22, 2019

Oracle Database 18c/19c: New Features asmcmd

Filed under: 18c,19c,ASM — mdinh @ 5:11 pm
============================================================
NEW:
============================================================

[oracle@ol7-19-rac1 ~]$ asmcmd showversion
ASM version         : 19.4.0.0.0
[oracle@ol7-19-rac1 ~]$ 

============================================================
OLD:
============================================================

[oracle@ol7-19-rac1 ~]$ asmcmd -V
asmcmd version 19.4.0.0.0
[oracle@ol7-19-rac1 ~]$

============================================================
NEW:
============================================================

[oracle@ol7-19-rac1 ~]$ asmcmd showpatches
---------------
List of Patches
===============
29401763
29517242
29517247
29585399
29834717
29850993
29851014

[oracle@ol7-19-rac1 ~]$ asmcmd showpatches -l
Oracle ASM release patch level is [2037353368] and 
the complete list of patches [29401763 29517242 29517247 29585399 29834717 29850993 29851014 ] have been applied on the local node. 
The release patch string is [19.4.0.0.0].
[oracle@ol7-19-rac1 ~]$

============================================================
OLD:
============================================================

### MISSING from OLD are previous 19.3 version:
29517247; ACFS RELEASE UPDATE 19.3.0.0.0	
29585399; OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
29517242; Database Release Update : 19.3.0.0.190416 (29517242)

[oracle@ol7-19-rac1 ~]$ crsctl query crs releasepatch
Oracle Clusterware release patch level is [2037353368] and 
the complete list of patches [29401763 29517242 29517247 29585399 29834717 29850993 29851014 ] have been applied on the local node. 
The release patch string is [19.4.0.0.0].
[oracle@ol7-19-rac1 ~]$

[oracle@ol7-19-rac1 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
29851014;ACFS RELEASE UPDATE 19.4.0.0.0 (29851014)
29850993;OCW RELEASE UPDATE 19.4.0.0.0 (29850993)
29834717;Database Release Update : 19.4.0.0.190716 (29834717)
29401763;TOMCAT RELEASE UPDATE 19.0.0.0.0 (29401763)

OPatch succeeded.
[oracle@ol7-19-rac1 ~]$

============================================================
NEW:
============================================================

[oracle@ol7-19-rac1 ~]$ asmcmd showversion --softwarepatch
ASM version         : 19.4.0.0.0
Software patchlevel : 2037353368
[oracle@ol7-19-rac1 ~]$

============================================================
OLD:
============================================================

[oracle@ol7-19-rac1 ~]$ crsctl query crs softwarepatch
Oracle Clusterware patch level on node ol7-19-rac1 is [2037353368].
[oracle@ol7-19-rac1 ~]$

============================================================
NEW:
============================================================

[oracle@ol7-19-rac1 ~]$ asmcmd showversion --active
Oracle ASM active version on the cluster is [19.0.0.0.0]. 
The cluster upgrade state is [NORMAL]. 
The cluster active patch level is [2037353368].
[oracle@ol7-19-rac1 ~]$

============================================================
OLD:
============================================================

[oracle@ol7-19-rac1 ~]$ crsctl query crs activeversion -f
Oracle Clusterware active version on the cluster is [19.0.0.0.0]. 
The cluster upgrade state is [NORMAL]. 
The cluster active patch level is [2037353368].
[oracle@ol7-19-rac1 ~]$

============================================================
NEW:
============================================================

[oracle@ol7-19-rac1 ~]$ asmcmd showversion --releasepatch
ASM version         : 19.4.0.0.0
Information about release patchlevel is unavailable since no ASM instance connected

[oracle@ol7-19-rac1 ~]$ asmcmd
ASMCMD> showversion --releasepatch
ASM version         : 19.4.0.0.0
Release patchlevel  : 2037353368
ASMCMD>

============================================================
OLD:
============================================================

[oracle@ol7-19-rac1 ~]$ crsctl query crs releasepatch
Oracle Clusterware release patch level is [2037353368] and 
the complete list of patches [29401763 29517242 29517247 29585399 29834717 29850993 29851014 ] have been applied on the local node. 
The release patch string is [19.4.0.0.0].
[oracle@ol7-19-rac1 ~]$

Basically, the new features for asmcmd existed for crsctl query and use the ones best suited.

October 22, 2019

srvctl config all

Filed under: 18c,19c,RAC,srvctl — mdinh @ 1:19 pm

Learned something new today and not sure if it’s new feature.

Seems a lot easier to gather clusterware configuration using one command.

Works with srvctl version: 18.0.0.0.0 or higher.

19c

oracle@ol7-19-rac2 ~]$ echo $ORACLE_HOME
/u01/app/19.0.0/grid

[oracle@ol7-19-rac2 ~]$ srvctl -version
srvctl version: 19.0.0.0.0

[oracle@ol7-19-rac2 ~]$ srvctl config all

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

Oracle Clusterware basic information
------------------------------------
  Operating system          Linux
  Name                      ol7-19-cluster
  Class                     STANDALONE
  Cluster nodes             ol7-19-rac1, ol7-19-rac2
  Version                   19.0.0.0.0
  Groups                    SYSOPER: SYSASM:dba SYSRAC:dba SYSDBA:dba
  OCR locations             +DATA
  Voting disk locations     DATA
  Voting disk file paths    /dev/oracleasm/asm-disk3

Cluster network configuration details
-------------------------------------
  Interface name  Type  Subnet           Classification
  eth1            IPV4  192.168.56.0/24  PUBLIC
  eth2            IPV4  192.168.1.0/24   PRIVATE, ASM

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

SCAN "ol7-19-scan" details
++++++++++++++++++++++++++
  Name                ol7-19-scan
  IPv4 subnet         192.168.56.0/24
  DHCP server type    static
  End points          TCP:1521

  SCAN listeners
  --------------
  Name              VIP address
  LISTENER_SCAN1    192.168.56.105
  LISTENER_SCAN2    192.168.56.106
  LISTENER_SCAN3    192.168.56.107


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

  ASM disk group details
  ++++++++++++++++++++++
  Name  Redundancy
  DATA  EXTERN

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

Database "ora.cdbrac.db" details
--------------------------------
  Name                ora.cdbrac.db
  Type                RAC
  Version             19.0.0.0.0
  Role                PRIMARY
  Management          AUTOMATIC
  policy
  SPFILE              +DATA
  Password file       +DATA
  Groups              OSDBA:dba OSOPER:oper OSBACKUP:dba OSDG:dba OSKM:dba OSRAC:dba
  Oracle home         /u01/app/oracle/product/19.0.0/dbhome_1
[oracle@ol7-19-rac2 ~]$

18c

[oracle@rac1 Desktop]$ srvctl -version
srvctl version: 18.0.0.0.0

[oracle@rac1 Desktop]$ srvctl config all

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

Oracle Clusterware basic information                                            
------------------------------------                                            
  Operating system         Linux                                           
  Name                     scan                                            
  Class                    STANDALONE                                      
  Cluster nodes            rac1, rac2                                      
  Version                  18.0.0.0.0                                      
  Groups                   SYSOPER:dba SYSASM:dba SYSRAC:dba SYSDBA:dba    
  Cluster home             /u01/app/18.0.0/grid                            
  OCR locations            +CRS                                            
  Voting disk locations    /dev/asm-disk8, /dev/asm-disk9, /dev/asm-disk7  

Cluster network configuration details                                           
-------------------------------------                                           
  Interface name  Type  Subnet           Classification  
  eth1            IPV4  10.1.1.0/24      PRIVATE, ASM    
  eth0            IPV4  192.168.11.0/24  PUBLIC          

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

SCAN "scan.localdomain" details                                                 
+++++++++++++++++++++++++++++++                                                 
  Name                scan.localdomain  
  IPv4 subnet         192.168.11.0/24   
  DHCP server type    static            
  End points          TCP:1521          

  SCAN listeners                                                                
  --------------                                                                
  Name        VIP address    
  LISTENER    192.168.11.60  


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

  ASM disk group details                                                        
  ++++++++++++++++++++++                                                        
  Name  Redundancy  
  CRS   NORMAL      
  DATA  EXTERN      
  FRA   EXTERN      
  RAC   EXTERN      

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

Database "ora.uptst.db" details                                                 
-------------------------------                                                 
  Name                ora.uptst.db                                                   
  Type                RAC                                                            
  Version             18.0.0.0.0                                                     
  Role                PRIMARY                                                        
  Management          AUTOMATIC                                                      
  policy                                                                             
  SPFILE              +DATA                                                          
  Password file       +DATA                                                          
  Groups              OSDBA:dba OSOPER:dba OSBACKUP:dba OSDG:dba OSKM:dba OSRAC:dba  
  Oracle home         /u01/app/oracle/product/18.0.0/db_home1                        

Database "ora.uptst2.db" details                                                
--------------------------------                                                
  Name                 ora.uptst2.db                                        
  Type                 RAC                                                  
  Version              12.1.0.2.0                                           
  Role                 PRIMARY                                              
  Management policy    AUTOMATIC                                            
  SPFILE               +DATA                                                
  Password file        +DATA                                                
  Groups               OSDBA:dba OSOPER:dba OSBACKUP:dba OSDG:dba OSKM:dba  
  Oracle home          /u01/app/oracle/product/12.1.0.2_1                   
[oracle@rac1 Desktop]$ 

June 28, 2019

DGMGRL Using Help To Learn About New Validate Features

Filed under: 18c,Dataguard,dgmgrl — mdinh @ 3:57 pm

Wouldn’t be nicer and much better if Oracle would add (NF) for new features to help syntax?

DGMGRL for Linux: Release 12.2.0.1.0

[oracle@db-fs-1 bin]$ ./dgmgrl /
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Fri Jun 28 17:49:16 2019

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

Welcome to DGMGRL, type "help" for information.
Connected to "orclcdb"
Connected as SYSDG.
DGMGRL> help validate

Performs an exhaustive set of validations for a member

Syntax:

  VALIDATE DATABASE [VERBOSE] <database name>;

  VALIDATE DATABASE [VERBOSE] <database name> DATAFILE <datafile number>
    OUTPUT=<file name>;

  VALIDATE DATABASE [VERBOSE] <database name> SPFILE;

  VALIDATE FAR_SYNC [VERBOSE] <far_sync name>
    [WHEN PRIMARY IS <database name>];

DGMGRL>

DGMGRL for Linux: Release 18.0.0.0.0

[oracle@ADC6160274 GDS]$ dgmgrl /
DGMGRL for Linux: Release 18.0.0.0.0 - Production on Fri Jun 28 15:54:36 2019
Version 18.3.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "chi"
Connected as SYSDG.
DGMGRL> help validate

Performs an exhaustive set of validations for a member

Syntax:

  VALIDATE DATABASE [VERBOSE] <database name>;

  VALIDATE DATABASE [VERBOSE] <database name> DATAFILE <datafile number>
    OUTPUT=<file name>;

  VALIDATE DATABASE [VERBOSE] <database name> SPFILE;

  VALIDATE FAR_SYNC [VERBOSE] <far_sync name>
    [WHEN PRIMARY IS <database name>];

  VALIDATE NETWORK CONFIGURATION FOR { ALL | <member name> }; [*** NF ***]

  VALIDATE STATIC CONNECT IDENTIFIER FOR { ALL | <database name> }; [*** NF ***]

DGMGRL>

validate network configuration

DGMGRL> validate network configuration for all;
Connecting to instance "sales" on database "sfo" ...
Connected to "sfo"
Checking connectivity from instance "sales" on database "sfo to instance "sales" on database "chi"...
Succeeded.
Connecting to instance "sales" on database "chi" ...
Connected to "chi"
Checking connectivity from instance "sales" on database "chi to instance "sales" on database "sfo"...
Succeeded.

Oracle Clusterware is not configured on database "sfo".
Connecting to database "sfo" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SLC02PNY.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sfo_DGMGRL)(INSTANCE_NAME=sales)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "sfo".

Oracle Clusterware is not configured on database "chi".
Connecting to database "chi" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ADC6160274.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=chi_DGMGRL)(INSTANCE_NAME=sales)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "chi".

validate static connect identifier

DGMGRL> validate static connect identifier for all;
Oracle Clusterware is not configured on database "sfo".
Connecting to database "sfo" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SLC02PNY.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sfo_DGMGRL)(INSTANCE_NAME=sales)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "sfo".

Oracle Clusterware is not configured on database "chi".
Connecting to database "chi" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ADC6160274.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=chi_DGMGRL)(INSTANCE_NAME=sales)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "chi".

DGMGRL>

May 5, 2019

What’s My Cluster Configuration

Filed under: 18c,Grid Infrastructure,RAC — mdinh @ 2:15 pm
[grid@ol7-183-node1 ~]$ . /media/patch/gi.env
The Oracle base has been set to /u01/app/grid
ORACLE_SID=+ASM1
ORACLE_BASE=/u01/app/grid
GRID_HOME=/u01/app/18.0.0.0/grid
ORACLE_HOME=/u01/app/18.0.0.0/grid
Oracle Instance alive for sid "+ASM1"

[grid@ol7-183-node1 ~]$ crsctl get cluster configuration
Name                : ol7-183-cluster
Configuration       : Cluster
Class               : Standalone Cluster
Type                : flex
The cluster is not extended.
--------------------------------------------------------------------------------
        MEMBER CLUSTER INFORMATION

      Name       Version        GUID                       Deployed Deconfigured
================================================================================
================================================================================

[grid@ol7-183-node1 ~]$ olsnodes -s -a -t
ol7-183-node1   Active  Hub     Unpinned
ol7-183-node2   Active  Hub     Unpinned

[grid@ol7-183-node1 ~]$ crsctl query crs releasepatch
Oracle Clusterware release patch level is [70732493] and the complete list of patches [27908644 27923415 28090523 28090553 28090557 28090564 28256701 ] have been applied on the local node. The release patch string is [18.3.0.0.0].

[grid@ol7-183-node1 ~]$ crsctl query crs activeversion -f
Oracle Clusterware active version on the cluster is [18.0.0.0.0]. The cluster upgrade state is [NORMAL]. The cluster active patch level is [70732493].
[grid@ol7-183-node1 ~]$
Next Page »

Create a free website or blog at WordPress.com.