标签云
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,589)
- DB2 (22)
- MySQL (70)
- Oracle (1,459)
- 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备份恢复 (526)
- Oracle安装升级 (83)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (13)
- PostgreSQL恢复 (3)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- 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误删除数据文件恢复
- Oracle 19C 备库DML重定向—DML Redirection
- ORA-01595/ORA-600 4194处理
- 从ORA-00283 ORA-16433报错开始恢复
- 近期又遇到ORA-600 16703和ORA-702故障
- RECOVER_YOUR_DATA勒索恢复
- ORA-01033: ORACLE initialization or shutdown in progress 故障处理
- Oracle 19c/21c最新patch信息-202401
- 存储故障,强制拉库报ORA-600 kcbzib_kcrsds_1处理
- ORA-600 kcrf_resilver_log_1故障处理
月归档:十一月 2016
plsql dev引起的数据库被黑勒索比特币实现原理分析和解决方案
数据库启动alert报错
Mon Jul 10 19:51:24 2016 Errors in file e:\app\administrator\diag\rdbms\zhxh\zhxh\trace\zhxh_ora_3584.trc: ORA-00604: 递归 SQL 级别 1 出现错误 ORA-20313: 你的数据库已被SQL RUSH Team锁死 发送5个比特币到这个地址 166xk1FXMB2g8JxBVF5T4Aw1Z5JaZ6vrSE (大小写一致) 之后把你的Oracle SID邮寄地址 sqlrush@mail.com 我们将让你知道如何解锁你的数据库 Hi buddy, your database was hacked by SQL RUSH Team, send 5 bitcoin to address 166xk1FXMB2g8JxBVF5T4Aw1Z5JaZ6vrSE (case sensitive), after that send your Oracle SID to mail address sqlrush@mail.com, we will let you know how to unlock your database. ORA-06512: 在 "ZHXH.DBMS_SYSTEM_INTERNAL ", line 15 ORA-06512: 在 line 2 Mon Jul 10 19:51:30 2016 OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项 O/S-Error: (OS 1) 函数不正确。 ! Mon Jul 10 19:51:34 2017 Errors in file e:\app\administrator\diag\rdbms\zhxh\zhxh\trace\zhxh_ora_824.trc: ORA-00604: 递归 SQL 级别 1 出现错误 ORA-20313: 你的数据库已被SQL RUSH Team锁死 发送5个比特币到这个地址 166xk1FXMB2g8JxBVF5T4Aw1Z5JaZ6vrSE (大小写一致) 之后把你的Oracle SID邮寄地址 sqlrush@mail.com 我们将让你知道如何解锁你的数据库 Hi buddy, your database was hacked by SQL RUSH Team, send 5 bitcoin to address 166xk1FXMB2g8JxBVF5T4Aw1Z5JaZ6vrSE (case sensitive), after that send your Oracle SID to mail address sqlrush@mail.com, we will let you know how to unlock your database. ORA-06512: 在 "ZHXH.DBMS_SYSTEM_INTERNAL ", line 15 ORA-06512: 在 line 2
afterconnect.sql
是plsql dev登录后自动执行脚本,非Oralce官方脚本
数据库启动后执行触发器DBMS_SUPPORT_INTERNAL
DBMS_SUPPORT_INTERNAL主要的意义是:
1. 当数据库创建时间大于1200天之后,开始备份tab$表
2. 删除tab$中除掉owner#为0和38的记录(sys,xdb)
3. 通过SYS.DBMS_BACKUP_RESTORE.RESETCFILESECTION清理掉备份信息(v$controlfile_record_section)
4. 然后通过DBMS_SYSTEM.KSDWRT在你的alert日志中写上2046次的提示信息
Hi buddy, your database was hacked by SQL RUSH Team, send 5 bitcoin to address 166xk1FXMB2g8JxBVF5T4Aw1Z5JaZ6vrSE (case sensitive), after that send your Oracle SID to mail address sqlrush@mail.com, we will let you know how to unlock your database.
你的数据库已被SQL RUSH Team锁死 发送5个比特币到这个地址 166xk1FXMB2g8JxBVF5T4Aw1Z5JaZ6vrSE (大小写一致) 之后把你的Oracle SID邮寄地址 sqlrush@mail.com 我们将让你知道如何解锁你的数据库
5. 再抛出一个前台的和4类似的警告信息
数据库登录触发器DBMS_SYSTEM_INTERNAL
当你的非SYSTEM,SYSAUX,EXAMPLE之外的所有表的最小统计信息时间大于1200天,而且非C89239.EXE程序,就会报出来” 你的数据库已被SQL RUSH Team锁死 发送5个比特币到这个地址 166xk1FXMB2g8JxBVF5T4Aw1Z5JaZ6vrSE (大小写一致) 之后把你的Oracle SID邮寄地址 sqlrush@mail.com 我们将让你知道如何解锁你的数据库 Hi buddy, your database was hacked by SQL RUSH Team, send 5 bitcoin to address 166xk1FXMB2g8JxBVF5T4Aw1Z5JaZ6vrSE
(case sensitive), after that send your Oracle SID to mail address sqlrush@mail.com, we will let you know how to unlock your database.”的信息
数据库登录触发器DBMS_CORE_INTERNAL
这里比较明显,把表名不含$,不含ORACHK,不是cluster的表放到一个游标里面,然后取非SYSTEM,SYSAUX,EXAMPLE之外的表空间的表的最小统计信息收集时间和当前时间比较如果大于1200天就执行truncate table操作,操作完成之后判断如果登录程序不为C89239.EXE,则报出来异常,” 你的数据库已被SQL RUSH Team锁死 发送5个比特币到这个地址 166xk1FXMB2g8JxBVF5T4Aw1Z5JaZ6vrSE (大小写一致) 之后把你的Oracle SID邮寄地址 sqlrush@mail.com 我们将让你知道如何解锁你的数据库 Hi buddy, your database was hacked by SQL RUSH Team, send 5 bitcoin to address 166xk1FXMB2g8JxBVF5T4Aw1Z5JaZ6vrSE
(case sensitive), after that send your Oracle SID to mail address sqlrush@mail.com, we will let you know how to unlock your database.”。
对于这次故障处理方法
1. 如果SELECT NVL(TO_CHAR(SYSDATE-MIN(LAST_ANALYZED)),0) FROM ALL_TABLES WHERE TABLESPACE_NAME NOT IN (‘SYSTEM’,’SYSAUX’,’EXAMPLE’); 小于1200,查询下列语句,然后删除掉(正常库查询为空)
2. 如果SYSDATE-MIN(LAST_ANALYZED)大于1200, SYSDATE-CREATED大于1200天未重启,或者SYSDATE-CREATED小于1200;就是tab$还未被清理,但是表被truncate,这样情况可以通过oracle原厂dul工具恢复
3. 如果SYSDATE-CREATED大于1200天,而且数据库重启过,但是SYSDATE-MIN(LAST_ANALYZED)小于1200天,那可以直接通过把ORACHK’||SUBSTR(SYS_GUID,10)中备份信息插入到$tab中
4. SYSDATE-CREATED大于1200天,而且数据库重启过,但是SYSDATE-MIN(LAST_ANALYZED)大于1200天,Oracle 原厂dul之类工具结合ORACHK’||SUBSTR(SYS_GUID,10)备份表中数据进行恢复
预防策略
1)数据库里面查询下,如果有这些对象,及时给与清理(注意% ‘中间有空格)
select 'DROP TRIGGER '||owner||'."'||TRIGGER_NAME||'";' from dba_triggers where TRIGGER_NAME like 'DBMS_%_INTERNAL% ' union all select 'DROP PROCEDURE '||owner||'."'||a.object_name||'";' from dba_procedures a where a.object_name like 'DBMS_%_INTERNAL% '; --注意% '之间的空格
2)建议业务用户尽量限制dba 权限
3)检查相关登陆工具的自动运行脚本 清理掉有风险脚本
sqlplus中的glogin.sql/login.sql
toad中的toad.ini
plsql dev中的login.sql/afterconnect.sql
4)建议从官方下载工具,不要使用绿色版/破解版等
如果不幸数据库被感染此种勒索比特币事件,而且无法自行恢复的,可以联系我们给予技术支持
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com
ext3/ext4 超级块修复
创建ext4文件系统
[root@localhost ~]# mkfs.ext4 /dev/sdb1 mke2fs 1.42.9 (28-Dec-2013) Filesystem label= OS type: Linux Block size=4096 (log=2) Fragment size=4096 (log=2) Stride=0 blocks, Stripe width=0 blocks 1310720 inodes, 5242624 blocks 262131 blocks (5.00%) reserved for the super user First data block=0 Maximum filesystem blocks=2153775104 160 block groups 32768 blocks per group, 32768 fragments per group 8192 inodes per group Superblock backups stored on blocks: 32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 4096000 Allocating group tables: done Writing inode tables: done Creating journal (32768 blocks): done Writing superblocks and filesystem accounting information: done [root@localhost ~]# mkdir /sdb [root@localhost ~]# mount /dev/sdb1 /sdb [root@localhost ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/ol-root 36G 4.0G 32G 12% / devtmpfs 1.8G 0 1.8G 0% /dev tmpfs 1.8G 0 1.8G 0% /dev/shm tmpfs 1.8G 8.9M 1.8G 1% /run tmpfs 1.8G 0 1.8G 0% /sys/fs/cgroup /dev/sda1 497M 195M 303M 40% /boot tmpfs 369M 0 369M 0% /run/user/0 /dev/sdb1 20G 45M 19G 1% /sdb
准备测试数据
[root@localhost sdb]# cd /etc/sysctl.d/ [root@localhost sysctl.d]# ls 99-sysctl.conf [root@localhost sysctl.d]# cp 99-sysctl.conf /sdb [root@localhost sysctl.d]# more 99-sysctl.conf # System default settings live in /usr/lib/sysctl.d/00-system.conf. # To override those settings, enter new settings here, or in an /etc/sysctl.d/<name>.conf file # # For more information, see sysctl.conf(5) and sysctl.d(5).
破坏ext4文件系统
[root@localhost ~]# dd if=/dev/zero of=/dev/sdb1 bs=1024 count=5 5+0 records in 5+0 records out 5120 bytes (5.1 kB) copied, 0.00270838 s, 1.9 MB/s [root@localhost ~]# mount /dev/sdb1 /sdb mount: unknown filesystem type '(null)'
日志报错
[ 8868.362628] sd 32:0:1:0: [sdb] Cache data unavailable [ 8868.362632] sd 32:0:1:0: [sdb] Assuming drive cache: write through [ 8868.363714] sdb: sdb1 [ 8868.390297] sd 32:0:1:0: [sdb] Cache data unavailable [ 8868.390301] sd 32:0:1:0: [sdb] Assuming drive cache: write through [ 8868.391462] sdb: sdb1 [ 8900.130143] EXT4-fs (sdb1): mounted filesystem with ordered data mode. Opts: (null) [ 8900.130163] SELinux: initialized (dev sdb1, type ext4), uses xattr [ 8902.803966] sdb1: WRITE SAME failed. Manually zeroing.
fsck修复
[root@localhost ~]# fsck -t ext4 /dev/sdb1 fsck from util-linux 2.23.2 e2fsck 1.42.9 (28-Dec-2013) ext2fs_open2: Bad magic number in super-block fsck.ext4: Superblock invalid, trying backup blocks... /dev/sdb1 was not cleanly unmounted, check forced. Pass 1: Checking inodes, blocks, and sizes Pass 2: Checking directory structure Pass 3: Checking directory connectivity Pass 4: Checking reference counts Pass 5: Checking group summary information Free blocks count wrong for group #1 (31740, counted=31739). Fix<y>? yes Free blocks count wrong (5116302, counted=5116301). Fix<y>? yes Free inodes count wrong for group #0 (8181, counted=8180). Fix<y>? yes Free inodes count wrong (1310709, counted=1310708). Fix<y>? yes /dev/sdb1: ***** FILE SYSTEM WAS MODIFIED ***** /dev/sdb1: 12/1310720 files (0.0% non-contiguous), 126323/5242624 blocks
测试修复结果
[root@localhost ~]# [root@localhost ~]# [root@localhost ~]# mount /dev/sdb1 /sdb [root@localhost ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/ol-root 36G 4.0G 32G 12% / devtmpfs 1.8G 0 1.8G 0% /dev tmpfs 1.8G 0 1.8G 0% /dev/shm tmpfs 1.8G 8.9M 1.8G 1% /run tmpfs 1.8G 0 1.8G 0% /sys/fs/cgroup /dev/sda1 497M 195M 303M 40% /boot tmpfs 369M 0 369M 0% /run/user/0 /dev/sdb1 20G 45M 19G 1% /sdb [root@localhost ~]# cd /sdb [root@localhost sdb]# ls 99-sysctl.conf lost+found [root@localhost sdb]# more 99-sysctl.conf # System default settings live in /usr/lib/sysctl.d/00-system.conf. # To override those settings, enter new settings here, or in an /etc/sysctl.d/<name>.conf file # # For more information, see sysctl.conf(5) and sysctl.d(5).
ext4文件系统修复
[root@localhost ~]# mkfs.ext3 /dev/sdb1 mke2fs 1.42.9 (28-Dec-2013) Filesystem label= OS type: Linux Block size=4096 (log=2) Fragment size=4096 (log=2) Stride=0 blocks, Stripe width=0 blocks 1310720 inodes, 5242624 blocks 262131 blocks (5.00%) reserved for the super user First data block=0 Maximum filesystem blocks=4294967296 160 block groups 32768 blocks per group, 32768 fragments per group 8192 inodes per group Superblock backups stored on blocks: 32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 4096000 Allocating group tables: done Writing inode tables: done Creating journal (32768 blocks): done Writing superblocks and filesystem accounting information: done [root@localhost ~]# mount /dev/sdb1 /sdb [root@localhost ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/ol-root 36G 4.0G 32G 12% / devtmpfs 1.8G 0 1.8G 0% /dev tmpfs 1.8G 0 1.8G 0% /dev/shm tmpfs 1.8G 8.9M 1.8G 1% /run tmpfs 1.8G 0 1.8G 0% /sys/fs/cgroup /dev/sda1 497M 195M 303M 40% /boot tmpfs 369M 0 369M 0% /run/user/0 /dev/sdb1 20G 45M 19G 1% /sdb [root@localhost ~]# dd if=/dev/zero of=/dev/sdb1 bs=1024 count=5 5+0 records in 5+0 records out 5120 bytes (5.1 kB) copied, 0.0138915 s, 369 kB/s [root@localhost ~]# fsck -t ext3 /dev/sdb1 fsck from util-linux 2.23.2 e2fsck 1.42.9 (28-Dec-2013) ext2fs_open2: Bad magic number in super-block fsck.ext3: Superblock invalid, trying backup blocks... /dev/sdb1 was not cleanly unmounted, check forced. Pass 1: Checking inodes, blocks, and sizes Pass 2: Checking directory structure Pass 3: Checking directory connectivity Pass 4: Checking reference counts Pass 5: Checking group summary information /dev/sdb1: ***** FILE SYSTEM WAS MODIFIED ***** /dev/sdb1: 11/1310720 files (0.0% non-contiguous), 126322/5242624 blocks [root@localhost ~]# mount /dev/sdb1 /sdb [root@localhost ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/ol-root 36G 4.0G 32G 12% / devtmpfs 1.8G 0 1.8G 0% /dev tmpfs 1.8G 0 1.8G 0% /dev/shm tmpfs 1.8G 8.9M 1.8G 1% /run tmpfs 1.8G 0 1.8G 0% /sys/fs/cgroup /dev/sda1 497M 195M 303M 40% /boot tmpfs 369M 0 369M 0% /run/user/0 /dev/sdb1 20G 45M 19G 1% /sdb
fsck修复危险性较大,建议先备份对应的分区(dd命令备份分区)然后再处理,有导致分区数据全部或者部分丢失的风险,如果超级块彻底损坏无法恢复,请联系我们,提供专业ORACLE数据库恢复技术支持
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com
File #N is offline, but is part of an online tablespace
在看一个客户的数据库恢复日志的时候发现类似警告(File #N is offline, but is part of an online tablespace.),以前没有注意,这次通过试验来重现该部分内容
创建表空间
SQL> create tablespace readonly datafile '/home/oracle/.oradata/test/readonly01.dbf' size 128M; Tablespace created. SQL> alter tablespace readonly add datafile '/home/oracle/.oradata/test/readonly02.dbf' size 128M; Tablespace altered.
写入数据
SQL> create table t_readonly tablespace readonly as select * from dba_objects; Table created.
read only 表空间
SQL> select count(*) from t_readonly; COUNT(*) ---------- 72226 SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> alter tablespace readonly read only; Tablespace altered. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered.
备份数据库
[oracle@localhost ~]$ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 1 21:15:51 2016 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: TEST (DBID=2210907828) RMAN> backup database format '/home/oracle/full_%U.rman'; Starting backup at 01-NOV-16 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=197 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/home/oracle/.oradata/test/system01.dbf input datafile file number=00002 name=/home/oracle/.oradata/test/sysaux01.dbf input datafile file number=00005 name=/home/oracle/.oradata/test/test01.dbf input datafile file number=00006 name=/home/oracle/.oradata/test/test02.dbf input datafile file number=00007 name=/home/oracle/.oradata/test/readonly01.dbf input datafile file number=00008 name=/home/oracle/.oradata/test/readonly02.dbf input datafile file number=00003 name=/home/oracle/.oradata/test/undotbs01.dbf input datafile file number=00004 name=/home/oracle/.oradata/test/users01.dbf channel ORA_DISK_1: starting piece 1 at 01-NOV-16 channel ORA_DISK_1: finished piece 1 at 01-NOV-16 piece handle=/home/oracle/full_03rjrp0t_1_1.rman tag=TAG20161101T211613 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 01-NOV-16 channel ORA_DISK_1: finished piece 1 at 01-NOV-16 piece handle=/home/oracle/full_04rjrp1m_1_1.rman tag=TAG20161101T211613 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 01-NOV-16 RMAN> sql 'alter system archive log current'; backup as compressed backupset archivelog all format '/home/oracle/arch_%T_%U.rman' delete input; sql statement: alter system archive log current RMAN> Starting backup at 01-NOV-16 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=494 RECID=1 STAMP=926802386 input archived log thread=1 sequence=495 RECID=2 STAMP=926802386 input archived log thread=1 sequence=496 RECID=3 STAMP=926802389 input archived log thread=1 sequence=497 RECID=4 STAMP=926802693 input archived log thread=1 sequence=498 RECID=5 STAMP=926802693 input archived log thread=1 sequence=499 RECID=6 STAMP=926802696 input archived log thread=1 sequence=500 RECID=7 STAMP=926802787 input archived log thread=1 sequence=501 RECID=8 STAMP=926802789 input archived log thread=1 sequence=502 RECID=9 STAMP=926802792 input archived log thread=1 sequence=503 RECID=10 STAMP=926802793 input archived log thread=1 sequence=504 RECID=11 STAMP=926802812 input archived log thread=1 sequence=505 RECID=12 STAMP=926802813 input archived log thread=1 sequence=506 RECID=13 STAMP=926802816 input archived log thread=1 sequence=507 RECID=14 STAMP=926803076 input archived log thread=1 sequence=508 RECID=15 STAMP=926803077 channel ORA_DISK_1: starting piece 1 at 01-NOV-16 channel ORA_DISK_1: finished piece 1 at 01-NOV-16 piece handle=/home/oracle/arch_20161101_05rjrp45_1_1.rman tag=TAG20161101T211757 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 channel ORA_DISK_1: deleting archived log(s) archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_494_d1k4tkot_.arc RECID=1 STAMP=926802386 archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_495_d1k4tln7_.arc RECID=2 STAMP=926802386 archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_496_d1k4tot5_.arc RECID=3 STAMP=926802389 archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_497_d1k544w3_.arc RECID=4 STAMP=926802693 archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_498_d1k545wc_.arc RECID=5 STAMP=926802693 archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_499_d1k548bm_.arc RECID=6 STAMP=926802696 archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_500_d1k5734v_.arc RECID=7 STAMP=926802787 archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_501_d1k5752s_.arc RECID=8 STAMP=926802789 archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_502_d1k578c2_.arc RECID=9 STAMP=926802792 archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_503_d1k579hy_.arc RECID=10 STAMP=926802793 archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_504_d1k57w6s_.arc RECID=11 STAMP=926802812 archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_505_d1k57xj1_.arc RECID=12 STAMP=926802813 archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_506_d1k580hj_.arc RECID=13 STAMP=926802816 archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_507_d1k5j4q0_.arc RECID=14 STAMP=926803076 archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_508_d1k5j4yq_.arc RECID=15 STAMP=926803077 Finished backup at 01-NOV-16 RMAN> backup format '/home/oracle/ctl_%T_%U.rman' current controlfile; Starting backup at 01-NOV-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 01-NOV-16 channel ORA_DISK_1: finished piece 1 at 01-NOV-16 piece handle=/home/oracle/ctl_20161101_06rjrp75_1_1.rman tag=TAG20161101T211933 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 01-NOV-16
清理环境还原数据库
RMAN> shutdown immediate; database closed database dismounted Oracle instance shut down RMAN> exit Recovery Manager complete. [oracle@localhost .oradata]$ mv test test_20161101 [oracle@localhost .oradata]$ mkdir test
还原数据库
[oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 1 21:21:09 2016 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount; ORACLE instance started. Total System Global Area 2421825536 bytes Fixed Size 2215744 bytes Variable Size 1795162304 bytes Database Buffers 603979776 bytes Redo Buffers 20467712 bytes SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@localhost ~]$ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 1 21:21:22 2016 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: TEST (not mounted) RMAN> restore controlfile from '/home/oracle/ctl_20161101_06rjrp75_1_1.rman'; Starting restore at 01-NOV-16 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=63 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/home/oracle/.oradata/test/control01.ctl Finished restore at 01-NOV-16 RMAN> alter database mount; database mounted released channel: ORA_DISK_1 RMAN> restore database; Starting restore at 01-NOV-16 Starting implicit crosscheck backup at 01-NOV-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=63 device type=DISK Crosschecked 3 objects Finished implicit crosscheck backup at 01-NOV-16 Starting implicit crosscheck copy at 01-NOV-16 using channel ORA_DISK_1 Finished implicit crosscheck copy at 01-NOV-16 searching for all files in the recovery area cataloging files... no files cataloged using channel ORA_DISK_1 datafile 5 not processed because file is offline datafile 6 not processed because file is offline channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/.oradata/test/system01.dbf channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/.oradata/test/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/.oradata/test/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/.oradata/test/users01.dbf channel ORA_DISK_1: restoring datafile 00007 to /home/oracle/.oradata/test/readonly01.dbf channel ORA_DISK_1: restoring datafile 00008 to /home/oracle/.oradata/test/readonly02.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/full_03rjrp0t_1_1.rman channel ORA_DISK_1: piece handle=/home/oracle/full_03rjrp0t_1_1.rman tag=TAG20161101T211613 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 Finished restore at 01-NOV-16 RMAN> exit Recovery Manager complete.
通过Oracle Database Recovery Check很明显发现,这里看到文件状态是read only的.
recover database
[oracle@localhost ~]$ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 1 21:28:14 2016 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: TEST (DBID=2210907828, not open) RMAN> recover database; Starting recover at 01-NOV-16 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=63 device type=DISK datafile 7 not processed because file is read-only <<<<=====注意 datafile 8 not processed because file is read-only <<<<=====注意 starting media recovery channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=507 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=508 channel ORA_DISK_1: reading from backup piece /home/oracle/arch_20161101_05rjrp45_1_1.rman channel ORA_DISK_1: piece handle=/home/oracle/arch_20161101_05rjrp45_1_1.rman tag=TAG20161101T211757 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_507_d1k63pj8_.arc thread=1 sequence=507 channel default: deleting archived log(s) archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_507_d1k63pj8_.arc RECID=16 STAMP=926803702 archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_508_d1k63pww_.arc thread=1 sequence=508 channel default: deleting archived log(s) archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_508_d1k63pww_.arc RECID=17 STAMP=926803702 unable to find archived log archived log thread=1 sequence=509 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 11/01/2016 21:28:24 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 509 and starting SCN of 8933048 RMAN> exit
resetlogs 打开数据库
SQL> set numw 16 SQL> SELECT status, checkpoint_change#, 2 3 checkpoint_time,last_change#, count(*) ROW_NUM FROM v$datafile GROUP BY status, checkpoint_change#, checkpoint_time,last_change# ORDER BY status, checkpoint_change#, checkpoint_time; 4 5 6 7 STATUS CHECKPOINT_CHANGE# CHECKPOIN LAST_CHANGE# ROW_NUM ------- ------------------ --------- ---------------- ---------------- ONLINE 8932792 01-NOV-16 2 ONLINE 8933048 01-NOV-16 3 SYSTEM 8933048 01-NOV-16 1 SQL> SELECT status, 2 checkpoint_change#, 3 checkpoint_time,FUZZY, 4 count(*) ROW_NUM 5 FROM v$datafile_header GROUP BY status, checkpoint_change#, checkpoint_time,fuzzy ORDER BY status, checkpoint_change#, checkpoint_time; 6 7 STATUS CHECKPOINT_CHANGE# CHECKPOIN FUZ ROW_NUM ------- ------------------ --------- --- ---------------- ONLINE 8932792 01-NOV-16 NO 2 ONLINE 8933048 01-NOV-16 NO 4 SQL> alter database open resetlogs; Database altered.
alert日志信息
Tue Nov 01 21:29:56 2016 alter database open resetlogs Errors in file /opt/oracle/diag/rdbms/test/test/trace/test_ora_14479.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/home/oracle/.oradata/test/redo01.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Errors in file /opt/oracle/diag/rdbms/test/test/trace/test_ora_14479.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/home/oracle/.oradata/test/redo01.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Errors in file /opt/oracle/diag/rdbms/test/test/trace/test_ora_14479.trc: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/home/oracle/.oradata/test/redo02.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Errors in file /opt/oracle/diag/rdbms/test/test/trace/test_ora_14479.trc: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/home/oracle/.oradata/test/redo02.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Errors in file /opt/oracle/diag/rdbms/test/test/trace/test_ora_14479.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/home/oracle/.oradata/test/redo03.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Errors in file /opt/oracle/diag/rdbms/test/test/trace/test_ora_14479.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/home/oracle/.oradata/test/redo03.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 RESETLOGS after incomplete recovery UNTIL CHANGE 8933048 Resetting resetlogs activation ID 2210869684 (0x83c731b4) Errors in file /opt/oracle/diag/rdbms/test/test/trace/test_ora_14479.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/home/oracle/.oradata/test/redo01.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Errors in file /opt/oracle/diag/rdbms/test/test/trace/test_ora_14479.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/home/oracle/.oradata/test/redo01.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Errors in file /opt/oracle/diag/rdbms/test/test/trace/test_ora_14479.trc: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/home/oracle/.oradata/test/redo02.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Errors in file /opt/oracle/diag/rdbms/test/test/trace/test_ora_14479.trc: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/home/oracle/.oradata/test/redo02.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Errors in file /opt/oracle/diag/rdbms/test/test/trace/test_ora_14479.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/home/oracle/.oradata/test/redo03.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Errors in file /opt/oracle/diag/rdbms/test/test/trace/test_ora_14479.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/home/oracle/.oradata/test/redo03.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Tue Nov 01 21:29:58 2016 Setting recovery target incarnation to 3 Tue Nov 01 21:29:58 2016 Assigning activation ID 2224900353 (0x849d4901) LGWR: STARTING ARCH PROCESSES Tue Nov 01 21:29:58 2016 ARC0 started with pid=20, OS id=14486 ARC0: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC0: STARTING ARCH PROCESSES Tue Nov 01 21:29:59 2016 ARC1 started with pid=21, OS id=14488 Tue Nov 01 21:29:59 2016 ARC2 started with pid=22, OS id=14490 ARC1: Archival started Tue Nov 01 21:29:59 2016 ARC3 started with pid=23, OS id=14492 ARC2: Archival started ARC1: Becoming the 'no FAL' ARCH ARC1: Becoming the 'no SRL' ARCH ARC2: Becoming the heartbeat ARCH Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: /home/oracle/.oradata/test/redo01.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Tue Nov 01 21:29:59 2016 SMON: enabling cache recovery Successfully onlined Undo Tablespace 2. Dictionary check beginning File #7 is offline, but is part of an online tablespace. data file 7: '/home/oracle/.oradata/test/readonly01.dbf' Successfuly brought file #7 online. File #8 is offline, but is part of an online tablespace. data file 8: '/home/oracle/.oradata/test/readonly02.dbf' Successfuly brought file #8 online. Tue Nov 01 21:29:59 2016 Errors in file /opt/oracle/diag/rdbms/test/test/trace/test_dbw0_14226.trc: ORA-01157: cannot identify/lock data file 201 - see DBWR trace file ORA-01110: data file 201: '/home/oracle/.oradata/test/temp01.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Errors in file /opt/oracle/diag/rdbms/test/test/trace/test_dbw0_14226.trc: ORA-01186: file 201 failed verification tests ORA-01157: cannot identify/lock data file 201 - see DBWR trace file ORA-01110: data file 201: '/home/oracle/.oradata/test/temp01.dbf' File 201 not verified due to error ORA-01157 Dictionary check complete Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Re-creating tempfile /home/oracle/.oradata/test/temp01.dbf Database Characterset is AL32UTF8 No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Tue Nov 01 21:30:00 2016 QMNC started with pid=24, OS id=14494 LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete ARC3: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE Completed: alter database open resetlogs
这里看到了我们期待的警告
File #7 is offline, but is part of an online tablespace.
data file 7: ‘/home/oracle/.oradata/test/readonly01.dbf’
Successfuly brought file #7 online.
File #8 is offline, but is part of an online tablespace.
data file 8: ‘/home/oracle/.oradata/test/readonly02.dbf’
Successfuly brought file #8 online.
结论:如果数据库的表空间之前是read only,然后resetlogs操作就会有类似提示(File #N is offline, but is part of an online tablespace).这样的整个恢复过程不影响read only表空间中的数据