Performing a Manual Upgrade

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