Rem drv: Rem Rem $Header: osm_post_creation.sql 14-dec-2005.08:40:50 rreilly Exp $ Rem Rem osm_post_creation.sql Rem Rem Copyright (c) 2004, 2005, Oracle. All rights reserved. Rem Rem NAME Rem osm_post_creation.sql Rem Rem DESCRIPTION Rem ASM repository creation Rem Rem Notes Rem Rem MODIFIED (MM/DD/YY) Rem rreilly 12/14/05 - add ECM registry to post creation Rem gsbhatia 07/11/05 - Fix repmgr header Rem chyu 06/28/05 - New repmgr header impl Rem jochen 11/23/04 - jochen_bug-3886779 Rem jochen 11/01/04 - Created Rem SET ECHO ON SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 -- -- Create credential types and sets so that preferred credentials can -- be set for targets from pre-10.2 agents. -- DECLARE l_host_type_cols MGMT_CRED_TYPE_COL_ARRAY := MGMT_CRED_TYPE_COL_ARRAY(); l_db_type_cols MGMT_CRED_TYPE_COL_ARRAY := MGMT_CRED_TYPE_COL_ARRAY(); l_db_host_type_cols MGMT_CRED_TYPE_COL_ARRAY := MGMT_CRED_TYPE_COL_ARRAY(); l_role_col_vals MGMT_CRED_TYPE_COL_VAL_ARRAY := MGMT_CRED_TYPE_COL_VAL_ARRAY(); l_host_set_cols MGMT_CRED_SET_COL_ARRAY := MGMT_CRED_SET_COL_ARRAY(); l_db_set_cols MGMT_CRED_SET_COL_ARRAY := MGMT_CRED_SET_COL_ARRAY(); l_db_host_set_cols MGMT_CRED_SET_COL_ARRAY := MGMT_CRED_SET_COL_ARRAY(); l_host_ref MGMT_CRED_TYPE_REF; l_refs MGMT_CRED_TYPE_REF_ARRAY := MGMT_CRED_TYPE_REF_ARRAY(); BEGIN -- Create the database type DBCreds -- Values for the DBRole type column l_role_col_vals.extend(1); l_role_col_vals(1) := MGMT_CRED_TYPE_COL_VAL('sysdba', 0); -- First, create the type DBCreds l_db_type_cols.extend(3); l_db_type_cols(1) := MGMT_CRED_TYPE_COL_RECORD('DBUserName', 1, null, null, 'UserName', 'CREDS_DATABASE_USERNAME', null); l_db_type_cols(2) := MGMT_CRED_TYPE_COL_RECORD('DBPassword', 0, null, null, 'Password', 'CREDS_DATABASE_PASSWORD', null); l_db_type_cols(3) := MGMT_CRED_TYPE_COL_RECORD('DBRole', 0, null, null, 'Role', 'CREDS_DATABASE_ROLE', l_role_col_vals); MGMT_CREDENTIAL.create_credential_type('osm_instance', 'DBCreds', '1.0', 'Database credentials', 'CREDS_DATABASE_DBCREDS', 'database credentials', null, l_db_type_cols); -- Create the type DBHostCreds l_host_ref := MGMT_CRED_TYPE_REF('HostRef', 'HostCreds', 'host', '2.0', 'host'); l_refs.extend(1); l_refs(1) := l_host_ref; l_db_host_type_cols.extend(2); l_db_host_type_cols(1) := MGMT_CRED_TYPE_COL_RECORD('HostUserName', 1, 'HostRef', 'HostUserName', 'Host UserName', 'CREDS_HOST_USER', null); l_db_host_type_cols(2) := MGMT_CRED_TYPE_COL_RECORD('HostPassword', 0, 'HostRef', 'HostPassword', 'Host Password', 'CREDS_HOST_PASSWORD', null); MGMT_CREDENTIAL.create_credential_type('osm_instance', 'DBHostCreds', '1.0', 'Database host credentials', 'CREDS_DATABASE_DBCREDS', 'database host credentials', l_refs, l_db_host_type_cols); -- Create the credential set DBCredsSYSDBA l_db_set_cols.extend(3); l_db_set_cols(1) := MGMT_CRED_SET_COL_RECORD('DBUserName', 'sysDBAUserName', 'SYSDBA UserName', 'CREDS_SYSDBA_USER', null); l_db_set_cols(2) := MGMT_CRED_SET_COL_RECORD('DBPassword', 'sysDBAPassword', 'SYSDBA Password', 'CREDS_SYSDBA_PASSWORD', null); l_db_set_cols(3) := MGMT_CRED_SET_COL_RECORD('DBRole', 'sysDBARole', 'SYSDBA Role', 'CREDS_SYSDBA_ROLE', null); MGMT_CREDENTIAL.create_credential_set('osm_instance', '1.0', 'DBCredsSYSDBA', MGMT_CREDENTIAL.PREFCRED_SET_USAGE, MGMT_CREDENTIAL.TARGET_SET_CONTEXT_TYPE, null, 'SYSDBA Database Credentials', 'CREDS_DATABASE_SYSDBA', 'DBCreds', l_db_set_cols); -- Create the credential set DBHostCreds l_db_host_set_cols.extend(2); l_db_host_set_cols(1) := MGMT_CRED_SET_COL_RECORD('HostUserName', 'HostUsername', 'Host UserName', 'CREDS_HOST_USER', null); l_db_host_set_cols(2) := MGMT_CRED_SET_COL_RECORD('HostPassword', 'HostPassword', 'Host Password', 'CREDS_HOST_PASSWORD', null); MGMT_CREDENTIAL.create_credential_set('osm_instance', '1.0', 'DBHostCreds', MGMT_CREDENTIAL.PREFCRED_SET_USAGE, MGMT_CREDENTIAL.TARGET_SET_CONTEXT_TYPE, null, 'Database Host Credentials', 'CREDS_HOST', 'DBHostCreds', l_db_host_set_cols); END; / COMMIT; -- ----------------------------------------------- -- Register ECM Policy Metadata for ASM -- ----------------------------------------------- ALTER SESSION SET CONSTRAINTS = DEFERRED; DECLARE metadata_id RAW(16) := SYS_GUID(); BEGIN -- -- Register metadata for osm snapshots -- -- Remove all related metadatas DELETE FROM MGMT_ECM_SNAPSHOT_MD_TABLES WHERE METADATA_ID IN (SELECT METADATA_ID FROM MGMT_ECM_SNAPSHOT_METADATA WHERE TARGET_TYPE = 'osm_instance' AND SNAPSHOT_TYPE = 'oracle_osm'); DELETE FROM MGMT_ECM_SNAPSHOT_METADATA WHERE TARGET_TYPE = 'osm_instance' AND SNAPSHOT_TYPE = 'oracle_osm'; -- Register metadata as loader (KIND='L') metadata INSERT INTO MGMT_ECM_SNAPSHOT_METADATA (SNAPSHOT_TYPE, TARGET_TYPE, KIND, METADATA_ID, UI_NAME, UI_ON, COMPARE_ON, HISTORY_ON, LINK_TABLE_NAME, AFTER_LOAD_PROC_NAME, AFTER_IMPORT_PROC_NAME, METADATA_VERSION, VIEW_URL, COMPARE_URL, HISTORY_URL, SAME_TARGET_COMPARE) VALUES ('oracle_osm', 'osm_instance', 'L', metadata_id, 'ASM Problems', 'Y', 'N', 'N', NULL, NULL, NULL, '1.0', NULL, NULL, NULL, 'H'); -- Register metadata as presentation (KIND='P') metadata INSERT INTO MGMT_ECM_SNAPSHOT_METADATA (SNAPSHOT_TYPE, TARGET_TYPE, KIND, METADATA_ID, UI_NAME, UI_ON, COMPARE_ON, HISTORY_ON, LINK_TABLE_NAME, AFTER_LOAD_PROC_NAME, AFTER_IMPORT_PROC_NAME, METADATA_VERSION, VIEW_URL, COMPARE_URL, HISTORY_URL, SAME_TARGET_COMPARE) VALUES ('oracle_osm', 'osm_instance', 'P', metadata_id, 'ASM Problems', 'Y', 'N', 'N', NULL, NULL, NULL, '1.0', NULL, NULL, NULL, 'H'); -- -- Register metadata for table MGMT_OSM_DISK_GROUP_ECM -- INSERT INTO MGMT_ECM_SNAPSHOT_MD_TABLES (METADATA_ID, NAME, UI_NAME, UI_ON, COMPARE_ON, HISTORY_ON, PARENT_TABLE_NAME, IS_SINGLE_ROW, TBL_ORDER) VALUES (metadata_id, 'MGMT_OSM_DISK_GROUP_ECM', 'ASM Disk Group Problems', 'Y', 'N', 'N', NULL, 'N', 1); INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, HISTORY_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER) VALUES (metadata_id, 'MGMT_OSM_DISK_GROUP_ECM', 'DISK_GROUP', 'Disk Group', 'S', '30', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N', NULL, 0); INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, HISTORY_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER) VALUES (metadata_id, 'MGMT_OSM_DISK_GROUP_ECM', 'PROBLEM_CODE', 'Object', 'N', '2', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', NULL, 1); INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, HISTORY_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER) VALUES (metadata_id, 'MGMT_OSM_DISK_GROUP_ECM', 'VALUE1_N', 'Number Value1', 'N', '10', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', NULL, 2); INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, HISTORY_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER) VALUES (metadata_id, 'MGMT_OSM_DISK_GROUP_ECM', 'VALUE2_N', 'Number Value2', 'N', '10', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', NULL, 3); INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, HISTORY_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER) VALUES (metadata_id, 'MGMT_OSM_DISK_GROUP_ECM', 'VALUE3_S', 'Value3', 'S', '100', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', NULL, 4); INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, HISTORY_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER) VALUES (metadata_id, 'MGMT_OSM_DISK_GROUP_ECM', 'VALUE4_S', 'Value4', 'S', '100', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', NULL, 5); END; / SHOW ERRORS; ALTER SESSION SET CONSTRAINTS = IMMEDIATE; COMMIT;