How to do deployment for Oracle database

Jephe Wu - http://linuxtechres.blogspot.com

Objective: how to do deployment properly for a production Oracle 11g database
Environment: RHEL 5 64bit + Oracle 11g 64bit


Steps:
1. Check if any user is using the database
The easy way to check is to go to OEM(Oracle Enterprise Manager) page, performance section, CPU usage to check if any schema user is still using database.

2. stop listener -  lsnrctl stop  (login as oracle user)
stop listener to prevent anyone from using the database during deployment.

3. proceed deployment
compile procedures, packages, table changes etc

4. re-compile all the schemas and check invalid objects
sqlplus / as sysdba
> exec dbms_utility.compile_schema('SCHEMA_NAME');

note: SCHEMA_NAME must be in capital letter, e.g. U_1000

 a. the number of invalid objects check, excluding objects beginning with FB_%
select count(*) from dba_objects where owner='U_10000' and status <> 'VALID' and object_name not like 'FB_%'  group by object_type;


b. details for invalid objects
select object_type , object_name from dba_objects where owner='U_1000' and status <> 'VALID' and object_name not like 'FB_%' order by object_type,object_name;

5. start listener  - lsnrctl start

6. register database services with listener to serve the client immediately, otherwise, need to wait for up to 1 minute to register database services with the listener.
sqlplus / as sysdba
> alter system register;