Home » RDBMS Server » Server Utilities » Migrate 10gR2 to 11gR2 on a different server (10gR2 on Win2003 32 / 11gR2 on Win2008 64)
 Migrate 10gR2 to 11gR2 on a different server [message #515422] |
Mon, 11 July 2011 08:47  |
 |
fribergb
Messages: 9
Registered: July 2011
Location: Kuwait City
|
Junior Member |
|
|
Hey all,
Here are the cards I've been dealt:
I've inherited a 10.2.0.1.0 instance running on a windows 2003 box; running fine, no problems other than system has been in production since 2005 and has gotten pretty old and tired. This old box has one tablespace on it... called "gateway".
I've installed 11.2.0.1 on a new (Windows 2008 R2 Enterprise 64-bit) server and created an empty database also called "gateway".
Now to move the data and views, objects, everything.
I've read up on a variety of migration techniques (oops, I mean "upgradation" LOL) and can follow the steps... but I'd really like to know what you folks suggest as the best / most complete / easiest.
In short, I want to pull everything off of server a (10.2) and put it into production on server b (11.2). There seems to be quit a few options...
1. install 10.2 on my NEW server (server b), move the data over and get everything running, then install 11.2 and have it upgrade the database as part of the installation process
2. drop the empty tablespace on server b, stop the database on server a, copy the files over from the old to the new home, run DBUA or set the compatibility attribute...
3. run some type of server a to server b utility that can bridge the two over the network. Some type of mirroring technique?
4. run file export scripts on server a, copy files to server b and run various import scripts
Frankly, (tho I am a newb) I tend to think that option 3 would be the best because both instances are in great health and are running right now.
Is there a mechanism that allows the 11.2 instance to see and upgrade from a different server???
What would you all suggest in this situation??
Thanks much, I appreciate the help...
-B
|
|
|
|
| Re: Migrate 10gR2 to 11gR2 on a different server [message #515427 is a reply to message #515422] |
Mon, 11 July 2011 09:02  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>>1. install 10.2 on my NEW server (server b), move the data over and get everything running, then install 11.2 and have it upgrade the database as part of the installation process
Done it quite a few times.
Edit:
I use RMAN to clone to new server 10g.
patch 10.2.0.1 to 10.2.0.4 and upgrade database.
install 11gR2 and upgrade to 11g.
You need to patch up to 10.2.0.4 (atleast) first and upgrade to 11gR2.
>>2. drop the empty tablespace ...
Not sure if it would work for above said reason.
>>3. run some type of server a to server b utility ...
Dblinks/MV's? Too much work and not sure how it behaves between versions.
>>4. run file export scripts on server a, ....
Works in most cases.
If the volume of data is large, pain.
Some restrictions apply.
[Updated on: Mon, 11 July 2011 09:05] Report message to a moderator |
|
|
|
|
|
| Re: Migrate 10gR2 to 11gR2 on a different server [message #515441 is a reply to message #515428] |
Mon, 11 July 2011 10:05 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Hi, man. I would normally recommend your option 4: use Data Pump to get the relevant data out of 10g and into 11g. I like it because it gives you the opportunity to configure all the new features that you might want to implement.
But you could have a problem with downtime, a Data Pump transfer can take a long time compared to the other techniques, particularly if you are on Standard Edition and so can't use parallel workers. You can do quite a lot of tuning, so don't give up if your first test is too slow.
|
|
|
|
| Re: Migrate 10gR2 to 11gR2 on a different server [message #515672 is a reply to message #515428] |
Tue, 12 July 2011 20:03 |
 |
rsager
Messages: 17
Registered: June 2011
Location: Sydney, Australia
|
Junior Member |
|
|
Hey fribergb...
It is difficult to give you a definitive answer as it depends on a number of things. Anyways....I'll make a stab..
The best option going from platform to platform with the basic binaries that are already installed is the export (exp) and import (imp). I have move from Solaris 8i base release to an 11g on Linux with a few modificiation to PL/SQL packages because of the difference in releases...however, that was minor (in my case). And many iteration of that ...
To make the move from your source server a (old and tried server) to the new target server b (with whatever DB release you install)...do some of the investigations on the source database ....first
1. Find out all the users in the source by doing a describe on the dba_users ( SQL> desc dba_users) when logged on a sys.
Don't worry about the seeded users (like sys, system, scott, etc) just those that have their default tablespace (tblsp) set to the "gateway" one.
2. You'll need to recreate that tblsp with the correct size etc on the new target db, then recreate those users with the same grants and privs.
3. Back on the source node create a simple .bat file containing something like the following:
----------GATEWAY_EXPORT.BAT------------
set ORACLE_SID=GATEWAY
set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
C:\Oracle\Ora10g\product\10.2.0\db_1\BIN\exp 'sys/password@gateway as sysdba' owner=(Schema_user1) file=C:\Oracle\Ora10g\admin\GATWAY\Export_Imports\GATEWAY_export.dmp log=C:\Oracle\Ora10g\admin\GATWAY\Export_Imports\GATEWAY_export.log
pause
-------------------------
Now the NLS_LANG setting is important in both the exp and imp to avoid stat errors....you can find out the NLS settings but issuing a SQLPLUS command (connected as sys) as
SQL> select * from NLS_DATABASE_PARAMETERS;
...look for....
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET WE8MSWIN1252
...
to get the settings in the GATEWAY_EXPORT.BAT for NLS_LANG=
In the bat file for the "owner=(user1, user2, ...)" parameter, list all those dba_users you which to export, rather than full=y. As I assume you are only interested in migrating those user's objects to the new target instance.
---------
Once you have an error free export (see the log for its results), copy the file to the target node and configure a GATEWAY_IMPORT.bat.. such as
--------------GATEWAY_IMPORT.bat-------
set ORACLE_SID=GATWAY
REM "Case on sys user import really important"
C:\Oracle\Ora11g\product\11.2.0\db_1\BIN\imp 'sys/password@gateway as sysdba' fromuser=(Schema_user 1, ....) touser=(Schema_user1...) file=C:\Oracle\Ora11g\admin\GATEWAY\Export_Imports\GATEWAY_export.dmp log=C:\Oracle\Ora11g\admin\GATEWAY\Export_Imports\GATEWAY_import.log
pause
-----------------
Also, check the dba_objects on both the source and target for invalid objects to ensure the transfer is as you expect.
By creating the users and tblsp on the target will help the import go smoothly.
I hope that gives you a start?
Cheers
Roger
|
|
|
|
|
|
|
|
| Re: Migrate 10gR2 to 11gR2 on a different server [message #515753 is a reply to message #515724] |
Wed, 13 July 2011 03:16 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Data Pump will go from 10.2 to 11.2 no problem. If the export is taken while the database is in use, it must be a consistent export, or it will probably be useless. I don't know the syntax for Windows, this is a Solaris example of specifying the time for consistency:
oracle@maputo> expdp directory=dpout full=y dumpfile=dpout:ziprodfull%U filesize=8g FLASHBACK_TIME=\"TO_TIMESTAMP\(\'13/07/2011 00:15:00\',\'DD/MM/YYYY HH24:MI:SS\'\)\"
If the export takes a few hours and the database is busy, you may need to set your undo_retention parameter to as long as it takes, and have an undo tablespace the size of Jupiter.
[update: added code tags, before I get flamed]
[Updated on: Wed, 13 July 2011 03:17] Report message to a moderator |
|
|
|
| Re: Migrate 10gR2 to 11gR2 on a different server [message #515864 is a reply to message #515724] |
Wed, 13 July 2011 18:16 |
|
rsager
Messages: 17
Registered: June 2011
Location: Sydney, Australia
|
Junior Member |
|
|
Using Oracle tools is a bit like life...every situation is different....thru the years Oracle has added various tools for Database management etc, but in my 10+ years I find the export and import utilities the easiest and more reliable way going from
one platform (such as Window to Solaris to Linux Redhat)
and from older 8i base directly 11g or whatever. Now every DBA has their own experiences and ways of doing things, and I am sure over time you'll find ways that suit you. However, if the developer or previous DBA wants to simply move the database files from one server to another on the same platform (ie in your case windows, albeit the 32-bit to 64-bit may give you a wee bit of trouble), then it is similar to cloning.
On the source node/tier: log into the instance as the sys user
(conn /as sysdba) and execute the following command
SQL> alter database backup controlfile to trace;
This statement will create a file (usually in the user_dump_dest directory
use sql to find it:
SQL> set lines 132
SQL> set pages 999
SQL> col name for a20
SQL> col value for a60
SQL> show parameter udump
SQL> select name, value from v$parameter where name like '%dump%'; )
The above path is where to find the trace in a format similar to <sid>_ora_<#>.trc
You can use this trace file (with modification) to recreate the database using the data files copied to the target node/tier.
So if you laid down a sample instance on the target, and shut it down, stop all services on the target (Listener, OracleService<SID> ,etc) then replace the sample database
files for that instance with the files from the source instance use can modify the trace file created earlier to accommodate new directory paths etc for the new instance. You can alter many things in the target via this controlfile trace...like the number of redo groups and members of, the SID name etc...search google or this forum for example...there are lots of variations when cloning. I have put a sample of a trace file modified to create an instance on the target using copied datafiles. Also, you will need the source init<SID>.ora file to use with the target instance being created. Generally, the source will be using the binary spfile<SID>.ora file, so again on the source instance connect as sys execute sql statement like my example:
SQL> create pfile='D:\oracle\product\10.2.0\db_1\database\initPTLIVE09.ora' from spfile;
Again, you may have to modifiy the init<SID>.ora for the target. The modified trace file will like similar to my example below:
sqlplus /nolog
spool D:\oracle\admin\PTLIVE09\scripts\Create_PTLIVE09_log.lis
set lines 132
set pages 999
set sqlp SYS_on_PTLIVE09>
conn sys/<password>@ptlive09 as sysdba
shutdown abort
STARTUP NOMOUNT pfile=D:\oracle\product\10.2.0\db_1\database\initPTLIVE09.ora
CREATE CONTROLFILE REUSE SET DATABASE "PTLIVE09" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 ('D:\ORACLE\ORADATA\PTLIVE09\Multiplex_redos\REDO01A.LOG',
'C:\ORACLE\ORADATA\PTLIVE09\Multiplex_redos\REDO01B.LOG' ) SIZE 100M,
GROUP 2 ('D:\ORACLE\ORADATA\PTLIVE09\Multiplex_redos\REDO02A.LOG',
'C:\ORACLE\ORADATA\PTLIVE09\Multiplex_redos\REDO02B.LOG' ) SIZE 100M,
GROUP 3 ('D:\ORACLE\ORADATA\PTLIVE09\Multiplex_redos\REDO03A.LOG',
'C:\ORACLE\ORADATA\PTLIVE09\Multiplex_redos\REDO03B.LOG' ) SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'D:\ORACLE\ORADATA\PTLIVE09\SYSTEM01.DBF',
'D:\ORACLE\ORADATA\PTLIVE09\UNDOTBS01.DBF',
'D:\ORACLE\ORADATA\PTLIVE09\PTLIVE_DATA01.DBF',
'D:\ORACLE\ORADATA\PTLIVE09\PTLIVE_INDEX01.DBF',
'D:\ORACLE\ORADATA\PTLIVE09\USERS01.DBF',
'D:\ORACLE\ORADATA\PTLIVE09\SYSAUX01.DBF',
'D:\ORACLE\ORADATA\PTLIVE09\DTIME_DATA01.DBF',
'D:\ORACLE\ORADATA\PTLIVE09\DTIME_INDEX01.DBF',
'D:\ORACLE\ORADATA\PTLIVE09\P2B_DATA01.DBF',
'D:\ORACLE\ORADATA\PTLIVE09\P2B_INDEX01.DBF'
CHARACTER SET WE8MSWIN1252;
# Database can now be opened normally.
ALTER DATABASE OPEN resetlogs;
#recover database using backup controlfile until cancel;
#D:\oracle\oradata\PTLIVE07\archive\PTLIVE07_1_1177_607087770.LOG
#D:\oracle\oradata\PTLIVE07\archive\PTLIVE07_1_1178_607087770.LOG
#cancel
#ALTER DATABASE OPEN resetlogs;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLE\ORADATA\PTLIVE09\TEMP01.DBF'
SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
# End of tempfile additions.
# Perform checks....
set pages 999
set lines 132
show user
select name, open_mode, log_mode from v$database;
select * from v$version;
col comp_name for a40
select comp_name, version, status from dba_registry;
col name for a55
Select file#, status, name from v$datafile;
select group#, thread#, members, archived, status from v$log;
col member for a55
select group#, status, member from v$logfile order by group#, member;
col object_name for a30
col object_type for a30
col owner for a12
select owner, status, object_name, object_type from dba_objects
where status !='VALID' order by owner, object_name;
create spfile from pfile='D:\oracle\product\10.2.0\db_1\database\initPTLIVE09.ora';
alter user sys identified by <newpassword>;
alter user system identified by <newpassword>;
shutdown immediate
startup
alter database rename global_name to "PTLIVE09";
------------------------
also, if might be wise to put the trace into a sql script file that you can execute from the SQL> prompt as you may have syntax issue to solve until you get it right.
Good luck
Cheers
Roger
|
|
|
|
| Re: Migrate 10gR2 to 11gR2 on a different server [message #516082 is a reply to message #515864] |
Fri, 15 July 2011 00:55 |
|
fribergb
Messages: 9
Registered: July 2011
Location: Kuwait City
|
Junior Member |
|
|
All,
Thanks very much for both the insight and the detailed examples!! You guys have been the best and I've been successful because of the complete
answers and the kindness to a newb.
I tried a lot of different things and learned a lot, but was ultimately successful with plain old IMP and EXP. Yes the language and the character set
bit me and caused some hair-pulling. But yesterday i was able to import my first clean instance on the new Windows 2008 R2 super server. SWEET!!!
Roger, thanks for the great examples... if anything is wrong with it I'll try that next. But I think I won this round.
Here's what I learned:
1. Character set can be selected but not after you do a default installation of 11g R2; you have to delete and re-create the database
2. Creating tablespace and users is a breeze if the developer before you took a moment to document the foundational users, roles and priveledges;
this should be done by everyone that supports an application that uses a base user account for connection to the RDBMS
3. After creating tablespace, users, roles and priv's, an IMP is a complete solution for all objects but the character sets and language settings need
to match or data truncation or corruption occurs. Lots of posts on this; makes perfect sense why
Have a great day, all, and thanks again!!!!
-B
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 09:30:07 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
