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

AIX environment file system migration to ASM storage

System environment:

Operating system: AIX5.3-08

Oracle: Oracle 10gR2

First, build an ASM storage environment

Http://tiany.blog.51cto.com/513694/1418564

Oracle DataBase single instance using ASM case (4)-- AIX system Oracle 10g Application

Migrate the file system to ASM

1. View ASM Instance

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

[oracle@aix211] $! sql

Sqlplus'/ as sysdba'

SQL*Plus: Release 10.2.0.1.0-Production on Thu May 29 17:23:24 2014

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

Connected to an idle instance.

SQL > startup

ASM instance started

Total System Global Area 130023424 bytes

Fixed Size 2019136 bytes

Variable Size 102838464 bytes

ASM Cache 25165824 bytes

ASM diskgroups mounted

SQL > select name, state from v$asm_diskgroup

NAME STATE

DG1 MOUNTED

RCY1 MOUNTED

SQL >

2. Modify instance parameters

SQL > alter system set db_create_file_dest ='+ dg1' scope=spfile

System altered.

SQL > show parameter control

NAME TYPE VALUE

-

Control_file_record_keep_time integer 7

Control_files string / u01/app/oracle/oradata/prod/c

Ontrol01.ctl, / dsk1/oradata/pr

Od/control02.ctl

SQL > alter system set control_files='+dg1' scope=spfile

System altered.

3. Dump the control file to the control file

RMAN > shutdown immediate

Database closed

Database dismounted

Oracle instance shut down

RMAN > startup nomount

Connected to target database (not started)

Oracle instance started

Total System Global Area 1258291200 bytes

Fixed Size 2020552 bytes

Variable Size 318769976 bytes

Database Buffers 922746880 bytes

Redo Buffers 14753792 bytes

RMAN > restore controlfile from'/ u01max App amp oracle control 01.ctl'

Starting restore at 29-MAY-14

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: sid=156 devtype=DISK

Channel ORA_DISK_1: copied control file copy

Output filename=+DG1/prod/controlfile/backup.256.848857069

Finished restore at 29-MAY-14

4. Dump data files to ASM disk group

RMAN > alter database mount

Database mounted

Released channel: ORA_DISK_1

RMAN > backup as copy database format'+ dg1'

Starting backup at 29-MAY-14

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: sid=156 devtype=DISK

Channel ORA_DISK_1: starting datafile copy

Input datafile fno=00001 name=/u01/app/oracle/oradata/prod/system01.dbf

Output filename=+DG1/prod/datafile/system.257.848857161 tag=TAG20140529T173920 recid=12 stamp=848857281

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:05

Channel ORA_DISK_1: starting datafile copy

Input datafile fno=00003 name=/u01/app/oracle/oradata/prod/sysaux01.dbf

Output filename=+DG1/prod/datafile/sysaux.258.848857287 tag=TAG20140529T173920 recid=13 stamp=848857355

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15

Channel ORA_DISK_1: starting datafile copy

Input datafile fno=00005 name=/u01/app/oracle/oradata/prod/example01.dbf

Output filename=+DG1/prod/datafile/example.259.848857363 tag=TAG20140529T173920 recid=14 stamp=848857404

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45

Channel ORA_DISK_1: starting datafile copy

Input datafile fno=00002 name=/u01/app/oracle/oradata/prod/undotbs01.dbf

Output filename=+DG1/prod/datafile/undotbs1.260.848857407 tag=TAG20140529T173920 recid=15 stamp=848857434

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35

Channel ORA_DISK_1: starting datafile copy

Input datafile fno=00004 name=/u01/app/oracle/oradata/prod/users01.dbf

Output filename=+DG1/prod/datafile/users.261.848857443 tag=TAG20140529T173920 recid=16 stamp=848857457

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

Finished backup at 29-MAY-14

Starting Control File and SPFILE Autobackup at 29-MAY-14

Piece handle=/backup/c-259825570-20140529-01 comment=NONE

Finished Control File and SPFILE Autobackup at 29-MAY-14

RMAN > switch database to copy

Datafile 1 switched to datafile copy "+ DG1/prod/datafile/system.257.848857161"

Datafile 2 switched to datafile copy "+ DG1/prod/datafile/undotbs1.260.848857407"

Datafile 3 switched to datafile copy "+ DG1/prod/datafile/sysaux.258.848857287"

Datafile 4 switched to datafile copy "+ DG1/prod/datafile/users.261.848857443"

Datafile 5 switched to datafile copy "+ DG1/prod/datafile/example.259.848857363"

5. Restore the database

RMAN > recover database

Starting recover at 29-MAY-14

Using channel ORA_DISK_1

Starting media recovery

Media recovery complete, elapsed time: 00:00:00

Finished recover at 29-MAY-14

RMAN > alter database open

Database opened

6. Migrate temporary tablespaces

SQL > select name from v$datafile

NAME

+ DG1/prod/datafile/system.257.848857161

+ DG1/prod/datafile/undotbs1.260.848857407

+ DG1/prod/datafile/sysaux.258.848857287

+ DG1/prod/datafile/users.261.848857443

+ DG1/prod/datafile/example.259.848857363

SQL > select name from v$tempfile

NAME

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

SQL > alter tablespace temp add tempfile'+ dg1'

Tablespace altered.

SQL > select name from v$tempfile

NAME

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

+ DG1/prod/tempfile/temp.262.848858041

SQL > alter tablespace temp drop tempfile'/ u01qqappActionoradataUnimax temp01.dbf'

Tablespace altered.

SQL > select name from v$tempfile

NAME

+ DG1/prod/tempfile/temp.262.848858041

7. Migrate redo log groups

SQL > set linesize 120

SQL > select * from v$log

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

-

11 114 52428800 2 YES INACTIVE 989355 29-MAY-14

2 1 115 52428800 2 YES INACTIVE 989368 29-MAY-14

3 1 113 52428800 2 YES INACTIVE 988986 29-MAY-14

4 1 116 52428800 2 NO CURRENT 989387 29-MAY-14

SQL > col member for A50

SQL > select group#,member from v$logfile

GROUP# MEMBER

3 / dsk1/oradata/prod/redo03a.log

2 / dsk1/oradata/prod/redo02a.log

1/ dsk1/oradata/prod/redo01a.log

1 / dsk2/oradata/prod/redo01b.log

2/ dsk2/oradata/prod/redo02b.log

3 / dsk2/oradata/prod/redo03b.log

4 / dsk1/oradata/prod/redo04a.log

4 / dsk2/oradata/prod/redo04b.log

8 rows selected.

Add a new log group to ASM diskgroup:

SQL > alter database add logfile

2 ('+ dg1','+rcy1') size 10m

SQL > alter database add logfile

2 ('+ dg1','+rcy1') size 10m

SQL > select * from v$log

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

-

11 114 52428800 2 YES INACTIVE 989355 29-MAY-14

2 1 115 52428800 2 YES INACTIVE 989368 29-MAY-14

3 1 113 52428800 2 YES INACTIVE 988986 29-MAY-14

4 1 116 52428800 2 NO CURRENT 989387 29-MAY-14

5 10 10485760 2 YES UNUSED 0

6 10 10485760 2 YES UNUSED 0

6 rows selected.

SQL > select group#,member from v$logfile order by 1

GROUP# MEMBER

1/ dsk1/oradata/prod/redo01a.log

1 / dsk2/oradata/prod/redo01b.log

2 / dsk1/oradata/prod/redo02a.log

2/ dsk2/oradata/prod/redo02b.log

3 / dsk1/oradata/prod/redo03a.log

3 / dsk2/oradata/prod/redo03b.log

4 / dsk1/oradata/prod/redo04a.log

4 / dsk2/oradata/prod/redo04b.log

5 + RCY1/prod/onlinelog/group_5.256.848858217

5 + DG1/prod/onlinelog/group_5.263.848858213

6 + RCY1/prod/onlinelog/group_6.257.848858259

GROUP# MEMBER

6 + DG1/prod/onlinelog/group_6.264.848858253

12 rows selected.

SQL > select * from v$log

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

-

11 114 52428800 2 YES INACTIVE 989355 29-MAY-14

2 1 115 52428800 2 YES INACTIVE 989368 29-MAY-14

3 1 113 52428800 2 YES INACTIVE 988986 29-MAY-14

4 1 116 52428800 2 YES ACTIVE 989387 29-MAY-14

5 1 117 10485760 2 YES ACTIVE 990481 29-MAY-14

6 1 118 10485760 2 NO CURRENT 990483 29-MAY-14

6 rows selected.

Delete the old log group:

SQL > alter database drop logfile group 1

Database altered.

SQL > alter database drop logfile group 2

Database altered.

SQL > alter database drop logfile group 3

Database altered.

SQL > alter system checkpoint

System altered.

SQL > select * from v$log

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

-

4 1 119 52428800 2 YES ACTIVE 990548 29-MAY-14

5 1 120 10485760 2 NO CURRENT 990566 29-MAY-14

6 1 118 10485760 2 YES INACTIVE 990483 29-MAY-14

SQL > alter system switch logfile

System altered.

SQL > select * from v$log

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

-

4 1 119 52428800 2 YES INACTIVE 990548 29-MAY-14

5 1 120 10485760 2 NO ACTIVE 990566 29-MAY-14

6 1 121 10485760 2 NO CURRENT 991692 29-MAY-14

SQL > alter database drop logfile group 4

Database altered.

SQL > select * from v$log

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

-

5 1 122 10485760 2 NO CURRENT 991711 29-MAY-14

6 1 121 10485760 2 YES INACTIVE 991692 29-MAY-14

SQL > select group#,member from v$logfile order by 1

GROUP# MEMBER

5 + DG1/prod/onlinelog/group_5.263.848858213

5 + RCY1/prod/onlinelog/group_5.256.848858217

6 + DG1/prod/onlinelog/group_6.264.848858253

6 + RCY1/prod/onlinelog/group_6.257.848858259

Adjust the recover area parameters:

SQL > show parameter recover

NAME TYPE VALUE

-

Db_recovery_file_dest string

Db_recovery_file_dest_size big integer 0

Recovery_parallelism integer 0

SQL > alter system set db_recovery_file_dest='+rcy1' scope=spfile

System altered.

SQL > alter system set db_recovery_file_dest_size=2g scope=spfile

System altered.

8. Delete the original data files under the file system

[oracle@aix211 ~] $cd / u01/app/oracle/oradata/prod/

[oracle@aix211 prod] $ls

Control01.ctl example01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf

[oracle@aix211 prod] $rm *

[oracle@aix211 prod] $ls

[oracle@aix211 prod] $

For spfile parameters can be stored on a file system or ASM disk group:

SQL > show parameter pfile

NAME TYPE VALUE

-

Spfile string / u01/app/oracle/product/10.2.0

/ db_1/dbs/spfileprod.ora

@ so far, the migration of file systems to ASM disk groups under AIX has been completed, and the read and write performance of the database has been improved to a certain extent through ASM storage.

Note:

The redo log group migration script provided by the Oracle official documentation:

Example 8-1 Migrating the online redo logs

SET SERVEROUTPUT ON

DECLARE

CURSOR rlc IS

SELECT GROUP# GRP, THREAD# THR, BYTES, 'NO' SRL

FROM V$LOG

UNION

SELECT GROUP# GRP, THREAD# THR, BYTES, 'YES' SRL

FROM V$STANDBY_LOG

ORDER BY 1

Stmt VARCHAR2 (2048)

BEGIN

FOR rlcRec IN rlc LOOP

IF (rlcRec.srl = 'YES') THEN

Stmt: = 'ALTER DATABASE ADD STANDBY LOGFILE THREAD' | |

RlcRec.thr | | 'SIZE' | | rlcRec.bytes

EXECUTE IMMEDIATE stmt

Stmt: = 'ALTER DATABASE DROP STANDBY LOGFILE GROUP' | | rlcRec.grp

EXECUTE IMMEDIATE stmt

ELSE

Stmt: = 'ALTER DATABASE ADD LOGFILE THREAD' | |

RlcRec.thr | | 'SIZE' | | rlcRec.bytes

EXECUTE IMMEDIATE stmt

BEGIN

Stmt: = 'ALTER DATABASE DROP LOGFILE GROUP' | | rlcRec.grp

DBMS_OUTPUT.PUT_LINE (stmt)

EXECUTE IMMEDIATE stmt

EXCEPTION

WHEN OTHERS THEN

EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE'

EXECUTE IMMEDIATE 'ALTER SYSTEM CHECKPOINT GLOBAL'

EXECUTE IMMEDIATE stmt

END

END IF

END LOOP

END

/

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.