Managing Oracle

Remove a lot of data from a table (4 ways)

2025-01-27T10:13:00.004+01:00

Deleting old historical data or test data from a database is one of these DBA jobs that may be boring, but may also need sufficient attention and care.
There are three ways to delete massive amounts of data, one more care full. than others:

Truncate
The easiest and most destructive way of deleting data is truncating a table. A truncate command is like cutting down a tree: It simply cuts away all data from a table and leaves it empty. It is unreversible, there is no UNDO saved.

Truncate has two options to either release all reserved space of a table or to keep it, for reallocation of new data.

SQL> truncate table massive_table drop storage;

This will empty the table and release all occupied storage in the datafiles, to be used by other objects. When new data is inserted into the table, it may be less fragmented, depending on the overal fragmentation of the datafiles.

The other option is:

SQL> truncate table massive_table reuse storage;

This will empty the table, but keep the allocated storage. This is quite use full for tables that hold huge amounts of data during a batch process or repeating process. When storage is reused, the database does not need to reallocate it, when data is inserted into the table again.
The data in the table will be as fragmented as it was before.

There is also an extra option "cascade". This can be added to the statement when foreign keys are used referencing other tables:

SQL> truncate table massive_table drop storage cascade;
CTAS
Create Table As Select is probably the most safe way when deleting huge amounts of data. It can be used to save the data to be deleted into a backup table before a truncate is executed. It is also possible to create a new table with "data to keep only" and use that table instead of the old one, by e.g. renaming it:

SQL> create table data_to_keep as
select * from massive_table
where year >= to_date('01-01-2010','dd-mm-yyyy');

SQL> drop table massive table purge;

SQL> alter table data_to_keep rename to massive_table;

Remember to add Indexes afterwards if they existed and recalculate statistics.

It is also possible to truncate the massive_table and reinsert the saved data back into it:

SQL> create table data_to_keep as
select * from massive_table
where year >= to_date('01-01-2010','dd-mm-yyyy');

SQL> truncate table massive table drop storage;

SQL> insert into massive_table (select * from data_to_keep);

SQL> commit;
Partitioning
Another way to delete massive amounts of data on a more regular basis is the use of partitioning. Partitioning is only available in an Enterprise Edition database and it needs a separaten license.
It requires to think over the partitioning clause. If in the future data is to deleted based on a year, it is not smart to partition e.g. on company name.
It interval-partitioning is not used (see this article), then partitions to be deleted can be found using:

SQL> select 'select count(*) from pohead_his partition ('||partition_name||') ;'
from user_tab_partitions where table_name='MASSIVE_TABLE'
and partition_name like '%2010';

This generates a list of statements to drop the partitions from the table
Filtered Move
Probably the easiest way to delete massive amounts of data the quickest way is a filtered move. In this way the table is rebuild with only the data that is needed to be kept.

The statement to do this, also has options to do this "online" ( Enterprise Edition only) and to update the indexes automatically.
The statement:

SQL> alter table massive_table
move online
update indexes
including rows
where year >= to_date('01-01-2010','dd-mm-yyyy');

This statement rebuilds the massive_table and deletes all data that have a year older than Jan. 1st of 2010.
Since an "alter table move" is a DDL action, an implicit commit is executed.
So, there is no way back. It is as effective as the CTAS option, but way more easier and faster.

This concludes this blog entry. Hopefully it helps

Generate Reset Password statements

2025-01-27T09:00:00.003+01:00

A lot of companies we as DBA's work for have Test- and Development databases that are based on a copy of a Production database. Every time a lower environment databases is refreshed with a copy of production, there are always post-clone steps needed, to convert the production data into test- or development-data. A nice article to make this action easy can be read here.

One of these post-clone steps is a reset of passwords for all user accounts. Passwords on the lower environments should not be the same as on the production database.
Before a test- or development database is refreshed, it is smart to retrieve and keep all passwords of user accounts of the old copy of these databases and restore them after the refresh.

Below a script is listed that generates "ALTER USER..." statements for all users in the database, except the common-users. It is known that resetting a password for a common user can only be done on the CDB level and not on the PDB level, so they are excluded.

The script makes use of a database directory called TMPDIR, that points to '/tmp'. First create this directory:

SQL> CREATE DIRECTORY TMPDIR AS '/tmp';

SQL> GRANT READ,WRITE ON DIRECTORY TMPDIR TO CLONE_USER;

It is assumed the one knows that grants have to be set before one can create a directory, or that one asks someone else (the DBA) to create a directory for him/her. The user CLONE_USER is a fictive user, e.g. the user that performs the post-clone actions.

Before the old copy of the Test-/Development database is destroyed/replaced, run this script, to save the old passwords.

The script:

declare
cursor userlist is
select username
from dba_users
where common = 'NO';
l_usercmd varchar2(500);
l_query varchar(500);
fd utl_file.file_type;
begin
-- Open SQL output file
fd := utl_file.fopen('TMPDIR', 'ResetUserPasswords.sql', 'W');
for r1 in userlist
loop
-- Generate CREATE USER command for every user
l_usercmd := dbms_metadata.get_ddl('USER', r1.username);
-- Reformat query so it can be used to reset the password
l_query :=
trim(
replace(replace(replace(substr(l_usercmd, 1, instr(l_usercmd, 'DEFAULT') - 1), 'CREATE', 'ALTER') || ';', chr(10), '')
, chr(13)
, ''
)
);
-- Write the query into the SQL file
utl_file.put_line(fd, l_query);
end loop;
utl_file.put_line(fd, 'quit;');
utl_file.fclose(fd);
end;

When successfully finished, a script "ResetUserPassword.sql" can be found in /tmp.
Execute this script on the new copy of the Test-/Development database.

Password rollover time

2025-01-22T11:26:00.140+01:00

A new security feature announced for Oracle 23ai has been back ported to 19c as of RU 19.12.
The feature offers the possibility to keep using an old password for a certain period after a new password for an account was set. This feature is called "Password Rollover Time".

And it is great!!

Now whenever an application account gets the password changed, one can postpone the update of the application itself to a moment where downtime can be planned. The same with regular users: If a user changes his/her password and the next day forgot that they did, they still can login with the old password. Sounds pretty cool, but the first question would be:
How about security ?
Allowing users to logon with an old password lowers the level of security, doesn't it?

It does not!!

When a password life time is used, forcing users to change their password after a given amount of time, there is also a grace time set, within which a password must be changed before it expires.

If the user changes his/her password, a kind of extra grace period added for the user or the application owner to have the new password configured in all their applications before the new password becomes valid definitely.

Password Rollover Time is controlled by the profile a user has. The value entered is a numeric value and ranges from 0 to 60 days. When set to the default, being 0, the functionality is disabled.
Setting/Enabling this functionality is done by setting a non-zero value like :

ALTER PROFILE USER_PROFILE LIMIT PASSWORD_ROLLOVER_TIME 10;

This gives a user 10 days to still keep using their old password after a password has been changed.
So the value given is in days. To set the value e.g. to 6 hours enter the value as following:

ALTER PROFILE USER_PROFILE LIMIT PASSWORD_ROLLOVER_TIME 6/24;

Important:
When also the PASSWORD_GRACE_TIME and PASSWORD_LIFE_TIME are used in the same profile, the maximum number or days for PASSWORD_ROLLOVER_TIME can not be greater than the PASSWORD_GRACE_TIME.
This is expected, otherwise password life time would be extended.
Also, if you try to set PASSWORD_ROLLOVER_TIME to a value larger than PASSWORD_GRACE_TIME an error is thrown:

>> ALTER PROFILE KRG_USERS LIMIT

PASSWORD_GRACE_TIME 10

PASSWORD_ROLLOVER_TIME 20

Error at line 1

ORA-02377: invalid profile limit PASSWORD_ROLLOVER_TIME

The timer will start at the first login after a password has been reset. The last date/time when the password was reset, can be seen in the dba_users table.

Also, when a password rollover has been enabled from the profile, the account status for that users changes from "OPEN" to "OPEN & IN ROLLOVER"

An example:

Create a profile that has grace_time, life_time and rollover_time for a password set:

CREATE PROFILE PWROLLTIME LIMIT

PASSWORD_GRACE_TIME 7

PASSWORD_LIFE_TIME 90

PASSWORD_ROLLOVER_TIME 6;

Create a user that uses this profile:

CREATE USER ORCL

IDENTIFIED BY P0wer_Rail

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

PROFILE PWROLLTIME

ACCOUNT UNLOCK;

GRANT CREATE SESSION TO ORCL;

ALTER USER ORCL QUOTA UNLIMITED ON USERS;

Now when the password is changed, either by the user, or by and administrator, the account status in dba_users changes:

SQL> ALTER USER ORCL IDENTIFIED BY ELEC_TR4IN;

SQL> SELECT USERNAME, ACCOUNT_STATUS, PASSWORD_CHANGE_DATE

FROM DBA_USERS WHERE USERNAME='ORCL';

USERNAME ACCOUNT_STATUS PASSWORD_CHANGE_DATE

---------- -------------------------------- --------------------

ORCL OPEN & IN ROLLOVER 22-JAN-25

1 row selected.

Following the created profile, this state will remain for the coming 6 days. After 6 days the account_status will change back to "OPEN" and the old password will not be valid anymore.
If within these 6 days the password of this user is changed again, then first change password is not valid anymore. Within one period of PASSWORD_ROLLOVER_TIME, always the oldest and most new password are valid;

So if we would issue another password change within the 6 days, on the account above:

SQL> ALTER USER ORCL IDENTIFIED BY ORACLE_4U;

then the user can login using the passwords P0wer_Rail and ORACLE_4U.

In the example above these two password will both be valid until Jan 28th.

If in the period between now and Jan 28th the rollover time in the profile is changed, the validness of the passwords is adjusted accordingly.

Restore database from NFS storage

2025-01-09T11:52:00.008+01:00

In this blog the restore of an Oracle database is described. The database was, before it got lost, daily backed up to an NFS mounted storage device. Next to that, every 10 minutes backups of the archived log files were created.

Starting point:

Clean installed VM, no database running
File systems created and sized to needed size
No DB, no spfiles etc. found on the system

Steps:

Set ORACLE_SID
Mount NetApp storage for this database under /mnt/backup/$ORACLE_SID
Find newest backup file that contains the spfile and restore it

ls -lrt /mnt/backup/$ORACLE_SID/c*

If you do not have a valid spfile or init.ora RMAN has the ability to nomount an instance without the requirement of a spfile. This will allow you to restore your spfile from a valid backup.

$ rman target /
( do not connect to the recovery catalog, as it will not find this forcefully started instance)

RMAN> startup nomount force;

To be able to restore the spfile, we do need the DBID for the database to be restored. Use SQLplus and query the DBID from the RMAN catalog;

SQL> select db_id from db where reg_db_unique_name='DBAPRDC';

Return to RMAN and execute the following statements:

RMAN> set DBID=<ID from query>;

RMAN> restore spfile from ‘<file found in ls -lrt command>';

- Shutdown the database and restart it in nomount using the restored spfile and then connect to the RMAN catalog:

RMAN> shutdown immediate;

RMAN> startup nomount;

Quit RMAN and restart using the recovery catalog;

rman target / rcvcat /@tsmora

Restore the controlfiles from the same backup file that held the spfile:

RMAN> restore controlfile from ‘<file found in ls -lrt command>’;

The control files are automatically restored to the correct location.

Restore and recover database

Now it is time to restore and recover the database.
Since we assume that we don’t have anything but the backup of the database, a full recovery is not possible as we are missing the latest redo information from the online redo files. We do have to recovery with the amount of redo we have from archived log backups:

RMAN> alter database mount;

RMAN> restore database;

RMAN> recover database until available redo;

Depending on the database size, this may take a while.

Open the database using RESETLOGS, to let the new redo-log file being created:

RMAN> alter database open resetlogs;

That completes the restore of the database.
Remember that the last couple minutes between the latest archivelog backup and the point in time where database got lost are gone. Data inconsistencies may therefore exist.

Runinstaller -silent on AIX not so silent at all

2024-07-10T15:09:00.004+02:00

A short blog-entry about running an unattended installation of Oracle 19c on AIX. With previous releases we have never seen this issue whereas an unattended installation completed without an issue.
One expect the installation to be silent, not asking anything at all and completing the installation using a given response file.

On 19c however, the runinstaller asks if you have run the rootpre.sh, even if you have specified the “-silent” option.

To solve this problem, echo a “Y” into a pipe and pass it to the runinstaller script, like this:

echo “y” | runinstaller -silent -ignorePrereq -waitforcompletion \
-responseFile oracle-db-software-install.rsp

Central Unified Audit Trail

2024-03-22T13:38:00.006+01:00

With the increasing risk of being hacked, suffering from ransomware attacks, the deployment of and activation of Unified Auditing on databases is highly recommended.

But, when being responsible for a lot of databases, all the Unified Audit data needs to be checked, maintained and where possible archived.

In this blog we will describe the setup of a central repository to be used for unified auditing. This setup is only valid for databases using the multitenant setup, and therefore are running on 12.1 and higher.

Oracle has the DataVault solution available that is also able to collect this kind of information in a central location. With many databases however, a DataVault license can become a costly challenge.
That is why we investigated a manual solution.
The idea of this solution is as in the picture.

All databases have Unified Auditing enabled and send on regular intervals their Audit data to this Central Table in a central database.

What is needed to get this correctly setup:
1) A central database with sufficient free space
2) A user in this database to hold the central repository table

3) A common user in all other databases
4) A database link for this common user, connecting to the central database using the repository user.
5) A procedure to regularly send Audit data to the central repository table.

1) The central database is preferably a Enterprise Edition database with the partitioning option enabled. Collecting Unified Auditing data can make a table grow very fast, especially in a average used environment. Preferably create a seperate PDB for this repository, to prevent other users from being able to read this data.

2) The user in this database needs to have a strong password but no special rights to be able to hold the repository table. CREATE TABLE rights and a tablespace quota are the only rights necessary.
It is recommended to create a partitioned table with the same structure as the CDB_UNIFIED_AUDIT_TRAIL table that resides in the CDB of each database and contains Auditing data for every open PDB. Preferably add two extra columns to this table, for the CDB name and PDB names of the remote databases.

3) In the remote databases create a common user in the root-container. This user is going to collect the Unified Audit data for all PDB's and write this to the central table. It is recommended to send data over per PDB, instead of the whole table at once. Remember that whenever a PDB is closed, the data for that PDB can not be read from this table, so when looping thtough the PDB's you will not miss any data when e.g. a PDB is temporarily down.

What to write to this central repository is depending on what is already there. We use the max(event_timestamp) value found in the destination table to determine what data has to be copied.
The first run will therefore take the most time, as there is no max(event_timestamp) found yet at the destination. A creation script would look like this:

CREATE USER C##AUDIT_AP

IDENTIFIED BY "<some complex password>"

HTTP DIGEST DISABLE

DEFAULT TABLESPACE UNIAUDITD

TEMPORARY TABLESPACE TEMP

PROFILE C##KRG_DB_MGMT

ACCOUNT UNLOCK

;

GRANT CREATE SESSION TO C##AUDIT_AP;

GRANT AUDIT_VIEWER TO C##AUDIT_AP;

ALTER USER C##AUDIT_AP DEFAULT ROLE ALL;

GRANT CREATE DATABASE LINK TO C##AUDIT_AP;

GRANT CREATE PROCEDURE TO C##AUDIT_AP;

GRANT CREATE TABLE TO C##AUDIT_AP;

GRANT SELECT ON CDB_PDBS TO C##AUDIT_AP CONTAINER=ALL;

GRANT SELECT ON CDB_UNIFIED_AUDIT_TRAIL TO C##AUDIT_AP CONTAINER=ALL;

ALTER USER C##AUDIT_AP SET CONTAINER_DATA = ALL FOR CDB_UNIFIED_AUDIT_TRAIL CONTAINER=CURRENT;

ALTER USER C##AUDIT_AP SET CONTAINER_DATA = ALL FOR CDB_PDBS CONTAINER=CURRENT;

ALTER USER C##AUDIT_AP QUOTA UNLIMITED ON UNIAUDITD;

Please note the two lines in bold, that give this user special rights to read these two container tables

4) The common user has its own private database link connecting to the repository database. This makes sure that the repository data is secure from unwanted access by other users.

5) Finally a procedure is needed to regularly copy the audit data over to the repository.

Some remarks on where to pay attention:

- The cdb_unified_audit_trail table differs per database version and even per patchlevel when on 19c. This means that all inserts have to have a complete specication of which colums to take into account.

- Running an inline query in a procedure to get the max(event_timestamp) from the repository for a certain PDB always resulted in a NULL value. We solved this by using an execute immediate, but we were unable to explain the why.

Feel free to send me a message on this topic, if you would like to have a copy of the procedure we have written.

Dataguard Setup

2024-01-12T09:43:00.003+01:00

1. General information

With the ever increasing level of security, the need to keep up to date with the latest patches of the Oracle database also increases.

Patching of an Oracle database becomes easier and easier with the release of Oracle 19c and the AutoUpgrade tool, but that subject is covered in another document.

In this document we describe the setup of a Dataguard environment and the patching of it. At Kramp we use Dataguard for the RMAN catalog database, to ensure that this database is always available. The RMAN catalog database is a very important database, as without it backups of a database can not be created.

Next to the setup of the Dataguard environment we will describe the patching of it. Understandably it differs from patching a Single Instance database, because of the redo apply process.

Note that in normal situations the use of dataguard is a licensed option. Since we however use it here on the database used for the RMAN catalog, which is a free-to-use database, also the use of dataguard is free of charge.

Note that we’re talking here about standard dataguard. Active dataguard where a standby database would be opened up read-only is a must-license feature always.

This document does not cover the contents of the database involved in this setup.

2. Setup of Dataguard

In this section the setup of the dataguard environment is described. In the examples the setup of the test environment for the RMAN catalog database is used for the screenshots and statements.

The basic setup of the environment consists of two Linux servers:

vtl1g002: the primary server, running an Oracle 19c EE database

vtl1g003: the secondary server, running the exact same Oracle version

It is important for a Dataguard setup that both primary and secondary servers run the same Oracle version and patch level.

On the primary server an empty database is created in the CDB/PDB setup called TSMT01C.

The OSuser for this installation is called “tsmt01”, so all file system locations are based on the Kramp standard: /opt/tsmt01/xxx

In the below sub sections the setup is explained in steps.

2.1. Enable archive logging

Dataguard relies on the shipping of redo information from primary server to the secondary server. To support this, both the primary and secondary server databases must have archived log mode enabled.

Start SQLplus on the primary server database and execute the following commands:

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

After enabling archivelog mode, make sure the database is backed up and archived logfiles are cleaned out.

After that the database must be set into Force Logging mode, so that all actions done on the database will result in redo-generation, even if a statement uses the NOLOGGING option.

To enable force logging, execute the following statement:

alter database force logging;

alter system switch logfile;

2.2. Create standby logfiles

Now create standby redo logs on the primary database in case of switchovers. The standby redo logs should be as big as the largest online redo logfile and there should be one extra group per thread. In our case we have 6 online redo file of 200Mb each.

This means we create 7 standby redo logs of the same size. They will be located in a subdirectory of the online log location.

First create this directory on both the primary and secondary server:

$ mkdir /opt/tsmt01/re01/standby

Now we can create standby log file from within SQLplus.

This is only done on the primary database, as we will copy this database in a later step completely to the secondary server:

alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby01.log’ size 200m;

alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby02.log’ size 200m;

alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby03.log’ size 200m;

alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby04.log’ size 200m;

alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby05.log’ size 200m;

alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby06.log’ size 200m;

alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby07.log’ size 200m;

2.3. Initialization Parameters

For a dataguard solution both the primary & standby databases should have the same name (db_name).

They should however distinguish themselves based on db_unique_name.

In our case, the primary database is in the Amsterdam datacentre and will therefore be called TSMT01C_AMS. The standby is located in the Enschede datacentre and is called TSMT01C_ENS.

In this step we will already name the primary instance. After we copy the primary to the standby server, we will rename it then,

alter system set db_unique_name=’TSMT01C_AMS’ scope=spfile;

Bounce the database to activate the above change.

Next parameter to be changed is “standby_file_management”. When set to “AUTO”, it makes sure that a physical change on the primary database, like adding or extending an tablespace is also reflected to the standby database.

This parameter is dynamic, so it does not need a restart (again):

alter system set standby_file_management=AUTO scope=both;

Enable this both on the primary and on standby database as this feature should also work after a failover or switchover.

If this parameter is not set to AUTO is will result in redo-apply on the standby server being unable to apply changes when dataguard is up and running.

If e.g. a tablespace is added on the primary database, the datafile(s) belonging to that new tablespace will not be present on the standby server. If this happens, the below error is seen in the DGMGRL console when looking at the configuration.

DGMGRL>; show configuration;

Configuration - tsmt01c_config

Protection Mode: MaxPerformance

Members:

tsmt01c_ams - Primary database

tsmt01c_ens - Physical standby database

Error: ORA-16766: Redo Apply is stopped

Fast-Start Failover: Disabled

Configuration Status:

ERROR (status updated 37 seconds ago)

2.4. Duplicate the database

If the database is shutdown, then first copy the contents of the spfile into a pfile.

SQL> create pfile from spfile;

File created.

Change directory to the $ORACLE_HOME/dbs directory and copy the pfile to the standby server:

$ scp initTSMT01C.ora vtl1g003:`pwd`

On the standby server copy the created initfile to ‘/tmp’ for use with RMAN as temporary initfile.

$ cp /opt/tsmt01/ora/19.0.0.0/dbs/initTSMT01C.ora /tmp/inittemp.ora

Now start the instance on the standby server in NOMOUNT mode

SQL> startup nomount pfile=’/tmp/inittemp.ora’;

Then use RMAN on the standby server connecting to the primary database as target and to the -to be- standby as auxiliary.

$ rman target sys/<password>@tsmt01c_ams auxiliary sys/<password>@tsmt01c_ens

Issue the following RMAN commands to copy the database:

duplicate target database

for standby

from active database

dorecover

spfile

set db_unique_name='TSMT01C_ENS'

nofilenamecheck;

Note the option “for standby”, telling the duplicate command that this database is used for a standby database. It will thus not change a DBID after the clone.

The duplicate command can ofcourse be extended with db_file_name_convert or log_file_name_convert options, if needed.

Wait for the command to finish before continuing.

Finally create a password file on the standby server, with the same password as used for SYS on the primary database server.

Preferably we copy the password file from the primary server to the standby server, to make sure that it contains the same password.

On primary server:

$ cd $ORACLE_HOME/dbs

$ scp orapwTSMT01C vtl1g003:`pwd`

As soon as the standby database has been created, add the same standby logfiles as on the primary:

alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby01.log’ size 200m;

alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby02.log’ size 200m;

alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby03.log’ size 200m;

alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby04.log’ size 200m;

alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby05.log’ size 200m;

alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby06.log’ size 200m;

alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby07.log’ size 200m;

2.5. Listener.ora

Edit the listener.ora on both the primary and secondary server to contain a static entry for the databases. This is needed for the dataguard broker to be able to access the database, even if it is not open.

Remember that only open databases will automatically register with a listener. When a database is in mounted mode.

Most documentation found on the Internet name a standby database to contain the “_stdby” extension, like “cdb1_stdby”

This is however not really smart, especially if a failover takes place and suddenly the standby database called “cdb1_stdby” suddenly becomes the primary databases.

This is why we name the databases following the physical location.

On the primary database add the following entry to the listener.ora:

SID_LIST_TSMT01 =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = TSMT01C_AMS_DGMGRL)

(ORACLE_HOME = /opt/tsmt01/ora/19.0.0.0)

(SID_NAME = TSMT01C)

(ENVS="TNS_ADMIN=/opt/tsmt01/ora/network/admin")

)

On the standby server add the following:

SID_LIST_TSMT01 =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = TSMT01C_ENS_DGMGRL)

(ORACLE_HOME = /opt/tsmt01/ora/19.0.0.0)

(SID_NAME = TSMT01C)

(ENVS="TNS_ADMIN=/opt/tsmt01/ora/network/admin")

)

Note the use of CAPITALS in the database names. This is important, as it must match how it is used later in the Dataguard Broker setup.

Restart both listener processes:

lsnrctl stop

lsnrctl start

2.6. Tnsnames.ora

Update the tnsnames.ora file on both servers to have entries for both db_unique_names and the static “gloval_name” as defined in the listener.ora. The following entries therefore need to be added to the tnsnames.ora file on both servers:

TSMT01C_AMS =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = vtl1g002.kramponline.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SID = TSMT01C)

)

TSMT01C_ENS =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = vtl1g003.kramponline.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SID = TSMT01C)

)

Note the use of SID i.o SERVICE_NAME as the Dataguard Broker will need to connect to the databases when they are down. In that case the services are not available.

3. Enable the Broker

At this point we are ready to start the Dataguard synchronization process. The first step is to start the Dataguard Broker on both databases.

Since we want to prevent the standby database from becoming its own incarnation, we only start it in “mount” mode.

The primary database can be opened in “open” mode.

3.1. Start the broker process

On the primary database:

$ sqlplus / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

….

Database mounted.

Database opened.

SQL> alter system set dg_broker_start=true scope=both;

On the standby database:

$ sqlplus / as sysdba

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

….

Database mounted.

SQL> alter system set dg_broker_start=true scope=both;

3.2. Create Broker configuration

On the primary server start the dataguard broker to register this server.

$ dgmgrl sys/<password>@tsmt01c_ams

DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Dec 5 16:27:35 2023

Version 19.16.0.0.0

Welcome to DGMGRL, type "help" for information.

Connected to "TSMT01C_AMS"

Connected as SYSDBA.

DGMGRL> create configuration tsmt01c_config as primary database is TSMT01C_AMS connect identifier is TSMT01C_AMS;

Configuration "tsmt01c_config" created with primary database "tsmt01c_ams"

DGMGRL>

Now add the standby database:

DGMGRL> add database TSMT01C_ENS as connect identifier is TSMT01C_ENS;

Database "tsmt01c_ens" added

DGMGRL>

It is important to use capitals in these statements for the database name if there are also capitals in the listener configuration.

If there is somewhere a discrepancy between these configurations, one can expect an ORA-01017 when trying to setup the dataguard configuration.

You can check the configuration using the “show configuration” command:

DGMGRL> show configuration

Configuration - tsmt01c_config

Protection Mode: MaxPerformance

Members:

tsmt01c_ams - Primary database

tsmt01c_ens - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:

DISABLED

Configuration status is “DISABLED” which means that dataguard is not active yet.

We need to enable the configuration to let the standby database become and stay in sync with the primary database.

DGMGRL> enable configuration;

Enabled.

DGMGRL>

This commando may take a while depending on the amount of data that has to be synced.

3.3. Tnsnames.ora

Before this installation can be successfully used and before we can do e.g. a switchover or a failover, clients connecting to this Dataguard database need to be made aware about the existence of two hosts, possibly serving the primary database.

To solve this an extra host address needs to be added to the tnsnames.ora entry for this database. There where a single instance usually only has one address line in tnsnames.ora, for a Dataguard setup you need to add one more.

The tnsnames entry for the above configuration therefor looks like this:

TSMT01 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS=(PROTOCOL=TCP)(HOST=vtl1g002.kramponline.com)(PORT = 1521))

(ADDRESS=(PROTOCOL=TCP)(HOST=vtl1g003.kramponline.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = TSMT01)

)

This way this entry enables the client to probe for which of the hosts is the active primary host and connections will always succeed

3.4. Monitoring

The configuration and activity can basically be monitored from the Dataguard Broker command line. More detailed monitoring and alerting on issues is better done from Oracle Enterprise Manager.

The fastest way to check the status of the Dataguard setup is to use the “show configuration” command from the Dataguard Broker command line.

This looks this:

DGMGR> show configuration

Configuration - tsmt01c_config

Protection Mode: MaxPerformance

Members:

tsmt01c_ens - Primary database

tsmt01c_ams - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:

SUCCESS (status updated 44 seconds ago)

It is important that the status show “SUCCESS”

If one looks in detail to the above screen output, it is visible that it is different from that on the previous page. There the tsmt01c_ams was primary database. Now we have switched roles and tsmt01c_ens is the primary database.

In the next chapter this is explained in detail

3.5. Switchover

From a dataguard setup, the members can switch roles. This can be done manually using a switchover, or automatically in case of a failover.

A failover can occur when e.g. de primary database server physically fails. The broker can in that case decide to promote the standby to be the primary database.

A failover or the setup of it, is not discussed in this document.

A switchover can be convenient in case of maintenance. If e.g. one of the servers needs to be shutdown, patched or something else, the standby database can take over the role of primary database. This makes it possible to shutdown the “before” primary database server.

A switchover making the tsmt01c_ams the primary database server looks like this:

DGMGRL> switchover to tsmt01c_ams;

Performing switchover NOW, please wait...

New primary database "tsmt01c_ams" is opening...

Operation requires start up of instance "TSMT01C" on database "tsmt01c_ens"

Starting instance "TSMT01C"...

Connected to an idle instance.

ORACLE instance started.

Connected to "TSMT01C_ENS"

Database mounted.

Connected to "TSMT01C_ENS"

Switchover succeeded, new primary is "tsmt01c_ams"

DGMGRL>

Confirming the switchover by looking at the resulting configuration shows:

DGMGRL> show configuration

Configuration - tsmt01c_config

Protection Mode: MaxPerformance

Members:

tsmt01c_ams - Primary database

tsmt01c_ens - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:

SUCCESS (status updated 51 seconds ago)

DGMGRL>

3.6. Failovers

With Dataguard it is also possible to let the configuration automatically failover to the standby node in case of a failure of the primary node.

This is called Fast Start Fail Over, of FSFO

To configure this, we should setup a so-called observer-server, a standalone server that is responsible for monitoring the primary database.

In case the observer notices that the primary database is not responding for a couple of seconds, for what kind of reason, it instructs the dataguard broker to switch the configuration to the standby database.

For the current purpose of our Dataguard Setup, we do not need automatic failover, so we skip this configuration for now.

Patching Dataguard

2024-01-12T07:59:00.102+01:00

1. Patching

When patching a database it is normal to bring down the database, patch the ORACLE_HOME and finally run ‘datapatch’ to apply any database changes introduced with the patch to the database.

With Out-Of-Place patching a new ORACLE_HOME is installed next to the existing and the database is started with that HOME.

But how do we do that with Dataguard?

Where we know that Dataguard applies changes done in the primary database onto the standby database using the redo-apply, we can’t patch the Dataguard the regular way.

If we would patch the primary database the usual way, it will apply database changes to the standby database of a patch that may be unknown to the ORACLE_HOME of this standby database.

1.1. The basics

This is why Oracle has introduced the so-called “Standby First Patching method”.

With this method, the ORACLE_HOME of the standby database is patched first. But we can’t run datapatch on this ORACLE_HOME as the database is not open to receive these changes.

So, after we have patched the ORACLE_HOME of the standby server, we switch roles, making the standby the primary. Then we can patch the ORACLE_HOME of the previous primary, now standby server.

Finally we run datapatch on the new primary and switch back.

In steps:

1) Disable redo apply on standby database

2) Shutdown standby database

3) Patch the ORACLE_HOME on the standby database

4) Start the standby database

5) Start the redo apply

6) Perform a switchover. The patched standby database now becomes primary database, but without that datapatch has- run.

7) Now repeat step 1 to 5 for the “new” standby database

8) When finished, switch back roles

9) Finally run datapatch from the primary database.

That concludes the in-place patching of a dataguard environment.

The above steps are only useful when patching an existing ORACLE_HOME, so-called in-place patching.

At Kramp we prefer- however out-of-place patching, where we install a new ORACLE_HOME with the patch already applied.

With out-of-place patching the steps needed to be done are:

1) Install a new patched home on both the primary and secondary server into a new directory.

2) Disable redo apply on standby database

3) Shutdown standby database

4) Copy password file, spfile and dataguard config files from the

5) Change environment to the new ORACLE_HOME

6) OLD_ORACLE_HOME/dbs to the NEW_ORACLE_HOME/dbs

7) Start the database using the new ORACLE_HOME in mount mode

8) Start redo-apply

9) Switchover the primary database role to the new started standby database.

10) Repeat steps 1 to 7 for the former primary database server, now being the standby

11) When ready, run datapatch from the primary database

12) Switchover to let the initial primary database taking this role again

On the next pages both processes are seen with commands and screen output.

1.2. In place patching

In this section the real patch process of in-place patching is discussed.

1) The first step is to disable the redo apply on the standby database. The command to use from SQLplus is:

2) Now we can shutdown the Standby database following step 2:

Note that the “ORA-01109” confirms that we are connected to the standby database.

Stopping the database includes stopping the listener:

3) At this point we assume that we have extracted the patch on a location with sufficient space, e.g. /opt/tsmt01/exp.

Also the OPatch version in the ORACLE_HOME has been replaced with the newest version.

Now we can apply the patches for database and ojvm:

When finished, optionally clean out old replace patch-sets and release updates.

The statements to use are :

opatch util listorderedinactivepatches

This lists the inactive patches and release updates.

You can actually delete them, using the command:

opatch util deleteinactivepatches

4) Then start the listener again.
Also start the standby database in MOUNT mode.

Do NOT run datapatch !!!

5) Start the redo apply services again:

It may, like in this screenshot, give an error is redo apply has already been started.

6) At this point we can switch over to make the current Standby database the Primary database, after which we can repeat the above steps, which we will not show here again

7) So… step 7 is skipped, as it would display everything from step 1 to 5 again

Step 7 finishes with the switchover to the initial primary database again.

8) In step 8 we’re going to run datapatch to apply the database changes that have come with the patch

Step into the ORACLE_HOME/OPatch directory and run ‘datapatch -verbose’:

This process may take a while. Because of the redo apply, both the Primary and Standby database are now supplied with the changes coming with the patch.

1.3. Out of Place patching

With out of place patching the downtime can be even more minimized. This method of patching uses a new patched installation as a new ORACLE_HOME.

The running database from the 1st ORACLE_HOME has to be brought down and can be immediately started from the 2nd ORACLE_HOME.

Because downtime is only very short, it is questionable if a switchover would not result in the same amount of downtime, as there also both databases are briefly down together while switching roles.

1) In this section we assume that the new ORACLE_HOME is already in place.

2) The first step is to disable the redo apply on the standby database. The command to use from SQLplus is:

3) Now we can shutdown the database and the listener

4) Copy all parameter files, both for the database and the dataguard configuration, and the password file to the new ORACLE_HOME/dbs, e.g.

$ cd $OLD_ORACLE_HOME/dbs

$ cp * $NEW_ORACLE_HOME/dbs

5) Set the environment tot the new ORACLE_HOME:

Also change the /etc/oratab and re-login to make sure that also the PATH variable is set correctly. Otherwise binaries from the OLD_ORACLE_HOME will be used to start the database again.

6) Edit the listener.ora file and update the ORACLE_HOME to point to the new location.

Then start the listener again with the new HOME.

Also start the standby database in MOUNT mode.

Do NOT run datapatch !!!

7) Start the redo apply services again:

It may, like in this screenshot, give an error is redo apply has already been started.

8) At this point we can switch over to make the current Standby database the Primary database, after which we can repeat the above steps, which we will not show here again

9) Repeat steps 1 through 7, to relocate the now running standby database to the new ORACLE_HOME

10) Switchover to let the initial primary database take this role again

11) Run datapatch to apply the database changes that have come with the patch

Step into the ORACLE_HOME/OPatch directory and run ‘datapatch -verbose’:

This process may take a while. Because of the redo apply, both the Primary and Standby database are now supplied with the changes coming with the patch.

2. Troubleshooting

In this section, issues encountered during testing and writing of this blog are discussed.

Actually only two issues occurred.

2.1. Listener configuration

The first was during the out-of-place patching where the primary was unable to find the standby database, showing:

If you encounter this error, when having done out of place patching of the standby and the database and listener have been started on the standby server, then you probably have not updated the listener.ora file, having the ORACLE_HOME pointing to the new location in the static listener entry:

If the above is true, this is confirmed in the dataguard broker logfile “drcTSMT01C.log”

There is may say:

Data Guard Broker Status Summary:

Type Name Severity Status

Configuration tsmt01c_config Warning ORA-16607: one or more members have failed

Primary Database tsmt01c_ams Error ORA-16778: redo transport error for one or more members

Physical Standby Database tsmt01c_ens Error ORA-01034: ORACLE not available

The problem is caused by the dataguard broker connecting the the standby listener, which in turn can not find a running database from the old ORACLE_HOME.

To solve this problem, stop the Standby database and listener again, and update the ORACLE_HOME entry in the listener.ora as seen here. After updating the listener.ora restart the listener and database and all should work as expected.

2.2. Switchover failing

During the patch process the configuration is switched over from primary to standby two times. This ensures that the database will remain available for the clients

It may happen that a switchover fails, in restarting the two databases in the correct mode. An error in Dataguard Broker is then seen:

Somehow the switchover was partly done.

Before acting upon this and blindly follow the action step, make sure that you check the other database if it is open in read-write mode and also if it is primary.

This can be done using this query:

SQL> column open_mode format a20

SQL> column database_role format a20;

SQL> set linesize 100

SQL> select open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE

-------------------- --------------------

READ WRITE PRIMARY

It should return the same as seen here. Otherwise manual intervention is needed to really dig in to logfiles to find out where the switchover has gone of track.

If it returns the same output as seen here, you can safely start the standby in mount mode as indicated which will solve the problem.

Refreshable PDBs, a DR setup made easy

2022-11-11T10:34:00.007+01:00

With the introduction of Oracle CDB-PDB architecture, DBA's had to get used to a whole way of thinking about databases. Databases became more flexible, more volatile in a sense that you can do many cool things with PDB's, easier upgrades, cloning etc...

With the release of Oracle 12.2 the cloning of PDB's got extended with a option to refresh a remote PDB with data from a local source PDB. The clone you created in a remote location can now be updated automatically with changes from its local partner (source) by applying redo that is retrieved over a database link.

This is called a Refreshable Clone PDB.

When thinking about these kind of options, DBA's may start to think of Dataguard, the Standby Database solution for Oracle Enterprise Editions.
The Refreshable Clone PDB is much of the same, but then free of any extra costs and very easy to setup and maintain. Even better, the Refreshable Clone PDB also works on Standard Edition, so an Enterprise Edition License is not needed here.
Of course the remote database server must be licensed correctly to run this database.

There are a number of advantages of having a Refreshable Clone PDB:

When the Clone is on another server, you can use it as a Disaster Recovery solution. The RPO is then dependent on the refresh rate you specify when creating the remote clone.
RTO is depending on the amount of work needed, to change the role of the clone to a production status in case of a disaster.
When there is no disaster and when there is sufficient archivelog space on the source database, one can even open up the clone in a "read only" state for reporting purposes, thus unloading the source database. Keep in mind that the clone does not get updated when it is open and thus gets out of sync with its source. The redo can only be applied, when the clone is closed.
Opening a database for reporting like this, hugely increases RTO and RPO.

A Refreshable Clone PDB can either be refreshed automatically or manually. When creating the clone this is specified in the "CREATE PLUGGABLE DATABASE" command. Of course the behavior can also changed afterwards. Manual refreshes can be run anyway, even in automatic refresh mode.

Some requirements before you can create a Refreshable Clone PDB:

A CDB on the remote location must already exist having the same patch version and components as the source CDB
A database link must be created from remote CDB, connecting to the local (source) CDB, using the SYSTEM account
The SYSTEM account in the source CDB must have "CREATE PLUGGABLE DATABASE" rights. In Oracle 12.2, this was the case, however in 19c this right is by default only granted to SYS.
The remote CDB must have the PDB_FILE_NAME_CONVERT parameter set in the spfile, if the directory structure of the clone does not match that of the source.

Example:

In this example we assume that we have two CDBs, a CDB1 as the source and a CDB2 as the remote location. The PDB in the source is called HR_PROD. It is up to you if you want to name the Clone PDB the same. For DR this may be convenient, as this eases a failover. When used for reporting the Clone PDB may get another name.

We assume that you have entered the correct connection string (named CDB1) in the tnsnames.ora on the remote server, pointing to the source server.

We start with creating the database link:

SQL> create database link TO_CDB1
connect to system identified by yourpassword
using 'CDB1';

Now we are creating the Refreshable Clone PDB:

SQL> alter system set
pdb_file_name_convert='/u01/oradata/hrprod','/u01/oradata/hrclone'
scope=both;

SQL> create pluggable database HR_CLONE from HR_PROD@TO_CDB1
refresh mode every 5 minutes;

That's it !

Checking the result shows:

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 HR_CLONE MOUNTED

If you now start monitoring the alert_log of the CDB2, then messages like this will appear:

HR_CLONE(3):Serial Media Recovery started

HR_CLONE(3):max_pdb is 3

2022-11-11T09:23:34.574917+01:00

HR_CLONE(3):Media Recovery Log /u01/oradata/hrprod/fr01/CDB1/archivelog/2022_11_11/o1_mf_1_3270_kpw1ft17_.arc

2022-11-11T09:23:34.618074+01:00

HR_CLONE(3):Media Recovery Log /u01/oradata/hrprod/fr01/CDB1/foreign_archivelog/HR_PROD/2022_11_11/o1_mf_1_3271_kpw1n6gy_.arc

2022-11-11T09:23:34.671022+01:00

HR_CLONE(3):Incomplete Recovery applied until change 104159016 time 11/11/2022 09:23:34

2022-11-11T09:23:34.672222+01:00

HR_CLONE(3):Media Recovery Complete (CDB2)

HR_CLONE(3):Completed: alter pluggable database refres

Some other commands to manage the Refreshable Clone PDB are:

Manual refresh:
SQL> alter pluggable database HR_CLONE refresh;

Change the refresh interval:
SQL> alter pluggable database HR_CLONE refresh mode manual;
or
SQL> alter pluggable database HR_CLONE refresh mode every 30 minutes;

Open the database:
SQL> alter pluggable database HR_CLONE open read only;

Thinking about this, it may be convenient to first put refresh mode to manual, before opening the database. This prevent you from getting lots of errors in the alert_log;

To resume the replication of the redo, the PDB must be closed again and a refresh interval set.

Switchover

Because a Refreshable Clone PDB is not a Dataguard solution, a switchover is something manual, to be performed by the DBA. With Dataguard, failovers can be automated with near zero loss of data.
When however doing a switchover correctly, the latest transactions are taken into account, also the switchover of a Cloned PDB must be possible with near zero dataloss.

We need to take into account that in the above database the refresh rate is set to 5 minutes. This means that every 5 minutes the source database is probed for changes. Whenever we do a switchover in between two such probes / refreshes, then we may loose changes applied in the source database during that interval.

First: Switchover can be done by the database by using an ALTER PLUGGABLE DATABASE .... SWITCHOVER command, but this feature is restricted to Enterprise Edition on Exadata and all editions on Oracle Cloud Services.
To overcome this for on-premise databases a hidden parameter can be set:

SQL> alter system set "_exadata_feature_on"=true scope=spfile;

A test using an on-premise Standard Edition runs into -yet- unresolvable errors, even when setting that parameter. It may be of the Standard Edition, but it may also be that the available documentation on the Internet is incomplete.

A manual switchover however is possible. Whenever we shutdown or loose the source database, we can disable the Refresh Mode on the Clone and open it in Read/Write mode to start using it.
This then looks like this:

SQL> alter pluggable database HR_CLONE refresh;

Pluggable database altered.

SQL> alter pluggable database HR_CLONE refresh mode none;

Pluggable database altered.

SQL> alter pluggable database HR_CLONE open;

Pluggable database altered.

If you do not disable the automatic refresh mode, the pluggable database can not be opened in Read/Write mode and the command errors out.

After opening the PDB in Read/Write mode it can not be reverted to Refreshable Clone.
It has to be recreated.

DIAGNOSTIC & TUNING packs automatically used

2022-10-13T13:48:00.006+02:00

We have configured an Oracle Enterprise Manager report that shows us the use of EE-options where we don't have a license.

What shows up, immediately started to confuse us.
It showed that the DIAGNOSTIC+TUNING option was used in several databases:

When checking the settings from within Oracle Enterprise Manager => Management Pack Usage, it showed that the packs were not in use, the radio-button was not active:

When clicking on the Edit button next to the radio-button, it surprised us to see this:

So it looks to enabled, unless the previous screen did not show that.

When looking at the database itself, we see the following:

SQL> show parameter control_management_pack_access

NAME TYPE

------------------------------------ ---------------------------------

VALUE

------------------------------

control_management_pack_access string

DIAGNOSTIC+TUNING

SQL>

So, it is active.

Checking the spfile it is not defined there. So activation is probably a default.

Then we found this document:

where is states:

So, when installing an Enterprise Edition of Oracle, and you don't have any pack licenses, you should explicitly set this option to NONE.

I myself find this default value very much up to discussion. When a junior DBA installs a database for the company he starts working for, creates unknowingly a huge risk of extra costs when subjected to a License Audit.

The solution is easy, but also easily forgotten when installing a new database release:

SQL> alter system set control_management_pack_access=NONE scope=both;

Using Oracle Wallets

2022-09-14T16:25:00.003+02:00

An Oracle Wallet is a file that contains encrypted credentials for databases. This prevents the use of hardcoded passwords in scripts and/or on the command line.
When using an Oracle Wallet the username and password are retrieved from this file on setting up the connection.

The goal of using a wallet is to increase security. There were scripting in small companies usually contain plain-text passwords, the use of an Oracle Wallet can change this, when e.g. a company increases and more employees are using these scripts.

A small disadvantage of using an Oracle Wallet is that the wallet can contain only one set of credentials per database name.
Since it however uses TNS entries to connect to a database, one can store this one set of credentials per TNS name.
So in the case that you have a database called ORADB, TNS entries for each user group can be created in tnsnames.ora and thus credentials for each group.
Using e.d. credentials for batch jobs and other credentials for an application, two entries in tnsnames.ora would be sufficient to support his, e.g. ORADB_BATCH & ORADB_APP.

Setting up the use of a Wallet is easy. First of course a wallet has to be created. Use the following command to do so:

mkstore -wrl /u01/oradata/wallet -create

The entered location is a directory where the wallet is created. Make sure the directory used exists.
The command will ask you to enter a password. This password is used to encrypt the Wallet. Store this Wallet password somewhere on a safe place. It is needed every time when adding/removing or changing entries in the wallet.

Adding/Deleting or changing entries in a wallet uses a similar syntax. Every type of action you perform on a wallet uses the "mkstore" command.
Adding an entry is done like this:

mkstore -wrl /u01/oradata/wallet -createCredential TNS_NAME USER PASSWORD

Example:

mksore -wrl /u01/oradata/wallet -createCredential ORADB_APP scott tiger

But now how do scripts use the wallet?

First we need to setup sqlnet.ora This file is usually found in the location defined by $TNS_ADMIN. IF it is not present, then create a new file and add the following contents to it:

WALLET_LOCATION =

(SOURCE =

(METHOD = FILE)

(METHOD_DATA =

(DIRECTORY = /u01/oradata/wallet)

)

SQLNET.WALLET_OVERRIDE = TRUE

The WALLET_LOCATION part is self-explaining.
The WALLET_OVERRIDE parameter allows this method to override any existing OS authentication configuration.

When adding an sqlnet.ora file or making changes to it, a listener restart is required to activate it.

This means that switching to the use of a wallet, requires a brief unavailability of the database for the business.

Using the wallet is now easy.
Any connections to the database not using a credential, will use the stored credential in the wallet, e.g.:

sqlplus /@ORADB_APP

will succesfully login to the database.

To manage the wallet over time, the following commands can be needed:

1. List all wallet entries

mkstore -wrl /u01/oradata/wallet -listCredential

2. Show password for specific entry

mkstore -wrl /u01/oradata/wallet -listCredential

Using presented list, use the INDEXnumber , e.g.:

mkstore -wrl /u01/oradata/wallet -viewEntry oracle.security.client.password13

3. Manual entry creation (TNS connection name must be unique) within wallet

mkstore -wrl /u01/oradata/wallet -createCredential TNS_NAME USER PASSWORD

Example:

mkstore -wrl /u01/oradata/wallet -createCredential oradb_app scott tiger

4. Manual entry deletion

mkstore -wrl /u01/oradata/wallet -deleteCredential TNS_NAME

This will delete "all", entries for this TNS_NAME, but since a wallet can store only one credential per TNS_NAME, it will delete that credential

5. Wallet deletion

mkstore -wrl /u01/oradata/wallet -delete

Note that this will delete the complete wallet. All entries are lost !!

6. Modifying an Entry:

mkstore -wrl /u01/oradata/wallet -modifyCredential TNS_NAME USER PASSWORD

The wallet is also usable for e.g. RMAN in combination with a catalog database, like:

$ORACLE_HOME/bin/rman target / catalog /@RMANCAT

DBSAT and the risks for EBS databases

2022-04-29T11:51:00.003+02:00

A couple of months ago we went on investigating the capabilities of Oracle's DBSAT, the DataBase Security and Assessment Tool.

The tool itself if powerful, it generates detailed information about vulnerabilities in your databases. The information is presented in readable HTML format that enables quick handling to reduce seurity risks.

However....

One special point of attention is needed here and we already made a mistake here.
DBSAT recognizes the installation of the so-called "sample schema's" in the scanned database and reports on this detailed, looking like this:

The special point of attention is regarding EBS database, as they have HR and OE schema's but then for real usage !!

The reporting of DBSAT that Sample Schema's are installed in EBS databases, which need to be dropped, is a so-called False Positive.

For EBS database DO NOT DROP these schema's, as it will render the EBS application unusable

CIO mount on AIX and Oracle 19c

2021-11-05T11:17:00.003+01:00

Today we faced a really strange issue.
We are in the midst of upgrading all our databases to the Oracle 19c release, or patching existing installations to the latest patch level.

After working through a lot of databases on Redhat 7.x without any problems, the first database to be upgraded on IBM AIX7.2 was next.
Release installation went fine, patching to the latest level also went without issues.

However when creating an empty CDB to house/upgrade the 12.1 PDB, we ran into strange problems. DBCA threw an error ORA-00205: error identifying control file:

Looking at the file system location where the databases was planned to be, control files were present as expected.
Oracle support suggested to run a DBverify on the file, but that also showed no errors:

$ORACLE_HOME/bin/dbv file=/opt/hisd01/db01/control01.ctl blocksize=16384

DBVERIFY: Release 19.0.0.0.0 - Production on Fri Nov 5 08:54:12 2021

DBVERIFY - Verification starting : FILE = /opt/hisd01/db01/control01.ctl

DBVERIFY - Verification complete

Total Pages Examined : 880
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 0
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 880
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 0

DBverify did not show any problems with the file. All pages were empty, but that is explained by the fact that the control files were newly created.

So, then what is the problem?
Why does the same action on Redhat 7.2 work like a charm, but throws this strange error on IBM AIX ?

For the answer we need to go back in time:

With the older Oracle releases running on IBM AIX, it was common to mount the file systems that contained database files and/or redo-log files with the CIO option. This enabled Concurrent I/O for the Oracle database, bypassing the file system cache.
As of Oracle 10g this requirement has changed.
I must admit that we never spend much attention on this change, as our UNIX system-admins were still delivering us systems with file systems that were CIO-mounted and performance was OK on all servers.

A very detailed explanation about CIO on AIX can be found here:

The first time we hit a problem regarding CIO mounted file systems, was when we copied an existing database-server running on Oracle 11.2.0.4 to a new IBM Storage System (FS7200). As this was a full clone, were nothing on server side changed, we were puzzled by the fact that we could not start the database anymore, after the copy.
Similar errors appeared about the control files that suddenly would be corrupted.
Because it was a full clone, we never suspected the database itself. It had to be in the move to the new storage system. We then decided to remove the CIO option from the mount-point which solved the problem.

The whole problem is related to the fact that the default value of the filesystemio_options parameter has changed with the release of 19c. In Oracle 12c ( 12.1.0.2) the value of this parameter was default set to "async", which enables asynchronous I/O where possible.

In release 19c the value of the filesystemio_options parameter is default set to "none" (on Linux), which disables all I/O performance enhancements.
When upgrading our first databases from Oracle 12c to 19c on Redhat 7.x, we ran into a huge degrade of I/O performance, because of this. The databases suddenly went from Asynchronous I/O to Synchronous I/O.
Investigating the issue, it revealed that this parameter had changed with the new release. We then decided to set it to the recommended value of "setall", which enables Direct I/O and Asynchronous I/O where possible.
I/O speed on Redhat immediately increased back to what we expected, resulting in the fact that we decided to make this value the default for all our databases.

And there the problem with IBM AIX was originating. As the database want to open a file using Direct I/O and the file system is mounted with CIO, the files can not be read.

Conclusion:
- When using Oracle set the filesystemio_options parameter to "setall"
- When using Oracle on AIX, do not mount the file systems with the CIO option (anymore)

Configuring Clients for Database Gateway for Websphere MQ 19c

2020-11-06T11:00:00.009+01:00

In this post, I will try to explain the upgrade of Database Gateway for MQ to version 19c.
It comes with two problems that makes an upgrade from e.g. Database Gateway 12c to 19c a bit more challenging than one would expect.

No support for PDBs

The first problem to address is that Database Gateway setup scripts for the clients do still have no support for Pluggable Databases (PDBs). Already the 12c version scripts, used to deploy the code in a client database had to be manually edited to correct the connection string. Back then this was still understandable as the major bulk of existing client database was a single instance database.
In version 19c, I would expect to see some kind of support for the use of PDBs, but there is not. In both versions you still have to edit the deployment files when using it with a PDB. Otherwise the packages will be installed in the CDB, where they are not accessible from the PDBs.

The first thing that needs to be fixed, is that the pgmdeploy.sql program simply exits without doing a thing. This is caused by the "whenever sqlerror exit" in the beginning of the script, followed by a connection attempt using the "scott/tiger" account, that (should) nowhere exist.
The general way to solve this is to comment out the line that has this connect command. It is around line 65 in the script. Simply put two dashes in front of it, to comment it out.

The second line that needs to be edited is found on line 111 of the pgmdeploy script. There the connection string used, should be appended with the PDB name the installation will take place in.

Change the lines:

PROMPT Verifying SYS Password...
connect SYS/&INPASS2 as sysdba

into

PROMPT Verifying SYS Password...
connect SYS/&INPASS2@<PDB NAME> as sysdba

For the pgmundeploy.sql the same needs to be done on line 91.

Remark: There were the scripts used with a single instance database were able to continue, by simply using the [Enter] key only, with a PDB you actually have to specify a SYS password.

Now the deploy and undeploy scripts should work OK, for Oracle 19c clients, but.....

No support for lower level databases

We all know that Oracle 12.1 is out of support, not even speaking about Oracle 11 databases.
When you because of that, decide to upgrade Database Gateway 12c to Database Gateway 19c, you will run into a major but solvable issue, when the client databases you use are on Oracle 12.1 or even lower.

If you however start using Database Gateway now for the first time and you decide to start with the latest (19c) release, then this issue is not easy to solve.

The problem is that as of release 12.2 ( 19c is 12.2.0.4), the DBMS_STANDARD package has been extended with a new variable ORA_MAX_NAME_LEN and some dependent functions.
Oracle Database Gateway 19c uses this new variable, which does not exist on lower level databases.

This makes the deploy of the PGM packages on Oracle 11 and Oracle 12.1 database impossible.
Only if you have or can get the deploy scripts for an earlier Database Gateway release then you can use Database Gateway 19c

Solution

To use an Oracle Database 11g or 12.1 with Database Gateway 19c, you should use the Database Gateway 12.1 deploy scripts.
As soon as they are deployed, you need to replace the library definition for LIBDG4MQ that has been created, making it point to the new 19c location of the library.

Use a statement similar to this and execute it as SYS in the client database ( PDB or single instance):

create or replace library LIBDG4MQ as
'[your dbgateway_home]/lib/libdg4mqc.so'
transactional;

Afterwards recompile all PGM packages under the SYS schema and your done. Note that the PGM_UTL8 package remains Invalid. This is expected.

DBCA hangs after entering Database Location (19c)

2020-11-03T17:08:00.006+01:00

Today we were installing a new Oracle server on IBM Pseries running AIX 7.2.
The installation was done with the, at this moment, newest release of Oracle, being 19.9

The installation of the base release 19.3 and the release update went successful.

Creating a database however using dbca got stuck after entering the database file location in Step 5.
The trace.log file of dbca only showed:

INFO: Nov 03, 2020 3:29:54 PM oracle.install.commons.util.StatusControl showMessage
INFO: Validating writability of the storage location provided

Since this was a new server and we already had installed multiple servers with this Oracle release but on Redhat Enterprise Linux 7, we suspected the AIX server to be the cause of this problem.

UNIX administrators were involved and together we started searching for the cause.
The file location was writable, the process state using "truss" showed a continues loop of checks, with certain repeating functions like "ptx_get_cpuinfo", "ptx_get_wparsysinfo" and "ptx_get_wparsysstats" etc..etc..

Oracle Support gave some result on these functions, but these results where linked to 'srvctl' and not 'dbca'. After entering a more detailed search description:

"dbca hang database files location"

the first result was Doc ID 2706037.1 with the title:

Database creation via DBCA hangs when the database files location does not include {DB_UNIQUE_NAME}

It turns out that according to this Doc that DBCA as of release 19.3 requires a DB_UNIQUE_NAME in the file location, whenever you deviate from the given default.

Again, on Linux we have never seen this issue, and all our systems have the same non-oracle-default setup.

After appending our default location, with this DB_UNIQUE_NAME, dbca continued as expected.

Conclusion: On AIX it seems that DBCA can't work with a non-default database location, if it does not contain the DB_UNIQUE_NAME.
It does however also not generate an error message to tell you about this.
It simply gets stuck.

However AIX is a very stable and reliable operating system, I tend to move all databases to Linux, since it is more reliable and thrustfull.

Top Activity option in OEM13 gone

2020-10-16T10:53:00.004+02:00

After an upgrade from Oracle Enterprise Manager 12c to version 13c, we noticed that we had lost the "Top Activity" option, that used to be in the Performance Menu of the database page.

Seen here is the Performance Menu of the version 13 of Oracle Enterprise Manager.

Below the "Performance Home" option the "Top Activity" option was available in version 12c

As you can see however, we are still able to access the screen.
It does still exist. It is only that the menu option is gone.
Luckily we kept the old http-link from version 12c, which we had saved as a Favorite in the browser.

Accessing this page was now possible by simply editing this saved link and changing the server name.

For all of you who didn't save this link and also upgraded to the new version, the link looks like this:

https://<your oem13 server>:7803/em/faces/sdk/nonFacesWrapper?target=<SID>&_em.coBM=%2Fconsole%2Fdatabase%2Finstance%2FwaitDetails%3F_em.coIFR%3Dtrue%26event%3DdoLoad%26target%3D<SID>%26type%3Doracle_database%26datasource%3DSQL%26waitClass%3DOverview&type=oracle_database

Replace <your oem13 server> with the hostname of your Oracle Enterprise Manager server and <SID> with the database instance you want to monitor.

Keep in mind that this page requires the Oracle Diagnostic Pack license to access.

If you have the license, you can enable the Diagnostic Pack via Setup => Management Pack => Management Pack Access.

You can also enable it from SQLplus by entering the command:

SQL> alter system set control_management_pack_access='DIAGNOSTIC' scope=both;

ORA-01017 on CONNECT / AS SYSDBA after changing OS user/group

2019-09-26T16:25:00.001+02:00

From an existing database server the database Instance name and the OS-user and -group running the database had to change, as the database itself went from Development environment to Test environment.
The database was called "STID01", its OS-user "stid01" with the group "dbad01"

Everything was running just fine before the change.
It was intended to change the database name to "STIT01" and its OS-user to "stit01", with the OS-group set to "dbat01".
So a very simple change.

We brought down the database and the UNIX admins changed the OS-username, its OS-group and renamed file system mount points also, as they also contain the Dev environment name.

When I got the machine back, I ran a command to find all files on the system that belonged to the old user and changed their ownership to the new username and group.
After that I relinked the Oracle binaries, using the "$ORACLE_HOME/bin/relink all" command.

Finally I updated the /etc/oratab, renamed and edited the initSTID01.ora file to match the new configuration and I also created a new password file.

Now trying to connect to the database, it generated an ORA-01017 error on connect as "/ as sysdba":

[stit01@vtl1g152 oraInventory]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 26 14:50:57 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Connecting as user SYS with its password, entered in the password file did work.
So I started working on renaming the Instance and datafile locations using a generated control file script:

SQL> alter database backup controlfile to trace as '/tmp/crctrlfile.sql';

Afer renaming the database using the generated script the database was opened with the RESETLOGS option.

Now I updated /etc/oratab, /etc/oraInst.loc and the .bash_profile file and reconnected.

Connecting with "/ as sysdba" still generated the same ORA-01017 however

Remembering a similar issue on IBM AIX, I went browsing the Internet.
Looking up things on Google, the main line of thinking in the results was based on the change of the OS-group. The user running the database, or wanting to connect as sysdba without a password should be member of a privileged dba-group set during the installation.

During the installation of this ORACLE_HOME this group was understandably set to "dbad01".
Now that we are part of group "dbat01" this OS authentication fails.

This brought me into looking into the installation and setup of the ORACLE_HOME.
From ORACLE_HOME I ran a "find" command for all files containing the OS-group "dbad01".
Two files came up as suspicious:

./install/chainedInstall/globalcontext.xml
./rdbms/lib/config.c

Especially the first one got my attention as a line in this file looked like:

Changing this file, setting the VALUE to "dbat01", I ran a "relink all" again.
Still no luck, again an ORA-01017 when trying to connect.

So, back to editing the next file.
Editing the file $ORACLE_HOME/rdbms/lib/config.c showed a whole list of variables set to "dbad01". Looking at the location, this file is used for recompiling the library files.

So we "relink all" again....and.... :

sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 26 16:08:01 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to an idle instance.

SQL>

Yes, we fixed it. This confirms that the OS-group is the variable that controls password less authentication at SYSDBA level.

Now this post reads more or less like a "try or die" post, but in fact it wasn't.

All changes before I went looking for the group-definition in the ORACLE_HOME are basic, well understandable changes that have to be done, when the OS-user and OS-group change on an Oracle database server

For the remainder, I had seen and done this before on IBM AIX, but not yet on Linux.

The Post is therefore written following me searching for the right file.

Calculating Table Sizes Oracle

2019-05-17T14:43:00.001+02:00

In this post I will discuss the growth of tables, and the ways to calculate the space used.
For the newbies among us, an Oracle databases exists of tablespaces, the defined space consisting of datafiles on disk, that hold the databases objects, like tables and indexes.

When a table grows, a tablespace can become full. The origin of this document is from one of these occassions where a tablespace is/was full and I was trying to free up space.

But first some theory:

How do tables grow in Oracle?

Well that's an easy question, one would think, but depending on the way a table is used, the table can either grow slowly, grow very fast, or keep approximately the same size.

When, in the last example, a table consists of very volatile data, so many inserts and deletes occur during a certain period of time, the database will use the space freed by the deletes for the new rows.
When there are more rows added than there are deleted, a table will grow, as new rows are added at the end of the table, behind the so-called high-water-mark of the table.
When an application uses a 'direct-insert' method, mostly seen in bulk-loads, the data is inserted after the high water mark anyway, so the database does in that case not look for free space within the existing occupied space.

That about how a table can grow, and of course this covers not all aspects that are involved.
We will address more dependencies, as we discuss the table's actual size.

First we need make a small sidestep and discuss "statistics".

Oracle database statistics contain information about your data. Statistics information consists e.g. of the number of rows in a table, the average length of a row, the spreading of the data in the different columns and more. Via this link, you can find "Oracle Database Statistics for Dummies".

Now that we now, we can find the number of rows in a table on another way than running a "select count(*)..", then we are able to calculate the size of the data within the table.
With that we can make a seperation between the actual physical size and the used size of a table.

Imagine this bowl being a table. When we have calculated statistics on this bowl, we know that there are -approx.- 100 rows (peas) in this table, with an average row-size of 1 pea.

The physical size of the bowl can be compared with the physical size of the table. This physical size consists of a 100 rows (peas) and a lot of free space, in this case air.

If we wouldn't have table statistics, we would not know the occupied size of the table, but only the physical size. In that case we would not be able to tell anything of the tables usage.

However when we do have statistics, we can tell more about the table's actual size. Oracle has options to shrink a table back to its used size, in order to free up space in the database but also to increase performance of the table, especially with inserts. If a table has a lot of free space, most of the time very fragmented, then the database possibly needs to spend lots of time to find a suitable empty spot in the table, where a row that is inserted would fit.
If we shrink the table to its used size, the database does not need to spent much time on looking for a free place and put the row at the end of the table. This is of course valid for so long, as there will be done new deletes again, which free up usable space again.

Now imagine this situation of a huge table, having a lot of free space in it. The below query shows us that the table INVOICES only has 49% of actual used space:

select ds.owner
, ds.segment_name tabname
, ds.bytes
, dt.num_rows
, dt.avg_row_len
, dt.num_rows * dt.avg_row_len usedsize_bytes
, round(dt.num_rows * dt.avg_row_len / ds.bytes * 100,1) "%used"
from dba_segments ds
, dba_tables dt
where dt.tablespace_name = 'DATA'
and ds.tablespace_name = dt.tablespace_name
and dt.owner = ds.owner
and dt.table_name = ds.segment_name;

Result:
OWNER TABNAME BYTES NUM_ROWS AVG_ROW_LEN USEDSIZE_BYTES %used
------- -------- ------------ ---------- ----------- -------------- ----------
FINANCE INVOICES 9768534016 1204707 3976 4789915032 49

Now we can use the 'alter table shrink space' command to reduce the physical size of the table.

SQL> alter table FINANCE.INVOICES enable row movement;
SQL> alter table FINANCE.INVOICES shrink space;

This will take some time, depending on the size of a table, but it will shrink the table to its smallest possible size. That is, when everything goes as expected. In our case it didn't. When running the above query again, the numbers now showed:

OWNER TABNAME BYTES NUM_ROWS AVG_ROW_LEN USEDSIZE_BYTES %used
------- -------- ------------ ---------- ----------- -------------- ----------
FINANCE INVOICES 9753395200 1204707 3976 4789915032 49

The table's physical size decreased just a little, but not as much as expected.
So what causes this behavior, what extra factors can/must be taken into account and why can this table not be brought down to its actual data size ?

The table's definition here is causing the problem itself. The table is setup as following:

CREATE TABLE FINANCE.INVOICES
(
INVOICE_ID NUMBER(10) NOT NULL,
CUSTNO NUMBER(8) NOT NULL,
INVOICE_DESC VARCHAR2(4000 CHAR) NOT NULL,
INV_AMOUNT NUMBER(10)
)
TABLESPACE DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
;

With a database blocksize of 8192, it can easily be determined that only one row of the table fits in a block. The actual available blocksize for row-storage is 8192 - 10% ( pctfree ) = 7372 bytes
The average row length of the table is 3972, the maximum row_length is approx. 4028 bytes (not taking into account UTF encoding or whatever)

So, it is easily calculated that every block of this table only holds one row, and no more. This is confirmed by looking at the number of rows in the table compared to the number of blocks the table occupies:

SQL> select sum(blocks) from user_Extents where segment_name = 'INVOICES'
2> union all
3> select num_rows from user_Tables where table_name = 'INVOICES';

CNT
----------
1190600
1206840

So this table holds approximately 8192 - 3972 = 4220 bytes of free space in each block it allocates.
That is 3400 bytes of free row space + 10% PCTFREE space = 820 bytes !

We can double check if this is correct, by doing the following math:

physical table size / ( db block size * average_row_length) = 4729063200

This value (almost) matches the value of the USEDSIZE in the above table being 4789915032.

Can we optimize these tables in any way at all??

Well, in our case for every row the database needs to read from disk, a whole block needs to read.
Put it the other way around, we only get one row when we read one block from disk.

If we would create a seperate tablespace for this table with 16kB blocksize, then it would make a difference. With still a PCTFREE of 10% we then would be able to store 3 rows in a single block, a 33% decrease of occupied space!!

A small test showed us that with the current setting (8k blocksize), a test table filled with 69000 rows occupies 68608 blocks ( approx. 536Mb )

If we recreate the same table in a 16k blocksize tablespace, then the same table only occupies 23040 blocks ( approx. 360Mb )
This saved 33% of physical diskspace as expected.

Conclusion:

Table growth is not only about rows in a table. Tables that are known for frequent data changes, ( inserts/deletes and updates) can benefit from a shrink every now and then.
However, if this does not bring the expected result as seen above, then the DBA or Data Architects can look at other solutions like different blocksize or maybe a change in the structure of a table.

Using Flashback Data Archive for Changed Data Capture

2018-07-18T16:17:00.003+02:00

What is Change Data Capture?
When a row in a table gets updated the content of the table changes. This is also true when data gets inserted or deleted. With Change Data Capture (CDC) you would like to capture all changes made to a certain amount of data.

Take for example the value of the Dollar on the Stock Exchange. In relation to other coin-values the value of the Dollar can change many times during one day.
If the value is stored in a single row of a table, you only get the current value when looking at it. You never know what happened to it in the period between two look ups.
If you do need to know what happened in the mean time, it can be very useful to track and record all changes that happened on the value of the Dollar and thus on the table containing the value.

With Oracle 9i Change Data Capture was introduced, and it made it possible to track changes to tables and store these changes in a separate table.
Other processes then could "consume" this captured data for further processing. CDC worked by creating triggers on the source tables, that transferred data synchronously to the staging tables. This caused processing overhead and, because it was synchronous, had effect on the applications working on the source tables.

In Oracle 10g Asynchronous CDC was introduced, that captured data from the online redo log files. The triggers that captured the transaction activity were no longer needed and therefore the performance impact was reduced. This redo log capturing of data was combined with Oracle Streams, requiring a certain configuration for each CDC change set.
Unfortunately the Oracle 10g CDC is only available with Oracle Enterprise Editions. Because the changes where only captured from the redo log files after a commit was received, a minor delay is seen between the commit on the source tables and arrival of the changed data.

In Oracle 11g the Oracle Streams functionality still exists but as of Oracle 12c this functionality along with CDC has been deprecated. It has been replaced by Oracle Golden Gate, which comes as a very expensive per core licensed solution.

Oracle Flashback
With Oracle 10g Oracle introduced the Flashback functionality. With Flashback the database recycle-bin was also introduced, enabling Database Administrators to retrieve dropped tables from within the recycle-bin. But the most important functionality of Flashback was the ability to look back in time. Default, depending on the size of the UNDO tablespace and its retention, a DBA was able to e.g restore deleted data from a table shortly after it got deleted.

When flashback was completely enabled on the database, the database would start creating flashback log files, keeping a record of all before-change values of the database up to a certain retention time.
More information on Oracle Flashback Technology can be found here.

Flashback Data Archive
The Flashback functionality in Oracle Database has been extended with Flashback Data Archive in Oracle 11g. With Flashback Data Archive (FDA) it is possible to store all changed data on tables involved for a certain retention period or indefinitely.
There were the standard Flashback functionality writes a before-image of a row-change to a flashback logfile, using FDA this change is written to a table directly related to the table that has configured with FDA.

How does it work:
Flashback Data Archive is not only the name of the functionality. It is also a real archive object in the database that has to be created and that will hold all staging tables containing changed data. The archive is preferably created in its own tablespace to be able to manage the size of the archive.
All is explained by the following example.

First create a tablespace to hold the Flashback Archive:

create tablespace fb_storage
datafile '/u01/oradata/fb_storage01.dbf'
size 100m autoextend on next 100m maxsize 5G;

Within the tablespace a Flashback Archive is created. At this point the decision has to be made if a database default archive is to be created, or a user-personal archive will be the best option.
To create a database default archive, user the tag "default" in the statement and execute it as user SYS:

create flashback archive
default fb_archive
tablespace fb_storage
quota 4 g retention 1 day;

This creates Flashback Arvchive named "fb_archive". The option "default" makes this archive the default archive of the database. It is possible to have more archives, but only one can be the default.
The qouta is set to 4G and the retention of the kept data is set to 1 day;
This quota is the maximum size of the archive in the given tablespace. If the archive becomes full then operations on the tracked tables will fail.
If the archive reaches 90% of its given quota, the database will start issuing "out of space" warnings, thus enabling the DBA to purge old data or increase the archive size.

If a user specific archive is to be created, then this user needs to receive the rights to do so first (as user SYS):

grant flashback archive administer to ehda;
alter user scott quota unlimited on fb_storage;

After this the user can create its own archive:

create flashback archive fb_archive_scott_1day
tablespace fb_storage
quota 4G retention 1 day;

It is recommended to put the name of the owner and the retention into the name of the archive. This makes working with archives easier, especially when more than one exists.

The below query shows the just created archive in its tablespace:

select flashback_archive_name
, flashback_archive#
, tablespace_name
, quota_in_mb
from dba_flashback_archive_ts
order by flashback_archive_name;

In order to use the default FDA on a certain table, rights have to be granted to the user owning that table(s) to use the FDA and its tablespace:

grant flashback archive on fb_archive to scott;
alter user scott quota unlimited on fb_storage;

The above steps are not needed if a user specific archive has been created.

Now connect as user scott and enable flashback archive on a table that has to be tracked:

alter table scott.emp flashback archive;

or for his personal archive, name the archive to be used:

alter table scott.emp flashback archive fb_archive_scott_1day;

Enabling and Disabling a flashback archive functionality on a table can be done by the table owner.
Disabling is done by a privileged user when using a database default archive.

With the next change of data in the scott.emp table, a old-value ( before image ) is stored in the flashback archive.
Keep in mind that there is no "before image" for inserted data, so an inserted row is only seen in the flashback archive if it has undergone an update afterwards.

The general documentation about FDA now would show a query about looking at changed data using the following query:

select * from scott.emp
versions between timestamp sysdate-4/1440 and sysdate;

This will show the contents of the scott.emp table including all the updated rows over the last 4 minutes. If the table started empty and only a couple of rows were inserted and changed, then this overview would still be quite readable. If the table however was already populated with a lot of data, you can only find changed data easily if you know (the primary key values of ) the rows that have changed.
To overcome this, it is also possible to directly query the staging table in the archive which holds only the changed rows. To find this staging table use the following query:

select * from
dba_flashback_archive_tables
where table_name ='EMP'
and owner='SCOTT';

It will return someting similar to this:

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
---------- ---------- ------------------------- -------------------- ----------
EMP SCOTT FB_ARCHIVE SYS_FBA_HIST_7785 ENABLED

Here the archive table name for the SCOTT.EMP table is shown as SYS_FBA_HIST_7785;
Every table that is tracked with FDA has its own archive table.
Note that naming convention of archive tables start with "SYS_FBA". When looking up, knowing this, it shows us that there are in total 3 tables associated with the SCOTT.EMP table in the FDA:

Table	Description
SYS_FBA_DDL_COLMAP_7785	Table that holds a mapping of the columns of the history table to the original table.Because of the administrational overhead, adding or removing a column takes much longer on a tracked table compared to a normal table.
SYS_FBA_HIST_7785	This table hold, as mentioned, the before images of the actual rows that have changed.
SYS_FBA_TCRV_7785	This table shows the STARTSCN & ENDSCN of the changes, inluding the OPeration that was involved (U=update, I=insert, etc..)

The SYS_FBA_HIST_xxxx tables are structural identical to a join of the SYS_FBA_TCRV table and the original SCOTT.EMP table.

Retrieving changed data:
As the archive tables hold the changed data along with SCN numbers, it is not hard to retrieve this data ordered in time, e.g:

select empno,ename,job from SYS_FBA_HIST_7785 order by endscn;

Select the maximum SCN number and retrieve all rows that have been archived before that SCN:

select max(endscn)
from sys_fba_hist_7785;

ENDSCN
-----------------
10640031020116

And then:

select empno,ename,job from sys_fba_hist_7785 where endscn < 10640031020116;

Purging the Flashback archive:
Besides the configured retention which would purge old data from the archive it is also possible to manually purge the data.
According to the Oracle Documentation, purging is possible based on SCN or TIMESTAMP, or by iussuing the command "purge all", deleting all data.

Warning: What the official documentation fails to mention is that there is a bug in Oracle 11.2 and 12.1 which prevents purging the FDA based on SCN or TIMESTAMP. Only in 12.2 this bug is solved (Doc ID 16898135.8).
There is however a one-off patch available for Oracle 11.2.0.3 and and 11.2.0.4.

So until this patch is installed, only "purge all" is a working solution, but that brings a timing problem as to when it is suitable to purge all data looking at the application that might be populating the table in the mean time. One can imagine that between reading the captured data and executing a "purge all", new data might have been archived, so an

alter flashback archive fb_archive purge all;

can possibly lead to data loss if the application that is responsible for populating the table is not shutdown beforehand.

DDL Limitations:
Before explaining on how to disable the tracking of data changes and dropping the archive, one DDL restrictions on tracked tables has to be mentioned.

Dropping the table

Simple, isn't it??
There is documentation found on the Internet, even within well-known Blogs where it is stated that a tracked table can also not be truncated or renamed or undergo column changes.
This is however not true. Only dropping the table is not allowed and will generate an error:

drop table scott.emp;

Error at line 1
ORA-55610: Invalid DDL statement on history-tracked table

Disable / Remove the Flashback Data Archive:

Before an FDA can be removed, all tables that are tracked need to be decoupled, using the statement:

alter table ehda.cohead no flashback archive;

In tests I ran, a common user ( owner of the table ) can enable the flashback archive function on a table, but disabling this generates an error:

ORA-55620: No privilege to use Flashback Archive

A privileged user has to execute this command.

As soon as all tables have be decoupled from the FDA, the FDA can be dropped:

drop flashback archive fb_archive;

After that also the tablespace can be dropped. Make sure before using the below command that the tablespace is really empty.

drop tablespace fb_storage including contents and datafiles;

Conclusion:
I was really excited to learn about this -for me- new functionality. It brings a very easy way to capture changed data. The solution is easy to implement, comes with no downtime, or impact to the application or database.
Unfortunately there are still some bugs that prevent flawless use, unless the database is upgraded to 12.2

Automatic opening pluggable databases

2018-05-22T11:41:00.001+02:00

When upgrading more and more databases from Oracle 11.2 to Oracle 12c we build more and more databases with the 12c CDB model.
Single Instande user databases have been converted to Pluggable DataBases ( PDB).
To make working with CDB's and PDB's easier, Oracle created an option to automatically open the PDB's as soon as the CDB is opened.
This save lots of work and makes automatic restarts from scripts easier.

To achieve this, the state of a PDB can be saved, so that the CDB knows which PDB to open after a startup.
The command to save this state is to be run from the PDB, so assuming that we have a CDB called ORACDB and a PDB called PLUG1, the command to execute would be:

$ export ORACLE_SID=ORACDB
$ sqlplus / as sysdba

SQL> alter pluggable database plug1 open;
SQL> alter session set container=plug1;
SQL> alter plugguble database plug1 save state;

Now, as soon as the CDB is started, the PDB is immediately accessible through the Listener.

ORA-15036: disk '/dev/asmdata01' is truncated

2017-06-14T14:26:00.000+02:00

Today I encountered one of these that you don't want to encounter.
We have this database server running Oracle 12c ( 12.1.0.2 ) on AIX 7.1

This server hosts 4 databases all around 400GB in size.
The databases are located on one ASM LUN connected through SAN storage of 1.5Tb

The need for yet another database on this server made us decide to increase the ASM disk to 2.1 TB and there we ran into problems.
The increased diskspace on Storage level and OS level was not recognized by ASM. It kept showing a size of 1.5 TB.
We had to reboot the server anyway, becuase we also had to increase the memory size and from there on we were in trouble.
Starting ASM went fine, but the disk was not mountable, diplaying the error:

ORA-15036: disk '/dev/asmdata01' is truncated

The disksize on OS level showed it as the expected 2.1Tb, within ASM it showed as 0 Mb !!!

The 2Tb disk-limit was a known limit for databases up to Oracle 11.2.
Since we are on Oracle 12.1 I did not expect to run into this problem.

The diskgroup however was created in the past with an 11.2 ASM and afterwards upgraded to 12.1
The diskgroup compatibility settings were also set to 12.1, but still the 2Tb limit seemed to be in effect.

The only solution we had was:
* Recreate the diskgroup and restore all database from backup, or
* Reduce the disksize (LUNsize) on the SAN and hope it gets reduced from the end of the disk where it just had been extended.

We chose option 2 above option 1.
If option 2 failed, we were any convicted to option 1.

Luckily for us it worked!!
Reducing the LUN to 1.9 Tb, enabled ASM to recognize the disk and mount it.
All databases were OK afterwards, so no data was lost.

There was one step left in the end, whereas the disksize to ASM still was 1.5GTb

SQL> select total_mb from v$asm_diskgroup where name='DB01';

TOTAL_MB
----------

1572864

so we had to resize the disk and in the end all is OK now

SQL> alter diskgroup db01 resize all;

Diskgroup altered.

SQL> select total_mb from v$asm_diskgroup where name='DB01';

TOTAL_MB
----------

2046976

ORA-15067: command or option incompatible with diskgroup redundancy

2017-03-22T16:55:00.004+01:00

At our company we had to replace the storage cabinets of a two node RAC cluster. The RAC cluster uses ASM diskgroups with NORMAL redundancy.
All diskgroups exist of two failgroups, with each failgroup located on one of the storage cabinets, for redundancy purposes, as seen here:

SQL> select distinct a.name , b.failgroup, count(*) numdisks
2 from v$asm_diskgroup a
3 , v$asm_disk b
4 where a.group_number = b.group_number
5 order by name;

NAME FAILGROUP NUMDISKS
------------ --------------------- ----------
DB01 DB01_0000 14
DB01 DB01_1000 14
FB01 FB01_0000 5
FB01 FB01_1000 5

While all disks in FAILGROUPS DB01_0000 and FB01_0000 are on one storage cabinet the command to drop all disks on one 'side' of the ASM diskgroup at once would be the first option to choose:

However:

SQL> alter diskgroup fb01 drop disks in failgroup FB01_0000;

alter diskgroup fb01 drop disks in failgroup FB01_0000

ERROR at line 1:

ORA-15067: command or option incompatible with diskgroup redundancy

Since all data is mirrored across the two failure groups, one would expect to be able to drop one complete set of the mirror, so one complete failgroup.

All can be explained by the redundancy of the diskgroup. Using NORMAL redundancy, the diskgroup expects to have at least one disk available in the first failgroup that can be used as a destination for a failing disk in the other failgroup. This is also seen when querying the REQUIRED_MIRROR_FREE value of the diskgroup:

SQL> SELECT name, type, total_mb, free_mb, required_mirror_free_mb,usable_file_mb FROM V$ASM_DISKGROUP;

NAME TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB

------------------------------ ------ ---------- ---------- ----------------------- --------------

FB01 NORMAL 512000 503598 51200 226199

DB01 NORMAL 819200 801612 102400 349606

The REQUIRED_MIRROR_FREE values are equal to the size of a single disk in these diskgroups as seen here:

SQL> select distinct * from (

2 select b.name, a.total_mb

3 from v$asm_disk a

4 , v$asm_diskgroup b

5 where

6 a.group_number=b.group_number);

NAME TOTAL_MB

------------------------------ ----------

DB01 102400

FB01 51200

So the NORMAL REDUNDANCY prevents us from dropping all disks at once in a failgroup.

Solution:

Drop all disks but one in the failgroup and then first add at least one new disk from the new storage cabinet

After that dropping the last disk from the old storage cabinet should be possible.

Just Another Friday with DBCA

2017-03-17T15:41:00.005+01:00

And then 'dbca' crashes, and you can start all over again....

ORA-12154 running dbca on a 12c RAC Cluster

2017-02-09T08:49:00.002+01:00

When setting up a Oracle RAC cluster on 12.1.0.2 ( in our case patchlevel 170117 ), creating a database using dbca can run into an ORA-12154 unable to complete the database creation.

The setup of an Oracle RAC cluster is that you have first of all the Oracle Grid Infrastructure, the so-called clusterware. This software piece is the cluster, and it is responsible for everything else but the actual RAC database.
Everything else also includes the Listener (SCAN-listener and normal listener). For this matter the default location of an Oracle Installation is used for the listener config file, being:

$GRID_HOME/network/admin

The RDBMS part of the installation is another software installation, with its own default location for SQL-net files, being:

$ORACLE_HOME/network/admin

Now when running 'dbca' and while creating a database, a tnsnames.ora file is being created in the $ORACLE_HOME/network/admin directory for self-reference of this newly created database.
As soon as the database is ready, it tries to register with the clusterware. At that moment the clusterware processes try to connect to the database and fail with an

ORA-12154 TNS Could not resolve service name

The reason for this is, that the clusterware home did not receive the tnsnames.ora information about the created database, resulting in an unresolvable service name.

Solution:

Before starting dbca, add support for the self-reference of the to-be created database into the $GRID_HOME/network/admin location, by creating an empty tnsnames.ora for the rdbms-user and a symbolic link to this file for the grid-user.

dbuser $ cd $ORACLE_HOME/network/admin
dbuser $ echo “NAMES.DIRECTORY_PATH=(TNSNAMES)” > sqlnet.ora
dbuser $ touch tnsnames.ora
dbuser $ chmod g+w . tnsnames.ora

Next:

griduser $ cd $GRID_HOME/network/admin
griduser $ ln –s /opt/oraadmin/network/admin/tnsnames.ora tnsnames.ora
griduser $ ln –s /opt/oraadmin/network/admin/ sqlnet.ora sqlnet.ora

In the above statements also a sqlnet.ora file is being created. This is (in our case) to prevent the grid-user from connecting to the still existing older version of this database by means of an EZCONNECT connection.

Now when dbca creates an entry in the tnsnames.ora file the grid-user is also aware of its existence and the ORA-12154 does not appear

Opatchauto for GI complains about actions.xml missing

2016-11-11T13:37:00.002+01:00

On Oracle 12.1 doing a GI CPU installation it is possible that, when trying to install one of the quarterly patch updates, that 'opatchauto' complains about an actions.xml file missing from one of the patch subdirectories.

In DocID 2086760.1 on Oracle Support this problem is explained to be caused by a too low version of opatchauto.
When using an opatch version 12.1.0.1.7 or higher this problem will be gone.

However, today I ran into the same problem, having opatch version 12.2.0.1.8 !!

After a long search for the cause, I found another error in the logfile, saying:

OPatchAutoBinary hit Binary Exception: oracle.opatchauto.code.binary.BinarySessionException:
ERROR: Unable to register this patch /opt/src/24433148. Select Patch is not valid.

This made me look at the directory structure where the patch was extracted.
There I noticed that I mistakenly had unzipped the patch as root-user, resulting in only root-permission files.

After changing the ownership of the unzipped patch-directory to the grid-owner, the patch installation has successfully completed.

So, permission problems on an unzipped patch directory can cause another problem to arise, making solving it a challenge.