Today I planned to test “Recover the datafile without prior backup”
Currently there is no failure in database. I shutdown the database & removed the below datafile in OS level “D:\ORACLE\APP\ORADATA\CHE\TEST.DBF”.
1.Database must be in archive log mode.
2.We need all the archive log files from lost datafile created.
1.Login into che database
SQL*Plus: Release 184.108.40.206.0 - Production on Wed May 19 14:21:03 2010
Copyright (c) 1982, 2008, Oracle.
What is Recycle Bin
Oracle has introduced "Recycle Bin" Feature Oracle 10g to store all the dropped objects.
If any table in Oracle 10g is dropped then any associated objects to this table such as indexes,
constraints and other dependant objects are simply renamed with a prefix of BIN$$.
Why Recycle Bin
A user drops a very important table--accidentally, of course--and it needs to be revived as soon as possible.
Oracle Data Recovery Advisor 11g new feature
Data recovery advisor is one of the oracle 11g new features.
The Data Recovery Advisor tool can assist with diagnosing media failures, making recommendations and providing the RMAN commands to resolve the problem.
Data Recovery advisor we have used three commands.
How it works?
The Data Recovery Advisor stores its information outside the database in the Automatic Diagnostic Repository (ADR - V$diag_info)
If you find any problem in database and Data recovery a
Sometime is necessary to move all database objects from one tablespace to another.
Basically are tables, indexes and lobs.
This script permit move database objects from one user and/or tablespace to another:
set echo off
set heading off
var tbs_source varchar2;
var tbs_dest varchar2;
var schema_user varchar2;
-- '' if it isn't relevant.
exec :tbs_source := 'SOURCE_TBS';
-- '' if it isn't relevant.
exec :schema_user := 'SOURCE_USER';
exec :tbs_dest := 'DEST_TBS';
select 'Transporting tablespace ' || :tbs_source || ' or user ' || :schema_user || ' to tables
Security is gaining importance. As per the reports in the year 2008, loss of confidential information has increased to 63%. This loss of confidential information is a breach of Service Level Agreement (SLA). Organizations will have to compensate these loses in the form of financial penalties.
A recent information protection law from a state in U.S.A claims that information of residents of this state stored in databases across the globe will have to be encrypted. Upon failure, the database owners will have to pay $5000 penalty.
Original blog post in here.
Give the password template to this function and get random password. It acts like dbms_random.string() function but takes more than one character as a parameter.
Here is a scenario (template)
- First char must be UPPERCASE
- Second and third one must be NUMBER
- Make fourth a NON-ALPHANUMERIC character
- Fifth one must be LOWERCASE
- Sixth is a NUMBER again
- Seventh is any character
Why indexes are going to unusable status?
When will the indexes goes for unusable state?
1.Any maintenance activity in partition table – Alter command
1.Truncate the table partition
2.Drop the table partition
3.Split Part/Sub Partition
2.Any maintenance activity in table – Table Reorganization.
1.Move the table.
More details about online redefinition -
3.Oracle Import – While import the data with skip_unusable_indexes=y.
To improve the perfo
RMAN Cloning using active database feature - Oracle 11g feature
Normally we well knew about RMAN cloning.
Oracle9i/10g using duplicate cloning command, we do the RMAN cloning.
We need a RMAN full backup for cloning the database in oracle9i/10g.
In oracle 11g provides a new feature, without RMAN database backup we can clone the database.
I have tested the active database RMAN cloning in my test server.
Operating system: Windows XP service pack 2
Database Version: 220.127.116.11 (32 bit)
Source database name: troy
Clone database name: clonedb
How its work?
READ ONLY tables in oracle 11g
A restore point can be used to flash back a table or the database to the time specified by the restore point without the need to determine the SCN or timestamp. Restore points are also useful in various RMAN operations, including backups and database duplication. You can use RMAN to create restore points in the process of implementing an archival backup.
1. Normal restore point