温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

通过辅助库(Auxiliary)做ASM迁移案例

发布时间:2020-06-20 21:54:01 来源:网络 阅读:950 作者:客居天涯 栏目:关系型数据库

通过辅助库(Auxiliary)做ASM迁移案例

系统环境:

操作系统:RedHat EL55

Oracle : Oracle 10gR2


  通过辅助库建立ASM迁移,可以很方便将文件系统的存储异机迁移;并且可减少数据库的停机时间,本案例为测试案例,目标库和辅助库都在同一台机器上。

 1、建立ASM实例

[oracle@rh65 ~]$export ORACLE_SID=+ASM

[oracle@rh65 ~]$sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 29 11:39:49 2014

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

Connected to an idle instance.

11:39:50 SYS@ +ASM>startup nomount

ASM instance started

Total System Global Area   83886080 bytes

Fixed Size                  1217836 bytes

Variable Size              57502420 bytes

ASM Cache                  25165824 bytes

11:39:57 SYS@ +ASM>select name ,state from v$asm_diskgroup;

NAME                           STATE

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

DG1                            DISMOUNTED

RCY1                           DISMOUNTED

Elapsed: 00:00:00.24

11:40:14 SYS@ +ASM>alter diskgroup dg1 mount;

Diskgroup altered.

Elapsed: 00:00:04.88

11:40:29 SYS@ +ASM>alter diskgroup rcy1 mount;

Diskgroup altered.

Elapsed: 00:00:04.77

2、建立并配置辅助库

Target DB:test1

Auxiliary DB: test1asm

11:41:58 SYS@ test1>show parameter name

NAME                                 TYPE        VALUE

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

db_name                              string      test1

db_unique_name                       string      test1

global_names                         boolean     FALSE

instance_name                        string      test1

service_names                        string      test1

11:42:06 SYS@ test1>show parameter spfile

NAME                                 TYPE        VALUE

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

spfile                               string      /u01/app/oracle/product/10.2.0

                                                 /db_1/dbs/spfiletest1.ora

11:42:11 SYS@ test1>create pfile from spfile;

File created.


建立辅助库初始化参数文件:

[oracle@rh65 dbs]$cp inittest1.ora inittest1asm.ora

[oracle@rh65 dbs]$ cat inittest1asm.ora 

*.background_dump_dest='$ORACLE_BASE/admin/test1asm/bdump'

*.control_files='+dg1/test1asm/controlfile/control01.ctl'

*.core_dump_dest='$ORACLE_BASE/admin/test1asm/cdump'

*.db_block_size=8192

*.db_cache_size=30M#DEMO

*.db_file_multiblock_read_count=16

*.db_name='test1asm'

*.instance_name='test1asm'

*.log_archive_dest_1='location=+rcy1'

*.log_archive_format='arch_%t_%s_%r.log'

*.optimizer_mode='choose'

*.parallel_threads_per_cpu=4#SMALL

*.pga_aggregate_target=30M#DEMO

*.query_rewrite_enabled='true'

*.query_rewrite_integrity='trusted'

*.sga_target=240M

*.shared_pool_size=20M#DEMO

*.star_transformation_enabled='true'

*.undo_management='auto'

*.undo_tablespace='undotbs1'

*.user_dump_dest='$ORACLE_BASE/admin/test1asm/udump'

*.db_create_file_dest='+DG1'

*.db_file_name_convert=("/u01/app/oracle/oradata/test1","+dg1/test1asm/datafile","/u01/app/oracle/oradata/test1","+dg1/test1asm/tempfile")

*.log_file_name_convert=("/u01/app/oracle/oradata/test1","+dg1/test1asm/onlinelog")

db_recovery_file_dest='+rcy1'

db_recovery_file_dest_size=2g

*.audit_file_dest='$ORACLE_BASE/admin/test1asm/adump'/bdump

建立辅助库相关目录:

[oracle@rh65 dbs]$ mkdir -p  $ORACLE_BASE/admin/test1asm/cdump

[oracle@rh65 dbs]$ mkdir -p  $ORACLE_BASE/admin/test1asm/udump

[oracle@rh65 dbs]$ mkdir -p  $ORACLE_BASE/admin/test1asm/adump

建立口令文件:

[oracle@rh65 dbs]$ orapwd file=orapwtest1asm password=oracle entries=3

建立tnsnames文件:

[oracle@rh65 admin]$ cat tnsnames.ora 

TEST1ASM =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = rh65)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = test1asm)

      ( UR = A )

    )

  )  

TEST1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = rh65)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = test1)

    )

  )

3、迁移文件系统到ASM存储

对目标库备份:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP  on;

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/disk2/bak/test1/auto_ctl_%d_%F';

RMAN> run{

2> shutdown immediate;

3> startup force mount;

4> allocate channel ch2 device type disk;

5> backup as compressed backupset database format '/disk2/bak/test1/%d_%s.bak'

6> plus archivelog format '/disk2/bak/test1/arch_%U.bak'

7> tag='full_log';

8> release channel ch2;}

启动辅助库实例:

[oracle@rh65 dbs]$export ORACLE_SID=test1asm

[oracle@rh65 dbs]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 29 12:17:22 2014

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

Connected to an idle instance.

12:17:22 SYS@ test1asm>startup nomount;

ORACLE instance started.

Total System Global Area  251658240 bytes

Fixed Size                  1218796 bytes

Variable Size              58722068 bytes

Database Buffers          188743680 bytes

Redo Buffers                2973696 bytes

15:47:09 SYS@ test1asm>show parameter name

NAME                                 TYPE        VALUE

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

db_file_name_convert                 string      /u01/app/oracle/oradata/test1,

                                                  +dg1/test1asm/datafile, /u01/

                                                 app/oracle/oradata/test1, +dg1

                                                 /test1asm/tempfile

db_name                         string      test1asm

db_unique_name                       string      test1asm

instance_name                    string      test1asm

log_file_name_convert                string      /u01/app/oracle/oradata/test1,

                                                  +dg1/test1asm/onlinelog

service_names                        string      test1asm


迁移数据文件到ASM存储:

export ORACLE_SID=test1

[oracle@rh65 dbs]$ rman target sys/oracle@test1 auxiliary sys/oracle@test1asm

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 29 15:34:03 2014

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

connected to target database: TEST1 (DBID=1174898526)

connected to auxiliary database: TEST1ASM (not mounted)

RMAN>duplicate target database to test1asm;

Starting Duplicate Db at 29-MAY-14

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=35 devtype=DISK


contents of Memory Script:

{

   set until scn  259211;

   set newname for datafile  1 to 

 "+DG1/test1asm/datafile/system01.dbf";

   set newname for datafile  2 to 

 "+DG1/test1asm/datafile/undotbs01.dbf";

   set newname for datafile  3 to 

 "+DG1/test1asm/datafile/sysaux01.dbf";

   set newname for datafile  4 to 

 "+DG1/test1asm/datafile/users01.dbf";

   restore

   check readonly

   clone database

   ;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 29-MAY-14

using channel ORA_AUX_DISK_1

skipping datafile 1; already restored to file +DG1/test1asm/datafile/system01.dbf

skipping datafile 2; already restored to file +DG1/test1asm/datafile/undotbs01.dbf

skipping datafile 3; already restored to file +DG1/test1asm/datafile/sysaux01.dbf

skipping datafile 4; already restored to file +DG1/test1asm/datafile/users01.dbf

restore not done; all files readonly, offline, or already restored

Finished restore at 29-MAY-14

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST1ASM" RESETLOGS ARCHIVELOG 

  MAXLOGFILES      5

  MAXLOGMEMBERS      5

  MAXDATAFILES      100

  MAXINSTANCES     1

  MAXLOGHISTORY      292

 LOGFILE

  GROUP  1 ( '+DG1/test1asm/onlinelog/redo01a.log' ) SIZE 100 M  REUSE,

  GROUP  2 ( '+DG1/test1asm/onlinelog/redo02a.log' ) SIZE 100 M  REUSE,

  GROUP  3 ( '+DG1/test1asm/onlinelog/redo03a.log' ) SIZE 100 M  REUSE

 DATAFILE

  '+DG1/test1asm/datafile/system01.dbf'

 CHARACTER SET ZHS16GBK

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

released channel: ORA_AUX_DISK_1

datafile 2 switched to datafile copy

input datafile copy recid=1 stamp=848849467 filename=+DG1/test1asm/datafile/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=2 stamp=848849467 filename=+DG1/test1asm/datafile/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=3 stamp=848849467 filename=+DG1/test1asm/datafile/users01.dbf

contents of Memory Script:

{

   set until scn  259211;

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause


Starting recover at 29-MAY-14

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=35 devtype=DISK

starting media recovery

archive log thread 1 sequence 17 is already on disk as file /disk1/arch_test1/arch_1_17_797856158.log

archive log thread 1 sequence 18 is already on disk as file /disk1/arch_test1/arch_1_18_797856158.log

archive log filename=/disk1/arch_test1/arch_1_17_797856158.log thread=1 sequence=17

archive log filename=/disk1/arch_test1/arch_1_18_797856158.log thread=1 sequence=18

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

Finished recover at 29-MAY-14

contents of Memory Script:

{

   shutdown clone;

   startup clone nomount ;

}

executing Memory Script

database dismounted

Oracle instance shut down

RMAN-00571: ===========================================================

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

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 05/29/2014 15:37:59

RMAN-03015: error occurred in stored script Memory Script

RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

  (以上错误信息:是因为Instance shutdown后,连接不到Instance,可以通过建立静态监听避免这个错误,这个错误不影响数据的迁移)

OPEN辅助库:

5:39:01 SYS@ test1asm>startup mount;

ORACLE instance started.

Total System Global Area  251658240 bytes

Fixed Size                  1218796 bytes

Variable Size              58722068 bytes

Database Buffers          188743680 bytes

Redo Buffers                2973696 bytes

Database mounted.

15:39:28 SYS@ test1asm>select name from v$datafile;

NAME

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

+DG1/test1asm/datafile/system01.dbf

+DG1/test1asm/datafile/undotbs01.dbf

+DG1/test1asm/datafile/sysaux01.dbf

+DG1/test1asm/datafile/users01.dbf

Elapsed: 00:00:00.20

15:39:39 SYS@ test1asm>select name from v$controlfile;

NAME

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

+DG1/test1asm/controlfile/control01.ctl

Elapsed: 00:00:00.04

15:39:47 SYS@ test1asm>select member from v$logfile;

MEMBER

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

+DG1/test1asm/onlinelog/redo03a.log

+DG1/test1asm/onlinelog/redo02a.log

+DG1/test1asm/onlinelog/redo01a.log

Elapsed: 00:00:00.05

Open database

15:41:39 SYS@ test1asm>alter database open resetlogs;

Database altered.

Elapsed: 00:00:59.79

查看数据信息:

15:42:47 SYS@ test1asm>select count(*) from scott.emp1;

  COUNT(*)

----------

        28

Elapsed: 00:00:00.11

15:43:20 SYS@ test1asm>select count(*) from scott.dept1;

  COUNT(*)

----------

         4

Elapsed: 00:00:00.06

15:43:29 SYS@ test1asm>

建立临时表空间:

15:46:46 SYS@ test1asm>create temporary tablespace temp;

Tablespace created.

Elapsed: 00:00:03.13

15:47:05 SYS@ test1asm>select name from v$tempfile;

NAME

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

+DG1/test1asm/tempfile/temp.265.848850423


4、数据迁移告警日志

alert 日志:

Starting background process ASMB

ASMB started with pid=16, OS id=6507

Starting background process RBAL

RBAL started with pid=17, OS id=6511

Loaded ASM Library - Generic Linux, version 2.0.4 (KABI_V2) library for asmlib interface

Thu May 29 15:36:00 2014

SUCCESS: diskgroup DG1 was mounted

SUCCESS: diskgroup DG1 was dismounted

SUCCESS: diskgroup DG1 was mounted

SUCCESS: diskgroup DG1 was dismounted

SUCCESS: diskgroup DG1 was mounted

SUCCESS: diskgroup DG1 was dismounted

SUCCESS: diskgroup DG1 was mounted

SUCCESS: diskgroup DG1 was dismounted

Thu May 29 15:36:03 2014

The input backup piece /disk2/bak/test1/TEST1_8.bak is in compressed format.

SUCCESS: diskgroup DG1 was mounted

Thu May 29 15:36:21 2014

Full restore complete of datafile 4 to datafile copy +DG1/test1asm/datafile/users01.dbf.  Elapsed time: 0:00:18 

  checkpoint is 256751

Thu May 29 15:36:33 2014

Full restore complete of datafile 2 to datafile copy +DG1/test1asm/datafile/undotbs01.dbf.  Elapsed time: 0:00:30 

  checkpoint is 256751

Full restore complete of datafile 1 to datafile copy +DG1/test1asm/datafile/system01.dbf.  Elapsed time: 0:00:38 

  checkpoint is 256751

  

  Thu May 29 15:36:56 2014

SUCCESS: diskgroup DG1 was dismounted

Full restore complete of datafile 3 to datafile copy +DG1/test1asm/datafile/sysaux01.dbf.  Elapsed time: 0:00:53 

  checkpoint is 256751

Thu May 29 15:36:58 2014

CREATE CONTROLFILE REUSE SET DATABASE "TEST1ASM" RESETLOGS ARCHIVELOG 

  MAXLOGFILES      5

  MAXLOGMEMBERS      5

  MAXDATAFILES      100

  MAXINSTANCES     1

  MAXLOGHISTORY      292

 LOGFILE

  GROUP  1 ( '+DG1/test1asm/onlinelog/redo01a.log' ) SIZE 100 M  REUSE,

  GROUP  2 ( '+DG1/test1asm/onlinelog/redo02a.log' ) SIZE 100 M  REUSE,

  GROUP  3 ( '+DG1/test1asm/onlinelog/redo03a.log' ) SIZE 100 M  REUSE

 DATAFILE

  '+DG1/test1asm/datafile/system01.dbf'

 CHARACTER SET ZHS16GBK

Thu May 29 15:36:58 2014

WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command

Default Temporary Tablespace will be necessary for a locally managed database in future release

SUCCESS: diskgroup DG1 was mounted

SUCCESS: diskgroup DG1 was dismounted

SUCCESS: diskgroup DG1 was mounted

SUCCESS: diskgroup DG1 was dismounted

SUCCESS: diskgroup DG1 was mounted

SUCCESS: diskgroup DG1 was dismounted

Thu May 29 15:37:03 2014

SUCCESS: diskgroup DG1 was mounted

Thu May 29 15:37:03 2014

Setting recovery target incarnation to 1

Thu May 29 15:37:03 2014

Successful mount of redo thread 1, with mount id 891011546

Thu May 29 15:37:03 2014

Completed: CREATE CONTROLFILE REUSE SET DATABASE "TEST1ASM" RESETLOGS ARCHIVELOG 

  MAXLOGFILES      5

  MAXLOGMEMBERS      5

  MAXDATAFILES      100

  MAXINSTANCES     1

  MAXLOGHISTORY      292

 LOGFILE

  GROUP  1 ( '+DG1/test1asm/onlinelog/redo01a.log' ) SIZE 100 M  REUSE,

  GROUP  2 ( '+DG1/test1asm/onlinelog/redo02a.log' ) SIZE 100 M  REUSE,

  GROUP  3 ( '+DG1/test1asm/onlinelog/redo03a.log' ) SIZE 100 M  REUSE

 DATAFILE

  '+DG1/test1asm/datafile/system01.dbf'

 CHARACTER SET ZHS16GBK

Switch of datafile 2 complete to datafile copy 

  checkpoint is 256751

Switch of datafile 3 complete to datafile copy 

  checkpoint is 256751

Switch of datafile 4 complete to datafile copy 

  checkpoint is 256751

Thu May 29 15:37:07 2014

alter database recover datafile list clear

Completed: alter database recover datafile list clear

Thu May 29 15:37:07 2014

alter database recover datafile list

 1 , 2 , 3 , 4

Completed: alter database recover datafile list

 1 , 2 , 3 , 4

Thu May 29 15:37:07 2014

alter database recover if needed

 start until change 259211 using backup controlfile

Media Recovery Start

ORA-279 signalled during: alter database recover if needed

 start until change 259211 using backup controlfile

...

Thu May 29 15:37:07 2014

alter database recover logfile '/disk1/arch_test1/arch_1_17_797856158.log'

Thu May 29 15:37:07 2014

Media Recovery Log /disk1/arch_test1/arch_1_17_797856158.log

ORA-279 signalled during: alter database recover logfile '/disk1/arch_test1/arch_1_17_797856158.log'...

Thu May 29 15:37:10 2014

alter database recover logfile '/disk1/arch_test1/arch_1_18_797856158.log'

Thu May 29 15:37:10 2014

Media Recovery Log /disk1/arch_test1/arch_1_18_797856158.log

Thu May 29 15:37:10 2014

Incomplete Recovery applied until change 259211

Thu May 29 15:37:10 2014

Media Recovery Complete (test1asm)

Completed: alter database recover logfile '/disk1/arch_test1/arch_1_18_797856158.log'

Shutting down instance: further logons disabled


@至此,从文件系统迁移数据到ASM存储完成,通过ASM存储,可以提升数据库的I/O性能,并提高对数据的保护能力。





向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI