记一次oracle数据库备份恢复(linux to win)_linux还原oracle数据库-优快云博客

本文介绍了在Linux和Windows环境下，如何使用Oracle工具如expdp和impdp进行数据库备份和恢复，包括创建新数据库、用户权限分配，以及对表空间的操作，如查询和创建表空间，以及导入DMP文件的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.登录linux系统，su - oracle

备份

expdp system/password directory=DATA_PUMP_DIR dumpfile=20220914.dmp logfile=20230804.log schemas=zs

2.登录win系统

创建新数据库系统，创建用户user zs 并赋予权限

3.查询原表的表空间，并在win中创建表空间

SELECT tablespace_name, file_name
FROM dba_data_files;

查询结果如下

1	USERS	/u01/app/oradata/ncdb/users01.dbf	0
2	UNDOTBS1	/u01/app/oradata/ncdb/undotbs01.dbf	32
3	SYSAUX	/u01/app/oradata/ncdb/sysaux01.dbf	23.68
4	SYSTEM	/u01/app/oradata/ncdb/system01.dbf	16.41
5	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0101.dbf	20
6	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0102.dbf	20
7	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0103.dbf	20
8	NC_zs1_DATA_01	/u01/app/oradata/ncdb/nc_zs1_data_0101.dbf	17.21
9	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0120.dbf	20
10	NC_ISM_DATA_01	/u01/app/oradata/ncdb/nc_ism_data_0101.dbf	2
11	NC_MA_DATA_01	/u01/app/oradata/ncdb/nc_ma_data_0101.dbf	2
12	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0104.dbf	20
13	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0105.dbf	20
14	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0106.dbf	20
15	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0107.dbf	20
16	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0108.dbf	20
17	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0109.dbf	20
18	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0110.dbf	20
19	NNC_INDEX01	/u01/app/oradata/ncdb/NNC_INDEX01_01.dbf	20
20	NNC_INDEX01	/u01/app/oradata/ncdb/NNC_INDEX01_02.dbf	20
21	NNC_INDEX01	/u01/app/oradata/ncdb/NNC_INDEX01_03.dbf	20
22	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0121.dbf	20
23	NNC_INDEX01	/u01/app/oradata/ncdb/NNC_INDEX01_04.dbf	20
24	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0111.dbf	20
25	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0112.dbf	20
26	NNC_INDEX01	/u01/app/oradata/ncdb/NNC_INDEX01_05.dbf	20
27	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0113.dbf	20
28	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0114.dbf	20
29	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0115.dbf	20
30	NNC_INDEX01	/u01/app/oradata/ncdb/NNC_INDEX01_06.dbf	20
31	NNC_INDEX01	/u01/app/oradata/ncdb/NNC_INDEX01_07.dbf	20
32	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0116.dbf	20
33	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0117.dbf	20
34	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0118.dbf	20
35	NNC_INDEX01	/u01/app/oradata/ncdb/NNC_INDEX01_08.dbf	20
36	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0119.dbf	20
37	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0122.dbf	20
38	NNC_INDEX01	/u01/app/oradata/ncdb/NNC_INDEX01_09.dbf	20
39	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0123.dbf	20
40	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0124.dbf	20
41	NNC_INDEX01	/u01/app/oradata/ncdb/NNC_INDEX01_10.dbf	20
42	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0125.dbf	10
43	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0126.dbf	10
44	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0127.dbf	10
45	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0128.dbf	10
46	NC_zs_DATA_01	/u01/app/oradata/ncdb/nc_zs_data_0129.dbf	10
47	NC_zs1_DATA_01	/u01/app/oradata/ncdb/nc_zs1_data_0102.dbf	10
48	NNC_INDEX01	/u01/app/oradata/ncdb/NNC_INDEX01_11.dbf	10

plsql链接win数据库，创建表空间

-- 创建表空间 NC_ZS_DATA_01
CREATE TABLESPACE NC_ZS_DATA_01 DATAFILE 'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0101.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0102.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0103.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0104.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0105.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0106.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0107.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0108.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0109.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0110.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0111.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0112.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0113.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0114.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0115.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0116.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0117.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0118.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0119.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0120.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0121.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0122.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0123.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0124.dbf' SIZE 20G ,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0125.dbf' SIZE 10G AUTOEXTEND ON MAXSIZE 20G,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0126.dbf' SIZE 10G AUTOEXTEND ON MAXSIZE 20G,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0127.dbf' SIZE 10G AUTOEXTEND ON MAXSIZE 20G,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0128.dbf' SIZE 10G AUTOEXTEND ON MAXSIZE 20G,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0129.dbf' SIZE 10G AUTOEXTEND ON MAXSIZE 20G,
                                'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0130.dbf' SIZE 10G AUTOEXTEND ON MAXSIZE 20G;

-- 创建表空间 NC_ISM_DATA_01
CREATE TABLESPACE NC_ISM_DATA_01 DATAFILE 'D:\app\Administrator\oradata\ncdbtest\nc_ism_data_0101.dbf' SIZE 2G AUTOEXTEND ON MAXSIZE 20G;

-- 创建表空间 NC_MA_DATA_01
CREATE TABLESPACE NC_MA_DATA_01 DATAFILE 'D:\app\Administrator\oradata\ncdbtest\nc_ma_data_0101.dbf' SIZE 2G AUTOEXTEND ON MAXSIZE 20G;

-- 创建表空间 NC_ZS1_DATA_01
CREATE TABLESPACE NC_ZS1_DATA_01 DATAFILE 'D:\app\Administrator\oradata\ncdbtest\nc_ZS1_data_0101.dbf' SIZE 17.21G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NC_ZS1_DATA_01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\nc_ZS1_data_0102.dbf' SIZE 10G AUTOEXTEND ON MAXSIZE 20G;

-- 创建表空间 NNC_INDEX01
CREATE TABLESPACE NNC_INDEX01 DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_01.dbf' SIZE 20G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_02.dbf' SIZE 20G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_03.dbf' SIZE 20G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_04.dbf' SIZE 20G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_05.dbf' SIZE 20G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_06.dbf' SIZE 20G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_07.dbf' SIZE 20G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_08.dbf' SIZE 20G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_09.dbf' SIZE 20G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_10.dbf' SIZE 20G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_11.dbf' SIZE 10G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_12.dbf' SIZE 10G AUTOEXTEND ON MAXSIZE 20G;

4.导入dmp文件

impdp system/passwodr directory=DATA_PUMP_DIR dumpfile=20230804.dmp logfile=import20230804.log