同机同版本数据库从文件系统迁移至ASM实验

最新推荐文章于 2024-08-14 23:11:00 发布
最新推荐文章于 2024-08-14 23:11:00 发布
阅读量111 收藏
点赞数
文章标签： 数据库 操作系统 网络
Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE 1. 实验环境说明

操作系统的版本是Oracle Linux 5内核是2.6.18-164.el5

[root@book ~]# uname -a

Linux book 2.6.18-164.el5 #1 SMP Thu Sep 3 02:16:47 EDT 2009 i686 i686 i386 GNU/Linux

Oracle数据库的版本是：

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

 

本机已经建立了ASM实例

[oracle@book ~]$ echo $ORACLE_SID

+ASM

[oracle@book ~]$ sqlplus  / as sysdba

 

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 3 23:00:14 2013

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

 

SQL> select name,total_mb,free_mb from v$asm_diskgroup;

 

NAME                             TOTAL_MB    FREE_MB

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

DGROUP01                             3579       2580

 

文件系统数据库实例说明

[oracle@book ~]$ export ORACLE_SID=practice

[oracle@book ~]$ echo $ORACLE_SID

practice

[oracle@book ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 3 23:04:22 2013

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

 

SQL> select name from v$database;

 

NAME

---------

PRACTICE

 

SQL> select name from v$datafile;

 

NAME

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

/u01/app/oracle/oradata/practice/system01.dbf

/u01/app/oracle/oradata/practice/undotbs01.dbf

/u01/app/oracle/oradata/practice/sysaux01.dbf

/u01/app/oracle/oradata/practice/users01.dbf

/u01/app/oracle/oradata/practice/users02.dbf

/u01/app/oracle/oradata/practice/tools01.dbf

/u01/app/oracle/oradata/practice/ts4drop01.dbf

 

7 rows selected.

 

SQL> select name from v$controlfile;

 

NAME

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

/u01/app/oracle/oradata/practice/control01.ctl

/u01/app/oracle/oradata/practice/control02.ctl

/u01/app/oracle/oradata/practice/control03.ctl

/home/oracle/flash_recover_area/HEADDB/control_file/control04.ctl

/home/oracle/flash_recover_area/HEADDB/control_file/control05.ctl

/home/oracle/flash_recover_area/HEADDB/control_file/control06.ctl

 

6 rows selected.

 

SQL> select name from v$tempfile;

 

NAME

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

/u01/app/oracle/oradata/practice/temp01.dbf

 

SQL> select member from v$logfile;

 

MEMBER

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

/u01/app/oracle/oradata/practice/redo031.log

/u01/app/oracle/oradata/practice/redo021.log

/u01/app/oracle/oradata/practice/redo011.log

/home/oracle/flash_recover_area/practice/logfile/redo012.log

/home/oracle/flash_recover_area/practice/logfile/redo022.log

/home/oracle/flash_recover_area/practice/logfile/redo032.log

 

6 rows selected.

2. 备份文件系统数据库

[oracle@book ~]$ echo $ORACLE_SID

practice

[oracle@book ~]$ rman target /

 

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Feb 3 22:26:27 2013

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

connected to target database: PRACTICE (DBID=2994901982)

RMAN> list backup;

 

 

RMAN> backup database plus archivelog delete all input;

 

Starting backup at 03-FEB-13

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=1 recid=1 stamp=804288718

input archive log thread=1 sequence=2 recid=2 stamp=804299193

input archive log thread=1 sequence=3 recid=3 stamp=804306343

input archive log thread=1 sequence=4 recid=4 stamp=804306840

input archive log thread=1 sequence=5 recid=5 stamp=804308863

channel ORA_DISK_1: starting piece 1 at 03-FEB-13

channel ORA_DISK_1: finished piece 1 at 03-FEB-13

piece handle=/u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_annnn_TAG20130203T222943_8jwx2v5p_.bkp tag=TAG20130203T222943 comment=NONE

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

channel ORA_DISK_1: deleting archive log(s)

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_01_09/o1_mf_1_1_8gtwb349_.arc recid=1 stamp=804288718

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_01_10/o1_mf_1_2_8gv6kpxb_.arc recid=2 stamp=804299193

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_01_10/o1_mf_1_3_8gvfk6mw_.arc recid=3 stamp=804306343

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_01_10/o1_mf_1_4_8gvg0rt7_.arc recid=4 stamp=804306840

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_01_10/o1_mf_1_5_8gvhzzmw_.arc recid=5 stamp=804308863

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=1 recid=6 stamp=804747422

input archive log thread=1 sequence=2 recid=7 stamp=804749079

input archive log thread=1 sequence=3 recid=8 stamp=804749153

input archive log thread=1 sequence=4 recid=9 stamp=804749448

input archive log thread=1 sequence=5 recid=10 stamp=804749622

input archive log thread=1 sequence=6 recid=11 stamp=806452181

channel ORA_DISK_1: starting piece 1 at 03-FEB-13

channel ORA_DISK_1: finished piece 1 at 03-FEB-13

piece handle=/u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_annnn_TAG20130203T222943_8jwx6c7r_.bkp tag=TAG20130203T222943 comment=NONE

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

channel ORA_DISK_1: deleting archive log(s)

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_01_15/o1_mf_1_1_8h8w8vxw_.arc recid=6 stamp=804747422

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_01_15/o1_mf_1_2_8h8xwqpo_.arc recid=7 stamp=804749079

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_01_15/o1_mf_1_3_8h8xz1dq_.arc recid=8 stamp=804749153

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_01_15/o1_mf_1_4_8h8y888s_.arc recid=9 stamp=804749448

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_01_15/o1_mf_1_5_8h8yfox8_.arc recid=10 stamp=804749622

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_02_03/o1_mf_1_6_8jwx29l6_.arc recid=11 stamp=806452181

Finished backup at 03-FEB-13

 

Starting backup at 03-FEB-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/u01/app/oracle/oradata/practice/system01.dbf

input datafile fno=00003 name=/u01/app/oracle/oradata/practice/sysaux01.dbf

input datafile fno=00002 name=/u01/app/oracle/oradata/practice/undotbs01.dbf

input datafile fno=00005 name=/u01/app/oracle/oradata/practice/users02.dbf

input datafile fno=00006 name=/u01/app/oracle/oradata/practice/tools01.dbf

input datafile fno=00007 name=/u01/app/oracle/oradata/practice/ts4drop01.dbf

input datafile fno=00004 name=/u01/app/oracle/oradata/practice/users01.dbf

channel ORA_DISK_1: starting piece 1 at 03-FEB-13

channel ORA_DISK_1: finished piece 1 at 03-FEB-13

piece handle=/u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_nnndf_TAG20130203T223152_8jwx6y2q_.bkp tag=TAG20130203T223152 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:03:43

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 03-FEB-13

channel ORA_DISK_1: finished piece 1 at 03-FEB-13

piece handle=/u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_ncsnf_TAG20130203T223152_8jwxg0j3_.bkp tag=TAG20130203T223152 comment=NONE

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

Finished backup at 03-FEB-13

 

Starting backup at 03-FEB-13

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=7 recid=12 stamp=806452564

channel ORA_DISK_1: starting piece 1 at 03-FEB-13

channel ORA_DISK_1: finished piece 1 at 03-FEB-13

piece handle=/u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_annnn_TAG20130203T223607_8jwxgrv1_.bkp tag=TAG20130203T223607 comment=NONE

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

channel ORA_DISK_1: deleting archive log(s)

archive log filename=/u01/app/oracle/flash_recovery_area/PRACTICE/archivelog/2013_02_03/o1_mf_1_7_8jwxgng2_.arc recid=12 stamp=806452564

Finished backup at 03-FEB-13

 

RMAN> list backup;

 

 

List of Backup Sets

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

 

BS Key  Size       Device Type Elapsed Time Completion Time

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

4       11.92M     DISK        00:00:07     03-FEB-13     

        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20130203T222943

        Piece Name: /u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_annnn_TAG20130203T222943_8jwx6c7r_.bkp

 

  List of Archived Logs in backup set 4

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    1       474409     10-JAN-13 499157     15-JAN-13

  1    2       499157     15-JAN-13 500057     15-JAN-13

  1    3       500057     15-JAN-13 500084     15-JAN-13

  1    4       500084     15-JAN-13 500198     15-JAN-13

  1    5       500198     15-JAN-13 500417     15-JAN-13

  1    6       500417     15-JAN-13 504497     03-FEB-13

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

5       Full    512.37M    DISK        00:03:38     03-FEB-13     

        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20130203T223152

        Piece Name: /u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_nnndf_TAG20130203T223152_8jwx6y2q_.bkp

  List of Datafiles in backup set 5

  File LV Type Ckp SCN    Ckp Time  Name

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

  1       Full 504516     03-FEB-13 /u01/app/oracle/oradata/practice/system01.dbf

  2       Full 504516     03-FEB-13 /u01/app/oracle/oradata/practice/undotbs01.dbf

  3       Full 504516     03-FEB-13 /u01/app/oracle/oradata/practice/sysaux01.dbf

  4       Full 504516     03-FEB-13 /u01/app/oracle/oradata/practice/users01.dbf

  5       Full 504516     03-FEB-13 /u01/app/oracle/oradata/practice/users02.dbf

  6       Full 504516     03-FEB-13 /u01/app/oracle/oradata/practice/tools01.dbf

  7       Full 504516     03-FEB-13 /u01/app/oracle/oradata/practice/ts4drop01.dbf

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

6       Full    6.80M      DISK        00:00:12     03-FEB-13     

        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20130203T223152

        Piece Name: /u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_ncsnf_TAG20130203T223152_8jwxg0j3_.bkp

  Control File Included: Ckp SCN: 504566       Ckp time: 03-FEB-13

  SPFILE Included: Modification time: 03-FEB-13

 

BS Key  Size       Device Type Elapsed Time Completion Time

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

7       484.00K    DISK        00:00:02     03-FEB-13     

        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20130203T223607

        Piece Name: /u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_annnn_TAG20130203T223607_8jwxgrv1_.bkp

 

  List of Archived Logs in backup set 7

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    7       504497     03-FEB-13 504603     03-FEB-13

3. 关闭文件系统数据库实例

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> create pfile from spfile;

 

File created.

后面我们要修改控制文件的位置指向，SPFILE是二进制文件，无法直接修改，我们先创建出一个pfile文件，然后修改pfile。当然也可以在还没有关闭数据库的时候执行如下命令：

Alter database set control_files=’+ DGROUP01’ scope=spfile;

然后再关闭数据库，设置即可生效。这里采用直接修改pfile的方式。

4. 修改参数文件

首先把文件系统的SPFILE备份

[oracle@book dbs]$ mv spfilepractice.ora spfilepractice.orabak

修改practice实例的参数文件，使其控制文件指向ASM

[oracle@book dbs]$ vi initpractice.ora

practice.__db_cache_size=494927872

practice.__java_pool_size=4194304

practice.__large_pool_size=4194304

practice.__shared_pool_size=226492416

practice.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/practice/adump'

*.background_dump_dest='/u01/app/oracle/admin/practice/bdump'

*.compatible='10.2.0.1.0'

*.control_files='+DGROUP01/practice/controlfile/control01.ctl','+DGROUP01/practice/controlfile/control02.ctl','+DGROUP01/practice/controlfile/control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/practice/cdump'

*.db_block_size=8192

*.db_domain='oracle.com'

*.db_file_multiblock_read_count=16

*.db_name='practice'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=5368709120

*.dispatchers='(PROTOCOL=TCP) (SERVICE=practiceXDB)'

*.fast_start_mttr_target=3600

*.job_queue_processes=10

*.log_archive_format='ARC%S_%R.%T.dbf'

*.open_cursors=300

*.pga_aggregate_target=314572800

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=734003200

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/practice/udump'

5. 检查ASM实例的状态

[oracle@book ~]$ echo $ORACLE_SID

+ASM

[oracle@book ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 3 23:39:12 2013

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

 

SQL> select name,total_mb,free_mb from v$asm_diskgroup;

 

NAME                             TOTAL_MB    FREE_MB

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

DGROUP01                             3579       2580

6. 恢复待迁移实例的控制文件

[oracle@book ~]$ echo $ORACLE_SID

practice

[oracle@book ~]$ rman target /

 

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Feb 3 23:42:13 2013

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

connected to target database (not started)

 

RMAN> startup nomount;

 

Oracle instance started

 

Total System Global Area     734003200 bytes

 

Fixed Size                     1221564 bytes

Variable Size                234884164 bytes

Database Buffers             494927872 bytes

Redo Buffers                   2969600 bytes

 

RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_ncsnf_TAG20130203T223152_8jwxg0j3_.bkp';

 

Starting restore at 03-FEB-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

 

channel ORA_DISK_1: restoring control file

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

output filename=+DGROUP01/practice/controlfile/control01.ctl

output filename=+DGROUP01/practice/controlfile/control02.ctl

output filename=+DGROUP01/practice/controlfile/control03.ctl

Finished restore at 03-FEB-13

我们再去ASM磁盘组中看看控制文件是否已经迁移至ASM中

[oracle@book ~]$ asmcmd

ASMCMD> pwd

+

ASMCMD> ls /DGROUP01/PRACTICE/CONTROLFILE/

Current.266.806456845

control01.ctl

control02.ctl

control03.ctl

current.267.806456853

current.268.806456857

这些控制文件已经迁移成功。

7. 恢复数据库

控制文件恢复以后，就可以把数据库起到mount状态。

SQL> alter database mount;

 

Database altered.

在RMAN窗口中执行如下命令来恢复数据文件：

RMAN> run{

2> set newname for datafile 1 to '+DGROUP01';

3> set newname for datafile 2 to '+DGROUP01';

4> set newname for datafile 3 to '+DGROUP01';

5> set newname for datafile 4 to '+DGROUP01';

6> set newname for datafile 5 to '+DGROUP01';

7> set newname for datafile 6 to '+DGROUP01';

8> set newname for datafile 7 to '+DGROUP01';

9> restore database;

10> switch datafile all;

11> recover database;

12> }

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

Starting restore at 04-FEB-13

using channel ORA_DISK_1

 

channel ORA_DISK_1: restoring datafile 00002

input datafile copy recid=5 stamp=806463586 filename=+DGROUP01/practice/datafile/undotbs1.271.806463507

destination for restore of datafile 00002: +DGROUP01

channel ORA_DISK_1: copied datafile copy of datafile 00002

output filename=+DGROUP01/practice/datafile/undotbs1.270.806463833 recid=6 stamp=806463842

channel ORA_DISK_1: restoring datafile 00004

input datafile copy recid=1 stamp=806463533 filename=+DGROUP01/practice/datafile/users.274.806463523

destination for restore of datafile 00004: +DGROUP01

channel ORA_DISK_1: copied datafile copy of datafile 00004

output filename=+DGROUP01/practice/datafile/users.269.806463849 recid=7 stamp=806463849

channel ORA_DISK_1: restoring datafile 00006

input datafile copy recid=3 stamp=806463581 filename=+DGROUP01/practice/datafile/tools.272.806463517

destination for restore of datafile 00006: +DGROUP01

channel ORA_DISK_1: copied datafile copy of datafile 00006

output filename=+DGROUP01/practice/datafile/tools.275.806463851 recid=8 stamp=806463852

channel ORA_DISK_1: restoring datafile 00007

input datafile copy recid=4 stamp=806463582 filename=+DGROUP01/practice/datafile/ts4frop.273.806463521

destination for restore of datafile 00007: +DGROUP01

channel ORA_DISK_1: copied datafile copy of datafile 00007

output filename=+DGROUP01/practice/datafile/ts4frop.276.806463855 recid=9 stamp=806463855

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to +DGROUP01

restoring datafile 00003 to +DGROUP01

restoring datafile 00005 to +DGROUP01

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_nnndf_TAG20130203T223152_8jwx6y2q_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/app/oracle/flash_recovery_area/PRACTICE/backupset/2013_02_03/o1_mf_nnndf_TAG20130203T223152_8jwx6y2q_.bkp tag=TAG20130203T223152

channel ORA_DISK_1: restore complete, elapsed time: 00:05:16

Finished restore at 04-FEB-13

 

datafile 1 switched to datafile copy

input datafile copy recid=13 stamp=806464172 filename=+DGROUP01/practice/datafile/system.277.806463857

datafile 2 switched to datafile copy

input datafile copy recid=14 stamp=806464173 filename=+DGROUP01/practice/datafile/undotbs1.270.806463833

datafile 3 switched to datafile copy

input datafile copy recid=15 stamp=806464173 filename=+DGROUP01/practice/datafile/sysaux.278.806463859

datafile 4 switched to datafile copy

input datafile copy recid=16 stamp=806464173 filename=+DGROUP01/practice/datafile/users.269.806463849

datafile 5 switched to datafile copy

input datafile copy recid=17 stamp=806464173 filename=+DGROUP01/practice/datafile/users.279.806463861

datafile 6 switched to datafile copy

input datafile copy recid=18 stamp=806464173 filename=+DGROUP01/practice/datafile/tools.275.806463851

datafile 7 switched to datafile copy

input datafile copy recid=19 stamp=806464173 filename=+DGROUP01/practice/datafile/ts4frop.276.806463855

 

Starting recover at 04-FEB-13

using channel ORA_DISK_1

 

starting media recovery

 

archive log thread 1 sequence 7 is already on disk as file /home/oracle/flash_recover_area/practice/logfile/redo012.log

archive log thread 1 sequence 8 is already on disk as file /home/oracle/flash_recover_area/practice/logfile/redo022.log

archive log filename=/home/oracle/flash_recover_area/practice/logfile/redo012.log thread=1 sequence=7

archive log filename=/home/oracle/flash_recover_area/practice/logfile/redo022.log thread=1 sequence=8

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

Finished recover at 04-FEB-13

数据文件都恢复之后就可以打开数据库了，因为使用的是备份集中恢复回来的控制文件，打开数据库时，必须要加上resetlogs选项。

SQL> select * from v$recover_file;

 

no rows selected

 

SQL> alter database open resetlogs;

 

Database altered.

8. 迁移日志文件

首先查看当前的日志文件的状态

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

 

    GROUP# STATUS

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

         1 UNUSED

         2 CURRENT

         3 UNUSED

 

SQL> select group#,member from v$logfile;

 

    GROUP# MEMBER

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

         3 /u01/app/oracle/oradata/practice/redo031.log

         2 /u01/app/oracle/oradata/practice/redo021.log

         1 /u01/app/oracle/oradata/practice/redo011.log

         1 /home/oracle/flash_recover_area/practice/logfile/redo012.log

         2 /home/oracle/flash_recover_area/practice/logfile/redo022.log

         3 /home/oracle/flash_recover_area/practice/logfile/redo032.log

从以上信息判断，目前日志文件都在文件系统中，并且只有日志组2部可以删除。我们先删除可以删除的组，然后在ASM中添加新的日志组，以此达到迁移的目的。

SQL> alter database drop logfile group 1;

 

Database altered.

 

SQL> alter database add logfile group 1 '+DGROUP01' size 50M;

 

Database altered.

 

SQL> alter database drop logfile group 3;

 

Database altered.

 

SQL> alter database add logfile group 3 '+DGROUP01' size 50M;

 

Database altered.

 

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

 

    GROUP# STATUS

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

         1 UNUSED

         2 CURRENT

         3 UNUSED

现在还需要删除group2，先执行一次日志切换

SQL> alter system switch logfile;

 

System altered.

 

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

 

    GROUP# STATUS

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

         1 CURRENT

         2 ACTIVE

         3 UNUSED

现在group2是active状态，让系统生成以此检查点

SQL> alter system checkpoint;

 

System altered.

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

 

    GROUP# STATUS

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

         1 CURRENT

         2 INACTIVE

         3 UNUSED

现在可以删除了。

SQL> alter database drop logfile group 2;

 

Database altered.

 

SQL> alter database add logfile group 2 '+DGROUP01' size 50M;

 

Database altered.

 

SQL> select member from v$logfile;

 

MEMBER

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

+DGROUP01/practice/onlinelog/group_3.281.806469935

+DGROUP01/practice/onlinelog/group_2.282.806470267

+DGROUP01/practice/onlinelog/group_1.280.806465581

9. 迁移临时文件

查看临时文件当前的位置

SQL> select name from v$tempfile;

 

NAME

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

/u01/app/oracle/oradata/practice/temp01.dbf

可以先给temp表空间增加一个位于ASM的临时数据文件，再删除位于文件系统的临时文件。

SQL> alter tablespace temp add tempfile '+DGROUP01' size 20M;

 

Tablespace altered.

 

SQL> select name from v$tempfile;

 

NAME

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

/u01/app/oracle/oradata/practice/temp01.dbf

+DGROUP01/practice/tempfile/temp.283.806470587

 

SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/practice/temp01.dbf';

 

Tablespace altered.

 

SQL> select name from v$tempfile;

 

NAME

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

+DGROUP01/practice/tempfile/temp.283.806470587

10. 检查迁移结果

SQL> select name  from v$datafile;

 

NAME

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

+DGROUP01/practice/datafile/system.277.806463857

+DGROUP01/practice/datafile/undotbs1.270.806463833

+DGROUP01/practice/datafile/sysaux.278.806463859

+DGROUP01/practice/datafile/users.269.806463849

+DGROUP01/practice/datafile/users.279.806463861

+DGROUP01/practice/datafile/tools.275.806463851

+DGROUP01/practice/datafile/ts4frop.276.806463855

 

7 rows selected.

 

SQL> select name from v$controlfile;

 

NAME

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

+DGROUP01/practice/controlfile/control01.ctl

+DGROUP01/practice/controlfile/control02.ctl

+DGROUP01/practice/controlfile/control03.ctl

 

SQL> select member from v$logfile;

 

MEMBER

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

+DGROUP01/practice/onlinelog/group_3.281.806469935

+DGROUP01/practice/onlinelog/group_2.282.806470267

+DGROUP01/practice/onlinelog/group_1.280.806465581

 

SQL> select name from v$tempfile;

 

NAME

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

+DGROUP01/practice/tempfile/temp.283.806470587

至此，数据文件、控制文件、日志文件、临时文件都已迁移至ASM中，迁移完成

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

转载于:http://blog.itpub.net/28850681/viewspace-759792/

cloud0912
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle数据库有文件系统迁移到ASM
zhlh_xt的专栏
05-04 1084
本实验所涉及的是将数据库有文件系统迁移到ASM上，首先要确保本机已经配置好ASM。主要过程如下： ①对当前库做全备      backup database plus archivelog; ②修改pfile，把控制文件指向ASM中      create pfile from spfile;      或者alter system set control_files='+FLASH_A
【数据迁移1】Oracle 10gR2 rman异机恢复实验（FS-RAW）(截图).pdf
07-23
本文将围绕标题、描述及部分展示的内容，全面解析Oracle数据库的数据迁移过程，尤其是从文件系统（FS）到原始分区（RAW）的数据恢复策略。 ### Oracle 10gR2 RMAN异机恢复概述 Oracle 10gR2（10.2.0.1版）是Oracle...
数据库迁移：文件系统迁至ASM
深蓝@黄炎龙
06-02 2490
数据库迁移：文件系统迁至ASM ***************************************************************************************** *前期目标：配置ASM、创建ASM磁盘组、启动ASM 实例 *******************************************************
数据库从文件系统转移至ASM实验记录
abc123098098的博客
11-16 84
实验环境：WinXP SP2 数据库版本：10.2.0.1 准备迁移的数据库实例名：TEST ASM实例名：+ASM ASM磁盘组：+TEST 创建ASM实例和磁盘组的步骤这里不再重复，请参考：http://space.itpub.net/498744/viewspace-247789 Microsoft Windows XP [版本 5.1.2600] (C) 版权所...
数据库从文件系统转移至ASM实验记录[转]
cuanyangpa3685的博客
03-30 97
实验环境：WinXP SP2数据库版本：10.2.0.1准备迁移的数据库实例名：TESTASM实例名：+ASMASM磁盘组：+TESTref: http://woodnan.itpub.net/post/37055/458037...
oracle asm 到 asm rman 迁移,使用RMAN将文件系统迁移到ASM
weixin_35662171的博客
04-05 215
1，全备数据库PRODrun{allocate channel c1 device type disk;allocate channel c2 device type disk;backup tag 'SH_DB' as compressed backupset format ‘/home/Oracle/full_%U.bak' databaseinclude current controlfil...
Oracle从文件系统迁移到ASM存储
weixin_34177064的博客
12-03 188
环境：RHEL 6.4 + Oracle 11.2.0.4 需求：数据库存储由文件系统迁移到ASM 数据库存储迁移到ASM磁盘组 1.1 编辑参数文件指定新的控制文件路径 1.2 启动数据库到nomount模式 1.3 RMAN恢复控制文件 1.4 启动数据库到mount模式 1.5 RMAN Copy 数据文件 1.6 RMAN Switch 数据文件 1.7 RMAN Switch 临时文件...
oracle临时文件存储位置,Oracle从文件系统迁移到ASM存储
weixin_39962758的博客
04-03 204
环境：RHEL 6.4 + Oracle 11.2.0.4需求：数据库存储由文件系统迁移到ASM数据库存储迁移到ASM磁盘组实验环境前期准备:迁移前原数据库各文件存储信息查询如下：--控制文件select name from v$controlfile;--数据文件select name from v$datafile;--临时文件select name from v$tempfile;--日志文...
oracle11数据库冷备迁移,oracle备份与恢复实验之冷备份--由ASM冷备迁移数据库到文件系统...
weixin_31720623的博客
04-04 225
实验环境如下：第一台机器模拟源数据库，第二台机器模拟目标数据库数据库版本：oracle 11.2.0.1.0操作系统:rhel5.6测试目的:第一台机器为ASM环境，第二台机器为文件系统，将asm中的数据文件通过冷备份的方式迁移到文件系统中。注明：本例在测试环境完成，在安装grid时未单独创建grid用户，可以使用oracle提供的oraenv命令来切换环境变量在本例中，为区别迁移的数据库，备份的...
oracle数据库迁移-精选.pdf
05-27
物理迁移通常包括加载初始化文件、控制文件、数据库文件和最后启动数据库。确保控制文件路径正确至关重要，否则可能导致迁移失败。 在实践中，例如将数据库从文件系统迁移到ASM磁盘组，ASM（Automatic Storage ...
ASM操作加盘.7z
07-03
Oracle ASM（Automatic ..."ASM操作加盘.xlsx"文件可能包含了这些操作的详细步骤、截图或实验记录，对于理解并实际操作ASM的磁盘管理非常有帮助。对于学习和掌握Oracle RAC环境下的ASM存储管理，了解这些内容至关重要。
【oracle日常维护】维护时需要用到的各种unix操作系统命令
11-17
- 实验记录还包括了故障切换和恢复测试，这对于确保系统的稳定性至关重要。 5. **Oracle RAC CRS、OCR、Voting 破坏重建** - 这篇文章提供了关于Oracle RAC集群资源管理器(CRS)、OCR (Oracle Cluster Registry) ...
[手工整理]208个Oracle安装文档，包含各个平台各个版本的单实例RAC以及DataGuard(1).xlsx
05-26
97 Oracle 迁移至 PostgreSQL 在华为 ARM 上的解决方案 98 Oracle 数据库隐含参数设置 99 Oracle 隐含参数 100 Oracle 诊断事件及深入解析10053事件 101 OS deploying-odg-with-oda-1615029 102 OS IBM AIX ...
JDBC详细使用
m0_73627305的博客
08-09 535
​ JDBC是一种标准,一种规则,主要作用是使用java语言操作数据库的​ JDBC这个标准中有很多的接口,接口中有很多方法。
seatunnel2.3.3在centos7上安装
鑫哥
08-09 892
安装前需要准备点环境，因为seatunnel是基于java开发的，因此需要先安装java，我这里使用的java1.8，可以网上搜索下java安装教程，记得配置JAVA_HOME环境变量。在安装的过程中需要用到mysql命令，因此也需要安装下MySQL环境，这里也不具体讲解了，在网上搜索安装就行。接下来就是正式安装seatunnel了，
springboot民办高校科研项目管理系统-计算机毕业设计源码54009
VX_DZbishe的博客
08-11 540
论文主要是对基于springboot的民办高校科研项目管理系统进行了介绍，包括研究的现状，还有涉及的开发背景，然后还对系统的设计目标进行了论述，还有系统的需求以及整个的设计方案，对系统的设计以及实现，也都论述的比较细致，最后对基于springboot的民办高校科研项目管理系统进行了一些具体测试。本次报告，首先分析了研究的背景、作用、意义，为研究工作的合理性打下了基础。
mysql介绍
最新发布
weixin_57763462的博客
08-14 280
丰富的存储引擎：MySQL 提供了多种存储引擎，如 InnoDB、MyISAM、Memory 等，每种存储引擎都有其特定的应用场景和优势。多语言支持：MySQL 支持多种编程语言，包括 C、C++、Python、Java、Perl、PHP 等，并提供了相应的 API。安全性：MySQL 提供了灵活的权限和密码系统，支持基于主机的验证，并且所有的密码传输都采用加密形式，确保了数据的安全性。管理工具：MySQL 提供了丰富的管理工具，如命令行客户端、图形界面管理工具等，方便用户进行数据库的管理和维护。
zabbix的自动发现和注册、proxy代理和SNMP监控
Alone8046的博客
08-09 374
二、zabbix通过代理服务器添加主机：分布式监控，代理服务器的作用就是分担 server 的集中式压力；先有服务器，再有模版，模版当中设置应用集，模版当中设置应用集，再应用集中配置监控项，在监控项之后配置触发器，最后配置图形（可选项）zabbix proxy（代理服务器）：代理服务器也要安装数据库，保存客户端的数据，然后再发送给服务端（代收器）zabbix客户端主动的和服务端联系，将自己的地址和端口发送给服务端，实现自动添加监控主机。snmp：简单网络管理协议，用来监控网络设备，可以监控交换机和路由器。
Oracle数据库裸设备到ASM迁移指南
此外，还提及了不同数据库版本间的迁移，特别是从10.2.0.1到10.2.0.2的升级过程。" 在Oracle数据库管理中，有时需要将使用裸设备存储的数据库迁移到使用ASM进行自动存储管理。这个过程涉及到多个步骤，首先是环境的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值