Test/Development Database
Refresh From Production Procedure
The following note describes the generic procedure to be followed to
refresh a Energy Test or Development environment database from a
Energy production database backup.
In this example, we are refreshing the DEVE72 database from a backup
taken of the PRD22 database.
The assumption here is that the required RMAN production backup has
already been either copied from the production database via scp or
has been restored from tape or has been placed in an NFS shared
location which is accessible from both machines.
On the target machine the backups have been restored in the location
u02/backup/DEVE72
Procedure:
[oracle@DEVE72
DEVE72]$ ps
-ef |grep pmon
oracle
12701 29275 0 15:36:00 pts/3 0:00 grep pmon
oracle
7377 2235 0 May 19 ? 84:59
ora_pmon_DEVE72
[oracle@DEVE72
DEVE72]$ echo
$ORACLE_SID
DEVE72
[oracle@DEVE72
DEVE72]$ sqlplus
sys as sysdba
SQL*Plus:
Release 10.2.0.4.0 - Production on Thu Jun 17 15:36:22 2010
Copyright
(c) 1982, 2007, Oracle. All Rights Reserved.
Enter
password:
Connected
to:
Oracle
Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With
the Partitioning, OLAP, Data Mining and Real Application Testing
options
SQL>
shutdown immediate
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
Note:
**Ensure we are connected to the right server and are in the
appropriate directory location**
[oracle@DEVE72
DEVE72]$ hostname
DEVE72
[oracle@DEVE72
DEVE72]$ pwd
/u03/oradata/DEVE72
[oracle@DEVE72
DEVE72]$ rm
*.dbf
[oracle@DEVE72
DEVE72]$ cd /u04/oradata/DEVE72
[oracle@DEVE72
DEVE72]$ ls
control2.ctl
redo01a.log redo02a.log redo03a.log
[oracle@DEVE72
DEVE72]$ rm
*.ctl
[oracle@DEVE72
DEVE72]$ rm
*.log
[oracle@DEVE72
DEVE72]$ cd
/u05/oradata/DEVE72
[oracle@DEVE72
DEVE72]$ ls
control3.ctl
redo01b.log redo02b.log redo03b.log
[oracle@DEVE72
~]$ cd
$ORACLE_HOME/dbs
[oracle@DEVE72
dbs]$ pwd
/u01/app/oracle/product/10.2.0/db_1/dbs
[oracle@DEVE72
dbs]$ cp
initDEVE72.ora initprd22.ora
*.db_name='prd22'
[oracle@DEVE72
dbs]$ export
ORACLE_SID=prd22
[oracle@DEVE72
dbs]$ sqlplus
sys as sysdba
SQL*Plus:
Release 10.2.0.4.0 - Production on Mon Jun 21 12:58:32 2010
Copyright
(c) 1982, 2007, Oracle. All Rights Reserved.
Enter
password:
Connected
to an idle instance.
SQL>
startup
nomount pfile=$ORACLE_HOME/dbs/initprd22.ora
ORACLE
instance started.
Total
System Global Area 3154116608 bytes
Fixed
Size 2043904 bytes
Variable
Size 637538304 bytes
Database
Buffers 2499805184 bytes
Redo
Buffers 14729216 bytes
SQL>
quit
The control file backup exists in the format
“c-<DBID>-<DATE>-<BACKUP SEQUENCE NUMBER>
Select the controlfile appropriate to the period of time that we wish
to restore the database from
[oracle@DEVE72
DEVE72]$ rman
target /
Recovery
Manager: Release 10.2.0.4.0 - Production on Fri Jun 18 11:05:20 2010
Copyright
(c) 1982, 2007, Oracle. All rights reserved.
connected
to target database: prd22 (not mounted)
RMAN>
restore
controlfile from '/u02/backup/DEVE72/c-4031762323-20100616-00';
Starting
restore at 18-JUN-10
using
target database control file instead of recovery catalog
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: sid=538 devtype=DISK
channel
ORA_DISK_1: restoring control file
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:06
output
filename=/u03/oradata/DEVE72/control1.ctl
output
filename=/u04/oradata/DEVE72/control2.ctl
output
filename=/u05/oradata/DEVE72/control3.ctl
Finished
restore at 18-JUN-10
RMAN>
alter
database mount
2>
;
database
mounted
released
channel: ORA_DISK_1
RMAN>
catalog
start with '/u02/backup/DEVE72';
searching
for all files that match the pattern /u02/backup/DEVE72
List
of Files Unknown to the Database
=====================================
File
Name: /u02/backup/DEVE72/c-4031762323-20100616-00
File
Name: /u02/backup/DEVE72/21lgatsk_1_1
File
Name: /u02/backup/DEVE72/c-2263349373-20100419-00
...
...
Do
you really want to catalog the above files (enter YES or NO)? YES
cataloging
files...
cataloging
done
List
of Cataloged Files
=======================
File
Name: /u02/backup/DEVE72/c-4031762323-20100616-00
File
Name: /u02/backup/DEVE72/21lgatsk_1_1
File
Name: /u02/backup/DEVE72/2flgdi89_1_1
...
...
List
of Files Which Where Not Cataloged
=======================================
File
Name: /u02/backup/DEVE72/c-2263349373-20100419-00
RMAN-07518:
Reason: Foreign database file DBID: 2263349373 Database Name: DEVE72
Note – ignore any errors reported for files that are not cataloged
Look
for the string "List of archived logs"
RMAN
> list
backup of archivelog all
List
of Archived Logs in backup set 69
Thrd
Seq Low SCN Low Time Next SCN Next Time
----
------- ---------- --------- ---------- ---------
1
79 7970987 16-JUN-10 7973402 16-JUN-10
In this case, the last archivelog backed up belongs to sequence
number 79. If we wish to recover the database until the last archived
log which has been backed up, we need to increment the last sequence
number by 1. So in this case it will be 79+1 or 80
vi
rman_head
RUN
{
#
allocate a channel to the tape device
ALLOCATE
CHANNEL d1 DEVICE TYPE disk;
#
rename the datafiles and online redo logs
vi
rman_tail
#
Do a SET UNTIL to prevent recovery of the online logs
SET
UNTIL SEQUENCE 80;
#
restore the database and switch the datafile names
RESTORE
DATABASE;
SWITCH
DATAFILE ALL;
#
recover the database
RECOVER
DATABASE;
}
vi
generate_datafiles.sql
set
head off pages 0 feed off echo off verify off
set
lines 200
spool
rename_datafiles.lst
select
'SET NEWNAME FOR DATAFILE ' || FILE# || ' TO ''' ||
'/u03/oradata/&1/' || substr(name,instr(name,'/',-1)+1) || ''';'
from v$datafile;
spool
off
exit;
vi
generate_logfiles.sql
set
head off pages 0 feed off echo off
spool
rename_logfiles.lst
SELECT
'SQL "ALTER DATABASE RENAME FILE '''''|| MEMBER ||''''''
||chr(10)||'to
''''' || member || '''''" ;' FROM V$LOGFILE;
exit
While in directory /u02/backup/{DB_NAME}, connect as sys as sysdba
via SQL*PLUS session and run the generate_datafiles.sql script.
The generate_datafiles.sql script accepts a parameter which is the
target database name.
SQL> @generate_datafiles DEVE72
It will create a file rename_datafiles.lst . The contents of this
file will be like this:
SET
NEWNAME FOR DATAFILE 1 TO '/u03/oradata/DEVE72/system01.dbf';
SET
NEWNAME FOR DATAFILE 2 TO '/u03/oradata/DEVE72/undotbs01.dbf';
SET
NEWNAME FOR DATAFILE 3 TO '/u03/oradata/DEVE72/sysaux01.dbf';
….
….
While in directory /u02/backup/{DB_NAME}, connect as sys as sysdba
via SQL*PLUS session and run the generate_logfiles.sql script.
SQL> @generate_logfiles.sql
It will create a file called rename_logfiles.lst
Edit the rename_logfiles.lst file and change values of prd22 to
DEVE72
SQL
"ALTER DATABASE RENAME FILE ''/u04/oradata/prd22/redo01a.log''
to ''/u04/oradata/DEVE72/redo01a.log''" ;
SQL
"ALTER DATABASE RENAME FILE ''/u05/oradata/prd22/redo01b.log''
to ''/u05/oradata/DEVE72/redo01b.log''" ;
SQL
"ALTER DATABASE RENAME FILE ''/u04/oradata/prd22/redo02a.log''
to ''/u04/oradata/DEVE72/redo02a.log''" ;
SQL
"ALTER DATABASE RENAME FILE ''/u05/oradata/prd22/redo02b.log''
to ''/u05/oradata/DEVE72/redo02b.log''" ;
SQL
"ALTER DATABASE RENAME FILE ''/u04/oradata/prd22/redo03a.log''
to ''/u04/oradata/DEVE72/redo03a.log''" ;
SQL
"ALTER DATABASE RENAME FILE ''/u05/oradata/prd22/redo03b.log''
to ''/u05/oradata/DEVE72/redo03b.log''" ;
Edit the rman_tail file and change the line with the words “>>
SET UNTIL SEQUENCE 80” to include the appropriate archive log
sequence which was noted in an earlier step.
[oracle@DEVE72
DEVE72]$ cat
rman_head rename_datafiles.lst rename_logfiles.lst rman_tail >
rman_recovery.rcv
[oracle@DEVE72
dbs]$ rman
target /
Recovery
Manager: Release 10.2.0.4.0 - Production on Mon Jun 21 13:04:04 2010
Copyright
(c) 1982, 2007, Oracle. All rights reserved.
connected
to target database (not started)
RMAN>
@rman_recovery.rcv
Note:
At this stage, we can continue to recover the database and keep it
in sync with the source production database by manually applying the
archive log files which are copied from the production server to the
log archive destination of the test database on the target server.
We can do this via SQL*PLUS connected as SYS by issuing the command
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
When there are no more archive log files to apply, we enter CANCEL
After the RMAN script has successfully run and recovered the database
until the last archive log sequence, we will now open the database
using the ALTER DATABASE OPEN RESETLOGS command executed either via
RMAN or from SQL*PLUS connected as SYS.
SQL>
alter
database open resetlogs;
Database
altered.
After the restore, we will note that the temporary tablespace files
are still pointing to the source production database as these
tempfiles have not been renamed when we renamed all the database data
files in an earlier step.
Obtain the name of the current tempfile -
SQL>
select
name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u03/oradata/prd22/temp01.dbf
Drop the tempfile-
SQL>
ALTER DATABASE TEMPFILE '/u03/oradata/prd22/temp01.dbf' drop
including datafiles;
Database
altered.
Add a new tempfile for the refreshed database in the appropriate
location-
SQL>
ALTER TABLESPACE temp ADD TEMPFILE '/u03/oradata/DEVE72/temp01.dbf'
size 2G;
Tablespace
altered.
We will now shutdown the database and then mount it.
We will then run the nid utility to change the database name –
we need to provide the appropriate password for the user SYS and the
new value we want for the database name.
[oracle@DEVE72
dbs]$ nid
target=sys dbname=DEVE72
DBNEWID:
Release 10.2.0.4.0 - Production on Fri Jun 18 13:55:14 2010
Copyright
(c) 1982, 2007, Oracle. All rights reserved.
Password:
Connected
to database PRD22 (DBID=4031762323)
Connected
to server version 10.2.0
Control
Files in database:
/u03/oradata/DEVE72/control1.ctl
/u04/oradata/DEVE72/control2.ctl
/u05/oradata/DEVE72/control3.ctl
Change
database ID and database name PRD22 to DEVE72? (Y/[N]) => Y
Proceeding
with operation
Changing
database ID from 4031762323 to 2271553224
Changing
database name from PRD22 to DEVE72
Control
File /u03/oradata/DEVE72/control1.ctl - modified
Control
File /u04/oradata/DEVE72/control2.ctl - modified
Control
File /u05/oradata/DEVE72/control3.ctl - modified
Datafile
/u03/oradata/DEVE72/system01.dbf - dbid changed, wrote new name
Datafile
/u03/oradata/DEVE72/undotbs01.dbf - dbid changed, wrote new name
Datafile
/u03/oradata/DEVE72/sysaux01.dbf - dbid changed, wrote new name
Datafile
/u03/oradata/DEVE72/users01.dbf - dbid changed, wrote new name
...
...
...
Datafile
/u03/oradata/DEVE72/COGNOSPAD_CLOB01.dbf - dbid changed, wrote new
name
Datafile
/u03/oradata/DEVE72/temp01.dbf - dbid changed, wrote new name
Control
File /u03/oradata/DEVE72/control1.ctl - dbid changed, wrote new name
Control
File /u04/oradata/DEVE72/control2.ctl - dbid changed, wrote new name
Control
File /u05/oradata/DEVE72/control3.ctl - dbid changed, wrote new name
Instance
shut down
Database
name changed to DEVE72.
Modify
parameter file and generate a new password file before restarting.
Database
ID for database DEVE72 changed to 2271553224.
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.
At this stage the database has been shutdown and now we need to mount
it and issue the RESETLOGS command after the database change.
Note:
We will now set the environment to the target database (until
this stage, for example, ORACLE_SID had been set to the production
database value)
[oracle@DEVE72
backup] export
ORACLE_SID=DEVE72
[oracle@DEVE72
backup]$ sqlplus
sys as sysdba
SQL*Plus:
Release 10.2.0.4.0 - Production on Mon Jun 21 14:12:00 2010
Copyright
(c) 1982, 2007, Oracle. All Rights Reserved.
Enter
password:
Connected
to an idle instance.
SQL>
startup
mount
ORACLE
instance started.
Total
System Global Area 3154116608 bytes
Fixed
Size 2043904 bytes
Variable
Size 637538304 bytes
Database
Buffers 2499805184 bytes
Redo
Buffers 14729216 bytes
SQL>
alter
database open resetlogs;
Database altered.
Create import and export directories required for Data Pump
/u02/export/{DB_NAME} - export_dir
/u02/import/{DB_NAME} - import_dir
RMAN – run the appropriate script to register details in the RMAN
catalog database where required.
