Category Uncategorized

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)

FIND POWER OF TWO NUMBERS IN PYTHON

    def power(x, y):
        pow = x ** y
        return pow