Error and Warrnings in D02

[former_member209962]

Dear All,

In DB02 under alerts database check I see errors and warnings and and date of this errors is mention as 2011 which is very old does it mean that this issue came in 2011 and now these issues are no more? only two errors are for 2012 please find below log

Table: SAPSR3.ZTB_PAR_CHLD # Table or index has no optimizer statistics

E    03.09.2012    23:50:43    3    DBA    MISSING_STATISTICS

2    Table: SAPSR3.ZTB_PAR_CHLD # Table or index has no optimizer statistics

W    03.09.2012    10:40:53    3    ORA    Checkpoint not complete

49    Time: 2011-04-12 19.40.08, line: Checkpoint not complete

I am running update statistics daily and it complete successfully, DB check i started running from past 3 to 4 days only.

Kindly help me to fix this issue

Table: SAPSR3.ZTB_PAR_CHLD # Table or index has no optimizer statistics

Value: 20 (set in parameter file) # Optimizer index cost adjustment

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

Time: 2011-05-02 18.36.27, line: ORA-00060: Deadlock detected. More info in file d:\oracle\fnp\saptrace\usertrace\fnp_ora_8708.t

Time: 2011-08-22 13.48.02, line: ORA-00600: internal error code, arguments: [kspsetpao1], [2142], [2102], [*], [user_dump_dest],

Time: 2011-07-20 14.22.46, line: ORA-1113 signalled during: alter database open...

Time: 2011-04-19 15.44.27, line: ORA-01114: IO error writing block to file 16 (block # 1)

Time: 2011-04-19 15.44.28, line: ORA-01122: database file 16 failed verification check

Time: 2011-04-27 20.26.41, line: ORA-1654: unable to extend index SAPSR3.D010INC^0 by 128 in tablespace               PSAPSR3701

Time: 2011-04-27 20.27.31, line: ORA-1691: unable to extend lobsegment SAPSR3.SYS_LOB0000198903C00013$$ by 128 in tablespace

Time: 2012-01-01 17.03.17, line: ORA-16014: log 3 sequence# 13587 not archived, no available destinations

Time: 2012-01-01 17.03.11, line: ORA-16038: log 3 sequence# 13587 cannot be archived

Time: 2012-01-01 17.03.11, line: ORA-19504: failed to create file "D:\ORACLE\FNP\ORAARCH\FNPARCH1_13587_748293311.DBF"

Time: 2012-01-01 17.03.11, line: ORA-27044: unable to write the header block of file

Time: 2011-04-12 19.40.08, line: Checkpoint not complete

Thanks

Tabrez

[Former Member · Accepted solution]

Hi Tabrez,

You can ignore most of the items belong to the 2011, except followings;

>> Table: SAPSR3.ZTB_PAR_CHLD # Table or index has no optimizer statistics

However the stats should be executed automatically, it should be investigated why the stats has not been collected for the table. So for the quick solution,execute DB20 transaction and create the stats manually specifically for the table.

>> Value: 20 (set in parameter file) # Optimizer index cost adjustment

Check the Oracle profile parameters

>> Time: 2011-07-20 14.22.46, line: ORA-1113 signalled during: alter database open...

>> Time: 2011-04-19 15.44.27, line: ORA-01114: IO error writing block to file 16 (block # 1)

>> Time: 2011-04-19 15.44.28, line: ORA-01122: database file 16 failed verification check

It is better to verify the datafile with "dbv". Check the Note 365481 - Block corruptions

>> Time: 2011-04-12 19.40.08, line: Checkpoint not complete

You should increase the online redolog size or add new files into the set. Check the Note 79341 - Checkpoint not complete

Best regards,

Orkun Gedik

[former_member209962 · Accepted solution]

Dear Orkun,

Thanks a lot for providing such a good explanation.

In the last DB check i don't see any errors except

BR0978W Database profile alert - level: WARNING, parameter: OPTIMIZER_INDEX_COST_ADJ, value: 20 (set in parameter file)

As per your suggestion i have created stats for that particular table in db20 and it was successful

can you please let me know exact command for running verify db job from brtools, to run this job i guess sap has to be stopped.

And also for increasing online redolog files as per note 79341

• Drop logfile group 11 (usually the number of the first log file group):
  
  sqlplus "/ as sysdba"
    ALTER DATABASE DROP LOGFILE GROUP 11;

• Remove related operating system files, e.g.:
  
  UNIX:    rm /oracle/<sid>/origlogA/log_g11_m1.dbf
          rm /oracle/<sid>/mirrlogA/log_g11_m2.dbf
  WINDOWS: del <drive>:\oracle\<sid>\origlogA/log_g11_m1.dbf
          del <drive>:\oracle\<sid>\mirrlogA/log_g11_m2.dbf

                     Do not remove the files before the related logfile group is dropped!

• Recreate the logfile group 11 with a bigger size <size>:
  
  ALTER DATABASE ADD LOGFILE GROUP 11
  ('/oracle/<sid>/origlogA/log_g11_m1.dbf',
  '/oracle/<sid>/mirrlogA/log_g11_m2.dbf')
  SIZE <new_size>M;

• Do the same for all other existing logfile groups.

is it safe to drop the log file group and also can we just increase size for one log file or we have to increase for all log files??

Thanks

Tabrez

[volker_borowski2 · Accepted solution]

DBV can be used online.

brbackup -t online -m all -w only_dbv -e 4

You can do it from DB13 if you like, but you need to have "online" as a default in initSID.sap otherwise your system will be shut down.

Better than DBV is RMAN for validation, but it is quite costy in terms of CPU, so I would only spend two processes to do it or even only a single one if you do not have enough cpu (-e parameter).

brbackup -t online -m all -w only_rmv -e 2

As for the logs: resize can be executed with brtools meanwhile. There is an option : "resize onlinelogs" that is doing all the steps required. If you like to go manually, check that the drop is really succeded, before you execute the delete. If it fails, because the log is still active, execute a logswitch and retry the drop. The drop must be successfull before you can delete the files otherwise you can severly damage your DB. I'd recommend to use brtools. Try in testsystem first, to make yourself familiar with the operation of the resize.

Keep in mind, that controlfiles are growing dynamicly, and you might have a copy in one of your log filesystems (drives). Be sure that there is enough space for them available after the log resize.

Hope this helps

Volker

[former_member209962 · Accepted solution]

Once again thanks!!

Can you please let me know where to change default as online in initsid.sap.

For backup type its already mention as online

Thanks

Tabrayz

[janos_mucsi-besze · Accepted solution]

Dear Tabrayz,

The init<SID>.sap can be found in the ORACLE_HOME directory under the dbs (for UNIX) or under database (for Windows)  directory.

To the change the online option there, you need to modify the value of the parameter of 'backup_type'.

Hope, this helps.

János

[Former Member · Accepted solution]

Why don't you use DB13 to schedule your backup, instead of starting it manually?

Best regards,

Orkun Gedik

[former_member209962 · Accepted solution]

Dear Orkun,

Well backup is taken care by HP data protector  and backup type is already offline in initsid.sap

Thanks

Tabrez

[former_member209962 · Accepted solution]

i mean  backup type is already online initsid.sap