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.
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