rman – Gokhan Atil’s Blog

Renaming Files for RMAN Restore

We usually need to duplicate our databases to test servers which have different disk layouts than production server. So we need to distribute the datafiles according to the disk space and directory structure of the new server. It becomes a time-consuming task if you’re dealing with hundreds of datafiles. Today I saw a PL/SQL script written by Bobby Durrett for renaming datafiles for RMAN restore. It produces required “set newname” commands which can be used by RMAN. That was a great idea and I decided to write my own version to satisfy my needs:

DECLARE
    CURSOR DF_CURSOR IS 
        SELECT 'data' ft, FILE_ID, FILE_NAME, 
        BYTES / (1024 * 1024 * 1024) fsize
        FROM dba_data_files
        union all 
        SELECT 'temp' ft, FILE_ID, FILE_NAME, 
        BYTES / (1024 * 1024 * 1024) fsize
        FROM dba_temp_files
        ORDER BY ft, file_id;

   TYPE array_number IS TABLE OF NUMBER;
   TYPE array_varchar2 IS TABLE OF VARCHAR2 (200);

   diskno      NUMBER;

   diskspace   array_number := array_number (
                                   30,
                                   20,
                                   20,
                                   120); -- Sizes in GB

   diskpath   array_varchar2 := array_varchar2 (
                                   '/u01/data',
                                   '/u02/data',
                                   '/u03/data',
                                   '/u04/data');

   prefix      VARCHAR2 (200) := '/SID'; -- will be added to diskpaths
   
BEGIN
   FOR df IN DF_CURSOR
   LOOP
      FOR diskno IN 1 .. diskspace.COUNT
      LOOP
         IF (diskspace (diskno) > df.fsize)
         THEN
            DBMS_OUTPUT.put_line ( 'set newname for ' || df.ft || 'file '
               || df.FILE_ID || ' to  ''' || diskpath (diskno) || prefix
               || SUBSTR (df.FILE_NAME, INSTR (df.FILE_NAME, '/', -1)) 
               || ''';');
            diskspace (diskno) := diskspace (diskno) - df.fsize;
            EXIT;
            ELSE
            IF diskno = diskspace.COUNT then
               DBMS_OUTPUT.put_line ('# no space for '||df.FILE_ID||' '
               ||SUBSTR (df.FILE_NAME, INSTR (df.FILE_NAME, '/', -1)));
            END IF;
         END IF;
      END LOOP;
   END LOOP;
END;
/

DECLARE

CURSOR DF_CURSOR IS

SELECT 'data' ft, FILE_ID, FILE_NAME,

BYTES / (1024 * 1024 * 1024) fsize

FROM dba_data_files

union all

SELECT 'temp' ft, FILE_ID, FILE_NAME,

BYTES / (1024 * 1024 * 1024) fsize

FROM dba_temp_files

ORDER BY ft, file_id;

TYPE array_number IS TABLE OF NUMBER;

TYPE array_varchar2 IS TABLE OF VARCHAR2 (200);

diskno NUMBER;

diskspace array_number := array_number (

30,

20,

120); -- Sizes in GB

diskpath array_varchar2 := array_varchar2 (

'/u01/data',

'/u02/data',

'/u03/data',

'/u04/data');

prefix VARCHAR2 (200) := '/SID'; -- will be added to diskpaths

BEGIN

FOR df IN DF_CURSOR

LOOP

FOR diskno IN 1 .. diskspace.COUNT

LOOP

IF (diskspace (diskno) > df.fsize)

THEN

DBMS_OUTPUT.put_line ( 'set newname for ' || df.ft || 'file '

|| df.FILE_ID || ' to ''' || diskpath (diskno) || prefix

|| SUBSTR (df.FILE_NAME, INSTR (df.FILE_NAME, '/', -1))

|| ''';');

diskspace (diskno) := diskspace (diskno) - df.fsize;

EXIT;

ELSE

IF diskno = diskspace.COUNT then

DBMS_OUTPUT.put_line ('# no space for '||df.FILE_ID||' '

||SUBSTR (df.FILE_NAME, INSTR (df.FILE_NAME, '/', -1)));

END IF;

END LOOP;

END;

June 21, 2013Gokhan Atil7 CommentsOracleoracle, rman

Bash Script to Upload RMAN Backups via FTP

Our customer asked if they can automatically upload latest RMAN backups via FTP to another server. Because they keep latest 3 backups in same directory, I wrote a small bash script which calls an SQL script to queries RMAN backups and then uploads only the backup pieces belong to the latest backup job. I wanted to share this script because it demonstrates some interesting methods such as handling arrays returned from SQLPLus.

Here’s the SQL script (rmanfind.sql) to query RMAN backups. Our customer takes archive log backups during the day, so I needed to state full backup jobs while querying.

SET HEA OFF
SET FEED OFF
SET LINES 500
SET PAGES 500
SET TRIM ON

SELECT handle
from V$BACKUP_PIECE
where rman_Status_stamp IN (
select  stamp from V$RMAN_STATUS where parent_stamp =
 (SELECT session_stamp
from V$RMAN_BACKUP_JOB_DETAILS
where start_time =
(select max(start_time) from 
V$RMAN_BACKUP_JOB_DETAILS where input_type = 'DB FULL')));

SET HEA OFF

SET FEED OFF

SET LINES 500

SET PAGES 500

SET TRIM ON

SELECT handle

from V$BACKUP_PIECE

where rman_Status_stamp IN (

select stamp from V$RMAN_STATUS where parent_stamp =

(SELECT session_stamp

from V$RMAN_BACKUP_JOB_DETAILS

where start_time =

(select max(start_time) from

V$RMAN_BACKUP_JOB_DETAILS where input_type = 'DB FULL')));

March 18, 2013Gokhan Atil16 CommentsOraclebash, ftp, rman

Backup and Recovery Best Practices

Here are the best practices for backup and recovery for Oracle Databases. Although most of them are basic principles and can be applied to all Oracle versions, some of them are only valid for Oracle 10g and newer databases:

1) No need to explain: You must always run your DB in Archivelog mode!

2) Multiplex the controlfile at least 3 different locations. Be sure that they are located in different partitions (if possible different physical disks).

3) Multiplex the online redo log groups and members, be sure that putting them in different partitions (if possible different physical disks):

SELECT GROUP#, MEMBER FROM V$LOGFILE; 
ALTER DATABASE ADD LOGFILE MEMBER '/u01/oradata/redo12.log' TO GROUP 1;

1 2	SELECT GROUP#, MEMBER FROM V$LOGFILE; ALTER DATABASE ADD LOGFILE MEMBER '/u01/oradata/redo12.log' TO GROUP 1;

4) Take regular backups and verify that they work! Use validate command of RMAN or restore them to a test environment periodically:

RMAN&gt; RESTORE DATABASE VALIDATE;
RMAN&gt; RESTORE ARCHIVELOG ALL VALIDATE;
RMAN&gt; RECOVER DATABASE TEST;

RMAN> RESTORE DATABASE VALIDATE;

RMAN> RESTORE ARCHIVELOG ALL VALIDATE;

RMAN> RECOVER DATABASE TEST;

June 20, 2011Gokhan Atil2 CommentsOraclebackup, best practices, recovery, rman

Performing Block Media Recovery with RMAN

We’ll corrupt a block on a datafile and try to restore it via RMAN. First let’s create a sample tablespace:

CREATE TABLESPACE SAMPLETBS DATAFILE '/oracle/oradata/test/sample01.dbf' 
SIZE 10M AUTOEXTEND OFF;

1 2	CREATE TABLESPACE SAMPLETBS DATAFILE '/oracle/oradata/test/sample01.dbf' SIZE 10M AUTOEXTEND OFF;

Then let’s create a table in this tablespace and insert a sample row:

CREATE TABLE SAMPLE_TABLE ( A NUMBER ) TABLESPACE SAMPLETBS;
INSERT INTO SAMPLE_TABLE VALUES ( 1 );
COMMIT;

CREATE TABLE SAMPLE_TABLE ( A NUMBER ) TABLESPACE SAMPLETBS;

INSERT INTO SAMPLE_TABLE VALUES ( 1 );

COMMIT;

Now we need to find the block number of this record (so we can corrupt it):

SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) blockno FROM SAMPLE_TABLE;

BLOCKNO          
----------
132

SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) blockno FROM SAMPLE_TABLE;

BLOCKNO

----------

132

Before we corrupt the datablock, we’ll take a full backup.

RMAN&gt; BACKUP DATABASE;

1	RMAN> BACKUP DATABASE;

After the backup completed, we’ll fill this block with zeros at the OS level.

January 25, 2011Gokhan Atil1 CommentOracleblock media recovery, rman

Oracle 11g New Feature: RMAN Data Recovery Advisor

In Oracle 11g, RMAN provides a new advisor called Data Recovery Advisor (DRA). DRA has several new commands to help DBAs for performing recovery tasks.

To list of database failures, you can issue:

RMAN> list failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
522        HIGH     OPEN      08-SEP-09     One or more non-system datafiles 
                                            are missing

RMAN> list failure;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

522 HIGH OPEN 08-SEP-09 One or more non-system datafiles

are missing

DRA will consolidate related failures into a single failure. You can list a failure individually by issuing “list failure X detail” command.

RMAN> list failure 522 detail;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
522        HIGH     OPEN      08-SEP-09     One or more non-system datafiles 
                                            are missing

  Impact: See impact for individual child failures
  List of child failures for parent failure ID 522
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  525        HIGH     OPEN      08-SEP-09     Datafile 4: '/abc' is missing

    Impact: Some objects in tablespace USERS might be unavailable

RMAN> list failure 522 detail;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

522 HIGH OPEN 08-SEP-09 One or more non-system datafiles

are missing

Impact: See impact for individual child failures

List of child failures for parent failure ID 522

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

525 HIGH OPEN 08-SEP-09 Datafile 4: '/abc' is missing

Impact: Some objects in tablespace USERS might be unavailable

September 8, 2009Gokhan Atil2 CommentsOracle11g, new feature, rman