Thinking Out Loud

May 22, 2021

emcli dg_verify_config

Filed under: Dataguard,dgmgrl,emcli — mdinh @ 1:43 am

I must love looking for trouble.

There are 47 RAC databases with Data Guard distributed among 7 clusters.

Being as lazy as I am, I did not want to connect to all the hosts to verify Data Guard configuration and create standby redo logs (SRL) for environments with Insufficient SRLs.

After some searching, I have found emcli dg_verify_config can be used; however, the documentation is not that great.

Currently working with Oracle Support and crossing my fingers.

$ $OMS_HOME/bin/emcli login -username=sysman
Enter password :

Login successful
$ $OMS_HOME/bin/emcli sync
Synchronized successfully

$ $OMS_HOME/bin/emcli dg_verify_config -primary_target_name="APEX18_XXXXXXX" -primary_target_type="rac_database" -verify_only
Parsing command line arguments...
Verify Data Guard Configuration procedure VERIFY_DG_CONFIG_20210520114056703 has been submitted for primary database APEX18_XXXXXXX.

$ $OMS_HOME/bin/emcli dg_verify_config -primary_target_name="APEX18_XXXXXXX" -primary_target_type="rac_database" -create_srls

The intention is to run emcli from OMS host to verify config and create srls where required.

However, there’s no documentation for how to retrieve results from verify_only at the OS level.

Seeing that I am struggling with Oracle support, Lead Database Consultant shared his implementation to verify Data Guard status before performing switchover.

Using the same concept, check for Insufficient SRLs from standby databases.

--- Prerequisite: Data Guard Broker is implemented.

--- Find Oracle Data Guard monitor process (DMON) and write to sids.txt file.
ps -ef|grep [d]mon|grep -v ASM|sort -k8 | awk -F "_" '{print $3}' > sids.txt

--- Create shell script dgmgrl_validate_srl.sh
arr=(`grep '^[A-Z].*' < sids.txt`)
for i in "${arr[@]}"
do
  echo ======================== $i ========================
  . oraenv <<< $i >/dev/null
  stby=`dgmgrl / "show configuration" | grep "Physical standby" | awk  '{print $1;}'`
  echo $stby
  dgmgrl / "validate database verbose '$stby'" | grep -B4 "Insufficient SRLs"
done


--- Run dgmgrl_validate_srl.sh
$ ./dgmgrl_validate_srl.sh
======================== ORACLE_SID ========================
XXXXXXXX_SPYBRACDR

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (XXXXXXXX_SPYBRAC)      (XXXXXXXX_SPYBRACDR)
    0         10                      0                       Insufficient SRLs
--

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (XXXXXXXX_SPYBRACDR)    (XXXXXXXX_SPYBRAC)
    0         10                      0                       Insufficient SRLs


Future Log File Groups Configuration - When current standby becomes primary.

Checking is different from fixing as 50%+ of RAC databases have issues with Insufficient SRLs.

Currently both processes (shell script and emcli) are deficient since only standby databases are checked.

When there is switchover or failover for primary databases, then check will have to be performed yet again.

At least, using emcli dg_verify_config -create_srls, does simplify the process since it can be execute from OMS host if it works.

May 14, 2021

Gather Your Session Info For Killing

Filed under: 11g,linux,oracle — mdinh @ 4:40 am

So there I was, running emremove.sql as part of pre-upgrade task; however, it was taking longer than expected.

Session was stuck at the output shown below and desperately CTRL-C did not work.

14:35:05 472  /
old  70:     IF (upper('&LOGGING') = 'VERBOSE')
new  70:     IF (upper('VERBOSE') = 'VERBOSE')

^C

^C^C

^C

I checked for blocking session and there were blocking locks from SYS which was really strange.

I made a gutsy call and kill SYS session from OS prompt based on timestamp.

~ $ ps -ef|grep sysdba
oracle    57147 231962  0 May12 pts/4    00:00:00 sqlplus   as sysdba
oracle   155919 139352  0 14:34 pts/1    00:00:00 sqlplus   as sysdba
oracle   244619 216760  0 15:25 pts/5    00:00:00 grep --color=auto sysdba

~ $ kill -9 155919

As it turns out, another DBA was logged in as sysdba causing havoc.

I was lucky to have killed the correct SYS session and will you be as lucky as I was?

Based on my near disaster, it would be better to create good practice of gathering your session info to be able to kill the correct session.

Here is current session info.

SQL> @my
SQL> select b.sid, b.serial#, a.spid processid, b.process clientpid from v$process a, v$session b
  2  where a.addr = b.paddr
  3  and b.audsid = userenv('sessionid')
  4  and b.sid=userenv('sid')
  5  ;

       SID    SERIAL# PROCESSID                CLIENTPID
---------- ---------- ------------------------ ------------------------
         5        101 16428                    16427

SQL>

[oracle@ol7-112-dg1 ~]$ ps -ef|grep 16427
oracle   16427  8573  0 03:44 pts/0    00:00:00 sqlplus   as sysdba
oracle   16428 16427  0 03:44 ?        00:00:00 oraclehawk (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   16461 11677  0 03:45 pts/1    00:00:00 grep --color=auto 16427
[oracle@ol7-112-dg1 ~]$

Kill OS process using sqlplus PROCESSID – don’t know session is killed until DML is performed.

[oracle@ol7-112-dg1 ~]$ kill -9 16428

SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 16428
Session ID: 5 Serial number: 101


SQL>

Another test

--- Session Info
SQL> @my
SQL> select b.sid, b.serial#, a.spid processid, b.process clientpid from v$process a, v$session b
  2  where a.addr = b.paddr
  3  and b.audsid = userenv('sessionid')
  4  and b.sid=userenv('sid')
  5  ;

       SID    SERIAL# PROCESSID                CLIENTPID
---------- ---------- ------------------------ ------------------------
         5        103 16533                    16532

SQL>

--- From another session, check waits for above session
SQL> r
  1  select NVL(s.username,'(oracle)') AS username, s.sid, s.serial#,
  2  sw.event, sw.seconds_in_wait, sw.state
  3  from v$session_wait sw, v$session s
  4  where s.sid = sw.sid and s.sid=&sid
  5*
Enter value for sid: 5
old   4: where s.sid = sw.sid and s.sid=&sid
new   4: where s.sid = sw.sid and s.sid=5

USERNAME               SID    SERIAL# EVENT                          SECONDS_IN_WAIT STATE
--------------- ---------- ---------- ------------------------------ --------------- -------------------
SYS                      5        115 SQL*Net message from client                169 WAITING

SQL>

Kill OS process using sqlplus CLIENTPID – immediate feedback –

[oracle@ol7-112-dg1 ~]$ ps -ef|grep 16532
oracle   16532  8573  0 03:46 pts/0    00:00:00 sqlplus   as sysdba
oracle   16533 16532  0 03:46 ?        00:00:00 oraclehawk (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   16557 11677  0 03:47 pts/1    00:00:00 grep --color=auto 16532
[oracle@ol7-112-dg1 ~]$


[oracle@ol7-112-dg1 ~]$ kill -9 16532


SQL> Killed
[oracle@ol7-112-dg1 ~]$

Hopefully you will never have to kill your own session.

When you need kill your session, it’s better to have the correct information versus guessing.

May 1, 2021

Extract DB User Password

Filed under: 19c,oracle — mdinh @ 12:40 am

For some reason, I had a mental block in trying to extract password for database users.

Here are some options.

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL>

-- From notes:
SQL> set echo off head off verify off feedb off pages 0 long 10000 longchunk 10000 trimspool on lines 2000 timing off term off
SQL> exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
SQL> exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',TRUE);

SQL> r
  1  select 'alter user '||username||' identified by values '||REGEXP_SUBSTR(DBMS_METADATA.get_ddl ('USER',USERNAME), '''[^'']+''')||';' ddl
  2  from dba_users where username='CTXSYS'
  3*
alter user CTXSYS identified by values 'S:29174843DD6989AA921A152BF37264659F042C2D8C216D97F6176AF13E4F;T:7B311EEA53E028F937CFABC4D6F6142E3027195E099798CD9E67910ABE6C621E9C23780121F208451B95AB558A4862F206A917C93B50D96E8F573FE7E1A4B2E98D77B9504BC2EBB457B63600127E34D';

SQL>


-- From colleague
SQL> select name, spare4 from sys.user$ where name='CTXSYS';
CTXSYS
S:29174843DD6989AA921A152BF37264659F042C2D8C216D97F6176AF13E4F;T:7B311EEA53E028F937CFABC4D6F6142E3027195E099798CD9E67910ABE6C621E9C23780121F208451B95AB558A4862F206A917C93B50D96E8F573FE7E1A34B2E98D77B9504BC2EBB457B63600127E34D

SQL>

-- From asktom
SQL> r
  1  with t as
  2  (select TO_CHAR(dbms_metadata.get_ddl('USER','CTXSYS')) ddl from dual )
  3  select replace(substr(ddl,1,instr(ddl,'DEFAULT')-1),'CREATE','ALTER')||';' sql
  4* from t

ALTER USER "CTXSYS" IDENTIFIED BY VALUES 'S:29174843DD6989AA921A152BF37264659F042C2D8C216D97F6176AF13E4F;T:7B311EEA53E028F937CFABC4D6F6142E3027195E099798CD9E67910ABE6C621E9C23780121F208451B95AB558A4862F206A917C93B50D96E8F573FE7E1A34B2E98D77B9504BC2EBB457B63600127E34D';

SQL>

Create a free website or blog at WordPress.com.