ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 数据文件位置调整

数据文件位置调整

原创 Linux操作系统 作者:lirenquan 时间:2011-03-07 13:53:21 0 删除 编辑

今天,在做创建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博客 ” ,链接:http://blog.itpub.net/12350275/viewspace-688554/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-12-24

  • 博文量
    24
  • 访问量
    110399