DBA Blogs

Tuning query with function calls in select

Tom Kyte - Sat, 2020-03-28 02:46
I have 7M rows from legacy system. <code> insert /*+ append */ into TGT_DATA ( LST_NAM, FRST_NAM, MDL_NAM ) SELECT /*+ parallel(10)*/ func_text_clean(LST_NAM), func_text_clean( FRST_NAM), func_text_clean( MDL_NAM) FROM P_ONST; </...
Categories: DBA Blogs

Pass string values to stored procedure

Tom Kyte - Sat, 2020-03-28 02:46
Hello, Ask Tom Team. I have several DELETE statements and I want them all within a stored procedure to simplify execution. All these statements would use different string values in the where condition. DELETE FROM user1.table4 t4 WHERE t4.id...
Categories: DBA Blogs

Basic DynamoDB Data Modelling

Pakistan's First Oracle Blog - Sat, 2020-03-28 01:58



The key differentiator between a NoSQL database like AWS Dynamodb and a relational database like Oracle is of the way data is modelled. In a relational model; data is normalized, de-duplicated and relationships are established between entities. In a NoSQL database like Dynamodb, data can be duplicated and entities can be fused together. This is done to facilitate scalability which is hallmark of today's hyper-scale cloud based applications.


One of the most common relationship within data modelling is One-to-Many. For example, we have 2 entities; Painting and Gallery. A painting can only reside in one gallery, whereas a gallery can have multiple paintings. This is one to many relationship. In relational modelling world, we would have 2 tables; Painting and Gallery, such as:


Museum
Painting

MuseumId
MuseumName
PaintingId
PaintingName
MuseumId
M1
Sofía
P1
Guernica
M1
M2
NSW Gallery
P2
The Great Masturbator
M1
M3
Louvre
P3
Sofala
M2
P4
Mona Lisa
M3


In above One-to-Many relationship, we have joined both Museum and Painting with primary key and foreign key relationship. So e.g. if want to check all paintings in Museum M1, then the query would be:

select p.Title, m.MuseumName from Painting p, Museum m where p.MuseumId=m.MuseumId and m.MuseumId=’M1’;

Now joins are expensive, and they become more expensive as the data grows. In huge cloud scale databases this hampers scalability and at times become impractical to have these joins, hence the NoSQL databases like Dynamodb.

So how do you model One-to-Many relationship in a Dynamodb table when we cannot have joins and aggregations?

We know what the entities are, and we have an access pattern, so now let’s design our table.


MuseumPaintingTable
Primary Key
Attributes
PK
SK
MuseumName
Title
Artist
Museum#M1
Painting#P1
Sofía
Guernica
Picasso
Painting#P2
Sofía
The Great Masturbator
Salvador Dali
Museum#M2
Painting#P3
NSW Gallery
Sofala
Russel Drysdale
Museum#M3
Painting#P4
Louvre
Mona Lisa
Leonardo


If you are coming from a relational world, the above table may seem like blasphemy even though I haven’t yet used different attributes for each item to keep it simple. This is where you need to appreciate the flexibility of NoSQL databases to facilitate scalability.

In above table, we have define a composite primary key, consisting of a partition key and a sort key. A primary key in Dynamodb table defines a unique item, where item corresponds to a row. Remember that all access patterns are driven through the primary key, which means that the entry point for your queries must be the primary key. Every museum and painting has its own unique id, so we have combined them together to generate a unique item.

Note that how we have defined a one-to-many relationship in above table using partition key and sort key within the primary key. For one Museum#M1, we have 2 paintings; Painting#P1 and Painting#P2, and then both of these paintings have their own attributes, which can even differ in structure. We have fused both Painting and Museum entities together.

Now how would you write the previous relational query in DynamoDB, where we wanted to check all paintings in the Museum M1? It would be like this:

aws dynamodb query \
    --table-name MuseumPaintingTable \
    --key-condition-expression "PK = :MID" \
    --expression-attribute-values  '{":MID":{"S":"M1"}}'
               --projection-expression 'Title,MuseumName'

The above command is a RESTful API call to Dynamodb table.
Categories: DBA Blogs

Band Joins

Tom Kyte - Fri, 2020-03-27 08:26
Team, Was reading about Band join from the below link <u>https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/joins.html#GUID-24F34188-110F-4245-9DE7-43954092AFE0</u> <u>https://jonathanlewis.wordpress.com/2017/02/13/band-j...
Categories: DBA Blogs

Proxy user info

Tom Kyte - Fri, 2020-03-27 08:26
Where in v$ views is hiding info about PROXY_USERNAME ? I'am aware of little piece of information in v$session_connect_info and dbproxy_username column in unified_audit_trail. Most appropriate place will be in v$session, but i didn't find it. R...
Categories: DBA Blogs

Guarantee a limit for total amount of resources used by all the sessions of users in a particular user group

Tom Kyte - Thu, 2020-03-26 14:06
Can we guarantee a limit for total amount of resources used by all the sessions of users in a particular user group so their total usage does not exceed 10% of CPU, 10% of IO, 10% of Memory? Can we ensure that a user group follows different resou...
Categories: DBA Blogs

Gaining low level and in depth understanding on switching through data base resource manager

Tom Kyte - Thu, 2020-03-26 14:06
I read that one way to switch the load plan is through scheduler. I guess scheduler will switch to a plan by taking switching decision based on time. It will not take switching decision based on need and availability of resources among various consum...
Categories: DBA Blogs

ora-12954 on oracle xe 18c with less than 12gb user data

Tom Kyte - Thu, 2020-03-26 14:06
Dear Tom, about two weeks ago we moved a test database from 12.2.0.1 SE2 to 18c XE via data pump. We used schema import into a pdb with compression enabled (from the parameter file: TRANSFORM=TABLE_COMPRESSION_CLAUSE:"ROW STORE COMPRESS ADVANCED")...
Categories: DBA Blogs

parallel database systems store

Tom Kyte - Thu, 2020-03-26 14:06
I am recently studying database and face such a question: Large-scale parallel database systems store an extra copy of each data item on disks attached to a different processor, to avoid loss of data if one of the processors fails. a. Instead of ...
Categories: DBA Blogs

v_x$kglob query takes longer time

Tom Kyte - Thu, 2020-03-26 14:06
Following query was taking longer time. <b>1)</b> We try to make markhot with following SQL <code>SYS.DBMS_SHARED_POOL.MARKHOT(hash=>e130457a4520f54c18acb0131777d76d,namespace=>0);</code> <b>ERROR at line 1: ORA-06550: line 1, column 42: ...
Categories: DBA Blogs

Submitting PL/SQL procedure execution request via REST

Tom Kyte - Thu, 2020-03-26 14:06
I need to click a URL on GUI page. Once clicked, respective PL/SQL procedure execution request should be submitted to respective database in form of query API request- REST API. Once procedure is executed in database, status message (success or faile...
Categories: DBA Blogs

Grouping same value in different groups

Tom Kyte - Thu, 2020-03-26 14:06
Database: Oracle Database 12c Release 12.2.0.1.0 Following is my test case script: <code>create table test ( id number(1), sdate date, tdate date, prnt_id number(1) ); i...
Categories: DBA Blogs

Data Pump API to refresh top 10 partitions

Tom Kyte - Thu, 2020-03-26 14:06
I have a requirement where I have to refresh tables from PROD to DEV. But refresh needs to be done in such a manner that partitioned table should be refreshed with its latest top 10 partitions and non partitioned table should be refreshed full I a...
Categories: DBA Blogs

Using FLASHBACK DATABASE for [destructive] D.R. Testing

Hemant K Chitale - Thu, 2020-03-26 11:45
Testing your Disaster Recovery strategy with an Oracle Standby Database can be at different "levels" for the database :
1. Graceful Switchover to the D.R. site and reversing roles between the two databases, but only querying* data at the D.R. site
2. Shutdown of the Production site and Failover to the D.R. site and only *querying* data at the D.R. site
3. Shutdown of the Production site and Failover to the D.R. site with *destructive* testing at the D.R. site followed by restore (or flashback) of the D.R. site database to "throwaway" all  changes
3. Either Switchover or Failover with role reversal and *destructive* testing at the D.R. site, validation that data changes flow back to the Production site and, finally, restore (or flashback) of the database at both sites.

Restoring a large database at one or both sites can take time.
You may have taken a Snapshot of the database(s) and just restore the snapshot.
Or you may FLASHBACK the database(s).

{for details on how I created this Standby database configuration in 19c, see my previous posts here and here}

I will try to use FLASHBACK DATABASE here.

I start with the Primary running at the Production site :

oracle19c>sqlplus hemant/hemant@orclpdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 26 23:22:26 2020
Version 19.3.0.0.0

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

Last Successful login time: Thu Mar 26 2020 23:22:02 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> drop table my_transactions purge;

Table dropped.

SQL> create table my_transactions (txn_id number, txn_data varchar2(50));

Table created.

SQL> insert into my_transactions values (1,'First at ProductionDC:Primary');

1 row created.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>


I then verify the state of both databases (the "oracle19c" prompt is at the Production site, the  "STDBYDB" prompt is at the D.R. site)

oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 26 23:23:48 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, current_scn
2 from v$database
3 /

CONTROL OPEN_RESETL DATABASE_ROLE FLASHBACK_ON CURRENT_SCN
------- ----------- ---------------- ------------------ -----------
CURRENT NOT ALLOWED PRIMARY NO 4796230

SQL>



STDBYDB>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 26 23:25:02 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, standby_became_primary_scn, current_scn
2 from v$database
3 /

CONTROL OPEN_RESETL DATABASE_ROLE FLASHBACK_ON
------- ----------- ---------------- ------------------
STANDBY_BECAME_PRIMARY_SCN CURRENT_SCN
-------------------------- -----------
STANDBY REQUIRED PHYSICAL STANDBY NO
0 4796205


SQL>


So, currently, the Standby is slightly behind (SCN#4796205) the Primary (SCN#4796230). Note that FLASHBACK is *not* enabled in the databases.

I first create my RESTORE POINT on the Standby and then on the Primary.

{at the current Standby at the D.R. site}
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /opt/oracle/FRA/STDBYDB
db_recovery_file_dest_size big integer 10G
SQL> create restore point dr_before_switch guarantee flashback database;

Restore point created.

SQL> select name, restore_point_time, database_incarnation#, scn, guarantee_flashback_database
2 from v$restore_point
3 /

NAME
--------------------------------------------------------------------------------
RESTORE_POINT_TIME
---------------------------------------------------------------------------
DATABASE_INCARNATION# SCN GUA
--------------------- ---------- ---
DR_BEFORE_SWITCH

2 4796590 YES


SQL>
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>




{at the current Primary at the Production site}
SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, current_scn
2 from v$database
3 /

CONTROL OPEN_RESETL DATABASE_ROLE FLASHBACK_ON CURRENT_SCN
------- ----------- ---------------- ------------------ -----------
CURRENT NOT ALLOWED PRIMARY NO 4796230

SQL> alter system switch logfile;

System altered.

SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, current_scn
2 from v$database
3 /

CONTROL OPEN_RESETL DATABASE_ROLE FLASHBACK_ON CURRENT_SCN
------- ----------- ---------------- ------------------ -----------
CURRENT NOT ALLOWED PRIMARY NO 4796968

SQL> create restore point production_before_switch guarantee flashback database;

Restore point created.

SQL> select name, restore_point_time, database_incarnation#, scn, guarantee_flashback_database
2 from v$restore_point
3 /

NAME
--------------------------------------------------------------------------------
RESTORE_POINT_TIME
---------------------------------------------------------------------------
DATABASE_INCARNATION# SCN GUA
--------------------- ---------- ---
PRODUCTION_BEFORE_SWITCH

2 4797182 YES


SQL>


At each site, I have created a Restore Point (with Guarantee Flashback Database). I have ensured that the Restore Point for the current Standby Database at the D.R. site is at a *lower* SCN (4796590) than that for the current Primary (4797182) (at the Production site).  To further ensure this, I did a log swich and verified the CURRENT_SCN at the Primary before creating the Restore Point.

(Note that both sites have a DB_RECOVERY_FILE_DEST configured for the GUARANTEEd Restore Point).

(a small note : I have to disable Recovery at the Standby database before I can create a Restore Point and then re-enable Recovery after that.  A Restore Point cannot be created when a database is in Recovery mode).


I now put in another transaction at the Primary (Production site database) and then Switchover to to the D.R. site.

SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> insert into my_transactions values (2,'Second, after R.P. at ProductionDC:Primary');

1 row created.

SQL> commit;

Commit complete.

SQL> connect / as sysdba
Connected.
SQL> alter database switchover to stdbydb;

Database altered.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 26 23:41:57 2020
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 436207616 bytes
Database Buffers 754974720 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, current_scn
2 from v$databasse
3
SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, current_scn
2 from v$database
3 /

CONTROL OPEN_RESETL DATABASE_ROLE FLASHBACK_ON CURRENT_SCN
------- ----------- ---------------- ------------------ -----------
STANDBY ALLOWED PHYSICAL STANDBY RESTORE POINT ONLY 4899284

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>


So, now the database at the Production site is a Standby database.

I now connect to the database at the D.R. site that is now a Primary

STDBYDB>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 26 23:45:02 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, standby_became_primary_scn, current_scn
2 from v$database
3 /

CONTROL OPEN_RESETL DATABASE_ROLE FLASHBACK_ON
------- ----------- ---------------- ------------------
STANDBY_BECAME_PRIMARY_SCN CURRENT_SCN
-------------------------- -----------
CURRENT NOT ALLOWED PRIMARY RESTORE POINT ONLY
4899284 0


SQL> shutdown ;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 436207616 bytes
Database Buffers 754974720 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL>
SQL> alter pluggable database orclpdb1 open;

Pluggable database altered.

SQL> connect hemant/hemant@STDBYPDB1
Connected.
SQL> select * from my_transactions order by 1;

TXN_ID TXN_DATA
---------- --------------------------------------------------
1 First at ProductionDC:Primary
2 Second, after R.P. at ProductionDC:Primary

SQL>
SQL> insert into my_transactions values (3,'Destructive change at DRDC');

1 row created.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
STDBYDB>


{Note that "STDBYDPDB1" is my tnsnames entry for the PDB which still has the name "orclpdb1" at the D.R. site.}

I have created a "destructive" change with the third row which should not be in production. However, I will switch back to the Production data centre and verify that the row has replicated back.

{at the D.R. site}
STDBYDB>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 26 23:50:29 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter database switchover to orclcdb;

Database altered.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
STDBYDB>



{at the Production site}
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 26 23:52:21 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 436207616 bytes
Database Buffers 754974720 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL> alter pluggable database orclpdb1 open;
alter pluggable database orclpdb1 open
*
ERROR at line 1:
ORA-65019: pluggable database ORCLPDB1 already open


SQL>
SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> select * from my_transactions order by 1;

TXN_ID TXN_DATA
---------- --------------------------------------------------
1 First at ProductionDC:Primary
2 Second, after R.P. at ProductionDC:Primary
3 Destructive change at DRDC

SQL>


So, I have been able to
1. SWITCHOVER from the Production site to the D.R. site
2. Create a new row when the database is Primary at the D.R. site
3. SWITCHOVER back to the Production site
4. Verify that the destructive row is now at the Production site.

I now need to reset both databases to the state they were in before I began the test.

{at the Production site}
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 26 23:56:16 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 436207616 bytes
Database Buffers 754974720 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, standby_became_primary_scn, current_scn
2 from v$database
3 /

CONTROL OPEN_RESETL DATABASE_ROLE FLASHBACK_ON
------- ----------- ---------------- ------------------
STANDBY_BECAME_PRIMARY_SCN CURRENT_SCN
-------------------------- -----------
CURRENT NOT ALLOWED PRIMARY RESTORE POINT ONLY
5000964 0


SQL>
SQL> select name, restore_point_time, database_incarnation#, scn, guarantee_flashback_database
2 from v$restore_point
3 /

NAME
--------------------------------------------------------------------------------
RESTORE_POINT_TIME
---------------------------------------------------------------------------
DATABASE_INCARNATION# SCN GUA
--------------------- ---------- ---
PRODUCTION_BEFORE_SWITCH

2 4797182 YES


SQL>
SQL> FLASHBACK DATABASE TO RESTORE POINT PRODUCTION_BEFORE_SWITCH;

Flashback complete.

SQL> alter database open resetlogs ;

Database altered.

SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, standby_became_primary_scn, current_scn
2 from v$database
3 /

CONTROL OPEN_RESETL DATABASE_ROLE FLASHBACK_ON
------- ----------- ---------------- ------------------
STANDBY_BECAME_PRIMARY_SCN CURRENT_SCN
-------------------------- -----------
CURRENT NOT ALLOWED PRIMARY RESTORE POINT ONLY
5000964 4798237


SQL>
SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> select * from my_transactions order by 1;

TXN_ID TXN_DATA
---------- --------------------------------------------------
1 First at ProductionDC:Primary

SQL>


So, now, the database at the Production site has reverted to the Restore Point and all changes after the Restore Point have been discarded.

This includes TXN_ID=2 which I had added to demonstrate propagation of a change from the Production site to the D.R. site ---- in your testing, you must ensure that you do not make any changes after the Restore Point is created.   Typically, you'd create your Production Restore Point with the applications disconnecte, database shutdown and re-mounted just before switchover.  Remember, this is for D.R. testing when you do have control over applications and database shutdown and startup.


What about the database at the D.R. site ?  Can I flashback it and resume it's role as a Standby ?
Remember that the Restore Point I created on the D.R. site was at a *lower* SCN than that for the Production site.

STDBYDB>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 27 00:08:25 2020
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 436207616 bytes
Database Buffers 754974720 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, standby_became_primary_scn, current_scn
2 from v$database
3 /

CONTROL OPEN_RESETL DATABASE_ROLE FLASHBACK_ON
------- ----------- ---------------- ------------------
STANDBY_BECAME_PRIMARY_SCN CURRENT_SCN
-------------------------- -----------
STANDBY ALLOWED PHYSICAL STANDBY RESTORE POINT ONLY
0 5000964


SQL> select name, restore_point_time, database_incarnation#, scn, guarantee_flashback_database
2 from v$restore_point
3 /

NAME
--------------------------------------------------------------------------------
RESTORE_POINT_TIME
---------------------------------------------------------------------------
DATABASE_INCARNATION# SCN GUA
--------------------- ---------- ---
DR_BEFORE_SWITCH

2 4796590 YES

PRODUCTION_BEFORE_SWITCH_PRIMARY

2 4797182 NO


SQL> FLASHBACK DATABASE TO RESTORE POINT DR_BEFORE_SWITCH;

Flashback complete.

SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, standby_became_primary_scn, current_scn
2 from v$database
3 /

CONTROL OPEN_RESETL DATABASE_ROLE FLASHBACK_ON
------- ----------- ---------------- ------------------
STANDBY_BECAME_PRIMARY_SCN CURRENT_SCN
-------------------------- -----------
STANDBY ALLOWED PHYSICAL STANDBY RESTORE POINT ONLY
0 4796590


SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>


Now the database at the Production site has resumed as a Primary database, at SCN#4798237 and the database at the D.R. site has resumed as a Standby database at SCN#4796590  (lower than the Primary).

If you noticed the second entry in v$restore_point at the D.R. site -- Restore Point name "PRODUCTION_BEFORE_SWITCH_PRIMARY" -- this is a 19c enhancement where a Restore Point created on the Primary automatically propagates to the Standby, with the suffix "_PRIMARY"  (to indicate that it came from a database in PRIMARY role) attached to the Restore Point name.

Can I really really be sure that I have reverted both databases to their intended roles ?

I  can verify this again :

{at the Production site}
SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> insert into my_transactions values (1001,'After DR Testing, back to normal life');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from my_transactions order by 1;

TXN_ID TXN_DATA
---------- --------------------------------------------------
1 First at ProductionDC:Primary
1001 After DR Testing, back to normal life

SQL>



{at the D.R site}
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> alter pluggable database orclpdb1 open;

Pluggable database altered.

SQL> connect hemant/hemant@stdbypdb1
Connected.
SQL> select * from my_transactions order by 1;

TXN_ID TXN_DATA
---------- --------------------------------------------------
1 First at ProductionDC:Primary
1001 After DR Testing, back to normal life

SQL>
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 436207616 bytes
Database Buffers 754974720 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, standby_became_primary_scn, current_scn
2 from v$database
3 /

CONTROL OPEN_RESETL DATABASE_ROLE FLASHBACK_ON
------- ----------- ---------------- ------------------
STANDBY_BECAME_PRIMARY_SCN CURRENT_SCN
-------------------------- -----------
STANDBY REQUIRED PHYSICAL STANDBY RESTORE POINT ONLY
0 4802358


SQL>


To verify the behaviour, I added a new row (TXN_ID=1001) in the Primary database at the Production site and then did an OPEN READ ONLY of the Standby database at the D.R. site to check the table.
Note :  So as to not require an Active Data Guard licence, I stopped Recovery on the Standby before I did an OPEN READ ONLY.
Of course, after the verification, I resumed the Standby database in Recovery mode.

This whole exercise also did NOT need the databases to be "permanently" in FLASHBACK ON mode.  I used the Guaranteed Restore Point feature with the Recovery File Dest to generate the minimal flashback logs.  At the end of the exercise, I can DROP the Restore Points.

{at the Production site}
oracle19c>sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 27 00:37:47 2020
Version 19.3.0.0.0

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

Enter user-name: / as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 436207616 bytes
Database Buffers 754974720 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL> drop restore point PRODUCTION_BEFORE_SWITCH;

Restore point dropped.

SQL> alter database open;

Database altered.

SQL> select name, restore_point_time, database_incarnation#, scn, guarantee_flashback_database
2 from v$restore_point
3 /

no rows selected

SQL>


{at the D.R. site}
STDBYDB>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 27 00:40:47 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select name from v$restore_point;

NAME
--------------------------------------------------------------------------------
DR_BEFORE_SWITCH
PRODUCTION_BEFORE_SWITCH_PRIMARY

SQL>
SQL> drop restore point PRODUCTION_BEFORE_SWITCH_PRIMARY;

Restore point dropped.

SQL> drop restore point DR_BEFORE_SWITCH;

Restore point dropped.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>
SQL> select name, restore_point_time, database_incarnation#, scn, guarantee_flashback_database
2 from v$restore_point
3 /

no rows selected

SQL>


The only "catch" is that I had to bring up the Production site (Primary) database in MOUNT mode before I could drop the Restore Point.  So, you need to factor this into you D.R. testing.


Categories: DBA Blogs

Datapump schema export from max_string_size Standard, import to max_string_size Extended => ORA-01450: maximum key length (6398) exceeded

Tom Kyte - Wed, 2020-03-25 19:46
Hi, Team of Oracle Masters I have a schema in a source database version 12.1.0.2 with charset AL32UTF8 and max_string_size Standard. Some tables have columns VARCHAR2(2000 CHAR), which in principle could mean up to 8000 bytes, but here the max is...
Categories: DBA Blogs

equality predicate on clob

Tom Kyte - Wed, 2020-03-25 01:26
I've looked into 9i clob manual (wow, whole manual for one built-in datatype!) and still have a question: What index technique can be used to make effective clob comparison in a query like this: select * from my_table where clob_column = another_c...
Categories: DBA Blogs

ROWTYPE declaration throws ORA-16000 error in standby database

Tom Kyte - Wed, 2020-03-25 01:26
Hi Tom, Could you please clarify, why I get the following error in standby database. <code> Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select log_mode, open_mode, database_role from v$da...
Categories: DBA Blogs

Autonomous Vs Traditional Database

Tom Kyte - Tue, 2020-03-24 07:06
What is difference between Autonomous Database (ADB/ATP/ ATP-Dedicated) or traditional oracle enterprise database?
Categories: DBA Blogs

ORA-01843 not a valid month

Tom Kyte - Tue, 2020-03-24 07:06
Hi, Am getting an error like. ORA-01843 not a valid month.. My stat like... CASE when To_date(column, 'mm/dd/yyyy')>= systdate then 'active ' Else 'inactive ' timezoneconverter(col) Like I seen Nls date format also, it is DD...
Categories: DBA Blogs

Creating Remote Procedure

Tom Kyte - Tue, 2020-03-24 07:06
Hi Tom, We are able to execute remote procedures, but we are not able to create them remotely. Example: 12:59:54 vdr@rdmdev02 > create or replace procedure c2a.r@rmsdev06 2 as 3 begin 4 dbms_output.put_line( 'hello' ); ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs