Search This Blog

Monday, January 30, 2012

Oracle 11g RMAN Validate command.

The validate command can be used to detect corrupt blocks and missing files,
also to determine whether backup set can be restored.

The validate command can check for physical and logical corruptions in database files.

The following command will validate and check all datafiles along with archivelogs,
current controlfile and spfile for any kind of corruption.

Read more >>

Oracle Database 11g Workload Replay.

Database replay feature can be used to capture workload from the 
production system, and replay it on a test system, with exact
timing and concurrency.

Its like you can emulate the transaction workload on the test system
as it was happening on the production system.

Main benefit of this is that you can test new settings on 
the test system before implementing them on the production system.
Read more >>

Oracle 11g Health Checks.

Oracle 11g now has a feature called health monitor which can be used 
to run diagnostic checks on the database.

Health check can be run in two ways.

1) Automatic - The fault diagnosable infrastructure runs the checks
               when a critical error is reported.

2) Manual - Can be done manually either through enterprise manager
            or through DBMS_HM PL/SQL package.
Read more >>

Oracle 11g Data Recovery Advisor.

The data recovery advisor automatically detects corruption or loss of
data on disk and also provides advisory and execution functions for
repair or recovery of lost data.

Example: I lost my Users Tablespace datafile for some reason.

Read more >>

Tuesday, January 24, 2012

Oracle 10g Using RMAN to Roll Forward a Physical Standby Database.

On Standby

SQL>alter database recover managed standby database cancel;
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     485739
Read more >>

Oracle 11gR2 Table level compression.

Earlier in oracle 10g we had compression at the table level but it
was only for bulk load operations.

But with Oracle 11gR2 compression is also available at the OLTP
level.
Read more >>

Oracle Segment Advisor 10g.

Segment advisor which is available from oracle 10g onwards helps
in detecting segments that have space issues.

For example if a table had lots of rows earlier but now it has few
rows then it may be hording up space because of its high water mark.

Segment advisor helps in finding following
types of segments.
Read more >>

Oracle Database Row Chaining Solution.

Move the affected table

1) alter table hr.employees move;

Rebuild the indexes on that table because they will become 
invalid because of move operation.
Read more >>

Oracle 10g Free up Unused Table Space.

The following operations will shrink the table and reset
the high water mark.
Read more >>

Excessive Redo Generation During Begin Backup Mode.

Excessive Redo Generation During Hot/Online backup.

During hot backup there is no excessive redo generation,
only some additional information is generated.

The process is something like following.

1) Before backup, tablespace or database is put
   into begin backup mode. 
Read more >>

Oracle 10g Enable Read Write On Physical Standby Database.

On Standby

Enable Flashback Database.

SQL> show parameter db_recovery

NAME                       TYPE        VALUE
----------------------     ----------- -----------------------------------
db_recovery_file_dest      string      /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 2G
Read more >>

Oracle Database Manual Backup and Recovery.

COLD BACKUP AND RECOVERY SCENARIOS.

Cold backup of Database in Noarchivelog Mode.

1) analyze the space requirements.


SQL> select sum(sum_bytes)/1024/11024 m_bytes
  2  from (
  3  select sum(bytes) sum_bytes from v$datafile
  4  union
  5  select sum(bytes) sum_bytes from v$tempfile
  6  union
  7  select (sum(bytes) * members) sum_bytes from v$log
  8  group by members);
Read more >>

Monday, January 23, 2012

Wednesday, January 18, 2012

Oracle Database User Management.

USER CREATION AND DELETION.

SQL> create user tom identified by jerry
  2  default tablespace users
  3  temporary tablespace temp 
  4  quota 20m on users;

User created.
Read more >>

Oracle Fuzzy Bits.

If there are FUZZY BITS in the datafile header then that means there
may have been writes on the datafile after the last checkpoint.

So, whenever a datafile is opened it is checked for fuzzy bits.
If it is fuzzy then the database checks the checkpoint_change$
column to search the SCN from which the redo logs are to be applied.
Read more >>

Opening a Standby Database For Read Only Access.

Open a standby database for read-only access when
it is currently performing redo apply.

SQL> alter database recover managed standby database cancel;

Database altered.
Read more >>

Gap Detection on Standby Database.

If the primary and standby databases are configured with

FAL_SERVER and FAL_CLIENT

parameters then gaps should not occur.

But still if the gaps occur do the following.


On the standby database.

SQL>  select * from v$archive_gap;

THREAD#   LOW_SEQUENCE#   HIGH_SEQUENCE#
-------   --------------  ---------------
      1               30               40
Read more >>

Oracle 10g Dataguard Failover.

On standby database.

Defer the remote archival destination, since we are not going to
use it.

SQL> alter system set log_archive_dest_state_2 = 'DEFER';

System altered.


Before performing going any furthur transfer all the unapplied
redo data from the primary to standby to reduce data loss.
Read more >>

Oracle 10g Dataguard Switchover.

This article is based on the dataguard tutorial written earlier.

Configure the follwoing parameters on both the databases.

SQL> show parameter fal_server
Read more >>

Friday, January 13, 2012

Oracle Database Online Redo Log Files Management.

Lets check the online redo log configuration. 


SQL> select group#, thread#, sequence#, bytes, members, archived,
 status from v$log;
Read more >>

Thursday, January 12, 2012

A Brief Introduction to Materialized Views.

The Following tutorial is prepared on Oracle Database 11gR2.

Materialized views facilitate you to execute a SQl query and 
save its results either locally or in a remote database.

After the Materialized view is initially populated it can be
refreshed later on to store fresh results into the underlying 
table.
Read more >>

Wednesday, January 11, 2012

Oracle Database Undo Tablespace Mangement.

Creating undo tablespace.

SQL> create undo tablespace undotbs2 
  2  datafile '/u01/app/oracle/oradata/orcl
     /undotbs201.dbf' size 200m
  3  autoextend on 
  4  extent management local;
Read more >>

Oracle Database Temporary Tablespace Management.

To check for all the default tablespaces in your database 
execute the following query.

SQL> column property_name format a30
SQL> column property_value format a30
SQL> column description format a50
Read more >>

Tuesday, January 10, 2012

Oracle 11g Feature Read Only Tables.

In oracle database 11g you can alter individual tables
to be read only. When in read-only mode, you can’t
issue any DML statements against the table. 

SQL>alter table emp read only;

Table altered.
Read more >>

Sunday, January 8, 2012

Oracle 10g Dataguard Physical Standby.

This tutorial is for configuring
physical standby database.


The dataguard type which we are configuring
here is "Maximum Performance Mode".

Primary Machine IP: 192.168.1.160

Database name:orcl
Read more >>

Monday, January 2, 2012

Oracle 11gR2 Compression feature.

In Oracle 10g we had basic compression in RMAN.
But Oracle 11gR2 has added some more compression features.

There are basically four compression levels.

HIGH   = unmodified BZIP2
MEDIUM = ZLIB
LOW    = LZO
BASIC  = BZIP2
Read more >>

Oracle 11g Feature Multisection Backup.

With this new feature RMAN backups can be split 
into multiple pieces.

Suppose you want to backup your system tablespace and spread the 
backups to three different backup locations.
You can do so through the following query.
Read more >>