断档情况,主要是针对备库来说,使用归档日志过程中,中间有个日志文件损坏了,或者丢失了,
即使开启着使用日志功能,也会 导致了往后所有的日志文件都不能使用,也即导致了主库与备库不同步,
这样的结果就是数据同步 备库功能丧失了。不同版本的oracle软件,对断档的恢复功能不一样。有些版本
在恢复主库中的日志之后,需要手动注册并使用日志,有些版本自动恢复使用日子,不需要手动注册。
断档的举例:
SQL> select SEQUENCE#,APPLIED from v$archived_log;
... ...
SEQUENCE# APPLIED
---------- ---------
105 YES
106 YES
102 YES
103 YES
107 YES
104 YES
108 YES
108 YES
(109 NO) #例如:109号日志文件损坏了或者丢失了
SEQUENCE# APPLIED
---------- ---------
110 NO
111 NO
112 NO #这种情况,109号日志文件之后的110、111与112号日志文件就不能被使用,就是断档的状况
-- 恢复断档日志文件的问题:
--模拟生产环境:
--主库的归档日志文件:
SQL> select SEQUENCE#,APPLIED from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
97 YES
96 YES
98 YES
99 YES
100 YES
... ...
SEQUENCE# APPLIED
---------- ---------
118 NO
119 YES
119 NO
120 YES
120 NO
121 NO
121 YES
122 NO
122 YES
42 rows selected.
--备库使用的日志文件:
SQL> select SEQUENCE#,APPLIED from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
67 YES
68 YES
69 YES
70 YES
... ...
115 YES
116 YES
117 YES
118 YES
119 YES
120 YES
SEQUENCE# APPLIED
---------- ---------
121 YES
122 YES
68 rows selected.
--查看主库suxing用户下的mytable表数据:
SQL> select * from MYTABLE;
ID NAME TEL CREATED
---------- --------------- ---------- ---------
12345 Suxing su 1.2365E+10
12346 Sky 1.3727E+10 16-OCT-16
--查看备库suxing用户下的mytable表数据:
SQL> select * from MYTABLE;
ID NAME TEL CREATED
---------- --------------- ---------- ---------
12345 Suxing su 1.2365E+10
12346 Sky 1.3727E+10 16-OCT-16
--在主库往MYTABLE插入一条数据,提交并切换日志组:
SQL> insert into mytable values(12347,'prod',15824678923,'');
1 row created.
SQL> commit;
Commit complete.
--再插入一条数据,提交并切换日志:
SQL> insert into mytable values(12348,'enmo',15924678923,'');
1 row created.
SQL> commit;
Commit complete.
SQL> show user
USER is "SYS"
SQL>
SQL> insert into suxing.mytable values(12349,'ppenmo',13924678923,sysdate);
1 row created.
SQL> insert into suxing.mytable values(12350,'qqenmo',13924678921,sysdate);
1 row created.
SQL> commit;
Commit complete.
--主库查看mytable的数据:
SQL> select * from suxing.mytable;
ID NAME TEL CREATED
---------- --------------- ---------- ---------
12349 ppenmo 1.3925E+10 20-OCT-16
12350 qqenmo 1.3925E+10 20-OCT-16
12345 Suxing su 1.2365E+10
12346 Sky 1.3727E+10 16-OCT-16
12347 prod 1.5825E+10
12348 enmo 1.5925E+10
6 rows selected.
--此时查看备库只用的日志文件:
SQL> select SEQUENCE#,APPLIED from v$archived_log;
... ...
SEQUENCE# APPLIED
---------- ---------
121 YES
122 YES
123 NO
124 YES
125 YES
123 YES
126 NO
127 NO
128 NO
75 rows selected.
--此时查看备库mytable表中的数据:
SQL> select * from suxing.mytable;
ID NAME TEL CREATED
---------- --------------- ---------- ---------
12345 Suxing su 1.2365E+10
12346 Sky 1.3727E+10 16-OCT-16
12347 prod 1.5825E+10
12348 enmo 1.5925E+10
--主库中有新归档的日志文件:126、127与128号归档日志文件:
[oracle@oracle ENMO]$ ls
1_100_924523013.arc 1_106_924523013.arc 1_112_924523013.arc 1_118_924523013.arc 1_124_924523013.arc 1_2_925677913.arc
1_101_924523013.arc 1_107_924523013.arc 1_113_924523013.arc 1_119_924523013.arc 1_125_924523013.arc 1_3_925677913.arc
1_102_924523013.arc 1_108_924523013.arc 1_114_924523013.arc 1_120_924523013.arc 1_126_924523013.arc 1_96_924523013.arc
1_103_924523013.arc 1_109_924523013.arc 1_115_924523013.arc 1_121_924523013.arc 1_127_924523013.arc 1_97_924523013.arc
1_104_924523013.arc 1_110_924523013.arc 1_116_924523013.arc 1_122_924523013.arc 1_128_924523013.arc 1_98_924523013.arc
1_105_924523013.arc 1_111_924523013.arc 1_117_924523013.arc 1_123_924523013.arc 1_1_925677913.arc 1_99_924523013.arc
[oracle@oracle ENMO]$
--模拟断档情况,备库丢失123号归档日志文件1_126_924523013.arc:
[oracle@enmo PROD]$ mv 1_126_924523013.arc /home/oracle/arch/PROD/bk/1_126_924523013.arc
[oracle@enmo PROD]$ ls
1_100_924523013.arc 1_108_924523013.arc 1_116_924523013.arc 1_124_924523013.arc 1_96_924523013.arc
1_101_924523013.arc 1_109_924523013.arc 1_117_924523013.arc 1_125_924523013.arc 1_97_924523013.arc
1_102_924523013.arc 1_110_924523013.arc 1_118_924523013.arc 1_127_924523013.arc 1_98_924523013.arc
1_103_924523013.arc 1_111_924523013.arc 1_119_924523013.arc 1_128_924523013.arc 1_99_924523013.arc
1_104_924523013.arc 1_112_924523013.arc 1_120_924523013.arc 1_92_924523013.arc bk
1_105_924523013.arc 1_113_924523013.arc 1_121_924523013.arc 1_93_924523013.arc
1_106_924523013.arc 1_114_924523013.arc 1_122_924523013.arc 1_94_924523013.arc
1_107_924523013.arc 1_115_924523013.arc 1_123_924523013.arc 1_95_924523013.arc
[oracle@enmo PROD]$
可看到,126号日志文件丢失了。
--备库尝试打开使用日志文件并查看是否使用127与128号归档日志:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
SQL> select SEQUENCE#,APPLIED from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
67 YES
68 YES
... ...
SEQUENCE# APPLIED
---------- ---------
121 YES
122 YES
123 NO
124 YES
125 YES
123 YES
126 NO
127 NO
128 NO
75 rows selected.
发现怎么使用日志,都不能用,因为126号日志文件丢失了。
--关闭日志 使用:
SQL> alter database recover managed standby database cancel;
Database altered.
--从主库找回对应的日志文件拷贝到备库,然后手动注册使用日志:
[oracle@enmo bk]$ cp 1_126_924523013.arc /home/oracle/arch/PROD/1_126_924523013.arc
[oracle@enmo bk]$ cd ..
[oracle@enmo PROD]$ ls
1_100_924523013.arc 1_108_924523013.arc 1_116_924523013.arc 1_124_924523013.arc 1_93_924523013.arc
1_101_924523013.arc 1_109_924523013.arc 1_117_924523013.arc 1_125_924523013.arc 1_94_924523013.arc
1_102_924523013.arc 1_110_924523013.arc 1_118_924523013.arc 1_126_924523013.arc 1_95_924523013.arc
1_103_924523013.arc 1_111_924523013.arc 1_119_924523013.arc 1_127_924523013.arc 1_96_924523013.arc
1_104_924523013.arc 1_112_924523013.arc 1_120_924523013.arc 1_128_924523013.arc 1_97_924523013.arc
1_105_924523013.arc 1_113_924523013.arc 1_121_924523013.arc 1_129_924523013.arc 1_98_924523013.arc
1_106_924523013.arc 1_114_924523013.arc 1_122_924523013.arc 1_130_924523013.arc 1_99_924523013.arc
1_107_924523013.arc 1_115_924523013.arc 1_123_924523013.arc 1_92_924523013.arc bk
此时已经找回126号日志文件。
--手动注册使用日志:
SQL> alter database register physical logfile '/home/oracle/arch/PROD/1_126_924523013.arc';
Database altered.
注册成功。
--恢复使用日志并查看使用状况 :
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
SQL> select SEQUENCE#,APPLIED from v$archived_log;
... ...
SEQUENCE# APPLIED
---------- ---------
121 YES
122 YES
123 NO
124 YES
125 YES
123 YES
126 NO
127 YES
128 YES
129 YES
130 IN-MEMORY
SEQUENCE# APPLIED
---------- ---------
126 YES
可以看到,恢复使用126日志之后,126号日志后面的日志文件自动使用到。
--使用日志文件之后,备库查看mytable中的数据是否与主库保持一致性:
SQL> select * from suxing.mytable;
ID NAME TEL CREATED
---------- --------------- ---------- ---------
12349 ppenmo 1.3925E+10 20-OCT-16
12350 qqenmo 1.3925E+10 20-OCT-16
12345 Suxing su 1.2365E+10
12346 Sky 1.3727E+10 16-OCT-16
12347 prod 1.5825E+10
12348 enmo 1.5925E+10
6 rows selected.
可以看到,备库恢复到与主库一致的状态。
即使开启着使用日志功能,也会 导致了往后所有的日志文件都不能使用,也即导致了主库与备库不同步,
这样的结果就是数据同步 备库功能丧失了。不同版本的oracle软件,对断档的恢复功能不一样。有些版本
在恢复主库中的日志之后,需要手动注册并使用日志,有些版本自动恢复使用日子,不需要手动注册。
断档的举例:
SQL> select SEQUENCE#,APPLIED from v$archived_log;
... ...
SEQUENCE# APPLIED
---------- ---------
105 YES
106 YES
102 YES
103 YES
107 YES
104 YES
108 YES
108 YES
(109 NO) #例如:109号日志文件损坏了或者丢失了
SEQUENCE# APPLIED
---------- ---------
110 NO
111 NO
112 NO #这种情况,109号日志文件之后的110、111与112号日志文件就不能被使用,就是断档的状况
-- 恢复断档日志文件的问题:
--模拟生产环境:
--主库的归档日志文件:
SQL> select SEQUENCE#,APPLIED from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
97 YES
96 YES
98 YES
99 YES
100 YES
... ...
SEQUENCE# APPLIED
---------- ---------
118 NO
119 YES
119 NO
120 YES
120 NO
121 NO
121 YES
122 NO
122 YES
42 rows selected.
--备库使用的日志文件:
SQL> select SEQUENCE#,APPLIED from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
67 YES
68 YES
69 YES
70 YES
... ...
115 YES
116 YES
117 YES
118 YES
119 YES
120 YES
SEQUENCE# APPLIED
---------- ---------
121 YES
122 YES
68 rows selected.
--查看主库suxing用户下的mytable表数据:
SQL> select * from MYTABLE;
ID NAME TEL CREATED
---------- --------------- ---------- ---------
12345 Suxing su 1.2365E+10
12346 Sky 1.3727E+10 16-OCT-16
--查看备库suxing用户下的mytable表数据:
SQL> select * from MYTABLE;
ID NAME TEL CREATED
---------- --------------- ---------- ---------
12345 Suxing su 1.2365E+10
12346 Sky 1.3727E+10 16-OCT-16
--在主库往MYTABLE插入一条数据,提交并切换日志组:
SQL> insert into mytable values(12347,'prod',15824678923,'');
1 row created.
SQL> commit;
Commit complete.
--再插入一条数据,提交并切换日志:
SQL> insert into mytable values(12348,'enmo',15924678923,'');
1 row created.
SQL> commit;
Commit complete.
SQL> show user
USER is "SYS"
SQL>
SQL> insert into suxing.mytable values(12349,'ppenmo',13924678923,sysdate);
1 row created.
SQL> insert into suxing.mytable values(12350,'qqenmo',13924678921,sysdate);
1 row created.
SQL> commit;
Commit complete.
--主库查看mytable的数据:
SQL> select * from suxing.mytable;
ID NAME TEL CREATED
---------- --------------- ---------- ---------
12349 ppenmo 1.3925E+10 20-OCT-16
12350 qqenmo 1.3925E+10 20-OCT-16
12345 Suxing su 1.2365E+10
12346 Sky 1.3727E+10 16-OCT-16
12347 prod 1.5825E+10
12348 enmo 1.5925E+10
6 rows selected.
--此时查看备库只用的日志文件:
SQL> select SEQUENCE#,APPLIED from v$archived_log;
... ...
SEQUENCE# APPLIED
---------- ---------
121 YES
122 YES
123 NO
124 YES
125 YES
123 YES
126 NO
127 NO
128 NO
75 rows selected.
--此时查看备库mytable表中的数据:
SQL> select * from suxing.mytable;
ID NAME TEL CREATED
---------- --------------- ---------- ---------
12345 Suxing su 1.2365E+10
12346 Sky 1.3727E+10 16-OCT-16
12347 prod 1.5825E+10
12348 enmo 1.5925E+10
--主库中有新归档的日志文件:126、127与128号归档日志文件:
[oracle@oracle ENMO]$ ls
1_100_924523013.arc 1_106_924523013.arc 1_112_924523013.arc 1_118_924523013.arc 1_124_924523013.arc 1_2_925677913.arc
1_101_924523013.arc 1_107_924523013.arc 1_113_924523013.arc 1_119_924523013.arc 1_125_924523013.arc 1_3_925677913.arc
1_102_924523013.arc 1_108_924523013.arc 1_114_924523013.arc 1_120_924523013.arc 1_126_924523013.arc 1_96_924523013.arc
1_103_924523013.arc 1_109_924523013.arc 1_115_924523013.arc 1_121_924523013.arc 1_127_924523013.arc 1_97_924523013.arc
1_104_924523013.arc 1_110_924523013.arc 1_116_924523013.arc 1_122_924523013.arc 1_128_924523013.arc 1_98_924523013.arc
1_105_924523013.arc 1_111_924523013.arc 1_117_924523013.arc 1_123_924523013.arc 1_1_925677913.arc 1_99_924523013.arc
[oracle@oracle ENMO]$
--模拟断档情况,备库丢失123号归档日志文件1_126_924523013.arc:
[oracle@enmo PROD]$ mv 1_126_924523013.arc /home/oracle/arch/PROD/bk/1_126_924523013.arc
[oracle@enmo PROD]$ ls
1_100_924523013.arc 1_108_924523013.arc 1_116_924523013.arc 1_124_924523013.arc 1_96_924523013.arc
1_101_924523013.arc 1_109_924523013.arc 1_117_924523013.arc 1_125_924523013.arc 1_97_924523013.arc
1_102_924523013.arc 1_110_924523013.arc 1_118_924523013.arc 1_127_924523013.arc 1_98_924523013.arc
1_103_924523013.arc 1_111_924523013.arc 1_119_924523013.arc 1_128_924523013.arc 1_99_924523013.arc
1_104_924523013.arc 1_112_924523013.arc 1_120_924523013.arc 1_92_924523013.arc bk
1_105_924523013.arc 1_113_924523013.arc 1_121_924523013.arc 1_93_924523013.arc
1_106_924523013.arc 1_114_924523013.arc 1_122_924523013.arc 1_94_924523013.arc
1_107_924523013.arc 1_115_924523013.arc 1_123_924523013.arc 1_95_924523013.arc
[oracle@enmo PROD]$
可看到,126号日志文件丢失了。
--备库尝试打开使用日志文件并查看是否使用127与128号归档日志:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
SQL> select SEQUENCE#,APPLIED from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
67 YES
68 YES
... ...
SEQUENCE# APPLIED
---------- ---------
121 YES
122 YES
123 NO
124 YES
125 YES
123 YES
126 NO
127 NO
128 NO
75 rows selected.
发现怎么使用日志,都不能用,因为126号日志文件丢失了。
--关闭日志 使用:
SQL> alter database recover managed standby database cancel;
Database altered.
--从主库找回对应的日志文件拷贝到备库,然后手动注册使用日志:
[oracle@enmo bk]$ cp 1_126_924523013.arc /home/oracle/arch/PROD/1_126_924523013.arc
[oracle@enmo bk]$ cd ..
[oracle@enmo PROD]$ ls
1_100_924523013.arc 1_108_924523013.arc 1_116_924523013.arc 1_124_924523013.arc 1_93_924523013.arc
1_101_924523013.arc 1_109_924523013.arc 1_117_924523013.arc 1_125_924523013.arc 1_94_924523013.arc
1_102_924523013.arc 1_110_924523013.arc 1_118_924523013.arc 1_126_924523013.arc 1_95_924523013.arc
1_103_924523013.arc 1_111_924523013.arc 1_119_924523013.arc 1_127_924523013.arc 1_96_924523013.arc
1_104_924523013.arc 1_112_924523013.arc 1_120_924523013.arc 1_128_924523013.arc 1_97_924523013.arc
1_105_924523013.arc 1_113_924523013.arc 1_121_924523013.arc 1_129_924523013.arc 1_98_924523013.arc
1_106_924523013.arc 1_114_924523013.arc 1_122_924523013.arc 1_130_924523013.arc 1_99_924523013.arc
1_107_924523013.arc 1_115_924523013.arc 1_123_924523013.arc 1_92_924523013.arc bk
此时已经找回126号日志文件。
--手动注册使用日志:
SQL> alter database register physical logfile '/home/oracle/arch/PROD/1_126_924523013.arc';
Database altered.
注册成功。
--恢复使用日志并查看使用状况 :
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
SQL> select SEQUENCE#,APPLIED from v$archived_log;
... ...
SEQUENCE# APPLIED
---------- ---------
121 YES
122 YES
123 NO
124 YES
125 YES
123 YES
126 NO
127 YES
128 YES
129 YES
130 IN-MEMORY
SEQUENCE# APPLIED
---------- ---------
126 YES
可以看到,恢复使用126日志之后,126号日志后面的日志文件自动使用到。
--使用日志文件之后,备库查看mytable中的数据是否与主库保持一致性:
SQL> select * from suxing.mytable;
ID NAME TEL CREATED
---------- --------------- ---------- ---------
12349 ppenmo 1.3925E+10 20-OCT-16
12350 qqenmo 1.3925E+10 20-OCT-16
12345 Suxing su 1.2365E+10
12346 Sky 1.3727E+10 16-OCT-16
12347 prod 1.5825E+10
12348 enmo 1.5925E+10
6 rows selected.
可以看到,备库恢复到与主库一致的状态。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31392094/viewspace-2126858/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31392094/viewspace-2126858/