Database upgradation 9i to 10g (Manual Method)

1. Connect to the database to be upgraded and run utlu101i.sql to determine the preupgrade
tasks to be completed
SQL> spool /tmp/upgrade.txt
SQL>@$ORACLE_HOME/rdbms/admin/utlu101i.sql
2. Resize the redo log files if they are smaller than 4 MB
3. Adjust the size of the tablespaces where the dictionary objects are stored.
4. Perform a cold backup of the database.
5. Shut down the database (do not perform a SHUTDOWN ABORT; perform only SHUTDOWN
IMMEDIATE or SHUTDOWN NORMAL). On Windows you will have to do NET STOP, ORADIM -
DELETE from the old Oracle home directory and ORADIM -NEW from the new Oracle 10g
home directory
6. Copy the parameter file (initDB.ora or spfileDB.ora) and password file from the old
Oracle home directory to the Oracle 10g Oracle home directory. The default location for
parameter file is $ORACLE_HOME/dbs on Unix platforms and ORACLE_HOME\database on
Windows. Adjust the following parameters:
* Adjust the COMPATIBLE parameter; the minimum value required is 9.2.0 for the upgrade.
If you set this to 10.0, you will never be able to downgrade the database to 9i.
* Update the initialization parameters. You must remove obsolete parameters.
* Set the DB_DOMAIN parameter properly.
* Make sure memory parameters have at least the minimum size required for upgrade:
SHARED_POOL_SIZE (96MB for 32-bit platforms, 144MB for 64-bit), PGA_AGGREGATE_
TARGET (24MB), JAVA_POOL_SIZE (48MB), and LARGE_POOL_SIZE (8MB). Use the
sizes recommended by the preinstall verification utility
7. Make sure all the environment variables are set to correctly reference the Oracle 10g Oracle
home. On Unix, verify ORACLE_HOME, PATH, ORA_NLS33, and LD_LIBRARY_PATH.
8. Use SQL*Plus, and connect to the database using the SYSDBA privilege. Start the instance
by using the STARTUP UPGRADE mode.
9. Create the SYSAUX tablespace with the following attributes:
* online
* permanent
* read write
* extent management local
* segment space management auto
The syntax could be as follows:
CREATE TALESPACE sysaux
DATAFILE ‘/ora01/oradata/OR0109/sysaux.dbf’ SIZE 500M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
10. Run the upgrade script from the $ORACLE_HOME/rdbms/admin directory. Based on the version
of the old database, the name of the upgrade script varies. The following lists the old
release and the upgrade script name:
Database Version Script to Run
8.0.6 —-u0800060.sql
8.1.7 —-u0801070.sql
9.0.1 —-u0900010.sql
9.2.0 —-u0902000.sql
For example, to upgrade an Oracle 8.1.7 database to Oracle 10g, you must run
u0801070.sql.
SQL> spool ora8i7upg.log
SQL> @?/rdbms/admin/u0801070.sql
SQL> spool off
If you get any errors during the upgrade script execution, reexecute the script after fixing
the error. The postupgrade status utility—utlu101s.sql—gives the name of specific script
to run to fix the failed component.
11. Run the utlu101s.sql utility with the TEXT option. It queries the DBA_SERVER_REGISTRY
to determine upgrade status and provides information about invalid or incorrect component
upgrades. It also provides names of scripts to rerun to fix the errors.
Here is an example (output truncated to fit in single line):
ora10g> @$ORACLE_HOME/rdbms/admin/utlu101s.sql TEXT
PL/SQL procedure successfully completed.
12. Shut down and restart the instance to reinitialize the system parameters for normal operation.
The restart also performs Oracle 10g database initialization for JServer Java Virtual
Machine and other components. Perform a clean shutdown (SHUTDOWN IMMEDIATE);
starting the instance flushes all caches, clears buffers, and performs other housekeeping
activities. This is an important step to ensure the integrity and consistency of the upgraded
database.
13. Run the utlrp.sql script to recompile all invalid objects.
ora10g> @$ORACLE_HOME/rdbms/admin/utlrp.sql
14. Update the listener.ora file with the new database information.
15. Back up the database.
Once upgrading the database there will be some degradation in the performance …so check the performance through AWR and ADDM….

Query to see the Reserved, Used and Free space of your database

1. Install Oracle Database 11g, Release 1 in a new
ORACLE_HOME.

2. Analyze the existing database:
– Use rdbms/admin/utlu111i.sql with the existing
server.
– SQL> spool pre_upgrade.log
– SQL> @utlu111i

3. Adjust redo logs and tablespace sizes if necessary.

4. Copy existing initialization files to the new
ORACLE_HOME and make recommended adjustments.

5. Shut down immediately, back

6. Start up using the Oracle Database 11g, Release 1
server:
– SQL> startup upgrade

7. If you are upgrading from 9.2, create a SYSAUX
tablespace:
– SQL> create tablespace SYSAUX datafile
'e:\oracle\oradata\empdb\sysaux01.dbf'
size 500M reuse
extent management local
segment space management auto
online;

8. Run the upgrade (automatically shuts down database):
– SQL> spool upgrade.log
– SQL> @catupgrd.sqlup

9. Restart the database instance in normal mode:
– SQL> startup

10.Run the Post-Upgrade Status Tool to display the
results of the upgrade:
– SQL>@utlu111s.sql

11.Run post-upgrade actions:
– SQL> @catuppst.sql

12.Recompile and revalidate any remaining application
objects:
– SQL> @utlrp (parallel compile on multiprocessor
system)


Downgrading a Database


1. Major release downgrades are supported back to 10.2 and
10.1.

2. Downgrade to only the release from which you upgraded.

3. Shut down and start up the instance in DOWNGRADE mode:
– SQL> startup downgrade

4. Run the downgrade script, which automatically determines
the version of the database and calls the specific component
scripts:
– SQL> SPOOL downgrade.log
– SQL> @catdwgrd.sql

5. Shut down the database immediately after the downgrade
script ends:
– SQL> shutdown immediate;

6. Move to the old ORACLE_HOME environment and start up the
database in the upgrade mode:
– SQL> startup upgrade

7. Reload the old packages and views:
– SQL> SPOOL reload.sql
– SQL> @catrelod.sql

8. Shut down and restart the instance for normal operation:
– SQL> shutdown immediate;
– SQL> startup

9. Run utlrp.sql to recompile all existing packages,
procedures, and types that were previously in an INVALID
state:
– SQL> @utlrp

10. Perform any necessary post-downgrade tasks.

Referance from Oracle..

Query to see the Reserved, Used and Free space of your database

SELECT r.tablespace_name, reserved_space "RESERVED_SPACE(MB)",
reserved_space - free_space "USED_SPACE(MB)",
free_space "FREE_SPACE(MB)"
FROM (SELECT tablespace_name, SUM (BYTES)
/ (1024 * 1024) reserved_space
FROM dba_data_files
GROUP BY tablespace_name) r,
(SELECT tablespace_name, SUM (BYTES) / (1024 * 1024) free_space
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE r.tablespace_name = f.tablespace_name
ORDER BY r.tablespace_name;

Manually Resolve archive GAP

Manually Resolve archive GAP

When ever there is a GAP, check the alert logfile for any errors on the Physical or Logical Standby database. If the logfiles are not shipped to physical or logical automatically, then follow these steps and transfer all the log files.

1. Identify the ARCHIVE GAP, ARCHIVE LOG Sequence and THREAD

2. Connect RMAN on PRIMARY Database

3. Backup a copy of ARCHIVELOG TO DISK

BACKUP AS COPY ARCHIVELOG SEQUENCE 3912 THREAD 1 FORMAT ‘/D01/3911.ARC’;

4. Transfer Files to Standby Server

a. SCP – Secure copy

scp filename1 userid@hostname:filename2

b.

5. REGISTER LOGFILE

a. ALTER DATABASE REGISTER LOGFILE ‘XYZ’;

b. ALTER DATABASE REGISTER LOGICAL LOGFILE ‘XYZ’; ----- For LOGICAL

6. Selelct * from v$managed_standby; -- MRP process should be applying the log file

Look for MRP process trace file for additional info.

7. Select * from v$archive_gap; -- To find the GAP