Thinking Out Loud

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>

April 27, 2021

RMAN Backup & Restore Review

Filed under: Recovery,RMAN — mdinh @ 10:08 pm

I have seen many backup reviews without any information on Recovery Time Objective (RTO) and Recovery Point Objective (RPO).

The environment I am reviewing has backup to tape only.

Here is the RMAN script I am using. Since I don’t know RTO and RPO, I chose an arbitrary RPO.

--- Did not specified device since it is configured from RMAN
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=';


$ cat /tmp/restore_validate_$ORACLE_SID.out

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Apr 27 12:02:26 2021

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

RMAN> spool log to restore_validate.log
2> set echo on
3> connect target;
4> show all;
5> list backup by file;
6> restore spfile validate;
7> restore controlfile validate;
8> restore database until time "TRUNC(sysdate)+1/24" validate preview summary;
9> report schema;
10> exit

There are six development databases on the host.

After running restore validate here are pertinent results from mining the log.

The following databases do not look to have successful restore since there is referenced to disk.

What do you think is required from disks for restore?

$ grep "scanning archived log" restore_validate*.log|grep ORA_DISK|awk -F ":" '{print $1}'|sort -u
restore_validate_DEVAAAD.log
restore_validate_DEVAAAQ.log
restore_validate_DEVBBBD.log
restore_validate_DEVBBBQ.log

Here is the crontab entries for archived log backup and the time restore testing was performed.

--- Restore until time TRUNC(sysdate)+1/24 will failed since archived logs are backed up every 12H.

FAILED: 
--- Archived logs backup starts 12:30. Restore test started at 10:35; hence, archived logs on disk is required.
30 0,12 * * * /home/oracle/bin/rman_backup.sh DEVAAAD arch
-rw-r--r--    1 oracle   dba           38478 Apr 27 10:05 restore_validate_DEVAAAD.log

--- Archived logs backup starts 12:55. Restore test started at 12:48; hence, archived logs on disk is required.
55 0,12 * * * /home/oracle/bin/rman_backup.sh DEVAAAQ arch
-rw-r--r--    1 oracle   dba           33440 Apr 26 12:48 restore_validate_DEVAAAQ.log

--- Archived logs backup starts 12:10. Restore test started at 12:02; hence, archived logs on disk is required.
10 0,12 * * * /home/oracle/bin/rman_backup.sh DEVBBBD arch
-rw-r--r--    1 oracle   dba           16152 Apr 26 12:02 restore_validate_DEVBBBD.log

--- Archived logs backup starts 12:10. Restore test started at 11:45; hence, archived logs on disk is required.
10 0,12 * * * /home/oracle/bin/rman_backup.sh DEVBBBQ arch
-rw-r--r--    1 oracle   dba           50474 Apr 22 11:48 restore_validate_DEVBBBQ.log


PASSED:
--- Archived logs backup starts 12:55. Restore test started at 13:22.
55 0,12 * * * /home/oracle/bin/rman_backup.sh DEVAAAU arch
-rw-r--r--    1 oracle   dba           38967 Apr 26 13:22 restore_validate_DEVAAAU.log

--- Archived logs backup starts 12:10. Restore test started at 12:16.
10 0,12 * * * /home/oracle/bin/rman_backup.sh DEVBBBU arch
-rw-r--r--    1 oracle   dba           43777 Apr 27 12:16 restore_validate_DEVBBBU.log

Is it acceptable to lose up to 12H of data?

Do You Tag RMAN Backups?

Filed under: RMAN — mdinh @ 7:53 pm

There were discussions if RMAN backups should be tagged.

Basically, I have been tagging backup as L0, L1, AL for backups to disk or tape only.

Then there are backups to both disk & tape and was planning to tag backups as L0_DISK, L1_DISK, AL_DISK, L0_TAPE, L1_TAPE, AL_TAPE.

I was provided the following info.

Tags can’t be used for this purpose. Tag does not indicate if backup is on disk. Tag should be used if you want to create a specific backup for specific purpose, and use this tag for copy, keep or something like this.

The above recommendations differ from Oracle’s documentation – Backup Tags

Next, I was validating backup using – restore database until time “TRUNC(sysdate)+1/24” validate preview summary;

Here is the result for mining the log and noticed the info for backup pieces.

Does it matter or important to know what the backup piece is?

$ grep "piece handle" restore_validate_$ORACLE_SID.log|grep tag|sort -u
channel ORA_SBT_TAPE_1: piece handle=5avt57n5_1_1 tag=LEVEL0
channel ORA_SBT_TAPE_1: piece handle=76vt7sog_1_1 tag=LEVEL1
channel ORA_SBT_TAPE_1: piece handle=78vt8rmb_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=79vt8rmc_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7avt8rmd_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7bvt8rmg_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7cvt8rmh_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7dvt8rmi_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7evt8rmj_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7fvt8rmk_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7gvt8rml_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7hvt8rmn_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7ivt8rmo_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7jvt8rmp_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7kvt8rmq_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7lvt8rmu_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7mvt8rmv_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7nvt8rn0_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7ovt8rn1_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7qvta5sc_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7rvta5t5_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7svta5t8_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7tvta5tc_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7uvta5te_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=7vvta5tg_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=80vta5tk_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=81vta5tm_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=82vta5tp_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=83vta5tr_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=84vta5tv_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=85vta5u3_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=86vta5u7_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=87vta5uc_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=88vta5ug_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=89vta5ui_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=8avta5un_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=8bvta5ur_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=8cvta5vb_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=8dvta5vg_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=8evta600_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=8fvta602_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=8gvta605_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=8hvta609_1_1 tag=ARCH
channel ORA_SBT_TAPE_1: piece handle=c-3547077149-20210427-01 tag=TAG20210427T034102

April 24, 2021

RMAN list backup

Filed under: RMAN — mdinh @ 2:01 am

I am used to using TAG when listing backup and today I learned something new.

### Change disk to sbt for device type or remove device type for both disk & sbt.
c/disk/sbt

### Change database with any of the followings:
c/database/archivelog all/archivelog sequence/controlfile/spfile/tablespace/datafile

### Notice how summary is inconsistent.
list backup summary device type=sbt;
list backup summary device type=disk;

list backup of database summary device type=disk;

list backup of database summary completed after 'sysdate-1' device type=disk;
list backup of database summary completed between 'sysdate-1' and 'sysdate' device type=disk;

list backup of tablespace 'SYSTEM' summary device type=disk;
list backup of datafile 1 summary device type=disk;

list backup of archivelog sequence between 46270 and 46274 summary device type=disk;
list backup of archivelog sequence 1 summary device type=disk;

list backup of archivelog all summary completed after 'sysdate-1' device type=disk;
list backup of archivelog all summary completed between 'sysdate-1' and 'sysdate' device type=disk;


[oracle@ol7-112-dg1 ~]$ rman checksyntax @ list.rman

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr 24 01:54:10 2021

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

RMAN> set echo on
2> list backup summary device type=disk;
3> list backup of database summary device type=disk;
4> list backup of database summary completed after 'sysdate-1' device type=disk;
5> list backup of database summary completed between 'sysdate-1' and 'sysdate' device type=disk;
6> list backup of tablespace 'SYSTEM' summary device type=disk;
7> list backup of datafile 1 summary device type=disk;
8> list backup of archivelog sequence between 46270 and 46274 summary device type=disk;
9> list backup of archivelog sequence 1 summary device type=disk;
10> list backup of archivelog all summary completed after 'sysdate-1' device type=disk;
11> list backup of archivelog all summary completed between 'sysdate-1' and 'sysdate' device type=disk;
12> exit
The cmdfile has no syntax errors

Recovery Manager complete.
[oracle@ol7-112-dg1 ~]$

### Remove device to show backup for both disk and sbt;
[oracle@ol7-112-dg1 ~]$ rman checksyntax @ list.rman

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr 24 10:54:25 2021

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

RMAN> set echo on
2> list backup summary;
3> list backup of database summary;
4> list backup of database summary completed after 'sysdate-1';
5> list backup of database summary completed between 'sysdate-1' and 'sysdate';
6> list backup of tablespace 'SYSTEM' summary;
7> list backup of datafile 1 summary;
8> list backup of archivelog sequence between 46270 and 46274 summary;
9> list backup of archivelog sequence 1 summary;
10> list backup of archivelog all summary completed after 'sysdate-1';
11> list backup of archivelog all summary completed between 'sysdate-1' and 'sysdate';
12> exit
The cmdfile has no syntax errors

Recovery Manager complete.
[oracle@ol7-112-dg1 ~]$

If you are interested in using TAG then see post Simplify RMAN Restore With Meaningful Tag

April 18, 2021

Use Different Listener For Standby Duplication?

Filed under: Dataguard,standby — mdinh @ 4:59 pm

I know what you are thinking!

Why make this more complicate than it needs to be and is an ounce of prevention worth a pound of cure?

Oracle support started patching Oracle Exadata Cloud@Customer (ExaCC) environment.

After patching, the listener did not start because there were entries in the listener referencing database and oracle home that have been removed.

There are multiple database homes for the same database versions and this is how it was implemented.

Primary database (DB_ASHBURN) was used to create a second standby (DB_PHOENIX).

The primary database (DB_ASHBURN) was switchover to second standby (DB_PHOENIX)

DB_PHOENIX is now the new primary and DB_ASHBURN is the standby.

DB_ASHBURN (standby) was decommissioned and ORACLE_HOME was removed.

Unfortunately, listener.ora was not modified and failed to start after patching was completed.

Here is an example for LISTENER failed to start.

[oracle@ol7-112-dg1 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 18-APR-2021 16:19:49

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ol7-112-dg1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-112-dg1.local)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
TNS-01201: Listener cannot find executable /u01/app/oracle/product/11.2.0.4/dbhome_2/bin/oracle for SID DB_PHOENIX
[oracle@ol7-112-dg1 admin]$

---------------------------------------------------------
--- ORACLE_HOME may be in /etc/oratab but does not exist.
---------------------------------------------------------
[oracle@ol7-112-dg1 admin]$ sort -u -t : -k 2,2 /etc/oratab | grep -v "^#" | awk -F ":" '{print $2}'
/u01/app/oracle/product/11.2.0.4/dbhome_1
/u01/app/oracle/product/11.2.0.4/dbhome_2

[oracle@ol7-112-dg1 admin]$ ls -ld /u01/app/oracle/product/11.2.0.4/
drwxr-xr-x. 3 oracle oinstall 22 Apr 14 18:29 /u01/app/oracle/product/11.2.0.4/
[oracle@ol7-112-dg1 admin]$

Having separate listeners, LISTENER started without issues

[oracle@ol7-112-dg1 admin]$ lsnrctl start LISTENER

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 18-APR-2021 16:32:24

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ol7-112-dg1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-112-dg1.local)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-112-dg1.local)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                18-APR-2021 16:32:25
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ol7-112-dg1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-112-dg1.local)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "hawk" has 1 instance(s).
  Instance "hawk", status UNKNOWN, has 1 handler(s) for this service...
Service "hawk_DGMGRL" has 1 instance(s).
  Instance "hawk", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@ol7-112-dg1 admin]$

What’s your preference, having separate listeners to play it safe or decommissioning the environment properly and completely?

April 11, 2021

Port Forwarding Using SSH Config File

Filed under: Grid Control,linux — mdinh @ 8:03 pm

Here is a good reference SSH config file for OpenSSH client

From a secured server, I am able to connect to 2 different environments which seems counter intuitive but I digress.

Since there are 2 different environments, the same ~/.ssh/config cannot be used as there may be IP overlap.

One environment will use ~/.ssh/config and ssh emhost

Other environment will use ~/.ssh/cbconfig and ssh -F ~/.ssh/cbconfig emhost

The default EM port for both hosts is 7803.

Using firefox https://localhost:7803/em to access EM does not work well when saving username and password as they will be overwritten.

One solution to save sysman’s password is to use URL with different port.

Hence, config will have EM port 7803 forward to 7803 while cbconfig will have EM port 7803 forward to 7804.

========================================
This is on cloud and looks complicated. 
========================================
I did not create the configuration and don't know how many hours it took.
~/.ssh/config

Host emhost
     HostName 10.157.38.66
     LocalForward 7001 10.157.38.66:7001
     LocalForward 7102 10.157.38.66:7102
     LocalForward 7803 10.157.38.66:7803
     LocalForward 9803 10.157.38.66:9803
     LocalForward 9851 10.157.38.66:9851

# DEFAULTS:
Host *
User dinh


========================================
This is on premise and looks simpler. 
========================================
ssh -F ~/.ssh/cbconfig emhost

Host emhost
     HostName 10.10.72.254
     # Forward port need to use IP address.
     # Equivalent to ssh -L 7804:10.10.72.254:7803 mdinh@10.10.72.254
     LocalForward 7804 10.131.28.227:7803

# DEFAULTS:
Host *
User mdinh

April 8, 2021

Who Can Access

Filed under: Uncategorized — mdinh @ 12:22 am

I had a request to list the users who have read access (or greater) to the APP schema.

Base on the results below:

User DINH has SELECT on APP.INTERVAL table (view)

Role APP_ROLE has SELECT/UPDATE on APP.INTERVAL table (view)

User DINH/APP/SYS has APP_ROLE

SQL> show con_name

CON_NAME
------------------------------
ORCLPDB1
SQL> show user
USER is "SYS"
SQL> @priv.sql

SQL> select username from dba_users where created  > (select created from v$database) order by 1;

USERNAME
------------------------------
APP
AUDIT_TEST
DINH
DINH099PD
PDBADMIN
WMS099PD

6 rows selected.

SQL> select * from DBA_TAB_PRIVS where owner='APP';

GRANTEE              OWNER                TABLE_NAME                GRANTOR              PRIVILEGE            GRANTABLE HIERARCHY COM TYPE                     INH
-------------------- -------------------- ------------------------- -------------------- -------------------- --------- --------- --- ------------------------ ---
DINH                 APP                  INTERVAL                  APP                  SELECT               NO        NO        NO  TABLE                    NO
APP_ROLE             APP                  INTERVAL                  APP                  SELECT               NO        NO        NO  TABLE                    NO
APP_ROLE             APP                  INTERVAL                  APP                  UPDATE               NO        NO        NO  TABLE                    NO

SQL> --- ROLE_TAB_PRIVS describes table privileges granted to roles.
SQL> --- Information is provided only about roles to which the user has access.
SQL> select * from ROLE_TAB_PRIVS where OWNER='APP';

ROLE                           OWNER                TABLE_NAME                COLUMN_NAME               PRIVILEGE            GRANTABLE COM INH
------------------------------ -------------------- ------------------------- ------------------------- -------------------- --------- --- ---
APP_ROLE                       APP                  INTERVAL                                            UPDATE               NO        NO  NO
APP_ROLE                       APP                  INTERVAL                                            SELECT               NO        NO  NO

SQL> select * from DBA_ROLE_PRIVS where GRANTED_ROLE='APP_ROLE' order by 1;

GRANTEE              GRANTED_ ADMIN DEL DEFAULT COM INH
-------------------- -------- ----- --- ------- --- ---
APP                  APP_ROLE YES   NO  YES     NO  NO
DINH                 APP_ROLE NO    NO  YES     NO  NO
SYS                  APP_ROLE NO    NO  YES     NO  NO

SQL>

I also used Pete Finnigan’s who_can_access.sql for comparison.

Note who_can_access.sql is per object vs per schema.

If there were hundreds / thousands of table, then not sure how this will scale.

who_can_access: Release 1.0.3.0.0 - Production on Wed Apr 07 19:00:04 2021
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

NAME OF OBJECT TO CHECK       [USER_OBJECTS]: INTERVAL
OWNER OF THE OBJECT TO CHECK          [USER]: APP
OUTPUT METHOD Screen/File                [S]:
FILE NAME FOR OUTPUT              [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY  or file (/tmp)]:
EXCLUDE CERTAIN USERS                    [N]:
USER TO SKIP                         [TEST%]:

Checking object => APP.INTERVAL
====================================================================


Object type is => TABLE (TAB)
        Privilege => SELECT is granted to =>
        Role => APP_ROLE (ADM = NO) which is granted to =>
                User => DINH (ADM = NO)
                User => SYS (ADM = NO)
                User => APP (ADM = YES)
        User => DINH (ADM = NO)
        Privilege => UPDATE is granted to =>
        Role => APP_ROLE (ADM = NO) which is granted to =>
                User => DINH (ADM = NO)
                User => SYS (ADM = NO)
                User => APP (ADM = YES)

PL/SQL procedure successfully completed.


For updates please visit http://www.petefinnigan.com/tools.htm

SQL>

Did I do this right?

This is not 100% accurate, missing the followings, and does not take into consideration recursive roles grant:

  1. Column grants: GRANT update (column_name) ON table_name TO user_name;
  2. Code based access control (CBAC)
  3. REFERENCES privileges: GRANT references ON table_name to user_name

April 6, 2021

Detect Linux Host Restart

Filed under: awk_sed_grep,linux,shell scripting — mdinh @ 3:15 am

Sometime ago I had blogged about Monitor Linux Host Restart

The simple solution: How to email admins automatically after a Linux server starts?

Here is the example from root’s cron:

# crontab -l
@reboot su oracle -c '/home/oracle/scripts/host_restart_alert.sh' > /tmp/host_restart_alert.out 2>&1

Shell script is used because mail cannot be sent from local host and will need to be sent from remote host.

#!/bin/bash -x
MAILFROM=
MAILTO=
SUBJECT="Node reboot detected for $(hostname)"
EMAILMESSAGE="$(hostname) was restarted `uptime -p| awk -F'up' '{print $2}'` ago at `uptime -s`"

# uptime reports minutely and need to sleep for at least 60s after host restart
sleep 63

ssh oracle@remotehost /bin/bash <<EOF
/home/oracle/scripts/send_email.sh "$EMAILMESSAGE" "$SUBJECT" "$MAILFROM" "$MAILTO"
EOF

exit

Why is there a need to detect host restart and isn’t there monitoring for the host?

This is Oracle Exadata Cloud@Customer (ExaCC) environment.

When Oracle support performs patching, they do not provide any sort of communication or status and monitoring is disable for all hosts beforehand.

OPatchAuto to Patch a GI/RAC Environment.

After the patching is complete and your servers are restarted, you should check your product software to verify that the issue has been resolved.

This is why there is a need to detect and be notified for server restart.

April 4, 2021

Linux Find Week# Of Month

Filed under: linux,shell scripting — mdinh @ 9:02 pm

Unfortunately, Linux does not have parameter for Week Number Of Month

I found the solution at https://serverfault.com/questions/383666/how-to-determine-number-of-week-of-the-month

echo $((($(date +%-d)-1)/7+1))

Here is how I have tested.

[oracle@oracle-12201-vagrant ~]$ date -d '20210404'
Sun Apr  4 00:00:00 -05 2021

[oracle@oracle-12201-vagrant ~]$ echo $((($(date -d '20210404' +%-d)-1)/7+1))
1

[oracle@oracle-12201-vagrant ~]$ date -d '20210411'
Sun Apr 11 00:00:00 -05 2021

[oracle@oracle-12201-vagrant ~]$ echo $((($(date -d '20210411' +%-d)-1)/7+1))
2

[oracle@oracle-12201-vagrant ~]$ date -d '20210418'
Sun Apr 18 00:00:00 -05 2021

[oracle@oracle-12201-vagrant ~]$ echo $((($(date -d '20210418' +%-d)-1)/7+1))
3

[oracle@oracle-12201-vagrant ~]$ date -d '20210425'
Sun Apr 25 00:00:00 -05 2021

[oracle@oracle-12201-vagrant ~]$ echo $((($(date -d '20210425' +%-d)-1)/7+1))
4

Why is this even useful?

It can be a continuation of Simplify Log Management For Backup

Next Page »

Blog at WordPress.com.