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');
Friday 22 April 2022
Make Unusable and Invalid Index to VALID in oracle database
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
Subscribe to:
Posts (Atom)