How to find Invalid Objects and Compile in Oracle

Finding Invalid Objects and Compiling

Objects that requires recompilation are
----------------------------------------------------
VIEW, SYNONYM, PUBLIC SYNONYM, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, UNDEFINED , JAVA CLASS, TYPE, TYPE BODY

Find Invalid Objects
---------------------------
set pages 50000 lines 32767
select OWNER, OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS WHERE STATUS = 'INVALID' and OWNER in ('&OWNER') GROUP BY OWNER, OBJECT_TYPE ORDER BY OBJECT_TYPE;

SPOOL The Invalids list
---------------------------
spool generate_invalids.lst

set pages 50000 lines 32767
col owner format a15
col object_type format a20
col object_name format a30
select owner, object_name, object_type,status,created,last_ddl_time from dba_objects where status = 'INVALID' and owner in ('&owner') order by object_type, object_name desc
/

spool off


Recompile Invalid Objects
-----------------------------------
spool recompile_invalids.lst

select 'alter '||decode(object_type,'PACKAGE BODY','PACKAGE',object_type) || ' ' ||owner|| '."'||object_name||'" '||decode(object_type,'PACKAGE BODY','COMPILE BODY','compile')|| ' ; '
from dba_objects where object_type in ('PACKAGE','PACKAGE BODY','PROCEDURE','VIEW', 'TRIGGER','FUNCTION','SYNONYM') and status='INVALID' and owner in ('&owner')

/
spool off

@recompile_invalids.lst

Validation
----------
set pages 50000 lines 32767
col owner format a15
col object_type format a20
col object_name format a30
select owner, object_name, object_type,status,created,last_ddl_time from dba_objects where owner in ('&owner') and trunc(last_ddl_time) = trunc(sysdate) order by object_type, object_name
/

exit
------------------------------------------------------------------------------------------------------------------

MORE QUERIES

Generate Oracle scripts to compile  procedure , function , package , package body,trigger, view :-

select
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
dba_objects a
where
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
and owner='SYS'
order by
OBJECT_TYPE,
OBJECT_NAME;

------------------------------------------------------------------------------------------------------------------

set heading off;
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set tab on;
set scan off;
set verify off;
--
SPOOL gen_inv_obj.sql;
select
     decode (OBJECT_TYPE, 'PACKAGE BODY',
     'alter package ' || a.OWNER||'.'||OBJECT_NAME || ' compile body;',
     'alter ' || OBJECT_TYPE || ' ' || a.OWNER||'.'||OBJECT_NAME || '
     compile;')
 from dba_objects a,
      (select max(level) order_number, object_id from public_dependency
       connect by object_id = prior referenced_object_id
       group by object_id) b
 where A.object_id = B.object_id(+)
   and STATUS = 'INVALID'
   and OBJECT_TYPE in ('PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE','TRIGGER', 'VIEW')
 order by
 order_number DESC,OBJECT_TYPE,OBJECT_NAME;
SPOOL off;

@gen_inv_obj.sql;

------------------------------------------------------------------------------------------------------------------

   set serverouput on
   DECLARE
   comp_pack        VARCHAR2 (100);
   comp_pack_body   VARCHAR2 (200);
   comp_view        VARCHAR2 (200);
   comp_proc        VARCHAR2 (200);
   comp_trig        VARCHAR2 (200);
   comp_func        VARCHAR2 (200);
BEGIN
   FOR c IN (  SELECT * FROM dba_objects WHERE status = 'INVALID' ORDER BY object_type)
   LOOP
      BEGIN
         --generate compile statement
         comp_pack :='alter package '|| c.owner|| '.'|| c.object_name|| ' compile;';
         comp_pack_body :='alter package '|| c.owner|| '.'|| c.object_name|| ' compile body;';
         comp_view :='alter view ' || c.owner || '.' || c.object_name || ' compile;';
         comp_proc :='alter procedure '|| c.owner|| '.'|| c.object_name|| ' compile;';
         comp_func :='alter function '|| c.owner|| '.'|| c.object_name|| ' compile;';
         comp_trig :='alter trigger '|| c.owner|| '.'|| c.object_name|| ' compile;';
         DBMS_OUTPUT.put_line ('Compile -> ' || c.object_name || ' type : ' || c.object_type);
         --compile
         IF c.object_type = 'PACKAGE' THEN
            EXECUTE IMMEDIATE comp_pack;
         ELSIF c.object_type = 'PACKAGE BODY' THEN
            EXECUTE IMMEDIATE comp_pack_body;
         ELSIF c.object_type = 'VIEW' THEN
            EXECUTE IMMEDIATE comp_view;
         ELSIF c.object_type = 'PROCEDURE' THEN
            EXECUTE IMMEDIATE comp_proc;
         ELSIF c.object_type = 'FUNCTION' THEN
            EXECUTE IMMEDIATE comp_func;
         ELSIF c.object_type = 'TRIGGER' THEN
            EXECUTE IMMEDIATE comp_trig;
         END IF;
         --catch exception and show
      EXCEPTION
         WHEN OTHERS THEN
            DBMS_OUTPUT.put_line ('Compile ERROR : '|| c.owner|| '.'|| c.object_name|| ' type => '|| c.object_type);
      END;
   END LOOP;
END;
------------------------------------------------------------------------------------------------------------------

Log Directory Structure in RAC

Log Directory Structure in RAC
-------------------------------------------
Each component in the CRS (Cluster Ready Services) stack has its respective directories created under the CRS home.

$ORA_CRS_HOME/crs/log Contains trace files for the CRS resources.

$ORA_CRS_HOME/crs/init Contains trace files of the CRS daemon during startup. Good place to start with any CRS login problems.

$ORA_CRS_HOME/css/log The Cluster Synchronization (CSS) logs indicate all actions such as reconfigurations, missed check-ins, connects, and disconnects from the client CSS listener. In some cases, the logger logs messages with the category of auth.crit for the reboots done by Oracle. This could be used for checking the exact time when the reboot occurred.

$ORA_CRS_HOME/css/init Contains core dumps from the Oracle Cluster Synchronization Service daemon (OCSSd) and the process ID (PID) for the CSS daemon whose death is treated as fatal. If abnormal restarts for CSS exist, the core files will have the format of core..

$ORA_CRS_HOME/evm/log Log files for the Event Volume Manager (EVM) and evmlogger daemons. Not used as often for debugging as the CRS and CSS directories.

$ORA_CRS_HOME/evm/init PID and lock files for EVM. Core files for EVM should also be written here.

$ORA_CRS_HOME/srvm/log Log files for Oracle Cluster Registry (OCR), which contains the details at the Oracle cluster level.

$ORA_CRS_HOME//log Log files for Oracle Clusterware (known as the cluster alert log), which contains diagnostic messages at the Oracle cluster level. This is available from Oracle database 10g R2.

RAC Commands

Cluster Related     Commands
---------------         --------
crs_stat -t        Shows HA resource status (hard to read)
crsstat            Ouptut of crs_stat -t formatted nicely
ps -ef|grep d.bin    crsd.bin evmd.bin ocssd.bin
crsctl check crs    CSS,CRS,EVM appears healthy
crsctl stop crs        Stop crs and all other services
crsctl disable crs*    Prevents CRS from starting on reboot
crsctl enable crs*    Enables CRS start on reboot
crs_stop -all        Stops all registered resources
crs_start -all        Starts all registered resources

NOTE
----
* These commands update the file /etc/oracle/scls_scr/<node>/root/crsstart which contains the string “enable” or “disable” as appropriate.

Database Related Commands
-------------------------
srvctl start instance -d <db_name> -i <inst_name>    Starts an instance
srvctl stop instance -d <db_name> -i <inst_name>    Stops an instance
srvctl status instance -d <db_name> -i <inst_name>    Checks an individual instance

srvctl start database -d <db_name>            Starts all instances
srvctl stop database -d <db_name>            Stops all instances, closes database
srvctl status database -d <db_name>            Checks status of all instances

srvctl start service -d <db_name> -s <service_name>    Starts a service
srvctl stop service -d <db_name> -s <service_name>    Stops a service
srvctl status service -d <db_name>            Checks status of a service

srvctl start nodeapps -n <node_name>            Starts gsd, vip, listener, and ons
srvctl stop nodeapps -n <node_name>            Stops gsd, vip and listener

BACKGROUND PROCESSES
----------------------------------
There are three main background processes you can see when doing a ps –ef|grep d.bin.  They are normally started by init during the operating system boot process.  They can be started and stopped manually by issuing the command /etc/init.d/init.crs {start|stop|enable|disable}

/etc/rc.d/init.d/init.evmd
/etc/rc.d/init.d/init.cssd
/etc/rc.d/init.d/init.crsd

SERVICES
------------
Once the above processes are running, they will automatically start the following services in the following order if they are enabled.  This list assumes you are using ASM and have a service set up for TAF/load balancing.

1.The nodeapps (gsd, VIP, ons, listener) are brought online.
2.The ASM instances are brought online.
3.The database instances are brought online.
4.Any defined services are brought online.

Finding Archivelog Names using the SCN

How to find the Archivelog names using the SCN
During database recovery,we may have a SCN number and need to know the archivelog names.

set pages 300 lines 300
col first_change# for 9,999,999,999
col next_change# for 9,999,999,999

alter session set nls_date_format='DD-MON-RRRR HH24:MI:SS';

select name, thread#, sequence#, status, first_time, next_time, first_change#, next_change# from v$archived_log
where <scn_number> between first_change# and next_change#;

SEQUENCE# number usually shows up on the archivelog name.

If you see 'D' in the STATUS column,
the archive log has been deleted from the disk. You may need to restore it from the tape.
rman target /
list backup of archivelog from logseq=<from_number> until logseq=<until_number>;
restore archivelog from logseq=<from_number> until logseq=<until_number>;

bg, fg and jobs Linux Commands

Every command you give is a job that is executed. A job can be suspended, placed in the background, moved back to the foreground or terminated.

While running a job you can        Shortcut
---------------------------            ----------
suspend a job                    ctrl+z

terminate a job                    ctrl+c

Function                            Command
--------                            ------------
Move a suspended job to the foreground        fg

Continue a suspended job in the background    bg

List all jobs                            jobs

Kill a job (%N where N is the job number)    kill %N && fg

Start a job directly in the background            command &

When you execute a unix shell-script or command that takes a long time, you can run it as a background job.

1. Executing a background job

$ find . -name "*.aud" -mtime +120 -exec rm {} \; &

2. Sending the current foreground job to the background using CTRL+Z and bg command
    step 1.Press 'CTRL+Z' which will suspend the current foreground job.
    step 2.Execute 'bg to' make that command to execute in background.

Press ‘CTRL+Z’
$ bg

3. View all the background jobs using jobs command.

$jobs

jobs    : lists the jobs that you are running in the background and in the foreground

jobs -p : list only the PID of process group leader

jobs -l : list only jobs that have change status since last notified by their status

jobs -r : resrict output to running jobs

jobs – s : restrict output to stopped jobs

4. Taking a job from the background to the foreground using fg command

$ fg

When executed without arguments, it will take the most recent background job to the foreground

SFTP

>sftp <user>@<hostname>
Connecting to <hostname>...
<user>'s Password:
sftp>pwd                   (remote working directory)
sftp>!pwd                  (local working directory)
sftp>cd /target/path/   (remote path)
sftp>pwd                   (remote working directory)
sftp>!ls -l                   (local working directory)
sftp>put <filename(s)>
Uploading <filename> to /target/path/<filename>
sftp>ls -l                    (remote location files)
sftp>bye