20、oracle用户管理恢复
20、oracle用户管理恢复 下面会一一讲解控制文件、redo文件及非归档模式、归档模式数据文件丢失的情况下,如何恢复数据? (1)控制文件(controlfile)丢失 在做恢复实验之前,先备份好数据。 案例1、模拟一个控制文件丢失 select name from v$controlfile; SQL> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /u02/oracle/oradata/orcl/control01.ctl /u02/oracle/oradata/orcl/control02.ctl 删除其中一个控制文件 rm -rf /u02/oracle/oradata/orcl/control01.ctl alter system switch logfile; create tablespace hello datafile '/u02/oracle/oradata/orcl/hello01.dbf'; SQL> create tablespace hello datafile '/u02/oracle/oradata/orcl/hello01.dbf'; create tablespace hello datafile '/u02/oracle/oradata/orcl/hello01.dbf' * ERROR at line 1: ORA-00210: cannot open the specified control file ORA-00202: control file: '/u02/oracle/oradata/orcl/control01.ctl' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 数据库启动不起来 SQL> startup ORACLE instance started. Total System Global Area 2772574208 bytes Fixed Size 2256472 bytes Variable Size 771752360 bytes Database Buffers 1979711488 bytes Redo Buffers 18853888 bytes ORA-00205: error in identifying control file,check alert log for more info SQL> select status from v$instance; STATUS ------------ STARTED 查看alert log内容如下: tail -f /u02/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log ORA-00210: cannot open the specified control file ORA-00202: control file: '/u02/oracle/oradata/orcl/control01.ctl' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Wed Aug 30 23:30:34 2017 Checker run found 1 new persistent data failures ORA-205 signalled during: ALTER DATABASE MOUNT... 如何恢复控制文件呢? cp /u02/oracle/oradata/orcl/control02.ctl /u02/oracle/oradata/orcl/control01.ctl ALTER DATABASE MOUNT; ALTER DATABASE open; 案例2、控制文件全部丢失。(前提控制文件有备份) alter database backup controlfile to '/u02/ctlbak001';(备份) rm -rf /u02/oracle/oradata/orcl/control01.ctl rm -rf /u02/oracle/oradata/orcl/control02.ctl SQL> startup ORACLE instance started. Total System Global Area 2772574208 bytes Fixed Size 2256472 bytes Variable Size 771752360 bytes Database Buffers 1979711488 bytes Redo Buffers 18853888 bytes ORA-00205: error in identifying control file,check alert log for more info ORA-00202: control file: '/u02/oracle/oradata/orcl/control02.ctl' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 ORA-00210: cannot open the specified control file ORA-00202: control file: '/u02/oracle/oradata/orcl/control01.ctl' 如何恢复控制文件呢? cp /u02/ctlbak001 /u02/oracle/oradata/orcl/control01.ctl cp /u02/ctlbak001 /u02/oracle/oradata/orcl/control02.ctl 启动数据库还是报错 SQL> startup ORACLE instance started. Total System Global Area 2772574208 bytes Fixed Size 2256472 bytes Variable Size 771752360 bytes Database Buffers 1979711488 bytes Redo Buffers 18853888 bytes Database mounted. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SQL> select GROUP#,FIRST_CHANGE#,NEXT_CHANGE# from v$log; GROUP# FIRST_CHANGE# NEXT_CHANGE# ---------- ------------- ------------ 1 1520101 1520104 3 1540267 2.8147E+14 2 1520104 1540267 继续执行如下命令: recover database using backup controlfile; SQL> recover database using backup controlfile; ORA-00279: change 1540268 generated at 08/30/2017 23:34:57 needed for thread 1 ORA-00289: suggestion : /u02/arch/1_51_944544087.dbf ORA-00280: change 1540268 for thread 1 is in sequence #51 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00308: cannot open archived log '/u02/arch/1_51_944544087.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 ORA-00308: cannot open archived log '/u02/arch/1_51_944544087.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> select GROUP#,MEMBER from v$logfile; GROUP# MEMBER -------------------------------------------------------------------------------- 3 /u02/oracle/oradata/orcl/redo03.log 2 /u02/oracle/oradata/orcl/redo02.log 1 /u02/oracle/oradata/orcl/redo01.log 通过上面分析,可以从/u02/oracle/oradata/orcl/redo03.log 恢复控制文件。 继续执行recover database using backup controlfile 命令 SQL> recover database using backup controlfile ORA-00279: change 1540268 generated at 08/30/2017 23:34:57 needed for thread 1 ORA-00289: suggestion : /u02/arch/1_51_944544087.dbf ORA-00280: change 1540268 for thread 1 is in sequence #51 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /u02/oracle/oradata/orcl/redo03.log Log applied. Media recovery complete. 最后执行命令: alter database open resetlogs; 搞定。 方法2:通过rman备份恢复控制文件。 下面通过rman备份控制文件来实验: rman target / RMAN> show all; using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name ORCL are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u02/oracle/app/dbs/snapcf_orcl.f'; # default 可以通过 CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default 看到rman没有自动备份控制文件。 rman手动备份控制文件 backup current controlfile; RMAN> backup current controlfile; Starting backup at 31-AUG-17 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=39 device type=DISK 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 31-AUG-17 channel ORA_DISK_1: finished piece 1 at 31-AUG-17 piece handle=/u02/oracle/app/dbs/02sd9jb1_1_1 tag=TAG20170831T125145 comment=NONE channel ORA_DISK_1: backup set complete,elapsed time: 00:00:01 Finished backup at 31-AUG-17 下面模拟删除所有控制文件 rm -rf /u02/oracle/oradata/orcl/control01.ctl rm -rf /u02/oracle/oradata/orcl/control02.ctl shutdown immediate; startup; 进入rman [oracle@oracle250 ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Thu Aug 31 13:11:34 2017 Copyright (c) 1982,2011,Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (not mounted) 通过rman备份的控制文件路径恢复。 restore controlfile from '/u02/oracle/app/dbs/02sd9jb1_1_1'; RMAN> restore controlfile from '/u02/oracle/app/dbs/02sd9jb1_1_1'; Starting restore at 31-AUG-17 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=21 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete,elapsed time: 00:00:03 output file name=/u02/oracle/oradata/orcl/control01.ctl output file name=/u02/oracle/oradata/orcl/control02.ctl Finished restore at 31-AUG-17 执行命令: alter database mount; alter database open; SQL> alter database open; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open 通过数据文件和控制文件的检查点信息可以看出,数据文件比控制文件的检查点要小。 col FILE for a10; col Name for a20; col CHECKPOINT_CHANGE for a10; select FILE#,Name,CHECKPOINT_CHANGE# from v$datafile; select FILE#,CHECKPOINT_CHANGE# from v$datafile_header; 继续执行recover database using backup controlfile 命令 SQL> select GROUP#,NEXT_CHANGE# from v$log; GROUP# FIRST_CHANGE# NEXT_CHANGE# ---------- ------------- ------------ 1 1540805 2.8147E+14 3 0 0 2 0 0 使用redo日志推进控制文件。 SQL> recover database using backup controlfile; ORA-00279: change 1561276 generated at 08/31/2017 00:16:02 needed for thread 1 ORA-00289: suggestion : /u02/arch/1_1_953424962.dbf ORA-00280: change 1561276 for thread 1 is in sequence #1 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /u02/oracle/oradata/orcl/redo01.log Log applied. Media recovery complete. 最后执行命令: alter database open resetlogs; 搞定。 注意:alter database open resetlogs; 这个命令,表示所有备份都失效了,要重新热备份哦。 方法3:通过trace恢复控制文件 问题:如果控制文件没有备份、rman也没有备份控制文件,只有备份trace文件,如何恢复控制文件呢? 首先,备份 alter database backup controlfile to trace as '/u02/ctlbak.trace'; 然后模拟故障:删除控制文件: rm -rf /u02/oracle/oradata/orcl/control01.ctl rm -rf /u02/oracle/oradata/orcl/control02.ctl 根据trace文件重建控制文件: CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u02/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u02/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u02/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/u02/oracle/oradata/orcl/system01.dbf', '/u02/oracle/oradata/orcl/sysaux01.dbf', '/u02/oracle/oradata/orcl/undotbs01.dbf', '/u02/oracle/oradata/orcl/users01.dbf', '/u02/oracle/oradata/orcl/test01.dbf', '/u02/oracle/oradata/orcl/rrr.dbf', '/u02/oracle/oradata/orcl/tttt.dbf', '/u02/oracle/oradata/orcl/tttt02.dbf', '/u02/oracle/oradata/orcl/tttt03.dbf' CHARACTER SET AL32UTF8 Control file created. 查看数据库状态: SQL> select status from v$instance; STATUS ------------ MOUNTED alter database open; SQL> alter database open; alter database open * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: '/u02/oracle/oradata/orcl/system01.dbf'; SQL> select GROUP#,SEQUENCE#,NEXT_CHANGE# from v$log; 使用redo推进控制文件。 SQL> recover database using backup controlfile; ORA-00279: change 1562427 generated at 08/31/2017 13:44:20 needed for thread 1 ORA-00289: suggestion : /u02/arch/1_1_953472820.dbf ORA-00280: change 1562427 for thread 1 is in sequence #1 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /u02/oracle/oradata/orcl/redo01.log Log applied. Media recovery complete. SQL> alter database open resetlogs; Database altered. 最后也要重新备份数据库。 (2)重做日志丢失 如果丢失重做日志文件组中的某个成员,并且组中至少还有一个成员,影响如下: 不会影响实例的正常操作, 通过删除丢失的日志成员,在添加新成员的方式来恢复。 如果日志切换,该日志已经归档(不是current log),则可以清空该日志组,然后重建丢失的日志成员。 在做实验之前,先做redo log 的多路复用。 select GROUP#,member from v$logfile; GROUP# MEMBER ---------- -------------------------------------------------- 1 /u02/oracle/oradata/orcl/redo01.log 3 /u02/oracle/oradata/orcl/redo03.log 2 /u02/oracle/oradata/orcl/redo02.log 下面多路复用。 alter database add logfile member '/u01/redo/redo01_1.log' to group 1; alter database add logfile member '/u01/redo/redo02_1.log' to group 2; alter database add logfile member '/u01/redo/redo03_1.log' to group 3; alter system switch logfile; 在查询结果如下: select GROUP#,member,status from v$logfile order by 1; SQL> select GROUP#,status from v$logfile order by 1; GROUP# MEMBER STATUS ---------- -------------------------------------------------- ------- 1 /u02/oracle/oradata/orcl/redo01.log 1 /u01/redo/redo01_1.log 2 /u02/oracle/oradata/orcl/redo02.log 2 /u01/redo/redo02_1.log 3 /u02/oracle/oradata/orcl/redo03.log 3 /u01/redo/redo03_1.log 6 rows selected. STATUS为空表示redo可以使用。 select a.GROUP#,a.MEMBERS,a.ARCHIVED,a.STATUS,b.MEMBER,b.STATUS from v$log a,v$logfile b where a.GROUP#=b.GROUP# order by 1,3; 结果如下: SQL> set lines 1200; SQL> select a.GROUP#,v$logfile b where a.GROUP#= b.GROUP# order by 1,3; GROUP# MEMBERS ARC STATUS MEMBER STATUS ---------- ---------- --- ---------------- ---------------------------------------- ------- 1 2 NO CURRENT /u02/oracle/oradata/orcl/redo01.log 1 2 NO CURRENT /u01/redo/redo01_1.log 2 2 YES INACTIVE /u02/oracle/oradata/orcl/redo02.log 2 2 YES INACTIVE /u01/redo/redo02_1.log 3 2 YES INACTIVE /u02/oracle/oradata/orcl/redo03.log 3 2 YES INACTIVE /u01/redo/redo03_1.log 6 rows selected. 案例1:删除日志组成员中一个(不管是不是单前的还是非单前的redolog,下面方法都适用。) rm -rf /u02/oracle/oradata/orcl/redo03.log alter system switch logfile; tail -f /u02/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log 看到报错信息如下: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/u02/oracle/oradata/orcl/redo03.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory 如何解决呢?先删除,在添加。 alter database drop logfile member '/u02/oracle/oradata/orcl/redo03.log'; alter database add logfile member '/u02/oracle/oradata/orcl/redo03.log' to group 3; 看log 没有报错: Thread 1 advanced to log sequence 27 (LGWR switch) Current log# 3 seq# 27 mem# 0: /u01/redo/redo03_1.log Current log# 3 seq# 27 mem# 1: /u02/oracle/oradata/orcl/redo03.log Thu Aug 31 19:28:50 2017 Archived Log entry 53 added for thread 1 sequence 26 ID 0x58497805 dest 1: Archived Log entry 54 added for thread 1 sequence 26 ID 0x58497805 dest 2: 搞定。。。。。。 补充一下: 如果丢非当前的redolog 属于group 3,还可以这样解决: alter database clear logfile group 3; 如果丢当前的redolog 属于group 3,还可以这样解决: 首先切换日志,让当前的redolog不属于group 3。 alter system switch logfile; alter database checkpoint; alter database clear logfile group 3; 案例2: 如果redo日志没有多路复用,如果redo日志丢了,怎么办? alter database drop logfile member '/u01/redo/redo01_1.log'; alter database drop logfile member '/u01/redo/redo02_1.log'; alter database drop logfile member '/u01/redo/redo03_1.log'; 目前redo日志没有多路复用,信息如下: select GROUP#,status from v$logfile order by 1; GROUP# MEMBER STATUS ---------- -------------------------------------------------- ------- 1 /u02/oracle/oradata/orcl/redo01.log 2 /u02/oracle/oradata/orcl/redo02.log 3 /u02/oracle/oradata/orcl/redo03.log
select a.GROUP#,3; GROUP# MEMBERS ARC STATUS MEMBER STATUS ---------- ---------- --- ---------------- -------------------------------------------------- ------- 1 1 YES ACTIVE /u02/oracle/oradata/orcl/redo01.log 2 1 NO CURRENT /u02/oracle/oradata/orcl/redo02.log 3 1 YES ACTIVE /u02/oracle/oradata/orcl/redo03.log 情况1:非当前redo日志丢失。 rm -rf /u02/oracle/oradata/orcl/redo03.log 模拟日志切换 alter system switch logfile; 报错信息: ORA-00312: online log 3 thread 1: '/u02/oracle/oradata/orcl/redo03.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory 执行命令: alter database clear logfile group 3; SQL> alter database clear logfile group 3; alter database clear logfile group 3 * ERROR at line 1: ORA-00350: log 3 of instance orcl (thread 1) needs to be archived ORA-00312: online log 3 thread 1: '/u02/oracle/oradata/orcl/redo03.log' 发现日志没有归档: select a.GROUP#,3; select name,NEXT_CHANGE# from v$archived_log; SQL> select a.GROUP#,3; GROUP# MEMBERS ARC STATUS MEMBER STATUS ---------- ---------- --- ---------------- -------------------------------------------------- ------- 1 1 NO INACTIVE /u02/oracle/oradata/orcl/redo01.log 2 1 NO CURRENT /u02/oracle/oradata/orcl/redo02.log 3 1 NO INACTIVE /u02/oracle/oradata/orcl/redo03.log 执行命令:alter system checkpoint; 使用清空非归档的日志组命令: alter database clear unarchived logfile group 3; SQL> alter database clear unarchived logfile group 3; Database altered. 在看看日志组3,结果有了: [oracle@oracle250 ~]$ ll /u02/oracle/oradata/orcl/redo03.log -rw-r----- 1 oracle dba 52429312 Aug 31 21:29 /u02/oracle/oradata/orcl/redo03.log 在看看信息如下: SQL> select a.GROUP#,3; GROUP# MEMBERS ARC STATUS MEMBER STATUS ---------- ---------- --- ---------------- -------------------------------------------------- ------- 1 1 NO CURRENT /u02/oracle/oradata/orcl/redo01.log 2 1 YES ACTIVE /u02/oracle/oradata/orcl/redo02.log 3 1 YES ACTIVE /u02/oracle/oradata/orcl/redo03.log 搞定了。。。。。。。。。。。。。。。 注意一下:之前的备份失效了,重新进行热备份哦。(因为删除了redo03日志,导致redo03日志没有归档,导致归档scn不连续。因此需要重新备份。) 情况2:如果当前日志组丢了怎么办? select a.GROUP#,3; SQL> select a.GROUP#,3; GROUP# MEMBERS ARC STATUS MEMBER STATUS ---------- ---------- --- ---------------- -------------------------------------------------- ------- 1 1 NO CURRENT /u02/oracle/oradata/orcl/redo01.log 2 1 YES INACTIVE /u02/oracle/oradata/orcl/redo02.log 3 1 YES INACTIVE /u02/oracle/oradata/orcl/redo03.log create table redo_01 as select * from scott.dept; 删除当前日志: rm -rf /u02/oracle/oradata/orcl/redo01.log SQL> startup; ORACLE instance started. Total System Global Area 2772574208 bytes Fixed Size 2256472 bytes Variable Size 771752360 bytes Database Buffers 1979711488 bytes Redo Buffers 18853888 bytes Database mounted. ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u02/oracle/oradata/orcl/redo01.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 数据库启动不了。具体信息上面。如何处理呢? 要利用热备份文件进行恢复,但是要丢失数据。。。。。 利用之前的备份文件还原回去。 之前的备份脚本如下: [oracle@oracle250 ~]$ cat hotbackup.sh sqlplus / as sysdba <<eof !echo 'begin hot backup ' alter database begin backup; !echo 'copy files' !cp -v /u02/oracle/oradata/orcl/system01.dbf /home/oracle/hot_backup/ !cp -v /u02/oracle/oradata/orcl/sysaux01.dbf /home/oracle/hot_backup/ !cp -v /u02/oracle/oradata/orcl/undotbs01.dbf /home/oracle/hot_backup/ !cp -v /u02/oracle/oradata/orcl/users01.dbf /home/oracle/hot_backup/ !cp -v /u02/oracle/oradata/orcl/test01.dbf /home/oracle/hot_backup/ !cp -v /u02/oracle/oradata/orcl/rrr.dbf /home/oracle/hot_backup/ !cp -v /u02/oracle/oradata/orcl/tttt.dbf /home/oracle/hot_backup/ !cp -v /u02/oracle/oradata/orcl/tttt02.dbf /home/oracle/hot_backup/ !cp -v /u02/oracle/oradata/orcl/tttt03.dbf /home/oracle/hot_backup/ !cp -v /u02/oracle/oradata/orcl/temp01.dbf /home/oracle/hot_backup/ !echo 'end backup database' alter database end backup; exit eof 还原数据。 cp /home/oracle/hot_backup/*.dbf /u02/oracle/oradata/orcl/ 执行命令: recover database until cancel; SQL> recover database until cancel; ORA-00279: change 1575684 generated at 08/31/2017 21:51:18 needed for thread 1 ORA-00289: suggestion : /u02/arch/1_34_953475378.dbf ORA-00280: change 1575684 for thread 1 is in sequence #34 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} CANCEL ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01195: online backup of file 1 needs more recovery to be consistent ORA-01110: data file 1: '/u02/oracle/oradata/orcl/system01.dbf' 填写 AUTO | CANCEL都不行,怎么办呢? SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01195: online backup of file 1 needs more recovery to be consistent ORA-01110: data file 1: '/u02/oracle/oradata/orcl/system01.dbf' 也不行。。。。。z在线求解答!!!!!! (3)非归档模式丢失数据文件(不完全恢复---丢数据) 如果数据库处于noarchivelog模式下,如果丢失了任何数据文件,请执行如下任务: 1、如果实例没有关闭,请关闭实例 2、从备份还原整个数据库,包括所有数据文件、控制文件和重做redo文件。 3、打开数据库 4、让用户重新输入自上次备份以来所做的所有更改。 startup mount; alter database noarchivelog;//改成非归档模式!!!!!!! archive log list; 做一次冷备份。 select * from v$log; select current_scn from v$database; 创建一张表,insert 一些数据。 影响:只能还原到备份之前的数据。备份之后的所有操作数据都丢了,好悲吹。这里我就不做实验了。 备注:数据库处于非归档状态,其联机日志循环覆盖使用,这意味着如果数据库需要进行介质恢复应用相关归档, 又由于非归档导致归档文件不存在,那么此时数据库只能执行基于取消的恢复,回退整个数据库到上一次全备份状态。 或者修改数据文件头部信息,来规避缺失归档。但不管怎么样都会丢失数据。 (4)归档模式丢失非关键数据文件 如果丢失某个数据文件,且该文件不属于system或undo表空间,则可以还原并恢复缺少的数据文件。。。。 已经打开归档模式: SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u02/arch Oldest online log sequence 47 Next log sequence to archive 49 Current log sequence 49 scott用户登录,创建一张表: create table test as select * from dept; select table_name,tablespace_name from dba_tables where table_name='TEST' and owner='SCOTT'; SQL> select table_name,tablespace_name from dba_tables where table_name='TEST' and owner='SCOTT'; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ TEST USERS 查看表空间和文件名对应关系: col file_name for a50 col tablespace_name for a10 col file_id for a50 select file_name,tablespace_name,file_id from dba_data_files where tablespace_name='USERS'; FILE_NAME TABLESPACE FILE_ID -------------------------------------------------- ---------- ---------- /u02/oracle/oradata/orcl/users01.dbf USERS ########## 删除users表空间对应的数据文件。 rm -rf /u02/oracle/oradata/orcl/users01.dbf SQL> select * from test; select * from test * ERROR at line 1: ORA-01116: error in opening database file 4 ORA-01110: data file 4: '/u02/oracle/oradata/orcl/users01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 利用热备份的数据 cp [oracle@oracle250 ~]$ cat hotbackup.sh sqlplus / as sysdba <<eof !echo 'begin hot backup ' alter database begin backup; !echo 'copy files' !cp -v /u02/oracle/oradata/orcl/system01.dbf /home/oracle/hot_backup/ !cp -v /u02/oracle/oradata/orcl/sysaux01.dbf /home/oracle/hot_backup/ !cp -v /u02/oracle/oradata/orcl/undotbs01.dbf /home/oracle/hot_backup/ !cp -v /u02/oracle/oradata/orcl/users01.dbf /home/oracle/hot_backup/ !cp -v /u02/oracle/oradata/orcl/test01.dbf /home/oracle/hot_backup/ !cp -v /u02/oracle/oradata/orcl/rrr.dbf /home/oracle/hot_backup/ !cp -v /u02/oracle/oradata/orcl/tttt.dbf /home/oracle/hot_backup/ !cp -v /u02/oracle/oradata/orcl/tttt02.dbf /home/oracle/hot_backup/ !cp -v /u02/oracle/oradata/orcl/tttt03.dbf /home/oracle/hot_backup/ !cp -v /u02/oracle/oradata/orcl/temp01.dbf /home/oracle/hot_backup/ !echo 'end backup database' alter database end backup; exit eof sys用户先执行: alter database datafile 4 offline; 执行copy命令: cp /home/oracle/hot_backup/users01.dbf /u02/oracle/oradata/orcl/users01.dbf alter database datafile 4 online; SQL> alter database datafile 4 online; alter database datafile 4 online * ERROR at line 1: ORA-01113: file 4 needs media recovery ORA-01110: data file 4: '/u02/oracle/oradata/orcl/users01.dbf' 继续执行命令:(对该文件单独恢复!!!) recover datafile 4; SQL> recover datafile 4; ORA-00279: change 1443443 generated at 08/31/2017 23:21:10 needed for thread 1 ORA-00289: suggestion : /u02/arch/1_44_944544087.dbf ORA-00280: change 1443443 for thread 1 is in sequence #44 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00279: change 1447568 generated at 09/01/2017 01:05:39 needed for thread 1 ORA-00289: suggestion : /u02/arch/1_45_944544087.dbf ORA-00280: change 1447568 for thread 1 is in sequence #45 Log applied. Media recovery complete. 执行online SQL> alter database datafile 4 online; Database altered. 继续查询test表; SQL> select * from test; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON test表已经恢复了。。。。。。。搞定。
(5)归档模式丢失系统关键数据文件 如果丢失system或者undo表空间。 处理步骤: 1、shutdown abort关闭实例 2、装卸数据库 (mount状态) 3、还原并恢复丢失的数据文件 4、打开数据库 案例1:假如丢失undo表空间(先备份数据库) SQL> select name from v$datafile; /u02/oracle/oradata/orcl/system01.dbf /u02/oracle/oradata/orcl/sysaux01.dbf /u02/oracle/oradata/orcl/undotbs01.dbf /u02/oracle/oradata/orcl/users01.dbf /u02/oracle/oradata/orcl/test01.dbf /u02/oracle/oradata/orcl/rrr.dbf /u02/oracle/oradata/orcl/tttt.dbf /u02/oracle/oradata/orcl/tttt02.dbf /u02/oracle/oradata/orcl/tttt03.dbf rm -rf /u02/oracle/oradata/orcl/undotbs01.dbf 错误信息: ORA-01110: data file 3: '/u02/oracle/oradata/orcl/undotbs01.dbf' ORA-1157 signalled during: ALTER DATABASE OPEN... SQL> shutdown abort ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 2772574208 bytes Fixed Size 2256472 bytes Variable Size 771752360 bytes Database Buffers 1979711488 bytes Redo Buffers 18853888 bytes Database mounted. ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/u02/oracle/oradata/orcl/undotbs01.dbf' 把之前热备份uodo表空间的数据cp到相关目录: cp /home/oracle/hot_backup/undotbs01.dbf /u02/oracle/oradata/orcl/ 根据上面错误信息恢复数据文件3: SQL> recover datafile 3; ORA-00279: change 1467926 generated at 09/09/2017 08:56:55 needed for thread 1 ORA-00289: suggestion : /u02/arch/1_50_944544087.dbf ORA-00280: change 1467926 for thread 1 is in sequence #50 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00279: change 1475654 generated at 09/09/2017 14:00:58 needed for thread 1 ORA-00289: suggestion : /u02/arch/1_51_944544087.dbf ORA-00280: change 1475654 for thread 1 is in sequence #51 Log applied. Media recovery complete. 最后打开数据库: SQL> alter database open; Database altered. 搞定!!!!!!!!!!!!! (6)归档模式下,用户误删除表(日志挖掘) 通过日志挖掘恢复表。注意:之后的操作都失效了。 1、确保归档模式、确保备份有效。 使用scott模拟误删除,删除emp表 SQL> conn scott/tiger; Connected. SQL> select * from tab; TNAME TABTYPECLUSTERID ------------------------------ ------- ---------- BONUS TABLE DEPT TABLE EMP TABLE SALGRADE TABLE TEST TABLE SQL> drop table emp purge; Table dropped. 2、使用sys用户,日志挖掘,找到误操作准确的scn conn / as sysdba 构造日志挖掘队列语句。 select 'exec dbms_logmnr.add_logfile('''||member||''')' from v$logfile; SQL> select 'exec dbms_logmnr.add_logfile('''||member||''')' from v$logfile; exec dbms_logmnr.add_logfile('/u02/oracle/oradata/orcl/redo03.log') exec dbms_logmnr.add_logfile('/u02/oracle/oradata/orcl/redo02.log') exec dbms_logmnr.add_logfile('/u02/oracle/oradata/orcl/redo01.log') 3、执行构造挖掘语句,把当前3个redo文件都添加到挖掘队列中去。 SQL> exec dbms_logmnr.add_logfile('/u02/oracle/oradata/orcl/redo02.log') PL/SQL procedure successfully completed. SQL> exec dbms_logmnr.add_logfile('/u02/oracle/oradata/orcl/redo01.log') PL/SQL procedure successfully completed. SQL> exec dbms_logmnr.add_logfile('/u02/oracle/oradata/orcl/redo03.log') PL/SQL procedure successfully completed. 4、执行挖掘: exec dbms_logmnr.start_logmnr(); SQL> exec dbms_logmnr.start_logmnr(); PL/SQL procedure successfully completed. 5、查询挖掘出来信息,找出误操作的scn select scn,sql_redo from v$logmnr_contents where seg_name='EMP' and seg_owner='SCOTT' and sql_redo like 'drop table emp%'; SQL> select scn,sql_redo from v$logmnr_contents where seg_name='EMP' and seg_owner='SCOTT' and sql_red o like 'drop table emp%'; SCN ---------- SQL_REDO 1440820 drop table emp purge; 6、停止数据库。 把热备份的数据文件cp到相关目录: cp /home/oracle/hot_backup/*.dbf /u02/oracle/oradata/orcl/ (生产环境要备份好。) 启动数据库到mount状态 startup mount; 7、执行恢复,把数据库恢复到误操作的scn(1440820)的前一刻 1440819 recover database until change 1440819; 8、 resetlogs 方式打开数据库。 alter database open resetlogs; SQL> shutdown abort; ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 2772574208 bytes Fixed Size 2256472 bytes Variable Size 771752360 bytes Database Buffers 1979711488 bytes Redo Buffers 18853888 bytes Database mounted. SQL> recover database until change 1440819; Media recovery complete. SQL> alter database open resetlogs; Database altered. 最后看emp表: SQL> select count(*) from emp; COUNT(*) ---------- 14 emp表找到了。。搞定了。。。。(最后重新备份。) (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |