- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: How can I update oracle test racledatabase? (O...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-20-2003 08:48 AM
тАО06-20-2003 08:48 AM
How can I update oracle test racledatabase? (Oracle Gurus)
Test rdbms: have 12 schemas, 6 schemas have about 500 records by table.
Production rdbms: have 6 schemas, each one have tables with thousands of records.
I need to update on each weekend the TEST rdbms with all new records from Production rdbms on these schemas, How I do that?
I was review Export/Import with 'incremental option', but it work over a base with full option, in my case not apply, and these option will be desoported and out soon.
I need updated data like: on production under the schema ZXY the most tables have 2000 new rows by week, I need to pass them to ZXY schema on test rdbms????
At this moment on production database are one table with 2,500,000; the same table on test database have 7,000 rows, I need to update that table without schema procedure, triggers, etc, only data. I can't do this with simple schema export/import, because it take long time, 4 days maybe.
(I don't use rman or other tool)
What can I do?
Thanks...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-20-2003 02:38 PM
тАО06-20-2003 02:38 PM
Re: How can I update oracle test racledatabase? (Oracle Gurus)
insert into TABLEA
select * from TABLEA@production a
where not exists
(select 1 from TABLEA b where
a.prikey1 = b.prikey1 and
a.prikey2 = b.prikey2 and
a.prikey3 = b.prikey3);
You will need to subsitute your table names and primary key columns in, but this will insert any rows that do not exist on your development server. You will probably need to have a large rollback segment setup for this. If you wanted to, you could convert this into a PL/SQL block and have commits generated every 10,000 rows.
Thanks,
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-20-2003 04:16 PM
тАО06-20-2003 04:16 PM
Re: How can I update oracle test racledatabase? (Oracle Gurus)
In my environment, we clone the whole production to test machine (copy the datafile, then change the instance name), and truncate or remove records from the new test database.
In you case, is it possible to
1. backup your schema objects from test database (tablespace export, user export)
2. clone production database to test database and change instance name
3. truncate the test database and remove unneeded data and schemas
4. import the "test data" back to the test database.
(but it is impossible to daily update the test database)
If the test database is used for reporting purpose, I will use "read only" standby database instead.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-20-2003 08:37 PM
тАО06-20-2003 08:37 PM
Re: How can I update oracle test racledatabase? (Oracle Gurus)
My personal favorite is the sqlplus copy command. Its in the "none of the above" category. Doesn't need dblinks, no need to write code, very fast -- and can be selective.
hope this helps!
regards
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-20-2003 08:40 PM
тАО06-20-2003 08:40 PM
Re: How can I update oracle test racledatabase? (Oracle Gurus)
attached documentation with examples.
regards
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-22-2003 11:05 PM
тАО06-22-2003 11:05 PM
Re: How can I update oracle test racledatabase? (Oracle Gurus)
if you can afford a little downtime i wuold go to a refresh of the database.
restoring and offline backup of the production system to the test system, doing the sid change.
IF you have different trigger or some users only in yhe test system, you can export that and re-import afterwards.
Another trick i saw from one of my customer was the implementation of advanced queuing, for keeping up-to-date and offsite DB.
Another customer used materialized view, to do this.
HTH,
Massimo
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-23-2003 07:08 AM
тАО06-23-2003 07:08 AM
Re: How can I update oracle test racledatabase? (Oracle Gurus)
The test database has designer 6i on it with private synonyms, at this point, I can't drop any schema or drop datafiles....
It only allow me, do an "append" or add new records to the already existents records in test database.
Can you understand my issue, I need add new records on tables in 6 schemas on test, the others 4 schemas are under developt at threir firts stages.
The other think is, I can drop all tables without drop designer tables and objects stored in each aplication schema....!!! This is very dificult task and take long time..
I need to find the way like "incremental" option in the export tool. Remember that incremental export work fine with one base "complete", I can use "complete" base because are diferents databases.
These enigma is very complicated..!!!
Any other idea is wellcomed...
PD: I do twice the assigment points, but I nerver see them put after 'submit', I hope that it has worked yet.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-23-2003 07:09 AM
тАО06-23-2003 07:09 AM
Re: How can I update oracle test racledatabase? (Oracle Gurus)
The test database has designer 6i on it with private synonyms, at this point, I can't drop any schema or drop datafiles....
It only allow me, do an "append" or add new records to the already existents records in test database.
Can you understand my issue, I need add new records on tables in 6 schemas on test, the others 4 schemas are under developt at threir firts stages.
The other think is, I can drop all tables without drop designer tables and objects stored in each aplication schema....!!! This is very dificult task and take long time..
I need to find the way like "incremental" option in the export tool. Remember that incremental export work fine with one base "complete", I can use "complete" base because are diferents databases.
These enigma is very complicated..!!!
Any other idea is wellcomed...
PD: I do twice the assigment points, but I nerver see them put after 'submit', I hope that it has worked yet.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-24-2003 11:37 AM
тАО06-24-2003 11:37 AM
Re: How can I update oracle test racledatabase? (Oracle Gurus)
just a few questions:
- Do you need to get a "consistent" copy of the entire production database ?
- Do you need ALL or only partially data.
- New rows are easy, because the will have new keys, but what if a row is deleted in prod ? Should it be deleted in Test as well ? What about updates (hell) ?
- What do you want to do with the data in the test environment ?
- What about data that is entered in the test environment for the next refreh ? Should be kept or should be deleted ?
I'd ever prefer to get a consistent copy, to be sure I have everything ok and I do not like to have downtimes for consistent exports in production. This leaves "backup/restore" as the only way, as incr-exp will be desupported and some type of replication will be complicated and put workload on production.
If you need the data for testing reports or mostly SELECT accesses, I'd drop the tables in Test-db and create DBLINKS to a second "prodcopy"-DB, which can be refreshed with backup / restore (several ways described in this forum [can even be done from online backups without causing downtime or additional workload in production]). This is easy.
If you have OLTP processing spreading about two databases, you might need to setup/configure additional stuff to activate two-phase-commit for distributed transactions. I am not sure HOW to do this (never had to configure) but I am sure Oracle supports this.
(distributed_transactions=true ?).
I am not sure either if this will affect your coding in your application.
If this leads to nothing, you might need to check out the replication features of Oracle or do it manually by creating triggers, that update the data in your testdatabase in addition, but this will put significant load on your test db.
Stuff to think about
Volker
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-24-2003 11:40 AM
тАО06-24-2003 11:40 AM
Re: How can I update oracle test racledatabase? (Oracle Gurus)
... significant load on your PROD - db ....
(because this is where the triggers/replication will be processed)
V.