PURPOSE
-------
Use transportable tablespace in 10G database to move tablespaces between different Operating Systems.
SCOPE & APPLICATION
-------------------
With previous releases of the Oracle Database, you could not transport a tablespace to a target database if a tablespace with the same name already existed in the target database.
With Oracle Database 10G, you can simply rename the tablespace in the target database to some other name before the transport.
Special Considerations:
Before issuing this command, make sure the compatibility level is set to at least 10.0.0.
If any datafile in the tablespace is offline or if the entire tablespace is offline, then the tablespace cannot be renamed.
Renaming a tablespace does not change its tablespace identifier. If the tablespace is READ ONLY, then this command does
not update datafile headers. You get a message in the alert log indicating the datafile headers are not updated. These
headers can only be changed if the tablespace is put back in READ WRITE mode, if ever possible. If the tablespace is
an undo tablespace, and it is the tablespace specified by the initialization parameter UNDO_TABLESPACE in any
instances, then they are changed to reflect the new name. These changes affect both memory and SPFILE. All
these changes are logged in the alert log file. If SPFILE is not being used, then a specific message is added
to the alert log file advising DBAs to manually change the corresponding initialization parameter file.
Note: Users with ALTER TABLESPACE privilege are able to use this feature. It is not possible to rename the SYSTEM or SYSAUX tablespaces.
TRANSPORTABLE TABLESPACE BETWEEN DIFFERENT Operating Systems
-------------------------------------------------------------
1. Set the tablespace to READ ONLY
-------------------------------
SQL> alter tablespace TRANSPORTABLE_TESTE read only;
Tablespace altered.
2. Export metadata
---------------
WINDOWS - exp userid=sys as sysdba transport_tablespace=y tablespaces=TRANSPORTABLE_TESTE file=ts.dmp log=exp_ts.log statistics=none
Export: Release 10.1.0.2.0 - Production on Qui Out 28 00:16:11 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Conectado a: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options
Exportação executada no conjunto de caracteres de WE8MSWIN1252 e no conjunto de caracteres de AL16UTF16 NCHAR
OBS: dados (linhas) da tabela não serão exportados
Prestes a exportar metadados de tablespace transportavel...
Para tablespace TRANSPORTABLE_TESTE ...
. exportando definições de cluster
. exportando definições de tabela
. exportando restrições referenciais de integridade
. exportando gatilhos
. exportação final de metadados de tablespace transportavel
Exportação encerrada com sucesso, sem advertências.
3. Check the endianness of the target database
For check platform do the following:
SQL> select * from v$transportable_platform;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------------------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
13 Linux 64-bit for AMD Little
16 Apple Mac OS Big
12 Microsoft Windows 64-bit for AMD Little
15 linhas selecionadas.
The source platform is Microsoft Windows IA (32-bit): endianness Little
The target platform is HP-UX (64-bit) : endianness Big
You have to convert the files :
--> locally before the import step so that the files are endian compatible:
<Windows> rman target=/
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
conectado ao banco de dados de destino: ORCL (DBID=1068469100)
RMAN> convert tablespace 'TRANSPORTABLE_TESTE' to platform="HP-UX (64-bit)" db_file_name_convert='D:\ORACLE\SERVER10G\ORCL\TRANSPORTABLE_TESTE.ORA', 'D:\TRANSPORTABLE_TESTE01.dbf';
Iniciando backup em 28/10/04
usar o arquivo de controle do banco de dados de destino em vez do catalogo de recuperação
canal alocado: ORA_DISK_1
canal ORA_DISK_1: sid=148 devtype=DISK
canal ORA_DISK_1: iniciando conversao de arquivo de dados
fno=00006 name=D:\ORACLE\SERVER10G\ORCL\TRANSPORTABLE_TESTE.ORA do arquivo de dados de entrada arquivo de dados convertido=D:\TRANSPORTABLE_TESTE01.DBF
canal ORA_DISK_1: conversao de arquivo de dados concluida, tempo decorrido: 00:00:03
Finalizado backup em 28/10/04
The converted datafile is staged in /tmp directory until it is copied to the target server.
--> remotely on the target server after having copied them on the server.
4. Move datafiles and export dump file
-----------------------------------
C:\> ftp ts.dmp
D:\TRANSPORTABLE_TESTE01.dbf
5. Import metadata
---------------
$ imp userid=\'/ as sysdba\' TRANSPORT_TABLESPACE=Y
datafiles=/TMP/TRANSPORTABLE_TESTE01.dbf
file=ts.dmp log=imp_ts.log
6. Set the tablespace to READ WRITE
--------------------------------
SQL> alter tablespace TRANSPORTABLE_TESTE read write;
Tablespace altered.
-------
Use transportable tablespace in 10G database to move tablespaces between different Operating Systems.
SCOPE & APPLICATION
-------------------
With previous releases of the Oracle Database, you could not transport a tablespace to a target database if a tablespace with the same name already existed in the target database.
With Oracle Database 10G, you can simply rename the tablespace in the target database to some other name before the transport.
Special Considerations:
Before issuing this command, make sure the compatibility level is set to at least 10.0.0.
If any datafile in the tablespace is offline or if the entire tablespace is offline, then the tablespace cannot be renamed.
Renaming a tablespace does not change its tablespace identifier. If the tablespace is READ ONLY, then this command does
not update datafile headers. You get a message in the alert log indicating the datafile headers are not updated. These
headers can only be changed if the tablespace is put back in READ WRITE mode, if ever possible. If the tablespace is
an undo tablespace, and it is the tablespace specified by the initialization parameter UNDO_TABLESPACE in any
instances, then they are changed to reflect the new name. These changes affect both memory and SPFILE. All
these changes are logged in the alert log file. If SPFILE is not being used, then a specific message is added
to the alert log file advising DBAs to manually change the corresponding initialization parameter file.
Note: Users with ALTER TABLESPACE privilege are able to use this feature. It is not possible to rename the SYSTEM or SYSAUX tablespaces.
TRANSPORTABLE TABLESPACE BETWEEN DIFFERENT Operating Systems
-------------------------------------------------------------
1. Set the tablespace to READ ONLY
-------------------------------
SQL> alter tablespace TRANSPORTABLE_TESTE read only;
Tablespace altered.
2. Export metadata
---------------
WINDOWS - exp userid=sys as sysdba transport_tablespace=y tablespaces=TRANSPORTABLE_TESTE file=ts.dmp log=exp_ts.log statistics=none
Export: Release 10.1.0.2.0 - Production on Qui Out 28 00:16:11 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Conectado a: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options
Exportação executada no conjunto de caracteres de WE8MSWIN1252 e no conjunto de caracteres de AL16UTF16 NCHAR
OBS: dados (linhas) da tabela não serão exportados
Prestes a exportar metadados de tablespace transportavel...
Para tablespace TRANSPORTABLE_TESTE ...
. exportando definições de cluster
. exportando definições de tabela
. exportando restrições referenciais de integridade
. exportando gatilhos
. exportação final de metadados de tablespace transportavel
Exportação encerrada com sucesso, sem advertências.
3. Check the endianness of the target database
For check platform do the following:
SQL> select * from v$transportable_platform;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------------------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
13 Linux 64-bit for AMD Little
16 Apple Mac OS Big
12 Microsoft Windows 64-bit for AMD Little
15 linhas selecionadas.
The source platform is Microsoft Windows IA (32-bit): endianness Little
The target platform is HP-UX (64-bit) : endianness Big
You have to convert the files :
--> locally before the import step so that the files are endian compatible:
<Windows> rman target=/
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
conectado ao banco de dados de destino: ORCL (DBID=1068469100)
RMAN> convert tablespace 'TRANSPORTABLE_TESTE' to platform="HP-UX (64-bit)" db_file_name_convert='D:\ORACLE\SERVER10G\ORCL\TRANSPORTABLE_TESTE.ORA', 'D:\TRANSPORTABLE_TESTE01.dbf';
Iniciando backup em 28/10/04
usar o arquivo de controle do banco de dados de destino em vez do catalogo de recuperação
canal alocado: ORA_DISK_1
canal ORA_DISK_1: sid=148 devtype=DISK
canal ORA_DISK_1: iniciando conversao de arquivo de dados
fno=00006 name=D:\ORACLE\SERVER10G\ORCL\TRANSPORTABLE_TESTE.ORA do arquivo de dados de entrada arquivo de dados convertido=D:\TRANSPORTABLE_TESTE01.DBF
canal ORA_DISK_1: conversao de arquivo de dados concluida, tempo decorrido: 00:00:03
Finalizado backup em 28/10/04
The converted datafile is staged in /tmp directory until it is copied to the target server.
--> remotely on the target server after having copied them on the server.
4. Move datafiles and export dump file
-----------------------------------
C:\> ftp ts.dmp
D:\TRANSPORTABLE_TESTE01.dbf
5. Import metadata
---------------
$ imp userid=\'/ as sysdba\' TRANSPORT_TABLESPACE=Y
datafiles=/TMP/TRANSPORTABLE_TESTE01.dbf
file=ts.dmp log=imp_ts.log
6. Set the tablespace to READ WRITE
--------------------------------
SQL> alter tablespace TRANSPORTABLE_TESTE read write;
Tablespace altered.
