TimesTen命令—ttBackup

ttBackup使用说明

c:\>ttBackup  -h
Usage:
  ttBackup [-h | -help | -?]
  ttBackup [-V | -version]
  ttBackup -dir <directory> [-type <backupType>] [-fname <fileprefix>]
           [-force] {<DSN> | [-connstr] <connStr>}

options:
  -h | -help | -?      Prints this message and exits.
  -V | -version        Prints the release number and exits.
  -type  <backupType>  The type of backup to be performed.  Supported backup
                       types are given below.  Default is "fileFull".
  -dir   <directory>   The directory in which to store the backup files.
  -fname <fileprefix>  The file prefix for the backup files in the backup
                       directory.  Default is the base filename portion of the
                       DataStore parameter of the data store to be backed up.
  -force               Destroy existing backup files before performing backup
                       (full file-based backups only).
  <DSN>, <connStr>     The DSN or ODBC connection string of the data store to
                       be backed up.

Backup types are:
  fileFull         Full backup to the specified backup directory.  The
                   resulting backup is not enabled for incremental backup.
                   This is the default backup type.
  fileFullEnable   Full backup to the specified backup directory.  The
                   resulting backup is enabled for incremental backup.
  fileIncremental  Incremental backup.  Augments pre-existing backup given
                   by '-dir' and optionally '-fname'.
  fileIncrOrFull   If incremental backup is possible, then behaves like
                   "fileIncremental"; else behaves like "fileFullEnable".

NOTE: For above types, '-dir' is required; '-fname' and '-force' are optional.

  streamFull       Full backup to the standard output.  '-dir', '-fname' and
                   '-force' are ignored.
  incrementalStop  Does not perform a backup. Disables an incremental-enabled
                   backup.  '-dir' is required; '-fname' is optional; '-force'
                   is ignored.

ttBackupStatus函数说

全备和增量备份

--全备并启动增量备份功能
c:\>ttbackup -dir E:\oracle\timesten\tt_back\full  -type fileFullEnable    -fname xifenfei01_ -force my_ttdb
Backup started ...
Backup complete

Command> call ttBackupStatus ();
< 2, 0, 1, 2012-02-22 23:13:18.125000, 2012-02-22 23:13:18.505000, 0, 11821056,0, 5896 >
1 row found.

--增量备份
c:\>ttbackup -dir E:\oracle\timesten\tt_back\full  -type fileIncremental  -fname xifenfei01_  -force my_ttdb
Backup started ...
Backup complete

Command> call ttBackupStatus ();
< 2, 0, 0, 2012-02-22 23:19:24.453000, 2012-02-22 23:19:24.702000, 0, 11825152,0, 3960 >
1 row found.

--执行结果
E:\oracle\timesten\tt_back\full>dir
 驱动器 E 中的卷没有标签。
 卷的序列号是 38D0-2A35

 E:\oracle\timesten\tt_back\full 的目录

2012/02/22  23:19    <DIR>          .
2012/02/22  23:19    <DIR>          ..
2012/02/22  23:13        21,119,936 xifenfei01_.0.bac
2012/02/22  23:19        11,943,936 xifenfei01_.0.bac0
2012/02/22  23:19               696 xifenfei01_.sta
               3 个文件     33,064,568 字节
               2 个目录  9,432,420,352 可用字节


--补充说明
1.需要使用fileFullEnable启动增量备份
2.增量备份需要指定dir和fname和全备时一致

fileIncrOrFull测试

c:\>ttbackup -dir E:\oracle\timesten\tt_back\full  -type fileIncrOrFull    -force  -fname xifenfei01_  my_ttdb
Backup started ...
Backup complete

Command> call ttBackupStatus ();
< 2, 0, 0, 2012-02-22 23:47:57.997000, 2012-02-22 23:47:58.174000, 0, 11880448,0, 5740 >
1 row found.

c:\>ttbackup -dir E:\oracle\timesten\tt_back\full  -type fileIncrOrFull    -force  -fname xifenfei00_  my_ttdb
Backup started ...
Backup complete

Command> call ttBackupStatus ();
< 2, 0, 1, 2012-02-22 23:53:37.364000, 2012-02-22 23:53:37.753000, 0, 11886592,0, 5076 >
1 row found.

--补充说明
在有启用增量备份(启用增量的全备或者增量备份本身)的基础上,会自动进行增量备份;
在无启用增量备份(没有备份或者备份没有启用增量备份)的基础上,如果无则会进行全备

streamFull测试

c:\>ttBackup -type streamFull my_ttdb>E:\oracle\timesten\tt_back\full\xifenfei.tream
Backup started ...
Backup complete

Command> call ttBackupStatus ();
< 2, 1, 1, 2012-02-22 23:38:52.480000, 2012-02-22 23:38:52.606000, 0, 11874304,0, 4384 >
1 row found.

c:\>dir E:\oracle\timesten\tt_back\full\xifenfei.*
 驱动器 E 中的卷没有标签。
 卷的序列号是 38D0-2A35

 E:\oracle\timesten\tt_back\full 的目录

2012/02/22  23:38        33,064,596 xifenfei.tream
               1 个文件     33,064,596 字节
               0 个目录  9,399,287,808 可用字节

--补充说明
在linux/unix环境中,执行流备份可以结合dd命令
如:ttBackup -type streamFull FastIns | dd bs=64k of=/dev/rmt0

statspack报告中逻辑读为负值

最近遇到两次在sp报告中,显示逻辑读为负数。进行分析情况如下:
一台是运营商的crm库(aix 5.3+9.2.0.8+rac)

--系统版本
[zwq_crm2:/home/crm_oraeye]oslevel -s 
5300-08-07-0920

--数据库版本
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

--数据库启动时间
SQL> SELECT A.INST_ID, TO_CHAR (startup_time, 'yyyy-mm-dd hh24:mi:ss')
  2   FROM gv$instance a;

   INST_ID TO_CHAR(STARTUP_TIM
---------- -------------------
         2 2011-02-19 03:33:49
         1 2011-02-19 03:56:17

--异常的逻辑读统计数据
SQL>   SELECT b.snap_id,
  2           TO_CHAR (B.SNAP_TIME, 'yyyy-mm-dd hh24:mi:ss'),
  3           TO_CHAR (A.VALUE, '999,999,999,999,999')
  4      FROM stats$sysstat a, stats$snapshot b
  5     WHERE     a.NAME = 'session logical reads'
  6           AND a.instance_number = 2
  7           AND a.snap_id = b.snap_id
  8           AND A.SNAP_ID >=47913
  9           AND A.SNAP_ID <=47920
 10  ORDER BY a.snap_id;

   SNAP_ID TO_CHAR(B.SNAP_TIME TO_CHAR(A.VALUE,'999
---------- ------------------- --------------------
   47913	2012-02-10 20:00	4,764,705,272,783
   47914	2012-02-10 21:00	4,761,539,910,574
   47915	2012-02-10 22:00	4,749,529,436,021
   47916	2012-02-10 23:00	4,745,952,040,146 
   47917	2012-02-11 00:00	4,738,052,256,634 
   47918	2012-02-11 01:00	4,738,894,245,521 
   47919	2012-02-11 02:00	4,739,587,095,184 
   47920	2012-02-11 03:00	4,740,409,262,259 

另一台是运营商的开停机库(aix 5.3+9.2.0.8+rac)

--系统版本
[zwq_offon2:/home/oraeye]oslevel -s 
5300-08-07-0920

--数据库版本
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

--数据库启动时间
SQL> SELECT A.INST_ID, TO_CHAR (startup_time, 'yyyy-mm-dd hh24:mi:ss')
  2    FROM gv$instance a;

   INST_ID TO_CHAR(STARTUP_TIM
---------- -------------------
         2 2010-01-23 19:16:46
         1 2010-01-23 19:15:09

--异常的逻辑读统计数据
SQL>   SELECT b.snap_id,
  2           TO_CHAR (B.SNAP_TIME, 'yyyy-mm-dd hh24:mi:ss'),
  3           TO_CHAR (A.VALUE, '999,999,999,999,999')
  4      FROM stats$sysstat a, stats$snapshot b
  5     WHERE     a.NAME = 'session logical reads'
  6           AND a.instance_number = 2
  7           AND a.snap_id = b.snap_id
  8           AND A.SNAP_ID IN ('38271', '38272', '38339', '38340')
  9  ORDER BY a.snap_id;

   SNAP_ID TO_CHAR(B.SNAP_TIME TO_CHAR(A.VALUE,'999
---------- ------------------- --------------------
     38271 2012-01-31 19:00:05    4,269,858,122,434
     38272 2012-01-31 20:00:02    4,266,001,522,867
     38339 2012-02-02 09:00:02    4,275,651,080,526
     38340 2012-02-02 10:00:02    4,250,263,107,466

这两个数据库中都出现了在数据库没有重启的情况下stats$sysstat.value的值出现波动情况,而且都是在40万亿以上的时候。不知道是特点的版本巧合(特定的数据库版本,特定的操作系统版本),还是Oracle未公布bug。出现这样的情况,目前只能通过重启实例来使得statspack捕获到的逻辑读值变小,从而避免这样的波动,从而解决sp中出现逻辑读为负数的情况。

找出11g undo 回滚段名称

当数据库启动的时报undo相关异常,很多情况下我们不得不使用隐含参数来处理(_offline_rollback_segments和_corrupted_rollback_segments),而这个就需要明确异常的undo回滚段名称。在11g中,undo回滚段的名称发生了改变,在11g之前的版本中,回滚段名称是”_SYSTEMn$”之类,而到了11g回滚段的名称变为了”_SYSTEMn_时间戳$”,因为时间戳我们不知道,所以我们不能通过n的值,来确定回滚段的名称,从而也就不能很明确的使用_offline_rollback_segments和_corrupted_rollback_segments来标明异常回滚段。既然回滚段的名称我们可以通过dba_rollback_segs视图来查询,那么我们可以根据这个视图找到对应的回滚段是存储在哪张基表中,下面为dba_rollback_segs视图对应的sql语句

select un.name, decode(un.user#,1,'PUBLIC','SYS'),
       ts.name, un.us#, f.file#, un.block#,
       s.iniexts * ts.blocksize,
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
                                      s.extsize * ts.blocksize),
       s.minexts, s.maxexts,
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
                                      s.extpct),
       decode(un.status$, 2, 'OFFLINE', 3, 'ONLINE',
                          4, 'UNDEFINED', 5, 'NEEDS RECOVERY',
                          6, 'PARTLY AVAILABLE', 'UNDEFINED'),
       decode(un.inst#, 0, NULL, un.inst#), un.file#
from sys.undo$ un, sys.seg$ s, sys.ts$ ts, sys.file$ f
where un.status$ != 1
  and un.ts# = s.ts#
  and un.file# = s.file#
  and un.block# = s.block#
  and s.type# in (1, 10)
  and s.ts# = ts.ts#
  and un.ts# = f.ts#
  and un.file# = f.relfile#

通过观察,我们知道回滚段信息是存储在sys.undo$中(name字段表示回滚段名称,status$字段表示回滚段状态[1:DELETE,2:OFFLINE,3:ONLINE,4:UNDEFINED,5:NEEDS RECOVERY,6:PARTLY AVAILABLE,其他表示:UNDEFINED]),通过这个视图我们需要找的到status$为5所对应name。
找这些值较简易的方法就是通过odu抽取sys.undo$表中数据,然后在其他库上还原,然后通过sql语句查询
1.设置odu参数(config.txt)
output_format dmp,其他参数根据你的实际情况设置

2.填写system表空间对应的数据文件(control.txt)

[oracle@localhost odu]$ more control.txt 
#ts #fno   #rfno     filename                                          block_size
1     1      1       /u01/oradata/first/system01.dbf

3.odu导出数据

ODU> unload table sys.undo$

Unloading table: UNDO$,object ID: 15
Unloading segment,storage(Obj#=15 DataObj#=15 TS#=0 File#=1 Block#=224 Cluster=0)
21 rows unloaded

4.导入至新库

imp chf/xifenfei file=C:\Users\XIFENFEI\Downloads\SYS_UNDO$.dmp FROMUSER=SYS TOUSER=CHF

5.查询需要处理的回滚段

--数据库版本10g及其以上版本
set pagesize 0 feedback off verify off heading off echo off linesize 1000
select WMSYS.WM_CONCAT(name) from UNDO$ where status$=5;

--数据库版本9i及其以下版本(自己拼接)
set pagesize 0 feedback off verify off heading off echo off 
select name from UNDO$ where status$=5;

现在已经找出来了需要处理的回滚段,其他数据库恢复步骤与以前数据库相同。

ARCn: Media recovery disabled原因分析

1.数据库版本

SQL> select * from v$version;

BANNER
-------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

2.alert日志出现类此记录

Fri Feb 10 05:16:55 2012
ARC1: Media recovery disabled
Fri Feb 10 05:17:23 2012
ARC0: Media recovery disabled
Fri Feb 10 05:17:23 2012
ARC1: Media recovery disabled
Fri Feb 10 05:18:23 2012
ARC1: Media recovery disabled
Fri Feb 10 05:19:12 2012
Thread 1 advanced to log sequence 24
  Current log# 1 seq# 24 mem# 0: /tmp/recover/redo01.log
Fri Feb 10 05:19:12 2012
ARC0: Media recovery disabled
Fri Feb 10 05:19:23 2012
ARC1: Media recovery disabled
Fri Feb 10 05:20:23 2012
ARC1: Media recovery disabled
Fri Feb 10 05:21:23 2012
ARC1: Media recovery disabled

3.数据库归档信息

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Enabled
Archive destination            /tmp/recover
Oldest online log sequence     25
Current log sequence           27

SQL> show parameter log_archive_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
log_archive_start                    boolean     TRUE

从这里可以看出,数据库处于非归档模式,但是log_archive_start=true,这样会导致归档进程自动启动(ARCn),因为数据库设置为非归档模式,从而导致该进程启动后,又自己关闭

4.解决方法

SQL> alter system set log_archive_start=false scope=spfile;

System altered.

SQL> startup force      
ORACLE instance started.

Total System Global Area  202445176 bytes
Fixed Size                   450936 bytes
Variable Size             167772160 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> show parameter log_archive_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
log_archive_start                    boolean     FALSE
SQL> ARCHIVE LOG LIST;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /tmp/recover
Oldest online log sequence     26
Current log sequence           28

ALERT中无类此记录出现