Backup and Recovery

RMAN backup operations and management (Access: DBA or SYSDBA privilege)

-- Connect to RMAN
-- Access: DBA or users with SYSDBA privilege
RMAN TARGET /

-- Full database backup
-- Access: DBA or users with SYSDBA privilege
BACKUP DATABASE PLUS ARCHIVELOG;

-- Tablespace backup
-- Access: DBA or users with SYSDBA privilege
BACKUP TABLESPACE users;

-- Datafile backup
-- Access: DBA or users with SYSDBA privilege
BACKUP DATAFILE '/u01/oracle/data/users01.dbf';

-- Incremental backup
-- Access: DBA or users with SYSDBA privilege
BACKUP INCREMENTAL LEVEL 1 DATABASE;

-- Backup with compression
-- Access: DBA or users with SYSDBA privilege
BACKUP AS COMPRESSED BACKUPSET DATABASE;

-- Backup to specific location
-- Access: DBA or users with SYSDBA privilege
BACKUP DATABASE FORMAT '/backup/db_%d_%s_%p_%t.bkp';

-- Backup validation
-- Access: DBA or users with SYSDBA privilege
BACKUP VALIDATE DATABASE;

-- List backups
-- Access: DBA or users with SYSDBA privilege
LIST BACKUP;

-- List backup sets
-- Access: DBA or users with SYSDBA privilege
LIST BACKUPSET;

-- List backup pieces
-- Access: DBA or users with SYSDBA privilege
LIST BACKUPPIECE;

-- Show backup summary
-- Access: DBA or users with SYSDBA privilege
REPORT SCHEMA;

-- Crosscheck backups
-- Access: DBA or users with SYSDBA privilege
CROSSCHECK BACKUP;

-- Delete expired backups
-- Access: DBA or users with SYSDBA privilege
DELETE EXPIRED BACKUP;

-- Delete obsolete backups
-- Access: DBA or users with SYSDBA privilege
DELETE OBSOLETE;

Database recovery and flashback operations (Access: DBA or SYSDBA privilege)

-- Database recovery
-- Access: DBA or users with SYSDBA privilege
RESTORE DATABASE;
RECOVER DATABASE;

-- Tablespace recovery
-- Access: DBA or users with SYSDBA privilege
RESTORE TABLESPACE users;
RECOVER TABLESPACE users;

-- Datafile recovery
-- Access: DBA or users with SYSDBA privilege
RESTORE DATAFILE '/u01/oracle/data/users01.dbf';
RECOVER DATAFILE '/u01/oracle/data/users01.dbf';

-- Point-in-time recovery
-- Access: DBA or users with SYSDBA privilege
RESTORE DATABASE UNTIL TIME '2024-01-01:12:00:00';
RECOVER DATABASE UNTIL TIME '2024-01-01:12:00:00';

-- Recovery with SCN
-- Access: DBA or users with SYSDBA privilege
RESTORE DATABASE UNTIL SCN 123456;
RECOVER DATABASE UNTIL SCN 123456;

-- Recovery to specific log sequence
-- Access: DBA or users with SYSDBA privilege
RESTORE DATABASE UNTIL SEQUENCE 100;
RECOVER DATABASE UNTIL SEQUENCE 100;

-- Test recovery
-- Access: DBA or users with SYSDBA privilege
RESTORE DATABASE PREVIEW;
RESTORE DATABASE VALIDATE;

-- Recovery with new location
-- Access: DBA or users with SYSDBA privilege
RESTORE DATABASE TO '/new/location';

-- Block recovery
-- Access: DBA or users with SYSDBA privilege
RECOVER BLOCK 123456 FROM BACKUP;

-- Flashback database
-- Access: DBA or users with SYSDBA privilege
FLASHBACK DATABASE TO TIME '2024-01-01:12:00:00';
FLASHBACK DATABASE TO SCN 123456;

-- Flashback table
-- Access: Users with FLASHBACK privilege on table
FLASHBACK TABLE employees TO TIMESTAMP TO_TIMESTAMP('2024-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS');

-- Flashback query
-- Access: Users with SELECT privilege on table
SELECT * FROM employees AS OF TIMESTAMP TO_TIMESTAMP('2024-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS');