RMAN commands

To enable control file autobackup
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;



To change the default format for autobackup file name
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE disk TO '/u1/oradata/cf_ORCL_auto_%F';

where %F is mandatory and translates into c-IIIIIIIIII-YYYYMMDD-QQ being:

• IIIIIIII the DBID
• YYYYMMDD timestamp of the backup
• QQ a sequence that starts with 00 and ends with FF

Use the SHOW command to list current settings
RMAN> SHOW CONTROLFILE AUTOBACKUP FORMAT;
RMAN> SHOW EXCLUDE;
RMAN> SHOW ALL;



Use the CLEAR command to reset any persistent settings to its default value
RMAN> CONFIGURE BACKUP OPTIMIZATION CLEAR;
RMAN> CONFIGURE MAXSETSIZE CLEAR;
RMAN> CONFIGURE DEFAULT DEVICE TYPE CLEAR;





Configure the default device type for automatic channel allocation
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO sbt;

or manually allocate a channel

RMAN> RUN {
2> ALLOCATE CHANNEL c1 DEVICE TYPE disk;
3> BACKUP DATAFILE '/u0/oradata/user01.dbf';
4> }





Configure parallelism
RMAN> CONFIGURE DEVICE TYPE disk PARALLELISM 3;




Specify the maximum backup piece size
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2G;





Format the name of the generated backup files
RMAN> RUN {
2> ALLOCATE CHANNEL d1 DEVICE TYPE disk
3> FORMAT '/disk1/backups/%U';
4> BACKUP DATABASE PLUS ARCHIVELOG; }





Some backup options (more on Administration Workshop II 3-9)
RMAN> BACKUP device type disk tag '%TAG' database include current controlfile;
RMAN> BACKUP database plus archivelog;
RMAN> BACKUP datafile '/u0/oradata/user01.dbf';
RMAN> BACKUP AS BACKUPSET TABLESPACE hr_data; (AS BACKUPSET can be omitted if RMAN is configured in mode BACKUPSET)

After a online backup, remember to archive current redo logs using:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
or all archive logs:
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
or a specific redo group:
SQL> ALTER SYSTEM ARCHIVE LOG GROUP n;

More on backups at http://www.stanford.edu/dept/itss/docs/oracle/9i/server.920/a96519/backup.htm

See backup constraints at Administration Workshop II 3-10
See parallel backup commands at Administration Workshop II 3-11

To create a clone database or a standby database from backups of the target database
RMAN> DUPLICATE database to auxiliaryDb;



To flashback a database

RMAN> FLASHBACK DATABASE
2> TO TIME = TO_DATE
3> ('06/25/03 12:00:00','MM/DD/YY HH:MI:SS');

after you have performed flashback database you should remember to reset logs:

SQL> ALTER DATABASE RESETLOGS;





To restore/recover
RMAN> RESTORE/RECOVER database;
RMAN> RESTORE/RECOVER tablespace
RMAN> RESTORE/RECOVER datafile





Reporting commands

• LIST
• REPORT

For catalog maintenance (Administration Workshop II 3-8)

• CROSSCHECK
• DELETE
• CHANGE
• LIST

RMAN command-line arguments

(Administration Workshop II 2-27)
First set the ORACLE_SID env
$ export ORACLE_SID=orcl

Writing RMAN output to a log file
$ rman TARGET sys/oracle LOG=$HOME/oradata/u03/rman.log APPEND

Executing a command file when RMAN is invoked
$ rman TARGET sys/oracle CMDFILE=$HOME/scripts/my_rman_script.rcv

Establishes database connections on RMAN startup (both TARGET and CATALOG DBs)
$ rman TARGET sys/oracle@orcl CATALOG rman/rman@rcat

Note: NOCATALOG is the default mode (rman will use the control file as rman repository) unless a CATALOG database is specified.

Administration Workshop II. 10-3. Table types

• Ordinary (heap organized table): Data is stored as an unordered collection (heap).
• Partitioned table: data is divided into smaller, more manageable pieces.
• Index-organized table (IOT): Rows (key and non-key values) are sorted and stored in a B-tree index structure.
• Clustered table: related data from more than one table are stored together. In other words, a cluster is a group of tables that share the same data blocks because they share common columns and are often used together.
  

Administration Workshop II. 4-14. re-creating indexes

Use options to reduce the time it takes to create the index:

• PARALLEL
• NOLOGGING

SQL> CREATE INDEX rname_idx ON hr.regions (region_name) PARALLEL 4;

WARNING: if you created the index with PARALLEL option 4, then you'll need to change it back to PARALLEL 1 after the index is created so that it doesn't use 4 CPUs every time it access the index later!

Administration Workshop II. 2-16. Monitoring the Flash Recovery Area evolution with EM

Go to Enterprise Manager -> Related Links -> All metrics. Click on Recovery Area link then again on the percentage number to see the chart.

Administration Workshop II. 2-5. RMAN Performance Enhancements

• Automatic parallelization of backup, restore, and recovery operations
• No generation of extra redo during online database backups
• Backups that are restricted to limit the number of reads per file per second to avoid interfering with OLTP work
• The use of multiplexing, which can prevent flooding of any one file with reads and writes while still keeping a tape drive streaming
  

Administration Workshop II. 2-5. Use RMAN to detect block corruption

You can use RMAN to detect block corruption. The information relating to the block corruption that is detected during backup can be obtained by using the V$BACKUP_CORRUPTION and V$COPY_CORRUPTION dynamic views.

Administration Workshop II. 2-14. Using a Flash Recovery Area with RMAN

Flash Recovery Area simplifies managing disk space and files related to backup and recovery:

• When the flash recovery area is used, RMAN automatically uses Oracle Managed Files (OMF) for its backup files.
• Each time RMAN creates a file in the flash recovery area, the Oracle database updates the list of files that are no longer required on disk. When a file needs to be written into the flash recovery area and space is not available for that file, the Oracle database deletes a file that is on the obsolete files list and writes a notification to the alert log.
• A warning is issued when the flash recovery area experiences space pressure or is low on free space because there are no files that can be deleted from the flash recovery area. To resolve the problem, you can:
  
  - add disk space
  - back up your files to a tertiary device
  - delete the files from the flash recovery area using RMAN
  - or change the RMAN retention policy.
  
  The flash recovery area is configured by setting the DB_RECOVERY_FILE_DEST initialization parameter. The DB_RECOVERY_FILE_DEST_SIZE parameter specifies its size.
  

18-12. How to avoid generating REDO entries

Even though a database is in archive log mode, it's possible to individually disable logging for a particular table. To do so:

• Set the NOLOGGING attribute for the table or
  

• Specifying the UNRECOVERABLE Clause in the Control File. Example:
  

  UNRECOVERABLE
  LOAD DATA
  INFILE 'sample.dat'
  INTO TABLE emp
  (ename VARCHAR2(10), empno NUMBER(4));
  

Timestamp manipulation

Get the timestamp 10 minutes earlier from now:

SELECT SYSTIMESTAMP - INTERVAL '10' minute FROM DUAL;


Get the timestamp 1 year later from the specified timestamp:

SELECT timestamp'2004-02-29 00:00:00' + INTERVAL '1' year(1) FROM dual;


Extract fields from a timestamp:

SELECT
EXTRACT(year FROM current_timestamp) EY,
EXTRACT(month FROM current_timestamp) EM,
EXTRACT(day FROM current_timestamp) ED,
EXTRACT(hour FROM current_timestamp) EH,
EXTRACT(minute FROM current_timestamp) EM,
EXTRACT(second FROM current_timestamp) ES,
EXTRACT(timezone_hour FROM current_timestamp) TH,
EXTRACT(timezone_minute FROM current_timestamp) TM,
EXTRACT(timezone_region FROM current_timestamp) TR,
EXTRACT(timezone_abbr FROM current_timestamp ) TA
FROM dual;

More on: Oracle Timestamp Data Type

17-25. Flashback Versions Query Considerations

The VERSIONS clause cannot be used to query

• External tables
• Temporary tables
• Fixed tables
• Views

The VERSIONS clause cannot span DDL commands
The VERSIONS clause in a SELECT statement cannot produce versions of rows across the DDL statements that change the structure of the corresponding tables. This means that the query stops producing rows after it reaches a time in the past when the table structure was changed.
What if I want to flashback version query after a DDL? is it possible?

Segment shrink operations are filtered out
Certain maintenance operations, such as a segment shrink, may move table rows across blocks. In this case, the version query filters out such phantom versions because the row data remains the same.

17-9. Flashback Database Limitations

• You cannot use Flashback Database if the control file has been restored or re-created.
  
• Data file dropped after Flashback target time cannot be recovered (the dropped data file is added to the control file and marked offline, but it is not flashed back)
• Data file that has been shrunk since the Flashback target time cannot be flashed back. Any such data files must be taken offline before performing the flashback operation.
  

17-7. Flashback Database: Reducing Restore Time

With Flashback Database, the time to recover a database is now proportional to the number of changes that need to be backed out (and not to the size of the database) because you do not have to restore data files.

The Oracle database periodically logs "before images" of data blocks in the Flashback Database logs. Bock images can be reused to quickly back out the data file changes to any time at which flashback logs are captured just before the desired target time. Then, changes from the redo log files are applied to fill in the gap (????? why applying the redo log?).

16-8. Loss of a Redo Log File

Database Control does not allow you to clear a log group that has not been archived. Doing so breaks the chain of redo information. If you must clear an unarchived log group, you should immediately take a full backup of the whole database. Failure to do so may result in a loss of data if another failure ocurrs.

15-19. Flash Recovery Area

The Flash Recovery Area contains:

• Archived logs *
• Backups
• Flashback logs

Oracle recommends that the size of the flash recovery area be at least twice the size of the database so that it can hold one backup and several archived logs.

*If you have configured your archived logs to be written to this location (with the USE_DB_RECOVERY_AREA flag in one of the locations), then it is important to monitor this space to ensure that it des not reach its capacity.

15-16. Backup control file to a trace file

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

It would be backed up at the location specified at USER_DUMP_DEST initialization parameter with a name sid_ora_pid.trc

15-7. Backup sets vs. image copies

Data blocks
Image copies: all data blocks
Backup sets: no empty data blocks are copied (saves space). Typically 20% of database space is empty data block.

Granularity
Image copies: only the file we need has to be copied
Backup sets: all backup set must be retrieved even though only one of them is needed

A backup of a database running in NOARCHIVELOG mode must have all three attributes:

• offline
• full backup (level 0)
• whole database