Oracle rman 备份与恢复临时表空间的文件问题解决

oracle

2011-08-07

关注关注

---------- Oracle rman 备份与恢复 ------
-- rman 自动备份脚本 2011/7/23 14:32:55
rman target / log=/oracle/bk_rman/rmanbackup.log <<!
# -- as compressed backupset 压缩备份
# -- filesperset 2 用多少个线程;---好像翻译名字不对;
backup as compressed backupset filesperset 2 database format '/oracle/bk_rman/%u';
backup archivelog all format '/oracle/bk_rman/%u.bk';
delete noprompt obsolete;
!
-- 玩恢复;2011/7/23 14:38:36
-- 做业务
col NAME for a50
select FILE#, name from V$datafile;
update scott.emp set sal=sal+1;
commit;
alter system switch logfile;
-- rman 恢复到新的位置 2011/7/23 15:15:48
-- 假如源文件为去哪
-- 到哪去
-- 源文件
-- 源文件
-- 到哪去
-- 去 rman 中执行;
RUN
{
ALLOCATE CHANNEL d1 DEVICE TYPE DISK;
SQL "ALTER TABLESPACE users OFFLINE IMMEDIATE";
SET NEWNAME FOR DATAFILE '/oracle/oradata/orcl_new/users01.dbf'
TO '/oracle/oradata/users01.QQ';
RESTORE DATAFILE '/oracle/oradata/orcl_new/users01.dbf';
SWITCH DATAFILE '/oracle/oradata/orcl_new/users01.dbf';
RECOVER DATAFILE '/oracle/oradata/users01.QQ';
SQL "ALTER TABLESPACE users ONLINE";
}
------- rman 远程服务器使用 rman 备份的数据库恢复到本地实验2011/7/23 20:55:23 -----------
------- begin --------
-- 首先把 rman 备份拷贝到本地; scp... /tmp/rman_bk
1.无配置启动
1)rman target /
在日志中找出pfile文件参数;并写出initxxx.ora中;
2)sqlplus 中 startup nomount;
2.找控制文件, 从拷贝回来的备份文件中;
找到从备份文件中找出存储控制文件的那个备份文件,特性是,小,时间靠后;
pfile中指定controfile位置 -- 参考最后的pfile 文件格式; initXXX.ora
sqlplus
startup nomount force
3.-- 恢复控制文件文件是根据 2 步骤找出来的;
rman
restore controlfile from '/tmp/rman_bk/0kmi5ov2'
4.登记到catalog
-- sqlplus 中
startup force mount
-- rman 中, 校验 copy 与backup 文件
crosscheck copy;
crosscheck backup;
-- 查看恢复ctlF中记录的copy(archivelog), backup(rman的备份)信息
list copy ;
list backup;
-- 清空恢复的文件中的copy(archivelog), backup(rman的备份) 记录
delete noprompt expired copy;
delete noprompt expired backup;
-- 登记备份的文件到 controlfile中;
catalog start with '/tmp/rman_bk';
-- 恢复 spfile --> 查看在哪个备份文件中;
list backup of spfile;
restore spfile;
---------- 将数据文件恢复到新的路径 -- 控制文件从存储了各个数据文件的存放地址,所以改恢复路经;-------------
-- 1.组合成要恢复的路经设置语句;
select 'set newname for datafile '''||name||''' to ''/oracle/oradata/orcl/'|| substr(name,7)||''';' from v$datafile;
-- 2. rman中执行恢复 set newname .... 来自于 1.的输出结果
run
{
set newname for datafile '/o254/system01.dbf' to '/oracle/oradata/orcl/system01.dbf';
set newname for datafile '/o254/undotbs01.dbf' to '/oracle/oradata/orcl/undotbs01.dbf';
set newname for datafile '/o254/sysaux01.dbf' to '/oracle/oradata/orcl/sysaux01.dbf';
set newname for datafile '/o254/users01.dbf' to '/oracle/oradata/orcl/users01.dbf';
set newname for datafile '/o254/example01.dbf' to '/oracle/oradata/orcl/example01.dbf';
restore database;
switch datafile all;
recover database;
}
/* 出现如下错误,正常--- 因为log文件位置不对;
RMAN-06054: media recovery requesting unknown log: thread 1 seq 9 lowscn 1531825
*/
-- 3. 在sqlplus中修改日志文件路经因 controlfile 文件中存储了日志文件的路经( rman 备份的那个 controlfile ),所以要替换成新的路经;
select 'alter database rename file '''||member||''' to '''|| replace(member,'/o254/','/oracle/oradata/orcl/')||''';' from v$logfile;
-- 得出如下修改命令 --> 修改 controlfile 存储日志文件的地址;
alter database rename file '/o254/redo03.log' to '/oracle/oradata/orcl/redo03.log';
alter database rename file '/o254/redo02.log' to '/oracle/oradata/orcl/redo02.log';
alter database rename file '/o254/redo01.log' to '/oracle/oradata/orcl/redo01.log';
-- 启动 DB 用 resetlog方式;
alter database open resetlog;
----- end ---------
----------------- 临时表空间的文件恢复后不能用的解决方法 ---------------------
-- 查看临时表空间信息;
select file#, NAME, status, CREATION_TIME from v$tempfile;
-- 临时表空间增加文件;
alter tablespace temp add tempfile '/oracle/oradata/orcl/temp02.dbf'
size 50m
maxsize 100m
autoextend on;
-- 让临时表空间数据文件下线, 并删除之
alter database tempfile '/oracle/oradata/orcl/temp.dbf' offline;
alter database tempfile '/oracle/oradata/orcl/temp.dbf' drop ;
-- 查询出表空间创建的 DDL语句;
set long 99999
set pagesize 9999
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.TABLESPACE_NAME) from dba_tablespaces ts;
---------------- end --------------------------------------------------------
---- pfile 文件样例 initXXX.ora------------------------
__shared_pool_size = 113246208
__large_pool_size = 4194304
__java_pool_size = 4194304
__streams_pool_size = 0
sga_target = 285212672
pga_aggregate_target = 94371840
db_block_size = 8192
__db_cache_size = 159383552 # 152M
compatible = 10.2.0.1.0
control_files = /oracle/oradata/orcl/control01.ctl, /oracle/oradata/orcl/control02.ctl, /oracle/oradata/orcl/control03.ctl
# archive file save path
log_archive_dest_1 = 'location=/oracle/arc'
log_archive_format = %s_%t_%r.arc
db_file_multiblock_read_count= 16
db_recovery_file_dest = /oracle/flash_recovery_area
db_recovery_file_dest_size= 2147483648
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
job_queue_processes = 10
background_dump_dest = /oracle/admin/orcl/bdump
user_dump_dest = /oracle/admin/orcl/udump
core_dump_dest = /oracle/admin/orcl/cdump
audit_file_dest = /oracle/admin/orcl/adump
db_name = orcl
open_cursors = 300