Oracle 培训笔记 9.10 rman(二)
来源:互联网 发布:微软linux子系统能干啥 编辑:程序博客网 时间:2024/06/05 01:17
1、环境准备
恢复一个干净数据库
设成归档模式
备份数据库
C:\>rman target /
RMAN> backup database;
2、数据库恢复
命令:还原 restore, 恢复 recover
练习1:单个表空间还原
模拟变化
create table t01(id number) tablespace users;
insert into t01 values(10);
commit;
模拟数据文件损坏 user01.dbf
RMAN> sql 'alter tablespace users offline';
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> sql 'alter tablespace users online';
练习2:所有文件都损坏(数据文件、控制文件、参数文件)
1)关闭数据库,删除文件
shutdown immediate
删除 数据文件、控制文件、参数文件
2)恢复参数文件
手动编写一个参数文件E:\oracle\product\10.2.0\db_1\database\initmysid.ora
db_name=mydb
sga_target=209715200
然后启动实例,恢复参数文件
C:\>rman target /
RMAN> startup nomount
RMAN> restore spfile from 'E:\oracle\product\10.2.0\flash_recovery_area\MYDB\BAC
KUPSET\2011_09_10\O1_MF_NCSNF_TAG20110910T094752_76OJH34D_.BKP';
3)恢复控制文件
关闭数据库,利用恢复的参数文件启动实例
RMAN> shutdown immediate
RMAN> startup nomount
RMAN> restore controlfile from 'E:\oracle\product\10.2.0\flash_recovery_area\MYD
B\BACKUPSET\2011_09_10\O1_MF_NCSNF_TAG20110910T094752_76OJH34D_.BKP';
4)恢复数据文件
RMAN> shutdown immediate
RMAN> startup mount
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
3、备份时是read only
restore database check readonly;
run{
set newname for datafile 'E:\oracle\product\10.2.0\oradata\mydb\users01.dbf' to 'E:\oracle\product\10.2.0\oradata\mydb\users01.dbf';
restore tablespace users;
}
4、镜像备份
RMAN> backup as copy database;
--模拟快速临时恢复使用
SQL> select * from t01;
ID
----------
10
20
SQL> insert into t01 values(30);
SQL> commit;
SQL> shutdown immediate
删除数据文件users01.dbf
模拟快速恢复,将数据文件直接指定到备份位置,恢复并使用
SQL> startup
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\USERS01.DBF'
SQL> alter database rename file 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\USERS01.D
BF' to 'd:\users01.dbf';
SQL> recover datafile 4;
Media recovery complete.
SQL> alter database open;
5、压缩备份
RMAN> backup as compressed backupset database;
6、增量备份
6.1 备份集
level 0: 全备
level 1: 在上一个备份基础上,备份变化过的内容。如果没有0级备份,则做全备
level 2: 累积备份
RMAN> backup incremental level 0 database;
RMAN> backup incremental level 1 database;
增加变化跟踪
SQL> alter database enable block change tracking using file 'd:\dat.trc';
Database altered.
RMAN> backup incremental level 2 database;
场景:level 0备份,增加数据文件,level 1,模拟数据文件全部损坏,查看还原
的数据文件是否包含新增加的文件
6.2 镜像前滚
C:\>rman target /
RMAN> backup as copy incremental level 0 database tag 'fullcopy';
RMAN> backup incremental level 1 for recover of copy tag 'fullcopy' database;
RMAN> recover copy of database with tag 'fullcopy';
练习:备份后创建的表空间没有备份,但损坏,如何恢复
SQL> create tablespace tbs2 datafile 'd:\tbs2.dbf' size 5M;
Tablespace created.
SQL> create table t03 (id number)tablespace tbs2;
Table created.
SQL> insert into t03 values(10);
1 row created.
SQL> commit;
Commit complete.
模拟数据文件损坏,且没有备份
1)处理办法1,创建文件
SQL> shutdown immediate
删除d:\tbs2.dbf
SQL> startup
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'D:\TBS2.DBF'
SQL> alter database create datafile 'D:\TBS2.DBF' as 'E:\TBS2.DBF';
SQL> recover tablespace tbs2;
SQL> alter database open;
SQL> select * from t03;
ID
----------
10
2)处理办法2,删除文件
SQL> shutdown immediate
删除e:\tbs2.dbf
SQL> startup
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'E:\TBS2.DBF'
SQL> alter database datafile 6 offline drop;
SQL> alter database open;
7、不完全恢复
1)备份数据库
2)模拟变化,记录scn号
SQL> create table t03 (id number) tablespace users;
Table created.
SQL> insert into t03 values(100);
1 row created.
SQL> commit;
Commit complete.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
222368
SQL> insert into t03 values(200);
1 row created.
SQL> commit;
Commit complete.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
222376
SQL> insert into t03 values(300);
1 row created.
SQL> commit;
Commit complete.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
222381
3)执行不完全恢复
RMAN> shutdown immediate
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 251658240 bytes
Fixed Size 1248356 bytes
Variable Size 83887004 bytes
Database Buffers 159383552 bytes
Redo Buffers 7139328 bytes
RMAN> restore database until scn 222376;
RMAN> recover database until scn 222376;
RMAN> alter database open resetlogs;
验证数据
C:\>sqlplus / as sysdba
SQL> select * from t03;
ID
----------
100
200
RMAN> restore database until {scn 222376|time 'xxxx'|sequence xx};
RMAN> recover database until {scn 222376|time 'xxxx'|sequence xx};
8、表空间不完全恢复(表空间基于时间点恢复TSPITR)
sqlplus / as sysdba
SQL> select * from t03;
ID
----------
100
200
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> insert into t03 values(300);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;
110000
SQL> insert into t03 values(400);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;
110010
SQL>exit;
rman target /
RMAN> recover tablespace users until scn 110000 auxiliary destination 'd:\incomp
lete';
sqlplus / as sysdba
SQL> alter tablespace user online;
SQL> select * from t03;
ID
----------
100
200
300
9、catalog
应该建一个独立的数据库:
C:\>sqlplus sys/111111@remote as sysdba
SQL> create tablespace tbs1 datafile 'd:\tbs1.dbf' size 50M autoextend on next 5M;
SQL> create user rman identified by rman default tablespace tbs1 quota unlimited on tbs1;
SQL> grant RECOVERY_CATALOG_OWNER to rman;
连接
C:\>rman target / catalog rman/rman@remote
创建恢复目录(表和视图)
RMAN> create catalog;
注册数据库
RMAN> register database;
取消注册 unregister database;
查incarnation信息
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 9 MYDB 2675868718 PARENT 1 03-SEP-11
1 10 MYDB 2675868718 PARENT 203470 10-SEP-11
1 2 MYDB 2675868718 CURRENT 222381 10-SEP-11
备份数据库
RMAN> backup database;
模拟数据变化
create table test(id number);
insert into test values(100);
commit;
select dbms_flashback.get_system_change_number from dual;
231157
insert into test values(200);
commit;
select dbms_flashback.get_system_change_number from dual;
231172
insert into test values(300);
commit;
select dbms_flashback.get_system_change_number from dual;
231178
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
1 1 2 52428800 1 NO CURRENT 226678 10-SEP-11
2 1 1 52428800 1 YES INACTIVE 222381 10-SEP-11
3 1 0 52428800 1 YES UNUSED 0
第一次不完全恢复
C:\>rman target / catalog rman/rman@remote
rman> shutdown immediate;
rman>startup mount;
rman>restore database until scn 231172;
rman>recover database until scn 231172;
rman>alter database open resetlogs;
C:\>sqlplus / as sysdba
SQL> select * from test;
ID
----------
100
200
第二次不完全恢复
C:\>rman target / catalog rman/rman@remote
rman> shutdown immediate;
rman>startup mount;
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 9 MYDB 2675868718 PARENT 1 03-SEP-11
1 10 MYDB 2675868718 PARENT 203470 10-SEP-11
1 2 MYDB 2675868718 PARENT 222381 10-SEP-11
1 264 MYDB 2675868718 CURRENT 231175 10-SEP-11
RMAN> reset database to incarnation 2;
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 9 MYDB 2675868718 PARENT 1 03-SEP-11
1 10 MYDB 2675868718 PARENT 203470 10-SEP-11
1 2 MYDB 2675868718 CURRENT 222381 10-SEP-11
1 264 MYDB 2675868718 ORPHAN 231175 10-SEP-11
rman>restore database until scn 231172;
rman>recover database until scn 231172;
rman>alter database open resetlogs;
C:\>sqlplus / as sysdba
SQL> select * from test;
ID
----------
100
第三次不完全恢复
C:\>rman target / catalog rman/rman@remote
rman> shutdown immediate;
rman>startup mount;
RMAN> reset database to incarnation 2;
rman>restore database until scn 231178;
rman>recover database until scn 231178;
rman>alter database open resetlogs;
C:\>sqlplus / as sysdba
SQL> select * from test;
ID
----------
100
200
300
10、备份脚本
创建脚本
RMAN> create script f1{
allocate channel c1 device type disk;
backup database;
delete noprompt obsolete;
}
查询视图
select * from RC_STORED_SCRIPT_LINE;
执行脚本
RMAN> run{
execute script f1;
}
创建备份执行文件
full.sql
run{
execute script f1;
}
backup.cmd
rman target / catalog rman/rman@mydb cmdfile=d:\full.sql trace=d:\backup.trc append
select * from rc_database;
select * from rc_stored_script;
11、块恢复
create tablespace tbs datafile 'd:\tbs.dbf' size 5M;
create table t01 tablespace tbs as select * from dba_objects;
执行rman备份:
rman target /
rman> backup database;
select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid),
dbms_rowid.rowid_row_number(rowid) from t01 where rownum<2;
1~8块 文件头
9~11块 段头
破坏文件
验证方法
1)全表扫描
SQL> select count(*) from t01;
select count(*) from t01
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 11)
ORA-01110: data file 7: 'D:\TBS.DBF'
2)分析表
SQL> analyze table t01 compute statistics;
analyze table t01 compute statistics
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 11)
ORA-01110: data file 7: 'D:\TBS.DBF'
3)借助导出工具empdp
D:\>exp userid='sys/1 as sysdba' file=dump.dat tables=(t01)
Export: Release 10.2.0.1.0 - Production on Sun Sep 11 09:53:58 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T01
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 7, block # 11)
ORA-01110: data file 7: 'D:\TBS.DBF'
Export terminated successfully with warnings.
4)使用rman
D:\>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Sun Sep 11 09:54:20 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: MYDB (DBID=2675868718)
RMAN> backup validate tablespace tbs;
Starting backup at 11-SEP-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=133 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00007 name=D:\TBS.DBF
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-SEP-11
查询视图
select * from v$database_block_corruption;
5)使用dbverify
D:\>dbv file=d:\tbs.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Sun Sep 11 09:49:23 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = d:\tbs.dbf
Page 11 is marked corrupt
Corrupt block relative dba: 0x01c0000b (file 7, block 11)
Bad header found during dbv:
Data in bad block:
type: 153 format: 1 rdba: 0x99999999
last change scn: 0x9999.99999999 seq: 0x99 flg: 0x99
spare1: 0x99 spare2: 0x99 spare3: 0x9999
consistency value in tail: 0x29f42301
check value in block header: 0x9999
block checksum disabled
DBVERIFY - Verification complete
Total Pages Examined : 640
Total Pages Processed (Data) : 146
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 19
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 474
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 272882 (0.272882)
块恢复
RMAN> blockrecover datafile 7 block 11;
相关参数
SQL> show parameter db_block
NAME TYPE VALUE
------------------------------------ ----------- ---------------
db_block_checking string FALSE
db_block_checksum string true
db_block_checksum:dbwN写数据文件时根据块内容计算一个校验码存入块,
在读数据时,根据块内容计算校验码与块写入时的校验码比对,判断块是否
出现错误。
db_block_checking:内存中的块校验
SQL> set timing on
SQL> show parameter db_block
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_checking string TRUE
SQL> create table t01 tablespace users as select * from dba_objects;
SQL> update t01 set object_name='aa1';
Elapsed: 00:00:01.60
SQL> alter system flush buffer_cache;
SQL> alter system set db_block_checking=false;
SQL> update t01 set object_name='aa1';
恢复一个干净数据库
设成归档模式
备份数据库
C:\>rman target /
RMAN> backup database;
2、数据库恢复
命令:还原 restore, 恢复 recover
练习1:单个表空间还原
模拟变化
create table t01(id number) tablespace users;
insert into t01 values(10);
commit;
模拟数据文件损坏 user01.dbf
RMAN> sql 'alter tablespace users offline';
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> sql 'alter tablespace users online';
练习2:所有文件都损坏(数据文件、控制文件、参数文件)
1)关闭数据库,删除文件
shutdown immediate
删除 数据文件、控制文件、参数文件
2)恢复参数文件
手动编写一个参数文件E:\oracle\product\10.2.0\db_1\database\initmysid.ora
db_name=mydb
sga_target=209715200
然后启动实例,恢复参数文件
C:\>rman target /
RMAN> startup nomount
RMAN> restore spfile from 'E:\oracle\product\10.2.0\flash_recovery_area\MYDB\BAC
KUPSET\2011_09_10\O1_MF_NCSNF_TAG20110910T094752_76OJH34D_.BKP';
3)恢复控制文件
关闭数据库,利用恢复的参数文件启动实例
RMAN> shutdown immediate
RMAN> startup nomount
RMAN> restore controlfile from 'E:\oracle\product\10.2.0\flash_recovery_area\MYD
B\BACKUPSET\2011_09_10\O1_MF_NCSNF_TAG20110910T094752_76OJH34D_.BKP';
4)恢复数据文件
RMAN> shutdown immediate
RMAN> startup mount
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
3、备份时是read only
restore database check readonly;
run{
set newname for datafile 'E:\oracle\product\10.2.0\oradata\mydb\users01.dbf' to 'E:\oracle\product\10.2.0\oradata\mydb\users01.dbf';
restore tablespace users;
}
4、镜像备份
RMAN> backup as copy database;
--模拟快速临时恢复使用
SQL> select * from t01;
ID
----------
10
20
SQL> insert into t01 values(30);
SQL> commit;
SQL> shutdown immediate
删除数据文件users01.dbf
模拟快速恢复,将数据文件直接指定到备份位置,恢复并使用
SQL> startup
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\USERS01.DBF'
SQL> alter database rename file 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\USERS01.D
BF' to 'd:\users01.dbf';
SQL> recover datafile 4;
Media recovery complete.
SQL> alter database open;
5、压缩备份
RMAN> backup as compressed backupset database;
6、增量备份
6.1 备份集
level 0: 全备
level 1: 在上一个备份基础上,备份变化过的内容。如果没有0级备份,则做全备
level 2: 累积备份
RMAN> backup incremental level 0 database;
RMAN> backup incremental level 1 database;
增加变化跟踪
SQL> alter database enable block change tracking using file 'd:\dat.trc';
Database altered.
RMAN> backup incremental level 2 database;
场景:level 0备份,增加数据文件,level 1,模拟数据文件全部损坏,查看还原
的数据文件是否包含新增加的文件
6.2 镜像前滚
C:\>rman target /
RMAN> backup as copy incremental level 0 database tag 'fullcopy';
RMAN> backup incremental level 1 for recover of copy tag 'fullcopy' database;
RMAN> recover copy of database with tag 'fullcopy';
练习:备份后创建的表空间没有备份,但损坏,如何恢复
SQL> create tablespace tbs2 datafile 'd:\tbs2.dbf' size 5M;
Tablespace created.
SQL> create table t03 (id number)tablespace tbs2;
Table created.
SQL> insert into t03 values(10);
1 row created.
SQL> commit;
Commit complete.
模拟数据文件损坏,且没有备份
1)处理办法1,创建文件
SQL> shutdown immediate
删除d:\tbs2.dbf
SQL> startup
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'D:\TBS2.DBF'
SQL> alter database create datafile 'D:\TBS2.DBF' as 'E:\TBS2.DBF';
SQL> recover tablespace tbs2;
SQL> alter database open;
SQL> select * from t03;
ID
----------
10
2)处理办法2,删除文件
SQL> shutdown immediate
删除e:\tbs2.dbf
SQL> startup
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'E:\TBS2.DBF'
SQL> alter database datafile 6 offline drop;
SQL> alter database open;
7、不完全恢复
1)备份数据库
2)模拟变化,记录scn号
SQL> create table t03 (id number) tablespace users;
Table created.
SQL> insert into t03 values(100);
1 row created.
SQL> commit;
Commit complete.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
222368
SQL> insert into t03 values(200);
1 row created.
SQL> commit;
Commit complete.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
222376
SQL> insert into t03 values(300);
1 row created.
SQL> commit;
Commit complete.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
222381
3)执行不完全恢复
RMAN> shutdown immediate
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 251658240 bytes
Fixed Size 1248356 bytes
Variable Size 83887004 bytes
Database Buffers 159383552 bytes
Redo Buffers 7139328 bytes
RMAN> restore database until scn 222376;
RMAN> recover database until scn 222376;
RMAN> alter database open resetlogs;
验证数据
C:\>sqlplus / as sysdba
SQL> select * from t03;
ID
----------
100
200
RMAN> restore database until {scn 222376|time 'xxxx'|sequence xx};
RMAN> recover database until {scn 222376|time 'xxxx'|sequence xx};
8、表空间不完全恢复(表空间基于时间点恢复TSPITR)
sqlplus / as sysdba
SQL> select * from t03;
ID
----------
100
200
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> insert into t03 values(300);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;
110000
SQL> insert into t03 values(400);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;
110010
SQL>exit;
rman target /
RMAN> recover tablespace users until scn 110000 auxiliary destination 'd:\incomp
lete';
sqlplus / as sysdba
SQL> alter tablespace user online;
SQL> select * from t03;
ID
----------
100
200
300
9、catalog
应该建一个独立的数据库:
C:\>sqlplus sys/111111@remote as sysdba
SQL> create tablespace tbs1 datafile 'd:\tbs1.dbf' size 50M autoextend on next 5M;
SQL> create user rman identified by rman default tablespace tbs1 quota unlimited on tbs1;
SQL> grant RECOVERY_CATALOG_OWNER to rman;
连接
C:\>rman target / catalog rman/rman@remote
创建恢复目录(表和视图)
RMAN> create catalog;
注册数据库
RMAN> register database;
取消注册 unregister database;
查incarnation信息
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 9 MYDB 2675868718 PARENT 1 03-SEP-11
1 10 MYDB 2675868718 PARENT 203470 10-SEP-11
1 2 MYDB 2675868718 CURRENT 222381 10-SEP-11
备份数据库
RMAN> backup database;
模拟数据变化
create table test(id number);
insert into test values(100);
commit;
select dbms_flashback.get_system_change_number from dual;
231157
insert into test values(200);
commit;
select dbms_flashback.get_system_change_number from dual;
231172
insert into test values(300);
commit;
select dbms_flashback.get_system_change_number from dual;
231178
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
1 1 2 52428800 1 NO CURRENT 226678 10-SEP-11
2 1 1 52428800 1 YES INACTIVE 222381 10-SEP-11
3 1 0 52428800 1 YES UNUSED 0
第一次不完全恢复
C:\>rman target / catalog rman/rman@remote
rman> shutdown immediate;
rman>startup mount;
rman>restore database until scn 231172;
rman>recover database until scn 231172;
rman>alter database open resetlogs;
C:\>sqlplus / as sysdba
SQL> select * from test;
ID
----------
100
200
第二次不完全恢复
C:\>rman target / catalog rman/rman@remote
rman> shutdown immediate;
rman>startup mount;
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 9 MYDB 2675868718 PARENT 1 03-SEP-11
1 10 MYDB 2675868718 PARENT 203470 10-SEP-11
1 2 MYDB 2675868718 PARENT 222381 10-SEP-11
1 264 MYDB 2675868718 CURRENT 231175 10-SEP-11
RMAN> reset database to incarnation 2;
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 9 MYDB 2675868718 PARENT 1 03-SEP-11
1 10 MYDB 2675868718 PARENT 203470 10-SEP-11
1 2 MYDB 2675868718 CURRENT 222381 10-SEP-11
1 264 MYDB 2675868718 ORPHAN 231175 10-SEP-11
rman>restore database until scn 231172;
rman>recover database until scn 231172;
rman>alter database open resetlogs;
C:\>sqlplus / as sysdba
SQL> select * from test;
ID
----------
100
第三次不完全恢复
C:\>rman target / catalog rman/rman@remote
rman> shutdown immediate;
rman>startup mount;
RMAN> reset database to incarnation 2;
rman>restore database until scn 231178;
rman>recover database until scn 231178;
rman>alter database open resetlogs;
C:\>sqlplus / as sysdba
SQL> select * from test;
ID
----------
100
200
300
10、备份脚本
创建脚本
RMAN> create script f1{
allocate channel c1 device type disk;
backup database;
delete noprompt obsolete;
}
查询视图
select * from RC_STORED_SCRIPT_LINE;
执行脚本
RMAN> run{
execute script f1;
}
创建备份执行文件
full.sql
run{
execute script f1;
}
backup.cmd
rman target / catalog rman/rman@mydb cmdfile=d:\full.sql trace=d:\backup.trc append
select * from rc_database;
select * from rc_stored_script;
11、块恢复
create tablespace tbs datafile 'd:\tbs.dbf' size 5M;
create table t01 tablespace tbs as select * from dba_objects;
执行rman备份:
rman target /
rman> backup database;
select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid),
dbms_rowid.rowid_row_number(rowid) from t01 where rownum<2;
1~8块 文件头
9~11块 段头
破坏文件
验证方法
1)全表扫描
SQL> select count(*) from t01;
select count(*) from t01
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 11)
ORA-01110: data file 7: 'D:\TBS.DBF'
2)分析表
SQL> analyze table t01 compute statistics;
analyze table t01 compute statistics
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 11)
ORA-01110: data file 7: 'D:\TBS.DBF'
3)借助导出工具empdp
D:\>exp userid='sys/1 as sysdba' file=dump.dat tables=(t01)
Export: Release 10.2.0.1.0 - Production on Sun Sep 11 09:53:58 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T01
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 7, block # 11)
ORA-01110: data file 7: 'D:\TBS.DBF'
Export terminated successfully with warnings.
4)使用rman
D:\>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Sun Sep 11 09:54:20 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: MYDB (DBID=2675868718)
RMAN> backup validate tablespace tbs;
Starting backup at 11-SEP-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=133 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00007 name=D:\TBS.DBF
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-SEP-11
查询视图
select * from v$database_block_corruption;
5)使用dbverify
D:\>dbv file=d:\tbs.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Sun Sep 11 09:49:23 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = d:\tbs.dbf
Page 11 is marked corrupt
Corrupt block relative dba: 0x01c0000b (file 7, block 11)
Bad header found during dbv:
Data in bad block:
type: 153 format: 1 rdba: 0x99999999
last change scn: 0x9999.99999999 seq: 0x99 flg: 0x99
spare1: 0x99 spare2: 0x99 spare3: 0x9999
consistency value in tail: 0x29f42301
check value in block header: 0x9999
block checksum disabled
DBVERIFY - Verification complete
Total Pages Examined : 640
Total Pages Processed (Data) : 146
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 19
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 474
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 272882 (0.272882)
块恢复
RMAN> blockrecover datafile 7 block 11;
相关参数
SQL> show parameter db_block
NAME TYPE VALUE
------------------------------------ ----------- ---------------
db_block_checking string FALSE
db_block_checksum string true
db_block_checksum:dbwN写数据文件时根据块内容计算一个校验码存入块,
在读数据时,根据块内容计算校验码与块写入时的校验码比对,判断块是否
出现错误。
db_block_checking:内存中的块校验
SQL> set timing on
SQL> show parameter db_block
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_checking string TRUE
SQL> create table t01 tablespace users as select * from dba_objects;
SQL> update t01 set object_name='aa1';
Elapsed: 00:00:01.60
SQL> alter system flush buffer_cache;
SQL> alter system set db_block_checking=false;
SQL> update t01 set object_name='aa1';
- Oracle 培训笔记 9.10 rman(二)
- Oracle 培训笔记 9.4 rman
- Oracle培训笔记 8.6 逻辑结构(二)
- ORACLE RMAN 命令总结二
- Java培训笔记二
- Oracle课程培训笔记
- oracle 培训笔记 约束
- oracle 培训笔记[1]
- oracle 培训笔记[2]
- oracle 培训笔记[3]
- oracle 培训笔记[4]
- Oracle 培训笔记
- Oracle培训笔记
- Oracle 学习笔记: RMAN常用命令
- RHCE 培训笔记(二) 文件系统
- Oracle培训笔记 8.6 用户权限
- Oracle培训笔记 8.7 审计
- Oracle 培训笔记 9.4 闪回
- 解决头文件相互包含问题的方法
- QPaintDevice类参考
- Latex中文简历模板
- Oracle 培训笔记 9.11 主动维护
- CentOS开机自动运行程序的脚本
- Oracle 培训笔记 9.10 rman(二)
- log4j和slf4j
- 不使用库函数将整数转换为字符串或将字符串转换为整数
- epoll LT VS ET (ZZ)
- 安全书籍推荐
- PL\SQL 在编辑的情况下进行排序
- 图像处理方法
- Google Protocol Buffer 学习
- JSF的编码和解码