Rodrigo Silva e Silva – Página: 14

ORA-01940: cannot drop a user that is currently connected

Publicado em 19 de julho de 2018 by rodrigosilvaesilva

Dropando usuário ORCL

SYS@ORCL > drop user ORCL cascade;

1	SYS@ORCL > drop user ORCL cascade;

Erro ORA-01940

ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

1 2	ERROR at line 1: ORA-01940: cannot drop a user that is currently connected

Solução: Parando

SYS@ORCL > Shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@ORCL > Shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Iniciar em modo restrict

SYS@ORCL > startup restrict;
ORACLE instance started.

Total System Global Area 1.0689E+10 bytes
Fixed Size 2262656 bytes
Variable Size 1979713920 bytes
Database Buffers 8690597888 bytes
Redo Buffers 16900096 bytes
Database mounted.
Database opened.

SYS@ORCL > startup restrict;

ORACLE instance started.

Total System Global Area 1.0689E+10 bytes

Fixed Size 2262656 bytes

Variable Size 1979713920 bytes

Database Buffers 8690597888 bytes

Redo Buffers 16900096 bytes

Database mounted.

Database opened.

Dropando usuário ORCL

SYS@NBS > drop user ORCL cascade;

User dropped.

SYS@NBS > drop user ORCL cascade;

User dropped.

Parando

SYS@ORCL > shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@ORCL > shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Iniciando

21:50:44 SYS@ORCL > startup
ORACLE instance started.

Total System Global Area 1.0689E+10 bytes
Fixed Size 2262656 bytes
Variable Size 1979713920 bytes
Database Buffers 8690597888 bytes
Redo Buffers 16900096 bytes
Database mounted.
Database opened.

21:50:44 SYS@ORCL > startup

ORACLE instance started.

Total System Global Area 1.0689E+10 bytes

Fixed Size 2262656 bytes

Variable Size 1979713920 bytes

Database Buffers 8690597888 bytes

Redo Buffers 16900096 bytes

Database mounted.

Database opened.

Em outro post mostrarei como matar processo do usuário.

ORA-28001: the password has expired

Publicado em 17 de julho de 2018 by rodrigosilvaesilva

Ao realizar a análise de uma rotina de backup Data Pump me deparei com seguinte erro no log abaixo:

EXP-00056: ORACLE error 28001 encountered
ORA-28001: the password has expired
EXP-00030: Unexpected End-Of-File encountered while reading input
EXP-00000: Export terminated unsuccessfully

EXP-00056: ORACLE error 28001 encountered

ORA-28001: the password has expired

EXP-00030: Unexpected End-Of-File encountered while reading input

EXP-00000: Export terminated unsuccessfully

Verificando contas com senha expirada.

SYSTEM@ORCL> select username, account_status from dba_users where lock_date is not null;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
FLOWS_FILES                    EXPIRED & LOCKED
MDSYS                          EXPIRED & LOCKED
ORDDATA                        EXPIRED & LOCKED
ORDSYS                         EXPIRED & LOCKED
CTXSYS                         EXPIRED & LOCKED
ANONYMOUS                      EXPIRED & LOCKED
EXFSYS                         EXPIRED & LOCKED
OUTLN                          EXPIRED & LOCKED
DIP                            EXPIRED & LOCKED
APEX_PUBLIC_USER               EXPIRED & LOCKED
WMSYS                          EXPIRED & LOCKED
XDB                            EXPIRED & LOCKED
ORACLE_OCM                     EXPIRED & LOCKED
APPQOSSYS                      EXPIRED & LOCKED
ORDPLUGINS                     EXPIRED & LOCKED
XS$NULL                        EXPIRED & LOCKED
APEX_030200                    EXPIRED & LOCKED
SI_INFORMTN_SCHEMA             EXPIRED & LOCKED
SYSTEM                         EXPIRED & LOCKED

18 rows selected.

SYSTEM@ORCL> select username, account_status from dba_users where lock_date is not null;

USERNAME ACCOUNT_STATUS

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

FLOWS_FILES EXPIRED & LOCKED

MDSYS EXPIRED & LOCKED

ORDDATA EXPIRED & LOCKED

ORDSYS EXPIRED & LOCKED

CTXSYS EXPIRED & LOCKED

ANONYMOUS EXPIRED & LOCKED

EXFSYS EXPIRED & LOCKED

OUTLN EXPIRED & LOCKED

DIP EXPIRED & LOCKED

APEX_PUBLIC_USER EXPIRED & LOCKED

WMSYS EXPIRED & LOCKED

XDB EXPIRED & LOCKED

ORACLE_OCM EXPIRED & LOCKED

APPQOSSYS EXPIRED & LOCKED

ORDPLUGINS EXPIRED & LOCKED

XS$NULL EXPIRED & LOCKED

APEX_030200 EXPIRED & LOCKED

SI_INFORMTN_SCHEMA EXPIRED & LOCKED

SYSTEM EXPIRED & LOCKED

18 rows selected.

Analisando profile do usuário SYSTEM

SYSTEM@ORCL > select username, profile from dba_users where username='SYSTEM';

USERNAME                       PROFILE
------------------------------ ------------------------------
SYSTEM                         DEFAULT

SYSTEM@ORCL > select username, profile from dba_users where username='SYSTEM';

USERNAME PROFILE

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

SYSTEM DEFAULT

Analisando DBA_PROFILES

SYSTEM@ORCL> select RESOURCE_NAME, LIMIT from dba_profiles where RESOURCE_NAME='PASSWORD_LIFE_TIME';

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------
PASSWORD_LIFE_TIME               180

SYSTEM@ORCL> select RESOURCE_NAME, LIMIT from dba_profiles where RESOURCE_NAME='PASSWORD_LIFE_TIME';

RESOURCE_NAME LIMIT

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

PASSWORD_LIFE_TIME 180

Está definido 180 dias por default. Alterando Profile para ilimitado.

SYSTEM@ORCL> ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

SYSTEM@ORCL> ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

Verificando novamente DBA_PROFILES.

SYSTEM@ORCL> select RESOURCE_NAME, LIMIT from dba_profiles where RESOURCE_NAME='PASSWORD_LIFE_TIME';

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------
PASSWORD_LIFE_TIME               UNLIMITED

SYSTEM@ORCL> select RESOURCE_NAME, LIMIT from dba_profiles where RESOURCE_NAME='PASSWORD_LIFE_TIME';

RESOURCE_NAME LIMIT

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

PASSWORD_LIFE_TIME UNLIMITED

Alterando a senha.

SYSTEM@ORCL> ALTER USER system IDENTIFIED BY password;         
User altered.

1 2	SYSTEM@ORCL> ALTER USER system IDENTIFIED BY password; User altered.

Desbloqueando…

[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.

← Posts recentes Posts antigos →