When you Transport Tablespaces, you may be aware of a change in behavior a long while ago. When your tablespace import has been finished, a tablespace will be taken read-write automatically. But there may be situations when you’d like the previous behavior. Now you can have this with Transportable Tablespaces: Keep tablespace files read-only.
Photo by freestocks on Unsplash
Default Behavior
Since Oracle Database 12c a tablespace will be taken read-write once the meta data import has been completed automatically. This was different in previous database releases. We used the technique a long time ago for a telco provider when we created an instance with a higher database version, and imported the tablespaces into it. As a result, the tablespaces were mounted from two different databases. This worked fine, and gave an ideal fallback in case of problems during import. It worked fine as the tablespaces were read-only.
For such scenarios but also to test the import several times, you may want to have this behavior back. The only workaround I knew was to change OS permissions on the data files to keep the files read-only.
New Option in Oracle 19c
With Oracle Database 19c, you have this new option. When you import the tablespaces, you can decide whether they should stay read-only. Then you need to switch them read-write manually afterwards at will.
When you exported tablespaces, you can use this new parameter option during import:
USERID=system/oracle@pdb1 DIRECTORY=DATA_PUMP_DIR DUMPFILE=tts.dmp LOGFILE=ttsimport.log TRANSPORT_DATAFILES='/u02/oradata/CDB2/pdb1/tts01.dbf' TRANSPORTABLE=KEEP_READ_ONLY
Once the import has been finished, you can check the tablespaces:
SQL> select tablespace_name, status from dba_tablespaces; TABLESPACE_NAME STATUS ---------------- ----------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE TTS READ ONLY
Now you can alter the tablespace to read-write whenever you’ll like to.
Further Information and Links
- TRANSPORTABLE=KEEP_READ_ONLY in the Oracle 19c Documentation
- Test Transportable Tablespaces without Read-Only Mode
- Transportable Tablespace Example
–Mike
keep_read_only is to avoid doing tablespace bitmap rebuild and timezone conversions, please see 19c DataPump New Features (Doc ID 2457955.1)
Believe me, this was NOT the reason why this came in. It is a nice side effect. Data Pump rejects the impdp for transportable when the time zone source/target don’t match as this will potentially logically corrupt your timestamp with time zone data types. Have you tried to set the TS read/write then with an incorrect time zone??
The feature came in for exactly the reason I explained as I filed it 🙂
But thanks for referring to the MOS note.
Cheers,
Mike
Hi Mike
I got an confusing error when importing full transportable .
In the import process show that it can not create index and the error is:
ORA-01647: tablespace ‘TBS_01’ is read-only, cannot allocate space in it.
My source database is 11.2.0.4 and target database is 19.8 and I’m importing to a PDB.
Why it happens?
You please need to open an SR – unfortunately I can’t do remote diagnosis 🙁
Cheers,
Mike