应用归档文件恢复测试

实验目的，通过恢复来演示归档文件的作用；归档模式；

测试：针对SCOTT.EMP1表进行操作，DML,DDL操作；

         针对表所在的表空间进行备份；

         切换日志，确保中途的某一次插入数据的操作，切换到一个归档文件，然后破坏这个归档文件；

        再切换几次日志，模拟真实环境；

        最后破坏数据文件；  ---使用RMAN工具恢复

准备工作：开启归档：

alter database archivelog    --数据库一致性关闭后，启动到Mount阶段；

alter system set log_archive_dest_2='location=/u01/recovery/';   ---一般dest_1就OK，可以指定多个归档文件，没有意义，两个归档目录就可以了，相当于一个日志组的两个成员互为备份；

archive log list  --归档模式查询，是否开启归档；

SQL> show parameter log log_archive_format                 

  string      %t_%s_%r.dbf

1_19_958808441.dbf       arch_1_21_958808441.log

alter system switch logfile;
SQL> select group#,sequence#,thread#,status from v$log;
    GROUP#  SEQUENCE#    THREAD# STATUS
---------- ---------- ---------- ----------------
         1         18          1 INACTIVE
         2         20          1 INACTIVE
         4         19          1 INACTIVE
         5         21          1 ACTIVE
         6         22          1 CURRENT

%t =>日志，线程的ID号，单实例都是1，RAC；多实例；

%s=>sequenct#，日志序列号写入归档名称，通过序列号快速找到归档日志

%r=>resetlogs =>alter database open resetlogs,代表数据库新的周期；       

日志重置，resetlogs从1开始，才会改变，否则统一

准备工作完成：开始测试：

查询相关视图：

SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where USERNAME='SCOTT';

USERNAME                       DEFAULT_TABLESPACE       TEMPORARY_TABLESPACE

------------------------------ ------------------------------  ------------------------------

SCOTT                                        USERS                                          TEMPTS1

SQL> conn scott/tiger

SQL> create table emp1 as select * from emp;

SQL> select count(*) from emp;

  COUNT(*) ----------         14

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/recovery/

cd /u01/recovery/      ls --查看

**使用RMAN 备份对应表空间：

[oracle@yang recovery]$ rman target /

RMAN> backup tablespace users;

RMAN> list backup of tablespace users;

  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  4       Full 437609     31-OCT-17 /picclife/app/hukou/data/users01.dbf

--继续操作；alter system switch logfile;     /      /        / 切换几次归档文件后，

 SQL> select group#,sequence#,thread#,status from v$log; --查询当前日志组；

         5         41          1 CURRENT           ---

--插入数据文件;

SQL> insert into emp1 select * from emp;

SQL> commit;

SQL> select count(*) from emp1;

  COUNT(*) ----------         28

 alter system switch logfile;   /  /  /   /

--破坏：

rm -f /picclife/app/hukou/data/users01.dbf

SQL> alter system flush buffer_cache;

--访问emp1表：

select * from emp1
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/picclife/app/hukou/data/users01.dbf'

--使用RMAN ：恢复：

001：restore 还原：  RMAN> restore datafile 4;  报错；先对非关键性表空间offline操作，此时数据库还是Open状态；

RMAN> sql 'alter database datafile 4 offline';

sql statement: alter database datafile 4 offline

RMAN> restore datafile 4;

--查询数据文件头部记录的SCN号：

SQL> select name,checkpoint_change# from v$datafile_header;

NAME -------------------------CHECKPOINT_CHANGE#

/picclife/app/hukou/data/undotbs01.dbf             437716

/picclife/app/hukou/data/users01.dbf             437609  为啥小，使用的是备份片还原来的，旧的；

RMAN> recover datafile 4;

RMAN> sql 'alter tablespace users online';

SQL> select count(*) from emp1;

  COUNT(*) ----------         28

以上是归档完整的情况，可以完全恢复数据文件；

如下我需要破坏一个归档文件；形成四个情况：

 A备份节点，表emp1，刚刚创建                    14条数据

SQL> drop table emp1 purge;

SQL> create table emp1 as select * from emp;

SQL> select count(*) from emp1;

  COUNT(*) ----------         14            此时14条记录

--查询创建的表，存储的数据所在的表空间：

SQL> select TABLESPACE_NAME from user_segments where SEGMENT_NAME='EMP1';

TABLESPACE_NAME ------------------------------ USERS

--备份：oracle: rman target /

RMAN> backup tablespace users;

RMAN> list backup of tablespace users;

 List of Datafiles in backup set 3
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  4       Full 444188     31-OCT-17 /picclife/app/hukou/data/users01.dbf

--生成归档文件，查询A操作产生的归档文件名称：

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/recovery/
Next log sequence to archive   69

alter system switch logfile;

[oracle@yang recovery]$ ls *69*
arch_1_69_958808441.log

B:  时间节点                                       28条数据

-操作，插入数据，commit<统计数据量，确定归档日志文件；

SQL> insert into emp1 select * from emp1;

SQL> commit;

SQL> select count(*) from emp1;

  COUNT(*) ----------         28

SQL> archive log list

Archive destination            /u01/recovery/

Next log sequence to archive   70

alter system switch logfile;

[oracle@yang recovery]$ ls *70*
arch_1_70_958808441.log

alter system switch logfile;  切换几次日志

C节点：delete emp1 where rownum<20;        9条数据

19 rows deleted.

SQL> commit;

SQL> select count(*) from emp1;

  COUNT(*) ----------          9

SQL> archive log list

Next log sequence to archive   71

SQL> alter system switch logfile;

 [oracle@yang recovery]$ ls *71*
arch_1_71_958808441.log

alter system switch logfile;  / /切换几次日志

D节点                   ---查询18条数据

SQL> insert into emp1 select * from emp1;

SQL> commit;

SQL> select count(*) from emp1;

  COUNT(*) ----------         18

alter system switch logfile;

SQL> archive log list

Next log sequence to archive   77

****破坏：删除C节点操作产生的归档文件；  因为设置两份归档，因此只破坏一个归档文件，还是可以恢复；

SQL> select NAME from v$archived_log where name like '%71%';

NAME --------------------------------------------------------------------------------

/u01/guidang/arch_1_71_958808441.log
/u01/recovery/arch_1_71_958808441.log

[oracle@yang recovery]$ rm /u01/guidang/arch_1_71_958808441.log
[oracle@yang recovery]$ ls /u01/guidang/arch_1_71_958808441.log
ls: /u01/guidang/arch_1_71_958808441.log: No such file or directory
[oracle@yang recovery]$ rm /u01/recovery/arch_1_71_958808441.log
[oracle@yang recovery]$ ls /u01/recovery/arch_1_71_958808441.log
ls: /u01/recovery/arch_1_71_958808441.log: No such file or directory

以上破坏了归档文件，继续破坏，删除数据文件；

[oracle@yang guidang]$ rm /picclife/app/hukou/data/users01.dbf

alter system flush buffer_cache;

select count(*) from scott.emp1;

ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/picclife/app/hukou/data/users01.dbf'

请问使用备份，进行恢复，能恢复到哪个节点：

A节点：14 

B节点：28

C节点：9 ---产生的归档文件删除了

D节点：18

实际操作：RMAN> sql 'alter tablespace users offline';

RMAN> restore datafile 4;

RMAN> recover datafile 4;

Starting recover at 31-OCT-17 using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 69 is already on disk as file /u01/guidang/arch_1_69_958808441.log archived log for thread 1 with sequence 70 is already on disk as file /u01/guidang/arch_1_70_958808441.log archived log for thread 1 with sequence 72 is already on disk as file /u01/guidang/arch_1_72_958808441.log archived log for thread 1 with sequence 73 is already on disk as file /u01/guidang/arch_1_73_958808441.log archived log for thread 1 with sequence 74 is already on disk as file /u01/guidang/arch_1_74_958808441.log archived log for thread 1 with sequence 75 is already on disk as file /u01/guidang/arch_1_75_958808441.log archived log for thread 1 with sequence 76 is already on disk as file /u01/guidang/arch_1_76_958808441.log RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 10/31/2017 19:59:49 RMAN-06053: unable to perform media recovery because of missing log RMAN-06025: no backup of archived log for thread 1 with sequence 71 and starting SCN of 444273 found to restore

RMAN> sql 'alter tablespace users online';

 ORA-01110: data file 4: '/picclife/app/hukou/data/users01.dbf'

SQL> alter tablespace users online;

alter tablespace users online * ERROR at line 1:

ORA-01113: file 4 needs media recovery

ORA-01110: data file 4: '/picclife/app/hukou/data/users01.dbf'

SQL> recover datafile 4; ORA-00279: change 444188 generated at 10/31/2017 19:46:56 needed for thread 1 ORA-00289: suggestion : /u01/recovery/arch_1_69_958808441.log ORA-00280: change 444188 for thread 1 is in sequence #69

Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00279: change 444226 generated at 10/31/2017 19:47:41 needed for thread 1 ORA-00289: suggestion : /u01/recovery/arch_1_70_958808441.log ORA-00280: change 444226 for thread 1 is in sequence #70

ORA-00279: change 444273 generated at 10/31/2017 19:49:52 needed for thread 1 ORA-00289: suggestion : /u01/recovery/arch_1_71_958808441.log ORA-00280: change 444273 for thread 1 is in sequence #71

ORA-00308: cannot open archived log '/u01/recovery/arch_1_71_958808441.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3

--------------------------------------如上遇到一个情况，一个数据文件，使用备份还原，但是缺失归档，导致控制文件与数据文件记录的SCN号不一致，无法被读取，这种情况下，局部应用redo recover无法取得效果，重启数据库启动到Mount状态，使用数据库级别的recover尝试

SQL> shutdown immediate;

SQL> startup mount;

SQL> select file#,name,checkpoint_change#,last_change#,status from v$datafile;

 3 /picclife/app/hukou/data/undotbs01.dbf             445828       445828 ONLINE

4 /picclife/app/hukou/data/users01.dbf                  444497       444497 OFFLINE

SQL> select file#,name,checkpoint_change#,status from v$datafile_header；

 3 /picclife/app/hukou/data/undotbs01.dbf             445828 ONLINE

         4                                                                            0 OFFLINE

SQL> alter database datafile 4 online;

SQL> select file#,name,checkpoint_change#,status from v$datafile_header；

 3 /picclife/app/hukou/data/undotbs01.dbf             445828 ONLINE

 4  /picclife/app/hukou/data/users01.dbf               444273 ONLINE --使用的是备份的数据文件还原回来的，因此，SCN小

alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/picclife/app/hukou/data/users01.dbf'


SQL> recover datafile 4; 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/recovery/arch_1_71_958808441.log'

cancel
Media recovery cancelled.

RMAN> run{
2> set until sequence 71;
3> restore database;
4> recover database;};

RMAN-03002: failure of restore command at 11/01/2017 00:28:31
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 1 found to restore 没有找到备份文件

不是全库备份：----------

SQL> @/home/oracle/huanyuan.sql      ---由于没有RMAN备份，只能使用之前的冷备脚本，但是这段时间内，新建的表空间没了，新的数据也没了，彻底的不完全恢复；

 
SQL> startp mount;

select file#,name,checkpoint_change#,last_change#,status from v$datafile

SQL> select file#,name,checkpoint_change# from v$datafile_header;           

     FILE# ---------- NAME -----CHECKPOINT_CHANGE# ------------------    

   1 /picclife/app/hukou/data/system01.dbf             377583

   2 /picclife/app/hukou/data/sysaux01.dbf             377583              ---控制文件记录的SCN，数据文件头部的SCN全都是统一的；

SQL> alter database open;  --为啥能打开？

Database altered.

SQL> archive log list Database log mode              No Archive Mode      --归档转为非归档了？

[oracle@yang app]$ cat /home/oracle/huanyuan.sql
host cp /u01/app/lengbei/system01.dbf /picclife/app/hukou/data/system01.dbf;
host cp /u01/app/lengbei/sysaux01.dbf /picclife/app/hukou/data/sysaux01.dbf;
host cp /u01/app/lengbei/undotbs01.dbf /picclife/app/hukou/data/undotbs01.dbf;
host cp /u01/app/lengbei/users01.dbf /picclife/app/hukou/data/users01.dbf;
host cp /u01/app/lengbei/redo03a.log /picclife/app/hukou/log/redo03a.log;
host cp /u01/app/lengbei/redo03b.log /picclife/app/hukou/log/redo03b.log;
host cp /u01/app/lengbei/redo02a.log /picclife/app/hukou/log/redo02a.log;
host cp /u01/app/lengbei/redo02b.log /picclife/app/hukou/log/redo02b.log;
host cp /u01/app/lengbei/redo01a.log /picclife/app/hukou/log/redo01a.log;
host cp /u01/app/lengbei/redo01b.log /picclife/app/hukou/log/redo01b.log;
host cp /u01/app/lengbei/ora_control1 /picclife/app/oracle/oradata/hukou/ora_control1;
host cp /u01/app/lengbei/control5 /picclife/control5;

*************总结，备份备份最重要，测试环境没有备份，无法完全恢复，直接不完全的恢复可以用，但是实际中万万要保存数据重要性不容忽视，丢失归档，如果没有全备，和不开归档时一样的

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/lvcha001/p/7805941.html