RMAN Recovery Scenarios

RMAN Backup and Recovery Scenarios

RMAN Backup and Recovery Scenarios

 

Complete Closed Database Recovery. System tablespace is missing

If the system tablespace is missing or corrupted the database cannot be started up so a complete closed database recovery must be performed.
Pre requisites: A closed or open database backup and archived logs.
1. Use OS commands to restore the missing or corrupted system datafile to its original location, ie:
cp -p /usr/backup/RMAN/system01.dbf  /usr/oradata/u01/IASDB/system01.dbf
2. startup mount;
3. recover datafile 1;
4. alter database open;

Complete Open Database Recovery. Non system tablespace is missing

If a non system tablespace is missing or corrupted while the database is open, recovery can be performed while the database remain open.
Pre requisites: A closed or open database backup and archived logs.
1. Use OS commands to restore the missing or corrupted datafile to its original location, ie:
cp -p /usr/backup/RMAN/user01.dbf /usr/oradata/u01/IASDB/user01.dbf
2. alter tablespace <tablespace_name> offline immediate;
3. recover tablespace <tablespace_name>;
4. alter tablespace <tablespace_name> online;

Complete Open Database Recovery (when the database is initially closed).Non system tablespace is missing

If a non system tablespace is missing or corrupted and the database crashed,recovery can be performed after the database is open.
Pre requisites: A closed or open database backup and archived logs.
1.   startup; (you will get ora-1157 ora-1110 and the name of the missing datafile, the database will remain mounted)
2.   Use OS commands to restore the missing or corrupted datafile to its original location, ie:
cp -p /usr/backup/RMAN/user01.dbf /usr/oradata/u01/IASDB/user01.dbf
3.   alter database datafile 6 offline; (tablespace cannot be used because the database is not open)
4.   alter database open;
5.   recover datafile 6;
6.   alter tablespace <tablespace_name> online;

Recovery of a Missing Datafile that has no backups (database is open).

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.
Pre requisites: All relevant archived logs.
1.   alter tablespace <tablespace_name> offline immediate;
2.   alter database create datafile ‘/user/oradata/u01/IASDB/newdata01.dbf’;
3.   recover tablespace <tablespace_name>;
4.   alter tablespace <tablespace_name> 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/IASDB/newdata01.dbf’ as ‘/user/oradata/u02/IASDB/newdata01.dbf’

Restore and Recovery of a Datafile to a different location.

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.
1.    Use OS commands to restore the missing or corrupted datafile to the new location, ie:
cp -p /usr/backup/RMAN/user01.dbf /usr/oradata/u02/IASDB/user01.dbf
2.    alter tablespace <tablespace_name> offline immediate;
3.    alter tablespace <tablespace_name> rename datafile ‘/user/oradata/u01/IASDB/user01.dbf’ to ‘/user/oradata/u02/IASDB/user01.dbf’;
4.    recover tablespace <tablespace_name>;
5.    alter tablespace <tablespace_name> online;

Control File Recovery

Always multiplex your controlfiles. Controlfiles are missing, database crash.
Pre requisites: A backup of your controlfile and all relevant archived logs.
1.    startup; (you get ora-205, missing controlfile, instance start but database is not mounted)
2.    Use OS commands to restore the missing controlfile to its original location:
cp -p /usr/backup/RMAN/control01.dbf /usr/oradata/u01/IASDB/control01.dbf
cp -p /usr/backup/RMAN/control02.dbf /usr/oradata/u01/IASDB/control02.dbf
3.    alter database mount;
4.    recover automatic database using backup controlfile;
5.    alter database open resetlogs;
6.    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 an archived log is missing, so recovery can only be made until the previous sequence, or when an important object was dropped, and recovery needs to be made until before the object was dropped.
Pre requisites: A closed or open database backup and archived logs, the time or sequence that the ‘until’ recovery needs to be performed.
1.  If the database is open, shutdown abort
2.  Use OS commands to restore all datafiles to its original locations:
cp -p /usr/backup/RMAN/u01/*.dbf /usr/oradata/u01/IASDB/
cp -p /usr/backup/RMAN/u02/*.dbf /usr/oradata/u01/IASDB/
cp -p /usr/backup/RMAN/u03/*.dbf /usr/oradata/u01/IASDB/
cp -p /usr/backup/RMAN/u04/*.dbf /usr/oradata/u01/IASDB/
etc…
3.  startup mount;
4.  recover automatic database until time ’2004-03-31:14:40:45′;
5.  alter database open resetlogs;
6.  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 time, until sequence or until cancel:
recover automatic database until sequence 120 thread 1; OR
recover database until cancel;

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:
configure controlfile autobackup on;
set the parameter retention policy to the recovery window you want to have,
ie redundancy 2 will keep the last two backups available, after executing delete obsolete commands:
configure retention policy to redundancy 2;
Execute your full backups with the option ‘plus archivelogs’ to include your archivelogs with every backup:
backup database plus archivelog;
Perform daily maintenance routines to maintain on your backup directory the number of backups you need only:
crosscheck backup;
crosscheck archivelog all;
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 rmuser identified by rmuser default tablespace repcat temporary tablespace temp;
4. grant connect, resource, recovery_catalog_owner to rmuser
5. exit
6. rman catalog rmuser/rmuser          # connect to rman catalog as the rmuser
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 <tablespace_name> offline immediate’;
3.   restore datafile 3;
4.   recover datafile 3;
5.   sql ‘alter tablespace <tablespace_name> online’;

Complete Open Database Recovery (when the database is initially closed).Non system tablespace is missing

A user datafile is reported missing when tryin 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 ‘<datafile_name>’ offline;
5.    alter database open;
6.    exit;
7.    rman target /
8.    restore datafile ‘<datafile_name>’;
9.    recover datafile ‘<datafile_name>’;
10.   sql ‘alter tablespace <tablespace_name> 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 <tablespace_name> offline immediate;
3.    alter database create datafile ‘/user/oradata/u01/IASDB/newdata01.dbf;
4.    exit
5.    rman target /
6.    recover tablespace <tablespace_name>;
7.    sql ‘alter tablespace <tablespace_name> 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/IASDB/newdata01.dbf’ as ‘/user/oradata/u02/IASDB/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 /usr/backup/RMAN/user01.dbf /usr/oradata/u02/IASDB/user01.dbf
2.    alter tablespace <tablespace_name> offline immediate;
3.    alter tablespace <tablespace_name> rename datafile ‘/user/oradata/u01/IASDB/user01.dbf’ to ‘/user/oradata/u02/IASDB/user01.dbf’;
4.    rman target /
5.    recover tablespace <tablespace_name>;
6.    sql ‘alter tablespace <tablespace_name> 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 <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 or sequence 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: ’2012-01-04:01:01:10′.

RMAN scenarios

http://itcareershift.com/blog1/2010/11/25/real-life-oracle-dba-scenarios-using-rman-backups-and-troubleshooting-new-oracle-dba-career/
http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmadvre.htm
http://www.reachdba.com/showthread.php?453-RMAN-All-major-Restoration-and-Recovery-Scenarios


Note: When database is in open state then we can not delete datafiles from OS level (File is being used by another User)


--------------------------------------------------------------DataFile removed physcially from OS level and no Backup of that datafile------------------------------------

1) rman backup taken 01-01-12
2) add datafile and add some tables and data on 02-01-12
3) remove datafile from OS level on 02-01-12 no backup till now
4) alter tablespace USR offline;
ORA-01116: error in opening database file 11
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
5) alter tablespace drtbs offline immediate;
select TABLESPACE_NAME,STATUS from dba_tablespaces;
6) select file_id, file_name from dba_data_files where Tablespace_name='USR';
7) RMAN TARGET
8) rman > list of backup datafile 11;
9) list backup of datafile 12; ---- no exist because no backup
10) RMAN> restore tablespace USR;
11) RMAN> recover tablespace USR;
select file_id, file_name from dba_data_files where Tablespace_name='USR';


--------------------------------------------------------------------------Loss of Controlfile-----------------------------

1) select name from v$controlfile;
2) remove controlfile physcially from OS level
3) alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount;

Since we are not using a RMAN catalog we need to set the DBID

RMAN> set dbid=2415549446;

executing command: SET DBID

Restore the controlfile

RMAN> run {
 restore controlfile from autobackup;
 }

error RMAN-06172 because when check show all; controlfile autoback is not present...also not able to configure again becuase of nomount state

set controlfile autobackup format for device type disk to '/orabkp\%F';
restore controlfile from autobackup;


RMAN> alter database mount;
RMAN> recover database;
SQL> alter database open resetlogs;

-------------------------------------recover and open the database if the archive log required for recovery is miss

SQL> recover database until cancel using backup controlfile;
CANCEL
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent


1) Set _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.
2) Startup Mount
3) recover database until cancel using backup controlfile;
4) Alter database open resetlogs.
5) reset undo_management to “manual” in init.ora file.
6) startup database
7) Create new undo tablespace
changed undo_management to “AUTO” and undo_tablespace to “NewTablespace”


------------------------------------------Redo log removed from OS level-----------------------------------

select member from v$Logfile;

rm redo*.log

If one or all of the online redo logfiles are delete then the database hangs and in the alert log file
ARC1: Failed to archive thread 1 sequence 10 (0)

SQL> shutdown immediate;
SQL> startup mount;

SELECT GROUP#,SEQUENCE#,STATUS,FIRST_CHANGE# from v$log;

   GROUP#  SEQUENCE# STATUS           FIRST_CHANGE#
--------- ---------- ---------------- -------------
        1         10 CURRENT             8.6056E+10
        3          9 INACTIVE            8.6056E+10
        2          8 INACTIVE            8.6056E+10

RMAN TARGET /
RMAN> run {
2> set until sequence 10;
3> restore database;
4> recover database;
5>  alter database open resetlogs;
6> }
EXIT

The recovery process creates the online redo logfiles at the operating system level also.
Since we have done an incomplete recover with open resetlogs, we should take a fresh complete backup of the database.

NOTE: Please make sure you remove all the old archived logfiles from the archived area.




------------------------------------------------------Drop tablespace by mistake (drop tablespace test including contents and datafiles;)------Point inTime

DBA realized the mistake;

He will refer alert log for the exact timing when tablespace was dropped.

-------Alert log--------------

Sun Feb  4 10:59:43 2012
drop tablespace test including contents and datafiles
Sun Feb 4 10:59:47 2012
Completed: drop tablespace test including contents and datafiles



SQL> shutdown abort
rman target /

RMAN> RUN
{
STARTUP NOMOUNT
SET UNTIL TIME "TO_DATE ('04-02-07 10:58:00', 'DD-MM-YY HH24:MI:SS')";
RESTORE CONTROLFILE;
ALTER DATABASE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}

select name from v$tablespace;

-----------------------------------------------------------Complete loss of all database files including SPFILE using RMAN

ls -l
rm *.dbf
ls -l *.dbf
ls: *.dbf: No such file or directory
mv spfileopsdba.ora spfileopsdba.org
ls -lt spfile* *SPFILE REMOVED


Database Details
------------------
Database Name=OPSDBA
Machine Name=ITLINUXDEVBLADE07
DBID=1499754868 (select dbid from v$database)
---------------------

Step 1: RECOVERY OF SPFILE

Create spfile.rcv as:
set dbid= 1499754868
run {
startup nomount force  ;
 };

rman target / catalog rman10/rman10@rman10p cmdfile=spfile.rcv

Now restore the spfile

set dbid=1499754868
run {
allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';
restore spfile ;
release channel ch1 ;
}


Step 2: RESTORE OF CONTROLFILES

Same Steps as spfile with the restore command changed. So the new script is

set dbid=1499754868
run {
allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';
restore controlfile ;
release channel ch1 ;
}

Step 3: RESTORE OF DATABASE
SQL> conn sys as sysdba
SQL> alter database mount;

Now get the log sequence number of the database from the catalog database:

select sequence# from rc_backup_redolog where db_name=’OPSDBA’;

RMAN> run {
2> allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';
3> restore database ;
4> recover database until logseq=6; -- GOT FROM THE ABOVE QUERY
5> release channel ch1 ;
6> }
7>


Step 4: alter database open resetlogs;



--------------------------- SYSTEM / SYSAUX / UNDO tablespace is missing

In this case complete recovery is performed, only the system tablespace is missing, so the database can be opened without resetlogs option.

$ rman target /
RMAN> startup mount;
RMAN> restore tablespace SYSTEM;
RMAN> recover tablespace SYSTEM;
RMAN> alter database open;

$ rman target /
RMAN> startup mount;
RMAN> restore tablespace SYSAUX;
RMAN> recover tablespace SYSAUX;
RMAN> alter database open;



------------------------------Non system tablespace is missing, database is up

$ rman target /
RMAN> sql ‘alter tablespace <tbs> offline immediate’ ;
RMAN> restore tablespace <tbs> ;
RMAN> recover tablespace <tbs> ;
RMAN> sql ‘alter tablespace <tbs> online’ ;

To restore/recover only datafile(s)
$ rman target /

RMAN>. sql 'alter database datafile <file#> offline';
RMAN> restore datafile <file#>;
RMAN> recover datafile <file#>;
RMAN> sql 'alter database datafile <file#> online' ;

-------------------------------Non system tablespace is missing,database is closed
sqlplus “/ as sysdba “
startup mount
alter database datafile <file#> offline;
alter database open;
exit;

$rman target /
RMAN> restore datafile <file#>;
RMAN> recover datafile <file#>;
RMAN> sql 'alter tablespace <tablespace_name> online';


-----------------------restore a tablespace to a new location
 rman target / catalog rman/rman@rcat
run {
allocate channel ch1 type disk;
sql 'ALTER TABLESPACE USERS OFFLINE IMMEDIATE';
set newname for datafile '/disk1/oracle/users_1.dbf' to '/disk2/oracle/users_1.dbf';
restore tablespace users;
# make the control file recognize the restored file as current
switch datafile all;
}

RMAN> recover tablespace USERS;
RMAN> sql 'alter tablespace USERS 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.

Pre requisites: All relevant archived logs.

$ rman target /
RMAN> sql ‘alter database datafile <file#> offline’;
RMAN> restore datafile <file#> ;
-- no need to create a blank file, restore command takes care of that.
RMAN> recover datafile <file#>;
RMAN> sql 'alter database datafile <file#> online';

-------------------------------Recovering After the Loss of All Members of an Online Redo Log Group

If a media failure damages all members of an online redo log group, then different scenarios can occur depending on the type of online redo log group affected by the failure and the archiving mode of the database.
If the damaged log group is inactive, then it is not needed for crash recovery; if it is active, then it is needed for crash recovery.
SQL> startup mount

Case-1 If the group is INACTIVE
Then it is not needed for crash recovery
Clear the archived or unarchived group. (For archive status, check in v$log)

1.1 Clearing Inactive, Archived Redo

alter database clear logfile group 1 ;
alter database open ;

1.2 Clearing Inactive, Not-Yet-Archived Redo

alter database clear unarchived logfile group 1 ;
OR
(If there is an offline datafile that requires the cleared log to bring it online, then the keywords UNRECOVERABLE DATAFILE are required. The datafile and its entire tablespace have to be dropped because the redo necessary to bring it online is being cleared, and there is no copy of it. )

alter database clear unarchived logfile group 1 unrecoverable datafile;

Take a complete backup of database.

And now open database:
alter database open ;


Case-2 If the group is ACTIVE

Restore backup and perform an incomplete recovery.
And open database using resetlogs
alter database open resetlogs;

Case-3 If the group is CURRENT

Restore backup and perform an incomplete recovery.
And open database using resetlogs
alter database open resetlogs;


--------------------------
1) OPEN REDO01.LOG (INACTIVE) AND EDIT AND CORRUPT IT

SQL> ALTER SYSTEM SWITCH LOGFILE; -------HANG
alter database clear logfile group 1 ;

ORA-00350: log 1 of instance rkangel (thread 1) needs to be archived
ORA-00312: online log 1 thread 1: 'G:\RKANGEL\REDO01.LOG'

SQL> SHUT IMMEDIATE
SQL> STARTUP MOUNT
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
SQL> ALTER DATABASE OPEN;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;

-------------2) OPEN REDO02.LOG (ACTIVE) AND EDIT AND CORRUPT IT
SQL> ALTER SYSTEM SWITCH LOGFILE; -------HANG
ERROR at line 1:
ORA-03113: end-of-file on communication channel

SQL> STARTUP
ORACLE instance started.

Total System Global Area 6413680640 bytes
Fixed Size                  2267184 bytes
Variable Size            4563404752 bytes
Database Buffers         1828716544 bytes
Redo Buffers               19292160 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: 'G:\RKANGEL\REDO02.LOG'
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 52429318)

ALTER DATABASE CLEAR LOGFILE GROUP 2;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
alter database clear logfile group 2 unrecoverable datafile;
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance rkangel (thread 1)
ORA-00312: online log 2 thread 1: 'G:\RKANGEL\REDO02.LOG'


-----note: For ACTIVE or CURRENT logfile
Restore backup and perform an incomplete recovery.
And open database using resetlogs
alter database open resetlogs;


1) Set _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.
2) Startup Mount
3) recover database until cancel using backup controlfile;
4) Alter database open resetlogs.
5) reset undo_management to “manual” in init.ora file.
6) startup database
7) Create new undo tablespace
changed undo_management to “AUTO” and undo_tablespace to “NewTablespace”



--------------------------------------Recovering a NOARCHIVELOG Database
Restore of a database running in NOARCHIVELOG mode is similar to restore of a database in ARCHIVELOG mode. The main differences are:

Only consistent backups can be used in restoring a database in NOARCHIVELOG mode.

Media recovery is not possible because no archived redo logs exist.

When recovering a NOARCHIVELOG database, specify the NOREDO option on the RECOVER command to indicate that RMAN should not attempt to apply archived redo logs.



----------------------------------------------------------------Remove Temporary datafile from OS level---------------------------------

shut immediate
Remove Temporary datafile from OS level
startup
auotmatically create temp

shut immediate
startup mount
Remove Temporary datafile from OS level
alter database open;
auotmatically create temp


----------------------------------------------------------------Remove UNDO datafile from OS level----------------------------------------

shut immediate
startup mount
Remove UNDO datafile from OS level
alter database open;

ERROR at line 1:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'G:\RKANGEL\UNDOTBS01.ORA'

RMAN> restore tablespace UNDOTBS1;
RMAN> recover tablespace UNDOTBS1;
RMAN> alter database open;


----------------------------------------How to recover dropped / deleted table using RMAN backup----------------------

restore backup on to the UAT machine
export the table from UAT
import into LIVE

1) incomplete recovery ( just before to the drop table) can recover your table from RMAN backup.
or
2) You can recover /duplcate the database to another database with RMAN backup
Export the concerned table from the restored/duplicated database to the database you want.

3) use flashback (in oracle 10g) for indivisual table recovery purpose.
Oracle 10g makes life easier with the ability to recover a dropped table similar to recovering a file from a Windows Recycle Bin.
And in case if this on 10g,
Eg: You dropped the DEPT table, which belongs to the USER_DATA tablespace
You can use the following command to recover a dropped table:

FLASHBACK TABLE <table_name> TO BEFORE DROP;



4) RMAN TSPITR is most useful for recovering the following:
An erroneous DROP TABLE or TRUNCATE TABLE statement
A table that has become logically corrupted
An incorrect batch job or other DML statement that has affected only a subset of the database
A logical schema to a point different from the rest of the physical database when multiple schemas exist in separate tablespaces of one physical database

--------------------------------------------------------Point in time recovery using RMAN (until a log sequence number) ----------------delete some data from table by mistake

Someone delete all data from a table at 1:47 PM (delete from myobjects) SELECT * FROM DBA_AUDIT_TRAIL;

windows dir d:\archive
20/09/2012  01:44 PM             1,024 ARC0000000010_0794497245.0001
20/09/2012  01:47 PM        41,461,248 ARC0000000011_0794497245.0001


archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/ORACLE/opsdba/arch
Oldest online log sequence     21
Next log sequence to archive   23
Current log sequence           23
--Note the current log sequence number (22)

We need to determine the log sequence we need to recover until
select sequence#,first_change#, to_char(first_time,'HH24:MI:SS') from v$log order by 3

SEQUENCE# FIRST_CHANGE# TO_CHAR(
--------- ------------- --------
       21    8.6056E+10 13:48:16
       22    8.6056E+10 13:48:22
       23    8.6056E+10 13:48:29

Log sequence 21 was first written to at 1:48 PM so we should recover to a log sequence before this – i.e sequence# 10 (from window dir )


SQL> shutdown immediate;
SQL> startup mount;
RMAN> run {
set until sequence=10;  >>> add one to the sequence number we have to recover until
restore database;
recover database;
}
alter database open resetlogs;

ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: 'G:\RKANGEL\SYSTEM01.ORA'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/20/2012 14:15:38
ORA-01147: SYSTEM tablespace file 1 is offline
ORA-01110: data file 1: 'G:\RKANGEL\SYSTEM01.ORA'

SQL> ALTER DATABASE DATAFILE 1 ONLINE;
SQL> RECOVER DATABASE;

ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.


select count(*) from myobjects;


---------------------------------------------------Recover Dropped Table from Recyclebin using Flashback Table-------------------------

Oracle Flashback Table enables you to restore a table to its state as of a previous point in time. It provides a fast, online solution for recovering a table that has been accidentally modified or deleted by a user or application. In many cases, Oracle Flashback Table eliminates the need for you to perform more complicated point-in-time recovery operations.

Oracle Flashback Table:

Restores all data in a specified table to a previous point in time described by a timestamp or SCN.

Performs the restore operation online.

Automatically maintains all of the table attributes, such as indexes, triggers, and constraints that are necessary for an application to function with the flashed-back table.

Maintains any remote state in a distributed environment. For example, all of the table modifications required by replication if a replicated table is flashed back.

Maintains data integrity as specified by constraints. Tables are flashed back provided none of the table constraints are violated. This includes any referential integrity constraints specified between a table included in the FLASHBACK TABLE statement and another table that is not included in the FLASHBACK TABLE statement.

Even after a flashback operation, the data in the original table is not lost. You can later revert to the original state.

FLASHBACK TABLE <table_name> TO BEFORE DROP;

Some other variations of the flashback database command include.

FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;
FLASHBACK DATABASE TO SCN my_scn;
FLASHBACK DATABASE TO BEFORE SCN my_scn;

--------------------------------------------------Recover deleted Table Data from Recyclebin using Flashback Table---------------------

On Oracle Database 11g (10gR2...), we can rewind one or more tables back to their contents at a previous time without affecting other database objects.

Before we use Flashback Table, We must enable row movement on the table. because rowids will change after the flashback.

Example: Flashback the table back to previous time using SCN


select count(*) from LDBO.test;
COUNT(*)
----------
68781

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
1584494

SQL> delete from LDBO.test where rownum <= 50000;

50000 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from LDBO.test;

COUNT(*)
----------
18781

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
1587106

Enable row movement:

SQL> alter table LDBO.test enable row movement;

Table altered.

SQL> FLASHBACK TABLE LDBO.test to scn 1584494;

Flashback complete.

SQL> select count(*) from LDBO.test;


QL> alter table LDBO.test disable row movement;

Table altered.


We can rewind the table back to previous time using timestamp:

SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2009/08/30 17:01:09

SQL> delete from LDBO.test ;

68781 rows deleted.

SQL> commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE
-------------------
2009/08/30 17:03:18

SQL> select count(*) from LDBO.test;

COUNT(*)
----------
0

SQL> alter table LDBO.test enable row movement;

Table altered.

SQL> flashback table LDBO.test to timestamp TO_TIMESTAMP('2009/08/30 17:01:09','YYYY/MM/DD HH24:MI:SS');

Flashback complete.

SQL> select count(*) from LDBO.test;

COUNT(*)
----------
68781

--------------------------------------------------------------------------------------------Flashback Table recover table to different table----------------------

FLASHBACK TABLE test TO BEFORE DROP RENAME TO test2;

flashback table LDBO.test to timestamp TO_TIMESTAMP('2009/08/30 17:01:09','YYYY/MM/DD HH24:MI:SS') RENAME TO test2;

General Errors

http://www.articles.freemegazone.com/oracleErrors.php

ORA12518: TNS:listener could not hand off client connection
ORA04030: out of process memory when trying to allocate
ORA00060: deadlock detected while waiting for resource
ORA00054: resource busy and acquire with NOWAIT specified
ORA00600: intemal error code, arguments: [numl, [?], [?], [?], [?], [?]
ORA00376: ORA01110 recovery from lost datafile
ORA01925: maximum of 148 enabled roles exceeded
ORA01000: maximum open cursors exceeded
ORA01180 ORA01110 RMAN recover on different location
ora 19870 ora 19505 ora 27041 RMANRecover Database witn missing Archieve Logs.
ORA12545: Connect failed because target host or object does not exist
ORA00942: table or view does not exist
ORA03113: endoffile on communication channel
ORA06502: PL/SQL: numeric or value error
ORA04031: unable to allocate num bytes of shared memory num, num, num
ORA01756: quoted string not properly terminated
ORA29283: invalid file operation
ORA00020: maximum number of processes num exceeded
ORA12203: TNS:unable to connect to destination
ORA12154: TNS:could not resolve the connect identifier specified
ORA01017: invalid username/password; logon denied
ORA01403: no data found


----------ORA-12518: TNS:listener could not hand off client connection------------------
ping 10.100.0.65 -t

tnsping 10.100.0.65 10

lnsrctl startus

database server memory were enough for new connection

check virtual memory

--solution---
size of listener.log is increased to 1GB

select * from v$resource_limit order by 2 desc;
kill sniped user session


Turn On Listener Tracing
LOGGING_LISTENER = on
TRACE_LEVEL_LISTENER=16
TRACE_FILE_LISTENER=listener.trc
TRACE_DIRECTORY_LISTENER=d:\oracle\product\10.2.0\db_1\network\trace


--------------------------------ORA-04030: out of process memory when trying to allocate

Do session, memory monitoring


Windows Server 2003 SP2
Oracle 10g
/PAE /3GB
RAM 8GB
instances:6

Memory Usage of Oracle.exe for current FY goes upto 2.5 GB

1) exclude database folders from virus scan

2) decrease sga_max_size for all other instances

3)Schedule to kill sniped sessions


----------------------ORA-00060: deadlock detected while waiting for resource

A deadlock is the situation where you have two, or more, Oracle "sessions" (well, transactional "states") competing for mutually locked resources. Oracle deals with deadlocks pretty much immediately by raising an exception (ORA-00060) in one of the sessions.


Trying to execute a statement, but your session was deadlocked because another session had the same resource locked. The statement(s) that you tried to execute have been rolled back.

1. You can wait a few minutes and try to re-execute the statement(s) that were rolled back.
2. You can execute a ROLLBACK and re-execute all statements since the last COMMIT was executed.



select do.object_name,
row_wait_obj#, do.data_object_id, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, do.data_object_id, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=543
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;



select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;


select session_id,oracle_username,process,
decode(locked_mode,
2, 'row share',
3, 'row exclusive',
4, 'share',
5, 'share row exclusive',
6, 'exclusive', 'unknown') "Lockmode"
from V$LOCKED_OBJECT;


Session 551 is blocking 2 other sessions

select * from v$session where sid='551'

select * from v$lock;

select sid, serial#,status from v$session where username = 'USER';

select serial#,status from v$session where sid='Session id';

alter system kill session 'SID,SERIAL#';


The session should now be killed and the lock SHOULD release.

Rechecking "v$locked_object" will tell you this. If the lock does not
immediately release, there may be a rollback occuring.

To check for rollback:

select used_ublk from v$transaction where ADDR=;


-------------------------------- ORA-00054: resource busy and acquire with NOWAIT specified

Trying to execute a LOCK TABLE or SELECT FOR UPDATE command with the NOWAIT keyword but the resource was unavailable.
1. Wait and try the command again after a few minutes.
2. Execute the command without the NOWAIT keyword.



---------------------------------------------ORA-00600: intemal error code, arguments: [numl, [?], [?], [?], [?], [?]
ORA-600 is an internal error generated by the generic kernel code of the Oracle RDBMS software. It is different from other Oracle errors in many ways. The following is a list of these differences:

1. An ORA-600 error may or may not be displayed on the screen. Therefore, screen output should not be relied on for capturing information on this error. Information on ORA-600 errors are found in the database alert and trace files. We recommend that you check these files frequently for database errors. (See the Alert and Trace Files section for more information.)

2. Each ORA-600 error comes with a list of arguments They usually enclosed in square brackets and follow the error on the same line for example:

ORA-00600 [14000][51202][1][51200][][]

Each argument has a specific meaning which can only be interpreted by an Oracle support analyst. The arguments may also change meaning from version to version therefore customers are not advised to memorize them.

3. Every occurrence of an ORA-600 should be reported to Oracle Support. Unlike other errors, you can not find help text for these errors. Only Oracle technical support should diagnose and take actions to prevent or resolve damage to the database.

4. Each ORA-600 error generates a database trace file.

Possible causes

Possible causes include:

* time-outs,
* file corruption,
* failed data checks in memory, hardware, memory, or I/O messages,
* incorrectly restored files
* a SELECT FROM DUAL statement in PL/SQL within Oracle Forms (you have to use SELECT FROM SYS.DUAL instead!)

How to fix it

Contact Oracle Support with the following information:

* events that led up to the error
* the operations that were attempted that led to the error
* the conditions of the operating system and database at the time of the error
* any unusual circumstances that occurred prior to receiving the ORA-00600 message.
* contents of any trace files generated by the error
* the relevant portions of the Alert file
* in Oracle Forms PL/SQL, use SELECT FROM SYS.DUAL to access the system "dual" table

------------------ORA-00376---------ORA-01110-------recovery from lost datafile
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: 'D:\VIKRAM\ORADATA\TEST2\USERS01.DBF'


sql>startup
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF’
RMAN> restore datafile 4;
RMAN> recover datafile 4;
RMAN> alter database open;

-----If the database is already open when datafile corruption is detected, you can recover the datafile without shutting down the database. The only additional step is to take the relevant tablespace offline before starting recovery. In this case you would perform recovery at the tablespace level.
RMAN> sql ‘alter tablespace USERS offline immediate’;
RMAN> recover tablespace USERS;
RMAN> sql ‘alter tablespace USERS online’;









-----------------------ORA-01925: maximum of 148 enabled roles exceeded ----------------

Increase max_enabled_roles and warm start the database.

or revoke roles from user
-------------------------------ORA-01000: maximum open cursors exceeded
Cause: A host language program attempted to open too many cursors. The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user.
Action: Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS, and then restart Oracle.


------------RMAN recover on different location---------ORA-01180--ORA-01110--------------------------
RMAN> restore database;


ORA-01180: can not create datafile 1
ORA-01110: data file 1: 'E:\SNSD1011\SYSTEM01.ORA'

crosscheck
delete force obsolete;
delete expired archivelog all;
crosscheck backup of database;
delete expired backup;

RMAN> catalog backuppiece 'E:\archive1011\sns1011\0ULAP4RC_1_1';
SQL> alter database rename file 'E:\SNSD1011\REDO01.ORA' TO 'D:\SNSD1011\REDO01.ORA' ;

run { set until sequence
set newname for datafile 1 to 'D:\SNSD1011\SYSTEM01.ORA' ;
set newname for datafile 1 to 'D:\SNSD1011\UNDOTBS01.ORA' ;
set newname for datafile 1 to 'D:\SNSD1011\SYSAUX01.ORA' ;
set newname for datafile 1 to 'D:\SNSD1011\INDX01.ORA' ;
set newname for datafile 1 to 'D:\SNSD1011\USERS01.ORA' ;




---------------------RMAN--------Recover Database witn missing Archieve Logs.----------ora 19870 ora 19505 ora 27041---------

I am trying the restore my old database but due to missing of one archieve log. i m not able to restore and recover from rman

Not able to open database

ora 19870 ora 19505 ora 27041 osd 04002

rman 00571 rman 00569 rman 03002 rman 06053 rman 06025


Solutions
shutdown immediate;
add into init.ora _allow_resetlogs_corruption=true
startup mount;
sql>recover database until cancel using backup controlfile;

Specify log: {=suggested | filename | AUTO | CANCEL}

CANCEL

Alter database open resetlogs



------------------ORA-01034: Oracle not available

Oracle is not started up. Possible causes may be that either the SGA requires more space than was allocated for it or the operating-system variable pointing to the instance is improperly defined.

1. Refer to accompanying messages for possible causes and correct the problem mentioned in the other messages.
2. If Oracle has been initialized, then on some operating systems, verify that Oracle was linked correctly.
3. See the platform specific Oracle documentation.

--------------------ORA-12545: Connect failed because target host or object does not exist

The address specified is not valid, or the program being connected to does not exist.
1. Ensure the ADDRESS parameters have been entered correctly.
2. Ensure that the executable for the server exists.
3. If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the host name to a numeric IP address and try again.


------------------------ORA-00942: table or view does not exist

1. SQL statement is executed that references a table or view that either does not exist.
2. You do not have access to the table or view, or the table or view belongs to another schema and you didn't reference the table by the schema name.


1. If this error occurred because the table or view does not exist, you will need to create the table or view.
2. If this error occurred because you do not have access to the table or view, you will need to have the owner of the table/view, or a DBA grant you the appropriate privileges to this object.
3. If this error occurred because the table/view belongs to another schema and you didn't reference the table by the schema name, you will need to rewrite your SQL to include the schema name.

---------------ORA-03113: end-of-file on communication channel

You encountered an unexpected end-of-file on the communication channel.

1. Check for network problems and review the SQL*Net setup.
2. Look in the alert.log file for any errors.
3. Test to see whether the server process is dead and whether a trace file was generated at failure time.



------------------------------------------ORA-06502: PL/SQL: numeric or value error
The executed statement resulted in an arithmetic, numeric, string, conversion, or constraint error. Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.


------------------------------------------23 ORA-04031: unable to allocate num bytes of shared memory num, num, num Tried to use more shared memory than was available. SGA private memory has been exhausted.

1. Reduce your use of shared memory.
2. Increase the SHARED_POOL_SIZE initialization parameter in the initialization file.
3. Use the DBMS_SHARED_POOL package to pin large packages.


------------------------------------------24 ORA-01756: quoted string not properly terminated A quoted string is not terminated with a single quote mark (') Insert the closing quote and retry the statement.


------------------------------------------25 ORA-29283: invalid file operation An attempt was made to read from a file or directory that does not exist, or file or directory access was denied by the operating system. Verify file and directory access privileges on the file system, and if reading, verify that the file exists.

------------------------------------------26 ORA-00020: maximum number of processes num exceeded All process state objects are in use.

1. Wait a few minutes and try to re-execute the statement(s).
2. Shut down Oracle, increase the PROCESSES parameter in the initialization parameter file, and restart Oracle.

------------------------------------------27 ORA-12203: TNS:unable to connect to destination

1. Invalid address specified or destination is not listening.
2. This error can also occur because of underlying network or network transport problems.


1. Verify that the net service name you entered was correct.
2. Verify that the ADDRESS portion of the connect descriptor which corresponds to the net service name is correct.
3. Ensure that the destination process (for example the listener) is running at the remote node.

----------------------------------------ORA-12154: TNS:could not resolve the connect identifier specified

You tried to connect to Oracle, but the service name is either missing from the TNSNAMES.ORA file or is incorrectly defined

1. Make sure that the TNSNAMES.ORA file exists and is in the correct directory.
2. Make sure that the service name that you are connecting to is included in the TNSNAMES.ORA file and that it is correctly defined.
3. Make sure that there are no syntax errors in the TNSNAMES.ORA file. For example, if there are unmatched brackets in the file, the file will be rendered unusable.




------------------------------------------28 ORA-01017: invalid username/password; logon denied Logging into Oracle with an invalid username/password combination. Enter a valid username and password combination in the correct format. If the username and password are entered together, the format is: username/password



------------------------------------------29 ORA-01403: no data found

1. Executing a SELECT INTO statement and no rows were returned.
2. Referencing an uninitialized row in a table.
3. Reading past the end of file with the UTL_FILE package.

Terminate processing of the data.


30 ORA-01033: ORACLE initialization or shutdown in progress An attempt was made to log on while Oracle is being started up or shutdown Wait a few minutes. Then retry the operation.




----------------------------------------????????????/
alter database open resetlogs;
ORA-01153: an incompatible media recovery is active
SQL> alter database recover cancel;
now
ORA-01112: media recovery not started


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

http://oracle.ittoolbox.com/groups/technical-functional/oracle-db-l/ora24324-service-handle-not-initialized-ora01041-internal-error-hostdef-extension-doesnt-exist-2771079

ORA-01092: ORACLE instance terminated. Disconnection forced

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

ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
ORA-24324 ORA-01041

--------
ORA-01033: ORACLE initialization or shutdown in progress
-----------------

Common Oracle error codes

ORA-00001 Unique constraint violated. (Invalid data has been rejected)

ORA-00600 Internal error (contact support)

ORA-03113 End-of-file on communication channel (Network connection lost)

ORA-03114 Not connected to ORACLE

ORA-00942 Table or view does not exist

ORA-01017 Invalid Username/Password

ORA-01031 Insufficient privileges

ORA-01034 Oracle not available (the database is down)

ORA-01403 No data found

ORA-01555 Snapshot too old (Rollback has been overwritten)


ORA-12154 TNS:could not resolve service name"
ORA-12203 TNS:unable to connect to destination"
ORA-12500 TNS:listener failed to start a dedicated server process"
ORA-12545 TNS:name lookup failure"
ORA-12560 TNS:protocol adapter error"
ORA-02330 Package error raised with DBMS_SYS_ERROR.RAISE_SYSTEM_ERROR


-