Troubleshooting daily on Oracle Systems, Linux & more !
English: Oracle Table in a Tablespace (Photo credit: Wikipedia)
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.
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:
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 !
Juan Andres
Your blog is so informative … ..I just bookmarked you….keep up the good work!!!!
LikeLike
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 !
LikeLike
Gracias por la información! y saber que casi 5 años después fue de gran utilidad…
Saludos desde Venezuela
LikeLike
Gracias ! En breve estare publicando articulos de 12c , Team Regards !
LikeLike
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.
LikeLike
This post will assist the internet viewers for building up new weblog
or even a blog from start to end.
LikeLike
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.
LikeLike