Sharing my experiences in Oracle + Linux + Perl + Java + Networking + High-Availability + Clusters: 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

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 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 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 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 ARCMAIN TERMINATED WITH SEVERITY 0

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 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 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 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 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 3^rd 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 3^rd 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

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 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 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 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 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 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

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 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 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 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 --- 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 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 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:

AnonymousJune 26, 2011 at 11:56 PM
Nice post
NeetuJune 27, 2011 at 12:49 AM
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
AnonymousJune 27, 2012 at 3:02 AM
Hello:

The bck_jl.sql source code is missing...

Cheers.
RajJune 27, 2012 at 6:13 AM
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;"
...
UnknownAugust 4, 2020 at 1:07 AM
Nice information,thanks for given this useful message really it was helpful for
me.

Python Coaching Classes near me | Python Tutorial in coimbatore | python Training Institute in coimbatore | Best Python Training Centre | Online python Training Institute in coimbatore | Python Course with placement in coimbatore | Python Course training in coimbatore

Sharing my experiences in Oracle + Linux + Perl + Java + Networking + High-Availability + Clusters

Friday, November 12, 2010

Configuring Teradata STANDBY Database.

Home | Profile

5 comments:

Followers

Popular Posts

Search This Blog