12.26.06
Posted in Oracle备份恢复 at 2:07 pm by David.Guo
今天比较背,下午上班不久,有开发的说一个开发库异常,无法连接数据库,报实例终止,连接拒绝;
环境为windows 2000+oracle 8174,非归档模式.
偶连上去看了下,还真的是连接拒绝.
由于该服务器上的oracle是8i的,所以只能用svrmgrl了
俺svrmgrl后,用connect internal;连接成功,但是shutdown immediate就报未连接到oracle;
没有办法了,只能shutdown abort,结果很久很久都无法关闭数据库;
查看数据库的log,发现有如下内容的报错:
Tue Dec 26 10:08:37 2006
ORA-3297 signalled during: alter database datafile ‘C:ORACLEORADATAORCGWU…
Tue Dec 26 12:34:51 2006
Errors in file c:oracleadminorcgwbdumporcgwLGWR.TRC:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: ‘C:ORACLEORADATAORCGWREDO02.LOG’
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Tue Dec 26 12:34:51 2006
LGWR: terminating instance due to error 313
Instance terminated by LGWR, pid = 1488
到数据库中去看,我ft,居然三组log file group只剩下一组的物理文件了.
在这种情况下,只能从windows的服务中关闭oracle的服务了;然后再打开oracle的服务;
再次连接到oracle中,数据库并未打开,先shutdown immediate,然后启动数据库到nomout,正常,再mount,正常,alter database open,这个时候就报错了,无法打开redo log file.当然无法打开了,文件都没有了,怎么打开.
不过幸好可以mount开,先给数据库增加1个log file group,成功,然后将log file物理文件丢失的两组log给drop掉,打开数据库.成功了.
检查后发现,俺在这个机器上打开的三个数据库中,全部丢失非处于current的log file,通过检查,发现可能是杀毒软件将物理文件直接给delete了,啥杀毒软件俺就不说了,总之是国产的,以后该如何预防?得好好想想了.唉.
Permalink
12.04.06
Posted in Oracle备份恢复 at 11:07 am by David.Guo
这两天,开发的兄弟在某地和其他系统作接口,不可避免的使用了dblink.
我这边的环境为Oracle 8.1.7.4.0,对方为Oracle9.2.0.1.0,dblink建好以后,作dblink的查询,报错,ora-03106.
俺介入处理,首先俺作了一个select * from v$parameter@dblink,查询是成功的,那么说,整个dblink都是通的;
然后select * from table@dblink,报错,ora-03106,查了下手册,发现可能是由于字符集的问题引起,核对两边的字符集,并无异常.然后作查询,select col1 from table@dblink,这里的col1是不含中文内容的字段,查询成功;然后select col1,col2 fromtable@dblink,这里的col2含有中文内容,查询依然成功,但是如果增加另外一个字段进去,就报错.
严重怀疑是bug,因为整个dblink看起来正常,中文的转换也正常.metalink看看,发现确实会有一个bug,bug id为3982017,看描述如下:
ORA-3106 can occur during a fetch in the server combination: 8i(local_server) < --> 9i(remote1_server)< --> 8i(remote2_serve) This can occur when executing a PL/SQL block which selects a remote synonym (9i) which is created for remote table in 8i(remote2_server) by opening a cursor and fetching data.
从这个描述看,和我的问题比较符合,于是,建议现场升级到9207,可惜现场使用我的升级包,一直无法在服务器上升级成功,直接就是不能安装.
郁闷了,现场兄弟继续修改,原来作查询的table是一个同义词,不查询同义词,直接查询基表,就不存在这样的问题.
至此问题解决,不过还是不明白到底是不是这个bug的问题.有机会再测试下.
Permalink
01.26.06
Posted in Oracle备份恢复 at 4:30 am by dosql
今天跟tzg讨论一个RMAN备份的问题: 如果一个表空间曾经被使用过, 但现在已经不再被任何表使用了, RMAN是不是把那些空间也备份下来?
测试步骤如下:
先CREATE TABLE xxx as select * from xxx
然后做FULL BACKUP
接着是DROP TABLE
再做FULL BACKUP
2者大小完全一样，为了方便，我把FILESPRET设为1的
测试的结果是只要表空间里的Block曾经被使用过, RMAN就会包含这些block.
这个好像有点不太合理, tzg发现了下面的文档, 确定在10gR2里这个问题才解决了. 现在手头没有10g的数据库, 以后有空再测试一下.
4。Unused Block Compression。我们的知识又一次被颠覆，记住，10gR2的RMAN备份是真的不备份那些没有用到的block了，不管是不是以前曾经用过.
By optimizing database backups, only blocks currently used by the database are backed up. In previous releases, NULL compression was utilized, whereby only never-used blocks were excluded from backup. With this new feature, all unused blocks, whether they have been used or not in the past, will be excluded from backup.
The only compression during RMAN backups is that it skips blocks that have never been used (uninitialized) by Oracle i.e. RMAN performans a sort of ‘unused blocks compression’. If a block has been previously used by Oracle and is now empty or unallocated, it will be included by RMAN in the backup. Well, this kind of makes sense since RMAN is desgined to perform backups even when the database is closed. In a closed database backup situation, RMAN does not have access to the data dictionary views to determine if the block is part of a segment or not.
Permalink
12.07.05
Posted in Oracle备份恢复 at 11:58 pm by dosql
用法示例:
(1) 找出删除前的数据:
SELECT ename, sal
FROM emp
AS OF TIMESTAMP (SYSTIMESTAMP –
INTERVAL ‘6’
MINUTE)
WHERE ename = ‘ALLEN’;
Read the rest of this entry »
Permalink
11.30.05
Posted in Oracle备份恢复 at 1:34 pm by David.Guo
- 软件环境
操作系统: Sun Solaris 2.8
Oracle版本: 9.2.0.6.0
- 原来的备份方法
Oracle手动在线备份
存在问题: 由于数据库已经达到600G, 手动在线备份要运行6个小时左右. 一方面在备份的时候整个Server都会很慢, 影响其他程序的运行. 另一方面由于要满足备份的要求, 晚上的作业就会排得很挤. 这样一旦晚上的作业出错了, 就很有可能会影响白天的运行.
- Point-in-Time (PIT) copy是如何工作的
<a href="http://www.oracledba.com.cn/blog/archives/3#more-3">Read the rest of this entry »</a>
Permalink
« Previous entries ·