oracle数据库物理文件整改全过程

原创

marvelyu 2010-03-30 13:31:02 博主文章分类：Oracle ©著作权

文章标签 oracle 职场休闲 oracle 数据库文件移动 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者marvelyu的原创作品，请联系作者获取转载授权，否则将追究法律责任

在一个项目中需要将一个数据库中的物理文件都从本地磁盘转移到磁盘阵列中，其实整改过程就是这么简单：

实例从修改控制文件开始操作：

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/usr2/dbfile/hbzydb/HBZYDB/controlfile/o1_mf_548vz8o7_.ctl

SQL> create spfile from pfile;

SQL> startup pfile='/opt/app/oracle/product/10.2.0.1/dbs/inithbzydb.ora'
ORACLE 例程已经启动。

Total System Global Area 3154116608 bytes
Fixed Size                  1997008 bytes
Variable Size             620758832 bytes
Database Buffers         2516582400 bytes
Redo Buffers               14778368 bytes
数据库装载完毕。
数据库已经打开。
SQL> show parameter control;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /oradata1/sysdata/o1_mf_548vz8
                                                 o7_.ctl
SQL> create spfile from pfile;

文件已创建。

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 3154116608 bytes
Fixed Size                  1997008 bytes
Variable Size             620758832 bytes
Database Buffers         2516582400 bytes
Redo Buffers               14778368 bytes
数据库装载完毕。
数据库已经打开。
SQL> show parameter pfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /opt/app/oracle/product/10.2.0
                                                 .1/dbs/spfilehbzydb.ora
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/usr2/dbfile/hbzydb/HBZYDB/datafile/o1_mf_system_548vzhnv_.dbf
/usr2/dbfile/hbzydb/HBZYDB/datafile/o1_mf_undotbs1_548w0nts_.dbf
/usr2/dbfile/hbzydb/HBZYDB/datafile/o1_mf_sysaux_548w1pxo_.dbf
/usr2/dbfile/hbzydb/HBZYDB/datafile/o1_mf_users_548w22pw_.dbf
/usr2/dbfile/dbdata/hbzydb/HBZHZY01.dbf
/usr2/dbfile/dbdata/hbzydb/INDX01.dbf
/usr2/dbfile/dbdata/hbzydb/TOOLS01.dbf
/usr2/dbfile/dbdata/hbzydb/UNDOTBS01.dbf

已选择8行。

SQL> !mkdir /oradata2/hbzydb

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> !cp /usr2/dbfile/hbzydb/HBZYDB/datafile/o1_mf_system_548vzhnv_.dbf /oradata2/hbzydb/o1_mf_system_548vzhnv_.dbf

SQL> !cp /usr2/dbfile/hbzydb/HBZYDB/datafile/o1_mf_undotbs1_548w0nts_.dbf /oradata2/hbzydb/o1_mf_undotbs1_548w0nts_.dbf

SQL> !cp /usr2/dbfile/hbzydb/HBZYDB/datafile/o1_mf_sysaux_548w1pxo_.dbf /oradata2/hbzydb/o1_mf_sysaux_548w1pxo_.dbf

SQL> !cp /usr2/dbfile/hbzydb/HBZYDB/datafile/o1_mf_users_548w22pw_.dbf /oradata2/hbzydb/o1_mf_users_548w22pw_.dbf

SQL> !cp /usr2/dbfile/dbdata/hbzydb/HBZHZY01.dbf /oradata2/hbzydb/HBZHZY01.dbf

SQL> !cp /usr2/dbfile/dbdata/hbzydb/INDX01.dbf /oradata2/hbzydb/INDX01.dbf

SQL> !cp /usr2/dbfile/dbdata/hbzydb/TOOLS01.dbf /oradata2/hbzydb/TOOLS01.dbf

SQL> !cp /usr2/dbfile/dbdata/hbzydb/UNDOTBS01.dbf /oradata2/hbzydb/UNDOTBS01.dbf

SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area 3154116608 bytes
Fixed Size                  1997008 bytes
Variable Size             620758832 bytes
Database Buffers         2516582400 bytes
Redo Buffers               14778368 bytes
数据库装载完毕。
SQL> alter database rename file '/usr2/dbfile/hbzydb/HBZYDB/datafile/o1_mf_system_548vzhnv_.dbf' to '/oradata2/hbzydb/o1_mf_system_548vzhnv_.dbf';

数据库已更改。

SQL> alter database rename file '/usr2/dbfile/hbzydb/HBZYDB/datafile/o1_mf_undotbs1_548w0nts_.dbf' to '/oradata2/hbzydb/o1_mf_undotbs1_548w0nts_.dbf';

数据库已更改。

SQL> alter database rename file '/usr2/dbfile/hbzydb/HBZYDB/datafile/o1_mf_sysaux_548w1pxo_.dbf' to '/oradata2/hbzydb/o1_mf_sysaux_548w1pxo_.dbf';

数据库已更改。

SQL> alter database rename file '/usr2/dbfile/hbzydb/HBZYDB/datafile/o1_mf_users_548w22pw_.dbf' to '/oradata2/hbzydb/o1_mf_users_548w22pw_.dbf';

数据库已更改。

SQL> alter database rename file '/usr2/dbfile/dbdata/hbzydb/HBZHZY01.dbf' to '/oradata2/hbzydb/HBZHZY01.dbf';

数据库已更改。

SQL> alter database rename file '/usr2/dbfile/dbdata/hbzydb/INDX01.dbf' to '/oradata2/hbzydb/INDX01.dbf';

数据库已更改。

SQL> alter database rename file '/usr2/dbfile/dbdata/hbzydb/TOOLS01.dbf' to '/oradata2/hbzydb/TOOLS01.dbf';

数据库已更改。

SQL> alter database rename file '/usr2/dbfile/dbdata/hbzydb/UNDOTBS01.dbf' to '/oradata2/hbzydb/UNDOTBS01.dbf';

数据库已更改。

SQL> alter database open;

数据库已更改。

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata2/hbzydb/o1_mf_system_548vzhnv_.dbf
/oradata2/hbzydb/o1_mf_undotbs1_548w0nts_.dbf
/oradata2/hbzydb/o1_mf_sysaux_548w1pxo_.dbf
/oradata2/hbzydb/o1_mf_users_548w22pw_.dbf
/oradata2/hbzydb/HBZHZY01.dbf
/oradata2/hbzydb/INDX01.dbf
/oradata2/hbzydb/TOOLS01.dbf
/oradata2/hbzydb/UNDOTBS01.dbf

已选择8行。

SQL> select FILE_NAME,TABLESPACE_NAME,BYTES,USER_BYTES from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BYTES USER_BYTES
------------------------------ ---------- ----------
/usr2/dbfile/hbzydb/HBZYDB/datafile/o1_mf_temp_548w1vg4_.tmp
TEMP 1073741824 1072693248

SQL> select username,temporary_tablespace from dba_users;

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW                      TEMP
SYS                            TEMP
SYSTEM                         TEMP
DBSNMP                         TEMP
SYSMAN                         TEMP
HBZHZY                         TEMP
OUTLN                          TEMP
WMSYS                          TEMP
ORDSYS                         TEMP
EXFSYS                         TEMP
XDB                            TEMP

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SI_INFORMTN_SCHEMA             TEMP
ORDPLUGINS                     TEMP
MDSYS                          TEMP
ANONYMOUS                      TEMP
TSMSYS                         TEMP
DIP                            TEMP

已选择17行。

SQL> !mkdir /oradata3/hbzydb

SQL> create temporary tablespace temp01 tempfile '/oradata3/hbzydb/temp01.dbf' size 4000M autoextend off;

表空间已创建。
SQL> alter database default temporary tablespace temp01;

数据库已更改。

SQL> select username,temporary_tablespace from dba_users;

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW                      TEMP01
SYS                            TEMP01
SYSTEM                         TEMP01
DBSNMP                         TEMP01
SYSMAN                         TEMP01
HBZHZY                         TEMP01
OUTLN                          TEMP01
WMSYS                          TEMP01
ORDSYS                         TEMP01
EXFSYS                         TEMP01
XDB                            TEMP01

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SI_INFORMTN_SCHEMA             TEMP01
ORDPLUGINS                     TEMP01
MDSYS                          TEMP01
ANONYMOUS                      TEMP01
TSMSYS                         TEMP01
DIP                            TEMP01

已选择17行。

SQL> drop tablespace temp including contents and datafiles;

表空间已删除。

SQL> !mkdir /redo_log1/hbzydb

SQL> !mkdir /redo_log2/hbzydb

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- -------------------
         1          1       1387   52428800          1 NO INACTIVE
     16282625 2010-03-20 23:00:53

2 1 1388 52428800 1 NO INACTIVE
16326793 2010-03-21 22:27:07

3 1 1389 52428800 1 NO CURRENT
16369044 2010-03-22 20:23:46

SQL> alter database add logfile member group 4 ('/redo_log1/hbzydb/redo01a.log',/redo_log2/hbzydb/redo01b.log') size 128M;
alter database add logfile member group 4 ('/redo_log1/hbzydb/redo01a.log',/redo_log2/hbzydb/redo01b.log') size 128M
*
第 1 行出现错误:
ORA-02236: 文件名无效

SQL> alter database add logfile group 4 ('/redo_log1/hbzydb/redo01a.log','/redo_log2/hbzydb/redo01b.log') size 128M;

数据库已更改。

SQL> select * from v$log;

2 1 1388 52428800 1 NO INACTIVE
16326793 2010-03-21 22:27:07

3 1 1389 52428800 1 NO CURRENT
16369044 2010-03-22 20:23:46

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- -------------------
         4          1          0 134217728          2 YES UNUSED
            0

SQL> alter database drop logfile group 2;

数据库已更改。

SQL> alter database add logfile group 1 ('/redo_log1/hbzydb/redo02a.log','/redo_log1/hbzydb/redo02b.log') size 128m;

数据库已更改。

SQL> alter database add logfile group 2 ('/redo_log1/hbzydb/redo03a.log','/redo_log2/hbzydb/redo03b.log') size 128m;

数据库已更改。

SQL> select * from v$log;

2 1 0 134217728 2 YES UNUSED
0

3 1 1389 52428800 1 NO CURRENT
16369044 2010-03-22 20:23:46

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- -------------------
         4          1          0 134217728          2 YES UNUSED
            0

SQL> alter system switch logfile;

系统已更改。

SQL> alter system checkpoint;

系统已更改。

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- -------------------
         1          1       1390 134217728          2 NO CURRENT
     16379379 2010-03-25 21:26:59

2 1 0 134217728 2 YES UNUSED
0

3 1 1389 52428800 1 NO INACTIVE
16369044 2010-03-22 20:23:46

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- -------------------
         4          1          0 134217728          2 YES UNUSED
            0

SQL> alter database drop logfile group 4;

数据库已更改。

SQL> alter database drop logfile group 3;

数据库已更改。

SQL> alter database add logfile group 3 ('/redo_log1/hbzydb/redo04a.log','/redo_log2/hbzydb/redo04b.log') size 128M;

数据库已更改。

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- -------------------
         1          1       1390 134217728          2 NO CURRENT
     16379379 2010-03-25 21:26:59

2 1 0 134217728 2 YES UNUSED
0

3 1 0 134217728 2 YES UNUSED
0

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/redo_log1/hbzydb/redo02a.log
/redo_log1/hbzydb/redo02b.log
/redo_log1/hbzydb/redo04a.log
/redo_log2/hbzydb/redo04b.log
/redo_log1/hbzydb/redo03a.log
/redo_log2/hbzydb/redo03b.log

已选择6行。

SQL> select name from v$datafiles;
select name from v$datafiles

SQL> select name from v$datafile;

已选择8行。

SQL> alter system set db_create_file_dest='/oradata2/hbzydb';

系统已更改。

SQL> show parameter db_create_file_dest;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /oradata2/hbzydb
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oradata1/sysdata/o1_mf_548vz8o7_.ctl

SQL> select FILE_NAME,TABLESPACE_NAME,BYTES,USER_BYTES from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BYTES USER_BYTES
------------------------------ ---------- ----------
/oradata3/hbzydb/temp01.dbf
TEMP01 4194304000 4193255424

SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
hbzydb OPEN

SQL>
SQL> show parameter control;