Database character set in oracle

The database character set in oracle determines the set of characters can be stored in the database. It is also used to determine the character set to be used for object identifiers.

The database character set information is stored in the data dictionary tables named SYS.PROPS$.

You can get the character set used in the database by SYS.PROPS$ table or any other views (like database_properties/ nls_database_parameters) exist in the database. The parameter NLS_CHARACTERSET value contains the database character set name. Get it from,

SQL> select value$ from sys.props$ where name=’NLS_CHARACTERSET’;


SQL> select property_value from database_properties where property_name=


SQL> select value from nls_database_parameters where parameter=’NLS_CHARACTERSET’;


Verify Database Creation Wordsize

Has the database been created originally in a 32-bit environment and is now on a 64-bit platform?

select decode(instr(metadata,’B023′),0, ’64bit Database’,’32bit Database’) “DB Creation”
from kopm$;

• KOPM$.METADATA contains ‘B023’ created in 32-bit env
• KOPM$.METADATA contains ‘B047’ created in 64-bit env

Flashback technologies

There are three distinct Flashback technologies available:

  • Flashback Database
    • Like pressing the rewind button on the entire database
    • Requires archivelog mode and the use of ALTER DATABASE OPEN RESETLOGS
    • Basic Steps:
      • shutdown;
      • mount;
      • flashback statement to a time, SCN or log switch sequence number;
      • open with resetlogs;
  • Flashback Query
    • Lets you query the database as it was in some point in the past, either for one select statement or by taking your whole session temporarily back in time.
    • Relies entirely on the use of UNDO segments
      • Flashback Versions makes it possible to select all versions of a row over a period of time, to show a history of what has happened to the row, when it happened, who did it, etc.
      • Flashback Transaction uses the Flashback Versions Query to identify which transaction caused the problem and uses SQL statements to undo the mistakes.
      • Flashback Table allows you to reverse all changes made to a table while leaving other tables intact.
  • Flashback Drop
    • Applies only to dropped tables.
    • When a table is dropped it is actually renamed to a system-generated name and not dropped until later if the space is needed by Oracle.
    • A Flashback Drop simply restores the name back to the table.
    • Can ONLY be used for objects removed with the DROP command (not truncate).
    • All associated indexes and permissions will also be restored.

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

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
(select tablespace_name, sum(maxbytes)-sum(bytes) autoextensible_bytes
from dba_data_files
group by tablespace_name) d
S.NEXT_EXTENT > ( F.MAX_FREE_BYTES + d.autoextensible_bytes )

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

from dba_indexes I, DBA_SEGMENTS S
AND I.max_extents < 100000

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.

Do you need to put tablespaces hot backup mode when using RMAN

To understand why RMAN does not require extra logging or backup mode, you must first understand why those features are required for non-RMAN online backups.

A non-RMAN online backup consists of a non-Oracle tool, such as cp or dd, backing up a datafile at the same time that DBWR is updating the file. Oracle can’t prevent the tool from reading a particular block at the exact same time that DBWR is updating that block. When that happens, the non-Oracle tool might read a block in a half-updated state, so that the block which is copied to the backup media might only have been updated in its first half, while the second half contains older data. This is called a “fractured block”. If this backup needs to be restored later, and that block needs to be recovered, recovery will fail because that block is not usable.

The ‘alter tablespace begin backup’ command is oracle solution for the fractured block problem. When a tablespace is in backup mode, and a change is made to a data block, instead of logging just the changed bytes to the redo log, oracle also log a copy of the entire block image before the change, so that it can reconstruct this block if media recovery finds that this block was fractured. That block image logging is what causes extra redo to be generated while files are in backup mode.

The reason that RMAN does not require extra logging is that it guarantees that it will never back up a fractured block. it can make that guarantee because we know the format of Oracle data blocks, and it verify that each block that it read is complete before it copy it to the backup. If it read a fractured block, it will read the block again to obtain a complete block before backing it up. non-Oracle tools are not able to do the same thing because they do not know how to verify the contents of an Oracle data block.

Backup mode has another effect, which is to ‘freeze’ the checkpoint in the header of the file until the file is removed from backup mode. Oracle does this because it cannot guarantee that the third-party backup tool will copy the file header prior to copying the data blocks. RMAN does not need to freeze the file header checkpoint because it knows the order in which it will read the blocks, which enables it to capture a known good checkpoint for the file.