-
Title
Post error "ORA-00959 tablespace 'name' does not exist" -
Description
The event log shows the following entry appearing over and over:
Line# 1738481: SharePlex Instance:sp_cop2102: 01/16/08 00:19 Error:
Skipping failed DDL Replication [sp_opst/4187]
Line# 1738482: SharePlex Instance:sp_cop2102: 01/16/08 00:20 Error: OCI
Error: ORA-00959: tablespace '<name>' does not exist.
[sp_opst/4187] -
Cause
The specified tablespace may not be existing on the target database, or the AUTO ADD feature has been enabled on the source Shareplex by configuring the parameter SP_OCT_AUTOADD_ENABLE to 1.
-
Resolution
The error can be due to specified tablespace not existing on the target database. This can happen due to several reasons, some of which (along with the resolution) are:
1. The logical structure of the target database changed after the replication was first initiated, whereby the tablespace in question does not exist at this time. To resolve the error, one can stop Post and then make changes to the logical structure of the target database so that the tablespace is created.
2. The parameter SP_OCT_REPLICATE_ALL_DDL may have been inadvertently set at 1 on source Shareplex. This results in any DDL executed on source table (regardless of it being in replication) getting replicated to the target database and failing with the above Oracle error. The resolution may be to set the parameter SP_OPO_STOP_ON_DDL_ERR to 0 on the target sp_ctrl and then restart Post. This will enable the Post to continue even if it encounters a DDL error. Once the error has been bypassed, one can set the parameter back to 1 so that the Post does stop when encountering a DDL error. The parameter change will only occur once the Post is bounced.
3. The auto-add feature of Shareplex is enabled as the parameter SP_OCT_AUTOADD_ENABLE is set to 1. This results in some DDLs still getting replicated despite all forms of DDL replication are disabled.
On target ignore the DDL by configuring the parameter SP_OPO_STOP_ON_DDL_ERR to 0 for the time being and restart Post. From this point on all DDL errors will be ignored by Post. Once the Post is past the problematic DDL, the parameter can be reset to the default of 0 by the sp_ctrl command “reset param <parameter name>”. Keeping it enabled indefinitely is not a good idea. See SOL21396 for details. In brief:
A. Configure the parameter and start Post:
sp_ctrl>set param SP_OPO_STOP_ON_DDL_ERR 0
sp_ctrl>start post
B. To disable it and set it to the default:
sp_ctrl>reset param SP_OPO_STOP_ON_DDL_ERR
To address the root cause so that the issue does not recur in future, disable the auto-add feature by configuring the following Capture parameter on source:
sp_ctrl>set paramSP_OCT_AUTOADD_ENABLE 0
-
Additional Information
The following provides explanation/elaboration of how DDL replication occurs when the parameter SP_OCT_AUTOADD_ENABLEis set to 1 or 0:
The parameter SP_OCT_AUTOADD_ENABLE also controls Create/Drop index, irrespective of the way DDL replication is configured:
With SP_OCT_AUTOADD_ENABLE set to 1, SP_OCT_REPLICATE_ALL_DDL to 0 and SP_OCT_REPLICATE_DDL to 3, we replicate DDLs.
With SP_OCT_AUTOADD_ENABLE set to 0, SP_OCT_REPLICATE_ALL_DDL to 0 and SP_OCT_REPLICATE_DDL to 3, we DO NOT replicate DDLs.
With SP_OCT_AUTOADD_ENABLE set to 0, SP_OCT_REPLICATE_ALL_DDL to 1, we DO NOT replicate DDLs.
Details of the Oracle error:
ORA-00959: tablespace does not exist
Cause: A statement specified the name of a tablespace that does not exist.
Action: Enter the name of an existing tablespace. For a list of tablespace names, query the data dictionary.