Copying Database in UNIX
Last month we have seen how to create a database on Unix platform using scripts. This month, we will discuss how to duplicate or copy an existing database on UNIX platform using the Operating System file copy.
The method involved is simple, we shutdown the database and copy all datafiles to the new location and rebuild the control file.
Lets take for this discussion a database named ORATEST to be copied to ORAPROD. The destination database can be on the same server or on a different server. ORAPROD database does not exist.
Control File Script
Before shutdown of the ORATEST database, we should generate the script to create the new control file. This can be done using the BACKUP CONTROLFILE TO TRACE command.
Set ORACLE_HOME and ORACLE_SID for the ORATEST database.
Invoke svrmgrl and connect internal.
SVRMGR> connect internal
Connected.
SVRMGR> alter database backup controlfile to trace resetlogs;
Statement processed.
SVRMGR>
Change directory to USER_DUMP_DEST (this variable is set in the init.ora/config.ora file)
Look for the latest trace file (ls -ltr). Now if you're not very confortable with the file name, let's rename it to cr_control.sql.
This file would be something similar to this...
Dump file /ora_dump/ORATEST/udump/ora_29745.trc
Oracle7 Server Release 7.3.3.4.1 - Production Release
With the distributed and parallel query options
PL/SQL Release 2.3.3.4.1 - Production
ORACLE_HOME = /ora_home/app/oracle/product/7.3.3
System name: HP-UX
... ... ... ...
*** SESSION ID:(26.7411) 1999.02.25.12.43.52.523
# The following commands will create a new control file and use it
# to open the database.
# The contents of online logs will be lost and all backups will
# be invalidated. Use this only if online logs are damaged.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORATEST" RESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 4
MAXDATAFILES 128
MAXINSTANCES 8
MAXLOGHISTORY 800
LOGFILE
GROUP 1 (
'/ora3/oradata/ORATEST/redo0101.log',
'/ora4/oradata/ORATEST/redo0102.log'
) SIZE 20M,
GROUP 2 (
'/ora3/oradata/ORATEST/redo0201.log',
'/ora4/oradata/ORATEST/redo0202.log'
) SIZE 20M,
GROUP 3 (
'/ora3/oradata/ORATEST/redo0301.log',
'/ora4/oradata/ORATEST/redo0302.log'
) SIZE 20M
DATAFILE
'/ora0/oradata/ORATEST/system01.dbf',
'/ora1/oradata/ORATEST/rbs01.dbf',
'/ora2/oradata/ORATEST/temp01.dbf',
'/ora5/oradata/ORATEST/tools.dbf',
'/ora6/oradata/ORATEST/user01.dbf',
'/ora5/oradata/ORATEST/data01.dbf',
'/ora6/oradata/ORATEST/index01.dbf'
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
# Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
Now shutdown (normal or immediate) the ORATEST database.
Copy files to new location
Copy the datafiles to new location. Let's copy the files to the same mount point, but under a different directory - copy from /ora/oradata/ORATEST to /ora/oradata/ORAPROD. We will also change the file name for two files.
cp /ora0/oradata/ORATEST/system01.dbf /ora0/oradata/ORAPROD/system01.dbf cp /ora1/oradata/ORATEST/rbs01.dbf /ora1/oradata/ORAPROD/rbs01.dbf cp /ora2/oradata/ORATEST/temp01.dbf /ora2/oradata/ORAPROD/temp01.dbf cp /ora5/oradata/ORATEST/tools.dbf /ora5/oradata/ORAPROD/tools.dbf cp /ora6/oradata/ORATEST/user01.dbf /ora6/oradata/ORAPROD/user01.dbf cp /ora5/oradata/ORATEST/data01.dbf /ora5/oradata/ORAPROD/userdata01.dbf cp /ora6/oradata/ORATEST/index01.dbf /ora6/oradata/ORAPROD/userindex01.dbf
Copy the init.ora and config.ora files
cp initORATEST.ora initORAPROD.ora cp configORATEST.ora configORAPROD.ora
(Note: you may use rcp to copy files to a different server)
Edit inti.ora and config.ora to reflect the new database name. You have to change CONTROL_FILES, BACKGROUND_DUMP_DEST, CORE_DUMP_DEST, DB_NAME, LOG_ARCHIVE_DEST, IFILE parameters.
Create new directories for BACKGROUND_DUMP_DEST, CORE_DUMP_DEST and USER_DUMP_DEST.
Copy the create control file script (cr_control.sql) to the new server or to a new "db create" directory if you have one.
Edit cr_control.sql. Remove the first few lines till SESSION ID. Change all occurrences of ORATEST to ORAPROD. Change the data file names and redolog file names to the new names. Change the keyword REUSE to SET in the CREATE line. After all changes, the new cr_control.sql file might look like this.
# The following commands will create a new control file and use it
# to open the database.
# The contents of online logs will be lost and all backups will
# be invalidated. Use this only if online logs are damaged.
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "ORAPROD" RESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 4
MAXDATAFILES 128
MAXINSTANCES 8
MAXLOGHISTORY 800
LOGFILE
GROUP 1 (
'/ora3/oradata/ORAPROD/redo0101.log',
'/ora4/oradata/ORAPROD/redo0102.log'
) SIZE 20M,
GROUP 2 (
'/ora3/oradata/ORAPROD/redo0201.log',
'/ora4/oradata/ORAPROD/redo0202.log'
) SIZE 20M,
GROUP 3 (
'/ora3/oradata/ORAPROD/redo0301.log',
'/ora4/oradata/ORAPROD/redo0302.log'
) SIZE 20M
DATAFILE
'/ora0/oradata/ORAPROD/system01.dbf',
'/ora1/oradata/ORAPROD/rbs01.dbf',
'/ora2/oradata/ORAPROD/temp01.dbf',
'/ora5/oradata/ORAPROD/tools.dbf',
'/ora6/oradata/ORAPROD/user01.dbf',
'/ora5/oradata/ORAPROD/userdata01.dbf',
'/ora6/oradata/ORAPROD/userindex01.dbf'
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
# RECOVER DATABASE USING BACKUP CONTROLFILE
# Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
Create New Database
Now we have copied all datafiles to the new location, changed the init.ora and config.ora files for the new database, mentioned the new control file names under CONTROL_FILES and made the script to create the new control file, we are ready to create the database.
Make sure the ORACLE_SID and ORACLE_HOME are set to the ORAPROD database. Invoke svrmgrl and connect internal. Run the cr_control.sql script. That's it... your new database is ready to use! Change the GLOBAL_NAME of the database to your new name.
ALTER DATABASE RENAME GLOBAL_NAME TO ORAPROD;
Add new entries to /etc/oratab or /var/opt/oracle/oratab and to listener.ora and tnsnames.ora files as appropriate.
Backup the newly created database.
 Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, Oracle8i OCP and Oracle9i OCA/OCP Certified DBA |
Questions/Comments? Write to webmaster@bijoos.com. © The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster. |