Tablespaces in full Db export
Date: Tue, 27 Jul 1999 02:02:28 -0400 (EDT)
From: S F sf3010@yahoo.com
To: oraapps-dba@cpa.qc.ca
Subject: Full Database Export/Import
Hi all,
This might sound like a trivial question, but I don't have time to
verify it practically, that's why I'm sending this email to the list.
In case of full database export, do I have to have the same tablespaces
i.e. same names of tablespaces or same number of tablespaces while
importing into another database?
Best regards,
SF
sf3010@yahoo.com
Date: Tue, 27 Jul 1999 07:32:23 -0500
From: "Kuan, Amy M" kuanam@texaco.com
Subject: RE: Full Database Export/Import
A full export will contain the ddl to create all tablespaces except system,
but the create statements will contain the same datafile names as the source
database. Therefore, all the specified directories must exist. Tablespaces
must exist with the same name for all other objects, like tables, indexes,
etc., because the ddl will specify its originating tablespace name. You can
pre-create these objects in a different tablespace prior to importing the
data, then ignore errors on import.
Date: Tue, 27 Jul 1999 05:36:38 -0700 (PDT)
From: Ted Sasscer tsasscer@yahoo.com
To: oraapps-dba@cpa.qc.ca
Subject: Re: Full Database Export/Import
SF,
No. During import, Oracle will put objects in the user's default
tablespace if the tablespace named in the export file doesn't exist.
Unfortunately, this means you tend to end up with both data and indexes
in the same tbsp. However, if you make two passes of the import, first
with indexes=n and rows=y, then ALTER USER ... DEFAULT TABLESPACE
idx_tbsp, then import with indexes=y and rows=n, then you will get them
mostly separated. Don't forget to ALTER USER back to original tbsp.
Ted Sasscer
Baytree Associates
Date: Tue, 27 Jul 1999 23:08:50 -0700 (PDT)
From: S F sf3010@yahoo.com
To: oraapps-dba@cpa.qc.ca, Multiple recipients of list
Subject: Re: Full Database Export/Import
Thanks Ted and Amy for the responses. The reason why I posted this
question was that I am supposed to do a full database export from one
HP server to another. Now the problem is that the mountpoints being
used on the first one, have equivalent in the second one but they are
already being used. That's why I can't have the same tablespaces. It's
basically an OraApps 10.7 database. Is there any other way besides
using user's default tablespace? Is there some way I could do an
offline cold backup and somehow modify the data dictionary of the new
database in restricted mode on the second server and then restart the
database? Even I think this sounds a little crazy, but I'm really
desperate. Anyone out there with some ideas/clues?
Thanks in advance,
SF
sf3010@yahoo.com
Date: Wed, 28 Jul 1999 08:39:43 +0100
Date: Wed, 28 Jul 1999 09:19:41 +0100
Date: Wed, 28 Jul 1999 10:49:07 METDST
Date: Wed, 28 Jul 1999 21:05:52 PDT
Date: Wed, 28 Jul 1999 21:13:08 PDT
Date: Thu, 29 Jul 1999 09:06:30 +0200
Date: Thu, 29 Jul 1999 10:14:24 +0200
Date: Sat, 31 Jul 1999 06:36:26 -0700 (PDT)
Date: Sat, 31 Jul 1999 06:31:45 -0700 (PDT)
--- "Kuan, Amy M" kuanam@texaco.com wrote:
Date: Sat, 31 Jul 1999 06:55:07 -0700 (PDT)
Date: Sat, 31 Jul 1999 10:00:31 -0400
Date: Sun, 1 Aug 1999 00:13:05 -0400
Date: Sun, 01 Aug 1999 20:54:56 PDT Time Synchronization of Servers With Oracle DBs
Date: Mon, 25 Oct 1999 08:18:49 -0700
Date: Mon, 25 Oct 1999 11:37:36 -0400
From: Graham Duggan gjduggan@mail.com
To: "'oraapps-dba@cpa.qc.ca'"
Subject: RE: Full Database Export/Import
If I understand your question, the answer is quite simple. It is not a
question of the same tablespaces, but rather a question of file
names/paths. There are two options.
Export/Import
When you create the database along with all of the tablespaces from the
original database, note that this means tablespaces with the same name,
but not necessarily the same location, the create tablespace commands in
the import will fail (unless you set destroy=3Dy, =3Dn is the default).
Therefore you import will run fine without danger to the existing
database.
Database clone.
This is where you cold copy the database to a new location and recreate
the control file. In the script to recreate the controlfile you can
change the location of the datafiles. Be careful here not to miss one.
There is a procedure on the web for this somewhere which I am sure
someone can point you to. You can create the script by executing the
"alter database backup controlfile to trace" in svrmgrl and then looking
for the trace file in your user_dump_dest. (hack out the comments and
make a few changes to change the database name and file locations)
Graham.
From: jem.lough@blackwell.co.uk
To: oraapps-dba@cpa.qc.ca
Subject: RE: Full Database Export/Import
SF,
It sounds as if you want to take a complete copy from one DB on one machine
to another machine.
If this is true, then I think it would be quicker to use the technique of :-
on the source DB....
backup controlfile to trace
shut down the source DB,
copy the datafiles to the new locations
amend the controlfile trace file use new DB name, datafiles etc. and use to
create new DB.
The above is a summary, see http://members.home.net/arivenes/aolcopy.htm for
an excellent detailed write up of the technique by Andy Rivenes.
It might seem more complicated than a full export/import but if it is a
large DB it will be much quicker.
HTH.
Jem.
Jem Lough
Switchboard 01865 330000
DDI 01865 333359
FAX 01865 791438
e-mail jem.lough@blackwell.co.uk
url http://www.blackwell.co.uk
From: Stephanie Mandell steph@A2000.com
To: oraapps-dba@cpa.qc.ca
Subject: RE: Full Database Export/Import
SF,
It sounds as if you want to take a complete copy from one DB on one
machine
to another machine.
NB: This will only work if both machines are on the same version
of the operating system. Since SF mentions HP-UX, I will say
this will work from any 10.x to another. It will not work
between 10.x and 11.
steph
From: "shankar govindan" shankargovindan@hotmail.com
To: oraapps-dba@cpa.qc.ca
Subject: Re: Full Database Export/Import
SF,
When you export/import you dont cary your system dictionay along. So what
is the problem. If you want to import it back you have to create a database
with system tablespace and run all the cat scripts.
Then create the table spaces reqd with OFA compliant. Import it back.
This way you have a new system dictionary data.
Shankar
From: "shankar govindan" shankargovindan@hotmail.com
To: oraapps-dba@cpa.qc.ca
Subject: RE: Full Database Export/Import
Stephane,
That's new. You mean the file format for HP 10.x is different
from 11 or are you talking about APPS 10.x to 11.x
Well I feel SF has a serious problem in his system tablespace and he wants
to recreate it if I am right. The whole exercise must be for this. The best
way is export/import according to me.
Shankar
From: stephanie mandell steph@a2000.com
To: oraapps-dba@cpa.qc.ca
Subject: RE: Full Database Export/Import
Right. HP 10 and 11 are not "binary compatible".
steph
From: "Worthington, S." S.Worthington@ahpg.com
To: "'oraapps-dba@cpa.qc.ca'"
Subject: RE: Full Database Export/Import
"binary compatible" normally means that executables on one will run on the
other.
I haven't done an upgrade from HPUX 10->11 upgrade, but I would expect that
it is possible to just copy the datafiles over, which is a lot easier than
export/import.
Stu
stuartw@email.com
From: S F sf3010@yahoo.com
To: oraapps-dba@cpa.qc.ca, Multiple recipients of list
oraapps-dba@cpa.qc.ca
Subject: RE: Full Database Export/Import
Thanks Stephanie, I'm on the safe side as I've got HP-UX 11 on both the
machines.
Best regards,
SF
sf3010@yahoo.com
From: S F sf3010@yahoo.com
To: oraapps-dba@cpa.qc.ca, Multiple recipients of list
oraapps-dba@cpa.qc.ca
Subject: RE: Full Database Export/Import
Hi all,
The reason why I posted this question is that I plan to export the
whole 10.7 Database from one HP-machine to another. I would like to do
an offline OS backup but in this case my mountpoints are different on
the target machine. I cannot create mountpoints with the same names as
they are already being used on the target machine. The only option I
have is to use new mountpoints and these mountpoints are the problem
that I am having for the OS backup. I was wondering if I export the
database and then start up the new database in restrict mode, can I
modify the dba_data_files table and then later shutdown and restart it
again? I know modifying the dba_data_files sounds a bit crazy, but I
was wondering if this could be done. However if this doesn't work out,
I'll try to do two different passes first with data and then with
indexes and use the manipulation of the user's default tablespace. This
was a suggestion given to me on the list. The task is still about week
ahead, so I'm still open to all clues/suggestions.
Best regards,
SF
sf3010@yahoo.com
Ted,
If you pre-create the tablespaces on whatever mount
points are available,
you can still do a full import. The information you
need to build the
create tablespace statements can be found in the
dba_tablespaces and
dba_data_files views.
If the new database hasn't been built yet, there is
another method of
migrating a database, but it requires a little more
work. The database
files can be copied directly from the old server or
from a cold backup of
the database to create a new database. Here is a
procedure from Oracle
support on how to do this. It describes how to make
a copy of a database on
the same server under a new name, but will work from
server to server as
well. Just ignore the instructions on renaming the
database if the name
isn't changing.
CREATING THE DATABASE
---------------------
METHOD IV - USING THE UNIX "CP" COMMAND
The following procedure describes how to create a
second database and
instance
from an existing database using the UNIX 'cp'
command and Oracle's 'create
control file statement'. The second instance can
also be created by
restoring
datafiles from a cold backup.
Steps for Method IV:
1. Set the UNIX environment variable to the current
database, DatabaseA.
(SEE SETTING UNIX ENVIRONMENT SECTION ABOVE).
2. Backup the control file to trace From DatabaseA.
The trace file
(ora_xxx.trc) is located in the directory defined
by USER_DUMP_DEST.
SVRMGRLalter database backup controlfile to
trace resetlogs;
3. Modify trace file script by doing the following:
a. Remove the header information.
b. Modify the LOGFILE to point to the new names
of the redo logfiles.
c. Modify the DATAFILE to point to the new
names of the data files.
d. Modify the create controlfile statement to:
CREATE CONTROLFILE SET DATABASE DatabaseB
RESETLOGS NOARCHIVELOG
e. Remove the RECOVER DATABASE command.
f. Verify the last line is "ALTER DATABASE OPEN
RESETLOGS;"
4. Shutdown DatabaseA. Make sure that this is a
NORMAL or IMMEDIATE
shutdown.
5. Copy DatabaseA database files and redo logfiles
(dbf,log) to the
DatabaseB
directories. The files may alternatively be
restored from a cold
backup.
6. Copy DatabaseA parameter files to DatabaseB
parameter files by
doing the following:
a. Copy initDatabaseA.ora to initDatabaseB.ora.
b. Copy configDatabaseA.ora to
configDatabaseB.ora.
c. Modify initDatabaseB.ora to point to the
config.ora of DatabaseB.
d. Modify the following parameters in
initDatabaseB.ora:
IFILE = configDatabaseB.ora
CONTROL_FILE = new control files names
DB_NAME = new database name
7. Set the following environment variables:
ORACLE_SID = DatabaseB
ORACLE_HOME = Full pathname of home directory of
DatabaseB
8. Run trace file script via SVRMGRL using connect
internal. The
database is now up and open under the new sid.
DatabaseA may be
restarted.
alter database rename global_name to
DatabaseB.WORLD;
9. Modify other utilities:
SQLNET (i.e. listener.ora, tnsnames.ora)
AUTOMATIC STARTUP (i.e. oratab)
From: S F sf3010@yahoo.com
To: "Kuan, Amy M" kuanam@texaco.com
Cc: oraapps-dba@cpa.qc.ca
Subject: RE:Full database export/import
Thanks Amy,
I think this was the solution I was looking for. Thanks a lot for
helping me out. I would also like to thank all who sent in their
suggestions/comments regarding this topic.
Best regards,
SF
sf3010@yahoo.com
From: Sudershan virdi virdi@interlog.com
To: oraapps-dba@cpa.qc.ca
Subject: Re: Full Database Export/Import
hi
Run the following command in your current database
alter database backup controlfile to trace;.
this will generate a trace file in the udump directory which lists the
structure of your current database and can be used to rebuild the database
on
the other machine.
now copy all the datafiles over to the other machine and place the files
wherever you want(don't worry about the mount points).
copy the trace file generated earlier also to the other machine. Indicate
the
location of the datafiles correctly(put them as per OFA
/mount_point/oradata/database_name/, you will realize the advantage when
having number of instances on the same machine) in this file and run this
file to rebuild the controlfile and the database with the new layout (if you
want you can even change the name of the datafiles from say prod_gl_tab1.dbf
to test_gl_tab1.dbf ). if you want to change the nam of your database
include
the word 'SET' at create controlfile SET database and you will have to open
the database with resetlogs which will start your logs from 1 again.
Then names of the tablespaces associated with the datafiles are in the
headers of the datafiles so logically your database remains same although
you
have placed the datafiles on different mount points or even if you give them
different names. when you rebuild controlfile the disctionary is updated
with
the updated new names of the datafiles(you can check from v$datafile.)
The issue regarding the binary compatibility of the datafiles between
HPUX10.20 and 11 needs to be checked with support,i'm not sure. If that is
the case then you have to go the full export/import way.
Before doing the full import build all the tablespaces with the same name as
your original databases, don't worry about the underlying datafiles(you seem
to be really confused about that), you can name them anything. create the
apps user with proper grants and don'r forget to run adprepdb.sql before you
run the actual import.
Hope this helps
Sudershan Virdi
DBA
Greater Toronto Airport Authority
From: "Timothy Brewer" tbrewer@tbsolns.com
Subject: Re: Full Database Export/Import
Having very recently performed an HP-UX 10.20 to HP-UX 11.0 upgrade for a
client, I can positively make the following comments:
1. You DO NOT have to perform the export/import process unless you like to
work harder for no added benefit. By far, the easiest AND QUICKEST way to
perform the migration is to copy all of the dbf and log files to the new
machine, recreate the control file, and open your database.
2. The EXECUTABLES are not compatible between the two OSs (RDBMS and Apps
executables). You will need to perform a fresh install on the HP-UX 11.0
(RDBMS and Apps) and then reapply all of the patches you've applied on your
HP-UX 10.20 system. Further, since you applied the patches to the database
already, you will only want to perform the OS portion of the patches.
3. The difference in mount points means nothing. Restore the dbfs and log
files to your new desired locations on the new box and then recreate the
control file (where the control file is specifying the correct
datafile/logfile locations).
4. The only issue we found between the two OSs is that UNIX Shell variables
(OE_TOP...) within a sql script is causing memory fault (core) type of
error. Oracle has a proposed patch of which we haven't applied yet but the
temporary workaround was to replace shell variable references with literal
paths (I know, not good idea).
PS - We were at 7.3.4.4. This issue is suppose to be fixed in 8.0.3 I
think.
This really was quite simple and painless.
Good Luck and hope this helps,
Timothy J. Brewer
Senior Oracle Consultant
TB Solutions, Inc.
/* makers of dbchecker*2000(TM) - Oracle date monitoring tool -
www.tbsolns.com */
From: "shankar govindan" shankargovindan@hotmail.com
To: oraapps-dba@cpa.qc.ca
Subject: Re: Full Database Export/Import
sf,
Sudershan is right, you could try his suggestion. Meanwhile are you trying
to tar the complete mount point. So what if your mount points are different
on the new machine. Do not tar obsolute path. Tar only the files and tar -xv
at the different machine. In fact you can copy all the control files
,oratab,env's,init.ora,pwd.ora,et..all. to the new machine and start a
similar database. You can rename the database by running the create
controlfile ang changes to your env's and init's.Even connect to the same
network and copy the files over the network during off office hours.
Shankar
From: CFowler@littonapd.com
To: oraapps-l@cpa.qc.ca
Subject: GEN: Time Synchronization of Servers With Oracle DBs
Hi All,
We are looking into synchronizing the servers that have our Oracle databases
on them. Our DBAs have concerns about changing the time on AIX servers
which contain our Oracle databases. We are trying to find out if there are
any things to watch out for.
If anyone is currently synchronizing or has synchronized servers in the past
containing Oracle databases we would appreciate any information that you may
have. How do you synchronize the servers ?? Do you employ a time server or
what? Currently our two servers are off by approximately 7 minutes from
each other. The two AIX servers are off by 10-15 minutes from the rest of
our network.
We are 10.7 16.1SC AIX 7.3.4.
Thanks In Advance,
Candace
From: "Mark W. Farnham" mwf@rsiz.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: Time Synchronization of Servers With Oracle DBs
Well one fun thing is daylight savings time. If you can use GMT worldwide
and avoid shifting for daylight savings that is nice, but if you are in a
situation where matching the local business time is the user preference, I
recommend shutting everything down for an hour and and a little buffer zone
a bit before the "fall back" time.
Why, you may ask? Well when the clock jumps backward an hour, SYSDATE and
the timestamp on files created or touched in the following hour will be
indistinct from the files created in the previous hour.
Will anything fail if you don't? I'm not sure. "Spring ahead" does not have
the similar problem since it cannot cause later files from appearing to be
earlier files.
Best wishes,
Mark