说明:
操作系统:oracle_linux_5.6_32
数据库:oracle_11.2.0.3 单实例
问题:
我一直有个问题,用delete in put这个命令的时候,备份的时候顺便备份归档了然后删掉了
原来目录下的归档,但是如果下次我们恢复的时候,需要归档怎么办,原来的位置下没有
归档了,oracle会自动应用刚刚备份的归档吗。
实验步骤如下:
(1)
查看表zsx中有多少条数据
SQL> select * from zsx;
ID
----------
1
2
3
4
5
6
7
8
9
10
(2)
做一个全备包括归档:
RMAN> backup database format '/backup/2013_11_14_1055_%U.bak'
plus archivelog delete all input;
(3)
插入10条数据
begin
for i in 11..20
loop
insert into zsx values (i);
commit;
end loop;
end;
(4)
SQL> alter system checkpoint;
SQL> alter system switch logfile;
(5)
继续插入数据
begin
for i in 21..10000
loop
insert into zsx values (i);
commit;
end loop;
end;
(6)
会产生更多的归档,我们将归档备份走:
RMAN> backup archivelog all format '/backup/2013_11_14_1139_arc_%U.bak' delete input;
(7)
现在想做完全恢复,但是/arch下的归档都没有了,而原先备份的时候只有10条数据
[oracle@RAC1 arch]$ ll
total 0
(8)
我们试着做完全恢复,看看会不会oracle自动用备份的归档。
[oracle@RAC1 rac1]$ rm -rf *.dbf
(9)
shutdown abort;
(10)
startup mount;
(11)
RMAN> restore database;
(12)
SQL> recover database;
ORA-00279: change 577511 generated at 11/26/2013 06:25:59 needed for thread 1
ORA-00289: suggestion : /arch/1_4_832476410.arc
ORA-00280: change 577511 for thread 1 is in sequence #4
Specify log: {=suggested | filename | AUTO | CANCEL}
注意:
这时候默认的归档路径下没有归档了,但是指定需要备份的归档而且路径是在/arch,我们拭目以待,
敲个auto。
auto
ORA-00308: cannot open archived log '/arch/1_4_832476410.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
注意:
发现不行。
(13)
根据上面发现,我们需要4号归档,因为我们有备份,所以不怕。
(14)
恢复归档
既然需要4号,而且4号以后的都没有了,那么我们就从4号归档开始恢复:
RMAN> restore archivelog from logseq 4;
(15)
查看归档路径下的归档,从4号到我们备份时候的归档都有了
[oracle@RAC1 arch]$ ll
total 5788
-rw-r----- 1 oracle oinstall 1024 Nov 26 07:34 1_10_832476410.arc
-rw-r----- 1 oracle oinstall 50176 Nov 26 07:34 1_11_832476410.arc
-rw-r----- 1 oracle oinstall 4608 Nov 26 07:34 1_12_832476410.arc
-rw-r----- 1 oracle oinstall 2048 Nov 26 07:34 1_13_832476410.arc
-rw-r----- 1 oracle oinstall 18944 Nov 26 07:34 1_4_832476410.arc
-rw-r----- 1 oracle oinstall 260096 Nov 26 07:34 1_5_832476410.arc
-rw-r----- 1 oracle oinstall 22528 Nov 26 07:34 1_6_832476410.arc
-rw-r----- 1 oracle oinstall 5519360 Nov 26 07:34 1_7_832476410.arc
-rw-r----- 1 oracle oinstall 1024 Nov 26 07:34 1_8_832476410.arc
-rw-r----- 1 oracle oinstall 1024 Nov 26 07:34 1_9_832476410.arc
(16)
再次恢复:输入auto
SQL> recover database;
ORA-00279: change 577511 generated at 11/26/2013 06:25:59 needed for thread 1
ORA-00289: suggestion : /arch/1_4_832476410.arc
ORA-00280: change 577511 for thread 1 is in sequence #4
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
注意归档自动应用,并且介质恢复完成。
ORA-00279: change 578065 generated at 11/26/2013 06:42:33 needed for thread 1
ORA-00289: suggestion : /arch/1_6_832476410.arc
ORA-00280: change 578065 for thread 1 is in sequence #6
ORA-00278: log file '/arch/1_5_832476410.arc' no longer needed for this
recovery
ORA-00279: change 598139 generated at 11/26/2013 06:50:53 needed for thread 1
ORA-00289: suggestion : /arch/1_7_832476410.arc
ORA-00280: change 598139 for thread 1 is in sequence #7
ORA-00278: log file '/arch/1_6_832476410.arc' no longer needed for this
recovery
ORA-00279: change 609383 generated at 11/26/2013 07:02:21 needed for thread 1
ORA-00289: suggestion : /arch/1_8_832476410.arc
ORA-00280: change 609383 for thread 1 is in sequence #8
ORA-00278: log file '/arch/1_7_832476410.arc' no longer needed for this
recovery
ORA-00279: change 609385 generated at 11/26/2013 07:02:23 needed for thread 1
ORA-00289: suggestion : /arch/1_9_832476410.arc
ORA-00280: change 609385 for thread 1 is in sequence #9
ORA-00278: log file '/arch/1_8_832476410.arc' no longer needed for this
recovery
ORA-00279: change 609387 generated at 11/26/2013 07:02:24 needed for thread 1
ORA-00289: suggestion : /arch/1_10_832476410.arc
ORA-00280: change 609387 for thread 1 is in sequence #10
ORA-00278: log file '/arch/1_9_832476410.arc' no longer needed for this
recovery
ORA-00279: change 609389 generated at 11/26/2013 07:02:24 needed for thread 1
ORA-00289: suggestion : /arch/1_11_832476410.arc
ORA-00280: change 609389 for thread 1 is in sequence #11
ORA-00278: log file '/arch/1_10_832476410.arc' no longer needed for this
recovery
Log applied.
Media recovery complete.
(17)
打开数据库
SQL> alter database open;
Database altered.
(18)
查看数据
SQL> select count(*) from zsx;
COUNT(*)
----------
10000
注意:
10000条数据都在,没有发现丢失。
操作系统:oracle_linux_5.6_32
数据库:oracle_11.2.0.3 单实例
问题:
我一直有个问题,用delete in put这个命令的时候,备份的时候顺便备份归档了然后删掉了
原来目录下的归档,但是如果下次我们恢复的时候,需要归档怎么办,原来的位置下没有
归档了,oracle会自动应用刚刚备份的归档吗。
实验步骤如下:
(1)
查看表zsx中有多少条数据
SQL> select * from zsx;
ID
----------
1
2
3
4
5
6
7
8
9
10
(2)
做一个全备包括归档:
RMAN> backup database format '/backup/2013_11_14_1055_%U.bak'
plus archivelog delete all input;
(3)
插入10条数据
begin
for i in 11..20
loop
insert into zsx values (i);
commit;
end loop;
end;
(4)
SQL> alter system checkpoint;
SQL> alter system switch logfile;
(5)
继续插入数据
begin
for i in 21..10000
loop
insert into zsx values (i);
commit;
end loop;
end;
(6)
会产生更多的归档,我们将归档备份走:
RMAN> backup archivelog all format '/backup/2013_11_14_1139_arc_%U.bak' delete input;
(7)
现在想做完全恢复,但是/arch下的归档都没有了,而原先备份的时候只有10条数据
[oracle@RAC1 arch]$ ll
total 0
(8)
我们试着做完全恢复,看看会不会oracle自动用备份的归档。
[oracle@RAC1 rac1]$ rm -rf *.dbf
(9)
shutdown abort;
(10)
startup mount;
(11)
RMAN> restore database;
(12)
SQL> recover database;
ORA-00279: change 577511 generated at 11/26/2013 06:25:59 needed for thread 1
ORA-00289: suggestion : /arch/1_4_832476410.arc
ORA-00280: change 577511 for thread 1 is in sequence #4
Specify log: {=suggested | filename | AUTO | CANCEL}
注意:
这时候默认的归档路径下没有归档了,但是指定需要备份的归档而且路径是在/arch,我们拭目以待,
敲个auto。
auto
ORA-00308: cannot open archived log '/arch/1_4_832476410.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
注意:
发现不行。
(13)
根据上面发现,我们需要4号归档,因为我们有备份,所以不怕。
(14)
恢复归档
既然需要4号,而且4号以后的都没有了,那么我们就从4号归档开始恢复:
RMAN> restore archivelog from logseq 4;
(15)
查看归档路径下的归档,从4号到我们备份时候的归档都有了
[oracle@RAC1 arch]$ ll
total 5788
-rw-r----- 1 oracle oinstall 1024 Nov 26 07:34 1_10_832476410.arc
-rw-r----- 1 oracle oinstall 50176 Nov 26 07:34 1_11_832476410.arc
-rw-r----- 1 oracle oinstall 4608 Nov 26 07:34 1_12_832476410.arc
-rw-r----- 1 oracle oinstall 2048 Nov 26 07:34 1_13_832476410.arc
-rw-r----- 1 oracle oinstall 18944 Nov 26 07:34 1_4_832476410.arc
-rw-r----- 1 oracle oinstall 260096 Nov 26 07:34 1_5_832476410.arc
-rw-r----- 1 oracle oinstall 22528 Nov 26 07:34 1_6_832476410.arc
-rw-r----- 1 oracle oinstall 5519360 Nov 26 07:34 1_7_832476410.arc
-rw-r----- 1 oracle oinstall 1024 Nov 26 07:34 1_8_832476410.arc
-rw-r----- 1 oracle oinstall 1024 Nov 26 07:34 1_9_832476410.arc
(16)
再次恢复:输入auto
SQL> recover database;
ORA-00279: change 577511 generated at 11/26/2013 06:25:59 needed for thread 1
ORA-00289: suggestion : /arch/1_4_832476410.arc
ORA-00280: change 577511 for thread 1 is in sequence #4
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
注意归档自动应用,并且介质恢复完成。
ORA-00279: change 578065 generated at 11/26/2013 06:42:33 needed for thread 1
ORA-00289: suggestion : /arch/1_6_832476410.arc
ORA-00280: change 578065 for thread 1 is in sequence #6
ORA-00278: log file '/arch/1_5_832476410.arc' no longer needed for this
recovery
ORA-00279: change 598139 generated at 11/26/2013 06:50:53 needed for thread 1
ORA-00289: suggestion : /arch/1_7_832476410.arc
ORA-00280: change 598139 for thread 1 is in sequence #7
ORA-00278: log file '/arch/1_6_832476410.arc' no longer needed for this
recovery
ORA-00279: change 609383 generated at 11/26/2013 07:02:21 needed for thread 1
ORA-00289: suggestion : /arch/1_8_832476410.arc
ORA-00280: change 609383 for thread 1 is in sequence #8
ORA-00278: log file '/arch/1_7_832476410.arc' no longer needed for this
recovery
ORA-00279: change 609385 generated at 11/26/2013 07:02:23 needed for thread 1
ORA-00289: suggestion : /arch/1_9_832476410.arc
ORA-00280: change 609385 for thread 1 is in sequence #9
ORA-00278: log file '/arch/1_8_832476410.arc' no longer needed for this
recovery
ORA-00279: change 609387 generated at 11/26/2013 07:02:24 needed for thread 1
ORA-00289: suggestion : /arch/1_10_832476410.arc
ORA-00280: change 609387 for thread 1 is in sequence #10
ORA-00278: log file '/arch/1_9_832476410.arc' no longer needed for this
recovery
ORA-00279: change 609389 generated at 11/26/2013 07:02:24 needed for thread 1
ORA-00289: suggestion : /arch/1_11_832476410.arc
ORA-00280: change 609389 for thread 1 is in sequence #11
ORA-00278: log file '/arch/1_10_832476410.arc' no longer needed for this
recovery
Log applied.
Media recovery complete.
(17)
打开数据库
SQL> alter database open;
Database altered.
(18)
查看数据
SQL> select count(*) from zsx;
COUNT(*)
----------
10000
注意:
10000条数据都在,没有发现丢失。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29107230/viewspace-1062261/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29107230/viewspace-1062261/