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
Last revisionBoth sides next revision
dba:oracle:rman_basic_querys [2022/02/11 11:12] – ↷ Page moved from oracle:rman_basic_querys to dba:oracle:rman_basic_querys dodgerdba:oracle:docs:rman_basic_querys [2023/01/31 08:22] – removed - external edit (Unknown date) 127.0.0.1
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> 
- 
- 
- 
- 
- 
- 
- 
- 
- 
- 
- 
- 
  
dba/oracle/docs/rman_basic_querys.txt · Last modified: 2023/01/31 08:22 by dodger