ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > oracle 存储整改全过程

oracle 存储整改全过程

原创 Linux操作系统 作者：伊思 时间：2010-03-25 22:21:13 0 删除 编辑

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

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;

    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


    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;

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

         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;

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> 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;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /oradata1/sysdata/o1_mf_548vz8
                                                 o7_.ctl
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
数据库装载完毕。
数据库已经打开。

ok，成功了。

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/23163255/viewspace-630415/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： oracle性能学习一
下一篇： oracle中修改用户名模式方法
请登录后发表评论 登录
全部评论
伊思

注册时间：2010-01-10

  • 博文量
    43
  • 访问量
    111058

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593