outubro 2018 – Rodrigo Silva e Silva

Alterando senha usuário root MySQL

Publicado em 29 de outubro de 2018 by rodrigosilvaesilva

mysql> alter user 'root'@'localhost' IDENTIFIED BY 'nova_senha_complexa';
Query OK, 0 rows affected (0.00 sec)

1 2	mysql> alter user 'root'@'localhost' IDENTIFIED BY 'nova_senha_complexa'; Query OK, 0 rows affected (0.00 sec)

Resetar senha root Oracle Linux 6.9

Publicado em 22 de outubro de 2018 by rodrigosilvaesilva

Resetar senha root Oracle Linux 6.9 Pressione Enter Selecione a opção de Kernel “Oracle Linux Server Unbreakable Enterprise Kernerl e pressione E para editar Selecione a Opção kernel /vmlinuz.. e pressione E para editar Ao final da linha quiet adicione 1, conforme imagem abaixo e Pressione Enter para salvar. Nessa tela, aperte b no Kernel…

Script hugepages

Publicado em 12 de outubro de 2018 by rodrigosilvaesilva

Download Segue o script hugepages

#!/bin/bash
#
# hugepages_settings.sh
#
# Linux bash script to compute values for the
# recommended HugePages/HugeTLB configuration
# on Oracle Linux
#
# Note: This script does calculation for all shared memory
# segments available when the script is run, no matter it
# is an Oracle RDBMS shared memory segment or not.
#
# This script is provided by Doc ID 401749.1 from My Oracle Support 
# http://support.oracle.com

# Welcome text
echo "
This script is provided by Doc ID 401749.1 from My Oracle Support 
(http://support.oracle.com) where it is intended to compute values for 
the recommended HugePages/HugeTLB configuration for the current shared 
memory segments on Oracle Linux. Before proceeding with the execution please note following:
 * For ASM instance, it needs to configure ASMM instead of AMM.
 * The 'pga_aggregate_target' is outside the SGA and 
   you should accommodate this while calculating SGA size.
 * In case you changes the DB SGA size, 
   as the new SGA will not fit in the previous HugePages configuration, 
   it had better disable the whole HugePages, 
   start the DB with new SGA size and run the script again.
And make sure that:
 * Oracle Database instance(s) are up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not setup 
   (See Doc ID 749851.1)
 * The shared memory segments can be listed by command:
     # ipcs -m


Press Enter to proceed..."

read

# Check for the kernel version
KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`

# Find out the HugePage size
HPG_SZ=`grep Hugepagesize /proc/meminfo | awk '{print $2}'`
if [ -z "$HPG_SZ" ];then
    echo "The hugepages may not be supported in the system where the script is being executed."
    exit 1
fi

# Initialize the counter
NUM_PG=0

# Cumulative number of pages required to handle the running shared memory segments
for SEG_BYTES in `ipcs -m | cut -c44-300 | awk '{print $1}' | grep "[0-9][0-9]*"`
do
    MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
    if [ $MIN_PG -gt 0 ]; then
        NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
    fi
done

RES_BYTES=`echo "$NUM_PG * $HPG_SZ * 1024" | bc -q`

# An SGA less than 100MB does not make sense
# Bail out if that is the case
if [ $RES_BYTES -lt 100000000 ]; then
    echo "***********"
    echo "** ERROR **"
    echo "***********"
    echo "Sorry! There are not enough total of shared memory segments allocated for 
HugePages configuration. HugePages can only be used for shared memory segments 
that you can list by command:

    # ipcs -m

of a size that can match an Oracle Database SGA. Please make sure that:
 * Oracle Database instance is up and running 
 * Oracle Database 11g Automatic Memory Management (AMM) is not configured"
    exit 1
fi

# Finish with results
case $KERN in
    '2.2') echo "Kernel version $KERN is not supported. Exiting." ;;
    '2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
           echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
    '2.6') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '3.8') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '3.10') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '4.1') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
esac

# En

#!/bin/bash

# hugepages_settings.sh

# Linux bash script to compute values for the

# recommended HugePages/HugeTLB configuration

# on Oracle Linux

# Note: This script does calculation for all shared memory

# segments available when the script is run, no matter it

# is an Oracle RDBMS shared memory segment or not.

# This script is provided by Doc ID 401749.1 from My Oracle Support

# http://support.oracle.com

# Welcome text

echo "

This script is provided by Doc ID 401749.1 from My Oracle Support

(http://support.oracle.com) where it is intended to compute values for

the recommended HugePages/HugeTLB configuration for the current shared

memory segments on Oracle Linux. Before proceeding with the execution please note following:

* For ASM instance, it needs to configure ASMM instead of AMM.

* The 'pga_aggregate_target' is outside the SGA and

you should accommodate this while calculating SGA size.

* In case you changes the DB SGA size,

as the new SGA will not fit in the previous HugePages configuration,

it had better disable the whole HugePages,

start the DB with new SGA size and run the script again.

And make sure that:

* Oracle Database instance(s) are up and running

* Oracle Database 11g Automatic Memory Management (AMM) is not setup

(See Doc ID 749851.1)

* The shared memory segments can be listed by command:

# ipcs -m

Press Enter to proceed..."

read

# Check for the kernel version

KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`

# Find out the HugePage size

HPG_SZ=`grep Hugepagesize /proc/meminfo | awk '{print $2}'`

if [ -z "$HPG_SZ" ];then

echo "The hugepages may not be supported in the system where the script is being executed."

exit 1

# Initialize the counter

NUM_PG=0

# Cumulative number of pages required to handle the running shared memory segments

for SEG_BYTES in `ipcs -m | cut -c44-300 | awk '{print $1}' | grep "[0-9][0-9]*"`

MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`

if [ $MIN_PG -gt 0 ]; then

NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`

done

RES_BYTES=`echo "$NUM_PG * $HPG_SZ * 1024" | bc -q`

# An SGA less than 100MB does not make sense

# Bail out if that is the case

if [ $RES_BYTES -lt 100000000 ]; then

echo "***********"

echo "** ERROR **"

echo "***********"

echo "Sorry! There are not enough total of shared memory segments allocated for

HugePages configuration. HugePages can only be used for shared memory segments

that you can list by command:

# ipcs -m

of a size that can match an Oracle Database SGA. Please make sure that:

* Oracle Database instance is up and running

* Oracle Database 11g Automatic Memory Management (AMM) is not configured"

exit 1

# Finish with results

case $KERN in

'2.2') echo "Kernel version $KERN is not supported. Exiting." ;;

'2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;

echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;

'2.6') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;

'3.8') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;

'3.10') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;

'4.1') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;

esac

# En

Fonte Oracle Support

Statspack Idle Events Data Guard: Timer, pman timer, Data Guard: Gap Manager, log file sync e CPU time

Publicado em 10 de outubro de 2018 by rodrigosilvaesilva

Ao analisar o Statspack me deparei com os seguintes eventos

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
Data Guard: Gap Manager                            180      10,800  60001   25.8
Data Guard: Timer                                   18      10,800 ######   25.8
pman timer                                       3,600      10,799   3000   25.8
CPU time                                                     6,565          15.7
db file parallel read                           11,095       1,329    120    3.2
          -------------------------------------------------------------

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) Time

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

Data Guard: Gap Manager 180 10,800 60001 25.8

Data Guard: Timer 18 10,800 ###### 25.8

pman timer 3,600 10,799 3000 25.8

CPU time 6,565 15.7

db file parallel read 11,095 1,329 120 3.2

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

Causa Este evento está sendo investigado bug: Bug 28523746 Solução Adicionar as seguintes linhas de eventos ociosos manualmente Oracle 12.2

insert into STATS$IDLE_EVENT (event) values ('ASM cluster membership changes');
insert into STATS$IDLE_EVENT (event) values ('Backup Appliance Comm SGA setup wait');
insert into STATS$IDLE_EVENT (event) values ('Backup Appliance Servlet wait');
insert into STATS$IDLE_EVENT (event) values ('Backup Appliance Surrogate wait');
insert into STATS$IDLE_EVENT (event) values ('Backup Appliance waiting for work');
insert into STATS$IDLE_EVENT (event) values ('Backup Appliance waiting restore start');
insert into STATS$IDLE_EVENT (event) values ('DNFS disp IO slave idle');
insert into STATS$IDLE_EVENT (event) values ('Data Guard: Gap Manager');
insert into STATS$IDLE_EVENT (event) values ('Data Guard: Timer');
insert into STATS$IDLE_EVENT (event) values ('Data Guard: controlfile update');
insert into STATS$IDLE_EVENT (event) values ('IMFS defer writes scheduler');
insert into STATS$IDLE_EVENT (event) values ('LMS CR slave timer');
insert into STATS$IDLE_EVENT (event) values ('OFS Receive Queue');
insert into STATS$IDLE_EVENT (event) values ('PL/SQL lock timer');
insert into STATS$IDLE_EVENT (event) values ('SCM slave idle');
insert into STATS$IDLE_EVENT (event) values ('Sharded  Queues : Part Truncate idle');
insert into STATS$IDLE_EVENT (event) values ('Streams AQ: opt idle');
insert into STATS$IDLE_EVENT (event) values ('block compare coord process idle');
insert into STATS$IDLE_EVENT (event) values ('gopp msg');
insert into STATS$IDLE_EVENT (event) values ('nologging fetch slave idle');
insert into STATS$IDLE_EVENT (event) values ('pman timer');
insert into STATS$IDLE_EVENT (event) values ('recovery coordinator idle');
insert into STATS$IDLE_EVENT (event) values ('recovery logmerger idle');
insert into STATS$IDLE_EVENT (event) values ('recovery receiver idle');
insert into STATS$IDLE_EVENT (event) values ('recovery sender idle');
commit;

insert into STATS$IDLE_EVENT (event) values ('ASM cluster membership changes');

insert into STATS$IDLE_EVENT (event) values ('Backup Appliance Comm SGA setup wait');

insert into STATS$IDLE_EVENT (event) values ('Backup Appliance Servlet wait');

insert into STATS$IDLE_EVENT (event) values ('Backup Appliance Surrogate wait');

insert into STATS$IDLE_EVENT (event) values ('Backup Appliance waiting for work');

insert into STATS$IDLE_EVENT (event) values ('Backup Appliance waiting restore start');

insert into STATS$IDLE_EVENT (event) values ('DNFS disp IO slave idle');

insert into STATS$IDLE_EVENT (event) values ('Data Guard: Gap Manager');

insert into STATS$IDLE_EVENT (event) values ('Data Guard: Timer');

insert into STATS$IDLE_EVENT (event) values ('Data Guard: controlfile update');

insert into STATS$IDLE_EVENT (event) values ('IMFS defer writes scheduler');

insert into STATS$IDLE_EVENT (event) values ('LMS CR slave timer');

insert into STATS$IDLE_EVENT (event) values ('OFS Receive Queue');

insert into STATS$IDLE_EVENT (event) values ('PL/SQL lock timer');

insert into STATS$IDLE_EVENT (event) values ('SCM slave idle');

insert into STATS$IDLE_EVENT (event) values ('Sharded Queues : Part Truncate idle');

insert into STATS$IDLE_EVENT (event) values ('Streams AQ: opt idle');

insert into STATS$IDLE_EVENT (event) values ('block compare coord process idle');

insert into STATS$IDLE_EVENT (event) values ('gopp msg');

insert into STATS$IDLE_EVENT (event) values ('nologging fetch slave idle');

insert into STATS$IDLE_EVENT (event) values ('pman timer');

insert into STATS$IDLE_EVENT (event) values ('recovery coordinator idle');

insert into STATS$IDLE_EVENT (event) values ('recovery logmerger idle');

insert into STATS$IDLE_EVENT (event) values ('recovery receiver idle');

insert into STATS$IDLE_EVENT (event) values ('recovery sender idle');

commit;

Oracle 18.8

insert into STATS$IDLE_EVENT (event) values ('DBWR timer');
insert into STATS$IDLE_EVENT (event) values ('LogMiner merger: idle');
insert into STATS$IDLE_EVENT (event) values ('memoptimize write drain idle');
commit;

insert into STATS$IDLE_EVENT (event) values ('DBWR timer');

insert into STATS$IDLE_EVENT (event) values ('LogMiner merger: idle');

insert into STATS$IDLE_EVENT (event) values ('memoptimize write drain idle');

commit;

Fonte: Oracle Support

ERROR ORA-01555

Publicado em 9 de outubro de 2018 by rodrigosilvaesilva

Hoje um cliente reportou o seguinte erro ORA-01555 segue abaixo: Analisando verifiquei que o UNDO está em 900 por padrão.

 SYS@ORCL > show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900

SYS@ORCL > show parameter undo_retention

NAME TYPE VALUE

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

undo_retention integer 900

Alterando para 3600

SYS@ORCL> alter system set undo_retention=3600 scope=both;

System altered.

SYS@ORCL> alter system set undo_retention=3600 scope=both;

System altered.

Verificando parameter undo_retention

SYS@ORCL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     3600

SYS@ORCL> show parameter undo_retention

NAME TYPE VALUE

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

undo_retention integer 3600

Em conversa com o cliente o mesmo estava realizando cargas de campanhas.

Posts antigos →