oracle数据交换留痕,oracle rman 备份与恢复 临时表空间的文件问题解决 留痕

---------- 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