recovery Receive Updates For This Category

List By Category: List By Month:

Per Page :

December 7, 2011 Articles 0 0

Data Guard

Oracle9i Data Guard is the new name for Oracle8i Standby Server, incorporating a large number of new features. In this article I shall only focus on those relevant to the “Oracle9i Database: New Features For Administrators” OCP exam. For more detailed information read the Oracle9i Data Guard Concepts and Administration documentation.

Architecture
Database Synchronization Options
Setup No-Data-Divergence
- Setup Primary Database
- Setup Standby Database
- Start Managed Standby Recovery
- Protect Primary Database
Cancel Managed Standby Recovery
Activating A Standby Database
Backup Standby Database
Database Switchover
Database Failover
Automatic Archive Gap Detection
Background Managed Recovery
Delayed Redo Application

Architecture

The Oracle9i Data Guard architecture incorporates the following items:

Primary Database – A production database that is used to create standby databases. The archive logs from the primary database are transfered and applied to standby databases. Each standby can only be associated with a single primary database, but a single primary database can be associated with multiple standby databases.
Standby Database – A replica of the primary database.
Log Transport Services – Control the automatic transfer of archive redo log files from the primary database to one or more standby destinations.
Network Configuration – The primary database is connected to one or more standby databases using Oracle Net.
Log Apply Services – Apply the archived redo logs to the standby database. The Managed Recovery Process (MRP) actually does the work of maintaining and applying the archived redo logs.
Role Management Services – Control the changing of database roles from primary to standby. The services include switchover, switchback and failover.
Data Guard Broker – Controls the creation and monitoring of Data Guard. It comes with a GUI and command line interface.

The services required on the primary database are:

Log Writer Process (LGWR) – Collects redo information and updates the online redo logs. It can also create local archived redo logs and transmit online redo to standby databases.
Archiver Process (ARCn) – One or more archiver processes make copies of online redo logs either locally or remotely for standby databases.
Fetch Archive Log (FAL) Server – Services requests for archive redo logs from FAL clients running on multiple standby databases. Multiple FAL servers can be run on a primary database, one for each FAL request. .

The services required on the standby database are:

Fetch Archive Log (FAL) Client – Pulls archived redo log files from the primary site. Initiates transfer of archived redo logs when it detects a gap sequence.
Remote File Server (RFS) – Receives archived and/or standby redo logs from the primary database.
Archiver (ARCn) Processes – Archives the standby redo logs applied by the managed recovery process (MRP).
Managed Recovery Process (MRP) – Applies archive redo log information to the standby database.

Database Synchronization Options

Data Guard can be configured to run with varying synchronization modes indicating the potential for data loss.

No-Data-Loss mode : This simply means that the log transport services will not acknowledge modifications to the primary database until they are available to the standby database. This doesn’t mean that the modifications have been applied to the standby database, merely that the log information is available to the log apply services should failover occur. This mode is implemented using standby redo logs on the standby server.
No-Data-Divergence mode : This is an extension of the no-data-loss mode whereby modifications to the primary database are prevented if conectivity between the primary and at least one standby database is unavailable.
Minimal-Data-Loss mode : When the performance requirements of the primary database are the top priority this mode provides the optimum balance of data protection and performance.

Setup No-Data-Divergence

To setup no-data-divergence, the most extreme level of data protection, then do the following.

Setup Primary Database

Shutdown the database using: SHUTDOWN IMMEDIATE
Backup all database files.
Add an entry for the standby server into the “tnsnames.ora” file.

stby1=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myServerName)(PORT = 1512))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = stby1.world)
    )
  )

Assuming your database in already in ARCHIVELOG mode one of the archive destinations will be set. Add the other entries.

CONTROL_FILES=primary.ctl
COMPATIBLE=9.0.1.0.0
LOG_ARCHIVE_START=true
LOG_ARCHIVE_DEST_1='LOCATION=C:\Oracle\Oradata\TSH1\Archive MANDATORY REOPEN=30'
LOG_ARCHIVE_DEST_2='SERVICE=stby1 LGWR SYNC AFFIRM'
LOG_ARCHIVE_DEST_STATE_1=enable
LOG_ARCHIVE_DEST_STATE_2=enable
LOG_ARCHIVE_FORMAT=arc%t_%s.arc
REMOTE_ARCHIVE_ENABLE=true

The LGWR SYNC AFFIRM keywords indicate that the Logwriter should synchronously write updates to the online redo logs to this location and wait for confirmation of the write before proceeding. The remote site will process and archive these standby redo logs to keep the databases synchronized. This whole process can impact performance greatly but provides maximum data security.

Startup the database using: STARTUP PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora
Create standby database controlfile using: ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'c:\stbycf.f';

Setup Standby Database

Copy the production backup files to the standby server.
Copy the standby controlfile to the standby server.
Alter the control_files and archive parameters of the init.ora as follows.

SERVICE_NAMES = stby1
CONTROL_FILES=standby.ctl
COMPATIBLE=9.0.1.0.0
LOG_ARCHIVE_START=true
LOCK_NAME_SPACE=stby1
FAL_SERVER=prim1
FAL_CLIENT=stby1

# Uncomment if filename conversion is needed
#DB_FILE_NAME_CONVERT=("/primary","/standby")
#LOG_FILE_NAME_CONVERT=("/primary","/standby")

STANDBY_ARCHIVE_DEST=C:\Oracle\Oradata\TSH1\Archive
LOG_ARCHIVE_DEST_1='LOCATION=C:\Oracle\Oradata\TSH1\Archive'
LOG_ARCHIVE_TRACE=127
LOG_ARCHIVE_FORMAT=arc%t_%s.arc
STANDBY_FILE_MANAGEMENT=auto
REMOTE_ARCHIVE_ENABLE=true

Add the following entries into the “listener.ora” file.

(ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = myHost)(PORT = 1512))
)

STANDBY_LISTENER = (ADDRESS_LIST=
 (ADDRESS=(PROTOCOL=tcp)(PORT=1512)(HOST=myHost))
)

The file should resemble the following.

# LISTENER.ORA Network Configuration File: C:\Oracle\Ora901\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = myHost)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = myHost)(PORT = 1512))
      )
    )
    (DESCRIPTION =
      (PROTOCOL_STACK =
        (PRESENTATION = GIOP)
        (SESSION = RAW)
      )
      (ADDRESS = (PROTOCOL = TCP)(HOST = myHost)(PORT = 2481))
    )
  )

STANDBY_LISTENER = (ADDRESS_LIST=
 (ADDRESS=(PROTOCOL=tcp)(PORT=1512)(HOST=myHost))
)

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC = (SID_NAME = PLSExtProc)(ORACLE_HOME = C:\Oracle\Ora901)(PROGRAM = extproc))
    (SID_DESC = (ORACLE_HOME = C:\Oracle\Ora901) (SID_NAME = TSH1)
    )
  )

Reload the listener file using lsnrctl reload from the command prompt.
Add the following entry into the tnsnames.ora file:

stby1=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myServerName)(PORT = 1512))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = stby1.world)
    )
  )

Create standby redo logs on the standby database to receive online redo information from the Logwriter on the primary database. The minimum number of groups required is an exact match, number and size, of the primary database, but performance may be increased by adding more.

ALTER DATABASE ADD STANDBY LOGFILE GROUP 10
  ('C:\Oracle\Oradata\TSH1\redo1a.log','C:\Oracle\Oradata\TSH1\redo1b.log') SIZE 500K;

Start Managed Standby Recovery

During managed recovery the transfer of archivelogs is controlled by the servers without user intervention.

Copy all archive logs from the primary to the standby server. This is the only time you should need to do this.
From sqlplus do the following.

SQL&gt; CONNECT sys/password AS SYSDBA
SQL&gt; STARTUP NOMOUNT PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora
SQL&gt; ALTER DATABASE MOUNT STANDBY DATABASE;
SQL&gt; RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Protect Primary Database

Now that Data Guard is configured and running the primary database can be prevented from applying updates unless the update has been sent to at least one standby location. Connect to the primary database and execute the following.

ALTER DATABASE SET STANDBY DATABASE PROTECTED;

Cancel Managed Standby Recovery

To stop managed standby recovery do the following.

SQL&gt; -- Cancel protected mode on primary
SQL&gt; CONNECT sys/password@primary1 AS SYSDBA
SQL&gt; ALTER DATABASE SET STANDBY DATABASE UNPROTECTED;
SQL&gt;
SQL&gt; -- Cancel recovery if necessary
SQL&gt; CONNECT sys/password@standby1 AS SYSDBA
SQL&gt; RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL&gt; ALTER DATABASE OPEN READ ONLY;

The database can subsequently be switched back to recovery mode as follows.

SQL&gt; -- Startup managed recovery
SQL&gt; CONNECT sys/password@standby1 AS SYSDBA
SQL&gt; SHUTDOWN IMMEDIATE
SQL&gt; STARTUP NOMOUNT PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora
SQL&gt; ALTER DATABASE MOUNT STANDBY DATABASE;
SQL&gt; RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

SQL&gt; -- Protect primary database
SQL&gt; CONNECT sys/password@primary1 AS SYSDBA
SQL&gt; ALTER DATABASE SET STANDBY DATABASE PROTECTED;

Activating A Standby Database

If the primary database is not available the standby database can be activated as a primary database using the following statements.

SQL&gt; -- Cancel recovery if necessary
SQL&gt; RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL&gt; ALTER DATABASE ACTIVATE STANDBY DATABASE;

Since the standby database is now the primary database it should be backed up immediately. The previous primary database can then be configured as a standby.

Backup Standby Database

Backups of the standby database can only be performed if the database is shut down or in read only mode. Read only mode is best for managed recovery systems as archive logs will still be transfered during the backup process, thus preventing gap sequences. Once the server is in the desired mode simply copy the appropriate database files.

Database Switchover

A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements.

-- Convert primary database to standby
CONNECT sys/change_on_install@prim1 AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

-- Shutdown primary database
SHUTDOWN IMMEDIATE;

-- Mount old primary database as standby database
STARTUP NOMOUNT PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

-- Convert standby database to primary
CONNECT sys/change_on_install@stby1 AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

-- Shutdown standby database
SHUTDOWN IMMEDIATE;

-- Open old standby database as primary
STARTUP PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora

This process has no affect on alternative standby locations. The process of converting the instances back to their original roles is known as a Switchback. The switchback is accomplished by performing another switchover.

Database Failover

Graceful Database Failover occurs when database failover causes a standby database to be converted to a primary database.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;

This process will recovery all or some of the application data using the standby redo logs, therefore avoiding reinstantiation of other standby databases. If completed successfully, only the primary database will need to be reinstatiated as a standby database.

Forced Database Failover changes one standby database to a primary database. Application data may be lost neccessitating the reinstantiation of the primary and all standby databases.

Automatic Archive Gap Detection

Gaps in the sequence of archive logs can be created when changes are applied to the primary database while the standby database is unavailable. In Oracle8i the archive redo logs associated with these gaps had to be identified using the V$ARCHIVE_GAP view and copied manually to the standby server before managed recovery could be initiated again. In Oracle9i most of these gap sequences can be resolved automatically. The following parameters must be added to the standby init.ora file where the values indicate net services names.

FAL_SERVER = 'primary_db1'
FAL_CLIENT = 'standby_db1'

The FAL server is normally the primary database, but can be another standby database. Once the standby database is placed in managed recovery mode it will automatically check for gap sequences. If it finds any it will request the appropriate files from the primary database via the FAL server. If the gap sequences cannot be resolved the files have to be recovered manually.

Background Managed Recovery

In Oracle8i managed recovery caused the user session to hang until the process was stopped by the user. This type of recovery is still available along with a background recovery that spawns a new background process and frees the user session

-- User session hangs
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

-- User session released
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Delayed Redo Application

Application of the archived redo logs to the standby database can be delayed using the DELAY keyword. If a rogue statement significantly damages the primary database the DBA can choose to switch to the standby database, which will be in a state prior to this action

-- Delay application of archived redo logs by 30 minutes.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30;

-- Return to no delay (Default).
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;

Go to Detail Page

Data Guard Physical Standby Setup in Oracle Database 11g Release 2

December 7, 2011 Articles 0 0

Data Guard Physical Standby Setup in Oracle Database 11g Release 2

Data Guard is the name for Oracle’s standby database solution, used for disaster recovery and high availability. This article contains an updated version of the 9i physical standby setup method posted here.

Assumptions
Primary Server Setup
- Logging
- Initialization Parameters
- Service Setup
- Backup Primary Database
- Create Standby Controlfile and PFILE
Standby Server Setup (Manual)
- Copy Files
- Start Listener
- Restore Backup
- Create Redo Logs
Standby Server Setup (DUPLICATE)
- Copy Files
- Start Listener
- Create Standby Redo Logs on Primary Server
- Create Standby using DUPLICATE
Start Apply Process
Test Log Transport
Protection Mode
Database Switchover
Failover
Flashback Database
Read-Only Standby and Active Data Guard
Snapshot Standby

Assumptions

You have two servers (physical or VMs) with an operating system and Oracle installed on them. In this case I’ve used Oracle Linux 5.6 and Oracle Database 11.2.0.2.
The primary server has a running instance.
The standby server has a software only installation.

Primary Server Setup

Logging

Check that the primary database is in archivelog mode.

SELECT log_mode FROM v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL&gt;

If it is noarchivelog mode, switch is to archivelog mode.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Enabled forced logging by issuing the following command.

ALTER DATABASE FORCE LOGGING;

Initialization Parameters

Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to “DB11G” on the primary database.

SQL&gt; show parameter db_name

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_name 			     string	 DB11G

SQL&gt; show parameter db_unique_name

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_unique_name			     string	 DB11G

SQL&gt;

The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter. For this example, the standby database will have the value “DB11G_STBY”.

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)';

Set suitable remote archive log destinations. In this case I’m using the fast recovery area for the local location, but you could specify an location explicitly if you prefer. Notice the SERVICE and the DB_UNIQUE_NAME for the remote location reference the standby location.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters must be set to appropriate values and the REMOTE_LOGIN_PASSWORDFILE must be set to exclusive.

ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

In addition to the previous setting, it is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters. Adjust the *_CONVERT parameters to account for your filename and patch differences between the servers.

ALTER SYSTEM SET FAL_SERVER=DB11G_STBY;
--ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DB11G_STBY','DB11G' SCOPE=SPFILE;
--ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DB11G_STBY','DB11G'  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Remember, some of the parameters are not modifiable, so the database will need to be restarted before they take effect.

Service Setup

Entries for the primary and standby databases are needed in the “$ORACLE_HOME/network/admin/tnsnames.ora” files on both servers. You can create these using the Network Configuration Utility (netca) or manually. The following entries were used during this setup.

DB11G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G.WORLD)
    )
  )

DB11G_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G.WORLD)
    )
  )

Backup Primary Database

If you are planning to use an active duplicate to create the standby database, then this step is unnecessary. For a backup-based duplicate, or a manual restore, take a backup of the primary database.

$ rman target=/

RMAN&gt; BACKUP DATABASE PLUS ARCHIVELOG;

Create Standby Controlfile and PFILE

Create a controlfile for the standby database by issuing the following command on the primary database.

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/db11g_stby.ctl';

Create a parameter file for the standby database.

CREATE PFILE='/tmp/initDB11G_stby.ora' FROM SPFILE;

Amend the PFILE making the entries relevant for the standby database. I’m making a replica of the original server, so in my case I only had to amend the following parameters.

*.db_unique_name='DB11G_STBY'
*.fal_server='DB11G'
*.log_archive_dest_2='SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'

Standby Server Setup (Manual)

Copy Files

Create the necessary directories on the standby server.

$ mkdir -p /u01/app/oracle/oradata/DB11G
$ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p /u01/app/oracle/admin/DB11G/adump

Copy the files from the primary to the standby server.

$ # Standby controlfile to all locations.
$ scp oracle@ol5-112-dga1:/tmp/db11g_stby.ctl /u01/app/oracle/oradata/DB11G/control01.ctl
$ cp /u01/app/oracle/oradata/DB11G/control01.ctl /u01/app/oracle/fast_recovery_area/DB11G/control02.ctl

$ # Archivelogs and backups
$ scp -r oracle@ol5-112-dga1:/u01/app/oracle/fast_recovery_area/DB11G/archivelog /u01/app/oracle/fast_recovery_area/DB11G
$ scp -r oracle@ol5-112-dga1:/u01/app/oracle/fast_recovery_area/DB11G/backupset /u01/app/oracle/fast_recovery_area/DB11G

$ # Parameter file.
$ scp oracle@ol5-112-dga1:/tmp/initDB11G_stby.ora /tmp/initDB11G_stby.ora

$ # Remote login password file.
$ scp oracle@ol5-112-dga1:$ORACLE_HOME/dbs/orapwDB11G $ORACLE_HOME/dbs

Start Listener

Make sure the listener is started on the standby server.

$ lsnrctl start

Restore Backup

Create the SPFILE form the amended PFILE.

$ export ORACLE_SID=DB11G
$ sqlplus / as sysdba

SQL&gt; CREATE SPFILE FROM PFILE='/tmp/initDB11G_stby.ora';

Restore the backup files.

$ export ORACLE_SID=DB11G
$ rman target=/

RMAN&gt; STARTUP MOUNT;
RMAN&gt; RESTORE DATABASE;

Create Redo Logs

Create online redo logs for the standby. It’s a good idea to match the configuration of the primary server.

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo01.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo02.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo03.log') SIZE 50M;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

In addition to the online redo logs, you should create standby redo logs on both the standby and the primary database (in case of switchovers). The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs. In my case, the following is standby redo logs must be created on both servers.

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo04.log') SIZE 50M;

Once this is complete, we can start the apply process.

Standby Server Setup (DUPLICATE)

Copy Files

Create the necessary directories on the standby server.

$ mkdir -p /u01/app/oracle/oradata/DB11G
$ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p /u01/app/oracle/admin/DB11G/adump

Copy the files from the primary to the standby server.

$ # Standby controlfile to all locations.
$ scp oracle@ol5-112-dga1:/tmp/db11g_stby.ctl /u01/app/oracle/oradata/DB11G/control01.ctl
$ cp /u01/app/oracle/oradata/DB11G/control01.ctl /u01/app/oracle/fast_recovery_area/DB11G/control02.ctl

$ # Parameter file.
$ scp oracle@ol5-112-dga1:/tmp/initDB11G_stby.ora /tmp/initDB11G_stby.ora

$ # Remote login password file.
$ scp oracle@ol5-112-dga1:$ORACLE_HOME/dbs/orapwDB11G $ORACLE_HOME/dbs

Start Listener

When using active duplicate, the standby server requires static listener configuration in a “listener.ora” file. In this case I used the following configuration.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DB11G.WORLD)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = DB11G)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga2.localdomain)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

Make sure the listener is started on the standby server.

$ lsnrctl start

Create Standby Redo Logs on Primary Server

The DUPLICATE command automatically creates the standby redo logs on the standby. To make sure the primary database is configured for switchover, we must create the standby redo logs on the primary server.

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo04.log') SIZE 50M;

Create Standby Using DUPLICATE

Start the auxillary instance on the standby server by starting it using the temporary “init.ora” file.

$ export ORACLE_SID=DB11G
$ sqlplus / as sysdba

SQL&gt; STARTUP NOMOUNT PFILE='/tmp/initDB11G_stby.ora';

Connect to RMAN, specifying a full connect string for both the TARGET and AUXILLARY instances. DO not attempt to use OS authentication.

$ rman TARGET sys/password@DB11G AUXILIARY sys/password@DB11G_STBY

Now issue the following DUPLICATE command.

DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    SET db_unique_name='DB11G_STBY' COMMENT 'Is standby'
    SET LOG_ARCHIVE_DEST_2='SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'
    SET FAL_SERVER='DB11G' COMMENT 'Is primary'
  NOFILENAMECHECK;

A brief explanation of the individual clauses is shown below.

FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.
FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafile, without an additional backup step.
DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.
SPFILE: Allows us to reset values in the spfile when it is copied from the source server.
NOFILENAMECHECK: Destination file locations are not checked.

Once the command is complete, we can start the apply process.

Start Apply Process

Start the apply process on standby server.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

If you need to cancel the apply process, issue the following command.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

If you prefer, you can set a delay between the arrival of the archived redo log and it being applied on the standby server using the following commands.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;

Test Log Transport

On the primary server, check the latest archived redo log and force a log switch.

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time
FROM   v$archived_log
ORDER BY sequence#;

ALTER SYSTEM SWITCH LOGFILE;

Check the new archived redo log has arrived at the standby server and been applied.

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied
FROM   v$archived_log
ORDER BY sequence#;

Protection Mode

There are three protection modes for the primary database:

Maximum Availability: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If no standby location is available, it acts in the same manner as maximum performance mode until a standby becomes available again.
Maximum Performance: Transactions on the primary commit as soon as redo information has been written to the online redo log. Transfer of redo information to the standby server is asynchronous, so it does not impact on performance of the primary.
Maximum Protection: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If not suitable standby location is available, the primary database shuts down.

By default a newly created standby database is in maximum performance mode.

SELECT protection_mode FROM v$database;

PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

SQL&gt;

The mode can be switched using the following commands. Note the alterations in the redo transport attributes.

-- Maximum Availability.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

-- Maximum Performance.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

-- Maximum Protection.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE OPEN;

Database Switchover

-- Convert primary database to standby
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

-- Shutdown primary database
SHUTDOWN IMMEDIATE;

-- Mount old primary database as standby database
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On the original standby database issue the following commands.

-- Convert standby database to primary
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

-- Shutdown standby database
SHUTDOWN IMMEDIATE;

-- Open old standby database as primary
STARTUP;

Once this is complete, test the log transport as before. If everything is working fine, switch the primary database back to the original server by doing another switchover. This is known as a switchback.

Failover

If the primary database is not available the standby database can be activated as a primary database using the following statements.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;

Since the standby database is now the primary database it should be backed up immediately.

The original primary database can now be configured as a standby. If Flashback Database was enabled on the primary database, then this can be done relatively easily (shown here). If not, the whole setup process must be followed, but this time using the original primary server as the standby.

Flashback Database

It was already mentioned in the previous section, but it is worth drawing your attention to Flashback Database once more. Although a switchover/switchback is safe for both the primary and standby database, a failover renders the original primary database useless for converting to a standby database. If flashback database is not enabled, the original primary must be scrapped and recreated as a standby database.

An alternative is to enable flashback database on the primary (and the standby if desired) so in the event of a failover, the primary can be flashed back to the time before the failover and quickly converted to a standby database. That process is shown here.

Read-Only Standby and Active Data Guard

Once a standby database is configured, it can be opened in read-only mode to allow query access. This is often used to offload reporting to the standby server, thereby freeing up resources on the primary server. When open in read-only mode, archive log shipping continues, but managed recovery is stopped, so the standby database becomes increasingly out of date until managed recovery is resumed.

To switch the standby database into read-only mode, do the following.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;

To resume managed recovery, do the following.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

In 11g, Oracle introduced the Active Data Guard feature. This allows the standby database to be open in read-only mode, but still apply redo information. This means a standby can be available for querying, yet still be up to date. There are licensing implications for this feature, but the following commands show how active data guard can be enabled.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Since managed recovery continues with active data guard, there is no need to switch back to managed recovery from read-only mode in this case.

Snapshot Standby

Introduced in 11g, snapshot standby allows the standby database to be opened in read-write mode. When switched back into standby mode, all changes made whilst in read-write mode are lost. This is achieved using flashback database, but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature, thought it works just the same if it is.

If you are using RAC, turn off all but one of the RAC instances. Make sure the instance is in MOUNT mode.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

Make sure managed recovery is disabled.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Convert the standby to a snapshot standby. The following example queries the V$DATABASE view to show that flashback database is not enabled prior to the conversion operation.

SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
NO

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE OPEN;
SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

SQL&gt;

You can now do treat the standby like any read-write database.

To convert it back to the physical standby, losing all the changes made since the conversion to snapshot standby, issue the following commands.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
NO

SQL&gt;

The standby is once again in managed recovery and archivelog shipping is resumed. Notice that flashback database is still not enabled.

Go to Detail Page

Database Backup and Recovery in 10G Release 2

October 25, 2011 Articles 0 0

Overview of Database Backup and Recovery

The focus in Oracle backup and recovery is generally on the physical backup of database files, which permit the full reconstruction of your database. The files protected by the backup and recovery facilities built into Enterprise Manager include datafiles, control files, server parameter files (SPFILEs), and archived redo log files. With these files your database can be reconstructed. The backup mechanisms that work at the physical level protect against damage at the file level, such as the accidental deletion of a datafile or the failure of a disk drive.

Logical-level backups, such as exporting database objects like tables or tablespaces, are a useful supplement to physical backups for some purposes. Nevertheless, logical backups cannot protect your entire database. An effective backup strategy must be based on physical-level backups.

Oracle Database’s flashback features provide a range of physical and logical data recovery tools as efficient, easy-to-use alternatives to physical and logical backups. The flashback features enable you to reverse the effects of unwanted database changes without restoring datafiles from backup or performing media recovery.

This chapter introduces the following logical-level flashback features:

Oracle Flashback Table, which lets you revert a table to its contents at a time in the recent past
Oracle Flashback Drop, which lets you rescue dropped database tables

Neither of the preceding features requires advance preparation such as creating logical-level exports to allow for retrieval of your lost data. Both can be used while your database is available. Oracle Database Backup and Recovery Basics discusses the flashback features of the Oracle database at greater length.

The Oracle Enterprise Manager physical backup and recovery features are built on the Recovery Manager (RMAN) command-line client. Enterprise Manager makes available much of the functionality of RMAN, and provides wizards and automatic strategies to simplify and further automate RMAN-based backup and recovery.

Oracle Backup, Restore, and Recovery Concepts

To back up your database is to make copies of your datafiles, control file, and archived redo logs (if your database runs in ARCHIVELOG mode). Restoring a database from a backup means copying the physical files that make up the database from a backup medium (disk or tape) to their locations during normal database operation. Recovery of your database is the process of updating database files restored from a backup with the changes made to the database since the backup, typically using redo log files.

Consistent and Inconsistent Backups

A backup can be consistent or inconsistent. A backup is consistent when there are no changes in the redo log that have not already been applied to the datafiles at the time of the backup.

To make a consistent backup, your database must have been shut down normally. It cannot be reopened for the duration of the backup. In shutting down the database, all committed changes in the redo log are written to the datafiles, so the datafiles are in a transaction-consistent state. This process is known as an offline backup because the entire database is offline for the duration of the backup.

In contrast to a consistent backup, an inconsistent backup is made while the database is open. In an inconsistent backup, the online redo logs contain changes that have not yet been applied to the datafiles. The datafiles are not in a transaction-consistent state. The database must be run in ARCHIVELOG mode to preserve the redo log. The online redo log at the moment of the backup must be archived and backed up along with the datafiles to preserve these changes.

In spite of the name, an inconsistent backup is as robust a form of backup as a consistent backup. The great advantage to making inconsistent backups is that you can back up your database while the database is open for updates.

Restoring from Consistent and Inconsistent Backups

When you restore your datafiles from a consistent backup, you can open the database immediately. When datafiles are restored from an inconsistent backup, you cannot open the database until committed changes recorded in the redo logs are applied to the datafiles, bringing them to a transaction-consistent state. The process of applying changes from the redo log to the datafiles restored from an inconsistent backup is called media recovery.

Media Recovery

If you restore the archived redo logs and the datafiles from backups, then you must perform media recovery before you can open the database. Any database transactions in the archived redo logs not already reflected in the datafiles are applied to the datafiles, bringing them to a transaction-consistent state before the database is opened.

Media recovery requires a control file, datafiles (typically restored from backup), and online and archived redo logs containing changes since the time the datafiles were backed up. Media recovery is most often used to recover from a media failure, such as the loss of a file or disk, or a user error, such as the deletion of the contents of a table.

There are two forms of media recovery: complete recovery and point-in-time recovery. In complete recovery, datafiles are restored from backup; all changes from the archived and online redo logs are applied to the datafiles. The database is returned to its state at the time of failure and can be opened with no loss of committed changes.

In point-in-time recovery, you return your database to its contents at a target time of your choosing in the past. You start with a backup of datafiles created prior to the target time and a complete set of archived redo log files from the time of that backup through the target time. During recovery, each change between the backup time and the target time is applied to the datafiles.

Point-in-time recovery can return your whole database to its state at any time between the time of your backup and the most recent change in the archived redo logs. All changes after the target time are discarded. Point-in-time recovery is also sometimes called incomplete recovery because you do not recover the complete set of changes to your database.

Enterprise Manager provides a convenient interface to both complete and point-in-time recovery in the form of a Recovery Wizard. However, this book focuses on complete recovery. Point-in-time recovery is discussed at more length in Oracle Database Backup and Recovery Basics.

The Flash Recovery Area

To simplify the management of backup and recovery related files, Oracle enables you to create a flash recovery area for your database. You must designate the following:

A location, which is typically a directory on disk
A maximum disk quota for the flash recovery area
A retention policy to specify your database recoverability goals

You can then direct backup-related activities, including archiving of redo logs, to store generated files in the flash recovery area. Oracle Database automatically manages this storage, deleting files that are no longer required on disk to meet your recoverability objectives if space is required for other files. A backup moved to tape is eligible for automatic deletion from the flash recovery area. Periodically copying backups to tape frees space in the flash recovery area for other files.

A flash recovery area simplifies backup storage management tasks. Therefore, it is strongly recommended. Except as noted, examples in this chapter assume the use of a flash recovery area.

The RMAN Repository

RMAN maintains a record of all database files, backup and recovery files, and backup history for your database. This record is referred to as the RMAN repository.

Every backup action that you perform through RMAN or Enterprise Manager is recorded in the repository, along with the location of and other metadata about every backup created by RMAN on disk or tape. If you back up a file without using RMAN, such as by copying the file on disk at the host operating system level, then you can add information about that copy to the RMAN repository as well. At recovery time, you can issue a command such as RESTORE DATABASE. Oracle uses the records in the repository to select backups on disk and tape needed to complete the recovery.

The primary location for the RMAN repository is in the database control file. This is one more reason why protecting your control file is a vital part of your backup strategy. In some installations, a second copy of the RMAN repository for one or more Oracle databases is stored in a set of tables called the recovery catalog. The recovery catalog is located in a separate Oracle database. Use of a recovery catalog is optional and is beyond the scope of this book.

Configuring Your Database for Basic Backup and Recovery

To take maximum advantage of Oracle Database features that automatically manage backup and recovery files and processes, configure your database as follows:

Use a flash recovery area, which automates storage management for most backup-related files.
Run your database in ARCHIVELOG mode, so that you can perform online backups and have data recovery options such as complete and point-in-time media recovery.
Use the flash recovery area as an archived log destination for your database.

You must also set a number of policies governing which files are backed up, what format is used to store backups on disk, and when files become eligible for deletion from the flash recovery area.

Planning Space Usage and Location for the Flash Recovery Area

You should place the flash recovery area on a separate disk from the working set of database files. Otherwise, the disk becomes a single point of failure for your database.

The amount of disk space to allocate for the flash recovery area depends upon the size and activity levels of your database, which determine the size of your datafiles and redo logs files as well as your recovery objectives. Your objectives dictate what kinds of backups you use, when you make them, and how long you must keep them.

Retention Policy and the Flash Recovery Area

Space management in the flash recovery area is governed by a backup retention policy. A retention policy determines when files are obsolete, meaning that they are no longer needed to meet your data recovery objectives. Retention policies can be based on redundancy of backups or on a recovery window.

Under a redundancy-based policy, the flash recovery area considers a backup of a file obsolete only when the RMAN repository has records of a specified number of more recent backups of that file. For example, assume your policy requires that two backups of each file be kept. You make backups nightly starting on a Monday night. After the Wednesday night backup succeeds, the Monday night backup becomes redundant because the Tuesday and Wednesday backups are available.

Under a recovery window-based policy, you specify a time interval measured in days. Files become obsolete only when they are no longer needed for successful complete recovery or point-in-time recovery to any point within that number of days into the past. For example, assume that you specify a recovery window of three days. A backup of all datafiles from at least three days ago must be retained, along with a full set of archived redo logs generated since that backup.

Note:

With a badly designed backup strategy, a recovery window-based retention policy can require the retention of large quantities of data. For example, assume that you specify a retention policy with a three-day recovery window and make a full database backup only on the first day of each month and no other backups. By the 28th day, recovery to any point in the three day window requires the full database backup from the first day as well as 28 days of archived redo logs.Furthermore, recovery time may be quite long after you restore from this backup. The reason is that a minimum of 25 days’ worth of transactions must be applied to the restored backup to reach the beginning of the recovery window. Thus, this strategy both wastes disk space usage and results in poor recovery performance.

A redundancy-based retention policy makes it easier to predict space usage in the flash recovery area, but it does not allow you to predict how far into the past you can recover your database. A recovery window-based policy offers better protection for your data, but can make storage requirements for backups harder to predict. As already noted, a poorly designed backup strategy can cause unexpectedly high space requirements, even with a short recovery window. Oracle recommends the use of a recovery window-based retention policy as part of a well-designed backup strategy.

Even after files in the flash recovery area are obsolete, they are generally not deleted from the flash recovery area until space is needed to store new files. As long as space permits, files recently moved to tape will remain on disk as well, so that they will not have to be retrieved from tape in the event of a recovery.

The automatic deletion of obsolete files and files moved to tape from the flash recovery area makes the flash recovery area a very convenient redo log archiving destination. Other archiving destinations require manual cleanup of archived redo logs no longer needed on disk for recovery.

Sizing the Flash Recovery Area

An approach for sizing the flash recovery area is described at length in Oracle Database Backup and Recovery Basics, but as a general rule, the larger the flash recovery area, the more useful it becomes. Ideally, the flash recovery area should be large enough to hold a copy of all of your datafiles and control files, the online redo logs, and the archived redo log files needed to recover your database using the datafile backups kept under your retention policy.

If your backup strategy includes incremental backups, which are described in “Incremental Backups of Datafiles”, then add enough space to the flash recovery area to accommodate these files as well. If you can move some backups to tape, then you can reduce the size of the flash recovery area somewhat. Note that retrieving those files from tape will cause longer database restore and recovery times.

Credentials for Performing Oracle Enterprise Manager Backup and Recovery

You must have the proper credentials to perform some of the configuration tasks for backup and recovery, and to schedule backup jobs and perform recovery. The following credentials may be required:

The Oracle user you use when you log in to Enterprise Manager
The host operating system user whose credentials you provide when performing backup and recovery tasks

To perform or schedule RMAN tasks, you must either log in to Enterprise Manager as a user with SYSDBA privileges, or provide host operating system credentials for a user who is a member of the DBA group. The host operating system user must also have execute permission for the RMAN command-line client.

For tasks requiring host operating system credentials, a Host Credentials form appears at the bottom of the page used to perform the task (see Figure 9-1, “Schedule Backup Page”). Enterprise Manager uses the credentials when it invokes RMAN to perform jobs you requested or scheduled.

Preferred Credentials for Backup and Recovery

The Host Credentials form always includes a checkbox labelled Save as Preferred Credential. If you check this box before performing your action, then the provided credentials are stored persistently for the currently logged-in Oracle user. The preferred credentials are reused by default whenever you log in as that user and perform operations requiring host credentials.

Note:

In situations in which the database is shut down, as is required for some database recovery operations, you may still be prompted for host credentials even if you save preferred credentials.

Configuring the Flash Recovery Area

You can configure a flash recovery area when first creating the database. If you did not perform this task at database creation time, however, then you can create a flash recovery area for your database now.

To configure a flash recovery area:

On the host operating system, create a directory to hold the flash recovery area. Make sure that the permissions for this directory allow Oracle to create files here.
From the Database Home page, click Maintenance.
The Maintenance property page appears.
In the Backup/Recovery section, select Recovery Settings.
The Recovery Settings page appears.
In the Flash Recovery section, enter the path to the flash recovery area location (the path to the directory on disk you created in step 1), and your desired flash recovery area size. Make sure the Apply changes to SPFILE only box is not checked, then click Apply to save your settings.

It is important to monitor space usage in the flash recovery area to ensure that it is large enough to contain backups and other recovery-related files. The High Availability section of the home page lists the percentage of flash recovery area space available. Click Usable Flash Recovery Area to navigate to the Recovery Settings page, which contains a Flash Recovery Area Usage graph showing how much space is allocated to each type of file and how much space is free.

Configuring ARCHIVELOG Mode For the Database

If you did not configure ARCHIVELOG mode when you first created your database, then configure it now with the following procedure. This procedure specifies that archived logs should be stored only in the flash recovery area, which is the best practice for managing archived redo logs.

To place the database in ARCHIVELOG mode:

On the Maintenance page, click Recovery Settings.
The Recovery Settings property page appears.
In the Media Recovery section, check ARCHIVELOG Mode if it is not already checked. Under the ARCHIVELOG Mode checkbox is a list of up to ten possible log archiving locations. The last one is set to USE_DB_RECOVERY_FILE_DEST, indicating that the flash recovery area should be an archiving destination. You can specify other locations on disk as well.
For ease of database management, the best practice is to use the flash recovery area as your only redo log archiving destination. Leave the other locations blank to store your archived logs in the flash recovery area only.
Click Apply to save your changes.
If your database was not previously running in ARCHIVELOG mode, you are prompted to restart your database so that the switch to ARCHIVELOG mode can take effect.
Click Yes to indicate that you want to restart the database.
The Restart Database:Specify Host and Target Database Credentials page appears.
Enter your credentials and click OK.
The Restart Database:Confirmation page appears.
Click Yes to begin the restart process.
Make a consistent (that is, offline) backup of your whole database immediately after switching your database into ARCHIVELOG mode.

Caution:

Backups from before the switch to ARCHIVELOGmode cannot be used to restore and recover the database to a point in time after the switch. Thus, if you do not immediately take a backup after switching, you are running your database without a backup.

Configuring Backup Settings

Assuming you have a flash recovery area configured and are running in ARCHIVELOG mode, you can configure a number of settings and policies that determine how backups are stored, which data is backed up, and how long backups are retained before being purged from the flash recovery area. You can also configure settings to optimize backup performance for your environment. This section provides information on concepts underlying the available settings, and information on how to change them through Enterprise Manager.

Understanding Backup Device Settings for Disk

The settings on the Device property page of the Backup Settings page affect how backups will be written to disk and to tape. For disk-based backups, you can configure the default format for storing backups, the location on disk where backups are stored, and whether backup tasks are run in parallel for improved performance.

Backup File Types

Database backups created by RMAN can be stored in one of two forms: image copies or backup sets.

Image copies are exact byte-for-byte copies of the files they back up. You can create an image copy by copying a file at the host operating system level. Unlike copying files at the operating system level, however, creating image copy backups through RMAN or Enterprise Manager records those copies in the RMAN repository. This technique allows RMAN to use these copies during database restore and recovery. RMAN can only use files in restore operations if they are recorded in the RMAN repository. RMAN can only create image copies on disk.

Backup sets are logical entities containing backups produced by RMAN backup commands. Individual RMAN BACKUP commands can produce one or more backup sets. Each backup set consists of several physical files called backup pieces. A backup piece stores the backup of one or more database files in a compact format that can only be manipulated by RMAN. Backup pieces cannot be usefully manipulated individually; they can only be accessed as part of backup sets. Backup sets can be created on disk or media management devices such as tapes. Backup sets are the only form in which backups can be written to a media manager.

Parallelism and RMAN Backups

RMAN depends on server sessions, processes that run on the database server, to perform backup and restore tasks. Each server session in turn corresponds to an RMAN channel, representing one stream of data to or from a backup device. RMAN supports parallelism, which is the use of multiple channels and server sessions to carry out the work of one backup or recovery task.

Proper exploitation of parallelism can greatly increase performance on backup and recovery tasks.

Configuring Backup Device Settings for Disk

From the Database Home page, click Maintenance. In the Backup/Recovery section, choose Backup Settings.

The Backup Settings page contains three property pages: Device, Backup Set, and Policy. The settings you pick here are defaults which can apply to all backup jobs. When performing individual backup tasks, you can override these defaults.

The Device property page is shown first by default. Review the following fields under the Disk Settings section:

Parallelism
For now, set this value to 1. Later, when you have had time to review the information in Oracle Database Backup and Recovery Advanced User’s Guide on parallelism and performance in RMAN, you may want to change this value.
Disk Backup Location
Leave this blank to direct backups to the flash recovery area.
Disk Backup Type
Make sure that Backup Set is selected. One advantage to backing up Oracle datafiles to backup sets is that RMAN uses unused block compression to save space in backing up datafiles. Only those blocks in your datafiles that have been used to store data are included in the backup set.

You can also provide the host credentials for the backup. Enter credentials as described in “Credentials for Performing Oracle Enterprise Manager Backup and Recovery”.

After these settings are filled in, you can click Test Disk Backup to make sure the credentials and backup location are correct.

The settings on the Backup Set property page should not be altered at this time.

Configuring Backup Policy Settings

From the Backup Settings page, click Policy. On the Policy property page, you can set the backup policies governing control file and SPFILE backups, tablespaces to exclude from whole database backup, and the backup retention policy.

Configuring Backup Policies

Check the following options in the Backup Policy page:

Automatically backup the control file and server parameter file (SPFILE) with every backup and database structural change
The SPFILE and control file are critical to the operation of your database and RMAN and are also relatively small compared to typical datafiles. Backing them up frequently imposes relatively little storage overhead. Leave the Autobackup Disk Location field blank so that the autobackups are sent to the flash recovery area.
Optimize the whole database backup by skipping unchanged files such as read-only and offline datafiles that have been backed up
This option saves space in the flash recovery area.
Enable block change tracking for faster incremental backups
This option takes advantage of the block change tracking feature of Oracle, which substantially improves performance of incremental backups at a small cost of overhead during normal operations.

Configuring Exclusions from Backup

You can specify a list of names for tablespaces to exclude from a backup. For example, you do not need to include read-only tablespaces in every backup. For now, make sure the list of excluded tablespaces is empty so that all tablespaces are backed up.

Configuring Backup Retention Policy

You can choose among the following forms of retention policy:

Retain All Backups
This option keep backups in the flash recovery area until you explicitly delete them. This option amounts to no retention policy at all.
Retain backups that are necessary for a recovery to any time within the specified number of days (point-in-time recovery)
This option enables a recovery window-based retention policy.
Retain at least the specified number of full backups for each datafile
This option enables a redundancy-based retention policy.

For now, choose the recovery window-based retention policy, with a recovery window of 31 days.

Verify that the Host Credentials section at the bottom of the page contains proper credentials. Click OK to save the new settings.

Backing Up Your Database

This section describes how to back up the database with Enterprise Manager. It introduces several types of Oracle database backup, then explains how to perform the different backup types, how to take advantage of Enterprise Manager’s Oracle-suggested backup strategy to implement a useful basic backup regimen permitting fast recovery, and how to schedule your own backups.

Note:

The Oracle-suggested strategy for disk-only backups, as described in this section, provides efficient daily backup of your entire database to disk. This strategy enables you to quickly return your database to its state at any point during the preceding 24 hours. If you need more flexible backup options, then refer to Oracle Database Backup and Recovery Basics.

Database Backup Concepts

To understand the Oracle-suggested backup strategy and other backup types provided through Enterprise Manager, you need some conceptual background on database backups as supported by Oracle.

Full Backups of Datafiles

A full backup of a datafile is a backup that includes all used blocks of the datafile. This backup can be either an image copy backup, which is an exact copy of the datafile as if copied with a host operating system file copy command, or a backup set created by RMAN. Regardless of the form in which the backup is stored, the entire datafile is backed up, even if only a few blocks have changed.

Incremental Backups of Datafiles

Incremental backups capture only those blocks that change between backups in each datafile. In a typical incremental backup strategy, a level 0 incremental backup is used as a starting point. A level 0 backup captures all blocks in the datafile.

Subsequent level 1 incremental backups, typically made at regular intervals such as once each day, capture images of each block in a datafile that changed. Level 1 backups can be cumulative, in which case all blocks changed since the most recent level 0 backup are included, or differential, in which case only those blocks changed since the most recent level 0 or level 1 incremental backup are included.

Recovering changed blocks from incremental backups is used to improve media recovery performance. Because an incremental level 1 backup captures the final contents of all datafile blocks changed during the period covered by the incremental, the recovery process can skip reapplying individual updates from the redo logs of that period and simply update each block with its final contents. The redo logs are only used for changes from the period not covered by level 1 incremental backups.

Incrementally Updated Backups: Rolling Forward Image Copies of Datafiles

The incrementally updated backups feature of Oracle lets you use one or more level 1 incremental backups with an older image copy backup of your datafiles. You can roll the old copy forward to the point in time of the last level 1 incremental backup. All blocks changed since the image copy was created are overwritten with their new contents as of the time of the last level 1 incremental backup. The effect is to roll the file forward in time, so that its contents are equivalent, for the purposes of database recovery, to an image copy full datafile backup made at the time of the last incremental level 1 backup.

Incorporating incrementally updated backups into your backup strategy shortens expected recovery times. The reason is that media recovery to the present time or a point in time in the recent past can begin at the time of the last level 1 backup applied rather than the time of the last full database backup. The Oracle-Suggested Strategy for backups is based upon incrementally updated backups.

Using Tags to Identify Backups

All RMAN backups, including incremental backups, can be labelled with a tag. A tag is a text string identifying that backup, either uniquely or as part of a group of backups. For instance, if you performed a weekly full database backup on Saturday nights, you could use the tag FULL_SATURDAY to identify all such backups. You can use these tags for referring to specific backups in RMAN commands; for example, you could issue a command to move the latest FULL_SATURDAY backup to tape.

Because you can use tags to refer to different groups of backups, you can create different routines in your overall backup strategy that do not interfere with each other. When you schedule a backup job and give the job a name, the job name is used to tag the backup.

Performing and Scheduling Backups with Enterprise Manager

Enterprise Manager lets you perform all of the different backup types supported by RMAN, and schedule the different backup jobs required by your backup strategy.

Performing a Whole Database Backup with Oracle Enterprise Manager

Whole backups of a database are based on backing up the entire contents of the database at the time of backup. Full backups of all datafiles are created. The results may be stored as image copies or as backup sets, but in either case the complete contents of all datafiles of the database are represented in the backup, as well as the control file, archived redo log, and server parameter file. With this set of files, you can perform complete recovery of the database.

While whole database backups can be an important element in your overall backup strategy, they are also a required step in some situations, such as when you switch ARCHIVELOG mode on or off (see “Configuring ARCHIVELOG Mode For the Database”).

To perform a whole database backup, follow these steps:

In the Backup/Recovery section of the Maintenance page, click Schedule Backup.
The Schedule Backup page appears, as shown in .

Figure 9-1 Schedule Backup Page

Description of “Figure 9-1 Schedule Backup Page”
The following sections of this page are important:
- The Oracle-Suggested Backup section, where you can click Schedule Oracle-Suggested Backup to choose among backup strategies recommended by the database
- The Customized Backup section, where you can schedule a one-time or repeated backup of database objects you select.
In the Customized Backup section, select Whole Database to make a full backup of your database immediately or schedule one as part of a user-designed backup strategy. Make sure the Username and Password fields are correct in the Host Credentials section and then click Schedule Customized Backup.
The Schedule Customized Backup: Options page appears. In this page you specify the options for this whole database backup.
In the Backup Type section, select Full Backup. In the Backup Mode section, select either Online Backup or Offline Backup. Typically, you will want to perform online backups to maximize database availability.

Note:

As explained in “Configuring Your Database for Basic Backup and Recovery”, you can only use online backups if your database is set up to run in ARCHIVELOG mode. In NOARCHIVELOG mode you can only perform offline backups.

In the Advanced section, make the following selections:
- Check Also back up all archived logs on disk if you are performing an online backup. There is no need to back up archived logs when performing an offline backup because the database is in a consistent state at the time of backup and does not require media recovery if you restore from this backup. Nevertheless, you can include archived logs in the backup if you wish.
- Do not check Delete all archived logs from disk after they are successfully backed up if a flash recovery area is your only archiving destination. In this case, redo logs that have been backed up are deleted automatically as space is needed for storage of other files. If you are using some other destination, it may be useful to check this option as part of managing backup storage.
- Do not check Use proxy copy supported by media management software to perform a backup for now.
- Do not check Delete obsolete backups if you use a flash recovery area for backup storage. In this case, obsolete backups are deleted automatically as space is needed for storage of other files. If you are using some other destination, it may be useful to check this option as part of managing backup storage.
- Do not enter a value for Maximum Files per Backup Set.
After making your selections, click Next.

The Schedule Customized Backup: Settings page appears.
Select a backup destination. Oracle recommends backing up to disk when possible to minimize recovery time by minimizing restores from tape. Backups created on disk can be moved to tape later. Click Next.
The Schedule Customized Backup: Schedule page appears.
Specify identifying information for the backup job (including a tag and a description for your reference), and specify when the tasks for this backup job are performed:
- In the Job section of the page, you can enter values for Job Name and Job Description. Default values for the name and description are generated for you. If you want to provide a tag for this backup, however, then enter the desired tag in the Job Name field. The job name is used as a prefix for the backup tag for backups produced by this job.
  You can set Job Description to any descriptive text that is useful for your own reference.
- In the Schedule section, specify when to start the backup and how often to repeat it. Leave the default start time of Immediately selected to run a backup immediately, or set Later and enter a time in the future. For recurring backup jobs, select options in the Repeat and Repeat Until sections. For one-time backups, select One Time Only and Indefinite.
  
  Note:
  
  For recurring jobs, it is useful to set a Job Name so that the resulting backups are easy to identify as part of an ongoing series. Use a descriptive tag, such as WEEKLY_FULL_BACKUP.
  
  When finished, click Next.
  
  The Schedule Customized Backup: Review page appears. This page presents a complete description of the backup job you specified in the previous pages.
You can perform one of the following actions:
- Click the Back button to change these options.
- Click Edit RMAN Script to view and, if desired, edit the RMAN commands that will be executed to perform your specified backup jobs.
- Click Submit Job to add the specified backup job to the schedule (or to run it immediately, if you specified that the job should run immediately).
- Click Cancel to stop the scheduled backup.
In this example, click Submit Job.

The Status page appears. This page should contain a message indicating that the job was successfully submitted.
Click View Job to monitor the progress of the backup job.
The Execution page appears. This page contains a Summary section describing the job. The Logs section includes a table listing the progress of the various steps of the backup job. You can reload this page in your browser to monitor the ongoing progress of the job. In the Name column of the table in the Logs section, you can see what phase the RMAN job is in. Clicking on the name of the phase of the backup displays a page containing the RMAN output for that part of the job. From this page, click the Back button in your browser to return to the Execution page.

Performing Offline Database Backups

When performing an offline backup, the database instance shuts down, then restarts and enters a MOUNTED state for the duration of the offline backup. The offline backup runs in the background, generating no user-visible output in the browser. The fact that the database is not open affects the pages you see from Enterprise Manager while the offline backup runs.

After you submit the backup job, a status page should appear indicating that the job has been successfully submitted. The output also includes a notification that the database will be shut down and mounted as part of the offline backup, and that you must wait for the backup to complete.

When the database is shut down and restarted, the Enterprise Manager application must also shut down for a brief time. During the period when Enterprise Manager is shut down, it cannot respond to attempts to refresh the page.

After Enterprise Manager restarts but the database is not open, Enterprise Manager reports that it cannot connect to the instance. The Database Instance section of the page reports the current state of the database listener and the instance (unmounted or mounted) as the database performs the offline backup. It also offers the options of Startup or Perform Recovery.

During the offline backup, do not click Startup or Perform Recovery because these may interfere with the offline backup. Instead, continue to refresh the page until the offline backup is complete and the database is restarted. At that time, Enterprise Manager prompts for login credentials. After you log in, you can return to the database home page.

Using the Oracle-Suggested Backup Strategy

Enterprise Manager makes it easy to set up an Oracle-suggested backup strategy for backups to disk that protects your data and provides efficient recoverability to any point in the a recovery window of your choosing. (In the simplest case, examined in this section, this window is 24 hours.) The Oracle-suggested strategy leverages Oracle’s incremental backup and incrementally-updated backup features to provide faster backups than whole database backups, and faster recoverability than is possible through applying database changes from the archived log to your datafiles.

The Oracle-suggested backup strategy is based on creating an image copy of your database. This copy is rolled forward by means of incrementally updated backups. Oracle Enterprise Manager schedules RMAN backups jobs for you to run during the overnight hours.

For each datafile, the strategy calls for backups as follows:

At the beginning of day 1 of the strategy (the time the first scheduled job actually runs), an incremental level 0 datafile copy backup. It contains the datafile’s contents at the beginning of day 1.
In a restore-and-recovery scenario, the redo logs from day 1 can be used to recover to any point during day 1.
At the beginning of day 2, an incremental level 1 backup is created, containing the blocks changed during day 1.
In a restore-and-recovery scenario, this incremental level 1 can be applied to quickly roll forward the level 0 backup to the beginning of day 2, and redo logs can be used to recover to any point during day 2.
At the beginning of each day n for days 3 and onwards, the level 1 backup from the beginning of day n-1 is applied to the level 0 backup. This brings the datafile copy to its state at the beginning of day n-1. Then, a new level 1 is created, containing the blocks changed during day n-1.
In a restore-and-recovery scenario, this incremental level 1 can be applied to the datafile rolled forward on day n-1 to the beginning of day n, and redo logs can be used to recover the database to any point during day n.

The datafile copies used in the Oracle-suggested backup strategy are tagged with the tag ORA$OEM_LEVEL_0. The level 1 incremental backups for use in this strategy are created for use with datafile copies that are so labelled. You can safely implement other backup strategies without concern for interference from the backups for the Oracle suggested strategy.

There are also Oracle-suggested strategies that use tape backups along with disk backups, but those are beyond the scope of this chapter.

Backing Up Your Database With the Oracle-Suggested Disk Backup Strategy

Follow these steps to use the Oracle-suggested strategy to back up to disk:

In the Backup/Recovery section of the Maintenance page, click Schedule Backup.
The Schedule Backup page appears, as shown in .
In the Oracle-Suggested Backup section, click the Schedule Oracle-Suggested Backup button.
The Schedule Oracle-Suggested Backup:Destination page appears. On this page, you select the destination media for the backup, which can be disk, tape or both.
Select Disk as the destination and click Next.
The Schedule Oracle-Suggested Backup: Setup page. This page describes the backups that are performed each day as part of the disk-based strategy.
There are no settings to change on this page. Click Next
The Schedule Oracle-Suggested Backup: Schedule page appears.
You are prompted for Start Date, Time Zone, and Daily Backup Time for the daily backups. Based upon your expected usage patterns, choose times for the nightly backup during which database activity is low. Click Next.
The Schedule Oracle-Suggested Backup: Review page appears.
The backup script RMAN will run is displayed (although you cannot edit the script directly). You are presented with a chance to confirm or alter your settings. In the backup script, you can see the tag ORA$OEM_LEVEL_0 that the script assigns to the backup. Assuming you do not need to change the schedule, click Submit Job to add the job for the Oracle-suggested strategy to your schedule.
Your database will now be backed up once daily, using incremental backups and incrementally applied backups, allowing quick recovery to any time in the preceding 24 hours.

Scheduling Other Backup Tasks

After taking some time to understand the full range of available backup options as described in Oracle Database Backup and Recovery Basics, you may decide to schedule backup tasks beyond those used to implement the Oracle-suggested backup strategy.

While the particulars of specifying the job to perform differ for each type of backup, all backups begin from the Schedule Backup page shown in Figure 9-1. Here you can select any of several object types to back up. You can also back up existing backups in one destination to some other destination, such as moving backups from disk to tape.

Click Schedule Customized Backup to continue to the pages where you specify details such as objects to back up, required options and settings. The choices presented on these pages are determined by the type of objects being backed up. On each page, after you have made your selections, click Next to proceed to the next page.

After you have specified the options, you reach the Schedule Customized Backup: Schedule page, where you specify the times at which the job is to be performed, as well as the Job Name and Job Description.

When you are done with the job name, description and schedule, click Next to move on to the Schedule Customized Backup: Review page. After reviewing the options, either click Back to make changes, click Edit RMAN Script to make changes to the script, or click Submit Job to add the job to the schedule.

Validating Backups and Testing Your Backup Strategy

As part of your backup strategy, you should periodically check whether your backups are intact and can be used to meet your recoverability objectives.

Through Enterprise Manager, there are two different ways to validate your backups:

You can select specific backup sets or image copies in Enterprise Manager and request that they be validated. This form of validation can reveal that a particular backup on disk or tape has been lost or corrupted.
You can specify database files to restore, and let RMAN select backups to use in restoring those files, as it would for a real database restore operation. This form of backup validation ensures that your available backups are sufficient to restore your database. For example, this operation can reveal that your backup strategy does not back up all of your tablespaces, or that the loss of a particular backup prevents you from restoring a certain tablespace.

Note:

Validating backups stored on tape can be time-consuming because the entire backup is actually read from tape.

Validating that you can perform specific restore operations with your available backups is performed through the Perform Recovery pages, and is described in “Validating the Restore of Datafiles from RMAN Backup”. Validating specific backup sets and image copies on disk or tape is performed through the Manage Current Backups pages, and is described in “Validating the Contents of Backup Sets or Image Copies”. Both forms of validation can be set up as scheduled tasks in Enterprise Manager. You should incorporate both forms of validation into your backup strategy to ensure that your recoverability goals are always met by your available backups.

Performing Restore and Recovery Operations

Enterprise Manager’s Guided Recovery capability provides a Recovery wizard that encapsulates the logic required for a wide range of restore and recovery scenarios, including the following:

Complete restore and recovery of the database
Point-in-time recovery of the database or selected tablespaces
Flashback Database
Other flashback features of Oracle for logical-level repair of unwanted changes to database objects
Block media recovery of datafiles with corrupt blocks

Enterprise Manager can determine which parts of the database must be restored and recovered, including proactively detecting situations such as corrupted database files. Enterprise Managers walks you through the recovery process, prompting for any needed information and performing needed recovery actions.

The examples in this section only illustrate a few common restore and recovery tasks. Nevertheless, the same Perform Recovery page is used to access the other whole database or object-level recovery features of Enterprise Manager.

To access the restore and recovery tasks:

In the Database home page, click Maintenance.
The Maintenance property page appears.
In the Backup/Recovery section of the Maintenance page, click Perform Recovery.
The Perform Recovery page appears. A section of this page is shown in Figure 9-2.

Figure 9-2 Perform Recovery Page

Description of “Figure 9-2 Perform Recovery Page”

On the Perform Recovery page, you can recover your whole database or only selected tablespaces, datafiles, archived logs, or tables.

Note:

In some recovery scenarios, such as a complete restore and recovery of your database, the database state will be altered by steps you take during the wizard. Changes, some of them irreversible, are made to your database at certain steps. For example, the database may be shut down and brought to MOUNTEDstate, or datafiles may be overwritten with versions from backup.Oracle Enterprise Manager will display warnings each time a significant database change will result from pressing Continue during the recovery process. Pay close attention to these warnings.

Recovering a Whole Database from Backup

This example demonstrates recovery of the whole database from backup. The example assumes that you are restoring and recovering your database after the loss of one or more datafiles, but still have a usable SPFILE and control file. Enterprise Manager can also be used to restore a lost SPFILE or control file. See “Recovering from a Lost SPFILE or Control File” for details.

In the Backup/Recovery section of the Maintenance page, click Perform Recovery.
The Perform Recovery page appears.
Select Recover to the current time or a previous point-in-time and click Perform Whole Database Recovery. Also, provide the requested host credentials at this time if necessary. Click Continue.
The Confirmation page appears.
Click Yes to confirm the shutdown of the database.
The Recovery Wizard page appears. At this point, Oracle shuts down your database.

Note:

When the database is shut down and brought to the MOUNTEDstate, Enterprise Manager is also shut down briefly and restarted. During this process, there is a period during which Enterprise Manager cannot respond to your browser, or may respond with an error. Refresh the page until Enterprise Manager responds again.When Enterprise Manager has restarted and the database is being started and brought to the MOUNTED state, Enterprise Manager may also briefly report that the database is reported to be in NOMOUNT state. You are offered the choices Refresh, Startup, and Perform Recovery. Refresh the page periodically until the Database Instance page reports that the database instance is mounted before proceeding.
Click Perform Recovery to resume your recovery session. You may be prompted for host and database credentials. Connect with SYSDBA role, or provide host credentials for a user in the DBA group.
When the Perform Recovery page is displayed again, it shows that the database is in a MOUNTED state (as is required for this operation).
At this point, as you did before, under Whole Database Recovery, select Recover to the current time or a previous point-in-time and click Perform Whole Database Recovery.
The Perform Whole Database Recovery: Point-in-time page appears.
Specify whether to recover all transactions to your database as of the present time (which is called complete recovery), or only transactions up through some point in time in the past (which is called point-in-time recovery).

Note:

Point-in-time recovery is a recovery technique that lets you return the database to its state before some unwanted major change.

For this example, select Recover to the current time and then click Next.

The Perform Whole Database Recovery: Rename page appears.
You can specify a new directory or new filename for the restored files. For this example, select No to restore the files to the default location, which is its location before the restore operation. Click Next to continue.
The Perform Whole Database Recovery: Review page appears.
Review the options you chose. You can click Edit RMAN script to display the RMAN script that will be run to carry out your requested restore and recovery action. Click Submit to start the recovery.

Recovering from a Lost SPFILE or Control File

Recovery from loss of the control file or SPFILE must be started with the database shut down. Note that the instance cannot be running if the control file is lost.

Typically, diagnosis of the lost control file occurs when you attempt to start the database and the startup fails. The basic steps are as follows:

From the database home page, click Startup to attempt to start the database.
If the startup fails, click View Details to view the reason.
If the cause is a failure to locate the SPFILE or control file, return to the database home page and click Perform Recovery.
From this point, Enterprise Manager’s Guided Recovery walks you through the process to restore the lost files.

After the control file is restored from backup, the database is mounted. When a control file restored from backup is used to start the database, you must perform complete recovery of the datafiles, even if none of the datafiles have been restored from backup. The database must be opened with the RESETLOGS option after the datafiles are recovered.

To perform complete recovery of the datafiles, use the following steps:

On the Perform Recovery page, select Recover to the current time or a previous point in time and click Perform Whole Database Recovery.
On the Perform Whole Database Recovery: Rename page, select Restore files to the default location and click Next.
On the Perform Whole Database Recovery: Review page, click Submit to start the media recovery process.

When recovery is complete, you are prompted to open the database with the RESETLOGS option.

Validating the Restore of Datafiles from RMAN Backup

Validating the restore of datafiles from a backup tests whether a sufficient set of backups exists that can be used to restore the specified files. After you specify which tablespaces to restore and, possibly, a point in time as of which to restore them, RMAN selects a set of backups that contain the needed data. RMAN then reads the selected backups in their entirety to confirm that they are not corrupt.

Note:

This operation corresponds to the RESTORE … VALIDATE command in Recovery Manager.
Validating the restore of files tests whether the file can be restored given the available backups, but it does not test whether all backups of the specified object are valid.

Validating particular backups and validating specific restore tasks are both useful in validating your backup strategy.

In the Backup/Recovery section of the Maintenance property page, click Perform Recovery.
The Perform Recovery page appears.
Specify the datafiles to validate individually or you can specify entire tablespaces. In the Object Level Recovery section, select Datafiles or Tablespaces. For the Operation Type, select Restore Datafiles or Restore Tablespaces. Make sure the host credentials are correct, and click Perform Object Level Recovery.
The Perform Object Level Recovery page appears.
Click Add to add tablespaces or datafiles for the validate operation. After making your selections, click Next.
The Perform Object Level Recovery: Restore page appears.
In the Backup Selection section, specify which backups to restore from. In the Backup Validation section, be sure to check Validate the specified backup without restoring the datafiles. Then click Next.
The Perform Object Level Recovery: Schedule page appears.
Specify a job name and description. The job will run immediately, so you are not prompted for a scheduled time. Click Next.
The Perform Object Level Recovery: Review page appears.
You can edit the RMAN script to be run or leave it as-is. Click Submit Job to run the validation.
The Perform Recovery: Result page appears.
Click View Job to view the progress of the running job, or click OK to return to the database home page.

Returning a Table to a Past State: Flashback Table

Oracle Flashback Table lets you revert one or more tables back to their contents at a previous time without affecting other objects in your database. This recovery technique lets you recover from logical data corruptions, such as erroneously inserting rows into a table or deleting data from a table. Flashback Table lets you return tables you select to their state at a past point in time without undoing desired changes to the other objects in your database, as would be required by a point-in-time recovery of the entire database. Also, unlike point-in-time recovery, your database remains available during the operation.

For this example, you will perform Flashback Table on the employees table in the hr schema. Assume that an erroneous update shortly after October 23, 2005, 15:30:00 has changed the lastname column for all employees to an empty string and you need to return the original lastname values to the table.

Before you can perform Flashback Table, you must ensure that row movement is enabled on the table to be flashed back.

Enabling Row Movement on a Table

To enable row movement on a table, or if you do not know whether row movement is enabled on the table, follow these steps:

In the Database Objects section of the Administration page, click Tables to administer tables.
The Tables page appears.
To find the target table for flashback table, you can enter one or both of the schema name in the Schema field and the table name in the Object Name field. Then click Go to search for the table. For example, search for tables in the hr schema. You may need to page through the search results to find your table.
After you find your table in the schema, select the table from the list of tables. For example, select employees. Click Edit.
The Edit Table: table_name page appears.
Click Options to navigate to the Options property page. Make sure Enable Row Movement is set to Yes, and click Apply to update the options for the table.

When the page has refreshed, you can click Tables in the locator link at the top of the page to return to the search results, and enable row movement on more tables by repeating these steps for each table.

Performing Flashback Table

To perform the Flashback Table operation:

In the Backup/Recovery section of the Maintenance page, select Perform Recovery.
The Perform Recovery page appears.
In the Object Level Recovery section, select Tables for the object type. The page reloads with options appropriate for object level recovery of tables. Choose the Flashback Existing Tables option and click Perform Object Level Recovery.
The Perform Object Level Recovery: Point-in-time page appears.
Choose the target time for your Flashback Table operation.

Note:

If you do not know the time at which the unwanted changes occurred, you can investigate the history of transactions affecting this table by choosing Evaluate row changes and transactions to decide upon a point in time. A feature called Oracle Flashback Versions Query lets you review all recent changes to the target table. Use of this feature is beyond the scope of this manual.

For this example, assume that the time of the corruption is known to be October 23, 2005, 15:36:00. In the form offered, select Flashback to a timestamp, and enter your target time. Click Next to continue with the Flashback Table process.

The Perform Object Level Recovery: Flashback Tables page appears.
Specify the target tables for Flashback Table by entering table names (one on each line) in the Tables to Flashback text box. You can enter multiple table names to flash several tables back to the same time. You can also click Add Tables and search for more tables to add. For this example, manually enter the hr.employees table in the Tables to Flashback text box. Click Next to continue with the Flashback Table process.
If your table has other dependent tables, then the Dependency Options page appears. This page asks how dependencies should be handled.
You can choose Cascade (flashing back any dependent tables), Restrict (flashing back only the target table), or Customize (selecting which dependent tables to flashback and which to leave as they are). You can click Show Dependencies to see which tables will be affected. How you proceed at this point will depend upon your application.
hr.employees has dependent tables hr.jobs and hr.departments. For this example, assume that it is safe to cascade any changes, flashing back those two tables as well as the hr.employees table.

Note:

Row movement must be enabled on all affected tables, not just the initial target tables.

Click Next to continue.

The Perform Object Level Recovery: Review page appears.
Review the target timestamp and tables to be flashed back. Click Submit to perform the Flashback Table operation.
When the operation is completed, a Confirmation page displays the results. Click OK to return to the database home page.

Recovering Dropped Tables: Flashback Drop

Oracle Flashback Drop lets you reverse the effects of dropping a table, returning the dropped table to the database along with its dependent objects such as indexes and triggers. It works by storing dropped objects in a Recycle Bin, from which they may be retrieved until the Recycle Bin is purged, either explicitly or because space is needed for new database objects.

As with Flashback Table, Flashback Drop can be used while the rest of your database remains open, and without undoing desired changes in objects not affected by the Flashback Drop operation. It is more convenient than forms of recovery that require taking the database offline and restoring files from backup.

Note:

For a table to be recoverable using Flashback Drop, it must reside in a locally managed tablespace. Also, tables in the SYSTEM tablespaces cannot recovered using Flashback Drop regardless of the tablespace type.

To perform the Flashback Drop operation:

In the Backup/Recovery section of the Maintenance page, select Perform Recovery.
The Perform Recovery page appears.
In the Object Level Recovery section, select Tables for the object type. The page reloads with options appropriate for tables in the Object Level Recovery section. For the Operation Type, select Flashback dropped tables and click Perform Object Level Recovery.
The Perform Object Level Recovery: Dropped Objects Selection page appears.
The Search form lets you search among the dropped objects in the Recycle Bin for the objects you want to recover. Provide values for one or both of the Schema Name and Table fields, and click Go to search.
When the page refreshes, the Results section lists the objects matching your search. If you only see the Recycle Bin listed, then click the arrow next to the Recycle Bin to expand its contents by one level, showing dropped tables matching your search but not their dependent objects. You can further expand individual tables, or click Expand All to see all objects in the Recycle Bin, including both dropped tables and dependent objects such as indexes and triggers. For each table listed, you can click View Content in the Operation column to display its contents.To select one or more tables for Flashback Drop, click the checkbox next to each table.

Note:

When a table is retrieved from the Recycle Bin, all of the dependent objects for the table that are in the recycle bin are retrieved with it. They cannot be retrieved separately.

When you have selected all of the objects to restore, click Next.

The Perform Object Level Recovery: Rename page appears.
If needed, specify new names for any dropped objects you are returning to your database. The primary reason for renaming objects when you retrieve them from the recycle bin is if you have created new tables with the same names as tables being retrieved. If you need to rename some objects, then enter new names as needed in the New Name field in the list of tables being flashed back. Click Next to continue.
The Perform Object Level Recovery: Review page appears. This page displays an impact analysis, showing the full set of objects to be flashed back, including the dependent objects, as well as the names they will have when the Flashback Drop operation is complete.
If you are satisfied with the changes listed in the review, click Submit to perform the Flashback Drop.
A confirmation page should indicate the success of the operation.
Click OK to return to the database home page.

Managing Your Backups

Managing RMAN backups, with or without Enterprise Manager, consists of two tasks: managing the backups of your database that are stored on disk or tape, and managing the record of those backups in the RMAN repository. Enterprise Manager simplifies both backup management tasks.

Backup Management: Concepts

A backup recorded in the RMAN repository can be in one of the following states:

Available, meaning that the backup is still present on disk or tape, as recorded in the repository
Expired, meaning that the backup no longer exists on disk or tape but is still listed in the repository
Unavailable, meaning that the backup is temporarily not available for data recovery operations (because, for example, it is stored on a tape that is stored off-site or a disk that is not mounted at the moment)

Backups can also be obsolete. An obsolete backup is one which, based on the currently configured retention policy, is no longer needed to satisfy data recovery goals.

Backup maintenance tasks that you can perform in Enterprise Manager include the following:

Viewing details about your backups
Crosschecking your repository, which checks whether backups listed in the repository exist and are accessible and marks as expired any backups not accessible at the time of the crosscheck
Deleting the record of expired backups from your RMAN repository
Deleting obsolete backups from the repository and from disk
Validating backups, to ensure that a given backup is still available and has not been corrupted

Note:

If a backup no longer exists, immediately delete the record of that backup from the RMAN repository. Without an accurate record of available backups, you may discover that you no longer have complete backups of your database when you try to perform a database recovery operation.

Note that if you use a flash recovery area for your backup storage, many maintenance activities are reduced or eliminated. The flash recovery area’s automatic disk space management mechanisms delete backups and other files as needed, to satisfy demands for space in ongoing database operations, without compromising the retention policy.

Using the Manage Current Backups Page

To access backup management functions, navigate to the database home page and click Maintenance to open the Maintenance property page. In the Backup/Recovery section, click Manage Current Backups.

The Manage Current Backups page has two property pages you can choose: Backup Set and Image Copy. Each serves a similar purpose, listing backups (stored as backup sets or image copies), based on the record in the Recovery Manager repository, and allowing for management of the backups listed. Figure 9-3, “Manage Current Backups Page” shows a typical view of the Backup Set property page.

Figure 9-3 Manage Current Backups Page

Description of “Figure 9-3 Manage Current Backups Page”

The Image Copies property page is similar, with the same operations supported. The elements on the Manage Current Backups page can be categorized as follows:

Buttons at the top of the page that allow you to perform maintenance tasks related to your entire collection of backups
The Search section, which enables you to select a subset of your backup sets or image copies for maintenance actions
The Results section, where you can select individual backups for maintenance actions, invoked using buttons at the top of the Results section

Searching for Backups on the Manage Current Backups Page

The Search section of each property page lets you restrict the listed backups based on the following:

Backup status (available, unavailable, or expired)
The types of files contained in the backup (archived log, datafile, control file, or SPFILE)
How recently the backup was completed

By default, all available backups from the last month are listed. Specify criteria as necessary to filter the backup list, and click Go to search the backups.

The Results section of the page lists backups that match the criteria specified in the Search section. Different details are listed for backup sets (on the Backup Sets property page) and for image copies (on the Image Copies property page).

Managing Current Backups: Backup Sets

On the Backup Sets property page, backup sets are identified by the unique value in the Key column (an internally generated number used to identify the backup set in RMAN commands) as well as the value in the Tag column. For each backup, the page lists information such as the type of files backed up, the destination device type, completion time of the backup, and current status.

You can click the value in the Tag column for a given backup set to view details about the backup set, including:

The input files backed up (listed in the Input Files section of the page), and details about each input file, including the original file location, size, and file checkpoint time and SCN when the backup was taken

The backup pieces produced as output by the backup job, including the output filename, tag, destination device type, size, and status

Managing Current Backups: Image Copies

On the Image Copies property page, image copies are identified by the unique value in the Key column (an internally generated number used to identify the backup set in RMAN commands) as well as the filename in the Name column. For each backup, the file type, tag, completion time and status are also listed. This page also lists the status of each image copy (available, unavailable, or expired) and a Keep column, which shows whether special exceptions to the retention policy for backups apply to this file.

By clicking the value in the Name column, you can also view details about that file, such as datafile number, file size, tablespace name, creation SCN, and last checkpoint SCN and time when the file was backed up.

You can also crosscheck or delete individual backups, or mark individual backups as unavailable if you know that they are temporarily not accessible by RMAN. For example, if you know that some backups are stored on tapes that have been moved offsite for long-term storage, you can mark them as unavailable to prevent RMAN from considering them usable in restore operations. Use the Select checkbox next to the file, and click the appropriate action button at the top of the Results list.

The Image Copy property page presents similar functionality to the Backup Sets property page. The focus in this section is on commands in the Backup Sets property page, where they are substantially similar to those for image copies.

As with the backup and restore commands in Enterprise Manager, the commands to crosscheck, delete and change the status of backups are ultimately translated to RMAN commands. The commands are submitted as RMAN jobs that can be run immediately or scheduled. Some tasks, such as periodic crosschecks of your backups, should be among the regularly scheduled components of your backup strategy.

Validating the Contents of Backup Sets or Image Copies

When you validate a backup, you ensure that the files for the backup are present, have not been corrupted, and can be used in recovery operations. The selected backup files are read in their entirety and checked for errors.

Note:

Validating specific backups only checks whether those backups are readable. It does not test whether the set of available backups meet your recoverability goals.For example, you can have image copies of datafiles for several tablespaces from your database, each of which can be validated. If there are some tablespaces for which no valid backups exist, however, you cannot restore and recover your database.

Validating a restore operation ensures that the set of backups available can actually be used to perform a specified restore operation, such as restoring a specific tablespace or your entire database.

To validate whether specific backups are usable in a restore and recovery operation:

In the Backup/Recovery section of the Maintenance property page, click Manage Current Backups.
The Manage Current Backups page appears.
Use the search features on this page to identify the backups whose contents you wish to validate, as described in “Using the Manage Current Backups Page”.
Select the checkboxes next to each backup of interest in the list of current backups and click Validate.
The Validate: Specify Job Parameters page appears.
Specify a job name and description, as well as time settings for starting and repeating the operation. You can click Show RMAN Script to view the RMAN commands used to perform your validation or Submit Job to start the job running according to its schedule.
A message confirms the submission of the job.
Click View Job to display details about the validation.

This process corresponds to the RMAN VALIDATE command. See Oracle Database Backup and Recovery Basics for details on the use of the RMAN VALIDATE command.

Crosschecking Backups

Crosschecking a backup causes RMAN to verify that the actual physical status of the backup matches the record of the backup in the RMAN repository. For example, if a backup on disk has been deleted with an operating system command and is therefore no longer available for use in restore operations, then crosschecking that file detects this condition. After the crosscheck operation, the RMAN repository correctly reflects the state of the backups on disk or tape.

Backups to disk are marked AVAILABLE if they are still present on disk in the location listed in the RMAN repository, and if they have no corruption in the file header. Backups on tape are listed as AVAILABLE if they are still present on tape (though the file headers are not checked for corruption). Backups that are missing or corrupt are marked EXPIRED.

To crosscheck individual files:

Use the search features of the Manage Current Backups page to find the backup sets or image copies whose contents you wish to crosscheck, as described in “Using the Manage Current Backups Page”.
Click the Select checkbox next to each backup in the Results list to be included in the crosscheck operation. Enterprise Manager does not support selecting both image copies and backup sets for crosscheck within a single operation.
Click Crosscheck at the top of the Results list. After a confirmation page, Enterprise Manager performs the crosscheck.

You can also crosscheck all backups recorded in the RMAN repository in one step by clicking Crosscheck All at the top of the page. In the Crosscheck All: Specify Job Parameters page, you can schedule the crosscheck to run now or at a later time, or even specify regularly scheduled crosschecks.

Note:

Crosschecking all backups in the RMAN repository may take a long time, especially for tape backups. A crosscheck of all files, unlike crosschecking individual files, is handled as a scheduled job.

This process corresponds to the RMAN CROSSCHECK command. See Oracle Database Backup and Recovery Basics for details.

Deleting Expired Backups

Deleting expired backups removes from the RMAN repository those backups which are marked EXPIRED, that is, those which RMAN found to be inaccessible during a crosscheck operation. No attempt is made to delete the files containing the backup from disk or tape; this action only updates the RMAN repository.

To delete expired backups:

In the Manage Current Backups page, click Delete All Expired. Note that this action deletes both expired backup sets and expired image copies from the RMAN repository, regardless of whether you are viewing the Backup Sets or Image Copies property page when you click Delete All Expired.
The Delete All Expired: Specify Job Parameters page appears.
Select your scheduling options. Along with the usual scheduling options for an RMAN job, there is a checkbox Perform the operation ‘Crosscheck All’ before ‘Delete All Expired’. Checking this box will cause the operation to take longer, but by performing the crosscheck operation immediately before deleting expired backups from the repository, RMAN will have the most up-to-date information possible about which backups are expired. Click Submit Job.
A message should appear indicating that your job was successfully submitted.
Click View Job to display details about the deletion operation.

Marking Backups as Available or Unavailable

If you know that one or more specific backups are unavailable because of a temporary condition, such as a disk drive that is temporarily offline or a tape stored off-site, then you can mark those backups as unavailable. RMAN does not try to use unavailable backups in restore and recovery operations. RMAN keeps the record of unavailable backups in the RMAN repository, however, and when you delete expired backups, RMAN does not try to delete backups marked unavailable. When the backups become accessible again, you can mark them as available.

Included among the buttons at the top of the Results section of the Manage Current Backups page are buttons for marking backups as available or unavailable.

Note:

If you have restricted the backups listed by searching only for available backups, only the Change to Unavailable button appears. If you have restricted the backups listed by searching only for unavailable backups, only the Change to Available button appears.

To mark backups as available, click the Select checkbox next to each backup in the Results list of backups, and select Change to Available.

To mark backups as unavailable, click the Select checkbox next to each backup in the Results list of backups, and select Change to Unavailable.

Note:

Backups stored in the flash recovery area cannot be marked as unavailable.

Deleting Obsolete Backups

To delete obsolete backups, that is, backups no longer needed to meet your retention policy, click Delete All Obsolete at the top of the Manage Current Backups page.

When you click Delete All Obsolete, you arrive at the Delete All Obsolete: Specify Job Parameters page. You can run the deletion job immediately or schedule it as you would a backup job.

Note:

All obsolete backups (both backup sets and image copies) will be deleted, regardless of whether you clicked Delete All Obsolete while viewing the Backup Set or Image Copy property page on the Manage Current Backups page.

Note that if you use a flash recovery area as your sole disk-based backup destination, then you never need to delete obsolete backups from disk. The automatic space management of the flash recovery area will keep files as specified by the backup retention policy, and then only delete them when space is needed.

Displaying Backup Reports

Backup reports contain summary and detailed information about past backup jobs run by RMAN, including both backups run through Enterprise Manager and the RMAN command-line client.

To view backup reports, from the Maintenance property page, in the Backup and Recovery section, click Backup Reports.

Figure 9-4 Backup Reports Page

Description of “Figure 9-4 Backup Reports Page”

The page contains a list of recent backup jobs. You can use the Filter By section of the page to restrict the backups listed by the time of the backup, the type of data backed up, and the status of the jobs to be listed (whether it succeeded or failed, and whether warnings were generated during the job). Specify any filter conditions and click Go to restrict the list to backups of interest.

The list contains basic information for each backup:

The name, start time and run time of the backup
The type of the backup (such as full or incremental database backup, archived log backup, backup of datafiles only, and so on)
The status of the backup (whether it succeeded or failed, and whether there were warnings associated with it)
The output destination (which can be disk or tape, or * to indicate output on both disk and tape
The size of inputs (files backed up), output (files written to disk or tape) and the rate at which backups were written

To view detailed information about any backup, click the value in the Backup Name column. The View Backup Report page is displayed for the selected backup. This page contains summary information about this backup (how many files of each type were backed up, how much data total, and the number, size and type of output files created), as well as details about inputs and outputs for the backup job, such as:

Datafiles backed up, by datafile number and tablespace name
Archived logs by sequence and thread number, and range of times and SCNs covered by the logs
Number of corrupt blocks found in input files
Tags, backup piece names or filenames for outputs
Size of output files and compression ratios

For recent jobs, you can also view a log of RMAN output for the job, by clicking the value in the Status column.

Note:

The control file view V$RMAN_OUTPUT contains the output of recent RMAN jobs. The contents of this view are not preserved if the instance is restarted. Therefore, the output from past RMAN jobs may not be available.

The View Backup Report page also contains a Filter By section that you can use to quickly run a search for another backup or backups from a specific date range. The resulting report contains aggregate information for backups matching the search criteria.

Go to Detail Page

Flashback New Features and Enhancements in Oracle Database 10g

October 28, 2011 Articles 0 0

Flashback New Features and Enhancements in Oracle Database 10g

Oracle9i introduced the DBMS_FLASHBACK package to allow queries to reference older versions of the database. Oracle 10g has taken this technology a step further making it simpler to use and much more flexible.

Note: Internally Oracle uses SCNs to track changes so any flashback operation that uses a timestamp must be translated into the nearest SCN which can result in a 3 second error.

Flashback Query
Flashback Version Query
Flashback Transaction Query
Flashback Table
Flashback Drop (Recycle Bin)
Flashback Database
Flashback Query Functions

Flashback Query

Flashback Query allows the contents of a table to be queried with reference to a specific point in time, using the AS OF clause. Essentially it is the same as the DBMS_FLASHBACK functionality or Oracle 9i, but in a more convenient form. For example.

CREATE TABLE flashback_query_test (
  id  NUMBER(10)
);

SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
     722452 2004-03-29 13:34:12

INSERT INTO flashback_query_test (id) VALUES (1);
COMMIT;

SELECT COUNT(*) FROM flashback_query_test;

  COUNT(*)
----------
         1

SELECT COUNT(*) FROM flashback_query_test AS OF TIMESTAMP TO_TIMESTAMP('2004-03-29 13:34:12', 'YYYY-MM-DD HH24:MI:SS');

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

SELECT COUNT(*) FROM flashback_query_test AS OF SCN 722452;

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

Flashback Version Query

Flashback version query allows the versions of a specific row to be tracked during a specified time period using the VERSIONS BETWEEN clause.

CREATE TABLE flashback_version_query_test (
  id           NUMBER(10),
  description  VARCHAR2(50)
);

INSERT INTO flashback_version_query_test (id, description) VALUES (1, 'ONE');
COMMIT;

SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
     725202 2004-03-29 14:59:08

UPDATE flashback_version_query_test SET description = 'TWO' WHERE id = 1;
COMMIT;
UPDATE flashback_version_query_test SET description = 'THREE' WHERE id = 1;
COMMIT;

SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
     725219 2004-03-29 14:59:36

COLUMN versions_startscn FORMAT 99999999999999999
COLUMN versions_starttime FORMAT A24
COLUMN versions_endscn FORMAT 99999999999999999
COLUMN versions_endtime FORMAT A24
COLUMN versions_xid FORMAT A16
COLUMN versions_operation FORMAT A1
COLUMN description FORMAT A11
SET LINESIZE 200

SELECT versions_startscn, versions_starttime,
       versions_endscn, versions_endtime,
       versions_xid, versions_operation,
       description
FROM   flashback_version_query_test
       VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2004-03-29 14:59:08', 'YYYY-MM-DD HH24:MI:SS')
       AND TO_TIMESTAMP('2004-03-29 14:59:36', 'YYYY-MM-DD HH24:MI:SS')
WHERE  id = 1;

 VERSIONS_STARTSCN VERSIONS_STARTTIME          VERSIONS_ENDSCN VERSIONS_ENDTIME         VERSIONS_XID     V DESCRIPTION
------------------ ------------------------ ------------------ ------------------------ ---------------- - -----------
            725212 29-MAR-04 02.59.16 PM                                                02001C0043030000 U THREE
            725209 29-MAR-04 02.59.16 PM                725212 29-MAR-04 02.59.16 PM    0600030021000000 U TWO
                                                        725209 29-MAR-04 02.59.16 PM                       ONE
SELECT versions_startscn, versions_starttime,
       versions_endscn, versions_endtime,
       versions_xid, versions_operation,
       description
FROM   flashback_version_query_test
       VERSIONS BETWEEN SCN 725202 AND 725219
WHERE  id = 1;

 VERSIONS_STARTSCN VERSIONS_STARTTIME          VERSIONS_ENDSCN VERSIONS_ENDTIME         VERSIONS_XID     V DESCRIPTION
------------------ ------------------------ ------------------ ------------------------ ---------------- - -----------
            725212 29-MAR-04 02.59.16 PM                                                02001C0043030000 U THREE
            725209 29-MAR-04 02.59.16 PM                725212 29-MAR-04 02.59.16 PM    0600030021000000 U TWO
                                                        725209 29-MAR-04 02.59.16 PM                       ONE

The available pseudocolumn meanings are:

VERSIONS_STARTSCN or VERSIONS_STARTTIME – Starting SCN and TIMESTAMP when row took on this value. The value of NULL is returned if the row was created before the lower bound SCN ot TIMESTAMP.
VERSIONS_ENDSCN or VERSIONS_ENDTIME – Ending SCN and TIMESTAMP when row last contained this value. The value of NULL is returned if the value of the row is still current at the upper bound SCN ot TIMESTAMP.
VERSIONS_XID – ID of the transaction that created the row in it’s current state.
VERSIONS_OPERATION – Operation performed by the transaction ((I)nsert, (U)pdate or (D)elete)

Flashback Transaction Query

Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries. The VERSIONS_XID column values from a flashback version query can be used to query the FLASHBACK_TRANSACTION_QUERY view.

SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
FROM   flashback_transaction_query
WHERE  xid = HEXTORAW('0600030021000000');

XID              OPERATION                         START_SCN COMMIT_SCN
---------------- -------------------------------- ---------- ----------
LOGON_USER
------------------------------
UNDO_SQL
----------------------------------------------------------------------------------------------------
0600030021000000 UPDATE                               725208     725209
SCOTT
update "SCOTT"."FLASHBACK_VERSION_QUERY_TEST" set "DESCRIPTION" = 'ONE' where ROWID = 'AAAMP9AAEAAAA
AYAAA';

0600030021000000 BEGIN                                725208     725209
SCOTT

XID              OPERATION                         START_SCN COMMIT_SCN
---------------- -------------------------------- ---------- ----------
LOGON_USER
------------------------------
UNDO_SQL
----------------------------------------------------------------------------------------------------

2 rows selected.

Flashback Table

The FLASHBACK TABLE command allows point in time recovery of individual tables subject to the following requirements.

You must have either the FLASHBACK ANY TABLE system privilege or have FLASHBACK object privilege on the table.
You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
There must be enough information in the undo tablespace to complete the operation.
Row movement must be enabled on the table (ALTER TABLE tablename ENABLE ROW MOVEMENT;).

The following example creates a table, inserts some data and flashbacks to a point prior to the data insertion. Finally it flashbacks to the time after the data insertion.

CREATE TABLE flashback_table_test (
  id  NUMBER(10)
);

ALTER TABLE flashback_table_test ENABLE ROW MOVEMENT;

SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
     715315

INSERT INTO flashback_table_test (id) VALUES (1);
COMMIT;

SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
     715340

FLASHBACK TABLE flashback_table_test TO SCN 715315;

SELECT COUNT(*) FROM flashback_table_test;

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

FLASHBACK TABLE flashback_table_test TO SCN 715340;

SELECT COUNT(*) FROM flashback_table_test;

  COUNT(*)
----------
         1

Flashback of tables can also be performed using timestamps.

FLASHBACK TABLE flashback_table_test TO TIMESTAMP TO_TIMESTAMP('2004-03-03 10:00:00', 'YYYY-MM-DD HH:MI:SS');

Flashback Drop (Recycle Bin)

In Oracle 10g the default action of a DROP TABLE command is to move the table to the recycle bin (or rename it), rather than actually dropping it. The PURGE option can be used to permanently drop a table.

The recycle bin is a logical collection of previously dropped objects, with access tied to the DROP privilege. The contents of the recycle bin can be shown using the SHOW RECYCLEBIN command and purged using the PURGE TABLE command. As a result, a previously dropped table can be recovered from the recycle bin.

CREATE TABLE flashback_drop_test (
  id  NUMBER(10)
);

INSERT INTO flashback_drop_test (id) VALUES (1);
COMMIT;

DROP TABLE flashback_drop_test;

SHOW RECYCLEBIN

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_T BIN$TstgCMiwQA66fl5FFDTBgA==$0 TABLE        2004-03-29:11:09:07
EST

FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;

SELECT * FROM flashback_drop_test;

        ID
----------
         1

Tables in the recycle bin can be queried like any other table.

DROP TABLE flashback_drop_test;

SHOW RECYCLEBIN

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_T BIN$TDGqmJZKR8u+Hrc6PGD8kw==$0 TABLE        2004-03-29:11:18:39
EST

SELECT * FROM "BIN$TDGqmJZKR8u+Hrc6PGD8kw==$0";

        ID
----------
         1

If an object is dropped and recreated multiple times all dropped versions will be kept in the recycle bin, subject to space. Where multiple versions are present it’s best to reference the tables via the RECYCLEBIN_NAME. For any references to the ORIGINAL_NAME it is assumed the most recent object is drop version in the referenced question. During the flashback operation the table can be renamed.

FLASHBACK TABLE flashback_drop_test TO BEFORE DROP RENAME TO flashback_drop_test_old;

Several purge options exist:

PURGE TABLE tablename;                   -- Specific table.
PURGE INDEX indexname;                   -- Specific index.
PURGE TABLESPACE ts_name;                -- All tables in a specific tablespace.
PURGE TABLESPACE ts_name USER username;  -- All tables in a specific tablespace for a specific user.
PURGE RECYCLEBIN;                        -- The current users entire recycle bin.
PURGE DBA_RECYCLEBIN;                    -- The whole recycle bin.

Several restrictions apply relating to the recycle bin.

Only available for non-system, locally managed tablespaces.
There is no fixed size for the recycle bin. The time an object remains in the recycle bin can vary.
The objects in the recycle bin are restricted to query operations only (no DDL or DML).
Flashback query operations must reference the recycle bin name.
Tables and all dependent objects are placed into, recovered and purged from the recycle bin at the same time.
Tables with Fine Grained Access policies aer not protected by the recycle bin.
Partitioned index-organized tables are not protected by the recycle bin.
The recycle bin does not preserve referential integrity.

Flashback Database

The FLASHBACK DATABASE command is a fast alternative to performing an incomplete recovery. In order to flashback the database you must have SYSDBA privilege and the flash recovery area must have been prepared in advance.

If the database is in NOARCHIVELOG it must be switched to ARCHIVELOG mode.

CONN sys/password AS SYSDBA
ALTER SYSTEM SET log_archive_dest_1='location=d:\oracle\oradata\DB10G\archive\' SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_format='ARC%S_%R.%T' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Flashback must be enabled before any flashback operations are performed.

CONN sys/password AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

With flashback enabled the database can be switched back to a previous point in time or SCN without the need for a manual incomplete recovery. In the following example a table is created, the database is then flashbacked to a time before the table was created.

-- Create a dummy table.
CONN scott/tiger
CREATE TABLE flashback_database_test (
  id  NUMBER(10)
);

-- Flashback 5 minutes.
CONN sys/password AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12);
ALTER DATABASE OPEN RESETLOGS;

-- Check that the table is gone.
CONN scott/tiger
DESC flashback_database_test

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;

The window of time that is available for flashback is determined by the DB_FLASHBACK_RETENTION_TARGET parameter. The maximum flashback can be determined by querying the V$FLASHBACK_DATABASE_LOG view. It is only possible to flashback to a point in time after flashback was enabled on the database and since the last RESETLOGS command.

Flashback Query Functions

The TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP functions have been added to SQL and PL/SQL to simplify flashback operations.

SELECT *
FROM   emp AS OF SCN TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);

SELECT *
FROM   emp AS OF TIMESTAMP SCN_TO_TIMESTAMP(993240);

DECLARE
  l_scn        NUMBER;
  l_timestamp  TIMESTAMP;
BEGIN
  l_scn       := TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);
  l_timestamp := SCN_TO_TIMESTAMP(l_scn);
END;
/

Go to Detail Page

Flashback New Features and Enhancements in Oracle Database 10g

December 7, 2011 Articles 0 0

Flashback New Features and Enhancements in Oracle Database 10g

Note: Internally Oracle uses SCNs to track changes so any flashback operation that uses a timestamp must be translated into the nearest SCN which can result in a 3 second error.

Flashback Query
Flashback Version Query
Flashback Transaction Query
Flashback Table
Flashback Drop (Recycle Bin)
Flashback Database
Flashback Query Functions

Flashback Query

CREATE TABLE flashback_query_test (
  id  NUMBER(10)
);

SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
     722452 2004-03-29 13:34:12

INSERT INTO flashback_query_test (id) VALUES (1);
COMMIT;

SELECT COUNT(*) FROM flashback_query_test;

  COUNT(*)
----------
         1

SELECT COUNT(*) FROM flashback_query_test AS OF TIMESTAMP TO_TIMESTAMP('2004-03-29 13:34:12', 'YYYY-MM-DD HH24:MI:SS');

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

SELECT COUNT(*) FROM flashback_query_test AS OF SCN 722452;

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

Flashback Version Query

Flashback version query allows the versions of a specific row to be tracked during a specified time period using the VERSIONS BETWEEN clause.

CREATE TABLE flashback_version_query_test (
  id           NUMBER(10),
  description  VARCHAR2(50)
);

INSERT INTO flashback_version_query_test (id, description) VALUES (1, 'ONE');
COMMIT;

SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
     725202 2004-03-29 14:59:08

UPDATE flashback_version_query_test SET description = 'TWO' WHERE id = 1;
COMMIT;
UPDATE flashback_version_query_test SET description = 'THREE' WHERE id = 1;
COMMIT;

SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
     725219 2004-03-29 14:59:36

COLUMN versions_startscn FORMAT 99999999999999999
COLUMN versions_starttime FORMAT A24
COLUMN versions_endscn FORMAT 99999999999999999
COLUMN versions_endtime FORMAT A24
COLUMN versions_xid FORMAT A16
COLUMN versions_operation FORMAT A1
COLUMN description FORMAT A11
SET LINESIZE 200

SELECT versions_startscn, versions_starttime,
       versions_endscn, versions_endtime,
       versions_xid, versions_operation,
       description
FROM   flashback_version_query_test
       VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2004-03-29 14:59:08', 'YYYY-MM-DD HH24:MI:SS')
       AND TO_TIMESTAMP('2004-03-29 14:59:36', 'YYYY-MM-DD HH24:MI:SS')
WHERE  id = 1;

 VERSIONS_STARTSCN VERSIONS_STARTTIME          VERSIONS_ENDSCN VERSIONS_ENDTIME         VERSIONS_XID     V DESCRIPTION
------------------ ------------------------ ------------------ ------------------------ ---------------- - -----------
            725212 29-MAR-04 02.59.16 PM                                                02001C0043030000 U THREE
            725209 29-MAR-04 02.59.16 PM                725212 29-MAR-04 02.59.16 PM    0600030021000000 U TWO
                                                        725209 29-MAR-04 02.59.16 PM                       ONE
SELECT versions_startscn, versions_starttime,
       versions_endscn, versions_endtime,
       versions_xid, versions_operation,
       description
FROM   flashback_version_query_test
       VERSIONS BETWEEN SCN 725202 AND 725219
WHERE  id = 1;

 VERSIONS_STARTSCN VERSIONS_STARTTIME          VERSIONS_ENDSCN VERSIONS_ENDTIME         VERSIONS_XID     V DESCRIPTION
------------------ ------------------------ ------------------ ------------------------ ---------------- - -----------
            725212 29-MAR-04 02.59.16 PM                                                02001C0043030000 U THREE
            725209 29-MAR-04 02.59.16 PM                725212 29-MAR-04 02.59.16 PM    0600030021000000 U TWO
                                                        725209 29-MAR-04 02.59.16 PM                       ONE

The available pseudocolumn meanings are:

VERSIONS_STARTSCN or VERSIONS_STARTTIME – Starting SCN and TIMESTAMP when row took on this value. The value of NULL is returned if the row was created before the lower bound SCN ot TIMESTAMP.
VERSIONS_ENDSCN or VERSIONS_ENDTIME – Ending SCN and TIMESTAMP when row last contained this value. The value of NULL is returned if the value of the row is still current at the upper bound SCN ot TIMESTAMP.
VERSIONS_XID – ID of the transaction that created the row in it’s current state.
VERSIONS_OPERATION – Operation performed by the transaction ((I)nsert, (U)pdate or (D)elete)

Flashback Transaction Query

SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
FROM   flashback_transaction_query
WHERE  xid = HEXTORAW('0600030021000000');

XID              OPERATION                         START_SCN COMMIT_SCN
---------------- -------------------------------- ---------- ----------
LOGON_USER
------------------------------
UNDO_SQL
----------------------------------------------------------------------------------------------------
0600030021000000 UPDATE                               725208     725209
SCOTT
update "SCOTT"."FLASHBACK_VERSION_QUERY_TEST" set "DESCRIPTION" = 'ONE' where ROWID = 'AAAMP9AAEAAAA
AYAAA';

0600030021000000 BEGIN                                725208     725209
SCOTT

XID              OPERATION                         START_SCN COMMIT_SCN
---------------- -------------------------------- ---------- ----------
LOGON_USER
------------------------------
UNDO_SQL
----------------------------------------------------------------------------------------------------

2 rows selected.

Flashback Table

The FLASHBACK TABLE command allows point in time recovery of individual tables subject to the following requirements.

You must have either the FLASHBACK ANY TABLE system privilege or have FLASHBACK object privilege on the table.
You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
There must be enough information in the undo tablespace to complete the operation.
Row movement must be enabled on the table (ALTER TABLE tablename ENABLE ROW MOVEMENT;).

The following example creates a table, inserts some data and flashbacks to a point prior to the data insertion. Finally it flashbacks to the time after the data insertion.

CREATE TABLE flashback_table_test (
  id  NUMBER(10)
);

ALTER TABLE flashback_table_test ENABLE ROW MOVEMENT;

SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
     715315

INSERT INTO flashback_table_test (id) VALUES (1);
COMMIT;

SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
     715340

FLASHBACK TABLE flashback_table_test TO SCN 715315;

SELECT COUNT(*) FROM flashback_table_test;

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

FLASHBACK TABLE flashback_table_test TO SCN 715340;

SELECT COUNT(*) FROM flashback_table_test;

  COUNT(*)
----------
         1

Flashback of tables can also be performed using timestamps.

FLASHBACK TABLE flashback_table_test TO TIMESTAMP TO_TIMESTAMP('2004-03-03 10:00:00', 'YYYY-MM-DD HH:MI:SS');

Flashback Drop (Recycle Bin)

CREATE TABLE flashback_drop_test (
  id  NUMBER(10)
);

INSERT INTO flashback_drop_test (id) VALUES (1);
COMMIT;

DROP TABLE flashback_drop_test;

SHOW RECYCLEBIN

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_T BIN$TstgCMiwQA66fl5FFDTBgA==$0 TABLE        2004-03-29:11:09:07
EST

FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;

SELECT * FROM flashback_drop_test;

        ID
----------
         1

Tables in the recycle bin can be queried like any other table.

DROP TABLE flashback_drop_test;

SHOW RECYCLEBIN

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_T BIN$TDGqmJZKR8u+Hrc6PGD8kw==$0 TABLE        2004-03-29:11:18:39
EST

SELECT * FROM "BIN$TDGqmJZKR8u+Hrc6PGD8kw==$0";

        ID
----------
         1

FLASHBACK TABLE flashback_drop_test TO BEFORE DROP RENAME TO flashback_drop_test_old;

Several purge options exist.

PURGE TABLE tablename;                   -- Specific table.
PURGE INDEX indexname;                   -- Specific index.
PURGE TABLESPACE ts_name;                -- All tables in a specific tablespace.
PURGE TABLESPACE ts_name USER username;  -- All tables in a specific tablespace for a specific user.
PURGE RECYCLEBIN;                        -- The current users entire recycle bin.
PURGE DBA_RECYCLEBIN;                    -- The whole recycle bin.

Several restrictions apply relating to the recycle bin.

Only available for non-system, locally managed tablespaces.
There is no fixed size for the recycle bin. The time an object remains in the recycle bin can vary.
The objects in the recycle bin are restricted to query operations only (no DDL or DML).
Flashback query operations must reference the recycle bin name.
Tables and all dependent objects are placed into, recovered and purged from the recycle bin at the same time.
Tables with Fine Grained Access policies aer not protected by the recycle bin.
Partitioned index-organized tables are not protected by the recycle bin.
The recycle bin does not preserve referential integrity.

Flashback Database

If the database is in NOARCHIVELOG it must be switched to ARCHIVELOG mode.

CONN sys/password AS SYSDBA
ALTER SYSTEM SET log_archive_dest_1='location=d:\oracle\oradata\DB10G\archive\' SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_format='ARC%S_%R.%T' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Flashback must be enabled before any flashback operations are performed.

CONN sys/password AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

-- Create a dummy table.
CONN scott/tiger
CREATE TABLE flashback_database_test (
  id  NUMBER(10)
);

-- Flashback 5 minutes.
CONN sys/password AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12);
ALTER DATABASE OPEN RESETLOGS;

-- Check that the table is gone.
CONN scott/tiger
DESC flashback_database_test

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;

Flashback Query Functions

The TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP functions have been added to SQL and PL/SQL to simplify flashback operations.

SELECT *
FROM   emp AS OF SCN TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);

SELECT *
FROM   emp AS OF TIMESTAMP SCN_TO_TIMESTAMP(993240);

DECLARE
  l_scn        NUMBER;
  l_timestamp  TIMESTAMP;
BEGIN
  l_scn       := TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);
  l_timestamp := SCN_TO_TIMESTAMP(l_scn);
END;
/

Go to Detail Page

RMAN Enhancements in Oracle Database 10g

October 28, 2011 Articles 0 0

RMAN Enhancements in Oracle Database 10g

Oracle 10g includes many RMAN enhancements making it a more complete tool, including the following.

Flash Recovery Area
Incrementally Updated Backups
Fast Incremental Backups
BACKUP for Backupsets and Image Copies
Cataloging Backup Pieces
Improved RMAN Reporting Through V$ Views
Automatic Instance Creation for RMAN TSPITR
Cross-Platform Tablespace Conversion
Enhanced Stored Scripts Commands
Backupset Compression
Restore Preview
Managing Backup Duration and Throttling
Miscellaneous
- Disk Topology and Automatic Performance Tuning
- Automatic Datafile Creation
- Proxy Archived Log Backups
- Recovery Through Restlogs
- Restore Failover
- Channel Failover
- Deferred Error Reporting

Flash Recovery Area

The flash recovery area is a location on the filesystem or on an ASM disk group that holds files related to recovery.

Multiplexed controlfiles
Multiplexed online redo logs
Archived redo logs
Flashback logs
RMAN disk backups
Files created by RESTORE and RECOVERY commands.

Space within the flash recovery area is managed by the database. If there is not enough space to complete an operation obsolete, backed up or redundant files are removed to free up some space.

The following example shows the parameters used to configure the flash recovery area.

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 2G;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/app/oracle/flash_recovery_area';
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 1440;

The flashback technologies are covered in the Flashback New Features and Enhancements in Oracle Database 10g article.

Incrementally Updated Backups

Using this feature all changes between the SCN of the original image copy and the SCN of the incremental backup are applied to the image copy, winding it forward to make the equivalent of a new database image copy without the overhead of such a backup. The following example shows how this can be used.

RUN {
  RECOVER COPY OF DATABASE WITH TAG 'incr_backup' UNTIL TIME 'SYSDATE - 7';
  BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
}

The RECOVER COPY... line will not do anything until the script has been running for more than 7 days. The BACKUP INCREMENTAL line will perform a complete backup (level 0) the first day it is run, with all subsequent backups being level 1 incremental backups. After 7 days, the RECOVER COPY... line will start to take effect, merging all incremental backups older than 7 days into the level 0 backup, effectively moving the level 0 backup forward. The effect of this is that you will permanently have a 7 day recovery window with a 7 day old level 0 backup and 6 level 1 incremental backups. Notice that the tag must be used to identify which incremental backups apply to which image copies.

If you wanted to keep your image copy as up to date as possible you might do the following.

RUN {
  BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
  RECOVER COPY OF DATABASE WITH TAG 'incr_backup';
}

In this example the incremental backup is merged into the image copy as soon as it is completed.

Fast Incremental Backups

There are performance issues associated with incremental backups as the whole of each datafile must be scanned to identify changed blocks. In Oracle 10g it is possible to track changed blocks using a change tracking file. Enabling change tracking does produce a small overhead, but it greatly improves the performance of incremental backups. The current change tracking status can be displayed using the following query.

SELECT status FROM v$block_change_tracking;

Change tracking is enabled using the ALTER DATABASE command.

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

By default the change tracking file is created as an Oracle Managed File (OMF) in the location pointed to by the DB_CREATE_FILE_DEST parameter. An alternate location can be specified using the following command.

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
  USING FILE '/u01/oradata/MYSID/rman_change_track.f' REUSE;

The tracking file is created with a minumum size of 10M and grows in 10M increments. It’s size is typically 1/30,000 the size of the datablocks to be tracked.

Change tracking can be disabled using the following command.

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Renaming or moving a tracking file can be accomplished in the normal way using the ALTER DATABASE RENAME FILE command. If the instance cannot be restarted you can simply disable and re-enable change tracking to create a new file. This method does result in the loss of any current change information.

BACKUP for Backupsets and Image Copies

In Oracle 10g the BACKUP command has been extended to allow it to initiate backups of image copies in addition to backupsets. As a result the COPY command has been deprecated in favour of this new syntax.

BACKUP AS COPY DATABASE;
BACKUP AS COPY TABLESPACE users;
BACKUP AS COPY DATAFILE 1;

RMAN supports the creation of image copies of datafiles and datafile copies, control files and controlfile copies, archived redo logs, and backup pieces.

Cataloging Backup Pieces

It is now possible to manually catalog a backup piece using the CATALOG commands in RMAN. This allows backup files to be moved to alternate locations or manually archived to tape and brought back for restore operations. In Oracle 9i this functionality was only availabale for controlfile copies, archivelog copies and datafile copies. In addition, there are some shortcuts to allow multiple files to be cataloged using a single command. The following examples give the general idea.

# Catalog specific backup piece.
CATALOG BACKUPPIECE '/backup/MYSID/01dmsbj4_1_1.bcp';

# Catalog all files and the contents of directories which
# begin with the pattern "/backup/MYSID/arch".
CATALOG START WITH '/backup/MYSID/arch';

# Catalog all files in the current recovery area.
CATALOG RECOVERY AREA NOPROMPT;

# Catalog all files in the current recovery area.
# This is an exact synonym of the previous command.
CATALOG DB_RECOVERY_FILE_DEST NOPROMPT;

The NOPROMPT clause supresses user confirmation for all matching files.

Improved RMAN Reporting Through V$ Views

Oracle 10g includes additional V$ views making the reporting of backup operations more transparent.

V$RMAN_OUTPUT – This is an in-memory view of the messages reported by RMAN holding a maximum of 32767 rows. Since this information is not recorded in the controlfile it is lost on instance restart.
V$RMAN_STATUS – This view displays progress and status information for in-progress and complete RMAN jobs. The information for the in-progress jobs is memory only, while the complete job information comes from the controlfile.
V$BACKUP_FILES – This view display information about RMAN image copies, backupsets and archived logs, similar to the information listed by the RMAN commands LIST BACKUP and LIST COPY. This view relies on the DBMS_RCVMAN.SETDATABASE procedure being run to set the database.

The V$RMAN_CONFIGURATION view from Oracle 9i is still available in Oracle 10g.

Automatic Instance Creation for RMAN TSPITR

If a tablespace point-in-time recovery (TSPITR) is initiated with no reference to an auxillary instance RMAN now automatically creates an one. The auxillary instance configuration is based on that of the target database. As a result, any channels required for the restore operations must be present in the target database so they are configured correctly in the auxillary instance. The location of the datafiles for the auxillary instance are specified using the AUXILIARY DESTINATION clause shown below.

RECOVER TABLESPACE users
  UNTIL LOGSEQ 2400 THREAD 1
  AUXILIARY DESTINATION '/u01/oradata/auxdest';

The tablespace is taken offline, restored from a backup, recovered to the specified point-in-time in the auxillary instance and re-imported into the target database. The tablespace in the target database should then be backed up and the tablespace brought back online.

BACKUP TABLESPACE users;
SQL "ALTER TABLESPACE users ONLINE";

On successful completion the auxillary instance will be cleaned up automatically. In the event of errors the auxillary instance is left intact to aid troubleshooting.

Cross-Platform Tablespace Conversion

The CONVERT TABLESPACE allows tablespaces to be transported between platforms with different byte orders. The mechanism for transporting a tablespaces is unchanged, this command merely converts the tablespace to allow the transport to work.

The platform of the source and destination platforms can be identified using the V$TRANSPORTABLE_PLATFORM view. The platform of the local server is not listed as no conversion in necessary for a matching platform.

SQL&gt; SELECT platform_name FROM v$transportable_platform;

PLATFORM_NAME
------------------------------------
Solaris[tm] OE (32-bit)
...
...
Microsoft Windows 64-bit for AMD

15 rows selected.

The tablespace conversion can take place on either the source or the destination server. The following examples show how the command is used in each case.

# Conversion on a Solaris source host to a Linux destincation file.
CONVERT TABLESPACE my_tablespace
  TO PLATFORM 'Linux IA (32-bit)'
  FORMAT='/tmp/transport_linux/%U';

# Conversion on a Linux destination host from a Solaris source file.
CONVERT DATAFILE=
    '/tmp/transport_solaris/my_ts_file01.dbf',
    '/tmp/transport_solaris/my_ts_file02.dbf'
  FROM PLATFORM 'Solaris[tm] OE (32-bit)'
  DB_FILE_NAME_CONVERT
    '/tmp/transport_solaris','/u01/oradata/MYDB';

In the first example the converted files are placed in the directory specified by the FORMAT clause. In the second example the specified datafiles are converted to the local servers platform and placed in the correct directory specified by the DB_FILE_NAME_CONVERT clause.

Enhanced Stored Scripts Commands

Scripts can now be defined as global allowing them to be accessed by all databases within the recovery catalog. The syntax for global script manipulation is the same as that for regular scripts with the addition of the GLOBAL clause prior the word SCRIPT. Examples of it’s usage are shown below.

CREATE GLOBAL SCRIPT full_backup
{
  BACKUP DATABASE PLUS ARCHIVELOG;
  DELETE FORCE NOPROMPT OBSOLETE;
}

CREATE GLOBAL SCRIPT full_backup FROM FILE 'full_backup.txt';

RUN { EXECUTE GLOBAL SCRIPT full_backup; }

PRINT GLOBAL SCRIPT full_backup;

LIST GLOBAL SCRIPT NAMES;
LIST ALL SCRIPT NAMES;     # Global and local scripts.

REPLACE GLOBAL SCRIPT full_backup
{
  BACKUP DATABASE PLUS ARCHIVELOG;
  DELETE FORCE NOPROMPT OBSOLETE;
}

REPLACE GLOBAL SCRIPT full_backup FROM FILE 'full_backup.txt';

DELETE GLOBAL SCRIPT 'full_backup';

Backupset Compression

The AS COMPRESSED BACKUPSET option of the BACKUP command allows RMAN to perform binary compression of backupsets. The resulting backupsets do not need to be uncompressed during recovery. It is most useful in the following circumstances:

You are performing disk-based backup with limited disk space.
You are performing backups across a network where network bandwidth is limiting.
You are performing backups to tape, CD or DVD where hardware compression is not available.

The following examples assume that some persistent parameters are configured in a similar manner to those listed below.

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backups/MYSID/%d_DB_%u_%s_%p';

The AS COMPRESSED BACKUPSET option can be used explicitly in the backup command.

# Whole database and archivelogs.
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

# Datafiles 1 and 5 only.
BACKUP AS COMPRESSED BACKUPSET DATAFILE 1,5;

Alternatively the option can be defined using the CONFIGURE command:

# Configure compression.
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;

# Whole database and archivelogs.
BACKUP DATABASE PLUS ARCHIVELOG;

Compression requires additional CPU cycles which may affect the performance of the database. For this reason it should not be used for tape backups where hardware compression is available.

Restore Preview

The PREVIEW option of the RESTORE command allows you to identify the backups required to complete a specific restore operation. The output generated by the command is in the same format as the LIST command. In addition the PREVIEW SUMMARY command can be used to produce a summary report with the same format as the LIST SUMMARY command. The following examples show how these commands are used.

# Preview
RESTORE DATABASE PREVIEW;
RESTORE TABLESPACE users PREVIEW;

# Preview Summary
RESTORE DATABASE PREVIEW SUMMARY;
RESTORE TABLESPACE users PREVIEW SUMMARY;

Managing Backup Duration and Throttling

The DURATION clause of the of the BACKUP command restricts the total time available for a backup to complete. At the end of the time window backup is interrupted with any incomplete backupsets discarded. All complete backupsets aer kept and used for future restore operations. The following examples show how it is used.

BACKUP DURATION 2:00 TABLESPACE users;
BACKUP DURATION 5:00 DATABASE PLUS ARCHIVELOGS;

Miscellaneous

Disk Topology and Automatic Performance Tuning – RMAN includes a new disk topology API allowing it to work with more platforms and file types. The information from this API allows RMAN to automatically tune some parameters related to multiplexing and disk buffers, decreasing the need to human intervention.
Automatic Datafile Creation – RMAN will automatically create missing datafiles in two circumstances. First, when the backup controlfile contains a reference to a datafile, but no backup of the datafile is present. Second, when a backup of the datafile is present, but there is no reference in the controlfile as it was not backed up after the datafile addition.
Proxy Archived Log Backups – During a proxy backup the media manager takes over full control of the backup process. RMAN is now able to backup and restore proxy copies of archived redo logs if a suitable media manager is used. If a suitable media manager is not available PROXY clause is ignored and a regular backup is performed. Using the PROXY ONLY results in an error of a proxy backup cannot be performed.
Restore Failover – RMAN now allows the recovery process to preceed from one incarnation through to another. The contents of the online redo logs are archived before being cleared when an OPEN RESTLOGS operation is issued. As a result it is no longer necessary to create a new backup after OPEN RESTLOGS operations.
Recovery Through Restlogs – When a backup file contains corrupt blocks or is inaccesible during restore operations (RECOVER, BLOCKRECOVER, and FLASHBACK DATABASE) RMAN automatically looks for another copy of the file. If one is not available RMAN will use older versions of the file if available. Only if a suitable copy of the file cannot be found will an error be produced. Successful failover operations result in an associated output message.
Channel Failover – When multiple channels are available for the same device type retriable errors in a backup step will automatically be retried on another channel. Retriable errors are usually associated with media managers failing to access tapes or instance failures in RAC environments.
Deferred Error Reporting – In addition to error messages during the job execution, the error stack at the end of the command execution now displays errors for all failed steps, making identification of failed step easier.

Go to Detail Page