abril 2018 – Rodrigo Silva e Silva

ORA-02095: specified initialization parameter cannot be modified

Publicado em 25 de abril de 2018 by rodrigosilvaesilva

OPTIMIZER_ADAPTIVE_FEATURES obsoleto no Oracle 12.2 No Oracle 12.2, o parâmetro OPTIMIZER_ADAPTIVE_FEATURES foi removido e substituído por dois novos parâmetros OPTIMIZER_ADAPTIVE_PLANS: Padrão (TRUE). Ativa / desativa planos adaptativos, poda de bitmap de transformação de estrela e o método de distribuição adaptativa paralela. OPTIMIZER_ADAPTIVE_STATISTICS: Padrão (FALSE). Ativa / desativa diretivas de plano de SQL, feedback de estatísticas…

ORA-01017 – SEC_CASE_SENSITIVE_LOGON

Publicado em 24 de abril de 2018 by rodrigosilvaesilva

Quando é habilitado o parametro de SEC_CASE_SENSITIVE_LOGON no Oracle 12.2.0.1, o usuário SYS para de autenticar. Para contornar esse problema aplicamos a seguinte solução desabilitando senha case sensitive

SQL> alter system set sec_case_sensitive_logon=false scope=spfile;
SQL> 
SQL> shutdown immediate; 
SQL> 
SQL> startup 
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

SQL> alter system set sec_case_sensitive_logon=false scope=spfile;

SQL>

SQL> shutdown immediate;

SQL>

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

Editar o arquivo sqlnet.ora

[oracle@bdora ~]$ vim $ORACLE_HOME/network/admin/sqlnet.ora

Adicionar linha abaixo
 
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

[oracle@bdora ~]$ vim $ORACLE_HOME/network/admin/sqlnet.ora

Adicionar linha abaixo

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

Verificando parâmetro de PASSWORD_VERSION

SQL > select username, password_versions from DBA_USERS where username IN ('SYSTEM','SYS');

USERNAME   PASSWORD_VERSIONS
---------- -----------------
SYS        10G 11G 12C
SYSTEM     10G 11G 12C

SQL > select username, password_versions from DBA_USERS where username IN ('SYSTEM','SYS');

USERNAME PASSWORD_VERSIONS

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

SYS 10G 11G 12C

SYSTEM 10G 11G 12C

É necessário alterar a senha do usuário.

alter user NOME_USUARIO identified by SENHA;

1	alter user NOME_USUARIO identified by SENHA;

Fonte: André Spadini

DataPump erro ORA-31633: unable to create master table “SYSTEM.SYS_IMPORT_SCHEMA_06”

Publicado em 24 de abril de 2018 by rodrigosilvaesilva

[oracle@bdora]$ time impdp system/senha schemas=Nome directory=BKPDIR dumpfile=NOME_SCHEMA.dmp logfile=impNovo.log EXCLUDE=STATISTICS

Import: Release 11.2.0.2.0 - Production on Tue Apr 24 10:12:49 2018

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

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.SYS_IMPORT_SCHEMA_06"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-01658: unable to create INITIAL extent for segment in tablespace SYSTEM

real    0m0.397s
user    0m0.022s
sys     0m0.022s

[oracle@bdora]$ time impdp system/senha schemas=Nome directory=BKPDIR dumpfile=NOME_SCHEMA.dmp logfile=impNovo.log EXCLUDE=STATISTICS

Import: Release 11.2.0.2.0 - Production on Tue Apr 24 10:12:49 2018

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

ORA-31626: job does not exist

ORA-31633: unable to create master table "SYSTEM.SYS_IMPORT_SCHEMA_06"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPV$FT", line 1020

ORA-01658: unable to create INITIAL extent for segment in tablespace SYSTEM

real 0m0.397s

user 0m0.022s

sys 0m0.022s

Visualizando eventos ddl

SYS@XE > select owner, object_name, object_type, status from dba_objects where lower(object_name) like '%evs_monitoring_ddl';
no rows selected

1 2	SYS@XE > select owner, object_name, object_type, status from dba_objects where lower(object_name) like '%evs_monitoring_ddl'; no rows selected

Verificando fila de execução datapump

column owner_name format a20;
column job_name format a20;
column operation format a10;
column job_mode format a10;
column state format a15;
column attached_sessions format a10;

column owner_name format a20;

column job_name format a20;

column operation format a10;

column job_mode format a10;

column state format a15;

column attached_sessions format a10;

SYS@XE > SELECT owner_name, job_name, operation, job_mode, state, attached_sessions
FROM dba_datapump_jobs;

OWNER_NAME           JOB_NAME             OPERATION  JOB_MODE   STATE           ATTACHED_SESSIONS
-------------------- -------------------- ---------- ---------- --------------- -----------------
SYSTEM               SYS_IMPORT_SCHEMA_01 IMPORT     SCHEMA     NOT RUNNING            ##########

SYS@XE > SELECT owner_name, job_name, operation, job_mode, state, attached_sessions

FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS

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

SYSTEM SYS_IMPORT_SCHEMA_01 IMPORT SCHEMA NOT RUNNING ##########

Dropando table system.SYS_IMPORT_SCHEMA_01

SYS@XE > drop table system.SYS_IMPORT_SCHEMA_01;
Table dropped.

1 2	SYS@XE > drop table system.SYS_IMPORT_SCHEMA_01; Table dropped.

Verificando Jobs

SYS@XE > select owner_name,job_name,operation,job_mode,state,attached_sessions from dba_datapump_jobs;
no rows selected

1 2	SYS@XE > select owner_name,job_name,operation,job_mode,state,attached_sessions from dba_datapump_jobs; no rows selected

Listar espaço Livre Tablespaces

Publicado em 23 de abril de 2018 by rodrigosilvaesilva

Select para listar espaço disponível Tablespaces

column dummy noprint
column pct_used format 999.9 heading "%|Used"
column name format a16 heading "Tablespace Name"
column Kbytes format 999,999,999 heading "KBytes"
column used format 999,999,999 heading "Used"
column free format 999,999,999 heading "Free"
column largest format 999,999,999 heading "Largest"
break on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report

column dummy noprint

column pct_used format 999.9 heading "%|Used"

column name format a16 heading "Tablespace Name"

column Kbytes format 999,999,999 heading "KBytes"

column used format 999,999,999 heading "Used"

column free format 999,999,999 heading "Free"

column largest format 999,999,999 heading "Largest"

break on report

compute sum of kbytes on report

compute sum of free on report

compute sum of used on report

SYS@XE >
select nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
nvl(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/
kbytes_alloc)*100 pct_used,
nvl(largest,0) largest
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
tablespace_name
from sys.dba_data_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by &1
/

SYS@XE >

select nvl(b.tablespace_name,

nvl(a.tablespace_name,'UNKOWN')) name,

kbytes_alloc kbytes,

kbytes_alloc-nvl(kbytes_free,0) used,

nvl(kbytes_free,0) free,

((kbytes_alloc-nvl(kbytes_free,0))/

kbytes_alloc)*100 pct_used,

nvl(largest,0) largest

from ( select sum(bytes)/1024 Kbytes_free,

max(bytes)/1024 largest,

tablespace_name

from sys.dba_free_space

group by tablespace_name ) a,

( select sum(bytes)/1024 Kbytes_alloc,

tablespace_name

from sys.dba_data_files

group by tablespace_name )b

where a.tablespace_name (+) = b.tablespace_name

order by &1

Enter value for 1: 1
old  19: order by &1
new  19: order by 1

                                                             %
Tablespace Name        KBytes         Used         Free   Used      Largest
---------------- ------------ ------------ ------------ ------ ------------
PROD01_DATA         4,706,304    4,337,664      368,640   92.2      368,640
PROD01_INDX         5,242,880        4,096    5,238,784     .1    2,096,128
SYSAUX                675,840      638,784       37,056   94.5       36,864
SYSTEM                614,400      613,568          832   99.9          832
UNDOTBS1              179,200      155,456       23,744   86.8        4,096
USERS                 102,400        2,624       99,776    2.6       99,776
                 ------------ ------------ ------------
sum                11,521,024    5,752,192    5,768,832

6 rows selected.

Enter value for 1: 1

old 19: order by &1

new 19: order by 1

Tablespace Name KBytes Used Free Used Largest

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

PROD01_DATA 4,706,304 4,337,664 368,640 92.2 368,640

PROD01_INDX 5,242,880 4,096 5,238,784 .1 2,096,128

SYSAUX 675,840 638,784 37,056 94.5 36,864

SYSTEM 614,400 613,568 832 99.9 832

UNDOTBS1 179,200 155,456 23,744 86.8 4,096

USERS 102,400 2,624 99,776 2.6 99,776

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

sum 11,521,024 5,752,192 5,768,832

6 rows selected.

Kbytes = espaço alocado para o espaço de tabela atualmente.
Usado = espaço alocado dentro do espaço de tabela para específico
objetos
Livre = espaço ainda não alocado para qualquer objeto
Usado =% do espaço alocado para objetos no espaço de tabela
Maior = Maior extensão contígua gratuita disponível (NEXT_EXTENTS
maior então isso irá falhar

Kbytes = espaço alocado para o espaço de tabela atualmente.

Usado = espaço alocado dentro do espaço de tabela para específico

objetos

Livre = espaço ainda não alocado para qualquer objeto

Usado =% do espaço alocado para objetos no espaço de tabela

Maior = Maior extensão contígua gratuita disponível (NEXT_EXTENTS

maior então isso irá falhar

Fonte: Ask Tom

Destruindo Interface virbr0

Publicado em 19 de abril de 2018 by rodrigosilvaesilva

Listando interface virbr0

[root@bd01 ~]# ifconfig virbr0
virbr0: flags=4099<UP,BROADCAST,MULTICAST>  mtu 1500
        inet 192.168.122.1  netmask 255.255.255.0  broadcast 192.168.122.255
        ether 52:54:00:f0:c0:a2  txqueuelen 0  (Ethernet)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@bd01 ~]# ifconfig virbr0

virbr0: flags=4099<UP,BROADCAST,MULTICAST> mtu 1500

inet 192.168.122.1 netmask 255.255.255.0 broadcast 192.168.122.255

ether 52:54:00:f0:c0:a2 txqueuelen 0 (Ethernet)

RX packets 0 bytes 0 (0.0 B)

RX errors 0 dropped 0 overruns 0 frame 0

TX packets 0 bytes 0 (0.0 B)

TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

Listar rede padrão

[root@bd01 ~]# virsh net-list
bash: virsh: command not found...

1 2	[root@bd01 ~]# virsh net-list bash: virsh: command not found...

Instalar o pacote virsh

[root@bd01 ~]# yum install libvirt

Dependencies Resolved

==========================================================================================================================
 Package                                    Arch               Version                       Repository              Size
==========================================================================================================================
Installing:
 libvirt                                    x86_64             3.9.0-14.el7_5.2              ol7_latest             172 k
Installing for dependencies:
 autogen-libopts                            x86_64             5.18-5.el7                    ol7_latest              65 k
 gnutls-dane                                x86_64             3.3.26-9.0.1.el7              ol7_latest              34 k
 gnutls-utils                               x86_64             3.3.26-9.0.1.el7              ol7_latest             236 k
 libvirt-client                             x86_64             3.9.0-14.el7_5.2              ol7_latest             457 k
 libvirt-daemon-config-nwfilter             x86_64             3.9.0-14.el7_5.2              ol7_latest             178 k
 libvirt-daemon-driver-lxc                  x86_64             3.9.0-14.el7_5.2              ol7_latest             899 k

Transaction Summary
==========================================================================================================================
Install  1 Package (+6 Dependent packages)

Total download size: 2.0 M
Installed size: 3.8 M
Is this ok [y/d/N]: y

[root@bd01 ~]# yum install libvirt

Dependencies Resolved

==========================================================================================================================

Package Arch Version Repository Size

==========================================================================================================================

Installing:

libvirt x86_64 3.9.0-14.el7_5.2 ol7_latest 172 k

Installing for dependencies:

autogen-libopts x86_64 5.18-5.el7 ol7_latest 65 k

gnutls-dane x86_64 3.3.26-9.0.1.el7 ol7_latest 34 k

gnutls-utils x86_64 3.3.26-9.0.1.el7 ol7_latest 236 k

libvirt-client x86_64 3.9.0-14.el7_5.2 ol7_latest 457 k

libvirt-daemon-config-nwfilter x86_64 3.9.0-14.el7_5.2 ol7_latest 178 k

libvirt-daemon-driver-lxc x86_64 3.9.0-14.el7_5.2 ol7_latest 899 k

Transaction Summary

==========================================================================================================================

Install 1 Package (+6 Dependent packages)

Total download size: 2.0 M

Installed size: 3.8 M

Is this ok [y/d/N]: y

Listar rede padrão

[root@bd01 ~]# virsh net-list
 Name                 State      Autostart     Persistent
----------------------------------------------------------
 default              active     yes           yes

[root@bd01 ~]# virsh net-list

Name State Autostart Persistent

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

default active yes yes

Destruir a rede padrão

[root@bd01 ~]# virsh net-destroy default
Network default destroyed

1 2	[root@bd01 ~]# virsh net-destroy default Network default destroyed

Remova permanentemente a rede virtual padrão da configuração.

[root@bd01 ~]# virsh net-undefine default
Network default has been undefined

1 2	[root@bd01 ~]# virsh net-undefine default Network default has been undefined

Listando interface virbr0

[root@bd01 ~]# ifconfig virbr0
virbr0: error fetching interface information: Device not found

1 2	[root@bd01 ~]# ifconfig virbr0 virbr0: error fetching interface information: Device not found

Posts antigos →