Refresh TEST/DEV Database from Production RMAN backup
In this post I will explain how to refresh test/dev
oracle database from production backup .
For Test/Development database refresh we will
prefer from valid production rman backup.
Here we taken valid RMAN
backup of PROD and want to restore the backup on TEST/DEV Instance as
below. We will ensure the backup of data file, spfile, archive log and control
file using rman..
Lets take two servers as
below:
SERVER1—PROD
SERVER2---ASR
ON SERVER1:
DB Name: PROD
DB Name: PROD
SQL> select name from
v$datafile;
NAME
--------------------------------------------------------------------------------
/data03/PROD/datafiles/system01.dbf
/data03/PROD/datafiles/system02.dbf
/data03/PROD/datafiles/system03.dbf
/data03/PROD/datafiles/system04.dbf
/data03/PROD/datafiles/system05.dbf
/data04/PROD/datafiles/ctxd01.dbf
/data04/PROD/datafiles/owad01.dbf
/data04/PROD/datafiles/a_queue02.dbf
/data04/PROD/datafiles/odm.dbf
/data04/PROD/datafiles/olap.dbf
/data04/PROD/datafiles/sysaux01.dbf
/data04/PROD/datafiles/apps_ts_tools01.dbf
/data04/PROD/datafiles/system122.dbf
/data04/PROD/datafiles/a_txn_data04.dbf
/data04/PROD/datafiles/a_txn_ind06.dbf
/data04/PROD/datafiles/a_ref03.dbf
/data04/PROD/datafiles/a_int02.dbf
/data04/PROD/datafiles/sysaux02.dbf
/data04/PROD/datafiles/olap01.dbf
/data04/PROD/datafiles/a_txn_data05.dbf
/data04/PROD/datafiles/a_txn_data06.dbf
/data04/PROD/datafiles/a_txn_ind07.dbf
–Take backup of PROD with
archivelog,controlfile and spfile.
Archivelog- from RMAN backup
Controlfile- from Rman Backup
Spfile—take pfile.
RMAN:
Spfile:
SQL> create pfile from
spfile;
[oraprod@tjpsb004 dbs]$ scp -r
initTJTG.ora oraascp@172.16.2.65:/uatdb/bkp
cd /uatdb/bkp
mv initTJTG.ora $ORACLE_HOME/dbs
[oraascp@TJPSB007
dbs]$ mv initASR.ora initTJTG.ora
Change db name from
ASR to TJTG
–Copy all backupsets from SERVER1(Location: /data1/bak/nsndb/) to SERVER2(Location:/u01/backup/nsndb/)
ON SERVER2:
–Create pfile for database nsndb.
set SID AS per RMAN backup(In RMAN it is TJTG).
So , set in .bash_profile as
TJTG.
DB Name: ASR #Change to Prod.
#export ORACLE_SID=TJTG
#rman target /
#rman target /
connected to target database (not started)
RMAN>startup nomount pfile='/uatdb/app/oraascp/db/11.2.0/dbs/initTJTG.ora';
Rman target /
connected to target database:
TJTG (not mounted)
RMAN> restore controlfile from '/uatdb/bkp/TJTG_Control_file976170784_43473_1';
RMAN>alter database mount;
database mounted
released channel: ORA_DISK_1
–Since backup location on
SERVER2 is different from SERVER1, so we have to use catalog command to
update new backup location in controlfile.
Use below command for all backuppieces:
update new backup location in controlfile.
Use below command for all backuppieces:
RMAN> catalog start with '/uatdb/bkp';
Since datafile location on ASR
is different from TJTG, So we have to update controlfile for same with SET
NEWNAME clause:
Now restore database backup on SERVER2:
Now restore database backup on SERVER2:
Run that restore_rman.sql
RMAN>
run
{
set newname for datafile '/data02/oradata/nsndb/system01.dbf' to '/u01/oradata/nsnbak/system01.dbf';
set newname for datafile '/data02/oradata/nsndb/undotbs01.dbf' to '/u01/oradata/nsnbak/undotbs1.dbf';
set newname for datafile '/data02/oradata/nsndb/sysaux01.dbf' to '/u01/oradata/nsnbak/sysaux01.dbf';
set newname for datafile '/data02/oradata/nsndb/users01.dbf' to '/u01/oradata/nsnbak/users01.dbf';
restore database;
switch datafile all;
}
RMAN>exit;
RUN
{
set until sequence 68030 thread 1;
recover database;
}
set until sequence 68030 thread 1;
recover database;
}
–Also update redo log file in
controlfile on SERVER2:
#sqlplus "/as sysdba"
SQL> select member from
v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oradb/oracle/product/TJTG/db/apps_st/data/log1a.dbf
/oradb_1/oracle/product/TJTG/db/apps_st/data/log1b.dbf
/oradb_2/oracle/product/TJTG/db/apps_st/data/log1c.dbf
/oradb/oracle/product/TJTG/db/apps_st/data/log2a.dbf
/oradb_1/oracle/product/TJTG/db/apps_st/data/log2b.dbf
/oradb_2/oracle/product/TJTG/db/apps_st/data/log2c.dbf
6 rows selected.
alter database rename file
'/oradb/oracle/product/TJTG/db/apps_st/data/log1a.dbf' to '/uatdb/app/oradata/log1a.dbf';
alter database rename file
'/oradb_1/oracle/product/TJTG/db/apps_st/data/log1b.dbf' to
'/uatdb/app/oradata/log1b.dbf';
alter database rename file
'/oradb_2/oracle/product/TJTG/db/apps_st/data/log1c.dbf' to
'/uatdb/app/oradata/log1c.dbf';
alter database rename file
'/oradb/oracle/product/TJTG/db/apps_st/data/log2a.dbf' to
'/uatdb/app/oradata/log2a.dbf';
alter database rename file
'/oradb_1/oracle/product/TJTG/db/apps_st/data/log2b.dbf' to
'/uatdb/app/oradata/log2b.dbf';
alter database rename file
'/oradb_2/oracle/product/TJTG/db/apps_st/data/log2c.dbf' to
'/uatdb/app/oradata/log2c.dbf';
>
- Open database using resetlogs:
SQL> alter database open resetlogs;
Rename Tempfile:
SQL> create TEMPORARY TABLESPACE TEMP3 TEMPFILE '/uatdb/app/oradata/temp002.dbf'
size 4000M;
create TEMPORARY TABLESPACE TEMP3 TEMPFILE
'/uatdb/app/oradata/temp002.dbf' size 4000M
*
ERROR at line 1:
ORA-01119: error in creating database file
'/uatdb/app/oradata/temp002.dbf'
ORA-27038: created file already exists
Additional information: 1
create TEMPORARY TABLESPACE TEMP9
TEMPFILE '/uatdb/app/oradata/temp09.dbf' size 4000M;
ALTER TABLESPACE TEMP9 ADD TEMPFILE '/uatdb/app/oradata/temp010.dbf' SIZE 5000m;
ALTER DATABASE DEFAULT TEMPORARY
TABLESPACE temp9;
drop tablespace temp1 including contents
and datafiles;
16.
> - Change database name using "nid" command
SQL> shut immediate
SQL> startup nomount pfile='/uatdb/app/oraascp/db/11.2.0/dbs/initTJTG.ora'
SQL> alter database mount;
SQL> select open_mode, name from v$database;
SQL> startup nomount pfile='/uatdb/app/oraascp/db/11.2.0/dbs/initTJTG.ora'
SQL> alter database mount;
SQL> select open_mode, name from v$database;
OPEN_MODE NAME
-------------------- ---------
MOUNTED TJTG
$ nid TARGET=sys DBNAME=ASR ----(Before Refreshing db , ASR was DB name)
Password: as sysdba
Password: as sysdba
Database name changed to ASR.
Modify parameter file and generate a new
password file before restarting.
Database ID for database ASR changed to
3724387482.
All previous backups and archived redo
logs for this database are unusable.
Database has been shutdown, open
database with RESETLOGS option.
Succesfully changed database name and
ID.
DBNEWID - Completed succesfully.
17.- Change ORACLE_SID in environment file and start database with reset logs:
cd u01/oraprod/db/tech_st/11.1.0
O_Home
[oraascp@TJPSB007 11.2.0]$ grep ORACLE_SID ASR_tjpsb007.env
O_Home
[oraascp@TJPSB007 11.2.0]$ grep ORACLE_SID ASR_tjpsb007.env
ORACLE_SID="ASR"
export ORACLE_SID
[oraprod@tos01 dbs]$ vi initTJTG.ora
[oraprod@tos01 dbs]$ vi initTJTG.ora
(change db name from TJTG TO new named
as ASR)
db_name = ASR
[oraprod@tos01 dbs mv initTJTG.ora initASR.ora
Now start with pfile initASR.ora
db_name = ASR
[oraprod@tos01 dbs mv initTJTG.ora initASR.ora
Now start with pfile initASR.ora
Source bash profile with Oracle_Sid as
new SID.
ORACLE_SID=ASR
export $ORACLE_SID
SQL> startup nomount pfile='/uatdb/app/oraascp/db/11.2.0/dbs/initASR.ora'
ORA-48108: invalid value given for the
diagnostic_dest init.ora parameter
ORA-48140: the specified ADR Base
directory does not exist [/ASCPPROD_DB/oracle/adr]
ORA-48187: specified directory does not
exist
Linux-x86_64 Error: 2: No such file or
directory
Additional information: 1
SQL> alter database mount;
alter database open resetlogs;
SQL> select open_mode, name from v$database;
OPEN_MODE NAME
-------------------- ---------
READ WRITE ASR
18.
> - Run autoconfig on database Tier:
cd $ORACLE_HOME/appsutil/scripts/ASR_tjpsb007
/uatdb/app/oraascp/db/11.2.0/appsutil/scripts/ASR_tjpsb007
sh adautocfg.sh
AutoConfig completed successfully.
CREATE SPFILE :
show parameter spfile;
pfile in use.
> create spfile from pfile;
>shut immediate
dbs]$ mv initASC.ora initASC.ora_bkp_lat
>startup
Success!!!!!!
DATABASE REFRESH IS COMPLETED.
NOW CONFIGURE FOR EBS.
[applascp@TJPSB008
bin]$ perl adcfgclone.pl appsTier
Log file located at
/uatapp/app/applascp/apps_st/comn/clone/bin/CloneConte
xt_0516085237.log
Provide the values required for creation of the new
APPL_TOP Context file.
Target Sytem Hostname (virtual or normal) [tjpsb008] :
Target System Database SID : ASR
Target System Database Server Node [tjpsb008] :
TJPSB007
Target System Database Domain Name [ebs.in] :
Target System Base Directory : /uatapp/app/applascp
Target System Tools ORACLE_HOME Directory
[/uatapp/app/applascp/apps/tech_st/10.1.2
] : /uatapp/app/applascp/tech_st/10.1.2
Target System Web ORACLE_HOME Directory
[/uatapp/app/applascp/apps/tech_st/10.1.3] :
/uatapp/app/applascp/tech_st/10.1.3
Target System APPL_TOP Directory
[/uatapp/app/applascp/apps/apps_st/appl] : /uatapp/app/applascp/apps_st/appl
Target System COMMON_TOP Directory
[/uatapp/app/applascp/apps/apps_st/comn] : /uatapp/app/applascp/apps_st/comn
Target System Instance Home Directory
[/uatapp/app/applascp/inst] : /uatapp/app/applascp/inst
Target System Root Service [enabled] :
Target System Web Entry Point Services [enabled] :
Target System Web Application Services [enabled] :
Target System Batch Processing Services [enabled] :
Target System Other Services [enabled] :
Do you want to preserve the Display [tjpsb005:1.0]
(y/n) : n
Target System Display [tjpsb008:0.0] :
RC-00217: Warning: Configuration home directory
(s_config_home) evaluates to /uatapp/app/applascp/inst/apps/ASR_tjpsb008. A
di
rectory with this name already exists and is not empty.
Do you want to continue (y/n) : y
Do you want the the target system to have the same
port values as the source system (y/n) [y] ? : n
Target System Port Pool [0-99] : 61
sysadmin
welcome123
SYSADMIN PASSWORD application side
CHECK WF FROM LOGIN APPLICATION
SERVICE AGENT
4. Verify the APPLCSF variable setting
s_applcsf variable in the context file and then run AutoConfig.
s_applcsf variable in the context file and then run AutoConfig.
Set the custom path:
1)In R12 go to
$INST_TOP/ora/10.1.2/forms/server
2)Make sure your CUSTOM_TOP is defined in default.env
/uatapp/app/applascp/apps_st/custom/TJTG/12.0.0
###Begin Customization
TJTG_TOP=/GSTAPP/app/appluat/apps_st/custom/TJTG/12.0.0
###End Customization
1.cd $APPL_TOP
vi customASR_tjpsb008.env
export
TJTG_TOP=/uatapp/app/applascp/apps_st/custom/TJTG/12.0.0
:wq
FNDCPASS apps/Jzave 0 Y system/tjpsebs11 SYSTEM APPLSYS apps
Running
autoconfig on database Tier:
/ORADB/app/orauat/11.2.0/appsutil/scripts/UAT_tjpsb007
sh adautocfg.sh
/ORADB/app/orauat/11.2.0/appsutil/scripts/UAT_tjpsb007
sh adautocfg.sh
-
Running autoconfig on application Tier:
/GSTAPP/app/appluat/inst/apps/UAT_tjpsb008/admin/scripts
/GSTAPP/app/appluat/inst/apps/UAT_tjpsb008/admin/scripts
<---Inst_top---------------------------------------------à
sh adautocfg.sh
sh adautocfg.sh
No comments:
Post a Comment