Borndba

Right way to drop the AWR is not catnoawr.sql. It’s documented 436386.1.

Incase ifyou have dropped awr one way to bring it back is catsvrm.sql. Flush the shared pool before that.

Dropping the AWR using catnoawr might have deleted row from WRM$_DATABASE_INSTANCE, restarting the instance will populate the data in this table.
Pls test this in test environment.

convert this post to pdf.

Recently while applying incremental backup in DG running under ASM, got into issue due the rman backup taken with %U.

May be should have used %N%f.dbf this helps in giving both tablespace name and absolute file_id. This information is documented in

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14194/rcmsynta033.htm#sthref531

convert this post to pdf.

Recently upgraded the OMS from 10204 to 10205.
As part of we want to upgrade the agent to 10205 from lower version.
We have around 150 targets. So upgrading each is very difficult. Also fresh install requires to install base version apply 10205 patch, psu patch and other fixes. Doing in each server is difficult.

Instead, we installed 10205 with up to date patch in a sand box. Took tar and moved to servers where we need to patch.

After untaring,
./runInstaller -clone -forceClone ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=agent10205 -noconfig -silent
this will do the proper clone of the agent applying all the bugs
incase if you get complient like home already exist you can try,

./runInstaller -detachHome ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=agent10204 -noconfig -silent

once done

go to $AGENT_HOME/sysman/config
vi emd.properties
check and apply correct port to emd_url,repository_url,emdWallet
once done
secure the agent and start the agent

after which run
$ORACLE_HOME/bin/agentca -f

if its RAC run the below command. For Rac DB agent needs to be installed in both the server. Make sure the directory structures are same.
$ORACLE_HOME/bin/agentca -f -n “cluster1,cluster2″

Due to nic setting we might have issue in RAC. That case

“ListenOnAllNICs=false” in emd.properties if it’s cluster environment and getting http error port already used errorcheck note 443524.1

convert this post to pdf.

After a while did the DG creation using rman. Referred note#789370.1

take init.ora of both primary and standby backup

run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format ‘/oradata/orabackup01/Primary/rman/%U’;
backup archivelog all format ‘/oradata/orabackup01/Primary/rman/%U’;
backup current controlfile for standby format ‘/oradata/orabackup01/Primary/rman/%U’;
}

alter system switch logfile;

scp the files to standby server. Use similar location.

on secondary

$ rman target sys/syspwd@Primary_dg auxiliary /

Recovery Manager: Release 10.2.0.2.0 – Production on Tue May 18 04:12:27 2010

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

connected to target database: Primary (DBID=863765789)
connected to auxiliary database: standby (not mounted)

RMAN> duplicate target database for standby dorecover;

Starting Duplicate Db at 18-MAY-2010 04:12:51
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: sid=155 devtype=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: sid=154 devtype=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Veritas NetBackup for Oracle – Release 6.5 (2008052301)
allocated channel: ORA_AUX_SBT_TAPE_2
channel ORA_AUX_SBT_TAPE_2: sid=153 devtype=SBT_TAPE
channel ORA_AUX_SBT_TAPE_2: Veritas NetBackup for Oracle – Release 6.5 (2008052301)

contents of Memory Script:
{
set until scn 8978424429104;
restore clone standby controlfile;
sql clone ‘alter database mount standby database’;
}
executing Memory Script

executing command: SET until clause

Starting restore at 18-MAY-2010 04:25:19
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /oradata/orabackup01/standby/rman/jolduq5h_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oradata/orabackup01/standby/rman/jolduq5h_1_1 tag=TAG20100518T011321
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:18
output filename=+DISK_GROUP1/standby/controlfile/control01_standby.ctl
Finished restore at 18-MAY-2010 04:25:40

sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1
released channel: ORA_AUX_DISK_2
released channel: ORA_AUX_SBT_TAPE_1
released channel: ORA_AUX_SBT_TAPE_2

contents of Memory Script:
{
set until scn 8978424429104;
set newname for clone tempfile 1 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
set newname for clone datafile 7 to new;
set newname for clone datafile 8 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 10 to new;
set newname for clone datafile 11 to new;
set newname for clone datafile 12 to new;
set newname for clone datafile 13 to new;
restore
check readonly
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed temporary file 1 to +DISK_GROUP1 in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 18-MAY-2010 04:26:02
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=153 devtype=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: sid=154 devtype=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: sid=155 devtype=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Veritas NetBackup for Oracle – Release 6.5 (2008052301)
allocated channel: ORA_AUX_SBT_TAPE_2
channel ORA_AUX_SBT_TAPE_2: sid=156 devtype=SBT_TAPE
channel ORA_AUX_SBT_TAPE_2: Veritas NetBackup for Oracle – Release 6.5 (2008052301)

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DISK_GROUP1
restoring datafile 00005 to +DISK_GROUP1
restoring datafile 00007 to +DISK_GROUP1
restoring datafile 00008 to +DISK_GROUP1
restoring datafile 00009 to +DISK_GROUP1
restoring datafile 00010 to +DISK_GROUP1
restoring datafile 00013 to +DISK_GROUP1
channel ORA_AUX_DISK_1: reading from backup piece /oradata/orabackup01/standby/rman/jkldupnn_1_1
channel ORA_AUX_DISK_2: starting datafile backupset restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +DISK_GROUP1
restoring datafile 00003 to +DISK_GROUP1
restoring datafile 00004 to +DISK_GROUP1
restoring datafile 00006 to +DISK_GROUP1
restoring datafile 00011 to +DISK_GROUP1
restoring datafile 00012 to +DISK_GROUP1
channel ORA_AUX_DISK_2: reading from backup piece /oradata/orabackup01/standby/rman/jjldupnm_1_1
channel ORA_AUX_DISK_2: restored backup piece 1
piece handle=/oradata/orabackup01/standby/rman/jjldupnm_1_1 tag=TAG20100518T010558
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:06:15
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oradata/orabackup01/standby/rman/jkldupnn_1_1 tag=TAG20100518T010558
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:06:31
Finished restore at 18-MAY-2010 04:32:41

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy recid=41 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/system.413.719295981
datafile 2 switched to datafile copy
input datafile copy recid=42 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/undo1.407.719295981
datafile 3 switched to datafile copy
input datafile copy recid=43 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/sysaux.345.719295971
datafile 4 switched to datafile copy
input datafile copy recid=44 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/users.416.719295983
datafile 5 switched to datafile copy
input datafile copy recid=45 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/ts_aud_data_purge.405.719295981
datafile 6 switched to datafile copy
input datafile copy recid=46 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/tspan_data.351.719295975
datafile 7 switched to datafile copy
input datafile copy recid=47 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/tspan_index.410.719295979
datafile 8 switched to datafile copy
input datafile copy recid=48 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/fuego_data.474.719295975
datafile 9 switched to datafile copy
input datafile copy recid=49 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/fuego_index.368.719295977
datafile 10 switched to datafile copy
input datafile copy recid=50 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/adw_data.342.719295971
datafile 11 switched to datafile copy
input datafile copy recid=51 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/adw_index.409.719295981
datafile 12 switched to datafile copy
input datafile copy recid=52 stamp=719296363 filename=+DISK_GROUP1/standby/datafile/reserve.411.719295979
datafile 13 switched to datafile copy
input datafile copy recid=53 stamp=719296363 filename=+DISK_GROUP1/standby/datafile/streams_tbs.406.719295983

contents of Memory Script:
{
set until scn 8978424429104;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 18-MAY-2010 04:32:45
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2

starting media recovery

Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘+DISK_GROUP1/standby/datafile/system.413.719295981′

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/18/2010 04:33:00
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 203315 lowscn 8978424428500 found to restore
RMAN-06025: no backup of log thread 1 seq 203314 lowscn 8978424427332 found to restore
RMAN-06025: no backup of log thread 1 seq 203313 lowscn 8978424419381 found to restore
RMAN-06025: no backup of log thread 1 seq 203312 lowscn 8978255748027 found to restore

exit from rman. Ignore the above error time being.

go to primary

alter system set log_archive_dest_state_2 = enable ;

go to standby

alter database recover managed standby database disconnect from session;

This should catch up the standby with the missing arch reported above.

execute the below command in standby, you will get the status.

select sequence#,applied from v$archived_log order by sequence#;

convert this post to pdf.

I am trying to remove agent from OEM. It’s running for quite a while and no result seen. If your case is like this, you can use below command to remove.

exec mgmt_admin.cleanup_agent(‘hostname:1830′);

if you want to delete any target from command line use

exec mgmt_admin.delete_target(‘target_name’,'oracle_database’);

followed by

commit;

convert this post to pdf.

Our managment decided to give OEM access to development DBA. We were worried that development DBA might share the password to developer. There are 100s of developer in our environment. If everyone login to the OEM, this would create night mare. Since OEM is administration tool, we would prefer to be used by DBA.
How to protect development DBA sharing their pwd with developer. We tried something similar to sessions_per_user profile. Which didn’t work, as it is only for oracle user and not for OEM user.
Other approach is enable auditing on OEM user and if the login is coming from more than one ip, alert the dba.
By default, ip addr and hostname will not be registered in mgmt$audit_log. To enable it follow the metalink note#457382.1

exec mgmt_audit_admin.set_audit(0, null, 0);
shutdown the oms,opmn,agent
update
$OMS_HOME/Apache/Apache/conf/httpd.conf

make these 2 entries
UseWebCacheIp On
HostnameLookups On

restart the OMS.
After which each login in insert into mgmt_user_sessions table along with hostname and ipaddr. If a userid is used in more than one ipaddr, we can easily track.
select EM_USER,
             EM_USER_TYPE,
             EM_USER_HOST_NAME,
             OMS_HOST_NAME ,
             IP_ADDRESS
   from  MGMT_USER_SESSION
order by LOGIN_TIME desc;

But problem is this table might keep growing.
To do the purge,
Stop oms
exec mgmt_audit_admin.set_audit(0,null,2)
exec mgmt_audit_admin.audit_purge(’11-Jan-10′); — Give sysdate+1 to remove all the data

enable auditing
exec mgmt_audit_admin.set_audit(0, null, 0);
start OMS.

convert this post to pdf.

we have a requirement to alert for backup failures. Connecting to each rman repository or db to get this information is difficult. We have OEM, and this how we have implemented. We created UDM and applied to necessary targets.

Monitor DB fullbackup.

select count(*) from (select rownum no,x.status,x.end_time from (select status,end_time from v$rman_status where end_time is not null and object_type=’DB FULL‘ and end_time > sysdate-1 order by end_time desc) x) y where y.no=1 and y.status = ‘COMPLETED’

Monitor DB incr,

select count(*) from (select rownum no,x.status,x.end_time from (select status,end_time from v$rman_status where end_time is not null and object_type=’DB INCR‘ and end_time > sysdate-1 order by end_time desc) x) y where y.no=1 and y.status = ‘COMPLETED’

Archivelog backup.

select count(*) from (select rownum no,x.status,x.end_time from (select status,end_time from v$rman_status where end_time is not null and object_type=’ARCHIVELOG’ and end_time > sysdate-1 order by end_time desc) x) y where y.no=1 and y.status =’COMPLETED’

Other similar query which i came across is

select (sysdate-min(t))*24 from
(
  select max(b.CHECKPOINT_TIME) t
  from v$backup_datafile b, v$tablespace ts, v$datafile f
  where INCLUDED_IN_DATABASE_BACKUP='YES'
  and f.file#=b.file#
  and f.ts#=ts.ts#
  group by f.file#
)

for arch log monitoring,

select (sysdate-max(NEXT_TIME))*24 from v$BACKUP_REDOLOG

convert this post to pdf.

We have an requirement, where we need to purge portion of data from huge table. After much discussion, we have decided go to like
1. create temp table
2. populate only relevant data from main table to temp table
3. drop main table.
4. rename temp table to main table and recreate missing indexes.

there are various approach to do this. But instead we have gone with something like split the big table in to logical split using rowid and with each logical split populate the temp table parallel.

create table scuser.tab1_bk as select * from scuser.tab1 where 1=2;

alter table scuser.tab1_bk nologging;

truncate table scuser.tab1_bk;

Drop table split_rowid purge;

CREATE TABLE split_rowid as select distinct grp,
first_value(relative_fno) over
(partition by grp order by relative_fno, block_id
rows between unbounded preceding and unbounded following) lo_fno,
first_value(block_id ) over
(partition by grp order by relative_fno, block_id
rows between unbounded preceding and unbounded following) lo_block,
last_value(relative_fno) over
(partition by grp order by relative_fno, block_id
rows between unbounded preceding and unbounded following) hi_fno,
last_value(block_id+blocks-1) over
(partition by grp order by relative_fno, block_id
rows between unbounded preceding and unbounded following) hi_block,
sum(blocks) over (partition by grp) sum_blocks
from (
select relative_fno,
block_id,
blocks,
trunc( (sum(blocks) over (order by relative_fno, block_id)-0.01) /
(sum(blocks) over ()/&chunk) ) grp
from dba_extents
where segment_name = ('TAB1')
and owner = '&owner' order by block_id
)
/

Create or replace procedure purge_clock(p_no number,lo_rowid rowid,hi_rowid rowid) as
type array is table of scuser.tab1_bk%rowtype;
l_data array;
cursor c is select /+* first_rows */ * from tab1 a where (exists
(select 1 from probsummarym1
where probsummarym1.numberprgn = a.key_char
and a.type = 'problem')
or a.datetime_last_upd > (sysdate-3)
or type <> 'problem')
and substr(name,0,7) <> 'JPASRPT' and rowid between lo_rowid and hi_rowid;;

lv_cnt number:=0;
lv_cnt1 number:=0;
BEGIN
open c;
loop
fetch c bulk collect into l_data LIMIT 10000;
lv_cnt:=lv_cnt+1;
lv_cnt1 := lv_cnt*10000;
dbms_application_info.set_client_info(p_no||' of Clock:'||lv_cnt1);
forall i in 1 .. l_data.count
Insert into scuser.tab1_bk values l_data(i);
COMMIT;
exit when c%notfound;
END LOOP;
CLOSE c;
commit;
END;
/

alter system set job_queue_processes=20;

set serveroutput on;
Declare
cursor c1 is
select rownum no,
dbms_rowid.rowid_create(1,
(select data_object_id from dba_objects where object_name='tab1' and object_type='TABLE'),lo_fno,lo_block,0) lo_rowid,
dbms_rowid.rowid_create(1,
(select data_object_id from dba_objects where object_name='tab1' and object_type='TABLE'),hi_fno,hi_block,32767) hi_rowid
from split_rowid order by no;

lv_job number;
begin
for i in c1 loop
dbms_job.submit(lv_job,'purge_clock('||i.no||','||chr(39)||i.lo_rowid||chr(39)||','||chr(39)||i.hi_rowid||chr(39)||');');
dbms_output.put_line('Job#'||lv_job);
commit;
end loop;
commit;
end;
/

convert this post to pdf.

If your shop using OEM as monitoring tool, then you should be aware of this. If not try it out. Create a group and put all the DB under that group. If you get into the group you will see the launch dashboard. That will invoke the console. This will be one place where you get all the information. Critical,warning, target down, target unknow information. There is a option to customize also.
In our shop we have given a extra TFT monitor to each DBA and he moves this console to this extra monitor. After implementing this there was tremendous progress in our monitoring ability. We are on top of all the alerts now. If budget permits you can go for a wall mounted LCD

convert this post to pdf.

I configured a oem agent 10204 in one of the AIX server. After configuration started the agent  and the agent started successfully.

But whenever we try to upload, we got error something like below.

 

$ ./emctl upload agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
—————————————————————
EMD upload error: XML error during upload to
https://hostname:1159/em/upload: direct-load :
A0000070.xml, will be renamed to A0000070.err.

*.err file has similar error message as shown the subject.

While analyzing we have found that the issue is related to password change of dbsnmp username.

We followed the recommendation given in metalink#259387.1 to resolve this issue.

The error message was vague and we tried various options including changing nls_lang setting.

convert this post to pdf.

A client mailed us with a query, which crashes with 4031 error. It’s a complex query has Nl and Hash joins. While diagonising i found that the problem lies with the below two line of where condition.

AND  ( (GL_MONTH_ACTV_BAL.BUS_UNIT_ID)=SAP_BUS_UNIT_MO_ASIS.BUS_UNIT_ID and SAP_BUS_UNIT_MO_ASIS.BUS_UNIT_CD_TYPE in (‘XYZ123′ ,’ABC’)  )
 AND  ( (GL_MONTH_ACTV_BAL.GLAS_BUS_UNIT_ASIS_ID)=GLAS_BUSINESS_UNIT.BUS_UNIT_ID   AND GLAS_BUSINESS_UNIT.BUS_UNIT_CD_TYPE IN (‘ABC123′ ,’ABC’)  )

When i change the query to

AND  ( (to_number(GL_MONTH_ACTV_BAL.BUS_UNIT_ID))=SAP_BUS_UNIT_MO_ASIS.BUS_UNIT_ID and SAP_BUS_UNIT_MO_ASIS.BUS_UNIT_CD_TYPE in (‘XYZ123′ ,’ABC’)  )
 AND  ( (to_number(GL_MONTH_ACTV_BAL.GLAS_BUS_UNIT_ASIS_ID))=GLAS_BUSINESS_UNIT.BUS_UNIT_ID   AND GLAS_BUSINESS_UNIT.BUS_UNIT_CD_TYPE IN (‘ABC123′ ,’ABC’)  )

It worked. The funny thing here is the column GL_MONTH_ACTV_BAL.BUS_UNIT_ID is  of number datatype.  We were not able to even get the explain plan user “Set autot trace exp”, as the query crashes with 4031 error.

On error and trial of this query, we found that ‘star_transformation_enabled’ is the culprit. It’s pure luck i found this one. I have to change this value from true to false.

I can’t set this at db level or session level, as it might have impact on other queries. That’s where we used /*+opt_param*/ hint

SELECT /*+ OPT_PARAM(‘star_transformation_enabled’,'false’) */

convert this post to pdf.

url what you see will make little sense compare to earlier one which will be something like /p?N. I was not able to change permalink due to restriction with yahoo hosting, where it doesn’t allow editing or even uploading a file starting with dot(.)
I am able to get a workaround by including /index.php at beginning in permalinks. So the url will make sense with only different of index.php added to it.
Which i am work for time being.

convert this post to pdf.