oracle 更改数据文件名,更改Oracle数据文件名及数据文件存放路径

最新推荐文章于 2024-05-07 15:00:56 发布

多腮寇

最新推荐文章于 2024-05-07 15:00:56 发布

阅读量257

点赞数

文章标签： oracle 更改数据文件名

更改Oracle数据文件名及数据文件存放路径

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

一、关闭数据库进行修改 [oracle@db1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 26 17:43:38 2012

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

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

二、在线修改方法1:表空间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 0 0 0 103809024 12672 ONLINE /db2/oracle/oradata/db2/db201.dbf 6 DB2 104857600 12800 AVAILABLE 6 NO 0 0 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 0 0 0 103809024 12672 ONLINE /db2/oracle/oradata/db2/db201.dbf 6 DB2 104857600 12800 AVAILABLE 6 NO 0 0 0 103809024 12672 ONLINE 方法2:数据文件脱机 (方法2必须在数据库归档模式下进行) 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

开归档 [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

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

多腮寇

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 更改数据文件名,更改Oracle数据文件名及数据文件存放路径

更改Oracle数据文件名及数据文件存放路径SQL> select * from v$dbfile; FILE# NAME ---------- -------------------------------------------------------------------------------- 1 /db2/oracle/oradata/db2/s...
复制链接

扫一扫