使用恢复点，闪回数据:
恢复点或者叫做还原点，实际上就是SCN号的别名.
V$FLASHBACK_DATABASE_STAT;
config restore point
1、create restore point
2、flashback database
/***************************************************************************************************************************************/
创建restore point，恢复数据库；
/***************************************************************************************************************************************/
SQL> show user USER is "SYS" SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE; Restore point created.
2、以U2用户登陆数据库，将表t中的salary列数据全部清零.
SQL> update t set salary = 0; 107 rows updated. SQL> commit; Commit complete. SQL> select employee_id,last_name,salary from t; EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 198 OConnell 0 199 Grant 0 200 Whalen 0 201 Hartstein 0 202 Fay 0 203 Mavris 0 204 Baer 0 205 Higgins 0 206 Gietz 0 100 King 0 101 Kochhar 0 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 102 De Haan 0 103 Hunold 0 104 Ernst 0 105 Austin 0 106 Pataballa 0 107 Lorentz 0 108 Greenberg 0 109 Faviet 0 110 Chen 0 111 Sciarra 0 112 Urman 0 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 113 Popp 0 114 Raphaely 0 115 Khoo 0 116 Baida 0 117 Tobias 0 118 Himuro 0 119 Colmenares 0 120 Weiss 0 121 Fripp 0 122 Kaufling 0 123 Vollman 0 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 124 Mourgos 0 125 Nayer 0 126 Mikkilineni 0 127 Landry 0 128 Markle 0 129 Bissot 0 130 Atkinson 0 131 Marlow 0 132 Olson 0 133 Mallin 0 134 Rogers 0 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 135 Gee 0 136 Philtanker 0 137 Ladwig 0 138 Stiles 0 139 Seo 0 140 Patel 0 141 Rajs 0 142 Davies 0 143 Matos 0 144 Vargas 0 145 Russell 0 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 146 Partners 0 147 Errazuriz 0 148 Cambrault 0 149 Zlotkey 0 150 Tucker 0 151 Bernstein 0 152 Hall 0 153 Olsen 0 154 Cambrault 0 155 Tuvault 0 156 King 0 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 157 Sully 0 158 McEwen 0 159 Smith 0 160 Doran 0 161 Sewall 0 162 Vishney 0 163 Greene 0 164 Marvins 0 165 Lee 0 166 Ande 0 167 Banda 0 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 168 Ozer 0 169 Bloom 0 170 Fox 0 171 Smith 0 172 Bates 0 173 Kumar 0 174 Abel 0 175 Hutton 0 176 Taylor 0 177 Livingston 0 178 Grant 0 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 179 Johnson 0 180 Taylor 0 181 Fleaur 0 182 Sullivan 0 183 Geoni 0 184 Sarchand 0 185 Bull 0 186 Dellinger 0 187 Cabrio 0 188 Chung 0 189 Dilly 0 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 190 Gates 0 191 Perkins 0 192 Bell 0 193 Everett 0 194 McCain 0 195 Jones 0 196 Walsh 0 197 Feeney 0 107 rows selected. SQL> commit; Commit complete.
3、以SYSDBA身份登陆，首先shutdown immediate,然后startup mount;
SQL> conn /as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 3256942592 bytes Fixed Size 2217752 bytes Variable Size 1845496040 bytes Database Buffers 1392508928 bytes Redo Buffers 16719872 bytes Database mounted.
4、执行闪回操作，按照还原点进行闪回；操作完成后，以read only模式打开数据库，验证数据库数据;
SQL> FLASHBACK DATABASE TO RESTORE POINT before_upgrade; Flashback complete. SQL> ALTER DATABASE OPEN READ ONLY; Database altered. SQL> SELECT employee_id,last_name,salary FROM U2.t; EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 198 OConnell 2600 199 Grant 2600 200 Whalen 4400 201 Hartstein 13000 202 Fay 6000 203 Mavris 6500 204 Baer 10000 205 Higgins 12008 206 Gietz 8300 100 King 24000 101 Kochhar 17000 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 102 De Haan 17000 103 Hunold 9000 104 Ernst 6000 105 Austin 4800 106 Pataballa 4800 107 Lorentz 4200 108 Greenberg 12008 109 Faviet 9000 110 Chen 8200 111 Sciarra 7700 112 Urman 7800 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 113 Popp 6900 114 Raphaely 11000 115 Khoo 3100 116 Baida 2900 117 Tobias 2800 118 Himuro 2600 119 Colmenares 2500 120 Weiss 8000 121 Fripp 8200 122 Kaufling 7900 123 Vollman 6500 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 124 Mourgos 5800 125 Nayer 3200 126 Mikkilineni 2700 127 Landry 2400 128 Markle 2200 129 Bissot 3300 130 Atkinson 2800 131 Marlow 2500 132 Olson 2100 133 Mallin 3300 134 Rogers 2900 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 135 Gee 2400 136 Philtanker 2200 137 Ladwig 3600 138 Stiles 3200 139 Seo 2700 140 Patel 2500 141 Rajs 3500 142 Davies 3100 143 Matos 2600 144 Vargas 2500 145 Russell 14000 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 146 Partners 13500 147 Errazuriz 12000 148 Cambrault 11000 149 Zlotkey 10500 150 Tucker 10000 151 Bernstein 9500 152 Hall 9000 153 Olsen 8000 154 Cambrault 7500 155 Tuvault 7000 156 King 10000 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 157 Sully 9500 158 McEwen 9000 159 Smith 8000 160 Doran 7500 161 Sewall 7000 162 Vishney 10500 163 Greene 9500 164 Marvins 7200 165 Lee 6800 166 Ande 6400 167 Banda 6200 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 168 Ozer 11500 169 Bloom 10000 170 Fox 9600 171 Smith 7400 172 Bates 7300 173 Kumar 6100 174 Abel 11000 175 Hutton 8800 176 Taylor 8600 177 Livingston 8400 178 Grant 7000 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 179 Johnson 6200 180 Taylor 3200 181 Fleaur 3100 182 Sullivan 2500 183 Geoni 2800 184 Sarchand 4200 185 Bull 4100 186 Dellinger 3400 187 Cabrio 3000 188 Chung 3800 189 Dilly 3600 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 190 Gates 2900 191 Perkins 2500 192 Bell 4000 193 Everett 3900 194 McCain 3200 195 Jones 2800 196 Walsh 3100 197 Feeney 3000 107 rows selected.
5、正式将数据库以resetlogs模式打开
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 3256942592 bytes Fixed Size 2217752 bytes Variable Size 1845496040 bytes Database Buffers 1392508928 bytes Redo Buffers 16719872 bytes Database mounted. SQL> alter database open resetlogs; Database altered. SQL> SELECT employee_id,last_name,salary FROM U2.t; EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 198 OConnell 2600 199 Grant 2600 200 Whalen 4400 201 Hartstein 13000 202 Fay 6000 203 Mavris 6500 204 Baer 10000 205 Higgins 12008 206 Gietz 8300 100 King 24000 101 Kochhar 17000 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 102 De Haan 17000 103 Hunold 9000 104 Ernst 6000 105 Austin 4800 106 Pataballa 4800 107 Lorentz 4200 108 Greenberg 12008 109 Faviet 9000 110 Chen 8200 111 Sciarra 7700 112 Urman 7800 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 113 Popp 6900 114 Raphaely 11000 115 Khoo 3100 116 Baida 2900 117 Tobias 2800 118 Himuro 2600 119 Colmenares 2500 120 Weiss 8000 121 Fripp 8200 122 Kaufling 7900 123 Vollman 6500 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 124 Mourgos 5800 125 Nayer 3200 126 Mikkilineni 2700 127 Landry 2400 128 Markle 2200 129 Bissot 3300 130 Atkinson 2800 131 Marlow 2500 132 Olson 2100 133 Mallin 3300 134 Rogers 2900 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 135 Gee 2400 136 Philtanker 2200 137 Ladwig 3600 138 Stiles 3200 139 Seo 2700 140 Patel 2500 141 Rajs 3500 142 Davies 3100 143 Matos 2600 144 Vargas 2500 145 Russell 14000 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 146 Partners 13500 147 Errazuriz 12000 148 Cambrault 11000 149 Zlotkey 10500 150 Tucker 10000 151 Bernstein 9500 152 Hall 9000 153 Olsen 8000 154 Cambrault 7500 155 Tuvault 7000 156 King 10000 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 157 Sully 9500 158 McEwen 9000 159 Smith 8000 160 Doran 7500 161 Sewall 7000 162 Vishney 10500 163 Greene 9500 164 Marvins 7200 165 Lee 6800 166 Ande 6400 167 Banda 6200 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 168 Ozer 11500 169 Bloom 10000 170 Fox 9600 171 Smith 7400 172 Bates 7300 173 Kumar 6100 174 Abel 11000 175 Hutton 8800 176 Taylor 8600 177 Livingston 8400 178 Grant 7000 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 179 Johnson 6200 180 Taylor 3200 181 Fleaur 3100 182 Sullivan 2500 183 Geoni 2800 184 Sarchand 4200 185 Bull 4100 186 Dellinger 3400 187 Cabrio 3000 188 Chung 3800 189 Dilly 3600 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 190 Gates 2900 191 Perkins 2500 192 Bell 4000 193 Everett 3900 194 McCain 3200 195 Jones 2800 196 Walsh 3100 197 Feeney 3000 107 rows selected.
Quiz
1、You can use Flashback Database,when you want to:
Repaire logical data corruptions
Recover a tablespace that has been dropped
Recover to a point prior to when a data file has been reduced in size
Recover to a point prior to when you re-created the control file
2、Flashback logs are archived to allow you to rewind to a point in time that your FRA cannot accommodate.
True
False
Summary
In this lesson,you should have learned how to:
- Configure Flashback Database
- Perform Flashback Database operations
- Monitor Flashback Database