Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to achieve tablespace transfer in the database", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to achieve tablespace transfer in the database" this article.

1. Make sure that the character sets of the source side and the destination side are consistent.

Select userenv ('language') from dual

The source and the destination databases must use compatible database character sets

Source and target databases must use compatible database character sets

The database character sets of the source and the target databases are the same.

The database character set of the source and target databases is the same.

The source database character set is a strict (binary) subset of the target database character set

The source database character set is a strict (binary) subset of the target database character set

The source and the target databases must use compatible national character sets

Source and target databases must use compatible national character sets

2. Make sure that the source-side tablespace does not contain SYS objects, and establish these OWNER on the destination side

Select OWNER from dba_segments where TABLESPACE_NAME='XX'

You cannot transport a tablespace to a destination database that contains a tablespace of the same name

Cannot transfer tablespaces to a target database that contains tablespaces with the same name

You cannot transport the SYSTEM tablespace or objects owned by the user SYS

You cannot transfer objects owned by SYSTEM tablespaces or user SYS

3. Query the byte order of the source and destination

SELECT * FROM V$TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME

If the source platform and the destination platform are of different endianness, then an additional step must be done on either the source or destination platform to convert the tablespace being transported to the destination format

If the source and target platforms have different byte order, additional steps must be performed on the source or target platform to convert the tablespace being transferred to the target format

4. Query whether the source tablespace is self-contained.

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK ('XX')

SELECT * FROM TRANSPORT_SET_VIOLATIONS;-- query result is empty, indicating self-contained

5. Query the data files corresponding to the source tablespace

Select FILE_NAME from dba_data_files where TABLESPACE_NAME='XX'

6. Set the tablespace to read-only on the source side and export the format file

SQL > ALTER TABLESPACE XX READ ONLY

Expdp system/123456 directory=data_pump_dir transport_tablespaces=XX dumpfile=expdpXX.dmp

7. If the byte order of the source and destination is the same, copy the expdpXX.dmp in step 6 to the directory corresponding to the destination data_pump_dir, and copy the data file corresponding to the source tablespace to the destination, such as c:\ app\ orauser\ oradata\ orawin\ XX.dbf.

8. If the byte order of the source side is inconsistent with that of the destination, the source side executes rman convert tablespace, and then copies the data files of expdpXX.dmp and / tmp/%U in step 6 to the destination side, expdpXX.dmp to the directory corresponding to data_pump_dir on the destination side, and the data file of / tmp/%U to the directory corresponding to the dba_data_files.file_name on the destination side.

RMAN > CONVERT TABLESPACE XX TO PLATFORM 'Microsoft Windows IA (32-bit)' FORMAT'/ tmp/%U'

As above, if the byte order of the target side is Microsoft Windows IA (32-bit), / tmp/%U is the data file that stores the converted XX table space.

9. Set the source tablespace back to read write

ALTER TABLESPACE XX READ WRITE

10. The target side imports the tablespace

Impdp system/123456 directory=data_pump_dir dumpfile=expdpXX.dmp transport_datafiles='c:\ app\ orauser\ oradata\ orawin\ XX.dbf'

11. Check (if EM is done, the source side uses replica export by default, and the destination side defaults to read wirte, so the source side defaults to ONLINE operation. If you use the command, the source side and destination side should be manually set to read write)

Source: select STATUS from dba_tablespaces where TABLESPACE_NAME='XX';-- result must be ONLINE. If it is READ ONLY, it must be set to read write.

Destination side:

The select STATUS from dba_tablespaces where TABLESPACE_NAME='XX';-- result must be ONLINE. If it is READ ONLY, set it to read write.

Select OWNER from dba_segments where TABLESPACE_NAME='XX'

Note: of course, if the byte order is different, the data file copied from the source side to the destination side can also execute rman convert tablespace on the destination side without going through step 8.

RMAN > CONVERT DATAFILE'c:\ app\ orauser\ oradata\ orawin\ XX.dbf' TO PLATFORM= "Microsoft Windows IA (32-bit)" FROM PLATFORM= "Solaris [tm] OE (32-bit)"

Or directly as follows, regardless of what the source end is.

RMAN > CONVERT DATAFILE'c:\ app\ orauser\ oradata\ orawin\ XX.dbf' TO PLATFORM= "Microsoft Windows IA (32-bit)"

Experimental step _ use command mode (the OS on the source side and the destination side is the same, so the byte order is the same)

1. The character set of source prod2 and destination TDB is the same.

SQL > show parameter db_name

NAME TYPE VALUE

Db_name string prod2

SQL > select userenv ('language') from dual

USERENV ('LANGUAGE')

-

AMERICAN_AMERICA.AL32UTF8

SQL > show parameter db_name

NAME TYPE VALUE

Db_name string TDB

SQL > select userenv ('language') from dual

USERENV ('LANGUAGE')

--

AMERICAN_AMERICA.AL32UTF8

2. The tablespace to be transferred on the source side is PRO2017, the user of the tablespace object does not have SYS, and the tablespace does not exist on the destination side.

SQL > select TABLESPACE_NAME from dba_tablespaces where TABLESPACE_NAME='PRO2017'

TABLESPACE_NAME

-

PRO2017

SQL > select OWNER from dba_segments where TABLESPACE_NAME='PRO2017'

OWNER

-

PRO2017

No result on the target side of SQL > select TABLESPACE_NAME from dba_tablespaces where TABLESPACE_NAME='PRO2017';--

No rows selected

3. Make sure that the source end is self-contained.

SQL > EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK ('PRO2017')

PL/SQL procedure successfully completed.

SQL > SELECT * FROM TRANSPORT_SET_VIOLATIONS

No rows selected

4. Query the data files corresponding to the source tablespace

SQL > select FILE_NAME from dba_data_files where TABLESPACE_NAME='PRO2017'

FILE_NAME

/ mnt/sdb1/u01/app/oracle/oradata/prod2/pro2017.dbf

5. Set the tablespace to read-only on the source side and export the format file

SQL > ALTER TABLESPACE PRO2017 READ ONLY

[oracle@mestest 2] $expdp system/123456 directory=data_pump_dir transport_tablespaces=PRO2017 dumpfile=expdpPRO2017.dmp

Export: Release 11.2.0.4.0-Production on Thu Oct 19 05:01:04 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM". "SYS_EXPORT_TRANSPORTABLE_01": system/* directory=data_pump_dir transport_tablespaces=PRO2017 dumpfile=expdpPRO2017.dmp

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Master table "SYSTEM". "SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded

*

Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:

/ mnt/sdb1/u01/app/oracle/admin/prod2/dpdump/expdpPRO2017.dmp

*

Datafiles required for transportable tablespace PRO2017:

/ mnt/sdb1/u01/app/oracle/oradata/prod2/pro2017.dbf

Job "SYSTEM". "SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu Oct 19 05:01:33 2017 elapsed 0 00:00:28

6. Copy the format file to the dump directory on the destination side, and datafile to the dba_data_file.file_name directory on the destination side.

Scp / mnt/sdb1/u01/app/oracle/oradata/prod2/pro2017.dbf oracle@192.168.30.173:/db/oracle/oradata/TDB/

Scp / mnt/sdb1/u01/app/oracle/admin/prod2/dpdump/expdpPRO2017.dmp oracle@192.168.30.173:/db/oracle/admin/TDB/dpdump/

7. Source-side execution

SQL > ALTER TABLESPACE PRO2017 READ WRITE

Tablespace altered.

8. If an error is reported on the target side, the user PRO2017 corresponding to the table space must be established on the target side.

[oracle@localhost TDB] $impdp system/123456 directory=data_pump_dir dumpfile=expdpPRO2017.dmp transport_datafiles='/db/oracle/oradata/TDB/pro2017.dbf'

Import: Release 11.2.0.4.0-Production on Thu Oct 19 20:33:59 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM". "SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYSTEM". "SYS_IMPORT_TRANSPORTABLE_01": system/* directory=data_pump_dir dumpfile=expdpPRO2017.dmp transport_datafiles=/db/oracle/oradata/TDB/pro2017.dbf

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

ORA-39123: Data Pump transportable tablespace job aborted

ORA-29342: user PRO2017 does not exist in the database

Job "SYSTEM". "SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at Thu Oct 19 20:34:02 2017 elapsed 0 00:00:02

SQL > create user PRO2017 identified by 123456

User created.

SQL > grant connect,resource to PRO2017

Grant succeeded.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@localhost TDB] $impdp system/123456 directory=data_pump_dir dumpfile=expdpPRO2017.dmp transport_datafiles='/db/oracle/oradata/TDB/pro2017.dbf'

Import: Release 11.2.0.4.0-Production on Thu Oct 19 20:35:19 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM". "SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYSTEM". "SYS_IMPORT_TRANSPORTABLE_01": system/* directory=data_pump_dir dumpfile=expdpPRO2017.dmp transport_datafiles=/db/oracle/oradata/TDB/pro2017.dbf

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYSTEM". "SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Thu Oct 19 20:35:23 2017 elapsed 0 00:00:02

SQL > select tablespace_name,status from dba_tablespaces where TABLESPACE_NAME='PRO2017'

TABLESPACE_NAME STATUS

PRO2017 READ ONLY

SQL > ALTER TABLESPACE PRO2017 READ WRITE

Tablespace altered.

The above is all the contents of the article "how to achieve tablespace transfer in the database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.