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

Oracle rman 備份與恢復臨時表空間的檔案問題解決

相關文章

Oracle rman 備份與恢復 臨時表空間的檔案問題解決

相關文章

Oracle rman 備份與恢復臨時表空間的檔案問題解決