How To Restore RMAN Disk backups of RAC to RAC Database. And configuration Application on Multitier

RAC to RAC Cloning

Terminologies used

Source database:

Database whose clone will be created (AWGPROD).

Target database:

Database on which clone of source will be made (QTA).

Requirement on source database:

Before take Cold Rman backup make sure adpreclone.pl has been run successfully on apps and db tier as

$ perl adpreclone.pl dbTier 

$ perl adpreclone.pl appsTier

Appspasswd

apps768prelive

               

There must be a latest cold backup of source database (preferably) of level-0 including “controlfile”.

Backup directory path set to: /PRELIVEBKP/PRELIVEBKP/RMANPRELIVEBKP/rmancoldbkp29apr13

·         Down both apps tier services cleanly on source as below

Sh adstpall.sh apps/***

·         Down both cluster db services on source as below

Srvctl stop database –d erpdb

It is assumed that source database is running in archive log mode and we will take cold backup to avoid db recovery after restoration of database.

Note: We will take normal backup instead of encrypted backup to avoid wallet/encryption related issues while restoration on target database.

 

###########RMAN Backup Script for database ERPDB  ################################

ORA_ENVFILE="/d01/app/erpdb/product/11.2.0/dbhome_1/erpdb1_AWGdb.env"

. $ORA_ENVFILE

rman target / log=/PRELIV EBKP/PRELIVEBKP/RMANPRELIVEBKP/rmancoldbkp29apr13/rmanbkplog_`date +%d%m%Y`.log <<EOF

configure retention policy to redundancy 2;

CONFIGURE ENCRYPTION FOR DATABASE OFF;

configure controlfile autobackup on;

configure controlfile autobackup format for device type disk to '/PRELIVEBKP/PRELIVEBKP/RMANPRELIVEBKP/rmancoldbkp29apr13/%d%F.ctl';

configure device type disk parallelism 32 backup type to compressed backupset;

configure channel device type disk format '/PRELIVEBKP/PRELIVEBKP/RMANPRELIVEBKP/rmancoldbkp29apr13/rmancold_bkp_%T_%d_%s_%U.bak';

sql "create pfile=''/PRELIVEBKP/PRELIVEBKP/RMANPRELIVEBKP/rmancoldbkp29apr13/pfile`date +%d%m%Y`.ora'' from spfile";

backup as compressed backupset database;

exit

EOF

 

Now we will move backup from source to target machine (on 1^st db node where we will perform restoration) as below.

Scp –r rmancoldbkp29apr13 oracle@101.73.65.117: /backupebsdb/

After successfully backup moved, start apps and db services on source application for user.

Requirement on target database

The target database machine must have sufficient disk space for the restoration of the backup sets being used.

Steps involved

Restore backup

The backup of source will be restored on target machine. This backup includes all datafiles and controlfiles.

Perform the following steps in the given sequence:

Step 1

Before doing anything we will take backup of current pfile on db1, create pfile from spfile and remove controlfile, onlineredologs, tempfiles, datafile from ‘+EBSDATA/AWGprod’ as follows

bash-3.2$ mv initAWGprod1.ora initAWGprod1.ora.bkp.30apr2013

bash-3.2$ mv initAWGprod.ora initAWGprod.ora.bkp.30apr2013

bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 30 14:41:13 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount

SQL> create pfile='/d01/app/oracle/product/11.2.0/db_1/dbs/initAWGprod1.ora' from spfile='+EBSDATA/spfileAWGprod.ora’;

File created.

SQL> shut immediate

Set GRID HOME env to remove files from ASM diskgroup.

bash-3.2$ export ORACLE_HOME=/d01/app/11.2.0/grid

bash-3.2$ export ORACLE_SID=+ASM1

bash-3.2$ export PATH=$PATH:$ORACLE_HOME/bin

ASMCMD> pwd

+EBSDATA/AWGPROD

ASMCMD> ls

CONTROLFILE/

DATAFILE/

ONLINELOG/

PARAMETERFILE/

TEMPFILE/

ASMCMD> rm –f DATAFILE/

ASMCMD> rm –f CONTROLFILE /

ASMCMD> rm –f ONLINELOG /

ASMCMD> rm –f TEMPFILE /

Remove all datafiles,controlfile,tempfiles,onlinelog from above mentioned folders to avoid and overwrite or space issue while restoration.

Edit newly created pfile (from spfile) and change db_name parameter from ‘AWGprod’ to ‘erpdb’ as we haveto restore backup of erpdb.

bash-3.2$ vi initAWGprod1.ora

From

*.db_name=’erpdb’

To

*.db_name=’AWGprod’

Step 2

Restore control file on target machine.

Open an RMAN prompt:

Start database in nomount state

bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 30 14:41:13 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/d01/app/oracle/product/11.2.0/db_1/dbs/initAWGprod1.ora';

$ rman target / nocatalog

RMAN> restore controlfile from '/backupebsdb/rmancoldbkp29apr13/ERPDBc-3075940697-20130429-07.ctl';

Starting restore at 30-APR-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=2647 instance=AWGprod1 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=+EBSDATA/erpdb/controlfile/current.322.814120777

Finished restore at 30-APR-13

Note: new controlfile create in +EBSDATA/erpdb/controlfile with their db_name.

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> exit

Now database is in mount mode.

Step 3

Register the backup piece(s) in control file according to target machine locations.

RMAN> catalog start with  '/backupebsdb/rmancoldbkp29apr13';

using target database control file instead of recovery catalog

searching for all files that match the pattern /backupebsdb/rmancoldbkp29apr13

Step 4

Perform restoration with below script.

$ORACLE_HOME/bin/rman target / nocatalog <<EOF

run{

allocate channel c1 device type disk;

allocate channel c2 device type disk;

allocate channel c3 device type disk;

set newname for datafile 1 to '+EBSDATA/erpdb/datafile/system01.dbf';

set newname for datafile 2 to '+EBSDATA/erpdb/datafile/system02.dbf';

set newname for datafile 3 to '+EBSDATA/erpdb/datafile/system03.dbf';

set newname for datafile 4 to '+EBSDATA/erpdb/datafile/system04.dbf';

set newname for datafile 5 to '+EBSDATA/erpdb/datafile/system05.dbf';

set newname for datafile 6 to '+EBSDATA/erpdb/datafile/system06.dbf';

set newname for datafile 7 to '+EBSDATA/erpdb/datafile/system07.dbf';

set newname for datafile 8 to '+EBSDATA/erpdb/datafile/system08.dbf';

set newname for datafile 9 to '+EBSDATA/erpdb/datafile/system09.dbf';

set newname for datafile 10 to '+EBSDATA/erpdb/datafile/system10.dbf';

set newname for datafile 11 to '+EBSDATA/erpdb/datafile/system11.dbf';

set newname for datafile 12 to '+EBSDATA/erpdb/datafile/undo01.dbf';

set newname for datafile 13 to '+EBSDATA/erpdb/datafile/a_archive01.dbf';

set newname for datafile 14 to '+EBSDATA/erpdb/datafile/a_int01.dbf';

set newname for datafile 15 to '+EBSDATA/erpdb/datafile/a_media01.dbf';

set newname for datafile 16 to '+EBSDATA/erpdb/datafile/a_nolog01.dbf';

set newname for datafile 17 to '+EBSDATA/erpdb/datafile/a_queue01.dbf';

set newname for datafile 18 to '+EBSDATA/erpdb/datafile/a_queue02.dbf';

set newname for datafile 19 to '+EBSDATA/erpdb/datafile/a_ref01.dbf';

set newname for datafile 20 to '+EBSDATA/erpdb/datafile/a_ref02.dbf';

set newname for datafile 21 to '+EBSDATA/erpdb/datafile/a_summ01.dbf';

set newname for datafile 22 to '+EBSDATA/erpdb/datafile/a_txn_data01.dbf';

set newname for datafile 23 to '+EBSDATA/erpdb/datafile/a_txn_data02.dbf';

set newname for datafile 24 to '+EBSDATA/erpdb/datafile/a_txn_data03.dbf';

set newname for datafile 25 to '+EBSDATA/erpdb/datafile/a_txn_ind01.dbf';

set newname for datafile 26 to '+EBSDATA/erpdb/datafile/a_txn_ind02.dbf';

set newname for datafile 27 to '+EBSDATA/erpdb/datafile/a_txn_ind03.dbf';

set newname for datafile 28 to '+EBSDATA/erpdb/datafile/a_txn_ind04.dbf';

set newname for datafile 29 to '+EBSDATA/erpdb/datafile/a_txn_ind05.dbf';

set newname for datafile 30 to '+EBSDATA/erpdb/datafile/ctxd01.dbf';

set newname for datafile 31 to '+EBSDATA/erpdb/datafile/odm.dbf';

set newname for datafile 32 to '+EBSDATA/erpdb/datafile/olap.dbf';

set newname for datafile 33 to '+EBSDATA/erpdb/datafile/owad01.dbf';

set newname for datafile 34 to '+EBSDATA/erpdb/datafile/portal01.dbf';

set newname for datafile 35 to '+EBSDATA/erpdb/datafile/sysaux01.dbf';

set newname for datafile 36 to '+EBSDATA/erpdb/datafile/apps_ts_tools01.dbf';

set newname for datafile 37 to '+EBSDATA/erpdb/datafile/interim.dbf';

set newname for datafile 38 to '+EBSDATA/erpdb/datafile/a_txn_data4.dbf';

set newname for datafile 39 to '+EBSDATA/erpdb/datafile/a_txn_data05.dbf';

set newname for datafile 40 to '+EBSDATA/erpdb/datafile/undo02.dbf';

set newname for datafile 41 to '+EBSDATA/erpdb/datafile/c_xxca1.dbf';

set newname for datafile 42 to '+EBSDATA/erpdb/datafile/sysaux02.dbf';

set newname for datafile 43 to '+EBSDATA/erpdb/datafile/a_txn_data06.dbf';

set newname for datafile 44 to '+EBSDATA/erpdb/datafile/undo03.dbf';

set newname for datafile 45 to '+EBSDATA/erpdb/datafile/undo04.dbf';

set newname for datafile 46 to '+EBSDATA/erpdb/datafile/a_ref03.dbf';

set newname for datafile 47 to '+EBSDATA/erpdb/datafile/a_summ02.dbf';

set newname for datafile 48 to '+EBSDATA/erpdb/datafile/system12.dbf';

set newname for datafile 49 to '+EBSDATA/erpdb/datafile/c_xxca2.dbf';

set newname for datafile 50 to '+EBSDATA/erpdb/datafile/a_txn_data07.dbf';

set newname for datafile 51 to '+EBSDATA/erpdb/datafile/undo05.dbf';

set newname for datafile 52 to '+EBSDATA/erpdb/datafile/a_ref04.dbf';

set newname for datafile 53 to '+EBSDATA/erpdb/datafile/a_txn_data08.dbf';

set newname for datafile 54 to '+EBSDATA/erpdb/datafile/a_txn_data09.dbf';

set newname for datafile 55 to '+EBSDATA/erpdb/datafile/a_txn_data10.dbf';

set newname for datafile 56 to '+EBSDATA/erpdb/datafile/a_txn_ind06.dbf';

set newname for datafile 57 to '+EBSDATA/erpdb/datafile/o1_mf_undotbs2_83kyylyp_.dbf';

set newname for datafile 58 to '+EBSDATA/erpdb/datafile/sysaux03.dbf';

set newname for datafile 59 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__85gcky4s_.dbf';

set newname for datafile 60 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__85j1mhyw_.dbf';

set newname for datafile 61 to '+EBSDATA/erpdb/datafile/o1_mf_sysaux_85ob61g6_.dbf';

set newname for datafile 62 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__86cb62r7_.dbf';

set newname for datafile 63 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__86cb99r0_.dbf';

set newname for datafile 64 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__884bxzjg_.dbf';

set newname for datafile 65 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88dj97b0_.dbf';

set newname for datafile 66 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88djh3nr_.dbf';

set newname for datafile 67 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88djl1bx_.dbf';

set newname for datafile 68 to '+EBSDATA/erpdb/datafile/o1_mf_system_88djqht8_.dbf';

set newname for datafile 69 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88h6xklr_.dbf';

set newname for datafile 70 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88h6ywnw_.dbf';

set newname for datafile 71 to '+EBSDATA/erpdb/datafile/o1_mf_system_88h70cwk_.dbf';

set newname for datafile 72 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88h72gqb_.dbf';

set newname for datafile 73 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88h74wfs_.dbf';

set newname for datafile 74 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__89wc4jy8_.dbf';

set newname for datafile 75 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__89ws999m_.dbf';

set newname for datafile 76 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__89wsckx1_.dbf';

set newname for datafile 77 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8byhjwgf_.dbf';

set newname for datafile 78 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8byho4lm_.dbf';

set newname for datafile 79 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8cy4xs9p_.dbf';

set newname for datafile 80 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8cy51r6s_.dbf';

set newname for datafile 81 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8cy56y3y_.dbf';

set newname for datafile 82 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8f5b79or_.dbf';

set newname for datafile 83 to '+EBSDATA/erpdb/datafile/o1_mf_custom_x_8f5b9ys7_.dbf';

set newname for datafile 84 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8f5bd39h_.dbf';

set newname for datafile 85 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8f61ggor_.dbf';

set newname for datafile 86 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8f61klp7_.dbf';

set newname for datafile 87 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7dojpl_.dbf';

set newname for datafile 88 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7dv8j0_.dbf';

set newname for datafile 89 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7f1mr2_.dbf';

set newname for datafile 90 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7f5ljy_.dbf';

set newname for datafile 91 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7fbo9s_.dbf';

set newname for datafile 92 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7fghfv_.dbf';

set newname for datafile 93 to '+EBSDATA/erpdb/datafile/o1_mf_system_8h7fm0kn_.dbf';

set newname for datafile 94 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8kf72xfo_.dbf';

set newname for datafile 95 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8kf74fgk_.dbf';

set newname for datafile 96 to '+EBSDATA/erpdb/datafile/undo06.dbf';

set newname for datafile 97 to '+EBSDATA/erpdb/datafile/o1_mf_sysaux_8mm63h3m_.dbf';

set newname for datafile 98 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8nhs6461_.dbf';

set newname for tempfile 1 to '+EBSDATA/erpdb/datafile/o1_mf_temp1_83mqxz18_.tmp';

set newname for tempfile 2 to '+EBSDATA/erpdb/datafile/o1_mf_temp2_83pbmk77_.tmp';

set newname for tempfile 3 to '+EBSDATA/erpdb/datafile/temp01.dbf';

set newname for tempfile 4 to '+EBSDATA/erpdb/datafile/temp02.dbf';

set newname for tempfile 5 to '+EBSDATA/erpdb/datafile/o1_mf_temp2_840mj9oj_.tmp';

set newname for tempfile 6 to '+EBSDATA/erpdb/datafile/o1_mf_temp1_85ftf43q_.tmp';

set newname for tempfile 7 to '+EBSDATA/erpdb/datafile/o1_mf_temp2_85ftg5mh_.tmp';

restore database;

switch datafile all;

switch tempfile all;

release channel c1;

release channel c2;

release channel c3;

}

EOF

Step 6

SQL> alter database backup controlfile to trace as ‘/d01/app/oracle/cntrol.sql’;

Database altered.

Edit cntrol.sql file as the following with adding online redo logs/groups. 

CREATE CONTROLFILE SET DATABASE "AWGPROD" RESETLOGS
    MAXLOGFILES 32
    MAXLOGMEMBERS 5
    MAXDATAFILES 512
    MAXINSTANCES 8
    MAXLOGHISTORY 20157
LOGFILE
GROUP 1 '+EBSDATA/erpdb/onlinelog/group_1.318.792864661'  SIZE 1024M BLOCKSIZE 512,
GROUP 2 '+EBSDATA/erpdb/onlinelog/group_2.319.792864669'  SIZE 1024M BLOCKSIZE 512,
GROUP 3 '+EBSDATA/erpdb/onlinelog/group_3.316.792864679'  SIZE 1024M BLOCKSIZE 512,
GROUP 4 '+EBSDATA/erpdb/onlinelog/group_4.317.792864687'  SIZE 1024M BLOCKSIZE 512,
GROUP 5 '+EBSDATA/erpdb/onlinelog/group_5.324.793822901'  SIZE 1024M BLOCKSIZE 512,
GROUP 6 '+EBSDATA/erpdb/onlinelog/group_6.325.793822949'  SIZE 1024M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+EBSDATA/erpdb/datafile/system01.dbf',
  '+EBSDATA/erpdb/datafile/system02.dbf',
  '+EBSDATA/erpdb/datafile/system03.dbf',
  '+EBSDATA/erpdb/datafile/system04.dbf',
  '+EBSDATA/erpdb/datafile/system05.dbf',
  '+EBSDATA/erpdb/datafile/system06.dbf',
  '+EBSDATA/erpdb/datafile/system07.dbf',
  '+EBSDATA/erpdb/datafile/system08.dbf',
  '+EBSDATA/erpdb/datafile/system09.dbf',
  '+EBSDATA/erpdb/datafile/system10.dbf',
  '+EBSDATA/erpdb/datafile/system11.dbf',
  '+EBSDATA/erpdb/datafile/undo01.dbf',
  '+EBSDATA/erpdb/datafile/a_archive01.dbf',
  '+EBSDATA/erpdb/datafile/a_int01.dbf',
  '+EBSDATA/erpdb/datafile/a_media01.dbf',
  '+EBSDATA/erpdb/datafile/a_nolog01.dbf',
  '+EBSDATA/erpdb/datafile/a_queue01.dbf',
  '+EBSDATA/erpdb/datafile/a_queue02.dbf',
  '+EBSDATA/erpdb/datafile/a_ref01.dbf',
  '+EBSDATA/erpdb/datafile/a_ref02.dbf',
  '+EBSDATA/erpdb/datafile/a_summ01.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_data01.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_data02.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_data03.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_ind01.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_ind02.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_ind03.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_ind04.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_ind05.dbf',
  '+EBSDATA/erpdb/datafile/ctxd01.dbf',
  '+EBSDATA/erpdb/datafile/odm.dbf',
  '+EBSDATA/erpdb/datafile/olap.dbf',
  '+EBSDATA/erpdb/datafile/owad01.dbf',
  '+EBSDATA/erpdb/datafile/portal01.dbf',
  '+EBSDATA/erpdb/datafile/sysaux01.dbf',
  '+EBSDATA/erpdb/datafile/apps_ts_tools01.dbf',
  '+EBSDATA/erpdb/datafile/interim.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_data4.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_data05.dbf',
  '+EBSDATA/erpdb/datafile/undo02.dbf',
  '+EBSDATA/erpdb/datafile/c_xxca1.dbf',
  '+EBSDATA/erpdb/datafile/sysaux02.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_data06.dbf',
  '+EBSDATA/erpdb/datafile/undo03.dbf',
  '+EBSDATA/erpdb/datafile/undo04.dbf',
  '+EBSDATA/erpdb/datafile/a_ref03.dbf',
  '+EBSDATA/erpdb/datafile/a_summ02.dbf',
  '+EBSDATA/erpdb/datafile/system12.dbf',
  '+EBSDATA/erpdb/datafile/c_xxca2.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_data07.dbf',
  '+EBSDATA/erpdb/datafile/undo05.dbf',
  '+EBSDATA/erpdb/datafile/a_ref04.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_data08.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_data09.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_data10.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_ind06.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_undotbs2_83kyylyp_.dbf',
  '+EBSDATA/erpdb/datafile/sysaux03.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__85gcky4s_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__85j1mhyw_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_sysaux_85ob61g6_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__86cb62r7_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__86cb99r0_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__884bxzjg_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88dj97b0_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88djh3nr_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88djl1bx_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_system_88djqht8_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88h6xklr_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88h6ywnw_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_system_88h70cwk_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88h72gqb_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88h74wfs_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__89wc4jy8_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__89ws999m_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__89wsckx1_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8byhjwgf_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8byho4lm_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8cy4xs9p_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8cy51r6s_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8cy56y3y_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8f5b79or_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_custom_x_8f5b9ys7_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8f5bd39h_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8f61ggor_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8f61klp7_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7dojpl_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7dv8j0_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7f1mr2_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7f5ljy_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7fbo9s_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7fghfv_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_system_8h7fm0kn_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8kf72xfo_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8kf74fgk_.dbf',
  '+EBSDATA/erpdb/datafile/undo06.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_sysaux_8mm63h3m_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8nhs6461_.dbf'
CHARACTER SET US7ASCII
;

SQL> @/d01/app/oracle/cntrol.sql;

CREATE CONTROLFILE SET DATABASE "AWGPROD" RESETLOGS

*

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-12720: operation requires database is in EXCLUSIVE mode

SOLUTION

After analyzing the issue, We found that cluster_database parameter needs to be set as FALSE. So proceeding with the change.

bash-3.2$ vi initAWGprod1.ora

*.cluster_database=FALSE

then

SQL> shut immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount pfile='/d01/app/oracle/product/11.2.0/db_1/dbs/initAWGprod1.ora.pfile';

ORACLE instance started.

Before create new control file comment on pfile old controlfile destination after creation copy new location and define it:

SQL> @/d01/app/oracle/cntrol.sql;

Control file created

Open the database with resetting log file headers.

RMAN> alter database open resetlogs;

Database altered.

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      +EBSDATA/AWGprod/controlfile/c
                                                 urrent.369.814133507
control_management_pack_access       string      DIAGNOSTIC+TUNING

After open database, create spfile from current pfile to shared location (+EBSDATA).

SQL> create spfile='+EBSDATA/spfileAWGprod.ora' from pfile='/d01/app/oracle/product/11.2.0/db_1/dbs/initAWGprod1.ora.pfile';

File created.

SQL> shut immediate

ORACLE instance shut down.

SQL> exit
bash-3.2$ sqlplus / as sysdba


Connected to an idle instance.

SQL> startup
ORACLE instance started.

SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +EBSDATA/spfileAWGprod.ora

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit

bash-3.2$ srvctl start database -d AWGprod
PRCR-1079 : Failed to start resource ora.AWGprod.db
CRS-5017: The resource action "ora.AWGprod.db start" encountered the following error:
ORA-01618: redo thread 2 is not enabled - cannot mount


CRS-2674: Start of 'ora.AWGprod.db' on 'AWGebsdb2' failed
CRS-2632: There are no more servers to try to place resource 'ora.AWGprod.db' on that would satisfy its placement policy


On the other end when we checked on 2nd db node its giving below error:

SQL> alter database mount
  2  ;
alter database mount
*
ERROR at line 1:
ORA-01618: redo thread 2 is not enabled - cannot mount

SOLUTION

After analyzing the issue, Start db on 1st node and run the following command to add group/logfiles in thread 2 and then enable thread2.


SQL> alter database add logfile thread 2 group 7 ('+EBSDATA/erpdb/onlinelog/group_7_01.dbf')  SIZE 1024M,
  2  group 8 ('+EBSDATA/erpdb/onlinelog/group_8_01.dbf')  SIZE 1024M,
  3  group 9 ('+EBSDATA/erpdb/onlinelog/group_9_01.dbf')  SIZE 1024M,
  4  group 10 ('+EBSDATA/erpdb/onlinelog/group_10_01.dbf')  SIZE 1024M,
  5  group 11 ('+EBSDATA/erpdb/onlinelog/group_11_01.dbf')  SIZE 1024M,
  6  group 12 ('+EBSDATA/erpdb/onlinelog/group_12_01.dbf')  SIZE 1024M;

Database altered.

SQL> alter database enable public thread 2;

Database altered.

Now we are able to start and stop db services with srvctl as below:

$ srvctl start database –d AWGprod

$ srvctl status database –d AWGprod

Db running on both nodes.

SQL> select * from v$tempfile;

no rows selected

SQL> alter tablespace TEMP1 add tempfile size 1024m;

Tablespace altered.

SQL> select * from v$tempfile;

     FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------
         1       7863567269 01-MAY-13          2          1 ONLINE  READ WRITE
1073741824     131072   1073741824       8192
+EBSDATA/AWGprod/tempfile/temp1.376.814285421



SQL> create temporary tablespace TEMP3 tempfile size 1024m;

Tablespace created.

SQL> alter database default temporary tablespace TEMP3;

Database altered.

SQL> select * from v$tablespace where name like '%TEMP%';

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         2 TEMP1                          NO  NO  YES
        20 TEMP2                          NO  NO  YES
        26 TEMP3                          NO  NO  YES

SQL> exit

SQL> show parameter db_name;

 

NAME                                 TYPE        VALUE

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

db_name                              string      AWGPROD

 

$cd $ORACLE_HOME/appsutil/install/AWGPROD_AWGebsdb1

$ sqlplus "/ as sysdba" @adupdlib.sql so

 

PL/SQL procedure successfully completed.

 

SQL>exit

bash-3.2$ hostname

AWGebsdb1.earms.army.gov

 

bash-3.2$ sqlplus / as sysdba

SQL> select * from v$option where parameter='Oracle Database Vault';

PARAMETER                            VALUE

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

Oracle Database Vault               TRUE

Enable archive log mode with database

bash-3.2$ hostname

AWGebsdb1.earms.army.gov

bash-3.2$ sqlplus / as sysdba

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /d01/ebsacfs/archive

Oldest online log sequence     1

Current log sequence           1

SQL> show parameter log_archive_dest

NAME                                 TYPE        VALUE

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

log_archive_dest                     string

log_archive_dest_1                   string      LOCATION=/d01/ebsacfs/archive

 

$srvctl stop database –d AWGprod

$srvctl start database –d AWGprod –o mount;

$sqlplus / as sysdba

 

SQL> Alter database archivelog;

SQL> Alter database open;

SQL> Alter system switch logfile;

$srvctl stop database –d AWGprod

$srvctl start database –d AWGprod

#cd /d01/ebsacfs/archive

#ls –ltr        [check archive logs are generating]

Step 7

At last Run autoconfig on both db tiers.

bash-3.2$ sh adautocfg.sh

Enter the APPS user password:

The log file for this session is located at: /d01/app/oracle/product/11.2.0/db_1/appsutil/log/AWGprod1_AWGebsdb1/05011430/adconfig.log

AutoConfig is configuring the Database environment...

AutoConfig will consider the custom templates if present.

        Using ORACLE_HOME location : /d01/app/oracle/product/11.2.0/db_1

        Classpath                   : :/d01/app/oracle/product/11.2.0/db_1/jdbc/lib/ojdbc5.jar:/d01/app/oracle/product/11.2.0/db_1/appsutil/java/xmlparserv2.jar:/d01/app/oracle/product/11.2.0/db_1/appsutil/java:/d01/app/oracle/product/11.2.0/db_1/jlib/netcfg.jar:/d01/app/oracle/product/11.2.0/db_1/jlib/ldapjclnt11.jar

        Using Context file          : /d01/app/oracle/product/11.2.0/db_1/appsutil/AWGprod1_AWGebsdb1.xml

Context Value Management will now update the Context file

        Updating Context file...COMPLETED

        Attempting upload of Context file and templates to database...COMPLETED

Updating rdbms version in Context file to db112

Updating rdbms type in Context file to 64 bits

Configuring templates from ORACLE_HOME ...

AutoConfig completed successfully.

Application tier preparation:

AWGEBS1:                                                                 

Now we will move only (COMMON_TOP and APPL_TOP) backup from source to target machine.

Scp –r BKPAPPS_AWGEBS_04272013.tar applmgr@101.73.65.137: /d01/prodapp

Check again below entries of AWGprod_AWGebs1.xml

Implement load balancing for the Oracle Applications database connections:

1.        Edit the Context file($CONTEXT_FILE) and set the value of "Tools OH TWO_TASK" (s_tools_twotask), "iAS OH TWO_TASK" (s_weboh_twotask) and "Apps JDBC Connect Alias" (s_apps_jdbc_connect_alias).

2.        To load balance the forms based applications database connections, set the value of "Tools OH TWO_TASK" to point to the <database_name>_balance alias generated in the tnsnames.ora file.

Change from “AWGprod” to “AWGprod _balance”

3.        To load balance the self-service applications database connections, set the value of "iAS OH TWO_TASK" (s_weboh_twotask) and "Apps JDBC Connect Alias"(s_apps_jdbc_connect_alias) to point to the <database_name>_balance alias generated in the tnsnames.ora file.

Change from “AWGprod” to “AWGprod _balance”

4.        Execute AutoConfig by running the command:

$ $AD_TOP/bin/adconfig.sh contextfile=$INST_TOP/appl/admin/AWGprod_AWGebs1.xml

5.        Restart the Applications processes, using the new scripts generated by AutoConfig.

6.        Ensure that value of the profile option "Application Database ID" is set to dbc file name generated in $FND_SECURE.

7.        Make sure all DB node names and scan names are resolvable (e-g HOST file entries) on appstier node if DNS is not used.

Note: If you are adding a new node to the application tier, repeat the above steps 1-6 for setting up load balancing on the new application tier node.

bash-3.2$ sh adautocfg.sh

Enter the APPS user password:

The log file for this session is located at: /d01/prodapp/inst/apps/AWGprod_AWGebs1/admin/log/05021502/adconfig.log

AutoConfig is configuring the Applications environment...

AutoConfig will consider the custom templates if present.

        Using CONFIG_HOME location     : /d01/prodapp/inst/apps/AWGprod_AWGebs1

        Classpath                   : /u1/prodapp/apps/apps_st/comn/java/lib/appsborg2.zip:/u1/prodapp/apps/apps_st/comn/java/classes

        Using Context file          : /d01/prodapp/inst/apps/AWGprod_AWGebs1/appl/admin/AWGprod_AWGebs1.xml

Context Value Management will now update the Context file

        Updating Context file...COMPLETED

        Attempting upload of Context file and templates to database...COMPLETED

Configuring templates from all of the product tops...

        Configuring AD_TOP........COMPLETED

        Configuring FND_TOP.......COMPLETED

        Configuring ICX_TOP.......COMPLETED

        Configuring MSC_TOP.......COMPLETED

        Configuring IEO_TOP.......COMPLETED

        Configuring BIS_TOP.......COMPLETED

        Configuring AMS_TOP.......COMPLETED

        Configuring CCT_TOP.......COMPLETED

        Configuring WSH_TOP.......COMPLETED

        Configuring CLN_TOP.......COMPLETED

        Configuring OKE_TOP.......COMPLETED

        Configuring OKL_TOP.......COMPLETED

        Configuring OKS_TOP.......COMPLETED

        Configuring CSF_TOP.......COMPLETED

        Configuring IGS_TOP.......COMPLETED

        Configuring IBY_TOP.......COMPLETED

        Configuring JTF_TOP.......COMPLETED

        Configuring MWA_TOP.......COMPLETED

        Configuring CN_TOP........COMPLETED

        Configuring CSI_TOP.......COMPLETED

        Configuring WIP_TOP.......COMPLETED

        Configuring CSE_TOP.......COMPLETED

        Configuring EAM_TOP.......COMPLETED

        Configuring FTE_TOP.......COMPLETED

        Configuring ONT_TOP.......COMPLETED

        Configuring AR_TOP........COMPLETED

        Configuring AHL_TOP.......COMPLETED

        Configuring OZF_TOP.......COMPLETED

        Configuring IES_TOP.......COMPLETED

        Configuring CSD_TOP.......COMPLETED

        Configuring IGC_TOP.......COMPLETED

AutoConfig completed successfully.

bash-3.2$ sh adstrtal.sh apps/apps768prelive

AWGEBS2:

Setup the inst_top as AWGebs1 and run autoconfig.

cd $ADMIN_SCRIPTS_HOME

sh adstrtal.sh apps/appspassword

Log on to Oracle E-Business Suite Release 12 using the SYSADMIN account, and choose the System Administrator responsibility. Navigate to Profile > System, change the profile option ‘Concurrent: TM Transport Type' to ‘QUEUE', and verify that the transaction manager works across the Oracle RAC instance.

Navigate to Concurrent > Manager > Define screen, and set up the primary and secondary node names for transaction managers.

Configure Enterprise Manager

AWGEBSDB1:

bash-3.2$ emca -config dbcontrol db -repos create -cluster

STARTED EMCA at May 3, 2013 8:21:41 PM

EM Configuration Assistant, Version 11.2.0.0.2 Production

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:

Database unique name: AWGprod

Service name: AWGprod

Listener port number: 1521

Listener ORACLE_HOME [ /d01/app/11.2.0/grid ]:

Password for SYS user:

Password for DBSNMP user:

Password for SYSMAN user:

Cluster name: AWGebsdb

Email address for notifications (optional):

Outgoing Mail (SMTP) server for notifications (optional):

ASM ORACLE_HOME [ /d01/app/11.2.0/grid ]:

ASM port [ 1521 ]:

ASM username [ ASMSNMP ]:

ASM user password:  ra

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

You have specified the following settings

Database ORACLE_HOME ................ /d01/app/oracle/product/11.2.0/db_1

Database instance hostname ................ Listener ORACLE_HOME ................ /d01/app/11.2.0/grid

Listener port number ................ 1521

Cluster name ................ AWGebsdb

Database unique name ................ AWGprod

Email address for notifications ...............

Outgoing Mail (SMTP) server for notifications ...............

ASM ORACLE_HOME ................ /d01/app/11.2.0/grid

ASM port ................ 1521

ASM user role ................ SYSDBA

ASM username ................ ASMSNMP

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

Do you wish to continue? [yes(Y)/no(N)]: Y

INFO:

****************  Current Configuration  ****************

 INSTANCE            NODE           DBCONTROL_UPLOAD_HOST

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

AWGprod           AWGebsdb1           AWGebsdb1.earms.army.gov

AWGprod           AWGebsdb2           AWGebsdb1.earms.army.gov

Enterprise Manager configuration completed successfully

FINISHED EMCA at May 3, 2013 8:50:02 PM

bash-3.2$

AWGEBSDB2:

bash-3.2$ emca -reconfig dbcontrol -cluster -EM_NODE AWGebsdb2 -EM_SID_LIST AWGprod2

STARTED EMCA at May 3, 2013 8:58:28 PM

EM Configuration Assistant, Version 11.2.0.0.2 Production

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:

Database unique name: AWGprod

Service name: AWGprod

Agent Node list [comma separated] (optional):

Do you wish to continue? [yes(Y)/no(N)]: Y

May 3, 2013 8:58:42 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /d01/app/oracle/cfgtoollogs/emca/AWGprod/emca_2013_05_03_20_58_28.log.

May 3, 2013 8:58:47 PM oracle.sysman.emcp.util.DBControlUtil stopOMS

INFO: Stopping Database Control (this may take a while) ...

May 3, 2013 8:59:58 PM oracle.sysman.emcp.EMAgentConfig performDbcReconfiguration

INFO: Propagating /d01/app/oracle/product/11.2.0/db_1/AWGebsdb1_AWGprod/sysman/config/emd.properties,/d01/app/oracle/product/11.2.0/db_1/AWGebsdb2_AWGprod/sysman/config/emd.properties to remote nodes ...

May 3, 2013 8:59:59 PM oracle.sysman.emcp.util.DBControlUtil startOMS

INFO: Starting Database Control (this may take a while) ...

May 3, 2013 9:00:45 PM oracle.sysman.emcp.EMDBPostConfig performDbcReconfiguration

INFO: Database Control started successfully

May 3, 2013 9:00:45 PM oracle.sysman.emcp.EMDBPostConfig showClusterDBCAgentMessage

INFO:

****************  Current Configuration  ****************

 INSTANCE            NODE           DBCONTROL_UPLOAD_HOST

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

AWGprod           AWGebsdb1           AWGebsdb2.earms.army.gov

AWGprod           AWGebsdb2           AWGebsdb2.earms.army.gov

Enterprise Manager configuration completed successfully

RMAN Backup Scrips

########################RMAN Backup Script for PRELIVE  ################

echo "#################################################################################################"

echo "Start RMAN Database Full Backup on Date";date

ORACLE_HOME=/d01/app/oracle/product/11.2.0/db_1; export ORACLE_HOME

ORACLE_SID=AWGprod1; export ORACLE_SID

PATH=$PATH:$ORACLE_HOME/bin; export PATH

echo "Current database status";date

srvctl status database -d AWGprod

echo "Start Full Database Backup on";date

$ORACLE_HOME/bin/rman target / nocatalog <<EOF

configure encryption for database on;

set encryption on identified by 'earmsprod123rman';

CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

configure controlfile autobackup on;

configure controlfile autobackup format for device type disk to '/backupebsdb/AWGPROD_RMANBKP/%F';

run{

allocate channel c1 device type disk;

allocate channel c2 device type disk;

backup as compressed backupset format '/backupebsdb/AWGPROD_RMANBKP/AWGprod_df_%t_%s_%p.bak'(database);

sql 'alter system archive log current';

backup format '/backupebsdb/AWGPROD_RMANBKP/archive_%t_%s_%p.bak' archivelog all;

copy current controlfile to '/backupebsdb/AWGPROD_RMANBKP/AWGprod.ctl';

delete noprompt obsolete;

release channel c1;

release channel c2;

}

exit

EOF

echo "RMAN Backup Finished on";date

srvctl status database -d AWGprod