Pages

Monday, February 21, 2022

Oracle RAC 19c pdb open failed with ORA-12963: A read-only instance cannot be the first instance to open a database

When opening pluggable database (pdb) on one node of two-node 19c RAC, got following errors
sys@rac01> alter pluggable database pdb1 open;
alter pluggable database pdb1 open
*
ERROR at line 1:
ORA-12963: A read-only instance cannot be the first instance to open a database.
Open the pdb on the other node. It is, by default, in read/write mode
sys@rac02> show pdbs

    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------ ---------- ----------
         2 PDB$SEED           READ ONLY  NO
         3 pdb1               MOUNTED
sys@rac02> alter pluggable database pdb1 open;

Pluggable database altered.

sys@rac02> show pdbs

    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------ ---------- ----------
         2 PDB$SEED           READ ONLY  NO
         3 pdb1               READ WRITE NO
And, try to open pdb on local node again, it will be opened successfully, but will be READ ONLY mode
sys@rac01> alter pluggable database pdb1 open;

Pluggable database altered.

sys@rac01> show pdbs

    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------ ---------- ----------
         2 PDB$SEED           READ ONLY  NO
         3 pdb1               READ ONLY  NO
Check the instances' status with command 'srvctl status database'
[oracle@rac01]$ srvctl status database -db orcl -verbose
Instance orcl_1 is running on node rac02. Instance status: Open.
Instance orcl_2 is running on node rac01. Instance status: Open,Readonly.
The instance is started in READ ONLY mode on node rac01, it usually happens when the node is assigned to a parallel query server pool (PQPOOL). A node assigned to PQPOOL is treated as a leaf node and database instance running on leaf node will be READ ONLY. 

Check the server pool configuration of the database
[oracle@rac01]$ srvctl config database -db orcl
Database unique name: orcl
Database name: orcl
Oracle home: /u01/app/oracle/product/19.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/ORCL/PARAMETERFILE/spfile.299.1096020569
Password file: +DATA/ORCL/PASSWORD/pwdorcl.289.1096019487
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: dbpool,pool2
PQ Helper Server Pools: pool2
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances:
Configured nodes:
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is policy managed
The database is configured with two pools: dbpool and pool2, and pool2 is a PQPOOL (PQ Helper Server Pools, also called parallel query server pool).

Check server pools' assignment with command 'srvctl status srvpool'
[oracle@rac01]$ srvctl status srvpool
Server pool name: Free
Active servers count: 0
Server pool name: Generic
Active servers count: 0
Server pool name: dbpool
Active servers count: 1
Server pool name: pool2
Active servers count: 1
The cluster is two-node RAC, both server pool dbpool and PQPOOL pool2 have one server assigned. It means orcl has one instance, which server node is in dbpool, will be normal (read/write) mode, and another instance, which server node is in pool2, will be read only mode.

Since it is a two-node RAC, and both nodes are hub nodes, PQPOOL is not necessary. The PQPOOL can be removed to fix the issue as following

1. Remove PQPOOL configuration from database with command 'srvctl modify database'
[oracle@rac01]$ srvctl stop database -db orcl

[oracle@rac01]$ srvctl modify database -db orcl -pqpool

[oracle@rac01]$ srvctl config database -db orcl
Database unique name: orcl
Database name: orcl
Oracle home: /u01/app/oracle/product/19.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/ORCL/PARAMETERFILE/spfile.299.1096020569
Password file: +DATA/ORCL/PASSWORD/pwdorcl.289.1096019487
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: dbpool
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances:
Configured nodes:
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is policy managed
2. Remove PQPOOL with command 'srvcl remove srvpool'
[oracle@rac01]$ srvctl remove srvpool -serverpool pool2
[oracle@rac01]$
[oracle@rac01]$ srvctl status srvpool
Server pool name: Free
Active servers count: 0
Server pool name: Generic
Active servers count: 0
Server pool name: dbpool
Active servers count: 2
The server pool dbpool gets two servers assigned, and database orcl will get two instances running in normal mode.

No comments: