https://www.saiidzeidan.com/k5hkw3sson2 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.
click here Vamos realizar o procedimento de recuperação da tablespace.
Primeiramente vamos localizar o datafile de UNDO.
https://www.pslra.org/l8nfhsfksy 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
source site 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).
click here 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://opponix.com/amybqksq0 Vamos conectar com o usuário de teste e criar uma tabela e realizar uma transação (bloco PL/SQL).
https://osteopatiaamparoandres.com/3ltbss0l 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
click here Neste momento entrei em outro terminal e removi o datafile da tablespace de UNDO conforme abaixo.
click here 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
Valium Online Cheapest O bloco PL/SQL ainda estava em execução quando ocorreu o erro abaixo.
https://hereisnewyorkv911.org/gsp7rskzt c := 2; * ERROR at line 4: ORA-03113: end-of-file on communication channel Process ID: 3850 Session ID: 145 Serial number: 7
follow Vamos analisar o alert log para verificar o momento do erro.
https://www.frolic-through-life.com/2025/01/l24f0jcvycg 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
go here 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://valkyrieswebzine.com/nouvel/valium-online-mastercard.php 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'
follow link 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.
Want To Buy Valium In Uk SQL> shut abort ORACLE instance shut down. SQL> create pfile='/home/oracle/initorcl.ora' from spfile; File created. SQL>
source 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.
https://www.mckenziesportsphysicaltherapy.com/33cejxnk8js 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>
https://thelowegroupltd.com/3x2zq5nqqpe Alterado o parâmetro, vamos desligar o banco de dados e forçar um startup novamente.
https://riverhillcurrent.com/xthmgcofz 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>
https://www.iql-nog.com/2025/01/19/yomir8jcv 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.
https://www.boasdeibiza.com/boat/cheap-valium-online-australia.php 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'
https://www.prestoavenuedesigns.com/save/buy-roche-diazepam-10mg.php Conforme acima, podemos perceber que no init há o parâmetro undo_tablespace. Vamos comentar o mesmo e acrescentar o undo_management para MANUAL.
go here 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
https://www.saiidzeidan.com/a299z8vy9c 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.
Purchasing Valium Online 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://bettierose.co.uk/dp0rytp Após aberto, devemos verificar quais os segmentos de rollback ainda estão pendentes para recuperação.
https://riverhillcurrent.com/kskqvmxok 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>
go to link 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.
Buy Generic Valium Online 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
watch 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.
here 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>
go to link Após a remoção do segmentos de UNDO, vamos remover a Tablespace de UNDO.
https://www.infotonicsmedia.com/about/buy-original-valium.php SQL> drop tablespace UNDOTBS1; Tablespace dropped. SQL>
https://www.amyglaze.com/m5kyt4va Vamos desligar o banco de dados e iniciar o banco de dados com o SPFILE e recriar a tablespace de UNDO.
http://foodsafetytrainingcertification.com/food-safety-news/d2rcd6n7m 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>
source link Conforme abaixo, o parâmetro undo_management está MANUAL, vamos alterá-lo para AUTO e desligar/inicializar o banco de dados.
https://www.accessoriesresourceteam.org/art/buy-liquid-diazepam.php 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>
https://hereisnewyorkv911.org/dto7mqj3 Conforme abaixo nosso banco de dados está online.
https://tvnordestevip.com/1so2re2w5u1 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>
click Vamos checar a transação na tabela do usuário teste.
get link SQL> select count(*) from teste.teste_tabela; COUNT(*) ---------- 527277 SQL>
https://osteopatiaamparoandres.com/kz9p3rgvxfd 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
https://www.frolic-through-life.com/2025/01/tu1onqgsn
Buy Valium Sleeping Tablets 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.
