abril 2019 – Rodrigo Silva e Silva

ORA-12713: Character data loss in NCHAR/CHAR conversion

Publicado em 17 de abril de 2019 by rodrigosilvaesilva

Erro ORA-12713: Character data loss in NCHAR/CHAR conversion

channel ORA_DISK_1: starting piece 1 at 13-APR-19
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/13/2019 12:17:45
ORA-12713: Character data loss in NCHAR/CHAR conversion

channel ORA_DISK_1: starting piece 1 at 13-APR-19

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/13/2019 12:17:45

ORA-12713: Character data loss in NCHAR/CHAR conversion

Causa: Identificado que as aspas estava invertidas. BACKUP DATABASE TAG ‘NOCOMPRESS’; Solução: Acerta aspas para ” BACKUP DATABASE TAG ‘NOCOMPRESS’; Resolvido!

Error ORA-19806: cannot make duplex backups in recovery area

Publicado em 16 de abril de 2019 by rodrigosilvaesilva

ORA-19806: cannot make duplex backups in recovery area

channel ORA_DISK_1: starting piece 1 at 13-APR-19
RMAN-00571: ===================================================                                                                        ========
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =======                                                                        ========
RMAN-00571: ===================================================                                                                        ========
RMAN-03009: failure of backup command on ORA_DISK_1 channel at                                                                         04/13/2019 01:01:14
ORA-19806: cannot make duplex backups in recovery area

channel ORA_DISK_1: starting piece 1 at 13-APR-19

RMAN-00571: =================================================== ========

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ======= ========

RMAN-00571: =================================================== ========

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/13/2019 01:01:14

ORA-19806: cannot make duplex backups in recovery area

List parâmetros

RMAN> SHOW ALL;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE 'DISK' TO 2;

RMAN> SHOW ALL;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE 'DISK' TO 2;

Causa: Backup duplex para a área de recuperação não é suportado. Solução: Remover a opção duplex.

Drop ACL Oracle

Publicado em 15 de abril de 2019 by rodrigosilvaesilva

Verificando ACL

SQL>
SELECT PRINCIPAL, HOST, lower_port, upper_port, acl, 'connect' AS PRIVILEGE,
    DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'connect'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
    JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID)
UNION ALL
SELECT PRINCIPAL, HOST, NULL lower_port, NULL upper_port, acl, 'resolve' AS PRIVILEGE,
    DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'resolve'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
    JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID);


PRINCIPA HOST  LOWER_PORT UPPER_PORT ACL                    PRIVILEGE    PRIVILE
-------- ----- ---------- ---------- ---------------------- ------------ -------
CPD      *                           /sys/acls/ACL_TI.xml  connect      GRANTED
CPD      *                           /sys/acls/ACL_TI.xml  connect      GRANTED
CPD      *                           /sys/acls/ACL_TI.xml  resolve      GRANTED
CPD      *                           /sys/acls/ACL_TI.xml  resolve      GRANTED

SQL>

SELECT PRINCIPAL, HOST, lower_port, upper_port, acl, 'connect' AS PRIVILEGE,

DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'connect'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS

FROM DBA_NETWORK_ACLS

JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID)

UNION ALL

SELECT PRINCIPAL, HOST, NULL lower_port, NULL upper_port, acl, 'resolve' AS PRIVILEGE,

DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'resolve'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS

FROM DBA_NETWORK_ACLS

JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID);

PRINCIPA HOST LOWER_PORT UPPER_PORT ACL PRIVILEGE PRIVILE

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

CPD * /sys/acls/ACL_TI.xml connect GRANTED

CPD * /sys/acls/ACL_TI.xml resolve GRANTED

Dropando ACL

SQL>
BEGIN
   DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
      acl => '/sys/acls/ACL_TI.xml');
   END;
/

PL/SQL procedure successfully completed.

SQL>

BEGIN

DBMS_NETWORK_ACL_ADMIN.DROP_ACL(

acl => '/sys/acls/ACL_TI.xml');

END;

PL/SQL procedure successfully completed.

Verificando ACL

SQL>
SELECT PRINCIPAL, HOST, lower_port, upper_port, acl, 'connect' AS PRIVILEGE,
    DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'connect'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
    JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID)
UNION ALL
SELECT PRINCIPAL, HOST, NULL lower_port, NULL upper_port, acl, 'resolve' AS PRIVILEGE,
    DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'resolve'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
  9  JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID);

no rows selected

SQL>

SELECT PRINCIPAL, HOST, lower_port, upper_port, acl, 'connect' AS PRIVILEGE,

DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'connect'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS

FROM DBA_NETWORK_ACLS

JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID)

UNION ALL

SELECT PRINCIPAL, HOST, NULL lower_port, NULL upper_port, acl, 'resolve' AS PRIVILEGE,

DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'resolve'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS

FROM DBA_NETWORK_ACLS

9 JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID);

no rows selected

Create ACL Oracle

Publicado em 15 de abril de 2019 by rodrigosilvaesilva

Create ACL Oracle

begin
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl => 'ACL_TEST.xml',
    description => 'Allow acl_viacep',
    principal => 'TI',
    is_grant => true,
    privilege => 'connect'
    );
  
  dbms_network_acl_admin.add_privilege (
  acl => 'ACL_TEST.xml',
  principal => 'TI',
  is_grant => true,
  privilege => 'resolve'
  );
 
  dbms_network_acl_admin.assign_acl(
  acl  => 'ACL_TEST.xml',
  host => '*'
  );
  commit;
end;
/

PL/SQL procedure successfully completed.

begin

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(

acl => 'ACL_TEST.xml',

description => 'Allow acl_viacep',

principal => 'TI',

is_grant => true,

privilege => 'connect'

);

dbms_network_acl_admin.add_privilege (

acl => 'ACL_TEST.xml',

principal => 'TI',

is_grant => true,

privilege => 'resolve'

);

dbms_network_acl_admin.assign_acl(

acl => 'ACL_TEST.xml',

host => '*'

);

commit;

end;

PL/SQL procedure successfully completed.

Set ACL Owners

Setando as ACLs para outro Owner
begin 
  dbms_network_acl_admin.add_privilege (
  acl => 'ACL_TESTE.xml',
  principal => 'TI',
  is_grant => true,
  privilege => 'resolve'
  );

  dbms_network_acl_admin.add_privilege (
  acl => 'ACL_TESTE.xml',
  principal => 'TI',
  is_grant => true,
  privilege => 'connect'
  );

  dbms_network_acl_admin.assign_acl(
  acl  => 'ACL_TESTE.xml',
  host => '*'
  );
  commit;
end;
/

Setando as ACLs para outro Owner

begin

dbms_network_acl_admin.add_privilege (

acl => 'ACL_TESTE.xml',

principal => 'TI',

is_grant => true,

privilege => 'resolve'

);

dbms_network_acl_admin.add_privilege (

acl => 'ACL_TESTE.xml',

principal => 'TI',

is_grant => true,

privilege => 'connect'

);

dbms_network_acl_admin.assign_acl(

acl => 'ACL_TESTE.xml',

host => '*'

);

commit;

end;

Check ACL

column owner format a12;
column index_name format a18;
column status format a10;
column domidx_status format a10;
column domidx_opstatus format a10;
column funcidx_status format a10;

SQL> select acl,principal,privilege,is_grant from DBA_NETWORK_ACL_PRIVILEGES;

ACL                    PRINCIPA PRIVILEGE    IS_GR
---------------------- -------- ------------ -----
/sys/acls/ACL_TEST.xml  TI      connect      true
/sys/acls/ACL_TEST.xml  TI      resolve      true

SQL> select acl,host,lower_port,upper_port from DBA_NETWORK_ACLS;

ACL                    HOST  LOWER_PORT UPPER_PORT
---------------------- ----- ---------- ----------
/sys/acls/ACL_TEST.xml  *

column owner format a12;

column index_name format a18;

column status format a10;

column domidx_status format a10;

column domidx_opstatus format a10;

column funcidx_status format a10;

SQL> select acl,principal,privilege,is_grant from DBA_NETWORK_ACL_PRIVILEGES;

ACL PRINCIPA PRIVILEGE IS_GR

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

/sys/acls/ACL_TEST.xml TI connect true

/sys/acls/ACL_TEST.xml TI resolve true

SQL> select acl,host,lower_port,upper_port from DBA_NETWORK_ACLS;

ACL HOST LOWER_PORT UPPER_PORT

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

/sys/acls/ACL_TEST.xml *

Check permissão ACLs, DBA_NETWORK_ACL_PRIVILEGES e DBA_NETWORK_ACLS

Publicado em 15 de abril de 2019 by rodrigosilvaesilva

Check permissão DBA_NETWORK_ACL_PRIVILEGES e DBA_NETWORK_ACLS Formatando visualização

column ACL format a25;
column PRINCIPAL formata20;
column PRINCIPAL format a20;
column PRIVILEGE format a20;
column HOST format a20;

column ACL format a25;

column PRINCIPAL formata20;

column PRINCIPAL format a20;

column PRIVILEGE format a20;

column HOST format a20;

Checando DBA_NETWORK_ACL_PRIVILEGES

SQL> select acl,principal,privilege,is_grant from DBA_NETWORK_ACL_PRIVILEGES;

ACL                       PRINCIPAL            PRIVILEGE            IS_GR
------------------------- -------------------- -------------------- -----
/sys/acls/ACL_TI.xml        TI                  connect              true
/sys/acls/ACL_TI.xml        TI                  resolve              true

SQL> select acl,principal,privilege,is_grant from DBA_NETWORK_ACL_PRIVILEGES;

ACL PRINCIPAL PRIVILEGE IS_GR

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

/sys/acls/ACL_TI.xml TI connect true

/sys/acls/ACL_TI.xml TI resolve true

Checando DBA_NETWORK_ACLS

SQL> select acl,host,lower_port,upper_port from DBA_NETWORK_ACLS;

ACL                       HOST                 LOWER_PORT UPPER_PORT
------------------------- -------------------- ---------- ----------
/sys/acls/ACL_TI.xml       *

SQL> select acl,host,lower_port,upper_port from DBA_NETWORK_ACLS;

ACL HOST LOWER_PORT UPPER_PORT

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

/sys/acls/ACL_TI.xml *

Posts antigos →