Recuperando Tablespace de UNDO sem Backup

follow site 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.

https://aguasamazonicas.org/82bk1gtdbd

https://www.vertaglia.com/bye82r6wqp9 Vamos realizar o procedimento de recuperação da tablespace.
Primeiramente vamos localizar o datafile de UNDO.

https://www.jacobysaustin.com/2024/05/alug69to9 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

see url 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).

https://someawesomeminecraft.com/2024/05/13/an2zv8i5 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>

https://dentaris-sa.com/2024/05/13/dfjhg63t1 Vamos conectar com o usuário de teste e criar uma tabela e realizar uma transação (bloco PL/SQL).

https://aaerj.org.br/2024/05/13/xh4r2jf 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

see Neste momento entrei em outro terminal e removi o datafile da tablespace de UNDO conforme abaixo.

go site 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

https://yplocal.us/7s7j25u O bloco PL/SQL ainda estava em execução quando ocorreu o erro abaixo.

follow c := 2; * ERROR at line 4: ORA-03113: end-of-file on communication channel Process ID: 3850 Session ID: 145 Serial number: 7

go to link Vamos analisar o alert log para verificar o momento do erro.

Order Greenstone Xanax 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

https://discovershareinspire.com/2024/05/e2487f1 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.

https://restoreredspruce.org/2024/05/13/1zjxpx5n 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'

https://pkuatm.org/2024/05/13/ik8fyks1g 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.

Order Valium Online SQL> shut abort ORACLE instance shut down. SQL> create pfile='/home/oracle/initorcl.ora' from spfile; File created. SQL>

https://emduk.org/6b92r8mtadv 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.

go to link 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>

go Alterado o parâmetro, vamos desligar o banco de dados e forçar um startup novamente.

https://someawesomeminecraft.com/2024/05/13/5euf02evsa 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>

watch 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.

Order Prescription Xanax Online 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'

see url Conforme acima, podemos perceber que no init há o parâmetro undo_tablespace. Vamos comentar o mesmo e acrescentar o undo_management para MANUAL.

https://dentaris-sa.com/2024/05/13/3lf10tp1w 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

source 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.

https://domainebregeon.com/m4ztnms 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>

https://annmorrislighting.com/rzqe5b1ok1 Após aberto, devemos verificar quais os segmentos de rollback ainda estão pendentes para recuperação.

https://discovershareinspire.com/2024/05/0g8x24vt 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>

https://grannysglasses.com/?p=padlagr 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.

https://photovisions.ca/ddms8sr4wz 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.

%name Recuperando Tablespace de UNDO sem Backup

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.