Refresh Database

STEP BY STEP PROCESS TO REFRESH DATABASE FROM BSCSRAJ (193.0.4.110) TO PREFINAL (193.0.4.173) ***************************************************************************** • • •

export ORACLE_SID=BSCSHAR startup recover database ; (if required)

alter database open ;

PREREQUISITES:  Check for duplicate datafile name select count(*) ,substr(file_name,instr(file_name,'/',-1)) substr(file_name,instr(file_name,'/',-1)) having count(*) > 1 ;

file_name

from

dba_data_files

group

by

select substr(file_name,instr(file_name,'/',-1)) file_name from dba_data_files

group by substr(file_name,instr(file_name,'/',-1)) having count(*) > 1 ; Step 1) Prepare rcp scripts for DATAFILE BSCSRAJ 193.0.4.110 select   'rcp   '||   file_name   ||   '   [email protected]:/preupefinal/PREFINAL/oradata' substr(file_name,instr(file_name,'/',­1)) from dba_data_files

 

|| 

 

|| 

Step 2) Prepare rcp scripts for CONTROLFILE BSCSRAJ 193.0.4.110 select   'rcp   '||   name   ||     '   substr(name,instr(name,'/',­1)) from v$controlfile

[email protected]:/preupefinal/PREFINAL/control'

Step 3) Prepare rcp scripts for LOGFILE BSCSRAJ 193.0.4.110 select   'rcp   '||   member   ||     ' substr(member,instr(member,'/',­1)) from v$logfile

 

[email protected]:/preupefinal/PREFINAL/redo'

 

|| 

Step 4) Prepare script to rename DATAFILE to be run on PREFINAL (193.0.4.173) select 'alter database rename file '||''''||file_name||''''||' to '||''''||'/preupefinal/PREFINAL/oradata'|| substr(file_name,instr(file_name,'/',­1))||''''||' ; ' from dba_data_files

Step 5) Prepare script to rename LOGFILE to be run on PREFINAL (193.0.4.173) select 'alter database rename file '||''''||member||''''||' to '||''''||'/preupefinal/PREFINAL/redo'|| substr(member,instr(member,'/',­1))||''''||' ; ' from v$logfile

Step 6) Shutdown the database. Step 7) Run the script prepared at step 1. Step 8) Run the script prepared at step 2. Step 9) Run the script prepared at step 3.

Login to Destination Server • • • • •

Prepare init file for PREFINAL export ORACLE_SID=BSCSRAJ rename control file startup no mount Check control file new location. (show parameter dump)

•

startup mount

Step 10) Run the script prepared at step 4. Step 11) Run the script prepared at step 5. Step 12) ALTER DATABASE BACKUP CONTROLFILE TO TRACE; Step 13) shutdown immediate; Start mount Alter database noarchivelog; Alter database open; ALTER DATABASE BACKUP CONTROLFILE TO TRACE; Shutdown immediate; Step 14) Open UDUMP Rename trace file to renameDB.sql Step 15) Open renameDB.sql and change REUSE to SET Step 16) export ORACLE_SID=PREFINAL Step 17) STARTUP NOMOUNT Step 18) start renameDB.sql Step 19) ALTER DATABASE OPEN RESETLOGS ; Alter all users with same password and lock all the users Drop all db_links Create or add temp tablespace

NOW ENJOY & PARTY…….. NEW DATABASE IS READY.