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;

How to drop a plan from the SMB

Find the SQL Plan Baselines name and sql handle:

SELECT  SQL_HANDLE,PLAN_NAME
FROM    DBA_SQL_PLAN_BASELINES 
WHERE   PLAN_NAME ='SQL_PLAN_b8p2xjvyx9ja5bcc2b4c9';

Run the following PL/SQL as a privileged user (a DBA or "as sysdba")

SET SERVEROUTPUT ON
DECLARE
  l_plans_dropped  PLS_INTEGER;
BEGIN
  l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
    sql_handle => 'SQL_b4545d8efdd4c545',
    plan_name  => 'SQL_PLAN_b8p2xjvyx9ja5bcc2b4c9');

  DBMS_OUTPUT.put_line('Number of plans dropped: ' || l_plans_dropped);
END;
/

How to perform a full restore of Oracle

The following scripts have been tested and proven to work.
Make sure the NB_ORA_POLICY is correctly entered and adheres to the documentation from your MML provider:

#!/bin/ksh
LOGFILE=$SCRIPT_BASE/log/restore_${ORACLE_SID}_`date +%Y%m%d%H%M%S`.log
export RECOVERY_CATALOG=catowner/**********@RMANCAT
nohup rman target / catalog $RECOVERY_CATALOG << EOF > $LOGFILE 2>&1  &
startup force nomount;
 set DBID=1554594349;
 run {
  allocate channel t1 type sbt;
  allocate channel t2 type sbt
  send 'NB_ORA_POLICY=ora_dbserver1_00_netwzone1,NB_ORA_CLIENT=dbserver1-bkp.mydomain.com,NB_ORA_SCHED=ora_dbserver1_00_netwzone1_user';
  set until sequence < logseq > thread 1;
  restore controlfile;
  alter database mount;
  restore database;
  recover database;
 }
 alter database open resetlogs;

EOF
echo "RMAN restore started in background. Check logfile: $LOGFILE"

A good crontab helptext

# Use the hash sign to prefix a comment
# +---------------- minute (0 - 59)
# |  +------------- hour (0 - 23)
# |  |  +---------- day of month (1 - 31)
# |  |  |  +------- month (1 - 12)
# |  |  |  |  +---- day of week (0 - 7) (Sunday=0 or 7)
# |  |  |  |  |
# *  *  *  *  *  command to be executed