English: Oracle Table in a Tablespace
English: Oracle Table in a Tablespace (Photo credit: Wikipedia)

How To Migrating datafiles from FileSystem to ASM instance

La tecnología ASM en ORACLE nos permite a los administradores poder tener el control de los volúmenes de nuestro filesystem.

La semana pasada nos solicitaron reorganizar los filesystem de algunos hosts, en esa movida aprovechamos y propusimos migrar el entorno donde los datafiles se encontraban en filesystem (administrados por un volume group de Linux.) a un entorno ASM.

Vamos a ver de que se trata:

1) Revisamos los procesos de Bases de Datos que esten corriendo.

[oracle@saturno ~]$ ps -ef | grep pmon
oracle   23958     1  0 Jul08 ?        00:00:17 asm_pmon_+ASM
oracle   24062     1  0 Jul08 ?        00:00:19 ora_pmon_RCAT
oracle   12963     1  0 Jul20 ?        00:00:06 ora_pmon_MANU
oracle   18097     1  0 Jul22 ?        00:00:02 ora_pmon_POPA
oracle    2134     1  0 18:50 ?        00:00:00 ora_pmon_MIMZY
oracle    6296  6245  0 19:27 pts/1    00:00:00 grep pmon

2) Setamos las variable de la base a ser migrada.

[oracle@saturno bin]$ . mimzy.sh

3) Nos loguemos en la base y comprobamos los siguientes puntos.

a) Si se encuentra en modo ArchiveLog (Muy Importante este punto).

SQL> archive log list
Database log mode	       No Archive Mode
Automatic archival	       Disabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldesnce     1
database closed.
Database dismounted.

b) En caso de no estar en modo archivelog, lo activamos.

SQL> startup mount
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
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log all
ORA-00271: there are no logs that need archiving
SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence	       3

Consultamos los objetos de la base con algun scritp o armamos un query . (con el script @dba_files_all.sql)

[oracle@saturno MIGRA_ASM]$ ls
dba_files_all.sql
[oracle@saturno MIGRA_ASM]$ sqlplus / as sysdba

Tablespace Name / File Class  Filename								     File Size Auto	       Next		Max
----------------------------- ---------------------------------------------------------------- --------------- ---- --------------- ---------------
EXAMPLE 		      /u02/oradata/MIMZY/example01.dbf					   104,857,600 YES	    655,360  34,359,721,984
SYSAUX			      /u02/oradata/MIMZY/sysaux01.dbf					   241,172,480 YES	 10,485,760  34,359,721,984
SYSTEM			      /u02/oradata/MIMZY/system01.dbf					   503,316,480 YES	 10,485,760  34,359,721,984
TEMP			      /u02/oradata/MIMZY/temp01.dbf					    20,971,520 YES	    655,360  34,359,721,984
UNDOTBS1		      /u02/oradata/MIMZY/undotbs01.dbf					    31,457,280 YES	  5,242,880  34,359,721,984
USERS			      /u02/oradata/MIMZY/users01.dbf					     5,242,880 YES	  1,310,720  34,359,721,984
[ CONTROL FILE	  ]	      /u02/oradata/MIMZY/control01.ctl
[ CONTROL FILE	  ]	      /u02/oradata/MIMZY/control02.ctl
[ CONTROL FILE	  ]	      /u02/oradata/MIMZY/control03.ctl
[ ONLINE REDO LOG ]	      /u02/oradata/MIMZY/redo01.log					    52,428,800
[ ONLINE REDO LOG ]	      /u02/oradata/MIMZY/redo02.log					    52,428,800
[ ONLINE REDO LOG ]	      /u02/oradata/MIMZY/redo03.log					    52,428,800
---------------
sum												 1,064,304,640

12 rows selected.

4) Vamos a cambiar los parámetros para poder recuperar la base de datos en los nuevos discos de ASM generados.

SQL> create pfile='/u01/app/oracle/product/10.2.0/db_gltest2/dbs/initMIMZY.ora' from spfile;

File created.

SQL> sho parameter control

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time	     integer	 7
control_files			     string	 /u02/oradata/MIMZY/control01.c
tl, /u02/oradata/MIMZY/control
02.ctl, /u02/oradata/MIMZY/con
trol03.ctl

SQL> alter system set control_files='+DATA_DG1' scope=spfile;

System altered.

SQL> sho parameter db_create_file_dest

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest		     string
SQL> alter system set db_create_file_dest='+DATA_DG1' scope=spfile;

System altered.

SQL> sho parameter db_recovery_file_dest

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /u02/flash_recovery_area
db_recovery_file_dest_size	     big integer 2G

SQL> alter system set db_recovery_file_dest='+DATA_DG2' scope=spfile;

System altered.

Empezamos con el Proceso de Migración con RMAN.

1) Bajamos la Base de Datos y la dejamos en modo nomount.

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

SQL> 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

2) Ahora nos logueamos a RMAN y vamos a recuperar:

  • Control File.
  • Datafiles.

De esta manera vamos a copiar el ControlFile al Diskgroup de ASM.

Como lo hacemos ?

Pasamos el Path vigente (donde almacenamos nuestro controlfile) de nuestro Filesystem.

[oracle@saturno dbs]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jul 23 20:01:03 2009

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

connected to target database: MIMZY (not mounted)

RMAN> restore controlfile from '/u02/oradata/MIMZY/control01.ctl';

Starting restore at 23-JUL-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DATA_DG1/mimzy/controlfile/backup.270.693000095
Finished restore at 23-JUL-09

Después de este proceso vamos a montar la base.

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

3) Ahora copiasmos los datafiles.

RMAN> backup as copy database format '+DATA_DG1';

Starting backup at 23-JUL-09
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=/u02/oradata/MIMZY/system01.dbf
output filename=+DATA_DG1/mimzy/datafile/system.269.693000221 tag=TAG20090723T200340 recid=2 stamp=693000256
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u02/oradata/MIMZY/sysaux01.dbf
output filename=+DATA_DG1/mimzy/datafile/sysaux.268.693000265 tag=TAG20090723T200340 recid=3 stamp=693000283
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u02/oradata/MIMZY/example01.dbf
output filename=+DATA_DG1/mimzy/datafile/example.267.693000291 tag=TAG20090723T200340 recid=4 stamp=693000295
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u02/oradata/MIMZY/undotbs01.dbf
output filename=+DATA_DG1/mimzy/datafile/undotbs1.260.693000299 tag=TAG20090723T200340 recid=5 stamp=693000302
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u02/oradata/MIMZY/users01.dbf
output filename=+DATA_DG1/mimzy/datafile/users.290.693000305 tag=TAG20090723T200340 recid=6 stamp=693000307
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DATA_DG1/mimzy/controlfile/backup.291.693000309 tag=TAG20090723T200340 recid=7 stamp=693000313
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 23-JUL-09
channel ORA_DISK_1: finished piece 1 at 23-JUL-09
piece handle=+DATA_DG1/mimzy/backupset/2009_07_23/nnsnf0_tag20090723t200340_0.292.693000317 tag=TAG20090723T200340 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 23-JUL-09

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA_DG1/mimzy/datafile/system.269.693000221"
datafile 2 switched to datafile copy "+DATA_DG1/mimzy/datafile/undotbs1.260.693000299"
datafile 3 switched to datafile copy "+DATA_DG1/mimzy/datafile/sysaux.268.693000265"
datafile 4 switched to datafile copy "+DATA_DG1/mimzy/datafile/users.290.693000305"
datafile 5 switched to datafile copy "+DATA_DG1/mimzy/datafile/example.267.693000291"

RMAN> quit

Recovery Manager complete.

4) Verificamos que los procesos se levantaron.

[oracle@saturno bin]$ ps -ef | grep pmon
oracle   23958     1  0 Jul08 ?        00:00:17 asm_pmon_+ASM
oracle   24062     1  0 Jul08 ?        00:00:19 ora_pmon_RCAT
oracle   12963     1  0 Jul20 ?        00:00:06 ora_pmon_MANU
oracle   18097     1  0 Jul22 ?        00:00:02 ora_pmon_POPA
oracle    2134     1  0 18:50 ?        00:00:00 ora_pmon_MIMZY
oracle    7120  6911  0 19:48 pts/2    00:00:00 grep pmon

5) Verificamos donde se encuentra los Datafiles , controlfiles redo & Archivelogs revisando en ASM.

[oracle@saturno bin]$ sqlplus / as sysdba

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 484546 generated at 07/23/2009 20:00:19 needed for thread 1
ORA-00289: suggestion : +DATA_DG2
ORA-00280: change 484546 for thread 1 is in sequence #3

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL ----------- LA PALABRA CANCEL LA ESCRIBIMOS Y LE DAMOS ENTER
Media recovery cancelled.

6) Abrimos la Base de Datos.

SQL> alter database open resetlogs;

Database altered.

7) Verificamos donde se encuentra el datafile temporal.

SQL> col FILE_NAME format a50
SQL> select tablespace_name, file_name, bytes from dba_temp_files;

TABLESPACE_NAME 	       FILE_NAME					       BYTES
------------------------------ -------------------------------------------------- ----------
TEMP			       /u02/oradata/MIMZY/temp01.dbf			    20971520

Borramos el Tablespace Temporal y creamos el mismo dentro de la estructura de ASM.

SQL> alter database tempfile '/u02/oradata/MIMZY/temp01.dbf' drop including datafiles;

Database altered.

SQL> alter tablespace temp add tempfile size 512m autoextend on next 250m maxsize unlimited;

Tablespace altered.

8) Verificamos si esta bien creado el Tablespace Temporal.

SQL> select tablespace_name, file_name, bytes from dba_temp_files;

TABLESPACE_NAME 	       FILE_NAME					       BYTES
------------------------------ -------------------------------------------------- ----------
TEMP			       +DATA_DG1/mimzy/tempfile/temp.293.693001849	   536870912

9) Recreamos los onlinelogs en ASM y verificamos en el mismo paso donde se encuentra.

SQL> col MEMBER format a50
SQL> select a.group#, a.member, b.bytes
from v$logfile a, v$log b where a.group#=b.group#

GROUP# MEMBER						   BYTES
---------- -------------------------------------------------- ----------
3 /u02/oradata/MIMZY/redo03.log			52428800
2 /u02/oradata/MIMZY/redo02.log			52428800
1 /u02/oradata/MIMZY/redo01.log			52428800

SQL> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 UNUSED
2 CURRENT
3 UNUSED

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 ACTIVE
2 ACTIVE
3 CURRENT

SQL> alter system checkpoint global;

System altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 size 250m;

Database altered.

.... SEGUIMOS CON TODOS LOS QUE FALTEN ----

SQL> select a.group#, a.member, b.bytes
2  from v$logfile a, v$log b where a.group# = b.group#;

GROUP# MEMBER						   BYTES
---------- -------------------------------------------------- ----------
3 +DATA_DG1/mimzy/onlinelog/group_3.296.693002939	20971520
2 +DATA_DG1/mimzy/onlinelog/group_2.295.693002857	20971520
1 +DATA_DG1/mimzy/onlinelog/group_1.294.693003127	20971520
1 +DATA_DG2/mimzy/onlinelog/group_1.268.693003129	20971520
2 +DATA_DG2/mimzy/onlinelog/group_2.270.693002861	20971520
3 +DATA_DG2/mimzy/onlinelog/group_3.272.693002943	20971520

6 rows selected.

10 ) Recreamos el  SPFILE para que apunte dentro de ASM.

SQL> CREATE PFILE='$ORACLE_HOME/dbs/initTESTDB.ora' FROM SPFILE='$ORACLE_HOME/dbs/spfileMIMZY.ora';

File created.

SQL> create spfile='+DATA_DG1/MIMZY/spfileMIMZY.ora' from pfile='$ORACLE_HOME/dbs/initTESTDB.ora';

File created.

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> SQL> startup
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
Database mounted.
Database opened.
SQL> quit

11) Verificamos todos los objetos que fueron creados dentro de ASM por nosotros en esta migración.

SQL> @dba_files_all.sql

Tablespace Name / File Class  Filename								     File Size Auto	       Next		Max
----------------------------- ---------------------------------------------------------------- --------------- ---- --------------- ---------------
EXAMPLE 		      +DATA_DG1/mimzy/datafile/example.267.693000291			   104,857,600 YES	    655,360  34,359,721,984
SYSAUX			      +DATA_DG1/mimzy/datafile/sysaux.268.693000265			   241,172,480 YES	 10,485,760  34,359,721,984
SYSTEM			      +DATA_DG1/mimzy/datafile/system.269.693000221			   503,316,480 YES	 10,485,760  34,359,721,984
TEMP			      +DATA_DG1/mimzy/tempfile/temp.293.693001849			   536,870,912 YES	262,144,000  34,359,721,984
UNDOTBS1		      +DATA_DG1/mimzy/datafile/undotbs1.260.693000299			    31,457,280 YES	  5,242,880  34,359,721,984
USERS			      +DATA_DG1/mimzy/datafile/users.290.693000305			     5,242,880 YES	  1,310,720  34,359,721,984
[ CONTROL FILE	  ]	      +DATA_DG1/mimzy/controlfile/backup.270.693000095
[ ONLINE REDO LOG ]	      +DATA_DG1/mimzy/onlinelog/group_1.294.693003127			    20,971,520
[ ONLINE REDO LOG ]	      +DATA_DG1/mimzy/onlinelog/group_2.295.693002857			    20,971,520
[ ONLINE REDO LOG ]	      +DATA_DG1/mimzy/onlinelog/group_3.296.693002939			    20,971,520
[ ONLINE REDO LOG ]	      +DATA_DG2/mimzy/onlinelog/group_1.268.693003129			    20,971,520
[ ONLINE REDO LOG ]	      +DATA_DG2/mimzy/onlinelog/group_2.270.693002861			    20,971,520
[ ONLINE REDO LOG ]	      +DATA_DG2/mimzy/onlinelog/group_3.272.693002943			    20,971,520
---------------
sum												 1,548,746,75213 rows selected.SQL>

Espero les sea de Utilidad !

Publicado por Juan Andres Mercado

Big Data Enthusiast, DBA & Exadata Manager | Consultant | Father | Musician | Farmer | Environmentalist Argentina | juanmercadoit.com

7 respuestas a “Oracle ASM | Migrating datafiles from FileSystem to ASM instance”

    1. Thanks my friend, i actualize the blog in this days, i working in a big proyect and i dont have a time, but your words are gratified !

      Me gusta

  3. You’ll be able to learn a whole lot about a organization by simply checking to
    see if they’re living up to common guidelines for instance a business
    license. That is why you should choose a specialist in
    the type of roofing you want. In cases like this we are the one you are interested
    in.

    Me gusta

  5. If possibly you paint your head’s hair, you may obtain a cost-effective hair painting for any couple of bucks, its surely not high-priced in any way,
    then you may really start off searching great! .
    For those who generally have the very hair coloring then thats a plus plus a dilemma
    a smaller amount to worry about. Later on you
    will aspiration to search for the essential naruto costume,
    resulting from any additional curly hair which
    it is actually have all around the neck, you can get
    a thing form a sibling or possibly a cosing or most likely some lover it becomes clear that appearance
    to have purchased a sweater or some issue similar to that
    which offers the different curly hair aspect, then this only dilemma remaining could possibly be to position several orange dress dress yourself
    in more than that to be certain the hairs go above the top rated in this orange shirts or dresses making it seem to be and incredibly believe that this
    have been a single.

    Me gusta

Los comentarios están cerrados.