月归档：六月 2012

关于DBMS_SCHEDULER基础

发表于 2012 年 6 月 19 日由惜分飞

长期以来,一直对DBMS_SCHEDULER包比较模糊,今天抽一点时间,通过一点试验,理清自己的思路,分清楚各个函数大概作用.不至于在以后使用该包的时候一片空白.
1.通过DBMS_SCHEDULER.CREATE_JOB直接创建job

SQL> create table t_xifenfei (x_type varchar2(10),x_date date);

表已创建。

SQL> begin
  2  DBMS_SCHEDULER.create_job (
  3  job_name => 'f_create_job',
  4  job_type => 'PLSQL_BLOCK',
  5  job_action => '
  6   begin
  7   insert into t_xifenfei values(''job'',sysdate);
  8   commit;
  9   end;
 10  ',
 11  enabled => true,
 12  start_date => SYSTIMESTAMP,
 13  repeat_interval => 'SYSTIMESTAMP + 1/1440',
 14  comments => 'xifenfei_create_job');
 15  END;
 16  /

SQL> select x_type,to_char(x_date,'yyyy-mm-dd hh24:mi:ss') from t_xifenfei;

X_TYPE     TO_CHAR(X_DATE,'YYY
---------- -------------------
job        2012-06-19 19:52:11
job        2012-06-19 19:53:11
job        2012-06-19 19:54:11

这里的使用方法和dbms_jobs有几分类此,不过这个提供了加灵活的使用方法,比如可以执行匿名块,执行操作系统命令等

2.CREATE_JOB结合CREATE_PROGRAM

SQL>  create or replace procedure p_xifenfei(in_type in varchar2)
  2   is
  3   begin
  4   insert into t_xifenfei values(in_type,sysdate);
  5   commit;
  6   end;
  7   /

过程已创建。

SQL> begin
  2  DBMS_SCHEDULER.CREATE_PROGRAM(
  3  program_name => 'x_program',
  4  program_action => 'p_xifenfei',
  5  program_type => 'STORED_PROCEDURE',
  6  number_of_arguments => 1,
  7  comments => 'xifenfei_PROGRAM',
  8  enabled => false);
  9  end;
 10  /

PL/SQL 过程已成功完成。

SQL> begin
  2  DBMS_SCHEDULER.define_program_argument(
  3  program_name => 'x_program',
  4  argument_position => 1,
  5  argument_type => 'VARCHAR2',
  6  default_value => 'program');
  7  END;
  8  /

PL/SQL 过程已成功完成。

SQL>  exec DBMS_SCHEDULER.enable('x_program');

PL/SQL 过程已成功完成。

SQL> begin
  2  DBMS_SCHEDULER.create_job(
  3  job_name => 's_xifenfei_job',
  4  program_name => 'x_program',
  5  comments => 's_xifenfei_job',
  6  repeat_interval => 'SYSTIMESTAMP + 1/1440',
  7  auto_drop => false,
  8  enabled => true);
  9  end;
 10  /

PL/SQL 过程已成功完成。

SQL> select x_type,to_char(x_date,'yyyy-mm-dd hh24:mi:ss') from t_xifenfei;

X_TYPE     TO_CHAR(X_DATE,'YYY
---------- -------------------
job        2012-06-19 20:27:11
program    2012-06-19 20:27:09
program    2012-06-19 20:28:09
job        2012-06-19 20:28:11

这里可以看出来CREATE_PROGRAM是把CREATE_JOB中的部分参数给独立出来,使得更加灵活的控制,比如这里的使用从参数

3.CREATE_JOB结合CREATE_PROGRAM和CREATE_SCHEDULE

SQL> exec DBMS_SCHEDULER.drop_job('s_xifenfei_job');

PL/SQL 过程已成功完成。

SQL> truncate table t_xifenfei;

表被截断。

SQL> begin
  2  DBMS_SCHEDULER.create_schedule(
  3  repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
  4  start_date => sysdate,
  5  comments => 'xifenfei_sch',
  6  schedule_name => 'X_SCH');
  7  end;
  8  /

PL/SQL 过程已成功完成。

SQL> begin
  2  DBMS_SCHEDULER.create_job(
  3  job_name => 't_xifenfei_job',
  4  program_name => 'x_program',
  5  comments => 't_xifenfei_job',
  6  schedule_name => 'X_SCH',
  7  auto_drop => false,
  8  enabled => true);
  9  end;
 10  /

PL/SQL 过程已成功完成。

SQL> select x_type,to_char(x_date,'yyyy-mm-dd hh24:mi:ss') from t_xifenfei;

X_TYPE     TO_CHAR(X_DATE,'YYY
---------- -------------------
job        2012-06-19 20:39:11
job        2012-06-19 20:37:11
job        2012-06-19 20:38:11
program    2012-06-19 20:39:01
program    2012-06-19 20:40:01

CREATE_SCHEDULE是把执行计划部分从CREATE_JOB独立处理,使得控制力度更大,更加灵活

补充说明:
1.还可以通过创建JOB_CLASS更加灵活的控制资源的使用情况,必须通过修改JOB_CLASS中的resource_consumer_group实现资源控制,service对应到数据库的service可以实现rac中在哪个节点执行等等
2.使用DBMS_SCHEDULER.set_attribute来修改相关属相如:

EXEC DBMS_SCHEDULER.set_attribute('GATHER_STATS_JOB','JOB_CLASS', 'AUTO_TASKS_JOB_CLASS2');

exec dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','REPEAT_INTERVAL','freq=daily;
byday=MON,TUE,WED,THU,FRI;byhour=2;byminute=0;bysecond=0');

发表在 Oracle | 评论关闭

ORACLE在线切换undo表空间

发表于 2012 年 6 月 17 日由惜分飞

切换undo的一些步骤和基本原则

查看原undo相关参数
SHOW PARAMETER UNDO;

创建新undo空间
create undo tablespace undo_x datafile 'E:\ORACLE\ORADATA\XIFENFEI\undo_xifenfei.dbf' size 10M 
autoextend on next 10M maxsize 30G;

查询历史undo是否还有事务(包含回滚事务)
SELECT a.tablespace_name,a.segment_name,b.ktuxesta,b.ktuxecfl,
b.ktuxeusn||'.'||b.ktuxeslt||'.'||b.ktuxesqn trans
FROM dba_rollback_segs a, x$ktuxe b 
WHERE a.segment_id = b.ktuxeusn 
AND a.tablespace_name = UPPER('&tsname') 
AND b.ktuxesta <> 'INACTIVE';
--因为有undo_retention参数,所以不能简单的通过确定该sql无事务就可以删除原undo

切换undo表空间(无论是否有事务,均可以切换[最好是无事务时切换],但是不能直接删除原undo表空间)
alter system set undo_tablespace='undo_x';

alert日志现象,表明原undo还有事务
Sun Jun 17 20:10:45 2012
Successfully onlined Undo Tablespace 7.
[36428] **** active transactions found in undo Tablespace 2 - moved to Pending Switch-Out state.
[36428] active transactions found/affinity dissolution incompletein undo tablespace 2 during switch-out.
ALTER SYSTEM SET undo_tablespace='undo_xifenfei' SCOPE=BOTH;

Sun Jun 17 20:11:38 2012
[36312] **** active transactions found in undo Tablespace 2 - moved to Pending Switch-Out state.
Sun Jun 17 20:16:15 2012
[36312] **** active transactions found in undo Tablespace 2 - moved to Pending Switch-Out state.
--只能表明有事务,就算长时间未出现类似记录,不能证明一定可以删除原undo,因为undo_retention

查询回滚段情况(原undo表空间的回滚段全部offline,可以删除相关表空间)
select tablespace_name,segment_name,status from dba_rollback_segs;

离线原undo表空间
alter tablespace undotbs1 offline;

确定原undo回滚段全部offline,直接删除
drop tablespace undotbs1 including contents and datafiles;

切换undo表空间一句话：新建undo几乎是任何时候都可以执行切换undo表空间命令,如果要删除历史undo需要等到该undo空间所有回滚段全部offline.千万别在尚有回滚段处于online状态,强制删除数据文件.

发表在 Oracle | 评论关闭

利用flashback database实现部分对象回滚

发表于 2012 年 6 月 17 日由惜分飞

flashback database功能在生产库中,很少被直接使用,因为没有多少业务可以承受整个数据库级别的回滚.但是如果发生一些让人意想不到的误操作时候,想回滚该操作,我们不得不使用历史的备份来进行不完全恢复.如果没有历史备份,那简直是人生一个悲剧的发生.这里通过使用结合flashback database,实现flashback table级别不能完成的恢复,而且确保整个数据库的其他数据还是最新.这些操作比如:修改表结构,删除数据库用户等操作.这里通过修改表列的处理思路来展示该功能的使用方法,其他处理方法类此
1.确定启用flashback database功能

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL>  show parameter flash

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file                  string
db_flash_cache_size                  big integer 0
db_flashback_retention_target        integer     1440

2.模拟表结构被修改

SQL> create table t_xifenfei
  2  as
  3  select object_id,object_name from dba_objects;

表已创建。

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

会话已更改。

SQL>  select sysdate from dual;

SYSDATE
-------------------------
17-6月 -2012 15:25:24

SQL> ALTER TABLE t_xifenfei drop column object_name;

表已更改。

3.尝试flashback query功能

SQL> SELECT * FROM t_xifenfei as of timestamp to_timestamp('2012-06-17 15:25:24','yyyy-mm-dd hh24:mi:ss');
SELECT * FROM t_xifenfei as of timestamp to_timestamp('2012-06-17 15:25:24','yyyy-mm-dd hh24:mi:ss')
              *
第 1 行出现错误:
ORA-01466: 无法读取数据 - 表定义已更改
--这个证明因为ddl操作发生在表上,无法使用flashback table/query等操作

4.尝试flashback database

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> STARTUP MOUNT;
ORACLE 例程已经启动。

Total System Global Area  535662592 bytes
Fixed Size                  1385840 bytes
Variable Size             390072976 bytes
Database Buffers          138412032 bytes
Redo Buffers                5791744 bytes
数据库装载完毕。
SQL>  flashback database to timestamp to_date('2012-06-17 15:25:24','yyyy-mm-ddhh24:mi:ss');

闪回完成。

SQL> alter database open read only;

数据库已更改。

SQL> DESC CHF.T_XIFENFEI
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 OBJECT_ID                                          NUMBER
 OBJECT_NAME                                        VARCHAR2(128)

5.导出需要回滚对象

C:\Users\XIFENFEI>EXP chf/xifenfei tables=t_xifenfei file=d:\t_xifenfei.dmp 
>log=d:\t_xifenfei.log

Export: Release 11.2.0.3.0 - Production on 星期日 6月 17 15:40:37 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过常规路径...
. . 正在导出表                      T_XIFENFEI导出了       75270 行
成功终止导出, 没有出现警告。

6.恢复数据库至最新状态

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL>  startup mount
ORACLE 例程已经启动。

Total System Global Area  535662592 bytes
Fixed Size                  1385840 bytes
Variable Size             390072976 bytes
Database Buffers          138412032 bytes
Redo Buffers                5791744 bytes
数据库装载完毕。
SQL> recover database;
完成介质恢复。
SQL> alter database open;

数据库已更改。

SQL> desc chf.t_xifenfei
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 OBJECT_ID                                          NUMBER

7.导入正确数据

SQL> drop table chf.t_xifenfei purge;

表已删除。

SQL> host imp chf/xifenfei tables=t_xifenfei file=d:\t_xifenfei.dmp 
>log=d:\t_xifenfei.log

Import: Release 11.2.0.3.0 - Production on 星期日 6月 17 15:45:53 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

经由常规路径由 EXPORT:V11.02.00 创建的导出文件
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. 正在将 CHF 的对象导入到 CHF
. 正在将 CHF 的对象导入到 CHF
. . 正在导入表                    "T_XIFENFEI"导入了       75270 行
成功终止导入, 没有出现警告。

SQL> desc chf.t_xifenfei
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 OBJECT_ID                                          NUMBER
 OBJECT_NAME                                        VARCHAR2(128)

发表在 Oracle | 评论关闭

17813235971

QQ 咨询

月归档：六月 2012

关于DBMS_SCHEDULER基础

ORACLE在线切换undo表空间

利用flashback database实现部分对象回滚

文章分类

最近发表

月归档：六月 2012

关于DBMS_SCHEDULER基础

ORACLE在线切换undo表空间

利用flashback database实现部分对象回滚

标签云

文章分类

最近发表