Migtating Datafile From Single-Instance To ASM

Migtating Datafile From Single-Instance To ASM

 

需求:将Oracle 11gR2 11.2.0.3单实例上的数据文件迁移到ASM上面。

由于Oracle 11g的ASM需要安装grid infrastructure,所以先得把这个东西装上。

具体的安装过程略过，比11gR2 11.2.0.3的RAC grid infrastructure的安装还要容易些。

由于操作系统不能访问ASM实例，所以只能通过rman将数据文件移动到ASM DISKGOUP上。

1.迁移前期准备

1.1确认实例名

SQL> select instance_name,status from v$instance;

 

INSTANCE_NAME    STATUS

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

szscdb           OPEN

 

1.2查看控制文件的路径

SQL> select name from v$controlfile;

 

NAME

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

/u02/app/oracle/oradata/szscdb/control01.ctl

/u02/app/oracle/oradata/szscdb/control02.ctl

 

1.3查看spfile路径

SQL> show parameter spfile;

 

NAME                                 TYPE        VALUE

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

spfile                               string      /u02/app/oracle/products/10.2.

                                                 0/db_1/dbs/spfileszscdb.ora

1.4查看数据文件的路径

SQL> col file_name for a35

SQL> select file_id,file_name from dba_data_files

  2  order by file_id;

 

   FILE_ID FILE_NAME

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

         1 /u02/app/oracle/oradata/szscdb/syst

           em01.dbf

 

         2 /u02/app/oracle/oradata/szscdb/sysa

           ux01.dbf

 

         3 /u02/app/oracle/oradata/szscdb/undo

           tbs01.dbf

 

         4 /u02/app/oracle/oradata/szscdb/user

           s01.dbf

SQL> select file_id,file_name from dba_temp_files

  2  order by file_id;

 

   FILE_ID FILE_NAME

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

         1 /u02/app/oracle/oradata/szscdb/temp

           01.dbf

 

1.5确认数据库的归档状态

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /RECO/arch/szscdb

Oldest online log sequence     3

Next log sequence to archive   5

Current log sequence           5

 

1.6查看ASM DISKGROUP

[root@szscdb ~]# su - grid

grid@szscdb:/home/grid>asmcmd

lASMCMD>lsdg

State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name

MOUNTED  NORMAL  N         512   4096  1048576      5420     5300                0            2650              0             N  DATA/

MOUNTED  EXTERN  N         512   4096  1048576      4816     4757                0            4757              0             N  DBFS/

 

1.7修改spfile中控制文件的路径

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

 

System altered.

 

1.8备份当前控制文件

RMAN> backup current controlfile format '/u02/control_20130716.bak';

 

Starting backup at 16-JUL-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 16-JUL-13

channel ORA_DISK_1: finished piece 1 at 16-JUL-13

piece handle=/u02/control_20130716.bak tag=TAG20130716T110255 comment=NONE

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

Finished backup at 16-JUL-13

 

RMAN> LIST BACKUP OF CONTROLFILE;

 

 

List of Backup Sets

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

 

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

3       Full    9.33M      DISK        00:00:01     16-JUL-13     

        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20130716T110255

        Piece Name: /u02/control_20130716.bak

  Control File Included: Ckp SCN: 1087506      Ckp time: 16-JUL-13

 

2.开始迁移

2.1关闭数据库

RMAN> shutdown immediate;

2.2启动到nomount状态

RMAN> startup nomount;

2.3恢复控制文件到ASM上

 

RMAN> restore controlfile from '/u02/control_20130716.bak';

 

Starting restore at 16-JUL-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=14 device type=DISK

 

channel ORA_DISK_1: restoring control file

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

output file name=+DATA/szscdb/controlfile/current.262.820926295

Finished restore at 16-JUL-13

 

2.3mount数据库

RMAN> alter database mount;

 

2.4迁移数据文件到ASM

 

RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';       

 

Starting backup at 16-JUL-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=125 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u02/app/oracle/oradata/szscdb/system01.dbf

output file name=+DATA/szscdb/datafile/system.260.820927781 tag=TAG20130716T112940 RECID=1 STAMP=820927792

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u02/app/oracle/oradata/szscdb/sysaux01.dbf

output file name=+DATA/szscdb/datafile/sysaux.261.820927797 tag=TAG20130716T112940 RECID=2 STAMP=820927803

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u02/app/oracle/oradata/szscdb/undotbs01.dbf

output file name=+DATA/szscdb/datafile/undotbs1.259.820927811 tag=TAG20130716T112940 RECID=3 STAMP=820927812

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u02/app/oracle/oradata/szscdb/users01.dbf

output file name=+DATA/szscdb/datafile/users.258.820927815 tag=TAG20130716T112940 RECID=4 STAMP=820927814

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

Finished backup at 16-JUL-13

 

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

 

----使用switch database to copy同步控制文件信息

RMAN> SWITCH DATABASE TO COPY;

 

datafile 1 switched to datafile copy "+DATA/szscdb/datafile/system.260.820927781"

datafile 2 switched to datafile copy "+DATA/szscdb/datafile/sysaux.261.820927797"

datafile 3 switched to datafile copy "+DATA/szscdb/datafile/undotbs1.259.820927811"

datafile 4 switched to datafile copy "+DATA/szscdb/datafile/users.258.820927815"

 

RMAN> alter database open;

 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 07/16/2013 11:33:57

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

RMAN> alter database open resetlogs;

 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 07/16/2013 11:34:07

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '+DATA/szscdb/datafile/system.260.820927781'

 

---做介质恢复

2.5 media recovery

SQL> recover database using backup controlfile;

ORA-00279: change 1087506 generated at 07/16/2013 08:54:43 needed for thread 1

ORA-00289: suggestion : /RECO/arch/szscdb/1_6_820683957.arc

ORA-00280: change 1087506 for thread 1 is in sequence #6

 

 

Specify log: {=suggested | filename | AUTO | CANCEL}

AUTO

ORA-00308: cannot open archived log '/RECO/arch/szscdb/1_6_820683957.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

------ORACLE告诉你在这个地方找不到该归档文件

 

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /RECO/arch/szscdb

Oldest online log sequence     4

Next log sequence to archive   6

Current log sequence           6

-----这里可以看到6号归档文件还没有产生，那么应该在redo日志里面

 

SQL> col member for a35

SQL> SELECT f.group#,l.status,f.member

  2  FROM V$LOG l,v$LOGFILE f

  3  WHERE l.group#=f.group#;

 

    GROUP# STATUS           MEMBER

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

         1 INACTIVE         /u02/app/oracle/oradata/szscdb/redo

                            01.log

 

         3 CURRENT          /u02/app/oracle/oradata/szscdb/redo

                            03.log

 

         2 INACTIVE         /u02/app/oracle/oradata/szscdb/redo

                            02.log

---根据返回结果猜测数据存在redo03.log中

 

 

SQL> recover database using backup controlfile;

ORA-00279: change 1087506 generated at 07/16/2013 08:54:43 needed for thread 1

ORA-00289: suggestion : /RECO/arch/szscdb/1_6_820683957.arc

ORA-00280: change 1087506 for thread 1 is in sequence #6

 

 

Specify log: {=suggested | filename | AUTO | CANCEL}

/u02/app/oracle/oradata/szscdb/redo03.log

Log applied.

Media recovery complete.

 

2.6 打开数据库

SQL> alter database open resetlogs;

 

Database altered.

 

2.7重建temp文件

 

SQL> select name from v$tempfile;

 

NAME

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

/u02/app/oracle/oradata/szscdb/temp01.dbf

 

SQL> alter database tempfile '/u02/app/oracle/oradata/szscdb/temp01.dbf' drop including datafiles;

 

Database altered.

 

SQL> alter tablespace temp add tempfile '+DATA/SZSCDB/DATAFILE/temp01.dbf' size 512M autoextend on next 250m maxsize unlimited;

 

Tablespace altered.

 

2.8将REDO移动到ASM上

SQL> select member from v$logfile;

 

MEMBER

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

/u02/app/oracle/oradata/szscdb/redo03.log

/u02/app/oracle/oradata/szscdb/redo02.log

/u02/app/oracle/oradata/szscdb/redo01.log

 

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

 

    GROUP# STATUS

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

         1 CURRENT

         2 UNUSED

         3 UNUSED

 

SQL> alter system switch logfile;

 

System altered.

 

SQL> alter system switch logfile;

 

System altered.

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

 

    GROUP# STATUS

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

         1 CURRENT

         2 INACTIVE

         3 INACTIVE

---这种情况下表示2,3两组可以被干掉，但是ORACLE要求至少有两组redo，所以只能把2先干掉。

SQL> alter database drop logfile group 2;

 

Database altered.

 

 

SQL> alter database add logfile group 2 ('+DATA') size 512M;

 

Database altered.

 

 

 

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

 

    GROUP# STATUS

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

         1 CURRENT

         2 UNUSED

         3 INACTIVE

SQL> alter system switch logfile;

 

System altered.

 

 

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

 

    GROUP# STATUS

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

         1 ACTIVE

         2 CURRENT

         3 INACTIVE

----把第三组redo日志干掉

SQL> alter database drop logfile group 3;

SQL> alter database add logfile group 3 ('+DATA') size 512M;

 

注：同理，干掉redo 1，并重建至ASM

SQL> select member from v$logfile;

 

MEMBER

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

+DATA/szscdb/onlinelog/group_2.256.820929703

 

 

2.9迁移spfile到ASM

 

SQL> create pfile ='/u02/initszscdb.ora' from spfile;

 

File created.

 

SQL> create spfile='+DATA/SZSCDB/spfileSZSCDB.ora'

  2  from pfile='/u02/initszscdb.ora';

 

File created.

 

SQL> shutdown immediate;

 

oracle@szscdb:/u02/app/oracle/products/10.2.0/db_1/dbs>mv spfileszscdb.ora /tmp/

oracle@szscdb:/u02/app/oracle/products/10.2.0/db_1/dbs>vi initszscdb.ora

##ADD

spfile=' +DATA/SZSCDB/ spfileSZSCDB.ora'

 

SQL> show parameter spfile

 

NAME                                 TYPE        VALUE

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

spfile                               string      +DATA/szscdb/spfileszscdb.ora

 

 

至此，ORACLE数据文件，控制文件，重做日志文件，参数文件已经完全迁移到ASM上。

可以删除迁移时rman的一些备份:

 

RMAN> DELETE NOPROMPT FORCE COPY;

 

 

 

 

 

 

 

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

转载于:http://blog.itpub.net/26169542/viewspace-766377/