03-002oracle数据库迁移

一、说明

注意点：登录secureCRT时，修改session字符集为gb2312或者default,中文字符不会显示为乱码
$

SQL>

###:表示注释掉的命令

二、数据导出

1、创建备份目录（可用plsql或者sqlplus）
$ mkdir /home/oracle/dhmp_web_to_test
登入数据库管理员，执行如下命令：
$ sqlplus / as sysdba
SQL> create or replace directory dump_dir as ‘/home/oracle/dhmp_web_to_test’;

2、查询dump_dir目录是否创建成功
登入数据库管理员，执行如下命令：
$ sqlplus / as sysdba
SQL> select * from dba_directories;

3、进入数据库创建对应目录且该目录需要oracle有对应权限，备份
$ expdp user1/pw1 directory=dump_dir dumpfile=dhmptest1.dmp

#如果迁移的目的库和原始库版本不一样，需要导出原始库的时候，需要加上目的库的版本，具体选项为 version = ***
oracle -> 12c @jq-db-001:/home/oracle$ expdp 用户名/密码 directory=dump_dir dumpfile=dhmp_ams.dmp logfile=dhmp_ams.log schemas=dhmp_ams version=11.2.0.4.0
查看版本语句：select * from v$version;
###user1/pw1 数据库用户名密码
###directory=dump_dir 备份目录
###dumpfile=dhmptest1.dmp 备份名称

###4、删除数据库中创建的目录
###登入数据库管理员，执行下面命令：
###$sqlplus / as sysdba
###SQL>drop directory dump_dir;

三、 数据导入

1、创建备份目录
$ mkdir /home/oracle/backup
登入数据库管理员，执行下面命令：
$ sqlplus / as sysdba
SQL> create or replace directory dump_dir1 as ‘/home/oracle/backup’;

###把dhmptest1.dmp放到此目录下

2、查询dump_dir1目录是否创建成功
登入数据库管理员，执行如下命令：
$ sqlplus / as sysdba
SQL> select * from dba_directories;

3、 创建web测试数据库表空间和用户以及用户授权
1) 创建web测试数据库表空间和用户以及用户授权：
创建表空间：
$ sqlplus / as sysdba

###SQL>create tablespace TBS_TNMSPON datafile ‘/home/oracle/oradata/ora112/tbs_tnmspon_101.dbf’ size 4g autoextend off; #目录可以放到其它目录
###SQL>drop tablespace TBS_TNMSPON including contents and datafiles;
上述只有4G，导致报错显示Oracle：ORA-00604: 递归 SQL 级别 1 出现错误，128*******

SQL> create tablespace AMSSHORCL_1 datafile ‘/oradata2/ora112/AMSSHORCL_1.dbf’ size 8g autoextend off;
SQL> create user user2 identified by pw2;
SQL> grant resource,connect,dba to user2;

###tablespace表示表空间
###datefile表示数据文件存放位置
###autoextend 磁盘初始为8G，数据库文件自动文件增长功能 关闭

4、使用impdp导入正式环境数据

$ impdp user2/pw2@ora112 directory=dump_dir1 dumpfile=dhmptest1.dmp REMAP_SCHEMA=user1:user2 EXCLUDE=USER remap_tablespace=DHMP_AMS:AMSSHORCL_1

###导入库的用户名密码为user2 ,密码为pw2
###导入目录为dump_dir1
###导入文件名为dhmptest1.dmp
###schema用户映射从user1映射为user2，因为源库为用户user1,要导入的用户为user2
###映射表空间从DHMP_AMS到表空间AMSSHORCL_1
####imp ams_sh_wl/ams_sh_wl file="/home/oracle/backup/shwl.dmp" full=y ignore=y

[oracle@HXQ-CMS-TEST ~]$ echo $ORACLE_SID
ora112

[oracle@HXQ-CMS-TEST ~]$ sqlplus amsshorcl/amsshorcl@ora112

[oracle@HXQ-CMS-DB01 ~]$ echo $ORACLE_HOME
/home/oracle/product/ora112
[oracle@HXQ-CMS-DB01 ~]$ sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.1.0 Production on 星期一 3月 18 12:41:25 2019

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

---

从一个用户expdp导出再impdp导入到另一个用户
如果想导入的用户已经存在:

1. 导出用户 expdp user1/pass1 directory=dumpdir dumpfile=user1.dmp
2. 导入用户 impdp user2/pass2 directory=dumpdir dumpfile=user1.dmp REMAP_SCHEMA=user1:user2 EXCLUDE=USER

如果想导入的用户不存在:

1. 导出用户 expdp user1/pass1 directory=dumpdir dumpfile=user1.dmp
2. 导入用户 impdp system/passsystem directory=dumpdir dumpfile=user1.dmp REMAP_SCHEMA=user1:user2
3. user2会自动建立，其权限和使用的表空间与user1相同，但此时用user2无法登录，必须修改user2的密码

查看表空间
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name FROM dba_free_space GROUP BY tablespace_name;

导入前需要先删除原来的表空间和用户，如果之前没创建过该表空间，则忽略此步骤
drop tablespace TBS_TNMSPON including contents and datafiles;
drop user JEECG_TEST cascade;

到此结束

---

以下为参考

1、创建备份目录
$mkdir /home/oracle/dhmp_web_to_test #该备份目录已经创建请忽略此步
登入数据库管理员，执行下面命令：
sqlplus / as sysdba
SQL>create or replace directory dump_dir as ‘/home/oracle/dhmp_web_to_test’;

2、查询backup目录是否创建成功
登入数据库管理员，执行下面命令：
sqlplus / as sysdba
SQL>select * from dba_directories;

3、数据库数据备份
1)正式库数据库备份
$expdp tnmspon/CmccTnms#999 directory=dump_dir dumpfile=tnmspon.dmp logfile=tnmspon.log schemas=tnmspon
去掉参数 schemas=tnmspon，否则会报错

4、删除数据库中创建的备份目录
登入数据库管理员，执行下面命令：
sqlplus / as sysdba
SQL>drop directory dump_dir;

1、创建备份目录
$mkdir /oradata02/backup #该备份目录已经创建请忽略此步
登入数据库管理员，执行下面命令：
$sqlplus / as sysdba
SQL>create or replace directory backup as ‘/oradata02/backup’;
把*tnmspon.dmp放到/oradata02/backup下

2.、查询backup目录是否创建成功
登入数据库管理员，执行下面命令：
$sqlplus / as sysdba
SQL>select * from dba_directories;

3、创建表空间和用户以及用户授权
1) 创建表空间和用户以及用户授权：
创建表空间：
$sqlplus / as sysdba
SQL>create tablespace TBS_TNMSPON datafile’ /oradata01/shddzj/tbs_tnmspon/tbs_tnmspon_101.dbf’ size 30g autoextend off;
注：已经创建了10个dbf文件，从tbs_tnmspon_101.dbf至tbs_tnmspon_110.dbf，TBS_TNMSPON表空间共300G空间。

创建用户和用户授权
SQL>create user tnmspon11 identified by CmccTnms#999;
SQL>grant resource,connect,dba to tnmspon;
注：已经创建该用户，但只授权了connect，需要对该用户再授权resource和dba权限。

4、使用impdp导入数据
1)数据导入命令：
$impdp tnmspon11/CmccTnms#999@shddzj directory=backup dumpfile=tnmspon.dmp logfile=tnmspon.log logtime=all table_exists_action=replace schemas=tnmspon transform=disable_archive_logging:y remap_tablespace=DHMP_AMS:AMSSHORCL_1

删除表空间
alter database datafile ‘/home/oracle/oradata/ora113/AMSSHORCL_test.dbf’ offline drop;
drop tablespace AMSSHORCL_1 including contents and datafiles;
参考：

使用impdp实现数据在不同用户、不同实例之间快速复制
http://blog.itpub.net/17203031/viewspace-694740/

通过数据泵expdp、impdp方式备份与还原（导出与导入）Oracle数据库
https://blog.csdn.net/qq_26230421/article/details/79382013

Oracle 查看表空间的大小及使用情况sql语句
http://blog.itpub.net/30246303/viewspace-2138852/
https://www.cnblogs.com/vmkash/p/6524793.html

---

oracle查询表空间大小以及每个表所占空间的大小

1、查询数据库中所有的表空间以及表空间所占空间的大小，直接执行语句就可以了：

select tablespace_name, sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

2、查看表空间物理文件的名称及大小
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;

3、查询所有表空间以及每个表空间的大小，已用空间，剩余空间，使用率和空闲率，直接执行语句就可以了：

select a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as “FREE%”, substr((total - free)/total * 100, 1, 5) as “USED%” from
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name;

4、查询某个具体的表所占空间的大小，把“TABLE_NAME”换成具体要查询的表的名称就可以了：

select t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) “占用空间(M)”
from dba_segments t
where t.segment_type=‘TABLE’
and t.segment_name=‘TABLE_NAME’
group by OWNER, t.segment_name, t.segment_type;