Hybrid Dataguard is a relatively new 19c feature that allows to have one side of a Dataguard configuration not encrypted. This is particularly useful when having a primary on premises and standby on a OCI, where tablespaces need to be encrypted. The main point is that it allows to spare a Advanced Security licenses on premises site.
Usages of Hybrid Dataguard can be during migration to the cloud or just as a high availability solution, when we have a small on-premises data center.
In the tutorial below I assume to have a primary database on premises without TDE, a we will create a standby in OCI with encryption enabled. The connection between on-premises and OCI is already configured and not explained.
TDE – Transparent Data Encryption – is the Oracle solution for protecting data at rest. This refers, protecting data that is stored in one file or one disk. This data will be encrypted. Only after the database is open and the wallet password is given, you can query the data and see it.
Recently I’ve been working more with ExaCC and migrating databases from on-premises to the Cloud. Took quite some days to get into TDE and sometimes confusing terms used in the documentation and on the web.
This post summarizes all the concepts to have in mind when working with Oracle encrypted databases, which is the default in the Cloud and Exadata systems.
Man shall pay only for what it uses. This is also a motto of the Cloud and Oracle with second-level billing pushes this model.
Concerning disk space, however, it is not always easy. While terabyte prices are getting cheaper, sometimes you make a big cleanup of your database and then you would like to pay only for what is being used.
On Oracle Autonomous Databases it is the sum of datafiles size that counts.
Image now that you have a huge table and then drop it. The datafile space is not recovered.
In order to recover space you need:
Purge the recycle bin:
SQL> purge dba_recyclebin
reduce size of DATA tablespace datafile
SQL> alter database datafile <file_id> resize yyyM;
Now, this will be possible only if there are no extents used at the end of the datafile. Otherwise, one can try to alter table <table_name> move online; and then alter tablespace <tbs_name> coalesce; but this is not sure to help.
During my tests I only had one table, which made things easier.
Let’s hope that Oracle either changes the way to calculate the space used or provides a way to (continuous) defragment a datafile and make the size dynamic.
To check the storage used on Autonomous Database and find the datafile file_id, you can run the following query:
-- Get Space used by tablespace and file_id
select TBS "File_ID-Tablespace",
round(sum(bytes)/1024/1024/1024,2) USED_GB,
round(sum(bytes)/max(PROPERTY_VALUE)*100) PCT
from
(select file_id||'-'||tablespace_name TBS, bytes
from DBA_DATA_FILES
where tablespace_name!='SAMPLESCHEMA'),
(select PROPERTY_VALUE
from DATABASE_PROPERTIES
where PROPERTY_NAME = 'MAX_PDB_STORAGE')
group by rollup(TBS);
FILE_ID-TABLESPACE USED_GB PCT
------------------ ------- ---
3252-SYSTEM 0.41 2
3253-SYSAUX 3.16 16
3254-UNDOTBS1 0.44 2
3255-DATA 0.1 0
3256-DBFS_DATA 0.1 0
4.2 21
-- Get Total space used by DB
select round(USED_BYTES/1024/1024/1024,2) USED_GB,
round(MAX_BYTES/1024/1024/1024,2) MAX_GB,
round(USED_BYTES/MAX_BYTES*100,2) PCT_USED
from
(select PROPERTY_VALUE MAX_BYTES
from DATABASE_PROPERTIES
where PROPERTY_NAME = 'MAX_PDB_STORAGE'),
(select sum(BYTES) USED_BYTES
from DBA_DATA_FILES
where TABLESPACE_NAME != 'SAMPLESCHEMA');
USED_GB MAX_GB PCT_USED
------- ------ --------
4.2 20 21.01
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:
It was nice to see that most of them offer either a powerpoint or icon set that you can use for your presentations. Here is the link for the ones I use:
Oracle and Microsoft announced in June 2019 a cloud interoperability partnership which enables workloads across Microsoft Azure and Oracle Cloud. By creating a first joint multi-cloud solution, the software giants can each continue to provide the best of their services. At the same time customers do not need to decide which vendor they opt-out when moving their on-premises constructs.
Being myself an Oracle Database Administrator, this article aims to check what is the impact of distributing resources in multiple clouds, with databases remaining on Oracle Cloud Infrastructure.
It is not aim of the article to discuss the costs of resources on any of the clouds.
These days I’ve been playing with Oracle Cloud and Azure. It is best practice to have a bastion or jumphost server with public IP address and all the rest on private networks, only accessible internally.
Also, there is no use of passwords and only the ssh public key of the user is located on the servers. Usually this is what we try to do:
To start with, I use MobaXterm to access my servers. There I’ve configured my SSH private keys to be loaded:
Connect to jumphost server and take SSH key
Use the -A option when connecting:
ssh -A opc@bastion-server
Then you can do ssh to the next server in the private subnet without password. Example of not using and using this option:
Connect to private server via jumphost directly
For this we use both -A and -J options:
ssh -A -J opc@bastion-server opc@private-server
SCP directly to private server via JumpHost
Here we have to use a ProxyJump option of scp in this case:
