| General |
Data Dictionary Objects |
| DBA_HIST_INSTANCE_RECOVERY |
GV_$RECOVERY_STATUS |
| DBA_RECOVERABLE_SCRIPT |
V_$FLASH_RECOVERY_AREA_USAGE |
| DBA_RECOVERABLE_SCRIPT_BLOCKS |
V_$INSTANCE_RECOVERY |
| DBA_RECOVERABLE_SCRIPT_ERRORS |
V_$RECOVER_FILE |
| DBA_RECOVERABLE_SCRIPT_PARAMS |
V_$RECOVERY_FILE_DEST |
| GV_$INSTANCE_RECOVERY |
V_$RECOVERY_FILE_STATUS |
| GV_$RECOVER_FILE |
V_$RECOVERY_LOG |
| GV_$RECOVERY_FILE_STATUS |
V_$RECOVERY_PROGRESS |
| GV_$RECOVERY_LOG |
V_$RECOVERY_STATUS |
| GV_$RECOVERY_PROGRESS |
|
|
Format Directives
|
| Format |
Description |
| %a |
Current activation id |
| %A |
Zero-filled activation ID |
| %d |
Database ID |
| %D |
Zero-filled database ID |
| %r |
Resetlogs ID |
| %s |
Sequence number |
| %S |
Zero-filled sequence number |
| %t |
Thread number |
| %T |
Zero-filled thread number |
|
|
Repository Initialization Parameters
|
compatible=10.2.0
log_archive_format=RMAN_%s_%r_%d.arc
remote_login_passwordfile=SHARED
|
| show parameter archive_format |
|
|
| Commands |
| @ |
To execute a series of RMAN commands stored in an operating system file with the specified full path name, for example, @/oracle/dbs/cmd/cmd1.rman. If you do not specify the full path name, then the current working directory is assumed, for example, @cmd1.rman. Do not use quotes around the string or leave whitespace between the @ and filename. RMAN processes the specified file as if its contents had appeared in place of the @ command.
@<file_name> |
rman TARGET / @backup_db.rcv
RMAN> @backup_db.rman
or
RMAN> RUN { @backup_db.rman } |
|
| |
| @@ |
To execute a series of RMAN commands stored in an operating system file with the specified filename, for example, @@cmd2.rman. If @@ is contained in a command file, then @@filename directs RMAN to look for the specified filename in the same directory as the command file from which it was called. If not used within a command file, the @@ command is identical to the @ command. For example, assume that you invoke RMAN as follows:
@@<file_name> |
% rman
RMAN> @$ORACLE_HOME/rdbms/admin/dba/scripts/cmd1.rcv
Assume that the command @@cmd2.rman appears inside the cmd1.rman script. In this case, the @@ command directs RMAN to look for the file cmd2.rman in the directory $ORACLE_HOME/rdbms/admin/dba/scripts/. |
|
| |
| ALLOCATE CHANNEL |
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 |
--
disk channel
RMAN> RUN
{
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK
FORMAT '/u01/backups/%U';
BACKUP DATABASE PLUS ARCHIVELOG;
} |
--
tape channel
RMAN RUN
{
ALLOCATE CHANNEL tape1 DEVICE TYPE sbt;
BACKUP DATABASE PLUS ARCHIVELOG;
} |
|
| Multiple Backup Channels |
RUN
{
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT
'/u01/backups/%U';
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT
'/u02/backups/%U';
BACKUP DATABASE PLUS ARCHIVELOG;
} |
| Multiple Backup Copies |
RUN
{
ALLOCATE CHANNEL d1 DEVICE TYPE DISK MAXPIECESIZE 5M;
BACKUP DATABASE PLUS ARCHIVELOG;
SET BACKUP COPIES = 2;
BACKUP DATAFILE 1 FORMAT '/disk1/backups/%U', '/disk2/backups/%U';
} |
| |
| ALLOCATE CHANNEL FOR MAINTENANCE |
Allocates a channel in preparation for issuing a CHANGE, DELETE, or CROSSCHECK command.
This command cannot be used within a RUN block.
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE <device_specifier> [allocOperandList]; |
| See Delete and Crosscheck Demos |
| |
| ALTER |
| ALTER DATABASE <MOUNT | OPEN | OPEN RESETLOGS>; |
STARTUP MOUNT;
BACKUP DATABASE;
ALTER DATABASE OPEN; |
STARTUP
NOMOUNT;
RESTORE CONTROLFILE;
ALTER DATABASE MOUNT;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS; |
RUN {
ALLOCATE CHANNEL ch1 DEVICE
TYPE sbt;
SET UNTIL SCN 1024;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
} |
|
| |
| BACKUP |
Back up a database, tablespace, datafile (current or copy), control file (current or copy), SPFILE, archived log, or backup set.
BACKUP [backupOperand] <backup spec> [PLUS ARCHIVELOG <backupSpecOperand>]
BACKUP AS
BACKUP BACKUPSET
BACKUP BLOCKS
BACKUP CHANNEL
BACKUP COPY
CHECK
CONTROLFILECOPY
COPIES
CUMULATIVE
CURRENT
DB_FILE_NAME_CONVERT
DISKRATIO
DURATION
FOR
FORCE
FORMAT
FULL |
| Backup Archivelog |
| BACKUP
ARCHIVELOG; |
| Backup Current Controlfile |
RUN {
BACKUP CURRENT CONTROLFILE;
} |
| Backup Database |
RUN
{
ALLOCATE CHANNEL d1 DEVICE TYPE DISK;
BACKUP DATABASE;
} |
| Backup Database and Archive
Logs |
RUN
{
ALLOCATE CHANNEL d1 DEVICE TYPE DISK;
BACKUP DATABASE PLUS ARCHIVELOG;
} |
| Backup Datafile (to two
locations) |
RUN
{
ALLOCATE CHANNEL d1 DEVICE TYPE DISK;
BACKUP DATAFILE 1 FORMAT '/u01/backups/%U', '/u02/backups/%U';
} |
| Datafile Copy |
| TBD |
| Device |
| TBD |
| DB_Recovery_File_Dest |
| TBD |
| DB_File_Name_Convert |
| TBD |
| Filesperset |
| TBD |
| Incremental Backup |
# Run once a week to back up database to disk as level 0:
BACKUP INCREMENTAL LEVEL 0 DATABASE;
# Run every day to back up blocks that have changed since most recent level 0 or 1:
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
BACKUP INCREMENTAL LEVEL 1 DIFFERENTIAL TABLESPACE users; |
| Keep |
| TBD |
| SPFILE |
| Backup / Restore
SPFILE |
| ? |
| TBD |
| |
| BEGLINE |
| descriptive text |
| |
| |
| BLOCKRECOVER |
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:
- I/O errors causing minor data loss
- Memory corruptions that get flushed to disk
|
| Recover corrupt blocks in three
datafiles |
BLOCKRECOVER DATAFILE 2 BLOCK 12, 13;
BLOCKRECOVER DATAFILE 2 BLOCK 12,13 DATAFILE 3 BLOCK 5,98,99 DATAFILE 4 BLOCK 19; |
| Limit block recovery by restoration
type |
RUN {
BLOCKRECOVER DATAFILE 3 BLOCK 2,3,4,5 TABLESPACE sales DBA 4194405, 4194409, 4194412
FROM DATAFILECOPY;
} |
| Limit block recovery by backup tag |
| BLOCKRECOVER TABLESPACE SYSTEM DBA 4194404, 4194405
FROM TAG "weekly_backup"; |
| Limit block recovery by time |
| BLOCKRECOVER TABLESPACE SYSTEM DBA 4194404, 4194405
RESTORE UNTIL TIME 'SYSDATE-2'; |
| Repair all corrupt blocks in the
database |
BACKUP VALIDATE DATABASE;
BLOCKRECOVER CORRUPTION LIST;
This command will find, list and then recover all corrupted blocks in the database using the
gv$database_block_corruption view. |
| |
| CATALOG |
| Create a Repository Catalog |
|
Repository Server (emrep on
bigdog) |
--
as oracle
$ sqlplus / as sysdba
-- using OMF
CREATE TABLESPACE cat_tbs;
SQL> CREATE USER omega
IDENTIFIED BY omega
DEFAULT TABLESPACE cat_tbs
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON cat_tbs;
GRANT create session TO omega
GRANT recovery_catalog_owner TO omega;
GRANT execute ON dbms_stats TO omega; |
|
Target Server (orcl on
omega1) as oracle user |
| create TNSNAMES
entry |
|
Target Server (orcl on
omega1) SQLPLUS Session |
Target Server (orcl on
omega1) RMAN Session |
$
sqlplus /nolog
conn omega/omega@repos
SELECT object_type, COUNT(*)
FROM user_objects
GROUP BY object_type; |
|
| |
$ rman target / catalog omega/omega@emrep
RMAN>
create catalog tablespace cat_tbs;
|
SELECT object_type, COUNT(*)
FROM user_objects
GROUP BY object_type;
exec dbms_stats.gather_schema_stats(USER, CASCADE=>TRUE);
SELECT table_name
FROM user_tables
WHERE num_rows > 0; |
|
| |
RMAN> register database;
|
SELECT object_type, COUNT(*)
FROM user_objects
GROUP BY object_type;
exec dbms_stats.gather_schema_stats(USER, CASCADE=>TRUE);
SELECT table_name
FROM user_tables
WHERE num_rows > 0;
|
|
| |
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> configure controlfile autobackup on;
|
|
| Need additions to catalog for
cataloging objects |
| |
| CHANGE |
To make the following changes:
- 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 them, and then recatalog them in the new
location |
HOST 'mv $ORACLE_HOME/oradata/trgt/arch/* /fs2/arch';
CHANGE ARCHIVELOG ALL UNCATALOG;
CATALOG START WITH '/fs2/arch'; |
Change Backup and Backupset
Change the status of backup set 100 as well as all backups of server parameter files created more than a day ago to
UNAVAILABLE |
CHANGE BACKUPSET 100 UNAVAILABLE;
CHANGE BACKUP OF SPFILE COMPLETED BEFORE 'SYSDATE-1' UNAVAILABLE; |
Change Backup Tag
Change an ordinary backup into a long-term backup |
| CHANGE BACKUP TAG
'consistent_db_bkup' KEEP FOREVER NOLOGS; |
| Change Backuppiece |
| TBD |
| Change Backupset |
| TBD |
| Change Controlfilecopy |
| TBD |
| Change Copy |
| TBD |
| Change Datafilecopy |
| TBD |
| Change Proxy |
| TBD |
| |
| CONFIGURE |
| 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. |
CONFIGURE AUXNAME FOR DATAFILE
CONFIGURE CONTROLFILE AUTOBACKUP |
| See Catalog Demo |
-- Production configuration
demo from Andrea Sparling:
$ rman
RMAN> SHOW ALL
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
# default
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'; # default
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
# default
RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
# default
RMAN> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
# default
RMAN> CONFIGURE MAXSETSIZE TO 2 G;
RMAN> CONFIGURE ENCRYPTION FOR DATABASE
OFF;
# default
RMAN> CONFIGURE ENCRYPTION ALGORITHM
'AES128';
# default
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
# default
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME
TO '/u01/app/oracle/product/ora102/dbs/snapcf_repos.f';
# default |
| |
| CONNECT |
Establish a connection between RMAN and a target, auxiliary, or recovery catalog database.
CONNECT AUXILIARY
CONNECT CATALOG
CONNECT TARGET |
| Connect to
local RMAN catalog |
$ rman
RMAN> connect omega1/omega1@emrep |
| Connect to
target server with a catalog |
$ rman
RMAN> connect target omega1/omega1@emrep |
| Connect to
target server without a catalog |
$ rman
RMAN> connect target omega1/omega1@emrep NOCATALOG |
| |
| CONVERT |
| To convert a datafile, tablespace or database to the format of a destination platform, in preparation for transport across different platforms. |
| |
| |
| COPY |
| descriptive text |
| copy demo |
| |
| CROSSCHECK |
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 |
Object is available for use by RMAN. For a backup set to be AVAILABLE, all backup pieces in the set must have the status 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 in the set must have the status UNAVAILABLE. |
The following objects can be crosschecked:
| ARCHIVELOG |
BACKUP |
BACKUPPIECE |
BACKUPSET |
CONTROLFILECOPY |
COPY |
DATAFILECOPY |
PROXY |
|
RMAN> RUN
{
ALLOCATE CHANNEL disk1 FOR
MAINTENANCE DEVICE TYPE DISK;
CROSSCHECK ARCHIVELOG ALL;
} |
| |
| DEBUG |
| descriptive text |
| debug demo |
| |
| DELETE |
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 Backup Set |
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE sbt;
DELETE NOPROMPT BACKUP OF DATABASE COMPLETED BEFORE 'SYSDATE-7'; |
| Delete both Disk and Tape Backup Sets |
# back up datafile to disk and tape
BACKUP DEVICE TYPE DISK DATAFILE 1 TAG "weekly_bkup";
BACKUP DEVICE TYPE sbt DATAFILE 1 TAG "weekly_bkup";
# manually allocate gsbt channel and disk channel
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE sbt;
DELETE BACKUPSET TAG "weekly_bkup"; |
| Delete Obsolete |
RMAN> LIST ARCHIVELOG LIKE
'%';
RMAN> DELETE FORCE OBSOLETE; |
| Wildcard Delete |
RMAN> LIST ARCHIVELOG LIKE
'%';
RMAN> DELETE ARCHIVELOG LIKE '%'; |
| |
|
|
| |
| Note: $ORACLE_HOME/rdbms/admin/prgrmanc.sql |
| |
-- as root
unmount /mnt
mkdir /u01
chown oracle:dba /u01
chmod 755 /u01
cd /etc
vi fstab
192.168.2.200:/vol/vol1/alpha1 /u01 nfs rw,bg,intr,hard,rsize=32768,wsize=32768,noac,nolock,tcp,vers=3
0 0 |
| Put the Database into Archive
Log Mode |
conn / as sysdba
CREATE pfile='/home/oracle/initorcl.ora'
FROM spfile='SPFILEORCL.ORA';
col name format a30
col value format a30
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%arch%';
-- alter the SPFILE as required.
shutdown immediate;
CREATE spfile FROM pfile='/home/oracle/initorcl.ora';
startup mount exclusive;
alter database archivelog;
alter database open;
alter system switch logfile;
archive log list |
| |
-- batch RMAN file from Andrea
Sparling:
|
-- RMAN cron file from Andrea
Sparling:
|
| |
Allocates a tape channel for a whole database
and archived redo log backup |
ALLOCATE [AUXILLIARY] CHANNEL = '<channel_id>'
DEVICE TYPE = deviceSpecifier [allocOperandList];
need device type list |
desc gv$backup_device
SELECT *
FROM gv$backup_device;
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
BACKUP DATABASE PLUS ARCHIVELOG;
} |
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 |
RUN {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/disk1/backups/%U';
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/disk2/backups/%U';
# AS COPY is default when backing up to disk
BACKUP DATABASE PLUS ARCHIVELOG;
} |
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 |
RUN {
ALLOCATE CHANNEL c1 DEVICE TYPE DISK MAXPIECESIZE 5M;
# AS COPY is the default, so RMAN creates image copies
BACKUP DATABASE PLUS ARCHIVELOG;
SET BACKUP COPIES = 2;
BACKUP DATAFILE 1 FORMAT '/disk1/backups/%U', '/disk2/backups/%U';
} |
| When creating a duplicate database, allocate a channel by using the AUXILIARY
option |
RUN {
ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE sbt;
ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE sbt;
DUPLICATE TARGET DATABASE TO ndbnewh
LOGFILE
'?/oradata/aux1/redo01.log' SIZE 200K,
'?/oradata/aux1/redo02.log' SIZE 200K
'?/oradata/aux1/redo03.log' SIZE 200K
SKIP READONLY
NOFILENAMECHECK;
} |
| Create (have catalog) need
GLOBAL and SCRIPT |
| |
| Data
Encryption |
| 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. |
| Encryption Modes |
dual mode
password mode
transparent mode |
| Initialization Parameter |
To use encryption the compatible parameter must be set to 10.2.0 or above |
| To use password encryption |
Use SET ENCRYPTION ON IDENTIFIED BY password ONLY |
| |
| Drop |
| Duplicate |
| Exit |
| Endinline |
| Host |
| Library |
| List |
| Mount |
| Mount Target Database |
MOUNT |
| See STARTUP Demo |
| Open |
| Print |
| Quit |
| Recover |
| Register |
Register a Target in the
Database
Recovery Catalog |
REGISTER DATABASE |
| RMAN> REGISTER DATABASE; |
| Release |
| Replace |
| Report |
| Report Catalog Information |
REPORT |
| RMAN> REPORT SCHEMA; |
| Renormalize |
| Reset |
| Restore |
| Execute Restore |
RESTORE |
RMAN> target / catalog rman/rman@rman
RMAN> backup database;
RMAN> validate backupset 1;
SQL> conn scott/tiger
SQL> DELETE FROM emp WHERE rownum < 6;
SQL> COMMIT;
RMAN> restore database; |
| Resync |
| Rpctest |
| Run |
| Send |
| Script |
Create Script
Creating a Local Stored Script: Example This example creates a stored script called backup_whole that backs up the database and archived redo logs:
# creates recovery catalog script to back up database and archived logs
CREATE SCRIPT backup_whole
COMMENT "backup whole database and logs" {
BACKUP INCREMENTAL LEVEL 0 TAG b_whole_l0
DATABASE PLUS ARCHIVELOG;
} |
Create Global Script
Creating a Global Stored Script: Example This example creates a stored script called backup_whole that backs up the database and archived redo logs:
# creates recovery catalog script to back up database and archived logs
CREATE GLOBAL SCRIPT global_backup_db
COMMENT "backup any database from the recovery catalog, with logs" {
BACKUP DATABASE PLUS ARCHIVELOG;
} |
Delete Script
deletes a stored script b_whole_10 from the recovery catalog:
rman TARGET / CATALOG rman/cat@catdb
RMAN> DELETE SCRIPT b_whole_10; |
| Set |
RMAN> run { set until time ="to_date('03/03/2006 16:58:01','mm/dd/yyyy hh24:mi:ss')";
restore database;
recover database;
} |
| Setlimit |
| Show |
| Shutdown |
| Spool |
| SQL |
| Startup |
| Startup a Database |
|
$ rman
$ rman TARGET / CATALOG rman/cat@catdb
RMAN> STARTUP MOUNT
$ rman TARGET SYS/oracle@trgt NOCATALOG
$ rman TARGET / CATALOG rman/cat@catdb AUXILIARY SYS/oracle@auxdb |
| Test |
| Unregister |
| Unregister a Database from the
Catalog |
|
| RMAN> UNREGISTER DATABASE; |
| Upgrade |
| Validate |
| |
-- from Andrea Sparling
using target database controlfile instead of recovery catalog
> RMAN configuration parameters are:
> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
> CONFIGURE BACKUP OPTIMIZATION OFF; # default
> CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
> CONFIGURE CONTROLFILE AUTOBACKUP OFF; # 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 2 G;
> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
> CONFIGURE SNAPSHOT CONTROLFILE NAME TO
> '/u01/app/oracle/product/10.1.0.3/dbs/snapcf_pnbdb.f'; # default
>
> RMAN> |
-- crontab from Andrea Sparling
-------------- Forwarded Message: --------------
From: Oracle db 10g software owner <oracle10g@parnassus.opb.washington.edu>
To: andrea.sparling@comcast.net
Subject: rcv and sh
Date: Fri, 3 Mar 2006 02:18:27 +0000
>> ##7 7 1 7 * "/home/oracle10g/sql/admin/monitor/alter_user_account_lock.sh" >
>> "/home/oracle10g/sql/admin/monitor/alter_user_account_lock.sh.out"
>> ##9 14 1 1 * "/home/oracle10g/sql/admin/monitor/alter_user_account_unlock.sh" >
>> "/home/oracle10g/sql/admin/monitor/alter_user_account_unlock.sh.out"
>> 0 * * * * "/home/oracle10g/sql/admin/monitor/add_license.sh" >
>> "/home/oracle10g/sql/admin/monitor/add_license.sh.out"
>> #0 * * * * ~/DBA/cron/check_db_mail.pl
>> #
>> # ---- daytime continuous jobs
>> # check alert log every 15 minutes, only mails if new errors found 7 am to 5
>> pm (will pickup overnight errors)
>> 0,15,30,45 7-17 * * 1-5 ~/DBA/cron/alert_log.sh 2>&1 > /dev/null
>> # checks disk space every 15 minutes, only mails if tolerance exceeded
>> 0,15,30,45 7-17 * * 1-5 ~/DBA/cron/spacemail.sh 2>&1 > /dev/null
>> # check tns name service every 30 minutes, only mails if tnsping fails
>> 0,30 7-17 * * 1-5 ~/DBA/cron/cktnsnms.ksh 2>&1 > /dev/null
>> #
>> # -- database statistics
>> # gather selected schema stats daily at 4 am
>> 0 4 * * 0-6 /home/oracle10g/DBA/cron/gather_all_schema_stats.sh 2>&1 >
>> /dev/null
>> #
>> # ------ database backups
>> # rman backups daily 3:00 AM well after the unitrends job
>> 0 3 * * 0-6 /u03/backup/scripts/rman_full.sh 2>&1 > /dev/null
>> # cleanup rman logs over 21 days, once a day 6 am
>> 0 6 * * 1-5 /home/oracle10g/DBA/cron/rman_cleanup_logs.sh 2>&1 > /dev/null
>> #
>> # --- unix reporting
>> # save unix sar statistics into table sar_stats sun-sat 11:50 to get whole
>> day
>> 50 11 * * 0-6 /home/oracle10g/DBA/cron/sar2db.sh 2>&1 > /dev/null
>> # a few unix summaries, once a day noon
>> 0 12 * * 1-5 /home/oracle10g/DBA/cron/dailyunix.sh 2>&1 > /dev/null
>> #
>> # EOO export dumps
>> # export EOO on sat 3 pm
>> 0 15 * * 6 /home/oracle10g/DBA/cron/eoo_export.sh 2>&1 > /dev/null
>> # export ACAD on sat 3:30 pm
>> 30 15 * * 6 /home/oracle10g/DBA/cron/acad_export.sh 2>&1 > /dev/null
>> # export STAFF on sat 4:00 pm
>> 0 16 * * 6 /home/oracle10g/DBA/cron/staff_export.sh 2>&1 > /dev/null |
RMAN commands from Andrea Sparling
>> CONNECT TARGET /
>>
>> BACKUP INCREMENTAL LEVEL 0
>> TAG full_backup
>> FORMAT '/u03/backup/rman/rman_fullbackup_%d_%t.%s.%c.%p.bus'
>> DATABASE;
>>
>> COPY CURRENT CONTROLFILE TO '/u03/backup/rman/controlfile.backup';
>>
>> CROSSCHECK BACKUP;
>>
>> CROSSCHECK ARCHIVELOG ALL;
>>
>> DELETE OBSOLETE;
>>
>> DELETE EXPIRED ARCHIVELOG ALL;
>>
>> LIST BACKUP;
>>
>> LIST ARCHIVELOG ALL; |
RCV and SH from Andrea Sparling
#!/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
. /etc/bashrc
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.1.0.3; export ORACLE_HOME
ORACLE_SID=pnbdb; export ORACLE_SID
LD_LIBRARY_PATH=/u01/app/oracle/product/10.1.0.3/lib:/lib:/usr/lib; export
LD_LIBRARY_PATH
# set relative head of directory (work disk is u03, oracle disk is u01)
HEAD=/u03
WORK=$HEAD/backup/scripts
cd $WORK
# unique logfile
DATE=`date +"%Y%m%d-%H%M%S"`
LOGFILE='/u03/backup/rman/logs/rman_full_backup_'${DATE}'.log'
echo "log: "${LOGFILE} > $LOGFILE
echo "Started " >> $LOGFILE
date >> $LOGFILE
echo instance $ORACLE_SID >> $LOGFILE
##########################################################################
${ORACLE_HOME}/bin/rman @${WORK}/rman_full.rcv >> $LOGFILE
returncode=$?
echo $returncode return code >> $LOGFILE
df >> $LOGFILE
echo "Ended " >> $LOGFILE
date >> $LOGFILE
if [ $returncode -ne 0 ]; then
echo " email error log " >> $LOGFILE
mail csds_opb@u.washington.edu -s"RMAN ERROR LOG for parnassus pnbdb backup"
< $LOGFILE
mail amspar@u.washington.edu -s"RMAN ERROR LOG for parnassus pnbdb backup" <
$LOGFILE
fi
exit |
| |
| RAC Demos |
| 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. |
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 Restore Datafiles
- copied from elsewhere for integration |
| The following is from $ORACLE_HOME/cfgtoollogs/dbca/orabase/rmanRestoreDatafiles.sql |
set echo off;
set serveroutput on;
SELECT TO_CHAR(systimestamp,'YYYYMMDD HH:MI:SS') FROM dual;
variable devicename varchar2(255);
DECLARE
omfname VARCHAR2(512) := NULL;
done BOOLEAN;
BEGIN
dbms_output.put_line(' ');
dbms_output.put_line(' Allocating device.... ');
dbms_output.put_line(' Specifying datafiles... ');
:devicename := dbms_backup_restore.deviceAllocate;
dbms_output.put_line(' Specifing datafiles... ');
dbms_backup_restore.restoreSetDataFile;
dbms_backup_restore.restoreDataFileTo(1, 'C:\oracle\product\oradata\orabase\SYSTEM01.DBF', 0, 'SYSTEM');
dbms_backup_restore.restoreDataFileTo(2, 'C:\oracle\product\oradata\orabase\UNDOTBS01.DBF', 0, 'UNDOTBS1');
dbms_backup_restore.restoreDataFileTo(3, 'C:\oracle\product\oradata\orabase\SYSAUX01.DBF', 0, 'SYSAUX');
dbms_backup_restore.restoreDataFileTo(4, 'C:\oracle\product\oradata\orabase\USERS01.DBF', 0, 'USERS');
dbms_output.put_line(' Restoring ... ');
dbms_backup_restore.restoreBackupPiece(
'C:\oracle\product\ora102\assistants\dbca\templates\Seed_Database.dfb', done);
IF done THEN
dbms_output.put_line(' Restore done.');
ELSE
dbms_output.put_line(' ORA-XXXX: Restore failed ');
END IF;
dbms_backup_restore.deviceDeallocate;
END;
/
SELECT TO_CHAR(systimestamp,'YYYYMMDD HH:MI:SS') FROM dual; |
| |
| RMAN
Related Queries |
? |
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/OracleHomes/oradata/orcl
ls -la
cd /app/oracle/product/OracleHomes/admin/orcl/bdump
tail alert_orcl.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_orcl.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/OracleHomes/oradata/orcl/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;}
tail the alert log
look at /app/oracle/OracleHomes/oradata/orcl
ls -la -- file is restored
conn / as sysdba
shutdown immediate;
startup
-- note error message
SQL> startup
ORACLE instance started.
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_orcl.log
-- next dumped controlfiles 1 and 3
shutdown immediate fails
shutdown abort
connect to RMAN |
| |
| Backup and
Recovery Practices Best Practices |
| 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 checksumming. 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 relevent 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.
RMAN> crosscheck backup;
RMAN> delete expired backup;
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 and then 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. |