[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 >
Wednesday, 9 October 2024
Remove Instance from InnoDb cluster in MYSQL
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>
Monday, 3 June 2024
ORA-01422: exact fetch returns more than requested number of rows in Oracle OEM 13
We are getting the following error when we want to add exadata database instances in Oracle OEM 13
Changes not saved.
bacoredb_STLBAS : ORA-01422: exact fetch returns more than requested number of rows
The error is seen due to duplicate targets that were existing in the repository.
Run the following query as SYSMAN user on the repository database to confirm if duplicate targets are present in the repository:
SQL> ALTER SESSION SET CURRENT_SCHEMA=SYSMAN;
SQL> column ENTITY_TYPE format a20
SQL> column ENTITY_NAME format a37
SQL> set lin 200
SQL> select entity_type,entity_name,manage_status,promote_status,LAST_UPDATED_TIME from EM_MANAGEABLE_ENTITIES where entity_name like '%bacoredb%';
ENTITY_TYPE ENTITY_NAME MANAGE_STATUS PROMOTE_STATUS LAST_UPDA
-------------------- ------------------------------------- ------------- -------------- ---------
oracle_database bacoredb_bacoredb1 2 3 01-NOV-23
oracle_pdb bacoredb_CDBROOT 2 3 01-NOV-23
oracle_pdb bacoredb_EMOB 2 3 01-NOV-23
oracle_pdb bacoredb_PDB1 2 3 01-NOV-23
oracle_pdb bacoredb_STLBAS 2 3 01-NOV-23
oracle_pdb bacoredb_HIKMAH 2 1 29-DEC-23
oracle_pdb bacoredb_ETESORO 2 1 02-JAN-24
oracle_dbsys bacoredb_sys 2 3 01-NOV-23
oracle_database bacoredb_bacoredb2_1 1 1 09-NOV-23
rac_database bacoredb_1 1 1 09-NOV-23
oracle_database bacoredb_bacoredb2 2 3 02-NOV-23
ENTITY_TYPE ENTITY_NAME MANAGE_STATUS PROMOTE_STATUS LAST_UPDA
-------------------- ------------------------------------- ------------- -------------- ---------
rac_database bacoredb 2 3 01-NOV-23
12 rows selected.
SQL>
From OMS server,
$OMS_HOME/bin/emcli login -username=SYSMAN
$OMS_HOME/bin/emcli sync
$OMS_HOME/bin/emcli delete_target -name="bacoredb_STLBAS" -type="oracle_database" -delete_monitored_targets
[oracle@em13c ~]$ /u01/app/oem/bin/emcli login -username=SYSMAN
Enter password :
Login successful
[oracle@em13c ~]$ /u01/app/oem/bin/emcli sync
Synchronized successfully
[oracle@em13c ~]$ /u01/app/oem/bin/emcli delete_target -name="bacoredb_STLBAS" -type="oracle_database" -delete_monitored_targets
Error: Target does not exist
[oracle@em13c ~]$ /u01/app/oem/bin/emcli delete_target -name="bacoredb_bacoredb2_1" -type="oracle_database" -delete_monitored_targets
Target "bacoredb_bacoredb2_1:oracle_database" deleted successfully
[oracle@em13c ~]$
SQL> select entity_type,entity_name,manage_status,promote_status,LAST_UPDATED_TIME from EM_MANAGEABLE_ENTITIES where entity_name like '%bacoredb%';
ENTITY_TYPE ENTITY_NAME MANAGE_STATUS PROMOTE_STATUS LAST_UPDA
-------------------- ------------------------------------- ------------- -------------- ---------
oracle_database bacoredb_bacoredb1 2 3 01-NOV-23
oracle_pdb bacoredb_CDBROOT 2 3 01-NOV-23
oracle_pdb bacoredb_EMOB 2 3 01-NOV-23
oracle_pdb bacoredb_PDB1 2 3 01-NOV-23
oracle_pdb bacoredb_STLBAS 2 3 01-NOV-23
oracle_pdb bacoredb_HIKMAH 2 1 29-DEC-23
oracle_pdb bacoredb_ETESORO 2 1 02-JAN-24
oracle_dbsys bacoredb_sys 2 3 01-NOV-23
rac_database bacoredb_1 1 1 09-NOV-23
oracle_database bacoredb_bacoredb2 2 3 02-NOV-23
rac_database bacoredb 2 3 01-NOV-23
11 rows selected.
SQL>
[oracle@em13c ~]$ /u01/app/oem/bin/emcli delete_target -name="bacoredb_1" -type="rac_database" -delete_monitored_targets
Target "bacoredb_1:rac_database" deleted successfully
[oracle@em13c ~]$
SQL> select entity_type,entity_name,manage_status,promote_status,LAST_UPDATED_TIME from EM_MANAGEABLE_ENTITIES where entity_name like '%bacoredb%';
ENTITY_TYPE ENTITY_NAME MANAGE_STATUS PROMOTE_STATUS LAST_UPDA
-------------------- ------------------------------------- ------------- -------------- ---------
oracle_database bacoredb_bacoredb1 2 3 01-NOV-23
oracle_pdb bacoredb_CDBROOT 2 3 01-NOV-23
oracle_pdb bacoredb_EMOB 2 3 01-NOV-23
oracle_pdb bacoredb_PDB1 2 3 01-NOV-23
oracle_pdb bacoredb_STLBAS 2 3 01-NOV-23
oracle_pdb bacoredb_HIKMAH 2 1 29-DEC-23
oracle_pdb bacoredb_ETESORO 2 1 02-JAN-24
oracle_dbsys bacoredb_sys 2 3 01-NOV-23
oracle_database bacoredb_bacoredb2 2 3 02-NOV-23
rac_database bacoredb 2 3 01-NOV-23
10 rows selected.
SQL>
Ref
EM12c, EM13c: How to Delete a Target in Enterprise Manager Cloud Control ( Doc ID 1905181.1 )