oracle database verification

[Former Member]

Hi

After i've installed a new production system, we had a disk problem on a partition and we've lost oraarch, origlogA and origlogB folders. I've created oraarch folder. I only lost offline redo log files and it is not very important because users has never been log on this system. I've created origlogA and origlogB folder and copy content of mirrlogA and mirrlogB folder to these folders

But i am not sure this database is healty or not. How can i sure about this issue? Have you got any recommendation?

Best Regards....

[Former Member]

No worries,

If it is a newly installed system, we can say that it's a dummy.

So just make sure that you can shutdown and startup the DB without any issues.

If any issue let us know.

If no issues....then proceed with post installation steps - Latest kernel update, update statistics, DB verification etc.

ORA1031 is a common error comes when the user doesn't have suf access privillages to execute and can easily resolved by giving suf access rights.

Regards,

Nick Loy

[Former Member]

Make sure you take a offline offline backup of your database first.

Did you copied the content to origlog* while DB was online or offline.

Run dbcheck from brtools or DB13.

Shutdown your DB and bring online, check the database alert_<SID>.log file under saptrace folder.

[Former Member]

When i run dbcheck via brtools i see the error below:

BR0280I BRCONNECT time stamp: 2010-12-08 18.02.32

BR0301E SQL error -1031 at location BrDbdiffRead-1, SQL statement:

'PREPARE stmt_5 STATEMENT FROM'

'SELECT OBJNAME FROM "SAPSR3".DBDIFF WHERE DBSYS IN ('ORACLE', ' ') AND OBJTYPE

= 'TABL' AND DIFFKIND IN ('02', '61', '99') ORDER BY OBJNAME'

ORA-01031: insufficient privileges

BR0806I End of BRCONNECT processing: ceesxilj.chk 2010-12-08 18.02.32

BR0280I BRCONNECT time stamp: 2010-12-08 18.02.32

BR0804I BRCONNECT terminated with errors

[Former Member]

Whats your DB version and OS?

if you search for ORA-01031: insufficient privileges on marketplace you get notes. The issue is woth sapconn role.

[Former Member]

But I wonder this issue is related with a db problem. Why does this kind of error exist newly installed system?

Maybe i have to reinstall whole system again. I need helpfull recommendation.

This is an ERP 6.0 EHP4 system run on oracle 10.2.0.4 and windows 2008 Enterprise x64 SP2

Best Regards...

[Former Member]

No, after installation you have not performed the post installation task as per installation guide.

hecne you are getting these errors.

[Former Member]

Hi Hande,

'SELECT OBJNAME FROM "SAPSR3".DBDIFF WHERE DBSYS IN ('ORACLE', ' ') AND OBJTYPE
= 'TABL' AND DIFFKIND IN ('02', '61', '99') ORDER BY OBJNAME'

Execute, sapdba_role.sql and sapconn_role.sql on your db host. Then, try to execute the command on SQL*Plus, manually. You don't need to reinstall the system again.

Best regards,

Orkun Gedik

[Former Member]

Hi

I've run sapconn_role.sql succesfully and there is no error in log file. But when i run sapdba_role.sql i see error in the log file of this script:

rant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

grant select on sys.object_usage$ to sapdba

*

ERROR at line 1:

ORA-00942: table or view does not exist

Grant succeeded.

Grant succeeded.

[Former Member]

I think should be Ok, can you now run the brtools?

[Former Member]

So, you should be able to run, now.

[Former Member]

Hi

Do you say it is not important to get error below. Is it true?

grant select on sys.object_usage$ to sapdba

*

ERROR at line 1:

ORA-00942: table or view does not exist

[Former Member]

Is it really sys.object_usage$ with an $ at the end?

Afaik it should be sys.object_usage or v$object_usage.

What is your Oracle version?

(I see, already answered: 10.2.0.4)

But anyway, I think you may ignore this error at this stage.

It is not related to your current ORA-01031 problems.

Edited by: Joe Bo. on Dec 9, 2010 10:50 AM

[Former Member]

No, it is about problem in DB13

[Former Member]

Hi

I've stopped r3 and oracle and started oracle via sqlplus without error but i see errors in log files:

alert_uep.log:

Thu Dec 09 11:55:15 2010

Errors in file f:\oracle\uep\saptrace\background\uep_lgwr_3880.trc:

ORA-00313: open failed for members of log group 1 of thread 1

Thu Dec 09 11:55:15 2010

Errors in file f:\oracle\uep\saptrace\background\uep_lgwr_3880.trc:

ORA-00313: open failed for members of log group 2 of thread 1

Thu Dec 09 11:55:15 2010

Errors in file f:\oracle\uep\saptrace\background\uep_lgwr_3880.trc:

ORA-00313: open failed for members of log group 3 of thread 1

Thu Dec 09 11:55:15 2010

Errors in file f:\oracle\uep\saptrace\background\uep_lgwr_3880.trc:

ORA-00313: open failed for members of log group 4 of thread 1

Thu Dec 09 11:55:15 2010

Errors in file f:\oracle\uep\saptrace\background\uep_lgwr_3880.trc:

ORA-00313: open failed for members of log group 2 of thread 1

uep_lgwr_3880.trc:

• SERVICE NAME:() 2010-12-09 11:55:15.745

• SESSION ID:(75.1) 2010-12-09 11:55:15.745

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00313: open failed for members of log group 4 of thread 1

ORA-00313: open failed for members of log group 2 of thread 1

Maximum redo generation record size = 197120 bytes

Maximum redo generation change vector size = 190204 bytes

[Former Member]

see Note 491160 - Restore scenarios for lost files of oracle databases

[Former Member]

It seems there was an error when you copied mirrlog to origlog.

Make sure user ora<sid> has read/write access to the copied files.

And file names correct?

[Former Member]

Ok, it seems that you are not able to read Oracle log files. So, at this stage, you should re-create logfile groups by using statement, below;

alter database add logfile group <#>

('<drive>\ORACLE\<SID>\ORIGLOGA\LOG_G11M1.DBF',

'<drive>\ORACLE\<SID>\MIRRLOGA\LOG_G11M2.DBF') size 100M;

I emphasize that this is an sample statement and you should re-arrange this statements regarding your requirements and create 8 or 16 groups.

Then the problem will be solved.

Best regards,

Orkun Gedik

[Former Member]

Hi

I've stopped oracle and copied logfiles from mirrlog folders to origlog folders again according to oss note 491160 but i still get same errors in oracle alert and log files. This is windows platform so there is no orasid user.

Best Regards...

[Former Member]

This is windows platform so there is no orasid user.

I was assuming Unix, sorry.

In case of Windows it will be about user sidadm. You could look at access permissions first.

If that doesn't help, I think you should consider Orkun's suggestion.

regards

[Former Member]

Hi

I've copied log files of mirrlog folders to origlog folders and rename than while oracle is shutdown. After that i switch logs several time. Now i don't get errors in alert log file and other log file after i opened oracle db.

All other checks i done step by step are:

- I run Check and Uptimizer statistics at DB13. It finished succesfully.

- I run checkdb at DB13 and ther are only missing statistics and database parameter warning.

- I run Validate structure at BRTOOLS because i could not run it at DB13. I get some errors like this:

BR0301E SQL error -14508 at location stats_tab_validate-2, SQL statement:

'ANALYZE TABLE "SYSTEM"."LOGMNR_TAB$" VALIDATE STRUCTURE CASCADE ONLINE'

ORA-14508: specified VALIDATE INTO table not found

BR0893E Validating structure failed for table/cluster SYSTEM.LOGMNR_TAB$

BR0280I BRCONNECT time stamp: 2010-12-09 17.16.47

- I run VerifyDB at BRTOOLS because i could not run it at DB13. It finished succesfully.

Have you got any recommendation? Should i do any other checks for example mentioned at oss note 23345 ?

Best Regards...

[Former Member]

>>This is an ERP 6.0 EHP4 system run on oracle 10.2.0.4 and windows 2008 Enterprise x64 SP2

Any specific reason installing 10.2.0.4? refer Note 1339724 - Oracle 10.2 Extended Support free until July 31, 2011

you need to follow Note 830576 - Parameter recommendations for Oracle 10g

[Former Member]

Hi Sunil Bujade

Thank you but this information is not related about my db verification problem. Isn't it? I think you give general recommendation and oracle release and parameters are not related with verification issue.

Best Regards...

Edited by: Hande Kilcer on Dec 12, 2010 10:24 PM