Oracle 学习：把数据库从ASM迁移到文件系统

原来的路径:

SQL> show parameter spfile ;

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------

spfile                               string      +DATA/orcl/spfileorcl.ora

create spfile='/u01/app/oracle/p1' from spfile;

shutdown immediate

su - grid

asmcmd

rm +data/orcl/spfileorcl.ora

删除$ORACLE_HOME/dbs/initorcl.ora

su - oracle

rm -rf $ORACLE_HOME/dbs/initorcl.ora

sqlplus as sysdba

create spfile from pfile='/u01/app/oracle/p1'

cd $ORACLE_HOME/dbs

startup

原来的路径：

SQL> show parameter control

NAME                                 TYPE        VALUE

------------------------------------ ----------- ---------

control_file_record_keep_time        integer     7

control_files                        string      +DATA/orcl/controlfile/current

                                                 .260.855760507, +FRA/orcl/cont

                                                 rolfile/current.256.855760511,

                                                  u01/app/oracle/control03.ctl

alter system set control_files='/u01/app/oracle/orcl/control01.ctl','/u01/app/oracle/orcl/control02.ctl'  scope=spfile;

关闭数据库到nomount

startup nomount;

SQL> show parameter control

NAME                                 TYPE        VALUE

------------------------------------ ----------- -----------

control_file_record_keep_time        integer     7

control_files                        string      /u01/app/oracle/orcl/control01

                                                 .ctl, u01/app/oracle/orcl/con

                                                 trol02.ctl

rman target

    #根据参数文件中指定控制文件路径来恢复

restore controlfile from '+DATA/orcl/controlfile/current.260.855760507';

启动到mount

alter database mount;

SQL> show parameter control

NAME                                 TYPE        VALUE

------------------------------------ ----------- -----------

control_file_record_keep_time        integer     7

control_files                        string      /u01/app/oracle/orcl/control01

                                                 .ctl, u01/app/oracle/orcl/con

                                                 trol02.ctl

SQL> select name from v$datafile ;

NAME

------------------------------------------ 

+DATA/orcl/datafile/system.256.853023133

+DATA/orcl/datafile/sysaux.257.853023133

+DATA/orcl/datafile/undotbs1.258.853023133

+DATA/orcl/datafile/user.bak

+DATA/orcl/datafile/example.265.853023375

+DGTEST/orcl/datafile/t1.256.855710613

+DATA/orcl/datafile/sts.267.855746975

/u01/app/oracle/test02.dbf

su -grid

asmcmd

cp +DATA/orcl/datafile/system.256.853023133   u01/app/oracle/orcl/system01.dbf

cp +DATA/orcl/datafile/sysaux.257.853023133 u01/app/oracle/orcl/sysaux.dbf

cp +DATA/orcl/datafile/undotbs1.258.853023133 u01/app/oracle/orcl/undotbs1.dbf

cp +DATA/orcl/datafile/user.bak u01/app/oracle/orcl/users.dbf

cp +DATA/orcl/datafile/example.265.853023375 u01/app/oracle/orcl/example.dbf

cp +DGTEST/orcl/datafile/t1.256.855710613 u01/app/oracle/orcl/t1.dbf

cp +DATA/orcl/datafile/sts.267.855746975 u01/app/oracle/orcl/sts.dbf

su - oracle

cp u01/app/oracle/test02.dbf u01/app/oracle/orcl/test02.dbf

新建脚本：

vim an.sql

写入如下内容：

alter database rename file  '+DATA/orcl/datafile/system.256.853023133'   to '/u01/app/oracle/orcl/system01.dbf';

alter database rename file  '+DATA/orcl/datafile/sysaux.257.853023133' to '/u01/app/oracle/orcl/sysaux.dbf';

alter database rename file '+DATA/orcl/datafile/undotbs1.258.853023133' to '/u01/app/oracle/orcl/undotbs1.dbf';

alter database rename file  '+DATA/orcl/datafile/user.bak' to '/u01/app/oracle/orcl/users.dbf';

alter database rename file  '+DATA/orcl/datafile/example.265.853023375' to '/u01/app/oracle/orcl/example.dbf';

alter database rename file  '+DGTEST/orcl/datafile/t1.256.855710613' to '/u01/app/oracle/orcl/t1.dbf';

alter database rename file  '+DATA/orcl/datafile/sts.267.855746975' to '/u01/app/oracle/orcl/sts.dbf';

alter database rename file  '/u01/app/oracle/test02.dbf' to '/u01/app/oracle/orcl/test02.dbf';

sqlplus as sysdba

SQL> @an.sql

检查：

SQL> select name from v$datafile ;

NAME

------------------

/u01/app/oracle/orcl/system01.dbf

/u01/app/oracle/orcl/sysaux.dbf

/u01/app/oracle/orcl/undotbs1.dbf

/u01/app/oracle/orcl/users.dbf

/u01/app/oracle/orcl/example.dbf

/u01/app/oracle/orcl/t1.dbf

/u01/app/oracle/orcl/sts.dbf

/u01/app/oracle/orcl/test02.dbf

SQL> alter database open;

删除原来所有的文件

su -grid

asmcmd

rm +data/orcl/datafile

rm +dgtest/orcl/datafile

原来的日志文件：

SQL> select  GROUP#, SEQUENCE#, STATUS from v$log;

    GROUP#  SEQUENCE# STATUS

---------- ---------- ----------------

         1         59 CURRENT

         2         56 INACTIVE

         3         57 INACTIVE

SQL> select member from v$logfile ;

MEMBER

--------------------------------------------

+DATA/orcl/onlinelog/group_3.263.853023339

+FRA/orcl/onlinelog/group_3.259.853023343

+DATA/orcl/onlinelog/group_2.262.853023327

+FRA/orcl/onlinelog/group_2.258.853023335

+DATA/orcl/onlinelog/group_1.261.853023319

+FRA/orcl/onlinelog/group_1.257.853023325

/u01/app/oracle/redo103.log

/u01/app/oracle/redo203.log

/u01/app/oracle/redo303.log

关闭数据库

shutdown immediate; 

su -grid

asmcmd

cp +DATA/orcl/onlinelog/group_3.263.853023339 /u01/app/oracle/orcl/red301.log

cp +FRA/orcl/onlinelog/group_3.259.853023343  /u01/app/oracle/orcl/red302.log

cp +DATA/orcl/onlinelog/group_2.262.853023327 /u01/app/oracle/orcl/red201.log

cp +FRA/orcl/onlinelog/group_2.258.853023335  /u01/app/oracle/orcl/red202.log

cp +DATA/orcl/onlinelog/group_1.261.853023319 /u01/app/oracle/orcl/red101.log

cp  +FRA/orcl/onlinelog/group_1.257.853023325 /u01/app/oracle/orcl/red102.log

su - oracle

cp /u01/app/oracle/redo103.log /u01/app/oracle/orcl/redo103.log

cp /u01/app/oracle/redo203.log /u01/app/oracle/orcl/redo203.log

cp /u01/app/oracle/redo303.log /u01/app/oracle/orcl/redo303.log

startup mount

alter database rename file  '+DATA/orcl/onlinelog/group_3.263.853023339' to  '/u01/app/oracle/orcl/red301.log'

alter database rename file '+FRA/orcl/onlinelog/group_3.259.853023343'  to '/u01/app/oracle/orcl/red302.log'

alter database rename file '+DATA/orcl/onlinelog/group_2.262.853023327' to '/u01/app/oracle/orcl/red201.log'

alter database rename file '+FRA/orcl/onlinelog/group_2.258.853023335'  to '/u01/app/oracle/orcl/red202.log'

alter database rename file '+DATA/orcl/onlinelog/group_1.261.853023319' to '/u01/app/oracle/orcl/red101.log'

alter database rename file  '+FRA/orcl/onlinelog/group_1.257.853023325' to '/u01/app/oracle/orcl/red102.log'

alter database open;

检查：

select member from v$logfile;

删除原来的文件

su - grid

asmcmd

rm +data/orcl/onlinelog

rm +fra/orcl/onlinelog

su - oracle

rm -rf /u01/app/oracle/redo*.log