refresh pdb - liamlamth/blog GitHub Wiki

Function

repeatedly clone pdb and make customized modification, such as production environment mirror

dictionary/log

select * from dba_scheduler_chains;
select * from dba_scheduler_chain_steps;
select * from dba_scheduler_chain_rules;
select * from dba_db_links;
select * from dba_scheduler_job_run_details where job_name = 'JOB_REFRESH_PDB' order by 1  desc;
select * from refresh_log;
select * from refresh_log_last;

cleaning existing built

exec DBMS_SCHEDULER.PURGE_LOG();
begin
    DBMS_SCHEDULER.stop_job ('JOB_REFRESH_PDB');
end;
/
begin 
    DBMS_SCHEDULER.drop_job ('JOB_REFRESH_PDB');
    DBMS_SCHEDULER.drop_chain ('CHAIN_REFRESH_PDB', true);
    DBMS_SCHEDULER.drop_program ('PGM_CLONE_PDB');
    DBMS_SCHEDULER.drop_program ('PGM_CLEANSE_P_UAT');
    DBMS_SCHEDULER.drop_program ('PGM_SUMMARIZE_CLONE');
end;
/

create chain and job

create or replace procedure refresh_pdb(in_src_cdb in varchar2, in_src_pdb in varchar2, in_loc_pdb in varchar2)
is
    v_common_location   varchar2(100) := '/u02/oradata/';
    v_lcdb_name         varchar2(10);
    v_lpdb_status       varchar2(10);
    v_job_duration      varchar2(10);
    stop_job            exception;
    err_msg             varchar2(200);
begin
    /* 
    PREREQUISITE:
        - assume datafile located in same parent directory name (v_common_location)
        - source cdb: archivelog mode (select log_mode from v$database;)
        - source cdb: clone user (CREATE USER c##cloner IDENTIFIED BY ABCD1234 CONTAINER=ALL;
                                 GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO c##cloner CONTAINER=ALL;)
        - local server: add source db entry in tnsname.ora 
        - local cdb: create database link TWCDB connect to c##cloner identified by ABCD1234 using 'TWCDB';

    REQUIRED LOG TABLE :
        create table REFRESH_LOG (LOG_ID        number generated by default on null as identity, 
                                  LOG_TIME      timestamp default systimestamp,
                                  LOG_TYPE      varchar2(20),
                                  DESCRIPTION   varchar2(200)
                                );

    OPTIONAL VIEW:
        create  or replace view refresh_log_last as 
        select * 
            from refresh_log 
            where log_id >= (select max(log_id) 
                                from refresh_log 
                                where log_type = 'summary' and description = 'begin refresh') 
            order by log_id;
    */

    insert into refresh_log(log_type,description) values('summary','begin refresh');

    execute immediate 'select 1 from user_tables@'||in_src_cdb;
    select name into v_lcdb_name from v$database;

    select (select open_mode from v$pdbs where name = in_loc_pdb) into v_lpdb_status from dual;
    if v_lpdb_status in ('READ WRITE','READ ONLY') then
        insert into refresh_log(log_type,description) values('details','target pdb is open');
    elsif v_lpdb_status in ('MOUNTED') then
        insert into refresh_log(log_type,description) values('details','target pdb is close.');
        goto drop_pdb;
    elsif v_lpdb_status is null then
        insert into refresh_log(log_type,description) values('details','target pdb does not exist.');
        goto clone_pdb;
    else
        insert into refresh_log(log_type,description) values('details','target pdb abnormal');
        raise stop_job;
    end if;

    insert into refresh_log(log_type,description) values('details','shutting down the old target pdb...');
    execute immediate 'alter pluggable database '||in_loc_pdb||' close immediate';

    <<drop_pdb>>
    insert into refresh_log(log_type,description) values('details','droping the old target pdb...');
    execute immediate 'drop pluggable database '||in_loc_pdb||' including datafiles';

    <<clone_pdb>>  
    insert into refresh_log(log_type,description) values('details','cloning the source pdb...');
    execute immediate 'create pluggable database '||in_loc_pdb||' from '||in_src_pdb||'@'||in_src_cdb||
                            ' file_name_convert = ('''||v_common_location||in_src_cdb||'/'||in_src_pdb||''','''||v_common_location||v_lcdb_name||'/'||in_loc_pdb||''')';

    insert into refresh_log(log_type,description) values('details','starting the fresh target pdb...');
    execute immediate 'alter pluggable database '||in_loc_pdb||' open';

    select (select open_mode from v$pdbs where name = in_loc_pdb) into v_lpdb_status from dual;
    if v_lpdb_status = 'READ WRITE' then
        select case extract(day from diff)
                    when 0 then
                        lpad(extract(hour from diff),2,'0') || ':' ||
                        lpad(extract(minute from diff),2,'0') || ':' ||
                        lpad(ceil(extract(second from diff)),2,'0')
                    else '1 day+'
               end 
               into v_job_duration
               from (select systimestamp-log_time diff
                        from refresh_log
                        where log_id = (select max(log_id) from refresh_log where log_type = 'summary' and description = 'begin refresh')
                    );
        insert into refresh_log(log_type,description) values('duration',v_job_duration);
        insert into refresh_log(log_type,description) values('summary','succeed refresh');
    else
        insert into refresh_log(log_type,description) values('summary','failed refresh');
    end if;

    commit;

exception 
    when stop_job then
        insert into refresh_log(log_type,description) values('summary','cancelled refresh');
        commit;
    when others then
        err_msg := substr(sqlerrm, 1, 200);
        insert into refresh_log(log_type,description) values('summary', err_msg );
        insert into refresh_log(log_type,description) values('summary','cancelled refresh');
        commit;
end;

DECLARE
    l_plsql_clone      varchar2(32767);
    l_script_cleanse   varchar2(32767);
    l_script_summarize varchar2(32767);
BEGIN
l_plsql_clone := '
BEGIN 
REFRESH_PDB(''C_PROD'',''P_PROD'',''P_UAT''); 
END;';
l_script_cleanse := '
/*
customized after-clone script
*/

conn / as sysdba
col c_today new_value v_today
select to_char(sysdate,''YYYYMMDD'') as c_today from dual;
spool /log/cleanse_p_uat_'||chr(38)||'v_today..log

set lin 230
col LOG_TIME format a30
col LOG_TYPE  format a10
col DESCRIPTION format a50
select * from refresh_log_last ;

conn appdba/apppassword@P_UAT as sysdba
commit;
';
l_script_summarize := '
#!/usr/bin/ksh
if [ $(/usr/bin/grep -ic "ERROR\|ORA-\|FAIL" /log/cleanse_p_uat_$(date ''+%Y%m%d'').log) -gt 0 ]; then
        cat /log/cleanse_$(date ''+%Y%m%d'').log | /usr/bin/mutt -s "P_UAT ERROR: Cloning Job - $(date ''+%Y-%m-%d'')" -- db_adm
fi
';

    DBMS_SCHEDULER.CREATE_PROGRAM (
        program_name             => 'PGM_CLONE_PDB',
        program_type             => 'PLSQL_BLOCK',
        program_action           => l_plsql_clone,
        enabled                  => TRUE);
    DBMS_SCHEDULER.CREATE_PROGRAM (
        program_name             => 'PGM_CLEANSE_P_UAT',
        program_type             => 'SQL_SCRIPT',
        program_action           => l_script_cleanse,
        enabled                  => TRUE);
    DBMS_SCHEDULER.CREATE_PROGRAM (
        program_name             => 'PGM_SUMMARIZE_CLONE',
        program_type             => 'EXTERNAL_SCRIPT',
        program_action           => l_script_summarize,
        enabled                  => TRUE);
END;
/

BEGIN
    DBMS_SCHEDULER.CREATE_CHAIN (
        chain_name          =>  'CHAIN_REFRESH_PDB');
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
        chain_name          =>  'CHAIN_REFRESH_PDB',  -- this must be the same name you used in the chain object definition / creation
        step_name           =>  'CLONE_PDB',
        program_name        =>  'PGM_CLONE_PDB');
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
        chain_name          =>  'CHAIN_REFRESH_PDB',  -- this must be the same name you used in the chain object definition / creation
        step_name           =>  'CLEANSE_P_UAT',
        program_name        =>  'PGM_CLEANSE_P_UAT');
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
        chain_name          =>  'CHAIN_REFRESH_PDB',  -- this must be the same name you used in the chain object definition / creation
        step_name           =>  'SUMMARIZE_CLONE',
        program_name        =>  'PGM_SUMMARIZE_CLONE');
    
    DBMS_SCHEDULER.ENABLE ('CHAIN_REFRESH_PDB');
    DBMS_SCHEDULER.ALTER_CHAIN('CHAIN_REFRESH_PDB','CLEANSE_P_UAT','credential_name','ORACLE');
    DBMS_SCHEDULER.ALTER_CHAIN('CHAIN_REFRESH_PDB','SUMMARIZE_CLONE','credential_name','ORACLE');
    
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
        chain_name          =>  'CHAIN_REFRESH_PDB', 
        condition           =>  'TRUE',
        action              =>  'START CLONE_PDB',
        rule_name           =>  'CHAIN_REFRESH_PDB_RULE_1'); 
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
        chain_name          =>  'CHAIN_REFRESH_PDB', 
        condition           =>  'CLONE_PDB SUCCEEDED',
        action              =>  'START CLEANSE_P_UAT',
        rule_name           =>  'CHAIN_REFRESH_PDB_RULE_2'); 
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
        chain_name          =>  'CHAIN_REFRESH_PDB', 
        condition           =>  'CLEANSE_P_UAT SUCCEEDED',
        action              =>  'START SUMMARIZE_CLONE',
        rule_name           =>  'CHAIN_REFRESH_PDB_RULE_3'); 
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
        chain_name          =>  'CHAIN_REFRESH_PDB', 
        condition           =>  'SUMMARIZE_CLONE SUCCEEDED',
        action              =>  'END',
        rule_name           =>  'CHAIN_REFRESH_PDB_RULE_4'); 
        
    DBMS_SCHEDULER.CREATE_JOB (
       job_name             =>  'JOB_REFRESH_PDB',
       job_type             =>  'CHAIN',
       job_action           =>  'CHAIN_REFRESH_PDB',
       repeat_interval      =>  'freq=daily; byhour=0; byminute=0',
       enabled              =>  TRUE);
END;
/

refresh pdb - liamlamth/blog GitHub Wiki

Function

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️