DBA Blogs

Generating a random number of rows for every date within a date range

Tom Kyte - Thu, 2021-07-29 19:46
I have some working SQL below that generates a row for each employee_id. My goal is to get every date in the range via the function, which works fine standalone, then get N ( random number ( 1-10) of rows for each employee_id for every in the range specified. Once the SQLworks I intend to put this code in a procedure so I can pass it a range of dates. So we can assure we are both running the same version of Oracle I tested this on live SQL. Below is some sample output for a single day only. Please note the employee_id and location_id must exist in their corresponding tables. Since my function call always generates dates with a time of 00:00:00 I plan on eventually adding time to the access_date. <code>EMPLOYEE_ID CARD_NUM LOCATION_ID ACCESS_DATE 1 F123456 10 07302021 09:47:48 1 F123456 5 07282021 19:17:42 2 R33432 4 07282021 02:00:37 3 C765341 2 07282021 17:33:57 3 C765341 6 07282021 17:33:57 3 C765341 1 07282021 18:53:07 4 D564311 6 07282021 03:06:37 ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS'; CREATE OR REPLACE TYPE nt_date IS TABLE OF DATE; / CREATE OR REPLACE FUNCTION generate_dates_pipelined( p_from IN DATE, p_to IN DATE ) RETURN nt_date PIPELINED DETERMINISTIC IS v_start DATE := TRUNC(LEAST(p_from, p_to)); v_end DATE := TRUNC(GREATEST(p_from, p_to)); BEGIN LOOP PIPE ROW (v_start); EXIT WHEN v_start >= v_end; v_start := v_start + INTERVAL '1' DAY; END LOOP; RETURN; END generate_dates_pipelined; / Create table employees( employee_id NUMBER(6), first_name VARCHAR2(20), last_name VARCHAR2(20), card_num VARCHAR2(10), work_days VARCHAR2(7) ); ALTER TABLE employees ADD ( CONSTRAINT employees_pk PRIMARY KEY (employee_id) ); INSERT INTO employees ( EMPLOYEE_ID, first_name, last_name, card_num, work_days ) WITH names AS ( SELECT 1, 'Jane', 'Doe', 'F123456', 'NYYYYYN' FROM dual UNION ALL SELECT 2, 'Madison', 'Smith', 'R33432','NYYYYYN' FROM dual UNION ALL SELECT 3, 'Justin', 'Case', 'C765341','NYYYYYN' FROM dual UNION ALL SELECT 4, 'Mike', 'Jones', 'D564311','NYYYYYN' FROM dual ) SELECT * FROM names; CREATE TABLE locations AS SELECT level AS location_id, 'Door ' || level AS location_name, CASE round(dbms_random.value(1,3)) WHEN 1 THEN 'A' WHEN 2 THEN 'T' WHEN 3 THEN 'G' END AS location_type FROM dual CONNECT BY level <= 10; ALTER TABLE locations ADD ( CONSTRAINT locations_pk PRIMARY KEY (location_id)); SELECT e.employee_id, e.card_num, l.location_id, c.access_date, e.rn, l.rn, c.rn FROM ( SELECT employee_id, round ( dbms_random.value ( 1, 10 ) ) rn, card_num FROM employees ) e INNER JOIN ( SELECT location_id, row_number() OVER (ORDER BY dbms_random.value) AS rn FROM locations ) l ON (e.rn = l.rn) INNER JOIN ( SELECT COLUMN_VALUE AS access_date, row_number() OVER (ORDER BY dbms_random.value) AS rn FROM TABLE(generate_dates_pipelined(SYSDATE, ADD_MONTHS(SYSDATE, 1))) ) c ON (e.rn >= c.rn) ORDER BY employee_id, location_id;</code>
Categories: DBA Blogs

Stats_mode function - Deterministic or Non- Deterministic

Tom Kyte - Thu, 2021-07-29 01:26
Stats_mode function - Deterministic or Non- Deterministic? What does Oracle return when there are multiple keys with same mode (highest) and how?
Categories: DBA Blogs

using explain plan

Tom Kyte - Wed, 2021-07-28 07:06
How should I use the EXPLAIN Plan for tuning of my SQL Statements .Kindly advice me on the sequence of steps to be followed . Some live examples could also be very helpful. With Regards. Ramesh.S
Categories: DBA Blogs

Oracle TDE - AES encryption mode of operation

Tom Kyte - Wed, 2021-07-28 07:06
Product: Oracle Database 19c Transparent Data Encryption (TDE) From the Chapter 10 of Advanced Security Guide, we know for the supported block ciphers "table keys are used in cipher block chaining (CBC) operating mode, and the tablespace keys are used in cipher feedback (CFB) operating mode." https://docs.oracle.com/en/database/oracle/oracle-database/19/asoag/frequently-asked-questions-about-transparent-data-encryption.html Question 1: Both modes of operation require an Initialization Vector to be specified however TDE does not allow the DBA to specify an IV. What IV does TDE actually use? Is it psuedorandom or a fixed value such as all zeros? Question 2: If the IV is fixed, it would leak information, e.g. for CBC mode it makes it deterministic, so the same plaintext always maps to the same ciphertext. So, it is possible to enhance TDE to allow an IV to be specified in the same way that DBMS_CRYPTO currently does? Thanks
Categories: DBA Blogs

how to transfer client files to DB server using PL/SQL

Tom Kyte - Mon, 2021-07-26 18:26
Hi Tom, Is there a way to transfer a file from client machine to DB server using PL/SQL? If this is possible, can you show me an example? Thank you very much. Amy
Categories: DBA Blogs

Opening a Lagging Standby Database (to verify data ?)

Hemant K Chitale - Sat, 2021-07-24 06:04

 As shown in my previous blog post, you can create a Standby Database that lags the Primary by not applying Redo immediately but "waiting" for a specified interval.  It continues to receive and  ArchiveLogs but simply applies each only after the "wait interval".


So, first, the status at the Primary:

oracle19c>echo $ORACLE_SID
ORCLCDB
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 24 18:03:12 2021
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 systimestamp, database_role, current_scn from v$database;

SYSTIMESTAMP DATABASE_ROLE CURRENT_SCN
-------------------------------------------------------------- ---------------- -----------
24-JUL-21 06.03.32.106863 PM +08:00 PRIMARY 13258062

SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> select * from hemant.job_tracking_tbl order by 1;

JOB_END_TIME JOB_ID DB_SCN
-------------------------------------------------------------- ---------- ----------
24-JUL-21 04.57.21.676949 PM 1 13239134
24-JUL-21 05.04.29.870877 PM 2 13241261
24-JUL-21 05.30.17.962275 PM 3 13246616
24-JUL-21 05.39.10.912969 PM 4 13247859
24-JUL-21 05.40.20.865467 PM 5 13248159
24-JUL-21 05.50.23.930352 PM 6 13252182
24-JUL-21 06.00.27.037797 PM 7 13257658

7 rows selected.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/archivelog/ORCLCDB
Oldest online log sequence 310
Next log sequence to archive 312
Current log sequence 312
SQL>


Now, the status at STDB2 (the Standby that is lagging with an enforced 60minutes delay

From the alert.log :
2021-07-24T17:51:15.716720+08:00
PR00 (PID:2924): Media Recovery Log /opt/oracle/archivelog/STDB21_303_1036108814.dbf
PR00 (PID:2924): Media Recovery Delayed for 59 minute(s) T-1.S-304
2021-07-24T17:57:26.299295+08:00
PR00 (PID:2924): Media Recovery Log /opt/oracle/archivelog/STDB21_304_1036108814.dbf
2021-07-24T17:57:44.580258+08:00
PR00 (PID:2924): Media Recovery Delayed for 60 minute(s) T-1.S-305
2021-07-24T18:00:32.550708+08:00
rfs (PID:3452): Archived Log entry 52 added for B-1036108814.T-1.S-311 ID 0xa7521ccd LAD:3
2021-07-24T18:00:33.444329+08:00
rfs (PID:3452): Selected LNO:4 for T-1.S-312 dbid 2778483057 branch 1036108814


oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 24 18:05:53 2021
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> alter database open read only;

Database altered.

SQL> select systimestamp, database_role, open_mode, current_scn from v$database;

SYSTIMESTAMP DATABASE_ROLE OPEN_MODE CURRENT_SCN
--------------------------------------------------- ---------------- ------------ -----------
24-JUL-21 06.06.51.313616 PM +08:00 PHYSICAL STANDBY READ ONLY 13239390

SQL>
SQL> alter pluggable database orclpdb1 open read only;

Pluggable database altered.

SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> select * from hemant.job_tracking_tbl order by 1;

CURRENT_TIMESTAMP JOB_ID CURRENT_SCN
-------------------------------------------------------------- ---------- -----------
24-JUL-21 04.57.21.676949 PM 1 13239134

SQL>


The Primary database is at Log Sequence#312. This Standby has applied only Sequence#304. Let me resume Recovery for some more time and then check the Standby again.

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

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 24 18:11:12 2021
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 318767104 bytes
Database Buffers 872415232 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL> alter database recover managed standby database using archived logfile disconnect from session;

Database altered.

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


Later ... from the STDB2 alert log :

2021-07-24T18:40:22.531574+08:00
PR00 (PID:29990): Media Recovery Log /opt/oracle/archivelog/STDB21_309_1036108814.dbf
PR00 (PID:29990): Media Recovery Delayed for 59 minute(s) T-1.S-310
2021-07-24T18:40:43.574486+08:00
rfs (PID:531): No SRLs available for T-1
2021-07-24T18:40:43.743506+08:00
rfs (PID:531): Opened log for T-1.S-317 dbid 2778483057 branch 1036108814
2021-07-24T18:40:43.762715+08:00
ARC3 (PID:29836): Archived Log entry 57 added for T-1.S-316 ID 0xa7521ccd LAD:1
2021-07-24T18:40:43.762785+08:00
ARC3 (PID:29836): Archive log for T-1.S-316 available in 60 minute(s)
2021-07-24T18:49:27.636427+08:00
PR00 (PID:29990): Media Recovery Log /opt/oracle/archivelog/STDB21_310_1036108814.dbf
PR00 (PID:29990): Media Recovery Delayed for 60 minute(s) T-1.S-311
2021-07-24T18:50:45.257290+08:00
rfs (PID:531): Archived Log entry 58 added for B-1036108814.T-1.S-317 ID 0xa7521ccd LAD:3
2021-07-24T18:50:46.045279+08:00
rfs (PID:531): Selected LNO:4 for T-1.S-318 dbid 2778483057 branch 1036108814


oracle19c>echo $ORACLE_SID
STDB2
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 24 18:51:27 2021
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> alter database open read only;

Database altered.

SQL> alter pluggable database orclpdb1 open read only;

Pluggable database altered.

SQL> alter session set container=orclpdb1;

Session altered.

SQL>
SQL> select * from hemant.job_tracking_tbl order by 1;

JOB_END_TIME JOB_ID DB_SCN
-------------------------------------------------------------- ---------- ----------
24-JUL-21 04.57.21.676949 PM 1 13239134
24-JUL-21 05.04.29.870877 PM 2 13241261
24-JUL-21 05.30.17.962275 PM 3 13246616
24-JUL-21 05.39.10.912969 PM 4 13247859
24-JUL-21 05.40.20.865467 PM 5 13248159
24-JUL-21 05.50.23.930352 PM 6 13252182

6 rows selected.

SQL>
SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
24-JUL-21 06.53.31.159094 PM +08:00

SQL>


So, now at 18:47, STDB2 has applied Sequence#310 and the database now shows data that came through that ArchiveLog. Upto JOB_ID=6, JOB_END_TIME=05:50:23pm
The Primary has already progressed further.

SQL> l
1* select * from hemant.job_tracking_tbl order by 1
SQL> /

JOB_END_TIME JOB_ID DB_SCN
-------------------------------------------------------------- ---------- ----------
24-JUL-21 04.57.21.676949 PM 1 13239134
24-JUL-21 05.04.29.870877 PM 2 13241261
24-JUL-21 05.30.17.962275 PM 3 13246616
24-JUL-21 05.39.10.912969 PM 4 13247859
24-JUL-21 05.40.20.865467 PM 5 13248159
24-JUL-21 05.50.23.930352 PM 6 13252182
24-JUL-21 06.00.27.037797 PM 7 13257658
24-JUL-21 06.10.33.163203 PM 8 13259223
24-JUL-21 06.20.36.839944 PM 9 13261275
24-JUL-21 06.22.46.972310 PM 10 13261560
24-JUL-21 06.30.39.787880 PM 11 13262799
24-JUL-21 06.37.18.623659 PM 12 13263658
24-JUL-21 06.40.41.713016 PM 13 13264263
24-JUL-21 06.50.43.755835 PM 14 13265798

14 rows selected.

SQL>


So, the operative methods at the Standby are :
 For Recovery :
1.  alter database recover managed standby database USING ARCHIVED LOGFILE disconnect from session

To Open and Query :
1. alter database recover managed standby database CANCEL
2. alter database OPEN READ ONLY
3. alter pluggable database <pdbname>   OPEN READ ONLY

To resume Recovery :
1. shutdown immediate
2. startup mount
3. alter database recover managed standby database USING ARCHIVED LOGFILE disconnect from session

While the Primary must specify a DELAY value in the log_archive_dest_n parameter for this destination Standby




Categories: DBA Blogs

Security steps to protect a database

Tom Kyte - Fri, 2021-07-23 17:06
Which are the important security steps to protect a database whose application will be in internet? Which are the differences between Connection Manager and Database Firewall? If I have native network encryption configured do I need to do something on application server level? I mean, for example, configuring the thin jdbc client network?
Categories: DBA Blogs

how to use dbms_stats.gather_databse_stats

Tom Kyte - Fri, 2021-07-23 17:06
Tom: i try to use dbms_stats to give me a report of what statistics is missing, what i do is: declare a dbms_stats.objecttab; begin dbms_stats.gather_database_stats(OPTIONS=>'LIST EMPTY',OBJLIST=>a); end; after that how can i know the content of a?
Categories: DBA Blogs

Getting Value from JSON array using PL/SQL

Tom Kyte - Fri, 2021-07-23 17:06
I need help. I do I get data from the stats object in the JSON array using PL/SQL? <code>{ "items": [ { " stats": { "m_date": "2019-05-31T00:00:00", "v_num": "0040012", "pk_num": "0562", "amt": 94, "bal": 75, "disc": 13 } } }</code> Thanks
Categories: DBA Blogs

Invalid XML character Error - How to find the invalid character from a VARCHAR2 database column?

Tom Kyte - Fri, 2021-07-23 17:06
Hello, Oracle newbie here. I am getting this error "Character reference "&#56256" is an invalid XML character" for XML data that is printed onto a report. The XML data that is causing the issue is from a VARCHAR2 data column in the database. I have filtered out the column to a separate backup table I have created and I want to go through the records in order to find where this invalid character is in. Then I need to write an update statement replacing the invalid character with a valid one. I am not sure how to do this. Is there a regex I can write with a SELECT statement for this? I tried below, but they didn't bring up any results: <code>select * from tabname where instr(colname,chr(56256)) > 0; </code> <code>select * from tabname where colname like unistr('%\dbc0%'); </code> Glad if someone one can help. Thank you!
Categories: DBA Blogs

Usage Flashback Query with Views

Tom Kyte - Fri, 2021-07-23 17:06
Dear TOM! Is it save to use Flashback Query with views? <b>Setup:</b> <code>CREATE VIEW my_view AS SELECT a.*, b.* FROM table_a a JOIN table_b b ON (a.ID = b.ID_A);</code> <b>Flashback Query at view level:</b> <code>SELECT v.* FROM my_view AS OF TIMESTAMP systimestamp - INTERVAL '30' SECOND v; </code> <b>Flashback Query at table level:</b> <code>SELECT a.*, b.* FROM table_a AS OF TIMESTAMP systimestamp - INTERVAL '30' SECONDa JOIN table_b AS OF TIMESTAMP systimestamp - INTERVAL '30' SECONDb ON (a.ID = b.ID_A);</code> <b>Will both queries <u>reliably</u> deliver the same result?</b> <b>Will the result of both queries be <u>consistent</u> over all affected tables?</b> Thank you, Matthias
Categories: DBA Blogs

Transaction after SELECT statement

Tom Kyte - Fri, 2021-07-23 17:06
Hi, Tom. I am investigating a process of creating a transaction after a SELECT statement as a DML operator. Here is a steps, <code> COMMIT; ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE; SELECT * FROM V$TRANSACTION; --Wait one minute to remember time of previous select execution SELECT DBMS_TRANSACTION.LOCAL_TRANSACTION_ID FROM DUAL; SELECT * FROM V$TRANSACTION; --the transaction create time is before call DBMS_TRANSACTION.LOCAL_TRANSACTION_ID </code> The questions are, We don't have any TX locks but the transaction exists. Why? After call DBMS_TRANSACTION.LOCAL_TRANSACTION_ID we can see a new transaction in V$TRANSACTION but before don't. Why? Also we don't see any transaction if ISOLATION_LEVEL = READ COMMITTED. Why? According to your excellent book Expert Oracle Chapter 8, "A transaction implicitly begins with the first statement that modifies data (the first statement that gets a TX lock)."
Categories: DBA Blogs

Help with trying to decide with authentication approach should be setup

Tom Kyte - Fri, 2021-07-23 17:06
Please help me keep my sanity by pointing me in the right direction when deciding the authentication approach to use with Oracle 19c databases. This is a very confusing topic since it deals with a few areas that require experience with Microsoft Active Directory or other nonRDBMS software. Also, each one of them isn't simple to use and Oracle training didn't go into implementing each of these when I attended in early 2000. This is driving me insane especially since I thought that database authentication was already secure enough. Here are all of the Oracle authentication methods that I know exist: <code>- Oracle database authentication ( create user identified by password ) - Operating System authentication ( create OPS$user identified externally ) - Kerberos / Radius (create user identified externally as 'kerberos_name' ) - certificate_DN ( create user identified externally as 'certificat_DN' ) (is this SSL authentication?) - Globally as 'directory_DN' ( create user identified GLOBALLY as 'directory_DN') ( Sigh ... this sounds so much like other authentication options.)</code> I'm not sure if these are authentication approaches, but I know they mingle with authentication and add to the confusion: - Centrally Managed Users - Enterprise User Security Also, knowning when Microsoft Active Directory can be used is confusing. I think these require Microsoft Active Directory: - Kerberos - Centrally Managed Users To muddy the water more, based on what I have seen, Kerberos can be used with Centrally Managed Users which is confusing since it seems like Kerberos with AD is enough. Finally, I keep seeing that Oracle Internet Directory is needed in some cases. The only one that seems to need is "Enterprise User Security" which seems like if we have Microsoft Active Directory, we would use "Centrally Managed Users" setup. I know i've mentioned a lot above. It would be nice if you can at a minimum tell me which one I should focus on to setup a secure authentication approach without going overboard. Which approach would recommend to use for the most secure authentication with the following in our infrastructure: <code>- Enterprise Edition Oracle 19c on Linux with April 2021 RU applied - SQLNET.TCP.INVITED_NODES - FAILED_LOGIN_ATTEMPTS=3 - orc12c_verify_function - We don't allow use of password file - Limit access through Oracle "grants" - We have changed all default passwords - We use profiles to expire passwords regularly - Microsoft active directory which we aren't using. - We use CA signed SSL certificates with strong encryption algorithms with FIPS-140-2 configured between database server and clients so we could use "Authentication with Public Key Infrastructure". - Our databases are only accessed through the applications not by individual users</code> Why isn't the above good enough? The only thing we aren't using is Microsoft Active directory or SSL Client Authentication. I thought that having Oracle database authentication with a complex password with the use of CA signed certificates would be a secure authentication approach. Why would Oracle feel the need to add more authentication approaches and confuse most of us? With this approach, a client needs to know the password. A client needs to have been given the CA signed certificate in order to be allowed to connect to the database. A client is forced to use a complex password, is only given limited password attempts with FAILED_LOGIN_ATTEMPTS=3, Finally, we have TCP.INVITED_NODES setup so only those clients with IPs in that list are allowed to connect. Geezzz, why is more needed? Thanks for your help, John
Categories: DBA Blogs

A Standby that lags the Primary by a deliberate Delay

Hemant K Chitale - Tue, 2021-07-20 06:24

 As I noted in my previous blog post, with multiple Standby databases, you can have one or more of them, lagging the Primary. This allows the organisation a database that can be quickly opened for data recovery in case someone makes a mistake and deletes data or drops tables/objects from the Primary and the delete/drop has already been replicated to the first Standby.

Here is a quick demo.

At the Primary I have :

SQL> alter system archive log current;

System altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/archivelog/ORCLCDB
Oldest online log sequence 280
Next log sequence to archive 282
Current log sequence 282
SQL>


At the first Standby "STDBYDB", I have :

2021-07-20T17:50:05.870763+08:00
PR00 (PID:2912): Media Recovery Waiting for T-1.S-282 (in transit)
2021-07-20T17:50:06.354006+08:00
ARC3 (PID:2736): Archived Log entry 35 added for T-1.S-281 ID 0xa7521ccd LAD:1
2021-07-20T17:50:06.396543+08:00
rfs (PID:3263): Archival of T-1.S-281 complete
2021-07-20T17:50:06.527483+08:00
rfs (PID:3263): Selected LNO:4 for T-1.S-282 dbid 2778483057 branch 1036108814
2021-07-20T17:50:07.008298+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 282 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log


At the second Standby "STDB2", I have :

2021-07-20T17:50:09.484608+08:00
PR00 (PID:2975): Media Recovery Waiting for T-1.S-282 (in transit)
2021-07-20T17:50:09.500278+08:00
rfs (PID:3216): Opened log for T-1.S-282 dbid 2778483057 branch 1036108814
2021-07-20T17:50:09.527462+08:00
ARC3 (PID:2867): Archived Log entry 22 added for T-1.S-281 ID 0xa7521ccd LAD:1


To introduce a delay in applying ArchiveLogs at STDBY, I specify the DELAY parameter at the Primary database  :

SQL> show parameter log_archive_dest_3

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string SERVICE=STDB2 ASYNC VALID_FOR=
(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=STDB2
log_archive_dest_30 string
log_archive_dest_31 string
SQL> alter system set log_archive_dest_3='SERVICE=STDB2 DELAY=60 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDB2';

System altered.

SQL> show parameter log_archive_dest_3

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string SERVICE=STDB2 DELAY=60 ASYNC V
ALID_FOR=(ONLINE_LOGFILES,PRIM
ARY_ROLE) DB_UNIQUE_NAME=STDB2
log_archive_dest_30 string
log_archive_dest_31 string
SQL>


So, I have introduced a lag of 60minutes for STDB2.  Over the next 60minutes, the Primary will continue generating Redo and ArchiveLogs and both Standbys will be receiving them.  But STDB2 will apply them only after 60minutes.  

However, to enforce this, I must also cause the Standby to *not* use Real Time Apply, so I must change the RECOVER command at the Standby  Note, however, that this must be done on the Standby first !  The Standby must start it's recovery with "USING ARCHIVED LOGFILE" *before* the Primary sets a DELAY value for the target log_archive_dest_n

oracle19c>echo $ORACLE_SID
STDB2
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 20 18:14:26 2021
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> alter database recover managed standby database using archived logfile disconnect from session;

Database altered.

SQL>


If I do not change the RECOVER command at the Standby to explicitly specify "USING ARCHIVED LOGFILE", it still defaults to Real Time Apply and ignores the DELAY specified by the Primary. I get the message in STDB2 alert log :

 rfs (PID:13712): WARN: Managed Standby Recovery started with REAL TIME APPLY
rfs (PID:13712): WARN: DELAY 60 minutes specified at primary ignored


I review the alert log files for all 3 databases about an hour later.

This is the first Standby (STDBYDB)

2021-07-20T19:10:57.161885+08:00
PR00 (PID:2912): Media Recovery Waiting for T-1.S-297 (in transit)
2021-07-20T19:10:57.324337+08:00
rfs (PID:3263): Selected LNO:4 for T-1.S-297 dbid 2778483057 branch 1036108814
2021-07-20T19:10:58.401720+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 297 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
2021-07-20T19:14:39.910894+08:00
ARC3 (PID:2736): Archived Log entry 51 added for T-1.S-297 ID 0xa7521ccd LAD:1
2021-07-20T19:14:39.943728+08:00
rfs (PID:3263): Standby controlfile consistent with primary
2021-07-20T19:14:40.136187+08:00
rfs (PID:3263): Selected LNO:4 for T-1.S-298 dbid 2778483057 branch 1036108814
2021-07-20T19:14:40.136811+08:00
PR00 (PID:2912): Media Recovery Waiting for T-1.S-298 (in transit)
2021-07-20T19:14:41.180355+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 298 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log

SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string STDBYDB
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
13123569

SQL>


And this is the Second Standby (STDB2) :

2021-07-20T19:10:58.180405+08:00
rfs (PID:21331): No SRLs available for T-1
2021-07-20T19:10:58.319036+08:00
ARC0 (PID:2857): Archived Log entry 37 added for T-1.S-296 ID 0xa7521ccd LAD:1
2021-07-20T19:10:58.319358+08:00
ARC0 (PID:2857): Archive log for T-1.S-296 available in 60 minute(s)
2021-07-20T19:10:58.320321+08:00
rfs (PID:21331): Opened log for T-1.S-297 dbid 2778483057 branch 1036108814
2021-07-20T19:14:40.363888+08:00
rfs (PID:21331): Archived Log entry 38 added for B-1036108814.T-1.S-297 ID 0xa7521ccd LAD:3
2021-07-20T19:14:40.782081+08:00
rfs (PID:21331): Selected LNO:4 for T-1.S-298 dbid 2778483057 branch 1036108814
2021-07-20T19:15:12.430015+08:00
PR00 (PID:26793): Media Recovery Log /opt/oracle/archivelog/STDB21_286_1036108814.dbf
PR00 (PID:26793): Media Recovery Delayed for 60 minute(s) T-1.S-287

SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string STDB2
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
13108691

SQL>


So, while the first Standby (STDBYDB) has already applied and archived Sequence#297 and is currently applying Sequence#298 from the Standby logfile, the second Standby (STDB2) has archived Sequence#297 and received Sequence#298 but notifies that Sequence#286 is currently being applied and the apply Sequence#297 is delayed by 60minutes.
I can also run an SCN_TO_TIMESTAMP query in the Primary (this cannot be executed on a Standby that is not OPEN) :

SQL> select scn_to_timestamp(13123569) At_STDBYDB from dual;

AT_STDBYDB
---------------------------------------------------------------------------
20-JUL-21 07.13.39.000000000 PM

SQL> select scn_to_timestamp(13108691) At_STDB2 from dual;

AT_STDB2
---------------------------------------------------------------------------
20-JUL-21 06.15.07.000000000 PM

SQL>


This shows that STDB2 is lagging by about 60minutes
So, if any "bad action"  (deletion of data or dropping of objects) is detected at the Primary (and a Flashback option is not available), the Standby can be OPENed Read Only to view the data as it was 1hour ago.  
I'll show that option in my next blog post.



Categories: DBA Blogs

Two New ASH FORCE_MATCHING_SIGNATURE scripts

Bobby Durrett's DBA Blog - Thu, 2021-07-15 17:40

I just put two new scripts on my OracleDatabaseTuningSQL GitHub repository. These came out of a PeopleSoft Financials performance problem I was working on that involved a lot of similar SQL statements that used constants instead of bind variables, so they did not show up at the top of the AWR report. I had to look at ASH data to find them and had to group by their force matching signature to group the similar statements together. These are the two scripts:

ashfmscount.sql – Looks at a single application engine session and groups all the time spent by force matching signature to find the queries that consumed the most time. I used my simple ashdump.sql script to dump out a few rows when I knew the app engine was running and I found the SESSION_ID and SESSION_SERIAL# values there.

ashtopelapsed.sql – This is meant to look like the SQL by elapsed time report on an AWR report except that it groups SQL by force matching signature but gives an example sql id with its text to give you an idea of what the signature represents. Might be good to run this the next time my AWR report does not have any long running SQL statement on the top SQL report.

I really used the first one to resolve the issue along with various other scripts to get to that point. I created the second one just now as a possible future script to use in addition to an AWR report. I didn’t check the ASH report to see if this is a duplicate of it, but these two new scripts work well.

Bobby

Categories: DBA Blogs

Creating [1 or more] Additional Standby Database[s]

Hemant K Chitale - Thu, 2021-07-15 10:15

 In my previous demos, I've shown 1 Standby (STDBYDB) for the Primary (ORCLCDB).

However, an organisation may choose to have additional Standby Databases.  

Note that each Standby Database must be licensed.  If the Primary is licensed by Processor count (say 32 processors), each Standby must also be licensed by Processor count, although the Standbys may be running on servers with fewer processors, thus needing smaller licensing than the Primary.

Why would an organisation have additional Standbys ?  Multiple reasons

1.  Having additional Disaster Recovery Data Centres.  Thus, with 2 DRCs in addition to the Production, there may be 2 Standby Databases.

2. Choosing to have a Standby that can be used to run Reporting Queries when it is OPENed READ ONLY for, say, 4hours a day.  [If the organisation has the Active Dataguard Licence, the Standby can be running Reporting Queries all 24hours]  Without the Active Dataguard Licence, Recovery has to be stopped at this Standby for those 4hours, though it will continue to receive and Archive Redo from the Primary.  Once the 4hour window is closed, Recovery can resumed and the Standby will apply all the "pending" Redo [from the ArchiveLogs that it has received] to "catch-up" to the Primary

3. To test D.R. scenarios, whereby a secondary Standby is opened for Read-Write operations, without impacting the actual Production and first Standby instance which continue to be in-sync throughoug the D.R. testing.  The Standby may be opened Read Write with a manually created Guaranteed Restore Point   OR   it may be opened as a Snapshot Standby

4. To have an environment that can be opened day-time hours for UAT while it is resynced (i.e. Redo Apply done) with the Primary every night.  This will require the Standby to have Flashback Restore Points created and reverted to each day.

5. To have a Standby that is lagging the Primary by, say, 30minutes or 1hour or 4hours deliberately.  This allows the organisation a database that can be quickly opened for data recovery in case someone makes a mistake and deletes data or drops tables/objects from the Primary and the delete/drop has already been replicated to the first Standby.

6. To run [additional] Database Backups at a Standby DRC.  I prefer that the Primary and each Standby be running local database backups.


I have built my Second Standby with these parameters :

initSTDB2.ora :

*.audit_file_dest='/opt/oracle/admin/STDB2/adump'
*.audit_sys_operations=false
*.audit_trail='none'
*.commit_logging='batch'
*.commit_wait='nowait'
*.compatible='19.0.0'
*.control_files='/opt/oracle/oradata/STDB2/control01.ctl','/opt/oracle/oradata/STDB2/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/opt/oracle/oradata'
*.db_file_name_convert='/opt/oracle/oradata/ORCLCDB','/opt/oracle/oradata/STDB2'
*.db_name='ORCLCDB'
*.db_recovery_file_dest_size=10G
*.db_recovery_file_dest='/opt/oracle/FRA/STDB2'
*.db_unique_name='STDB2'
*.diagnostic_dest='/opt/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=STDB2XDB)'
*.enable_pluggable_database=true
*.fal_server='ORCLCDB'
*.filesystemio_options='setall'
*.local_listener='LISTENER_STDB2'
*.log_archive_config='DG_CONFIG=(ORCLCDB, STDBYDB, STDB2)'
*.log_archive_dest_1='LOCATION=/opt/oracle/archivelog/STDB2'
###*.log_archive_dest_2='SERVICE=ORCLCDB SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLCDB'
*.log_file_name_convert='/opt/oracle/oradata/ORCLCDB','/opt/oracle/oradata/STDB2'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=384m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1152m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'


and listener.ora :
LISTENER_STDB2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1532))
)
)

SID_LIST_LISTENER_STDB2 =
(SID_LIST=
(SID_DESC =
(ORACLE_HOME = /opt/oracle/product/19c/dbhome_1)
(SID_NAME = STDB2)
)
)

and the password file copied from the Primary -- although Oracle will copy the password file in recent versions, I still prefer to do it myself
oracle19c>pwd
/opt/oracle/product/19c/dbhome_1/dbs
oracle19c>ls orapw$ORACLE_SID
orapwSTDB2
oracle19c>


Note how I have disabled log_archive_dest_2.  In this case, this Standby will never be part of a Role Reversal done either by Switchover or Failover [where it could become a Primary in the D.R. Data Centre and ship Redo to the old Primary in the Production Data Centre]

oracle19c>lsnrctl start listener_STDB2

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-JUL-2021 22:07:14

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

Starting /opt/oracle/product/19c/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/ora19cs1/listener_stdb2/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1532)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1532)))
STATUS of the LISTENER
------------------------
Alias listener_STDB2
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 15-JUL-2021 22:07:14
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/ora19cs1/listener_stdb2/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1532)))
Services Summary...
Service "STDB2" has 1 instance(s).
Instance "STDB2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
oracle19c>
oracle19c>echo $ORACLE_SID
STDB2
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 15 22:21:47 2021
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 318767104 bytes
Database Buffers 872415232 bytes
Redo Buffers 7639040 bytes
SQL>


Then, at the Primary :

SQL> show parameter log_archive_config

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(ORCLCDB, STDBYDB)
SQL> alter system set log_archive_config='DG_CONFIG=(ORCLCDB, STDBYDB, STDB2)'; -- note that this update is also done at the first Standy STDBYDB2

System altered.

SQL> show parameter log_archive_dest_2

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=STDBYDB SYNC AFFIRM VA
LID_FOR=(ONLINE_LOGFILES,PRIMA
RY_ROLE) DB_UNIQUE_NAME=STDBYD
B
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
SQL> show parameter log_archive_dest_3

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string
SQL> alter system set log_archive_dest_3='SERVICE=STDB2 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDB2';

System altered.

SQL>


Note that log_archive_dest_2 is set to SYNC AFFIRM but log_archive_dest_3 is set to ASYNC and [NOAFFIRM]. Because I can afford a slight lag (latency) for this Standby.  Maybe it is at a Data Centre that is much further away, increasing the latency.



Then, I copy the Primary to the new Standby using RMAN (see the previous example)

-- assuming that the tnsnames.ora at the Primary is updated to include "STDB2"

oracle19c>rman target sys/manager auxiliary sys/manager@STDB2

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jul 15 22:24:00 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCLCDB (DBID=2778483057)
connected to auxiliary database: ORCLCDB (not mounted)

RMAN> duplicate target database for standby from active database dorecover;

Starting Duplicate Db at 15-JUL-21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=256 device type=DISK
current log archived

contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/opt/oracle/product/19c/dbhome_1/dbs/orapwSTDB2' ;
}
executing Memory Script

Starting backup at 15-JUL-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=273 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=274 device type=DISK

....
....
....
....
....

Finished Duplicate Db at 15-JUL-21

RMAN>


After that I shutdown and restart the new Standby and enable Recovery

SQL> shutdown immediate;
ORA-01109: database not open


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

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

Database altered.

SQL>



Let me now verify that the Primary is shipping Redo to *2* Standbys

2021-07-15T22:49:55.328276+08:00
ALTER SYSTEM ARCHIVE LOG
2021-07-15T22:49:56.801388+08:00
LGWR (PID:6404): SRL selected to archive T-1.S-272
LGWR (PID:6404): SRL selected for T-1.S-272 for LAD:2
LGWR (PID:6404): SRL selected to archive T-1.S-272. However, all SRLs are currently active
2021-07-15T22:49:57.005260+08:00
Thread 1 advanced to log sequence 272 (LGWR switch)
Current log# 2 seq# 272 mem# 0: /opt/oracle/oradata/ORCLCDB/redo02.log
2021-07-15T22:49:57.973514+08:00
NET (PID:28568): Archived Log entry 588 added for T-1.S-271 ID 0xa7521ccd LAD:1
2021-07-15T22:49:59.487773+08:00
TT03 (PID:8344): SRL selected for T-1.S-272 for LAD:3
2021-07-15T22:50:06.667866+08:00
ALTER SYSTEM ARCHIVE LOG
2021-07-15T22:50:07.669103+08:00
LGWR (PID:6404): SRL selected to archive T-1.S-273
LGWR (PID:6404): SRL selected for T-1.S-273 for LAD:2
LGWR (PID:6404): SRL selected to archive T-1.S-273. However, all SRLs are currently active
2021-07-15T22:50:07.862847+08:00
Thread 1 advanced to log sequence 273 (LGWR switch)
Current log# 3 seq# 273 mem# 0: /opt/oracle/oradata/ORCLCDB/redo03.log
2021-07-15T22:50:08.191087+08:00
NET (PID:28568): Archived Log entry 592 added for T-1.S-272 ID 0xa7521ccd LAD:1
2021-07-15T22:52:42.492619+08:00
ALTER SYSTEM ARCHIVE LOG
2021-07-15T22:52:42.988590+08:00
LGWR (PID:6404): SRL selected to archive T-1.S-274
LGWR (PID:6404): SRL selected for T-1.S-274 for LAD:2
LGWR (PID:6404): SRL selected to archive T-1.S-274. However, all SRLs are currently active
2021-07-15T22:52:43.059342+08:00
Thread 1 advanced to log sequence 274 (LGWR switch)
Current log# 1 seq# 274 mem# 0: /opt/oracle/oradata/ORCLCDB/redo01.log
2021-07-15T22:52:43.142797+08:00
NET (PID:28568): Archived Log entry 594 added for T-1.S-273 ID 0xa7521ccd LAD:1
2021-07-15T22:52:43.917398+08:00
TT03 (PID:8344): SRL selected for T-1.S-274 for LAD:3



and

SQL> select protection_mode from v$database;

PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY

SQL>


Thus, we can see that the Primary is shipping Redo to both "LAD2" and "LAD3"

While the first Standby (STDBYDB) shows :

ARC2 (PID:23577): Archived Log entry 26 added for T-1.S-272 ID 0xa7521ccd LAD:1
2021-07-15T22:50:07.448944+08:00
rfs (PID:23634): Archival of T-1.S-272 complete
2021-07-15T22:50:07.533082+08:00
PR00 (PID:23765): Media Recovery Waiting for T-1.S-273 (in transit)
2021-07-15T22:50:07.668714+08:00
rfs (PID:23634): Selected LNO:4 for T-1.S-273 dbid 2778483057 branch 1036108814
2021-07-15T22:50:09.298541+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 273 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
2021-07-15T22:52:42.921683+08:00
rfs (PID:23634): Standby controlfile consistent with primary
rfs (PID:23634): No SRLs available for T-1
rfs (PID:23634): Waiting for T-1.S-273 archival to complete
2021-07-15T22:52:42.929839+08:00
ARC0 (PID:23569): Archived Log entry 27 added for T-1.S-273 ID 0xa7521ccd LAD:1
2021-07-15T22:52:42.935444+08:00
rfs (PID:23634): Archival of T-1.S-273 complete
2021-07-15T22:52:42.988242+08:00
rfs (PID:23634): Selected LNO:4 for T-1.S-274 dbid 2778483057 branch 1036108814
2021-07-15T22:52:43.001151+08:00
PR00 (PID:23765): Media Recovery Waiting for T-1.S-274 (in transit)
2021-07-15T22:52:43.002924+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 274 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log


and

SQL> select protection_mode from v$database;

PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY

SQL>


And the new Standby (STDB2) shows :

Recovery of Online Redo Log: Thread 1 Group 4 Seq 272 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDB2/stdbredo01.log
2021-07-15T22:50:08.467719+08:00
rfs (PID:23637): No SRLs available for T-1
2021-07-15T22:50:08.530396+08:00
PR00 (PID:23655): Media Recovery Waiting for T-1.S-273 (in transit)
2021-07-15T22:50:08.532241+08:00
rfs (PID:23637): Opened log for T-1.S-273 dbid 2778483057 branch 1036108814
2021-07-15T22:50:08.532340+08:00
ARC0 (PID:23450): Archived Log entry 13 added for T-1.S-272 ID 0xa7521ccd LAD:1
2021-07-15T22:52:43.299392+08:00
rfs (PID:23637): Archived Log entry 14 added for B-1036108814.T-1.S-273 ID 0xa7521ccd LAD:3
2021-07-15T22:52:43.917098+08:00
rfs (PID:23637): Selected LNO:4 for T-1.S-274 dbid 2778483057 branch 1036108814
2021-07-15T22:52:44.137685+08:00
PR00 (PID:23655): Media Recovery Log /opt/oracle/archivelog/STDB21_273_1036108814.dbf
PR00 (PID:23655): Media Recovery Waiting for T-1.S-274 (in transit)
2021-07-15T22:52:44.576855+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 274 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDB2/stdbredo01.log


and

SQL> select protection_mode from v$database;

PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

SQL>


So, now the Primary is shipping Redo to 2 Standbys. The first Standby (STDBYDB) is in MAXIMUM AVAILABILITY mode, the second Standby (STDB2) is in MAXIMUM PERFORMANCE mode.
(For simplicity, I haven't created additional Standby Redo Logs, I have only Standby Redo Log at Primary and each of the Standbys --- it is group#4, filename "stdbredo01.log" in the listing above)

    

In the next blog post, I will explore how to have STDB2 deliberately lagging the Primary by 30minutes (use case 5 in the list at the beginning of this blog post)


Categories: DBA Blogs

Plan Change Due to Index Partition Names Mismatch

Bobby Durrett's DBA Blog - Wed, 2021-07-14 14:51

I want to document a bug I ran across in Oracle 11.2.0.3 on HP-UX Itanium and how it caused unexpected plan changes that would bring the database to its knees with many long running queries running the bad plan. I found that the problem table had a local index whose partition names did not match the partition names of the table and that recreating the index with partition names that matched the table resolved the issue.

We have a busy and important Oracle database that had queries that ran fine for weeks and months and then suddenly changed to a bad plan. They all had a similar pattern. The good plan did a simple range scan on a certain partition’s index. The bad plan did a fast full index scan.

Good:

PARTITION RANGE SINGLE | 
 INDEX RANGE SCAN | PROBLEM_INDEX

Bad:

PARTITION RANGE SINGLE | 
 INDEX FAST FULL SCAN | PROBLEM_INDEX

The query accessed the table using the first two columns of PROBLEM_INDEX and the first column of the index is the partitioning column for the range partitioned table. So, call the first two columns of the index PARTITIONING_C and OTHER_C then the query was like:

select
...
from problem_table
where
PARTITIONING_C = :bindvar1 and
OTHER_C = :bindvar2
...

I have known for a long time that some choice of bind variable values was causing the plan to flip to the fast full scan on the index partition. But I did not know that it had to do with the names of the index’s partitions. The table and index creation scripts must have looked something like this:

create problem_table
(
PARTITIONING_C NUMBER,
OTHER_C NUMBER,
...
)
PARTITION BY RANGE (PARTITIONING_C)
(
PARTITION P1000 VALUES LESS THAN (1000),
PARTITION P2000 VALUES LESS THAN (2000),
PARTITION P3000 VALUES LESS THAN (3000),
PARTITION P4000 VALUES LESS THAN (4000),
PARTITION P5000 VALUES LESS THAN (5000));

CREATE UNIQUE INDEX problem_index ON problem_table
(PARTITIONING_C, OTHER_C , ...)
LOCAL (  
  PARTITION P2000,
  PARTITION P3000,
  PARTITION P4000,
  PARTITION P5000,
  PARTITION P6000);

The index has the same number of partitions as the table and a lot of them have the same names, but they do not line up. I found this bug in the version of Oracle that we are on:

Bug 14013094 – DBMS_STATS places statistics in the wrong index partition

I am not sure that I am hitting that bug, but I am hitting some similar bug because 14013094 relates to index partitions names that do not match table partition names. For one partition of the problem index the statistics were set to 0 but its corresponding table partition had millions of rows. It would be as if partition P3000 on problem_table had 20,000,000 rows in stats and corresponding partition P4000 on problem_index had 0 rows. If I gathered index partition statistics on P4000 it correctly set stats to 20,000,000. If I gathered table partition statistics on P3000 it cleared the index partition stats on P4000 setting them to 0! (!!) Yikes! How weird is that? Seems obvious to me it is a bug, but maybe not exactly 14013094. I tried dropping and recreating the index leaving the partition names as they are, but it did not resolve the issue. Then I just created the index letting it default to matching partition names like this:

CREATE UNIQUE INDEX problem_index ON problem_table
(PARTITIONING_C, OTHER_C , ...)
LOCAL;

I’m not sure how the partition names got mismatched but it is a simple fix. It took me a while staring at the partition statistics to realize what was going on and then it took a while to prove out the fix. We do not yet have this in production, but I believe we have nailed down the cause of the plan changes. In the past I have been using SQL Profiles to lock in the plans of any new query that uses the problem table, but I plan to run without them after putting in the index. I kick myself for not researching this earlier, but it was not obvious so maybe it was not crazy to use SQL Profiles for a while. But it became a burden to keep using them and it left the system vulnerable to a plan change on any query on the problem table that did not already have a SQL Profile.

In a lot of ways this is a simple query tuning problem with bad zero stats on a partition with millions of rows. Any time the statistics are wildly inaccurate bad plans are likely. But tracking down the reason for the zero statistics was not simple. I should mention that some of the table partitions are empty and have 0 rows. In fact, to take our example, imagine that table partition P4000 has 0 rows just like index partition P4000. No one would suspect that the statistics are wrong unless you realize that P4000 on the index corresponds not to P4000 but to P3000 on the table and P3000 has 20,000,000 rows!

Bind variables lead to these unexpected plan changes when the plan is different when different values are passed into the variables. It is a fundamental tradeoff of using bind variables to reduce the parsing that constants would cause. If one set of bind variable values causes a bad plan that plan gets locked into memory and it is used for all the other values passed to the query until the plan is flushed out of memory. There are some cases where the optimizer will look at the values passed into the bind variables and choose between multiple plans but that did not occur in this problem.

So, what can someone get from this post if they are not on 11.2.0.3 and do not have locally partitioned indexes with similar partition names to the table but offset and mismatched? I think for me the point is after using a SQL Profile to resolve a problem query plan to dig deeper into the underlying reason for the bad plan. I expect bad plans because I think that the optimizer is limited in its ability to correctly run a query even if it has the best possible statistics. Also, since I support many databases, I do not have time to dig deeply into the underlying reason for every bad plan. But I need to dig deeper when similar queries keep getting the same kind of bad plans. In many cases bad statistics lead to bad plans and it is a lot better to fix the statistics once than to keep using SQL Profiles and hints repeatedly to fix similar queries with the same sort of switches to bad plans. In this case I had the bizarre fix of recreating the index with partition names that match the table and that ensured that the statistics on the index partitions were accurate and that I no longer need SQL Profiles to lock in the good plan.

Bobby

Categories: DBA Blogs

Failover to a Standby with Data Loss

Hemant K Chitale - Sat, 2021-07-10 05:47

 One scenario I consider when testing a Standby Database -- actually testing a Disaster Recovery  site --  is to simulate complete loss of the Primary Database (at the Production site)

Previously, I have demonstrated two methods of "Destructive" Disaster Recovery site testing :

1. Being able to Switchover to the Standby (at a Disaster Recovery site), verifying that transactions created at the Disaster Recovery site do actually flow back to the Production site and, finally, reverting both databases to their pre-test image)

2. Opening a Standby Database as a Snapshot Standby, verify that it is writable and, finally, reverting it to being a "normal" Standby


But what if you want to simulate complete loss of the Production site (Primary database) -- signifying that you may have even lost some transactions, but needing to open the Standby for Read-Write operations ?  OR you actually encounter such a situation where the Primary goes down when the Standby is lagging ? 

On my Primary I had these configured for MAXIMUM AVAILABILITY (with SYNC and AFFIRM for log_archive_dest_2) :

SQL>alter system set log_archive_dest_2='SERVICE=STDBYDB SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBYDB';

System altered.

SQL>
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 822083584 bytes
Database Buffers 369098752 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL>
SQL> alter database SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY ;

Database altered.

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 822083584 bytes
Database Buffers 369098752 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL>
SQL> select protection_mode, open_mode, database_role from v$database;

PROTECTION_MODE OPEN_MODE DATABASE_ROLE
-------------------- -------------------- ----------------
MAXIMUM AVAILABILITY READ WRITE PRIMARY

SQL>


This is information of the Primary Database (at the Production site)  when Primary and Standby are in sync :

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/archivelog/ORCLCDB
Oldest online log sequence 235
Next log sequence to archive 237
Current log sequence 237
SQL>


And this is at the Standby Database (at the Disaster Recovery site) :
 
rfs (PID:4415): Changing standby controlfile to MAXIMUM AVAILABILITY mode
rfs (PID:4415): Changing standby controlfile to RESYNCHRONIZATION level
rfs (PID:4415): Standby controlfile consistent with primary

Recovery of Online Redo Log: Thread 1 Group 5 Seq 237 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo02.log



My Standby database / site goes down for a short while.  
So the Primary reports :

LGWR (PID:4280): Failed to archive LNO:1 T-1.S-238, error=3113
LGWR (PID:4280): Error 1041 disconnecting from LAD:2 standby host 'STDBYDB'
2021-07-10T17:58:05.264661+08:00
Thread 1 advanced to log sequence 239 (LGWR switch)
Current log# 2 seq# 239 mem# 0: /opt/oracle/oradata/ORCLCDB/redo02.log
2021-07-10T17:58:05.390312+08:00
ARC1 (PID:4345): Archived Log entry 524 added for T-1.S-238 ID 0xa7521ccd LAD:1


and sometime later

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/archivelog/ORCLCDB
Oldest online log sequence 242
Next log sequence to archive 244
Current log sequence 244
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
12768459

SQL>


We now know that the Standby (last applying Online Redo Sequence#237 [it went on to 238 before the Instance/Server crashed]) is lagging behind the Primary (current Redo Log 244, SCN 12768459 .

Reminder : The configuration is in MAXIMUM AVAILABILITY mode (not MAXIMUM PROTECTION).

Now, my Primary goes down.

So the FAILOVER must be issued from the Standby when I bring it up.  Let my try that now

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> create restore point before_failover guarantee flashback database;

Restore point created.

SQL>
SQL> select protection_mode, open_mode, database_role from v$database;

PROTECTION_MODE OPEN_MODE DATABASE_ROLE
-------------------- -------------------- ----------------
MAXIMUM AVAILABILITY MOUNTED PHYSICAL STANDBY

SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string STDBYDB
SQL>
SQL> alter database failover to STDBYDB;

Database altered.

SQL>
SQL> select protection_mode, open_mode, database_role from v$database;

PROTECTION_MODE OPEN_MODE DATABASE_ROLE
-------------------- -------------------- ----------------
MAXIMUM PERFORMANCE MOUNTED PRIMARY

SQL> alter database open;

Database altered.

SQL>


Let's see what the alert log on the Standby shows

ARC1 (PID:6205): FAL: Error 12541 connecting to ORCLCDB for fetching gap sequence

2021-07-10T18:04:34.507622+08:00
RVWR started with pid=45, OS id=11261
2021-07-10T18:04:38.704492+08:00
Allocating 8388608 bytes in shared pool for flashback generation buffer.
Allocated 5430064 bytes in shared pool for flashback generation buffer
2021-07-10T18:04:39.802983+08:00
Created guaranteed restore point BEFORE_FAILOVER

2021-07-10T18:05:50.402988+08:00
alter database failover to STDBYDB
2021-07-10T18:05:50.403086+08:00
.... (PID:6240): The Time Management Interface (TMI) is being enabled for role transition
.... (PID:6240): information. This will result in messages beingoutput to the alert log
.... (PID:6240): file with the prefix 'TMI: '. This is being enabled to make the timing of
.... (PID:6240): the various stages of the role transition available for diagnostic purposes.
.... (PID:6240): This output will end when the role transition is complete.
TMI: dbsdrv failover to target BEGIN 2021-07-10 18:05:50.403742
Terminal Recovery requested in process 6240
TMI: adbdrv termRecovery BEGIN 2021-07-10 18:05:50.408509
2021-07-10T18:05:50.494984+08:00
Attempt to do a Terminal Recovery (STDBYDB)
TMI: adbdrv termRecovery END 2021-07-10 18:05:50.495053
2021-07-10T18:05:50.495642+08:00
Media Recovery Start: Managed Standby Recovery (STDBYDB)
Started logmerger process
2021-07-10T18:05:50.532463+08:00
PR00 (PID:11393): Managed Standby Recovery not using Real Time Apply
max_pdb is 5
2021-07-10T18:05:51.094491+08:00
Parallel Media Recovery started with 2 slaves
2021-07-10T18:05:51.233396+08:00
stopping change tracking
2021-07-10T18:05:51.583393+08:00
PR00 (PID:11393): Begin: SRL archival
PR00 (PID:11393): End: SRL archival
PR00 (PID:11393): Terminal Recovery timestamp is '07/10/2021 18:05:51'
PR00 (PID:11393): Terminal Recovery: applying standby redo logs.
PR00 (PID:11393): Terminal Recovery: thread 1 seq# 238 redo required
2021-07-10T18:05:51.682050+08:00
PR00 (PID:11393): Terminal Recovery:
2021-07-10T18:05:51.685886+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 238 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
Terminal Recovery finished with No-Data-Loss
2021-07-10T18:05:51.985957+08:00
Incomplete Recovery applied until change 12767732 time 07/10/2021 17:57:59
2021-07-10T18:05:52.074427+08:00
Media Recovery Complete (STDBYDB)
2021-07-10T18:05:52.655358+08:00
Terminal Recovery: successful completion
PR00 (PID:11393): Forcing ARSCN to IRSCN for TR SCN:0x0000000000c2d1f4
PR00 (PID:11393): Attempt to set limbo arscn SCN:0x0000000000c2d1f4 irscn SCN:0x0000000000c2d1f4
PR00 (PID:11393): Resetting standby activation ID 2807176397 (0xa7521ccd)
stopping change tracking
2021-07-10T18:05:52.873082+08:00
ALTER DATABASE SWITCHOVER TO PRIMARY (STDBYDB)
2021-07-10T18:05:52.873684+08:00
Maximum wait for role transition is 15 minutes.
TMI: kcv_commit_to_so_to_primary wait for MRP to finish BEGIN 2021-07-10 18:05:52.873693
TMI: kcv_commit_to_so_to_primary wait for MRP to finish END 2021-07-10 18:05:52.873771
TMI: kcv_commit_to_so_to_primary Switchover from physical BEGIN 2021-07-10 18:05:52.874148
Backup controlfile written to trace file /opt/oracle/diag/rdbms/stdbydb/STDBYDB/trace/STDBYDB_ora_6240.trc
2021-07-10T18:05:53.571015+08:00
Standby terminal recovery start SCN: 12767670
RESETLOGS after complete recovery through change 12767732
NET (PID:6240): ORL pre-clearing operation disabled by switchover
Online log /opt/oracle/oradata/STDBYDB/redo01.log: Thread 1 Group 1 was previously cleared
Online log /opt/oracle/oradata/STDBYDB/redo02.log: Thread 1 Group 2 was previously cleared
Online log /opt/oracle/oradata/STDBYDB/redo03.log: Thread 1 Group 3 was previously cleared
2021-07-10T18:05:53.907884+08:00
Standby became primary SCN: 12767669
2021-07-10T18:05:53.908157+08:00
Setting recovery target incarnation to 4
2021-07-10T18:05:53.966494+08:00
NET (PID:6240): RT: Role transition work is not done
NET (PID:6240): The Time Management Interface (TMI) is being enabled for role transition
NET (PID:6240): information. This will result in messages beingoutput to the alert log
NET (PID:6240): file with the prefix 'TMI: '. This is being enabled to make the timing of
NET (PID:6240): the various stages of the role transition available for diagnostic purposes.
NET (PID:6240): This output will end when the role transition is complete.
NET (PID:6240): Redo network throttle feature is disabled at mount time
2021-07-10T18:05:54.087343+08:00
NET (PID:6240): Database role cleared from PHYSICAL STANDBY [kcvs.c:1030]
Switchover: Complete - Database mounted as primary
TMI: kcv_commit_to_so_to_primary Switchover from physical END 2021-07-10 18:05:54.088069
TMI: dbsdrv failover to target END 2021-07-10 18:05:54.088135
Failover completed with No-Data-Loss.
Completed: alter database failover to STDBYDB
2021-07-10T18:06:33.754882+08:00
alter database open
2021-07-10T18:06:33.755022+08:00
TMI: adbdrv open database BEGIN 2021-07-10 18:06:33.754898
Ping without log force is disabled:
instance mounted in exclusive mode.
2021-07-10T18:06:33.945688+08:00
Crash Recovery excluding pdb 2 which was cleanly closed.
2021-07-10T18:06:33.945807+08:00
Crash Recovery excluding pdb 4 which was cleanly closed.
Endian type of dictionary set to little
2021-07-10T18:06:33.999216+08:00
Assigning activation ID 2848626420 (0xa9ca96f4)
2021-07-10T18:06:34.009104+08:00
LGWR (PID:6045): LAD:2 is UNSYNCHRONIZED
2021-07-10T18:06:34.503600+08:00
Thread 1 advanced to log sequence 2 (thread open)
2021-07-10T18:06:34.503635+08:00
ARC3 (PID:6209): Becoming the 'no SRL' ARCH
2021-07-10T18:06:34.503832+08:00
Redo log for group 2, sequence 2 is not located on DAX storage
Thread 1 opened at log sequence 2
Current log# 2 seq# 2 mem# 0: /opt/oracle/oradata/STDBYDB/redo02.log
Successful open of redo thread 1
2021-07-10T18:06:34.521347+08:00
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
stopping change tracking
2021-07-10T18:06:34.662361+08:00
TT03 (PID:11461): Sleep 5 seconds and then try to clear SRLs in 2 time(s)
2021-07-10T18:06:34.883461+08:00
ARC0 (PID:6199): Archived Log entry 259 added for T-1.S-1 ID 0xa9ca96f4 LAD:1
2021-07-10T18:06:35.784688+08:00
Undo initialization recovery: err:0 start: 1407469 end: 1407660 diff: 191 ms (0.2 seconds)
[6240] Successfully onlined Undo Tablespace 2.
Undo initialization online undo segments: err:0 start: 1407660 end: 1408339 diff: 679 ms (0.7 seconds)
Undo initialization finished serial:0 start:1407469 end:1408406 diff:937 ms (0.9 seconds)
Dictionary check beginning
2021-07-10T18:06:36.927538+08:00
Dictionary check complete
Verifying minimum file header compatibility for tablespace encryption for pdb 1..
Verifying file header compatibility for tablespace encryption completed for pdb 1
Database Characterset is AL32UTF8
No Resource Manager plan active
2021-07-10T18:06:39.177858+08:00


TT00 (PID:6201): Error 12541 received logging on to the standby
TT00 (PID:6201): Check whether the listener is up and running.
2021-07-10T18:06:39.185611+08:00
TT03 (PID:11461): Sleep 5 seconds and then try to clear SRLs in 3 time(s)
2021-07-10T18:06:39.733886+08:00
joxcsys_required_dirobj_exists: directory object exists with required path /opt/oracle/product/19c/dbhome_1/javavm/admin/, pid 6240 cid 1
replication_dependency_tracking turned off (no async multimaster replication found)
2021-07-10T18:06:42.855357+08:00
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Starting background process AQPC
2021-07-10T18:06:43.050601+08:00
AQPC started with pid=50, OS id=11483
PDB$SEED(2):Autotune of undo retention is turned on.
2021-07-10T18:06:44.060956+08:00
PDB$SEED(2):Endian type of dictionary set to little
2021-07-10T18:06:44.314613+08:00
TT03 (PID:11461): Enabling archival of deferred physical standby SRLs
2021-07-10T18:06:44.532641+08:00
TT03 (PID:11461): Archived Log entry 260 added for T-1.S-238 ID 0xa7521ccd LAD:1
2021-07-10T18:06:44.681960+08:00
PDB$SEED(2):Undo initialization finished serial:0 start:1416557 end:1416557 diff:0 ms (0.0 seconds)
PDB$SEED(2):Pluggable database PDB$SEED dictionary check beginning
2021-07-10T18:06:48.986311+08:00
PDB$SEED(2):Pluggable Database PDB$SEED Dictionary check complete
PDB$SEED(2):Database Characterset for PDB$SEED is AL32UTF8
2021-07-10T18:07:04.127503+08:00
PDB$SEED(2):Opening pdb with no Resource Manager plan active
2021-07-10T18:07:05.037256+08:00
QPI: opatch file present, opatch
QPI: qopiprep.bat file present
2021-07-10T18:07:06.501677+08:00
Starting background process CJQ0
2021-07-10T18:07:06.511870+08:00
CJQ0 started with pid=47, OS id=11538
Completed: alter database open


Note the messages 

 PR00 (PID:11393): Terminal Recovery:
2021-07-10T18:05:51.685886+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 238 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
Terminal Recovery finished with No-Data-Loss
2021-07-10T18:05:51.985957+08:00
Incomplete Recovery applied until change 12767732 time 07/10/2021 17:57:59
2021-07-10T18:05:52.074427+08:00
Media Recovery Complete (STDBYDB)
2021-07-10T18:05:52.655358+08:00
Terminal Recovery: successful completion

Standby terminal recovery start SCN: 12767670

Standby became primary SCN: 12767669

Failover completed with No-Data-Loss.
Completed: alter database failover to STDBYDB


The Standby completed Recovery to Sequence#238 and SCN 12767669 which are much lower then the last known ArchiveLog (Sequence#243) and SCN (something higher than 12768459).

I had expected to see an ORA-16472 error requiring the usage of the "ALTER DATABASE ACTIVATE STANDBY DATABASE" command.  Unfortunately, in this case, the Standby assumes that there are no transactions from the Primary and assumes that there is no Data Loss !

So, the next time you do a Failover, you have to be careful to identify whether there is actual Data Loss if you are unable to access the Primary database.

Now, at the Standby I can query

SQL> select scn_to_timestamp(12767669) from dual;

SCN_TO_TIMESTAMP(12767669)
---------------------------------------------------------------------------
10-JUL-21 05.48.25.000000000 PM

SQL>


which certainly tells me that the last transaction at the Standby is as of  05:48pm (approximately,  the SCN_TO_TIMESTAMP provides us an approximate time only), even though I startup the Standby at 06:04pm.  
Fortunately, when I can access the Primary and read the alert.log, I find that the Primary went down after 06:02pm, with the last ArchiveLog generated at 6:00pm


2021-07-10T18:00:12.509208+08:00
Thread 1 advanced to log sequence 244 (LGWR switch)
Current log# 1 seq# 244 mem# 0: /opt/oracle/oradata/ORCLCDB/redo01.log
2021-07-10T18:00:12.634091+08:00
ARC2 (PID:4347): Archived Log entry 529 added for T-1.S-243 ID 0xa7521ccd LAD:1


So I can confirm that there IS Data Loss !

FYI , ORA-16472 is :


oracle19c>oerr ora 16472
16472, 0000, "failover failed due to data loss"
// *Cause: The database was in MaxAvailability or MaxProtection mode but
// terminal recovery finished with data loss.
// *Action: For a data loss failover, issue ALTER DATABASE ACTIVATE STANDBY
// DATABASE command.
oracle19c>


This signifies that a FAILOVER command has not succeeded and an ALTER DATABASE ACTIVATE STANDBY DATABASE must be issued.
Categories: DBA Blogs

Index on boolean column

Tom Kyte - Mon, 2021-07-05 09:06
Hello, ASK TOM Team. I have a 14 columns table, millions of rows, highly used. I need to add another column (FLAG_STATE), which will have two different values (0/1); is like "boolean". <code>CREATE TABLE SCHEMA.TABLE1 ( ID NUMBER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP) NOT NULL, ... CUSTOMER VARCHAR2(15 BYTE) NOT NULL, DOC VARCHAR2(9 BYTE) NOT NULL, ... FLAG_STATE NUMBER(1,0) )</code> This table often is queried by customer and doc columns. A customer can have several docs (sometimes the same doc 3 or 4 times). I have an index on (customer,doc). Right now, I need to add FLAG_STATE column to the table. What kind of index should I use if I want to filter by FLAG_STATE too? Is OK with the existing index? Should I add the new column to the existing index? Should I use null values as a state and just flag rows with "1" when needed? What else am I missing? Thanks in advanced. Regards,
Categories: DBA Blogs

MongoDB test server on Oracle Linux

Bobby Durrett's DBA Blog - Fri, 2021-07-02 11:41

I use Oracle Enterprise Linux on VirtualBox running on my Windows 10 laptop for test servers (virtual machines) of various types of software. I just setup a MongoDB VM yesterday and thought I would document some of the things I did which are not in the standard documentation.

I followed this URL for the install:

https://docs.mongodb.com/manual/tutorial/install-mongodb-on-red-hat/

I created the yum repository file mongodb-org-4.4.repo as documented:

[root@mongodb yum.repos.d]# cat mongodb-org-4.4.repo
[mongodb-org-4.4]
name=MongoDB Repository
baseurl=https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/4.4/x86_64/
gpgcheck=1
enabled=1
gpgkey=https://www.mongodb.org/static/pgp/server-4.4.asc

I installed the MongoDB yum package:

[root@mongodb yum.repos.d]# sudo yum install -y mongodb-org
Loaded plugins: langpacks, ulninfo
mongodb-org-4.4                                                                                                           | 2.5 kB  00:00:00
ol7_UEKR4                                                                                                                 | 2.5 kB  00:00:00
ol7_latest                                                                                                                | 2.7 kB  00:00:00
mongodb-org-4.4/7Server/primary_db                                                                                        |  47 kB  00:00:02
Resolving Dependencies
--> Running transaction check
---> Package mongodb-org.x86_64 0:4.4.6-1.el7 will be installed
--> Processing Dependency: mongodb-org-shell = 4.4.6 for package: mongodb-org-4.4.6-1.el7.x86_64
...

I didn’t need sudo since I was root, but it worked. I don’t know if this was really needed but I set ulimit with these commands:

ulimit -f unlimited
ulimit -t unlimited
ulimit -v unlimited
ulimit -l unlimited
ulimit -n 64000
ulimit -m unlimited
ulimit -u 64000

I am not sure if these commands stick when you run them as root. They seem to but for now I’ve been running them manually after I reboot. These were documented here:

https://docs.mongodb.com/manual/reference/ulimit/

Based on this document I also created the file /etc/security/limits.d/99-mongodb-nproc.conf:

[root@mongodb ~]# cat /etc/security/limits.d/99-mongodb-nproc.conf
*          soft    nproc     64000
*          hard    nproc     64000
root       soft    nproc     unlimited
[root@mongodb ~]#

I don’t know for sure if this was needed, but it did not cause any problems.

I edited /etc/selinux/config to prevent SELinux from interfering:

[root@mongodb selinux]# diff config.07012021 config
7c7
< SELINUX=enforcing
---
> SELINUX=disabled

I also disabled the firewall just in case it would cause problems:

[root@mongodb ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
Removed symlink /etc/systemd/system/basic.target.wants/firewalld.service.
[root@mongodb ~]# systemctl stop firewalld
[root@mongodb ~]# systemctl status firewalld
? firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:firewalld(1)
...

Lastly, in order to be able to access MongoDB from outside the VM I had to edit /etc/mongod.conf to allow access from all IP addresses:

[root@mongodb etc]# diff  mongod.conf mongod.conf.07012021
29c29
<   bindIp: 0.0.0.0 # Enter 0.0.0.0,:: to bind to all IPv4 and IPv6 addresses or, alternatively, use the net.bindIpAll setting.
---
>   bindIp: 127.0.0.1  # Enter 0.0.0.0,:: to bind to all IPv4 and IPv6 addresses or, alternatively, use the net.bindIpAll setting.

Of course, in a production system you would want to make this more secure, but this is just a quick and dirty test VM.

Finally, this command brings up the database:

systemctl start mongod

I ran this in a new root Putty window to get the ulimit settings. Not sure if that was necessary, but it did work.

I have a NAT network and port forwarding setup so that while MongoDB listens by default on port 27017 host localhost I setup VirtualBox to connect it to port 61029 host 127.0.0.1 on my laptop.

Since the programming language that I am most familiar with is Python (I have not learned any JavaScript) I setup a test connection to my new MongoDB database using the pymongo module.

I installed it like this:

pip install pymongo[srv]

Simple test program looks like this:

from pymongo import MongoClient
from pymongo.errors import ConnectionFailure

client = MongoClient('127.0.0.1', 61029)
try:
    # The ismaster command is cheap and does not require auth.
    client.admin.command('ismaster')
except ConnectionFailure:
    print("Server not available")

I got that from stackoverflow. I was also following the pymongo tutorial:

https://pymongo.readthedocs.io/en/stable/tutorial.html

One test program:

from pymongo import MongoClient

client = MongoClient('127.0.0.1', 61029)

db = client['test-database']

collection = db['test-collection']

import datetime
post = {"author": "Mike",
          "text": "My first blog post!",
          "tags": ["mongodb", "python", "pymongo"],
          "date": datetime.datetime.utcnow()}
          
posts = db.posts
post_id = posts.insert_one(post).inserted_id
print(type(post_id))
print(post_id)

print(db.list_collection_names())

import pprint
pprint.pprint(posts.find_one())

pprint.pprint(posts.find_one({"author": "Mike"}))

pprint.pprint(posts.find_one({"author": "Eliot"}))

pprint.pprint(posts.find_one({"_id": post_id}))

post_id_as_str = str(post_id)
pprint.pprint(posts.find_one({"_id": post_id_as_str}))

from bson.objectid import ObjectId

pprint.pprint(posts.find_one({"_id": ObjectId(post_id_as_str)}))

Its output:

<class 'bson.objectid.ObjectId'>
60df400f53f43d1c2703265c
['test-collection', 'posts']
{'_id': ObjectId('60de4f187d04f268e5b54786'),
 'author': 'Mike',
 'date': datetime.datetime(2021, 7, 1, 23, 26, 16, 538000),
 'tags': ['mongodb', 'python', 'pymongo'],
 'text': 'My first blog post!'}
{'_id': ObjectId('60de4f187d04f268e5b54786'),
 'author': 'Mike',
 'date': datetime.datetime(2021, 7, 1, 23, 26, 16, 538000),
 'tags': ['mongodb', 'python', 'pymongo'],
 'text': 'My first blog post!'}
None
{'_id': ObjectId('60df400f53f43d1c2703265c'),
 'author': 'Mike',
 'date': datetime.datetime(2021, 7, 2, 16, 34, 23, 410000),
 'tags': ['mongodb', 'python', 'pymongo'],
 'text': 'My first blog post!'}
None
{'_id': ObjectId('60df400f53f43d1c2703265c'),
 'author': 'Mike',
 'date': datetime.datetime(2021, 7, 2, 16, 34, 23, 410000),
 'tags': ['mongodb', 'python', 'pymongo'],
 'text': 'My first blog post!'}

I ran this on Python 3.9.6 so the strings like ‘Mike’ are not u’Mike’. It looks like the output on the tutorial is from some version of Python 2, so you get Unicode strings like u’Mike’ but on Python 3 strings are Unicode by default so you get ‘Mike’.

Anyway, I didn’t get any further than getting MongoDB installed and starting to run through the tutorial, but it is up and running. Might be helpful to someone else (or myself) if they are running through this to setup a test VM.

Bobby

Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs