Conversão de ambientes
Single File System RAC ASM
Backup + Restore + Conversão + Recovery
ORIGEM : BANCO DE DADOS ORACLE SINGLE EM FILESYSTEM NATIVO DESTINO : BANCO DE DADOS ORACLE ORACLE RAC EM ASM
Na Origem (BANCO DE DADOS ORACLE SINGLE EM FILESYSTEM NATIVO), extraia um backup via RMAN, usando o script abaixo:
Obs.: Para entrar no RMAN, sete as variáveis pertinentes ao SID que deseja fazer a conversão e use o comando "rman target /" para entrar no RMAN.
run {
allocate channel canal01 type disk;
allocate channel canal02 type disk;
allocate channel canal03 type disk;
allocate channel canal04 type disk;
backup as compressed backupset full
format '/oracle/backup/rman/BACKUP_database_%d_%I_%s_[%D_%M_%Y]' filesperset = 4
database plus archivelog
format '/oracle/backup/rman/BACKUP_archives_%d_%I_%s_[%D_%M_%Y]' filesperset = 20 ;
}
%d : Nome do DB
%I : DBID (database ID)
%s : Número do backupset (Nunca vai se repetir)
[%D_%M_%Y] : DATA (dd_mm_aaaa)
Vamos a algumas observações:
1. Certifique-se que na origem o diretório /oracle/backup/rman exista caso não exista criado crie, tenha espaço para receber o backup e tenhas as permissões necessárias de gravação.
2. Certifique-se que o banco origem esteja em ARCHIVE LOG MODE ou faça essa operação com o banco origem em estado mount (backup frio)
Caso não esteja em "autobackup controlfile" (para ver show all conectado ao RMAN) é necessário fazer o backup do controlfile manualmente com o comando "backup current controlfile" O backup será criado no FRA, caso o FRA não esteja habilitado, o mesmo é jogado em $ORACLE_HOME/dbs
O que é FRA?
Flash Recovery Area definido no parâmetro db_recovery_file_dest.
Vamos transferir os backup e o controlfile para o destino.
obs.: Use o usuário owner do oracle para isso !!!!
Exemplo: Se o oracle tem um usuário oracle na origem o usuário destino deve ser o usuário equivalente ou seja o owner oracle no destino !!
Neste caso, fizemos o backup do controlfile via comando "backup current controlfile"
Backup)
No destino (BANCO DE DADOS ORACLE RAC EM ASM) devemos criar o caminho de diretório /oracle/backup/rman
mkdir -p /oracle/backup/rman
scp -r /oracle/backup/rman/ root@DISTINO_RAC:/oracle/backup
Control File)
Vamos pegar o controlfile na Origem e transferir para o destino.
Sabendo que na origem (SINGLE) o controlfile está localizado em /oracle/FRA/autobackup/2013_07_08/, cujo path inteiro é
/oracle/FRA/autobackup/2013_07_08/o1_mf_ncnnf_TAG20130801T120854_8znyj7l8_.bkp
E o nome do arquivo é o1_mf_ncnnf_TAG20130801T120854_8znyj7l8_.bkp
Vamos transferir via scp o controlfile para o caminho (path) onde os backupset (backup full database) foi transferido, ou seja, vamos transferir o arquivo localizado na origem (SINGLE), cujo path ou caminho completo é
/oracle/FRA/autobackup/2013_07_08/o1_mf_ncnnf_TAG20130801T120854_8znyj7l8_.bkp, para o destino cujo path ou caminho completo ira ser /oracle/backup/rman/ .
Comando:
scp -r /oracle/FRA/autobackup/2013_07_08/o1_mf_s_820254244_8xp4v236_.bkp root@DESTINO_RAC:/oracle/backup/rman/
Após isso, crie uma Instance no RAC via DBCA para receber o banco de dados.
Veja os detalhes no link abaixo:
http://dbafurushima.com.br/criacao_database_linux_rh5.html
O fato de criarmos essa Instance é um artificio/atalho para facilitar o trabalho, pois a criação de uma Instance em RAC, contempla uma criação de recurso no clusterware, recurso esse que é flutuado em uma possível operação de Failover.
Veja um exemplo, usando o crsctl:
ora.tst.db
1 ONLINE ONLINE dbafurushima01 Open 2 ONLINE ONLINE dbafurushima02 Open
O dbca criará os seguintes arquivos:
Parameter File
Datafile
Controlfiles
Redo Log Files
Archives
Entre no ASMCMD, setando as variáveis pertinente ao GRID Infrastructure. É necessário eliminar os seguintes arquivos criados, após finalização da criação da Instance via DBCA :
Datafile
Controlfiles
Redo Log Files
Archives (Se houver)
Vamos iniciar agora as parametrizações para conversão da características da origem para o destino.
NO DESTINO: RAC
Vamos ajustar as parametrização de Instancia
Criar um pfile no home do usuário da instancia através do spfile criado.
create pfile='/home/oratst/pfile' from spfile ; Baixar o banco
srvctl stop database -d tst
c) Ajustar parâmetros
Vamos ajustar para que essa instancia vire uma single
Iremos fazer tudo a partir do node 1
pfile :
*.audit_file_dest='/oracle/admin/TST/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/TST/controlfile/current.367.820259197','+DATA/TST/cont rolfile/current.368.820259197'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='tst'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=53191114752
*.diagnostic_dest='/oracle' TST1.instance_number=1 TST2.instance_number=2
*.log_archive_dest_1='LOCATION=+DATA'
*.log_archive_format='SAP_archive_TST_%t_%s_%r.dbf'
*.memory_target=2187329536
*.open_cursors=300
*.processes=150
*.remote_listener='cluster_rac:1521'
*.remote_login_passwordfile='exclusive' TST2.thread=2
TST1.thread=1
TST1.undo_tablespace='UNDOTBS1' TST2.undo_tablespace='UNDOTBS2'
Vamos comentar as linhas:
TST2.instance_number=2
TST2.thread=2
TST2.undo_tablespace='UNDOTBS2'
*.cluster_database=true
d) Vamos subir o banco de dados na mão
sqlplus / as sysdba
startup nomount pfile=/home/oratst/pfile
Vamos agora fazer o procedimento de Rman Restore + conversão + recover
Vamos entrar no rman na ORIGEM e montar o bloco run para este ser executado no DESTINO.
rman target /
No script run do rman para o processo de restore p/ asm é necessário ter o seguinte estrutura:
run {
SET NEWNAME FOR DATAFILE 1 to NEW ; SET NEWNAME FOR DATAFILE 2 to NEW ;
...SET NEWNAME PARA TODOS OS DATAFILES...
RESTORE DATABASE;
}
Para facilitar o uso do “SET NEWNAME FOR DATAFILE to NEW” é necessário ter o OMF ativado, ou seja, o parâmetro db_create_file_dest, verifique se no DESTINO (RAC), este parâmetro está setado, por padrão o ASM, usa o OMF.
Afim de facilitar a montagem do código procedural rman (bloco run), fiz uma query que monta todos os comando “SET NEWNAME FOR DATAFILE to NEW”, esta query deve ser executada no ORIGEM para montarmos o código procedural rman (bloco run), que será executado no DISTINO.
select distinct * from (
select distinct ' SET NEWNAME FOR DATAFILE ' || FILE_ID|| ' to NEW ; ' from dba_data_files union
select distinct' SET NEWNAME FOR TEMPFILE ' || FILE_ID|| ' to NEW ; ' from DBA_TEMP_FILES
) ;
Veja o meu retorno:
SET NEWNAME FOR DATAFILE 1 to NEW ; SET NEWNAME FOR DATAFILE 10 to NEW ; SET NEWNAME FOR DATAFILE 11 to NEW ; SET NEWNAME FOR DATAFILE 12 to NEW ; SET NEWNAME FOR DATAFILE 13 to NEW ; SET NEWNAME FOR DATAFILE 14 to NEW ; SET NEWNAME FOR DATAFILE 15 to NEW ; SET NEWNAME FOR DATAFILE 16 to NEW ; SET NEWNAME FOR DATAFILE 17 to NEW ; SET NEWNAME FOR DATAFILE 18 to NEW ; SET NEWNAME FOR DATAFILE 19 to NEW ; SET NEWNAME FOR DATAFILE 2 to NEW ; SET NEWNAME FOR DATAFILE 20 to NEW ; SET NEWNAME FOR DATAFILE 21 to NEW ; SET NEWNAME FOR DATAFILE 22 to NEW ; SET NEWNAME FOR DATAFILE 23 to NEW ; SET NEWNAME FOR DATAFILE 24 to NEW ; SET NEWNAME FOR DATAFILE 25 to NEW ; SET NEWNAME FOR DATAFILE 26 to NEW ; SET NEWNAME FOR DATAFILE 3 to NEW ; SET NEWNAME FOR DATAFILE 4 to NEW ; SET NEWNAME FOR DATAFILE 5 to NEW ; SET NEWNAME FOR DATAFILE 6 to NEW ; SET NEWNAME FOR DATAFILE 7 to NEW ; SET NEWNAME FOR DATAFILE 8 to NEW ; SET NEWNAME FOR DATAFILE 9 to NEW ; SET NEWNAME FOR TEMPFILE 1 to NEW ;
Com essas informações é possível montar o bloco run, veja como ficou:
run {
SET NEWNAME FOR DATAFILE 1 to NEW ; SET NEWNAME FOR DATAFILE 10 to NEW ;
SET NEWNAME FOR DATAFILE 11 to NEW ; SET NEWNAME FOR DATAFILE 12 to NEW ; SET NEWNAME FOR DATAFILE 13 to NEW ; SET NEWNAME FOR DATAFILE 14 to NEW ; SET NEWNAME FOR DATAFILE 15 to NEW ; SET NEWNAME FOR DATAFILE 16 to NEW ; SET NEWNAME FOR DATAFILE 17 to NEW ; SET NEWNAME FOR DATAFILE 18 to NEW ; SET NEWNAME FOR DATAFILE 19 to NEW ; SET NEWNAME FOR DATAFILE 2 to NEW ; SET NEWNAME FOR DATAFILE 20 to NEW ; SET NEWNAME FOR DATAFILE 21 to NEW ; SET NEWNAME FOR DATAFILE 22 to NEW ; SET NEWNAME FOR DATAFILE 23 to NEW ; SET NEWNAME FOR DATAFILE 24 to NEW ; SET NEWNAME FOR DATAFILE 25 to NEW ; SET NEWNAME FOR DATAFILE 26 to NEW ; SET NEWNAME FOR DATAFILE 3 to NEW ; SET NEWNAME FOR DATAFILE 4 to NEW ; SET NEWNAME FOR DATAFILE 5 to NEW ; SET NEWNAME FOR DATAFILE 6 to NEW ; SET NEWNAME FOR DATAFILE 7 to NEW ; SET NEWNAME FOR DATAFILE 8 to NEW ; SET NEWNAME FOR DATAFILE 9 to NEW ; SET NEWNAME FOR TEMPFILE 1 to NEW ; Restore database ;
}
Vamos no DISTINO, no node 1, vamos iniciar o procedimento, certifique-se que as duas instancias estejam down, caso esteja, vamos usar o pfile criado e devidamente ajustado, conforme mencionado nos textos anteriores e subir somente a instancia 1 com o este pfile com o estado nomount.
startup nomount pfile=/home/oratst/pfile ;
Após isso vamos entrar no RMAN, ainda no DESTINO e fazer o restore do controlfile:
restore controlfile from
'/oracle/backup/rman/o1_mf_ncnnf_TAG20130801T120854_8znyj7l8_.bkp' ;
Lembrando que na origem não existe FRA portanto temos que passar o caminho inteiro:
/oracle/backup/rman/o1_mf_ncnnf_TAG20130801T120854_8znyj7l8_.bkp
Veja o LOG:
[tst1.oratst@dbafurushima:/home/oratst]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Aug 1 17:00:01 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: tst (not mounted) RMAN> restore controlfile from
'/oracle/backup/rman/o1_mf_ncnnf_TAG20130801T120854_8znyj7l8_.bkp’;
Starting restore at 01-AUG-13
using target database control file instead of recovery catalog allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=190 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 output file name=+DATA/tst/controlfile/current.313.822327669 output file name=+DATA/tst/controlfile/current.314.822327669 Finished restore at 01-AUG-13
Obs.: O controlfile será criado seguindo o parâmetro no pfile *.controlfile
Feito isso vamos montar o banco de dados, como comando:
alter database mount ; obs.: No próprio rman.
Após isso é possível executar o bloco run, montado previamente:
executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME
executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME
Starting restore at 01-AUG-13
Starting implicit crosscheck backup at 01-AUG-13 allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=66 device type=DISK Crosschecked 13 objects
Finished implicit crosscheck backup at 01-AUG-13
Starting implicit crosscheck copy at 01-AUG-13 using channel ORA_DISK_1
Finished implicit crosscheck copy at 01-AUG-13
searching for all files in the recovery area cataloging files...
cataloging done
Starting restore at 01-AUG-13 using channel ORA_DISK_1
channel ORA_DISK_1: restoring datafile 00001 input datafile copy RECID=26 STAMP=822331691 file name=+DATA/tst/datafile/system.356.822330299 destination for restore of datafile 00001: +DATA
channel ORA_DISK_1: copied datafile copy of datafile 00001
output file name=+DATA/tst/datafile/system.357.822332113 RECID=27 STAMP=822332116 channel ORA_DISK_1: restoring datafile 00003
input datafile copy RECID=24 STAMP=822331691 file name=+DATA/tst/datafile/UNDOTBS.354.822330295 destination for restore of datafile 00003: +DATA
channel ORA_DISK_1: copied datafile copy of datafile 00003
output file name=+DATA/tst/datafile/UNDOTBS.358.822332119 RECID=28 STAMP=822332144
channel ORA_DISK_1: restoring datafile 00009 input datafile copy RECID=25 STAMP=822331691 file name=+DATA/tst/datafile/psapsr3.355.822330297 destination for restore of datafile 00009: +DATA
channel ORA_DISK_1: copied datafile copy of datafile 00009
output file name=+DATA/tst/datafile/psapsr3.359.822332145 RECID=29 STAMP=822332155
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00005 to +DATA
channel ORA_DISK_1: restoring datafile 00011 to +DATA channel ORA_DISK_1: restoring datafile 00020 to +DATA channel ORA_DISK_1: restoring datafile 00026 to +DATA channel ORA_DISK_1: reading from backup piece
/oracle/backup/rman/BACKUP_database_tst_2752271903_11_[01_08_2013]
channel ORA_DISK_1: piece
handle=/oracle/backup/rman/BACKUP_database_tst_2752271903_11_[01_08_2013]
tag=TAG20130801T114312
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:06:05 channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00006 to +DATA
channel ORA_DISK_1: restoring datafile 00012 to +DATA channel ORA_DISK_1: restoring datafile 00016 to +DATA channel ORA_DISK_1: restoring datafile 00021 to +DATA channel ORA_DISK_1: reading from backup piece
/oracle/backup/rman/BACKUP_database_tst_2752271903_7_[01_08_2013]
channel ORA_DISK_1: piece
handle=/oracle/backup/rman/BACKUP_database_tst_2752271903_7_[01_08_2013]
tag=TAG20130801T114312
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:06:25 channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00007 to +DATA
channel ORA_DISK_1: restoring datafile 00013 to +DATA channel ORA_DISK_1: restoring datafile 00017 to +DATA channel ORA_DISK_1: restoring datafile 00025 to +DATA channel ORA_DISK_1: reading from backup piece
/oracle/backup/rman/BACKUP_database_tst_2752271903_5_[01_08_2013]
channel ORA_DISK_1: piece
handle=/oracle/backup/rman/BACKUP_database_tst_2752271903_5_[01_08_2013]
tag=TAG20130801T114312
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:07:15 channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00008 to +DATA
channel ORA_DISK_1: restoring datafile 00014 to +DATA channel ORA_DISK_1: restoring datafile 00018 to +DATA channel ORA_DISK_1: restoring datafile 00019 to +DATA channel ORA_DISK_1: reading from backup piece
/oracle/backup/rman/BACKUP_database_tst_2752271903_6_[01_08_2013]
channel ORA_DISK_1: piece
handle=/oracle/backup/rman/BACKUP_database_tst_2752271903_6_[01_08_2013]
tag=TAG20130801T114312
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:06:56 channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00015 to +DATA
channel ORA_DISK_1: restoring datafile 00022 to +DATA channel ORA_DISK_1: restoring datafile 00024 to +DATA channel ORA_DISK_1: reading from backup piece
/oracle/backup/rman/BACKUP_database_tst_2752271903_8_[01_08_2013]
channel ORA_DISK_1: piece
handle=/oracle/backup/rman/BACKUP_database_tst_2752271903_8_[01_08_2013]
tag=TAG20130801T114312
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:08:25 channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00002 to +DATA
channel ORA_DISK_1: restoring datafile 00004 to +DATA channel ORA_DISK_1: restoring datafile 00010 to +DATA channel ORA_DISK_1: restoring datafile 00023 to +DATA channel ORA_DISK_1: reading from backup piece
/oracle/backup/rman/BACKUP_database_tst_2752271903_9_[01_08_2013]
channel ORA_DISK_1: piece
handle=/oracle/backup/rman/BACKUP_database_tst_2752271903_9_[01_08_2013]
tag=TAG20130801T114312
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:06:45 Finished restore at 01-AUG-13
Neste passo os datafiles estarão no ASM, o caminho antigo (filesystem nativo) estão ainda mapeados no Controlfile. É necessário ajustar o controlfile para que ele saiba o novo mapeamento.
Isso pode ser feito de 2 maneiras
1 - Recriar o controlfile
2 - Fazer o SWITCH no RMAN
Vamos optar pela 2 opção devido a sua maior facilidade.
Vamos executar o comando "SWITCH database to copy" para que ocorra o novo mapeamento do controlfile.
Veja o log de execução
RMAN> SWITCH database to copy ;
datafile 1 switched to datafile copy "+DATA/tst/datafile/system.357.822332113"
datafile 2 switched to datafile copy "+DATA/tst/datafile/sysaux.382.822334273"
datafile 3 switched to datafile copy "+DATA/tst/datafile/UNDOTBS.358.822332119"
datafile 4 switched to datafile copy "+DATA/tst/datafile/psapsr3.380.822334269"
datafile 5 switched to datafile copy "+DATA/tst/datafile/psapsr3.361.822332163"
datafile 6 switched to datafile copy "+DATA/tst/datafile/psapsr3.365.822332529"
datafile 7 switched to datafile copy "+DATA/tst/datafile/psapsr3.369.822332913"
datafile 8 switched to datafile copy "+DATA/tst/datafile/psapsr3.373.822333349"
datafile 9 switched to datafile copy "+DATA/tst/datafile/psapsr3.359.822332145"
datafile 10 switched to datafile copy "+DATA/tst/datafile/psapsr3.381.822334271"
datafile 11 switched to datafile copy "+DATA/tst/datafile/psapsr3.362.822332165"
datafile 12 switched to datafile copy "+DATA/tst/datafile/psapsr3.366.822332529"
datafile 13 switched to datafile copy "+DATA/tst/datafile/psapsr3.370.822332915"
datafile 14 switched to datafile copy "+DATA/tst/datafile/psapsr3.374.822333351"
datafile 15 switched to datafile copy "+DATA/tst/datafile/psapsr3.378.822333767"
datafile 16 switched to datafile copy "+DATA/tst/datafile/psapsr3.367.822332531"
datafile 17 switched to datafile copy "+DATA/tst/datafile/psapsr3.371.822332917"
datafile 18 switched to datafile copy "+DATA/tst/datafile/psapsr3.375.822333351"
datafile 19 switched to datafile copy "+DATA/tst/datafile/psapsr3731.372.822333345"
datafile 20 switched to datafile copy "+DATA/tst/datafile/psapsr3731.360.822332161"
datafile 21 switched to datafile copy "+DATA/tst/datafile/psapsr3731.364.822332525"
datafile 22 switched to datafile copy "+DATA/tst/datafile/psapsr3731.376.822333761"
datafile 23 switched to datafile copy "+DATA/tst/datafile/psapsr3731.379.822334267"
datafile 24 switched to datafile copy "+DATA/tst/datafile/psapsr3731.377.822333763"
datafile 25 switched to datafile copy "+DATA/tst/datafile/psapsr3731.368.822332911"
datafile 26 switched to datafile copy "+DATA/tst/datafile/psapsr3usr.363.822332165"
Vamos executar o procedimento de RECOVER com o comando RECOVER DATABASE.
RMAN> RECOVER DATABASE;
Starting recover at 01-AUG-13 using channel ORA_DISK_1
starting media recovery
RMAN-08187: WARNING: media recovery until SCN 41914917 complete Finished recover at 01-AUG-13
Vamos agora executar alguns tópicos pós-migração.
1. Ajuste dos REDO LOG FILES
a) Criaremos a THREAD 2 dos redo logfiles.
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 5 ('+DATA') size 500M,
GROUP 6 ('+DATA') size 500M, GROUP 7 ('+DATA') size 500M, GROUP 8 ('+DATA') size 500M;
ALTER DATABASE enable THREAD 2;
b) Ajustes na THREAD 1
Todos os membros que estiverem com caminho de filesystem nativo é necessário renomea-los para no caminho do asm.
É possível visualizar isso com a query:
select MEMBER from v$logfile ;
MEMBER
--- /oracle/tst/origlogA/log_g11m1.dbf /oracle/tst/mirrlogA/log_g11m2.dbf /oracle/tst/origlogB/log_g12m1.dbf /oracle/tst/mirrlogB/log_g12m2.dbf /oracle/tst/origlogA/log_g13m1.dbf /oracle/tst/mirrlogA/log_g13m2.dbf /oracle/tst/origlogB/log_g14m1.dbf /oracle/tst/mirrlogB/log_g14m2.dbf
+DATA/tst/onlinelog/group_5.383.822339343 +DATA/tst/onlinelog/group_5.384.822339345 +DATA/tst/onlinelog/group_6.385.822339347 +DATA/tst/onlinelog/group_6.386.822339349 +DATA/tst/onlinelog/group_7.387.822339351 +DATA/tst/onlinelog/group_7.388.822339353 +DATA/tst/onlinelog/group_8.389.822339355
+DATA/tst/onlinelog/group_8.390.822339355
Percebe que os redo logs
/oracle/tst/origlogA/log_g11m1.dbf /oracle/tst/mirrlogA/log_g11m2.dbf /oracle/tst/origlogB/log_g12m1.dbf /oracle/tst/mirrlogB/log_g12m2.dbf /oracle/tst/origlogA/log_g13m1.dbf /oracle/tst/mirrlogA/log_g13m2.dbf /oracle/tst/origlogB/log_g14m1.dbf /oracle/tst/mirrlogB/log_g14m2.dbf
Estão na filesystem nativo e os redo logs +DATA/tst/onlinelog/group_5.383.822339343 +DATA/tst/onlinelog/group_5.384.822339345 +DATA/tst/onlinelog/group_6.385.822339347 +DATA/tst/onlinelog/group_6.386.822339349 +DATA/tst/onlinelog/group_7.387.822339351 +DATA/tst/onlinelog/group_7.388.822339353 +DATA/tst/onlinelog/group_8.389.822339355 +DATA/tst/onlinelog/group_8.390.822339355 Estão no asm .
TODOS devem ficar no ASM para que o banco possa abrir.
Vamos dar renames, fazendo uso da query abaixo, afim de facilitação select 'ALTER DATABASE RENAME FILE '''|| member ||''' TO '''||'+DATA/tst/onlinelog/a1'||''';'
from v$logfile
where member like '/oracle%'
Veja um exemplo de como deve ser feito:
ALTER DATABASE RENAME FILE '/oracle/tst/origlogA/log_g11m1.dbf' TO '+DATA/tst/onlinelog/a1';
ALTER DATABASE RENAME FILE '/oracle/tst/mirrlogA/log_g11m2.dbf' TO '+DATA/tst/onlinelog/a2';
ALTER DATABASE RENAME FILE '/oracle/tst/origlogB/log_g12m1.dbf' TO '+DATA/tst/onlinelog/a3';
ALTER DATABASE RENAME FILE '/oracle/tst/mirrlogB/log_g12m2.dbf' TO '+DATA/tst/onlinelog/a4';
ALTER DATABASE RENAME FILE '/oracle/tst/origlogA/log_g13m1.dbf' TO '+DATA/tst/onlinelog/a5';
ALTER DATABASE RENAME FILE '/oracle/tst/mirrlogA/log_g13m2.dbf' TO '+DATA/tst/onlinelog/a6';
ALTER DATABASE RENAME FILE '/oracle/tst/origlogB/log_g14m1.dbf' TO '+DATA/tst/onlinelog/a7';
ALTER DATABASE RENAME FILE '/oracle/tst/mirrlogB/log_g14m2.dbf' TO '+DATA/tst/onlinelog/a8';
Veja como ficou agora:
SQL> select MEMBER from v$logfile;
MEMBER
---
+DATA/tst/onlinelog/a1 +DATA/tst/onlinelog/a2 +DATA/tst/onlinelog/a3 +DATA/tst/onlinelog/a4 +DATA/tst/onlinelog/a5 +DATA/tst/onlinelog/a6 +DATA/tst/onlinelog/a7 +DATA/tst/onlinelog/a8
+DATA/tst/onlinelog/group_5.383.822339343 +DATA/tst/onlinelog/group_5.384.822339345 +DATA/tst/onlinelog/group_6.385.822339347 +DATA/tst/onlinelog/group_6.386.822339349 +DATA/tst/onlinelog/group_7.387.822339351 +DATA/tst/onlinelog/group_7.388.822339353 +DATA/tst/onlinelog/group_8.389.822339355 +DATA/tst/onlinelog/group_8.390.822339355
16 rows selected.
c) Padronização dos redo log files
Neste momento, estamos usando o pfile abaixo:
*.audit_file_dest='/oracle/admin/TST/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/TST/controlfile/current.367.820259197','+DATA/TST/cont rolfile/current.368.820259197'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='tst'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=53191114752
*.diagnostic_dest='/oracle' TST1.instance_number=1
#TST2.instance_number=2
*.log_archive_dest_1='LOCATION=+DATA'
*.log_archive_format='SAP_archive_TST_%t_%s_%r.dbf'
*.memory_target=2187329536
*.open_cursors=300
*.processes=150
*.remote_listener='cluster_rac:1521'
*.remote_login_passwordfile='exclusive'
#TST2.thread=2 TST1.thread=1
TST1.undo_tablespace='UNDOTBS1'
#TST2.undo_tablespace='UNDOTBS2'
Também neste, estamos no node 1, vamos dar um shutdown immediate, ir até a Instance do node 2 e dar um startup com o pfile corrigido, ou seja, descomentar as linhas abaixo:
#TST2.instance_number=2
#TST2.thread=2
#TST2.undo_tablespace='UNDOTBS2'
Comentar as linhas abaixo:
TST1.instance_number=1 TST1.thread=1
TST1.undo_tablespace='UNDOTBS1'
O pfile ficaria deste jeito:
*.audit_file_dest='/oracle/admin/TST/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/TST/controlfile/current.367.820259197','+DATA/TST/cont rolfile/current.368.820259197'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='tst'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=53191114752
*.diagnostic_dest='/oracle'
#TST1.instance_number=1 ####COMENTADO TST2.instance_number=2
*.log_archive_dest_1='LOCATION=+DATA'
*.log_archive_format='SAP_archive_TST_%t_%s_%r.dbf'
*.memory_target=2187329536
*.open_cursors=300
*.processes=150
*.remote_listener='cluster_rac:1521'
*.remote_login_passwordfile='exclusive' TST2.thread=2
#TST1.thread=1 ####COMENTADO
#TST1.undo_tablespace='UNDOTBS1' ####COMENTADO TST2.undo_tablespace='UNDOTBS2'
Após subida da Instance, dropar os grupos de logfile 1, 2, 3 e 4 (ISSO NO CENÁRIO DO EXEMPLO!!! TALVEZ SEJA DIFERENTE, CONSULTE A V$LOG).
ALTER DATABASE DROP LOGFILE GROUP 1 ; ALTER DATABASE DROP LOGFILE GROUP 2 ; ALTER DATABASE DROP LOGFILE GROUP 3 ; ALTER DATABASE DROP LOGFILE GROUP 4 ;
Vamos então padronizar os REDO LOG FILE.
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ('+DATA') size 500M,
GROUP 2 ('+DATA') size 500M, GROUP 3 ('+DATA') size 500M, GROUP 4 ('+DATA') size 500M;
ALTER DATABASE enable THREAD 1;
2. Ajuste na tablespace temporária
Vamos executar este select:
SELECT tablespace_name, file_name, bytes FROM dba_temp_files ;
Caso você se depare com um erro como este:
ERROR at line 1:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file ORA-01110: data file 201: '/oracle/tst/sapdata1/temp_1/temp.data1'
Isso significa que sua tablespace temporário está fora do asm e isso gera este erro .
Temos que recriar a TEMP no ASM, para isso devamos fazer uma pequena gambi :
a) Criar a tablespace TEMP_GAMBIARRA .
CREATE TEMPORARY TABLESPACE TEMP_GAMBIARRA TEMPFILE '+DATA' SIZE 5M REUSE ;
b) Mudar a DEFAULT TABLESPACE TEMPORARY
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_GAMBIARRA ; c) Dropar a tablespace temporária antiga e criar uma nova
Executar o select abaixo:
select b.NAME from v$tablespace b , v$tempfile a where a.TS# = b.TS# and b.NAME not like '%GAMBIARRA' ;
Digamos que seu nome é TBSTEMP, vamos fazer o drop :
drop tablespace TBSTEMP;
Vamos agora recriar a tablespace TEMP no ASM .
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA' SIZE 1500M REUSE AUTOEXTEND ON NEXT 1000M MAXSIZE unlimited;
d) Colocar a nova tablespace TEMP como DEFAULT
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
Vamos então dropar a tablespace gambiarra
drop TABLESPACE TEMP_GAMBIARRA INCLUDING CONTENTS AND DATAFILES ;
Vamos aplicar o select novamente, agora é para dar certo
SELECT tablespace_name, file_name, bytes FROM dba_temp_files ;
3. Ajuste da tablespace de UNDO
Para padronizar vamos criar 2 tablespace de undo, no RAC, cada node tem sua tablespace de UNDO.
CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '+DATA' SIZE 1500M AUTOEXTEND ON NEXT 1500M;
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '+DATA' SIZE 1500M AUTOEXTEND ON NEXT 1500M;
3. Ajuste final do pfile e spfile
Vamos fazer o seguinte:
Descomentar todos os seguintes parâmetros comentados
instance_number cluster_database thread
undo_tablespace
Veja o modelo do pfile :
*.audit_file_dest='/oracle/admin/tst/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/tst/controlfile/current.313.822327669','+DATA/tst/controlfile/curren t.314.822327669'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='tst'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=6005194752
*.diagnostic_dest='/oracle' tst1.instance_number=1 tst2.instance_number=2
*.log_archive_dest_1='LOCATION=+DATA'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=1099956224
*.processes=150
*.remote_listener='cluster_rac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=3300917248 tst2.thread=2
tst1.thread=1
tst1.undo_tablespace='UNDOTBS1' tst2.undo_tablespace=' UNDOTBS2'
Vamos agora, transformar o pfile ajustado para spfile e transferi-lo para o ASM:
create spfile='+DATA/tst/spfiletst.ora' from pfile='/home/oracle/pfile';
Por último vamos agora atualizar os cartuchos e options do Real Application Clusters, vemos rodar as seguintes scripts:
sqlplus / as sysdba
SQL> @?/rdbms/admin/owminst.plb SQL> @?/rdbms/admin/catclust.sql SQL> @?/rdbms/admin/utlrp.sql
Após execução veja como ficou:
Antes
10:23:15 SQL> SELECT comp_name || ' : '||version|| ' : '|| status FROM dba_registry;
COMP_NAME||':'||VERSION||':'||STATUS
--- Oracle Database Catalog Views : 11.2.0.3.0 : VALID
Oracle Database Packages and Types : 11.2.0.3.0 : VALID
Depois
10:27:29 SQL> SELECT comp_name || ' : '||version|| ' : '|| status FROM dba_registry;
COMP_NAME||':'||VERSION||':'||STATUS
--- Oracle Workspace Manager : 11.2.0.3.0 : VALID
Oracle Database Catalog Views : 11.2.0.3.0 : VALID Oracle Database Packages and Types : 11.2.0.3.0 : VALID Oracle Real Application Clusters : 11.2.0.3.0 : VALID