同机同版本数据库从文件系统迁移至ASM实验

1. 实验环境说明

操作系统的版本是Oracle Linux 5内核是2.6.18-164.el5

[root@book ~]# uname -a

Linux book 2.6.18-164.el5 #1 SMP Thu Sep 3 02:16:47 EDT 2009 i686 i686 i386 GNU/Linux

Oracle数据库的版本是：

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

 

本机已经建立了ASM实例

[oracle@book ~]$ echo $ORACLE_SID

+ASM

[oracle@book ~]$ sqlplus  / as sysdba

 

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 3 23:00:14 2013

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

 

SQL> select name,total_mb,free_mb from v$asm_diskgroup;

 

NAME                             TOTAL_MB    FREE_MB

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

DGROUP01                             3579       2580

 

文件系统数据库实例说明

[oracle@book ~]$ export ORACLE_SID=practice

[oracle@book ~]$ echo $ORACLE_SID

practice

[oracle@book ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 3 23:04:22 2013

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

 

SQL> select name from v$database;

 

NAME

---------

PRACTICE

 

SQL> select name from v$datafile;

 

NAME

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

/u01/app/oracle/oradata/practice/system01.dbf

/u01/app/oracle/oradata/practice/undotbs01.dbf

/u01/app/oracle/oradata/practice/sysaux01.dbf

/u01/app/oracle/oradata/practice/users01.dbf

/u01/app/oracle/oradata/practice/users02.dbf

/u01/app/oracle/oradata/practice/tools01.dbf

/u01/app/oracle/oradata/practice/ts4drop01.dbf

 

7 rows selected.

 

SQL> select name from v$controlfile;

 

NAME

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

/u01/app/oracle/oradata/practice/control01.ctl

/u01/app/oracle/oradata/practice/control02.ctl

/u01/app/oracle/oradata/practice/control03.ctl

/home/oracle/flash_recover_area/HEADDB/control_file/control04.ctl

/home/oracle/flash_recover_area/HEADDB/control_file/control05.ctl

/home/oracle/flash_recover_area/HEADDB/control_file/control06.ctl

 

6 rows selected.

 

SQL> select name from v$tempfile;

 

NAME

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

/u01/app/oracle/oradata/practice/temp01.dbf

 

SQL> select member from v$logfile;

 

MEMBER

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

/u01/app/oracle/oradata/practice/redo031.log

/u01/app/oracle/oradata/practice/redo021.log

/u01/app/oracle/oradata/practice/redo011.log

/home/oracle/flash_recover_area/practice/logfile/redo012.log

/home/oracle/flash_recover_area/practice/logfile/redo022.log

/home/oracle/flash_recover_area/practice/logfile/redo032.log

 

6 rows selected.

2. 备份文件系统数据库

[oracle@book ~]$ echo $ORACLE_SID

practice

[oracle@book ~]$ rman target /

 

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Feb 3 22:26:27 2013

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

connected to target database: PRACTICE (DBID=2994901982)

RMAN> list backup;

 

 

RMAN> backup database plus archivelog delete all input;

 

Starting backup at 03-FEB-13

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=1 recid=1 stamp=804288718

input archive log thread=1 sequence=2 recid=2 stamp=804299193

input archive log thread=1 sequence=3 recid=3 stamp=804306343

input archive log thread=1 sequence=4 recid=4 stamp=804306840

input archive log thread=1 sequence=5 recid=5 stamp=804308863

channel ORA_DISK_1: starting piece 1 at 03-FEB-13

channel ORA_DISK_1: finished piece 1 at 03-FEB-13

piece handle=/u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_annnn_TAG20130203T222943_8jwx2v5p_.bkp tag=TAG20130203T222943 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:40

channel ORA_DISK_1: deleting archive log(s)

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_01_09/o1_mf_1_1_8gtwb349_.arc recid=1 stamp=804288718

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_01_10/o1_mf_1_2_8gv6kpxb_.arc recid=2 stamp=804299193

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_01_10/o1_mf_1_3_8gvfk6mw_.arc recid=3 stamp=804306343

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_01_10/o1_mf_1_4_8gvg0rt7_.arc recid=4 stamp=804306840

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_01_10/o1_mf_1_5_8gvhzzmw_.arc recid=5 stamp=804308863

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=1 recid=6 stamp=804747422

input archive log thread=1 sequence=2 recid=7 stamp=804749079

input archive log thread=1 sequence=3 recid=8 stamp=804749153

input archive log thread=1 sequence=4 recid=9 stamp=804749448

input archive log thread=1 sequence=5 recid=10 stamp=804749622

input archive log thread=1 sequence=6 recid=11 stamp=806452181

channel ORA_DISK_1: starting piece 1 at 03-FEB-13

channel ORA_DISK_1: finished piece 1 at 03-FEB-13

piece handle=/u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_annnn_TAG20130203T222943_8jwx6c7r_.bkp tag=TAG20130203T222943 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:10

channel ORA_DISK_1: deleting archive log(s)

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_01_15/o1_mf_1_1_8h8w8vxw_.arc recid=6 stamp=804747422

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_01_15/o1_mf_1_2_8h8xwqpo_.arc recid=7 stamp=804749079

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_01_15/o1_mf_1_3_8h8xz1dq_.arc recid=8 stamp=804749153

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_01_15/o1_mf_1_4_8h8y888s_.arc recid=9 stamp=804749448

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_01_15/o1_mf_1_5_8h8yfox8_.arc recid=10 stamp=804749622

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_02_03/o1_mf_1_6_8jwx29l6_.arc recid=11 stamp=806452181

Finished backup at 03-FEB-13

 

Starting backup at 03-FEB-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/u01/app/oracle/oradata/practice/system01.dbf

input datafile fno=00003 name=/u01/app/oracle/oradata/practice/sysaux01.dbf

input datafile fno=00002 name=/u01/app/oracle/oradata/practice/undotbs01.dbf

input datafile fno=00005 name=/u01/app/oracle/oradata/practice/users02.dbf

input datafile fno=00006 name=/u01/app/oracle/oradata/practice/tools01.dbf

input datafile fno=00007 name=/u01/app/oracle/oradata/practice/ts4drop01.dbf

input datafile fno=00004 name=/u01/app/oracle/oradata/practice/users01.dbf

channel ORA_DISK_1: starting piece 1 at 03-FEB-13

channel ORA_DISK_1: finished piece 1 at 03-FEB-13

piece handle=/u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_nnndf_TAG20130203T223152_8jwx6y2q_.bkp tag=TAG20130203T223152 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:03:43

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 03-FEB-13

channel ORA_DISK_1: finished piece 1 at 03-FEB-13

piece handle=/u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_ncsnf_TAG20130203T223152_8jwxg0j3_.bkp tag=TAG20130203T223152 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:23

Finished backup at 03-FEB-13

 

Starting backup at 03-FEB-13

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=7 recid=12 stamp=806452564

channel ORA_DISK_1: starting piece 1 at 03-FEB-13

channel ORA_DISK_1: finished piece 1 at 03-FEB-13

piece handle=/u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_annnn_TAG20130203T223607_8jwxgrv1_.bkp tag=TAG20130203T223607 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04

channel ORA_DISK_1: deleting archive log(s)

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_02_03/o1_mf_1_7_8jwxgng2_.arc recid=12 stamp=806452564

Finished backup at 03-FEB-13

 

RMAN> list backup;

 

 

List of Backup Sets

===================

 

BS Key  Size       Device Type Elapsed Time Completion Time

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

4       11.92M     DISK        00:00:07     03-FEB-13     

        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20130203T222943

        Piece Name: /u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_annnn_TAG20130203T222943_8jwx6c7r_.bkp

 

  List of Archived Logs in backup set 4

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    1       474409     10-JAN-13 499157     15-JAN-13

  1    2       499157     15-JAN-13 500057     15-JAN-13

  1    3       500057     15-JAN-13 500084     15-JAN-13

  1    4       500084     15-JAN-13 500198     15-JAN-13

  1    5       500198     15-JAN-13 500417     15-JAN-13

  1    6       500417     15-JAN-13 504497     03-FEB-13

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

5       Full    512.37M    DISK        00:03:38     03-FEB-13     

        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20130203T223152

        Piece Name: /u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_nnndf_TAG20130203T223152_8jwx6y2q_.bkp

  List of Datafiles in backup set 5

  File LV Type Ckp SCN    Ckp Time  Name

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

  1       Full 504516     03-FEB-13 /u01/app/oracle/oradata/practice/system01.dbf

  2       Full 504516     03-FEB-13 /u01/app/oracle/oradata/practice/undotbs01.dbf

  3       Full 504516     03-FEB-13 /u01/app/oracle/oradata/practice/sysaux01.dbf

  4       Full 504516     03-FEB-13 /u01/app/oracle/oradata/practice/users01.dbf

  5       Full 504516     03-FEB-13 /u01/app/oracle/oradata/practice/users02.dbf

  6       Full 504516     03-FEB-13 /u01/app/oracle/oradata/practice/tools01.dbf

  7       Full 504516     03-FEB-13 /u01/app/oracle/oradata/practice/ts4drop01.dbf

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

6       Full    6.80M      DISK        00:00:12     03-FEB-13     

        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20130203T223152

        Piece Name: /u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_ncsnf_TAG20130203T223152_8jwxg0j3_.bkp

  Control File Included: Ckp SCN: 504566       Ckp time: 03-FEB-13

  SPFILE Included: Modification time: 03-FEB-13

 

BS Key  Size       Device Type Elapsed Time Completion Time

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

7       484.00K    DISK        00:00:02     03-FEB-13     

        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20130203T223607

        Piece Name: /u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_annnn_TAG20130203T223607_8jwxgrv1_.bkp

 

  List of Archived Logs in backup set 7

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    7       504497     03-FEB-13 504603     03-FEB-13

3. 关闭文件系统数据库实例

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> create pfile from spfile;

 

File created.

后面我们要修改控制文件的位置指向，SPFILE是二进制文件，无法直接修改，我们先创建出一个pfile文件，然后修改pfile。当然也可以在还没有关闭数据库的时候执行如下命令：

Alter database set control_files=’+ DGROUP01’ scope=spfile;

然后再关闭数据库，设置即可生效。这里采用直接修改pfile的方式。

4. 修改参数文件

首先把文件系统的SPFILE备份

[oracle@book dbs]$ mv spfilepractice.ora spfilepractice.orabak

修改practice实例的参数文件，使其控制文件指向ASM

[oracle@book dbs]$ vi initpractice.ora

practice.__db_cache_size=494927872

practice.__java_pool_size=4194304

practice.__large_pool_size=4194304

practice.__shared_pool_size=226492416

practice.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/practice/adump'

*.background_dump_dest='/u01/app/oracle/admin/practice/bdump'

*.compatible='10.2.0.1.0'

*.control_files='+DGROUP01/practice/controlfile/control01.ctl','+DGROUP01/practice/controlfile/control02.ctl','+DGROUP01/practice/controlfile/control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/practice/cdump'

*.db_block_size=8192

*.db_domain='oracle.com'

*.db_file_multiblock_read_count=16

*.db_name='practice'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=5368709120

*.dispatchers='(PROTOCOL=TCP) (SERVICE=practiceXDB)'

*.fast_start_mttr_target=3600

*.job_queue_processes=10

*.log_archive_format='ARC%S_%R.%T.dbf'

*.open_cursors=300

*.pga_aggregate_target=314572800

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=734003200

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/practice/udump'

5. 检查ASM实例的状态

[oracle@book ~]$ echo $ORACLE_SID

+ASM

[oracle@book ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 3 23:39:12 2013

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

 

SQL> select name,total_mb,free_mb from v$asm_diskgroup;

 

NAME                             TOTAL_MB    FREE_MB

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

DGROUP01                             3579       2580

6. 恢复待迁移实例的控制文件

[oracle@book ~]$ echo $ORACLE_SID

practice

[oracle@book ~]$ rman target /

 

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Feb 3 23:42:13 2013

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

connected to target database (not started)

 

RMAN> startup nomount;

 

Oracle instance started

 

Total System Global Area     734003200 bytes

 

Fixed Size                     1221564 bytes

Variable Size                234884164 bytes

Database Buffers             494927872 bytes

Redo Buffers                   2969600 bytes

 

RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_ncsnf_TAG20130203T223152_8jwxg0j3_.bkp';

 

Starting restore at 03-FEB-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

 

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:30

output filename=+DGROUP01/practice/controlfile/control01.ctl

output filename=+DGROUP01/practice/controlfile/control02.ctl

output filename=+DGROUP01/practice/controlfile/control03.ctl

Finished restore at 03-FEB-13

我们再去ASM磁盘组中看看控制文件是否已经迁移至ASM中

[oracle@book ~]$ asmcmd

ASMCMD> pwd

+

ASMCMD> ls /DGROUP01/PRACTICE/CONTROLFILE/

Current.266.806456845

control01.ctl

control02.ctl

control03.ctl

current.267.806456853

current.268.806456857

这些控制文件已经迁移成功。

7. 恢复数据库

控制文件恢复以后，就可以把数据库起到mount状态。

SQL> alter database mount;

 

Database altered.

在RMAN窗口中执行如下命令来恢复数据文件：

RMAN> run{

2> set newname for datafile 1 to '+DGROUP01';

3> set newname for datafile 2 to '+DGROUP01';

4> set newname for datafile 3 to '+DGROUP01';

5> set newname for datafile 4 to '+DGROUP01';

6> set newname for datafile 5 to '+DGROUP01';

7> set newname for datafile 6 to '+DGROUP01';

8> set newname for datafile 7 to '+DGROUP01';

9> restore database;

10> switch datafile all;

11> recover database;

12> }

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

Starting restore at 04-FEB-13

using channel ORA_DISK_1

 

channel ORA_DISK_1: restoring datafile 00002

input datafile copy recid=5 stamp=806463586 filename=+DGROUP01/practice/datafile/undotbs1.271.806463507

destination for restore of datafile 00002: +DGROUP01

channel ORA_DISK_1: copied datafile copy of datafile 00002

output filename=+DGROUP01/practice/datafile/undotbs1.270.806463833 recid=6 stamp=806463842

channel ORA_DISK_1: restoring datafile 00004

input datafile copy recid=1 stamp=806463533 filename=+DGROUP01/practice/datafile/users.274.806463523

destination for restore of datafile 00004: +DGROUP01

channel ORA_DISK_1: copied datafile copy of datafile 00004

output filename=+DGROUP01/practice/datafile/users.269.806463849 recid=7 stamp=806463849

channel ORA_DISK_1: restoring datafile 00006

input datafile copy recid=3 stamp=806463581 filename=+DGROUP01/practice/datafile/tools.272.806463517

destination for restore of datafile 00006: +DGROUP01

channel ORA_DISK_1: copied datafile copy of datafile 00006

output filename=+DGROUP01/practice/datafile/tools.275.806463851 recid=8 stamp=806463852

channel ORA_DISK_1: restoring datafile 00007

input datafile copy recid=4 stamp=806463582 filename=+DGROUP01/practice/datafile/ts4frop.273.806463521

destination for restore of datafile 00007: +DGROUP01

channel ORA_DISK_1: copied datafile copy of datafile 00007

output filename=+DGROUP01/practice/datafile/ts4frop.276.806463855 recid=9 stamp=806463855

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to +DGROUP01

restoring datafile 00003 to +DGROUP01

restoring datafile 00005 to +DGROUP01

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_nnndf_TAG20130203T223152_8jwx6y2q_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_nnndf_TAG20130203T223152_8jwx6y2q_.bkp tag=TAG20130203T223152

channel ORA_DISK_1: restore complete, elapsed time: 00:05:16

Finished restore at 04-FEB-13

 

datafile 1 switched to datafile copy

input datafile copy recid=13 stamp=806464172 filename=+DGROUP01/practice/datafile/system.277.806463857

datafile 2 switched to datafile copy

input datafile copy recid=14 stamp=806464173 filename=+DGROUP01/practice/datafile/undotbs1.270.806463833

datafile 3 switched to datafile copy

input datafile copy recid=15 stamp=806464173 filename=+DGROUP01/practice/datafile/sysaux.278.806463859

datafile 4 switched to datafile copy

input datafile copy recid=16 stamp=806464173 filename=+DGROUP01/practice/datafile/users.269.806463849

datafile 5 switched to datafile copy

input datafile copy recid=17 stamp=806464173 filename=+DGROUP01/practice/datafile/users.279.806463861

datafile 6 switched to datafile copy

input datafile copy recid=18 stamp=806464173 filename=+DGROUP01/practice/datafile/tools.275.806463851

datafile 7 switched to datafile copy

input datafile copy recid=19 stamp=806464173 filename=+DGROUP01/practice/datafile/ts4frop.276.806463855

 

Starting recover at 04-FEB-13

using channel ORA_DISK_1

 

starting media recovery

 

archive log thread 1 sequence 7 is already on disk as file /home/oracle/flash_recover_area/practice/logfile/redo012.log

archive log thread 1 sequence 8 is already on disk as file /home/oracle/flash_recover_area/practice/logfile/redo022.log

archive log filename=/home/oracle/flash_recover_area/practice/logfile/redo012.log thread=1 sequence=7

archive log filename=/home/oracle/flash_recover_area/practice/logfile/redo022.log thread=1 sequence=8

media recovery complete, elapsed time: 00:00:25

Finished recover at 04-FEB-13

数据文件都恢复之后就可以打开数据库了，因为使用的是备份集中恢复回来的控制文件，打开数据库时，必须要加上resetlogs选项。

SQL> select * from v$recover_file;

 

no rows selected

 

SQL> alter database open resetlogs;

 

Database altered.

8. 迁移日志文件

首先查看当前的日志文件的状态

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

 

    GROUP# STATUS

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

         1 UNUSED

         2 CURRENT

         3 UNUSED

 

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

 

    GROUP# MEMBER

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

         3 /u01/app/oracle/oradata/practice/redo031.log

         2 /u01/app/oracle/oradata/practice/redo021.log

         1 /u01/app/oracle/oradata/practice/redo011.log

         1 /home/oracle/flash_recover_area/practice/logfile/redo012.log

         2 /home/oracle/flash_recover_area/practice/logfile/redo022.log

         3 /home/oracle/flash_recover_area/practice/logfile/redo032.log

从以上信息判断，目前日志文件都在文件系统中，并且只有日志组2部可以删除。我们先删除可以删除的组，然后在ASM中添加新的日志组，以此达到迁移的目的。

SQL> alter database drop logfile group 1;

 

Database altered.

 

SQL> alter database add logfile group 1 '+DGROUP01' size 50M;

 

Database altered.

 

SQL> alter database drop logfile group 3;

 

Database altered.

 

SQL> alter database add logfile group 3 '+DGROUP01' size 50M;

 

Database altered.

 

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

 

    GROUP# STATUS

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

         1 UNUSED

         2 CURRENT

         3 UNUSED

现在还需要删除group2，先执行一次日志切换

SQL> alter system switch logfile;

 

System altered.

 

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

 

    GROUP# STATUS

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

         1 CURRENT

         2 ACTIVE

         3 UNUSED

现在group2是active状态，让系统生成以此检查点

SQL> alter system checkpoint;

 

System altered.

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

 

    GROUP# STATUS

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

         1 CURRENT

         2 INACTIVE

         3 UNUSED

现在可以删除了。

SQL> alter database drop logfile group 2;

 

Database altered.

 

SQL> alter database add logfile group 2 '+DGROUP01' size 50M;

 

Database altered.

 

SQL> select member from v$logfile;

 

MEMBER

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

+DGROUP01/practice/onlinelog/group_3.281.806469935

+DGROUP01/practice/onlinelog/group_2.282.806470267

+DGROUP01/practice/onlinelog/group_1.280.806465581

9. 迁移临时文件

查看临时文件当前的位置

SQL> select name from v$tempfile;

 

NAME

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

/u01/app/oracle/oradata/practice/temp01.dbf

可以先给temp表空间增加一个位于ASM的临时数据文件，再删除位于文件系统的临时文件。

SQL> alter tablespace temp add tempfile '+DGROUP01' size 20M;

 

Tablespace altered.

 

SQL> select name from v$tempfile;

 

NAME

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

/u01/app/oracle/oradata/practice/temp01.dbf

+DGROUP01/practice/tempfile/temp.283.806470587

 

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

 

Tablespace altered.

 

SQL> select name from v$tempfile;

 

NAME

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

+DGROUP01/practice/tempfile/temp.283.806470587

10. 检查迁移结果

SQL> select name  from v$datafile;

 

NAME

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

+DGROUP01/practice/datafile/system.277.806463857

+DGROUP01/practice/datafile/undotbs1.270.806463833

+DGROUP01/practice/datafile/sysaux.278.806463859

+DGROUP01/practice/datafile/users.269.806463849

+DGROUP01/practice/datafile/users.279.806463861

+DGROUP01/practice/datafile/tools.275.806463851

+DGROUP01/practice/datafile/ts4frop.276.806463855

 

7 rows selected.

 

SQL> select name from v$controlfile;

 

NAME

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

+DGROUP01/practice/controlfile/control01.ctl

+DGROUP01/practice/controlfile/control02.ctl

+DGROUP01/practice/controlfile/control03.ctl

 

SQL> select member from v$logfile;

 

MEMBER

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

+DGROUP01/practice/onlinelog/group_3.281.806469935

+DGROUP01/practice/onlinelog/group_2.282.806470267

+DGROUP01/practice/onlinelog/group_1.280.806465581

 

SQL> select name from v$tempfile;

 

NAME

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

+DGROUP01/practice/tempfile/temp.283.806470587

至此，数据文件、控制文件、日志文件、临时文件都已迁移至ASM中，迁移完成