Oracle Apps DBA stuff

Friday, November 26, 2010

Enable Database Enterprise Manager Console on R12

Enable Database Enterprise Manager Console on R12
First login as applmgr user, shutdown all application tier process using adstpall.sh.
Login as oracle user to linux. Source the database environment.
Ensure REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE is set in the init.ora for the database using this command: SQL> show parameter REMOTE_LOGIN_PASSWORDFILE;
Create a password file using this command on linux: orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=change_on_install entries=5
Then execute this command: emca -config dbcontrol db -repos recreate -SID ITB -PORT 1521 -DBCONTROL_HTTP_PORT 5500
(remember to change SID and port if needed).
You will be asked some question:
SYS password: the default is change_on_install
DBSNMP password: dbsnmp
SYSMAN password: sysman
Email: enter your email address (optional)
SMTP server: enter your smtp server
SYSMAN schema will be dropped and recreate.
While dropping the scema, database will be quisced. Database sessions that blocking the quiescing process must be killed.
Use sqlplus to identify the sessions:
SQL> select bl.sid, serial#, user, osuser, type, program from v$blocking_quiesce bl, v$session se where bl.sid = se.sid;
Make note on SID and SERIAL#.
Kill the session using this sql command:
SQL> alter system kill session 'SID,SERIAL#' immediate;
(don't forget to change SID and SERIAL#).
On create schema process you may need to increase the size of SYSAUX tablespace.
After the process completed you can access the enterprise manager on internet browser using this URL:
http://hostname:5500/em
You may also have to run $ORACLE_HOME/root.sh as root to avoid ERROR: NMO not setuid-root. Just answer no if overwrite is asked.

EMCTL ISSUES

An Stack of Problems while creating Repository using emca
In my database I tried to create repository and stopped several times while creating repository. I spend a significant amount of time to create repository and after analysis I got the following.

A)Manually Resolve
1)If you fail after invoking command
emca -config dbcontrol db -repos create see alert log file for more details. Also refer to another log file if it shows. Directly go to step 3)

2)However I see many errors like,
CONFIG: ORA-00955: name is already used by an existing object
CONFIG: ORA-01921: role name 'MGMT_USER' conflicts with another user or role name
and many severals errors.

3)Execute the following commands in SQL*plus and then execute emca command. I wish no more error will come. After several experiment I get this.

drop user sysman cascade;
drop public synonym SETEMVIEWUSERCONTEXT;
drop role MGMT_USER;
drop PUBLIC SYNONYM MGMT_TARGET_BLACKOUTS;
drop user MGMT_VIEW;

And then,

$ emca -deconfig dbcontrol db
$ emca -config dbcontrol db -repos create

B)Using RepManager:
If I drop the Repository using RepManager like following error will not come while creating repository.
$ORACLE_HOME/sysman/admin/emdrep/bin/RepManager -action drop

This will not remove the configuration files, but only the repository related objects from the database.

But you need to remember when RepManager is ran the database will be put in Quiescing mode.

Thursday, November 25, 2010

Master Note For Oracle Recovery Manager (RMAN)

Master Note For Oracle Recovery Manager (RMAN) [ID 1116484.1]
Modified 14-OCT-2010 Type ANNOUNCEMENT Status PUBLISHED

In this Document
What is being announced?
What do you need to do?
Introduction
Getting started with RMAN
Rman compatibility
Flash Recovery Area
Configuring the Environment for RMAN Backups
Recovery Catalog for Rman backups.
Backing up Database files using RMAN and Maintainig the backup records.
Restore and recovery techiques/scenarios using RMAN.
Tablespace point in time recovery
Rman Transportable tablespaces and database.
Rman Duplicate Database
Rman Performance.
RMAN and Media managers
Rman and Dataguard
Rman and Rac
Rman and Corruption
Related Master Notes
Using My Oracle Support Effectively
Generic Links

Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.2.0.1.0 - Release: 10.1 to 11.2
Information in this document applies to any platform.
What is being announced?
This Master Note is intended to provide an index and references to the most frequently used My Oracle Support Notes with respect to Oracle Recovery Manager (RMAN).This Master Note is subdivided into categories to allow for easy access and reference to notes that are applicable to your area of interest, within RMAN.This includes the following categories:

* Getting started with RMAN
* Rman compatibility
* Flash Recovery Area
* Configuring the environment for RMAN backups
* Recovery catalog for RMAN backups
* Backing up database files using RMAN and maintaining the backup records
* Restore and recovery techniques/scenarios using RMAN
* Tablespace point in time recovery (TSPITR)
* RMAN Transportable tablespaces and database
* RMAN Duplicate database
* RMAN Performance.
* RMAN and Media managers.
* RMAN and Dataguard
* RMAN and Rac
* RMAN and Corruption
* Related Master Notes
* Using My Oracle Support Effectively
* Generic Links

What do you need to do?
Introduction
Recovery Manager is Oracle’s utility to manage the backup, and more importantly the recovery, of the database. It eliminates operational complexity while providing superior performance and availability of the database. Recovery Manager debuted with Oracle8 to provide DBAs an integrated backup and recovery solution.

Recovery Manager determines the most efficient method of executing the requested backup, restore, or recovery operation and then executes these operations in concert with the Oracle database server. Recovery Manager and the server automatically identify modifications to the structure of the database and dynamically adjust the required operation to adapt to the changes.

This note applies to the following versions of these products:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.x
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.1.0.x
Oracle Server - Enterprise Edition - Version: 11.2.0.1 to 11.2.0.x

Getting started with RMAN
The articles in this section give you a overview of the oracle utility RMAN,the new features of RMAN in Oracle 11 release 1,2 and FAQ on RMAN.

Note.360416.1 Oracle10g / 11g - Getting Started with Recovery Manager (RMAN)
Note 809867.1 RMAN 11gR1 : New Features
Note 1115423.1 Rman Enhancements In Oracle 11g.
Note.469777.1 RMAN - Frequently Asked Question (FAQ)

Rman compatibility
Note.73431.1 RMAN Compatibility Matrix

Flash Recovery Area
The flash recovery area is an Oracle-managed directory, file system, or Automatic Storage Management disk group that provides a centralized disk location for backup and recovery files. All the files you need to completely recover a database from a media failure are part of the Flash Recovery Area. Oracle creates archived logs and flashback logs in the flash recovery area. RMAN can store its backup sets and image copies in the flash recovery area, and it uses it when restoring files during media recovery. The flash recovery area also acts as a disk cache for tape.

The articles mentioned in the following section help you in understanding the concepts and working of flash recovery area,Rman integration with the flash recovery area,troubleshooting flash recovery area issues

Note.305648.1 What is a Flash Recovery Area and how to configure it ?
Note.305796.1 RMAN and Flash Recovery Area
Note.833663.1 Flash Recovery Area - FAQ
Note.560133.1 Benefits Of Using Flash Recovery Area
Note.315098.1 How is the space pressure managed in the Flash Recovery Area - An Example.
Note.305812.1 Flash Recovery area - Space management Warning & Alerts
Note.829755.1 Space issue in Flash Recovery Area( FRA )
Note.305810.1 Configuring file creation in Flash recovery area and order of Precedence
Note.305651.1 How to change Flash Recovery Area to a new location ?
Note.762934.1 Flash Recovery Area Sizing

Configuring the Environment for RMAN Backups
To simplify ongoing use of RMAN, you can set a number of persistent configuration settings for each target database. These settings control many aspects of RMAN behavior. For example, you can configure the backup retention policy, default destinations for backups, default backup device type, and so on. You can use the SHOW and CONFIGURE commands to view and change RMAN configurations.

The articles mentioned in the following section help you in configuring the rman persistent configurations,understanding their behaviour and troubleshooting them.

Note 305565.1 Persistent Controlfile Configurations for RMAN in 9i and 10g
Note.462978.1 Rman backup retention policy
Note.463875.1 Frequently asked questions on Rman backup retention policy
Note.351455.1 Oracle Suggested Strategy & Backup Retention

Recovery Catalog for Rman backups.
A recovery catalog is a set of tables and views that Recovery Manager (RMAN) uses to store metadata (information about a database structure, archived redo logs, backup sets, and data file copies) about a target database (database that RMAN backups or restores). RMAN uses this metadata to conduct its backup, recovery, and maintenance operations. Recovery catalog is optional, i.e., if a recovery catalog is not created, RMAN uses the target database’s control file to store the metadata about the target database. Although RMAN can conduct all major backup and recovery operations by using the control file, some RMAN commands work only if a recovery catalog exists.

The articles in the following section describe the configuration,managment of recovery catalog and some of the known issues related to recovery catalog.

Note.452529.1 Recovery catalog for RMAN backup
Note.467969.1 How To Configure RMAN Recovery Catalog Using Enterprise Manager DB Console.

Backing up Database files using RMAN and Maintainig the backup records.
The articles and links in the following section describe the different methods to perform backups and useful rman backup methods.

Oracle Database Backup and Recovery Advanced User's Guide

* Rman backup concepts - http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642/rcmcncpt.htm#i1006083
* Backing up database - http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642/rcmbckba.htm#i1005689
* Advanced backups - http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642/rcmbckad.htm#CEGHFJCF
* Backup maintenance - http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642/rcmmaint.htm#j1006083

NOTE:388422.1 Top 10 Backup and Recovery best practices
Note 262853.1 10G RMAN Fast Incremental Backups
Note.745798.1 Merged Incremental Backup Strategies
Note.735953.1 How To Configure RMAN Backups To Tape via Oracle Enterprise Managery
Note.137181.1 RMAN Backup Shell Script Example

Restore and recovery techiques/scenarios using RMAN.
The articles in this section provide various restore and recovery techniques and scenarios what can be achieved using rman.

Note.144911.1 RMAN 9i Block-Level Media Recovery - Concept & Example
NOTE:388422.1 Top 10 Backup and Recovery best practices
Note.94114.1 Backup and Recovery Scenarios
Note.372996.1 Using RMAN to Restore and Recover a Database When the Repository and Spfile/Init.ora Files Are Also Lost
Note.162855.1 RMAN Consistent Backup, Restore and Recovery using RMAN
Note.223543.1 How to Recover From a DROP / TRUNCATE / DELETE TABLE with RMAN
Note.403883.1 How To Restore Controlfile From A Backupset Without A Catalog Or Autobackup
Note.580414.1 RMAN - How to restore the controlfile using RMAN
Note.419137.1 How To Restore Rman Backups On A Different Node When The Directory Structures Are Different
Note.415579.1 HowTo Restore RMAN Disk backups of RAC Database to Single Instance On Another Node
Note.472536.1 10g RMAN Benefits of Simplified Recovery Through Resetlogs.
Note.61994.1 Procedures/Restrictions for Point-In-Time Recovery without a Recovery Catalog
Note.246325.1 Automated File Creation During RMAN Recovery
Note.358171.1 Oracle10g RMAN will not restore in parallel from tape

Tablespace point in time recovery
Recovery Manager (RMAN) Automatic TSPITR enables quick recovery of one or more tablespaces in a database to an earlier time without affecting the rest of the tablespaces and objects in the database.Here are a few articles which explain the steps involved in performing a TSPITR:

Note.109979.1 RMAN Tablespace Point In Time Recovery (TSPITR) Procedure.
Note.62332.1 TSPITR - An Example Walkthrough

Rman Transportable tablespaces and database.
Rman can be used to create transportable tablespace sets and can be used to convert the datafiles for cross platform transportable tablespace and database. Here are a few articles which provide all the required information to understand the procedures:

Note.371556.1 How move tablespaces across platforms using Transportable Tablespaces with RMAN
Note.455593.1 Creating a transportable tablespace set from RMAN backupsets
Note.831223.1 Using Rman Incremental backups To Update Transportable Tablespaces.
Note.413586.1 How To Use RMAN CONVERT DATABASE on Source Host for Cross Platform Migration
Note.414878.1 Cross-Platform Migration on Destination Host Using Rman Convert Database
Note.733205.1 Migration of Oracle Database Instances Across OS Platforms

Rman Duplicate Database
Database duplication is the use of the DUPLICATE command to copy all or a subset of the data in a source database. The duplicate database (the copied database) functions entirely independently from the source database (the database being copied).In articles in this section help you in understanding the duplicate procedures and various techinques available for the rman duplicate operation.

Note.228257.1 RMAN 'Duplicate Database' Feature in Oracle9i / 10G and 11G
Note.452868.1 RMAN 'Duplicate Database' Feature in 11G
Note.1113713.1 Creation Of Rman Duplicate Without Target And Recovery Catalog Connection.
Note.388431.1 Creating a Duplicate Database on a New Host.
Note.382669.1 Duplicate database from non ASM to ASM (vise versa) to a different host
Note.388424.1 How To Create A Production (Full or Partial) Duplicate On The Same Host
Note.293717.1 How to duplicate a database to previous incarnation
Note.340848.1 Performing duplicate database with ASM/OMF/RMAN
Note 461479.1 RMAN Duplicate Database From RAC ASM To RAC ASM
Note.840647.1 Article on How to do Rman Duplicate on ASM/RAC/OMF/Single Instance
Note.360962.1 Manual Completion of a Failed RMAN Duplicate
Note.369644.1 Answers To FAQ For Restoring Or Duplicating Between Different Versions And Platforms

Rman Performance.
Note.360443.1 RMAN Backup Performance
Note 740911.1 RMAN Restore Performance
Note 247611.1 Known RMAN Performance Problems
NOTE.360443.1 Analysing RMAN Performance Issues
NOTE.579158.1 Advise On How To Improve Rman Performance
NOTE.296985.1 RMAN Performance Data Gathering
Note.463227.1 Oracle10g RMAN Recovery Catalog Known Performance Issues

RMAN and Media managers
Oracle designed an architecture that allows RMAN to manage the process of database backup and recovery, yet integrate with industry-leading tape storage management subsystems. The interface between Rman and media management vendor products is keyed on an Oracle design specification. This specification allows Oracle RMAN to use third party media management software to back-up to and restore from tape.

Many organizations rely on Oracle to provide solutions for very large or highly distributed mission critical systems. In addition to needing databases capable of handling large amounts of data and complex queries, these organizations also need robust backup and recovery technology. Recovery of data quickly and reliably is paramount should some aspect of the system fail. To address these needs, Oracle has created the Backup Solutions Program (BSP), a cooperative program designed to facilitate tighter integration between Oracle's backup products and those of third-party media management vendors. Together, Oracle and media management vendors provide robust easy-to-use database backup and recovery solutions to customers with high-end requirements.

The link to access the information regarding backup solutions program is:

http://www.oracle.com/technetwork/database/features/availability/bsp-088814.html

"Under the BSP, vendors are committed to integrating Recovery Manager (RMAN) with their media management software packages and provide first line technical support for the integrated backup and recovery solutions for Oracle RDBMS."

So any issues related to the media manager functionality or configuration has to be addressed only by the vendors and not by Oracle.

Below are a few articles which help you in understanding the most common issues with rman backups to tape, environment variable used for backups to tape and procedure to check if the media manager installation has been done properly.

Note.942418.1 How To Verify A Media Manager Layer Installation ?
Note 1062824.6 RMAN: Unable to Backup with Third-Party Storage Manager
Note 227517.1 Main Index of Common Causes for ORA-19511
NOTE.312737.1 RMAN and Specific Media Managers Environment Variables.

Rman and Dataguard
Data Guard and RMAN were both designed with the Oracle database architecture in mind. Together, they offer the most reliable and tightly integrated solution to achieve superior levels of Oracle database availability supporting your mission critical applications. Data Guard and RMAN are both fully supported features of the Oracle Database Enterprise Edition (RMAN is also provided with Oracle Database Standard Edition).Here are a few articles that help you in effectively using rman with dataguard and some of the known rman issues with dataguard.

Note.848716.1 Using RMAN Effectively In A Dataguard Environment
Note.357759.1 Known RMAN - Dataguard Problems
Note.368336.1 10gR1 RAC/DataGuard/ASM physical standby deployment procedure

Rman and Rac
Note 243760.1 RMAN: RAC Backup and Recovery using RMAN
Note.415579.1 HowTo Restore RMAN Disk backups of RAC Database to Single Instance On Another Node
Note 461479.1 RMAN Duplicate Database From RAC ASM To RAC ASM
Note.840647.1 Article on How to do Rman Duplicate on ASM/RAC/OMF/Single Instance

Rman and Corruption
Rman is a very effective utility in identifying the database block corruption. Here are are a few article which provides the information about using rman to detect and fix corruption.

Note.283053.1 How To Use RMAN To Check For Logical & Physical Database Corruption
Note 836658.1 Identify the corruption extension using RMAN/DBV/ANALYZE etc
Note.561010.1 Which Blocks Will RMAN Check For Corruption Or Include In A Backupset?
Note 428570.1 Best Practices for Avoiding and Detecting Corruption

Related Master Notes
Note 1199803.1 Master Note For Oracle Backup And Recovery
Note 1096952.1 Master Note for Real Application Clusters (RAC) Oracle Clusterware and Oracle Grid Infrastructure
Note 1088018.1 Master Note for Oracle Database Corruption
Note 1101938.1 Master Note for Data Guard

Using My Oracle Support Effectively
Note 374370.1 New Customers Start Here
Note 736737.1 My Oracle Support - The Next Generation Support Platform
Note 730283.1 Get the most out of My Oracle Support
Note 747242.5 My Oracle Support Configuration Management FAQ
Note 868955.1 My Oracle Support Health Checks Catalog
Note 166650.1 Working Effectively With Global Customer Support
Note 199389.1 Escalating Service Requests with Oracle Support Services

Generic Links
Note 854428.1 Patch Set Updates for Oracle Products
Note 1061295.1 Patch Set Updates - One-off Patch Conflict Resolution
Note 881382.1 Critical Patch Update October 2009 Patch Availability Document for Oracle Products
Note 967472.1 Critical Patch Update January 2010 Patch Availability Document for Oracle Products
Note 1060989.1 Critical Patch Update April 2010 Patch Availability Document for Oracle Products
Note 756671.1 Oracle Recommended Patches -- Oracle Database
Note 268895.1 Oracle Database Server Patchset Information, Versions: 8.1. 7 to 11.2.0
Note 161549.1 Oracle Database Server and Networking Patches for Microsoft Platforms

Show Related Information Related
Products

* Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

Keywords
RAC; TRANSPORTABLE; CONVERT DATABASE; DATABASE RECOVERY; DATA GUARD; DUPLICATE DATABASE; ENTERPRISE MANAGER; RECOVERY MANAGER
Errors
ORA-19511

Back to topBack to top
Rate this document
Article Rating
Rate this document
Excellent
Good
Poor
Did this document help you?
Yes
No
Just browsing
How easy was it to find this document?
Very easy
Somewhat easy
Not easy

Comments
Provide feedback for this article. Please use 'Contact Us' for other feedback.

Important Note: this feedback is anonymously visible to other customers until processed by Oracle Support.
Cancel

Master Note For Oracle Recovery Manager (RMAN)

Monday, November 22, 2010

sendmail.mc

Solution
To implement the solution, please execute the following steps:

1. Edit /etc/mail/sendmail.mc to have:

define(`SMART_HOST',`')

and

DAEMON_OPTIONS(`Port=smtp,Addr=127.0.0.1, Name=MTA')dnl

Do NOT edit /etc/mail/sendmail.cf as it may cause unexpected results.

(The "DAEMON_OPTIONS" line is a security measure - it allows sendmail to accept e-mail only from the local server. If you do not need otherwise, this is a good security practice. )

2. Regenerate sendmail.cf from sendmail.mc:

# m4 /etc/mail/sendmail.mc > /etc/mail/sendmail.cf

3. Restart the sendmail service:

# service sendmail restart

How to Recreate a Controlfile

How to Recreate a Controlfile

This article describes how you can recreate your controlfile.
Solution

Warning

You should only recreate your control file under the following circumstances:

* All current copies of the control file have been lost or are corrupted.
* You are restoring a backup in which the control file is corrupted or missing.
* You need to change a hard limit database parameter in the controlfile.
* If you are moving your database to another server and files are located in a different location.
* Oracle Customer Support advises you to.

Creating a new Controlfile from an existing database that is mounted or open.

First you must generate an ascii dump of the controlfile.

Whilst the database is mounted or open issue:
SQL> alter database backup controlfile to trace;

A trace file will be generated in the user_dump_destination directory.
SQL> show parameter user_dump_dest

NAME TYPE VALUE
-------------- ------ ------------------------------------------------
user_dump_dest string /oracle/product/11.1.0/db_1/diag/rdbms/V11/trace

After navigating to the directory locate the latest trace file by date/time by issuing: ls -ltr.
% cd /oracle/product/11.1.0/db_1/diag/rdbms/V11/trace
% ls -ltr

Once you locate the file it will appear as an ordinary trace file:
Trace file /oracle/product/11.1.0/db_1/diag/rdbms/V11/trace/V11_ora_31225.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/11.1.0/db_1

You are interested in the section that contains the create controlfile script.

Modify the trace file and use it as a script to create the control.

CREATE CONTROLFILE REUSE DATABASE "V11" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata/V11/redo01.log' SIZE 50M,
GROUP 2 '/oradata/V11/redo02.log' SIZE 50M,
GROUP 3 '/oradata/V11/redo03.log' SIZE 50M
DATAFILE
'/oradata/V11/system01.dbf',
'/oradata/V11/sysaux01.dbf',
'/oradata/V11/undotbs01.dbf'
'/oradata/V11/user01.dbf'
CHARACTER SET WE8MSWIN1252
;

It is important to delete everything above the "CREATE CONTROLFILE" and
everything after the CHARACTER SET. Ensure you leave the semi colon. ";".
In the above example we are choosing the NORESETLOGS option and running the
database in archivelog mode. After successfully saving the script you are now
able to recreate the controlfile. When shutting down the database ensure that
you shutdown with the immediate option.

SQL> shutdown immediate;

NOTE: Oracle recommeds that a full database is taken prior to a creation of the
controlfile.

SQL> startup nomount;

SQL>@control.sql

Once the controlfile is successfully created the database is automatically
mounted. If you have opened the database with a resetlogs it is important to
take a backup asap.

Creating a new controlfile from a database that is not able to mount.

Under the rare occasion that you do not have a controlfile to either:

1. Restore

2. or have a script from a "backup controlfile to trace script"

you must create a script from the beginning.

CREATE CONTROLFILE REUSE DATABASE "DBNAME" NORESETLOGS ARCHIVELOG

Follow the format listing:

- Location of redo logs.

- Location of datafiles

- Specifying the characterset.

Once you have listed all files correctly you are ready to recreate your controlfile
SQL> startup nomount;

SQL>@control.sql

RMAN : Consistent Backup, Restore and Recovery using RMAN

RMAN : Consistent Backup, Restore and Recovery using RMAN [ID 162855.1]
Modified 19-OCT-2010 Type HOWTO Status PUBLISHED

"Checked for relevance on 02-JUL-2010"

Oracle9i Lab 1

Consistent Backup, Restore and Recovery using RMAN

In Oracle9i, Recovery Manager still works the same as in 8 and 8i with
enhancements for ease of use and manageability. Using the updateable
persistent configuration options, backing up the database is easier then
ever. To begin lets look where information is stored in the database about
RMAN backups. For testing we'll use the target database controlfile without
a recovery catalog. By setting up the autocontrolfile backup feature we can
use the controlfile backups for a recovery catalog.

1. V$ tables - What does the controlfile know?

What are the views and synonyms related to backup and rman?
Let's take a look before backups are done to see what information
they hold for us.

SQL> set pagesize 60
SQL> column object_name format a30
SQL> select object_name from dba_objects
2 where object_name like '%BACKUP%'
3 and object_type = 'SYNONYM';

select object_name from dba_objects
where object_name like '%RMAN%'
and object_type = 'SYNONYM';

OBJECT_NAME
--------------------------------
V$BACKUP_CORRUPTION
V$BACKUP_DATAFILE
V$BACKUP_DEVICE
V$BACKUP_PIECE
V$BACKUP_REDOLOG
V$BACKUP_SET
V$BACKUP_SYNC_IO
V$RMAN_CONFIGURATION
V$BACKUP
V$BACKUP_ASYNC_IO

Other controlfile views to reference are:

v$controlfile_record_section, v$copy_corruption, v$database, v$datafile,
v$datafile_copy, v$offline_range, v$proxy_archivelog, v$proxy_datafile,
v$session_longops, v$database_block_corruption

2. RMAN Configuration

Check the default configuration in RMAN and then check the V$ view to see what
is stored. Then update the configuration in RMAN and take a look at the view
again. This is accomplished in RMAN using the "show all" command.

% sqlplus "/ as sysdba"

set pagesize 60
spool rman_new_conf.lst
column name format a30
column VALUE format a31
set echo on;
set serveroutput on;
select * from v$rman_configuration;
spool off;

CONF# NAME VALUE
---------- ------------------------------ -------------------------------
1 RETENTION POLICY TO REDUNDANCY 3
2 BACKUP OPTIMIZATION OFF
3 DEFAULT DEVICE TYPE TO DISK
4 CONTROLFILE AUTOBACKUP ON
5 CONTROLFILE AUTOBACKUP FORMAT DISK TO '/proj/SME9i/backup/%F'
FOR DEVICE TYPE
6 DEVICE TYPE DISK PARALLELISM 2
7 DATAFILE BACKUP COPIES FOR DEV DISK TO 1
ICE TYPE
8 ARCHIVELOG BACKUP COPIES FOR D DISK TO 1
EVICE TYPE
9 MAXSETSIZE TO UNLIMITED

9 rows selected.

Since we are not using a recovery catalog let's look at the controlfile since
this is our catalog without a recovery catalog. In sqlplus run:

select TYPE, RECORDS_TOTAL, RECORDS_USED
from v$controlfile_record_section
where type like '%BACKUP%';

TYPE RECORDS_TOTAL RECORDS_USED
------------------ ------------- ------------
BACKUP SET 101 0
BACKUP PIECE 204 0
BACKUP DATAFILE 210 0
BACKUP REDOLOG 53 0
BACKUP CORRUPTION 185 0

The records used column is still zero since we have not taken any backups.
The records are inserted into these views after successful creation of a
backupset. When RMAN reads the files to backup, if any corruption is
encountered then the corruption views are populated with file#, block# and
contiguous blocks after the initial corrupt block.

3. Create a persistent configuration for reuse

Create a persistent backup configuration. Once these parameters are configured
RMAN will continue to reuse the configured options for subsequent backups unless
you override the option within your script or to clear or disable it.

In 9i RMAN you use the "show" command to see the currently configured options.

SHOW show_operand [,show_operand ...];

show_operand: RETENTION POLICY |
EXCLUDE |
BACKUP COPIES |
CHANNEL |
DEFAULT DEVICE TYPE |
DEVICE TYPE |
SNAPSHOT CONTROLFILE |
...
ALL

RMAN> show all;

using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/beta/app/oracle/product/9.0.1/dbs/snapc
f_V901.f'; # default

You can script the configuration you want to use within a run block to change
more then one parameter or just change a single parameter at a time from the
RMAN prompt not using the run block.

run {
# Use the configure commands to create your backup policy.
# When complete these will be the new persistent
# configuration parameters for RMAN in the controlfile
# of the target datbase.
#
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'/proj/SME9i/backup/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/proj/SME9i/backup/snapf_prod9.f';
}

RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'/proj/SME9i/backup/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/proj/SME9i/backup/snapf_prod9.f';

4. Create a consistent backup script

Create an RMAN script to generate closed database backups without a recovery
catalog. We set the autocontrolfile backup feature during configuration so
the backup controlfile will become our catalog to avoid the overhead of
maintaining a second database for recovery.

% vi backup_lvl0_cold.rcv

## Consistent backup
#
connect target;
run {
shutdown immedate;
startup mount pfile=/proj/SME9i/prod9/pfile/initprod9.ora;
backup
incremental level 0
database format '/proj/SME9i/backup/%d_closed_%U'
tag=prod9_closed_LVL0;
shutdown;
startup pfile=/proj/SME9i/prod9/pfile/initprod9.ora;
}
exit

5. Create a backup using backup_lvl0_cold.rcv.

Make a backup of the target database using the new script. Rerun the loop
script in sqlplus to generate changes and run another backup of the database.
Do this until you have 4 backups of the database.

How many backup records were created for the backup we did?
Let's look at the controlfile and verify what's there.

select TYPE, RECORDS_TOTAL, RECORDS_USED
from v$controlfile_record_section
where type like '%BACKUP%';

TYPE RECORDS_TOTAL RECORDS_USED
------------------ ------------- ------------
BACKUP SET 101 3
BACKUP PIECE 204 3
BACKUP DATAFILE 210 9
BACKUP REDOLOG 53 0
BACKUP CORRUPTION 185 0

We have 2 backupsets from the parallelism 2 in the channel persistent
configuration set in step #3. The files to channel algorithm will split
the files across the allocated channels, since there are 2 channels there
will be 2 backupsets that consist of the datafile backups. The 3rd is the
controlfile backup created with "AUTOCONTROLFILE BACKUP" feature.

Use the "by summary" option to list your backups in RMAN.
This shows a short summary of your backupsets.

1 - channel datafile backupset
2 - channel datafile backupset
3 - autocontrolfile backup

RMAN> list backup summary;

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Tag
------- -- -- - ----------- --------------- ------- ------- ---
1 B F A DISK 20-JUL-01 1 1
2 B F A DISK 20-JUL-01 1 1
3 B F A DISK 20-JUL-01 1 1

The "list backup" command will also show the files associated with the
backupset key. It uses verbose by default.

6. Sample procedure for change generation.

This is a test procedure to generate 5 1M archived logs or just to
generate changes to the database to create blocks for backup. Useful
for incremental backup testing.

-- loop.sql

-- set echo off

drop table team1;

create table team1
(col1 number, col2 date);

drop sequence team1_seq;

create sequence team1_seq start with 1;

truncate table team1;

begin
for i in 1..100 loop
for i in 1..200 loop
insert into team1
values(team1_seq.nextval, sysdate);
end loop;
commit;
end loop;
end;
/
-- end

7. Create a test schema.

Create a schema to generate changes to the database so there will changed
blocks for backup. Then connect as that user and run "loop.sql". This can
be used to make changes for incremental backups.

SQL> create user team1 identified by team1
2 default tablespace users
3 temporary tablespace temp;

User created.

SQL> grant connect, resource to team1;

Grant succeeded.

SQL> connect team1/team1
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.

SQL> @loop.sql
drop table team1
*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

drop sequence team1_seq
*
ERROR at line 1:
ORA-02289: sequence does not exist

Sequence created.

Table truncated.

PL/SQL procedure successfully completed.

SQL> exit

8. Create a new closed database backup of the database.

% rman trace b_prod9_2_closed.log

RMAN> run {
shutdown immediate;
startup mount pfile=/proj/SME9i/prod9/pfile/initprod9.ora;
backup
incremental level 0
database format '/proj/SME9i/backup/%d_closed_%U';
shutdown;
startup pfile=/proj/SME9i/prod9/pfile/initprod9.ora;
}

9. Verify the new backup information in sqlplus and compare with RMAN.

sqlplus "/ as sysdba"

select TYPE, RECORDS_TOTAL, RECORDS_USED
from v$controlfile_record_section
where type like '%BACKUP%';

TYPE RECORDS_TOTAL RECORDS_USED
------------------ ------------- ------------
BACKUP SET 101 6
BACKUP PIECE 204 6
BACKUP DATAFILE 210 18
BACKUP REDOLOG 53 0
BACKUP CORRUPTION 185 0

RMAN> list backup summary;

using target database controlfile instead of recovery catalog

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Tag
------- -- -- - ----------- --------------- ------- ------- ---
1 B F A DISK 20-JUL-01 1 1
2 B F A DISK 20-JUL-01 1 1
3 B F A DISK 20-JUL-01 1 1

4 B F A DISK 20-JUL-01 1 1
5 B F A DISK 20-JUL-01 1 1
6 B F A DISK 20-JUL-01 1 1

RMAN> list backup by file;

List of Datafile Backups
========================

File Key TY LV S Ckp SCN Ckp Time #Pieces #Copies Tag
---- ------- - -- - ---------- --------- ------- ------- ---
1 5 B F A 97763 20-JUL-01 1 1
2 B F A 96266 20-JUL-01 1 1
2 4 B F A 97763 20-JUL-01 1 1
1 B F A 96266 20-JUL-01 1 1
3 4 B F A 97763 20-JUL-01 1 1
1 B F A 96266 20-JUL-01 1 1
4 5 B F A 97763 20-JUL-01 1 1
2 B F A 96266 20-JUL-01 1 1
5 5 B F A 97763 20-JUL-01 1 1
2 B F A 96266 20-JUL-01 1 1
6 4 B F A 97763 20-JUL-01 1 1
1 B F A 96266 20-JUL-01 1 1
7 5 B F A 97763 20-JUL-01 1 1
2 B F A 96266 20-JUL-01 1 1
8 4 B F A 97763 20-JUL-01 1 1
1 B F A 96266 20-JUL-01 1 1

List of Controlfile Backups
===========================

CF Ckp SCN Ckp Time BS Key S #Pieces #Copies Tag
---------- --------- ------- - ------- ------- ---
97763 20-JUL-01 6 A 1 1
96266 20-JUL-01 3 A 1 1

RMAN> exit

The above shows the controlfile backups are in BS_KEY 3 and 6 after 2 backups.
List by file shows details for which file belongs to which backupset.

10. Accounting for all files in the target.

SQL> column name format a50
SQL> select file#, name from v$datafile;

FILE# NAME
---------- --------------------------------------------------
1 /proj/SME9i/prod9/data/system01.dbf
2 /proj/SME9i/prod9/data/undotbs01.dbf
3 /proj/SME9i/prod9/data/cwmlite01.dbf
4 /proj/SME9i/prod9/data/drsys01.dbf
5 /proj/SME9i/prod9/data/example01.dbf
6 /proj/SME9i/prod9/data/indx01.dbf
7 /proj/SME9i/prod9/data/tools01.dbf
8 /proj/SME9i/prod9/data/users01.dbf

8 rows selected.

What and where is file #9?

/proj/SME9i/prod9/data/control01.ctl

The controlfile autotbackup from the controlfile persistent configuration
does a backup of the controlfile when you issue a backup command.

BACKUP DATABASE
BACKUP TABLESPACE
BACKUP DATAFILE
BACKUP ARCHIVELOG

RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 3
Report of files with less than 3 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 2 /proj/SME9i/prod9/data/system01.dbf
2 2 /proj/SME9i/prod9/data/undotbs01.dbf
3 2 /proj/SME9i/prod9/data/cwmlite01.dbf
4 2 /proj/SME9i/prod9/data/drsys01.dbf
5 2 /proj/SME9i/prod9/data/example01.dbf
6 2 /proj/SME9i/prod9/data/indx01.dbf
7 2 /proj/SME9i/prod9/data/tools01.dbf
8 2 /proj/SME9i/prod9/data/users01.dbf

In sqlplus run the team1 procedure again to generate more changes.

RMAN> @b_prod9_closed.rcv

Here is what I have in my backup directory.
Take a look at what's in yours.

[otcsol1]/proj/SME9i/backup> ls -g
ls -FC -g
total 1491252
-rw-r----- 1 udba 1511936 Jul 20 10:09 c-2094960375-20010720-00
-rw-r----- 1 udba 1511936 Jul 20 11:31 c-2094960375-20010720-01
-rw-r----- 1 udba 1511936 Jul 20 12:07 c-2094960375-20010720-02
-rw-r----- 1 udba 129495552 Jul 20 10:09 PROD9_closed_01cvcpvf_1_1
-rw-r----- 1 udba 122544640 Jul 20 10:09 PROD9_closed_02cvcpvf_1_1
-rw-r----- 1 udba 129909248 Jul 20 11:30 PROD9_closed_04cvcup9_1_1
-rw-r----- 1 udba 122544640 Jul 20 11:31 PROD9_closed_05cvcup9_1_1
-rw-r----- 1 udba 129909248 Jul 20 12:06 PROD9_closed_07cvd0ua_1_1
-rw-r----- 1 udba 122544640 Jul 20 12:07 PROD9_closed_08cvd0ua_1_1
-rw-r----- 1 udba 1503232 Jul 20 12:07 snapf_prod9.f

Since the retention policy is redundancy 3, let's see what needs backup or
if we've satisfied the policy.

RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 3
Report of files with less than 3 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 3
no obsolete backups found

RMAN>

Nothing to obsolete yet. Only 3 backups.

Run team# loop.sql to generate more changes.

Run sqlplus and verify the control file v$ backup information.

select TYPE, RECORDS_TOTAL, RECORDS_USED
from v$controlfile_record_section
where type like '%BACKUP%';

TYPE RECORDS_TOTAL RECORDS_USED
------------------ ------------- ------------
BACKUP SET 101 12
BACKUP PIECE 204 12
BACKUP DATAFILE 210 36
BACKUP REDOLOG 53 0
BACKUP CORRUPTION 185 0

[otcsol1]/proj/SME9i/backup> ls -g
ls -FC -g
total 1987620
-rw-r----- 1 udba 1511936 Jul 20 10:09 c-2094960375-20010720-00
-rw-r----- 1 udba 1511936 Jul 20 11:31 c-2094960375-20010720-01
-rw-r----- 1 udba 1511936 Jul 20 12:07 c-2094960375-20010720-02
-rw-r----- 1 udba 1511936 Jul 20 12:22 c-2094960375-20010720-03
-rw-r----- 1 udba 129495552 Jul 20 10:09 PROD9_closed_01cvcpvf_1_1
-rw-r----- 1 udba 122544640 Jul 20 10:09 PROD9_closed_02cvcpvf_1_1
-rw-r----- 1 udba 129909248 Jul 20 11:30 PROD9_closed_04cvcup9_1_1
-rw-r----- 1 udba 122544640 Jul 20 11:31 PROD9_closed_05cvcup9_1_1
-rw-r----- 1 udba 129909248 Jul 20 12:06 PROD9_closed_07cvd0ua_1_1
-rw-r----- 1 udba 122544640 Jul 20 12:07 PROD9_closed_08cvd0ua_1_1
-rw-r----- 1 udba 129909248 Jul 20 12:22 PROD9_closed_0acvd1ps_1_1
-rw-r----- 1 udba 122544640 Jul 20 12:22 PROD9_closed_0bcvd1ps_1_1
-rw-r----- 1 udba 1503232 Jul 20 12:22 snapf_prod9.f

SQL> set pagesize 60
SQL> select RECID, STAMP, COMPLETION_TIME, INCREMENTAL_LEVEL from v$backup_set;

RECID STAMP COMPLETION_TIME INCREMENTAL_LEVEL
---------- ---------- --------------- -----------------
1 435578964 20-JUL-01 0
2 435578982 20-JUL-01 0
3 435578987 20-JUL-01
4 435583853 20-JUL-01 0
5 435583874 20-JUL-01 0
6 435583877 20-JUL-01
7 435586019 20-JUL-01 0
8 435586027 20-JUL-01 0
9 435586036 20-JUL-01
10 435586934 20-JUL-01 0
11 435586942 20-JUL-01 0
12 435586945 20-JUL-01

12 rows selected.

SQL> set pagesize 60
SQL> column handle format a32
SQL> column tag format a18
SQL> select RECID,SET_STAMP, TAG, STATUS, HANDLE from v$backup_piece
order by SET_STAMP;

RECID SET_STAMP TAG S HANDLE
---------- ---------- ------------------ - --------------------------------
1 435578863 A /proj/SME9i/backup/PROD9_closed_
01cvcpvf_1_1 <<= DB Backupset

2 435578863 A /proj/SME9i/backup/PROD9_closed_
02cvcpvf_1_1 <<= DB Backupset

3 435578986 A /proj/SME9i/backup/c-2094960375-
20010720-00 <<= Controlfile backup

4 435583785 A /proj/SME9i/backup/PROD9_closed_
04cvcup9_1_1 <<= DB Backupset

5 435583785 A /proj/SME9i/backup/PROD9_closed_
05cvcup9_1_1 <<= DB Backupset

6 435583876 A /proj/SME9i/backup/c-2094960375-
20010720-01 <<= Controlfile backup

7 435585994 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_
07cvd0ua_1_1 <<= DB Backupset

8 435585994 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_
08cvd0ua_1_1 <<= DB Backupset

9 435586035 A /proj/SME9i/backup/c-2094960375-
20010720-02 <<= Controlfile backup

10 435586876 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_
0acvd1ps_1_1 <<= DB Backupset

11 435586876 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_
0bcvd1ps_1_1 <<= DB Backupset

12 435586944 A /proj/SME9i/backup/c-2094960375-
20010720-03 <<= Controlfile backup

12 rows selected.

What do we have in the backup directory?

[otcsol1]/proj/SME9i/backup> ls -g
ls -FC -g
total 1987620
-rw-r----- 1 udba 1511936 Jul 20 10:09 c-2094960375-20010720-00
-rw-r----- 1 udba 1511936 Jul 20 11:31 c-2094960375-20010720-01
-rw-r----- 1 udba 1511936 Jul 20 12:07 c-2094960375-20010720-02
-rw-r----- 1 udba 1511936 Jul 20 12:22 c-2094960375-20010720-03
-rw-r----- 1 udba 129495552 Jul 20 10:09 PROD9_closed_01cvcpvf_1_1
-rw-r----- 1 udba 122544640 Jul 20 10:09 PROD9_closed_02cvcpvf_1_1
-rw-r----- 1 udba 129909248 Jul 20 11:30 PROD9_closed_04cvcup9_1_1
-rw-r----- 1 udba 122544640 Jul 20 11:31 PROD9_closed_05cvcup9_1_1
-rw-r----- 1 udba 129909248 Jul 20 12:06 PROD9_closed_07cvd0ua_1_1
-rw-r----- 1 udba 122544640 Jul 20 12:07 PROD9_closed_08cvd0ua_1_1
-rw-r----- 1 udba 129909248 Jul 20 12:22 PROD9_closed_0acvd1ps_1_1
-rw-r----- 1 udba 122544640 Jul 20 12:22 PROD9_closed_0bcvd1ps_1_1
-rw-r----- 1 udba 1503232 Jul 20 12:22 snapf_prod9.f
[otcsol1]/proj/SME9i/backup>

What backups obsolete now?

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 3
Report of obsolete backups and copies
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 1 20-JUL-01
Backup Piece 1 20-JUL-01 /proj/SME9i/backup/PROD9_closed_0
1cvcpvf_1_1
Backup Set 2 20-JUL-01
Backup Piece 2 20-JUL-01 /proj/SME9i/backup/PROD9_closed_0
2cvcpvf_1_1
Backup Set 3 20-JUL-01
Backup Piece 3 20-JUL-01 /proj/SME9i/backup/c-2094960375-2
0010720-00

We now have an obsolete controlfile and 2 bakup sets. These are the 1st backups
we took. Backupsets 1, 2, and 3.

To remove the backupsets that exceed the retention policy set run "delete obsolete"
command and you will be prompted for Y/N to remove the physical backupsets and pieces.

RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 3
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=9 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=10 devtype=DISK
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 1 20-JUL-01
Backup Piece 1 20-JUL-01 /proj/SME9i/backup/PROD9_closed_0
1cvcpvf_1_1
Backup Set 2 20-JUL-01
Backup Piece 2 20-JUL-01 /proj/SME9i/backup/PROD9_closed_0
2cvcpvf_1_1
Backup Set 3 20-JUL-01
Backup Piece 3 20-JUL-01 /proj/SME9i/backup/c-2094960375-2
0010720-00

Do you really want to delete the above objects (enter YES or NO)? y
deleted backup piece
backup piece handle=/proj/SME9i/backup/PROD9_closed_01cvcpvf_1_1 recid=1 stamp=4
35578863
deleted backup piece
backup piece handle=/proj/SME9i/backup/PROD9_closed_02cvcpvf_1_1 recid=2 stamp=4
35578863
deleted backup piece
backup piece handle=/proj/SME9i/backup/c-2094960375-20010720-00 recid=3 stamp=43
5578987

The backups are removed from the backup directory and the controlfile.

[otcsol1]/proj/SME9i/backup> ls -g
ls -FC -g
total 1492068
-rw-r----- 1 udba 1511936 Jul 20 11:31 c-2094960375-20010720-01
-rw-r----- 1 udba 1511936 Jul 20 12:07 c-2094960375-20010720-02
-rw-r----- 1 udba 1511936 Jul 20 12:22 c-2094960375-20010720-03
-rw-r----- 1 udba 129909248 Jul 20 11:30 PROD9_closed_04cvcup9_1_1
-rw-r----- 1 udba 122544640 Jul 20 11:31 PROD9_closed_05cvcup9_1_1
-rw-r----- 1 udba 129909248 Jul 20 12:06 PROD9_closed_07cvd0ua_1_1
-rw-r----- 1 udba 122544640 Jul 20 12:07 PROD9_closed_08cvd0ua_1_1
-rw-r----- 1 udba 129909248 Jul 20 12:22 PROD9_closed_0acvd1ps_1_1
-rw-r----- 1 udba 122544640 Jul 20 12:22 PROD9_closed_0bcvd1ps_1_1
-rw-r----- 1 udba 1503232 Jul 20 12:22 snapf_prod9.f

There were 13 backup objects before delete obsolete and there are now 10.
The 3 backup sets are removed.

Looking at it in RMAN now:

RMAN> list backup by file;

List of Datafile Backups
========================

File Key TY LV S Ckp SCN Ckp Time #Pieces #Copies Tag
---- ------- - -- - ---------- --------- ------- ------- ---
1 11 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
8 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
5 B F A 97763 20-JUL-01 1 1
2 10 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
7 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
4 B F A 97763 20-JUL-01 1 1
3 10 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
7 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
4 B F A 97763 20-JUL-01 1 1
4 11 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
8 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
5 B F A 97763 20-JUL-01 1 1
5 11 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
8 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
5 B F A 97763 20-JUL-01 1 1
6 10 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
7 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
4 B F A 97763 20-JUL-01 1 1
7 11 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
8 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
5 B F A 97763 20-JUL-01 1 1
8 10 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
7 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
4 B F A 97763 20-JUL-01 1 1

List of Controlfile Backups
===========================

CF Ckp SCN Ckp Time BS Key S #Pieces #Copies Tag
---------- --------- ------- - ------- ------- ---
100734 20-JUL-01 12 A 1 1
99238 20-JUL-01 9 A 1 1
97763 20-JUL-01 6 A 1 1

The records are no longer available (RECID 1, 2, and 3) in RMAN.

set pagesize 60
column handle format a32
column tag format a18
select RECID,SET_STAMP, TAG, STATUS, HANDLE from v$backup_piece
order by SET_STAMP;

RECID SET_STAMP TAG S HANDLE
---------- ---------- ------------------ - --------------------------------
1 435578863 D /proj/SME9i/backup/PROD9_closed_
01cvcpvf_1_1

2 435578863 D /proj/SME9i/backup/PROD9_closed_
02cvcpvf_1_1

3 435578986 D /proj/SME9i/backup/c-2094960375-
20010720-00

4 435583785 A /proj/SME9i/backup/PROD9_closed_
04cvcup9_1_1

5 435583785 A /proj/SME9i/backup/PROD9_closed_
05cvcup9_1_1

6 435583876 A /proj/SME9i/backup/c-2094960375-
20010720-01

7 435585994 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_
07cvd0ua_1_1

8 435585994 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_
08cvd0ua_1_1

9 435586035 A /proj/SME9i/backup/c-2094960375-
20010720-02

10 435586876 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_
0acvd1ps_1_1

11 435586876 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_
0bcvd1ps_1_1

12 435586944 A /proj/SME9i/backup/c-2094960375-
20010720-03

12 rows selected.

select TYPE, RECORDS_TOTAL, RECORDS_USED
from v$controlfile_record_section
where type like '%BACKUP%';

TYPE RECORDS_TOTAL RECORDS_USED
------------------ ------------- ------------
BACKUP SET 101 12
BACKUP PIECE 204 12
BACKUP DATAFILE 210 36
BACKUP REDOLOG 53 0
BACKUP CORRUPTION 185 0

Because we are maintaining the backups using a set backup policy the records_used
will increase/decrease as you manage your backups.

2. Restore of a database w/o a catalog and noachivelog mode.

Restoring using the backups you've taken. Now the fun begins. First we'll remove
the database to simulate our own disaster to recover from.

[otcsol1]/proj/SME9i/prod9/rmanlab> cd ../data
[otcsol1]/proj/SME9i/prod9/data> ls -ltr
total 1320832
-rw-r----- 1 usupport udba 41947136 Jul 12 14:49 temp01.dbf
-rw-r----- 1 usupport udba 1049088 Jul 20 13:59 redo03.log
-rw-r----- 1 usupport udba 1049088 Jul 20 13:59 redo02.log
-rw-r----- 1 usupport udba 26218496 Jul 20 14:00 users01.dbf
-rw-r----- 1 usupport udba 209719296 Jul 20 14:00 undotbs01.dbf
-rw-r----- 1 usupport udba 10489856 Jul 20 14:00 tools01.dbf
-rw-r----- 1 usupport udba 340791296 Jul 20 14:00 system01.dbf
-rw-r----- 1 usupport udba 1049088 Jul 20 14:00 redo01.log
-rw-r----- 1 usupport udba 26218496 Jul 20 14:00 indx01.dbf
-rw-r----- 1 usupport udba 10489856 Jul 20 14:00 example01.dbf
-rw-r----- 1 usupport udba 20975616 Jul 20 14:00 drsys01.dbf
-rw-r----- 1 usupport udba 20975616 Jul 20 14:00 cwmlite01.dbf
-rw-r----- 1 usupport udba 1503232 Jul 20 14:00 control03.ctl
-rw-r----- 1 usupport udba 1503232 Jul 20 14:00 control02.ctl
-rw-r----- 1 usupport udba 1503232 Jul 20 14:00 control01.ctl

[otcsol1]/proj/SME9i/prod9/data> rm *
rm: remove control01.ctl (yes/no)? y
rm: remove control02.ctl (yes/no)? y
rm: remove control03.ctl (yes/no)? y
rm: remove cwmlite01.dbf (yes/no)? y
rm: remove drsys01.dbf (yes/no)? y
rm: remove example01.dbf (yes/no)? y
rm: remove indx01.dbf (yes/no)? y
rm: remove redo01.log (yes/no)? y
rm: remove redo02.log (yes/no)? y
rm: remove redo03.log (yes/no)? y
rm: remove system01.dbf (yes/no)? y
rm: remove temp01.dbf (yes/no)? y
rm: remove tools01.dbf (yes/no)? y
rm: remove undotbs01.dbf (yes/no)? y
rm: remove users01.dbf (yes/no)? y
[otcsol1]/proj/SME9i/prod9/data> y

[otcsol1]/proj/SME9i/prod9/data> ls -la
total 4
drwxr-xr-x 2 usupport udba 512 Jul 20 14:20 ./
drwxr-xr-x 11 usupport udba 512 Jul 19 13:43 ../

OK it's all gone now and you have to bring it back with RMAN...

Note: Make sure you set the NLS_LANG on the target for the recovery session at the
Unix prompt if the database is not using the US7ASCII characterset. Ex.
whatever your database characterset is you are restoring.

setenv NLS_LANG AMERICAN_AMERICA.WE8ISO8859P1
or
setenv NLS_LANG AMERICAN_AMERICA.UTF8

To recover the database using an autobackup of the control file without a recovery
catalog:

1. Use SQL*Plus to start, but not mount, the database. For example, run:

SQL> STARTUP NOMOUNT

2. Start RMAN but do not connect to the target database:

% rman

RMAN>

3. Set the database identifier for the target database with SET DBID. RMAN
displays the DBID whenever you connect to the target. You can also get it
by running LIST or by querying the catalog (refer to "Restoring When
Multiple Databases Share the same Name: Example"). For example, run:

SET DBID 2094960375;

Note: You can use log files to determine the DBID also. Everytime RMAN connects
to the database the DBID is displayed if the database is open or mounted.

4. Connect to the target database. For example, run:

CONNECT TARGET

5. Restore the backup control file, then perform recovery. Do the following:

a. Optionally, specify the most recent backup time stamp that
RMAN can use when searching for a control file autobackup to restore.

b. If a non-default format was used to create the control file, then
specify a non-default format for the restore of the control file.

c. If the channel that created the control file autobackup was device
type sbt, then you must allocate one or more sbt channels. Because no
repository is available, you cannot use automatic channels. If the
autobackup was created on a disk channel, however, then you do not
need to manually allocate a channel.

d. Restore the autobackup of the control file, optionally setting the
maximum number of days backward that RMAN can search (up to 366) and
the initial sequence number that it should use in its search for the
first day.

e. Mount the database. Note that because the repository is now
available, any automatic channels that you configured are also
available.

f. If the online logs are inaccessible, then restore and recover the
database as described in "Performing Incomplete Restore and
Recovery". You must terminate recovery by setting the UNTIL clause to
a time, log sequence, or SCN before the online redo logs. If the
online logs are usable, then restore and recover the database as
described in "Performing Complete Restore and Recovery".

In this example, the online redo logs have been lost. This example limits
the restore of the control file autobackup, then performs recovery of the
database to log sequence 13243, which is the most recent archived log:

# manually allocate one or more channels

RUN
{
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE disk TO
'/proj/SME9i/backup/%F';
ALLOCATE CHANNEL d1 DEVICE TYPE disk;
RESTORE CONTROLFILE FROM AUTOBACKUP
MAXSEQ 5 # start at sequence 5 and count down
MAXDAYS 5; # start at UNTIL TIME and search back 5 days
MOUNT DATABASE;
}

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

allocated channel: d1
channel d1: sid=9 devtype=DISK

Starting restore at 20-JUL-01

channel d1, looking for controlfile autobackup on day: 20010720
channel d1, controlfile autobackup found: /proj/SME9i/backup/c-2094960375-200107
20-03
channel d1, controlfile autobackup restore complete
replicating controlfile
input filename=/proj/SME9i/prod9/data/control01.ctl
output filename=/proj/SME9i/prod9/data/control02.ctl
output filename=/proj/SME9i/prod9/data/control03.ctl
Finished restore at 20-JUL-01

database mounted
released channel: d1

r_prod9_db.log:

RMAN> run {
2> restore database;
3> recover database noredo;
4> alter database open resetlogs;
5> }

Backup the database after resetlogs.

A new 3rd autocontrolfile backup is created. We used the old -03 to recover with
so backup -03 never really existed since the current controlfile now would have
been backup -03 of the controlfile.

Starting Control File Autobackup at 20-JUL-01
piece handle=/proj/SME9i/backup/c-2094960375-20010720-03 comment=NONE
Finished Control File Autobackup at 20-JUL-01

[otcsol1]/proj/SME9i/backup> ls -g
ls -FC -g
total 1985460
-rw-r----- 1 udba 1511936 Jul 20 11:31 c-2094960375-20010720-01
-rw-r----- 1 udba 1511936 Jul 20 12:07 c-2094960375-20010720-02
-rw-r----- 1 udba 1511936 Jul 20 16:18 c-2094960375-20010720-03
-rw-r----- 1 udba 129909248 Jul 20 11:30 PROD9_closed_04cvcup9_1_1
-rw-r----- 1 udba 122544640 Jul 20 11:31 PROD9_closed_05cvcup9_1_1
-rw-r----- 1 udba 129909248 Jul 20 12:06 PROD9_closed_07cvd0ua_1_1
-rw-r----- 1 udba 122544640 Jul 20 12:07 PROD9_closed_08cvd0ua_1_1
-rw-r----- 1 udba 129909248 Jul 20 12:22 PROD9_closed_0acvd1ps_1_1
-rw-r----- 1 udba 122544640 Jul 20 12:22 PROD9_closed_0bcvd1ps_1_1
-rw-r----- 1 udba 129909248 Jul 20 16:18 PROD9_closed_0dcvdfjd_1_1
-rw-r----- 1 udba 122544640 Jul 20 16:18 PROD9_closed_0ecvdfjd_1_1
-rw-r----- 1 udba 1503232 Jul 20 12:22 snapf_prod9.f
[otcsol1]/proj/SME9i/backup> ls -g
ls -FC -g
total 992724
-rw-r----- 1 udba 1511936 Jul 20 16:18 c-2094960375-20010720-03
-rw-r----- 1 udba 129909248 Jul 20 12:22 PROD9_closed_0acvd1ps_1_1
-rw-r----- 1 udba 122544640 Jul 20 12:22 PROD9_closed_0bcvd1ps_1_1
-rw-r----- 1 udba 129909248 Jul 20 16:18 PROD9_closed_0dcvdfjd_1_1
-rw-r----- 1 udba 122544640 Jul 20 16:18 PROD9_closed_0ecvdfjd_1_1
-rw-r----- 1 udba 1503232 Jul 20 12:22 snapf_prod9.f

RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 3
Report of files with less than 3 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 2 /proj/SME9i/prod9/data/system01.dbf
2 2 /proj/SME9i/prod9/data/undotbs01.dbf
3 2 /proj/SME9i/prod9/data/cwmlite01.dbf
4 2 /proj/SME9i/prod9/data/drsys01.dbf
5 2 /proj/SME9i/prod9/data/example01.dbf
6 2 /proj/SME9i/prod9/data/indx01.dbf
7 2 /proj/SME9i/prod9/data/tools01.dbf
8 2 /proj/SME9i/prod9/data/users01.dbf

select TYPE, RECORDS_TOTAL, RECORDS_USED
from v$controlfile_record_section
where type like '%BACKUP%';

TYPE RECORDS_TOTAL RECORDS_USED
------------------ ------------- ------------
BACKUP SET 101 14
BACKUP PIECE 204 14
BACKUP DATAFILE 210 44
BACKUP REDOLOG 53 0
BACKUP CORRUPTION 185 0

Summary:

In this lesson you should have learned how to configure RMAN to use the target
database controlfile as the recovery catalog using autocontrolfile backups. You
also learned how to created a persistent backup configuration for reuse. Then
you created level 0 and level 1 consistent backup using RMAN. After simulating
your own disaster you successfully restored and recovered the database using
the backups taken with RMAN.

RELATED DOCUMENTS
-----------------

Oracle9i Recovery Manager Users Guide and Reference Manuals.

Oracle Apps DBA stuff

Friday, November 26, 2010

Enable Database Enterprise Manager Console on R12

EMCTL ISSUES

Thursday, November 25, 2010

Master Note For Oracle Recovery Manager (RMAN)

Master Note For Oracle Recovery Manager (RMAN)

Master Note For Oracle Recovery Manager (RMAN)

Monday, November 22, 2010

sendmail.mc

How to Recreate a Controlfile

RMAN : Consistent Backup, Restore and Recovery using RMAN

Search This Blog

Blog Archive