Archive

Archive for February, 2013

rman restore of controlfile and spfile

February 21, 2013 Leave a comment

RESTORE CONTROLFILE USING RMAN

backups on disk no catalog  

RMAN> set DBID xxxxxxx
RMAN> restore controlfile from  {'<path>' | autobackup};

backups on disk with catalog  

RMAN> restore controlfile

Restore of the control file from controlfile autobackup
Database state : Nomount

RMAN> set dbid xxxxxx

RMAN> restore controlfile from autobackup;

The restored control file is written to all locations listed in the CONTROL_FILES initialization parameter.

Database state : OPEN

RMAN> restore until time 'sysdate-1' CONTROLFILE to '/u02/oradata/20130218_control01.ctl' from autobackup;

Note: The until time clause set the time validity for controlfile. By default RMAN looks for autobackup for 7 days.
If you want to override this then simply use this:

RMAN> restore until time 'sysdate-1 'CONTROLFILE to '/u02/oradata/20130218_control01.ctl' from autobackup maxdays 31;

if you multiple backups of the controlfile on a particular day , restore from autobackup always
restores the latest copy
If you need to restore controlfile from a specific backup then we need to use “SET UNTIL TIME”


run
{
set until time "to_date('20130218:12:01','yyyymmdd:hh24:mi')";
restore controlfile from autobackup preview;
}

Restore Controlfile to a new location

restore controlfile to ‘d:\controlfile_backup\04042012_control01.ctl’;

you can run the above command when the database is in a nomount/mount / open  states as you are not overwritting the controlfile

restore controlfile from a know position

rman> restore controlfile from 'filename';

RESTORING THE SERVER PARAMETER FILE (SPFILE) FROM BACKUP

using recovery catalog

rman> connect target /

rman> startup force mount; -- ( rman starts the instance with a dummy parameter file)

rman> restore spfile  from autobackup ;

to restore to a non default location

rman> restore spfile to '/tmp/spfiletest.ora' from autobackup ;

Using controlfile autobackups

rman> set dbid xxxxxxx;  ( if you need to get the dbid refer to http://arjudba.blogspot.co.uk/2008/05/how-to-discover-find-dbid.html )

rman> restore spfile from autobackup;

if you need to serach past the 7 days default

rman> restore until time 'sysdate-1' SPFILE to '/tmp/spfiletest.ora' from autobackup maxdays 100;

Example of recovering spfile and controlfile using rman

rman target =/

rman> set dbid = xxxxxx  if you need to get the dbid refer to http://arjudba.blogspot.co.uk/2008/05/how-to-discover-find-dbid.html )
rman>
run
{
set controlfile autobackup format for device type disk to '/rmanbackup/oradata/uknppp/cf_%F';
restore spfile from autobackup;
}

rman> shutdown;

rman> startup force nomount;

rman> run
{

set controlfile autobackup format for device type disk to '/rmanbackup/oradata/uknppp/cf_%F';
restore controlfile from autobackup;

}

Advertisements
Categories: Oracle

restoring the OCR in 11gr2

February 21, 2013 Leave a comment

Restoring the OCR

If you back it up, there might come a time to restore it. Recovering the OCR from the physical backups is fairly straight forward, just follow these steps:

Locate the OCR backup using the ocrconfig command.

ocrconfig -showbackup

Stop Oracle Clusterware (on all nodes)

crsctl stop cluster -all

Stop CRS on all nodes

crsctl stop crs

Restore the OCR backup (physical) with the ocrconfig command.

ocrconfig -restore {path_to_backup/backup_file_to_restore}

Restart CRS

crsctl start crs

Check the integrity of the newly restored OCR:

cluvfy comp ocr -n all

You can also restore the OCR using a logical backup as seen here:

Locate your logical backup.

Stop Oracle Clusterware (on all nodes)

crsctl stop cluster -all

Stop CRS on all nodes

crsctl stop crs

Restore the OCR backup (physical) with the ocrconfig command.

ocrconfig -import /tmp/export_file.fil

Restart CRS

crsctl start crs

Check the integrity of the newly restored OCR:

cluvfy comp ocr -n all
Categories: Oracle Rac

RMAN-06059: expected archived log not found

February 7, 2013 Leave a comment

trying to fix it by crosscheck:
run {
allocate channel c1 type disk ;
crosscheck archivelog all ;
release channel c1 ;
}

Categories: Oracle

Viewing RMAN jobs status and output

February 6, 2013 Leave a comment

extracts of this post have been taken from
http://www.pythian.com/blog/viewing-rma-jobs-status-and-output/

Backup jobs’ status and metadata

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.

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


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 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"
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, 'Monday', 2, 'Tuesday',
                                     3, 'Wednesday', 4, 'Thursday',
                                     5, 'Friday', 6, 'Saturday',
                                     7, 'Sunday') 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;

CF: Number of controlfile backups included in the backup set
DF: Number of datafile full backups included in the backup set
I0: Number of datafile incremental level-0 backups included in the backup set
I1: Number of datafile incremental level-1 backups included in the backup set
L: Number of archived log backups included in the backup set
OUT INST: Instance where the job was executed and the output is available (see below)

Another important thing to note is that in a RAC environment some fields for a RUNNING backup job may contain invalid
information until the backup job is finished. To get consistent information, run this query on the node where the backup is running.

Backup sizes


 select ctime "Date",
       decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type,
        bsize "Size MB"
 from (select trunc(bp.completion_time) ctime
              , backup_type
              , round(sum(bp.bytes/1024/1024),2) bsize
       from v$backup_set bs, v$backup_piece bp
       where bs.set_stamp = bp.set_stamp
       and bs.set_count  = bp.set_count
       and bp.status = 'A'
       group by trunc(bp.completion_time), backup_type)
order by 1, 2;

Backup set details

Once you found the general information about the backup sets available, you may need to get more information about
the backup sets for one particular backup job. Each backup job is uniquely identified by (SESSION_RECID, SESSION_STAMP), which are listed by the query above.

The query below retrieves details for a backup job, given a pair of values for (SESSION_RECID, SESSION_STAMP):


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;

From Recovery Catalog

RMAN obtains backup and recovery metadata from a target database control file and stores it in the tables of the recovery catalog. The recovery catalog views are derived from these tables. Note that the recovery catalog views are not normalized or optimized for user queries.In general, the recovery catalog views are not as user-friendly as the RMAN reporting commands. For example, when you start RMAN and connect to a target database, you obtain the information for this target database only when you issue LIST, REPORT, and SHOW commands.

If you have ten different target databases registered in the same recovery catalog, then any query of the catalog views show the metadata for all incarnations of all ten databases. You often have to perform complex selects and joins among the views to extract usable information about a database incarnation.

Most of the catalog views have a corresponding V$ view in the database. For example, RC_BACKUP_PIECE corresponds to V$BACKUP_PIECE. The primary difference between the recovery catalog view and corresponding V$ view is that each recovery catalog view contains metadata about all the target databases registered in the recovery catalog. The V$ view contains information only about itself.

set pages 2000 lines 200
COL STATUS FORMAT a9
COL hrs FORMAT 999.99
select DB_NAME,
INPUT_TYPE,
STATUS,
TO_CHAR(START_TIME,’mm/dd/yy hh24:mi’) start_time,
TO_CHAR(END_TIME,’mm/dd/yy hh24:mi’) end_time,
ELAPSED_SECONDS/3600 hrs,
INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,
OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,
OUTPUT_DEVICE_TYPE
FROM RC_RMAN_BACKUP_JOB_DETAILS
–where DB_NAME=’SBLTPS’
order by DB_NAME,SESSION_KEY;
Categories: Oracle