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.

Categories: ORA-00439:Feature not enabled Tags:

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

Categories: Cancel-Based Recovery Tags:

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/
Categories: Backup-Restore Control File Tags:

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;
Categories: RMAN Backup Tags: ,

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
Categories: RMAN Configuration Tags: ,

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:

Flashback Recovery

December 16, 2009 Leave a comment

Flashback query is a powerful and useful feature introduced in Oracle 9i, and enhanced greatly in Oracle 10g, that can help us recover data, lost or corrupted, due to human error. One big advantages of using flashback over point-in-time recovery is that for the latter not only transactions from the time of error to the current time would be lost but also the system will be unavailable for the duration of the recovery. For flashback query, on the other hand, there will be no down time needed and repair or recovery is less labor and time intensive than what it used to be in earlier versions of Oracle. With the new features like Recycle Bin, Flashback databases and Flashback Drop in Oracle 10g, the flashback capability introduced in 9i has been improved tremendously now turning a small feature into a powerful tool in the new Oracle releases.

Type of flashback recovery:

  • Flashback Database (We can revert database at a past time)
  • Flashback Drop (Reverses the effects of a DROP TABLE statement)
  • Flashback Table (Reverses a table to its state at a previous point in time)
  • Flashback Query (We can specify a target time and then run queries, viewing results and recover from an unwanted change)
  • Flashback Transaction Query (We can view changes made by a transaction during a period of time.)

Requirement for Flashback:

  • Database must be in Archive log mode
  • Must have flash recovery area enable

According to the Oracle documentation, Flashback technologies are applicable in repairing the following user errors.

  • Erroneous or malicious DROP TABLE statements
  • Erroneous or malicious update, delete or insert transactions
  • Erroneous or malicious batch job or wide-spread application errors

Dependent Objects

  • V_$FLASHBACK_DATABASE_LOG
  • V_$FLASHBACK_DATABASE_LOGFILE
  • V_$FLASHBACK_DATABASE_STAT
  • GV_$FLASHBACK_DATABASE_LOG
  • GV_$FLASHBACK_DATABASE_LOGFILE
  • GV_$FLASHBACK_DATABASE_STAT

Syntax:

Syntax base on SCN: 
SCN FLASHBACK [STANDBY] DATABASE [<database_name>]
TO [BEFORE] SCN <system_change_number> 

Syntax base on TIMESTAMP: 
TIMESTAMP FLASHBACK [STANDBY] DATABASE [<database_name>]
TO [BEFORE] TIMESTMP <system_timestamp_value> 

Syntax base on RESTORE POINT: 
RESTORE POINT FLASHBACK [STANDBY] DATABASE [<database_name>]
TO [BEFORE] RESTORE POINT <restore_point_name>

Flashback Syntax Elements

How to OFF Flashback 

Syntax:
ALTER DATABASE FLASHBACK OFF;

How to ON Flashback 

Syntax:
ALTER DATABASE FLASHBACK ON;

Start flashback on a tablespace

ALTER TABLESPACE <tablespace_name> FLASHBACK ON;

Stop flashback on a tablespace

ALTER TABLESPACE <tablespace_name> FLASHBACK OFF;

Initialization Parameters

Setting the location of the flashback recovery area 

db_recovery_file_dest=/oracle/flash_recovery_area

Setting the size of the flashback recovery area 

db_recovery_file_dest_size=2147483648

Setting the retention time for flashback files (in minutes) — 2 days

db_flashback_retention_target=2880

Set Retention Target

Syntax:
ALTER SYSTEM SET db_flashback_retention_target = <number_of_minutes>;
SQL Code:
alter system set DB_FLASHBACK_RETENTION_TARGET = 2880;

How to Enable Flashback

Flashback query is not enabled by default and must be turned on in following sequence. We will set retention to 10 hours (600 minutes) and set recovery size up to 2 GB in file “/recovery/flashback”

Step 01: Verify the Database in flash back mode and the retention_target.

SQL> SELECT flashback_on, log_mode
FROM v$database;
FLASHBACK_ON       LOG_MODE
------------------ ------------
NO                 ARCHIVELOG

Step 02: Shutdown the database and start in exclusive mode

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250452 bytes
Variable Size             268438380 bytes
Database Buffers          339738624 bytes
Redo Buffers                2940928 bytes
Database mounted.

Step 03: Enable the Archive log and Set the DB_FLASHBACK_RETENTION_TARGET, DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST.

Flash Recovery Area created by the DBA, is the allocation of space on the disk to hold all the recovery related files in one, centralized place. Flash Recovery Area contains the Flashback Logs, Redo Archive logs, backups files by RMAN and copies of control files. The destination and the size of the recovery area are setup using the db_recovery_file_dest and b_recovery_file_dest_size initializatin parameters.

SQL> alter database archivelog;

Database altered.

SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=600;

System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=2G;

System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST=
‘C:\oracle\product\10.2.0\flash_recovery_area\ORCL\FLASHBACK’;

System altered.

N.B: For UNIX system issue the following command

alter system set DB_RECOVERY_FILE_DEST=‘/recovery/flashback’;

Step 04: On the Flash back and open the database.

SQL> alter database flashback on;

System altered.

SQL> alter database open;

Step 05: Now Verify the Database flashback mode.

SQL> SELECT flashback_on, log_mode
FROM v$database;
FLASHBACK_ON       LOG_MODE
------------------ ------------
YES                ARCHIVELOG
SQL> SELECT name, value
FROM gv$parameter
WHERE name LIKE ‘%flashback%’;
NAME                              VALUE
---------------------------------------
db_flashback_retention_target      600
SQL> SELECT estimated_flashback_size
FROM gv$flashback_database_log;
ESTIMATED_FLASHBACK_SIZE
------------------------
22835200

How to Recover Database from Flashback recovery area

Step 01: Find the Current SCN and Flashback time.

SQL> SELECT current_scn
  2  FROM v$database;

CURRENT_SCN
-----------
    1143033

SQL> SELECT oldest_flashback_scn,oldest_flashback_time
  2  FROM gv$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL
-------------------- ---------
             1141575 16-DEC-09

Step 02: Grant flashback to the user.

So that user can create a restore point and flashback

GRANT flashback any table TO <user_name>;

Step 03: Shutdown the database and start in exclusive mode.

SQL> SHUTDOWN immediate;

SQL> startup mount exclusive;

Step 04: Be sure to substitute your SCN and issue the following command

SQL> FLASHBACK DATABASE TO SCN <SCN Number>;

Flashback complete.

Or If restore point create by the user

FLASHBACK DATABASE TO RESTORE POINT <RESTORE POINT>;

Or flashback using TIMESTAMP 

FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24);
FLASHBACK DATABASE TO TIMESTAMP Timestamp ‘2009-11-05 14:00:00’;
FLASHBACK DATABASE TO TIMESTAMP
                   TO_TIMESTAMP (‘2009-11-11 16:00:00’, ‘YYYY-MM-DD HH24:MI:SS’);

Step 05: Now open database using resetlogs

alter database open will fail

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

alter database open resetlogs will be succeed

SQL> alter database open resetlogs;

Database altered.

Step 06: See the flashback status 

SELECT *
FROM gv$flashback_database_stat;

INST_ID   BEGIN_TIME  END_TIME  FLASHBACK_DATA   DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
------- ------------ --------- --------------- --------- ---------- ------------------------
1         16-DEC-09   16-DEC-09 827392          9797632    130048                         0

Step 06: Now switch the log and use RMAN to clear the archive log following way 

alter system switch logfile;

shutdown immediate;

startup mount exclusive;

alter database flashback off;

alter database noarchivelog;

alter database open;

SQL> SELECT flashback_on, log_mode
  2  FROM v$database;

FLASHBACK_ON       LOG_MODE
------------------ ------------
NO                 NOARCHIVELOG

Run rman to delete the archive log

$rman target sys/oracle@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Dec 16 15:06:09 2009

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

connected to target database: ORCL (DBID=1223903242)

RMAN> crosscheck archivelog all;

RMAN> delete archivelog all;

RMAN> list archivelog all;

Note:

In RAC Database, flashback recovery area must be store is clustered file system or in ASM

Default retation target is 1440 (One Days).

If we want to retain flashback logs to perform a 48 hour flashback, set the retention target to 2880 minutes (2 days x 24 hours/day x 60 minutes/hour)

By default, flashback logs are generated for all permanent tablespaces

Important:

  • If we disable Flashback Database for a tablespace, then we must take its datafiles offline before running FLASHBACK DATABASE.
  • We can enable Flashback Database not only on a primary database, but also on a standby database.
Categories: Backup & Recovery, Flashback Recovery Tags: , , ,

Export (exp) and Import (imp)

December 3, 2009 1 comment

Oracle’s export (exp) and import (imp) utilities are used to perform logical database backup and recovery. When exporting, database objects are dumped to a binary file which can then be imported into another Oracle database.
In order to use exp and imp in Oracle Database you have to run catexp.sql script. catexp.sql basically creates the exp_full_database and imp_full_database roles to the oracle user. We have to run catexp.sql for the time first time if database not create using DBCA. Because it is already executed if you create your database by DBCA.

Look for the “imp” and “exp” executables in your $ORACLE_HOME/bin directory. These parameters can be listed by executing the following commands: “exp help=yes” or “imp help=yes”.

Source: $ORACLE_HOME/rdbms/admin/catexp.sql

Full database export:
The EXP_FULL_DATABASE and IMP_FULL_DATABASE respectively, are needed to perform a full export.
Use the full=yes export parameter for a full export.

exp scott/tiger file=emp.dmp full=yes statistics=none
imp system/manager file=emp.dmp fromuser=scott touser=scott

Tablespace:

Use the tablespaces export parameter for a tablespace export. It is only apply to transportable tablespaces.

exp userid=’system/manager’ tablespaces=users file=exp.dmp log=tbs.log statistics=none

Schema:
This mode can be used to export and import all objects that belong to a user. Use the owner export parameter and the fromuser import parameter for a user (owner) export-import.

Table:
Specific tables (and partitions) can be exported/imported with table export mode.
Use the tables export parameter for a table export.

Example :
exp scott/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no
exp scott/tiger file=emp.dmp tables=(emp,dept)

imp scott/tiger file=emp.dmp full=yes
imp scott/tiger file=emp.dmp fromuser=scott touser=scott tables=dept

Using a parameter file:
exp userid=scott/tiger@orcl parfile=export.txt

export.txt contains:

BUFFER=100000
FILE=scott_data.dmp
FULL=n
OWNER=scott
GRANTS=y
COMPRESS=y

Take DDL output in Dump file:
imp scott/tiger file= emp.dmp indexfile=emp.txt

or, to see ddl into screen,
imp scott/tiger file=emp.dmp show=y

How can one improve Import/ Export performance?

EXPORT (exp):

  • Set the BUFFER parameter to a high value (e.g. 2Mb — entered as an integer “2000000”)
  • Set the RECORDLENGTH parameter to a high value (e.g. 64Kb — entered as an integer “64000”)
  • Use DIRECT=yes (direct mode export)
  • Stop unnecessary applications to free-up resources for your job.
  • If you run multiple export sessions, ensure they write to different physical disks.
  • DO NOT export to an NFS mounted file system.  It will take forever.

IMPORT (imp):

  • Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.
  • Place the file to be imported on a separate physical disk from the oracle data files
  • Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file
  • Set the LOG_BUFFER to a big value and restart oracle.
  • Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG;)
  • Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)
  • Use COMMIT=N in the import parameter file if you can afford it
  • Use STATISTICS=NONE in the import parameter file to avoid time consuming to import the statistics
  • Remember to run the indexfile previously created

What are the common Import/ Export problems?

  • ORA-00001: Unique constraint (…) violated
    • You are importing duplicate rows. Use IGNORE=YES to skip tables that already exist (imp will give an error if the object is re-created).
  • ORA-01555: Snapshot too old
    • Ask your users to STOP working while you are exporting or try using parameter CONSISTENT=NO
  • ORA-01562: Failed to extend rollback segment
    • Create bigger rollback segments or set parameter COMMIT=Y while importing
  • IMP-00015: Statement failed … object already exists…
    • Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.
 Source www.orafaq.com
Categories: Export And Import Tags: , , , ,