Site Logo

IzzySoft


› › RMAN
{itemlist}
 

RMAN stuff

Restore a tablespace

Accidentally dropped a tablespace? Already the first grey hair starts spreading, thinking about all the work with creating some temporary database to restore your backup to in order to regain your tablespace … Stop! You're running Oracle 11g or higer? You do your backups with RMAN? So what?

RMAN> RECOVER TABLESPACE dropped_ts
      UNTIL TIME ( to_date('2012-04-20 22:31:54','YYYY-MM-DD HH24:MI:SS') )
      AUXILIARY DESTINATION '/opt/freespace';

dropped_ts is the name of the tablespace to restore. The timestamp was taken from the alert_log (minus one second, to be before the drop), and the AUXILIARY DESTINATION is where RMAN shall automatically do all the "temporary database" stuff.

Recovery Advisor

Another new feature coming with Oracle 11g: Let RMAN look for failures, suggest strategies, automatically generate required scripts, and do the job! Say "something's wrong" with your database. First time this happens to you, so you are a bit nervous. Or, even worse: you are not even there, but only some "assistant" who called you on the phone … OK. So now we get it step by step: First thing of course is to start RMAN and connect to the database in question:

Create a catalog DB

First connect to the database which shall hold the catalog and create the RMAN user:

create user rman identified by topfSecret default tablespace rman quota unlimited on rman;
grant recovery_catalog_owner to rman;

Now use rman to create the catalog:

$ rman
RMAN> connect catalog rman/topfSecret
RMAN> create catalog;

Create Standby (using catalog DB)

On the primary:

Now transfer the password file to the standby machine, then start the new standby instance with a simple parameter file containing nothing but the db_name – which must be the same as the on primary (e.g. *.db_name='MYDB')

$ export ORACLE_SID=MYDB
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> startup nomount pfile=/path/to/mydb.ora

Prepare a parameter file for RMAN, e.g. duplicate_mydb.rman. <password> must be your sys password, the connection names must be in your tnsnames.ora, adjust the service_names as you see fit:

connect target sys/<password>@MYDB_PRIM
connect auxiliary sys/<password>@MYDB_STDBY
duplicate target database for standby
from active database
dorecover
spfile
set db_unique_name='MYDB_STDBY'
set service_names='MYDB,MYDB_STDBY'
set global_names='TRUE'
nofilenamecheck;

Ready to let RMAN do the job:

$ rman cmdfile=duplicate_mydb.rmn

Lean back and watch! Then, do not forget to setup Data Guard ;)

Regular backups via Cron

A simple but effective backup strategy based on the following assumptions:

Initially configure RMAN accordingly:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE CONTROLFILE AUTOBACKUP ON;

Once a week, you will create a Level-0 backup (full backup to be used with subsequent incremental Level-1 backups). Your RMAN script for that (e.g. called level0.rmn will look like this:

run {
  allocate channel disk1 type disk format '/mnt/db_backups/%d/%d_full%t.bkp';
  backup incremental level 0 database tag "backup_level0";
  release channel disk1;
  allocate channel disk1 type disk format '/mnt/db_backups/%d/%d_archivelog_%e.bkp';
  backup archivelog all not backed up 2 times tag "backup_archivelog";
}

Every other day, you will create a Level-1 backup using level1.rmn:

run {
  allocate channel disk1 type disk format '/mnt/db_backups/%d/%d_incremental_%t.bkp';
  configure controlfile autobackup on;
  backup incremental level 1 database tag "backup_level1";
  release channel disk1;
  allocate channel disk1 type disk format '/mnt/db_backups/%d/%d_archivelog_%e.bkp';
  backup archivelog all not backed up 2 times tag "backup_archivelog";
}

Hourly (except when your Level-0/Level-1 backups are running), you will backup your archive logs with archivelog.rmn:

run {
  allocate channel disk1 type disk format '/mnt/db_backups/%d/%d_archivelog_%e.bkp';
  configure controlfile autobackup on;
  backup archivelog all not backed up 2 times tag "backup_archivelog";
}

And nightly (probably after your Level-0/Level-1 backups have been run), you will do some housekeeping.rmn:

crosscheck backup;
crosscheck archivelog all;
delete noprompt obsolete;
delete noprompt archivelog all backed up 2 times to disk;

Now, to run them via Cron, you will need a shell script running them. Make sure it sets your Oracle environment properly ($ORACLE_HOME, $ORACLE_SID, $PATH etc), then have it call rman target / cmdfile=$rmnfile (where $rmnfile is the corresponding file from above), followed by a echo -e "resync catalog;\n" | rman target / catalog /@CAT (where CAT is the TNS-Alias of your catalog database, and has a corresponding Oracle Wallet entry for password-less login).

Why two steps – and not connecting „target“ and „catalog“ right to start with? This ensures that backups are created even if the catalog database should be temporarily unreachable. In such a case, the sync would simply take place at the next run, nothing lost – the control file will take care for that. As long as you get the catalog database up and running before the control file runs out of space ;)

Register/Unregister a database from catalog

Check if it's already there:

$ sqlplus /@CAT
SQL> select * from rc_database where name='MYDB';

It is there but should be gone? Let's get rid of it:

$ rman
RMAN> connect catalog /@repo
RMAN> list incarnation of database MYDB;

# just one DB ID for this? Easy:

RMAN> unregister database MYDB;

# ouch, multiple already? Time for a cleanup:

RMAN> run
{  
  set DBID 1567892184;
  unregister database mydb;
}

Now let's register our shiny new database:

$ rman target=/ catalog=/@CAT
RMAN> register database;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE CONTROLFILE AUTOBACKUP ON;

Upgrade Catalog

If you've updated your database (that includes regular quarterly patches), connecting to the catalog might present you with a warning your database is newer than the catalog. Then you know it cannot hurt to upgrade the catalog as well. Make sure to do this while you're not running a backup (or the backup will fail):

$ rman target=/ catalog=/@CAT
RMAN> upgrade catalog;
RMAN> upgrade catalog;

In case you wonder: yes, twice. You will see why when you're doing it: RMAN will ask you to „say again, please“ for confirmation.

For more details and some background, see e.g. RMAN catalog upgrade, why, when and how.

Snippets to deal with Archive Logs

2020-11-18