Category: General

Today I noticed that the changes to the SYS user’s password were not being reflected in the ptime column of SYS.USER$. While this may be trivial, this column can be used to identify passwords that have not been changed recently. This issue is documented by Oracle in “After Changing SYS Password, DBA_USERS.EXPIRY_DATE Not Updated For SYS User (Doc ID 2518310.1)” and Bug 28538439. In summary, after applying the July 2020 patch, you need to apply Patch 28538439 and perform the below steps:

This was performed on a RAC database running Release 12.1.0.2.0.

After changing the SYS user’s password, the SYS.USER$ table displayed the below information:

set line 300
col name format a15
SELECT    name, ctime, ptime
FROM      sys.user$
where     name like 'SYS%'
order by  name
;  
 
NAME            CTIME     PTIME
--------------- --------- ---------
SYS             07-AUG-18 10-JUN-19 <-- Incorrect date

Check to see if patch 28538439 has been applied:

opatch lsinv -all | grep 28538439
     21117072, 22611167, 23182225, 25139545, 
     26196152, 27487279, 28538439 <-- Patch has been applied

The solution is to set the below parameter to “TRUE”:

show parameter _enable_ptime_update_for_sys;
 
No output is displayed, indicating that the 
parameter has not been set

Note that this parameter cannot be set by an ALTER SYSTEM command. It has to be present in the spfile at startup.

Create a temporary pfile from the spfile:

create pfile='/tmp/PRD1_pfile.txt' from spfile='+ORA_DATA_1/PRD1/PARAMETERFILE/spfile';

Edit the temporary pfile and add in the parameter:

vi /tmp/PRD1_pfile.txt
 
add the below line
 
*._enable_ptime_update_for_sys = TRUE

Stop the database and check the status:

srvctl stop database -d PRD1 -o immediate
 
srvctl status database -d PRD1

Start up one node of the RAC in nomount to recreate the spfile from the pfile:

sqlplus / as sysdba
 
startup nomount pfile='/tmp/PRD1_pfile.txt';
 
create spfile='+ORA_DATA_1/PRD1/PARAMETERFILE/spfile' from pfile='/tmp/PRD1_pfile.txt';
 
shutdown immediate;

Start up the RAC (all nodes) and check the status:

srvctl start database -d PRD1
srvctl status database -d PRD1

Check that the parameter to resolve Bug 28538439 has been set:

show parameter _enable_ptime_update_for_sys;
 
Output will be:
 
NAME                         TYPE    VALUE
---------------------------- ------- -----
_enable_ptime_update_for_sys boolean TRUE

This is not retroactive to the previous change to the SYS user’s password. The next time the SYS user’s password is changed, it will be reflected in the SYS.USER$ table:

set line 300
col name format a15
SELECT    name, ctime, ptime
FROM      sys.user$
where     name like 'SYS%'
order by  name
;  
 
NAME            CTIME     PTIME
--------------- --------- ---------
SYS             07-AUG-18 03-DEC-20 <-- Correct date

I had a need to turn on auditing of SYS operations and FGA on some tables. Additionally, I preferred to have the audit destination be the DB. I had moved bot the SYS.AUD$ and the SYS.FGA_LOG$ out of SYSAUX to their own tablespace. After collecting some data, I was curious the see if compressing the tables would allow me to store more data. This was on a 12.1.0.2.0 – 64bit Unix environment.

Run the GET_COMPRESSION_RATIO procedures as follows:

SET SERVEROUTPUT ON
DECLARE
  l_blkcnt_cmp    PLS_INTEGER;
  l_blkcnt_uncmp  PLS_INTEGER;
  l_row_cmp       PLS_INTEGER;
  l_row_uncmp     PLS_INTEGER;
  l_cmp_ratio     NUMBER;
  l_comptype_str  VARCHAR2(32767);
BEGIN
  DBMS_COMPRESSION.get_compression_ratio (
    scratchtbsname  => 'USERS',
    ownname         => 'SYS',
    objname         => 'AUD$',
    subobjname      => NULL,
    comptype        => DBMS_COMPRESSION.comp_advanced,
    blkcnt_cmp      => l_blkcnt_cmp,
    blkcnt_uncmp    => l_blkcnt_uncmp,
    row_cmp         => l_row_cmp,
    row_uncmp       => l_row_uncmp,
    cmp_ratio       => l_cmp_ratio,
    comptype_str    => l_comptype_str,
    subset_numrows  => DBMS_COMPRESSION.comp_ratio_allrows,
    objtype         => DBMS_COMPRESSION.objtype_table
  );

  DBMS_OUTPUT.put_line('Number of blocks used (compressed)       : ' ||  l_blkcnt_cmp);
  DBMS_OUTPUT.put_line('Number of blocks used (uncompressed)     : ' ||  l_blkcnt_uncmp);
  DBMS_OUTPUT.put_line('Number of rows in a block (compressed)   : ' ||  l_row_cmp);
  DBMS_OUTPUT.put_line('Number of rows in a block (uncompressed) : ' ||  l_row_uncmp);
  DBMS_OUTPUT.put_line('Compression ratio                        : ' ||  l_cmp_ratio);
  DBMS_OUTPUT.put_line('Compression type                         : ' ||  l_comptype_str);
END;
/

The output looks like:

Number of blocks used (compressed)       : 252
Number of blocks used (uncompressed)     : 459
Number of rows in a block (compressed)   : 70
Number of rows in a block (uncompressed) : 38
Compression ratio                        : 1.8
Compression type                         : "Compress Advanced"

This indicates that we can store about twice the number of rows in the same amount of space.

I had a requirement to track the activity on a list of tables and decided to try of Fine Grained Auditing (FGA). This was done in a 12.1.0.2.0 – 64bit Unix environment.

Note that the SYS user is not audited by FGA.

Setting up FGA

Check the location of the audit trail

show parameter audit;

If not set to DB, change as follows (11g):

alter system set audit_trail='DB_EXTENDED' scope=SPFILE sid='*';

In 12c the command seems to have changed to:

alter system set audit_trail=DB,EXTENDED scope=SPFILE sid='*';

Bounce the database

The tables to contain the auditing data are created in SYSAUX. It is good practice to move them to their own table space.

SELECT   table_name
        ,tablespace_name 
FROM     dba_tables 
WHERE    owner='SYS' 
  AND    table_name IN ('AUD$','FGA_LOG$')
;

The standard auditing data is written into SYS.AUD$ but a better-formatted version is in table DBA_AUDIT_TRAIL. Similarily, SYS.FGA_LOG$ is best viewed from DBA_FGA_AUDIT_TRAIL.
Create a tablespace

CREATE TABLESPACE "AUDAUX" DATAFILE 
  '+DG_ORA1/datafile/audaux_1.dbf' SIZE 2G
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 524288 DEFAULT 
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
;

Turn off auto extend as this can fill up fast:

alter database datafile '+DG_ORA1/datafile/audaux_1.dbf' autoextend off;

Move SYS.AUD$ to the new tablespace with

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => 'AUDAUX');
END;
/

Move SYS.FGA_LOG$ to the new tablespace with:

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    audit_trail_location_value => 'AUDAUX');
END;
/

Create an audit policy as follows. In this case, I am auditing inserts, updates, and deletes on this table.

EXEC DBMS_FGA.ADD_POLICY
(
  object_schema=>'BILLING',
  object_name=>'INVENTORY_MASTER',
  policy_name=>'AUDIT_BILLING_INVENTORY_MASTER',
  statement_types=>'insert, update, delete'
)
;

If needed, the policy can be dropped with
EXEC DBMS_FGA.DROP_POLICY
(
object_schema=>’BILLING’,
object_name=>’INVENTORY_MASTER’,
policy_name=>’AUDIT_BILLING_INVENTORY_MASTER’
)
;

Check the current policies and their status

SELECT *
--object_schema,object_name,policy_name,policy_column,enabled,sel,ins,upd,del 
FROM   dba_audit_policies
;

The above example turned on FGA for all columns in the table. FGA can also be used for a specific column as shown below:

EXEC DBMS_FGA.ADD_POLICY
(
  object_schema=>'BILLING',
  object_name=>'INVENTORY_MASTER',
  policy_name=>'AUDIT_BILLING_INVENTORY_MASTER',
  audit_column=>'bonus'
  statement_types=>'insert, update, delete'
)
;

The columns that are part of an FGA policy can be checked with:

SELECT  * 
FROM    dba_audit_policy_columns;

The information being collected by the FGA can be viewed with:

ALTER SESSION SET nls_date_format='dd-Mon-yyyy hh24:mi:ss';

select    *
FROM      DBA_FGA_AUDIT_TRAIL
order by  timestamp
;

Cleaning up FGA
Please read until the end before running these commands:

Check if the DBMS_AUDIT_MGMT.INIT_CLEANUP has already been run on the database:

SET serveroutput ON SIZE 999999
BEGIN
  IF DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD)
  THEN
    DBMS_output.put_line('True');
  ELSE
    DBMS_output.put_line('False');
  END IF;
END;
/

If the response is “FALSE”, run the DBMS_AUDIT_MGMT.INIT_CLEANUP as follows:

In this example, the cleanup is being set to run every 1 hour

BEGIN
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    default_cleanup_interval => 1 /* hours */);
END;
/

If you get an error such as:

Error starting at line : 7 in command -
BEGIN
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    default_cleanup_interval => 24 /* hours */);
END;
Error report -
ORA-46263: The audit trail is already initialized for cleanup
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 61
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 757
ORA-06512: at line 2

Uninitialize the cleanup with:

BEGIN
 DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
  AUDIT_TRAIL_TYPE  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD);
END;
/

Followed by the above command to initialize the cleanup.

I spent some time looking into DBMS_SCHEDULER, DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP and DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD etc. but it seemed overly complicated for what the goal was. The same can be accomplished with a cron script:

DELETE 
FROM   SYS.fga_log$ 
WHERE  NTIMESTAMP# < to_date('21-SEP-20 18.42.00','dd-MON-yy hh24:mi:ss')
;

Remember to gather statistics and/or add indices to improve performance

exec dbms_stats.gather_table_stats (ownname=>'SYS', tabname=>'AUD$' , estimate_percent=>10, cascade=>TRUE, degree=>5);

exec dbms_stats.gather_table_stats (ownname=>'SYS', tabname=>'FGA_LOG$' , estimate_percent=>10, cascade=>TRUE, degree=>5);

We had a requirement to track how, when, and who made changes to the data in some of our sensitive tables. In the past, I would accomplish this by creating a trigger that would create before-and-after images into an audit table along with some other information such as the time of the change, the current user making the change, the IP address of the user making the change, etc. With 11g, Oracle introduced “Total Recall” that was subsequently renamed to Flashback Data Archive (FDA or FBA) in 12c. This new feature allows for long-term storage of “undo” data in a separate area known as the flashback archive.

Since my target database was on Oracle 12c, I decided to try out this new technology as opposed to a trigger solution.

Setup steps

Create a TABLESPACE and associated data file

  CREATE TABLESPACE "FDA" DATAFILE 
  '+DG_ORA1/datafile/fda_01.dbf' SIZE 1G
  NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT 
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

Create the flashback archive pointing to the above TABLESPACE. Additionally, set the quota and retention

CREATE FLASHBACK ARCHIVE DEFAULT OA_FDA TABLESPACE FDA
  QUOTA 1G RETENTION 30 day;

If you add more space to the TABLESPACE associated with the flashback archive, remember to increase the quota assigned above to the flashback archive. It is important to allocate enough space to this TABLESPACE as running out of space here will also impact the operations on the base tables.

In order to capture additional information about the user account, terminal, program, etc. that made the change, set the context level as shown below. There are other options to the level, however, I found “ALL” to be a good choice.

exec dbms_flashback_archive.set_context_level(level=> 'ALL');

Grant the schema owner access to the flashback archive

ALTER USER ACCT_OWNER QUOTA UNLIMITED ON FDA;

add the tables that you want to monitor to the flashback archive

ALTER TABLE "ACCT_OWNER"."ACCT_MASTER"  FLASHBACK ARCHIVE OA_FDA;

For every table that is added to the flashback archive, Oracle creates three additional tables named

SYS_FBA_DDL_COLMAP_91245
SYS_FBA_HIST_91245
SYS_FBA_TCRV_91245

I am not exactly sure how the number is assigned for each table added to the flashback archive but it will get a number and these three tables. These tables are created in the flashback data archive TABLESPACE created at the beginning of this process.

You can list the tables that are currently in the flashback data archive with the following SQL statement

SELECT     *
FROM       dba_flashback_archive_tables
ORDER BY   owner_name, table_name
;

Viewing the information if you have the key columns

The following SQL statement will display the change to a given row of table. Note that the SQL statement is run on the actual table and the optimizer uses the indexes on the actual table hence code the predicates appropriately

SELECT 
         versions_startscn
        ,versions_starttime
        ,versions_endtime
        ,versions_xid
        ,case versions_operation
              when 'D' then 'Delete'
              when 'I' then 'Insert'
              when 'U' then 'Update'
              else          versions_operation 
         end as Action    
        ,DBMS_FLASHBACK_ARCHIVE.get_sys_context
              (versions_xid, 'USERENV','SESSION_USER') AS session_user
        ,DBMS_FLASHBACK_ARCHIVE.get_sys_context
              (versions_xid, 'USERENV','HOST') AS host
        ,DBMS_FLASHBACK_ARCHIVE.get_sys_context
              (versions_xid, 'USERENV','module') AS module
        ,DBMS_FLASHBACK_ARCHIVE.get_sys_context
              (versions_xid, 'USERENV','os_user') AS os_user
        ,DBMS_FLASHBACK_ARCHIVE.get_sys_context
              (versions_xid, 'USERENV','ip_address') AS ip_address
        ,DBMS_FLASHBACK_ARCHIVE.get_sys_context
              (versions_xid, 'USERENV','AUTHENTICATED_IDENTITY') 
               as AUTHENTICATED_IDENTITY
        ,DBMS_FLASHBACK_ARCHIVE.get_sys_context
              (versions_xid, 'USERENV','DATABASE_ROLE') as test
        ,RO.*
FROM    "ACCT_OWNER"."ACCT_MASTER"  versions between 
                 timestamp (systimestamp - interval '1' day) and 
                 systimestamp RO
WHERE   key_value_col = 1622310
  and   versions_xid is not null
  order by 
        versions_starttime desc
;

Note:
a. that the table is aliased to RO at the end of the FROM clause (i.e. after the versions between part of the SQL). This allows me to see all the columns with RO.*.

b. in some cases I saw rows where the versions_xid was not populated and this causes the DBMS_FLASHBACK_ARCHIVE.get_sys_context to fail.

c. if you attempt to read data before any changes have been made and written to the flashback archive, you get a null error. Adjust the time period specified in the versions above.

d. there is a slight delay between modifying the table and seeing it in the flashback data archive. Changes to the flashback archive are written by a new process called the “fbda”. This is not a significant issue and I assume I was only able to notice it because I was testing on a quiet system where I was the only user.

Viewing the information if you DO NOT have the key columns
The above SQL will answer the question of who modified the data in a particular row. If you are attempting to identify all the changes that were made to a given table (i.e. you do not have key column values), the above SQL will not return in any reasonable amount of time. The issue is that each row in the result set initiates three calls to DBMS_FLASHBACK_ARCHIVE.get_sys_context for every column that uses the function. For example, if there are 100 rows that are in the flashback archive, and I am using the get_sys_context column 7 times in the above SQL, there will be (1000 rows * 7 columns * 3 calls) = 21,000 calls to DBMS_FLASHBACK_ARCHIVE.get_sys_context.

Also, note that the versions clause will list all of the rows that exist in the specified timeframe and not the rows that were modified in that timeframe. This will be discussed later below.

As a result of these gotchas, the below SQL performs better if you’re searching for all the rows that are modified a a given time

SELECT 
         versions_startscn
        ,versions_starttime
        ,versions_endtime
        ,versions_xid
        ,case versions_operation
              when 'D' then 'Delete'
              when 'I' then 'Insert'
              when 'U' then 'Update'
              else          versions_operation 
         end as Action    
        ,RO.*
FROM    "ACCT_OWNER"."ACCT_MASTER"  versions between 
                 timestamp (systimestamp - interval '1' day) and 
                 systimestamp RO
  order by 
        versions_starttime desc
;

The above query assumes that you want to see the changes in chronological order descending. If you want to see the information by a key column, then order by that column. After you have identified the particular row that you’re interested in, use the key column value and run the previous SQLs with the DBMS_FLASHBACK_ARCHIVE.get_sys_context columns.

Altering a table – Adding a column
If you add a column, the rows that existed in the flashback archive for the have values of now with the new column which is pretty much expected behavior for any table.

Altering a table – Modifying a column
If you modify an existing column of the table that is currently in the flashback archive, you will see a new column named something like M_50389_old_column_name together with the previous column.

Dropping a table that is part of the flashback archive
Before dropping a table that is part of the flashback archive, you need to remove it from the flashback data archive otherwise you will get the error

drop table "ACCT_OWNER"."ACCT_MASTER_DEAN"
Error report -
ORA-55610: Invalid DDL statement on history-tracked table
55610. 00000 -  "Invalid DDL statement on history-tracked table"
*Cause:    An attempt was made to perform certain DDL statement that is
           disallowed on tables that are enabled for Flashback Archive.
*Action:   No action required.

Remove the table from the flashback archive as follows

ALTER TABLE "ACCT_OWNER"."ACCT_MASTER"         NO FLASHBACK ARCHIVE;

and then it can be dropped.

Any attempt to read the flashback data for a table that has been drop results in the error

ORA-01466: unable to read data - table definition has changed
01466. 00000 -  "unable to read data - table definition has changed"
*Cause:    Query parsed after tbl (or index) change, and executed
           w/old snapshot
*Action:   commit (or rollback) transaction, and re-execute

Rows that exist vs. rows that were modified in the versions interval

When I started this exercise, I assumed that a query based on versions between timestamp “A” and timestamp “B” would only show me the rows that were modified between these two timestamps. However, my queries would return rows that had been modified outside of this range. This is documented at Oracle support in Doc id 2686158.1 which states “the VERSIONS BETWEEN clause is used to qualify rows which “existed” in the time range, and it is not something used to qualify rows which were “inserted” in the time range”. The column versions_starttime indicates the time of the actual change.

Performance implications
In a quiet system, I ran a workload of 10,000 inserts, 10,000 updates followed by 10,000 deletes. Each SQL statement was followed by a commit in an attempt to simulate an OLTP workload. After the AWR snapshot had rolled over to a new snapshot interval, I turned on the flashback archive and ran the same workload. An AWR difference report did not show any noticeable difference in resource consumption. However, this could have been due to the small size of my workload. Users testing in a somewhat busy nonproduction environment were also unable to identify any perceptible increase in elapsed time.

Locking
I naively assumed that since the rows were being written to the flashback archive via the FBDA process there would be no locking. However, we did see instances of locking. A colleague found the below documents on Oracle support

2423880.1 - FBDA: Massive Performance Problem After 
            enabling FDA (Doc ID 2423880.1)
            Request patch for bug 22239699
 
22239699  - Bug - FBDA TCRV delete maintenance colliding with 
                  archiving delete causing TX contention 
 
2344671.1 - Flashback Data Archive: Error ORA-1 and ORA-60 In 
            SQL Merge Into SYS_FBA_TCRV DEADLOCK ERRORS
 
2370465.1 - FDA - Flashback Data Archive Usage and Best 
            Practices (a.k.a. Total Recall)

Summary
In summary, it was relatively easy to set up the flashback archive and add or remove tables. Volume testing in a nonproduction environment has not shown any increase in resource consumption. Ensuring that there is enough space in the flashback archive table space is very important as running out of space impacts the application.

This blog post contains the steps required to convert the physical standby to an open (read/write) database and allow the application to perform a DR test without impacting the primary database and the users.

This test was conducted on a four-node primary RAC with a four-node physical standby, both running Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production.

We did not make any changes to the primary. As a result, users were able to work on the primary and it was able to ship logs to that standby. However, as we reserved all the space on the standby for DB_RECOVERY_FILE_DEST_SIZE the primary was unable to ship and issued the below error messages. For next time, DO NOT reserve all the space for DB_RECOVERY_FILE_DEST_SIZE. Instead, maybe use 50% or 60% of the available space and allow the archive logs from the primary to continue to land on the standby.

These errors were reported in the alert logs of the primary database because we had reserved all the space on the standby:

Tue Jun 09 12:33:44 2020
TT00: Standby redo logfile selected for thread 4 sequence 144397 for destination LOG_ARCHIVE_DEST_2
Tue Jun 09 12:35:13 2020
TT00: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3135)
TT00: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Tue Jun 09 12:35:13 2020
Errors in file /oracle_home/product/diag/rdbms/prprimy/PRPRIMY4/trace/PRPRIMY4_tt00_53355.trc:
ORA-03135: connection lost contact
Error 3135 for archive log file 8 to 'PRSTNBY'

All of the below commands/SQLs were run on the standby RAC.

Check the recovery file destination:

SELECT  NAME,FLOOR (space_limit/1024/1024) "Size MB",CEIL (space_used/1024/1024) "Used MB"
FROM    v$recovery_file_dest
;

NAME                              Size MB    Used MB
------------------------------ ---------- ----------
+DG_ARCHIVE                          5535        408

Confirm space in ASM:

ASMCMD [+] > lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576    208896   204136                0          204136              0             N  DG_ARCHIVE/

Modify the below parameters:

alter system set DB_RECOVERY_FILE_DEST_SIZE= '190G' scope=both sid='*';
alter system set DB_RECOVERY_FILE_DEST='+DG_ARCHIVE' scope=both sid='*';
alter system set undo_retention = 21600 scope=both sid='*';

Stop managed recovery process:

alter database recover managed standby database cancel;

Stop the database via srvctl:

srvctl stop database –d PRSTNBY –o immediate

In older versions of Oracle, we used to turn on flashback and activate the standby database. This is no longer required because the CONVERT TO SNAPSHOT STANDBY will turn on flashback. At this point the value of flashback will be:

select FLASHBACK_ON from gv$database;

FLASHBACK_ON
------------------
NO
NO
NO
NO

alter database flashback on;    <--- this was deemed not needed, so we didn't execute this
select flashback_on from gv$database;
select * from gv$tablespace where FLASHBACK_ON='NO';

On the standby, bring up one node in mount status to convert to snapshot:

startup nomount
alter database mount standby database;
select name, guarantee_flashback_database from gv$restore_point;

At this point, the above SQL will say no rows selected

Convert to snapshot:

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Check the flashback status:

select name, guarantee_flashback_database from gv$restore_point;

NAME                                            GUA
---------------------------------------------   ---
SNAPSHOT_STANDBY_REQUIRED_06/09/2020 12:35:05   YES


select flashback_on from gv$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

Sanity check - Stop here !! -- verify that restore point EXISTs, then open database

Try to open the one node that we are working on to make sure that the open works successfully:

ALTER DATABASE OPEN;

select status, instance_name, database_role, open_mode from gv$database, gv$instance;
STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED      PRSTNBY4         SNAPSHOT STANDBY MOUNTED

Shutdown immediate as we had only started one node to convert to snapshot

SHUTDOWN IMMEDIATE;

Bring up the database via SRVCTL in mount mode

srvctl start database –d PRSTNBY -o mount

Log on to each node (instance) and open that instance

ALTER DATABASE OPEN;

Inform users to point their application server to the former standby database and begin the DR Exercise.

After the applications inform us that they have completed their DR Exercise, continue with the below steps.

Stop the database via SRVCTL:

srvctl stop database –d PRSTNBY –o immediate

Confirm all nodes are down

srvctl status database –d PRSTNBY

Bring up one node to convert back to physical standby (Reduce the DB_RECOVERY_FILE_DEST_SIZE else we will face the error:

Waiting for all non-current ORLs to be archived...
Tue Jun 09 17:50:17 2020
Waiting for the ORL for thread 1 sequence 1 to be archived...
ARC3: Archiving not possible: error count exceeded

as the archive destination is being reserved for the DB_RECOVERY_FILE_DEST_SIZE):

startup mount
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
alter database mount standby database;
alter system set DB_RECOVERY_FILE_DEST_SIZE= '5535M' scope=both sid='*';
alter system set undo_retention = 14400 scope=both sid='*';
shutdown immediate;

Start the standby via SRVCTL

srvctl start database –d PRSTNBY

Confirm that MRP is running and log apply is progressing.

Apologies in advance as I cannot share the original query as the information is proprietary. I hope to be able to explain as much as possible to make it useful.

One of our application had converted from Sybase to Oracle Database 18c Enterprise Edition Release 18.0.0.0.0. This (obfuscated) query used to run in under 2 seconds in Sybase but was taking between 15 to 25 seconds to execute in Oracle. The 25 second result tended to be on the first execution with subsequent executions running in between 15 to 17 seconds. I put aside the 25 second result as being attributable to the creation of the access path and focused on the 15 to 17 second executions.

Original query

SELECT 
       many,
       columns,
       from,
       all,
       the,
       joined,
       tables
FROM   customer_order ao
       full outer join sales_notes sn
                    ON ao.order_id = sn.order_id
       full outer join order_contacts poc
                    ON ao.order_id = poc.order_id
       full outer join contact c
                    ON poc.contact = c.contact,
       site_order so
       full outer join order_info oori
                    ON so.site = oori.site
       full outer join tax_info t
                    ON so.site = t.site
                       AND so.order_id = t.order_id
       full outer join order_notes son
                    ON so.site = son.site
       full outer join tax_notes tn
                    ON so.site = tn.site
       full outer join tax_order_detail tod
                    ON so.site = tod.site
       full outer join tax_sub_order tso
                    ON so.site = tso.site
       full outer join other_vax_order ovo
                    ON so.site = ovo.site
       address a
WHERE  so.site          IS NOT NULL
  AND  so.site          = 'some value here'
  AND  so.site          = ao.site
  AND  so.acct_order_id = ao.acct_order_id
  AND  so.site          = a.site
ORDER  BY 
       so.site,
       so.acct_order_id 
;

All of these tables were below 100k rows with no complexity of LOB columns or anything out of the ordinary. The cost estimate for this SQL was 365,047,136.

The filter predicates in the where clause on the so.site will always result in less than 10 rows. Site is the primary index on the site table. All the other tables have an index to satisfy the join columns. The tunning advisor reported that no better plan was available.

In order to understand what was occurring, I ran an explain on the below SQL:

SELECT 
       many,
       columns,
       from,
       all,
       the,
       joined,
       tables
FROM   customer_order ao,
       site_order so,
       address a
WHERE  so.site          IS NOT NULL
  AND  so.site          = 'some value here'
  AND  so.site          = ao.site
  AND  so.acct_order_id = ao.acct_order_id
  AND  so.site          = a.site
ORDER  BY 
       so.site,
       so.acct_order_id 
;

The optimizer estimated the cost at 9 and the query ran consistently in less than a second. I then experimented with adding the other FULL OUTER JOIN tables one by one. The addition of any two tables as FULL OUTER JOINS did not impact the query cost. Additionally, these other two tables would be joined as NESTED LOOP joins. The addition of a third table as a FULL OUTER JOIN would raise the query cost significantly and also use a hash join.

After some trial and error, I added in the optimizer hint:
Modified query

SELECT /*+ USE_NL(so ovo) */
       many,
       columns,
       from,
       all,
       the,
       joined,
       tables
FROM   customer_order ao
       full outer join sales_notes sn
                    ON ao.order_id = sn.order_id
       full outer join order_contacts poc
                    ON ao.order_id = poc.order_id
       full outer join contact c
                    ON poc.contact = c.contact,
       site_order so
       full outer join order_info oori
                    ON so.site = oori.site
       full outer join tax_info t
                    ON so.site = t.site
                       AND so.order_id = t.order_id
       full outer join order_notes son
                    ON so.site = son.site
       full outer join tax_notes tn
                    ON so.site = tn.site
       full outer join tax_order_detail tod
                    ON so.site = tod.site
       full outer join tax_sub_order tso
                    ON so.site = tso.site
       full outer join other_vax_order ovo
                    ON so.site = ovo.site
       address a
WHERE  so.site          IS NOT NULL
  AND  so.site          = 'some value here'
  AND  so.site          = ao.site
  AND  so.acct_order_id = ao.acct_order_id
  AND  so.site          = a.site
ORDER  BY 
       so.site,
       so.acct_order_id 
;

And the cost went down to around 70 with the query consistently performing in under two seconds. I just happened to be testing the order of joins with the table aliased as OVO and hence the hint lists OVO. Almost any other combination results in the same (i.e. improved) access path.

We had a user who needed to run an adhoc report for 24k account numbers. The structure of the account table was as follows:

COL_A NUMBER(3,0) DEFAULT ON NULL 0 NOT NULL ENABLE
COL_B NUMBER(3,0) DEFAULT ON NULL 0 NOT NULL ENABLE
COL_C NUMBER(4,0) DEFAULT ON NULL 0 NOT NULL ENABLE
About 100 other columns

The table had 60 million rows and occupied about 34 gigabytes of space.

There was an index on COL_A, COL_B, COL_C plus other trailing columns.

The list of account numbers was provided as
0020510043
1024100289
And so on

A new list of account numbers may be provided every day or multiple times a day and hence the process needed to be repeatable. The first attempt was to loop through this list and execute SQL statement such as:

  select    col_01, col_02, col_03, col_04, col_05, col_06, col_07
  from      tbowner.tbname
  where     col_a = 002 
    and     col_b = 051
    and     col_c = 0043
  ;
  select    col_01, col_02, col_03, col_04, col_05, col_06, col_07
  from      tbowner.tbname
  where     col_a = 102
    and     col_b = 410
    and     col_c = 0289
  ;

and so on 24k times. The run time for this execution was about 300 minutes.

I changed the code to use bind variables as shown below and the execution time dropped to 22 minutes.

#!/bin/ksh
PATH=$PATH:/oracle_home/12.1.0.2
export ORACLE_SID=PROD4

function run_the_sql
{
  #echo 'run_the_sql - start' `date`
  
  /oracle_home/12.1.0.2/bin/sqlplus -s / as sysdba << EOF >> report.txt
  set linesize 150
  set heading off
  set pagesize 50000; 
  set echo off; 
  set feedback off; 
  
  var    col_a_bind number;
  var    col_b_bind number;
  var    col_c_bind number;
  
  exec :col_a_bind :=${col_a_os};
  exec :col_b_bind :=${col_b_os};
  exec :col_c_bind :=${col_c_os};
  
  select    col_01, col_02, col_03, col_04, col_05, col_06, col_07
  from      tbowner.tbname
  where     col_a = :col_a_bind 
    and     col_b = :col_b_bind
    and     col_c = :col_c_bind
  ;
  exit
EOF
  
  #echo 'run_the_sql - end  ' `date`
}


#
## Main processing
#
clear

echo 'start : ' `date`
`rm report.txt`

temp_ctr=0

while IFS=',' read -r -u3 acct_num
do  
   col_a_os=`expr substr $acct_num 1 3`
   col_b_os=`expr substr $acct_num 4 3`
   col_c_os=`expr substr $acct_num 7 4`
   run_the_sql
   temp_ctr=temp_ctr+1
   if  [temp_ctr -gt 1000 ]; then
       print '1000 done' `date`
       temp_ctr=0
   fi
done 3< list_of_tf.txt
echo 'end   : ' `date`

I had the below input file and a script whose logic was to process each line of the input file. Within the logic, I needed to perform an OS command such as the “more /etc/oratab” command below.

Contents on the input file:

[server_name] more temp.txt
a
b
c

Code

#! /bin/bash
function perform_logic
{
  echo '***'
  temp=`more /etc/oratab | grep -i dean | wc -l`
}
  
while IFS=',' read -r service lsnr port
do
   echo $service $lsnr $port 
   perform_logic
done < temp.txt

I was expecting three lines of output; one for each line in the input file. However, the output was:

[server_name] ./temp.ksh
a
***
[server_name] 

Basically processing stops after the first line of the input file. The issue is that the “more /etc/oratab” is overlaying the standard input/output of the while command. After I changed the code to:

#! /bin/bash
function perform_logic
{
  echo '***'
  temp=`more /etc/oratab | grep -i dean | wc -l`
}
  
while IFS=',' read -r –u3 service lsnr port
do
   echo $service $lsnr $port 
   perform_logic
done 3< temp.txt 

Now the output displays that all lines of the input file are processed:

[server_name] ./temp.ksh
a
***
b
***
c
***
[dt2ompd2-splunk2]

#! /bin/bash
clear

function perform_logic
{
  echo '***'
  temp=`more /etc/oratab | grep -i dean | wc -l`
}
  
while IFS=',' read -r -u3 service lsnr port
#while IFS=',' read -r service lsnr port
do
   echo $service $lsnr $port 
   perform_logic
done 3< temp.txt 
#done < temp.txt

[dt2ompd2-splunk2] ./temp.ksh
a
***
[server_name]

An offshore user contacted me today about an ongoing issue. Apparently the insert statements that his process was performing were disappearing from the table. We stepped through his java code and the code looked fine; the insert was occurring and was successful, no exceptions etc.

We ran a trace on the session and see the insert being executed.

TKPROF: Release 11.2.0.4.0 - Development on Wed Feb 26 11:52:46 2020
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
Trace file: PRDA11_ora_8025.trc
Sort options: default
 
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************
 
SQL ID: dgya487q14n43 Plan Hash: 0
 
insert into ACCTOWN.ACC_CUST (ACCOUNT_NUMBER, STATUS, ACTIVATE_DATE, 
  CUST_ID) 
values
 (:1, :2, :3, :4)

However, the row was missing from the table.

My suspicion was that someone or some process was deleting the data from the table. In order to confirm this, I turned on auditing on the table:

AUDIT all ON ACCTOWN.ACC_CUST by ACCESS;

Auditing this database is set to write to the database.

show parameter audit;
audit_trail          string  DB

At this point I asked the user to run the process again and then checked the audit table:

alter session set nls_date_format = 'dd-MON-yy hh24.mi.ss';
select    os_username, username, userhost, 
          timestamp, owner,obj_name,action_name
from      dba_audit_trail
where     obj_name = 'ACC_CUST'
  and     action_name not in ('SELECT','SESSION','SESSION REC')
order by  timestamp
;
 

And I was able to confirm that a different process from the same application was deleting the data:

os_username username userhost timestamp          owner   obj_name action_name
ACCTOWN     ACCTOWN  srsr012  26-FEB-20 10.08.42 ACCTOWN ACC_CUST INSERT
kundjo4     ACCTOWN  srvr012  26-FEB-20 10.08.46 ACCTOWN ACC_CUST DELETE

After this was confirmed, turn off auditing on the table:

NOAUDIT ALL ON ACCTOWN.ACC_CUST;