Este artigo tem como objetivo demonstrar a recuperação da tablespace de UNDO sem backup da mesma.
OBS: Tal procedimento é para subir o banco de dados, porém há perdas de dados nos segmentos de UNDO.
Recomenda-se sempre obter o backup físico e lógico do banco de dados para evitar problemas futuros.
Vamos realizar o procedimento de recuperação da tablespace.
Primeiramente vamos localizar o datafile de UNDO.
oelas.tomiasi.local=oracle=orcl-> cd /u01/app/oracle/oradata/orcl/ oelas.tomiasi.local=oracle=orcl-> ls -l undotbs01.dbf -rw-r----- 1 oracle dba 529539072 Jul 22 21:02 undotbs01.dbf
Conectando no banco de dados, vamos criar um usuário de teste e simular uma transação para forçar o erro (possível corrupção ou perda do datafile de UNDO).
oelas.tomiasi.local=oracle=orcl-> sqlplus ******** SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 22 21:08:28 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production SQL> create user teste identified by teste; User created. SQL> grant connect, resource to teste; Grant succeeded. SQL>
Vamos conectar com o usuário de teste e criar uma tabela e realizar uma transação (bloco PL/SQL).
SQL> conn teste/teste
Connected.
SQL> create table teste_tabela (id number);
Table created.
SQL> insert into teste_tabela values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from teste_tabela;
ID
----------
1
SQL> declare
c number;
begin
c := 2;
while c <= 1000000 loop
insert into teste_tabela values (c);
c:=c+1;
end loop;
commit;
while c >= 0 loop
insert into teste_tabela values (c);
c:=c-1;
end loop;
rollback;
end;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
Neste momento entrei em outro terminal e removi o datafile da tablespace de UNDO conforme abaixo.
oelas.tomiasi.local=oracle=orcl-> rm undotbs01.dbf oelas.tomiasi.local=oracle=orcl-> ls -l undotbs01.dbf ls: undotbs01.dbf: Arquivo ou diret▒rio n▒o encontrado
O bloco PL/SQL ainda estava em execução quando ocorreu o erro abaixo.
c := 2;
*
ERROR at line 4:
ORA-03113: end-of-file on communication channel
Process ID: 3850
Session ID: 145 Serial number: 7
Vamos analisar o alert log para verificar o momento do erro.
Tue Jul 22 21:17:21 2014 Thread 1 advanced to log sequence 176 (LGWR switch) Current log# 2 seq# 176 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log Tue Jul 22 21:17:22 2014 Archived Log entry 109 added for thread 1 sequence 175 ID 0x5206dd85 dest 1: Tue Jul 22 21:17:33 2014 Thread 1 advanced to log sequence 177 (LGWR switch) Current log# 3 seq# 177 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log Tue Jul 22 21:17:34 2014 Archived Log entry 110 added for thread 1 sequence 176 ID 0x5206dd85 dest 1: Tue Jul 22 21:17:43 2014 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ckpt_2972.trc: ORA-63999: data file suffered media failure ORA-01116: error in opening database file 3 ORA-01110: data file 3: '/u01/app/oracle/oradata/orcl/undotbs01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ckpt_2972.trc: ORA-63999: data file suffered media failure ORA-01116: error in opening database file 3 ORA-01110: data file 3: '/u01/app/oracle/oradata/orcl/undotbs01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Tue Jul 22 21:17:44 2014 System state dump requested by (instance=1, osid=2972 (CKPT)), summary=[abnormal instance termination]. System State dumped to trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_diag_2948_20140722211744.trc CKPT (ospid: 2972): terminating the instance due to error 63999 Dumping diagnostic data in directory=[cdmp_20140722211744], requested by (instance=1, osid=2972 (CKPT)), summary=[abnormal instance termination]. Instance terminated by CKPT, pid = 2972
Conforme alert log, a instância terminou pela falta do datafile 3 (Tablespace de UNDO) ocorrendo falha de media.
Se tentarmos iniciar o banco de dados neste momento ocorrerá um erro, pois não temos o datafile de UNDO.
SQL> conn / as sysdba Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2253824 bytes Variable Size 180358144 bytes Database Buffers 226492416 bytes Redo Buffers 8441856 bytes Database mounted. ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/u01/app/oracle/oradata/orcl/undotbs01.dbf'
Vamos desligar o banco de dados e criar um PFILE do SPFILE para ajustarmos o mesmo e iniciar para subir o banco de dados sem erros.
SQL> shut abort ORACLE instance shut down. SQL> create pfile='/home/oracle/initorcl.ora' from spfile; File created. SQL>
Vamos subir o banco de dados em posição nomount e alterar o parâmetro de UNDO para subir em modo MANUAL, assim o banco de dados não vai tentar utilizar segmentos de UNDO automaticamente.
SQL> startup nomount ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2253824 bytes Variable Size 180358144 bytes Database Buffers 226492416 bytes Redo Buffers 8441856 bytes SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 SQL> alter system set undo_management='MANUAL' scope=spfile; System altered. SQL>
Alterado o parâmetro, vamos desligar o banco de dados e forçar um startup novamente.
SQL> shut abort ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2253824 bytes Variable Size 180358144 bytes Database Buffers 226492416 bytes Redo Buffers 8441856 bytes Database mounted. ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/u01/app/oracle/oradata/orcl/undotbs01.dbf' SQL>
Podemos perceber que o erro continua.
O primeiro passo é remover o datafile de UNDO, mas devemos subir o banco de dados sem o parâmetro undo_tablespace, portanto vamos iniciar o banco de dados com o PFILE.
SQL> shut abort ORACLE instance shut down. SQL> ! oelas.tomiasi.local=oracle=orcl-> cat /home/oracle/initorcl.ora orcl.__db_cache_size=226492416 orcl.__java_pool_size=4194304 orcl.__large_pool_size=71303168 orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=159383552 orcl.__sga_target=419430400 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=104857600 orcl.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.job_queue_processes=10 *.log_archive_dest_1='LOCATION=/u01/oraarchive' *.log_archive_format='orcl_%t_%s_%r.arc' *.open_cursors=300 *.pga_aggregate_target=157286400 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sec_case_sensitive_logon=FALSE *.sga_target=419430400 *.undo_tablespace='UNDOTBS1'
Conforme acima, podemos perceber que no init há o parâmetro undo_tablespace. Vamos comentar o mesmo e acrescentar o undo_management para MANUAL.
oelas.tomiasi.local=oracle=orcl-> vi /home/oracle/initorcl.ora oelas.tomiasi.local=oracle=orcl-> cat /home/oracle/initorcl.ora orcl.__db_cache_size=226492416 orcl.__java_pool_size=4194304 orcl.__large_pool_size=71303168 orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=159383552 orcl.__sga_target=419430400 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=104857600 orcl.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.job_queue_processes=10 *.log_archive_dest_1='LOCATION=/u01/oraarchive' *.log_archive_format='orcl_%t_%s_%r.arc' *.open_cursors=300 *.pga_aggregate_target=157286400 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sec_case_sensitive_logon=FALSE *.sga_target=419430400 #*.undo_tablespace='UNDOTBS1' *.undo_management='MANUAL' oelas.tomiasi.local=oracle=orcl-> exit exit
Vamos iniciar o banco de dados com o init ajustado em posição mount, remover o datafile da tablespace de UNDO e abrir o banco de dados.
SQL> startup mount pfile='/home/oracle/initorcl.ora'; ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2253824 bytes Variable Size 180358144 bytes Database Buffers 226492416 bytes Redo Buffers 8441856 bytes Database mounted. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/orcl/system01.dbf /u01/app/oracle/oradata/orcl/sysaux01.dbf /u01/app/oracle/oradata/orcl/undotbs01.dbf /u01/app/oracle/oradata/orcl/users01.dbf /u01/app/oracle/oradata/orcl/gap_data01.dbf /u01/app/oracle/oradata/orcl/gap_index01.dbf /u01/app/oracle/oradata/orcl/bkpauto_data01.dbf /u01/app/oracle/oradata/orcl/bkpauto_index01.dbf 8 rows selected. SQL> alter database datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' offline drop; Database altered. SQL> alter database open; Database altered. SQL>
Após aberto, devemos verificar quais os segmentos de rollback ainda estão pendentes para recuperação.
SQL> select segment_name, tablespace_name from dba_rollback_segs where tablespace_name = 'UNDOTBS1'; SEGMENT_NAME TABLESPACE_NAME ------------------------------ ------------------------------ _SYSSMU1_2787866692$ UNDOTBS1 _SYSSMU2_813793091$ UNDOTBS1 _SYSSMU3_789065765$ UNDOTBS1 _SYSSMU4_3786318891$ UNDOTBS1 _SYSSMU5_3049255392$ UNDOTBS1 _SYSSMU6_144312568$ UNDOTBS1 _SYSSMU7_2592793290$ UNDOTBS1 _SYSSMU8_3437359293$ UNDOTBS1 _SYSSMU9_2942210132$ UNDOTBS1 _SYSSMU10_1924522192$ UNDOTBS1 10 rows selected. SQL>
Após a checagem, vamos desligar o banco de dados e ajustar o nosso PFILE ajustando o parâmetro _offline_rollback_segments para iniciar os rollbacks em destaque.
OBS: Não use este a menos instruído a fazê-lo pelo Oracle Support.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
oelas.tomiasi.local=oracle=orcl-> vi /home/oracle/initorcl.ora
oelas.tomiasi.local=oracle=orcl-> cat /home/oracle/initorcl.ora
orcl.__db_cache_size=226492416
orcl.__java_pool_size=4194304
orcl.__large_pool_size=71303168
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=159383552
orcl.__sga_target=419430400
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=104857600
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/u01/oraarchive'
*.log_archive_format='orcl_%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=157286400
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sga_target=419430400
#*.undo_tablespace='UNDOTBS1'
*.undo_management='MANUAL'
*._offline_rollback_segments=('_SYSSMU1_2787866692$','_SYSSMU2_813793091$','_SYSSMU3_789065765$','_SYSSMU4_3786318891$','_SYSSMU5_3049255392$','_SYSSMU6_144312568$','_SYSSMU7_2592793290$','_SYSSMU8_3437359293$','_SYSSMU9_2942210132$','_SYSSMU10_1924522192$')
oelas.tomiasi.local=oracle=orcl-> exit
exit
Conforme acima, podemos verificar que colocamos os segmentos para subir de modo offline para remoção dos mesmos.
Vamos subir o banco de dados e remover os segmentos de UNDO.
SQL> startup pfile='/home/oracle/initorcl.ora'; ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2253824 bytes Variable Size 180358144 bytes Database Buffers 226492416 bytes Redo Buffers 8441856 bytes Database mounted. Database opened. SQL> select 'drop rollback segment "'||segment_name||'";' from dba_rollback_segs where tablespace_name = 'UNDOTBS1'; 'DROPROLLBACKSEGMENT"'||SEGMENT_NAME||'";' ------------------------------------------------------- drop rollback segment "_SYSSMU1_2787866692$"; drop rollback segment "_SYSSMU2_813793091$"; drop rollback segment "_SYSSMU3_789065765$"; drop rollback segment "_SYSSMU4_3786318891$"; drop rollback segment "_SYSSMU5_3049255392$"; drop rollback segment "_SYSSMU6_144312568$"; drop rollback segment "_SYSSMU7_2592793290$"; drop rollback segment "_SYSSMU8_3437359293$"; drop rollback segment "_SYSSMU9_2942210132$"; drop rollback segment "_SYSSMU10_1924522192$"; 10 rows selected. SQL> drop rollback segment "_SYSSMU1_2787866692$"; drop rollback segment "_SYSSMU2_813793091$"; drop rollback segment "_SYSSMU3_789065765$"; drop rollback segment "_SYSSMU4_3786318891$"; drop rollback segment "_SYSSMU5_3049255392$"; drop rollback segment "_SYSSMU6_144312568$"; drop rollback segment "_SYSSMU7_2592793290$"; drop rollback segment "_SYSSMU8_3437359293$"; drop rollback segment "_SYSSMU9_2942210132$"; drop rollback segment "_SYSSMU10_1924522192$"; Rollback segment dropped. SQL> Rollback segment dropped. SQL> Rollback segment dropped. SQL> Rollback segment dropped. SQL> Rollback segment dropped. SQL> Rollback segment dropped. SQL> Rollback segment dropped. SQL> Rollback segment dropped. SQL> Rollback segment dropped. SQL> Rollback segment dropped. SQL>
Após a remoção do segmentos de UNDO, vamos remover a Tablespace de UNDO.
SQL> drop tablespace UNDOTBS1; Tablespace dropped. SQL>
Vamos desligar o banco de dados e iniciar o banco de dados com o SPFILE e recriar a tablespace de UNDO.
SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2253824 bytes Variable Size 180358144 bytes Database Buffers 226492416 bytes Redo Buffers 8441856 bytes Database mounted. SQL> alter database open; Database altered. SQL> create undo tablespace UNDOTBS1 datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' size 200M autoextend on next 200M maxsize 3000M; Tablespace created. SQL>
Conforme abaixo, o parâmetro undo_management está MANUAL, vamos alterá-lo para AUTO e desligar/inicializar o banco de dados.
SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string MANUAL undo_retention integer 900 undo_tablespace string UNDOTBS1 SQL> alter system set undo_management='AUTO' scope=spfile; System altered. SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2253824 bytes Variable Size 180358144 bytes Database Buffers 226492416 bytes Redo Buffers 8441856 bytes Database mounted. Database opened. SQL>
Conforme abaixo nosso banco de dados está online.
SQL> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE SQL> select status from v$instance; STATUS ------------ OPEN SQL> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS GAP_DATA GAP_INDEX BKPAUTO_DATA BKPAUTO_INDEX 9 rows selected. SQL>
Vamos checar a transação na tabela do usuário teste.
SQL> select count(*) from teste.teste_tabela;
COUNT(*)
----------
527277
SQL>
Conforme acima, houve a transação até o momento da corrupção do datafile, ou seja, houve perda de dados neste momento.
Pronto, recuperamos a tablespace de UNDO e o banco de dados está ONLINE novamente.
Mais informações sobre o assunto, acessar o Oracle Metalink e documentação da Oracle.
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.
