Linux 下 Oracle 文件迁移方案

Linux 下 Oracle 文件迁移方案

机器准备

• node1

ip：10.10.200.84
hostname：node1
desc：迁移前机器

• node2

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 文件迁移

准备

• node1 关闭 oracle 服务

# 连接
[oracle@node1 ~]$ sqlplus / as sysdba

# 关闭 oralce
SQL> shutdown immediate;

• node1 关闭监听

[oracle@node1 ~]$ lsnrctl stop

• node2 关闭 oracle 服务

# 连接
[oracle@node2 ~]$ sqlplus / as sysdba

# 关闭 oralce
SQL> shutdown immediate;

• node2 关闭监听

[oracle@node2 ~]$ lsnrctl stop

文件拷贝

• node2

# 备份原有目录
[oracle@node2 ~]$ mv /data/v1.3/oracle /data/v1.3/oracle_bak

• node1

# 拷贝整个目录
[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.

• open database

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)