martes, 19 de julio de 2011

Migrating Oracle10g Database to Automatic Storage management (ASM)

Step By Step Instructions on Migrating Oracle10g Database to Automatic Storage

Disable Block change tracking:

SQL> select * from v$block_change_tracking;

STATUS

----------

FILENAME

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

BYTES

----------

DISABLED



If not disabled then, disble using this command.


SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
Database altered.


SQL>

Shutdown Database Cleanly:

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options
Create pfile and add/modify the below parameters:


[oracle@node1-pub oracle]$ sqlplus "/ as sysdba"


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 12:17:50 2006



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



Connected to an idle instance.



SQL> create pfile from spfile;
File created.


Modify pfile with these parameters:

I have already created 2 ASM diskgroups DATA and FLASH.


*.control_files=(+DATA, +FLASH)

*.db_recovery_file_dest=+FLASH

*.db_recovery_file_dest_size=2147483648

*.db_create_file_dest=+DATA

*.db_create_online_log_dest_1=+FLASH

*.db_create_online_log_dest_2=+DATA -- optional if you want another online redo logs dest.


Create spfile back from modified pfile:

PS: take a copy of original spfile before you overwrite spfile using below command.

SQL> create spfile from pfile;



File created.

SQL> exit

Disconnected


Copy Database to ASM diskgroups using rman:

(1) start the instance on NOMOUNT state

(2) copy the controlfile from old location to ASM usin "resrore" rman command

(3) mount the database

(4) copy the datafiles to ASM disk group using rman "BACKUP AS COPY DATABASE" command

(5) Switch database to COPY and open the database.



[oracle@node1-pub oracle]$ $ORACLE_HOME/bin/rman



Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jul 21 10:03:10 2006



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



RMAN> connect target



connected to target database (not started)



RMAN> startup nomount



Oracle instance started



Total System Global Area 167772160 bytes



Fixed Size 1218316 bytes

Variable Size 83888372 bytes

Database Buffers 79691776 bytes

Redo Buffers 2973696 bytes



RMAN> restore controlfile from '/home/oracle/oradata/db10g/control01.ctl';



Starting restore at 21-JUL-06

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK



channel ORA_DISK_1: copied control file copy

output filename=+DATA/db10g/controlfile/backup.256.596369129

output filename=+FLASH/db10g/controlfile/backup.256.596369131

Finished restore at 21-JUL-06



RMAN> startup mount



database is already started

database mounted

released channel: ORA_DISK_1



RMAN> configure device type disk parallelism 4;



new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters are successfully stored



RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';


Starting backup at 21-JUL-06

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=152 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=151 devtype=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: sid=150 devtype=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: sid=149 devtype=DISK

channel ORA_DISK_1: starting datafile copy

input datafile fno=00001 name=/home/oracle/oradata/db10g/system01.dbf

channel ORA_DISK_2: starting datafile copy

input datafile fno=00003 name=/home/oracle/oradata/db10g/sysaux01.dbf

channel ORA_DISK_3: starting datafile copy

input datafile fno=00002 name=/home/oracle/oradata/db10g/undotbs01.dbf

channel ORA_DISK_4: starting datafile copy

input datafile fno=00004 name=/home/oracle/oradata/db10g/users01.dbf

output filename=+DATA/db10g/datafile/undotbs1.259.596369341 tag=TAG20060721T100858 recid=2 stamp=596369352

channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:16

channel ORA_DISK_3: starting datafile copy

copying current control file

output filename=+DATA/db10g/datafile/users.260.596369341 tag=TAG20060721T100858 recid=1 stamp=596369350

channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:20

channel ORA_DISK_4: starting full datafile backupset

channel ORA_DISK_4: specifying datafile(s) in backupset

output filename=+DATA/db10g/controlfile/backup.261.596369361 tag=TAG20060721T100858 recid=3 stamp=596369364

channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:06

including current SPFILE in backupset

channel ORA_DISK_4: starting piece 1 at 21-JUL-06

channel ORA_DISK_4: finished piece 1 at 21-JUL-06

piece handle=+DATA/db10g/backupset/2006_07_21/nnsnf0_tag20060721t100858_0.262.596369369 tag=TAG20060721T100858 comment=NONE

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

output filename=+DATA/db10g/datafile/sysaux.258.596369341 tag=TAG20060721T100858 recid=4 stamp=596369390

channel ORA_DISK_2: datafile copy complete, elapsed time: 00:01:05

output filename=+DATA/db10g/datafile/system.257.596369339 tag=TAG20060721T100858 recid=5 stamp=596369414

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:21

Finished backup at 21-JUL-06



RMAN> SWITCH DATABASE TO COPY;



datafile 1 switched to datafile copy "+DATA/db10g/datafile/system.257.596369339"

datafile 2 switched to datafile copy "+DATA/db10g/datafile/undotbs1.259.596369341"

datafile 3 switched to datafile copy "+DATA/db10g/datafile/sysaux.258.596369341"

datafile 4 switched to datafile copy "+DATA/db10g/datafile/users.260.596369341"



RMAN> alter database open;



database opened



RMAN> exit


Recovery Manager complete.





Migrate tempfile to ASM:



RMAN does not migrate the tempfile as part of the BACKUP AS COPY and SWITCH command becuase the tempfile is not listed in controlfile.
The tempfile has to be manually migrated to ASM.



[oracle@node1-pub oracle]$ sqlplus "/ as sysdba"



SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 10:12:42 2006



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, bytes from v$tempfile;



NAME

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

BYTES

----------

/home/oracle/oradata/db10g/temp01.dbf

20971520



SQL> create temporary tablespace temp1 tempfile SIZE 100M extent management local uniform size 1M;



Tablespace created.



SQL> alter database default temporary tablespace temp1;



Database altered.



SQL> drop tablespace temp including contents;



Tablespace dropped.



SQL> create temporary tablespace temp tempfile SIZE 100M extent management local uniform size 1M;



Tablespace created.



SQL> alter database default temporary tablespace temp;



Database altered.



SQL> drop tablespace temp1 including contents;



Tablespace dropped.



SQL> select name from v$tempfile;



NAME

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

+DATA/db10g/tempfile/temp.264.596370217



Migrate and drop the old Online Redo Logs to ASM:



Run the below procedure to migrate the redo logs to ASM. This program is taken from OTN/metalink.



SQL> declare

cursor orlc is

select lf.member, l.bytes

from v$log l, v$logfile lf

where l.group# = lf.group# and

lf.type = 'ONLINE'

order by l.thread#, l.sequence#;



type numTab_t is table of number index by binary_integer;

type charTab_t is table of varchar2(1024) index by binary_integer;

byteslist numTab_t; namelist charTab_t;



procedure migrateorlfile(name IN varchar2, bytes IN number) is

retry number;

stmt varchar2(1024);

als varchar2(1024) := 'alter system switch logfile';

begin

select count(*) into retry from v$logfile;

stmt := 'alter database add logfile size ' || bytes;

execute immediate stmt;

stmt := 'alter database drop logfile ''' || name || '''';

for i in 1..retry loop

begin execute immediate stmt;

exit;

exception

when others then

if i > retry then raise;

end if;

execute immediate als;

end;

end loop;

end;

begin

open orlc;

fetch orlc bulk collect into namelist, byteslist;

close orlc;

for i in 1..namelist.count loop migrateorlfile(namelist(i), byteslist(i));

end loop;

end;

/

2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40



PL/SQL procedure successfully completed.



SQL> SQL>

SQL>





SQL> select member from v$logfile;



MEMBER

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

+FLASH/db10g/onlinelog/group_3.259.596373299

+FLASH/db10g/onlinelog/group_2.258.596373295

/home/oracle/oradata/db10g/redo01.log

+FLASH/db10g/onlinelog/group_4.257.596373293



SQL> alter system switch logfile;



System altered.



SQL> /



System altered.



SQL> /



System altered.



SQL> /



System altered.



Re-Execute the same script again in order to migrate the remaining ones.



SQL> declare

cursor orlc is

select lf.member, l.bytes

from v$log l, v$logfile lf

where l.group# = lf.group# and

lf.type = 'ONLINE'

order by l.thread#, l.sequence#;



type numTab_t is table of number index by binary_integer;

type charTab_t is table of varchar2(1024) index by binary_integer;

byteslist numTab_t; namelist charTab_t;



procedure migrateorlfile(name IN varchar2, bytes IN number) is

retry number;

stmt varchar2(1024);

als varchar2(1024) := 'alter system switch logfile';

begin

select count(*) into retry from v$logfile;

stmt := 'alter database add logfile size ' || bytes;

execute immediate stmt;

stmt := 'alter database drop logfile ''' || name || '''';

for i in 1..retry loop

begin execute immediate stmt;

exit;

exception

when others then

if i > retry then raise;

end if;

execute immediate als;

end;

end loop;

end;

begin

open orlc;

fetch orlc bulk collect into namelist, byteslist;

close orlc;

for i in 1..namelist.count loop migrateorlfile(namelist(i), byteslist(i));

end loop;

end;

/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40



PL/SQL procedure successfully completed.



SQL> select member from v$logfile;



MEMBER

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

+FLASH/db10g/onlinelog/group_3.259.596373619

+FLASH/db10g/onlinelog/group_2.258.596373615

+FLASH/db10g/onlinelog/group_1.261.596373613

+FLASH/db10g/onlinelog/group_4.257.596373293

+FLASH/db10g/onlinelog/group_5.260.596373609



SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options



DELETE THE OLD DATAFILES USING RMAN.



This way, it will also clear out the datafiles entry from controlfile.



[oracle@node1-pub oracle]$ $ORACLE_HOME/bin/rman



Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jul 21 11:22:33 2006



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



RMAN> connect target



connected to target database: DB10G (DBID=4283639931)



RMAN> run {

2> DELETE COPY OF DATABASE;

3> }



using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=134 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=151 devtype=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: sid=153 devtype=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: sid=138 devtype=DISK



List of Datafile Copies

Key File S Completion Time Ckp SCN Ckp Time Name

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

6 1 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/system01.dbf

7 2 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/undotbs01.dbf

8 3 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/sysaux01.dbf

9 4 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/users01.dbf



Do you really want to delete the above objects (enter YES or NO)? YES

deleted datafile copy

datafile copy filename=/home/oracle/oradata/db10g/system01.dbf recid=6 stamp=596369439

deleted datafile copy

datafile copy filename=/home/oracle/oradata/db10g/undotbs01.dbf recid=7 stamp=596369439

deleted datafile copy

datafile copy filename=/home/oracle/oradata/db10g/sysaux01.dbf recid=8 stamp=596369440

deleted datafile copy

datafile copy filename=/home/oracle/oradata/db10g/users01.dbf recid=9 stamp=596369440

Deleted 4 objects





RMAN> exit





Recovery Manager complete.


REMOVE THE OLD ONLINE REDO LOGS FILES PHYSICALLY:




[oracle@node1-pub oracle]$ rm /home/oracle/oradata/db10g/redo*.log

[oracle@node1-pub oracle]$ sqlplus "/ as sysdba"



SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 11:29:56 2006



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


Enable the block change tracking:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
Database altered.


SQL>


How To Move The Database To Different Diskgroup Migrating from External Redundancy to Normal Redundancy

Solution

Create a new diskgroup with desired redundancy and move the existing data to newly created diskgroup.

1) If we have extra disk space available,then we can create a new diskgroup and move the files from old diskgroup to it.

-- Initially I have 3 diskgroup with external redundancy as:

This blog will move the database in all the diskgroups to one diskgroup


export ORACLE_HOME=/opt/oracle/product/10.2.0.2/asm1

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=+ASM1

SQL> select state,name from v$asm_diskgroup;

STATE NAME

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

MOUNTED DATA1

MOUNTED RMAN1

MOUNTED SYS1

SQL> !ls -ltr /dev/rhdisk*

crw------- 2 root system 14, 1 Feb 5 15:58 /dev/rhdisk1

crw------- 1 root system 14, 0 May 14 13:41 /dev/rhdisk0

crw-r----- 1 root oinstall 14, 2 May 14 17:13 /dev/rhdisk2

crw-r----- 1 root oinstall 14, 7 Jul 11 09:26 /dev/rhdisk7

crw------- 1 oracle dba 14, 9 Jul 11 09:26 /dev/rhdisk9

crw-r--r-- 1 oracle oinstall 14, 8 Jul 11 09:26 /dev/rhdisk8

crw------- 1 oracle dba 14, 14 Jul 11 09:26 /dev/rhdisk14

crw-r--r-- 1 oracle oinstall 14, 13 Jul 11 09:26 /dev/rhdisk13

crw-r----- 1 root oinstall 14, 12 Jul 11 09:26 /dev/rhdisk12

crw------- 1 oracle dba 14, 11 Jul 11 09:26 /dev/rhdisk11

crw------- 1 oracle dba 14, 10 Jul 11 09:26 /dev/rhdisk10

crw------- 1 oracle dba 14, 16 Jul 11 09:26 /dev/rhdisk16

crw------- 1 oracle dba 14, 15 Jul 11 09:26 /dev/rhdisk15

crw------- 1 oracle dba 14, 6 Jul 11 12:05 /dev/rhdisk6

crw------- 1 oracle dba 14, 5 Jul 11 12:06 /dev/rhdisk5

crw------- 1 oracle dba 14, 4 Jul 11 12:06 /dev/rhdisk4

crw-r--r-- 1 oracle oinstall 14, 3 Jul 11 12:06 /dev/rhdisk3

SQL> show PARAMETER ASM_DISKSTRING

NAME TYPE VALUE

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

asm_diskstring string

SQL> !ssh node2 ls -l /dev/rhdisk*

crw------- 2 root system 15, 0 Feb 5 10:23 /dev/rhdisk0

crw------- 1 root system 15, 1 Feb 5 10:47 /dev/rhdisk1

crw------- 1 oracle dba 15, 11 Jul 11 09:33 /dev/rhdisk10

crw------- 1 oracle dba 15, 10 Jul 11 09:35 /dev/rhdisk11

crw-r----- 1 root oinstall 15, 12 Jul 11 09:36 /dev/rhdisk12

crw-r--r-- 1 oracle oinstall 15, 13 Jul 11 09:37 /dev/rhdisk13

crw------- 1 oracle dba 15, 14 Jul 11 09:38 /dev/rhdisk14

crw------- 1 oracle dba 15, 15 Jul 11 09:40 /dev/rhdisk15

crw------- 1 oracle dba 15, 16 Jul 11 09:42 /dev/rhdisk16

crw-r----- 1 root oinstall 15, 2 Jul 11 12:07 /dev/rhdisk2

crw-r--r-- 1 oracle oinstall 15, 3 Jul 11 12:07 /dev/rhdisk3

crw------- 1 oracle dba 15, 4 Jul 11 12:07 /dev/rhdisk4

crw------- 1 oracle dba 15, 5 Jul 11 12:07 /dev/rhdisk5

crw------- 1 oracle dba 15, 6 Jul 11 12:07 /dev/rhdisk6

crw-r----- 1 root oinstall 15, 7 Jul 11 09:30 /dev/rhdisk7

crw-r--r-- 1 oracle oinstall 15, 8 Jul 11 09:31 /dev/rhdisk8

crw------- 1 oracle dba 15, 9 Jul 11 09:32 /dev/rhdisk9
2) Create a new diskgroup with normal redundancy as :


SQL> create diskgroup DATA2 normal redundancy failgroup datafg01 disk '/dev/rhdisk9' failgroup datafg02 disk '/dev/rhdisk14';

Diskgroup created.

SQL> select state,name,type from v$asm_diskgroup;

STATE NAME TYPE

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

MOUNTED DATA1 EXTERN

MOUNTED RMAN1 EXTERN

MOUNTED SYS1 EXTERN

MOUNTED DATA2 NORMAL

export ORACLE_HOME=/opt/oracle/product/10.2.0.2/db1

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=db1
3)Backup the current database as follows:


SQL> show parameter db_name

NAME TYPE VALUE

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

db_name string db

SQL> show parameter control

NAME TYPE VALUE

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

control_file_record_keep_time integer 7

control_files string +SYS1/db/controlfile/current.257.623341273

SQL> alter database backup controlfile to '+DATA2';

Database altered.

SQL> alter system set control_files='+DATA2\db\CONTROLFILE\Backup.256.627662879' SCOPE=SPFILE;

System altered.

-- Connect to rman

home/oracle > rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Wed Jul 11 14:53:59 2007

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

connected to target database: DB (DBID=732666905)

RMAN> shutdown immediate;=== if its RAC shutdown from node 2 also

using target database control file instead of recovery catalog

database closed

database dismounted

Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)

Oracle instance started

Total System Global Area 2063597568 bytes

Fixed Size 2072480 bytes

Variable Size 469762144 bytes

Database Buffers 1577058304 bytes

Redo Buffers 14704640 bytes

RMAN> restore controlfile from '+SYS1\db\CONTROLFILE\Current.257.623341273';

Starting restore at 11-JUL-07

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=145 instance=db1 devtype=DISK

channel ORA_DISK_1: copied control file copy

output filename=+DATA2/db/controlfile/backup.256.627662879

Finished restore at 11-JUL-07

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> backup as copy database format '+DATA2';
4)Switch the database to copy. At this moment we are switching to the new Diskgroup

== A SWITCH is equivalent to using the PL/SQL "alter database rename file" statement.


RMAN> switch database to copy;

RMAN > alter database open;==wil not work
this is a bug in 10.2.0.2

exit out and


sqlplus “/ as sysdba”

alter database open

shutdown immediate;

startup

alter system set db_create_file_dest ='+DATA2';
5)Add new tempfile to newly created database.


SQL> create bigfile temporary tablespace temp03 tempfile size 100M;

Tablespace created.

SQL> alter database default temporary tablespace temp03;

Database altered.

Drop any existing tempfile on the old diskgroup

SQL> drop tablespace temp;

Tablespace dropped.
6)Find out how many members we have have in redolog groups, make sure that we have only one member in each log group.(drop other members).

SQL> select * from v$log;

Suppose we have 4 log groups, then add one member to each log group as following:


SQL> alter database add logfile member '+data2' to group 1;

Database altered.

SQL> alter database add logfile member '+data2' to group 2;

Database altered.

SQL> alter database add logfile member '+data2' to group 3;

Database altered.

SQL> alter database add logfile member '+data2' to group 4;

Database altered.

SQL>
Then we can drop the old logfile member from earlier diskgroups as:

Issue the below command till all the logfiles are in valid state


alter system switch logfile;

SQL> alter database drop logfile member '+SYS1/db/onlinelog/group_2.260.623341307';

Database altered.

SQL> select * from v$logfile;
Logfiles from group 3 and 4 cannot be dropped at this point from node 1 as node 2 is still down

start the database on node 2 and delete the old logfiles in the data1/sys1 diskgroup

7)Use the following query to verify that all the files are moved to new diskgroup with desired redundancy:


SQL> select name from v$controlfile

union

select name from v$datafile

union

select name from v$tempfile

union

select member from v$logfile

union

select filename from v$block_change_tracking
union
select name from v$flashback_database_logfile;

Switching Datafile Filenames After a Restore:

Example

Switching Datafile Filenames After a Restore: Example
This example allocates one disk device and one tape device to allow RMAN to restore from disk and tape.

RUN
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev2 DEVICE TYPE sbt;
SQL "ALTER TABLESPACE tbs_1 OFFLINE IMMEDIATE";
SET NEWNAME FOR DATAFILE '/disk7/oracle/tbs11.f'
TO '/disk9/oracle/tbs11.f';
RESTORE TABLESPACE tbs_1;
SWITCH DATAFILE ALL;
RECOVER TABLESPACE tbs_1;
SQL "ALTER TABLESPACE tbs_1 ONLINE";
}

Migracion de File System +ASM

Cambiar la single instance 11.1.0.6 a ASM con RMAN

Una vez hecha la migración de nuestra base de datos es el momento de realizar el
cambio de tipo de almacenamiento. Actualmente tenemos nuestra base de datos
trabajando en File System y vamos a realizar el paso a Automatic Storage
Management (ASM).

Antes de empezar cabe recordar que la instalación de ASM que hemos realizado en
un paso anterior la hemos hecho en cluster, es decir, cuando cambiemos el
almacenamiento de file system a ASM ya tendremos el almacenamiento preparado
para RAC. No habrá que hacer nuevas configuraciones a este respecto.
Para realizar esta tarea lo primero que debemos hacer es editar el init.ora para
que apunte al nuevo control file que va a tener la base de datos:

control_files =
'+DATA/control01.ctl','+FRA/control02.ctl','+FRA/con
trol03.ctl'

Ahora, debemos rearrancarar la base de datos en modo nomount. Esto lo hacemos
mediante:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount


Tras esto, debemos conectarnos mediante una sesión de RMAN para realizar una
copia de los control files (son 3 archivos) y llevarlos a su nueva localización. Así
pues, nos conectamos a RMAN:
[oracle@nodo1 ~]$ rman
Recovery Manager: Release 11.1.0.6.0 - Production on
Tue Nov 3 17:35:37 2009
Copyright (c) 1982, 2007, Oracle. All rights
reserved.
RMAN> connect target
connected to target database: DBASE10 (not mounted)
Realizamos la copia de los control files ejecutando:
RMAN> RESTORE CONTROLFILE FROM
'/u01/app/oracle/oradata/DBase10/control02.ctl';
Starting restore at 03-NOV-09
using target database control file instead of
recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=147 device type=DISK
channel ORA_DISK_1: copied control file copy
output file
name=/u01/app/oracle/oradata/DBase10/control01.ctl
output file
name=/u01/app/oracle/oradata/DBase10/control02.ctl
output file
name=/u01/app/oracle/oradata/DBase10/control03.ctl
Finished restore

RMAN> RESTORE CONTROLFILE FROM
'/u01/app/oracle/oradata/DBase10/control01.ctl';
Starting restore at 03-NOV-09
using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy
output file
name=/u01/app/oracle/oradata/DBase10/control01.ctl
output file
name=/u01/app/oracle/oradata/DBase10/control02.ctl
output file
name=/u01/app/oracle/oradata/DBase10/control03.ctl
Finished restore

RMAN> RESTORE CONTROLFILE FROM
'/u01/app/oracle/oradata/DBase10/control03.ctl';
Starting restore at 03-NOV-09
using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy
output file
name=/u01/app/oracle/oradata/DBase10/control01.ctl
output file
name=/u01/app/oracle/oradata/DBase10/control02.ctl
output file
name=/u01/app/oracle/oradata/DBase10/control03.ctl
Finished restore


Antes de continuar debemos poner la base de datos de nuevo en modo mount:

SQL> alter database mount;


El siguiente paso es realizar una copia de los datafiles y situarlos en ASM. Esto lo
realizamos mediante la siguiente ejecución en RMAN:
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';
Starting backup at 03-NOV-09
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=147 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001
name=/u01/app/oracle/oradata/DBase10/system01.dbf
output file
name=+DATA/dbase10/datafile/system.256.701977649
tag=TAG20091103T174721 RECID=1 STAMP=701977758
channel ORA_DISK_1: datafile copy complete, elapsed
time: 00:02:10
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002
name=/u01/app/oracle/oradata/DBase10/sysaux01.dbf
output file
name=+DATA/dbase10/datafile/sysaux.257.701977775
tag=TAG20091103T174721 RECID=2 STAMP=701977847
channel ORA_DISK_1: datafile copy complete, elapsed
time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003
name=/u01/app/oracle/oradata/DBase10/undotbs01.dbf
output file
name=+DATA/dbase10/datafile/undotbs1.258.701977851
tag=TAG20091103T174721 RECID=3 STAMP=701977853
channel ORA_DISK_1: datafile copy complete, elapsed
time: 00:00:07
channel ORA_DISK_1: starting datafile copy
copying current control file
output file
name=+DATA/dbase10/controlfile/backup.259.701977857
tag=TAG20091103T174721 RECID=4 STAMP=701977859
channel ORA_DISK_1: datafile copy complete, elapsed
time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004
name=/u01/app/oracle/oradata/DBase10/users01.dbf
output file
name=+DATA/dbase10/datafile/users.260.701977861
tag=TAG20091103T174721 RECID=5 STAMP=701977861
channel ORA_DISK_1: datafile copy complete, elapsed
time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup
set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 03-NOV-09
channel ORA_DISK_1: finished piece 1 at 03-NOV-09
piece
handle=+DATA/dbase10/backupset/2009_11_03/nnsnf0_tag
20091103t174721_0.261.701977863
tag=TAG20091103T174721 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed
time: 00:00:01
Finished backup

Ahora renombramos los datafiles mediante RMAN ejecutando:
RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy
"+DATA/dbase10/datafile/system.256.701977649"
datafile 2 switched to datafile copy
"+DATA/dbase10/datafile/sysaux.257.701977775"
datafile 3 switched to datafile copy
"+DATA/dbase10/datafile/undotbs1.258.701977851"
datafile 4 switched to datafile copy
"+DATA/dbase10/datafile/users.260.701977861"

Una vez hecho esto debemos realizar un switch del tempfile mediante:
RMAN> run{
2> set newname for tempfile 1 to '+DATA';
3> switch tempfile all;
4> }
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file

Tras esta última ejecución abrimos la base de datos y salimos de RMAN:
RMAN> ALTER DATABASE OPEN;
database opened

Tras estas operaciones comprobamos cuáles son ahora los datafiles de la base de
datos:
SQL> select FILE#, STATUS, NAME from v$datafile;
FILE# STATUS
---------- -------
NAME
-----------------------------------------------------
1 SYSTEM
+DATA/dbase11/datafile/system.256.701977649
2 ONLINE
+DATA/dbase11/datafile/sysaux.257.701977775
3 ONLINE
+DATA/dbase11/datafile/undotbs1.258.701977851
4 ONLINE
+DATA/dbase11/datafile/users.260.701977861

El último paso que nos queda por dar para finalizar la migración de File System a
ASM es el cambio de los archivos de redo. Para ello deberemos añadir nuevos redo
logs a la base de datos en ASM para que reemplacen a los actuales, ejecutar un
cambio de redo logs y eliminar los antiguos.

Consultamos los archivos de redo log actuales:
SQL> SELECT a.group#, b.member, a.status FROM v$log
a, v$logfile b WHERE a.group#=b.group#;
GROUP# MEMBER
-----------------------------------------------
STATUS
----------------
3 /u01/app/oracle/oradata/DBase10/redo03.log
INACTIVE
2 /u01/app/oracle/oradata/DBase10/redo02.log
INACTIVE
1 /u01/app/oracle/oradata/DBase10/redo01.log
CURRENT
Como se puede observar, el redo log que está actualmente en uso es el redo01.log,
como indica la consulta mediante su STATUS CURRENT.
Como se puede observar en la consulta mostrada, nuestra base de datos actual tiene
3 grupos de redo con un redo log cada uno.

Ahora añadimos un redo logfile a cada grupo de redo mediante:

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA'TO
GROUP 1;
Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA'TO
GROUP 2;
Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA'TO
GROUP 3;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER
'/u01/app/oracle/oradata/DBase10/redo02.log';


Database altered.
Después de añadir los archivos debemos realizar varios operaciones. Primero,
teniendo en cuenta que el redo log que está actualmente en uso es el redo01.log,
realizamos un switch consiguiendo cambiar al nuevo redo log que hemos creado en
+DATA, en el grupo 2:
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
Esto lo hacemos así ya que redo02.log ya ha sido eliminado y con el switch
cambiamos al archivo que hemos creado antes. De esta forma ya estamos trabajando
con los nuevos logs en ASM.
Tras este cambio podemos eliminar el redo03.log y el redo01.log ya que no están en
uso dejando así en los grupos 1 y 3 un solo archivo de log.
SQL> ALTER DATABASE DROP LOGFILE MEMBER
'/u01/app/oracle/oradata/DBase10/redo03.log';
Database altered.
SQL> ALTER DATABASE DROP LOGFILE MEMBER
'/u01/app/oracle/oradata/DBase10/redo01.log';
Database altered
Ahora, una vez hechas estas operaciones cambiamos de logfile por última vez para
comprobar que funcionan todos correctamente.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
Hacemos una consulta para ver el estado final de los grupos de log:
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES
---------- ---------- ---------- ----------
MEMBERS ARC STATUS
----------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 7 52428800
1 NO ACTIVE
614424 06-NOV-09
2 1 8 52428800
2 NO ACTIVE
614594 06-NOV-09
3 1 9 52428800
2 NO CURRENT
614698 06-NOV-09
Consultamos el nombre y la localización de los nuevos archivos de log y
comprobamos que se han eliminado los antiguos:
SQL> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
-----------------------------------------------------

IS_
---
1 ONLINE
+DATA/dbase10/onlinelog/group_1.263.701979411
NO
2 ONLINE
+DATA/dbase10/onlinelog/group_2.265.701981023
NO
3 ONLINE
+DATA/dbase10/onlinelog/group_3.267.702213275
NO

Con esta última consulta comprobamos que está todo correcto y damos por
finalizada está configuración.

En este momento tenemos un sistema con una base de datos 11.1.0.6 con ASM
funcionando sin problemas.