Wednesday 9 October 2024

step by step Oracle 12c Data Guard Switchover

 

 Step:1 In Primary database check the database role and open_mode

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

NAME      OPEN_MODE           DATABASE_ROLE
--------- -------------------- ----------------
BACHDB      READ WRITE           PRIMARY

SQL>

 Step:2 In standby database check the database role and open_mode

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

NAME      OPEN_MODE           DATABASE_ROLE
--------- -------------------- ----------------
BACHDB      READ ONLY WITH APPLY PHYSICAL STANDBY

 

Step:2 Check archive log gap sequence

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#; 

 SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

Step 3: Check switchover status in primary database

 SQL>  select switchover_status from v$database;

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

SQL> 

 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

 

Step 4: On Primary database:-

SQL> alter database commit to switchover to standby;

 

Step 5 On old Primary database will become now standby
 

shutdown immediate

startup nomount

alter database mount standby database 

Step 6:-select name,open_mode,database_role from v$database;

 


Remove Instance from InnoDb cluster in MYSQL

 [root@nikash-db2 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
15.68.13.86        nikash-db1
15.68.13.87        nikash-db2
15.68.13.88        nikash-db3
15.68.13.89     nikash-db4
[root@nikash-db2 ~]#

mysql> select member_host,member_role from performance_schema.replication_group_members;
+-------------+-------------+
| member_host | member_role |
+-------------+-------------+
| nikash-db2  | SECONDARY   |
| nikash-db4  | PRIMARY     |
| nikash-db3  | SECONDARY   |
+-------------+-------------+
3 rows in set (0.00 sec)

mysql>

[root@nikash-db2 ~]# mysql --user root --password=Bankasia#2020 --host 10.88.13.87 --port 4407 nikash_np_eft
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 112997
Server version: 8.0.31-cluster MySQL Cluster Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| nikash-db2 |
+------------+
1 row in set (0.01 sec)



[root@nikash-db2 ~]# mysqlsh
MySQL Shell 8.0.31

Copyright (c) 2016, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS > root@nikash-db2
SyntaxError: Invalid or unexpected token
 MySQL  JS > \c root@nikash-db2
Creating a session to 'root@nikash-db2'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 111803 (X protocol)
Server version: 8.0.31-cluster MySQL Cluster Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  nikash-db2:33060+ ssl  JS > c.status()
ReferenceError: c is not defined
 MySQL  nikash-db2:33060+ ssl  JS > var c=dba.getCluster()
 MySQL  nikash-db2:33060+ ssl  JS > c.status()
{
    "clusterName": "nikash_db_cluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "nikash-db4:4407",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "nikash-db2:4407": {
                "address": "nikash-db2:4407",
                "instanceErrors": [
                    "WARNING: Instance is NOT a PRIMARY but super_read_only option is OFF."
                ],
                "memberRole": "SECONDARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.31"
            },
            "nikash-db3:4407": {
                "address": "nikash-db3:4407",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.31"
            },
            "nikash-db4:4407": {
                "address": "nikash-db4:4407",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.31"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "nikash-db4:4407"
}
 MySQL  nikash-db2:33060+ ssl  JS >
 
 
  MySQL  nikash-db2:33060+ ssl  JS > c.describe()
{
    "clusterName": "nikash_db_cluster",
    "defaultReplicaSet": {
        "name": "default",
        "topology": [
            {
                "address": "nikash-db2:4407",
                "label": "nikash-db2:4407",
                "role": "HA"
            },
            {
                "address": "nikash-db3:4407",
                "label": "nikash-db3:4407",
                "role": "HA"
            },
            {
                "address": "nikash-db4:4407",
                "label": "nikash-db4:4407",
                "role": "HA"
            }
        ],
        "topologyMode": "Single-Primary"
    }
}
 MySQL  nikash-db2:33060+ ssl  JS >
 
 
 
  MySQL  nikash-db2:33060+ ssl  JS > c.listRouters();
{
    "clusterName": "nikash_db_cluster",
    "routers": {
        "nikash-db2::system": {
            "hostname": "nikash-db2",
            "lastCheckIn": "2024-10-09 14:25:48",
            "roPort": "6447",
            "roXPort": "6449",
            "rwPort": "6446",
            "rwXPort": "6448",
            "version": "8.0.31"
        }
    }
}
 MySQL  nikash-db2:33060+ ssl  JS >
 
----------------------Remove INSTANCE from InnoDB cluster------------------

 
MySQL  nikash-db2:33060+ ssl  JS > c.removeInstance('nikash-db4:4407')
The instance will be removed from the InnoDB cluster. Depending on the instance
being the Seed or not, the Metadata session might become invalid. If so, please
start a new session to the Metadata Storage R/W instance.

* Waiting for instance 'nikash-db4:4407' to synchronize with the primary...
** Transactions replicated  ############################################################  100%

* Instance 'nikash-db4:4407' is attempting to leave the cluster...

The instance 'nikash-db4:4407' was successfully removed from the cluster.

 MySQL  nikash-db2:33060+ ssl  JS >
 
 
 --------------------Now check Cluster Group Member-------------
 
 mysql> select member_host,member_role from performance_schema.replication_group_members;
+-------------+-------------+
| member_host | member_role |
+-------------+-------------+
| nikash-db2  | PRIMARY     |
| nikash-db3  | SECONDARY   |
+-------------+-------------+
2 rows in set (0.00 sec)

mysql>



MySQL  nikash-db2:33060+ ssl  JS > c.removeInstance('nikash-db3:4407')
The instance will be removed from the InnoDB cluster. Depending on the instance
being the Seed or not, the Metadata session might become invalid. If so, please
start a new session to the Metadata Storage R/W instance.

* Waiting for instance 'nikash-db3:4407' to synchronize with the primary...
** Transactions replicated  ############################################################  100%

* Instance 'nikash-db3:4407' is attempting to leave the cluster...

The instance 'nikash-db3:4407' was successfully removed from the cluster.

 MySQL  nikash-db2:33060+ ssl  JS >


mysql> select member_host,member_role from performance_schema.replication_group_members;
+-------------+-------------+
| member_host | member_role |
+-------------+-------------+
| nikash-db2  | PRIMARY     |
+-------------+-------------+
1 row in set (0.00 sec)

mysql>



 MySQL  nikash-db2:33060+ ssl  JS > c.status()
{
    "clusterName": "nikash_db_cluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "nikash-db2:4407",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "nikash-db2:4407": {
                "address": "nikash-db2:4407",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.31"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "nikash-db2:4407"
}
 MySQL  nikash-db2:33060+ ssl  JS >
 
 
 MySQL  nikash-db2:33060+ ssl  JS > c.describe()
{
    "clusterName": "nikash_db_cluster",
    "defaultReplicaSet": {
        "name": "default",
        "topology": [
            {
                "address": "nikash-db2:4407",
                "label": "nikash-db2:4407",
                "role": "HA"
            }
        ],
        "topologyMode": "Single-Primary"
    }
}
 MySQL  nikash-db2:33060+ ssl  JS >
 
 
 
 

Tuesday 8 October 2024

ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 41 into a target database with TSTZ version 32.

 
Import: Release 19.0.0.0.0 - Production on Wed Oct 9 09:11:46 2024
Version 19.21.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 41 into a target database with TSTZ version 32.



-bash-4.2$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 9 09:17:49 2024
Version 19.21.0.0.0

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

Enter user-name: /as sysdba

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

SQL> select * from v$timezone_file;

FILENAME        VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat          32      0

SQL>

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;  2    3    4  

PROPERTY_NAME
--------------------------------------------------------------------------------------------------------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
32

DST_SECONDARY_TT_VERSION
0

DST_UPGRADE_STATE
NONE


SQL>


-bash-4.2$ ls -lrt
total 0
drwxr-xr-x 4 oracle oinstall 48 Oct  9 09:28 35099667
-bash-4.2$ cd 35099667/
-bash-4.2$ pwd
/u01/soft/patch/35099667
-bash-4.2$ /u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.43
Copyright (c) 2024, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/19.0.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.43
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-10-09_09-30-29AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
-bash-4.2$

-bash-4.2$ /u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.43
Copyright (c) 2024, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/19.0.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.43
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-10-09_09-34-45AM_1.log

Verifying environment and performing prerequisite checks...

Conflicts/Supersets for each patch are:

Patch : 35099667

    Bug SubSet of 35643107
    Subset bugs are:
    35099667

OPatch found that the following patch(es) are not required.
They are either subset of the patches in Oracle Home (or) subset of the patches in the given list:
 [ 35099667 ]

Log file location: /u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-10-09_09-34-45AM_1.log

OPatch succeeded.
-bash-4.2$


The normal process to upgrade timezone fixes this
@?/rdbms/admin/utltz_countstats.sql
@?/rdbms/admin/utltz_upg_check.sql
@?/rdbms/admin/utltz_upg_apply.sql

SQL> select * from v$timezone_file;

FILENAME        VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_42.dat          42      0

1 row selected.

SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 PDB                  MOUNTED
     4 STLBAS              MOUNTED
SQL> alter pluggable database STLBAS open;

Pluggable database altered.

SQL>


SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 PDB                  MOUNTED
     4 STLBAS              READ WRITE NO
     
SQL> alter session set container=stlbas;

Session altered.

SQL> select * from v$timezone_file;

FILENAME        VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat          32      0


The normal process to upgrade timezone fixes this
@?/rdbms/admin/utltz_countstats.sql
@?/rdbms/admin/utltz_upg_check.sql
@?/rdbms/admin/utltz_upg_apply.sql

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

Session altered.

.
Amount of TSTZ data using num_rows stats info in DBA_TABLES.
.
.
.

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

Session altered.

INFO: Starting with RDBMS DST update preparation.
.
.
.
.


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

Session altered.

INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv42 .
INFO: This database
.
.
.

Session altered.

SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     4 STLBAS              READ WRITE NO
SQL> select * from v$timezone_file;

FILENAME        VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_42.dat          42      0

1 row selected.

SQL>