Transportable Tablespaces fails with ORA-01647 – TABLESPACE IS READ-ONLY

This is a blog post I wanted to write a long time ago  – and I missed it. Today, my best buddy Jony Safi shared an issue from a customer migration he had been pulled in the other day. I read the SR, and it dawned me that others may have seen this error as well. As a result, Transportable Tablespaces fails with ORA-01647 – TABLESPACE IS READ-ONLY – and there are two ways to solve this. Let me add ORA-6512 and ORA-1647 in case you search without the trailing zero.

Photo by Ben Harritt on Unsplash

What is the error you will see?

We came across this a while ago when we migrated “The Beast” (1:50:53 – Customer Case: Migration the Beast – M5 use case). When we started the Full Transportable Export, it failed with:

BEGIN
"SYS"."DBMS_SCHED_EXPORT_CALLOUTS".SCHEMA_CALLOUT(:1,0,1,'19.00.00.00.00');
END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-01647: tablespace '' is read-only, cannot allocate space in it
ORA-01647: tablespace 'TS_MYDATA' is read-only, cannot allocate space in it
...

This lead to sort of a catch-22 situation. You need to turn your user tablespaces into read-only mode for transporting them. But then you receive the error being raised because the tablespace is in read-only mode.

Why did the error come up?

Actually, a security change was made for Oracle 19c under Bug 27692190 – DBMS_SCHED_MAIN_EXPORT ACCESSES CUSTOMER SCHEMA. This lead to this new behavior years ago.

You would only see this when your source environment is 19c, and when you attempt either a Transportable Tablespace operation or a Full Transportable Export/Import migration, for instance using the M5 or the old V4 scripts. Hence, this is not a “new” issue. This change was done years ago, and many of you will never see it since you are not doing such migrations.

But I needed to put it out here on the blog for those who run into the issue. This way, it is easy to find – and solve it.

Andreas asked me to add this important information (which I happily do. Thanks, Andreas!!):

The issue doesn’t happen generally with scheduler jobs but in the case you having arguments when calling jobs. In this case it can happen with scheduler jobs and scheduler programs.

How do you solve it?

There are two options to solve this known to me.

Option 1 – Apply a patch

The (mis)behavior has been logged as Bug 35332790 – FULL EXPORT: ORA-06512: AT “SYS.DBMS_SYS_ERROR” ORA-01647 – TABLESPACE IS READ-ONLY FOR SCHEDULER JOBS. Therefore, your option 1 is to download a patch for Bug 35332790. But this fix has never added to any RU since it changes prvtbsch.sql. This (or prvtbsch.plb) are the scheduler definition packages, and changes in these are not allowed to be included into RUs most likely since they may break the rolling patching abilities (this may change sometime later potentially).

This means, you need to download a one-off patch 35332790 from My Oracle Support. But check it upfront. I found 15 one-offs for 10 different RUs on 4 different platforms. You may need to request a backport on top of your specific RU for your platform. Don’t forget to upload your “opatch lsinventory” and “opatch lspatches” files when you request the backport.

.

Option 2 – Workaround with a script

The problem may occur not only with the scheduler itself but with other users as well. And there is a relatively simple workaround available.

1. You need to create a script where you could change all of your users to default tablespace SYSTEM
2. You need to create a script where you could switch all your users back to their original default tablespaces
3. Then, when downtime started and before you turn all users tablespaces to read-only, you execute script (1) which alters all users to default tablespace SYSTEM
4. Then you run your full transportable export
5. Afterwards, you usually turn your tablespaces back to read-write, and then you execute the script from (2)
6. Since you transported your database and recreated the users on the target now with default tablespace SYSTEM, you need to execute the script from (2) on the target side as well

I know, this sounds a bit weird but it is a simple and structured workaround.

.

Script examples

Here’s an example for Option 2 – but please try it out at first before copying it blindly.

1. Script to alter users to SYSTEM

   set echo off
   set feedback off
   set heading off
   set pagesize 0
   set linesize 32767
   set trimspool on
   set verify off
   set termout off
   
   spool alter_non_oracle_users_to_system.sql
   
   select 'ALTER USER "' || username || '" DEFAULT TABLESPACE SYSTEM;'
   from   dba_users
   where  oracle_maintained = 'N'
   and    default_tablespace <> 'SYSTEM'
   order  by username;
   
   spool off

2. Script to alter users back to their default tablespace

   set echo off
   set feedback off
   set heading off
   set pagesize 0
   set linesize 32767
   set trimspool on
   set verify off
   set termout off
   
   spool restore_non_oracle_users_default_ts.sql
   
   select 'ALTER USER "' || username || '" DEFAULT TABLESPACE "' ||
          default_tablespace || '";'
   from   dba_users 
   where  oracle_maintained = 'N'
   and    default_tablespace <> 'SYSTEM'
   order  by username;
   
   spool off

    

   .

Further Links and Information

• 1:50:53 – Customer Case: Migration the Beast – M5 use case
• Virtual Classroom Seminar 18: Cross Platform Migration – Transportable Tablespaces to the Extreme
• MOS Note: KI37168 – Bug 35332790 – “SYS.DBMS_SCHED_EXPORT_CALLOUTS”, ORA-1647 – tablespace is read-only for scheduler jobs

–Mike