Link to home
Create AccountLog in
Avatar of alcsoft
alcsoft

asked on

How to sync the production Oracle database into the test Enviroment!

Hi
I have 8i Oracle Database runs on AIX Unix machine! this database is my production environment..

The developers asked me to get a copy of this database and deploy it into the Test server, which is also an AIX with oracle 8i..

I am wondering what is the best way and most optimum way to do that?

Please note the production has a daily backup using rman..

Please list detailed steps to do that, I know how to do that easily with different ways in SQL server, but I am new to oracle!
Avatar of tangchunfeng
tangchunfeng


Creating and Updating Duplicate Databases with RMAN
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb.htm
Avatar of arnold
Does the test database have to have the current data as the production database?
I.e. depending on your backup schedule, do you have a full backup of the production database that you can use to restore on the test system?

What do you use to access the oracle DB do you use toad GUI? or do you access the sql using the sqlplus?
Avatar of alcsoft

ASKER

Arnold,

Yes I want my Test DB has the exact data and objects as the production!
also I have a full backup of the production, there is a nightly cron job call RMAN to generate this backup!
I am using sqlplus and rapidsql as well! also I have Oracle Enterprise Manager console
Would using a nightly restore of the production database backup onto the test environment is what you are looking for or do you want any update on the production side to be pushed/updated in the test environment?
i.e. are you looking at setting up "replication" from the production to the test?
Avatar of alcsoft

ASKER

I think a onetime restore would be fine!
I mean I would like to restore the database into the Test server when the developers ask me to do that!
Can you give me the steps to do so? Backup the prod database in the prod server, then I can copy it to the Test server using FTP, and finally run some commands to restore it into the Test server! I am not comfortable to do it by reading online! I am afraid I will restore it into the production server, if I didn't do it right!!
I am sorry, in my question, I mentioned it is Unix server, actually it is a Windows 2000 server :)
Any help in here is really appreciated  
ask your unix administrator to configure the backup software on the test server
such as Veritas
then you can use rman / duplicate to restore the production db to test db
Avatar of alcsoft

ASKER

Tangchunfeng,

I correct it!! it is not Unix, it is a Windows!
Please read my last post.

veritas or something else works both on unix and windows
How big is the database?  I would probably go with exp/imp when crossing Operating Systems.

Create a new database on the Test Server using dbca with the exact same version/patch levels.  Make sure to precreate the tablespaces to match all the tablespaces in prod.

On Prod:
exp system/manager full=Y consistent=y file=prod_export.dmp

Then move this file over to the test server:
imp system/manager full=Y file=prod_export.dmp
Avatar of alcsoft

ASKER

slightwv:
Thanks... This what I have been using so far...
The problem this approche takes long time to import especially if the database is big! and sometimes it fires some errors and stops unless I put the switch ignore=y

I am wondering if I can use RMAN instead!! Any experience of doing this? Do you have any concern of not using RMAN to restore the backup from the production into the test server?
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
>>and sometimes it fires some errors and stops unless I put the switch ignore=y

You need to make sure you understand what this does.  If you don't properly prep the test database for the refresh you could duplicate data in some tables.
Avatar of alcsoft

ASKER

Nope I truncate the table before the import.

I think the need for ignore=y is to avoid recreate the tables and views!!

Avatar of alcsoft

ASKER

Thanks SlightWV,

I would be interested to see how to do it using RMAN...

Can you please post details, it would be nice to see a walk through steps.


I believe I mentioned in http:#a34493711 that it likely cannot be done with version 8 and RMAN.
Avatar of alcsoft

ASKER

Yes Slightwv,
But what if I want to do it in 10g... How this will work?
Did you not read the post referenced above?

Again, covered in http:#a34493711:

"If they were 10g or above you 'might' have a chance with the RMAN CONVERT"

I provided a link that talks about it...
Avatar of alcsoft

ASKER

So Basically telling me you can't provide me with step by step procedure?
There is no 'step by step' per say.

It's a single RMAN command per tablespace and that command is at the bottom of the link I provided.

As long as the Endians match, it should work.  The link provides AIX is Big Endian.  I'm not an admin so I'm not sure about Windows.
Avatar of alcsoft

ASKER

Anyone can advice on Windows!
Sorry but you cannot use RMAN to to this between AIX and Windows 2000.

If you can believe Wiki, Windows is little endian:  http://en.wikipedia.org/wiki/Endianness

Again, did you not bother to read the article form the link I posted?

While researching this for you, and re-reading the link I provided, Oracle tells you this.

Excerpt from that link:

A new data dictionary view, v$transportable_platform, lists all nine supported platforms, along with platform ID and endian format.

Also an excerpt form that link:
To transport a tablespace from one platform to another, datafiles on different platforms must be in the same endian format (byte ordering).



Avatar of alcsoft

ASKER

Opps...

Thanks for research this.
Do you suggest another way to do the sync other than import/export?
I can't think of any other way that would be any better or faster than export/import.

It's the crossing platforms that is messing you up.
Avatar of alcsoft

ASKER

Ok.. Thanks Slightwv...
I advise simplifying your life and getting an AIX test server instead. It is always best to test on the same platform as production, because Oracle bugs differ between platforms. Testing on Windows db != Testing on AIX db

If you don't have one, you can buy an old cheap that can run 8i. You only need 1GB of RAM or so.

My second advice is to get to 10g or 11g, but that is another issue.
Avatar of alcsoft

ASKER

Thanks for the response!  So basically we back to the original question again!!!


This is what we did:
We curve out some resources of the IBM server that we have, and we installed AIX on the new partition so now we have two logical servers, let's call them AIXPROD and AIXTEST running on the same IBM server.

We installed the the Ora 8i on the test server and now I want to use the RMAN backup from the production and push it to the TEST database.
Can you please show me how to do that?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Why are you back to the original question?

The very first post provided a link on how to duplicate a database using RMAN.

There are also a TON of links out there on how to copy/clone/restore to a different server using RMAN that you can find with a quick Google search.

In http:#a34433666 you changed this to Windows not Unix.  When/why/how did this change?

Avatar of alcsoft

ASKER

mrjoltcola, I will try it and let you know if it works.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.