Oracle数据库文件路径变更

环境：CentOS 7.7 + Oracle 11.2.0.4

1.检查所有文件的绝对路径

• 查看数据文件、临时文件、日志文件、控制文件当前目录

SQL>  select name from v$datafile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oragata/ora11g/system01.dbf
/u01/app/oracle/oragata/ora11g/sysaux01.dbf
/u01/app/oracle/oragata/ora11g/undotbs01.dbf
/u01/app/oracle/oragata/ora11g/users01.dbf

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oragata/ora11g/temp01.dbf

SQL>  select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oragata/ora11g/redo01.log
/u01/app/oracle/oragata/ora11g/redo02.log
/u01/app/oracle/oragata/ora11g/redo03.log

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oragata/ora11g/control01.ctl
/u01/app/oracle/oragata/ora11g/control02.ctl

• 查看参数文件

[oracle@weblog dbs]$ ll
total 20
-rw-rw---- 1 oracle oinstall 1544 Dec 17 14:42 hc_ora11g.dat
-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r----- 1 oracle oinstall   24 Dec 17 14:24 lkORA11G
-rw-r----- 1 oracle oinstall 1536 Dec 17 14:24 orapwora11g
-rw-r----- 1 oracle oinstall 2560 Dec 17 14:42 spfileora11g.ora

• 创建spfile

SQL> create pfile from spfile;

File created.

[oracle@weblog dbs]$ ll
total 24
-rw-rw---- 1 oracle oinstall 1544 Dec 17 14:42 hc_ora11g.dat
-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r--r-- 1 oracle oinstall  876 Dec 17 14:49 initora11g.ora
-rw-r----- 1 oracle oinstall   24 Dec 17 14:24 lkORA11G
-rw-r----- 1 oracle oinstall 1536 Dec 17 14:24 orapwora11g
-rw-r----- 1 oracle oinstall 2560 Dec 17 14:42 spfileora11g.ora

2.关闭实例

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

3.创建新目录

[oracle@weblog dbs]$ cd /u01/app/oracle/
[oracle@weblog oracle]$ ll
total 0
drwxr-x---  3 oracle oinstall  28 Dec 17 14:24 admin/
drwxr-x---  6 oracle oinstall  80 Dec 17 14:42 cfgtoollogs/
drwxr-xr-x  2 oracle oinstall  10 Dec 17 14:14 checkpoints/
drwxrwxr-x 11 oracle oinstall 168 Dec 17 14:09 diag/
drwxr-xr-x  2 oracle oinstall  10 Dec 17 14:21 oradata/
drwxr-x---  3 oracle oinstall  28 Dec 17 14:24 oragata/
drwxrwxr-x  3 oracle oinstall  28 Dec 17 13:48 product/

4.修改控制文件

• 在pfile中更改控制文件地址

[oracle@weblog oracle]$ cd $ORACLE_HOME/dbs
[oracle@weblog dbs]$ more initora11g.ora
ora11g.__db_cache_size=3456106496
ora11g.__java_pool_size=50331648
ora11g.__large_pool_size=67108864
ora11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ora11g.__pga_aggregate_target=212097564672
ora11g.__sga_target=4294967296
ora11g.__shared_io_pool_size=0
ora11g.__shared_pool_size=687865856
ora11g.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ora11g/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oragata/ora11g/control01.ctl','/u01/app/oracle/oragata/ora11g/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ora11g'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)'
*.open_cursors=300
*.pga_aggregate_target=212095467520
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=2205
*.sga_target=4293918720
*.undo_tablespace='UNDOTBS1'

[oracle@weblog dbs]$ vi initora11g.ora
[oracle@weblog dbs]$ more initora11g.ora
ora11g.__db_cache_size=3456106496
ora11g.__java_pool_size=50331648
ora11g.__large_pool_size=67108864
ora11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ora11g.__pga_aggregate_target=212097564672
ora11g.__sga_target=4294967296
ora11g.__shared_io_pool_size=0
ora11g.__shared_pool_size=687865856
ora11g.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ora11g/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/ora11g/control01.ctl','/u01/app/oracle/oradata/ora11g/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ora11g'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)'
*.open_cursors=300
*.pga_aggregate_target=212095467520
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=2205
*.sga_target=4293918720
*.undo_tablespace='UNDOTBS1'

• 用修改过控制文件目录的参数文件启动数据库至mount

SQL> startup pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initora11g.ora' nomount;
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size            2260088 bytes
Variable Size         1526727560 bytes
Database Buffers     2734686208 bytes
Redo Buffers           12107776 bytes
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: ?????????, ??????, ???????


SQL> select status from v$instance;

STATUS
------------------------------------
STARTED
！ 命令错误，应该用 startup nomount pfile ='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initora11g.ora';
SQL> shutdown immediate;
ORA-01507: ??????


ORACLE instance shut down.
SQL> 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@weblog oradata]$  export NLS_LANG=american_america.zhs16gbk
[oracle@weblog oradata]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 17 14:55:21 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile ='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initora11g.ora';
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size            2260088 bytes
Variable Size         1526727560 bytes
Database Buffers     2734686208 bytes
Redo Buffers           12107776 bytes
SQL> alter database rename file '/u01/app/oracle/oragata/ora11g/system01.dbf' to '/u01/app/oracle/oradata/ora11g/system01.dbf';
alter database rename file '/u01/app/oracle/oragata/ora11g/system01.dbf' to '/u01/app/oracle/oradata/ora11g/system01.dbf'
*
ERROR at line 1:
ORA-01507: database not mounted


SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
控制文件必须真实存在在参数文件记录的地址中

报错原因：需要将控制文件移至要修改的目录下

[oracle@weblog dbs]$ cp /u01/app/oracle/oragata/ora11g/control01.ctl /u01/app/oracle/oradata/ora11g/control01.ctl
cp: cannot create regular file ‘/u01/app/oracle/oradata/ora11g/control01.ctl’: No such file or directory
！注意，要提前创建好目录
[oracle@weblog dbs]$ cd /u01/app/oracle/oradata/
[oracle@weblog oradata]$ mkdir ora11g
[oracle@weblog oradata]$ ll
total 0
drwxr-xr-x 2 oracle oinstall 10 Dec 17 15:02 ora11g/
[oracle@weblog oradata]$ cd ..
[oracle@weblog oracle]$ cd ..

[oracle@weblog oracle]$ cp /u01/app/oracle/oragata/ora11g/control01.ctl /u01/app/oracle/oradata/ora11g/control01.ctl
[oracle@weblog oracle]$ cp /u01/app/oracle/oragata/ora11g/control02.ctl /u01/app/oracle/oradata/ora11g/control02.ctl

• 重新mount数据库

SQL> alter database mount;

Database altered.

5.修改数据文件、日志文件、临时文件

SQL> alter database rename file '/u01/app/oracle/oragata/ora11g/system01.dbf' to '/u01/app/oracle/oradata/ora11g/system01.dbf';
alter database rename file '/u01/app/oracle/oragata/ora11g/system01.dbf' to '/u01/app/oracle/oradata/ora11g/system01.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 1 - new file
'/u01/app/oracle/oradata/ora11g/system01.dbf' not found
ORA-01110: data file 1: '/u01/app/oracle/oragata/ora11g/system01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

！错误原因同上

• 将数据文件、日志文件等移动到目标目录下

[oracle@weblog oracle]$ cp /u01/app/oracle/oragata/ora11g/system01.dbf /u01/app/oracle/oradata/ora11g/system01.dbf
[oracle@weblog oracle]$ cp /u01/app/oracle/oragata/ora11g/sysaux01.dbf /u01/app/oracle/oradata/ora11g/sysaux01.dbf
[oracle@weblog oracle]$ cp /u01/app/oracle/oragata/ora11g/undotbs01.dbf /u01/app/oracle/oradata/ora11g/undotbs01.dbf
[oracle@weblog oracle]$ cp /u01/app/oracle/oragata/ora11g/users01.dbf /u01/app/oracle/oradata/ora11g/users01.dbf
[oracle@weblog oracle]$ cp /u01/app/oracle/oragata/ora11g/temp01.dbf /u01/app/oracle/oradata/ora11g/temp01.dbf
[oracle@weblog oracle]$ cp /u01/app/oracle/oragata/ora11g/redo01.log /u01/app/oracle/oradata/ora11g/redo01.log
[oracle@weblog oracle]$ cp /u01/app/oracle/oragata/ora11g/redo02.log /u01/app/oracle/oradata/ora11g/redo02.log
[oracle@weblog oracle]$ cp /u01/app/oracle/oragata/ora11g/redo03.log /u01/app/oracle/oradata/ora11g/redo03.log

• rename数据文件、日志文件等

SQL> alter database rename file '/u01/app/oracle/oragata/ora11g/system01.dbf' to '/u01/app/oracle/oradata/ora11g/system01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oragata/ora11g/sysaux01.dbf' to '/u01/app/oracle/oradata/ora11g/sysaux01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oragata/ora11g/undotbs01.dbf' to '/u01/app/oracle/oradata/ora11g/undotbs01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oragata/ora11g/users01.dbf' to '/u01/app/oracle/oradata/ora11g/users01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oragata/ora11g/temp01.dbf' to '/u01/app/oracle/oradata/ora11g/temp01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oragata/ora11g/redo01.log' to '/u01/app/oracle/oradata/ora11g/redo01.log';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oragata/ora11g/redo02.log' to '/u01/app/oracle/oradata/ora11g/redo02.log';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oragata/ora11g/redo03.log' to '/u01/app/oracle/oradata/ora11g/redo03.log';

Database altered.

控制文件已经卸载参数文件里了，不需要rename

SQL> alter database rename file '/u01/app/oracle/oragata/ora11g/control01.ctl' to '/u01/app/oracle/oradata/ora11g/control01.ctl';
alter database rename file '/u01/app/oracle/oragata/ora11g/control01.ctl' to '/u01/app/oracle/oradata/ora11g/control01.ctl'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, data file, or temporary file
"/u01/app/oracle/oragata/ora11g/control01.ctl"

6. 开库

SQL> alter database open;

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/system01.dbf
/u01/app/oracle/oradata/ora11g/sysaux01.dbf
/u01/app/oracle/oradata/ora11g/undotbs01.dbf
/u01/app/oracle/oradata/ora11g/users01.dbf

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/temp01.dbf

SQL>  select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/redo01.log
/u01/app/oracle/oradata/ora11g/redo02.log
/u01/app/oracle/oradata/ora11g/redo03.log

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/control01.ctl
/u01/app/oracle/oradata/ora11g/control02.ctl

SQL> show parameter pfile

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                     string

7.更新spfile

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size            2260088 bytes
Variable Size         1526727560 bytes
Database Buffers     2734686208 bytes
Redo Buffers           12107776 bytes
Database mounted.
Database opened.