Monthly Archives: 二月 2011

SQL Server恢复模式

Posted on by

一、查询现有数据库恢复模式
1、直接查看
1).展开“数据库”，然后根据数据库的不同，选择用户数据库，或展开“系统数据库”，再选择系统数据库。
2).右键单击该数据库，再单击“属性”，这将打开“数据库属性”对话框。
3).在“选择页”窗格中，单击“选项”。
4).当前恢复模式显示在“恢复模式”列表框中。
5).也可以从列表中选择不同的模式来更改恢复模式。可以选择“完整”、“大容量日志”或“简单”。

2、通过sql语句查看 

--sql server 2000及其以前版本
SELECT name, (SELECT DATABASEPROPERTYEX(name, 'RECOVERY')) RecoveryModel FROM master.sys.databases ORDER BY name

--sql server 2005及其以后版本
SELECT name, recovery_model_desc FROM master.sys.databases ORDER BY name

二、三种恢复模式比较
1、简单恢复模式
特点：无日志备份。自动回收日志空间以减少空间需求，实际上不再需要管理事务日志空间。
工作丢失的风险 ：最新备份之后的更改不受保护。在发生灾难时，这些更改必须重做。
能否恢复到时点：只能恢复到备份的结尾
降低工作丢失风险：不影响备份管理的前提下时常备份，以免丢失大量数据。
适用范围(符合下列所有要求)：
1.不需要故障点恢复。如果数据库丢失或损坏，则会丢失自上一次备份到故障发生之间的所有更新，但您愿意接受这个损失。
2.您愿意承担丢失日志中某些数据的风险。
3.您不希望备份和还原事务日志，希望只依靠完整备份和差异备份。

2、完整恢复模式
特点：需要日志备份。数据文件丢失或损坏不会导致丢失工作。可以恢复到任意时点（例如应用程序或用户错误之前）。
工作丢失的风险:正常情况下没有。如果日志尾部损坏，则必须重做自最新日志备份之后所做的更改。
能否恢复到时点：果备份在接近特定的时点完成，则可以恢复到该时点。
时点恢复：出现故障后，可以尝试备份“日志尾部”（尚未备份的日志）。如果结尾日志备份成功，则可以通过将数据库还原到故障点来避免任何工作丢失。
缺点：使用日志备份的缺点是它们需要使用存储空间并会增加还原时间和复杂性。
一般的备份策略：
1.首先完整备份数据库以及日志备份.
2.在日志备份后的某个时间,数据库发生错误.接下来 先备份活动日志
3.然后还原完整数据库备份和日志备份,但是不恢复数据库；
4.还原并恢复结尾日志备份。这样就完成了恢复待故障点,恢复了所有数据.
降低工作丢失风险:建议经常执行日志备份，以将工作丢失的风险限定在业务要求所允许的范围内。
适用范围(符合下列任一要求)：
1.您必须能够恢复所有数据
2.数据库包含多个文件组，并且您希望逐段还原读/写辅助文件组（以及可选地还原只读文件组）。
3.您必须能够恢复到故障点
4.您希望可以还原单个页
5.您愿意承担事务日志备份的管理开销。

3、大容量日志会恢复
特点：需要日志备份。是完整恢复模式的附加模式，允许执行高性能的大容量复制操作。通过使用最小方式记录大多数大容量操作，减少日志空间使用量。
工作丢失的风险:如果在最新日志备份后发生日志损坏或执行大容量日志记录操作，则必须重做自该上次备份之后所做的更改,否则不丢失任何工作。
能否恢复到时点：可以恢复到任何备份的结尾。不支持时点恢复。
切换到该模式的必要性：对于某些大规模大容量操作（如大容量导入或索引创建）,暂时切换到大容量日志恢复模式可提高性能并减少日志空间使用量。仍需要日志备份。
何时使用大容量日志恢复模式：仅在运行大规模大容量操作期间以及在不需要数据库的时点恢复时使用该模式。

三、修改当前数据库恢复模式

--简单恢复模型:
USE master;
ALTER DATABASE dbname SET RECOVERY SIMPLE

--完整恢复模型:
USE master;
ALTER DATABASE dbname  SET RECOVERY FULL

--批量日志恢复模型:
USE master;
ALTER DATABASE dbname  SET RECOVERY BULK_LOGGED

goldengate同步sql server to oracle

Posted on by

准备工作，在sql server机器上建立odbc连接
一、初始化加载数据
1、source端
1)添加extract进程

ADD EXTRACT einito, SOURCEISTABLE
edit param einito
--以下添加到einito.prm文件中
EXTRACT einito
SOURCEDB mssql_test
RMTHOST 127.0.0.1, MGRPORT 7815
RMTTASK REPLICAT, GROUP rinitm
TABLE dbo.t1;

2)生成defgen文件

edit params defgen
---以下为defgen.prm中内容
defsfile F:\ogg\mssql\dirdef\t1.def
sourcedb mssql_test
table dbo.t1;

--退出ggsci(ogg安装目录dos下)
exit
defgen paramfile F:\ogg\mssql\dirprm\defgen.prm

2、target端
1)replicat 进程

ADD REPLICAT rinitm, SPECIALRUN
edit params rinitm
--以下内容在rinitm.prm文件中
replicat rinitm
sourcedefs F:\ogg\oracle\dirdef\t1.def
SETENV (NLS_LANG =AMERICAN_AMERICA.ZHS16GBK)
USERID chf, PASSWORD xifenfei
DISCARDFILE F:\ogg\oracle\dirrpt\RINItm.dsc, append
MAP "dbo.t1", TARGET CHF.T1_1;

二、数据同步
1、source端
1)添加附件日志

dblogin sourcedb mssql_test
add trandata dbo.t1

2)摄取进程（extract）

add extract extm,tranlog,begin now
ADD EXTTRAIL F:\ogg\mssql\dirdat\ms, EXTRACT EXTM
edit param extm
--以下为extm.prm内容
extract extm
SOURCEDB mssql_test
exttrail F:\ogg\mssql\dirdat\ms
dynamicresolution
gettruncates
tranlogoptions managesecondarytruncationpoint
TABLE dbo.t1;

3)传递进程（data pump extract ）

ADD EXTRACT pump1, EXTTRAILSOURCE F:\ogg\mssql\dirdat\ms, BEGIN now
add rmttrail F:\ogg\oracle\dirdat\or extract pump1
edit params pump1
--以下为pump1.prm内容
extract pump1
SOURCEDB mssql_test  --需要，不然不能获得数据
rmthost 127.0.0.1, mgrport 7815
rmttrail F:\ogg\oracle\dirdat\or
PASSTHRU
gettruncates
TABLE dbo.t1;

2、target端
1)设置检查点表

edit params ./GLOBALS
--下面一句为GLOBALS文件中内容
CHECKPOINTTABLE ogg.chkpoint

dblogin userid ogg,password xifenfei 
ADD CHECKPOINTTABLE ogg.chkpoint

2)replicat进程

add replicat repl exttrail F:\ogg\oracle\dirdat\or,begin now,checkpointtable ogg.chkpoint 
edit  params repl
--以下为repl.prm中内容
replicat repl          
SETENV (NLS_LANG =AMERICAN_AMERICA.ZHS16GBK)                     
userid ogg,password xifenfei                    
sourcedefs F:\ogg\oracle\dirdef\t1.def                         
reperror default,discard                      
discardfile F:\ogg\oracle\dirtmp\repsz.dsc,append,megabytes 100   
gettruncates                                                      
MAP "dbo.t1", TARGET CHF.T1_1;

note：
因为defgen中的表名为小写，所以在replicat相关进程中，map表需要使用双引号小写

oracle修改表增加列删除列修改列

Posted on by

1.增加列

ALTER TABLE table_name ADD( column datatype [DEFAULT EXPR][,column datatype…]);

例如:

SQL>ALTER TABLE emp01 ADD eno NUMBER(4);

2.修改列定义

例如:

SQL>ALTER TABLE emp01 MODIFY job VARCHAR2(15)

2 DEFAULT ‘CLERK’

3.删除列

例如:

SQL> ALTER TABLE emp01 DROP COLUMN dno;

4.修改列名

例如:

SQL>ALTER TABLE emp01 RENAME COLUMN eno TO empno;

5.修改表名

例如:

SQL>RENAME emp01 TO employee;

6.增加注释

例如:

SQL>COMMENT ON TABLE employee IS ‘存放雇员信息’;

goldengate 异常处理

Posted on by

异常处理一（异常表通用型）
新建异常处理表

create table ogg.exception_log
    ( replicat_name varchar2(10),
      table_name varchar2(100),
      errno number,
      dberrmsg varchar2(4000),
      optype varchar2(20),
      errtype varchar2(20),
      logrba number,
      logposition number,
     committimestamp timestamp,
     primary key(logrba,logposition,committimestamp)
);

REPLICAT添加异常处理

REPERROR (DEFAULT, EXCEPTION)
REPERROR (DEFAULT2,discard)---abend根据需求
map chf.a_t_1, target chf.a_t_1;
map chf.a_t_1, target ogg.exception_log,
EXCEPTIONSONLY,
INSERTALLRECORDS,
COLMAP (   replicat_name = "repl"
, table_name = @GETENV ("GGHEADER", "TABLENAME")
, errno = @GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = @GETENV ("LASTERR", "DBERRMSG")
, optype = @GETENV ("LASTERR", "OPTYPE")
, errtype = @GETENV ("LASTERR", "ERRTYPE")
, logrba = @GETENV ("GGHEADER", "LOGRBA")
, logposition = @GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP"));
--实例中只处理chf.a_t_1表

异常处理二(异常表需要定制)
新建表(正常表和异常表)

--正常表
create table fei_1_1(id number , name varchar2(1000));
--异常表
create table ogg.exception_fei_1
    ( id number,
      name varchar2(1000),
      table_name varchar2(100),
      errno number,
      dberrmsg varchar2(4000),
      optype varchar2(20),
      errtype varchar2(20),
      logrba number,
      logposition number,
     committimestamp timestamp,
     primary key(logrba,logposition,committimestamp)
);

异常处理程序

map chf.fei_1, target chf.fei_1_1;
map chf.fei_1, target ogg.exception_fei_1,
EXCEPTIONSONLY,
INSERTALLRECORDS,
COLMAP ( USEDEFAULTS
, table_name = @GETENV ("GGHEADER", "TABLENAME")
, errno = @GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = @GETENV ("LASTERR", "DBERRMSG")
, optype = @GETENV ("LASTERR", "OPTYPE")
, errtype = @GETENV ("LASTERR", "ERRTYPE")
, logrba = @GETENV ("GGHEADER", "LOGRBA")
, logposition = @GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP"));

异常处理三（通配符MAPEXCEPTION）
新建异常表

create table ogg.exception_fei_all
    ( replicat_name varchar2(10),
      table_name varchar2(100),
      errno number,
      dberrmsg varchar2(4000),
      optype varchar2(20),
      errtype varchar2(20),
      logrba number,
      logposition number,
     committimestamp timestamp,
     primary key(logrba,logposition,committimestamp)
);

异常处理程序

MAP chf.fei_*, TARGET chf.*,
MAPEXCEPTION (TARGET ogg.exception_fei_all,
COLMAP (   replicat_name = "repl"
, table_name = @GETENV ("GGHEADER", "TABLENAME")
, errno = @GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = @GETENV ("LASTERR", "DBERRMSG")
, optype = @GETENV ("LASTERR", "OPTYPE")
, errtype = @GETENV ("LASTERR", "ERRTYPE")
, logrba = @GETENV ("GGHEADER", "LOGRBA")
, logposition = @GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP")));

处理说明：
REPERROR参数用以控制Replicat进程如何响应映射过程中发生的错误
DEFAULT参数代表一种全局错误类型，即除去所有已明确指定的错误外的一切错误
DEFAULT2参数代表当DEFAULT错误以Exception方式响应时，所有MAP映射中未定义Exception部分出现的所有错误
EXCEPTIONSONLY只能用于确定表的异常处理
MAPEXCEPTION可以用于通配符的表异常处理

oracle 中如何定位重要(消耗资源多)的SQL

Posted on by

1、查看值得怀疑的SQL

select substr(to_char(s.pct,'99.00'),2)||'%'load,
       s.executions executes,
       p.sql_text
from(select address,
               disk_reads,
               executions,
               pct,
               rank()over(order by disk_reads desc) ranking
         from(select address,
                       disk_reads,
                       executions,
                      100*ratio_to_report(disk_reads)over() pct
                 from sys.v_$sql
                where command_type!=47)
        where disk_reads>50*executions) s,
       sys.v_$sqltext p
where s.ranking<=5
  and p.address=s.address
order by 1, s.address, p.piece;

2、查看消耗内存多的sql

select b.username,
       a. buffer_gets,
       a.executions,
       a.disk_reads / decode(a.executions, 0, 1, a.executions),
       a.sql_text SQL
  from v$sqlarea a, dba_users b
 where a.parsing_user_id = b.user_id
   and a.disk_reads > 10000
 order by disk_reads desc;

3、查看逻辑读多的SQL

select*
from(select buffer_gets, sql_text
         from v$sqlarea
        where buffer_gets>500000
        order by buffer_gets desc)
where rownum<=30;

4、查看执行次数多的SQL

select sql_text, executions
  from (select sql_text, executions from v$sqlarea order by executions desc)
 where rownum < 81;

5、查看读硬盘多的SQL

select sql_text, disk_reads
from(select sql_text, disk_reads from v$sqlarea order by disk_reads desc)
where rownum<21;

6、查看排序多的SQL

select sql_text, sorts
from(select sql_text, sorts from v$sqlarea order by sorts desc)
where rownum<21;

7、分析的次数太多，执行的次数太少，要用绑变量的方法来写sql

select substr(sql_text, 1, 80) "sql", count(*), sum(executions) "totexecs"
  from v$sqlarea
 where executions < 5
 group by substr(sql_text, 1, 80)
having count(*) > 30
 order by 2;