Autonomous Database 1Z0-931 Exams Tips in Oracle Cloud Infrastructures

Oracle cloud infrastructure:(OCI)
It is enterprise cloud, capable for running most workload. Highest, most consistent performance Low, predictable pricing Automation, innovation.
Autonomous database is a service which is hosted in OCI.

Oracle cloud infrastructure terms:
Region: is composed of one or more availability domains. It is like a geographically area.
Availability Domain: has one regions only. It is like data-center. In region we have availability domains.
Fault domain: is a grouping of hardware and infrastructure with in availability domain. Each availability domain contain 3 fault domains.
Fault domain use to protect against unexpected hardware failure and protect from planned and unplanned downtime like hardware upgrade, software upgrade etc.

Autonomous database:
It is integration of Oracle database running on EXADATA platform with complete infrastructure automation and fully automated data center operations.
Automated data operation include provisioning , patching, upgrade and online backup, monitoring, scaling , diagnosing , performance tuning ,
optimizing, testing and change management of complex application and workload, automatically handle failure and errors.

Two types of Autonomous Database in OCI:
1. Autonomous Transaction Processing:
2. Autonomous Data Warehouse

Autonomous Transaction database
Workload type sets up the database for a transnational workload, targeting high volumes of random data access.

Database Services of Transaction Database:
DBNAME_LOW: lowest Priority application connection for reporting and batch operations. all operations does not run in parallel.(ATP & ADW)
DBNAME_MEDIUM: typical application connection service for reporting and batch operations. all operation run in parallel (max 4) and subject to queuing. (ATP & ADW)
DBNAME_HIGH: High priority application connection service for reporting and batch operations. all operation run in parallel( max depend upto CPU count) and are subject to queuing. (ATP & ADW)
DBNAME_TP : typical application connection service for transaction processing operation. connection service doesn’t run with parallelism. (ATP)
DBNAME_TPURGENT: Highest priority application connection service for time critical transaction processing operation , manual parallelism. (ATP)

Autonomous Data Warehouse
Workload type configures the database for decision support or data warehouse workload, with a deviation for large data scanning operations.

Database Services of Datawarehouse database:
HIGH: The High database service provides the highest level of resources to each SQL statement resulting in the highest performance, but supports the fewest number of concurrent SQL statements.
MEDIUM: Medium database service provides a lower level of resources to each SQL statement potentially resulting a lower level of performance, but supports more concurrent SQL statements
LOW: The Low database service provides the least level of resources to each SQL statement, but supports the most number of concurrent SQL statements. Any SQL statement in this service can use a single CPU and multiple IO resources in your database.

Automatic managed services included
–Backing up the database (Retention is 60 days automatic backup)
–Patching the database (Patching: patching is done once in quarter. It not effect availability of DB.)
–Upgrading the database
–Tuning the database

Security:
Deeper customer isolation
Encryption is default enabled, data encryption end to end,stores all data in encrypted format.
network protection
verifiable security
all connection with authentication and SSL certificate
encrypted store key in wallet
In ADB , no login allow to OS, no sysdba or root login allow.
Auditing is enabled by default in autonomous database etc login failures, changes to users,grant creation etc.

Benefits
Innovate faster with lower cost.
cut runtime cost to 90%
Eliminate the full stack administrator cost.
Ensure data safety
eliminate cyber attack
server level objective t0 99.95%

Automatic tuning:
ADB is a completely self tuning service.
Physical schema tuning is not required and not recommended
Table do not need to be partitioned or configured for in memory
Indexes do not need to be created. In Oracle 19c, Automatic index is used to create secondary indexes not primary indexes.(default disabled)
compression doesn’t need to be specified
tablespace do not be created.

Autonomous Database Free Service
Free The Autonomous Databases have 8 GB of memory, 20 GB of storage, 1 OCPU and can be configured to workloads Autonomous Transaction Processing or Autonomous Data Warehouse.
we can configure up-to 2 ADB.

Preview version:
Oracle Cloud Infrastructure offers periodically preview versions of the Autonomous Database Oracle Database for testing purposes.

Exam Tips:
1. you can scale the number of CPU cores or the storage capacity of the database at any time without affecting availability or performance.
2. Wallet can be downloaded from service console or using REST APIs
3. There is no need to configure or manage any hardware or install any software.
4. Database Services of Autonomous Data Warehouse:
The basic characteristics of these consumer groups are:
HIGH: Highest resources, lowest concurrency. Queries run in parallel.
MEDIUM: Less resources, higher concurrency. Queries run in parallel.
LOW: Least resources, highest concurrency. Queries run serially.
5. Autonomous Data Warehouse ignores optimizer hints and PARALLEL hints in SQL statements by default
For enable hints and parallel:
ALTER SESSION SET OPTIMIZER_IGNORE_HINTS=FALSE;
ALTER SESSION SET OPTIMIZER_IGNORE_PARALLEL_HINTS=FALSE;
6. Auto Indexing is disabled by default in Autonomous Database.
Enable automatic index:
EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’IMPLEMENT’);
Disable automatic index
EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’OFF’);
7.
DBMS_CLOUD: PL/SQL package DBMS_CLOUD provides support for loading data from text, Parquet, and Avro files in the Cloud to your tables in Autonomous Data Warehouse.
DBMS_CLOUD supports loading from files in the following cloud services: Oracle Cloud Infrastructure Object Storage, Oracle Cloud Infrastructure Object Storage Classic, Azure Blob Storage, and Amazon S3.
Monitoring dataload with SELECT table_name, owner_name, type, status, start_time, update_time, logfile_table, badfile_table FROM user_load_operations
8.JDBC Thin Connections with an HTTP Proxy:
you need to use the JDBC Thin Client 18.1 or higher which enables connections through HTTP proxies.
To connect to Autonomous Data Warehouse through an HTTPS proxy, open and update your tnsnames.ora file.
Add the HTTP proxy hostname(https_proxy) and port (https_proxy_port) to the connection string.
9. Monitoring ADB
Cloud console is easiest way to monitor the ADB database.
Overview page: Storage, CPU utilization, running SQL statements, avg SQL statement response time, SQL executed/seconds.
Oracle cloud -> Service console –> graphics to see Storage used,graphic CPU utilization of last 8 days, avg running SQL of last 8 days,
10. Gather statistics automatically
Gathers statistics is enabled in ATP and run in standard windows. ADW also gatherstats during batch loads.
11. These services map with resource manager consumer grouping
RUN AWAY QUERY MANAGEMENT: (means stop the executed query if it taking more time as specified)
SET as ADB Service console or CS_RESOURCE_MANAGER package.
user can specify the limit of consumer groups: 1. statement runtime is greater than 2. amount of IO is greater than
Statement exceeding the limit is cancel but session remains connected.
Service console –> Administration –> Set resource management plan –> Consumer group(high, medium , low) you modified it according your need.
12. DBMS_HIERARCHY package:
Create_validate_log_table: Create a table that you can use of logging messages generated by validate_hierarchy and validate_analystice_view.
validate_analystic_view : validates that the data in a table is suitable for use by an analytic view.
validate_check_success : indicates whether a prior call to VALIDATE_HIERARCHY or VALIDATE_ANALYSTIC_VIEW was successful or produced validation errors.
validate_hierarch : validate the data in a table is suitable for use by a hierarchy.
13. Workflow for OML:(Macine Learning) in sequence:
a. Create ORacle MAchine learning user Service console–>Administration — > Manage ORacle ML Users
b. Create workspaces
c. create projects
d. create notebooks
e. Run SQLSCript/statements.
14. Permission in ML for workflow:
You need to give permissions manager, viewer and developer as:
Project is a container for organizing the notebooks
Notebook wehere you write code, equation and text

	Work space	Projects	Notebooks
MANAGER	read only	create|update|delete	create|update|delete
DEVELOPER	read only	read only	create|update|delete
VIEWER	read only	read only	read only

15. Data visualization desktop is only supporting Operating system: Windows , Sierra
16. DBMS_CLOUD package and its functions details:
PUT_OBJECT Procedure
This procedure copies a file from Autonomous Database to the Cloud Object Storage. The maximum file size allowed in this procedure is 5 gigabytes (GB).
CREATE_CREDENTIAL Procedure
This procedure stores cloud service credentials in Autonomous Database.
Use stored cloud service credentials to access the cloud service for data loading
, for querying external data residing in the cloud, or for other cases when you use DBMS_CLOUD procedures with a credential_name parameter.
VALIDATE_EXTERNAL_TABLE Procedure
This procedure validates the source files for an external table, generates log information,
and stores the rows that do not match the format options specified for the external table in a badfile table on Autonomous Database.
DELETE_FILE Procedure
This procedure removes the specified file from the specified directory on Autonomous Database.
CREATE_EXTERNAL_TABLE Procedure
This procedure creates an external table on files in the Cloud.
This allows you to run queries on external data from Autonomous Database.
DELETE_OBJECT Procedure
This procedure deletes the specified object on object store.
DROP_CREDENTIAL Procedure
This procedure removes an existing credential from Autonomous Database.
GET_OBJECT Procedure
This procedure reads an object from Cloud Object Storage and copies it to Autonomous Database.
LIST_FILES Function
This function lists the files in the specified directory.
The results include the file names and additional metadata about the files such as file size in bytes, creation timestamp, and the last modification timestamp.
LIST_OBJECTS Function
This function lists objects in the specified location on object store.
The results include the object names and additional metadata about the objects such as size, checksum, creation timestamp, and the last modification timestamp.
17. Maximum limit up to 128 CPUs and 128TB can be provisioned from the cloud console. For more you require to call Oracle.
18. DATA SYNC:
Use Data Sync to upload, and manage data. You can load data from files (CSV and XLSX), various relational sources (tables, views, SQL statements),
OTBI, JDBC data sources, and Oracle Service Cloud. You can load to relational tables or data sets.
Data Sync when you want to:
Load data sources other than Oracle.
Load a combination of data sources, such as CSV, XLSX, and Oracle relational files.
Perform incremental data loads or rolling deletes.
Perform insert-only or append strategies.
Merge data from multiple sources.
Transform your data (if you’re using Database As A Service or an on-premises database that is configured using the ‘Oracle (Thin)’).
Schedule data loads. You can replace, append, and update data in tables by scheduling data loads and using the Load Strategy option of this utility.

Use Data Sync to load data from these databases:
Oracle
DB2
Microsoft SQL Server
MySQL
Teradata
TimesTen
Load data from generic JDBC data sources too, for example:
Greenplum
Hive
Impala
Informix
MongoDB
NetSuite
PostgreSQL
Redshift
Salesforce
Sybase

19. Database Service of Autonomous Transaction Processing:
tpurgent: The highest priority application connection service for time critical transaction processing operations. This connection service supports manual parallelism.
tp: A typical application connection service for transaction processing operations. This connection service does not run with parallelism.
high: A high priority application connection service for reporting and batch operations. All operations run in parallel and are subject to queuing.
medium: A typical application connection service for reporting and batch operations. All operations run in parallel and are subject to queuing. Using this service the degree of parallelism is limited to four (4).
low: A lowest priority application connection service for reporting or batch processing operations. This connection service does not run with parallelism.

20. ANALYTIC Views permissions:
CREATE ANALYTIC VIEW
Create an analytic view in the grantee’s schema.

CREATE ANY ANALYTIC VIEW
Create analytic views in any schema except SYS.

CREATE ATTRIBUTE DIMENSION
Create an attribute dimension in the grantee’s schema.

CREATE ANY ATTRIBUTE DIMENSION
Create attribute dimensions in any schema except SYS.

CREATE HIERARCHY
Create a hierarchy in the grantee’s schema.

CREATE ANY HIERARCHY
Create hierarchies in any schema except SYS.

ALTER ANY ANALYTIC VIEW
Rename analytic views in any schema except SYS.

ALTER ANY ATTRIBUTE DIMENSION
Rename attribute dimensions in any schema except SYS.

ALTER ANY HIERARCHY
Rename hierarchies in any schema except SYS.
DROP ANY ANALYTIC VIEW
Drop analytic views in any schema except SYS.

DROP ANY ATTRIBUTE DIMENSION
Drop attribute dimensions in any schema except SYS.

DROP ANY HIERARCHY
Drop hierarchies in any schema except SYS.

21. RESTAPI:
StartAutonomousDatabase DATABASE
​POST /20160918/autonomousDatabases/{autonomousDatabaseId}/actions/start

Status: 200
The request was successfully received and the Autonomous Database will be started.

StopAutonomousDatabase DATABASE
​POST /20160918/autonomousDatabases/{autonomousDatabaseId}/actions/stop

DeleteAutonomousDatabase DATABASE
​DELETE /20160918/autonomousDatabases/{autonomousDatabaseId}

ChangeAutonomousDatabaseCompartment DATABASE
​POST /20160918/autonomousDatabases/{autonomousDatabaseId}/actions/changeCompartment

UpdateAutonomousDatabase DATABASE
​PUT /20160918/autonomousDatabases/{autonomousDatabaseId}

GetAutonomousDatabase DATABASE
​GET /20160918/autonomousDatabases/{autonomousDatabaseId}

gET DETAIL OF adb

ListAutonomousDatabases DATABASE
​GET /20160918/autonomousDatabases

22. Credentials zip file available for download once ADB is created. the zip file contains a collection of files:
The zip file includes the following:
– tnsnames.ora and sqlnet.ora: Network configuration files storing connect descriptors and SQL*Net client side configuration.
– cwallet.sso and ewallet.p12: Auto-open SSO wallet and PKCS12 file. PKCS12 file is protected by the wallet password provided in the UI.
– keystore.jks and truststore.jks: Java keystore and truststore files. They are protected by the wallet password provided while downloading the wallet.
– ojdbc.properties: Contains the wallet related connection property required for JDBC connection. This should be in the same path as tnsnames.ora.
– README: Contains wallet expiration information

23. Terraform open source orchestration tool can be used to provision autonomous database resources in Oracle Cloud Infrastructure.