Bounce Grid Control and Restart node Agent

What is OPMN?
Oracle Process Manager and Notification Server (OPMN) is installed and configured with every Oracle Application Server installation type and is essential for running Oracle Application Server.

OPMN Command List
prompt > opmnctl help

  scope      command      options
_________   __________   __________
            start                         – Start opmn
            startall                      – Start opmn and all managed processes
            stopall                       – Stop opmn and all managed processes
            shutdown                      – Shutdown opmn and all managed processes
[<scope>]   startproc   [<attr>=<val>..]  – Start opmn managed processes
[<scope>]   restartproc [<attr>=<val>..]  – Restart opmn managed processes
[<scope>]   stopproc    [<attr>=<val>..]  – Stop opmn managed processes
[<scope>]   reload                        – Trigger opmn to reread opmn.xml
[<scope>]   status      [<options>]       – Get managed process status
            ping        [<max_retry>]     – Ping local opmn
            validate    [<filename>]      – Validate the given xml file
            help                          – Print brief usage description
            usage       [<command>]       – Print detailed usage description

Bounce Grid Control

Log onto the Grid Control Box

> cd $ORA_GC_HOME
> cd oms10g
> cd opmn
> cd bin
> ./opmnctl status

Processes in Instance: EnterpriseManager0.xxx.xxx.xxx

——————-+——————–+———+———
ias-component | process-type | pid | status
——————-+——————–+———+———

DSA | DSA | N/A | Down
HTTP_Server | HTTP_Server | 29803 | Alive
LogLoader | logloaderd | N/A | Down
dcm-daemon | dcm-daemon | 29758 | Alive
OC4J | home | 30190 | Alive
OC4J | OC4J_EMPROV | 30194 | Alive
OC4J | OC4J_EM | 29848 | Alive
WebCache | WebCache | 30217 | Alive
WebCache | WebCacheAdmin | 30196 | Alive

(Make sure the ones in green are always running)

> ./opmnctl stopall

> ./opmnctl startall

If the status of a process is down and should be up then it can be started manually

If the status is Down, start thr process
> opmnctl startproc ias-component=dcm-daemon

To Restart an agent on a specific node

Navigate to the following directory

> cd $ORA_AGENT_HOME
> cd bin
> ./emctl

Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Oracle Enterprise Manager 10g Agent Commands:

emctl start | stop agent
emctl getversion
emctl reload | upload | clearstate | getversion agent
emctl reload agent dynamicproperties [<Target_name>:<Target_Type>]….
emctl config agent <options>
emctl config agent updateTZ
emctl config agent getTZ
emctl resetTZ agent
emctl config agent credentials [<Target_name>[:<Target_Type>]]

> ./emctl status agent

This gives the version details as well as cml files pending upload

Exploring the Database

Taken from SQL Server Bible. Some interesting queries to quickly find out useful object information.

——————————————————————–
— databases and files
select * from sys.databases

— database files (for current database)
select *  from sys.database_files

——————————————————————–
— Tables 

Select s.name as [schema],  t.*
 from sys.tables t
    join sys.schemas as s
      on s.schema_id = t.schema_id
  order by [schema], t.name

select * from sys.objects objects
  where type_desc = ‘USER_TABLE’
    and objects.name <> ‘sysdiagrams’
  order by objects.name

— Schemas
select * from sys.schemas

— Find a Column in the database
SELECT * FROM sysobjects
WHERE id IN ( SELECT id FROM syscolumns WHERE name = ‘Customer_ID’ )
and xtype = ‘U’

— columns per table

select s.name + ‘.’ + t.name as [table], c.name as [column], ty.name as UserDataType, st.name as SystemDataType, 
  c.max_length, c.precision, c.scale, c.is_nullable, c.is_computed, c.is_identity
  from sys.tables as t
    join sys.columns as c
      on t.object_id = c.object_id
    join sys.schemas as s
      on s.schema_id = t.schema_id
    join sys.types as ty
      on ty.user_type_id = c.user_type_id
    join sys.types st
      on ty.system_type_id = st.user_type_id
  where t.name <> ‘sysdiagrams’
  order by [table], c.column_id

——————————————————————–
— Primary Key columns per Table

select s.name + ‘.’ + t.name as [table], i.name as [index], i.type_desc as [type], ic.index_column_id as [Col Order], c.name as [column]#
from sys.tables t
    join sys.schemas s
      on s.schema_id = t.schema_id
    join sys.indexes i
      on t.object_id = i.object_id
    join sys.index_columns ic
        on i.object_id = ic.object_id
      and i.index_id = ic.index_id
    join sys.columns c
      on ic.object_id = c.object_id
      and ic.column_id = c.column_id
  where is_primary_key = 1
  order by [table], [index], ic.index_column_id

——————————————————————–
— Foreign Keys w/columns

select fko.name as [FK Name], fk.constraint_column_id as [Col Order], 
fks.name + ‘.’ + fkt.name as [FK table], pc.name as [FK column], rcs.name + ‘.’ + rct.name as [PK table], rc.name as [PK column]
  from sys.foreign_key_columns fk
    — FK columns
    join sys.columns pc 
      on fk.parent_object_id = pc.object_id
        and fk.parent_column_id = pc.column_id
    join sys.objects fkt
      on pc.object_id = fkt.object_id
    join sys.schemas as fks
      on fks.schema_id = fkt.schema_id

   — referenced PK columns

    join sys.columns rc 
      on fk.referenced_object_id = rc.object_id
        and fk.referenced_column_id = rc.column_id
    join sys.objects rct
      on rc.object_id = rct.object_id
    join sys.schemas as rcs
      on rcs.schema_id = rct.schema_id

    — foreign key constraint name

    join sys.objects fko 
      on fk.constraint_object_id = fko.object_id

        –and fk.referenced_column_id = rc.column_id

  order by fko.name, fk.constraint_column_id

— Foreign Keys columns w/o indexes

select  * 
 
from sys.foreign_key_columns fk
   
left join sys.index_columns ic
     
on fk.parent_object_id = ic.object_id
      and parent_column_id = ic.index_id
  where ic.object_id IS NULL

select * from sys.foreign_key_columns fk join sys.objects o on fk.parent_object_id = o.object_id

select * from sys.index_columns

select fko.name as [FK Name], fk.constraint_column_id as [Col Order], 
fks.name + ‘.’ + fkt.name as [FK table], pc.name as [FK column]–, rcs.name + ‘.’ + rct.name as [PK table], rc.name as [PK column]
  from sys.foreign_key_columns fk

   
— FK columns
    join sys.columns pc 
      on fk.parent_object_id = pc.object_id
        and fk.parent_column_id = pc.column_id
    join sys.objects fkt
      on pc.object_id = fkt.object_id
    join sys.schemas as fks
      on fks.schema_id = fkt.schema_id

    — foreign key constraint name

    join sys.objects fko 
      on fk.constraint_object_id = fko.object_id
        –and fk.referenced_column_id = rc.column_id
  left join sys.index_columns ic
      on fk.parent_object_id = ic.object_id
        and parent_column_id = ic.index_id
  where ic.object_id IS NULL
order by fko.name, fk.constraint_column_id

——————————————————————–
— Indexes by table

select s.name + ‘.’ + t.name as [table], i.name as [index], i.type_desc as [type], 
   is_unique, is_primary_key, is_disabled, 
  fill_factor, is_padded, ignore_dup_key, is_unique_constraint
  from sys.tables t
    join sys.indexes i
      on t.object_id = i.object_id
    join sys.schemas s
      on s.schema_id = t.schema_id
  where t.name <> ‘sysdiagrams’
  order by [table], [index]

— Indexes w/columns

select s.name + ‘.’ + t.name as [table], i.name as [index], i.type_desc as [type],
      ic.index_column_id as [Col Order], c.name as [column], is_primary_key, ic.is_descending_key, is_included_column
  from sys.tables t
    join sys.schemas s
      on s.schema_id = t.schema_id
    join sys.indexes i
      on t.object_id = i.object_id
    join sys.index_columns ic
        on i.object_id = ic.object_id
      and i.index_id = ic.index_id
    join sys.columns c
      on ic.object_id = c.object_id
      and ic.column_id = c.column_id
  order by [table], [index], ic.index_column_id

Move the TEMPDB Database

USE master
GO

ALTER DATABASE tempdb

MODIFY FILE

(NAME = N‘tempdev’,

FILENAME = N‘C:\Tempdb\tempdb.mdf’ ,

SIZE = 1024,

FILEGROWTH = 256)

GO

ALTER DATABASE tempdb

MODIFY FILE

(NAME = N‘templog’,

FILENAME = N‘C:\TempDB\tempdb.ldf’ ,

SIZE = 1024,

FILEGROWTH = 512)

Go

Then restart SQL Server and delete the old tempdb files.

Kill ALL Connections to a Database

As a SQL Server DBA there will be time when you want to kill all the connections to database which can be for restore or to perform other database tasks. As a DBA you can put the database in single mode or dbo use only, but most of the time you are performing the task as automated process using scripts. You can kill all connections to SQL Server database using the following script, make sure you replace the database name by setting @DBName variable value.

SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr =

Set @DBName = ‘DATABASE_NAME’
IF db_id(@DBName) < 4
BEGIN
PRINT ‘Connections to system databases cannot be killed’
RETURN
END
SELECT @spidstr=coalesce(@spidstr,‘,’ )+‘kill ‘+convert(varchar, spid)+ ‘;’
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END

The above script can be used in SQL Query Analyzer or in SQL Server Agent Job.