文件系统怎样迁移到ASM

文件系统怎样迁移到ASM，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

网站建设哪家好，找创新互联公司！专注于网页设计、网站建设、微信开发、重庆小程序开发、集团企业网站建设等服务项目。为回馈新老客户创新互联还提供了湛江免费建站欢迎大家使用！

最好是以grid用户进入asm 环境

1. [oracle@sink~]$ su- grid

2. Password:

3. [grid@sink~]$ asmcmd

做完 0 级 增量备份后的备份文件

1. ASMCMD> pwd

2. +data/sink

3. ASMCMD> ls

4. BACKUPSET/

5. CONTROLFILE/

6. DATAFILE/

7. PARAMETERFILE/

查看刚才做的 0级增量 备份

1. RMAN>listcopyofdatabase;

3. Listof Datafile Copies

4. =======================

6. KeyFile S Completion Time Ckp SCN    Ckp Time

7. ------- ---- - --------------- ---------- ---------------

8. 9       1    A 06-JAN-18       1218041    06-JAN-18

9.         Name:+DATA/sink/datafile/system.258.964712643

10.         Tag: ORA_ASM_MIGRATION

12. 8       2    A 06-JAN-18       1218040    06-JAN-18

13.         Name:+DATA/sink/datafile/sysaux.257.964712643

14.         Tag: ORA_ASM_MIGRATION

16. 4       3    A 06-JAN-18       1218045    06-JAN-18

17.         Name:+DATA/sink/datafile/undotbs1.261.964712645

18.         Tag: ORA_ASM_MIGRATION

20. 6       4    A 06-JAN-18       1218050    06-JAN-18

21.         Name:+DATA/sink/datafile/users.263.964712657

22.         Tag: ORA_ASM_MIGRATION

24. 7       5    A 06-JAN-18       1218043    06-JAN-18

25.         Name:+DATA/sink/datafile/example.260.964712643

26.         Tag: ORA_ASM_MIGRATION

28. 3       6    A 06-JAN-18       1218042    06-JAN-18

29.         Name:+DATA/sink/datafile/tbssss.259.964712643

30.         Tag: ORA_ASM_MIGRATION

33. RMAN>

转储spfile到

1. RMAN> restore spfile to'+DATA/spfilesink.ora';

3. Starting restore at 06-JAN-18

4. using target database control file instead of recovery catalog

5. allocated channel: ORA_DISK_1

6. channel ORA_DISK_1: SID=18 device type=DISK

8. channel ORA_DISK_1: starting datafile backup set restore

9. channel ORA_DISK_1: restoring SPFILE

10. output file name=+DATA/spfilesink.ora

11. channel ORA_DISK_1: reading from backup piece +DATA/sink/backupset/2018_01_06/nnsnn0_ora_asm_migration_0.264.964712657

12. channel ORA_DISK_1: piece handle=+DATA/sink/backupset/2018_01_06/nnsnn0_ora_asm_migration_0.264.964712657 tag=ORA_ASM_MIGRATION

13. channel ORA_DISK_1: restored backup piece 1

14. channel ORA_DISK_1: restore complete, elapsed time: 00:00:04

15. Finished restore at 06-JAN-18

查看转储spfile参数文件后的  在ASM中的 路径状态

1. ASMCMD> pwd

2. +data

3. ASMCMD> ls

4. ASM/

5. SINK/

6. spfilesink.ora

7. ASMCMD>

修改并查看pfile中的内容，使其内容指向ASM中spfile的spfilesink.ora的路径，有意思！

1. [oracle@sink dbs]$ pwd

2. /u01/app/oracle/product/11.2.0/dbhome_1/dbs

3. [oracle@sink dbs]$ ls

4. afiedt.buf   hc_test.dat  initsink.ora  lkSINK  orapwsink  snapcf_sink.f

5. hc_sink.dat  init.ora     inittest.ora  lkTEST  orapwtest  spfilesink.ora

6. [oracle@sink dbs]$ vim initsink.ora

7. [oracle@sink dbs]$ cat initsink.ora

8. spfile='+DATA/spfilesink.ora'

9. [oracle@sink dbs]$

再次进入SQL环境，启动到nomount，设置spfile路径指向，并查看相关参数是否操作生效！成功了！

1. [oracle@sink dbs]$!sql

2. sqlplus/as sysdba

4. SQL*Plus:Release 11.2.0.4.0 Productionon Sat Jan 6 16:30:19 2018

6. Copyright(c) 1982, 2013, Oracle.All rights reserved.

9. Connectedto:

10. OracleDatabase 11g Enterprise EditionRelease 11.2.0.4.0- 64bit Production

11. With the Partitioning,Automatic Storage Management, OLAP,Data Mining

12. andReal Application Testing options

14. 16:30:19 SYS@ sink>startupforce nomount;

15. ORACLE instance started.

17. Total System Global Area  835104768 bytes

18. FixedSize            2257840 bytes

19. VariableSize          553651280 bytes

20. Database Buffers      276824064 bytes

21. Redo Buffers            2371584 bytes

22. 16:30:38 SYS@ sink>alter systemset db_create_file_dest='+DATA';

24. System altered.

26. Elapsed: 00:00:00.01

27. 16:31:23 SYS@ sink>show parametercreate;

29. NAME                TYPE    VALUE

30. ------------------------------------ ----------- ------------------------------

31. create_bitmap_area_size     integer     8388608

32. create_stored_outlines             string

33. db_create_file_dest             string    +DATA

34. db_create_online_log_dest_1         string

35. db_create_online_log_dest_2         string

36. db_create_online_log_dest_3         string

37. db_create_online_log_dest_4         string

38. db_create_online_log_dest_5         string

39. 16:31:33 SYS@ sink>

自上一步之后，查看数据库启动状态，然后satrtup force，将他启动到opened状态

1. 16:31:33 SYS@ sink>select statusfrom v$instance;

3. STATUS

4. ------------

5. STARTED

7. 1row selected.

9. Elapsed: 00:00:00.00

10. 16:35:24 SYS@ sink>alterdatabase open;

11. alterdatabase open

12. *

13. ERROR at line 1:

14. ORA-01507:databasenot mounted

17. Elapsed: 00:00:00.00

18. 16:35:39 SYS@ sink>startupforce;

19. ORACLE instance started.

21. Total System Global Area  835104768 bytes

22. FixedSize            2257840 bytes

23. VariableSize          553651280 bytes

24. Database Buffers      276824064 bytes

25. Redo Buffers            2371584 bytes

26. Database mounted.

27. Database opened.

28. 16:36:24 SYS@ sink>

这里查询一下数据文件的file#和name，方便之后的数据文件迁移

1. SYS@ sink>r

2.   1*selectfile#,namefrom v$datafile

4.      FILE# NAME

5. ---------- -------------------------------------------------------

6.      1/u01/app/oracle/oradata/sink/system01.dbf

7.      2/u01/app/oracle/oradata/sink/sysaux01.dbf

8.      3/u01/app/oracle/oradata/sink/undotbs01.dbf

9.      4/u01/app/oracle/oradata/sink/users01.dbf

10.      5/u01/app/oracle/oradata/sink/example01.dbf

11.      6+DATA/sink/datafile/tbssss.256.963504823

13. 6rows selected.

15. Elapsed: 00:00:00.01

转储控制文件失败，看错误信息提示：意思是在数据库是mount或者open执行这条语句没有使用TO语句，

1. RMAN> restore controlfilefrom'/u01/app/oracle/oradata/sink/control01.ctl';

3. Starting restore at 06-JAN-18

4. using targetdatabase controlfileinsteadof recovery catalog

5. allocated channel: ORA_DISK_1

6. channel ORA_DISK_1: SID=42 devicetype=DISK

8. RMAN-00571:===========================================================

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

10. RMAN-00571:===========================================================

11. RMAN-03002: failureof restore command at 01/06/2018 16:45:57

12. RMAN-06496: must use theTO clause when thedatabaseis mountedor open

这次加了to语句还是不行，后来纠结了一阵子，发现转储控制文件发现必须要oralce处于nomount状态

1. RMAN> restore controlfilefrom'/u01/app/oracle/oradata/sink/control01.ctl'to'+data/';

3. RMAN-00571:===========================================================

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

5. RMAN-00571:===========================================================

6. RMAN-00558: error encountered while parsinginput commands

7. RMAN-01009: syntax error: found"to": expecting oneof:"archivelog, channel, check, controlfile, database, datafile, device, force, from, high, preview, primary, skip readonly, spfile, standby, tablespace, to restore point, until restore point, until, validate, (, ;"

8. RMAN-01007: at line 1column 71file: standardinput

 找到头绪之后，将数据库启动到nomount状态

1. [oracle@sink dbs]$!sql

2. sqlplus/as sysdba

4. SQL*Plus:Release 11.2.0.4.0 Productionon Sat Jan 6 16:48:57 2018

6. Copyright(c) 1982, 2013, Oracle.All rights reserved.

9. Connectedto:

10. OracleDatabase 11g Enterprise EditionRelease 11.2.0.4.0- 64bit Production

11. With the Partitioning,Automatic Storage Management, OLAP,Data Mining

12. andReal Application Testing options

14. 16:48:57 SYS@ sink>select statusfrom v$instance;

16. STATUS

17. ------------

18. OPEN

20. 1row selected.

22. Elapsed: 00:00:00.01

23. 16:49:10 SYS@ sink>startup nomount;

24. ORA-01081: cannotstart already-running ORACLE- shut it down first

25. 16:49:17 SYS@ sink>startupforce nomount;

26. ORACLE instance started.

28. Total System Global Area  835104768 bytes

29. FixedSize            2257840 bytes

30. VariableSize          553651280 bytes

31. Database Buffers      276824064 bytes

32. Redo Buffers            2371584 bytes

33. 16:49:33 SYS@ sink>select statusfrom v$instance;

35. STATUS

36. ------------

37. STARTED

39. 1row selected.

41. Elapsed: 00:00:00.01

42. 16:49:57 SYS@ sink>

好了，经过一翻纠结，终于成功了，控制文件转储成功！

1. RMAN> restore controlfilefrom'/u01/app/oracle/oradata/sink/control01.ctl';

3. Starting restore at 06-JAN-18

4. using targetdatabase controlfileinsteadof recovery catalog

5. allocated channel: ORA_DISK_1

6. channel ORA_DISK_1: SID=23 devicetype=DISK

8. channel ORA_DISK_1: copied controlfilecopy

9. outputfile name=+DATA/sink/controlfile/current.266.964717197

10. outputfile name=+DATA/sink/controlfile/current.267.964717197

11. Finished restore at 06-JAN-18

13. RMAN>

查看控制文件迁移后的效果

1. ASMCMD> pwd

2. +data/sink/controlfile

3. ASMCMD> ls

4. Backup.262.964712653

5. current.266.964717197

6. current.267.964717197

在RMAN环境中将数据库切到mount状态，switch...修改控制文件用datafile copy做当前DB的datafile使用

1. RMAN>alterdatabase mount;

3. database mounted

4. released channel: ORA_DISK_1

6. RMAN> switchdatabasetocopy;

8. using targetdatabase controlfileinsteadof recovery catalog

9. datafile 1 switchedto datafilecopy"+DATA/sink/datafile/system.258.964712643"

10. datafile 2 switchedto datafilecopy"+DATA/sink/datafile/sysaux.257.964712643"

11. datafile 3 switchedto datafilecopy"+DATA/sink/datafile/undotbs1.261.964712645"

12. datafile 4 switchedto datafilecopy"+DATA/sink/datafile/users.263.964712657"

13. datafile 5 switchedto datafilecopy"+DATA/sink/datafile/example.260.964712643"

14. datafile 6 switchedto datafilecopy"+DATA/sink/datafile/tbssss.259.964712643"

直接exit退出rman环境，然后直接!sql进入SQL环境，注意数据库状态仍然为mount，查看dataifle的路径

1. RMAN>exit

4. Recovery Manager complete.

5. [oracle@sink dbs]$!sql

6. sqlplus/as sysdba

8. SQL*Plus:Release 11.2.0.4.0 Productionon Sat Jan 6 17:42:31 2018

10. Copyright(c) 1982, 2013, Oracle.All rights reserved.

13. Connectedto:

14. OracleDatabase 11g Enterprise EditionRelease 11.2.0.4.0- 64bit Production

15. With the Partitioning,Automatic Storage Management, OLAP,Data Mining

16. andReal Application Testing options

18. 17:42:41 SYS@ sink>col namefor a55

19. 17:42:57 SYS@ sink>r

20.   1*select namefrom v$datafile

22. NAME

23. -------------------------------------------------------

24. +DATA/sink/datafile/system.258.964712643

25. +DATA/sink/datafile/sysaux.257.964712643

26. +DATA/sink/datafile/undotbs1.261.964712645

27. +DATA/sink/datafile/users.263.964712657

28. +DATA/sink/datafile/example.260.964712643

29. +DATA/sink/datafile/tbssss.259.964712643

31. 6rows selected.

33. Elapsed: 00:00:00.01

34. 17:42:58 SYS@ sink>

recover databse（应用 ）开多个channel加快recover速度，生产DB很大，恢复时间很长，这是不错的办法

1. RMAN>run{

2. 2> allocate channel dev1 devicetype disk;

3. 3> allocate channel dev2 devicetype disk;

4. 4> allocate channel dev3 devicetype disk;

5. 5> allocate channel dev4 devicetype disk;

6. 6>recoverdatabase;

7. 7>}

9. released channel: ORA_DISK_1

10. allocated channel: dev1

11. channel dev1: SID=25 devicetype=DISK

13. allocated channel: dev2

14. channel dev2: SID=26 devicetype=DISK

16. allocated channel: dev3

17. channel dev3: SID=27 devicetype=DISK

19. allocated channel: dev4

20. channel dev4: SID=28 devicetype=DISK

22. Startingrecover at 06-JAN-18

24. starting media recovery

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

27. Finishedrecover at 06-JAN-18

28. released channel: dev1

29. released channel: dev2

30. released channel: dev3

31. released channel: dev4

33. RMAN>

exit，进入sql环境，打开到open，看临时文件name，临时表空间name，

1. RMAN> exit

2. Recovery Manager complete.

3. [oracle@sink dbs]$!sql

4. sqlplus / as sysdba

6. SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 6 17:57:16 2018

8. Copyright(c) 1982, 2013, Oracle. All rights reserved.

11. Connected to:

12. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0- 64bit Production

13. With the Partitioning, Automatic Storage Management, OLAP, Data Mining

14. and Real Application Testing options

16. 17:59:16 SYS @ sink>alter database open;

18. Database altered.

20. Elapsed: 00:00:02.44

21. 17:59:50 SYS @ sink>selectname from v$tempfile;

23. NAME

24. -------------------------------------------------------

25. /u01/app/oracle/oradata/sink/temp01.dbf

27. 1 row selected.

29. Elapsed: 00:00:00.02

30. 18:00:05 SYS @ sink>select tablespace_name,contents from dba_tablespaces;

32. TABLESPACE_NAME            CONTENTS

33. ---------------------------------------

34. SYSTEM                   PERMANENT

35. SYSAUX                   PERMANENT

36. UNDOTBS1               UNDO

37. TEMP                   TEMPORARY

38. USERS                   PERMANENT

39. EXAMPLE                PERMANENT

40. TBSSSS                   PERMANENT

42. 7 rows selected.

44. Elapsed: 00:00:00.01

把临时表空间temp里面的临时文件temp01.dbf加入到ASM中的+date目录下

1. 18:00:24 SYS@ sink>altertablespace tempadd tempfile'+data';

3. Tablespace altered.

5. Elapsed: 00:00:00.11

6. 18:01:27 SYS@ sink>select namefrom v$tempfile;

8. NAME

9. -------------------------------------------------------

10. /u01/app/oracle/oradata/sink/temp01.dbf

11. +DATA/sink/tempfile/temp.268.964720887

13. 2rows selected.

15. Elapsed: 00:00:00.01

因为只有一个临时表空间的一个临时文件所以无法删除，多个就能成功，一直到最后一个就不能这样删除了

1. 18:12:22 SYS@ sink>altertablespace tempdrop tempfile'/u01/app/oracle/oradata/sink/temp01.dbf';

3. Tablespace altered.

5. Elapsed: 00:00:00.12

6. 18:13:35 SYS@ sink>select namefrom v$tempfile;

8. NAME

9. -------------------------------------------------------

10. /u01/app/oracle/oradata/sink/temp01.dbf

11. +DATA/sink/tempfile/temp.268.964720887

13. 2rows selected.

15. Elapsed: 00:00:00.01

上一步中DB处于open，以上操作将临时文件删至1个的时候，就不能继续删除，得将DB置于mount状态才行

1. 18:14:03 SYS@ sink>shutdownimmediate;

2. Database closed.

3. Database dismounted.

4. ORACLE instance shut down.

5. 
   当前文章：文件系统怎样迁移到ASM
   标题网址：http://jkwzsj.com/article/pihods.html