标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,598)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
月归档:一月 2015
windows rman自动备份并传输到远程服务器处理方法
在linux中,要使用rman备份后传输到远程服务器上,可以选择ftp,scp,nfs等方式实现,在win主机上可以配置ftp或者共享实现.linux的解决方法已经很多,这里重点提供win上面实现rman备份且传输到远程服务器的解决方法,简单实现异地备份方法:
1.win配置共享目录,而且设置远程服务器有写权限,如果省事可以配置everyone有读写权限
2.创建相关备份目录,这里主要是rmanfile,rmanscript,rmanlog
3.编写rman备份脚本
CONFIGURE RETENTION POLICY TO REDUNDANCY = 7; CONFIGURE DEVICE TYPE DISK PARALLELISM 4; CONFIGURE DEFAULT DEVICE TYPE TO DISK; backup as compressed backupset database format 'E:\backup_db\rmanfile\full_%T_%U.rman'; sql 'alter system archive log current'; backup as compressed backupset archivelog all format 'E:\backup_db\rmanfile\arch_%T_%U.rman' delete input; DELETE noprompt OBSOLETE; crosscheck backup; delete noprompt expired backup; backup format 'E:\backup_db\rmanfile\ctl_%T_%U.rman' current controlfile; backup spfile format 'E:\backup_db\rmanfile\spfile_%T_%U.rman' ; exit;
4.调用rman备份脚本
rman target / cmdfile=E:\backup_db\scriptfile\backup_db.rman log=E:\backup_db\logfile\rmanlog_%date:~0,4%%date:~5,2%%date:~8,2%.log
5.拷贝到远程脚本
需要注意是按照备份集中的日期作为标记来删除的,也就是说,一次备份最好不要跨天
copy /y e:\backup_db\rmanfile\*_%date:~0,4%%date:~5,2%%date:~8,2%_*.RMAN \\192.168.13.40\oracle_backup
6.删除远程服务器N天前备份脚本
需要注意是按照备份集中的日期作为标记来删除的,也就是说,一次备份最好不要跨天
@echo off set DaysAgo=5 call :DateToDays %date:~0,4% %date:~5,2% %date:~8,2% PassDays set /a PassDays-=%DaysAgo% call :DaysToDate %PassDays% DstYear DstMonth DstDay del \\192.168.13.40\oracle_backup\*_%DstYear%%DstMonth%%DstDay%_*.RMAN goto :eof :DateToDays %yy% %mm% %dd% days setlocal ENABLEEXTENSIONS set yy=%1&set mm=%2&set dd=%3 if 1%yy% LSS 200 if 1%yy% LSS 170 (set yy=20%yy%) else (set yy=19%yy%) set /a dd=100%dd%%%100,mm=100%mm%%%100 set /a z=14-mm,z/=12,y=yy+4800-z,m=mm+12*z-3,j=153*m+2 set /a j=j/5+dd+y*365+y/4-y/100+y/400-2472633 endlocal&set %4=%j%&goto :EOF :DaysToDate %days% yy mm dd setlocal ENABLEEXTENSIONS set /a a=%1+2472632,b=4*a+3,b/=146097,c=-b*146097,c/=4,c+=a set /a d=4*c+3,d/=1461,e=-1461*d,e/=4,e+=c,m=5*e+2,m/=153,dd=153*m+2,dd/=5 set /a dd=-dd+e+1,mm=-m/10,mm*=12,mm+=m+3,yy=b*100+d-4800+m/10 (if %mm% LSS 10 set mm=0%mm%)&(if %dd% LSS 10 set dd=0%dd%) endlocal&set %2=%yy%&set %3=%mm%&set %4=%dd%&goto :EOF
7.配置计划任务,让定时执行相关脚本
升级数据库到10.2.0.5遭遇ORA-00918: column ambiguously defined
一个数据库从10201升级到10205之后,出现ORA-00918错误,查询mos发现在以前版本中是bug,Oracle好像在10205中把它修复了,结果就是以前应用的sql无法正常执行.这次升级的结果就是客户晚上3点联系开发商紧急修改程序。再次提醒:再小的系统数据库升级都需要做,功能测试,SPA测试,确保升级后功能和性能都正常.
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for 64-bit Windows: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production
执行报错ORA-00918
多个表JOIN连接,由于在select中的列未指定表名,而且该列在多个表中有,因此在10205中报ORA-00918错误,Oracle认为在以前的版本中是 Bug 5368296: SQL NOT GENERATING ORA-918 WHEN USING JOIN. 升级到10.2.0.5, 11.1.0.7 and 11.2.0.2版本,需要注意此类问题。修复bug没事,但是修复了之后导致系统需要修改sql才能够运行,确实让人很无语
SQL> set autot trace SQL> set lines 100 SQL> SELECT yz_id, item_code, DECODE (yzlx, 0, '长期医嘱', '临时医嘱') yzlx, 2 item_name, gg, sl || sldw sl, zyjs, yf, a.pc, zbj, zbh, 3 TO_CHAR (dcl, 'fm9999990.009') || dcldw dcl, a.bz, lb, zyh,ch,xm, 4 bq, cfh, lrysdm, lrysxm, lrrq, hdrdm, hdrxm, hdrq, sender_code, 5 sender_name, send_date, tzysdm, tzysxm, tzrq, ksrq, zxfy, 6 lb_yp_yl, zsq_code 7 FROM op.yz a LEFT OUTER JOIN op.pc b 8 ON NVL (TRIM (UPPER (a.pc)), ' ') = NVL (TRIM (UPPER (b.pc)), ' ') 9 LEFT JOIN op.zy p ON a.zyh = p.zyh 10 WHERE p.cy='在院' AND p.new_patient='1' 11 AND upper(nvl(p.bj,1))<> 'Y' 12 AND (state = '已核对') 13 AND is_in_bill IS NULL 14 ORDER BY ksrq, yz_id ; bq, cfh, lrysdm, lrysxm, lrrq, hdrdm, hdrxm, hdrq, sender_code, * ERROR at line 4: ORA-00918: column ambiguously defined SQL> select COLUMN_NAME,TABLE_NAME from DBA_tab_columns where column_name='BQ' 2 AND TABLE_NAME IN('YZ','ZY','PC'); COLUMN_NAME TABLE_NAME ------------------------------ ------------------------------ BQ ZY BQ YZ
10.2.0.1中执行正常
E:\>sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 1月 3 14:09:51 2015 Copyright (c) 1982, 2005, Oracle. All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options SQL> set autot trace SQL> set lines 100 SQL> SELECT yz_id, item_code, DECODE (yzlx, 0, '长期医嘱', '临时医嘱') yzlx, 2 item_name, gg, sl || sldw sl, zyjs, yf, a.pc, zbj, zbh, 3 TO_CHAR (dcl, 'fm9999990.009') || dcldw dcl, a.bz, lb, zyh,ch,xm , 4 bq, cfh, lrysdm, lrysxm, lrrq, hdrdm, hdrxm, hdrq, sender_code, 5 sender_name, send_date, tzysdm, tzysxm, tzrq, ksrq, zxfy, 6 lb_yp_yl, zsq_code 7 FROM op.yz a LEFT OUTER JOIN op.pc b 8 ON NVL (TRIM (UPPER (a.pc)), ' ') = NVL (TRIM (UPPER (b.pc)), ' ') 9 LEFT JOIN op.zy p ON a.zyh = p.zyh 10 WHERE p.cy='在院' AND p.new_patient='1' 11 AND upper(nvl(p.bj,1))<> 'Y' 12 AND (state = '已核对') 13 AND is_in_bill IS NULL 14 ORDER BY ksrq, yz_id ; 已选择19804行。 执行计划 ---------------------------------------------------------- ERROR: ORA-00604: 递归 SQL 级别 2 出现错误 ORA-16000: 打开数据库以进行只读访问 SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 41945 consistent gets 0 physical reads 0 redo size 2075973 bytes sent via SQL*Net to client 14989 bytes received via SQL*Net from client 1322 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 19804 rows processed
10.2.0.5库中同名列增加表名前缀执行OK
1 SQL> set autot trace SQL> set lines 100 SQL> SELECT yz_id, item_code, DECODE (yzlx, 0, '长期医嘱', '临时医嘱') yzlx, 2 item_name, gg, sl || sldw sl, zyjs, yf, a.pc, zbj, zbh, 3 TO_CHAR (dcl, 'fm9999990.009') || dcldw dcl, a.bz, lb,zyh,ch,xm, 4 a.bq, cfh, lrysdm, lrysxm, lrrq, hdrdm, hdrxm, hdrq, sender_code, 5 sender_name, send_date, tzysdm, tzysxm, tzrq, ksrq, zxfy, 6 lb_yp_yl, zsq_code 7 FROM op.yz a LEFT OUTER JOIN op.pc b 8 ON NVL (TRIM (UPPER (a.pc)), ' ') = NVL (TRIM (UPPER (b.pc)), ' ') 9 LEFT JOIN op.zy p ON a.zyh = p.zyh 10 WHERE p.cy='在院' AND p.new_patient='1' 11 AND upper(nvl(p.bj,1))<> 'Y' 12 AND (state = '已核对') 13 AND is_in_bill IS NULL 14 ORDER BY ksrq, yz_id ; 20629 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3468887510 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 2580 | 2968 (2)| 00:00:36 | | 1 | SORT ORDER BY | | 10 | 2580 | 2968 (2)| 00:00:36 | |* 2 | HASH JOIN OUTER | | 10 | 2580 | 2967 (2)| 00:00:36 | |* 3 | TABLE ACCESS BY INDEX ROWID| YZ | 3 | 672 | 42 (0)| 00:00:01 | | 4 | NESTED LOOPS | | 10 | 2390 | 2963 (2)| 00:00:36 | |* 5 | TABLE ACCESS FULL | ZY | 3 | 45 | 2917 (2)| 00:00:36 | |* 6 | INDEX RANGE SCAN | DZBLYZ_ZYH | 118 | | 2 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | PC | 33 | 627 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(NVL(TRIM(UPPER("A"."PC")),' ')=NVL(TRIM(UPPER("B"."PC"(+))),' ')) 3 - filter("A"."STATE"='已核对' AND "A"."IS_IN_BILL" IS NULL) 5 - filter("P"."CY"='在院' AND UPPER(NVL("P"."BJ",'1'))<>'Y' AND "P"."NEW_PATIENT"='1') 6 - access("A"."ZYH"="P"."ZYH") Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 42121 consistent gets 0 physical reads 0 redo size 2181383 bytes sent via SQL*Net to client 15617 bytes received via SQL*Net from client 1377 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 20629 rows processed
Bug 5368296: SQL NOT GENERATING ORA-918 WHEN USING JOIN
Bug 12388159 : SQL REPORTING ORA00918 AFTER UPGRADE TO 10.2.0.5.0
再次提醒:再小的系统数据库升级都需要做,功能测试,SPA测试,确保升级后功能和性能都正常.