Calculate Storage ROI with this basic tool

This is a basic tool that could help in calculating the saving in your storage for a 11g Database.


http://www.oracle.com/us/media/calculator/db11g/index.html

Best Regards,
Paulo Portugal

Oracle SHUTDOWN HANGING

If you are trying to NOT shutdown abort your database, this is a good workaround:

1-Shutdwn abort once
SQL> SHUT ABORT

2-Startup in RESTRICTED MODE
SQL> startup RESTRICT

3-Shutdown IMMEDIATE now
SQL>SHUT IMMEDIATE

4-Finally, startup normally
SQL> startup


Best Regards,
Paulo Portugal

2014-03-18 20:15:23 ERROR OGG-00665 Oracle GoldenGate Capture for Oracle, ext_sou1.prm: OCI Error getting OCI_ATTR_NAME for UDT SYS.XMLTYPE (status = 24328-ORA-24328: illegal attribute value), SQL.

If your EXTRACT process is abending with error like below:

2014-03-18 20:15:23  ERROR   OGG-00665  Oracle GoldenGate Capture for Oracle, ext_sou1.prm:  OCI Error getting OCI_ATTR_NAME for UDT SYS.XMLTYPE (status = 24328-ORA-24328: illegal attribute value), SQL.


Check if that is a new table created with XMLYPE column data type.

select distinct data_type from dba_tab_columns ;
select object_name,object_type, created from dba_objects where object_name in (select table_name from dba_tab_columns where data_type= 'XMLTYPE');


Add a new line in your EXTRACT parameter excluding this table from replication:

TABLEEXCLUDE owner.yournewtable;



This work for me.

Best Regards,
Paulo Portugal

Script Backup database using RMAN in Oracle 8i

This is my last example:

run {
     allocate channel c1 type disk format '/migra_UOL/APPRD/%d_APPRD_BKP_12_MAR_2014_%u_%s_%p';
     set limit channel c1 kbytes = 20000000;
     allocate channel c2 type disk format '/migra_UOL/APPRD/%d_APPRD_BKP_12_MAR_2014_%u_%s_%p';
     set limit channel c2 kbytes = 20000000;
     allocate channel c3 type disk format '/migra_UOL/APPRD/%d_APPRD_BKP_12_MAR_2014_%u_%s_%p';
     set limit channel c3 kbytes = 20000000;
     allocate channel c4 type disk format '/migra_UOL/APPRD/%d_APPRD_BKP_12_MAR_2014_%u_%s_%p';
     set limit channel c4 kbytes = 20000000;
     allocate channel c5 type disk format '/migra_UOL/APPRD/%d_APPRD_BKP_12_MAR_2014_%u_%s_%p';
     set limit channel c5 kbytes = 20000000;
     allocate channel c6 type disk format '/migra_UOL/APPRD/%d_APPRD_BKP_12_MAR_2014_%u_%s_%p';
     set limit channel c6 kbytes = 20000000;
     backup current controlfile format '/migra_UOL/APPRD/ctl_for_APPRD_antes.ctl';
     backup database include current controlfile tag = 'Backup_APPRD_12_Mar_2014';
     backup current controlfile format '/migra_UOL/APPRD/ctl_for_APPRD_depois.ctl';
     allocate channel ch1 type disk format '/migra_UOL/APPRD/%d_ARCH_%u_%s_%p';
     backup archivelog all;
}

PS: I used kbytes to set a maximum file size so I could move these files while backup is still running without going out of space.


Best Regards,
Paulo Portugal

Oracle 8i 8.1.7.4 Clone showing errors sqlplus Could not load program sqlplus: Symbol resolution failed for sqlplus because

After cloning Oracle 8i binaries I was getting these errors below when trying to start a sqlplus session:


sqlplus
Could not load program sqlplus:
Symbol resolution failed for sqlplus because:
        Symbol pw_post (number 274) is not exported from dependent
          module /unix.
        Symbol pw_wait (number 275) is not exported from dependent
          module /unix.
        Symbol pw_config (number 276) is not exported from dependent
          module /unix.
        Symbol aix_ora_pw_version3_required (number 277) is not exported from dependent
          module /unix.
Examine .loader section symbols with the 'dump -Tv' command.


Even after checking LIBPATH, LD_LIBRARY_PATH, OS Patches, Relinking binaries without any error the same problem persists.

I them found the patch in MOS 2896876. Ifter downloading it, run the ROOTPRE.sh as ROOT and relink all again.

This fixed my problem.

Best Regards,
Paulo Portugal

USER (ospid: 12124306): terminating the instance due to error 29760 Thu Mar 06 14:58:42 2014 Instance terminated by USER, pid = 12124306

If you got this error below in a RAC environment that you just finished to create/clone, you maybe forgot to set INSTANCE_NUMBER and THREAD parameter.

With two RAC Instances you should be seeing something like below:

SYS@BWOTMHM1> select inst_id,name,value from gv$parameter where name like '%instance_number%';


  INST_ID NAME                                                                             VALUE
---------- -------------------------------------------------------------------------------- ------------------------------
         1 instance_number                                                                  1
         2 instance_number                                                                  2



After everything is ok, double ckeck your threads:

SYS@BWOTMHM1> select thread#,status,enabled,instance from v$thread;

   THREAD# STATUS ENABLED  INSTANCE
---------- ------ -------- --------------------------------------------------------------------------------
         1 OPEN   PUBLIC   BWOTMHM1
         2 OPEN   PUBLIC   BWOTMHM2

SYS@BWOTMHM1>


Best Regards,
Paulo Portugal

Cancel a Query running in another session without killing the session

Hi,

If you want to cancel a query running but not kill the session, you can do that by following these steps below:



1-Create a new session and run something :

  SYS@ORCL>
set serveroutput on
create or replace procedure testing
as
begin
  while 2>1 loop
dbms_output.put_line('Testing killing query!!');
dbms_lock.sleep(1);
end loop;
end;
/

exec testing;


2-On another session , check sid and serial of your session:

SYS@ORCL> select a.sid,s.serial# from v$access a, v$session s where a.object='TESTING' and a.sid = s.sid;



3-Now, cancel your query but not kill the session

SYS@ORCL> exec dbms_system.set_ev(3968,641,10237,4,'');

4-On your fist session, you will see something like below:

SYS@ORCL> exec testing
Testing killing query!!
Testing killing query!!

BEGIN testing; END;

*
ERROR at line 1:
ORA-01013: o usuario solicitou o cancelamento da operac?o atual
ORA-06512: em "SYS.DBMS_LOCK", line 205
ORA-06512: em "SYS.TESTING", line 6
ORA-06512: em line 1


SYS@ORCL>


5-Make sure that you will TURN OFF the event on your session. Otherwise you will get ORA-01013 forever.

SYS@ORCL> exec dbms_system.set_ev(3968,641,10237,0,'');

5-To make sure your session wans't killed, run any new query on your old session:

SYS@ORCL> select count(*) from dict;

  COUNT(*)
----------
      2620
   
   
   
Best Regards,
Paulo Portugal