Running Script on Multiple Oracle Pdb Container Database

 

There are situations where we need to run script/task on multiple on Pdb/container   database . There are 3 options we can use .

1) Using catcon.pl 

2) Using Shell Script . 

3) Using  Pl/Sql block 

#####################################

Using catcon.pl  : 

#####################################

catcon.pl is oracle provided script / 

cd $ORACLE_HOME/rdbms/admin/

perl catcon.pl -d $ORACLE_HOME/rdbms/admin -C 'CDB$ROOT PDB$SEED'   -l /tmp/utlrp_output -b utlrp_output utlrp.sql

perl catcon.pl -d $ORACLE_HOME/rdbms/admin -C 'CDB$ROOT PDB$SEED'   -l /tmp/utlrp_output -b utlrp_output -- --x"SELECT SYS_CONTEXT('USERENV', 'CON_NAME') FROM dual"

perl catcon.pl -d $ORACLE_HOME/rdbms/admin -C 'CDB$ROOT PDB$SEED'   -l /tmp/utlrp_output -b utlrp_output -- --x"SELECT SYS_CONTEXT('USERENV', 'CON_NAME') FROM dual" --x"SELECT USER FROM dual"

 

Usage: catcon  [-h, --help]

                 [-u, --usr username

                   [{/password | -w, --usr_pwd_env_var env-var-name}]]

                 [-U, --int_usr username

                   [{/password | -W, --int_usr_pwd_env_var env-var-name]]

                 [-d, --script_dir directory]

                 [-l, --log_dir directory]

                 [{-c, --incl_con | -C, --excl_con} container]

                 [-p, --catcon_instances degree-of-parallelism]

                 [-z, --ez_conn EZConnect-strings]

                 [-e, --echo]

                 [-s, --spool]

                 [-E, --error_logging

                   { ON | errorlogging-table-other-than-SPERRORLOG } ]

                 [-F, --app_con Application-Root]

                 [-V, --ignore_errors errors-to-ignore ]

                 [-I, --no_set_errlog_ident]

                 [-g, --diag]

                 [-v, --verbose]

                 [-f, --ignore_unavailable_pdbs]

                 [--fail_on_unopenable_pdbs]

                 [-r, --reverse]

                 [-R, --recover]

                 [-m, --pdb_seed_mode pdb-mode]

                 [--force_pdb_mode pdb-mode]

                 [--all_instances]

                 [--upgrade]

                 [--ezconn_to_pdb pdb-name]

                 [--sqlplus_dir directory]

                 [--dflt_app_module app-module]

                 -b, --log_file_base log-file-name-base

                 --

                 { sqlplus-script [arguments] | --x } ...

 

.

#####################################

Pl sql block 

#####################################

Below is sample pl/sq block we can use 

declare
type names_t is table of v$pdbs.name%type;
names names_t;
--vname names_t;
type open_modes_t is table of v$pdbs.open_mode%type;
open_modes open_modes_t;
type privilege_user_t is table of dba_sys_privs.privilege%type;
privilege_user privilege_user_t;
begin
        select name, open_mode
        bulk collect
        into names, open_modes
        from v$pdbs
        where name not in ('PDB$SEED' , 'DCPDB01');
                for j in 1 .. names.count()
                loop
                if open_modes (j) <> 'MOUNTED'
                then
                execute immediate 'alter session set container= "' || names (j) || '"';
                end if;
                --select name bulk collect into vname from v$pdbs;
                select grantee bulk collect into privilege_user from dba_sys_privs
                where (privilege like '%ANY%' or privilege ='DBA') and grantee like 'U%'
                group by  grantee, privilege
                order by grantee;
                DBMS_OUTPUT.put_line(privilege_user);
        end loop;
end;
/

DBMS_OUTPUT.put_line(privilege_user(j).[column_name])
DBMS_OUTPUT.put_line() expects a string for an argument. You are passing it a type of a table of dba_sys_privs.privilege%type's.

#####################################

Using Shell Script : 

#####################################

Below was  shell script i wrote  for fetching  data from multiple  Pdb database .

#!/bin/bash

export LOG_FILE=/home/oracle/pdb.lst 

export OUTPUT_FILE=/home/oracle/pdbout.log 

$ORACLE_HOME/bin/sqlplus -s "/ as sysdba"  << EOF >> $LOG_FILE

set SQLBLANLINES OFF 

set echo off 

set feedback off 

SET SERVEROUTput off 

set heading off 

set termout off 

set trimsool off 

set wrap off 

select name from v$pdbs  where name not in (PDB\$SEED','DCPDB01') and open_mode  <> 'MOUNTED' order by 1 ;

exit 

EOF 

FOR pdb_name in `more  /home/oracle/pdb.lst`

do 

$ORACLE_HOME/bin/sqlplus -s "/ as  sysdba" << EOF >> $OUTPUT_FILE

set line 40

col name format a20

col value format a20 

set SQLBLANLINES OFF 

set echo off 

set feedback off 

SET SERVEROUTput off 

set heading off 

set termout off 

set trimsool off 

set wrap off 

set COLSEP '|'

alter session set container= $pdb_name ; 

select name , value from v\$nls_parameter , v\$pdbs where parameter='NLS_CHARACTERSET'; 

EXIT 

EOF

done