Enclosed is my work note about how to do database copy. Feel free to make your comments
Copying a Database
2.1 Create ctl_test.sql script for creating new database control file
Assume I want to copy Oracle database data030d to wtest030.
Always set up oracle environment first by doing this:
root@unixserver02>su – oracle
oracle@unixserver02>. /bin/oraenv data030d
Create trace file from data030d first:
oracle@ unixserver02>sqlplus system@dada030d
SQL>alter database backup controlfile to trace;
SQL>exit
ShUClown database data030d before I copy any file from it.
oracle@ unixserver02>. /bin/oraenv data030d
oracle@ unixserver02>svrmgrl
SVRMGR>connect internal
SVRMGR>shuclown immediate
SVRMGR>exit
The trace file was created as:
/eXPort/home/oracle/admin/data030d/udump/data030d_ora_11143.trc
Delete the header up to “START NOMOUNT” and make some modification as follows:
· Change
“CREATE CONTROLFILE REUSE DATABASE “DATA030D” NORESETLOGS NOARCHIVELOG”
to
“CREATE CONTROLFILE SET DATABASE “wtest030” RESETLOGS NOARCHIVELOG”
· Comment out
“RECOVER DATABASE”
· Change
“ALTER DATABASE OPEN;”
to
“ALTER DATABASE OPEN RESETLOGS”
· Substitute data030d to wtest030
Rename the file as ctl_test.sql, I can give it any extension rather than .sql
2.2 Make init_databasename.ora file available for database wtest030
Make /export/home/oracle/admin/wtest030 Directory for the new database. The easiest way to do this is to copy /export/home/oracle/admin/data030d drectory and remove anything except for pfile directory.
oracle@ unixserver02>cd /export/home/oracle/admin/data030d
oracle@ unixserver02>cp –r * ../wtest030
Rename init_databasename.ora file to initwtest030.ora and substitute data030d to wtest030
Since oracle only contact init_databasename.ora file at /export/home/oracle/product/8.1.6/dbs directory, I need to make a link for initwtest030.ora file.
oracle@ unixserver02>ln –s /export/home/oracle/admin/wtest030/pfile/initwtest030.ora initwtest030.ora
2.3 Add an entry to oratab file
I need to add an entry to /var/opt/oracle/oratab file:
wtest030: /export/home/oracle/product/8.1.6:Y:NOPVCS
while wtest030 is databse name, /export/home/oracle/product/8.1.6 is oracle home directory, Y means this database will be started automatically after using “ oradbstart start all”. NOPVCS means no PVCS set up for this database.
2.4 Modifying tnsnames.ora file
There is a link for tnsnames.ora in /var/opt/oracle pointed to:
/export/home/oracle/product/8.1.6/network/admin/tnsnames.ora
Make a copy for tnsnames.ora, then:
Add following lines to the file just as I do on PCs:
wtest030=
(DESCRIPTION=
(ADDRESS = (PROTOCOL= TCP) (Host= unixserver02.daye.com) (Port= 1521)
(CONNECT_DATA = (SID = wtest030))
2.5 Modifying listener.ora file, reload the listener
There is a link for listener.ora in /var/opt/oracle pointed to:
/export/home/oracle/product/8.1.6/network/admin/listener.ora
Make a copy for listener.ora, then:
Add following lines to the file:
(SID_DESC=
(GLOBAL_NAME=wtest030)
(GLOBAL_HOME= /export/home/oracle/product/8.1.6)
(SID_NAME = wtest030)
Since Oracle listener is already running, I only need to reload it:
oracle@ unixserver02>lsnrctl reload
If this command won’t work, try this:
oracle@ unixserver02> cd /export/home/oracle
oracle@ unixserver02>. /bin/oraenv wtest030
oracle@ unixserver02>lsnrctl status
I should find line like this:
Wtest030 has 1 service handler(s)
If not, do this:
oracle@ unixserver02>lsnrctl stop
oracle@ unixserver02>lsnrctl start
2.6 Copy database from data030d to wtest030
Assume data030d is only on /u02/oradata/data030d and the newly copied database is at:
/u02/oradata/wtest030.
I can copy the files by doing this:
oracle@ unixserver02>cd /u02/oradata
oracle@ unixserver02>mkdir wtest030
oracle@ unixserver02>cd /u02/oradata/data030d
oracle@ unixserver02>cp –r * ../wtest030
After that, all old control files need to be removed:
oracle@ unixserver02>rm /u02/oradata/wtest030/*.ctl
2.7 Create control files for new database wtest030 and more
Set up oracle environment for the new database.
oracle@ unixserver02>. /bin/oraenv wtest030
oracle@ unixserver02>cd /export/home/oracle/admin/data030d/udump
Run ctl_test.sql script to create control files for database wtest030
oracle@ unixserver02>svrmgrl
SVRMGR>connect internal
Note: Here I do not need to edacute command “startup nomount” because this line is already inside ctl_test.sql script
SVRMGR>@ctl_test.sql
Change global_name from xta030d to wtest030
SVRGMR>alter database rename global_name to wtest030;
Since only development database has sqlnav user, I need to drop sqlnav:
SVRMGR>drop user sqlnav cascade;
If I want to change passWord to daye for a user, say daye:
SVRMGR>alter user daye identified by daye;
Then shuclown and restart the database:
SVRMGR> shuclown normal
SVRMGR>exit
2.8 If I want to copy database from one server to another server
When I try to copy database from one server to another server, I need to finish the following steps. Suppose I want to copy database clta030 from server unixserver01 to database clta030r on server unixserver02.
2.8.1. Share the directories on which databases reside
Do this as superuser.
Since clta030 is on /u01 and /u02, I run this command:
root@ unixserver01>share /u01
root@ unixserver01>share /u02
2.8.2. Mount the shared directories on the destination server to the meaningful mount points on the destination sever
Do this as superuser
root@ unixserver02>cd /
root@ unixserver02>mkdir unixserver01_u01
root@ unixserver02>mkdir unixserver01_u02
root@ unixserver02>mount unixserver01:/u01 /unixserver01_u01
root@ unixserver02>mount unixserver01:/u02 /unixserver01_u02
2.8.3. Build up database copy script and double check
Before I copy the database, I need to check if there is enough space on the related file systems of the destination server.
root@ unixserver02>df –k
There are also file systems named /u01 and /u02 on unixserver02, I want to copy the database to them.
oracle@ unixserver02>cd /u01/oradata
oracle@ unixserver02>mkdir clta030r
oracle@ unixserver02>cd /u02/oradata
oracle@ unixserver02>mkdir clta030r
The database copy script is: dbcopy_clta030r
#!
/bin/kshPrint “Removing the original data in clta030r”rm –f /u01/oradata/clta030r/*rm –f /u02/oradata/clta030r/*Print “Copy from unixserver01”cp /unixserver01_u01/oradata/clta030/* /u01/oradata/clta030rcp /unixserver02_u02/oradata/clta030/* /u02/oradata/clta030rPrint “Remove the original control file”rm –f /u01/oradata/clta030r/*.ctlrm –f /u02/oradata/clta030r/*.ctl
2.8.4. Create ctl_test.sql script for creating new database control file
2.8.5. Create admin directory
Every database has an admin directory. If the database SID is clta030, then the directory is “/export/home/oracle/admin/clta030”. Inside it, there are following subdirectories: audit, export, create, bdump, cdump, udump and pfile. I can copy these sub directories or create them myself.
If I want to copy these sub directories, I need to copy the whole admin directory of clta030 from server unixserver01 to database clta030r on server unixserver02.
Share /export/home on unixserver01, create a mount point unixserver01_export_home on unixserver02 and mount /export/home of unixserver01.
root@ unixserver02>mount unixserver01:/export/home /unixserver01_export_home
oracle@ unixserver02>cd /unixserver01_export_home/oracle/admin/clta030
oracle@ unixserver02>cp –r * /export/home/oracle/admin/clta030r
Make init_databasename.ora file available for database clta030r
First change initclta030.ora to initclta030r.ora. Substitute clta030 by clta030r, then make a link in /export/home/oracle/product/8.1.6/dbs
oracle@ unixserver02>cd /export/home/oracle/product/8.1.6/dbs
oracle@ unixserver02>ln –s /export/home/oracle/admin/wtest030/pfile/initclta030r.ora initclta030r.ora
2.8.6. Add an entry to oratab file
2.8.7. Modifying tnsnames.ora file
2.8.8. Modifying listener.ora file, reload the listener
2.8.9. Shuclown source database then begin to copy it and remove old control files
oracle@ unixserver01>su – oracle
oracle@ unixserver01>. /bin/oraenv clta030
oracle@
unixserver01>svrmgrl
SVRMGR>connect internal
SVRMGR>shuclown immediate
SVRMGR>exit
oracle@ unixserver02>./dbcopy_clta030r
2.8.10. Create new control files for new database clta030r
Set up oracle environment for the new database.
oracle@ unixserver02>. /bin/oraenv clta030r
oracle@ unixserver02>cd /export/home/oracle/admin
Run ctl.sql script to create control files for database clta030r
oracle@ unixserver02>svrmgrl
SVRMGR>connect internal
Note: Here I do not need to edacute command “startup nomount” because this line is already inside ctl.sql script
SVRMGR>@ctl.sql
Note: script ctl.sql was generated from clta030 on unixserver01.
2.8.11. Change global name for new database
Change global_name from clta030 to clta030r
SVRGMR>alter database rename global_name to clta030r;
Since only development database has sqlnav user, I need to drop sqlnav:
SVRMGR>drop user sqlnav cascade;
If I want to change password to daye for a user, say daye:
SVRMGR>alter user daye identified by daye;
Then shuclown and restart the database:
SVRMGR> shuclown normal
SVRMGR>exit
2.8.12. Unmount the shared directories on the destination server
root@ unixserver02> umount /unixserver01_u01
root@ unixserver02> umount /unixserver01_u02
2.8.13. Unshare the shared directories on the source serve
root@ unixserver01>unshare /u01
root@ unixserver01>unshare /u02
2.8.14. Bring up the source database
oracle@ unixserver01>. /bin/oraenv clta030
oracle@ unixserver01>echo $ORACLE_SID
oracle@ unixserver01>svrmgrl
SVRMGR>connect internal
SVRMGR>startup
SVRMGR>exit
2.8.15. Clean up user daye registry
Log on to clta030r by user daye. Run cleardaregs.sql
SQL>@/export/home/oracle/admin/scripts/dbcopy/cleardaregs.sql
It will display “12”, then type”/”.