Friday 22 April 2022

Make Unusable and Invalid Index to VALID in oracle database

Make Unusable and Invalid Index to VALID in oracle database with below commands

Indexes:
SELECT 'alter index '||owner||'.'||index_name||' rebuild tablespace '||tablespace_name ||' ONLINE PARALLEL 8;'  FROM   dba_indexes  WHERE  status in('INVALID','UNUSABLE');

Index partitions:
SELECT 'alter index '||index_owner||'.'||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||' ONLINE PARALLEL 8;'  FROM   dba_ind_partitions  WHERE status in('INVALID','UNUSABLE');

Index subpartitions:
SELECT 'alter index '||index_owner||'.'||index_name ||' rebuild subpartition '||SUBPARTITION_NAME||' TABLESPACE '||tablespace_name ||' ONLINE PARALLEL 8;'  FROM   dba_ind_subpartitions WHERE  status in('INVALID','UNUSABLE');


Upgrade window or an on-demand or datapump-job loading of a secondary time zone data file is in an active state

Error while upgrading Timezone patch

SQL> DECLARE
            l_tz_version PLS_INTEGER;
        BEGIN
            l_tz_version := DBMS_DST.GET_LATEST_TIMEZONE_VERSION;
            DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
            DBMS_DST.begin_prepare(l_tz_version);
        END;
        /
  2    3    4    5    6    7    8  DECLARE
*
ERROR at line 1:
ORA-56920: a prepare or upgrade window or an on-demand or datapump-job loading of a secondary time zone data file is in an active state
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1382
ORA-06512: at line 6

SQL> SELECT property_name, property_value
        FROM   database_properties
        WHERE  property_name LIKE 'DST_%'
        ORDER BY property_name;
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         34
DST_SECONDARY_TT_VERSION       14
DST_UPGRADE_STATE              DATAPUMP(1)

SQL> select name, value$ from sys.props$ where name like '%DST%';
NAME                                           VALUE$
-------------------------         ----------------------------
DST_SECONDARY_TT_VERSION 14
DST_PRIMARY_TT_VERSION 34
DST_UPGRADE_STATE DATAPUMP(1)

Solution:
SQL> exec dbms_dst.unload_secondary;
PL/SQL procedure successfully completed.

SQL> select property_value from sys.database_properties where property_name = 'DST_UPGRADE_STATE';
PROPERTY_VALUE
--------------------
NONE

Unable to start postgres db with db=,user=,app=,client=DETAIL: Permissions should be u=rwx (0700) error

Unable start postgres database, when i tried to start db it exit with below error

root@postgresvm01.int data10 #  systemctl start edb-as-10

Job for edb-as-10.service failed because the control process exited with error code. See "systemctl status edb-as-10.service" and "journalctl -xe" for details.



Verified pgstartup.log file

[enterprisedb@postgresvm01 log]$ cat pgstartup.log

WARNING --> PERL_INSTALL_PATH is not set in /u01/edb/as10/etc/sysconfig/plLanguages.config file

WARNING --> PYTHON_INSTALL_PATH is not set in /u01/edb/as10/etc/sysconfig/plLanguages.config file

WARNING --> TCL_INSTALL_PATH is not set in /u01/edb/as10/etc/sysconfig/plLanguages.config file

2022-04-22 08:34:34 PDT [6885]: [1-1] db=,user=,app=,client=FATAL:  data directory "/pg_base/data10" has group or world access

2022-04-22 08:34:34 PDT [6885]: [2-1] db=,user=,app=,client=DETAIL:  Permissions should be u=rwx (0700).



Startup command failing due to permission issues 


Check Permission 

root@postgresvm01.int # ls -ltr

total 8

drwxr-xr-x. 22 enterprisedb enterprisedb 4096 Apr 22 08:31 data10


Change Permission 

root@postgresvm01.int # chmod 700 data10


root@postgresvm01.int # ls -ltr

total 8

drwx------. 22 enterprisedb enterprisedb 4096 Apr 22 08:31 data10


Db came up without any issues 

root@postgresvm01.int #systemctl start edb-as-10


root@postgresvm01.int # systemctl status edb-as-10

● edb-as-10.service - EDB Postgres Advanced Server 10

   Loaded: loaded (/usr/lib/systemd/system/edb-as-10.service; enabled; vendor preset: disabled)

   Active: active (running) since Fri 2022-04-22 08:37:47 PDT; 2h 8min ago

  Process: 6887 ExecStopPost=/bin/bash -c rm -f /var/lock/edb/as10/edb-as-10 (code=exited, status=0/SUCCESS)

  Process: 27092 ExecStop=/u01/edb/as10/bin/pg_ctl stop -m fast -w -D /pg_base/data10 (code=exited, status=0/SUCCESS)

  Process: 7294 ExecStartPost=/bin/bash -c touch /var/lock/edb/as10/edb-as-10 (code=exited, status=0/SUCCESS)

  Process: 7212 ExecStart=/bin/bash -c /usr/lib/systemd/system/edb-as-10.sh (code=exited, status=0/SUCCESS)

 Main PID: 7234 (edb-postgres)

   Memory: 7.4G

   CGroup: /system.slice/edb-as-10.service

           ─7234 /u01/edb/as10/bin/edb-postgres -D /pg_base/data10

           ─7235 postgres: logger process

           ─7236 postgres: startup process   recovering 0000000400001E420000006E

           ─7257 postgres: checkpointer process

           ─7258 postgres: writer process

           ─7292 postgres: stats collector process

           ─7293 postgres: wal receiver process   streaming 1E42/6E09A000

           └─7392 postgres: enterprisedb edb [local] idle