Oracle普通库迁移至ASM存储

--查看当前库文件位置

SQL> select name from v$datafile

  2  union

  3  select member from v$logfile

  4  union

  5  select name from v$controlfile

  6  union

  7  select name from v$tempfile;

 

NAME

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

/u01/oradata/tos/control01.ctl

/u01/oradata/tos/control02.ctl

/u01/oradata/tos/control03.ctl

/u01/oradata/tos/example01.dbf

/u01/oradata/tos/redo01.log

/u01/oradata/tos/redo02.log

/u01/oradata/tos/redo03.log

/u01/oradata/tos/sysaux01.dbf

/u01/oradata/tos/system01.dbf

/u01/oradata/tos/temp01.dbf

/u01/oradata/tos/undotbs01.dbf

/u01/oradata/tos/users01.dbf

/u01/oradata/tos/xff01.dbf

/u01/oradata/tos/xff02.dbf

 

14 rows selected.

 

--备份控制文件

SQL> alter database backup controlfile to '/u01/control.ctl';

 

Database altered.

 

--修改spfile中的控制文件至asm

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

 

System altered.

 

--修改db_recovery_file_dest至asm中

SQL> alter system set db_recovery_file_dest='+FLASHBACK';

 

System altered.

 

--修改归档日志至asm中

SQL> alter system set log_archive_dest_1='location=+DATA';

 

System altered.

 

--关闭数据库

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

--登录rman

[oracle@localhost ~]$ rman target /

 

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 08:02:37 2011

 

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

 

connected to target database (not started)

 

--启动数据库至nomount状态

RMAN> startup nomount;

 

Oracle instance started

 

Total System Global Area     167772160 bytes

 

Fixed Size                     1260672 bytes

Variable Size                 67109760 bytes

Database Buffers              92274688 bytes

Redo Buffers                   7127040 bytes

 

--恢复控制文件

RMAN>  restore controlfile from '/u01/control.ctl';  

 

Starting restore at 27-JUN-11

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: copied control file copy

output filename=+DATA/tos/controlfile/current.268.754905785

Finished restore at 27-JUN-11

 

--打开数据库至mount状态

RMAN> alter database mount;

 

database mounted

released channel: ORA_DISK_1

 

--copy数据文件至asm

RMAN> backup as copy database format '+DATA';

 

Starting backup at 27-JUN-11

Starting implicit crosscheck backup at 27-JUN-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

Finished implicit crosscheck backup at 27-JUN-11

 

Starting implicit crosscheck copy at 27-JUN-11

using channel ORA_DISK_1

Finished implicit crosscheck copy at 27-JUN-11

 

searching for all files in the recovery area

cataloging files...

no files cataloged

 

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile fno=00001 name=/u01/oradata/tos/system01.dbf

output filename=+DATA/tos/datafile/system.270.754905833 tag=TAG20110627T080352 recid=2 stamp=754905928

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35

channel ORA_DISK_1: starting datafile copy

input datafile fno=00003 name=/u01/oradata/tos/sysaux01.dbf

output filename=+DATA/tos/datafile/sysaux.271.754905929 tag=TAG20110627T080352 recid=3 stamp=754905990

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05

channel ORA_DISK_1: starting datafile copy

input datafile fno=00005 name=/u01/oradata/tos/example01.dbf

output filename=+DATA/tos/datafile/example.272.754905995 tag=TAG20110627T080352 recid=4 stamp=754906010

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26

channel ORA_DISK_1: starting datafile copy

input datafile fno=00002 name=/u01/oradata/tos/undotbs01.dbf

output filename=+DATA/tos/datafile/undotbs1.273.754906021 tag=TAG20110627T080352 recid=5 stamp=754906025

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile fno=00006 name=/u01/oradata/tos/xff01.dbf

output filename=+DATA/tos/datafile/xff.274.754906027 tag=TAG20110627T080352 recid=6 stamp=754906029

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile copy

input datafile fno=00007 name=/u01/oradata/tos/xff02.dbf

output filename=+DATA/tos/datafile/xff.275.754906031 tag=TAG20110627T080352 recid=7 stamp=754906032

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile copy

input datafile fno=00004 name=/u01/oradata/tos/users01.dbf

output filename=+DATA/tos/datafile/users.276.754906035 tag=TAG20110627T080352 recid=8 stamp=754906035

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04

Finished backup at 27-JUN-11

 

RMAN-06497: WARNING: control file is not current, control file autobackup skipped

 

--恢复数据库

RMAN>  recover database;

 

Starting recover at 27-JUN-11

using channel ORA_DISK_1

 

starting media recovery

 

archive log thread 1 sequence 3 is already on disk as file /u01/oradata/tos/redo02.log

archive log filename=/u01/oradata/tos/redo02.log thread=1 sequence=3

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

Finished recover at 27-JUN-11

 

--修改数据库中数据文件路径

RMAN> switch database to copy;

 

datafile 1 switched to datafile copy "+DATA/tos/datafile/system.270.754905833"

datafile 2 switched to datafile copy "+DATA/tos/datafile/undotbs1.273.754906021"

datafile 3 switched to datafile copy "+DATA/tos/datafile/sysaux.271.754905929"

datafile 4 switched to datafile copy "+DATA/tos/datafile/users.276.754906035"

datafile 5 switched to datafile copy "+DATA/tos/datafile/example.272.754905995"

datafile 6 switched to datafile copy "+DATA/tos/datafile/xff.274.754906027"

datafile 7 switched to datafile copy "+DATA/tos/datafile/xff.275.754906031"

 

--打开数据库

RMAN> alter database open resetlogs;

 

database opened

 

--添加日志文件

SQL> alter database add logfile group 4 '+DATA' size 10m;

 

Database altered.

 

SQL> alter database add logfile group 5 '+DATA' size 10m;

 

Database altered.

 

SQL> alter database add logfile group 6 '+DATA' size 10m;

 

Database altered.

 

--添加临时文件

SQL> alter tablespace temp add tempfile '+DATA' size 30m;

 

Tablespace altered.

 

--删除原临时文件

SQL> alter tablespace temp drop tempfile '/u01/oradata/tos/temp01.dbf';

 

Tablespace altered.

 

--切换日志

SQL> alter system switch logfile; --多次

 

System altered.

 

--数据文件修改写入磁盘

SQL> alter system checkpoint;

 

System altered.

 

--检查联机日志状态

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

 

    GROUP# STATUS

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

         1 INACTIVE

         2 INACTIVE

         3 INACTIVE

         4 INACTIVE

         5 INACTIVE

         6 CURRENT

 

6 rows selected.

 

--删除原联机日志

SQL> alter database drop logfile group 1;

 

Database altered.

 

SQL> alter database drop logfile group 2;

 

Database altered.

 

SQL> alter database drop logfile group 3;

 

Database altered.

 

--查看修改后状态

SQL>  select name from v$datafile

  2      union

  3      select member from v$logfile

  4      union

  5      select name from v$controlfile

  6      union

  7      select name from v$tempfile;

 

NAME

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

+DATA/tos/controlfile/current.268.754905785

+DATA/tos/datafile/example.272.754905995

+DATA/tos/datafile/sysaux.271.754905929

+DATA/tos/datafile/system.270.754905833

+DATA/tos/datafile/undotbs1.273.754906021

+DATA/tos/datafile/users.276.754906035

+DATA/tos/datafile/xff.274.754906027

+DATA/tos/datafile/xff.275.754906031

+DATA/tos/onlinelog/group_4.277.754906309

+DATA/tos/onlinelog/group_5.278.754906319

+DATA/tos/onlinelog/group_6.279.754906321

+DATA/tos/tempfile/temp.280.754906369

 

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

转载于:http://blog.itpub.net/31448824/viewspace-2139470/