1
Hi. I receive a database backup on a daily basis. I then import this dump on another server which I use for software development. There is two db's being hosted on this server. We test software upgrades against these two db's. The "refresh" needs to be completed at least once a day, sometimes 4-5 times a day. I need the quickest way of doing this task. I have extremely limited knowledge on scripting. What I need is something that I can just click, and then automatically do the import onto my training environment. My current procedure is something like this:
This procedure will refresh the UAT database from the latest live database export. The live export is a daily generated file -->
WED_mvdl.dmp.gz
THU_mvdl.dmp.gz
FRI_mvdl.dmp.gz
etc...
For the import to work, the dump must be copied from the live server’s export directory to the UAT server’s import directory. Keep in mind that the dump is GZipped and will be unzipped with a simple putty command.
Copy the newest -DUMP- to the Server’s –IMPORT- directory.
Sort the files by date. The file we need is the newest dump; its type is “GZ”
• The path to the dump export folder is:
\\192.168.11.210\database exports
Username: root
Password: root01
• The path to the training/uat server’s import folder is:
\\192.168.11.202\backup\exports
Username: root
Password: root01
• Copy the newest dump. (for instance: WED_mvdl.dmp.gz)
Note that the TRNG and UAT db’s is hosted on the same server. This is the training server and is not in the server room. Connect to the training server via putty. All the following happens in the putty session:
(Oracle user)
• Putty to: 192.168.11.202
Username: Oracle
Password: Ora528i
Type the following into putty’s prompt window.
• Sqlplus / as sysdba (enter)
SQL>Select name from v$database;
This should be displayed to indicate a trng db connection:
NAME
---------
TRNG
The following procedure will drop the UAT user from any active sessions that it may have. Keep in mind that we are working on a standalone testing and training environment so no serious damage can be done. There is no risk in performing these steps if the correct procedures are followed.
• Shutdown immediate;
• Startup;
• Drop user uat cascade;
• Copy all of this text into putty session:
“
spool create_UAT_user.log
CREATE USER UAT PROFILE DEFAULT IDENTIFIED BY NAUAT DEFAULT TABLESPACE TIS_DATA QUOTA UNLIMITED ON TIS_DATA QUOTA UNLIMITED ON TIS_INDEX TEMPORARY TABLESPACE TEMP;
GRANT CREATE PROCEDURE TO UAT;
GRANT CREATE SEQUENCE TO UAT;
GRANT CREATE SESSION TO UAT;
GRANT CREATE TABLE TO UAT;
GRANT CREATE VIEW TO UAT;
GRANT CREATE TRIGGER TO UAT;
GRANT EXECUTE ANY PROCEDURE TO UAT;
GRANT CONNECT TO UAT;
connect sys/sys528i@trng as sysdba
grant select on SYS.V_$DATABASE to UAT;
GRANT SELECT ON SYS.V_$MYSTAT TO UAT;
GRANT SELECT ON SYS.V_$SESSION TO UAT;
GRANT SELECT ON SYS.V_$PARAMETER TO UAT;
spool off
“
• Exit;
cd /u03/backup/exports
su root
Password root01
pwd // NB The Path must be “/u03/backup/exports”
chown oracle:dba *.dmp*
exit
With the following command we will unzip the dump file on the server:
• gunzip FRI_mvdl.dmp.gz
If prompted to overwrite the file type “Y” for yes.
This takes roughly a minute.
• imp system@trng file=FRI_mvdl.dmp log=imp_uat.log fromuser=TANZ touser=UAT
Password sys528i
This operation will take about 15minutes
Note!! On completion, the sentence: The import completed successfully…
If anyone can help me I'd appreciate it alot. Any simplified steps or script solution will be welcomed. Thank you.
