Upgrading 9iR2 to 10gR2 using hotbackup

Upgrading 9iR2 to 10gR2 using hotbackup

Abstract

The document is aimed at describing the step by step procedure that was followed to upgrade a database from Oracle 9iR2 to 10gR2 using hot backup of 9iR2 database. The basis of this upgrade is a hot backup clone.

Case History

The database to be upgraded is about 185GB in size, this activity was tested on a development system to validate the success of this exercise before proceeding with the production environment.


Analysis

Summary

A hot backup was used to create a database on 10gR2 by recreating the controlfile.

Detailed Analysis

1. A password file was initially created in $ORACLE_HOME/dbs of 10gR2 for this particular instance using the orapwd utility. The pfile from the 9i database was used to start the instance by just changing the compatible parameter to 10.2.0 and no other changes were made to the init.ora file since all the deprecated and obsolete parameters will be listed in the alert log once the database is brought up which can be removed later. The instance was now started.

SQL> startup nomount

This step can also be executed as shown below, which is an observation from the alert log when the database failed to open with an error of “ORA-39700: database must be opened with UPGRADE option" preceded with an error of "ORA-00704: bootstrap process failure"

SQL> startup upgrade

2. The next step is to execute the control file recreation script in order to create the controlfile. This is a text controlfile script which was dumped to a trace file from the production environment and updated to reflect the locations of files in the development server.

SQL> !cat control_9_to_10.sql

CREATE CONTROLFILE SET DATABASE "TEN_DB" RESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 '/home/oracle/dev/log1a.rdo' SIZE 200M,
GROUP 2 '/home/oracle/dev/log2a.rdo' SIZE 200M,
GROUP 3 '/home/oracle/dev/log3a.rdo' SIZE 200M,
GROUP 4 '/home/oracle/dev/log4a.rdo' SIZE 200M
DATAFILE
'/home/oracle/dev/data01.dbf',
'/home/oracle/dev/data02.dbf',
'/home/oracle/dev/data03.dbf',
'/home/oracle/dev/users.dbf',
'/home/oracle/dev/tools.dbf',
'/home/oracle/dev/system01.dbf',
'/home/oracle/dev/undotbs01.dbf',
'/home/oracle/dev/undotbs02.dbf',
'/home/oracle/dev/undotbs03.dbf',
'/home/oracle/dev/index01.dbf',
'/home/oracle/dev/index02.dbf',
'/home/oracle/dev/index04.dbf',
'/home/oracle/dev/index05.dbf',
'/home/oracle/dev/index06.dbf',
'/home/oracle/dev/index03.dbf',
'/home/oracle/dev/index07.dbf',
'/home/oracle/dev/data04.dbf',
'/home/oracle/dev/data05.dbf',
'/home/oracle/dev/data06.dbf',
'/home/oracle/dev/data07.dbf',
'/home/oracle/dev/data08.dbf',
'/home/oracle/dev/data09.dbf',
'/home/oracle/dev/data10.dbf',
'/home/oracle/dev/data11.dbf',
'/home/oracle/dev/data12.dbf',
'/home/oracle/dev/data13.dbf',
'/home/oracle/dev/data14.dbf',
'/home/oracle/dev/data15.dbf',
'/home/oracle/dev/data16.dbf',
'/home/oracle/dev/data17.dbf'
CHARACTER SET US7ASCII;

SQL> @control_9_to_10.sql

Control file created.

3. The database was then recovered to the most recent point of time (until the last available archive file). Since the controlfile was recreated so it was very much necessary to use the keywords “using backup controlfile” during recovery. The files marked in bold are the archives which were applied for recovery.

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 79118 generated at 08/06/2007 15:43:45 needed for thread 1
ORA-00289: suggestion :
/home/oracle/dev/arch/1_43_629467995.dbf
ORA-00280: change 79118 for thread 1 is in sequence #43

Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/dev/arch/1_43_629467995.dbf

ORA-00279: change 79120 generated at 08/06/2007 15:43:51 needed for thread 1
ORA-00289: suggestion :
/home/oracle/dev/arch/1_44_629467995.dbf
ORA-00280: change 79120 for thread 1 is in sequence #44
ORA-00278: log file '/home/oracle/dev/arch/1_43.dbf' no
longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/dev/arch/1_44_629467995.dbf

ORA-00279: change 79122 generated at 08/06/2007 15:43:51 needed for thread 1
ORA-00289: suggestion :
/home/oracle/dev/arch/1_45_629467995.dbf
ORA-00280: change 79122 for thread 1 is in sequence #45
ORA-00278: log file '/home/oracle/dev/arch/1_44.dbf' no
longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL>

4. This is the most daunting step one would go through, because a normal “alter database open resetlogs” reported an error of "ORA-39700: database must be opened with UPGRADE option" in the alert log and an error of "ORA-01092: ORACLE instance terminated. Disconnection forced" at the SQL prompt. It was improvised from this point that a "startup upgrade" can also be used to start the instance. As for the “ORA-39700” error in alert log, the statement shown below was executed and it surprisingly worked.

SQL> alter database open resetlogs upgrade;

Database altered.

5. Since there is no concept of SYSAUX tablespace in 9i and which is one of the essential tablespaces in 10g, a SYSAUX tablespace was then created manually as shown below.

SQL> create tablespace SYSAUX datafile '/home/oracle/dev/sysaux.dbf' size 500M online permanent extent management local segment space management auto;

Tablespace created.

6. A temporary tablespace was created since it was not included in the controlfile recreation script, and made the default temporary tablespace.

SQL> create temporary tablespace temp tempfile
'/home/oracle/dev/temp01.dbf' size 8000M;

Tablespace created.

SQL> alter database default temporary tablespace temp;

Database altered.

7. Now, When trying to query the dba or v$ views, the database would not respond. When trying to interrupt using the traditional interrupt keys on UNIX "Ctrl+c" an error of ORA-600 was reported at the SQL prompt as shown.

SQL> select file_name from dba_data_files;

ORA-00600: internal error code, arguments: [17069], 0xC00000002B8EDDA0],[], [], [], [], [], []

To start it all over again the database was bounced but it would not come up with a normal "startup" and only accepts "startup upgrade" to come up. This is where it was located from the discussion threads of Metalink that in doing an upgrade from 9i to 10g, the data dictionary needs to be synchronized with the version of the database. The Oracle sever makes a match between the database version and data dictionary every time it opens the database thereby only allowing a “startup upgrade" rather than a normal “startup” to bring the database up.

It was suggested from another discussion thread of Metalink that catupgrd.sql script needed to be executed from 10gR2 $ORACLE_HOME/rdbms/admin to achieve the synchronization. The script ran for a while and completed successfully.

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

The following script was executed for the utility upgrade status check which was successful as well.

SQL> @$ORACLE_HOME/rdbms/admin/utlu102s.sql

The database was bounced yet again and this time it did come up with a normal "startup". The script utlrp.sql was executed a couple of times to recompile all the invalid objects. The global name of the database was then changed to reflect the new name because creating a database with a control file recreation keeps the global_name of the old database unchanged as shown below.

SQL> select * from global_name;

GLOBAL_NAME
-----------------------------------------------------------
NINE_DB.TEST.COM

The global_name was changed by running the following statement which marked the successful completion of upgrade from Oracle 9iR2 to 10gR2.

SQL> alter database rename global_name to TEN_DB.TEST.COM;

Note: If the upgrade is from Oracle 9iR2 to 10gR1 then the synchronization is done by executing u0902000.sql and the utility upgrade status is checked by executing utlu101s.sql from 10gR1 $ORACLE_HOME/rdbms/admin.(Metalink Note: 263809.1)

10gR1 $ORACLE_HOME/rdbms/admin/u0902000.sql --- to upgrade to 10.1.0
which is renamed as catupgrd.sql in 10gR2

10gR1 $ORACLE_HOME/rdbms/admin/utlu101s.sql --- to upgrade to 10.1.0
which is also renamed as utlu102s.sql in 10gR2


Conclusion

A simple concept of cloning a database has been a vital step for the upgrade of an Oracle server version, which not only reduces the downtime for the upgrade but also is achievable with least overhead.

Author: Nawaz Ahmed M.A.S., System Engineer, GAVS Information Services, India. (Metalink Note id: 455990.1)

Configuration of kernel parameters

Configuring Kernel Parameters

If the value of any kernel parameter is different from the recommended value, then complete the following procedure:

Using any text editor, create or edit the /etc/sysctl.conf file, and add or edit lines similar to the following:

Note:

Include lines only for the kernel parameter values that you want to change. For the semaphore parameters (kernel.sem), you must specify all four values. However, if any of the current values are larger than the recommended value, then specify the larger value.

fs.file-max = 512 * PROCESSES
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144

Note: If the current value for any parameter is higher than the value listed in this table, then do not change the value of that parameter.

By specifying the values in the /etc/sysctl.conf file, they persist when you restart the system.

Enter the following command to change the current values of the kernel parameters:

# /sbin/sysctl -p

Setting Shell Limits for the oracle User

To improve the performance of the software on Linux systems, you must increase the following shell limits for the oracle user:

Shell Limit Item in limits.conf Hard Limit

Maximum number of open file descriptors nofile 65536
Maximum number of processes available to a single user nproc 16384

To increase the shell limits:

Add the following lines to the /etc/security/limits.conf file:

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

Add or edit the following line in the /etc/pam.d/login file, if it does not already exist:

session required /lib/security/pam_limits.so
session required pam_limits.so

Note:

For 64-bit platforms, the following entry is not required in the /etc/pam.d/login file:

session required /lib/security/pam_limits.so

Depending on the oracle user's default shell, make the following changes to the default shell start-up file:

For the Bourne, Bash, or Korn shell, add the following lines to the /etc/profile file (or the file on SUSE systems)/etc/profile.local:

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

For the C shell (csh or tcsh), add the following lines to the /etc/csh.login file (or the file on SUSE systems)/etc/csh.login.local:

if ( $USER == "oracle" ) then
limit maxproc 16384
limit descriptors 65536
endif

Scheduling backup using Crontab in Linux

Edit the crontab using following command and add the line as below:

$crontab -e

0 06,18 * * * sh /home/oracle/Desktop/bkup.sh

Scheduling backup twice a day at 6 in the morning and 6 in the evening.

Contents of bkup.sh
-------------------
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=edr
mv /home/oracle/edrbkup/old_edr.dmp /home/oracle/edrbkup/old_bkup/
mv /home/oracle/edrbkup/old_edr.log /home/oracle/edrbkup/old_bkup/
mv /home/oracle/edrbkup/edr.dmp /home/oracle/edrbkup/old_edr.dmp
mv /home/oracle/edrbkup/edr.log /home/oracle/edrbkup/old_edr.log
exp edr/regp file=/home/oracle/edrbkup/edr.dmp log=/home/oracle/edrbkup/edr.log owner=edr buffer=200000

This script will automatically clean the old backup and maintains three backups' at any given point of time.

cron commands:

$crontab -l To list the cron jobs scheduled
$crontab -r To remove the cron job
$crontab -e To edit/schedule cron jobs

General Overview:

Crontab Environment

cron invokes the command from the user's HOME directory with the shell, /usr/bin/sh).
cron supplies a default environment for every shell, defining:
HOME=user's-home-directory
LOGNAME=user's-login-id
PATH=/usr/bin:/usr/sbin:.
SHELL=/usr/bin/sh

Users who desire to have their .profile executed must explicitly do so in the crontab entry or in a script called by the entry.


* * * * * command to be executed
- - - - -
| | | | |
| | | | +----- day of week (0 - 6) (sunday = 0)
| | | +------- month (1 - 12)
| | +--------- day of month (1 - 31)
| +----------- hour (0 - 23)
+------------- min (0 - 59)

Hope this may help you.

Creating a Samba Share on a Linux box for a Windows box in network

[root@oraclesrvr ~]# mkdir /regp
[root@oraclesrvr ~]# useradd regpadmin
[root@oraclesrvr ~]# useradd regpuser
[root@oraclesrvr ~]# passwd regpadmin
[root@oraclesrvr ~]# passwd regpuser
[root@oraclesrvr ~]# chown -R regpadmin:regpadmin /regp
[root@oraclesrvr ~]# chmod -R 777 /regp


[root@oraclesrvr ~]# vi /etc/samba/smb.conf

Edit the following lines which are present at the end of file accordingly:
[regp]
comment = regp user stuff
path = /regp/
valid users = regpadmin regpuser
public = no
writable = yes
printable = no
create mask = 0765
:wq

[root@oraclesrvr ~]# cat /etc/passwd | mksmbpasswd.sh > /etc/samba/smbpasswd
[root@oraclesrvr ~]# chmod 600 /etc/samba/smbpasswd
[root@oraclesrvr ~]# smbpasswd regpadmin
[root@oraclesrvr ~]# smbpasswd regpuser
[root@oraclesrvr ~]# service smb restart

Now connect to the linux box from any windows machine in same network and try to create,modify or delete any file in the share created by you.
These permissions can be set according to your requirements.

Very Large Memory

Creation of Database With Very Large Memory

Once the Oracle software is installed successfully, then the next major step is to create the database:

This document deals with database with Very Large Memory option i.e., allocating more than 1.7GB of SGA on a 32 bit Windows server.


In Windows 32 bit architechture(x86) there is limit for 2GB of memory usage that Windows will allow for any single process to consume.
And the databases in which a large amount of i/o takesplace needs much more memory than this limit. So, to allocate and allow oracle to use more than 2GB of memory we use VLM.

1. Open your boot.ini file and add /PAE and /3GB switches.

2. Install the Oracle Software.

3. Open the regedit file

start->run->regedit->find ORACLE_HOME key this will be in HKEY_LOCAL_MACHINE-->software-->oracle-->key_oracle_home(what ever your home is)

in the right hand pane right click and create a new string value with name AWE_WINDOW_MEMORY

Then double click thih new string and give the vaule in bytes that you require.
By default the value of this string is 1GB.


4. Restart the server to make this changes take effect.

5. create the database with memory greater than the 1.7GB SGA.

While allocating the SGA parameter values take care that you give the following parameter values:

Parameter NAME VALUE
-------------- -----
db_cache_size =0
db_2k_cahe_size =null
db_4k_cahe_size =null
db_8k_cahe_size =null
db_16k_cahe_size =null
db_32k_cahe_size =null
db_cache_advice =OFF

db_block_buffers =262144(2GB) i.e (262144*8192)
db_block_size =8192

shared_pool_size =600M
large_pool_size =48M
java_pool_size =24M
pga_aggregate_target =700M
sga_target =0
used_indirect_buffers=TRUE

Note:db-block_buffres is calculated with the following formula(max that can be allocated):

==>Your Server Physical Ram - 4GB + AWE_WINDOW_MEMORY(1GB)

So for example if your System Physical RAM is 9GB, then according to formula:

9-4+1=6GB

You can allocate upto 6GB value for your db_block_buffers.

Oracle 10G Installation on Red Hat Linux

First Install Linux with all the rpms and packages required.

1. Create Groups oinstall & dba, and oracle user as follows:

#groupadd oinstall
#groupadd dba
#useradd -g oinstall -G dba oracle
#passwd oracle

give a new password

2. Edit the .bash_profile file of oracle user and add the following lines as follows:

#vi /home/oracle/.bash_profile

umask 022

PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
LD_LIBRARY_PATH=/usr/lib:/usr/X11R6/lib

ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
ORACLE_SID='SERVICE_NAME'
PATH=$ORACLE_HOME/bin:$PATH

export PATH LD_LIBRARY_PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID

:wq(save and exit)

3. Then run the .bash_profile to make the current changes effect

#. .bash_profile

Create the directory for the software installation and assign ownership to oracle:oinstall. In the example, you use /u01/app/oracle.

#mkdir -p /u01/app/oracle
#mkdir -p /u01/app/oracle/product/10.1.0/db_1

Then change the permissions accordingly

#chown -R oracle:oinstall /u01/app
#chmod -R 775 /u01/app

4. Edit the /etc/inittab file and change the runlevel from 5 to 3.
Note: Not required if using Oracle's Enterprise Linux.

5. If your OS is greater than RHEL 3, then the Oracle Universal Installer does not recognize the OS, change the version to 3 as follows:

#cp /etc/redhat-release /etc/redhat-release.orig
#vi /etc/redhat-release

#And replace the line present in the file with
Red Hat Enterprise Linux AS release 3 (Taroon)
:wq

Once the installation is completed replace the orignal file back.

#cp /etc/redhat-release.orig /etc/redhat-release

overwrite:yes

6. Change the Kernel Parameters according to your Hardware:

#vi /etc/sysctl.conf

kernel.sem= 250 32000 100 128
kernel.shmmax = 2147483648
kernal.shmmni = 4096
kernel.shmall = 262144000
fs.file-max= 1024
net.ipv4.ip_local_port_range= 1024 65000

:wq

Note: If any of the value is more than the value given above don't change those values.

7. To make the changes effect immediately without reboot give the following command:

#/sbin/sysctl -p

8. REBOOT(init 6) the system and login as oracle User:

9. You need to install Oracle in the GUI mode so change the display mode to GUI by giving followinh command:

$startx

10. Mount the media disk which contains the Oracle 10G software:
Note: It is recommended that you copy the contents of the CD onto the Filesystem and start the installation:

$cp /mnt/disk1/*.* /home/oracle/oracle10g

Give total permissions on the newly created folder oracle10g to oracle user

$chmod -R 777 /home/oracle/oracle10g

11. Then Start the installation as follows:

$sh /home/oracle/oracle10g/runinstaller.sh

The OUI is started and continue Installation of Oracle same as in Windows:

next>next>next>.....................................Finish.

Inbetween it prompts you to run two scripts as root

DON'T close the current window until you run the respected script as root in a new terminal.

1st script:

#root>sh /u01/app/oracle/oraInventory/orainstall.sh

After executing this script as root go to the previous screen and press the continue tab

2nd script:

#root> sh /u01/app/oracle/product/10.1.0/db_1/root.sh

After executing this script as root go to the previous screen and press the OK tab.


Your Installation of Oracle 10G on Linux is completed successfully(hopefully).