Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose
Provides an interface to examine and manipulate data about pluggable databases.
BEGIN
IF dbms_pdb.check_plug_compatibility('/home/oracle/pdbdev.xml', 'PDBDEV') THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/
col time format a30
col name format a12
col cause format a10
col type format a8
col action format a50
col message format a95
col con_id format 999
SELECT time, name, type, message
FROM pdb_plug_in_violations;
TIME NAME TYPE MESSAGE
------------------------------ --------- -------- -----------------------------------------------------
11-DEC-20 06.16.54.684970 PM PDB$SEED ERROR Database option APS mismatch: PDB installed version
21.0.0.0.0. CDB installed version NULL.
11-DEC-20 06.16.54.684970 PM PDB$SEED ERROR Database option XOQ mismatch: PDB installed version
21.0.0.0.0. CDB installed version NULL.
17-JAN-21 02.22.09.504732 AM PDB$SEED WARNING Database option RAC mismatch: PDB installed version NULL.
CDB installed version 21.0.0.0.0.
17-JAN-21 02.22.13.427033 AM TEST21P1 WARNING Database option RAC mismatch: PDB installed version NULL.
CDB installed version 21.0.0.0.0.
17-JAN-21 02.22.13.724639 AM TEST21P1 WARNING Tablespace SYSTEM is not encrypted.
Oracle Cloud mandates all tablespaces should be encrypted.
17-JAN-21 02.22.13.727556 AM TEST21P1 WARNING Tablespace SYSAUX is not encrypted.
Oracle Cloud mandates all tablespaces should be encrypted.
SELECT time, name, type, status, action
FROM pdb_plug_in_violations;
TIME NAME TYPE STATUS ACTION
------------------------------ --------- -------- --------- --------------------------------------------
11-DEC-20 06.16.54.684970 PM PDB$SEED ERROR RESOLVED Fix the database option in the PDB or the CDB
11-DEC-20 06.16.54.684970 PM PDB$SEED ERROR RESOLVED Fix the database option in the PDB or the CDB
17-JAN-21 02.22.09.504732 AM PDB$SEED WARNING PENDING Fix the database option in the PDB or the CDB
17-JAN-21 02.22.13.427033 AM TEST21P1 WARNING PENDING Fix the database option in the PDB or the CDB
17-JAN-21 02.22.13.724639 AM TEST21P1 WARNING PENDING Encrypt the tablespace.
17-JAN-21 02.22.13.727556 AM TEST21P1 WARNING PENDING Encrypt the tablespace.
Performs cleanup task previously performed by SMON
-- returns 0 if the next scheduled time for the job does not need to be changed. N if the next schedule job should be N seconds from now (not my experience as shown below)
dbms_pdb.cleanup_task(task_id IN NUMBER) RETURN NUMBER;
SELECT dbms_pdb.cleanup_task(153)
FROM dual;
*
ERROR at line 1:
ORA-65160: invalid cleanup task ID
ORA-06512: at "SYS.DBMS_PDB", line 85
dbms_pdb.convert_to_local(
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
namespace IN NUMBER,
object_subname IN VARCHAR2 DEFAULT NULL,
sig_mismatch IN BOOLEAN DEFAULT FALSE);
Use only in the ADW cloud where some users/roles, which should have been Oracle maintained, was not and needs to be converted in an existing PDB
Note: This could be extraordinarily dangerous with patching. Do not use this procedure.
dbms_pdb.convert_to_oracle_maintained(user_name IN VARCHAR2);
CREATE USER c##convert NO AUTHENTICATION;
User created.
SELECT oracle_maintained
FROM dba_users
WHERE username = 'C##CONVERT';
O
-
N
exec dbms_pdb.convert_to_oracle_maintained('C##CONVERT');
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_PDB", line 575
ORA-06512: at line 1
-- A decision has been made not to publish the
workaround
This procedure enables execution of certain restricted statements (most DDLs) on metadata-linked objects, from within a PDB
Note: This procedure is called by the package initialization section.
This is accomplished by running the statement as an oracle script (i.e. with the parameter _oracle_script set to TRUE). This is for use by Oracle internal packages only.
dbms_pdb.exec_as_oracle_script(sql_stmt IN VARCHAR2);
Exports RMAN backup information that belong to the pluggable database to its dictionary before unplug so that pre-plugin backups can be used. The pluggable database has to be opened in read write mode.
If the database is non-cdb, then pdb_name must be omitted. If the pdb_name is omitted, then the pluggable database to which the session is connected will be exported. If the pdb_name is omitted, and the session is connected to the Root, an error is returned.
dbms_pdb.exportRMANbackup(pdb_name IN VARCHAR2 DEFAULT NULL);
Generates Partial Archive Logs for all non-spooled online logs
dbms_pdb.generate_partial_logs(
partial_log_dest IN VARCHAR2,
remote_dblink IN VARCHAR2 DEFAULT NULL,
include_fralogs IN BOOLEAN DEFAULT FALSE,
copy_fralogs IN BOOLEAN DEFAULT TRUE,
copy_dop IN NUMBER DEFAULT 16,
force_copy_logs IN BOOLEAN DEFAULT FALSE);
Checks whether given path_name is corresponding to the path_prefix property
dbms_pdb.is_valid_path(path_name IN VARCHAR2) RETURN BOOLEAN;
BEGIN
IF dbms_pdb.is_valid_path('/u04/app/oracle/oradata/orabase/orabase/uwapp/') THEN
dbms_output.put_line('T');
ELSE
dbms_output.put_line('F');
END IF;
END;
/ F
Generates an XML file describing a pluggable database by using data files belonging to the database. The XML file can be used to plug the PDB into a CDB using CREATE PLUGGABLE DATABASE.
dbms_pdb.recover(
pdb_descr_file IN VARCHAR2,
pdb_name IN VARCHAR2,
filenames IN VARCHAR2);
SQL> exec dbms_pdb.recover('c:\tmp\orabase_desc.xml', 'ORABASE1', 'c\app\oracle\oradata\orabase\orabase1\orabase1_temp.dbf');
BEGIN dbms_pdb.recover('c:\tmp\orabase_desc.xml', 'ORABASE1', 'c\app\oracle\oradata\orabase\orabase1\orabase1_temp.dbf'); END;
*
ERROR at line 1:
ORA-65128: PDB recover data file name not specified
ORA-06512: at "SYS.DBMS_PDB", line 104
ORA-06512: at line 1
Used to mark an object as Data linked in an App Root. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers.
dbms_pdb.set_data_linked(
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
namespace IN NUMBER,
edition_name IN VARCHAR2 DEFAULT NULL);
Use to mark an object as Extended Data linked in an App Root. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, and where there was no support for application containers
dbms_pdb.set_ext_data_linked(
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
namespace IN NUMBER,
edition_name IN VARCHAR2 DEFAULT NULL);
Used to mark an object as Metadata linked in an App Root. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers.
dbms_pdb.set_metadata_linked(
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
namespace IN NUMBER,
edition_name IN VARCHAR2 DEFAULT NULL)
Used to mark a profile as an explicit Application Common role. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers.
When such a PDB or non-CDB is converted into an Application Root via clone or plugin, the profiles would have been marked as implicit Application Common profile.
dbms_pdb.set_profile_explicit(profile_name IN VARCHAR2);
BEGIN
dbms_pdb.set_profile_explicit('MATRIX_PROFILE');
END;
/
Used to mark a role as an explicit Application Common role. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers.
When such a PDB or non-CDB is converted into an Application Root via clone or plugin, the roles would have been marked as implicit Application Common roles.
dbms_pdb.set_role_explicit(role_name IN VARCHAR2);
BEGIN
dbms_pdb.set_role_explicit('MATRIX_RO');
END;
/
Used to set SHARTING=NONE status on an object in an App Root. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers.
dbms_pdb.set_sharing_none(
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
namespace IN NUMBER,
edition_name IN VARCHAR2 DEFAULT NULL);
Used to mark a user as an explicit Application Common user. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers.
When such a PDB or non-CDB is converted into an Application Root via clone or plugin, the users would have been marked as implicit Application Common users.
dbms_pdb.set_user_explicit(user_name IN VARCHAR2);
BEGIN
dbms_pdb.set_user_explicit('MATRIX');
END;
/
Updates CDB View Stats and returns 0 if the next scheduled job change time does not need to be changed: N if the next scheduled time should be N seconds from SYSDATE
dbms_pdb.update_cdbvw_stats RETURN NUMBER;
set timing on
SELECT dbms_pdb.update_cdbvw_stats
FROM dual;
UPDATE_CDBVW_STATS
------------------
3600
Elapsed: 00:04:27.10
set timing off
-- will appear to hang the system for a very long time while stats are collected
