【物理熱備】（下）備份恢復系統表空間 手工備份恢復
三：系統表空間的熱備及模擬故障恢復
恢復資料庫只能選擇關閉恢復，因為系統表空間只能online；
1. （前提）物理熱備必須開啟歸檔，檢視：
SYS@ORA11GR2>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15
SYS@ORA11GR2>
2. 檢視備份檔案絕對路徑（重要）
SYS@ORA11GR2>select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORA11GR2/control01.ctl
/u01/app/oracle/oradata/ORA11GR2/control02.ctl
/u01/app/FRA/control03.ctl
SYS@ORA11GR2>select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORA11GR2/system01.dbf
/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf
/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf
/u01/app/oracle/oradata/ORA11GR2/users01.dbf
/u01/app/oracle/oradata/ORA11GR2/example01.dbf
/u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf
/u01/app/oracle/oradata/ORA11GR2/undotbs2_01.dbf
7 rows selected.
SYS@ORA11GR2>select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORA11GR2/redo03.log
/u01/app/oracle/oradata/ORA11GR2/redo02.log
/u01/app/oracle/oradata/ORA11GR2/redo01.log
/u01/app/oracle/oradata/ORA11GR2/redo01_a.log
/u01/app/oracle/oradata/ORA11GR2/redo02_a.log
/u01/app/oracle/oradata/ORA11GR2/redo03_a.log
/u01/app/FRA/redo01_b.log
/u01/app/FRA/redo02_b.log
/u01/app/FRA/redo03_b.log
9 rows selected.
3. 檢視資料庫的表空間及對應檔案
SYS@ORA11GR2>select tablespace_name,file_name from dba_data_files;
TABLESPACE FILE_NAME
---------- -------------------------------------------------------
USERS /u01/app/oracle/oradata/ORA11GR2/users01.dbf
UNDOTBS1 /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf
SYSAUX /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf
SYSTEM /u01/app/oracle/oradata/ORA11GR2/system01.dbf
EXAMPLE /u01/app/oracle/oradata/ORA11GR2/example01.dbf
TS_ORA11GR2 /u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf
UNDOTBS2 /u01/app/oracle/oradata/ORA11GR2/undotbs2_01.dbf
7 rows selected.
4.備份系統表空間：
SYS@ORA11GR2>alter tablespace SYSTEM begin backup;
Tablespace altered.
SYS@ORA11GR2>
——檢視備份狀態：
SYS@ORA11GR2>select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 ACTIVE 1548675 27-SEP-16
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0
6 NOT ACTIVE 1543727 27-SEP-16
7 NOT ACTIVE 0
7 rows selected.
SYS@ORA11GR2>select FILE_ID,TABLESPACE_NAME,FILE_NAME from dba_data_files order by 1;
FILE_ID TABLESPACE FILE_NAME
---------- ---------- -------------------------------------------------------
1 SYSTEM /u01/app/oracle/oradata/ORA11GR2/system01.dbf
2 SYSAUX /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf
3 UNDOTBS1 /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf
4 USERS /u01/app/oracle/oradata/ORA11GR2/users01.dbf
5 EXAMPLE /u01/app/oracle/oradata/ORA11GR2/example01.dbf
6 TS_ORA11GR2 /u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf
7 UNDOTBS2 /u01/app/oracle/oradata/ORA11GR2/undotbs2_01.dbf
7 rows selected.
5. 回到作業系統層copy 表空間system：
[oracle@wang ORA11GR2]$ pwd
/u01/app/oracle/oradata/ORA11GR2
[oracle@wang ORA11GR2]$ cp system01.dbf /home/oracle/
[oracle@wang ORA11GR2]$cd
[oracle@wang ~]$ pwd
/home/oracle
[oracle@wang ~]$ ls system01.dbf
system01.dbf
[oracle@wang ~]$
6.結束備份：
SYS@ORA11GR2>alter tablespace system end backup;
Tablespace altered.
——檢視備份狀態：
SYS@ORA11GR2>select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 1548675 27-SEP-16
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0
6 NOT ACTIVE 1543727 27-SEP-16
7 NOT ACTIVE 0
7 rows selected.
SYS@ORA11GR2>select FILE_ID,TABLESPACE_NAME,FILE_NAME from dba_data_files order by 1;
FILE_ID TABLESPACE FILE_NAME
---------- ---------- -------------------------------------------------------
1 SYSTEM /u01/app/oracle/oradata/ORA11GR2/system01.dbf
2 SYSAUX /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf
3 UNDOTBS1 /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf
4 USERS /u01/app/oracle/oradata/ORA11GR2/users01.dbf
5 EXAMPLE /u01/app/oracle/oradata/ORA11GR2/example01.dbf
6 TS_ORA11GR2 /u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf
7 UNDOTBS2 /u01/app/oracle/oradata/ORA11GR2/undotbs2_01.dbf
7 rows selected.
SYS@ORA11GR2>
1. 操作刪除system表空間下的資料檔案
[oracle@wang ORA11GR2]$ pwd
/u01/app/oracle/oradata/ORA11GR2
[oracle@wang ORA11GR2]$ ls system01.dbf
system01.dbf
[oracle@wang ORA11GR2]$ rm system01.dbf
[oracle@wang ORA11GR2]$
[oracle@wang ORA11GR2]$ ls system01.dbf
ls: system01.dbf: No such file or directory
[oracle@wang ORA11GR2]$
——關庫：（模擬故障）
SYS@ORA11GR2>shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
2. 啟動資料庫：（顯示故障）
SYS@ORA11GR2>startup
ORACLE instance started.
Total System Global Area 730714112 bytes
Fixed Size 2256832 bytes
Variable Size 457179200 bytes
Database Buffers 268435456 bytes
Redo Buffers 2842624 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORA11GR2/system01.dbf'
9.將表空間置於offline進行恢復
SYS@ORA11GR2>alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary
（system等系統表空間不能置於offline下進行恢復，必須關庫後恢復）
SYS@ORA11GR2>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORA11GR2>
——將備份的system01.dbf複製到/u01/app/oracle/oradata/ORA11GR2/下
[oracle@wang ~]$ pwd
/home/oracle
[oracle@wang ~]$ ls
system01.dbf ts_ora11gr2_01.dbf
[oracle@wang ~]$
[oracle@wang ~]$ cp system01.dbf /u01/app/oracle/oradata/ORA11GR2/
驗證：
[oracle@wang ORA11GR2]$ pwd
/u01/app/oracle/oradata/ORA11GR2
[oracle@wang ORA11GR2]$
[oracle@wang ORA11GR2]$ ls system01.dbf
system01.dbf
[oracle@wang ORA11GR2]$
——再次啟動資料庫
SYS@ORA11GR2>startup
ORACLE instance started.
Total System Global Area 730714112 bytes
Fixed Size 2256832 bytes
Variable Size 457179200 bytes
Database Buffers 268435456 bytes
Redo Buffers 2842624 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORA11GR2/system01.dbf'
——恢復表空間system;（熱備開啟了歸檔，所以可以recover）
SYS@ORA11GR2>recover tablespace system
Media recovery complete.
SYS@ORA11GR2>select status from v$instance;
STATUS
------------
MOUNTED
SYS@ORA11GR2>alter database open;
Database altered.
SYS@ORA11GR2>select status from v$instance;
STATUS
------------
OPEN
完成！！！！！！！！！！！！！！！！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2126509/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- Oracle備份恢復之熱備份恢復及異機恢復Oracle
- Mysql備份與恢復(1)---物理備份MySql
- 非系統表空間損壞，rman備份恢復
- Oracle 聯機備份 離線備份 物理備份 恢復Oracle
- 詳解叢集級備份恢復：物理細粒度備份恢復
- MySQL 非常規恢復與物理備份恢復MySql
- mysql無備份恢復-獨立表空間MySql
- 表空間級資料庫備份恢復資料庫
- 【備份恢復】從備份恢復資料庫資料庫
- 【管理篇備份恢復】備份恢復基礎
- windwos server 路由備份和恢復 路由表備份和恢復Server路由
- 【備份恢復】noarchive模式下使用增量備份恢復資料庫Hive模式資料庫
- DB2 使用表空間備份恢復庫DB2
- 備份與恢復--利用備份的控制檔案恢復
- 備份＆恢復之四：非歸檔模式下的備份與恢復模式
- Mysql備份恢復MySql
- Postgresql 備份恢復SQL
- redis備份恢復Redis
- mysql 備份恢復MySql
- OceanBase物理備份恢復實踐
- mysql 無備份恢復drop資料-共享表空間MySql
- 歸檔模式下，offline表空間備份與恢復模式
- 備份與恢復：polardb資料庫備份與恢復資料庫
- 【備份恢復】Oracle 資料備份與恢復微實踐Oracle
- RMAN備份恢復典型案例——RMAN備份&系統變慢
- Linux 系統的備份恢復Linux
- HP系統備份及恢復方案
- HP系統的備份與恢復
- RMAN備份與恢復之加密備份加密
- 資料庫備份與異機恢復——熱備份方式資料庫
- 【恢復】基於使用者管理的熱備份對非系統表空間的損壞進行恢復
- 備份與恢復系列 十一 控制檔案的備份與恢復
- Oracle12c多租戶資料庫備份與恢復 - 備份表空間Oracle資料庫
- 物理冷備份與恢復的操作命令
- RAC 恢復（備份後建立的表空間(leviton)恢復後會自動重建）
- 【管理篇備份恢復】rman恢復測試（一） 表空間資料檔案
- Linux下mysql備份 恢復LinuxMySql