Oracle 逻辑备份（数据迁移）

DB架构

已于 2022-12-06 21:17:44 修改

阅读量1.1k

点赞数

分类专栏： DBA 文章标签： oracle 数据库

于 2022-12-05 23:40:52 首次发布

本文链接：https://blog.csdn.net/u011868279/article/details/128195126

版权

DBA 专栏收录该内容

183 篇文章 12 订阅

订阅专栏

以逻辑结构为单位进行的备份

跨用户移动数据
跨数据库移动数据库
为测试保存原始的数据状态
对数据库进行版本升级

逻辑导出的注意事项：

exp程序在目录中发现同名文件时会直接覆盖，不提示！！
exp无法备份无段的空表
执行逻辑导出时一定要注意字符集！最好使用包含中文的小表做测试！！
执行逻辑导出时一定要注意字符集！最好使用包含中文的小表做测试！！
导入时的数据和导出时的数据一模一样，导出之后数据库中表的数据变化全都丢失！！

逻辑导出：所有版本都可用，服务器端和客户端都可用。

mkdir -p /home/oracle/expbk

exp userid=scott/tiger@pdb1 table=t01 file=/home/oralce/expbk/t01.dmp buffer=1048576 feedback=10000 log=/home/oracle/expbk/t01.log

[oracle@oracle-db-19c expbk]$ 
[oracle@oracle-db-19c expbk]$ exp userid=scott/tiger@PDB1 tables=emp file=/home/oracle/expbk/emp.dmp buffer=1048576 log=/home/oracle/expbk/emp.log

Export: Release 19.0.0.0.0 - Production on Mon Dec 5 23:34:37 2022
Version 19.3.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
Version 19.3.0.0.0
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                            EMP         14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
[oracle@oracle-db-19c expbk]$

逻辑导入：

drop table t01 purge;

imp userid=scott/tiger tables=t01 file=/home/oracle/expbk/t01.dmp buffer=1048576 feedback=10000 log=/home/oracle/expbk/t01.log

[oracle@oracle-db-19c expbk]$ 
[oracle@oracle-db-19c expbk]$ imp userid=scott/tiger@PDB1 tables=emp file=/home/oracle/expbk/emp.dmp buffer=1048576 log=/home/oracle/expbk/imp_emp.log

Import: Release 19.0.0.0.0 - Production on Tue Dec 6 18:13:14 2022
Version 19.3.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
Version 19.3.0.0.0

Export file created by EXPORT:V19.00.00 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses US7ASCII character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                          "EMP"         14 rows imported
About to enable constraints...
Import terminated successfully without warnings.
[oracle@oracle-db-19c expbk]$

导出数据时带有查询条件：

exp scott/tiger tables=emp file=/home/oracle/expbk/emp_30.dmp query=\'where deptno=30\' buffer=1000000 log=/home/oracle/expbk/emp_30.log

[oracle@oracle-db-19c expbk]$ 
[oracle@oracle-db-19c expbk]$ exp scott/tiger@PDB1 tables=emp file=/home/oracle/expbk/emp_30.dmp query=\'where deptno=30\' buffer=1000000 log=/home/oracle/expbk/emp_30.log

Export: Release 19.0.0.0.0 - Production on Tue Dec 6 18:30:18 2022
Version 19.3.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
Version 19.3.0.0.0
Export done in UTF8 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                            EMP          6 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
[oracle@oracle-db-19c expbk]$ ls -ltr
total 48
-rw-r--r--. 1 oracle oinstall   516 Dec  5 23:34 emp.log
-rw-r--r--. 1 oracle oinstall 16384 Dec  5 23:34 emp.dmp
-rw-r--r--. 1 oracle oinstall   614 Dec  6 18:13 imp_emp.log
-rw-r--r--. 1 oracle oinstall   153 Dec  6 18:21 employees.log
-rw-r--r--. 1 oracle oinstall   512 Dec  6 18:30 emp_30.log
-rw-r--r--. 1 oracle oinstall 16384 Dec  6 18:30 emp_30.dmp
[oracle@oracle-db-19c expbk]$

导入时追加数据：ignore=y

（1）删除30部门的数据。

SQL> 
SQL> delete emp where deptno=30;

6 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 24-JAN-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7876 ADAMS      CLERK           7788 02-APR-87       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

8 rows selected.

（2）导入备份数据到表中

[oracle@oracle-db-19c expbk]$ 
[oracle@oracle-db-19c expbk]$ imp scott/tiger@PDB1 tables=emp file=/home/oracle/expbk/emp_30.dmp ignore=y buffer=100000 log=/home/oracle/expbk/imp_emp_30.log

Import: Release 19.0.0.0.0 - Production on Tue Dec 6 18:33:35 2022
Version 19.3.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
Version 19.3.0.0.0

Export file created by EXPORT:V19.00.00 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                          "EMP"          6 rows imported
About to enable constraints...
Import terminated successfully without warnings.
[oracle@oracle-db-19c expbk]$

（3）查询数据

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 24-JAN-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7876 ADAMS      CLERK           7788 02-APR-87       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL>

闪回导出：只有system可以调用

exp system/system@PDB1 tables=scott.emp file=/home/oracle/expbk/emp_1010.dmp buffer=1000000 flashback_time=\"to_timestamp\(\'2022-12-06 19:00:00\',\'yyyy-mm-dd hh24:mi:ss'\)\" log=/home/oracle/expbk/emp_1010.log

[oracle@oracle-db-19c expbk]$ 
[oracle@oracle-db-19c expbk]$ exp system/system@PDB1 tables=scott.e03 file=/home/oracle/expbk/e03_1930.dmp buffer=1000000 flashback_time=\"to_timestamp\(\'2022-12-06 19:30:00\',\'yyyy-mm-dd hh24:mi:ss\'\)\" log=/home/oracle/expbk/e03_1930.log

Export: Release 19.0.0.0.0 - Production on Tue Dec 6 19:47:12 2022
Version 19.3.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
Version 19.3.0.0.0
Export done in UTF8 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table                            E03         14 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
[oracle@oracle-db-19c expbk]$

导入数据：

[oracle@oracle-db-19c expbk]$ imp system/system@PDB1 file=/home/oracle/expbk/e03_1930.dmp full=y ignore=y

Import: Release 19.0.0.0.0 - Production on Tue Dec 6 19:49:06 2022
Version 19.3.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
Version 19.3.0.0.0

Export file created by EXPORT:V19.00.00 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
IMP-00403: 

Warning: This import generated a separate SQL file "import_sys" which contains DDL that failed due to a privilege issue.

. importing SYSTEM's objects into SYSTEM
. importing SCOTT's objects into SCOTT
. . importing table                          "E03"         14 rows imported
Import terminated successfully with warnings.
[oracle@oracle-db-19c expbk]$

只导出表结构（元数据）不导出数据：备份模型，不备份数据

exp scott/tiger@PDB1 tables=ob1 rows=n file=/home/oracle/expbk/ob1_metadata.dmp log=/home/oracle/expbk/ob1_metadata.log

select object_name,created from user_objects;

vi /home/oracle/expbk/exp.sh

export ORACLE_HOME = $ORACLE_BASE/product/19.3.0/dbhome_1
export ORACLE_SID = PDB1
export LANG=en_US.UTF-8
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
name=`date '+%Y%m%d_%H%M%s'`
`$ORACLE_HOME/bin/exp userid=scott/tiger@PDB1 tables=t01 file=/home/oracle/expbk/t01\_$name.dmp buffer=1048576 feedback=10000 log=/home/oracle/expbk/t01\_$name.log`

chmod +x /home/oracle/expbk/exp.sh

[oracle@oracle-db-19c expbk]$ 
[oracle@oracle-db-19c expbk]$ sh -x exp.sh   
+ export ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1
exp.sh: line 2: export: `=': not a valid identifier
exp.sh: line 2: export: `/u01/app/oracle/product/19.3.0/dbhome_1': not a valid identifier
+ export ORACLE_SID = PDB1
exp.sh: line 3: export: `=': not a valid identifier
+ export LANG=en_US.UTF-8
+ LANG=en_US.UTF-8
+ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
+ NLS_LANG=AMERICAN_AMERICA.AL32UTF8
++ date +%Y%m%d_%H%M%S
+ name=20221206_20191670329163
++ /u01/app/oracle/product/19.3.0/dbhome_1/bin/exp userid=scott/tiger@PDB1 tables=t01 file=/home/oracle/expbk/t01_20221206_20191670329163.dmp buffer=1048576 feedback=10000 log=/home/oracle/expbk/t01_20221206_20191670329163.log

Export: Release 19.0.0.0.0 - Production on Tue Dec 6 20:19:23 2022
Version 19.3.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
Version 19.3.0.0.0
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                            T01
                                                           14 rows exported
Export terminated successfully without warnings.
[oracle@oracle-db-19c expbk]$ cat exp.sh

export ORACLE_HOME=$ORACLE_BASE/product/19.3.0/dbhome_1
export ORACLE_SID=PDB1
export LANG=en_US.UTF-8
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
name=`date '+%Y%m%d_%H%M%s'`
`$ORACLE_HOME/bin/exp userid=scott/tiger@PDB1 tables=t01 file=/home/oracle/expbk/t01\_$name.dmp buffer=1048576 feedback=10000 log=/home/oracle/expbk/t01\_$name.log`
[oracle@oracle-db-19c expbk]$

[oracle@oracle-db-19c expbk]$ 
[oracle@oracle-db-19c expbk]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@oracle-db-19c expbk]$ echo $ORACLE_BASE
/u01/app/oracle
[oracle@oracle-db-19c expbk]$

导出用户：

exp userid=scott/tiger@PDB1 owner=scott file=/home/oracle/expbk/scott.dmp buffer=1048576 feedback=10000 log=/home/oracle/expbk/scott.log

[oracle@oracle-db-19c expbk]$ 
[oracle@oracle-db-19c expbk]$ 
[oracle@oracle-db-19c expbk]$ exp userid=scott/tiger@PDB1 owner=scott file=/home/oracle/expbk/scott.dmp buffer=1048576 feedback=10000 log=/home/oracle/expbk/scott.log

Export: Release 19.0.0.0.0 - Production on Tue Dec 6 20:29:06 2022
Version 19.3.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
Version 19.3.0.0.0
Export done in UTF8 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT 
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS
                                                            0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                           DEPT
                                                            4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                            E01
                                                           13 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                            E03
                                                           14 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                            EMP
                                                           14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                    EMP_AGGR_MV
                                                            3 rows exported
. . exporting table                       SALGRADE
                                                            5 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                            T01
                                                           14 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                           TAB1
                                                            0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                           TAB2
                                                            0 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
[oracle@oracle-db-19c expbk]$

先删除SCOTT用户：

SQL> 
SQL> show user
USER is "SYS"
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> 
SQL> drop user scott cascade;
drop user scott cascade
          *
ERROR at line 1:
ORA-01918: user 'SCOTT' does not exist


SQL> 
SQL> alter session set container=PDB1;

Session altered.

SQL> drop user scott cascade;

User dropped.

SQL> 
SQL> 
SQL> 
SQL> grant connect,resource to scott identified by tiger;

Grant succeeded.

SQL> conn scott/tiger@PDB1;
Connected.
SQL> select * from emp;
select * from emp
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

导入用户：

imp userid=scott/tiger full=y file=/home/oracle/expbk/scott.dmp buffer=1048576 feedback=10000 log=/home/oracle/expbk/impscott.log

跨用户导入数据： scott ---> tom

imp userid=system/system@PDB1 file=scott.dmp fromuser=scott touser=tom tables=dept,emp,salgrade buffer=1000000 log=imptom.log

使用主机管道压缩备份文件：

mknod /home/oracle/expbk/exp_pipe p

exp userid=scott/tiger@PDB1 owner=scott log=/home/oracle/expk/scott.log file=/home/oracle/expbk/exp_pipe & gzip </home/oracle/expbk/exp_pipe> scott.dmp.gz

[oracle@oracle-db-19c expbk]$ exp userid=scott/tiger@PDB1 owner=scott log=/home/oracle/expbk/scott.log file=/home/oracle/expbk/exp_pipe & gzip </home/oracle/expbk/exp_pipe> scott.dmp.gz
[1] 87055
-bash: /home/oracle/expbk/exp_pipe: No such file or directory
[oracle@oracle-db-19c expbk]$ 
Export: Release 19.0.0.0.0 - Production on Tue Dec 6 21:13:04 2022
Version 19.3.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
Version 19.3.0.0.0
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT 
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS          0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                           DEPT          4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                            EMP         14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                    EMP_AGGR_MV          3 rows exported
. . exporting table                       SALGRADE          5 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                            T01          0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                           TAB1          0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                           TAB2          0 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

[1]+  Done                    exp userid=scott/tiger@PDB1 owner=scott log=/home/oracle/expbk/scott.log file=/home/oracle/expbk/exp_pipe
[oracle@oracle-db-19c expbk]$

导入用户数据时，要在数据库中将用户创建好。

imp scott/tiger@PDB1 file=scott full=y buffer=10000000 log=/home/oracle/expbk/imp_scott.log

导出表空间：

exp system/system@PDB1 tablespaces=tbs1 file=/home/oracle/expbk/exp_tbs1.dmp buffer=10000000