非归档模式下全备份后恢复

郑全

非归档模式下全备份后恢复
1.全备份
  1.1 关闭数据库
RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

  1.2 启动到 mount状态

[oracle@dbserver sztech1]$ sqlplus /nolog
con
SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 20 19:19:13 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> nect / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  619360256 bytes
Fixed Size                  1346700 bytes
Variable Size             411042676 bytes
Database Buffers          201326592 bytes
Redo Buffers                5644288 bytes
Database mounted.
SQL>

  1.3 全备份

RMAN> backup database;

Starting backup at 20-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 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=/oracle/app/oracle/oradata/sztech1/system01.dbf
input datafile file number=00002 name=/oracle/app/oracle/oradata/sztech1/sysaux01.dbf
input datafile file number=00005 name=/oracle/app/oracle/oradata/sztech1/example01.dbf
input datafile file number=00003 name=/oracle/app/oracle/oradata/sztech1/undotbs01.dbf
input datafile file number=00006 name=/oracle/app/oracle/oradata/sztech1/idx01.dbf
input datafile file number=00004 name=/oracle/app/oracle/oradata/sztech1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 20-AUG-12
channel ORA_DISK_1: finished piece 1 at 20-AUG-12
piece handle=/oracle/app/oracle/fast_recovery_area/SZTECH1/backupset/2012_08_20/o1_mf_nnndf_TAG20120820T191957_8347bylm_.bkp tag=TAG20120820T191957 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
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 20-AUG-12
channel ORA_DISK_1: finished piece 1 at 20-AUG-12
piece handle=/oracle/app/oracle/fast_recovery_area/SZTECH1/backupset/2012_08_20/o1_mf_ncsnf_TAG20120820T191957_8347fc7g_.bkp tag=TAG20120820T191957 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-AUG-12

RMAN>

 

2.做一些业务变化
  2.1 打开数据库
SQL> alter database open;

Database altered.

  2.2 进行日志切换
SQL> alter system switch logfile;

System altered.

  2.3 增加测试表，下次恢复后，这个表应该不存在
SQL> conn hr/hr      
Connected.
SQL> create table emp2 as select * from employees;

Table created.

SQL> select count(*) from emp2;

  COUNT(*)
----------
       107

SQL>

  2.4 日志切换
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;

3.进行全备份后的恢复
  3.1 关闭数据库
  SQL> shutdown immediate
  Database closed.
  Database dismounted.
  ORACLE instance shut down.

  3.2 启动数据库到mount
SQL> startup mount;
ORACLE instance started.

Total System Global Area  619360256 bytes
Fixed Size                  1346700 bytes
Variable Size             411042676 bytes
Database Buffers          201326592 bytes
Redo Buffers                5644288 bytes
Database mounted.
SQL>

  3.3 删除数据文件
[oracle@dbserver ~]$ cd /oracle/app/oracle/oradata/sztech1
[oracle@dbserver sztech1]$ ls -ltr
total 2345184
-rw-r----- 1 oracle dba  52436992 Aug 20 18:34 temp02.dbf
-rw-r----- 1 oracle dba  52429312 Aug 20 19:23 redo02.log
-rw-r----- 1 oracle dba  52429312 Aug 20 19:23 redo03.log
-rw-r----- 1 oracle dba  13115392 Aug 20 19:24 users01.dbf
-rw-r----- 1 oracle dba 110108672 Aug 20 19:24 undotbs01.dbf
-rw-r----- 1 oracle dba 922755072 Aug 20 19:24 system01.dbf
-rw-r----- 1 oracle dba 692068352 Aug 20 19:24 sysaux01.dbf
-rw-r----- 1 oracle dba  52429312 Aug 20 19:24 redo01.log
-rw-r----- 1 oracle dba  20979712 Aug 20 19:24 idx01.dbf
-rw-r----- 1 oracle dba 471867392 Aug 20 19:24 example01.dbf
-rw-r----- 1 oracle dba   9748480 Aug 20 19:26 control01.ctl
[oracle@dbserver sztech1]$ rm -fr system01.dbf
[oracle@dbserver sztech1]$

  3.4 删除日志文件
[oracle@dbserver sztech1]$ mv  redo01.log redo01bak.log

  3.5 数据还原

[oracle@dbserver sztech1]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Aug 20 19:26:16 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SZTECH1 (DBID=3206860853, not open)

RMAN> restore database;

Starting restore at 20-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK

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 /oracle/app/oracle/oradata/sztech1/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oracle/app/oracle/oradata/sztech1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oracle/app/oracle/oradata/sztech1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oracle/app/oracle/oradata/sztech1/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /oracle/app/oracle/oradata/sztech1/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /oracle/app/oracle/oradata/sztech1/idx01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/app/oracle/fast_recovery_area/SZTECH1/backupset/2012_08_20/o1_mf_nnndf_TAG20120820T191957_8347bylm_.bkp
channel ORA_DISK_1: piece handle=/oracle/app/oracle/fast_recovery_area/SZTECH1/backupset/2012_08_20/o1_mf_nnndf_TAG20120820T191957_8347bylm_.bkp tag=TAG20120820T191957
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 20-AUG-12

   3.6 介质恢复
  
RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 08/20/2012 19:29:39
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/app/oracle/oradata/sztech1/system01.dbf'

RMAN> recover database;

Starting recover at 20-AUG-12
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file /oracle/app/oracle/oradata/sztech1/redo02.log
archived log for thread 1 with sequence 3 is already on disk as file /oracle/app/oracle/oradata/sztech1/redo03.log
RMAN-08187: WARNING: media recovery until SCN 960247 complete
Finished recover at 20-AUG-12

  3.7 使用resetlogs打开数据库

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 08/20/2012 19:34:01
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

database opened

4.验证数据
  验证备份后产生的数据，是否还在？

[oracle@dbserver sztech1]$ sqlplus /nolog
conne
SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 20 19:35:39 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> ct hr/hr
Connected.
SQL> select count(*) from emp2;
select count(*) from emp2
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select table_name from tabs;

TABLE_NAME
------------------------------
COUNTRIES
EMP
BIG
JOB_HISTORY
EMPLOYEES
JOBS
DEPARTMENTS
LOCATIONS
REGIONS

9 rows selected.

  说明备份后的产生的数据已经丢失