今天，在做创建ORACLE GOLDENGATE测试数据库时，将数据库的数据文件目录放在了一个空间占用比较大的盘上，需要调整过来。下面详细记录这一过程。
1、关闭数据库
shutdown immediate
2、将数据库的数据文件移动到新位置
mv /u01/ora10g/oradata/ggtarget /u02/ora10g/oradata
3、启动数据库到nomount状态
-bash-3.2$ export ORACLE_SID=ggtarge
-bash-3.2$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on 2 14:47:28 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 222298112 bytes
Fixed Size 1266680 bytes
Variable Size 75500552 bytes
Database Buffers 138412032 bytes
Redo Buffers 7118848 bytes
4、调整control_files参数，将重启数据库到mount状态
SQL> show parameter control
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
control_file_record_keep_time integer
7
control_files string
/u01/ora10g/oradata/ggtarge/co
ntrol01.ctl, /u01/ora10g/orada
ta/ggtarge/control02.ctl, /u01
/ora10g/oradata/ggtarge/contro
l03.ctl
SQL> alter system set control_files='/u02/ora10g/oradata/ggtarge/control01.ctl, /u02/ora10g/oradata/ggtarge/control02.ctl, /u02/ora10g/oradata/ggtarge/control03.ctl' scope=both;
alter system set control_files='/u02/ora10g/oradata/ggtarge/control01.ctl, /u02/ora10g/oradata/ggtarge/control02.ctl, /u02/ora10g/oradata/ggtarge/control03.ctl' scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set control_files='/u02/ora10g/oradata/ggtarge/control01.ctl, /u02/ora10g/oradata/ggtarge/control02.ctl, /u02/ora10g/oradata/ggtarge/control03.ctl' scope=spfile;
System altered.
SQL> select * from v$datafile;
select * from v$datafile
*
ERROR at line 1:
ORA-01507: database not mounted
SQL>
发现直接修改spfile，会给参数中带来单引号，考虑修改pfile解决
SQL> show parameter control
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
control_file_record_keep_time integer
7
control_files string
/u02/ora10g/oradata/ggtarge/co
ntrol01.ctl, /u02/ora10g/orada
ta/ggtarge/control02.ctl, /u02
/ora10g/oradata/ggtarge/contro
l03.ctl
SQL> create pfile from spfile;
File created.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
编辑参数文件/u01/ora10g/product/10.2.0/db_1/dbs/initggtarge.ora,修改参数：
*.control_files='/u02/ora10g/oradata/ggtarge/control01.ctl, /u02/ora10g/oradata/ggtarge/control02.ctl,/u02/ora10g/oradata/ggtarge/control03.ctl'
改为*.control_files=/u02/ora10g/oradata/ggtarge/control01.ctl, /u02/ora10g/oradata/ggtarge/control02.ctl,/u02/ora10g/oradata/ggtarge/control03.ctl
再次启动数据库到mount状态：
SQL> startup mount pfile=/u01/ora10g/product/10.2.0/db_1/dbs/initggtarge.ora
ORACLE instance started.
Total System Global Area 222298112 bytes
Fixed Size 1266680 bytes
Variable Size 75500552 bytes
Database Buffers 138412032 bytes
Redo Buffers 7118848 bytes
Database mounted.
SQL> show parameter control
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
control_file_record_keep_time integer
7
control_files string
/u02/ora10g/oradata/ggtarge/co
ntrol01.ctl, /u02/ora10g/orada
ta/ggtarge/control02.ctl, /u02
/ora10g/oradata/ggtarge/contro
l03.ctl
SQL> create spfile from pfile;
File created.
5、修改数据文件
SQL> desc dba_data_files;
ERROR:
ORA-04043: object dba_data_files does not exist
SQL> desc dba_datafiles;
ERROR:
ORA-04043: object dba_datafiles does not exist
SQL> desc v$datafile;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
CHECKPOINT_CHANGE# NUMBER
CHECKPOINT_TIME DATE
UNRECOVERABLE_CHANGE# NUMBER
UNRECOVERABLE_TIME DATE
LAST_CHANGE# NUMBER
LAST_TIME DATE
OFFLINE_CHANGE# NUMBER
ONLINE_CHANGE# NUMBER
ONLINE_TIME DATE
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
PLUGGED_IN NUMBER
BLOCK1_OFFSET NUMBER
AUX_NAME VARCHAR2(513)
FIRST_NONLOGGED_SCN NUMBER
FIRST_NONLOGGED_TIME DATE
SQL> col name for a40 ;
SQL> select name from v$datafile;
NAME
----------------------------------------
/u01/ora10g/oradata/ggtarge/system01.dbf
/u01/ora10g/oradata/ggtarge/undotbs01.db
f
/u01/ora10g/oradata/ggtarge/sysaux01.dbf
/u01/ora10g/oradata/ggtarge/users01.dbf
SQL> alter database rename datafile '/u01/ora10g/oradata/ggtarge/system01.dbf' to '/u02/ora10g/oradata/ggtarge/system01.dbf';
alter database rename datafile '/u01/ora10g/oradata/ggtarge/system01.dbf' to '/u02/ora10g/oradata/ggtarge/system01.dbf'
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
SQL> alter database rename file '/u01/ora10g/oradata/ggtarge/system01.dbf' to '/u02/ora10g/oradata/ggtarge/system01.dbf';
Database altered.
SQL> alter database rename file '/u01/ora10g/oradata/ggtarge/undotbs01.dbf' to '/u02/ora10g/oradata/ggtarge/undotbs01.dbf';
Database altered.
SQL> alter database rename file '/u01/ora10g/oradata/ggtarge/sysaux01.dbf' to '/u02/ora10g/oradata/ggtarge/sysaux01.dbf';
Database altered.
SQL> alter database rename file '/u01/ora10g/oradata/ggtarge/users01.dbf' to '/u02/ora10g/oradata/ggtarge/users01.dbf';
Database altered.
SQL> select name from v$datafile;
NAME
----------------------------------------
/u02/ora10g/oradata/ggtarge/system01.dbf
/u02/ora10g/oradata/ggtarge/undotbs01.db
f
/u02/ora10g/oradata/ggtarge/sysaux01.dbf
/u02/ora10g/oradata/ggtarge/users01.dbf
6、修改日志文件
SQL> desc v$logfile;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
SQL> col member for a40;
SQL> select member from v$logfile;
MEMBER
----------------------------------------
/u01/ora10g/oradata/ggtarge/redo03.log
/u01/ora10g/oradata/ggtarge/redo02.log
/u01/ora10g/oradata/ggtarge/redo01.log
SQL> alter database rename file '/u01/ora10g/oradata/ggtarge/redo03.log' to '/u02/ora10g/oradata/ggtarge/redo03.log';
Database altered.
SQL> alter database rename file '/u01/ora10g/oradata/ggtarge/redo02.log' to '/u02/ora10g/oradata/ggtarge/redo02.log';
Database altered.
SQL> alter database rename file '/u01/ora10g/oradata/ggtarge/redo01.log' to '/u02/ora10g/oradata/ggtarge/redo01.log';
Database altered.
SQL> select member from v$logfile;
MEMBER
----------------------------------------
/u02/ora10g/oradata/ggtarge/redo03.log
/u02/ora10g/oradata/ggtarge/redo02.log
/u02/ora10g/oradata/ggtarge/redo01.log
7、修改临时文件
SQL> desc v$tempfile;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
SQL> select name from v$tempfile;
NAME
----------------------------------------
/u01/ora10g/oradata/ggtarge/temp01.dbf
SQL> alter database rename file '/u01/ora10g/oradata/ggtarge/temp01.dbf' to '/u02/ora10g/oradata/ggtarge/temp01.dbf
2 ';
Database altered.
SQL> select * from v$tempfile;
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS
---------- ---------------- -------------- ---------- ---------- --------------
ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
-------------------- ---------- ---------- ------------ ----------
NAME
----------------------------------------
1 464714 07-1210 3 1 ONLINE
READ WRITE 0 0 20971520 8192
/u02/ora10g/oradata/ggtarge/temp01.dbf
8、测试将数据库启动到OPEN状态
SQL> alter database open;
Database altered.
SQL> select sysdate from dual;
SYSDATE
--------------
07-1210
OK，移动数据文件位置后成功打开数据库
9、差点忘了，已经开了归档了，但是没有设置归档目录
SQL> show parameter archive;
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
archive_lag_target integer
0
log_archive_config string
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
enable
log_archive_dest_state_10 string
enable
log_archive_dest_state_2 string
enable
log_archive_dest_state_3 string
enable
log_archive_dest_state_4 string
enable
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
log_archive_dest_state_5 string
enable
log_archive_dest_state_6 string
enable
log_archive_dest_state_7 string
enable
log_archive_dest_state_8 string
enable
log_archive_dest_state_9 string
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
enable
log_archive_duplex_dest string
log_archive_format string
%t_%s_%r.dbf
log_archive_local_first boolean
TRUE
log_archive_max_processes integer
2
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
log_archive_min_succeed_dest integer
1
log_archive_start boolean
FALSE
log_archive_trace integer
0
remote_archive_enable string
true
standby_archive_dest string
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
?/dbs/arch
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
调整归档目录：
SQL> alter system set log_archive_dest_1='location=/u02/ora10g/oradata/archive' scope=both;
alter system set log_archive_dest_1='location=/u02/ora10g/oradata/archive' scope=both
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup
SQL> alter system set log_archive_dest_1='location=/u02/ora10g/oradata/archive' scope=spfile;
alter system set log_archive_dest_1='location=/u02/ora10g/oradata/archive' scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup
看下是我刚刚以pfile启动数据库因此必须要将数据库重新以spfile启动后修改
SQL> startup nomount
ORACLE instance started.
Total System Global Area 222298112 bytes
Fixed Size 1266680 bytes
Variable Size 75500552 bytes
Database Buffers 138412032 bytes
Redo Buffers 7118848 bytes
SQL> alter system set log_archive_dest_1='location=/u02/ora10g/oradata/archive' scope=both;
System altered.
SQL> startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
SQL> show parameter log_archive_dest_1
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
log_archive_dest_1 string
location=/u02/ora10g/oradata/a
rchive
log_archive_dest_10 string
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/ora10g/oradata/archive
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
OK，所有需要调整的参数都已经调整过来
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12350275/viewspace-688554/ ，如需转载，请注明出处，否则将追究法律责任。
