Migrando Banco de Dados Oracle Single para ASM non-RAC

março 6, 2012 - Alta disponibilidade, Backup, Instalação, Linux, Migração, Oracle Database - Tagged: , , , , , , , , , , , , , , , , , , , , , , , , , , , - no comments

https://boxfanexpo.com/om8du1dh53 Este artigo tem como objetivo mostrar como migrar o Banco de Dados Oracle “Single” para “ASM” porém não para RAC.
Vamos utilizar a versão 10.2.0.5 do Banco de Dados Oracle para realizar a migração.

https://www.thephysicaltherapyadvisor.com/2024/09/18/cw37n2n Para iniciarmos devemos estender os discos colocados que será os discos do ASM (Automatic Storage Management).

https://traffordhistory.org/lookingback/jbttbuzwah
Vamos listar os discos disponíveis no servidor de Banco de Dados.
OBS: Todos os procedimentos a seguir são realizado com o usuário “root”.

https://technocretetrading.com/qhbxjg3vq2 [root@serveroracle ~]# ls -l /dev/sd* brw-r----- 1 root disk 8, 0 Feb 21 19:37 /dev/sda brw-r----- 1 root disk 8, 1 Feb 21 19:39 /dev/sda1 brw-r----- 1 root disk 8, 2 Feb 21 19:39 /dev/sda2 brw-r----- 1 root disk 8, 3 Feb 21 19:39 /dev/sda3 brw-r----- 1 root disk 8, 4 Feb 21 19:37 /dev/sda4 brw-r----- 1 root disk 8, 5 Feb 21 19:39 /dev/sda5 brw-r----- 1 root disk 8, 6 Feb 21 19:39 /dev/sda6 brw-r----- 1 root disk 8, 7 Feb 21 19:37 /dev/sda7 brw-r----- 1 root disk 8, 8 Feb 21 19:39 /dev/sda8 brw-r----- 1 root disk 8, 16 Feb 21 19:37 /dev/sdb brw-r----- 1 root disk 8, 32 Feb 21 19:37 /dev/sdc brw-r----- 1 root disk 8, 48 Feb 21 19:37 /dev/sdd brw-r----- 1 root disk 8, 64 Feb 21 19:37 /dev/sde brw-r----- 1 root disk 8, 80 Feb 21 19:37 /dev/sdf brw-r----- 1 root disk 8, 96 Feb 21 19:37 /dev/sdg brw-r----- 1 root disk 8, 112 Feb 21 19:37 /dev/sdh brw-r----- 1 root disk 8, 128 Feb 21 19:37 /dev/sdi [root@serveroracle ~]#

https://ragadamed.com.br/2024/09/18/6x7xrfuzlup Vamos utilizar os discos para o ASM o “sdb”,”sdc”,”sdd”,”sde”,”sdf”,”sdg”,”sdh” e “sdi”.
Vamos formatar os discos e criar as partições no mesmo.

https://www.thoughtleaderlife.com/ow7osmvwu [root@serveroracle ~]# fdisk /dev/sdb Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel Building a new DOS disklabel. Changes will remain in memory only, until you decide to write them. After that, of course, the previous content won't be recoverable. Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite) Command (m for help): n Command action e extended p primary partition (1-4) p Partition number (1-4): 1 First cylinder (1-522, default 1): Using default value 1 Last cylinder or +size or +sizeM or +sizeK (1-522, default 522): Using default value 522 Command (m for help): w The partition table has been altered! Calling ioctl() to re-read partition table. Syncing disks. [root@serveroracle ~]# fdisk /dev/sdc Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel Building a new DOS disklabel. Changes will remain in memory only, until you decide to write them. After that, of course, the previous content won't be recoverable. Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite) Command (m for help): n Command action e extended p primary partition (1-4) p Partition number (1-4): 1 First cylinder (1-522, default 1): Using default value 1 Last cylinder or +size or +sizeM or +sizeK (1-522, default 522): Using default value 522 Command (m for help): w The partition table has been altered! Calling ioctl() to re-read partition table. Syncing disks. [root@serveroracle ~]# fdisk /dev/sdd Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel Building a new DOS disklabel. Changes will remain in memory only, until you decide to write them. After that, of course, the previous content won't be recoverable. Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite) Command (m for help): n Command action e extended p primary partition (1-4) p Partition number (1-4): 1 First cylinder (1-261, default 1): Using default value 1 Last cylinder or +size or +sizeM or +sizeK (1-261, default 261): Using default value 261 Command (m for help): w The partition table has been altered! Calling ioctl() to re-read partition table. Syncing disks. [root@serveroracle ~]# fdisk /dev/sde Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel Building a new DOS disklabel. Changes will remain in memory only, until you decide to write them. After that, of course, the previous content won't be recoverable. Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite) Command (m for help): n Command action e extended p primary partition (1-4) p Partition number (1-4): 1 First cylinder (1-261, default 1): Using default value 1 Last cylinder or +size or +sizeM or +sizeK (1-261, default 261): Using default value 261 Command (m for help): w The partition table has been altered! Calling ioctl() to re-read partition table. Syncing disks. [root@serveroracle ~]# fdisk /dev/sdf Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel Building a new DOS disklabel. Changes will remain in memory only, until you decide to write them. After that, of course, the previous content won't be recoverable. Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite) Command (m for help): n Command action e extended p primary partition (1-4) p Partition number (1-4): 1 First cylinder (1-65, default 1): Using default value 1 Last cylinder or +size or +sizeM or +sizeK (1-65, default 65): Using default value 65 Command (m for help): w The partition table has been altered! Calling ioctl() to re-read partition table. Syncing disks. [root@serveroracle ~]# fdisk /dev/sdg Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel Building a new DOS disklabel. Changes will remain in memory only, until you decide to write them. After that, of course, the previous content won't be recoverable. Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite) Command (m for help): n Command action e extended p primary partition (1-4) p Partition number (1-4): 1 First cylinder (1-65, default 1): Using default value 1 Last cylinder or +size or +sizeM or +sizeK (1-65, default 65): Using default value 65 Command (m for help): w The partition table has been altered! Calling ioctl() to re-read partition table. Syncing disks. [root@serveroracle ~]# fdisk /dev/sdh Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel Building a new DOS disklabel. Changes will remain in memory only, until you decide to write them. After that, of course, the previous content won't be recoverable. Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite) Command (m for help): n Command action e extended p primary partition (1-4) p Partition number (1-4): 1 First cylinder (1-65, default 1): Using default value 1 Last cylinder or +size or +sizeM or +sizeK (1-65, default 65): Using default value 65 Command (m for help): w The partition table has been altered! Calling ioctl() to re-read partition table. Syncing disks. [root@serveroracle ~]# fdisk /dev/sdi Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel Building a new DOS disklabel. Changes will remain in memory only, until you decide to write them. After that, of course, the previous content won't be recoverable. Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite) Command (m for help): n Command action e extended p primary partition (1-4) p Partition number (1-4): 1 First cylinder (1-65, default 1): Using default value 1 Last cylinder or +size or +sizeM or +sizeK (1-65, default 65): Using default value 65 Command (m for help): w The partition table has been altered! Calling ioctl() to re-read partition table. Syncing disks. [root@serveroracle ~]#

https://semnul.com/creative-mathematics/?p=a6a0xyze0 Vamos verificar os discos após a criação das partições dos mesmos.

https://www.modulocapital.com.br/126vkrvw [root@serveroracle ~]# ls -l /dev/sd* brw-r----- 1 root disk 8, 0 Feb 21 19:37 /dev/sda brw-r----- 1 root disk 8, 1 Feb 21 19:39 /dev/sda1 brw-r----- 1 root disk 8, 2 Feb 21 19:39 /dev/sda2 brw-r----- 1 root disk 8, 3 Feb 21 19:39 /dev/sda3 brw-r----- 1 root disk 8, 4 Feb 21 19:37 /dev/sda4 brw-r----- 1 root disk 8, 5 Feb 21 19:39 /dev/sda5 brw-r----- 1 root disk 8, 6 Feb 21 19:39 /dev/sda6 brw-r----- 1 root disk 8, 7 Feb 21 19:37 /dev/sda7 brw-r----- 1 root disk 8, 8 Feb 21 19:39 /dev/sda8 brw-r----- 1 root disk 8, 16 Feb 21 19:42 /dev/sdb brw-r----- 1 root disk 8, 17 Feb 21 19:42 /dev/sdb1 brw-r----- 1 root disk 8, 32 Feb 21 19:42 /dev/sdc brw-r----- 1 root disk 8, 33 Feb 21 19:43 /dev/sdc1 brw-r----- 1 root disk 8, 48 Feb 21 19:43 /dev/sdd brw-r----- 1 root disk 8, 49 Feb 21 19:43 /dev/sdd1 brw-r----- 1 root disk 8, 64 Feb 21 19:43 /dev/sde brw-r----- 1 root disk 8, 65 Feb 21 19:43 /dev/sde1 brw-r----- 1 root disk 8, 80 Feb 21 19:43 /dev/sdf brw-r----- 1 root disk 8, 81 Feb 21 19:43 /dev/sdf1 brw-r----- 1 root disk 8, 96 Feb 21 19:43 /dev/sdg brw-r----- 1 root disk 8, 97 Feb 21 19:43 /dev/sdg1 brw-r----- 1 root disk 8, 112 Feb 21 19:44 /dev/sdh brw-r----- 1 root disk 8, 113 Feb 21 19:44 /dev/sdh1 brw-r----- 1 root disk 8, 128 Feb 21 19:44 /dev/sdi brw-r----- 1 root disk 8, 129 Feb 21 19:44 /dev/sdi1 [root@serveroracle ~]#

follow link Vamos agora configurar as “RAWs” (rawdevices) para os discos récem-particionados para configuração/ instalação do ASM.
Vamos utilizar a configuração atráves do “UDEV”. Sobre udev acesse Informações “udev”.
Vamos acessar o diretório e visualizar o arquivo para a configuração das raws.

Buy Valium Cheap Uk [root@serveroracle ~]# cd /etc/udev/rules.d/ [root@serveroracle rules.d]# ls -ltr 60-raw.rules -rw-r--r-- 1 root root 593 Feb 21 16:48 60-raw.rules [root@serveroracle rules.d]#

https://luisfernandocastro.com/p37fgo0s1d No arquivo 60-raw.rules iremos configurar as raws para os discos listados no começo do artigo.
Vamos acrescentar no arquivo as seguintes linhas.

go to site ACTION=="add", KERNEL=="/dev/sdb1", RUN+="/bin/raw /dev/raw/raw1 %N" ACTION=="add", KERNEL=="/dev/sdc1", RUN+="/bin/raw /dev/raw/raw2 %N" ACTION=="add", KERNEL=="/dev/sdd1", RUN+="/bin/raw /dev/raw/raw3 %N" ACTION=="add", KERNEL=="/dev/sde1", RUN+="/bin/raw /dev/raw/raw4 %N" ACTION=="add", KERNEL=="/dev/sdf1", RUN+="/bin/raw /dev/raw/raw5 %N" ACTION=="add", KERNEL=="/dev/sdg1", RUN+="/bin/raw /dev/raw/raw6 %N" ACTION=="add", KERNEL=="/dev/sdh1", RUN+="/bin/raw /dev/raw/raw7 %N" ACTION=="add", KERNEL=="/dev/sdi1", RUN+="/bin/raw /dev/raw/raw8 %N"

go Segue o mesmo abaixo.

go [root@serveroracle rules.d]# vi 60-raw.rules [root@serveroracle rules.d]# cat 60-raw.rules # Enter raw device bindings here. # # An example would be: # ACTION=="add", KERNEL=="sda", RUN+="/bin/raw /dev/raw/raw1 %N" # to bind /dev/raw/raw1 to /dev/sda, or # ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="1", RUN+="/bin/raw /dev/raw/raw2 %M %m" # to bind /dev/raw/raw2 to the device with major 8, minor 1. ACTION=="add", KERNEL=="/dev/sdb1", RUN+="/bin/raw /dev/raw/raw1 %N" ACTION=="add", KERNEL=="/dev/sdc1", RUN+="/bin/raw /dev/raw/raw2 %N" ACTION=="add", KERNEL=="/dev/sdd1", RUN+="/bin/raw /dev/raw/raw3 %N" ACTION=="add", KERNEL=="/dev/sde1", RUN+="/bin/raw /dev/raw/raw4 %N" ACTION=="add", KERNEL=="/dev/sdf1", RUN+="/bin/raw /dev/raw/raw5 %N" ACTION=="add", KERNEL=="/dev/sdg1", RUN+="/bin/raw /dev/raw/raw6 %N" ACTION=="add", KERNEL=="/dev/sdh1", RUN+="/bin/raw /dev/raw/raw7 %N" ACTION=="add", KERNEL=="/dev/sdi1", RUN+="/bin/raw /dev/raw/raw8 %N" [root@serveroracle rules.d]#

Buy Valium From India Explicação:
ACTION==”add” (Adiciona o Dispositivo no Script).
KERNEL==”/dev/sdb1″ (Dispositivo a ser adicionado).
RUN+=”/bin/raw /dev/raw/raw1 %N (Aonde será executado).
ACTION==”add”, KERNEL==”/dev/sdb1″, RUN+=”/bin/raw /dev/raw/raw1 %N” (Chama udev que quando é detectado o drive é adicionado, executa o script especificado).

Order Valium From Canada Vamos iniciar as raws nos seus específicos dispositivos.

https://luisfernandocastro.com/9p4ydkm0t3n [root@serveroracle rules.d]# /bin/raw /dev/raw/raw1 /dev/sdb1 /dev/raw/raw1: bound to major 8, minor 17 [root@serveroracle rules.d]# /bin/raw /dev/raw/raw2 /dev/sdc1 /dev/raw/raw2: bound to major 8, minor 33 [root@serveroracle rules.d]# /bin/raw /dev/raw/raw3 /dev/sdd1 /dev/raw/raw3: bound to major 8, minor 49 [root@serveroracle rules.d]# /bin/raw /dev/raw/raw4 /dev/sde1 /dev/raw/raw4: bound to major 8, minor 65 [root@serveroracle rules.d]# /bin/raw /dev/raw/raw5 /dev/sdf1 /dev/raw/raw5: bound to major 8, minor 81 [root@serveroracle rules.d]# /bin/raw /dev/raw/raw6 /dev/sdg1 /dev/raw/raw6: bound to major 8, minor 97 [root@serveroracle rules.d]# /bin/raw /dev/raw/raw7 /dev/sdh1 /dev/raw/raw7: bound to major 8, minor 113 [root@serveroracle rules.d]# /bin/raw /dev/raw/raw8 /dev/sdi1 /dev/raw/raw8: bound to major 8, minor 129 [root@serveroracle rules.d]#

Cheap Valium Online Uk Vamos configurar o arquivo rawdevices do sistema para o reconhecimentos dos dispositivos.
No arquivo “rawdevices” adicione as seguintes linhas.

https://www.drcarolineedwards.com/2024/09/18/myvp8as561 /dev/raw/raw1 /dev/sdb1 /dev/raw/raw2 /dev/sdc1 /dev/raw/raw3 /dev/sdd1 /dev/raw/raw4 /dev/sde1 /dev/raw/raw5 /dev/sdf1 /dev/raw/raw6 /dev/sdg1 /dev/raw/raw7 /dev/sdh1 /dev/raw/raw8 /dev/sdi1

source link Vamos configurar o arquivo “rawdevices”.

follow site [root@serveroracle rules.d]# vi /etc/sysconfig/rawdevices [root@serveroracle rules.d]# cat /etc/sysconfig/rawdevices # raw device bindings # format: <rawdev> <major> <minor> # <rawdev> <blockdev> # example: /dev/raw/raw1 /dev/sda1 # /dev/raw/raw2 8 5 /dev/raw/raw1 /dev/sdb1 /dev/raw/raw2 /dev/sdc1 /dev/raw/raw3 /dev/sdd1 /dev/raw/raw4 /dev/sde1 /dev/raw/raw5 /dev/sdf1 /dev/raw/raw6 /dev/sdg1 /dev/raw/raw7 /dev/sdh1 /dev/raw/raw8 /dev/sdi1 [root@serveroracle rules.d]#

go here Vamos reiniciar o serviço do rawdevices.

https://semnul.com/creative-mathematics/?p=dibvifu5pyb [root@serveroracle rules.d]# /sbin/service rawdevices restart Assigning devices: /dev/raw/raw1 --> /dev/sdb1 /dev/raw/raw1: bound to major 8, minor 17 /dev/raw/raw2 --> /dev/sdc1 /dev/raw/raw2: bound to major 8, minor 33 /dev/raw/raw3 --> /dev/sdd1 /dev/raw/raw3: bound to major 8, minor 49 /dev/raw/raw4 --> /dev/sde1 /dev/raw/raw4: bound to major 8, minor 65 /dev/raw/raw5 --> /dev/sdf1 /dev/raw/raw5: bound to major 8, minor 81 /dev/raw/raw6 --> /dev/sdg1 /dev/raw/raw6: bound to major 8, minor 97 /dev/raw/raw7 --> /dev/sdh1 /dev/raw/raw7: bound to major 8, minor 113 /dev/raw/raw8 --> /dev/sdi1 /dev/raw/raw8: bound to major 8, minor 129 done [root@serveroracle rules.d]#

https://boxfanexpo.com/13gdvwnf Após a reinicialização do serviço “rawdevices”, vamos privilegiar o usuário “oracle” do grupo “dba” nas rawdevices de 1 a 8 conforme abaixo.

https://ragadamed.com.br/2024/09/18/u40xz3v9bus [root@serveroracle rules.d]# chown -R oracle.dba /dev/raw/raw[1-8] [root@serveroracle rules.d]# chmod 660 /dev/raw/raw[1-8] [root@serveroracle rules.d]# ls -ltr /dev/raw/raw* crw------- 1 oracle dba 162, 1 Feb 21 19:48 /dev/raw/raw1 crw------- 1 oracle dba 162, 2 Feb 21 19:48 /dev/raw/raw2 crw------- 1 oracle dba 162, 3 Feb 21 19:48 /dev/raw/raw3 crw------- 1 oracle dba 162, 4 Feb 21 19:48 /dev/raw/raw4 crw------- 1 oracle dba 162, 5 Feb 21 19:48 /dev/raw/raw5 crw------- 1 oracle dba 162, 6 Feb 21 19:48 /dev/raw/raw6 crw------- 1 oracle dba 162, 7 Feb 21 19:48 /dev/raw/raw7 crw------- 1 oracle dba 162, 8 Feb 21 19:48 /dev/raw/raw8 [root@serveroracle rules.d]#

https://www.thephysicaltherapyadvisor.com/2024/09/18/cw37n2n Agora precisamos privilegiar o usuário “oracle” para os dispositivos, para que na próxima reinicialização do Sistema Operacional o usuário “oracle” seja possível subir a instância “ASM”.

https://vbmotorworld.com/q3k5yxp1l Exite duas maneiras:

go site 1 – Podemos conforme abaixo no arquivo “rc.local” garantir privilégios nos dispositivos butos (RAW) para o usuário “oracle” do grupo “dba”;
2 – Criar o arquivo “99-raw-perms.rules” dentro do diretório “/etc/udev/rules.d/” e garantir privilégios nos dispositivos butos (RAW) para o usuário “oracle” do grupo “dba”;.

https://everitte.org/c91dczpxh4f Vou mostrar das duas formas.

https://technocretetrading.com/r97yqea8 No arquivo “rc.local”.

https://www.thoughtleaderlife.com/n055yq7kd chown -R oracle.dba /dev/raw/raw[1-8] chmod 660 /dev/raw/raw[1-8]

https://www.modulocapital.com.br/urtk09md Vamos acrescentar no arquivo as linhas acima.

[root@serveroracle scripts]# cd /etc/ [root@serveroracle etc]# cat rc.local #!/bin/sh # # This script will be executed *after* all the other init scripts. # You can put your own initialization stuff in here if you don't # want to do the full Sys V style init stuff. touch /var/lock/subsys/local [root@serveroracle etc]# vi rc.local [root@serveroracle etc]# cat rc.local #!/bin/sh # # This script will be executed *after* all the other init scripts. # You can put your own initialization stuff in here if you don't # want to do the full Sys V style init stuff. touch /var/lock/subsys/local chown -R oracle.dba /dev/raw/raw[1-8] chmod 660 /dev/raw/raw[1-8] [root@serveroracle etc]#

No arquivo “99-raw-perms.rules”.

KERNEL=="raw[1-8]", MODE="0660", GROUP="dba", OWNER="oracle"

Vamos acrescentar no arquivo as linhas acima.

[root@serveroracle rules.d]# vi 99-raw-perms.rules
[root@serveroracle rules.d]# cat 99-raw-perms.rules
KERNEL=="raw[1-8]", MODE="0660", GROUP="dba", OWNER="oracle"
[root@serveroracle rules.d]#

Pronto, preparamos os dispositivos e vamos criar a instância ASM.
Com o usuário “oracle” vamos verificar o ambiente de banco de dados.

[root@serveroracle ~]# su - oracle
[oracle@serveroracle ~]$ ps -ef | grep pmon
oracle    4227     1  0 19:51 ?        00:00:00 ora_pmon_dbprod
oracle    4308  4185  0 19:52 pts/2    00:00:00 grep pmon
[oracle@serveroracle ~]$

Conforme informações acima, o nosso Banco de Dados Oracle “dbprod” está executando. Vamos conferir algumas informações no mesmo para realizarmos a migração do ambiente SINGLE para ASM.
Verificando o Listener.

[oracle@serveroracle ~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 21-FEB-2012 19:52:42

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.104)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date                21-FEB-2012 19:51:15
Uptime                    0 days 0 hr. 1 min. 27 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oraprd01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /oraprd01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.104)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dbprod" has 2 instance(s).
  Instance "dbprod", status UNKNOWN, has 1 handler(s) for this service...
  Instance "dbprod", status READY, has 1 handler(s) for this service...
Service "dbprodXDB" has 1 instance(s).
  Instance "dbprod", status READY, has 1 handler(s) for this service...
Service "dbprod_XPT" has 1 instance(s).
  Instance "dbprod", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@serveroracle ~]$

Vamos criar a instância ASM. (Veja as Imagens na Galeria para a Criação da Instância ASM).
Com o DBCA vamos criar a instância ASM e após a criação vamos visualizar se as instâncias estão executando.

[oracle@serveroracle ~]$ dbca

Num outro terminal vamos executar com o usuário “root” durante a criação da instância “+ASM” o comando abaixo para criar o OCR.

[root@serveroracle ~]# /oraprd01/app/oracle/product/10.2.0/db_1/bin/localconfig add
/etc/oracle does not exist. Creating it now.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Configuration for local CSS has been initialized

Adding to inittab
Startup will be queued to init within 30 seconds.
Checking the status of new Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
        serveroracle
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)
[root@serveroracle ~]#

Após criação da instância “+ASM” vamos visualizar os processos para verificar se os mesmos executando.

[oracle@serveroracle ~]$ ps -ef | grep pmon
oracle    4227     1  0 19:51 ?        00:00:00 ora_pmon_dbprod
oracle    4387     1  0 19:57 ?        00:00:00 asm_pmon_+ASM
oracle    4490  4185  0 20:06 pts/2    00:00:00 grep pmon
[oracle@serveroracle ~]$

Após criado a Instância ASM, podemos perceber que as duas instâncias “dbprod” e “+ASM” estão executando no servidor de Banco de Dados Oracle.
Vamos verificar o status do Enterprise Manager.

[oracle@serveroracle ~]$ emctl status dbconsole
TZ set to Brazil/East
Oracle Enterprise Manager 10g Database Control Release 10.2.0.5.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
https://serveroracle.localdomain:5500/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory /oraprd01/app/oracle/product/10.2.0/db_1/serveroracle.localdomain_dbprod/sysman/log
[oracle@serveroracle ~]$ echo $ORACLE_SID
dbprod
[oracle@serveroracle ~]$

Como podemos perceber o EM (Enterprise Manager) está executando.
Vamos conectar no Banco de Dados “dbprod” e verificar informações para a migração.
OBS: Todas as informações vistas abaixo são importantes para a migração do ambiente.

[oracle@serveroracle ~]$ sqlplus sys/******* as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 21 20:21:55 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Release 10.2.0.5.0 - Production

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
dbprod           OPEN

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /oraprd02/oradata/dbprod/contr
                                                 ol01.ctl, /oraprd02/oradata/db
                                                 prod/control02.ctl, /oraprd02/
                                                 oradata/dbprod/control03.ctl
SQL> col member for a45
SQL> select member from v$logfile;

MEMBER
---------------------------------------------
/oraprd02/oradata/dbprod/redo003.log
/oraprd02/oradata/dbprod/redo002.log
/oraprd02/oradata/dbprod/redo001.log

SQL> col name for a45
SQL> select name from v$datafile;

NAME
---------------------------------------------
/oraprd02/oradata/dbprod/system01.dbf
/oraprd02/oradata/dbprod/undotbs01.dbf
/oraprd02/oradata/dbprod/sysaux01.dbf
/oraprd02/oradata/dbprod/users01.dbf
/oraprd02/oradata/dbprod/users02.dbf
/oraprd01/oradata/dbprod/users03.dbf
/oraprd02/oradata/dbprod/master.dbf

7 rows selected.

SQL> show parameter db_create_file_dest                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string
SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> show parameter log_archive_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string      /orabackup/dbprod/oraarchive
.
.
log_archive_dest_state_9             string      enable
SQL>

Obtivemos as informações dos arquivos para a migração do ambiente single para o ASM.
Obtivemos informações de:
– controlfiles;
– logfiles;
– datafiles;
– archivelogs;

Vamos iniciar a configuração do Banco de Dados para a Migração.

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

System altered.

SQL> alter system set db_create_file_dest='+DGDADOS' scope=spfile;

System altered.

SQL> !

Ajustando o parâmetro “db_create_file_dest” especifica a localização default dos arquivos gerenciados pelo Oracle.
Mais informações acesse DB_CREATE_FILE_DEST.
Com o RMAN vamos iniciar a migração para ASM.
Vamos realizar o backup do controlfile corrente.

[oracle@serveroracle ~]$ rman target sys/*******

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Feb 21 20:36:54 2012

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

connected to target database: DBPROD (DBID=862715809)

RMAN> backup current controlfile format '/oraprd01/app/oracle/control.ctl';

Starting backup at 21-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=133 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 21-FEB-12
channel ORA_DISK_1: finished piece 1 at 21-FEB-12
piece handle=/oraprd01/app/oracle/control.ctl tag=TAG20120221T203833 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 21-FEB-12

Starting Control File and SPFILE Autobackup at 21-FEB-12
piece handle=/orabackup/dbprod/fisico/c-862715809-20120221-13 comment=NONE
Finished Control File and SPFILE Autobackup at 21-FEB-12

RMAN> exit


Recovery Manager complete.
[oracle@serveroracle ~]$ exit
exit

Vamos desligar o nosso Banco de Dados e iniciá-lo em estado “nomount”.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  1277824 bytes
Variable Size             541065344 bytes
Database Buffers          524288000 bytes
Redo Buffers                7110656 bytes
SQL> !

Com o RMAN vamos restaurar o backup do corrente controlfile para dentro do Disco de Grupo “+DGDADOS” conforme abaixo.

[oracle@serveroracle ~]$ rman target sys/*********

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Feb 21 20:43:22 2012

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

connected to target database: dbprod (not mounted)

RMAN> restore controlfile from '/oraprd01/app/oracle/control.ctl';

Starting restore at 21-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
output filename=+DGDADOS/dbprod/controlfile/current.256.776451519
Finished restore at 21-FEB-12

RMAN>

Podemos perceber acima que o controlfile foi restaurado para o disco de grupo “+DGDADOS”.
Nome do arquivo gerado “+DGDADOS/dbprod/controlfile/current.256.776451519”.

Vamos montar o Banco de Dados.

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN>

Após o Banco de Dados montado, vamos realizar o Backup do mesmo com formato para o Grupo de Disco “+DGDADOS”.

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

Starting backup at 21-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/oraprd02/oradata/dbprod/undotbs01.dbf
output filename=+DGDADOS/dbprod/datafile/undotbs1.257.775861731 tag=TAG20120221T210848 recid=28 stamp=775861817
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/oraprd02/oradata/dbprod/system01.dbf
output filename=+DGDADOS/dbprod/datafile/system.258.775861825 tag=TAG20120221T210848 recid=29 stamp=775861872
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/oraprd02/oradata/dbprod/sysaux01.dbf
output filename=+DGDADOS/dbprod/datafile/sysaux.259.775861881 tag=TAG20120221T210848 recid=30 stamp=775861909
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/oraprd02/oradata/dbprod/users02.dbf
output filename=+DGDADOS/dbprod/datafile/users.260.775861915 tag=TAG20120221T210848 recid=31 stamp=775861936
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00009 name=/oraprd02/oradata/dbprod/master.dbf
output filename=+DGDADOS/dbprod/datafile/master.261.775861941 tag=TAG20120221T210848 recid=32 stamp=775861958
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/oraprd02/oradata/dbprod/users01.dbf
output filename=+DGDADOS/dbprod/datafile/users.262.775861967 tag=TAG20120221T210848 recid=33 stamp=775861979
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/oraprd01/oradata/dbprod/users03.dbf
output filename=+DGDADOS/dbprod/datafile/users.263.775861983 tag=TAG20120221T210848 recid=34 stamp=775861994
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 21-FEB-12

RMAN-06497: WARNING: control file is not current, control file autobackup skipped

O erro acima é de apenas aviso.
Após a finalização do Backup vamos verificar os arquivos (datafiles e tempfiles) do Banco de Dados.

RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    442      SYSTEM               ***     /oraprd02/oradata/dbprod/system01.dbf
2    759      UNDOTBS1             ***     /oraprd02/oradata/dbprod/undotbs01.dbf
3    255      SYSAUX               ***     /oraprd02/oradata/dbprod/sysaux01.dbf
4    104      USERS                ***     /oraprd02/oradata/dbprod/users01.dbf
5    202      USERS                ***     /oraprd02/oradata/dbprod/users02.dbf
6    102      USERS                ***     /oraprd01/oradata/dbprod/users03.dbf
9    160      MASTER               ***     /oraprd02/oradata/dbprod/master.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    2048     TEMP                 2048        /oraprd02/oradata/dbprod/temp06.dbf
2    3072     TEMP                 3072        /oraprd02/oradata/dbprod/temp05.dbf

RMAN>

Conforme acima os arquivos do Banco de Dados ainda estão aparecendo no seus caminhos antigos, isso ocorre pois a copia do catálogo do RMAN não foi atualizado no controlfile ainda.
Com o comando “SWITCH” vamos atualizar o mesmo e verificar novamente os arquivos.

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DGDADOS/dbprod/datafile/system.258.775861825"
datafile 2 switched to datafile copy "+DGDADOS/dbprod/datafile/undotbs1.257.775861731"
datafile 3 switched to datafile copy "+DGDADOS/dbprod/datafile/sysaux.259.775861881"
datafile 4 switched to datafile copy "+DGDADOS/dbprod/datafile/users.262.775861967"
datafile 5 switched to datafile copy "+DGDADOS/dbprod/datafile/users.260.775861915"
datafile 6 switched to datafile copy "+DGDADOS/dbprod/datafile/users.263.775861983"
datafile 9 switched to datafile copy "+DGDADOS/dbprod/datafile/master.261.775861941"

RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    442      SYSTEM               ***     +DGDADOS/dbprod/datafile/system.258.775861825
2    759      UNDOTBS1             ***     +DGDADOS/dbprod/datafile/undotbs1.257.775861731
3    255      SYSAUX               ***     +DGDADOS/dbprod/datafile/sysaux.259.775861881
4    104      USERS                ***     +DGDADOS/dbprod/datafile/users.262.775861967
5    202      USERS                ***     +DGDADOS/dbprod/datafile/users.260.775861915
6    102      USERS                ***     +DGDADOS/dbprod/datafile/users.263.775861983
9    160      MASTER               ***     +DGDADOS/dbprod/datafile/master.261.775861941

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    2048     TEMP                 2048        /oraprd02/oradata/dbprod/temp06.dbf
2    3072     TEMP                 3072        /oraprd02/oradata/dbprod/temp05.dbf

RMAN>

Vamos abrir o Banco de Dados com a opção “resetlogs”.
Porque abrir o Banco de Dados com a opção de RESETLOGS?
Porque como houve restauração e recuperação completa do Banco de Dados através do um backup do “controlfile’, isso gera uma nova incarnação do Banco de Dados.

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 02/21/2012 21:16:08
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DGDADOS/dbprod/datafile/system.258.775861825'

RMAN>

O erro acima ocorre, pois o datafile da tablespace “SYSTEM” está inconsistente.
Vamos recuperar o Banco de Dados Oracle e abrir novamente com a opção “resetlogs”.

RMAN> recover database;

Starting recover at 21-FEB-12
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 214 is already on disk as file /oraprd02/oradata/dbprod/redo001.log
archive log filename=/oraprd02/oradata/dbprod/redo001.log thread=1 sequence=214
media recovery complete, elapsed time: 00:00:09
Finished recover at 21-FEB-12

RMAN> alter database open resetlogs;

database opened

RMAN> exit


Recovery Manager complete.
[oracle@serveroracle ~]$

Após o Banco de Dados aberto, vamos verificar os arquivos do mesmo após a migração dos arquivos de dados e continuar o processo de migração de outros arquivos.

[oracle@serveroracle ~]$ echo $ORACLE_SID
dbprod
[oracle@serveroracle ~]$ sqlplus sys/******* as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 21 21:19:29 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Release 10.2.0.5.0 - Production

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
dbprod           OPEN

SQL> col name for a50
SQL> select name from v$datafile;

NAME
--------------------------------------------------
+DGDADOS/dbprod/datafile/system.258.775861825
+DGDADOS/dbprod/datafile/undotbs1.257.775861731
+DGDADOS/dbprod/datafile/sysaux.259.775861881
+DGDADOS/dbprod/datafile/users.262.775861967
+DGDADOS/dbprod/datafile/users.260.775861915
+DGDADOS/dbprod/datafile/users.263.775861983
+DGDADOS/dbprod/datafile/master.261.775861941

7 rows selected.

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DGDADOS/dbprod/controlfile/cu
                                                 rrent.256.776451519
SQL> select name from v$tempfile;

NAME
--------------------------------------------------
/oraprd02/oradata/dbprod/temp06.dbf
/oraprd02/oradata/dbprod/temp05.dbf

SQL>

Conforme acima, migramos para o ASM os arquivos de dados e os controlfiles. Visualizando os TempFiles ainda encontra-se no filesystem.
Vamos remover os arquivos temporários e criar os mesmo no Disco de Grupo “+DGDADOS”.

SQL> alter database tempfile '/oraprd02/oradata/dbprod/temp06.dbf' drop including datafiles;

Database altered.

SQL> alter database tempfile '/oraprd02/oradata/dbprod/temp05.dbf' drop including datafiles;

Database altered.

SQL> alter tablespace temp add tempfile '+DGDADOS' size 128M autoextend on next 64M maxsize 3072M;

Tablespace altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------
+DGDADOS/dbprod/tempfile/temp.264.775862855

SQL>

Vamos migrar os redo log files para o Disco de Grupo destinado aos mesmos “+DGREDOA” e “+DGREDOB”.

SQL> set lines 155
col MEMBER for a50
select l.GROUP#, l.THREAD#, l.SEQUENCE#, l.ARCHIVED, l.STATUS, (l.BYTES/1024/1024) BYTES_MB, lf.MEMBER
from v$log l join v$logfile lf
on(l.GROUP#=lf.GROUP#)
where l.THREAD# = 1
order by 1
;SQL> SQL>   2    3    4    5    6

    GROUP#    THREAD#  SEQUENCE# ARC STATUS             BYTES_MB MEMBER
---------- ---------- ---------- --- ---------------- ---------- --------------------------------------------------
         1          1          1 NO  CURRENT                  80 /oraprd02/oradata/dbprod/redo001.log
         2          1          0 YES UNUSED                   80 /oraprd02/oradata/dbprod/redo002.log
         3          1          0 YES UNUSED                   80 /oraprd02/oradata/dbprod/redo003.log

SQL> alter database drop logfile group 2;

Database altered.

SQL> ALTER DATABASE
  ADD LOGFILE GROUP 2 ('+DGREDOA','+DGREDOB')
      SIZE 100M;  2    3

Database altered.

SQL>  alter database drop logfile group 3;

Database altered.

SQL> ALTER DATABASE
  ADD LOGFILE GROUP 3 ('+DGREDOA','+DGREDOB')
      SIZE 100M;  2    3

Database altered.

SQL>

Como o membro “/oraprd02/oradata/dbprod/redo001.log” do “grupo 1” está sendo acessado correntemente, portanto devemos trocar de grupo para removermos este grupo e criar o mesmo nos Discos de Grupo “+DGREDOA” e “+DGREDOB”. 

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> select l.GROUP#, l.THREAD#, l.SEQUENCE#, l.ARCHIVED, l.STATUS, (l.BYTES/1024/1024) BYTES_MB, lf.MEMBER
from v$log l join v$logfile lf
on(l.GROUP#=lf.GROUP#)
where l.THREAD# = 1
order by 1
;  2    3    4    5    6

    GROUP#    THREAD#  SEQUENCE# ARC STATUS             BYTES_MB MEMBER
---------- ---------- ---------- --- ---------------- ---------- --------------------------------------------------
         1          1          1 YES INACTIVE                 80 /oraprd02/oradata/dbprod/redo001.log
         2          1          2 NO  CURRENT                 100 +DGREDOB/dbprod/onlinelog/group_2.256.776451595
         2          1          2 NO  CURRENT                 100 +DGREDOA/dbprod/onlinelog/group_2.256.776451583
         3          1          0 YES UNUSED                  100 +DGREDOB/dbprod/onlinelog/group_3.257.776450393
         3          1          0 YES UNUSED                  100 +DGREDOA/dbprod/onlinelog/group_3.257.776450383		 

SQL> alter database drop logfile group 1;

Database altered.

SQL> ALTER DATABASE
  ADD LOGFILE GROUP 1 ('+DGREDOA','+DGREDOB')
      SIZE 100M;  2    3

Database altered.

SQL>  select l.GROUP#, l.THREAD#, l.SEQUENCE#, l.ARCHIVED, l.STATUS, (l.BYTES/1024/1024) BYTES_MB, lf.MEMBER
from v$log l join v$logfile lf
on(l.GROUP#=lf.GROUP#)
where l.THREAD# = 1
order by 1
;  2    3    4    5    6
 
    GROUP#    THREAD#  SEQUENCE# ARC STATUS             BYTES_MB MEMBER
---------- ---------- ---------- --- ---------------- ---------- --------------------------------------------------
         1          1          0 YES UNUSED                  100 +DGREDOA/dbprod/onlinelog/group_1.258.776450197
         1          1          0 YES UNUSED                  100 +DGREDOB/dbprod/onlinelog/group_1.258.776450205
         2          1          2 NO  CURRENT                 100 +DGREDOB/dbprod/onlinelog/group_2.256.776451595
         2          1          2 NO  CURRENT                 100 +DGREDOA/dbprod/onlinelog/group_2.256.776451583
         3          1          0 YES UNUSED                  100 +DGREDOB/dbprod/onlinelog/group_3.257.776450393
         3          1          0 YES UNUSED                  100 +DGREDOA/dbprod/onlinelog/group_3.257.776450383
		 

6 rows selected.

SQL>

Vamos verificar o SPFILE e criar o mesmo dentro do Disco de Grupo “+DGDADOS” e desligar o banco de dados.

SQL> sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oraprd01/app/oracle/product/1
                                                 0.2.0/db_1/dbs/spfiledbprod.or
                                                 a
SQL> create pfile='$ORACLE_BASE/init.ora' from spfile;

File created.

SQL>  create spfile='+DGDADOS' from pfile='$ORACLE_BASE/init.ora';

File created.

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

Vamos remover o SPFILE do filesystem e no arquivo “initdbprod.ora” vamos adicionar o novo caminho do SPFILE do “+DGDADOS”.

[oracle@serveroracle ~]$ cd /oraprd01/app/oracle/product/10.2.0/db_1/dbs/
[oracle@serveroracle dbs]$ cp -p spfiledbprod.ora spfiledbprod.ora.bkp
[oracle@serveroracle dbs]$ rm spfiledbprod.ora

Vamos verificar no ASM o caminho do SPFILE para colocar no arquivo “initdbprod.ora”.

[oracle@serveroracle dbs]$ export ORACLE_SID=+ASM
[oracle@serveroracle dbs]$ asmcmd
ASMCMD> cd +DGDADOS/DBPROD/PARAMETERFILE
ASMCMD> pwd
+DGDADOS/DBPROD/PARAMETERFILE
ASMCMD> ls -ltr
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  MIRROR  COARSE   FEB 28 17:00:00  Y    spfile.265.776453043
ASMCMD> exit
[oracle@serveroracle dbs]$ vi initdbprod.ora
[oracle@serveroracle dbs]$ cat initdbprod.ora
spfile='+DGDADOS/DBPROD/PARAMETERFILE/spfile.265.776453043'
[oracle@serveroracle dbs]$ exit
exit

Vamos iniciar o Banco de Dados com o SPFILE e verificar o mesmo.

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  1277824 bytes
Variable Size             541065344 bytes
Database Buffers          524288000 bytes
Redo Buffers                7110656 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DGDADOS/dbprod/parameterfile/
                                                 spfile.265.776453043
SQL> !

Após ter migrado a maioria dos arquivos, vamos migrar o Enterprise Manager para o ASM. Vamos remover o mesmo e criar novamente o “dbconsole”.
OBS: Veja nas imagenas da galeria o EM após criado com o ASM.

[oracle@serveroracle ~]$ emctl status dbconsole
TZ set to Brazil/East
Oracle Enterprise Manager 10g Database Control Release 10.2.0.5.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
https://serveroracle.localdomain:5500/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory /oraprd01/app/oracle/product/10.2.0/db_1/serveroracle.localdomain_dbprod/sysman/log
[oracle@serveroracle ~]$ emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Feb 21, 2012 10:24:27 PM
EM Configuration Assistant, Version 10.2.0.5.0 Production
Copyright (c) 2003, 2009, Oracle.  All rights reserved.

Enter the following information:
Database SID: dbprod
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

----------------------------------------------------------------------
WARNING : While repository is dropped the database will be put in quiesce mode.
----------------------------------------------------------------------

Do you wish to continue? [yes(Y)/no(N)]: Y
Feb 21, 2012 10:24:40 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /oraprd01/app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/dbprod/emca_2012-02-21_10-24-27-PM.log.
Feb 21, 2012 10:24:43 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Feb 21, 2012 10:25:12 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Feb 21, 2012 10:25:13 PM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
Feb 21, 2012 10:27:55 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Feb 21, 2012 10:27:56 PM
[oracle@serveroracle ~]$ emca -config dbcontrol db -repos create

STARTED EMCA at Feb 21, 2012 10:29:22 PM
EM Configuration Assistant, Version 10.2.0.5.0 Production
Copyright (c) 2003, 2009, Oracle.  All rights reserved.

Enter the following information:
Database SID: dbprod
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME [ /oraprd01/app/oracle/product/10.2.0/db_1 ]:
ASM SID [ +ASM ]:
ASM port [ 1521 ]:
ASM user role [ SYSDBA ]:
ASM username [ SYS ]:
ASM user password:
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /oraprd01/app/oracle/product/10.2.0/db_1

Local hostname ................ serveroracle.localdomain
Listener port number ................ 1521
Database SID ................ dbprod
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
ASM ORACLE_HOME ................ /oraprd01/app/oracle/product/10.2.0/db_1
ASM SID ................ +ASM
ASM port ................ 1521
ASM user role ................ SYSDBA
ASM username ................ SYS

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Feb 21, 2012 10:31:42 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /oraprd01/app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/dbprod/emca_2012-02-21_10-29-22-PM.log.
Feb 21, 2012 10:31:50 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Feb 21, 2012 10:36:24 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Feb 21, 2012 10:36:55 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Feb 21, 2012 10:38:33 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Feb 21, 2012 10:38:33 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Feb 21, 2012 10:40:40 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Feb 21, 2012 10:40:41 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://serveroracle.localdomain:5500/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Feb 21, 2012 10:40:41 PM
[oracle@serveroracle ~]$

Vamos agora gerar os archivelogs para dentro do Disco de Grupo “+DGARCH”.
Vamos criar o diretório no ASM para o arquivamento dos archivelogs alterar o destino do archivelog no parâmetro e reiniciar o Banco de Dados. 

[oracle@serveroracle ~]$ export ORACLE_SID=+ASM
[oracle@serveroracle ~]$ asmcmd
ASMCMD> ls -l
State    Type    Rebal  Unbal  Name
MOUNTED  NORMAL  N      N      DGARCH/
MOUNTED  NORMAL  N      N      DGDADOS/
MOUNTED  NORMAL  N      N      DGREDOA/
MOUNTED  NORMAL  N      N      DGREDOB/
ASMCMD> mkdir DGARCH/DBPROD/
ASMCMD> mkdir DGARCH/DBPROD/ARCHIVEDLOG
ASMCMD> exit
[oracle@serveroracle ~]$ exit
exit

SQL>  create pfile='$ORACLE_BASE/init.ora' from spfile;

File created.

SQL> show parameter log_archive_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string      /orabackup/dbprod/oraarchive
log_archive_dest_1                   string
.
.
log_archive_dest_state_9             string      enable
SQL> alter system set log_archive_dest='' scope=spfile;

System altered.

SQL> alter system set log_archive_dest_1='LOCATION=+DGARCH/DBPROD/ARCHIVEDLOG' scope=spfile;

System altered.

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

Total System Global Area 1073741824 bytes
Fixed Size                  1277824 bytes
Variable Size             541065344 bytes
Database Buffers          524288000 bytes
Redo Buffers                7110656 bytes
Database mounted.
Database opened.
SQL> !

Após tudo migrado, vamos realizar um backup full do Banco de Dados.

[oracle@serveroracle ~]$ rman target sys/*****************

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Feb 21 23:44:56 2012

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

connected to target database: DBPROD (DBID=862715809)

RMAN> run {
allocate channel c1 device type disk maxpiecesize 1024M;
backup as backupset
2>      format='/orabackup/dbprod/fisico/full_%d_%t_%p_%D_%M_%Y_%t'
3> 4> 5>        tag='BKP_FULL'
                (database);
6> 7> backup as backupset
8>      format='/orabackup/dbprod/fisico/ctlf_%d_%t_%p_%D_%M_%Y_%t'
9>      tag='BKP_CONTROLFILE'
                (current controlfile);
sql 'alter system switch logfile';
10> 11> 12> backup as backupset
13>     format='/orabackup/dbprod/fisico/arch_%d_%t_%p_%D_%M_%Y_%t'
14>     tag='BKP_ARCHIVELOG'
15>             (archivelog all delete input);
16> backup as backupset
17>     format='/orabackup/dbprod/fisico/spf_%d_%t_%p_%D_%M_%Y_%t'
18>     tag='BKP_SPFILE'
                (spfile);
19> 20> release channel c1;
}21>

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=136 devtype=DISK

Starting backup at 21-FEB-12
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00002 name=+DGDADOS/dbprod/datafile/undotbs1.257.775861731
input datafile fno=00001 name=+DGDADOS/dbprod/datafile/system.258.775861825
input datafile fno=00003 name=+DGDADOS/dbprod/datafile/sysaux.259.775861881
input datafile fno=00005 name=+DGDADOS/dbprod/datafile/users.260.775861915
input datafile fno=00009 name=+DGDADOS/dbprod/datafile/master.261.775861941
input datafile fno=00004 name=+DGDADOS/dbprod/datafile/users.262.775861967
input datafile fno=00006 name=+DGDADOS/dbprod/datafile/users.263.775861983
channel c1: starting piece 1 at 21-FEB-12
channel c1: finished piece 1 at 21-FEB-12
piece handle=/orabackup/dbprod/fisico/full_DBPROD_775871137_1_21_02_2012_775871137 tag=BKP_FULL comment=NONE
channel c1: starting piece 2 at 21-FEB-12
channel c1: finished piece 2 at 21-FEB-12
piece handle=/orabackup/dbprod/fisico/full_DBPROD_775871137_2_21_02_2012_775871137 tag=BKP_FULL comment=NONE
channel c1: backup set complete, elapsed time: 00:03:51
Finished backup at 21-FEB-12

Starting backup at 21-FEB-12
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current control file in backupset
channel c1: starting piece 1 at 21-FEB-12
channel c1: finished piece 1 at 21-FEB-12
piece handle=/orabackup/dbprod/fisico/ctlf_DBPROD_775871370_1_21_02_2012_775871370 tag=BKP_CONTROLFILE comment=NONE
channel c1: backup set complete, elapsed time: 00:00:04
Finished backup at 21-FEB-12

Starting Control File and SPFILE Autobackup at 21-FEB-12
piece handle=/orabackup/dbprod/fisico/c-862715809-20120221-1f comment=NONE
Finished Control File and SPFILE Autobackup at 21-FEB-12

sql statement: alter system switch logfile

Starting backup at 21-FEB-12
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=332 stamp=775864303
input archive log thread=1 sequence=2 recid=333 stamp=775865342
input archive log thread=1 sequence=3 recid=334 stamp=775866794
input archive log thread=1 sequence=4 recid=335 stamp=775868341
input archive log thread=1 sequence=5 recid=336 stamp=775871383
input archive log thread=1 sequence=6 recid=337 stamp=775871386
channel c1: starting piece 1 at 21-FEB-12
channel c1: finished piece 1 at 21-FEB-12
piece handle=/orabackup/dbprod/fisico/arch_DBPROD_775871387_1_21_02_2012_775871387 tag=BKP_ARCHIVELOG comment=NONE
channel c1: backup set complete, elapsed time: 00:00:26
channel c1: deleting archive log(s)
archive log filename=/orabackup/dbprod/oraarchive/dbprod_1_1_775862237.arc recid=332 stamp=775864303
archive log filename=/orabackup/dbprod/oraarchive/dbprod_1_2_775862237.arc recid=333 stamp=775865342
archive log filename=/orabackup/dbprod/oraarchive/dbprod_1_3_775862237.arc recid=334 stamp=775866794
archive log filename=/orabackup/dbprod/oraarchive/dbprod_1_4_775862237.arc recid=335 stamp=775868341
archive log filename=+DGARCH/dbprod/archivedlog/dbprod_1_5_775862237.arc recid=336 stamp=775871383
archive log filename=+DGARCH/dbprod/archivedlog/dbprod_1_6_775862237.arc recid=337 stamp=775871386
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=210 recid=322 stamp=775837219
input archive log thread=1 sequence=211 recid=323 stamp=775838278
input archive log thread=1 sequence=212 recid=330 stamp=775862239
input archive log thread=1 sequence=213 recid=331 stamp=775862239
input archive log thread=1 sequence=214 recid=329 stamp=775862237
channel c1: starting piece 1 at 21-FEB-12
channel c1: finished piece 1 at 21-FEB-12
piece handle=/orabackup/dbprod/fisico/arch_DBPROD_775871415_1_21_02_2012_775871415 tag=BKP_ARCHIVELOG comment=NONE
channel c1: backup set complete, elapsed time: 00:00:08
channel c1: deleting archive log(s)
archive log filename=/orabackup/dbprod/oraarchive/dbprod_1_210_774222764.arc recid=322 stamp=775837219
archive log filename=/orabackup/dbprod/oraarchive/dbprod_1_211_774222764.arc recid=323 stamp=775838278
archive log filename=/orabackup/dbprod/oraarchive/dbprod_1_212_774222764.arc recid=330 stamp=775862239
archive log filename=/orabackup/dbprod/oraarchive/dbprod_1_213_774222764.arc recid=331 stamp=775862239
archive log filename=/orabackup/dbprod/oraarchive/dbprod_1_214_774222764.arc recid=329 stamp=775862237
Finished backup at 21-FEB-12

Starting backup at 21-FEB-12
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel c1: starting piece 1 at 21-FEB-12
channel c1: finished piece 1 at 21-FEB-12
piece handle=/orabackup/dbprod/fisico/spf_DBPROD_775871424_1_21_02_2012_775871424 tag=BKP_SPFILE comment=NONE
channel c1: backup set complete, elapsed time: 00:00:02
Finished backup at 21-FEB-12

Starting Control File and SPFILE Autobackup at 21-FEB-12
piece handle=/orabackup/dbprod/fisico/c-862715809-20120221-20 comment=NONE
Finished Control File and SPFILE Autobackup at 21-FEB-12

released channel: c1

RMAN> exit


Recovery Manager complete.
[oracle@serveroracle ~]$

Agora vamos desligar o nosso Banco de Dados.
Com o ambiente de Banco de Dados Oracle em ASM, primeiramente devemos desligar a instância “dbprod” depois a instância “+ASM”, o Enterprise Manager se executando e o Listener.

[oracle@serveroracle ~]$ exit
exit

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.5.0 - Production
[oracle@serveroracle orabackup]$ export ORACLE_SID=+ASM
[oracle@serveroracle orabackup]$ sqlplus sys/********* as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 21 23:59:08 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Release 10.2.0.5.0 - Production

SQL> shutdown immediate
ASM diskgroups dismounted
ASM instance shutdown
SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.5.0 - Production
[oracle@serveroracle ~]$ emctl stop dbconsole
TZ set to Brazil/East
Oracle Enterprise Manager 10g Database Control Release 10.2.0.5.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
https://serveroracle.localdomain:5500/em/console/aboutApplication
Stopping Oracle Enterprise Manager 10g Database Control ...
 ...  Stopped.
[oracle@serveroracle orabackup]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 21-FEB-2012 23:58:40

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.104)(PORT=1521)))
The command completed successfully
[oracle@serveroracle ~]$

Pronto migramos nosso Banco de Dados Oracle Single para ASM.

%name Migrando Banco de Dados Oracle Single para ASM non RAC

Autor: Maycon Tomiasi

Formado em Tecnologia da Informação na FIPP (Faculdade de Informática de Presidente Prudente), Analista DBA Oracle pela Teiko Soluções em Tecnologia da Informação, residente em Blumenau/ SC, Certificado OCP 10g/11g/12c, OCS 11g Implementation, OCE 11g Performance Tuning, OCE 11g RAC & GRID e OPN Specialist. Conhecimentos em PHP.

  • linkedin Migrando Banco de Dados Oracle Single para ASM non RAC
  • wordpress Migrando Banco de Dados Oracle Single para ASM non RAC