vineri, 21 martie 2014

EMD upload error: Upload timed out before completion. Number of files to upload before the upload: xxx ...

Bad time. This error can be very time consuming. The message itself doesn't say anything useful, you should go deeper and search for the cause within the logs. 
Most often cause is a time desynchronization between OMS and the grid agent or between the OMS and the agent that is faulty.
First, asumming the OMS is working and the Cloud Control can manage the agents, go to Setup -> Agents in Could Control Panel. Here, you can see some important tools and methods, useful for the administration of agents. First, looked at the Blocked column, for the coresponding agent. If the value is Yes, of course you can imagine the things cannot stay like this. Unblock it and retry to upload. If you not succeed, go to step 2: click on the name of the host, you will get to the managing panel of that agent, click Agent -> Resynchronization. A job will be scheduled and started and you cand follow it through the provided link.
When the job is done, go back to the host with the agent problem and try to upload. If doesn't succeed, next step is to mine into the logs. The most important log in the $AGENT_HOME/sysman/log folder is the gcagent.log, by my opinion. If the root cause of the error is a TZ mismatch (TZ=timezone), you should try a TZ reset:

vi $AGENT_HOME/sysman/config/emd.properties

-comment the agentTZRegion line
-save and exit
-check out the time zone of the operating system:

[oracle@orastdby config]$ cat /etc/sysconfig/clock
# The time zone of the system is defined by the contents of /etc/localtime.
# This file is only for evaluation by system-config-date, do not rely on its
# contents elsewhere.
ZONE="Europe/Bucharest"

-set the TZ variable, using the value from the /etc/sysconfig/clock file:

export TZ=Europe/Bucharest

-reset the agent:

emctl stop agent
emctl resetTZ agent
emctl start agent
emctl upload agent

If the problem is still there, send me an email and i will try to help. All the best!

miercuri, 19 martie 2014

How to rename the database, instance and the XDB service

When needed, don't change only the name of the database or only the name of the instance.
I prefer to change all of the above three names, in order to have a naming rule consistency and for the sake of an easy administration.

Steps:

sql> shutdown immediate
sql> startup mount
sql> exit

$ nid target=sys dbname=somename setname=yes

Answer Y when prompted!


sql> shutdown immediate
sql> startup nomount
sql> alter system set db_name=somename scope=spfile;
sql> alter system set instance_name=somename scope=spfile;
sql> alter system set dispatchers='(protocol=tcp)(service=somenameXDB)' scope=both;
sql> shutdown immediate
sql> exit

Change the ORACLE_SID in your environment script and run it or change on the fly the value of ORACLE_SID with export ORACLE_SID=somename !

sql> startup
sql> exit

lsnrctl reload

Now you have all the important names in your database changed.


marți, 18 martie 2014

Get rid of ORA-04091 table owner.table_name is mutating, trigger/function may not see it.

I won't show how you can get into this error, i am sure you know, because you are here. Briefly, if you make a trigger against some table and you rely in the body of the trigger on the same table data, not to mention if you try to modify data on that table, the above mentioned error will show up.

The workarounds are about three and i will concentrate here only on one of them: using PRAGMA AUTONOMOUS_TRANSACTION and a procedure invoked by the trigger.

So, the code will be:

create or replace procedure proc_ex
(pdi in number) as
pragma autonomous_transaction;
cated number;
begin
select count(*) into cated from  table_ex
            WHERE   field1=1 and field2=1 and field3=pdi;
if cated >0 then
    Raise_application_error (
      -20300,
      'It's wrong!! ' 
    );            
end if;
end;
/            

CREATE OR REPLACE TRIGGER trigger_ex
before update
 of field2,field1 ON table_ex  
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare 
exc exception;
cate number;
BEGIN
if (:old.field1=0 or :old.field2=0) and (:new.field1=1 AND  :new.field2=1) then --verify if any of the two fields was 
                     --changed and the both are 1, after the 
                     --change
    proc_ex (:new.field3);
end if;    
END ;
/

That's it! Good luck!

joi, 13 martie 2014

Find a trigger, searching by the raised exception content

Sometimes, you can be caught in a sensitive situation, staring at the error issued by some application, and being clueless about where from that error appeared. If it is a database trigger, you can easily find:

select * from user_source where type='TRIGGER' and upper(text) like '%some text%another piece of text%and so on%';
   
As you can see, you can look after more than one piece of text, by once.

But keep in mind my advice, better put in the exception text the name of the trigger and maybe some other information you can think is useful (the name of the table, some inline variables, etc).

miercuri, 12 martie 2014

What to do when you forgot about cleaning the fast recovery area on your standby

We assume, from the scratch, we've got a standalone standby database over an asm instance. If the total amount of archived logs, came from primary site, meets the capacity of  the FRA, sometimes this could lead to very big issues.
Test the level of emptiness on FRA:

col name format a30;
SELECT 
NAME,
TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,
TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE,
'999,999,999,999') AS SPACE_AVAILABLE,
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)
AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;

If the percent_full column indicates 
Normally, you can connect with rman and delete some archivelogs by issuing:

rman target /
delete archivelog until time 'sysdate-30';

where "30" is the number of days you want to remain safe by preserving the archived logs.

Well, let's say the operation above will not succeed and the result is a a frozen prompt. Believe me, it's a very posible situation.
What next? The last resort seems to be the access of archived logs through the file system. If this is the ASM Oracle proprietary one, you have to go under the grid user and run asmcmd utility:

asmcmd
ASMCMD> ls
CRS/
DATA/
ASMCMD>cd data
.....
and so on, until you reach the archivelogs folder. Well, now the last ls could end, also, in a frozen blinking prompt, waiting undefinitely to see the content of the archivelogs folder. Again, trust me, sounds very likely, don't say "this won't happen to me".
Next step could not be else than restarting the machine. The both ways that can be used to delete archivelogs are shut. 
After reboot, 99% probably the cluster services are not ok. Probably te cssd daemon is down. This means you cannot even mount the database, no way to open it. Of course, you cannot open neither the ASM instance, through the traditional sqlplus way.
A solution can be to verify and start the crs services and asm instance. This can be done in one tempo.

srvctl status asm
srvctl stop asm
srvctl start asm
sqlplus / as sysasm
startup 

then:

su - oracle
sqlplus / as sysdba
startup
exit
rman target /
delete archivelog until time 'sysdate-30';

if, after:


tail -f -n 50 $ORACLE_BASE/..../alert_$ORACLE_SID.log

you will notice that the gap is not recovered yet and the transport is still not established, probably the listener is the cause. If, after lsnrctl start command, the problem persist, maybe the database is not registered, this is a known issue, when the standby was made by grid control wizard. So:

sqlplus / as sysdba
alter system register;
exit

and if the problem persists, cool down and send a message to me.