SQLSERVER - Base Suspect

RECUPERAR BASE DE DADOS EM SUSPECT SQLSERVER

1. PARAR O SERVIÇO DO SQL;

2. FAZER UMA CÓPIA DA PASTA ONDE ESTÁ ARMAZENADO OS ARQUIVOS MDF E LDF ;
(C:\Program Files\Microsoft SQL Server\MSSQL*.SQLEXPRESS\MSSQL\DATA)

3. EXCLUIR OS ARQUIVOS MDF E LDF DESTA PASTA;

4. INICIAR O SERVIÇO DO SQL;

5. EXCLUIR O BANCO DE DADOS;

OBS: TENTEI EXCLUIR DIRETAMENTE O BANCO QUE ESTAVA EM SUPECT MAS NÃO EXCLUÍA, ENTÃO POR ISSO ELIMINEI O MDF E LDF PRIMEIRO E DEPOIS O BANCO.

6. RESTAURAR UM BACKUP DO BANCO ANTIGO COM O MESMO NOME QUE ESTAVA ANTERIORMENTE E NO MESMO CAMINHO PARA OS ARQUIVOS MDF E LDF;

7. PARAR O SERVIÇO DO SQL NOVAMENTE;

8. EXCLUIR OS ARQUIVOS MDF E LDF DESTA PASTA E SUBSTITUÍ-LOS PELOS 2 ARQUIVOS MAIS RECENTES (OS QUE ESTAVAM EM SUSPECT);

9. INICIAR O SERVIÇO DO SQL;

----EXECUTAR OS SCRIPTS ABAIXO---- 

10. ALTER DATABASE BANCO SET EMERGENCY

11. ALTER DATABASE BANCO SET SINGLE_USER

12. DBCC CHECKDB (BANCO, repair_allow_data_loss ) WITH ALL_ERRORMSGS

13. ALTER DATABASE BANCO SET read_write

14. ALTER DATABASE BANCO SET multi_user

Read More

ORACLE - Swiss penknife QUERY

Swiss penknife QUERY

SHOW AND KILL USER CONNECTED 

SQL> alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where username='TESTE';

SHOW SIZE BY DATAFILE

SQL> select file_id, block_id, blocks*8192/1024 MB,

owner || '.' || segment_name "Name", block_id*8192/1024 "Position MB"

from  sys.dba_Extents

where file_id = &&fileid

union

select file_id, block_id, blocks*8192/1024, 'Free' "Name", block_id*8192/1024 "P

osition MB"

from sys.dba_free_space

where file_id = &fileid

order by 1,2,3

/

SHOW INFO BY OBJECTS

SQL> select owner, object_name, object_type, status from all_objects where object_name='TSILGT';

OWNER       OBJECT_NAME       OBJECT_TYPE   STATUS

------------------------------ ------------------------------ ------------------- -------

TREINA       STP_GRAVATABLOG       PROCEDURE   VALID

SANKHYA        STP_GRAVATABLOG       PROCEDURE   VALID

WMS       STP_GRAVATABLOG       PROCEDURE   VALID

TESTE       STP_GRAVATABLOG       PROCEDURE   INVALID

SQL> alter procedure teste.STP_GRAVATABLOG compile;

Warning: Procedure altered with compilation errors.

SQL> show error

Errors for PROCEDURE TESTE.STP_GRAVATABLOG:

LINE/COL ERROR

-------- -----------------------------------------------------------------

33/5 PL/SQL: SQL Statement ignored

46/9 PL/SQL: ORA-00942: table or view does not exist

SQL> SELECT text FROM all_source WHERE name = 'STP_GRAVATABLOG'  and owner='TESTE' ORDER BY line;

SHOW SIZE BY TABLE OR SEGMENTS

SQL>select sum(bytes/1024/1024) as Tamanho_MB from dba_segments

where owner = 'SVP'

and segment_type = 'TABLE'

and segment_name in ('TABLE_1','TABLE_2','TABLE_N')

SQL>select onwer, segment_name, segment_type, tablespace_name, bytes/1024/1024 

from dba_segments where segment_name='TABLE_NAME';

SHOW DATAFILES INFO GENERAL/STATUS 

SQL> select tablespace_name, file_name, bytes/1024/1024 mbytes, AUTOEXTENSIBLE, MAXBYTES/1024/1024 maxbytes, INCREMENT_BY/1024/1024 nextmb from dba_data_files;

TABLESPACE FILE_NAME MBYTES AUT   MAXBYTES   NEXTMB

---------- ---------------------------------------- ---------- --- ---------- ----------

USERS   /u01/app/oracle/oradata/XE/users.dbf    100 YES 11264 .001220703

SYSAUX   /u01/app/oracle/oradata/XE/sysaux.dbf   710 YES 32767.9844 .001220703

UNDOTBS1   /u01/app/oracle/oradata/XE/undotbs1.dbf     75 YES 32767.9844 .000610352

SYSTEM   /u01/app/oracle/oradata/XE/system.dbf   360 YES   600 .001220703

SHOW ALL INX / REBUILD

SQL>select 'alter index ' || owner || '.' || index_name ||' rebuild;'
from dba_indexes
where status ='UNUSABLE'
order by index_name
/

in construction ... 

Read More