Setting up RMAN in your environment. 1. Put the database in archive log mode. (technically, this isn't really necessary, but otherwise rman will perform cold backups only, shutting down the database each time). Q: How do you setup archive logging on a server? A: - create a directory to hold your archive logs. - shutdown the server. (9i: create pfile from spfile) - Add these lines to your init.ora parameter file/pfile: *.log_archive_dest="/your/directory/for/archivelogs" *.log_archive_start='TRUE' *.log_archive_format="log%s_%t.arc" (9i: create spfile from pfile when done) - startup mount exclusive - alter database archivelog; - shutdown and startup again to test whether its working: - alter system switch logfile; this forces a logfile switch and should write a log file to the directory specified. #----------------------------------------------------------------------------------------- 2. Create the RMAN Recovery Catalog (see chapter 16 of the Oracle9i Recovery Manager User's Guide for documentation). (see chapter 10 of the Oracle10g Backup and Recovery Advanced Users's guide) This step is not required, but is recommended and can be helpful when managing more than one database. Without a catalog database, Oracle depends solely on the control files for recovery information. The documents suggest NOT using the same DB that you want to backup as the catalog host ... for obvious reasons. However, the catalog has to reside *somewhere*. Find the most appropriate spot for it. I've created a small separate database instance, called it "RMANSVR" and created the rman user and catalog. - Create the rman user, by logging into the host db instance as sys CREATE USER rman IDENTIFIED BY rman TEMPORARY TABLESPACE temp DEFAULT TABLESPACE tools QUOTA UNLIMITED ON tools; GRANT RECOVERY_CATALOG_OWNER TO rman; GRANT CONNECT, RESOURCE TO rman; - Create the rman catalog through the rman command: $ rman catalog rman/rman@rmansvr or, from the rman prompt: RMAN> CONNECT CATALOG rman/rman@rmansvr (this will tell you "recovery catalog is not installed") RMAN> create catalog (this will simply return "catalog created" and you're done. Log in as rman, do "select table_name from user_tables" to confirm. You should see 30 tables created under the rman schema. #----------------------------------------------------------------------------------------- 3. Register the database you're going to be backing up into the catalog schema you've just created. Set your $ORACLE_SID to be the "target" database that you're registering and run this: $ rman target sys/sys@targetserver catalog rman/rman@stg30dev or $ rman target sys@stg30dev catalog rman@stg30dev if you don't want to show passwords ... you'll be prompted for both pwds. Alternatively you can do this: $ rman catalog rman/rman@stg30dev RMAN> connect target then, RMAN> register database; This will register whatever the ORACLE_SID is set to. If you're running this as oracle and are local to the machine with the database, no additional login is necessary, b/c oracle's already allowed to login as sysdba based on its membership in the dba group at the OS level. You can also do this: # rman catalog rman/rman@stg30dev RMAN> connect target sys/sys@targetserver RMAN> register database; Once done, you can test your work by : RMAN> report schema; #----------------------------------------------------------------------------------------- 4. Configure the database's backup parameters within RMAN. I create a file called "rman_server_config.rcv" with the following (change directories as appropriate) (see $ORACLE_HOME/rdbms/demo/*.rcv for demo files that fully explain what these mean). ----8<---- begin rman config file ----8<---- # Rman config parameters: select * from CONF to see these # backups to disk, as opposed to tape (if tape, other options needed) CONFIGURE DEFAULT DEVICE TYPE TO DISK; # keeps at least 5 copies of each file CONFIGURE RETENTION POLICY TO REDUNDANCY 5; # uses two channels (server processes) to write data CONFIGURE DEVICE TYPE DISK PARALLELISM 2; # specify disk directory CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/array/backup/rman/ora_df%t_s%s_s%p'; # backs up the control file too (always a good idea) CONFIGURE CONTROLFILE AUTOBACKUP ON; # tells RMAN where to backup the control files CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/array/backup/rman/ora_cf%F'; # allows for point in time recovery going back 30 days CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS; # tells RMAN to be smart while backing up, and not re-backup files already backedup CONFIGURE BACKUP OPTIMIZATION ON; # select * from TS to see which tablespaces ARE included # tablespaces to exclude: no need to specify temp # Note: ONLY exlucde tablespaces that you KNOW you do not use or which you KNOW you're better # off creating by hand ... never exclude any system tablespaces CONFIGURE EXCLUDE FOR TABLESPACE indx; ----8<---- end rman config file ----8<---- You can put all these in one file, and then run the file from RMAN like this: RMAN> @rman_stg30dev_config.rcv or $ rman target sys/sys@stg30dev catalog rman/rman@stg30dev cmdfile rman_stg30dev_config.rcv Note: these are configured ONCE for each database, and are stored in the repository going forward. You can change them and RMAN will report old and new values like this: RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK; old RMAN configuration parameters: CONFIGURE DEFAULT DEVICE TYPE TO DISK; new RMAN configuration parameters: CONFIGURE DEFAULT DEVICE TYPE TO DISK; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete You can always look at the rman catalog table "CONF" to see these values. You can always look at the rman catalog table "TS" to see what tablespaces ARE being backed up. If you exclude a tablespace that you now want to INCLUDE, you can run this command: RMAN> configure exclude for tablespace cwmlite clear; If you want to see what is being excluded: RMAN> show exclude (or show all, to get all parameters) You can clear any existing configure parameter and restore it to default by calling the same configure command and put "clear" at the end. #----------------------------------------------------------------------------------------- 5. Run your first backup. Like any other backup routine, you'll need a cold backup (obtained while the database is shut down) to start your backup routine. I've got these steps in a file called rman_full_dbshutdown_backup.rcv. STARTUP FORCE DBA; SHUTDOWN IMMEDIATE; STARTUP MOUNT; BACKUP INCREMENTAL LEVEL 0 DATABASE FILESPERSET 4; ALTER DATABASE OPEN; #----------------------------------------------------------------------------------------- 6. Run recurring backups I've got these steps in a file called rman_incr_weeklyroutine_backup.rcv DELETE BACKUP COMPLETED BEFORE 'SYSDATE-7' DEVICE TYPE DISK; BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DEVICE TYPE DISK DATABASE FILESPERSET 4; BACKUP BACKUPSET ALL; # copies backups from disk to tape BACKUP ARCHIVELOG ALL; DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-7'; And I've got a script called rman_nightly.sh, running at 2am in cron that looks like this; for server in STG30DEV do echo $name rman target sys/sys@${server} catalog rman/rman@rmansvr << EORMAN @rman_incr_weeklyroutine_backup.rcv EORMAN done #----------------------------------------------------------------------------------------- 7. Adhoc Backups: - if you have read only tablespaces, you can back them up at any time RMAN> BACKUP TABLESPACE read_only_tablespace_name; #----------------------------------------------------------------------------------------- 8. Maintenance commands (these all assume you're logged in, and connected to the target server) RMAN> restore database validate; this checks your database to see if its restorable. Any errors will be shown. Immediately follow with report schema; to figure out which datafile #'s refer to which tablespaces. RMAN> RESTORE TABLESPACE read_only_tablespace_name VALIDATE; same for your read only tablespace. RMAN> RESTORE CONTROLFILE VALIDATE; same for your control files # check if archivelogs for the past two weeks can be restored RMAN> RESTORE ARCHIVELOG FROM TIME 'SYSDATE-14' VALIDATE; RMAN> report schema; this shows all the tablespaces in the target server, whether they're being backed up or not (excludes temporary tablespaces by default, since you'll never back them up). RMAN> report need backup; tells you what needs to be backedup. For example, if you've added a new tablespace. RMAN> crosscheck backup; Verifies that all backups on the backup media are intact. RMAN> delete obsolete; this will purge any and all backups that it knows are no longer needed. It reads the retention parameter (in our example, 30 days) and deletes backups older. It then deletes unneeded archive log files (which are, any archive log files that are older than the datafile backup they support). RMAN> list backup RMAN> list backup summary; these list the backups existant in the RMAN catalog. #----------------------------------------------------------------------------------------- 9. Create recovery scripts, specific to each database. Cutting and pasting from the case1.rcv demo file, i'd suggest pre-creating these scripts so they're ready to go. This example is from a local database. ----8<---- begin rman restore file ----8<---- SET DBID 3201665279; CONNECT TARGET STG30DEV; STARTUP NOMOUNT; RUN { # uncomment the SET UNTIL command to restore database to the incremental # backup taken three days ago. # SET UNTIL TIME 'SYSDATE-3'; # set this to be the same as your current backup location and format name CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/array/backup/rman/ora_cf%F'; # restore the control file, then the database. RESTORE CONTROLFILE FROM AUTOBACKUP; ALTER DATABASE MOUNT; RESTORE DATABASE CHECK READONLY; RECOVER DATABASE NOREDO; ALTER DATABASE OPEN RESETLOGS; } ----8<---- end rman restore file ----8<---- #----------------------------------------------------------------------------------------- 2/16: Simple Recovery scenarios: 1. recover the entire database; database starts but cannot read a datafile (as an example) RMAN> connect target RMAN> restore database; RMAN> recover database; RMAN> alter database open; 2. restore a tablespace sql> alter tablespace X offline immediate; RMAN> restore tablespace X; RMAN> recover tablespace X; sql> alter tablespace X online; (note: youc an also run sql from rman like this: RMAN> sql 'select * from dual'; 3. Restore a block that's been corrupted. If you get Ora-01578 errors in the alert log like this " ORA-01578: ORACLE data block corrupted (file # 7, block # 3) ORA-01110: data file 7: '/oracle/oradata/trgt/tools01.dbf' you can recover like this: RMAN> BLOCKRECOVER DATAFILE 7 BLOCK 3 DATAFILE 2 BLOCK 235; #----------------------------------------------------------------------------------------- Helpful Hints: 1. alias 'rmanlogin' to 'rman catalog rman/rman@stg30dev' to save typing. 2. Have documented the DBID that goes with each server. The DBID is used during recovery by RMAN and is an internal unique identifier for a database. It can also be handy, since RMAN only knows of the DBID during some of its recovery operations, and its the ONLY way to refer to a backup procedure if you're NOT using a catalog database. You can get the DBID when connecting to a target within RMAN. dbname dbid db_key in rman DW30DEV (DBID=2078697134) 18 STG30DEV (DBID=3201665279) 1 DW20TST (DBID=3312156904) 35 STG20TST (DBID=1618016879) 367 DW30TST (DBID=2692760053) 596 STG30TST (DBID=2237182618) 600 Then, match up these DBIDs to their keys by logging in as rman and sql> select * from db. This will allow you to know what db_keys are what servers within RMAN's catalog. (this data is also located in DBINC table in rman catalog). 3. Save configuration parameters into a file for later use. You can also get the configuration for your database by logging in as rman and sql> select * from conf where db_key=; (If for no other reason than to be able to quickly cut-n-paste them and configure a new server) 2/16: 4. You can call scripts from RMAN command line all at once like this: $ rman target sys/sys@db1 catalog rman/rman@rmansvr CMDFILE abc.rcv LOG out.log 2/16: 5. You can store scripts in RMAN like this: RMAN> replace script #----------------------------------------------------------------------------------------- Scripting in RMAN rman> create script full_backup (or) rman> replace script ts_system_backup { allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; sql 'alter system switch logfile'; resync catalog; backup database plus archivelog delete input tag 'Full Backup'; (or) backup tablespace system format='/fs1/backup/al_%d%t%p'; } This creates an "rman script" called "backup_full" that can be called from a shell script like this: #!/bin/sh rman catalog rman/password@rman target sys/password@systest << EOF run {execute script full_backup;} EOF