Cloning
Read this entire document before cloning
Brief outline of cloning
Typically you need to do the following when cloning over the top of an existing database:
1. shutdown PPRD and PROD
2. run a com file that deletes the old PPRD files and copies prod datafile to pprd‟s area
copy_prod.com
3. Make sure you are poinitng to the PPRD database
go into svrmgrl
connect internal
startup nomount
run a create_control.sql files to create PPRD‟s control file
4. Change database name and passwords in PPRD
5. Startup PROD
VMS cloning in a nutshell:
Cloning a Database - OVMS
{NOTE: This feature is NOT supported by ORACLE}
1. Compile a listing of all datafiles and redo log file associated with PROD.
2. Shutdown the PROD database. DO NOT use shutdown abort.
3. Copy all of PROD's datafiles and redo log files to another location to use
for your new database. The new names should reflect the name of your new
database. DO NOT copy the control files. Create a com file for repeat use.
=============== CUT HERE ======================================
$! COPY_PROD.COM
$!
$ set rms/extend=50000
$!
$! delete previous PPRD data files:
$1
$ del/conf DRA0:[ORADATA.PPRD]*PPRD*.*;*
$!
$! copy all log file
$!
$ copy DRA0:[ORADATA.PROD]REDO_PROD_1A.RDO -
DRA0:[ORADATA.PPRD]REDO_PPRD_1A.RDO
$!
$! copy all data files
$!
$ copy DRA3:[ORADATA.PROD]DEVL_PROD_01.DBF -
DRA0:[ORADATA.PPRD]DEVL_PPRD_01.DBS
=============== CUT HERE ======================================
4. Go into Server Manager, database nomount, create the control files.
$ svrmgrl lmode=y
Last printed 12/1/2011 10:38:00 AM 1
SVRMGRL> connect internal
SVRMGRL> startup nomount
SVRMGRL> @create_ctrl
=============== CUT HERE ======================================
rem CREATE_CTRL.sql
CREATE CONTROLFILE
SET DATABASE PPRD
LOGFILE GROUP 1 ('DRA0:[ORADATA.PPRD]REDO_PPRD_1A.RDO',
'DRA1:[ORADATA.PPRD]REDO_PPRD_1B.RDO') SIZE 10M,
GROUP 2 ('DRA1:[ORADATA.PPRD]REDO_PPRD_2A.RDO',
'DRA2:[ORADATA.PPRD]REDO_PPRD_2B.RDO') SIZE 10M,
GROUP 3 ('DRA2:[ORADATA.PPRD]REDO_PPRD_3A.RDO',
'DRA3:[ORADATA.PPRD]REDO_PPRD_3B.RDO') SIZE 10M,
GROUP 4 ('DRA3:[ORADATA.PPRD]REDO_PPRD_4A.RDO',
'DRA0:[ORADATA.PPRD]REDO_PPRD_4B.RDO') SIZE 10M
RESETLOGS
DATAFILE 'DRA3:[ORADATA.PPRD]DEVL_PPRD_01.DBF',
'DRA3:[ORADATA.PPRD]RBS_PPRD_01.DBF',
'DRA3:[ORADATA.PPRD]SYST_PPRD_01.DBF',
'DRA1:[ORADATA.PPRD]TEMP_PPRD_01.DBF',
'DRA3:[ORADATA.PPRD]TOOL_PPRD_01.DBF',
'DRA3:[ORADATA.PPRD]USER_PPRD_01.DBF',
'DRA3:[ORADATA.PPRD]USER_PPRD_02.DBF';
=============== CUT HERE ======================================
5. After you see the response "statement processed" you then:
database resetting logs; change the internal database name.
SVRMGRL> alter database open resetlogs;
SVRMGRL> alter database rename global_name to PPRD;
SVRMGRL> select * from global_name;
SVRMGRL> select * from v$parameter where name='db_name';
6. Run the mod_pword.sql script to change passwords and the value
of the instance name in GUBINST table.
Sample mod_pword.sql script
MOD_PWORD.SQL
spool mod_pwrd
rem
alter user BANINST1 identified by &&baninst1_pword;
rem
alter user BANSECR identified by &&bansecr_pword;
rem
alter user FAISMGR identified by &&owner_pword;
alter user FAISPRD identified by &&owner_pword;
alter user FAISUSR identified by &&owner_pword;
alter user FIMSMGR identified by &&owner_pword;
alter user FIMSPRD identified by &&owner_pword;
alter user FIMSUSR identified by &&owner_pword;
Last printed 12/1/2011 10:38:00 AM 2
alter user GENERAL identified by &&owner_pword;
alter user GENLPRD identified by &&owner_pword;
alter user HRISPRD identified by &&owner_pword;
alter user HRISUSR identified by &&owner_pword;
alter user PAYROLL identified by &&owner_pword;
alter user POSNCTL identified by &&owner_pword;
alter user POSNPRD identified by &&owner_pword;
alter user SAISPRD identified by &&owner_pword;
alter user SAISUSR identified by &&owner_pword;
alter user SATURN identified by &&owner_pword;
alter user TAISMGR identified by &&owner_pword;
alter user UPGRADE1 identified by &&owner_pword;
alter user WTAILOR identified by &&owner_pword;
rem
alter user WWW_USER identified by &&web_pword;
rem
rem web users
rem
rem alter user JOBSUB identified by &&jsub_pword;
rem
update gubinst set GUBINST_INSTANCE_NAME='PPRD';
update gubinst set GUBINST_NAME='PPRD, cloned &&date';
exit
7. Reset the ORACLE_SID to PROD and startup the PROD instance
TESC cloning Instructions:
Cloning PROD to PPRD
These instructions are for a PROD to PPRD clone. (this section of instructions will suffice for a clone
from prod to test or prod to trng. (substitued pprd for specific instance.)
Instructions for clone of R25p to r25t are toward the end of this document.
Kill dm_extract off of datamart before beginning export of prod and shutting down db for clone.
Dm_extract runs 4:30 am to 6am.
Notify the following users of the upcoming clone:
Banner BRAT ST DL
Student Systems Steering Committee DL
1
A Clone from PROD to PPRD is the example.
A. Verify that backups have run successfully the night before.
1
If you find the need to panic first do nothing. Don’t make any changes. Walk away & take a short 10
min. break , and then maybe talk to someone. After taking a short break, and talking to someone it is then
ok to panic. Most big mistakes are made within the 1st 10 min. of making a little mistake. Often you can
recover from the little mistakes more easily if you don’t panic right away.
Last printed 12/1/2011 10:38:00 AM 3
1.Log on to admin node and type the following :
@ UTIL$MISC:check_backups full
This will take you through the process of backup verification.
1a. If you are unsure of the backups it never hurts to do a full system export of both the
instances you are working with BEFORE starting the copy process. That way if
something goes haywire you can restore from an export quickly, or if a table wasn‟t saved
from the instance being overwritten, then you can recover the table if needed.
It is strongly recommended that there be a full system export (buffer size: 20480) of the
R25P, PROD database every evening.
B. Compile and Compare a list of all data files and redo log files associated with
PROD and PPRD.
You can Compare the list of redo log files and data files with the
copy.com file by running database_layout.sql in
DRB0:[SCT.WORK.LOCAL.DBATOOLS] against the database. (have to do this
while the DB is still up and running.) this script gives info only. ok to run any time.
The output from database_layout will be called database_layout.lis. We are
typically renaming these after they are finished to either _layout.lis or
database_layout.. These files are CURRENTL being stored in the
following directory DRB0:[SCT.WORK.LOCAL.DBATOOLS]
Verify that the redo log files (.rdo),and the data files (.dbs), are the same ones
referenced in the copy.com and .sql scripts listed below. And that
nothing has been added or changed. Do this prior to each clone.
Add process here to make a note of db links:
From DBA STUDIO (gui) utility using net8, should check dblinks in PPRD prior to shutting down and
cloning to PPRD
C. Shutdown PPRD and PROD DB instances
Note: Start up and shut down instances using the oracle user only.
3. Log on as oracle
Note: 3a.When shutting down the databases ensure that there are no users left in
the database by executing the following command from SVRMGRL
SVRMGRL> Select username, sid, serial# from v$session;
You will always see 6-7 processes without a username, NEVER remove these, as
these are the database‟s back ground processes. (You may see sys or sytem, that is
you logged on to look, and/or to run an export) If you need to remove other users, ie:
job submission (this is where you may want to run gurstop(3b)) or users that have
come in early, use the following command:
Last printed 12/1/2011 10:38:00 AM 4
SVRMGRL> alter system kill session ‟25,99‟ ;
Where 25=sid and 99 = serial# of the username that you want to kill.
3b. Run gurstop if needed (vms part) (repeat this for each instance )
PPRD, svrmgrl, connect internal, @gen$plus:gurstop (run gurstop before
shutting down the db if there are job submission jobs in db)
Dave has a job called DBSTAT that is scheduled to run every hour
during production hours. If you wish you can delete the entry for the job
from the queue on tesc05
Specify the instance name: e.g. PPRD
Type „svrmgrl‟
Type „connect internal‟
Note: There are 3 ways to shut down an instance
1. Shutdown – waits for the users to disconnect, and then shuts down
2. Shutdown Immediate -- kicks off users and shuts down.
3. Shutdown Abort -- (to be used only as a last resort)
Type „shutdown immediate‟
Repeat these steps again to shut down another instance.
To avoid the following error (during startup, covered later in this document )ORA-
07690: smscre: $CRMPSC_GDZRO_64 failure %SYSTEM-F-GPTFULL, global
page table is full you will want to shut down all the instances in order, and bring them up
in the appropriate order...
The current order as of NOV 2001 is: PROD,R25P,R25T,TEST,PPRD,TRNG
LOGON AS ORACLE, SPECIFY INSTANCE,SVRMGRL,CONNECT INTERNAL,
(check sid,serial#,useraname) SHUTDOWN IMMEDIATE, EXIT.
D. Set the location of the database home:
Set the instance to PPRD by typing „PPRD‟, this points to the test instance and
runs banlogic.com.
Type SET DEFAULT ORA_DB -- sets the location of the database home.
The database home is where the clone scripts are kept.
E. Run a com file that deletes the old PPRD files and copies prod datafiles to
pprd’s area (VMS part).
Submit copy_prod.com using the following command:
submit/notify/noprint/log=DRB0:[ORACLE.DB_PPRD]COPY_PROD.log
COPY_PROD
Set „verify on‟ in copy_prod.com if you are having a problem
Last printed 12/1/2011 10:38:00 AM 5
This com file can take anywhere from 1 – 2 hours to run depending on the size of
the DB. This is the lengthy part of the clone.
You can monitor progress by typing „DIR USER2:[ORADATA.PPRD]/SIZE’
and looking at the files copied (comparing this with the list of files to be copied in
copy_prod.com. If a file has a size of 0, it is most likely the current file being
copied.
F. Create PPRD’s control file (oracle part).
Make sure you are pointing to the PPRD database
(You can do this by typing one of the following: „ SHOW LOGICAL ORA_DB‟ or
„SHOW LOGICAL ORA_SID‟)
Type SVRMGRL, connect internal, STARTUP NOMOUNT
Then run the create_ctrl.sql file to create PPRD‟s control file with this command
@create_ctrl. This file will create the 2 control files for PPRD, will then open the
database with the resetlogs option, and will change the internal name of the
database from PROD (which was brought over by the clone) to PPRD. The
script will also select from various views to show that the database‟s name has
been changed.
This is a good place to change the system password.
svrmgrl, connect internal, alter user SYSTEM identified by .
SYS “” “”
G. Change GUAINST database name and passwords in PPRD(cannot run this
inside svrmgrl)
Run mod_pword.sql with the command @mod_pword.sql
Then enter passwords as prompted based on our established naming
conventions
This changes the instance name from PROD to PPRD also.
you will be prompted for a date, use this format: [DD-MON-YY]
H. TESC (institution specific) PART (see migrate_wtailor.doc)
Update twgbwrul
Update local mods for web products if needed.
I. Startup PROD
At this point you will be ready to start up PROD, and PPRD
To avoid the following error: ORA-07690: smscre: $CRMPSC_GDZRO_64 failure
%SYSTEM-F-GPTFULL, global page table is full you will want to shut down all the
instances in order, and bring them up in the appropriate order...
The current order as of NOV 2001 is: PROD,R25P,R25T,TEST,PPRD,TRNG
SVRMGRL, SPECIFY INSTANCE,CONNECT INTERNAL, STARTUP
Last printed 12/1/2011 10:38:00 AM 6
J. GURJOB and SLEEPWAKE Run if needed (vms part) using the following
examples for PROD and PPRD: always be sure to run the .com to stop these processes
before running the .com to start them... it makes a difference.
To check the queue type ‘ show queue/all/full sys$batch02’.
The following can be found in sys$sysdevice:[banjob.startup]
@gurjobstops.com
@gurjobstarts.com
@sleepwake_stops.com
@sleepwake_starts.com
db links go into db studio, check db links prior to cloning.
remember datamart pwd scripts.
To be done following clone of prod to pprd
1. Run the script to change the passwords and the value of the instance name (see section G
of the cloning document)
2. Verify passwords have been changed.
If the table TWGBWRUL is empty run this insert script and change
the twgbwrul_cgibin_dir depending on the instance. If the table
is populate with the values similar to the insert script below,
then run the update script with the appropriate
twgbwrul_cgibin_dir.
INSERT INTO TWGBWRUL
VALUES ('999','//owa','MON DD, YYYY','HH:MI','5',SYSDATE,'Stop
Sign','Caution','BannerSecured_fr','Y','Exit','999',NULL,'N',NULL,'N','Y','N','amenu.P_M
ainMnu','Y','MM/DD/YYYY')
/
OR
UPDATE TWGBWRUL
SET TWGBWRUL_CGIBIN_DIR = '/pprd/owa'
3. WHERE TWGBWRUL_CGIBIN_DIR = '/prod/owa';
4. Run set all pins in PPRD to ‘999999’ in the gobtpac table with the following script:
5. UPDATE gobtpac SET gobtpac_pin = ‘999999’ WHERE gobtpac_pin is NOT NULL;
6. Add process to change passwords for db links from DBA STUDIO (gui) utility using net8, should
check dblinks in PPRD prior to shutting down and cloning to PPRD
7. Go to http://banweb.evergreen.edu:8888/ log in and stop and start the banweb server.
8. Change the back Link URL from [http://www.evergreen.edu/…..etc] to
[http://banweb.evergreen.edu:7778/]
Last printed 12/1/2011 10:38:00 AM 7
Change reference of back link in pprd from http://www.evergreen.edu/gateway (or)
http://www.evergreen.edu/registration to http://banweb.evergreen.edu:7778/
Log on as wtailor to PPRD and run the following script (or something like it) to get the
records to be changed
select * from wtailor.twgbwmnu t
where twgbwmnu_back_url like '%http%'
TWGBWMNU_NAME TWGBWMNU_DESC TWGBWMNU_PAGE_TITLE TWGBWMNU_BACK_URL
HWSRCTLG Course Catalog: Course Catalog: http://www.evergreen.edu/gateway
hwskalog.P_AdmsMnuNon Admissions SCT WWW Information System http://www.evergreen.edu/gateway/
hwskalog.P_DispLoginNonAdmissions LoginAdmissions Login http://www.evergreen.edu/gateway/
hwskalog.P_DispLogoutNonAdmissions LogoutAdmissions Logout http://www.evergreen.edu/gateway/
hwskalog.P_DispPinLockedRecord Locked Record Locked http://www.evergreen.edu/gateway/
HWSRSCHD Class Schedule:Class Schedule: http://www.evergreen.edu/gateway/
amenu.P_MainMnu Main Menu Main Menu http://www.evergreen.edu/gateway/
twgkwbis.P_Logout General - User Logout User Logout http://www.evergreen.edu/gateway/
HWPREDIR Employee DirectoryEmployee Directoryhttp://www.evergreen.edu/registration/
HWPREDIR_INDEX Directory Menu Directory Menu http://www.evergreen.edu/registration/
Run the following script to make the changes following :
Update twgbwmnu
Set twgbwmnu_back_url = ‘http://banweb.evergreen.edu:7778/’
Where twgbwmnu_back_url like ‘%http%’;
TWGBWMNU_NAME TWGBWMNU_DESC TWGBWMNU_PAGE_TITLE TWGBWMNU_BACK_URL
HWSRCTLG Course Catalog:Course Catalog: http://banweb.evergreen.edu:7778/
hwskalog.P_AdmsMnuNon AdmissionsSCT WWW Information System http://banweb.evergreen.edu:7778/
hwskalog.P_DispLoginNonAdmissions LoginAdmissions Login http://banweb.evergreen.edu:7778/
hwskalog.P_DispLogoutNonAdmissions LogoutAdmissions Logout http://banweb.evergreen.edu:7778/
hwskalog.P_DispPinLockedRecord Locked Record Locked http://banweb.evergreen.edu:7778/
HWSRSCHD Class Schedule:Class Schedule: http://banweb.evergreen.edu:7778/
amenu.P_MainMnuMain Menu Main Menu http://banweb.evergreen.edu:7778/
twgkwbis.P_LogoutGeneral - User LogoutUser Logout http://banweb.evergreen.edu:7778/
HWPREDIREmployee Directory Employee Directory http://banweb.evergreen.edu:7778/
HWPREDIR_INDEX Directory Menu Directory Menu http://banweb.evergreen.edu:7778/
9. We have elected not to change seed number.
Cloning r25p to r25t
Notify the following users of the upcoming clone:
Coordinate clone of r25p with Patti Zimmerman, Carol Davidson, Admin computing Team,
Network Svc.
2
A Clone from R25P to R25T is the example.
Logon as oracle8
Specify instance (e.g. r25p)
Set def ora_db which puts you in the following directory:
Directory DISK$SYSTEM2:[ORACLE.DB_R25P]
2
If you find the need to panic first do nothing. Don’t make any changes. Walk away & take a short 10
min. break , and then maybe talk to someone. After taking a short break, and talking to someone it is then
ok to panic. Most big mistakes are made within the 1st 10 min. of making a little mistake. Often you can
recover from the little mistakes more easily if you don’t panic right away.
Last printed 12/1/2011 10:38:00 AM 8
B. Verify that backups have run successfully the night before.
Enable restricted sessions for r25p and r25t
Do a full system export of the R25P, r25t.
J. Compile a list of all data files and redo log file associated with R25P.
2b.First, verify that the redo log files,and the data files, are the same ones referenced in the
copy.com and .sql scripts listed below. And that nothing has been added or
changed.
Note: 2a. You can Compare the list of redo log files and data files with the
copy.com file by running database_layout.sql in local.dba.tools against the
database. (have to do this while the DB is still up and running.) this script gives info only. ok
to run any time.
K. Shutdown R25T and R25P DB instances
Note: Start up and shut down instances using the oracle user only.
3. Log on as oracle
Note: 3a.When shutting down the databases ensure that there are no users left in
the database by executing the following command from SVRMGRL
SVRMGRL> Select username, sid, serial# from v$session;
You will always see 6-7 processes without a username, NEVER remove these, as
these are the database‟s back ground processes. (You may see sys or sytem, that is
you logged on to look, and/or to run an export) If you need to remove other users, ie:
job submission (this is where you may want to run gurstop(3b)) or users that have
come in early, use the following command:
SVRMGRL> alter system kill session ‟25,99‟ ;
Where 25=sid and 99 = serial# of the username that you want to kill.
3b. Run gurstop, if needed, (vms part) (repeat this for each instance )
R25T, svrmgrl, connect internal, @gen$plus:gurstop (run gurstop before
shutting down the db if there are job submission jobs in db)
Dave has a job called DBSTAT that is scheduled to run every hour
during r25puction hours. If you wish you can delete the entry for the job
from the queue on tesc05
Specify the instance name: e.g. R25T
Type „svrmgrl‟
Type „connect internal‟
Note: There are 3 ways to shut down an instance
Last printed 12/1/2011 10:38:00 AM 9
4. Shutdown – waits for the users to disconnect, and then shuts down
5. Shutdown Immediate -- kicks off users and shuts down.
6. Shutdown Abort -- (to be used only as a last resort)
Type „shutdown immediate‟
Repeat these steps again to shut down another instance.
L. Set the location of the database home:
Set the instance to R25T by typing „R25T‟, this points to the test instance and
runs banlogic.com.
Type SET DEFAULT ORA_DB -- sets the location of the database home.
The database home is where the clone scripts are kept.
M. Run a com file that deletes the old R25T files and copies r25p datafiles to
r25t’s area (VMS part).
Submit copy_r25p.com using the following command:
submit/notify/noprint/log=DRB0:[ORACLE.DB_R25T]COPY_R25P.log
COPY_R25P
Set „verify on‟ in copy_r25p.com if you are having a problem
This com file can take anywhere from 1 – 2 hours to run depending on the size of
the DB. This is the lengthy part of the clone.
You can monitor progress by typing „DIR USER2:[ORADATA.R25T]/SIZE’
and looking at the files copied (comparing this with the list of files to be copied in
copy_r25p.com. If a file has a size of 0, it is most likely the current file being
copied.
N. Create R25T’s control file (oracle part).
Make sure you are pointing to the R25T database
(You can do this by typing one of the following: „ SHOW LOGICAL ORA_DB‟ or
„SHOW LOGICAL ORA_SID‟)
Type SVRMGRL, connect internal, STARTUP NOMOUNT
Then run the create_ctrl.sql file to create R25T‟s control file with this command @create_ctrl.
This file will create the 2 control files for R25T, will then open the database with the resetlogs
option, and will change the internal name of the database from R25P (which was brought
over by the clone) to R25T. The script will also select from various views to show that the
database‟s name has been changed.
This is a good place to change the system password.
svrmgrl, connect internal, alter user SYSTEM identified by .
Last printed 12/1/2011 10:38:00 AM 10
G. Change GUAINST database name and passwords in R25T(cannot run this
inside svrmgrl)
Run mod_pword.sql with the command @mod_pword.sql
Then enter passwords as prompted based on our established naming conventions
This changes the instance name from R25P to R25T also.
Then shutdown the r25t database ….. svrmgrl, connect internal, shutdown immediate
O. TESC PART (if you deleted these entries before doing the clone submit them
again)
User3:[r25.adds.views.200230]Create_sc.com
User3:[r25.adds.views.200240]Create_40sc.com
P. Startup R25P and R25T
R25T, svrmgrl, connect internal, startup
R25P, svrmgrl, connect internal, startup
A. To be done following clone of r25p to r25t
Resource25 requires 2 usernames and passwords to log in: an application password and a database
password. We have elected to simplify login by using the same username and password for both
the application level and the database level.
When a clone is completed from r25p to r25t the passwords are the same in both the application
and the database, until the DBA runs a script to change some of the passwords in the r25t
database.
If this happens we must be sure and change the same passwords at the resource25 application level
to keep application security and database security in sync.
.
Last printed 12/1/2011 10:38:00 AM 11