文件系统迁移到ASM（数据迁移）

  • 大鲨鱼o0O

    2018-01-06 18:23:35
  • Oracle
  • 原创

尊重原创，转载请注明出处，谢谢

打开数据库

  1. 15:35:11 SYS @ sink >startup
  2. ORACLE instance started.

  4. Total System Global Area 835104768 bytes
  5. Fixed Size         2257840 bytes
  6. Variable Size         553651280 bytes
  7. Database Buffers     276824064 bytes
  8. Redo Buffers         2371584 bytes
  9. Database mounted.
  10. Database opened.
  11. 15:35:25 SYS @ sink >

exit退出SQL环境，进入linux操作系统环境，此时的oracle还处于open状态

  1. 15:35:25 SYS @ sink >exit
  2. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  3. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  4. [oracle@sink ~]$

在linux的oracle用户下进入rman环境。  rman target /   等价于   rman target sys/oracle@sink

  1. [oracle@sink ~]$ rman target /

  3. Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jan 6 15:41:27 2018

  5. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

  7. connected to target database: SINK (DBID=207714324)

  9. RMAN>


在做一系列的没把握的未知操作之前先做一个 0 级增量备份 最保险

  1. RMAN> run
  2. 2> {
  3. 3> allocate channel dev1 device type disk;
  4. 4> allocate channel dev2 device type disk;
  5. 5> allocate channel dev3 device type disk;
  6. 6> allocate channel dev4 device type disk;
  7. 7> backup as copy incremental level 0 database format '+DATA' tag 'ora_asm_migration';
  8. 8> }

  10. using target database control file instead of recovery catalog
  11. allocated channel: dev1
  12. channel dev1: SID=37 device type=DISK

  14. allocated channel: dev2
  15. channel dev2: SID=41 device type=DISK

  17. allocated channel: dev3
  18. channel dev3: SID=40 device type=DISK

  20. allocated channel: dev4
  21. channel dev4: SID=42 device type=DISK

  23. Starting backup at 06-JAN-18
  24. channel dev1: starting datafile copy
  25. input datafile file number=00001 name=/u01/app/oracle/oradata/sink/system01.dbf
  26. channel dev2: starting datafile copy
  27. input datafile file number=00002 name=/u01/app/oracle/oradata/sink/sysaux01.dbf
  28. channel dev3: starting datafile copy
  29. input datafile file number=00005 name=/u01/app/oracle/oradata/sink/example01.dbf
  30. channel dev4: starting datafile copy
  31. input datafile file number=00006 name=+DATA/sink/datafile/tbssss.256.963504823
  32. output file name=+DATA/sink/datafile/tbssss.259.964712643 tag=ORA_ASM_MIGRATION RECID=3 STAMP=964712644
  33. channel dev4: datafile copy complete, elapsed time: 00:00:03
  34. channel dev4: starting datafile copy
  35. input datafile file number=00003 name=/u01/app/oracle/oradata/sink/undotbs01.dbf
  36. output file name=+DATA/sink/datafile/undotbs1.261.964712645 tag=ORA_ASM_MIGRATION RECID=4 STAMP=964712652
  37. channel dev4: datafile copy complete, elapsed time: 00:00:07
  38. channel dev4: starting datafile copy
  39. copying current control file
  40. output file name=+DATA/sink/controlfile/backup.262.964712653 tag=ORA_ASM_MIGRATION RECID=5 STAMP=964712654
  41. channel dev4: datafile copy complete, elapsed time: 00:00:03
  42. channel dev4: starting datafile copy
  43. input datafile file number=00004 name=/u01/app/oracle/oradata/sink/users01.dbf
  44. output file name=+DATA/sink/datafile/users.263.964712657 tag=ORA_ASM_MIGRATION RECID=6 STAMP=964712656
  45. channel dev4: datafile copy complete, elapsed time: 00:00:01
  46. channel dev4: starting incremental level 0 datafile backup set
  47. channel dev4: specifying datafile(s) in backup set
  48. including current SPFILE in backup set
  49. channel dev4: starting piece 1 at 06-JAN-18
  50. channel dev4: finished piece 1 at 06-JAN-18
  51. piece handle=+DATA/sink/backupset/2018_01_06/nnsnn0_ora_asm_migration_0.264.964712657 tag=ORA_ASM_MIGRATION comment=NONE
  52. channel dev4: backup set complete, elapsed time: 00:00:01
  53. output file name=+DATA/sink/datafile/example.260.964712643 tag=ORA_ASM_MIGRATION RECID=7 STAMP=964712658
  54. channel dev3: datafile copy complete, elapsed time: 00:00:18
  55. output file name=+DATA/sink/datafile/sysaux.257.964712643 tag=ORA_ASM_MIGRATION RECID=8 STAMP=964712662
  56. channel dev2: datafile copy complete, elapsed time: 00:00:22
  57. output file name=+DATA/sink/datafile/system.258.964712643 tag=ORA_ASM_MIGRATION RECID=9 STAMP=964712665
  58. channel dev1: datafile copy complete, elapsed time: 00:00:30
  59. Finished backup at 06-JAN-18
  60. released channel: dev1
  61. released channel: dev2
  62. released channel: dev3
  63. released channel: dev4

  65. RMAN>


数据库全备 仅仅是一个databse的全备，没有任何额外的附加功能，仅
0 级 增量备份 既是数据库的全备，又是，0级增量备份，在此基础上，可以后续执行增量备份
结论 因此推荐使用0级增量备份做数据库的全备


最好是以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> list copy of database;

  3. List of Datafile Copies
  4. =======================

  6. Key File 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 Production on Sat Jan 6 16:30:19 2018

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


  9. Connected to:
  10. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  11. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  12. and Real Application Testing options

  14. 16:30:19 SYS @ sink >startup force nomount;
  15. ORACLE instance started.

  17. Total System Global Area 835104768 bytes
  18. Fixed Size         2257840 bytes
  19. Variable Size         553651280 bytes
  20. Database Buffers     276824064 bytes
  21. Redo Buffers         2371584 bytes
  22. 16:30:38 SYS @ sink >alter system set db_create_file_dest='+DATA';

  24. System altered.

  26. Elapsed: 00:00:00.01
  27. 16:31:23 SYS @ sink >show parameter create;

  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 status from v$instance;

  3. STATUS
  4. ------------
  5. STARTED

  7. 1 row selected.

  9. Elapsed: 00:00:00.00
  10. 16:35:24 SYS @ sink >alter database open;
  11. alter database open
  12. *
  13. ERROR at line 1:
  14. ORA-01507: database not mounted


  17. Elapsed: 00:00:00.00
  18. 16:35:39 SYS @ sink >startup force;
  19. ORACLE instance started.

  21. Total System Global Area 835104768 bytes
  22. Fixed Size         2257840 bytes
  23. Variable Size         553651280 bytes
  24. Database Buffers     276824064 bytes
  25. Redo Buffers         2371584 bytes
  26. Database mounted.
  27. Database opened.
  28. 16:36:24 SYS @ sink >

oracle启动到某一状态的时候 之后启动的情况
satrtup nomount
 alter database mount alter database open
startup mount alter database open --------------------------
startup (open) ---------------------------- --------------------------
oracle启动到某一状态之后，只能一步一步的启动，直至open状态，不能越级实行后续启动

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

  1. SYS @ sink >r
  2.   1* select file#,name from 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. 6 rows selected.

  15. Elapsed: 00:00:00.01


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

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

  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=42 device type=DISK

  8. RMAN-00571: ===========================================================
  9. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  10. RMAN-00571: ===========================================================
  11. RMAN-03002: failure of restore command at 01/06/2018 16:45:57
  12. RMAN-06496: must use the TO clause when the database is mounted or open


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

  1. RMAN> restore controlfile from '/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 parsing input commands
  7. RMAN-01009: syntax error: found "to": expecting one of: "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 1 column 71 file: standard input


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

  1. [oracle@sink dbs]$ !sql
  2. sqlplus / as sysdba

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

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


  9. Connected to:
  10. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  11. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  12. and Real Application Testing options

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

  16. STATUS
  17. ------------
  18. OPEN

  20. 1 row selected.

  22. Elapsed: 00:00:00.01
  23. 16:49:10 SYS @ sink >startup nomount;
  24. ORA-01081: cannot start already-running ORACLE - shut it down first
  25. 16:49:17 SYS @ sink >startup force nomount;
  26. ORACLE instance started.

  28. Total System Global Area 835104768 bytes
  29. Fixed Size         2257840 bytes
  30. Variable Size         553651280 bytes
  31. Database Buffers     276824064 bytes
  32. Redo Buffers         2371584 bytes
  33. 16:49:33 SYS @ sink >select status from v$instance;

  35. STATUS
  36. ------------
  37. STARTED

  39. 1 row selected.

  41. Elapsed: 00:00:00.01
  42. 16:49:57 SYS @ sink >


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

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

  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=23 device type=DISK

  8. channel ORA_DISK_1: copied control file copy
  9. output file name=+DATA/sink/controlfile/current.266.964717197
  10. output file 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> alter database mount;

  3. database mounted
  4. released channel: ORA_DISK_1

  6. RMAN> switch database to copy;

  8. using target database control file instead of recovery catalog
  9. datafile 1 switched to datafile copy "+DATA/sink/datafile/system.258.964712643"
  10. datafile 2 switched to datafile copy "+DATA/sink/datafile/sysaux.257.964712643"
  11. datafile 3 switched to datafile copy "+DATA/sink/datafile/undotbs1.261.964712645"
  12. datafile 4 switched to datafile copy "+DATA/sink/datafile/users.263.964712657"
  13. datafile 5 switched to datafile copy "+DATA/sink/datafile/example.260.964712643"
  14. datafile 6 switched to datafile copy "+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 Production on Sat Jan 6 17:42:31 2018

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


  13. Connected to:
  14. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  15. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  16. and Real Application Testing options

  18. 17:42:41 SYS @ sink >col name for a55
  19. 17:42:57 SYS @ sink >r
  20.   1* select name from 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. 6 rows 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 device type disk;
  3. 3> allocate channel dev2 device type disk;
  4. 4> allocate channel dev3 device type disk;
  5. 5> allocate channel dev4 device type disk;
  6. 6> recover database;
  7. 7> }

  9. released channel: ORA_DISK_1
  10. allocated channel: dev1
  11. channel dev1: SID=25 device type=DISK

  13. allocated channel: dev2
  14. channel dev2: SID=26 device type=DISK

  16. allocated channel: dev3
  17. channel dev3: SID=27 device type=DISK

  19. allocated channel: dev4
  20. channel dev4: SID=28 device type=DISK

  22. Starting recover at 06-JAN-18

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

  27. Finished recover 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 >select name 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 >alter tablespace temp add tempfile '+data';

  3. Tablespace altered.

  5. Elapsed: 00:00:00.11
  6. 18:01:27 SYS @ sink >select name from v$tempfile;

  8. NAME
  9. -------------------------------------------------------
  10. /u01/app/oracle/oradata/sink/temp01.dbf
  11. +DATA/sink/tempfile/temp.268.964720887

  13. 2 rows selected.

  15. Elapsed: 00:00:00.01


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

  1. 18:12:22 SYS @ sink >alter tablespace temp drop tempfile '/u01/app/oracle/oradata/sink/temp01.dbf';

  3. Tablespace altered.

  5. Elapsed: 00:00:00.12
  6. 18:13:35 SYS @ sink >select name from v$tempfile;

  8. NAME
  9. -------------------------------------------------------
  10. /u01/app/oracle/oradata/sink/temp01.dbf
  11. +DATA/sink/tempfile/temp.268.964720887

  13. 2 rows selected.

  15. Elapsed: 00:00:00.01


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

  1. 18:14:03 SYS @ sink >shutdown immediate;
  2. Database closed.
  3. Database dismounted.
  4. ORACLE instance shut down.
  5. 18:19:51 SYS @ sink >startup mount;
  6. ORACLE instance started.

  8. Total System Global Area 835104768 bytes
  9. Fixed Size         2257840 bytes
  10. Variable Size         553651280 bytes
  11. Database Buffers     276824064 bytes
  12. Redo Buffers         2371584 bytes
  13. Database mounted.
  14. 18:20:08 SYS @ sink >alter database drop tempfile '/u01/app/oracle/oradata/sink/temp01.dbf';
  15. alter database drop tempfile '/u01/app/oracle/oradata/sink/temp01.dbf'
  16.                     *
  17. ERROR at line 1:
  18. ORA-01900: LOGFILE keyword expected

  20. Elapsed: 00:00:00.00
  21. 18:21:45 SYS @ sink >edit
  22. Wrote file afiedt.buf

  24.   1* alter database tempfile '/u01/app/oracle/oradata/sink/temp01.dbf' drop
  25. 18:22:16 SYS @ sink >r
  26.   1* alter database tempfile '/u01/app/oracle/oradata/sink/temp01.dbf' drop

  28. Database altered.

  30. Elapsed: 00:00:00.03
  31. 18:22:17 SYS @ sink >alter database open;

  33. Database altered.

  35. Elapsed: 00:00:02.34
  36. 18:23:19 SYS @ sink >col tablespace_name for a10
  37. 18:23:38 SYS @ sink >col file_name for a55
  38. 18:23:54 SYS @ sink >r
  39.   1* select tablespace_name,file_name from dba_temp_files

  41. TABLESPACE FILE_NAME
  42. ---------- -------------------------------------------------------
  43. TEMP     +DATA/sink/tempfile/temp.268.964720887

  45. 1 row selected.

  47. Elapsed: 00:00:00.00


迁移日志文件到ASM，具体细节就不多做解释了，整了这么久，累，去吃饭去了

  1. 18:23:55 SYS @ sink >select group#,status,sequence#,bytes from v$log;

  3.     GROUP# STATUS     SEQUENCE#     BYTES
  4. ---------- ---------------- ---------- ----------
  5.      1 INACTIVE         16     52428800
  6.      2 CURRENT         17     52428800
  7.      3 INACTIVE         15     52428800

  9. 3 rows selected.

  11. Elapsed: 00:00:00.01
  12. 18:29:19 SYS @ sink >col member for a55
  13. 18:29:37 SYS @ sink >select group#,member from v$logfile;

  15.     GROUP# MEMBER
  16. ---------- -------------------------------------------------------
  17.      3 /u01/app/oracle/oradata/sink/redo03.log
  18.      2 /u01/app/oracle/oradata/sink/redo02.log
  19.      1 /u01/app/oracle/oradata/sink/redo01.log

  21. 3 rows selected.

  23. Elapsed: 00:00:00.01
  24. 18:29:57 SYS @ sink >alter database add logfile '+data' size 50m;

  26. Database altered.

  28. Elapsed: 00:00:01.41
  29. 18:30:32 SYS @ sink >alter database add logfile '+data' size 50m;

  31. Database altered.

  33. Elapsed: 00:00:00.62
  34. 18:30:54 SYS @ sink >select group#,member from v$logfile;

  36.     GROUP# MEMBER
  37. ---------- -------------------------------------------------------
  38.      3 /u01/app/oracle/oradata/sink/redo03.log
  39.      2 /u01/app/oracle/oradata/sink/redo02.log
  40.      1 /u01/app/oracle/oradata/sink/redo01.log
  41.      4 +DATA/sink/onlinelog/group_4.269.964722631
  42.      5 +DATA/sink/onlinelog/group_5.270.964722653

  44. 5 rows selected.

  46. Elapsed: 00:00:00.00
  47. 18:31:13 SYS @ sink >select group#,status,sequence#,bytes from v$log;

  49.     GROUP# STATUS     SEQUENCE#     BYTES
  50. ---------- ---------------- ---------- ----------
  51.      1 INACTIVE         16     52428800
  52.      2 CURRENT         17     52428800
  53.      3 INACTIVE         15     52428800
  54.      4 UNUSED         0     52428800
  55.      5 UNUSED         0     52428800

  57. 5 rows selected.

  59. Elapsed: 00:00:00.02
  60. 18:33:37 SYS @ sink >alter database drop logfile group 1;

  62. Database altered.

  64. Elapsed: 00:00:00.01
  65. 18:35:03 SYS @ sink >alter database drop logfile group 3;

  67. Database altered.

  69. Elapsed: 00:00:00.01
  70. 18:35:20 SYS @ sink >select group#,status,sequence#,bytes from v$log;

  72.     GROUP# STATUS     SEQUENCE#     BYTES
  73. ---------- ---------------- ---------- ----------
  74.      2 CURRENT         17     52428800
  75.      4 UNUSED         0     52428800
  76.      5 UNUSED         0     52428800

  78. 3 rows selected.

  80. Elapsed: 00:00:00.00
  81. 18:35:56 SYS @ sink >alter system switch logfile;

  83. System altered.

  85. Elapsed: 00:00:00.01
  86. 18:36:31 SYS @ sink >r
  87.   1* alter system switch logfile

  89. System altered.

  91. Elapsed: 00:00:00.01
  92. 18:36:37 SYS @ sink >select group#,status,sequence#,bytes from v$log;

  94.     GROUP# STATUS     SEQUENCE#     BYTES
  95. ---------- ---------------- ---------- ----------
  96.      2 ACTIVE         17     52428800
  97.      4 ACTIVE         18     52428800
  98.      5 CURRENT         19     52428800

  100. 3 rows selected.

  102. Elapsed: 00:00:00.01
  103. 18:37:00 SYS @ sink >alter system checkpoint;

  105. System altered.

  107. Elapsed: 00:00:00.01
  108. 18:37:35 SYS @ sink >select group#,status,sequence#,bytes from v$log;

  110.     GROUP# STATUS     SEQUENCE#     BYTES
  111. ---------- ---------------- ---------- ----------
  112.      2 INACTIVE         17     52428800
  113.      4 INACTIVE         18     52428800
  114.      5 CURRENT         19     52428800

  116. 3 rows selected.

  118. Elapsed: 00:00:00.01
  119. 18:38:02 SYS @ sink >alter database drop logfile group 2;

  121. Database altered.

  123. Elapsed: 00:00:00.02
  124. 18:38:54 SYS @ sink >select group#,status,sequence#,bytes from v$log;

  126.     GROUP# STATUS     SEQUENCE#     BYTES
  127. ---------- ---------------- ---------- ----------
  128.      4 INACTIVE         18     52428800
  129.      5 CURRENT         19     52428800

  131. 2 rows selected.

  133. Elapsed: 00:00:00.00
  134. 18:39:30 SYS @ sink >select group#,member from v$logfile;

  136.     GROUP# MEMBER
  137. ---------- -------------------------------------------------------
  138.      4 +DATA/sink/onlinelog/group_4.269.964722631
  139.      5 +DATA/sink/onlinelog/group_5.270.964722653

  141. 2 rows selected.

  143. Elapsed: 00:00:00.00
  144. 18:40:00 SYS @ sink >alter database add logfile '+data' size 50m;

  146. Database altered.

  148. Elapsed: 00:00:00.32
  149. 18:40:36 SYS @ sink >select group#,member from v$logfile;

  151.     GROUP# MEMBER
  152. ---------- -------------------------------------------------------
  153.      1 +DATA/sink/onlinelog/group_1.271.964723237
  154.      4 +DATA/sink/onlinelog/group_4.269.964722631
  155.      5 +DATA/sink/onlinelog/group_5.270.964722653

  157. 3 rows selected.

  159. Elapsed: 00:00:00.00


好了，所有成果的效果，数据迁移完成（参数文件，控制文件，数据文件，日志文件）

  1. 18:41:47 SYS @ sink >select name from v$datafile
  2. 18:41:59 2 union all
  3. 18:42:03 3 select name from v$controlfile
  4. 18:42:18 4 union all
  5. 18:42:21 5 select member name from v$logfile;

  7. NAME
  8. -------------------------------------------------------
  9. +DATA/sink/datafile/system.258.964712643
  10. +DATA/sink/datafile/sysaux.257.964712643
  11. +DATA/sink/datafile/undotbs1.261.964712645
  12. +DATA/sink/datafile/users.263.964712657
  13. +DATA/sink/datafile/example.260.964712643
  14. +DATA/sink/datafile/tbssss.259.964712643
  15. +DATA/sink/controlfile/current.266.964717197
  16. +DATA/sink/controlfile/current.267.964717197
  17. +DATA/sink/onlinelog/group_1.271.964723237
  18. +DATA/sink/onlinelog/group_4.269.964722631
  19. +DATA/sink/onlinelog/group_5.270.964722653

  21. 11 rows selected.

  23. Elapsed: 00:00:00.01
  24. 18:42:35 SYS @ sink >


这里就是迁移到ASM的效果了

  1. ASMCMD> pwd
  2. +data/sink/datafile
  3. ASMCMD> ls
  4. EXAMPLE.260.964712643
  5. SYSAUX.257.964712643
  6. SYSTEM.258.964712643
  7. TBSSSS.256.963504823
  8. TBSSSS.259.964712643
  9. UNDOTBS1.261.964712645
  10. USERS.263.964712657
  11. ASMCMD> cd ../controlfile
  12. ASMCMD> pwd
  13. +data/sink/controlfile
  14. ASMCMD> ls
  15. Backup.262.964712653
  16. current.266.964717197
  17. current.267.964717197
  18. ASMCMD> cd ../onlinelog
  19. ASMCMD> pwd
  20. +data/sink/onlinelog
  21. ASMCMD> ls
  22. group_1.271.964723237
  23. group_4.269.964722631
  24. group_5.270.964722653
  25. ASMCMD>

尊重原创，转载请注明出处，谢谢！！！


请使用浏览器的分享功能分享到微信等