Using Recovery Control Views
Several system views contain information about ARC utility events. You can use these views for recovery control.
|
This view… |
Provides information about … |
On table… |
|
DBC.AssociationV |
objects (databases, users, tables, views, macros, indexes, stored procedures) that you imported from another Teradata Database system or created via the ARC utility COPY statement |
DBC.DBCAssociation |
|
DBC.EventsVX |
archive or restore activities, with a row (audit trail) for each archive and recovery event. Each time another ARC activity begins, ARC inserts a new row in the underlying system table of the EventsV view. |
DBC.RCEvent |
|
DBC.Events_ConfigurationVX |
archive and recovery activities that did NOT affect all AMPs |
DBC.RCConfiguration |
|
DBC.Events_MediaVX |
archive and recovery activities that involved removable media |
DBC.RCMedia |
Finding Information about Imported Objects with the AssociationV View
The AssociationV view allows you to retrieve information about an object imported from another Teradata Database. An existing object created with the ARC COPY command also appears in the AssociationV view. If you later drop a copied object from its new destination, the information is deleted from the AssociationV view and is no longer available.
The following example uses the AssociationV view to list all tables, views, or macros that were copied into the payroll database. The result of the query displays imported and current table names. The object column displays the current name of each table. The Source column provides the name of the original table. The event column shows the number assigned to the restore operation.
SELECT TRIM (DatabaseName)||'.'||TableName
(NAMED Object, FORMAT 'X (25)')
TRIM (Original_DatabaseName)||'.'||
Original_TableName
(NAMED Source, FORMAT 'X(25)')
EventNum (NAMED Event, FORMAT '9(5)')
FROM DBC.AssociationV
WHERE DatabaseName LIKE %Payroll%'
ORDER BY Object ;
This query returns event rows similar to the following:
Object Source Event
---------------------------------------------------------------
Payroll_Prod.DEPARTMENT PAYROLL_TEST.department 00014
Payroll_Prod.DEPT PAYROLL_TEST.dept 00014
Payroll_Prod.EMP PAYROLL_TEST.emp 00014
Payroll_Prod.EMPLOYEE PAYROLL_TEST.employee 00014
Payroll_Prod.NEWHIRE PAYROLL_TEST.newhire 00014
Tracking ARC Activity with the EventsV View
The EventsV view tracks ARC activity. ARC inserts a new row in the EventsV system table each time another ARC activity begins. The EventsV view returns a row for each activity tracked.
The following table describes events the system creates depending on the type of object on which the activity was performed.
|
A row for this event type … |
Is created for each … |
|
Checkpoint Event Row |
journal checkpointed. |
|
Delete Event Row |
journal deleted. |
|
Dump Event Row |
database or table archived. |
|
Restore Event Row |
database or table restored. |
|
Rollback Event Row |
database or table rolled back. |
|
Rollforward Event Row |
database or table rolled forward. |
The SQL statement in the following example requests a list of all ARC activity that took place March 28th.
SELECT EventNum
,UserName (CHAR (12))
,EventType (CHAR (12))
,DatabaseName (CHAR (15))
FROM DBC.EventsV
WHERE CreateDate=990328
ORDER BY EventNum ;
EventNum
180
181
|
UserName
BRM
RPK
|
EventType
Dump
Restore
|
DatabaseName
Payroll_Test
Personnel_Test
|
Tracking ARC Activity on Specific AMPs with the Events_ConfigurationV View
The Events_ConfigurationV view contains rows for each archive activity that does not affect all AMPs in the database configuration. If the ARC command specifies all AMPs and there are one or more AMPs offline, a row is inserted in the system table for each offline AMPs. If the statement is for specific AMPs, a row is inserted for each specified and online AMPs.
The following example submits an SQL statement to find out which user did not release the utility locks on processor 2. Query results show three different users: AMT, ALK, and JLR.To determine who left the utility locks on processor 2, submit the following query.
SELECT CreateTime
,EventNum
,EventType (CHAR (12))
,UserName (CHAR (12))
,vproc
FROM DBC.Events_ConfigurationV
WHERE vproc= '2' ORDER BY 2 ;
CreateTime
|
EventNum
|
EventType
|
UserName
|
Vproc
|
14:06:22
16:06:39
18:12:09
|
1,153
1,159
1,164
|
Dump
Dump
Restore
|
AMT
ALK
JLR
|
2
2
2
|
Finding Information about Removable Storage Media with the Events_MediaV View
The Events_MediaV view provides information about ARC activities that used removable storage media such as volume serial numbers assigned to portable devices.
The following example requests the volume serial number of a restore tape. The query results show two restore operations, each with their own serial number and data set name.
SELECT EventNum
,EventType (CHAR (12))
,UserName (CHAR (12))
,VolSerialID
,DataSetName (CHAR (12))
FROM DBC.Events_MediaV
ORDER BY EventNum ;
EventNum
|
EventType
|
UserName
|
VolSerialID
|
DataSetName
|
79
180
|
Restore
Restore
|
PJ
PJ
|
MPC001
MPC002
|
LDR.DMP1.JNL
RAN.DMP2.JNL
|
Restoring a Missing DBC.Database View
If you receive the error message “object DBC.Database does not exist” when opening Teradata Administrator or Teradata Studio, your view DBC.Database is missing. You can restore this view by running the DIPALL or DIPVIEWSV script from the DIP utility.
Notice:
Before restoring DBC.Database, make sure you have backed up everything, including DBC.