19c Encrypt tablespace online with error code on the alert log ORA-01578/ORA-376/Corrupt

 When you have a issue or error encrypting tablespace online on exacc (exadata) like this:

(from alert log)
ALTER TABLESPACE XXXXX encryption online encrypt ;
ORA-00376: file xxxx cannot be read at this time

Maybe the other error codes it could be ORA-01578/ORA-376/Corrupt Block messages on the alert log. If you have this issue on your exadata db 19c (in my case 19.16) that is caused for a bug. The current workaround is disable the SQL processing offload on Exadata Storage Server while momentarily you are executing the online encryption tablespaces (This bug is only present when you encrypt online)

alter session set container=XX_your_pdb_name_xxx ;
alter system set CELL_OFFLOAD_PROCESSING=FALSE scope=memory sid='*';
ALTER TABLESPACE XXXXX encryption online encrypt ;
alter system set CELL_OFFLOAD_PROCESSING=TRUE  scope=memory sid='*';

Note: if you cancel the last execution of your encrypt you need to retake the last encrypt using the command FINISH:

ALTER TABLESPACE XXXXX ENCRYPTION ONLINE FINISH ENCRYPT ;

 
I hope this help you.

Oracle Cloud - Connect with our ADB through sqlplus

Connect with our ADB through sqlplus

First, we need to download our wallet_*.zip in order to connect to ADB. From our OCI Console on our ADB Resource, from the button “DB Connection” we proceed to download the zip file.

Press the Download button:

And we need put a password for this wallet:

Once that is ready, we need to copy that file to a folder on our local Machine and decompress that file.

After we need to go the decompress folder

Now, we need open sqlnet.ora and replace the next path for our case:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/Users/felipedonoso/oracle_FDB_ToolKit/TNS_ADMIN_CLOUD/wallet_dbatp")))

SSL_SERVER_DN_MATCH=yes

After we need configure the TNS_ADMIN variable environment with our respective folder (or add that in our bash profile ):

export TNS_ADMIN="/Users/felipedonoso/oracle_FDB_ToolKit/TNS_ADMIN_CLOUD/wallet_dbatp"

Now we can connect to new environment using our credentials and with any service from our tnsnames.ora (*_high, *_medium, *_tpurgent, etc..):

Connect with our database through sqldeveloper

For this case the situation is the same thing the last one chapter. We need our wallet_*.zip. We need too indicate the type of connection that in this case is “Cloud Wallet”, and indicate the path of our wallet file and the kind of service name  (*_high, *_medium, *_tpurgent, etc..). That’s all.

Best Regards, Felipe.

TRCANLZR (TRCA): SQL_TRACE/Event 10046 Trace File Analyzer

I want recommend you a tool very very useful for oracle traces analyzer, this tool is call: TRCANLZR (owner: Carlos Sierra, carlos.sierra@oracle.com). This tool allow you to summarize and describe trace files better than tkprof. It generates a HTML File with all information about the traces: top querys, top event waits, execution plans and much more:

Some Examples about the output after execution:

Full HTML Example about the output after execution traces analyzer:

https://drive.google.com/uc?export=download&id=0B8Tjo6f10a1WbEVDaTBSNUpwd1E

In Metalink can see information about this tool in the note:

TRCANLZR (TRCA): SQL_TRACE/Event 10046 Trace File Analyzer - Tool for Interpreting Raw SQL Traces (Doc ID 224270.1)

The Download link: https://mosemp.us.oracle.com/epmos/main/downloadattachmentprocessor?attachid=224270.1%3ATRCA_TOOL_11458&docType=TROUBLESHOOTING&action=download

The install tool is very simple:

1.- Uncompress the zip file.

2.- Enter the folder: trca/install

3.- Execute: sqlplus "/as sysdba" @tacreate.sql

This step is for create database schemas. The tool require a database instance.

4.- enter the folder diag/....trace/ (the folder where is located the database's alert log)

5.- In that directory copy the *.trc file what you will analyze.

6.- Go the folder trca/install

7.- Execute: sqlplus  "TRCANLZR/password_database" @trcanlzr.sql file_trace_to_analyzer.trc

8.- When the procesess finish, will be generated ZIP file on the folder trca\run

And open that zip file and you can see the HTML unique file with all results analyzer. (Example output: https://drive.google.com/uc?export=download&id=0B8Tjo6f10a1WbEVDaTBSNUpwd1E)

PD: For 9i trace files, you should to use this tool:
9i SQL Tuning Health Check Script SQLHC Doc ID 1366133.1


Link from author tool: https://carlos-sierra.net/2013/04/25/differences-between-tkprof-and-trace-analyzer-trcanlzr-trca/

Instalación y configuración del motor de base de datos redis

Hace poco me ha tocado realizar la instalación y configuración del motor redis una base de datos orientada a memoria y también ocupada como message broker. Esta instalación la realizaremos con el usuario root. Sólo algunas cosas las haremos con el usuario redis que crearemos más adelante.

Cómo usuario root procedemos a ejecutar (tomando en cuenta que mis instaladores los tengo en la ruta: /dbprdRedis):

[root@bci-bluemixdbredis01 tmp]# cd /dbprdRedis

[root@bci-bluemixdbredis01 dbprdRedis]# ls -lptr

total 1512

drwxr-xr-x 2 usr_dbredis root       6 May  3 04:03 bd/

-rwxr-xr-x 1 root        root 1547237 May 23 10:50 redis-3.2.8.tar.gz

(procedemos a descomprimir el fuente)

[root@bci-bluemixdbredis01 dbprdRedis]# tar xzvf redis-3.2.8.tar.gz

(ingresamos a la nueva carpeta e instalamos)

[root@bci-bluemixdbredis01 dbprdRedis]# cd redis-3.2.8/

[root@bci-bluemixdbredis01 redis-3.2.8]# make

cd src && make all

make[1]: Entering directory `/dbprdRedis/redis-3.2.8/src'

rm -rf redis-server redis-sentinel redis-cli redis-benchmark redis-check-rdb redis-check-aof *.o *.gcda *.gcno *.gcov redis.info lcov-html

(cd ../deps && make distclean)

make[2]: Entering directory `/dbprdRedis/redis-3.2.8/deps'

(cd hiredis && make clean) > /dev/null || true

(cd linenoise && make clean) > /dev/null || true

(cd lua && make clean) > /dev/null || true

(cd geohash-int && make clean) > /dev/null || true

(cd jemalloc && [ -f Makefile ] && make distclean) > /dev/null || true

(rm -f .make-*)

make[2]: Leaving directory `/dbprdRedis/redis-3.2.8/deps'

(rm -f .make-*)

echo STD=-std=c99 -pedantic -DREDIS_STATIC='' >> .make-settings

echo WARN=-Wall -W >> .make-settings

echo OPT=-O2 >> .make-settings

echo MALLOC=jemalloc >> .make-settings

echo CFLAGS= >> .make-settings

echo LDFLAGS= >> .make-settings

echo REDIS_CFLAGS= >> .make-settings

echo REDIS_LDFLAGS= >> .make-settings

echo PREV_FINAL_CFLAGS=-std=c99 -pedantic -DREDIS_STATIC='' -Wall -W -O2 -g -ggdb   -I../deps/geohash-int -I../deps/hiredis -I../deps/linenoise -I../deps/lua/src -DUSE_JEMALLOC -I../deps/jemalloc/include >> .make-settings

echo PREV_FINAL_LDFLAGS=  -g -ggdb -rdynamic >> .make-settings

(cd ../deps && make hiredis linenoise lua geohash-int jemalloc)

make[2]: Entering directory `/dbprdRedis/redis-3.2.8/deps'

(cd hiredis && make clean) > /dev/null || true

(cd linenoise && make clean) > /dev/null || true

(cd lua && make clean) > /dev/null || true

(cd geohash-int && make clean) > /dev/null || true

(cd jemalloc && [ -f Makefile ] && make distclean) > /dev/null || true

(rm -f .make-*)

(echo "" > .make-cflags)

(echo "" > .make-ldflags)

(Esto tomará algunos minutos y mostrará bastante output. Luego pedirá ejecutar make test)

[root@bci-bluemixdbredis01 redis-3.2.8]# make test

cd src && make test

make[1]: Entering directory `/dbprdRedis/redis-3.2.8/src'

You need tcl 8.5 or newer in order to run the Redis test

make[1]: *** [test] Error 1

make[1]: Leaving directory `/dbprdRedis/redis-3.2.8/src'

make: *** [test] Error 2

Esto requiere tener instalado tcl:

[root@bci-bluemixdbredis01 redis-3.2.8]# yum install tcl*

Una vez instalado se puede proseguir:

(también esto tomará algunos minutos)

[root@bci-bluemixdbredis01 redis-3.2.8]# make test

cd src && make test

make[1]: Entering directory `/dbprdRedis/redis-3.2.8/src'

Cleanup: may take some time... OK

Starting test server at port 11111

[ready]: 58210

Testing unit/printver

[ready]: 58219

Testing unit/dump

[ready]: 58213

Testing unit/auth

[ready]: 58216

Testing unit/protocol

[ready]: 58222

Testing unit/keyspace

[ready]: 58225

Testing unit/scan

[ready]: 58228

Testing unit/type/string

[ready]: 58230

Testing unit/type/incr

[ready]: 58234

Testing unit/type/list

[ready]: 58237

Finalmente se ejecuta el make install para dejar todos los binarios en el sistema:

[root@bci-bluemixdbredis01 redis-3.2.8]# make install

cd src && make install

make[1]: Entering directory `/dbprdRedis/redis-3.2.8/src'

Hint: It's a good idea to run 'make test' ;)

    INSTALL install

    INSTALL install

    INSTALL install

    INSTALL install

    INSTALL install

make[1]: Leaving directory `/dbprdRedis/redis-3.2.8/src'

Creamos el siguiente directorio y copiamos el siguiente archivo para proceder a editar:

[root@bci-bluemixdbredis01 redis-3.2.8]# mkdir /etc/redis

[root@bci-bluemixdbredis01 redis-3.2.8]# cp redis.conf /etc/redis

[root@bci-bluemixdbredis01 redis-3.2.8]# vi  /etc/redis/redis.conf

En dicho archivo modificaremos el parámetro supervised al valor systemd

# If you run Redis from upstart or systemd, Redis can interact with your

# supervision tree. Options:

#   supervised no      - no supervision interaction

#   supervised upstart - signal upstart by putting Redis into SIGSTOP mode

#   supervised systemd - signal systemd by writing READY=1 to $NOTIFY_SOCKET

#   supervised auto    - detect upstart or systemd method based on

#                        UPSTART_JOB or NOTIFY_SOCKET environment variables

# Note: these supervision methods only signal "process is ready."

#       They do not enable continuous liveness pings back to your supervisor.

supervised systemd

Modificaremos también el parámetro dir para señalar dónde se almacenarán nuestros dumps o archivos de bases de datos (que en mi caso es un disco por si solo)

# The working directory.

#

# The DB will be written inside this directory, with the filename specified

# above using the 'dbfilename' configuration directive.

#

# The Append Only File will also be created inside this directory.

#

# Note that you must specify a directory here, not a file name.

dir /dbprdRedis/bd

Modificaremos también el parámetro requirepass para que nos pida pass al momento de conectarnos al cliente:

# Require clients to issue AUTH <PASSWORD> before processing any other

# commands.  This might be useful in environments in which you do not trust

# others with access to the host running redis-server.

#

# This should stay commented out for backward compatibility and because most

# people do not need auth (e.g. they run their own servers).

#

# Warning: since Redis is pretty fast an outside user can try up to

# 150k passwords per second against a good box. This means that you should

# use a very strong password otherwise it will be very easy to break.

#

requirepass redis2017

Procederemos a crear luego el siguiente archivo:

[root@bci-bluemixdbredis01 bd]# vi /etc/systemd/system/redis.service

[root@bci-bluemixdbredis01 bd]#

Con el siguiente contenido:

[Unit]

Description=Redis In-Memory Data Store

After=network.target

[Service]

User=redis

Group=redis

ExecStart=/usr/local/bin/redis-server /etc/redis/redis.conf

ExecStop=/usr/local/bin/redis-cli shutdown

Restart=always

[Install]

WantedBy=multi-user.target

Ahora procederemos a crear el usuario redis:

[root@bci-bluemixdbredis01 bd]# mkdir /home/redis

[root@bci-bluemixdbredis01 bd]# chown redis:redis /home/redis

[root@bci-bluemixdbredis01 bd]# adduser --system -d /home/redis redis

[root@bci-bluemixdbredis01 bd]# echo secu13re | passwd --stdin redis

Changing password for user redis.

passwd: all authentication tokens updated successfully.

[root@bci-bluemixdbredis01 bd]# usermod -c 'redis_database_not_delete' redis

[root@bci-bluemixdbredis01 bd]# usermod -s /bin/bash redis

[root@bci-bluemixdbredis01 ~]# chmod 700  /home/redis

 

Ajustamos los permisos para la carpeta de base de datos:

[root@bci-bluemixdbredis01 bd]# chown redis:redis /dbprdRedis/bd

[root@bci-bluemixdbredis01 bd]# chmod 770 /dbprdRedis/bd

[root@bci-bluemixdbredis01 bd]#

Probamos que el sistema levante sin problemas el servicio redis:

[root@bci-bluemixdbredis01 bd]# systemctl start redis

[root@bci-bluemixdbredis01 bd]# ps -fea | grep -i redis

redis    61755     1  0 11:27 ?        00:00:00 /usr/local/bin/redis-server 127.0.0.1:6379

root     61759 55077  0 11:27 pts/0    00:00:00 grep --color=auto -i redis

[root@bci-bluemixdbredis01 bd]# systemctl status redis

● redis.service - Redis In-Memory Data Store

  Loaded: loaded (/etc/systemd/system/redis.service; disabled; vendor preset: disabled)

  Active: active (running) since Tue 2017-05-23 11:27:06 CLT; 15s ago

Main PID: 61755 (redis-server)

  CGroup: /system.slice/redis.service

          └─61755 /usr/local/bin/redis-server 127.0.0.1:6379

May 23 11:27:06 bci-bluemixdbredis01 redis-server[61755]: |    `-._`-._        _.-'_.-'    |

May 23 11:27:06 bci-bluemixdbredis01 redis-server[61755]: `-._    `-._`-.__.-'_.-'    _.-'

May 23 11:27:06 bci-bluemixdbredis01 redis-server[61755]: `-._    `-.__.-'    _.-'

May 23 11:27:06 bci-bluemixdbredis01 redis-server[61755]: `-._        _.-'

May 23 11:27:06 bci-bluemixdbredis01 redis-server[61755]: `-.__.-'

May 23 11:27:06 bci-bluemixdbredis01 redis-server[61755]: 61755:M 23 May 11:27:06.589 # WARNING: The TCP backlog setting of 511 cannot be enforced because /proc/sys/net/core/somaxconn is set to the lower value of 128.

May 23 11:27:06 bci-bluemixdbredis01 redis-server[61755]: 61755:M 23 May 11:27:06.589 # Server started, Redis version 3.2.8

May 23 11:27:06 bci-bluemixdbredis01 redis-server[61755]: 61755:M 23 May 11:27:06.589 # WARNING overcommit_memory is set to 0! Background save may fail under low memory condition. To fix this issue add 'vm.ov...o take effect.

May 23 11:27:06 bci-bluemixdbredis01 redis-server[61755]: 61755:M 23 May 11:27:06.589 # WARNING you have Transparent Huge Pages (THP) support enabled in your kernel. This will create latency and memory usage issues with Re...

May 23 11:27:06 bci-bluemixdbredis01 redis-server[61755]: 61755:M 23 May 11:27:06.589 * The server is now ready to accept connections on port 6379

Hint: Some lines were ellipsized, use -l to show in full.

[root@bci-bluemixdbredis01 bd]#

Ahora probaremos si funciona convirtiéndonos en el usuario de so redis:

[root@bci-bluemixdbredis01 bd]# su - redis

Last login: Tue May 23 11:33:03 CLT 2017 on pts/0

-bash-4.2$ redis-cli

127.0.0.1:6379> ping

PONG

127.0.0.1:6379> set test "hola mundo"

OK

127.0.0.1:6379> get test

"hola mundo"

127.0.0.1:6379>

Finalmente dejaremos configurado redis para que inicie (esto lo ejecutamos como usuario root):

[root@bci-bluemixdbredis01 bd]# sudo systemctl enable redis

Created symlink from /etc/systemd/system/multi-user.target.wants/redis.service to /etc/systemd/system/redis.service.

[root@bci-bluemixdbredis01 bd]#

Con lo anterior ya tendremos nuestro redis configurado.

Saludos. Felipe.