ORACLE_基础二十三(User-Managed Recovery)



建表空间
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 APP1

6 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 /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 13 20:03:18 2015

Copyright (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 bytes
Fixed Size                  1218968 bytes
Variable Size              83887720 bytes
Database Buffers          192937984 bytes
Redo Buffers                7168000 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-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-15


SQL> 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-15

SQL> 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 directory
Additional information: 3
Tue Apr 14 01:06:19 2015
Errors in file /u01/app/oracle/admin/king/bdump/king_ckpt_6874.trc:
ORA-01171: datafile 5 going offline due to error advancing checkpoint
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/app/oracle/oradata/king/backup/app1_01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3


ORACLE 发现出错了

SQL> select * from v$recover_file;                                                                                           

     FILE# ONLINE  ONLINE_ ERROR                   CHANGE# TIME
---------- ------- ------- -------------------- ---------- ---------
         5 OFFLINE OFFLINE FILE NOT FOUND                0


//离线数据文件5
SQL> 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">

数据文件丢失 也木有数据文件备份，不过联机重做日志，归档文件，控制文件完好的情况下

startup
SQL> 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.dbf

6 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             677574

6 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             677574

6 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/udump

alter database backup controlfile to trace as '下/u01/admin/c.trc'

[oracle@localhost backup]$ rm contro*   //删除 所有控制文件

修改 备份的控制文件 ， 里边有两部分SQL，根据情况  我们保留第一部分 然后
 
[oracle@localhost udump]$ sqlplus /nolog
SQL> 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 CURRENT

SQL> 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  归档日志文件，也找不到 Cris

SQL> 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 1
ORA-00289: suggestion :
/u01/app/oracle/oradata/king/backup/arc/1_4_877042398.dbf
ORA-00280: change 727879 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                                                                           
ORA-00279: change 728942 generated at 04/15/2015 07:08:26 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/oradata/king/backup/arc/1_5_877042398.dbf
ORA-00280: change 728942 for thread 1 is in sequence #5
ORA-00278: log file '/u01/app/oracle/oradata/king/backup/arc/1_4_877042398.dbf'
no longer needed for this recovery


Specify 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 status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> 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 32767M

SQL> select * from usr1.ta;                                                                                                  

        ID NAME
---------- ----------
         1 wwww
         0 wdwwdw
   

  结果是 应该是  0,1,2都在， 可能是 我的 id=2的记录没有进归档文件， 在跟视频操作的时候  是不是执行漏了

Change-Based Recovery

略