Application PDB and Appseed codes

8.1 Application PDB and Appseed codes

Example 8-1 Application_Installation.sql

SET VERIFY ON
SET HEAD ON
SET FEEDBACK 1
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO ON
SET TRIMSPOOL ON
SET COLSEP ';'
SET SERVEROUT OFF
clear screen
SPOOL ON
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON
SET ERRORLOGGING ON
SET ECHO ON
prompt Welcome to Application PDB Configuration
SPOOL "&SPOOL_PATH"
/* Inputs are recieved */
/* Connect CDB as sys user */
accept P_CDB_USER Prompt 'Enter CDB Schema Username: '
accept P_CDB_PWD Prompt  'Enter CDB Schema Password: '
accept P_CDB_HOST Prompt 'Enter CDB Schema Host: '
accept P_CDB_PORT Prompt 'Enter CDB Schema Port: '
accept P_APPROOT_NAME Prompt 'Enter Application Root Name: '
accept P_APPLICATION_NAME Prompt   'Enter application name to be installed: '
accept P_COMMON_USER Prompt 'Enter Common User Name: '

/* Connecting to Application Root As SYSDBA*/
conn &P_CDB_USER/&P_CDB_PWD@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=&P_CDB_HOST)(PORT=&P_CDB_PORT)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=&P_APPROOT_NAME))) as sysdba;

alter pluggable database application &P_APPLICATION_NAME begin install '1.0';
	exec dbms_pdb.set_user_explicit('&P_COMMON_USER');
alter pluggable database application &P_APPLICATION_NAME end install;

SET ERRORLOGGING OFF
SPOOL OFF

Example 8-2 Application_PDB_Creation.sql

/* Pre-requisites: Step 2 on application root and application seed has to be completed.*/
SET VERIFY ON
SET HEAD ON
SET FEEDBACK 1
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO ON
SET TRIMSPOOL ON
SET COLSEP ';'
SET SERVEROUT OFF
clear screen
SPOOL ON
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON
SET ERRORLOGGING ON
SET ECHO ON
prompt Welcome to Application PDB Configuration
SPOOL "&SPOOL_PATH"
/* Inputs are recieved */
/* Connect Approot as sys user */
accept P_CDB_USER Prompt 'Enter CDB Username: '
accept P_CDB_PWD Prompt  'Enter CDB Password: '
accept P_CDB_HOST Prompt 'Enter CDB Host: '
accept P_CDB_PORT Prompt 'Enter CDB Port: '
accept P_CDB_NAME Prompt 'Enter CDB Schema Name: '
accept P_DB_MOUNTED_PATH Prompt 'Enter Approot mounted path for approot application seed creation: [Eg: /scratch/db1800dat]'

accept P_APPROOT_NAME Prompt 'Enter Application Root Name: '
accept P_APPPDB_NAME Prompt 'Please provide name for Application PDB Name -- Application Root associated PDB: '

/* Connecting to Application Root As SYSDBA*/
conn &P_CDB_USER/&P_CDB_PWD@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=&P_CDB_HOST)(PORT=&P_CDB_PORT)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=&P_APPROOT_NAME))) as sysdba;

/* Creating Application Associated PDB*/
CREATE pluggable database &P_APPPDB_NAME FROM &P_APPROOT_NAME$SEED file_name_convert=('&P_DB_MOUNTED_PATH/&P_CDB_NAME/SEED&P_APPROOT_NAME/','&P_DB_MOUNTED_PATH/&P_APPROOT_NAME/&P_APPPDB_NAME/');
ALTER pluggable database &P_APPPDB_NAME OPEN;

SET ERRORLOGGING OFF
SPOOL OFF

Example 8-3 Application_Template_PDB_Creation.sql

/* Pre-requisites: DB server is created with 18c database installed along with CDB setup */
SET VERIFY ON
SET HEAD ON
SET FEEDBACK 1
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO ON
SET TRIMSPOOL ON
SET COLSEP ';'
SET SERVEROUT OFF
clear screen
SPOOL ON
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON
SET ERRORLOGGING ON
SET ECHO ON
prompt Welcome to Application Template PDB Configuration
SPOOL "&SPOOL_PATH"
/* CDB sys user name and password to be given */
accept P_CDB_USER Prompt 'Enter CDB Schema Username: '
accept P_CDB_PWD Prompt 'Enter CDB Schema Password: '
accept P_CDB_HOST Prompt 'Enter CDB Schema Host: '
accept P_CDB_PORT Prompt 'Enter CDB Schema Port: '
accept P_CDB_NAME Prompt 'Enter CDB Schema Name: '
accept P_DB_MOUNTED_PATH Prompt 'Enter CDB mounted path: [Eg: /scratch/db1800dat]'

accept P_APP_TEMPLATE_PDB Prompt 'Enter Name for Application Template PDB to be created: '
accept P_COMMON_USER Prompt 'Enter Common Username to be created: '
accept P_COMMON_USER_PWD Prompt 'Enter Pwd for Common User : '

/* Connecting to CDB as sysdba */
CONN &P_CDB_USER/&P_CDB_PWD@&P_CDB_NAME AS sysdba;

create pluggable database &P_APP_TEMPLATE_PDB ADMIN USER sourceadmin IDENTIFIED BY sourceadmin file_name_convert=('pdbseed','&P_APP_TEMPLATE_PDB')
default tablespace users datafile '&P_DB_MOUNTED_PATH/&P_CDB_NAME/&P_APP_TEMPLATE_PDB/users01.dbf' size 100M autoextend on next 10M maxsize 30000M;
alter pluggable database &P_APP_TEMPLATE_PDB open;

/*connecting to template pdb as sysdba */
conn &P_CDB_USER/&P_CDB_PWD@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=&P_CDB_HOST)(PORT=&P_CDB_PORT)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=&P_APP_TEMPLATE_PDB))) as sysdba;
CREATE USER &P_COMMON_USER IDENTIFIED BY &P_COMMON_USER_PWD;
grant execute on dbms_sql to &P_COMMON_USER;
grant execute on dbms_lock to &P_COMMON_USER;
grant execute on dbms_job to &P_COMMON_USER;
/*grant execute on dbms_alert to &P_COMMON_USER;*/ /*//FCUBS_14.6 Autonomous Database impact Remediation changes - commented*/
grant execute on dbms_refresh to &P_COMMON_USER;
/*grant execute on dbms_pipe to &P_COMMON_USER;*/ /*//FCUBS_14.6 Autonomous Database impact Remediation changes - commented*/
/*grant execute on dbms_shared_pool to &P_COMMON_USER;*/ /*//FCUBS_14.6 Autonomous Database impact Remediation changes - commented*/
grant execute on dbms_application_info to &P_COMMON_USER;
grant execute on utl_file to &P_COMMON_USER;
grant select on v_$process to &P_COMMON_USER;
grant select on v_$session to &P_COMMON_USER;
grant select on v_$instance to &P_COMMON_USER;
grant select on v_$timer to &P_COMMON_USER;
grant select on v_$database to &P_COMMON_USER;
grant select on v_$parameter to &P_COMMON_USER;
grant select on v_$nls_parameters to &P_COMMON_USER;
grant select on dba_jobs_running to &P_COMMON_USER;
grant create session to &P_COMMON_USER;
grant create synonym to &P_COMMON_USER;
grant create view to &P_COMMON_USER;
grant create sequence to &P_COMMON_USER;
grant create table to &P_COMMON_USER;
grant create procedure to &P_COMMON_USER;
grant create trigger to &P_COMMON_USER;
grant create type to &P_COMMON_USER;
grant create library to  &P_COMMON_USER;
grant create any synonym to  &P_COMMON_USER;
grant select on dba_jobs to &P_COMMON_USER;
grant create materialized view to &P_COMMON_USER;
grant execute on dbms_aq to &P_COMMON_USER;
grant execute on dbms_aqadm to &P_COMMON_USER;
grant execute on dbms_job to  &P_COMMON_USER;
grant execute on dbms_lock to  &P_COMMON_USER;
/*grant execute on dbms_pipe to  &P_COMMON_USER;*/ /*//FCUBS_14.6 Autonomous Database impact Remediation changes - commented*/
grant execute on dbms_refresh to  &P_COMMON_USER;
grant execute on dbms_rls to  &P_COMMON_USER;
/*create public synonym dbms_shared_pool for sys.dbms_shared_pool;*/ /*//FCUBS_14.6 Autonomous Database impact Remediation changes - commented*/
/*grant execute on dbms_shared_pool to  &P_COMMON_USER;*/ /*//FCUBS_14.6 Autonomous Database impact Remediation changes - commented*/
grant execute on dbms_sql to  &P_COMMON_USER;
grant execute on utl_file to  &P_COMMON_USER;
grant select on SYS.TRANSPORT_SET_VIOLATIONS to  &P_COMMON_USER;
grant create evaluation context to  &P_COMMON_USER;
grant create rule to  &P_COMMON_USER;
grant create job to  &P_COMMON_USER;
grant create rule set to  &P_COMMON_USER;
grant exp_full_database to  &P_COMMON_USER; 
grant alter tablespace to  &P_COMMON_USER;
grant manage tablespace to  &P_COMMON_USER;
grant execute on DBMS_FILE_TRANSFER to  &P_COMMON_USER;
grant execute on SYS.DBMS_TTS to  &P_COMMON_USER; 
grant execute on SYS.DBMS_DATAPUMP to  &P_COMMON_USER;
grant JAVAUSERPRIV to &P_COMMON_USER;
grant execute on dbms_scheduler to  &P_COMMON_USER;
create public synonym UTL_RECOMP for sys.UTL_RECOMP;
grant execute on UTL_RECOMP to &P_COMMON_USER;
/*grant execute on DBMS_MONITOR to &P_COMMON_USER;*/ /*//FCUBS_14.6 Autonomous Database impact Remediation changes - commented*/
grant select on dba_directories to &P_COMMON_USER;
grant execute on DBMS_CRYPTO to &P_COMMON_USER;
grant select on gv_$session to &P_COMMON_USER;
grant create any directory to &P_COMMON_USER;
grant select on SYS.DBA_SCHEDULER_RUNNING_JOBS to &P_COMMON_USER;
grant execute on sys.dbms_redact to &P_COMMON_USER; 
grant SELECT on sys.redaction_policies to &P_COMMON_USER; 
grant SELECT on sys.redaction_columns to &P_COMMON_USER; 
grant SELECT on sys.redaction_values_for_type_full to &P_COMMON_USER; 
grant create session,connect,resource to &P_COMMON_USER;
grant SELECT ON dba_applications to &P_COMMON_USER; --FCUBS14.4_18C changes added
grant SELECT ON dba_app_versions to &P_COMMON_USER; --FCUBS14.4_18C changes added
grant dba to &P_COMMON_USER;

SET ECHO OFF
clear screen
spool off

Example 8-4 Approot_AppSeed_Creation.sql

/* Pre-requisites: 
    a. Step 1 on template pdb and user creation is completed.
    b. Property file has to be created with SMS and Entity schema details as Template pdb.
    c. Objects has to be loaded in the template pdb from installer for respective product processer
    d. Template pdb schema should be checked for sanity with zero invalids.
*/
SET VERIFY ON
SET HEAD ON
SET FEEDBACK 1
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO ON
SET TRIMSPOOL ON
SET COLSEP ';'
SET SERVEROUT OFF
clear screen
SPOOL ON
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON
SET ERRORLOGGING ON
SET ECHO ON
prompt Welcome to Approot and ApprootSeed Configuration
SPOOL "&SPOOL_PATH"
/* Inputs are recieved */
accept P_CDB_USER Prompt 'Enter CDB Schema Username: '
accept P_CDB_PWD Prompt  'Enter CDB Schema Password: '
accept P_CDB_HOST Prompt 'Enter CDB Schema Host: '
accept P_CDB_PORT Prompt 'Enter CDB Schema Port: '
accept P_CDB_NAME Prompt 'Enter CDB Schema Name: '
accept P_DB_MOUNTED_PATH Prompt 'Enter CDB mounted path for approot application seed creation[Eg: /scratch/db1800dat] :'

accept P_TEMPLATE_PDB Prompt 'Enter Template PDB Name: '
accept P_APPROOT_NAME Prompt 'Enter Approot Name: '
accept P_PDB_TO_APPPDB Prompt 'Please provide path for pdb_to_apppdb.sql: '
accept P_COMMON_USER Prompt 'Enter Common Username created in Template PDB: '

/* Connecting to cdb
conn sys/FC142SYS18C@fc142cbd as sysdba */
CONN &P_CDB_USER/&P_CDB_PWD@&P_CDB_NAME AS sysdba;

/* Creating the Approot */
CREATE pluggable database &P_APPROOT_NAME AS application container FROM &P_TEMPLATE_PDB file_name_convert=('&P_TEMPLATE_PDB','&P_APPROOT_NAME');
ALTER pluggable database &P_APPROOT_NAME open;

/* Connecting to Approot as sysdba*/
conn &P_CDB_USER/&P_CDB_PWD@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=&P_CDB_HOST)(PORT=&P_CDB_PORT)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=&P_APPROOT_NAME))) as sysdba;

grant select on v_$session to &P_COMMON_USER container=all;
grant create session to &P_COMMON_USER container=all;
grant select on gv_$session to &P_COMMON_USER container=all;
grant select on gv_$session to &P_COMMON_USER container=all;
grant select on v_$database to &P_COMMON_USER container=all;

/*Creating Application Seed Manually*/
create pluggable database as seed from &P_APPROOT_NAME file_name_convert=('&P_DB_MOUNTED_PATH/&P_CDB_NAME/&P_APPROOT_NAME/','&P_DB_MOUNTED_PATH/&P_CDB_NAME/SEED&P_APPROOT_NAME/');

alter pluggable database &P_APPROOT_NAME$SEED open;
alter session set container = &P_APPROOT_NAME$SEED;

@&P_PDB_TO_APPPDB;
select cause, type, message, status, action from pdb_plug_in_violations;

SET ERRORLOGGING OFF
SPOOL OFF

Example 8-5 Approot_AppSeed_Sync.sql

/* Pre-requisites: Step 3 on Application associated pdb creation is completed */
SET VERIFY ON
SET HEAD ON
SET FEEDBACK 1
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO ON
SET TRIMSPOOL ON
SET COLSEP ';'
SET SERVEROUT OFF
clear screen
SPOOL ON
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON
SET ERRORLOGGING ON
SET ECHO ON
prompt Welcome to Application PDB Configuration
SPOOL "&SPOOL_PATH"
/* Inputs are received */
accept P_APPROOT_USER Prompt 'Enter Approot Schema Username: '
accept P_APPROOT_PWD Prompt  'Enter Approot Schema Password: '
accept P_APPROOT_HOST Prompt 'Enter Approot Schema Host: '
accept P_APPROOT_PORT Prompt 'Enter Approot Schema Port: '
accept P_APPROOT_NAME Prompt 'Enter Application Root Name: '

accept P_APPLICATION_NAME Prompt   'Enter application name to be upgraded for object conversion: '

/*Connecting to Application seed*/
conn &P_APPROOT_USER/&P_APPROOT_PWD@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=&P_APPROOT_HOST)(PORT=&P_APPROOT_PORT)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=&P_APPROOT_NAME$SEED)));

/*Synching object conversion to application seed */
alter pluggable database application &P_APPLICATION_NAME sync;

SET ERRORLOGGING OFF
SPOOL OFF

Example 8-6 Approot_PDB_Sync.sql

SET VERIFY ON
SET HEAD ON
SET FEEDBACK 1
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO ON
SET TRIMSPOOL ON
SET COLSEP ';'
SET SERVEROUT OFF
clear screen
SPOOL ON
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON
SET ERRORLOGGING ON
SET ECHO ON
prompt Welcome to Application PDB Sync
SPOOL "&SPOOL_PATH"
/* Inputs are received */
accept P_PDB_USER Prompt 'Enter PDB Schema Username: '
accept P_PDB_PWD Prompt 'Enter PDB Schema Password: '
accept P_PDB_HOST Prompt 'Enter PDB Schema Host: '
accept P_PDB_PORT Prompt 'Enter PDB Schema Port: '

accept P_PDB_NAME Prompt 'Enter the PDB name to be synched: '
accept P_APPLICATION_NAME Prompt 'Enter the application name: '

/*Connecting to pdb */
conn &P_PDB_USER/&P_PDB_PWD@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=&P_PDB_HOST)(PORT=&P_PDB_PORT)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=&P_PDB_NAME)));

/*Synching the application with pdbs */
alter pluggable database application &P_APPLICATION_NAME sync;

SET ERRORLOGGING OFF
SPOOL OFF

Example 8-7 fast.sql

EXEC UTL_RECOMP.recomp_parallel(&THREADS,'&SCHEMA');

Example 8-8 fn_error_handler.fnc

CREATE OR REPLACE FUNCTION fn_error_handler(octcode   IN NUMBER,
                                            errcode   IN NUMBER,
                                            statement IN VARCHAR2,
                                            resync    IN NUMBER)
  RETURN NUMBER AUTHID CURRENT_USER is
  retcode NUMBER := DBMS_PDB_APP_CON.SYNC_ERROR_NOT_OK;
BEGIN
  IF errcode IN (24344,6512,65297,65272,65274,4045,1,2264,1430,1434,955,4063,942,4043,65215,2260,904,4023,6510,4097,6508,4088) THEN
    retcode := DBMS_PDB_APP_CON.SYNC_ERROR_OK_ALWAYS;
  END IF;
  RETURN retcode;
END;
/

Parent topic: Annexure 2