on 10-13-2011 10:43 AM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
> 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.
> 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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.