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
Next revision		Previous revision
Last revisionBoth sides next revision
oracle:rman_basic_querys [2018/04/27 09:00] – 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