After Replacing New Disks “importForceRequired” Status For Celldisks

After replacing new disks by replacing old disks in exadata, the status changed to “importForceRequired”

CellCLI> list celldisk
         CD_CELL001_xstcell      importForceRequired
         CD_CELL002_xstcell      importForceRequired
         CD_CELL003_xstcell      importForceRequired
         CD_CELL004_xstcell      importForceRequired
         CD_CELL005_xstcell      importForceRequired
         CD_CELL006_xstcell      importForceRequired
         CD_CELL007_xstcell      importForceRequired
         CD_CELL008_xstcell      importForceRequired
         CD_CELL009_xstcell      importForceRequired
         CD_CELL010_xstcell      importForceRequired
         CD_CELL011_xstcell      importForceRequired
         CD_CELL012_xstcell      importForceRequired
         CD_CELL013_xstcell      importForceRequired
         CD_CELL014_xstcell      importForceRequired
         CD_CELL015_xstcell      importForceRequired
         CD_CELL016_xstcell      importForceRequired
         CD_CELL017_xstcell      importForceRequired
         CD_CELL018_xstcell      importForceRequired
         CD_CELL019_xstcell      importForceRequired
         CD_CELL020_xstcell      importForceRequired
         CD_CELL021_xstcell      importForceRequired
         CD_CELL022_xstcell      importForceRequired
         CD_CELL023_xstcell      importForceRequired
         FD_00_xstcell           importForceRequired
         FD_01_xstcell           importForceRequired
         FD_02_xstcell           importForceRequired
         FD_03_xstcell           importForceRequired
         FD_04_xstcell           importForceRequired
         FD_05_xstcell           importForceRequired

Check if all disks are printed from OS using a shell script.

[root@x3cel00 raw]# for disks in `cellcli -e list celldisk|awk '{print $1}'`;do echo $disks;done
CD_CELL001_xstcell
CD_CELL002_xstcell
CD_CELL003_xstcell
CD_CELL004_xstcell
CD_CELL005_xstcell
CD_CELL006_xstcell
CD_CELL007_xstcell
CD_CELL008_xstcell
CD_CELL009_xstcell
CD_CELL010_xstcell
CD_CELL011_xstcell
CD_CELL012_xstcell
CD_CELL013_xstcell
CD_CELL014_xstcell
CD_CELL015_xstcell
CD_CELL016_xstcell
CD_CELL017_xstcell
CD_CELL018_xstcell
CD_CELL019_xstcell
CD_CELL020_xstcell
CD_CELL021_xstcell
CD_CELL022_xstcell
CD_CELL023_xstcell
FD_00_xstcell
FD_01_xstcell
FD_02_xstcell
FD_03_xstcell
FD_04_xstcell
FD_05_xstcell

Use the command “import celldisk cell_disk_name force” to force the import of the disks.

[root@x3cel00 raw]# for disk in `cellcli -e list celldisk|awk '{print $1}'`;do cellcli -e import celldisk $disk force;done
CellDisk CD_CELL001_xstcell successfully imported
CellDisk CD_CELL002_xstcell successfully imported
CellDisk CD_CELL003_xstcell successfully imported
CellDisk CD_CELL004_xstcell successfully imported
CellDisk CD_CELL005_xstcell successfully imported
CellDisk CD_CELL006_xstcell successfully imported
CellDisk CD_CELL007_xstcell successfully imported
CellDisk CD_CELL008_xstcell successfully imported
CellDisk CD_CELL009_xstcell successfully imported
CellDisk CD_CELL010_xstcell successfully imported
CellDisk CD_CELL011_xstcell successfully imported
CellDisk CD_CELL012_xstcell successfully imported
CellDisk CD_CELL013_xstcell successfully imported
CellDisk CD_CELL014_xstcell successfully imported
CellDisk CD_CELL015_xstcell successfully imported
CellDisk CD_CELL016_xstcell successfully imported
CellDisk CD_CELL017_xstcell successfully imported
CellDisk CD_CELL018_xstcell successfully imported
CellDisk CD_CELL019_xstcell successfully imported
CellDisk CD_CELL020_xstcell successfully imported
CellDisk CD_CELL021_xstcell successfully imported
CellDisk CD_CELL022_xstcell successfully imported
CellDisk CD_CELL023_xstcell successfully imported
CellDisk FD_00_xstcell successfully imported
CellDisk FD_01_xstcell successfully imported
CellDisk FD_02_xstcell successfully imported
CellDisk FD_03_xstcell successfully imported
CellDisk FD_04_xstcell successfully imported
CellDisk FD_05_xstcell successfully imported

After force import, the celldisk status changed to normal.

[root@x3cel00 raw]# cellcli
CellCLI: Release 11.2.3.2.1 - Production on Tue Jul 18 16:20:48 GMT+05:30 2023

Copyright (c) 2007, 2012, Oracle.  All rights reserved.
Cell Efficiency Ratio: 658

CellCLI> list celldisk
         CD_CELL001_xstcell      normal
         CD_CELL002_xstcell      normal
         CD_CELL003_xstcell      normal
         CD_CELL004_xstcell      normal
         CD_CELL005_xstcell      normal
         CD_CELL006_xstcell      normal
         CD_CELL007_xstcell      normal
         CD_CELL008_xstcell      normal
         CD_CELL009_xstcell      normal
         CD_CELL010_xstcell      normal
         CD_CELL011_xstcell      normal
         CD_CELL012_xstcell      normal
         CD_CELL013_xstcell      normal
         CD_CELL014_xstcell      normal
         CD_CELL015_xstcell      normal
         CD_CELL016_xstcell      normal
         CD_CELL017_xstcell      normal
         CD_CELL018_xstcell      normal
         CD_CELL019_xstcell      normal
         CD_CELL020_xstcell      normal
         CD_CELL021_xstcell      normal
         CD_CELL022_xstcell      normal
         CD_CELL023_xstcell      normal
         FD_00_xstcell           normal
         FD_01_xstcell           normal
         FD_02_xstcell           normal
         FD_03_xstcell           normal
         FD_04_xstcell           normal
         FD_05_xstcell           normal

All griddisks are also active.

CellCLI> list griddisk
         XS_CD_CELL001_xstcell   active
         XS_CD_CELL002_xstcell   active
         XS_CD_CELL003_xstcell   active
         XS_CD_CELL004_xstcell   active
         XS_CD_CELL005_xstcell   active
         XS_CD_CELL006_xstcell   active
         XS_CD_CELL007_xstcell   active
         XS_CD_CELL008_xstcell   active
         XS_CD_CELL009_xstcell   active
         XS_CD_CELL010_xstcell   active
         XS_CD_CELL011_xstcell   active
         XS_CD_CELL012_xstcell   active
         XS_CD_CELL013_xstcell   active
         XS_CD_CELL014_xstcell   active
         XS_CD_CELL015_xstcell   active
         XS_CD_CELL016_xstcell   active
         XS_CD_CELL017_xstcell   active
         XS_CD_CELL018_xstcell   active
         XS_CD_CELL019_xstcell   active
         XS_CD_CELL020_xstcell   active
         XS_CD_CELL021_xstcell   active
         XS_CD_CELL022_xstcell   active
         XS_CD_CELL023_xstcell   active

CellCLI> list griddisk attributes name,asmmodestatus
         XS_CD_CELL001_xstcell   UNUSED
         XS_CD_CELL002_xstcell   UNUSED
         XS_CD_CELL003_xstcell   UNUSED
         XS_CD_CELL004_xstcell   UNUSED
         XS_CD_CELL005_xstcell   UNUSED
         XS_CD_CELL006_xstcell   UNUSED
         XS_CD_CELL007_xstcell   UNUSED
         XS_CD_CELL008_xstcell   UNUSED
         XS_CD_CELL009_xstcell   UNUSED
         XS_CD_CELL010_xstcell   UNUSED
         XS_CD_CELL011_xstcell   UNUSED
         XS_CD_CELL012_xstcell   UNUSED
         XS_CD_CELL013_xstcell   UNUSED
         XS_CD_CELL014_xstcell   UNUSED
         XS_CD_CELL015_xstcell   UNUSED
         XS_CD_CELL016_xstcell   UNUSED
         XS_CD_CELL017_xstcell   UNUSED
         XS_CD_CELL018_xstcell   UNUSED
         XS_CD_CELL019_xstcell   UNUSED
         XS_CD_CELL020_xstcell   UNUSED
         XS_CD_CELL021_xstcell   UNUSED
         XS_CD_CELL022_xstcell   UNUSED
         XS_CD_CELL023_xstcell   UNUSED

CellCLI> list alerthistory
         8_1     2022-02-28T17:12:44+05:30       warning         "Hugepage allocation failure in service cellsrv.  Number of Hugepages allocated is 0, failed to allocate 3000"
         51      2023-07-18T12:50:56+05:30       critical        "ORA-00700: soft internal error, arguments: [main_6a], [3], [Invalid IP addresses in cellinit.ora file], [], [], [], [], [], [], [], [], []"
         52      2023-07-18T13:03:37+05:30       critical        "ORA-00700: soft internal error, arguments: [main_6a], [3], [Invalid IP addresses in cellinit.ora file], [], [], [], [], [], [], [], [], []"
         53      2023-07-18T13:07:14+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL001  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL001_xstcel  Grid Disk     : XS_CD_CELL001_xstcel"
         54      2023-07-18T13:07:14+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL002  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL002_xstcel  Grid Disk     : XS_CD_CELL002_xstcel"
         55      2023-07-18T13:07:15+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL003  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL003_xstcel  Grid Disk     : XS_CD_CELL003_xstcel"
         56      2023-07-18T13:07:15+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL004  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL004_xstcel  Grid Disk     : XS_CD_CELL004_xstcel"
         57      2023-07-18T13:07:16+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL005  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL005_xstcel  Grid Disk     : XS_CD_CELL005_xstcel"
         58      2023-07-18T13:07:17+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL006  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL006_xstcel  Grid Disk     : XS_CD_CELL006_xstcel"
         59      2023-07-18T13:07:17+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL007  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL007_xstcel  Grid Disk     : XS_CD_CELL007_xstcel"
         60      2023-07-18T13:07:18+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL008  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL008_xstcel  Grid Disk     : XS_CD_CELL008_xstcel"
         61      2023-07-18T13:07:19+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL009  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL009_xstcel  Grid Disk     : XS_CD_CELL009_xstcel"
         62      2023-07-18T13:07:19+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL010  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL010_xstcel  Grid Disk     : XS_CD_CELL010_xstcel"
         63      2023-07-18T13:07:20+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL011  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL011_xstcel  Grid Disk     : XS_CD_CELL011_xstcel"
         64      2023-07-18T13:07:20+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL012  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL012_xstcel  Grid Disk     : XS_CD_CELL012_xstcel"
         65      2023-07-18T13:07:21+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL013  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL013_xstcel  Grid Disk     : XS_CD_CELL013_xstcel"
         66      2023-07-18T13:07:22+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL014  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL014_xstcel  Grid Disk     : XS_CD_CELL014_xstcel"
         67      2023-07-18T13:07:22+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL015  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL015_xstcel  Grid Disk     : XS_CD_CELL015_xstcel"
         68      2023-07-18T13:07:23+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL016  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL016_xstcel  Grid Disk     : XS_CD_CELL016_xstcel"
         69      2023-07-18T13:07:24+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL017  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL017_xstcel  Grid Disk     : XS_CD_CELL017_xstcel"
         70      2023-07-18T13:07:24+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL018  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL018_xstcel  Grid Disk     : XS_CD_CELL018_xstcel"
         71      2023-07-18T13:07:25+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL019  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL019_xstcel  Grid Disk     : XS_CD_CELL019_xstcel"
         72      2023-07-18T13:07:25+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL020  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL020_xstcel  Grid Disk     : XS_CD_CELL020_xstcel"
         73      2023-07-18T13:07:26+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL021  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL021_xstcel  Grid Disk     : XS_CD_CELL021_xstcel"
         74      2023-07-18T13:07:27+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL022  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL022_xstcel  Grid Disk     : XS_CD_CELL022_xstcel"
         75      2023-07-18T13:07:27+05:30       clear           "Hard disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/CELL023  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : CD_CELL023_xstcel  Grid Disk     : XS_CD_CELL023_xstcel"
         76      2023-07-18T13:07:28+05:30       clear           "Flash disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/FLASH01  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : FD_00_xstcel  Grid Disk     : Not configured  Flash Cache   : Not configured  Flash Log     : Present"
         77      2023-07-18T13:07:29+05:30       clear           "Flash disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/FLASH02  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : FD_01_xstcel  Grid Disk     : Not configured  Flash Cache   : Not configured  Flash Log     : Present"
         78      2023-07-18T13:07:29+05:30       clear           "Flash disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/FLASH03  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : FD_02_xstcel  Grid Disk     : Not configured  Flash Cache   : Not configured  Flash Log     : Present"
         79      2023-07-18T13:07:30+05:30       clear           "Flash disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/FLASH04  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : FD_03_xstcel  Grid Disk     : Not configured  Flash Cache   : Not configured  Flash Log     : Present"
         80      2023-07-18T13:07:30+05:30       clear           "Flash disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/FLASH05  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : FD_04_xstcel  Grid Disk     : Not configured  Flash Cache   : Not configured  Flash Log     : Present"
         81      2023-07-18T13:07:31+05:30       clear           "Flash disk was replaced.  Status        : NORMAL  Manufacturer  : Unknown  Model Number  : Unknown  Size          : 0M  Serial Number : /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/FLASH06  Firmware      : Unknown  Slot Number   : Unknown  Cell Disk     : FD_05_xstcel  Grid Disk     : Not configured  Flash Cache   : Not configured  Flash Log     : Present"
         82      2023-07-18T13:38:00+05:30       critical        "ORA-00700: soft internal error, arguments: [main_21], [11], [Not enough open file descriptors], [], [], [], [], [], [], [], [], []"

CAN WE CREATE MULTIPLE INDEXES ON THE SAME COLUMN?

It is not possible to create multiple straight forward or visible index in the same column of the table.

SQL> BEGIN
  for x in 1 .. 10 loop
  execute immediate 'CREATE index ind_'||x||' on bline(name)';
  END loop;
END;
/  2    3    4    5    6
BEGIN
*
ERROR at line 1:
ORA-01408: such column list already indexed
ORA-06512: at line 3

But invisible indexes can be created with different structure.

2 b-tree cannot be created on the same column.

One bitmap invisible and b-tree can be created on same column.

SQL> CREATE index ind_2 on bline(name) invisible;
CREATE index ind_2 on bline(name) invisible
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> CREATE bitmap index ind_2 on bline(name) invisible;

Index created.

PLS-00201: identifier ‘DBMS_STATS.GATHER_DATABASE_STATS’ must be declared

Cause:

SQL> EXEC dbms_stats.gather_database_stats();
BEGIN dbms_stats.gather_database_stats(); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_STATS.GATHER_DATABASE_STATS' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Solution:

If it is multitenant, then open the pdb.

SQL> alter pluggable database updb open;

Pluggable database altered.

SQL> alter pluggable database updb save state;

Pluggable database altered.

SQL> alter session set container=updb;

Session altered.

SQL> set timing on
SQL> EXEC dbms_stats.gather_database_stats();

Sync PDB failed with ORA-959 during ‘alter user c##hydra temporary tablespace temp1’

While opening of a new pdb which was created from PDB$SEED, error ORA-959 occurred due to missing temp tablespace.

SQL> show pdbs

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

Warning: PDB altered with errors.

Plugin violations shows these failures which need action to open the PDB clean.

SQL> col NAME for a20
SQL> col CAUSE for a20
SQL> col MESSAGE for a20
SQL> col ACTION for a20
SQL> set lines 200 pages 1000
SQL> SELECT name,cause,message,action from PDB_PLUG_IN_VIOLATIONS;

NAME                 CAUSE                MESSAGE              ACTION
-------------------- -------------------- -------------------- --------------------
UPDB                 Sync Failure         Sync PDB failed with
                                           ORA-959 during 'alt
                                          er user c##hydra tem
                                          porary tablespace te
                                          mp1'

UPDB                 Sync Failure         Sync PDB failed with
                                           ORA-959 during 'alt
                                          er user system tempo
                                          rary tablespace temp
                                          1'

Workaround the problem by manually creating the missing temporary tablespace.

SQL> alter session set container=updb;

Session altered.

SQL> CREATE temporary tablespace TEMP1 tempfile '/u01/app/oracle/oradata/hydrupgd/updb/temp1.dbf' size 1G;

Tablespace created.

PDB is opened without any restricted mode.

SQL> alter pluggable database updb close;

Pluggable database altered.

SQL> alter pluggable database updb open;

Pluggable database altered.

RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

This error may occur 95% of the time due to wrong listener entry in the network configuration files. The error itself is some what misleading to the original cause.

Steps taken for ora-01017 – Multiple times changed the password for sys user, recreated password files and also copied from primary to standby. It frustrate a lot with waste of valuable time for this simple error with no solution.

Even though the error states that the user credentials and password are incorrect, still this error may occur due to dynamic listener registration.

[oracle@xstdby dbs]$ rman target sys/password@db9zx auxiliary sys/password@xstdb

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Jun 26 10:08:23 2023
Version 19.3.0.0.0

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

connected to target database: DB9ZX (DBID=1041852011)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

In listener status, there are two output’s, one with unknown and other with blocked. This says that the listener is not dynamically registered and need to statistically configured. Even though there are lot of formats for static registration of listener over the internet, none of them works properly due to spaces or parenthesis etc.

[oracle@xstdby admin]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 26-JUN-2023 10:13:45

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                26-JUN-2023 10:11:54
Uptime                    0 days 0 hr. 1 min. 52 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/db_home/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/xstdby/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xstdby)(PORT=1521)))
Services Summary...
Service "XSTDB" has 2 instance(s).
  Instance "xstdb", status UNKNOWN, has 1 handler(s) for this service...
  Instance "xstdb", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

Use the below format for static listener configuration which works correctly.

LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.1)(PORT = 1521))
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   )
 )
SID_LIST_LISTENER=
   (SID_LIST=
     (SID_DESC=
      (GLOBAL_DBNAME=xstdb)
      (SID_NAME=xstdb)
      (ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_home/)
 )
 )

STATIC LISTENER ENTRY FORMAT IN ORACLE

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME=ORCL)
      (SID_NAME = orcldg)
      (ORACLE_HOME = /grid/base/product/12.1.0/grid)
      (PROGRAM = extproc)
    )

    (SID_DESC =
      (GLOBAL_DBNAME=ORCL)
      (SID_NAME = orcldgp)
      (ORACLE_HOME = /grid/base/product/12.1.0/grid)
      (PROGRAM = extproc)
    )
  )

TNS-12541: TNS:no listener

During tnsping, the following error is expected if the source listener is not running.

[oracle@xhydra dbs]$ tnsping db9zx

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-JUN-2023 07:32:39

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <host>)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = db9zx)))
TNS-12541: TNS:no listener

Error utility says the following.

[oracle@xhydra dbs]$ oerr ora 12541
12541, 00000, "TNS:no listener"
// *Cause: The connection request could not be completed because the listener
// is not running.
// *Action: Ensure that the supplied destination address matches one of
// the addresses used by the listener - compare the TNSNAMES.ORA entry with
// the appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to
// go by way of an Interchange). Start the listener on the remote machine.

Start the listener in source.

[oracle@xhydra ~]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-JUN-2023 07:33:14

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/xhydra/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xhydra)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xhydra)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                17-JUN-2023 07:33:14
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/xhydra/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xhydra)(PORT=1521)))
The listener supports no services
The command completed successfully

Tnsping works perfectly.

SERVICE_NAME = db9zx)))
OK (510 msec)

FIND OUT IF AN INIT PARAMETER IS MODIFIED OR NOT

There are multiple ways to identify a human change of system parameter in oracle database. This is really essential for any DBA because of security reasons and to avoid any future impacts due to repercussions of the change unknowingly or intentionally by junior guy or external hackers.

Modify the parameter value for parallel_max_servers

SQL> set time on
14:09:03 SQL> show parameter parallel_max_ser

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers                 integer     40
14:09:09 SQL> alter system set parallel_max_servers=80;

System altered.

1)Check dba_hist_parameter to check the modified status of the parameter. This view depends on AWR snapshots and its retention. If the retention period is expired, then there is no use of this method.

14:39:32 SQL> SELECT SNAP_ID,PARAMETER_NAME,VALUE,ISDEFAULT,ISMODIFIED from dba_hist_parameter WHERE PARAMETER_NAME='parallel_max_servers' and ISMODIFIED='MODIFIED' order by SNAP_ID asc;

   SNAP_ID PARAMETER_NAME       VALUE                ISDEFAULT ISMODIFIED
---------- -------------------- -------------------- --------- ----------
       100 parallel_max_servers 80                   TRUE      MODIFIED

2) Probe the alert log for change of parameter. This is because, alert log is the bible to refer for any parameter change in the database. There are some exceptional cases where alert log may miss the changes.

[oracle@xhydra ~]$ grep 'parallel_max_servers' /u01/app/oracle/diag/rdbms/db9zx/db9zx/trace/alert_db9zx.log
ALTER SYSTEM SET parallel_max_servers=80 SCOPE=BOTH;

3)Another way is to enable 10046 system trace in the database all the time. This may record all the happenings in the database and may need an extra storage. This trace can be periodically compressed with extreme compression to counter storage issues. But the benefits of using this trace outweighs the issue itself. Just to identify an ‘ONCE IN A BLUEMOON’ happening, it is not necessary to enable trace all the time. But 10046 trace also has other performance diagnostics which can balance the benefits to drawback.

SQL> alter system set tracefile_identifier='10046';

Session altered.

SQL> alter system set events '10046 trace name context forever,level 2';

Session altered.

...


*** 2023-06-17T07:50:58.502863+05:30 (CDB$ROOT(1))
WAIT #140609190796088: nam='SQL*Net message from client' ela= 12117882 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=6439910440
CLOSE #140609190796088:c=6,e=6,dep=0,type=1,tim=6439910526
=====================
PARSING IN CURSOR #140609190794448 len=40 dep=0 uid=0 oct=49 lid=0 tim=6439910690 hv=3012368407 ad='0' sqlid='bvkx42utsu60r'
alter system set parallel_max_servers=80 <==================
END OF STMT
PARSE #140609190794448:c=144,e=144,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=6439910690
WAIT #140609190794448: nam='reliable message' ela= 169 channel context=2023208056 channel handle=2023112368 broadcast message=2026347512 obj#=-1 tim=6439911255
WAIT #140609190794448: nam='Disk file operations I/O' ela= 86 FileOperation=2 fileno=0 filetype=13 obj#=-1 tim=6439911404
WAIT #140609190794448: nam='Parameter File I/O' ela= 8 blkno=1 #blks=1 read/write=1 obj#=-1 tim=6439911431
WAIT #140609190794448: nam='Parameter File I/O' ela= 2 blkno=2 #blks=3 read/write=1 obj#=-1 tim=6439911496
WAIT #140609190794448: nam='Parameter File I/O' ela= 8226 blkno=5 #blks=3 read/write=2 obj#=-1 tim=6439920451
WAIT #140609190794448: nam='Parameter File I/O' ela= 753 blkno=1 #blks=1 read/write=2 obj#=-1 tim=6439921293
WAIT #140609190794448: nam='Parameter File I/O' ela= 6 blkno=5 #blks=3 read/write=1 obj#=-1 tim=6439921336
WAIT #140609190794448: nam='Parameter File I/O' ela= 674 blkno=2 #blks=3 read/write=2 obj#=-1 tim=6439922021
WAIT #140609190794448: nam='Parameter File I/O' ela= 581 blkno=1 #blks=1 read/write=2 obj#=-1 tim=6439922633
WAIT #140609190794448: nam='Parameter File I/O' ela= 926 blkno=5 #blks=3 read/write=2 obj#=-1 tim=6439923582
WAIT #140609190794448: nam='Disk file operations I/O' ela= 14 FileOperation=5 fileno=0 filetype=13 obj#=-1 tim=6439923618
WAIT #140609190794448: nam='Disk file operations I/O' ela= 20 FileOperation=8 fileno=0 filetype=8 obj#=-1 tim=6439923976
EXEC #140609190794448:c=2131,e=13302,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=6439924018
WAIT #140609190794448: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=6439924076

4) Use audit trails

ORA-39083: Object type MATERIALIZED_VIEW:”KISH”.”MVREMOTE” failed to create with error:ORA-00942: table or view does not exist

Cause:

The error is expected behavior because of missing dependent container table of the mview.

[oracle@xhydra ~]$ impdp \'/ as sysdba\' directory=home dumpfile=mview.dmp logfile=mview.log remap_schema=KISH:KISH

Import: Release 19.0.0.0.0 - Production on Mon Jun 12 20:02:24 2023
Version 19.3.0.0.0

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

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

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=home dumpfile=mview.dmp logfile=mview.log remap_schema=KISH:KISH
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
ORA-39083: Object type MATERIALIZED_VIEW:"KISH"."MVREMOTE" failed to create with error:
ORA-00942: table or view does not exist

Failing sql is:
CREATE MATERIALIZED VIEW "KISH"."MVREMOTE" ("XNAME", "XID", "CITY", "COUNTRY", "DEPARTMENT", "DEP_ID", "XDATE", "SALARY") USING ("MVREMOTE", (10, 'DB9ZX', 1, 0, 0, "KISH", "YTBL", '2023-06-12 19:32:24', 262144, 73213, '1950-01-01 12:00:00', '', 1, 'FE01', 15319225, 0, NULL, (1, "XID", "XID", 2, 323, 0)), 2097249, 10, ('1950-01-01 12:00:00', 61, 0, 0, 15319225, 0, 0, 4194304, 2, NULL, NULL)) REFRESH COMPLETE WITH PRIMARY KEY AS SELECT "YTBL"."XNAME" "XNAME","YTBL"."XID" "XID","YTBL"."CITY" "CITY","YTBL"."COUNTRY" "COUNTRY","YTBL"."DEPARTMENT" "DEPARTMENT","YTBL"."DEP_ID" "DEP_ID","YTBL"."XDATE" "XDATE","YTBL"."SALARY" "SALARY" FROM "KISH"."YTBL" "YTBL"

All mviews are created along with a container table during initial phase. During export/import, the dependent container table should also be export/imported in database.

Solution:

Verify the container table.

SQL> col OWNER for a20
SQL> SELECT owner,object_name, object_type FROM dba_objects WHERE object_name = 'MVREMOTE';

OWNER                OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------- --------------------

KISH                 MVREMOTE             TABLE
KISH                 MVREMOTE             MATERIALIZED VIEW

Export both container and its mview pair.

[oracle@xhydra ~]$ expdp \'/ as sysdba\' directory=home dumpfile=mview.dmp logfile=mview.log schemas=KISH include=TABLE:"IN('MVREMOTE')", MATERIALIZED_VIEW:"IN('MVREMOTE')" reuse_dumpfiles=Y

Export: Release 19.0.0.0.0 - Production on Mon Jun 12 20:24:30 2023
Version 19.3.0.0.0

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

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

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=home dumpfile=mview.dmp logfile=mview.log schemas=KISH include=TABLE:IN('MVREMOTE'), MATERIALIZED_VIEW:IN('MVREMOTE') reuse_dumpfiles=Y
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
. . exported "KISH"."MVREMOTE"                           277.0 MB 5000000 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/mview.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Jun 12 20:24:57 2023 elapsed 0 00:00:26

Import both container and mview.

[oracle@xhydra ~]$ impdp \'/ as sysdba\' directory=home dumpfile=mview.dmp logfile=mview.log INCLUDE=TABLE:"IN('MVREMOTE')", MATERIALIZED_VIEW:"IN('MVREMOTE')" table_exists_action=replace

Import: Release 19.0.0.0.0 - Production on Mon Jun 12 20:29:28 2023
Version 19.3.0.0.0

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

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

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=home dumpfile=mview.dmp logfile=mview.log INCLUDE=TABLE:IN('MVREMOTE'), MATERIALIZED_VIEW:IN('MVREMOTE') table_exists_action=replace
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "KISH"."MVREMOTE"                           277.0 MB 5000000 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Mon Jun 12 20:29:35 2023 elapsed 0 00:00:06

TNS-12543: TNS:destination host unreachable

There are multiple reasons for the error to occur due to unreachable host itself and there is nothing to do from database or listener.

[oracle@xhydra ~]$ tnsping xscalibal

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = xscalibal)(UR = A)))
TNS-12543: TNS:destination host unreachable

One of the main reason is firewall service which does not allow the connections.

[oracle@DBZX21 ~]$ service firewalld status
Redirecting to /bin/systemctl status firewalld.service
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
   Active: active (running) since Tue 2023-06-13 12:47:34 IST; 1h 59min ago
     Docs: man:firewalld(1)
 Main PID: 877 (firewalld)
    Tasks: 2 (limit: 35835)
   Memory: 30.1M
   CGroup: /system.slice/firewalld.service
           └─877 /usr/libexec/platform-python -s /usr/sbin/firewalld --nofork --nopid
[oracle@DBZX21 ~]$ service firewalld stop
Redirecting to /bin/systemctl stop firewalld.service
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ====
Authentication is required to stop 'firewalld.service'.
Authenticating as: kishan
Password:
polkit-agent-helper-1: pam_authenticate failed: Authentication failure
==== AUTHENTICATION FAILED ====
Failed to stop firewalld.service: Access denied
See system logs and 'systemctl status firewalld.service' for details.
[oracle@DBZX21 ~]$ su
Password:
[root@DBZX21 oracle]# service firewalld stop
Redirecting to /bin/systemctl stop firewalld.service
[root@DBZX21 oracle]# service firewalld disable
The service command supports only basic LSB actions (start, stop, restart, try-restart, reload, force-reload, status). For other actions, please try to use systemctl.

After disabling firewall, tnsping worked fine.

[oracle@xhydra ~]$ tnsping xscalibal

(SERVICE_NAME = xscalibal)(UR = A)))
OK (0 msec)