Purge bases on Composite Name
There are some scenarios where executing purge scripts for a single composite make sense then executing it on whole of the soainfra schema.
when we execute Purge script based on composite name the instances of the specific composite gets deleted. This can be again be more specific by providing composite versions and partition name.
Below is the script for both loop purge and parallel purge. Basic setup for executing purge based on composite name is same as loop purge or parallel purge(please refer blogs on this blog same names)
parameters used :
composite_name => 'HelowSoaPurge',
composite_revision => '2.0',
soa_partition_name => 'SoaPurge');
=====================================================================
Loop purge
DECLARE
MIN_CREATION_DATE timestamp := TIMESTAMP'2011-09-19 02:00:00';
MAX_CREATION_DATE timestamp := TIMESTAMP'2011-09-20 00:00:00';
batch_size integer;
max_runtime integer;
retention_period timestamp;
BEGIN
max_runtime := 60;
retention_period := to_timestamp('2011-09-20','YYYY-MM-DD');
batch_size := 10000;
soa.delete_instances(
min_creation_date => MIN_CREATION_DATE,
max_creation_date => MAX_CREATION_DATE,
batch_size => batch_size,
max_runtime => max_runtime,
retention_period => retention_period,
purge_partitioned_component => true,
composite_name => 'HelowSoaPurge',
composite_revision => '2.0',
soa_partition_name => 'SoaPurge');
END;
/
=====================================================================
Parallel Purge
DECLARE
MIN_CREATION_DATE timestamp := TIMESTAMP'2011-09-19 01:00:00';
MAX_CREATION_DATE timestamp := TIMESTAMP'2011-09-20 00:00:00';
batch_size integer;
max_runtime integer;
retention_period timestamp;
BEGIN
max_runtime := 60;
retention_period := to_timestamp('2011-09-21','YYYY-MM-DD');
batch_size := 10000;
soa.delete_instances_in_parallel(
min_creation_date => MIN_CREATION_DATE,
max_creation_date => MAX_CREATION_DATE,
batch_size => 10000,
max_runtime => 60,
retention_period => retention_period,
DOP => 3,
max_count => 1000000,
purge_partitioned_component => true,
composite_name => 'HelowSoaPurge',
composite_revision => '1.0',
soa_partition_name => 'SoaPurge');
END;
/
=================================================================
Disclaimer: The views expressed on this blog are solely our own and do not necessarily reflect the views of Oracle.
=================================================================
Thursday, January 26, 2012
Tuesday, January 17, 2012
ORA-06512: at "SYS.UTL_FILE", line 536
ssue 4 : obtained when soa_exec_verify.sql is executed
Enter value for 1: 1
old 19: if &1 = 1 then
new 19: if 1 = 1 then
DECLARE
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "DB2813_SOAINFRA.VERIFY_SOA", line 252
ORA-06512: at line 20
Solution:
1. Make sure you have given permissions as specified in Partitioning Concept blog
2. Make sure the directory is created for dumping sql logs
================================================================================
Disclaimer: The views expressed on this blog are solely our own and do not necessarily reflect the views of Oracle.
================================================================================
Enter value for 1: 1
old 19: if &1 = 1 then
new 19: if 1 = 1 then
DECLARE
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "DB2813_SOAINFRA.VERIFY_SOA", line 252
ORA-06512: at line 20
Solution:
1. Make sure you have given permissions as specified in Partitioning Concept blog
2. Make sure the directory is created for dumping sql logs
================================================================================
Disclaimer: The views expressed on this blog are solely our own and do not necessarily reflect the views of Oracle.
================================================================================
Partition cannot be dropped for XML_DOCUMENTS
Issue 3: (Partition drop)
Partition cannot be dropped if there are some b2b records associated with the table.
Solution:
log into soainfra user and run "exec b2b_disable_constraints" command.
then rerun verify script that was generated, you will be able to see all b2b related tables getting altered
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue 5 : cannot drop xml_document table partition
SQL> @/tmp/verify/SOA_P03_2012_RESULT_2
SQL> REM Auto generated by the SOA verify script
SQL> REM Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
SQL> REM DESCRIPTION
SQL> REM This script is generated by the SOA verify sctipts. It is used to drop partitions from the SOA Table.
SQL> REM This script should only be run by DBA who is familiar with the SOA Schema.
SQL> REM
SQL> -- COMPONENT: FABRIC
SQL> -- COMPONENT: BPEL
SQL> ALTER TABLE XML_DOCUMENT drop partition P03_2012 UPDATE GLOBAL INDEXES;
ALTER TABLE XML_DOCUMENT drop partition P03_2012 UPDATE GLOBAL INDEXES
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
Solution:
Reason : XML_DOCUMENT is referred by B2B related tables also and hence the partition cannot be dropped so easily.
solution : Run b2b disable constraints as below then run drop partition command
login as soainfra user and run
$SQL>exec B2B_DISABLE_CONSTRAINTS
to enable B2B constraint after dropping run below command
$SQL> exec B2B_ENABLE_CONSTRAINTS
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
================================================================================
Disclaimer: The views expressed on this blog are solely our own and do not necessarily reflect the views of Oracle.
================================================================================
Partition cannot be dropped if there are some b2b records associated with the table.
Solution:
log into soainfra user and run "exec b2b_disable_constraints" command.
then rerun verify script that was generated, you will be able to see all b2b related tables getting altered
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue 5 : cannot drop xml_document table partition
SQL> @/tmp/verify/SOA_P03_2012_RESULT_2
SQL> REM Auto generated by the SOA verify script
SQL> REM Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
SQL> REM DESCRIPTION
SQL> REM This script is generated by the SOA verify sctipts. It is used to drop partitions from the SOA Table.
SQL> REM This script should only be run by DBA who is familiar with the SOA Schema.
SQL> REM
SQL> -- COMPONENT: FABRIC
SQL> -- COMPONENT: BPEL
SQL> ALTER TABLE XML_DOCUMENT drop partition P03_2012 UPDATE GLOBAL INDEXES;
ALTER TABLE XML_DOCUMENT drop partition P03_2012 UPDATE GLOBAL INDEXES
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
Solution:
Reason : XML_DOCUMENT is referred by B2B related tables also and hence the partition cannot be dropped so easily.
solution : Run b2b disable constraints as below then run drop partition command
login as soainfra user and run
$SQL>exec B2B_DISABLE_CONSTRAINTS
to enable B2B constraint after dropping run below command
$SQL> exec B2B_ENABLE_CONSTRAINTS
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
================================================================================
Disclaimer: The views expressed on this blog are solely our own and do not necessarily reflect the views of Oracle.
================================================================================
Error Message = ORA-20001: Jobs still running or not shut down properly
Issue2 :(specific to Parallel Purge)
02-JAN-2012 03:57:10 : jobs running = 2
02-JAN-2012 03:57:10
: ERROR(delete_instances_in_parallel. Error Code = -20001,
Error Message = ORA-20001: Jobs still running or not shut down properly
DECLARE
*
ERROR at line 1:
ORA-20001: Jobs still running or not shut down properly
ORA-06512: at "DB7607_SOAINFRA.SOA", line 661
ORA-06512: at line 11
Solution:
Delete all records from job_flow_control table;
Delete from job_flow_control
=================================================================
Disclaimer: The views expressed on this blog are solely our own and do not necessarily reflect the views of Oracle.
=================================================================
02-JAN-2012 03:57:10 : jobs running = 2
02-JAN-2012 03:57:10
: ERROR(delete_instances_in_parallel. Error Code = -20001,
Error Message = ORA-20001: Jobs still running or not shut down properly
DECLARE
*
ERROR at line 1:
ORA-20001: Jobs still running or not shut down properly
ORA-06512: at "DB7607_SOAINFRA.SOA", line 661
ORA-06512: at line 11
Solution:
Delete all records from job_flow_control table;
Delete from job_flow_control
=================================================================
Disclaimer: The views expressed on this blog are solely our own and do not necessarily reflect the views of Oracle.
=================================================================
error "ORA-01861 literal does not match format string"
Hacks and Workarounds
Debugging issues seen during purge script execution
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue1 :(Generic)
error "ORA-01861 literal does not match format string"
Solution:
date format giving in your purge script may be wrong please check for the correct format as listed below.
min_creation_date := to_timestamp('2010-01-01','YYYY-MM-DD');
max_creation_date := to_timestamp('2010-01-31','YYYY-MM-DD');
retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');
or like this
MIN_CREATION_DATE timestamp := TIMESTAMP'2011-09-19 02:00:00';
MAX_CREATION_DATE timestamp := TIMESTAMP'2011-09-20 00:00:00';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
=================================================================
Disclaimer: The views expressed on this blog are solely our own and do not necessarily reflect the views of Oracle.
=================================================================
Debugging issues seen during purge script execution
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue1 :(Generic)
error "ORA-01861 literal does not match format string"
Solution:
date format giving in your purge script may be wrong please check for the correct format as listed below.
min_creation_date := to_timestamp('2010-01-01','YYYY-MM-DD');
max_creation_date := to_timestamp('2010-01-31','YYYY-MM-DD');
retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');
or like this
MIN_CREATION_DATE timestamp := TIMESTAMP'2011-09-19 02:00:00';
MAX_CREATION_DATE timestamp := TIMESTAMP'2011-09-20 00:00:00';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
=================================================================
Disclaimer: The views expressed on this blog are solely our own and do not necessarily reflect the views of Oracle.
=================================================================
Tuesday, January 3, 2012
Hacks and Workarounds
Debugging issues seen during purge script execution
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue1 :(Generic)
error "ORA-01861 literal does not match format string"
Solution:
date format giving in your purge script may be wrong please check for the correct format as listed below.
min_creation_date := to_timestamp('2010-01-01','YYYY-MM-DD');
max_creation_date := to_timestamp('2010-01-31','YYYY-MM-DD');
retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');
or like this
MIN_CREATION_DATE timestamp := TIMESTAMP'2011-09-19 02:00:00';
MAX_CREATION_DATE timestamp := TIMESTAMP'2011-09-20 00:00:00';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue2 :(specific to Parallel Purge)
02-JAN-2012 03:57:10 : jobs running = 2
02-JAN-2012 03:57:10
: ERROR(delete_instances_in_parallel. Error Code = -20001,
Error Message = ORA-20001: Jobs still running or not shut down properly
DECLARE
*
ERROR at line 1:
ORA-20001: Jobs still running or not shut down properly
ORA-06512: at "DB7607_SOAINFRA.SOA", line 661
ORA-06512: at line 11
Solution:
Delete all records from job_flow_control table;
Delete from job_flow_control
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue 3: (Partition drop)
Partition cannot be dropped if there are some b2b records associated with the table.
Solution:
log into soainfra user and run "exec b2b_disable_constraints" command.
then rerun verify script that was generated, you will be able to see all b2b related tables getting altered
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue 4 : obtained when soa_exec_verify.sql is executed
Enter value for 1: 1
old 19: if &1 = 1 then
new 19: if 1 = 1 then
DECLARE
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "DB2813_SOAINFRA.VERIFY_SOA", line 252
ORA-06512: at line 20
Solution:
1. Make sure you have given permissions as specified in Partitioning Concept blog
2. Make sure the directory is created for dumping sql logs
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue 5 : cannot drop xml_document table partition
SQL> @/tmp/verify/SOA_P03_2012_RESULT_2
SQL> REM Auto generated by the SOA verify script
SQL> REM Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
SQL> REM DESCRIPTION
SQL> REM This script is generated by the SOA verify sctipts. It is used to drop partitions from the SOA Table.
SQL> REM This script should only be run by DBA who is familiar with the SOA Schema.
SQL> REM
SQL> -- COMPONENT: FABRIC
SQL> -- COMPONENT: BPEL
SQL> ALTER TABLE XML_DOCUMENT drop partition P03_2012 UPDATE GLOBAL INDEXES;
ALTER TABLE XML_DOCUMENT drop partition P03_2012 UPDATE GLOBAL INDEXES
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
Solution:
Reason : XML_DOCUMENT is referred by B2B related tables also and hence the partition cannot be dropped so easily.
solution : Run b2b disable constraints as below then run drop partition command
login as soainfra user and run
$SQL>exec B2B_DISABLE_CONSTRAINTS
to enable B2B constraint after dropping run below command
$SQL> exec B2B_ENABLE_CONSTRAINTS
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
================================================================================
Disclaimer: The views expressed on this blog are solely our own and do not necessarily reflect the views of Oracle.
================================================================================
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue1 :(Generic)
error "ORA-01861 literal does not match format string"
Solution:
date format giving in your purge script may be wrong please check for the correct format as listed below.
min_creation_date := to_timestamp('2010-01-01','YYYY-MM-DD');
max_creation_date := to_timestamp('2010-01-31','YYYY-MM-DD');
retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');
or like this
MIN_CREATION_DATE timestamp := TIMESTAMP'2011-09-19 02:00:00';
MAX_CREATION_DATE timestamp := TIMESTAMP'2011-09-20 00:00:00';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue2 :(specific to Parallel Purge)
02-JAN-2012 03:57:10 : jobs running = 2
02-JAN-2012 03:57:10
: ERROR(delete_instances_in_parallel. Error Code = -20001,
Error Message = ORA-20001: Jobs still running or not shut down properly
DECLARE
*
ERROR at line 1:
ORA-20001: Jobs still running or not shut down properly
ORA-06512: at "DB7607_SOAINFRA.SOA", line 661
ORA-06512: at line 11
Solution:
Delete all records from job_flow_control table;
Delete from job_flow_control
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue 3: (Partition drop)
Partition cannot be dropped if there are some b2b records associated with the table.
Solution:
log into soainfra user and run "exec b2b_disable_constraints" command.
then rerun verify script that was generated, you will be able to see all b2b related tables getting altered
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue 4 : obtained when soa_exec_verify.sql is executed
Enter value for 1: 1
old 19: if &1 = 1 then
new 19: if 1 = 1 then
DECLARE
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "DB2813_SOAINFRA.VERIFY_SOA", line 252
ORA-06512: at line 20
Solution:
1. Make sure you have given permissions as specified in Partitioning Concept blog
2. Make sure the directory is created for dumping sql logs
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue 5 : cannot drop xml_document table partition
SQL> @/tmp/verify/SOA_P03_2012_RESULT_2
SQL> REM Auto generated by the SOA verify script
SQL> REM Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
SQL> REM DESCRIPTION
SQL> REM This script is generated by the SOA verify sctipts. It is used to drop partitions from the SOA Table.
SQL> REM This script should only be run by DBA who is familiar with the SOA Schema.
SQL> REM
SQL> -- COMPONENT: FABRIC
SQL> -- COMPONENT: BPEL
SQL> ALTER TABLE XML_DOCUMENT drop partition P03_2012 UPDATE GLOBAL INDEXES;
ALTER TABLE XML_DOCUMENT drop partition P03_2012 UPDATE GLOBAL INDEXES
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
Solution:
Reason : XML_DOCUMENT is referred by B2B related tables also and hence the partition cannot be dropped so easily.
solution : Run b2b disable constraints as below then run drop partition command
login as soainfra user and run
$SQL>exec B2B_DISABLE_CONSTRAINTS
to enable B2B constraint after dropping run below command
$SQL> exec B2B_ENABLE_CONSTRAINTS
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
================================================================================
Disclaimer: The views expressed on this blog are solely our own and do not necessarily reflect the views of Oracle.
================================================================================
Subscribe to:
Posts (Atom)