2005年02月04日

跟踪SLPLUS  SESSION

CONN   / AS SYSDBA

sql>begin

dbms_application_info.set_client_info(‘winy’);

end;

/

pl/sql 过程已成功完成

sql>select sid,client_info from v$session;

1

2

3

4

5

6

7 winy

8

9

10

2005年01月20日

壹、好用網管工具-webmin的安裝

一、說明:web界面的網管工具,非常簡單、好用,很多的網管、設定都可以用它(webmin)完成。

二、下載網址:
1.webmin網站
http://www.webmin.com
2.中正FTP站
ftp://www.ccps.tcc.edu.tw/程式區/linux/webmin/webmin-1.080.tar.gz

三、安裝步驟:
1.下載檔案webmin-1.xxx.tar.gz到/usr/local路徑下。(目前最新版本1.80)

2.解壓縮
#cd /usr/local
#tar zxvf webmin-1.xxx.tar.gz
#cd webmin1-xxx

3.安裝
#./setup.sh

(底下為安裝時所看到的提示)

Config file directory [/etc/webmin]:直接按enter
Log file directory [/var/webmin]:直接按enter

Full path to perl (default /usr/bin/perl):直接按enter
Web server port (default 10000):9988用預設(port1000)直接按enter,若要改的話,請輸入port的值,再按enter。(建議修改比較安全)
Login name (default admin):root輸入管理者帳號。
Login password:輸入密碼。
Password again:再打一次。
The Perl SSLeay library is not installed. SSL not available.
Start Webmin at boot time (y/n): y開機是否啟動webmin

4.安裝完成路徑。
http://xxx.xxx.xxx.xxx:port


四、移除步驟:
1.執行底下指令
#cd /etc/webmin/
# ./uninstall.sh
Are you sure you want to uninstall Webmin? (y/n) :y
2.移除完成。



 

貳、利用NFS來作網路主機的資料備份
一、 說明:
由於學校有數部的網路主機,將設定檔、重要資料直接備份在本身的電腦上又有風險,例如遇到駭客、雷擊、硬碟損壞,所以架設NFS主機,分別將數台電腦的資料,定時tar到NFS主機上,以降低風險,。

二、學校環境:
目前學校有proxy server 1部、web server1部、mail server 1部、pc router 1部,我是在web server這部電腦上加裝1顆硬碟,然後再將NFS server裝在這部電腦上。

三、NFS server安裝、設定步驟:安裝在web server這部電腦上。(以我們學校為例)
1.先檢查是否安裝:指令如下
#rpm -qa | grep nfs
(若沒有安裝的話,請找到光碟中nfs套件安裝)

2.啟動NFS server:指令如下
#/etc/rc.d/init.d/nfs start

3.建立共用目錄:
#mkdir /backup/mail_back
#mkdir /backup/proxy_back
#mkdir /backup/router_back
(以上建立的資料夾,分別作為mail、proxy、router備份的資料夾)

4.進行共用目錄的匯出設定:(以webmin 1.07設定)
舉例說明:mail server的共用目錄的設定方法
a.登入webmin後,在點選networking、NFS Exports、Add a new export
b.在Create Export中的Directory to export選擇你要匯出的共用目錄,例如/backup/mail_back。
c.接著Export to…,選擇host,然後再輸入位址,例如163.17.97.x
d.在Trust remote users中,選擇Everyone

其它server的共用目錄設定同上述。

四BNFS Client設定步驟:(Client端的是mail、proxy、router這些機器)
舉例說明:mail server的設定
1.建立備份資料夾
#mkdir /mail_back

2.檢視NFS server的共用資源,指令
#showmount -e 163.17.97.x
應該會看到nfs server匯出的目錄。

3.掛載NFS server的匯出目錄,指令
#mount -t nfs 163.17.97.x:/backup/mail_back /mail_back

五、資料備份:(舉例說明:mail server的設定)
1.編輯mail_back.sh,指令
#vi /mail_back/mail_back.sh

#以下內容僅供參考,你可依照需要,增減你要備份的資料。
day=`date +%Y-%m-%d`
cp /etc/passwd /mail_back/passwd/passwd.”$day”
cp /etc/shadow /mail_back/shadow/shadow.”$day”
cp /etc/group /mail_back/group/group.”$day”
cp /etc/gshadow /mail_back/gshadow/gshadow.”$day”
tar -czf /mail_back/mail/mail.tar.gz /var/spool/mail
tar -czf /mail_back/home/home.tar.gz /home
tar -czf /mail_back/www/www.tar.gz /var/www
tar -czf /mail_back/mysql/mysql.”$day”.tar.gz /var/lib/mysql
tar -czf /mail_back/smb/smb.tar.gz /etc/samba

2.排入排程:(以webmin 1.07設定)
a.登入webmin,點選system、Scheduled Cron Jobs、Create a new scheduled cron job
b.在Edit Cron Job中,Execute cron job as請輸入root,Command請輸入/mail_back/mail_back.sh
c.設定When to execute(你要執行的時間)
d.最後作save的動作,你也可以執行一下(Run Now)看看有無問題。

六、建議:
為了再保險一點,建議將tar到NFS主機上的資料再定期copy出來,而這樣作可能有點麻煩,不過卻又多了一分的保障。

2005年01月17日

关于SGA设置的一点总结
作者:Biti_rainy

说明:
本总结不针对特例,仅对服务器只存在OS + ORACLE 为例,如果存在其他应用请酌情考虑。写这个也是因为近来这种重复性的问题发生的太多所导致的。

首先不要迷信STSOCP SG以及某些专家给出的任何建议、内存百分比的说法。基本掌握的原则是:

l          data buffer 通常可以尽可能的大

l          shared_pool_size 要适度

l          log_buffer 通常大到几百K1M就差不多了

设置之前,首先要明确2个问题
1
:除去OS和一些其他开销,能给ORACLE使用的内存有多大?
2
Oracle64 bit 还是 32 bit 32bit 通常 SGA 1.7G 的限制(某些OS有特定处理或者WINDOWS上有特定设定可以支持到2G以上甚至达到3.7G,本人无这方面经验)

下面是我的Windows2000下的Oracle :

SQL> select * from v$version;

BANNER
—————————————————————-
Oracle8i Enterprise Edition Release 8.1.7.0.0 – Production
PL/SQL Release 8.1.7.0.0 – Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 – Production
NLSRTL Version 3.4.1.0.0 – Production

SQL>

windows
上存在32bit的限制,如AIXHP-UX 等有明确的64 bit OS ORACLE的版本,32 bit Oracle可以装在64 bit OS 上,64 bit Oracle不能装在32 bit OS上。

不管Oracle32 bit 还是 64 bit 的,假定应用存在没有很好的使用bind var 的情况,也不能设置 shared_pool_size 过大,通常应该控制在100M--200M,除非是 ORACLE ERP 一类的使用了很多存储过程函数、包 ,这样的很大的系统,可以考虑增大shared_pool_size,但是如果超过500M可能是危险的,达到1G几乎就会造成CPU的严重负担,系统甚至瘫痪。所以shared_pool_size 如果超过200M还命中率不高,那么应该从应用上找原因而不是一味的增加内存,shared_pool_size 过大主要增加了管理负担和latch 的开销。

log_buffer
128K — 1M 之间通常问题不大,不应该太大。

large_pool_size
:如果不设置MTS,通常在 RMAN OPQ 会使用到,但是在10M --50M 应该差不多了。假如设置 MTS,则由于 UGA 放到large_pool_size 的缘故,这个时候依据 session最大数量和 sort_ares_size 等参数设置,必须增大large_pool_size 的设置,可以考虑为 session * (sort_area_size + 2M)。这里要提醒一点,不是必须使用MTS,我们都不主张使用MTS,尤其同时在线用户数小于500的情况下。

java_pool_size :
若不使用java,给30M通常就够了。

data buffer
:在做了前面的设置后,凡可以提供给Oracle的内存,都应该给data buffer = (db_block_size * db_block_buffers) 9i 中可以是 db_cache_size

还有2个重要参数我们需要注意:

sort_area_size
hash_area_size
这两个参数在非MTS下都是属于PGA ,不属于SGA。它是为每个session单独分配的,在我们的服务器上除了OS + SGA,一定要考虑这两部分。

(****) : OS
使用内存+ SGA + session*(sort_area_size + hash_area_size + 2M) < 总物理RAM 为好

这样归结过来,假定Oracle 32 bit ,服务器RAM大于2G ,注意你的PGA的情况,则建议:

shared_pool_size + data buffer +large_pool_size + java_pool_size < 1.6G

再具体化,注意满足上面(****) 的原则的基础上可以参考如下设置:
如果512M RAM
建议 shared_pool_size = 50M, data buffer = 200M

如果1G RAM
shared_pool_size = 100M , data buffer = 500M


如果2G RAM
shared_pool_size = 150M ,data buffer = 1.2G


物理内存再大已经跟参数没有关系了

举例来说:
假定64 bit ORACLE

内存4G
shared_pool_size = 200M , data buffer = 2.5G

内存8G
shared_pool_size = 200M , data buffer = 5G

内存 12G
shared_pool_size = 300M , data buffer = 8G

总结

以上仅为参考值,建议在设置参数的同时,init中使用 lock_sga ,在不同的平台上可能有不同的方式,使得SGA锁定在物理内存中而不被放入 SWAP 中,这样对效率有好处。(关于内存的设置,要再进行细致的调整,起的作用不大,但可根据statspack信息和v$system_event,v$sysstat,v$sesstat,v$latch 等视图信息来考虑微调。)


编辑注: 关于此贴的详细情况请参考论坛的帖子:
http://www.itpub.net/showthread.php?s=&threadid=109787
你可以找到Biti_rainy,Chao_ping,Coolyl等对此话题的更多讨论。

 

(END)

关于SGA设置的一点总结
作者:Biti_rainy

说明:
本总结不针对特例,仅对服务器只存在OS + ORACLE 为例,如果存在其他应用请酌情考虑。写这个也是因为近来这种重复性的问题发生的太多所导致的。

首先不要迷信STSOCP SG以及某些专家给出的任何建议、内存百分比的说法。基本掌握的原则是:

l          data buffer 通常可以尽可能的大

l          shared_pool_size 要适度

l          log_buffer 通常大到几百K1M就差不多了

设置之前,首先要明确2个问题
1
:除去OS和一些其他开销,能给ORACLE使用的内存有多大?
2
Oracle64 bit 还是 32 bit 32bit 通常 SGA 1.7G 的限制(某些OS有特定处理或者WINDOWS上有特定设定可以支持到2G以上甚至达到3.7G,本人无这方面经验)

下面是我的Windows2000下的Oracle :

SQL> select * from v$version;

BANNER
—————————————————————-
Oracle8i Enterprise Edition Release 8.1.7.0.0 – Production
PL/SQL Release 8.1.7.0.0 – Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 – Production
NLSRTL Version 3.4.1.0.0 – Production

SQL>

windows
上存在32bit的限制,如AIXHP-UX 等有明确的64 bit OS ORACLE的版本,32 bit Oracle可以装在64 bit OS 上,64 bit Oracle不能装在32 bit OS上。

不管Oracle32 bit 还是 64 bit 的,假定应用存在没有很好的使用bind var 的情况,也不能设置 shared_pool_size 过大,通常应该控制在100M--200M,除非是 ORACLE ERP 一类的使用了很多存储过程函数、包 ,这样的很大的系统,可以考虑增大shared_pool_size,但是如果超过500M可能是危险的,达到1G几乎就会造成CPU的严重负担,系统甚至瘫痪。所以shared_pool_size 如果超过200M还命中率不高,那么应该从应用上找原因而不是一味的增加内存,shared_pool_size 过大主要增加了管理负担和latch 的开销。

log_buffer
128K — 1M 之间通常问题不大,不应该太大。

large_pool_size
:如果不设置MTS,通常在 RMAN OPQ 会使用到,但是在10M --50M 应该差不多了。假如设置 MTS,则由于 UGA 放到large_pool_size 的缘故,这个时候依据 session最大数量和 sort_ares_size 等参数设置,必须增大large_pool_size 的设置,可以考虑为 session * (sort_area_size + 2M)。这里要提醒一点,不是必须使用MTS,我们都不主张使用MTS,尤其同时在线用户数小于500的情况下。

java_pool_size :
若不使用java,给30M通常就够了。

data buffer
:在做了前面的设置后,凡可以提供给Oracle的内存,都应该给data buffer = (db_block_size * db_block_buffers) 9i 中可以是 db_cache_size

还有2个重要参数我们需要注意:

sort_area_size
hash_area_size
这两个参数在非MTS下都是属于PGA ,不属于SGA。它是为每个session单独分配的,在我们的服务器上除了OS + SGA,一定要考虑这两部分。

(****) : OS
使用内存+ SGA + session*(sort_area_size + hash_area_size + 2M) < 总物理RAM 为好

这样归结过来,假定Oracle 32 bit ,服务器RAM大于2G ,注意你的PGA的情况,则建议:

shared_pool_size + data buffer +large_pool_size + java_pool_size < 1.6G

再具体化,注意满足上面(****) 的原则的基础上可以参考如下设置:
如果512M RAM
建议 shared_pool_size = 50M, data buffer = 200M

如果1G RAM
shared_pool_size = 100M , data buffer = 500M


如果2G RAM
shared_pool_size = 150M ,data buffer = 1.2G


物理内存再大已经跟参数没有关系了

举例来说:
假定64 bit ORACLE

内存4G
shared_pool_size = 200M , data buffer = 2.5G

内存8G
shared_pool_size = 200M , data buffer = 5G

内存 12G
shared_pool_size = 300M , data buffer = 8G

总结

以上仅为参考值,建议在设置参数的同时,init中使用 lock_sga ,在不同的平台上可能有不同的方式,使得SGA锁定在物理内存中而不被放入 SWAP 中,这样对效率有好处。(关于内存的设置,要再进行细致的调整,起的作用不大,但可根据statspack信息和v$system_event,v$sysstat,v$sesstat,v$latch 等视图信息来考虑微调。)


编辑注: 关于此贴的详细情况请参考论坛的帖子:
http://www.itpub.net/showthread.php?s=&threadid=109787
你可以找到Biti_rainy,Chao_ping,Coolyl等对此话题的更多讨论。

 

(END)

2005年01月06日

物化视图是包括一个查询结果的数据库对像,它是远程数据的的本地副本,或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据,也可以称为快照。

       物化视图可以查询表,视图和其它的物化视图。

       通常情况下,物化视图被称为主表(在复制期间)或明细表(在数据仓库中)。

       对于复制,物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本,必须用高级复制的功能。当你想从一个表或视图中抽取数据时,你可以用从物化视图中抽取。

       对于数据仓库,创建的物化视图通常情况下是聚合视图,单一表聚合视图和连接视图。

本篇我们将会看到怎样创建物化视图并且讨论它的刷新选项。

       在复制环境下,创建的物化视图通常情况下主键,rowid,和子查询视图。

1.主键物化视图:

              下面的语法在远程数据库表emp上创建主键物化视图

        SQL> CREATE MATERIALIZED VIEW mv_emp_pk
             REFRESH FAST START WITH SYSDATE 
            NEXT  SYSDATE + 1/48
            WITH PRIMARY KEY 
            AS SELECT * FROM emp@remote_db;
            Materialized view created.

              注意:当用FAST选项创建物化视图,必须创建基于主表的视图日志,如下:

            SQL> CREATE MATERIALIZED VIEW LOG ON emp;
            Materialized view log created.

2.Rowid物化视图

              下面的语法在远程数据库表emp上创建Rowid物化视图

      SQL> CREATE MATERIALIZED VIEW mv_emp_rowid 
            REFRESH WITH ROWID 
             AS SELECT * FROM emp@remote_db; 
            Materialized view log created.

3.子查询物化视图

              下面的语法在远程数据库表emp上创建基于empdept表的子查询物化视图

            SQL> CREATE MATERIALIZED VIEW  mv_empdept
            AS SELECT * FROM emp@remote_db e
            WHERE EXISTS
             (SELECT * FROM dept@remote_db d
             WHERE e.dept_no = d.dept_no)
      Materialized view log created.

       REFRESH 子句

                    [refresh [fast|complete|force]
            [on demand | commit]
            [start with date] [next date]
            [with {primary key|rowid}]]
 

       Refresh选项说明:

a.      oracle用刷新方法在物化视图中刷新数据.

b.      是基于主键还是基于rowid的物化视图

c.       物化视图的刷新时间和间隔刷新时间

 

Refresh方法-FAST子句

       增量刷新用物化视图日志(参照上面所述)来发送主表已经修改的数据行到物化视图中.如果指定REFRESH FAST子句,那么应该对主表创建物化视图日志

                    SQL> CREATE MATERIALIZED VIEW LOG ON emp;

Materialized view log created.

       对于增量刷新选项,如果在子查询中存在分析函数,则物化视图不起作用。

 

Refresh方法- COMPLETE子句

       完全刷新重新生成整个视图,如果请求完全刷新,oracle会完成       完全刷新即使增量刷新可用。

 

Refresh Method – FORCE 子句

       当指定FORCE子句,如果增量刷新可用Oracle将完成增量刷新,否则将完成完全刷新,如果不指定刷新方法(FAST, COMPLETE, or FORCE),Force选项是默认选项

 

主键和ROWD子句

       WITH PRIMARY KEY选项生成主键物化视图,也就是说物化视图是基于主表的主键,而不是ROWID(对应于ROWID子句). PRIMARY KEY是默认选项,为了生成PRIMARY KEY子句,应该在主表上定义主键,否则应该用基于ROWID的物化视图.

       主键物化视图允许识别物化视图主表而不影响物化视图增量刷新的可用性。

       Rowid物化视图只有一个单一的主表,不能包括下面任何一项:

n         Distinct 或者聚合函数.

n         Group by,子查询,连接和SET操作

 

刷新时间      

       START WITH子句通知数据库完成从主表到本地表第一次复制的时间,应该及时估计下一次运行的时间点, NEXT 子句说明了刷新的间隔时间.

            SQL> CREATE MATERIALIZED VIEW mv_emp_pk
                     REFRESH FAST 
                  START WITH SYSDATE 
                  NEXT  SYSDATE + 2
                  WITH PRIMARY KEY 
                  AS SELECT * FROM emp@remote_db;
                  Materialized view created.

在上面的例子中,物化视图数据的第一个副本在创建时生成,以后每两天刷新一次.

 

总结

物化视图提供了可伸缩的基于主键或ROWID的视图,指定了刷新方法和自动刷新的时间。

2004年12月30日

rman 的备份文件,只能在 server 端生成,不管 rman 在什么客户端;(比如 server linux, 而 rman 在 windows 下启动,最终的
backup piece 还是在 linux 端生成)

backup database 的时候,假如不设置 channel 的 limit ,一开始就会创建一个大文件,因此,假如你空间不够,他就会报错,比如

RMAN> run { allocate channel c3 type disk; backup database ;}

RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: c3
RMAN-08500: channel c3: sid=23 devtype=DISK

RMAN-03022: compiling command: backup
RMAN-03023: executing command: backup
RMAN-08008: channel c3: starting full datafile backupset
RMAN-08502: set_count=19 set_stamp=483979175 creation_time=22-JAN-03
RMAN-08010: channel c3: specifying datafile(s) in backupset
RMAN-08522: input datafile fno=00005 name=/db/oracle/oradata/netbar/users01.dbf
RMAN-08522: input datafile fno=00003 name=/db/oracle/oradata/netbar/rbs01.dbf
RMAN-08522: input datafile fno=00004 name=/db/oracle/oradata/netbar/temp01.dbf
RMAN-08522: input datafile fno=00006 name=/db/oracle/oradata/netbar/indx01.dbf
RMAN-08522: input datafile fno=00001 name=/db/oracle/oradata/netbar/system01.dbf
RMAN-08011: including current controlfile in backupset
RMAN-08522: input datafile fno=00002 name=/db/oracle/oradata/netbar/tools01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03007: retryable error occurred during execution of command: backup
RMAN-07004: unhandled exception during command execution on channel c3
RMAN-10035: exception raised in RPC: ORA-19504: failed to create file “0jedhrt7_1_1″
ORA-27044: unable to write the header block of file
Linux Error: 22: Invalid argument
Additional information: 2
RMAN-10031: ORA-19624 occurred during call to DBMS_BACKUP_RESTORE.BACKUPPIECECREATE

因此,必须设置 channel 的 limit 啦
RMAN>run { allocate channel c3 type disk;set limit channel c3 kbytes 1800000;backup database format ‘/home/ora/netbars_%p.rman’;}
就完全工作正常。

另外,假如没有指明 format ,那么 文件位置在 $ORACLE_HOME/dbs 下面

情况描述
客户报告数据库故障,新来的系统管理员误操作。删掉了一些文件。具体情况是:删掉了所有重要数据文件、所有控制文件。数据库原来是归档模式,用rman备份数据,而rman 使用控制文件。幸运的是,最后一次 rman full 备份是包括了控制文件在内。系统没有设定自动备份控制文件。现在状况是数据库无法启动。
不用说,客户的备份方案不够完善,但是这时候再去说这些话责备用户有事后诸葛亮之嫌,”用户是上帝,不要去得罪他”。还有,客户有Full备份(虽然不是自动备份控制文件,这样无法用常规的恢复步骤来进行恢复)。这对我们来说是个绝对的好消息。

下面我们通过一次模拟操作来演示这个问题的解决办法。

背景知识
在Oracle 816 以后的版本中,Oracle提供了一个包:DBMS_BACKUP_RESTORE.DBMS_BACKUP_RESTORE 包是由dbmsbkrs.sql 和 prvtbkrs.plb 这两个脚本创建的.catproc.sql 脚本运行后会调用这两个包.所以是每个数据库都有的这个包是Oracle服务器和操作系统之间IO操作的接口.由恢复管理器直接调用。而且据说这两个脚本的功能是内建到Oracle的一些库文件中的.

由此可见,我们可以在数据库 nomount 情况下调用这些package ,来达到我们的恢复目的。在dbmsbkrs.sql 和prvtbkrs.plb 这两个脚本中有详细的说明文档,出于篇幅问题,就不一一加以翻译了,但在下面会直接引用一些原文说明。

关键的内容有:

FUNCTION  deviceAllocate(
       type IN varchar2 default NULL
      ,name IN varchar2 default NULL
      ,ident IN varchar2 default NULL
      ,noio IN boolean default FALSE
      ,params IN varchar2 default NULL )
RETURN varchar2;

– Describe the device to be used for sequential I/O. For device types where
– only one process at a time can use a device, this call allocates a device
– for exclusive use by this session. The device remains allocated until
– deviceDeallocate is called or session termination. The device can be used
– both for creating and restoring backups.

– Specifying a device allocates a context that exists until the session
– terminates or deviceDeallocate is called. Only one device can be specified
– at a time for a particular session. Thus deviceDeallocate must be called
– before a different device can be specified. This is not a limitation since
– a session can only read or write one backup at a time.

– The other major effect of allocating a device is to specify the name space
– for the backup handles (file names). The handle for a sequential file does
– not necessarily define the type of device used to write the file. Thus it
– is necessary to specify the device type in order to interpret the file
– handle. The NULL device type is defined for all systems. It is the file
– system supplied by the operating system. The sequential file handles are
– thus normal file names.

– A device can be specified either by name or by type.
– If the type is specified but not the name, the system picks an
– available device of that type.
– If the name is specified but not the type, the type is determined
– from the device.
– If neither the type or the name is given, the backups are files in
– the operating system file system.

– Note that some types of devices, optical disks for example, can be shared
– by many processes, and thus do not really require allocation of the device
– itself. However we do need to allocate the context for accessing the
– device, and we do need to know the device type for proper interpretation
– of the file handle. Thus it is always necessary to make the device
– allocation call before making most other calls in this package.

– Input parameters:
– type
– If specified, this gives the type of device to use for sequential
– I/O. The allowed types are port specific. For example a port may
– support the type “TAPE” which is implemented via the Oracle tape
– API. If no type is specified, it may be implied by specifying a
– particular device name to allocate. The type should be allowed to
– default to NULL if operating system files are to be used.

– name
– If specified, this names a particular piece of hardware to use for
– accessing sequential files. If not specified, any available
– device of the correct type will be allocated. If the device cannot
– be shared, it is allocated to this session for exclusive use.
– The name should be allowed to default to NULL if operating system
– files are to be used.

– ident
– This is the users identifier that he uses to name this device. It
– is only used to report the status of this session via
– dbms_application_info. This value will be placed in the CLIENT_INFO
– column of the V$SESSION table, in the row corresponding to the
– session in which the device was allocated. This value can also
– be queried with the dbms_application_info.read_client_info procedure.

– noio
– If TRUE, the device will not be used for doing any I/O. This allows
– the specification of a device type for deleting sequential files
– without actually allocating a piece of hardware. An allocation for
– noio can also be used for issuing device commands. Note that some
– commands may actually require a physical device and thus will get
– an error if the allocate was done with noio set to TRUE.

– params
– This string is simply passed to the device allocate OSD. It is
– completely port and device specific.

– Returns:
– It returns a valid device type. This is the type that should be
– allocated to access the same sequential files at a later date. Note
– that this might not be exactly the same value as the input string.
– The allocate OSD may do some translation of the type passed in. The
– return value is NULL when using operating system files.

PROCEDURE restoreControlfileTo(cfname IN varchar2);

– This copies the controlfile from the backup set to an operating system
– file. If the database is mounted, the name must NOT match any of the
– current controlfiles.

– Input parameters:
– cfname
– Name of file to create or overwrite with the controlfile from the
– backup set.

PROCEDURE restoreDataFileTo( dfnumber IN binary_integer
,toname IN varchar2 default NULL);

– restoreDataFileTo creates the output file from a complete backup in the
– backup set.

如果您有兴趣可以去阅读一下这两个文件的注释说明.

解决过程
首先,用控制文件作数据库系统的全备份:

C:WUTemp>rman target /

Recovery Manager: Release 9.2.0.1.0 – Production.

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: DEMO (DBID=3272375326)

RMAN> run {
2> allocate channel C1 type disk;
3> backup full tag ‘FullBackup’ format ‘d:\KDE\%d_%u_%s_%p.dbf’ database include current controlfile;
4> sql ‘ alter system archive log current’;
5> release channel C1;
6> }

using target database controlfile instead of recovery catalog
allocated channel: C1
channel C1: sid=15 devtype=DISK

Starting backup at 18-JUL-04
channel C1: starting full datafile backupset
channel C1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00001 name=D:\ORACLE\ORADATA\DEMO\SYSTEM01.DBF
input datafile fno=00002 name=D:\ORACLE\ORADATA\DEMO\UNDOTBS01.DBF
input datafile fno=00004 name=D:\ORACLE\ORADATA\DEMO\EXAMPLE01.DBF
input datafile fno=00009 name=D:\ORACLE\ORADATA\DEMO\XDB01.DBF
input datafile fno=00005 name=D:\ORACLE\ORADATA\DEMO\INDX01.DBF
input datafile fno=00008 name=D:\ORACLE\ORADATA\DEMO\USERS01.DBF
input datafile fno=00003 name=D:\ORACLE\ORADATA\DEMO\DRSYS01.DBF
input datafile fno=00006 name=D:\ORACLE\ORADATA\DEMO\ODM01.DBF
input datafile fno=00007 name=D:\ORACLE\ORADATA\DEMO\TOOLS01.DBF
channel C1: starting piece 1 at 18-JUL-04
channel C1: finished piece 1 at 18-JUL-04
piece handle=D:\KDE\DEMO_01FR79OT_1_1.DBF comment=NONE
channel C1: backup set complete, elapsed time: 00:01:17
Finished backup at 18-JUL-04

sql statement: alter system archive log current

released channel: C1

如上所示,我们做了一次数据库的Full备份.备份片中包括控制文件.注意上面输出内容的黑体部分.我们在后面的恢复操作中会用到.

模拟错误,关掉实例,删掉所有的控制文件和所有的.DBF文件。然后starup会看到如下的出错信息:

SQL> startup
ORACLE instance started.

Total System Global Area 152115804 bytes
Fixed Size 453212 bytes
Variable Size 100663296 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes
ORA-00205: error in identifying controlfile, check alert log for more info

查看alert Log,应该是系统找不到控制文件.现在情形和客户问题一致.不过在继续讲述之前,我们还需要介绍一点背景知识.

我们首先尝试恢复控制文件:
SQL>startup force nomount;

SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>”,ident=>’T1′);
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreControlfileTo(cfname=>’d:\oracle\Control01.ctl’);
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>’D:\KDE\DEMO_01FR79OT_1_1.DBF’, params=>null);
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /

PL/SQL procedure successfully completed.

OK,控制文件恢复完成.对以上内容的解释:
第五行 分配一个device channel,因为使用的操作系统文件,所以这里为空,如果是从磁带上恢复要用    “sbt_tape”;
第六行 指明开始restore ;
第七行 指出待恢复文件目标存储位置;
第八行 从哪个备份片中恢复;
第九行 释放设备通道.
不妨对以上操作的结果验证一下:

SQL> host dir d:\oracle
Volume in drive D is DATA
Volume Serial Number is DC79-57F8
Directory of d:\oracle

07/18/2004 09:08 PM

.
07/18/2004 09:08 PM ..
06/08/2004 03:21 PM admin
07/18/2004 09:08 PM 1,871,872 CONTROL01.CTL
07/16/2004 11:27 AM ORA92
07/18/2004 09:02 PM oradata

这样,我们成功的restore了控制文件 .如果控制文件在Full备份之后单独做的,接下来关掉实例,拷贝控制文件到具体位置,然后rman 执行restore database;即可。 可是,我们这里的情况有些不同. 视丢失文件的情况而定,继续进行如下的恢复操作:
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate (type=>”,ident=>’t1′);
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>’
d:\oracle\oradata\demo\SYSTEM01.DBF’);
8 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>’
d:\oracle\oradata\demo\UNDOTBS01.DBF’);
9 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>’
d:\oracle\oradata\demo\DRSYS01.DBF’);
10 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>’
d:\oracle\oradata\demo\EXAMPLE01.DBF’);
11 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>’
d:\oracle\oradata\demo\INDX01.DBF’);
12 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>06,toname=>’
d:\oracle\oradata\demo\ODM01.DBF’);
13 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>07,toname=>’
d:\oracle\oradata\demo\TOOLS01.DBF’);
14 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>08,toname=>’
d:\oracle\oradata\demo\USERS01.DBF’);
15 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>09,toname=>’
d:\oracle\oradata\demo\XDB01.DBF’);
16 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>’
D:\KDE\DEMO_01FR79OT_1_1.DBF’, params=>null);
17 sys.dbms_backup_restore.deviceDeallocate;
18 END;
19 /

PL/SQL procedure successfully completed.

–我们的情形是所有的数据文件都丢失了,那就如法炮制 ……….. –文件对应编号来自前面全备份时候的屏幕输出内容.所以,在备份的时候保留操作Log是个很好的习惯.
SQL> startup force mount;
ORACLE instance started.

Total System Global Area 152115804 bytes
Fixed Size 453212 bytes
Variable Size 100663296 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> Recover database using backup controlfile until cancel ;
ORA-00279: change 243854 generated at 07/18/2004 20:57:03 needed for thread 1
ORA-00289: suggestion : D:\KDE\ARC00002.001
ORA-00280: change 243854 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}

D:\KDE\ARC00002.001
ORA-00279: change 244089 generated at 07/18/2004 20:58:18 needed for thread 1
ORA-00289: suggestion : D:\KDE\ARC00003.001
ORA-00280: change 244089 for thread 1 is in sequence #3
ORA-00278: log file ‘D:\KDE\ARC00002.001′ no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> alter database open resetlogs;

Database altered.

最后,不得不resetlogs .

然后,打扫战场,马上进行数据库的全备份。如果您是DBA的话,应该进一步制定并完善备份计划.亡羊补牢,为时未晚。

总结一下
控制文件在备份中意义重大,建议每次对其单独备份,如果数据库版本允许的话,应该设置为控制文件自动备 份。同时应该尽可能地增大CONTROL_FILE_RECORD_KEEP_TIME这个初始化参数的值。以便备份信息能更长时间的保留
应该制定比较完善的备份计划,否则备份计划一旦出现缺口,将可能给系统带来灾难.记住, "可能出错的地方一定会出错".
熟悉RMAN内部备份机制,对DBMS_BACKUP_RESTORE的用法有一定的掌握在关键时侯很有帮助.
备份脚本应该对Log重定向并保存.以便在出错的查找有用信息.

参考信息

RMAN Recovery Without Recovery Catalog or Controlfiles by Bonnie Bizzaro
dbmsbkrs.sql 和 prvtbkrs.plb 文件说明注释(可在你的系统 $ORACLE_HOME/rdbms/admin/中找到.)

相关链接
本文的更多讨论,请看这里 – http://www.itpub.net/244345.html
本文的Blog讨论,请看这里 – http://blog.csdn.net/fenng/archive/2004/07/19/44945.aspx

情况描述
客户报告数据库故障,新来的系统管理员误操作。删掉了一些文件。具体情况是:删掉了所有重要数据文件、所有控制文件。数据库原来是归档模式,用rman备份数据,而rman 使用控制文件。幸运的是,最后一次 rman full 备份是包括了控制文件在内。系统没有设定自动备份控制文件。现在状况是数据库无法启动。
不用说,客户的备份方案不够完善,但是这时候再去说这些话责备用户有事后诸葛亮之嫌,”用户是上帝,不要去得罪他”。还有,客户有Full备份(虽然不是自动备份控制文件,这样无法用常规的恢复步骤来进行恢复)。这对我们来说是个绝对的好消息。

下面我们通过一次模拟操作来演示这个问题的解决办法。

背景知识
在Oracle 816 以后的版本中,Oracle提供了一个包:DBMS_BACKUP_RESTORE.DBMS_BACKUP_RESTORE 包是由dbmsbkrs.sql 和 prvtbkrs.plb 这两个脚本创建的.catproc.sql 脚本运行后会调用这两个包.所以是每个数据库都有的这个包是Oracle服务器和操作系统之间IO操作的接口.由恢复管理器直接调用。而且据说这两个脚本的功能是内建到Oracle的一些库文件中的.

由此可见,我们可以在数据库 nomount 情况下调用这些package ,来达到我们的恢复目的。在dbmsbkrs.sql 和prvtbkrs.plb 这两个脚本中有详细的说明文档,出于篇幅问题,就不一一加以翻译了,但在下面会直接引用一些原文说明。

关键的内容有:

FUNCTION  deviceAllocate(
       type IN varchar2 default NULL
      ,name IN varchar2 default NULL
      ,ident IN varchar2 default NULL
      ,noio IN boolean default FALSE
      ,params IN varchar2 default NULL )
RETURN varchar2;

– Describe the device to be used for sequential I/O. For device types where
– only one process at a time can use a device, this call allocates a device
– for exclusive use by this session. The device remains allocated until
– deviceDeallocate is called or session termination. The device can be used
– both for creating and restoring backups.

– Specifying a device allocates a context that exists until the session
– terminates or deviceDeallocate is called. Only one device can be specified
– at a time for a particular session. Thus deviceDeallocate must be called
– before a different device can be specified. This is not a limitation since
– a session can only read or write one backup at a time.

– The other major effect of allocating a device is to specify the name space
– for the backup handles (file names). The handle for a sequential file does
– not necessarily define the type of device used to write the file. Thus it
– is necessary to specify the device type in order to interpret the file
– handle. The NULL device type is defined for all systems. It is the file
– system supplied by the operating system. The sequential file handles are
– thus normal file names.

– A device can be specified either by name or by type.
– If the type is specified but not the name, the system picks an
– available device of that type.
– If the name is specified but not the type, the type is determined
– from the device.
– If neither the type or the name is given, the backups are files in
– the operating system file system.

– Note that some types of devices, optical disks for example, can be shared
– by many processes, and thus do not really require allocation of the device
– itself. However we do need to allocate the context for accessing the
– device, and we do need to know the device type for proper interpretation
– of the file handle. Thus it is always necessary to make the device
– allocation call before making most other calls in this package.

– Input parameters:
– type
– If specified, this gives the type of device to use for sequential
– I/O. The allowed types are port specific. For example a port may
– support the type “TAPE” which is implemented via the Oracle tape
– API. If no type is specified, it may be implied by specifying a
– particular device name to allocate. The type should be allowed to
– default to NULL if operating system files are to be used.

– name
– If specified, this names a particular piece of hardware to use for
– accessing sequential files. If not specified, any available
– device of the correct type will be allocated. If the device cannot
– be shared, it is allocated to this session for exclusive use.
– The name should be allowed to default to NULL if operating system
– files are to be used.

– ident
– This is the users identifier that he uses to name this device. It
– is only used to report the status of this session via
– dbms_application_info. This value will be placed in the CLIENT_INFO
– column of the V$SESSION table, in the row corresponding to the
– session in which the device was allocated. This value can also
– be queried with the dbms_application_info.read_client_info procedure.

– noio
– If TRUE, the device will not be used for doing any I/O. This allows
– the specification of a device type for deleting sequential files
– without actually allocating a piece of hardware. An allocation for
– noio can also be used for issuing device commands. Note that some
– commands may actually require a physical device and thus will get
– an error if the allocate was done with noio set to TRUE.

– params
– This string is simply passed to the device allocate OSD. It is
– completely port and device specific.

– Returns:
– It returns a valid device type. This is the type that should be
– allocated to access the same sequential files at a later date. Note
– that this might not be exactly the same value as the input string.
– The allocate OSD may do some translation of the type passed in. The
– return value is NULL when using operating system files.

PROCEDURE restoreControlfileTo(cfname IN varchar2);

– This copies the controlfile from the backup set to an operating system
– file. If the database is mounted, the name must NOT match any of the
– current controlfiles.

– Input parameters:
– cfname
– Name of file to create or overwrite with the controlfile from the
– backup set.

PROCEDURE restoreDataFileTo( dfnumber IN binary_integer
,toname IN varchar2 default NULL);

– restoreDataFileTo creates the output file from a complete backup in the
– backup set.

如果您有兴趣可以去阅读一下这两个文件的注释说明.

解决过程
首先,用控制文件作数据库系统的全备份:

C:WUTemp>rman target /

Recovery Manager: Release 9.2.0.1.0 – Production.

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: DEMO (DBID=3272375326)

RMAN> run {
2> allocate channel C1 type disk;
3> backup full tag ‘FullBackup’ format ‘d:\KDE\%d_%u_%s_%p.dbf’ database include current controlfile;
4> sql ‘ alter system archive log current’;
5> release channel C1;
6> }

using target database controlfile instead of recovery catalog
allocated channel: C1
channel C1: sid=15 devtype=DISK

Starting backup at 18-JUL-04
channel C1: starting full datafile backupset
channel C1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00001 name=D:\ORACLE\ORADATA\DEMO\SYSTEM01.DBF
input datafile fno=00002 name=D:\ORACLE\ORADATA\DEMO\UNDOTBS01.DBF
input datafile fno=00004 name=D:\ORACLE\ORADATA\DEMO\EXAMPLE01.DBF
input datafile fno=00009 name=D:\ORACLE\ORADATA\DEMO\XDB01.DBF
input datafile fno=00005 name=D:\ORACLE\ORADATA\DEMO\INDX01.DBF
input datafile fno=00008 name=D:\ORACLE\ORADATA\DEMO\USERS01.DBF
input datafile fno=00003 name=D:\ORACLE\ORADATA\DEMO\DRSYS01.DBF
input datafile fno=00006 name=D:\ORACLE\ORADATA\DEMO\ODM01.DBF
input datafile fno=00007 name=D:\ORACLE\ORADATA\DEMO\TOOLS01.DBF
channel C1: starting piece 1 at 18-JUL-04
channel C1: finished piece 1 at 18-JUL-04
piece handle=D:\KDE\DEMO_01FR79OT_1_1.DBF comment=NONE
channel C1: backup set complete, elapsed time: 00:01:17
Finished backup at 18-JUL-04

sql statement: alter system archive log current

released channel: C1

如上所示,我们做了一次数据库的Full备份.备份片中包括控制文件.注意上面输出内容的黑体部分.我们在后面的恢复操作中会用到.

模拟错误,关掉实例,删掉所有的控制文件和所有的.DBF文件。然后starup会看到如下的出错信息:

SQL> startup
ORACLE instance started.

Total System Global Area 152115804 bytes
Fixed Size 453212 bytes
Variable Size 100663296 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes
ORA-00205: error in identifying controlfile, check alert log for more info

查看alert Log,应该是系统找不到控制文件.现在情形和客户问题一致.不过在继续讲述之前,我们还需要介绍一点背景知识.

我们首先尝试恢复控制文件:
SQL>startup force nomount;

SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>”,ident=>’T1′);
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreControlfileTo(cfname=>’d:\oracle\Control01.ctl’);
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>’D:\KDE\DEMO_01FR79OT_1_1.DBF’, params=>null);
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /

PL/SQL procedure successfully completed.

OK,控制文件恢复完成.对以上内容的解释:
第五行 分配一个device channel,因为使用的操作系统文件,所以这里为空,如果是从磁带上恢复要用    “sbt_tape”;
第六行 指明开始restore ;
第七行 指出待恢复文件目标存储位置;
第八行 从哪个备份片中恢复;
第九行 释放设备通道.
不妨对以上操作的结果验证一下:

SQL> host dir d:\oracle
Volume in drive D is DATA
Volume Serial Number is DC79-57F8
Directory of d:\oracle

07/18/2004 09:08 PM

.
07/18/2004 09:08 PM ..
06/08/2004 03:21 PM admin
07/18/2004 09:08 PM 1,871,872 CONTROL01.CTL
07/16/2004 11:27 AM ORA92
07/18/2004 09:02 PM oradata

这样,我们成功的restore了控制文件 .如果控制文件在Full备份之后单独做的,接下来关掉实例,拷贝控制文件到具体位置,然后rman 执行restore database;即可。 可是,我们这里的情况有些不同. 视丢失文件的情况而定,继续进行如下的恢复操作:
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate (type=>”,ident=>’t1′);
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>’
d:\oracle\oradata\demo\SYSTEM01.DBF’);
8 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>’
d:\oracle\oradata\demo\UNDOTBS01.DBF’);
9 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>’
d:\oracle\oradata\demo\DRSYS01.DBF’);
10 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>’
d:\oracle\oradata\demo\EXAMPLE01.DBF’);
11 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>’
d:\oracle\oradata\demo\INDX01.DBF’);
12 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>06,toname=>’
d:\oracle\oradata\demo\ODM01.DBF’);
13 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>07,toname=>’
d:\oracle\oradata\demo\TOOLS01.DBF’);
14 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>08,toname=>’
d:\oracle\oradata\demo\USERS01.DBF’);
15 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>09,toname=>’
d:\oracle\oradata\demo\XDB01.DBF’);
16 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>’
D:\KDE\DEMO_01FR79OT_1_1.DBF’, params=>null);
17 sys.dbms_backup_restore.deviceDeallocate;
18 END;
19 /

PL/SQL procedure successfully completed.

–我们的情形是所有的数据文件都丢失了,那就如法炮制 ……….. –文件对应编号来自前面全备份时候的屏幕输出内容.所以,在备份的时候保留操作Log是个很好的习惯.
SQL> startup force mount;
ORACLE instance started.

Total System Global Area 152115804 bytes
Fixed Size 453212 bytes
Variable Size 100663296 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> Recover database using backup controlfile until cancel ;
ORA-00279: change 243854 generated at 07/18/2004 20:57:03 needed for thread 1
ORA-00289: suggestion : D:\KDE\ARC00002.001
ORA-00280: change 243854 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}

D:\KDE\ARC00002.001
ORA-00279: change 244089 generated at 07/18/2004 20:58:18 needed for thread 1
ORA-00289: suggestion : D:\KDE\ARC00003.001
ORA-00280: change 244089 for thread 1 is in sequence #3
ORA-00278: log file ‘D:\KDE\ARC00002.001′ no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> alter database open resetlogs;

Database altered.

最后,不得不resetlogs .

然后,打扫战场,马上进行数据库的全备份。如果您是DBA的话,应该进一步制定并完善备份计划.亡羊补牢,为时未晚。

总结一下
控制文件在备份中意义重大,建议每次对其单独备份,如果数据库版本允许的话,应该设置为控制文件自动备 份。同时应该尽可能地增大CONTROL_FILE_RECORD_KEEP_TIME这个初始化参数的值。以便备份信息能更长时间的保留
应该制定比较完善的备份计划,否则备份计划一旦出现缺口,将可能给系统带来灾难.记住, "可能出错的地方一定会出错".
熟悉RMAN内部备份机制,对DBMS_BACKUP_RESTORE的用法有一定的掌握在关键时侯很有帮助.
备份脚本应该对Log重定向并保存.以便在出错的查找有用信息.

参考信息

RMAN Recovery Without Recovery Catalog or Controlfiles by Bonnie Bizzaro
dbmsbkrs.sql 和 prvtbkrs.plb 文件说明注释(可在你的系统 $ORACLE_HOME/rdbms/admin/中找到.)

相关链接
本文的更多讨论,请看这里 – http://www.itpub.net/244345.html
本文的Blog讨论,请看这里 – http://blog.csdn.net/fenng/archive/2004/07/19/44945.aspx

2004年12月14日

1 删除undotbs01.dbf
2 连接到数据库
SQL> connect sys/oracle as sysdba
Connected.
SQL> startup force
ORACLE instance started.

Total System Global Area 336662768 bytes
Fixed Size 450800 bytes
Variable Size 117440512 bytes
Database Buffers 218103808 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 – see DBWR trace file
ORA-01110: data file 2: ‘/home/oracle/oradata/esal/undotbs01.dbf’
3 查看rollback_segments
SQL> show parameter rollback

NAME TYPE VALUE
———————————— ———– ——————————
fast_start_parallel_rollback string LOW
max_rollback_segments integer 37
rollback_segments string
transactions_per_rollback_segment integer 5
4 修改初始化参数
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=MANUAL
undo_retention=10800
undo_tablespace=UNDOTBS01
rollback_segments=’SYSTEM’
5 启动数据库
SQL> connect sys/oracle as sysdba
Connected.
SQL> startup force
ORACLE instance started.

Total System Global Area 336662768 bytes
Fixed Size 450800 bytes
Variable Size 117440512 bytes
Database Buffers 218103808 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 – see DBWR trace file
ORA-01110: data file 2: ‘/home/oracle/oradata/esal/undotbs01.dbf’


SQL> alter database /home/oracle/oradata/esal/undotbs01.dbf’ offline;
alter database /home/oracle/oradata/esal/undotbs01.dbf’ offline
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE


SQL> alter database datafile ‘/home/oracle/oradata/esal/undotbs01.dbf’ offline drop;

Database altered.

SQL> alter database open;

Database altered.




SQL> create spfile from pfile;

File created.

SQL> create undo tablespace undotbs1 datafile ‘/home/oracle/oradata/esal/undotbs01.dbf’ size 200M autoextend on;
create undo tablespace undotbs1 datafile ‘/home/oracle/oradata/esal/undotbs01.dbf’ size 200M autoextend on
*
ERROR at line 1:
ORA-01543: tablespace ‘UNDOTBS1′ already exists

SQL> drop tablespace undotbs1;

Tablespace dropped.

SQL> create undo tablespace undotbs1 datafile ‘/home/oracle/oradata/esal/undotbs01.dbf’ size 200M autoextend on;

Tablespace created.

SQL> show parameter undo

NAME TYPE VALUE
———————————— ———– ——————————
undo_management string MANUAL
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS01
SQL> alter system set undo_management=auto scope=both;
alter system set undo_management=auto scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL> alter system set undo_management=auto scope=spfile;
alter system set undo_management=auto scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1
#rollback_segments=’SYSTEM’
“initxzh.ora” 99L, 2989C written
[oracle@WWW2 dbs]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 – Production on Mon Jul 5 13:17:17 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys/oracle@xzh as sysdba
Connected.
SQL> startup force
ORACLE instance started.

Total System Global Area 336662768 bytes
Fixed Size 450800 bytes
Variable Size 117440512 bytes
Database Buffers 218103808 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.


感觉楼上的说的让人迷惑,经过试验把步骤总结如下,还请楼上指正:
1。如果发现回退表空间损坏,则先修改pfile文件INITSID.ORA为:
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=MANUAL
undo_retention=10800
undo_tablespace=UNDOTBS01
rollback_segments=’SYSTEM’
2。然后用pfile启动数据库startup mount pfile=”;
alter database datafile ‘/home/oracle/oradata/esal/undotbs01.dbf’ offline drop;
alter database open;
drop tablespace undotbs1 including contents;
create undo tablespace undotbs1 datafile ‘/home/oracle/oradata/esal/undotbs01.dbf’ size 200M autoextend on;
3。修改pfile文件INITSID.ORA为:
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS01
#rollback_segments=’SYSTEM’
4。关闭数据库shutdown immediate
5。启动数据库startup

create or replace trigger tri_xxx_delete
after delete on xxxx
begin
insert into xxx_bak select * from xxx where xxx.id = 困惑ld.id;
end;


更改insert into xxx_bak select * from xxx where xxx.id = : old.id;




Create or replace trigger biud_employee_copy
Before insert or update or delete
On employees_copy
Declare
L_action employees_log.action%type;
Begin
if inserting then
l_action:=’Insert’;
elsif updating then
l_action:=’Update’;
elsif deleting then
l_action:=’Delete’;
else
raise_application_error(-20001,’You should never ever get this error.’);

Insert into employees_log(
Who,action,when)
Values( user, l_action,sysdate);
End;
/








不过有个问题,不是很方便:
SQL> create table test
2 (id number ,
3 user_name varchar2(16)
4 );

Table created.

SQL> create table test_bak as select * from test;

Table created.


SQL> create or replace trigger catch_sql
BEFORE DELETE ON test
FOR EACH ROW
begin
insert into test_bak select * from test where id=:old.id;
end;
/ 2 3 4 5 6 7

Trigger created.

SQL> insert into test values(1,’liubin’);

1 row created.

SQL> insert into test values(2,’test’);

1 row created.

SQL> select * from test;

ID USER_NAME
———- —————-
1 liubin
2 test

SQL> select * from test_bak;

no rows selected

SQL> select * from test_bak;

no rows selected

SQL> delete from test;
delete from test
*
ERROR at line 1:
ORA-04091: table APP.TEST is mutating, trigger/function may not see it
ORA-06512: at “APP.CATCH_SQL”, line 2
ORA-04088: error during execution of trigger ‘APP.CATCH_SQL’


SQL> delete from test where id=1;
delete from test where id=1
*
ERROR at line 1:
ORA-04091: table APP.TEST is mutating, trigger/function may not see it
ORA-06512: at “APP.CATCH_SQL”, line 2
ORA-04088: error during execution of trigger ‘APP.CATCH_SQL’


SQL> create or replace trigger catch_sql
BEFORE DELETE ON test
FOR EACH ROW
declare
v_id number;
v_user_name varchar2(16);
begin
v_id:=:old.id;
v_user_name:=:old.user_name;
insert into test_bak values(v_id,v_user_name);
end;
/ 2 3 4 5 6 7 8 9 10 11 12

Trigger created.


SQL> delete from test;

2 rows deleted.

SQL> select * from test_bak;

ID USER_NAME
———- —————-
1 liubin
2 test

SQL> select * from test;

no rows selected

SQL>
也就是说,在触发器里面不能用select那个记录了,我只好用笨方法把每个值取出来再插进去。
http://download-west.oracle.com/doc…dg13trg.htm#786





不明白,为什么还要加个中间变量,直接使用困惑ld. 的值不就可以了吗?




在触发器中不能访问被触发表,只能用:new和困惑ld访问当前记录的值。