Difference between revisions of "RMAN"

From dbawiki
Jump to: navigation, search
(→‎No catalog available)
(→‎Catalog available)
Line 919: Line 919:
 
RESTORE CONTROLFILE;
 
RESTORE CONTROLFILE;
 
</pre>
 
</pre>
−
Restores controlfile(s) to original locations
+
Restores controlfile(s) to original location(s)
 
<pre>
 
<pre>
 
RESTORE CONTROLFILE ... TO 'filename';
 
RESTORE CONTROLFILE ... TO 'filename';
 
</pre>
 
</pre>
−
Restores the control file but only to a location other than CONTROL_FILES parameter
+
Restores the controlfile but only to a location other than CONTROL_FILES parameter
 
<pre>
 
<pre>
 
RESTORE CONTROLFILE ... FROM 'piece handle'; or
 
RESTORE CONTROLFILE ... FROM 'piece handle'; or

Revision as of 00:08, 23 August 2015

list - tells you what has already been done
report - tells you what needs to be done

Contents

Change date and time format to get better reporting

Set this at unix prompt before starting RMAN 

export NLS_DATE_FORMAT="dd-mon-yyyy hh24:mi:ss"

Crosscheck the archivelogs

change archivelog all crosscheck;

What is the size and duration of the RMAN backup?

set pages 100 lines 200
col mb for 999,999,999
col completed for a11
col sortcol noprint

select vi.instance_name sid
,      to_char(completion_time, 'DD-MON-YYYY') completed
,      completion_time sortcol
,      type
,      round(sum(bytes)/1048576)      mb
,      round(sum(elapsed_seconds)/60) min
from
(
select
case
   when s.backup_type          = 'L'                             then 'ARCHIVELOG'
   when s.controlfile_included = 'YES'                           then 'CONTROLFILE'
   when s.backup_type          = 'D' and s.incremental_level = 0 then 'LEVEL 0'
   when s.backup_type          = 'I' and s.incremental_level = 1 then 'LEVEL 1'
   when s.backup_type          = 'D' and s.incremental_level is null then 'FULL'
   else s.backup_type
end type
,      trunc(s.completion_time) completion_time
,      p.bytes
,      s.elapsed_seconds
from   v$backup_piece p
,      v$backup_set   s
where  p.status = 'A'
and    p.recid  = s.recid
union all
select 'datafilecopy' type
,      trunc(completion_time) completion_time
,      output_bytes
,      0 elapsed_seconds
from   v$backup_copy_details
)
,      v$instance vi
group  by vi.instance_name, to_char(completion_time, 'DD-MON-YYYY'), completion_time, type
order  by 1,3,4
/

Delete archivelogs that have been backed up

delete noprompt archivelog all backed up 2 times to sbt_tape;

delete noprompt archivelog until time 'sysdate-2' backed up 1 times to device type 'sbt_tape';

delete noprompt archivelog until time 'sysdate-(1*6/24)' backed up 1 times to device type 'sbt_tape';

delete expired archivelog all;

delete noprompt archivelog until logseq 42000 backed up 1 times to device type 'sbt_tape';

Backup archivelogs that have not yet been backed up then delete any older than 72 hours

run {
    allocate channel type 'sbt_tape' format 'LOG_d%d_t%t_s%s_u%u' parms 'env=(tdpo_optfile=/oracle/JDBEOP1/admin/tdpo.opt)';
    backup archivelog all not backed up 1 times tag=bu20140808t170528p5685;
    delete noprompt archivelog until time 'sysdate-(1 * 72 / 24)'  backed up 1 times to device type 'sbt_tape';
}

Backup archivelogs between a range of sequence numbers

run {
    allocate channel t1 device type 'sbt_tape' format 'ARC_d%d_t%t_s%s_u%u' parms 'env=(tdpo_optfile=c:\home\ibmtools\scripts\rman\tdpo_golfp1.opt)';
    backup archivelog from logseq 224801  until logseq 224890;
}

Backup the archivelogs to tape then delete the ones on disk

In a space emergency, backup all the archivelogs to the configured tape and immediately delete them from disk 

backup device type sbt_tape archivelog all delete all input;

or ensure the correct device... 

run {
    allocate channel c1 type sbt_tape parms 'ENV=(TDPO_OPTFILE=/oracle/${ORACLE_SID}/admin/tdpo.opt)';
    backup device type sbt_tape archivelog all delete all input;
}

Restore archivelogs

    restore archivelog from logseq=38400 until logseq=38499;
    restore archivelog logseq=25444;

FRA usage

If the Flash (or Fast) Recovery Area is used for archive redo logs as well as flashback logs, we need to keep an eye on it's size 

col fsname   heading "Filesystem"     for a50
col space_limit  heading "Allocated GB"   for 999,999,990
col space_used heading "Used GB"        for a20
col percused heading "% Used"         for 90.0
col space_reclaimable  heading "Reclaimable GB" for a20
col percrec  heading "% Reclaimable"  for 90.0
set linesize 1000
with maxsizes as (
select name
,      floor(space_limit/1024/1024/1024) space_limit
from   v$recovery_file_dest
)
,    useds as (
select name
,      ceil(space_used/1024/1024/1024) space_used
from   v$recovery_file_dest
)
,    reclaimables as (
select name
,      (space_reclaimable/1024/1024/1024) space_reclaimable
from   v$recovery_file_dest
)
select m.name  fsname
,      trunc(m.space_limit)  space_limit
,      trunc(u.space_used)  ||' (' ||
       round((u.space_used/m.space_limit)*100,2) ||'%)' space_used
,      trunc(r.space_reclaimable)  ||' (' ||
       round((r.space_reclaimable/m.space_limit)*100,2)  ||'%)' space_reclaimable
from   maxsizes     m
,      useds        u
,      reclaimables r
where  m.name = u.name
and    u.name = r.name
order  by m.name
/

and in detail... 

set linesize 1000
select *
from   v$recovery_area_usage
where  percent_space_used > 0;

size taken up by the flashback logs 

select estimated_flashback_size
from   v$flashback_database_log;

Validate backups or restores

Validate that the database components are free of corruption 

backup validate <something>

or validate that the database components on tape are free of corruption 

restore <something> validate;

eg: 

backup validate datafile 1;
backup validate tablespace users;
backup validate archivelog all;
backup validate current controlfile;
backup validate database;

or 

restore tablespace users validate;
restore archivelog all validate;
restore spfile validate;
restore database validate;

Substitute "validate" by "preview" to see the list of backup pieces that would be used in the restore process.

Recover datafile blocks using RMAN

connect target /

backup validate datafile 00091;

run {
    set maxcorrupt for datafile 91 to 32;
    backup validate datafile 00091;
}

blockrecover datafile 91 block 889664;

Quick check that the tdpo configuration is correct

Channel test 

run {
    allocate channel c1 type sbt_tape parms 'ENV=(TDPO_OPTFILE=/oracle/${ORACLE_SID}/admin/tdpo.opt)';
}

Quick backup to tape via tdpo

rman nocatalog target /
run {
    allocate channel c1 type sbt_tape parms 'ENV=(TDPO_OPTFILE=/oracle/${ORACLE_SID}/admin/tdpo.opt)';
    backup database;
}

Create a physical standby from an RMAN backup

Backup primary database to disk

#!/bin/ksh
#
# Inspired by the book RMAN Recipes for Oracle database 11g

export ORACLE_SID=JDBEOP1


echo `date`
HOSTNAME=`hostname`
TODAY=`date +%Y%m%d_%H%M`

ORAENV_ASK=NO
. oraenv

export nls_date_format="dd-mon-yyyy hh24:mi:ss"

sqlplus / as sysdba<<EOSQL
set numwidth 20 lines 2000
col status for a12
select sysdate, current_scn from v\$database;
select group#,thread#,sequence#,archived,status,first_change#,first_time from v\$log;
EOSQL

rman target / nocatalog msglog rman_database_${ORACLE_SID}_${TODAY}.log<<EORMAN
run {
    allocate channel d1 type disk;
    allocate channel d2 type disk;
    allocate channel d3 type disk;
    allocate channel d4 type disk;
    allocate channel d5 type disk;
    allocate channel d6 type disk;
    backup format '/JDBEOP1-CLONE/rman_db_t%t_s%s_p%p'  database;
    sql 'alter system archive log current';
    backup format '/JDBEOP1-CLONE/rman_al_t%t_s%s_p%p'  archivelog all;
    backup format '/JDBEOP1-CLONE/spfile_t%t_s%s_p%p'   spfile;
    backup format '/JDBEOP1-CLONE/rman_ctl_t%t_s%s_p%p' current controlfile for standby;
    release channel d1;
    release channel d2;
    release channel d3;
    release channel d4;
    release channel d5;
    release channel d6;
}
EORMAN

echo `date`
ls -altr /JDBEOP1-CLONE

Copy RMAN backup files

Copy over the backup files from source to destination server

  • using scp
scp -p /CLONEDISK/* bemauerp12:/CLONEDISK/
  • using rsync
rsync -uav --progress /CLONEDISK/* bemauerp12:/CLONEDISK/
  • using zfs disk (Solaris)

On source server unmount the disk and comment the appropriate line in /etc/vfstab 

umount /CLONEDISK
vxdg deport clonedg
vi /etc/vfstab

# /dev/vx/dsk/clonedg/lv_clonedg_jdbeop1clone     /dev/vx/rdsk/clonedg/lv_clonedg_jdbeop1clone    /CLONEDISK  vxfs    3       yes     -

On destination server uncomment the appropriate line in /etc/vfstab and mount the disk 

vi /etc/vfstab

/dev/vx/dsk/clonedg/lv_clonedg_jdbeop1clone     /dev/vx/rdsk/clonedg/lv_clonedg_jdbeop1clone    /CLONEDISK  vxfs    3       yes     -

vxdg import clonedg
mount /CLONEDISK

Edit the tnsnames.ora file on the destination server

Standby database needs to be able to connect to the primary via tns 

vi $TNS_ADMIN/tnsnames.ora

PRIMARY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1555))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = JDBEOP1)
    )
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1555))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = JDBEOP1_DR)
    )
  )

Restore the database on the destination server

#!/usr/bin/ksh

HOSTNAME=`hostname`
TODAY=`date +%Y%m%d_%H%M`; export TODAY

# ===================
# set the environment
# ===================
export ORACLE_SID=JDBEOP1
ORAENV_ASK=NO
. oraenv

echo $ORACLE_SID
echo $ORACLE_HOME

# ==================================
# kill any previous (failed) attempt
# ==================================
sqlplus / as sysdba <<EOSQL
shutdown abort
EOSQL

# ======================
# start a dummy instance
# ======================
sqlplus / as sysdba <<EOSQL
startup nomount pfile='/oracle/JDBEOP1/admin/change/init_clone.ora'
EOSQL

# =====================
# start the duplication
# =====================
echo `date`
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
rman nocatalog cmdfile=duplicate4standby.cmd msglog=duplicate_${HOSTNAME}_${ORACLE_SID}_${TODAY}.log
echo `date`

init_clone.ora file

Used for the initial start of the standby database in the above script 

DB_NAME=JDBEOP1
DB_UNIQUE_NAME=JDBEOP1_DR

duplicate4standby.cmd file

RMAN command file used in the above script 

connect target sys/&syspasswordonprimary@JDBEOP1
connect auxiliary /
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate auxiliary channel a1 device type disk;
allocate auxiliary channel a2 device type disk;
set newname for tempfile 1 to '/oracle/JDBEOP1/oradata1/temp01.dbf';
set newname for tempfile 2 to '/oracle/JDBEOP1/oradata2/temp03.dbf';
set newname for datafile 518 to '/oracle/JDBEOP1/arcdata/ARCENG_05.dbf';
set newname for datafile 555 to '/oracle/JDBEOP1/arcdata/PRODARCT_09.dbf';
set newname for datafile 550 to '/oracle/JDBEOP1/arcdata/PRODARCI_08.dbf';
duplicate target database for standby 
nofilenamecheck
dorecover
spfile set db_unique_name          = 'JDBEOP1_DR'
       set sga_max_size            = '124748364800'
       set sga_target              = '124748364800'
       set db_cache_size           = '100000000000'
       set fal_server              = 'JDBEOP1'
       set fal_client              = 'JDBEOP1_DR' 
       set db_block_checksum       = 'FULL'
       set db_lost_write_protect   = 'TYPICAL'
       set db_recovery_file_dest   = '/oracle/JDBEOP1/recovery_area'
       set log_archive_dest_1      = 'LOCATION="USE_DB_RECOVERY_FILE_DEST"'
       set standby_archive_dest    = 'LOCATION="USE_DB_RECOVERY_FILE_DEST"'
;
}

Backup the control file

backup current controlfile;

Take a binary copy of the controlfile that can be used without a catalog to recover an instance somewhere else

backup as copy current controlfile format '/tmp/MOCMMSP2.ctl';

Take a text copy of the controlfile that can be used without a catalog to rebuild an instance (worst case)

backup as copy current controlfile format '/tmp/MOCMMSP2.ctl';

Check the status of currently running backups

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
/

Check the status of the database backups for the past 7 days

Run this one from sys on the database server 

set pages 100
set lines 200
col operation   for a12
col backup_type for a27
col start_time  for a17
col end_time    for a17
col status      for a12
col duration    for 90.99
col days_ago    for 90

select vrbsd.operation
,      vrbsd.command_id                              backup_type
,      to_char(vrbsd.start_time,'DD-MON-YY HH24:MI') start_time
,      to_char(vrbsd.end_time,'DD-MON-YY HH24:MI')   end_time
,      vrbsd.status                                  status
,      (vrbd.elapsed_seconds/3600)                   duration
,      trunc(sysdate)-trunc(vrbsd.start_time)        days_ago
from   v$rman_backup_job_details     vrbd
,      v$rman_backup_subjob_details  vrbsd
where  1=1
and    vrbd.session_key = vrbsd.session_key
and    vrbd.input_type='DB INCR'
and    vrbsd.start_time > sysdate -8
order  by vrbsd.start_time desc

Check the status of the database backups from the RMAN catalog

Run this one from the RMAN catalog server 

select s.db_name
,      s.status
,      max(s.start_time) start_time
,      max(round(s.mbytes_processed/1024))  "processed_data(GB)"
,      max(round((end_time-start_time)*60*24)) "duration(min)"
,      trunc(sysdate)-trunc(s.start_time)   days_since_last_backup
from   rc_rman_status s 
where  1=1
and    lower(s.operation)   = 'backup'
and    lower(s.object_type) ='db full'
and    s.start_time =
       (
       select max(md.start_time)
       from   rc_rman_status md 
       where  1=1
       and    lower(md.operation)   = 'backup'
       and    lower(md.object_type) ='db full'
       and    md.db_name = s.db_name
       )
group  by s.db_name
,      s.object_type
,      s.operation
,      s.status
,      trunc(sysdate)-trunc(s.start_time)
order  by trunc(sysdate)-trunc(s.start_time) desc


Which tapes are the backups on?

Not really necessary as RMAN can work it out but if you need to know... 

select media
, to_char(completion_time,'DD-MON-YYYY HH24:MI:SS') backed_up
from   rc_backup_piece
where  1=1
and    db_id           = 4053457814
and    completion_Time > sysdate - 2
order  by 2

Block change tracking

Check / Set it 

select filename from v$block_change_tracking;
show parameter db_create_file_dest
alter system set db_create_file_dest='/oracle/SID/oradata1' scope=both;

Enable it 

alter database enable block change tracking;
alter database enable block change tracking using file '/oracle/SID/oradata1/change_track.dbf';

Disable it 

alter database disable block change tracking;

Rename / Move it 

alter database disable block change tracking;
alter database enable block change tracking using file '<something different>';

To remove all rows from v$rman_status

If the status of an RMAN backup is stuck in RUNNING, the next backup will not start up. Reset the view with this... 

exec sys.dbms_backup_restore.resetcfilesection(28);

To reset all RMAN configuration back to defaults

execute dbms_backup_restore.resetconfig;

Check for hanging RMAN sessions by checking wait

COLUMN EVENT FORMAT a10
COLUMN SECONDS_IN_WAIT FORMAT 999
COLUMN STATE FORMAT a20
COLUMN CLIENT_INFO FORMAT a30

SELECT p.SPID
,      EVENT
,      SECONDS_IN_WAIT SEC_WAIT
,      sw.STATE
,      CLIENT_INFO
FROM   V$SESSION_WAIT sw
,      V$SESSION s
,      V$PROCESS p
WHERE  1=1
and    (sw.EVENT LIKE 'sbt%' or sw.EVENT LIKE '%MML%')
AND    s.SID    = sw.SID
AND    s.PADDR  = p.ADDR
;

Useful RMAN commands

Create RMAN catalog schema

create tablespace rmanAGRDWHP1 datafile '/oracle/RMANV11/oradata1/rmanTDPOTEST.dbf' size 5M autoextend on;
alter database datafile '/oracle/RMANV11/oradata1/rmanTDPOTEST.dbf' autoextend on next 5M maxsize unlimited;

create user rmanTDPOTEST identified by threeguesses
       temporary tablespace temp
       default tablespace rmanTDPOTEST
       quota unlimited on rmanTDPOTEST;

grant connect, resource, recovery_catalog_owner to rmanTDPOTEST;

Connect RMAN to the database controlfile (assuming db environment is set)

rman target /

Connect RMAN to the recovery catalog (assuming db environment is set)

rman target / catalog catowner/catpass@catdb

Problem with this method is that anyone doing a 'ps -ef' will see the catalog password! Do it this way and the password will not be visible... 

rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Jun 8 10:49:57 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: EUDSTGP2 (DBID=1013086362)

RMAN> connect catalog catowner/catpass@catdb;

connected to recovery catalog database

Show what backups are available

list backup of database;
list backup of spfile;
list backup of controlfile;
LIST ARCHIVELOG ALL;
LIST BACKUP OF ARCHIVELOG ALL;
LIST BACKUP OF DATAFILE 1;
LIST BACKUP SUMMARY;
LIST INCARNATION;
LIST BACKUP BY FILE;
LIST BACKUP OF DATAFILE 11 SUMMARY;
LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1437;
LIST CONTROLFILECOPY "/tmp/cntrlfile.copy";
LIST BACKUPSET OF DATAFILE 1;
LIST FAILURE;
LIST FAILURE 641231 detail;

Show all the configured options

show all;

What needs backing up to satisfy retention policy (times backed up/recovery window etc)?

report need backup;

What can be deleted?

report obsolete;

Delete what is not needed

delete noprompt expired archivelog all;

delete noprompt obsolete;

Check whether the backup pieces registered in the controlfile still exist

CROSSCHECK BACKUP;
CROSSCHECK COPY;
CROSSCHECK backup of database;
CROSSCHECK backup of controlfile;
CROSSCHECK archivelog all;

Restore database from TSM (tape) to different server using RMAN

Scenario
Customer wants a complete database restored to a different server so that they can retrieve data that is no longer available on the source database.
It is not possible to restore from an export as the required dump files are no longer available.

Source: PROD1 on prod004
Destination: PRODX on test002

Method

  • Request Unix team to attach a temporary filesystem big enough to hold the complete database (and an extra bit for contingency) to the destination server
  • Request Unix team to restore (or copy from source) the Oracle software to the mounted filesystem
  • Recreate the /oracle/SID/admin directory (contains necessary tdpo files)
  • Update dsm.sys with SID stanza from source server
  • Test the tdpo access from destination server
  • Add destination SID to oratab
  • Create a minimal init.ora file
  • Check that the desired data can be restored (validate)
  • Create an RMAN command file to perform the work
  • Create a shell to run the process



Request Unix team to attach a temporary filesystem big enough to hold the complete database (and an extra bit for contingency) to the destination server
Either attach the storage directly to the destination server or nfs mount it (with hard mount options) from another server

Request Unix team to restore (or copy from source) the Oracle software to the mounted filesystem
The whole of the Oracle source code can be copied from the source server to the destination server. The contents of the dbs directory can be removed.

Recreate the /oracle/SID/admin directory (contains necessary tdpo files)
For this customer, we keep all the TSM related files in this directory. These include the tdpo.opt, dsm.opt and TSM.PWD files.

test002:/oracle/PROD1/admin# ll -tr
total 62
-rwxrwx---   1 oracle   dba          257 Jul 13 12:58 tdpo.opt
-rwxrwx---   1 oracle   dba          814 Jul 13 12:58 dsm.opt
-rwxrwx---   1 oracle   dba          268 Jul 13 12:58 inclexcl.lst
drwxrwx---   2 oracle   dba           96 Jul 13 13:18 tdpoerror_PROD1
-rwxrwx---   1 oracle   dba          184 Jul 13 13:18 TSM.PWD
-rwxrwx---   1 oracle   dba            0 Nov 12 11:59 dsmerror.log
drwxrwx---   2 oracle   dba         1024 Nov 12 16:50 change


Update dsm.sys with SID stanza from source server
The stanza from the source server relating to this database should be copied into the dsm.sys on the destination server so that tdpo can access the TSM server. 

SErvername                 TSM_PROD1_TDPO
   TCPPort                 1518
   TCPServeraddress        tsm-bxt
   TCPBuffsize             31
   TCPWindowsize           1024
   LARGECOMMBUFFERS        YES
   TXNBytelimit            10000
   PASSWORDACCESS          GENERATE
   PASSWORDDIR             /oracle/PROD1/admin
   MAXCMDRETRIES           4
   INCLEXCL                /oracle/PROD1/admin/inclexcl.lst
   QUERYSCHEDPERIOD        3
   RETRYPERIOD             30
   SCHEDLOGNAME            /oracle/PROD1/admin/dsmsched.log
   ERRORLOGNAME            /oracle/PROD1/admin/dsmerror.log
   SCHEDLOGRETENTION       15 D
   SCHEDMODE               POLLING
   NODENAME                SO_U_PROD1_ORA_X_BAX
   DIRMC                   D01NOS_14


Test the tdpo access from destination server
Use tdpoconf to test access to the TSM server and to check the related files are all usable. 

tdpoconf showenv -tdpo_opt=/oracle/PROD1/admin/tdpo.opt


Add destination SID to oratab
Add a line with the new SID 

echo "PRODX:/oracle/PROD1/product/11204:Y" >> /etc/oratab


Create a minimal init.ora file
This is just a dummy file so that we can start an idle instance. 

echo "DB_NAME=dummy" > /oracle/PROD1/admin/change/init_dummy.ora


Check that the desired data can be restored (validate)
Use an RMAN preview command to ensure that files can be restored as far back as the required date.
If you have no access to the catalog, you will need to use "set dbid=<DBID>;" 

rman target / catalog catuser/catpass@catdb

run {
set until time "to_date('18-OCT-14 10:10:00','DD-MON-YY HH24:MI:SS')";
restore controlfile preview;
}


Create an RMAN command file to perform the work 

connect catalog catuser/catpass@catdb;
connect auxiliary /

run {
allocate auxiliary channel d1 device type disk;
allocate auxiliary channel a1 device type sbt_tape parms 'ENV=(TDPO_OPTFILE=/oracle/PROD1/admin/tdpo.opt)';
allocate auxiliary channel a2 device type sbt_tape parms 'ENV=(TDPO_OPTFILE=/oracle/PROD1/admin/tdpo.opt)';
allocate auxiliary channel a3 device type sbt_tape parms 'ENV=(TDPO_OPTFILE=/oracle/PROD1/admin/tdpo.opt)';
allocate auxiliary channel a4 device type sbt_tape parms 'ENV=(TDPO_OPTFILE=/oracle/PROD1/admin/tdpo.opt)';
allocate auxiliary channel a5 device type sbt_tape parms 'ENV=(TDPO_OPTFILE=/oracle/PROD1/admin/tdpo.opt)';

duplicate database PROD1 dbid 2992702833 to PRODX
    until time "to_date('18-OCT-14 10:10:00','DD-MON-YY HH24:MI:SS')"
    spfile
    set db_unique_name = 'PRODX'
    nofilenamecheck
;

}


Create a shell to run the process 

#!/usr/bin/ksh

HOSTNAME=`hostname`
export TODAY=`date +%Y%m%d_%H%M`

export ORACLE_SID=PRODX
ORAENV_ASK=NO
. oraenv

echo $ORACLE_SID
echo $ORACLE_HOME

sqlplus / as sysdba <<EOSQL
shutdown abort
EOSQL

sqlplus / as sysdba <<EOSQL
startup nomount pfile='/oracle/PROD1/admin/change/init_dummy.ora'
EOSQL

echo `date`
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
rman cmdfile=/oracle/PROD1/admin/change/dupdb.cmd msglog=/oracle/PROD1/admin/change/dupdb_${HOSTNAME}_${ORACLE_SID}_${TODAY}.log
echo `date`


Bad Hair Day

When media failure strikes, look in the alertlog for more info if you can still get to it.

Restore and recover tablespace

If some/all datafiles in one tablespace are damaged, you can recover the tablespace. 

rman target /
startup mount;
restore tablespace users;
recover tablespace users;
alter database open;

or while database is open... 

rman target /
sql 'alter tablespace users offline immediate';
restore tablespace users;
recover tablespace users;
sql 'alter tablespace data_ts online';

or until a particular moment in time (TSPITR) 

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area   10692665344 bytes

Fixed Size                     2168728 bytes
Variable Size               7314868328 bytes
Database Buffers            3355443200 bytes
Redo Buffers                  20185088 bytes

RMAN> run {
2> set until time "to_date('27-NOV-14 09:25:00','DD-MON-YY HH24:MI:SS')";
3> restore tablespace "nh_PFT_0v_quartz_ts";
4> recover tablespace "nh_PFT_0v_quartz_ts";
5> }

...or even better if flashbask is enabled (this takes seconds instead of hours) 

SQL> flashback database to timestamp to_timestamp ('27-NOV-14 09:25:00','DD-MON-YY HH24:MI:SS');

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL>

Restore and recover datafile

To get a list of datafiles and their numbers, use 'report schema' or 'select file#,name from v$datafile;' (this queries the control file) 

rman target /
startup mount;
restore datafile 1;
recover datafile 1;
alter database open;

or while database is open... 

rman target /
sql 'alter datafile 3 offline';
restore datafile 3;
recover datafile 3;
sql 'alter tdatafile 3 online';

Restore datafile to a different location and recover

rman target /
startup mount;
run {
set newname for datafile 2 to '/oracle/SID/users.dbf';
restore tablespace users;
switch datafile all;
recover tablespace users;
alter database open;
}

Restore database

If all else fails, restore the database to a time before the bad thing happened, then recover it by rolling forward with the archivelogs.
Can do it by time, scn, logseq.
Can do it in one step or two. 

export ORACLE_SID=<SID>
. oraenv
rman target / catalog catusr/catpwd@catdb
startup mount;
run {
set until time "to_date('20-MAR-14 10:00:00','dd-mon-yy hh24:mi:ss')";
restore database;
}

Get scn for this timestamp 

select timestamp_to_scn('20-MAR-14 10.00.00') from dual;

rman target / catalog catusr/catpwd@catdb
run {
set until scn=10328571660050;
recover database;
}

or 

export ORACLE_SID=<SID>
. oraenv
rman target / catalog catusr/catpwd@catdb
startup mount;
run {
set until logseq 101145;
restore database;
recover database;
}

Lost the control files as well?

Assuming database is started in NOMOUNT mode...

Catalog available
RESTORE CONTROLFILE;

Restores controlfile(s) to original location(s) 

RESTORE CONTROLFILE ... TO 'filename';

Restores the controlfile but only to a location other than CONTROL_FILES parameter 

RESTORE CONTROLFILE ... FROM 'piece handle'; or
RESTORE CONTROLFILE ... FROM 'piece handle' to 'filename'; or
RESTORE CONTROLFILE ... FROM TAG 'tag'; or
RESTORE CONTROLFILE ... FROM TAG 'tag' to 'filename';

Restores controlfile(s) from specified file to original locations unless to 'filename' is specified (then only to this location).

No catalog available

Note: If controlfile was backed up with autobackup format set to non-default location, format must be specified for restore with: SET CONTROLFILE AUTOBACKUP FORMAT '<format>'; 

RESTORE CONTROLFILE;

Not possible. RMAN cannot know from where to restore it 

RESTORE CONTROLFILE FROM AUTOBACKUP;

Restores controlfile(s) to original locations 

RESTORE CONTROLFILE FROM AUTOBACKUP... TO 'filename';

Restores the controlfile to specified location 

SET DBID=<dbid>;
RESTORE CONTROLFILE ... FROM 'piece handle'; or
RESTORE CONTROLFILE ... FROM 'piece handle' to 'filename'; or

Note: SET DBID must be used here. Restores controlfile(s) from specified file to original locations unless to 'filename' is specified (then only to this location). 

RESTORE CONTROLFILE ... FROM TAG 'tag'; or
RESTORE CONTROLFILE ... FROM TAG 'tag' to 'filename';

Not possible. Catalog needs to be available to be able to restore from a tag

Duplicating an 11gR2 Oracle database with no connection to the target

One of the new Backup and Recovery features of Oracle 11g Release 2 (11gR2) is the ability to duplicate a database without connecting to the target database.
In RMAN terminology, the “target” database is the one you wish to duplicate.
In this case, the only requirement is that you provide a disk backup location where RMAN can find all the backups, data file copies, archived logs, and control file copies for database duplication.
This database duplication enhancement is helpful when it is not possible to connect to the target database and the recovery catalog.

This came in handy for me recently on a customer project. They wanted me to test a disaster recovery scenario in which the production database server was completely gone and we needed to restore it from tape onto another server.
This is a fairly typical DR situation, but in this case it was made more difficult because the directory structure on the test server didn’t match that of the production server.
Specifically, on the production server, everything was located on the E: drive while on the Test server, the only drive that exists is C:.
This means that all file locations need to be changed as part of the duplicate procedure.
This can all be done in one single RMAN command.

Note: One thing that makes this process easier is to enable controlfile autobackups on the production database.
This is a good practice in general and should be configured for all databases.

rman target / nocatalog
configure controlfile autobackup on;

Here are the steps that I followed:

Step1) Copy the most recent backup files and archivelog files to the test server.
In this case, we restored from tape to the location c:\prod backup files. The backup files and archivelogs come from the following locations:

E:\app\flash_recovery_area\prod\ARCHIVELOG\2013_03_21
E:\app\flash_recovery_area\prod\BACKUPSET\2013_03_21
E:\app\flash_recovery_area\prod\AUTOBACKUP\2013_03_21

Step 2) Create a file called INITPROD2.ORA in %ORACLE_HOME%\database containing one line: db_name=PROD2

Step 3) At a command prompt:

set oracle_sid=prod2
oradim –new –sid prod2
sqlplus / as sysdba
startup nomount
exit
rman auxiliary / msglog=c:\restore.log

Step 4) At the RMAN prompt:

duplicate database to prod2 spfile
set control_files=’C:\app\oracle\oradata\prod2\control01.ctl’
set db_file_name_convert=’E:\app\oradata\prod’,'C:\app\oracle\oradata\prod2′,’E:\app\prod_tbs’,'C:\app\oracle\oradata\prod2′
set LOG_FILE_NAME_CONVERT=’E:\app\oradata\prod’,'C:\app\oracle\oradata\prod2′,’C:\oracle\oradata\prod’,'C:\app\oracle\oradata\prod2′
set db_recovery_file_dest=’c:\app\administrator\flash_recovery_area’
set diagnostic_dest=’c:\app\administrator\diag’
backup location “c:\prod backup files\”;

That’s all it takes! As you can see, I changed all file location parameters and converted datafile and logfile names with the _CONVERT parameters.
You can set any parameters for the new database in this way. For example you may want to disable all database jobs in the duplicate database by setting job_queue_processes=0.
Or, change the size of the SGA for a smaller server. RMAN will create the PROD2 database, perform as much recovery as possible given the available archive logs and open the database with the resetlogs option.
This is a really nice feature of RMAN.

Update RMAN configuration parameters from Sql*Plus

If you backup the controlfile with:
alter database backup controlfile to trace as '/oracle/${OSID}/admin/${NSID}_controlfile.sql';
This example found in backup controlfile 

-- Configure snapshot controlfile filename
EXECUTE SYS.DBMS_BACKUP_RESTORE.CFILESETSNAPSHOTNAME('/oracle/product/11.2.0.3/dbs/snapshot_controlfile_WM820T.ctl');
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 31 DAYS');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEFAULT DEVICE TYPE TO','''SBT_TAPE''');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE ''SBT_TAPE'' FORMAT   ''LOG_%d_t%T_s%s_u%U'' PARMS  ''ENV=(TDPO_OPTFILE=/oracle/WM820T/admin/tdpo.opt)''');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','''SBT_TAPE'' PARALLELISM 1 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 5
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','''SBT_TAPE'' TO ''LOG_%d_controlfile_%F.rman''');
-- Configure RMAN configuration record 6
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 7
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('SNAPSHOT CONTROLFILE NAME','TO ''snapshot_controlfile_WM820T.ctl''');

Script to reorganise datafiles into neat new filesystems

This script can generate 'set newname' statements for use when duplicating a database for standby.
If the files are scattered all over the place, this will gather them together and spread the datafiles evenly over the available space. 

-- =============================================================================
-- Filename    : reorganise_datafiles.sql
-- Description : Shares the datafiles on one host over several
--               filesystems on another host
--
-- Notes       : Fill the l_datafile_tab and optionally the l_archive_tab
--               with the names of the new filesystems
--
-- Modification History
-- ====================
-- Who               When      What
-- ================= ========= =================================================
-- Stuart Barkley    22-OCT-12 Created
-- Stuart Barkley    29-OCT-12 Added tempfiles to cursor c_row
-- Stuart Barkley    01-NOV-12 Added code to treat archive log files separately
-- =============================================================================

spool reorganise_datafiles
set serveroutput on format wrapped size unlimited
set termo on
set trims on
set lines 200
set pages 1000
set feedb off
set verif off
create or replace type l_filesystem_obj as object
    (
        name        varchar2(100)
    ,   bigness     number
    )
/

create or replace type l_filesystem_tabtype is table of l_filesystem_obj
/

declare

    -- =========================================================
    -- add/delete/replace the lines in this table with the names
    -- of the new mount points for normal datafiles
    -- =========================================================
    l_datafile_tab            l_filesystem_tabtype := l_filesystem_tabtype(
                                                                            l_filesystem_obj( '/oracle/JDBEOP1/oradata1/' ,0)
                                                                          , l_filesystem_obj( '/oracle/JDBEOP1/oradata2/' ,0)
                                                                          , l_filesystem_obj( '/oracle/JDBEOP1/oradata3/' ,0)
                                                                          , l_filesystem_obj( '/oracle/JDBEOP1/oradata4/' ,0)
                                                                          );

    -- =========================================================
    -- add/delete/replace the lines in this table with the names
    -- of the new mount points for archive log datafiles
    -- =========================================================
    l_archive_tab             l_filesystem_tabtype := l_filesystem_tabtype(
                                                                            l_filesystem_obj( '/oracle/JDBEOP1/arcdata/' ,0)
                                                                          );

    -- =====================================
    -- gather info about data and temp files
    -- =====================================
    cursor c_datafiles is
    select file_id                                        file_id
    ,      file_name                                      full_file_name
    ,      substr(file_name,instr(file_name,'/',-1)+1)    file_name
    ,      round(sum(bytes)/1024/1024/1024,2)             gb
    ,      'data'                                         file_type
    from   dba_data_files ddf
    where  1=1
    and    upper(file_name) not like '%ARC%'
    group  by file_id
    ,      file_name
    union
    select file_id                                        file_id
    ,      file_name                                      full_file_name
    ,      substr(file_name,instr(file_name,'/',-1)+1)    file_name
    ,      round(sum(bytes)/1024/1024/1024,2)             gb
    ,      'temp'                                         file_type
    from   dba_temp_files ddf
    where  1=1
    group  by file_id
    ,      file_name
    order  by 4 desc
    ;

    -- ===================================
    -- gather info about archive log files
    -- ===================================
    cursor c_archivefiles is
    select file_id                                        file_id
    ,      file_name                                      full_file_name
    ,      substr(file_name,instr(file_name,'/',-1)+1)    file_name
    ,      round(sum(bytes)/1024/1024/1024,2)             gb
    ,      'data'                                         file_type
    from   dba_data_files ddf
    where  1=1
    and    upper(file_name) like '%ARC%'
    group  by file_id
    ,      file_name
    order  by 4 desc
    ;

    l_running_total       pls_integer    := 0;
    l_fs_num              binary_integer := 1;
    l_total_db_size       number;

begin

    dbms_output.enable(null);

    for r_row in c_datafiles
    loop

        -- ===========================================
        -- when we get to the last bucket, start again
        -- ===========================================
        if l_fs_num > nvl(l_datafile_tab.count,0) then
           l_fs_num := 1;
        end if;

        -- ==============================
        -- update the size of this bucket
        -- ==============================
        l_datafile_tab(l_fs_num).bigness := l_datafile_tab(l_fs_num).bigness + r_row.gb;

        -- =====================
        -- print out a good line
        -- =====================
        dbms_output.put_line ( 'set newname for '||r_row.file_type||'file '||r_row.file_id||' to '''||l_datafile_tab(l_fs_num).name||r_row.file_name||''';' );
        l_fs_num := l_fs_num+1;

    end loop;  -- c_datafiles


    -- ========================================= 
    -- datafiles done, now the archive log files
    -- =========================================
    l_fs_num := 1;
    for r_row in c_archivefiles
    loop

        -- ===========================================
        -- when we get to the last bucket, start again
        -- ===========================================
        if l_fs_num > nvl(l_archive_tab.count,0) then
           l_fs_num := 1;
        end if;

        -- ==============================
        -- update the size of this bucket
        -- ==============================
        l_archive_tab(l_fs_num).bigness := l_archive_tab(l_fs_num).bigness + r_row.gb;

        -- =====================
        -- print out a good line
        -- =====================
        dbms_output.put_line ( 'set newname for '||r_row.file_type||'file '||r_row.file_id||' to '''||l_archive_tab(l_fs_num).name||r_row.file_name||''';' );
        l_fs_num := l_fs_num+1;

    end loop;  -- c_archivefiles


    -- =========
    -- summarise
    -- =========
    dbms_output.put_line (null);
    dbms_output.put_line ( 'Summary');
    dbms_output.put_line ( '=======');
    for l_fs_num in 1 .. nvl(l_datafile_tab.count,0)
    loop
        dbms_output.put_line ( 'Mount point '||l_datafile_tab(l_fs_num).name||' will have a total size of '||l_datafile_tab(l_fs_num).bigness||' Gb');
    end loop;

    for l_fs_num in 1 .. nvl(l_archive_tab.count,0)
    loop
        dbms_output.put_line ( 'Mount point '||l_archive_tab(l_fs_num).name||' will have a total size of '||l_archive_tab(l_fs_num).bigness||' Gb');
    end loop;
    dbms_output.put_line (null);


exception

when others then
    dbms_output.put_line(sqlerrm);
    raise_application_error(-20000,'Problem');

end;
/

spool off

drop type l_filesystem_tabtype
/

drop type l_filesystem_obj
/

RMAN reporting by André Araujo

Reference: pythian.com
Reproduced here in case it disappears from the internets.


A lot of metadata about the RMAN backup jobs can be found in the V$RMAN_% views.
These views show past RMAN jobs as well as jobs currently running.
Once the jobs complete backup sets, metadata about the sets and pieces are also added to the control file and can be accessed through the V$BACKUP_% views.

For the queries in this post I need only four of those views:

  • V$BACKUP_SET
  • V$BACKUP_SET_DETAILS
  • V$RMAN_BACKUP_JOB_DETAILS
  • GV$RMAN_OUTPUT

Query 1 (Backup jobs' status and metadata) 

set lines 220
set pages 1000
col cf         for 9,999
col df         for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0         for 9,999
col i1         for 9,999
col l          for 9,999
col input_type for a13
col start_time for a19
col end_time   for a19
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST" noprint
select j.session_recid
,      j.session_stamp
,      to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time
,      to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss')   end_time
,      (j.output_bytes/1024/1024)                     output_mbytes
,      j.status
,      j.input_type
,      decode(to_char(j.start_time, 'd')
             , 1, 'Sunday',   2, 'Monday'
             , 3, 'Tuesday',  4, 'Wednesday'
             , 5, 'Thursday', 6, 'Friday'
             , 7, 'Saturday')   dow
,      j.elapsed_seconds
,      j.time_taken_display
,  x.cf, x.df, x.i0, x.i1, x.l
,  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;

Query 2 (Backup set details) 

set lines 220
set pages 1000
col backup_type for a4 heading "TYPE"
col controlfile_included heading "CF?"
col incremental_level heading "INCR LVL"
col pieces for 999 heading "PCS"
col elapsed_seconds heading "ELAPSED|SECONDS"
col device_type for a10 trunc heading "DEVICE|TYPE"
col compressed for a4 heading "ZIP?"
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col input_file_scan_only for a4 heading "SCAN|ONLY"
select
  d.bs_key, d.backup_type, d.controlfile_included, d.incremental_level, d.pieces,
  to_char(d.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(d.completion_time, 'yyyy-mm-dd hh24:mi:ss') completion_time,
  d.elapsed_seconds, d.device_type, d.compressed, (d.output_bytes/1024/1024) output_mbytes, s.input_file_scan_only
from V$BACKUP_SET_DETAILS d
  join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where session_recid = &SESSION_RECID
  and session_stamp = &SESSION_STAMP
order by d.start_time;

Query 3 (Backup job output) 

set lines 200
set pages 1000
select output
from GV$RMAN_OUTPUT
where session_recid = &SESSION_RECID
  and session_stamp = &SESSION_STAMP
order by recid;
Retrieved from "https://mailnest.com/dbawiki/index.php?title=RMAN&oldid=1955"