[Home] [Help]
PROCEDURE: SYS.DBMS_FEATURE_DATA_GUARD
Source
1 procedure DBMS_FEATURE_DATA_GUARD
2 (feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB)
5 AS
6 feature_usage varchar2(1000);
7 log_transport varchar2(25);
8 num_arch number;
9 num_casc_stby number;
10 num_compression number;
11 num_dgconfig number;
12 num_far_sync number;
13 num_fast_sync number;
14 num_lgwr_async number;
15 num_lgwr_sync number;
16 num_realtime_apply number;
17 num_redo_apply number;
18 num_snapshot number;
19 num_sql_apply number;
20 num_standbys number;
21 num_terminal_db number;
22 num_ra number;
23 protection_mode varchar2(24);
24 use_broker varchar2(5);
25 use_compression varchar2(8);
26 use_far_sync varchar2(5);
27 use_fast_sync varchar2(5);
28 use_flashback varchar2(18);
29 use_fs_failover varchar2(22);
30 use_realtime_apply varchar2(5);
31 use_redo_apply varchar2(5);
32 use_snapshot varchar2(5);
33 use_sql_apply varchar2(5);
34 use_ra varchar2(5);
35
36 begin
37 -- initialize
38 feature_boolean := 0;
39 aux_count := 0;
40 log_transport := NULL;
41 num_arch := 0;
42 num_casc_stby := 0;
43 num_compression := 0;
44 num_dgconfig :=0;
45 num_far_sync := 0;
46 num_fast_sync := 0;
47 num_lgwr_async := 0;
48 num_lgwr_sync := 0;
49 num_realtime_apply := 0;
50 num_redo_apply := 0;
51 num_snapshot := 0;
52 num_sql_apply := 0;
53 num_standbys := 0;
54 num_terminal_db := 0;
55 num_ra := 0;
56 use_broker := 'FALSE';
57 use_compression := 'FALSE';
58 use_far_sync := 'FALSE';
59 use_fast_sync := 'FALSE';
60 use_flashback := 'FALSE';
61 use_fs_failover := 'FALSE';
62 use_realtime_apply := 'FALSE';
63 use_redo_apply := 'FALSE';
64 use_snapshot := 'FALSE';
65 use_sql_apply := 'FALSE';
66 use_ra := 'FALSE';
67
68 -- check for Data Guard usage by counting valid standby destinations
69 -- We use v$archive_dest here and NOT v$dataguard_config because if the
70 -- dg_config is not initialized, v$dataguard_config will be empty.
71 execute immediate 'select count(*) from v$archive_dest ' ||
72 'where status = ''VALID'' and target = ''STANDBY'''
73 into num_standbys;
74
75 if (num_standbys > 0) then
76 feature_boolean := 1;
77
78 -- determine if v$dataguard_config is populated
79 execute immediate 'select count(*) from v$dataguard_config'
80 into num_dgconfig;
81
82 -- Depending on whether v$dataguard_config is populated or not, some
83 -- of the commands below will either use v$dataguard_config or
84 -- v$archive_dest.
85
86 if (num_dgconfig > 0) then
87 -- get the real number of standbys
88 execute immediate 'select count(unique(DB_UNIQUE_NAME)) ' ||
89 'from v$dataguard_config ' ||
90 'where (DEST_ROLE like ''% STANDBY'')'
91 into num_standbys;
92
93 -- get the number of cascading standbys
94 execute immediate 'select count(unique(PARENT_DBUN)) ' ||
95 'from v$dataguard_config ' ||
96 'where (PARENT_DBUN not in ' ||
97 '(select DB_UNIQUE_NAME from v$database) and ' ||
98 'PARENT_DBUN != ''NONE'' and PARENT_DBUN != ''UNKNOWN'')'
99 into num_casc_stby;
100
101 -- get the number of terminal databases
102 execute immediate 'select count(unique(DB_UNIQUE_NAME)) ' ||
103 'from v$dataguard_config ' ||
104 'where (DB_UNIQUE_NAME not in ' ||
105 '(select PARENT_DBUN from v$dataguard_config) and ' ||
106 'PARENT_DBUN != ''UNKNOWN'')'
107 into num_terminal_db;
108
109 -- check for Redo Apply (Physical Standby) usage
110 execute immediate 'select count(unique(DB_UNIQUE_NAME)) ' ||
111 'from v$dataguard_config ' ||
112 'where (DEST_ROLE = ''PHYSICAL STANDBY'')'
113 into num_redo_apply;
114 if (num_redo_apply > 0) then
115 use_redo_apply := 'TRUE';
116 end if;
117
118 -- check for SQL Apply (Logical Standby) usage
119 execute immediate 'select count(unique(DB_UNIQUE_NAME)) ' ||
120 'from v$dataguard_config ' ||
121 'where (DEST_ROLE = ''LOGICAL STANDBY'')'
122 into num_sql_apply;
123 if (num_sql_apply > 0) then
124 use_sql_apply := 'TRUE';
125 end if;
126
127 -- check for Far Sync Instance usage
128 execute immediate 'select count(unique(DB_UNIQUE_NAME)) ' ||
129 'from v$dataguard_config ' ||
130 'where (DEST_ROLE = ''FAR SYNC INSTANCE'')'
131 into num_far_sync;
132 if (num_far_sync > 0) then
133 use_far_sync := 'TRUE';
134 end if;
135
136 -- check for Snapshot Standby usage
137 execute immediate 'select count(unique(DB_UNIQUE_NAME)) ' ||
138 'from v$dataguard_config ' ||
139 'where (DEST_ROLE = ''SNAPSHOT STANDBY'')'
140 into num_snapshot;
141 if (num_snapshot > 0) then
142 use_snapshot := 'TRUE';
143 end if;
144
145 -- check for Recovery Appliance usage using v$dataguard_config
146 execute immediate 'select count(unique(DB_UNIQUE_NAME)) ' ||
147 'from v$dataguard_config ' ||
148 'where (DEST_ROLE = ''BACKUP APPLIANCE'')'
149 into num_ra;
150 if (num_ra > 0) then
151 use_ra := 'TRUE';
152 end if;
153
154 else
155 -- check for Redo Apply (Physical Standby) usage
156 execute immediate 'select count(*) from v$archive_dest_status ' ||
157 'where status = ''VALID'' and type = ''PHYSICAL'''
158 into num_redo_apply;
159 if (num_redo_apply > 0) then
160 use_redo_apply := 'TRUE';
161 end if;
162
163 -- check for SQL Apply (Logical Standby) usage
164 execute immediate 'select count(*) from v$archive_dest_status ' ||
165 'where status = ''VALID'' and type = ''LOGICAL'''
166 into num_sql_apply;
167 if (num_sql_apply > 0) then
168 use_sql_apply := 'TRUE';
169 end if;
170
171 -- check for Far Sync Instance usage
172 execute immediate 'select count(*) from v$archive_dest_status ' ||
173 'where status = ''VALID'' and type = ''FAR SYNC'''
174 into num_far_sync;
175 if (num_far_sync > 0) then
176 use_far_sync := 'TRUE';
177 end if;
178
179 -- copy far sync instance count into cascading standby
180 num_casc_stby := num_far_sync;
181
182 -- check for Snapshot Standby usage
183 execute immediate 'select count(*) from v$archive_dest_status ' ||
184 'where status = ''VALID'' and type = ''SNAPSHOT'''
185 into num_snapshot;
186 if (num_snapshot > 0) then
187 use_snapshot := 'TRUE';
188 end if;
189
190 -- check for Recovery Appliance usage using v$archive_dest_status
191 execute immediate 'select count(*) from v$archive_dest_status ' ||
192 'where status = ''VALID'' and type = ''BACKUP APPLIANCE'''
193 into num_ra;
194 if (num_ra > 0) then
195 use_ra := 'TRUE';
196 end if;
197
198 end if;
199
200 -- check for Broker usage by selecting the init param value
201 execute immediate 'select value from v$system_parameter ' ||
202 'where name = ''dg_broker_start'''
203 into use_broker;
204
205 -- get all log transport methods
206 execute immediate 'select count(*) from v$archive_dest ' ||
207 'where status = ''VALID'' and target = ''STANDBY'' ' ||
208 'and archiver like ''ARC%'''
209 into num_arch;
210 if (num_arch > 0) then
211 log_transport := 'ARCH ';
212 end if;
213 execute immediate 'select count(*) from v$archive_dest ' ||
214 'where status = ''VALID'' and target = ''STANDBY'' ' ||
215 'and archiver = ''LGWR'' ' ||
216 'and (transmit_mode = ''SYNCHRONOUS'' or ' ||
217 ' transmit_mode = ''PARALLELSYNC'')'
218 into num_lgwr_sync;
219 if (num_lgwr_sync > 0) then
220 log_transport := log_transport || 'LGWR SYNC ';
221 end if;
222 execute immediate 'select count(*) from v$archive_dest ' ||
223 'where status = ''VALID'' and target = ''STANDBY'' ' ||
224 'and archiver = ''LGWR'' ' ||
225 'and transmit_mode = ''ASYNCHRONOUS'''
226 into num_lgwr_async;
227 if (num_lgwr_async > 0) then
228 log_transport := log_transport || 'LGWR ASYNC';
229 end if;
230
231 -- get protection mode for primary db
232 execute immediate 'select protection_mode from v$database'
233 into protection_mode;
234
235 -- check for Fast Sync usage
236 if (protection_mode = 'MAXIMUM AVAILABILITY') then
237 execute immediate 'select count(*) from v$archive_dest ' ||
238 'where status = ''VALID'' and target = ''STANDBY'' ' ||
239 'and archiver = ''LGWR'' ' ||
240 'and (transmit_mode = ''SYNCHRONOUS'' or ' ||
241 ' transmit_mode = ''PARALLELSYNC'') ' ||
242 'and affirm = ''NO'' '
243 into num_fast_sync;
244 if (num_fast_sync > 0) then
245 use_fast_sync := 'TRUE';
246 end if;
247 end if;
248
249 -- check for fast-start failover usage
250 execute immediate 'select fs_failover_status from v$database'
251 into use_fs_failover;
252 if (use_fs_failover != 'DISABLED') then
253 use_fs_failover := 'TRUE';
254 else
255 use_fs_failover := 'FALSE';
256 end if;
257
258 -- check for realtime apply usage
259 -- We can only count the directly connected standbys
260 execute immediate 'select count(*) from v$archive_dest_status ' ||
261 'where status = ''VALID'' ' ||
262 'and recovery_mode like ''%REAL TIME APPLY'''
263 into num_realtime_apply;
264 if (num_realtime_apply > 0) then
265 use_realtime_apply := 'TRUE';
266 end if;
267
268 -- check for network compression usage
269 -- We can only count the directly connected standbys
270 execute immediate 'select count(*) from v$archive_dest ' ||
271 'where status = ''VALID'' and target = ''STANDBY'' ' ||
272 'and compression = ''ENABLE'''
273 into num_compression;
274 if (num_compression > 0) then
275 use_compression := 'TRUE';
276 end if;
277
278 -- check for flashback usage
279 execute immediate 'select flashback_on from v$database'
280 into use_flashback;
281 if (use_flashback = 'YES') then
282 use_flashback := 'TRUE';
283 else
284 use_flashback := 'FALSE';
285 end if;
286
287 feature_usage :=
288 'Number of standbys: ' || to_char(num_standbys) ||
289 ', ' || 'Number of Cascading databases: ' || to_char(num_casc_stby) ||
290 ', ' || 'Number of Terminal databases: ' || to_char(num_terminal_db) ||
291 ', ' || 'Redo Apply used: ' || upper(use_redo_apply) ||
292 ', ' || 'SQL Apply used: ' || upper(use_sql_apply) ||
293 ', ' || 'Far Sync Instance used: ' || upper(use_far_sync) ||
294 ', ' || 'Snapshot Standby used: ' || upper(use_snapshot) ||
295 ', ' || 'Broker used: ' || upper(use_broker) ||
296 ', ' || 'Protection mode: ' || upper(protection_mode) ||
297 ', ' || 'Log transports used: ' || upper(log_transport) ||
298 ', ' || 'Fast Sync used: ' || upper(use_fast_sync) ||
299 ', ' || 'Fast-Start Failover used: ' || upper(use_fs_failover) ||
300 ', ' || 'Real-Time Apply used: ' || upper(use_realtime_apply) ||
301 ', ' || 'Compression used: ' || upper(use_compression) ||
302 ', ' || 'Flashback used: ' || upper(use_flashback) ||
303 ', ' || 'Recovery Appliance used: ' || upper(use_ra)
304 ;
305 feature_info := to_clob(feature_usage);
306 else
307 feature_info := to_clob('Data Guard usage not detected');
308 end if;
309 end;