基于恢复点的恢复

郑全

基于恢复点的恢复
SQL> conn / as sysdba
Connected.
SQL>  create restore point before_update;

Restore point created.

SQL> conn hr/hr
Connected.
SQL> select employee_id,last_name from emp;

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        198 OConnell
        199 Grant
        200 Whalen
        201 Hartstein
        202 Fay
        203 Mavris
        204 Baer
        205 Higgins
        206 Gietz
        100 King
        101 Kochhar

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        102 De Haan
        103 Hunold
        104 Ernst
        105 Austin
        106 Pataballa
        107 Lorentz
        108 Greenberg
        109 Faviet
        110 Chen
        111 Sciarra
        112 Urman

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        113 Popp
        114 Raphaely
        115 Khoo
        116 Baida
        117 Tobias
        118 Himuro
        119 Colmenares
        120 Weiss
        121 Fripp
        122 Kaufling
        123 Vollman

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        124 Mourgos
        125 Nayer
        126 Mikkilineni
        127 Landry
        128 Markle
        129 Bissot
        130 Atkinson
        131 Marlow
        132 Olson
        133 Mallin
        134 Rogers

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        135 Gee
        136 Philtanker
        137 Ladwig
        138 Stiles
        139 Seo
        140 Patel
        141 Rajs
        142 Davies
        143 Matos
        144 Vargas
        145 Russell

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        146 Partners
        147 Errazuriz
        148 Cambrault
        149 Zlotkey
        150 Tucker
        151 Bernstein
        152 Hall
        153 Olsen
        154 Cambrault
        155 Tuvault
        156 King

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        157 Sully
        158 McEwen
        159 Smith
        160 Doran
        161 Sewall
        162 Vishney
        163 Greene
        164 Marvins
        165 Lee
        166 Ande
        167 Banda

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        168 Ozer
        169 Bloom
        170 Fox
        171 Smith
        172 Bates
        173 Kumar
        174 Abel
        175 Hutton
        176 Taylor
        177 Livingston
        178 Grant

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        179 Johnson
        180 Taylor
        181 Fleaur
        182 Sullivan
        183 Geoni
        184 Sarchand
        185 Bull
        186 Dellinger
        187 Cabrio
        188 Chung
        189 Dilly

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        190 Gates
        191 Perkins
        192 Bell
        193 Everett
        194 McCain
        195 Jones
        196 Walsh
        197 Feeney

107 rows selected.

SQL> update emp set last_name ='ABC';

107 rows updated.

SQL> commit;

Commit complete.

SQL> select employee_id,last_name from emp;

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        198 ABC
        199 ABC
        200 ABC
        201 ABC
        202 ABC
        203 ABC
        204 ABC
        205 ABC
        206 ABC
        100 ABC
        101 ABC

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        102 ABC
        103 ABC
        104 ABC
        105 ABC
        106 ABC
        107 ABC
        108 ABC
        109 ABC
        110 ABC
        111 ABC
        112 ABC

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        113 ABC
        114 ABC
        115 ABC
        116 ABC
        117 ABC
        118 ABC
        119 ABC
        120 ABC
        121 ABC
        122 ABC
        123 ABC

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        124 ABC
        125 ABC
        126 ABC
        127 ABC
        128 ABC
        129 ABC
        130 ABC
        131 ABC
        132 ABC
        133 ABC
        134 ABC

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        135 ABC
        136 ABC
        137 ABC
        138 ABC
        139 ABC
        140 ABC
        141 ABC
        142 ABC
        143 ABC
        144 ABC
        145 ABC

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        146 ABC
        147 ABC
        148 ABC
        149 ABC
        150 ABC
        151 ABC
        152 ABC
        153 ABC
        154 ABC
        155 ABC
        156 ABC

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        157 ABC
        158 ABC
        159 ABC
        160 ABC
        161 ABC
        162 ABC
        163 ABC
        164 ABC
        165 ABC
        166 ABC
        167 ABC

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        168 ABC
        169 ABC
        170 ABC
        171 ABC
        172 ABC
        173 ABC
        174 ABC
        175 ABC
        176 ABC
        177 ABC
        178 ABC

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        179 ABC
        180 ABC
        181 ABC
        182 ABC
        183 ABC
        184 ABC
        185 ABC
        186 ABC
        187 ABC
        188 ABC
        189 ABC

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        190 ABC
        191 ABC
        192 ABC
        193 ABC
        194 ABC
        195 ABC
        196 ABC
        197 ABC

107 rows selected.

 

[oracle@dbserver sztech2]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 21 10:55:35 2012

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

SQL> connect / as sysdba
Connected.
SQL> shutdown immedaite
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
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> exit

 

RMAN> run   {
2>  set until restore point before_update;
3> restore database;
4> recover database;
5> }

executing command: SET until clause

Starting restore at 21-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: restoring datafile 00001
input datafile copy RECID=31 STAMP=791894436 file name=/oracle/app/oracle/oradata/sztech1/system01.dbf
destination for restore of datafile 00001: /oracle/app/oracle/oradata/sztech2/system01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00001
output file name=/oracle/app/oracle/oradata/sztech2/system01.dbf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00002
input datafile copy RECID=32 STAMP=791894436 file name=/oracle/app/oracle/oradata/sztech1/sysaux01.dbf
destination for restore of datafile 00002: /oracle/app/oracle/oradata/sztech2/sysaux01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00002
output file name=/oracle/app/oracle/oradata/sztech2/sysaux01.dbf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00003
input datafile copy RECID=33 STAMP=791894436 file name=/oracle/app/oracle/oradata/sztech1/undotbs01.dbf
destination for restore of datafile 00003: /oracle/app/oracle/oradata/sztech2/undotbs01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00003
output file name=/oracle/app/oracle/oradata/sztech2/undotbs01.dbf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=34 STAMP=791894436 file name=/oracle/app/oracle/oradata/sztech1/users01.dbf
destination for restore of datafile 00004: /oracle/app/oracle/oradata/sztech2/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/oracle/app/oracle/oradata/sztech2/users01.dbf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00005
input datafile copy RECID=35 STAMP=791894436 file name=/oracle/app/oracle/oradata/sztech1/example01.dbf
destination for restore of datafile 00005: /oracle/app/oracle/oradata/sztech2/example01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00005
output file name=/oracle/app/oracle/oradata/sztech2/example01.dbf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00006
input datafile copy RECID=36 STAMP=791894436 file name=/oracle/app/oracle/oradata/sztech1/idx01.dbf
destination for restore of datafile 00006: /oracle/app/oracle/oradata/sztech2/idx01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00006
output file name=/oracle/app/oracle/oradata/sztech2/idx01.dbf RECID=0 STAMP=0
Finished restore at 21-AUG-12

Starting recover at 21-AUG-12
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:05

Finished recover at 21-AUG-12

 

RMAN> alter database open resetlogs;

database opened

[oracle@dbserver sztech2]$ sqlplus /nolog
conne
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 21 11:07:34 2012

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

SQL> ct / as sysdba

SQL> select GROUP#,SEQUENCE#,status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          0 UNUSED
         3          0 UNUSED

SQL> conn hr/hr;
Connected.

SQL> select employee_id,last_name from emp;

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        198 OConnell
        199 Grant
        200 Whalen
        201 Hartstein
        202 Fay
        203 Mavris
        204 Baer
        205 Higgins
        206 Gietz
        100 King
        101 Kochhar

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        102 De Haan
        103 Hunold
        104 Ernst
        105 Austin
        106 Pataballa
        107 Lorentz
        108 Greenberg
        109 Faviet
        110 Chen
        111 Sciarra
        112 Urman

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        113 Popp
        114 Raphaely
        115 Khoo
        116 Baida
        117 Tobias
        118 Himuro
        119 Colmenares
        120 Weiss
        121 Fripp
        122 Kaufling
        123 Vollman

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        124 Mourgos
        125 Nayer
        126 Mikkilineni
        127 Landry
        128 Markle
        129 Bissot
        130 Atkinson
        131 Marlow
        132 Olson
        133 Mallin
        134 Rogers

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        135 Gee
        136 Philtanker
        137 Ladwig
        138 Stiles
        139 Seo
        140 Patel
        141 Rajs
        142 Davies
        143 Matos
        144 Vargas
        145 Russell

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        146 Partners
        147 Errazuriz
        148 Cambrault
        149 Zlotkey
        150 Tucker
        151 Bernstein
        152 Hall
        153 Olsen
        154 Cambrault
        155 Tuvault
        156 King

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        157 Sully
        158 McEwen
        159 Smith
        160 Doran
        161 Sewall
        162 Vishney
        163 Greene
        164 Marvins
        165 Lee
        166 Ande
        167 Banda

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        168 Ozer
        169 Bloom
        170 Fox
        171 Smith
        172 Bates
        173 Kumar
        174 Abel
        175 Hutton
        176 Taylor
        177 Livingston
        178 Grant

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        179 Johnson
        180 Taylor
        181 Fleaur
        182 Sullivan
        183 Geoni
        184 Sarchand
        185 Bull
        186 Dellinger
        187 Cabrio
        188 Chung
        189 Dilly

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        190 Gates
        191 Perkins
        192 Bell
        193 Everett
        194 McCain
        195 Jones
        196 Walsh
        197 Feeney

107 rows selected.