ORA-55610: Invalid DDL statement on history-tracked table

ORA-55610: Invalid DDL statement on history-tracked table

This error comes when you are trying to drop tables and flashback enabled for the table.

Select OWNER_NAME,TABLE_NAME,FLASHBACK_ARCHIVE_NAME,STATUS from dba_flashback_archive_tables where table_name = 'DEPT';

 If the table is listed with status is enabled from the dba_flash_archive_tables, then i will not allow to drop the table. you need to disable the flashback.

below command will disable the flashback of the table.

alter table DEPT no flashback archive;

how you can drop the table.

alertlog rotation script, cleanup trace location for oracle database

Below script will delete the trc,trm,backup alertlog zip files and alert xml files for all databases in one server.

#!/bin/bash
DB_LIST=`egrep -i ":Y|:N" /etc/oratab | cut -d":" -f1 | grep -v "\#" | grep -v "\*"`
for i in $DB_LIST ; do
export ORACLE_SID=$i
export ORACLE_HOME=`egrep -i ":Y|:N" /etc/oratab |grep $ORACLE_SID| cut -d":" -f2 | grep -v "\#" | grep -v "\*"`
export PATH=$ORACLE_HOME/bin:$PATH
TRACE_LOC=`sqlplus -s "/ as sysdba" <<EOF
       set heading off feedback off verify off
       select VALUE from v\\$diag_info where NAME='Diag Trace';
       exit
EOF`
##deleting trc,trm and .gz files in trace location
find $TRACE_LOC -name '*.trc' -mtime +5 -exec rm -f {} \;
find $TRACE_LOC -name '*.trm' -mtime +5 -exec rm -f {} \;
find $TRACE_LOC -name '*.gz' -mtime +10 -exec rm -f {} \;
TO_DATE="`date '+%d%m%Y%H%M%S'`";
##rotate alertlog.
mv $TRACE_LOC/alert_$ORACLE_SID.log $TRACE_LOC/alert_$ORACLE_SID.$TO_DATE
touch $TRACE_LOC/alert_$ORACLE_SID.log
chmod 640 $TRACE_LOC/alert_$ORACLE_SID.log
gzip $TRACE_LOC/alert_$ORACLE_SID.$TO_DATE
ALXML_LOC=`sqlplus -s "/ as sysdba" <<EOF
       set heading off feedback off verify off
       select VALUE from v\\$diag_info where NAME='Diag Alert';
       exit
EOF`
##deleting alert xml files
find $ALXML_LOC -name 'log_*' -mtime +5 -exec rm -f {} \;
done

latest oracle patches download

Oracle will list patches that are recently released in the master Doc ID (Doc ID 756671.1) in metalink.

ORA-02030: can only select from fixed tables/views

When we tried to grant acceess on v$session to a user, we will get below error

SQL> GRANT SELECT ON v$session TO tst_usr;
grant select on v$session to tst_usr
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views


this is because v$session is not direct table, its a public synonym. so you need to grant access on direct table that is V_$SESSION

Below query shows on which table v$session synonym created.

select table_owner, table_name FROM dba_synonyms where synonym_name = ‘V$SESSION’;
TABLE_OWNER TABLE_NAME
————– ——————————
SYS V_$SESSION

above out put show V_$SESSION  is the table, grant on table V_$SESSION equal to grant access on v$session


grant select on v_$session to tst_usr;

shell script to check archivelog gap between standby and primary(DG sync monitoring script)

below script can be used to find archivelog gap between primary and standby.
if shipping gap or apply gap is greater than or equal to 5 you will get mail(you can change the number if you want in if condition).


#!/bin/bash
export ORACLE_SID=ORCL
export ORACLE_HOME=`egrep -i ":Y|:N" /etc/oratab |grep $ORACLE_SID| cut -d":" -f2 | grep -v "\#" | grep -v "\*"`
export PATH=$ORACLE_HOME/bin:$PATH
DG_GAP_CHECK=`sqlplus -s "/ as sysdba" <<EOF
set pagesize 200
set linesize 121
set heading off feedback off verify off
select(select name from v\\$database) name,
(select max(sequence#) from v\\$archived_log where dest_id=1
) current_primary_seq,
(select max(sequence#)
from v\\$archived_log
where trunc(next_time)> sysdate-1
and dest_id=2
) max_stby,
(select nvl(
(select max(sequence#)-min(sequence#)
from v\\$archived_log
where trunc(next_time)> sysdate-1
and dest_id=2
and applied='NO'
),0)
from dual
) "To be Applied",
((select max(sequence#) from v\\$archived_log where dest_id=1)-
(select max(sequence#) from v\\$archived_log where dest_id=2)) "To be shipped"
from dual;
exit;
EOF`
TO_BE_SHIPPED="`echo $DG_GAP_CHECK|awk '{ print $5}'`"
TO_BE_APPLIED="`echo $DG_GAP_CHECK|awk '{ print $4}'`"
        if [ $TO_BE_SHIPPED -ge 5 ] || [ $TO_BE_APPLIED -ge 5 ]
        then
                echo $DG_GAP_CHECK | mail -r "sender@company.com" -s "`hostname`:Dataguard is out of sync" "receiver@company.com receiver@company.com"
          fi

RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process archived log file name

I find the warning in archivelog RMAN backup log.


RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process archived log file name

Check the archivelog file showing in warning is shipped to standby database and applied in standby.
also
Check primary and standby databases are in sync and archivelog shipping and applying is happening properly for standby database and applying according to your archivelog deletion policy.

If any delay if shipping archivelogs or any delay in apply archivelogs in standby also causes this warning message.

You can safely ignore this warning if primary and standby are in sync or apply process is going fine.

example for archivelog deletion policies:

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED TO ALL STANDBY;
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED TO ALL STANDBY BACKED UP 1 TIMES TO DISK;