【物理熱備】（下）備份恢復系統表空間 手工備份恢復
三：系統表空間的熱備及模擬故障恢復
恢復資料庫只能選擇關閉恢復，因為系統表空間只能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
- Mysql備份與恢復(1)---物理備份MySql
- 【PG備份恢復】pg_basebackup 多表空間備份恢復測試
- SYSTEM 表空間管理及備份恢復
- 詳解叢集級備份恢復：物理細粒度備份恢復
- MySQL 非常規恢復與物理備份恢復MySql
- RAC備份恢復之Voting備份與恢復
- OceanBase物理備份恢復實踐
- Linux 系統的備份恢復Linux
- 備份與恢復：polardb資料庫備份與恢復資料庫
- mydumper備份恢復
- Mysql備份恢復MySql
- 備份和恢復
- RMAN備份恢復典型案例——RMAN備份&系統變慢
- Linux 系統的備份與恢復Linux
- innobackupex 部分表備份和恢復
- MySQL備份與恢復——基於MyDumper/MyLoader 邏輯備份恢復MySql
- 物理冷備份與恢復的操作命令
- rman 增量備份恢復
- Jenkins備份與恢復Jenkins
- Postgresql 備份與恢復SQL
- MySQL 備份與恢復MySql
- KunlunDB備份和恢復
- RMAN備份恢復技巧
- redis 備份和恢復Redis
- Grafana 備份恢復教程Grafana
- Mysql備份與恢復(2)---邏輯備份MySql
- MySQL備份與恢復——基於OUTFILE /LOAD DATA 邏輯備份恢復MySql
- XFS檔案系統的備份、恢復、修復
- 達夢DM備份恢復(物理和邏輯)
- GitLab的自動備份、清理備份與恢復Gitlab
- SqlServer備份和恢復(二)SQLServer
- Oracle 備份 與 恢復 概述Oracle
- Oracle 備份恢復之 FlashbackOracle
- SqlServer 備份和恢復(一)SQLServer
- 【MySQL】MySQL備份和恢復MySql
- DB的備份與恢復
- ORACLE備份&恢復案例(轉)Oracle
- GitLab的備份與恢復Gitlab