OracleNotes

Tag: ATP

  • Exploring Autonomous Databases – Client Credentials

    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.

  • Exploring Autonomous Database – Init Parameters

    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.