• About Me

    Maxwell Miranda
    Oracle DBA Consultant.
  • Category

  • Archives

  • My Blog Calendar

    February 2010
    M T W T F S S
    « Jan   Mar »
    1234567
    891011121314
    15161718192021
    22232425262728
  • RSS Oracle Feeds

    • Can't access database after power outage
      I could access the database before the power outage. From what I've read, it is a problem with the environment variables, but they are correct. Not sure what else to add. C:\Documents and Settings\konoca>sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 21 09:17:08 2018 Copyright (c) 1982, 2005, Oracle. All rights reserved. ERR […]
    • Problems with RESET DATABASE
      Recently I wanted to do RESET DATABASE. Oracle documentation suggests the following commands connect target / startup force nomount reset database to incarnation 2 run { set until time "date time" restore controlfile from autobackup alter database mount restore database recover database } alter database open resetlogs In this view my db has given e […]
  • Advertisements

What is Oracle Instant Client?

•Instant Client allows you to run your applications
without installing the standard Oracle client or
having an ORACLE_HOME.
•While using significantly less disk space than
before.
•SQL*Plus can be used with Instant Client

Advertisements

Index information

–show index information. showDex.sql
set linesize 150
col owner for a10
col index_name for a25
col table_name for a20 hea TNAME
col table_owner for a10 hea TOWNER
col compression for a3 trunc hea CMP
col index_type for a10 trunc
col degree for a6
col ANLYZED for a7 trunc
col STATUS for a3 trunc hea STS
col ini_trans for 999 hea ITR
col max_trans for 999 hea MTR
col num_rows for 9999999 hea ROWS
col tablespace_name for a10 hea TBNAME
select owner,index_name,index_type,STATUS,INI_TRANS,MAX_TRANS,COMPRESSION,degree,TABLESPACE_NAME,num_rows,
to_char(LAST_ANALYZED, ‘DDMonYY’) ANLYZED,table_owner,table_name from DBA_INDEXES where owner in
(‘SYS’,’SYSTEM’,’OUTLN’,’DBSNMP’,’OUTLN’,’ORACLE’,’PERFSTAT’,’OPS$ORACLE’) order by 1,2;

Monitoring: database extents

1. Objects whose next extent is too big to fit in the tablespace (ORA-1653, ORA-1654, ORA-1655):

SELECT OWNER,SEGMENT_NAME, round(NEXT_EXTENT/(1024*1024),1) next_in_MB,
S.TABLESPACE_NAME,round(MAX_FREE_BYTES/(1024*1024),1) max_free_MB,
autoextensible_bytes/(1024*1024) autoextend_MB
FROM SYS.DBA_SEGMENTS S,
(SELECT TABLESPACE_NAME,MAX(BYTES) MAX_FREE_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(select tablespace_name, sum(maxbytes)-sum(bytes) autoextensible_bytes
from dba_data_files
group by tablespace_name) d
WHERE
S.NEXT_EXTENT > ( F.MAX_FREE_BYTES + d.autoextensible_bytes )
AND S.TABLESPACE_NAME=F.TABLESPACE_NAME and
f.tablespace_name=d.tablespace_name;

2. Objects that are near their MAXEXTENTS (ORA-1632):

select I.OWNER, INDEX_NAME, EXTENTS, I.MAX_EXTENTS
from dba_indexes I, DBA_SEGMENTS S
where I.OWNER=S.OWNER AND I.INDEX_NAME=S.SEGMENT_NAME AND
S.SEGMENT_TYPE=’INDEX’
AND I.max_extents < 100000
AND I.MAX_EXTENTS – S.EXTENTS < 10;

RMAN – When do you require DBID for database recovery

dbid integer  

specifies the db identifier, which is a unique 32-bit identification number computed when the database is created. The DBID column of the V$DATABASE data dictionary view displays the identifier. The DBID is also stored in the DB table of the recovery catalog.

The set dbid command is useful for restoring the control file when each of these conditions is met:

  • The control file has been lost and must be restored from a backup.

  • You are using a recovery catalog.

  • Multiple databases registered in the recovery catalog share a database name.

  • You receive the “RMAN-20005: target database name is ambiguous” message when you attempt to restore the control file.

If these conditions are not met, RMAN will correctly identify the control file to restore, so you do not need to use the set dbid command.

RMAN accepts set dbid only if you have not yet connected to the target database, i.e., set dbid must precede the connect target command. If the target database is mounted, then RMAN verifies that the user-specified DBID matches the DBID from the database; it not, RMAN signals an error. If the target database is not mounted, RMAN uses the user-specified DBID to restore the control file. Once you have restored the control file, you can mount the database to restore the rest of the database.

Different Types of Shells to Declare

#!/usr/bin/sh OR #!/bin/sh Declares a Bourne shell
#!/usr/bin/ksh OR #!/bin/ksh Declares a Korn shell
#!/usr/bin/csh OR #!/bin/csh Declares a C shell
#!/usr/bin/bash OR #!/bin/bash Declares a Bourne-Again shell