Rman Recovery Scenarios

Rman recovery scenarios require that the database
is in archive log mode, and that backups of datafiles,
control files and archived redolog files are made using
Rman. Incremental Rman backups may be used also.

Rman can be used with the repository installed on the
archivelog, or with a recovery catalog that may be
installed in the same or other database.

Configuration and operation recommendations:

Set the parameter controlfile autobackup to ON
to have with each backup a controlfile backup also:

rman>configure controlfile autobackup on;

Set the parameter retention policy to the recovery
window you want to have, i.e redundancy 2 will keep
the last two backups available, after executing
delete obsolete commands:

rman>configure retention policy to redundancy 2;

Execute your full backups with the option
'plus archivelogs' to include your
archivelogs with every backup:

rman>backup database plus archivelog;

Perform daily maintenance routines to maintain on
your backup directory the number of backups you need only:

rman>crosscheck backup;
rman>crosscheck archivelog all;
rman>delete noprompt obsolete backup;

To work with Rman and a database based
catalog follow these steps:
1. sqlplus /
2. create tablespace repcat;
3. create user rcuser identified by rcuser default tablespace repcat
temporary tablespace temp;
4. grant connect, resource, recovery_catalog_owner to rcuser;
5. exit
6. rman catalog rcuser/rcuser # connect to rman catalog as the rcuser
7. create catalog # create the catalog
8. connect target / #
=============================================
Complete Closed Database Recovery. System tablespace is missing
---------------------------------------------
In this case complete recovery is performed, only the
system tablespace is missing, so the database can be
opened without reseting the redologs.
1. rman target /
2. startup mount;
3. restore database;
4. recover database;
5. alter database open;
=============================================
Complete Open Database Recovery. Non system tablespace
is missing,database is up
---------------------------------------------
1. rman target /
2. sql 'alter tablespace offline immediate';
3. restore datafile 3;
4. recover datafile 3;
5. sql 'alter tablespace online';
=============================================
Complete Open Database Recovery (when the database
is initially closed). Non system tablespace is missing
---------------------------------------------
A user datafile is reported missing when trying to
startup the database. The datafile can be turned offline
and the database started up. Restore and recovery are
performed using Rman. After recovery is performed
the datafile can be turned online again.
1. sqlplus /nolog
2. connect / as sysdba
3. startup mount
4. alter database datafile '' offline;
5. alter database open;
6. exit;
7. rman target /
8. restore datafile '';
9. recover datafile '';
10. sql 'alter tablespace online';
=============================================
Recovery of a Datafile that has no backups (database is up).
---------------------------------------------
If a non system datafile that was not backed up since
the last backup is missing, recovery can be performed
if all archived logs since the creation of the missing
datafile exist. Since the database is up you can check
the tablespace name and put it offline. The option offline
immediate is used to avoid that the update of the datafile header.

Pre requisites: All relevant archived logs.
1. sqlplus '/ as sysdba'
2. alter tablespace offline immediate;
3. alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf;
4. exit
5. rman target /
6. recover tablespace ;
7. sql 'alter tablespace online';
If the create datafile command needs to be executed to place
the datafile on a location different than the original use:
alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf' as
'/user/oradata/u02/dbtst/newdata01.dbf'
=============================================
Restore and Recovery of a Datafile to a different location. Database is up.
---------------------------------------------
If a non system datafile is missing and its original
location not available, restore can be made to a different
location and recovery performed.

Pre requisites: All relevant archived logs, complete cold or hot backup.
1. Use OS commands to restore the missing or corrupted datafile to the new
location, ie:
cp -p /user/backup/uman/user01.dbf /user/oradata/u02/dbtst/user01.dbf
2. alter tablespace offline immediate;
3. alter tablespace rename datafile
'/user/oradata/u01/dbtst/user01.dbf' to '/user/oradata/u02/dbtst/user01.dbf';
4. rman target /
5. recover tablespace ;
6. sql 'alter tablespace online';
=============================================
Control File Recovery
---------------------------------------------
Always multiplex your controlfiles. If you loose only one
controlfile you can replace it with the one you have in place,
and startup the Database. If both controlfiles are missing,
the database will crash.

Pre requisites: A backup of your controlfile and all relevant
archived logs. When using Rman alway set configuration parameter
autobackup of controlfile to ON. You will need the dbid to
restore the controlfile, get it from the name of the backed up
controlfile. It is the number following the 'c-' at the start of the name.
1. rman target /
2. set dbid
3. startup nomount;
4. restore controlfile from autobackup;
5. alter database mount;
6. recover database;
7. alter database open resetlogs;
8. make a new complete backup, as the database is open in a new incarnation and
previous archived log are not relevant.
=============================================
Incomplete Recovery, Until Time/Sequence/Cancel
---------------------------------------------
Incomplete recovery may be necessaire when the database
crash and needs to be recovered, and in the recovery process
you find that an archived log is missing. In this case recovery
can only be made until the sequence before the one that is missing.

Another scenario for incomplete recovery occurs when an important
object was dropped or incorrect data was committed on it.
In this case recovery needs to be performed until before the
object was dropped.

Pre requisites: A full closed or open database backup and archived
logs, the time orsequence that the 'until' recovery
needs to be performed.
1. If the database is open, shutdown it to perform full restore.
2. rman target \
3. startup mount;
4. restore database;
5. recover database until sequence 8 thread 1; # you must pass the thread, if a
single instance will always be 1.
6. alter database open resetlogs;
7. make a new complete backup, as the database is open in a new incarnation and
previous archived log are not relevant.
Alternatively you may use instead of until sequence, until time, ie: '2008-03-
16:01:01:10'.

How to change the root password of a linux server.

Use this information at your own risk,

Please note that you cannot perform this remotely,
you must be physically at the machine.

1. The first step is to reboot server.

2. Since the server I inherit has GRUB, wait until
the grub splash screen comes on and hit the letter 'e'
on your keyboard. this will stop grub.

3. hit 'e' again and this will take you to another screen

4. you should see one of the choices has "kernel"
select the one with "kernel

5. hit 'e' on your keyboard

6. type ' single' or 1 (make sure to include a space before "single/1")
and hit enter. then enter 'b' to boot.

7. your system should boot up in single mode as root

8. now that you are logged in single mode you can
change the root password by sending this command:
-->> passwd
Give the new password

9. after you have changed the root password, reboot server
and you should be able to login with the new password.

DONE

LILO ----------------------------

1. If you have LILO, its different, first reboot server and when you
see the LILO: prompt type "linux single" and hit enter.
this will log you in as root in single user mode.

2. Once booting is complete, you can change your password with this command:
-->> passwd
Give the new password
and reboot server.

DONE

Usage of Split command in Linux.

Split, have a large file that you need to split into smaller chucks? A Oracle dump maybe? split is your command. Below I split a 110MB file into 30 megabyte chunks.

Split works just fine on both text, and binary (even compressed) files. An example is worth a thousand words of man page (that don't have examples). Here I have TBL_LOSANGELES_CA.dmp, a 109MB Oracle export dump file.

[root@oracle11gr1 ~]# mkdir split
[root@oracle11gr1 ~]# mv /share/TBL_LOSANGELES_CA.dmp split/
[root@oracle11gr1 ~]# cd split/
[root@oracle11gr1 split]# ll
total 111284
-rwxrw-r-- 1 vshare vshare 113836032 Oct 11 22:43 TBL_LOSANGELES_CA.dmp

[root@oracle11gr1 split]#split -b 30m TBL_LOSANGELES_CA.dmp TBL_LOSANGELES_CA_part_
[root@oracle11gr1 split]# ll
total 222584
-rwxrw-r-- 1 vshare vshare 113836032 Oct 11 22:43 TBL_LOSANGELES_CA.dmp
-rw-r--r-- 1 root root 31457280 Mar 14 14:07 TBL_LOSANGELES_CA_part_aa
-rw-r--r-- 1 root root 31457280 Mar 14 14:07 TBL_LOSANGELES_CA_part_ab
-rw-r--r-- 1 root root 31457280 Mar 14 14:07 TBL_LOSANGELES_CA_part_ac
-rw-r--r-- 1 root root 19464192 Mar 14 14:07 TBL_LOSANGELES_CA_part_ad

Cat command is used to join the files split by above split command.

[root@oracle11gr1 split]#cat TBL_LOSANGELES_CA_part_aa TBL_LOSANGELES_CA_part_ab TBL_LOSANGELES_CA_part_ac TBL_LOSANGELES_CA_part_ad >TBL_LOSANGELES

[root@oracle11gr1 split]# ls -lh
total 327M
-rw-r--r-- 1 root root 109M Mar 14 14:15 TBL_LOSANGELES
-rwxrw-r-- 1 vshare vshare 109M Oct 11 22:43 TBL_LOSANGELES_CA.dmp
-rw-r--r-- 1 root root 30M Mar 14 14:07 TBL_LOSANGELES_CA_part_aa
-rw-r--r-- 1 root root 30M Mar 14 14:07 TBL_LOSANGELES_CA_part_ab
-rw-r--r-- 1 root root 30M Mar 14 14:07 TBL_LOSANGELES_CA_part_ac
-rw-r--r-- 1 root root 19M Mar 14 14:07 TBL_LOSANGELES_CA_part_ad

You can check whether the files are identical are not by using the DIFF command.

[root@oracle11gr1 split]# diff -s TBL_LOSANGELES TBL_LOSANGELES_CA.dmp
Files TBL_LOSANGELES and TBL_LOSANGELES_CA.dmp are identical
[root@oracle11gr1 split]#

The cat command can be broken into parts (this is useful if each part is on a separate disk, like one each on CDs). Take note that the first time, a single > is used to make sure that the data from this first part overwrites the destination file (if it already exists), but that each time after that, a double > is used to append to the destination file.

cat TBL_LOSANGELES_CA_part_aa > TBL_LOSANGELES
cat TBL_LOSANGELES_CA_part_ab >> TBL_LOSANGELES
cat TBL_LOSANGELES_CA_part_ac >> TBL_LOSANGELES
cat TBL_LOSANGELES_CA_part_ad >> TBL_LOSANGELES

RMAN - Recovery Manager for Oracle Database 10g

WHAT IS RMAN ?

Recovery Manager is a tool that: manages the process of creating backups and also manages the process of restoring and recovering from them.

WHY USE RMAN ?

No extra costs …Its available for free
RMAN introduced in Oracle 8 it has become simpler with
newer versions and easier than user managed backups Proper security
You are 100% sure your database has been backed up.
Its contains detail of the backups taken etc in its central repository
Facility for testing validity of backups also commands like crosscheck to Check the status of backup.
Faster backups and restores compared to backups without RMAN
RMAN is the only backup tool which supports incremental backups.
Oracle 10g has got further optimized incremental backup which has
resulted in improvement of performance during backup and recovery time
Parallel operations are supported
Better querying facility for knowing different details of backup
No extra redo generated when backup is taken..compared to online backup
without RMAN which results in saving of space in hard disk
RMAN is an intelligent tool
Maintains repository of backup metadata
Remembers backup set location
Knows what need to backed up
Knows what is required for recovery
Knows what backup are redundant

UNDERSTANDING THE RMAN ARCHITECTURE

An oracle RMAN comprises of RMAN EXECUTABLE
This could be present and fired even through
client side TARGET DATABASE.
(Target)This is the database which needs to be backed up
RECOVERY CATALOG is optional otherwise backup
details are stored in target database controlfile
It is a repository of information queried and updated by Recovery Manager
It is a schema or user stored in Oracle database
One schema can support many databases
It contains information about physical schema of target database datafile and archive log, backup sets and pieces

Recovery catalog is a must in following scenarios
In order to store scripts
For tablespace point in time recovery

Media Management Software

Media Management software is a must if you are using
RMAN for storing backup in tape drive directly.

Backups in RMAN

Oracle backups in RMAN are of the following type

RMAN complete backup OR RMAN incremental backup

These backups are of RMAN proprietary nature

IMAGE COPY

Its again a kind of backup. The advantage of uing Image copy is its not in RMAN proprietary format.

Backup Format

RMAN backup is not in oracle format but in RMAN format.
Oracle backup comprises of backup sets and it consists
of backup pieces. Backup sets are logical entity.
In oracle 9i it gets stored in a default location.

There are two type of backup sets
Datafile backup sets,
Archivelog backup sets

One more important point of data file backup sets
is it do not include empty blocks. A backup set
would contain many backup pieces. A single backup piece
consists of physical files which are in RMAN proprietary format.

You can go to RMAN prompt by just typing rman.
RMAN executable is present in ORACLE_HOME/bin location.

bash-2.05$ rman

Recovery Manager: Release 10.1.0.5.0 - Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.


RMAN>

You can use target connect to connect to database.
The database it will connect to depends on the environment variable ORACLE_HOME.

RMAN> connect target

connected to target database: ORCL (DBID=1176834246)

Alternatively you can use “rman TARGET SYS/sys@test NOCATALOG”
to connect to the RMAN of “test” instance.

Here we will be using target database control file
to store all the information required for RMAN,
like backupsets and backup image information etc.

Backup Database:

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 1660944384 bytes

Fixed Size 1322112 bytes
Variable Size 686019456 bytes
Database Buffers 973078528 bytes
Redo Buffers 524288 bytes

RMAN> backup database;

Starting backup at 13-MAR-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=166 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-MAR-08
channel ORA_DISK_1: finished piece 1 at 13-MAR-08
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2008_03_13/

o1_mf_nnndf_TAG20080313T031455_3b8zv57d_.bkp tag=TAG20080313T031455 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 13-MAR-08
channel ORA_DISK_1: finished piece 1 at 13-MAR-08
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2008_03_13/

o1_mf_ncsnf_TAG20080313T031455_3b8zy7xr_.bkp tag=TAG20080313T031455 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
Finished backup at 13-MAR-08
Creating RMAN Catalog

You can also create a repository for RMAN to store all this
information. Repository will be just another small
database which can store the catalog information.
Creating a catalog is a 3 step process

1) Create database which will hold the catalog.
Else you can use the existing database also.
All you need is to create a seperate tablespace
for holding the information about RMAN catalog.

2) Create RMAN tablespace and RMAN user

SQL> create tablespace rman_tbs datafile ‘/u01/app/oracle/oradata/TEST/rman01.dbf’ size 240M EXTENT MANAGEMENT LOCAL segment SPACE MANAGEMENT AUTO ;

Tablespace created.

SQL> create user rcat identified by rcat
default tablespace rman_tbs
TEMPORARY TABLESPACE TEMPTS1
QUOTA UNLIMITED ON rman_tbs account unlock;

User created.

After creating user, you need to grant RECOVERY_CATALOG_OWNER role to that user.

3) Create RMAN catalog.

bash-2.05$ rman catalog rcat/rcat@test

Recovery Manager: Release 10.1.0.5.0 - Production on Thu Mar 13 02:47:16 2008

Copyright (c) 1995, 2004, Oracle. All rights reserved.

connected to recovery catalog database

RMAN> create catalog

recovery catalog created

For registering the database, you need to get connected
to database as well as catalog at the same time. Here is how you can do.

RMAN> connect target

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

The above error is because the database we connected to is not found in the catalog database. We can register the database in catalog.

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Registering database will also resynch the information present
in the target database control file and catalog database.
Since we have taken 1 backup early, it will synchup that
information with RMAN catalog. We can check the same using
LIST BACKUP command at RMAN prompt.

RMAN> list backup;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
18 Full 573.48M DISK 00:01:26 13-MAR-08
BP Key: 20 Status: AVAILABLE Compressed: NO Tag: TAG20080313T031355
Piece Name: /u01/app/oracle/oradata/ORCL/backupset/2008_03_13/
o1_mf_nnndf_TAG20080313T031355_3b8zv57d_.bkp

List of Datafiles in backup set 18
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 Full 562487 13-MAR-08 /u01/app/oracle/oradata/ORCL/system01.dbf
2 Full 562487 13-MAR-08 /u01/app/oracle/oradata/ORCL/undotbs01.dbf
3 Full 562487 13-MAR-08 /u01/app/oracle/oradata/ORCL/sysaux01.dbf
4 Full 562487 13-MAR-08 /u01/app/oracle/oradata/ORCL/users01.dbf
5 Full 562487 13-MAR-08 /u01/app/oracle/oradata/ORCL/example01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
19 Full 6.80M DISK 00:00:05 13-MAR-08
BP Key: 21 Status: AVAILABLE Compressed: NO Tag: TAG20080313T031355
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2008_03_13/
o1_mf_ncsnf_TAG20080313T031355_3b8zy7xr_.bkp

Control File Included: Ckp SCN: 562487 Ckp time: 13-MAR-08
SPFILE Included: Modification time: 13-MAR-08

RMAN Command line

Backing up the Controlfile and Spfile

The control file can be automatically backed up after each
RMAN backup and database structure change as a way to protect
the RMAN repository (when we are not using a seperate catalog for RMAN).

RMAN> configure controlfile autobackup on;

Backing up control file

RMAN> backup current controlfile;

Creating Image copy of all datafiles in database

These are the image copies and are stored in ORACLE format
and not in RMAN format. Backupsets and backuppieces are
stored in internal RMAN format. Hence these image copies can be
used for manual restore and recovery as well.

RMAN> backup as copy database;

Creating backupsets of all datafiles in database

If you specify BACKUP AS BACKUPSET, then RMAN stores its backups
in backup sets. A backup set, consisting of one or more backup pieces,
contains the physical file data being backed up. This backupset
is written in a format that only RMAN can access. Only RMAN can
create and restore backup sets. Backup sets can be written to disk
or tape, and they are the only type of backup which RMAN can use
to write backups to tape.

RMAN> backup as backupset database;

Backup individual tablespace as backupsets

RMAN> backup tablespace system, HTMLDB;

Backup individual tablespace as image copies

RMAN> backup as copy tablespace system;

Backup individual files as image copies

RMAN> backup as copy datafile ‘/u01/app/oracle/oradata/test/system01.dbf’;

Backup individual files as Backupsets

RMAN> backup datafile ‘/u01/app/oracle/oradata/test/system01.dbf’;

Backup archivelogs

We can backup the archive logs according to the output of some
search condition. Example we want to backup only those
archivelogs which starts with “ARCH_616814159_”.

RMAN> backup acrchivelog like '%ARCH_616814159_%';

Copy archivelogs from some time stamp.

Suppose we want to copy the archivelogs of last 2 days,
then we can use the following commands.

RMAN> BACKUP ARCHIVELOG from time 'sysdate-2';

Backup all archivelog files

RMAN> Backup archivelog all;

Backup archivelog between some time.

RMAN> BACKUP ARCHIVELOG FROM TIME 'SYSDATE-30' UNTIL TIME 'SYSDATE-7';

Specifying copies while backing up.

RMAN> backup copies 2 datafile '/u01/app/oracle/oradata/test/cs_tbs01.dbf';

Remember that copies option cannot be used with image copies.
It can be used only with backupsets.

Giving tags to backups

RMAN> BACKUP TAG ‘weekly_full_db_bkup’ DATABASE MAXSETSIZE 100M;

Backing up backupsets
RMAN> BACKUP BACKUPSET ALL;

Backup imagecopies

RMAN> Backup as copy backupset all;

List Imagecopies

RMAN> list copy;

List Backupsets

RMAN> list backup;

Restoring and Recovering the database

Use the RESTORE and RECOVER commands for RMAN restore and
recovery of physical database files.

RMAN> STARTUP FORCE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;

Recovering Current Tablespaces

RMAN> SQL ‘ALTER TABLESPACE users OFFLINE’;
RESTORE TABLESPACE users;
RECOVER TABLESPACE users;
SQL ‘ALTER TABLESPACE users ONLINE;

Recovering Current Datafiles

RMAN> SQL ‘ALTER DATABASE DATAFILE 7 OFFLINE’;
RESTORE DATAFILE 7;
RECOVER DATAFILE 7;
SQL ‘ALTER DATABASE DATAFILE 7 ONLINE’;

Recovering Individual Data Blocks

RMAN can recover individual corrupted datafile blocks.
When RMAN performs a complete scan of a file for a backup,
any corrupted blocks are listed in V$DATABASE_BLOCK_CORRUPTION.
Corruption is usually reported in alert logs, trace files or
results of SQL queries. Use BLOCKRECOVER to repair all corrupted blocks:

RMAN> BLOCKRECOVER CORRUPTION LIST;

You can also recover individual blocks, as shown in this example:
RMAN> BLOCKRECOVER DATAFILE 7 BLOCK 233, 235 DATAFILE 4 BLOCK 101;

Managing RMAN repository

As you know that if you do not use a recovery catalog, then
control file of the target database is used as RMAN repository
and eventually after some time the control file records
for RMAN information will get overwritten.Set this initialization
parameter in the parameter file of the target database to
determine how long records are kept:

CONTROL_FILE_RECORD_KEEP_TIME =

You can configure a retention policy to be used by RMAN
to determine which backups are considered obsolete.
This allows you to remove files from the repository that
are no longer needed to meet your retention requirements.
This policy can be based on a recovery window (the maximum number
of days into the past for which you can recover) or redundancy
(how many copies of each backed-up file to keep).

Two Parameters are used to set retention policies.

You can specify the days days between the current time and the
earliest point of recoverability, this is called RECOVERY WINDOW.
RMAN does not consider any full or level 0 incremental backup
as obsolete if it falls within the recovery window.
Alternatively the REDUNDANCY parameter will instruct to store the
number of copies of backup in RMAN repository. If the number of
backups for a specific datafile or control file exceeds the REDUNDANCY
setting considers the extra backups as obsolete.

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

This will make the REDUNDANCY setting to 3. Meaning that it will
at max store 3 copies of backups and ikmages of datafile.
Any more images or backups are consider obsolete.

When we run the below command

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

it will configure new retention policy based on the RECOVERY WINDOW
of 7 days. This means that all the backups which falls outside this
window will be considered obsolute. So in this case you need to have
backup scheduled every week to have atleast 1 valid backup.

Remember that at any point of time, only one policy can be active.
It can be either REDUNDANCY or RECOVERY WINDOW.

When you change the retention policy to another one, it will suspend
the previous policy as shown below.

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Cross checking the available backups

RMAN> CROSSCHECK BACKUP;

Crosscheck is needed when an archivelog file or backup is manually
removed, i.e., not deleted by RMAN. This command ensures that data
about backups in the recovery catalog or control file is synchronized
with corresponding data on disk or in the media management catalog.
The CROSSCHECK command operates only on files that are recorded in
the recovery catalog or the control file.

The CROSSCHECK command does not delete any files that it is
unable to find, but updates their repository records to EXPIRED.
Then, you can run DELETE EXPIRED to remove the repository records
for all expired files as well as any existing physical files whose
records show the status EXPIRED.

If some backup pieces or copies were erroneously marked as EXPIRED,
for example, because the media manager was misconfigured, then after
ensuring that the files really do exist in the media manager, run
the CROSSCHECK BACKUP command again to restore those files to AVAILABLE
status.

RMAN> CROSSCHECK COPY;

This will validate the image copies.

Deleting the backups

The DELETE command removes RMAN backups and copies from DISK marks
the records in control file as DELETED or removes the records from the
recovery catalog (if you use a catalog).

RMAN> DELETE BACKUPSET 101, 102, 103;

RMAN> delete controlfilecopy ‘%ctl’; # Pattern search is allowed.

RMAN> delete archivelog until sequence 20;

This will delete all the archives from the oldest one till the
sequence we have specified.

RMAN> delete backup of tablespace system;

RMAN> DELETE ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE DISK;

List commands

RMAN> list backup;

RMAN> list copy;

RMAN> list incarnation of database;

RMAN> list expired backupset;

RMAN> list expired copy;

RMAN> list backup of tablespace sysaux;

RMAN> list copy of datafile 3;

Reporting in RMAN

RMAN> report need backup;

Reports which database files need to be backed up to meet a configured
or specified retention policy

RMAN> report unrecoverable;

Reports which database files require backup because they have been
affected by some NOLOGGING operation such as a direct-path insert

RMAN> report need backup recovery window of 7 days;

Displays objects requiring backup to satisfy a recovery window-based
retention policy.

Suppose in the above command we want to skip a perticular tablespace
like perfstat then we can use the below command.

RMAN> report need backup recovery window of 2 days database skip tablespace perfstat;

RMAN> report need backup redundancy 3;

Displays objects requiring backup to satisfy a redundancy-based
retention policy.

RMAN> report need backup days 7;

Displays files that require more than n days’ worth of archived
redo log files for recovery.

RMAN> report need backup incremental 7;

Displays files that require application of more than n incremental
backups for recovery.

RMAN> report need backup redundancy 2 datafile 3;

Gives report of files with less than 2 redundant backups.

RMAN> report need backup tablespace htmldb;

Report of files that must be backed up to satisfy current
retention policy for this tablespace (htmldb)

RMAN> report need backup incremental 2;

Report of files that need more than 2 incrementals during recovery

RMAN> report need backup device type disk;

Report of files that must be backed up to satisfy current
retention policy for database

RMAN> report obsolete;
Shows backups that are obsolete according to the current
retention policy.

You can add the options RECOVERY WINDOW and REDUNDANCY with
this command as given below.

RMAN> report obsolete recovery window of 3 days;

RMAN> report obsolete redundancy 2;

RMAN> report schema;

This command lists and displays information about the database files.

RMAN> report schema at time 'sysdate - 14';

This command gives report on schema 14 days ago.

RMAN> report schema at SCN 10000;

This gives report on schema at scn 10000.

RMAN> report schema at sequence 55 thread 1;

Gives report of schema at sequence 55.

RMAN configuration

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128′; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/oradata/test/dbs/snapcf_test.f’; # default

We can change each of these parameters as per our requirements.

You can return any setting to its default value by using CONFIGURE… CLEAR

RMAN> CONFIGURE BACKUP OPTIMIZATION CLEAR;

RMAN> CONFIGURE RETENTION POLICY CLEAR;

By default, RMAN sends all backups to an operating system
specific directory on disk. So default setting for DEVICE
TYPE is DISK. You can configure to make backups by default
on tape or any other device as given below.

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO sbt;

You can configure backup sets or image copies as the default
for a particular device type, using either of the following commands:

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY; # Default becomes image copies
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET; # Default becomes uncompressed

You can configure RMAN to use compressed backupsets by
default on a particular device type, by using the CONFIGURE
DEVICE TYPE command with the BACKUP TYPE TO COMPRESSED BACKUPSET
option, as shown in the following examples.

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

RMAN> CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO COMPRESSED BACKUPSET;

To disable compression you can use below command

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET;

RMAN> CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO BACKUPSET;

You can configure the parallelism for a device type.

RMAM> CONFIGURE DEVICE TYPE sbt PARALLELISM 2;

You can turn on/off controlfile autobackup using

RMAN> configure controlfile autobackup off;

RMAN> configure controlfile autobackup on;

We can configure RMAN channel to write backups and images in a specific format.

The following command configures RMAN to write disk backups to the /backup directory

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/backup/ora_df%t_s%s_s%p’;

%t is replaced with a four byte time stamp,
%s with the backup set number, and
%p with the backup piece number.

You can also configure format for controlfile autobackup as well.
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE deviceSpecifier TO ’string’;

For example, you can run the following command:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘?/oradata/cf_%F’;

Here %F will be replaced by following

c-IIIIIIIIII-YYYYMMDD-QQ, where:

IIIIIIIIII stands for the DBID.
YYYYMMDD is a time stamp of the day the backup is generated
QQ is the hex sequence that starts with 00 and has a maximum of FF

You can clear the format using following command.
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;



References:

Metalink Note ID: 360416.1

http://advait.wordpress.com/2007/08/10/rman-recovery-manager-for-oracle-database-10g/

http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14192/toc.htm

Network Related Files

Oracle uses three files (listener.ora, tnsnames.ora & sqlnet.ora) for network configuration.

Listener.ora
The listerner.ora file contains server side network configuration parameters.

It can be found in the ORACLE_HOME/network/admin directory on the server.

EXAMPLE Listener.ora file:

-------------LINUX----------------
# listener.ora Network Configuration File: /u01/app/oracle/product/10.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.1.0/db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = edrserver)(PORT = 1521))
)
)
)
-------------WINDOWS----------------

# listener.ora Network Configuration File: D:\oracle\product\10.1.0\Db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.1.0\Db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Ntws205.rmsi.com)(PORT = 1521))
)
)
)
==================================================================================
After any changes/modifications in the listener.ora file
the listener should be restarted or reloaded to allow the new configuation to take effect.
[oracle@edrserver admin]$lsnrctl stop
[oracle@edrserver admin]$lsnrctl start
or
[oracle@edrserver admin]$lsnrctl reload

Tnsnames.ora

The tnsnames.ora file contains client side network configuration parameters. It can be found in the ORACLE_HOME/network/admin directory on the client. This file will also be present on the server if client style connections are used on the server itself.

Example of a tnsnames.ora file:

--------------WINDOWS--------------------
# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

EDR_205 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ntws205.rmsi.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = edr)
(SERVER = DEDICATED)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

----------------LINUX----------------------
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

EDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edrserver)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = edr)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

NOTE: There are two names “EDR_205” which is called as “TNS_ENTRY” which we use with the connect string like “username/pwd@TNS_ENTRY” & “EDR” which is the service_name (instance_name).
=================================================================================
Sqlnet.ora

The sqlnet.ora file contains client side network configuration parameters. It can be found in the ORACLE_HOME/network/admin directory on the client. This file will also be present on the server if client style connections are used on the server itself.

Example of an sqlnet.ora file:

# sqlnet.ora Network Configuration File: D:\oracle\product\10.1.0\Db_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

====================================

You can find some of the Network related issues and their solutions in the following link:

Error codes and solutions

Establishing a Connection from Oracle to SQL Server

Here are steps to make a connection from Oracle to SQL Server using Oracles' heterogeneous Services ODBC agent.

Oracle has a generic connectivity methodology that allows for the Oracle database server to access non-Oracle database systems through ODBC and SQL*Net services. Step-by-Step setup to query from a SQL Server database.


1. Define a Data Source Name (DSN) for SQL Server

--> The first step is to define a system DSN within the Windows ODBC Data Sources.

a.From the start menu click on Settings -> Control Panel and double click the ODBC icon.

b.Click on the System DSN tab and then click the Add button.

c.Choose the SQL Server driver since this will be a connection to SQL Server. Click Finish to continue with the data source definition.

d.Key in any name you would like to reference this ODBC data source. I have chose SS for simplistic reasons but it should be descriptive to the database you may be connecting to within SQL Server. You may also describe the data source in any way you wish. This is my remote SQL Server that I will be connecting to. Click Next to continue.

e.I selected Sql Server authentication and also provided Login ID and Password below. Then click Next to continue.

f.Typically, this window is populated with the default SQL Server database of "master." Click the check box to change the default database this ODBC connection should connect to and use the drop down list to select. I have chosen to use the my test Remshr database. Click Next to continue.

g.I also left current window alone and clicked Finish.

h.The next window then appears for you to look at the settings you have configured for the data source. Click Test Data Source to validate your definition.

i.TEST COMPLETED SUCCESSFULLY appears, in which case you have successfully configured the data source. Click OK to close all windows as you are done with the data source definition.

j.The end product should be a valid System DSN. You may in the future click on the DSN name and click the Configure button to change the definition if you like. I did this when I wanted to switch between databases. A small warning here on re-configuration of the DSN: you will need to drop and re-create the database link to activate the DSN. Click OK to exit the DSN administrator.


2. Create a Heterogeneous Services Initialization File

Oracle has provided a sample heterogeneous services init file within the %ORACLE_HOME\hs\admin directory. You will need to copy that file to a new file name within the same directory and edit it for the ODBC DSN you have just created. Below you will find the sample heterogeneous services file Oracle provides and then an edited version, which I have given a new name that corresponds to my DSN name.

%ORACLE_HOME\hs\admin\inithsodbc.ora sample file

(ORACLE_HOME=D:\oracle\product\10.1.0\Db_1)

# This is a sample agent init file that contains the HS parameters that are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = (odbc data_source_name)
HS_FDS_TRACE_LEVEL = (trace_level)
#
# Environment variables required for the non-Oracle system
#
#set (env var)=(value)

%ORACLE_HOME\hs\admin\initSS.ora altered file

# This is a sample agent init file that contains the HS parameters that are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = SS
HS_FDS_TRACE_LEVEL = OFF

3. Alter your listener.ora file

Here again Oracle has given us a sample listener.ora file to follow for heterogeneous services within the %ORACLE_HOME\hs\admin directory. Below you will find the sample file and the additions I made to my listener.ora file. I made five distinct changes..
Created my own listener name of LISTENERSS
Changed the Port number to 1522
Changed the SID_NAME to my DSN (SS)
Changed the ORACLE_HOME location
Changed the PROGRAM to hsodbc

%ORACLE_HOME\network\admin\listener.ora altered file

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Ntws205.rmsi.com)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.1.0\Db_1)
(PROGRAM = extproc)
)
)

LISTENERSS =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENERSS=
(SID_LIST=
(SID_DESC=
(SID_NAME=SS)
(ORACLE_HOME = D:\oracle\product\10.1.0\Db_1)
(PROGRAM=hsodbc)
)
)

4. Alter your tnsnames.ora file

Here again Oracle has given us a sample tnsnames.ora file to follow for heterogeneous services within the %ORACLE_HOME\hs\admin directory. Below you will find the sample file and the additions I made to my tnsnames.ora file.

1. I made four distinct changes.Created a TNS entry named SS

2. Changed the Port number to 1522

3. Changed the SID to my DSN (SS)

4. Added OK to the HS= parameter

%ORACLE_HOME\network\admin\tnsnames.ora altered file

SS =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(CONNECT_DATA=(SID=SS))
(HS=OK)
)

5. Start the new Listener

This should be self-explanatory but I provide the output here so that you can know what to expect when you start yours.

D:\>lsnrctl start listenerss

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 15-FEB-2008 16:54:55

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
System parameter file is d:\oracle\product\10.1.0\db_1\network\admin\listener.ora
Log messages written to d:\oracle\product\10.1.0\db_1\network\log\listenerss.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
STATUS of the LISTENER
------------------------
Alias listenerss
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 15-FEB-2008 16:55:09
Uptime 0 days 0 hr. 0 min. 4 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File d:\oracle\product\10.1.0\db_1\network\admin\listener.ora
Listener Log File d:\oracle\product\10.1.0\db_1\network\log\listenerss.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))
Services Summary...
Service "SS" has 1 instance(s).
Instance "SS", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

6. Validate the connection to your DSN

You can now validate the connection to your SQL Server database by the normal Oracle tnsping utility.

D:\>tnsping ss

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 15-FEB-2008 17:14:57

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
D:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)) (CONNECT_DATA=(SID=SS)) (HS=OK))
OK (10 msec)


7. Create a Database Link within Your Oracle Database

Since we would like to connect from our Oracle database and select information from the SQL Server database, we need to create a database link just as if we were connecting to any other remote Oracle database. I have used the default sa login from SQL Server but you may wish to create your own.

SQL> create database link ss
connect to sa identified sa by using 'SS';

Database link created.

This is the fun part as it is the culmination of what we were trying to do. You may describe the tables from SQL Server just as you have done with Oracle in the past and then use a SELECT command. Note that my select statement has double quotes and exact upper and lower cases for the SQL Server query. This is required, at least I tried every combination and none worked except this way.

SQL> desc tblemp@ss
Name Null? Type
----------------------------------------- -------- ----------------------------
EmpNo NOT NULL VARCHAR2(4)
EmpInit NOT NULL VARCHAR2(4)
EmpName VARCHAR2(50)
Salutation VARCHAR2(4)
Nationality VARCHAR2(15)
Sex VARCHAR2(1)
DOB DATE
DOW DATE
Qualification VARCHAR2(15)
-------
-------
-------

SQL>select "EmpNo","EmpInit","EmpName" from tblemp@ss;

Source of this article:
http://www.databasejournal.com/features/oracle/article.php/10893_3442661_1

How to apply a patch ?

1. You MUST read the Readme.txt file included in opatch file, look for any prereq. steps/ post installation steps or and DB related changes. Also, make sure that you have the correct opatch version required by this patch.

2.Make sure you have a good backup of database.

3. Make a note of all Invalid objects in the database prior to the patch.

4. Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.

5. You MUST Backup your oracle Home and Inventory

tar cvf - $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz

6. Unzip the patch in $ORACLE_HOME/patches

7. cd to the patch direcory and do opatch -apply to apply the patch.

8. Read the output/log file to make sure there were no errors.

How to Rollback a patch?

opatch rollback -id (Patch Number)

I messed up my Oracle home/ opatch rollback is failing , what to do now?

Remember, we took the backup of the ORACLE_HOME and main importantly the Oracle inventory, you can just restore (uncompress) the software and the inventory and you are all set. Also for fixing the corrupt inventory, please refer to this OracleMetaLink Note:298906.1

Critical Patch Update JAN 2008 Linux

Oracle Critical Patch Update Advisory - January 2008
OracleCPU Advisory

Below procedure based on my test enviourment if you follow this instruction please go through README.TXT file that comes with PATCH SET.

Patch Installation Procedures for Oracle Database Release 11.1.0.6.0
Patch Number : p6646866_111060_LINUX.zip

Download it from OracleMetaLink

SYSTEM Overview

Platforms : Oracle Enterprise Linux 5
Database : 11.1.0.6.0

Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.

OPatch Utility Information

You must use the OPatch utility release 1.0.0.0.57 or later. You can download it from
OracleMetaLink.

Check OPatch Version

Oracle 10G onwards, the RDBMS software ships with OPatch and it can be loacated under $ORACLE_HOME/OPatch. To download, opatch you need to have an metalink.oracle.com account. On OracleMetaLink search for Patch 4898608, select the Oracle RDBMS version and download the patch.

[oracle@oracle11gr1]$ cd $ORACLE_HOME
[oracle@oracle11gr1 db_1]$ cd OPatch/
[oracle@oracle11gr1 OPatch]$ sh opatch version
Invoking OPatch 11.1.0.6.2

OPatch Version: 11.1.0.6.2

OPatch succeeded.

[oracle@oracle11gr1 OPatch]$ sh opatch napply /u01/app/oracle/product/11.1.0/db_1/6646866/ -skip_subset -skip_duplicate

Invoking OPatch 11.1.0.6.2

[Output Edited]
OPatch continues with these patches: 6646866 6650132 6650135 6731395

Do you want to proceed? [y|n]
Y
User Responded with: Y
Running prerequisite checks...
[Output Edited]

Is the local system ready for patching? [y|n]
Y
User Responded with: Y
[Output Edited]

Execution of 'sh /u01/app/oracle/product/11.1.0/db_1/6646866/6646866/custom/scripts/post -apply 6646866 ':

Return Code = 0

The local system has been patched and can be restarted.

UtilSession: N-Apply done.

OPatch succeeded.

Post Installation Instructions

[oracle@oracle11gr1 OPatch]$
After installing the patch, perform the following actions:

Loading Modified .sql Files into the Database

There are no steps to perform, because the CPU for Release 11.1.0.6 does not use catcpu.sql or related scripts.

You can go directly to Recompiling Views in the Database

You must recompile views for all databases except the following:

Databases created with Release 11.1.0.6 or later

Databases created with any release (for example, 10.2.0.3, 10.1.0.5, or 9.2.0.8) after CPUJan2008 or a later CPU has been applied

The time required to recompile the views and related objects depends on the total number of objects and on your system configuration. In one internal Oracle test with approximately 2000 views and 4000 objects, the total execution time for view_recompile_jan2008cpu.sql and utlrp.sql was about 30 minutes.

If you want to check whether view recompilation has already been performed for the database, execute the following statement.

SELECT * FROM registry$history where ID = '6452863';

[oracle@oracle11gr1]$ sqlplus /nolog

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
Database opened.
SQL>
SQL> SELECT * FROM registry$history where ID = '6452863';
no rows selected

If the view recompilation has been performed, this statement returns one row. If the view recompilation has not been performed, this statement returns no rows.

To recompile the views in the database, follow these steps:

-->Run the pre-check script, which reports the maximum number of views and objects that may be recompiled:

cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @recompile_precheck_jan2008cpu.sql

Running precheck.sql...

Number of views to be recompiled :2715
-----------------------------------------------------------------------

Number of objects to be recompiled :5523
Please follow the README.txt instructions for running viewrecomp.sql

PL/SQL procedure successfully completed.

The purpose of this step is to help you determine whether view recompilation should be done at the same time as the CPU install, or scheduled later.

-->Run the view recompilation script. Note that this script is run with the database in upgrade mode, which restricts connections as SYSDBA.

cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP UPGRADE
SQL> @view_recompile_jan2008cpu.sql
PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

1 row created.

Commit complete.

No. of Invalid Objects is :31
Please refer to README.html to for instructions on validating these objects

PL/SQL procedure successfully completed.

Logfile for the current viewrecomp.sql session is : vcomp_ORCL_04Feb2008_16_10_52.log

SQL> SHUTDOWN;
SQL> STARTUP;
SQL> exit

If any invalid objects were reported, run the utlrp.sql script as follows:

[oracle@oracle11gr1 view_recompile]$ cd $ORACLE_HOME/rdbms/admin
[oracle@oracle11gr1 admin]$ sqlplus /nolog
SQL> conn /as sysdba
Connected.
SQL> @utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2008-02-04 16:18:22

[Output Edited]
PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2008-02-04 16:18:50

PL/SQL procedure successfully completed.

[Output Edited]
OBJECTS WITH ERRORS
-------------------
0
[Output Edited]
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL> SELECT * FROM registry$history where ID = '6452863';

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS
---------------------------- -------- ---------- -------- ---------- ----------
04-FEB-08 04.13.43.702093 PM CPU 6452863 CPUJan2008

Still if you find any invalid objects then, manually recompile them. For example:
SQL> alter package schemaname.packagename compile;

Critical Patch Update JAN 2008 on Windows

Oracle Critical Patch Update Advisory - January 2008
OracleCPU Advisory

Below procedure based on my test enviourment if you follow this instruction please go through README.TXT file that comes with PATCH SET.

Patch Installation Procedures for Oracle Database Release 10.1.0.5
Patch Number : p6637274_10105_WINNT.zip

SYSTEM Overview

Platforms : Windows XP SP2
Database : 10.1.0.5.0

Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.

OPatch Utility Information

You must use the OPatch utility release 1.0.0.0.53 or later. You can download it from
OracleMetaLink with patch 2617419.

Check Oracle Inventory Setup

Oracle 10G onwards, the RDBMS software ships with OPatch and it can be loacated under $ORACLE_HOME/OPatch. To download, opatch you need to have an metalink.oracle.com account. On OracleMetaLink search for Patch 4898608 , select the Oracle RDBMS version and download the patch.

set ORACLE_SID=ARIES
set ORACLE_HOME=D:\oracle\product\10.1.0\Db_1
set PERL5LIB=D:\oracle\product\10.1.0\db_1\perl\5.6.1\lib;%PERL5LIB%
set PATH=D:\oracle\product\10.1.0\db_1\perl\5.6.1\bin\MSWin32-x86;%PATH%

D:\patchset10gR1\6637274>cd D:\oracle\product\10.1.0\db_1\opatch

D:\oracle\product\10.1.0\Db_1\OPatch>opatch lsinventory
[Output Edited]
Creating log file "D:\oracle\product\10.1.0\db_1\.patch_storage\LsInventory__02-05-2008_10-57-56.log"

Result:

There is no Interim Patch

OPatch succeeded.
OPatch returns with error code = 0

D:\oracle\product\10.1.0\Db_1\OPatch>

==================
D:\oracle\product\10.1.0\Db_1\OPatch>opatch apply D:\patchset10gR1\6637274

[Output Edited]
Creating log file "D:\oracle\product\10.1.0\db_1\.patch_storage\6637274\Apply_6637274_02-05-2008_11-06-12.log"

[Output Edited]
Is this system ready for updating?
Please respond Y|N >
Y
Executing the Apply pre-patch script (D:\patchset10gR1\6637274\custom\scripts\pre.bat)...
Applying patch 6637274...

[Output Edited]
Updating inventory...
Backing up comps.xml ...

********************************************************************************
** ATTENTION **
** **
** Please note that the Security Patch Installation (Patch Deinstallation) is **
** not complete until all the Post Installation (Post Deinstallation) **
** instructions noted in the Readme accompanying this patch, have been **
** successfully completed. **
** **
********************************************************************************
********************************************************************************
Executing the Apply post-patch script (D:\patchset10gR1\6637274\custom\scripts\post.bat)...

OPatch succeeded.
OPatch returns with error code = 0

D:\oracle\product\10.1.0\Db_1\OPatch>

====================================
CD D:\oracle\product\10.1.0\db_1\CPU\cpujan2008

D:\oracle\product\10.1.0\Db_1\cpu\cpujan2008>sqlplus /nolog
SQL> conn sys/sys as sysdba
Connected.
SQL> select name from v$instance;

INSTANCE_NAME
---------------
aries
SQL> shutdown immediate;

SQL> STARTUP MIGRATE
ORACLE instance started.
Database opened.
SQL>@CATCPU.SQL

[OUTPUT EDITED]
Package body created.
Package body created.
Package body created.
Session altered.

1 row selected.

Session altered.

OWA_MESSAGE
--------------------------------------------------------------------------------
Installed OWA version is: 9.0.4.0.2

Backing up previous OWA packages to: MODPLSQL\owa_restore_aries.sql

OWA_MESSAGE
-------------------

OWALOAD File: MODPLSQL\owa_all\90\owaload_patch.sql

Logfile: MODPLSQL\owa_patch_aries.log

OWA_DBG_MSG

-------------------
Installed OWA version is: 9.0.4.0.2;
Shipped OWA version is : 9.0.4.0.6;
OWA packages v9.0.4.0.6 will be installed into your database v101050
Will install owadummy.sql and owacomm.sql
[Output Edited]
No errors.
No. of Invalid Objects is :115
Please refer to README.html to for instructions on validating these objects
Logfile for the current catcpu.sql session is : APPLY_ARIES_05Feb2008_11_53_51.log
not spooling currently
SQL> SHUTDOWN IMMEDIATE
SQL> exit
D:\oracle\product\10.1.0\Db_1\cpu\cpujan2008>

If catcpu.sql reports any Invalid Objects, compile the invalid objects as follows

cd D:\oracle\product\10.1.0\db_1\rdbms\admin

D:\oracle\product\10.1.0\Db_1\RDBMS\ADMIN>sqlplus /nolog

SQL> conn sys/sys as sysdba
Connected to an idle instance.
SQL> startup
Database opened.

SQL> @utlprp.sql 0

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2008-02-05 12:08:43

PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2008-02-05 12:08:43

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL> select OBJECT_NAME from dba_objects where status = 'INVALID';

no rows selected

SQL> exit

Run the CATCPU.sql and UTLRP.sql on all the instances in the ORACLE_HOME individually.

To view the details about the patch applied :

select * from dba_registry_history;

SELECT * FROM registry$history where ID = '6452863';

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS
---------------------------- -------- ---------- -------- ---------- ----------
05-FEB-08 12.30.36.406000 PM CPU 6637274 CPUJan2008

Still if you find any invalid objects then, manually recompile them. For example:

SQL> alter package schemaname.packagename compile;

TNS less connection in Oracle.

I have tested this connection method in Oracle 8i, 9i, 10g and in 11g and it works fine.

D:\>sqlplus /nolog

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Jan 24 16:59:44 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(Host=ntwssrv)(Port=1521))(CONNECT_DATA=(SID=ORCL)))
Connected.
SQL>

If you think about above connection, it is more like specifying complete address in the connect string as you have in the tnsnames.ora file.

For 10g and 11g you can use following method as well. This method does not work for 8i or 9i databases.

==========
D:\>sqlplus /nolog

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Jan 24 17:09:47 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn scott/tiger@ntwssrv:1521/aries
Connected.

===========
[oracle@oracle11gr1 oracle]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jan 24 17:27:40 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> conn scott/tiger@oracle11gr1:1521/orcl
Connected.
SQL>

Resolving ORA-12705 on Client

This tip resolves Oracle client error: “ORA-12705: Cannot access NLS data files or invalid environment specified“. According to Oracle documentation, “ORA-12705 can happen under the following conditions:

1. ORA-12705 with incorrect NLS_LANG parameter
2. ORA-12705 with incorrectly specified ORA_NLSx
3. ORA-12705 from incorrect Oracle install or configuration
4. ORA-12705 when using Special Character sets
5. ORA-12705 when connecting with SQL*Net
6. ORA-12705 during migrations
7. ORA-12705 when connecting with SQL*Plus
8. ORA-12705 when connecting with language pre-compilers
9. ORA-12705 during Export/Import

Try doing the following to fix this error on the client side. On the client Windows machine (this tip is only for Windows), The NLS_LANG must be unset in the Windows registry (re-naming works the best because you know what you changed). Look for the NLS_LANG subkey in the registry at \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE, and rename it. Then try SQLPlus again.

Limit user sessions in Oracle

How can I limit the number of times a single user can connect to the database at any one time ?

We can do this by limiting the number of sessions they are allowed to have.
Easy as always to do in Oracle. First we allow resource limits by setting the resource_limit to true, then we simply create a profile and assign that to the user. The profile can set the limit of how many session a user is allowed to have.

A little demonstration:

D:\>sqlplus sys/sys as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Jan 21 10:46:28 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show parameter resource_limit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean FALSE

SQL> alter system set RESOURCE_LIMIT=true scope=both;

System altered.

SQL> create profile sesslimit limit sessions_per_user 1;

Profile created.

SQL> create user sree identified by sree123
default tablespace sbdata profile sesslimit;

User created.

SQL> grant create session to sree;

Grant succeeded.

If you want to add this profile to existing user then:

SQL> alter user sree profile sesslimit;

SQL> connect sree/sree123
Connected.

-- I'll start second session in another terminal.

SQL> connect sree/sree123
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

==========

SQL> SELECT DISTINCT resource_name, limit
FROM dba_profiles
ORDER BY resource_name;

SQL> drop profile sesslimit cascade;

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production With the Partitioning, OLAP and Data Mining options

IMPORTANT LINKS

Oracle By Example (OBE)

The Oracle by Example (OBE) series provides hands-on, step-by-step instructions on how to implement various technology solutions to business problems. OBE solutions are built for practical real-world situations, allowing you to gain valuable hands-on experience as well as use the presented solutions as the foundation for production implementation, dramatically reducing time to deployment.
Direct Link to all Articles

Installing Oracle Database 11g on Linux
Direct Link to Article

Installing Oracle Database 11g on Windows
Direct Link to Article

Exploring Your Database with Enterprise Manager Database Control
Direct Link to Article

Using Database Replay to Perform Real-World Testing
Direct Link to Article

Creating a Physical Standby Database
Direct Link to Article

Oracle by Example Series: Oracle Database 11g
Direct Link to Articles

Oracle by Example Series: Oracle Database 10g
Direct Link to Articles

Automating Installation of Oracle Database 10g and Red Hat Enterprise Linux
Direct Link to Article

RAC LINKS

Biggest challenge in learning / understanding RAC is lack of hands on experience because of expensive hardware, network cards, interconnect and cluster file system. The following links cover step by step Oracle RAC installation and configuration
on single laptop/desktop using VMWare Server without investing anything extra on hardware (using two virtual machines, virtual network cards and virtual shared disk for Oracle RAC database shared files system ).

Download VMware Server (for Windows and Linux systems)
Download VMWARE

Installing Oracle Database 10g on Windows by Using Real Application Clusters (RAC) and Automated Storage Management (ASM)
Direct Link to Article

Steps for configuring/building Oracle RAC Database on laptop/desktop machine (without investing any thing on hardware).
Direct Link to Article

Oracle 10g RAC installation using Unbreakable Linux
Direct Link to Article

Installing Oracle RAC 10g Release 1 on Linux x86
Direct Link to Article

Installation of Oracle 10g release 2 (10.2.0.1) RAC on Linux (CentOS 4) using VMware Server with no additional shared disk devices.
Direct Link to Article

Installing Oracle RAC 10g Release 2 on Linux x86
Direct Link to Article

Oracle 10g RAC On Windows 2003 Using VMware Server
Direct Link to Article

Install Oracle RAC 10g on Oracle Enterprise Linux Using VMware Server
Direct Link to Article

The Oracle-on-Linux Installation Menu
Direct Link to Article

Installing Oracle Database 10g with Real Application Cluster (RAC) on Red Hat Enterprise Linux Advanced Server 3
Direct Link to Article

Convert a Single Instance Database to Oracle RAC 10g on RHEL3
Direct Link to Article

Code to shrink Your Tablespaces

Use it at your own risk.


SET verify OFF
COLUMN file_name format a50 WORD_WRAPPED
COLUMN smallest format 999,990 heading "Smallest|Size|Poss."
COLUMN currsize format 999,990 heading "Current|Size"
COLUMN savings format 999,990 heading "Poss.|Savings"
BREAK on REPORT
COMPUTE sum of savings on REPORT
COLUMN value new_val BLKSIZE
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'db_block_size';

SELECT FILE_NAME,
CEIL((NVL(HWM,1) *&& BLKSIZE) / 1024 / 1024) SMALLEST,
CEIL(BLOCKS *&& BLKSIZE / 1024 / 1024) CURRSIZE,
CEIL(BLOCKS *&& BLKSIZE / 1024 / 1024) - CEIL((NVL(HWM,1) *&& BLKSIZE) / 1024 / 1024) SAVINGS
FROM DBA_DATA_FILES A,
(SELECT FILE_ID,
MAX(BLOCK_ID + BLOCKS - 1) HWM
FROM DBA_EXTENTS
GROUP BY FILE_ID) B
WHERE A.FILE_ID = B.FILE_ID (+);

SELECT 'alter database datafile '''
||File_Name
||''' resize '
||Ceil((Nvl(hwm,1) *&& blkSize) / 1024 / 1024)
||'m;' cmd
FROM dba_Data_Files a,
(SELECT File_Id,
MAX(Block_Id + Blocks - 1) hwm
FROM dba_Extents
GROUP BY File_Id) b
WHERE a.File_Id = b.File_Id (+)
AND Ceil(Blocks *&& blkSize / 1024 / 1024) - Ceil((Nvl(hwm,1) *&& blkSize) / 1024 / 1024) > 0;

Cool Scripts for daily DBA activities

I will be adding scripts to this post when ever I come across a good one.

Note: Test the scripts before using on a Production database.
Finely Format the SQL statements before use.

Calculate the Database Size

COLUMN "Total Mb" FORMAT 999,999,999.0
COLUMN "Redo Mb" FORMAT 999,999,999.0
COLUMN "Temp Mb" FORMAT 999,999,999.0
COLUMN "Data Mb" FORMAT 999,999,999.0

Prompt
Prompt "Database Size"

select (select sum(bytes/1048576) from dba_data_files) "Data Mb",
(select NVL(sum(bytes/1048576),0) from dba_temp_files) "Temp Mb",
(select sum(bytes/1048576)*max(members) from v$log) "Redo Mb",
(select sum(bytes/1048576) from dba_data_files) +
(select NVL(sum(bytes/1048576),0) from dba_temp_files) +
(select sum(bytes/1048576)*max(members) from v$log) "Total Mb"
from dual;
=========================================================
Heavy CPU SQL

This will generate the top SQL statements that produce heavy CPU usage

set termout on
set feedback on
set pagesize 132

#spool cpusql.lis

SELECT username,address, hash_value,
buffer_gets, executions, buffer_gets/executions "Gets/Exec",sql_text
FROM v$sqlarea,dba_users
WHERE buffer_gets > 50000
and executions > 0
and v$sqlarea.parsing_user_id = dba_users.user_id
order by 4 desc;

#spool off;
=========================================================
Calculate the Table Size

SELECT Segment_Name Table_Name,
SUM(Bytes) / (1024 * 1024) Table_Size_Meg
FROM dba_Extents
WHERE Owner = 'SCOTT'
AND Segment_Name = 'DEPT'
AND Segment_Type = 'TABLE'
GROUP BY Segment_Name
/

=============================================================================
Determine Tablespace Usage

SELECT a.TableSpace_Name,
Round(a.Bytes_Alloc / 1024 / 1024,2) Megs_Alloc,
Round(Nvl(b.Bytes_Free,0) / 1024 / 1024,2) Megs_Free,
Round((a.Bytes_Alloc - Nvl(b.Bytes_Free,0)) / 1024 / 1024,
2) Megs_Used,
Round((Nvl(b.Bytes_Free,0) / a.Bytes_Alloc) * 100,
2) pct_Free,
100 - Round((Nvl(b.Bytes_Free,0) / a.Bytes_Alloc) * 100,
2) pct_Used,
Round(MaxBytes / 1048576,2) MAX
FROM (SELECT f.TableSpace_Name,
SUM(f.Bytes) Bytes_Alloc,
SUM(DECODE(f.AutoexTensible,'YES',f.MaxBytes,
'NO',f.Bytes)) MaxBytes
FROM dba_Data_Files f
GROUP BY TableSpace_Name) a,
(SELECT f.TableSpace_Name,
SUM(f.Bytes) Bytes_Free
FROM dba_Free_Space f
GROUP BY TableSpace_Name) b
WHERE a.TableSpace_Name = b.TableSpace_Name (+)
UNION ALL
SELECT h.TableSpace_Name,
Round(SUM(h.Bytes_Free + h.Bytes_Used) / 1048576,
2) Megs_Alloc,
Round(SUM((h.Bytes_Free + h.Bytes_Used) - Nvl(p.Bytes_Used,0)) / 1048576,
2) Megs_Free,
Round(SUM(Nvl(p.Bytes_Used,0)) / 1048576,2) Megs_Used,
Round((SUM((h.Bytes_Free + h.Bytes_Used) - Nvl(p.Bytes_Used,0)) / SUM(h.Bytes_Used + h.Bytes_Free)) * 100,
2) pct_Free,
100 - Round((SUM((h.Bytes_Free + h.Bytes_Used) - Nvl(p.Bytes_Used,0)) / SUM(h.Bytes_Used + h.Bytes_Free)) * 100,
2) pct_Used,
Round(SUM(f.MaxBytes) / 1048576,2) MAX
FROM sys.v_$temp_Space_Header h,
sys.v_$temp_Extent_Pool p,
dba_temp_Files f
WHERE p.File_Id (+) = h.File_Id
AND p.TableSpace_Name (+) = h.TableSpace_Name
AND f.File_Id = h.File_Id
AND f.TableSpace_Name = h.TableSpace_Name
GROUP BY h.TableSpace_Name
ORDER BY 1
-------------------------------------------------------------------------
SELECT TableSpace_Name "Tablespace",
COUNT(Bytes) "Pieces",
MIN(Bytes) "Min",
Round(Avg(Bytes)) "Average",
MAX(Bytes) "Max",
SUM(Bytes) "Total"
FROM sys.dba_Free_Space
GROUP BY TableSpace_Name

==========================================================================
Track your import process:

SELECT Substr(sql_Text,Instr(sql_Text,'INTO "'),30) Table_Name,
Rows_Processed,
Round((SYSDATE - To_date(First_Load_Time,'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
1) Minutes,
Trunc(Rows_Processed / ((SYSDATE - To_date(First_Load_Time,'yyyy-mm-dd hh24:mi:ss')) * 24 * 60)) Rows_Per_Minute
FROM sys.v_$sqlArea
WHERE sql_Text LIKE 'INSERT %INTO "%'
AND Command_Type = 2
AND Open_Versions > 0;
===============================================================================
Details of parameters for SPfile modifications

SELECT NAME,
Isses_modIfiAble,
Issys_modIfiAble,
IsInstance_modIfiAble
FROM v$Parameter
ORDER BY NAME;
==============================================================================
Query to find the difference between two dates omitting weekends and holidays.


SELECT (To_date('31/01/2008','dd/mm/rrrr') - To_date('01/01/2008','dd/mm/rrrr') + 1) - (SELECT COUNT(Days)
FROM (SELECT To_char(To_date('01/01/2008','dd/mm/rrrr') + LEVEL,'D') Days
FROM Dual CONNECT BY LEVEL <= 31) WHERE Days IN ('7','1')) DAY
FROM Dual;
===============================================================================

Deleting Duplicate Records

You may have come across numerous situations where duplicate records needs to be deleted from some table quickly. More often than not this is due to an application or data transformation issue and the number of duplicate rows are generally small related to the overall number of rows in the table. DBAs struggle with this task and spend way too much time and so coming up with elaborate scripts.

Assuming the table looks like this:

ACCOUNT
ACCOUNT_ID
STATUS_CODE
FIRST_NAME
LAST_NAME
STREET_ADDRESS

In this example, there is no primary key or unique constraint preventing duplicates from being inserted. The "logical primary key" is ACCOUNT_ID and STATUS_CODE.

First we might want to view the duplicate records to assist with troubleshooting and this can be done with this query:

SELECT Account_Id,
Status_Code,
COUNT(* ) OccurAnces
FROM ACCOUNT
GROUP BY Account_Id,Status_Code
HAVING COUNT(* ) > 1;

And to delete them we rely on Oracle’s rowed which is a unique "key" that exists on every table row:

DELETE
FROM ACCOUNT A
WHERE
(ACCOUNT_ID,STATUS_CODE) IN (
SELECT
ACCOUNT_ID,
STATUS_CODE,
FROM ACCOUNT
GROUP BY
ACCOUNT_ID,
STATUS_CODE
HAVING
COUNT(*) > 1) AND
ROWID != (
SELECT
MIN(ROWID)
FROM ACCOUNT
WHERE
ACCOUNT_ID = A.ACCOUNT_ID AND
STATUS_CODE = A.STATUS_CODE);

Alternatively this more simplified query can be used, but in some cases it might not execute as fast as the above query because of having to do more random I/O:

DELETE FROM ACCOUNT a
WHERE RowId != (SELECT MIN(RowId)
FROM ACCOUNT
WHERE Account_Id = a.Account_Id
AND Status_Code = a.Status_Code);

Please make sure that you thoroughly understand your duplicate record problem and what makes a record unique before deleting duplicate records in a table. Make sure you test the above SQL statements in a test environment with a representative test case before attempting this in a production environment

DBVERIFY

DBVERIFY is an external command-line utility that performs a physical data structure integrity check. It can be used on offline or online databases, as well on backup files. You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored, or as a diagnostic aid when you have encountered data corruption problems.

DBVERIFY checks are limited to cache-managed blocks (that is, data blocks). Because DBVERIFY is only for use with datafiles, it will not work against control files or redo logs.


There are two command-line interfaces to DBVERIFY


1.Using DBVERIFY to Validate Disk Blocks of a Single Datafile

D:/>dbv FILE=D:\oracle\product\10.1.0\oradata\ORCL\users.dbf FEEDBACK=100

------------------------------------------------------------

2.Using DBVERIFY to Validate a Segment
If we want to check segment level you can use SEGMENT_ID

SEGMENT_ID = Segment ID (tsn.relfile.block)

we can get all three values from below query.

SQL> Grant sysdba to scott;

SQL>SELECT t.ts#,
s.Header_File,
s.Header_Block
FROM v$TableSpace t,
dba_Segments s
WHERE s.Owner = 'SCOTT'
AND s.Segment_Name = 'EMP'
AND t.NAME = s.TableSpace_Name;


TS# HEADER_FILE HEADER_BLOCK
---------- ----------- ------------
4 4 27

SQL> host dbv userid=scott/tiger segment_id=4.4.27

New Year Wishes to all my Guests....

May the dawning of this New Year, fill your heart with new hopes, open up new horizons and bring for you promises of brighter tomorrows. May you have a great New Year - 2008.

Some of the Oracle Database 11g New Features

Oracle Database 11g Release 1 (11.1) New Features
=================================================

Case Sensitive Passwords

Beginning with Oracle Database 11g Release 1, database passwords are case sensitive.
You can disable this features by setting the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE.

=======================================
Readonly Tables

To place a table in read-only mode, you must have the ALTER TABLE privilege on thetable or the ALTER ANY

TABLE privilege. In addition, the COMPATIBILE initializationparameter must be set to 11.1.0 or greater.

The following example places the EMP table in read-only mode:

ALTER TABLE EMP READ ONLY;

The following example returns the table to read/write mode:

ALTER TABLE EMP READ WRITE;

=========================================

Invisible Indexes

An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. Making an index invisible is an alternative to making it unusable or dropping it.

Using invisible indexes, you can do the following:

Test the removal of an index before dropping it.

Altering Indexes

Use temporary index structures for certain operations or modules of an application without affecting the overall application.

To create an invisible index, use the SQL statement CREATE INDEX with theINVISIBLE clause. The following statement creates an invisible index named IND_EMP_DEPT for the DEPT column of the EMP table:

CREATE INDEX ind_emp_dept ON emp(dept)INVISIBLE;

To make an invisible index visible, issue this statement:

ALTER INDEX ind_emp_dept VISIBLE;

To make a visible index invisible, issue this statement:

ALTER INDEX ind_emp_dept INVISIBLE;

To check whether an index is visible or invisible, query the dictionary views

USER[ALL][DBA]_INDEXES

For example, to determine if above index is invisible, issue the following query:

SELECT INDEX_NAME, VISIBILITY FROM
USER_INDEXES WHERE INDEX_NAME = 'IND_EMP_DEPT';

INDEX_NAME VISIBILITY
------------- ----------
IND_EMP_DEPT VISIBLE

===========================================

Automatic Stats Collection on Tables

As you know that you can enable DBMS_STATS to automatically gather statistics for a table by specifying the MONITORING keyword in the CREATE (or ALTER) TABLE statement. But, starting with Oracle Database 11g, the MONITORING and NOMONITORING keywords have been deprecated and statistics are collected automatically. If you do specify these keywords, they are ignored.

Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE operations for the table since the last time statistics were gathered. Information abouthow many rows are affected is maintained in the SGA, until periodically (about everythree hours) SMON incorporates the data into the data dictionary.

This data dictionary information is made visible through the DBA_TAB_MODIFICATIONS, ALL_TAB_MODIFICATIONS, or USER_TAB_MODIFICATIONS views. The database uses these
views to identify tables with stale statistics.

=================================================

Virtual columns

Tables can now include virtual columns. The value of a virtual column in a row is derived by evaluating an expression. The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions. In some cases, a virtual column eliminates the need to create a separate view. You can create an index on a virtual column, and you can use a virtual column as a partition or subpartition key.

==============================================

Simplified and improved automatic memory management

You can now set a single initialization parameter (MEMORY_TARGET) to indicate the total amount of memory that is to be allocated to the database (the SGA and instance PGA). The system then automatically and dynamically tunes all SGA and PGA components for optimal performance. You can still designate minimum sizes individually for the SGA and instance PGA.

===============================================

Database resident connection pooling

Database resident connection pooling (DRCP) provides a connection pool in the database server for typical Web application usage scenarios where the application acquires a database connection, works on it for a relatively short duration, and then releases it. DRCP pools "dedicated" servers, which are the equivalent of a server foreground process and a database session combined. DRCP enables sharing of
database connections across middle-tier processes on the same middle-tier host and across middle-tier hosts. This results in significant reduction in database resources needed to support a large number of client connections, thereby boosting the scalability of both middle-tier and database tiers.

==================================================

Tablespace-level encryption

You can encrypt any permanent tablespace to protect sensitive data. Tablespace encryption is completely transparent to your applications. When you encrypt a tablespace, all tablespace blocks are encrypted.

All segment types are supported for encryption, including tables, clusters, indexes, LOBs, table and index partitions, and so on.

==========================================================

Result cache in the system global area

Results of queries and query fragments can be cached in memory in the result cache. The database can then use cached results to answer future executions of these queries and query fragments. Because retrieving results from the result cache is faster than rerunning a query, frequently run queries experience a significant performance improvement when their results are cached.

The result cache occupies memory in the shared pool.

=============================================================

Enhanced online index creation and rebuild

Online index creation and rebuild prior to this release required a DML-blocking lock at the beginning and at the end of the rebuild for a short period of time. This lock could delay other DML statements and therefore cause a performance spike. This lock is no longer required, making these online index operations fully transparent.

===============================================================

Ability to online redefine tables that have materialized view logs

Tables with materialized view logs can now be redefined online. Materialized view logs are now one of the dependent objects that can be copied to the interim table with the DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS package procedure.

=====================================================================

Optimized ALTER TABLE...ADD COLUMN

For certain types of tables, when adding a column that has both a NOT NULL constraint and a default value, the database can optimize the resource usage and storage requirements for the operation. It does so by storing the default value for the new column as table metadata, avoiding the need to store the value in all existing records.

In addition, the following ADD COLUMN operations can now run concurrently with DML operations:

Add a NOT NULL column with a default value

Add a nullable column without a default value

Add a virtual column
==========================================================