User Tools

Site Tools


dba:oracle:docs:rman_basic_querys

[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

list backup ;

O un poco más resumido:

list backup summary ;

backupsets

by tag

LIST BACKUPSET TAG 'BAVELPRO_20140426-2143' ;

by date

 list backupset completed after 'SYSDATE-1' ;

Backups Obsoletos

list expired backup ;

o

list expired backup summary ;

Archivers

LIST ARCHIVELOG ALL;

Backup de Archivers

LIST BACKUP OF ARCHIVELOG ALL;

Summary;

LIST BACKUP OF ARCHIVELOG ALL SUMMARY;

http://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_1035.htm#REFRN30362

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#
;

Fichero de backup de Archiver

LIST COPY OF DATABASE ARCHIVELOG ALL;

Backup de datafile

LIST BACKUP OF DATAFILE 1;

Backup por fichero

LIST BACKUP BY FILE;

Datafilecopy

list datafilecopy ALL;

BBDD Incarnation

LIST INCARNATION;

Fallos

LIST FAILURE;

Detalles:

LIST FAILURE 641231 detail;

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

CROSSCHECK BACKUP;
CROSSCHECK backup OF DATABASE;
CROSSCHECK backup OF controlfile;

Copy

CROSSCHECK COPY;

Archivelog

CROSSCHECK archivelog ALL;

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

DELETE force noprompt backup completed BEFORE 'SYSDATE-1';

Borrar TODOS los archivers de la FRA

CUIDADO CON ESTE COMANDO!!!!

DELETE ARCHIVELOG ALL ;

Borrar TODOS los archivers de la FRA a sysdate-1

CUIDADO CON ESTE COMANDO!!!!

DELETE ARCHIVELOG ALL  COMPLETED BEFORE 'TRUNC(SYSDATE-1)';

Borrar backups obsoletos

Después del crosscheck…

  DELETE force noprompt expired backup ;

Borrar backup por tag

Después del crosscheck…

DELETE BACKUP TAG 'TEST01';

Borrar datafilecopy

Todo:

DELETE datafilecopy ALL;

By key:

DELETE datafilecopy <key> ;

by name:

DELETE datafilecopy 'datafile_name' ;

Examples

Restoring spfile from backup

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

Validar

la bbdd completa

VALIDATE DATABASE;

backupset

VALIDATE BACKUPSET 264287 ;

Datafile

 VALIDATE DATAFILE 1 BLOCK 10;

SQL*

Status of the backups inside oracle

Clean (only backup ops):

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
;

All:

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
;

Destination of backups

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;

Query FRA backup by type of file

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;

Kill RMAN session

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%';

Flashback

List restore points

LIST RESTORE POINT ALL;

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:
CREATE TABLESPACE "RMAN" DATAFILE '+DATA' SIZE 2000M AUTOEXTEND OFF;
  • Create the user for the catalog
CREATE USER rman IDENTIFIED BY yourpassword
  TEMPORARY TABLESPACE temp
  DEFAULT TABLESPACE RMAN
  QUOTA UNLIMITED ON RMAN;
  • Grant it:
GRANT RECOVERY_CATALOG_OWNER TO rman;
  • Connect to the catalog (in the same host of the DB)
rman CATALOG rman/yourpassword@RMANDB
  • Create the catalog itself
CREATE CATALOG
  • Now on the DB host, connect to the catalog:
 rman TARGET / CATALOG rman/yourpassword@RMANDB
  • and register the database
REGISTER DATABASE;
  • Done! Begin using the catalog

DEBUG of common errors

ORA-01547+ORA-01152+RMAN-06054

Something like:

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

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$datafileheader to see datafile information about datafiles and see CHECKPOINTCHANGE#

I've seen in the destination datafiles with a CHECKPOINTCHANGE# from about 1 year ago of current SCN (impossible→ ORACLE BUG). In the source database I'm seeing correct CHECKPOINTCHANGE# for all the datafiles. In the backup everything is OK. So I just simply restore the datafiles:

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;
}
dba/oracle/docs/rman_basic_querys.txt · Last modified: 2023/01/31 08:22 by dodger