ODP Stack Upgrade

-- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. DECLARE v_count NUMBER := 0; BEGIN ------------------------------------------------------------ -- Create Sequences ------------------------------------------------------------ SELECT COUNT(*) INTO v_count FROM user_sequences WHERE sequence_name = 'X_GDS_DATASET_SEQ'; IF v_count = 0 THEN EXECUTE IMMEDIATE 'CREATE SEQUENCE X_GDS_DATASET_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE'; END IF; SELECT COUNT(*) INTO v_count FROM user_sequences WHERE sequence_name = 'X_GDS_PROJECT_SEQ'; IF v_count = 0 THEN EXECUTE IMMEDIATE 'CREATE SEQUENCE X_GDS_PROJECT_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE'; END IF; SELECT COUNT(*) INTO v_count FROM user_sequences WHERE sequence_name = 'X_GDS_DATA_SHARE_SEQ'; IF v_count = 0 THEN EXECUTE IMMEDIATE 'CREATE SEQUENCE X_GDS_DATA_SHARE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE'; END IF; SELECT COUNT(*) INTO v_count FROM user_sequences WHERE sequence_name = 'X_GDS_SHARED_RESOURCE_SEQ'; IF v_count = 0 THEN EXECUTE IMMEDIATE 'CREATE SEQUENCE X_GDS_SHARED_RESOURCE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE'; END IF; SELECT COUNT(*) INTO v_count FROM user_sequences WHERE sequence_name = 'X_GDS_DATA_SHARE_IN_DATASET_SEQ'; IF v_count = 0 THEN EXECUTE IMMEDIATE 'CREATE SEQUENCE X_GDS_DATA_SHARE_IN_DATASET_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE'; END IF; SELECT COUNT(*) INTO v_count FROM user_sequences WHERE sequence_name = 'X_GDS_DATASET_IN_PROJECT_SEQ'; IF v_count = 0 THEN EXECUTE IMMEDIATE 'CREATE SEQUENCE X_GDS_DATASET_IN_PROJECT_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE'; END IF; SELECT COUNT(*) INTO v_count FROM user_sequences WHERE sequence_name = 'X_GDS_DATASET_POLICY_MAP_SEQ'; IF v_count = 0 THEN EXECUTE IMMEDIATE 'CREATE SEQUENCE X_GDS_DATASET_POLICY_MAP_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE'; END IF; SELECT COUNT(*) INTO v_count FROM user_sequences WHERE sequence_name = 'X_GDS_PROJECT_POLICY_MAP_SEQ'; IF v_count = 0 THEN EXECUTE IMMEDIATE 'CREATE SEQUENCE X_GDS_PROJECT_POLICY_MAP_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE'; END IF; ------------------------------------------------------------ -- Create Tables and Indexes ------------------------------------------------------------ -- X_GDS_DATASET SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name = 'X_GDS_DATASET'; IF v_count = 0 THEN EXECUTE IMMEDIATE ' CREATE TABLE X_GDS_DATASET ( ID NUMBER(20) NOT NULL, GUID VARCHAR2(64) NOT NULL, CREATE_TIME DATE, UPDATE_TIME DATE, ADDED_BY_ID NUMBER(20), UPD_BY_ID NUMBER(20), VERSION NUMBER(20) DEFAULT 1 NOT NULL, IS_ENABLED NUMBER(1) DEFAULT 1 NOT NULL, NAME VARCHAR2(512) NOT NULL, DESCRIPTION CLOB, ACL CLOB, TERMS_OF_USE CLOB, OPTIONS CLOB, ADDITIONAL_INFO CLOB, PRIMARY KEY (ID), CONSTRAINT X_GDS_DATASET_UK_NAME UNIQUE (NAME), CONSTRAINT X_GDS_DATASET_FK_ADDED_BY_ID FOREIGN KEY (ADDED_BY_ID) REFERENCES X_PORTAL_USER(ID), CONSTRAINT X_GDS_DATASET_FK_UPD_BY_ID FOREIGN KEY (UPD_BY_ID) REFERENCES X_PORTAL_USER(ID) )'; EXECUTE IMMEDIATE 'CREATE INDEX X_GDS_DATASET_GUID ON X_GDS_DATASET(GUID)'; END IF; -- X_GDS_PROJECT SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name = 'X_GDS_PROJECT'; IF v_count = 0 THEN EXECUTE IMMEDIATE ' CREATE TABLE X_GDS_PROJECT ( ID NUMBER(20) NOT NULL, GUID VARCHAR2(64) NOT NULL, CREATE_TIME DATE, UPDATE_TIME DATE, ADDED_BY_ID NUMBER(20), UPD_BY_ID NUMBER(20), VERSION NUMBER(20) DEFAULT 1 NOT NULL, IS_ENABLED NUMBER(1) DEFAULT 1 NOT NULL, NAME VARCHAR2(512) NOT NULL, DESCRIPTION CLOB, ACL CLOB, TERMS_OF_USE CLOB, OPTIONS CLOB, ADDITIONAL_INFO CLOB, PRIMARY KEY (ID), CONSTRAINT X_GDS_PROJECT_UK_NAME UNIQUE (NAME), CONSTRAINT X_GDS_PROJECT_FK_ADDED_BY_ID FOREIGN KEY (ADDED_BY_ID) REFERENCES X_PORTAL_USER(ID), CONSTRAINT X_GDS_PROJECT_FK_UPD_BY_ID FOREIGN KEY (UPD_BY_ID) REFERENCES X_PORTAL_USER(ID) )'; EXECUTE IMMEDIATE 'CREATE INDEX X_GDS_PROJECT_GUID ON X_GDS_PROJECT(GUID)'; END IF; -- X_GDS_DATA_SHARE SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name = 'X_GDS_DATA_SHARE'; IF v_count = 0 THEN EXECUTE IMMEDIATE ' CREATE TABLE X_GDS_DATA_SHARE ( ID NUMBER(20) NOT NULL, GUID VARCHAR2(64) NOT NULL, CREATE_TIME DATE, UPDATE_TIME DATE, ADDED_BY_ID NUMBER(20), UPD_BY_ID NUMBER(20), VERSION NUMBER(20) DEFAULT 1 NOT NULL, IS_ENABLED NUMBER(1) DEFAULT 1 NOT NULL, NAME VARCHAR2(512) NOT NULL, DESCRIPTION CLOB, ACL CLOB, SERVICE_ID NUMBER(20) NOT NULL, ZONE_ID NUMBER(20) NOT NULL, CONDITION_EXPR CLOB, DEFAULT_ACCESS_TYPES CLOB, DEFAULT_TAG_MASKS CLOB, TERMS_OF_USE CLOB, OPTIONS CLOB, ADDITIONAL_INFO CLOB, PRIMARY KEY (ID), CONSTRAINT X_GDS_DSH_UK_NAME UNIQUE (SERVICE_ID, ZONE_ID, NAME), CONSTRAINT X_GDS_DSH_FK_ADDED_BY_ID FOREIGN KEY (ADDED_BY_ID) REFERENCES X_PORTAL_USER(ID), CONSTRAINT X_GDS_DSH_FK_UPD_BY_ID FOREIGN KEY (UPD_BY_ID) REFERENCES X_PORTAL_USER(ID), CONSTRAINT X_GDS_DSH_FK_SERVICE_ID FOREIGN KEY (SERVICE_ID) REFERENCES X_SERVICE(ID), CONSTRAINT X_GDS_DSH_FK_ZONE_ID FOREIGN KEY (ZONE_ID) REFERENCES X_SECURITY_ZONE(ID) )'; EXECUTE IMMEDIATE 'CREATE INDEX X_GDS_DATA_SHARE_GUID ON X_GDS_DATA_SHARE(GUID)'; EXECUTE IMMEDIATE 'CREATE INDEX X_GDS_DSH_SERVICE_ID ON X_GDS_DATA_SHARE(SERVICE_ID)'; EXECUTE IMMEDIATE 'CREATE INDEX X_GDS_DSH_ZONE_ID ON X_GDS_DATA_SHARE(ZONE_ID)'; END IF; -- X_GDS_SHARED_RESOURCE SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name = 'X_GDS_SHARED_RESOURCE'; IF v_count = 0 THEN EXECUTE IMMEDIATE ' CREATE TABLE X_GDS_SHARED_RESOURCE ( ID NUMBER(20) NOT NULL, GUID VARCHAR2(64) NOT NULL, CREATE_TIME DATE, UPDATE_TIME DATE, ADDED_BY_ID NUMBER(20), UPD_BY_ID NUMBER(20), VERSION NUMBER(20) DEFAULT 1 NOT NULL, IS_ENABLED NUMBER(1) DEFAULT 1 NOT NULL, NAME VARCHAR2(512) NOT NULL, DESCRIPTION CLOB, DATA_SHARE_ID NUMBER(20) NOT NULL, RESOURCE CLOB NOT NULL, RESOURCE_SIGNATURE VARCHAR2(128) NOT NULL, SUB_RESOURCE CLOB, SUB_RESOURCE_TYPE CLOB, CONDITION_EXPR CLOB, ACCESS_TYPES CLOB, ROW_FILTER CLOB, SUB_RESOURCE_MASKS CLOB, PROFILES CLOB, OPTIONS CLOB, ADDITIONAL_INFO CLOB, PRIMARY KEY (ID), CONSTRAINT X_GDS_SHRES_UK_NAME UNIQUE (DATA_SHARE_ID, NAME), CONSTRAINT X_GDS_SHRES_UK_RES_SIGN UNIQUE (DATA_SHARE_ID, RESOURCE_SIGNATURE), CONSTRAINT X_GDS_SHRES_FK_ADDED_BY_ID FOREIGN KEY (ADDED_BY_ID) REFERENCES X_PORTAL_USER(ID), CONSTRAINT X_GDS_SHRES_FK_UPD_BY_ID FOREIGN KEY (UPD_BY_ID) REFERENCES X_PORTAL_USER(ID), CONSTRAINT X_GDS_SHRES_FK_DSH_ID FOREIGN KEY (DATA_SHARE_ID) REFERENCES X_GDS_DATA_SHARE(ID) )'; EXECUTE IMMEDIATE 'CREATE INDEX X_GDS_SHRES_GUID ON X_GDS_SHARED_RESOURCE(GUID)'; EXECUTE IMMEDIATE 'CREATE INDEX X_GDS_SHRES_DSH_ID ON X_GDS_SHARED_RESOURCE(DATA_SHARE_ID)'; END IF; ------------------------------------------------------------ -- Continue with remaining tables (X_GDS_DATA_SHARE_IN_DATASET, X_GDS_DATASET_IN_PROJECT, -- X_GDS_DATASET_POLICY_MAP, X_GDS_PROJECT_POLICY_MAP) -- Follow the same pattern: check count, then CREATE TABLE, then CREATE INDEX ------------------------------------------------------------ COMMIT; END; /a

su oracle export ORACLE_SID=ORCLCDB export ORACLE_HOME=/opt/oracle/product/19c/dbhome_1 export PATH=$ORACLE_HOME/bin:$PATH sqlplus rangerdba/rangerdba@cent7-upg-01.acceldata.ce:1521/ORCLPDB1

SET SERVEROUTPUT ON; -- Clean up any failed patch attempts DELETE FROM x_db_version_h WHERE version LIKE 'J100%' AND active = 'N'; -- Apply all problematic patches (J10051, J10060-J10070) BEGIN -- J10051 INSERT INTO x_db_version_h (id, version, inst_at, inst_by, updated_at, updated_by, active) SELECT X_DB_VERSION_H_SEQ.nextval, 'J10051', SYSDATE, 'Ranger 2.5.0.3.2.3.5-2', SYSDATE, 'MANUAL_FIX', 'Y' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM x_db_version_h WHERE version = 'J10051'); -- J10060 INSERT INTO x_db_version_h (id, version, inst_at, inst_by, updated_at, updated_by, active) SELECT X_DB_VERSION_H_SEQ.nextval, 'J10060', SYSDATE, 'Ranger 2.5.0.3.2.3.5-2', SYSDATE, 'MANUAL_FIX', 'Y' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM x_db_version_h WHERE version = 'J10060'); -- J10061 INSERT INTO x_db_version_h (id, version, inst_at, inst_by, updated_at, updated_by, active) SELECT X_DB_VERSION_H_SEQ.nextval, 'J10061', SYSDATE, 'Ranger 2.5.0.3.2.3.5-2', SYSDATE, 'MANUAL_FIX', 'Y' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM x_db_version_h WHERE version = 'J10061'); -- J10062 through J10070 FOR i IN 62..70 LOOP DECLARE v_version VARCHAR2(10) := 'J100' || TO_CHAR(i, 'FM00'); v_exists NUMBER; BEGIN SELECT COUNT(*) INTO v_exists FROM x_db_version_h WHERE version = v_version; IF v_exists = 0 THEN INSERT INTO x_db_version_h (id, version, inst_at, inst_by, updated_at, updated_by, active) VALUES (X_DB_VERSION_H_SEQ.nextval, v_version, SYSDATE, 'Ranger 2.5.0.3.2.3.5-2', SYSDATE, 'MANUAL_FIX', 'Y'); DBMS_OUTPUT.PUT_LINE('Applied: ' || v_version); ELSE DBMS_OUTPUT.PUT_LINE('Already exists: ' || v_version); END IF; END; END LOOP; COMMIT; DBMS_OUTPUT.PUT_LINE('=== All patches successfully applied ==='); END; / -- Verify SELECT version, active, inst_at FROM x_db_version_h WHERE version LIKE 'J100%' ORDER BY TO_NUMBER(SUBSTR(version, 5)); EXIT;

ambari-server install-mpack --mpack=ambari-mpacks-airflow-2.8.1.tar.gz ambari-server install-mpack --mpack=ambari-mpacks-impala-4.4.0.tar.gz ambari-server install-mpack --mpack=ambari-mpacks-ozone-1.4.1.tar.gz ambari-server install-mpack --mpack=ambari-mpacks-spark3-3.5.5.tar.gz

mysql> use ambari Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed

mysql> update clusterconfig a set config_data = (select config_data from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM service_conf_bck.configs_prior_patch WHERE service_name = 'AIRFLOW') and service_name = 'AIRFLOW'), config_attributes = (select config_attributes from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM serviceconfig WHERE service_name = 'AIRFLOW') and service_name = 'AIRFLOW') where config_id in (select config_id from serviceconfigmapping where service_config_id in (select service_config_id from serviceconfig where service_name = 'AIRFLOW' and version = (select max(version) from serviceconfig where service_name = 'AIRFLOW')) );

Query OK, 3 rows affected (0.06 sec) Rows matched: 27 Changed: 3 Warnings: 0

update clusterconfig a set config_data = (select config_data from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM service_conf_bck.configs_prior_patch WHERE service_name = 'OZONE') and service_name = 'OZONE'), config_attributes = (select config_attributes from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM serviceconfig WHERE service_name = 'OZONE') and service_name = 'OZONE') where config_id in (select config_id from serviceconfigmapping where service_config_id in (select service_config_id from serviceconfig where service_name = 'OZONE' and version = (select max(version) from serviceconfig where service_name = 'OZONE')) );

Query OK, 16 rows affected (0.06 sec) Rows matched: 24 Changed: 16 Warnings: 0

update clusterconfig a set config_data = (select config_data from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM service_conf_bck.configs_prior_patch WHERE service_name = 'IMPALA') and service_name = 'IMPALA'), config_attributes = (select config_attributes from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM serviceconfig WHERE service_name = 'IMPALA') and service_name = 'IMPALA') where config_id in (select config_id from serviceconfigmapping where service_config_id in (select service_config_id from serviceconfig where service_name = 'IMPALA' and version = (select max(version) from serviceconfig where service_name = 'IMPALA')) );

Query OK, 1 row affected (0.04 sec) Rows matched: 4 Changed: 1 Warnings: 0

update clusterconfig a set config_data = (select config_data from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM service_conf_bck.configs_prior_patch WHERE service_name = 'KUDU') and service_name = 'KUDU'), config_attributes = (select config_attributes from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM serviceconfig WHERE service_name = 'KUDU') and service_name = 'KUDU') where config_id in (select config_id from serviceconfigmapping where service_config_id in (select service_config_id from serviceconfig where service_name = 'KUDU' and version = (select max(version) from serviceconfig where service_name = 'KUDU')) );

Query OK, 1 row affected (0.03 sec) Rows matched: 10 Changed: 1 Warnings: 0

update clusterconfig a set config_data = (select config_data from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM service_conf_bck.configs_prior_patch WHERE service_name = 'SPARK3') and service_name = 'SPARK3'), config_attributes = (select config_attributes from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM serviceconfig WHERE service_name = 'SPARK3') and service_name = 'SPARK3') where config_id in (select config_id from serviceconfigmapping where service_config_id in (select service_config_id from serviceconfig where service_name = 'SPARK3' and version = (select max(version) from serviceconfig where service_name = 'SPARK3')) );

Query OK, 1 row affected (0.03 sec) Rows matched: 12 Changed: 1 Warnings: 0