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):
Parameter | ATP | ADW |
---|---|---|
optimizer_ignore_hints | TRUE | |
optimizer_ignore_parallel_hints | TRUE | |
parallel_degree_policy | AUTO | |
parallel_min_degree | CPU | |
pdb_lockdown | OLTP | DWCS |
pga_aggregate_target | 3000M | 5100M |
resource_manager_plan | FORCE:OLTP_PLAN | FORCE:DWCS_PLAN |
result_cache_max_result | 1 | |
result_cache_mode | MANUAL | FORCE |
sga_target | 8000M | 3400M |
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:
parameter | ATP June 2020 | ATP August 2020 |
---|---|---|
audit_file_dest | /u01/… | /u02/… |
cluster_database | FALSE | TRUE |
control_files | /u01/…,/u05/… | +DATA/…,+RECO/…. |
db_create_file_dest | /u01/app/oracle/oradata | +DATA |
db_name | FBPOD1 | feio1pod |
db_recovery_file_dest | /u05/fra | +RECO |
de_recovery_file_dest_size | 5715584748749 | 21167764M |
diagnostic_dest | /u01/app/oracle | /u02/app/oracle |
drcp_connection_limit | 600 | |
external_keystore_credential | /u01/…/wallets/tde_seps | +DATA/encrypt_wallet/tde_seps |
gcs_server_processes | 0 | 4 |
instance_number | 2 | |
log_archive_dest_1 | LOCATION=USE_DB_RECOVERY_FILE_DEST MANDATORY | |
log_archive_dest_state_4 | ENABLE | |
log_archive_dest_state_5 | ENABLE | |
log_archive_dest_state_6 | ENABLE | |
max_pdbs | 4096 | |
processes | 20000 | 40000 |
sga_max_size | 321G | 301G |
thread | 2 |