Linux 下 Oracle 文件迁移方案
文章目录
- Linux 下 Oracle 文件迁移方案
- 机器准备
- Oracle 11g 安装
-
- 源 Oracle 数据准备
- 进入 oracle 用户
- 查看监听器状态
- sysadmin 连接
- 创建表空间
- 创建用户
- 用户授权
- 写入数据
- 查看表信息
- Oracle 文件迁移
-
- Oracle 启动
-
- 验证结果
-
- 参考
机器准备
ip：10.10.200.84
hostname：node1
desc：迁移前机器
ip：10.10.200.49
hostname：node2
desc：迁移后机器
Oracle 11g 安装
node1
安装目录：/home/data/v1.3/oracle
[root@node1 ~]# tree /home/data/v1.3/oracle/ -L 2
/home/data/v1.3/oracle/
├── admin
│ └── orcl
├── cfgtoollogs
│ ├── dbca
│ └── netca
├── checkpoints
├── diag
│ ├── rdbms
│ └── tnslsnr
├── fast_recovery_area
├── flash_recovery_area
│ ├── orcl
│ └── ORCL
├── inventory
│ ├── ContentsXML
│ ├── install.platform
│ ├── logs
│ ├── oraInstaller.properties
│ ├── oraInst.loc
│ ├── orainstRoot.sh
│ └── oui
├── oradata
│ └── orcl
└── product
└── 11.2.0
21 directories, 4 files
node2
安装目录：/data/v1.3/oracle
[root@node2 ~]# tree /data/v1.3/oracle/ -L 2
/data/v1.3/oracle/
├── admin
│ └── orcl
├── cfgtoollogs
│ ├── dbca
│ └── netca
├── checkpoints
├── diag
│ ├── rdbms
│ └── tnslsnr
├── fast_recovery_area
├── flash_recovery_area
│ ├── orcl
│ └── ORCL
├── inventory
│ ├── ContentsXML
│ ├── install.platform
│ ├── logs
│ ├── oraInstaller.properties
│ ├── oraInst.loc
│ ├── orainstRoot.sh
│ └── oui
├── oradata
│ └── orcl
└── product
└── 11.2.0
21 directories, 4 files
源 Oracle 数据准备
进入 oracle 用户
[root@node1 ~]# su - oracle
Last login: Wed Oct 20 14:39:42 CST 2021 on pts/0
查看监听器状态
[oracle@node1 ~]$ lsnrctl status
sysadmin 连接
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 20 14:42:51 2021
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
创建表空间
# 目录 -- /home/data/v1.3/oracle
SQL> CREATE TABLESPACE test_oracle_move DATAFILE '/home/data/v1.3/oracle/oradata/orcl/test_oracle_move.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;
Tablespace created.
创建用户
# 用户创建 -- jhemr/xxxxx
SQL> CREATE USER jhemr IDENTIFIED BY xxxxx DEFAULT TABLESPACE test_oracle_move;
User created.
用户授权
# 授权 -- 最大（仅限临时测试）
SQL> GRANT CONNECT, RESOURCE, DBA TO jhemr;
Grant succeeded.
写入数据
# 通过 select 创建
SQL> create table copy_emp as select * from scott.emp;
Table created.
查看表信息
# 查看数据量
SQL> select count(*) from copy_emp;
COUNT(*)
----------
14
# 查看表结构
SQL> desc copy_emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
Oracle 文件迁移
准备
# 连接
[oracle@node1 ~]$ sqlplus / as sysdba
# 关闭 oralce
SQL> shutdown immediate;
[oracle@node1 ~]$ lsnrctl stop
# 连接
[oracle@node2 ~]$ sqlplus / as sysdba
# 关闭 oralce
SQL> shutdown immediate;
[oracle@node2 ~]$ lsnrctl stop
文件拷贝
# 备份原有目录
[oracle@node2 ~]$ mv /data/v1.3/oracle /data/v1.3/oracle_bak
# 拷贝整个目录
[root@node1 ~]# scp -r /home/data/v1.3/oracle root@10.10.200.49:/data/v1.3/oracle
node2 配置
# node1 scp 使用 root -- 需改成 oracle 启动权限
[root@node2 ~]# chown -R oracle.oinstall /data/v1.3/oracle
# 更改前 -- node1
[root@node2 ~]# cat /data/v1.3/oracle/product/11.2.0/network/admin/listener.ora | grep node
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
# 更改后 -- node2
[root@node2 ~]# cat /data/v1.3/oracle/product/11.2.0/network/admin/listener.ora | grep node
(ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
Oracle 启动
启动监听
[root@node2 ~]# su - oracle
Last login: Wed Oct 20 15:25:55 CST 2021 on pts/0
[oracle@node2 ~]$ lsnrctl start
连接 oracle
[oracle@node2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 20 15:29:50 2021
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
启动 oracle
SQL> startup
ORACLE instance started.
Total System Global Area 1720328192 bytes
Fixed Size 2214056 bytes
Variable Size 1006634840 bytes
Database Buffers 704643072 bytes
Redo Buffers 6836224 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5:
'/home/data/v1.3/oracle/oradata/orcl/test_oracle_move.dbf'
SQL> alter database rename file '/home/data/v1.3/oracle/oradata/orcl/test_oracle_move.dbf' to '/data/v1.3/oracle/oradata/orcl/test_oracle_move.dbf';
Database altered.
SQL> alter database open
Database altered.
验证结果
连接
[oracle@node2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 20 15:44:00 2021
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
查看表信息
[oracle@node2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 20 15:44:00 2021
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select count(*) from copy_emp;
COUNT(*)
----------
14
SQL> desc copy_emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
查看用户
SQL> select * from all_users where USERNAME='JHEMR';
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
JHEMR 85 20-OCT-21
参考
- Oracle 数据文件迁移 - update_ - 博客园 (cnblogs.com)
