ODU恢复PACKAGE/VIEW/DBLINK等数据脚本操作

当数据库不能拉起来时候,我们可以通过odu找回丢失的数据,今天客户出要求,需要我帮忙找回不能起来库中的包,过程,函数,视图,触发器等之类东西。这些东西都是存在system空间中,存在一些系统的基表中,我们如果能够拿到这些基表的数据然后进行处理,原则上就可以得到客户需要的东西
一、PROCEDURE/FUNCTION/PACKAGE/PACKAGE BODY/TRIGGER/TYPE/TYPE BODY之类恢复
1. 查看这些数据存储在什么基表中

select u.name, o.name,
decode(o.type#, 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
               11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY',
               'UNDEFINED'),
s.line, s.source
from sys.obj$ o, sys.source$ s, sys.user$ u
where o.obj# = s.obj#
  and o.owner# = u.user#
  and ( o.type# in (7, 8, 9, 11, 12, 14) OR
       ( o.type# = 13 AND o.subname is null))
union all
select u.name, o.name, 'JAVA SOURCE', s.joxftlno, s.joxftsrc
from sys.obj$ o, x$joxfs s, sys.user$ u
where o.obj# = s.joxftobn
  and o.owner# = u.user#
  and o.type# = 28;

通过这个sql知道主要存储在sys.obj$ o, sys.source$ s, sys.user$中(至于x$joxfs表,我暂时在数据库中未找到,不能使用odu提取数据,也就是说我这里不能恢复出来JAVA SOURCE)

2. 使用odu导出数据(注意output_format设置为dmp,防止出现意外)
使用类此unload table sys.obj$ 下面语句导出数据

3. 加载这些数据到新库中(切记不要使用sys schema)
使用类此imp chf/xifenfei file=d:/SYS_OBJ$.dmp fromuser=sys touser=chf导入数据

4. 在查询出需要对象

   SELECT u.name USER_NAME,
         o.name OBJECT_nAME,
         DECODE (o.type#,
                 7, 'PROCEDURE',
                 8, 'FUNCTION',
                 9, 'PACKAGE',
                 11, 'PACKAGE BODY',
                 12, 'TRIGGER',
                 13, 'TYPE',
                 14, 'TYPE BODY',
                 'UNDEFINED')
            OBJECT_tYPE,
         s.line,
         s.source
    FROM chf.obj$ o, chf.source$ s, chf.user$ u
   WHERE o.obj# = s.obj# AND o.owner# = u.user#
         AND (o.type# IN (7, 8, 9, 11, 12, 14)
              OR (o.type# = 13 AND o.subname IS NULL))
--过来条件选择出需要对象
-- AND U.name='BSSADMIN'
ORDER BY U.NAME,
         O.NAME,
         O.TYPE#,
         S.LINE;

5. 使用说明
在line=1的source前面加上create or replace ,使用合适的用户创建需要对象

二、VIEW恢复
1. 找出视图存储基表
sys.obj$,sys.view$,sys.user$
2. 使用odu导出需要表
3. 使用imp导入数据库
4. 查询语句

    SELECT u.name username, o.name viewname, v.text
  FROM chf.obj$ o, chf.view$ v, chf.user$ u
 WHERE o.obj# = v.obj# AND o.owner# = u.user# 
 --过滤条件,挑选需要视图
 AND u.name = 'MAS_ADMIN';

5. 使用说明
create VIEW OR REPLACE username.viewname AS+TEXT中内容

三、dblink恢复
1. 找出关联基表
Sys.link$和sys.user$
2. odu导出相关表
3. imp导入数据
4. 查询语句

SELECT U.NAME   USERNAME,
       L.NAME   DBLINK_NAME,
       L.USERID LOGIN_USER,
       L.HOST   TNS,
       L.CTIME
  FROM CHF.LINK$ L, CHF.USER$ U
 WHERE L.OWNER# = U.USER#
  --过滤条件
   AND U.NAME = 'PUBLIC'

5. 使用说明
根据查询出来信息,自己创建DBLINK

FAST_START_PARALLEL_ROLLBACK与回滚恢复

1.模拟产生大事务需回滚

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 16 12:47:08 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> show sga;

Total System Global Area  622149632 bytes
Fixed Size                  2230912 bytes
Variable Size             406848896 bytes
Database Buffers          205520896 bytes
Redo Buffers                7548928 bytes

SQL> create table chf.xifenfei tablespace users 
  2  as
  3  select * from dba_objects;

--下面两句多次执行
SQL> insert into chf.xifenfei 
  2  select * from chf.xifenfei;

73831 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from  chf.xifenfei;

  COUNT(*)
----------
  18900736

SQL> select bytes/1024/1024 from dba_segments where segment_name='XIFENFEI';

BYTES/1024/1024
---------------
           2103
--删除数据不提交
SQL> delete from chf.xifenfei;

18900736 rows deleted.

--直接kill掉ora_dbw进程

2.FAST_START_PARALLEL_ROLLBACK=LOW(默认值)

SQL> select  undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone
  2   "ToDo",decode(cputime,0,'unknown',to_char(sysdate+(((undoblockstotal-undoblocksdone) 
  3   / (undoblocksdone / cputime)) / 86400),'yyyy-mm-dd hh24:mi:ss')) 
  4  "Estimated time to complete",to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') 
  5  from v$fast_start_transactions;

     Total       Done       ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
    545624     103020     442604 2012-02-16 13:57:38 2012-02-16 13:47:02


SQL> /

     Total       Done       ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
    545624     122614     423010 2012-02-16 13:57:42 2012-02-16 13:47:31

--每秒钟回滚undo数据块数量
SQL> select (122614-103020)/29 from dual;

(122614-103020)/29
------------------
        675.655172

--数据库并发回滚进程数
[oracle@node1 ~]$ ps -ef|grep ora_p0
oracle   24901     1  4 13:44 ?        00:00:15 ora_p000_chf
oracle   24903     1  3 13:44 ?        00:00:12 ora_p001_chf
oracle   24905     1  3 13:44 ?        00:00:12 ora_p002_chf
oracle   24907     1  3 13:44 ?        00:00:12 ora_p003_chf
oracle   24909     1  3 13:44 ?        00:00:12 ora_p004_chf
oracle   24911     1  3 13:44 ?        00:00:12 ora_p005_chf
oracle   24913     1  3 13:44 ?        00:00:12 ora_p006_chf
oracle   24915     1  3 13:44 ?        00:00:12 ora_p007_chf
oracle   24917     1  3 13:44 ?        00:00:12 ora_p008_chf
oracle   24919     1  3 13:44 ?        00:00:12 ora_p009_chf
oracle   24921     1  3 13:44 ?        00:00:12 ora_p010_chf
oracle   24923     1  3 13:44 ?        00:00:12 ora_p011_chf
oracle   24925     1  3 13:44 ?        00:00:12 ora_p012_chf
oracle   24927     1  3 13:44 ?        00:00:12 ora_p013_chf
oracle   24929     1  3 13:44 ?        00:00:12 ora_p014_chf
oracle   24931     1  3 13:44 ?        00:00:12 ora_p015_chf

说明:该机器操作系统是8个CPU

并发数=CPU*2

3.FAST_START_PARALLEL_ROLLBACK =HIGH

SQL> alter system set FAST_START_PARALLEL_ROLLBACK =HIGH;

System altered.

SQL> select  undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone
  2   "ToDo",decode(cputime,0,'unknown',to_char(sysdate+(((undoblockstotal-undoblocksdone) 
  3   / (undoblocksdone / cputime)) / 86400),'yyyy-mm-dd hh24:mi:ss')) 
  4  "Estimated time to complete",to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') 
  5  from v$fast_start_transactions;

     Total       Done       ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
    380434      25351     355083 2012-02-16 14:03:53 2012-02-16 13:49:39


SQL> /

     Total       Done       ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
    380434      39592     340842 2012-02-16 14:03:33 2012-02-16 13:50:12

--每秒钟回滚undo数据块数量
SQL> select (39592-25351)/33 from dual;

(39592-25351)/33
----------------
      431.545455

--数据库并发回滚进程数
[oracle@node1 ~]$ ps -ef|grep ora_p0
oracle   24901     1  4 13:44 ?        00:00:15 ora_p000_chf
oracle   24903     1  3 13:44 ?        00:00:12 ora_p001_chf
oracle   24905     1  3 13:44 ?        00:00:12 ora_p002_chf
oracle   24907     1  3 13:44 ?        00:00:12 ora_p003_chf
oracle   24909     1  3 13:44 ?        00:00:12 ora_p004_chf
oracle   24911     1  3 13:44 ?        00:00:12 ora_p005_chf
oracle   24913     1  3 13:44 ?        00:00:12 ora_p006_chf
oracle   24915     1  3 13:44 ?        00:00:12 ora_p007_chf
oracle   24917     1  3 13:44 ?        00:00:12 ora_p008_chf
oracle   24919     1  3 13:44 ?        00:00:12 ora_p009_chf
oracle   24921     1  3 13:44 ?        00:00:12 ora_p010_chf
oracle   24923     1  3 13:44 ?        00:00:12 ora_p011_chf
oracle   24925     1  3 13:44 ?        00:00:12 ora_p012_chf
oracle   24927     1  3 13:44 ?        00:00:12 ora_p013_chf
oracle   24929     1  3 13:44 ?        00:00:12 ora_p014_chf
oracle   24931     1  3 13:44 ?        00:00:12 ora_p015_chf
oracle   25072     1  0 13:48 ?        00:00:01 ora_p016_chf
oracle   25074     1  0 13:48 ?        00:00:01 ora_p017_chf
oracle   25076     1  0 13:48 ?        00:00:01 ora_p018_chf
oracle   25078     1  0 13:48 ?        00:00:01 ora_p019_chf
oracle   25080     1  0 13:48 ?        00:00:01 ora_p020_chf
oracle   25082     1  0 13:48 ?        00:00:01 ora_p021_chf
oracle   25084     1  0 13:48 ?        00:00:01 ora_p022_chf
oracle   25086     1  0 13:48 ?        00:00:01 ora_p023_chf
oracle   25088     1  0 13:48 ?        00:00:01 ora_p024_chf
oracle   25090     1  0 13:48 ?        00:00:01 ora_p025_chf
oracle   25092     1  0 13:48 ?        00:00:01 ora_p026_chf
oracle   25094     1  0 13:48 ?        00:00:01 ora_p027_chf
oracle   25096     1  0 13:48 ?        00:00:01 ora_p028_chf
oracle   25098     1  0 13:48 ?        00:00:01 ora_p029_chf
oracle   25100     1  0 13:48 ?        00:00:01 ora_p030_chf
oracle   25102     1  0 13:48 ?        00:00:01 ora_p031_chf

1.说明问题:直接修改FAST_START_PARALLEL_ROLLBACK =HIGH后,
  数据库在原来并发进程基础上,又重新启动额外进程

2.修改FAST_START_PARALLEL_ROLLBACK后,以前回滚过的数据块是成功的
  v$fast_start_transactions视图重新开始计算

3.并发数=CPU*4
[/shell]

<strong>4.FAST_START_PARALLEL_ROLLBACK=FALSE</strong>
1
SQL> alter system set FAST_START_PARALLEL_ROLLBACK=FALSE;

System altered.

--直接修改为FALSE后,观察到数据库的并发等资源都没有释放,重启数据库释放资源继续试验
SQL> startup force;
ORACLE instance started.

Total System Global Area  622149632 bytes
Fixed Size                  2230912 bytes
Variable Size             406848896 bytes
Database Buffers          205520896 bytes
Redo Buffers                7548928 bytes
Database mounted.
Database opened.
SQL> select  undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone
  2   "ToDo",decode(cputime,0,'unknown',to_char(sysdate+(((undoblockstotal-undoblocksdone) 
  3   / (undoblocksdone / cputime)) / 86400),'yyyy-mm-dd hh24:mi:ss')) 
  4  "Estimated time to complete",to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') 
  5  from v$fast_start_transactions;

     Total       Done       ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
    306828      15735     291093 2012-02-16 14:04:34 2012-02-16 13:52:33


SQL> /

     Total       Done       ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
    306828      65861     240967 2012-02-16 14:05:15 2012-02-16 13:54:46

--每秒钟回滚undo数据块数量
SQL> select (65861-15735)/133 from dual;

(65861-15735)/133
-----------------
       376.887218

--数据库并发回滚进程数
[oracle@node1 ~]$ ps -ef|grep ora_p0
oracle   25252     1  1 13:51 ?        00:00:00 ora_p000_chf
oracle   25254     1  0 13:51 ?        00:00:00 ora_p001_chf
oracle   25256     1  1 13:51 ?        00:00:00 ora_p002_chf
oracle   25258     1  1 13:51 ?        00:00:00 ora_p003_chf
oracle   25260     1  1 13:51 ?        00:00:00 ora_p004_chf
oracle   25262     1  1 13:51 ?        00:00:00 ora_p005_chf
oracle   25264     1  1 13:51 ?        00:00:00 ora_p006_chf

1.数据库重启后,以前的回滚依然生效(v$fast_start_transactions.undoblockstotal变小)

2.FAST_START_PARALLEL_ROLLBACK=false还是有并发,而非官方文档描述(Parallel rollback is disabled)

5.总结

通过这三种情况下的每秒钟回滚undo数据块数量比较可以知道在LOW状态下最快,HIGH状态下次之,FALSE最慢。其实这个实验没有任何实际说明力,只是想说明几个问题:
1)Oracle大事物回滚,是没有办法取消,但是可以通过FAST_START_PARALLEL_ROLLBACK干预回滚速度
2)数据库的并发效率高于低,取决于系统的资源情况(如果你系统的cpu非常强大,那么可能设置HIGH速度最快)
3)回滚的数据类型,在回滚表中数据时可能设置并发比FALSE快,
但是如果是要回滚串行数据(如:index),那么可能串行方法方式速度更快
4)根据你的系统的使用状况,比如你想让系统的业务受到的影响最小,那么设置FALSE可能是个不错的选择。

6.补充官方说明

FAST_START_PARALLEL_ROLLBACK specifies the degree of parallelism used when recovering terminated transactions. 
Terminated transactions are transactions that are active before a system failure. 
If a system fails when there are uncommitted parallel DML or DDL transactions, 
then you can speed up transaction recovery during startup by using this parameter.

Values:

FALSE
Parallel rollback is disabled

LOW
Limits the maximum degree of parallelism to 2 * CPU_COUNT

HIGH
Limits the maximum degree of parallelism to 4 * CPU_COUNT

Note:If you change the value of this parameter, 
then transaction recovery will be stopped and restarted with the new implied degree of parallelism.