Month: July 2019

ORA-16751: failed to switchover to physical standby database and Switchover Ends with Two Physical Standby Databases

Trying to switch over database from primary database to physical standby database,  it ran into the issue where both database became physical standby.

DGMGRL> switchover to "STDBYDB"
Performing switchover NOW, please wait...
Operation requires a connection to instance "STDBYDB" on database "STDBYDB"

Connecting to instance "STDBYDB"...
Connected.

Error: ORA-16751: failed to switchover to physical standby database

Failed.

Unable to switchover, primary database is still "PRIMDB"

DGMGRL> 

--
-- Check database role
--

NAME       OPEN_MODE            DATABASE_ROLE    
--------- -------------------- ---------------- 
PRIMDB    CLOSED BY SWITCHOVER PHYSICAL STANDBY  
STDBYDB   MOUNTED              PHYSICAL STANDBY

Data Guard Broker log:

07/19/2019 08:07:55
SQL Execution error=604, 
sql=[ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN]. 
See error stack below.
ORA-00604: error occurred at recursive SQL level 1
ORA-00344: unable to re-create online log '/u05/oralog/PRIMDB/redo1a.log'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information:
switchover to primary command failed
Database Resource SetState Error (16751)
Command SWITCHOVER TO STDBYDB completed with error ORA-16751

DataGuard cannot create online redo logs on standby database side. The reason is the following two parameters are not set up correctly.

The parameters on standby database side should be like those:

DB_FILE_NAME_CONVERT  = 'PRIMDB','STDBYDB'
LOG_FILE_NAME_CONVERT = 'PRIMDB','STDBYDB'

Solution

Logon to standby database server.

SQL>select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

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

SQL> startup mount
ORACLE instance started.
...
..
.
Database mounted.


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database finish;

Database altered.

SQL> ALTER database commit to switchover to primary with session shutdown;

Database altered.

SQL> alter database open;

Database altered.

SQL>  select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

Finally rebuild the Data Guard Broker Configuration.

“No space left on device” while AWS RDS Storage Auto Scaling Enabled

PostgreSQL RDS instance got “No space left on device” errors, even RDS storage auto scaling enabled. The storage can only be extended after at least 6 hours.

As for now,  although automatic scaling helps you to increase storage on your RDS instance dynamically, you should still configure the initial storage for your DB instance to an appropriate size for your typical workload.

Auto Scaling will kick in when these factors apply:

  • Free available space is less than 10 percent of the allocated storage.
  • The low-storage condition lasts at least five minutes.

We can also see, that the minimum the storage will increase is either 5GB or max 10% of your current allocated storage. That means that if you have an instance of 200Gb storage right now, your max ‘auto allocated’ storage space would be +20Gb of 200Gb which would be 220Gb. This will not be able to change for at least 6 hours.

Remember 6 hours policy when using “auto scaling”.

ORA-00932: inconsistent datatypes from Heterogeneous Service Database Link

There is an Oracle database link between Oracle and PostgreSQL database through Heterogeneous Service ( HS ).

The sql query is working fine in PostgreSQL database:

testdb=> \d test_tbl
Table "testuser.test_tbl"
Column  | Type                    | Collation | Nullable | Default
--------+-------------------------+-----------+----------+---------
id      | double precision        |           |          |
s10     | character varying(10)   |           |          |
s20     | character varying(20)   |           |          |
s30     | character varying(2048) |           |          |


testdb=> select * from test_tbl;
id  | s10    | s20    | s30
----+--------+--------+---------------
1   | tets1 | test2 | Test Record 1

(1 row)

testdb=>select "id", substr("s10",1,5),
                substr("s20",1,5),substr("s30",1,10) 
         from test_tbl;

id | substr | substr | substr
---+--------+--------+------------
1  | test1  | test2  | Test Recor

(1 row)

While running the same query in Oracle database through Heterogeneous Service ( HS ) database link, get “ORA-00932: inconsistent datatypes” error. Sometimes you might get “ORA-02070: database does not support TO_CHAR in this context”.

SQL> select "id", substr("s10",1,5),substr("s20",1,5),substr("s30",1,10) 
      from "test_tbl"@PGLINK;
select "id", substr("s10",1,5),substr("s20",1,5), substr("s30",1,10) 
from "test_tbl"@PGLINK
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got LONG

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

Error 1033 received logging on to the standby

PRIMARY:

alert.log:

Error 1033 received logging on to the standby
Wed Jul 10 16:15:08 2019
Error 1033 received logging on to the standby
Wed Jul 10 16:16:11 2019
Error 1033 received logging on to the standby
Wed Jul 10 16:17:13 2019
Error 1033 received logging on to the standby

dr.log:

07/10/2019 16:19:57
Failed to connect to remote database TESTSTY. Error is ORA-01033
Failed to send message to site TESTSTY. Error code is ORA-01033.
Data Guard Broker Status Summary:
Type Name Severity Status
Configuration TESTDB Warning ORA-16607
Primary Database TESTDB Error ORA-16778
Physical Standby Database TESTSTY Error ORA-01033

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe