|
Category :
Database
Resources -> Backup and Recovery
DB Version
:
Oracle 8 / 8i / 9i
OS Details :
Sun Solaris9
If
you have a separate database environment for development and testing,
then chances are you will be asked to refresh those database environments from
production. This method of database refresh as suggested in this
article adopts the standard backup
and recovery procedures. For this method to work, we need an offline
or an online full database backup of the production database.
Steps:
(1) Restore the full production database backup on the machine
where the database is being refreshed to. It is assumed that the file
system directory structures on the test / development machine matches
with production.
(2) On the test / development machine, startup and open the
restored database. Make sure the ORACLE_SID is set to be the same name
as the production database. After the database comes up, connect as sysdba and create a controlfile trace. For
example:
SQL> connect / as sysdba
SQL> alter database backup controlfile to trace
resetlogs;
A trace file will be generated in the user_dump_dest directory. Rename
this trace file to prodcontrol.sql and save it.
(3) Copy the init.ora file (e.g initPRODDB.ora) from the
production database to the $ORACLE_HOME/dbs directory on the Test or
development machine. This assumes that all database parameters are
specified in only the init.ora file.
(4) Rename the initPRODDB.ora to initDEVDB.ora on the Test /
development machine.
(5) Edit the initDEVDB.ora and make the following
modifications:
a. control_files = <'directory_location_on_dev_box/cntrlDEVDB.dbf
b. Set db_name to DEVDB
c. Disable archive logging since
you really don't need any recovery to be done to your
development or QA databases.
d. %dump_dest parameters should be changed to point to
suitable directories
e. Disable remote login passwordfile.
(6) Ensure that the shell environment is set correctly. At the
very least, the following environment variables must be defined:
a. ORACLE_HOME
b. ORACLE_SID should be set to DEVDB
(7) Connect as a privileged user. For example:
SQL> connect / as sysdba
(8) Start the instance, after setting ORACLE_SID to DEVDB :
SQL> startup nomount
(9) The prodcontrol.sql file (created in Step 2) contains the
syntax necessary to
create a new controlfile. Edit this file and modify the following:
- Remove the REUSE keyword and replace it
with SET
- Optionally change the ARCHIVELOG keyword
to NOARCHIVELOG (so the
DEVDB database will not operate
in archivelog mode)
An example of the completed prodcontrol.sql script might be:
CREATE CONTROLFILE SET DATABASE "DEVDB" RESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 4
MAXDATAFILES 200
MAXINSTANCES 1
MAXLOGHISTORY 400
LOGFILE
GROUP 1 '/oradata/restore/logs1PRODDB.dbf'
SIZE 10M,
GROUP 2 '/oradata/restore/logs2PRODDB.dbf'
SIZE 10M
DATAFILE
'/oradata/sys/sysPRODDB.dbf',
'/oradata/rbs/rbsPRODDB.dbf',
'/oradata/temp/tempPRODDB.dbf',
'/oradata/userdata/usersPRODDB.dbf';
Remove any additional lines that you might find within the file except
for the ones that might be there to recreate the tempfiles and then execute prodcontrol.sql script to create control file(s).
(10) Open the database with the resetlogs option:
SQL> alter database open resetlogs;
(11) Make sure the passwords in the development database are
reset and the database links are pointing to the correct locations
since this database was a copy from production.
(12) Shutdown the database and take a full offline backup
before releasing it to the development team.
|
