1.Archive and Recovery Utility (ARC)
Teradata ARC可做如下事项:
• 备份数据库,表,或者分区表选定的分区.
• 恢复数据库,表,或者分区表指定的分区.
• 拷贝已备份数据库,表,或者分区表选定的分区到不同硬件平台的Teratdata数据库上.
• 利用回滚或前滚技术将数据库恢复到任意的checkpoint(using change images from a journal table).
• Delete change image rows from a journal table.
Archive – captures user data on portable storage media.
Restore – restores data from portable storage media.
Recovery – recovers changes to data from permanent journal tables.
ARC provides additional data protection for these situations:
• Loss of an AMP’s Vdisk for no fallback tables
• Loss of multiple Vdisks (AMPs) in the same cluster
• Failed batch processes
• Accidentally dropped tables, views or macros
• Miscellaneous user errors
• Disaster recovery
Common uses for ARC:
• Archive a database, individual table, or selected partitions of a PPI table (V2R6).
• Restore a database, individual table, or selected partitions of a PPI table (V2R6).
• Copy an archived database, table, or selected partitions of a PPI table (V2R6) to a Teradata Database on a different system.
2.Archive and Recovery Phases
• Phase 1 — 数据字典阶段(Dictionary Phase)
1. Allocate an event number (from DBC.Next).
2. Issue a BEGIN TRANSACTION statement.
3. Resolve object name.
4. Check access rights.
5. Place locks:
- Utility locks on data dictionary rows.
- Utility locks on data rows.
Note: READ locks on ARCHIVE; EXCLUSIVE locks on RESTORE.
6. Delete existing tables prior to RESTORE.
7. Issue an END TRANSACTION statement.
• Phase 2 — 数据阶段(Data Phase)
1. Issue a BEGIN TRANSACTION statement.
2. Insert rows into RCEVENT and RCCONFIGURATION.
3. Perform the operation.
4. Update RCEVENT.
5. Release locks (if user specified).
6. Issue an END TRANSACTION statement.
3.Restore versus FastLoad
• fastload步骤:
- FastLoad uses a single session to send the INSERT statement to the PE and AMP vprocs.
- Multiple sessions are then used to facilitate sending rows to the AMP vprocs.
- Upon receipt, each AMP vproc hashes each record and redistributes it over the BYNET. This is done in parallel.
- The receiving AMP vproc then writes these rows directly to the target table as unsorted blocks.
- When loading completes, each AMP vproc sorts the target table, puts the rows into blocks, and writes the blocks to disk.
- Then, fallback rows are generated if required. FastLoad operates only on tables with no secondary indexes.
- You have to create any required indexes when the FastLoad is complete.
• Restore步骤:
恢复到相同配置的操作:
- Recovery of data blocks to the AMP vproc.
- The blocks are already in the appropriate format.
恢复到不同配置的操作:
- The block is first sent to the AMP vproc in the old configuration.
- Then, it strips off its own rows and forwards (redistributes) the remainder of the block to the AMP vproc for the new configuration.
Since the original rows were sorted in data blocks by RowID, the result is usually much faster than a normal redistribution.
上图可以说明一个事实,fastload的restore比fastload本身更快,原因如下:
• FastLoad has to hash each row, redistribute every row, collect and write to disk, then read, sort, and write back to disk.
• RESTORE copies blocks to the appropriate AMPs.
• FastLoad operates on a table by table basis (one at a time).
• RESTORE can restore all of the tables for one or more databases with a single job.
4.ARC
There are several ways to invoke the Archive facility.
• NetVault (from BakBone software)
• NetBackup (from VERITAS software) – limited support
• ASF2 (previous X Windows tape management utility – not supported with V2R5/V2R6)
– ASF2 Tape Reader (ASF2TR) can be used to read previous ASF2 tapes
• Command Line (execute arcmain)
• Host or Mainframe
arcmain脚本实例:
--语法
LOGON dbc/sysdba,dbapass;
ARCHIVE DATA TABLES (Database_1),
ABORT, RELEASE LOCK, FILE = db1_data;
LOGOFF;
--dbc备份
.logon dbc,dbcpassword;
Archive data tables (dbc),file = DBCDATA,release lock;
.logoff;
--数据字典备份
.logon dbc,dbcpassword;
Archive dictionary tables (dbc) all,file = DBCDIC,release lock;
.logoff;
5.Restart Log
备份/恢复的第一个动作是将脚本写入“restart log file”.此日志文件包括:
• Current statement
• Object list
• Checkpoint positioning information
• Checkpoint configuration information
如果此作业由于某种原因被中断,ARC工具会利用这个日志来重启archive,restore,或recovery等正在执行的动作.
The RESTARTLOG (or RLOG) = filename runtime parameter can be used to specify a restart log name in UNIX MP-RAS and Windows systems.
6.Session Control
登录命令:
1. 导致两个session登入: 一个是SQL语句,一个是控制请求.
-- 当遇见ARCHIVE或RESTORE命令时, ARC在运行时参数SESSIONS=nnn中吊起一个额外的session请求.
2. 识别用户及账户以分配资源.
3. 为Teradata数据库系统识别用户,以便相关软件核实从属关系并判定访问权限.
CHECKPOINT Permits you to execute both the SQL and ARC utility checkpoint statements.
DUMP Permits you to execute the ARC Archive statement
RESTORE 允许执行下列ARC语句: Restore | Delete Journal | Rollforward | Release Lock* | Rollback Build
登出命令:
1. Ends all Teradata sessions logged on by the task, and
2. Terminates the utility.
要释放其他用户所持有的锁,你必须指定忽略(specify Override)并且持有删除潜在对象的权限.
7.Multiple Sessions
合适的session数目取决于以下因素:
• number of AMPs
• number of channel or LAN connections
• speed and type of tape subsystem
对于小型系统,每个AMP一个session可以确保来自于所有AMP的数据块均匀分布.
• Teradata将每个session分配到单个vproc上,session保持在此vproc上直到所有需要的数据存档,此后按照需要看要不要移动到其他vproc上.
• Archive attempts to build blocks from each vproc in turn. The blocks are composed of complete database blocks.
• Data blocks from different vprocs are never mixed within the same archive block.
8.ARC Statements
LOGON Begins a session.
LOGOFF Ends a session.
ARCHIVE Archives a copy of a database or table to a host-resident data set/file.
ANALYZE Reads an archive tape to display information about its content.
RESTORE Restores a database or table from a archive file to specified AMPs.
COPY Restores a copy of an archived file to a specified Teradata database system.
BUILD Builds indexes and fallback data.
RELEASE LOCK Releases host utility locks on databases or tables.
DELETE DATABASE Deletes a database.
CHECKPOINT Marks a journal for later archive or recovery activities.
ROLLBACK Recovers a database and tables to a state that existed before some change.
ROLLFORWARD Recovers a database or table to a state that existed after some change.
DELETE JOURNAL Deletes SAVED or RESTORED Journal rows.
REVALIDATE REFERENCES Revalidate referential integrity; a housekeeping or cleanup function.
ARC options:
• ARCDFLT – 这是指明包含system-wide默认参数值的环境变量:
Example: SET ARCDFLT=C:TESTARCCONFIG.ARC
CONFIG.ARC文件将包含有效的运行时参数变量,例如:
SESSIONS=8
RESTARTLOG=C:TEMParcrlog1
• ARCENV – 这是定义任何有效的Teradata ARC运行是参数的环境变量.
Example: SET ARCENV=RESTARTLOG=C:TEMParcrlog2
• ARCENVX – same as ARCENV, except that ARCENVX has the highest override priority.
9.ARCHIVE Statement
10.ARCHIVE Examples
例一:
LOGON dbc/sysdba,dbapass;
ARCHIVE DATA TABLES (PD)
, ABORT
, RELEASE LOCK
, FILE = arc1_PD;
LOGOFF;
例二:
LOGON dbc/sysdba,dbapass;
ARCHIVE DATA TABLES (PD)
(EXCLUDE TABLES (dept_summary, phone_summary))
, ABORT
, RELEASE LOCK
, FILE = arc2_PD;
LOGOFF;
例三:
LOGON dbc/sysdba,dbapass;
ARCHIVE DATA TABLES (Sysdba) ALL
, ABORT , RELEASE LOCK
, FILE = arc3_Sys;
LOGOFF;
例四:
LOGON dbc/sysdba,dbapass;
ARCHIVE DATA TABLES
(Sysdba) ALL
(EXCLUDE TABLES (PD.dept_summary, PD.phone_summary))
, EXCLUDE (Demo), (Guest_Users) ALL, (Sandbox)
, ABORT , RELEASE LOCK
, FILE = arc4_Sys;
LOGOFF;
例五:
LOGON dbc/dbc,dbcpass;
ARCHIVE DATA TABLES (DBC) ALL
, ABORT, RELEASE LOCK
, FILE = arc5_DBC;
LOGOFF;
运行方式:
arcmain < archive2_pd.arc
11.Archiving Selected Partitions of PPI Table
V2R6.0起,可以对一个或多个分区执行全AMP备份:
• 对所选分区备份只局限于全AMP备份;
• Dictionary, cluster, and journal archives are not supported.
考虑因素:
• Archiving selected partitions operates on complete partitions within tables.
– Defining a partial partition means that the entire partition will be archived.
– A restore operation always deletes the selected partitions of the target table before restoring the rows that are stored in the archive.
• An archive or restore of selected partitions only places full-table locks. Locks on individual partitions are not supported.
• Re-collect table statistics after a restore of selected partitions because statistics are part of the table dictionary rows, which are not restored during a partition-level restore.
分区备份实例:
LOGON dbc/sysdba,dbapass;
ARCHIVE DATA TABLES
(TFACT.Sales_PPI) (PARTITIONS WHERE (!Sales_Date BETWEEN '2006-01-01' AND '2006-03-31'!))
, ABORT, RELEASE LOCK, FILE = arc6_PPI;
LOGOFF;
12.ANALYZE Statement
作用:分析备份到磁带中的数据,可帮助用户恢复备份的部分数据而不是全部,运行此命令不需要登录数据库
语法:
ANALYZE [ * | ALL | [ (Databasename) | (Dbname1) TO (Dbname2) ] [, ...]
[ , DISPLAY [ LONG] | , VALIDATE ]
, FILE = name ;
注意:
• The ANALYZE statement instructs the ARC utility to read an archive file and display information about its content.
• The LONG option displays all table, view, macro, trigger, and stored procedure names.
– If an archive file contains a selected partition archive of a table (V2R6), the bounding condition used to select the archived partitions is displayed.
• The VALIDATE option reads each record to check that each block on the archive file is readable.
• ANALYZE doesn’t require a LOGON or LOGOFF statement.
Example: analyze1_pd.arc (script name)
ANALYZE (PD), DISPLAY LONG, FILE = arc1_PD;
To execute: arcmain < analyze1_pd.arc
13.Types of Archives
• ARCHIVE DATA TABLES
– Fallback Tables, Views, Macros, Triggers, and Stored Procedures
– No fallback tables
– All AMP or cluster archive
• ARCHIVE DICTIONARY TABLES
– DD/D rows to complement cluster-level archive
• ARCHIVE NO FALLBACK TABLES
– Non-fallback tables
– Archives AMP data missed during previous all AMP or cluster-level archive
• ARCHIVE JOURNAL TABLES
– Journal Tables
ALL AMP Database ARCHIVE includes:
• Data rows from the tables in the specific database(s).
• Table structure information.
• All table, column, and index definitions.
• All views, macros, and triggers definitions.
• Stored procedures.
• Permanent journal information is not included.
ALL AMP Table ARCHIVE includes:
• Data rows from the table.
• All dictionary information for the table.
• All table, column, and index definitions.
Specific AMPs or Cluster ARCHIVE includes:
• Data rows from the table or tables within the specific database(s).
• No dictionary rows.
Dictionary ARCHIVE includes:
• Dictionary rows for the object being archived.(Tables: TVM, TVFields, Indexes, IndexNames.)
• Permanent journal information is not included.
说明:
因为分区备份不备数据字典信息,所以需要单独维护数据字典以恢复数据.
14.Archive Levels
除非用户指定特定的处理器或cluster备份,系统将执行全AMP级别的备份.
– 分区备份可以将一个或多个分区的数据备份到一个数据文件中;
– 分区备份作业可以并行运行也可以分开运行;
– 单AMP的恢复会更快;数据字典需要单独备份;
单AMP备份只能在此AMP已经恢复服务时完成非fallback表的备份操作;
15.Archive Options
• Release Lock
– Utility locks automatically released upon successful operation completion
• Indexes
– Restricted to all-AMP dumps
– Includes secondary indexes with archive
– Requires more time and media
• Abort
– Fails ALL AMP or cluster dumps AND provides error messages if:
> AMP vproc is off-line AND,
> Dumped objects include no fallback tables, OR
> Dumped objects include single-image journals
• Non empty Database(s)
– Excludes users/databases without tables, views, macros, triggers, or stored procedures from archive operation
• Use Group Read Lock
– Permits concurrent table archiving and transaction updates on locked rows
– Requires after-image journaling of table
16.Indexes Option
• ARCHIVE Operation
– If all AMPs are online, then all indexes are archived.
– If the table is fallback, then only unique secondary indexes are archived.
– No indexes are dumped.
• RESTORE Operation
– If all AMPs are online, then all indexes are restored.
– If the table is fallback, then only unique secondary indexes are restored.
17.Group Read Lock Option
作用:允许表在被修改的时候做备份操作(原因是此备份操作将数据修改日志一起做备份操作)。
限制条件:
• Rolling archive while you are using a table.
• Must have After-Image Journal defined.
• Only valid for an all-AMPs archive.
操作结果:
• All transactions will be included in the journal.
• Journal must be archived.
• Completed archive set includes data table archive and journal table archive.
18.Database DBC Archive
DBC备份主要有下列内容:
AccessRights Specification of all GRANTed rights
AccLogRuleTbl Stores access logging specifications
Accounts Lists all authorized account numbers
CollationTbl Defines MULTINATIONAL collation
DBase Definition of each DATABASE and USER
Hosts Character set default override rules
LogonRuleTbl User, host, password requirements
Next Internal table for generating TABLE and DATABASE identifiers
Owners Defines all databases owned by another
Parents Defines the parent/child relationship between databases
Profiles Defines Profiles(new with V2R5)
Roles Defines Roles(new with V2R5)
RoleGrants Contains Users and Roles granted to Roles (new with V2R5)
RCConfiguration Records the configuration for the RCEvents rows
RCEvent Records all archive and recovery events
RCMedia Records all removable media used in archive activities
19.Summary
• Archive and Recovery (ARC) is a command-line utility that performs three operations: archive, restore and recovery.
• For small systems, the optimum number of sessions for archive and recovery operations is:
– One per AMP vproc for archive
– Two per AMP vproc for recovery
• An archive operation can back up a single database or table, multiple databases or tables, or all databases.
• Available archive levels are all-AMP, specific AMP and cluster archives.
• The four types of archives are all-AMP database archive, all-AMP-table archive, specific-AMP or cluster archive and dictionary archive.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16723161/viewspace-1014771/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16723161/viewspace-1014771/