Tag: Oracle

Case sensitive passwords (and auditing) are a default feature of newly created Oracle 11g databases

Original: http://www.oracle-base.com/articles/11g/case-sensitive-passwords-11gr1.php

Case sensitive passwords (and auditing) are a default feature of newly created Oracle 11g databases. The Database Configuration Assistant (DBCA) allows you to revert these settings back to the pre-11g functionality during database creation. The SEC_CASE_SENSITIVE_LOGON initialization parameter gives control over case sensitive passwords. If existing applications struggle to authenticate against 11g, you can use the ALTER SYSTEM command to turn off this functionality.

SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL> 

SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

System altered.

SQL>

The following code demonstrates the case sensitive password functionality. First, it resets the SEC_CASE_SENSITIVE_LOGON initialization parameter to TRUE and creates a new user with a mixed case password.

CONN / AS SYSDBA
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE;

CREATE USER test2 IDENTIFIED BY Test2;
GRANT CONNECT TO test2;

We can see the case sensitive password functionality in operation if we attempt to connect to the new user with both the correct and incorrect case password.

SQL> CONN test2/Test2
Connected.
SQL> CONN test2/test2
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL>

By switching the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE we are able to connect using both variations of the password.

CONN / AS SYSDBA
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

SQL> CONN test2/Test2
Connected.
SQL> CONN test2/test2
Connected.
SQL>

The important thing to remember here is even when case sensitive passwords are not enabled, the original case of the password is retained so it can be used if case sensitivity is subsequently enabled. The following code disables case sensitivity and creates a new user with a mixed case password.

CONN / AS SYSDBA
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
CREATE USER test3 IDENTIFIED BY Test3;
GRANT CONNECT TO test3;

As you would expect, connection to the user is possible regardless of the case of the password.

SQL> CONN test3/Test3
Connected.
SQL> CONN test3/test3
Connected.
SQL>

If we enable case sensitivity, authentication is done against the mixed case password.

CONN / AS SYSDBA
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE;

SQL> CONN test3/Test3
Connected.
SQL> CONN test3/test3
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL>

The DBA_USERS view includes a PASSWORD_VERSIONS column that indicates the database release in which the password was created or last modified.

SQL> SELECT username, password_versions FROM dba_users;

USERNAME                       PASSWORD
------------------------------ --------
TEST                           10G 11G
SPATIAL_WFS_ADMIN_USR          10G 11G
SPATIAL_CSW_ADMIN_USR          10G 11G
APEX_PUBLIC_USER               10G 11G
.
.
.
SYSTEM                         10G 11G
SYS                            10G 11G
MGMT_VIEW                      10G 11G
OUTLN                          10G 11G

32 rows selected.

SQL>

Users imported from a 10g database have a PASSWORD_VERSIONS value of “10G” and maintain case insensitive passwords independent of the SEC_CASE_SENSITIVE_LOGON parameter setting. Their passwords become case sensitive as soon as they are changed, assuming the SEC_CASE_SENSITIVE_LOGON parameter is set to TRUE. The ignorecase parameter of the orapwd utility allows control over case sensitivity of passwords in the password file. The default value is “n”, meaning the passwords are case sensitive. When privileged users (SYSDBA & SYSOPER) are imported from a previous release their passwords are included in the password file. These users will retain case insensitive passwords until the password is modified. To create case insensitive passwords in the password file, recreate the password file using the ignorecase=y option.

$ orapwd file=orapwDB11Gb entries=100 ignorecase=y password=mypassword

The passwords associated with database links are also case sensitive, which presents some issues when connecting between different releases:

  • 11g to 11g: The database link must be created with the password in the correct case to match the remote users password.
  • 11g to Pre-11g: The database link can be created with the password in any case as case is ignored by the remote database.
  • Pre-11g to 11g: The remote user must have its password modified to be totally in upper case, as this is how it will be stored and passed by the Pre-11g database.

For more information see:

Automatizando startup e shutdown do Oracle 11g R2 no CentOS 6

TEXTO DE: http://mytracelog.blogspot.com.br/2011/07/automatizando-startup-e-shutdown-do.html

Automatizar processos manuais e repetitivos é uma forma de maximizar o tempo, tudo que é manual e repetitivo tem uma grande chance de erro humano. Neste artigo será apresentado como automatizar o startup e o shutdown do banco de dados Oracle 11g R2 x86_64 instalado no CentOS 5.6 x86_64. A automatização será feita através de um script bash que tornará todo o processo simples, seguro, além de não necessitar de nenhuma intervenção humana.

A automatização do processo é muito útil em ambientes 24×7 (24 horas por dia e 7 dias por semana). Implementar o startup automático pode evitar que você tenha que ir até a empresa de madrugada para iniciar o banco devido um pico de energia, neste caso, continue dormindo, pois ao reiniciar a máquina, o banco estará de pé novamente. Descanse bem, pois logo de manhã talvez você precise fazer um recovery, ou se tiver sorte, o próprio Oracle vai se recuperar da queda.

O startup automático iniciará os processos na seguinte ordem:

  1. Listener
  2. Oracle Database
  3. Oracle Enterprise Manager

O shutdown automático finalizará os processos na seguinte ordem:

  1. Oracle Enterprise Manager
  2. Listener
  3. Oracle Database


O processo de automatização é relativamente simples, na própria instalação do Oracle é disponibilizado scripts para startup e shutdown, são eles: $ORACLE_HOME/bin/dbstart e$ORACLE_HOME/bin/dbshut. Esses dois scripts trabalham junto com o arquivo /etc/oratab.

O arquivo oratab é responsável por definir quais instâncias serão inicializadas e finalizadas pelos scriptsdbstart e dbshut. O oratab é composto por entradas que seguem o seguinte padrão:

1
$ORACLE_SID:$ORACLE_HOME:<N|Y>

 

A identificação da instância é representada no primeiro campo, o segundo campo é o diretório home do banco e o terceiro defini se será (Y) ou não (N) utilizado nos scripts dbstart e dbshut.

Para automatizar o processo siga os passos:

    1. Editar o arquivo /etc/oratab, definindo Y para as instâncias que serão automatizadas:
1
[oracle@mytracelog ~]$ vi /etc/oratab
      Arquivo

/etc/oratab

      :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
mytracelog:/u01/app/oracle/product/11.2.0/db_1:Y
    1. Criar o script /etc/init.d/dbora:
1
[root@mytracelog ~]# vi /etc/init.d/dbora
      Script

/etc/init.d/dbora

      :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
#!/bin/bash
# script: dbora
# autor: sakamoto
# site: <a class="vglnk" href="http://mytracelog.blogspot.com" rel="nofollow"><span>http</span><span>://</span><span>mytracelog</span><span>.</span><span>blogspot</span><span>.</span><span>com</span></a>
# data: 2011-07-31
# versao: 1.0
# alvo: oracle 11g R2 x86_64 & centos 5.6 x86_64
# chkconfig: 35 99 10
# description: startup/shutdown listener, oracle database and enterprise manager
ORA_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
ORA_OWNER=oracle
start(){
   echo "Starting..."
   su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
   su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start dbconsole"
   touch /var/lock/subsys/dbora
}
stop(){
   echo "Stopping..."
   su - $ORA_OWNER -c "$ORA_HOME/bin/emctl stop dbconsole"
   su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
   rm -f /var/lock/subsys/dbora
}
restart(){
   stop
   start
}
usage(){
   echo "usage: $0 {start|stop|restart}"
}
if [ `id -u` -ne 0 ]
then
   echo "Este script deve ser executado como root"
   exit
fi
case $1 in
   'start') start;;
   'stop') stop;;
   'restart') restart;;
   *) usage;;
esac
    1. Defina a permissão para script /etc/init.d/dbora:
1
[root@mytracelog ~]# chmod 755 /etc/init.d/dbora
    1. Registre o serviço:
1
[root@mytracelog ~]# chkconfig --add dbora

Automatização completa com sucesso! Agora ao iniciar a máquina podemos ver o serviço dbora sendo iniciado:
startupdbora.png

Ao desligar ou reiniciar a máquina também podemos ver o serviço dbora sendo finalizado:
shutdowndbora.png

Caso necessite fazer o startup e shutdown de forma manual, utilize o usuário root.

A sintaxe do script dbora é a seguinte:

1
2
[root@mytracelog ~]# /etc/init.d/dbora
usage: /etc/init.d/dbora {start|stop|restart}

Para finalizar, algumas considerações sobre o script /etc/init.d/dbora. Como podemos ver, o script dbora é basicamente uma chamada para os outros dois scripts $ORACLE_HOME/bin/dbstart e$ORACLE_HOME/bin/dbshut com mais alguns recursos. Foi implementado o startup e shutdown do Oracle Enterprise Manager. O script dbora possui três opções: start, stop e restart. O restart nada mais é que a chamada do stop seguido do start.

 

OUTRAS FONTES

https://oracle-base.com/articles/linux/automating-database-startup-and-shutdown-on-linux

 

PDKSH-5.2.14 Problemas na instalação do Oracle 11g no CentOS 6.x

Problemas com lib  PDKSH Oracle

Texto de http://dinizdba.com/problema-com-pdksh-oracle/

Eu não estava me conformando com um erro na lib PDKSH toda vez que ia instalar um banco de dados Oracle 11g.

Pesquisei no site da própria RED HAT, e nele informa que o pacote em questão foi descontinuado e substituído pelo MKSH. Até ai tudo bem, mas esse erro de PDKSH assombra a nossa vida, principalmente nos ambientes RAC – ficam aparecendo mensagens no alert do banco de dados a todo momento.

Procurando e depois de muito tentar, consegui “desaparecer com esse erro” e para isso é necessário no momento da instalação do banco de dados alterar o valor do parâmetroCV_ASSUME_DISTID que fica no arquivo cvu_config no diretório database/stage/cvu/cv/admin do instalador do Oracle 11g.

CV_ASSUME_DISTID=OEL4

para

CV_ASSUME_DISTID=OEL6

 

Outros textos pesquisados:

https://douglasdba.wordpress.com/2014/08/18/pdksh-5-2-14/

https://dbakalyan.wordpress.com/2013/02/22/37/

http://www.marcelocauduro.com.br/2015/10/oracle-faltando-pacotes-pdksh-e-elfutils-ao-instalar-o-11g-no-el67/

Oracle Database 11g Release 2 Instalação no CentOS 6.x

 by Narad Shrestha | Published: September 23, 2013 | Last Updated: January 7, 2015

Desativando a expiração da senha dos usuários (schemas) no Oracle 11G R2

Original de http://rdglinux.blogspot.com.br/2011/06/desativando-expiracao-da-senha-dos.html

Recentemente tivemos um problema onde de uma maneira inesperada os sistemas que realizavam conexão com o Banco Oracle não estavam acessíveis, e o erro original não era retornado por falta de tratamento na aplicação.Depois de realizar várias verificações identificamos que as senhas dos Schemas estavam para expirar.

O que acontece?

Quando se cria um usuário (schema) no Oracle temos as opções de criar um profile específico e personalizado para esse usuário ou utilizar o profile default.

No Oracle 11G r2 a opção PASSWORD_LIFE_TIME no profile defaul, que é a opção do tempo de vida da senha, geralmente está definida como 90 dias.

Para desabilitar a opção de expiração de senha dos usuários (default no Oracle 11G r2), basta alterar o profile default conforme abaixo:

Como resolver:

Conecte-se no servidor de banco de dados;
Abra o prompt do sqlplus: 
sqlplus /nolog

Conecte-se como SYS as SYSDBA: 
conn /as sysdba

Execute o comando: 
alter profile default limit PASSWORD_LIFE_TIME unlimited;

E pronto!!! Se for somente esse o problema o seu sistema voltará a funcionar normalmente.!!!

 

Case Sensitive Passwords in Oracle Database 11g Release 1

Original: http://www.oracle-base.com/articles/11g/case-sensitive-passwords-11gr1.php

Case sensitive passwords (and auditing) are a default feature of newly created Oracle 11g databases. The Database Configuration Assistant (DBCA) allows you to revert these settings back to the pre-11g functionality during database creation.

The SEC_CASE_SENSITIVE_LOGON initialization parameter gives control over case sensitive passwords. If existing applications struggle to authenticate against 11g, you can use the ALTER SYSTEM command to turn off this functionality.

SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL> 


SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

System altered.

SQL>

The following code demonstrates the case sensitive password functionality. First, it resets the SEC_CASE_SENSITIVE_LOGON initialization parameter to TRUE and creates a new user with a mixed case password.

CONN / AS SYSDBA
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE;

CREATE USER test2 IDENTIFIED BY Test2;
GRANT CONNECT TO test2;

We can see the case sensitive password functionality in operation if we attempt to connect to the new user with both the correct and incorrect case password.

SQL> CONN test2/Test2
Connected.
SQL> CONN test2/test2
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>

By switching the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE we are able to connect using both variations of the password.

CONN / AS SYSDBA
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

SQL> CONN test2/Test2
Connected.
SQL> CONN test2/test2
Connected.
SQL>

The important thing to remember here is even when case sensitive passwords are not enabled, the original case of the password is retained so it can be used if case sensitivity is subsequently enabled. The following code disables case sensitivity and creates a new user with a mixed case password.

CONN / AS SYSDBA
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
CREATE USER test3 IDENTIFIED BY Test3;
GRANT CONNECT TO test3;

As you would expect, connection to the user is possible regardless of the case of the password.

SQL> CONN test3/Test3
Connected.
SQL> CONN test3/test3
Connected.
SQL>

If we enable case sensitivity, authentication is done against the mixed case password.

CONN / AS SYSDBA
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE;

SQL> CONN test3/Test3
Connected.
SQL> CONN test3/test3
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>

The DBA_USERS view includes a PASSWORD_VERSIONS column that indicates the database release in which the password was created or last modified.

SQL> SELECT username, password_versions FROM dba_users;

USERNAME                       PASSWORD
------------------------------ --------
TEST                           10G 11G
SPATIAL_WFS_ADMIN_USR          10G 11G
SPATIAL_CSW_ADMIN_USR          10G 11G
APEX_PUBLIC_USER               10G 11G
.
.
.
SYSTEM                         10G 11G
SYS                            10G 11G
MGMT_VIEW                      10G 11G
OUTLN                          10G 11G

32 rows selected.

SQL>

Users imported from a 10g database have a PASSWORD_VERSIONS value of “10G” and maintain case insensitive passwords independent of the SEC_CASE_SENSITIVE_LOGON parameter setting. Their passwords become case sensitive as soon as they are changed, assuming the SEC_CASE_SENSITIVE_LOGON parameter is set to TRUE.

The ignorecase parameter of the orapwd utility allows control over case sensitivity of passwords in the password file. The default value is “n”, meaning the passwords are case sensitive. When privileged users (SYSDBA & SYSOPER) are imported from a previous release their passwords are included in the password file. These users will retain case insensitive passwords until the password is modified.

To create case insensitive passwords in the password file, recreate the password file using the ignorecase=y option.

$ orapwd file=orapwDB11Gb entries=100 ignorecase=y password=mypassword

The passwords associated with database links are also case sensitive, which presents some issues when connecting between different releases:

  • 11g to 11g: The database link must be created with the password in the correct case to match the remote users password.
  • 11g to Pre-11g: The database link can be created with the password in any case as case is ignored by the remote database.
  • Pre-11g to 11g: The remote user must have its password modified to be totally in upper case, as this is how it will be stored and passed by the Pre-11g database.

For more information see: