A set of UNIX shell scripts has been created which automates the refresh of any Oracle database from an export file. This type of refresh process is often used to refresh the ATCT database with data from either an earlier version of ATCT or from ATCQ data at some previous point in time.
|
Step |
Action |
Command |
Expected
Output |
|
0 |
Telnet
to 10.7.79.66 (ykktasg) |
telnet 10.7.79.66 |
AIX Version 5 (C) Copyrights by IBM and by others 1982, 2002. login: |
|
1 |
Log
on to ykktasg (10.7.79.66)as “oracle” |
AIX Version 5 (C) Copyrights by IBM and by others 1982, 2002. login: oracle |
AIX Version 5 (C) Copyrights by IBM and by others 1982, 2002. login: oracle oracle's Password: |
|
1.1 |
Type
in the password for the “oracle” user |
AIX Version 5 (C) Copyrights by IBM and by others 1982, 2002. login: oracle oracle's Password: ***** |
******************************************************************************* *
* * Welcome
to AIX Version 5.2! * *
* * Please
see the README file in /usr/lpp/bos for information pertinent to * * this
release of the AIX Operating System. * *
* ******************************************************************************* Last unsuccessful login: Mon Oct 11 15:07:11 EDT
2004 on ftp from ::ffff:10.97.31.164 Last login: Wed Feb 2 11:00:30 EST 2005 on ftp from ::ffff:10.97.31.170 oracle @ykktasg.montefiore.org [/m01/oracle] # |
|
2 |
Change
directory to /m02/oracle/scripts |
#cd
/m02/oracle/scripts |
oracle @ykktasg.montefiore.org [/m02/oracle/scripts] # |
|
3 |
Check
that all required program scripts are installed |
oracle #ls –l *.ksh *.sql |
-rwxr-xr--
1 oracle dba 7154 Feb 02 14:27
auto_refresh_v3.ksh -rwxr-xr--
1 oracle dba 491 Feb 01 16:35
control_fkeys_disable.sql -rwxr-xr--
1 oracle dba 491 Feb 01 16:40
control_fkeys_enable.sql -rwxr-xr--
1 oracle dba 2779 Jan 31 13:53
crt_control_fkeys_proc.sql -rwxr-xr--
1 oracle dba 2479 Feb 01 11:33
disable_pl_v2.sql -rwxr-xr--
1 oracle dba 2032 Feb 01 11:24
enable_pl_v2.sql |
|
4 |
Set
the correct Oracle Environment |
#export
ORACLE_SID=XXXX #echo $ORACLE_SID |
oracle @ykktasg.montefiore.org [/m02/oracle/scripts] # XXXX (e.g. XXXX = ATCT or ATCQ, the target database
of the refresh). |
|
5 |
Execute
the refresh program |
#auto_refresh_v3.ksh |
You are preparing to do an Oracle database
refresh... It is necessary to shut down the Kronos
Application Before refreshing data. Please shut down the Kronos applications before
continuing... Is the Kronos Application Shut Down (Enter Y/N): |
|
6 |
Verify
that the Kronos web and application pieces, as necessary, are shut down |
Is the Kronos Application Shut Down (Enter Y/N): Y NOTE 1: Entering an “N” here terminates the
refresh program and outputs a message to standard output. |
Enter the ORACLE_SID of the database you want to
refresh: XXXX (e.g. XXXX = ATCT or ATCQ, the target
database of the refresh). |
|
7 |
Verify
that you have selected the correct database refresh target |
The SID you selected is ATCQ2 : is that right ?
(Y/N) Y NOTE 1: Entering an “N” here loops and keeps
asking for A SID until you accept the SID. |
You are performing a restore to the ATCQ2 Kronos
System. Do you wish to continue (Enter Y/N): |
|
8 |
Re-confirm
database target |
Do you wish to continue (Enter Y/N): Y NOTE 1:
Entering an “N” here terminates the program and sends a message to
standard output. |
WARNING: THIS REFRESH SCRIPT WILL DESTROY THE
CURRENT DATA IN THE TARGET ATCQ2 DATABASE UNLESS YOU TAKE A PRE-REFRESH
EXPORT !!! YOU CAN RETAIN THE CURRENT DATA BY TAKING THE
FOLLOWING PRE-REFRESH EXPORT OF THE CURRENT ATCQ2 DATABASE ... Would you like to do a pre-refresh export of the
ATCQ2 database before refreshing (RECOMMENDED) Enter Y/N: |
|
9 |
Capture
optional pre-refresh export of current data recommended |
Would you like to do a pre-refresh export of the
ATCQ2 database before refreshing it with the new data (Enter Y/N):Y NOTE 1: If
you enter “N” here program asks you to reconfirm and warns you that it is
highly recommended to take a pre-refresh export. |
You are doing an EXPORT of the pre-import image of
the existing system... Enter the oracle SYSTEM password when prompted... Export of the pre-import image will begin in about
5 seconds... Export: Release 9.2.0.4.0 - Production on Wed Feb
2 12:25:50 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Password: |
|
10 |
Type
in the password for the oracle database “system” user |
Password: ********* NOTE 1:
After the import completes, SQL scripts execute (see list step 3).
Wait while scripts execute. Information about the status and execution of
these scripts is buffered to standard output. Watch. The scripts run
in about a minute or two (or less) total elapsed time. |
Connected to: Oracle9i Enterprise Edition Release
9.2.0.4.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining
options JServer Release 9.2.0.4.0 - Production Export done in US7ASCII character set and
AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible
charset conversion) About to export the entire database ... . exporting tablespace definitions . exporting profiles . exporting user definitions . . . Export terminated successfully without warnings. The pre-refresh export has been taken. The refresh begins in a minute or two...WAIT... |
|
11 |
Enter
the name of the export file to be used as the data source for the refresh. |
Enter the export file to use for this refresh: exp_ATCQ_011605-0800.dmp.gz NOTE 1: You
can enter either a zipped (*.dmp.gz) or unzipped (*.dmp) file name. Above name is just a typical example. NOTE 2:
The file to be imported must be a valid oracle export file and must be
located in: /m02/oracle/admin/dbexport NOTE 3:
Invalid filename or not in m02/oracle/admin/dbexport directory,
program will prompt in loop for valid filename until valid. |
Unzipping the file to be imported... You are importing exp_ATCQ_011605-0800.dmp Enter the Oracle SYSTEM password when prompted... Import: Release 9.2.0.4.0 - Production on Wed Feb
2 12:35:40 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Password: |
|
12 |
Enter
the password for the oracle database “system” user account |
Password: ********* NOTE 1:
The import of the data (the refresh) is executed. NOTE 2:
After the import finishes, SQL scripts execute (see list step 3). Wait while scripts execute. Information about the status and execution of
these scripts is buffered to standard output. Watch. The scripts run in about a minute or two (or less) total
elapsed time. |
Connected to: Oracle9i Enterprise Edition Release
9.2.0.4.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining
options JServer Release 9.2.0.4.0 - Production Export file created by EXPORT:V09.02.00 via
conventional path import done in US7ASCII character set and
AL16UTF16 NCHAR character set import server uses WE8ISO8859P1 character set
(possible charset conversion) . importing TKCSOWNER's objects into TKCSOWNER . . importing table "ACCESSPROFILE" 22 rows imported . . importing table "ACCESSPROFPERM" 1995 rows imported . About to enable constraints... Import terminated successfully without warnings. The data has been refreshed. The post-refresh steps will begin in a minute or
two . . .WAIT. . . USER is "TKCSOWNER" |
|
13 |
Completion
messages appear. You are done. |
No input is necessary. NOTE 1: If
there are any INVALID objects; DISABLED constraints; or if there was any
warnings or errors in the import, the program will advise you of this. NOTE 2:
The program verification result for a successful refresh is shown here
as the example output. |
PL/SQL procedure successfully completed. Elapsed: 00:01:03.70 (NOTE: your time will vary) Zip the export file back up... The auto_refresh program has verified that: All TKCSOWNER objects are VALID. All TKCSOWNER referential integrity constraints
are ENABLED. The refresh Import terminated successfully without
warnings. oracle @ykktasg.montefiore.org [/m02/oracle/scripts] # |