Posted in 2016

Catching errors

Sometimes happened that some rows could not be loaded and the application got an ORA-1, how can you find out what value it was, I know, it can be defined other way too, but I wanted it this way… I don’t remember even why I did it this way, this was in a time like my first year as a DBA, but it resolved the problem. And this is important… and I have to check it if it’s really working, but it should.. it’s enough to create a unique column and insert value 1 two times..

create table caught_errors (
  dt        date,               
  sid 		number
);

create or replace trigger catch_errors
   before insert on catchtab
   for each row
declare
spid_val number ;   
PRAGMA AUTONOMOUS_TRANSACTION;
begin

IF (IS_SERVERERROR(1)) THEN
execute immediate 'alter session set events ''10046 trace name context level 12, lifetime 1''';
select spid into spid_val from v$session s,v$process p where s.paddr=p.addr and audsid=userenv('sessionid') ;
insert into caught_errors values (sysdate,spid_val) ;
END IF;

end;
/
Advertisements
Posted in 2016

Monitor Flashback Area

Hi, there are times when you have a monitoring team and you don’t want to wait until your destination is full right.. to achieve this I have thought about a procedure, this procedure is really easy and makes basically what I needed, let’s check it…

CREATE or replace PROCEDURE FLASHBACK_WATCHER as 
threshold number;
BEGIN
select round(PERCENT_SPACE_USED) into threshold 
    from V$FLASH_RECOVERY_AREA_USAGE where file_type='FLASHBACK LOG' ;

if (threshold>80) then
    dbms_system.ksdwrt(2,'ORA-07445: Flashback is full to: '||threshold||'Please inform DB team.') ;
end if ;
EXCEPTION
        WHEN OTHERS THEN
        NULL ;
END;
/

exec dbms_scheduler.create_job(job_name=>'FLASHBACK_WATCHER_JOB', job_type=>'PLSQL_BLOCK', job_action=>'begin FLASHBACK_WATCHER end;',start_date=>SYSDATE,repeat_interval=>'FREQ=HOURLY;byminute=0; bysecond=0;') 
exec dbms_scheduler.enable(name=>'FLASHBACK_WATCHER_JOB')

Great,or? .. and once the message was written to alert.log we got alerted by a monitoring guy who automatically identified the problem. PS: we did not use FRA as location for backups or archlogs… so this monitoring was used only when we needed to setup flashback ad in a FAQ the ORA-7445 was described as very very bad, so they called us.

Posted in 2016

usefull goldengate commands

---
- GoldenGate Guru

- Manager
  - each manager needs a default PORT for communication
  - each manager instance has another PORT
  - use DYNAMICPORTLIST on target hosts after FW
  - commands:
    ggsci> EDIT PARAMS MGR
    ggsci> START MANAGER
    ggsci> STOP MANAGER [!]

- OBEY Files # frequently used commands
  - OBEY file_name
	# example - start_extract
	ADD EXTRACT myext, TRANLOG, BEGIN now
	START EXTRACT myext
	INFO EXTRACT myext, DETAIL
	INFO REPLICAT myrep, DETAIL

    ggsci> OBEY start_extract

- Controlling EXTRACT|REPLICAT
  - Start/Stop
    ggsci> START|STOP {EXTRACT|REPLICAT} group_name or extgroup* # wildcard
  - Delete EXTRACT
    ggsci> DBLOGIN USERID user, PASSWORD pwd
    ggsci> STOP EXTRACT group_name
    ggsci> DELETE EXTRACT group_name
    ggsci> UNREGISTER EXTRACT group_name,database_name # Oracle

  - Delete REPLICAT
    ggsci> STOP REPLICAT group_name
    ggsci> DBLOGIN USERID user, PASSWORD pwd
    ggsci> DELETE REPLICAT group_name

  - View a Parameter File
    ggsci> VIEW PARAMS group_name   
Posted in 2016

Definer and Invoker’s rights

connect / as sysdba
create tablespace test ;
create user peter identified by peter default tablespace test quota unlimited on test ;
grant connect, resource to peter ;
create user tomas identified by tomas default tablespace test quota unlimited on test ;
grant create session,create table to tomas ;

connect peter/peter
create table test (id varchar2(100)) ;
insert into test values ('Works') ;
commit ;

create or replace procedure showuser1 authid definer as
v_syscontext varchar2(1000) ;
v_value varchar2(100) ;
begin
select sys_context('USERENV','CURRENT_USER') into v_syscontext from dual ;
dbms_output.put_line(v_syscontext);

select id into v_value from test ;
dbms_output.put_line(v_value) ;
end;
/

create or replace procedure showuser2 authid current_user as
v_syscontext varchar2(1000) ;
v_value varchar2(100) ;
begin
select sys_context('USERENV','CURRENT_USER') into v_syscontext from dual ;
dbms_output.put_line(v_syscontext);

select id into v_value from test ;
dbms_output.put_line(v_value) ;
end;
/

grant execute on showuser1 to tomas ;
grant execute on showuser2 to tomas ;

connect tomas/tomas 
 create table test (id varchar2(100)) ;
insert into test values ('Yeaah') ;
commit ;

TOMAS> exec peter.showuser1
PETER
Works
TOMAS> exec peter.showuser2
TOMAS
Yeaah
TOMAS> connect peter/peter
Connected.
PETER> create or replace procedure showuser2 authid current_user as
v_syscontext varchar2(1000) ;
v_value varchar2(100) ;
begin
select sys_context('USERENV','CURRENT_USER') into v_syscontext from dual ;
dbms_output.put_line(v_syscontext);

select id into v_value from test ;
dbms_output.put_line(v_value) ;

/* I am an evil developer, haha */
delete from test ;
insert into test values ('hacked') ;
commit ;
end;
/
Procedure created.
PETER> connect tomas/tomas
Connected.
TOMAS>
TOMAS> exec peter.showuser1
PETER
Works

PL/SQL procedure successfully completed.

TOMAS> exec peter.showuser2
TOMAS
Yeaah

PL/SQL procedure successfully completed.

TOMAS> exec peter.showuser2
TOMAS
hacked

PL/SQL procedure successfully completed.

Thanks to Steven Feuerstein.. now I am a little step forward to understanding privilege inheritance. But what now?

The answer:
https://community.oracle.com/message/14120331#14120331

Posted in 2016

Unified auditing



# Stop RAC DBs vom ORACLE_HOME, dass man relinkt

[oracle@rac2-pub ~]$ cd /u01/app/oracle/product/12.1.0.2/rdbms/lib/
[oracle@rac2-pub lib]$ make -f ins_rdbms.mk uniaud_on ioracle

# setasmgid wegen oracle:asmadmin auf oracle binary

[oracle@rac2-pub lib]$ su
Password: 
[root@rac2-pub lib]# /u01/app/12.1.0.2/grid/bin/setasmgid -o=/u01/app/oracle/product/12.1.0.2/bin/oracle
[root@rac2-pub lib]# ls -l /u01/app/oracle/product/12.1.0.2/bin/oracle
-rwsr-s--x. 1 oracle asmadmin 323795924 Apr  8 13:51 /u01/app/oracle/product/12.1.0.2/bin/oracle
[root@rac1-pub lib]# exit
[oracle@rac1-pub lib]$ srvctl start database -db racdb

# Verifikation sqlplus session zeigt jetzt Unified Auditing

[oracle@rac1-pub lib]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Apr 8 13:56:16 2016

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics, Real Application Testing and Unified Auditing options

SQL> col parameter for a16
SQL> col value for a5
SQL> select parameter , value from v$option where PARAMETER = 'Unified Auditing';

PARAMETER	 VALUE
---------------- -----
Unified Auditing TRUE