ORA-01647 during TTS IMPDP from 11.2.0.4 64 bit Solaris SPARC to 12.1.0.2 64 bit RHEL

[Sundar Mahadevan]

Hi All,
Greetings. While performing a transportable tablespace of application
tablespaces from 11.2.0.4 Enterprise Edition 64 bit Solaris SPARC 10
1/13 to 12.1.0.2 Enterprise Edition 64 bit RHEL 7.3, I hit the following
error.

ORA-39083: Object type TABLE:"ARGUS_APP"."CASE_PRODUCT" failed to create
with error:
ORA-01647: tablespace 'ARGUS_DATA_05' is read-only, cannot allocate space
in it
Failing sql is:
CREATE TABLE "ARGUS_APP"."CASE_PRODUCT" ("CASE_ID" NUMBER NOT NULL ENABLE,
"SEQ_NUM" NUMBER NOT NULL ENABLE, "PRODUCT_ID" NUMBER, "DRUG_TYPE" NUMBER,
"PAT_EXPOSURE" NUMBER DEFAULT 0, "MANUFACTURER_ID" NUMBER,
"PROTOCOL_FOLLOWED" NUMBER, "SYS_C00008_11082108:57:04$" NUMBER,
"FIRST_SUS_PROD" NUMBER, "SELECTED_VIEW" NUMBER, "SORT_ID
ORA-39083: Object type TABLE:"ARGUS_APP"."CASE_ASSESS" failed to create
with error:
ORA-01647: tablespace 'ARGUS_DATA_01' is read-only, cannot allocate space
in it

I get this error for 5 tables. 3 tables out of 5 that returned ORA-01647
during impdp were non empty tables. No partitions on any of the tables. 2
tables out of the 5 have clobs. Regardless of deferred_segment_creation set
to TRUE or FALSE on the source database, I get the same error. I even
manually allocated extent on the 5 failing tables as suggested in a blog
post with no luck. Anyone faced this issue? Ticket has been created with
support. Not much progress yet. Thanks for your time and assistance.
Appreciate it.

[Mladen Gogala]

Did you do DBMS_TTS.TRANSPORT_SET_CHECK, with the constraints included,
on the source side?

Post by Sundar Mahadevan
Hi All,
Greetings. While performing a transportable tablespace of application
tablespaces from 11.2.0.4 Enterprise Edition 64 bit Solaris SPARC 10
1/13 to 12.1.0.2 Enterprise Edition 64 bit RHEL 7.3, I hit the
following error.
ORA-39083: Object type TABLE:"ARGUS_APP"."CASE_PRODUCT" failed to
ORA-01647: tablespace 'ARGUS_DATA_05' is read-only, cannot allocate
space in it
CREATE TABLE "ARGUS_APP"."CASE_PRODUCT" ("CASE_ID" NUMBER NOT NULL
ENABLE, "SEQ_NUM" NUMBER NOT NULL ENABLE, "PRODUCT_ID" NUMBER,
"DRUG_TYPE" NUMBER, "PAT_EXPOSURE" NUMBER DEFAULT 0, "MANUFACTURER_ID"
NUMBER, "PROTOCOL_FOLLOWED" NUMBER, "SYS_C00008_11082108:57:04$"
NUMBER, "FIRST_SUS_PROD" NUMBER, "SELECTED_VIEW" NUMBER, "SORT_ID
ORA-39083: Object type TABLE:"ARGUS_APP"."CASE_ASSESS" failed to
ORA-01647: tablespace 'ARGUS_DATA_01' is read-only, cannot allocate
space in it
I get this error for 5 tables. 3 tables out of 5 that returned
ORA-01647 during impdp were non empty tables. No partitions on any of
the tables. 2 tables out of the 5 have clobs. Regardless
of deferred_segment_creation set to TRUE or FALSE on the source
database, I get the same error. I even manually allocated extent on
the 5 failing tables as suggested in a blog post with no luck. Anyone
faced this issue? Ticket has been created with support. Not much
progress yet. Thanks for your time and assistance. Appreciate it.

[Sundar Mahadevan]

Thanks Mladen for reverting back. Yes, I did
run DBMS_TTS.TRANSPORT_SET_CHECK, with the constraints included, on the
source side and it returned no issues.

${ORACLE_HOME}/bin/sqlplus -s /nolog << eom

set head off feedback on pages 0 lines 400 echo on
connect / as sysdba
execute

sys.dbms_tts.transport_set_check('ARGUS_AEXP_DATA_01,ARGUS_AEXP_INDEX_01,DLP_INDEX_06,DLP_INDEX_04,ARGUS_DATA_01,
-
DLP_DATA_04,ARGUS_AL_INDEX_01,XDB,ARGUS_INDEX_03,ARGUS_INDEX_06,ESM_INDEX_01,ARGUS_DATA_02,ARGUS_DATA_04,DLP_INDEX_05,
-
ARGUS_INDEX_01,ARGUS_DATA_03,DLP_DATA_01,ARGUS_AL_DATA_01,ARGUS_INDEX_02,DLP_DATA_02,DLP_DATA_05,ARGUS_DATA_05,ARGUS_INDEX_04,
-
DLP_INDEX_03,DLP_DATA_03,DLP_INDEX_02,ESM_DATA_01,DLP_LOB_01,DLP_INDEX_01,USERS,ARGUS_INDEX_05',
true, true);

select * from sys.transport_set_violations;
eom

PL/SQL procedure successfully completed.


no rows selected

Did you do DBMS_TTS.TRANSPORT_SET_CHECK, with the constraints included,
on the source side?

Post by Sundar Mahadevan
Hi All,
Greetings. While performing a transportable tablespace of application
tablespaces from 11.2.0.4 Enterprise Edition 64 bit Solaris SPARC 10
1/13 to 12.1.0.2 Enterprise Edition 64 bit RHEL 7.3, I hit the
following error.
ORA-39083: Object type TABLE:"ARGUS_APP"."CASE_PRODUCT" failed to
ORA-01647: tablespace 'ARGUS_DATA_05' is read-only, cannot allocate
space in it
CREATE TABLE "ARGUS_APP"."CASE_PRODUCT" ("CASE_ID" NUMBER NOT NULL
ENABLE, "SEQ_NUM" NUMBER NOT NULL ENABLE, "PRODUCT_ID" NUMBER,
"DRUG_TYPE" NUMBER, "PAT_EXPOSURE" NUMBER DEFAULT 0, "MANUFACTURER_ID"
NUMBER, "PROTOCOL_FOLLOWED" NUMBER, "SYS_C00008_11082108:57:04$"
NUMBER, "FIRST_SUS_PROD" NUMBER, "SELECTED_VIEW" NUMBER, "SORT_ID
ORA-39083: Object type TABLE:"ARGUS_APP"."CASE_ASSESS" failed to
ORA-01647: tablespace 'ARGUS_DATA_01' is read-only, cannot allocate
space in it
I get this error for 5 tables. 3 tables out of 5 that returned
ORA-01647 during impdp were non empty tables. No partitions on any of
the tables. 2 tables out of the 5 have clobs. Regardless
of deferred_segment_creation set to TRUE or FALSE on the source
database, I get the same error. I even manually allocated extent on
the 5 failing tables as suggested in a blog post with no luck. Anyone
faced this issue? Ticket has been created with support. Not much
progress yet. Thanks for your time and assistance. Appreciate it.

--
Mladen Gogala Database Consultant Tel: [phone]
--
http://www.freelists.org/webpage/oracle-l

[Hameed, Amir]

What is the default tablespace of SYS and SYSTEM?

From: oracle-l-***@freelists.org <oracle-l-***@freelists.org> On Behalf Of Sundar Mahadevan
Sent: Saturday, November 10, 2018 6:45 PM
To: ***@gmail.com
Cc: ORACLE-L <oracle-***@freelists.org>
Subject: Re: ORA-01647 during TTS IMPDP from 11.2.0.4 64 bit Solaris SPARC to 12.1.0.2 64 bit RHEL

Thanks Mladen for reverting back. Yes, I did run DBMS_TTS.TRANSPORT_SET_CHECK, with the constraints included, on the source side and it returned no issues.

${ORACLE_HOME}/bin/sqlplus -s /nolog << eom

set head off feedback on pages 0 lines 400 echo on
connect / as sysdba
execute sys.dbms_tts.transport_set_check('ARGUS_AEXP_DATA_01,ARGUS_AEXP_INDEX_01,DLP_INDEX_06,DLP_INDEX_04,ARGUS_DATA_01, -
DLP_DATA_04,ARGUS_AL_INDEX_01,XDB,ARGUS_INDEX_03,ARGUS_INDEX_06,ESM_INDEX_01,ARGUS_DATA_02,ARGUS_DATA_04,DLP_INDEX_05, -
ARGUS_INDEX_01,ARGUS_DATA_03,DLP_DATA_01,ARGUS_AL_DATA_01,ARGUS_INDEX_02,DLP_DATA_02,DLP_DATA_05,ARGUS_DATA_05,ARGUS_INDEX_04, -
DLP_INDEX_03,DLP_DATA_03,DLP_INDEX_02,ESM_DATA_01,DLP_LOB_01,DLP_INDEX_01,USERS,ARGUS_INDEX_05', true, true);
select * from sys.transport_set_violations;
eom

PL/SQL procedure successfully completed.


no rows selected


On Sat, Nov 10, 2018 at 1:26 PM Mladen Gogala <***@gmail.com<mailto:***@gmail.com>> wrote:
Did you do DBMS_TTS.TRANSPORT_SET_CHECK, with the constraints included,
on the source side?

Hi All,
Greetings. While performing a transportable tablespace of application
tablespaces from 11.2.0.4 Enterprise Edition 64 bit Solaris SPARC 10
1/13 to 12.1.0.2 Enterprise Edition 64 bit RHEL 7.3, I hit the
following error.
ORA-39083: Object type TABLE:"ARGUS_APP"."CASE_PRODUCT" failed to
ORA-01647: tablespace 'ARGUS_DATA_05' is read-only, cannot allocate
space in it
CREATE TABLE "ARGUS_APP"."CASE_PRODUCT" ("CASE_ID" NUMBER NOT NULL
ENABLE, "SEQ_NUM" NUMBER NOT NULL ENABLE, "PRODUCT_ID" NUMBER,
"DRUG_TYPE" NUMBER, "PAT_EXPOSURE" NUMBER DEFAULT 0, "MANUFACTURER_ID"
NUMBER, "PROTOCOL_FOLLOWED" NUMBER, "SYS_C00008_11082108:57:04$"
NUMBER, "FIRST_SUS_PROD" NUMBER, "SELECTED_VIEW" NUMBER, "SORT_ID
ORA-39083: Object type TABLE:"ARGUS_APP"."CASE_ASSESS" failed to
ORA-01647: tablespace 'ARGUS_DATA_01' is read-only, cannot allocate
space in it
I get this error for 5 tables. 3 tables out of 5 that returned
ORA-01647 during impdp were non empty tables. No partitions on any of
the tables. 2 tables out of the 5 have clobs. Regardless
of deferred_segment_creation set to TRUE or FALSE on the source
database, I get the same error. I even manually allocated extent on
the 5 failing tables as suggested in a blog post with no luck. Anyone
faced this issue? Ticket has been created with support. Not much
progress yet. Thanks for your time and assistance. Appreciate it.

[Sundar Mahadevan]

Hi Amir,
Same output on source and target. default_tablespace is set to system for
both sys and system.

SQL>select username, default_tablespace from dba_users where username in
('SYS', 'SYSTEM');

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYS SYSTEM
SYSTEM SYSTEM

2 rows selected.

Post by Hameed, Amir
What is the default tablespace of SYS and SYSTEM?
Behalf Of *Sundar Mahadevan
*Sent:* Saturday, November 10, 2018 6:45 PM
*Subject:* Re: ORA-01647 during TTS IMPDP from 11.2.0.4 64 bit Solaris
SPARC to 12.1.0.2 64 bit RHEL
Thanks Mladen for reverting back. Yes, I did
run DBMS_TTS.TRANSPORT_SET_CHECK, with the constraints included, on the
source side and it returned no issues.
${ORACLE_HOME}/bin/sqlplus -s /nolog << eom

set head off feedback on pages 0 lines 400 echo on
connect / as sysdba
execute

sys.dbms_tts.transport_set_check('ARGUS_AEXP_DATA_01,ARGUS_AEXP_INDEX_01,DLP_INDEX_06,DLP_INDEX_04,ARGUS_DATA_01,
-
DLP_DATA_04,ARGUS_AL_INDEX_01,XDB,ARGUS_INDEX_03,ARGUS_INDEX_06,ESM_INDEX_01,ARGUS_DATA_02,ARGUS_DATA_04,DLP_INDEX_05,
-
ARGUS_INDEX_01,ARGUS_DATA_03,DLP_DATA_01,ARGUS_AL_DATA_01,ARGUS_INDEX_02,DLP_DATA_02,DLP_DATA_05,ARGUS_DATA_05,ARGUS_INDEX_04,
-
DLP_INDEX_03,DLP_DATA_03,DLP_INDEX_02,ESM_DATA_01,DLP_LOB_01,DLP_INDEX_01,USERS,ARGUS_INDEX_05',
true, true);

select * from sys.transport_set_violations;
eom

PL/SQL procedure successfully completed.
no rows selected
Did you do DBMS_TTS.TRANSPORT_SET_CHECK, with the constraints included,
on the source side?

Hi All,
Greetings. While performing a transportable tablespace of application
tablespaces from 11.2.0.4 Enterprise Edition 64 bit Solaris SPARC 10
1/13 to 12.1.0.2 Enterprise Edition 64 bit RHEL 7.3, I hit the
following error.
ORA-39083: Object type TABLE:"ARGUS_APP"."CASE_PRODUCT" failed to
ORA-01647: tablespace 'ARGUS_DATA_05' is read-only, cannot allocate
space in it
CREATE TABLE "ARGUS_APP"."CASE_PRODUCT" ("CASE_ID" NUMBER NOT NULL
ENABLE, "SEQ_NUM" NUMBER NOT NULL ENABLE, "PRODUCT_ID" NUMBER,
"DRUG_TYPE" NUMBER, "PAT_EXPOSURE" NUMBER DEFAULT 0, "MANUFACTURER_ID"
NUMBER, "PROTOCOL_FOLLOWED" NUMBER, "SYS_C00008_11082108:57:04$"
NUMBER, "FIRST_SUS_PROD" NUMBER, "SELECTED_VIEW" NUMBER, "SORT_ID
ORA-39083: Object type TABLE:"ARGUS_APP"."CASE_ASSESS" failed to
ORA-01647: tablespace 'ARGUS_DATA_01' is read-only, cannot allocate
space in it
I get this error for 5 tables. 3 tables out of 5 that returned
ORA-01647 during impdp were non empty tables. No partitions on any of
the tables. 2 tables out of the 5 have clobs. Regardless
of deferred_segment_creation set to TRUE or FALSE on the source
database, I get the same error. I even manually allocated extent on
the 5 failing tables as suggested in a blog post with no luck. Anyone
faced this issue? Ticket has been created with support. Not much
progress yet. Thanks for your time and assistance. Appreciate it.

--
Mladen Gogala Database Consultant Tel: [phone]
--
http://www.freelists.org/webpage/oracle-l

[Mladen Gogala]

Post by Sundar Mahadevan
Thanks Mladen for reverting back. Yes, I did
run DBMS_TTS.TRANSPORT_SET_CHECK, with the constraints included, on
the source side and it returned no issues.
${ORACLE_HOME}/bin/sqlplus -s /nolog << eom

set head off feedback on pages 0 lines 400 echo on
connect / as sysdba
execute

sys.dbms_tts.transport_set_check('ARGUS_AEXP_DATA_01,ARGUS_AEXP_INDEX_01,DLP_INDEX_06,DLP_INDEX_04,ARGUS_DATA_01,
-
DLP_DATA_04,ARGUS_AL_INDEX_01,XDB,ARGUS_INDEX_03,ARGUS_INDEX_06,ESM_INDEX_01,ARGUS_DATA_02,ARGUS_DATA_04,DLP_INDEX_05,
-
ARGUS_INDEX_01,ARGUS_DATA_03,DLP_DATA_01,ARGUS_AL_DATA_01,ARGUS_INDEX_02,DLP_DATA_02,DLP_DATA_05,ARGUS_DATA_05,ARGUS_INDEX_04,
-
DLP_INDEX_03,DLP_DATA_03,DLP_INDEX_02,ESM_DATA_01,DLP_LOB_01,DLP_INDEX_01,USERS,ARGUS_INDEX_05',
true, true);

select * from sys.transport_set_violations;
eom

PL/SQL procedure successfully completed.
no rows selected

It looks like a bug in Oracle 12c on RHEL. You should open a support
case. If TTS check didn't report any issues, your tablespaces should
have been imported. The only thing that comes to mind is some curious
file protection issue which prevents Oracle 12c on Linux to open the
file for writing.

[Sundar Mahadevan]

Thanks Mladen and Amir. Case was opened 2 days back. Waiting on them. So
far not much progress on the SR.

Post by Sundar Mahadevan

Post by Sundar Mahadevan
Thanks Mladen for reverting back. Yes, I did
run DBMS_TTS.TRANSPORT_SET_CHECK, with the constraints included, on
the source side and it returned no issues.
${ORACLE_HOME}/bin/sqlplus -s /nolog << eom

set head off feedback on pages 0 lines 400 echo on
connect / as sysdba
execute

sys.dbms_tts.transport_set_check('ARGUS_AEXP_DATA_01,ARGUS_AEXP_INDEX_01,DLP_INDEX_06,DLP_INDEX_04,ARGUS_DATA_01,

Post by Sundar Mahadevan
-

DLP_DATA_04,ARGUS_AL_INDEX_01,XDB,ARGUS_INDEX_03,ARGUS_INDEX_06,ESM_INDEX_01,ARGUS_DATA_02,ARGUS_DATA_04,DLP_INDEX_05,

Post by Sundar Mahadevan
-

ARGUS_INDEX_01,ARGUS_DATA_03,DLP_DATA_01,ARGUS_AL_DATA_01,ARGUS_INDEX_02,DLP_DATA_02,DLP_DATA_05,ARGUS_DATA_05,ARGUS_INDEX_04,

Post by Sundar Mahadevan
-

DLP_INDEX_03,DLP_DATA_03,DLP_INDEX_02,ESM_DATA_01,DLP_LOB_01,DLP_INDEX_01,USERS,ARGUS_INDEX_05',

Post by Sundar Mahadevan
true, true);

select * from sys.transport_set_violations;
eom

PL/SQL procedure successfully completed.
no rows selected

It looks like a bug in Oracle 12c on RHEL. You should open a support
case. If TTS check didn't report any issues, your tablespaces should
have been imported. The only thing that comes to mind is some curious
file protection issue which prevents Oracle 12c on Linux to open the
file for writing.
--
Mladen Gogala
Database Consultant
Tel: [phone]