ORA-16038: log 1 sequence# cannot be archived

SQL> startup; ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2146112 bytes
Variable Size 322961600 bytes
Database Buffers 205520896 bytes
Redo Buffers 3833856 bytes
Database mounted.
ORA-16038: log 1 sequence# 187 cannot be archived
ORA-19502: write error on file "", block number (block size=)
ORA-00312: online log 1 thread 1: '/opt/app/oracle/datafile/vihaan/redo01.log'


clear some space in mount point

like find . -atime +2 -exec rm {} \;

Quotes in Strings ----- Oracle Q-quote

select q'[dfdsfhgdaskghfkdskhdasfhfkjhasfsaf]' from dual;


Q'[DFDSFHGDASKGHFKDSKHDASFHFKJHASF
----------------------------------
dfdsfhgdaskghfkdskhdasfhfkjhasfsaf




SQL> select q'[What ever stuff you'd like to see, just as you'd like to see it.]' string from dual;

STRING
----------------------------------------------------------------
What ever stuff you'd like to see, just as you'd like to see it.



select q'[dfdsfhgda'fdhf'hdjasdkjhsakd'klsadjlsd'''ksjadljd''']' from dual
2 /

Q'[DFDSFHGDA'FDHF'HDJASDKJHSAKD'KLSADJLSD'''KSJADLJD
----------------------------------------------------
dfdsfhgda'fdhf'hdjasdkjhsakd'klsadjlsd'''ksjadljd'''

ASM diagnostics Report

ASM diagnostics Report
# From Metalink Note 470211.1

SPOOL ASM_FIRST.HTML
SET MARKUP HTML ON
SET ECHO ON
SET PAGESIZE 200
SELECT * FROM V$ASM_DISKGROUP;
SELECT * FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;
SELECT * FROM V$ASM_OPERATION;
SELECT * FROM V$VERSION;
SHOW PARAMETER ASM
SHOW PARAMETER CLUSTER
SHOW PARAMETER INSTANCE_TYPE
SHOW PARAMETER INSTANCE_NAME
SHOW PARAMETER SPFILE
SPOOL OFF

User has no SELECT privilege on V$SESSION v_$sql_plan v_$sql

if you are getting this error from scott



SQL> select * from table(dbms_xplan.display_cursor(null, null, 'OUTLINE'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'OUTLINE'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
User has no SELECT privilege on V$SQL_PLAN


then grant following permission to scott



SQL> GRANT SELECT ON v_$session TO scott;

Grant succeeded.

SQL> grant select on v_$sql_plan_statistics_all to scott;

Grant succeeded.

SQL> grant select on v_$sql_plan to scott;

Grant succeeded.

SQL> grant select on v_$sql to scott;

Grant succeeded.

Oracle Table fragmentation

declare

space_used number ;
space_allocated number ;
chain_pcent number;

begin
for i in (select table_name, owner from dba_tables
where owner='ANUJ'
and table_name not in (select table_name from sys.dba_external_tables
where owner='ANUJ'))

LOOP
DBMS_SPACE.OBJECT_SPACE_USAGE(i.owner,i.table_name,'TABLE','0',space_used,space_allocated,CHAIN_PCENT,'');
dbms_output.put_line(i.table_name|| ',' || round(space_used/1024)||',' || ROUND(space_allocated/1024)||',' || ROUND((1-(space_used/space_allocated))*100));

end loop;
END;

====

to remove fragmentation in table



SQL> create table ANUJ as select * from dba_tables;

Table created.

SQL>
SQL>
SQL>
SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
from user_tables where table_name='ANUJ'; 2

Ever Used Never Used Total rows
---------- ---------- ----------


SQL> analyze table test compute statistics;

Table analyzed.

SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows" from user_tables where table_name='ANUJ';

Ever Used Never Used Total rows
---------- ---------- ----------
68 4 2003

SQL> delete from test where owner='SYS';

722 rows deleted.

SQL> commit;

Commit complete.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows" from user_tables where table_name='ANUJ';

Ever Used Never Used Total rows
---------- ---------- ----------
68 4 1281

SQL> select count(*) from test;

COUNT(*)
----------
1281

SQL> alter table test enable row movement;

Table altered.

SQL> alter table test shrink space compact;

Table altered.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows" from user_tables where table_name='ANUJ';

Ever Used Never Used Total rows
---------- ---------- ----------
68 4 1281


SQL> alter table ANUJ shrink space
2 /

Table altered.

SQL> analyze table ANUJ compute statistics;

Table analyzed.

SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows" from user_tables where table_name='ANUJ';

Ever Used Never Used Total rows
---------- ---------- ----------
40 8 1281



script to find fragmentation In table

col wastedspace format a10
col blocksize format a10
col avgsize format a10
select table_name,round((blocks*8),2)||' kb' "blocksize",
round((num_rows*avg_row_len/1024),2)||' kb' "avgsize",
round((blocks*8),2) - round((num_rows*avg_row_len/1024),2) ||' kb' "wastedspace"
from user_tables
-- where table_name = 'ANUJ';

oracle trace file gzip

# Gzips Oracle Trace Files that are older than 5 days


/usr/bin/find /opt/oracle/admin/cccdb/udump /opt/oracle/admin/cccdb/bdump -name '*.trc' -mtime +5 -exec /usr/bin/gzip -9 {} \;

Put a line No in taxt file

awk '{print NR $0}' anuj.txt >anujline.txt