Archive

Archive for the ‘Backup & Recovery’ Category

ORA-00439: feature not enabled: Flashback Database

March 13, 2011 1 comment

Some feature is not enabled in oracle database standard edition, as a result we can not use flashback feature in Oracle Database if you are use in standard edition of oracle.

Following Error Message show in Oracle 10g standard edition

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-00439: feature not enabled: Flashback Database

Version of Oracle

SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.1.0 - Production
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Features are available in standard edition

SQL> SELECT Parameter,Value FROM V$OPTION Where Value = 'TRUE';
PARAMETER                                                        VALUE                                                           
----------------------------------------------------------------------
Objects                                                          TRUE                                                            
Connection multiplexing                                          TRUE                                                            
Connection pooling                                               TRUE                                                            
Database queuing                                                 TRUE                                                            
Incremental backup and recovery                                  TRUE                                                            
Instead-of triggers                                              TRUE                                                            
Parallel load                                                    TRUE                                                            
Proxy authentication/authorization                               TRUE                                                            
Plan Stability                                                   TRUE                                                            
Transparent Application Failover                                 TRUE                                                            
Sample Scan                                                      TRUE                                                            
Java                                                             TRUE                                                            
OLAP Window Functions                                            TRUE

So flashback database is disabled in standard edition or in standard one  edition. To be able to flashback on you have to use enterprise edition.

Cancel-Based Recovery

June 22, 2010 Leave a comment

A cancel-based recovery is a type of user-managed incomplete recovery that is performed by specifying the UNTIL CANCEL clause with the RECOVER command (a SQL*Plus command that is used to recover a database). The UNTIL CANCEL clause specifies that the recovery process will continue until the user manually cancels the recovery process issuing the CANCEL command.

In a cancel-based incomplete recovery, the recovery process proceeds by prompting the user with the suggested archived redo log files’ names. The recovery process stops when the user specifies CANCEL instead of specifying an archived redo log file’s name. If the user does not specify CANCEL, the recovery process automatically stops when all the archived redo log files have been applied to the database.

A cancel-based recovery is usually performed when the requirement is to recover up to a particular archived redo log file. For example, if one of the archived redo log files required for the complete recovery is corrupt or missing, the only option is to recover up to the missing archived redo log file.

Recovery Scenario Preferred Recovery Method
Some important table is dropped Oracle Time-based Recovery based Recovery
Some bad data is committed in a table Oracle Time-based Recovery based Recovery
Lost archive log results in failure of  complete recovery Oracle Cancel-based Recovery
Backup control file does not know anything about the arhivelogs Oracle Cancel-based Recovery
All unarchived Redo Logs and datafiles are lost Oracle Cancel-based Recovery
Recovery is needed up to a specific archived log file Oracle Cancel-based Recovery
Recovery through Resetlogs when media failure occurs before backup completion. Oracle Change-based Recovery
A Tablespace is dropped Recovery with a backup control file

1. Start SQL*Plus and connect to Oracle with administrator privileges. For example, enter:

sqlplus '/ AS SYSDBA'

2. Start a new instance and mount the database:

STARTUP MOUNT

3. Begin cancel-based recovery by issuing the following command:

RECOVER DATABASE UNTIL CANCEL

If you are using a backup control file with this incomplete recovery, then specify the USING BACKUP CONTROLFILE option in the RECOVER command.

RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE

4. Oracle applies the necessary redo log files to reconstruct the restored datafiles. Oracle supplies the name it expects to find from LOG_ARCHIVE_DEST_1 and requests you to stop or proceed with applying the log file. Note that if the control file is a backup, then you must supply the names of the online logs if you want to apply the changes in these logs.

5. Continue applying redo log files until the last log has been applied to the restored datafiles, then cancel recovery by executing the following command:

CANCEL

Oracle returns a message indicating whether recovery is successful. Note that if you cancel recovery before all the datafiles have been recovered to a consistent SCN and then try to open the database, you will get an ORA-1113 error if more recovery is necessary for the file. You can query V$RECOVER_FILE to determine whether more recovery is needed, or if a backup of a datafile was not restored prior to starting incomplete recovery.

6. Open the database in RESETLOGS mode. You must always reset the online logs after incomplete recovery or recovery with a backup control file. For example, enter:

ALTER DATABASE OPEN RESETLOGS;

For More Detail please visit here

How to backup and restore the Oracle Control File

January 2, 2010 Leave a comment

There are two approaches to take Backup of control file in Oracle:

  • Backing it up in a binary format
  • Backing it up in a human readable format
Syntax: 
alter database backup controlfile to ['filename' | trace]

This command comes in two versions. One backs up the control file in a binary format while the other backs it up in a human readable form. It is required if the database is running in archive log mode and a structural change was made to the database.

Backing it up in a binary format:

You can generate a binary image of the Control File

SQL Code: 
alter database backup controlfile
               to '/some/arbitrary/path';
alter database backup controlfile
               to '/some/arbitrary/path' reuse;

Backing it up in a human readable format:

You generate a text file script which will re-generate a Control File when run as a SQL script. The file name will be something like ‘ora_<some numbers>.trc’

SQL Code:
alter database backup controlfile to trace;

Check udump directory for text based controlfile.

Syntax:
alter database backup controlfile to trace
               as '/some/arbitrary/path';
alter database backup controlfile to trace
               as '/some/arbitrary/path' reuse;

If the human readable form is chosen, the file can be made usable if the comments at the beginning are removed and replaced with a connect / as sysdba. If the init.ora file is not at its default location, it has to be appended with a pfile=…. in the line containing a startup.

Restore the control file

The trouble starts when you attempt to restore the binary version of the Control File backup. Because it was an exact, binary copy of a Control File, its SCN number will not agree with the SCN in the headers of all the data files -basically, the Master Clock is out of whack. You therefore have to issue the following command

RECOVER DATABASE USING BACKUP CONTROLFILE;

It tells the system not to pay too much attention to the SCN of the Control File. Unfortunately, after you issue that command (and following any recovery that it might cause to take place), you must open the database with the following command:

ALTER DATABASE OPEN RESETLOGS;

You can also use RMAN script to restore and recover control file to all locations specified in the parameter file then restore the database, using that control file:

SQL Code
STARTUP NOMOUNT;
RUN
{
  ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
  RESTORE CONTROLFILE;
  ALTER DATABASE MOUNT;
  RESTORE DATABASE;
}

Restore control file to default location:

The default location is defined by CONTROL_FILES parameter of pfile/spfile. If you don’t specify any location while restoring your control file then the control file will be restored to the location set by CONTROL_FILES parameter.

RMAN Code: 
RMAN> SET DBID 7887865467
RMAN> RUN {
      RESTORE CONTROLFILE FROM AUTOBACKUP;
     }

Restore of the Control File from Control File Autobackup

For the use who are using  recovery catalog, you can restore your control file from an autobackup. The database must be in a NOMOUNT state. And you have to set DBID. RMAN uses the autobackup format and DBID to determine where to find for the control file autobackup.

RMAN Code:
RMAN> SET DBID 7887865467
RMAN> RUN {
        SET CONTROLFILE AUTOBACKUP FORMAT
        FOR DEVICE TYPE DISK TO 'autobackup_format';
        RESTORE CONTROLFILE FROM AUTOBACKUP;
      }

Restoring a Control File When Using a Recovery Catalog

The recovery catalog contains a complete record of the backups of Database, including backups of the control file. Therefore, It is not necessary to mention the DBID or control file autobackup format.

RMAN Code:
$rman TARGET / CATALOG cpdb/cpdb
RMAN> RESTORE CONTROLFILE;

Restore of the Control File From a Known Location

If you know the backuppiece of controlfile or any copy then simply you can use,

RMAN Code: 
RMAN> RESTORE CONTROLFILE from 'filename';

Restore of the Control File to a New Location

In prior cases RMAN restore the control file to the location specified by CONTROL_FILES parameter of the spfile or pfile.If you want to restore the control file to another location use,

RMAN Code:
RMAN> RESTORE CONTROLFILE TO '<new_location>';

It is also possible to change CONTROL_FILES parameter and then perform RESTORE CONTROLFILE to change location.

Limitations When Using a Backup Control File

After Complete the restore and recover the control file  using a backup control file, It is mandatory  run RECOVER DATABASE and perform an OPEN RESETLOGS on the database. Where SCN is change.

 

I have Take a backup using RMAN in following way.

Backup Control file using RMAN

 
RMAN nocatalog target /
RMAN> BACKUP CURRENT CONTROLFILE;

Scenario: I have deleted the control files now tiring to start the database; I have got the following error message successfully.

SQL> STARTUP
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1247900 bytes
Variable Size              67110244 bytes
Database Buffers           96468992 bytes
Redo Buffers                2945024 bytes
ORA-00205: error in identifying control file, check alert log for more info

Recover Control file using RMAN

RMAN nocatalog target /
RMAN> Restore controlfile from autobackup;
Or
RMAN> RUN {
RESTORE CONTROLFILE FROM AUTOBACKUP;
}
RMAN> alter database mount;
RMAN> recover database;
RMAN> alter database open resetlogs;


Scenario: incomplete database recovery
SQL> Select name from v$database;
 
NAME
---------
CJPROD
 
SQL> select status from v$instance;
 
STATUS
------------
MOUNTED
 
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\CJPROD\SYSTEM01.DBF'
 
 
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
 
 
SQL> recover database using backup controlfile until cancel
ORA-00279: change 551523 generated at 03/13/2011 00:15:03 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\CJPROD\ARCHIVELOG\2011_03_13\O1_MF_1_3_%U_.ARC
ORA-00280: change 551523 for thread 1 is in sequence #3
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\oracle\product\10.2.0\oradata\cjprod\REDO02.LOG
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
 
Database altered.
 

Related Link: https://tamimdba.wordpress.com/category/oracle10g/controlfile-auto-backup/

RMAN Retore & Recover

December 16, 2009 Leave a comment

Use the RMAN RESTORE command to restore the following types of files from copies on disk or backups on other media:

· Database (all datafiles)
· Tablespaces
· Control files
· Archived redo logs
· Server parameter files

Automates the procedure for restoring files. When you issue a RESTORE command, RMAN restore the correct backups and copies to either:

· The default location, overwriting the old files with the same name
· A new location, which you can specify with the SET NEWNAME command

RMAN Backup Clause Syntax

   RECOVER [DEVICE TYPE deviceSpecifier [, deviceSpecifier]...]
               recoverObject [recoverOptionList];

Steps for media recovery Using RMAN

Step 01:

Mount or open the database. Mount the database when performing whole database recovery, or open the database when performing online tablespace recovery.

STARTUP FORCE MOUNT;

Step 02:

To perform incomplete recovery, use the SET UNTIL command to specify the time, SCN, or log sequence number at which recovery terminates. Alternatively, specify the UNTIL clause on the RESTORE and RECOVER commands.

Step 03:

Restore the necessary files with the RESTORE command.

Step 04:

Recover the datafiles with the RECOVER command.

Step 05:

Place the database in its normal state. For example, open it or bring recovered tablespaces online.

Restore and recover the whole database

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

Script Code:
STARTUP NOMOUNT;
RUN
{
   ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
   RESTORE DATABASE;
   ALTER DATABASE MOUNT;   
   RECOVER DATABASE;
}

Restore and recover a tablespace

RMAN> SQL 'ALTER TABLESPACE users OFFLINE';
RMAN> RESTORE TABLESPACE users;
RMAN> RECOVER TABLESPACE users;
RMAN> SQL 'ALTER TABLESPACE users ONLINE';

Restore and recover a datafile

RMAN> SQL 'ALTER DATABASE DATAFILE 32 OFFLINE';
RMAN> RESTORE DATAFILE 32;
RMAN> RECOVER DATAFILE 32;
RMAN> SQL 'ALTER DATABASE DATAFILE 32 ONLINE';

Restore and recover the Control file from Backup

Restore the control file, (to all locations specified in the parameter file) then restore the database, using that control file:

STARTUP NOMOUNT;
RUN
{
   ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
   RESTORE CONTROLFILE;
   ALTER DATABASE MOUNT;
   RECOVER DATABASE;
}

Create a new control file

If all control file copies are lost, you can create a new control file using the NORESETLOGS option and open the database after doing media recovery. An existing standby database instance can generate the script to create a new control file by using the following statement

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS;

Recovery from the Loss of an Online Redo Log File

To add a new member to a redo log group, issue the following statement:

SQL> ALTER DATABASE ADD LOGFILE MEMBER 'log_file_name' REUSE TO GROUP n

Disaster Recovery

In a disaster situation where all files are lost you can only recover to the last SCN in the archived redo logs. Beyond this point the recovery would have to make reference to the online redo logs which are not present. Disaster recovery is therefore a type of incomplete recovery.

Step 01: Connect to RMAN

$rman catalog=rman/rman@orcl target=sys/oracle@orcl

Step 02: Recover the control file if needed.

RMAN> startup nomount;
RMAN> restore controlfile;
RMAN> alter database mount;

Step 03: Collect the last SCN using SQL*Plus as SYS

SQL> SELECT archivelog_change#-1 FROM v$database;

ARCHIVELOG_CHANGE#-1
--------------------
             1203813

Step 04: Restore and Recover database using RMAN.

 
RMAN> run {
        set until scn 1203813;
        restore database;
        recover database;
        alter database open resetlogs;
        }

Restore Validation

Restore Validation confirms that a restore could be run, by confirming that all database files exist and are free of physical and logical corruption, this does not generate any output.

RMAN> RESTORE DATABASE VALIDATE;
Categories: RMAN Recover Tags:

Backup Using RMAN

December 16, 2009 Leave a comment

Types of Files that can be Backup Using RMAN

The BACKUP command can back up of Database, which includes all data files as well as the current control file and current server parameter.

Following type of backup can be perform by RMAN

  • Tablespaces (except for locally-managed temporary tablespaces)
  • Current datafiles
  • Current control file
  • Archived redo logs
  • Current server parameter file
  • Backup sets

RMAN does not back up the following:

  • Online redo logs
  • Transported tablespaces before they have been made read/write
  • Client-side initialization parameter files or noncurrent server parameter files

RMAN Backup Clause Syntax

   BACKUP FULL Options
   BACKUP FULL AS (COPY | BACKUPSET) Options
   BACKUP INCREMENTAL LEVEL [=] integer Options
   BACKUP INCREMENTAL LEVEL [=] integer AS (COPY | BACKUPSET) Options
   BACKUP AS (COPY | BACKUPSET) Options
   BACKUP AS (COPY | BACKUPSET) (FULL | INCREMENTAL LEVEL [=] integer) Options
Database Backup

Back up the database, and then the control file which contains a record of the backup

RMAN> BACKUP DATABASE;
RMAN> BACKUP CURRENT CONTROLFILE;
Data files Backup
RMAN> BACKUP AS BACKUPSET DATAFILE
        'ORACLE_HOME/oradata/users01.dbf',
        'ORACLE_HOME/oradata/tools01.dbf';
Backup all data files in the database

Bit-for-bit copies, created on disk

RMAN> BACKUP AS COPY DATABASE;
Backup archive logs
RMAN> BACKUP ARCHIVELOG COMPLETION TIME BETWEEN 'SYSDATE-30' AND 'SYSDATE';
Backup tablespace
RMAN> BACKUP TABLESPACE system, users, tools;
Backup controlfile
RMAN> BACKUP CURRENT CONTROLFILE TO '/backup/cntrlfile.copy';
Backup Server parameter file
RMAN> BACKUP SPFILE;
Backup everything
RMAN> BACKUP BACKUPSET ALL;

Create a consistent backup and keep the backup for 1 year:

Exempt from the retention policy

RMAN> SHUTDOWN;
RMAN> STARTUP MOUNT;
RMAN> BACKUP DATABASE UNTIL 'SYSDATE+365' NOLOGS;

Backup Validation confirms that a backup could be run, by confirming that all database files exist and are free of physical and logical corruption, this does not generate any output.

RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

Multilevel Incremental Backups

RMAN can create multilevel incremental backups. Each incremental level is denoted by an integer, for example, 0, 1, 2, and so forth. A level 0 incremental backup, which is the base for subsequent incremental backups, copies all blocks containing data. The only difference between a level 0 backup and a full backup is that a full backup is never included in an incremental strategy.

If no level 0 backup exists when you run a level 1 or higher backup, RMAN makes a level 0 backup automatically to serve as the base.

The benefit of performing multilevel incremental backups is that RMAN does not back up all block all of the time.

Differential Incremental Backups

In a differential level n incremental backup, RMAN backs up all blocks that have changed since the most recent backup at level n or lower.

For example, in a differential level 2 backup, RMAN determines which level 2 or level 1 backup occurred most recently and backs up all blocks modified after that backup. If no level 1 is available, RMAN copies all blocks changed since the base level 0 backup. If no level 0 backup is available, RMAN makes a new base level 0 backup for this file.

Use Command for incremental Level Backup

RMAN> backup incremental level 0 database tag="SUNDAY";
RMAN> backup incremental level 3 database tag="MONDAY";
RMAN> backup incremental level 3 database tag="TUESDAY";
RMAN> backup incremental level 3 database tag="WEDNESDAY";
RMAN> backup incremental level 2 database tag="THURSDAY";
RMAN> backup incremental level 3 database tag="FRIDAY";
RMAN> backup incremental level 3 database tag="SATURDAY";

Cumulative Incremental Backups

RMAN provides an option to make cumulative incremental backups at level 1 or greater. In a cumulative level n backup, RMAN backs up all the blocks used since the most recent backup at level n-1 or lower.

For example, in cumulative level 2 backups, RMAN determines which level 1 backup occurred most recently and copies all blocks changed since that backup. If no level 1 backups are available, RMAN copies all blocks changed since the base level 0 backup.

Cumulative incremental backups reduce the work needed for a restore by ensuring that you only need one incremental backup from any particular level. Cumulative backups require more space and time than differential backups, however, because they duplicate the work done by previous backups at the same level.

Use Command for Cumulative Level Backup

RMAN> backup incremental level=0 database tag='base';
RMAN> backup incremental level=2 cumulative database tag='monday';
RMAN> backup incremental level=2 cumulative database tag='tuesday';
RMAN> backup incremental level=2 cumulative database tag='wednesday';
RMAN> backup incremental level=2 cumulative database tag='thursday';
RMAN> backup incremental level=2 cumulative database tag='friday';
RMAN> backup incremental level=2 cumulative database tag='saturday';
RMAN> backup incremental level=1 cumulative database tag='weekly'

You can view your incremental Backup Details by using following Query

SQL Code: 
select  incremental_level,
        incremental_change#,
        checkpoint_change#,
        blocks
from    v$backup_datafile;

RMAN Configuration

December 16, 2009 Leave a comment

A complete high availability and disaster recovery strategy requires dependable data backup, restore, and recovery procedures. Oracle Recovery Manager (RMAN), a command-line and Enterprise Manager-based tool, is the Oracle-preferred method for efficiently backing up and recovering your Oracle database. RMAN is designed to work intimately with the server, providing block-level corruption detection during backup and restore. RMAN optimizes performance and space consumption during backup with file multiplexing and backup set compression, and integrates with Oracle Secure Backup and third party media management products for tape backup.

RMAN takes care of all underlying database procedures before and after backup or restore, freeing dependency on OS and SQL*Plus scripts. It provides a common interface for backup tasks across different host operating systems, and offers features not available through user-managed methods, such as parallelization of backup/recovery data streams, backup files retention policy, and detailed history of all backups.

Step 01: Create tablepsace to hold repository

#sqlplus sys/sys_password@orcl AS SYSDBA

SQL>  CREATE TABLESPACE "RMAN"
DATAFILE 'C:\oracle\product\10.2.0\oradata\orcl\RMAN.DBF' SIZE 6208K REUSE
AUTOEXTEND ON NEXT 64K MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Step 02: Create user RMAN Schema owner for the backup and recovery using RMAN.

SQL>  CREATE USER rman IDENTIFIED BY rman
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE rman
QUOTA UNLIMITED ON rman;

Step 03: Grant recovery_catalog_owner to RMAN user

SQL>  GRANT connect, resource, recovery_catalog_owner TO rman;

Step 04: Now connect to the RMAN and Create Repository catalog

$rman catalog=rman/rman@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Dec 16 19:55:32 2009

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

connected to recovery catalog database

RMAN> create catalog tablespace "RMAN";

recovery catalog created

Step 05: Register Database Each database to be backed up by RMAN must be registered

$rman catalog=rman/rman@orcl target=sys/oracle@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Dec 16 20:01:00 2009

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

connected to target database: ORCL (DBID=1223903242)
connected to recovery catalog database

RMAN> register database;

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

RMAN> exit

Recovery Manager complete.
We have done!!!! The configuration of RMAN in our Database

Some Basic Parameter of RMAN

Retention Policy:

This instructs RMAN on the backups that are eligible for deletion. For example: A retention policy with redundancy 2 would mean that two backups – the latest and the one prior to that – should be retained. All other backups are candidates for deletion.

Default Device Type:

This can be “disk” or “sbt” (system backup to tape). We will backup to disk and then have our OS backup utility copy the completed backup, and other supporting files, to tape.

Control files Auto backup:

This can be set to “on” or “off”. When set to “on”, RMAN takes a backup of the control file AND server parameter file each time a backup is performed. Note that “off” is the default

Parallelism:

This tells RMAN how many server processes you want dedicated to performing the backups.

Device Type Format:

This specifies the location and name of the backup files. We need to specify the format for each channel. The “%U” ensures that Oracle appends a unique identifier to the backup file name. The MAXPIECESIZE attribute sets a maximum file size for each file in the backup set.

Control files Auto backup Format:

This tells RMAN where the controlfile backup is to be stored. The “%F” in the file name instructs RMAN to append the database identifier and backup timestamp to the backup filename. The database identifier, or DBID, is a unique integer identifier for the database.

For example, one can turn off control file auto backups by issuing:
RMAN> configure controlfile autobackup off;

Show All Command

Any of the above parameters can be changed using the commands displayed by the “show all” command.

RMAN> show all;

Mechanism of Restore and Recovery operation

RMAN> RESTORE DATABASE;

RMAN> RECOVER DATABASE;

Resynchronized Catalog

The recovery catalog should be resynchronized on a regular basis so that changes to the database structure and presence of new archive logs is recorded. Some commands perform partial and full resyncs implicitly, but if you are in doubt you can perform a full resync using the following command.

RMAN> resync catalog;

starting full resync of recovery catalog
full resync complete

RECYCLE BIN

December 16, 2009 Leave a comment

Oracle has introduced the RECYCLE BIN which is a logical entity to hold all the deleted objects and works exactly like the recycle bin provided in Windows operating system for example. All the deleted objects are kept n the recycle bin; these objects can be retrieved from the recycle bin or deleted permanently by using the PURGE command. Either an individual object like a table or an index can be deleted from the recycle bin

Related Data Dictionary Objects

·         recyclebin$
·         dba_recyclebin   
·         recyclebin 
·         user_recyclebin

user_recyclebin and dba_recyclebin are use for recovery using flashback

 
SQL> SELECT name, value
FROM v$parameter
WHERE name LIKE 'recyc%';

NAME             VALUE       
----------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
recyclebin       on       
 

How to Use Recycle Bin in Oracle 10g

Starting and stopping the recyclebin

Syntax: 
ALTER SYSTEM SET recyclebin=<OFF | ON> SCOPE=<BOTH | MEMORY | SPFILE>;

By default recyclebin is set to on

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

SQL> select OBJECT_NAME, ORIGINAL_NAME, TYPE from  dba_recyclebin;
no rows selected
SQL> drop table scott.emp;
Table dropped.

SQL> select OBJECT_NAME, ORIGINAL_NAME, TYPE from dba_recyclebin;
OBJECT_NAME                              ORIGINAL_NAME        TYPE
-----------------------------------------------------------------
BIN$iSsOuMCxThKB65n/ep2Y5g==$0    PK_EMP        INDEX
BIN$JsuRtykaSpOd8XLplx1/vA==$0    EMP           TABLE

To recover the emp table issue the following command

Syntax:
FLASHBACK TABLE <table_name> TO BEFORE DROP {RENAME TO <new_table_name>};

SQL> flashback table scott.emp to before drop;
Flashback complete.
Now emp table is restored from recyclebin.

You can also rename the table during the time of restore

SQL> flashback table scott.emp to before drop
     RENAME TO scott.emp2; 

To clear the recycle bin issue the following command

Syntax:
PURGE TABLE <recycle_bin_name>;
 
SQL> PURGE TABLE scott.emp;
 
Table purged.

Remove Recycle Bin Objects by Tablespace and User

Syntax:
PURGE TABLESPACE <tablespace_name> 
USER <schema_name>;
 
SQL Code:
PURGE TABLESPACE users USER scott;

Clear full recycilebin

Syntax:
PURGE RECYCLEBIN

Empty Everything in All Recycle Bins

SQL Code:
PURGE dba_recyclebin;
 
 
Categories: RECYCLE BIN Tags: