Shulou(Shulou.com)06/01 Report--

Use Auxiliary to do ASM migration cases.

System environment:

Operating system: RedHat EL55

Oracle: Oracle 10gR2

Through the establishment of ASM migration through the auxiliary library, it is very convenient to migrate the storage of the file system out of machine, and can reduce the downtime of the database. This case is a test case, and the target library and the auxiliary library are on the same machine.

1. Create an ASM instance

[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. Establish and configure the auxiliary library

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.

Establish an auxiliary library initialization parameter file:

[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

Establish a catalog related to the auxiliary library:

[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

Create a password file:

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

Create the tnsnames file:

[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. Migrate the file system to ASM storage

Back up the target library:

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;}

Start the auxiliary library instance:

[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

Migrate data files to ASM storage:

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 100m REUSE

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

GROUP 3 ('+ DG1/test1asm/onlinelog/redo03a.log') SIZE 100m 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

(the above error message: because the Instance cannot be connected after Instance shutdown, this error can be avoided by establishing static listening. This error does not affect data migration.)

OPEN Helper Library:

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

View data information:

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

COUNT (*)

-

twenty-eight

Elapsed: 00:00:00.11

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

COUNT (*)

-

four

Elapsed: 00:00:00.06

15:43:29 SYS@ test1asm >

Create temporary tablespaces:

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. Data migration alarm log

Alert log:

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 100m REUSE

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

GROUP 3 ('+ DG1/test1asm/onlinelog/redo03a.log') SIZE 100m 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 100m REUSE

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

GROUP 3 ('+ DG1/test1asm/onlinelog/redo03a.log') SIZE 100m 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

@ so far, migrating data from the file system to ASM storage is complete. Through ASM storage, you can improve the performance of the database and improve the ability to protect data.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.