29 March, 2025

Flashback a Pluggable Database in RAC with Data Guard

 I have published a new video Flashback PDB in RAC with DG.

Here are some important considerations / commands when you want to use Flashback :

1.  Ensure that you have FLASHBACK_ON in the Database (at both Primary and Standby).  If you don't have FLASHBACK_ON,  ensure that you create the RESTORE POINT WITH GUARANTEE FLASHBACK DATABASE at both Primary and Standby.  (In the latter case, the Flashback SCN / Point In Time of the Standby must be lower than that of the Primary).

3.  Monitor the FRA (Recovery Area) (V$FLASH_RECOVERY_AREA_USAGE) and  Oldest Flashback Time (in V$FLASHBACK_DATABASE_LOG).  If necessary, increase DB_FLASHBACK_RETENTION_TARGET and DB_RECOVERY_FILE_DEST_SIZE to ensure that you don't run out of space for the Flashback Logs !  

2. If you have used dgmgrl -- i.e. Data Guard Broker Configuration, all commands to Stop Redo Shipping / Resume Redo Shipping,  Stop Redo Apply / Resume Redo Apply must be issued through dgmgrl.  Do not use SQL commands.

3. At the Standby the Flashback command uses the "STANDBY" clause to identify that you are doing a Flashback for a Standby.


Some older Blog Posts on FLASHBACK DATABASE are at  Blog Series on Flashback Database.


02 February, 2025

Domains in 23ai Schema Development

 I had heard of Data Use Case Domains in 23ai. However, the New Features nd Database Concepts documentation didn't provide enough examples for me to build on.

However, this blog post by Ulrike Schwinn  (which was shared by @thatjeffsmith  on X) helped me explore domains.

In this demo, I am using the Pre-Seeded Domains.  However, you can see the example posted by Ulrike Schwimm  or even read in the Database Concepts documentation  to help build your own custom Domains.

A Data Use Case Domain is like defining a Custom DataType such that only valid values are permitted.  The Domain name can be a self-identifier (just as "DATE" or "NUMBER" identifies the type of data being stored).

Here  is my demonstration  (I also use the Annotations feature -- the Data Use Case Domains documentation links above also lead to this feature)


SQL> set pages600 linesize 132
SQL> col contact_person format a32
SQL> col contact_email format a24
SQL>
SQL> drop table forex_rates_contacts;

Table dropped.

SQL>
SQL>
SQL> create table forex_rates_contacts
  2  (
  3   country_iso_code  varchar2(3) domain country_code_d,  -- preseeded SYS domain
  4   currency_code varchar2(3) domain currency_code_d, -- preseeded SYS domain
  5   contact_person varchar2(128),
  6   contact_email     varchar2(4000) domain email_d -- preseed SYS domain
  7  )
  8  annotations (display 'Forex Contact Persons')
  9  /

Table created.

SQL>
SQL> desc forex_rates_contacts
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 COUNTRY_ISO_CODE                                                                  VARCHAR2(3) SYS.COUNTRY_CODE_D
 CURRENCY_CODE                                                                     VARCHAR2(3) SYS.CURRENCY_CODE_D
 CONTACT_PERSON                                                                    VARCHAR2(128)
 CONTACT_EMAIL                                                                     VARCHAR2(4000) SYS.EMAIL_D

SQL>
SQL>
SQL> set long 1000
SQL> set longc 1000
SQL> set serveroutput on
SQL>
SQL> rem  FROM clause is no longer required in 23ai
SQL> select dbms_metadata.get_ddl('TABLE','FOREX_RATES_CONTACTS','HEMANT');

DBMS_METADATA.GET_DDL('TABLE','FOREX_RATES_CONTACTS','HEMANT')
------------------------------------------------------------------------------------------------------------------------------------

  CREATE TABLE "HEMANT"."FOREX_RATES_CONTACTS"
   (    "COUNTRY_ISO_CODE" VARCHAR2(3) DOMAIN "SYS"."COUNTRY_CODE_D",
        "CURRENCY_CODE" VARCHAR2(3) DOMAIN "SYS"."CURRENCY_CODE_D",
        "CONTACT_PERSON" VARCHAR2(128),
        "CONTACT_EMAIL" VARCHAR2(4000) DOMAIN "SYS"."EMAIL_D"
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"
  ANNOTATIONS("DISPLAY" 'Forex Contact Persons')


SQL>
SQL>
SQL>
SQL> rem  MULTI-ROW Insert
SQL> insert into forex_rates_contacts
  2  values
  3  ('US','USD','Mr Unknown','unknown@nowhere.gov'),
  4  ('IN','INR','Someone at RBI','someone@rbi.gov.in')
  5  /

2 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select * from forex_rates_contacts
  2  order by country_iso_code
  3  /

COU CUR CONTACT_PERSON                   CONTACT_EMAIL
--- --- -------------------------------- ------------------------
IN  INR Someone at RBI                   someone@rbi.gov.in
US  USD Mr Unknown                       unknown@nowhere.gov

SQL>
SQL> -- Note that the country_code_d and currency_code_d do not check validity against really ISO codes
SQL> -- thus, it does not disallow "ZZ" and "ZZZ"
SQL> insert into forex_rates_contacts
  2  values
  3  ('ZZ','ZZZ','Mr Unknown','unknown@nowhere.zz')
  4  /

1 row created.

SQL>
SQL> select * from forex_rates_contacts
  2  order by country_iso_code
  3  /

COU CUR CONTACT_PERSON                   CONTACT_EMAIL
--- --- -------------------------------- ------------------------
IN  INR Someone at RBI                   someone@rbi.gov.in
US  USD Mr Unknown                       unknown@nowhere.gov
ZZ  ZZZ Mr Unknown                       unknown@nowhere.zz

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> -- But the rules for email validation are encoded
SQL> insert into forex_rates_contacts
  2  values
  3  ('UK','GBP','Mr Someone','someone@x')
  4  /
insert into forex_rates_contacts
*
ERROR at line 1:
ORA-11534: check constraint (HEMANT.SYS_C0013464) involving column CONTACT_EMAIL due to domain constraint SYS.SYS_DOMAIN_C0030 of
domain SYS.EMAIL_D violated
Help: https://docs.oracle.com/error-help/db/ora-11534/


SQL>
SQL> select * from forex_rates_contacts
  2  order by country_iso_code
  3  /

COU CUR CONTACT_PERSON                   CONTACT_EMAIL
--- --- -------------------------------- ------------------------
IN  INR Someone at RBI                   someone@rbi.gov.in
US  USD Mr Unknown                       unknown@nowhere.gov
ZZ  ZZZ Mr Unknown                       unknown@nowhere.zz

SQL>
SQL> spool off


I haven't added my own custom Domains but used the PreSeeded domains for Country, Currency and Email.  Look at "10.1.12 Built-In Use Case Domains" in the documentation.