Table of Contents
[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#
for all the datafiles.
In the backup everything is OK.
So I just simply restore the datafiles:
from about 1 year ago of current SCN (impossible→ ORACLE BUG).
In the source database I'm seeing correct
CHECKPOINTCHANGE#
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; }