/*
|| Oracle 10g RMAN Listing 1
||
|| Contains examples of new Oracle 10g Recovery Manager (RMAN) features.
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g's
|| new Recovery Manager (RMAN) features and should be carefully proofread
|| before executing it against any existing Oracle database to insure
|| that no potential damage can occur.
||
*/
-----
-- Listing 1.1: Image Copy Enhancements
-----
RUN {
# Set the default channel configuration
ALLOCATE CHANNEL dbkp1 DEVICE TYPE DISK
FORMAT 'c:\oracle\rmanbkup\ic_%d_%s_%t_%p';
# Back up specific datafiles and retain them as an image copies
BACKUP AS COPY (DATAFILE 2, 6, 9 MAXSETSIZE 25M);
# Back up a specific tablespace and retain it as an image copy
BACKUP AS COPY (TABLESPACE example MAXSETSIZE 15M);
# Back up the whole database and retain it as an image copy
BACKUP AS COPY DATABASE;
}
-----
-- Listing 1.2: Incrementally-Updated Backups: A Weekly Implementation
-----
RUN {
######
# This script will create image copy backups to which incremental
# changes can be applied on a weekly schedule
######
# Roll forward any available changes to image copy files
# from the previous set of incremental Level 1 backups. Note that
# the roll-forward will not occur until 7 days have elapsed!
RECOVER
COPY OF DATABASE
WITH TAG 'img_cpy_upd'
UNTIL TIME (SYSDATE-7);
## Create incremental level 1 backup of all datafiles in the database
# for roll-forward application against weekly image copies
BACKUP
INCREMENTAL LEVEL 1
FOR RECOVER OF COPY WITH TAG 'img_cpy_upd'
DATABASE;
}
-----
-- Listing 1.3: Managing Block Change Tracking
-----
-----
-- Activate block change tracking, if Oracle Managed Files (OMF) is in place
-----
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
-----
-- Activate block change tracking when OMF is +not+ in place. Note that:
-- 1.) Initial file size is 10MB
-- 2.) File size grows in 10MB increments
-- 3.) Will be approximately 1/30000th size of total database
-----
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE 'c:\oracle\rmanbkup\ocft\cft.f' REUSE;
-- Verify block change tracking file's existence
SELECT *
FROM v$block_change_tracking;
-----
-- Shut down block change tracking to move the block change tracking file.
-- Note that this will cause the loss of all block change tracking information,
-- but is the only alternative to shutting down the database
-----
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE 'c:\oracle\rmanbkup\ocft\cft.f' REUSE;
-----
-- Listing 1.4: What files will RMAN use during a RESTORE operation?
-- NOTE: These commands should be issued from within an active RMAN session
-----
# Spool output to a log file
SPOOL LOG TO c:\oracle\rmancmd\restoresummary.lst;
# Show what files will be used to restore the SYSTEM tablespace's datafile
RESTORE DATAFILE 1 PREVIEW;
# Show what files will be used to restore a specific tablespace
RESTORE TABLESPACE hr PREVIEW;
# Show a summary for a full database restore
RESTORE DATABASE PREVIEW SUMMARY;
# Close the log file
SPOOL LOG OFF;
-----
-- The resulting output:
-----
Spooling started in log file: c:\oracle\rmancmd\restoresummary.lst
Recovery Manager: Release 10.1.0.2.0 - Production
RMAN>
Starting restore at 21-NOV-04
using channel ORA_DISK_1
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
14 1 A 21-NOV-04 2034765 21-NOV-04 C:\ORACLE\RMANBKUP\IC_ZDCDB_100_1_542808680
Finished restore at 21-NOV-04
RMAN>
Starting restore at 21-NOV-04
using channel ORA_DISK_1
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
17 4 A 21-NOV-04 2034819 21-NOV-04 C:\ORACLE\RMANBKUP\IC_ZDCDB_103_1_542808837
Finished restore at 21-NOV-04
RMAN>
Starting restore at 21-NOV-04
using channel ORA_DISK_1
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
14 1 A 21-NOV-04 2034765 21-NOV-04 C:\ORACLE\RMANBKUP\IC_ZDCDB_100_1_542808680
16 2 A 21-NOV-04 2034807 21-NOV-04 C:\ORACLE\RMANBKUP\IC_ZDCDB_102_1_542808801
21 3 A 21-NOV-04 2034839 21-NOV-04 C:\ORACLE\RMANBKUP\IC_ZDCDB_107_1_542808886
17 4 A 21-NOV-04 2034819 21-NOV-04 C:\ORACLE\RMANBKUP\IC_ZDCDB_103_1_542808837
19 5 A 21-NOV-04 2034832 21-NOV-04 C:\ORACLE\RMANBKUP\IC_ZDCDB_105_1_542808871
23 6 A 21-NOV-04 2034845 21-NOV-04 C:\ORACLE\RMANBKUP\IC_ZDCDB_109_1_542808898
20 7 A 21-NOV-04 2034836 21-NOV-04 C:\ORACLE\RMANBKUP\IC_ZDCDB_106_1_542808879
18 8 A 21-NOV-04 2034829 21-NOV-04 C:\ORACLE\RMANBKUP\IC_ZDCDB_104_1_542808863
24 9 A 21-NOV-04 2034847 21-NOV-04 C:\ORACLE\RMANBKUP\IC_ZDCDB_110_1_542808902
22 10 A 21-NOV-04 2034843 21-NOV-04 C:\ORACLE\RMANBKUP\IC_ZDCDB_108_1_542808894
26 11 A 21-NOV-04 2034852 21-NOV-04 C:\ORACLE\RMANBKUP\IC_ZDCDB_112_1_542808909
25 12 A 21-NOV-04 2034850 21-NOV-04 C:\ORACLE\RMANBKUP\IC_ZDCDB_111_1_542808906
15 13 A 21-NOV-04 2034791 21-NOV-04 C:\ORACLE\RMANBKUP\IC_ZDCDB_101_1_542808756
Finished restore at 21-NOV-04
-----
-- Listing 1.5: Show RMAN current and historical activity
-----
-- What's the current RMAN session activity?
TTITLE 'Results of currently active Recovery Manager sessions'
COL output FORMAT A64 HEADING 'Activity'
SELECT
output
FROM v$rman_output
;
-- What are results from prior RMAN commands and sessions?
TTITLE 'Results of most recent Recovery Manager sessions'
COL command_id FORMAT A20 HEADING 'TimeStamp'
COL row_type FORMAT A8 HEADING 'Command/|Session'
COL operation FORMAT A8 HEADING 'Action'
COL status FORMAT A24 HEADING 'Status'
SELECT
command_id
,row_type
,operation
,status
FROM v$rman_status
ORDER BY command_id DESC
;