Durham Oracle

DBA_JOBS

Just a note on DBA_JOBS so I don't forget - pain when looking for DBA_JOBS in google and all you get is actual oracle jobs.

To show broken jobs.

1* select JOB,BROKEN,FAILURES,SCHEMA_USER,LAST_DATE,LAST_SEC,INTERVAL from DBA_JOBS e.g. job 11 has failed 121 times.

SQL> /

JOB B FAILURES SCHEMA_USE LAST_DATE LAST_SEC INTERVAL

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

9 N 0 EDW 31-OCT-18 05:00:10 TRUNC(SYSDATE+1) + 5/24

10 N 0 DW_MAIN 31-OCT-18 23:42:39 SYSDATE + 5/1440

8 N 0 EDW 31-OCT-18 04:00:29 TRUNC(SYSDATE+1) + 4/24

11 N 121 DW_MAIN 31-OCT-18 09:34:24 SYSDATE + 5/1440

850 N DW_MAIN NEXT_RUN_TIME ('Update Card Statuses')

Read only users

Used this to create read only users - edit as appropriate.

[oracle@collabn1 ~]$ cat read_only.sh

export ORACLE_SID=emrep

sqlplus -silent /nolog<<EOF

connect / as sysdba

set pagesize 0 linesize 32767 trimspool on echo off feedback on termout off colsep "" verify off

set serveroutput on size unlimited

alter session set nls_date_format='dd-Mon-yyyy hh24:mi:ss';

DECLARE

sql_text varchar2(200) := '';

exist_count number := 0;

BEGIN

for usr in (select username from all_users where username = 'RUSSELL') loop

select count (*) into exist_count from dba_roles where role = usr.username || '_READONLY';

if exist_count = 0 then

sql_text := 'create role ' || usr.username || '_READONLY NOT IDENTIFIED';

execute immediate sql_text;

dbms_output.put_line(sql_text);

for obj in (select object_name from all_objects where owner = usr.username and object_type in ('TABLE','VIEW')) loop

sql_text := 'grant select on ' || usr.username || '.' || obj.object_name || ' to ' || usr.username || '_READONLY';

BEGIN

execute immediate sql_text;

EXCEPTION

WHEN OTHERS then null;

END;

end loop;

end if;

end loop;

END;

exit

EOF

SQL> l
1* select * from dba_tab_privs where grantee = 'RUSSELL_READONLY'

SQL> /

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE COM TYPE INHERITED
------------------------------ --------------- -------------- ------------------------------ --------------- --- --- --- ----- ----------
RUSSELL_READONLY RUSSELL TEST RUSSELL SELECT NO NO NO TABLE NO

SQL> l
1* select * from dba_role_privs where grantee = 'IAN'

SQL> /

GRANTEE GRANTED_ROLE ADM DEL DEF COM INHERITED
------------------------------ -------------------- --- --- --- --- ----------
IAN RUSSELL_READONLY NO NO YES NO NO

1* select a.grantee, a.granted_role, b.owner, b.privilege from dba_role_privs a, dba_tab_privs b where a.granted_role = b.grantee and a.granted_role = 'RUSSELL_READONLY'

SQL> /

GRANTEE          GRANTED_ROLE     OWNER     PRIVILEGE
------------------------------ -------------------- --------------- ---------------
IAN          RUSSELL_READONLY     RUSSELL     SELECT
SYS          RUSSELL_READONLY     RUSSELL     SELECT

#!/bin/bash
create_ro()
{
#use the -s to suppress output, to spool to file use <<EOF >> output.txt
sqlplus -s system/………….@...........scan:1521/.....SVC <<EOF
set serveroutput on;
set verify off;
set echo off;
set pagesize 0;
set head off;
DECLARE
        v VARCHAR2(150);
        w VARCHAR2(150);
        CURSOR c is select 'grant select on $FROMUSR.'||table_name|| ' to $TOUSR;' from dba_tables where owner='$FROMUSR';
        CURSOR d is select 'grant select on $FROMUSR.'||view_name|| ' to $TOUSR;' from dba_views where owner='$FROMUSR';
BEGIN
OPEN c;
    LOOP
                    FETCH c into v;
                        EXIT WHEN c%NOTFOUND;
                        dbms_output.put_line(v);
                END LOOP;
   CLOSE c;
   OPEN d;
    LOOP
                    FETCH d into w;
                        EXIT WHEN d%NOTFOUND;
                        dbms_output.put_line(w);
                END LOOP;
   CLOSE d;
END;
/
EOF
}
# Main processing
[ $# -eq 0 ] && { echo "Usage: supply the 2 users to clone from / to"; exit 1; }
FROMUSR=$1; export FROMUSR
TOUSR=$2; export TOUSR
echo " "
echo "Run output against " $2
echo "----------------------------------"
create_ro
exit

Identify ASM disk to physical disk

Identify ASM disk on ASM instance :

SQL>

select g.name disk_group
       ,d.DISK_NUMBER
       ,d.name disk_name
       ,d.PATH
       ,d.TOTAL_MB
       ,d.header_status
from   v$asm_disk d,
       v$asm_diskgroup g
where d.GROUP_NUMBER = g.GROUP_NUMBER (+)
order by g.name, d.disk_numberSQL>   2    3    4    5    6    7    8    9   10
11 /

DISK_GROUP                     DISK_NUMBER DISK_NAME
------------------------------ ----------- ------------------------------
PATH
--------------------------------------------------------------------------------
TOTAL_MB HEADER_STATU
---------- ------------
DATA                                     0 DATA_0000
/dev/oracleasm/disks/NYCOEMDATA00
    306176 MEMBER

[oracle@oem01 etc]$ cd /dev/oracleasm

[oracle@oem01 oracleasm]$ cd disks

[oracle@oem01 disks]$ ls

OEMDATA00

[oracle@oem01 disks]$ ls -la

total 0

drwxr-xr-x. 1 root root 0 May 29 15:04 .

drwxr-xr-x. 4 root root 0 May 29 15:04 ..

brw-------. 1 oracle oinstall 8, 49 Sep 28 09:06 OEMDATA00

[oracle@oem01 disks]$ oracleasm listdisks

OEMDATA00

[oracle@oem01 disks]$ oracleasm querydisk -d OEMDATA00

Disk "OEMDATA00" is a valid ASM disk on device [8,49]

[oracle@oem01 disks]$ ls -l /dev/* | grep 8, | grep 49

brw-rw----. 1 root disk 8, 49 May 29 15:41 /dev/sdd1

From fdisk -l command :

Disk /dev/sdd: 322.1 GB, 322122547200 bytes, 629145600 sectors