迁移Oracle数据库dbf文件

迁移Oracle数据库dbf文件

线上服务器磁盘空间不够，需要将dbf文件迁移至多余空间的磁盘，在自己服务器搭建测试环境测试，由于一开始不想搞乱本地环境，直接在Docker里面部署了Oracle环境，导致没有办法进行停机迁移，Oracle的dbf文件迁移 真是搬起石头砸自己脚。

查看表空间以及表空间dbf的路径

SELECT 
 a.tablespace_name    "表空间名", 
 total                "表空间大小", 
 free                 "表空间剩余大小", 
 (total - free)        "表空间使用大小", 
 total / (1024 * 1024 * 1024) "表空间大小(G)", 
 free / (1024 * 1024 * 1024)   "表空间剩余大小(G)", 
 (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", 
round((total - free) / total, 4) * 100 "使用率 %" 
FROM (SELECT tablespace_name, SUM(bytes) free 
       FROM dba_free_space 
        GROUP BY tablespace_name) a, 
    (SELECT tablespace_name, SUM(bytes) total 
     FROM dba_data_files 
       GROUP BY tablespace_name) b 
WHERE 
a.  tablespace_name = b.tablespace_name;

如果没有创建表空间，默认表空间为USERS

根据表空间名查看dbf路径

select * from dba_data_files where tablespace_name = 'USERS'

登陆Oracle容器内部,执行数据文件迁移

root@localhost:~$ sudo docker exec -it oracle bash

bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Thu Nov 30 16:08:29 2023
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> ALTER TABLESPACE USERS READ ONLY;
Tablespace altered.

SQL> ALTER DATABASE MOVE DATAFILE '/opt/oracle/oradata/ORCLCDB/users01.dbf' TO '/opt/oracle/oradata/users01.dbf';
Database altered.

SQL> ALTER TABLESPACE USERS READ WRITE;
Tablespace altered.

SQL> exit
Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

查看dbf文件是否被迁移

bash-4.2$ ls -al /opt/oracle/oradata/ORCLCDB/users01.dbf
ls: cannot access /opt/oracle/oradata/ORCLCDB/users01.dbf: No such file or directory

bash-4.2$ ls -al /opt/oracle/oradata/users01.dbf
-rw-r----- 1 oracle dba 5251072 Nov 30 16:12 /opt/oracle/oradata/users01.dbf

Oracle容器日志

ALTER TABLESPACE USERS READ ONLY
2023-11-30T16:10:00.394933+00:00
 Converting block 0 to version 10 format
Completed: ALTER TABLESPACE USERS READ ONLY
2023-11-30T16:11:26.524007+00:00
ALTER DATABASE MOVE DATAFILE '/opt/oracle/oradata/ORCLCDB/users01.dbf' TO '/opt/oracle/oradata/users01.dbf'
2023-11-30T16:11:26.556685+00:00
Moving datafile /opt/oracle/oradata/ORCLCDB/users01.dbf (7) to /opt/oracle/oradata/users01.dbf
Move operation committed for file /opt/oracle/oradata/users01.dbf
2023-11-30T16:11:29.591696+00:00
Completed: ALTER DATABASE MOVE DATAFILE '/opt/oracle/oradata/ORCLCDB/users01.dbf' TO '/opt/oracle/oradata/users01.dbf'
2023-11-30T16:12:36.822126+00:00
ALTER TABLESPACE USERS READ WRITE
Completed: ALTER TABLESPACE USERS READ WRITE
2023-11-30T16:13:21.639669+00:00
Resize operation completed for file# 3, fname /opt/oracle/oradata/ORCLCDB/sysaux01.dbf, old size 604160K, new size 614400K