• List databases registered with rman repository

select NAME
     , DBID
  from RC_DATABASE;

• List rman backup STATUS details with database name, Start time, End time , Processed MB/GB

SELECT db_name, row_type, start_time, end_time, operation, status, mbytes_processed
    FROM   rc_rman_status
    WHERE  db_name like  ‘PROD%’
      AND  start_time > SYSDATE – 1
    ORDER  BY END_TIME desc;

• List backup details of all registered RMAN databases 

SELECT  db_name
      , start_time
      , row_type
      , end_time
      , operation
      , status
      , mbytes_processed
   FROM rc_rman_status
  WHERE start_time > SYSDATE – 1
  ORDER BY DB_NAME
  , start_time;

• Show backups which are still RUNNING

SELECT  db_name
      , start_time
      , row_type
      , operation
      , status
  FROM rc_rman_status
  WHERE start_time > SYSDATE – 1
  AND STATUS = ‘RUNNING’
  ORDER BY DB_NAME
  , start_time;

• List db name, tablespace & datafiles backed up with size

select DB_NAME
     , TABLESPACE_NAME
     , FILE#
     , BYTES/1000000000 SIZE_GB
     , NAME
  from RC_DATAFILE
 where DB_NAME = ‘PROD’;

• List RMAN backup configuration Parameters

select
DB_UNIQUE_NAME
,NAME
,VALUE         
from RC_RMAN_CONFIGURATION
 where DB_UNIQUE_NAME =’PROD’
order by 1,2,3;

ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE      DISK TO 1
ARCHIVELOG DELETION POLICY                    TO NONE
BACKUP OPTIMIZATION                           OFF
CHANNEL                                       DEVICE TYPE DISK FORMAT   '/mnt/backup/Rman/PROD1/%d_%s_%p_%U'
CONTROLFILE AUTOBACKUP                        ON
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/mnt/backup/Rman/PROD1/%F'
DATAFILE BACKUP COPIES FOR DEVICE TYPE        DISK TO 1
DEVICE TYPE                                   DISK PARALLELISM 3 BACKUP TYPE TO COMPRESSED BACKUPSET
MAXSETSIZE TO                                 UNLIMITED
SNAPSHOT CONTROLFILE NAME                     TO '/opt/app/oracle/product/11.2/db_1/dbs/snapcf_RMANTST.f'  

• List the most recent FULL Database backup with STATUS=’COMPLETED’

select   a.db_name
Database
       , db.dbid DBID
       , a.end_time Latest_Backup
       , round ( a.output_bytes / 1000000000 ) GBytes_Processed
       , round ( ( end_time – start_time ) * 60 * 24 ) Minutes
    from rc_rman_status a
       , rc_database db
   where object_type in ( ‘DB FULL’
   , ‘DB INCR’ )
     and status = ‘COMPLETED’
     and operation = ‘BACKUP’
     and a.db_name = ‘&&DB_NAME’
     and end_time in ( select end_time
           from rc_rman_status b
          where b.db_name = a.db_name
            and b.db_key = a.db_key
            and object_type in ( ‘DB FULL’
          , ‘DB INCR’ )
            and status = ‘COMPLETED’
            and operation = ‘BACKUP’ )
     and db.db_key = a.db_key
   order by a.db_name
   , end_time desc;

• List Backups for a Specific Database Instance ‘&&DB_NAME’

select   a.db_name
Database
       , db.dbid DBID
       , a.end_time Latest_Backup
       , round ( a.output_bytes / 1000000000 ) GBytes_Processed
       , round ( ( end_time – start_time ) * 60 * 24 ) Minutes_Taken
    from rman.rc_rman_status a
       , rman.rc_database db
   where object_type in ( ‘DB FULL’
   , ‘DB INCR’ )
     and status = ‘COMPLETED’
     and operation = ‘BACKUP’
     and a.db_name = ‘&&DB_NAME’
     and end_time in ( select end_time
           from rman.rc_rman_status b
          where b.db_name = a.db_name
            and b.db_key = a.db_key
            and object_type in ( ‘DB FULL’
          , ‘DB INCR’ )
            and status = ‘COMPLETED’
            and operation = ‘BACKUP’ )
     and db.db_key = a.db_key
   order by a.db_name
   , end_time desc;

DATABASE       DBID LATEST_BA GBYTES_PROCESSED MINUTES_TAKEN
-------- ---------- --------- ---------------- -------------
PROD   4020163152 22-SEP-11                0            20
PROD   4020163152 21-SEP-11                0            19
PROD   4020163152 20-SEP-11                0            17
PROD   4020163152 19-SEP-11                0            16
PROD   4020163152 18-SEP-11                4            13
PROD   4020163152 17-SEP-11                0            15

• List Archivelog backups

SELECT a.db_name “Database”
     , db.dbid “DBID”
     , a.end_time “Latest Backup”
     , ROUND ( a.output_bytes / 1000000 ) “MBytes Processed”
     , ROUND ( ( end_time – start_time ) * 60 * 24 ) “Minutes Taken”
  FROM rman.rc_rman_status a
     , rman.rc_database db
 WHERE object_type = ‘ARCHIVELOG’
   AND status = ‘COMPLETED’
   AND operation = ‘BACKUP’
   AND a.db_name = ‘&&DB_NAME’
   AND end_time IN ( SELECT end_time
         FROM rman.rc_rman_status b
        WHERE b.db_name = a.db_name
          AND b.db_key = a.db_key
          AND object_type = ‘ARCHIVELOG’
          AND status = ‘COMPLETED’
          AND operation = ‘BACKUP’ )
   AND db.db_key = a.db_key
 ORDER BY a.db_name
 , end_time desc;

• List all Database FULL backups done in last 30 days

SELECT  SUBSTR ( TO_CHAR ( START_TIME
    , ‘DD-MON-YYYY HH24:MI’ )
        , 1
        , 20 )
      , SUBSTR ( OBJECT_TYPE
  , 1
  , 20 )
   FROM rman.rc_rman_status
  WHERE start_time > SYSDATE – 30
    AND object_type = ‘DB FULL’
  ORDER BY 1 DESC;

• Is RMAN Backup Still Running?

SELECT TO_CHAR ( start_time
        , ‘DD-MON-YY HH24:MI’ ) “BACKUP STARTED”
     , sofar
     , totalwork
     , elapsed_seconds / 60 “ELAPSE (Min)”
     , ROUND ( sofar / totalwork * 100
       , 2 ) “Complete%”
  FROM sys.v_$session_longops
 WHERE opname = ‘dbms_backup_restore’;

Here I am duplicating 11g RAC database from one RHEL Server to Another by old 10g method.
I could have used 11g “DUPLICATE TARGET DATABASE TO TARGET_DB FROM ACTIVE DATABASE” which doesn’t need previous rman backup at source. But it may not be a good option for large databases or at places with narrow network bandwidth.

Assumptions Made:

– RAC Clusterware and Database binaries are installed at Destination Nodes
– Clusterware services “crsctl check crs” reported active

PRIMARY site Tasks (Ora01a1,Ora01a2):

• Create FULL RMAN Backup
• Copy backup files from PRIMARY server to New server
• Create pfile from spfile at source RAC
• Copy init.ora from $Primary_Server:ORACLE_HOME/dbs to $New_Server:ORACLE_HOME/dbs
• Copy $Primary_Server:ORACLE_HOME/dbs/password file to $New_Server:ORACLE_HOME/dbs

[oracle@Ora01a1 RAC1]$ scp Ora01a1BKUP.tgz oracle@Node1:/mnt/data
Warning: Permanently added (RSA) to the list of known hosts.
Ora01a1BKUP.tgz                                                          100%  274MB  11.4MB/s   00:24

SQL> show parameter pfile
NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      /mnt/data/oradata/primary/spfileRAC.ora

SQL> show parameter spfile
NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      /mnt/data/oradata/primary/spfileRAC.ora

SQL> create pfile=’/mnt/data/oradata/primary/init.ora’ from spfile;
File created

[oracle@Ora01a1 RAC]$ scp init.ora oracle@Node1:/mnt/data/rman_backups/bkup/init.ora 100% 1612     1.6KB/s   00:00
[oracle@Ora01a1 dbs]$ scp /mnt/data/oradata/primary/orapwRAC oracle@Node1:/mnt/data/rman_backups/bkup   orapwRAC 100% 1536     1.5KB/s   00:00

Destination Site Tasks (Node1,Node2)
Create required directories for bdump,adump as well as database mount volumes.

[oracle@Node1]$ grep /mnt initRAC.ora
*.control_files=’/mnt/data/oradata/primary/control01.ctl’,’/mnt/data/oradata/primary/control02.ctl’
*.db_recovery_file_dest=’/mnt/logs/oradata/primary/fast_recovery_area’
*.log_archive_dest_1=’LOCATION=/mnt/logs/oradata/primary/arch’

[oracle@Node1]$ mkdir -p /mnt/data/oradata/primary/
[oracle@Node1]$ mkdir -p /mnt/logs/oradata/primary/fast_recovery_area
[oracle@Node1]$ mkdir -p /mnt/logs/oradata/primary/arch

“opt” is a local volume for each instance so create directories on both RAC nodes
[oracle@Node1]$ grep /opt initRAC.ora
*.audit_file_dest=’/opt/app/oracle/admin/primary/adump’
*.diagnostic_dest=’/opt/app/oracle’

[oracle@Node1]$ mkdir -p /opt/app/oracle/admin/primary/adump
[oracle@Node1]$ mkdir -p /opt/app/oracle

[oracle@Node2]$ mkdir -p /opt/app/oracle/admin/primary/adump
[oracle@Node3]$ mkdir -p /opt/app/oracle

Under 11g background trace will be maintained at “$ORACLE_BASE/diag/rdbms”, if required create necessary directories there.

Modify init.ora file ($ORACLE_HOME/dbs/init.ora) and amend/change parameters. I had to comment out “remote_listener” parameter as the serversnames at destination are different.

Copy init.ora at both nodes “Node1,Node2″@$ORACLE_HOME/dbs

[oracle@Node1 dbs]$ cp initRAC.ora initRAC1.ora

[oracle@Node1 dbs]$ echo $ORACLE_SID
RAC1
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 9152860160 bytes
Fixed Size                  2234056 bytes
Variable Size            6945769784 bytes
Database Buffers         2181038080 bytes
Redo Buffers               23818240 bytes

[oracle@Node1 dbs]$ rman target / nocatalog
connected to target database: RAC (not mounted)
using target database control file instead of recovery catalog
RMAN> restore controlfile from ‘/mnt/data/rman_backups/bkup/c-4020163152-20110405-01’;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=588 instance=RAC1 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/mnt/data/oradata/primary/control01.ctl
output file name=/mnt/data/oradata/primary/control02.ctl
.
…
………
Finished restore at 05-APR-11

Verify controlfiles are copied at right location

[oracle@Node2 RAC]$ pwd
/mnt/data/oradata/RAC

[oracle@Node2 RAC]$ ls -lrt
-rw-r—–  1 oracle oinstall 22986752 Apr  5 16:35 control01.ctl
-rw-r—–  1 oracle oinstall 22986752 Apr  5 16:35 control02.ctl

RMAN> alter database mount;
database mounted
RMAN> RESTORE DATABASE;
Starting restore at 05-APR-11
Starting implicit crosscheck backup at 05-APR-11
allocated channel: ORA_DISK_1
allocated channel: ORA_DISK_2
******* This returned errors
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/05/2011 16:36:54
RMAN-06026: some targets not found – aborting restore
RMAN-06023: no backup or copy of datafile 3 found to restore

RMAN was not able to locate backup since backupset was not registered with rman inventory & copied at different location. Let’s catalog backup pieces that were shipped from Primary database.

I have multiple copies of backup files so I used
RMAN> CATALOG START WITH ‘/mnt/data/rman_backups/bkup/’ NOPROMPT;
List of Cataloged Files
=======================
File Name: /mnt/data/rman_backups/bkup/c-4020163152-20110405-04
File Name: /mnt/data/rman_backups/bkup/c-4020163152-20110405-05
File Name: /mnt/data/rman_backups/bkup/db_bk_ub8m91cg7_s3432_p1_t747680263.bkp
File Name: /mnt/data/rman_backups/bkup/db_bk_ub9m91cg8_s3433_p1_t747680264.bkp
File Name: /mnt/data/rman_backups/bkup/db_bk_ubam91cg9_s3434_p1_t747680265.bkp
File Name: /mnt/data/rman_backups/bkup/db_bk_ubcm91cgi_s3436_p1_t747680274.bkp
File Name: /mnt/data/rman_backups/bkup/db_bk_ubdm91cgi_s3437_p1_t747680274.bkp
File Name: /mnt/data/rman_backups/bkup/db_bk_ubbm91cgi_s3435_p1_t747680274.bkp
File Name: /mnt/data/rman_backups/bkup/db_bk_ubem91ck0_s3438_p1_t747680384.bkp
File Name: /mnt/data/rman_backups/bkup/db_bk_ubfm91ck0_s3439_p1_t747680384.bkp
File Name: /mnt/data/rman_backups/bkup/ctl_bk_ubhm91ck3_s3441_p1_t747680387.bkp

RMAN> RESTORE DATABASE;
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /mnt/data/oradata/primary/system01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /mnt/data/oradata/primary/undotbs02.dbf
channel ORA_DISK_1: reading from backup piece /mnt/data/rman_backups/bkup/db_bk_ubdm91cgi_s3437_p1_t747680274.bkp
.
..
channel ORA_DISK_1: piece handle=/mnt/data/rman_backups/bkup/db_bk_ubdm91cgi_s3437_p1_t747680274.bkp tag=TAG20110405T165753
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:26
channel ORA_DISK_2: piece handle=/mnt/data/rman_backups/bkup/db_bk_ubcm91cgi_s3436_p1_t747680274.bkp tag=TAG20110405T165753
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:01:26
channel ORA_DISK_3: piece handle=/mnt/data/rman_backups/bkup/db_bk_ubbm91cgi_s3435_p1_t747680274.bkp tag=TAG20110405T165753
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:01:56
Finished restore at 05-APR-11

RMAN> recover database;
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=3289
channel ORA_DISK_1: reading from backup piece /mnt/data/rman_backups/bkup/db_bk_ubem91ck0_s3438_p1_t747680384.bkp
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=2 sequence=3484
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/05/2011 17:17:46
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 3290 and starting SCN of 246447604

RMAN> ALTER DATABASE OPEN RESETLOGS;
database opened

Shutdown and restart database RAC1

SQL> shutdown abort;
ORACLE instance shut down.

set sqlprompt ‘&_CONNECT_IDENTIFIER > ‘
RAC1> startup;
ORACLE instance started.
Total System Global Area 9152860160 bytes
Fixed Size                  2234056 bytes
Variable Size            6945769784 bytes
Database Buffers         2181038080 bytes
Redo Buffers               23818240 bytes
Database mounted.
Database opened.

RAC1> set linesize 200;
RAC1> set pagesize 20;
RAC1> select inst_id,substr(member,1,35) from gv$logfile;
INST_ID SUBSTR(MEMBER,1,35)
———- ——————————————————————————————————————————————–
1 /mnt/data/oradata/primary/redo02.log
1 /mnt/data/oradata/primary/redo01.log
1 /mnt/data/oradata/primary/redo03.log
1 /mnt/data/oradata/primary/redo04.log

I can see , INSTANCE 2 REDO log files are not listed so startup RAC2 instance at Node2

[oracle@Node2 dbs]$ echo $ORACLE_SID
RAC2
SQL> set sqlprompt ‘&_CONNECT_IDENTIFIER > ‘
RAC2> startup;
ORACLE instance started.
Total System Global Area 9152860160 bytes
Fixed Size                  2234056 bytes
Variable Size            6610225464 bytes
Database Buffers         2516582400 bytes
Redo Buffers               23818240 bytes
Database mounted.
Database opened.

I can now locate REDO files for Instance 1 as well as 2

select inst_id,substr(member,1,35) from gv$logfile;
INST_ID SUBSTR(MEMBER,1,35)
———- ——————————————————————————————————————————————–
2 /mnt/data/oradata/primary/redo02.log
2 /mnt/data/oradata/primary/redo01.log
2 /mnt/data/oradata/primary/redo03.log
2 /mnt/data/oradata/primary/redo04.log
1 /mnt/data/oradata/primary/redo02.log
1 /mnt/data/oradata/primary/redo01.log
1 /mnt/data/oradata/primary/redo03.log
1 /mnt/data/oradata/primary/redo04.log
8 rows selected.

I will carry log switchs to see ARCHIVE files create at Archive Destination “/mnt/logs/oradata/primary/arch”

RAC1 > alter system switch logfile;
System altered.
RAC1 > /
System altered.
RAC2 > alter system switch logfile;
System altered.
RAC2 > /
System altered.
[oracle@Node2 arch]$ pwd
/mnt/logs/oradata/primary/arch
[oracle@Node2 arch]$ ls -lrt
total 5348
-rw-r—–  1 oracle oinstall  777216 Apr  6 10:00 1_10_747681489.arc
-rw-r—–  1 oracle oinstall    4096 Apr  6 10:00 1_11_747681489.arc
-rw-r—–  1 oracle oinstall 4667392 Apr  6 10:00 2_11_747681489.arc
-rw-r—–  1 oracle oinstall   56832 Apr  6 10:01 2_12_747681489.arc

We have some background jobs in this database. I will set them to sleep at both databases for some time

RAC1 > alter system set job_queue_processes=0;
System altered.

RAC2 > alter system set job_queue_processes=0;
System altered.

See if there are any alrtlog errors reported at nodes node1/node2 before Registering  database with CRS

RAC1> create spfile from pfile;
File created.

[oracle@Node1 dbs]$ pwd
/opt/app/oracle/product/11.2/db_1/dbs
-rw-r—–  1 oracle oinstall     3584 Apr  6 10:20 spfileRAC1.ora

Move spfile at a shared clustered location accessible to both Nodes/Instances RAC1/RAC2.

cp spfileRAC1.ora /mnt/data/oradata/primary/spfileRAC.ora

[oracle@(RAC1 or RAC2 ) ]$ df -k
/dev/mapper/System-Opt 20314748  14636172   4630208  76% /opt   — Local Storage
NETAPP_Server:/vol/prod_data 52428800  33919456  18509344  65% /mnt/data — Clustered Storage

[oracle@RAC1 PROD]$ ls -l /mnt/data/oradata/primary/spfile*
-rw-r—– 1 oracle oinstall 7680 May 10 15:18 spfileRAC.ora

Link individual init files on nodes RAC1/RAC2 to spfile

[oracle@RAC1]$ cd $ORACLE_HOME/dbs

[oracle@RAC1 dbs]$ cat initRAC1.ora
SPFILE=’/mnt/data/oradata/primary/spfileRAC.ora’

[oracle@RAC2 dbs]$ cat initRAC2.ora
SPFILE=’/mnt/data/oradata/primary/spfileRAC.ora’

Registering  database with CRS

[oracle@Node1 dbs]$ srvctl add database -d RAC -o /opt/app/oracle/product/11.2/db_1 -p  /mnt/data/oradata/primary/spfileRAC.ora
[oracle@Node1 dbs]$ srvctl add instance -d RAC -i RAC1 -n Node1
[oracle@Node1 dbs]$ srvctl add instance -d RAC -i RAC2 -n Node2
[oracle@Node2 arch]$ crsstat.sh  | grep RAC
ora.RAC.db                                 OFFLINE    OFFLINE

Before using services, we must check the cluster configuration is correct

[oracle@Node1 dbs]$ srvctl config database -d RAC
Database unique name: RAC
Database name:
Oracle home: /opt/app/oracle/product/11.2/db_1
Oracle user: oracle
Spfile: /mnt/data/oradata/primary/spfileRAC.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: RAC
Database instances: RAC1,RAC2
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is administrator managed

[oracle@Node1 dbs]$ srvctl start database -d RAC
PRCR-1079 : Failed to start resource ora.RAC.db
CRS-5017: The resource action “ora.RAC.db start” encountered the following error:
ORA-29760: instance_number parameter not specified

Solution of the Problem
srvctl is case sensitive. So we need to ensure that instance and database definitions set in spfile/pfile are same case as those in the OCR and as are used in the srvctl commands. I made a mistake here and added “GDPROD1/2” in lowercase “RAC1/RAC2” while creating services.
Before going into solution be sure that ORACLE_SID reflects correct case so that instance can be accessed using SQL*Plus

I will have to remove services created earlier and add them with “UPPERCASE” instance name

[oracle@Node1 dbs]$ srvctl remove database -d RAC

Remove the database RAC? (y/[n]) y
[oracle@Node1 dbs]$ srvctl remove instance -d RAC -i RAC1
PRCD-1120 : The resource for database RAC could not be found.
PRCR-1001 : Resource ora.RAC.db does not exist
[oracle@Node1 dbs]$ srvctl remove instance -d RAC -i RAC2
PRCD-1120 : The resource for database RAC could not be found.

[oracle@Node1 dbs]$ srvctl add database -d RAC -o /opt/app/oracle/product/11.2/db_1 -p /mnt/data/oradata/primary/spfileRAC.ora
[oracle@Node1 dbs]$ srvctl add instance -d RAC -i RAC1 -n Node1
[oracle@Node1 dbs]$ srvctl add instance -d RAC -i RAC2 -n Node2
[oracle@Node2 arch]$ crsstat.sh  | grep RAC
ora.RAC.db                                 OFFLINE OFFLINE

Moment of TRUTH , start the Database

[oracle@Node1 dbs]$ srvctl start database -d RAC
[oracle@Node1 dbs]$ crsstat.sh  | grep RAC
ora.RAC.db                                 ONLINE ONLINE on Node1

[oracle@Node1 ~]$ export ORACLE_SID=RAC1
SQL> set sqlprompt ‘&_CONNECT_IDENTIFIER > ‘
RAC1 > archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /mnt/logs/oradata/primary/arch
Oldest online log sequence     18
Next log sequence to archive   19
Current log sequence           19

SQL>  set sqlprompt ‘&_CONNECT_IDENTIFIER > ‘
RAC2 > archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /mnt/logs/oradata/primary/arch
Oldest online log sequence     20
Next log sequence to archive   21
Current log sequence           21

Finally have a look at alrtlog for any issues reported

To test session failover , I will create SQLPLUS connection and see if  it gets migrated to other node when instance goes down.

SQL> select machine from v$session where rownum <5;
MACHINE
—————————————————————-
Node1
Node1
Node1
Node1

Node1 RAC1> shutdown abort;
ORACLE instance shut down.

SQL> select machine from v$session where rownum <5;
MACHINE
—————————————————————-
Node2
Node2
Node2
Node2

Number of my systems are on Oracle 10g with flashback Area allocated. The Archive logs , redo logs & backups are destined at this area. This morning users complained about system being down and when I looked at alrtlog I could see archive error.

SQL> show parameter  recovery

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_recovery_file_dest                string      /u07/backup/oat/flash_recovery_area

db_recovery_file_dest_size     big integer 50G

recovery_parallelism                 integer     0

Let’s have a look at Alrtlog file

SQL> show parameter background

NAME                                 TYPE        VALUE

———————————— ———– ——————————

background_core_dump                 string      partial

background_dump_dest                 string      /u01/app/oracle/product/10.2.0/admin/oat/bdump

Tail alrtlog

ORA-19815: WARNING: db_recovery_file_dest_size of 53687091200 bytes is 99.97% used, and has 14042624 remaining bytes available.

Tue Jun 15 09:52:12 2010

Errors in file /u01/app/oracle/product/10.2.0/admin/at/bdump/oat_arc4_7353.trc:

ORA-16038: log 1 sequence# 7603 cannot be archived

ORA-19815: WARNING: db_recovery_file_dest_size of 53687091200 bytes is 99.97% used, and has 14042624 remaining bytes available.

Tue Jun 15 08:03:57 2010

************************************************************************

You have following choices to free up space from flash recovery area:

1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,

then consider changing RMAN ARCHIVELOG DELETION POLICY.

2. Back up files to tertiary device such as tape using RMAN

BACKUP RECOVERY AREA command.

3. Add disk space and increase db_recovery_file_dest_size parameter to

reflect the new space.

4. Delete unnecessary files using RMAN DELETE command. If an operating

system command was used to delete files, then use RMAN CROSSCHECK and

DELETE EXPIRED commands.

************************************************************************

SQL> select space_used/(1024*1024),space_limit/(1024*1024) from v$recovery_file_dest;

SPACE_USED/(1024*1024) SPACE_LIMIT/(1024*1024)

———————- ———————–

51200                  51200

Quick Fix :

$ du /u07/backup/oat/flash_recovery_area/OAT/archivelog/  — To locate space used

$ cd /u07/backup/oat/flash_recovery_area/OAT/archivelog/

$ find -name ‘*.arc’ -mtime +2 -exec rm {} \; — Delete archive files older than 2 days

Just deleting archives is no good and we  need to update catalog with deleted file details

$ rman target / nocatalog

RMAN> crosscheck archivelog all;

RMAN> delete noprompt expired archivelog all;

SQL> select space_used/(1024*1024),space_limit/(1024*1024) from v$recovery_file_dest;

SPACE_USED/(1024*1024) SPACE_LIMIT/(1024*1024)

———————- ———————–

2932.44385                   51200

OR Add more space

SQL> select space_used/(1024*1024),space_limit/(1024*1024) from v$recovery_file_dest;

SPACE_USED/(1024*1024) SPACE_LIMIT/(1024*1024)

———————- ———————–

3227.13867                    4032

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE =20G scope=Both sid=’*’;

System altered.

SQL> select space_used/(1024*1024),space_limit/(1024*1024) from v$recovery_file_dest;

SPACE_USED/(1024*1024) SPACE_LIMIT/(1024*1024)

———————- ———————–

3941.9248                   20480

I carried a big data upload and as a result of it my archive volume filled up in no time. As matter of urgency I started moving archive logs without backup to another destination. The next thing to go wrong was my nightly backup.

current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 03/10/2010 12:00:01
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /u06/oradata/sit/prod/1_38525_700413864.arc
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Run rman>crosscheck archivelog all;

archive log filename=/u06/oradata/prod/arch/1_39458_700413864.arc recid=57937 stamp=713233333
validation succeeded for archived log
archive log filename=/u06/oradata/prod/arch/1_39459_700413864.arc recid=57938 stamp=713275039
validation succeeded for archived log
archive log filename=/u06/oradata/prod/arch/1_39460_700413864.arc recid=57939 stamp=713275200
Crosschecked 936 objects

Fired backup again and it was OK.

I have more than 100 database servers.

How can I report if my backup was successful last night or last week?

Normally one can use shell scripts and grep rman log for errors but here is a better way.

select to_char(START_TIME,’DD MON YY HH24:Mi’) START_TIME ,
to_char(END_TIME,’DD MON YY HH24:Mi’) END_TIME ,
OUTPUT_BYTES/1000000  PROCESSED_IN_MB,STATUS
from v$rman_status where trunc(START_TIME)= trunc(sysdate)

Please edit trunc(sysdate) for DAY you need to see backup details

START_TIME      END_TIME        PROCESSED_IN_MB STATUS
————— ————— ————— ———————–
25 JAN 10 15:05 25 JAN 10 15:07      2041.57747 RUNNING
25 JAN 10 15:05 25 JAN 10 15:07               0 RUNNING
25 JAN 10 07:00 25 JAN 10 07:00               0 COMPLETED
25 JAN 10 14:50 25 JAN 10 14:51               0 FAILED
25 JAN 10 14:48 25 JAN 10 14:48               0 COMPLETED
25 JAN 10 07:00 25 JAN 10 07:00               0 COMPLETED
25 JAN 10 14:50 25 JAN 10 14:51               0 COMPLETED WITH ERRORS
25 JAN 10 07:00 25 JAN 10 07:00               0 COMPLETED WITH WARNINGS
25 JAN 10 14:48 25 JAN 10 14:48               0 COMPLETED
25 JAN 10 07:00 25 JAN 10 07:00               0 COMPLETED

I want to see if my backups are growing over time.

select trunc(START_TIME),sum(OUTPUT_BYTES)/1000000  PROCESSED_IN_MB
from v$rman_status where STATUS =’COMPLETED’
group by trunc(START_TIME)
order by 1 desc

TRUNC(START_TIME)	PROCESSED_IN_MB
08/07/2010	0
07/07/2010	109935.0671
06/07/2010	50093.3591
05/07/2010	49868.96384
04/07/2010	49808.14643
03/07/2010	49803.95213
02/07/2010	49801.85498
01/07/2010	99461.10362
30/06/2010	51695.32109

How much of TAPE/Disk space have been used by backups

select sum(OUTPUT_BYTES)/1000000  PROCESSED_IN_MB
from v$rman_status where STATUS =’COMPLETED’

select to_char(START_TIME,’DD MON YY HH24:Mi’) START_TIME ,STATUS,OPERATION
from v$rman_status where STATUS like ‘%ERROR%’
order by 1 desc

You can use the create RMAN DUPLICATE command to duplicate database from target database backups while still retaining the original target database. The duplicate database can be either identical to the original database or contain only a subset of the original tablespaces. A duplicate database is a copy of the target database that you can run independently for a variety of purposes.

Read more…

Below is a windows BATCH script used to Maintain RMAN catalogue. The script accepts 3 parameters Target SID, Target password and catalogue password.

@echo off
REM
REM NT RMAN catalogue maintenance
REM
REM 3 parameters SID, target password and catalogue password
REM

set ORACLE_SID=%1
set LOG=C:\rmanscripts\maintain_%1.log

REM
REM First generate the rman script using the two supplied parameters
REM

echo connect target rmantarget/%2@%1 >C:\rmanscripts\maintain_catalog.rman
echo connect catalog rman/%3@recovery_catalog >>C:\rmanscripts\maintain_catalog.rman
echo allocate channel for maintenance type “sbt_tape” >>C:\rmanscripts\maintain_catalog.rman
echo crosscheck backup of database; >>C:\rmanscripts\maintain_catalog.rman
echo release channel; >>C:\rmanscripts\maintain_catalog.rman
echo allocate channel for maintenance type “sbt_tape” >>C:\rmanscripts\maintain_catalog.rman
echo crosscheck backup of archivelog all; >>C:\rmanscripts\maintain_catalog.rman
echo release channel; >>C:\rmanscripts\maintain_catalog.rman
echo allocate channel for delete type “sbt_tape”; >>C:\rmanscripts\maintain_catalog.rman
echo delete noprompt expired backup; >>C:\rmanscripts\maintain_catalog.rman
echo release channel; >>C:\rmanscripts\maintain_catalog.rman
echo exit >>C:\rmanscripts\maintain_catalog.rman

REM
REM Second generate the email call script
REM

echo connect rman/%3@recovery_catalog >C:\rmanscripts\send_email.sql
echo define subject=^&1 >>C:\rmanscripts\send_email.sql
echo define sender=^&2 >>C:\rmanscripts\send_email.sql
echo execute send_email_header(‘^&subject’,’^&sender’); >>C:\rmanscripts\send_email.sql
echo exit >>C:\rmanscripts\send_email.sql

date /t> %LOG%
time /t >> %LOG%
For /F “tokens=1,2,3,4* delims=/, ” %%i in (‘date/T’) Do SET DDMMYYYY=%%i-%%j-%%k

rman msglog=%LOG% cmdfile=C:\rmanscripts\maintain_catalog.rman

if not errorlevel 1 (
sqlplus -s /nolog @C:\rmanscripts\send_email.sql \”‘RMAN catalog maintenance complete on %DDMMYYYY%’\” %COMPUTERNAME%
) else (
sqlplus -s /nolog @C:\rmanscripts\send_email.sql \”‘RMAN catalog maintenance failure on %DDMMYYYY%’\” %COMPUTERNAME%
)
exit

Please compile send_email_header pl/sql procedure as rman user.

CREATE OR REPLACE PROCEDURE send_email_header(
msg_subject varchar2 ,
msg_sendner varchar2 )
IS
c utl_tcp.connection;
rc integer;
BEGIN
c := utl_tcp.open_connection(‘127.0.0.1’, 25); — open the SMTP port 25 on local machine
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, ‘HELO localhost’);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, ‘MAIL FROM: ‘||msg_sender);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, ‘RCPT TO: ‘||’dba@oracledbasupport.co.uk’);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, ‘QUIT’);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
utl_tcp.close_connection(c); — Close the connection
EXCEPTION
when others then
raise_application_error(
-20000, ‘Unable to send e-mail message from pl/sql because of: ‘||
sqlerrm);
END;
/
show errors

Duplicate Database using rman

connect target rman/%password%@primary_server
connect auxiliary sys/%password%@standby_server
connect rcvcat rman/%password%@recovery_catalog
run {
set until time ’27-JUL-2009 10:59:00′;
allocate auxiliary channel tp1 type “sbt_tape”;
allocate auxiliary channel tp2 type “sbt_tape”;

duplicate target database to %target_instance_name%;
release channel tp1;
release channel tp2;
}

Restore rman database for Standby

connect target rman/%password%@primary_server
connect auxiliary sys/%password%@standby_server
connect rcvcat rman/%password%@recovery_catalog
Run {
set until time ’20-SEP-2009 06:03:00′;
allocate auxiliary channel tp1 type “sbt_tape”;
allocate auxiliary channel tp2 type “sbt_tape”;

duplicate target database for standby dorecover nofilenamecheck;
release channel tp1;
release channel tp2;
}

rman> shutdown immediate;
rman> startup mount

1. Let’s validate if there is ample of space for backup at Target device type i.e DISK/TAPE

ALLOCATE CHANNEL c1 DEVICE TYPE sbt;

RMAN>
run {
ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
BACKUP VALIDATE DATABASE;
}

allocated channel: c1
channel c1: sid=18 devtype=SBT_TAPE
channel c1: VERITAS NetBackup for Oracle – Release 4.5MP6 (00000000)

Starting backup at 14-JAN-09
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset

input datafile fno=00010 name=/oradata/oradataTEST/tspace/TESTO5TEST_TESTmes_idx_01.dbf
input datafile fno=00007 name=/oradata/oradataTEST/tspace/TESTO5TEST_TESTtrn_dat_01.dbf
input datafile fno=00009 name=/oradata/oradataTEST/tspace/TESTO5TEST_TESTmes_dat_01.dbf
input datafile fno=00017 name=/oradata/oradataTEST/tspace/TESTO5TEST_TESTtrn_idx_02.dbf
input datafile fno=00019 name=/oradata/oradataTEST/tspace/TESTO5TEST_TESTvch_idx_02.dbf

2. If validate doesn’t return any errors go head with following script to backup entire database.

rman target sys/manager nocatalog

RMAN> shutdown immediate

RMAN> startup mount

RMAN>
run { allocate channel c1 device type disk format ‘%Directory%\backup_db_%d_S_%s_P_%p_T_%t’;
# I need to create a compressed backup set due to lack of drive space.
# The entire database backup was compressed from 77GB to 5GB (10G new feature) so it does work well.
backup as compressed backupset full skip inaccessible
tag rman_backup_Offline_full
filesperset 10
(database include current controlfile);
delete obsolete;
crosscheck backup
}

— Server A – Is a Live Server
— Server B – Is a Physical Standby Server
Both were configured for a data guard

One fine day a standby blows away and we need to recreate it from Live

At Live Server A

1. There is no need but if you want please disable Dataguard Broker

SQLPLUS> show parameter dg_broker_start

NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean TRUE

SQLPLUS> alter system set dg_broker_start=FALSE

DGMGRL> show configuration verbose;

Configuration
Name: odba
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
catp_hongkong – Primary database
catp_morocco – Physical standby database (disabled)

2. Put current database into a HOT backup mode.
Backup Datafiles, Controlfiles and Redo logs

Copy files from backup set on a standby database.

At Standby Server B

3. Please make sure following parameters are set on a Physical Standby database init.ora file

4. Recover Standby Database Now

SQL> connect / as sysdba
Connected.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle1/oradata/catp
Oldest online log sequence 199166
Next log sequence to archive 199169
Current log sequence 199169
SQL> show parameter dg_broker_start

NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean FALSE

SQL> alter system set dg_broker_start=TRUE scope=both;
System altered.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 2030296 bytes
Variable Size 503317800 bytes
Database Buffers 1627389952 bytes
Redo Buffers 14745600 bytes

SQL> show parameter dg_broker_start

NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean TRUE

SQL> recover managed standby database disconnect from session;
ORA-01507: database not mounted

SQL> alter database mount standby database;

Database altered.

SQL> recover managed standby database disconnect from session;
Media recovery complete.

Monitor Alrt log for messages for ARCHIVE Success

Media Recovery Waiting for thread 1 sequence 199169
Fetching gap sequence in thread 1, gap sequence 199169-199177
Tue Jun 5 15:54:45 2007
Redo Shipping Client Connected as PUBLIC
— Connected User is Valid
RFS[2]: Assigned to RFS process 3397
RFS[2]: Identified database type as ‘physical standby’
RFS[2]: No standby redo logfiles created
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199179_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199169_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199170_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199171_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199172_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199173_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199174_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199175_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199176_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199177_1_528884944.arc’
Tue Jun 5 15:55:15 2007
Media Recovery Log /oracle1/oradata/catp/catp_0000199169_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199170_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199171_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199172_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199173_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199174_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199175_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199176_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199177_1_528884944.arc
Media Recovery Waiting for thread 1 sequence 199178
Fetching gap sequence in thread 1, gap sequence 199178-199178
Tue Jun 5 15:55:18 2007
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199178_1_528884944.arc’

You can check v$archived_log and v$dataguard_status views to find out logs applied and errors if any respectively.

1. Logs Applied on Standby
Select sequence#, applied from v$archived_log where STANDBY_DEST=’YES’ order by sequence#;
OR
select sequence#, applied ,completion_time from v$archived_log where STANDBY_DEST=’YES’ order by 3 desc

2. Error message at Dataguard
Select to_char(TIMESTAMP, ‘dd-mon-yyyy hh24:mm:ss’), MESSAGE from v$dataguard_status;

Sometimes Oracle throws error messages at ALRTLOG – Failed to request gap sequence

Reason : DG FAL failed to copy archive files from PRIMARY to STANDBY

Media Recovery Waiting for thread 1 sequence 84730
Fetching gap sequence in thread 1, gap sequence 84730-85042
FAL[client]: Trying FAL server: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=zagreb)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=bfp_zagreb_XPT.yell)(SERVER=dedicated)))
Mon Nov 12 15:06:45 2007
Completed: ALTER DATABASE RECOVER managed standby database d
Mon Nov 12 15:11:45 2007
FAL[client]: Failed to request gap sequence for thread 1 gap sequence 84730-85042
FAL[client]: All defined FAL servers have been attempted.

Fix:

SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> Select sequence#, applied from v$archived_log where STANDBY_DEST=’YES’ order by sequence#;

no rows selected

SQL> recover standby database;
ORA-01153: an incompatible media recovery is active

STOP EARLIER recovery session

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> recover standby database;
ORA-00279: change 1967405177515 generated at 11/08/2007 10:06:55 needed for
thread 1
ORA-00289: suggestion : /oracle1/oradata/bfp/bfp_1_569156389_0000084730.arc
ORA-00280: change 1967405177515 for thread 1 is in sequence #84730

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto

This will end up as

ORA-00308: cannot open archived log
‘/oracle1/oradata/bfp/bfp_1_569156389_0000084800.arc’
ORA-27037: unable to obtain file status
HPUX-ia64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

Rman Script to Create Standby From Live Server
connect target rman/obst@live
connect auxiliary sys/ams@standby
connect rcvcat rmanc/obss@rcvcat
Run {
allocate auxiliary channel tp1 type “sbt_tape”;
allocate auxiliary channel tp2 type “sbt_tape”;
send ‘NSR_ENV=(NSR_SERVER=bsbk10,NSR_DATA_VOLUME_POOL=Default,NSR_CLIENT=miles)’;
duplicate target database for standby dorecover nofilenamecheck;
release channel tp1;
release channel tp2;
}

Above script will return an error
ORA-01830: date format picture ends before converting entire input string
Add a line
Run {
set until time “to_date(‘Dec 11 2007 06:30:09′,’Mon DD YYYY HH24:MI:SS’)”;

Restore Archivelogs which were backed up thru rman

connect rcvcat rmanc/obseq@rcvcat
connect target rmant/obus@live
run {
allocate channel tp1 type “sbt_tape”
parms ‘ENV=(NSR_SERVER=bsbk10,NSR_DATA_VOLUME_POOL=Default)’;
allocate channel tp2 type “sbt_tape”
parms ‘ENV=(NSR_SERVER=bsbk10,NSR_DATA_VOLUME_POOL=Default)’;
set archivelog destination to ‘f:\oracle\oradata\live’;
restore archivelog from logseq 111094 until logseq 111111;
release channel tp1;
release channel tp2;

Run RMAN script to create log as
rman cmdfile=create_standby.rman msglog=create_standby.log

• Select database for catalogue
• Create catalogue owner RMANC, default tablespace TOOLS
• Grant RECOVERY_CATALOG_OWNER role to RMANC
• Run the RMAN executable, being the same version as the catalogue database codeset and connect to the catalogue as the RMANC user
• Issue the CREATE CATALOG command

At RMAN repository Database

SQL>   create user rmanc identified by xxxxxxxx Temporary tablespace TEMP Default tablespace TOOLS quota unlimited on TOOLS;
SQL>   grant RECOVERY_CATALOG_OWNER to RMANC;
%RMAN catalog RMANC/xxxxxxxx@catdb
rman>   CREATE CATALOG

At Target Database which need to be backed up thru RMAN. Before registering a target database, create the target rman user in the target database.

SQL>   connect / as sysdba
SQL>   create user RMANT identified by xxxxxxxx
Default tablespace TOOLS
Temporary tablespace TEMP;
SQL>   grant SYSDBA,CREATE SESSION to RMANT;

Initialisation Parameters : To improve backup performance following parameters must be set….

BACKUP_TAPE_IO_SLAVES = TRUE
For 8i
LARGE_POOL_SIZE = (4 *
<number> *
DB_BLOCK_SIZE *
DB_FILE_DIRECT_IO_COUNT *
<level>
) +
(4 *
<number> *
<size>
)

For 9i
LARGE_POOL_SIZE= <number>*
(16MB +
<size>
)

The size of a tape buffer is determined by the RMAN parameter BLKSIZE.
BLKSIZE is OS dependant, but performance benefits can be made by explicitly setting it higher. 4M seems to offer improved performance. When testing a new backup, try differing values, in conjunction with the above calculation for LARGE_POOL_SIZE, to determine the optimal setting.

Net Requirements
RMAN requires a dedicated server connection to the target database.

Targetdb_rman.domain =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=dbname.db_domain)(SERVER=DEDICATED))
)

The above should be configured on both the target and catalogue servers. The server hosting the target database also requires a Net configuration for the catalogue database, as this is where the RMAN commands are normally issued.

Database registration: To register the target database, issue the following commands.

rman TARGET  rmanc/xxxxxxxx@targetdb  CATALOG rmant/xxxxxxxx@cataloguedb
rman> REGISTER DATABASE;
To check successful registration, issue the following command whilst still in RMAN;
rman> REPORT SCHEMA;
Report of database schema
File K-bytes    Tablespace           RB segs Datafile Name
—- ———- ——————– ——- ——————-
1        524288 SYSTEM               YES     G:\ORACLE\ORADATA\TKNWP\SYSTEM01.DB
F
2        524288 UNDOTBS              YES     G:\ORACLE\ORADATA\TKNWP\UNDOTBS01.D
BF
3         20480 GENESYS_CONFIG_DATA  NO      I:\ORACLE\ORADATA\TKNWP\GENESYS_CON
FIG_DATA_01.DBF
4         20480 GENESYS_LOGS_DATA    NO      G:\ORACLE\ORADATA\TKNWP\GENESYS_LOG
S_DATA_01.DBF
5        131072 TOOLS                NO      H:\ORACLE\ORADATA\TKNWP\TOOLS_01.DB
F
6         25600 USERS                NO      H:\ORACLE\ORADATA\TKNWP\USERS_01.DB
F
7        256000 PERFSTAT             NO      G:\ORACLE\ORADATA\TKNWP\PERFSTAT_01
.DBF

Create RMAN Catalogue steps
• Select database for catalogue.
• Create catalogue owner RMANC, default tablespace TOOLS.
• Grant RECOVERY_CATALOG_OWNER role to RMANC.
• Run the RMAN executable, being the same version as the catalogue database codeset and connect to the catalogue as the RMANC user.
• Issue the CREATE CATALOG command.

At RMAN repository Database

SQL>   create user rmanc identified by xxxxxxxx
   Temporary tablespace TEMP
   Default tablespace TOOLS quota unlimited on TOOLS;
SQL>   grant RECOVERY_CATALOG_OWNER to RMANC;
%RMAN catalog RMANC/xxxxxxxx@catdb
rman>   CREATE CATALOG

At Target Database which need to be backed up thru RMAN
Before registering a target database, create the target rman user in the target database.

SQL>   connect / as sysdba
SQL>   create user RMANT identified by xxxxxxxx
   Default tablespace TOOLS
   Temporary tablespace TEMP;
SQL>   grant SYSDBA,CREATE SESSION to RMANT;

Initialisation Parameters
To improve backup performance following parameters must be set….
• BACKUP_TAPE_IO_SLAVES = TRUE
For 8i
• LARGE_POOL_SIZE = (4 *
<number> *
DB_BLOCK_SIZE *
DB_FILE_DIRECT_IO_COUNT *
<level>
) +
(4 *
<number> *
<size>
)
For 9i
• LARGE_POOL_SIZE= <number>*
(16MB +
<size>
)
The size of a tape buffer is determined by the RMAN parameter BLKSIZE.
BLKSIZE is OS dependant, but performance benefits can be made by explicitly setting it higher. 4M seems to offer improved performance. When testing a new backup, try differing values, in conjunction with the above calculation for LARGE_POOL_SIZE, to determine the optimal setting.
Net Requirements

RMAN requires a dedicated server connection to the target database.
Targetdb_rman.domain =
   (DESCRIPTION=
      (ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521))
      (CONNECT_DATA=(SERVICE_NAME=dbname.db_domain)(SERVER=DEDICATED))
   )

The above should be configured on both the target and catalogue servers. The server hosting the target database also requires a Net configuration for the catalogue database, as this is where the RMAN commands are normally issued.
Database registration

To register the target database, issue the following commands.

> rman TARGET  rmanc/xxxxxxxx@targetdb  CATALOG rmant/xxxxxxxx@cataloguedb
rman> REGISTER DATABASE;
To check successful registration, issue the following command whilst still in RMAN;
rman> REPORT SCHEMA;
RMAN> report schema;
Report of database schema
File K-bytes    Tablespace           RB segs Datafile Name
---- ---------- -------------------- ------- ------------------- 
1        524288 SYSTEM               YES     G:\ORACLE\ORADATA\TKNWP\SYSTEM01.DB
F
2        524288 UNDOTBS              YES     G:\ORACLE\ORADATA\TKNWP\UNDOTBS01.D
BF
3         20480 GENESYS_CONFIG_DATA  NO      I:\ORACLE\ORADATA\TKNWP\GENESYS_CON
FIG_DATA_01.DBF
4         20480 GENESYS_LOGS_DATA    NO      G:\ORACLE\ORADATA\TKNWP\GENESYS_LOG
S_DATA_01.DBF
5        131072 TOOLS                NO      H:\ORACLE\ORADATA\TKNWP\TOOLS_01.DB
F
6         25600 USERS                NO      H:\ORACLE\ORADATA\TKNWP\USERS_01.DB
F
7        256000 PERFSTAT             NO      G:\ORACLE\ORADATA\TKNWP\PERFSTAT_01
.DBF

Post-Recovery/Standby failover Tasks
If a resetlogs command has been issued, or a standby database has been activated, this needs to be reflected in the recovery catalogue. Any operations in RMAN after these operations will fail with the ‘target database incarnation not found in recovery catalog’ error. This is due to the resetlogs command, resetting the SCN’s. As the database Id is still the same, we need to issue the ‘reset database’ command to align the recovery catalogue with the database.
> rman TARGET rmanc/xxxxxxxx@targetdb CATALOG rmant/xxxxxxxx@cataloguedb
rman> RESET DATABASE;
To check;
rman> list incarnation of database;
Monitoring an RMAN Backup
To view the progress of a backup, the v$ views can be queried. Here is an example of a monitoring script:
Execute this whilst the backup is processing:

SELECT sid, serial#, context, sofar, totalwork,
round(sofar/totalwork*100,2) "% Complete"
FROM v$session_longops
WHERE opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND totalwork != 0
AND sofar <totalwork>/
SID SERIAL# CONTEXT SOFAR TOTALWORK % Complete
------ ---------- ---------- ---------- ---------- ---------- 
8 19 1 10377 36617 28.34
SQL>/
SID SERIAL# CONTEXT SOFAR TOTALWORK % Complete
---- ---------- ---------- ---------- ---------- ---------- 
8 19 1 21513 36617 58.75
SQL>/
SID SERIAL# CONTEXT SOFAR TOTALWORK % Complete
----- ---------- ---------- ---------- ---------- ---------- 
8 19 1 29641 36617 80.95
SQL>/
SID SERIAL# CONTEXT SOFAR TOTALWORK % Complete
----- ---------- ---------- ---------- ---------- ---------- 
8 19 1 35849 36617 97.9
SQL>/
no rows selected

The views V$BACKUP_ASYNC_IO and V$BACKUP_SYNC_IO contain detailed information on backup operations. This data is not persistent. If there is data in the V$BACKUP_SYNC_IO view, then you need to investigate why the backups are not running asynchronously. Check BACKUP_TAPE_IO_SLAVES is set.

Datafiles Backed up during past 24 Hours

SELECT dbfiles||' from '||numfiles "Datafiles backed up",
cfiles "Control Files backed up", spfiles "SPFiles backed up"
FROM (select count(*) numfiles from sys.v_$datafile),
(select count(*) dbfiles
from sys.v_$backup_datafile a, sys.v_$datafile b
where a.file# = b.file#
and a.completion_time > sysdate - 1),
(select count(*) cfiles from sys.v_$backup_datafile
where file# = 0 and completion_time > sysdate - 1),
(select count(*) spfiles from sys.v_$backup_spfile
where completion_time > sysdate - 1) 

Archlog Files Backed up during past 24 Hours

SELECT backedup||' from '||archived "Archlog files backed up",
ondisk "Archlog files still on disk"
FROM (select count(*) archived
from sys.v_$archived_log where completion_time > sysdate - 1),
(select count(*) backedup from sys.v_$archived_log
where backup_count > 0
and completion_time > sysdate - 1),
(select count(*) ondisk from sys.v_$archived_log
where archived = 'YES' and deleted = 'NO')

RMAN Backups Still Running:

SELECT to_char(start_time,'DD-MON-YY HH24:MI') "BACKUP STARTED",
sofar, totalwork,
elapsed_seconds/60 "ELAPSE (Min)",
round(sofar/totalwork*100,2) "Complete%"
FROM sys.v_$session_longops
WHERE compnam = 'dbms_backup_restore'
/

BACKUP STARTED,SOFAR,TOTALWORK,ELAPSE (Min),Complete%
27-JUN-07 09:54,755,45683,2.73333333333333,1.65
27-JUN-07 09:52,1283,10947,4.36666666666667,11.72
27-JUN-07 09:46,11275,11275,0.783333333333333,100
27-JUN-07 09:46,58723,58723,5.73333333333333,100
27-JUN-07 09:46,12363,12363,0.333333333333333,100
27-JUN-07 09:44,11115,11115,4.53333333333333,100
27-JUN-07 09:44,12371,12371,0.183333333333333,100
27-JUN-07 07:34,4706,4706,0.166666666666667,100
27-JUN-07 07:34,83729,83729,118.35,100
27-JUN-07 05:21,8433,8433,0.333333333333333,100
27-JUN-07 05:21,83729,83729,132.25,100

2 Oracle databases – A & B. Server A is available and need to be replicated on server B. We will use rman to backup datafiles including control file and archive logs.

RMAN script

connect rcvcat rmanc/obsequ10us@rcvcat
connect target rmant/obstreper0us@tknwp.yell
run {
 allocate channel tp1 type "sbt_tape";
 allocate channel tp2 type "sbt_tape";
 allocate channel tp3 type "sbt_tape";
 backup current controlfile for standby;
 backup incremental level = 0 database plus archivelog;
 change archivelog until time 'sysdate - 3' delete;
 release channel tp1;
 release channel tp2;
 release channel tp3;
}

Spool of rman script
C:\Documents and Settings\oracle>set oracle_sid=TKNWP
C:\Documents and Settings\oracle>cd S:\nsr
C:\Documents and Settings\oracle>s:
S:\nsr>rman
Recovery Manager: Release 9.2.0.8.0 – Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
RMAN> @tknwp_full.rman
RMAN> connect rcvcat rmanc/obsequ10us@rcvcat
connected to recovery catalog database
RMAN> connect target rmant/obstreper0us@tknwp.yell
connected to target database: TKNWP (DBID=38387564)
RMAN> run {
2> allocate channel tp1 type “sbt_tape”;
3> allocate channel tp2 type “sbt_tape”;
4> allocate channel tp3 type “sbt_tape”;
5> send ‘NSR_ENV=(NSR_SERVER=bsbk10,NSR_DATA_VOLUME_POOL=Default)’;
6> backup current controlfile for standby;
7> backup incremental level = 0 database plus archivelog;
8> change archivelog until time ‘sysdate – 3’ delete;
9> release channel tp1;
10> release channel tp2;
11> release channel tp3;
12> }
allocated channel: tp1
channel tp1: sid=17 devtype=SBT_TAPE
channel tp1: NMO v4.1.0.0
allocated channel: tp2
channel tp2: sid=18 devtype=SBT_TAPE
channel tp2: NMO v4.1.0.0
allocated channel: tp3
channel tp3: sid=22 devtype=SBT_TAPE
channel tp3: NMO v4.1.0.0
sent command to channel: tp1
sent command to channel: tp2
sent command to channel: tp3
Starting backup at 28-FEB-2007:11:55:28
channel tp1: starting full datafile backupset
channel tp1: specifying datafile(s) in backupset
including standby controlfile in backupset
channel tp1: starting piece 1 at 28-FEB-2007:11:55:29
channel tp1: finished piece 1 at 28-FEB-2007:11:55:36
piece handle=01ib6i5g_1_1 comment=API Version 2.0,MMS Version 4.1.0.0
channel tp1: backup set complete, elapsed time: 00:00:08
Finished backup at 28-FEB-2007:11:55:36
Starting backup at 28-FEB-2007:11:55:37
current log archived
channel tp1: starting archive log backupset
channel tp1: specifying archive log(s) in backup set
input archive log thread=1 sequence=14 recid=1 stamp=614526721
input archive log thread=1 sequence=15 recid=2 stamp=614526722
input archive log thread=1 sequence=16 recid=3 stamp=614528129
input archive log thread=1 sequence=17 recid=4 stamp=614536625
input archive log thread=1 sequence=18 recid=6 stamp=614536632
input archive log thread=1 sequence=19 recid=8 stamp=614833726
input archive log thread=1 sequence=20 recid=9 stamp=615033366
input archive log thread=1 sequence=21 recid=10 stamp=615042001
input archive log thread=1 sequence=22 recid=11 stamp=615117605
channel tp1: starting piece 1 at 28-FEB-2007:11:55:40
channel tp2: starting archive log backupset
channel tp2: specifying archive log(s) in backup set
input archive log thread=1 sequence=23 recid=12 stamp=615189602
input archive log thread=1 sequence=24 recid=13 stamp=615265205
input archive log thread=1 sequence=25 recid=14 stamp=615337204
input archive log thread=1 sequence=26 recid=15 stamp=615404575
channel tp2: starting piece 1 at 28-FEB-2007:11:55:40
channel tp3: starting archive log backupset
channel tp3: specifying archive log(s) in backup set
input archive log thread=1 sequence=27 recid=16 stamp=615404584
input archive log thread=1 sequence=28 recid=26 stamp=615477606
input archive log thread=1 sequence=29 recid=28 stamp=615553206
input archive log thread=1 sequence=30 recid=29 stamp=615621602
input archive log thread=1 sequence=31 recid=30 stamp=615639537
input archive log thread=1 sequence=32 recid=31 stamp=615639544
input archive log thread=1 sequence=33 recid=32 stamp=615639606
input archive log thread=1 sequence=34 recid=33 stamp=615640180
input archive log thread=1 sequence=35 recid=34 stamp=615640586
channel tp3: starting piece 1 at 28-FEB-2007:11:55:40
channel tp1: finished piece 1 at 28-FEB-2007:11:55:55
piece handle=02ib6i5r_1_1 comment=API Version 2.0,MMS Version 4.1.0.0
channel tp1: backup set complete, elapsed time: 00:00:16
channel tp2: finished piece 1 at 28-FEB-2007:11:55:55
piece handle=03ib6i5r_1_1 comment=API Version 2.0,MMS Version 4.1.0.0
channel tp2: backup set complete, elapsed time: 00:00:16
channel tp3: finished piece 1 at 28-FEB-2007:11:55:55
piece handle=04ib6i5r_1_1 comment=API Version 2.0,MMS Version 4.1.0.0
channel tp3: backup set complete, elapsed time: 00:00:16
channel tp1: starting archive log backupset
channel tp1: specifying archive log(s) in backup set
input archive log thread=1 sequence=36 recid=36 stamp=615640591
input archive log thread=1 sequence=37 recid=44 stamp=615640774
input archive log thread=1 sequence=38 recid=46 stamp=615658800
input archive log thread=1 sequence=39 recid=47 stamp=615658801
input archive log thread=1 sequence=40 recid=48 stamp=615729337
channel tp1: starting piece 1 at 28-FEB-2007:11:55:55
channel tp1: finished piece 1 at 28-FEB-2007:11:56:02
piece handle=05ib6i6b_1_1 comment=API Version 2.0,MMS Version 4.1.0.0
channel tp1: backup set complete, elapsed time: 00:00:07
Finished backup at 28-FEB-2007:11:56:02
Starting backup at 28-FEB-2007:11:56:03
channel tp1: starting incremental level 0 datafile backupset
channel tp1: specifying datafile(s) in backupset
input datafile fno=00007 name=G:\ORACLE\ORADATA\TKNWP\PERFSTAT_01.DBF
input datafile fno=00005 name=H:\ORACLE\ORADATA\TKNWP\TOOLS_01.DBF
input datafile fno=00006 name=H:\ORACLE\ORADATA\TKNWP\USERS_01.DBF
channel tp1: starting piece 1 at 28-FEB-2007:11:56:03
channel tp2: starting incremental level 0 datafile backupset
channel tp2: specifying datafile(s) in backupset
including current controlfile in backupset
input datafile fno=00001 name=G:\ORACLE\ORADATA\TKNWP\SYSTEM01.DBF
input datafile fno=00004 name=G:\ORACLE\ORADATA\TKNWP\GENESYS_LOGS_DATA_01.DBF
channel tp2: starting piece 1 at 28-FEB-2007:11:56:03
channel tp3: starting incremental level 0 datafile backupset
channel tp3: specifying datafile(s) in backupset
input datafile fno=00002 name=G:\ORACLE\ORADATA\TKNWP\UNDOTBS01.DBF
input datafile fno=00003 name=I:\ORACLE\ORADATA\TKNWP\GENESYS_CONFIG_DATA_01.DBF
channel tp3: starting piece 1 at 28-FEB-2007:11:56:04
channel tp1: finished piece 1 at 28-FEB-2007:11:56:29
piece handle=06ib6i6j_1_1 comment=API Version 2.0,MMS Version 4.1.0.0
channel tp1: backup set complete, elapsed time: 00:00:26
channel tp2: finished piece 1 at 28-FEB-2007:11:56:54
piece handle=07ib6i6j_1_1 comment=API Version 2.0,MMS Version 4.1.0.0
channel tp2: backup set complete, elapsed time: 00:00:51
channel tp3: finished piece 1 at 28-FEB-2007:11:56:54
piece handle=08ib6i6j_1_1 comment=API Version 2.0,MMS Version 4.1.0.0
channel tp3: backup set complete, elapsed time: 00:00:51
Finished backup at 28-FEB-2007:11:56:54
Starting backup at 28-FEB-2007:11:56:55
current log archived
channel tp1: starting archive log backupset
channel tp1: specifying archive log(s) in backup set
input archive log thread=1 sequence=41 recid=49 stamp=615729415
channel tp1: starting piece 1 at 28-FEB-2007:11:56:57
channel tp1: finished piece 1 at 28-FEB-2007:11:57:04
piece handle=09ib6i88_1_1 comment=API Version 2.0,MMS Version 4.1.0.0
channel tp1: backup set complete, elapsed time: 00:00:08
Finished backup at 28-FEB-2007:11:57:04
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00014.ARC recid=1 stamp=61452
6721
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00015.ARC recid=2 stamp=61452
6722
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00016.ARC recid=3 stamp=61452
8129
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00017.ARC recid=4 stamp=61453
6625
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00018.ARC recid=6 stamp=61453
6632
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00019.ARC recid=8 stamp=61483
3726
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00020.ARC recid=9 stamp=61503
3366
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00021.ARC recid=10 stamp=6150
42001
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00022.ARC recid=11 stamp=6151
17605
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00023.ARC recid=12 stamp=6151
89602
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00024.ARC recid=13 stamp=6152
65205
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00025.ARC recid=14 stamp=6153
37204
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00026.ARC recid=15 stamp=6154
04575
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00027.ARC recid=16 stamp=6154
04584
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00028.ARC recid=26 stamp=6154
77606
Deleted 15 objects
released channel: tp1
released channel: tp2
released channel: tp3
RMAN>
RMAN>
RMAN>
RMAN>
RMAN> **end-of-file**

Read more…