CNDBA社区
1.单实例环境
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/users01.dbf
/u01/app/oracle/oradata/ORCL/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/system01.dbf
/u01/app/oracle/oradata/ORCL/sysaux01.dbf
/oradata/ORCL/ogg01.dbf
2.创建单实例测试对象
SQL> create tablespace tbs01 datafile '/oradata/ORCL/tbs01.dbf' size 1024m;
Tablespace created.
SQL> create user c##hbhe0316 identified by wwwwww default tablespace tbs01 temporary tablespace temp;
User created.
SQL> grant dba to c##hbhe0316;
Grant succeeded.
SQL> grant connect to c##hbhe0316;
Grant succeeded.
SQL> grant resource to c##hbhe0316;
Grant succeeded.
SQL> conn c##hbhe0316/wwwwww
Connected.
SQL> create table t1(id ,name,owner,type) as select object_id , object_name,owner,object_type from dba_objects;
Table created.
SQL> commit;
Commit complete.
3.用数据泵导出c##hbhe0316的表空间
SQL> conn / as sysdba
Connected.
SQL> create directory backup as '/oradata/backup';
Directory created.
SQL> grant read,write on directory backup to c##hbhe0316;
Grant succeeded.
SQL> select owner,directory_name,directory_path from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- -------------- -------------------
SYS BACKUP /oradata/backup
4.导出c##hbhe0316 schema
[oracle@db01 oradata]$ expdp c##hbhe0316/wwwwww directory=backup dumpfile=tbs01.dmp schemas=c##hbhe0316 logfile=tbs01.log
Export: Release 19.0.0.0.0 - Production on Sun Oct 3 10:43:14 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
FLASHBACK automatically enabled to preserve database integrity.
Starting "C##HBHE0316"."SYS_EXPORT_SCHEMA_01": c##hbhe0316/******** directory=backup dumpfile=tbs01.dmp schemas=c##hbhe0316 logfile=tbs01.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
. . exported "C##HBHE0316"."T1" 3.973 MB 72710 rows
Master table "C##HBHE0316"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for C##HBHE0316.SYS_EXPORT_SCHEMA_01 is:
/oradata/backup/tbs01.dmp
Job "C##HBHE0316"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Oct 3 10:44:29 2021 elapsed 0 00:01:09
5.在RAC端创建相关数据
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/CXMTDB/DATAFILE/system.257.1083760203
+DATA/CXMTDB/DATAFILE/sysaux.258.1083760247
+DATA/CXMTDB/DATAFILE/undotbs1.259.1083760273
+DATA/CXMTDB/DATAFILE/users.260.1083760275
+DATA/CXMTDB/DATAFILE/undotbs2.269.1083761287
SQL> create tablespace tbs01 datafile '+DATA/CXMTDB/DATAFILE/tbs01.dbf' size 1024m;
Tablespace created.
SQL> create user c##hbhe0316 identified by wwwwww default tablespace tbs01 temporary tablespace temp;
User created.
SQL> grant dba to c##hbhe0316;
Grant succeeded.
SQL> grant connect to c##hbhe0316;
Grant succeeded.
SQL> grant resource to c##hbhe0316;
Grant succeeded.
创建导入的directory
SQL> create directory backup as '/home/oracle/backup';
Directory created.
SQL> grant read,write on directory backup to c##hbhe0316;
Grant succeeded.
6.将表空间文件scp至RAC环境
[oracle@db01 backup]$ scp tbs01.dmp 192.168.56.200:/home/oracle/backup
7.使用impdp命令导出数据
[oracle@rac01 backup]$ impdp c##hbhe0316/wwwwww directory=backup dumpfile=tbs01.dmp schemas=c##hbhe0316 logfile=impdp.log
Import: Release 19.0.0.0.0 - Production on Sun Oct 3 10:53:06 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
Master table "C##HBHE0316"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "C##HBHE0316"."SYS_IMPORT_SCHEMA_01": c##hbhe0316/******** directory=backup dumpfile=tbs01.dmp schemas=c##hbhe0316 logfile=impdp.log
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "C##HBHE0316"."T1" 3.973 MB 72710 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "C##HBHE0316"."SYS_IMPORT_SCHEMA_01" successfully completed at Sun Oct 3 10:54:15 2021 elapsed 0 00:00:53
版权声明：本文为博主原创文章，未经博主允许不得转载。
Linux,oracle
