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.
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
Deeper customer isolation
Encryption is default enabled, data encryption end to end,stores all data in encrypted format.
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.
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%
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.
Oracle Cloud Infrastructure offers periodically preview versions of the Autonomous Database Oracle Database for testing purposes.
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:
Disable automatic index
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
|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:
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).
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.
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.
This procedure removes the specified file from the specified directory on Autonomous Database.
This procedure creates an external table on files in the Cloud.
This allows you to run queries on external data from Autonomous Database.
This procedure deletes the specified object on object store.
This procedure removes an existing credential from Autonomous Database.
This procedure reads an object from Cloud Object Storage and copies it to Autonomous Database.
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.
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:
Microsoft SQL Server
Load data from generic JDBC data sources too, for example:
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 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.
The request was successfully received and the Autonomous Database will be started.
gET DETAIL OF adb
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.