Guide for CAMIS Oracle job

The oracle job JOB_RUN_SP_BACKUP_W005 calls the procedure SP_BACKUP_W005_PROCESS_LOG which runs every six months. The Team Leader will get the e-mail from the Oracle job.

The procedure to create the backup WA005_PROCESS_LOG table has all possible exemptions to catch any error. In case of any error is too complex to undo anything, so this has to be done manually.

After receiving the e-mail from the Oracle job being run, make sure to check both tables, the WA005_PROCESS_LOG and the WA005_PROCESS_LOG_ARCHIVE_(CURRENT MONTH)_(CURRENT YEAR) as shown below. Make sure the WA005_PROCESS_LOG is either empty or has records from the day the Oracle job was run. The WA005_PROCESS_LOG_ARCHIVE_(CURRENT MONTH)_(CURRENT YEAR) must have the current month and year in its name as shown below and must have millions of records (we estimated about 80 millions ).

If the Oracle job was successful, check from 2 to 6 just to make sure the tables are in place. There is nothing to run. On the other hand if the Oracle job had errors we must undo whatever was done and fix it manually.

Analyze the situation:

1- Look at the Oracle error to have an idea where the program failed.

2- Was the WA005_PROCESS_LOG renamed to WA005_PROCESS_LOG_ARCHIVE_(CURRENT MONTH)_(CURRENT YEAR) and contains about 80 millions of records?

3- Was the WA005_PROCESS_LOG created? Empty or with recent records?

4- Check the indexes and constraints on both tables, the name must have an extra “_b” for the WA005_PROCESS_LOG_ARCHIVE_(CURRENT MONTH)_(CURRENT YEAR). Both tables should have both(indexes and constraints).

5- Check the triggers on both tables, the WA005_PROCESS_LOG table must have both triggers and the WA005_PROCESS_LOG_ARCHIVE_(CURRENT MONTH)_(CURRENT YEAR) must have none.

6- Check the indexes, constraint and triggers from the previous backup table in this example (the one from June). They should not exist.

Based on your analysis where the program failed(fix the problem) you have to decide to undo all the changes and rerun the oracle job manually doing the following:

a- If this is ACC OR PROD have to request help from the DBA’s to run the script below or fix any problem.

b- If the WA005_PROCESS_LOG was renamed to WA005_PROCESS_LOG_ARCHIVE_(CURRENT MONTH)_(CURRENT YEAR) and the WA005_PROCESS_LOG was re-created then fix the problem and undo the renamed by running the code below:

Script to run---

DROP table WA005_PROCESS_LOG cascade constraints;

ALTER TABLE camis. WA005_PROCESS_LOG_ARCHIVE_(CURRENT MONTH)_(CURRENT YEAR) RENAME TO WA005_PROCESS_LOG;

 --------rename INDEXES and CONSTRAINT back---------
ALTER INDEX WA005_PK_b RENAME TO WA005_PK;

ALTER INDEX  WA005_IE1_b RENAME TO WA005_IE1;

ALTER INDEX  WA005_IE2_b RENAME TO WA005_IE2;

ALTER TABLE camis.WA005_PROCESS_LOG rename constraint WA005_PK_b to WA005_PK;

----Create triggers-----------------------------
CREATE OR REPLACE TRIGGER CAMIS.WA005_TG1_BI_PROCESS_LOG_ID

 BEFORE INSERT

 ON CAMIS.WA005_PROCESS_LOG

 REFERENCING OLD AS OLD NEW AS NEW

 FOR EACH ROW

BEGIN

--  IF :NEW.process_log_id IS NULL THEN

     SELECT WA005_SQ1_BI_PROCESS_LOG_ID.NEXTVAL

       INTO :NEW.PROCESS_LOG_ID

       FROM DUAL;

--  END IF;

END;

/

CREATE OR REPLACE TRIGGER CAMIS.WA005_TG1_BU_DATE_LAST_UPDATE

 BEFORE UPDATE

 ON CAMIS.WA005_PROCESS_LOG

 REFERENCING OLD AS OLD NEW AS NEW

 FOR EACH ROW

DECLARE

 BEGIN

  :NEW.DATE_LAST_UPDATE_DTE := SYSDATE;

 END;

/

--SYNONYMS----
CREATE OR REPLACE SYNONYM CAMIS_ANALYST.WA005_PROCESS_LOG FOR CAMIS.WA005_PROCESS_LOG;
 
CREATE OR REPLACE SYNONYM CAMIS_DATA_ADMIN.WA005_PROCESS_LOG FOR CAMIS.WA005_PROCESS_LOG;

CREATE OR REPLACE SYNONYM CAMIS_DUI.WA005_PROCESS_LOG FOR CAMIS.WA005_PROCESS_LOG;

--GRANTS----
GRANT SELECT ON CAMIS.WA005_PROCESS_LOG TO CAMIS_ANALYST;

GRANT DELETE, INSERT, SELECT, UPDATE ON CAMIS.WA005_PROCESS_LOG TO CAMIS_DATA_ADMIN;

GRANT SELECT ON CAMIS.WA005_PROCESS_LOG TO CAMIS_DUI WITH GRANT OPTION;

GRANT DELETE, INSERT, UPDATE ON CAMIS.WA005_PROCESS_LOG TO CAMIS_DUI;
 
GRANT DELETE, INSERT, SELECT, UPDATE ON CAMIS.WA005_PROCESS_LOG TO CAMIS_EMER;

GRANT DELETE ON CAMIS.WA005_PROCESS_LOG TO CAMIS_EXTB;

---for the previous backup table, the one that existed before running the Oracle job,

---check if there are no indexes, constraint, synonyms or grants and recreate them as follow:

CREATE UNIQUE INDEX CAMIS.WA005_PK_B ON CAMIS. WA005_PROCESS_LOG_ARCHIVE_(PREVIOUS_BACKUP TABLE)(PROCESS_LOG_ID);

ALTER TABLE CAMIS.WA005_PROCESS_LOG_ARCHIVE_(PREVIOUS_BACKUP TABLE) ADD (

  CONSTRAINT WA005_PK_b

  PRIMARY KEY

  (PROCESS_LOG_ID)

  USING INDEX CAMIS.WA005_PK_b

  ENABLE VALIDATE);

CREATE INDEX CAMIS.WA005_IE1_b ON CAMIS.WA005_PROCESS_LOG_ARCHIVE_(PREVIOUS_BACKUP TABLE)

(DATE_LAST_UPDATE_DTE);

CREATE INDEX CAMIS.WA005_IE2_b ON CAMIS.WA005_PROCESS_LOG_ARCHIVE_(PREVIOUS_BACKUP TABLE)

(PROCESS_NAME_NM);

 --SYNONYMS  for the previous backup table ----
CREATE OR REPLACE SYNONYM CAMIS_ANALYST.WA005_PROCESS_LOG_ARCHIVE_(PREVIOUS_BACKUP TABLE) FOR CAMIS.WA005_PROCESS_LOG_ARCHIVE_(PREVIOUS_BACKUP TABLE);

CREATE OR REPLACE SYNONYM CAMIS_DATA_ADMIN.WA005_PROCESS_LOG_ARCHIVE_(PREVIOUS_BACKUP TABLE) FOR CAMIS.WA005_PROCESS_LOG_ (PREVIOUS_BACKUP TABLE);

c- Nothing was updated. Again, make sure to know exactly where the program failed and fix the problem.

d- After fixing and undoing anything, try to run the oracle job again.