• About Me

    Maxwell Miranda
    Oracle DBA Consultant.
  • Category

  • Archives

  • My Blog Calendar

    March 2010
    M T W T F S S
    « Feb   Apr »
  • 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 - 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

11g RMAN: Archive log deletion policy

RMAN> configure archivelog deletion policy to backed up 2 times to sbt;

new RMAN configuration parameters:
new RMAN configuration parameters are successfully stored

The preceding configure command specifies that once an archived redo log has been backed up twice to tape, it’s eligible for deletion from all archived redo log locations, including the flash recovery area. The configuration of an archived redo log deletion policy is a new feature introduced in the Oracle Database 11g release.


11g RMAN Compression algorithms

When using the RMAN compression feature, you can choose among different compression algorithms. You can query the view V$RMAN_COMPRESSION_ALGORITHM to view the compression algorithms available to you:

SQL> select algorithm_name,algorithm_description, is_default from v$rman_compression_algorithm;
—————————————————————- —
ZLIB fast but little worse compression ratio YES
BZIP2 good compression ratio but little slower NO

As the query shows, the ZLIB compression algorithm offers speed but not a good compression ratio. The alternate compression algorithm, BZIP2, is slower but provides a better compression ratio. You can use the show command to check the current compression algorithm in use:

RMAN> show compression algorithm;
RMAN configuration parameters are:
■Note If you set the compatible initialization parameter to 11.1 or newer, ZLIB will be the default compression algorithm. You can’t choose the compression algorithm if you set the compatible parameter to anything older than 11.0.

Remember that the choice of the compression algorithm is available only in Oracle Database 11g. In earlier versions of the database, you only have a single choice—the default algorithm BZIP2.

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.