backup 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

Duplicate a Database Using RMAN in Oracle Database 11g Release 2

November 22, 2011 Articles 0 0

Duplicate a Database Using RMAN in Oracle Database 11g Release 2

Introduction
Backup-Based Duplication
Active Database Duplication

Introduction

RMAN has the ability to duplicate, or clone, a database from a backup or from an active database. It is possible to create a duplicate database on a remote server with the same file structure, a remote server will a different file structure or the local server with a different file structure.

The article assumes the duplicate database is being created on a separate server, using the same SID (DB11G) and the same file structure as the source database. Explanations of several other scenarios are available here.

Backup-Based Duplication

Create a backup of the source database, if a suitable one doesn’t already exist.

$ rman target=/

RMAN&gt; CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN&gt; BACKUP DATABASE PLUS ARCHIVELOG

All subsequent actions occur on the server running the duplicate database.

Create a password file for the duplicate instance.

$ orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwDB11G password=password entries=10

Add the appropriate entries into the “tnsnames.ora” file in the “$ORACLE_HOME/network/admin” directory to allow connections to the target database from the duplicate server.

# Added to the tnsnames.ora
DB11G-SOURCE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dup1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (ORACLE_SID = DB11G)
    )
  )

Create a PFILE for the duplicate database. Since we are duplicating the database onto a separate server with the same filesystem as the original, we don’t need to convert the file names. In this case, the PFILE is called “initDB11G.ora” and is placed in the “$ORACLE_HOME/dbs” directory.

# Minimum Requirement.
DB_NAME=DB11G

# Convert file names to allow for different directory structure if necessary.
#DB_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/DB11G/,/u01/app/oracle/oradata/NEWSID/)
#LOG_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/DB11G/,/u02/app/oracle/oradata/NEWSID/)

Create any directories necessary for start the duplicate database.

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

Make the backup files from the source database available to the destination server. That can be done by either copying them to the matching location on the destination server, or placing them on a shared drive. If you are copying the files, you may want to use the following type of commands.

$ scp -r oracle@ol5-112-dup1:/u01/app/oracle/fast_recovery_area/DB11G/archivelog /u01/app/oracle/fast_recovery_area/DB11G
$ scp -r oracle@ol5-112-dup1:/u01/app/oracle/fast_recovery_area/DB11G/backupset /u01/app/oracle/fast_recovery_area/DB11G
$ scp -r oracle@ol5-112-dup1:/u01/app/oracle/fast_recovery_area/DB11G/autobackup /u01/app/oracle/fast_recovery_area/DB11G

Connect to the duplicate instance.

$ ORACLE_SID=DB11G; export ORACLE_SID
$ sqlplus / as sysdba

Start the database in NOMOUNT mode.

SQL&gt; STARTUP NOMOUNT;

With the duplicate database started we can now connect to it from RMAN. For the duplication to work we must connect to the duplicate database (AUXILIARY), but depending on the type of duplication we are doing we may optionally connect to the original database (TARGET) and/or the recovery catalog (CATALOG).

$ ORACLE_SID=DB11G; export ORACLE_SID

$ rman AUXILIARY /
$ rman TARGET sys/password@DB11G-SOURCE AUXILIARY /
$ rman CATALOG rman/password@rman-catalog AUXILIARY /
$ rman TARGET sys/password@DB11G-SOURCE CATALOG rman/password@rman-catalog AUXILIARY /

We can then duplicate the database using one of the following commands.

# Backup files are in matching location to that on the source server.
# Duplicate database to TARGET's current state.
DUPLICATE TARGET DATABASE TO DB11G
  SPFILE
  NOFILENAMECHECK;

# Duplicate database to TARGET's state 4 days ago.
DUPLICATE TARGET DATABASE TO DB11G
  UNTIL TIME 'SYSDATE-4'
  SPFILE
  NOFILENAMECHECK;

# Backup files are in a different location to that on the source server.
# Duplicate database to the most recent state possible using the provided backups.
# Works with just an AUXILIARY connection only.
DUPLICATE DATABASE TO DB11G
  SPFILE
  BACKUP LOCATION '/source/app/oracle/fast_recovery_area/DB11G'
  NOFILENAMECHECK;

The time it takes to complete varies depending on the size of the database and the specification of the server. Once the process is finished RMAN produces a completion message and you have your duplicate instance.

Active Database Duplication

Oracle 11g introduced the ability to create duplicate databases directly without the need for a backup. This is known as active database duplication. The process is similar to the backup-based duplication, with a few exceptions.

First, and most obviously, you don’t need a backup of the source system, but it does have to be in ARCHIVELOG mode.

The passwords in the password files must match for both servers, so remember to set the correct password when creating the password file on the destination server.

Both the source and destination database servers require a “tnsnames.ora” entry for the destination database. In this case I added the following to each server. The destination server still requires the source entry shown in the previous section.

# Added to the tnsnames.ora on source and destination server.
DB11G-DESTINATION =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dup2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (ORACLE_SID = DB11G)
    )
  )

The destination server requires static listener configuration in a “listener.ora” file. In this case I used the following configuration. Remember to restart or reload the listener.

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-dup2.localdomain)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

When connecting to RMAN, you must use a connect string for both the target and auxiliary connections.

$ ORACLE_SID=DB11G; export ORACLE_SID

$ rman TARGET sys/password@DB11G-SOURCE AUXILIARY sys/password@DB11G-DESTINATION

Include the FROM ACTIVE DATABASE clause in the DUPLICATE command.

DUPLICATE DATABASE TO DB11G
  FROM ACTIVE DATABASE
  SPFILE
  NOFILENAMECHECK;

For more information see:

Duplicating a Database

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

Graceful Failover and Failback Procedures in Non-Data Guard Environments

November 1, 2011 Articles 0 0

Introduction

This paper describes the procedures to perform “graceful” failovers and failbacks of hot standby databases in Oracle environments that are not using Data Guard. A “graceful” failover is one that does not require databases to be opened with the RESETLOGS option—and as a result, graceful failbacks do not require the primary database to be rebuilt. (That is, they do not require the standby database’s datafiles to be copied to the primary server.) Graceful failovers and failbacks are also known as “switchovers” and “switchbacks”.

In order to avoid confusion, the original primary database will be referred to as database “abc”, and the original standby database will be referred to as database “xyz”. In other words, before any of these procedures are run, the primary database is “abc” and the standby database is “xyz”.

The procedures in this document are generally based on the information contained in Metalink document 90817.1.

Graceful Failover Procedure

Shut down database abc and database xyz with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE.
Copy the unsent archived redo logs, all of the online redo logs, and all of the control files from abc’s host to xyz’s host. Make sure that you copy all of these files into their pre-existing locations on xyz’s host. For example, if a control file called “control01.ctl” exists in the /u01 directory on abc’s host; but it exists in the /u04 directory on xyz’s host, then copy that file from /u01 on abc’s host to /u04 on xyz’s host.
Start up database xyz with STARTUP MOUNT.
If necessary, rename the datafiles and online redo logs in database xyz to reflect those files’ locations on xyz’s host with ALTER DATABASE RENAME FILE commands. This will be necessary if any datafiles or online redo logs have different filenames or locations on xyz’s host than on abc’s host.
In database xyz, execute the command RECOVER DATABASE. If any archived redo logs are needed for recovery, then you will be prompted for the next log. In that case, type in AUTO at that prompt. When the instance finishes applying all of the changes contained in the archived redo logs and the online redo logs, the message “Media recovery complete” will be displayed.
Open database xyz by executing the ALTER DATABASE OPEN; command. The xyz database is now open in read-write mode; so at this point the xyz database is ready to be used as the new primary database.
In database xyz execute the following command, substituting an appropriate filename with full path:
```
        ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'filename';
```
Copy the standby controlfile that you just created to abc’s host. Then, on that host, overwrite all of the existing controlfiles for the abc database with this standby controlfile. In other words, copy the standby controlfile into each of the existing controlfile’s directories; and then rename the standby controlfiles to match the name of the existing controlfiles.

Start up the abc database with the following commands:

        STARTUP NOMOUNT
        ALTER DATABASE MOUNT STANDBY DATABASE;

If necessary, rename the datafiles and online redo logs in database abc to reflect those files’ locations on abc’s host with ALTER DATABASE RENAME FILE commands. This will be necessary if any datafiles or online redo logs have different filenames or locations on abc’s host than on xyz’s host. At this point, the abc database is configured as the new standby database, and is ready to apply archived redo logs from the xyz database.

Graceful Failback Procedure

(These steps are basically identical to the failover procedure above—the only difference is that you are reversing the roles of the abc and xyz databases.)

Shut down database abc and database xyz with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE.
Copy the unsent archived redo logs, all of the online redo logs, and all of the control files from xyz’s host to abc’s host. Make sure that you copy all of these files into their pre-existing locations on abc’s host. For example, if a control file called “control01.ctl” exists in the /u01 directory on abc’s host; but it exists in the /u04 directory on xyz’s host, then copy that file from /u04 on xyz’s host to /u01 on abc’s host
Start up database abc with STARTUP MOUNT.
If necessary, rename the datafiles and online redo logs in database abc to reflect those files’ locations on abc’s host with ALTER DATABASE RENAME FILE commands. This will be necessary if any datafiles or online redo logs have different filenames or locations on abc’s host than on xyz’s host.
In database abc, execute the command RECOVER DATABASE. If any archived redo logs are needed for recovery, then you will be prompted for the next log. In that case, type in AUTO at that prompt. When the instance finishes applying all of the changes contained in the archived redo logs and the online redo logs, the message “Media recovery complete” will be displayed.
Open database abc by executing the ALTER DATABASE OPEN; command. The abc database is now open in read-write mode; so at this point the abc database is ready to be used as the new primary database.
In database abc execute the following command, substituting an appropriate filename with full path:
```
        ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'filename';
```
Copy the standby controlfile that you just created to xyz’s host. Then, on that host, overwrite all of the existing controlfiles for the xyz database with this standby controlfile. In other words, copy the standby controlfile into each of the existing controlfile’s directories; and then rename the standby controlfiles to match the name of the existing controlfiles.

Start up the xyz database with the following commands:

        STARTUP NOMOUNT
        ALTER DATABASE MOUNT STANDBY DATABASE;

If necessary, rename the datafiles and online redo logs in database xyz to reflect those files’ locations on xyz’s host with ALTER DATABASE RENAME FILE commands. This will be necessary if any datafiles or online redo logs have different filenames or locations on xyz’s host than on abc’s host. At this point, the xyz database is configured as the new standby database, and is ready to apply archived redo logs from the abc database.

Requirements and Notes

This section describes the requirements that must be met in order to use these procedures; along with some notes.

First, the primary database’s host must be accessible. In order to use these procedures, the control files and online redo logs from the primary database need to be copied from the primary database’s host to the standby database’s host. Therefore, these procedures can not be used if the primary database’s host is inaccessible—because that would prevent those files from being copied to the standby server. (It may be possible to configure a geographic disk mirroring utility between the primary and standby hosts in order to be able to eliminate this requirement; but I have not seen any successful implementation of geographic disk mirroring in Oracle environments.)

Next, the primary and standby databases must be shut down with normal or immediate priority. If the databases (particularly the primary database) are shut down with SHUTDOWN ABORT, then it might not be possible to fully recover the standby database—and that would prevent these procedures from being usable.

These procedures include converting the original primary database into a standby database during graceful failover (after the original standby database has been converted into a primary database). It should be pointed out that it is not required to convert the primary into a standby—it is still possible to fail back to the original primary, even if that database was not converted into a standby. However, if you do not convert the original primary database into a standby, then you must ensure that that database never gets opened until you are performing the failback procedure. This is because the act of opening a primary database generates some redo in that database—and that redo will cause the primary database’s datafiles to become permanently out of sync with the redo in the standby database. So, if you open up the original primary database without converting it to a standby, you will not be able to use these procedures; you will have to completely rebuild the primary database from a copy of the standby database’s datafiles.

Thirdly, these procedures specify to copy the control files from the primary database to the standby database. An alternative to doing this is to rebuild the standby’s control files from the output of an ALTER DATABASE BACKUP CONTROLFILE TO TRACE command. Note, however, that if you wish to rebuild the standby’s control files, then you must ensure that the CREATE CONTROLFILE statement contains the NORESETLOGS option. If you change that option to specify RESETLOGS, then you will not be able to open the database with a regular ALTER DATABASE OPEN. Instead, you will be forced to specify RESETLOGS when opening the database, and, of course, the whole purpose of these procedures is to avoid opening the database with RESETLOGS.

Finally, there is an “idiosyncrasy” with these procedures when they are used in conjunction with locally-managed temporary tablespaces. To be specific, if these procedures are used with a database that contains a locally-managed temporary tablespace, and if the tempfiles of that tablespace were not added to the hot standby database before the standby was converted into the new primary database, then that locally managed temporary tablespace will have to be completely dropped and re-created in the new primary database in order to use the tempfiles in question.

The reason for this is that these procedures specify to copy the control files from the primary database to the standby database. Therefore, if you try to add the tempfiles to the standby database’s temporary tablespace after converting it to the new primary, you will receive a “file is already part of database” error message – because the control files (which were copied from the original primary) will already contain the information about those tempfiles. Also, if the new primary instance tries to use any of the tempfiles (such as for a sorting operation) then a “cannot identify/lock data file xxx” error message will be displayed – because the tempfile in question does not actually exist on the new primary’s host. As mentioned above, one solution to this issue is to simply drop and re-create the locally managed temporary tablespace completely, after converting the standby database into a primary database. An alternate solution is to manually ensure that all tempfiles have been added to the standby before converting the standby into the new primary.

Go to Detail Page

Moving Oracle Databases Across Platforms without Export/Import

October 31, 2011 Articles 0 0

Introduction

Prior to Oracle 10g, one of the only supported ways to move an Oracle database across platforms was to export the data from the existing database and import it into a new database on the new server. This works pretty well if your database is small, but can require an unreasonable amount of down time if your database is large. In Oracle 10g, the transportable tablespace feature has been enhanced in a way that makes it possible to move large databases (or portions of them) across platforms much more quickly and simply than the export/import method. We will begin this paper with a high-level look at Oracle 10g’s cross-platform support for transportable tablespaces and possible uses for this feature. Then we’ll walk through a real-life case study of moving an Oracle database from a Sun Enterprise 450 server running the Solaris operating system on SPARC processors to a Dell server running Red Hat Enterprise Linux on Intel processors. We’ll look at each of the steps in detail and show all of the commands used and resulting output from Oracle utilities. We will wrap up with a discussion of pitfalls, limitations, and things to keep in mind when preparing to move Oracle data across platforms.

Cross-platform Transportable Tablespaces: An Overview

The transportable tablespace feature, introduced in Oracle 8i, allows you to copy one or more tablespaces from one database to another. The export and import utilities are used to copy the metadata for the objects residing in the transported tablespaces, and ordinary file copy commands like FTP or SCP are used to copy the actual data files. This feature made sharing information between databases, or flowing data from one database to another, fast and efficient. Transporting tablespaces is fast because Oracle only needs to write the metadata—or schema object definitions—into the export file. This saves the time-consuming and resource intensive steps of exporting data into a dump file at the source database, loading the data from the dump file into the target database, and rebuilding indexes in the target database from scratch.

In Oracle 8i and Oracle 9i, tablespaces could only be transported into databases that ran on the same hardware platform and operating system. So if your OLTP system ran on HP-UX and your data warehouse on Linux, you could not use transportable tablespaces to copy data efficiently between the databases. And if you had a database running on a Windows server and you wanted to permanently move it to a Sun server running Solaris, you couldn’t use transportable tablespaces either.

Beginning in Oracle 10g release 1, cross-platform support for transportable tablespaces is available for several of the most commonly used platforms. The process is similar to transporting tablespaces in previous Oracle releases, except there are a few possible extra steps, and there are more limitations and restrictions. Oracle 10g release 2 goes one step further and offers the ability to transport an entire database across platforms in one step. But the limitations here are even stricter.

At a high level, transporting tablespaces across platforms works like this: First you make sure your tablespaces qualify for using the cross-platform transportable tablespaces feature. Next you determine if file conversion will be required. (This depends on what platform you are moving from and to.) Next you make the tablespaces read-only on the source database and you export the metadata. If file conversion is required, next you invoke RMAN to perform the file conversion. Next you copy the data files to the target database server and import the metadata. If you want to make the tablespaces read-write on the target database, you can do so.

This paper focuses on using the cross-platform transportable tablespace feature for moving a database permanently from one platform to another as quickly and efficiently as possible. But this feature could also be used on a regular basis for information sharing between databases running on different platforms. In some cases it may even be possible for multiple databases on different platforms to share the same physical data files in read-only mode. Because you can transport tablespaces into a database running a higher Oracle version, it is theoretically possible that you could upgrade an Oracle 10g release 1 database to Oracle 10g release 2 and move it to a new platform all in one step.

Many companies today run Oracle databases on multiple platforms and would like to share information easily and efficiently between systems. Other companies are looking to move their Oracle databases to newer hardware platforms or different operating systems than they are presently using. The introduction of cross-platform support for the transportable tablespace feature can be a great help in these situations.

A Case Study

With the increased acceptance of Linux by the business community, many companies are moving IT systems to commodity hardware running Linux. We are seeing many organizations that want to move Oracle databases from expensive servers running AIX, HP-UX, or Solaris operating systems to less expensive hardware running Linux. In this section of the paper we will discuss in great detail one such project, walking through each of the steps of the process thoroughly.

The company had an Oracle 10g release 2 database running on a Sun Enterprise 450 server running the Solaris operating system which they wanted moved to a Dell server running Red Hat Enterprise Linux. The new feature in Oracle 10g release 2 that allows transporting an entire database across platforms in one step cannot be used to transport between the Sun SPARC processor architecture and the Intel processor architecture, so we used the cross-platform transportable tablespace feature instead. The database, at about 8 Gb, was quite small. We could probably have used the export/import method to move all of the data in a reasonable amount of time, but the goal was to move the database with as little down time as possible.

The Sun and Dell servers in this case study both ran Oracle 10g release 2 with the 10.2.0.2.0 patch set. The procedure should be generally the same for other releases of Oracle 10g, but there may be minor changes as Oracle fixes bugs, improves usability, and enhances functionality. Although the 10.2.0.2.0 patch set was the latest available when this project took place, it is likely that a newer patch set has been released by the time you read this paper. Please bear this in mind and use this paper as a starting point only, and not a substitute for proper planning and preparation.

A note about the export and import utilities: Data pump has been introduced in Oracle 10g as a new and improved method for exporting and importing data in Oracle databases. The export and import utilities we used in Oracle 9i and earlier are still around in Oracle 10g and are now known as “Original” export and import. Both tool sets are supported in Oracle 10g, and you can use either for transporting tablespaces across platforms. One of the advantages of original export and import is stability—these tools have been around a long time and do what they do very well. Data pump, being new, has more bugs and is apt to be less stable.

Whether you should use original export and import or data pump will depend on certain aspects of your database environment and in general how comfortable you are with newer Oracle products. My team favors stability and predictability over new and whiz-bang, so we used original export and import for this project. Here are some data points for you to consider in choosing which tool set you should use:

Metalink documents 271984.1 and 294992.1 present two examples of documented bugs in data pump where a suggested workaround is to use original export and import instead. There probably are many others. But to be fair, the two documents listed here cite bugs in Oracle 10g release 1 data pump that are claimed to be fixed in Oracle 10g release 2.
Metalink document 371556.1 points out that data pump cannot transport XMLTypes while original export and import can.
Data pump offers many benefits over original export and import in the areas of performance and job management, but these benefits have little impact when transporting tablespaces because metadata export and import is usually very fast to begin with.
Original export and import are not obvious winners, however. Original export and import cannot transport BINARY_FLOAT and BINARY_DOUBLE data types, while data pump can.
When original export and import transport a tablespace that contains materialized views, the materialized views will be converted into regular tables on the target database. Data pump, on the other hand, keeps them as materialized views.

Step 1: Check Platform Support and File Conversion Requirement

First we need to verify that Oracle supports cross-platform tablespace transport between the two platforms we have in mind. The v$database view shows the name of the platform (as Oracle sees it) and the v$transportable_platform view shows a list of all supported platforms. If we join these two together in a query, we get the information we need.

On the source database:

SQL&gt; SELECT A.platform_id, A.platform_name, B.endian_format
  2  FROM   v$database A, v$transportable_platform B
  3  WHERE  B.platform_id (+) = A.platform_id;

PLATFORM_ID PLATFORM_NAME                       ENDIAN_FORMAT
----------- ----------------------------------- --------------
          2 Solaris[tm] OE (64-bit)             Big

SQL&gt;

On the target database:

SQL&gt; SELECT A.platform_id, A.platform_name, B.endian_format
  2  FROM   v$database A, v$transportable_platform B
  3  WHERE  B.platform_id (+) = A.platform_id;

PLATFORM_ID PLATFORM_NAME                       ENDIAN_FORMAT
----------- ----------------------------------- --------------
         10 Linux IA (32-bit)                   Little

SQL&gt;

The endian_format column in this query’s output will show either “Big,” “Little,” or will be blank. A blank indicates that the platform is