1 PACKAGE dbms_gsm_pooladmin AS
2
3
4 --*****************************************************************************
5 -- Package Public Types
6 --*****************************************************************************
7
8
9 -----------------
10 -- Name list type
11 -----------------
12 TYPE name_list_type IS TABLE OF varchar2(30) index by binary_integer;
13
14
15 --*****************************************************************************
16 -- Package Public Constants
17 --*****************************************************************************
18
19 prv_key constant number := 0;
20 pub_key constant number := 1;
21 prk_enc_str constant number := 2;
22
23
24 --*****************************************************************************
25 -- Package Public Exceptions
26 --*****************************************************************************
27
28
29
30 --*****************************************************************************
31 -- Package Public Procedures
32 --*****************************************************************************
33
34 --*****************************************************************************
35 -- Package private constants
36 --*****************************************************************************
37 -- Action parameter
38 logical constant number := 1;
39 physical constant number := 2;
40
41 -- Force parameter
42 force_off constant number := 0;
43 force_on constant number := 1;
44
45 -- gen_aq_notification parameter
46 gen_aq_off constant number := 0;
47 gen_aq_on constant number := 1;
48
49 -------------------------------------------------------------------------------
50 --
51 -- PROCEDURE changeServiceState
52 --
53 -- Description:
54 -- update service state, sends notification with new service state
55 --
56 -- Parameters:
57 -- service_name: The name of the service to check.
58 -- database_pool_name: The database pool to check.
59 -- database_name: The db unique name of the database.
60 -- new_state: New state of service.
61 -- gen_notification: Send AQ notification
62 --
63 --
64 -- Returns:
65 --
66 -- Notes:
67 --
68 -------------------------------------------------------------------------------
69 PROCEDURE changeServiceState( service_name IN varchar2,
70 pool_name IN varchar2,
71 db_name IN varchar2,
72 new_state IN varchar2,
73 gen_notification IN number default 0);
74
75 -------------------------------------------------------------------------------
76 --
77 -- PROCEDURE addBrokerConfig
78 --
79 -- Description:
80 -- Makes a database pool a Data Guard broker configuration.
81 --
82 -- Parameters:
83 -- db_unique_name: db_unique name for the primary
84 -- database in a Data Guard broker
85 -- configuration.
86 -- database_pool_name: The name of the database pool.
87 -- database_connect_string: Connect string for the database.
88 -- password: Encrypted password for the database.
89 -- region: Region in which to put the databases.
90 -- num_standbys: The number of standby databases to
91 -- reserve for the broker config.
92 --
93 -- Notes:
94 -- addBrokerConfig is implemented as follows:
95 -- 1. GSMCTL invokes this routine and it:
96 -- a. adds the primary database entry to the catalog with status "I"
97 -- b. assigns the database a unique number and reserves a range of
98 -- numbers for the standbys
99 -- c. generates a GSM change message indicating that an Add
100 -- Brokerconfig has been done. Included in the message is the
101 -- number of database id's reserved for the config
102 -- (-num_standbys S) [Note that this number will be greater than
103 -- the input parameter "num_standbys" to this routine to allow for
104 -- the addition of new standbys during the processing of this
105 -- command], and the number of instances reserved for
106 -- each database in the config (-num_instances I).
107 -- 2. The Master GSM is notified of the change and then:
108 -- a. modifies the primary database to become part of the cloud
109 -- b. invokes addDatabaseDone to clear the "I" status for the
110 -- primary database and optionally add the scan address and
111 -- ons port for the primary database
112 -- c. queries the primary for the names of all the standbys
113 -- d. modifies the standbys to become part of the cloud
114 -- e. invokes addDatabaseInternal for each standby to add the
115 -- standby database to the catalog, set its status appropriately,
116 -- optionally add the database's scan and ons port, and
117 -- generate a 'DatabaseDone' AQ notification. All the GSMs
118 -- process this message and update their internal data structures
119 -- accordingly.
120 --
121 -- database_pool_name can be NULL if there is only one database pool
122 -- in the cloud. In which case the command will default to that pool.
123 --
124 -- If region_name is NULL and there is a single region defined, then
125 -- the primary database is put into that region. If more than one
126 -- region is defined, then the database will be given a NULL region.
127 -------------------------------------------------------------------------------
128
129 PROCEDURE addBrokerConfig( db_unique_name IN varchar2,
130 database_pool_name IN varchar2 default NULL,
131 database_connect_string IN varchar2,
132 password IN varchar2 default NULL,
133 region IN varchar2 default NULL,
134 num_standbys IN number default 1,
135 instances IN number default NULL,
136 encpassword IN RAW default NULL);
137
138
139 -- TODO: make num_standbys required when GSM code has been modified?
140 -------------------------------------------------------------------------------
141 --
142 -- PROCEDURE removeBrokerConfig
143 --
144 -- Description:
145 -- Removes entire broker configuration (all databases and services)
146 --
147 -- Parameters:
148 --
149 -- database_pool_name: The name of the database pool.
150 -- action: logical or physical;
151 -- logical will update as removed (first phase)
152 -- physical will remove database records
153 -- gen_aq_notification gen_aq_on or gen_aq_off
154 -- determines if AQ notification is generated
155 --
156 -- Notes:
157 -- This procedure will remove all databases and services for the provided
158 -- database pool after verifying that it is a broker configuration. The
159 -- "force" option is used for databases and services since the operation
160 -- is not yet recoverable; the assumption is that all databases and services
161 -- must eventually be removed. WARNING: this operaion is not reversible and
162 -- complete removal must be completed once initiated.
163 -- TODO: recover this somehow if it goes wrong in GSM?
164 -------------------------------------------------------------------------------
165 PROCEDURE removeBrokerConfig(database_pool_name IN varchar2,
166 action IN number default logical,
167 gen_aq_notification IN number default gen_aq_on);
168 -------------------------------------------------------------------------------
169 --
170 -- PROCEDURE syncBrokerConfig
171 --
172 -- Description:
173 -- Sync GSM's version of broker configuration with latest updates
174 --
175 -- Parameters:
176 --
177 -- database_pool_name: The name of the database pool.
178 -- database_name: Name of primary database in BC (optional)
179 --
180 -- Notes:
181 -- This function simply notifies GSM through AQ and gsm_requests that the
182 -- broker configuration needs to be synced. GSM will do all the work.
183 -------------------------------------------------------------------------------
184 PROCEDURE syncBrokerConfig ( database_pool_name IN varchar2 DEFAULT NULL,
185 database_name IN varchar2 DEFAULT NULL);
186
187 -------------------------------------------------------------------------------
188 --
189 -- PROCEDURE addDatabaseInternal
190 --
191 -- Description:
192 -- Adds a new database to a broker configuration.
193 --
194 -- Parameters:
195 -- db_unique_name: db_unique name for the database to add
196 -- database_pool_name: The name of the database pool.
197 -- database_connect_string: Connect string for the database.
198 -- password: Encrypted password for the database.
199 -- region: Region in which to put the database.
200 -- status: Configuration status to give the database
201 -- db_num: The database number assigned to this
202 -- standby by the GSM. If NULL then the
203 -- this routine will assign the database
204 -- a number.
205 -- scan_address: If the database is RAC, its SCAN address
206 -- ons_port: If the database is RAC, its ONS port
207 -- hostname hostname or IP address for VNCR
208 -- db_vers GDS internal version of the database
209 -- prmdb_name name of primary DB to cp password from
210 --
211 -- Notes:
212 -- This is the final step of addBrokerConfig(). The master GSM invokes
213 -- this routine to add each standby database to the cloud catalog.
214 --
215 -- The routine will generate a "database done" AQ notification.
216 --
217 -- database_pool_name can be NULL if there is only one database pool
218 -- in the cloud. In which case the command will default to that pool.
219 --
220 -- If region_name is NULL and there is a single region defined, then
221 -- the primary database is put into that region. If more than one
222 -- region is defined, then the database will be given a NULL region.
223 -------------------------------------------------------------------------------
224
225 PROCEDURE addDatabaseInternal( db_unique_name IN varchar2,
226 database_pool_name IN varchar2 default NULL,
227 database_connect_string IN varchar2,
231 db_num IN number default NULL,
228 password IN varchar2 default NULL,
229 region IN varchar2 default NULL,
230 status IN char,
232 -- TODO: make db_num mandatory after GSM code is modified ?
233 scan_address IN varchar2 default NULL,
234 ons_port IN number default NULL,
235 hostname IN varchar2 default NULL,
236 db_vers IN number default NULL,
237 prmdb_name IN varchar2 default NULL,
238 db_type IN char default 'U');
239
240 -------------------------------------------------------------------------------
241 --
242 -- PROCEDURE addDatabase
243 --
244 -- Description:
245 -- Adds a database to a region and a database pool.
246 --
247 -- Parameters:
248 -- db_unique_name: db_unique name for the database to
249 -- add to the pool.
250 -- database_pool_name: The name of the database pool.
251 -- database_connect_string: Connect string for the database.
252 -- password: Encrypted password for the database.
253 -- region: Region in which to put the database.
254 -- instances Number of instances reserved.
255 -- cpu_thresh CPU threshold value.
256 -- srlat_thresh Single read latency threshold.
257 --
258 -- Notes:
259 -- The "Add Database" command is implemented in three phases:
260 -- 1. GSMCTL invokes this routine and it:
261 -- a. Adds the database entry to the cloud catalog
262 -- b. Set the status to "I" (incomplete)
263 -- c. Generates a GSM change message indicating that an
264 -- Add Database has been done
265 -- 2. The Master GSM is notified of the change and then modifies
266 -- each database to become part of the cloud. This involves adding
267 -- some GSM related hidden parameters.
268 -- 3. The Master GSM then invokes addDatabaseDone() to clear the "I"
269 -- status for the database, optionally add the scan address and ons
270 -- port to the database's catalog entry, and generate a GSM change
271 -- message indicating that the add of the database completed. All
272 -- the GSMs process this message and update their internal data
273 -- structures accordingly.
274 --
275 -- database_pool_name can be NULL if there is only one database pool
276 -- in the cloud. In which case the command will default to that pool.
277 --
278 -- region_name can be NULL if there is only one region in the cloud.
279 -- In which case the command will default to that region.
280 -------------------------------------------------------------------------------
281
282 PROCEDURE addDatabase( db_unique_name IN varchar2,
283 database_pool_name IN varchar2 default NULL,
284 database_connect_string IN varchar2,
285 password IN varchar2 default NULL,
286 region IN varchar2 default NULL,
287 instances IN number default NULL,
288 cpu IN number default NULL,
292
289 srlat IN number default NULL,
290 encpassword IN RAW default NULL);
291
293
294 -------------------------------------------------------------------------------
295 --
296 -- PROCEDURE modifyDatabase
297 --
298 -- Description:
299 -- modifies database database pool.
300 --
301 -- Parameters:
302 -- db_unique_name: db_unique name
303 -- database_pool_name: The name of the database pool.
304 -- database_connect_string: Connect string for the database.
305 -- password: Encrypted password for the database.
306 -- region: Region in which to put the database.
307 -- scan: Scan address
308 -- ons: ONS port
309 -- cpu_thresh CPU threshold value.
310 -- srlat_thresh Single read latency threshold.
311 --
312 -- Notes:
313 -- This procedure is used for manual correction of db parameters
314 -------------------------------------------------------------------------------
315 PROCEDURE modifyDatabase ( db_unique_name IN varchar2,
316 database_pool_name IN varchar2 default NULL,
317 database_connect_string IN varchar2 default NULL,
318 password IN varchar2 default NULL,
319 region IN varchar2 default NULL,
320 scan IN varchar2 default NULL,
321 ons IN number default NULL,
322 cpu IN number default NULL,
323 srlat IN number default NULL,
324 encpassword IN RAW default NULL);
325
326
327 -------------------------------------------------------------------------------
328 --
329 -- PROCEDURE addDatabaseDone
330 --
331 -- Description:
332 -- Marks the end of "add database" processing.
333 --
334 -- Parameters:
335 -- db_unique_name: db_unique name for the database
336 -- database_pool_name: The name of the database pool.
337 -- status: Configuration status to give the database.
338 -- scan_address: If the database is RAC, its SCAN address
339 -- ons_port: If the database is RAC, its ONS port
340 -- hostname host name or IP for autoVNCR
341 -- db_vers numeric cloud database version
342 -- db_type type of database
343 --
344 -- Notes:
345 -- This is the final step of addDatabase(). The master GSM invokes
346 -- this routine to complete "add database" processing. The status,
347 -- scan_address, and ons_port of the database will be updated. In
348 -- addition, a "database done" AQ notification will be made.
349 -------------------------------------------------------------------------------
350 PROCEDURE addDatabaseDone( db_unique_name IN varchar2,
351 database_pool_name IN varchar2,
352 scan_address IN varchar2 default NULL,
356 db_type IN char default 'U' );
353 ons_port IN number default NULL,
354 hostname IN varchar2 default NULL,
355 db_vers IN number default NULL,
357 -------------------------------------------------------------------------------
358 --
359 -- PROCEDURE updateDatabaseStatus
360 --
361 -- Description:
362 -- Updates runtime status information for database
363 --
364 -- Parameters:
365 -- db_unique_name: db_unique name for the database
366 -- database_pool_name: The name of the database pool.
367 -- status: Configuration status to give the database.
368 -- db_vers GSM internal DB version
369 --
370 -- Notes:
371 -- The master GSM invokes this routine to notify other GSMS on db status.
372 -- If mastership will be switched, new master will be aware of current db
373 -- status
374 -------------------------------------------------------------------------------
375 PROCEDURE updateDatabaseStatus ( db_unique_name IN varchar2,
376 database_pool_name IN varchar2 default NULL,
377 status IN char DEFAULT NULL,
378 db_vers IN NUMBER DEFAULT NULL );
379
380 -------------------------------------------------------------------------------
381 --
382 -- PROCEDURE removeDatabase
383 --
384 -- Description:
385 -- Remove a database from a database pool.
386 --
387 -- Parameters:
388 -- db_unique_name: db_unique name for the database to
389 -- add to the pool.
390 -- database_pool_name: The name of the database pool.
391 -- action: logical: Mark the database entry as removed
392 -- (also generate change message)
393 -- physical: Physically remove the database entry
394 -- (but do not generate a change message)
395 -- force: Interactive user supplied the "-force"
396 -- parameter.
397 -- gen_aq_notification: Only valid if action = "physical"
398 -- gen_aq_off: Don't generate an AQ notification
399 -- gen_aq_on: Generate an AQ notification
400 --
401 -- Notes:
402 -- The "Remove Database" command is implemented in two phases:
403 -- 1. GSMCTL invokes this routine and it: sets the status for the
404 -- database to "R" (for removed), and generates a GSM change
405 -- message indicating that a Remove Database was done.
406 -- 2. The Master GSM is notified of the change log entry and then
407 -- calls this routine again to physically remove the database
408 -- entry from the catalog. By deferring the removal of the catalog
409 -- entry, the Master GSM is able to use the database credentials in
410 -- the catalog to make the necessary changes on the database.
411 --
412 -- This routine may also be called with "physical" flag when an Add
413 -- Database command fails in order to drop the catalog database entry
414 -- for the database that could not be added. In that case,
415 -- "gen_aq_notification" will be set to "gen_aq_off".
416 --
417 -- No checking is done other than verifying that the database is in the
418 -- catalog. Entries from service_preferred_available are also removed.
419 --
420 -- database_pool_name can be NULL if there is only one database pool
421 -- in the cloud. In which case the command will default to that pool.
422 -------------------------------------------------------------------------------
423
424
425
426
427 PROCEDURE removeDatabase( db_unique_name IN varchar2,
428 database_pool_name IN varchar2 default NULL,
429 action IN number default logical,
430 force IN number default NULL,
431 gen_aq_notification IN number default gen_aq_on );
432
433
434
435 -------------------------------------------------------------------------------
436 --
437 -- PROCEDURE addService
438 --
439 -- Description:
440 -- Add a service to a database pool.
441 --
442 -- Parameters:
443 --
444 -- Note: all constants are defined in package dbms_gwm_common.
445 --
446 -- database_pool_name: Name of the database pool hosting the service
447 --
448 -- service_name: Name of the service. If the name has a "." in it
449 -- then this name will also be used as the network
450 -- service name. Otherwise the network service name
454 -- preferred_all: Define which databases should host the service.
451 -- will be:
452 -- <service_name>.<database_pool_name>.<cloud_name>
453 --
455 -- Allowed values are:
456 -- select_dbs - will select preferred and available databases in
457 -- "preferred_dbs" and "available_dbs".
458 -- prefer_all_dbs - all databases in the pool are "preferred"
459 -- databases for this service.
460 --
461 -- preferred_dbs: list of database names to be preferred databases for
462 -- the service. "preferred_all" should be set to
463 -- "select_dbs".
464 --
465 -- available_dbs: list of database names to be available databases for
466 -- the service. "preferred_all" should be set to
467 -- "select_dbs".
468 --
469 -- svc_locality: Specify the degree of service afinity to a region.
470 -- Allowed values are:
471 -- service_anywhere - A client connection request is routed to the
472 -- the best database that can satisfy the CLB
473 -- goal for the requested service.
474 -- service_local_only - A client connection request is routed to the
475 -- best database in the client region that can
476 -- satisfy the CLB goal for the requested
477 -- service.
478 --
479 -- region_failover: This policy is in effect when "svc_locality" is
480 -- set to "service_local_only". Allowed values are:
481 -- region_failover_on - If there are no databases in the local
482 -- region offering a service, instead of denying
483 -- the client request, it is forwarded to the
484 -- best database in another region that has
485 -- the requested service started.
486 -- region_failover_off - Client connection requests are not
487 -- forwarded outside the region.
488 --
489 -- db_role: Specifies the role a database must have before the service
490 -- can be started on it. This parameter is only valid for
491 -- services in a Data Guard broker configuration.
492 -- Allowed values are:
493 -- db_role_none - the service can be started on a database with
494 -- any role.
495 -- db_role_primary - the service will only be started on a
496 -- database with primary role.
497 -- db_role_phys_stby - the service will only be started on a
498 -- physical standby database.
499 -- db_role_log_stby - the service will only be started on a
500 -- logical standby database.
501 -- db_role_snap_stby - the service will only be started on a
502 -- snapshot standby database.
503 --
504 -- failover_primary: Enables a service for failover to a primary
505 -- database. Applicable to only services with
506 -- "db_role" = "db_role_phys_stby".
507 -- Allowed values are:
508 -- failover_primary_off - turns off failover to the primary
509 -- failover_primary_on - turns on failover to the primary
510 --
511 -- rlb_goal: Run-time load balancing goal
512 -- Allowed values are:
513 -- rlb_goal_none - Turns off run time load balancing.
514 -- rlb_goal_service_time
518 -- Allowed values are:
515 -- rlb_goal_throughput
516 --
517 -- clb_goal: Connection time load balancing goal
519 -- clb_goal_none - Turns off connection load balancing.
520 -- clb_goal_short
521 -- clb_goal_long
522 --
523 -- ha_notification: HA notifications for this service
524 -- Allowed values are:
525 -- ha_notification_off
526 -- ha_notification_on
527 --
528 -- taf_policy: TAF policy specification.
529 -- Allowed values are:
530 -- taf_none
531 -- taf_basic
532 -- taf_preconnect
533 --
534 -- restart_policy: Management policy.
535 -- Allowed values are:
536 -- policy_manual
537 -- policy_automatic
538 --
539 -- distr_trans: Enables distributed transaction processing.
540 -- Allowed values are:
541 -- dtp_off
542 -- dtp_on
543 --
544 -- lag: Specifies if and how much lag is allowed for this service.
545 -- Allowed values are:
546 -- any_lag - any lag is tolerated for this service.
547 -- specified_lag - the lag specified in "max_lag" is the
548 -- lag tolerated for this service. This
549 -- parameter is only valid for services
550 -- in a Data Guard broker configuration.
551 --
552 -- max_lag: maximum lag if lag = "specified_lag"
553 --
554 -- TAF parameters:
555 -- failover_method: TAF failover method. Allowed values are:
556 -- failover_none
557 -- failover_basic
558 --
559 -- failover_type: TAF failover type. Allowed values are:
560 -- failover_type_none
561 -- failover_type_session
562 -- failover_type_select
563 -- failover_type_transact -
564 --
565 -- failover_retries: TAF failover retries
566 --
567 -- failover_delay: TAF failover delay.
568 --
569 -- edition: database edition
570 --
571 -- pdb: plugable database id
572 --
573 -- Parameters for transaction continuity:
574 -- commit_outcome: Allowed values are:
575 -- commit_outcome_off
576 -- commit_outcome_on
577 --
578 -- retention_timeout:
579 --
580 -- replay_initiation_timeout:
581 --
582 -- session_state_consistency: Allowed values are:
583 -- session_state_static
584 -- session_state_dynamic
585 --
586 -- sql_translation_profile: Directs how to interpret non-Oracle SQL
587 --
588 -- Notes:
589 -- database_pool_name can be NULL if there is only one database pool
590 -- in the cloud. In which case the command will default to that pool.
591 --
592 -- Status of the service is set to:
593 -- 'P' (stopped) in the "service" table
594 -- 'E' (enabled) in the "service_preferred_available" table
595 --
596 -------------------------------------------------------------------------------
600 dbms_gsm_common.prefer_all_dbs,
597 PROCEDURE addService( database_pool_name IN varchar2 default NULL,
598 service_name IN varchar2,
599 preferred_all IN number default
601 preferred_dbs IN name_list_type,
602 available_dbs IN name_list_type,
603 svc_locality IN number default
604 dbms_gsm_common.service_anywhere,
605 region_failover IN number default
606 dbms_gsm_common.region_failover_off,
607 db_role IN number default
608 dbms_gsm_common.db_role_none,
609 failover_primary IN number default
610 dbms_gsm_common.failover_primary_off,
611 rlb_goal IN number default
612 dbms_gsm_common.rlb_goal_none,
613 clb_goal IN number default
614 dbms_gsm_common.clb_goal_none,
615 ha_notification IN number default
616 dbms_gsm_common.ha_notification_on,
617 taf_policy IN number default
618 dbms_gsm_common.taf_none,
619 restart_policy IN number default
620 dbms_gsm_common.policy_automatic,
621 distr_trans IN number default
622 dbms_gsm_common.dtp_off,
623 lag IN number default
624 dbms_gsm_common.any_lag,
625 max_lag IN number default 0,
626 failover_method IN varchar2 default
627 dbms_gsm_common.failover_none,
628 failover_type IN varchar2 default
629 dbms_gsm_common.failover_type_none,
630 failover_retries IN number default NULL,
631 failover_delay IN number default NULL,
632 edition IN varchar2 default NULL,
633 pdb IN varchar2 default NULL,
634 commit_outcome IN number default NULL,
635 retention_timeout IN number default NULL,
636 replay_initiation_timeout IN number default NULL,
637 session_state_consistency IN varchar2 default NULL,
638 sql_translation_profile IN varchar2 default NULL );
639
640 -------------------------------------------------------------------------------
641 --
642 -- PROCEDURE modifyService
643 --
644 -- Description:
645 -- Modify one or more attributes of a service.
646 --
647 -- Parameters:
651 -- Changes are reflected only in new connections to the service.
648 -- See addService() for a description.
649 --
650 -- Notes:
652 --
653 -- edition and sql_translation_profile parameters may be set to NULL
654 -- by passing literal 'null' to them.
655 --
656 -- database_pool_name can be NULL if there is only one database pool
657 -- in the cloud. In which case the command will default to that pool.
658 -------------------------------------------------------------------------------
659 PROCEDURE modifyService( database_pool_name IN varchar2 default NULL,
660 service_name IN varchar2,
661 svc_locality IN number default NULL,
662 region_failover IN number default NULL,
663 db_role IN number default NULL,
664 failover_primary IN number default NULL,
665 rlb_goal IN number default NULL,
666 clb_goal IN number default NULL,
667 ha_notification IN number default NULL,
668 taf_policy IN number default NULL,
669 restart_policy IN number default NULL,
670 distr_trans IN number default NULL,
671 lag IN number default NULL,
672 max_lag IN number default NULL,
673 failover_method IN varchar2 default NULL,
674 failover_type IN varchar2 default NULL,
675 failover_retries IN number default NULL,
676 failover_delay IN number default NULL,
677 edition IN varchar2 default NULL,
678 pdb IN varchar2 default NULL,
682 session_state_consistency IN varchar2 default NULL,
679 commit_outcome IN number default NULL,
680 retention_timeout IN number default NULL,
681 replay_initiation_timeout IN number default NULL,
683 sql_translation_profile IN varchar2 default NULL,
684 force IN number
685 default dbms_gsm_common.isFalse);
686
687 -------------------------------------------------------------------------------
688 --
689 -- PROCEDURE addServiceToDBs
690 --
691 -- Description:
692 -- Add an existing service to additional preferred and/or available
693 -- databases.
694 --
695 -- Parameters:
696 -- database_pool_name - The database pool in which the service
697 -- is defined.
698 -- service_name - An existing service.
699 -- preferred_dbs - A list of preferred databases to add the
700 -- service to.
701 -- available_dbs - A list of available databases to add the
702 -- service to.
703 --
704 -- Notes:
705 -- It is an error if "preferred_all" is set for the service.
706 --
707 -- At least one preferred or available database should be set.
708 --
709 -- None of the preferred or available databases should already be
710 -- a database for the service.
711 --
712 -- database_pool_name can be NULL if there is only one database pool
713 -- in the cloud. In which case the command will default to that pool.
714 -------------------------------------------------------------------------------
715 PROCEDURE addServiceToDBs( database_pool_name IN varchar2 default NULL,
716 service_name IN varchar2,
717 preferred_dbs IN name_list_type,
718 available_dbs IN name_list_type );
719
720 -------------------------------------------------------------------------------
721 --
722 -- PROCEDURE moveServiceToDB
723 --
724 -- Description:
725 -- Move an existing service from one database to another.
726 --
727 -- Parameters:
731 -- old_db - Database to move service from.
728 -- database_pool_name - The database pool in which the service
729 -- is defined.
730 -- service_name - An existing service.
732 -- new_db - Database to move service to.
733 -- force - User supplied the "force" parameter.
734 --
735 -- Notes:
736 -- The "force" parameter is passed on to the master GSM.
737 --
738 -- The service should not already be defined on the new database.
739 --
740 -- See removeDatabase for definitions for "force" parameter.
741 --
742 -- database_pool_name can be NULL if there is only one database pool
743 -- in the cloud. In which case the command will default to that pool.
744 -------------------------------------------------------------------------------
745 PROCEDURE moveServiceToDB( database_pool_name IN varchar2 default NULL,
746 service_name IN varchar2,
747 old_db IN varchar2,
748 new_db IN varchar2,
749 force IN number default NULL );
750
751 -------------------------------------------------------------------------------
752 --
753 -- PROCEDURE makeDBsPreferred
754 --
755 -- Description:
756 -- Changes the specified databases to preferred databases for the
757 -- service.
758 --
759 -- Parameters:
760 -- database_pool_name - The database pool in which the service
761 -- is defined.
762 -- service_name - An existing service.
763 -- dbs - A list of names of the databases to make
764 -- preferred for the service.
765 -- force - User supplied "force" parameter.
766 -- TODO: what does it mean in this case?
767 --
768 -- Notes:
769 -- The service should already be defined on the databases.
770 --
771 -- The databases should be in the database pool and either not have
772 -- the service defined on them or be available databases for the
773 -- service.
774 --
775 -- It is an error if the service has "preferred_all" set.
776 --
777 -- See removeDatabase for definitions for "force" parameter.
778 --
779 -- database_pool_name can be NULL if there is only one database pool
780 -- in the cloud. In which case the command will default to that pool.
781 -------------------------------------------------------------------------------
782 PROCEDURE makeDBsPreferred( database_pool_name IN varchar2 default NULL,
783 service_name IN varchar2,
784 dbs IN name_list_type,
785 force IN number default NULL );
786
787 -------------------------------------------------------------------------------
788 --
789 -- PROCEDURE modifyServiceConfig
790 --
791 -- Description:
792 -- Changes the set of preferred and available databases for a service.
793 --
794 -- Parameters:
795 -- database_pool_name - The database pool in which the service
796 -- is defined.
797 -- service_name - An existing service.
798 -- preferred_all - Set to dbms_gsm_common.prefer_all_dbs if
799 -- all databases in the pool should be set
800 -- to preferred.
801 -- preferred_dbs - The names of the databases to be set
802 -- preferred for the service.
803 -- available_dbs - The names of the databases to be set
804 -- available for the service.
805 -- force - User supplied "force" parameter.
806 --
807 -- Notes:
808 -- The "force" parameter is passed on to the master GSM.
809 --
813 -- If "prefer_dbs" is set then the current preferred/available list is
810 -- If "preferred_all" is set to "prefer_all_dbs" then "preferred_dbs"
811 -- and "available_dbs" are ignored.
812 --
814 -- cleared and new lists are built based on "preferred_dbs" and
815 -- "available_dbs".
816 --
817 -- See removeDatabase for definitions for "force" parameter.
818 --
819 -- database_pool_name can be NULL if there is only one database pool
820 -- in the cloud. In which case the command will default to that pool.
821 -------------------------------------------------------------------------------
822 PROCEDURE modifyServiceConfig( database_pool_name IN varchar2 default NULL,
823 service_name IN varchar2,
824 preferred_all IN number,
825 preferred_dbs IN name_list_type,
826 available_dbs IN name_list_type,
827 force IN number default NULL );
828 -------------------------------------------------------------------------------
829 --
830 -- FUNCTION getServiceDBParams
831 --
832 -- Description:
833 -- Converts DB paramters in name_list_type to a parameter string.
834 -- Used primarily by GDSCTL/GSM to return parameter strings from
835 -- database object types
836 --
837 -- Parameters:
838 -- dbparam_names - list of parameter names
839 -- dbparam_values - list of parameter values
840 --
841 -- Returns:
842 -- varchar - string containing parameters in NVP format
843 --
844 -- Notes:
845 -------------------------------------------------------------------------------
846 FUNCTION getServiceDBParams (dbparams IN dbparams_list)
847 RETURN varchar2;
848
849 -------------------------------------------------------------------------------
850 --
851 -- FUNCTION getServiceLocalParams
852 --
853 -- Description:
854 -- Converts DB local parameter list types to a parameter string.
855 -- Used primarily by GDSCTL/GSM to return parameter strings from
856 -- database object types
857 --
858 -- Parameters:
859 -- dbparams - list of parameter names
860 -- instances - list of instances
861 --
862 -- Returns:
863 -- varchar - string containing parameters in NVP format
864 --
865 -- Notes:
866 -------------------------------------------------------------------------------
867 FUNCTION getServiceLocalParams (dbparams IN dbparams_list,
868 instances IN instance_list)
869 RETURN varchar2;
870
871 -------------------------------------------------------------------------------
872 --
873 -- PROCEDURE getInstanceString
874 --
875 -- Description:
876 -- returns a list of preferred/available instances in string form
877 --
878 -- Parameters:
879 -- pool_name - The database pool in which the service
880 -- is defined.
881 -- service_name - An existing service.
882 -- database_name - The database on which service is defined
883 -- instance_string - String containing instance list for this
884 -- service in NVP format
885 --
886 --
887 -------------------------------------------------------------------------------
888 PROCEDURE getInstanceString (service_name IN varchar2,
889 pool_name IN varchar2,
890 database_name IN varchar2,
891 instance_string OUT varchar2);
892
893 -------------------------------------------------------------------------------
894 --
895 -- PROCEDURE modifyServiceOnDB
896 --
897 -- Description:
901 -- database_pool_name - The database pool in which the service
898 -- Modifies the attributes of a service specific to a (RAC) database.
899 --
900 -- Parameters:
902 -- is defined.
903 -- service_name - An existing service.
904 -- database_name - The database on which to change the
905 -- service attributes.
906 -- params - A copy of the rest of the parameters
907 -- supplied by the user. Maximum size is
908 -- 1024.
909 -- dbparam_names - list of DB specific parameter names
910 -- dbparam_value - list of matching values for above names
911 -- palist_op - operation for preferred/available list
912 -- 'A' - Add this as new list (old list erased)
913 -- 'M' - Existing list is modified
914 -- 'D' - remove values fom existing list
915 -- preferred_list - list of preferred instances
916 -- available_list - list of available instances
917 --
918 -- Notes:
919 -- The command is implemented in the Master GSM. The catalog database
920 -- just passes the request on to the GSM.
921 --
922 -------------------------------------------------------------------------------
923 PROCEDURE modifyServiceOnDB( database_pool_name IN varchar2 default NULL,
924 service_name IN varchar2,
925 database_name IN varchar2,
926 params IN varchar2 DEFAULT NULL,
927 dbparam_names IN name_list_type
928 DEFAULT CAST(NULL AS name_list_type),
929 dbparam_values IN name_list_type
930 DEFAULT CAST(NULL AS name_list_type),
931 palist_op IN char DEFAULT NULL,
932 preferred_list IN name_list_type
933 DEFAULT CAST(NULL AS name_list_type),
934 available_list IN name_list_type
935 DEFAULT CAST(NULL AS name_list_type),
936 force IN number
937 DEFAULT dbms_gsm_common.isFalse);
938 -------------------------------------------------------------------------------
939 --
940 -- PROCEDURE removeService
941 --
942 -- Description:
943 -- Remove a service from a database pool.
944 --
945 -- Parameters:
946 -- database_pool_name: The name of the database pool.
947 -- service_name: The name of the service.
948 --
949 -- Notes:
950 -- No checking is done at this time other than verifying that the service
951 -- is in the "service" table. Entries are also removed from
952 -- the "service_preferred_available" table.
953 --
954 -- database_pool_name can be NULL if there is only one database pool
955 -- in the cloud. In which case the command will default to that pool.
956 -------------------------------------------------------------------------------
957 PROCEDURE removeService( database_pool_name IN varchar2 default NULL,
958 service_name IN varchar2 );
959
960 -------------------------------------------------------------------------------
961 --
962 -- PROCEDURE removeServiceInternal
963 --
964 -- Description:
965 -- Remove a service from a database pool; called by GSM directly
966 --
967 -- Parameters:
968 -- database_pool_name: The name of the database pool.
969 -- service_name: The name of the service.
970 -- CalledByGSM 1 if called by GSM, 0 otherwise
971 -- gen_aq_notification gen_aq_on or gen_aq_off
972 -- force force removal even if service is running
973 --
974 -- Notes:
975 -------------------------------------------------------------------------------
976 PROCEDURE removeServiceInternal( database_pool_name IN varchar2 default NULL,
977 service_name IN varchar2,
978 CalledByGSM IN number default 0,
979 gen_aq_notification IN number
983 --
980 default gen_aq_on,
981 force IN number default NULL );
982 -------------------------------------------------------------------------------
984 -- PROCEDURE startService
985 --
986 -- Description:
987 -- Start a service in a database pool.
988 --
989 -- Parameters:
990 -- database_pool_name: The name of the database pool.
991 -- service_name: The name of the service.
992 -- database_name: The name of the database (db_unique_name).
993 --
994 -- Notes:
995 -- database_pool_name can be NULL if there is only one database pool
996 -- in the cloud. In which case the command will default to that pool.
997 --
998 -- If service name is NULL then starts all the services in the pool.
999 --
1000 -- If database_name is NULL then starts the service on all databases
1001 -- where the service is defined.
1002 --
1003 -- Status of service is changed to "S" in "service" table
1004 -------------------------------------------------------------------------------
1005 PROCEDURE startService( database_pool_name IN varchar2 default NULL,
1006 service_name IN varchar2 default NULL,
1007 database_name IN varchar2 default NULL );
1008
1009
1010 -------------------------------------------------------------------------------
1011 --
1012 -- PROCEDURE stopService
1013 --
1014 -- Description:
1015 -- Stop a service in a database pool.
1016 --
1017 -- Parameters:
1018 -- database_pool_name: The name of the database pool.
1019 -- service_name: The name of the service.
1020 -- database_name: The name of the database (db_unique_name).
1021 -- force: The interactive user specified the "-force"
1022 -- parameter.
1023 --
1024 -- Notes:
1025 -- See removeDatabase for definitions for "force" parameter.
1026 --
1027 -- database_pool_name can be NULL if there is only one database pool
1028 -- in the cloud. In which case the command will default to that pool.
1029 --
1030 -- If service name is NULL then stops all the services in the pool.
1031 --
1032 -- If database_name is NULL then stops the service on all databases
1033 -- where the service is defined.
1034 --
1035 -- Status of service is changed to "P" in "service" table
1036 -------------------------------------------------------------------------------
1037 PROCEDURE stopService( database_pool_name IN varchar2 default NULL,
1038 service_name IN varchar2 default NULL,
1039 database_name IN varchar2 default NULL,
1040 force IN number default NULL );
1041
1042 -------------------------------------------------------------------------------
1043 --
1044 -- PROCEDURE enableService
1045 --
1046 -- Description:
1047 -- Enable a service in a database pool.
1048 --
1049 -- Parameters:
1050 -- database_pool_name: The name of the database pool.
1051 -- service_name: The name of the service.
1052 -- database_name: The name of the database (db_unique_name).
1053 --
1054 -- Notes:
1055 -- database_pool_name can be NULL if there is only one database pool
1056 -- in the cloud. In which case the command will default to that pool.
1057 --
1058 -- If service name is NULL then enables all the services in the pool.
1059 --
1060 -- If database_name is NULL then enables the service on all databases
1061 -- where the service is defined.
1062 --
1063 -- Status of service is changed to "E" in "service_preferred_available"
1064 -- table.
1065 -------------------------------------------------------------------------------
1066 PROCEDURE enableService( database_pool_name IN varchar2 default NULL,
1067 service_name IN varchar2 default NULL,
1068 database_name IN varchar2 default NULL );
1069
1070 -------------------------------------------------------------------------------
1071 --
1072 -- PROCEDURE disableService
1073 --
1074 -- Description:
1075 -- Disable a service in a database pool.
1076 --
1077 -- Parameters:
1078 -- database_pool_name: The name of the database pool.
1079 -- service_name: The name of the service.
1080 -- database_name: The name of the database (db_unique_name).
1081 --
1082 -- Notes:
1083 -- database_pool_name can be NULL if there is only one database pool
1084 -- in the cloud. In which case the command will default to that pool.
1085 --
1089 -- where the service is defined.
1086 -- If service name is NULL then disables all the services in the pool.
1087 --
1088 -- If database_name is NULL then disables the service on all databases
1090 --
1091 -- Status of service is changed to "E" in "service_preferred_available"
1092 -- table.
1093 -------------------------------------------------------------------------------
1094 PROCEDURE disableService( database_pool_name IN varchar2 default NULL,
1095 service_name IN varchar2 default NULL,
1096 database_name IN varchar2 default NULL );
1097
1098 -------------------------------------------------------------------------------
1099 --
1100 -- PROCEDURE relocateService
1101 --
1102 -- Description:
1103 -- Relocate a service from one database to another.
1104 -- This operation does not change the underlying configuration of the
1105 -- service.
1106 --
1107 -- Parameters:
1108 -- database_pool_name: The name of the database pool.
1109 -- service_name: The name of the service.
1110 -- old_database_name: The name of the database (db_unique_name) from
1111 -- which to move the service.
1112 -- new_database_name: The name of the database (db_unique_name) to
1113 -- which to move the service.
1114 -- force: The interactive user specified the "-force"
1115 -- parameter.
1116 --
1117 -- Notes:
1118 -- The command is implemented in the Master GSM. The catalog database
1119 -- just passes the request on to the GSM.
1120 --
1121 -- See removeDatabase for definitions for "force" parameter.
1122 --
1123 -- database_pool_name can be NULL if there is only one database pool
1124 -- in the cloud. In which case the command will default to that pool.
1125 -------------------------------------------------------------------------------
1126 PROCEDURE relocateService( database_pool_name IN varchar2 default NULL,
1127 service_name IN varchar2,
1128 old_database_name IN varchar2,
1129 new_database_name IN varchar2,
1130 force IN number default NULL );
1131
1132 -------------------------------------------------------------------------------
1133 --
1134 -- PROCEDURE syncDatabase
1135 --
1136 -- Description:
1137 -- Send database sync AQ message to GSM
1138 --
1139 -- Parameters:
1140 -- database_pool_name: The name of the database pool.
1141 -- database_name: Name of database to sync (optional)
1142 --
1143 -- Notes:
1144 -- Null database name will sync all databases in the pool
1145 -------------------------------------------------------------------------------
1146 PROCEDURE syncDatabase ( database_pool_name IN varchar2 DEFAULT NULL,
1147 database_name IN varchar2 DEFAULT NULL);
1148
1149 -------------------------------------------------------------------------------
1150 --
1151 -- PROCEDURE catRollback
1152 --
1153 -- Description:
1154 -- Perform rollback operation on catalog when distributed
1155 -- change has failed on target database(s)
1156 --
1157 -- Parameters:
1158 -- change GSM change request
1159 --
1160 -- Notes:
1161 -- This procedure is called from a trigger when gsm_requests status
1162 -- is updated to 'A' (Aborted) by the GSM server
1163 -------------------------------------------------------------------------------
1164 PROCEDURE catRollback (change IN gsm_change_message,
1165 old_instances IN instance_list);
1166
1167 -------------------------------------------------------------------------------
1168 --
1169 -- PROCEDURE requestDone
1170 --
1171 -- Description:
1172 -- Perform completion actions when change request is done
1173 --
1174 -- Parameters:
1175 -- change GSM change request
1176 --
1177 -- Notes:
1178 -- This procedure is called from a trigger when gsm_requests status
1179 -- is updated to 'D' (Done) or the row is deleted by the GSM server
1180 -------------------------------------------------------------------------------
1181 PROCEDURE requestDone (change IN gsm_change_message);
1182
1183 -------------------------------------------------------------------------------
1184 --
1185 -- PROCEDURE strtolist
1186 --
1187 -- Description:
1188 -- convert varchar2 string to name_list_type
1189 --
1190 -- Parameters:
1194 -- Primarily for unit testing, this function allows us to call
1191 -- lstring - string reprsenting a list
1192 --
1193 -- Notes:
1195 -- PL/SQL functions for complex types from SQLPLUS
1196 -------------------------------------------------------------------------------
1197 FUNCTION strtolist (lstring IN VARCHAR2)
1198 return name_list_type;
1199
1200 -------------------------------------------------------------------------------
1201 --
1202 -- PROCEDURE set_key
1203 --
1204 -- Description:
1205 -- Set PKI Keys and flags
1206 --
1207 -- Parameters:
1208 -- key_name: key type
1209 -- key_value: value of key
1210 --
1211 -------------------------------------------------------------------------------
1212
1213
1214 PROCEDURE set_key(key_type in number,
1215 key_value in RAW);
1216
1217 -------------------------------------------------------------------------------
1218 --
1219 -- PROCEDURE get_key
1220 --
1221 -- Description:
1222 -- get the value of a key by name
1223 --
1224 -- Parameters:
1225 -- key_type: key type
1226 --
1227 -------------------------------------------------------------------------------
1228 FUNCTION get_key(key_type in number) RETURN RAW;
1229
1230
1231 --*****************************************************************************
1232 -- End of Package Public Procedures
1233 --*****************************************************************************
1234
1235 END dbms_gsm_pooladmin;