Oracle 11g的Data Guard不仅仅带给我们的是Active Data Guard实时查询特性,同时还带来了另外一个新特性，这便是Snapshot Standby数据库功能，此项功能可将备库置身于“可读写状态”用于不方便在生产环境主库中测试的内容，比如模拟上线测试等任务。当备库读写状态下任务完成后，可以非常轻松的完成Snapshot Standby数据库角色切换回备库角色，恢复与主库数据同步。在Snapshot Standby数据库状态下，备库是可以接收主库传过来的日志，但是不能对日志进行应用。
案例分析：
1、查看数据库信息
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
Primay DB:
14
:
47
:
09
SYS@ prod >select name,database_role,protection_mode
from
v$database;
NAME DATABASE_ROLE PROTECTION_MODE
--------- ---------------- --------------------
PROD PRIMARY MAXIMUM PERFORMANCE
14
:
46
:
52
SYS@ prod >select max(sequence#)
from
v$archived_log;
MAX(SEQUENCE#)
--------------
536
Standby DB:
14
:
47
:
04
SYS@ shdb >select name,database_role,protection_mode
from
v$database;
NAME DATABASE_ROLE PROTECTION_MODE
--------- ---------------- --------------------
PROD PHYSICAL STANDBY MAXIMUM PERFORMANCE
14
:
46
:
09
SYS@ shdb >select max(sequence#)
from
v$archived_log;
MAX(SEQUENCE#)
--------------
536
|
2、切换备库到Snapshot Standby
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
|
1
）必须终止Media Recover Process
14
:
48
:
05
SYS@ shdb >alter database convert to snapshot standby;
alter database convert to snapshot standby
*
ERROR
at
line
1
:
ORA
-38784
: Cannot create restore point
'SNAPSHOT_STANDBY_REQUIRED_11/27/2014 14:50:05'
.
ORA
-01153
: an incompatible media recovery is active
14
:
50
:
05
SYS@ shdb >recover managed standby database cancel;
Media recovery complete.
2
）必须建立Recover Area
snapshot standby实际上是基于flashback database的运行机制，恢复到原先的standby状态
14
:
50
:
44
SYS@ shdb >alter database convert to snapshot standby;
alter database convert to snapshot standby
*
ERROR
at
line
1
:
ORA
-38784
: Cannot create restore point
'SNAPSHOT_STANDBY_REQUIRED_11/27/2014 14:50:58'
.
ORA
-38786
: Recovery area is
not
enabled.
3
）启用recover area
14
:
50
:
58
SYS@ shdb >show parameter recover
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer
0
recovery_parallelism integer
0
14
:
52
:
51
SYS@ shdb >alter system set db_recovery_file_dest_size=2g;
System altered.
14
:
53
:
12
SYS@ shdb >alter system set db_recovery_file_dest=
'/dsk4/backup'
;
System altered.
14
:
53
:
18
SYS@ shdb >show parameter recover
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
db_recovery_file_dest string /dsk4/backup
db_recovery_file_dest_size big integer 2G
recovery_parallelism integer
3
）转换成功
14
:
54
:
13
SYS@ shdb >alter database convert to snapshot standby;
Database altered.
Elapsed:
00
:
00
:
03.10
14
:
54
:
41
SYS@ shdb >select status
from
v$instance;
STATUS
------------
MOUNTED
告警日志：
主库：
LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (
3135
)
LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Errors
in
file /u01/app/oracle/diag/rdbms/bjdb/prod/trace/prod_nsa2_2960.trc:
ORA
-03135
: connection lost contact
Error
3135
for
archive log file
5
to
'shdb'
Errors
in
file /u01/app/oracle/diag/rdbms/bjdb/prod/trace/prod_nsa2_2960.trc:
ORA
-03135
: connection lost contact
LNS: Failed to archive log
5
thread
1
sequence
537
(
3135
)
Errors
in
file /u01/app/oracle/diag/rdbms/bjdb/prod/trace/prod_nsa2_2960.trc:
ORA
-03135
: connection lost contact
备库：
alter database convert to snapshot standby
ORA
-38784
signalled during: alter database convert to snapshot standby...
Thu Nov
27
14
:
53
:
12
2014
ALTER SYSTEM SET db_recovery_file_dest_size=
'2G'
SCOPE=MEMORY;
Using STANDBY_ARCHIVE_DEST parameter default value
as
USE_DB_RECOVERY_FILE_DEST
**********************************************************
WARNING: Files may exists
in
db_recovery_file_dest
that are
not
known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged,
then
manually
delete
them
using OS command.
One of the following events caused
this
:
1.
A backup controlfile was restored.
2.
A standby controlfile was restored.
3.
The controlfile was re-created.
4.
db_recovery_file_dest had previously been enabled
and
then
disabled.
**********************************************************
ALTER SYSTEM SET db_recovery_file_dest=
'/dsk4/backup'
SCOPE=MEMORY;
Thu Nov
27
14
:
53
:
18
2014
db_recovery_file_dest_size of
2048
MB is
0.00
% used. This is a
user-specified limit
on
the amount of space that will be used by
this
database
for
recovery-related files,
and
does
not
reflect the amount of
space available
in
the underlying filesystem
or
ASM diskgroup.
Thu Nov
27
14
:
54
:
38
2014
alter database convert to snapshot standby
Starting background process RVWR
Thu Nov
27
14
:
54
:
38
2014
RVWR started
with
pid=
21
, OS id=
2198
Allocated
3981204
bytes
in
shared pool
for
flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_11/
27
/
2014
14
:
54
:
38
krsv_proc_kill: Killing
3
processes (all RFS)
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Thu Nov
27
14
:
54
:
41
2014
SMON: disabling cache recovery
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS
after
incomplete recovery UNTIL CHANGE
8596005
Resetting resetlogs activation ID
219765236
(
0xd1959f4
)
Online log /dsk2/oradata/shdb/redo04b.log: Thread
1
Group
4
was previously cleared
Online log /dsk1/oradata/shdb/redo04a.log: Thread
1
Group
4
was previously cleared
Online log /dsk2/oradata/shdb/redo05b.log: Thread
1
Group
5
was previously cleared
Online log /dsk1/oradata/shdb/redo05a.log: Thread
1
Group
5
was previously cleared
Standby became primary SCN:
8596003
Thu Nov
27
14
:
54
:
41
2014
Setting recovery target incarnation to
3
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted
as
snapshot standby
Completed: alter database convert to snapshot standby
14
:
54
:
55
SYS@ shdb >select database_role,open_mode
from
v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY MOUNTED
|
|
1
2
|
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_11/
27
/
2014
14
:
54
:
38
在日志中可以看到，Database建立了restore point
|
3、Open Database验证
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
|
1
）open database
14
:
57
:
02
SYS@ shdb >alter database open;
Database altered.
14
:
57
:
33
SYS@ shdb >select database_role,open_mode
from
v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY READ WRITE
2
）日志切换：
主库：
14
:
47
:
31
SYS@ prod >alter system switch logfile;
System altered.
15
:
01
:
11
SYS@ prod >/
System altered.
15
:
01
:
23
SYS@ prod >select max(sequence#)
from
v$archived_log;
MAX(SEQUENCE#)
--------------
539
告警日志：
Thread
1
advanced to log sequence
538
(LGWR switch)
Current log#
4
seq#
538
mem#
0
: /dsk2/oradata/prod/redo04b.log
Current log#
4
seq#
538
mem#
1
: /dsk1/oradata/prod/redo04a.log
Thu Nov
27
14
:
59
:
46
2014
Archived Log entry
504
added
for
thread
1
sequence
537
ID
0xd1959f4
dest
1
:
Thu Nov
27
15
:
00
:
53
2014
Thread
1
cannot allocate
new
log, sequence
539
Checkpoint
not
complete
Current log#
4
seq#
538
mem#
0
: /dsk2/oradata/prod/redo04b.log
Current log#
4
seq#
538
mem#
1
: /dsk1/oradata/prod/redo04a.log
Thu Nov
27
15
:
01
:
11
2014
Thread
1
advanced to log sequence
539
(LGWR switch)
Current log#
5
seq#
539
mem#
0
: /dsk2/oradata/prod/redo05b.log
Current log#
5
seq#
539
mem#
1
: /dsk1/oradata/prod/redo05a.log
Thu Nov
27
15
:
01
:
12
2014
Archived Log entry
507
added
for
thread
1
sequence
538
ID
0xd1959f4
dest
1
:
Thread
1
cannot allocate
new
log, sequence
540
Checkpoint
not
complete
Current log#
5
seq#
539
mem#
0
: /dsk2/oradata/prod/redo05b.log
Current log#
5
seq#
539
mem#
1
: /dsk1/oradata/prod/redo05a.log
Thu Nov
27
15
:
01
:
23
2014
Thread
1
advanced to log sequence
540
(LGWR switch)
Current log#
4
seq#
540
mem#
0
: /dsk2/oradata/prod/redo04b.log
Current log#
4
seq#
540
mem#
1
: /dsk1/oradata/prod/redo04a.log
Thu Nov
27
15
:
01
:
25
2014
Archived Log entry
509
added
for
thread
1
sequence
539
ID
0xd1959f4
dest
1
:
备库：
14
:
57
:
49
SYS@ shdb >select max(sequence#)
from
v$archived_log;
MAX(SEQUENCE#)
--------------
539
告警日志：
RFS[
6
]: Assigned to RFS process
2254
RFS[
6
]: Identified database type
as
'snapshot standby'
: Client is ARCH pid
2953
Thu Nov
27
14
:
59
:
45
2014
RFS[
7
]: Assigned to RFS process
2256
RFS[
7
]: Identified database type
as
'snapshot standby'
: Client is LGWR ASYNC pid
2960
RFS[
7
]: Opened log
for
thread
1
sequence
537
dbid
219724276
branch
807885951
Archived Log entry
24
added
for
thread
1
sequence
537
rlc
807885951
ID
0xd1959f4
dest
2
:
RFS[
7
]: Opened log
for
thread
1
sequence
538
dbid
219724276
branch
807885951
Thu Nov
27
15
:
01
:
12
2014
Archived Log entry
25
added
for
thread
1
sequence
538
rlc
807885951
ID
0xd1959f4
dest
2
:
RFS[
7
]: Opened log
for
thread
1
sequence
539
dbid
219724276
branch
807885951
Thu Nov
27
15
:
01
:
23
2014
Archived Log entry
26
added
for
thread
1
sequence
539
rlc
807885951
ID
0xd1959f4
dest
2
:
RFS[
7
]: Opened log
for
thread
1
sequence
540
dbid
219724276
branch
807885951
-----可以看到备库只是接收日志，并不对日志进行apply。
3
）在备库做DML和压力测试:
15
:
03
:
52
SCOTT@ shdb >begin
15
:
03
:
59
2
for
i
in
1.
.10000
loop
15
:
04
:
06
3
insert
into
t2 values (i,i*
10
);
15
:
04
:
29
4
end loop;
15
:
04
:
34
5
commit;
15
:
04
:
35
6
end;
15
:
04
:
37
7
/
PL/SQL procedure successfully completed.
Elapsed:
00
:
00
:
04.43
15
:
04
:
42
SCOTT@ shdb >select count(*)
from
t2;
COUNT(*)
----------
10003
|
4、将数据库切回Physical Standby
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
|
1
） 关闭数据库，启动到mount
15
:
06
:
10
SYS@ shdb >shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
15
:
06
:
25
SYS@ shdb >startup mount;
ORACLE instance started.
Total System Global Area
330600448
bytes
Fixed Size
1336344
bytes
Variable Size
113249256
bytes
Database Buffers
209715200
bytes
Redo Buffers
6299648
bytes
Database mounted.
2
）转换到Physical Standby
15
:
06
:
57
SYS@ shdb >alter database convert to physical standby;
Database altered.
备库告警日志：
Using STANDBY_ARCHIVE_DEST parameter default value
as
/dsk4/arch_shdb
RFS[
1
]: Assigned to RFS process
2423
RFS[
1
]: Identified database type
as
'snapshot standby'
: Client is ARCH pid
2953
Thu Nov
27
15
:
06
:
44
2014
RFS[
2
]: Assigned to RFS process
2425
RFS[
2
]: Identified database type
as
'snapshot standby'
: Client is LGWR ASYNC pid
2960
RFS[
2
]: Opened log
for
thread
1
sequence
540
dbid
219724276
branch
807885951
Archived Log entry
28
added
for
thread
1
sequence
540
rlc
807885951
ID
0xd1959f4
dest
2
:
RFS[
2
]: Opened log
for
thread
1
sequence
541
dbid
219724276
branch
807885951
Thu Nov
27
15
:
06
:
53
2014
RFS[
3
]: Assigned to RFS process
2427
RFS[
3
]: Identified database type
as
'snapshot standby'
: Client is ARCH pid
2953
Thu Nov
27
15
:
07
:
09
2014
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (shdb)
krsv_proc_kill: Killing
2
processes (all RFS)
Flashback Restore Start
Flashback Restore Complete
Stopping background process RVWR
Deleted Oracle managed file /dsk4/backup/SHDB/flashback/o1_mf_b7flogt9_.flb
Guaranteed restore point dropped
Clearing standby activation ID
276569850
(
0x107c1efa
)
The primary database controlfile was created using the
'MAXLOGFILES 16'
clause.
There is space
for
up to
14
standby redo logfiles
Use the following SQL commands
on
the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE
'srl1.f'
SIZE
4194304
;
ALTER DATABASE ADD STANDBY LOGFILE
'srl2.f'
SIZE
4194304
;
ALTER DATABASE ADD STANDBY LOGFILE
'srl3.f'
SIZE
4194304
;
Completed: alter database convert to physical standby
3
）转换后，数据库处于非mount状态
15
:
07
:
10
SYS@ shdb >select database_role,open_mode
from
v$database;
select database_role,open_mode
from
v$database
*
ERROR
at
line
1
:
ORA
-01507
: database
not
mounted
15
:
08
:
29
SYS@ shdb >alter database mount;
alter database mount
*
ERROR
at
line
1
:
ORA
-00750
: database has been previously mounted
and
dismounted
15
:
08
:
39
SYS@ shdb >shutdown immediate;
ORA
-01507
: database
not
mounted
ORACLE instance shut down.
15
:
09
:
28
SYS@ shdb >startup
ORACLE instance started.
Total System Global Area
330600448
bytes
Fixed Size
1336344
bytes
Variable Size
113249256
bytes
Database Buffers
209715200
bytes
Redo Buffers
6299648
bytes
Database mounted.
Database opened.
4
）转换成Physical Standby
15
:
09
:
39
SYS@ shdb >select database_role,open_mode
from
v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY
备库告警日志：
Physical standby database opened
for
read only access.
Completed: ALTER DATABASE OPEN
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
Thu Nov
27
15
:
09
:
40
2014
Using STANDBY_ARCHIVE_DEST parameter default value
as
/dsk4/arch_shdb
RFS[
1
]: Assigned to RFS process
2763
RFS[
1
]: Identified database type
as
'physical standby'
: Client is ARCH pid
2953
Thu Nov
27
15
:
09
:
46
2014
RFS[
2
]: Assigned to RFS process
2765
RFS[
2
]: Identified database type
as
'physical standby'
: Client is LGWR ASYNC pid
2960
Primary database is
in
MAXIMUM PERFORMANCE mode
RFS[
2
]: Opened log
for
thread
1
sequence
541
dbid
219724276
branch
807885951
Archived Log entry
29
added
for
thread
1
sequence
541
rlc
807885951
ID
0xd1959f4
dest
2
:
RFS[
2
]: Opened log
for
thread
1
sequence
542
dbid
219724276
branch
807885951
Thu Nov
27
15
:
09
:
53
2014
RFS[
3
]: Assigned to RFS process
2768
RFS[
3
]: Identified database type
as
'physical standby'
: Client is ARCH pid
2953
主库告警日志：
Using STANDBY_ARCHIVE_DEST parameter default value
as
/dsk4/arch_prod
ALTER SYSTEM SET log_archive_dest_state_2=
'ENABLE'
SCOPE=MEMORY SID=
'*'
;
Thu Nov
27
15
:
09
:
42
2014
Thread
1
cannot allocate
new
log, sequence
542
Checkpoint
not
complete
Current log#
5
seq#
541
mem#
0
: /dsk2/oradata/prod/redo05b.log
Current log#
5
seq#
541
mem#
1
: /dsk1/oradata/prod/redo05a.log
Thu Nov
27
15
:
09
:
46
2014
******************************************************************
LGWR: Setting
'active'
archival
for
destination LOG_ARCHIVE_DEST_2
******************************************************************
Thread
1
advanced to log sequence
542
(LGWR switch)
Current log#
4
seq#
542
mem#
0
: /dsk2/oradata/prod/redo04b.log
Current log#
4
seq#
542
mem#
1
: /dsk1/oradata/prod/redo04a.log
Thu Nov
27
15
:
09
:
48
2014
Archived Log entry
513
added
for
thread
1
sequence
541
ID
0xd1959f4
dest
1
5
）查看Snapshot状态下创建的数据已经被还原
15
:
09
:
50
SYS@ shdb >conn scott/tiger
Connected.
15
:
12
:
49
SCOTT@ shdb >select count(*)
from
t2;
COUNT(*)
----------
3
|
小结
Oracle 11g DataGuar增加的Snapshot Standby数据库”功能，备库可以临时成为一个可读写的独立数据库，这极大的扩展了备库的应用场合，我们可以使用备库的这一项特殊功能将那些在生产环境中“不敢”模拟和再现的问题在备库端进行测试，测试完毕后再恢复其物理备库的身份进行日志恢复，极大的方便我们在日常生产环境中对DG的应用。
本文转自 客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/1583311，如需转载请自行联系原作者
