Roberto Sobrinho

17/05/2024

Como Resolver a Perda Definitiva da Tablespace de UNDO no Oracle Database

Olá “abigos” e “abigas“, hoje vou comentar a respeito dos procedimentos para recuperar um banco de dados Oracle que perdeu a sua área de UNDO de forma abrupta. A tablespace de UNDO é super importante no banco de dados Oracle, nela ficam armazenadas informações que permitem executar rollback das transações e garantir que tudo funcione perfeitamente. Perder essa tablespace pode causar muitos problemas, inclusive a perda de transações alem da indisponibilidade do ambiente. Mas não se preocupe! Vou te mostrar como resolver isso passo a passo, começando com uma simulação do erro.

🚀🚀🚀 Vamos lá! Bó Bó 🚀🚀🚀

O Que é a Tablespace de UNDO?

A tablespace de UNDO guarda informações necessárias para realizar rollback das transações, ler dados de forma consistente e recuperar o banco de dados após uma falha não esperada.

Simulando a Geração de Dados na Tablespace de UNDO e a Perda da Mesma

Vamos primeiro gerar dados na tablespace de UNDO por meio de uma transação. Atenção: Não faça isso em um ambiente de produção. É só para fins educativos.

Passo 1: Criar uma Tabela de Exemplo

Crie uma tabela de exemplo para usar na transação:

CREATE TABLE teste_undo (
    id NUMBER,
    descricao VARCHAR2(100)
);

Table created.

Elapsed: 00:00:00.01

Passo 2: Iniciar uma Transação e Inserir Dados

Inicie uma transação e insira alguns dados:

BEGIN
    FOR i IN 1..100000 LOOP
        INSERT INTO teste_undo VALUES (i, 'Cavalo de Fogo '|| i);
    END LOOP;
    -->> Não vamos emitir commit ou rollback para a transação ficar ativa na UNDO
END;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05

Passo 3: Identificar o Datafile da Tablespace de UNDO

Identifique o datafile associado à tablespace de UNDO:

SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'UNDOTBS1';

FILE_NAME
---------------------------------------------
/u01/app/oracle/oradata/XE/undotbs1.dbf

Elapsed: 00:00:00.01

Passo 4: Remover o Datafile da UNDO

No utilitario asmcmd, remova o datafile da tablespace de UNDO.

cat ~/.bash_profile > /u01/app/oracle/oradata/XE/undotbs1.dbf

Passo 5: Tentar Realizar Operações no Banco de Dados

Agora, tente realizar algumas operações no banco de dados. Você verá erros e pode verificar o alert log para detalhes.

Tentativa de Operação DML

Vamos tentar inserir mais dados na tabela:

INSERT INTO teste_undo VALUES (171, 'Guina não tinha dó!');
COMMIT;
DELETE teste_undo;

Erro esperado:

Commit complete.

Elapsed: 00:00:00.01
sys@XE > delete teste_undo;
delete teste_undo
*
ERRO na linha 1:
ORA-03113: end-of-file on communication channel
Process ID: 3351
Session ID: 31 Serial number: 53

Elapsed: 00:00:03.89

Resolvendo a Perda da Tablespace de UNDO

Se houver transações pendentes na tablespace de UNDO, é importante saber que essas transações podem ser perdidas. Seguir os passos abaixo ajudará a minimizar os impactos e restaurar o funcionamento normal do banco de dados.

1. Parar o Banco de Dados

SHUTDOWN ABORT;

ORACLE instance shut down.

2. Iniciar o Banco de Dados em MOUNT

STARTUP;

ORACLE instance started.

Total System Global Area  726540288 bytes
Fixed Size                  2230048 bytes
Variable Size             461375712 bytes
Database Buffers          260046848 bytes
Redo Buffers                2887680 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/XE/undotbs1.dbf'

3. Remover o Datafile 3 da Tablespace de UNDO

ALTER DATABASE DATAFILE 3 OFFLINE DROP;

Database altered.

4. Criar uma Nova Tablespace de UNDO

ALTER DATABASE OPEN;

CREATE UNDO TABLESPACE undotbs_BUM DATAFILE 'undotbs02_BUM.dbf' SIZE 1G REUSE AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;

Database altered.

Tablespace created.

5. Configurar o Banco de Dados para Usar a Nova Tablespace de UNDO

Diga ao banco de dados para usar a nova tablespace de UNDO:

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_BUM scope=both sid='*';

System altered.

6. Abrir o Banco de Dados

STARTUP FORCE;

ORACLE instance started.

Total System Global Area  726540288 bytes
Fixed Size                  2230048 bytes
Variable Size             461375712 bytes
Database Buffers          260046848 bytes
Redo Buffers                2887680 bytes
Database mounted.
Database opened.

07. Apagar a Tablespace de UNDO Antiga

Se a antiga tablespace de UNDO estiver corrompida ou inacessível, tente apagá-la:

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_3789641169$' found, terminate dropping tablespace

Não é possivel apaga-la porque existem segmentos não finalizados.

08. Removendo Segmentos não Finalizados:

Identificando os Segmentos:

SELECT SEGMENT_NAME,STATUS,TABLESPACE_NAME FROM DBA_ROLLBACK_SEGS;

SEGMENT_NAME           STATUS           TABLESPACE_NAME
---------------------- ---------------- ------------------
SYSTEM                 ONLINE           SYSTEM
_SYSSMU10_2569484742$  NEEDS RECOVERY   UNDOTBS1
_SYSSMU9_1317495879$   NEEDS RECOVERY   UNDOTBS1
_SYSSMU8_4175076471$   NEEDS RECOVERY   UNDOTBS1
_SYSSMU7_3583332791$   NEEDS RECOVERY   UNDOTBS1
_SYSSMU6_725569783$    NEEDS RECOVERY   UNDOTBS1
_SYSSMU5_3703078872$   NEEDS RECOVERY   UNDOTBS1
_SYSSMU4_655887589$    NEEDS RECOVERY   UNDOTBS1
_SYSSMU3_3346129149$   NEEDS RECOVERY   UNDOTBS1
_SYSSMU2_1827203912$   NEEDS RECOVERY   UNDOTBS1
_SYSSMU1_3789641169$   NEEDS RECOVERY   UNDOTBS1
_SYSSMU20_2313067695$  ONLINE           UNDOTBS_BUM
_SYSSMU19_4278715212$  ONLINE           UNDOTBS_BUM
_SYSSMU18_2738302769$  ONLINE           UNDOTBS_BUM
_SYSSMU17_2231566987$  ONLINE           UNDOTBS_BUM
_SYSSMU16_3932376258$  ONLINE           UNDOTBS_BUM
_SYSSMU15_2230693899$  ONLINE           UNDOTBS_BUM
_SYSSMU14_555568742$   ONLINE           UNDOTBS_BUM
_SYSSMU13_82682313$    ONLINE           UNDOTBS_BUM
_SYSSMU12_2270422982$  ONLINE           UNDOTBS_BUM
_SYSSMU11_1026804264$  ONLINE           UNDOTBS_BUM

21 rows selected.

Os segmentos da UNDOTBS1 devem ser excluídos.

Para isso, criar um pfile temporário para identificar os segmentos que devem ser excluídos.

create pfile='/tmp/ppfile.ora' from spfile;

File created.

Indicar no pfile temporário os segmentos que serão excluídos:

vi /tmp/ppfile.ora

##Adicionar conteudo no arquivo:

XE._offline_rollback_segments=('_SYSSMU10_2569484742$','_SYSSMU9_1317495879$', '_SYSSMU8_4175076471$', '_SYSSMU7_3583332791$', '_SYSSMU6_725569783$', '_SYSSMU5_3703078872$', '_SYSSMU4_655887589$', '_SYSSMU3_3346129149$','_SYSSMU2_1827203912$','_SYSSMU1_3789641169$') 

cat /tmp/ppfile.ora

XE._offline_rollback_segments=('_SYSSMU10_2569484742$','_SYSSMU9_1317495879$', '_SYSSMU8_4175076471$', '_SYSSMU7_3583332791$', '_SYSSMU6_725569783$', '_SYSSMU5_3703078872$', '_SYSSMU4_655887589$', '_SYSSMU3_3346129149$','_SYSSMU2_1827203912$','_SYSSMU1_3789641169$')
*.audit_file_dest='/u01/app/oracle/admin/XE/adump'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/XE/control.dbf'
*.db_name='XE'
*.DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area'
*.DB_RECOVERY_FILE_DEST_SIZE=10G
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XEXDB)'
*.job_queue_processes=4
*.memory_target=729808896
*.open_cursors=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=20
*.shared_servers=4
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS_BUM'

Iniciar a instancia com o pfile temporário:

SHUTDOWN ABORT;
STARTUP PFILE='/tmp/ppfile.ora';

ORACLE instance shut down.

ORACLE instance started.

Total System Global Area  726540288 bytes
Fixed Size                  2230048 bytes
Variable Size             432015584 bytes
Database Buffers          289406976 bytes
Redo Buffers                2887680 bytes
Database mounted.
Database opened.

Para isso, criar um pfile temporário para identificar os segmentos que devem ser excluídos.

SELECT SEGMENT_NAME,STATUS,TABLESPACE_NAME 
 FROM DBA_ROLLBACK_SEGS WHERE TABLESPACE_NAME = 'UNDOTBS1';
 
drop rollback segment "_SYSSMU10_2569484742$"; 
drop rollback segment "_SYSSMU9_1317495879$" ; 
drop rollback segment "_SYSSMU8_4175076471$" ; 
drop rollback segment "_SYSSMU7_3583332791$" ; 
drop rollback segment "_SYSSMU6_725569783$"  ; 
drop rollback segment "_SYSSMU5_3703078872$" ; 
drop rollback segment "_SYSSMU4_655887589$"  ; 
drop rollback segment "_SYSSMU3_3346129149$" ; 
drop rollback segment "_SYSSMU2_1827203912$" ; 
drop rollback segment "_SYSSMU1_3789641169$" ;

SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU1_3789641169$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU2_1827203912$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU3_3346129149$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU4_655887589$            NEEDS RECOVERY   UNDOTBS1
_SYSSMU5_3703078872$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU6_725569783$            NEEDS RECOVERY   UNDOTBS1
_SYSSMU7_3583332791$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU8_4175076471$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU9_1317495879$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU10_2569484742$          NEEDS RECOVERY   UNDOTBS1

Rollback segment dropped.

Rollback segment dropped.

Rollback segment dropped.

Rollback segment dropped.

Rollback segment dropped.

Rollback segment dropped.

Rollback segment dropped.

Rollback segment dropped.

Rollback segment dropped.

Rollback segment dropped.

Então pode seguir com a exclusão da tablespace de UNDO:

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped

Feito! E voilà, a tablespace foi removida com sucesso!

Agora então, basta reniciar a intancia para usar o spfile default.

STARTUP FORCE;

ORACLE instance started.

Total System Global Area  726540288 bytes
Fixed Size                  2230048 bytes
Variable Size             461375712 bytes
Database Buffers          260046848 bytes
Redo Buffers                2887680 bytes
Database mounted.
Database opened.

08. Verificar se a Nova Tablespace de UNDO

Finalmente, veja se a nova tablespace de UNDO está funcionando perfeitamente:

SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE CONTENTS = 'UNDO';

TABLESPACE_NAME                STATUS
------------------------------ ---------
UNDOTBS_BUM                    ONLINE

Conclusão

Lidar com a perda da tablespace de UNDO no Oracle Database pode ser desafiador, especialmente se houver transações pendentes. No entanto, ao seguir os passos detalhados neste guia, é possível recuperar o banco de dados e minimizar os impactos causados pela falha.

É essencial lembrar que a tablespace de UNDO desempenha um papel crítico na garantia da integridade dos dados e na capacidade de desfazer transações. Portanto, a perda dessa tablespace pode levar à corrupção de dados e à perda de transações não confirmadas.

Além disso, a realização de backups regulares do banco de dados é fundamental para proteger contra situações como essa. Um backup recente pode ser usado para restaurar o banco de dados para um estado consistente, mesmo em casos de perda de tablespaces críticas.

Portanto, sempre mantenha uma estratégia de backup robusta e pratique ações proativas para evitar a perda de dados e garantir a disponibilidade contínua do seu banco de dados Oracle.

🚀🚀🚀 Cacete de Agulha! 💉💉💉

Compartilhe

Pesquisar

Roberto Sobrinho

Sou Roberto Fernandes Sobrinho, também conhecido como Sobrinho DBA , pós graduado em “Architecture and Database Administration”, entusiasta, dedicado e com 20 anos de experiência com Oracle Database e suas diversas distribuições e variações.