ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Given how large this topic is deprecated syntax is not included. Be sure to review the header and code in $ORACLE_HOME/rdbms/admin/prgrmanc.sql
This page is in the process of being upgraded to 20c so you will find some sections incomplete and others with notes supporting the on-going edits. Check back frequently as the page updates should be complete by week's end.
Database Commands that can be executed at the RMAN prompt
ASSOCIATE STATISTICS
DESCRIBE
INSERT
AUDIT
DISASSOCIATE STATISTICS
LOCK
BEGIN
DROP
MERGE
CALL
EXPLAIN PLAN
MOUNT
COMMENT
FLASHBACK
NOAUDIT
COMMIT
FLASHBACK TABLE
PURGE
DELETE
GRANT
RENAME
Data Dictionary Objects
See Related Topic "Dynamic Performance Views" Link Below
System Privileges
SYSDBA
BACKUP ANY TABLE
Encryption Algorithms
col algorithm_name format a15
col algorithm_description format a25
SELECT * FROM gv$rman_encryption_algorithms;
The default encryption algorithm is 128-bit AES.
Data Dictionary Objects
DBA_HIST_INSTANCE_RECOVERY
GV_$RECOVERY_FILE_STATUS
V_$RECOVER_FILE
DBA_RECOVERABLE_SCRIPT
GV_$RECOVERY_LOG
V_$RECOVERY_FILE_DEST
DBA_RECOVERABLE_SCRIPT_BLOCKS
GV_$RECOVERY_PROGRESS
V_$RECOVERY_FILE_STATUS
DBA_RECOVERABLE_SCRIPT_ERRORS
GV_$RECOVERY_STATUS
V_$RECOVERY_LOG
DBA_RECOVERABLE_SCRIPT_PARAMS
V_$FLASH_RECOVERY_AREA_USAGE
V_$RECOVERY_PROGRESS
GV_$INSTANCE_RECOVERY
V_$INSTANCE_RECOVERY
V_$RECOVERY_STATUS
GV_$RECOVER_FILE
Format Directives
Format
Description
%a
Current database activation id
%A
Zero-filled activation ID
%b
Specifies the file name stripped of directory paths. It is only valid for SET NEWNAME and backup when producing image copies.
Yields errors if used as a format specification for a backup that produces backup pieces.
%c
Specifies the copy number of the backup piece within a set of duplexed backup pieces. If you did not duplex a backup, then this variable is 1 for backup sets and 0 for proxy copies.
If a command is enabled, then the variable shows the copy number. The maximum value for %c is 256.
%d
Database name
%D
Current day of the month from the Gregorian calendar in format DD
%e
Archived log sequence number
%f
Absolute file number
%F
Combines the DBID, day, month, year, and sequence into a unique and repeatable generated name
%h
Archived redo log thread number
%I
DBID
%M
Month in the Gregorian calendar in the format MM
%n
Database name, padded on the right with x characters to a total length of eight characters
%N
Tablespace name. Only valid when backing up datafiles as image copies.
%p
Piece number within the backup set. This value starts at 1 for each backup set and is incremented by 1 for each backup piece created.
If a PROXY is specified, the %p variable must be included in the FORMAT string either explicitly or implicitly within %U.
%r
Resetlogs ID
%s
Backup set number. This number is a counter in the control file that is incremented for each backup set.
The counter value starts at 1 and is unique for the lifetime of the control file. If you restore a backup control file, then duplicate values can result. CREATE CONTROLFILE initializes the counter at 1.
%S
Zero-filled sequence number
%t
Backup set time stamp, a 4-byte value derived as the number of seconds elapsed since a fixed reference time. The combination of %s and %t can be used to form a unique name for the backup set.
%T
Year, month, and day in the Gregorian calendar in the format: YYYYMMDD
%u
An 8-character name constituted by compressed representations of the backup set or image copy number and the time the backup set or image copy was created
%U
A system-generated unique filename (default). %U is different for image copies and backup pieces.
For a backup piece, %U is a shorthand for %u_%p_%c and guarantees uniqueness in generated backup filenames. For an image copy of a datafile, %U means the following:
data-D-%d_id-%I_TS-%N_FNO-%f_%u
For an image copy of an archived redo log, %U means the following:
arch-D_%d-id-%I_S-%e_T-%h_A-%a_%u
For an image copy of a control file, %U means the following:
cf-D_%d-id-%I_%u
%Y
Year in this format: YYYY
%%
Percent (%) character. For example, %%Y translates to the string %Y
/* as Linux user oracle modify the TNSNAMES.ORA to point to the database that will host the repository catalog: Then ... */
$ sqlplus / as sysdba
-- using OMF
SQL> CREATE TABLESPACE cat_tbs;
SQL> CREATE USER repoomega
IDENTIFIED BY oracle1
DEFAULT TABLESPACE cat_tbs
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON cat_tbs;
SQL> GRANT create session TO repoomega
SQL> GRANT recovery_catalog_owner TO repoomega;
SQL> GRANT execute ON dbms_stats TO repoomega; -- for demos
-- in the operating system shell
$ rman target / catalog repoomega/oracle1@repos
RMAN> create catalog tablespace cat_tbs;
RMAN> register database;
RMAN> report schema;
RMAN> configure default device type to disk;
RMAN> configure device type disk parallelism 2 backup type to copy;
RMAN> configure retention policy to recovery window of 7 days;
RMAN> configure backup optimization on;
RMAN Catalog Upgrade
Thank you Ed Whalen
An RMAN catalog must be at the maximum version of all databases in the catalog or a higher version. For example, if the RMAN catalog is used to backup databases of version 12.2, 19.5 and 20.1 the catalog must be 20.1 or higher.
The following code sample shows how to upgrade the RMAN catalog even if the catalog database is, itself, of a lower version. target / catalog <catalog_connection_string> UPGRADE CATALOG;
Displays repair options for the specified failures. Prints a summary of the failures identified by the Data Recovery Advisor and implicitly closes all open failures that are already fixed.
To manually allocate a channel, which is a connection between RMAN and a database instance.
Each connection initiates an database server session on the target or auxiliary instance: this server session performs the work of backing up, restoring, or recovering RMAN backups.
Manually allocated channels (allocated by using ALLOCATE) should be distinguished from automatically allocated channels (specified by using CONFIGURE).
Manually allocated channels apply only to the RUN job in which you issue the command. Automatic channels apply to any RMAN job in which you do not manually allocate channels.
You can always override automatic channel configurations by manually allocating channels within a RUN command.
Each channel operates on one backup set or image copy at a time. RMAN automatically releases the channel at the end of the job.
You can control the degree of parallelism within a job by allocating the desired number of channels. Allocating multiple channels simultaneously allows a single job to read or write multiple backup sets or disk copies in parallel.
If you establish multiple connections, then each connection operates on a separate backup set or disk copy.
Whether ALLOCATE CHANNEL causes operating system resources to be allocated immediately depends on the operating system. On some platforms, operating system resources are allocated at the time the command is issued.
On other platforms, operating system resources are not allocated until you open a file for reading or writing.
Allocating a Single Backup Channel
Allocate a disk channel
ALLOCATE [AUXILIARY] CHANNEL <channel_name> [, <channel_id>,
DEVICE TYPE <device_type_name>[allocation_operand_list]
SQL> desc gv$backup_device
SQL> SELECT * FROM gv$backup_device;
RMAN> target / catalog repoomega/oracle1
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO disk;
RMAN> RUN {
ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '/u01/backups/%U';
BACKUP DATABASE PLUS ARCHIVELOG;
RELEASE CHANNEL;
}
Allocates a tape channel for a whole database and archived redo log backup
RMAN> RUN {
ALLOCATE CHANNEL tape1 DEVICE TYPE sbt
BACKUP DATABASE PLUS ARCHIVELOG;
RELEASE CHANNEL;
}
Spread the backup across several disk drives. Allocate one DEVICE TYPE DISK channel for ach disk drive and specify the format string so that the filenames are on different disks
RMAN> RUN {
ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '/u01/backups/%U';
ALLOCATE CHANNEL d2 DEVICE TYPE DISK FORMAT '/u02/backups/%U';
BACKUP DATABASE PLUS ARCHIVELOG;
RELEASE CHANNEL;
}
When creating multiple copies of a backup, you can specify the SET BACKUP COPIES command.
The following example generates a single backup of the database to disk, and then creates two identical backups of datafile 1 to two different file systems
RMAN> RUN {
ALLOCATE CHANNEL c1 DEVICE TYPE DISK MAXPIECESIZE 2G;
# AS COPY is the default, so RMAN creates image copies
BACKUP DATABASE PLUS ARCHIVELOG;
SET BACKUP COPIES = 2;
BACKUP DATAFILE 1 FORMAT '/u01/backups/%U', '/u02/backups/%U';
}
Allocate Channel for Maintenance
Allocates a channel in preparation for a CHANGE, DELETE, or CROSSCHECK. This cannot be used within a RUN block.
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE <device_specifier> [allocation_operand_list];
RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
Starting Control File and SPFILE Autobackup at 21-AUG-19
piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\AUTOBACKUP\ 2019_04_21\O1_MF_S_759794645_753RT6WB_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 21-AUG-19
Backup archive logs
RMAN> run
2> {
3> ALLOCATE CHANNEL t1 type 'SBT_TAPE';
4> sql 'alter system archive log current';
5> BACKUP ARCHIVELOG ALL filesperset
10 FORMAT 'orabase_al_t%t_s%s_p%p'
6> PLUS
7> ARCHIVELOG FORMAT '%d_%Y_%M_%D_%s_%p.arc'
8> DELETE ALL INPUT;
9> SHOW ALL;
10> RELEASE CHANNEL t1;
11> }
using target database control file instead of recovery catalog allocated channel: t1 channel t1: sid=102 devtype=SBT_TAPE channel t1: VERITAS NetBackup for Oracle - Release 5.1 (2002111220)
In the first RMAN places datafiles 3 through 7 into one backup set and datafiles 8 and 9 into another. In the second all datafiles go into a single backup set.
BACKUP AS BACKUPSET DEVICE TYPE <device_identifier> COPIES <integer> DATABASE FORMAT '<location_and_format>' ....
BACKUP AS COMPRESSED BACKUPSET DEVICE TYPE <device_identifier>
COPIES <integer>
DATABASE FORMAT '<location_and_format>'
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE;
RMAN> BACKUP AS COMPRESSED BACKUPSET DEVICE TYPE DISK COPIES 2 DATABASE FORMAT '/u01/db_%U', '/u02/db_%U';
BACKUP AS COPY
Backing up as an image copy
BACKUP AS COPY '<backup_specification>' ....
RUN {
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK FORMAT '/u01/%U';
ALLOCATE CHANNEL dev2 DEVICE TYPE DISK FORMAT '/u02/%U';
BACKUP AS COPY TABLESPACE system, tools, users, undotbs;
BACKUP AS COPY CURRENT CONTROLFILE;
}
With REUSE syntax
BACKUP AS COPY REUSE ARCHIVELOG LIKE "/orabase/ARCHIVELOG/O1_MF_1_124_76LLH2H4_.ARC
AUXILIARY FORMAT "+RCVDEST";
BACKUP CHANNEL
Manually allocate a channel, in order to specify that the backup run by this channel should go to both pools first_copy and second_copy.
Back up only logs without 2 backups on disk
BACKUP CHANNEL '<channel_id>' ....
RUN {
ALLOCATE CHANNEL d2 DEVICE TYPE DISK;
ALLOCATE CHANNEL d1 DEVICE TYPE DISK;
BACKUP CHANNEL d1 ARCHIVELOG UNTIL TIME 'SYSDATE' NOT BACKED UP 2 TIMES TAG SECOND_COPY;
BACKUP CHANNEL d2 ARCHIVELOG UNTIL TIME 'SYSDATE' NOT BACKED UP 2 TIMES TAG FIRST_COPY;
}
BACKUP CHECK
SET MAXCORRUPT indicates that no more than 1 corruption should be tolerated in each datafile. Because the CHECK LOGICAL option is specified on the BACKUP command, RMAN checks for both physical and logical corruption.
BACKUP CHECK [LOGICAL] <database_specifications>
RUN {
SET MAXCORRUPT FOR DATAFILE 5,6,7,8 TO 1;
BACKUP CHECK LOGICAL DATABASE;
}
Check for logical corruption in datafile 42
RMAN> BACKUP CHECK LOGICAL DATAFILE 42;
BACKUP CONTROLFILECOPY
Create a backup copy of a control file
BACKUP CONTROLFILECOPY ....
RMAN> BACKUP AS COPY CURRENT CONTROLFILE FORMAT 'c:\stage\control01.ctl';
RMAN> BACKUP CONTROLFILECOPY 'c:\stage\control01.ctl';
BACKUP COPIES
Define the number of backup copies to create
BACKUP COPIES <integer> DEVICE TYPE <device_type_name> BACKUPSET <ALL | backup_set_name>;
RUN {
BACKUP COPIES 2 DEVICE TYPE sbt BACKUPSET ALL;
}
BACKUP CURRENT
Backup the current Control File to the default or specified location
BACKUP CURRENT CONTROLFILE [TO DESTINATION <destination_path>];
RUN {
BACKUP CURRENT CONTROLFILE TO DESTINATION '/u01/backups';
}
BACKUP DATABASE
Backup Database
RUN {
ALLOCATE CHANNEL d1 DEVICE TYPE DISK;
BACKUP DATABASE;
}
Backup Database Plus Archivelog
RUN {
ALLOCATE CHANNEL d1 DEVICE TYPE DISK;
BACKUP DATABASE PLUS ARCHIVELOG;
}
BACKUP AS COPY DB_FILE_NAME_CONVERT (<current_string, new_string>)
TABLESPACE <target_tablespace>
RMAN> BACKUP AS COPY DB_FILE_NAME_CONVERT
('/app/oracle/oradata/orabase/uwdata01.dbf', '/app/oracle/oradata/orcl/classdemo01.dbf')
TABLESPACE uwclass;
BACKUP DB_RECOVERY_FILE_DEST
Synonym for BACKUP RECOVERY AREA
See BACKUP RECOVERY AREA below
BACKUP DEVICE TYPE
RMAN locates all datafile copies with the tag LATESTCOPY, backs them up to tape, and names the backups by means of substitution variables.
After the datafile copies are on tape, the example deletes all image copies with the tag LATESTCOPY.
BACKUP DEVICE TYPE <DISK | SBT> ....
RMAN> {
BACKUP DEVICE TYPE sbt
DATAFILECOPY FROM TAG 'LATESTCOPY' FORMAT 'Datafile%f_Database%d';
DELETE COPY TAG 'LATESTCOPY';
}
BACKUP DISKRATIO
Populate each backup set with datafiles from the specified number of disks. To disable set to 0.
RMAN> BACKUP DISKRATIO 0; -- disable
RMAN> BACKUP DISKRATIO 5; -- enable
BACKUP DURATION
Backup will stop if it is not completed in the specified duration
Specifies the maximum number of input files to include in each output backup set. This parameter is only relevant when BACKUP generates backup sets.
BACKUP FILESPERSET <integer> ....;
RMAN> BACKUP FILESPERSET 5 ARCHIVELOG ALL;
Starting backup at 22-AUG-19
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_05\ O1_MF_1_39_73SHKHNZ_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_05\ O1_MF_1_40_73SM7O4W_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_06\ O1_MF_1_41_73TOMX3F_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_06\ O1_MF_1_42_73TOMZBQ_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_06\ O1_MF_1_43_73TON4NJ_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_06\ O1_MF_1_44_73W88ZKL_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_07\ O1_MF_1_45_73WO26LK_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_07\ O1_MF_1_46_73XKB6MK_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_07\ O1_MF_1_47_73Y1Y9NJ_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_07\ O1_MF_1_48_73YCXQJ3_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_07\ O1_MF_1_49_73YCXXLK_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_07\ O1_MF_1_50_73YCYDSD_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_07\ O1_MF_1_51_73YFZ6Y8_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_07\ O1_MF_1_52_73YMTP6C_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_07\ O1_MF_1_53_73YMTR4W_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_07\ O1_MF_1_54_73YMTWKL_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_07\ O1_MF_1_55_73YWP90Z_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_08\ O1_MF_1_56_7412HMX9_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_08\ O1_MF_1_57_741HRVWB_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_09\ O1_MF_1_58_743CJG5V_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_09\ O1_MF_1_59_74471D2X_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_10\ O1_MF_1_60_7464B2D6_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_11\ O1_MF_1_61_749KV6QG_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_11\ O1_MF_1_62_749MMCSW_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_12\ O1_MF_1_63_74D16TWS_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_13\ O1_MF_1_64_74D8F300_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_14\ O1_MF_1_65_74JJTD6C_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_14\ O1_MF_1_66_74K11V98_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_15\ O1_MF_1_67_74MC0J3X_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_15\ O1_MF_1_68_74MYJJ1G_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_16\ O1_MF_1_69_74OC3HDP_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_16\ O1_MF_1_70_74PLR1LK_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_17\ O1_MF_1_71_74QXXFNJ_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_17\ O1_MF_1_72_74QY94C7_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_18\ O1_MF_1_73_74TK4200_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_18\ O1_MF_1_74_74TXM7GN_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_18\ O1_MF_1_75_74VZJJ7T_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_19\ O1_MF_1_76_74YHV0CQ_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_20\ O1_MF_1_77_74ZLSC4W_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_21\ O1_MF_1_78_753QZG00_.ARC; already backed up 1 time(s)
skipping archived log file C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_04_21\ O1_MF_1_79_753ROW4D_.ARC; already backed up 1 time(s)
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=80 RECID=42 STAMP=759841223
input archived log thread=1 sequence=81 RECID=43 STAMP=759880832
channel ORA_DISK_1: starting piece 1 at 22-AUG-19
channel ORA_DISK_2: starting archived log backup set
channel ORA_DISK_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=82 RECID=44 STAMP=759882877
channel ORA_DISK_2: starting piece 1 at 22-AUG-19
channel ORA_DISK_1: finished piece 1 at 22-AUG-19
piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_04_22\ O1_MF_ANNNN_TAG20190422T223438_756GZHTV_.BKP tag=TAG20190422T223438 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_2: finished piece 1 at 22-AUG-19
piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_04_22\ O1_MF_ANNNN_TAG20190422T223438_756GZJX9_.BKP tag=TAG20190422T223438 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:07
Finished backup at 22-AP-19
Starting Control File and SPFILE Autobackup at 22-APR-19
piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\AUTOBACKUP\2019_04_22\ O1_MF_S_759882888_756GZSYR_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 22-APR-19
BACKUP ... FOR RECOVER OF ...
This command will give me a level 1 backup to be used to recover level 0 backup to the latest level
BACKUP ... FOR RECOVER OF COPY WITH TAG ....
RMAN > BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG
'incr_upd' DATABASE FORMAT '/oradata/rman/161932/inc_%d_%T_%t_%s_%p';
Alters the RECOVER COPY... WITH TAG to perform incomplete recovery of the datafile copies to the point in time in the past where you want your window of recoverability to begin
-- maintain a 7 day window
RUN {
RECOVER COPY OF DATABASE WITH TAG 'incr_update' UNTIL TIME 'SYSDATE-7';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_upd' DATABASE;
}
BACKUP FORCE
Forces RMAN to ignore backup optimization even if CONFIGURE BACKUP OPTIMIZATION is ON
BACKUP FORCE <backup_specification>
RMAN> BACKUP FORCE DATABASE;
BACKUP FORMAT
Specify Backup Format
BACKUP FORMAT <format_specification> ....
RMAN> BACKUP FORMAT = 'UW_%d/%t/%s/%p' ARCHIVELOG LIKE '%arc_dest%';
BACKUP FULL
Creates a backup of all blocks of datafiles included in the backup
The keepOption subclause is used to specify the status of a backup or copy in relation to a retention policy
BACKUP KEEP <FOREVER | UNTIL TIME 'date_string'> [RESTORE POINT <restore_point_name>]
-- this demo contains an exception you must keep in mind if wishing to use this option
RMAN> BACKUP TABLESPACE UWDATA KEEP UNTIL TIME '10-SEP-2019';
Starting backup at 04-SEP-19
starting full resync of recovery catalog
full resync complete
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=145 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=26 device type=DISK
backup will be obsolete on date 10-SEP-19
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=C:\APPS19\ORADATA\ORABASE\UWDATA01.DBF
channel ORA_DISK_1: starting piece 1 at 04-SEP-19
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 09/04/2019 16:49:09
ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes
Copies two datafiles and exempts them from the retention policy forever
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> BACKUP KEEP FOREVER FORMAT '?/dbs/%U_longterm.cpy' TAG LNGTRM_BCK DATAFILE 5 DATAFILE 6;
RMAN> ALTER DATABASE OPEN;
BACKUP MAXSETSIZE
Use the MAXSETSIZE parameter of the CONFIGURE CHANNEL or ALLOCATE CHANNEL command to limit the size of backup pieces
BACKUP ... MAXSETSIZE ....;
RMAN> BACKUP DEVICE TYPE disk MAXSETSIZE 10G ARCHIVELOG ALL;
BACKUP NOCHECKSUM
If you specify the NOCHECKSUM option, then RMAN does not perform a checksum of the blocks when writing the backup
BACKUP NOCHECKSUM ...
RMAN> BACKUP NOCHECKSUM DATAFILE 5;
RMAN> BACKUP NOCHECKSUM TABLESPACE UWDATA PLUS ARCHIVELOG;
RMAN> BACKUP NOCHECKSUM TABLESPACE UWDATA;
Starting backup at 08-SEP-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=22 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=C:\APPS19\ORADATA\ORABASE\UWDATA01.DBF
channel ORA_DISK_1: starting piece 1 at 08-SEP-19
channel ORA_DISK_1: finished piece 1 at 08-SEP-19
piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_09_08\ O1_MF_NNNDF_TAG20190908T155925_76LL6HBQ_.BKP tag=TAG20190908T155925 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:23
Finished backup at 08-SEP-19
Starting Control File and SPFILE Autobackup at 08-SEP-19
piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\AUTOBACKUP\2019_09_08\ O1_MF_S_761327971_76LL6NXS_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 08-SEP-19
BACKUP NOEXCLUDE
Do not exclude a tablespace previously excluded using CONFIGURE EXCLUDE
BACKUP DATABASE NOEXCLUDE;
BACKUP DATABASE NOEXCLUDE;
BACKUP NOT BACKED UP
Backup not previously backed up by name or timestamp
BACKUP NOT BACKED UP ...;
RMAN> BACKUP NOT BACKED UP TABLESPACE EXAMPLE;
RMAN> BACKUP NOT BACKED UP SINCE TIME 'SYSDATE-14' DATABASE PLUS ARCHIVELOG;
BACKUP POOL
Specifies the media pool in which the backup is stored. Consult your media management documentation to see whether POOL is supported.
BACKUP POOL <integer> ....
RMAN> BACKUP POOL 1 VALIDATE ARCHIVELOG ALL;
Starting backup at 09-SEP-19
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=124 RECID=86 STAMP=761328242
input archived log thread=1 sequence=125 RECID=87 STAMP=761351587
channel ORA_DISK_2: starting archived log backup set
channel ORA_DISK_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=126 RECID=88 STAMP=761417042
input archived log thread=1 sequence=127 RECID=89 STAMP=761417817
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:00
List of Archived Logs
=====================
Thrd Seq Status Blocks Failing Blocks Examined Name
---- ------- ------ -------------- --------------- ---------------
1 124 OK 0 820 C:\APPS19\RECOVERY_AREA\
ORABASE\ARCHIVELOG\ 2019_09_08\ O1_MF_1_124_76LLH2H4_.ARC
1 125 OK 0 81141 C:\APPS19\RECOVERY_AREA\ ORABASE\ARCHIVELOG\2019_09_08\ O1_MF_1_125_76M98HTC_.ARC
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:02
List of Archived Logs
=====================
Thrd Seq Status Blocks Failing Blocks Examined Name
---- ------- ------ -------------- --------------- ---------------
1 126 OK 0 48214 C:\APPS19\RECOVERY_AREA\ ORABASE\ARCHIVELOG\2019_09_09\ O1_MF_1_126_76O9605V_.ARC
1 127 OK 0 5222 C:\APPS19\RECOVERY_AREA\ ORABASE\ARCHIVELOG\2019_09_09\ O1_MF_1_127_76O9Y9F5_.ARC
Finished backup at 09-SEP-19
BACKUP PROXY
Backs up the specified files with the proxy copy functionality, which gives the media management software control over the data transfer between storage devices and the datafiles on disk
BACKUP DEVICE TYPE <device_type> PROXY <DATAFILE <integer> | ONLY <DATABASE | ARCHIVELOG ALL>>
BACKUP DEVICE TYPE sbt PROXY DATAFILE 42;
BACKUP DEVICE TYPE sbt PROXY ONLY ARCHIVELOG ALL;
BACKUP DEVICE TYPE sbt PROXY ONLY DATABASE;
BACKUP RECOVERY AREA
Back up the fast recovery area
BACKUP RECOVERY AREA;
RMAN> BACKUP RECOVERY AREA;
BACKUP REUSE
REUSE enables RMAN to overwrite an already existing backup or copy with the same filename as the file that BACKUP is currently creating
BACKUP REUSE ....
RMAN> BACKUP REUSE TABLESPACE example;
Starting backup at 09-SEP-19
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=C:\APPS19\ORADATA\ORABASE\EXAMPLE01.DBF
channel ORA_DISK_1: starting piece 1 at 09-SEP-19
channel ORA_DISK_1: finished piece 1 at 09-SEP-19
piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_09_09\ O1_MF_NNNDF_TAG20190909T201348_76OOHFFO_.BKP tag=TAG20190909T201348 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 09-SEP-19
Starting Control File and SPFILE Autobackup at 09-SEP-19
piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\AUTOBACKUP\2019_09_09\ O1_MF_S_761429645_76OOHYSD_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 09-SEP-19
Excludes datafiles or archived redo log files from the backup if they are inaccessible, offline, or read-only
BACKUP SKIP <INACCESSIBLE | OFFLINE | READONLY >
RMAN> BACKUP DATABASE SKIP OFFLINE;
Starting backup at 23-SEP-19
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=142 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=141 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=C:\APPS19\ORADATA\ORABASE\SYSAUX01.DBF
input datafile file number=00005 name=C:\APPS19\ORADATA\ORABASE\EXAMPLE01.DBF
input datafile file number=00004 name=C:\APPS19\ORADATA\ORABASE\USERS01.DBF
input datafile file number=00003 name=C:\APPS19\ORADATA\ORABASE\UNDOTBS01.DBF
channel ORA_DISK_1: starting piece 1 at 23-SEP-19
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\APPS19\ORADATA\ORABASE\SYSTEM01.DBF
input datafile file number=00006 name=C:\APPS19\ORADATA\ORABASE\UWDATA01.DBF
input datafile file number=00007 name=C:\APPS19\ORADATA\ORABASE\CATTBS01.DBF
channel ORA_DISK_2: starting piece 1 at 23-SEP-19
channel ORA_DISK_1: finished piece 1 at 23-SEP-19
piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_09_23\ O1_MF_NNNDF_TAG20190923T201940_77TLWWD6_.BKP tag=TAG20190923T201940 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:48
channel ORA_DISK_2: finished piece 1 at 23-SEP-19
piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_09_23\ O1_MF_NNNDF_TAG20190923T201940_77TLWY00_.BKP tag=TAG20190923T201940 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:56
Finished backup at 23-SEP-19
Starting Control File and SPFILE Autobackup at 23-SEP-19
piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\AUTOBACKUP\2019_09_23\ O1_MF_S_762639523_77TM0O5C_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 23-SEP-19
BACKUP SPFILE
Backup the Server Parameter file to the default or specified location
BACKUP SPFILE [TO DESTINATION <destination_path>]
RMAN> backup spfile to destination 'c:\stage';
Starting backup at 17-AUG-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=150 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 17-AUG-19
channel ORA_DISK_1: finished piece 1 at 17-AUG-19
piece handle=C:\STAGE\ORABASE\BACKUPSET\2019_08_17\ O1_MF_NNSNF_TAG20190817T101852_74QXZX3F_.BKP tag=TAG20190817T101852 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-AUG-19
BACKUP TABLESPACE
Backup a named tablespace
BACKUP TABLESPACE <tablespace_name>;
RMAN> backup tablespace users;
Starting backup at 02-SEP-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=144 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=151 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=C:\APPS19\ORADATA\ORABASE\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 02-SEP-19
channel ORA_DISK_1: finished piece 1 at 02-SEP-19
piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_09_02\ O1_MF_NNNDF_TAG20190902T204201_7638JCBQ_.BKP tag=TAG20190902T204201 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 02-SEP-19
Starting Control File and SPFILE Autobackup at 02-SEP-19
piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\AUTOBACKUP\ 2019_09_02\O1_MF_S_760826527_7638JJQX_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 02-SEP-19
BACKUP TAG
Backup the database and apply a user specified tag, This is only supported with a recovery catalog.
BACKUP TAG '<tag_name>' ....
RMAN> BACKUP TAG 'weekly_full_db_bkup' DATABASE MAXSETSIZE 2G;
RMAN> BACKUP TAG Q107 DATABASE KEEP FOREVER;
BACKUP VALIDATE
Verify backup components are valid
BACKUP VALIDATE <ARCHIVELOG | AS .... | AUXILIARY | BACKUPSET | BACKUP | CHANNEL | CHECK | CONTROLFILECOPY | COPIES | COPY | CUMULATIVE | CURRENT DATABASE |
DATAFILECOPY | DATAFILE | DB_FILE_NAME_CONVERT | DB_RECOVERY_FILE_DEST | DEVICE | DISKRATIO | DURATION | FILESPERSET | FORCE | FORMAT | FOR | FROM | FULL | INCREMENTAL | KEEP | MAXSETSIZE |
NOCHECKSUM | NOEXCLUDE | NOKEEP | NOT | POOL | PROXY | RECOVERY | REUSE | SECTION | SKIP READONLY | SKIP | SPFILE | TABLESPACE | TAG | TO | VALIDATE>
-- after any of the above backup validate statements
SELECT *
FROM v$database_block_corruption;
Verify the spfile for backup
RMAN> BACKUP VALIDATE SPFILE;
Starting backup at 19-AUG-19
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Finished backup at 19-AUG-19
Verify a tablespace for backup
RMAN> BACKUP VALIDATE TABLESPACE 'UWDATA';
Starting backup at 19-AUG-19
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=C:\APPS19\ORADATA\ORABASE\UWDATA01.DBF
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 23217 32000 5105383
File Name: C:\APPS19\ORADATA\ORABASE\UWDATA01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 6872
Index 0 968
Other 0 943
Catalog the Backup Pieces held in the location set by DB_RECOVERY_FILE_DEST
CATALOG DB_RECOVERY_FILE_DEST
RMAN> CATALOG DB_RECOVERY_FILE_DEST;
CATALOG RECOVERY AREA
Catalog all files in the currently enabled fast recovery area
CATALOG RECOVERY AREA [NOPROMPT];
RMAN> CATALOG RECOVERY AREA NOPROMPT;
searching for all files in the recovery area
no files found to be unknown to the database
List of files in Recovery Area not managed by the database
==========================================================
File Name: C:\APPS19\RECOVERY_AREA\ORABASE\CONTROL02.CTL
RMAN-07526: Reason: File is not an Oracle Managed File
number of files not managed by recovery area is 1, totaling 9.31GB
CATALOG START WITH
Catalog all files in a named disk location
CATALOG START WITH <path_name> [NOPROMPT];
RMAN> CATALOG START WITH '/u02/archivelog' NOPROMPT;
To change the status of backups, copies, and archived logs in the repository to AVAILABLE or UNAVAILABLE.
This feature is useful when a previously unavailable file is made available again, or you do not want a specific backup or copy to be eligible to be restored but also do not want to delete it.
To alter the repository status of usable backups and copies from prior incarnations.
To remove catalog records for backups and copies, and update the corresponding records in the target control file to status DELETED.
This feature is useful when you remove a file by using an operating system command rather than the RMAN CHANGE command, and want to remove its repository record as well.
To specify that a backup or copy should either abide by the currently configured retention policy or be exempt from it.
CHANGE ARCHIVELOG
Move all archived logs to a new directory, uncatalog then recatalog them in the new location
CHANGE ARCHIVELOG [ALL] ....
HOST 'mv $ORACLE_HOME/oradata/trgt/arch/* /fs2/arch';
RMAN>
CHANGE ARCHIVELOG ALL UNCATALOG;
RMAN>
CATALOG START WITH '/fs2/arch';
Crosscheck archivelogs. This should be run if archivelogs have been deleted and can not be recovered to tell the database to ignore the missing files.
RMAN> CHANGE ARCHIVELOG ALL CROSSCHECK;
CHANGE BACKUP
Change the status of all backups of server parameter files created more than a day ago to UNAVAILABLE
CHANGE BACKUP OF <ARCHIVELOG | SPFILE> ...
RMAN> CHANGE BACKUP OF SPFILE COMPLETED BEFORE 'SYSDATE-1' UNAVAILABLE;
RMAN> CHANGE BACKUP OF ARCHIVELOG ALL NOKEEP;
Associate proda with the primary database
RMAN> CHANGE BACKUP FOR DB_UNIQUE_NAME proda RESET DB_UNIQUE_NAME;
Associate standby prodb with standby proda
RMAN> CHANGE BACKUP FOR DB_UNIQUE_NAME prodb RESET DB_UNIQUE_NAME TO proda;
Starting Control File and SPFILE Autobackup at 25-SEP-19
piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\AUTOBACKUP\2019_09_25\ O1_MF_S_762802665_77ZLBT7V_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 25-SEP-19
To configure persistent settings affecting RMAN backup, restore, duplication, and maintenance jobs. These configurations are in effect for any RMAN session until the configuration is cleared or changed.
Configuration Defaults
$ rman
RMAN> SHOW ALL;
RMAN> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
RMAN> CONFIGURE BACKUP OPTIMIZATION OFF;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP OFF;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
RMAN> CONFIGURE ENCRYPTION FOR DATABASE OFF;
RMAN> CONFIGURE ENCRYPTION ALGORITHM 'AES128';
RMAN> CONFIGURE MAXSETSIZE TO 2 G;
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/app/oracle/product/recovery_area/snapshot.ctl';
CONFIGURE ARCHIVELOG
Configures an number of archivelog copies based on the device
CONFIGURE ARCHIVELOG COPIES FOR DEVICE TYPE <device_type> TO <integer>;
RMAN> CONFIGURE ARCHIVELOG COPIES FOR DEVICE TYPE sbt TO 2;
Configures an archivelog deletion policy
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP <integer> TIMES TO DISK;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
For Data Guard: Applies the deletion policy to the standby
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
CONFIGURE AUXNAME
Set and clear an auxiliary name for a datafile
CONFIGURE AUXNAME FOR DATAFILE <data_file_number> TO <path_and_file_name>;
CONFIGURE AUXNAME FOR DATAFILE CLEAR;
RMAN> CONFIGURE AUXNAME FOR DATAFILE 5 TO '/home/oracle/auxfiles/aux1.f;
RMAN> CONFIGURE AUXNAME FOR DATAFILE 4 CLEAR;
CONFIGURE BACKUP OPTIMIZATION
Backup or don't backup unchanged files
CONFIGURE BACKUP OPTIMIZATION <ON | OFF>;
See SHOW BACKUP OPTIMIZATION Demo
CONFIGURE CHANNEL
Configure a backup channel to tape
CONFIGURE CHANNEL <DEVICE TYPE | INTEGER> <configuration | CLEAR>;
RMAN> CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' CLEAR;
SELECT algorithm_name, algorithm_description
FROM v$rman_compression_algorithm;
RMAN> CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';
RMAN> CONFIGURE COMPRESSION ALGORITHM 'BASIC';
CONFIGURE CONTROLFILE AUTOBACKUP
Enable autobackup and configure the default autobackup format for a DISK device
CONFIGURE CONTROLFILE AUTOBACKUP <ON | OFF | FORMAT FOR DEVICE TYPE ....>.;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
Configure the location for controlfile autobackups
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/%F';
SQL> col algorithm_name format a20
SQL> col algorithm_description format a25
SQL> SELECT algorithm_name, algorithm_description
2 FROM v$rman_encryption_algorithms;
RMAN> CONFIGURE ENCRYPTION ALGORITHM 'AES256';
RMAN> CONFIGURE ENCRYPTION CLEAR;
Configure Database or Tablespace Encryption
CONFIGURE ENCRYPTION FOR <DATABASE | TABLESPACE <tablespace_name> <ON | OFF | CLEAR>
RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON;
RMAN> CONFIGURE ENCRYPTION FOR TABLESPACE uwdata OFF;
CONFIGURE DEFAULT DEVICE TYPE TO
Changes the default device type to the default device type specified
CONFIGURE DEFAULT DEVICE TYPE TO <disk | sbt>;
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO disk;
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored
CONFIGURE DEVICE TYPE
Change the default configuration of a backup device
CONFIGURE DEFAULT DEVICE TYPE <disk | sbt> ....;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
CONFIGURE EXCLUDE FOR TABLESPACE
Exclude a tablespace from RMAN backup
CONFIGURE EXCLUDE FOR TABLESPACE <tablespace_name>;
CONFIGURE EXCLUDE FOR TABLESPACE staging;
CONFIGURE RETENTION POLICY
Retention Policy
CONFIGURE RETENTION POLICY TO REDUNDANCY <integer>;
Converts a datafile, tablespace or database to the format of a destination platform, in preparation for transport across different platforms.
CONVERT DATABASE
Uses CONVERT DATABASE NEW DATABASE to convert datafiles and generate a transport script
CONVERT DATABASE NEW DATABASE '<db_name>'
TRANSPORT SCRIPT '<script_location>'
TO PLATFORM '<platform_name>'
DB_FILE_NAME_CONVERT '<source_location> <destination_location>'
CONVERT DATABASE
NEW DATABASE 'prodb'
TRANSPORT SCRIPT '/tmp/convertdb/transportscript'
TO PLATFORM 'Linux IA (64-bit)'
DB_FILE_NAME_CONVERT '/u01/oradata', '/stage/dbfiles';
CONVERT DATAFILE
Converts the datafiles to be transported to the destination host format and deposits the results in /u02/oradata
CONVERT DATAFILE <datafile_name_list>
DB_FILE_NAME_CONVERT '<source_location> <destination_location>'
FROM PLATFORM <original_platform_name>
CONVERT DATAFILE
'/tmp/transport_from_aix/u01/uwdata01.dbf',
'/tmp/transport_from_aix/u01/uwdata02.dbf',
'/tmp/transport_from_aix/u03/users01.dbf',
'/tmp/transport_from_aix/u03/users02.dbf'
DB_FILE_NAME_CONVERT
'/app/oracle/product/oradata','/stage/oradata',
'/tmp/transport_from_solaris/hr','/stage/oradata'
FROM PLATFORM 'Linux IA (64-bit)';
CONVERT TABLESPACE
Convert a tablespaces to 64 bit Linux
CONVERT TABLESPACE <tablespace_name_list> TO PLATFORM <platform_name> FORMAT '<format_string>'
desc v$transportable_platform
SELECT platform_name, endian_format
FROM v$transportable_platform;
CONVERT TABLESPACE uwdata, users TO PLATFORM 'Linux IA (64-bit)'
FORMAT '/appk/oracle/product/recovery_area/linux64/%U';
Recovery Manager: Release 21.0.0.0.0 - Production
on Sun Jun 27 13:02:29 2021
Version 21.1.0.0.0
Copyright (c) 1982, 2020, Oracle and/or its affiliates. All rights
reserved.
connected to target database: TEST21DB (DBID=2140826538)
connected to recovery catalog database
RMAN> CREATE CATALOG;
recovery catalog created.
RMAN> EXIT; Recovery Manager complete.
[oracle@test21 dbhome_1]$ exit exit
SELECT object_type, COUNT(*)
FROM dba_objects
WHERE owner = 'ZZYZX'
GROUP BY object_type
ORDER BY 1;
OBJECT_TYPE
COUNT(*)
-------------- ----------
FUNCTION
5
INDEX
219
LOB
5
PACKAGE
3
PACKAGE BODY
3
SEQUENCE
1
TABLE
61
TRIGGER
6
TYPE
6
TYPE BODY
2
VIEW
121
CREATE GLOBAL SCRIPT
Create and store a global script in the repository
CREATE GLOBAL SCRIPT '<script_name>' [COMMENT '<comment>'] ....
RMAN> CREATE GLOBAL SCRIPT global_backup { BACKUP DATABASE PLUS ARCHIVELOG; }
CREATE RESTORE POINT
Create a restore point
CREATE RESTORE POINT <restore_point_name>
RMAN> CREATE RESTORE POINT app_patch;
RMAN> DROP RESTORE POINT app_patch;
CREATE SCRIPT
Creates a stored script called backup_whole that backs up the database and archived redo logs
CREATE [GLOBAL] SCRIPT '<script_name>'
COMMENT '<comment>'
{RMAN_command_list}
FROM FILE '<file_name>'
CREATE SCRIPT backup_whole
COMMENT "backup whole database and logs"
{
BACKUP INCREMENTAL LEVEL 0 TAG b_whole_l0
DATABASE PLUS ARCHIVELOG;
}
created script backup_whole
Creates a stored script called backup_whole that backs up the database and archived redo logs
CREATE GLOBAL SCRIPT global_backup_db
COMMENT "backup any database from the recovery catalog, with logs"
{
BACKUP DATABASE PLUS ARCHIVELOG;
}
Verify the status of backups and copies recorded in the RMAN repository against media such as disk or tape.
The CROSSCHECK command only processes files created on the same device type as the channels running the crosscheck.
Status
Description
AVAILABLE
The Object is available for use by RMAN. For a backup set to be AVAILABLE, all set backup pieces must be AVAILABLE.
EXPIRED
Object is not found either in file system (for DISK) or in the media manager (for sbt).
Note that for a backup set to be EXPIRED, all backup pieces in the set must be EXPIRED. EXPIRED does not mean the same as OBSOLETE.
UNAVAILABLE
Object is not available for use by RMAN. For a backup set to be UNAVAILABLE, all backup pieces must be UNAVAILABLE.
The following objects can be crosschecked:
ARCHIVELOG
BACKUP
BACKUPPIECE
BACKUPSET
CONTROLFILECOPY
COPY
DATAFILECOPY
PROXY
CROSSCHECK ARCHIVELOG
Allocate a channel and crosscheck all archivelogs
CROSSCHECK <archivelogRecordSpecifier> ALL;
RMAN> RUN {
ALLOCATE CHANNEL d1 FOR MAINTENANCE DEVICE TYPE DISK;
CROSSCHECK ARCHIVELOG ALL;
RELEASE CHANNEL;
}
CROSSCHECK BACKUP
Crosscheck all backup sets
CROSSCHECK BACKUP;
RMAN> CROSSCHECK BACKUP;
using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_08_21\ O1_MF_ANNNN_TAG20190821T220191_753RP3L2_.BKP RECID=3 STAMP=759794515
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_08_21\ O1_MF_ANNNN_TAG20190821T220191_753RPK00_.BKP RECID=4 STAMP=759794528
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_08_22\ O1_MF_ANNNN_TAG20190822T223438_756GZJX9_.BKP RECID=6 STAMP=759882880
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_08_22\ O1_MF_ANNNN_TAG20190822T223438_756GZHTV_.BKP RECID=7 STAMP=759882879
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_09_01\ O1_MF_ANNNN_TAG20190901T220194_760RSVXS_.BKP RECID=10 STAMP=760744907
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_09_01\ O1_MF_ANNNN_TAG20190901T220194_760RSWMK_.BKP RECID=11 STAMP=760744907
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_09_01\ O1_MF_ANNNN_TAG20190901T220194_760RTO1Y_.BKP RECID=12 STAMP=760744933
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_09_01\ O1_MF_ANNNN_TAG20190901T220194_760RTRQX_.BKP RECID=13 STAMP=760744933
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_09_01\ O1_MF_ANNNN_TAG20190901T220194_760RV4Y8_.BKP RECID=14 STAMP=760744948
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_09_01\ O1_MF_ANNNN_TAG20190901T220194_760RVBSW_.BKP RECID=15 STAMP=760744950
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_09_01\ O1_MF_ANNNN_TAG20190901T220194_760RVJC7_.BKP RECID=16 STAMP=760744959
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_09_01\ O1_MF_ANNNN_TAG20190901T220194_760RVN0Z_.BKP RECID=17 STAMP=760744963
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_09_01\ O1_MF_ANNNN_TAG20190901T220194_760RVW2G_.BKP RECID=18 STAMP=760744972
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_09_01\ O1_MF_ANNNN_TAG20190901T220540_760S15X9_.BKP RECID=20 STAMP=760745141
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\AUTOBACKUP\2019_09_01\ O1_MF_S_760745144_760S19CQ_.BKP RECID=21 STAMP=760745145
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\BACKUPSET\2019_09_02\ O1_MF_NNNDF_TAG20190902T204201_7638JCBQ_.BKP RECID=22 STAMP=760826523
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\AUTOBACKUP\2019_09_02\ O1_MF_S_760826527_7638JJQX_.BKP RECID=23 STAMP=760826528
Crosschecked 17 objects
Allocate a channel, crosscheck backups in a date range and release the channel after use
CROSSCHECK BACKUP DEVICE ....
RMAN> RUN {
ALLOCATE CHANNEL d1 FOR MAINTENANCE DEVICE TYPE DISK;
CROSSCHECK BACKUP DEVICE TYPE DISK COMPLETED BETWEEN '01-JUL-19' AND '30-SEP-19';
RELEASE CHANNEL;
}
Crosscheck a controlfile backup
CROSSCHECK BACKUP OF CONTROLFILE;
RMAN> CROSSCHECK BACKUP OF CONTROLFILE;
Crosscheck all database datafile backups
CROSSCHECK BACKUP OF DATABASE;
RMAN> CROSSCHECK BACKUP OF DATABASE;
Crosscheck a datafile backup
CROSSCHECK BACKUP OF DATAFILE <datafile_number>;
RMAN> CROSSCHECK BACKUP OF DATAFILE 7;
Crosscheck the SPFILE backup
CROSSCHECK BACKUP OF SPFILE;
RMAN> CROSSCHECK BACKUP OF SPFILE;
Crosscheck a tablespace backup
CROSSCHECK BACKUP OF TABLESPACE <tablespace_name>;
RMAN> CROSSCHECK BACKUP OF TABLESPACE uwdata;
CROSSCHECK BACKUPPIECE
Checks that specific backuppieces still exist
CROSSCHECK BACKUPPIECE <backuppiece_identifier>;
RMAN> CROSSCHECK BACKUPPIECE 4;
CROSSCHECK BACKUPSET
Checks that specific backupsets still exist
CROSSCHECK BACKUPSET OF <backupset identifier>;
RMAN> CROSSCHECK BACKUPSET OF TABLESPACE UWDATA;
CROSSCHECK COPY
Checks for the physical existence of archivelog files and will change the V$ARCHIVED_LOG.STATUS of affected archivelogs from "A" for AVAILABLE to "X" for EXPIRED.
Once the archivelog file has a status of X, RMAN will no longer attempt to backup this archivelog file.
CROSSCHECK COPY OF ARCHIVELOG [ALL];
RMAN> CROSSCHECK COPY OF ARCHIVELOG ALL;
CROSSCHECK CONTROLFILECOPY
Checks that specific controlfile copies still exist
To delete physical backups and copies as well as do the following:
Update their repository records in the target control file to status DELETED
Remove their repository records from the recovery catalog (if you use a catalog)
When running RMAN interactively, DELETE displays a list of the files and prompts you for confirmation before deleting any file in the list. When reading commands from a command file, RMAN will not prompt for confirmation.
DELETE ARCHIVELOG
Wildcard Delete
DELETE ARCHIVELOG <ALL | FROM | HIGH | LIKE | LOGSEQ | LOW | QUOTED_STRING | SCN | SEQUENCE | TIME | UNTIL> ....
RMAN> LIST ARCHIVELOG LIKE '%';
RMAN> CROSSCHECK ARCHIVELOG LIKE '%';
RMAN> DELETE ARCHIVELOG LIKE '%';
Delete archivelogs older than 7 days if they have been backed up to tape
RMAN> DELETE NOPROMPT ARCHIVELOG ALL UNTIL 'SYSDATE-7'
BACKED UP 1 TIMES TO TAPE;
DELETE BACKUP
Delete Backup Set
DELETE [NOPROMPT] BACKUP OF DATABASE COMPLETED BEFORE '<timestamp>';
RMAN> DELETE NOPROMPT BACKUP OF DATABASE COMPLETED BEFORE 'SYSDATE-7';
# back up datafile to disk and tape
RMAN> BACKUP DEVICE TYPE DISK DATAFILE 1 TAG "weekly_bkup";
RMAN> BACKUP DEVICE TYPE sbt DATAFILE 1 TAG "weekly_bkup";
# manually allocate disk and sbt channels
RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE SBT;
RMAN> DELETE BACKUPSET TAG "weekly_bkup";
starting full resync of recovery catalog
full resync complete
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=144 device type=DISK
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 140 17-AUG-19
Backup Piece 141 17-AUG-19 C:\STAGE\ORABASE\BACKUPSET\2019_08_17\O1_MF_NNSNF_TAG20190817T101852_74QXZX3F_.BKP
Backup Set 354 22-AUG-19
Backup Piece 357 22-AUG-19 C:\APPS19\RECOVERY_AREA\ORABASE\AUTOBACKUP\2019_08_22\O1_MF_S_759882888_756GZSYR_.BKP
Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=C:\STAGE\ORABASE\BACKUPSET\2019_08_17\ O1_MF_NNSNF_TAG20190817T101852_74QXZX3F_.BKP RECID=1 STAMP=759406733
deleted backup piece
backup piece handle=C:\APPS19\RECOVERY_AREA\ORABASE\AUTOBACKUP\2019_08_22\ O1_MF_S_759882888_756GZSYR_.BKP RECID=8 STAMP=759882889
Deleted 2 objects
DELETE SCRIPT
Deletes a stored script b_whole_10 from the recovery catalog
Use to list the column definitions of a table or view just as you would in SQL*Plus.
To access a table or view in another schema, you must have READ ANY TABLE or SELECT [ANY TABLE] privileges on the object or connect in AS SYSDBA mode. The SYSBACKUP privilege does not grant access to user tables or views.
Use to create a full or subset copy of a source database as either a primary or standby database. Database duplication can be performed in any of the following modes:
Active Duplication: RMAN duplicates the files directly from either an open or mounted database. Active duplication can use image copies or backup sets. Backup sets offer several advantages, including unused block compression and encryption.
Backup-based duplication without a target connection: Creates duplicate files from pre-existing RMAN backups or copies. The DUPLICATE command must have been issued with the DATABASE clause.
This form requires a connection to an auxiliary instance and a recovery catalog and is useful when the target database is not available or a connection to it is not desirable (as mandated by security policy restrictions or a firewall).
Backup-based duplication with a target connection: RMAN creates duplicate files from pre-existing RMAN backups and copies.
Backup-based duplication without a connection to target or a recovery catalog: RMAN creates duplicate files from RMAN backups and copies that were placed in a designated BACKUP LOCATION
This full syntax of the DUPLICATE command is extensive. Only a single example has been placed in the library for now and others will be added from time-to-time.
-- The database needs to be running nomount before running this, this can be achieved by creating a very simple pfile with the database name in it
RMAN> CONNECT AUXILIARY /
DUPLICATE DATABASE TO &1
BACKUP LOCATION '&4'
-- location must include controlfile auto backups and spfile
NOREDO
-- specify this in backup based duplication without a target#
UNTIL TIME "TO_DATE('2021-05-07
12:00:00','YYYY-MM-DD HH24:MI:SS')"
SPFILE
-- must specify this or spfile is not created
PARAMETER_VALUE_CONVERT ('&5','&1',
'&6','&2') -- case sensitive
SET memory_max_target='&3G'
SET memory_target='&3G'
SET diagnostic_dest='&7'
SET audit_file_dest='&7/adump'
SET audit_sys_operations='FALSE'
SET local_listener='LSNR_&2'
SET db_recovery_file_dest_size='&9G'
RESET remote_login_passwordfile
-- a remote login password file is not recommend
SET log_archive_format='&1_%t_%s_%r.arc'
SET large_pool_size='128M'
DB_FILE_NAME_CONVERT ('&5','&1')
LOGFILE
-- specify if not being created automatically in the FRA
GROUP 1 ('/u01/oradata/oradata/log01a.rdo',
'/u02/orabase21fra/onlinelog/log01b.rdo') SIZE &8M REUSE,
GROUP 2 ('/u01/oradata/oradata/log02a.rdo',
'/u02/orabase21fra/onlinelog/log02b.rdo') SIZE &8M REUSE,
GROUP 3 ('/u01/oradata/oradata/log03a.rdo',
'/u02/orabase21fra/onlinelog/log03b.rdo') SIZE &8M REUSE,
GROUP 4 ('/u01/oradata/oradata/log04a.rdo',
'/u02/orabase21fra/onlinelog/log04b.rdo') SIZE &8M REUSE;
Duplicate To
DUPLICATE TO '<database_name>' <duplicate_options_list>;
List of Archived Log Copies for database with db_unique_name ORABASE
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
328 1 81 A 22-AUG-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_08_22\ O1_MF_1_81_756DZFMK_.ARC
334 1 82 A 22-AUG-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_08_22\ O1_MF_1_82_756GZD5V_.ARC
375 1 83 A 22-AUG-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_08_23\ O1_MF_1_83_75857O98_.ARC
376 1 84 A 23-AUG-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_08_23\ O1_MF_1_84_7591D1GN_.ARC
399 1 85 A 23-AUG-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_08_24\ O1_MF_1_85_75BPPFYR_.ARC
400 1 86 A 24-AUG-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_08_24\ O1_MF_1_86_75CNYOJM_.ARC
401 1 87 A 24-AUG-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_08_25\ O1_MF_1_87_75CXRLPG_.ARC
402 1 88 A 25-AUG-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_08_26\ O1_MF_1_88_75HBX57T_.ARC
403 1 89 A 26-AUG-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_08_26\ O1_MF_1_89_75J5VGJ3_.ARC
457 1 90 A 26-AUG-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_08_26\ O1_MF_1_90_75JYHMQG_.ARC
458 1 91 A 26-AUG-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_08_27\ O1_MF_1_91_75L0THTC_.ARC
459 1 92 A 27-AUG-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_08_28\ O1_MF_1_92_75OVMJL2_.ARC
460 1 93 A 28-AUG-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_08_28\ O1_MF_1_93_75P2KPCQ_.ARC
461 1 94 A 28-AUG-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_08_29\ O1_MF_1_94_75RTPH0Z_.ARC
462 1 95 A 29-AUG-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_08_29\ O1_MF_1_95_75RWGKC7_.ARC
463 1 96 A 29-AUG-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_08_30\ O1_MF_1_96_75VDJQ3V_.ARC
464 1 97 A 30-AUG-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_08_30\ O1_MF_1_97_75VLKJSC_.ARC
465 1 98 A 30-AUG-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_08_31\ O1_MF_1_98_75X31SPG_.ARC
466 1 99 A 31-AUG-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_08_31\ O1_MF_1_99_75Y4CRM1_.ARC
467 1 100 A 31-AUG-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_08_31\ O1_MF_1_100_75Y81LJM_.ARC
468 1 101 A 31-AUG-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_09_01\ O1_MF_1_101_760RNFGN_.ARC
469 1 102 A 01-SEP-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_09_01\ O1_MF_1_102_760RSMC7_.ARC
535 1 103 A 01-SEP-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_09_01\ O1_MF_1_103_760S131Y_.ARC
574 1 104 A 01-SEP-19
Name: C:\APPS19\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_09_02\ O1_MF_1_104_762PBWH4_.ARC
LIST BACKUP
List cataloged backups
LIST BACKUP [SUMMARY]
RMAN> LIST BACKUP;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 17.95M DISK 00:00:01 25-JUN-19
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20190625T145431
Piece Name: C:\U01\ORABASE19\FAST_RECOVERY_AREA\ORABASEXIX\AUTOBACKUP\2019_06_25\ O1_MF_S_1011884071_GK4YZR0L_.BKP
SPFILE Included: Modification time: 25-JUN-19
SPFILE db_unique_name: ORABASEXIX
Control File Included: Ckp SCN: 1980266 Ckp time: 25-JUN-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 17.95M DISK 00:00:01 25-JUN-19
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20190625T211149
Piece Name: C:\U01\ORABASE19\FAST_RECOVERY_AREA\ORABASEXIX\AUTOBACKUP\2019_06_25\ O1_MF_S_1011906709_GK5O362W_.BKP
SPFILE Included: Modification time: 25-JUN-19
SPFILE db_unique_name: ORABASEXIX
Control File Included: Ckp SCN: 2016227 Ckp time: 25-JUN-19
List cataloged backups summary
RMAN> LIST BACKUP SUMMARY;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
238 B A A DISK 21-AUG-19 1 1 NO TAG20190821T220191
239 B A A DISK 21-AUG-19 1 1 NO TAG20190821T220191
340 B A A DISK 22-AUG-19 1 1 NO TAG20190822T223438
341 B A A DISK 22-AUG-19 1 1 NO TAG20190822T223438
474 B A A DISK 01-SEP-19 1 1 NO TAG20190901T220194
475 B A A DISK 01-SEP-19 1 1 NO TAG20190901T220194
476 B A A DISK 01-SEP-19 1 1 NO TAG20190901T220194
477 B A A DISK 01-SEP-19 1 1 NO TAG20190901T220194
478 B A A DISK 01-SEP-19 1 1 NO TAG20190901T220194
479 B A A DISK 01-SEP-19 1 1 NO TAG20190901T220194
480 B A A DISK 01-SEP-19 1 1 NO TAG20190901T220194
481 B A A DISK 01-SEP-19 1 1 NO TAG20190901T220194
482 B A A DISK 01-SEP-19 1 1 NO TAG20190901T220194
542 B A A DISK 01-SEP-19 1 1 NO TAG20190901T220540
553 B F A DISK 01-SEP-19 1 1 NO TAG20190901T220544
579 B F A DISK 02-SEP-19 1 1 NO TAG20190902T204201
588 B F A DISK 02-SEP-19 1 1 NO TAG20190902T204207
LIST BACKUPPIECE
LIST BACKUPPIECE <integer | string | tag>...;
RMAN> LIST
BACKUPPIECE 'C:\U01\ORABASE19\FAST_RECOVERY_AREA\ORABASEXIX\AUTOBACKUP\2019_06_25\ O1_MF_S_1011906709_GK5O362W_.BKP';
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
2 2 1 1 AVAILABLE DISK C:\U01\ORABASE19\FAST_RECOVERY_AREA\ORABASEXIX\AUTOBACKUP\2019_06_25\ O1_MF_S_1011906709_GK5O362W_.BKP
LIST BACKUPSET
List cataloged backup sets
LIST BACKUPSET [COMPLETED BEFORE <date_string>];
# rman target /
RMAN> LIST BACKUPSET;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1 1.09G DISK 00:00:14 19-JAN-19
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20190119T160739
Piece Name: C:\APP\ORACLE\RECOVERY_AREA\ORABASE\BACKUPSET\2019_01_19\ O1_MF_ANNNN_TAG20180119T160739_8HPFBDK8_.BKP
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
2 301.34M DISK 00:00:04 19-JAN-19
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20180119T160807
Piece Name: C:\APP\ORACLE\RECOVERY_AREA\ORABASE\BACKUPSET\2019_01_19\ O1_MF_ANNNN_TAG20180119T160807_8HPFC7P7_.BKP
RMAN> LIST BACKUPSET COMPLETED BEFORE
'01-JAN-2019';
specification does not match any backup in the repository
RMAN> LIST BACKUPSET COMPLETED BEFORE
'01-JUL-2019';
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 17.95M DISK 00:00:01 25-JUN-19
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20190625T145431
Piece Name: C:\U01\ORABASE19\FAST_RECOVERY_AREA\ORABASEXIX\AUTOBACKUP\2019_06_25\ O1_MF_S_1011884071_GK4YZR0L_.BKP
SPFILE Included: Modification time: 25-JUN-19
SPFILE db_unique_name: ORABASEXIX
Control File Included: Ckp SCN: 1980266 Ckp time: 25-JUN-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 17.95M DISK 00:00:01 25-JUN-19
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20190625T211149
Piece Name: C:\U01\ORABASE19\FAST_RECOVERY_AREA\ORABASEXIX\AUTOBACKUP\2019_06_25\ O1_MF_S_1011906709_GK5O362W_.BKP
SPFILE Included: Modification time: 25-JUN-19
SPFILE db_unique_name: ORABASEXIX
Control File Included: Ckp SCN: 2016227 Ckp time: 25-JUN-19
LIST CONTROLFILECOPY
LIST CONTROLFILECOPY <integer | string | tag> ...;
RMAN> LIST CONTROLFILECOPY 12;
LIST COPY
List cataloged backup copies
LIST COPY ...;
RMAN> LIST COPY;
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
List of Archived Log Copies for database with db_unique_name ORABASE
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
47 1 233 A 19-JAN-19
Name: C:\APP\ORACLE\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_01_20\ O1_MF_1_233_8HRCMOM2_.ARC
48 1 234 A 20-JAN-19
Name: C:\APP\ORACLE\RECOVERY_AREA\ORABASE\ARCHIVELOG\2019_01_20\ O1_MF_1_234_8HRCNSX0_.ARC
LIST DATAFILECOPY
LIST DATAFILECOPY <all | like | tag>;
RMAN> LIST DATAFILECOPY ALL;
specification does not match any datafile copy in the repository
LIST DB_UNIQUE_NAME
List all database unique names
LIST DB_UNIQUE_NAME ...;
RMAN> LIST DB_UNIQUE_NAME ALL;
List of Databases
DB Key DB Name DB ID Database Role Db_unique_name
------- ------- ----------------- --------------- ------------------
1 ORABASE 1824802046 PRIMARY ORABASE
LIST EXPIRED
List expired backups for database standby1
LIST EXPIRED <ARCHIVELOG | BACKUP | BACKUPPIECE | BACKUPSET | CONTROLFILECOPY | COPY | DATAFILECOPY | FOREIGN | PROXY> [<ALL | FROM | HIGH | LIKE | LOW | SCN | SEQUENCE | TIME | UNTIL>;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
2103 CRITICAL OPEN 15-JAN-19 SQL dictionary health check: check seq$.partcount range 106 on object SEQ$ failed
2100 CRITICAL OPEN 15-JAN-19 SQL dictionary health check: check seq$.partcount range 106 on object SEQ$ failed
2097 CRITICAL OPEN 15-JAN-19 SQL dictionary health check: check seq$.partcount range 106 on object SEQ$ failed
2094 CRITICAL OPEN 15-JAN-19 SQL dictionary health check: check seq$.partcount range 106 on object SEQ$ failed
1194 CRITICAL OPEN 15-JAN-19 SQL dictionary health check: obj$.type# 48 on object OBJ$ failed
1188 CRITICAL OPEN 15-JAN-19 SQL dictionary health check: obj$.namespace 47 on object OBJ$ failed
1048 CRITICAL OPEN 08-JAN-19 SQL dictionary health check: ecol$.tabobj#,colnum fk 146 on object ECOL$ failed
1045 CRITICAL OPEN 08-JAN-19 SQL dictionary health check: check seq$.partcount range 106 on object SEQ$ failed
-- also see Advise Failure Demo Above
LIST FOREIGN
LIST FOREIGN ARCHIVELOG <ALL | FROM | HIGH | LIKE | LOW | SCN | SEQUENCE | TIME | UNTIL> ....;
RMAN> LIST FOREIGN ARCHIVELOG ALL;
specification does not match any foreign archived log in the repository
LIST INCARNATION [OF DATABASE ['<database_name>']] ;
RMAN> LIST INCARNATION;
List of Database Incarnations
DBKey Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ----------- -------- ---------- ----------
1 1 ORABASE 2611279002 PARENT 1 27-SEP-19
2 2 ORABASE 2611279002 CURRENT 2265381 03-DEC-19
LIST PREPLUGIN
LIST PREPLUGIN <ARCHIVELOG | BACKUP |
BACKUPPIECE | BACKUPSET | CONTROLFILECOPY | COPY | DATAFILECOPY | FOREIGN | PROXY> [<ALL | FROM | HIGH | LIKE | LOW | SCN | SEQUENCE | TIME | UNTIL>;
Formerly BLOCKRECOVER this was renamed as of version 11.1.0.6.
Block media recovery recovers an individual data block or set of data blocks within a datafile. This type of recovery is useful if the data loss or corruption applies to a small number of blocks rather than to an entire datafile.
Typically, block corruption is reported in error messages in trace files. Block-level data loss usually results from:
RMAN stores metadata in the control file of each target database on which it performs operations and, if available, in an RMAN repository.
As part of a backup and recovery strategy reports should be run to review what has been backed up and to determine which datafiles need backups or which files have not been backed up recently.
Reports can also be used to preview which backups RMAN would need to be restored in the event of an issue. Reports can also be used to monitor and manage space usage and to support a program of regular obsolete backup deletion.
In addition use reports to obtain historical information about RMAN jobs.
REPORT <DEVICE | NEED | OBSOLETE | SCHEMA | UNRECOVERABLE>
REPORT NEED BACKUP
Report objects whose backup is not current
REPORT NEED BACKUP [DEVICE TYPE <device_type_name>];
RMAN> REPORT NEED BACKUP;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------
1 0 C:\APPS19\ORADATA\ORABASE\SYSTEM01.DBF
2 0 C:\APPS19\ORADATA\ORABASE\SYSAUX01.DBF
3 0 C:\APPS19\ORADATA\ORABASE\UNDOTBS01.DBF
4 0 C:\APPS19\ORADATA\ORABASE\USERS01.DBF
5 0 C:\APPS19\ORADATA\ORABASE\EXAMPLE01.DBF
6 0 C:\APPS19\ORADATA\ORABASE\UWDATA01.DBF
7 0 C:\APPS19\ORADATA\ORABASE\CATTBS01.DBF
RMAN> REPORT NEED BACKUP DEVICE TYPE DISK;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------
1 0 C:\APPS19\ORADATA\ORABASE\SYSTEM01.DBF
2 0 C:\APPS19\ORADATA\ORABASE\SYSAUX01.DBF
3 0 C:\APPS19\ORADATA\ORABASE\UNDOTBS01.DBF
4 0 C:\APPS19\ORADATA\ORABASE\USERS01.DBF
5 0 C:\APPS19\ORADATA\ORABASE\EXAMPLE01.DBF
6 0 C:\APPS19\ORADATA\ORABASE\UWDATA01.DBF
7 0 C:\APPS19\ORADATA\ORABASE\CATTBS01.DBF
REPORT OBSOLETE
Reports obsolete backups found
REPORT OBSOLETE;
RMAN> REPORT OBSOLETE;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1 no obsolete backups found
REPORT SCHEMA
Performs a catalog resync and reports on permanent, undo, and temp tablespaces
REPORT SCHEMA;
RMAN> REPORT SCHEMA;
Report of database schema for database with db_unique_name ORABASE
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- --------------------------------------
1 730 SYSTEM *** C:\APPS19\ORADATA\ORABASE\SYSTEM01.DBF
2 660 SYSAUX *** C:\APPS19\ORADATA\ORABASE\SYSAUX01.DBF
3 65 UNDOTBS1 *** C:\APPS19\ORADATA\ORABASE\UNDOTBS01.DBF
4 5 USERS *** C:\APPS19\ORADATA\ORABASE\USERS01.DBF
5 345 EXAMPLE *** C:\APPS19\ORADATA\ORABASE\EXAMPLE01.DBF
6 250 UWDATA *** C:\APPS19\ORADATA\ORABASE\UWDATA01.DBF
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- ---------- ----------- ------------------------------------
1 20 TEMP 32767 C:\APPS19\ORADATA\ORABASE\TEMP01.DBF
REPORT UNRECOVERABLE
Report unrecoverable objects
REPORT UNRECOVERABLE
RMAN> REPORT UNRECOVERABLE;
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
5 full C:\APPS19\ORADATA\ORABASE\EXAMPLE01.DBF
Reset the incarnation of the target database in the RMAN repository to a previous database incarnation
RESET DATABASE TO INCARNATION <incarnation_identifier>;
$ rman target / nocatalog
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN> ALTER DATABASE MOUNT;
RMAN> LIST INCARNATION OF DATABASE orabase;
RMAN> RESET DATABASE TO INCARNATION 1;
RMAN> RESTORE DATABASE UNTIL SCN 4208974;
RMAN> RECOVER DATABASE UNTIL SCN 4208974;
RMAN> ALTER DATABASE OPEN RESETLOGS;
RMAN> LIST INCARNATION OF DATABASE orabase;
Restore files from backup sets or from disk copies to the default or a new location
RESTORE <ARCHIVELOG | CHANNEL | CHECK | CONTROLFILE | DATABASE | DATAFILE | DEVICE | FORCE | FROM | HIGH | PREVIEW
[SUMMARY] | PRIMARY | FROM SERVICE | SKIP READONLY | SPFILE | STANDBY | TABLESPACE | TO RESTORE POINT | UNTIL |
UNTIL RESTORE POINT | VALIDATE>;
RESTORE ARCHIVELOG
Restore an Archived Redo Log
RESTORE ARCHIVELOG FROM <system_change_number> [PREVIEW];
Starting restore at 04-APR-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
datafile 13 will be created automatically during restore operation
datafile 17 will be created automatically during restore operation
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/04/2018 08:15:37
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 16 found to restore
RMAN-06023: no backup or copy of datafile 15 found to restore
RMAN-06023: no backup or copy of datafile 14 found to restore
RMAN-06023: no backup or copy of datafile 12 found to restore
RMAN-06023: no backup or copy of datafile 11 found to restore
RMAN-06023: no backup or copy of datafile 10 found to restore
RMAN-06023: no backup or copy of datafile 9 found to restore
RMAN-06023: no backup or copy of datafile 8 found to restore
RMAN-06023: no backup or copy of datafile 7 found to restore
RMAN-06023: no backup or copy of datafile 6 found to restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
RMAN> restore database preview;
RESTORE FROM SERVICE
RESTORE <DATABASE <database_name> | DATAFILE <datafile_name>>
FROM SERVICE <tns_service_name>;
Perform a full resynchronization, which creates a snapshot control file and then copies any new or changed information from that snapshot control file to the recovery catalog.
RESYNC <FROM | CATALOG FROM>
RESYNC
Performs a full resynchronization of the target database after archiving all unarchived redo logs
RESYNC FROM DB_UNIQUE_NAME ALL;
RESYNC FROM DB_UNIQUE_NAME '<db_unique_name>';
RMAN> RESYNC CATALOG FROM CONTROLFILECOPY '/u03/backups/bkup_control01.ctl';
RMAN> RESYNC FROM DB_UNIQUE_NAME ALL;
RMAN> RESYNC FROM DB_UNIQUE_NAME 'orabase';
RESYNC CATALOG
After adding a new datafile to an existing tablespace
RESYNC CATALOG;
RMAN> RESYNC CATALOG;
Mount the target database, update the repository in the current control file with metadata from a backup control file, then open the database
RESYNC CATALOG FROM CONTROLFILECOPY '<file_name>'
$ rman target /
RMAN> STARTUP FORCE MOUNT;
RMAN> RESYNC CATALOG FROM CONTROLFILECOPY '/u01/bkup_controlfile01.ctl';
RMAN> ALTER DATABASE OPEN;
Set the value of various attributes that affect RMAN behavior for the duration of a RUN block or a session
SET <ARCHIVELOG | AUXILIARY | BACKUP | COMPRESSION | DECRYPTION | ECHO | ENCRYPTION | IDENTIFIED BY | MAXCORRUPT | NEWNAME | TO RESTORE | UNTIL>
ARCHIVELOG
Set the archivelog destination
SET ARCHIVELOG DESTINATION TO '<path>'
TBD
AUXILIARY INSTANCE
Specifies the path to the parameter file to use in starting the instance. You can use this parameter when customizing TSPITR with an automatic auxiliary instance or when cloning RMAN tablespaces with RMAN.
SET AUXILIARY INSTANCE PARAMETER FILE TO '<file_name>
TBD
BACKUP
Set the number of backup copies
SET BACKUP COPIES <integer>
CONFIGURE ARCHIVELOG COPIES FOR DEVICE TYPE sbt TO 3;
CONFIGURE DATAFILE COPIES FOR DEVICE TYPE sbt TO 3;
RMAN> run {
ALLOCATE CHANNEL dev1 DEVICE TYPE sbt;
SET BACKUP COPIES 2;
BACKUP DATABASE PLUS ARCHIVELOG;
}
COMMAND_ID
Sets the command string in v$session.client_info
SET COMMAND_ID='<string>'
RUN {
set command_id='RMAN is active';
}
SQL> SELECT client_info FROM v$session ORDER BY 1;
RMAN> RUN {
ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '/u01/%U';
ALLOCATE CHANNEL d2 DEVICE TYPE DISK FORMAT '/u02/%U';
SET COMMAND ID TO 'rman';
BACKUP INCREMENTAL LEVEL 0 DATABASE;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
}
COMPRESSION
Set the compression algorithm
SET COMPRESSION ALGORITHM '<compression_algorithm_name>'
TBD
CONTROLFILE AUTO BACKUP
Automates control file backups
SET CONTROLFILE AUTO BACKUP [FORMAT FOR DEVICE TYPE <deviceSpecifier> TO <formatSpec>]
SET CONTROLFILE AUTO BACKUP;
DBID
Sets the DBID for subsequent actions within the RMAN session
SET DBID <integer>
RMAN> SET DBID 3257174182;
RMAN> STARTUP FORCE NOMOUNT;
RMAN> RUN {
ALLOCATE CHANNEL t1 DEVICE TYPE sbt;
RESTORE SPFILE FROM AUTOBACKUP;
}
STARTUP FORCE; # RMAN restarts database with restored SPFILE
$ rman target /
RMAN> STARTUP FORCE NOMOUNT
RMAN> SET DBID 28014364;
RMAN> RUN {
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/cf_%F.bak';
RESTORE CONTROLFILE FROM AUTOBACKUP MAXSEQ 100;
}
ALTER DATABASE MOUNT;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
DECRYPTION
Set the password for backup decryption
SET DECRYPTION IDENTIFIED BY <password>
RMAN> set decryption identified by "N0Way!";
executing command: SET decryption
ECHO
Echo commands to the terminal
SET ECHO <ON | OFF>
RMAN> set echo on;
echo set on
RMAN> set echo off;
set echo off;
echo set off
ENCRYPTION
Set the encryption algorithm
SET ENCRYPTION ALGORITHM '<algorithm_name>'
TBD
IDENTIFIED BY
Set the encryption password
SET IDENTIFIED BY <password> [ONLY]
TBD
MAXCORRUPT
Set the maximum number of corrupt blocks in a datafile backup
SET MAXCORRUPT FOR DATAFILE <dataFileSpec> TO <integer>
TBD
NEWNAME
Datafile name to file name
SET NEWNAME FOR DATAFILE <dataFileSpec> TO '<file_name>'
TBD
Datafile name to new
SET NEWNAME FOR DATAFILE <dataFileSpec> TO NEW
TBD
Tempfile name to Quoted String
SET NEWNAME FOR TEMPFILE <tempFileSpec> TO '<quoted_string>'
TBD
Tempfile to new
SET NEWNAME FOR TEMPFILE <tempFileSpec> TO NEW
TBD
OFF | ON
???
SET <OFF | ON> [FOR ALL TABLESPACES]
SET ON FOR ALL TABLESPACES;
TO RESTORE
Set to specified restore point
SET TO RESTORE POINT <restore_point_name>
TBD
UNTIL
Restore database and recover until the specified System Change Number
SET UNTIL SCN <scn_number>
TBD
Restore database and recover until the specified date and time
SET UNTIL TIME <date_time>
RMAN> run {
SET UNTIL TIME = "TO_DATE('03/15/20 10:42:07','MM/DD/RR HH24:MI:SS')";
RESTORE DATABASE;
RECOVER DATABASE;
}
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TEST21DB_IAD25G are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+RECO/TEST21DB_IAD25G/controlfile/snapcf_test21db_iad25g.f';
ARCHIVELOG
Archivelog
ARCHIVELOG <BACKUP | COPIES | DELETION>
RMAN> show archivelog backup copies;
RMAN configuration parameters for database with db_unique_name ORABASE19 are:
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
AUXILIARY
Auxiliary
AUXILIARY CHANNEL
RMAN> show auxiliary channel;
AUXNAME
Auxname
AUXNAME
RMAN> show auxname;
BACKUP
Backup Copies
{DATAFILE | ARCHIVELOG} BACKUP COPIES
RMAN> show datafile backup copies;
RMAN> show archivelog backup copies;
Backup Optimization
BACKUP OPTIMIZATION
RMAN> show backup optimization;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RMAN> show backup optimization;
CHANNEL
Channel
[AUXILIARY] CHANNEL [FOR DEVICE TYPE <deviceSpecifier>]
RMAN> show channel;
COMPRESSION
Compression
COMPRESSION <ALGORITHM | USING>
RMAN> show compression algorithm;
CONTROLFILE
Control File Auto-Backup
CONTROLFILE AUTOBACKUP [FORMAT <format mask>]
RMAN> show controfile autobackup;
DATAFILE
Datafile
DATAFILE BACKUP COPIES
RMAN> show datafile backup copies;
DB_UNIQUE_NAME
DB_UNIQUE_NAME
DATAFILE DB_UNIQUE_NAME
RMAN> show db_unique_name;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORABASE19 are:
RMAN configuration has no stored or default parameters
DEFAULT
Default
DEFAULT DEVICE TYPE
RMAN> show default device type;
DEVICE_TYPE
Device Type
DEVICE TYPE;
RMAN> show device type;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORABASE are:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
EXCLUDE
Exclude
EXCLUDE
$ rman target / catalog repoomega1/oracle1@repos
RMAN> show exclude;
RMAN configuration parameters for database with db_unique_name ORABASE are:
RMAN configuration has no stored or default parameters
ENCRYPTION
Encryption
ENCRYPTION <ALGORITHM | FOR | USING>
RMAN> show encryption algorithm;
RMAN> show encryption for database;
INCREMENTAL
Incremental
INCREMENTAL
RMAN> show incremental for availability machine;
RMAN configuration parameters for database with db_unique_name ORABASE19 are:
CONFIGURE INCREMENTAL FOR AVAILABILITY MACHINE OFF; # default
MAXSETSIZE
Maximum Backup Set Size
MAXSETSIZE
RMAN> show maxsetsize;
RMAN configuration parameters for database with db_unique_name ORABASE19 are:
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
RETENTION
Retention Policy
RETENTION POLICY [TO REDUNDANCY]
RMAN> show retention policy;
RMAN> show retention policy to redundancy;
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO sbt;
RMAN> SPOOL LOG TO '/tmp/current_config.log';
RMAN> SHOW ALL;
RMAN> SPOOL LOG OFF;
RMAN> SPOOL LOG TO '/home/oracle/rman.log';
RMAN> BACKUP DATABASE;
RMAN> SPOOL LOG OFF;
RMAN> spool log to
c:\temp\s201.ora append;
RMAN> show all;
RMAN> spool log off;
Spooling for log turned off
Recovery Manager19.3.0.0.0
-- output Spooling started in log file: c:\temp\s201.ora
Recovery Manager19.3.0.0.0
RMAN>
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORABASEXIX
are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; #
default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; #
default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE
FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'C:\U01\ORAHOME21\WINDOWS.X64_213000_DB_HOME\DATABASE\SNCFORABASEXIX.ORA';
# default
Specify that a datafile copy is now the current datafile, that is, the datafile pointed to by the control file. This command is equivalent to the SQL statement ALTER DATABASE RENAME FILE as it applies to datafiles.
Switch from the current datafile to a datafile copy identified by the specified tag
SWITCH DATAFILE '<data_file_specification>' TO DATAFILECOPY TAG '<tag_name>'
RMAN> SWITCH DATAFILE 14 TO DATAFILECOPY TAG uwdfcopy;
A disk fails, forcing a datafile restore to a new disk location. Connecting to the TARGET, SET NEWNAME to rename the datafile, then RESTORE the missing datafile. Run SWITCH to point the control file to the new datafile and then RECOVER.
SWITCH DATAFILE ALL
RUN {
ALLOCATE CHANNEL d1 DEVICE TYPE DISK;
SQL "ALTER TABLESPACE uwdata OFFLINE IMMEDIATE";
SET NEWNAME FOR DATAFILE '/u01/oradata/users01.dbf' TO '/u02/oradata/users01.dbf';
RESTORE TABLESPACE uwclass;
SWITCH DATAFILE ALL;
RECOVER TABLESPACE uwclass;
SQL "ALTER TABLESPACE uwclass ONLINE";
}
PLUGGABLE
???
SWITCH PLUGGABLE ???
RMAN> SWITCH PLUGGABLE ???;
TABLESPACE
Assume all datafiles of the USERS tablespace are damaged but image copies exist in the FRA. Connect to the TARGET and use SWITCH to point the control file to the new datafiles then RECOVER.
Switches the database from the current tempfile to the tempfile copy
SWITCH TEMPFILE <temp_file_specification> TO '<file_name>'
RMAN> SWITCH TEMPFILE /u01/oradata/orabase/temp01.dbf' TO '/u04/recovery_area/orabase/temp01.copy';
Following renaming of the tempfiles SWITCH TEMPFILE ALL creates the tempfiles in the new location when the database is opened
SWITCH TEMPFILE ALL;
SQL> STARTUP FORCE MOUNT;
RMAN> SET NEWNAME FOR TEMPFILE 1 TO '/u01/oradata/orabase/temp01.dbf';
RMAN> SET NEWNAME FOR TEMPFILE 2 TO '/u02/oradata/orabase/temp02.dbf';
RMAN> SWITCH TEMPFILE ALL;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
TRANSPORT TABLESPACE <tablespace_name> TABLESPACE DESTINATION <path_and_file_name> AUXILIARY DESTINATION <path_and_file_name>
UNTIL SCN <scn_number>;
RMAN> TRANSPORT TABLESPACE uwdata
TABLESPACE DESTINATION '/u01/oradata/orabase/uwdata01.dbf'
AUXILIARY DESTINATION '/u07/oradata/orabase/uwdata01.dbf'
UNTIL SCN 10912156206286;
Until Restore Point
TRANSPORT TABLESPACE <tablespace_name>
TABLESPACE DESTINATION <path_and_file_name> AUXILIARY DESTINATION <path_and_file_name>
UNTIL RESTORE POINT <restore_point_name>;
RMAN> TRANSPORT TABLESPACE uwdata
TABLESPACE DESTINATION '/u01/oradata/orabase/uwdata01.dbf'
AUXILIARY DESTINATION '/u07/oradata/orabase/uwdata01.dbf'
UNTIL RESTORE POINT 'before_damage';
Until Datetime
TRANSPORT TABLESPACE <tablespace_name> TABLESPACE DESTINATION <path_and_file_name> AUXILIARY DESTINATION <path_and_file_name>
UNTIL TIME <datetime>;
RMAN> TRANSPORT TABLESPACE uwdata
TABLESPACE DESTINATION '/u01/oradata/orabase/uwdata01.dbf'
AUXILIARY DESTINATION '/u07/oradata/orabase/uwdata01.dbf'
UNTIL TIME 'SYSDATE-6/24';
Examine a backup set and report whether its data is intact. RMAN scans all of the backup pieces in the specified backup sets and looks at the checksums to verify that the contents can be successfully restored
VALIDATE ARCHIVELOG ALL
VALIDATE ARCHIVELOG LIKE '<string_pattern>'
VALIDATE ARCHIVELOG FROM SCN <integer>
VALIDATE ARCHIVELOG BETWEEN SCN <integer> AND SCN <integer>
VALIDATE ARCHIVELOG UNTIL SCN <integer>
VALIDATE ARCHIVELOG FROM SEQUENCE <integer> [THREAD <integer>]
VALIDATE ARCHIVELOG SEQUENCE <integer> [THREAD <integer>]
VALIDATE ARCHIVELOG SEQUENCE BETWEEN <integer> AND <integer>
VALIDATE ARCHIVELOG UNTIL SEQUENCE <integer> [THREAD <integer>]
TIME BETWEEN '<date_string>' AND '<date_string>'
UNTIL TIME '<date_string>'
RMAN> VALIDATE ARCHIVELOG ALL;
BACKUPSET
Validate a backup set
VALIDATE BACKUPSET <primaryKey>;
RMAN> LIST BACKUP SUMMARY;
RMAN> VALIDATE BACKUPSET 4242;
CONTROLFILECOPY
Validate all control files copies
VALIDATE CONTROLFILECOPY ALL;
VALIDATE CONTROLFILECOPY '<file_name>';
VALIDATE CONTROLFILECOPY LIKE '<string_pattern>';
RMAN> VALIDATE CONTROLFILECOPY ALL;
COPY OF
Validate all database, datafile, or tablespace copies
VALIDATE COPY OF DATABASE;
VALIDATE COPY OF DATAFILE '<file_name>';
VALIDATE COPY OF DATAFILE <file_number>;
VALIDATE COPY OF TABLESPACE '<tablespace_name>';
RMAN> VALIDATE COPY OF TABLESPACE 'UWDATA';
CURRENT
Validate the current control file
VALIDATE CURRENT CONTROLFILE;
RMAN> VALIDATE CURRENT CONTROLFILE;
DATABASE
Validate the complete database
VALIDATE DATABASE;
RMAN> VALIDATE DATABASE;
DATAFILE
Validate the identified datafile or datafile blocks
VALIDATE DATAFILE '<file_name>' [BLOCK <starting_block> TO <ending_block>];
VALIDATE DATAFILE <file_number> [BLOCK <starting_block> TO <ending_block>];;
Using a target database controlfile instead of recovery catalog
The following were provided to us by Andrea Sparling of the University of Washington
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
RMAN> CONFIGURE BACKUP OPTIMIZATION OFF; # default
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE
TO COMPRESSED BACKUPSET; # default
RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
RMAN> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
RMAN> CONFIGURE MAXSETSIZE TO 2 G;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/dbs/snapcf_pnbdb.f';
Incremental Level 0 Backup
RMAN> connect target /
RMAN> BACKUP INCREMENTAL LEVEL 0
TAG full_backup
FORMAT '/u03/backups/rman_fullbackup_%d_%t.%s.%c.%p.bus'
DATABASE;
RMAN> COPY CURRENT CONTROLFILE TO '/u03/backups/ctrlfile.backup';
RMAN> CROSSCHECK BACKUP;
RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> DELETE OBSOLETE;
RMAN> DELETE EXPIRED ARCHIVELOG ALL;
RMAN> LIST BACKUP;
RMAN> LIST ARCHIVELOG ALL;
Incremental
#!/bin/sh
#####################################################
# script to take a rman full backup
# this script must be run
# as the trusted user 'oracle10g' 'oracle9i' etc
#####################################################
# create output logs with that number and the current date in the ./rman/logs directory age out in 14-30 days
# get env IMPORTANT, if multiple Oracle SID's the env var ORACLE_SID must be set
-- in a terminal window
# cd $ORACLE_BASE/product/oradata/orabase
# ./dra1_setup.sh
rm users01.dbf
rm example01.dbf
SQL> shutdown
SQL> startup;
rman target /nocatalog
-- list failures
RMAN> list failure;
-- list failure details
RMAN> list failure detail;
-- diagnose the failure
RMAN> advise failure;
-- view the repair script
RMAN> repair failure preview;
-- repair the error
RMAN> repair failure;
-- when asked "do you want to open the database answer "Yes"
RMAN> exit;
RAC Demo
Crosschecking on Multiple Nodes of an Oracle Real Application Clusters Configuration: Example In this example, you perform a crosscheck of backups on two nodes of an Oracle Real Application Clusters configuration,
where each node has access to a subset of backups. It is assumed here that all backups are accessible by at least one of the two nodes used in the crosscheck. Any backups not accessible from at least one of the nodes are marked EXPIRED after the crosscheck.
RMAN on a RAC Cluster
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK CONNECT 'sys/oracle1@inst1';
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK CONNECT 'sys/oracle1@inst2';
CROSSCHECK BACKUP;
RMAN Related Queries
A review of this workflow should provide you examples of actual work done to recover a database
SELECT recovery_estimated_ios, actual_redo_blks, target_redo_blks, target_mttr, estimated_mttr
FROM gv$instance_recovery;
SELECT * FROM v$recovery_file_dest;
select count(*) from dba_hist_instance_recovery;
cd /app/oracle/product/oradata/orabase
ls -la
cd /app/oracle/product/diag/rdbms/orabase/orabase/trace
tail alert_orabase.log
rm control02.ctl
ls -la
SQL> conn hr/hr (after unlocking account)
SQL> desc jobs
SQL> INSERT INTO jobs VALUES (987,'TEST',0,1);
SQL> COMMIT;
-- also build a dummy table
tail alert_orabase.log
-- no one knows anything bad has happened
SQL> select file_name from mgmt$db_controlfiles;
-- lets do a shutdown and restart
SQL> shutdown immediate;
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file:
'/app/oracle/product/oradata/orabase/control02.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
-- now we have a problem
SQL> shutdown abort;
tail the alert log and we know what happened
RUN {
STARTUP FORCE NOMOUNT;
RESTORE CONTROLFILE;
ALTER DATABASE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
}
alter database open resetlogs;
ls -la shows control files back
rman done
-- hadn't done a backup after last restore
RMAN> run {startup force nomount; restore controlfile;
alter database mount;}
Total System Global Area 440401920 bytes
Fixed Size 1219880 bytes
Variable Size 130024152 bytes
Database Buffers 306184192 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option
for database open
SQL> startup mount;
tail alert_orabase.log
-- next dumped controlfiles 1 and 3
shutdown immediate fails
shutdown abort
connect to RMAN
Determine if a corrupt block belongs to an object
SELECT owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = <absolute_file_id>
AND <corrupted_block_number> BETWEEN block_id AND block_id+blocks-1;
SELECT owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = 2
AND 78104 BETWEEN block_id AND block_id+blocks-1;
Determine if a corrupt block belongs to free space
SELECT *
FROM dba_free_space
WHERE file_id = <absolute_file_id>
AND <corrupted_block_number> BETWEEN block_id AND block_idblocks-1>;
SELECT *
FROM dba_free_space
WHERE file_id = 1
AND 101258 BETWEEN block_id AND block_id+blocks-1;
Backup and Recovery "Best Practices" and Demos
Metalink Note: 388422.1
1. Turn on block checking
REASON: The aim is to detect, very early the presence of corrupt blocks in the database. This has a slight performance overhead, but Checksums allow Oracle to detect early corruption caused by underlying disk, storage system, or I/O system problems.
SQL> alter system set db_block_checking = true scope=both;
2. Turn on block tracking when using RMAN backups (if running 10g or above)
REASON: This will allow RMAN to backup only those blocks that have changed since the last full backup, which will reduce the time taken to back up, as less blocks will be backed up.
SQL>alter database enable block change tracking using file '/u01/oradata/ora1/change_tracking.f';
3. Duplex log groups and members and have more than one archive log dest
REASON: If an archivelog is corrupted or lost, by having multiple copies in multiple locations, the other logs will still be available and could be used.
If an online log is deleted or becomes corrupt, you will have another member that can be used to recover if required.
SQL> alter system set log_archive_dest_2='location=/new/location/archive2' scope=both;
SQL> alter database add logfile member '/new/location/redo21.log' to group 1;
4. When backing up the database use the 'check logical' parameter
REASON: This will cause RMAN to check for logical corruption within a block as well as the normal head/tail check-summing. This is the best way to ensure that you will get a good backup.
RMAN> backup check logical database plus archivelog delete input;
5. Test your backup
REASON: This will do everything except actually restore the database. This is the best method to determine if your backup is good and usable before being in a situation where it is critical and issues exist.
RMAN> restore validate database;
6. Have each datafile in a single backup piece
REASON: When doing a partial restore RMAN must read through the entire piece to get the datafile/archivelog requested. The smaller the backup piece the quicker the restore can complete.
This is especially relevant with tape backups of large databases or where the restore is only on individual / few files.
RMAN> backup database filesperset 1 plus archivelog delete input;
7. Maintain your RMAN catalog/controlfile
REASON: Choose your retention policy carefully. Make sure that it compliments your tape subsystem retention policy, requirements for backup recovery strategy.
If not using a catalog, ensure that your controlfile record keep time instance parameter matches your retention policy.
SQL> alter system set control_file_record_keep_time=21 scope=both;
This will keep 21 days of backup records.
Run regular catalog maintenance.
REASON: Delete obsolete will remove backups that are outside your retention policy. If obsolete backups are not deleted, the catalog will continue to grow until performance becomes an issue.
RMAN> delete obsolete;
REASON: crosschecking will check that the catalog/controlfile matches the physical backups. If a backup is missing, it will set the piece to 'EXPIRED' so when a restore is started, that it will not be eligible, and an earlier backup will be used.
To remove the expired backups from the catalog/controlfile use the delete expired command.
8. Prepare for loss of controlfiles set autobackup on
REASON: This will ensure that you always have an up to date controlfile available that has been taken at the end of the current backup not during.
RMAN> configure controlfile autobackup on;
keep your backup logs
REASON: The backup log contains parameters for your tape access, locations on controlfile backups that can be utilised if complete loss occurs.
9. Test your recovery
REASON: During a recovery situation this will let you know how the recovery will go without actually doing it, and can avoid having to restore source datafiles again.
SQL> recover database test;
10. Do not specify 'delete all input' when backing up archivelogs
REASON: Delete all input' will backup from one destination then delete both copies of the archivelog where as 'delete input' will backup from one location andthen delete what has been backed up.
The next backup will back up those from location 2 as well as new logs from location 1, then delete all that are backed up.
This means that you will have the archivelogs since the last backup available on disk in location 2 (as well as backed up once) and two copies backup up prior to the previous backup.
Using a target database controlfile instead of recovery catalog
The following were provided to us by Andrea Sparling of the University of Washington
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
RMAN> CONFIGURE BACKUP OPTIMIZATION OFF; # default
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK
TO '%F';
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO
COMPRESSED BACKUPSET; # default
RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
RMAN> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
RMAN> CONFIGURE MAXSETSIZE TO 2 G;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'/u01/app/oracle/product/10.1.0.3/dbs/snapcf_pnbdb.f';
Incremental Level 0 Backup
RMAN> connect target /
RMAN> BACKUP INCREMENTAL LEVEL 0
TAG full_backup
FORMAT '/u03/backup/rman_fullbackup_%d_%t.%s.%c.%p.bus'
DATABASE;
RMAN> COPY CURRENT CONTROLFILE TO '/u03/backup/ctrlfile.backup';
RMAN> CROSSCHECK BACKUP;
RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> DELETE OBSOLETE;
RMAN> DELETE EXPIRED ARCHIVELOG ALL;
RMAN> LIST BACKUP;
RMAN> LIST ARCHIVELOG ALL;
Incremental Level 1 Backup
#!/bin/sh
#####################################################
# script to take a rman full backup
# this script must be run
# as the trusted user 'oracle10g' 'oracle9i' etc
#####################################################
# create output logs with that number and the current date in the ./rman/logs directory age out in 14-30 days
# get env IMPORTANT, if multiple Oracle SID's the env var ORACLE_SID must be set
-- in a terminal window
# cd $ORACLE_BASE/product/oradata/orabase
# ./dra1_setup.sh
rm users01.dbf
rm example01.dbf
SQL> shutdown
SQL> startup;
rman target /nocatalog
-- list failures
RMAN> list failure;
-- list failure details
RMAN> list failure detail;
-- diagnose the failure
RMAN> advise failure;
-- view the repair script
RMAN> repair failure preview;
-- repair the error
RMAN> repair failure;
-- when asked "do you want to open the database answer "Yes"
RMAN> exit;
Backup, then delete archivelogs as soon as they
are generated
run {
ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '/u01/backups/%U';
ALLOCATE CHANNEL d2 DEVICE TYPE DISK FORMAT '/u02/backups/%U';
send 'NB_ORA_CLIENT=ch001';
set command id to 'archive_log_backup';
crosscheck archivelog all;
backup
filesperset 1
format 'al_%d_%Y%M%D_%s_%p_%t_S%e_T%h'
archivelog all
delete input;
backup
format 'c_%d_%Y%M%D_%s_%p_%t'
current controlfile;
}
Backup all archivelogs not previously backed up,
then delete archivelogs older than the specified period
run {
ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '/u01/backups/%U';
ALLOCATE CHANNEL d2 DEVICE TYPE DISK FORMAT '/u02/backups/%U';
send 'NB_ORA_CLIENT=ch001';
set command id to 'archive_log_backup';
crosscheck archivelog all;
backup
filesperset 1
format 'al_%d_%Y%M%D_%s_%p_%t_S%e_T%h'
archivelog all
not backed up;
backup
format 'c_%d_%Y%M%D_%s_%p_%t'
current controlfile;
}
