AMP Failure!!!

[Guest_imported]

Hello All,

How do one bring a Down AMP back to online status. I've a Teradata demo installed on my comp, but when i tried to give a select statement against a table i got a message saying "One or more tables cannot be selected when the AMP is Down". What does that mean? How do i rectify? Someone can tell me how to get over this!!!

I checked the configuration from the database window and I found that there are two AMPs with AMP 0 showing offline.

Any help will be highly appreciated.

Thanks

TDNewbie

 

[tdatgod]

Hi,
it measn that the AMP has Crash 3 times within 30 minutes so we just hold that AMP from doing any work until it can be repaired to avoid a crash loop.

Can you go into the event log, look under Application log, and post the error message about why it crashed. This probably happened days in the past so you might have to seach for it.

I realize this is the DEMO code, but it is a exact version of teradata and if you crashed it the production version will crash as well.

As to fix that depdend if was an SQL bug or a corrupt file. Once you post the error I will tell you if it can be fixed.

start a Teradata command prompt and execute vprocmanager

vprocmanager
set restart = coldwait
set 0 online
quit

tpareset "to bring amp 0 online"



As for why your selects don't work. Teradata works by distributing the data into UNITS of Work called AMPS. with 2 amps, each will contain 1/2 of the data. If one of them is unaccessible we can't complete the query because we are missing half of the data. rather than returing an incorrect answer we tell you one of your amps are offline.


Now Teradata has a Software redundancy feature called

FallBack

create Table blort, Fallback .....

and what this does is tell Teradata this is a really important table and I want to be able access even if one of the amps is down.

This is like software Mirroring and is configurable on a table by table basis, We store a copy of the data amp 0 is reesonsible for on another AMP in the system. On a real production environment, that copy would reside on another NODE in the configuration ( unless you had a single node ( like the demo) in which case there is no choice.

 

[Guest_imported]

tdatgod,

Thanks for your response. I checked up the Application Log under EventLog and couldn't find any error message related to this problem. I also checked log file tables in DBC and it is empty. Why do you say AMP has crashed 3 times within 30 minutes? I also did that VProcManager commands but of no use. One more thing I would like to know is, if that AMP0 becomes online will I be able to access my tables(1/2 of the data not accessible when AMP 0 was down), which were created before this AMP problem.

Thanks

 

[tdatgod]

Hi,
running VprocManager is the only way to get AMp 0 back online. If Vprocmanager can't get Amp 0 back online your data is lost.


vprocmanager

status -- will display online/offline status of vprocs
set 0 online -- will set AMP 0 online.
set restart = coldwait
wait until AMP is recovered before starting.

quit

Then run

tpareset "to bring the amp back online"



Question? Is it possible someone deleted the AMP0-Pdisk0 file while trying to find some diskspace? This is a File in

program files\ncr\tdat\tdconfig

There should be Pdisks for Amp0 and Amp 1. If someone deleted the file the data is gone.

 

[Guest_imported]

Tdatgod,

Thanks very much for your response. I will check whether that file
which you have mentioned is there or not in the specified directory.

Suppose we go by your assumption that the file PDisk-Amp0 has been

deleted or some bad sector in that part of the HardDisk, that does mean

there is no way of recovering the tables without any data loss. I would

like to know whether the system tables under DBC will be intact or that

will also be lost. What will happen to the databases and users created?

I don't have that many databases and users created in my demo machine

but just want to know what will happen to those. Is there anyway by

which I can bring that AMP online even if that means losing the datas

in tables that I have created. How do I do that? I would like to bring

the system up without installing the Teradata demo again.

If system tables, databases and users won't be lost where does
Teradata store those details. Is the system tables all are made
FALLBACK? What about database and users created?

Thanks

TDNewbie

 

[Guest_imported]

tdatgod,

yeah that Amp0-Pdisk0 file was missing.so my friend asked me to do these commands to bring the AMP 0 up.

BOOT 0 //told this will initialize the RDBMS partitions of first AMP

once i did that i said "Successfully Invoked Startup Task" and also said more information is available in System Console.But i don't know how to start the System Console so i moved on the next step.

then i did a all tables rebuild on first amp using the following command

START REBUILD

REBUILD AMP 0 ALL TABLES ALL DATA

when the task is completed i restarted my PC and found to my surprise that AMP 0 shows online
in VPROCMANAGER. i'm able to see the table scripts but not able to do a select statement against

the tables. it gives error message no 2545. the problem now is it is not even allowing me to drop the tables i've created. it says "Table rebuilt without fallback". tell me how do i drop the tables. or tell me whether i've done something wrong. what is the way forward now without going for a reinstallation.

thanks

TDNewbie

 

[Guest_imported]

tdatgod,

yeah that Amp0-Pdisk0 file was missing.so my friend asked me to do these commands to bring the AMP 0 up.

BOOT 0 //told this will initialize the RDBMS partitions of first AMP

once i did that i said "Successfully Invoked Startup Task" and also said more information is available in System Console.But i don't know how to start the System Console so i moved on the next step.

then i did a all tables rebuild on first amp using the following command

START REBUILD

REBUILD AMP 0 ALL TABLES ALL DATA

when the task is completed i restarted my PC and found to my surprise that AMP 0 shows online
in VPROCMANAGER. i'm able to see the table scripts but not able to do a select statement against

the tables. it gives error message no 2545. the problem now is it is not even allowing me to drop the tables i've created. it says "Table rebuilt without fallback". tell me how do i drop the tables. or tell me whether i've done something wrong. what is the way forward now without going for a reinstallation.

thanks

TDNewbie

 

[mgaw]

A 2545 error says that the table is rebuilt and is non-fallback.

In a fallback table when a table row is written to an amp a copy of that row is also written to another amp in the system. In the event of an amp failure that copy (fallback) is used to rebuild the table on a failed amp.

If the table is non-fallback the table rebuild utility will rebuild the table header on the failed amp but will not have the fallback rows to populate the table.

The table must now be restored from the original source data or any archive (backup) that may have been taken.

 

[Guest_imported]

hello mgaw,


Thanks very much for your reply. I don't have a backup of those tables data and that is not of much importance. The problem now for me is to get rid of those non fallback tables after running that Table Rebuild Utility. I'm able to drop a non fallback tables which doesn't have any FOREIGN KEY constraint relationships defined. Can you tell me any method by which I can drop those tables with relationships which is giving me Error No 2545 by doing that. I checked up V2R4 documentation and it is mentioned

Remedy: Either resubmit the request after the table
has been restored, drop the table, or rebuild the table
without the LOCK NO FALLBACK TABLES option.

I even tried that LOCK NO FALLBACK TABLES option as well but this command is not working.



Thanks

TDNewbie

 

[tdatgod]

Hi,
That is an interesting question? Will it allow you to do an alter table to drop the foreign key? If Not can you post an example of the cross over and I will open a problem report about this at work because this shouldn't happen.

----

 

[Guest_imported]

tdatgod,

It is interesting as well as strange. It is not even allowing to drop the foreign key constraint.
One more thing that is very interesting is if i have a fallback table with a child relationship

to another non-fallback table and when i try to insert a record into the newly rebuilt fallback

table it works provided there is no referential integrity violation even though non-fallback

table doesn't have any data after the rebuild process. If i give values which existed in

non-fallback table prior to rebuild it works perfectly.

create table db1.employee
(
emp_id integer,
emp_name varchar(20),
dept_id integer,
constraint f_dept_id foreign key (dept_id) references db1.dept(dept_id)
)
unique primary index p_emp_id(emp_id);


create table db1.dept
(
dept_id integer,
dept_desc varchar(20)
)
unique primary index p_dept_id(dept_id);


after the rebuild process if i try to drop employee which is not a fallback protected table it

gives me

BTEQ -- Enter your DBC/SQL request or BTEQ command:
drop table employee;
drop table employee;
*** Failure 2545 Operation not allowed: db1.dept was Rebuilt without fallback.
Statement# 1, Info =0
*** Total elapsed time was 1 second.

If i try to drop dept table

BTEQ -- Enter your DBC/SQL request or BTEQ command:
drop table dept;
drop table dept;
*** Failure 5313 Referential integrity violation: cannot drop a referenced
(Parent) table.
Statement# 1, Info =0
*** Total elapsed time was 1 second.


BTEQ -- Enter your DBC/SQL request or BTEQ command:
alter table employee drop constraint f_dept_id;
alter table employee drop constraint f_dept_id;
*** Failure 2545 Operation not allowed: db1.employee was Rebuilt without fa
llback.
Statement# 1, Info =0
*** Total elapsed time was 1 second.


This problem really frustrating me. If you can find some solution I will be grateful.



Thanks

TDNewbie