通过辅助库（Auxiliary）做ASM迁移案例-Mysql Tutorial-php.cn

通过辅助库（Auxiliary）做ASM迁移案例系统环境：操作系统：RedHat EL55 Oracle ： Oracle 10gR2 通过辅助库建立ASM迁移，可以很方便将文件系统的存储异机迁移；并且可减少数据库的停机时间，本案例为测试案例，目标库和辅助库都在同一台机器上。 1、建立A

通过辅助库（Auxiliary）做ASM迁移案例

系统环境：

操作系统：RedHat EL55

Oracle ： Oracle 10gR2

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

1、建立ASM实例

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

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

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

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@rh55 dbs]$cp inittest1.ora inittest1asm.ora

[oracle@rh55 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@rh55 dbs]$ mkdir -p $ORACLE_BASE/admin/test1asm/cdump

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

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

建立口令文件：

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

建立tnsnames文件：

[oracle@rh55 admin]$ cat tnsnames.ora

TEST1ASM =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = test1asm)

( UR = A )

)

TEST1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rh55)(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 ch1 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 ch1;}

启动辅助库实例：

[oracle@rh55 dbs]$export ORACLE_SID=test1asm

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

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

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@rh55 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

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

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(*)

----------

Elapsed: 00:00:00.11

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

COUNT(*)

----------

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性能，并提高对数据的保护能力。

Php8, I'm coming too

Learn website layout in 30 minutes

Shangguan Oracle Beginner to Proficient Video Tutorial

Your first line of UNI-APP code

Flutter from scratch to app launch

Brother Lian New Linux Video Tutorial

AXURE 9 Video Tutorial (Suitable for Product Manager Interactive Product Design UI)

Zero Basic Proficiency PS Video Tutorial

16 day UI video tutorial to get you started

PS Techniques and Slicing Techniques Video Tutorial

Alibaba Cloud Environment Construction and Project Launch Video Tutorial

Overview of Computer Networks - Basic Knowledge that Programmers Must Master

Essential Tutorial for Programmers - HTTP Protocol Explanation

Websocket Video Tutorial

通过辅助库（Auxiliary）做ASM迁移案例