Oracle DBA by Example. Code samples, Scripts, Reference.: February 2019

Sunday, February 17, 2019

No space on device, under oracle trc files directory

===================================
General
===================================
When checking for free space, only 2.7G are available.
But when checking for used space, there should be 15Gb Available.
Where did 12Gb gone?

oracle@my_server:/software/oracle>% du -sh * |sort -nr | head -10
150M diag
16K lost+found
12K cfgtoollogs
5.2M admin
4.4G 111
3.9M installers
2.9M oracle

oracle@my_server:/software/oracle>% df -hP | grep oracle
/dev/mapper/Volume00-LogVol07 20G 16G 2.7G 86% /software/oracle

Where are 12Gb gone?

===================================
Look for deleted files, which were not really deleted
===================================

These files should have been deleted, but are locked by a process that prevent them from being deleted.

For example:

/software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trc (deleted)

The solution would be to kill the locking process.

And the file would be erased from disk by Linux OS.

Be Careful!!! - Do not to kill oracle background processes!

There are many (deleted) files

run this as root

oracle 26032 oracle 11w REG 253,8 13428 99120 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trc (deleted)
oracle 26032 oracle 12w REG 253,8 257 99121 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trm (deleted)
oracle 26036 oracle 11w REG 253,8 13428 99120 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trc (deleted)
oracle 26036 oracle 12w REG 253,8 257 99121 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trm (deleted)
oracle 26040 oracle 11w REG 253,8 13428 99120 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trc (deleted)
oracle 26040 oracle 12w REG 253,8 257 99121 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trm (deleted)
oracle 26044 oracle 11w REG 253,8 13428 99120 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trc (deleted)
oracle 26044 oracle 12w REG 253,8 257 99121 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trm (deleted)
oracle 26048 oracle 11w REG 253,8 13428 99120 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trc (deleted)
oracle 26048 oracle 12w REG 253,8 257 99121 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trm (deleted)
oracle 26052 oracle 11w REG 253,8 13428 99120 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trc (deleted)
oracle 26052 oracle 12w REG 253,8 257 99121 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trm (deleted)
oracle 26056 oracle 11w REG 253,8 13428 99120 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trc (deleted)
oracle 26056 oracle 12w REG 253,8 257 99121 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trm (deleted)
oracle 26060 oracle 11w REG 253,8 13428 99120 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trc (deleted)
oracle 26060 oracle 12w REG 253,8 257 99121 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trm (deleted)
oracle 26064 oracle 11w REG 253,8 13428 99120 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trc (deleted)
oracle 26064 oracle 12w REG 253,8 257 99121 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trm (deleted)
oracle 26068 oracle 11w REG 253,8 13428 99120 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trc (deleted)
oracle 26068 oracle 12w REG 253,8 257 99121 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trm (deleted)
oracle 26119 oracle 11w REG 253,8 13428 99120 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trc (deleted)
oracle 26119 oracle 12w REG 253,8 257 99121 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trm (deleted)
oracle 26123 oracle 11w REG 253,8 13428 99120 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trc (deleted)
oracle 26123 oracle 12w REG 253,8 257 99121 /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_25871.trm (deleted)

lsof -n | grep /software/oracle | grep -v lib | grep -v dbs | grep -v bin | grep -v msb | grep deleted | wc -l
72

Some are locked by Oracle Background processes:
oracle@my_server:/software/oracle>% ps -ef | grep 26032
oracle 26032 1 0 2018 ? 18:32:38 ora_dbw0_igt

Some are locked by regular sessions:
oracle@my_server:/software/oracle>% ps -ef | grep 31779
oracle 31779 1 2 2018 ? 3-14:05:48 oracleigt (LOCAL=NO)

These can be killed:
oracle@my_server:/software/oracle>% kill -9 31779

After killing the locking processes, the used disk is down to 4.7G
Which is in sync with

oracle@my_server:/software/oracle>% du -sh * |sort -nr | head -10
150M diag
16K lost+found
12K cfgtoollogs
5.2M admin
4.4G 111
3.9M installers
2.9M oracle

oracle@my_server:/software/oracle>% df -hP | grep oracle
/dev/mapper/Volume00-LogVol07 20G 4.7G 14G 26% /software/oracle

===================================
One more example
===================================

root@my_server:/backup/ora_online>% lsof | grep online
vbda 5488 root 4r DIR 253,25 4096 2 /backup/ora_online
vbda 5488 root 5r DIR 253,25 0 4685825 /backup/ora_online/for_backup/20191107_1622 (deleted)
su 7703 root cwd DIR 253,25 4096 2 /backup/ora_online
bash 7807 root cwd DIR 253,25 4096 2 /backup/ora_online
lsof 9371 root cwd DIR 253,25 4096 2 /backup/ora_online
grep 9372 root cwd DIR 253,25 4096 2 /backup/ora_online
lsof 9373 root cwd DIR 253,25 4096 2 /backup/ora_online
bash 28238 shdaemon cwd DIR 253,25 4096 2 /backup/ora_online

root@my_server:/backup/ora_online>% ls -l /backup/ora_online/for_backup/20191107_1622
ls: /backup/ora_online/for_backup/20191107_1622: No such file or directory

root@my_server:/backup/ora_online>% rm -f /backup/ora_online/for_backup/20191107_1622

root@my_server:/backup/ora_online>% lsof | grep online
su 7703 root cwd DIR 253,25 4096 2 /backup/ora_online
bash 7807 root cwd DIR 253,25 4096 2 /backup/ora_online
lsof 11300 root cwd DIR 253,25 4096 2 /backup/ora_online
grep 11301 root cwd DIR 253,25 4096 2 /backup/ora_online
lsof 11302 root cwd DIR 253,25 4096 2 /backup/ora_online
bash 28238 shdaemon cwd DIR 253,25 4096 2 /backup/ora_online

Thursday, February 14, 2019

Code Generation Example sql

SET FEEDBACK OFF

spool gen_gen.sql
SELECT
'SPOOL '||owner||'_REBUILD.sql'||CHR(10)||
'PROMPT PROMPT START WORKING ON '||owner||CHR(10)||
'PROMPT ALTER TABLE '||owner||'.SGA_W_PSMS_SUBSCRIBER ENABLE ROW MOVEMENT ;;' ||CHR(10)||
'PROMPT ALTER TABLE '||owner||'.SGA_W_PSMS_SUBSCRIBER MOVE;;' ||CHR(10)||
'PROMPT PROMPT Finished Rebuild Table'||CHR(10)||
'PROMPT ALTER INDEX '||owner||'.SSU_PSMS_MSISDN_IDX REBUILD ONLINE;;'||CHR(10)||
'PROMPT ALTER INDEX '||owner||'.SSU_PSMS_IMSI_IDX REBUILD ONLINE;;'||CHR(10)||
'PROMPT ALTER INDEX '||owner||'.CURRENT_COUNTRY_INX REBUILD ONLINE;;'||CHR(10)||
'PROMPT PROMPT Finished Rebuild Indexes'||CHR(10)||
'PROMPT BEGIN'||CHR(10)||
'PROMPT DBMS_STATS.unlock_table_stats(ownname => '''||owner||''', tabname => ''SGA_W_PSMS_SUBSCRIBER'');;'||CHR(10)||
'PROMPT DBMS_STATS.gather_table_stats(ownname => '''||owner||''', tabname => ''SGA_W_PSMS_SUBSCRIBER'');;'||CHR(10)||
'PROMPT DBMS_STATS.gather_index_stats(ownname => '''||owner||''', indname => ''SSU_PSMS_IMSI_IDX'');;'||CHR(10)||
'PROMPT DBMS_STATS.gather_index_stats(ownname => '''||owner||''', indname => ''SSU_PSMS_MSISDN_IDX'');;'||CHR(10)||
'PROMPT DBMS_STATS.gather_index_stats(ownname => '''||owner||''', indname => ''CURRENT_COUNTRY_INX'');;'||CHR(10)||
'PROMPT DBMS_STATS.lock_table_stats(ownname => '''||owner||''', tabname => ''SGA_W_PSMS_SUBSCRIBER'');;'||CHR(10)||
'PROMPT END;;'||CHR(10)||
'PROMPT /'||CHR(10)||
'PROMPT PROMPT Finished Gather Table Stats'||CHR(10)||
'PROMPT PROMPT SUCCESS!!! Finished Working on '||owner||CHR(10)||
'PROMPT EXIT;;'
FROM DBA_SEGMENTS
WHERE segment_type = 'TABLE'
AND segment_name = 'SGA_W_PSMS_SUBSCRIBER'
AND owner LIKE '%USER_A%'
AND ROUND(bytes/1024/1024) > 0
ORDER BY ROUND(bytes/1024/1024) ASC;

PROMPT EXIT;;
spool off

@gen_gen.sql

gen_gen.sql
SPOOL USER_A_REBUILD.sql
PROMPT PROMPT START WORKING ON USER_A
PROMPT ALTER TABLE USER_A.SGA_W_PSMS_SUBSCRIBER ENABLE ROW MOVEMENT ;;
PROMPT ALTER TABLE USER_A.SGA_W_PSMS_SUBSCRIBER MOVE;;
PROMPT PROMPT Finished Rebuild Table
PROMPT ALTER INDEX USER_A.SSU_PSMS_MSISDN_IDX REBUILD ONLINE;;
PROMPT ALTER INDEX USER_A.SSU_PSMS_IMSI_IDX REBUILD ONLINE;;
PROMPT ALTER INDEX USER_A.CURRENT_COUNTRY_INX REBUILD ONLINE;;
PROMPT PROMPT Finished Rebuild Indexes
PROMPT BEGIN
PROMPT DBMS_STATS.unlock_table_stats(ownname => 'USER_A', tabname => 'SGA_W_PSMS_SUBSCRIBER');;
PROMPT DBMS_STATS.gather_table_stats(ownname => 'USER_A', tabname => 'SGA_W_PSMS_SUBSCRIBER');;
PROMPT DBMS_STATS.gather_index_stats(ownname => 'USER_A', indname => 'SSU_PSMS_IMSI_IDX');;
PROMPT DBMS_STATS.gather_index_stats(ownname => 'USER_A', indname => 'SSU_PSMS_MSISDN_IDX');;
PROMPT DBMS_STATS.gather_index_stats(ownname => 'USER_A', indname => 'CURRENT_COUNTRY_INX');;
PROMPT DBMS_STATS.lock_table_stats(ownname => 'USER_A', tabname => 'SGA_W_PSMS_SUBSCRIBER');;
PROMPT END;;
PROMPT /
PROMPT PROMPT Finished Gather Table Stats
PROMPT PROMPT SUCCESS!!! Finished Working on USER_A
PROMPT EXIT;;

USER_A_REBUILD.sql

PROMPT START WORKING ON USER_A

ALTER TABLE USER_A.SGA_W_PSMS_SUBSCRIBER ENABLE ROW MOVEMENT ;

ALTER TABLE USER_A.SGA_W_PSMS_SUBSCRIBER MOVE;

PROMPT Finished Rebuild Table

ALTER INDEX USER_A.SSU_PSMS_MSISDN_IDX REBUILD ONLINE;

ALTER INDEX USER_A.SSU_PSMS_IMSI_IDX REBUILD ONLINE;

ALTER INDEX USER_A.CURRENT_COUNTRY_INX REBUILD ONLINE;

PROMPT Finished Rebuild Indexes

BEGIN

DBMS_STATS.unlock_table_stats(ownname => 'USER_A', tabname => 'SGA_W_PSMS_SUBSCRIBER');

DBMS_STATS.gather_table_stats(ownname => 'USER_A', tabname => 'SGA_W_PSMS_SUBSCRIBER');

DBMS_STATS.gather_index_stats(ownname => 'USER_A', indname => 'SSU_PSMS_IMSI_IDX');

DBMS_STATS.gather_index_stats(ownname => 'USER_A', indname => 'SSU_PSMS_MSISDN_IDX');

DBMS_STATS.gather_index_stats(ownname => 'USER_A', indname => 'CURRENT_COUNTRY_INX');

DBMS_STATS.lock_table_stats(ownname => 'USER_A', tabname => 'SGA_W_PSMS_SUBSCRIBER');

END;

PROMPT Finished Gather Table Stats

PROMPT SUCCESS!!! Finished Working on USER_A

EXIT;

Pages

Sunday, February 17, 2019

No space on device, under oracle trc files directory

Thursday, February 14, 2019

Code Generation Example sql