文章內(nèi)容

Oracle rman 備份與恢復(fù) 臨時表空間的文件問題解決

發(fā)布時間: 2012/8/26 16:13:44

1.---------- Oracle rman 備份與恢復(fù) ------
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.-- 玩恢復(fù);2011/7/23 14:38:36   14.-- 做業(yè)務(wù)   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 恢復(fù)到新的位置   2011/7/23 15:15:48   23. -- 假如源文件為去哪   24. -- 到哪去   25. -- 源文件   26.   -- 源文件   27.   -- 到哪去   28.-- 去 rman 中執(zhí)行;   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 遠程服務(wù)器使用 rman 備份的數(shù)據(jù)庫恢復(fù)到本地實驗2011/7/23 20:55:23 -----------   44.------- begin --------   45.-- 首先把 rman 備份拷貝到本地; scp... /tmp/rman_bk    46.1.無配置啟動
47. 1)rman target /
48.    在日志中找出pfile文件參數(shù);并寫出initxxx.ora中;
49. 2)sqlplus 中 startup nomount;
50.2.找控制文件, 從拷貝回來的備份文件中;
51. 找到從備份文件中找出存儲控制文件的那個備份文件,特性是,小,時間靠后;
52. pfile中指定controfile位置 -- 參考最后的pfile 文件格式; initXXX.ora   53. sqlplus
54.    startup nomount force 55.3.-- 恢復(fù)控制文件文件是根據(jù) 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.    -- 查看恢復(fù)ctlF中記錄的copy(archivelog), backup(rman的備份)信息   66.    list copy ;
67.    list backup;
68.
69.    -- 清空恢復(fù)的文件中的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.    -- 恢復(fù) spfile --> 查看在哪個備份文件中;    77.    list backup of spfile; 78.    restore spfile;
79.
80.    ---------- 將數(shù)據(jù)文件恢復(fù)到新的路徑 -- 控制文件從存儲了各個數(shù)據(jù)文件的存放地址,所以改恢復(fù)路經(jīng);-------------   81.    -- 1.組合成要恢復(fù)的路經(jīng)設(shè)置語句;   82.    select 'set newname for datafile '''||name||''' to ''/Oracle/oradata/orcl/'|| substr(name,7)||''';' from v$datafile; 83.
84.    -- 2. rman中執(zhí)行恢復(fù) set newname .... 來自于 1.的輸出結(jié)果   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.    /* 出現(xiàn)如下錯誤,正常--- 因為log文件位置不對;   97.    RMAN-06054: media recovery requesting unknown log: thread 1 seq 9 lowscn 1531825
98.    */
99.
100.    -- 3. 在sqlplus中修改日志文件路經(jīng) 因 controlfile 文件中存儲了日志文件的路經(jīng)( rman 備份的那個 controlfile ),所以要替換成新的路經(jīng);   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.----------------- 臨時表空間的文件恢復(fù)后不能用的解決方法 ---------------------   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. -- 讓臨時表空間數(shù)據(jù)文件下線, 并刪除之   126. alter database tempfile '/Oracle/oradata/orcl/temp.dbf' offline; 127. alter database tempfile '/Oracle/oradata/orcl/temp.dbf' drop ; 128.
129.-- 查詢出表空間創(chuàng)建的 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

本文出自：億恩科技【mszdt.com】

服務(wù)器租用/服務(wù)器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]