目标:将80数据库导入到245
整个步骤:
1.从80上导出数据库为文件epsiii.dmp
[oracle@localhost ~]$ exp system/blcgebnew owner=epsiii direct=y constraints=y indexes=y file=dump/epsiii.dmp
2.245上取80的文件(传输文件)
首先生成密匙在80和245之间,这样执行scp的时候就不用再输入用户名和密码
(1)在245上执行:
[oracle@localhost ~]$ ssh-keygen -b 1024 -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase): <-- 直接输入回车
Enter same passphrase again: <-- 直接输入回车
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
49:9c:8a:8f:bc:19:5e:8c:c0:10:d3:15:60:a3:32:1c oracle@245
(2)将公钥证书id_rsa.pub复制到机器80的oracle目录的.ssh子目录中,同时将文件名更换为authorized_keys。
[oracle@outer_proj ~]$ scp -p .ssh/id_rsa.pub oracle@***.**.**.80:/home/oracle/.ssh/authorized_keys
oracle@***.**.**.80's password: <-- 输入机器Server的oracle用户密码
id_rsa.pub 100% |**************************| 218 00:00
以上的只在第一次执行,之后都可以不用输入用户和密码进行传输了
[oracle@outer_proj ~]$ scp oracle@**.**.**.80:/home/oracle/dump/epsiii.dmp /opt/dump
3.245上执行对数据库操作的脚本
以下实现了连接数据库以及sql语句操作,最重要的地方是一次执行完毕,不用在执行期间输入用户和密码,不是交互式的.是写这个的重点和难点.
[oracle@outer_proj bin]$ vi sql.sh
#!/bin/sh
sqlplus /nolog <<EOF
conn /as sysdba;
drop user ebnewmt cascade;
create user EBNEWMT identified by "ebnewmt123" default tablespace EBNEWMT temporary tablespace TEMP profile DEFAULT;
grant connect to EBNEWMT;
grant resource to EBNEWMT;
grant unlimited tablespace to EBNEWMT;
exit;
EOF
[oracle@outer_proj bin]$ chmod 755 sql.sh//最后记得给脚本执行的权限
(执行此操作时注意要断开一切与数据库的连接才可以,包括tomcat关闭)
4.245上将epsiii.dmp导入数据库
[oracle@outer_proj ~]$ cd /opt/dump
[oracle@outer_proj dump]$imp system/123456 fromuser=epsiii touser=ebnewmt grants=y indexes=y constraints=y file=epsiii.dmp