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 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
- Use RMAN rather than expdp to backup the 11gR2 database on NAO3.
- Rysnc that over to NAO4
- Make sure Oracle 12 is down. Start Oracle 11.
- Restore RMAN into Oracle 11.
- In place upgrade to Oracle 12c
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- 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