Transportable Tablespaces: Keep tablespace files read-only

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