来自oracle官方培训的rman资料
===================================
oracle backup and recover workshop2
===================================
1\a datafile is lost
2\a tablespace is lost
3\read-only tablespace is lost
4\all controlfiles is lost
5\loss of all datafiles ,one has no backup
6\non-current redo log file are lost
7\all redo file are lost
8\a tempfile is lost
9\loss of complete database
10\loss of an important table
11\loss of an important tablespace
12\recovery through resetlogs
13\transportable tablespace
scenario1
a datafile is lost
problem: a datafile is lost,the problem tablespace has more than one datafile
backup: rman online full database backup
1.check v$recover_file ,alert.log and os file
2.rman target sys/chaneg_on_install@s01 catalog rman/rman@r01
3.if catalog database is not available ,another alternative is to use target database controlfile.
4.check whether the lost datafile has backup
恢复脚本：
恢复步骤：
1、分配通道
2、SQL使数据文件offline
3、restore datafile
4、recover datafile
5、SQL使数据文件online
rman>run{
allocate channel d1 type disk;
sql"alter database datafile 5 offline";
restore datafile 5;
recover datafile 5;
sql"alter database datafile 5 online";
}
//测试
1、CREATE TABLESPACE user_test DATAFILE 'D:\oracle\product\10.2.0\oradata\orcl\user_test01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 128K;
alter tablespace user_test add datafile 'D:\oracle\product\10.2.0\oradata\orcl\user_test02.dbf' SIZE 50M;
alter tablespace user_test add datafile 'D:\oracle\product\10.2.0\oradata\orcl\user_test03.dbf' SIZE 50M;
1、备份数据库 全备
2、在文件系统删除一个数据文件
3、恢复数据库执行RMAN
4、成功
------------------------------------------------------------------
scenario2
a tablespace is lost
problem:the users tablespace has been moved to another disk(in our case,it's oradata1 directory)
afterward the users tablespace is lost due to disk crash,and the disk is not repaired yet,database is closed.
backup:rman online full database backup;
steps for scenario 2
startup open will fall with:
ora-01157:cannot identify/lock datafile 5--see dbwr trace file
ora-01110:data file :'./oradata1/users1.dbf'
SQL>select t.name "ts_name",d.name,r.error from v$tablespace t,v$datafile d,v$recover_file r
where t.ts#=d.ts# and r.file#=d.file#;
rman script.:
run{
startup mount;
allocate channel d1 type disk;
sql "alter database datafile 5,6,7 offline";
sql "alter database open";
set newname for datafile 5 to 'E:\oracle\oradata\USER_TEST01.DBF';
set newname for datafile 6 to 'E:\oracle\oradata\USER_TEST02.DBF';
set newname for datafile 7 to 'E:\oracle\oradata\USER_TEST03.DBF';
restore tablespace user_test;
switch datafile 5;
switch datafile 6;
switch datafile 7;
recover tablespace user_test;
sql "alter tablespace user_test online";
}
//
LOSS OF SYSTEM TABLESPACE
if the lost datafile belongs to system tablespace
RMAN>startup mount
RMAN-06196:Oracle instance started
RMAN-06199:Database mounted
rman>run{
allocate channel d1 type disk;
retore datafile 1;
recover datafile 1;
sql "alter database open";
}
//loss of non-essential data files
.startup mount
.alter database datafile ...offline
.alter database open;
if it's a temporary tablespace(with no tempfile);
----drop tablespace temp;
----create tablespace temp;
====================================================
Loss of Non-Essential Data Files --index tablespace
====================================================
if it's an index tablespace
--alter table .. disable primary key cascade;
--drop tablespace index including contents cascade constraints;
--create tablespace indx..;
--alter table .. enbale primary key using index tablespace indx;
--recreate foreign key index.
scenario 3
read-only tablespace is lost
problem :a-read-only tablespace is lost or damaged.
RMAN backup :RMan online full database backup,the problem tablespace is writable when the last backup occurred.
PS: This scenario has no practice.
> select status from dba_tablespaces where tablespace_name='TOOLS';
>run{
allocate channel d1 type disk;
sql "alter tablespace tools offline immediate";
restore tablespace tools;
recover tablespace tools;
sql"alter tablespace tools online";
}
read_only tablespace backup
only one backup is needed after altering the tablespace to read-only;
resume a normal backup schedule for that tablespace after marking it read-write;
trips
always backup your controlfiles and affected datafiles immediately whenever your your database structure changes.
read-only tablespace recovery
speical considerations must be taken for read-only tablespaces when re-creating a control file.
read-only when the last backup occurred,in this case,there's no need to apply any redo logs.
if the tablespace begin recovered id read-writable,but was read-only when the last backup occurred,in
this case,you need to apply the redo logs from the tablespace was made writable.
if the tablespace begin recovered is read-only ,but was writable when the last backup occurred,
in this case,you need to recover up to the time that the tablespace was made read only.
scenario 4
all controlfiles are lost
problem:all controlfiles have been lost,and the database is closed now,the database has a read-only tablespace.
RMAN backup:rman online full database backup.
1\
RMAN>STARTUP NOMOUNT;
CHECK WHETEHR CONTROLFILES BACKUP IS AVAILABLE.
run{
allocate channel d1 type disk;
restore controlfile;
alter database mount;
recover database;
alter database open resetlogs;
}
.backup your database immediately after open with resetlogs options.
.before you can use rman againg with a target database,that you have
opened with the resetlogs option,notify rman that you have reset the database
incarnation.
RMAN>reset database;
in oracle 9i,new incarnation of database will be registered in recovery catalog automatically
in this case.
2\other recovery issues.
.recovery will fail with ora-1244 if the controlfiles backup or recovery catalog does not has
latest database schema,you can check this wih
RMAN>report schema
.you can also recreate control files with command if you know the database schema;
>>create control file
CREATE CONTROLFILE REUSE DATABASE "S01"
NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 254
MAXINSTANCES 8
MAXLOGHISTORY 904
LOGFILE
GROUP 1 '/u01/dennis/s00/oradata/redo01.log' SIZE 500K,
GROUP 2 '/u01/dennis/s00/oradata/redo02.log' SIZE 500K,
GROUP 3 '/u01/dennis/s00/oradata/redo03.log' SIZE 500K
DATAFILE
'/u01/dennis/s00/oradata/system01.dbf',
'/u01/dennis/s00/oradata/rbs1.dbf',
'/u01/dennis/s00/oradata/temp1.dbf',
'/u01/dennis/s00/oradata/users1.dbf',
'/u01/dennis/s00/oradata/users2.dbf',
'/u01/dennis/s00/oradata/indx1.dbf'
CHARACTER SET UTF8;
another alternative to recreate controlfiles
1\startup nomount;
2\create controlfile;
CREATE CONTROLFILE REUSE DATABASE "S01"
NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 254
MAXINSTANCES 8
MAXLOGHISTORY 904
LOGFILE
GROUP 1 '/u01/dennis/s00/oradata/redo01.log' SIZE 500K,
GROUP 2 '/u01/dennis/s00/oradata/redo02.log' SIZE 500K,
GROUP 3 '/u01/dennis/s00/oradata/redo03.log' SIZE 500K
DATAFILE
'/u01/dennis/s00/oradata/system01.dbf',
'/u01/dennis/s00/oradata/rbs1.dbf',
'/u01/dennis/s00/oradata/temp1.dbf',
'/u01/dennis/s00/oradata/users1.dbf',
'/u01/dennis/s00/oradata/users2.dbf',
'/u01/dennis/s00/oradata/indx1.dbf'
CHARACTER SET UTF8;
3\recover database;(if the previous shutdown is not normal or immediate)
another alternative to recreate controlfile
open database will fail with error(ie:ora-1092),you have to:
>>alter database rename file 'MISSING00002' to '.../oradata/tools1.dbf';
>>alter database datafile '../tools1.dbf' online';
>>alter database open;
scenario 5
loss of all datafiles,one has no backup
problem:all datafiles are lost,the database has a read-only tablespace.
rman>backup: the latest rman online full database backup does not include a new datafiles
however we have all archived logs since this new datafile has been created.
datafile 8 has no backup
run{
startup mount;
allocate channel c1 type disk;
restore datafile 1,2,3,4,5,6,7;
sql "alter database create datafile "../oradata/users3.dbf" as "../oradata/users3/dbf""; >>该8号文件是没有备份的文件。重新创建
recover database;
alter database open;
}
！！！ do not use "restore database" unless you have all datafiles backup
backup datafiles which have no backup.
scenario 6
non-current redo log files are lost
problem:non-current redo log files are lost.
backup:the redo log group only have one memeber rman online full database backup.
>>
if the lost redo log file is inactive and archived:
>> alter database clear logfile group group#;
>>
if the lost redo log file is inactive and unarchived:
>> alter database clear unarchived logfile group group#;
backup database immediately due to archvied log files are lost,rman will skip the lost archived log
files in this case during backup provess.
.every redo log group is advised to have multiple members and put in different disk drives.
scenario 7
all redo log files are lost
problem :all redo log files are lost,database is closed.
backup: rman online full database backup.
>>
startup mount;
>>
check archived log destination to get the sequence number of the last archived log.
>>
if the last archived log sequence number is 22:
run{
startup mount;
allocate channel c1 type disk;
set until logseq 23 thread 1; //恢复1到23号日志文件 apply redo log
restore database check readonly; >> if a database has a read-only tablespaces,you have to restore database with check readonly;
reover databse;
alter databse open resetlogs;
}
ps:the trainee should modify the last archived log sequence number according to their cases
before starting recovery.
.backup database immediately;
other recovery issues.
>>
if a database has read-only tablespaces,you have to restore database with check readonly;
>>
rman>> restore database check readonly;
====================================
scrnario 8 a tempfile is lost
=====================================
problem:a file is lost,users gets errors:
ora-01110,ora-27041
(the file wes created with :create temporary tablespace...tempfile...?)
backup:RMAN online full database backup:
because space management is much simpler and more efficient in locally managed tablespaces,
use temporary tablespaces since they are the only temporary tablespaces completible with local managemnt.
you can find tempfiles in v$tempfile instead of v$dbfile or v$datafile;
since oracle does not record checkpoint information in tempfiles,oracle can start up a database with
a missing tempfile normally but,dbw0 will write a trace file indicating the tempfile is not found.
if you need to recreate control files,you have to add tempfiles manually after database is open.
SQL> SELECT FILE#,NAME FROM V$TEMPFILE;
You can drop the logical tempfile and add a new one
>>drop tempfile
>> alter database tempfile '../oradata/tmp.dbf' drop;
>>add tempfile
>> alter tablespace tmp add tempfile '../oradata/tmp.dbf' size 5m;
=========================================
scenario 9
loss of complete database
problem:current complete database(include redo log files add control files) has been lost
the database contains a read-only tablespace and tempfile.
RMAN Backup:RMAN online full database backup
=========================================
steps for scenario 9
.check the sequence number of the last archived log, for example the last one ie 9
.rman>run{
startup nomount;
allocate channel c1 tyep disk;
restore controlfile;
alter database mount;
set until logseq 10 thread 1;
restore database check readonly;
recover database;
sql "alter database open resetlogs";
}
PS:The trainee should modify the last archived log sequence number according to their cases
before starting recovery.
>>alter tablespace temp add tempfile '../oradata/tmp.dbf' size 5m;
>>rman reset database;
>>backup your database immediately.
========================================
Scenario 10
loss of an important table
problem:one important table is dropped by accident after 21:04:51;
RMAN backup:RMAN online full database backup
PS:This scenario is optional.
>> set nls_date_format='yyyy-mm-dd hh24:mi:ss'
backup your current controlfiles add redo log files to other location.
>>
run{
startup mount;
allocate channel c1 type disk;
set until time '2002-12-28 21:04:51';
restore database check readonly;
recover database;
sql "alter database open resetlogs";
}
>> in this case,data what was modified after 21:04:51 will be lost
check whether the dropped table is restored,if not,use the current controlfiles add redo log files backup to recover again.
RMAN>reset database
Backup your database immediately;
========================================
Scenario 11
Loss of na important tablespace
problem:one important tablespace is dropped by accident.
RMAN backup:RMAN online full database backup.
set nls_date_format='yyyy-mm-dd:hh24:mi:ss'
backup your current control files and redo log files to other location.
in alert log,you can find the exact time of dropping tablespace
set Dec 28 21 :13:49 2002
"DROP TABLESPACE TEST INCLUDING CONTENTS AND DATAFILES"
>>
RMAN>>
RUN{
startup nomount;
allocate channel d1 type disk;
set until time '2002-12-28:21:13:48';
restore controlfile;
alter database mount;
restore database check readonly;
recover database;
sql "alter database open resetlogs";
}
========================================
steps for scenario 11
check whether the dropped tablespace is restored,if not,use the current controlfiles and redo log files backup to recover again.
RMAN> Reset databsae;
BACKUP your database immediately.
PS:1、please DO NOT backup your database with RMAN here in order to implement the next scenario
2、please backup the archived log files NOW.
========================================
Scenario 13
Transportable tablespace
problem:some index blocks of system tablespace are corrupted,we need to rebuild the database as soon as possible.
overview of transportable tablespaces
.the transportable tablespaces feature is introduced in v8.1.5
.it is sometimes referred to as pluggable tablespaces.
.you need to have installed the enterprise edition of oracle to generate a transportable tablespace set.
.this mechanism consists in the combination of 2things:
-- an export of the metadata(instead of the data) of a tablespace.
-- a copy of the files belongoing to the tablespace.
-- it is much faster than a classical export/import.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7194105/viewspace-681992/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/7194105/viewspace-681992/
810
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
