NAASC Oracle 12c Upgrade Troubleshoot Wiki Page

1 Basic info

  • The current 11g machine is NAASC-ORACLE-3 (NAO3) and is running Golden Gate.
  • The 12c machine is NAASC-ORACLE-4 (NAO4).
  • The new Golden Gate machine is NAASC-GOLDENGATE-01 (NAGG01).

2 Fundamental problem

  • There has been a variety of adjustments to the NAO3 machine and we are still not producing consistent exports with 'expdp'.
  • Mike has done research regarding the error reported in the logs: "ORA-01555: snapshot too old: rollback segment number with name... too small"

  • UNDOTBS has been increased from 32 GB to match the 128 GB at SCO
  • The retention period has been increased from 7,200 to 28,800
SYS@ALMA.ARC.NA (ALMA-NAASC-ORACLE-3)> @sql/tablespace_report.sql

TABLESPACE_NAME                                                                            TOTAL_SPACE_MB FREE_SPACE_MB PERCENT_FREE
------------------------------------------------------------------------------------------ -------------- ------------- ------------
ALMA_BACKUP                                                                                          1024          1023         99.9
APOMON                                                                                               1024          1023         99.9
EMS                                                                                                  1024          1023         99.9
NGAS                                                                                                19456          4283        22.01
OGG                                                                                                  2048           393        19.19
REQUEST                                                                                              1024           657        64.16
SOURCECATALOGUE                                                                                      1024           797        77.83
SYSAUX                                                                                              13312        844.81         6.35
SYSTEM                                                                                               6144           559          9.1

UNDOTBS                                                                                            131072     130876.75        99.85

USERS                                                                                                1024           990        96.68
XDB                                                                                                  1024        895.19        87.42
XMLSTORE                                                                                       3384279.11         91987         2.72

13 rows selected.

SYS@ALMA.ARC.NA (ALMA-NAASC-ORACLE-3)> show parameter undo

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
undo_management                      string                            AUTO
undo_retention                       integer                           28800
undo_tablespace                      string                            UNDOTBS

  • The database exports are broken down by user: ALMA, NGAS, REQUEST, SOURCECATALOGUE, etc.
  • NAO3 does not have enough local disk space on the server and uses and NFS mounted filesystem to export to.
  • The scn is captured and is incorporated in the export. A sample export command is:
$ expdp system PARALLEL=8 FILESIZE=100G DIRECTORY=NAO3_ALMA dumpfile=ALMA_2017-05-08_%U.dmp logfile=ALMA-Export_2017-05-08.log schemas=ALMA flashback_scn=33355017417

2.3 Two logs of failed exports are attached

3 Another problem: memory over-utilization

  • This is a reposting of all the data sent to the DBA List.
  • Basically, we seem fine for RAM utilization but when the export starts, we overutilize and hit 10-18 GB of swap, which is a 30-60% overcommitment.
  • Ganglia. I started the export at 18:40 US ET.

NAO3 export.png
NAO3 Export
NAO3 has 32 GB RAM
64 GB swap
/dev/shm is 28 GB


SYS@ALMA.ARC.NA (ALMA-NAASC-ORACLE-3)> show parameter target
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
archive_lag_target                   integer                           0
db_flashback_retention_target        integer                           2880
fast_start_io_target                 integer                           0
fast_start_mttr_target               integer                           120
memory_max_target                    big integer                       24G
memory_target                        big integer                       16G
parallel_servers_target              integer                           256
pga_aggregate_target                 big integer                       0
sga_target                           big integer                       0

SYS@ALMA.ARC.NA (ALMA-NAASC-ORACLE-3)> show parameter sga
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
lock_sga                             boolean                           FALSE
pre_page_sga                         boolean                           FALSE
sga_max_size                         big integer                       16G
sga_target                           big integer                       0

As requested by Maxs - select component,CURRENT_SIZE/1024/1024,LAST_OPER_TYPE,LAST_OPER_TIME from v$sga_dynamic_components;:

SQL> @/home/oracle/sql/dynamic_components.sql

COMPONENT                CURRENT_SIZE/1024/1024 LAST_OPER_TYPE                          LAST_OPER_TIME
------------------------ ---------------------- --------------------------------------- ---------------
shared pool                                1728 STATIC
large pool                                   64 STATIC
java pool                                   128 STATIC
streams pool                               1024 STATIC
DEFAULT buffer cache                       2624 INITIALIZING
KEEP buffer cache                             0 STATIC
RECYCLE buffer cache                          0 STATIC
DEFAULT 2K buffer cache                       0 STATIC
DEFAULT 4K buffer cache                       0 STATIC
DEFAULT 8K buffer cache                       0 STATIC
DEFAULT 16K buffer cache                      0 STATIC

COMPONENT                CURRENT_SIZE/1024/1024 LAST_OPER_TYPE                          LAST_OPER_TIME
------------------------ ---------------------- --------------------------------------- ---------------
DEFAULT 32K buffer cache                      0 STATIC
Shared IO Pool                              512 STATIC
ASM Buffer Cache                              0 STATIC

14 rows selected.

4 RMAN Plan

  1. Use RMAN rather than expdp to backup the 11gR2 database on NAO3.
  2. Rysnc that over to NAO4
  3. Make sure Oracle 12 is down. Start Oracle 11.
  4. Restore RMAN into Oracle 11.
  5. In place upgrade to Oracle 12c
  6. Configure Golden Gate on NAGG01
Issues / Concerns With The Plan

  • We have to have an exact match of Oracle + patches from the OEL 5.11 server - NAO3 to install on the OEL 7.3 server - NAO4. I am working on this.
  • How do we account for the fact that the storage is already built and under grid for ASM 12c? Is there a compatibility flag to set to go back to 11.2? Do we need an 11gR2 grid installation then, if we can't use grid 12c, right at the start?
  • How do we account for all of the re-mapping as part of the migration exercise? Tables, Indexes and LOBs.
  • The ASM storage has been configured to use the new table layouts.
    /dev/xvdc (4TB,physical disk):   /backup (ext4 for FRA)
    /dev/xvdd (100GB,physical disk): GROUP1_DISK1 for GROUP1_SYSTEM
    /dev/xvde (100GB,physical disk): GROUP2_DISK1 for GROUP2_UNDO
    /dev/xvdf (100GB,physical disk): GROUP3_DISK1 for GROUP3_TEMP
    /dev/xvdg (300GB,physical disk): GROUP4_DISK1 for GROUP4_REDO1
    /dev/xvdh (300GB,physical disk): GROUP5_DISK1 for GROUP5_REDO2
    /dev/xvdi (300GB,physical disk): GROUP6_DISK1 for GROUP6_TABLE
    /dev/xvdj (300GB,physical disk): GROUP7_DISK1 for GROUP7_INDEX
    /dev/xvdk (2000GB,physical disk):GROUP8_DISK1 for GROUP8_LOB
    /dev/xvdl (2000GB,physical disk):GROUP8_DISK2 for GROUP8_LOB
    /dev/xvdm (2000GB,physical disk):GROUP8_DISK3 for GROUP8_LOB

Oracle OPatch Information
oracle@naasc-oracle-3:[~]$ /usr/local/oracle/app/oracle/product/11.2.0/database/OPatch/opatch lsinv
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation.  All rights reserved.

Oracle Home       : /usr/local/oracle/app/oracle/product/11.2.0/database
Central Inventory : /usr/local/oracle/app/oraInventory
   from           : /usr/local/oracle/app/oracle/product/11.2.0/database/oraInst.loc
OPatch version    : 11.2.0.3.4
OUI version       : 11.2.0.4.0
Log file location : /usr/local/oracle/app/oracle/product/11.2.0/database/cfgtoollogs/opatch/opatch2017-05-18_19-24-48PM_1.log

Lsinventory Output file location : /usr/local/oracle/app/oracle/product/11.2.0/database/cfgtoollogs/opatch/lsinv/lsinventory2017-05-18_19-24-48PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.4.0
There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch  19269498     : applied on Fri Feb 13 05:27:45 UTC 2015
Unique Patch ID:  17875333
   Created on 5 Aug 2014, 02:45:40 hrs PST8PDT
   Bugs fixed:
     17622427, 17545847, 17393683, 16912439, 17040764, 17205719, 16228604
     17389192, 17375354, 17016369, 16854386, 17027426, 17394950, 17341326
     17235750, 17546973, 18996843, 16956380, 17082359, 19032777, 17344412
     17889583, 17441661, 17390431, 16285691, 14338435
--------------------------------------------------------------------------------

Grid OPatch Information
OPatch succeeded.
oracle@naasc-oracle-3:[~]$ /usr/local/oracle/app/oracle/product/11.2.0/grid/OPatch/opatch lsinv
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation.  All rights reserved.

Oracle Home       : /usr/local/oracle/app/oracle/product/11.2.0/database
Central Inventory : /usr/local/oracle/app/oraInventory
   from           : /usr/local/oracle/app/oracle/product/11.2.0/database/oraInst.loc
OPatch version    : 11.2.0.3.4
OUI version       : 11.2.0.4.0
Log file location : /usr/local/oracle/app/oracle/product/11.2.0/database/cfgtoollogs/opatch/opatch2017-05-18_19-24-51PM_1.log

Lsinventory Output file location : /usr/local/oracle/app/oracle/product/11.2.0/database/cfgtoollogs/opatch/lsinv/lsinventory2017-05-18_19-24-51PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.4.0
There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch  19269498     : applied on Fri Feb 13 05:27:45 UTC 2015
Unique Patch ID:  17875333
   Created on 5 Aug 2014, 02:45:40 hrs PST8PDT
   Bugs fixed:
     17622427, 17545847, 17393683, 16912439, 17040764, 17205719, 16228604
     17389192, 17375354, 17016369, 16854386, 17027426, 17394950, 17341326
     17235750, 17546973, 18996843, 16956380, 17082359, 19032777, 17344412
     17889583, 17441661, 17390431, 16285691, 14338435
--------------------------------------------------------------------------------

OPatch succeeded.

5 Changelog / Activity Log

1) Bump MEMORY_TARGET to 22 G
SQL> alter system set memory_target=22G scope=both;

2) New import/export monitoring script:
SQL> @/home/oracle/sql/import_export_monitor.sql 

3) Check PCTVERSION and raise failing tables to 30. This check was scripted as show below - /home/oracle/sql/lobs_check.sql

SQL> select OWNER, TABLE_NAME, COLUMN_NAME, PCTVERSION, RETENTION from dba_lobs where OWNER='ALMA' order by 2,3;

SQL> alter table SCHED_BLOCK_STATUS modify lob(XML.XMLDATA) (pctversion 20);

You can also use the RETENTION parameter but not PCTVERSION and RETENTION. Just use one or the other.

This means any table that needs to be adjusted cannot have a retention setting.

This fix was for XML only.
SYS@ALMA.ARC.NA (ALMA-NAASC-ORACLE-3)> @/home/oracle/sql/lobs_check.sql

OWNER    TABLE_NAME                                                       COLUMN_NAME          PCTVERSION  RETENTION
-------- ---------------------------------------------------------------- -------------------- ---------- ----------
ALMA     AQUA_ATTACHMENT                                                  CONTENTS                              7200
ALMA     AQUA_COMMENT                                                     CCOMMENT                              7200
ALMA     AQUA_EXECBLOCK_ETL_DATA                                          ETL_DATA                              7200
ALMA     AQUA_PIPELINE_RUN                                                REPORT                                7200
ALMA     AQUA_STATUS_HISTORY                                              CCOMMENT                              7200
ALMA     ASA_MAIL_TEMPLATES                                               TEMPLATECONTENT                       7200
ALMA     BMMV_OBSPROPOSAL                                                 ABSTRACT_TEXT                         7200
ALMA     DR$TITLE_IDX$I                                                   TOKEN_INFO                            7200
ALMA     DR$TITLE_IDX$R                                                   DATA                                  7200
ALMA     OBS_PROJECT_STATUS                                               SYS_NC00009$                 10
ALMA     OBS_UNIT_SET_STATUS                                              SYS_NC00010$                 10
ALMA     PH1M_AUTOSAVE                                                    SAVED_TEXT                            7200
ALMA     PH1M_EMAIL_TEMPLATE                                              BODY                                  7200
ALMA     PH1M_EMAIL_TEMPLATE                                              QUERY                                 7200
ALMA     PH1M_PANEL                                                       MINUTES                               7200
ALMA     PRJ_ATTACHMENT                                                   CONTENTS                              7200
ALMA     PROPOSAL12_BACK                                                  APRC_COMMENT                 10
ALMA     PROPOSAL12_BACK                                                  ARP_COMMENT                  10
ALMA     PROPOSAL_CYCLE2_BACKUP                                           APRC_COMMENT                 10
ALMA     PROPOSAL_CYCLE2_BACKUP                                           ARP_COMMENT                  10
ALMA     SCHED_BLOCK_STATUS                                               SYS_NC00010$                 10
ALMA     SHIFTLOG_REPLY                                                   REPLY_COMMENT                         7200
ALMA     SHIFTLOG_REPLY_BAK                                               REPLY_COMMENT                         7200
ALMA     SLOG_ENTRY_ATTACH                                                SLOG_ATTACH_CONTENTS                  7200
ALMA     SLOG_ENTRY_ATTACH_BAK                                            SLOG_ATTACH_CONTENTS                  7200
ALMA     XML_ACAPOLARIZATION_ENTITIES                                     SYS_NC00004$                 10
ALMA     XML_ACCUMMODE_ENTITIES                                           SYS_NC00004$                 10
ALMA     XML_ACSALARMMESSAGE_ENTITIES                                     SYS_NC00004$                 10
ALMA     XML_ACSCOMMANDCENTERP_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_ACSCOMMANDCENTERT_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_ACSERROR_ENTITIES                                            SYS_NC00004$                 10
ALMA     XML_ACSLOGTS_ENTITIES                                            SYS_NC00004$                 10
ALMA     XML_ADDRESS_ENTITIES                                             SYS_NC00004$                 10
ALMA     XML_ALMARADIOMETERTAB_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_ANNOTATIONTABLE_ENTITIES                                     SYS_NC00004$                 10
ALMA     XML_ANTENNAMAKE_ENTITIES                                         SYS_NC00004$                 10
ALMA     XML_ANTENNAMOTIONPATT_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_ANTENNATABLE_ENTITIES                                        SYS_NC00004$                 10
ALMA     XML_ANTENNATYPE_ENTITIES                                         SYS_NC00004$                 10
ALMA     XML_ASDMBINARYTABLE_ENTITIES                                     SYS_NC00004$                 10
ALMA     XML_ASDM_ENTITIES                                                SYS_NC00004$                 10
ALMA     XML_ASICONFIGURATION_ENTITIES                                    SYS_NC00004$                 10
ALMA     XML_ASIMESSAGE_ENTITIES                                          SYS_NC00004$                 10
ALMA     XML_ASSOCIATEDCALNATU_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_ASSOCIATEDFIELDNA_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_ATMPHASECORRECTIO_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_AXISNAME_ENTITIES                                            SYS_NC00004$                 10
ALMA     XML_BASEBANDNAME_ENTITIES                                        SYS_NC00004$                 10
ALMA     XML_BASELINEREFERENCE_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_BEAMTABLE_ENTITIES                                           SYS_NC00004$                 10
ALMA     XML_BULKTEST_ENTITIES                                            SYS_NC00004$                 10
ALMA     XML_CALAMPLITABLE_ENTITIES                                       SYS_NC00004$                 10
ALMA     XML_CALAPPPHASETABLE_ENTITIES                                    SYS_NC00004$                 10
ALMA     XML_CALATMOSPHERETABL_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_CALBANDPASSTABLE_ENTITIES                                    SYS_NC00004$                 10
ALMA     XML_CALCURVETABLE_ENTITIES                                       SYS_NC00004$                 10
ALMA     XML_CALCURVETYPE_ENTITIES                                        SYS_NC00004$                 10
ALMA     XML_CALDATAORIGIN_ENTITIES                                       SYS_NC00004$                 10
ALMA     XML_CALDATATABLE_ENTITIES                                        SYS_NC00004$                 10
ALMA     XML_CALDELAYTABLE_ENTITIES                                       SYS_NC00004$                 10
ALMA     XML_CALDEVICETABLE_ENTITIES                                      SYS_NC00004$                 10
ALMA     XML_CALFLUXTABLE_ENTITIES                                        SYS_NC00004$                 10
ALMA     XML_CALFOCUSMODELTABL_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_CALFOCUSTABLE_ENTITIES                                       SYS_NC00004$                 10
ALMA     XML_CALGAINTABLE_ENTITIES                                        SYS_NC00004$                 10
ALMA     XML_CALHOLOGRAPHYTABL_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_CALIBRATIONDEVICE_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_CALIBRATIONFUNCTI_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_CALIBRATIONMODE_ENTITIES                                     SYS_NC00004$                 10
ALMA     XML_CALIBRATIONSET_ENTITIES                                      SYS_NC00004$                 10
ALMA     XML_CALPHASETABLE_ENTITIES                                       SYS_NC00004$                 10
ALMA     XML_CALPOINTINGMODELT_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_CALPOINTINGTABLE_ENTITIES                                    SYS_NC00004$                 10
ALMA     XML_CALPOSITIONTABLE_ENTITIES                                    SYS_NC00004$                 10
ALMA     XML_CALPRIMARYBEAMTAB_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_CALQUERYPARAMETER_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_CALREDUCTIONTABLE_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_CALSEEINGTABLE_ENTITIES                                      SYS_NC00004$                 10
ALMA     XML_CALTYPE_ENTITIES                                             SYS_NC00004$                 10
ALMA     XML_CALWVRTABLE_ENTITIES                                         SYS_NC00004$                 10
ALMA     XML_COMMONENTITY_ENTITIES                                        SYS_NC00004$                 10
ALMA     XML_COMMONTYPES_ENTITIES                                         SYS_NC00004$                 10
ALMA     XML_CONFIGDESCRIPTION_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_CORRELATIONBIT_ENTITIES                                      SYS_NC00004$                 10
ALMA     XML_CORRELATIONMODE_ENTITIES                                     SYS_NC00004$                 10
ALMA     XML_CORRELATORCALIBRA_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_CORRELATORMODETAB_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_CORRELATORNAME_ENTITIES                                      SYS_NC00004$                 10
ALMA     XML_CORRELATORTYPE_ENTITIES                                      SYS_NC00004$                 10
ALMA     XML_DATACONTENT_ENTITIES                                         SYS_NC00004$                 10
ALMA     XML_DATADESCRIPTIONTA_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_DATASCALE_ENTITIES                                           SYS_NC00004$                 10
ALMA     XML_DELAYMODELTABLE_ENTITIES                                     SYS_NC00004$                 10
ALMA     XML_DETECTORBANDTYPE_ENTITIES                                    SYS_NC00004$                 10
ALMA     XML_DIRECTIONREFERENC_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_DOPPLERREFERENCEC_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_DOPPLERTABLE_ENTITIES                                        SYS_NC00004$                 10
ALMA     XML_DOPPLERTRACKINGMO_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_EPHEMERISTABLE_ENTITIES                                      SYS_NC00004$                 10
ALMA     XML_EXECBLOCKTABLE_ENTITIES                                      SYS_NC00004$                 10
ALMA     XML_EXECCONFIG_ENTITIES                                          SYS_NC00004$                 10
ALMA     XML_FEEDTABLE_ENTITIES                                           SYS_NC00004$                 10
ALMA     XML_FIELDCODE_ENTITIES                                           SYS_NC00004$                 10
ALMA     XML_FIELDTABLE_ENTITIES                                          SYS_NC00004$                 10
ALMA     XML_FILTERMODE_ENTITIES                                          SYS_NC00004$                 10
ALMA     XML_FLAGCMDTABLE_ENTITIES                                        SYS_NC00004$                 10
ALMA     XML_FLAGTABLE_ENTITIES                                           SYS_NC00004$                 10
ALMA     XML_FLUXCALIBRATIONME_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_FOCUSMETHOD_ENTITIES                                         SYS_NC00004$                 10
ALMA     XML_FOCUSMODELTABLE_ENTITIES                                     SYS_NC00004$                 10
ALMA     XML_FOCUSTABLE_ENTITIES                                          SYS_NC00004$                 10
ALMA     XML_FREQOFFSETTABLE_ENTITIES                                     SYS_NC00004$                 10
ALMA     XML_FREQUENCYREFERENC_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_GAINTRACKINGTABLE_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_HISTORYTABLE_ENTITIES                                        SYS_NC00004$                 10
ALMA     XML_HOLOGRAPHYCHANNEL_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_HOLOGRAPHYTABLE_ENTITIES                                     SYS_NC00004$                 10
ALMA     XML_IDENTIFIERRANGE_ENTITIES                                     SYS_NC00004$                 10
ALMA     XML_INVALIDATINGCONDI_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_LOGGINGMI_ENTITIES                                           SYS_NC00004$                 10
ALMA     XML_MAINTABLE_ENTITIES                                           SYS_NC00004$                 10
ALMA     XML_NETSIDEBAND_ENTITIES                                         SYS_NC00004$                 10
ALMA     XML_OBSATTACHMENT_ENTITIES                                       SYS_NC00004$                 10
ALMA     XML_OBSERVATIONTABLE_ENTITIES                                    SYS_NC00004$                 10
ALMA     XML_OBSERVINGCONTROLS_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_OBSERVINGMODE_ENTITIES                                       SYS_NC00004$                 10
ALMA     XML_OBSPROJECT_ENTITIES                                          SYS_NC00004$                 10
ALMA     XML_OBSPROPOSAL_ENTITIES                                         SYS_NC00004$                 10
ALMA     XML_OBSREVIEW_ENTITIES                                           SYS_NC00004$                 10
ALMA     XML_OBSTOOLUSERPREFS_ENTITIES                                    SYS_NC00004$                 10
ALMA     XML_OUSSTATUS_ENTITIES                                           SYS_NC00004$                 10
ALMA     XML_POINTINGMETHOD_ENTITIES                                      SYS_NC00004$                 10
ALMA     XML_POINTINGMODELMODE_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_POINTINGMODELTABL_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_POINTINGTABLE_ENTITIES                                       SYS_NC00004$                 10
ALMA     XML_POLARIZATIONTABLE_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_POLARIZATIONTYPE_ENTITIES                                    SYS_NC00004$                 10
ALMA     XML_POSITIONMETHOD_ENTITIES                                      SYS_NC00004$                 10
ALMA     XML_POSITIONREFERENCE_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_PREFERENCES_ENTITIES                                         SYS_NC00004$                 10
ALMA     XML_PRIMARYBEAMDESCRI_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_PRIMITIVEDATATYPE_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_PROCESSORSUBTYPE_ENTITIES                                    SYS_NC00004$                 10
ALMA     XML_PROCESSORTABLE_ENTITIES                                      SYS_NC00004$                 10
ALMA     XML_PROCESSORTYPE_ENTITIES                                       SYS_NC00004$                 10
ALMA     XML_PROJECTSTATUS_ENTITIES                                       SYS_NC00004$                 10
ALMA     XML_PSETDEF_ENTITIES                                             SYS_NC00004$                 10
ALMA     XML_PSET_ENTITIES                                                SYS_NC00004$                 10
ALMA     XML_QLALARMS_ENTITIES                                            SYS_NC00004$                 10
ALMA     XML_QLATMOSPHERESUMMA_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_QLFOCUSSUMMARY_ENTITIES                                      SYS_NC00004$                 10
ALMA     XML_QLPHASESUMMARY_ENTITIES                                      SYS_NC00004$                 10
ALMA     XML_QLPOINTINGSUMMARY_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_QUICKLOOKDISPLAYX_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_QUICKLOOKDISPLAY_ENTITIES                                    SYS_NC00004$                 10
ALMA     XML_QUICKLOOKRESULT_ENTITIES                                     SYS_NC00004$                 10
ALMA     XML_QUICKLOOKSUMMARY_ENTITIES                                    SYS_NC00004$                 10
ALMA     XML_RADIALVELOCITYREF_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_RECEIVERBAND_ENTITIES                                        SYS_NC00004$                 10
ALMA     XML_RECEIVERSIDEBAND_ENTITIES                                    SYS_NC00004$                 10
ALMA     XML_RECEIVERTABLE_ENTITIES                                       SYS_NC00004$                 10
ALMA     XML_SBSTATUS_ENTITIES                                            SYS_NC00004$                 10
ALMA     XML_SBSUMMARYTABLE_ENTITIES                                      SYS_NC00004$                 10
ALMA     XML_SBTYPE_ENTITIES                                              SYS_NC00004$                 10
ALMA     XML_SCALETABLE_ENTITIES                                          SYS_NC00004$                 10
ALMA     XML_SCANINTENT_ENTITIES                                          SYS_NC00004$                 10
ALMA     XML_SCANTABLE_ENTITIES                                           SYS_NC00004$                 10
ALMA     XML_SCHEDBLOCK_ENTITIES                                          SYS_NC00004$                 10
ALMA     XML_SCHEDULERMODE_ENTITIES                                       SYS_NC00004$                 10
ALMA     XML_SCHEDULINGPOLICY_ENTITIES                                    SYS_NC00004$                 10
ALMA     XML_SCHEMA_ENTITIES                                              XML                          10
ALMA     XML_SCIPIPEREQUEST_ENTITIES                                      SYS_NC00004$                 10
ALMA     XML_SCIPIPERESULTS_ENTITIES                                      SYS_NC00004$                 10
ALMA     XML_SDMDATAHEADER_ENTITIES                                       SYS_NC00004$                 10
ALMA     XML_SEEINGTABLE_ENTITIES                                         SYS_NC00004$                 10
ALMA     XML_SIDEBANDPROCESSIN_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_SOURCEMODEL_ENTITIES                                         SYS_NC00004$                 10
ALMA     XML_SOURCETABLE_ENTITIES                                         SYS_NC00004$                 10
ALMA     XML_SPECIALSB_ENTITIES                                           SYS_NC00004$                 10
ALMA     XML_SPECTRALRESOLUTIO_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_SPECTRALWINDOWTAB_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_SQUARELAWDETECTOR_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_STATETABLE_ENTITIES                                          SYS_NC00004$                 10
ALMA     XML_STATIONTABLE_ENTITIES                                        SYS_NC00004$                 10
ALMA     XML_STATIONTYPE_ENTITIES                                         SYS_NC00004$                 10
ALMA     XML_STOKESPARAMETER_ENTITIES                                     SYS_NC00004$                 10
ALMA     XML_STYLESHEETS                                                  SYS_NC00004$                 10
ALMA     XML_SUBSCANFIELDSOURC_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_SUBSCANINTENT_ENTITIES                                       SYS_NC00004$                 10
ALMA     XML_SUBSCANSPECTRALSP_ENTITIES                                   SYS_NC00004$                 10
ALMA     XML_SUBSCANTABLE_ENTITIES                                        SYS_NC00004$                 10
ALMA     XML_SWITCHCYCLETABLE_ENTITIES                                    SYS_NC00004$                 10
ALMA     XML_SWITCHINGMODE_ENTITIES                                       SYS_NC00004$                 10
ALMA     XML_SYSCALMETHOD_ENTITIES                                        SYS_NC00004$                 10
ALMA     XML_SYSCALTABLE_ENTITIES                                         SYS_NC00004$                 10
ALMA     XML_SYSPOWERTABLE_ENTITIES                                       SYS_NC00004$                 10
ALMA     XML_TESTOBSPROJECT_ENTITIES                                      SYS_NC00004$                 10
ALMA     XML_TESTOBSPROPOSAL_ENTITIES                                     SYS_NC00004$                 10
ALMA     XML_TESTSCHEDBLOCK_ENTITIES                                      SYS_NC00004$                 10
ALMA     XML_TESTVALUETYPES_ENTITIES                                      SYS_NC00004$                 10
ALMA     XML_TIMESAMPLING_ENTITIES                                        SYS_NC00004$                 10
ALMA     XML_TIMESCALE_ENTITIES                                           SYS_NC00004$                 10
ALMA     XML_TOTALPOWERTABLE_ENTITIES                                     SYS_NC00004$                 10
ALMA     XML_USER_ENTITIES                                                SYS_NC00004$                 10
ALMA     XML_VALUETYPES_ENTITIES                                          SYS_NC00004$                 10
ALMA     XML_WEATHERTABLE_ENTITIES                                        SYS_NC00004$                 10
ALMA     XML_WEIGHTTYPE_ENTITIES                                          SYS_NC00004$                 10
ALMA     XML_WINDOWFUNCTION_ENTITIES                                      SYS_NC00004$                 10
ALMA     XML_WVMCALTABLE_ENTITIES                                         SYS_NC00004$                 10
ALMA     XML_WVRMETHOD_ENTITIES                                           SYS_NC00004$                 10
ALMA     XSLT                                                             SYS_NC00003$                 10

211 rows selected.

The failing tables are:

# grep skipped *.log | sort -k5

ALMA-Export_2017-05-02.log:ORA-31693: Table data object "ALMA"."OBS_UNIT_SET_STATUS" failed to load/unload and is being skipped due to error:
ALMA-Export_2017-05-08.log:ORA-31693: Table data object "ALMA"."OBS_UNIT_SET_STATUS" failed to load/unload and is being skipped due to error:

ALMA-Export_2017-05-02.log:ORA-31693: Table data object "ALMA"."SCHED_BLOCK_STATUS" failed to load/unload and is being skipped due to error:
ALMA-Export_2017-05-08.log:ORA-31693: Table data object "ALMA"."SCHED_BLOCK_STATUS" failed to load/unload and is being skipped due to error:

ALMA-Export_2017-05-02.log:ORA-31693: Table data object "ALMA"."XML_ALMARADIOMETERTAB_ENTITIES" failed to load/unload and is being skipped due to error:

ALMA-Export_2017-05-02.log:ORA-31693: Table data object "ALMA"."XML_CALDEVICETABLE_ENTITIES" failed to load/unload and is being skipped due to error:

ALMA-Export_2017-05-02.log:ORA-31693: Table data object "ALMA"."XML_SYSCALTABLE_ENTITIES" failed to load/unload and is being skipped due to error:

 The PCTVERSION has been adjusted with a script:

alter table ALMA.OBS_UNIT_SET_STATUS modify lob(XML.XMLDATA) (pctversion 30);
alter table ALMA.SCHED_BLOCK_STATUS modify lob(XML.XMLDATA) (pctversion 30);
alter table ALMA.XML_ALMARADIOMETERTAB_ENTITIES modify lob(XML.XMLDATA) (pctversion 30);
alter table ALMA.XML_CALDEVICETABLE_ENTITIES modify lob(XML.XMLDATA) (pctversion 30);
alter table ALMA.XML_SYSCALTABLE_ENTITIES modify lob(XML.XMLDATA) (pctversion 30);
alter table ALMA.XML_ASDM_ENTITIES modify lob(XML.XMLDATA) (pctversion 30);
alter table ALMA.XML_SDMDATAHEADER_ENTITIES modify lob(XML.XMLDATA) (pctversion 30);
alter table ALMA.XML_FLAGTABLE_ENTITIES modify lob(XML.XMLDATA) (pctversion 30);
alter table ALMA.XML_FEEDTABLE_ENTITIES modify lob(XML.XMLDATA) (pctversion 30);
alter table ALMA.XML_CALPHASETABLE_ENTITIES modify lob(XML.XMLDATA) (pctversion 30);

SYS@ALMA.ARC.NA (ALMA-NAASC-ORACLE-3)> select OWNER, TABLE_NAME, COLUMN_NAME, PCTVERSION, RETENTION from dba_lobs where OWNER='ALMA' and PCTVERSION ='30'order by 2,3;

OWNER    TABLE_NAME                                                       COLUMN_NAME          PCTVERSION  RETENTION
-------- ---------------------------------------------------------------- -------------------- ---------- ----------
ALMA     OBS_UNIT_SET_STATUS                                              SYS_NC00010$                 30
ALMA     SCHED_BLOCK_STATUS                                               SYS_NC00010$                 30
ALMA     XML_ALMARADIOMETERTAB_ENTITIES                                   SYS_NC00004$                 30
ALMA     XML_CALDEVICETABLE_ENTITIES                                      SYS_NC00004$                 30
ALMA     XML_SYSCALTABLE_ENTITIES                                         SYS_NC00004$                 30
ALMA     XML_ASDM_ENTITIES                                         SYS_NC00004$                 30
ALMA     XML_SDMDATAHEADER_ENTITIES                                         SYS_NC00004$                 30
ALMA     XML_FLAGTABLE_ENTITIES                                                    SYS_NC00004$                 30
ALMA     XML_FEEDTABLE_ENTITIES                                                    SYS_NC00004$                 30
ALMA     XML_CALPHASETABLE_ENTITIES                                                    SYS_NC00004$                 30

4) NAO3 ALMA Export started at 2017-05-12-12:45 US ET
SYS@ALMA.ARC.NA (ALMA-NAASC-ORACLE-3)> @/home/oracle/sql/scn_show.sql

      CURRENT_SCN
-----------------
      33405461228

$ screen -S NAO3_Export
$ expdp system PARALLEL=8 FILESIZE=100G DIRECTORY=NAO3_ALMA dumpfile=ALMA_2017-05-12_%U.dmp logfile=ALMA-Export_2017-05-12.log schemas=ALMA flashback_scn=33405461228

Sample output from monitoring script:

SYS@ALMA.ARC.NA (ALMA-NAASC-ORACLE-3)> @sql/import_export_monitor.sql

     % MESSAGE                          TARGET                           START_TIME      Min Left FINISH_TIME
------ -------------------------------- -------------------------------- --------------- -------- ---------------
   .36 Table Scan:  ALMA.XML_CALATMOSPH ALMA.XML_CALATMOSPHERETABL_ENTIT 12-MAY-17         12,477 21-MAY-17
       ERETABL_ENTITIES: 170 out of 478 IES
       53 Blocks done

 79.23 Table Scan:  ALMA.XML_CALDEVICET ALMA.XML_CALDEVICETABLE_ENTITIES 12-MAY-17             12 12-MAY-17
       ABLE_ENTITIES: 10747 out of 1356
       4 Blocks done

 85.38 Table Scan:  ALMA.XML_CALPHASETA ALMA.XML_CALPHASETABLE_ENTITIES  12-MAY-17              8 12-MAY-17
       BLE_ENTITIES: 15660 out of 18341
        Blocks done

 74.56 Table Scan:  ALMA.XML_FEEDTABLE_ ALMA.XML_FEEDTABLE_ENTITIES      12-MAY-17             15 12-MAY-17
       ENTITIES: 13263 out of 17788 Blo
       cks done

 62.70 Table Scan:  ALMA.XML_FLAGTABLE_ ALMA.XML_FLAGTABLE_ENTITIES      12-MAY-17             27 12-MAY-17
       ENTITIES: 9066 out of 14459 Bloc
       ks done

 15.88 Table Scan:  ALMA.XML_QLPHASESUM ALMA.XML_QLPHASESUMMARY_ENTITIES 12-MAY-17            236 12-MAY-17
       MARY_ENTITIES: 2802 out of 17648
        Blocks done

 34.56 Table Scan:  ALMA.XML_SDMDATAHEA ALMA.XML_SDMDATAHEADER_ENTITIES  12-MAY-17             84 12-MAY-17
       DER_ENTITIES: 2448588 out of 708
       4071 Blocks done

   .00 SYS_EXPORT_SCHEMA_02: EXPORT : 0                                  12-MAY-17
        out of 3285191 MB done


8 rows selected.

5) Tried fixing the segfault caused by Bug 18758878 - Automatic SQL tuning advisor fails. Tweak from Oracle did not fix it. Got irritated and disabled the job altogether.

I had to use a mix of:

https://www.pythian.com/blog/yury-remember-this-is-how-you-disable-auto_tasks-in-11g/

And this for Oracle Docs:

17.2.2 Enabling and Disabling Automatic SQL Tuning

Automatic SQL tuning runs as part of the automated maintenance tasks infrastructure.

To enable automatic SQL tuning, use the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:

BEGIN DBMS_AUTO_TASK_ADMIN.ENABLE( client_name => 'sql tuning advisor' , operation => NULL , window_name => NULL ); END; /

To disable automatic SQL tuning, use the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:

BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'sql tuning advisor' , operation => NULL , window_name => NULL ); END; /


SYS@ALMA.ARC.NA (ALMA-NAASC-ORACLE-3)> select client_name, status,attributes,service_name from dba_autotask_client;

CLIENT_NAME                              STATUS                   ATTRIBUTES
---------------------------------------- ------------------------ ------------------------------------------------------------
SERVICE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
auto optimizer stats collection          DISABLED                 ON BY DEFAULT, VOLATILE, SAFE TO KILL

auto space advisor                       DISABLED                 ON BY DEFAULT, VOLATILE, SAFE TO KILL

sql tuning advisor                       DISABLED                 ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL
Topic attachments
ISorted ascending Attachment Action Size Date Who Comment
ALMA-Export_2017-05-02.loglog ALMA-Export_2017-05-02.log manage 30 K 2017-05-11 - 15:38 MikeHatz May 2 Export Log
ALMA-Export_2017-05-08.loglog ALMA-Export_2017-05-08.log manage 27 K 2017-05-11 - 15:39 MikeHatz May 8 Export Log
NAO3_export.pngpng NAO3_export.png manage 265 K 2017-05-11 - 16:05 MikeHatz Ganglia Sample Export
igp_ea135fa03afef9d27fc370c5a4d16ba2_NAO3_export.pngpng igp_ea135fa03afef9d27fc370c5a4d16ba2_NAO3_export.png manage 139 K 2017-05-11 - 16:17 UnknownUser  
Topic revision: r13 - 2017-05-28, MaxsSimmonds
This site is powered by FoswikiCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding NRAO Public Wiki? Send feedback