Home >Database >Mysql Tutorial > ORACLE数据库文件系统迁移到ASM磁盘

ORACLE数据库文件系统迁移到ASM磁盘

WBOY
WBOYOriginal
2016-06-07 17:36:341326browse

1、登陆+ASM实例查看ASM磁盘是否正[oracle@rhel5~]$exportORACLE_SID=+ASM[oracle@rhel5~]$sqlplus/assysdbaSQLselectname,statefromv$asm_diskgroup; ..


1、登陆+ASM实例查看ASM磁盘是否正

[oracle@rhel5 ~]$ export ORACLE_SID=+ASM
[oracle@rhel5 ~]$ sqlplus  / as sysdba

SQL> select name,state from  v$asm_diskgroup;                                                                


NAME                        STATE
---------------           -----------
DG1                         MOUNTED

2、登陆数据库prod
[oracle@rhel5 ~]$ export ORACLE_SID=prod
[oracle@rhel5 ~]$ sqlplus / as sysdba

a、查看控制文件
SQL> show parameter control_files;

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
control_files                        string
/u01/app/oracle/oradata/prod/c
ontrol01.ctl, /u01/app/oracle/
oradata/prod/control02.ctl, /u
01/app/oracle/oradata/prod/con
trol03.ctl
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/control01.ctl
/u01/app/oracle/oradata/prod/control02.ctl
/u01/app/oracle/oradata/prod/control03.ctl


b、查看db_create_file_dest参数
SQL> show parameter db_create_file_dest;

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_create_file_dest                  string


c、修改控制文件的位置

SQL>  alter system set control_files='+DG1' scope=spfile;

System altered.

SQL> alter system set db_create_file_dest='+DG1' scope=spfile;

System altered.


d、关闭数据库

SQL> shutdown immediate;

3、登陆rman

[oracle@rhel5 ~]$ export ORACLE_SID=prod
[oracle@rhel5 ~]$ rman target /

a、利用rman迁移目标数据库控制文件和数据文件

RMAN> startup nomount;

b、利用rman将文件系统上的控制文件重建控制文件到ASM磁盘的DG1上
RMAN> restore controlfile from '/u01/app/oracle/oradata/prod/control01.ctl';

c、利用rman复制数据库文件到ASM磁盘组DG1上
RMAN> alter database mount;
RMAN> run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
backup as copy database format '+DG1';
}

d、利用RMAN的SWITCH 命令修改控制文件内数据文件的指针,使其指向新位置
RMAN> switch database to copy;
RMAN> recover database;
RMAN> alter database open;


4、登陆数据库prod
[oracle@rhel5 ~]$ export ORACLE_SID=prod
[oracle@rhel5 ~]$ sqlplus / as sysdba

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DG1/prod/controlfile/backup.271.842150617

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DG1/prod/datafile/system.272.842151185
+DG1/prod/datafile/undotbs1.275.842151191
+DG1/prod/datafile/sysaux.273.842151185
+DG1/prod/datafile/users.276.842151211
+DG1/prod/datafile/example.274.842151187

a、迁移temp文件
SQL> select name,status,enabled from v$tempfile;

NAME
--------------------------------------------------------------------------------
STATUS                ENABLED
--------------------- ------------------------------
/u01/app/oracle/oradata/prod/temp01.dbf
ONLINE                READ WRITE

由于temp文件没有可用的数据只是缓存数据,temp可以直接添加一个新的temp文件,然后将老的temp文件删除
SQL> alter tablespace temp add tempfile '+DG1';

Tablespace altered.

SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/prod/temp01.dbf';

Tablespace altered.


SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DG1/prod/tempfile/temp.279.842151759

b、迁移日志文件,在DG1创建新的日志文件,然后将老的文件删除

SQL> select group#,member from v$logfile;

   GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
        3
/u01/app/oracle/oradata/prod/redo03.log

        2
/u01/app/oracle/oradata/prod/redo02.log

        1
/u01/app/oracle/oradata/prod/redo01.log



SQL> alter database add logfile '+DG1' size 51m;

Database altered.

SQL> alter database add logfile '+DG1' size 51m;

Database altered.

SQL> alter database add logfile '+DG1' size 51m;

Database altered.


删除的时候需要日志文件组状态为inactive状态,不过删除不了,,说明日志文件组不是inactive状态

SQL> select group#,status from v$log;

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn