Get the App
SLTechnology News&Howtos  ›  Servers  › 

Modify Oracle data file name and data file storage path

Shulou Source: shulou.com Published: 2022-06-02 05:45:02 10月07日 Update

Change Oracle data file name and data file storage path SQL > select * from v$dbfile

FILE# NAME

-

1 / db2/oracle/oradata/db2/system01.dbf

2/ db2/oracle/oradata/db2/sysaux01.dbf

3 / db2/oracle/oradata/db2/undotbs01.dbf

4 / db2/oracle/oradata/db2/users01.dbf

5 / db2/oracle/product/11.2.0/db_1/dbs/D:ORACLE11GADMINORADATAADMINgg01.dbf

6 / db2/oracle/oradata/db2/db201.dbf

First, close the database and make changes

[oracle@db1] $sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 26 17:43:38 2012 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 > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down. [oracle@db1] $mv / db2/oracle/product/11.2.0/db_1/dbs/D\: ORACLE11GADMINORADATAADMINgg01.dbf / db2/oracle/oradata/db2/gg01.dbf SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 26 17:48:31 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL > startup mount

ORACLE instance started. Total System Global Area 521936896 bytes

Fixed Size 2214936 bytes

Variable Size 163578856 bytes

Database Buffers 352321536 bytes

Redo Buffers 3821568 bytes

Database mounted. SQL > alter database rename file'/ db2/oracle/product/11.2.0/db_1/dbs/D\: ORACLE11GADMINORADATAADMINgg01.dbf' to'/ db2/oracle/oradata/db2/gg01.dbf'; Database altered. SQL > select * from v$dbfile

FILE# NAME

-

1 / db2/oracle/oradata/db2/system01.dbf

2/ db2/oracle/oradata/db2/sysaux01.dbf

3 / db2/oracle/oradata/db2/undotbs01.dbf

4 / db2/oracle/oradata/db2/users01.dbf

5 / db2/oracle/oradata/db2/gg01.dbf

6 / db2/oracle/oradata/db2/db201.dbf

6 rows selected II. Online revision

Method 1: tablespace offline

SQL > select * from dba_data_files

FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS

-

/ db2/oracle/oradata/db2/system01.dbf 1 SYSTEM 734003200 89600 AVAILABLE 1 YES 3435972198 4194302 1280 732954624 89472 SYSTEM

/ db2/oracle/oradata/db2/sysaux01.dbf 2 SYSAUX 629145600 76800 AVAILABLE 2 YES 3435972198 4194302 1280 628097024 76672 ONLINE

/ db2/oracle/oradata/db2/undotbs01.dbf 3 UNDOTBS1 618659840 75520 AVAILABLE 3 YES 3435972198 4194302 640 617611264 75392 ONLINE

/ db2/oracle/oradata/db2/users01.dbf 4 USERS 5242880 640 AVAILABLE 4 YES 3435972198 4194302 160 4194304 512 ONLINE

/ db2/oracle/oradata/db2/gg01.dbf 5 GG 104857600 12800 AVAILABLE 5 NO 00 0 103809024 12672 ONLINE

/ db2/oracle/oradata/db2/db201.dbf 6 DB2 104857600 12800 AVAILABLE 6 NO 00 0 103809024 12672 ONLINE

SQL > alter tablespace gg offline

Tablespace altered SQL > host mv / db2/oracle/oradata/db2/gg01.dbf / db2/oracle/oradata/db2/admin.dbf SQL > alter database rename file'/ db2/oracle/oradata/db2/gg01.dbf' to'/ db2/oracle/oradata/db2/admin.dbf'

Database altered SQL > alter tablespace gg online

Tablespace altered SQL > select * from dba_data_files

FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS

-

/ db2/oracle/oradata/db2/system01.dbf 1 SYSTEM 734003200 89600 AVAILABLE 1 YES 3435972198 4194302 1280 732954624 89472 SYSTEM

/ db2/oracle/oradata/db2/sysaux01.dbf 2 SYSAUX 629145600 76800 AVAILABLE 2 YES 3435972198 4194302 1280 628097024 76672 ONLINE

/ db2/oracle/oradata/db2/undotbs01.dbf 3 UNDOTBS1 618659840 75520 AVAILABLE 3 YES 3435972198 4194302 640 617611264 75392 ONLINE

/ db2/oracle/oradata/db2/users01.dbf 4 USERS 5242880 640 AVAILABLE 4 YES 3435972198 4194302 160 4194304 512 ONLINE

/ db2/oracle/oradata/db2/admin.dbf 5 GG 104857600 12800 AVAILABLE 5 NO 00 0 103809024 12672 ONLINE

/ db2/oracle/oradata/db2/db201.dbf 6 DB2 104857600 12800 AVAILABLE 6 NO 00 0 103809024 12672 ONLINE

Method 2: the data file is offline (method 2 must be done in database archiving mode)

SQL > alter database datafile 5 offline

Alter database datafile 5 offline

*

ERROR at line 1:

ORA-01145: offline immediate disallowed unless media recovery enabled SQL > archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 69

Current log sequence 71 opens archiving

[oracle@db1 ~] $mkdir / db2/oracle/archivelog

[oracle@db1] $sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 26 18:15:52 2012 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 > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down. SQL > startup mount

ORACLE instance started. Total System Global Area 521936896 bytes

Fixed Size 2214936 bytes

Variable Size 163578856 bytes

Database Buffers 352321536 bytes

Redo Buffers 3821568 bytes

Database mounted.

SQL > alter system set log_archive_dest_1='location=/db2/oracle/archivelog/'; System altered. SQL > alter database archivelog; Database altered. SQL > alter database open; Database altered. SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / db2/oracle/archivelog/

Oldest online log sequence 69

Next log sequence to archive 71

Current log sequence 71 SQL > alter database datafile 5 offline; Database altered. SQL > host mv / db2/oracle/oradata/db2/admin.dbf / db2/oracle/oradata/db2/gg01.dbf SQL > alter database rename file'/ db2/oracle/oradata/db2/admin.dbf' to'/ db2/oracle/oradata/db2/gg01.dbf'; Database altered. SQL > alter database datafile 5 online

Alter database datafile 5 online

*

ERROR at line 1:

ORA-01113: file 5 needs media recovery

ORA-01110: data file 5:'/ db2/oracle/oradata/db2/gg01.dbf'

SQL > recover datafile 5

Media recovery complete. SQL > alter database datafile 5 online; Database altered. SQL > select file_id,file_name,tablespace_name,online_status from dba_data_files

FILE_ID FILE_NAME TABLESPACE_NAME ONLINE_STATUS

1 / db2/oracle/oradata/db2/system01.dbf SYSTEM SYSTEM

2/ db2/oracle/oradata/db2/sysaux01.dbf SYSAUX ONLINE

3 / db2/oracle/oradata/db2/undotbs01.dbf UNDOTBS1 ONLINE

4 / db2/oracle/oradata/db2/users01.dbf USERS ONLINE

5 / db2/oracle/oradata/db2/gg01.dbf GG ONLINE

6 / db2/oracle/oradata/db2/db201.dbf DB2 ONLINE

Tags: Data file method database file name path pattern space online offline Apple Docker Huawei Linux macOS MariaDB Microsoft MySQL NVidia OPPO Reno Microsoft Linux MariaDB Docker MySQL

Related Articles