Oracle – Página: 2 – Rodrigo Silva e Silva

ORA-39082: Object type PROCEDURE: ORA-39082: Object type TRIGGER:

Publicado em 10 de julho de 2020 by rodrigosilvaesilva

Bom dia pessoal! Hoje me deparei com um cliente com a seguinte situação: Ao realizar o impdp várias TRIGGERS e PROCEDURES ficaram invalidadas. Resolvendo erro ORA-3902

ORA-39082: Object type PROCEDURE:"NAME"."STP_GRAVATABLOG" created with compilation warnings
ORA-39082: Object type PROCEDURE:"NAME"."STP_GRAVA_TSIPARLOG" created with compilation warnings
ORA-39082: Object type PROCEDURE:"NAME"."DESABILITA_TRG_TAB" created with compilation warnings
ORA-39082: Object type PROCEDURE:"NAME"."HABILITA_TRG_TAB" created with compilation warnings
ORA-39082: Object type PROCEDURE:"NAME"."STP_GRAVATABLOG" created with compilation warnings
ORA-39082: Object type PROCEDURE:"NAME"."STP_GRAVA_TSIPARLOG" created with compilation warnings
ORA-39082: Object type PROCEDURE:"NAME"."DESABILITA_TRG_TAB" created with compilation warnings
ORA-39082: Object type PROCEDURE:"NAME"."HABILITA_TRG_TAB" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFFIN_EXCLUSAO" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_UPD_DLT_TGMTME_EXCLUSAO" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFMBC_EXCLUSAO" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFLIV_EXC" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_INS_TGWLOG" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_INS_TSILOG" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFITE" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFTEF_EXCLUSAO" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_INC_UPD_DLT_TFPVPS_LOG" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFCAB" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_TSIPAR_LOG" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_UPD_DLT_TGMMSG_EXCLUSAO" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFNUM" created with compilation warnings

ORA-39082: Object type PROCEDURE:"NAME"."STP_GRAVATABLOG" created with compilation warnings

ORA-39082: Object type PROCEDURE:"NAME"."STP_GRAVA_TSIPARLOG" created with compilation warnings

ORA-39082: Object type PROCEDURE:"NAME"."DESABILITA_TRG_TAB" created with compilation warnings

ORA-39082: Object type PROCEDURE:"NAME"."HABILITA_TRG_TAB" created with compilation warnings

ORA-39082: Object type PROCEDURE:"NAME"."STP_GRAVATABLOG" created with compilation warnings

ORA-39082: Object type PROCEDURE:"NAME"."STP_GRAVA_TSIPARLOG" created with compilation warnings

ORA-39082: Object type PROCEDURE:"NAME"."DESABILITA_TRG_TAB" created with compilation warnings

ORA-39082: Object type PROCEDURE:"NAME"."HABILITA_TRG_TAB" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFFIN_EXCLUSAO" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_UPD_DLT_TGMTME_EXCLUSAO" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFMBC_EXCLUSAO" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFLIV_EXC" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_INS_TGWLOG" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_INS_TSILOG" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFITE" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFTEF_EXCLUSAO" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_INC_UPD_DLT_TFPVPS_LOG" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFCAB" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_TSIPAR_LOG" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_UPD_DLT_TGMMSG_EXCLUSAO" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFNUM" created with compilation warnings

Concedendo permissões para o usuário NAME

GRANT SELECT ON V_$SESSION TO NAME 
/ 
GRANT SELECT ON DBA_TABLES  TO NAME 
/ 
GRANT CREATE SESSION TO NAME 
/ 
GRANT SELECT ON DBA_TAB_COLUMNS TO NAME 
/ 
GRANT SELECT ON DBA_CONSTRAINTS  TO NAME 
/ 
GRANT SELECT ON DBA_TRIGGERS TO NAME 
/ 
GRANT SELECT ON DBA_INDEXES TO NAME 
/ 
GRANT SELECT ON DBA_VIEWS  TO  NAME 
/ 
GRANT SELECT ON DBA_IND_COLUMNS TO NAME 
/ 
GRANT SELECT ON DBA_OBJECTS TO NAME  
/ 
GRANT EXP_FULL_DATABASE TO NAME 
/ 
GRANT IMP_FULL_DATABASE TO NAME

GRANT SELECT ON V_$SESSION TO NAME

GRANT SELECT ON DBA_TABLES TO NAME

GRANT CREATE SESSION TO NAME

GRANT SELECT ON DBA_TAB_COLUMNS TO NAME

GRANT SELECT ON DBA_CONSTRAINTS TO NAME

GRANT SELECT ON DBA_TRIGGERS TO NAME

GRANT SELECT ON DBA_INDEXES TO NAME

GRANT SELECT ON DBA_VIEWS TO NAME

GRANT SELECT ON DBA_IND_COLUMNS TO NAME

GRANT SELECT ON DBA_OBJECTS TO NAME

GRANT EXP_FULL_DATABASE TO NAME

GRANT IMP_FULL_DATABASE TO NAME

Rebuild objetos inválidos

EXEC DBMS_UTILITY.compile_schema(schema => 'NAME')

1	EXEC DBMS_UTILITY.compile_schema(schema => 'NAME')

ORA-02304: invalid object identifier literal

Publicado em 11 de abril de 2020 by rodrigosilvaesilva

Ao realizar import me deparei com o erro: Erro ORA-39083: Object type TYPE failed to create with error: ORA-02304: invalid object identifier literal

Connected to: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/********@DB directory=BKPDIR transform=disable_archive_logging:Y dumpfile=DB_FULL_09042020.DMP logfile=IMP_MIGRACAO_DB_09042020.log schemas=DB EXCLUDE=STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"SANKHYA" already exists
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
ORA-39083: Object type TYPE:"DB"."T_EXTRATO" failed to create with error:
ORA-02304: invalid object identifier literal

Failing sql is:
CREATE TYPE "DB"."T_EXTRATO"   OID '117BA7FA64CE4D8D802965580CAA506B' AS OBJECT (
        DTMOV  DATE,
        COTACAO FLOAT,
        VLRANT FLOAT,
        VLRAPLICADO FLOAT
    )

Connected to: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/********@DB directory=BKPDIR transform=disable_archive_logging:Y dumpfile=DB_FULL_09042020.DMP logfile=IMP_MIGRACAO_DB_09042020.log schemas=DB EXCLUDE=STATISTICS

Processing object type DATABASE_EXPORT/SCHEMA/USER

ORA-31684: Object type USER:"SANKHYA" already exists

Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA

Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE

Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC

ORA-39083: Object type TYPE:"DB"."T_EXTRATO" failed to create with error:

ORA-02304: invalid object identifier literal

Failing sql is:

CREATE TYPE "DB"."T_EXTRATO" OID '117BA7FA64CE4D8D802965580CAA506B' AS OBJECT (

DTMOV DATE,

COTACAO FLOAT,

VLRANT FLOAT,

VLRAPLICADO FLOAT

)

Causa: Retorna o erro pois o Oracle não permite importar dois objetos com o mesmo OID. Cada objeto deve ter OID único no banco de dados TRANSFORM=OID:N parameter on the import….

Extrair DDL roles

Publicado em 11 de abril de 2020 by rodrigosilvaesilva

Extrair DDL ROLES

set heading off;
set echo off;
set pages 1000
set long 3000
spool extrair_roles_ddl.sql
select dbms_metadata.get_ddl('ROLE', ROLE) from dba_roles;
spool off



CREATE ROLE "CONNECT";

CREATE ROLE "RESOURCE";

CREATE ROLE "DBA";

CREATE ROLE "PDB_DBA";

CREATE ROLE "AUDIT_ADMIN";

CREATE ROLE "AUDIT_VIEWER";

CREATE ROLE "SELECT_CATALOG_ROLE";

set heading off;

set echo off;

set pages 1000

set long 3000

spool extrair_roles_ddl.sql

select dbms_metadata.get_ddl('ROLE', ROLE) from dba_roles;

spool off

CREATE ROLE "CONNECT";

CREATE ROLE "RESOURCE";

CREATE ROLE "DBA";

CREATE ROLE "PDB_DBA";

CREATE ROLE "AUDIT_ADMIN";

CREATE ROLE "AUDIT_VIEWER";

CREATE ROLE "SELECT_CATALOG_ROLE";

TNS-01151: Missing listener name, orcl, in LISTENER.ORA

Publicado em 10 de março de 2020 by rodrigosilvaesilva

Erro TNS-01151: Missing listener name, orcl, in LISTENER.ORA Causa Verificado que a porta foi alterada no listener.ora e no tnsnames.ora não foi alterada.

/u01/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bdora-prod.prod.local)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD)
    )
  )

/u01/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PROD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = bdora-prod.prod.local)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = PROD)

)

# listener.ora Network Configuration File: /u01/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = bdora-prod.prod.local)(PORT = 1522))
    )
  )

ADR_BASE_LISTENER = /u01/oracle

# listener.ora Network Configuration File: /u01/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = bdora-prod.prod.local)(PORT = 1522))

)

ADR_BASE_LISTENER = /u01/oracle

Solução. Acertar a porta no /u01/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = bdora-prod.prod.local)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/oracle

# listener.ora Network Configuration File: /u01/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = bdora-prod.prod.local)(PORT = 1521))

)

ADR_BASE_LISTENER = /u01/oracle

Impdp erro ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-39088: file name cannot contain a path specification

Publicado em 27 de fevereiro de 2020 by rodrigosilvaesilva

Erro ao realizar import Erro

[oracle@dbora bkp_logico]$ time impdp system/"senha"@OLTP  dumpfile='/u02/bkp_logico/OLTP.DMP' logfile=IMP_OLTP_27022020.log schemas=PROD,TESTE

Import: Release 19.0.0.0.0 - Production on Thu Feb 27 19:12:04 2020
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39088: file name cannot contain a path specification

[oracle@dbora bkp_logico]$ time impdp system/"senha"@OLTP dumpfile='/u02/bkp_logico/OLTP.DMP' logfile=IMP_OLTP_27022020.log schemas=PROD,TESTE

Import: Release 19.0.0.0.0 - Production on Thu Feb 27 19:12:04 2020

Version 19.5.0.0.0

Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production

ORA-39001: invalid argument value

ORA-39000: bad dump file specification

ORA-39088: file name cannot contain a path specification

Causa Usuário sys sem permissão Solução

SYSTEM@OLTP > grant read, write on DIRECTORY DIRECTORY to sys;

Grant succeeded.

SYSTEM@OLTP > grant read, write on DIRECTORY DIRECTORY to sys;

Grant succeeded.

Resolvido!

← Posts recentes Posts antigos →