transportable tablespace with rman - no read only needed!

This is the ‘standard method or transporting a tablespace introduced at 10g (though a 9i version with old style export was also available). The source tablespace is put into read only mode, the files copied and then a short datapump command run.

Make the tablespace read only

ALTER TABLESPACE ENDURF_ENV01 read only;

Copy the files using standard os tools

cp ENDURF_ENV01_01.DBF ENDURF_TTS_01.DBF
cp ENDURF_ENV01_02.DBF ENDURF_TTS_02.DBF
cp ENDURF_ENV01_03.DBF ENDURF_TTS_03.DBF
cp ENDURF_ENV01_04.DBF ENDURF_TTS_04.DBF
cp ENDURF_ENV01_05.DBF ENDURF_TTS_05.DBF
cp ENDURF_ENV01_06.DBF ENDURF_TTS_06.DBF
cp ENDURF_ENV01_07.DBF ENDURF_TTS_07.DBF



Export the metadata for the physical objects in that tablespace

expdp / dumpfile=tts.dmp transport_tablespaces=ENDURF_ENV01

Switch the tablespace back to read write

ALTER TABLESPACE ENDURF_ENV01 read write;


The way Oracle have got round the tablespace needing to be read only for the transport is to extend the functionality of rman. A command has been added that will do all of the required steps for you and is shown below:


rman target=/

run {
SET AUXILIARY INSTANCE PARAMETER FILE TO '/tmp/temp.ora';
transport tablespace endurf_env01
tablespace destination '/oracle/ENDRF1D/oradata/TTS'
auxiliary destination '/oracle/ENDRF1D/oradata/TTS'
until time 'sysdate-1/2';
}

The command does the following steps in order to generate the transportable tablespace set:
a)      Create a temporary new database with a randomly generated name which it will restore to
b)      Restore the tablespace required (and system/sysaux/undo) from tape to this temporary instance
c)      Recover it to the point in time specified in the command
d)     Open the database and make the tablespace read only
e)      Do a datapump export of the tablespace
f)       Remove all the temporary files it created along the way and just leave you with the datafiles and the datapump file

The line ‘set auxiliary instance parameter file to xxxx is only necessary in oracle 11.1 due to a bug. This file contains just one line with the following contents (processes=100).
All of the datafiles are recovered to the destination mentioned in the ‘tablespace destination’ setting
All of the datapump files/logs are created in the ‘auxiliary destination’ directory.
Until time can be used to specify any time for which we have a backup on disk – the default is current time if its not specified.

 

Which method used will have created a full copy of the physical tablespace – tables/indexes etc – things which physically exist inside the datafiles. We also need to copy the ‘logical’ objects which only exist in the data dictionary of the source system – procedures for example. This is done using another datapump script which will extract all the logical objects we need to have a full schema copy.

The command to run this is shown below:

Expdp / schemas=ENDURF_ENV01
include=TABLE:"IN (SELECT table_name from dba_tables where owner='ENDURF_ENV01' and temporary='Y')"
include=FUNCTION,PACKAGE,PROCEDURE,SEQUENCE,SYNONYM,VIEW,USER,ROLE_GRANT,SYSTEM_GRANT
dumpfile=ttsuser.dmp

To summarise what this is doing – we are saying to datapump given me the following objects from the ENDURF_ENV01 schema:

FUNCTION,PACKAGE,PROCEDURE,SEQUENCE,SYNONYM,VIEW,USER,ROLE_GRANT,SYSTEM_GRANT

And also give me the table definitions for temporary tables (these do not physically exist in the datafile only in the data dictionary).

At this point we have a full copy of the entire Endur schema that we can copy to any destination system.

The previous steps will have generated a copy of the physical datafiles and some datapump files. All of these files should be copied to the destination system using standard file transfer tools scp/sftp for example.



The datapump file generated containing the logical objects should now be imported into the destination system – this will create the destination schema with the correct rights and will also create all of the logical objects we extracted . At this point as none of the physical tables exist this step will create a huge amount of errors as none of the procedures/views will compile. These can be ignored as we will recompile them after the physical objects have been created.

impdp / remap_schema=ENDURF_ENV01:TTS directory=tts dumpfile=ttsuser.dmp

Attaching the tablespace to the destination system is the same process whichever method was used to create the initial copy. Again it is a simple datapump import which is shown below:

impdp / parfile=ttstablespace.par (where ttstablespace.par contains the following)

directory=tts dumpfile= dmpfile.dmp transport_datafiles= /oracle/ENDRF1D/oradata/TTS/ENDURF_ENV01_01.DBF, /oracle/ENDRF1D/oradata/TTS/ENDURF_ENV01_06.DBF, /oracle/ENDRF1D/oradata/TTS/ENDURF_ENV01_03.DBF, /oracle/ENDRF1D/oradata/TTS/ENDURF_ENV01_02.DBF, /oracle/ENDRF1D/oradata/TTS/ENDURF_ENV01_05.DBF, /oracle/ENDRF1D/oradata/TTS/ENDURF_ENV01_04.DBF, /oracle/ENDRF1D/oradata/TTS/ENDURF_ENV01_07.DBF remap_schema=ENDURF_ENV01:TTS remap_tablespace=ENDURF_ENV01:TTS

The key parts of the import are the remapping of the schema name and the tablespace name. Care must be taken to ensure all of the files generated earlier are listed in the import command.

The dumpfile mentioned is the one generated automatically by datapump.


The following steps complete the copying process and make the system useable again for the Endur application.

Make the tablespace read/write and available for use.

alter tablespace tts read write;

Give the standard Endur sys privileges required for operation of the system.

grant select on V_$MYSTAT to tts;
grant select on  GV_$SESSION to tts;
grant select on V_$SESSION  to tts;
grant select on DBA_ROLES to tts;

Change the destination schema to have full quota on the new tablespace and make it the users default. This was not done by the earlier ‘logical’ import step as the destination tablespace still did not exist at this point.

alter user tts quota unlimited on tts;
alter user tts default tablespace tts;

Recompile all the objects that were created when the logical import was done before the physical tables existed.

@?/rdbms/admin/utlrp

Comments

Post a Comment