ITPub博客

首页 > 数据库 > Oracle > 文件系统迁移到ASM(数据迁移)

文件系统迁移到ASM(数据迁移)

原创 Oracle 作者:大鲨鱼o0O 时间:2018-01-06 18:23:35 0 删除 编辑

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

打开数据库

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

  3. Total System Global Area 835104768 bytes
  4. Fixed Size         2257840 bytes
  5. Variable Size         553651280 bytes
  6. Database Buffers     276824064 bytes
  7. Redo Buffers         2371584 bytes
  8. Database mounted.
  9. Database opened.
  10. 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 /

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

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

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

  5. 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> }

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

  12. allocated channel: dev2
  13. channel dev2: SID=41 device type=DISK

  14. allocated channel: dev3
  15. channel dev3: SID=40 device type=DISK

  16. allocated channel: dev4
  17. channel dev4: SID=42 device type=DISK

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

  59. 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;

  2. List of Datafile Copies
  3. =======================

  4. Key File S Completion Time Ckp SCN Ckp Time
  5. ------- ---- - --------------- ---------- ---------------
  6. 9 1 A 06-JAN-18 1218041 06-JAN-18
  7.         Name: +DATA/sink/datafile/system.258.964712643
  8.         Tag: ORA_ASM_MIGRATION

  9. 8 2 A 06-JAN-18 1218040 06-JAN-18
  10.         Name: +DATA/sink/datafile/sysaux.257.964712643
  11.         Tag: ORA_ASM_MIGRATION

  12. 4 3 A 06-JAN-18 1218045 06-JAN-18
  13.         Name: +DATA/sink/datafile/undotbs1.261.964712645
  14.         Tag: ORA_ASM_MIGRATION

  15. 6 4 A 06-JAN-18 1218050 06-JAN-18
  16.         Name: +DATA/sink/datafile/users.263.964712657
  17.         Tag: ORA_ASM_MIGRATION

  18. 7 5 A 06-JAN-18 1218043 06-JAN-18
  19.         Name: +DATA/sink/datafile/example.260.964712643
  20.         Tag: ORA_ASM_MIGRATION

  21. 3 6 A 06-JAN-18 1218042 06-JAN-18
  22.         Name: +DATA/sink/datafile/tbssss.259.964712643
  23.         Tag: ORA_ASM_MIGRATION


  24. RMAN>


转储spfile到

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

  2. Starting restore at 06-JAN-18
  3. using target database control file instead of recovery catalog
  4. allocated channel: ORA_DISK_1
  5. channel ORA_DISK_1: SID=18 device type=DISK

  6. channel ORA_DISK_1: starting datafile backup set restore
  7. channel ORA_DISK_1: restoring SPFILE
  8. output file name=+DATA/spfilesink.ora
  9. channel ORA_DISK_1: reading from backup piece +DATA/sink/backupset/2018_01_06/nnsnn0_ora_asm_migration_0.264.964712657
  10. channel ORA_DISK_1: piece handle=+DATA/sink/backupset/2018_01_06/nnsnn0_ora_asm_migration_0.264.964712657 tag=ORA_ASM_MIGRATION
  11. channel ORA_DISK_1: restored backup piece 1
  12. channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
  13. 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

  3. SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 6 16:30:19 2018

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


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

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

  11. Total System Global Area 835104768 bytes
  12. Fixed Size         2257840 bytes
  13. Variable Size         553651280 bytes
  14. Database Buffers     276824064 bytes
  15. Redo Buffers         2371584 bytes
  16. 16:30:38 SYS @ sink >alter system set db_create_file_dest='+DATA';

  17. System altered.

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

  20. NAME                 TYPE     VALUE
  21. ------------------------------------ ----------- ------------------------------
  22. create_bitmap_area_size      integer     8388608
  23. create_stored_outlines         string
  24. db_create_file_dest         string     +DATA
  25. db_create_online_log_dest_1     string
  26. db_create_online_log_dest_2     string
  27. db_create_online_log_dest_3     string
  28. db_create_online_log_dest_4     string
  29. db_create_online_log_dest_5     string
  30. 16:31:33 SYS @ sink >


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

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

  2. STATUS
  3. ------------
  4. STARTED

  5. 1 row selected.

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


  12. Elapsed: 00:00:00.00
  13. 16:35:39 SYS @ sink >startup force;
  14. ORACLE instance started.

  15. Total System Global Area 835104768 bytes
  16. Fixed Size         2257840 bytes
  17. Variable Size         553651280 bytes
  18. Database Buffers     276824064 bytes
  19. Redo Buffers         2371584 bytes
  20. Database mounted.
  21. Database opened.
  22. 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

  3.      FILE# NAME
  4. ---------- -------------------------------------------------------
  5.      1 /u01/app/oracle/oradata/sink/system01.dbf
  6.      2 /u01/app/oracle/oradata/sink/sysaux01.dbf
  7.      3 /u01/app/oracle/oradata/sink/undotbs01.dbf
  8.      4 /u01/app/oracle/oradata/sink/users01.dbf
  9.      5 /u01/app/oracle/oradata/sink/example01.dbf
  10.      6 +DATA/sink/datafile/tbssss.256.963504823

  11. 6 rows selected.

  12. Elapsed: 00:00:00.01


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

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

  2. Starting restore at 06-JAN-18
  3. using target database control file instead of recovery catalog
  4. allocated channel: ORA_DISK_1
  5. channel ORA_DISK_1: SID=42 device type=DISK

  6. RMAN-00571: ===========================================================
  7. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  8. RMAN-00571: ===========================================================
  9. RMAN-03002: failure of restore command at 01/06/2018 16:45:57
  10. 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/';

  2. RMAN-00571: ===========================================================
  3. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  4. RMAN-00571: ===========================================================
  5. RMAN-00558: error encountered while parsing input commands
  6. 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, (, ;"
  7. RMAN-01007: at line 1 column 71 file: standard input


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

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

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

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


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

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

  10. STATUS
  11. ------------
  12. OPEN

  13. 1 row selected.

  14. Elapsed: 00:00:00.01
  15. 16:49:10 SYS @ sink >startup nomount;
  16. ORA-01081: cannot start already-running ORACLE - shut it down first
  17. 16:49:17 SYS @ sink >startup force nomount;
  18. ORACLE instance started.

  19. Total System Global Area 835104768 bytes
  20. Fixed Size         2257840 bytes
  21. Variable Size         553651280 bytes
  22. Database Buffers     276824064 bytes
  23. Redo Buffers         2371584 bytes
  24. 16:49:33 SYS @ sink >select status from v$instance;

  25. STATUS
  26. ------------
  27. STARTED

  28. 1 row selected.

  29. Elapsed: 00:00:00.01
  30. 16:49:57 SYS @ sink >


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

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

  2. Starting restore at 06-JAN-18
  3. using target database control file instead of recovery catalog
  4. allocated channel: ORA_DISK_1
  5. channel ORA_DISK_1: SID=23 device type=DISK

  6. channel ORA_DISK_1: copied control file copy
  7. output file name=+DATA/sink/controlfile/current.266.964717197
  8. output file name=+DATA/sink/controlfile/current.267.964717197
  9. Finished restore at 06-JAN-18

  10. 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;

  2. database mounted
  3. released channel: ORA_DISK_1

  4. RMAN> switch database to copy;

  5. using target database control file instead of recovery catalog
  6. datafile 1 switched to datafile copy "+DATA/sink/datafile/system.258.964712643"
  7. datafile 2 switched to datafile copy "+DATA/sink/datafile/sysaux.257.964712643"
  8. datafile 3 switched to datafile copy "+DATA/sink/datafile/undotbs1.261.964712645"
  9. datafile 4 switched to datafile copy "+DATA/sink/datafile/users.263.964712657"
  10. datafile 5 switched to datafile copy "+DATA/sink/datafile/example.260.964712643"
  11. datafile 6 switched to datafile copy "+DATA/sink/datafile/tbssss.259.964712643"



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

  1. RMAN> exit


  2. Recovery Manager complete.
  3. [oracle@sink dbs]$ !sql
  4. sqlplus / as sysdba

  5. SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 6 17:42:31 2018

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


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

  11. 17:42:41 SYS @ sink >col name for a55
  12. 17:42:57 SYS @ sink >r
  13.   1* select name from v$datafile

  14. NAME
  15. -------------------------------------------------------
  16. +DATA/sink/datafile/system.258.964712643
  17. +DATA/sink/datafile/sysaux.257.964712643
  18. +DATA/sink/datafile/undotbs1.261.964712645
  19. +DATA/sink/datafile/users.263.964712657
  20. +DATA/sink/datafile/example.260.964712643
  21. +DATA/sink/datafile/tbssss.259.964712643

  22. 6 rows selected.

  23. Elapsed: 00:00:00.01
  24. 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> }

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

  11. allocated channel: dev2
  12. channel dev2: SID=26 device type=DISK

  13. allocated channel: dev3
  14. channel dev3: SID=27 device type=DISK

  15. allocated channel: dev4
  16. channel dev4: SID=28 device type=DISK

  17. Starting recover at 06-JAN-18

  18. starting media recovery
  19. media recovery complete, elapsed time: 00:00:00

  20. Finished recover at 06-JAN-18
  21. released channel: dev1
  22. released channel: dev2
  23. released channel: dev3
  24. released channel: dev4

  25. RMAN>


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

  1. RMAN> exit
  2. Recovery Manager complete.
  3. [oracle@sink dbs]$ !sql
  4. sqlplus / as sysdba

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

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


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

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

  12. Database altered.

  13. Elapsed: 00:00:02.44
  14. 17:59:50 SYS @ sink >select name from v$tempfile;

  15. NAME
  16. -------------------------------------------------------
  17. /u01/app/oracle/oradata/sink/temp01.dbf

  18. 1 row selected.

  19. Elapsed: 00:00:00.02
  20. 18:00:05 SYS @ sink >select tablespace_name,contents from dba_tablespaces;

  21. TABLESPACE_NAME      CONTENTS
  22. ------------------------------ ---------
  23. SYSTEM             PERMANENT
  24. SYSAUX             PERMANENT
  25. UNDOTBS1         UNDO
  26. TEMP             TEMPORARY
  27. USERS             PERMANENT
  28. EXAMPLE          PERMANENT
  29. TBSSSS             PERMANENT

  30. 7 rows selected.

  31. Elapsed: 00:00:00.01


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

  1. 18:00:24 SYS @ sink >alter tablespace temp add tempfile '+data';

  2. Tablespace altered.

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

  5. NAME
  6. -------------------------------------------------------
  7. /u01/app/oracle/oradata/sink/temp01.dbf
  8. +DATA/sink/tempfile/temp.268.964720887

  9. 2 rows selected.

  10. Elapsed: 00:00:00.01


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

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

  2. Tablespace altered.

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

  5. NAME
  6. -------------------------------------------------------
  7. /u01/app/oracle/oradata/sink/temp01.dbf
  8. +DATA/sink/tempfile/temp.268.964720887

  9. 2 rows selected.

  10. 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.

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

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

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

  24. Database altered.

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

  27. Database altered.

  28. Elapsed: 00:00:02.34
  29. 18:23:19 SYS @ sink >col tablespace_name for a10
  30. 18:23:38 SYS @ sink >col file_name for a55
  31. 18:23:54 SYS @ sink >r
  32.   1* select tablespace_name,file_name from dba_temp_files

  33. TABLESPACE FILE_NAME
  34. ---------- -------------------------------------------------------
  35. TEMP     +DATA/sink/tempfile/temp.268.964720887

  36. 1 row selected.

  37. Elapsed: 00:00:00.00


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

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

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

  7. 3 rows selected.

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

  11.     GROUP# MEMBER
  12. ---------- -------------------------------------------------------
  13.      3 /u01/app/oracle/oradata/sink/redo03.log
  14.      2 /u01/app/oracle/oradata/sink/redo02.log
  15.      1 /u01/app/oracle/oradata/sink/redo01.log

  16. 3 rows selected.

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

  19. Database altered.

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

  22. Database altered.

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

  25.     GROUP# MEMBER
  26. ---------- -------------------------------------------------------
  27.      3 /u01/app/oracle/oradata/sink/redo03.log
  28.      2 /u01/app/oracle/oradata/sink/redo02.log
  29.      1 /u01/app/oracle/oradata/sink/redo01.log
  30.      4 +DATA/sink/onlinelog/group_4.269.964722631
  31.      5 +DATA/sink/onlinelog/group_5.270.964722653

  32. 5 rows selected.

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

  35.     GROUP# STATUS     SEQUENCE#     BYTES
  36. ---------- ---------------- ---------- ----------
  37.      1 INACTIVE         16     52428800
  38.      2 CURRENT         17     52428800
  39.      3 INACTIVE         15     52428800
  40.      4 UNUSED         0     52428800
  41.      5 UNUSED         0     52428800

  42. 5 rows selected.

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

  45. Database altered.

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

  48. Database altered.

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

  51.     GROUP# STATUS     SEQUENCE#     BYTES
  52. ---------- ---------------- ---------- ----------
  53.      2 CURRENT         17     52428800
  54.      4 UNUSED         0     52428800
  55.      5 UNUSED         0     52428800

  56. 3 rows selected.

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

  59. System altered.

  60. Elapsed: 00:00:00.01
  61. 18:36:31 SYS @ sink >r
  62.   1* alter system switch logfile

  63. System altered.

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

  66.     GROUP# STATUS     SEQUENCE#     BYTES
  67. ---------- ---------------- ---------- ----------
  68.      2 ACTIVE         17     52428800
  69.      4 ACTIVE         18     52428800
  70.      5 CURRENT         19     52428800

  71. 3 rows selected.

  72. Elapsed: 00:00:00.01
  73. 18:37:00 SYS @ sink >alter system checkpoint;

  74. System altered.

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

  77.     GROUP# STATUS     SEQUENCE#     BYTES
  78. ---------- ---------------- ---------- ----------
  79.      2 INACTIVE         17     52428800
  80.      4 INACTIVE         18     52428800
  81.      5 CURRENT         19     52428800

  82. 3 rows selected.

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

  85. Database altered.

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

  88.     GROUP# STATUS     SEQUENCE#     BYTES
  89. ---------- ---------------- ---------- ----------
  90.      4 INACTIVE         18     52428800
  91.      5 CURRENT         19     52428800

  92. 2 rows selected.

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

  95.     GROUP# MEMBER
  96. ---------- -------------------------------------------------------
  97.      4 +DATA/sink/onlinelog/group_4.269.964722631
  98.      5 +DATA/sink/onlinelog/group_5.270.964722653

  99. 2 rows selected.

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

  102. Database altered.

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

  105.     GROUP# MEMBER
  106. ---------- -------------------------------------------------------
  107.      1 +DATA/sink/onlinelog/group_1.271.964723237
  108.      4 +DATA/sink/onlinelog/group_4.269.964722631
  109.      5 +DATA/sink/onlinelog/group_5.270.964722653

  110. 3 rows selected.

  111. 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;

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

  19. 11 rows selected.

  20. Elapsed: 00:00:00.01
  21. 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>

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


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

请登录后发表评论 登录
全部评论

注册时间:2016-10-26

  • 博文量
    32
  • 访问量
    5208