database export failing

Hi!

Our daily backup on the agile db server does an export of the database.

The export is not completing. It stops at ‘baseline_rev.’ (see attached)

Is there another log file that would explain why the export is failing?

 

Thanks

Laura

 

Agile User Asked on December 5, 2019 in Product Collaboration.
Add Comment
6 Answer(s)

HI Laura,

 

I’m not seeing the attachment. Can you post the last few lines from the export log? This should give a clue as to what is going on. Also, is there sufficient memory and disk space on the server?

Agile Angel Answered on December 5, 2019.
Add Comment

Hi Keith,

I started an export last night at 7:00pm and it’s still running 12 hours later! It’s now 17.1gb and log still shows it is on change_history! I’ve never seen a db dmp so big.

I just used the agile9exp.bat job:

set NLS_LANG=American_America.AL32UTF8
set ORACLE_HOME=D:\oracle\product\12.1.0\dbhome_1
set ORACLE_SID=agile9

%ORACLE_HOME%\bin\sqlplus agile/%PW_AGILE% @removestatsjob.sql
%ORACLE_HOME%\bin\exp agile/%PW_AGILE% parfile=agile9exp.par
%ORACLE_HOME%\bin\sqlplus agile/%PW_AGILE% @submitstatsjob.sql

 

parfile: file=agile9exp120519.dmp log=agile9exp120519.log owner=agile

 

Do you think I should stop it? I’ve never seen a dmp file so big. The company has been on agile since 2013.

I found one dmp file from 2016 on the server that was 4.89gb back then so I figured it would be around 9gb now but this is much larger.

I know they did use PC and PQM.

Here’s what the ora files look like:

 

https://link.shutterfly.com/PH2sJypzc2

 

Let me know if you think the dmp file is ok.

Thanks,

Laura

And the server has 8gb RAM and plenty of disc space.

Agile User Answered on December 6, 2019.
Add Comment

With this size of output, I recommend using data pump vs. export. Take a look at agile9expdp.bat. There may be some opportunities to clean up the system, such as truncating the A_DW_TXL_LOG table, which is only used for OPLA. The data files are very large. Typically they are capped at 4 GB and additional data files added to support additional space needs. I wonder how many extents are in the system and it may be time to export / drop / import the dmp file to clean it up a bit.

Agile Angel Answered on December 6, 2019.
Add Comment

At 17GB, something is definitely wrong.  Interesting that the AGILE_DATA2 tablespace file is so large in comparison to all the others (26GB vs. 2GB for all others). Of course, that is where CHANGE_HISTORY is located. But also PROPERTYTABLE, ATTACHMENTS, ITEM, NOTIFICATION and NOTIFICATION_FIELDS. That is 2 (CH and NF) out of the 4 tables that usually take the most time during import or export (also ITEM_HISTORY and A_DW_TXN_LOG).

As Keith suggested, try using DataPump and also truncate the A_DW_TXN_LOG table. Also get a count of records from CHANGE_HISTORY, see how large it is. And get a count of records by class from CHANGE_HISTORY (select class_id, count(*) from change_history group by class_id order by class_id). It might be interesting to see if you are getting some weird records in there (like a lot of PDP records or null records). Right now, I think that is a good place to start looking.

Agile Angel Answered on December 6, 2019.

Actually, the following query is even easier : select table_name, num_rows from user_tables where tablespace_name = ‘AGILE_DATA2’ where num_rows > 0; This won’t require executing a count on anything, although it might not be exact either. Then again, close is good enough.

on December 6, 2019.

I ran

select table_name, num_rows from user_tables where tablespace_name = ‘AGILE_DATA2’ and (num_rows > 0);

and got back:

PROPERTYTABLE   274097
VERSION   129073
CONVERSION   1
NOTIFICATION   3069
NOTIFICATION_FIELDS   26815
NOTIFICATION_TEMPLATE   17
NOTIFICATION_USER   095
ORGANIZATION   1
PROGRAM_HISTORY   1
PROGRAM_TEMPLATE   1
USER_PASSWORD_HISTORY   312
ITEM   13558
MANU_PARTS   7391
USER_APPROVAL_PASSWORD_HISTORY   36
RENDITION_FILE_MAP   8
OBJECT_MAP   4904
CACHE_FILE_MAP  1 2
CHANGE_HISTORY   138214335

 

I ran the other query as well:

select class_id, count(*) from change_history group by class_id order by class_id;

and got:

1450      22655
3122      2
4428      52
4878      84580
4895      98683
4928      7360
6000      317337
6159      512088
7000      155
8000      53577
11761      137925126

 

Can I run the datapump with users on the system?

 

Thanks so much. I appreciate your help.

Laura

 

 

on December 9, 2019.

And that is interesting. 11761 (which most records have as CLASS_ID) is for Automated Transfer Orders. Or what might be used for sending a data package somewhere. Do they have a PX that runs on a schedule, to send data to a downstream system? And does it check if there is anything *to* send?

Also look at the OUTBOUND table, as that is where ATO/CTOs are stored (and not in CHANGE). OUTBOUND_OBJECTS shows the objects that are handled by the ATO or CTO. If an ATO/CTO has nothing in OUTBOUND_OBJECTS, I seriously think you can delete it and thus clean up a LOT of records out of CHANGE_HISTORY, OUTBOUND, PAGE_TWO and PAGE_THREE. If you want a script to do that deletion, let me know, as there are about 5-6 tables linked to OUTBOUND. Contact me via LInkedIn.

And yes, you can run the datapump export with users on the system. All the usual caveats apply, but you can do it.

on December 10, 2019.
Add Comment

The export completed after nearly 24 hours, it ended up at 33.1gb!!  Change history was huge 136542511 !

I will do those queries and post soon.

Here’s the export log.

I tried to import the db dmp but the import failed after BOM_RESULT.

I need to get the test db back up and make sure this company has valid backups to restore from. I think that export was the first one to complete since 2016, but what good is it if it can’t be imported?

Connected to: Oracle Database 12c Release 12.1.0.1.0 – 64bit Production
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user AGILE
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user AGILE
About to export AGILE’s objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export AGILE’s tables via Conventional Path …
. . exporting table ACCOUNT_LOCKOUT_NOTIFY 36 rows exported
. . exporting table ACTION_ITEM 0 rows exported
. . exporting table ACTIVITY 0 rows exported
. . exporting table ACTIVITY_BASELINE 0 rows exported
. . exporting table ACTIVITY_DEPENDENCY 0 rows exported
. . exporting table ADMINCLOBATT 0 rows exported
. . exporting table ADMINCRITERIA 829 rows exported
. . exporting table ADMINMSATT 13078 rows exported
. . exporting table ADMIN_HISTORY 8384 rows exported
. . exporting table ADMIN_HISTORY_DETAILS 8384 rows exported
. . exporting table AGGREGATED_SUBSTANCE 0 rows exported
. . exporting table AGILESEQUENCES 133 rows exported
. . exporting table AGILEUSER 323 rows exported
. . exporting table AGILE_FLEX 222023 rows exported
. . exporting table ALIAS 0 rows exported
. . exporting table APPLIEDTO 152970 rows exported
. . exporting table APPROVAL_ITEMS 0 rows exported
. . exporting table APP_PROPERTY 0 rows exported
. . exporting table ASL_MAP 0 rows exported
. . exporting table ATTACHMENT 116488 rows exported
. . exporting table ATTACHMENTS 0 rows exported
. . exporting table ATTACHMENT_MAP 66353 rows exported
. . exporting table ATTACH_REDLINE 0 rows exported
. . exporting table ATTR_PREF 0 rows exported
. . exporting table AUTHORIZED_USER 0 rows exported
. . exporting table AUTH_FAILED 2228 rows exported
. . exporting table AUTONUMBER 0 rows exported
. . exporting table AUTOSELECT_TEMP 0 rows exported
. . exporting table AVL 0 rows exported
. . exporting table A_CLUSTER_CACHE_SYNCH_LOG 6670 rows exported
. . exporting table A_CONTENT_FOLDER 20 rows exported
. . exporting table A_CONTENT_FOLDER_MAP 0 rows exported
. . exporting table A_DW_TXN_LOG 7845111 rows exported
. . exporting table A_PROJ_RSRC 0 rows exported
. . exporting table A_SUBSTANCE_ALIAS 0 rows exported
. . exporting table A_TMP_BOM_INT_PROC
. . exporting table A_UI_BLOB_DATA 0 rows exported
. . exporting table A_UI_PAGE 1 rows exported
. . exporting table A_UI_WIDGET 7 rows exported
. . exporting table A_UI_WIDGET_CONFIG 0 rows exported
. . exporting table A_UI_WIDGET_PAGE 7 rows exported
. . exporting table BASELINE_REV 0 rows exported
. . exporting table BLOB_DATA 0 rows exported
. . exporting table BOM 23992 rows exported
. . exporting table BOM_RESULT 1 rows exported
. . exporting table BO_ATTACH_VERSIONS_HISTORY 142353 rows exported
. . exporting table BUYER_LINE_CARD 0 rows exported
. . exporting table BUYER_PLANNER 0 rows exported
. . exporting table CACHE_FILE_MAP 0 rows exported
. . exporting table CACHE_FILE_MAP1 2 rows exported
. . exporting table CHANGE 9566 rows exported
. . exporting table CHANGE_HISTORY 136542511 rows exported
. . exporting table CLASS_AUTONUMBER 0 rows exported
. . exporting table CLOB_DATA 12 rows exported
. . exporting table CLUSTER_THREAD_LOCKS 22 rows exported
. . exporting table COMMODITY_DUPS 0 rows exported
. . exporting table COMPLIANCE 0 rows exported
. . exporting table CONTENT 9 rows exported
. . exporting table CONTRACT 0 rows exported
. . exporting table CONVERSION 1 rows exported
. . exporting table CRITERIA 3458 rows exported
. . exporting table CURRENCY 0 rows exported
. . exporting table CUSTOMER 36 rows exported
. . exporting table DATE_FORMAT 1 rows exported
. . exporting table DISCUSSION 1 rows exported
. . exporting table DISCUSSION_WHERE_USED 0 rows exported
. . exporting table DLS_ATTRIBUTE_INFO 0 rows exported
. . exporting table DLS_ITEM_ATTRS 0 rows exported
. . exporting table DLS_ITEM_MFGS 0 rows exported
. . exporting table DLS_ITEM_RECORDS 0 rows exported
. . exporting table DLS_ITEM_SAMPLE_DATA 0 rows exported
. . exporting table DLS_JOB_MESSAGES 0 rows exported
. . exporting table DLS_SEMANTIC_CACHE 0 rows exported
. . exporting table DL_AML 0 rows exported
. . exporting table DL_BOM 0 rows exported
. . exporting table DL_REV 0 rows exported
. . exporting table EHUB_HEARTBEAT 1 rows exported
. . exporting table EHUB_STATE 1 rows exported
. . exporting table ESCALATIONS 2 rows exported
. . exporting table EVENT_HISTORY 8844 rows exported
. . exporting table EXTENSION_MODULES 0 rows exported
. . exporting table EXTENSION_PROCEDURES 0 rows exported
. . exporting table EXTERNAL_OBJECT 0 rows exported
. . exporting table FACT_TABLE 0 rows exported
. . exporting table FACT_TABLE_TASK 0 rows exported
. . exporting table FILES 129057 rows exported
. . exporting table FILE_INFO 132832 rows exported
. . exporting table FILE_UPLOAD_FILEINFO 9 rows exported
. . exporting table FLEX_FIELD 0 rows exported
. . exporting table FOLDER 2782 rows exported
. . exporting table FOLDERING 19264 rows exported
. . exporting table FOLDER_STRUCTURE 0 rows exported
. . exporting table GAP_TABLE 0 rows exported
. . exporting table GEOGRAPHY_ROUTING 0 rows exported
. . exporting table GROUP_HEADER 0 rows exported
. . exporting table GROUP_LINE 0 rows exported
. . exporting table HISTORY 0 rows exported
. . exporting table HISTORY_FILE_UPLOAD 13818 rows exported
. . exporting table INBOX 0 rows exported
. . exporting table INBOX_USER 0 rows exported
. . exporting table INSTANCES 0 rows exported
. . exporting table ITEM 13558 rows exported
. . exporting table ITEM_HISTORY 648040 rows exported
. . exporting table ITEM_HISTORY2 169 rows exported
. . exporting table ITEM_SITES 0 rows exported
. . exporting table LANGTABLE 0 rows exported
. . exporting table LICENSE_EVENTS 0 rows exported
. . exporting table LISTENTRY 7836 rows exported
. . exporting table LISTNAME 674 rows exported
. . exporting table LIST_TABLE 0 rows exported
. . exporting table MANUFACTURER 0 rows exported
. . exporting table MANUFACTURERS 552 rows exported
. . exporting table MANU_BY 22714 rows exported
. . exporting table MANU_PARTS 7391 rows exported
. . exporting table MATERIAL_DECLARATION 0 rows exported
. . exporting table MATERIAL_DECLARATION_MAP 0 rows exported
. . exporting table METAFILES_QUEUE 0 rows exported
. . exporting table MODATT 64 rows exported
. . exporting table MSATT 167689 rows exported
. . exporting table MSP_ACTIVITY_FILE_MAP 0 rows exported
. . exporting table MSP_RESOURCE_MAP 0 rows exported
. . exporting table NEWS 0 rows exported
. . exporting table NODETABLE 28209 rows exported
. . exporting table NOTIFICATION 3069 rows exported
. . exporting table NOTIFICATION_FIELDS 26815 rows exported
. . exporting table NOTIFICATION_TEMPLATE 17 rows exported
. . exporting table NOTIFICATION_USER 3095 rows exported
. . exporting table NOTIFY_ATTRIBUTE 6735 rows exported
. . exporting table NOTIFY_FROM 1 rows exported
. . exporting table NOTIFY_HISTORY 0 rows exported
. . exporting table NOTIFY_HISTORY_ATT 0 rows exported
. . exporting table NOTIFY_TEMPLATE 474 rows exported
. . exporting table OBJECTACL 3970 rows exported
. . exporting table OBJECT_DETAIL 205 rows exported
. . exporting table OBJECT_LOCKS 0 rows exported
. . exporting table OBJECT_MAP 4903 rows exported
. . exporting table ONETIME_PX_TOKENS 0 rows exported
. . exporting table ORGANIZATION 1 rows exported
. . exporting table OUTBOUND 11287 rows exported
. . exporting table OUTBOUND_LOCK 1547 rows exported
. . exporting table OUTBOUND_OBJECTS 11287 rows exported
. . exporting table OUTBOUND_TRACK 0 rows exported
. . exporting table OUTBOUND_WHERESENT 20477 rows exported
. . exporting table OUTPUT_BOM 6676 rows exported
. . exporting table PAGE_THREE 169399 rows exported
. . exporting table PAGE_TWO 169717 rows exported
. . exporting table PART_FAMILY 2 rows exported
. . exporting table PART_FAMILY_MAP 0 rows exported
. . exporting table PDP 1 rows exported
. . exporting table PDQ_ITEM_DICTIONARY 0 rows exported
. . exporting table PERS_CRITERIA 0 rows exported
. . exporting table PERS_CRITERIA_NODE 0 rows exported
. . exporting table PGC_FIND_TLA 0 rows exported
. . exporting table PGC_ROLLUP_PART 0 rows exported
. . exporting table PGC_SMART_ROLLUP_TRIGGER 0 rows exported
. . exporting table PGC_SMART_ROLLUP_TRIGGER_CP 0 rows exported
. . exporting table PRICE 0 rows exported
. . exporting table PRICEENTRY 0 rows exported
. . exporting table PRICELINE 0 rows exported
. . exporting table PRICEPOINT 0 rows exported
. . exporting table PRICE_HISTORY 0 rows exported
. . exporting table PRODUCT_LINE 0 rows exported
. . exporting table PRODUCT_LINE_MAP 0 rows exported
. . exporting table PROGRAM 0 rows exported
. . exporting table PROGRAM_HISTORY 1 rows exported
. . exporting table PROGRAM_LOCKS 0 rows exported
. . exporting table PROGRAM_TEMPLATE 1 rows exported
. . exporting table PROJECT 0 rows exported
. . exporting table PROPERTYTABLE 274097 rows exported
. . exporting table PSR 9822 rows exported
. . exporting table PSR_ITEM 9424 rows exported
. . exporting table PS_REPORT_HEADER 0 rows exported
. . exporting table QCR 105 rows exported
. . exporting table QCR_AI 10 rows exported
. . exporting table QUERY 2043 rows exported
. . exporting table QUERY_REORG_LOG 0 rows exported
. . exporting table QUERY_TABLE 262 rows exported
. . exporting table QUOTE_HISTORY 0 rows exported
. . exporting table RECURRENCE 12 rows exported
. . exporting table REDLINE_ATTRIBUTE 14265 rows exported
. . exporting table REDLINE_MSATTRIBUTE 8552 rows exported
. . exporting table REFDESIG 33953 rows exported
. . exporting table REFERENCE_OBJECT 0 rows exported
. . exporting table REGION 12 rows exported
. . exporting table REGION_CONTENT 12 rows exported
. . exporting table REGULATION 0 rows exported
. . exporting table REGULATION_ADDORREPLACE_ACTION 0 rows exported
. . exporting table REGULATION_ADDORREPLACE_TASK 0 rows exported
. . exporting table REGULATION_PAGE2_MAPPING 0 rows exported
. . exporting table RELATIONSHIP 18209 rows exported
. . exporting table REMOTE_USER 0 rows exported
. . exporting table RENDITION_FILE_MAP 8 rows exported
. . exporting table REPORT 457 rows exported
. . exporting table REPORT_LAYOUT 375 rows exported
. . exporting table REPORT_TEMPLATE 0 rows exported
. . exporting table RESPONSE 0 rows exported
. . exporting table RESPONSE_HEADER 0 rows exported
. . exporting table RESPONSE_RFQ_MAPPING 0 rows exported
. . exporting table RESPONSE_RFQ_TERMS 0 rows exported
. . exporting table RESULT_OBJECT_COMPLIANCE 0 rows exported
. . exporting table RESULT_SUBSTANCE_COMPLIANCE 0 rows exported
. . exporting table REV 43158 rows exported
. . exporting table RFQ_COMMENT 0 rows exported
. . exporting table RFQ_COMMENT_RECIPIENT 0 rows exported
. . exporting table RFQ_HEADER 0 rows exported
. . exporting table RFQ_LINE 0 rows exported
. . exporting table ROLLUP_COMPOSITION 0 rows exported
. . exporting table ROLLUP_COMPOSITION_DECL
. . exporting table ROOT_OBJECT 0 rows exported
. . exporting table ROW_TABLE 24 rows exported
. . exporting table SCHEDULED_EVENT 24 rows exported
. . exporting table SCHEDULED_EVENT_TRACKING 43 rows exported
. . exporting table SCRIPT_MAINT 17 rows exported
. . exporting table SC_AML 0 rows exported
. . exporting table SC_BOM 0 rows exported
. . exporting table SC_COMMENT 0 rows exported
. . exporting table SC_COST_QTY_ROLLUP 0 rows exported
. . exporting table SC_DELTA_BOM 0 rows exported
. . exporting table SC_ITEM 0 rows exported
. . exporting table SC_PROJECT_SITES 0 rows exported
. . exporting table SC_SUPPLIERS 0 rows exported
. . exporting table SC_WORK_IN_PROGRESS 0 rows exported
. . exporting table SEARCH 0 rows exported
. . exporting table SECTION 5 rows exported
. . exporting table SELECT_LIST 12100 rows exported
. . exporting table SERVERHOST 1 rows exported
. . exporting table SERVER_LICENSE 292 rows exported
. . exporting table SIGNOFF 258012 rows exported
. . exporting table SIMPLE_QUERY_HISTORY 0 rows exported
. . exporting table SIMPLE_RELATION 3920 rows exported
. . exporting table SITE 0 rows exported
. . exporting table SITES 1 rows exported
. . exporting table SPECIFICATION_SUBSTANCES 0 rows exported
. . exporting table STATEACTIVITY_RELATIONS 0 rows exported
. . exporting table STYLE 5 rows exported
. . exporting table SUBSCRIBER_TRACK 0 rows exported
. . exporting table SUBSCRIPTIONS 396 rows exported
. . exporting table SUBSCRIPTIONS_BADREC_BACKUP 0 rows exported
. . exporting table SUBSTANCE 2 rows exported
. . exporting table SUBSTANCE_MAP 0 rows exported
. . exporting table SUPPLIER_LINE_CARD 0 rows exported
. . exporting table SUPPLIER_MDO_PX 15 rows exported
. . exporting table SYSTEM_MEMORY 0 rows exported
. . exporting table TABLEINFO 54648 rows exported
. . exporting table TABLE_FILTER 278 rows exported
. . exporting table TABLE_VIEW 652 rows exported
. . exporting table TAB_DISP_CONFIG 0 rows exported
. . exporting table TAB_DISP_CONFIG_INFO 0 rows exported
. . exporting table TA_ESC 0 rows exported
. . exporting table TEAM 0 rows exported
. . exporting table TEAM_BASELINE 0 rows exported
. . exporting table TEMPLATE 3 rows exported
. . exporting table THUMBNAIL_FILE_MAP 0 rows exported
. . exporting table THUMBNAIL_OBJECT_MAP 0 rows exported
. . exporting table TIMESHEET 0 rows exported
. . exporting table TMPUSER 1 rows exported
. . exporting table TMP_ROLLUP
. . exporting table TRANSFERAUTH 141 rows exported
. . exporting table TRANSFERFLAG 0 rows exported
. . exporting table T_ACT_APP 0 rows exported
. . exporting table T_ACT_CLA 0 rows exported
. . exporting table T_ACT_DAT 0 rows exported
. . exporting table T_ACT_HIS 0 rows exported
. . exporting table T_ACT_SNG 0 rows exported
. . exporting table T_ACT_STR 0 rows exported
. . exporting table T_BAS_USER 0 rows exported
. . exporting table T_CLA_DAT 0 rows exported
. . exporting table T_DEFAULT 4 rows exported
. . exporting table T_DEF_PERIODS 6 rows exported
. . exporting table T_DSB_DAT 0 rows exported
. . exporting table T_DSB_ELM 0 rows exported
. . exporting table T_DSB_STR 0 rows exported
. . exporting table T_EWO_DAT 0 rows exported
. . exporting table T_EWR_DAT 0 rows exported
. . exporting table T_FIELD 6 rows exported
. . exporting table T_GROUP 0 rows exported
. . exporting table T_GROUP_DAT 0 rows exported
. . exporting table T_GRP_ART 0 rows exported
. . exporting table T_GRP_CLA 0 rows exported
. . exporting table T_GRP_PRC 0 rows exported
. . exporting table T_GRP_USR 0 rows exported
. . exporting table T_MAIL_DAT 0 rows exported
. . exporting table T_MASK 1 rows exported
. . exporting table T_MAS_FLD 8 rows exported
. . exporting table T_MENU 2 rows exported
. . exporting table T_MEN_SEL 9 rows exported
. . exporting table T_NUM_BLK 9 rows exported
. . exporting table T_NUM_GEN 8 rows exported
. . exporting table T_ORG_DAT 0 rows exported
. . exporting table T_POS_DAT 0 rows exported
. . exporting table T_PRC_DAT 0 rows exported
. . exporting table T_PRC_HIS 0 rows exported
. . exporting table T_PRO_DAT 0 rows exported
. . exporting table T_PRS_DAT 0 rows exported
. . exporting table T_ROL_DAT 0 rows exported
. . exporting table T_ROL_STR 0 rows exported
. . exporting table T_ROL_TSK 0 rows exported
. . exporting table T_SELECTION 9 rows exported
. . exporting table T_TSK_DAT 0 rows exported
. . exporting table T_USER 0 rows exported
. . exporting table T_WFL_CON 47 rows exported
. . exporting table UPG88 0 rows exported
. . exporting table USAGE_HISTORY 0 rows exported
. . exporting table USER_APPROVAL_PASSWORD_HISTORY 36 rows exported
. . exporting table USER_ASSIGNMENT 2786 rows exported
. . exporting table USER_BOOKMARK 0 rows exported
. . exporting table USER_DEFAULT_TABLEVIEW 16 rows exported
. . exporting table USER_FUNCTION_MAP 16 rows exported
. . exporting table USER_GROUP 12 rows exported
. . exporting table USER_LICENSE 288 rows exported
. . exporting table USER_PASSWORD_HISTORY 312 rows exported
. . exporting table USER_POLICY 156 rows exported
. . exporting table USER_ROLE 0 rows exported
. . exporting table USER_SESSION 13 rows exported
. . exporting table USER_SITE 0 rows exported
. . exporting table USER_USAGE_HISTORY 2378593 rows exported
. . exporting table VAULT 2 rows exported
. . exporting table VERSION 129067 rows exported
. . exporting table VERSION_FILE_MAP 130072 rows exported
. . exporting table VISIT_HISTORY 0 rows exported
. . exporting table WORKFLOW_PROCESS 170245 rows exported
. . exporting table WORKFLOW_RULES 0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

 

Thanks,

Laura

Agile User Answered on December 9, 2019.

I’m going to guess that there are PXs here that are generating a lot of history records. Less than 10,000 changes and 136+ million history records? That’s 14,273 history rows per change. Outrageous! By contrast, there are almost 48 history entries per item, which is still high, but not like the changes. This environment needs some help clearing out the bogus change history records. Check if the PXs/Events can be re-architected and check into reducing the event recursion. In the meantime, use agile9expdp instead as that runs much faster for large data sets.

on December 9, 2019.

Hi Keith and Kevin,

Thank you for your responses.

Yes, they have some PXs running – from Events there are 2;  that every time a part is created, a mfr part with same PN is created and another one that every time a part is ‘saved as’ a mfr part is created too.

In the Assign New MFP Part ID script px there is a note:

* Upon Item creation and save-as, set Page2.text11 field to a number fetched from the SECOND
* auto-number for CTOs. Auto-number is stored in CTO subclass because it has to be associated
* with an object type. Chose to use CTO subclass because it is never used in real life, so nobody
* will notice an annoying second auto-number sequence lurking there.
* — This script uses SDK calls. — Post Event Trigger

So that seems to be where the CTO class comes in.

There is also a script px running that Automatically sets the revision field when adding an item to the affected item tab of a change order.

There are also events running in ACS – they are sending 4 change types over to QAD at Released step of the change orders.

I’m running the datapump now. Hopefully I’ll be able to import that and get their test db back up.

After we get a good backup do you think we should run some scripts to clear out some history to get the file sizes down?

Thank you,

Laura

 

 

on December 10, 2019.

Yes. The OUTBOUND table has 11287 objects at the moment, with each one having exactly 1 record in OUTBOUND_OBJECT. I am wondering if you have a lot of records in CHANGE_HISTORY that belong to OUTBOUND objects that are no longer there??

Run the query :

select count(*) from change_history ch where class_id = 11761 and not exists (select null from outbound where id = ch.change_id);

If you get a large count from the above query, then you have a LOT of records in CHANGE_HISTORY than can easily be removed, as the object that they should link to does not exist. If not, then something else is going on. I would also look at the values in EVENT_TYPE for the ATO/CTO records in CHANGE_HISTORY, see what is going on when they are created.

And something must be advancing the ATO/CTO through it’s workflow (how many steps does it have?). The SIGNOFF table has 258012 records, so if the ATO/CTO records are also piling up in there, that would be somewhere else we can remove some records. And the same thing  for WORKFLOW_PROCESS, at 170245 records. Neither is huge, but……..

on December 16, 2019.
Add Comment

Hi,

Sorry, here is the log file.  It just ends, doesn’t give any errors.

******
Connected to: Oracle Database 12c Release 12.1.0.1.0 – 64bit Production
Export done in UTF8 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified users …
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user AGILE
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user AGILE
About to export AGILE’s objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export AGILE’s tables via Conventional Path …
. . exporting table ACCOUNT_LOCKOUT_NOTIFY 36 rows exported
. . exporting table ACTION_ITEM 0 rows exported
. . exporting table ACTIVITY 0 rows exported
. . exporting table ACTIVITY_BASELINE 0 rows exported
. . exporting table ACTIVITY_DEPENDENCY 0 rows exported
. . exporting table ADMINCLOBATT 0 rows exported
. . exporting table ADMINCRITERIA 829 rows exported
. . exporting table ADMINMSATT 13078 rows exported
. . exporting table ADMIN_HISTORY 8384 rows exported
. . exporting table ADMIN_HISTORY_DETAILS 8384 rows exported
. . exporting table AGGREGATED_SUBSTANCE 0 rows exported
. . exporting table AGILESEQUENCES 133 rows exported
. . exporting table AGILEUSER 322 rows exported
. . exporting table AGILE_FLEX 221963 rows exported
. . exporting table ALIAS 0 rows exported
. . exporting table APPLIEDTO 152970 rows exported
. . exporting table APPROVAL_ITEMS 0 rows exported
. . exporting table APP_PROPERTY 0 rows exported
. . exporting table ASL_MAP 0 rows exported
. . exporting table ATTACHMENT 116446 rows exported
. . exporting table ATTACHMENTS 0 rows exported
. . exporting table ATTACHMENT_MAP 66333 rows exported
. . exporting table ATTACH_REDLINE 0 rows exported
. . exporting table ATTR_PREF 0 rows exported
. . exporting table AUTHORIZED_USER 0 rows exported
. . exporting table AUTH_FAILED 2226 rows exported
. . exporting table AUTONUMBER 0 rows exported
. . exporting table AUTOSELECT_TEMP 0 rows exported
. . exporting table AVL 0 rows exported
. . exporting table A_CLUSTER_CACHE_SYNCH_LOG 6670 rows exported
. . exporting table A_CONTENT_FOLDER 20 rows exported
. . exporting table A_CONTENT_FOLDER_MAP 0 rows exported
. . exporting table A_DW_TXN_LOG 7839448 rows exported
. . exporting table A_PROJ_RSRC 0 rows exported
. . exporting table A_SUBSTANCE_ALIAS 0 rows exported
. . exporting table A_TMP_BOM_INT_PROC
. . exporting table A_UI_BLOB_DATA 0 rows exported
. . exporting table A_UI_PAGE 1 rows exported
. . exporting table A_UI_WIDGET 7 rows exported
. . exporting table A_UI_WIDGET_CONFIG 0 rows exported
. . exporting table A_UI_WIDGET_PAGE 7 rows exported
. . exporting table BASELINE_REV 0 rows exported

*********

The  dmp file that got created from that daily backup last night that didn’t finish ended up being 1,465,120kb.

I’m trying another export now after kicking all users off system to see if that works.

So far its up to CHANGE_HISTORY… that table is always big, hopefully it’ll get through.

If it doesn’t, are there any other places to look for errors.

No one has been doing db maintenance so maybe I need to run averify.

IT confirmed there aren’t any disc errors or system issues on this virtual machine.

Thanks,

Laura

 

Agile User Answered on December 6, 2019.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.