Category: Cloud Database

Posted on

Exploring Autonomous Databases – Client Credentials

You connect to the autonomous database (ATP or ADW) by downloading the Client Credentials Zip file from the Administration console. 

Service Console -> Administration -> Download Client Credentials

Using SQL Developer, you can connect to the ATP or ADW database by choosing the “Connection Type” as “Cloud PDB” and provide the downloaded zip file name (wallet) as the configuration file name. A wallet contains a collection of files, including the key and other information needed to connect to your database service in the Autonomous Cloud. All communications between your computer and the database server are encrypted.

The downloaded zip file includes the connection information for all the ATP and ADW databases you have created under the cloud account, even though the credential zip file name is “wallet_<DBName>.zip”. 

SQL Developer Screen – Cloud PDB Connection entries from the ATP database credentials zip file

The picture shows connection details for 4 different autonomous databases, though the wallet file name (database name) is DB201810021812. 

When you extract the zip file, and review the tnsnames.ora file, you can find all connection entries (4 services for each ATP database, 3 services for each ADW database). The ADW connection names are <DBNAME>_high,  <DBNAME>_medium, and <DBNAME>_low. The ATP connections are the same, plus  <DBNAME>_parallel.

The predefined service names provide different levels of performance and concurrency for Autonomous Databases. Read about the different service names here.

Posted on

Exploring Autonomous Database – Init Parameters

A CDB in Oracle Cloud could host both ATP and ADW (pluggable) databases (see here). Let’s look at the initialization parameter differences at the PDB level for ATP and ADW.  

ATP has a minimum number of parameters configured, whereas ADW has several initialization parameters. Though my ATP and ADW databases are both 1 oCPU / 1 TB size, the SGA and PGA values are different. ADW has a bigger PGA, ATP has bigger SGA. 

Init ParameterATPADW
pga_aggregate_target3000M5100M
pga_aggregate_limit6000M10200M
sga_target8000M3400M

All the parameters set in the ATP database are set in the ADW database as well. ADW has a lot more, though. Here are the parameters from ADW. The first column shows if the parameter is set in ATP or not. 

ATP?PARAMETER NAMEDISPLAY VALUEDEFAULT VALUE
_cell_offload_vector_groupbyFALSETRUE
_cloud_service_typeDWCSNULL
_datapump_inherit_svcnameTRUEFALSE
_default_pct_free10
_enable_parallel_dmlTRUEFALSE
YES_kd_rows_chkFALSETRUE
_ldr_io_size33554432262144
_ldr_io_size2335544321048576
_max_io_size335544321048576
_optimizer_answering_ query_using_statsTRUEFALSE
_optimizer_gather_stats_on_ load_allTRUEFALSE
_optimizer_gather_stats_on_ load_histTRUEFALSE
_parallel_cluster_cache_ policyADAPTIVEADAPTIVE
_pdb_inherit_cfdTRUEFALSE
_pdb_lockdown_ddl_clauses1310660
_pdb_max_audit_size200M0
_pdb_max_diag_size100M0
_px_xtgranule_size12800010000
YEScpu_count10
YES db_lost_write_protectTYPICALNONE
max_idle_blocker_time50
YES nls_languageAMERICANAMERICAN
YESnls_territoryAMERICAAMERICA
YESofs_threads404
parallel_degree_policyAUTOMANUAL
YESparallel_max_servers124294967295
parallel_min_degreeCPU1
YESparallel_servers_target120
YESpga_aggregate_limit10200M0
YESpga_aggregate_target5100M0
YESplscope_settingsidentifiers:allIDENTIFIERS:NONE
YESplsql_optimize_level22
resource_manager_planFORCE:DWCS_PLANNULL
result_cache_max_result15
YESresult_cache_max_size10M1
result_cache_modeFORCEMANUAL
YESsessions1004294967295
YESsga_target3400M0

Out of the 39 parameters set in ADW, half are underscored parameters!

The ATP and ADW have two different resource management plans. ADW plan name is DWCS_PLAN and ATP plan name is OLTP_PLAN. The (default) tablespaces are same for ATP and ADW.