Technological recipes that I've held to prepare some solutions DBA environment. (by Felipe Donoso Bastias)

29 Oct 2010

Respaldar o visualizar privilegios sobre objetos en SQL Server

¿Se han preguntado como en SQL Server yo puedo averiguar que privilegios existen en mi base de datos y que para que usuarios están siendo estos otorgados? Bueno, existe un método para averiguar esto, que también a su vez puede servir como método de Backup & Recovery. Lo he encontrado desde el el sitio web http://www.mssqltips.com.

Que un DBA posea a su alcance información de esta naturaleza puede ser de gran utilidad frente a determinadas circunstancias. En Oracle sabemos que existen vistas que entregan esta información como DBA_PROFILES, DBA_ROLES,etc. ¿Pero que hay para SQL Server? La Receta mágica es:


El procedimiento almacenado: sp_helprotect.

Este procedimiento almacenado entrega información de privilegios asignados sobre objetos de base de datos. He aquí un ejemplo de la información que entrega:

(Naturalmente ocupamos el usuario SA)

EXEC master.dbo.sp_helprotect;



Aún así para obtener un script más automatizado que nos entregue un procedimiento con todas las sentencias grant de nuestra base de datos, podemos utilizar las siguientes funciones para obtener un listado de comandos listos para usar:

-- Temporary table to hold results from sp_helprotect

drop table #TempPerms

go

CREATE TABLE #TempPerms (

[Owner] sysname NULL,

[Object] sysname NULL,

[Grantee] sysname NULL,

[Grantor] sysname NULL,

[ProtectType] NVARCHAR(9) NULL,

[Action] NVARCHAR(100) NULL,

[Column] NVARCHAR(300) NULL,

[WithGrant] VARCHAR(18) NULL DEFAULT ''

);

-- Let sp_helprotect do the work for us

INSERT INTO #TempPerms

([Owner], [Object], [Grantee], [Grantor], [ProtectType], [Action], [Column])

EXEC master.dbo.sp_helprotect;


-- actualizamos por si acaso la columna ProtectType

-- en el caso de existieran valores 'Grant_WGO'

-- lo que quiere decir que se otorgaron privilegios

-- con permisos de otorgamiento hacia otros usuarios

UPDATE #TempPerms

SET [ProtectType] = 'Grant ', [WithGrant] = ' WITH GRANT OPTION'

WHERE [ProtectType] = 'Grant_WGO';


-- Build and output the permissions

SELECT

CASE [Owner]

WHEN '.' THEN ProtectType + ' ' + [Action] + ' TO [' + [Grantee] + ']' + [WithGrant] + ';'

ELSE CASE [Column]

WHEN '(All+New)' THEN ProtectType + ' ' + [Action] + ' ON [' + [Owner] + '].[' + [Object] + '] TO [' + Grantee + ']' + [WithGrant] + ';'

WHEN '(All)' THEN ProtectType + ' ' + [Action] + ' ON [' + [Owner] + '].[' + [Object] + '] TO [' + Grantee + ']' + [WithGrant] + ';'

WHEN '.' THEN ProtectType + ' ' + [Action] + ' ON [' + [Owner] + '].[' + [Object] + '] TO [' + Grantee + ']' + [WithGrant] + ';'

ELSE ProtectType + ' ' + [Action] + ' ON [' + [Owner] + '].[' + [Object] + '] ([' + [Column] + ']) TO [' + Grantee + ']' + [WithGrant] + ';'

END

END AS 'Permissions'

FROM #TempPerms;

Al ejecutar la consulta verán que el resultado será el siguiente. (les sugiero presionar [CTRL+T] para obtener los resultados en modo texto más rápido.

Grant Execute ON [dbo].[fn_MSgensqescstr] TO [public];

Grant Execute ON [dbo].[fn_MSsharedversion] TO [public];

Grant Execute ON [dbo].[fn_sqlvarbasetostr] TO [public];

Grant Execute ON [dbo].[fn_varbintohexstr] TO [public];

Grant Execute ON [dbo].[fn_varbintohexsubstring] TO [public];

Grant Execute ON [dbo].[MS_sqlctrs_users] TO [public];

Esto funciona a la perfección en SQLServer2000 hacia atrás. Ahora para SQL Server 2005 y superior se tiene que realizar una consulta sobre las tablas sys.database_permissions, sys.database_principals y sys.objects. La query quedaría mas o menos así:

SELECT CASE dperms.state_desc

WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT'

ELSE state_desc

END

+ ' ' + permission_name + ' ON ' +

CASE dperms.class

WHEN 0 THEN 'DATABASE::[' + DB_NAME() + ']'

WHEN 1 THEN

CASE dperms.minor_id

WHEN 0 THEN 'OBJECT::[' + sch.[name] + '].[' + obj.[name] + ']'

ELSE 'OBJECT::[' + sch.[name] + '].[' + obj.[name] + '] ([' + col.[name] + '])'

END

WHEN 3 THEN 'SCHEMA::[' + SCHEMA_NAME(major_id) + ']'

WHEN 4 THEN 'USER::[' + USER_NAME(major_id) + ']'

WHEN 24 THEN 'SYMMETRIC KEY::[' + symm.[name] + ']'

WHEN 25 THEN 'CERTIFICATE::[' + certs.[name] + ']'

WHEN 26 THEN 'ASYMMETRIC KEY::[' + asymm.[name] +']'

END

+ ' TO [' + dprins.[name] + ']' +

CASE dperms.state_desc

WHEN 'GRANT_WITH_GRANT_OPTION' THEN ' WITH GRANT OPTION;'

ELSE ';'

END COLLATE database_default AS 'Permissions'

FROM sys.database_permissions dperms

INNER JOIN sys.database_principals dprins

ON dperms.grantee_principal_id = dprins.principal_id

LEFT JOIN sys.columns col

ON dperms.major_id = col.object_id AND dperms.minor_id = col.column_id

LEFT JOIN sys.objects obj

ON dperms.major_id = obj.object_id

LEFT JOIN sys.schemas sch

ON obj.schema_id = sch.schema_id

LEFT JOIN sys.asymmetric_keys asymm

ON dperms.major_id = asymm.asymmetric_key_id

LEFT JOIN sys.symmetric_keys symm

ON dperms.major_id = symm.symmetric_key_id

LEFT JOIN sys.certificates certs

ON dperms.major_id = certs.certificate_id

WHERE dperms.type <> 'CO'

AND dperms.major_id > 0;


Eso es todo.

Aquí la fuente origina la Fuente Original:

http://www.mssqltips.com/tip.asp?tip=2142

Share:

Como optimizar consultas en Oracle con el parámetro optimizer_index_cost_adj

Es muy común que los DBAs traten dentro de los ambientes OLTP evitar este tipo de accesos a los datos. Sabemos que este acceso es lento para tablas grandes, pero también sabemos que es muy usado en tablas pequeñas. Veamos como podemos sacar provecho de la eliminación o uso del no muy bien ponderado full table scan.

Vamos a empezar a ver algún ejemplo práctico: Vamos a crear una tabla mas o menos masiva a partir de otra ya existente:

[oracle@antares ~]$ sqlplus “/as sysdba”

SQL*Plus: Release 9.2.0.8.0 – Production on Tue Mar 3 17:06:31 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Release 9.2.0.8.0 – Production
JServer Release 9.2.0.8.0 – Production

SQL> create table tabla_test as select * from obj$;

Table created.

SQL> select count(*) from tabla_test;

COUNT(*)
———-
66084

SQL>

Una vez que tengamos creada nuestra tabla, vamos a realizar una simple consulta, para ver el tipo de acceso que tenemos (Obviamente tendremos un full table scan en las tablas de este tipo de volumen):

SQL> set autotrace traceonly
SQL> select * from tabla_test where owner#=1;

2834 rows selected.
Execution Plan
———————————————————-
Plan hash value: 1026990376

——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 24608 | 30M| 174 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TABLA_TEST | 24608 | 30M| 174 (3)| 00:00:03 |
——————————————————————————–

Statistics
———————————————————-
0 recursive calls
0 db block gets
2212 consistent gets
0 physical reads
0 redo size
1093152 bytes sent via SQL*Net to client
16470 bytes received via SQL*Net from client
1457 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2834 rows processed

SQL>

Ahora bien, un buen administrador de base de datos diría que para eliminar el full table scan es necesario crear un índice. Veamos los resultados luego de indexar (sin olvidar de actualizar estadística):

SQL> create index index_1_test on tabla_test(owner#);

Index created.

SQL> EXEC DBMS_STATS.gather_index_stats(‘sys’, ‘index_1_test’, estimate_percent => 100);

PL/SQL procedure successfully completed.

SQL> select * from tabla_test where owner#=1;

21834 rows selected.
Execution Plan
———————————————————-
Plan hash value: 1026990376

——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 24608 | 30M| 174 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TABLA_TEST | 24608 | 30M| 174 (3)| 00:00:03 |
——————————————————————————–

Statistics
———————————————————-
0 recursive calls
0 db block gets
2319 consistent gets
0 physical reads
0 redo size
1093152 bytes sent via SQL*Net to client
16470 bytes received via SQL*Net from client
1457 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
2834 rows processed

SQL>

¿Que pasó? ¡Aún después de haber creado un índice y actualizar estadísticas se sigue accediendo a la tabla por full table scan!. Bueno esto puede suceder a causa de decisiones que toma el CBO, estamos leyendo una gran cantidad de datos de la tabla, el CBO piensa según los datos que él posee que es mucho mejor para la base de datos acceder a la tabla completa que utilizar un índice. ¿Pero será tan así esto?

Para asegurarnos que las consultas que hagamos utilicen realmente los índices existe un parámetro de base de datos que se llama optimizer_index_cost_adj. Con ayuda de este parámetro podemos hacer que las decisiones de acceso del optimizador hacia los datos favorezcan el uso de índices antes que del uso de FTS. Oracle recomienda una formula para asignarle un valor a este parámetro (por defecto está en 100):

optimizer_index_cost_adj = Costo FTS de la consulta/Costo con hint usando el índice*100

Ya conocemos cual es el valor del costo por FTS, 174. Ahora averiguaremos el valor del costo utilizando como ayuda el hint del índice que creamos:

SQL> select /*+ index(tabla_test index_1_test) */ * from tabla_test where owner#=1;

2834 rows selected.
Execution Plan
———————————————————-
Plan hash value: 1582054771

——————————————————————————–
————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |

——————————————————————————–
————

| 0 | SELECT STATEMENT | | 24608 | 30M| 499 (1)
| 00:00:06 |

| 1 | TABLE ACCESS BY INDEX ROWID| TABLA_TEST | 24608 | 30M| 499 (1)
| 00:00:06 |

|* 2 | INDEX RANGE SCAN | INDEX_1_TEST | 24608 | | 42 (3)
| 00:00:01 |

——————————————————————————–
————
Statistics
———————————————————-
0 recursive calls
0 db block gets
367 consistent gets
0 physical reads
0 redo size
2126037 bytes sent via SQL*Net to client
16470 bytes received via SQL*Net from client
1457 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2834 rows processed

Como se puede ver el costo es bastante más alto, aún así utilizando el índice. Apliquemos la fórmula y asignemos un nuevo valor a nivel de sesión para el optimizer_index_cost_adj:

costo FTS/costo indice hint * 100

174/499*100 = 34,8

Asignaremos este nuevo valor al parámetro:

SQL> alter session set optimizer_index_cost_adj = 34;

Session altered.

SQL> select * from tabla_test where owner#=1;

2834 rows selected.
Execution Plan
———————————————————-
Plan hash value: 1582054771

——————————————————————————–
————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |

——————————————————————————–
————

| 0 | SELECT STATEMENT | | 24608 | 30M| 100 (1)
| 00:00:02 |

| 1 | TABLE ACCESS BY INDEX ROWID| TABLA_TEST | 24608 | 30M| 100 (1)
| 00:00:02 |

|* 2 | INDEX RANGE SCAN | INDEX_1_TEST | 24608 | | 8 (0)
| 00:00:01 |

——————————————————————————–
————
Statistics
———————————————————-
13 recursive calls
0 db block gets
367 consistent gets
0 physical reads
0 redo size
2126037 bytes sent via SQL*Net to client
16470 bytes received via SQL*Net from client
1457 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
21834 rows processed

Como podemos ver luego de la modificación de este parámetro el costo y los consisten gets disminuyeron sin necesidad de recurrir a la utilización de un hint, además de eliminar el FTS.

Como Observación es bastante común por lo menos para mí observar el valor de optimizer_index_cost_adj entre 20 y 30 en las bases de datos. Es importante recalcar que el DBA siempre debe optimizar sus resultados en base a las lecturas más que por el costo. Recordemos que el Costo es un valor que Oracle representa para un plan de ejecución, es algo así como un puntaje. El verdadero afinamiento debe estar orientado a las estadisticas que te entrega el plan como por ejemplo los consistent gets.

En esta nota metalink 243269.1How does Parameter OPTIMIZER_INDEX_COST_ADJ influence Index Access” está la información original, además del comentario de otros parámetros a los cuales ponerle atención. No olvidar establecer el valor de parámetro desde el archivo SPFILE o PFILE de Oracle, sólo para efectos de prueba lo declaramos a nivel de sesión.


Originalmente escrito en: www.dbagroup.cl blog de la empresa donde presto servicios de consultor de empresas.

Share:

4 Oct 2010

Oracle Standby: Log shipping rcp

www.felipedonoso.cl
felipe@felipedonoso.cl
RC6016
1NF3RNU5



If you need to make a Oracle Standby Database in Unix environment and you haven't the typical tools like ssh,ftp,nfs and/or Data Guard (Oracle) you can to use rcp and rsh for to transport the archived logs (and to use cksum or sum for to check the copied files).

First:

You should to have installed rsh-server, and then to configure the /etc/hosts.equiv

Second:

Execute periodically this script for to copy archived redolog files:

#!/usr/bin/ksh
# ----------------------------------------------------------------------
# Autores : Felipe Donoso Bastias
#
#
# Version : 1.1
#
# Fecha de Creacion : 14-12-2007
#
# Objetivos : Copiar los archived logs al servidor secundario
#
# Modificado : 28-12-2007
# - Se agrega comprobacion de cheksum a los archivos traspasados
# - Se modifica el formato de salida de los archivos de log
#
# Observaciones : Se comprueba la integridad de cada archivo (Checksum) a través
# del utilitario cksum.
#
# ----------------------------------------------------------------------
listado_remoto=/opt/oracle/stby/listado_remoto.txt
listado_local=/opt/oracle/stby/listado_local.txt
arch_des=
/u02/oradata/felipe
log=/opt/oracle/stby/log_shipping.log
log_hist=/opt/oracle/stby/log_shipping_hist.log
remote_host=felipe_prod
local_host=felipe_stby
rsh $remote_host ls $
arch_des/\*.arc > $listado_remoto
ls $
arch_des/*.arc > $listado_local
lista_archivos=$(sdiff -s $listado_remoto $listado_local | grep \< | awk ' { $2=R; print}') print "+----------------------------------------------------------------+" > $log
print "| Ejecucion de proceso copiado de archive |" >> $log
print "+----------------------------------------------------------------+" >> $log
print "******************************************************************" >> $log
echo "Lista de Archivos a Copiar: ">> $log
echo $lista_archivos >> $log
print "Inicio Copia de archived logs: "$(date) >> $log
for foo in $lista_archivos;do
rcp $remote_host:$foo $arch_des
cksum_local=$(cksum $foo | awk '{print $1}')
cksum_remoto=$(rsh $remote_host cksum $foo | awk '{print $1}')
#Aqui se hace una comprobacion del archivo recien copiado
keeplooping=1

while [[ $keeplooping -eq 1 ]] ; do
if [[ "$cksum_local" = "$cksum_remoto" ]] ; then
#el archived fue bien copiado
keeplooping=0
fi
if [[ "$cksum_local" != "$cksum_remoto" ]] ; then
echo "CKSUM local : $cksum_local" >> $log
echo "CKSUM remoto: $cksum_remoto" >> $log
echo "Hay que realizar la copia de nuevo de $foo" >> $log
sleep 20
rcp $remote_host:$foo $arch_des
cksum_local=$(cksum $foo | awk '{print $1}')
cksum_remoto=$(rsh $remote_host cksum $foo | awk '{print $1}')
fi
done
done
print "Fin Copia de archived logs: "$(date) >> $log
echo "Lista de Archivo no copiados: ">> $log
rsh $remote_host ls
$arch_des/\*.arc > $listado_remoto
ls
$arch_des/*.arc > $listado_local
lista_archivos=$(sdiff -s $listado_remoto $listado_local | grep \< | awk ' { $2=R; print}') echo $lista_archivos >> $log
print "+----------------------------------------------------------------+" >> $log
print "| Fin de Ejecucion de proceso |" >> $log
print "+----------------------------------------------------------------+" >> $log
print " " >> $log
print " " >> $log
print " " >> $log
print " " >> $log
cat $log >> $log_hist


Its very easy, sorry for my very BAD english.
PD: I executed this in environment ORacle 7.3 on Solaris 6
Greetings for all, in special for my Sindy friend. Felipe



Share:

Copyright © DBA TIPS | Powered by Blogger
Design by SimpleWpThemes | Blogger Theme by NewBloggerThemes.com | Free Blogger Templates