重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: 如何在不重启库的情况下修改表空间名称及其数据文件（需开归档） [打印本页]

作者: Inkcup 时间: 2025-5-23 19:08
标题: 如何在不重启库的情况下修改表空间名称及其数据文件（需开归档）
本帖最后由 Inkcup 于 2025-5-29 16:42 编辑

在12c以后，可以直接在线更改数据文件的位置，但是在12c以前，修改数据文件的位置需要先将数据库启动到mount状态下。但是也可以通过RMAN进行无需重启库的在线修改：

①确认信息
SQL> archive log list;
Database log mode             Archive Mode
Automatic archival          Enabled
Archive destination          /u01/arch_log
Oldest online log sequence    28
Next log sequence to archive 30
Current log sequence          30

SQL> select FILE_NAME,TABLESPACE_NAME,BYTES,STATUS,ONLINE_status from dba_data_files where tablespace_name='SCHOOL';

FILE_NAME                                                       TABLESPACE_NAME             BYTES STATUS ONLINE_
------------------------------------------------------------- ---------- --------- -------
/u01/app/oracle/oradata/orcl/school01.dbf          SCHOOL                         536870912 AVAILABLE ONLINE

②修改表空间名称
SQL> alter tablespace SCHOOL rename to TEST;

Tablespace altered.

SQL> select FILE_NAME,TABLESPACE_NAME,BYTES,STATUS,ONLINE_status from dba_data_files where tablespace_name='TEST';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                   BYTES STATUS ONLINE_
------------------------------ ---------- --------- -------
/u01/app/oracle/oradata/orcl/school01.dbf
TEST                         536870912 AVAILABLE ONLINE

③修改表空间的数据文件名称
方法一：离线单个数据文件
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/school01.dbf' offline;

Database altered.

[oracle@alluse orcl]$ mv school01.dbf test01.dbf
#缺少这一步会报错：ORA-01141: error renaming data file 5 - new file '/u01/app/oracle/oradata/orcl/l/test01.dbf' not found

SQL> alter tablespace TEST rename datafile '/u01/app/oracle/oradata/orcl/school01.dbf' to '/u01/app/oracle/oradata/orcl/test01.dbf';

Tablespace altered.

SQL> alter database recover datafile '/u01/app/oracle/oradata/orcl/test01.dbf';
#缺少这一步会报错：ORA-01113: file 5 needs media recovery
Database altered.

SQL> alter tablespace TEST online;
#一般是只拉单个文件，此处表空间仅有单个文件，因此使用此命令。
Tablespace altered.

SQL> select FILE_NAME,TABLESPACE_NAME,BYTES,STATUS,ONLINE_status from dba_data_files where tablespace_name='TEST';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                   BYTES STATUS ONLINE_
------------------------------ ---------- --------- -------
/u01/app/oracle/oradata/orcl/test01.dbf
TEST                         536870912 AVAILABLE ONLINE

方法二：离线表空间
注意：此方法将导致该表空间的所有文件暂不可用
alter tablespace <tablespace_name> offline;
$ mv /path/to/旧表空间名.dbf /path/to/新表空间名.dbf
alter tablespace <tablespace_name> rename datafile '/path/to/旧表空间名.dbf' to '/path/to/新表空间名.dbf';
alter tablespace <tablespace_name> online;

#以上两种方法均需手动修改数据文件路径，有停业务的风险！

方法三:使用RMAN修改（在线修改无需挂载状态）
[oracle@alluse orcl]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri May 23 06:44:13 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1728231960)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace          RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 760    SYSTEM             ***    /u01/app/oracle/oradata/orcl/system01.dbf
2 530    SYSAUX             ***    /u01/app/oracle/oradata/orcl/sysaux01.dbf
3 75    UNDOTBS1          ***    /u01/app/oracle/oradata/orcl/undotbs01.dbf
4 5       USERS             ***    /u01/app/oracle/oradata/orcl/users01.dbf
5 512    TEST                ***    /u01/app/oracle/oradata/orcl/test01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace          Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29    TEMP                32767    /u01/app/oracle/oradata/orcl/temp01.dbf

RMAN> backup as copy datafile 5 format '/u01/app/oracle/test01.dbf';

Starting backup at 23-MAY-25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=69 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/test01.dbf
output file name=/u01/app/oracle/test01.dbf tag=TAG20250523T064459 RECID=4 STAMP=1201848305
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 23-MAY-25

RMAN> sql 'alter database datafile 5 offline';

sql statement: alter database datafile 5 offline

RMAN> switch datafile 5 to copy;

datafile 5 switched to datafile copy "/u01/app/oracle/test01.dbf"

RMAN> recover datafile 5;

Starting recover at 23-MAY-25
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 23-MAY-25

RMAN> sql" alter database datafile 5 online";

sql statement:  alter database datafile 5 online

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace          RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 760    SYSTEM             ***    /u01/app/oracle/oradata/orcl/system01.dbf
2 530    SYSAUX             ***    /u01/app/oracle/oradata/orcl/sysaux01.dbf
3 75    UNDOTBS1          ***    /u01/app/oracle/oradata/orcl/undotbs01.dbf
4 5       USERS             ***    /u01/app/oracle/oradata/orcl/users01.dbf
5 512    TEST                ***    /u01/app/oracle/test01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace          Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29    TEMP                32767    /u01/app/oracle/oradata/orcl/temp01.dbf

欢迎光临重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)