Oracle migration data table space

Source: Internet
Author: User

To test a large amount of basic data, IMP imports data too slowly. You can use the following method to quickly back up and restore a tablespace in the current Oracle database; the following method is also suitable for data table space migration between two databases (which must be the same operating system, the same database version, and the same character set.

Lab environment:
Source: 192.168.1.249 tablespace: anenjoy
Target: 192.168.1.248 tablespace: anenjoy02
Step 1: Make sure that the platform running in oracle supports transport tablespace (basically all supported. Learn to View Platform commands)
SQL> select * from v $ transportable_platform; (view the platforms supported by oracle)
SQL> select platform_name from v $ database; (view the platform currently running oracle)
SQL> select d. platform_name, endian_format from v $ transportable_platform tp, v $ database d where tp. platform_name = d. platform_name; (check whether the platform running in oracle is on the oracle Support Platform, and true if the returned data is true)

Step 2: create a data table space and data file on the source
SQL> create tablespace anenjoy datafile '/opt/oracle/oradata/test05/test05.dbf' size 50 m extent management local autoallocate;
Tablespace created.
Create a table based on the tablespace
SQL> create table test (name varchar2 (15), work varchar2 (10) tablespace anenjoy; (two fields: name and work)
Table created.
Insert some data,
Insert into test (name, work) values ('frank', 'it ');
SQL> select * from test;

NAME WORK
--------------------------------------------------
Frank IT
Alex caiwu
Leon yanfa

Step 3: Pick a Self-Contained Set of Tablespaces (check whether it is Self-Contained, that is, the tablespace to be transmitted is independent)
Execute dbms_tts.transport_set_check ('anenjoy ', true );
If you have multiple tablespaces, separate them with commas, for example, 'anenjoy01, anenjoy02'
Output log:
PL/SQL procedure successfully completed.
Run the command to check whether there are tablespaces that cannot be transferred.
SQL> select * from transport_set_violations; if a row is returned, it must be processed before transmission.

Step 4: generate a transport Character Set
First, you must put the source tablespace In the read-only State to prevent modification of the tablespace data.
SQL> alter tablespace anenjoy read only;
Next is the data export procedure (important)

Execute the import operation. The import operation should correspond to the export operation. If the export operation is exp, it needs to be imported with imp. If the export operation is expdp, it needs to be imported with impdp. For details, refer to using the official expdp and impdp
Host expdp system dumpfile = expdat. dmp directory = data_pump_dir transport_tablespaces = 'anenjoy 'logfile = tts_export.log;
You will be prompted to enter the system password. If you forget the password, you can refer to the previous article to learn how to change the password.
Tip 1: The system user is used here. If you are using a user defined by yourself, you need to create this user on the target host.
Tip 2: the path of data_pump_dir is/opt/oracle/admin/test05/dpdump, and test05 is the database name, this directory will be created after the oracle database is installed
You can run the SQL command to view the data_pump_dir path.
Select * from dba_directories;
Log output:
Job "SYSTEM". "SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 13:36:16

Step 5: copy data to the target host
Transport both the datafiles and the export (dump) file of the tablespaces to a place that is accessible to the destination database.
Scp test05.dbf oracle@192.168.1.248:/opt/oracle/oradata/test02/(to target database)
Scp-r dpdump/oracle@192.168.1.248:/home/oracle/(this is export files)
Then copy the dpdump file to the corresponding data_pump_dir of target, that is, you/opt/oracle/admin/ORALE_SID/dpdump

Step 6: import data
Impdp system dumpfile = expdat. dmp directory = data_pump_dir transport_datafiles = '/opt/oracle/oradata/test02/test05.dbf' logfile = tts_import.log
Normal log output:
Job "SYSTEM". "SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 00:41:33
Then, change the tablespace attributes on the source and target hosts.
Alter tablespace anenjoy read write; (both servers are executed)
Verification phase:
On the target, run the SQL command to check whether the tablespace anenjoy is synchronized.
SQL> select * from v $ tablespace;

TS # NAME INCLUD BIGFIL FLASHB ENCRYP
6 ANENJOY YES NOYES
View table data in the tablespace anenjoy
SQL> select * from test;

NAME WORK
--------------------------------------------------
Frank IT
Alex caiwu
Leon yanfa

Now you can insert data to the table test, or create a new data table and index in the target anenjoy tablespace.

[Oracle] data migration records between tablespaces

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.