Oracle備份與恢復系列 （二）停機一致性備份
關閉資料庫的備份與恢復
關閉資料庫備份原理很簡單，就像我們備份照片、備份word文件一樣。複製一份原檔案並放置在其它位置備用，如果磁碟空間緊張通常會複製到行動硬碟或網盤上。當原檔案損壞時，將備份再複製回來就可以了。當然資料庫有自己的備份專屬地，磁帶庫，而不會備份到行動硬碟上。
本次試驗分為以下步驟
1，建立備份指令碼
2，執行備份指令碼
3，刪除資料檔案
4，執行還原指令碼
5，開啟資料庫
6，確認資料庫還原
在其它視窗關注警告日誌的動態
tail -f /u01/app/oracle/diag/rdbms/practice/PRACTICE/trace/alert_PRACTICE.log
step1 建立備份指令碼
su - root
mkdir /backup/scripts
mkidr /backup/closed_backup
chown oracle:oinstall /backup
vi /backup/scripts/closed_backup.sql
set feedback off heading off verify off
set pagesize 0 linesize 200
define dir = '/backup/closed_backup'
define fil = '/backup/scripts/closed_backup_commands.sql'
prompt *** Spooling to &fil
spool &fil
select 'host cp '|| name ||' &dir' from v$datafile order by 1;
select 'host cp '|| member ||' &dir' from v$logfile order by 1;
select 'host cp '|| name ||' &dir' from v$controlfile order by 1;
select 'host cp '|| name ||' &dir' from v$tempfile order by 1;
spool off;
prompt *** Spooling end ***
shutdown immediate;
@&fil
startup;
step2 執行備份指令碼
注意備份之前資料庫關閉時間
insert into snow.date_log values(sysdate,'lunchtime');
commit;
select * from snow.date_log order by create_time;
CREATE_TIME NAME
------------------- ------------------------------
2014/07/31 11:23:42 --
2014/07/31 11:24:42 --
2014/07/31 11:25:16 lunchtime <==插入資料的時間點
@/backup/scripts/closed_backup_commands.sql
資料庫將要一致性關閉，複製所有檔案到備份目錄，成功後重新啟動資料庫
檢視備份是否存在
ls -al /backup/closed_backup
total 2042840
drwxr-xr-x 2 oracle oinstall 4096 Jul 31 10:49 .
drwxr-xr-x 4 oracle oinstall 4096 Jul 31 09:55 ..
-rw-r----- 1 oracle oinstall 9748480 Jul 31 11:25 control01.ctl <==冷備的時間點
-rw-r----- 1 oracle oinstall 9748480 Jul 31 11:25 control02.ctl
-rw-r----- 1 oracle oinstall 362422272 Jul 31 11:25 example01.dbf
-rw-r----- 1 oracle oinstall 20979712 Jul 31 11:25 indx.dbf
-rw-r----- 1 oracle oinstall 52429312 Jul 31 11:25 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jul 31 11:25 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jul 31 11:25 redo03.log
-rw-r----- 1 oracle oinstall 597696512 Jul 31 11:25 sysaux01.dbf
-rw-r----- 1 oracle oinstall 754982912 Jul 31 11:25 system01.dbf
-rw-r----- 1 oracle oinstall 30416896 Jul 31 11:25 temp01.dbf
-rw-r----- 1 oracle oinstall 20979712 Jul 31 11:25 tools01.dbf
-rw-r----- 1 oracle oinstall 115351552 Jul 31 11:25 undotbs01.dbf
-rw-r----- 1 oracle oinstall 10493952 Jul 31 11:25 users01.dbf
-rw-r----- 1 oracle oinstall 10493952 Jul 31 11:25 users02.dbf
step3 刪除全部資料檔案
shutdown immediate;
rm -rf /oradata/PRACTICE/*
step4 執行還原指令碼
這一步同rman備份中的restore作用相同
cp /backup/closed_backup/* /oradata/PRACTICE/
mv /oradata/PRACTICE/control02.ctl /u01/app/oracle/fast_recovery_area/PRACTICE/
step5 開啟資料庫
startup mount;
檢查最後檢查點時間與備份前的關閉是否一致
set linesize 200;
select file#,status,checkpoint_change#,checkpoint_time,last_change#,last_time from v$datafile;
FILE# STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME LAST_CHANGE# LAST_TIME
---------- --------------------- ------------------ ------------------- ------------ -------------------
1 SYSTEM 1169838 2014/07/31 11:06:35
2 ONLINE 1169838 2014/07/31 11:06:35
3 ONLINE 1169838 2014/07/31 11:06:35
4 ONLINE 1169838 2014/07/31 11:06:35
5 ONLINE 1169838 2014/07/31 11:06:35
6 ONLINE 1169838 2014/07/31 11:06:35
7 ONLINE 1169838 2014/07/31 11:06:35
8 ONLINE 1169838 2014/07/31 11:06:35
select group#,sequence#,status,first_change#,first_time from v$log order by first_change#;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ------------------------------------------------ ------------- -------------------
1 7 INACTIVE 1116891 2014/07/30 19:42:28
2 8 INACTIVE 1138909 2014/07/30 22:01:09
3 9 CURRENT 1169837 2014/07/31 11:06:35
step6 確認資料庫還原
alter database open;
select * from snow.date_log order by create_time;
CREATE_TIME NAME
------------------- ------------------------------
2014/07/31 11:23:42 --
2014/07/31 11:24:42 --
2014/07/31 11:25:16 lunchtime <==還原成功
2014/07/31 11:25:42 --
2014/07/31 11:32:27 --
歸檔模式下的完全資料庫恢復
本次試驗分為以下步驟
1 配置資料庫歸檔
2 執行備份指令碼
3 切換重做日誌
4 刪除一個資料檔案
5 還原丟失的資料檔案
6 恢復還原的資料問價
7 確認資料庫恢復
step1 配置資料庫歸檔
設定歸檔日誌存放路徑
alter system set log_archive_dest_1="location=/archive";
設定歸檔格式
alter system set log_archive_format = '%t_%s_%r.arc' scope=spfile;
開啟歸檔模式
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
驗證設定結果，其中status=valid為成功
col DESTINATION for a20
select DEST_ID,STATUS,DESTINATION from v$archive_dest where dest_id=1;
DEST_ID STATUS DESTINATION ---------- --------- -------------------------------------------------- 1 VALID /archive
或者透過archive log list命令檢視
archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archive
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
step2 執行備份指令碼
跟之前備份區的區別是這次在archivelog模式下執行
shutdown immediate;
startup mount;
@/backup/scripts/closed_backup.sql
ls -l /backup/closed_backup/
total 2042836
-rw-r--r-- 1 oracle oinstall 2814 Jul 31 19:23 closed_backup_commands.sql <==冷備時間19:23
-rw-r----- 1 oracle oinstall 9748480 Jul 31 19:23 control01.ctl
-rw-r----- 1 oracle oinstall 9748480 Jul 31 19:23 control02.ctl
-rw-r----- 1 oracle oinstall 362422272 Jul 31 19:23 example01.dbf
-rw-r----- 1 oracle oinstall 20979712 Jul 31 19:23 indx.dbf
-rw-r----- 1 oracle oinstall 52429312 Jul 31 19:23 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jul 31 19:23 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jul 31 19:23 redo03.log
-rw-r----- 1 oracle oinstall 597696512 Jul 31 19:23 sysaux01.dbf
-rw-r----- 1 oracle oinstall 754982912 Jul 31 19:23 system01.dbf
-rw-r----- 1 oracle oinstall 30416896 Jul 31 19:23 temp01.dbf
-rw-r----- 1 oracle oinstall 20979712 Jul 31 19:23 tools01.dbf
-rw-r----- 1 oracle oinstall 115351552 Jul 31 19:23 undotbs01.dbf
-rw-r----- 1 oracle oinstall 10493952 Jul 31 19:23 users01.dbf
-rw-r----- 1 oracle oinstall 10493952 Jul 31 19:23 users02.dbf
step3 切換重做日誌
備份結束後，檢視當前日誌狀態
select group#,sequence#,archived,status,first_change#,first_time,next_change#,next_time from v$log;
GROUP# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- ---------- ------------- ------------------- ------------ -------------------
1 22 NO CURRENT 1293718 2014/07/31 19:46:56 2.8147E+14 <==當前日誌為第22號
2 20 YES INACTIVE 1271522 2014/07/31 19:23:43 1272202 2014/07/31 19:28:04
3 21 YES INACTIVE 1272202 2014/07/31 19:28:04 1293718 2014/07/31 19:46:56
select sequence#,first_change#,first_time,next_change#,resetlogs_change# from v$log_history;
SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE#
---------- ------------- ------------------- ------------ -----------------
17 1218933 2014/07/31 13:35:53 1224098 995548
18 1224098 2014/07/31 14:56:11 1250987 995548
19 1250987 2014/07/31 15:23:34 1271522 995548
20 1271522 2014/07/31 19:23:43 1272202 995548
21 1272202 2014/07/31 19:28:04 1293718 995548
手工插入資料Alex1
insert into snow.date_log values(sysdate,'snow1');
commit;
alter system switch logfile;
檢視切換日誌後的狀態
alert日誌
Thu Jul 31 19:50:08 2014
Thread 1 advanced to log sequence 23 (LGWR switch)
Current log# 2 seq# 23 mem# 0: /oradata/PRACTICE/redo02.log
Thu Jul 31 19:50:08 2014
Archived Log entry 18 added for thread 1 sequence 22 ID 0xb57f8923 dest 1: <==第22號日誌已經被歸檔，剛才的current redolog 被歸檔了
select group#,sequence#,archived,status,first_change#,first_time,next_change#,next_time from v$log;
GROUP# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- ---------- ------------- ------------------- ------------ -------------------
1 22 YES ACTIVE 1293718 2014/07/31 19:46:56 1294188 2014/07/31 19:50:08
2 23 NO CURRENT 1294188 2014/07/31 19:50:08 2.8147E+14 <==最新的當前日誌為23號
3 21 YES INACTIVE 1272202 2014/07/31 19:28:04 1293718 2014/07/31 19:46:56
select name,sequence#,first_change#,next_change# from v$archived_log
NAME SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
------------------------------ ---------- ------------- ------------
/archive/1_17_854302181.arc 17 1218933 1224098
/archive/1_18_854302181.arc 18 1224098 1250987
/archive/1_19_854302181.arc 19 1250987 1271522
/archive/1_20_854302181.arc 20 1271522 1272202
/archive/1_21_854302181.arc 21 1272202 1293718
/archive/1_22_854302181.arc 22 1293718 1294188 <==snow1 應該就在此處
重複以上插入資料的步驟
insert into snow.date_log values(sysdate,'snow2');
commit;
alter system switch log file;
insert into snow.date_log values(sysdate,'snow3');
commit;
alter system switch log file;
insert into snow.date_log values(sysdate,'snow4');
commit;
alter system switch log file;
檢視歸檔日誌
select name,sequence#,first_change#,next_change# from v$archived_log
NAME SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
------------------------------ ---------- ------------- ------------
/archive/1_22_854302181.arc 22 1293718 1294188
/archive/1_23_854302181.arc 23 1294188 1294332
/archive/1_24_854302181.arc 24 1294332 1294354
/archive/1_25_854302181.arc 25 1294354 1294362
step4 刪除一個資料檔案users01.dbf
rm /oradata/PRACTICE/users01.dbf
關閉資料庫提示/oradata/PRACTICE/users01.dbf不存在，無法寫入SCN
shutdown immediate;
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/oradata/PRACTICE/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
shutdown abort;
step5 還原丟失的資料檔案
startup mount;
select file#,error,change# from v$recover_file;
FILE# ERROR CHANGE#
---------- -------------------- ----------
4 FILE NOT FOUND <==change#為空
從備份中複製users01.dbf檔案，此時recover_file動態檢視產生了變化
cp /backup/closed_backup/users01.dbf /oradata/PRACTICE/
select file#,error,change# from v$recover_file;
FILE# ERROR CHANGE#
---------- ------------------ ----------
4 1293716 <==user01.dbf為4號資料檔案，SCN為1293716
注意checkpoint_change#的值大於change#,為了一致性開啟資料庫change#需要從1195007恢復到1197189
select FILE#,CHECKPOINT_CHANGE#, CHECKPOINT_TIME from v$datafile;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- ------------------ -------------------
1 1294362 2014/07/31 19:55:21 <==其它檔案的SCN 1294362 比要剛剛複製的檔案SCN 1293716大
2 1294362 2014/07/31 19:55:21
3 1294362 2014/07/31 19:55:21
4 1294362 2014/07/31 19:55:21
5 1294362 2014/07/31 19:55:21
6 1294362 2014/07/31 19:55:21
7 1294362 2014/07/31 19:55:21
8 1294362 2014/07/31 19:55:21
需要將file4 的scn 恢復到與其它資料檔案一致才可以開啟資料庫
select sequence#,first_change#,first_time,next_change#,resetlogs_change# from v$log_history order by sequence# desc;
SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE#
---------- ------------- ------------------- ------------ -----------------
25 1294354 2014/07/31 19:55:05 1294362 995548
24 1294332 2014/07/31 19:54:42 1294354 995548
23 1294188 2014/07/31 19:50:08 1294332 995548
22 1293718 2014/07/31 19:46:56 1294188 995548
21 1272202 2014/07/31 19:28:04 1293718 995548
step6 恢復還原的資料檔案
recover database;
ORA-00279: change 1293716 generated at 07/31/2014 19:40:48 needed for thread 1
ORA-00289: suggestion : /archive/1_21_854302181.arc
ORA-00280: change 1293716 for thread 1 is in sequence #21
Specify log: {=suggested | filename | AUTO | CANCEL} <==輸入回車繼續
ORA-00279: change 1293718 generated at 07/31/2014 19:46:56 needed for thread 1
ORA-00289: suggestion : /archive/1_22_854302181.arc
ORA-00280: change 1293718 for thread 1 is in sequence #22
Specify log: {=suggested | filename | AUTO | CANCEL} <==輸入回車繼續
ORA-00279: change 1294188 generated at 07/31/2014 19:50:08 needed for thread 1
ORA-00289: suggestion : /archive/1_23_854302181.arc
ORA-00280: change 1294188 for thread 1 is in sequence #23
Specify log: {=suggested | filename | AUTO | CANCEL} <==輸入回車繼續
Log applied.
Media recovery complete.
檢視alert日誌
select * from v$recover_file;
no rows selected <==恢復成功，不再顯示內容
在看看alert日誌中提供的資料
ORA-279 signalled during: ALTER DATABASE RECOVER database ...
Thu Jul 31 20:02:43 2014
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /archive/1_21_854302181.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /archive/1_22_854302181.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /archive/1_23_854302181.arc
Thu Jul 31 20:02:46 2014
Recovery of Online Redo Log: Thread 1 Group 3 Seq 24 Reading mem 0
Mem# 0: /oradata/PRACTICE/redo03.log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0
Mem# 0: /oradata/PRACTICE/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 26 Reading mem 0
Mem# 0: /oradata/PRACTICE/redo02.log
Media Recovery Complete (PRACTICE)
Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT
step7 確認資料庫恢復
alter database open;
select * from snow.date_log order by create_time;
CREATE_TIME NAME
------------------- ------------------------------
2014/07/31 19:35:48 --
2014/07/31 19:36:48 --
2014/07/31 19:37:48 --
2014/07/31 19:38:48 --
2014/07/31 19:39:48 --
2014/07/31 19:40:48 --
2014/07/31 19:47:01 --
2014/07/31 19:48:02 --
2014/07/31 19:49:02 --
2014/07/31 19:49:54 snow1
2014/07/31 19:50:02 --
2014/07/31 19:51:02 --
2014/07/31 19:52:02 --
2014/07/31 19:53:02 --
2014/07/31 19:54:02 --
2014/07/31 19:54:28 snow2
2014/07/31 19:54:54 snow3
2014/07/31 19:55:02 --
2014/07/31 19:55:11 snow4
2014/07/31 19:56:02 --
2014/07/31 19:57:02 --
2014/07/31 19:58:02 —
關閉資料庫備份原理很簡單，就像我們備份照片、備份word文件一樣。複製一份原檔案並放置在其它位置備用，如果磁碟空間緊張通常會複製到行動硬碟或網盤上。當原檔案損壞時，將備份再複製回來就可以了。當然資料庫有自己的備份專屬地，磁帶庫，而不會備份到行動硬碟上。
本次試驗分為以下步驟
1，建立備份指令碼
2，執行備份指令碼
3，刪除資料檔案
4，執行還原指令碼
5，開啟資料庫
6，確認資料庫還原
在其它視窗關注警告日誌的動態
tail -f /u01/app/oracle/diag/rdbms/practice/PRACTICE/trace/alert_PRACTICE.log
step1 建立備份指令碼
su - root
mkdir /backup/scripts
mkidr /backup/closed_backup
chown oracle:oinstall /backup
vi /backup/scripts/closed_backup.sql
set feedback off heading off verify off
set pagesize 0 linesize 200
define dir = '/backup/closed_backup'
define fil = '/backup/scripts/closed_backup_commands.sql'
prompt *** Spooling to &fil
spool &fil
select 'host cp '|| name ||' &dir' from v$datafile order by 1;
select 'host cp '|| member ||' &dir' from v$logfile order by 1;
select 'host cp '|| name ||' &dir' from v$controlfile order by 1;
select 'host cp '|| name ||' &dir' from v$tempfile order by 1;
spool off;
prompt *** Spooling end ***
shutdown immediate;
@&fil
startup;
step2 執行備份指令碼
注意備份之前資料庫關閉時間
insert into snow.date_log values(sysdate,'lunchtime');
commit;
select * from snow.date_log order by create_time;
CREATE_TIME NAME
------------------- ------------------------------
2014/07/31 11:23:42 --
2014/07/31 11:24:42 --
2014/07/31 11:25:16 lunchtime <==插入資料的時間點
@/backup/scripts/closed_backup_commands.sql
資料庫將要一致性關閉，複製所有檔案到備份目錄，成功後重新啟動資料庫
檢視備份是否存在
ls -al /backup/closed_backup
total 2042840
drwxr-xr-x 2 oracle oinstall 4096 Jul 31 10:49 .
drwxr-xr-x 4 oracle oinstall 4096 Jul 31 09:55 ..
-rw-r----- 1 oracle oinstall 9748480 Jul 31 11:25 control01.ctl <==冷備的時間點
-rw-r----- 1 oracle oinstall 9748480 Jul 31 11:25 control02.ctl
-rw-r----- 1 oracle oinstall 362422272 Jul 31 11:25 example01.dbf
-rw-r----- 1 oracle oinstall 20979712 Jul 31 11:25 indx.dbf
-rw-r----- 1 oracle oinstall 52429312 Jul 31 11:25 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jul 31 11:25 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jul 31 11:25 redo03.log
-rw-r----- 1 oracle oinstall 597696512 Jul 31 11:25 sysaux01.dbf
-rw-r----- 1 oracle oinstall 754982912 Jul 31 11:25 system01.dbf
-rw-r----- 1 oracle oinstall 30416896 Jul 31 11:25 temp01.dbf
-rw-r----- 1 oracle oinstall 20979712 Jul 31 11:25 tools01.dbf
-rw-r----- 1 oracle oinstall 115351552 Jul 31 11:25 undotbs01.dbf
-rw-r----- 1 oracle oinstall 10493952 Jul 31 11:25 users01.dbf
-rw-r----- 1 oracle oinstall 10493952 Jul 31 11:25 users02.dbf
step3 刪除全部資料檔案
shutdown immediate;
rm -rf /oradata/PRACTICE/*
step4 執行還原指令碼
這一步同rman備份中的restore作用相同
cp /backup/closed_backup/* /oradata/PRACTICE/
mv /oradata/PRACTICE/control02.ctl /u01/app/oracle/fast_recovery_area/PRACTICE/
step5 開啟資料庫
startup mount;
檢查最後檢查點時間與備份前的關閉是否一致
set linesize 200;
select file#,status,checkpoint_change#,checkpoint_time,last_change#,last_time from v$datafile;
FILE# STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME LAST_CHANGE# LAST_TIME
---------- --------------------- ------------------ ------------------- ------------ -------------------
1 SYSTEM 1169838 2014/07/31 11:06:35
2 ONLINE 1169838 2014/07/31 11:06:35
3 ONLINE 1169838 2014/07/31 11:06:35
4 ONLINE 1169838 2014/07/31 11:06:35
5 ONLINE 1169838 2014/07/31 11:06:35
6 ONLINE 1169838 2014/07/31 11:06:35
7 ONLINE 1169838 2014/07/31 11:06:35
8 ONLINE 1169838 2014/07/31 11:06:35
select group#,sequence#,status,first_change#,first_time from v$log order by first_change#;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ------------------------------------------------ ------------- -------------------
1 7 INACTIVE 1116891 2014/07/30 19:42:28
2 8 INACTIVE 1138909 2014/07/30 22:01:09
3 9 CURRENT 1169837 2014/07/31 11:06:35
step6 確認資料庫還原
alter database open;
select * from snow.date_log order by create_time;
CREATE_TIME NAME
------------------- ------------------------------
2014/07/31 11:23:42 --
2014/07/31 11:24:42 --
2014/07/31 11:25:16 lunchtime <==還原成功
2014/07/31 11:25:42 --
2014/07/31 11:32:27 --
歸檔模式下的完全資料庫恢復
本次試驗分為以下步驟
1 配置資料庫歸檔
2 執行備份指令碼
3 切換重做日誌
4 刪除一個資料檔案
5 還原丟失的資料檔案
6 恢復還原的資料問價
7 確認資料庫恢復
step1 配置資料庫歸檔
設定歸檔日誌存放路徑
alter system set log_archive_dest_1="location=/archive";
設定歸檔格式
alter system set log_archive_format = '%t_%s_%r.arc' scope=spfile;
開啟歸檔模式
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
驗證設定結果，其中status=valid為成功
col DESTINATION for a20
select DEST_ID,STATUS,DESTINATION from v$archive_dest where dest_id=1;
DEST_ID STATUS DESTINATION ---------- --------- -------------------------------------------------- 1 VALID /archive
或者透過archive log list命令檢視
archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archive
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
step2 執行備份指令碼
跟之前備份區的區別是這次在archivelog模式下執行
shutdown immediate;
startup mount;
@/backup/scripts/closed_backup.sql
ls -l /backup/closed_backup/
total 2042836
-rw-r--r-- 1 oracle oinstall 2814 Jul 31 19:23 closed_backup_commands.sql <==冷備時間19:23
-rw-r----- 1 oracle oinstall 9748480 Jul 31 19:23 control01.ctl
-rw-r----- 1 oracle oinstall 9748480 Jul 31 19:23 control02.ctl
-rw-r----- 1 oracle oinstall 362422272 Jul 31 19:23 example01.dbf
-rw-r----- 1 oracle oinstall 20979712 Jul 31 19:23 indx.dbf
-rw-r----- 1 oracle oinstall 52429312 Jul 31 19:23 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jul 31 19:23 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jul 31 19:23 redo03.log
-rw-r----- 1 oracle oinstall 597696512 Jul 31 19:23 sysaux01.dbf
-rw-r----- 1 oracle oinstall 754982912 Jul 31 19:23 system01.dbf
-rw-r----- 1 oracle oinstall 30416896 Jul 31 19:23 temp01.dbf
-rw-r----- 1 oracle oinstall 20979712 Jul 31 19:23 tools01.dbf
-rw-r----- 1 oracle oinstall 115351552 Jul 31 19:23 undotbs01.dbf
-rw-r----- 1 oracle oinstall 10493952 Jul 31 19:23 users01.dbf
-rw-r----- 1 oracle oinstall 10493952 Jul 31 19:23 users02.dbf
step3 切換重做日誌
備份結束後，檢視當前日誌狀態
select group#,sequence#,archived,status,first_change#,first_time,next_change#,next_time from v$log;
GROUP# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- ---------- ------------- ------------------- ------------ -------------------
1 22 NO CURRENT 1293718 2014/07/31 19:46:56 2.8147E+14 <==當前日誌為第22號
2 20 YES INACTIVE 1271522 2014/07/31 19:23:43 1272202 2014/07/31 19:28:04
3 21 YES INACTIVE 1272202 2014/07/31 19:28:04 1293718 2014/07/31 19:46:56
select sequence#,first_change#,first_time,next_change#,resetlogs_change# from v$log_history;
SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE#
---------- ------------- ------------------- ------------ -----------------
17 1218933 2014/07/31 13:35:53 1224098 995548
18 1224098 2014/07/31 14:56:11 1250987 995548
19 1250987 2014/07/31 15:23:34 1271522 995548
20 1271522 2014/07/31 19:23:43 1272202 995548
21 1272202 2014/07/31 19:28:04 1293718 995548
手工插入資料Alex1
insert into snow.date_log values(sysdate,'snow1');
commit;
alter system switch logfile;
檢視切換日誌後的狀態
alert日誌
Thu Jul 31 19:50:08 2014
Thread 1 advanced to log sequence 23 (LGWR switch)
Current log# 2 seq# 23 mem# 0: /oradata/PRACTICE/redo02.log
Thu Jul 31 19:50:08 2014
Archived Log entry 18 added for thread 1 sequence 22 ID 0xb57f8923 dest 1: <==第22號日誌已經被歸檔，剛才的current redolog 被歸檔了
select group#,sequence#,archived,status,first_change#,first_time,next_change#,next_time from v$log;
GROUP# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- ---------- ------------- ------------------- ------------ -------------------
1 22 YES ACTIVE 1293718 2014/07/31 19:46:56 1294188 2014/07/31 19:50:08
2 23 NO CURRENT 1294188 2014/07/31 19:50:08 2.8147E+14 <==最新的當前日誌為23號
3 21 YES INACTIVE 1272202 2014/07/31 19:28:04 1293718 2014/07/31 19:46:56
select name,sequence#,first_change#,next_change# from v$archived_log
NAME SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
------------------------------ ---------- ------------- ------------
/archive/1_17_854302181.arc 17 1218933 1224098
/archive/1_18_854302181.arc 18 1224098 1250987
/archive/1_19_854302181.arc 19 1250987 1271522
/archive/1_20_854302181.arc 20 1271522 1272202
/archive/1_21_854302181.arc 21 1272202 1293718
/archive/1_22_854302181.arc 22 1293718 1294188 <==snow1 應該就在此處
重複以上插入資料的步驟
insert into snow.date_log values(sysdate,'snow2');
commit;
alter system switch log file;
insert into snow.date_log values(sysdate,'snow3');
commit;
alter system switch log file;
insert into snow.date_log values(sysdate,'snow4');
commit;
alter system switch log file;
檢視歸檔日誌
select name,sequence#,first_change#,next_change# from v$archived_log
NAME SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
------------------------------ ---------- ------------- ------------
/archive/1_22_854302181.arc 22 1293718 1294188
/archive/1_23_854302181.arc 23 1294188 1294332
/archive/1_24_854302181.arc 24 1294332 1294354
/archive/1_25_854302181.arc 25 1294354 1294362
step4 刪除一個資料檔案users01.dbf
rm /oradata/PRACTICE/users01.dbf
關閉資料庫提示/oradata/PRACTICE/users01.dbf不存在，無法寫入SCN
shutdown immediate;
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/oradata/PRACTICE/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
shutdown abort;
step5 還原丟失的資料檔案
startup mount;
select file#,error,change# from v$recover_file;
FILE# ERROR CHANGE#
---------- -------------------- ----------
4 FILE NOT FOUND <==change#為空
從備份中複製users01.dbf檔案，此時recover_file動態檢視產生了變化
cp /backup/closed_backup/users01.dbf /oradata/PRACTICE/
select file#,error,change# from v$recover_file;
FILE# ERROR CHANGE#
---------- ------------------ ----------
4 1293716 <==user01.dbf為4號資料檔案，SCN為1293716
注意checkpoint_change#的值大於change#,為了一致性開啟資料庫change#需要從1195007恢復到1197189
select FILE#,CHECKPOINT_CHANGE#, CHECKPOINT_TIME from v$datafile;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- ------------------ -------------------
1 1294362 2014/07/31 19:55:21 <==其它檔案的SCN 1294362 比要剛剛複製的檔案SCN 1293716大
2 1294362 2014/07/31 19:55:21
3 1294362 2014/07/31 19:55:21
4 1294362 2014/07/31 19:55:21
5 1294362 2014/07/31 19:55:21
6 1294362 2014/07/31 19:55:21
7 1294362 2014/07/31 19:55:21
8 1294362 2014/07/31 19:55:21
需要將file4 的scn 恢復到與其它資料檔案一致才可以開啟資料庫
select sequence#,first_change#,first_time,next_change#,resetlogs_change# from v$log_history order by sequence# desc;
SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE#
---------- ------------- ------------------- ------------ -----------------
25 1294354 2014/07/31 19:55:05 1294362 995548
24 1294332 2014/07/31 19:54:42 1294354 995548
23 1294188 2014/07/31 19:50:08 1294332 995548
22 1293718 2014/07/31 19:46:56 1294188 995548
21 1272202 2014/07/31 19:28:04 1293718 995548
step6 恢復還原的資料檔案
recover database;
ORA-00279: change 1293716 generated at 07/31/2014 19:40:48 needed for thread 1
ORA-00289: suggestion : /archive/1_21_854302181.arc
ORA-00280: change 1293716 for thread 1 is in sequence #21
Specify log: {
ORA-00279: change 1293718 generated at 07/31/2014 19:46:56 needed for thread 1
ORA-00289: suggestion : /archive/1_22_854302181.arc
ORA-00280: change 1293718 for thread 1 is in sequence #22
Specify log: {
ORA-00279: change 1294188 generated at 07/31/2014 19:50:08 needed for thread 1
ORA-00289: suggestion : /archive/1_23_854302181.arc
ORA-00280: change 1294188 for thread 1 is in sequence #23
Specify log: {
Log applied.
Media recovery complete.
檢視alert日誌
select * from v$recover_file;
no rows selected <==恢復成功，不再顯示內容
在看看alert日誌中提供的資料
ORA-279 signalled during: ALTER DATABASE RECOVER database ...
Thu Jul 31 20:02:43 2014
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /archive/1_21_854302181.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /archive/1_22_854302181.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /archive/1_23_854302181.arc
Thu Jul 31 20:02:46 2014
Recovery of Online Redo Log: Thread 1 Group 3 Seq 24 Reading mem 0
Mem# 0: /oradata/PRACTICE/redo03.log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0
Mem# 0: /oradata/PRACTICE/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 26 Reading mem 0
Mem# 0: /oradata/PRACTICE/redo02.log
Media Recovery Complete (PRACTICE)
Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT
step7 確認資料庫恢復
alter database open;
select * from snow.date_log order by create_time;
CREATE_TIME NAME
------------------- ------------------------------
2014/07/31 19:35:48 --
2014/07/31 19:36:48 --
2014/07/31 19:37:48 --
2014/07/31 19:38:48 --
2014/07/31 19:39:48 --
2014/07/31 19:40:48 --
2014/07/31 19:47:01 --
2014/07/31 19:48:02 --
2014/07/31 19:49:02 --
2014/07/31 19:49:54 snow1
2014/07/31 19:50:02 --
2014/07/31 19:51:02 --
2014/07/31 19:52:02 --
2014/07/31 19:53:02 --
2014/07/31 19:54:02 --
2014/07/31 19:54:28 snow2
2014/07/31 19:54:54 snow3
2014/07/31 19:55:02 --
2014/07/31 19:55:11 snow4
2014/07/31 19:56:02 --
2014/07/31 19:57:02 --
2014/07/31 19:58:02 —
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29047826/viewspace-1243347/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle備份恢復之熱備份恢復及異機恢復Oracle
- Oracle 聯機備份 離線備份 物理備份 恢復Oracle
- 備份與恢復系列 十一 控制檔案的備份與恢復
- Mysql 備份與恢復 二MySql
- 【備份恢復】Oracle 資料備份與恢復微實踐Oracle
- Oracle 備份 與 恢復 概述Oracle
- Oracle RAC備份與恢復Oracle
- Oracle備份與恢復 (zt)Oracle
- Oracle備份與恢復案例Oracle
- Oracle備份與恢復(轉)Oracle
- 備份與恢復系列 十 引數檔案spfile的備份與恢復
- ORACLE備份&恢復案例二(轉)Oracle
- Oracle資料庫備份與恢復之三：OS備份/使用者管理的備份與恢復Oracle資料庫
- Mysql備份與恢復(1)---物理備份MySql
- RMAN備份與恢復之加密備份加密
- 備份與恢復：polardb資料庫備份與恢復資料庫
- 備份與恢復oracle_homeOracle
- Oracle OCR的備份與恢復Oracle
- Oracle 備份與恢復(一):概念Oracle
- oracle備份與恢復雜記Oracle
- Oracle備份與恢復入門Oracle
- Oracle備份與恢復案例 (zt)Oracle
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- 備份與恢復--利用備份的控制檔案恢復
- oracle冷備份、恢復和異機恢復Oracle
- Oracle SCN機制———在備份與恢復中Oracle
- 資料庫備份與異機恢復——熱備份方式資料庫
- Oracle 備份恢復概念Oracle
- oracle備份恢復PPTOracle
- Oracle 備份和恢復Oracle
- ORACLE備份&恢復案例Oracle
- Oracle備份與恢復系列（三）alter tablspace begin backupOracle
- MySQL入門--備份與恢復（二）MySql
- RMAN 備份與恢復深入解析（二）
- Mysql備份與恢復(2)---邏輯備份MySql
- Postgresql 備份與恢復SQL
- MySQL備份與恢復MySql
- MySQL 備份與恢復MySql