Import FULL ORACLE Database

You are correct.  Just rum import as you suggested (with: full=y and ignore=y).  Yes, some records in the SYSTEM schema will be reported in the import log file as errors, because they already exist.  But no, this will not corrupt the TEST database.

What about system password? Is it overwritten with the password on PROD database?

If a user account already exists, import will not change the password for it.  If the PROD database has some user accounts that do not exist in TEST, then import will create the new accounts and give them the same password they have in PROD.

I know that is true for "normal" user accounts.  But I think I have seen import change the password for SYS and/or SYSTEM at times, so you will have to check.

1. You have to set NLS_LANG to a correct value if you are working
with non-English letters.

2. It is also recommended to create also the non-system users.

3. Another way is to use schema level export like in

https://www.experts-exchange.com/Database/Oracle/Q__22835034.html

Now I am using import fromuser=user1,user2,user3 touser=user1,user2,user3 but I´m afraid grants are lost, and what about roles and synonums???

From ... to ... is used when you change the schema/user.
I think this is not the case.

Grants and constraints are automatically included if you use "full=y".  But if you do a schema-level export, you may also need to include: "grants=Y" and "constraints=y".

Private synonyms will be included in a schema-level export, but public synonyms will not be.  Roles will also not be included in a schema-level export.

You could do an export though with: "full=y"  and "rows=n" which will be small and fast, and will include: users, roles, grants, constraints, PL\SQL objects etc. - everything except the actual table data.

Good Idea markgeer!

May be the best procedure is the following one:
On PROD Database:
1. export full=y rows=n file=export_db_structure.dmp
2. export full=y file=export_db_date.dmp
On TEST Database:
1. create tablespaces with the same name on PROD
2. import full=y file=export_db_structure.dmp ignore=y
Now we have users of PROD, on TEST database. SYSTEM user is not imported, because already exists.
3. import fromuser=user1,user2,user3 touser=user1,user2,user3  file=export_db_data.dmp ignore=y
now we have user1..3 data on their tables...

if you are doing a full import to create a test database, why not restore a production backup over the top of the test database?  this is simpler and far less error prone.

To rbrooker:  True, but then you have an exact copy of production (including the SID name).  That may or may not be the desired result in all cases  For example, we want our test system to have a different SID name (to make it less likely that someone will accidentally connect to the wrong one).  And, we want our test system to have some additional, and/or different tables and PL\SQL objects.  So, we have to use export and import to copy the data.  (Yes, before running import we also have to truncate the tables in test that also exist in production.)

A very rare used option:

Using DBCA create a template database files (structure and data)from your DB.
After that place the template files in appropriate directory
in the new installation and create a new instance using this template file.
If the OS and Oracle version is the same  - you will succeed.

I use this method with 9i database in our company to create reference database - this is a starter database with the whole database structure and some data a few tables.

When you restore a database, there is no requirement to keep the sid.  you recreate the control files.  at the top of the create control file script there is this :

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "<ORIGINAL_SID>" [NO]RESETLOGS  [NO]ARCHIVELOG
    MAXLOGFILES 255
    MAXLOGMEMBERS 2
    MAXDATAFILES 1022 etc...

changing it to :
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "<NEW_SID>" [NO]RESETLOGS  [NO]ARCHIVELOG
    MAXLOGFILES 255
    MAXLOGMEMBERS 2
    MAXDATAFILES 1022 etc...

allows you to change the name of the database.  thus preserving your desire to have an exact copy of prod but with different sids to stop people connecting to prod and thinking it is test.

Yes, that is possible and faster than an import (and I have done that too, once or twice).  But that assumes that an exact copy of the production system is the desired outcome, and that nothing from the test system needs to be saved.

Hi,

I wish to learn Oracle database from scratch,
    1.    Which version should I download from which site.
    2.    How should I install it to my PC,  where the OS is MS  ME

Please help me through this process.   Thanks.  
johnsrinivassan@hotmail.com

Go to Oracle Technology network and download 10g Release 2.
In Windows the installation is strathforward. Plan the disk for ORACLE_HOME - this is the Oracle software repository.
If the computer is not in network with ststic IP install the loopback adapter of the OS and make it the first network adapter - find the instruction on the net.
Make sure that there are NO firewalls and antiviruses.
Do not forget the SYS and SYSTEM password you give by installation.