- 挂牌上市企业
- 60秒人工响应
- 99.99%连通率
- 7*24h人工
- 故障100倍补偿
Oracle rman 备份与恢复 临时表空间的文件问题解决 |
| 发布时间: 2012/8/26 16:13:44 |
|
1.---------- Oracle rman 备份与恢复 ------ 2.-- rman 自动备份脚本 2011/7/23 14:32:55 3.rman target / log=/Oracle/bk_rman/rmanbackup.log <<! 4.# -- as compressed backupset 压缩备份 5.# -- filesperset 2 用多少个线程;---好像翻译名字不对; 6.backup as compressed backupset filesperset 2 database format '/Oracle/bk_rman/%u'; 7.backup archivelog all format '/Oracle/bk_rman/%u.bk'; 8.delete noprompt obsolete; 9.! 10. 11. 12. 13.-- 玩恢复;2011/7/23 14:38:36 14.-- 做业务 15.col NAME for a50 16.select FILE#, name from V$datafile; 17.update scott.emp set sal=sal+1; 18.commit; 19.alter system switch logfile; 20. 21. 22.-- rman 恢复到新的位置 2011/7/23 15:15:48 23. -- 假如 源文件 为 去哪 24. -- 到哪去 25. -- 源文件 26. -- 源文件 27. -- 到哪去 28.-- 去 rman 中执行; 29.RUN 30.{ 31.ALLOCATE CHANNEL d1 DEVICE TYPE DISK; 32.SQL "ALTER TABLESPACE users OFFLINE IMMEDIATE"; 33.SET NEWNAME FOR DATAFILE '/Oracle/oradata/orcl_new/users01.dbf' 34.TO '/Oracle/oradata/users01.QQ'; 35.RESTORE DATAFILE '/Oracle/oradata/orcl_new/users01.dbf'; 36.SWITCH DATAFILE '/Oracle/oradata/orcl_new/users01.dbf'; 37.RECOVER DATAFILE '/Oracle/oradata/users01.QQ'; 38.SQL "ALTER TABLESPACE users ONLINE"; 39.} 40. 41. 42. 43.------- rman 远程服务器使用 rman 备份的数据库 恢复到本地 实验2011/7/23 20:55:23 ----------- 44.------- begin -------- 45.-- 首先 把 rman 备份拷贝到本地; scp... /tmp/rman_bk 46.1.无配置启动 47. 1)rman target / 48. 在日志中找出pfile文件参数;并写出initxxx.ora中; 49. 2)sqlplus 中 startup nomount; 50.2.找控制文件, 从拷贝回来的备份文件中; 51. 找到从备份文件中找出存储控制文件的那个备份文件,特性是,小,时间靠后; 52. pfile中指定controfile位置 -- 参考 最后的pfile 文件格式; initXXX.ora 53. sqlplus 54. startup nomount force 55.3.-- 恢复控制文件 文件是根据 2 步骤找出来的; 56. rman 57. restore controlfile from '/tmp/rman_bk/0kmi5ov2' 58.4.登记到catalog 59. -- sqlplus 中 60. startup force mount 61. -- rman 中, 校验 copy 与backup 文件 62. crosscheck copy; 63. crosscheck backup; 64. 65. -- 查看恢复ctlF中记录的copy(archivelog), backup(rman的备份)信息 66. list copy ; 67. list backup; 68. 69. -- 清空恢复的文件中的copy(archivelog), backup(rman的备份) 记录 70. delete noprompt expired copy; 71. delete noprompt expired backup; 72. 73. -- 登记备份的文件到 controlfile中; 74. catalog start with '/tmp/rman_bk'; 75. 76. -- 恢复 spfile --> 查看在哪个备份文件中; 77. list backup of spfile; 78. restore spfile; 79. 80. ---------- 将数据文件恢复到新的路径 -- 控制文件从存储了各个数据文件的存放地址,所以改恢复路经;------------- 81. -- 1.组合成要恢复的路经设置语句; 82. select 'set newname for datafile '''||name||''' to ''/Oracle/oradata/orcl/'|| substr(name,7)||''';' from v$datafile; 83. 84. -- 2. rman中执行恢复 set newname .... 来自于 1.的输出结果 85. run 86. { 87. set newname for datafile '/o254/system01.dbf' to '/Oracle/oradata/orcl/system01.dbf'; 88.set newname for datafile '/o254/undotbs01.dbf' to '/Oracle/oradata/orcl/undotbs01.dbf'; 89.set newname for datafile '/o254/sysaux01.dbf' to '/Oracle/oradata/orcl/sysaux01.dbf'; 90.set newname for datafile '/o254/users01.dbf' to '/Oracle/oradata/orcl/users01.dbf'; 91.set newname for datafile '/o254/example01.dbf' to '/Oracle/oradata/orcl/example01.dbf'; 92.restore database; 93.switch datafile all; 94.recover database; 95. } 96. /* 出现如下错误,正常--- 因为log文件位置不对; 97. RMAN-06054: media recovery requesting unknown log: thread 1 seq 9 lowscn 1531825 98. */ 99. 100. -- 3. 在sqlplus中修改日志文件路经 因 controlfile 文件中存储了 日志文件的路经( rman 备份的那个 controlfile ),所以要替换成新的路经; 101. select 'alter database rename file '''||member||''' to '''|| replace(member,'/o254/','/Oracle/oradata/orcl/')||''';' from v$logfile; 102. -- 得出如下修改命令 --> 修改 controlfile 存储日志文件的地址; 103.alter database rename file '/o254/redo03.log' to '/Oracle/oradata/orcl/redo03.log'; 104.alter database rename file '/o254/redo02.log' to '/Oracle/oradata/orcl/redo02.log'; 105.alter database rename file '/o254/redo01.log' to '/Oracle/oradata/orcl/redo01.log'; 106. 107.-- 启动 DB 用 resetlog方式; 108.alter database open resetlog; 109. 110.----- end --------- 111. 112. 113. 114. 115.----------------- 临时表空间的文件恢复后不能用的解决方法 --------------------- 116.-- 查看临时表空间信息; 117. select file#, NAME, status, CREATION_TIME from v$tempfile; 118. 119. -- 临时表空间 增加文件; 120. alter tablespace temp add tempfile '/Oracle/oradata/orcl/temp02.dbf' 121. size 50m 122. maxsize 100m 123. autoextend on; 124. 125. -- 让临时表空间数据文件下线, 并删除之 126. alter database tempfile '/Oracle/oradata/orcl/temp.dbf' offline; 127. alter database tempfile '/Oracle/oradata/orcl/temp.dbf' drop ; 128. 129.-- 查询出表空间创建的 DDL语句; 130.set long 99999 131.set pagesize 9999 132.SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.TABLESPACE_NAME) from dba_tablespaces ts; 133.---------------- end -------------------------------------------------------- 134. 135. 136. 137. 138. 139. 140.---- pfile 文件 样例 initXXX.ora------------------------ 141.__shared_pool_size = 113246208 142.__large_pool_size = 4194304 143.__java_pool_size = 4194304 144.__streams_pool_size = 0 145.sga_target = 285212672 146.pga_aggregate_target = 94371840 147. 148.db_block_size = 8192 149.__db_cache_size = 159383552 # 152M 150.compatible = 10.2.0.1.0 151.control_files = /Oracle/oradata/orcl/control01.ctl, /oracle/oradata/orcl/control02.ctl, /oracle/oradata/orcl/control03.ctl 152.# archive file save path 153.log_archive_dest_1 = 'location=/Oracle/arc' 154.log_archive_format = %s_%t_%r.arc 155.db_file_multiblock_read_count= 16 156.db_recovery_file_dest = /Oracle/flash_recovery_area 157.db_recovery_file_dest_size= 2147483648 158.undo_management = AUTO 159.undo_tablespace = UNDOTBS1 160.remote_login_passwordfile= EXCLUSIVE 161.job_queue_processes = 10 162.background_dump_dest = /Oracle/admin/orcl/bdump 163.user_dump_dest = /Oracle/admin/orcl/udump 164.core_dump_dest = /Oracle/admin/orcl/cdump 165.audit_file_dest = /Oracle/admin/orcl/adump 166.db_name = orcl 167.open_cursors = 300 本文出自：亿恩科技【www.enkj.com】 |
0371-60135900
京公网安备41019702002023号
香港服务器租用
美国服务器租用
电信骨干机房
联通骨干机房
