Ciberterminal Wiki

User Tools

Site Tools

Trace:
dba:oracle:docs:rman_basic_querys

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
dba:oracle:docs:rman_basic_querys [2023/01/31 08:22] – removed - external edit (Unknown date) 127.0.0.1dba:oracle:docs:rman_basic_querys [2023/01/31 08:22] (current) – ↷ Page moved from dba:oracle:rman_basic_querys to dba:oracle:docs:rman_basic_querys dodger
Line 1: Line 1:
 +
 +====== [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>
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +