junho 2018 – Rodrigo Silva e Silva

[WARNING][DBT-11207] Specified SGA size is greater than the shmmax on the system.

Publicado em 11 de junho de 2018 by rodrigosilvaesilva

Ao tentar instalar o Oracle 12.2 com o dbca me deparei com a seguinte mensagem de aviso.

[WARNING][DBT-11207] Specified SGA size is greater than the shmmax on the system. This might make database creation to fail with ORA-27125 - Unable to create shared memory segment error.
ACTION: Specify SGA size lesser than or equal to the shmmax on the system.

1 2	[WARNING][DBT-11207] Specified SGA size is greater than the shmmax on the system. This might make database creation to fail with ORA-27125 - Unable to create shared memory segment error. ACTION: Specify SGA size lesser than or equal to the shmmax on the system.

Identifiquei que a configuração não estava padrão conforme documentação da Oracle. Adicionado a seguinte linha no /etc/sysctl.conf

#40GB de RAM:
kernel.shmmax=21474836479
kernel.shmmni=4096
kernel.shmall=10485760
vm.nr_hugepages=10240

#40GB de RAM:

kernel.shmmax=21474836479

kernel.shmmni=4096

kernel.shmall=10485760

vm.nr_hugepages=10240

Resolvido!

[WARNING] [DBT-10328], [FATAL] [DBT-10317]

Publicado em 11 de junho de 2018 by rodrigosilvaesilva

Ao executar o dbca para criar uma nova instância me deparei com o seguinte erro:

[oracle@bdoracle]$ dbca -silent -createDatabase -templateName New_Database.dbt 
-gdbName ORCL -characterSet WE8MSWIN1252 -sysPassword passccmoracle 
-systemPassword passccmoracle -emConfiguration NONE -databaseType OLTP -redoLogFileSize 300 
-recoveryAreaDestination /u02/fra

[oracle@bdoracle]$ dbca -silent -createDatabase -templateName New_Database.dbt

-gdbName ORCL -characterSet WE8MSWIN1252 -sysPassword passccmoracle

-systemPassword passccmoracle -emConfiguration NONE -databaseType OLTP -redoLogFileSize 300

-recoveryAreaDestination /u02/fra

[WARNING] [DBT-10328] Specified GDB Name (ORCL) may have a potential conflict with an already existing database on the system.
 ACTION: Specify a different GDB Name that does not conflict with existing databases on the system.
[FATAL] [DBT-10317] Specified SID Name (ORCL) already exists.
 ACTION: Specify a different SID Name that does not already exist.

[WARNING] [DBT-10328] Specified GDB Name (ORCL) may have a potential conflict with an already existing database on the system.

ACTION: Specify a different GDB Name that does not conflict with existing databases on the system.

[FATAL] [DBT-10317] Specified SID Name (ORCL) already exists.

ACTION: Specify a different SID Name that does not already exist.

Ao analisar identifiquei que já havia ocorrido a tentativa de criar uma instância com o nome ORCL Executar o dbca para remover.

[oracle@bdoracle]$ dbca -silent -deleteDatabase -sourceDB ORCL -sysDBAPassword password -sysDBAUserName SYS


The Database Configuration Assistant will delete the Oracle instance and datafiles for your database. All information in the database will be destroyed. Do you want to proceed?
Connecting to database
DBCA_PROGRESS : 4%
DBCA_PROGRESS : 5%
ORA-12547: TNS:lost contact
DBCA_PROGRESS : 10%
DBCA_PROGRESS : 14%
DBCA_PROGRESS : 19%
DBCA_PROGRESS : 24%
DBCA_PROGRESS : 29%
ORA-12547: TNS:lost contact
DBCA_PROGRESS : 47%
Updating network configuration files
DBCA_PROGRESS : 52%
Deleting instance and datafiles
DBCA_PROGRESS : 76%
DBCA_PROGRESS : 100%
Database deletion completed.

[oracle@bdoracle]$ dbca -silent -deleteDatabase -sourceDB ORCL -sysDBAPassword password -sysDBAUserName SYS

The Database Configuration Assistant will delete the Oracle instance and datafiles for your database. All information in the database will be destroyed. Do you want to proceed?

Connecting to database

DBCA_PROGRESS : 4%

DBCA_PROGRESS : 5%

ORA-12547: TNS:lost contact

DBCA_PROGRESS : 10%

DBCA_PROGRESS : 14%

DBCA_PROGRESS : 19%

DBCA_PROGRESS : 24%

DBCA_PROGRESS : 29%

ORA-12547: TNS:lost contact

DBCA_PROGRESS : 47%

Updating network configuration files

DBCA_PROGRESS : 52%

Deleting instance and datafiles

DBCA_PROGRESS : 76%

DBCA_PROGRESS : 100%

Database deletion completed.

Lembrar de excluir do arquivo oratab a linha:

ORCL:/u01/oracle/product/12.2.0/dbhome_1:Y

1	ORCL:/u01/oracle/product/12.2.0/dbhome_1:Y

Verificando permissões e GRANTS de usuários

Publicado em 4 de junho de 2018 by rodrigosilvaesilva

Verificando permissões do usuário TESTE_ORCL

SYS@ORCL > SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE LIKE 'TESTE_ORCL';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
TESTE_ORCL                      ALTER SESSION                            NO
TESTE_ORCL                      ALTER SYSTEM                             NO
TESTE_ORCL                      CREATE SEQUENCE                          NO
TESTE_ORCL                      CREATE TABLE                             NO
TESTE_ORCL                      MANAGE TABLESPACE                        NO
TESTE_ORCL                      EXECUTE ANY PROCEDURE                    NO
TESTE_ORCL                      CREATE TRIGGER                           NO
TESTE_ORCL                      CREATE VIEW                              NO
TESTE_ORCL                      SELECT ANY TABLE                         NO
TESTE_ORCL                      UNLIMITED TABLESPACE                     NO
TESTE_ORCL                      CREATE SESSION                           NO
TESTE_ORCL                      SELECT ANY DICTIONARY                    NO
TESTE_ORCL                      CREATE PROCEDURE                         NO

13 rows selected.

SYS@ORCL > SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE LIKE 'TESTE_ORCL';

GRANTEE PRIVILEGE ADM

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

TESTE_ORCL ALTER SESSION NO

TESTE_ORCL ALTER SYSTEM NO

TESTE_ORCL CREATE SEQUENCE NO

TESTE_ORCL CREATE TABLE NO

TESTE_ORCL MANAGE TABLESPACE NO

TESTE_ORCL EXECUTE ANY PROCEDURE NO

TESTE_ORCL CREATE TRIGGER NO

TESTE_ORCL CREATE VIEW NO

TESTE_ORCL SELECT ANY TABLE NO

TESTE_ORCL UNLIMITED TABLESPACE NO

TESTE_ORCL CREATE SESSION NO

TESTE_ORCL SELECT ANY DICTIONARY NO

TESTE_ORCL CREATE PROCEDURE NO

13 rows selected.

SYS@ORCL > select * from (
select 'GRANT '||privilege||' TO '||grantee||';' from dba_sys_privs
where grantee in ('TESTE_ORCL')
union all
select 'grant '||privilege||' on '||grantor||'.'||table_name||' to '||grantee||';' from dba_tab_privs
where grantee in ('TESTE_ORCL')
union all
select 'GRANT '||GRANTED_ROLE||' TO '||grantee||';' from dba_role_privs
  9  where grantee in ('TESTE_ORCL'));
----------------------------------------------------------------------
GRANT ALTER SESSION TO TESTE_ORCL;
GRANT ALTER SYSTEM TO TESTE_ORCL;
GRANT CREATE SEQUENCE TO TESTE_ORCL;
GRANT CREATE TABLE TO TESTE_ORCL;
GRANT MANAGE TABLESPACE TO TESTE_ORCL;
GRANT EXECUTE ANY PROCEDURE TO TESTE_ORCL;
GRANT CREATE TRIGGER TO TESTE_ORCL;
GRANT CREATE VIEW TO TESTE_ORCL;
GRANT SELECT ANY TABLE TO TESTE_ORCL;
GRANT UNLIMITED TABLESPACE TO TESTE_ORCL;
GRANT CREATE SESSION TO TESTE_ORCL;
GRANT SELECT ANY DICTIONARY TO TESTE_ORCL;
GRANT CREATE PROCEDURE TO TESTE_ORCL;
GRANT CONNECT TO TESTE_ORCL;

14 rows selected.

SYS@ORCL > select * from (

select 'GRANT '||privilege||' TO '||grantee||';' from dba_sys_privs

where grantee in ('TESTE_ORCL')

union all

select 'grant '||privilege||' on '||grantor||'.'||table_name||' to '||grantee||';' from dba_tab_privs

where grantee in ('TESTE_ORCL')

union all

select 'GRANT '||GRANTED_ROLE||' TO '||grantee||';' from dba_role_privs

9 where grantee in ('TESTE_ORCL'));

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

GRANT ALTER SESSION TO TESTE_ORCL;

GRANT ALTER SYSTEM TO TESTE_ORCL;

GRANT CREATE SEQUENCE TO TESTE_ORCL;

GRANT CREATE TABLE TO TESTE_ORCL;

GRANT MANAGE TABLESPACE TO TESTE_ORCL;

GRANT EXECUTE ANY PROCEDURE TO TESTE_ORCL;

GRANT CREATE TRIGGER TO TESTE_ORCL;

GRANT CREATE VIEW TO TESTE_ORCL;

GRANT SELECT ANY TABLE TO TESTE_ORCL;

GRANT UNLIMITED TABLESPACE TO TESTE_ORCL;

GRANT CREATE SESSION TO TESTE_ORCL;

GRANT SELECT ANY DICTIONARY TO TESTE_ORCL;

GRANT CREATE PROCEDURE TO TESTE_ORCL;

GRANT CONNECT TO TESTE_ORCL;

14 rows selected.

ORA-12516 TNS: listener can not find the available handler with the corresponding protocol stack!

Publicado em 1 de junho de 2018 by rodrigosilvaesilva

ERROR : ORA-12516: TNS:listener could not find available handler with matching protocol stack

1	ERROR : ORA-12516: TNS:listener could not find available handler with matching protocol stack

Motivo: Limite de processos atingidos. Não é mais possível criar novas sessões no banco. Verificando valor máximo de sessões e processos do banco de dados

SYS@ORCL > select name, value from v$parameter where name in ('processes','sessions');

NAME
--------------------------------------------------------------------------------
VALUE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
processes
150

sessions
248

SYS@ORCL > select name, value from v$parameter where name in ('processes','sessions');

NAME

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

VALUE

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

processes

150

sessions

248

Verificando quantidade sessões e processos ativos no momento do erro

SYS@ORCL > select resource_name, CURRENT_UTILIZATION from v$resource_limit where resource_name in ('processes','sessions');

RESOURCE_NAME                  CURRENT_UTILIZATION
------------------------------ -------------------
processes                                      149
sessions                                       153

SYS@ORCL > select resource_name, CURRENT_UTILIZATION from v$resource_limit where resource_name in ('processes','sessions');

RESOURCE_NAME CURRENT_UTILIZATION

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

processes 149

sessions 153

Verificando processos setados.

 SYS@ORCL > show parameter process

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
cell_offload_processing              boolean     TRUE
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     150
processor_group_name                 string

SYS@ORCL > show parameter process

NAME TYPE VALUE

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

aq_tm_processes integer 1

cell_offload_processing boolean TRUE

db_writer_processes integer 1

gcs_server_processes integer 0

global_txn_processes integer 1

job_queue_processes integer 1000

log_archive_max_processes integer 4

processes integer 150

processor_group_name string

Visualizando os 2 selects juntos.

select decode(name,'processes','Processos Configurados','sessions','Sessões Configuradas') as "CONFIGURACAO",to_char(value) as "VALOR"
from v$parameter where name in ('processes','sessions')
 
UNION ALL
 
select decode(resource_name,'processes','Processos Ativos','sessions','Sessões Ativas') as "CONFIGURACAO", 
to_char(CURRENT_UTILIZATION) as "VALOR"
from v$resource_limit where resource_name in ('processes','sessions');

select decode(name,'processes','Processos Configurados','sessions','Sessões Configuradas') as "CONFIGURACAO",to_char(value) as "VALOR"

from v$parameter where name in ('processes','sessions')

UNION ALL

select decode(resource_name,'processes','Processos Ativos','sessions','Sessões Ativas') as "CONFIGURACAO",

to_char(CURRENT_UTILIZATION) as "VALOR"

from v$resource_limit where resource_name in ('processes','sessions');

Solução: Aumente a quantidade de processos e…