记一次oracle数据备份还原的操作

其他 评论

背景

  • oracle版本：12.2.0
  • 目的：将一个用户的表结构和数据复制到另一个用户下(全量备份)
  • 当前环境为虚拟机，已提前做好快照，避免中途出现问题
  • 如果对oracle不熟，最好还是了解下用户，表空间和表之间的关系
  • 数据备份和数据还原下仅列出主要命令，调试用到的命令见其他

    数据备份

    1. 管理员登录

    1
    sqlplus sys/123456@orcl as sysdba;

    2. 创建备份目录

    1
    2
    3
    select * from dba_directories;

    create directory backup as 'd:\backup';

    3. 赋于要导出数据表所属用户权限

    1
    grant read,write on directory backup to C##USER1;

    4. 数据备份

    1
    expdp C##USER1/123456@orcl directory=backup dumpfile=backup.dmp logfile=export.log
    数据备份并没有遇到多大的阻碍，一路执行就成功了，这里贴出导出成功的日志文件：
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    ;;; 
    Export: Release 12.2.0.1.0 - Production on 星期一 10月 31 10:54:38 2022

    Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
    ;;; 
    连接到: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    启动 "C##LEGACY"."SYS_EXPORT_SCHEMA_01":  C##LEGACY/********@orcl directory=bpdata1 dumpfile=backup.dmp logfile=export.log 
    处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA
    处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    处理对象类型 SCHEMA_EXPORT/STATISTICS/MARKER
    处理对象类型 SCHEMA_EXPORT/SYSTEM_GRANT
    处理对象类型 SCHEMA_EXPORT/ROLE_GRANT
    处理对象类型 SCHEMA_EXPORT/DEFAULT_ROLE
    处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
    处理对象类型 SCHEMA_EXPORT/TABLE/TABLE
    处理对象类型 SCHEMA_EXPORT/TABLE/COMMENT
    处理对象类型 SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
    处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX
    处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
    . . 导出了 "C##LEGACY"."MSC_HUA_HISTORY"               14.58 GB 71276923 行
    . . 导出了 "C##LEGACY"."ROSHAN_CUSTOMERS_ARCHIVE"      2.877 GB 20218000 行
    . . 导出了 "C##LEGACY"."CELL_TOWERS"                   1.080 GB 16865669 行
    . . 导出了 "C##LEGACY"."NEW_CELL_IDS"                  2.318 MB   14745 行
    . . 导出了 "C##LEGACY"."MSC_DATA"                      124.4 KB     246 行
    . . 导出了 "C##LEGACY"."MCC_MNC_OPERATOR"              158.0 KB    1511 行
    . . 导出了 "C##LEGACY"."DJANGO_ADMIN_LOG"              41.49 KB     145 行
    . . 导出了 "C##LEGACY"."DJANGO_SESSION"                21.28 KB      14 行
    . . 导出了 "C##LEGACY"."AUTH_USER"                     10.00 KB       2 行
    . . 导出了 "C##LEGACY"."QUERY_TASK"                    9.156 KB       1 行
    . . 导出了 "C##LEGACY"."AUTH_PERMISSION"               8.781 KB      32 行
    . . 导出了 "C##LEGACY"."DJANGO_MIGRATIONS"             8.437 KB      25 行
    . . 导出了 "C##LEGACY"."DJANGO_CONTENT_TYPE"           6.257 KB       8 行
    . . 导出了 "C##LEGACY"."AUTH_GROUP"                        0 KB       0 行
    . . 导出了 "C##LEGACY"."AUTH_GROUP_PERMISSIONS"            0 KB       0 行
    . . 导出了 "C##LEGACY"."AUTH_USER_GROUPS"                  0 KB       0 行
    . . 导出了 "C##LEGACY"."AUTH_USER_USER_PERMISSIONS"        0 KB       0 行
    已成功加载/卸载了主表 "C##LEGACY"."SYS_EXPORT_SCHEMA_01" 
    ******************************************************************************
    C##LEGACY.SYS_EXPORT_SCHEMA_01 的转储文件集为:
      C:\BACKUP\BACKUP.DMP
    作业 "C##LEGACY"."SYS_EXPORT_SCHEMA_01" 已于 星期一 10月 31 10:56:19 2022 elapsed 0 00:01:38 成功完成

    数据还原

    注：下述sql命令均在sys账号下执行

1. 新建表空间

1
2
3
create tablespace bp
datafile 'C:/backup/bp.DBF'
size 100M autoextend on next 50M maxsize unlimited;

2. 创建用户并赋权

1
2
3
4
5
6
7
8
create user C##USER2 
identified by "123456"
default tablespace bp
profile DEFAULT
ACCOUNT UNLOCK;

grant dba to C##USER2;
grant resource to C##USER2;

3.数据还原

1
impdp C##USER2/123456@orcl directory=backup dumpfile=backup.dmp remap_schema=C##LEGACY:C##WYX logfile=import.log

若已有相同的数据表(此时会报ORA-39151错误)，则添加下述参数：
table_exists_action=replace

  • skip：跳过并处理下一个对象
  • append：为表增加数据
  • truncate：清空表，然后为其增加数据
  • replace：删除已存在表，重新建表并追加数据

本以为到此应该能够成功进行还原了，没想到报了一个数据文件无法扩展的问题，找了下资料发现oracle里一个数据文件最大为32G，故根据提示在对应的表空间新增数据文件：

1
alter tablespace "USERS" add datafile 'C:\APP\ORACLE\ORADATA\ORCL\USERS02.DBF' size 100m autoextend on next 100m maxsize unlimited;

至此，已经能成功导入还原了：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
;;; 
Import: Release 12.2.0.1.0 - Production on 星期一 10月 31 18:51:06 2022

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
;;; 
连接到: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
已成功加载/卸载了主表 "C##LEGACY"."SYS_IMPORT_FULL_01" 
ORA-39146: 方案 "C##WYX2" 不存在
启动 "C##LEGACY"."SYS_IMPORT_FULL_01":  C##WYX/********@orcl directory=bpdata1 dumpfile=backup.dmp remap_schema=C##WYX2:C##LEGACY logfile=import.log 
处理对象类型 SCHEMA_EXPORT/SYSTEM_GRANT
处理对象类型 SCHEMA_EXPORT/ROLE_GRANT
处理对象类型 SCHEMA_EXPORT/DEFAULT_ROLE
处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA
. . 导入了 "C##WYX"."MSC_HUA_HISTORY"               14.58 GB 71276923 行
. . 导入了 "C##WYX"."ROSHAN_CUSTOMERS_ARCHIVE"      2.877 GB 20218000 行
. . 导入了 "C##WYX"."CELL_TOWERS"                   1.080 GB 16865669 行
. . 导入了 "C##WYX"."NEW_CELL_IDS"                  2.318 MB   14745 行
. . 导入了 "C##WYX"."MSC_DATA"                      124.4 KB     246 行
. . 导入了 "C##WYX"."MCC_MNC_OPERATOR"              158.0 KB    1511 行
. . 导入了 "C##WYX"."DJANGO_ADMIN_LOG"              41.49 KB     145 行
. . 导入了 "C##WYX"."DJANGO_SESSION"                21.28 KB      14 行
. . 导入了 "C##WYX"."AUTH_USER"                     10.00 KB       2 行
. . 导入了 "C##WYX"."QUERY_TASK"                    9.156 KB       1 行
. . 导入了 "C##WYX"."AUTH_PERMISSION"               8.781 KB      32 行
. . 导入了 "C##WYX"."DJANGO_MIGRATIONS"             8.437 KB      25 行
. . 导入了 "C##WYX"."DJANGO_CONTENT_TYPE"           6.257 KB       8 行
. . 导入了 "C##WYX"."AUTH_GROUP"                        0 KB       0 行
. . 导入了 "C##WYX"."AUTH_GROUP_PERMISSIONS"            0 KB       0 行
. . 导入了 "C##WYX"."AUTH_USER_GROUPS"                  0 KB       0 行
. . 导入了 "C##WYX"."AUTH_USER_USER_PERMISSIONS"        0 KB       0 行
处理对象类型 SCHEMA_EXPORT/TABLE/COMMENT
处理对象类型 SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX
处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
处理对象类型 SCHEMA_EXPORT/STATISTICS/MARKER
作业 "C##LEGACY"."SYS_IMPORT_FULL_01" 已于 星期一 10月 31 19:00:59 2022 elapsed 0 00:09:49 成功完成

其他

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
select * from dba_directories;

select * from dba_tablespaces;

select * from dba_data_files;

select * from dba_users;

alter tablespace "USERS" drop datafile 23;

SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;

SELECT a.tablespace_name "表空间名", 
total "表空间大小", 
free "表空间剩余大小", 
(total - free) "表空间使用大小", 
total / (1024 * 1024 * 1024) "表空间大小(G)", 
free / (1024 * 1024 * 1024) "表空间剩余大小(G)", 
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", 
round((total - free) / total, 4) * 100 "使用率 %" 
FROM (SELECT tablespace_name, SUM(bytes) free 
FROM dba_free_space 
GROUP BY tablespace_name) a, 
(SELECT tablespace_name, SUM(bytes) total 
FROM dba_data_files 
GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name;

HAHADeveloper & Designer

个人简介