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
Hi Mike,
Thanks for the transportable tablespace read-only Tip.
We were thinking of using TTS Migration by using a physical standby database to avoid the physical file copy.
The idea here is to use the physical standby as file transporter for TTS.
In our case both source and destination are on same platform and OS.
I know we would have used rman duplicate in this case, but we want to use TTS in order to clean up old system/sysaux Tablespace.
The steps we are thinking of are:
– create a normal physical standby db ( we already have one )
– on same standby db server create a second db ( future target db ), with only system/sysaux and temp tablespaces
– Import users on future target db
– we are relying on the idea that, Tablespaces can be mounted from two dbs at the same time as long as they are set read-only
– The next step would be to set tts tablespaces to read-only and do transportable import into future target db.
At this point it’s not clear wether we should activate the standby db, set tbs to read only and then proceed with tts import on target db,
or if we could just perform transportable import into target db, by specifying the datafiles of the existing phy standby db.
Are you aware of any Documents which clarify this procedure in detail?
Especially regarding the steps during the transportable import.
Best Regards,
Pascal
Hi Pascal,
this should work – and I would convert the standby into a Snapshot Standby – this helps you avoid all the read/only trouble.
And no, I am not aware of documents unfortunately.
Cheers,
Mike
We also used to have same tablespace imported in two databases. However we changed the concept and introduced storage level snapshot before we imported to the second daabase.
This default read/write was really pain. It caused even different issue, when you imported more tablespaces and impdp failed somewhere in middle, you were not able to repeat the job as part of tablespaces was already read write. The workarround was only to remove write permissions to files on filesystem level…
I just wonder why oracle did not change also why oracle did not change documentation also in impdp HELP=Y. There is still stated:
TRANSPORTABLE
Options for choosing transportable data movement.
Valid keywords are: ALWAYS and [NEVER].
Only valid in NETWORK_LINK mode import operations.
Interesting – I was not aware that the HELP does not show this. Let me drop a message to the dev team.
With a coming release, even TTS jobs will become restartable – then your pain will be relieved …
cheers, and sorry for this inconvenience!
Mike