TimesTen数据库的备份和恢复

时间：2023-12-17 20:15:44

建立不支持增量备份的全备份

做一个全备份，fileFull为不支持增量的全备份

$ ttbackup -type fileFull  -dir /tmp/backupdir sampledb_1122
（ttbackup -type fileFull  -dir 指定存放目录  要备份的表明 ）

恢复到原数据库

$ ttrestore -dir /tmp/backupdir sampledb_1122

Restore started ...

Restore failed:

Error 12133: TT12133: Data store file already exists -- file "restore.c", lineno 1006, procedure "doRestore"

恢复时原数据库必须不存在

$ ttdestroy sampledb_1122

$ ttrestore -dir /tmp/backupdir sampledb_1122

Restore started ...

Restore complete

$ ttisql -v1 sampledb_1122

Command> select * from test;

< 1, 2016-04-05 05:35:53.000000 >

恢复到另一数据库

**必须用-fname指定文件前缀，否则以sampledb1_1122为前缀**

$ ttrestore -fname sampledb_1122 -dir /tmp/backupdir/ sampledb1_1122

Restore started ...

ttRestore: SQL Error: Failed to connect to data store

*** [TimesTen][TimesTen 11.2.2.6.2 ODBC Driver][TimesTen]TT6200: New value for permanent data partition size ignored since smaller than current value of 40 megabytes -- file "db.c", lineno 10239, procedure "sbDbConnect"

*** ODBC Error = 01000, TimesTen Error = 6200

*** [TimesTen][TimesTen 11.2.2.6.2 ODBC Driver]Invalid value (AL32UTF8) for DatabaseCharacterSet connection attribute -- value must be the same as the current data store value (US7ASCII)

*** ODBC Error = 08004, TimesTen Error = 6228

ttRestore: Failed connecting to data store after restore

虽然有很多警告，不过备份还是成功了，警告为：

- 恢复库的字符集不一致，忽略。因为字符集必须一致

- 恢复库的PermSize较备份库小，忽略. 如果较大则没有问题

$ ttisql -v1 sampledb1_1122

Warning  6226: Ignoring value requested for first connection attribute 'PermSize' -- value currently in use: 40, requested value: 32

Command> select * from test;

< 1, 2016-04-05 05:35:53.000000 >

备份中到底包含哪些内容

备份的内容包含三部分,最近一次的checkpoint文件，transaction log文件和.sta元数据文件

$ ls -l /tmp/backupdir

total 43880

-rw------- 1 oracle oracle 28834840 Apr  5 06:27 sampledb_1122.0.bac

-rw------- 1 oracle oracle 16039936 Apr  5 06:27 sampledb_1122.0.bac0

-rw------- 1 oracle oracle      720 Apr  5 06:27 sampledb_1122.sta

从元数据文件中可得到备份的DataStore的信息

$ strings sampledb_1122.sta

/home/oracle/TimesTen/tt1122/info/DemoDataStore/sampledb_1122

tt1122

然后可以与DataStore中的checkpoint和日志文件核对

$ ll /home/oracle/TimesTen/tt1122/info/DemoDataStore/sampledb_1122*

-rw-rw---- 1 oracle oracle 28834840 Apr  5 06:27 /home/oracle/TimesTen/tt1122/info/DemoDataStore/sampledb_1122.ds0

-rw-rw---- 1 oracle oracle 28834840 Apr  5 06:27 /home/oracle/TimesTen/tt1122/info/DemoDataStore/sampledb_1122.ds1

-rw-rw---- 1 oracle oracle 16039936 Apr  5 06:27 /home/oracle/TimesTen/tt1122/info/DemoDataStore/sampledb_1122.log0

-rw-rw---- 1 oracle oracle 67108864 Apr  5 06:21 /home/oracle/TimesTen/tt1122/info/DemoDataStore/sampledb_1122.res0

-rw-rw---- 1 oracle oracle 67108864 Apr  5 06:21 /home/oracle/TimesTen/tt1122/info/DemoDataStore/sampledb_1122.res1

-rw-rw---- 1 oracle oracle 67108864 Apr  5 06:21 /home/oracle/TimesTen/tt1122/info/DemoDataStore/sampledb_1122.res2

使用流式全备份和恢复

流式备份的特点是输出到stdout，因此可以定向到文件或磁带等，或在线压缩如： 
备份：ttbackup -type streamfull sampledb_1122|gzip > sampledb1122.ttb.gz 
恢复：gunzip -c sampledb1122.ttb.gz | ttrestore -i sampledb1122

$ ttbackup -type streamfull sampledb_1122 > sampledb_1122.ttb

Backup started ...

Backup complete

$ ll sampledb_1122.ttb

-rw-rw-r-- 1 oracle oracle 44842736 Apr  5 06:18 sampledb_1122.ttb

$ ttdestroy sampledb_1122

$ ttrestore -i sampledb_1122 < ./sampledb_1122.ttb

Restore started ...

Restore complete

$ ttisql -v1 sampledb_1122

Command> select * from test;

< 1, 2016-04-05 05:35:53.000000 >

增量备份和恢复

ttbulkcp结合ttschema进行逻辑备份和恢复

ttbulkcp可以将表中的数据导出成ASCII文件，反向亦可。 
但每次只能导出一个表，如果批量导需要自己写批处理文件，如下面示意：

$ ttisql -v1 -e "tables;exit" plsqldb|while read tab; do

> echo ttbulkcp $tab

> done;

ttbulkcp ORACLE.COUNTRIES

ttbulkcp ORACLE.DEPARTMENTS

ttbulkcp ORACLE.EMPLOYEES

ttbulkcp ORACLE.JOBS

ttbulkcp ORACLE.JOB_HISTORY

ttbulkcp ORACLE.LOCATIONS

ttbulkcp ORACLE.REGIONS

ttbulkcp ORACLE.T1

ttbulkcp ORACLE.TT_TEST

导出：

ttbulkcp -o 导出后的文件名  表的明=========>整张表导出
$ ttbulkcp -o plsqldb employees employees.dump107/107 rows copied

$ more employees.dump

##ttBulkCp:DATEMODE=TIMESTAMP

#

# ORACLE.EMPLOYEES, 11 columns, dumped Tue Apr  5 18:35:46 2016

# columns:

#      1. EMPLOYEE_ID  NUMBER(6)

#      2. FIRST_NAME   VARCHAR2(20 BYTE)

#      3. LAST_NAME    VARCHAR2(25 BYTE)

#      4. EMAIL        VARCHAR2(25 BYTE)

#      5. PHONE_NUMBER VARCHAR2(20 BYTE)

#      6. HIRE_DATE    DATE

#      7. JOB_ID       VARCHAR2(10 BYTE)

#      8. SALARY       NUMBER(8,2)

#      9. COMMISSION_PCT NUMBER(2,2)

#     10. MANAGER_ID   NUMBER(6)

#     11. DEPARTMENT_ID NUMBER(4)

# end

#

100,"Steven","King","SKING","515.123.4567",1987-06-17 00:00:00,"AD_PRES",24000,N

ULL,NULL,90

101,"Neena","Kochhar","NKOCHHAR","515.123.4568",1989-09-21 00:00:00,"AD_VP",1700

0,NULL,100,90

导入：

ttbulkcp -i 导出后的文件名  表的明=========>整张表导出（导入导出就是参数不一样，一个-i，一个-o）
$ ttbulkcp -i -connstr "dsn=sampledb_1122;uid=tthr" employees employees.dump

Enter password for 'tthr':

ttBulkCp: Error received while retrieving information about table employees -- Table not found.

由于目标库的表结构还不存在，这时我们可以用ttschema导出表定义

$ ttschema plsqldb oracle.employees > employees.sql

Warning: objects may not be printed in an order that can satisfy foreign key reference constraints or other dependencies

$ cat employees.sql

-- Database is in Oracle type mode

Warning: objects may not be printed in an order that can satisfy foreign key reference constraints or other dependencies

create table "ORACLE".EMPLOYEES (

        EMPLOYEE_ID    NUMBER(6) NOT NULL,

        FIRST_NAME     VARCHAR2(20 BYTE) INLINE,

        LAST_NAME      VARCHAR2(25 BYTE) INLINE NOT NULL,

        EMAIL          VARCHAR2(25 BYTE) INLINE NOT NULL UNIQUE,

        PHONE_NUMBER   VARCHAR2(20 BYTE) INLINE,

        HIRE_DATE      DATE NOT NULL,

        JOB_ID         VARCHAR2(10 BYTE) INLINE NOT NULL,

        SALARY         NUMBER(8,2),

        COMMISSION_PCT NUMBER(2,2),

        MANAGER_ID     NUMBER(6),

        DEPARTMENT_ID  NUMBER(4),

    primary key (EMPLOYEE_ID)

    );

    create index "ORACLE".EMP_MANAGER_IX on "ORACLE".EMPLOYEES (MANAGER_ID);

    create index "ORACLE".EMP_NAME_IX on "ORACLE".EMPLOYEES

        (LAST_NAME, FIRST_NAME);

不过我们看到ttschema导出的文件中包含了用户名，如果需要在另一个用户如tthr的schema下建表，则需要使用vi替换一下

(1,$ s/ORACLE/TTHR/g)

$ ttisql -connstr "dsn=sampledb_1122;uid=tthr;pwd=tthr" -f employees.sql

$ ttbulkcp -i -connstr "dsn=sampledb_1122;uid=tthr" employees employees.dump

Enter password for 'tthr': 

employees.dump:

    107 rows inserted

    107 rows total

借鉴：https://blog.csdn.net/stevensxiao/article/details/51067958

相关文章