ORACLE_基础二十三(User-Managed Recovery)
来源：互联网 发布：java 多态 编辑：程序博客网 时间：2024/06/05 21:42
建表空间SQL> create tablespace app1 datafile '/u01/app/oracle/oradata/king/backup/app1_01.dbf' size 100M extent management local uniform segment space management auto;QL> select ts#,name from v$tablespace; TS# NAME---------- ------------------------------ 0 SYSTEM 1 UNDOTBS1 2 SYSAUX 4 USERS 3 TEMP 6 APP16 rows selected.SQL> select file#,ts#,name from v$datafile; FILE# TS# NAME---------- ---------- -------------------------------------------------- 1 0 /u01/app/oracle/oradata/king/backup/system01.dbf 2 1 /u01/app/oracle/oradata/king/backup/undotbs01.dbf 3 2 /u01/app/oracle/oradata/king/backup/sysaux01.dbf 4 4 /u01/app/oracle/oradata/king/backup/users01.dbf 5 6 /u01/app/oracle/oradata/king/backup/app1_01.dbf建用户SQL> create user usr1 identified by usr1 default tablespace app1; User created.赋权限SQL> grant connect , resource to usr1; Grant succeeded.新用户联入[oracle@localhost ~]$ sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 13 20:03:18 2015Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn usr1/usr1 Connected.建表SQL> create table t (id int , name varchar2(10)); Table created.插数据SQL> insert into t values(0,'boobooke'); 1 row created.SQL> commit; 操作系统建备份目录 cold 冷备 hot热备[oracle@localhost backup]$ mkdir cold;[oracle@localhost backup]$ mkdir hot;关闭SQL> shutdown immediate; 切到冷备目录下cp *.* ./cold/ #冷备启动SQL>startup普通用户连入SQL> conn usr1/usr1 Connected.插入第二条记录SQL> insert into t values ('1','boobooke'); 1 row created.//热备份SQL> alter tablespace app1 begin backup; cp app1_01.dbf ./hot/SQL> alter tablespace app1 end backup; SQL> conn usr1/usr1; Connected插入第三条记录SQL> insert into t values(2,'boobooke'); 1 row created.SQL> commit;SQL> conn /as sysdba Connected.SQL> alter system switch logfile; System altered.情况一： 数据库关闭的时候做完整恢复 rm -rf *.dbf 把数据文件删除 SQL> shutdown abort; ORACLE instance shut down. 关闭数据库从cold目录下拷回文件cp *.dbf ..///启动SQL> startup ORACLE instance started.Total System Global Area 285212672 bytesFixed Size 1218968 bytesVariable Size 83887720 bytesDatabase Buffers 192937984 bytesRedo Buffers 7168000 bytesDatabase mounted.ORA-01113: file 1 needs media recoveryORA-01110: data file 1: '/u01/app/oracle/oradata/king/backup/system01.dbf'--需要恢复的文件SQL> select * from v$recover_file; FILE# ONLINE ONLINE_ ERROR CHANGE# TIME---------- ------- ------- -------------------- ---------- --------- 1 ONLINE ONLINE 637400 13-APR-15 2 ONLINE ONLINE 637400 13-APR-15 3 ONLINE ONLINE 637400 13-APR-15 4 ONLINE ONLINE 637400 13-APR-15 5 ONLINE ONLINE 637400 13-APR-15SQL> set autorecovery off; --用手动方式SQL> recover datafile 1; SQL> select * from v$recover_file; FILE# ONLINE ONLINE_ ERROR CHANGE# TIME---------- ------- ------- -------------------- ---------- --------- 2 ONLINE ONLINE 637400 13-APR-15 3 ONLINE ONLINE 637400 13-APR-15 4 ONLINE ONLINE 637400 13-APR-15 5 ONLINE ONLINE 637400 13-APR-15SQL> recover database; //整个库恢复SQL> select * from v$recover_file; SQL> alter database open; ----------------运行中恢复数据文件-------------insert into usr1.t values(3,'boobooke'); commit;SQL> alter system switch logfile; SQL> alter system switch logfile; rm app1_01.dbf //模式损坏 把改文件删掉 SQL> insert into usr1.t values(4,'boobooke'); 1 row created.SQL> commit; Commit complete.SQL> alter system checkpoint; // 日志里会记录出错信息[oracle@localhost bdump]$ pwd/u01/app/oracle/admin/king/bdump[oracle@localhost bdump]$ tail -f alert_king.log Linux Error: 2: No such file or directoryAdditional information: 3Tue Apr 14 01:06:19 2015Errors in file /u01/app/oracle/admin/king/bdump/king_ckpt_6874.trc:ORA-01171: datafile 5 going offline due to error advancing checkpointORA-01116: error in opening database file 5ORA-01110: data file 5: '/u01/app/oracle/oradata/king/backup/app1_01.dbf'ORA-27041: unable to open fileLinux Error: 2: No such file or directoryAdditional information: 3ORACLE 发现出错了SQL> select * from v$recover_file; FILE# ONLINE ONLINE_ ERROR CHANGE# TIME---------- ------- ------- -------------------- ---------- --------- 5 OFFLINE OFFLINE FILE NOT FOUND 0//离线数据文件5SQL> alter database datafile 5 offline; Database altered.拷贝回来cp ./hot/app1_01.dbf .///恢复recover datafile 5;从数据字典看文件状态SQL> select file#,status,name from v$datafile; FILE# STATUS NAME---------- ------- -------------------------------------------------- 1 SYSTEM /u01/app/oracle/oradata/king/backup/system01.dbf 2 ONLINE /u01/app/oracle/oradata/king/backup/undotbs01.dbf 3 ONLINE /u01/app/oracle/oradata/king/backup/sysaux01.dbf 4 ONLINE /u01/app/oracle/oradata/king/backup/users01.dbf 5 OFFLINE /u01/app/oracle/oradata/king/backup/app1_01.dbf重新启用SQL> alter database datafile 5 online; Database altered.原来的数据都还在SQL> select * from usr1.t; ID NAME---------- -------------------------------------------------- 0 boobooke 1 boobooke 2 boobooke 3 boobooke 4 boobooke</pre><pre class="sql" name="code">
数据文件丢失 也木有数据文件备份，不过联机重做日志，归档文件，控制文件完好的情况下
startupSQL> create tablespace app2 datafile '/u01/app/oracle/oradata/king/backup/app02_01.dbf' size 10m;create table usr1.t2(id int, name char(10)) tablespace app2; Table created.SQL> insert into usr1.t2 values(0,'mahee'); 1 row created.SQL> commit; Commit complete.删掉文件oracle@localhost backup]$ pwd/u01/app/oracle/oradata/king/backup[oracle@localhost backup]$ rm app02_01.dbf SQL> col name format a50; SQL> select file#,name from v$datafile order by 1; FILE# NAME---------- -------------------------------------------------- 1 /u01/app/oracle/oradata/king/backup/system01.dbf 2 /u01/app/oracle/oradata/king/backup/undotbs01.dbf 3 /u01/app/oracle/oradata/king/backup/sysaux01.dbf 4 /u01/app/oracle/oradata/king/backup/users01.dbf 5 /u01/app/oracle/oradata/king/backup/app1_01.dbf 6 /u01/app/oracle/oradata/king/backup/app02_01.dbf6 rows selected.SQL> alter database datafile 6 offline; Database altered.SQL> alter database create datafile '/u01/app/oracle/oradata/king/backup/app02_01.dbf'； SQL> recover datafile 6; SQL> alter database datafile 6 online; Database altered.
Loss of control Files
You may need to create control files if :
a.Allcontrol files are lost because of a failure
b. The name of a database needs to be changed
c The current settings in the control file need to be changed
Control Files Behavior
the behavior of multiplexed control files is this:
1. The database writes to all filenames listed for the initailization parameter CONTROL_FILES in the database initialization parameter file.
2. Th database reads only the first file listed in the CONTROL_FILES parameter during database operation.
3 If any of the control files become unavailable during database operation , the instance becomes inopperable and should be aborted.
SCN Scenarios
//从控制文件读SQL> select file#,checkpoint_change# from v$datafile; FILE# CHECKPOINT_CHANGE#---------- ------------------ 1 676993 2 676993 3 676993 4 676993 5 676993 6 6775746 rows selected.//从数据文件读SQL> select file#,checkpoint_change# from v$datafile_header; FILE# CHECKPOINT_CHANGE#---------- ------------------ 1 676993 2 676993 3 676993 4 676993 5 676993 6 6775746 rows selected.
Recovering Control Files
Methods to recover from loss of control file:
1.Use the current control file
2.Create a new control file
3. Use a backup control file
通过被备份好的控制文件来恢复
SQL> alter database backup controlfile to trace; 备份控制文件 默认在：/u01/app/oracle/admin/king/udumpalter database backup controlfile to trace as '下/u01/admin/c.trc'[oracle@localhost backup]$ rm contro* //删除 所有控制文件修改 备份的控制文件 ， 里边有两部分SQL，根据情况 我们保留第一部分 然后 [oracle@localhost udump]$ sqlplus /nologSQL> conn /as sysdba SQL> @/u01/app/oracle/admin/king/udump/king_ora_6554.trc
各个操作和文件的 关系
SQL> create table t3(id int, name char(10)) tablespace app1; Table created.SQL> insert into t3 values(0,'Cris'); 1 row created.SQL> select group#,status from v$log; GROUP# STATUS---------- ---------------- 1 INACTIVE 2 INACTIVE 3 CURRENTSQL> select group#,member from v$logfile; GROUP# MEMBER---------- -------------------------------------------------- 2 /u01/app/oracle/oradata/king/backup/redo02.log 1 /u01/app/oracle/oradata/king/backup/redo01.log 3 /u01/app/oracle/oradata/king/backup/redo03.log[oracle@localhost backup]$ strings redo03.log | grep "Cris";Cris Cris Cris commit之后 写入联机重做文件数据文件木有[oracle@localhost backup]$ strings app1_01.dbf | grep "Cris";[oracle@localhost backup]$ SQL> alter system checkpoint; System altered. 已经写入到数据文件[oracle@localhost backup]$ strings app1_01.dbf | grep "Cris";Cris <Cris <Cris strings 归档日志文件，也找不到 CrisSQL> alter system switch logfile; System altered.strings 归档日志文件 找到了
情景：控制文件丢了 ，数据库结构有变化变化
备份文件
alter database backup controlfile to 'tmp/w1.bin'
删除掉所有的控制文件
关闭数据库 shutdown abort;
把备份的文件拷到原来的位置， 原来有几个文件都还原出来
startup mount
select file#,checkpoint_change# from v$datafile;
select file#,checkpoint_change# from v$datafile_header;
如果运行时间长 v$datafile_header 里的 checkpoint_change# 较新 ， v$datafile 信息来自控制文件，这里的控制文件时原来备份的
v$datafile_header 的信息来自数据文件
recover database using backup controlfile;
可能需要最新的归档文件来恢复，但是最新的日志还木有归档，我们可以根据提示用 联机重做日志来，每个都试
最后有个文件时不识别的，会提示 然后要重命名
select * from v$recover_file;
select file#,name from v$datafile;
alter database rename file '发线的新文件' to '对应的原文件'
这个对应关系可以在alert里边找
recover database using backup controlfile until cancel;
然后根据提示 用联机重做日志
alter database open resetlogs;
About RESETLOGS
Incarnation
Read-Only TS Recovery
Read-only TS Recovery Issues
Special considerations must be taken for read-only tablespaces when:
1.Re-creating a control file
2.Renaming datafiles
3.Using a backup control file
如果控制文件重构 或者使用备份的控制文件 readonly方式的 tablesapce 有如下不同 SQL> alter database backup controlfile to trace as '/tmp/t1.sql'; SQL> alter tablespace app2 read only; Tablespace altered.SQL> alter database backup controlfile to trace as '/tmp/t2.sql'; t2.sql里 在控制文件并木有注册数据文件 ，在后边 用以下语句处理只读表空间-- Files in read-only tablespaces are now named.ALTER DATABASE RENAME FILE 'MISSING00006' TO '/u01/app/oracle/oradata/king/backup/app02_01.dbf';-- Online the files in read-only tablespaces.ALTER TABLESPACE "APP2" ONLINE;
-------------------------------以下内容 都是不完全恢复-------------------------------------------
Situations Requiring IR
1.Complete recovery fails because an archived log is lost
2.All unarchived redo log files and a datafile are lost;
3.User error
a.An important table was dropped.
b.Invalid data was commiteed in a tale
4.Current control file is lost and a backup control file must be used to open the database
Types of IR
1.There are three types of incomplete recovery:
a.Time-based recovery
b.Cancel-based recovery
c.Chnag-based recovery
2.You may need to recover using a restord control file when:
a.Control files are lost
b.Performing incomplete recovery to a point when the database structure is different than the current;
USER recover procedure
1.Shut down an back up the database;
2.Restore all datafiles .Oo not restore the control file, redo logs, password file, or parameter file.
3.Mount the database.
4.Recover the datafiles to a point before the time of failure
5.Open the database with RESETLOGS.
6.Perform a closed database backup
Time -Based Recovery
Scenario:
1.The current time is 12:00 p.m on March 9,2002
.2.The EMPLOYEES table hasbeen dropped
3. The table was dropped at approximately 11:45 a.m.
4.Database activity is minimal because most staff are currently in a meeting.
5.The table must be recovered
Cancel-Based Recovery
cp *.dbf ./cold2/ 关闭数据库后冷备SQL> startup SQL> select * from usr1.ta; ID NAME---------- ---------- 1 wwww 0 wdwwdw SQL> insert into usr1.ta values('2','bkb111'); commit;SQL> alter system checkpoint; SQL> alter system switch logfile;SQL> insert into usr1.ta values(4,'markw'); SQL> commit; SQL> alter system checkpoint; SQL> alter database backup controlfile to trace as '/tmp/wilson.sql' 把 数据库所有的数据文件，重做文件，控制文件删掉。 这个时候只有归档日志文件SQL> shutdown abort; 先把数据文件从备份里拷贝过来cp ./cold2/*.dbf .[oracle@localhost tmp]$ cp wilson.sql w1.sql修改w1.sql因为是不完全恢复， 保留 w1.sql 中第二部分SQL，然后把填充RMAN以后的SQL都删掉 我们手动完成SQL> @/tmp/w1.sql 链上PL/SQL 就执行 这个文件内部自动到nomount状态 SQL> recover database using backup controlfile until cancel ORA-00279: change 727879 generated at 04/15/2015 06:55:05 needed for thread 1ORA-00289: suggestion :/u01/app/oracle/oradata/king/backup/arc/1_4_877042398.dbfORA-00280: change 727879 for thread 1 is in sequence #4Specify log: {<RET>=suggested | filename | AUTO | CANCEL} ORA-00279: change 728942 generated at 04/15/2015 07:08:26 needed for thread 1ORA-00289: suggestion :/u01/app/oracle/oradata/king/backup/arc/1_5_877042398.dbfORA-00280: change 728942 for thread 1 is in sequence #5ORA-00278: log file '/u01/app/oracle/oradata/king/backup/arc/1_4_877042398.dbf'no longer needed for this recoverySpecify log: {<RET>=suggested | filename | AUTO | CANCEL} ORA-00308: cannot open archived log'/u01/app/oracle/oradata/king/backup/arc/1_5_877042398.dbf' 他需要的这个文件 我们木有了，在联机重做日志里边 ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3SQL> alter database open resetlogs; 下边这句 从 wilson.sql 中拷贝来的SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/king/backup/temp01.dbf' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767MSQL> select * from usr1.ta; ID NAME---------- ---------- 1 wwww 0 wdwwdw 结果是 应该是 0,1,2都在， 可能是 我的 id=2的记录没有进归档文件， 在跟视频操作的时候 是不是执行漏了Change-Based Recovery
略
- ORACLE_基础二十三(User-Managed Recovery)
- ORACLE_基础二十二(User-Managed Backup)
- ORACLE_基础十三(user)
- Oracle user managed complete recovery
- Oracle user -Managed incomplete recovery
- ORACLE_基础十九(Backup Recovery)
- Performing User-Managed Database-18.6、About User-Managed Media Recovery
- Recovery Manager And User Managed Backup
- All about Performing User-Managed Database Recovery
- Performing User-Managed Database-18.7、Performing Complete User-Managed Media Recovery
- Overview of RMAN and User-Managed Restore and Recovery
- ORACLE_基础二十(Archiving Mode)
- ORACLE_基础二十四(RMAN RECOVER)
- ORACLE_基础二十五（RMAN Maintenance）
- ORACLE_基础二十六(Import and Export)
- oracle user-maneged recovery（二）
- ORACLE_基础二十一(Oracle Revover Manager Overview)
- Oracle user managed backups
- 解题报告 之 HDU1698 Alice's Chance
- UART接口
- Kernel development
- DPM(Deformable Parts Model)--原理(一)
- nyoj247 虚拟城市的旅行（spfa）
- ORACLE_基础二十三(User-Managed Recovery)
- OpenGL--位图
- DPM(Defomable Parts Model) 源码分析-训练（三）
- 第一篇 为什么要写这个博客
- Android学习笔记之布局
- G.729A--编码--主函数
- boost::bind参数例子使用
- HDU 1081 To The Max
- 终于还是换到了这个新博客
