What is an RMAN CROSSCHECK?

Purpose: To ensure that data about backups in the recovery catalog or control file is synchronized with actual files on disk or in the media management catalog.

If the backup is on disk, then the CROSSCHECK command checks whether the header of the file is valid.
If the backup is on tape, then the crosscheck command checks that the backups exist in the media management software's catalog.

Backup pieces and image copies can have the status AVAILABLE, EXPIRED, or UNAVAILABLE.

NOTE:
The CROSSCHECK command *does not* delete operating system files or remove repository records.
For such operations, you must use the DELETE command.

In short, in RMAN terms, obsolete means "file is not needed", whereas expired means it "file not found".

How to properly mask qotation marks in an SQL statement

Example:

You want to insert the string

order_date=to_date('2013-11-14','YYYY-MM-DD')

into a column of a table.

For oracle to understand that you want the single quotes to be part of the inserted string, you need to mask it as below:

INSERT INTO SCOTT.FILTER_TABLE
       (filter_name,
        filter_priority,
        table_owner_pattern,
        table_name_pattern,
        column_name_pattern,
        sql_filter)
VALUES ('MYFILTER',
        3,
        'LXPROD%',
        '%',
        'ORDER_DATE',
        'order_date=to_date(''&&target_date'',''YYYY-MM-DD'')');

How to check for progress on a long-running RMAN job

If you want to know if your backup is actually doing anything then the following SQL is useful

col INPUT_BYTES format  999,999,999,999
col OUTPUT_BYTES format 999,999,999,999
col OPERATION format a30
col OUTPUT_DEVICE_TYPE format a9
col RECID format 99999
col PARENT_RECID format 99999
col PARENT_RECID heading P_RECID
col SID format 99999
col STATUS format a30

set linesize 300
set pagesize 5000
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';
SELECT   ST.SID, 
         LO.SERIAL#,
         ST.RECID, 
         ST.PARENT_RECID, 
         ST.STATUS, 
         ST.START_TIME, 
         ST.END_TIME, 
         ST.OPERATION,
         ST.OBJECT_TYPE, 
         ST.OUTPUT_DEVICE_TYPE, 
         ST.INPUT_BYTES, 
         ST.OUTPUT_BYTES, 
         TY.INPUT_TYPE,
         LO.MESSAGE,
         ROUND(LO.TIME_REMAINING/60) "ETA (MIN)"
FROM V$RMAN_STATUS ST INNER JOIN V$SESSION_LONGOPS LO ON ST.SID = LO.SID
LEFT OUTER JOIN V$RMAN_BACKUP_TYPE TY ON TY.INPUT_TYPE = ST.OBJECT_TYPE
WHERE ST.STATUS = 'RUNNING'
ORDER BY ST.RECID ASC, ST.START_TIME DESC;

Output:

SID	SERIAL#	RECID	PARENT_RECID	STATUS	START_TIME	END_TIME	OPERATION	OBJECT_TYPE	OUTPUT_DEVICE_TYPE	INPUT_BYTES	OUTPUT_BYTES	INPUT_TYPE	ETA (MIN)
227	42437	44435		RUNNING	24.01.2014 12:50:36	24.01.2014 16:06:12	RMAN			0	0		0
227	42676	44435		RUNNING	24.01.2014 12:50:36	24.01.2014 16:06:12	RMAN			0	0		0
227	42437	44436	44435	RUNNING	24.01.2014 12:51:11	24.01.2014 16:06:12	BACKUP	DB FULL	DISK	298811883520	52378599424	DB FULL	0
227	42676	44436	44435	RUNNING	24.01.2014 12:51:11	24.01.2014 16:06:12	BACKUP	DB FULL	DISK	298811883520	52378599424	DB FULL	0

As long as your INPUT_BYTES and OUTPUT_BYTES bytes are increasing then something is happening!

How to use RMANs REPORT command

Report which objects need backup under the currently configured retention policy:

REPORT NEED BACKUP;

Variants:

REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE SKIP TABLESPACE TBS_2;
REPORT NEED BACKUP TABLESPACE TBS_3; # uses configured retention policy
REPORT NEED BACKUP INCREMENTAL 2; # checks entire database
REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE DEVICE TYPE SBT;


To identify datafiles affected by an unrecoverable (such as a direct load insert)operation and the type of backup
required to ensure the datafile can be restored from backup:

REPORT UNRECOVERABLE;

Variants:
REPORT OBSOLETE;
REPORT OBSOLETE RECOVERY WINDOW OF 3 DAYS;
REPORT OBSOLETE REDUNDANCY 1;


Lists and display information about the database files:
REPORT SCHEMA;

If you use a recovery catalog:

REPORT SCHEMA AT TIME 'SYSDATE-14'; # schema 14 days ago
REPORT SCHEMA AT SCN 1000; # schema at scn 1000
REPORT SCHEMA AT SEQUENCE 100 THREAD 1; # schema at sequence 100

What is the difference between DELETE INPUT and DELETE ALL INPUT in RMAN?

With DELETE INPUT, RMAN only deletes the specific copy of the archived redo log chosen for the backup set.
With DELETE ALL INPUT, RMAN will delete each backed-up archived redo log file from all log archiving destinations.

For example, assume that you archive to /arc_dest1, /arc_dest2, and /arc_dest3, and you run the following command:

BACKUP DEVICE TYPE sbt 
  ARCHIVELOG ALL 
  DELETE ALL INPUT;

In this case RMAN backs up only one copy of each log sequence number in these directories, and then deletes all copies of any
log that it backed up from the (potentially multiple) archiving destinations.

If you had specified DELETE INPUT rather than DELETE ALL INPUT, then RMAN would only delete the specific archived redo log files
that it backed up (for example, it would delete the archived redo log files in /arc_dest1 if those were the files
used as the source of the backup, but it would leave the contents of the /arc_dest2 and /arc_dest3 intact).

How to use the VALIDATE options in RMAN - both 10g and 11g

Validate if database can be backed up (10g)
-------------------------------------------
BACKUP VALIDATE validate that all database files and archived logs can be backed up.
When you run BACKUP VALIDATE, RMAN reads the files to be backed up in their entirety, as it would during
a real backup. RMAN does not, however, actually produce any backup sets or image copies.

Validate against logical corruption (10g)
-----------------------------------------
BACKUP VALIDATE CHECK LOGICAL checks for logical corruptions.
In a logical corruption, the contents of the block are logically inconsistent.
Examples of logical corruption include corruption of a row piece or index entry.
If RMAN detects logical corruption, then it logs the block in the alert log and server session trace file.

Validate if a file or backupset can be restored (10g)
-------------------------------------------------------
RESTORE ... VALIDATE test whether RMAN can restore a specific file or set of files from a backup.
RMAN chooses which backups to use.
Validation of backups of the datafiles only reads the backups and does not affect the production datafiles.
RMAN reads all blocks in the backup piece or image copy. RMAN also validates offsite backups.
The validation is identical to a real restore operation except that RMAN does not write output files.
The lack of error messages means that RMAN had confirmed that it can use these backups successfully
during a real restore and recovery.

Validate against physical corruption (11g)
------------------------------------------
In Oracle Database 11g, a new command in RMAN, VALIDATE DATABASE, makes the operation of checking that the database is healthy and has no bad blocks trivial.
It can substitute the dbverify tool used in previous releases.
If physical corruption is detected, it logs into the Automatic Diagnostic Repository.
RMAN then produces an output that is partially shown below:

RMAN> validate database;

 You can also validate a specific tablespace: 

 RMAN> validate tablespace users;

 Or, datafile: 

 RMAN> validate datafile 1;

 Or, even a block in a datafile: 

 RMAN> validate datafile 4 block 56;

Add all the archivelogs, too:

RMAN> validate database plus archivelog check logical;

The VALIDATE command extends much beyond datafiles however. You can validate spfile, controlfilecopy, recovery files, Flash Recovery Area, and so on.

Oracle 19c documentation here

What are restore points and how are they used?

Definition:

A restore point is an alias to the system change number (SCN) of the database at the time the restore point was created.

Types of restore points:

1. Normal
2. Guaranteed

For both types, the name of the restore point and the SCN are recorded in the database control file.

Normal restore points are very lightweight. The control file can maintain a record of thousands of normal restore points with no significant impact upon database performance. Normal restore points eventually age out of the control file if not manually deleted, so they require no ongoing maintenance.

Guaranteed restore points on the other hand, guarantees that Oracle will retain the flashback logs for a Flashback Database operation.

A guaranteed restore point does not age out of the control file and must be explicitly dropped.
Guaranteed restore points will utilize space in the flash recovery area.
The flash recovery area must be therefore be defined and large enough to hold the flashback logs for the duration of the guaranteed restore point's existence.

Creation example:

CREATE RESTORE POINT PRE_EOD_201208;
CREATE RESTORE POINT PRE_RELEASE2 GUARANTEE FLASHBACK DATABASE;

Usage:

You can use restore points with any commands that recognize a RESTORE POINT clause as a shorthand for specifying an SCN.

Examples:

FLASHBACK DATABASE TO RESTORE POINT < restore point name >;
RESTORE DATABASE TO RESTORE POINT < restore point name >;
FLASHBACK TABLE emp TO RESTORE POINT < restore point name >;

To view the restore points created in your database, use:

select name,scn,time,database_incarnation#, 
guarantee_flashback_database,storage_size/1024/1024 "MB"
from v$restore_point;

NAME                SCN               TIME                                     DATABASE_INCARNATION# GUA        MB
------------------- ---------------- ---------------------------------------- --------------------- --- ----------
AKSEPT_TEST_START   153050263689      17-NOV-14 07.53.33.000000000 AM                              2 YES      14336

To create a normal restore point, you need either

the SELECT ANY DICTIONARY system privilege, or

the FLASHBACK ANY TABLE system privilege

To create a guaranteed restore point, you need SYSDBA privileges.

To view or use a restore point, the user need either of the following:

The SELECT ANY DICTIONARY system privilege

The FLASHBACK ANY TABLE system privilege

The SELECT_CATALOG_ROLE role

To drop a restore point:

DROP RESTORE POINT AKSEPT_TEST_START;