CREATE OR REPLACE DIRECTORY JMT as '/d05/teste'; -- Dar privilegio de Leitura e Gravacao para o Diretorio.GRANT READ ON DIRECTORY jmt to public; GRANT WRITE ON DIRECTORY jmt to public; SELECT substr(a.USERNAME,1,15) USUARIO, substr(a.STATUS,1,12) STATUS, substr(a.osuser,1,15) OSUSER, substr(a.MACHINE,1,20) MAQUINA, substr(a.MODULE,1,20) PROGRAMA, 'kill -9 '||b.SPID||'' "PROC DO S.O.", 'alter system kill session '||CHR(39)||''||A.SID||','||A.SERIAL#||''||CHR(39)||';' "SCRIPT PARA MATAR PROCESSO"from v$session a,v$process bwhere b.ADDR = a.paddrand a.username like upper('%&USUARIO%')order by 1; fdisk -lcriar a particao no HD novo com o "fdisk"-----------------------------------------------------------------depois formatarmke2fs -j /dev/sdc1 -L /d03 (formata e cria a Label)-----------------------------------------------------------------formatar sem criar labelmke2fs -j /dev/sdc1-----------------------------------------------------------------entrar no /etc/fstab e colocar a linhaLABEL=/d03 /d03 ext3 defaults 1 2 SELECT segment_name , segment_type , owner , tablespace_name FROM sys.dba_extents WHERE file_id = &bad_file_id AND &bad_block_id BETWEEN block_id and block_id + blocks -1;Enter value for bad_file_id: 17 (numero do datafile)Enter value for bad_block_id: 251462 (bloco corrompido)##########################################################PROCEDIMENTO PARA ISOLAR OS BLOCOS CORROMPIDOS DA TABELA.####################################################################################################################Cria a tabela REPAIR_TABLE##########################################################exec sys.dbms_repair.admin_tables('REPAIR_TABLE',sys.DBMS_REPAIR.REPAIR_TABLE,sys.DBMS_REPAIR.CREATE_ACTION,'com');##########################################################Verifica e popula a tabela dba_repair_table##########################################################SET SERVEROUTPUT ON DECLARE num_corrupt INT; BEGIN num_corrupt := 0; DBMS_REPAIR.CHECK_OBJECT ( SCHEMA_NAME => 'PRODUCAO', OBJECT_NAME => 'COM_BASE_FAT_AUXILIAR_V04_BKP', REPAIR_TABLE_NAME => 'REPAIR_TABLE', CORRUPT_COUNT => num_corrupt); DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt)); END; / ##########################################################Marca os blocos Danificados##########################################################SET SERVEROUTPUT ON DECLARE num_corrupt INT; BEGIN num_corrupt := 0; DBMS_REPAIR.FIX_CORRUPT_BLOCKS ( SCHEMA_NAME => 'PRODUCAO', OBJECT_NAME => 'COM_BASE_FAT_AUXILIAR_V04_BKP', FIX_COUNT => num_corrupt); DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt)); END; / ##########################################################Ativar a opcao para ignorar blocos danificados:##########################################################exec dbms_repair.skip_corrupt_blocks('PRODUCAO','COM_BASE_FAT_AUXILIAR_V04_BKP'); - Alterar o init.ora e modificar o nome da base, controlfile, archive, global_name.- Criar a estrutura de diretorio para a nova base.- Fechar a base de Producao e copiar todos os datafiles.- Apagar os redologs da nova base.- Apagar os controlfiles da nova base.STARTUP NOMOUNTCREATE CONTROLFILE SET DATABASE "XRTH" RESETLOGSNOARCHIVELOGMAXDATAFILES 240 logfile group 1 ('/d01/oradata/xrth/re1xrth.log', '/d01/oradata/xrth/re1xrthc.log') size 30m, group 2 ('/d01/oradata/xrth/re2xrth.log', '/d01/oradata/xrth/re2xrthc.log') size 30m, group 3 ('/d01/oradata/xrth/re3xrth.log', '/d01/oradata/xrth/re3xrthc.log') size 30mDATAFILE'/d01/oradata/xrth/system.dbf','/d01/oradata/xrth/rbs_undo01.dbf','/d01/oradata/xrth/sysaux.dbf','/d01/oradata/xrth/xrt_dados.dbf','/d01/oradata/xrth/rman01.dbf','/d01/oradata/xrth/xrt_idx.dbf','/d01/oradata/xrth/xrt_lob.dbf' REUSECHARACTER SET WE8ISO8859P1;ALTER DATABASE OPEN RESETLOGS;- Recriar a tablespace temp.create tablespace temp01 temporarydatafile '/d01/oradata/xrth/temp0101.dbf' size 10M autoextend on maxsize 2000m EXTENT MANAGEMENT DICTIONARY;ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp01;drop tablespace temp including contents and datafiles;create tablespace temp temporarydatafile '/d01/oradata/xrth/temp01.dbf' size 10M autoextend on maxsize 2000m EXTENT MANAGEMENT DICTIONARY;ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;drop tablespace temp01 including contents and datafiles; ORA-01591--------DISTRIBUTED TRANSACTION--------SELECT LOCAL_TRAN_ID FROM DBA_2PC_PENDING;ROLLBACK FORCE '19.94.11922';delete from sys.pending_trans$ where local_tran_id = '163.26.353462';delete from sys.pending_sessions$ where local_tran_id = '163.26.353462';delete from sys.pending_sub_sessions$ where local_tran_id = '163.26.353462'; commit;alter system disable distributed recovery;insert into pending_trans$ (LOCAL_TRAN_ID,GLOBAL_TRAN_FMT,GLOBAL_ORACLE_ID,STATE,STATUS,SESSION_VECTOR,RECO_VECTOR,TYPE#,FAIL_TIME,RECO_TIME)values( '163.26.353462', /* <== Replace this with your local tran id */306206, /* */'XXXXXXX.12345.1.2.3', /* These values can be used without any */'prepared','P', /* modification. Most of the values are */hextoraw( '00000001' ), /* constant. */hextoraw( '00000000' ), /* */0, sysdate, sysdate );insert into pending_sessions$ values( '163.26.353462',/* <==Replace only this with your local tran id */1, hextoraw('05004F003A1500000104'), 'C', 0, 30258592, '', 146);commit;commit force '163.26.353462';alter system enable distributed recovery; imp username/password file=exp_campo.dmp indexfile=script.sql full=y rows=n editar o arquivo script.sql com o "vi"esc :1,$ g/REM /s/REM //g 1,$ g/INITIAL *$/j 1,$ g/INITIAL *[0-9]* /s/INITIAL *[0-9]* /INITIAL 100K /g 1,$ g/NEXT *$/j 1,$ g/NEXT *[0-9]* /s/NEXT *[0-9]* /NEXT 100K /g 1,$ g/PCTINCREASE *$/j 1,$ g/PCTINCREASE *[0-9]* /s/PCTINCREASE *[0-9]* /PCTINCREASE 0 /g SELECT USERNAME, SID, SERIAL#, STATUS, OSUSER, PROGRAM, MACHINE