Pb with SYS.BSLN_MAINTAIN_STATS_JOB after a SAP Refresh

[benoit-schmid]

Hello,

We are using ECC 6.04 with Oracle 11.

I have done a Sap Dev Refresh from my PRD database.

It works fine. But this week I got the following error:

---

ORA-12012: error on auto execute of job SYS.BSLN_MAINTAIN_STATS_JOB

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at DBSNMP.BSLN_INTERNAL, line 2073

ORA-06512: at line 1

---

I did not see any doc on MetaLink.

After searching on the web I just found http://dbhk.wordpress.com/category/troubleshooting/page/2/

that specify to do the following:

SQL> @?/rdbms/admin/catnsnmp.sql

SQL> @?/rdbms/admin/catsnmp.sql

It seems to be similar a pb for me as I have the following:

SQL> select * from dbsnmp.bsln_baselines;

1824967783 DEV 0 BB52B57B6FE4FFB935403F2204784723 NW Y

ACTIVE 09-OCT-11

1824967783 PRD 0 A0C45375C1F8DCF973AA0A27D99AF839 HX Y

ACTIVE 25-SEP-11

The solution is to drop and recreate the dnsmp user.

As I have never performed this, in the past I have the following questions.

1. Have you ever faced this problem?

2. What is the risk of dropping and creating the dbsnmp user?

Thanks in advance for your answer.

[willi_eimler · Accepted solution]

Dear Mr Schmidt,

you don’t need to drop the dbsnmp user. The error is a result of the system copy. The cause of the error is an inconsistent entry in table  DBSNMP.BSLN_BASELINES.

With statements

set linesize 300
select * from DBSNMP.BSLN_BASELINES;

you will find two entries. One with the original SAPSID (For example: you made a system copy from OOO to ZZZ, then you will find an entry with OOO)

OUTPUT:
DBID INSTANCE_NAME    BASELINE_ID BSLN_GUID             TI A STATUS           LAST_COMP
---------- ---------------- ----------- -------------------------------- -- - ---------------- ---------
120131277 OOO   0 5D0A9F344824DD82EDFCC5A5418D1895 NW Y ACTIVE        14-APR-12
120131277 ZZZ     0 7C4B653E6DB147518F78A19F704031D3   HX  Y ACTIVE        14-APR-12

Delete the entry with the original SAPSID (in the example it is OOO)

delete from DBSNMP.BSLN_BASELINES where INSTANCE_NAME='<original SAPSID>';
commit;

Now start the job again.

exec dbms_scheduler.run_job('BSLN_MAINTAIN_STATS_JOB',false);

With statement

select log_date,status from dba_scheduler_job_run_details where job_name='BSLN_MAINTAIN_STATS_JOB';

you can check  the result of job BSLN_MAINTAIN_STATS_JOB.
OUTPUT:
LOG_DATE                                                                    STATUS
--------------------------------------------------------------------------- ------------------------------
12-MAY-12 12.00.01.507421 AM +02:00                                         FAILED
19-MAY-12 12.00.01.837181 AM +02:00                                         SUCCEEDED
05-MAY-12 12.00.02.536184 AM +02:00                                         FAILED
28-APR-12 12.00.02.207433 AM +02:00                                         FAILED

You should not get the error >ORA-12012: error on auto execute of job “SYS”.”BSLN_MAINTAIN_STATS_JOB”< again.

Best regards
Willi Eimler

[benoit-schmid · Accepted solution]

Hello Willy,

Willi Eimler wrote:
you don’t need to drop the dbsnmp user. The error is a result of the system copy. The cause of the error is an inconsistent entry in table  DBSNMP.BSLN_BASELINES.

I agree with you.

Dropping the whole schema is brute force method.

But it takes less characters to write 🙂

Thanks for your feedback.

[Former Member]

Forgot your questions -

1. Have you ever faced this problem? - No

2. What is the risk of dropping and creating the dbsnmp user? - No Risk, normally in 'Oracle with SAP' we keep this user locked. It is used for Enterprise manager functionality when we don't use Oracle with SAP.

Thanks

[benoit-schmid]

> 2. What is the risk of dropping and creating the dbsnmp user? - No Risk, normally in 'Oracle with SAP' we keep this user locked. It is used for Enterprise manager functionality when we don't use Oracle with SAP.

1. Is there a nicer way, to solve this problem than drop and create?

2. Is there a note that documents your oracle schema lockings?

3. FYI, my jobs are disabled as documented in note 974781

SQL> SELECT WINDOW_NAME,

AUTOTASK_STATUS,

OPTIMIZER_STATS,

SEGMENT_ADVISOR,

SQL_TUNE_ADVISOR

FROM DBA_AUTOTASK_WINDOW_CLIENTS 2 3 4 5 6 ;

WINDOW_NAME AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE

-

---

-

---

-

---

-

---

-

---

MONDAY_WINDOW DISABLED DISABLED DISABLED DISABLED

TUESDAY_WINDOW DISABLED DISABLED DISABLED DISABLED

WEDNESDAY_WINDOW DISABLED DISABLED DISABLED DISABLED

THURSDAY_WINDOW DISABLED DISABLED DISABLED DISABLED

FRIDAY_WINDOW DISABLED DISABLED DISABLED DISABLED

SATURDAY_WINDOW DISABLED DISABLED DISABLED DISABLED

SUNDAY_WINDOW DISABLED DISABLED DISABLED DISABLED

7 rows selected.

SQL> SELECT CLIENT_NAME, STATUS

FROM DBA_AUTOTASK_CLIENT 2 ;

CLIENT_NAME STATUS

-

---

-

---

auto optimizer stats collection DISABLED

auto space advisor DISABLED

sql tuning advisor DISABLED

Thanks in advance for your answer.

[benoit-schmid]

> 2. What is the risk of dropping and creating the dbsnmp user? - No Risk, normally in 'Oracle with SAP' we keep this user locked. It is used for Enterprise manager functionality when we don't use Oracle with SAP.

Hello,

You are right it has been locked by sap post commands:

SQL> select username,ACCOUNT_STATUS from dba_users;

USERNAME ACCOUNT_STATUS

-

---

-

---

SAPTST OPEN

SYSTEM OPEN

SYS OPEN

OPS$ORATST OPEN

OPS$TSTADM OPEN

OUTLN OPEN

OPS$XYMON OPEN

DBSNMP LOCKED

DIP EXPIRED & LOCKED

ORACLE_OCM EXPIRED & LOCKED

APPQOSSYS EXPIRED & LOCKED

11 rows selected.

As the job is started by SYS, it is launched.

Which method should I use to avoid this error after each refresh?

Thanks in advance for your answer.

[Former Member]

Hi Benoit,

What activity were you doing when you observed this error, any specific transaction, any specific job ?

Just to confirm, do you already have latest patch/SBP applied for oracle 11g ?

Thanks

[benoit-schmid]

Hi Benoit,
> 
> What activity were you doing when you observed this error, any specific transaction, any specific job ?
> 
> Just to confirm, do you already have latest patch/SBP applied for oracle 11g ?
> 
> 
> Thanks

Hello,

I was not doing special activity.

This jobs is scheduled by Oracle each Sunday.

The problem comes from the refrresh.

This is the only thing that has changed.

Thanks in advance for your help.