Friday, November 12, 2010

Configuring Teradata STANDBY Database.

Home | Profile 



A STANDBY database is nothing but a clone of Production Database existing on a Test/QA server synced periodically with transaction logs/journals so that if a DR (disaster) were to hit the Production datacenter, with minimal/no data loss, application’s can be re-pointed to this standby database, thus reducing the impact on the business.

With version 13.x, seems Teradata is coming up with “Dual Active” systems similar to what Oracle’s Data Guard or Sybase’s Replication servers or otherwise MS SQL’s cluster services are, but on any version journal restores can be considered as a low cost solution of implementing a standby database for critical applications.

Although it can be setup for OLAP applications as well, it’s more useful for OLTP (Transactional) or OLTA (Transactional/Analytical) apps. The setup requires only few steps and is easy to configure. No additional tools or system changes are required. Just arcmain and couple of shell (Linux)/JCL (Mainframe) scripts are enough. Also we have to use "Permanent Journals" instead of Transient.

Summary of Steps:
  • Configure the Primary database.
  • Configure the Standby Database.
  • Setup client scripts for shipping journals/transaction logs.     

Break it down further, steps involved are pretty straight forward - a onetime Archive/Restore and then an ongoing process involving Checkpoint and Journal Archive/Restore, RollForward  to standby.


Step 1: Configure the Primary Database

Enable JOURNALING on the Primary Database

Create the database with "AFTER JOURNAL" and provide "JOURNAL TABLE" so that we can capture the transaction logs and journals.

CREATE DATABASE RBDataGuard FROM RAJ
AS PERM=1073741824
FALLBACK
NO BEFORE JOURNAL
AFTER JOURNAL
DEFAULT JOURNAL TABLE=RBDataGuard_JOURNAL;

"FALLBACK" is for added protection and decision to enable it depends on the storage capacity of the system and size of the tables. As you know with fallback data storage is doubled.

If you plan to use an existing database you will need to modify the database to enable "AFTER JOURNAL" and as well the tables need to be altered to enable journaling.

Setup few tables to capture/create transactions

For my testing I’m creating 2 transaction/data tables, although in a real time scenario you don’t need this.

CREATE SET TABLE RBDataTabA ,FALLBACK ,
     NO BEFORE JOURNAL,
     DUAL AFTER JOURNAL,
     WITH JOURNAL TABLE = RBDataGuard_JOURNAL ,
     MINIMUM DATABLOCKSIZE  , FREESPACE = 20 PERCENT, CHECKSUM = DEFAULT
     (
      ColTXN INTEGER,
      LastUpdatedTStamp TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
      LastUpdatedUser CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT USER )
PRIMARY INDEX ( ColTXN  );

CREATE SET TABLE RBDataTabB ,FALLBACK ,
     NO BEFORE JOURNAL,
     DUAL AFTER JOURNAL,
     WITH JOURNAL TABLE = RBDataGuard_JOURNAL ,
     MINIMUM DATABLOCKSIZE  , FREESPACE = 20 PERCENT, CHECKSUM = DEFAULT
     (
      ColTXN INTEGER,
      LastUpdatedTStamp TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
      LastUpdatedUser CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT USER )
PRIMARY INDEX ( ColTXN  );

CREATE SET TABLE RBDual,FALLBACK ,
     NO BEFORE JOURNAL,
     DUAL AFTER JOURNAL,
     WITH JOURNAL TABLE = RBDataGuard_JOURNAL ,
     MINIMUM DATABLOCKSIZE  , FREESPACE = 20 PERCENT, CHECKSUM = DEFAULT
     (
      SCN DECIMAL(38,0),
      LastUpdatedDate DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE ,
      LastUpdatedTStamp TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
      LastUpdatedUser CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT USER )
UNIQUE PRIMARY INDEX ( SCN ,LastUpdatedDate );

Also initialize table RBDual with one row.

INSERT INTO RBDual ( SCN, LastUpdatedUser ) VALUES ( 1, 'Raj' );                 
  

Step 2: Configure the Standby Database.

Create the standby database using the same SQL used to setup Primary

CREATE DATABASE RBDataGuard FROM RAJ
AS PERM=1073741824
FALLBACK
NO BEFORE JOURNAL
AFTER JOURNAL
DEFAULT JOURNAL TABLE=RBDataGuard_JOURNAL;

Step 3: Setup client scripts for transaction logs archive/recovery.

Run a full archive/restore (onetime)

Make sure to create the tables on the STANDBY database through Archive/Restore process. Journal restores may not work if you manually create them using SQL Assistant or BTEQ.  See the scripts used below, most of it is self explanatory.

bck_db.sql
-------------
.logon prodsrv/raj,password;
ARCHIVE DATA TABLES
   (RBDataGuard)
  ,RELEASE LOCK
  ,FILE = DBDump;
LOGOFF;
QUIT;

res_db.sql
------------
.logon stdbysrv/raj,passwd;
COPY DATA TABLES    (  RBDataGuard)
,RELEASE LOCK
,FILE=DBDump;
LOGOFF;
QUIT;

run_db.sh
------------
#!/bin/sh
#Clean up previous logs
rm *.rlg *.log
#Archive/Restore of Prod to Standby
arcmain  sessions=12 VB3 perffile=arcDB.log < bck_db.sql
arcmain  sessions=12 VB3 perffile=resDB.log < res_db.sql
#End

Once you create above scripts, execute run_db.sh, which will take a full archive of Prod DB and restore it to STANDBY database.

$ sh run_db.sh

11/10/2010 15:47:16   Copyright 1989-2007, NCR Corporation.
11/10/2010 15:47:16   All Rights Reserved.
11/10/2010 15:47:16
11/10/2010 15:47:16     ***   ****    ****
11/10/2010 15:47:16    *   *  *   *  *        PROGRAM: ARCMAIN
11/10/2010 15:47:16    *****  ****   *        RELEASE: 12.00.00.10
11/10/2010 15:47:16    *   *  *  *   *        BUILD:   070177eLX (Oct  1 2009)
11/10/2010 15:47:16    *   *  *   *   ****
11/10/2010 15:47:16
11/10/2010 15:47:16 ---  Opening Restart Log: ARCLOG101110_154716_25008.rlg
11/10/2010 15:47:16 ---
11/10/2010 15:47:16  RESTARTLOG = ARCLOG101110_154716_25008.rlg
11/10/2010 15:47:16
11/10/2010 15:47:16  PARAMETERS IN USE:
11/10/2010 15:47:16
11/10/2010 15:47:16  SESSIONS 12
11/10/2010 15:47:16  VERBOSE LEVEL 3
11/10/2010 15:47:16  PERFFILE = arcDB.log
11/10/2010 15:47:16
11/10/2010 15:47:16
11/10/2010 15:47:16  CHARACTER SET IN USE: ASCII
11/10/2010 15:47:16  .LOGON PRODSRV/raj,;
11/10/2010 15:47:16 ---  Connecting SQL and CONTROL sessions.
11/10/2010 15:47:17 ---  Logon Sequence Number = 4496468 (0x00449c54)
11/10/2010 15:47:17 ---  Maximum Parcel Size supported: 64K bytes
11/10/2010 15:47:17 ---  Teradata V12 detected
11/10/2010 15:47:17  LOGGED ON 3 SESSIONS
11/10/2010 15:47:17 ---  CLIv2 VERSION: 12.00.00.0
11/10/2010 15:47:18
11/10/2010 15:47:18  DBS LANGUAGE SUPPORT MODE Standard
11/10/2010 15:47:18  DBS RELEASE 12.00.03.04
11/10/2010 15:47:18  DBS VERSION 12.00.03.06d
11/10/2010 15:47:18
11/10/2010 15:47:18  STATEMENT COMPLETED
11/10/2010 15:47:18
11/10/2010 15:47:18
11/10/2010 15:47:18  ARCHIVE DATA TABLES
11/10/2010 15:47:18     (RBDataGuard)
11/10/2010 15:47:18    ,RELEASE LOCK
11/10/2010 15:47:18    ,FILE = DBDump;
11/10/2010 15:47:18 ---  Checking for ACTIVATE_CATALOG macro
11/10/2010 15:47:18 ---  ACTIVATE_CATALOG does not exist or select accessright
                    ---  not granted to this user
11/10/2010 15:47:18 ---  Checking for ACTIVATE_LOGSKIPPED macro
11/10/2010 15:47:18 ---  ACTIVATE_LOGSKIPPED does not exist or select
                    ---  accessright not granted to this user
11/10/2010 15:47:18 ---  Checking for ACTIVATE_ENCRYPTION macro
11/10/2010 15:47:18 ---  ACTIVATE_ENCRYPTION does not exist or select
                    ---  accessright not granted to this user
11/10/2010 15:47:18 ---  Sending Begin Transaction request
11/10/2010 15:47:18 ---  Sending Allocate Event Number request.
11/10/2010 15:47:18  UTILITY EVENT NUMBER  - 2344402
11/10/2010 15:47:18 ---  Sending End Transaction request
11/10/2010 15:47:18 ---  Connecting data sessions.
11/10/2010 15:47:58  LOGGED ON   12 SESSIONS
11/10/2010 15:47:58 ---  Opening file DBDUMP.
11/10/2010 15:47:58 ---  File open done.
11/10/2010 15:47:58 ---  Writing archive header record.
11/10/2010 15:47:58
11/10/2010 15:47:58  ARCHIVING DATABASE "RBDATAGUARD"
11/10/2010 15:47:58 ---  Begin dictionary phase
11/10/2010 15:47:58 ---  Requesting DBS to send dictionary rows.
11/10/2010 15:47:58 ---  Sending Begin Transaction request
11/10/2010 15:48:01 ---  Retrieving and processing configuration parcels
11/10/2010 15:48:01  DBS Configuration Information:
11/10/2010 15:48:01  Total AMPs: 572
11/10/2010 15:48:01  Down AMPs: 0
11/10/2010 15:48:01  Number of AMPs involved: 572
11/10/2010 15:48:01  System Format: 7
11/10/2010 15:48:01  Hash Function: 773
11/10/2010 15:48:01 ---  Writing database header record.
11/10/2010 15:48:01 ---  Writing DBS configuration records
11/10/2010 15:48:01 ---  Writing table descriptor records.
11/10/2010 15:48:01 ---  PARTITIONS WHERE clause not specified
11/10/2010 15:48:01 ---  Set ObjHasData Flag based on object type
11/10/2010 15:48:01 ---  Start writing dictionary rows
11/10/2010 15:48:01 ---  Writing dictionary trailer record.
11/10/2010 15:48:01 ---  Sending End Transaction request
11/10/2010 15:48:01 ---  End dictionary phase
11/10/2010 15:48:01 ---  Begin data phase
11/10/2010 15:48:01 ---  Sending Begin Transaction request
11/10/2010 15:48:02 ---  Retrieving and processing configuration parcels
11/10/2010 15:48:02 ---  Inserting event row in RCEVENT table.
11/10/2010 15:48:02 ---  Starting table "RBDataTabA"
11/10/2010 15:48:02 ---  Table "RBDataTabA" is not a spanned object
11/10/2010 15:48:02 ---  Starting AMP=478
11/10/2010 15:48:02 ---  Writing table header record
11/10/2010 15:48:02 ---  Writing DBS configuration records
11/10/2010 15:48:02 ---  Start receiving and writing data block record(s)
11/10/2010 15:48:03 ---  Data subtable:   402 bytes, 0 rows received
11/10/2010 15:48:03 ---  Writing table trailer record.
11/10/2010 15:48:03 ---  Table "RBDataTabA": Table Id=0x9CB100AF,Fallback=F
11/10/2010 15:48:03  TABLE "RBDataTabA" - 402 BYTES, 0 ROWS ARCHIVED
11/10/2010 15:48:03 ---  Starting table "RBDataTabB"
11/10/2010 15:48:03 ---  Table "RBDataTabB" is not a spanned object
11/10/2010 15:48:03 ---  Starting AMP=138
11/10/2010 15:48:03 ---  Writing table header record
11/10/2010 15:48:03 ---  Writing DBS configuration records
11/10/2010 15:48:03 ---  Start receiving and writing data block record(s)
11/10/2010 15:48:04 ---  Data subtable:   402 bytes, 0 rows received
11/10/2010 15:48:04 ---  Writing table trailer record.
11/10/2010 15:48:04 ---  Table "RBDataTabB": Table Id=0x9CB200AF,Fallback=F
11/10/2010 15:48:04  TABLE "RBDataTabB" - 402 BYTES, 0 ROWS ARCHIVED
11/10/2010 15:48:04 ---  Starting table "RBDual"
11/10/2010 15:48:04 ---  Table "RBDual" is not a spanned object
11/10/2010 15:48:04 ---  Starting AMP=400
11/10/2010 15:48:04 ---  Writing table header record
11/10/2010 15:48:04 ---  Writing DBS configuration records
11/10/2010 15:48:04 ---  Start receiving and writing data block record(s)
11/10/2010 15:48:05 ---  Data subtable:   489 bytes, 1 rows received
11/10/2010 15:48:05 ---  Writing table trailer record.
11/10/2010 15:48:05 ---  Table "RBDual": Table Id=0x9CB000AF,Fallback=F
11/10/2010 15:48:05  TABLE "RBDual" - 489 BYTES, 1 ROWS ARCHIVED
11/10/2010 15:48:05 ---  Updating event row.
11/10/2010 15:48:05 ---  Sending End Transaction request
11/10/2010 15:48:05 ---  End data phase
11/10/2010 15:48:05 ---  Writing database trailer record.
11/10/2010 15:48:05 ---  Sending release lock request to DBS.
11/10/2010 15:48:05  "RBDATAGUARD" - LOCK RELEASED
11/10/2010 15:48:05 ---  Writing archive trailer record.
11/10/2010 15:48:05 ---  Closing archive file(s)
11/10/2010 15:48:05  DUMP COMPLETED
11/10/2010 15:48:05  STATEMENT COMPLETED
11/10/2010 15:48:05
11/10/2010 15:48:05
11/10/2010 15:48:05  LOGOFF;
11/10/2010 15:48:05 ---  Disconnecting all sessions.
11/10/2010 15:48:08  LOGGED OFF  15 SESSIONS
11/10/2010 15:48:08  STATEMENT COMPLETED
11/10/2010 15:48:08
11/10/2010 15:48:08
11/10/2010 15:48:08  ARCMAIN TERMINATED WITH SEVERITY 0
11/10/2010 15:48:10   Copyright 1989-2007, NCR Corporation.
11/10/2010 15:48:10   All Rights Reserved.
11/10/2010 15:48:10
11/10/2010 15:48:10     ***   ****    ****
11/10/2010 15:48:10    *   *  *   *  *        PROGRAM: ARCMAIN
11/10/2010 15:48:10    *****  ****   *        RELEASE: 12.00.00.10
11/10/2010 15:48:10    *   *  *  *   *        BUILD:   070177eLX (Oct  1 2009)
11/10/2010 15:48:10    *   *  *   *   ****
11/10/2010 15:48:10
11/10/2010 15:48:10 ---  Opening Restart Log: ARCLOG101110_154810_25358.rlg
11/10/2010 15:48:10 ---
11/10/2010 15:48:10  RESTARTLOG = ARCLOG101110_154810_25358.rlg
11/10/2010 15:48:10
11/10/2010 15:48:10  PARAMETERS IN USE:
11/10/2010 15:48:10
11/10/2010 15:48:10  SESSIONS 12
11/10/2010 15:48:10  VERBOSE LEVEL 3
11/10/2010 15:48:10  PERFFILE = resDB.log
11/10/2010 15:48:10
11/10/2010 15:48:10
11/10/2010 15:48:10  CHARACTER SET IN USE: ASCII
11/10/2010 15:48:10  .LOGON STDBYSRV/raj,;
11/10/2010 15:48:10 ---  Connecting SQL and CONTROL sessions.
11/10/2010 15:48:11 ---  Logon Sequence Number = 519 (0x00000207)
11/10/2010 15:48:11 ---  Maximum Parcel Size supported: 64K bytes
11/10/2010 15:48:11 ---  Teradata V12 detected
11/10/2010 15:48:11  LOGGED ON 3 SESSIONS
11/10/2010 15:48:11 ---  CLIv2 VERSION: 12.00.00.0
11/10/2010 15:48:11
11/10/2010 15:48:11  DBS LANGUAGE SUPPORT MODE Standard
11/10/2010 15:48:11  DBS RELEASE 12.00.03.16
11/10/2010 15:48:11  DBS VERSION 12.00.03.18
11/10/2010 15:48:11
11/10/2010 15:48:11  STATEMENT COMPLETED
11/10/2010 15:48:11
11/10/2010 15:48:11
11/10/2010 15:48:11  COPY DATA TABLES    (  RBDataGuard)
11/10/2010 15:48:11  ,RELEASE LOCK
11/10/2010 15:48:11  ,FILE=DBDump;
11/10/2010 15:48:11 ---  RESTORE FALLBACK enabled due to COPY statement.
11/10/2010 15:48:11 ---  Sending Begin Transaction request
11/10/2010 15:48:11 ---  Sending Allocate Event Number request.
11/10/2010 15:48:11  UTILITY EVENT NUMBER  - 2430
11/10/2010 15:48:11 ---  Sending End Transaction request
11/10/2010 15:48:11 ---  Connecting data sessions.
11/10/2010 15:48:12  LOGGED ON   12 SESSIONS
11/10/2010 15:48:12 ---  Checking for multiple users
11/10/2010 15:48:12 ---  Opening ARCHIVE file
11/10/2010 15:48:12 ---  Reading Archive Header Record
11/10/2010 15:48:12 ---  ARC VERSION  14
11/10/2010 15:48:12 ---  ARCHIVED AT    11-10-10 15:47:58
11/10/2010 15:48:12 ---  ARCHIVE CHARACTER SET: ASCII
11/10/2010 15:48:12 ---  ARCHIVED FROM  ALL AMP DOMAINS
11/10/2010 15:48:12 ---  SYSTEM INTERNAL FORMAT 7
11/10/2010 15:48:12 ---  UTILITY EVENT NUMBER IN ARCHIVE  - 2344402
11/10/2010 15:48:12 ---  SOURCE RDBMS VERSION: LANGUAGE SUPPORT MODE:Standard;
                    ---  RELEASE:12.00.03.04; VERSION:12.00.03.06d;
11/10/2010 15:48:12 ---  SOURCE ROWFORMAT: 1
11/10/2010 15:48:12 ---  SOURCE DBSPLATFORM: 17
11/10/2010 15:48:12 ---  Archive Event Number = 2344402
11/10/2010 15:48:12 ---  Archive System Format = 7
11/10/2010 15:48:12 ---  Archive Locale = 0
11/10/2010 15:48:12 ---  Archive Version Number = 14
11/10/2010 15:48:12 ---  Database header record: "RBDATAGUARD"
11/10/2010 15:48:12 ---  Begin dictionary phase
11/10/2010 15:48:12 ---  Build Excluded Tables List for ALL PARTITIONS
11/10/2010 15:48:12 ---  Check if excluded tables exist
11/10/2010 15:48:12 ---  Creating table "RBDataTabA"
11/10/2010 15:48:12  "RBDATAGUARD"."RBDataTabA" CREATED
11/10/2010 15:48:12 ---  Creating table "RBDataTabB"
11/10/2010 15:48:12  "RBDATAGUARD"."RBDataTabB" CREATED
11/10/2010 15:48:12 ---  Creating table "RBDual"
11/10/2010 15:48:12  "RBDATAGUARD"."RBDual" CREATED
11/10/2010 15:48:12  STARTING TO COPY DATABASE "RBDATAGUARD"
11/10/2010 15:48:12 ---  Sending Begin Transaction request
11/10/2010 15:48:13 ---  PARTITIONS WHERE clause not specified
11/10/2010 15:48:13 ---  Retrieving and processing configuration parcels
11/10/2010 15:48:13  DBS Configuration Information:
11/10/2010 15:48:13  Total AMPs: 16
11/10/2010 15:48:13  Down AMPs: 0
11/10/2010 15:48:13  Number of AMPs involved: 16
11/10/2010 15:48:13  System Format: 7
11/10/2010 15:48:13  Hash Function: 773
11/10/2010 15:48:13 ---  Sending request to delete tables
11/10/2010 15:48:13 ---  Start sending Insert Dictionary Row requests.
11/10/2010 15:48:13 ---  Sending End Transaction request
11/10/2010 15:48:13  "RBDataTabA" - DICTIONARY ROWS COPIED
11/10/2010 15:48:13  "RBDataTabB" - DICTIONARY ROWS COPIED
11/10/2010 15:48:13  "RBDual" - DICTIONARY ROWS COPIED
11/10/2010 15:48:13  DICTIONARY COPY COMPLETED
11/10/2010 15:48:13 ---  End dictionary phase
11/10/2010 15:48:13 ---  Delete Excluded Tables List for ALL PARTITIONS
11/10/2010 15:48:13 ---  Begin data phase
11/10/2010 15:48:13 ---  Sending Begin Transaction request
11/10/2010 15:48:13 ---  Retrieving and processing configuration parcels
11/10/2010 15:48:13 ---  Inserting event row in RCEVENT table.
11/10/2010 15:48:13 ---  Searching for Table Header Record on ARCHIVE
11/10/2010 15:48:13 ---  Table header record: "RBDataTabA"
11/10/2010 15:48:13 ---  Different config/hash size. RESTORE FALLBACK disabled.
11/10/2010 15:48:13 ---  Starting table "RBDataTabA"
11/10/2010 15:48:13 ---  Table "RBDataTabA" is not a deferred object
11/10/2010 15:48:13 ---  Table "RBDataTabA": Table Id= 0x116f0000, Fallback=F
11/10/2010 15:48:13 ---  Requesting index descriptor from DBS.
11/10/2010 15:48:13 ---  Requesting LOB descriptor from DBS.
11/10/2010 15:48:13 ---  Restoring data for Primary Data subtable
11/10/2010 15:48:13  "RBDataTabA" - 402 BYTES, 0 ROWS COPIED
11/10/2010 15:48:13 ---  Searching for Table Header Record on ARCHIVE
11/10/2010 15:48:13 ---  Table header record: "RBDataTabB"
11/10/2010 15:48:13 ---  Starting table "RBDataTabB"
11/10/2010 15:48:13 ---  Table "RBDataTabB" is not a deferred object
11/10/2010 15:48:13 ---  Table "RBDataTabB": Table Id= 0x11700000, Fallback=F
11/10/2010 15:48:13 ---  Requesting index descriptor from DBS.
11/10/2010 15:48:13 ---  Requesting LOB descriptor from DBS.
11/10/2010 15:48:13 ---  Restoring data for Primary Data subtable
11/10/2010 15:48:13  "RBDataTabB" - 402 BYTES, 0 ROWS COPIED
11/10/2010 15:48:13 ---  Searching for Table Header Record on ARCHIVE
11/10/2010 15:48:13 ---  Table header record: "RBDual"
11/10/2010 15:48:13 ---  Starting table "RBDual"
11/10/2010 15:48:13 ---  Table "RBDual" is not a deferred object
11/10/2010 15:48:13 ---  Table "RBDual": Table Id= 0x11710000, Fallback=F
11/10/2010 15:48:13 ---  Requesting index descriptor from DBS.
11/10/2010 15:48:13 ---  Requesting LOB descriptor from DBS.
11/10/2010 15:48:13 ---  Restoring data for Primary Data subtable
11/10/2010 15:48:13  "RBDual" - 489 BYTES, 1 ROWS COPIED
11/10/2010 15:48:13 ---  Sending buildsort for index 0, Primary Table.
11/10/2010 15:48:13 ---  Building fallback subtable for index 0.
11/10/2010 15:48:14 ---  Clearing build flag for index 0.
11/10/2010 15:48:14 ---  Sending buildsort for index 0, Primary Table.
11/10/2010 15:48:14 ---  Building fallback subtable for index 0.
11/10/2010 15:48:15 ---  Clearing build flag for index 0.
11/10/2010 15:48:15 ---  Sending buildsort for index 0, Primary Table.
11/10/2010 15:48:15 ---  Building fallback subtable for index 0.
11/10/2010 15:48:16 ---  Clearing build flag for index 0.
11/10/2010 15:48:16 ---  Updating event row.
11/10/2010 15:48:16 ---  Sending End Transaction request
11/10/2010 15:48:16 ---  End data phase
11/10/2010 15:48:16 ---  Sending release lock request to DBS.
11/10/2010 15:48:16  "RBDATAGUARD" - LOCK RELEASED
11/10/2010 15:48:16
11/10/2010 15:48:16  STATEMENT COMPLETED
11/10/2010 15:48:16
11/10/2010 15:48:16
11/10/2010 15:48:16  LOGOFF;
11/10/2010 15:48:16 ---  Disconnecting all sessions.
11/10/2010 15:48:16  LOGGED OFF  15 SESSIONS
11/10/2010 15:48:16  STATEMENT COMPLETED
11/10/2010 15:48:16
11/10/2010 15:48:16
11/10/2010 15:48:16  ARCMAIN TERMINATED WITH SEVERITY 0

CHECKPOINT, JOURNAL Archive/Restore and ROLLFORWARD

Checkpoint basically creates a snapshot, so that we can capture the transactions point in time from the permanent journal table. If you cannot afford to loose more than 30mts worth of data from Production, the CHECKPOINT process should be cron'd or scheduled to run every 30mts.

Also applying JOUNRALS to STANDBY depends on the need. You can basically take archives of these journals and keep it in the file system than applying if you are concerned about CPU costs or reduce the processing time overall. This way, you will need to apply the journals as and when needed.

Cumulative vs. incremental decision depends on how size of the journals. If there is too much of DML  
activity on too many rows, that increases the size of journals. Disadvantage of the incremental one's is, every snapshot increment should be applied in order and roll forwarded before the subsequent one can be applied. So depending on how often you take snapshots and how big the journals you can decide whether to go for incremental vs. cumulative.

Also why do I need a 3rd table –  RBDual?

In a real time scenario, application database will have many data tables. And the only good way to check if the journal process is working is relying on the logs. But how do you really really make sure of this- by looking at the data. This requires expertise in querying application tables to find out exactly when transactions occurred on Production and whether those got applied to the standby. As long as you know how to query the application tables, you don't need an extra table. But ideally for as an additional check for DBA's supporting the application, creating this additional table will help.  Oracle's System Change Number process – SCN generation. SCN in Oracle is far superior, but the concept I used here is generating a one-up number to store it in RBDual.  As you proceed further you will understand the need for it.

Below are the scripts used for journal archive/restore.

scn_prod
----------

.logon prodsrv/raj,passwd;
DATABASE RBDataGuard;
INSERT INTO RBDual (SCN)
SELECT CSUM(1,1) + DT.SCN
  FROM
   (SELECT ZEROIFNULL(MAX(SCN)) FROM RBDual ) AS DT(SCN);
SELECT SCN , LastUpdatedTStamp FROM RBDual ORDER BY SCN ;
LOGOFF;
QUIT;

scn_stdby.sql
----------------

.logon stdbysrv/raj,passwd;
DATABASE RBDataGuard;
SELECT SCN , LastUpdatedTStamp FROM RBDual ORDER BY SCN ;
LOGOFF;
QUIT;


res_jl.sql

----------
.logon stdbysrv/raj,passwd;
COPY JOURNAL TABLE
            (RBDataGuard.RBDataGuard_JOURNAL)
 (APPLY TO(
   RBDataGuard.RBDual
  ,RBDataGuard.RBDataTabA
  ,RBDataGuard.RBDataTabB
  ))
,FILE = JLDump
, RELEASE LOCK;

ROLLFORWARD (RBDataGuard) ALL
    ,USE RESTORED JOURNAL
    ,RELEASE LOCK;

LOGOFF;
QUIT;

run_jl.sh

----------


#!/bin/sh

#Clean up previous logs
rm *.rlg *.log

#Generate SCN, Archive Journal and Generate SCN again.
bteq < scn_prod.sql
arcmain  sessions=12 VB3 perffile=arcJL.log < bck_jl.sql
bteq < scn_prod.sql

bteq < scn_stdby.sql
arcmain  sessions=12 VB3 perffile=resJL.log < res_jl.sql
bteq < scn_stdby.sql

#End


Retrieve SCN from STDBY, Apply Journal, RollForward.

Once you establish above scripts, execute run_jl.sh. To make sure I get some transactions in the data tables, I simultaneously ran a multithreaded Java Program to insert random numbers. I wanted to start the inserts before the checkpoint process and have these stay on until the whole journal process is done. So for this I used 10 threads (users) each with one connection and each of these threads inserting 150+ (approx) SQL's so the inserts stay on for the entire duration of the checkpoint.

If you scroll down to see the marked RED portion of the output from sh run_jl.sh you can see the checkpoint was done at 15:49:05 and the inserts started @15:49:01.390000  and finished @ 15:49:23.490000.
While running the run_jl.sh, I will also be inserting one row to RBDual just before and after the checkpoint by incrementing the value by one. See highlighted portion in green.


Also on the STDBY server, I'm doing a select on this same table prior to the restore of the journal and also after the journal has been restored and roll forwarded. See highlighted portion in blue. Looking at the SCN and Timestamp of the RBDual from the Test Server  you can see the 2 rows prior to checkpoint
came over but the 3rd hasn't, which's what the expected behavior is.

Script consists of actions performed in Prodserver and also on Standby. I have highlighted the line, which shows from where the standby actions are performed. Look for "logon stdbysrv/raj"

$ sh run_jl.sh

BTEQ 12.00.00.09 Wed Nov 10 15:49:02 2010

+---------+---------+---------+---------+---------+---------+---------+----
.logon prodsrv/raj,

 *** Logon successfully completed.
 *** Teradata Database Release is 12.00.03.04
 *** Teradata Database Version is 12.00.03.06d
 *** Transaction Semantics are BTET.
 *** Character Set Name is 'ASCII'.

 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

DATABASE RBDataGuard;

 *** New default database accepted.
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----

INSERT INTO RBDual (SCN)
SELECT CSUM(1,1) + DT.SCN
  FROM
   (SELECT ZEROIFNULL(MAX(SCN)) FROM RBDual ) AS DT(SCN);

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----

SELECT SCN , LastUpdatedTStamp FROM RBDual ORDER BY SCN ;

 *** Query completed. 2 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

                                     SCN           LastUpdatedTStamp
----------------------------------------  --------------------------
                                      1.  2010-11-10 15:28:53.000000
                                      2.  2010-11-10 15:49:02.960000

+---------+---------+---------+---------+---------+---------+---------+----

LOGOFF;
 *** You are now logged off from the DBC.
 *** Warning: You must log on before sending DBC/SQL requests.
+---------+---------+---------+---------+---------+---------+---------+----
QUIT;
 *** Exiting BTEQ...
 *** RC (return code) = 2
11/10/2010 15:49:03   Copyright 1989-2007, NCR Corporation.
11/10/2010 15:49:03   All Rights Reserved.
11/10/2010 15:49:03
11/10/2010 15:49:03     ***   ****    ****
11/10/2010 15:49:03    *   *  *   *  *        PROGRAM: ARCMAIN
11/10/2010 15:49:03    *****  ****   *        RELEASE: 12.00.00.10
11/10/2010 15:49:03    *   *  *  *   *        BUILD:   070177eLX (Oct  1 2009)
11/10/2010 15:49:03    *   *  *   *   ****
11/10/2010 15:49:03
11/10/2010 15:49:03 ---  Opening Restart Log: ARCLOG101110_154903_25794.rlg
11/10/2010 15:49:03 ---
11/10/2010 15:49:03  RESTARTLOG = ARCLOG101110_154903_25794.rlg
11/10/2010 15:49:03
11/10/2010 15:49:03  PARAMETERS IN USE:
11/10/2010 15:49:03
11/10/2010 15:49:03  SESSIONS 12
11/10/2010 15:49:03  VERBOSE LEVEL 3
11/10/2010 15:49:03  PERFFILE = arcJL.log
11/10/2010 15:49:03
11/10/2010 15:49:03
11/10/2010 15:49:03  CHARACTER SET IN USE: ASCII
11/10/2010 15:49:03  .LOGON PRODSRV/raj,;
11/10/2010 15:49:03 ---  Connecting SQL and CONTROL sessions.
11/10/2010 15:49:04 ---  Logon Sequence Number = 4496473 (0x00449c59)
11/10/2010 15:49:04 ---  Maximum Parcel Size supported: 64K bytes
11/10/2010 15:49:04 ---  Teradata V12 detected
11/10/2010 15:49:04  LOGGED ON 3 SESSIONS
11/10/2010 15:49:04 ---  CLIv2 VERSION: 12.00.00.0
11/10/2010 15:49:04
11/10/2010 15:49:04  DBS LANGUAGE SUPPORT MODE Standard
11/10/2010 15:49:04  DBS RELEASE 12.00.03.04
11/10/2010 15:49:04  DBS VERSION 12.00.03.06d
11/10/2010 15:49:04
11/10/2010 15:49:04  STATEMENT COMPLETED
11/10/2010 15:49:04
11/10/2010 15:49:04
11/10/2010 15:49:04  CHECKPOINT (RBDataGuard) ALL ,WITH SAVE;

11/10/2010 15:49:05 ---  Sending Begin Transaction request
11/10/2010 15:49:05 ---  Sending Allocate Event Number request.
11/10/2010 15:49:05  UTILITY EVENT NUMBER  - 2344420
11/10/2010 15:49:05 ---  Sending End Transaction request
11/10/2010 15:49:05  CHECKPOINT JOURNAL - "RBDATAGUARD"
11/10/2010 15:49:05 ---  Sending Begin Transaction request
11/10/2010 15:49:05 ---  Sending End Transaction request
11/10/2010 15:49:05  STATEMENT COMPLETED
11/10/2010 15:49:05
11/10/2010 15:49:05
11/10/2010 15:49:05  ARCHIVE JOURNAL TABLES
11/10/2010 15:49:05     (RBDataGuard.RBDataGuard_JOURNAL)
11/10/2010 15:49:05    ,RELEASE LOCK
11/10/2010 15:49:05    ,FILE = JLDump;
11/10/2010 15:49:05 ---  Checking for ACTIVATE_CATALOG macro
11/10/2010 15:49:05 ---  ACTIVATE_CATALOG does not exist or select accessright
                    ---  not granted to this user
11/10/2010 15:49:05 ---  Checking for ACTIVATE_LOGSKIPPED macro
11/10/2010 15:49:05 ---  ACTIVATE_LOGSKIPPED does not exist or select
                    ---  accessright not granted to this user
11/10/2010 15:49:05 ---  Checking for ACTIVATE_ENCRYPTION macro
11/10/2010 15:49:05 ---  ACTIVATE_ENCRYPTION does not exist or select
                    ---  accessright not granted to this user
11/10/2010 15:49:05 ---  Sending Begin Transaction request
11/10/2010 15:49:05 ---  Sending Allocate Event Number request.
11/10/2010 15:49:05  UTILITY EVENT NUMBER  - 2344421
11/10/2010 15:49:05 ---  Sending End Transaction request
11/10/2010 15:49:05 ---  Connecting data sessions.
11/10/2010 15:49:13  LOGGED ON   12 SESSIONS
11/10/2010 15:49:13 ---  Opening file JLDUMP.
11/10/2010 15:49:13 ---  File open done.
11/10/2010 15:49:13 ---  Writing archive header record.
11/10/2010 15:49:13
11/10/2010 15:49:13  ARCHIVING DATABASE "RBDATAGUARD"
11/10/2010 15:49:13 ---  Begin dictionary phase
11/10/2010 15:49:13 ---  Requesting DBS to send dictionary rows.
11/10/2010 15:49:13 ---  Sending Begin Transaction request
11/10/2010 15:49:13 ---  Retrieving and processing configuration parcels
11/10/2010 15:49:13  DBS Configuration Information:
11/10/2010 15:49:13  Total AMPs: 572
11/10/2010 15:49:13  Down AMPs: 0
11/10/2010 15:49:13  Number of AMPs involved: 572
11/10/2010 15:49:13  System Format: 7
11/10/2010 15:49:13  Hash Function: 773
11/10/2010 15:49:13 ---  Writing database header record.
11/10/2010 15:49:13 ---  Writing DBS configuration records
11/10/2010 15:49:13 ---  Writing table descriptor records.
11/10/2010 15:49:13 ---  PARTITIONS WHERE clause not specified
11/10/2010 15:49:13 ---  Set ObjHasData Flag based on object type
11/10/2010 15:49:13 ---  Start writing dictionary rows
11/10/2010 15:49:13 ---  Writing dictionary trailer record.
11/10/2010 15:49:13 ---  Sending End Transaction request
11/10/2010 15:49:13 ---  End dictionary phase
11/10/2010 15:49:13 ---  Begin data phase
11/10/2010 15:49:13 ---  Sending Begin Transaction request
11/10/2010 15:49:14 ---  Retrieving and processing configuration parcels
11/10/2010 15:49:14 ---  Inserting event row in RCEVENT table.
11/10/2010 15:49:14 ---  Starting table "RBDataGuard_JOURNAL"
11/10/2010 15:49:14 ---  Table "RBDataGuard_JOURNAL" is not a spanned object
11/10/2010 15:49:14 ---  Starting AMP=286
11/10/2010 15:49:14 ---  Writing table header record
11/10/2010 15:49:14 ---  Writing DBS configuration records
11/10/2010 15:49:14 ---  Start receiving and writing data block record(s)
11/10/2010 15:49:14 ---  Data subtable:   235,612 bytes received
11/10/2010 15:49:14 ---  Writing table trailer record.
11/10/2010 15:49:14 ---  Table "RBDataGuard_JOURNAL": Table
                    ---  Id=0x9A8140AF,Fallback=N
11/10/2010 15:49:14  TABLE "RBDataGuard_JOURNAL" - 235,612 BYTES ARCHIVED
11/10/2010 15:49:14 ---  Updating event row.
11/10/2010 15:49:14 ---  Sending End Transaction request
11/10/2010 15:49:14 ---  End data phase
11/10/2010 15:49:14 ---  Writing database trailer record.
11/10/2010 15:49:14 ---  Sending release lock request to DBS.
11/10/2010 15:49:14  "RBDATAGUARD"."RBDATAGUARD_JOURNAL" - LOCK RELEASED
11/10/2010 15:49:14 ---  Writing archive trailer record.
11/10/2010 15:49:14 ---  Closing archive file(s)
11/10/2010 15:49:14  DUMP COMPLETED
11/10/2010 15:49:14  STATEMENT COMPLETED
11/10/2010 15:49:14
11/10/2010 15:49:14
11/10/2010 15:49:14  LOGOFF;
11/10/2010 15:49:14 ---  Disconnecting all sessions.
11/10/2010 15:49:16  LOGGED OFF  15 SESSIONS
11/10/2010 15:49:16  STATEMENT COMPLETED
11/10/2010 15:49:16
11/10/2010 15:49:16
11/10/2010 15:49:16  ARCMAIN TERMINATED WITH SEVERITY 0


BTEQ 12.00.00.09 Wed Nov 10 15:49:16 2010

+---------+---------+---------+---------+---------+---------+---------+----
.logon prodsrv/raj,

 *** Logon successfully completed.
 *** Teradata Database Release is 12.00.03.04
 *** Teradata Database Version is 12.00.03.06d
 *** Transaction Semantics are BTET.
 *** Character Set Name is 'ASCII'.

 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

DATABASE RBDataGuard;

 *** New default database accepted.
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----

INSERT INTO RBDual (SCN)
SELECT CSUM(1,1) + DT.SCN
  FROM
   (SELECT ZEROIFNULL(MAX(SCN)) FROM RBDual ) AS DT(SCN);

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----

SELECT SCN , LastUpdatedTStamp FROM RBDual ORDER BY SCN ;

 *** Query completed. 3 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

                                     SCN           LastUpdatedTStamp
----------------------------------------  --------------------------
                                      1.  2010-11-10 15:28:53.000000
                                      2.  2010-11-10 15:49:02.960000
                                      3.  2010-11-10 15:49:17.130000

+---------+---------+---------+---------+---------+---------+---------+----

LOGOFF;
 *** You are now logged off from the DBC.
 *** Warning: You must log on before sending DBC/SQL requests.
+---------+---------+---------+---------+---------+---------+---------+----
QUIT;
 *** Exiting BTEQ...
 *** RC (return code) = 2
BTEQ 12.00.00.09 Wed Nov 10 15:49:17 2010

+---------+---------+---------+---------+---------+---------+---------+----
.logon stdbysrv/raj,

 *** Logon successfully completed.
 *** Teradata Database Release is 12.00.03.16
 *** Teradata Database Version is 12.00.03.18
 *** Transaction Semantics are BTET.
 *** Character Set Name is 'ASCII'.

 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

DATABASE RBDataGuard;

 *** New default database accepted.
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----

SELECT SCN , LastUpdatedTStamp FROM RBDual ORDER BY SCN ;

 *** Query completed. One row found. 2 columns returned.
 *** Total elapsed time was 1 second.

                                     SCN           LastUpdatedTStamp
----------------------------------------  --------------------------
                                      1.  2010-11-10 15:28:53.000000

+---------+---------+---------+---------+---------+---------+---------+----

LOGOFF;
 *** You are now logged off from the DBC.
 *** Warning: You must log on before sending DBC/SQL requests.
+---------+---------+---------+---------+---------+---------+---------+----
QUIT;
 *** Exiting BTEQ...
 *** RC (return code) = 2
11/10/2010 15:49:17   Copyright 1989-2007, NCR Corporation.
11/10/2010 15:49:17   All Rights Reserved.
11/10/2010 15:49:17
11/10/2010 15:49:17     ***   ****    ****
11/10/2010 15:49:17    *   *  *   *  *        PROGRAM: ARCMAIN
11/10/2010 15:49:17    *****  ****   *        RELEASE: 12.00.00.10
11/10/2010 15:49:17    *   *  *  *   *        BUILD:   070177eLX (Oct  1 2009)
11/10/2010 15:49:17    *   *  *   *   ****
11/10/2010 15:49:17
11/10/2010 15:49:17 ---  Opening Restart Log: ARCLOG101110_154917_25903.rlg
11/10/2010 15:49:17 ---
11/10/2010 15:49:17  RESTARTLOG = ARCLOG101110_154917_25903.rlg
11/10/2010 15:49:17
11/10/2010 15:49:17  PARAMETERS IN USE:
11/10/2010 15:49:17
11/10/2010 15:49:17  SESSIONS 12
11/10/2010 15:49:17  VERBOSE LEVEL 3
11/10/2010 15:49:17  PERFFILE = resJL.log
11/10/2010 15:49:17
11/10/2010 15:49:17
11/10/2010 15:49:17  CHARACTER SET IN USE: ASCII
11/10/2010 15:49:17  .LOGON STDBYSRV/raj,;
11/10/2010 15:49:17 ---  Connecting SQL and CONTROL sessions.
11/10/2010 15:49:18 ---  Logon Sequence Number = 521 (0x00000209)
11/10/2010 15:49:18 ---  Maximum Parcel Size supported: 64K bytes
11/10/2010 15:49:18 ---  Teradata V12 detected
11/10/2010 15:49:18  LOGGED ON 3 SESSIONS
11/10/2010 15:49:18 ---  CLIv2 VERSION: 12.00.00.0
11/10/2010 15:49:18
11/10/2010 15:49:18  DBS LANGUAGE SUPPORT MODE Standard
11/10/2010 15:49:18  DBS RELEASE 12.00.03.16
11/10/2010 15:49:18  DBS VERSION 12.00.03.18
11/10/2010 15:49:18
11/10/2010 15:49:18  STATEMENT COMPLETED
11/10/2010 15:49:18
11/10/2010 15:49:18
11/10/2010 15:49:18  COPY JOURNAL TABLE

11/10/2010 15:49:18              (RBDataGuard.RBDataGuard_JOURNAL)

11/10/2010 15:49:18   (APPLY TO(

11/10/2010 15:49:18     RBDataGuard.RBDual
11/10/2010 15:49:18    ,RBDataGuard.RBDataTabA
11/10/2010 15:49:18    ,RBDataGuard.RBDataTabB
11/10/2010 15:49:18    ))

11/10/2010 15:49:18  ,FILE = JLDump
11/10/2010 15:49:18  , RELEASE LOCK;
11/10/2010 15:49:18 ---  RESTORE FALLBACK enabled due to COPY statement.
11/10/2010 15:49:18 ---  Sending Begin Transaction request
11/10/2010 15:49:18 ---  Sending Allocate Event Number request.
11/10/2010 15:49:18  UTILITY EVENT NUMBER  - 2432
11/10/2010 15:49:18 ---  Sending End Transaction request
11/10/2010 15:49:18 ---  Connecting data sessions.
11/10/2010 15:49:28  LOGGED ON   12 SESSIONS
11/10/2010 15:49:28 ---  Checking for multiple users
11/10/2010 15:49:28 ---  Opening ARCHIVE file
11/10/2010 15:49:28 ---  Reading Archive Header Record
11/10/2010 15:49:28 ---  ARC VERSION  14
11/10/2010 15:49:28 ---  ARCHIVED AT    11-10-10 15:49:13
11/10/2010 15:49:28 ---  ARCHIVE CHARACTER SET: ASCII
11/10/2010 15:49:28 ---  ARCHIVED FROM  ALL AMP DOMAINS
11/10/2010 15:49:28 ---  SYSTEM INTERNAL FORMAT 7
11/10/2010 15:49:28 ---  UTILITY EVENT NUMBER IN ARCHIVE  - 2344421
11/10/2010 15:49:28 ---  SOURCE RDBMS VERSION: LANGUAGE SUPPORT MODE:Standard;
                    ---  RELEASE:12.00.03.04; VERSION:12.00.03.06d;
11/10/2010 15:49:28 ---  SOURCE ROWFORMAT: 1
11/10/2010 15:49:28 ---  SOURCE DBSPLATFORM: 17
11/10/2010 15:49:28 ---  Archive Event Number = 2344421
11/10/2010 15:49:28 ---  Archive System Format = 7
11/10/2010 15:49:28 ---  Archive Locale = 0
11/10/2010 15:49:28 ---  Archive Version Number = 14
11/10/2010 15:49:28 ---  Database header record: "RBDATAGUARD"
11/10/2010 15:49:28 ---  Begin dictionary phase
11/10/2010 15:49:28 ---  Build Excluded Tables List for ALL PARTITIONS
11/10/2010 15:49:28 ---  Check if excluded tables exist
11/10/2010 15:49:28  STARTING TO COPY TABLE "RBDATAGUARD"."RBDataGuard_JOURNAL"
11/10/2010 15:49:28 ---  Sending Begin Transaction request
11/10/2010 15:49:28 ---  PARTITIONS WHERE clause not specified
11/10/2010 15:49:28 ---  Retrieving and processing configuration parcels
11/10/2010 15:49:28  DBS Configuration Information:
11/10/2010 15:49:28  Total AMPs: 16
11/10/2010 15:49:28  Down AMPs: 0
11/10/2010 15:49:28  Number of AMPs involved: 16
11/10/2010 15:49:28  System Format: 7
11/10/2010 15:49:28  Hash Function: 773
11/10/2010 15:49:28 ---  Start sending Insert Dictionary Row requests.
11/10/2010 15:49:28 ---  Sending End Transaction request
11/10/2010 15:49:28  DICTIONARY COPY COMPLETED
11/10/2010 15:49:28 ---  End dictionary phase
11/10/2010 15:49:28 ---  Delete Excluded Tables List for ALL PARTITIONS
11/10/2010 15:49:28 ---  Begin data phase
11/10/2010 15:49:28 ---  Sending Begin Transaction request
11/10/2010 15:49:28 ---  Retrieving and processing configuration parcels
11/10/2010 15:49:28 ---  Inserting event row in RCEVENT table.
11/10/2010 15:49:28 ---  Searching for Table Header Record on ARCHIVE
11/10/2010 15:49:28 ---  Table header record: "RBDataGuard_JOURNAL"
11/10/2010 15:49:28 ---  Different config/hash size. RESTORE FALLBACK disabled.
11/10/2010 15:49:28 ---  Starting table "RBDATAGUARD_JOURNAL"
11/10/2010 15:49:28 ---  Table "RBDATAGUARD_JOURNAL" is not a deferred object
11/10/2010 15:49:28 ---  Table "RBDATAGUARD_JOURNAL": Table Id= 0x11614000,
                    ---  Fallback=N
11/10/2010 15:49:28 ---  Restoring data for Primary Data subtable
11/10/2010 15:49:29  "RBDATAGUARD_JOURNAL" - 235,612 BYTES PROCESSED, 235,612
                     BYTES COPIED
11/10/2010 15:49:29 ---  Sending buildsort for index 0, Primary Table.
11/10/2010 15:49:29 ---  Building fallback subtable for index 0.
11/10/2010 15:49:29 ---  Clearing build flag for index 0.
11/10/2010 15:49:29 ---  Updating event row.
11/10/2010 15:49:29 ---  Sending End Transaction request
11/10/2010 15:49:29 ---  End data phase
11/10/2010 15:49:29 ---  Sending release lock request to DBS.
11/10/2010 15:49:29  "RBDATAGUARD"."RBDATAGUARD_JOURNAL" - LOCK RELEASED
11/10/2010 15:49:29
11/10/2010 15:49:29  STATEMENT COMPLETED
11/10/2010 15:49:29
11/10/2010 15:49:29
11/10/2010 15:49:29  ROLLFORWARD (RBDataGuard) ALL
11/10/2010 15:49:29      ,USE RESTORED JOURNAL
11/10/2010 15:49:29      ,RELEASE LOCK;
11/10/2010 15:49:29 ---  Sending Begin Transaction request
11/10/2010 15:49:29 ---  Sending Allocate Event Number request.
11/10/2010 15:49:29  UTILITY EVENT NUMBER  - 2434
11/10/2010 15:49:29 ---  Sending End Transaction request
11/10/2010 15:49:29 ---  Sending Begin Transaction request
11/10/2010 15:49:29 ---  Retrieving and processing configuration parcels
11/10/2010 15:49:29 ---  Sending End Transaction request
11/10/2010 15:49:29 ---  Sending Begin Transaction request
11/10/2010 15:49:29 ---  Retrieving and processing configuration parcels
11/10/2010 15:49:29 ---  Inserting event row in RCEVENT table.
11/10/2010 15:49:29 ---  Inserting event row in RCEVENT table.
11/10/2010 15:49:29 ---  Inserting event row in RCEVENT table.
11/10/2010 15:49:29 ---  Updating event row.
11/10/2010 15:49:30 ---  Sending request to delete tables
11/10/2010 15:49:30 ---  Sending End Transaction request
11/10/2010 15:49:30 ---  Sending release lock request to DBS.
11/10/2010 15:49:30  "RBDATAGUARD"."RBDATATABA" - LOCK RELEASED
11/10/2010 15:49:30 ---  Sending release lock request to DBS.
11/10/2010 15:49:30  "RBDATAGUARD"."RBDATATABB" - LOCK RELEASED
11/10/2010 15:49:30 ---  Sending release lock request to DBS.
11/10/2010 15:49:30  "RBDATAGUARD"."RBDUAL" - LOCK RELEASED
11/10/2010 15:49:30 ---  Sending release lock request to DBS.
11/10/2010 15:49:30  "RBDATAGUARD"."RBDATAGUARD_JOURNAL" - LOCK RELEASED
11/10/2010 15:49:30  'ROLLFORWARD' COMPLETED.
11/10/2010 15:49:30  STATEMENT COMPLETED
11/10/2010 15:49:30
11/10/2010 15:49:30
11/10/2010 15:49:30  LOGOFF;
11/10/2010 15:49:30 ---  Disconnecting all sessions.
11/10/2010 15:49:30  LOGGED OFF  15 SESSIONS
11/10/2010 15:49:30  STATEMENT COMPLETED
11/10/2010 15:49:30
11/10/2010 15:49:30
11/10/2010 15:49:30  ARCMAIN TERMINATED WITH SEVERITY 0


BTEQ 12.00.00.09 Wed Nov 10 15:49:30 2010

+---------+---------+---------+---------+---------+---------+---------+----
.logon stdbysrv/raj,

 *** Logon successfully completed.
 *** Teradata Database Release is 12.00.03.16
 *** Teradata Database Version is 12.00.03.18
 *** Transaction Semantics are BTET.
 *** Character Set Name is 'ASCII'.

 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

DATABASE RBDataGuard;

 *** New default database accepted.
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----

SELECT SCN , LastUpdatedTStamp FROM RBDual ORDER BY SCN ;

 *** Query completed. 2 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

                                     SCN           LastUpdatedTStamp
----------------------------------------  --------------------------
                                      1.  2010-11-10 15:28:53.000000
                                      2.  2010-11-10 15:49:02.960000

+---------+---------+---------+---------+---------+---------+---------+----

LOGOFF;
 *** You are now logged off from the DBC.
 *** Warning: You must log on before sending DBC/SQL requests.
+---------+---------+---------+---------+---------+---------+---------+----
QUIT;
 *** Exiting BTEQ...
 *** RC (return code) = 2

Teradata's locking mechanism is not that great as Oracle. Of few test runs I did, checkpoint statement really blocked the transactions at times for more than 3-3mts, which isn't good. And if you look at standby database to see what transactions came after the journal restore, anything that started from 15:49:01.390000 until 2010-11-10 15:49:05.290000, later is the Checkpoint completion time.

On Standby Server

SELECT MIN( LastUpdatedTStamp) ,   MAX (LastUpdatedTStamp) FROM RBDataTabA    ;

SELECT MIN( LastUpdatedTStamp) ,   MAX (LastUpdatedTStamp) FROM RBDataTabA
   ;

 *** Query completed. One row found. 2 columns returned.
 *** Total elapsed time was 1 second.

Minimum(LastUpdatedTStamp)  Maximum(LastUpdatedTStamp)
--------------------------  --------------------------
2010-11-10 15:49:01.390000  2010-11-10 15:49:05.290000

 BTEQ -- Enter your DBC/SQL request or BTEQ command:


SELECT MIN( LastUpdatedTStamp) ,   MAX (LastUpdatedTStamp) FROM RBDataTabB    ;

SELECT MIN( LastUpdatedTStamp) ,   MAX (LastUpdatedTStamp) FROM RBDataTabB
   ;

 *** Query completed. One row found. 2 columns returned.
 *** Total elapsed time was 1 second.

Minimum(LastUpdatedTStamp)  Maximum(LastUpdatedTStamp)
--------------------------  --------------------------
2010-11-10 15:49:01.670000  2010-11-10 15:49:05.310000

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

This is no way close to Oracle's Data Guard or Fast Start failover, which is far superior in terms of role transitions and failover/switchover capabilities but can get the job done depending on how critical and active your system is.

5 comments:

  1. I am very impressed with the information that you have shared in your post. You have excellently explained the purpose and all the steps to configure the TeraData STANDBY database. Its a great way to perform all the business transaction even in case of a disaster.
    sap upgrade automation

    ReplyDelete
  2. Hello:

    The bck_jl.sql source code is missing...

    Cheers.

    ReplyDelete
  3. Thanks. See below please.


    .logon prodsrv/raj,passwd;
    CHECKPOINT (RBDataGuard) ALL ,WITH SAVE;
    ARCHIVE JOURNAL TABLES
    (RBDataGuard.RBDataGuard_JOURNAL)
    ,FILE = JLDump
    ,RELEASE LOCK;
    LOGOFF;
    QUIT;


    I pretty much copied it from the output log itself, if you see starting from this line,
    ...
    "11/10/2010 15:49:04 CHECKPOINT (RBDataGuard) ALL ,WITH SAVE;"
    ...

    ReplyDelete