Month: October 2021

How to Open Seed Database to Read Write Mode

In CDB database environment, the seed database is always in “READ ONLY” mode. The mode changes only by applying patches using “datapatch”.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TESTPDB                        READ WRITE NO

Under the guidance of Oracle Support for specific purpose, the following instructions can be followed to open seed database “PDB$SEED” in “READ WRITE” mode.

Set hidden parameter “_oracle_script” at session level

SQL> alter session set "_oracle_script"=TRUE;

Session altered.

Close and then open seed database

SQL> alter pluggable database pdb$seed close immediate instances=all;

Pluggable database altered.

SQL>  alter pluggable database pdb$seed open read write instances=all;

Pluggable database altered.

Run the script Oracle support provided

SQL> alter session set container=PDB$SEED;

Session altered.

SQL>  @?/rdbms/admin/catclust.sql
...
..
.

Close and then open seed database in READ ONLY mode

SQL> connect / as sysdba
SQL> alter session set "_oracle_script"=TRUE;

Session altered.

SQL> alter pluggable database pdb$seed close immediate instances=all;

Pluggable database altered.


SQL> connect / as sysdba
SQL> alter session set "_oracle_script"=TRUE;

SQL>  alter pluggable database pdb$seed open read only instances=all;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TESTPDB                        READ WRITE NO

Per Development, setting _ORACLE_SCRIPT to TRUE is something to be used only by Oracle internally.  There may be a case where a note says to use it explicitly, but you should not use it outside the context of that note.  In general is not to be set explicitly by users.

Role Transitions with Oracle Data Guard by using SQL

As a DBA, there are many situations required to switchover database role in Data Guard environment. From 12c onwards , the easy way is to execute one line command to switchover database:

SQL> alter database switchover to STESTDB;

OR

DGMGRL> switchover to STESTDB;

This post demonstrates how to implement role transitions step by step by using SQL.

Primary DB/Instances: TESTDB/TESTDB1,TESTDB2
Standby DB/Instances: STESTDB/STESTDB1, STESTDB2

On Primary Database

From node1:

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TESTDB      READ WRITE           PRIMARY
SQL> select switchover_status from v$database ;

SWITCHOVER_STATUS
--------------------
TO STANDBY

The switchover_status column of v$database can have the following values:

Not Allowed:-Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases
Session Active:- Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted
Switchover Pending:- This is a standby database and the primary database switchover request has been received but not processed.
Switchover Latent:- The switchover was in pending mode, but did not complete and went back to the primary database
To Primary:- This is a standby database, with no active sessions, that is allowed to switch over to a primary database
To Standby:- This is a primary database, with no active sessions, that is allowed to switch over to a standby database
Recovery Needed:- This is a standby database that has not received the switchover request

SQL> select status, gap_status from v$archive_dest_status where dest_id = 2;

STATUS    GAP_STATUS
--------- ------------------------
VALID     NO GAP

Verify switchover is OK or not.
Still on node1:

SQL>  alter database switchover to STESTDB verify;
 alter database switchover to STESTDB verify
*
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details

Go to alert log, and find the following messages:

SWITCHOVER VERIFY WARNING: switchover target has no standby database defined in LOG_ARCHIVE_DEST_n parameter. If the switchover target is converted to a primary database, the new primary database will not be protected.
ORA-16475 signalled during:  alter database switchover to STESTDB verify...

Standby database ‘ LOG_ARCHIVE_DEST_2’ has not been defined, so we define them properly.

SQL>  show parameter LOG_ARCHIVE_DEST_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string


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

System altered.

Now we go to primary node 1 and run verify again. Check alert log, and fix the problem until getting the following result.

SQL> alter database switchover to STESTDB verify;


Database altered.

Finally primary role transition to standby, and check alert log for details.

Still on primary node 1:

SQL> alter database commit to switchover to standby with session shutdown;

Database altered.

After the previous step, the new standby database ( old primary database ) is down. so we have to manually startup and start recovery.

$ srvctl status database -d testdb
Instance TESTDB1 is not running on node node1
Instance TESTDB2 is not running on node node2
-- on old primary node 1 still 
SQL> startup nomount;

SQL> alter database mount standby database;

Database altered.

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST      MOUNTED              PHYSICAL STANDBY

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

Database altered. 

-- start up second new standby instance through SQL or srvctl ( you need change database role and startup option first). 

$ srvctl start instance -d TESTDB -i TESTDB2 -o mount

$ srvctl status database -d testdb
Instance TESTDB1 is running on node node 1
Instance TESTDB2 is running on node node 2

Now we move onto standby database.

On Standby Database

On old standby node 1:

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TESTDB    MOUNTED              PHYSICAL STANDBY


SQL> select switchover_status from v$database ;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED

This is the initial switchover status before primary switchover to standby yet.

After primary switchover to standby already, then continue the following steps.

SQL>  select switchover_status from v$database ;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL>  alter database commit to switchover to PRIMARY with session shutdown;

Database altered.


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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
SBYTEST  MOUNTED              PRIMARY


SQL> alter database open;

Database altered.

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TESTDB    READ WRITE           PRIMARY

Check and make sure second instance OPEN READ WRITE.

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TESTDB    MOUNTED              PRIMARY

SQL> alter database open;

Database altered.

SQL> select INST_ID,NAME,OPEN_MODE,RESTRICTED 
     from gv$pdbs order by 1,2;

INST_ID  NAME         OPEN_MODE  RES
-------- ------------ ---------- ---
 1       RACTESTPDB   READ WRITE NO
 1       PDB$SEED     READ ONLY  NO
 2       RACTESTPDB   READ WRITE NO
 2       PDB$SEED     READ ONLY  NO

SQL> alter pluggable database RACTESTPDB save state instances=all;

Pluggable database altered.

SQL>  select CON_ID,CON_NAME,INSTANCE_NAME,state, restricted from  DBA_PDB_SAVED_STATES;

-- make sure PDB' restricted=NO

Enable Change Blocking

SQL>SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;

SQL> show parameter DB_CREATE_FILE_DEST;

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Check instances both are running :

$ srvctl status database -d STESTDB
Instance TESTDB1 is running on node snode1
Instance TESTDB2 is running on node snode2

Reconfigure Data Guard Broker because databases role changed.

On new Primary node 1 ( snode 1):

DGMGRL> show configuration;

Configuration - dg_testdb

  Protection Mode: MaxPerformance
  Members:
  testdb  - Primary database
    Error: ORA-16810: multiple errors or warnings detected for the member

    stestdb - Physical standby database
      Error: ORA-16816: incorrect database role

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 27 seconds ago)
DGMGRL > disable configuration;
GMGRL > enable configuration;
DGMGRL> show configuration;

Configuration - dg_testdb

  Protection Mode: MaxPerformance
  Members:
  stestdb - Primary database
    testdb  - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 12 seconds ago)

Finally if it is RAC environment, modify database configurations like “Start options” and “Database role”, etc.

$srvctl config database -d TESTDB

$ srvctl modify  database -d TESTDB -role PHYSICAL_STANDBY -startoption "READ ONLY"

$ srvctl stop database -d TESTDB

$srvctl start database -d TESTDB
$srvctl config database -d STESTDB

$srvctl modify  database -d STESTDB -role PRIMARY -startoption OPEN 

$ srvctl stop database -d STESTDB

$srvctl start database -d STESTDB
DGMGRL> show configuration;

Configuration - dg_testdb

  Protection Mode: MaxPerformance
  Members:
  stestdb - Primary database
    testdb  - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 42 seconds ago)

On primary, log on to RMAN and change the archive log deletion policy by running:

RMAN>CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

On standby, log on to RMAN and change the archive log deletion policy by running:

RMAN>CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

Oracle Real Application Clusters Component Showing “OPTION OFF”  

After Oracle database migrated from Non-RAC to RAC environment, the oracle component “Oracle Real Application Clusters” shows “OPTION OFF”.

SQL>  select COMP_NAME, VERSION,STATUS,SCHEMA from dba_registry;

COMP_NAME                                VERSION                        STATUS                                       SCHEMA
---------------------------------------- ------------------------------ -------------------------------------------- --------------------
...
..
.
Oracle Database Catalog Views            12.1.0.2.0                     VALID                                        SYS
Oracle Database Packages and Types       12.1.0.2.0                     VALID                                        SYS
JServer JAVA Virtual Machine             12.1.0.2.0                     VALID                                        SYS
Oracle XDK                               12.1.0.2.0                     VALID                                        SYS
Oracle Database Java Packages            12.1.0.2.0                     VALID                                        SYS
OLAP Analytic Workspace                  12.1.0.2.0                     VALID                                        SYS
Oracle OLAP API                          12.1.0.2.0                     VALID                                        SYS
Oracle Real Application Clusters         12.1.0.2.0                     OPTION OFF                                        SYS

15 rows selected.

SOLUTION

Verify Real Application Clusters enabled at binary level

$ ar -t $ORACLE_HOME/rdbms/lib/libknlopt.a|grep kcsm.o
kcsm.o

Returning “kcsm.o” indicates RAC binary has been enabled.

run script to recreate RAC associated views and validates rac registry 

SQL> @?/rdbms/admin/catclust.sql

Verify the component status by executing the query on dba_registry

SQL>  select COMP_NAME, VERSION,STATUS,SCHEMA from dba_registry;

COMP_NAME                                VERSION                        STATUS                                       SCHEMA
---------------------------------------- ------------------------------ -------------------------------------------- --------------------
...
..
.
Oracle Database Catalog Views            12.1.0.2.0                     VALID                                        SYS
Oracle Database Packages and Types       12.1.0.2.0                     VALID                                        SYS
JServer JAVA Virtual Machine             12.1.0.2.0                     VALID                                        SYS
Oracle XDK                               12.1.0.2.0                     VALID                                        SYS
Oracle Database Java Packages            12.1.0.2.0                     VALID                                        SYS
OLAP Analytic Workspace                  12.1.0.2.0                     VALID                                        SYS
Oracle OLAP API                          12.1.0.2.0                     VALID                                        SYS
Oracle Real Application Clusters         12.1.0.2.0                     VALID                                        SYS

15 rows selected.

How To Relocate Data Guard Broker Configuration File Onto ASM

Just after the database migrated onto ASM, it is noticed that DG broker configuration files are still sitting on the default locations as following:

SQL>  show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u02/app/oracle/product/12.1.0
                                                 /dbhome_1/dbs/dr1TESTDB.dat
dg_broker_config_file2               string      /u02/app/oracle/product/12.1.0
                                                 /dbhome_1/dbs/dr2TESTDB.dat

Stop the broker 

SQL> alter system set dg_broker_start = FALSE;

Set the dg_broker_config_file1 & 2 parameters to new location

SQL> alter system set dg_broker_config_file1 = '+DATAC1/TESTDB/dr1TESTDB.dat';
SQL> alter system set dg_broker_config_file2 = '+DATAC1/TESTDB/dr2TESTDB.dat'

Restart the broker 

SQL> alter system set dg_broker_start = TRUE;

Recreate the Data Guard Configuration

DGMGRL>  CREATE CONFIGURATION 'TESTDB' AS PRIMARY DATABASE IS 'TESTDB' CONNECT IDENTIFIER IS 'TESTDB' ;
Configuration "TESTDB" created with primary database "TESTDB"

DGMGRL>ADD DATABASE 'TESTBYDB' AS CONNECT IDENTIFIER IS 'TESTBYDB' MAINTAINED AS PHYSICAL;
Database "TESTBYDB" added

DGMGRL> enable configuration;
Enabled.

Verify the results

ASMCMD> pwd
+DATAC1/TESTDB
ASMCMD>  ls -lt
Type             Redund  Striped  Time             Sys  Name
                                                   Y    TEMPFILE/
                                                   Y    PASSWORD/
                                                   Y    PARAMETERFILE/
                                                   Y    ONLINELOG/
                                                   Y    DATAGUARDCONFIG/
                                                   Y    DATAFILE/
                                                   Y    CONTROLFILE/
                                                   Y    2347F41E951336CFE053200F030A8127/
                                                   Y    2346D55DBF2B64B2E053200F030A299D/
DATAGUARDCONFIG  HIGH    COARSE   OCT 04 17:00:00  N    dr2testdb.dat => +DATAC1/TESTDB/DATAGUARDCONFIG/TESTDB.290.1085073589
DATAGUARDCONFIG  HIGH    COARSE   OCT 04 17:00:00  N    dr1testdb.dat => +DATAC1/TESTDB/DATAGUARDCONFIG/TESTDB.561.1085073589