473,895 Members | 2,245 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Database import/export with putty.

1 New Member
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.21 0\database exports
Username: root
Password: root01
• The path to the training/uat server’s import folder is:
\\192.168.11.20 2\backup\export s
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_$PARAMETE R 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.d mp 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.
Jul 30 '07 #1
0 10011

Sign in to post your reply or Sign up for a free account.

Similar topics

11
4211
by: Mike MacSween | last post by:
My client has an MS Access database application on her local machine. I have full access to that in terms of changing the design. I've got a simple PHP/MySql application on shared hosting, so no direct access to the db server. I'd like to give her the facility to export the information in her local Access application to the shared PHP/MySql site. From one command button (or similar) in the Access application.
3
12149
by: Cristina | last post by:
Hallo i am a beginner into Oracle Technologies.I would like to make backup of my database,but i dont know how.Is there tools?Can i schedule the backup plan? thanks Cristina
3
2377
by: Bennett Haselton | last post by:
I'm working on an ASP.Net project where I want to test code on a local machine using a local database as a back-end, and then export it to the production machine where it uses the hosting provider's SQL Server database on the back-end. Is there a way to export tables from one SQL Server database to another in such a way that if a table already exists in the destination database, it will be updated to reflect the changes to the local...
3
4922
by: Xerxes | last post by:
Hi, I would like to import the tables from MySQL database into Access database and I am not sure how to go about it. I tired to "Import External Data" from Access, selected "ODBC database" from file type dropdown, selected "New data source" and picked SQL Server from the list. I specified the server name (mydomain.com) along with user id and password. But when I clicked on "Next", I got: "connection failed: SQL Server Error:10060,...
6
6355
by: Frank Fiene | last post by:
How does this work? I have one database SBO_DB (SAP BusinessOne) I want to copy tablespace TS_SEW_ACCESS_DEMO to TS_TEST2. The directory of the database is: F:\DB2\NODE0000\SQL00002 Please help! Frank
4
4919
by: Max2006 | last post by:
Hi, We are developing a SQL server based asp.net application. As part of requirement we should allow users import/export some relational data through web user interface. We are investigation which file format would be the most efficient format to import export relational data. So far we came up with two options: XML and Access MDB files and we prefer MDB files.
6
5666
by: mirianCalin | last post by:
i am doing the programming at home and our teacher checks it in school that's why i need to export/import my database.. but i dont know how.. i've found this instruction on the net but i cant understand 1. Export A MySQL Database This example shows you how to export a database. It is a good idea to export your data often as a backup. # mysqldump -u username -ppassword database_name > FILE.sql Replace username, password and...
39
5896
by: alex | last post by:
I've converted a latin1 database I have to utf8. The process has been: # mysqldump -u root -p --default-character-set=latin1 -c --insert-ignore --skip-set-charset mydb mydb.sql # iconv -f ISO-8859-1 -t UTF-8 mydb.sql mydb_utf8.sql mysqlCREATE DATABASE mydb_utf8 CHARACTER SET utf8 COLLATE utf8_general_ci;
0
2897
by: kkshansid | last post by:
i cannot export my sql server database to msaccess database which i usually did successfully 3weeks ago pls help me to find the error so that i can correct it in future error is - Validating (Error) Messages * Error 0xc02020f6: Data Flow Task: Column "Project ID" cannot convert between unicode and non-unicode string data types. (SQL Server Import and Export Wizard)
0
9990
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9835
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10473
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9649
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8028
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6068
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4689
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4285
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3294
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.