Home » RDBMS Server » Server Administration » Upgrade from Oracle 9i to Oracle 10g (Oracle 10g on Solaris 9)
Upgrade from Oracle 9i to Oracle 10g [message #434595] Wed, 09 December 2009 20:10 Go to next message
yash1000
Messages: 22
Registered: August 2005
Junior Member
Hi All,

Software:- Oracle 9i,Oracle 10g
OS - Solaris 9.

We have 5 databases in our development enviornment. All the databases(9i) are running on Solaris 9 os. I have given responsibilty to install oracle 10g on solaris box(development enviornment).

Once the 10g gets install then upgrade one of the database running on the 9i to 10g. i.e. migrate all the database objects, and others stuff.


I am Oracle Developer and have never involved in Administration role. I have referred few document over the net to get headup.
1. Oracle 10g Install guide on Solaris
2. Oracle Database Upgrade Oracle 10g.

After reading the document I feel I still don't have enough information on some of issue which I think it may arise.
1. approach for installing 10g
2. How to mirgartion data from 9i to 10g
3. What issues does arises during installation/post installation and so on.
4. Any risk?
5. Backout startegy

I would like to know

1. Install guide for Oracle on solaris box. What precation to take care if there is old version of database running?
2. Regarding backup and transferring data to 10g Should I just copy the datafile and other files to some directory and once 10g is install copy them to 10g respective folder?

3. How would I point the old instance running on 9i database to 10g.
4. Any changes required in .profile, envirornment file

5. How do I make sure the upgrade of database should not affect other instance?.

6. Any link which give me further headups on th current project
Many thanks
7. What startegy should I use to proceed further?

Any suggesstion are most welcome.

Regards,
Yash
icon6.gif  Re: Upgrade from Oracle 9i to Oracle 10g [message #434889 is a reply to message #434595] Fri, 11 December 2009 07:45 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
All your questions are answered in these two books you mention, including the "precautions" you have to take:

1. Oracle 10g Install guide on Solaris
2. Oracle Database Upgrade Oracle 10g.

With regards to your "I would like to know" questions:

Quote:
1. Install guide for Oracle on solaris box. What precation to take care if there is old version of database running?

Read the above 1 and 2 books. Database to be upgraded from old version is to be shutdown.

Quote:
2. Regarding backup and transferring data to 10g Should I just copy the datafile and other files to some directory and once 10g is install copy them to 10g respective folder?

Detailed process is described in book 2. No need to move data to another directory ("folder" is a WinDoze term, YUCK!)

Now, if you do have the data under the Oracle home (sloppiness and bad practice), then you may want to relocate the data files.

Quote:
3. How would I point the old instance running on 9i database to 10g.

You may or may not have to configure access to the database in the tnsnames.ora file(s) on the client side.

Also you need to execute the listener from tha highest version of Oracle home (10g) -- or -- have a different listener per oracle home.

Quote:
4. Any changes required in .profile, envirornment file

Depends. you may need to change the ORACLE_HOME, ORACLE_SID and PATH variables every time you need to work or execute scripts on different database. (check out the "/usr/local/bin/oraenv" script).

Quote:
5. How do I make sure the upgrade of database should not affect other instance?.

Because it will be on a different oracle home and you will have set the ORACLE_HOME, ORACLE_SID and PATH variables correctly each time.

Quote:
6. Any link which give me further headups on th current project

You may want to check the Oracle® Database READMEs and Release Notes 10g Release 2 (10.2) for Solaris Operating System (SPARC 64-Bit) -- there are also release notes for (x86 and x86-64).

Quote:
7. What startegy should I use to proceed further?

Extreme caution and backups after each step.
Razz
Re: Upgrade from Oracle 9i to Oracle 10g [message #435374 is a reply to message #434889] Tue, 15 December 2009 21:38 Go to previous messageGo to next message
yash1000
Messages: 22
Registered: August 2005
Junior Member
My Database :- 9.2.0.7

Thanks for your advise. I gone through the installation guide and upgrade guide. I have confusion over upgrade path to new oracle10g database release?

Please click on the link that talk about upgrade path.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/preup.htm#i1007814

One thing that is confusing me after going through the link is does my database which is on 9.2.0.7 need any more patch? If it requires where can I find minimum patch release for 9.2.0.7? Do I have to install patch releases of 10.1.0.2 on our database and then upgrade to 10.2.0?

I would appreciate if I could get advise on it.

Kind regards,
Yash



Re: Upgrade from Oracle 9i to Oracle 10g [message #435377 is a reply to message #435374] Tue, 15 December 2009 22:10 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
>I would appreciate if I could get advise on it.
You can upgrade directly from V9.2.0.7 to V10.2
Re: Upgrade from Oracle 9i to Oracle 10g [message #435571 is a reply to message #435377] Wed, 16 December 2009 20:45 Go to previous messageGo to next message
yash1000
Messages: 22
Registered: August 2005
Junior Member
I am getting the error when I tried to run the 10g listener.

The listener supports no services
The command completed successfully


I am currently having two listener one for 9i database and other for 10g. I configured the 10g listener with below values with different port number in listener.ora.

I checked entry in /var/opt/oracle/oratab to make sure the database is pointing to 10g.

I change KEY to EXTPROC1 for 10g listener and KEY = EXTPROC for 9i listener.

How do I start 10g listener.9i Listener is runnig fine
LISTENER10G =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1523))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/10.2.0)
(PROGRAM = extproc)
)
)

tnsname.ora

10gdatabase =
(DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(Host= xxxx)(Port= 1523))
(CONNECT_DATA = (SID = CAT))
)



9i Listner.ora details
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/9.2.0.7)
(PROGRAM = extproc)
)
)

Re: Upgrade from Oracle 9i to Oracle 10g [message #435572 is a reply to message #435571] Wed, 16 December 2009 20:54 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

PLEASE read URL above and then use CUT & PASTE so we can see exactly what you do & how Oracle responds.

post results from follow command:

env | sort

Only run 1 listener as below

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test.appstech.local)
(ORACLE_HOME = /d02/app/oracle/product/10.2.0/db_1)
(SID_NAME = test)
)
(SID_DESC =
(GLOBAL_DBNAME = testing.appstech.local)
(ORACLE_HOME = /d01/app/oracle/product/9.2/ora_infra)
(SID_NAME = testing)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host.local)(PORT = 1521))
)
)

[Updated on: Wed, 16 December 2009 21:15]

Report message to a moderator

icon2.gif  Re: Upgrade from Oracle 9i to Oracle 10g [message #435573 is a reply to message #435572] Wed, 16 December 2009 21:52 Go to previous messageGo to next message
yash1000
Messages: 22
Registered: August 2005
Junior Member
Hi,

Thanks for your reply. The reason of running two listener on differen port is because we want to test end to end connectivity with 10g database upgrade without causing any impact on any other development enviornments. Once we happy with the test we would migrate other database to 10g. From that point we will have only one listener running. But at this stage we would like to run multiple listener.

I hope I managed to post reply properly. Anyway below is the output from the following command.
[b]env|sort[/b]
    EDITOR=/bin/vi
    HOME=/opt/oracle
    HOSTNAME=aalsun110
    HOSTTYPE=sparc
    HZ=
    LOGNAME=oracle
    MACHTYPE=sparc-sun-solaris2.9
    MAIL=/var/mail/oracle
    NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
    OLDPWD=/var/opt/oracle
    ORACLE_BASE=/opt/oracle
    ORACLE_HOME=/opt/oracle/product/10.2.0
    ORACLE_SID=CAT
    ORAENV_ASK=YES
    OSTYPE=solaris2.9
    PAGER=pg
PATH=/opt/oracle/product/10.2.0/bin:/sbin:/bin:/usr/bin:/usr/local/bin:/usr/ucb:/etc:.
    PS1=$LOGNAME:$ORACLE_SID:$PWD >
    PWD=/opt/oracle/product/10.2.0/network/admin
    SHELL=/bin/ksh
    SHLVL=1


Moved the listener.ora out of the way.

[b]lsnrctl start[/b]

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 17-DEC-2009 16:36:20

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /opt/oracle/product/10.2.0/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
Log messages written to /opt/oracle/product/10.2.0/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
Start Date                17-DEC-2009 16:36:20
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /opt/oracle/product/10.2.0/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx)(PORT=1521)))
The listener supports no services
The command completed successfully
   


oracle:/opt/oracle/product/10.2.0/network/admin > lsnrctl status

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 17-DEC-2009 16:37:26

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
Start Date                17-DEC-2009 16:36:20
Uptime                    0 days 0 hr. 1 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /opt/oracle/product/10.2.0/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xx)(PORT=1521)))
Services Summary...
Service "ABC" has 1 instance(s).
  Instance "ABC", status READY, has 1 handler(s) for this service...



The ABC is 9i database which the listener has started but it did not started DEF database. Also how do I point the listener to run on different port.

[Updated on: Wed, 16 December 2009 21:59]

Report message to a moderator

Re: Upgrade from Oracle 9i to Oracle 10g [message #435574 is a reply to message #435573] Wed, 16 December 2009 22:11 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
After a connection is made between client & DB, the listener is 100% & totally out of that loop.

Running 1 listener will never "cause any impact on any other development enviornments"

If you insist on (ab)using 2 listeners, then customize the template I posted.
Re: Upgrade from Oracle 9i to Oracle 10g [message #435575 is a reply to message #434595] Wed, 16 December 2009 22:16 Go to previous messageGo to next message
yash1000
Messages: 22
Registered: August 2005
Junior Member
sorry which template are you talking about?
Re: Upgrade from Oracle 9i to Oracle 10g [message #435576 is a reply to message #435575] Wed, 16 December 2009 22:20 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
yash1000 wrote on Wed, 16 December 2009 20:16
sorry which template are you talking about?


SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /d02/app/oracle/product/10.2.0/db_1)
(SID_NAME = test)
)
(SID_DESC =
(ORACLE_HOME = /d01/app/oracle/product/9.2/ora_infra)
(SID_NAME = testing)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host.local)(PORT = 1521))
)
)

change ORACLE_HOME, SID_NAME, & HOST for your environment

Re: Upgrade from Oracle 9i to Oracle 10g [message #435580 is a reply to message #435576] Wed, 16 December 2009 23:26 Go to previous message
yash1000
Messages: 22
Registered: August 2005
Junior Member
Hey that template did the trick. Here are the details I entered in 10g listener.ora file. I stop/start the listener and it is now show all the databases including 10g.

But what confusing to me is listener.ora file was under $ORACLE_HOME/network/admin/sample so how does it manage to recoginze the file as point of entry. Because normally you keep those file under $ORACLE_HOME/network/admin.

I am now going to do end to end testing. Will see what comes later. But I really appreciate for your help.

Many thanks,
Yash

SID_LIST_LISTENER =
(SID_LIST =
   (SID_DESC =
      (ORACLE_HOME =  /opt/oracle/product/10.2.0)
      (SID_NAME = test)
    )
 (SID_DESC =
      (ORACLE_HOME =  /opt/oracle/product/9.2.0.7)
      (SID_NAME = testing)
    )
)
LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx)(PORT = 1521))
      )
    )




Previous Topic: System Tablespace is almost full.
Next Topic: update remote database
Goto Forum:
  


Current Time: Tue Dec 06 00:22:23 CST 2016

Total time taken to generate the page: 0.13515 seconds