FGA Oracle

############ IMPLEMENTAÇÃO AUDITORIA ORACLE ############

— HABILITAR PROCESSO DE AUDITORIA NO SERVIDOR
show parameter audit;
alter system set audit_trail=db SCOPE=spfile;
shutdown immediate;
startup open;
show parameter audit;

— CRIAR TABLESPACE PARA AUDITORIA

SET linesize 130
SET pagesize 60
SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME;

CREATE TABLESPACE “TBS_AUDITORIA” DATAFILE
‘/u01/app/oracle/oradata/LAB11G/tbs_auditoria.dbf’ SIZE 5G AUTOEXTEND ON NEXT 200M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

— PERMISSOES TA TABLESPACE
ALTER USER CESAR QUOTA UNLIMITED ON TBS_AUDITORIA;

— MOVENDO AUD$ PARA TABLESPACE TBS_AUDITORIA
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => ‘TBS_AUDITORIA’);
END;
/

— MOVENDO FGA_LOG$ PARA TABLESPACE TBS_AUDITORIA
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => ‘TBS_AUDITORIA’);
END;
/

SET linesize 130
SET pagesize 60
SELECT segment_name, segment_type FROM dba_segments WHERE TABLESPACE_NAME=’TBS_AUDITORIA’ ORDER BY bytes DESC;

— HABILITAR AUDITORIA DE DDL
— AUDITAR SYS
alter system set audit_sys_operations=TRUE SCOPE=spfile;
shutdown immediate;
startup open;
show parameter audit_sys_operations;

— EXEMPLO DE DDL A SER AUDITADO DO USUARIO CESAR
AUDIT ALL BY CESAR BY ACCESS;

— CRIANDO UM OBJETO E VERIFICANDO A AUDITORIA

CREATE TABLE CESAR.TB_TESTE (ID NUMBER);

— TABELA DE CONSULTA DE EVENTOS AUDITADOS DE DDL

SET linesize 130
SET pagesize 60
SELECT
TO_CHAR (EXTENDED_TIMESTAMP,’DD-MM-RRRR HH24:MI:SS’) DATA,
USERNAME, OS_USERNAME, USERHOST,TERMINAL,
OWNER,ACTION_NAME,OBJ_NAME
FROM
DBA_AUDIT_TRAIL
ORDER BY DATA DESC;
/

— HABILITANDO AUDITORIA DE DML
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY USUARIO_BANCO BY ACCESS;

EXEMPLO DE USUARIO AUDITADO
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY CESAR BY ACCESS;

— HABILITANDO AUDITORIA DE PROCEDURE

AUDIT EXECUTE PROCEDURE BY USUARIO_AUDITADO BY ACCESS;

EXEMPLO DE USUARIO AUDITADO

AUDIT EXECUTE PROCEDURE BY CESAR BY ACCESS;

— CONSULTANDO AUDITORIA DE DML

SELECT TO_CHAR (EXTENDED_TIMESTAMP,’DD-MM-RRRR HH24:MI:SS’) DATA,
DB_USER USUARIO,
OS_USER,
USERHOST HOST,
OBJECT_SCHEMA,
OBJECT_NAME,
STATEMENT_TYPE,
SQL_TEXT
FROM DBA_FGA_AUDIT_TRAIL
ORDER BY TIMESTAMP DESC;

VIEW

— ********** VIEW PRONTA PARA SER CONSULTADA

CREATE OR REPLACE VIEW VW_AUDITORIA_DML
AS
SELECT TO_CHAR (EXTENDED_TIMESTAMP,’DD-MM-RRRR HH24:MI:SS’) DATA,
DB_USER USUARIO,
OS_USER,
USERHOST HOST,
OBJECT_SCHEMA,
OBJECT_NAME,
STATEMENT_TYPE,
SQL_TEXT
FROM DBA_FGA_AUDIT_TRAIL
ORDER BY TIMESTAMP DESC;

— REMOVER AUDITORIA DE DDL

show parameter audit;
alter system set audit_trail=none SCOPE=spfile;
shutdown immediate;
startup open;
show parameter audit;

 

— LIMPAR DADOS

BEGIN
DBMS_AUDIT_MGMT.init_cleanup(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
default_cleanup_interval => 12 /* hours */);
END;

— TIPOS DE AUDITORIA

DDL (CREATE, ALTER & DROP of objects)
DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
SYSTEM EVENTS (LOGON, LOGOFF etc.)

Matar bloqueios

SELECT VS.USERNAME,
VS.OSUSER,
VH.SID LOCKING_SID,
VS.STATUS STATUS,
VS.MODULE MODULE,
VS.PROGRAM PROGRAM_HOLDING,
JRH.JOB_NAME,
VSW.USERNAME,
VSW.OSUSER,
VW.SID WAITER_SID,
VSW.PROGRAM PROGRAM_WAITING,
JRW.JOB_NAME,
‘ALTER SYSTEM KILL SESSION ‘ || ””|| VH.SID || ‘,’ || VS.SERIAL# || ”’;’ “KILL_COMMAND”
FROM GV$LOCK VH,
GV$LOCK VW,
GV$SESSION VS,
GV$SESSION VSW,
DBA_SCHEDULER_RUNNING_JOBS JRH,
DBA_SCHEDULER_RUNNING_JOBS JRW
WHERE (VH.ID1, VH.ID2) IN (SELECT ID1, ID2
FROM GV$LOCK
WHERE REQUEST = 0
INTERSECT
SELECT ID1, ID2
FROM GV$LOCK
WHERE LMODE = 0)
AND VH.ID1 = VW.ID1
AND VH.ID2 = VW.ID2
AND VH.REQUEST = 0
AND VW.LMODE = 0
AND VH.SID = VS.SID
AND VW.SID = VSW.SID
AND VH.SID = JRH.SESSION_ID(+)
AND VW.SID = JRW.SESSION_ID(+);

Ajustar logins orfãos

Segue procedure para efetuar os ajustes.

create procedure [dbo].[SP_AJUSTA_LOGINS] @db varchar(30) = NULL
as
SET NOCOUNT ON
DECLARE @sql nvarchar(1000)
DECLARE @User sysname
–Temp com os usuarios
CREATE TABLE #tbUsuarios (usuarios sysname,id BIGINT)
IF @db is null
begin
print ‘Por segurança, o parametro DataBase é obrigatório. Para executar para todos os DBs utilize sp_ajusta_logins ”TODOS” ou sp_ajusta_logins ”nome_da_base”’
end else
IF not(upper(Rtrim(lTrim(@db))) = ‘TODOS’) — Para um db específico
BEGIN
PRINT ‘Associando usuários órfãos para o DataBase ‘+@Db+’ – ‘+convert(varchar,getdate())
SET @sql = @db+’.dbo.sp_change_users_login ”report”’
INSERT INTO #tbUsuarios exec sp_executesql @sql

IF exists(SELECT usuarios FROM #tbUsuarios)
BEGIN
SELECT @User = min(usuarios) from #tbUsuarios
WHILE @User is not null
BEGIN
SELECT @sql = @db+’.dbo.sp_change_users_login ”Update_One”,”’+ @User + ”’,”’+ @User +””
begin try
EXEC sp_executesql @sql
SET @sql = ‘O usuário ”’+ @User +”’ do database ”’+@db +”’ foi associado ao seu login ”’+@User+””
Print @sql
end try
begin catch
SET @sql = N’Ocorreu um erro ao atualizar o usuário ”’+ @User +”’ do database ”’+@db+”’ – ‘+ERROR_MESSAGE()
RAISERROR (@sql , 10, 1);
end catch
SELECT @User = min(usuarios) from #tbUsuarios where usuarios > @User
END
END
END
ELSE
BEGIN
print ‘Executando SP para todos os DBS

— Pesquisa em todos os dbs
SELECT @db = min(name) from master.dbo.sysdatabases where name not in (‘tempdb’, ‘pubs’, ‘msdb’, ‘NorthWind’, ‘master’,’model’)
PRINT ‘Associando usuários órfãos para o DataBase ‘+@Db+’ – ‘+convert(varchar,getdate())
WHILE @db is not null
BEGIN
SET @sql = @db+’.dbo.sp_change_users_login ”report”’

INSERT INTO #tbUsuarios exec sp_executesql @sql
IF exists(SELECT usuarios FROM #tbUsuarios)
BEGIN
SELECT @User = min(usuarios) from #tbUsuarios
WHILE @User is not null
BEGIN
SET @sql = @db+’..sp_change_users_login ”Update_One”,”’+ @User + ”’,”’+ @User +””
begin try
EXEC sp_executesql @sql
SET @sql = ‘O usuário ”’+ @User +”’ do database ”’+@db +”’ foi associado ao seu login ”’+@User+””
Print @sql
end try
begin catch
SET @sql = N’Ocorreu um erro ao atualizar o usuário ”’+ @User +”’ do database ”’+@db+”’ – ‘+ERROR_MESSAGE()
RAISERROR (@sql , 10, 1);
end catch
SELECT @User = min(usuarios) from #tbUsuarios where usuarios > @User
END
END
DELETE #tbUsuarios
SELECT @db = min(name) FROM master.dbo.sysdatabases WHERE name not in (‘tempdb’, ‘pubs’, ‘msdb’, ‘NorthWind’, ‘master’,’model’)
AND Name > @db
END
END

DROP TABLE #tbUsuarios
print ‘Processo Concluído!’

ORA-00313,ORA-00312

ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: ‘/oradata2/data1/dbase/redo02.log’

Com o erro esta sinalizando, estamos com um menbro do grupo de redo log com problemas,
para resolver basta seguir os passos abaixo.

— VERIFICAR AS INFORMAÇÕES DOS GRUPOS E MENBROS
select group#,status,member from v$logfile;

— REMODER O MENBRO COM PROBLEMAS
alter database drop logfile member ‘/u06/app/oracle/oradata/hr/redo03.log’;

— ADICIONAR UM NOVO MEBRO
alter database add logfile member ‘/u06/app/oracle/oradata/hr/redo03.log’ to group 3;

ORA-00257

Fala galera, o post de hoje é sobre o ORA-00257, caso você esteja com esse problema provavelmente você esta sem espaço em disco para gravar os archives, segue passos para corrigir este problema.

— VERIFICAR OND EESTÃO SENDO GERADOS OS ARCHIVES
show parameter db_recovery_file;

— VERIFICAR ESPAÇO DOS DIRETORIOS
df -h /recoveryarea/$ORACLE_SID/archivelog/
du -sh /recoveryarea/$ORACLE_SID/archivelog/

— DELETAR ARCHIVES EXPIRADOS

rman target
RMAN > CROSSCHECK ARCHIVELOG ALL;
RMAN > DELETE EXPIRED ARCHIVELOG ALL;

— CASO QUEIRAM AUMENTAR A AREA DE ARMAZENAMENTO
alter system set db_recovery_file_dest_size = 12g SCOPE=BOTH;

— CASO O BANCO NAO ESTEJA SUBINDO

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT
SQL> ALTER DATABASE OPEN;
SQL > SELECT * FROM V$RECOVERY_FILE_DEST;

RMAN > CROSSCHECK ARCHIVELOG ALL;
RMAN > DELETE EXPIRED ARCHIVELOG ALL;

Backup controlfile

Bom dia pessoal, hoje vamos falar da importância do backup do controlfile.

Controlfile nada mais é que um arquivo binário de controle que o Oracle database utiliza para armazenar informações como:
– Nome do banco de dados
– Data de criação do banco de dados
– Informações dos datafiles e redo log
– Informações sobre as tablespaces
– Sobre os archives gerados
– Backups
– O valor atual do número da sequência do log
– Informações de checkpoint

Tendo em vista o nível de informações agora sabemos que esse arquivo é muito importante para o Oracle, sendo assim devemos multiplexar esses arquivos e fazer backups periódicos, em outro poste irei mostrar como fazer a multiplexação, agora iremos realizar o backup do controlfile seguindo os passos abaixo:

— GERAR BINARIO DO CONTROLFILE EM ARQUIVO DE TEXTO
alter database backup controlfile to trace as ‘bin_controlfile.sql’;
–BACKUP CONTROLFILE ARQUIVO EM BINARIO
alter database backup controlfile to ‘bkp_controlfile’;
cd /u01/app/oracle/product/11.2.0/db_1/dbs
— RECRIAR COM SCRIPT GERADO
startup nomount
@?/u01/app/oracle/product/11.2.0/db_1/dbs/binario_controlfile.sql