1 PACKAGE dbms_logstdby AUTHID CURRENT_USER IS
2
3 -- Skip procedure constants
4 SKIP_ACTION_SKIP CONSTANT NUMBER := 1;
5 SKIP_ACTION_APPLY CONSTANT NUMBER := 0;
6 SKIP_ACTION_REPLACE CONSTANT NUMBER := -1;
7 SKIP_ACTION_ERROR CONSTANT NUMBER := -2;
8 SKIP_ACTION_NOPRIVS CONSTANT NUMBER := -3;
9
10 -- maximum event records that can be recorded in dba_logstdby_events
11 MAX_EVENTS CONSTANT NUMBER := 2000000000;
12
13 --
14 -- NAME: apply_set
15 --
16 -- DESCRIPTION:
17 -- This procedure sets configuration options
18 --
19 -- PARAMETERS:
20 -- inname - config option (see documentation or validate_set)
21 -- value - value for specified option
22 --
23 -- USAGE NOTES:
24 -- This procedure will return an error if APPLY is active.
25 --
26 -- EXCEPTIONS:
27 -- ora-16104 "invalid Logical Standby option requested"
28 -- ora-16236 "Logical Standby metadata operation in progress"
29 -- ora-16103 "Logical Standby must be stopped to allow this operation"
30 --
31 PROCEDURE apply_set(inname IN VARCHAR,
32 value IN VARCHAR);
33
34
35 --
36 -- NAME: apply_unset
37 --
38 -- DESCRIPTION:
39 -- This procedure sets a configuration option back to its default value
40 --
41 -- PARAMETERS:
42 -- inname - config option (see documentation or validate_set)
43 --
44 -- USAGE NOTES:
45 -- This procedure will return an error if APPLY is active.
46 --
47 -- EXCEPTIONS:
48 -- ora-16104 "invalid Logical Standby option requested"
49 -- ora-16236 "Logical Standby metadata operation in progress"
50 -- ora-16103 "Logical Standby must be stopped to allow this operation"
51 --
52 PROCEDURE apply_unset(inname IN VARCHAR);
53
54
55 --
56 -- NAME: build
57 --
58 -- DESCRIPTION:
59 -- Build a LogMiner dictionary into the redo log steam.
60 -- Captures system catalog metadata from primary database for
61 -- use by Logical Standby while apply redo log changes. This procedure
62 -- also turns on supplemental logging.
63 --
64 -- PARAMETERS:
65 -- none
66 --
67 -- USAGE NOTES:
68 --
69 -- EXCEPTIONS:
70 -- none
71 --
72 PROCEDURE build;
73
74
75 --
76 -- NAME: rebuild
77 --
78 -- DESCRIPTION:
79 -- This procedure is called
80 -- after an error was detected during the LSP1 LogMiner dictionary build.
81 -- Unlike
82 -- normal LogMiner dictionary builds, the lockdown SCN has already been
83 -- determined. This SCN is stored as the FIRST_CHANGE# of a record in
84 -- system.logstdby$history that represents the current log stream. The
85 -- lockdown SCN is simply fetched and supplied to the dictionary gather
86 -- routine. This routine will also attempt to archive SRLs that were
87 -- purposely deferred during activation. These two activities, build and
88 -- SRL archival, must complete in order for reinstatement of standbys
89 --- to be successful. The status of these activities is reflected in the
90 -- REINSTATEMENT_STATUS parameter which can be any of the following values:
91 -- BUILD PENDING, SRL ARCHIVE PENDING, READY, or NOT POSSIBLE. A status of
92 -- BUILD PENDING means that the LogMiner dictionary build is pending. A
93 -- status of SRL ARCHIVE PENDING means that the SRL archival is pending.
94 -- Due to the ordering of this routine, a status of SRL ARCHIVE PENDING also
95 -- implies that a LogMiner dictionary build was successful. A status of
96 -- READY means that reinstatement of standbys is possible. A status of
97 -- NOT POSSIBLE means that reinstatement is not possible. The NOT POSSIBLE
98 -- status will only occur if the LogMiner dictionary build returns a snapshot
99 -- too old error.
100 --
101 -- PARAMETERS:
102 -- none
103 --
104 -- USAGE NOTES:
105 --
106 -- EXCEPTIONS:
107 -- none
108 --
109 PROCEDURE rebuild;
110
111
112 --
113 -- NAME: skip
114 --
115 -- DESCRIPTION:
116 -- This is a stored procedure that inserts a row in the skip table
117 -- according to the data passed in. Used to define filters that
118 -- prevent application of SQL statements by Logical Standby apply.
119 --
120 -- PARAMETERS:
121 -- see documentation
122 --
123 -- USAGE NOTES:
124 -- This procedure will return an error if APPLY is active.
125 --
126 -- EXCEPTIONS:
127 -- ora-16236 "Logical Standby metadata operation in progress"
128 -- ora-16103 "Logical Standby must be stopped to allow this operation"
129 -- ora-16104 "invalid Logical Standby option requested"
130 --
131 SUBTYPE CHAR1 IS CHAR(1);
132 PROCEDURE skip(stmt IN VARCHAR2,
133 schema_name IN VARCHAR2 DEFAULT NULL,
134 object_name IN VARCHAR2 DEFAULT NULL,
135 proc_name IN VARCHAR2 DEFAULT NULL,
136 use_like IN BOOLEAN DEFAULT TRUE,
137 esc IN CHAR1 DEFAULT NULL);
138
139
140 --
141 -- NAME: skip_error
142 --
143 -- DESCRIPTION:
144 -- This is a stored procedure that inserts a row into the
145 -- skip table according to the data passed in. Used to tell Logical
146 -- Standby apply how to behave when encountering an error.
147 --
148 -- PARAMETERS:
149 -- see documentation
150 --
151 -- USAGE NOTES:
152 -- This procedure will return an error if APPLY is active.
153 --
154 -- EXCEPTIONS:
155 -- ora-16236 "Logical Standby metadata operation in progress"
156 -- ora-16103 "Logical Standby must be stopped to allow this operation"
157 -- ora-16104 "invalid Logical Standby option requested"
158 -- ora-01031 "insufficient privileges"
159 --
160 PROCEDURE skip_error(stmt IN VARCHAR2,
161 schema_name IN VARCHAR2 DEFAULT NULL,
162 object_name IN VARCHAR2 DEFAULT NULL,
163 proc_name IN VARCHAR2 DEFAULT NULL,
164 use_like IN BOOLEAN DEFAULT TRUE,
165 esc IN CHAR1 DEFAULT NULL);
166
167
168 --
169 -- NAME: skip_transaction
170 --
171 -- DESCRIPTION:
172 -- This is a stored procedure that inserts a row into the
173 -- skip transaction table according to the data passed in.
174 -- Used to tell Logical Standby to skip a particular txn.
175 --
176 -- PARAMETERS
177 -- xid
178 --
179 -- USAGE NOTES:
180 -- This procedure will return an error if APPLY is active.
181 -- WARNING: be sure skipping of this transaction will not affect
182 -- applying future transactions.
183 --
184 -- EXCEPTIONS:
185 -- ora-16236 "Logical Standby metadata operation in progress"
186 -- ora-16103 "Logical Standby must be stopped to allow this operation"
187 -- ora-01031 "insufficient privileges"
188 --
189 PROCEDURE skip_transaction(xidusn_p IN NUMBER,
190 xidslt_p IN NUMBER,
191 xidsqn_p IN NUMBER);
192
193
194 --
195 -- NAME: unskip
196 --
197 -- DESCRIPTION:
198 -- This is a stored procedure that deletes a row from the
199 -- skip table according to the data passed in. Negates effects
200 -- from skip procedure.
201 --
202 -- PARAMETERS
203 -- see documentation
204 --
205 -- USAGE NOTES:
206 -- This procedure will return an error if APPLY is active.
207 --
208 -- EXCEPTIONS:
209 -- ora-16236 "Logical Standby metadata operation in progress"
210 -- ora-16103 "Logical Standby must be stopped to allow this operation"
211 -- ora-16104 "invalid Logical Standby option requested"
212 -- ora-01031 "insufficient privileges"
213 --
214 PROCEDURE unskip(stmt IN VARCHAR2,
215 schema_name IN VARCHAR2 DEFAULT NULL,
216 object_name IN VARCHAR2 DEFAULT NULL);
217
218
219 --
220 -- NAME: unskip_error
221 --
222 -- DESCRIPTION:
223 -- This is a stored procedure that deletes a row from the
224 -- skip table according to the data passed in. Negates effects
225 -- from skip_error procedure.
226 --
227 -- PARAMETERS:
228 -- see documentation
229 --
230 -- USAGE NOTES:
231 -- This procedure will return an error if APPLY is active.
232 --
233 -- EXCEPTIONS:
234 -- ora-16236 "Logical Standby metadata operation in progress"
235 -- ora-16103 "Logical Standby must be stopped to allow this operation"
236 -- ora-16104 "invalid Logical Standby option requested"
237 -- ora-01031 "insufficient privileges"
238 --
239 PROCEDURE unskip_error(stmt IN VARCHAR2,
240 schema_name IN VARCHAR2 DEFAULT NULL,
241 object_name IN VARCHAR2 DEFAULT NULL);
242
243
244 --
245 -- NAME: unskip_transaction
246 --
247 -- DESCRIPTION:
248 -- This is a stored procedure that deletes a row from the
249 -- skip transaction table according to the data passed in.
250 -- Negates effects from skip_transaction procedure.
251 --
252 -- PARAMETERS
253 -- xid
254 --
255 -- USAGE NOTES:
256 -- This procedure will return an error if APPLY is active.
257 --
258 -- EXCEPTIONS:
259 -- ora-16236 "Logical Standby metadata operation in progress"
260 -- ora-16103 "Logical Standby must be stopped to allow this operation"
261 -- ora-16100 "not a valid Logical Standby database"
262 -- ora-01031 "insufficient privileges"
263 --
264 PROCEDURE unskip_transaction(xidusn_p IN NUMBER,
265 xidslt_p IN NUMBER,
266 xidsqn_p IN NUMBER);
267
268
269 --
270 -- NAME: instantiate_table
271 --
272 -- DESCRIPTION:
273 -- This procedure creates and populates a table and its
274 -- children from a table existing on a source database as
275 -- accessed via the dblink parameter.
276 --
277 -- If the table currently exists in the target database,
278 -- it will be dropped. Any constraint or index that exists
279 -- on the source table will also be created but physical
280 -- storage characteristics will be omitted.
281 --
282 -- PARAMETERS:
283 -- table_name Name of table to be instantiated
284 -- schema_name Schema name in which the table resides
285 -- dblink link to database in which the table resides
286 --
287 -- USAGE NOTES:
288 -- This procedure should be called on a logical standby database
289 -- whenever a table needs to be re-instantiated. If the apply
290 -- engine is currently running, and exception will be raised.
291 -- The target table will be dropped first if it currently exists.
292 -- Uses datapump so datapump rules apply.
293 --
294 -- EXCEPTIONS:
295 -- ora-16236 "Logical Standby metadata operation in progress"
296 -- ora-16103 "Logical Standby must be stopped to allow this operation"
297 -- ora-16277 "specified table is not supported by logical standby"
298 -- ora-16278 "specified table has a multi-object skip rule defined"
299 -- ora-00942 "Table does not exist (on primary)"
300 -- ora-16308 "specified table has extended data type support"
301 --
302 PROCEDURE instantiate_table(schema_name IN VARCHAR2,
303 table_name IN VARCHAR2,
304 dblink IN VARCHAR2);
305
306 --
307 -- NAME: set_tablespace
308 --
309 -- DESCRIPTION:
310 -- This procedure changes the tablespace used to store logical
311 -- standby metadata. By default this data is stored in SYSAUX.
312 -- Users have the option to move the metadata to another schema
313 -- provided APPLY is not running when the data is moved.
314 --
315 -- PARAMETERS:
316 -- new_tablespace Name of tablespace to hold metadata
317 --
318 -- USAGE NOTES:
319 -- This procedure will return an error if APPLY is active.
320 --
321 -- EXCEPTIONS:
322 -- ora-16236 "Logical Standby metadata operation in progress"
323 -- ora-16103 "Logical Standby must be stopped to allow this operation"
324 --
325 PROCEDURE set_tablespace(new_tablespace IN VARCHAR2);
326
327
328 --
329 -- NAME: purge_session
330 --
331 -- DESCRIPTION:
332 -- This procedure purges the session metadata up to the latest
333 -- safe purge scn. This procedure can be called while the logical
334 -- standby apply is running
335 --
336 -- PARAMETERS:
337 -- NONE
338 --
339 -- USAGE NOTES:
340 -- This procedure can be called while apply is running
341 --
342 -- EXCEPTIONS:
343 -- ora-01309 "invalid session"
344 --
345 PROCEDURE purge_session;
346
347
348 --
349 -- NAME: prepare_for_new_primary
350 --
351 -- DESCRIPTION:
352 --
353 -- This procedure is called to ready the local logical standby
354 -- for configuration with a failed-over primary. This routine will:
355 --
356 -- 1. Ensure the primary is only one role transition ahead of us.
357 -- 2. Ensure we haven't applied too far (i.e. flashback required).
358 -- 3. Purge log$ of all logfiles that need to be obtained from the
359 -- new primary's copy (a.k.a terminal logs).
360 --
361 -- If the new primary was formerly a physical standby, the user should
362 -- issue a START LOGICAL STANDBY APPLY. If the new primary was formerly
363 -- a logical standby, the user must ensure to copy and re-register the
364 -- terminal logs, as indicated in the alert.log, and issue a START
365 -- LOGICAL STANDBY APPLY NEW PRIMARY. This DDL will ensure the apply
366 -- runs in the appropriate apply mode.
367 --
368 -- PARAMETERS:
369 -- former_standby_type -- Type of standby the new primary was activated
370 -- from. Valid values are 'PHYSICAL' | 'LOGICAL'
371 -- dblink -- dblink to the activated primary
372 --
373 -- USAGE NOTES:
374 -- NONE
375 --
376 -- EXCEPTIONS:
377 -- NONE
378 --
379 PROCEDURE prepare_for_new_primary (former_standby_type IN VARCHAR2,
380 dblink IN VARCHAR2);
381
382 --
383 -- NAME: map_primary_scn
384 --
385 -- DESCRIPTION:
386 -- Return conservative scn on standby for specified scn on primary
387 --
388 -- PARAMETERS:
389 -- scn -- Valid scn on primary
390 --
391 -- USAGE NOTES:
392 -- Return an scn on the standby that predates the supplied scn
393 -- from the primary by at least 5 minutes. This is a safe scn to
394 -- flashback the standby to prior to the scn to which the primary
395 -- was flashed back
396 --
397 -- EXCEPTIONS:
398 -- -20001, primary scn before mapped range
399 -- -20002, scn mapping requires PRESERVE_COMMIT_ORDER true
400 --
401
402 FUNCTION map_primary_scn(primary_scn NUMBER) RETURN NUMBER;
403
404 -- NAME: db_is_logstdby
405 --
406 -- DESCRIPTION:
407 -- Function returns 1 if called from a Logical Standby database
408 -- and 0 otherwise.
409 --
410 -- PARAMETERS:
411 -- USAGE NOTES:
412 -- EXCEPTIONS:
413 --
414 FUNCTION db_is_logstdby RETURN BINARY_INTEGER;
415
416 --
417 -- NAME: is_apply_server
418 --
419 -- DESCRIPTION:
420 -- Functions returns TRUE/FALSE on whether called from apply process
421 --
422 -- PARAMETERS:
423 --
424 -- USAGE NOTES:
425 -- Needed for standby trigger support
426 --
427 -- EXCEPTIONS:
428 --
432 -- Name: eds_add_table - Add Trigger-Based Support for EDS table
429 FUNCTION is_apply_server RETURN BOOLEAN;
430
431 --
433 --
434 -- Description:
435 -- Feature procedure for Extended Datatype Support on logical standby.
436 -- By calling this procedure on the primary first and then the standby,
437 -- tables with extended datatypes can be supported on a logical standby.
438 --
439 -- Parameters:
440 -- table_owner (IN) owner of the table
441 -- table_name (IN) table name to support
442 -- p_dblink (IN) db link to the primary
443 --
444 -- Usage:
445 -- Call on primary first, then on standby. It creates a shadow table and
446 -- 2 triggers; one on the base table and one on the shadow table. On the
447 -- standby it must be called with a dblink to the primary which it will
448 -- use for dictionary queries and for instantiate_table.
449 --
450 PROCEDURE eds_add_table(
451 table_owner IN varchar2,
452 table_name IN varchar2,
453 p_dblink IN varchar2 default NULL);
454
455
456 --
457 -- Name: eds_remove_table - Remove Trigger-Based Support for EDS table
458 --
459 -- Description:
460 -- Feature procedure for Extended Datatype Support on logical standby.
461 -- This can be invoked on the primary or the standby. If invoked on the
462 -- primary, its actions will be replicated by way of an AUTO pragma.
463 -- If invoked from the standby it will only drop EDS on that standby.
464 --
465 -- Parameters:
466 -- table_owner (IN) owner of the table
467 -- table_name (IN) table name to support
468 --
469 -- Usage:
470 --
471 PROCEDURE eds_remove_table(
472 table_owner IN varchar2,
473 table_name IN varchar2);
474
475 --
476 -- Name: eds_evolve_automatic - EDS Automatic Evolve
477 --
478 -- Description:
479 -- This procedure enables or disables the automated EDS evolve
480 -- infrastructure whereby EDS objects are automatically evolved
481 -- in response to DDL done on an EDS-maintained table.
482 --
483 -- Parameters:
484 -- options (IN) 'ENABLE' - enables auto-evolve
485 -- 'DISABLE' - disables auto-evolve
486 -- Usage:
487 -- Should be called once prior to first eds_add_table call to enable
488 -- automatic evolve across DDLs.
489 --
490 PROCEDURE eds_evolve_automatic(
491 options IN VARCHAR2);
492
493 --
494 -- Name: eds_evolve_manual - EDS Manual Evolve
495 --
496 -- Description:
497 -- This procedure should be called if the user chooses to manually
498 -- evolve his EDS infrastructure across a DDL on the EDS-maintained
499 -- table. The automated evolve facility uses this procedure itself.
500 --
501 -- Parameters:
502 -- options (IN) 'START' - starts an evolve operation
503 -- 'FINISH' - finishes an evolve operation
504 -- 'CANCEL' - cancels an evolve operation
505 -- table_owner (IN) owner of the table
506 -- table_name (IN) name of the table
507 --
508 -- Usage:
509 -- Use this procedure only if NOT using the automated evolve facility
510 -- (eds_evolve_automatic). If DDL must be done on an EDS-maintained
511 -- table, first call this procedure with options=>'START', then perform
512 -- the DDL, and finally call this procedure with options=>'FINISH'.
513 -- If this was called with the START option already and the user wishes
514 -- to cancel the evolve, call with options=>'CANCEL'.
515 --
516 PROCEDURE eds_evolve_manual(
517 options IN VARCHAR2,
518 table_owner IN VARCHAR2,
519 table_name IN VARCHAR2);
520
521 END dbms_logstdby;