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

This article mainly introduces "how to migrate Oracle partial data in the form of cold copy tablespaces". In daily operation, I believe many people have doubts about how to migrate Oracle partial data in the form of cold copy tablespaces. I have consulted all kinds of materials and sorted out simple and easy operation methods. I hope to help you answer the doubts about "how to migrate Oracle partial data in the form of cold copy tablespaces"! Next, please follow the small series to learn together!

Requirements: A dual-active replication system for users. The host is the production node and the backup is the backup node. However, there are users and data established by other applications in the backup. It is required to fully synchronize the data of the standby machine once, migrate the data of several users of the host machine to the standby machine, keep the rowid consistent, and do not allow downtime. It is not allowed to activate other users of the standby machine.

Limitations: 1. The primary and standby environments are consistent (operating system version, database version, installation path, character set, data block size, etc.)

Host tablespaces need to be read-only during exp.

Method steps:

1. First check the table space for self-inclusion on the host

SQL> EXECUTE SYS.DBMS_TTS.TRANSPORT_SET_CHECK('USERS',TRUE);

PL/SQL procedure successfully completed.

view results

SQL> select * from sys.transport_set_violations;

no rows selected

If there is no data, the table space is self-contained. If there is data, it means it is not self-contained and needs to be processed. However, if the associated table space is also within the migration scope, it is OK not to self-contain.

2. Query the table space where the host user is located

select username,default_tablespace from dba_users where username='xxx';

3. Query datafiles in table space

select file_name from dba_data_files where tablespace_name = 'xxx';

4. Take the host table space offline

alter tablespace XXX read only;

5. Take the current SCN of the host as the breakpoint (used by the active-active replication system).

select to_char(current_scn,'XXXXXXXXXXXX') from v$database;

6. Execute exp script

exp transport_tablespace=y tablespaces=xxx,yyy,zzz file=e:\0711\45.dmp

7. Copy the file exported by exp and the datafile file corresponding to the table space to the standby machine (at this time, the table space of the host machine can already be read and written)

8, standby machine delete table space and data files

drop tablespace XXX including contents;

If there is no corresponding user for the standby machine, you need to create it.

9. Overwrite the datafile to the same path of the standby machine

10. Execute imp script

imp transport_tablespace=y datafiles='D:\app\Administrator\oradata\xxx','D:\app\Administrator\oradata\xxx','D:\app\Administrator\oradata\xxx','D:\app\Administrator\oradata\xxx','D:\app\Administrator\oradata\xxx','D:\app\Administrator\oradata\xxx','D:\app\Administrator\oradata\xxx','D:\app\Administrator\oradata\xxx','D:\app\Administrator\oradata\xxx' tablespaces=xxx,yyy,zzz file=E:\0711\45.dmp

11. Finally, remember to restore the standby table space to the read write state

alter tablespace XXXX read write

12. Related processing of other applications

At this point, the study of "how to migrate Oracle partial data in the form of cold copy table space" is over, hoping to solve everyone's doubts. Theory and practice can better match to help you learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

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.