Duplicating Database using RMAN:-

This test was done in 11.2.0.4 version

Target Database is UAT database :- UAT

Auxiliary Database is Prod database (duplicate database) :- Prod

1. Checking password file for TARGET and Auxiliary Database

Sql>select * from V$PWFILE_USERS;

Sql>grant sysdba to sys;

Sql>alter user sys identified by password;

Create Password File On Remote Server For Aux DB (if its not already present).

You must also have a Password file on the original server for the Target DB.

IMPORTANT: ** Set the password to the same as target database. **

If no password file on Target, create and bounce the database

On UNIX:

$cd $ORACLE_HOME/dbs

$orapwd file=orapwSID password=xxxxxxxx force=y (if you already have one,

use same name)

(Note: Replace xxxxxxxxx with your actual password for the SYS user.)

2. Checking connections between TARGET and Auxiliary Databases.

Configure TNSNAMES.ora  entry for TARGET DB entry in Auxiliary DB and Auxiliary DB entry in TARGET DB

Check the connections by tnsping on both DB side .

3. Performing TARGET Full Database backup with controlfile on Disk Level by RMAN

Note:- The storage should be common or you need to do NFS this backup mountpoint to the remote server (auxiliary database location) during the rman duplicate command

Connect TARGET DB

Connect RMAN and RUN the below RMAN script to take FULL TARGET Database backup.

RUN {

  ALLOCATE CHANNEL t1 TYPE DISK FORMAT ‘/dbrestore/UAT_rman/UATDB%d_DB_%u_%s_%p’;

  BACKUP DATABASE PLUS ARCHIVELOG;

  RELEASE CHANNEL t1;

  }

4. Set the following 2 parameters in Auxiliary DB init.ora (PFILE)

Create PFILE for Aux DB.

Copy the initTARGET.ora file to the Aux server and modify.

Make sure all paths are set correctly for new database.

update these:

Set CONTROL_FILES parameter based on new database locations.

Set DB_NAME parameter based on new database.

These are new:

Set DB_FILE_NAME_CONVERT based on new database.

Ex: db_file_name_convert=’OLD_FILE_NAME’,’NEW_FILE_NAME’

Set LOG_FILE_NAME_CONVERT based on new database.

Ex: log_file_name_convert=’OLD_FILE_NAME’,’NEW_FILE_NAME’

5. Start Auxiliary Database (prod) In A NOMOUNT State From Aux Server Through SQL*Plus.

Sqlplus / as sysdba

STARTUP FORCE NOMOUNT

6. Duplicating TARGET Database :-

RMAN> connect target

connected to target database: UAT (DBID=293507132)

RMAN> connect auxiliary sys/W0rldw1de@prod

connected to auxiliary database: PROD (DBID=534731388)

RMAN>

  run {

       allocate auxiliary channel t1 type disk;

       allocate auxiliary channel t2 type disk;

       allocate auxiliary channel t3 type disk;

      duplicate target database to prod;  

    }

Note:- If the target (UAT) database backup was taken as offline backup it will automatically duplicate, if the target DB is in online, you need to set the archivelog number in the beginning during this duplicate command so that the auxiliary (prod) DB will be restored upto that point of time.

7. Checking VALID/INVALID objects in Auxiliary DB (After DUPLICATED from TARGET).

Check the object count (VALID & INVALID) in TARGET DB by

Select count(*) from dba_objects where status=’VALID’;

Select count(*) from dba_objects where status=’INVALID’;

(Confirm all the objects are duplicated properly)

Check the object count (VALID & INVALID) in Auxiliary DB by

Select count(*) from dba_objects where status=’VALID’;

Select count(*) from dba_objects where status=’INVALID’;

Backup Fails Because of Control File Enqueue

Backup Fails Because of Control File Enqueue: Scenario

In this scenario, a backup job fails because RMAN cannot make a snapshot control file. The message stack is as follows:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 25/05/2014 22:48:44
ORA-00230: operation disallowed: snapshot control file enqueue unavailable
Backup Fails Because of Control File Enqueue: Diagnosis
When RMAN needs to back up or resynchronize from the control file, it first creates a snapshot or consistent image of the control file. If one RMAN job is already backing up the control file while another needs to create a new snapshot control file, then you may see the following message:

waiting for snapshot control file enqueue

Under normal circumstances, a job that must wait for the control file enqueue waits for a brief interval and then successfully obtains the enqueue. RMAN makes up to five attempts to get the enqueue and then fails the job. The conflict is usually caused when two jobs are both backing up the control file, and the job that first starts backing up the control file waits for service from the media manager.

To determine which job is holding the conflicting enqueue:

After you see the first message stating “RMAN-08512: waiting for snapshot control file enqueue”, start a new SQL*Plus session on the target database:

% sqlplus ‘SYS/oracle@trgt AS SYSDBA’

Execute the following query to determine which job is causing the wait:

SELECT s.SID, USERNAME AS “User”, PROGRAM, MODULE,
ACTION, LOGON_TIME “Logon”, l.*
FROM V$SESSION s, V$ENQUEUE_LOCK l
WHERE l.SID = s.SID
AND l.TYPE = ‘CF’
AND l.ID1 = 0
AND l.ID2 = 2;

You should see output similar to the following (the output in this example has been truncated):

SID User Program Module Action Logon
— —- ——————– ——————- —————- ———
9 SYS rman@h13 (TNS V1-V3) backup full datafile: c10000210 STARTED 25-May-2014
Backup Fails Because of Control File Enqueue: Solution
Commonly, enqueue situations occur when a job is writing to a tape drive, but the tape drive is waiting for new tape to be inserted. If you start a new job in this situation, then you will probably receive the enqueue message because the first job cannot complete until the new tape is loaded.

After you have determined which job is creating the enqueue, you can do one of the following:

Wait until the job holding the enqueue completes

Cancel the current job and restart it after the job holding the enqueue completes

Cancel the job creating the enqueue

Rman Restore SPFILE from controlfile autoback

Once I have wrongly deleted the spfile in our environment.. Thank god the db was up and running so there was no need for the spfile at the time.. Then I have restore the spfile from the controlfile autoback.

If you have configured control file autobackups, the SPFILE is backed up with the control file whenever an autobackup is taken.

If you want to restore the SPFILE from the autobackup, you must first set the DBID for your database, and then use the RESTORE SPFILE FROM AUTOBACKUP command. The procedure is similar to restoring the control file from autobackup. You must first set the DBID for your database, and then use the RESTORE CONTROLFILE FROM AUTOBACKUP command:

RMAN uses the autobackup format and DBID to hunt for control file autobackups, and if a control file autobackup is found, restores the SPFILE from that backup to its default location.

RMAN> SET DBID 1066917090;

RMAN>run {
2> allocate channel c1 type ‘sbt_tape’ parms ‘ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/emsp_tdpo.opt)’;
3> RESTORE SPFILE to ‘/u01/app/oracle/product/spfiletestdb.ora’ FROM AUTOBACKUP;  ## if you not specifying any location it will                       ##restore to the default location ($ORACLE_HOME)
4> release channel c1;
5> }

allocated channel: c1
channel c1: sid=65 devtype=SBT_TAPE
channel c1: Data Protection for Oracle: version 5.5.1.0

Starting restore at 25-JAN-201212:46:31

channel c1: looking for autobackup on day: 20120125
channel c1: autobackup found: c-849745015-20120125-01
channel c1: SPFILE restore from autobackup complete
Finished restore at 25-JAN-201212:50:01

released channel: c1

RMAN> exit

Script to find out the RMAN DB backup status

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, 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
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

To get the current status of the Running RMAN JOB

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;