database restore

Database not starting after disk hardware fault. The following error occurs.

SQL> startup ;

ORACLE instance started.

Total System Global Area 266485920 bytes

Fixed Size 73888 bytes

Variable Size 71385088 bytes

Database Buffers 194846720 bytes

Redo Buffers 180224 bytes

Database mounted.

ORA1113: file 4 needs media recovery

ORA1110: data file 4: ‘/u01/app/oracle/oradata/gendm/temp01.dbf’

The archive logs are not available, so we would have to restore from the last good backup taken on 21/03/04. What would be the impact on the other gensys databases and the rest of the gensys application if the Data Mart instance was recovered from the backup of 21/03 ?

Currently the ODS database is collating and holding the data as the datamart database is down.

Thanks

I think the datanart and other DBs will be OK providing it’s the datamart that has only been affected. You will have lost any data between the 23/3/04 backup that isn’t contained in the ODS(s).

As soon as you start the ETL, the chunks will be copied across as nornal, though it will take a while (possibly tens of hours) for ETL to catch up fully.

I have restored the database back to Sunday and restarted the ETLService process. The java processes are clocking up CPU time but the data is not copying from the ODS database tot he DataMart database.

Any ideas?

Patience is a virtue with ETL.

What does the ETL log say? If you want, send it to me and I’ll have a look at it (zip it first please).

OK,

FIRST OF ALL, DO NOT PANIC.

Now that I got that out, let’s focus on Oracle first.

Chances are you can recover your Oracle DB up to pretty much the point where it crashed, so, maybe there is no reason to worry at all.

First of all, do you have an Oracle DB person there, if so, check with him if he has REDO and ARCHIVE logs available. If so, how recent.

If you have recovered DB and already running it, please tell me the following:

how often do you run ETL purge?

do you still have ODS data available

does your ETL show that it is connected to ODS?

in your ETL log, what does it say?

Thanks Vic

So far we have restored the DM database back to 21st March 2004. The database restored ok and restarted fine. No archive logs were available… we have a lot of recommendations to make after this!

There is data in the ODS database which is already marked as transferred from Monday 22nd and no data for Tuesday as the system was unavailable – I am happy to accept that we may loose this data.

The next available data in ODS is from Wednesday. Once the DM database is started and the ETService process is running I assumed that the data should start copying from ODS to DM. We left this over night and still cannot see the data in DM (via crystal reports).

The ETL.log file is reporting the following:

04/03/30 09:57:55.884 GMT+01:00 4325376 ChunkWriter#1_for#1@9384278 WARNING Report View Access Object Pool: invalid : ReportViewAccessObject[connection: oracle.jdbc.driver.OracleConnection@1dc965f, numberOfViewAccessed: 1, listOfViewAccessed: {1, }] was returned. Removing it

04/03/30 09:57:56.103 GMT+01:00 43253760 ChunkWriter#1_for#15@13525044 EXCEPT Exception happend: [ExcptMsg: ORA0001: unique constraint (SA.SRC_TO_LOGFK) violated ]

Add info:

SQLState: 23000

Message:  ORA0001: unique constraint (SA.SRC_TO_LOGFK) violated



Vendor:   1

Stack Trace: 

java.sql.SQLException: ORA0001: unique constraint (SA.SRC_TO_LOGFK) violated

From searching Genesys tickets it appears this may be to do with trying to insert duplicate rows from ODS to Datamart but I am unsure how to overcome this. Your help is appreciated…

Louisa,

I suggest not to use reporting tool (Crystal Report as you said in your case or Brio) to find out if data are in DM (data can be already in your DB but not shown in report due to other reasons). Use ETLAssistant to find out where the problem is

data not transferred/data not aggregated.

Then you can stop your ETL Service and run manually needed processes I did so a few times and usually it helped.

Poor girl,

you have a problem with primary key sequence.

This is why your ETL is not writing anything.

SRC_TO_LOGFK

If you look into your DM sql script, you will see that:

create index SRC_TO_LOGFK on CHUNK_LOG (SOURCE_ID asc)

and that CHUNK_LOG actually has the following PK:

constraint PK_CHUNK_LOG primary key (CHUNK_ID)

since you have rebuild your DM and then imported the data, I assume you have not updated anything, right?

What are the current values for all of your sequences?

Have your DB adjust the sequence numbers to the lates value in each table+1. That should fix your problem.

Thanks.

The sequence numbers you are suggesting are changed are these for the CHUNK_LOG table in the ODS or DataMart database?

Also, do you know if this will play catch up if the sequence numbers are changed as suggested and transfer the data from the last few days.

First of all, BACKUP

Make sure that you have a full backup. (ARCHIVE+REDO) should be fine. just, PLEASE make sure that Oracle DB engineer does it.

I guess you can always do EXPORT…

You see, looking through the part of ETL log that you have provided, I have noticed that ETL fails on writing into DB.

And it is failing so on writing the log part. The error by itself says that primary key is not unique. It is really hard for me to fully assess the situation by looking only at that part of the log.

How did you recover your DB? did you rebuild DB by creating a new database, ran Datamart script and then imported the data? Or did you actually use a backup tool?

Also, change ETL log trace level so that we can actually see SQL statement issued by it. This way you can see EXACTLY where the error occurs as well as the values.

Did you ask Genesys support for help?