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

最新推荐文章于 2021-12-11 17:24:38 发布
最新推荐文章于 2021-12-11 17:24:38 发布
阅读量253 收藏
点赞数

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

打开数据库

  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>

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


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

转载于:http://blog.itpub.net/31405474/viewspace-2149812/

cipaidao7776
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
利用RMAN将数据库从文件系统迁移到ASM
08-27
利用RMAN将数据库从文件系统迁移到ASM
ASM转文件系统方法四种方法
pengshk 专注于oracle ebs dba跟BI
04-16 2135
1.ASM Access Through FTP and HTML Using XDB Configuration 2.rman3.dbms_file_transfer4.11g asmcmd利用RMAN将数据库从文件系统迁移到ASM一、打开ASM实例[oracle@ASM ~]$ echo $ORACLE_SID+ASM[oracle@ASM ~]$ sqlplus /nolog
数据库迁移至ASM
weixin_34258078的博客
04-20 92
  本实验主要介绍将数据库迁移到ASM的过程，并不介绍ASM实例的搭建。实验环境：solaris10+oracle 10R 1.迁移前期准备 1.1 ASM实例搭建(略) 1.2 磁盘分区使用说明 　 RDBMS 实例 ASM实例 /dev/dsk/c1t0d0s0 /u01/oracle 　 /dev/rdsk/c2t0d0s1 　 da...
文件系统迁移到ASM
kuaile_yuanzi的博客
12-30 277
磁盘组要先建好,然后备份到磁盘组里,然后用备份恢复控制文件和数据文件,然后启动库,日志和temp都后面手动添加新的,删除旧的!!! 1、首先打开归档，确认归档路径SQL> archive log list – 建磁盘组 cd /u01/app/oracle/product/11.2.0/grid/bin - - > ./asmca 2、查询下数据库文件结构SQL> select...
迁移数据文件到ASM【转】
lt89102476的博客
12-23 210
[size=small]1.迁移数据文件到ASM 1）数据库一致性情况下迁移: 将数据库启动到mount状态,生成rman copy 语句,然后在rman中执行: SQL> startup mount SQL> select q'(copy datafile ')'||name||q'(' to '+oradata';)' from v$datafile; Q'(COPYDATAFILE...
ASM维护手册之-ASM存储迁移(包括各种文件迁移）
11-10
ASM维护手册之-ASM存储迁移(包括各种文件迁移）
11G祼设备表空间通过RMAN迁移到ASM
11-01
ORACLE 11G RAC祼设备表空间通过RMAN迁移到ASM
工程日志数据库迁移到asm.doc
07-14
工程日志数据库迁移到asm.doc
数据库迁移到ASM
ciliting2867的博客
08-31 107
本文主要描述将数据库从文件系统迁移到ASM的过程。1. 将数据库以copy的方式备份到asm磁盘组DG1上：[oracle@rhel ~]$ rman target /Recovery Manager: Release 10.2...
从文件系统迁移到ASM上
weixin_34354173的博客
02-05 181
一、从文件系统迁移到ASM上 1、添加一个磁盘，fdisk分区，创建raw，start_udev 配置相应的参数以及环境 root： groupadd asmadmin groupadd asmdba groupadd asmoper useradd -g oinstall -G asmadmin,asmdba,...
文件系统与ASM的左右迁移
陈泽钦的博客
12-11 800
文件系统与ASM的左右迁移ASM迁移到文件系统rmanasmcmd cpdbms_file_transfer文件系统迁移到ASMrmanasmcmd copydbms_file_transfer ASM迁移到文件系统 现我们接到如下需求： 将 ASM 共享盘中 OPTMTBS 表空间数据文件迁移到文件系统中。 asm 迁移到文件系统上共有三种解决方案。 SQL> select a.name, b.name from v$tablespace a, v$datafile b where a.ts#
数据库迁移：文件系统迁至ASM
深蓝@黄炎龙
06-02 2478
数据库迁移：文件系统迁至ASM ***************************************************************************************** *前期目标：配置ASM、创建ASM磁盘组、启动ASM 实例 *******************************************************
实验: 把数据库迁移到ASM（完全恢复）
清风小阁
06-01 1664
网络很多帖子介绍的都是不完全恢复的方法. 注意: 迁移前要保证是归档状态下. 1 迁移在线重做日志文件 迁移重做日志的方法与不完全恢复的做法是一致的。在ASM上有两个磁盘组，一个是DATA，一个是FRA。 1.1 修改参数 SQL> show parameter db_create; NAME                                  TYPE
将数据库从普通文件系统迁移到ASM中
anshejd70787的博客
06-20 141
数据库存储的是普通的文件系统，现在将数据库迁移到ASM存储中。 准备ASM环境： [oracle@kel ~]$ asmcmd ASMCMD> ls ASM/ KEL/ ASMCMD> 在本实例中，将数据库文件都存储在KEL磁盘组中。 对数据库做一个全备份，利用备份来恢复数据文件，从而达到转移存储的目的： RMAN> run 2> { 3&g...
迁移到ASM
congxinlan3332的博客
03-07 67
一.控制文件迁移(RMAN)：SQL>alter system set control_files='+DATA' scope=spfile;SQL>shutdown immediateSQL>startup ...
oracle 11g 文件系统 迁移至asm,文件系统迁移至ASM
weixin_39976413的博客
04-14 177
操作系统AIX5.3主机名AIX213数据库版本10.2.0实例名mydb在实施迁移之前做一全库冷备！1.创建vg[root@aix213 /]$mkvg-y 'migration' -s '64' '-f' hdisk32.建立逻辑卷[root@aix213/]$mklv -y 'asm1' -t 'raw'migration 160[root@aix213/]$mklv -y 'asm2' -...
将数据库从文件系统迁移到ASM
03-01 121
支持数据库版本：10gR2 前提条件: 磁盘组 DG1 DG2 均以创建并挂载 1. 确认所要迁移的文件 SQL> select name from v$controlfile union all s...
迁移数据库到ASM
cti27515的博客
08-27 110
有个迁移到ASM存储的需求，今天研究了 觉得用RMAN的COPY+SWITCH应该是个能最短化停机时间的方法 下面记录下过程 SYS@SOURCE10 > select file# from v$datafile; ...
oracle11gR2单实例由文件系统迁移到ASM磁盘组
kadwf123的专栏
05-12 868
1、实验目标：把一个单实例oracle11g 11.2.0.1的使用文件系统的库，迁移到asm的单实例环境中去。2、磁盘组规划：1.1 磁盘组规划说明： +DG1 /dev/sdb1,/dev/sdc2 用于存放数据文件，控制文件，联机日志 +DG2 /dev/sdb3 用于存放联机日志 +RECOVERY /dev/sdb4,/dev/sdb5,/dev/sdb6 用于Recovery Area...
oracle asm 怎么加数据文件
最新发布
06-08
要向 Oracle ASM 添加数据文件，首先需要创建存储空间（diskgroup），然后将数据文件添加到存储空间中。 以下是添加数据文件的步骤： 1. 创建存储空间（如果尚未创建）。使用 ASMCMD 命令或 SQL 语句创建存储空间...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值