MySQL - Grants

Queries para exportar os grants de um determinado usuário:

set @usuario='\'nome_usuario\'@\'ip_usuario\'';

--criação usuário
select concat('GRANT USAGE ON *.* TO \'',user,'\'@\'',host,'\' identified by password \'',password,'\';') from mysql.user where @usuario=concat('\'',user,'\'@\'',host,'\'');

-- user_privileges
select concat('grant ',PRIVILEGE_TYPE,' on *.* to ',@usuario,CASE IS_GRANTABLE WHEN 'YES' THEN ' with grant option;' WHEN 'NO' THEN ';' ELSE ';' END) from information_schema.user_privileges where GRANTEE = @usuario;

-- schema_privileges
select concat('grant ',PRIVILEGE_TYPE,' on ',TABLE_SCHEMA,'.* to ',@usuario,CASE IS_GRANTABLE WHEN 'YES' THEN ' with grant option;' WHEN 'NO' THEN ';' ELSE ';' END) from information_schema.schema_privileges where GRANTEE = @usuario;

-- table_privileges
select concat('grant ',PRIVILEGE_TYPE,' on ',TABLE_SCHEMA,'.',TABLE_NAME,' to ',@usuario,CASE IS_GRANTABLE WHEN 'YES' THEN ' with grant option;' WHEN 'NO' THEN ';' ELSE ';' END) from information_schema.table_privileges where GRANTEE = @usuario;

Queries para exportar os grants de todos os usuários:

--criação usuário

select concat('GRANT USAGE ON *.* TO \'',user,'\'@\'',host,'\' identified by password \'',password,'\';') from mysql.user;

-- user_privileges
select concat('grant ',PRIVILEGE_TYPE,' on *.* to ',GRANTEE,CASE IS_GRANTABLE WHEN 'YES' THEN ' with grant option;' WHEN 'NO' THEN ';' ELSE ';' END) from information_schema.user_privileges;

-- schema_privileges
select concat('grant ',PRIVILEGE_TYPE,' on ',TABLE_SCHEMA,'.* to ',GRANTEE,CASE IS_GRANTABLE WHEN 'YES' THEN ' with grant option;' WHEN 'NO' THEN ';' ELSE ';' END) from information_schema.schema_privileges;

-- table_privileges
select concat('grant ',PRIVILEGE_TYPE,' on ',TABLE_SCHEMA,'.',TABLE_NAME,' to ',GRANTEE,CASE IS_GRANTABLE WHEN 'YES' THEN ' with grant option;' WHEN 'NO' THEN ';' ELSE ';' END) from information_schema.table_privileges;

Grid Control - Falha de monitoração Oracle 9

Agent 10.2.0.5 e 11.1.0.1 falha na monitoração de database target na versão 9.

Os erros gerados são do tipo:

emagent.trc :
~~~~~~~~~~~~~~~~
    2009-05-15 11:14:17,848 Thread-5584 WARN  vpxoci: Login 0x35c0ee8 failed, error=ORA-03113:
    end-of-file on communication channel
    2009-05-15 11:14:17,848 Thread-5584 WARN  TargetManager: Exception in computing dynamic properties
    of {EPPROD, oracle_database },GetDumpDestination::ORA-03113: end-of-file on communication channel
    2009-05-15 11:14:19,801 Thread-5584 WARN  vpxoci: vpxoci_CtxLogin: Breaking out of
    OCISessionGet/OCIPing loop after 7 tries
    2009-05-15 11:14:19,801 Thread-5584 ERROR vpxoci: ORA-03113: end-of-file on communication channel
    2009-05-15 11:14:19,801 Thread-5584 WARN  vpxoci: Login 0x35c0ee8 failed, error=ORA-03113:
    end-of-file on communication channel
    2009-05-15 11:14:19,801 Thread-5584 WARN  TargetManager: Exception in computing dynamic properties
    of {EPPROD, oracle_database },GetArchiveDestination::ORA-03113: end-of-file on communication channel
    2009-05-15 11:14:19,801 Thread-5584 WARN  engine: Missing Properties : [background_dump_dest]
    2009-05-15 11:14:19,801 Thread-5584 WARN  TargetManager: Exception in computing dynamic properties
    of {EPPROD, oracle_database },DeduceAlertLogFile::Missing Properties : [background_dump_dest]
    2009-05-15 11:14:21,723 Thread-5584 WARN  vpxoci: vpxoci_CtxLogin: Breaking out of
    OCISessionGet/OCIPing loop after 7 tries 

O alert do banco gera um:

ORA-07445: exception encountered: core dump [0x5012414E53414354] [SIGSEGV] [unknown code] [0x8000000014BE4328] [] [

ou

ORA-07445: exception encountered: core dump [opitsk()+4223] [SIGSEGV] [unknown code] [0x000000000] [] []

A correção disso, para agent 11, é descomentar e setar o seguinte parâmetro:


# Flag to Determine if OCIPing can be used. Older versions of databases such
# as 9206 do not support this.
#
#@description=Flag to Determine if OCIPing can be used.
#@default=0
AvoidOCIPing=1


Para agent 10.2.0.5, seguir documentação:

Grid Control Agent Fails to Monitor 9.2 Databases With ORA-3113 in Emagent.trc And ORA-7445 in Alert.log File of Target Database [ID 828464.1]

Agent emctl - Could not create the Java virtual machine

Me deparei com esse problema recentemente devido a limitação e fragmentação de memória no servidor para fazer qualquer operação relacionada ao Agent:

[oracle@server ~]$ /u00/app/oracle/product/agent11g/bin/emctl start agent
Could not create the Java virtual machine.
EM Configuration issue. /u00/app/oracle/product/agent11g/Error occurred during initialization of VM
Could not reserve enough space for object heap not found.

A solução para isso é bem simples: exportar uma variável de ambiente do Java forçando os limites de alocação de memória:

export _JAVA_OPTIONS="-Xms256m -Xmx512m"

(Coloque no profile do usuário oracle para persistir)