Oracle Cloud: what parameters make ATP different from ADW?


Using the Free Tier of Oracle Cloud I created one Autonomous DB of each type – one Autonomous Transaction Processing and one Autonomous Data Warehouse (Autonomous JSON are not yet available). Then did run

select name, display_value
from v$parameter
where isdefault='FALSE'
order by 1;

on each of the DBs, I got the follow differences (empty means not set):

ParameterATPADW
optimizer_ignore_hintsTRUE
optimizer_ignore_parallel_hintsTRUE
parallel_degree_policyAUTO
parallel_min_degreeCPU
pdb_lockdownOLTPDWCS
pga_aggregate_target3000M5100M
resource_manager_planFORCE:OLTP_PLANFORCE:DWCS_PLAN
result_cache_max_result1
result_cache_modeMANUALFORCE
sga_target8000M3400M

Both the databases (PDB) share the same Container (CDB).

I did check also

select *
from database_properties;

but there are no initial state differences.

Something I found interesting. I had a 2-month old ATP when I created the ADW. Immediately I saw that my old ATP was not using ASM, compared to the ADW, also that ADW was a cluster DB while the old ATP was single instance.

I recreated the ATP to check if this remained. But no. My new ATP was co-located on the same database as the ADW, so parameters are mostly the same as we could see above.

For historical reasons, I leave here the parameter changes between ATP created in June and end-August 2020. For paths, only the differences are highlighted:

parameterATP June 2020ATP August 2020
audit_file_dest/u01/…/u02/…
cluster_databaseFALSETRUE
control_files/u01/…,/u05/…+DATA/…,+RECO/….
db_create_file_dest/u01/app/oracle/oradata+DATA
db_nameFBPOD1feio1pod
db_recovery_file_dest/u05/fra+RECO
de_recovery_file_dest_size571558474874921167764M
diagnostic_dest/u01/app/oracle/u02/app/oracle
drcp_connection_limit600
external_keystore_credential/u01/…/wallets/tde_seps+DATA/encrypt_wallet/tde_seps
gcs_server_processes04
instance_number2
log_archive_dest_1LOCATION=USE_DB_RECOVERY_FILE_DEST MANDATORY
log_archive_dest_state_4ENABLE
log_archive_dest_state_5ENABLE
log_archive_dest_state_6ENABLE
max_pdbs4096
processes2000040000
sga_max_size321G301G
thread2

Leave a comment

Your email address will not be published. Required fields are marked *