I bet you heard about ORACLE_PDB_SID, right? But do you know how it works?

This one is more on a curious side =) but somehow interesting.

Not too long ago Mike Dietrichde published a post on to connect directly to a PDB without a password.

After it, lots of DBAs started to post their testing showing the same, But how it works? As there is only a bequeath connection available to a CDB?

Well, I was doing some digging for another project and stumbled on a curious thing. A logon trigger called DBMS_SET_PDB

To my surprise, this trigger alters the session´s container to the one specified by the ORACLE_PDB_SID variable. Similar to Tanel Poder´s script called cc.sql or ccr.sql which basically are short cuts to avoid a lot of typing =)

SYS@CDB19c>select
DBMS_Metadata.Get_DDL('TRIGGER',t.trigger_name,t.owner)
From
DBA_Triggers t
Where
TRIGGER_NAME ='DBMS_SET_PDB'; 2 3 4 5 6

DBMS_METADATA.GET_DDL('TRIGGER',T.TRIGGER_NAME,T.OWNER)
--------------------------------------------------------------------------------

CREATE OR REPLACE NONEDITIONABLE TRIGGER "SYS"."DBMS_SET_PDB" after logon on database
WHEN (user = 'SYS' or user = 'SYSTEM') declare
pdb_name varchar2(64);
begin
DBMS_SYSTEM.get_env ('ORACLE_PDB_SID', pdb_name);
if(pdb_name is not null)
then
EXECUTE IMMEDIATE 'alter session set container = ' || '"' || pdb_name
|| '"';
end if;
exception
when others then
NULL;
end dbms_set_pdb;
ALTER TRIGGER "SYS"."DBMS_SET_PDB" ENABLE

Also is mentioned in the article that this is this would only work on versions 18.8 and above. But well, as we have the code for the trigger, should it possible to work on 12c as well?

Let´s try it| =) the oldest container which I have at the moment is 12r2  but that should do it.

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

SYS@CDB12CR2>select * from v$version;

BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0

SYS@CDB12CR2>CREATE OR REPLACE NONEDITIONABLE TRIGGER "SYS"."DBMS_SET_PDB_EFA" after logon on database
2 WHEN (user = 'SYS' or user = 'SYSTEM') declare
3 pdb_name varchar2(64);
begin
4 5 DBMS_SYSTEM.get_env ('ORACLE_PDB_SID', pdb_name);
6 if(pdb_name is not null)
7 then
8 EXECUTE IMMEDIATE 'alter session set container = ' || '"' || pdb_name
9 || '"';
10 end if;
exception
when others then
NULL;
end DBMS_SET_PDB_EFA; 11 12 13 14
15 /

Trigger created.

SYS@CDB12CR2>ALTER TRIGGER "SYS"."DBMS_SET_PDB_EFA" ENABLE
2 /

Trigger altered.

SYS@CDB12CR2>show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB12CR2 READ WRITE NO
SYS@CDB12CR2>exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@server01 ~]$ export ORACLE_PDB_SID=PDB12CR2;
[oracle@server01 ~]$ CDB12CR2
The Oracle base remains unchanged with value /oraadm/oracle
LD_LIBRARY_PATH=/oraadm/oracle/product/12.2.0.1/lib
ORACLE_BASE=/oraadm/oracle
ORACLE_HOME=/oraadm/oracle/product/12.2.0.1
ORACLE_PDB_SID=PDB12CR2
ORACLE_SID=CDB12CR2
ORAENV_ASK=NO

[oracle@server01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 30 11:30:34 2020

Copyright (c) 1982, 2016, Oracle. All rights reserved.


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

SYS@CDB12CR2>show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB12CR2 READ WRITE NO
SYS@CDB12CR2>alter session set container=cdb$root;

Session altered.

SYS@CDB12CR2>show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB12CR2 READ WRITE NO
SYS@CDB12CR2>

And as you can see, the trigger works as expected right, it’s not a rocket science as it gets the context environment variable value and alters the session´s container.

Thanks and until next time.

Cheers.

Elisson Almeida

 

Note:

Right after I wrote this post, I went to look for more information on ORACLE_PDB_SID and found another post from Mike Dietrichde which also shows the trigger code and some pitfalls that you must be aware.

AWS RDS: Read Oracle Traces from SQL*Plus

Hi all,

One more for AWS services. Let’s say you need to read tracefiles from RDS, hot to do it?

Here is an example on how to list and read those files:

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
---------- ------------------------- ------------------------------------------------------------ -------------
SYS BDUMP /rdsdbdata/log/trace 0
SYS ADUMP /rdsdbdata/log/audit 0
SYS OPATCH_LOG_DIR /rdsdbbin/oracle/QOpatch 0
SYS OPATCH_SCRIPT_DIR /rdsdbbin/oracle/QOpatch 0
SYS DATA_PUMP_DIR /rdsdbdata/datapump 0
SYS OPATCH_INST_DIR /rdsdbbin/oracle/OPatch 0
SYS TMP /rdsdbdata/userdirs/01 0

7 rows selected.


SQL> select * from table (rdsadmin.rds_file_util.read_text_file( p_directory => 'BDUMP', p_filename => 'trace/CPROD1_s003_81573.trc'));


From the trace file:
=========================
..................................................................................
index undo for leaf key operations
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x000e289e.51da.47
Dump kdilk : itl=98, kdxlkflg=0x1 sdc=0 indexid=0x1c53db block=0x0019bdc0
(kdxlpu): purge leaf row
key :(24):
07 78 76 0c 17 17 09 32 08 c7 07 1a 02 28 15 01 18 06 00 1a 3c 99 00 1e

File 3 is not mirrored.

End dump previous blocks for kdsgrp
* kdsgrp1-2: ***********************************************
kdsDumpState: RID context dump

45511581 rows selected.

SQL> select * from table (rdsadmin.rds_file_util.listdir( p_directory => 'BDUMP')) order by mtime;

FILENAME TYPE FILESIZE MTIME
---------------------------------------- ---------- ---------- ---------
CPROD1_ora_48800.trm file 73 24-DEC-18
CPROD1_ora_48800.trc file 998 24-DEC-18
CPROD1_ora_86597.trc file 998 24-DEC-18
CPROD1_ora_86597.trm file 73 24-DEC-18
CPROD1_ora_7999.trc file 881 24-DEC-18
CPROD1_ora_7999.trm file 71 24-DEC-18
CPROD1_ora_7997.trm file 71 24-DEC-18
CPROD1_ora_7997.trc file 881 24-DEC-18
CPROD1_ora_8240.trm file 71 24-DEC-18
CPROD1_ora_8240.trc file 881 24-DEC-18
CPROD1_ora_8381.trm file 72 24-DEC-18
CPROD1_ora_8381.trc file 995 24-DEC-18
CPROD1_ora_8540.trc file 881 24-DEC-18
CPROD1_ora_8540.trm file 71 24-DEC-18
CPROD1_ora_9876.trc file 881 24-DEC-18
CPROD1_ora_9876.trm file 71 24-DEC-18
CPROD1_ora_11142.trm file 72 24-DEC-18
CPROD1_ora_11142.trc file 883 24-DEC-18
CPROD1_ora_11182.trc file 883 24-DEC-18
CPROD1_ora_11182.trm file 72 24-DEC-18
CPROD1_ora_55077.trm file 73 24-DEC-18
CPROD1_ora_55077.trc file 997 24-DEC-18
CPROD1_ora_92260.trm file 73 24-DEC-18
CPROD1_ora_92260.trc file 997 24-DEC-18
CPROD1_ora_123869.trc file 1000 24-DEC-18
CPROD1_ora_123869.trm file 74 24-DEC-18
CPROD1_ora_41305.trc file 998 24-DEC-18
CPROD1_ora_41305.trm file 73 24-DEC-18
CPROD1_j002_3293.trc file 114049 24-DEC-18
CPROD1_j002_3293.trm file 370 24-DEC-18
CPROD1_mmon_71739.trc file 7511332 24-DEC-18
CPROD1_mmon_71739.trm file 738330 24-DEC-18
CPROD1_ora_92888.trc file 997 24-DEC-18
CPROD1_ora_92888.trm file 73 24-DEC-18
trace/ directory 323584 24-DEC-18
alert_CPROD1.log file 204808 24-DEC-18
CPROD1_ora_70145.trc file 1470 24-DEC-18
CPROD1_ora_70145.trm file 109 24-DEC-18

3845 rows selected.

SQL> select * from table (rdsadmin.rds_file_util.listdir( p_directory => 'BDUMP')) where filename like '%CPROD1_s003_81573.trc%';

FILENAME TYPE FILESIZE MTIME
---------------------------------------- ---------- ---------- ---------
CPROD1_s003_81573.trc file 1948134047 23-DEC-18

									

Tips to Improve your Oracle DB Shellscripting!

There are some small tricks to speed up your shellscripts for Oracle DBs. I’m pretty sure you can do almost magic only with those:

# To get all OS Users with an Oracle Instance in server:

ps -ef | grep [o]ra_pmon | awk -F_ '{ print $1}'|cut -f1 -d" "|sort -u

# To get all DBSids in server:

ps -ef | grep [o]ra_pmon  | awk -F_ '{ print $3}'

# To get all DBNames in server:

ps -ef | grep [o]ra_pmon  | awk -F_ '{ print substr($3, 1, length($3)-1)}'

# To get Oracle Home from /etc/oratab for a database:

grep "dbname:" /etc/oratab|cut -d: -f2 -s

# Looping for any of the lists from commands above:

for DBUSER|DBNAME|DBINST in `$Comand_From_ListAbove` do [...] done

# Example:
Take DBInstance and Oracle Home for Each DBName on Each OSUser:

for DBUSER in `ps -ef | grep [o]ra_pmon | awk -F_ '{ print $1}'|cut -f1 -d" "|sort -u`
do
for DBNAME in `ps -ef | grep [o]ra_pmon | grep ${DBUSER} | awk -F_ '{ print substr($3, 1, length($3)-1)}'`
do
DBSID=`ps -ef | grep [o]ra_pmon | grep -i ${DBNAME^^}| awk -F_ '{ print $3}'`
DBOH=`grep "^${DBNAME,,}:" /etc/oratab|cut -d: -f2 -s`
done
done

# Variable Upper Case or Lower Case:

For uppercase: ${DBNAME^^}
For lowercase: ${DBNAME,,}

# Putting Colors on your messages:

RED='\033[0;31m'
GREEN='\033[0;32m'
PURPLE='\033[0;35m'
NC='\033[0m' # No Color
echo -e "Status: ${GREEN}All Good!${NC}";
echo -e "Status: ${RED}ERROR!${NC}"
echo -e "Status: ${PURPLE}Do you like messages in purple?${NC}"

# Conditional: Is there any line with a specific content?

if [`cat myfile.log | grep "text_I_want"| wc -l |awk '{print $1}'` -gt 0 ]; then
echo -e "Count Greater Then Zero: ${GREEN}Line Exists!${NC}";
else echo -e "Count equals to Zero: ${RED}No line with this text...${NC}"
fi;

# Some other related posts:
Shellscript & Oracle: Run Script for a List of Databases
Shellscript: Using eval and SQLPlus
Recursive string change
Grepping Entries from Alert.log
Grepping Alert by Day
Shellscript: Master Blaster KB!

PS: Thanks for Jared Still for suggesting improvements and saving some command greps (grep ora_pmon |grep -v grep -> grep [o]ra_pmon). That’s a nice tip. Thanks! 😀

Anything I forgot to put here? Tell me with a comment!
Cheers!

Formatting DBMS_GET_DDL Output on SQLPlus

Hey all!
Issues when formatting output for DBMS_GET_DDL on SQLPlus? Me too!

Check below the best way to format it. Add to your scripts!

set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000

begin
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/
-- Now you command:
select dbms_get_ddl.xxx(..) as ddl from dual;

Hope you enjoy!
Cheers!

Explain ORA-XXX on SQL*Plus

For those when the error is unkown/rare, SQL*Plus helps us. It’s just call “oerr” from OS.

See the Linux example (made on RHEL):

SQL>  !oerr ora 01652
01652, 00000, "unable to extend temp segment by %s in tablespace %s"
// *Cause:  Failed to allocate an extent of the required number of blocks for
//          a temporary segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
//          files to the tablespace indicated.

Pretty cool, han?

Have a nice week!
Matheus.

Shellscript: Using eval and SQLPlus

I always liked bash programming, and sometimes need to set Bash variables using information from Oracle tables.

To achieve that I’m using below solution, which I explain in details later.

# SQLPlus should return a string with all Bash commands
$ORACLE_HOME/bin/sqlplus -S -L -R 3 / as sysdba > /tmp/sqlplus.log <<-EOF
   SET PAGES 0 FEEDBACK OFF TIMING OFF VERIFY OFF LINES 1000
   SELECT 'OK:DBNAME='||UPPER(D.NAME)||'; INST_NAME='||I.INSTANCE_NAME AS STR
     FROM V\$DATABASE D, V\$INSTANCE I;
EOF

# Now, tests if sqlplus exit fine, and check if result string starts with OK keyword
if [ $? -eq 0 ] && [ "$( cat /tmp/sqlplus.log | head -n 1 | cut -d: -f1 )" == "OK" ]; then
   sed -i 's/OK://g' /tmp/sqlplus.log
   while read r; do eval "$r"; done </tmp/sqlplus.log
else
   echo "Failed to search local instance $ORACLE_SID"
   return 2
fi

In the first part, I call sqlplus, which select should return an string that contains valid bash commands, to set all variables I need. In this example, sqlplus returns Database Name and Instance Name:

      OK:DBNAME=xpto; INST_NAME=xpto_1;

The second part, exists only for consistency checks. It verify if result string starts with “OK” keywork. If all went fine, it execute the result string using the bash command eval.

 

eval – That is where magic happens!

The command eval, can be used to evaluate (and execute) an ordinary string, using the current bash context and environment. That is different than when you put your commands in a subshell.

The below source code, reads sqlplus.log and execute every command using eval:

while read line; do eval "$line"; done </tmp/sqlplus.log

Cassiano.

Acentuação no SQLPLUS com Windows em inglês

Hoje perdi tempão pra fazer funcionar a acentuação no SQLPLUS com Windows em inglês. Resolvi compartilhar.
E não. Não basta escolher o idioma certo na instalação do client. Depende realmente do idioma do SO.

Cenário:
Server Windows com client Oracle 11.2 x64 instalado. Necessário usar esta versão do OracleClient visando reconhecimento de scan listeners. Restrições da situação demandavam utilização do sqlplus, obrigatoriamente.

Convém destacar:
– O escopo deste post é fazer com que os dados sejam ‘escritos’ corretamente no BD, e não corrigir dados que já estão gravados incorretamente.
– O escopo deste post não é fazer a conversão de charset do BD, e sim a configuração de nls_lang do client.
– Antes de reclamar que não tá funcionando, dá uma olhadinha no bônus, lá no final do post.

Basicamente, concluí que a melhor configuração para resolver o problema consiste em:
1) Setar o CHCP para 1252 (código de página)
2) Setar variável NLS_LANG do server para BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252
3) Setar NLS_LANG do Oracle para BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252

OBS: Funciona com outras combinações, mas optei pelo BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252 pois segundo a Oracle (http://docs.oracle.com/html/B13804_02/gblsupp.htm) é o que temos como ‘oficial’ pro “Português (Brazil).”

Vale lembrar que esta configuração pode ser feita diretamente via prompt de comando antes da utilização do sqlplus. Aí o problema já estaria resolvido (:D).
Até aqui o Eduardo Legatti me ajudou 100%. Mas agora pra que a configuração seja permanente no servidor, como faz?

Faz assim:

1)  Configuração CHCP para 1252 por Default:
– Run > regedit

Em [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage]:
– Setar o (Default) para c_1252.nls

Default CHCP

–  SetarOEMCP para 1252

set OEMCP

2) Configurar NLS_LANG do Oracle:

Em [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_]:
– Setar NLS_LANG para BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252

NLS_LANG Oracle
3) Configurar o NLS_LANG como variável de ambiente:

Em [My Computer > Properties > Advanced System Settings > Environment Variables]:
– Setar o NLS_LANG para BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252

set OEMCP
4) Reboot do Servidor:

– Para efetivar mudanças no REGEDIT é necessário reiniciar o server.

Era isso!
Espero que seja útil para mais alguém, assim como foram úteis pra mim os links abaixo:

Nota: Greek Characters Are Not Displayed Correctly In A DOS Window (Doc ID 208902.1)
http://www.leniel.net/2010/03/oracle-nlslang-character-set-encoding.html#sthash.KPuB7Rtg.YxcOtWFd.dpbs
http://docs.oracle.com/html/B13804_02/gblsupp.htm
http://nagaappani.blogspot.com.br/2013/05/setting-nlslang-on-windows-for-oracle.html
http://ss64.com/nt/chcp.html
http://www.fabioprado.net/2012/11/configurando-national-language-support.html
http://tosemopcao.blogspot.com.br/2009/11/bd-oracle-acentos-no-10g-xe.html
http://eduardolegatti.blogspot.com.br/2011/01/habilitando-o-suporte-acentuacao-no.html

Bônus)
Embora as configurações estejam adequadas, a fonte (tipo de letra) do DOS (prompt de comando) pode não suportar os caracteres especiais e acentos. Basta trocar pra “Lucida Console”:

Fonte_Caracteres

Fechado?

Até breve!