[Home] [Help]
PROCEDURE: SYS.DBMS_FEATURE_ACTIVE_DATA_GUARD
Source
1 PROCEDURE DBMS_FEATURE_ACTIVE_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 num_casc_stby number;
8 num_dgconfig number;
9 num_far_sync number;
10 num_realtime_query number;
11 num_terminal_db number;
12 num_rolling number;
13 num_rolling_logs number;
14 num_rolling_parts number;
15 num_rolling_pops number;
16 num_rolling_pots number;
17 num_global_seq_use number;
18 use_global_sequences varchar2(5);
19 use_realtime_query varchar2(5);
20 use_rolling varchar2(5);
21 BEGIN
22 -- initialize
23 feature_boolean := 0;
24 aux_count := 0;
25 num_casc_stby := 0;
26 num_dgconfig :=0;
27 num_far_sync := 0;
28 num_realtime_query := 0;
29 num_terminal_db := 0;
30 num_rolling := 0;
31 num_rolling_logs := 0;
32 num_rolling_parts := 0;
33 num_rolling_pops := 0;
34 num_rolling_pots := 0;
35 num_global_seq_use := 0;
36 use_global_sequences := 'FALSE';
37 use_realtime_query := 'FALSE';
38 use_rolling := 'FALSE';
39
40 -- We have to first look for each Active Data Guard feature before we can
41 -- report if they are using any of them.
42
43 -- determine if v$dataguard_config is populated
44 execute immediate 'select count(*) from v$dataguard_config'
45 into num_dgconfig;
46
47 -- Depending on whether v$dataguard_config is populated or not, some
48 -- of the commands below will either use v$dataguard_config or
49 -- v$archive_dest.
50
51 if (num_dgconfig > 0) then
52 -- get number of Far Sync Instances
53 execute immediate 'select count(unique(DB_UNIQUE_NAME)) ' ||
54 'from v$dataguard_config ' ||
55 'where (DEST_ROLE = ''FAR SYNC INSTANCE'')'
56 into num_far_sync;
57 if (num_far_sync > 0) then
58 feature_boolean := 1;
59 end if;
60
61 -- get the number of cascading standbys
62 execute immediate 'select count(unique(PARENT_DBUN)) ' ||
63 'from v$dataguard_config ' ||
64 'where (DEST_ROLE != ''LOGICAL STANDBY'') ' ||
65 'and (PARENT_DBUN not in ' ||
66 '(select DB_UNIQUE_NAME from v$database) and ' ||
67 'PARENT_DBUN != ''NONE'' and PARENT_DBUN != ''UNKNOWN'')'
68 into num_casc_stby;
69 if (num_casc_stby > 0) then
70 feature_boolean := 1;
71 end if;
72
73 -- get the number of terminal databases
74 -- Note: this is not an Active Data Guard feature, but we report it.
75 execute immediate 'select count(unique(DB_UNIQUE_NAME)) ' ||
76 'from v$dataguard_config ' ||
77 'where (DB_UNIQUE_NAME not in ' ||
78 '(select PARENT_DBUN from v$dataguard_config) and ' ||
79 'PARENT_DBUN != ''UNKNOWN'')'
80 into num_terminal_db;
81
82 -- check for DBMS_ROLLING usage
83 execute immediate 'select count(status) from dba_rolling_status'
84 into num_rolling;
85 if (num_rolling > 0) then
86 feature_boolean := 1;
87 use_rolling := 'TRUE';
88
89 -- get the total number of DBMS_ROLLING participants
90 execute immediate 'select count(dbun) ' ||
91 'from dba_rolling_databases ' ||
92 'where participant=''YES'''
93 into num_rolling_parts;
94
95 -- get the number of physicals of the original primary
96 execute immediate 'select count(scope) ' ||
97 'from dba_rolling_parameters where name=''PROTECTS''' ||
98 'and curval=''PRIMARY'' and scope in (select dbun ' ||
99 'from dba_rolling_databases where participant=''YES''' ||
100 'and role=''PHYSICAL'')'
101 into num_rolling_pops;
102
103 -- get the number of physicals of the future primary
104 execute immediate 'select count(scope) ' ||
105 'from dba_rolling_parameters where name=''PROTECTS''' ||
106 'and curval=''TRANSIENT'' and scope in (select dbun ' ||
107 'from dba_rolling_databases where participant=''YES'' ' ||
108 'and role=''PHYSICAL'')'
109 into num_rolling_pots;
110
111 -- get the number of logical standbys
112 execute immediate 'select count(dbun) ' ||
113 'from dba_rolling_databases where participant=''YES'' ' ||
114 'and role=''LOGICAL'' and dbun != ' ||
115 '(select future_primary from dba_rolling_status)'
116 into num_rolling_logs;
117 end if;
118 else
119 -- get number of Far Sync Instances
120 execute immediate 'select count(*) from v$archive_dest_status ' ||
121 'where status = ''VALID'' and type = ''FAR SYNC'''
122 into num_far_sync;
123 if (num_far_sync > 0) then
124 feature_boolean := 1;
125 end if;
126
127 -- copy far sync instance count into cascading standby
128 num_casc_stby := num_far_sync;
129 end if;
130
131 -- check for real time query usage
132 -- We can only count the directly connected standbys
133 execute immediate 'select count(*) from v$archive_dest_status ' ||
134 'where status = ''VALID'' and ' ||
135 'recovery_mode like ''MANAGED%'' and ' ||
136 'database_mode = ''OPEN_READ-ONLY'''
137 into num_realtime_query;
138 if (num_realtime_query > 0) then
139 use_realtime_query := 'TRUE';
140 feature_boolean := 1;
141 end if;
142 -- check for global sequence usage
143 execute immediate 'select count(*) from dba_sequences ' ||
144 'where sequence_owner != ''SYS'' and session_flag = ''N'''
145 into num_global_seq_use;
146 if (num_global_seq_use > 0) then
147 use_global_sequences := 'TRUE';
148 end if;
149 if (feature_boolean = 1) then
150 feature_usage :=
151 'Number of Far Sync Instances: ' || to_char(num_far_sync) ||
152 ', ' || 'Number of Cascading databases: ' || to_char(num_casc_stby) ||
153 ', ' || 'Number of Terminal databases: ' || to_char(num_terminal_db) ||
154 ', ' || 'Real Time Query used: ' || upper(use_realtime_query) ||
155 ', ' || 'Global Sequences used: ' || upper(use_global_sequences) ||
156 ', ' || 'DBMS_ROLLING used: ' || upper(use_rolling) ||
157 ', ' || 'Number of DBMS_ROLLING Participants: '
158 || to_char(num_rolling_parts) ||
159 ', ' || 'Number of DBMS_ROLLING OP Physicals: '
160 || to_char(num_rolling_pops) ||
161 ', ' || 'Number of DBMS_ROLLING FP Physicals: '
162 || to_char(num_rolling_pots) ||
163 ', ' || 'Number of DBMS_ROLLING OP Logicals: '
164 || to_char(num_rolling_logs)
165 ;
166 feature_info := to_clob(feature_usage);
167 else
168 feature_info := to_clob('Active Data Guard usage not detected');
169 end if;
170 END;