oracle 熱備及恢復
熱備：
在熱備時，oracle會控制scn不發生變化，凍結塊頭。然後拷貝物理檔案，之後解凍塊頭，然scn可以變化。
熱備備份
|
首先確認是歸檔模式並且可用 SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination +DATA/orcl/ Oldest online log sequence 5 Next log sequence to archive 7 Current log sequence 7 SQL> 手動切換日誌確認是否切換成功 SQL> alter system switch logfile;
System altered.
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination +DATA/orcl/ Oldest online log sequence 6 Next log sequence to archive 8 Current log sequence 8 8SQL>
如果不是歸檔模式，將資料庫一致性關閉shutdown immediate; 啟動到mount狀態startup mount； 然後開啟歸檔模式 alter database archivelog； 最後確認是否成功 archive log list |
備份前的查詢
|
SQL> show user USER is "SYS" 當前庫名 SQL> select name from v$database;
NAME --------- ORCL 例項名 SQL> select instance_name from v$instance;
INSTANCE_NAME ---------------- orcl 當前所有資料檔案及狀態 SQL> select file_name,tablespace_name,status,online_status from dba_data_files;
FILE_NAME TABLESPACE STATUS ONLINE_ ------------------------------------------------------- ---------- --------- ------- /u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1 AVAILABLE ONLINE /u01/app/oracle/oradata/orcl/system01.dbf SYSTEM AVAILABLE SYSTEM /u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX AVAILABLE ONLINE /u01/app/oracle/oradata/orcl/users01.dbf USERS AVAILABLE ONLINE /u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE AVAILABLE ONLINE
SQL> 檢視資料檔案位置 SQL> select name from v$datafile;
NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/orcl/system01.dbf /u01/app/oracle/oradata/orcl/undotbs01.dbf /u01/app/oracle/oradata/orcl/sysaux01.dbf /u01/app/oracle/oradata/orcl/users01.dbf /u01/app/oracle/oradata/orcl/example01.dbf
臨時檔案位置 SQL> select name from v$tempfile;
NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/orcl/temp01.dbf SQL> 日誌檔案位置 SQL> select member from v$logfile;
MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/orcl/redo2a.rdo /u01/app/oracle/oradata/orcl/redo2b.rdo /u01/app/oracle/oradata/orcl/redo1a.rdo /u01/app/oracle/oradata/orcl/redo3a.rdo /u01/app/oracle/oradata/orcl/redo3b.rdo /u01/app/oracle/oradata/orcl/redo1b.rdo
6 rows selected.
SQL> 控制檔案位置 SQL> select name from v$controlfile;
NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/oradata/orcl/control02.ctl
SQL> 引數檔案可以直接通過建立指定一個目錄 SQL> create pfile='/u01/app/....' from spfile;
密碼檔案位置 SQL> ho ls $ORACLE_HOME/dbs/orapw$ORACLE_SID /u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl
SQL>
|
凍結塊頭 進行資料庫級別備份，然後建立完整備份指令碼
|
凍結塊頭 SQL> alter database begin backup; 建立備份存放路徑 SQL> ho mkdir /u02/horcl
連線符生成備份資料檔案命令
SQL> select 'ho cp '||name||' /u02/horcl' from v$datafile;
'HOCP'||NAME||'/U02/HORCL' -------------------------------------------------------------------------------- ho cp /u01/app/oracle/oradata/orcl/system01.dbf /u02/horcl ho cp /u01/app/oracle/oradata/orcl/undotbs01.dbf /u02/horcl ho cp /u01/app/oracle/oradata/orcl/sysaux01.dbf /u02/horcl ho cp /u01/app/oracle/oradata/orcl/users01.dbf /u02/horcl ho cp /u01/app/oracle/oradata/orcl/example01.dbf /u02/horcl 拷貝命令直接執行 SQL> ho cp /u01/app/oracle/oradata/orcl/system01.dbf /u02/horcl ho cp /u01/app/oracle/oradata/orcl/undotbs01.dbf /u02/horcl ho cp /u01/app/oracle/oradata/orcl/sysaux01.dbf /u02/horcl ho cp /u01/app/oracle/oradata/orcl/users01.dbf /u02/horcl ho cp /u01/app/oracle/oradata/orcl/example01.dbf /u02/horcl
解凍塊頭 SQL> alter database end backup; 備份控制檔案 SQL> alter database backup controlfile to '/u02/horcl/control01.ctl'; 備份引數檔案 SQL> create pfile='/u02/horcl/initorcl.ora' from spfile;
建立備份指令碼 [oracle@yang ~]$ vi /u02/hotbak.sql spool /tmp/hot.sql select 'ho cp '||name||' /u02/horcl' from v$datafile; spool off ho sed -n '/^ho cp/p' /tmp/hot.sql >/tmp/hotorcl.sql alter database begin backup; start /tmp/hotorcl.sql alter database end backup; ho rm /tmp/hot*.sql alter database backup controlfile to '/u02/horcl/control01.ctl'; create pfile='/u02/horcl/initorcl.ora' from spfile; |
如果alter database begin backup; 執行失敗可以以表空間形式備份
|
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME ------------------------------ FILE_NAME -------------------------------------------------------------------------------- USERS /u01/app/oracle/oradata/orcl/users01.dbf SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf
TABLESPACE_NAME ------------------------------ FILE_NAME -------------------------------------------------------------------------------- SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf
SQL> 示例： SQL> alter tablespace system begin backup; SQL> ho cp /u01/app/oracle/oradata/orcl/system01.dbf /u02/horcl/ SQL> alter tablespace system end backup;
編寫指令碼 [oracle@yang ~]$ vi /u02/hotbak2.sql set heading off spool /tmp/hot.sql select 'alter tablespace '||tablespace_name||' begin backup; '||chr(10)||'ho cp '||file_name||' /u02/horcl'||chr(10)||'alter tablespace '||tablespace_name||' end backup;' from dba_data_files order by tablesoace_name; spool off start /tmp/hot.sql ho rm /tmp/hot.sql alter database backup controlfile to '/u02/horcl/control01.ctl'; create pfile='/u02/horcl/initorcl.ora' from spfile;
~ |
最簡單的還是RMAN熱備
|
確認預設存放備份目錄 SQL> show parameter db_rec
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/flash_recover_area/ db_recovery_file_dest_size big integer 2G db_recycle_cache_size big integer 0 SQL> 登入RMAN 進行全庫熱備 [oracle@yang ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon May 12 02:33:15 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1374419528)
RMAN> backup as copy database;
使用上面命令會自動備份所有資料檔案、spfile與controlfile到/u01/app/oracle/flash_recover_area/$ORACLE_SID/下
|
熱備恢復
控制檔案全部丟失
只要有一個存在就不需要恢復，直接cp或者修改引數即可
|
SQL>startup ORA-00205： error in identifying control file,check alter log for more info
首先確認檔案是否丟失 SQL> show parameter control_
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string /u01/app/oracle/oradata/orcl/ control01.ctl, /u01/app/oracle/ oradata/orcl/control02.ctl, /u01/app/oracle/oradata/orcl/ Control03.ctl SQL> ho ls /u01/app/oracle/oradata/orcl/control* ls: /u01/app/oracle/oradata/orcl/control*: No such file or directory
還原控制檔案 SQL> ho cp /u02/horcl/control01.ctl /u01/app/oracle/oradata/orcl/control01.ctl SQL> ho cp /u02/horcl/control01.ctl /u01/app/oracle/oradata/orcl/control02.ctl SQL> ho cp /u02/horcl/control01.ctl /u01/app/oracle/oradata/orcl/control03.ctl 將資料庫啟動到mount狀態 SQL> alter database mount; 然後open資料庫根據提示操作 SQL> alter database open; ERROR at line 1： ORA-01589：must use RESETLOGS or NORESETLOGS option for database open 一般情況下是需要進行介質恢復 SQL> recover database using backup controlfile;
SQL> Specify log: auto ---自動查詢需要的歸檔日誌 如果未找到需要指定日誌檔案路徑 SQL> recover database using backup controlfile;
SQL> Specify log: /u01/app/oracle/oradata/orcl/redo01.log --一次指定一個直到找到為止 /u01/app/oracle/oradata/orcl/redo02.log /u01/app/oracle/oradata/orcl/redo03.log
恢復完成 resetlogs方式開啟資料庫 SQL> alter database open resetlogs;
完成之後記得重新全備 [oracle@yang ~]$ vi /u02/hotbak.sql ho rm /u02/horcl/* ---以前備份無效了，可以直接刪除 spool /tmp/hot.sql select 'ho cp '||name||' /u02/horcl' from v$datafile; spool off ho sed -n '/^ho cp/p' /tmp/hot.sql >/tmp/hotorcl.sql alter database begin backup; start /tmp/hotorcl.sql alter database end backup; ho rm /tmp/hot*.sql alter database backup controlfile to '/u02/horcl/control01.ctl'; create pfile='/u02/horcl/initorcl.ora' from spfile; |
資料檔案丟失
分為：
1、可離線檔案 -------可以線上還原與恢復
2、不可離線檔案 ----需啟動到mount狀態還原恢復
|
1、可離線檔案丟失 SQL> select * from scott.dept; select * from scott.dept; * ERROR at line 1: ORA-01116:error in opening database file 4 ORA-01110: data file 4: ‘/u01/app/oracle/oradata/orcl/users01.dbf’ -----(users表空間丟失) ORA-27041: unable to open file
首先檢視資料檔案的狀態 SQL> select file#,status,enabled from v$datafile;
FILE# STATUS ENABLED ---------- ------- ---------- 1 SYSTEM READ WRITE 2 ONLINE READ WRITE 3 ONLINE READ WRITE 4 ONLINE READ WRITE 5 ONLINE READ WRITE SQL> 將檔案號為4的離線在檢視狀態 SQL> alter database datafile 4 offline; SQL> select file#,status,enabled from v$datafile;
FILE# STATUS ENABLED ---------- ------- ---------- 1 SYSTEM READ WRITE 2 ONLINE READ WRITE 3 ONLINE READ WRITE 4 RECOVER READ WRITE 5 ONLINE READ WRITE RECOVER :說明此檔案時不正常的需要恢復 SQL>
進行資料檔案還原，將備份的資料檔案拷貝到資料檔案原路徑 SQL> ho cp /u02/horcl/users01.dbf /u01/app/oracle/oradata/orcl/users01.dbf 將資料檔案聯機檢視是否需要media recover SQL> alter database datafile 4 online; ORA-01113： file 4 needs media recover ORA-01110: data file 4: ‘/u01/app/oracle/oradata/orcl/users01.dbf’ 恢復資料檔案4 SQL> recover datafile 4; 最後將資料檔案聯機 SQL> alter database datafile 4 online; 確認是否恢復成功
SQL> select * from scott.dept;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
SQL> 2、不能離線的資料檔案丟失
刪除系統表空間資料檔案 SQL> ho rm /u01/app/oracle/oradata/orcl/system01.dbf
SQL> select tablespace_name,file_name from dba_data_files; ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01116: error in opening database file 1 ORA-01110: data file 1: ‘/u01/app/oracle/oradata/orcl/system01.dbf’ ORA-27041: unable to open file 系統表空間是不能離線的 SQL> alter database datafile 1 offline; alter database datafile 1 offline * ERROR at line 1: ORA-01541: system tablespace cannot be brought offline; shut down if necessary
關閉資料庫一致性關閉資料庫是無效的 可以 SQL>shutdown abort SQL>startup mount 或者 SQL> startup force mount 將備份的資料檔案拷貝到原資料檔案路徑 SQL> ho cp /u02/horcl/system01.dbf /u01/app/oracle/oradata/orcl/system01.dbf 恢復資料檔案1 SQL> recover datafile 1;
啟動資料庫到open階段 SQL>alter database open; 驗證 SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME ------------------------------ FILE_NAME -------------------------------------------------------------------------------- USERS /u01/app/oracle/oradata/orcl/users01.dbf SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf
TABLESPACE_NAME ------------------------------ FILE_NAME -------------------------------------------------------------------------------- SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf |
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29532781/viewspace-1174683/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle備份恢復之熱備份恢復及異機恢復Oracle
- oracle 冷備及恢復Oracle
- PostgreSQL 的熱備和恢復SQL
- oracle 10g RMAN備份及恢復Oracle 10g
- oracle實驗記錄 （恢復-關於熱備份）Oracle
- oracle冷備恢復Oracle
- oracle 備用恢復Oracle
- 【物理熱備】（下）備份恢復系統表空間 手工備份恢復
- Oracle 備份恢復概念Oracle
- oracle備份恢復PPTOracle
- Oracle 備份和恢復Oracle
- ORACLE備份&恢復案例Oracle
- oracle冷備份、恢復和異機恢復Oracle
- Oracle9i RMAN備份及恢復步驟Oracle
- 【備份恢復】Oracle 資料備份與恢復微實踐Oracle
- ORACLE備份&恢復案例(轉)Oracle
- Oracle 備份 與 恢復 概述Oracle
- Oracle 備份恢復之 FlashbackOracle
- Oracle RAC備份與恢復Oracle
- ORACLE備份&恢復案例(3)Oracle
- ORACLE備份&恢復案例(5)Oracle
- ORACLE備份&恢復案例(4)Oracle
- ORACLE備份&恢復案例(7)Oracle
- ORACLE備份&恢復案例(6)Oracle
- ORACLE備份&恢復案例(8)Oracle
- ORACLE備份&恢復案例(1)Oracle
- ORACLE備份&恢復案例(2)Oracle
- Oracle備份與恢復 (zt)Oracle
- Oracle備份與恢復案例Oracle
- Oracle備份與恢復(轉)Oracle
- Oracle資料庫的熱備份與完整恢復測試 （2）Oracle資料庫
- Oracle資料庫的熱備份與完整恢復測試 （1）Oracle資料庫
- 基於tsm的oracle資料庫備份及恢復Oracle資料庫
- Oracle中使用RMAN備份及一些恢復方法Oracle
- Oracle9i RMAN備份及恢復步驟(zt)Oracle
- Oracle資料庫的備份及恢復策略研究(轉)Oracle資料庫
- docker 中 MySQL 備份及恢復DockerMySql
- 【mysqldump】mysqldump及備份恢復示例MySql