Database Check and Parameter_check_102

[#1]

Dear Oracle Expert,

When I execute database_check via DB02 then getting Warning against following Parameters.

DBO    ARCHIVE_TOO_OLD	1    Operation: 'Archive log backup' has not been run # Operation too old or has not been run in clean-up period
PROF    LOG_BUFFER	1    Value: 14238720 (>< 4096,512 KB) # Size of redo log buffer in bytes
PROF    OPTIMIZER_INDEX_COST_ADJ	1    Value: 20 (set in parameter file) # Optimizer index cost adjustment
PROF    STATISTICS_LEVEL	1    Value: TYPICAL (set in parameter file) # Statistics level
ORA    Checkpoint not complete	100    Time: Stan-04-09 11.15.52, line: Checkpoint not complete

while I have configured mostly parameter in initSID.ora file but still confuse about following parameters.

Parametercheck for Oracle 10.2. based on Note/Version: 830576/222

Parametercheck last changed: 2012-04-10

Parametercheck Execution: 2012-04-11 15:55:00

DB Startup: 2012-04-11 11:09:27

DB SID: PRD

DB Environment: OLTP, not RAC

DB Platform: Microsoft Windows x86 64-bit

DB Patchset: 10.2.0.5.0

Last detectable DB Mergefix: from SAP Bundle Patch (released 2011-06-10)

Reliability checks: events passed, _fix_controls passed

WARNING: Extended support for 10.2.0.5 only available with a special extended support contract (note 1431752)

Parameter	IS_VALUE	SHOULD_BE_VALUE
event (10411)		set with level 1 if Winbundle>=3
_b_tree_bitmap_plans		set to FALSE if Winbundle<=2
db_writer_processes	4	change default in case of dbwr problems only
_fix_control (4728348)	4728348:OFF	set to 4728348:OFF if Winbundle<=5
_first_spare_parameter	1	set to 1 if Winbundle>=3

please find attached file parameter_check_102 for remaining parameter which i did set in initSID.ora file.

would anyone guide me about the exact value of above parameters and meaning of Winbundle?

Regards,

parameter_check_102.txt.zip

[Former Member · Accepted solution]

Hello Ma Jamil

"WinnBundle" is the SAP Windows bundle.    What your parameter check tells you is to check the parameters against the oss note for your Oracle version to see what is missing/incorrectly set. 

A script which can be run in dbacockpit ->sql editor or SE38 RSORADJV report will compare settings on your system to the relevant paramter note and flag any incorrect settings.  The parameter note is

#830576 for 10g.

The note is #1171650 - parameter check  (please read the note if you have a BI (OLAP) system as there is a change that needs to be made to the script before running.

The note which explains the Bundle terms, etc is note # 1175996

So to anwer your question the above parameters you flagged  for example should be set if the bundle patchset is greater or equal to 3 underlined  here

event (10411)		set with level 1 if Winbundle>=3
_b_tree_bitmap_plans		set to FALSE if Winbundle<=2
db_writer_processes	4	change default in case of dbwr problems only
_fix_control (4728348)	4728348:OFF	set to 4728348:OFF if Winbundle<=5
_first_spare_parameter	1	set to 1 if Winbundle>=3

To get the bundle output simply execute the following SQL statement

select action_time, version, bundle_series, comments

from dba_registry_history

where bundle_series = 'WINBUNDLE'

order by action_time desc

SAP always recommends to run the parameter check script before and after upgrade, patch installation.

Best Regards

Rachel

[former_member182034 · Accepted solution]

hi Rachel,

thanks for your reply,

Dear, the result of mentioned command.

SQL> select action_time, version, bundle_series, comments from dba_registry_hist

ory where bundle_series = 'WINBUNDLE' order by action_time desc;

select action_time, version, bundle_series, comments from dba_registry_history w

here bundle_series = 'WINBUNDLE' order by action_time desc

     *

ERROR at line 1:

ORA-00904: "BUNDLE_SERIES": invalid identifier

while I already shared the OPatch status.

and current status of Oracle is :

SQL> select comp_name, version, status from sys.dba_registry;

COMP_NAME

-----------------------------------------------------------------

VERSION                        STATUS

------------------------------ ---------------------------------

Oracle Database Catalog Views

10.2.0.5.0                     VALID

Oracle Database Packages and Types

10.2.0.5.0                     VALID

the Parameter_Check_102

Parametercheck for Oracle 10.2. based on Note/Version: 830576/222

Parametercheck last changed: 2012-04-10

Parametercheck Execution: 2012-04-16 19:43:13

DB Startup: 2012-04-16 01:33:57

DB SID: PRD

DB Environment: OLTP, not RAC

DB Platform: Microsoft Windows x86 64-bit

DB Patchset: 10.2.0.5.0

Last detectable DB Mergefix: from SAP Bundle Patch (released 2011-06-10)

Reliability checks: events passed, _fix_controls passed

Parameter	SET	IS_VALUE	SHOULD_BE_VALUE
event (10411)	N		set with level 1 if Winbundle>=3
_b_tree_bitmap_plans	N		set to FALSE if Winbundle<=2
db_writer_processes	N	4	change default in case of dbwr problems only
_fix_control (4728348)	Y	4728348:OFF	set to 4728348:OFF if Winbundle<=5
_first_spare_parameter	Y	1	set to 1 if Winbundle>=3

I am really confused about the exact value of above parameters and specially about WinBundle?

Regards,

[former_member524429 · Accepted solution]

Hi,

ERROR at line 1:
ORA-00904: "BUNDLE_SERIES": invalid identifier

From the provided information of init<SID>.ora, its looking like that you have not performed Post Installation steps bundle Patch 10.2.0.5 Patch 14. So complete all the listed/applicable post-installation instructions one by one as mentioned in README.html of bundle patch p13460968_10205_MSWINx86.

As a part of post-installation,

As per point 3.3.5.1 of README.html of bundle patch p13460968_10205_MSWINx86, You will require to perform the following additional actions,

1. set event="8298501 trace name context forever, level 10"

2. Execute catcpu.sql located at %ORACLE_HOME%\BUNDLE\Patch14

3. Execute utlprp.sql  as per step 11 of point 3.3.5.1 (if required..)

Then try to execute the below mentioned query,

select action_time, version, bundle_series, comments from dba_registry_history  where bundle_series = 'WINBUNDLE' order by action_time desc;

It will give you some output.

I am really confused about the exact value of above parameters and specially about WinBundle?

For Oracle Patch installation on Windows, the table filed bundle_series is maintained with word WINBUNDLE in table dba_registry_history.  WINBUNDLE is the similar term as SBP (SAP Bundle Patch). You can find more information about it in SAP Note 1175996

As per the above SQL query, the query must report WINBUNDLE with Patch level 14. In your case WINBUNDLE level will be 14, so you need to set those Oracle parameters accordingly. You need to set following oracle parameters only.

Parameter	SET	IS_VALUE	SHOULD_BE_VALUE
event (10411)	N		set with level 1 if Winbundle>=3

I hope the above information is helpful for your understanding.

--

Regards,

Bhavik G. Shroff

[former_member182034 · Accepted solution]

hi Bhavik,

bundle of thanks to point out the issue. I updated the remaining commands now the status of said command is :

SQL> select action_time, version, bundle_series, comments from dba_registry_hist

ory  where bundle_series = 'WINBUNDLE' order by action_time desc;

ACTION_TIME

---------------------------------------------------------------------------

VERSION                        BUNDLE_SERIES

------------------------------ ------------------------------

COMMENTS

--------------------------------------------------------------------------------

18-APR-12 11.04.35.156000 AM

10.2.0.5                       WINBUNDLE

Patch 14

now what is the exact value of following parameters.

Parameter	IS_VALUE	SHOULD_BE_VALUE
event (10411)		set with level 1 if Winbundle>=3
_b_tree_bitmap_plans		set to FALSE if Winbundle<=2
db_writer_processes	4	change default in case of dbwr problems only
_fix_control (4728348)	4728348:OFF	set to 4728348:OFF if Winbundle<=5
_first_spare_parameter	1	set to 1 if Winbundle>=3

Regards,

[former_member524429 · Accepted solution]

Hi,

The reason for not getting the output of that SQL query was the lack of Post-installation activities. Now, as per the output, your system is running with WINBUNDLE - patch level 14 of Oracle 10.2.0.5. CPU/PSU do updation in that table dba_registry_history which was a part of post-installation activities.

You need to set the following parameter accordingly.

alter system set event="10411 trace name context forever, level 1" scope=spfile sid='*';

In your system _first_spare_parameter is already set with value 1. (see IS_VALUE for parameter _first_spare_parameter). so no need to touch it. For rest of the suggested oracle parameters are not applicable for WINBUNDLE 14.

Re-execute that Parameter check script. Confirm the parameter settings according to recommendations.

Restart your Database after such changes.

--

Regards,

Bhavik G. Shroff

[former_member182034 · Accepted solution]

I really appreciate to your expert reply and explanation.

now please put some light on following parameters which i asked in this post.

DBO    ARCHIVE_TOO_OLD	1    Operation: 'Archive log backup' has not been run # Operation too old or has not been run in clean-up period
PROF    LOG_BUFFER	1    Value: 14238720 (>< 4096,512 KB) # Size of redo log buffer in bytes
PROF    OPTIMIZER_INDEX_COST_ADJ	1    Value: 20 (set in parameter file) # Optimizer index cost adjustment
PROF    STATISTICS_LEVEL	1    Value: TYPICAL (set in parameter file) # Statistics level
ORA    Checkpoint not complete	100    Time: Stan-04-09 11.15.52, line: Checkpoint not complete

the last Ora Checkpoint not complete is resolved after increase the size of redo log.

why am i getting following parameters while these are already exist in initSID.ora file.

OPTIMIZER_INDEX_COST_ADJ =20

STATISTICS_LEVEL = TYPICAL

you can see these parameters in my previous reply.

Thanks and Best Regards,

[former_member524429 · Accepted solution]

Hi,

I would recommend you to run "Cleanup logs" job available in DB13 and then perform "Check database" job in DB13.

If still you are getting the same warnings as you have mentioned, then you need to take actions as following.

1.

DBO

ARCHIVE_TOO_OLD   1

Operation: 'Archive log backup' has not been run # Operation too old or has not been run in clean-up period

It may be possible that Archive log backup is not performed for the complained System. If not yet, then perform it.

2.

PROF

LOG_BUFFER   1

Value: 14238720 (>< 4096,512 KB) # Size of redo log buffer in bytes

You can reconfigure this parameter check condition in DB17 , if still you are getting frequent warning in DB check result. You can set this parameter check with value 14238720 (this is in KB) with "less than /equal" to condition.

3.

OPTIMIZER_INDEX_COST_ADJ

You can remove Active flag for this check condition in DB17

4.

PROF	STATISTICS_LEVEL

You can reconfigure this parameter check condition with check condition "<>Not equal to" and value TYPICAL in DB17

I hope the above information will be helpful to you.

--

Regards,

Bhavik G. Shroff

[former_member182034 · Accepted solution]

Dear Respected Bhavik G,

I have set the mentioned parameter with the help of alter system set command now these parameter are in SPFILE while not showing in PFILE. now i have to create PFILE from SPFILE or no need.

and would you give me last favor, I attached initSID.ora, please have a look and check the value of parameters.

Parameter_check102.txt.zip

[former_member524429 · Accepted solution]

Hi,

now i have to create PFILE from SPFILE or no need.

You can create PFILE manually or you can simply Restart DB instance.

The parameter settings are looking Ok. But keep in mind that when you apply next SAP Bundle Patch /WINBUNDLE Oracle Patch, the some parameter settings will be changed according to relative post installation steps of Bundle patch.

You need to plan for Oracle 10g to Oracle 11g upgrade.

--

Regards,

Bhavik G. Shroff

[Former Member · Accepted solution]

Hello Bhavik G.

My Oracle DB version is the 10.2.0.4, I applied the last interim patch file for Windows X64 (p12429521_10204_MSWINx8664) and I perform all the instructions in point 3.3.6 Post Installation Instructions.

I found this thread when I tried to find some information about what´s the meaning of the word WINBUNDLE that I see in the recommendations in oracle parameters list which shown all the oracle parameters that is recommended by SAP, regarding the sap notes 1171650 - Automated Oracle DB parameter check and 830576 - Parameter recommendations for Oracle 10g.

But now I´m with a little problem... when I run the following SQL query (mentioned in sap note 1175996 - Oracle 10g/11g patches check) anything happens... I don´t know why! Can you please help me...!?

SQL> select action_time, version, bundle_series, comments

from dba_registry_history

where bundle_series = 'WINBUNDLE'

order by action_time desc;

no rows selected

Can you help me please?

Thank you very much,

JDimas