Posts Tagged ‘rman’

Oracle – RMAN backup example

RMAN can be used to backup a database. You can perform a hot backup if the database is in archivelog mode, or a cold backup if it is not. You can either perform a full backup or an incremental backup. RMAN is particularly useful because it only backs up used space.

An RMAN backup will backup the datafiles, controlfile, server parameter file, and archivelogs (where applicable).

To backup a database using RMAN with a level 0 backup you can perform the following:

$ ORACLE_SID = <oracle_sid>
$ORACLE_HOME = <oracle_home>
$ rman
RMAN> BACKUP DATABASE; # Cold backup without archivelogs
RMAN> BACKUP DATABASE PLUS ARCHIVELOG; # Cold backup with archivelogs

Incremental backups save time, bandwidth and reduce backup sizes. Incremental backups abck up all the blocks changed since the last level 0 incremental backup. If no level 0 backup has been run when you try to run a level 1 backup, then RMAN automatically makes a level 0 incremental backup.

To backup a database using RMAN with a level 1 incremental backup, and skipping inaccessible files, you can perform the following:


Further Reading


Oracle – RMAN 11g List Failure

The 11g version of RMAN has a useful feature for diagnosing and repairing any failures.

Find any database failures:

RMAN> list failure;

Determine manual or automatic repair:

RMAN> advise failure;

From the output of the advise, try to manually fix the failure. If this fails to work, then try the repair command:

RMAN> repair failure;

Further Reading

Oracle® Database Backup and Recovery User’s Guide

Oracle – Unregister database from RMAN

When removing a database and using RMAN it is necessary to remove the database from the RMAN repository to prevent RMAN from failing when it goes to run backups.

You do that by doing the following:

  •  Set your oracle environment for the database that needs to be removed
  • Check ORACLE_SID is correct database (to prevent removing the wrong one)
  • Log onto RMAN

RMAN> unregister database
RMAN> shutdown immediate

Oracle – Archive Log Mode

November 22, 2010 Leave a comment

Archive log mode is necessary for hot backups and point-in-time recovery to take place. By default the database is created in noarchivelog mode, however you would need to shutdown the database in order to perform a backup. Archivelog mode allows for hot backups to be run with the archive logs being included in the backup to allow for consistency.

In archivelog mode the database will make copies of completed redo logs, archive logfiles. The disadvantage of this is that until the archivelog for a redo log has been written, the redo log cannot be reused.


The following parameters need to be set in order to use archivelog mode:

log_archive_start = TRUE
log_archive_dest_1 = 'LOCATION=/arch_dir_name'
log_archive_dest_state_1 = ENABLE
log_archive_format = %d_%t_%s.arc

Enable Archivelog Mode

You must first shutdown all instances of the database. Then startup one instance, enable archivelog mode, the restart the remaining instances.

SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;

Check Archivelog Mode

You can easily check if a database is running in archivelog mode by running the following:

SQL> Archive log list;

Disable Archivelog Mode

To disable archivelog mode you once again need to shutdown the database, startup mount, then disable archivelog mode:

SQL> shutdown immediate
SQL> startup mount
SQL> alter database noarchivelog;
SQL> alter database open;

Oracle – Move datafile from single-node into ASM using RMAN and recover (online mode)

November 2, 2010 Leave a comment

If you need to move a datafile from single-node to ASM then it is necessary to use RMAN to copy the datafile before renaming the datafile using SQLPLUS.

  1. Log into RMAN and copy the datafile to the required diskgroup:

    merlin:oracle@OASLIVE1 > rman
    Recovery Manager: Release - Production on Mon Nov 1 11:42:00 2010
    Copyright (c) 1982, 2007, Oracle. All rights reserved.
    RMAN> connect target /
    connected to target database: OASLIVE (DBID=3426612930)

    RMAN> copy datafile 19 to '+LIVEDATA';
    Starting backup at 01-NOV-10
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=122 instance=OASLIVE1 devtype=DISK
    channel ORA_DISK_1: starting datafile copy
    input datafile fno=00019 name=/u01/oracle/ora102/dbs/F:ORACLEORADATAOASLIVEOASLIVE_REPOS_01.DBF
    output filename=+LIVEDATA/oaslive/datafile/repository.288.733923757 tag=TAG20101101T114237 recid=1 stamp=733923758
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
    Finished backup at 01-NOV-10

  2. Take the datafile offline to prevent data from being written to it whilst the renaming is commencing

    merlin:oracle@OASLIVE1 > sqlplus '/as sysdba'
    SQL*Plus: Release - Production on Mon Nov 1 11:46:07 2010
    Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to:
    Oracle Database 10g Enterprise Edition Release - 64bit Production
    With the Partitioning, Real Application Clusters, OLAP, Data Mining
    and Real Application Testing options

    SQL> alter database datafile 19 offline;
    Database altered.

  3. Rename the datafile

    SQL> alter database rename file '/u01/oracle/ora102/dbs/F:ORACLEORADATAOASLIVEOASLIVE_REPOS_01.DBF' to '+LIVEDATA/oaslive/datafile/repository.288.733923757';
    Database altered.

  4. If we try to put the datafile back online we will get an error message saying that the datafile requires recovery. This is because the copy of the datafile was completed whilst the datafile was still online. The following error message will occur:

    SQL> alter database datafile 19 online;
    alter database datafile 19 online
    ERROR at line 1:
    ORA-01113: file 19 needs media recovery
    ORA-01110: data file 19: '+LIVEDATA/oaslive/datafile/repository.288.733923757'

  5. Recover the datafile to restore

    SQL> recover datafile 19;
    Media recovery complete.

  6. Bring the datafile back online:

    SQL> alter database datafile 19 online;
    Database altered.

Oracle – Migrate database from Oracle 9i to Oracle 10g

October 18, 2010 1 comment

If you are migrating from Oracle 9i to Oracle 10g you must use the Oracle export/import tools.  The process for migrating from Oracle 9i to Oracle 10g is to export the data from the Oracle 9i system, the transfer the files across to the 10g system. On the 10g system you must then create a 10g database. Once the 10g database has been created you must then import the data from the 9i system. After the import has completed statistics can then be gathered.

9i Preparation

  1. Check how much disk space is available, either by checking the file system, or checking how much space can be created when backups have been cleared down
  2. Check how big the datafiles are to see how big the export is likely to be, remember the export will be compressed but it is better to have more space than necessary than not enough
  3. So as not to run out of undo tablespace create the following script and store it on the 9i system:

    create table tmp_users as select * from dba_users;
    delete tmp_users;

    Do not close the session or commit the transaction whilst the export is completing. If the database export is large then there is a possibility you will run out of undo tablespace during export. If this happens you will receive the following error:

    ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'

  4. For ease of use we can create an export parameter file. This file can be reused for further exports. Create the export parameter file on the 9i system:


  5. Create a shell script for running the export, this should include a nohupto allow the script to run in the background. If the script runs in the foreground then the export will fail if the server window times out.

    nohup exp parfile=export.par &

  6. Query tablespaces and sizes. Take a note of the sizes of the tablespaces and datafiles in order to recreate them for use on the 10g system.

10g Preparation

  1. On the 10g system create an import shell script. This will look similar to that of the export on the 9i system:

    nohup imp parfile=import.par &

  2. Create the export parameter file on the 10g system:


  3. Create a transactional database using DBCA in a VNC session
  4. Create a sql script for the creation of tablespaces and datafiles

Export Data from 9i

  1. Check backup to tape has been successful
  2. Cleardown any old backups if space is required before export
  3. Run the sql script for undo tablespace retention
  4. In a separate session start the export using the previously created export shell script
  5. Tail nohup.out to see when the export has completed

    $  tail -f nohup.out

FTP the files to the 10g server

  1. FTP the files across to the 10g server either using WinSCP (linux to linux) or using mget (windows to linux/windows)

Import to 10g

  1. In a VNC session run the import shell script created previously