Sachin's DBA Blog

Oracle Solaris 11

2011-12-22T05:49:00.002+05:30

Oracle Corporation has announced Oracle Solaris 11, the first cloud OS.

Below is the summary of key features/benefits of Oracle (cloud) Operating System.

User Environment

Popular Open Source Software Packages - Java SE 6 and 7, GCC 4.5.2, Python 2.7, Perl 5.12, Ruby 1.8.7, PHP 5.2.17 and complete web stack
Enhanced Desktop Environment
Default $PATH and User Familiarity
Default Shell - bash shell for users
Removable Media
New Sound System
Search for Content in Manual Pages
Virtual Console Terminals
Time Slider Snapshot Management
CUPS (Common UNIX Printing System) Printing
libc Familiarity
paths.h Path Name Definitions
Locales and Languages
TrueType Fonts

Installation

Installation of Solaris 11 or upgradation/migration to Solaris 11 made easier.

Automated Installer
Jumpstart Migration Utility
Interactive Text Installations
Live Media Installation
Distribution Constructor

Packaging

No more patching! when installing & updating software, IPS manages dependencies and image instances to keep installations running smoothly.

Image Packaging System (IPS)
SVR4 Package Compatibility

Virtualization/Zones

Oracle Solaris 11 virtualization enables the deployment of less hardware to perform the work that used to be distributed among many hardware systems.

Oracle Solaris 10 Zones
Zone Physical-to-Virtual (p2v) and Virtual-to-Virtual (v2v) Pre-flight Checker
NFS Server in a Zone
Exclusive-IP Zones by Default
Automatic VNIC (virtual network interface controller) Creation for Zones
Administer Network Flows within Non-Global Zones
Delegated Administration
Zone Boot Environments (ZBE)
Improved Zones Dataset Layout
Immutable Zones
Shutdown Zones Cleanly with zoneadm
Monitoring a Zone with zonestat
Zones Statistics Library, libzonestat

Storage

Oracle Solaris 11, with it's File system, DTrace and predictive self-healing features, ensures that the cloud's services are always available.

ZFS (Zone File System) Dataset Encryption
Save Space with ZFS
ZFS Shadow Migration
ZFS Backup with NDMP (Network Data Management Protocol)
Temporary ZFS Mounts
ZFS Snapshot Alias
Recursive ZFS Send
ZFS Diffs
NFSv4 Client and Server Migration Support
Built-in CIFS (Common Internet File System) for Microsoft Interoperability
DTrace Storage Providers
COMSTAR (Common Multiprotocol SCSI Target) SCSI Target Framework

Security

Oracle Solaris 11 provides unmatched security for cloud.

Role Authentication
Trusted Platform Module (TPM)
Labeled IPsec
IPsec Supports AES GMAC (AES Galois/Counter Mode) Cryptographic Algorithm
New Kerberos DTrace Providers
Trusted Extensions Enhancements
Support for ssh X.509 Certificate Extension
Oracle Solaris Cryptographic Framework
In-Kernel pfexec, Forced and Basic Privileges

Networking

Oracle Solaris 11 keeps the cloud eco-friendly with zones & network virtualization.

Network Virtualization and Resource Management
Manual and Automatic Networking
Default Names for Datalinks
Changing MAC Address with dladm
InfiniBand, Enabled and Optimized
Registration of Virtual LANs
Link Layer Discovery Protocol (LLDP) Support
New Sockets Architecture
Load Balancing
Link Protection
Bridging and Tunneling
IP Observability
IP Multipathing (IPMP)
IPMP Administration through ipadm
IPMP Transitive Probing for Less Restrictive Availability
Improved Availability for in.mpathd
I/O Enhancements to netcat
New FTP Server Implementation
DTrace Networking Providers

System Configuration

With Oracle Solaris 11, the first cloud operating system, users sees only what they actually use, and use only what they actually need.

Layering of the Service Management Facility (SMF) Repository
System Configuration in the SMF repository
Resetting System Configuration
SMF and FMA Notifications

Kernel/Platform Support

Solaris 11, from Oracle Corporation, has been optimized for Oracle Database.

SPARC T4 Support
Critical Threads
Single-root I/O Virtualization
NUMA (Non-Uniform Memory Access) I/O
Intel Advanced Vector Extensions (AVX)
Dynamic Intimate Shared Memory (DISM) Performance Improvements - useful for Dynamic SGA in Oracle Database
Suspend and Resume to RAM
Improved Hardware Support
Measuring System Latency
DTrace cpc Provider

Related Articles: Oracle 12C Oracle Certifications (for DBAs)

oracleasm utility

2011-11-19T03:03:00.020+05:30

Oracle ASM library (ASMLib) driver

#/etc/init.d/oracleasm start

#/etc/init.d/oracleasm stop

#/etc/init.d/oracleasm restart

#/etc/init.d/oracleasm status

#/etc/init.d/oracleasm enable

#/etc/init.d/oracleasm disable

#/etc/init.d/oracleasm configure

#/etc/init.d/oracleasm createdisk DISK_NAME PARTITION_NAME

#/etc/init.d/oracleasm createdisk VOL3 /dev/sdd1

#/etc/init.d/oracleasm createdisk ASM_DATA /dev/cciss/c0d1p1

#/etc/init.d/oracleasm scandisks

#/etc/init.d/oracleasm listdisks

#/etc/init.d/oracleasm querydisk DISK_NAME
#/etc/init.d/oracleasm querydisk -d LABEL
#/etc/init.d/oracleasm querydisk /dev/sdc6
#/etc/init.d/oracleasm querydisk -d VOL6
#/etc/init.d/oracleasm querydisk -p VOL1

#/etc/init.d/oracleasm renamedisk PARTITION_NAME DISK_NAME

#/etc/init.d/oracleasm renamedisk /dev/sdc1 VOL2

#/etc/init.d/oracleasm deletedisk DISK_NAME

#/etc/init.d/oracleasm deletedisk /dev/sdc9

/etc/sysconfig/oracleasm -- Oracle ASMLib configuration file

Oracle 12C

2011-07-22T22:29:00.009+05:30

Oracle8i

Oracle9i

...

Oracle 10g

....

Oracle 11g

.....

Oracle 12c (c for Cloud)

Oracle Enterprise Manager 12c was launched by Oracle Corporation.

Related Articles: Oracle 11g Release1 Oracle 11g Release2 Oracle 10g

ADRCI Commands

2010-10-10T23:23:00.036+05:30

ADRCI Commands
Automatic Diagnostic Repository (ADR) Command Interpreter, from 11g

$ adrci [-HELP] [SCRIPT=script_filename] [EXEC="command [;command;...]"]
$ adrci -help
$ adrci script=adrci_script.adi
$ adrci script=env.adrci
$ adrci exec="show alert"
$ adrci exec="show home; show incident"
$ adrci exec="show base; show editor; show tracefile"

$ adrci
adrci> HELP [COMMAND]
adrci> help
adrci> help set browser

adrci> CREATE REPORT report_type report_id
adrci> create report hm_run hm_run_6
HM - Health Monitor

adrci> ECHO quoted_string
adrci> echo "Hello world"

adrci> EXIT
adrci> exit

adrci> QUIT
adrci> quit

adrci> HOST ["host_command_string"]
adrci> host
adrci> host "ls -l *.sh"
adrci> host "vi tailalert.adrci"

Incident Packaging Service (IPS)
adrci> IPS ADD {INCIDENT inc_id | PROBLEM prob_id | PROBLEMKEY pr_key | SECONDS secs | TIME start_time TO end_time} PACKAGE pkg_id
adrci> ips add incident 22 package 33
adrci> ips add problem 3 package 1
adrci> ips add seconds 60 package 9
adrci> ips add time '2010-10-01 10:00:00.00 -07:00' to '2010-10-01 23:00:00.00 -07:00' package 7

adrci> IPS ADD NEW INCIDENTS PACKAGE package_id
adrci> ips add new incidents package 321

adrci> IPS ADD FILE filespec PACKAGE package_id
adrci> ips add file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log package 99

adrci> IPS COPY IN FILE filename [TO new_name][OVERWRITE] PACKAGE pkgid [INCIDENT incid]
adrci> ips copy in file /home/sona/trace/orcl_ora_63175.trc to ADR_HOME/trace/orcl_ora_63175.trc package 11 incident 6

adrci> IPS COPY OUT FILE source TO target [OVERWRITE]
adrci> ips copy out file ADR_HOME/trace/orcl_ora_63175.trc to /home/sona/trace/orcl_ora_63175.trc

adrci> IPS CREATE PACKAGE {INCIDENT inc_id | PROBLEM prob_id | PROBLEMKEY prob_key | SECONDS secs | TIME start_time TO end_time} [CORRELATE BASIC|TYPICAL|ALL]
adrci> ips create package
adrci> ips create package incident 111
adrci> ips create package incident 222 correlate basic
adrci> ips create package problem 6
adrci> ips create package problemkey '?'
adrci> ips create package problemkey "?"
adrci> ips create package seconds 333
adrci> ips create package seconds 444 correlate all
adrci> ips create package time '2010-10-01 00:00:00 -08:00' to '2010-10-02 23.59.59 -08:00'

adrci> IPS DELETE PACKAGE pkg_id
adrci> ips delete package 22

adrci> IPS FINALIZE PACKAGE pkg_id
adrci> ips finalize package 33

adrci> IPS GENERATE PACKAGE pkg_id [IN location] [COMPLETE|INCREMENTAL]
adrci> ips generate package 4 in /tmp
adrci> ips generate package 14 incremental

adrci> IPS GET MANIFEST FROM FILE filename
adrci> ips get manifest from file /home/satya/ORA603_20101006235311_INC_1.zip

adrci> IPS GET METADATA {FROM FILE filename | FROM ADR}
adrci> ips get metadata from file /home/satya/ORA600_20101006135346_COM_1.zip
adrci> ips get metadata from adr

adrci> IPS PACK [INCIDENT inc_id | PROBLEM prob_id | PROBLEMKEY prob_key | SECONDS secs | TIME start_time TO end_time] [CORRELATE {BASIC|TYPICAL|ALL}] [IN path]
adrci> ips pack
adrci> ips pack incident 41

adrci> ips pack problem 5 in /tmp

adrci> ips pack problemkey ORA 4031
adrci> ips pack seconds 60 correlate all

adrci> IPS REMOVE {INCIDENT inc_id | PROBLEM prob_id | PROBLEMKEY prob_key} PACKAGE pkg_id
adrci> ips remove incident 2 package 7
adrci> ips remove problem 4 package 8

adrci> IPS REMOVE FILE file_name PACKAGE pkg_id
adrci> ips remove file ADR_HOME/trace/orcl_ora_3579.trc package 4

adrci> IPS SET CONFIGURATION parameter_id value (parameter_id = 1 to 23)
adrci> ips set configuration 4 8

adrci> IPS SHOW CONFIGURATION [parameter_id]

(parameter_id = 1 to 23)
adrci> ips show configuration
adrci> ips show configuration 21

adrci> IPS SHOW FILES PACKAGE pkg_id
adrci> ips show files package 9

adrci> IPS SHOW INCIDENTS PACKAGE package_id
adrci> ips show incidents package 333

adrci> IPS UNPACK FILE file_name [INTO path]
adrci> ips unpack file /home/oracle/ORA4030_20101008145306_COM_1.zip into /tmp/newadr

adrci> PURGE [[-i {id1 | start_id end_id}] [-age mins] [-type {ALERT|INCIDENT|TRACE|CDUMP|HM}]
adrci> purge
adrci> purge -i 123 456
adrci> purge -age 60 -type incident
adrci> purge –age 720 –type alert

adrci> RUN script_name
@script_name
@@script_name
adrci> run adrscr9
adrci> run adrscr.adi
adrci> @adr.scr

adrci> SET BASE base_string
adrci> set base /u01/app/oracle/product

adrci> SET BROWSER browser_program
adrci> set browser firefox
adrci> set browser mozilla

adrci> SET CONTROL (SHORTP_POLICY = [720|value] | LONGP_POLICY = [8760|value])
adrci> set control (SHORTP_POLICY = 360)
adrci> set control (LONGP_POLICY = 4380)

adrci> SET ECHO ON|OFF
adrci> set echo on
adrci> set echo off

adrci> SET EDITOR editor_program (default editor is vi)
adrci> set editor vi
adrci> set editor xemacs

adrci> SET HOME home_location
adrci> set home diag\rdbms\orabase\orabase

adrci> SET HOMES home_location1, home_location2 [, ...]
adrci> set homes diag\rdbms\orabase\orabase, diag\tnslsnr\testdb\listener

adrci> SET HOMEPATH homepath_str1 [homepath_str2] [...]
adrci> set homepath diag\dbms\orabase\orabase
adrci> set homepath diag/rdbms/orcl/orcl4
adrci> set homepath diag/rdbms/dwh3/dwh31 diag/rdbms/dwh3/dwh32

adrci> SET TERMOUT ON|OFF
adrci> set termout on
adrci> set termout off

adrci> SHOW ALERT [-p predicate_string] [-tail [num] [-f]] [-term] [-file alert_file_name]
adrci> show alert -- it will open alert in vi editor
adrci> show alert -tail -- like Unix command tail
adrci> show alert -tail 200 -- like Unix command tail -200
adrci> show alert -tail -f -- like Unix command tail –f
adrci> show alert -tail 20 -f
adrci> show alert -term
adrci> show alert -P "MESSAGE_TEXT LIKE '%ORA-%'"
-- To list all the "ORA-" errors

adrci> SHOW BASE [-product product_name]
adrci> show base
adrci> show base -product client
adrci> show base -product adrci

adrci> SHOW CONTROL
adrci> show control

adrci> SHOW HM_RUN [-p predicate_string]
adrci> show hm_run
adrci> show hm_run -p "run_id=123"

adrci> SHOW HOME
adrci> show home

adrci> SHOW HOMES
adrci> show homes

adrci> SHOW HOMEPATH
adrci> show homepath

adrci> SHOW INCDIR [id | id_low id_high]
adrci> show incdir
adrci> show incdir 317
adrci> show incdir 3801 3804

adrci> SHOW INCIDENT -p predicate_string [-mode {BASIC|BRIEF|DETAIL}] [-orderby field1, field2, ...] [ASC|DSC]
adrci> show incident
adrci> show incident -mode brief
adrci> show incident -mode brief -p "incident_id=905"
adrci> show incident -mode detail
adrci> show incident -mode detail -p "incident_id=33"
adrci> show incident -p "CREATE_TIME > '2010-09-18 21:35:25.012579 +00:00'"

adrci> SHOW PROBLEM [-p predicate_string] [-last 50|num|-all] [-orderby field1, field2, ...] [ASC|DSC]]
adrci> show problem
adrci> show problem -all
adrci> show problem -p "problem_id=44"

adrci> SHOW REPORT report_type run_name
adrci> show report hm_run hm_run_9

adrci> SHOW TRACEFILE [file1 file2 ...] [-rt | -t] [-i inc1 inc2 ...] [-path path1 path2 ...]

adrci> show tracefile
adrci> show tracefile -t
adrci> show tracefile -rt
adrci> show tracefile %pmon%
adrci> show tracefile alert%log
adrci> show tracefile %mmon% -rt
adrci> show tracefile %smon% -path /home/satya/temp
adrci> show tracefile -i 916
adrci> show tracefile -i 1 4 -path diag/rdbms/orabase/orabase

adrci> SPOOL full_file_name [APPEND|OFF]
adrci> spool c:\dwh\alrt.txt
adrci> spool /home/satya/myalert.log
adrci> spool myfile.ado append
adrci> spool off

adrci> HELP EXTENDED
adrci> help extended
HELP [topic]
Available Topics:
BEGIN BACKUP
CD
DDE
DEFINE
DESCRIBE
END BACKUP
LIST DEFINE
MERGE ALERT
MERGE FILE
QUERY
SET COLUMN
SHOW CATALOG
SHOW DUMP
SHOW SECTION
SHOW TRACE
SHOW TRACEMAP
SWEEP
UNDEFINE
VIEW

adrci> define alertLog 'alert_prod.log'
adrci> list define
adrci> merge alert
adrci> show trace alert_dwh.log
adrci> show trace ~alertLog
adrci> undefine alertLog

adrci> HELP HIDDEN
adrci> help hidden
HELP [topic]
Available Topics:
CREATE HOME
CREATE INCIDENT
REGISTER INCIDENT FILE

adrci> CREATE HOME keyname=key_value [keyname=key_value ...]
adrci> create home base=/tmp product_type=rdbms product_id=prod1 instance_id=inst1

adrci> CREATE INCIDENT keyname=key_value [keyname=key_value ...]
adrci> create incident problem_key="ORA-00600: [Memory corruption]" error_facility=ORA error_number=600

adrci> REGISTER INCIDENT FILE keyname=key_value [keyname=key_value ...]
adrci> register incident file filename=ora_pmon_12345.trc incident_id=1

adrci> EXPORT relation_name [-p predicate_string] [-file filename] [-overwrite] [-dir]
adrci> export incident
adrci> export incident -p “incident_id>100″

adrci> IMPORT file_name [-dir dir_name]
adrci> import incident_99.imp

Source: Internet

dgmgrl utility

2010-09-01T19:11:00.076+05:30

dgmgrl - Data Guard Manager Utility

$dgmgrl sys/pwd
$dgmgrl sys/pwd@oltp
$dgmgrl -logfile observer.log / "stop observer"
$dgmgrl sys/test@dgprimary "show database 'prod'"

ADD - Adds a standby database to the broker configuration.

DGMGRL> ADD DATABASE db_name [AS CONNECT IDENTIFIER IS conn_identifier] [MAINTAINED AS {PHYSICAL|LOGICAL}];

DGMGRL> ADD DATABASE 'testdb' AS CONNECT IDENTIFIER IS testdb MAINTAINED AS PHYSICAL;
DGMGRL> ADD DATABASE 'logdb' AS CONNECT IDENTIFIER IS logdb MAINTAINED AS LOGICAL;

DGMGRL> ADD DATABASE 'devdb' AS CONNECT IDENTIFIER IS devdb.foo.com;

CONNECT - Connects to an Oracle database instance.

DGMGRL> CONNECT username/password[@connect_identifier]

DGMGRL> CONNECT sys/pwd;

DGMGRL> CONNECT sys/pwd@dwh;
DGMGRL> CONNECT /
DGMGRL> CONNECT /@dgsetup

CONVERT - Converts a database from one type to another (from 11g).

DGMGRL> CONVERT DATABASE database_name TO {SNAPSHOT STANDBY|PHYSICAL STANDBY};

DGMGRL> CONVERT DATABASE 'devdb' to SNAPSHOT STANDBY;

DGMGRL> CONVERT DATABASE 'devdb' to PHYSICAL STANDBY;

CREATE - Creates a broker configuration.

DGMGRL> CREATE CONFIGURATION config_name AS PRIMARY DATABASE IS db_name CONNECT IDENTIFIER IS conn_ident;

DGMGRL> CREATE CONFIGURATION 'dg' AS PRIMARY DATABASE IS 'prod' CONNECT IDENTIFIER IS prod.foo.com;

DGMGRL> CREATE CONFIGURATION 'dg_test' AS PRIMARY DATABASE IS 'test' CONNECT IDENTIFIER IS test;

DISABLE - Disables a configuration, a database, or fast-start failover (FSFO).

DGMGRL> DISABLE CONFIGURATION;

DGMGRL> DISABLE DATABASE database_name;

DGMGRL> DISABLE DATABASE 'devdb';

DGMGRL> DISABLE FAST_START FAILOVER [FORCE | CONDITION condition];

DGMGRL> DISABLE FAST_START FAILOVER;

DGMGRL> DISABLE FAST_START FAILOVER FORCE;

EDIT - Edits a configuration, database, or instance.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS {MaxProtection|MaxAvailability|MaxPerformance};

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPROTECTION;

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;

DGMGRL> EDIT CONFIGURATION SET PROPERTY property_name = value;

DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = 45;

DGMGRL> EDIT DATABASE database_name SET PROPERTY property_name = value;

DGMGRL> EDIT DATABASE devdb SET PROPERTY 'LogArchiveFormat'='log_%t_%s_%r_%d.arc';
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogXptMode=SYNC;

DGMGRL> EDIT DATABASE prodb SET PROPERTY LogXptMode=ASYNC;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogXptMode=ARCH;
DGMGRL> EDIT DATABASE devdb SET PROPERTY LogShipping=OFF;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogArchiveTrace=8;
DGMGRL> EDIT DATABASE prodb SET PROPERTY NetTimeout=60;
DGMGRL> EDIT DATABASE devdb SET PROPERTY 'ReopenSecs'=300;
DGMGRL> EDIT DATABASE prodb SET PROPERTY ArchiveLagTarget=1200;
DGMGRL> EDIT DATABASE prodb SET PROPERTY FastStartFailoverTarget='standby';
DGMGRL> EDIT DATABASE devdb SET PROPERTY 'StandbyArchiveLocation'='/oradata/archive/';

DGMGRL> EDIT DATABASE database_name RENAME TO new database_name;
DGMGRL> EDIT DATABASE 'devdbb' RENAME TO 'devdb';

DGMGRL> EDIT DATABASE database_name SET STATE = state [WITH APPLY INSTANCE = instance_name];

DGMGRL> EDIT DATABASE devdb SET STATE='READ-ONLY';

DGMGRL> EDIT DATABASE devdb SET STATE='OFFLINE';

DGMGRL> EDIT DATABASE devdb SET STATE='APPLY-OFF';
DGMGRL> EDIT DATABASE devdb SET STATE='APPLY-ON';

DGMGRL> EDIT DATABASE devdb SET STATE='TRANSPORT-OFF';

DGMGRL> EDIT DATABASE devdb SET STATE='TRANSPORT-ON';

DGMGRL> EDIT DATABASE prodb SET STATE='LOG-TRANSPORT-OFF';
DGMGRL> EDIT DATABASE devdb SET STATE='ONLINE' WITH APPLY INSTANCE=devdb2;

DGMGRL> EDIT INSTANCE instance_name [ON DATABASE database_name] SET AUTO PFILE [={init_file_path|OFF}];

DGMGRL> EDIT INSTANCE 'devdb1' ON DATABASE 'devdb' SET AUTO PFILE='initdevdb1.ora';

DGMGRL> EDIT INSTANCE instance_name [ON DATABASE database_name] SET PROPERTY property_name = value;

DGMGRL> EDIT INSTANCE * ON DATABASE database_name SET PROPERTY property_name = value;

DGMGRL> EDIT INSTANCE 'proddb' ON DATABASE 'proddb' SET PROPERTY 'StandbyArchiveLocation'='/oradata/arch/';

ENABLE - Enables a configuration, a database, or fast-start failover (FSFO).

DGMGRL> ENABLE CONFIGURATION;

DGMGRL> ENABLE DATABASE database_name;

DGMGRL> ENABLE DATABASE 'devdb';

DGMGRL> ENABLE FAST_START FAILOVER [CONDITION condition];

DGMGRL> ENABLE FAST_START FAILOVER;

EXIT - Exits the program.

DGMGRL> EXIT;

FAILOVER - Changes a standby database to be the primary database.

DGMGRL> FAILOVER TO standby_database_name [IMMEDIATE]

DGMGRL> FAILOVER TO "testdb";
DGMGRL> FAILOVER TO "snapdb" IMMEDIATE;

HELP - Displays description and syntax for a command.

DGMGRL> HELP [command];

DGMGRL> HELP REINSTATE
DGMGRL> HELP EDIT

QUIT - Exits the program.

DGMGRL> QUIT;

REINSTATE - Changes a database marked for reinstatement into a viable standby.

DGMGRL> REINSTATE DATABASE database_name;

DGMGRL> REINSTATE DATABASE prim1;

REM - Comment to be ignored by DGMGRL.

DGMGRL> REM [comment];

REMOVE - Removes a configuration, database, or instance.

DGMGRL> REMOVE CONFIGURATION [PRESERVE DESTINATIONS];

DGMGRL> REMOVE CONFIGURATION;

DGMGRL> REMOVE CONFIGURATION PRESERVE DESTINATIONS;

DGMGRL> REMOVE DATABASE database_name [PRESERVE DESTINATIONS];

DGMGRL> REMOVE DATABASE devdb;
DGMGRL> REMOVE DATABASE standby PRESERVE DESTINATIONS;

DGMGRL> REMOVE INSTANCE instance_name [ON DATABASE database_name];

DGMGRL> REMOVE INSTANCE inst1 ON DATABASE racdb;

SHOW - Displays information about a configuration, database, instance or FSFO.

DGMGRL> SHOW CONFIGURATION [VERBOSE];

DGMGRL> SHOW CONFIGURATION;

DGMGRL> SHOW CONFIGURATION VERBOSE;

DGMGRL> SHOW DATABASE [VERBOSE] db_name [property_name];

DGMGRL> SHOW DATABASE 'devdb';

DGMGRL> SHOW DATABASE VERBOSE 'test';

DGMGRL> SHOW DATABASE 'proddb' 'LogXptStatus';

DGMGRL> SHOW DATABASE 'proddb' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'proddb' 'InconsistentLogXptProps';
DGMGRL> SHOW DATABASE 'testdb' 'ArchiveLagTarget';
DGMGRL> SHOW DATABASE 'testdb' 'LogShipping';
DGMGRL> SHOW DATABASE 'testdb' 'PreferredApplyInstance';
DGMGRL> SHOW DATABASE 'proddb' 'StatusReport';
DGMGRL> SHOW DATABASE 'testdb' 'RecvQEntries';
DGMGRL> SHOW DATABASE 'proddb' 'SendQEntries';

DGMGRL> SHOW INSTANCE [VERBOSE] instance_name [property_name] [ON DATABASE db_name];

DGMGRL> SHOW INSTANCE inst1;

DGMGRL> SHOW INSTANCE VERBOSE inst3;
DGMGRL> SHOW INSTANCE testdb 'TopWaitEvents';

DGMGRL> SHOW FAST_START FAILOVER;
DGMGRL> SHOW FAST_START FAILOVER;

SHUTDOWN - Shuts down a currently running Oracle instance.

DGMGRL> SHUTDOWN [NORMAL | IMMEDIATE | ABORT];

DGMGRL> SHUTDOWN;
DGMGRL> SHUT IMMEDIATE;

START - Starts the fast-start failover(FSFO) observer.

DGMGRL> START OBSERVER [FILE=observer_configuration_file];

DGMGRL> START OBSERVER;

STARTUP - Starts an Oracle database instance.

DGMGRL> STARTUP [RESTRICT] [FORCE] [PFILE=filespec]
[NOMOUNT | MOUNT | OPEN [READ ONLY|READ WRITE]];

DGMGRL> STARTUP;
DGMGRL> STARTUP NOMOUNT;
DGMGRL> STARTUP MOUNT;
DGMGRL> STARTUP OPEN;

DGMGRL> STARTUP FORCE RESTRICT NOMOUNT;
DGMGRL> STARTUP PFILE=initdwh.ora NOMOUNT;

STOP - Stops the fast-start failover(FSFO) observer.

DGMGRL> STOP OBSERVER;

SWITCHOVER - Switches roles between a primary and standby database.

DGMGRL> SWITCHOVER TO standby_database_name;

DGMGRL> SWITCHOVER TO "standby";

$ORACLE_HOME/rdbms/log/drc*.log

alter system set dg_broker_start=false;
alter system set dg_broker_start=false sid='*';
alter system set dg_broker_start=FALSE SCOPE=spfile SID='*';
alter system set dg_broker_start=true;
alter system set dg_broker_start=true sid='*';
alter system set dg_broker_start=TRUE SCOPE=spfile SID='*';

alter system set dg_broker_config_file1='/u01/dg_broker_config_files/dr1TESTPRI.dat' sid='*';
alter system set dg_broker_config_file2='/u01/dg_broker_config_files/dr2TESTPRI.dat' sid='*';

Source: Internet

Setting SQL prompt

2010-08-09T17:13:00.023+05:30

The default prompt in SQL*Plus is SQL>, does not provide any information like who the user is and what the user is connected as. Prior to Oracle9i, we have to do elaborate coding to get the information as the SQL prompt, from 9.2.0 we can use SET SQLPROMPT along with SQL*Plus predefined variables.

Whenever SQL*PLUS starts up, it looks for a file named glogin.sql under the directory $ORACLE_HOME/sqlplus/admin. If the file is found, it is read and the containing statements executed. This allows to store settings across SQL*PLUS sessions. From 10g, after reading glogin.sql, SQL*PLUS also looks for a file named login.sql, in the directory from where SQL*PLUS was and in the directory that the environment variable SQLPATH points to, and reads it and executes it. Settings from the login.sql take precedence over settings from glogin.sql.

In Oracle9i, whenever a user connects through SQL*PLUS, Oracle will execute only glogin.sql, from 10g Oracle will execute login.sql as well. From Oracle 10g, the login.sql file is not only executed at SQL*Plus startup time, but also at connect time as well. So SQL prompt will be changed after connect command.

To set SQL prompt permanently, update $ORACLE_HOME/sqlplus/admin/glogin.sql or login.sql

SQLP[ROMPT] {SQL>|text}

TEXT can be predefined substitution variables which are prefixed with an underscore.

_connect_identifier	will display connection identifier.
_date	will display date.
_editor	will display editor name used by the EDIT command.
_o_version	will display Oracle version.
_o_release	will display Oracle release.
_privilege	will display privilege such as SYSDBA, SYSOPER, SYSASM
_sqlplus_release	will display SQL*PLUS release.
_user	will display current user name.

The variable _CONNECT_IDENTIFIER was introduced in SQL*Plus 9.2 and _DATE, _PRIVILEGE and _USER were introduced in SQL*Plus 10.1.

_USER
The variable _USER contains the current user name given by SHOW USER. If SQL*Plus is not connected, the variable is defined as an empty string.

SQL> set sqlprompt "_user>"

The SQL*Plus prompt will shows
SYSTEM>
SATYA>

_PRIVILEGE
When SQL*Plus is connected as a privileged user the variable _PRIVILEGE contains the connection privilege "AS SYSBDA" or "AS SYSOPER" or "AS SYSASM". If SQL*Plus is connected as a normal user the variable is defined as an empty string.
SQL> set sqlprompt "_user _privilege>"

The SQL*Plus prompt will shows
SYS AS SYSDBA>
SATYA AS SYSOPER>
ASMADM AS SYSASM>

_CONNECT_IDENTIFIER
The variable _CONNECT_IDENTIFIER contains the connection identifier used to start SQL*Plus. For example, if the SQL*Plus connection string is "hr/my_password@MYSID" then the variable contains MYSID. If you use a complete Oracle Net connection string like "hr/my_password@(DESCRIPTION=(ADDRESS_LIST=...(SERVICE_NAME=MYSID.MYDOMAIN)))" then _CONNECT_IDENTIFIER will be set to MYSID. If the connect identifier is not explicitly specified then _CONNECT_IDENTIFIER contains the default connect identifier Oracle uses for connection. For example, on UNIX it will contain the value in the environment variable ORACLE_SID or TWO_TASK. If SQL*Plus is not connected then the variable is defined as an empty string.

SQL> set sqlprompt "&_user@&_connect_identifier>"
or
SQL> set sqlprompt "_user'@'_connect_identifier>"
The SQL*Plus prompt will shows
SYS@PROD>
SYSTEM@DWH>

_DATE
The variable _DATE can be either dynamic, showing the current date or it can be set to a fixed string. The date is formatted using the value of NLS_DATE_FORMAT and will show time information. By default a DEFINE or dereference using &_DATE will give the date at the time of use. _DATE can be UNDEFINED, or set to a fixed string with an explicit DEFINE command. Dynamic date behavior is re-enabled by defining _DATE to an empty string.If we want to display current date:
SQL> set sqlprompt "_user _privilege 'on' _date>"
SYS AS SYSDBA on 9-AUG-10>

If we want to display the current date & time:
SATYA on 9-AUG-10 at DEVDB> alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss';
SATYA on 08/09/2010 13:03:51 at DEVDB>

_EDITOR
The variable _EDITOR contains the external text editor executable name.
set sqlprompt _editor>

The SQL*Plus prompt will shows
vi>

_O_VERSION
The variable _O_VERSION contains a text string showing the database version and available options.
set sqlprompt _o_version>

The SQL*Plus prompt will shows
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Data Mining and Real Application Testing options>

_O_RELEASE
The variable _O_RELEASE contains a string representation of the Oracle database version number. If Oracle database version is 11.1.0.7.0 then the variable contains "1101000700". The Oracle version may be different from the SQL*Plus version if you use Oracle Net to connect to a remote database.

set sqlprompt _o_release>

The SQL*Plus prompt will shows (for 10.2.0.4.0)
1002000400>

_SQLPLUS_RELEASE
The variable _SQLPLUS_RELEASE contains the SQL*Plus version number in a similar format to _O_RELEASE.

set sqlprompt _sqlplus_release>

The SQL*Plus prompt will shows (for 10.2.0.4.0)
1002000400>

SQL> set sqlprompt "_user'@'_connect_identifier:SQL> "
SCOTT@DEVDB:SQL> connect system/manager
SYSTEM@DEVDB:SQL> disconnect
@:SQL> connect OEM/OEM@oemdb
OEM@OEMDB:SQL>

To reset to the default SQL prompt,
SQL> set sqlprompt 'SQL>'

Source: Internet

Differences between Managers and Leaders

2010-07-19T13:03:00.008+05:30

A manager and a leader are two different kinds of individuals.

Leaders as inspiring visionaries, concerned about substance; while managers are viewed as planners who have concerns with process.
Managers administer, leaders innovate.
Managers ask how and when, leaders ask what and why.
Managers focus on systems, leaders focus on people.
Managers do things right, leaders do the right things.
Managers maintain, leaders develop.

Managers rely on control, leaders inspire trust.
Managers have a short-term perspective, leaders have a longer-term perspective.
Managers accept the status-quo; leaders challenge the status-quo.
Managers have an eye on the bottom line, leaders have an eye on the horizon
Managers imitate, leaders originate.
Managers emulate the classic good soldier, leaders are their own person.
Manager's copy, leaders show originality.
Managers take care of the day to day efficiency focused set of activities and bring in the operational excellence that helps the organization prepare itself for the leader who then builds the long term, strategic and transformational initiatives.

In an organization we need both types of individuals.

Source: SMU MBA HRM Book

Oracle Statistics

2010-06-03T11:14:00.042+05:30

Whenever a valid SQL statement is processed Oracle has to decide how to retrieve the necessary data. This decision can be made using one of two methods:

Rule Based Optimizer (RBO) - This method is used if the server has no internal statistics relating to the objects referenced by the statement. This method is no longer favoured by Oracle and was desupported from 10g.
Cost Based Optimizer (CBO) - This method is used if internal statistics are present. The CBO checks several possible execution plans and selects the one with the lowest cost, where cost relates to system resources. Since Oracle 8i the Cost Based Optimizer (CBO) is the preferred optimizer for Oracle.

Oracle statistics tell us the size of the tables, the distribution of values within columns, and other important information so that SQL statements will always generate the best execution plans. If new objects are created, or the amount of data in the database changes the statistics will no longer represent the real state of the database so the CBO decision process may be seriously impaired.

Oracle can do things in several different ways, e.g. select might be done by table scan or by using indexes. It uses statistics, a variety of counts and averages and other numbers, to figure out the best way to do things. It does the figuring automatically, using the Cost Based Optimizer. DBA job is to make sure the numbers are good enough for that optimizer to work properly.

Oracle statistics may refer to historical performance statistics that are kept in STATSPACK or AWR, but more common use of the term Oracle statistics is about Oracle optimizer Metadata statistics in order to provide the cost-based SQL optimizer with the information about the nature of the tables. The statistics mentioned here are optimizer statistics, which are created for the purposes of query optimization and are stored in the data dictionary. These statistics should not be confused with performance statistics visible through V$ views.

The optimizer is influenced by the following factors:

OPTIMIZER_MODE in the initialization file
Statistics in the data dictionary
Hints

OPTIMIZER_MODE can have the following values:
CHOOSE
ALL_ROWS
FIRST_ROWS
RULE

If we provide Oracle with good statistics about the schema the CBO will almost always generate an optimal execution plan. The areas of schema analysis include:

Object statistics - Statistics for all tables, partitions, IOTs, etc should be sampled with a deep and statistically valid sample size.
Critical columns - Those columns that are regularly-referenced in SQL statements that are:
- Heavily skewed columns - This helps the CBO properly choose between an index range scan and a full table scan.
- Foreign key columns - For n-way table joins, the CBO needs to determine the optimal table join order and knowing the cardinality of the intermediate results sets is critical.
External statistics - Oracle will sample the CPU cost and I/O cost during statistics collection and use this information to determine the optimal execution plan, based on optimizer_mode. External statistics are most useful for SQL running in the all_rows optimizer mode.

Optimizer statistics are a collection of data that describe more details about the database and the objects in the database. These statistics are used by the query optimizer to choose the best execution plan for each SQL statement. Optimizer statistics include the following:

Table statistics
- Number of rows
- Number of blocks
- Average row length
Column statistics
- Number of distinct values (NDV) in column
- Number of nulls in column
- Data distribution (histogram)
Index statistics
- Number of leaf blocks
- Levels
- Clustering factor
System statistics
- I/O performance and utilization
- CPU performance and utilization

The optimizer statistics are stored in the data dictionary. They can be viewed using data dictionary views. Only statistics stored in the dictionary itself have an impact on the cost-based optimizer.

When statistics are updated for a database object, Oracle invalidates any currently parsed SQL statements that access the object. The next time such a statement executes, the statement is re-parsed and the optimizer automatically chooses a new execution plan based on the new statistics. Distributed statements accessing objects with new statistics on remote databases are not invalidated. The new statistics take effect the next time the SQL statement is parsed.

Because the objects in a database can be constantly changing, statistics must be regularly updated so that they accurately describe these database objects. Statistics are maintained automatically by Oracle or we can maintain the optimizer statistics manually using the DBMS_STATS package.

DBMS_STATS package provides procedures for managing statistics. We can save and restore copies of statistics. You can export statistics from one system and import those statistics into another system. For example, you could export statistics from a production system to a test system. We can lock statistics to prevent those statistics from changing.

For data warehouses and database using the all_rows optimizer_mode, from Oracle9i release 2 we can collect the external cpu_cost and io_cost metrics. The ability to save and re-use schema statistics is important:

Bi-Modal databases - Many databases get huge benefits from using two sets of stats, one for OLTP (daytime), and another for batch (evening jobs).
Test databases - Many Oracle professionals will export their production statistics into the development instances so that the test execution plans more closely resemble the production database.

Creating statistics
In order to make good use of the CBO, we need to create statistics for the data in the database. There are several options to create statistics.

Automatic Statistics Gathering
The recommended approach to gathering statistics is to allow Oracle to automatically gather the statistics. Oracle gathers statistics on all database objects automatically and maintains those statistics in a regularly-scheduled maintenance job. Automated statistics collection eliminates many of the manual tasks associated with managing the query optimizer, and significantly reduces the chances of getting poor execution plans because of missing or stale statistics.

GATHER_STATS_JOB
Optimizer statistics are automatically gathered with the job GATHER_STATS_JOB. This job gathers statistics on all objects in the database which have missing statistics and stale statistics.

This job is created automatically at database creation time and is managed by the Scheduler. The Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends. The stop_on_window_close attribute controls whether the GATHER_STATS_JOB continues when the maintenance window closes. The default setting for the stop_on_window_close attribute is TRUE, causing Scheduler to terminate GATHER_STATS_JOB when the maintenance window closes. The remaining objects are then processed in the next maintenance window.

The GATHER_STATS_JOB job gathers optimizer statistics by calling the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure. The GATHER_DATABASE_STATS_JOB_PROC procedure collects statistics on database objects when the object has no previously gathered statistics or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows).The GATHER_DATABASE_STATS_JOB_PROC is an internal procedure, but its operates in a very similar fashion to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. The primary difference is that the GATHER_DATABASE_STATS_JOB_PROC procedure prioritizes the database objects that require statistics, so that those objects which most need updated statistics are processed first. This ensures that the most-needed statistics are gathered before the maintenance window closes.

Enabling Automatic Statistics Gathering
Automatic statistics gathering is enabled by default when a database is created, or when a database is upgraded from an earlier database release. We can verify that the job exists by viewing the DBA_SCHEDULER_JOBS view:
SQL> SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';

In situations when you want to disable automatic statistics gathering, then disable the GATHER_STATS_JOB as follows:
BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/

Automatic statistics gathering relies on the modification monitoring feature. If this feature is disabled, then the automatic statistics gathering job is not able to detect stale statistics. This feature is enabled when the STATISTICS_LEVEL parameter is set to TYPICAL (default) or ALL.

When to Use Manual Statistics
Automatic statistics gathering should be sufficient for most database objects which are being modified at a moderate speed. However, there are cases where automatic statistics gathering may not be adequate. Because the automatic statistics gathering runs during an overnight batch window, the statistics on tables which are significantly modified during the day may become stale. There are typically two types of such objects:

Volatile tables that are being deleted or truncated and rebuilt during the course of the day.
Objects which are the target of large bulk loads which add 10% or more to the object's total size.

For highly volatile tables, there are two approaches:

The statistics on these tables can be set to NULL. When Oracle encounters a table with no statistics, Oracle dynamically gathers the necessary statistics as part of query optimization. This dynamic sampling feature is controlled by the OPTIMIZER_DYNAMIC_SAMPLING parameter, and this parameter should be set to a value of 2 or higher. The default value is 2. The statistics can set to NULL by deleting and then locking the statistics:
BEGIN
DBMS_STATS.DELETE_TABLE_STATS('SCOTT','EMP');
DBMS_STATS.LOCK_TABLE_STATS('SCOTT','EMP');
END;
/
The statistics on these tables can be set to values that represent the typical state of the table. We should gather statistics on the table when the tables have a representative number of rows, and then lock the statistics.

This is more effective than the GATHER_STATS_JOB, because any statistics generated on the table during the overnight batch window may not be the most appropriate statistics for the daytime workload.For tables which are being bulk-loaded, the statistics-gathering procedures should be run on those tables immediately following the load process, preferably as part of the same script or job that is running the bulk load.

For external tables, statistics are not collected during GATHER_SCHEMA_STATS, GATHER_DATABASE_STATS, and automatic statistics gathering processing. However, you can collect statistics on an individual external table using GATHER_TABLE_STATS. Sampling on external tables is not supported so the ESTIMATE_PERCENT option should be explicitly set to NULL. Because data manipulation is not allowed against external tables, it is sufficient to analyze external tables when the corresponding file changes.

If the monitoring feature is disabled by setting STATISTICS_LEVEL to BASIC, automatic statistics gathering cannot detect stale statistics. In this case statistics need to be manually gathered.

Another area in which statistics need to be manually gathered is the system statistics. These statistics are not automatically gathered.

Statistics on fixed objects, such as the dynamic performance tables, need to be manually collected using GATHER_FIXED_OBJECTS_STATS procedure. Fixed objects record current database activity; statistics gathering should be done when database has representative activity.

Whenever statistics in dictionary are modified, old versions of statistics are saved automatically for future restoring. Statistics can be restored using RESTORE procedures of DBMS_STATS package.

In some cases, we may want to prevent any new statistics from being gathered on a table or schema by the DBMS_STATS_JOB process, such as highly volatile tables. In those cases, the DBMS_STATS package provides procedures for locking the statistics for a table or schema.

Scheduling Stats
Scheduling the gathering of statistics using DBMS_JOB is the easiest way to make sure they are always up to date:

SET SERVEROUTPUT ON
DECLARE
l_job NUMBER;
BEGIN
DBMS_JOB.submit(l_job, 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
SYSDATE,'SYSDATE + 1');
COMMIT;
DBMS_OUTPUT.put_line('Job: ' || l_job);
END;
/

The above code sets up a job to gather statistics for SCOTT for the current time every day. We can list the current jobs on the server using the DBA_JOBS and DBA_JOBS_RUNNING views.

Existing jobs can be removed using:
EXEC DBMS_JOB.remove(X);
COMMIT;
Where 'X' is the number of the job to be removed.

Manual Statistics Gathering
If you choose not to use automatic statistics gathering, then you need to manually collect statistics in all schemas, including system schemas. If the data in the database changes regularly, you also need to gather statistics regularly to ensure that the statistics accurately represent characteristics of your database objects.

The preferred tool for collecting statistics used to be the ANALYZE command. Over the past few releases, the DBMS_STATS package in the PL/SQL Packages and Types reference has taken over the statistics functions, and left the ANALYZE command with more mundane 'health check' work like analyzing chained rows.

Analyze Statement
The ANALYZE statement can be used to gather statistics for a specific table, index or cluster. The statistics can be computed exactly, or estimated based on a specific number of rows, or a percentage of rows.

The ANALYZE command is available for all versions of Oracle, however to obtain faster and better statistics use the procedures supplied - in 7.3.4 and 8.0 DBMS_UTILITY.ANALYZE_SCHEMA, and in 8i and above - DBMS_STATS.GATHER_SCHEMA_STATS.

The analyze table can be used to create statistics for 1 table, index or cluster.
Syntax:
ANALYZE table tableName {compute|estimate|delete} statistics options
ANALYZE index indexName {compute|estimate|delete} statistics options
ANALYZE cluster clusterName {compute|estimate|delete} statistics options

ANALYZE TABLE emp COMPUTE STATISTICS;
ANALYZE TABLE emp COMPUTE STATISTICS FOR COLUMNS sal SIZE 10;
ANALYZE TABLE emp PARTITION (p1) COMPUTE STATISTICS;
ANALYZE INDEX emp_pk COMPUTE STATISTICS;

ANALYZE TABLE emp ESTIMATE STATISTICS;
ANALYZE TABLE emp ESTIMATE STATISTICS SAMPLE 500 ROWS;
ANALYZE TABLE emp ESTIMATE STATISTICS SAMPLE 15 PERCENT;
ANALYZE TABLE emp ESTIMATE STATISTICS FOR ALL COLUMNS;

ANALYZE TABLE emp DELETE STATISTICS;
ANALYZE INDEX emp_pk DELETE STATISTICS;

ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;
ANALYZE INDEX emp_pk VALIDATE STRUCTURE;
ANALYZE CLUSTER emp_custs VALIDATE STRUCTURE CASCADE;

ANALYZE TABLE emp VALIDATE REF UPDATE;
ANALYZE TABLE emp LIST CHAINED ROWS INTO cr;

Note: Do not use the COMPUTE and ESTIMATE clauses of ANALYZE statement to collect optimizer statistics. These clauses are supported solely for backward compatibility and may be removed in a future release. The DBMS_STATS package collects a broader, more accurate set of statistics, and gathers statistics more efficiently.

We may continue to use ANALYZE statement to for other purposes not related to optimizer statistics collection:

To use the VALIDATE or LIST CHAINED ROWS clauses
To collect information on free list blocks
To sample a number (rather than a percentage) of rows

DBMS_UTILITY
The DBMS_UTILITY package can be used to gather statistics for a whole schema or database. With DBMS_UTILITY.ANALYZE_SCHEMA you can gather all the statistics for all the tables, clusters and indexes of a schema. Both methods follow the same format as the ANALYZE statement:

EXEC DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');
EXEC DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE',ESTIMATE_ROWS=>100);
EXEC DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE',ESTIMATE_PERCENT=>25);
EXEC DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','DELETE');EXEC DBMS_UTILITY.ANALYZE_DATABASE('COMPUTE');
EXEC DBMS_UTILITY.ANALYZE_DATABASE('ESTIMATE',ESTIMATE_ROWS=>100);
EXEC DBMS_UTILITY.ANALYZE_DATABASE('ESTIMATE',ESTIMATE_PERCENT=>15);

DBMS_STATS
The DBMS_STATS package was introduced in Oracle 8i and is Oracles preferred method of gathering object statistics. Oracle list a number of benefits to using it including parallel execution, long term storage of statistics and transfer of statistics between servers. This PL/SQL package is also used to modify, view, export, import, and delete statistics. It follows a similar format to the other methods.

The DBMS_STATS package can gather statistics on table and indexes, and well as individual columns and partitions of tables. It does not gather cluster statistics; however, we can use DBMS_STATS to gather statistics on the individual tables instead of the whole cluster.

When we generate statistics for a table, column, or index, if the data dictionary already contains statistics for the object, then Oracle updates the existing statistics. The older statistics are saved and can be restored later if necessary.

Procedures in the DBMS_STATS package for gathering statistics on database objects:

Procedure	Collects
GATHER_INDEX_STATS	Index statistics
GATHER_TABLE_STATS	Table, column, and index statistics
GATHER_SCHEMA_STATS	Statistics for all objects in a schema
GATHER_DICTIONARY_STATS	Statistics for all dictionary objects
GATHER_DATABASE_STATS	Statistics for all objects in a database

EXEC DBMS_STATS.GATHER_DATABASE_STATS;
EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>20);

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname, estimate_percent, block_sample, method_opt, degree, granularity, cascade, stattab, statid, options, statown, no_invalidate, gather_temp, gather_fixed);
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'MRT');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',ESTIMATE_PERCENT=>10);

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP',ESTIMATE_PERCENT=>15);

EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT','EMP_PK');
EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT','EMP_PK',ESTIMATE_PERCENT=>15);

This package also gives us the ability to delete statistics:
EXEC DBMS_STATS.DELETE_DATABASE_STATS;
EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');
EXEC DBMS_STATS.DELETE_TABLE_STATS('SCOTT','EMP');
EXEC DBMS_STATS.DELETE_INDEX_STATS('SCOTT','EMP_PK');
EXEC DBMS_STATS.DELETE_PENDING_STATS('SH','SALES');

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'"DWH"',OPTIONS=>'GATHER AUTO');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'PERFSTAT',CASCADE=>TRUE);

When gathering statistics on system schemas, we can use the procedure DBMS_STATS.GATHER_DICTIONARY_STATS. This procedure gathers statistics for all system schemas, including SYS and SYSTEM, and other optional schemas, such as CTXSYS and DRSYS.

Statistics Gathering Using Sampling
The statistics-gathering operations can utilize sampling to estimate statistics. Sampling is an important technique for gathering statistics. Gathering statistics without sampling requires full table scans and sorts of entire tables. Sampling minimizes the resources necessary to gather statistics.

Sampling is specified using the ESTIMATE_PERCENT argument to the DBMS_STATS procedures. While the sampling percentage can be set to any value, Oracle Corporation recommends setting the ESTIMATE_PERCENT parameter of the DBMS_STATS gathering procedures to DBMS_STATS.AUTO_SAMPLE_SIZE to maximize performance gains while achieving necessary statistical accuracy. AUTO_SAMPLE_SIZE lets Oracle determine the best sample size necessary for good statistics, based on the statistical property of the object. Because each type of statistics has different requirements, the size of the actual sample taken may not be the same across the table, columns, or indexes. For example, to collect table and column statistics for all tables in the SCOTT schema with auto-sampling, you could use:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'SCOTT', ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE);

When the ESTIMATE_PERCENT parameter is manually specified, the DBMS_STATS gathering procedures may automatically increase the sampling percentage if the specified percentage did not produce a large enough sample. This ensures the stability of the estimated values by reducing fluctuations.
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'SCOTT',ESTIMATE_PERCENT=>25);

Parallel Statistics Gathering
The statistics-gathering operations can run either serially or in parallel. The degree of parallelism can be specified with the DEGREE argument to the DBMS_STATS gathering procedures. Parallel statistics gathering can be used in conjunction with sampling. Oracle recommends setting the DEGREE parameter to DBMS_STATS.AUTO_DEGREE. This setting allows Oracle to choose an appropriate degree of parallelism based on the size of the object and the settings for the parallel-related init.ora parameters.

Note that certain types of index statistics are not gathered in parallel, including cluster indexes, domain indexes, and bitmap join indexes.

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'SCOTT', ESTIMATE_PERCENT=> DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT=> 'FOR ALL COLUMNS SIZE AUTO',DEGREE=>7);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>’DWH, METHOD_OPT=>’FOR ALL COLUMNS SIZE AUTO’,DEGREE=>6,ESTIMATE_PERCENT=>5, NO_INVALIDATE=>FALSE);

Statistics on Partitioned Objects
For partitioned tables and indexes, DBMS_STATS can gather separate statistics for each partition, as well as global statistics for the entire table or index. Similarly, for composite partitioning, DBMS_STATS can gather separate statistics for subpartitions, partitions, and the entire table or index. The type of partitioning statistics to be gathered is specified in the GRANULARITY argument to the DBMS_STATS gathering procedures.

Depending on the SQL statement being optimized, the optimizer can choose to use either the partition (or subpartition) statistics or the global statistics. Both types of statistics are important for most applications, and Oracle recommends setting the GRANULARITY parameter to AUTO to gather both types of partition statistics.

Column Statistics and Histograms
When gathering statistics on a table, DBMS_STATS gathers information about the data distribution of the columns within the table. The most basic information about the data distribution is the maximum value and minimum value of the column. However, this level of statistics may be insufficient for the optimizer's needs if the data within the column is skewed. For skewed data distributions, histograms can also be created as part of the column statistics to describe the data distribution of a given column.

Histograms are specified using the METHOD_OPT argument of the DBMS_STATS gathering procedures. Oracle recommends setting the METHOD_OPT to FOR ALL COLUMNS SIZE AUTO. With this setting, Oracle automatically determines which columns require histograms and the number of buckets (size) of each histogram. You can also manually specify which columns should have histograms and the size of each histogram.

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','SALES',method_opt=>'FOR COLUMNS (empno, deptno)');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','SALES',method_op =>'FOR COLUMNS (sal+comm)');

Note: If you need to remove all rows from a table when using DBMS_STATS, use TRUNCATE instead of dropping and re-creating the same table. When a table is dropped, workload information used by the auto-histogram gathering feature and saved statistics history used by the RESTORE_*_STATS procedures will be lost. Without this data, these features will not function properly.

Determining Stale Statistics
Statistics must be regularly gathered on database objects as those database objects are modified over time. In order to determine whether or not given database object needs new database statistics, Oracle provides a table monitoring facility. This monitoring is enabled by default when STATISTICS_LEVEL is set to TYPICAL or ALL. Monitoring tracks the approximate number of INSERTs, UPDATEs, and DELETEs for that table, as well as whether the table has been truncated, since the last time statistics were gathered. The information about changes of tables can be viewed in the USER_TAB_MODIFICATIONS view. Following a data-modification, there may be a few minutes delay while Oracle propagates the information to this view. Use the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the outstanding monitored information kept in the memory.

-- Table level
ALTER TABLE emp NOMONITORING;
ALTER TABLE emp MONITORING;

-- Schema level
EXEC DBMS_STATS.alter_schema_tab_monitoring('SCOTT', TRUE);
EXEC DBMS_STATS.alter_schema_tab_monitoring('SCOTT', FALSE);

-- Database level
EXEC DBMS_STATS.alter_database_tab_monitoring(TRUE);
EXEC DBMS_STATS.alter_database_tab_monitoring(FALSE);

The GATHER_DATABASE_STATS or GATHER_SCHEMA_STATS procedures gather new statistics for tables with stale statistics when the OPTIONS parameter is set to GATHER STALE or GATHER AUTO. If a monitored table has been modified more than 10%, then these statistics are considered stale and gathered again.

User-defined Statistics
You can create user-defined optimizer statistics to support user-defined indexes and functions. When you associate a statistics type with a column or domain index, Oracle calls the statistics collection method in the statistics type whenever statistics are gathered for database objects.

You should gather new column statistics on a table after creating a function-based index, to allow Oracle to collect column statistics equivalent information for the expression. This is done by calling the statistics-gathering procedure with the METHOD_OPT argument set to FOR ALL HIDDEN COLUMNS.When to Gather Statistics
When gathering statistics manually, we not only need to determine how to gather statistics, but also when and how often to gather new statistics.

For an application in which tables are being incrementally modified, we may only need to gather new statistics every week or every month. The simplest way to gather statistics in these environments is to use a script or job scheduling tool to regularly run the GATHER_SCHEMA_STATS and GATHER_DATABASE_STATS procedures. The frequency of collection intervals should balance the task of providing accurate statistics for the optimizer against the processing overhead incurred by the statistics collection process.

For tables which are being substantially modified in batch operations, such as with bulk loads, statistics should be gathered on those tables as part of the batch operation. The DBMS_STATS procedure should be called as soon as the load operation completes.

For partitioned tables, there are often cases in which only a single partition is modified. In those cases, statistics can be gathered only on those partitions rather than gathering statistics for the entire table. However, gathering global statistics for the partitioned table may still be necessary.

Transferring Statistics between databases
It is possible to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. First the statistics must be collected into a statistics table. It can be very handy to use production statistics on development database, so that we can forecast the optimizer behaviour.

Statistics can be exported and imported from the data dictionary to user-owned tables. This enables you to create multiple versions of statistics for the same schema. It also enables you to copy statistics from one database to another database. You may want to do this to copy the statistics from a production database to a scaled-down test database.

Note: Exporting and importing statistics is a distinct concept from the EXP and IMP utilities of the database. The DBMS_STATS export and import packages do utilize IMP and EXP dumpfiles.

Before exporting statistics, you first need to create a table for holding the statistics. This statistics table is created using the procedure DBMS_STATS.CREATE_STAT_TABLE. After this table is created, then you can export statistics from the data dictionary into your statistics table using the DBMS_STATS.EXPORT_*_STATS procedures. The statistics can then be imported using the DBMS_STATS.IMPORT_*_STATS procedures.

Note that the optimizer does not use statistics stored in a user-owned table. The only statistics used by the optimizer are the statistics stored in the data dictionary. In order to have the optimizer use the statistics in user-owned tables, you must import those statistics into the data dictionary using the statistics import procedures.

In order to move statistics from one database to another, you must first export the statistics on the first database, then copy the statistics table to the second database, using the EXP and IMP utilities or other mechanisms, and finally import the statistics into the second database.

Note: The EXP and IMP utilities export and import optimizer statistics from the database along with the table. One exception is that statistics are not exported with the data if a table has columns with system-generated names.

In the following example the statistics for the APPSCHEMA user are collected into a new table, STATS_TAB, which is owned by DBASCHEMA:

1. Create the statistics table.
EXEC DBMS_STATS.CREATE_STAT_TABLE(ownname =>'SCHEMA_NAME', stat_tab => 'STATS_TABLE', tblspace => 'STATS_TABLESPACE');

SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE('DBASCHEMA','STATS_TAB');

2. Export statistics to statistics table.
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('ORIGINAL_SCHEMA', 'STATS_TABLE', NULL, 'STATS_TABLE_OWNER');

SQL> EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('APPSCHEMA','STATS_TAB',NULL, 'DBASCHEMA');
(or)
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS(OWNNAME=>'APPSCHEMA', STATTAB=>'STAT_TAB',STATID=>'030610',STATOWN=>'DBASCHEMA');

3. This table can be transferred to another server using any one of the below methods.
SQLPlus Copy:
SQL> insert into dbaschema.stats_tab select * from dbaschema.stats_tab@source;

Export/Import:
exp file=stats.dmp log=stats_exp.log tables=dbaschema.stats_tab
imp file=stats.dmp log=stats_imp.log

Datapump:
expdp directory=dpump_dir dumpfile=stats.dmp logfile=stats_exp.log tables= dbaschema.stats_tab
impdp directory=dpump_dir dumpfile=stats.dmp logfile=stats_imp.log

4. Import statistics into the data dictionary.
EXEC DBMS_STATS.IMPORT_SCHEMA_STATS('NEW_SCHEMA', 'STATS_TABLE', NULL, 'SYSTEM');

SQL> EXEC DBMS_STATS.IMPORT_SCHEMA_STATS('APPSCHEMA','STATS_TAB',NULL, 'DBASCHEMA');
(or)
EXEC DBMS_STATS.IMPORT_SCHEMA_STATS(OWNNAME=>'APPSCHEMA', STATTAB=>'STAT_TAB',STATID=>'030610',STATOWN=>'DBASCHEMA');

5. Drop the statistics table (optional step).
EXEC DBMS_STATS.DROP_STAT_TABLE('SYSTEM','STATS_TABLE');
SQL> EXEC DBMS_STATS.DROP_STAT_TABLE('DBASCHEMA','STATS_TAB');

Getting top-quality stats
Because Oracle9i schema statistics work best with external system load, we like to schedule a valid sample (using dbms_stats.auto_sample_size) during regular working hours. For example, here we refresh statistics using the "auto" option which works with the table monitoring facility to only re-analyze those Oracle tables that have experienced more than a 10% change in row content:
begin
dbms_stats.gather_schema_stats(ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 7);
end;
/

Optimizer Hints
ALL_ROWS
FIRST_ROWS
FIRST_n_ROWS
APPEND
FULL
INDEX
DYNAMIC_SAMPLING
BYPASS_RECURSIVE_CHECK
BYPASS_RECURSIVE_CHECK APPEND

Examples:
SELECT /*+ ALL_ROWS */ empid, last_name, sal FROM emp;
SELECT /*+ FIRST_ROWS */ * FROM emp;
SELECT /*+ FIRST_20_ROWS */ * FROM emp;
SELECT /*+ FIRST_ROWS(100) */ empid, last_name, sal FROM emp;

System Statistics
System statistics describe the system's hardware characteristics, such as I/O and CPU performance and utilization, to the query optimizer. When choosing an execution plan, the optimizer estimates the I/O and CPU resources required for each query. System statistics enable the query optimizer to more accurately estimate I/O and CPU costs, enabling the query optimizer to choose a better execution plan.

When Oracle gathers system statistics, it analyzes system activity in a specified time period (workload statistics) or simulates a workload (noworkload statistics). The statistics are collected using the DBMS_STATS.GATHER_SYSTEM_STATS procedure. Oracle highly recommends that you gather system statistics.

Note: You must have DBA privileges or GATHER_SYSTEM_STATISTICS role to update dictionary system statistics.

EXEC DBMS_STATS.GATHER_SYSTEM_STATS (interval=>720, stattab=>'mystats', statid=>'OLTP');
EXEC DBMS_STATS.IMPORT_SYSTEM_STATS('mystats', 'OLTP');Unlike table, index, or column statistics, Oracle does not invalidate already parsed SQL statements when system statistics get updated. All new SQL statements are parsed using new statistics.

These options better facilitate the gathering process to the physical database and workload: when workload system statistics are gathered, noworkload system statistics will be ignored. Noworkload system statistics are initialized to default values at the first database startup.

Workload Statistics
Workload statistics, introduced in Oracle 9i, gather single and multiblock read times, mbrc, CPU speed (cpuspeed), maximum system throughput, and average slave throughput. The sreadtim, mreadtim, and mbrc are computed by comparing the number of physical sequential and random reads between two points in time from the beginning to the end of a workload. These values are implemented through counters that change when the buffer cache completes synchronous read requests. Since the counters are in the buffer cache, they include not only I/O delays, but also waits related to latch contention and task switching. Workload statistics thus depend on the activity the system had during the workload window. If system is I/O bound—both latch contention and I/O throughput—it will be reflected in the statistics and will therefore promote a less I/O intensive plan after the statistics are used. Furthermore, workload statistics gathering does not generate additional overhead.

In release 9.2, maximum I/O throughput and average slave throughput were added to set a lower limit for a full table scan (FTS).
To gather workload statistics, either:

Run the dbms_stats.gather_system_stats('start') procedure at the beginning of the workload window, then the dbms_stats.gather_system_stats('stop') procedure at the end of the workload window.
Run dbms_stats.gather_system_stats('interval', interval=>N) where N is the number of minutes when statistics gathering will be stopped automatically.

To delete system statistics, run dbms_stats.delete_system_stats(). Workload statistics will be deleted and reset to the default noworkload statistics.

Noworkload Statistics
Noworkload statistics consist of I/O transfer speed, I/O seek time, and CPU speed (cpuspeednw). The major difference between workload statistics and noworkload statistics lies in the gathering method.

Noworkload statistics gather data by submitting random reads against all data files, while workload statistics uses counters updated when database activity occurs. isseektim represents the time it takes to position the disk head to read data. Its value usually varies from 5 ms to 15 ms, depending on disk rotation speed and the disk or RAID specification. The I/O transfer speed represents the speed at which one operating system process can read data from the I/O subsystem. Its value varies greatly, from a few MBs per second to hundreds of MBs per second. Oracle uses relatively conservative default settings for I/O transfer speed.

In Oracle 10g, Oracle uses noworkload statistics and the CPU cost model by default. The values of noworkload statistics are
initialized to defaults at the first instance startup:
ioseektim = 10ms
iotrfspeed = 4096 bytes/ms
cpuspeednw = gathered value, varies based on system

If workload statistics are gathered, noworkload statistics will be ignored and Oracle will use workload statistics instead. To gather noworkload statistics, run dbms_stats.gather_system_stats() with no arguments. There will be an overhead on the I/O system during the gathering process of noworkload statistics. The gathering process may take from a few seconds to several minutes, depending on I/O performance and database size.

The information is analyzed and verified for consistency. In some cases, the value of noworkload statistics may remain its default value. In such cases, repeat the statistics gathering process or set the value manually to values that the I/O system has according to its specifications by using the dbms_stats.set_system_stats procedure.

Managing Statistics
Restoring Previous Versions of Statistics
Whenever statistics in dictionary are modified, old versions of statistics are saved automatically for future restoring. Statistics can be restored using RESTORE procedures of DBMS_STATS package. These procedures use a time stamp as an argument and restore statistics as of that time stamp. This is useful in case newly collected statistics leads to some sub-optimal execution plans and the administrator wants to revert to the previous set of statistics.There are dictionary views that display the time of statistics modifications. These views are useful in determining the time stamp to be used for statistics restoration.

Catalog view DBA_OPTSTAT_OPERATIONS contain history of statistics operations performed at schema and database level using DBMS_STATS.
The views *_TAB_STATS_HISTORY views (ALL, DBA, or USER) contain a history of table statistics modifications.

The old statistics are purged automatically at regular intervals based on the statistics history retention setting and the time of the recent analysis of the system. Retention is configurable using the ALTER_STATS_HISTORY_RETENTION procedure of DBMS_STATS. The default value is 31 days, which means that you would be able to restore the optimizer statistics to any time in last 31 days.

Automatic purging is enabled when STATISTICS_LEVEL parameter is set to TYPICAL or ALL. If automatic purging is disabled, the old versions of statistics need to be purged manually using the PURGE_STATS procedure.

The other DBMS_STATS procedures related to restoring and purging statistics include:

PURGE_STATS: This procedure can be used to manually purge old versions beyond a time stamp.
GET_STATS_HISTORY_RENTENTION: This function can be used to get the current statistics history retention value.
GET_STATS_HISTORY_AVAILABILITY: This function gets the oldest time stamp where statistics history is available. Users cannot restore statistics to a time stamp older than the oldest time stamp.

When restoring previous versions of statistics, the following limitations apply:

RESTORE procedures cannot restore user-defined statistics.
Old versions of statistics are not stored when the ANALYZE command has been used for collecting statistics.

Note: If you need to remove all rows from a table when using DBMS_STATS, use TRUNCATE instead of dropping and re-creating the same table. When a table is dropped, workload information used by the auto-histogram gathering feature and saved statistics history used by the RESTORE_*_STATS procedures will be lost. Without this data, these features will not function properly.

Restoring Statistics versus Importing or Exporting Statistics
The functionality for restoring statistics is similar in some respects to the functionality of importing and exporting statistics. In general, you should use the restore capability when:

You want to recover older versions of the statistics. For example, to restore the optimizer behaviour to an earlier date.
You want the database to manage the retention and purging of statistics histories.

You should use EXPORT/IMPORT_*_STATS procedures when:

You want to experiment with multiple sets of statistics and change the values back and forth.
You want to move the statistics from one database to another database. For example, moving statistics from a production system to a test system.
You want to preserve a known set of statistics for a longer period of time than the desired retention date for restoring statistics.

Locking Statistics for a Table or Schema
Statistics for a table or schema can be locked. Once statistics are locked, no modifications can be made to those statistics until the statistics have been unlocked. These locking procedures are useful in a static environment in which you want to guarantee that the statistics never change.

The DBMS_STATS package provides two procedures for locking and two procedures for unlocking statistics:

LOCK_SCHEMA_STATS
LOCK_TABLE_STATS
UNLOCK_SCHEMA_STATS
UNLOCK_TABLE_STATS

EXEC DBMS_STATS.LOCK_SCHEMA_STATS('AP');
EXEC DBMS_STATS.UNLOCK_SCHEMA_STATS('AP');

Setting Statistics
We can set table, column, index, and system statistics using the SET_*_STATISTICS procedures. Setting statistics in the manner is not recommended, because inaccurate or inconsistent statistics can lead to poor performance.

Dynamic Sampling
The purpose of dynamic sampling is to improve server performance by determining more accurate estimates for predicate selectivity and statistics for tables and indexes. The statistics for tables and indexes include table block counts, applicable index block counts, table cardinalities, and relevant join column statistics. These more accurate estimates allow the optimizer to produce better performing plans.

You can use dynamic sampling to:

Estimate single-table predicate selectivities when collected statistics cannot be used or are likely to lead to significant errors in estimation.
Estimate statistics for tables and relevant indexes without statistics.
Estimate statistics for tables and relevant indexes whose statistics are too out of date to trust.

This dynamic sampling feature is controlled by the OPTIMIZER_DYNAMIC_SAMPLING parameter. For dynamic sampling to automatically gather the necessary statistics, this parameter should be set to a value of 2(default) or higher.

The primary performance attribute is compile time. Oracle determines at compile time whether a query would benefit from dynamic sampling. If so, a recursive SQL statement is issued to scan a small random sample of the table's blocks, and to apply the relevant single table predicates to estimate predicate selectivities. The sample cardinality can also be used, in some cases, to estimate table cardinality. Any relevant column and index statistics are also collected. Depending on the value of the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter, a certain number of blocks are read by the dynamic sampling query.

For a query that normally completes quickly (in less than a few seconds), we will not want to incur the cost of dynamic sampling. However, dynamic sampling can be beneficial under any of the following conditions:

A better plan can be found using dynamic sampling.
The sampling time is a small fraction of total execution time for the query.
The query will be executed many times.

Dynamic sampling can be applied to a subset of a single table's predicates and combined with standard selectivity estimates of predicates for which dynamic sampling is not done.

We control dynamic sampling with the OPTIMIZER_DYNAMIC_SAMPLING parameter, which can be set to a value from 0 to 10. The default is 2.

A value of 0 means dynamic sampling will not be done.
Increasing the value of the parameter results in more aggressive application of dynamic sampling, in terms of both the type of tables sampled (analyzed or unanalyzed) and the amount of I/O spent on sampling.

Dynamic sampling is repeatable if no rows have been inserted, deleted, or updated in the table being sampled. The parameter OPTIMIZER_FEATURES_ENABLE turns off dynamic sampling if set to a version prior to 9.2.0.

Dynamic Sampling Levels
The sampling levels are as follows if the dynamic sampling level used is from a cursor hint or from the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter:

Level 0: Do not use dynamic sampling.
Level 1: Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).
Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.
Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.
Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.

The sampling levels are as follows if the dynamic sampling level for a table is set using the DYNAMIC_SAMPLING optimizer hint:

Level 0: Do not use dynamic sampling.
Level 1: The number of blocks sampled is the default number of dynamic sampling blocks (32).
Levels 2, 3, 4, 5, 6, 7, 8, and 9: The number of blocks sampled is 2, 4, 8, 16, 32, 64, 128, or 256 times the default number of dynamic sampling blocks respectively.
Level 10: Read all blocks in the table.

Handling Missing Statistics
When Oracle encounters a table with missing statistics, Oracle dynamically gathers the necessary statistics needed by the optimizer. However, for certain types of tables, Oracle does not perform dynamic sampling. These include remote tables and external tables. In those cases and also when dynamic sampling has been disabled, the optimizer uses default values for its statistics.

Default Table Values When Statistics Are Missing

Table Statistic Default Value Used by Optimizer
Cardinality num_of_blocks * (block_size - cache_layer) / avg_row_len
Average row length 100 bytes
Number of blocks 100 or actual value based on the extent map
Remote cardinality 2000 rows
Remote average row length 100 bytes
Default Index Values When Statistics Are Missing

Index Statistic Default Value Used by Optimizer
Levels 1
Leaf blocks 25
Leaf blocks/key 1
Data blocks/key 1
Distinct keys 100
Clustering factor 800

Viewing Statistics
Statistics on Tables, Indexes and Columns
Statistics on tables, indexes, and columns are stored in the data dictionary. To view statistics in the data dictionary, query the appropriate data dictionary view (USER, ALL, or DBA). These DBA_* views include the following:

· DBA_TAB_STATISTICS
· ALL_TAB_STATISTICS
· USER_TAB_STATISTICS
· DBA_TAB_COL_STATISTICS
· ALL_TAB_COL_STATISTICS
· USER_TAB_COL_STATISTICS
· DBA_TAB_HISTOGRAMS
· ALL_TAB_HISTOGRAMS
· USER_TAB_HISTOGRAMS

· DBA_TABLES
· DBA_OBJECT_TABLES
· DBA_TAB_HISTOGRAMS
· DBA_INDEXES
· DBA_IND_STATISTICS
· DBA_CLUSTERS
· DBA_TAB_PARTITIONS
· DBA_TAB_SUBPARTITIONS
· DBA_IND_PARTITIONS
· DBA_IND_SUBPARTITIONS
· DBA_PART_COL_STATISTICS
· DBA_PART_HISTOGRAMS
· DBA_SUBPART_COL_STATISTICS
· DBA_SUBPART_HISTOGRAMS

Viewing Histograms
Column statistics may be stored as histograms. These histograms provide accurate estimates of the distribution of column data. Histograms provide improved selectivity estimates in the presence of data skew, resulting in optimal execution plans with non uniform data distributions.

Oracle uses two types of histograms for column statistics: height-balanced histograms and frequency histograms. The type of histogram is stored in the HISTOGRAM column of the *TAB_COL_STATISTICS views (USER and DBA). This column can have values of HEIGHT BALANCED, FREQUENCY, or NONE.

Height-Balanced Histograms
In a height-balanced histogram, the column values are divided into bands so that each band contains approximately the same number of rows. The useful information that the histogram provides is where in the range of values the endpoints fall. Height-balanced histograms can be viewed using the *TAB_HISTOGRAMS tables.

Example for Viewing Height-Balanced Histogram Statistics
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'OE', TABNAME => 'INVENTORIES',
METHOD_OPT => 'FOR COLUMNS SIZE 10 QUANTITY_ON_HAND');
END;
/

SELECT COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS, HISTOGRAM
FROM USER_TAB_COL_STATISTICS
WHERE TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'QUANTITY_ON_HAND';

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
QUANTITY_ON_HAND 237 10 HEIGHT BALANCED

SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM USER_HISTOGRAMS
WHERE TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'QUANTITY_ON_HAND'
ORDER BY ENDPOINT_NUMBER;

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
0 0
1 27
2 42
3 57
4 74
5 98
6 123
7 149
8 175
9 202
10 353

In the query output, one row corresponds to one bucket in the histogram.

Frequency Histograms
In a frequency histogram, each value of the column corresponds to a single bucket of the histogram. Each bucket contains the number of occurrences of that single value. Frequency histograms are automatically created instead of height-balanced histograms when the number of distinct values is less than or equal to the number of histogram buckets specified. Frequency histograms can be viewed using the *TAB_HISTOGRAMS tables.

Example for Viewing Frequency Histogram Statistics
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'OE', TABNAME => 'INVENTORIES',
METHOD_OPT => 'FOR COLUMNS SIZE 20 WAREHOUSE_ID');
END;
/

SELECT COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS, HISTOGRAM
FROM USER_TAB_COL_STATISTICS
WHERE TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'WAREHOUSE_ID';

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
WAREHOUSE_ID 9 9 FREQUENCY

SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM USER_HISTOGRAMS
WHERE TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'WAREHOUSE_ID'
ORDER BY ENDPOINT_NUMBER;

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
36 1
213 2
261 3
370 4
484 5
692 6
798 7
984 8
1112 9

Issues

Exclude dataload tables from your regular stats gathering, unless you know they will be full at the time that stats are gathered.
Gathering stats for the SYS schema can make the system run slower, not faster.
Gathering statistics can be very resource intensive for the server so avoid peak workload times or gather stale stats only.
Even if scheduled, it may be necessary to gather fresh statistics after database maintenance or large data loads.
If a table goes from 1 row to 200 rows, that's a significant change. When a table goes from 100,000 rows to 150,000 rows, that's not a terribly significant change. When a table goes from 1000 rows all with identical values in commonly-queried column X to 1000 rows with nearly unique values in column X, that's a significant change.

Statistics store information about item counts and relative frequencies. Things that will let it "guess" at how many rows will match a given criteria. When it guesses wrong, the optimizer can pick a very suboptimal query plan.

Source:Internet

RMAN (Recovery Manager) Commands

2010-04-12T15:26:00.115+05:30

rman command
Start RMAN from the OS command line.

rman
[ TARGET [=] ['] [userid][/[password]][@net_service_name] [']
| {CATALOG [=] ['] [userid][/[password]][@net_service_name] [']
| LOG [=] [']filename['] [APPEND]
...
]...

$ rman
$ rman NOCATALOG
$ rman TARGET SYS/pwd@target
$ rman TARGET SYS/pwd@target NOCATALOG
$ rman TARGET SYS/pwd@target LOG $ORACLE_HOME/dbs/my_log.log APPEND
$ rman CATALOG rman/pwd@catdb
$ rman TARGET=SYS/pwd@target CATALOG=rman/pwd@cat
$ rman TARGET / CATALOG rman/rman@cat
$ rman TARGET / SCRIPT dwh LOG /tmp/dwh.log
$ rman PIPE newpipe TARGET / TIMEOUT 90
$ rman @/my_dir/my_commands.txt
$ rman @backup_ts_generic.rman "/tmp" USERS
$ rman CMDFILE=backup_ts_users.rman
$ rman TARGET / @backup_db.rman
$ rman TARGET / CATALOG rman/pwd@cat CMDFILE cmdfile.rcv LOG outfile.txt
$ rman TARGET / CATALOG rman/pwd@cat DEBUG TRACE trace.log
$ rman TARGET SYS/pwd@prod CATALOG rman/rman@rcat @'/oracle/dbs/whole.rcv'
$ rman TARGET user/pwd CMDFILE=takefulldb.cmd @@takefulldb.cmd
$ rman CHECKSYNTAX @'/tmp/backup_db.cmd'
$ rman MSGNO
$ rman | tee rman.log
$ rman help=yes

@ (at sign) Run a command file.
@@ (double at sign) Run a command file in the same directory as another command file that is currently running. The @@ command differs from the @ command only when run from within a command file.
RMAN> @backup_db.rman
RMAN> @/my_dir/my_command_file.txt
RMAN> @/tmp/bkup_db.rman whole_db
RMAN> @backup_ts_generic.rman "/tmp" $1
RMAN> RUN {@backup_db.rman}

CONNECT command
Establish a connection between RMAN and a target, auxiliary, or recovery catalog database.
RMAN> CONNECT TARGET;
RMAN> CONNECT TARGET /
RMAN> CONNECT TARGET sys@tgt;
RMAN> CONNECT TARGET sys/pwd@tgt;
RMAN> CONNECT CATALOG rman@catdb;
RMAN> CONNECT CATALOG rman/pwd@catdb;
RMAN> CONNECT AUXILIARY /
RMAN> CONNECT AUXILIARY rman@auxdb;
RMAN> CONNECT AUXILIARY rman/pwd@auxdb;

CREATE CATALOG command
Create the schema for the recovery catalog.
RMAN> CREATE CATALOG;
RMAN> CREATE CATALOG TABLESPACE rmants;
RMAN> CREATE VIRTUAL CATALOG; -- 11g R1
SQL> EXEC rman.DBMS_RCVCAT.CREATE_VIRTUAL_CATALOG; -- 11g R1

DROP CATALOG command
Remove the schema from the recovery catalog.
RMAN> DROP CATALOG;

RESYNC CATALOG command
Perform a full resynchronization, which creates a snapshot control file and then copies any new or changed information from that snapshot control file to the recovery catalog.
RMAN> RESYNC CATALOG;
RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME prod_db;
RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME ALL;

UPGRADE CATALOG command
Upgrade the recovery catalog schema from an older version to the version required by the RMAN executable.
RMAN> UPGRADE CATALOG;

IMPORT CATALOG command
Import the metadata from one recovery catalog into another recovery catalog.
RMAN> IMPORT CATALOG cat@srcdb;
RMAN> IMPORT CATALOG rcat@inst DBID=2871507123;
RMAN> IMPORT CATALOG cat@srcdb DBID=1844750987, 61738563;
RMAN> IMPORT CATALOG cat@srcdb DB_NAME=prod2;
RMAN> IMPORT CATALOG cat@srcdb DB_NAME=prod3, prod4;
RMAN> IMPORT CATALOG rman/rman@catdb1 DB_NAME=prod1 NO UNREGISTER;

REGISTER command
Register the target database in the recovery catalog.
RMAN> REGISTER DATABASE;
RMAN> REGISTER CATALOG;
RMAN> REGISTER CATALOG TABLESPACE tbs-name;

UNREGISTER command
Unregister a database from the recovery catalog.
RMAN> UNREGISTER DATABASE;
RMAN> UNREGISTER DATABASE NOPROMPT;
RMAN> UNREGISTER DATABASE prod1;
RMAN> UNREGISTER DATABASE prod2 NOPROMPT;
RMAN> UNREGISTER DB_UNIQUE_NAME prod2;
RMAN> UNREGISTER DB_UNIQUE_NAME prod1 NOPROMPT;
RMAN> UNREGISTER DB_UNIQUE_NAME prod2 INCLUDING BACKUPS;
RMAN> UNREGISTER DB_UNIQUE_NAME prod3 INCLUDING BACKUPS NOPROMPT;

GRANT command
Grant privileges to a recovery catalog user.
RMAN> GRANT CATALOG FOR DATABASE prod1 TO vpc1; -- 11g R1
RMAN> GRANT REGISTER DATABASE TO bckop2;
RMAN> GRANT RECOVERY_CATALOG_OWNER TO rmanop1, rmanop3;

REVOKE command
Revoke privileges from a recovery catalog user.
RMAN> REVOKE CATALOG FOR DATABASE prod1 FROM vpc1; -- 11g R1
RMAN> REVOKE REGISTER DATABASE FROM bckop2;
RMAN> REVOKE RECOVERY_CATALOG_OWNER FROM bckop;

RESET DATABASE command
Inform RMAN that the SQL statement ALTER DATABASE OPEN RESETLOGS has been executed and that a new incarnation of the target database has been created, or reset the target database to a prior incarnation.
RMAN> RESET DATABASE TO INCARNATION 3;

STARTUP command
Startup the target database. This command is equivalent to the SQL*Plus STARTUP command.
RMAN> STARTUP;
RMAN> STARTUP PFILE=’/u01/app/oracle/admin/pfile/initsid.ora’
RMAN> STARTUP NOMOUNT;
RMAN> STARTUP MOUNT;
RMAN> STARTUP FORCE;
RMAN> STARTUP FORCE DBA;
RMAN> STARTUP FORCE DBA PFILE=c:\Oracle\Admin\pfile\init.ora;
RMAN> STARTUP FORCE NOMOUNT;
RMAN> STARTUP FORCE MOUNT DBA PFILE=/tmp/inittrgt.ora;

SHUTDOWN command
Shutdown the target database. This command is equivalent to the SQL*Plus SHUTDOWN command.
RMAN> SHUTDOWN;
RMAN> SHUTDOWN NORMAL;
RMAN> SHUTDOWN TRANSACTIONAL;
RMAN> SHUTDOWN IMMEDIATE;
RMAN> SHUTDOWN ABORT;

ALTER DATABASE command
Mount or open a database.
RMAN> ALTER DATABASE MOUNT;
RMAN> ALTER DATABASE OPEN;
RMAN> ALTER DATABASE OPEN RESETLOGS;

SHOW command
Display the current CONFIGURE settings.

SHOW
{ RETENTION POLICY
| BACKUP OPTIMIZATION
| [DEFAULT] DEVICE TYPE
| CONTROLFILE AUTOBACKUP [FORMAT]
| [AUXILIARY] CHANNEL [FOR DEVICE TYPE deviceSpecifier]
| MAXSETSIZE
| DATAFILE BACKUP COPIES
| ARCHIVELOG [BACKUP COPIES|DELETION POLICY]
| AUXNAME
| EXCLUDE
| ENCRYPTION {ALGORITHM | FOR [DATABASE|TABLESPACE]}
| COMPRESSION ALGORITHM
| SNAPSHOT CONTROLFILE NAME
| DB_UNIQUE_NAME
| ALL
} FOR [DB_UNIQUE_NAME [‘db_unique_name’|ALL]];

RMAN> SHOW ALL;
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 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR SBT TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR SBT TO 1; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE; # default -- 11g R2
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '.../dbs/snapcf_sid.f'; # default

%F = dbid, day, month, year and sequence
%U = %u_%p_%c
%u = eight characters of the backup set and time ...
%p = piece number within the backupset
%c = copy number of the backup piece ...

RMAN> SHOW RETENTION POLICY;
RMAN> SHOW RETENTION POLICY FOR DB_UNIQUE_NAME ALL;
RMAN> SHOW DEVICE TYPE;
RMAN> SHOW DEVICE TYPE FOR DB_UNIQUE_NAME prod3;
RMAN> SHOW DEFAULT DEVICE TYPE;
RMAN> SHOW CHANNEL;
RMAN> SHOW MAXSETSIZE;
RMAN> SHOW BACKUP OPTIMIZATION;
RMAN> SHOW SNAPSHOT CONTROLFILE NAME;
RMAN> SHOW CONTROLFILE AUTOBACKUP;
RMAN> SHOW COMPRESSION ALGORITHM;
RMAN> SHOW ENCRYPTION ALGORITHM;
RMAN> SHOW ALL FOR DB_UNIQUE_NAME ALL;
RMAN> SHOW ALL FOR DB_UNIQUE_NAME 'STANDBY';

CONFIGURE command
To configure persistent RMAN settings. These settings apply to all RMAN sessions until explicitly changed or disabled.

CONFIGURE deviceConf;
CONFIGURE backupConf;
CONFIGURE AUXNAME FOR DATAFILE datafileSpec {TO 'filename' | CLEAR};
CONFIGURE SNAPSHOT CONTROLFILE NAME {TO 'filename' | CLEAR};
CONFIGURE cfauConf;

deviceConf::=
{ DEFAULT DEVICE TYPE { TO deviceSpec | CLEAR }
| DEVICE TYPE deviceSpec { PARALLELISM integer | CLEAR }
| [AUXILIARY] CHANNEL [integer] DEVICE TYPE deviceSpec {allocOperandList|CLEAR}
}

allocOperandList::=
{ PARMS [=] 'channel_parms'
| FORMAT [=] 'format_string' [, 'format_string']...
| { MAXPIECESIZE [=] integer | RATE [=] integer } [K | M | G]
...
}...

connectStringSpec::=
['] [userid] [/[password]] [@net_service_name] [']

backupConf::=
{RETENTION POLICY {TO {RECOVERY WINDOW OF integer DAYS
| REDUNDANCY [=] integer | NONE
}
| CLEAR
}
| MAXSETSIZE {TO {integer [K | M | G]| UNLIMITED}
| CLEAR
}
| {ARCHIVELOG | DATAFILE}
BACKUP COPIES FOR DEVICE TYPE deviceSpec {TO integer | CLEAR}
| BACKUP OPTIMIZATION {ON | OFF | CLEAR}
| EXCLUDE FOR TABLESPACE tablespace_name [CLEAR]
}

cfauConf::==
CONTROLFILE AUTOBACKUP {ON | OFF | CLEAR | FORMAT FOR DEVICE TYPE deviceSpec {TO 'format string'|CLEAR}}

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP OFF;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf%F';
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+BACKUP';
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
RMAN> CONFIGURE RETENTION POLICY CLEAR;
RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;
RMAN> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 2;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; --11g
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY CLEAR;
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO sbt;
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;
RMAN> CONFIGURE DEVICE TYPE sbt PARALLELISM 3;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt;
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt PARMS='ENV=mml_env_settings';
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt PARMS 'ENV=(NSR_SERVER=bksrv1)';
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt PARMS 'BLKSIZE=1048576';
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt FORMAT 'bkup_%U';
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt CLEAR;
RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE sbt CONNECT 'SYS/pwd@node2' PARMS 'ENV=(NSR_SERVER=bksrv2)';
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/tmp/%U';
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'C:\backup\df%t_s%s_s%p';
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT CLEAR;
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK DEBUG 5;
RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/backup/db_%s%d_%p';

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE BACKUP OPTIMIZATION OFF;
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/backup/snapcf_%d.f‘;
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘+FRA/snap/snapcf_%d.f‘;
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/ocfs/oradata/snapcf‘;
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/dev/sda‘;
RMAN> CONFIGURE MAXSETSIZE TO 100M;
RMAN> CONFIGURE MAXSETSIZE TO UNLIMITED;
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt MAXPIECESIZE 1G;
RMAN> CONFIGURE EXCLUDE FOR TABLESPACE example;
RMAN> CONFIGURE EXCLUDE CLEAR;
RMAN> CONFIGURE AUXNAME FOR DATAFILE 4 TO '/oracle/auxfiles/aux_4.f';
RMAN> CONFIGURE AUXNAME FOR DATAFILE 2 CLEAR;

RMAN> CONFIGURE COMPRESSION ALGORITHM 'BZIP2';
RMAN> CONFIGURE COMPRESSION ALGORITHM 'ZLIB'; --11g R1
RMAN> CONFIGURE COMPRESSION ALGORITHM 'LOW'; --11g R2,corresponds to LZO
RMAN> CONFIGURE COMPRESSION ALGORITHM 'MEDIUM'; --11g R2,corresponds to ZLIB
RMAN> CONFIGURE COMPRESSION ALGORITHM 'HIGH'; --11g R2,corresponds to unmodified BZIP2
RMAN> CONFIGURE COMPRESSION ALGORITHM 'BASIC'; --11g R2,corresponds to BZIP2

RMAN> CONFIGURE DB_UNIQUE_NAME 'standby' CONNECT IDENTIFIER 'standby_cs';
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK FOR DB_UNIQUE_NAME 'standby';
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK FOR DB_UNIQUE_NAME ALL;
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO SBT FOR DB_UNIQUE_NAME po;

SET command
Set the value of various attributes that affect RMAN behaviour for the duration of a RUN block or a session.

SET {set_rman_option [;] | set_run_option;}

set_rman_option::=
{ECHO {ON|OFF} | DBID [=] integer
| CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE deviceSpec TO 'frmt_string'

set_run_option::=
{ NEWNAME FOR DATAFILE datafileSpec TO {'filename' | NEW}
| ARCHIVELOG DESTINATION TO 'log_archive_dest'
| untilClause
| COMMAND ID TO 'string'
| CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE deviceSpec TO 'frmt_string'
...
}

ECHO - Controls whether RMAN commands are displayed in the message log.

DBID - A unique 32-bit identification number computed when the database is created. RMAN displays the DBID upon connection to the target database. We can obtain the DBID by querying V$DATABASE or RC_DATABASE.

NEWNAME FOR DATAFILE - The default name for all subsequent RESTORE or SWITCH commands that affect the specified datafile.

MAXCORRUPT FOR DATAFILE - A limit on the number of previously undetected physical block corruptions that Oracle will allow in the datafile(s).

AUTOLOCATE - Force RMAN to automatically discover which nodes of an Oracle Real Application Clusters configuration contain the backups that you want to restore.

RMAN> SET ECHO ON;
RMAN> SET ECHO OFF;
RMAN> SET DATABASE prod;
RMAN> SET DBID=4240978820;
RMAN> SET DBID 591329635;
RMAN> SET COMMAND ID TO 'rman';
RMAN> SET MAXCORRUPT FOR DATABASE TO 2;
RMAN> SET MAXCORRUPT FOR DATAFILE 1 TO 2;
RMAN> SET BACKUP COPIES = 2;

RMAN> SET NEWNAME FOR DATABASE TO '/oradata1/%b';
RMAN> SET NEWNAME FOR TABLESPACE users TO '/oradata2/%U';
RMAN> SET NEWNAME FOR DATAFILE 1 to ‘/oradata/system01.dbf’;
RMAN> SET NEWNAME FOR DATAFILE '/disk7/tbs11.f' TO '/disk9/tbs11.f';
RMAN> SET NEWNAME FOR TEMPFILE 1 TO '/newdisk/dbs/temp1.f';

RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE sbt TO 'cf_%F';
RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf_%F.bak';
RMAN> SET UNTIL TIME ’04-23-2010:23:50:04’;
RMAN> SET ARCHIVELOG DESTINATION TO '/oracle/temp_restore';
RMAN> SET COMPRESSION ALGORITHM 'LOW';
RMAN> SET COMPRESSION ALGORITHM 'LOW' OPTIMIZE FOR LOAD FALSE;
RMAN> SET COMPRESSION ALGORITHM 'MEDIUM';
RMAN> SET COMPRESSION ALGORITHM 'HIGH';

BACKUP command
Backup database files, copies of database files, archived logs, or backup sets.

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

Options::=
[backupOperand [backupOperand]...] backupSpec [backupSpec]...
[PLUS ARCHIVELOG [backupSpecOperand [backupSpecOperand]...]];

backupOperand::=
{ FORMAT [=] 'format_string' [, 'format_string']...
| CHANNEL ['] channel_id [']
| CUMULATIVE
| MAXSETSIZE [=] integer [K | M | G]
| TAG [=] ['] tag_name [']
| keepOption
| SKIP {OFFLINE | READONLY | INACCESSIBLE}
| VALIDATE
| NOT BACKED UP [SINCE TIME [=] 'date_string']
| COPIES [=] integer
| DEVICE TYPE deviceSpecifier
...
}

backupSpec::=
[(]
{ BACKUPSET
{ {ALL | completedTimeSpec } | primary_key) [, primary_key]... }
| COPY OF { DATABASE
| TABLESPACE ['] tablespace_name ['] [, ['] tablespace_name [']]...
| DATAFILE datafileSpec [, datafileSpec]...
}
| DATAFILE datafileSpec [, datafileSpec]...
| DATAFILECOPY 'filename' [, 'filename']...
| DATAFILECOPY FROM TAG [=] ['] tag_name ['] [, ['] tag_name [']]...
| DATAFILECOPY { ALL | LIKE 'string_pattern' }
| TABLESPACE ['] tablespace_name ['] [, ['] tablespace_name [']]...
| DATABASE
| archivelogRecordSpecifier
| CURRENT CONTROLFILE [FOR STANDBY]
| CONTROLFILECOPY 'filename'
| SPFILE
}
[backupSpecOperand [backupSpecOperand]...]

backupSpecOperand::=
{ FORMAT [=] 'format_string' [, 'format_string']...
| CHANNEL ['] channel_id [']
| CUMULATIVE
| MAXSETSIZE [=] integer [K | M | G]
| TAG [=] ['] tag_name [']
| keepOption
| SKIP {OFFLINE | READONLY | INACCESSIBLE}
| NOT BACKED UP [SINCE TIME [=] 'date_string' | integer TIMES]
| DELETE [ALL] INPUT
...
}

RMAN> BACKUP DATABASE;
RMAN> BACKUP DATABASE TAG=’test backup’;
RMAN> BACKUP DATABASE COMMENT=’full backup’;
RMAN> BACKUP TAG 'weekly_full_db_bkup' DATABASE MAXSETSIZE 10M;
RMAN> BACKUP MAXSETSIZE 500M DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP DURATION 00:60 DATABASE;
RMAN> BACKUP DURATION 00:30 MINIMIZE TIME DATABASE;
RMAN> BACKUP DURATION 00:45 MINIMIZE LOAD DATABASE;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP DATABASE KEEP FOREVER;
RMAN> BACKUP DATABASE KEEP UNTIL TIME=’SYSDATE+30’;
RMAN> BACKUP DATABASE UNTIL 'SYSDATE+365' NOLOGS;
RMAN> BACKUP DATABASE NOEXCLUDE;
RMAN> BACKUP DATABASE NOEXCLUDE KEEP FOREVER TAG=’abc’;
RMAN> BACKUP DATABASE SKIP READONLY;
RMAN> BACKUP DATABASE SKIP OFFLINE;
RMAN> BACKUP DATABASE SKIP INACCESSIBLE;
RMAN> BACKUP DATABASE SKIP READONLY SKIP OFFLINE SKIP INACCESSIBLE;
RMAN> BACKUP DATABASE FORCE; -- backup read only database also
RMAN> BACKUP DATABASE NOT BACKED UP;
RMAN> BACKUP DATABASE NOT BACKED UP SINCE TIME=’SYSDATE–3’;
RMAN> BACKUP NOT BACKED UP SINCE TIME 'SYSDATE-10' MAXSETSIZE 500M DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP DATABASE COPIES=2;
RMAN> BACKUP DATABASE FORMAT '/disk1/backups/db_%U.bck'
TAG quarterly KEEP UNTIL TIME 'SYSDATE+365' RESTORE POINT Q1FY10;
RMAN> BACKUP DEVICE TYPE DISK DATABASE;
RMAN> BACKUP DEVICE TYPE sbt DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP DEVICE TYPE sbt DATAFILECOPY FROM TAG 'latest' FORMAT 'df%f_%d';
RMAN> BACKUP DEVICE TYPE sbt ARCHIVELOG LIKE '/disk%arc%' DELETE ALL INPUT;
RMAN> BACKUP DEVICE TYPE sbt BACKUPSET COMPLETED BEFORE 'SYSDATE-14'DELETE INPUT;
RMAN> BACKUP CHECK LOGICAL DATABASE;
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;
RMAN> BACKUP VALIDATE DATABASE;
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

RMAN> BACKUP TABLESPACE test;
RMAN> BACKUP TABLESPACE system, users, tools;
RMAN> BACKUP TABLESPACE 4;
RMAN> BACKUP TABLESPACE gld PLUS ARCHIVELOG;
RMAN> BACKUP TABLESPACE invd INCLUDE CURRENT CONTROLFILE;
RMAN> BACKUP TABLESPACE appsd INCLUDE CURRENT CONTROLFILE PLUS ARCHIVELOG;
RMAN> BACKUP TABLESPACE dwh SECTION SIZE 100M;
RMAN> BACKUP SECTION SIZE 250M TABLESPACE datamart;

RMAN> BACKUP DATAFILE 1;
RMAN> BACKUP DATAFILE 3, 2, 14;
RMAN> BACKUP DATAFILE ‘/u01/data/...’;
RMAN> BACKUP DATAFILE 1 PLUS ARCHIVELOG;
RMAN> BACKUP KEEP FOREVER FORMAT '?/dbs/%U_longterm.cpy' TAG longterm_bck DATAFILE 1 DATAFILE 2;
RMAN> BACKUP SECTION SIZE 500M DATAFILE 6;

RMAN> BACKUP ARCHIVELOG ALL;
RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;
RMAN> BACKUP ARCHIVELOG LIKE '/arch%' DELETE ALL INPUT;
RMAN> BACKUP ARCHIVELOG FROM TIME ‘SYSDATE–3’;
RMAN> BACKUP ARCHIVELOG FROM SEQUENCE 100;
RMAN> BACKUP ARCHIVELOG FROM SEQUENCE 999 DELETE INPUT;
RMAN> BACKUP ARCHIVELOG NOT BACKED UP 2 TIMES;
RMAN> BACKUP ARCHIVELOG COMPLETION TIME BETWEEN 'SYSDATE-28' AND 'SYSDATE-7';
RMAN> BACKUP FORMAT='AL_%d/%t/%s/%p' ARCHIVELOG LIKE '%arc_dest%';

RMAN> BACKUP CURRENT CONTROLFILE;
OR
RMAN> SQL “ALTER DATABASE BACKUP CONTROLFILE TO ’’/u01/ .../bkctl.ctl’’ “;

RMAN> BACKUP CURRENT CONTROLFILE TO '/backup/cntrlfile.copy';
RMAN> BACKUP CONTROLFILE COPY ‘/u10/backup/control.bkp’;
RMAN> BACKUP SPFILE;
RMAN> BACKUP DEVICE TYPE sbt SPFILE ARCHIVELOG ALL;
RMAN> BACKUP DEVICE TYPE sbt DATAFILECOPY ALL NODUPLICATES;

RMAN> BACKUP RECOVERY FILES;

BACKUP set
RMAN> BACKUP BACKUPSET ALL;
RMAN> BACKUP BACKUPSET ALL FORMAT = ‘/u01/.../backup_%u.bak’;
RMAN> BACKUP BACKUPSET COMPLETED BEFORE ‘SYSDATE-3’ DELETE INPUT;
RMAN> BACKUP DEVICE TYPE sbt BACKUPSET COMPLETED BEFORE 'SYSDATE-14' DELETE INPUT;
RMAN> BACKUP COPIES 2 DEVICE TYPE sbt BACKUPSET ALL;
RMAN> BACKUP AS COMPRESSED BACKUPSET;
RMAN> BACKUP AS COMPRESSED BACKUPSET DEVICE TYPE DISK COPIES 2 DATABASE FORMAT '/disk1/db_%U', '/disk2/db_%U';
RMAN> BACKUP AS BACKUPSET DATAFILE '$ORACLE_HOME/oradata/users01.dbf','$ORACLE_HOME/oradata/tools01.dbf';
RMAN> BACKUP AS BACKUPSET DATAFILECOPY ALL;
RMAN> BACKUP AS BACKUPSET DATAFILECOPY ALL NODUPLICATES;

IMAGE copy
RMAN> BACKUP AS COPY DATABASE;
RMAN> BACKUP AS COPY COPY OF DATABASE FROM TAG 'test' CHECK LOGICAL TAG 'duptest';
RMAN> BACKUP AS COPY TABLESPACE 8;
RMAN> BACKUP AS COPY TABLESPACE test;
RMAN> BACKUP AS COPY TABLESPACE system, tools, users, undotbs;
RMAN> BACKUP AS COPY DATAFILE 1;
RMAN> BACKUP AS COPY DATAFILE 2 FORMAT '/disk2/df2.cpy' TAG my_tag;
RMAN> BACKUP AS COPY CURRENT CONTROLFILE;
RMAN> BACKUP AS COPY CURRENT CONTROLFILE FORMAT ‘/....’;
RMAN> BACKUP AS COPY ARCHIVELOG ALL;
RMAN> BACKUP AS COPY KEEP FOREVER NOLOGS CURRENT CONTROLFILE FORMAT '?/oradata/cf_longterm.cpy', DATAFILE 1 FORMAT '?/oradata/df1_longterm.cpy', DATAFILE 2 FORMAT '?/oradata/df2_longterm.cpy';
RMAN> BACKUP AS COPY DATAFILECOPY 'bar' FORMAT 'foobar';
RMAN> BACKUP AS COPY DATAFILECOPY '/disk2/df2.cpy' FORMAT '/disk1/df2.cpy';

Incremental backups
RMAN> BACKUP INCREMENTAL LEVEL=0 DATABASE;
RMAN> BACKUP INCREMENTAL LEVEL=1 DATABASE;
RMAN> BACKUP INCREMENTAL LEVEL=2 DATABASE;
RMAN> BACKUP INCREMENTAL LEVEL 2 CUMULATIVE DATABASE;
RMAN> BACKUP INCREMENTAL LEVEL 2 DATABASE;
RMAN> BACKUP INCREMENTAL LEVEL=0 DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE SKIP INACCESSIBLE DATABASE;
RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr' DATABASE;
RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'oltp' DATABASE;
RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 351986 DATABASE FORMAT '/tmp/incr_standby_%U';
RMAN> BACKUP INCREMENTAL FROM SCN 629184 DATAFILE 5 FORMAT '/tmp/ForStandby_%U' TAG 'FORSTANDBY';

RMAN> BACKUP INCREMENTAL LEVEL = --- tablespace/datafile

LIST command
Produce a detailed listing of backup sets or copies.

LIST
{ INCARNATION [OF DATABASE [[']database_name[']]]
| [EXPIRED] {listObjectSpec
[ maintQualifier | RECOVERABLE [untilClause] ]... | recordSpec}
};

listObjectSpec::=
{BACKUP [OF listObjectList] [listBackupOption] | COPY [OF listObjectList] | archivelogRecordSpecifier}

listObjectList::=
[ DATAFILE datafileSpec [, datafileSpec]...
| TABLESPACE [']tablespace_name['] [, [']tablespace_name[']]...
| archivelogRecordSpecifier
| DATABASE [SKIP TABLESPACE [']tablespace_name['] [, [']tablespace_name[']] ...]
| CONTROLFILE
| SPFILE
]...

listBackupOption::=
[[BY BACKUP] [VERBOSE] | SUMMARY | BY {BACKUP SUMMARY|FILE}]

RMAN> LIST INCARNATION;
RMAN> LIST INCARNATION OF DATABASE;
RMAN> LIST INCARNATION OF DATABASE vis;
RMAN> LIST DB_UNIQUE_NAME ALL;
RMAN> LIST DB_UNIQUE_NAME OF DATABASE;

RMAN> LIST BACKUP;
RMAN> LIST BACKUP SUMMARY;
RMAN> LIST BACKUP BY FILE;
RMAN> LIST BACKUP OF DATABASE;
RMAN> LIST BACKUP OF DATABASE BY BACKUP;
RMAN> LIST BACKUP OF TABLESPACE test SUMMARY;
RMAN> LIST BACKUP OF DATAFILE 65;
RMAN> LIST BACKUP OF DATAFILE 11 SUMMARY;
RMAN> LIST BACKUP OF CONTROLFILE;
RMAN> LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 2222;
RMAN> LIST BACKUP RECOVERABLE;
RMAN> LIST EXPIRED BACKUP;
RMAN> LIST EXPIRED BACKUP OF ARCHIVELOG ALL SUMMARY;

RMAN> LIST COPY;
RMAN> LIST COPY OF DATABASE ARCHIVELOG ALL;
RMAN> LIST COPY OF TABLESPACE appl_idx;
RMAN> LIST COPY OF DATAFILE 11, 60, 98;
RMAN> LIST COPY OF CONTROLFILE;
RMAN> LIST EXPIRED COPY;

RMAN> LIST BACKUPSET SUMMARY;
RMAN> LIST BACKUPSET 109;
RMAN> LIST BACKUPSET OF DATAFILE 1;
RMAN> LIST ARCHIVELOG;
RMAN> LIST CONTROLFILECOPY "/tmp/cntrlfile.copy";

RMAN> LIST SCRIPT NAMES;
RMAN> LIST ALL SCRIPT NAMES;
RMAN> LIST GLOBAL SCRIPT NAMES;

RMAN> LIST FAILURE; -- 11g R1
RMAN> LIST FAILURE 420 DETAIL; -- 11g R1
RMAN> LIST FAILURE ALL; -- 11g R1

REPORT command
Report backup status: database, files, and backups. Perform detailed analyses of the content of the recovery catalog.

REPORT
{{NEED BACKUP [{INCREMENTAL | DAYS} [=] integer| REDUNDANCY [=] integer | RECOVERY WINDOW OF integer DAYS)]
| UNRECOVERABLE
}
reportObject
| SCHEMA [atClause]
| OBSOLETE [obsOperandList]
}
[DEVICE TYPE deviceSpecifier [,deviceSpecifier]... ]

reportObject::=
[ DATAFILE datafileSpec [, datafileSpec]...
| TABLESPACE [']tablespace_name['] [, [']tablespace_name[']] ...
| DATABASE [SKIP TABLESPACE [']tablespace_name['] [, [']tablespace_name[']] ...]
]

atClause::=
{AT TIME [=] 'date_string' | AT SCN [=] integer|AT SEQUENCE [=] integer THREAD [=] integer
}

obsOperandList::=
[REDUNDANCY [=] integer | RECOVERY WINDOW OF integer DAYS | ORPHAN]...

RMAN> REPORT OBSOLETE;
RMAN> REPORT NEED BACKUP;
RMAN> REPORT NEED BACKUP DAYS=5;
RMAN> REPORT NEED BACKUP REDUNDANCY=3;
RMAN> REPORT NEED BACKUP RECOVERY WINDOW OF 7 DAYS;
RMAN> REPORT NEED BACKUP DATABASE;
RMAN> REPORT NEED BACKUP INCREMENTAL 1;
RMAN> REPORT UNRECOVERABLE;
RMAN> REPORT SCHEMA;
RMAN> REPORT SCHEMA AT TIME 'sysdate-20/1440';

CHANGE command
Update the status of a backup in the RMAN repository. Mark a backup piece, image copy, or archived redo log as having the status UNAVAILABLE or AVAILABLE; remove the repository record for a backup or copy; override the retention policy for a backup or copy; update the recovery catalog with the DB_UNIQUE_NAME for the target database.

CHANGE {BACKUP | COPY} [OF listObjList]
[maintQualifier [maintQualifier]...]
{AVAILABLE | UNAVAILABLE | UNCATALOG | keepOption}
[DEVICE TYPE deviceSpecifier [, deviceSpecifier]...];

CHANGE archivelogRecordSpecifier
{AVAILABLE | UNAVAILABLE | UNCATALOG | keepOption}
[DEVICE TYPE deviceSpecifier [, deviceSpecifier]...];

CHANGE recordSpec [DEVICE TYPE deviceSpecifier [, deviceSpecifier]...
{AVAILABLE | UNAVAILABLE | UNCATALOG | keepOption}
[DEVICE TYPE deviceSpecifier [, deviceSpecifier]...];

listObjList::=
[DATAFILE datafileSpec [, datafileSpec]...
| TABLESPACE ['] tablespace_name ['] [, ['] tablespace_name [']]...
| archivelogRecordSpecifier
| DATABASE [SKIP TABLESPACE [']tablespace_name['] [, [']tablespace_name[']] ...]
| CONTROLFILE
| SPFILE
]...

recordSpec::=
{{BACKUPPIECE | PROXY}
{'media_handle' [, 'media_handle']... | primary_key [, primary_key]... | TAG [=] ['] tag_name [']
}
| BACKUPSET primary_key [, primary_key]...
| {CONTROLFILECOPY | DATAFILECOPY}
{{primary_key [, primary_key]... | 'filename' [, 'filename']...}
| TAG [=] ['] tag_name ['] [, ['] tag_name [']]...
}
| ARCHIVELOG
{primary_key [, primary_key]... | 'filename' [, 'filename']...}
}

RMAN> CHANGE BACKUPSET 431 KEEP FOREVER NOLOGS;
RMAN> CHANGE BACKUPSET 100 UNAVAILABLE;
RMAN> CHANGE BACKUPSET 121,122,127,203,300 UNCATALOG;
RMAN> CHANGE BACKUP OF DATABASE TAG=’abc’ UNAVAILABLE;
RMAN> CHANGE BACKUP OF DATABASE DEVICE TYPE DISK UNAVAILABLE;
RMAN> CHANGE BACKUP OF SPFILE COMPLETED BEFORE 'SYSDATE-3' UNAVAILABLE;
RMAN> CHANGE BACKUP TAG 'consistent_db_bkup' KEEP FOREVER;
RMAN> CHANGE BACKUP TAG 'consistent_db_bkup' KEEP FOREVER NOLOGS;
RMAN> CHANGE BACKUP FOR DB_UNIQUE_NAME standby1 RESET DB_UNIQUE_NAME;
RMAN> CHANGE BACKUP FOR DB_UNIQUE_NAME st3 RESET DB_UNIQUE_NAME TO st2;
RMAN> CHANGE DB_UNIQUE_NAME FROM rdbms4 TO rdbms_dev;RMAN> CHANGE ARCHIVELOG ALL UNCATALOG;
RMAN> CHANGE CONTROLFILECOPY '/tmp/cf.cpy' UNCATALOG;
RMAN> CHANGE FAILURE 5 PRIORITY LOW;

CROSSCHECK command
Check whether files managed by RMAN, such as archived logs, datafile copies, and backup pieces, still exist on disk or tape.

CROSSCHECK
{{BACKUP [OF listObjList] | COPY [OF listObjList] | archivelogRecordSpecifier} [maintQualifier [maintQualifier]...]
| recordSpec [DEVICE TYPE deviceSpecifier [, deviceSpecifier]...]
};

listObjList::=
[ DATAFILE datafileSpec [, datafileSpec]...
| TABLESPACE ['] tablespace_name ['] [, ['] tablespace_name [']]...
| archivelogRecordSpecifier
| DATABASE [SKIP TABLESPACE [']tablespace_name['] [, [']tablespace_name[']] ...]
| CONTROLFILE
| SPFILE
]...

recordSpec::=
{{ BACKUPPIECE | PROXY }
{ 'media_handle' [, 'media_handle']...
| primary_key [, primary_key]... | TAG [=] ['] tag_name [']
}
| BACKUPSET primary_key [, primary_key]...
| { CONTROLFILECOPY | DATAFILECOPY }
{ {primary_key [, primary_key]... | 'filename' [, 'filename']...}
| TAG [=] ['] tag_name ['] [, ['] tag_name [']]...
}
| ARCHIVELOG
{ primary_key [, primary_key]... | 'filename' [, 'filename']... }
}

RMAN> CROSSCHECK BACKUP;
RMAN> CROSSCHECK BACKUP TAG=’full db’;
RMAN> CROSSCHECK BACKUP COMPLETED BETWEEN ‘SYSDATE-7’ AND ‘SYSDATE–1’;
RMAN> CROSSCHECK BACKUP COMPLETED BETWEEN '01-JAN-10' AND '14-FEB-10';
RMAN> CROSSCHECK BACKUP DEVICE TYPE sbt COMPLETED BETWEEN '01-AUG-09' AND '31-DEC-09';
RMAN> CROSSCHECK BACKUP DEVICE TYPE DISK COMPLETED BETWEEN '01-JAN-10' AND '23-MAR-10';

RMAN> CROSSCHECK BACKUP OF DATABASE;
RMAN> CROSSCHECK BACKUP OF TABLESPACE warehouse;
RMAN> CROSSCHECK BACKUP OF TABLESPACE userd COMPLETED BEFORE 'SYSDATE-14';
RMAN> CROSSCHECK BACKUP OF TABLESPACES gld, invd;
RMAN> CROSSCHECK BACKUP OF DATAFILE 9;
RMAN> CROSSCHECK BACKUP OF DATAFILE 4 COMPLETED AFTER 'SYSDATE-14';
RMAN> CROSSCHECK BACKUP OF DATAFILE "?/oradata/dwh/system01.dbf" COMPLETED AFTER 'SYSDATE-14';
RMAN> CROSSCHECK BACKUP OF CONTROLFILE;
RMAN> CROSSCHECK BACKUP OF SPFILE;
RMAN> CROSSCHECK BACKUP OF ARCHIVELOG ALL;
RMAN> CROSSCHECK BACKUP OF ARCHIVELOG ALL SPFILE;

RMAN> CROSSCHECK COPY;
RMAN> CROSSCHECK COPY OF DATABASE;
RMAN> CROSSCHECK DATAFILECOPY 113, 114, 115;
RMAN> CROSSCHECK CONTROLFILECOPY '/tmp/control01.ctl';
RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> CROSSCHECK BACKUPSET;
RMAN> CROSSCHECK BACKUPSET 1338, 1339, 1340;
RMAN> CROSSCHECK BACKUPPIECE TAG = 'nightly_backup';
RMAN> CROSSCHECK PROXY 789;

SQL command
Execute a SQL statement from within Recovery Manager.

SQL [CHANNEL ‘channel_id’] ‘command’;

RMAN> SQL 'ALTER TABLESPACE users ONLINE';
RMAN> SQL 'ALTER DATABASE DATAFILE 64 OFFLINE';
RMAN> SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";
RMAN> SQL "ALTER SYSTEM SWITCH LOGFILE";
RMAN> SQL "ALTER DATABASE BACKUP CONTROLFILE TO TRACE";
RMAN> SQL "ALTER TABLESPACE users ADD DATAFILE ''/disk1/ora/users02.dbf'' SIZE 100K AUTOEXTEND ON NEXT 10K MAXSIZE 100K";

RESTORE command
Restore files from backup sets or from disk copies to the default or a new location.

RESTORE
[(] restoreObject [(restoreSpecOperand [restoreSpecOperand]...] [)]...
[ CHANNEL ['] channel_id [']
| PARMS [=] 'channel_parms'
| FROM { BACKUPSET | DATAFILECOPY }
| untilClause
| FROM TAG [=] ['] tag_name [']
| VALIDATE
| DEVICE TYPE deviceSpecifier [, deviceSpecifier]...
]...;

restoreObject::=
{ CONTROLFILE [TO 'filename']
| DATABASE [SKIP [FOREVER] TABLESPACE [']tablespace_name['] [, [']tablespace_name[']] ...]
| DATAFILE datafileSpec [, datafileSpec]...
| TABLESPACE ['] tablespace_name ['] [, ['] tablespace_name [']]...
| archivelogRecordSpecifier
| SPFILE [TO [PFILE] 'filename']
}

restoreSpecOperand::=
{ CHANNEL ['] channel_id [']
| FROM TAG [=] ['] tag_name [']
| PARMS [=] 'channel_parms'
| FROM {AUTOBACKUP [{MAXSEQ | MAXDAYS} [=] integer)]... | 'media_handle'}
}

RMAN> RESTORE DATABASE;
RMAN> RESTORE DATABASE VALIDATE;
RMAN> RESTORE DATABASE PREVIEW;
RMAN> RESTORE DATABASE PREVIEW SUMMARY;
RMAN> RESTORE DATABASE SKIP TABLESPACE temp, history;
RMAN> RESTORE DATABASE UNTIL SCN 154876;

RMAN> RESTORE TABLESPACE users;
RMAN> RESTORE TABLESPACE dwh1, dwh2;
RMAN> RESTORE TABLESPACE tbs1 PREVIEW;
RMAN> RESTORE TABLESPACE users VALIDATE;

RMAN> RESTORE DATAFILE 45;
RMAN> RESTORE DATAFILE 23 PREVIEW;
RMAN> RESTORE DATAFILE 12 VALIDATE;

RMAN> RESTORE CONTROLFILE;
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN> RESTORE CONTROLFILE FROM TAG 'monday_cf_backup';
RMAN> RESTORE CONTROLFILE VALIDATE;
RMAN> RESTORE CONTROLFILE TO '/tmp/autobkp.dbf' FROM AUTOBACKUP MAXSEQ 20 MAXDAYS 150;

RMAN> RESTORE SPFILE;
RMAN> RESTORE SPFILE FROM AUTOBACKUP;
RMAN> RESTORE ARCHIVELOG ALL VALIDATE;
RMAN> RESTORE ARCHIVELOG ALL PREVIEW;
RMAN> RESTORE ARCHIVELOG ALL PREVIEW RECALL;
RMAN> RESTORE ARCHIVELOG ALL DEVICE TYPE sbt PREVIEW;
RMAN> RESTORE STANDBY CONTROLFILE FROM TAG 'forstandby';

Restore the control file, (to all locations specified in the parameter file) then restore the database, using that control file:
STARTUP NOMOUNT;
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
RESTORE CONTROLFILE;
ALTER DATABASE MOUNT;
RESTORE DATABASE;
}

RECOVER command
Perform media recovery from RMAN backups and copies. Apply redo log files and incremental backups to datafiles or data blocks restored from backup or datafile copies, to update them to a specified time.

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

recoverObject::=
{ DATABASE
[ untilClause
| [untilClause] SKIP [FOREVER] TABLESPACE [']tablespace_name['] [, [']tablespace_name[']] ...]
| TABLESPACE [']tablespace_name['] [, [']tablespace_name[']]...
| DATAFILE datafileSpec [, datafileSpec]...
}

recoverOptionList::=
{ DELETE ARCHIVELOG [MAXSIZE {integer [K | M | G]}]
| CHECK READONLY
| NOREDO
| {FROM TAG | ARCHIVELOG TAG} [=] ['] tag_name [']
...
}

RMAN> RECOVER DATABASE;
RMAN> RECOVER DATABASE NOREDO;
RMAN> RECOVER DATABASE SKIP TABLESPACE temp;
RMAN> RECOVER DATABASE SKIP FOREVER TABLESPACE exam;
RMAN> RECOVER DATABASE UNTIL SCN 154876;

RMAN> RECOVER TABLESPACE users;
RMAN> RECOVER TABLESPACE dwh DELETE ARCHIVELOG MAXSIZE 2M;
RMAN> RECOVER DATAFILE 33;
RMAN> RECOVER DATAFILECOPY '/disk1/img.df' UNTIL TIME 'SYSDATE-7';
RMAN> RECOVER COPY OF DATABASE WITH TAG 'incr';
RMAN> RECOVER COPY OF DATABASE WITH TAG 'upd' UNTIL TIME 'SYSDATE - 7';
RMAN> RECOVER CORRUPTION LIST;

Restore and recover the whole database
RMAN> STARTUP FORCE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

Restore and recover a tablespace
RMAN> SQL 'ALTER TABLESPACE users OFFLINE';
RMAN> RESTORE TABLESPACE users;
RMAN> RECOVER TABLESPACE users;
RMAN> SQL 'ALTER TABLESPACE users ONLINE';

Restore and recover a datafile
RMAN> SQL 'ALTER DATABASE DATAFILE 64 OFFLINE';
RMAN> RESTORE DATAFILE 64;
RMAN> RECOVER DATAFILE 64;
RMAN> SQL 'ALTER DATABASE DATAFILE 64 ONLINE';

Steps for media recovery:
1. Mount or open the database. Mount the database when performing whole database recovery, or open the database when performing online tablespace/datafile recovery.
2. To perform incomplete recovery, use the SET UNTIL command to specify the time, SCN, or log sequence number at which recovery terminates. Alternatively, specify the UNTIL clause on the RESTORE and RECOVER commands.
3. Restore the necessary files with the RESTORE command.
4. Recover the datafiles with the RECOVER command.
5. Place the database in its normal state. For example, open it or bring recovered tablespaces/datafiles online.

DELETE command
Delete backups and copies, remove references to them from the recovery catalog, and update their control file records to status DELETED.

DELETE [FORCE] [NOPROMPT]
{[EXPIRED]
{
{BACKUP [OF listObjectList]
| COPY [OF listObectjList]
| archivelogRecordSpecifier
} [maintQualifier [maintQualifier]...]
| recordSpec [DEVICE TYPE deviceSpecifier [, deviceSpecifier]...]
}
| OBSOLETE [REDUNDANCY [=] integer | RECOVERY WINDOW OF integer DAYS | ORPHAN] [DEVICE TYPE (deviceSpecifier [, deviceSpecifier]...]
};

recordSpec::=
{ { BACKUPPIECE | PROXY }
{ 'media_handle' [, 'media_handle']...
| primary_key [, primary_key]...
| TAG [=] ['] tag_name [']
}
| BACKUPSET primary_key [, primary_key]...
| { CONTROLFILECOPY | DATAFILECOPY }
{ {primary_key [, primary_key]... | 'filename' [, 'filename']...}
| TAG [=] ['] tag_name ['] [, ['] tag_name [']]...
}
| ARCHIVELOG
{ primary_key [, primary_key]... | 'filename' [, 'filename']... }

listObjectList::=
[ DATAFILE datafileSpec [, datafileSpec]...
| TABLESPACE ['] tablespace_name ['] [, ['] tablespace_name [']]...
| archivelogRecordSpecifier
| DATABASE [SKIP TABLESPACE [']tablespace_name['] [, [']tablespace_name[']] ...]
| CONTROLFILE
| SPFILE
]...

RMAN> DELETE OBSOLETE;
RMAN> DELETE NOPROMPT OBSOLETE;
RMAN> DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
RMAN> DELETE EXPIRED BACKUP;
RMAN> DELETE EXPIRED BACKUP DEVICE TYPE sbt;
RMAN> DELETE BACKUP OF DATABASE LIKE '/tmp%';
RMAN> DELETE NOPROMPT EXPIRED BACKUP OF TABLESPACE userd COMPLETED BEFORE 'SYSDATE-14';
RMAN> DELETE BACKUP OF SPFILE TABLESPACE users DEVICE TYPE SBT;

RMAN> DELETE ARCHIVELOG ALL;
RMAN> DELETE ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE SBT;
RMAN> DELETE NOPROMPT ARCHIVELOG UNTIL SEQUENCE = 7300;
RMAN> DELETE EXPIRED ARCHIVELOG ALL;
RMAN> DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
RMAN> DELETE BACKUPSET 101, 102, 103;
RMAN> DELETE NOPROMPT BACKUPSET TAG weekly_bkup;
RMAN> DELETE FORCE NOPROMPT BACKUPSET TAG weekly_bkup;

RMAN> DELETE DATAFILECOPY "+DG_DATA/db/datafile/system.259.699468079";
RMAN> DELETE CONTROLFILECOPY '/tmp/cntrlfile.copy';
RMAN> DELETE BACKUP DEVICE TYPE SBT;
RMAN> DELETE BACKUP DEVICE TYPE DISK;
RMAN> DELETE COPY;
RMAN> DELETE EXPIRED COPY;
RMAN> DELETE COPY TAG 'lastest';

DROP DATABASE command
Delete the target database from disk and unregisters it.
RMAN> DROP DATABASE;
RMAN> DROP DATABASE NOPROMPT;
RMAN> DROP DATABASE INCLUDING BACKUPS;
RMAN> DROP DATABASE INCLUDING BACKUPS NOPROMPT;

DUPLICATE command
Use backups of the target database to create a duplicate database that we can use for testing purposes or to create a standby database.
RMAN> DUPLICATE TARGET DATABASE;
RMAN> DUPLICATE TARGET DATABASE TO dwhdb;
RMAN> DUPLICATE TARGET DATABASE TO test PFILE=/u01/apps/db/inittest.ora;
RMAN> DUPLICATE TARGET DATABASE TO devdb NOFILENAMECHECK;
RMAN> DUPLICATE DATABASE 'prod' DBID 139525561 TO 'dupdb' NOFILENAMECHECK;
RMAN> DUPLICATE TARGET DATABASE TO dup FROM ACTIVE DATABASE NOFILENAMECHECK PASSWORD FILE SPFILE;
RMAN> DUPLICATE TARGET DATABASE TO dupdb
LOGFILE GROUP 1 ('?/dbs/dupdb_log_1_1.f','?/dbs/dupdb_log_1_2.f') SIZE 200K, GROUP 2 ('?/dbs/dupdb_log_2_1.f','?/dbs/dupdb_log_2_2.f') SIZE 200K REUSE;
RMAN> DUPLICATE TARGET DATABASE TO dup FOR STANDBY FROM ACTIVE DATABASE PASSWORD FILE SPFILE PARAMETER_VALUE_CONVERT '/disk1', '/disk2'
SET DB_FILE_NAME_CONVERT '/disk1','/disk2'
SET LOG_FILE_NAME_CONVERT '/disk1','/disk2'
SET SGA_MAX_SIZE 200M SET SGA_TARGET 125M;

SWITCH command
Specify that a datafile copy is now the current datafile, i.e. the datafile pointed to by the control file. This command is equivalent to the SQL statement ALTER DATABASE RENAME FILE as it applies to datafiles.
RMAN> SWITCH DATABASE TO COPY;
RMAN> SWITCH TABLESPACE users TO COPY;
RMAN> SWITCH DATAFILE ALL;
RMAN> SWITCH DATAFILE '/disk1/tols.dbf' TO DATAFILECOPY '/disk2/tols.copy';
RMAN> SWITCH DATAFILE "+DG_OLD/db/datafile/sysaux.260.699468081" TO COPY;
RMAN> SWITCH TEMPFILE 1;
RMAN> SWITCH TEMPFILE 1 TO '/newdisk/dbs/temp1.f';
RMAN> SWITCH TEMPFILE ALL;

CATALOG command
Add information about file copies and user-managed backups to the catalog repository.
RMAN> CATALOG DATAFILECOPY '/disk1/old_datafiles/01_10_2009/users01.dbf';
RMAN> CATALOG DATAFILECOPY '/disk2/backup/users01.bkp' LEVEL 0;
RMAN> CATALOG CONTROLFILECOPY '/disk3/backup/cf_copy.bkp';
RMAN> CATALOG ARCHIVELOG '/disk1/arch1_731.dbf', '/disk1/arch1_732.dbf';
RMAN> CATALOG BACKUPPIECE '/disk1/c-874220581-20090428-01';
RMAN> CATALOG LIKE '/backup';
RMAN> CATALOG START WITH '/fs2/arch';
RMAN> CATALOG START WITH '/disk2/archlog' NOPROMPT;
RMAN> CATALOG START WITH '+dg2';
RMAN> CATALOG RECOVERY AREA;

ALLOCATE command
Establish a channel, which is a connection between RMAN and a database instance.
RMAN> ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
RMAN> ALLOCATE CHANNEL ch DEVICE TYPE DISK FORMAT ‘C:\ORACLEBKP\DB_U%’;
RMAN> ALLOCATE CHANNEL t1 DEVICE TYPE DISK CONNECT 'sys/pwd@bkp1’;
RMAN> ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS 'ENV=(OB_MEDIA_FAMILY=wholedb_mf)';
RMAN> ALLOCATE CHANNEL t1 DEVICE TYPE sbt PARMS 'ENV=(OB_DEVICE_1=tape1, OB_DEVICE_2=tape3)';
RMAN> ALLOCATE CHANNEL t1 TYPE 'sbt_tape' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so.1';
RMAN> ALLOCATE CHANNEL t1 TYPE 'sbt_tape' SEND "NB_ORA_CLIENT=CLIENT_MACHINE_NAME";
RMAN> ALLOCATE CHANNEL 'dev1' TYPE 'sbt_tape' PARMS 'ENV=OB2BARTYPE=ORACLE8, OB2APPNAME=ORCL, OB2BARLIST=MACHINENAME_ORCL_ARCHLOGS)';
RMAN> ALLOCATE AUXILIARY CHANNEL ac1 TYPE DISK;
RMAN> ALLOCATE AUXILIARY CHANNEL ac2 DEVICE TYPE sbt;

ALLOCATE CHANNEL FOR MAINTENANCE - allocate a channel in preparation for issuing maintenance commands such as DELETE.
RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK FORMAT "/disk2/%U";
RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK CONNECT '@test1';
RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE sbt;
RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE sbt PARMS 'SBT_LIBRARY=/usr/local/oracle/backup/lib/libobk.so, ENV=(OB_DEVICE_1=tape2)';

RELEASE CHANNEL command
Release a channel that was allocated with an ALLOCATE CHANNEL or ALLOCATE CHANNEL FOR MAINTENANCE command.
RMAN> RELEASE CHANNEL;
RMAN> RELEASE CHANNEL c1;

BLOCKRECOVER command
Will recover the corrupted blocks.
RMAN> BLOCKRECOVER CORRUPTION LIST;
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 22;
RMAN> BLOCKRECOVER DATAFILE 7 BLOCK 233,235 DATAFILE 4 BLOCK 101;
RMAN> BLOCKRECOVER DATAFILE 2 BLOCK 12,13 DATAFILE 3 BLOCK 5,98,99 DATAFILE 4 BLOCK 19;
RMAN> BLOCKRECOVER DATAFILE 3 BLOCK 2,4,5 TABLESPACE sales DBA 4194405,4194412 FROM DATAFILECOPY;
RMAN> BLOCKRECOVER TABLESPACE dwh DBA 4194404,4194405 FROM TAG "weekly";
RMAN> BLOCKRECOVER TABLESPACE dwh DBA 94404 RESTORE UNTIL TIME 'SYSDATE-2';

ADVISE FAILURE command (From 11gR1)
Display repair options.
RMAN> ADVISE FAILURE;
RMAN> ADVISE FAILURE 555, 242;
RMAN> ADVISE FAILURE ALL;
RMAN> ADVISE FAILURE CRITICAL;
RMAN> ADVISE FAILURE HIGH;
RMAN> ADVISE FAILURE LOW;
RMAN> ADVISE FAILURE HIGH EXCLUDE FAILURE 625;

REPAIR FAILURE command (From 11gR1)
Repair one or more failures recorded in the automated diagnostic repository.
RMAN> REPAIR FAILURE;
RMAN> REPAIR FAILURE PREVIEW;
RMAN> REPAIR FAILURE NOPROMPT;
RMAN> REPAIR FAILURE USING ADVISE OPTION integer;

VALIDATE command
Examine a backup set and report whether its data is intact. RMAN scans all of the backup pieces in the specified backup sets and looks at the checksums to verify that the contents can be successfully restored.
RMAN> VALIDATE BACKUPSET 218;
RMAN> VALIDATE BACKUPSET 3871, 3890;
RMAN> VALIDATE DATABASE; -- 11g R1
RMAN> VALIDATE TABLESPACE dwh;
RMAN> VALIDATE DATAFILE 2;
RMAN> VALIDATE DATAFILE 4,8;
RMAN> VALIDATE DATAFILE 4 BLOCK 56;
RMAN> VALIDATE CURRENT CONTROLFILE;
RMAN> VALIDATE SPFILE;
RMAN> VALIDATE RECOVERY FILES;
RMAN> VALIDATE RECOVERY AREA;
RMAN> VALIDATE DB_RECOVERY_FILE_DEST;

SPOOL command
Write RMAN output to a log file.
RMAN> SPOOL LOG TO '/tmp/spool.log';
RMAN> SPOOL LOG TO '/tmp/backup.log' APPEND;
RMAN> SPOOL LOG OFF;

run command
Execute a sequence of one or more RMAN commands, which are one or more statements executed within the braces of RUN.
RMAN> run {
ALLOCATE CHANNEL c1 TYPE DISK FORMAT '/orabak/%U';
BACKUP TABLESPACE users;
}
RMAN> run {
ALLOCATE CHANNEL c1 TYPE DISK FORMAT '&1/%U';
BACKUP TABLESPACE &2;
}
RMAN> run
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK FORMAT '/fs1/%U';
ALLOCATE CHANNEL dev2 DEVICE TYPE DISK FORMAT '/fs2/%U';
BACKUP (TABLESPACE system,fin,mark FILESPERSET 20) (DATAFILE 2,3,6);
}

CREATE SCRIPT command
Create a stored script and store it in the recovery catalog.

RMAN> CREATE SCRIPT backup_whole
COMMENT "backup whole database and archived redo log files"
{BACKUP INCREMENTAL LEVEL 0 TAG backup_whole FORMAT "/disk2/backup/%U" DATABASE PLUS ARCHIVELOG;}

RMAN> CREATE SCRIPT backup_ts_users
COMMENT 'tablespace users backup'
{ALLOCATE CHANNEL c1 TYPE DISK FORMAT 'c:\temp\%U';
BACKUP TABLESPACE users;}

RMAN> CREATE SCRIPT df {BACKUP DATAFILE &1 TAG &2.1 FORMAT '/disk1/&3_%U';}
RMAN> CREATE SCRIPT backup_ts_users FROM FILE 'backup_ts_users.rman';
RMAN> CREATE GLOBAL SCRIPT gl_backup_db {BACKUP DATABASE PLUS ARCHIVELOG;}
RMAN> CREATE GLOBAL SCRIPT backup_db
COMMENT "back up any database from the recovery catalog, with logs"
{BACKUP DATABASE;}

PRINT SCRIPT command
Display a stored script.
RMAN> PRINT SCRIPT backup_db;
RMAN> PRINT GLOBAL SCRIPT backup_db;
RMAN> PRINT GLOBAL SCRIPT gl_backup_db TO FILE "/tmp/gl_backupdb.rman";

REPLACE SCRIPT command
Replace an existing script stored in the recovery catalog. If the script does not exist, then REPLACE SCRIPT creates it.
RMAN> REPLACE SCRIPT backup_db {BACKUP DATABASE PLUS ARCHIVELOG;}
RMAN> REPLACE SCRIPT df {BACKUP DATAFILE &1 TAG &2.1 FORMAT '&3_%U';}
RMAN> REPLACE GLOBAL SCRIPT backup_db {BACKUP DATABASE PLUS ARCHIVELOG;}
RMAN> REPLACE GLOBAL SCRIPT gl_full_bkp FROM FILE '/tmp/script_file.txt';

EXECUTE SCRIPT command
Run an RMAN stored script.
RMAN> RUN {EXECUTE SCRIPT backup_whole;}
RMAN> RUN {EXECUTE SCRIPT backup_ts_any USING 'example';}
RMAN> RUN {EXECUTE SCRIPT backup_df USING 3 test_backup df3;}
RMAN> RUN {EXECUTE GLOBAL SCRIPT global_backup_db;}

DELETE SCRIPT command
Delete a stored script from the recovery catalog.
RMAN> DELETE SCRIPT backup_db;
RMAN> DELETE GLOBAL SCRIPT global_backup_db;

FLASHBACK DATABASE command
Return the database to its state at a previous time or SCN.
RMAN> FLASHBACK DATABASE TO SCN 411010;
RMAN> FLASHBACK DATABASE TO RESTORE POINT 'before_update';

TRANSPORT TABLESPACE command
Create transportable tablespace sets from backup for one or more tablespaces.
RMAN> TRANSPORT TABLESPACE example, tools
TABLESPACE DESTINATION '/disk1/trans' AUXILIARY DESTINATION '/disk1/aux' UNTIL TIME 'SYSDATE-15/1440';
RMAN> TRANSPORT TABLESPACE exam
TABLESPACE DESTINATION '/disk1/trans' AUXILIARY DESTINATION '/disk1/aux' DATAPUMP DIRECTORY dpdir DUMP FILE 'dmpfile.dmp' IMPORT SCRIPT 'impscript.sql' EXPORT LOG 'explog.log';

CONVERT command
Convert datafile formats for transporting tablespaces and databases across platforms.
RMAN> CONVERT DATABASE NEW DATABASE 'prodwin' TRANSPORT SCRIPT '/tmp/convertdb/transportscript' TO PLATFORM 'Microsoft Windows IA (32-bit)' DB_FILE_NAME_CONVERT '/disk1/oracle/dbs','/tmp/convertdb';
RMAN> CONVERT DATABASE ON DESTINATION PLATFORM CONVERT SCRIPT '/tmp/convertdb/convertscript.rman' TRANSPORT SCRIPT '/tmp/convertdb/transportscript.sql' NEW DATABASE 'prodwin' FORMAT '/tmp/convertdb/%U';
RMAN> CONVERT DATABASE ON DESTINATION PLATFORM CONVERT SCRIPT '/tmp/convert_newdb.rman' TRANSPORT SCRIPT '/tmp/transport_newdb.sql' NEW DATABASE 'prodaix' DB_FILE_NAME_CONVERT '/u01/oradata/datafile','+DATA';

RMAN> CONVERT TABLESPACE tbs_2 FORMAT '/tmp/tbs_2_%U.df';
RMAN> CONVERT TABLESPACE finance, hr TO PLATFORM 'Solaris[tm] OE (32-bit)' FORMAT '/tmp/transport_to_solaris/%U';

RMAN> CONVERT DATAFILE '/disk1/oracle/dbs/tbs_f1.df', '/disk1/oracle/dbs/ax1.f' FORMAT '+DATAFILE';
RMAN> CONVERT DATAFILE '/u01/oradata/datafile/system.dbf' FROM PLATFORM 'Linux x86 64-bit' FORMAT '+DATA/system.dbf';
RMAN> CONVERT DATAFILE
'/tmp/from_solaris/fin/fin01.dbf', '/tmp/from_solaris/fin/fin02.dbf',
'/tmp/from_solaris/hr/hr01.dbf', '/tmp/from_solaris/hr/hr02.dbf'
DB_FILE_NAME_CONVERT '/tmp/from_solaris/fin','/disk2/orahome/dbs/fin', '/tmp/from_solaris/hr','/disk2/orahome/dbs/hr'
FROM PLATFORM 'Solaris[tm] OE (64-bit)';

EXIT or QUIT Command
Exit the RMAN console.

RMAN> exit;
RMAN> quit;

SEND command
Send a vendor-specific quoted string to one or more specific channels.
RMAN> SEND 'OB_DEVICE tape1';

HOST command
Invoke an operating system command-line subshell from within RMAN or run a specific operating system command.
RMAN> HOST;
RMAN> HOST 'ls -lt /disk2/*';
RMAN> HOST '/bin/mv $ORACLE_HOME/dbs/*.arc /disk2/archlog/';

Source: Internet