oracle更改文件,Oracle修改数据文件名以及移动数据文件

最新推荐文章于 2023-12-02 11:59:03 发布

weixin_39928648

最新推荐文章于 2023-12-02 11:59:03 发布

阅读量208

点赞数

文章标签： oracle更改文件

生产上有时会用到数据文件的迁移，下边示例几种Oracle数据文件的迁移方法。

数据库版本

sys@ORCL>select * from v$version;

select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE11.2.0.4.0Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

sys@ORCL>select name from v$datafile;

select name from v$datafile;

NAME

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/u02/app/oracle/oradata/orcl/system01.dbf

/u02/app/oracle/oradata/orcl/sysaux01.dbf

/u02/app/oracle/oradata/orcl/undotbs01.dbf

/u02/app/oracle/oradata/orcl/users01.dbf

/u02/app/oracle/oradata/orcl/DEV_svctbl.dbf

/u02/app/oracle/oradata/orcl/DEV_ias_opss.dbf

/u02/app/oracle/oradata/orcl/DEV_iau.dbf

/u02/app/oracle/oradata/orcl/DEV_veridata_user.dbf

/u02/app/oracle/oradata/orcl/DEV3_mds.dbf

/u02/app/oracle/oradata/orcl/DEV3_wlsservices.dbf

/u02/app/oracle/oradata/orcl/DEV3_iau.dbf

/u02/app/oracle/oradata/orcl/DEV3_ias_opss.dbf

/u02/app/oracle/oradata/orcl/DEV3_oggmon.dbf

/u02/app/oracle/oradata/orcl/DEV2_ias_opss.dbf

/u02/app/oracle/oradata/orcl/DEV3_UMS.dbf

/u02/app/oracle/oradata/orcl/DEV3_svctbl.dbf

/u02/app/oracle/oradata/orcl/DEV2_oggmon.dbf

/home/oracle/tt.bdf

/home/oracle/undo02.dbf

19 rows selected.

一、关闭数据库修改

1、使用mv方式(适用于文件系统)

sys@ORCL>shutdown immediate;

shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@ORCL>! mv /home/oracle/tt.bdf /home/oracle/tt_mv.dbf

! mv /home/oracle/tt.bdf /home/oracle/tt_mv.dbf

l /home/oracle/tt_! ls -l /home/oracle/tt_mv.dbf

-rw-r----- 1 oracle oinstall 1073750016 Oct 29 08:31 /home/oracle/tt_mv.dbf

sys@ORCL>startup mount;

startup mount;

ORACLE instance started.

Total System Global Area 784998400 bytes

Fixed Size 2257352 bytes

Variable Size 478154296 bytes

Database Buffers 297795584 bytes

Redo Buffers 6791168 bytes

Database mounted.

sys@ORCL>alter database rename file '/home/oracle/tt.bdf' to '/home/oracle/tt_mv.dbf';

alter database rename file '/home/oracle/tt.bdf' to '/home/oracle/tt_mv.dbf';

Database altered.

sys@ORCL>select name from v$datafile;

select name from v$datafile;

NAME

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/u02/app/oracle/oradata/orcl/system01.dbf

/u02/app/oracle/oradata/orcl/sysaux01.dbf

/u02/app/oracle/oradata/orcl/undotbs01.dbf

/u02/app/oracle/oradata/orcl/users01.dbf

/u02/app/oracle/oradata/orcl/DEV_svctbl.dbf

/u02/app/oracle/oradata/orcl/DEV_ias_opss.dbf

/u02/app/oracle/oradata/orcl/DEV_iau.dbf

/u02/app/oracle/oradata/orcl/DEV_veridata_user.dbf

/u02/app/oracle/oradata/orcl/DEV3_mds.dbf

/u02/app/oracle/oradata/orcl/DEV3_wlsservices.dbf

/u02/app/oracle/oradata/orcl/DEV3_iau.dbf

/u02/app/oracle/oradata/orcl/DEV3_ias_opss.dbf

/u02/app/oracle/oradata/orcl/DEV3_oggmon.dbf

/u02/app/oracle/oradata/orcl/DEV2_ias_opss.dbf

/u02/app/oracle/oradata/orcl/DEV3_UMS.dbf

/u02/app/oracle/oradata/orcl/DEV3_svctbl.dbf

/u02/app/oracle/oradata/orcl/DEV2_oggmon.dbf

/home/oracle/tt_mv.dbf

/home/oracle/undo02.dbf

19 rows selected.

sys@ORCL>alter database open;

alter database open;

Database altered.

2、RMAN备份方式(适用于文件系统、祼设备、ASM)

sys@ORCL>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@ORCL>exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@rhel6 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Oct 29 21:13:36 2016

connected to target database (not started)

RMAN> startup mount;

Oracle instance started

database mounted

Total System Global Area 784998400 bytes

Fixed Size 2257352 bytes

Variable Size 478154296 bytes

Database Buffers 297795584 bytes

Redo Buffers 6791168 bytes

RMAN> backup as copy datafile '/u02/app/oracle/oradata/orcl/users01.dbf' format '/dev/raw/raw1';

Starting backup at 29-OCT-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=133 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u02/app/oracle/oradata/orcl/users01.dbf

output file name=/dev/raw/raw1 tag=TAG20161029T211402 RECID=2 STAMP=926543658

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

Finished backup at 29-OCT-16

RMAN> switch datafile '/u02/app/oracle/oradata/orcl/users01.dbf' to copy;

datafile 4 switched to datafile copy "/dev/raw/raw1"

RMAN> alter database open;

database opened

RMAN> exit

Recovery Manager complete.

[oracle@rhel6 ~]$ ss

SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 29 21:15:03 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

sys@ORCL>select name from v$datafile;

NAME

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/u02/app/oracle/oradata/orcl/system01.dbf

/u02/app/oracle/oradata/orcl/sysaux01.dbf

/u02/app/oracle/oradata/orcl/undotbs01.dbf

/dev/raw/raw1

/u02/app/oracle/oradata/orcl/DEV_svctbl.dbf

/u02/app/oracle/oradata/orcl/DEV_ias_opss.dbf

/u02/app/oracle/oradata/orcl/DEV_iau.dbf

/u02/app/oracle/oradata/orcl/DEV_veridata_user.dbf

/u02/app/oracle/oradata/orcl/DEV3_mds.dbf

/u02/app/oracle/oradata/orcl/DEV3_wlsservices.dbf

/u02/app/oracle/oradata/orcl/DEV3_iau.dbf

/u02/app/oracle/oradata/orcl/DEV3_ias_opss.dbf

/u02/app/oracle/oradata/orcl/DEV3_oggmon.dbf

/u02/app/oracle/oradata/orcl/DEV2_ias_opss.dbf

/u02/app/oracle/oradata/orcl/DEV3_UMS.dbf

/u02/app/oracle/oradata/orcl/DEV3_svctbl.dbf

/u02/app/oracle/oradata/orcl/DEV2_oggmon.dbf

/home/oracle/tt_mv3.dbf

/home/oracle/undo02.dbf

19 rows selected.

二、在线修改

1、表空间OFFLINE方式

sys@ORCL>col file_name for a70

sys@ORCL>select file_name,tablespace_name,status,online_status from dba_data_files;

select file_name,tablespace_name,status,online_status from dba_data_files;

FILE_NAME TABLESPACE_NAME STATUSONLINE_

---------------------------------------------------------------------- ------------------------------ --------- -------

/u02/app/oracle/oradata/orcl/users01.dbf USERS AVAILABLE ONLINE

/u02/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1 AVAILABLE ONLINE

/u02/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX AVAILABLE ONLINE

/u02/app/oracle/oradata/orcl/system01.dbf SYSTEM AVAILABLE SYSTEM

/u02/app/oracle/oradata/orcl/DEV_svctbl.dbf DEV_STB AVAILABLE ONLINE

/u02/app/oracle/oradata/orcl/DEV_ias_opss.dbf DEV_IAS_OPSS AVAILABLE ONLINE

/u02/app/oracle/oradata/orcl/DEV_iau.dbf DEV_IAU AVAILABLE ONLINE

/u02/app/oracle/oradata/orcl/DEV_veridata_user.dbf DEV_VERIDATA_USER AVAILABLE ONLINE

/u02/app/oracle/oradata/orcl/DEV3_mds.dbf DEV3_MDS AVAILABLE ONLINE

/u02/app/oracle/oradata/orcl/DEV3_wlsservices.dbf DEV3_WLS AVAILABLE ONLINE

/u02/app/oracle/oradata/orcl/DEV3_iau.dbf DEV3_IAU AVAILABLE ONLINE

/u02/app/oracle/oradata/orcl/DEV3_ias_opss.dbf DEV3_IAS_OPSS AVAILABLE ONLINE

/u02/app/oracle/oradata/orcl/DEV3_oggmon.dbf DEV3_OGGMON AVAILABLE ONLINE

/u02/app/oracle/oradata/orcl/DEV2_ias_opss.dbf DEV2_IAS_OPSS AVAILABLE ONLINE

/u02/app/oracle/oradata/orcl/DEV3_UMS.dbf DEV3_IAS_UMS AVAILABLE ONLINE

/u02/app/oracle/oradata/orcl/DEV3_svctbl.dbf DEV3_STB AVAILABLE ONLINE

/u02/app/oracle/oradata/orcl/DEV2_oggmon.dbf DEV2_OGGMON AVAILABLE ONLINE

/home/oracle/tt_mv.dbf TT AVAILABLE ONLINE

/home/oracle/undo02.dbf UNDOTBS1 AVAILABLE ONLINE

19 rows selected.

sys@ORCL>alter tablespace tt offline;

alter tablespace tt offline;

Tablespace altered.