RMAN Script: OMR Database Full Backup (Database Mounted)

 [ dbbkup_full.scp]

run {

allocate channel c1 type disk;

backup

tag weekly_omr_full

format '/u07/omr/backup/full_%d_%s_%p_%t'

(database);

release channel c1;

configure controlfile autobackup format for device type disk to '/u07/omr/backup

/auto_cntrl_%F';

configure controlfile autobackup on;

allocate channel c2 type disk;

backup

format '/u07/omr/backup/archive_%d_%s_%p_%t'

(archivelog all);

release channel c2;

}

startup;

RMAN Script: Cumulative level 2 backup

[ call_dbbkup_cm2.scp]

#!/bin/ksh

export ORACLE_HOME="/u01/app/oracle/product/10.2.0"

export ORACLE_SID="omr"

PATH=$PATH:$ORACLE_HOME/bin

echo rman backup cm level1 for CATDB started `date` >> /u07/catdb/rmanbkup.log

rman target sys/password@omr catalog rman/rman@catdb cmdfile='/u04/catdb/scripts/dbbkup_cm1.scp'

echo rman backup cm level0 for CATDB ended `date` >> /u07/catdb/rmanbkup.log

exit

[dbbkup_cm2.scp]

run

{

allocate channel c1 type disk;

backup incremental level 2 cumulative

        tag omr_cm2

        format '/u10/catdb/backup/cm2_%d_%s_%p_%t'

        (database);

release channel c1;

#backing up controlfile to the specified destination keeping autobakup copy

configure controlfile autobackup format for device type disk to '/u10/catdb/back

up/auto_cntrl_%F';

configure controlfile autobackup on;

#backup up archivelog files

allocate channel c2 type disk;

backup

format '/u10/catdb/backup/cm2_%d_%s_%p_%t'

(archivelog all);

release channel c2;

}

RMAN Script: Cumulative level 1 backup

[ call_dbbkup_cm1.scp]

#!/bin/ksh

export ORACLE_HOME="/u01/app/oracle/product/10.2.0"

export ORACLE_SID="omr"

PATH=$PATH:$ORACLE_HOME/bin

echo rman backup cm level1 for CATDB started `date` >> /u07/catdb/rmanbkup.log

rman target sys/password@omr catalog rman/rman@catdb cmdfile='/u04/catdb/scripts/dbbkup_cm1.scp'

echo rman backup cm level0 for CATDB ended `date` >> /u07/catdb/rmanbkup.log

exit

[dbbkup_cm1.scp]

run

{

allocate channel c1 type disk;

backup incremental level 1 cumulative

        tag omr_cm1

        format '/u10/catdb/backup/cm1_%d_%s_%p_%t'

        (database);

release channel c1;

#backing up controlfile to the specified destination keeping autobakup copy

configure controlfile autobackup format for device type disk to '/u10/catdb/back

up/auto_cntrl_%F';

configure controlfile autobackup on;

#backup up archivelog files

allocate channel c2 type disk;

backup

format '/u10/catdb/backup/cm1_%d_%s_%p_%t'

(archivelog all);

release channel c2;

}

RMAN Script: Cumulative level 0 backup

[ call_dbbkup_cm0.scp]

#!/bin/ksh

export ORACLE_HOME="/u01/app/oracle/product/10.2.0"

export ORACLE_SID="omr"

PATH=$PATH:$ORACLE_HOME/bin

echo rman backup cm level0 for CATDB started `date` >> /u07/catdb/rmanbkup.log

rman target sys/password@omr catalog rman/rman@catdb cmdfile='/u04/catdb/scripts/dbbkup_cm0.scp'

echo rman backup cm level0 for CATDB ended `date` >> /u07/catdb/rmanbkup.log

exit

[dbbkup_cm0.scp]

run

{

allocate channel c1 type disk;

backup incremental level 0 cumulative

        tag omr_cm0

        format '/u10/catdb/backup/cm0_%d_%s_%p_%t'

        (database);

release channel c1;

#backing up controlfile to the specified destination keeping autobakup copy

configure controlfile autobackup format for device type disk to '/u10/catdb/back

up/auto_cntrl_%F';

configure controlfile autobackup on;

#backup up archivelog files

allocate channel c2 type disk;

backup

format '/u10/catdb/backup/cm0_%d_%s_%p_%t'

(archivelog all);

release channel c2;

}

RMAN Script: Deleting archivelog when catalog exists.

[call_omr_archflush.scp]

#!/bin/ksh

export ORACLE_HOME="/u01/app/oracle/product/10.2.0"

export ORACLE_SID="omr"

PATH=$PATH:$ORACLE_HOME/bin

rman target sys/password@omr catalog rman/rman1956@rman cmdfile='/u04/rman/rman/scripts/omr_archflush.scp'

exit

  [omr_archflush.scp]

# RMAN SCRIPT: DELETING ARCHIVE LOGS
run
{
allocate channel c1 type disk;
delete archivelog until time 'SYSDATE-8';
        # OR delete archivelog until sequence=;
        #or  RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;
release channel c1;
}

RMAN Script: Deleting the old archives when no catalog exists

[ call_catdb_archflush.scp]

#!/bin/ksh

export ORACLE_HOME="/u01/app/oracle/product/10.2.0"

export ORACLE_SID="catdb"

PATH=$PATH:$ORACLE_HOME/bin

rman target sys/password@catdb cmdfile='/u04/catdb/scripts/catdb_archflush.scp'

exit                 

[ catdb_archflush.scp]

run

{

allocate channel c1 type disk;

delete archivelog until time 'SYSDATE-8';

        # OR delete archivelog until sequence=;

release channel c1;

RMAN Script: Backing up all the archivelog files

[call_arch_bkup.scp] (this scripts is calling the script arch_bkup.scp)

#!/bin/ksh

export ORACLE_HOME="/u01/app/oracle/product/10.2.0"

export ORACLE_SID="omr"

PATH=$PATH:$ORACLE_HOME/bin

echo rman ARCHIVE backup for  CATDB started `date` >> /u07/catdb/rmanbkup.log

rman target sys/password@omr catalog rman/rman@catdb cmdfile='/u04/catdb/scripts/arch_bkup.scp'

echo rman ARCHIVE backup for CATDB ended `date` >> /u07/catdb/rmanbkup.log

exit

 [arch_bkup.scp]

run

{

allocate channel c1 type disk;

backup

format '/u10/catdb/backup/arch_%d_%s_%p_%t'

(archivelog all);

release channel c1;

# deleting archive logs older than 8 days

allocate channel c2 type disk;

delete archivelog until time 'SYSDATE-5';

release channel c2;

}

Oracle Solaris 10 Installation on HP DL380G7 server

Switching Undo Tablespace

CREATE UNDO TABLESPACE "UNDOTBS"
DATAFILE '/u05/omr/oradata/undotbs.dbf' SIZE 4000M;

SQL> select name from v$tablespace where name like 'UNDO%';
NAME
--------
UNDOTBS1
UNDOTBS

SQL> ALTER SYSTEM SET undo_tablespace='UNDOTBS' SCOPE=BOTH;
SQL> show parameter undo

NAME TYPE VALUE
undo_management string AUTO
undo_retention integer 21600
undo_tablespace string UNDOTBS

SQL>select status from V$ROLLSTAT
STATUS
ONLINE
ONLINE
PENDING OFFLINE
PENDING OFFLINE
PENDING OFFLINE
PENDING OFFLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE

If the status is pending offline; u cannot drop undo tablespace UNDOTBS1

SQL>drop tablespace undotbs1 including contents and datafiles

If u drop u will get ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

Note: You can find the following messages in alert.log after issuing alter system set command
Sat Jul 18 15:38:38 2009
Successfully onlined Undo Tablespace 7.
Undo Tablespace 1 moved to Pending Switch-Out state.
*** active transactions found in undo tablespace 1 during switch-out.
Sat Jul 18 15:46:28 2009
Undo Tablespace 1 successfully switched out.

Creating Catalog Database on Oracle10g

Step 1: Create o/s User:

Useradd –u 204 –g 2001 –G 1001 –c “Catdb Catalog User” –m –d /u04/catdb –s /bin/ksh catdb
Passwd : Enter password for catdb user

Group 2001: dba
Group 1001 : oinstall


Step 2: Creating Database
Use DBCA to create Database

Step 3: Create additional tablespaces:
create tablespace catdbtbs datafile '/u03/catdb/oradata/catdbtbs01.dbf' size 1000M reuse extent management local;

Step 4:
Take Backup of the database (if required)

Step 5:
Configure Listener.ora / tnsnames.ora

Step 6: Create RMAN user
Sql> Create user RMAN identified by RMAN
Default tablespace cattbs
Temporary tablespace temp;
Sql> Grant connect, resource, recovery_catalog_owner to RMAN;

Step 7: create catalog
$ rman
Recovery Manager: Release 10.2.0.4.0 - Production on Tue Sep 8 11:16:57 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> connect catalog rman/rman@catdb
connected to recovery catalog database


RMAN> create catalog tablespace cattbs;
recovery catalog created

RMAN> exit
Recovery Manager complete.


Step 8: Registering database
$ rman target sys/omr1956@omr catalog rman/rman@catdb
Recovery Manager: Release 10.2.0.4.0 - Production on Tue Sep 8 11:19:26 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: OMR (DBID=1312345947)
connected to recovery catalog database

RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u10/catdb/backup/auto_cntrl_%F';
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 ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/dbs/snapcf_omr.f'; # default
RMAN>