====== [DOC] RMAN Basic queries ======
===== Very basics commands =====
==== Connect user OS auth ====
  * ''rman target /''

==== Logging commands ====
This will display NO OUTPUT:
  * ''rman LOG /tmp/rman.log''

So the best way is use tee:
  * ''rman | tee /tmp/rman.log''


==== Using a script ====
Like SQL*:
  * ''rman TARGET / @scriptfile''

==== Checking Syntax ====
When checking syntax, nothing is executed (test/debug/dry mode)
  * ''rman CHECKSYNTAX''



===== Listar =====
==== Configuration ====
  * ''SHOW ALL ;''
  * ''SHOW RETENTION POLICY ;''
  * ''SHOW DEFAULT DEVICE TYPE ;''


==== Backups ====
<code sql>list backup ;</code>
O un poco más resumido:
<code sql>list backup summary ;</code>

==== backupsets ====
by tag
<code sql>LIST BACKUPSET TAG 'BAVELPRO_20140426-2143' ;
</code>
by date
<code sql> list backupset completed after 'SYSDATE-1' ;
</code>

==== Backups Obsoletos ====
<code sql>list expired backup ;</code>
o
<code sql>list expired backup summary ;</code>
==== Archivers ====
<code sql>LIST ARCHIVELOG ALL;
</code>
==== Backup de Archivers ====
<code sql>LIST BACKUP OF ARCHIVELOG ALL;
</code>
Summary;
<code sql>
LIST BACKUP OF ARCHIVELOG ALL SUMMARY;
</code>

http://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_1035.htm#REFRN30362
<code sql>
col filesize_display format a20
SELECT BTYPE,
  BTYPE_KEY,
  SESSION_KEY,
  SESSION_RECID,
  SESSION_STAMP ,
  ID1 ,
  ID2 ,
  THREAD#,
  SEQUENCE#,
  RESETLOGS_CHANGE#,
  RESETLOGS_TIME ,
  FIRST_CHANGE# ,
  FIRST_TIME ,
  NEXT_CHANGE# ,
  NEXT_TIME ,
  FILESIZE ,
  COMPRESSION_RATIO ,
  FILESIZE_DISPLAY
FROM V$BACKUP_ARCHIVELOG_DETAILS
ORDER BY sequence#
;
</code>
==== Fichero de backup de Archiver ====
<code sql>LIST COPY OF DATABASE ARCHIVELOG ALL;
</code>

==== Backup de datafile ====
<code sql>LIST BACKUP OF DATAFILE 1;
</code>

==== Backup por fichero ====
<code sql>LIST BACKUP BY FILE;
</code>

==== Datafilecopy ====
<code sql>list datafilecopy all;
</code>

==== BBDD Incarnation ====
<code sql>LIST INCARNATION;
</code>

==== Fallos ====
<code sql>LIST FAILURE;
</code>
Detalles:
<code sql>LIST FAILURE 641231 detail;</code>

===== CROSSCHECK =====
Crosscheck actualiza la bbdd de backups respecto a lo que realmente hay o explicado por oracle:

//The CROSSCHECK command synchronizes the logical records of RMAN backups and copies with the files on storage media. If a backup is on disk, then CROSSCHECK determines whether the header of the file is valid. If a backup is on tape, then RMAN queries the RMAN repository for the names and locations of the backup pieces. It is a good idea to crosscheck backups and copies before deleting them.//

==== Backup ====
<code sql>CROSSCHECK BACKUP;
</code>
<code sql>CROSSCHECK backup of database;
</code>
<code sql>CROSSCHECK backup of controlfile;
</code>
==== Copy ====
<code sql>CROSSCHECK COPY;
</code>
==== Archivelog ====
<code sql>CROSSCHECK archivelog all;
</code>

===== Report =====
REPORT analiza qué ha cambiado e indica de qué ha de hacerse backup:

^ Comando ^ Descripción ^
| ''REPORT NEED BACKUP DATABASE'' | Shows which files need backing up under current retention policy. Use optional REDUNDANCY and RECOVERY WINDOW parameters to specify different criteria. |
| ''REPORT OBSOLETE'' | Lists backups that are obsolete under the configured backup retention policy. Use the optional REDUNDANCY and RECOVERY WINDOW parameters to override the default. |
| ''REPORT SCHEMA'' | Reports the tablespaces and data files in the database at the current time (default) or a different time. |
| ''REPORT UNRECOVERABLE'' | Lists all data files for which an unrecoverable operation has been performed against an object in the data file since the last backup of the data file. |


===== Borrado =====

==== Forzar el borrado de backups antiguos ====
<code sql>delete force noprompt backup completed before 'SYSDATE-1';</code>

==== Borrar TODOS los archivers de la FRA ====
CUIDADO CON ESTE COMANDO!!!!
<code sql>
DELETE ARCHIVELOG ALL ;
</code>

==== Borrar TODOS los archivers de la FRA a sysdate-1 ====
CUIDADO CON ESTE COMANDO!!!!
<code sql>
DELETE ARCHIVELOG ALL  COMPLETED BEFORE 'TRUNC(SYSDATE-1)';
</code>

==== Borrar backups obsoletos ====
Después del crosscheck...
<code sql>
  delete force noprompt expired backup ;
</code>

==== Borrar backup por tag ====
Después del crosscheck...
<code sql>
DELETE BACKUP TAG 'TEST01';
</code>


==== Borrar datafilecopy ====
Todo:
<code sql>
DELETE datafilecopy all;
</code>

By key:
<code sql>
DELETE datafilecopy <key> ;
</code>

by name:
<code sql>
DELETE datafilecopy 'datafile_name' ;
</code>

===== Examples =====
==== Restoring spfile from backup ====
  * Connect:
<code sql>$ rman</code>
<code sql>
CONNECT TARGET /
</code>
  * If your database is not up and you don't use recovery catalog then use:
<code sql>SET DBID 3386862614</code>
  * Start the instance with dummy parameter file:
<code sql>STARTUP FORCE NOMOUNT</code>
  * Restore server parameter file to restore in default location:
<code sql>RESTORE SPFILE FROM AUTOBACKUP;</code>
  * or restore in another location:
<code sql>RESTORE SPFILE TO 'new_location' FROM AUTOBACKUP;</code>
  * If you want to restore to a pfile then use:
<code sql>RESTORE SPFILE TO PFILE '/tmp/initTEMP.ora';</code>
  * Start the instance:
<code sql>STARTUP;</code>

===== Validar =====
[[http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmvalid.htm|Documentacion oficial]]

==== la bbdd completa ====
<code sql>VALIDATE DATABASE;</code>

==== backupset ====
<code sql>VALIDATE BACKUPSET 264287 ;</code>

==== Datafile ====
<code sql>
 VALIDATE DATAFILE 1 BLOCK 10;
</code>

====== SQL* ======

===== Status of the backups inside oracle =====

Clean (only backup ops):
<code sql>
SET LINESIZE 280
COL OUTPUT_DEVICE_TYPE FORMAT A30
COL COMMAND_ID FORMAT A20
COL STATUS FORMAT A30
COL OPERATION FORMAT A60

SELECT 
    RECID,
    STAMP,
    PARENT_RECID,
    PARENT_STAMP,
    SESSION_RECID,
    SESSION_STAMP,
--    COMMAND_ID,
    OPERATION,
    STATUS,
    MBYTES_PROCESSED,
    START_TIME,
    END_TIME,
    OPTIMIZED,
    OBJECT_TYPE,
    OUTPUT_DEVICE_TYPE
FROM  V$RMAN_STATUS
WHERE NOT OPERATION IN ('CROSSCHECK', 'DELETE')
order by start_time
;
</code>

All:
<code sql>
SET LINESIZE 280
COL OUTPUT_DEVICE_TYPE FORMAT A30
COL COMMAND_ID FORMAT A20
COL STATUS FORMAT A30
COL OPERATION FORMAT A60

SELECT 
    RECID,
    STAMP,
    PARENT_RECID,
    PARENT_STAMP,
    SESSION_RECID,
    SESSION_STAMP,
--    COMMAND_ID,
    OPERATION,
    STATUS,
    MBYTES_PROCESSED,
    START_TIME,
    END_TIME,
    OPTIMIZED,
    OBJECT_TYPE,
    OUTPUT_DEVICE_TYPE
FROM  V$RMAN_STATUS 
order by start_time
;
</code>



===== Destination of backups =====
<code sql>
COLUMN NAME FORMAT A30;
col space_limit format 999999999999999999
col SPACE_USED format 999999999999999999
col SPACE_RECLAIMABLE format 999999999999999999
select * from v$recovery_file_dest;
</code>

===== Query FRA backup by type of file =====
<code sql>
select file_type, 
       space_used*percent_space_used/100/1024/1024 used,
       space_reclaimable*percent_space_reclaimable/100/1024/1024 reclaimable,
       frau.number_of_files
from v$recovery_file_dest rfd, v$flash_recovery_area_usage frau;
</code>

===== Kill RMAN session =====
<code sql>
select 'ALTER SYSTEM KILL SESSION ''' || b.sid || ',' || b.serial# || ''' IMMEDIATE ;'
from
 v$process a, v$session b
where
 a.addr=b.paddr and client_info
like
 'rman%';
</code>

====== Flashback ======
https://docs.oracle.com/cd/E11882_01/backup.112/e10642/flashdb.htm

===== List restore points =====
<code sql>
LIST RESTORE POINT ALL;
</code>


====== Create RMAN Catalog ======
To create an external catalog (in another database), Oracle recommends using the GridControl Database.
Steps to do that, inside the Grid Database:
  * Create the tablespace for the RMAN catalog:
<code sql>CREATE TABLESPACE "RMAN" DATAFILE '+DATA' size 2000M AUTOEXTEND OFF;</code>
  * Create the user for the catalog
<code sql>CREATE USER rman IDENTIFIED BY yourpassword
  TEMPORARY TABLESPACE temp
  DEFAULT TABLESPACE RMAN
  QUOTA UNLIMITED ON RMAN;
</code>
  * Grant it:
<code sql>GRANT RECOVERY_CATALOG_OWNER TO rman;
</code>
  * Connect to the catalog (in the same host of the DB)
<code sql>rman CATALOG rman/yourpassword@RMANDB
</code>
  * Create the catalog itself
<code sql>CREATE CATALOG</code>
  * Now on the DB host, connect to the catalog:
<code sql> rman TARGET / CATALOG rman/yourpassword@RMANDB
</code>
  * and register the database
<code sql>REGISTER DATABASE;
</code>
  * Done! Begin using the catalog



====== DEBUG of common errors ======
===== ORA-01547+ORA-01152+RMAN-06054 =====

Something like:
<code sql>
unable to find archived log
archived log thread=2 sequence=10583
Oracle Error: 
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup 
ORA-01110: data file 1: '+DG_DATA_01/DBNAMEGDN/DATAFILE/system.540.958515399'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 10/27/2017 22:20:49
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 10583 and starting SCN of 39396106955
</code>

Reference:
  * How to resolve ORA-01547 warning: RECOVER succeeded but OPEN RESETLOGS would get error below (Doc ID 1684437.1)
  * How to quickly check that Database is consistent after incomplete recovery (Point in Time Recovery) before OPEN RESETLOGS (Doc ID 1354256.1)
  * Incomplete recovery gives ORA-01152 (Doc ID 278856.1)

Summary:
Use ''v$datafile_header'' to see datafile information about datafiles and see ''CHECKPOINT_CHANGE#''

I've seen in the destination datafiles with a ''CHECKPOINT_CHANGE#'' from about 1 year ago of current SCN (impossible-> ORACLE BUG).
In the source database I'm seeing correct ''CHECKPOINT_CHANGE#'' for all the datafiles.
In the backup everything is OK.
So I just simply restore the datafiles:
<code sql>
RUN{
SET NEWNAME FOR DATAFILE 43 TO '+DG_RECO_01';
SET NEWNAME FOR DATAFILE 44 TO '+DG_RECO_01';
SET NEWNAME FOR DATAFILE 45 TO '+DG_RECO_01';
RESTORE DATAFILE 43;
RESTORE DATAFILE 44;
RESTORE DATAFILE 45;
SWITCH DATAFILE 43;
SWITCH DATAFILE 44;
SWITCH DATAFILE 45;
}
</code>