For how many years have you been working
with physical servers that are starving your database of the memory
necessary to deploy important new performance features such as the Result
Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and
Full Database Caching? Too long? Contact me to learn how to improve all
queries ... not just some queries.
Purpose
Procedures and functions used to perform checks on a database which is getting ready to be upgrade.
Note
Constants and subprograms listed here are only those that are publicly accessible. Additional constants and subprograms are private to the package body.
A list of all CHECK_NAME values can be found in the INIT_PREUPCHECKS procedure local to the package body (see source code).
The demos below were run against a 18.1.0.0.
AUTHID
DEFINER
Constants
Name
Data Type
Value
c_build
NUMBER
'1'
c_success
NUMBER
1
c_failure
NUMBER
2
debug
BOOLEAN
FALSE
pDBGSizeResources
BOOLEAN
FALSE
Fixup Script Name Constants
C_FIXUP_SCRIPT_NAME_PRE_BASE
VARCHAR2(30)
'preupgrade_fixups'
C_FIXUP_SCRIPT_NAME_POST_BASE
VARCHAR2(30)
'postupgrade_fixups';
Fixup Script Name Variables
C_FIXUP_SCRIPT_NAME_PRE
VARCHAR2(256);
NULL
C_FIXUP_SCRIPT_NAME_POST
VARCHAR2(256)
NULL
Flashback Log Size
C_MIN_FLASHBACK_KB_PER_PDB
NUMBER
333 * 1024
Indexes By Pool Name
cs_idx
V$PARAMETER.NAME%TYPE
'db_cache_size'
jv_idx
V$PARAMETER.NAME%TYPE
'java_pool_size'
lp_idx
V$PARAMETER.NAME%TYPE
'large_pool_size'
mt_idx
V$PARAMETER.NAME%TYPE
'memory_target'
pt_idx
V$PARAMETER.NAME%TYPE
'pga_aggregate_target'
st_idx
V$PARAMETER.NAME%TYPE
'sga_target'
sp_idx
V$PARAMETER.NAME%TYPE
'shared_pool_size'
sr_idx
V$PARAMETER.NAME%TYPE
'streams_pool_size'
Severity Check Level
c_check_level_success
NUMBER
1
c_check_level_warning
NUMBER
2
c_check_level_info
NUMBER
3
c_check_level_error
NUMBER
4
c_check_level_recommend
NUMBER
5
Severity Check Type
c_param_type_version
NUMBER
-1
c_param_type_other
NUMBER
0
c_param_type_string
NUMBER
2
c_param_type_number
NUMBER
3
c_param_type_number_alt
NUMBER
6
Substitution Delimiter
C_SUBSTITUTION_DELIMITER_OPEN
CHAR(1)
'{'
C_SUBSTITUTION_DELIMITER_CLOSE
CHAR(1)
'}'
Data Types
TYPE string_array_t IS TABLE OF VARCHAR2(32767);
TYPE string_array_collection_t IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TYPE detail_t IS RECORD (
detail VARCHAR2(4000),
detail_type VARCHAR2(30));
TYPE number_array_t IS TABLE OF NUMBER;
TYPE messagevalue_t is RECORD (
position NUMBER,
value CLOB);
TYPE messagevalues_t IS TABLE OF messagevalue_t INDEX BY BINARY_INTEGER;
TYPE message_t is RECORD (
-- msg_text VARCHAR2(4000), -- aka the RULE
-- cause VARCHAR2(4000), -- aka the BROKEN_RULE
-- action VARCHAR2(4000),
-- detail detail_t
id VARCHAR2(4000),
messagevalues messagevalues_t);
TYPE fixup_t IS RECORD (
fixup_type VARCHAR2(30),
fixAtStage VARCHAR2(30));
TYPE component_t IS RECORD (
cid VARCHAR2(30), -- component id
cname VARCHAR2(45), -- component name
script VARCHAR2(128), -- upgrade script name
version VARCHAR2(30), -- version
status VARCHAR2(15), -- component status
install BOOLEAN);
TYPE components_t IS TABLE OF component_t INDEX BY BINARY_INTEGER;
TYPE tablespace_t IS RECORD (
name VARCHAR2(128),
additional_size INTEGER,
min INTEGER,
alloc INTEGER,
inc_by INTEGER,
fauto BOOLEAN,
contents SYS.dba_tablespaces.contents%type);
TYPE tablespaces_t IS TABLE OF tablespace_t INDEX BY BINARY_INTEGER;
TYPE archivelogs_t IS RECORD (
name VARCHAR2(128),
additional_size INTEGER);
TYPE flashbacklogs_t IS RECORD (
name VARCHAR2(128),
additional_size INTEGER);
TYPE rollback_segment_t IS RECORD (
name VARCHAR2(128),
tablespc VARCHAR2(128),
status VARCHAR2(31),
auto INTEGER,
inuse INTEGER,
next INTEGER,
max_ext INTEGER);
TYPE rollback_segments_t IS TABLE OF rollback_segment_t
INDEX BY BINARY_INTEGER;
TYPE flashback_info_t IS RECORD (
name VARCHAR2(513), -- name
limit INTEGER, -- space limit
used INTEGER, -- Used
dsize INTEGER, -- db_recovery_file_dest_size
reclaimable INTEGER,
files INTEGER, -- number of files
min_fra_size INTEGER);
TYPE fra_info_t IS RECORD (
name V$RECOVERY_FILE_DEST.NAME%TYPE, -- name/path
limit INTEGER, -- db_recovery_file_dest_size (bytes)
used INTEGER, -- Used (bytes)
dsize INTEGER, -- destination size
reclaimable INTEGER, -- bytes reclaimable
files INTEGER, -- number of files
avail INTEGER, -- bytes available in FRA
min_archive_gen INTEGER, -- minimum archive logs (bytes) estimated
-- to be generate during upgrade
min_flashback_gen INTEGER, -- rough minimum flashback logs (bytes)
-- to be generated during upgrade
min_fra_size INTEGER, -- new db_recovery_file_dest_size to set
min_freespace_reqd INTEGER, -- min free space needed for logs
-- to be generated during upgrade
additional_size INTEGER); -- additional size + limit = min_fra_size
-- TYPE ARCHive DESTination RECORD Type
-- stores info from v$archive_dest if there's at least 1 non-fra destination
TYPE archiveDest_info_t IS RECORD (
dest_name V$ARCHIVE_DEST.DEST_NAME%TYPE, -- log_archive_dest_<N>
destination V$ARCHIVE_DEST.DESTINATION%TYPE, -- destination <path>
status V$ARCHIVE_DEST.STATUS%TYPE, -- e.g., VALID/INACTIVE
min_archive_gen NUMBER); -- min free space needed for archivelogs
-- to be generated during upgrade
TYPE systemresource_t IS RECORD (
tablespaces tablespaces_t,
archivelogs archivelogs_t,
flashbacklogs flashbacklogs_t,
rollback_segments rollback_segments_t,
flashback_info flashback_info_t,
archivedest_info archiveDest_info_t);
-- @@Datatype
TYPE parameter_xml_record_t IS RECORD (
name V$PARAMETER.NAME%TYPE,
value V$PARAMETER.VALUE%TYPE, -- used only when a parameter is being renamed.
type V$PARAMETER.TYPE%TYPE,
isdefault V$PARAMETER.ISDEFAULT%TYPE,
is_obsoleted BOOLEAN,
is_deprecated BOOLEAN,
renamed_to_name VARCHAR2(80),
new_value VARCHAR2(80),
min_value INTEGER,
min_char_value VARCHAR2(20)); -- used for COMPATIBLE whose value is a pseudo number
TYPE parameters_t IS TABLE OF parameter_xml_record_t INDEX BY BINARY_INTEGER;
TYPE initparams_t IS RECORD (
update_params parameters_t,
nonhandled_params parameters_t, -- at 12.2, will have no params. maintained for XML compatibility only.
rename_params parameters_t,
remove_params parameters_t);
TYPE preupgradecheck_t IS RECORD (
id VARCHAR2(30), -- the CHECK name
severity NUMBER, -- "status" attribute in xml
-- message message_t,
rule message_t,
broken_rule message_t,
action message_t,
detail detail_t,
fixup fixup_t);
TYPE preupgradechecks_t IS TABLE OF preupgradecheck_t
INDEX BY BINARY_INTEGER;
TYPE rdbmsup_t IS RECORD (
xmlns VARCHAR2(1000),
version VARCHAR2(30),
upgradable_versions VARCHAR2(1000));
TYPE database_t IS RECORD (
name VARCHAR2(256),
containerName VARCHAR2(256),
containerId NUMBER,
version VARCHAR2(30),
compatibility VARCHAR2(30),
blocksize INTEGER,
platform VARCHAR2(100),
timezoneVer NUMBER,
log_mode VARCHAR2(30),
readonly BOOLEAN,
edition_val VARCHAR2(30)); -- SYS.REGISTRY$.EDITION%TYPE - except it is not avail on 10.2
-- this table holds computation info for the memory pools we are
-- making sizing recommendations for
TYPE memparameter_record_t IS RECORD (
name V$PARAMETER.NAME%TYPE,
old_value NUMBER, -- current value
min_value NUMBER, -- minimum value for upgrade
new_value NUMBER, -- new/recommended value for upgrade
dif_value NUMBER, -- diff of old_value - min_value
isdefault V$PARAMETER.ISDEFAULT%TYPE, -- is the value defaulted?
display BOOLEAN); -- display recommended value? T/F
TYPE memparameter_table_t IS TABLE of memparameter_record_t
INDEX BY V$PARAMETER.NAME%TYPE;
db_is_cdb BOOLEAN; -- is db a cdb? T/F
db_is_root BOOLEAN; -- is db a ROOT container database? T/F
db_n_pdbs NUMBER; -- total Number of PDBs as queried from v$pdbs
is_show_mem_sizes BOOLEAN := FALSE; -- SHOW/display minimum MEMory SIZES?
-- init to FALSE
is_archivelog_in_fra BOOLEAN := FALSE; -- are archive logs in FRA? T/F
-- Provide mappings from a c_check_level_* constant to its string evuivalent used
-- for XML output only. These strings have specific meanings to DBUA. Do not
-- change without corresponding change in the upgrade.xsd and DBUA buy-in.
-- IMPORTANT: The order of these strings must correspond to the constants C_CHECK_LEVEL_*
check_level_strings string_array_t := new string_array_t('SUCCESS','WARNING','INFO','ERROR','RECOMMEND');
TYPE check_level_ints_t IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
check_level_ints check_level_ints_t;
dbms_preup.compatible_parameter_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
OutVal CLOB;
RetVal NUMBER;
BEGIN
RetVal := dbms_preup.compatible_parameter_check(OutVal);
dbms_output.put_line(TO_CHAR(RetVal) || ': ' || OutVal);
END;
/
0: ERROR: --> Compatible set too low "compatible" currently set at and must be set to at least 11.0.0 prior to upgrading the database. Update your init.ora or spfile to make this change.
Performs the following EXECUTE IMMEDIATE 'ALTER TABLE ' ||
t_full_name || ' UPGRADE INCLUDING DATA'; in a cursor loop based on criteria in the cursor's WHERE clause
dbms_preup.depend_usr_tables_fixup(
result_txt IN OUT VARCHAR2,
pSqlcode IN OUT NUMBER)
RETURN NUMBER;
dbms_preup.get_failed_check_xml(
check_name IN VARCHAR2,
substitution_parameter_values IN string_array_t,
detail_type IN VARCHAR2,
detail_info IN VARCHAR2)
RETURN CLOB;
Returns TRUE if memory sizing is specified for the named parameter in the upgrade. Tests sizing for cs_idx, jv_idx, sp_idx, lp_idx, sr_idx, pt_idx, st_idx, and mt_idx
is_size_this_memparam(name IN v$parameter.name%TYPE)
RETURN BOOLEAN;
BEGIN
IF dbms_preup.is_size_this_memparam('jv_idx') THEN
dbms_output.put_line('T');
ELSE
dbms_output.put_line('F');
END IF;
END;
/
Rrecommends re-gathering dictionary stats post upgrade.
Checks if statistics has been taken after upgrade, if not report it and generate the fixup in the postupgrade fixup script, after the fixup run, it will not fail and therefore it will report this check as successfull.
dbms_preup.post_dictionary_check(result_txt OUT CLOB)
RETURN NUMBER;
Checks for stats collection on fixed object tables. If none of the fixed object tables have had stats collected then gather fixed objects stats else do nothing
dbms_preup.pre_fixed_objects_check(result_txt OUT CLOB)
RETURN NUMBER;
Intended to run any internal procedure such as dbms_stats and others, calls execute_sql_statement and returns a boolean to report if the procedure could run
dbms_preup.run_int_proc(
statement IN VARCHAR2,
result_txt IN OUT VARCHAR2,
pSqlcode IN OUT NUMBER)
RETURN BOOLEAN;
If local undo is enabled, each PDB will have its own UNDO tablespace and reported on its respective log,
if disabled all PDB's are sharing same UNDO tablespace which is created in cdb$root.
Upgrade sizes UNDO tablespace based on the default number of PDB's being upgraded in parallel, with undo space size = (total_space x num_pdbs) upgraded in parallel.
dbms_preup.tablespaces_check(result_txt OUT CLOB)
RETURN NUMBER;
Once Ultra Search instance is created the wk$instance table is populated.
The logic determines if Ultra Search has data or not by looking up wk$instance table. WKSYS.WK$INSTANCE table exists when Ultra Search is installed.
If it's not installed, WKSYS.WK$INSTANCE doesn't exist and the pl/sql block raises exception.
dbms_preup.ultrasearch_data_check(result_txt OUT CLOB)
RETURN NUMBER;