HELP!! newbee - lost BI files .

[nabhamani]

Hi! All,

Our progress DBA quit and I have to resolve a situation. Our backups are failing and reporting that BI files are missing. I checked and there is only 1 BI file out of 6. It seems 5 were deleted. Can any one please help me get out of this situation. I am also looking for a good book to understand progress database, its architecture and commands I am certified DBA in Oracle, DB2UDB, MS SQL Server and Sybase so I know the terminologies and may not need book for beginners but if I do please let me know.

Appreciate your help
NB

 

[Casper]

Well you definitely need to give us more information.
First of all always state your Progress verzion and OS
Second if you get errors, tell us the exact error message (including number).

now for your problem:

Do you get the error during the back up or while restorng the backup?
Is the database still up and running or is the database crashed?
Can you move back to last back-up?
Did you enable AI?

Casper.

 

[nabhamani]

Hi Casper,

I appreciate your response, yes it makes sense to know about the information you asked. My responses are below:

Q) First of all always state your Progress verzion and OS
A) PROGRESS Version 9.1C on SOLARIS. (4234)
SunOS 5.6 Generic_105181-33 sun4u sparc SUNW,Ultra-Enterprise

Q) Second if you get errors, tell us the exact error message (including number).
A) ** Cannot find or open file /bi6/syman/syman.b2, errno = 2. (43)^M
** Cannot find or open file /bi6/syman/syman.b2, errno = 0. (43)^M


now for your problem:

Q) Do you get the error during the back up or while restorng the backup?
A) I get error while doing backups (above error). then I verified the st files for the location of BI files and found there is only one out of 6.

Q) Is the database still up and running or is the database crashed?
A) Database is up and running, I have asked users to do read and small write and it went successful.


Q) Can you move back to last back-up?
Yes I can but I am not seeing the backup files looks like someone tried to do cleanup and those are gone too.

Q) Did you enable AI?
A) I am not sure how can I verify can you please help

thanks
NB

 

[tamhas]

Is this a Progress backup or an operating system backup like tar or cpio?

If the latter, it is possible that your script is out of sync with the database structure. If those 5 bi files were actually a part of the working database and were missing, then the database wouldn't start.

 

[TomBascom]

Are you doing an online backup of a database that has been up and running since your predecessor quit?

That would explain a lot of what you are saying.

It is possible that your .st file is wrong. Try:

prostrct list syman

That will show you where the .b* files *should* be and it will create a valid .st file as a side effect. (Make a copy of the old .st file before running this command -- just in case you want it later.)

 

[nabhamani]

Thankyou for your response Tamhas and Tom, I am putting response to your questions together here

Q)Is this a Progress backup or an operating system backup like tar or cpio?
A) It is progress backup (that what I understood from the backup script, it is running probkup command)

Q) If the latter, it is possible that your script is out of sync with the database structure. If those 5 bi files were actually a part of the working database and were missing, then the database wouldn't start.
A) the database has not brought down in a while now, it is up and running for a while, Wednesday we got error from backup output that it failed and since then we have asked users not to make any changes in the database. I think if we shut it down now it won't come up but is there a way to regenerate new empty bi files ?


Q) Are you doing an online backup of a database that has been up and running since your predecessor quit?
A) Yes the backup script runs daily, I am assuming it is online backup cause it does not shut the database down if it did I would see the database down since the script runs every night.



That would explain a lot of what you are saying.

It is possible that your .st file is wrong. Try:

Q) prostrct list syman
A) prostrct list syman
Area Name: Control Area, Type 6, Block Size 4096, Extents 1, Records/Block 32
Ext # 1, Type VARIABLE, Size 32 KByte, Name: /u15/eesv3/database/syman.db

Area Name: Primary Recovery Area, Type 3, Block Size 8192, Extents 6
Ext # 1, Type FIXED , Size 150016 KByte, Name: /bi6/syman/syman.b1
Ext # 2, Type FIXED , Size 150016 KByte, Name: /bi6/syman/syman.b2
Ext # 3, Type FIXED , Size 150016 KByte, Name: /bi6/syman/syman.b3
Ext # 4, Type FIXED , Size 150016 KByte, Name: /bi6/syman/syman.b4
Ext # 5, Type FIXED , Size 150016 KByte, Name: /bi6/syman/syman.b5
Ext # 6, Type VARIABLE, Size 0 KByte, Name: /bi6/syman/syman.b6

Area Name: Schema Area, Type 6, Block Size 4096, Extents 16, Records/Block 32
Ext # 1, Type FIXED , Size 150016 KByte, Name: /db5/syman/syman.d1
Ext # 2, Type FIXED , Size 150016 KByte, Name: /db5/syman/syman.d2
Ext # 3, Type FIXED , Size 150016 KByte, Name: /db5/syman/syman.d3
Ext # 4, Type FIXED , Size 150016 KByte, Name: /db5/syman/syman.d4
Ext # 5, Type FIXED , Size 150016 KByte, Name: /db5/syman/syman.d5
Ext # 6, Type FIXED , Size 150016 KByte, Name: /db5/syman/syman.d6
Ext # 7, Type FIXED , Size 150016 KByte, Name: /db5/syman/syman.d7
Ext # 8, Type FIXED , Size 150016 KByte, Name: /db5/syman/syman.d8
Ext # 9, Type FIXED , Size 150016 KByte, Name: /db5/syman/syman.d9
Ext # 10, Type FIXED , Size 150016 KByte, Name: /db5/syman/syman.d10
Ext # 11, Type FIXED , Size 150016 KByte, Name: /db5/syman/syman.d11
Ext # 12, Type FIXED , Size 150016 KByte, Name: /db5/syman/syman.d12
Ext # 13, Type FIXED , Size 150016 KByte, Name: /db5/syman/syman.d13
Ext # 14, Type FIXED , Size 150016 KByte, Name: /db5/syman/syman.d14
Ext # 15, Type FIXED , Size 150016 KByte, Name: /db5/syman/syman.d15
Ext # 16, Type VARIABLE, Size 1170496 KByte, Name: /db5/syman/syman.d16

QUCIK question
If I find those bi files in the file system backup can I restore those 5 bi files to the above (appropriate) location then shutdown the database and restart, will the database pick it up? I want to see what all my options are from here on.

I appreciate your help


Thanks
NB

 

[tamhas]

Tom is the authority in this area, so I will let him give the definitive answer, but I would suggest getting very cautious at this point. *VERY* cautious. From what you seem to have presented, the scenario suggests that the database was up and running and then someone removed the missing BI files using an operating system command. The server, being a running process, still has a handle on the files which you can't see in the operating system, but your progress backup has no way of getting those handles because it is a new process.

Depending on what you have in terms of OS backups, you might want to look to see if a prior OS backup had those BI files and compare what time that was to the last server start in the .lg file. If the files were missing at the time of the last server start and the files were a part of the database, then the server would not have started.

Depending on your operating system, it *might* be possible to get the missing files reattached to the file system, but I would consider this chancy at best. Safest and easiest would be to simply go back to the last successful backup. You should see a time for that in the .lg file as well. If you had that and AI enabled, you would be in good shape, but without AI it could be painful.

Your other option here ... which could be painful depending on the size of your database ... is to use one of the existing sessions to dump all the data ... having gotten everyone to have stopped work first, of course. If you can successfully dump everything, then you could build a new database from that. I would strongly suggest getting yourself a consultant to walk you through the process, though, because there is no margin for error.

Whatever you do, don't stop the server until you figure out that you can live without the contents of the current database.

 

[TomBascom]

The indicated .b* files are, in fact, missing?

The database server is still running but there are no longer any users?

No new user sessions can start successfully?

This has been true for some time now? Hours? Days?

Given those assumptions...

1) Losing the before-image files is seriously bad. Your database integrity is no longer assured.

2) Recovering the bi files from backup is not going to help. You need the information in the current bi files not some old stale copy.

Ideally you would restore from backup and roll forward your after image logs. This is the sort of thing that after imaging is for. Unfortunately, as prostrct list shows, you do not have after imaging enabled.

Less ideally, if there is a live 4gl session somewhere that is still connected to these databases and which you control (that's a lot of "ifs") then you could, potentially, export the contents of the database and reload it. It looks like you have about 9GB of data and you're on fairly ancient hardware -- if there is just one session live that could take a couple of days. Probably not much of an option.

So you are reduced to bad choices:

a) Shutdown or crash the database. Replace the missing bi files using some combination of prostrct add, prostrct repair and prostrct unlock (the gory details vary a bit). Force your way into the db with the -F flag and hope for the best. You might get lucky. Then again, you might not.

b) Restore from backup. Accept that you have lost a days work (or however long it has been since the last good backup).

I guess I'd would take a gamble on option a -- if the db has been quiet for quite a while then most of the contents of the bi file should have been flushed to disk by now. It's a gamble and there is no way to be sure that it works. It might appear to work and then in two days you discover that all customers in California are missing, or 6 months from now some account will fail to balance. But, on the whole, it's probably your best bet.

In any event, when that's all done you really ought to enable after-imaging. And upgrade to a relatively current release.

 

[nabhamani]

thanks Tom and tomas,

I just found out that we are able to connect it via TCP but not direct.

Questions
1) what command do I need to do an export of the current data I need, looking at one of the latest backup files its little less than 2GB and I would prefer if I can export the data and save it first. This is one of the most critical database and the data is quite critical.

2) Replace the missing bi files using some combination of prostrct add, prostrct repair and prostrct unlock. Can you please let me know the steps or commands to do this?

thanks
NB

 

[tamhas]

For the dump, you will need to be able to get to the data dictionary. To do that you will have to be coming from a product where you can either get the dictionary directly, e.g., a Windows development license, or one that can access the Progress Editor where you will find it on the Tools menu.

There are menu options there for dumping the data, dictionary, users, and sequences ... all of which you will need to rebuild.

I would get help sooner rather than later because later may be too late.

 

[nabhamani]

Thanks Tomas,

I agree with you, is there a place where can I find consultants for progress . Though I am not the one to make decisions I can forward the info like rate etc to my manager and he can make decisions.

thanks
NB

 

[tamhas]

www.greenfieldtech.com is your best source for performance and database issues.

Other than that, I would post on the DBA list at www.peg.com for a broadcast appeal and other hand-holding.

 

[nabhamani]

I have two questions
1) Replace the missing bi files using some combination of prostrct add, prostrct repair and prostrct unlock. Can you please let me know the steps or commands to do this?

2) Is there a way to read the information from current one BI file that is out there and apply those changes if I have to restore from previous backup?

Please let me know
thanks
NB

 

[TomBascom]

1) I'm not in the habit of smashing database like that -- so it isn't an everyday procedure... And I don't have 9.1C installed anywhere right now. But see below...

2) No. It doesn't work that way. BI files that do not match the timestamps of the database that they belong to are not usable in that way. But see step #6 below. Those files aren't quite utterly useless.

Having said that the following experiment might be useful (some steps may differ -- I used 10.1B):

1) prodb sports2000 sports2000
2) proutil sports2000 -C truncate bi
3) prostrct remove sports2000 bi
4) create the following sports2000.st:
b . f 1024
b . f 1024
b .
5) prostrct add sports2000
6) cp sports2000.b2 sports2000.xxx

The copy of the fixed bi extent is helpful -- you could get this from a backup tape if need be or create a blank one with some utility like "dd" but we need a file that is the correct size...

7) rm sports2000.b[12]

You now have a database that is damaged in much the same way that I believe these posts have described.

Try starting Progress, making a backup etc. Nothing works...

8) cp sports2000.xxx sports2000.b1
9) cp sports2000.xxx sports2000.b2
10) cp sports2000.xxx sports2000.b3

Attempting to start Progress still fails.

11) prostrct unlock sports2000
12) proutil sports2000 -F -C truncate bi
13) proutil sports2000 -C truncate bi
14) pro sports2000

This should work -- your database is damaged and it should be dumped and loaded. But you should be able to get to what is left of your data now.

To cleanup the bi files:

14) prostrct remove sports2000 bi
15) prostrct remove sports2000 bi
16) prostrct remove sports2000 bi
17) prostrct add sports2000
18) pro sports2000

Remember - your database is damaged. The message is not kidding. This is not a routine recovery technique. Even if you "get away with it" this is a very dangerous thing to be doing.

 

[nabhamani]

Thanks Tom,

I will try your steps on our development database see how it goes.

I truly appreciate your help. I will post if I have any questions

Thanks again
NB

 

[nabhamani]

Tom,

I was able to restore the database backup to development database. Unfortunately one of the developers testing mentioned below
---------------------
when I tried to view any of the existing tables I got the following error:
“** Insufficient access privilege for table artran. (234)”

Error 234 is further explained as:
“A procedure cannot compile if you do not have at least read access for every table in the request. If a table is to be updated, deleted from, or created into, you must have those corresponding privileges.”.

We must have root access to the db. There is the possibility that every cron job and script will error out without it.

-------------------------------------
She said she is able to access as her own id but not as root. Is this something fixable?

Can you please let me know
thanks
NB

 

[Casper]

You probably have disallowed blank user ID access through the security in the data administartion.

If that is the case then you have to reset the proviliges again to allow for blank user ID.

I would test the following code on a copy of this database (don't want to mess up things again so first try on a copy ;-):
log in with a userID and run the following

FOR EACH _file EXCLUSIVE-LOCK:
    ASSIGN _file._can-create = '*'
           _file._can-read = '*'
           _file._can-write = '*'
           _file._can-delete = '*'.
END.

This should do the trick.

HTH,

Casper.

 

[nabhamani]

Hi! Casper,

I appreciate your response. I ran the command as root and as myself and both time I got this error

³** Insufficient access privilege for Field _Can-Create. (233) ³
³ ³** Could not understand line 2. (196)


I appreciate your response.
Thanks
NB

 

[rhi]

Hi! Casper,

I appreciate your response. I ran the command as root and as myself and both time I got this error

³** Insufficient access privilege for Field _Can-Create. (233) ³
³ ³** Could not understand line 2. (196)


I appreciate your response.
Thanks
NB

root or "myself" probably are not setup as administrators of the database. Possibly try sysprogress account, if you know the password.

 

[tamhas]

Also check the permissions on the database. If it is not owned by root, root will not have its usual magic.