ADDING DISKS TO DISKGROUP

SQL> l
1* select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,TOTAL_MB,FREE_MB,NAME,FAILGROUP,PATH from v$asm_disk
SQL> /

GROUP_NUMBER DISK_NUMBER MOUNT_S TOTAL_MB FREE_MB NAME FAILGROUP PATH
------------ ----------- ------- ---------- ---------- ------------------------------ ------------------------------ -------------------------
0 0 CLOSED 97152 0 /dev/rasmdata07
0 1 CLOSED 97152 0 /dev/rasmdata06
0 2 CLOSED 97152 0 /dev/rasmdata08
2 2 CACHED 10112 7553 FLASHDG_0002 FLASHDG_0002 /dev/rasmflash03
1 4 CACHED 97280 1197 DATADG_0004 DATADG_0004 /dev/rasmdata05
1 3 CACHED 97280 1155 DATADG_0003 DATADG_0003 /dev/rasmdata04
2 0 CACHED 30720 22961 FLASHDG_0000 FLASHDG_0000 /dev/rasmflash01
1 0 CACHED 97152 1095 DATADG_0000 DATADG_0000 /dev/rasmdata01
2 1 CACHED 10112 7553 FLASHDG_0001 FLASHDG_0001 /dev/rasmflash02
1 2 CACHED 97152 1118 DATADG_0002 DATADG_0002 /dev/rasmdata03
1 1 CACHED 97152 1170 DATADG_0001 DATADG_0001 /dev/rasmdata02

11 rows selected.


SQL> select NAME,STATE,TOTAL_MB,FREE_MB,REQUIRED_MIRROR_FREE_MB,USABLE_FILE_MB,OFFLINE_DISKS from V$ASM_DISKGROUP;

NAME STATE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS
------------------------------ ----------- ---------- ---------- ----------------------- -------------- -------------
DATADG MOUNTED 486016 5735 0 5735 0
FLASHDG MOUNTED 50944 38067 0 38067 0


select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,TOTAL_MB,FREE_MB,NAME,FAILGROUP,PATH from v$asm_disk order by 6;

SQL> select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,TOTAL_MB,FREE_MB,NAME,FAILGROUP,PATH from v$asm_disk order by 6;

GROUP_NUMBER DISK_NUMBER MOUNT_S TOTAL_MB FREE_MB NAME FAILGROUP PATH
------------ ----------- ------- ---------- ---------- ------------------------------ ------------------------------ -------------------------
1 0 CACHED 97152 1095 DATADG_0000 DATADG_0000 /dev/rasmdata01
1 1 CACHED 97152 1170 DATADG_0001 DATADG_0001 /dev/rasmdata02
1 2 CACHED 97152 1118 DATADG_0002 DATADG_0002 /dev/rasmdata03
1 3 CACHED 97280 1155 DATADG_0003 DATADG_0003 /dev/rasmdata04
1 4 CACHED 97280 1197 DATADG_0004 DATADG_0004 /dev/rasmdata05

2 0 CACHED 30720 22961 FLASHDG_0000 FLASHDG_0000 /dev/rasmflash01
2 1 CACHED 10112 7553 FLASHDG_0001 FLASHDG_0001 /dev/rasmflash02
2 2 CACHED 10112 7553 FLASHDG_0002 FLASHDG_0002 /dev/rasmflash03
0 2 CLOSED 97152 0 /dev/rasmdata08
0 1 CLOSED 97152 0 /dev/rasmdata06
0 0 CLOSED 97152 0 /dev/rasmdata07

11 rows selected.

SQL> !ls -ltr /dev/asmdata08
brw-rw---- 1 oracle dba 34, 11 Dec 28 16:40 /dev/asmdata08


alter diskgroup DATADG/ add disk 'asmdata08';

ALTER DISKGROUP DATADG ADD DISK '/dev/rasmdata08' NAME DATADG_0005;

Since no FAILGROUP clauses are included


SQL> ALTER DISKGROUP DATADG ADD DISK '/dev/rasmdata08' NAME DATADG_0005;

Diskgroup altered.

SQL> select NAME,STATE,TOTAL_MB,FREE_MB,REQUIRED_MIRROR_FREE_MB,USABLE_FILE_MB,OFFLINE_DISKS from V$ASM_DISKGROUP;

NAME STATE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS
------------------------------ ----------- ---------- ---------- ----------------------- -------------- -------------
DATADG MOUNTED 583168 102885 0 102885 0
FLASHDG MOUNTED 50944 38067 0 38067 0

SQL> select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,TOTAL_MB,FREE_MB,NAME,FAILGROUP,PATH from v$asm_disk order by 6;

GROUP_NUMBER DISK_NUMBER MOUNT_S TOTAL_MB FREE_MB NAME FAILGROUP PATH
------------ ----------- ------- ---------- ---------- ------------------------------ ------------------------------ -------------------------
1 0 CACHED 97152 1167 DATADG_0000 DATADG_0000 /dev/rasmdata01
1 1 CACHED 97152 1243 DATADG_0001 DATADG_0001 /dev/rasmdata02
1 2 CACHED 97152 1190 DATADG_0002 DATADG_0002 /dev/rasmdata03
1 3 CACHED 97280 1230 DATADG_0003 DATADG_0003 /dev/rasmdata04
1 4 CACHED 97280 1272 DATADG_0004 DATADG_0004 /dev/rasmdata05
1 5 CACHED 97152 96783 DATADG_0005 DATADG_0005 /dev/rasmdata08
2 0 CACHED 30720 22961 FLASHDG_0000 FLASHDG_0000 /dev/rasmflash01
2 1 CACHED 10112 7553 FLASHDG_0001 FLASHDG_0001 /dev/rasmflash02
2 2 CACHED 10112 7553 FLASHDG_0002 FLASHDG_0002 /dev/rasmflash03
0 1 CLOSED 97152 0 /dev/rasmdata06
0 0 CLOSED 97152 0 /dev/rasmdata07

11 rows selected.

Step 1: How to take the export



expdp \"/ as sysdba\" DIRECTORY=dump_ORACLETEST_dir SCHEMAS=SCOTT DUMPFILE=CH00271509.ORACLETEST.SCOTT.%U.dmp FILESIZE=10G PARALLEL=4 JOB_NAME=EXPDP_SCOTT LOGFILE=CH00271509.ORACLETEST.SCOTT.log


%U: This will create the dump files based on the file size.
Parallel: This parameter is based on the number of CPU’S on the server. Ex: CPU’S on the server is 5, the parallel value is no of cpu’s -1 means , the value is 4.[prtconf is the command to find no of cpu’s]
DIRECTORY= We have to create before start of export or import. In the mentioned location files will be located.
SYNTAX TO CREATE DIRECTORY:


CREATE DIRECTORY dump_ORACLETEST_import_dir AS '/export_dump/CH00271509';

grant read,write on directory dump_ORACLETEST_import_dir to SCOTT;

4. SCHEMAS : Name of the schema to be exported.



Step 2: How to take import



impdp \"/ as sysdba\" DIRECTORY=dump_ORACLETEST_import_dir dumpfile=CH00271509.ORCLLIVE.SCOTT.01.dmp,CH00271509.ORCLLIVE.SCOTT.02.dmp,CH00271509.ORCLLIVE.SCOTT.03.dmp,CH00271509.ORCLLIVE.SCOTT.04.dmp LOGFILE=CH00271509.ORACLETEST.SCOTT_import.log PARALLEL=4 JOB_NAME=IMPDP_SCOTT SCHEMAS=SCOTT



Step3: How to monitor export or import



a0.



select b.username,a.sid,b.opname,b.target,round(b.SOFAR*100/b.TOTALWORK,0) || '%' as "%DONE",

b.TIME_REMAINING,to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') START_TIME

from V$SESSION_LONGOPS b,V$SESSION a where a.sid=b.sid order by 6;



a.

select sid, serial#, sofar, totalwork,

dp.owner_name, dp.state, dp.job_mode

from gv$session_longops sl, gv$datapump_job dp

where sl.opname = dp.job_name and sofar != totalwork;



b.



SQL> select * from dba_datapump_jobs;





OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS

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

SYS IMPDP_SCOTT IMPORT SCHEMA EXECUTING 1 1 3



c.



select

substr(sql_text,instr(sql_text,'into "'),30) table_name,

rows_processed, round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,

trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute

from

sys.v_$sqlarea

where

sql_text like 'insert %into "%' and command_type = 2 and open_versions > 0;



Following commands will be use full during export and import.



Generate the user creation syntax


SELECT dbms_metadata.get_ddl('USER','SCOTT') FROM dual;



Ex : -



CREATE USER "SCOTT" IDENTIFIED BY VALUES '6A063DED69BB5204'

DEFAULT TABLESPACE "SMDATA"

TEMPORARY TABLESPACE "TEMP"

QUOTA UNLIMITED ON SMDATA

QUOTA UNLIMITED ON SMINDX;



GRANT DBA TO SCOTT;

grant connect,resource to SCOTT;



2. How to find Roles and Privilages



SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='SCOTT';

SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='SCOTT';



3. How to find object status and count?



SELECT OWNER,OBJECT_TYPE,STATUS,COUNT(*) FROM DBA_OBJECTS

WHERE OWNER='SCOTT'

GROUP BY OWNER,OBJECT_TYPE,STATUS;



How to check the flash recovery area usage?


SQL> show parameter db_recovery_file_dest_size



NAME TYPE VALUE

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

db_recovery_file_dest_size big integer 5G



SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;



How to increase the db_recovery_file_dest_size ?


alter system set db_recovery_file_dest_size=10240M scope=both;



How to take the backup if the


rman> connect target /



rman > connect catalog rman/rman@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=*********)(PORT=1523)))(CONNECT_DATA=(SERVICE_NAME=dbname)))



rman > BACKUP ARCHIVELOG ALL DELETE INPUT;

select ts.name "Tablespace", today.full,last_week.full "Full_LAST_WEEK"
from
( select tsu.tablespace_id, round(tsu.tablespace_usedsize/tsu.tablespace_maxsize
*100,0) as full
from
wrh$_tablespace_space_usage tsu
where to_char(to_date(substr(tsu.rtime,1,13),'MM/DD/YYYYHH24'),'MM/DD/YYYYHH24')
= to_char(sysdate,'MM/DD/YYYYHH24')
) today,
( select tsu.tablespace_id, round(tsu.tablespace_usedsize/tsu.tablespace_maxsize
*100,0) as full
from
wrh$_tablespace_space_usage tsu
where to_char(to_date(substr(tsu.rtime,1,13),'MM/DD/YYYYHH24'),'MM/DD/YYYYHH24'
)= to_char(sysdate-7,'MM/DD/YYYYHH24')
) last_week,
v$tablespace ts
where
ts.ts# = today.tablespace_id
and ts.ts# = last_week.tablespace_id
order by 1;

Startup nomount

run
{
allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/u01/app/oracle/admin/tsm_rman/_tdpo.opt)';
set until time "TO_DATE('04/OCT/2011 11:10:00 ','DD/MON/YYYY HH24:MI:SS')";
restore controlfile;
release channel t1;
}

alter database mount;


$date

run
{
allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/u01/app/oracle/admin/tsm_rman/_tdpo.opt)';
allocate channel t2 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/u01/app/oracle/admin/tsm_rman/_tdpo.opt)';
set until time "TO_DATE('04/OCT/2011 11:10:00 ','DD/MON/YYYY HH24:MI:SS')";
restore database;
recover database;
release channel t1;
release channel t2;
}


alter database open resetlogs;

run {
allocate channel ch1 type 'SBT_TAPE' MAXOPENFILES 1 parms 'ENV=(NB_ORA_CLIENT=, NB_ORA_POLICY=, NB_ORA_SCHED=Schedule Name)';
BACKUP ARCHIVELOG FROM SEQUENCE 3863 UNTIL SEQUENCE 3970 DELETE INPUT;
release channel ch1;
}

TABLESPACE MONITORING
Unable to extend tablespace for database:


Step 1. Below query can be used for check the freespace in the tablespace

select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
round((1-(b.fsz/a.tsz))*100) "Pct Used”, round ((b.fsz/a.tsz)*100) "Pct Free" from
(select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
where tablespace_name ='TABLESPACE_NAME' group by tablespace_name) a,
(select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
where tablespace_name ='TABLESPACE_NAME' group by tablespace_name) b
Where a.tbl=b.tblsp;


Step 2: Below query can be used for get the file name and file size.


set pages 2000
set lines 120
col file_name for a65

select file_name, bytes/1024/1024 "Size (M)", maxbytes/1024/1024 "Max Size",autoextensible
from dba_data_files
where tablespace_name = upper('&tablespace')
order by substr(file_name,-7);


!df -k |grep dmx3 |sort


Step 3: Use below query to add the space and change the file system according to the requirement.



ttitle off
btitle off
set pagesize 0
set heading off
set feedback off
set verify off
set echo off
set linesize 240
set pause off

rem def tablespace = &&1
ACC tablespace CHAR PROMPT 'Enter tablespace:’


col prog_text format a70 newline

spool add_df

alter system set db_create_file_dest='/d1001/oradata/dwdevl';

alter tablespace &tablespace add datafile size 11m autoextend on next 10M maxsize 16001m;

Fix unusable indexes:

We can use below query to find unusable indexes:

col index_name for a30
col partition_name for a30
col tablespace_name for a30


PROMPT *******************************************
PROMPT UNUSABLE NON-PARTITIONED INDEXES
PROMPT *******************************************

select OWNER, INDEX_NAME, TABLESPACE_NAME, STATUS
from dba_indexes
where status = 'UNUSABLE';

PROMPT *******************************************
PROMPT UNUSABLE PARTITIONED INDEXES
PROMPT *******************************************

select INDEX_NAME, PARTITION_NAME, TABLESPACE_NAME, STATUS
from dba_ind_partitions
where status != 'USABLE'
order by 1,2;

Here is the syntax for rebuilding a non partitioned index:

alter index schema.index_name rebuild tablespace tablespace_name;

Note: replace schema.index_name and tablespace_name with the appropiate values

Here is the syntax for rebuild a partitioned index:

alter index schema.index_name rebuild partition partition_name tablespace tablespace_name;

Note: replace schema.index_name, tablespace_name, partition_name with the appropiate values



1. I use the following query to query to free rollback extents:

SELECT SEGMENT_TYPE, SEGMENT_NAME,TABLESPACE_NAME,
(MAX_EXTENTS - EXTENTS) FREE,
MAX_EXTENTS, EXTENTS, BLOCKS, BYTES
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'ROLLBACK'
ORDER BY FREE;

2. Use the following query to find out that RBS tablespace is full:

select tbs.tablespace_name,
tot.bytes/1024 total,
tot.bytes/1024-sum(nvl(fre.bytes,0))/1024 used,
sum(nvl(fre.bytes,0))/1024 free,
(1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pct,
decode(
greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, &max_pct),
&max_pct, '', '*'
) pct_warn
from dba_free_space fre,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name
and fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/1024, tot.bytes
order by 5, 1 ;

DAB_DATA_FILES

col tablespace_name format a20
col file_name format a45
col bytes format 9999999
select tablespace_name,file_name,bytes/1024/1024 from dba_data_files
where tablespace_name='TALLY_BIG_DATA';


DAB_FREESPACE

select tablespace_name,SUM(bytes)/1024/1024 from dba_free_space
where tablespace_name='SCBIDAT01' group by tablespace_name;

select index_name from dba_indexes where tablespace_name='SCBIDAT01';



Rebuild tablespace

select 'alter index '||owner||'.'||index_name||' rebuild tablespace PMFREQIDX01;' from dba_indexes where tablespace_name='PMFREQDAT01';


select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
(select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
where tablespace_name ='TALLY_BIG_DATA' group by tablespace_name) a,
(select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
where tablespace_name ='TALLY_BIG_DATA' group by tablespace_name) b
Where a.tbl=b.tblsp;

select 'alter database datafile '||''''||b.file_name||''''||' resize
'||round(((c.max_blockid+a.blocks) * 4096)/1024/1024)||'M ;'
from dba_extents a, dba_data_files b ,
(select file_id, max(block_id) max_blockid from dba_extents group by
file_id) c
where a.file_id=b.file_id
and a.file_id=c.file_id
and a.block_id = c.max_blockid
and b.file_name like lower('%&file_name%');

ALTER TABLESPACE PMFREQIDX01 COALESCE;

alter database datafile '/tstcpq/u011/oradata/tstcpq/sltc_pricedat0101.dbf' resize 1600M;

select index_name,table_name,tablespace_name from dba_indexes where owner='PMFREQ' AND INDEX_NAME='SYS_C00132902';

alter tablespace temp default storage(pctfree 0);

alter tablespace add datafile '/devl3/u09/oradata/devl3/jldl102.dbf' size 50M;

alter database datafile '/u33/oradata/matprod/enpidat11.dbf' resize 4000m;


!find /u*/oradata/matprod/

!ls /u24/oradata/matprodenpidat12.dbf

select segment_name from dba_ROLLBACK_segs where tablespace_name='RBS';

SELECT SEGMENT_NAME,TABLESPACE_NAME from dba_ROLLBACK_segs;

SELECT USN,EXTENTS FROM V$ROLLSTAT;

select tablespace_name,owner,SUM(BYTES)/1024/1024 from dba_segments where tablespace_name LIKE 'UCMDATA%' GROUP
BY TABLESPACE_NAME,OWNER;

SELECT DISTINCT TABLESPACE_NAME FROM DBA_SEGMENTS WHERE OWNER='UCM';

select tablespace_name from DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE 'UCM%';

select object_type,count(*) from dba_objects where OWNER='UCM' group by object_type;
COL OWNER FOR A6
COL TABLESPACE_NAME FOR A10
SELECT OWNER,TABLESPACE_NAME,SEGMENT_TYPE,COUNT(*) FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME LIKE '%UCM%'
GROUP BY OWNER,TABLESPACE_NAME,SEGMENT_TYPE;

SELECT 'ALTER INDEX UCM.'|| INDEX_NAME||' REBUILD TABLESPACE UCMINDEX;'
FROM DBA_INDEXES WHERE TABLESPACE_NAME='UCMDATA'; AND OWNER='UCM';


qiomkfile -e 51200k /glbake/u38/oradata/gltest/gl_balances_indx16.dbf

alter database datafile '/glbake/u38/oradata/gltest/gl_balances_indx16.dbf' resize 174144k;

alter tablespace MSDATADAT01 coalesce;

select snap_id,to_char(snap_time,'DD:MON:YYYY-HH24:MI') from stats$snapshot order by snap_id;


Adding space to ASM instance
alter tablespace x add datafile ‘+TIER2_DG’ size 128m autoextend on next 128m maxsize 32001m

Agent management in 10g

emctl start agent : Starts the Management Agent

emctl stop agent : Stops the Management Agent

emctl status agent: If the Management Agent is running, this command displays status information about the Management Agent, including the Agent Home, the process ID, and the time and date of the last successful upload to the Management Repository.


{orclprd}/root$ su - oracle
orclprd-> . oraenv
ORACLE_SID = [oracle] ? AGENT
orclprd-> emctl status agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.3.0
OMS Version : 10.2.0.3.0
Protocol Version : 10.2.0.2.0
Agent Home : /u01/app/oracle/product/10.2.0/agent10g
Agent binaries : /u01/app/oracle/product/10.2.0/agent10g
Agent Process ID : 2642014
Parent Process ID : 4358362
Agent URL : https://orclprd:3872/emd/main/
Repository URL : https://swopapsu2-sv2:1159/em/upload
Started at : 2009-07-24 22:30:44
Started by user : oracle
Last Reload : 2009-08-06 04:02:22
Last successful upload : 2009-08-12 09:51:43
Total Megabytes of XML files uploaded so far : 7133.93
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 25.52%
Last successful heartbeat to OMS : 2009-08-12 09:51:47
---------------------------------------------------------------
Agent is Running and Ready

Archivelog space usage query

The folliwng query will give the usage details of FRA.

COLUMN DUMMY NOPRINT
COMPUTE SUM OF percent_space_used ON DUMMY
COMPUTE SUM OF number_of_files ON DUMMY
COMPUTE SUM OF percent_space_reclaimable ON DUMMY
BREAK ON DUMMY
SELECT null dummy, file_type, number_of_files,percent_space_used, percent_space_reclaimable
FROM v$flash_recovery_area_usage
order by 4 desc;

Finding Locks in a database (Stand-alone/RAC)

Select * from v$lock;
For RAC databases the GV$ and GV_$ views will show information on the inst_id


Set lines 136
Set pages 200
Select * from gv_$lock; *amended with ‘where type=’TX’;
Which session is the blocker and which sessions are waiting to get the lock?

This SELECT will return no rows if there are no waiters and blockers. You can
never find situations when there is only blocker(s) or waiter(s). One session
can block many others.

column Username format A15 column Sid format 9990 heading SID
column Type format A4 column Lmode format 990 heading 'HELD'
column Request format 990 heading 'REQ' column Id1 format 9999990
column Id2 format 9999990 break on Id1 skip 1 dup
SELECT SN.Username, M.Sid, M.Type,
DECODE(M.Lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(Lmode,'990'))) Lmode,
DECODE(M.Request, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(M.Request, '990'))) Request,
M.Id1, M.Id2
FROM V$SESSION SN, V$LOCK M
WHERE (SN.Sid = M.Sid and M.Request ! = 0)
or (SN.Sid = M.Sid and M.Request = 0 and Lmode != 4 and (id1, id2)
in (select S.Id1, S.Id2 from V$LOCK S where Request != 0 and S.Id1
= M.Id1 and S.Id2 = M.Id2) ) order by Id1, Id2, M.Request;

USERNAME SID TY LMODE REQUEST ID1 ID2
---------------- ------- -- ------------- ------------- ---------- --------
SCOTT 9 TX Exclusive None 262154 117
SCOTT 11 TX None Exclusive 262154 117

Session 9 is blocking(LMODE=Exclusive)

Session 11 is waiting(REQUEST=Exclusive)

The meaning of ID1 and ID2 depends on the lock TYPE.

• We can see situations where a session is both a Blocker and a Waiter.

• If there are only two sessions and both are Blockers and Waiters then we
got a deadlock situation (which Oracle will solve automatically).

• A fast way to check blocking/waiting situations is to run:

SELECT * FROM v$lock WHERE block > 0 OR request > 0;

Frequently Used sqls:

1. Active_sessions.sql

SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
AND s.status = 'ACTIVE'
ORDER BY s.username, s.osuser;

2.inactive_sessions.sql

SET LINESIZE 132
SET PAGESIZE 1000

COLUMN username FORMAT A12
COLUMN s.osuser FORMAT A14
COLUMN logon_time FORMAT A15
column s.sid for 9999999
column p.spid for 9999999

SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
p.spid,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
AND s.status = 'INACTIVE'
ORDER BY s.username, s.osuser,logon_time;

3.active_session1.sql

SET LINESIZE 132
SET PAGESIZE 1000

COLUMN username FORMAT A8
COLUMN OSUSER for a8
COLUMN logon_time FORMAT A20
column s.sid for 9999999
column p.spid for 9999999

SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
p.spid,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
AND s.status = 'ACTIVE'
ORDER BY s.username, s.osuser,logon_time;

4.longops_rac.sql

SET LINESIZE 132
COLUMN sid FORMAT 9999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10

SELECT s.inst_id,
s.sid,
s.serial#,
s.username,
s.module,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM gv$session s,
gv$session_longops sl
WHERE s.sid = sl.sid
AND s.inst_id = sl.inst_id
AND s.serial# = sl.serial#;

5.top_sqls.sql

-- Description : Displays a list of SQL statements that are using the most resources.

SET LINESIZE 500
SET PAGESIZE 1000
col sql_text for a300
SET VERIFY OFF

SELECT *
FROM (SELECT Substr(a.sql_text,1,500) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a
ORDER BY 2 DESC)
WHERE rownum <= 5;

SET PAGESIZE 14

6.locked_objects.sql

SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15

SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;

SET PAGESIZE 14
SET VERIFY ON

7.file_io.sql

SET PAGESIZE 1000

SELECT Substr(d.name,1,50) "File Name",
f.phyblkrd "Blocks Read",
f.phyblkwrt "Blocks Writen",
f.phyblkrd + f.phyblkwrt "Total I/O"
FROM v$filestat f,
v$datafile d
WHERE d.file# = f.file#
ORDER BY f.phyblkrd + f.phyblkwrt DESC;

SET PAGESIZE 18

8.db_links.sql

SET LINESIZE 132
COLUMN db_link FORMAT A30
COLUMN host FORMAT a20
COLUMN owner FORMAT a10
COLUMN USERNAME FORMAT a10
set pagesize 200


SELECT owner,
db_link,
username,
host
FROM dba_db_links
ORDER BY owner, db_link;

9.db_properties.sql
SET PAGESIZE 200
SET LINESIZE 132
COLUMN property_value FORMAT A30
COLUMN property_name FORMAT A40


SELECT property_name, property_value FROM database_properties ORDER BY property_name;

10.dispatchers.sql

SET LINESIZE 500
SET PAGESIZE 1000
COLUMN a.status for a8
SET VERIFY OFF

SELECT a.name "Name",
a.status "Status",
a.bytes "Total Bytes",
a.idle "Total Idle Time",
a.busy "Total Busy Time",
Round(a.busy/(a.busy + a.idle),2) "Load"
FROM v$dispatcher a ORDER BY 1;

SET PAGESIZE 14
SET VERIFY ON

11. tablespace_monitoring.sql

select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
(select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
group by tablespace_name) a,
(select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
group by tablespace_name) b
Where a.tbl=b.tblsp
order by 4;

12. highly_accessed_tables.sql

select c.owner, c.name name, t.buffer_pool, c.executions, c.loads, s.bytes/1024kb,t.tablespace_name tablespace
from dba_tables t,
dba_segments s,
v$db_object_cache c
where c.type = 'TABLE'
and c.loads > 100
and c.executions >100
and c.name = t.table_name
and c.owner = t.owner
and c.name = s.segment_name
and c.owner = s.owner;

13. invalid_objects.sql

col owner for a10
set pagesize 100
set linesize 132

COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;

14. sid_sqltext.sql

set linesize 132
set pagesize 200
col status for a10
col schemaname for a12
col OSUSER for a10
col PROGRAM for a18


SELECT s.sid,
s.status,
s.process,
s.schemaname,
s.osuser,
p.program
FROM v$session s,
v$sqlarea a,
v$process p
WHERE s.SQL_HASH_VALUE = a.HASH_VALUE
AND s.SQL_ADDRESS = a.ADDRESS
AND s.PADDR = p.ADDR;

set pagesize 200
set linesize 132
SELECT s.sid,a.sql_text,p.program
FROM v$session s,
v$sqlarea a,
v$process p
WHERE s.SQL_HASH_VALUE = a.HASH_VALUE
AND s.SQL_ADDRESS = a.ADDRESS
AND s.PADDR = p.ADDR;

col username for a15
col MACHINE for a30
col status for a10
set linesize 132

select username,MACHINE,count(1),status from v$session group by username,machine,status order by 3;
select username,MACHINE,count(1) from v$session group by username,machine orderby 3;

1. Finding Volume group

{p0104crmp05}/home/sdtsldba$ lsvg
rootvg
sh_crmp05_vg2
hb_crmp05_vg1
hb_crmp06_vg1
sh_tmspdb_203
altinst_rootvg

2. Finding the file system in which volume group
{p0104crmp05}/home/sdtsldba$ lsvg -l sh_tmspdb_203


3. Size of the file system

{p01102sbap03}/home/oracle$ lslv ssp_jx_data17
LOGICAL VOLUME: ssp_jx_data17 VOLUME GROUP: orascanlive_206
LV IDENTIFIER: 0031f1df00004c00000001122e2daed5.25 PERMISSION: read/write
VG STATE: active/complete LV STATE: opened/syncd
TYPE: raw WRITE VERIFY: off
MAX LPs: 512 PP SIZE: 128 megabyte(s)
COPIES: 2 SCHED POLICY: parallel
LPs: 112 PPs: 224
STALE PPs: 0 BB POLICY: relocatable
INTER-POLICY: minimum RELOCATABLE: yes
INTRA-POLICY: middle UPPER BOUND: 2
MOUNT POINT: N/A LABEL: None
MIRROR WRITE CONSISTENCY: off
EACH LP COPY ON A SEPARATE PV?: yes (superstrict)
Serialize IO? NO


Size: SSP IDX Actual 112x128= 14336M

Operating System commands

The following commands are a useful snapshot and quick ref for determining problems within oracle from a operating system point of view: - for example if you find process hanging such as a crsd or listener search for the pid and then piping the following commands to a trace file would provide invaluable information for Oracle Support to provide RCA Root Cause Analysis

1. Ps –ef | grep : List information of the pid – this can be used to determine a session with in the database via gv$session or find the identity of the top process from topas

2. Procmap –l : Prints the address space map of a process, this will also show the read/write/execute permissions of the library’s that are particular oracle process is using

3. Procstack : Displays the hexadecimal address and symbolic names for each stack fromes of the current thread in processes

4. Truss –o -f : Think of truss as a utility that allows you to monitor a process or command and how the system uses that process or command to call something. Invaluable to Oracle support if a processes is hanging as it may show where the error is

5. Ps –f : Show the processes currently running including cpu information

6. Proctree –a : The proctree command prints the process tree containing the specified process id’s or users – this includes any child processes.

7. Topas : This invokes the top utility to list the current performance metrics of a system

8. ps -ef | egrep "crsd|ocssd|evmd|oprocd" : Check if CRS is running

To determine the physical RAM size, enter one of the following commands:

1.Platform Command AIX # /usr/sbin/lsattr -E -l sys0 -a realmem

2.HP-UX # /usr/sbin/dmesg | grep "Physical:"

3.Linux # grep MemTotal /proc/meminfo

4.Solaris # /usr/sbin/prtconf | grep "Memory size"

5.Tru64 UNIX # /bin/vmstat -P | grep "Total Physical Memory"

1>rcp -r mqprod/* gemsdevp01:/mon01/app/oracle/admin/mqprod/.
2>rcp gems28:/u23/oradata/r201dev1/gl_ifh03.dbf /u07/oradata/glintst/.


3>awk '{print $2}' file_name
--->for getting the 2nd field from the file

4>cat file_name |cut -d "/" -f4 >new_file
--->it cuts the file as per the delimiter /

5>paste -d "\0" file1 file2 >file3
--->pastes the two files without the any gap between them.

6>egrep '\.log|\.dbf' file_name
--->for getting the lines containing the specified format.

7>egrep '\.log|\.dbf' file_name |cut f2 -d "'"
--->cut the specified lines as per the delimiter ' from the given file

chmod g+w file_name (or) dir_name --->to chang the any file permission to the group or any other
permission --- r,w,x
users owner,group,others
+ for giving the permission
- for removing the permission

10>sort according to the required field number
sort +3n -t:
Where 3 is field number in the file_name.

sort -t/ +4 ----------to sort the file contents from $th field onwards.

11)paste -d " " file1 file2 ---- to map the two files.
paste -d"\0" rcp_mqtp_mqtt.sh test
NLS_LANG=AMERICAN_AMERICA.UTF8

12)cat source* |cut -d"/" -f7

13)date +%j-%y

14)cat cold_db.sh |awk -F/ '{print $2"/.snapshot/ivrp/"$3"/"$4"/"$5}'

15) du -sk ---> to find the total space occupied by internal directories & files in a directory.

16) set disk_usage = `df -k . | tail -1 | cut -f8 -d" " | cut -b1,2`

df -kl|tr -s ' '|cut -f6 -d ' '

1.mailx -s "$ORACLE_SID Dynamic Pin ERROR" "thirupal.boreddy@thiru.com"

2.cat xy.log xy.html | mailx -s "Objects list" "Thirupal.Boreddy@thiru.com"

3.cat /tmp/thiru/xy.log | mailx -s "Objects list" "Thirupal.Boreddy@thiru.com"

4.uuencode /tmp/thiru/tally_objects_6.log xx.txt| mailx -s "Objects list" "Thirupal.Boreddy@thiru.com"

5.uuencode pwmb1s_ora_23076.trc pwmb1s_ora_23076.txt |mailx -s "Result" "Thirupal.Boreddy@thiru.com"

6.uuencode alert_psaf1.log alert_psaf1.txt |mailx -s "Alertlogfile" "Thirupal.Boreddy@thiru.com"

7.uuencode psaf1_j000_13854.trc psaf1_j000_13854.txt |mailx -s "tracefile" "Thirupal.Boreddy@thiru.com"

TAR COMMANDS
------------

1. tar cvf tarflnm * ---> copy of files to target files --

A.tar cvf /u97/orabackup/tar/817.tar *
B.tar cvf /u92/oracle/wissen/gldbs/test/dev6itarfile/gen.tar *
C.tar cvf /tmp/p8172/old817.tar *

2. tar rvf ---> append to existing target file

3. tar xvf --->untar of the file in target

4. tar cf - 8.0.6 | remsh srvr27d '(cd /u01/app/oracle/product/; tar xf - )'

5. tar tvf --> for listing files in target filename

6. tar cvf /g10/backup/oracle734.tar *

cvf -------->commond for making the file to tar

srvr28.rar------> tar file name(all files are tared in to this file)

* ------> indicates all files in this directory

7. tar tvf srvr28.tar -----------> To list the tared files

Untar of the file in target directory
-------------------------------------
1. tar xvf srvr28.tar
2. tar xvf 8172_solaris_32_patchset.tar
3. tar xvf 81730_Solaris32_Release.tar

UNIX COMMANDS:

1.To Check bit size :
-------------------------

f7p21-03:5.9:tbrdev1> /bin/isainfo -kv
64-bit sparcv9 kernel modules

2.To check oracle software bit size:
-----------------------------------------
$ORACLE_HOME/bin/file oracle

3. To check Memory size:
----------------------------------

Ensure database server has at lease 512 MB of RAM.
/usr/sbin/prtconf | grep "Memory size"
Memory size: 1792 Megabytes


4. To change oracle password:
-----------------------------------
passwd oracle


5. To check for these executables, enter the following:
------------------------------------------------------------
$ /usr/bin/which "command"

echo "\n set head off pages 0 feed off;\n select name from v\$database;" | sqlplus -s system/oraTest1

echo "\n set head off pages 0 feed off;\n select name from v\$database;" | sqlplus -s system/oraTest1


Find Command Options
--------------------

1.find . -name "*" -exec \ls -ltr {} \; |awk '{sum += $5} END {print sum}'
2.find /u99/oradata/arch/colb -mtime +3 -exec \rm {} \;
3.find *arc -mtime +3 -exec \ls -ltr {} \;
4.find /$SERVER -name oradata -exec chown oracle:dba {} \;
5.find /gemsora15d/exp01/devswb/ -name devswb*.log -type f -mtime +2 -follow -exec echo Removing file: {} ; -exec /usr/bin/rm -f {} ;
6.find ${ORACLE_ADMIN}/${ORACLE_SID}/cdump -name "core_*" -mtime +14 -exec rm -rf {} \; chown oracle:dba /opt/oracle

CRSCTL CheatSheet:
You can find below various commands which can be used to administer Oracle Clusterware using crsctl. This is for purpose of easy reference.

1.Start Oracle Clusterware
#crsctl start crs

2.Stop Oracle Clusterware#crsctl stop crs

3.Enable Oracle Clusterware

#crsctl enable crs

It enables automatic startup of Clusterware daemons

4.Disable Oracle Clusterware

#crsctl disable crs

It disables automatic startup of Clusterware daemons. This is useful when you are performing some
operations like OS patching and does not want clusterware to start the daemons automatically.

5. Checking Voting disk Location

$crsctl query css votedisk
0. 0 /dev/sda3
1. 0 /dev/sda5
2. 0 /dev/sda6

Located 3 voting disk(s).
Note: -Any command which just needs to query information can be run using oracle user. But anything which alters Oracle Clusterware requires root privileges.

6.Add Voting disk

#crsctl add css votedisk path

7.Remove Voting disk

#crsctl delete css votedisk path

8.Check CRS Status

$crsctl check crs
Cluster Synchronization Services appears healthy
Cluster Ready Services appears healthy
Event Manager appears healthy

9.You can also see particular daemon status

$crsctl check cssd

10.Cluster Synchronization Services appears healthy

$crsctl check crsd

11.Cluster Ready Services appears healthy

$crsctl check evmd
Event Manager appears healthy

12.You can also check Clusterware status on both the nodes using

$crsctl check cluster
prod01 ONLINE
prod02 ONLINE

13.Checking Oracle Clusterware Version
To determine software version (binary version of the software on a particular cluster node) use

14.$crsctl query crs softwareversion

Oracle Clusterware version on node [prod01] is [11.1.0.6.0]

For checking active version on cluster, use

$ crsctl query crs activeversion

Oracle Clusterware active version on the cluster is [11.1.0.6.0]

As per documentation, multiple versions are used while upgrading.
There are other options for CRSCTL too which can be seen using

$crsctl
Or

$crsctl help
crsctl query css votedisk

1.What is an OCRCHECK utility?

An ocrcheck utility is a diagnostic tool used for diagnosing OCR(Oracle Cluster Registry) Problems. This is used to verify the Oracle Cluster Registry(OCR) integrity.

2.What does an ocrcheck display?

The OCRCHECK utility displays the version of the OCR’s block format, total space available and used space, OCRID, and the OCR locations that we have configured.

3.How does ocrcheck perform integrity check?

OCRCHECK performs a block-by-block checksum operation for all of the blocks in all of the OCRs that we have configured. It also returns an individual status for each file as well as a result for the overall OCR integrity check.

4.Give a sample output of ocrcheck utility:-
Sample of the OCRCHECK utility output:

Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262144
Used space (kbytes) : 16256
Available space (kbytes) : 245888
ID : 1918913332
Device/File Name : /dev/raw/raw1
Device/File integrity check succeeded
Device/File Name : /dev/raw/raw2
Device/File integrity check succeeded
Cluster registry integrity check succeeded

5.Where does an ocrcheck utility create a log file?

OCRCHECK creates a log file in the directory
CRS_home/log/hostname/client.

6.How can we change the amount of logging?
To change amount of logging, edit the file
CRS_home/srvm/admin/ocrlog.ini.

LABELS: OCRCHECK UTILITY

{orclprd001}/home/oracle$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 261916
Used space (kbytes) : 24276
Available space (kbytes) : 237640
ID : 1338974581
Device/File Name : /dev/p2_ocrdisk_1
Device/File integrity check succeeded
Device/File Name : /dev/p3_ocrdisk_1
Device/File integrity check succeeded

Cluster registry integrity check succeeded

Job scheduling from Oracle 10g with dbms_scheduler
In Oracle 10g the DBMS_JOB package is replaced by the DBMS_SCHEDULER package. The DBMS_JOB package is now depricated and in Oracle 10g it's only provided for backward compatibility. From Oracle 10g the DBMS_JOB package should not be used any more, because is could not exist in a future version of Oracle.

With DBMS_SCHEDULER Oracle procedures and functions can be executed. Also binary and shell-scripts can be scheduled.


Rights

If you have DBA rights you can do all the scheduling. For administering job scheduling you need the priviliges belonging to the SCHEDULER_ADMIN role. To create and run jobs in your own schedule you need the 'CREATE JOB' privilege.

With DBMS_JOB you needed to set an initialization parameter to start a job coordinator background process. With Oracle 10g DBMS_SCHEDULER this is not needed any more.

If you want to user resource plans and/or consumer groups you need to set a system parameter:

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;


Getting started quickly
To quickly get a job running, you can use code like this:

begin

dbms_scheduler.create_job(
job_name => 'DEMO_JOB_SCHEDULE'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin package.procedure(''param_value''); end; '
,start_date => '01/01/2006 02:00 AM'
,repeat_interval => 'FREQ=DAILY'
,enabled => TRUE
,comments => 'Demo for job schedule.');
end;
/

This schedules a pl/sql block to be executed daily starting 1/1/2006 02:00 AM.

You can schedule things like this, but DBMS_SCHEDULER can reuse components.

You can build a schedule using components like program, schedule, job, job class and window. We will now discuss these components in detail.

Program

The program component represents program-code that can be executed. This program code can have parameters. Code example


begin
dbms_scheduler.create_program (
program_name => 'DEMO_JOB_SCHEDULE'
,program_type => 'STORED_PROCEDURE'
,program_action => 'package.procedure'
,number_of_arguments => 1
,enabled => FALSE
,comments => 'Demo for job schedule.');


dbms_scheduler.define_program_argument (
program_name => 'DEMO_JOB_SCHEDULE'
,argument_position => 1
,argument_name => 'kol1'
,argument_type => 'VARCHAR2'
,default_value => 'default'
);
dbms_scheduler.enable(name => 'DEMO_JOB_SCHEDULE');
end;
/


The parameter program_type can have one of the following values: 'PLSQL_BLOCK', 'STORED_PROCEDURE','EXECUTABLE'.

dbms_scheduler also allows to execute shell scripts (Windows: *.bat files) and executables.


Schedule

A schedule defines the frequence and date/time specifics of the start-time for the job.

example code

begin

dbms_scheduler.create_schedule(
schedule_name => 'DEMO_SCHEDULE'
, start_date => '01/01/2006 22:00:00'
, repeat_interval => 'FREQ=WEEKLY'
, comments => 'Weekly at 22:00');
END;
/


To drop the schedule:

begin

dbms_scheduler.drop_schedule(
schedule_name => 'DEMO_SCHEDULE'
, force => TRUE );
end;
/

Calendar expresions can have one of these values: 'Yearly','Monthly','Weekly','Daily','Hourly','Minutely','Secondely'


Job

A job defines when a specific task will be started. This can be done by assigning a program to one or more schedules (or to a specific date/time). A job can belong to only 1 job class. Code example

begin

dbms_scheduler.create_job(
job_name => 'DEMO_JOB1'
, program_name =>'DEMO_JOB_SCHEDULE'
, schedule_name =>'DEMO_SCHEDULE'
, enabled => FALSE
, comments => 'Run demo program every week at 22:00');
dbms_scheduler.set_job_argument_value(
job_name => 'DEMO_JOB1'
, argument_position => 1
, argument_value => 'param1');
dbms_scheduler.enable('DEMO_JOB1');
commit;
end;

/


Or start shell script


begin

dbms_scheduler.create_job

(

job_name => 'RUN_SHELL1',
schedule_name => 'DEMO_SCHEDULE',
job_type => 'EXECUTABLE',
job_action => '/home/test/run_script.sh',
enabled => true,
comments => 'Run shell-script'
);
end;
/


Monitoring job-scheduling

Jobs can be monitored using Oracle Enterprise Manager 10g. It's also possible to use a number of views that have been created in Oracle 10g. We will discuss some of these views here.


To show details on job run:


select log_date
, job_name
, status
, req_start_date
, actual_start_date
, run_duration
from dba_scheduler_job_run_details


To show running jobs:

select job_name
, session_id
, running_instance
, elapsed_time
, cpu_used
from dba_scheduler_running_jobs;

To show job history:

select log_date
, job_name
, status
from dba_scheduler_job_log;


show all schedules:

select schedule_name, schedule_type, start_date, repeat_interval from dba_scheduler_schedules;

show all jobs and their attributes:

select * from dba_scheduler_jobs

show all program-objects and their attributes

select * from dba_scheduler_programs;

show all program-arguments:

select * from dba_scheduler_program_args;

Oracle 10g - Setting up Flash Recovery Area (FRA) / flashback database


1. Ensure database is in archivelog mode


SELECT LOG_MODE FROM V$DATABASE



2. Create 'FRA' directory on appropriate mountpoint - mkdir fra



3. Set FRA size


ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE= SCOPE=SPFILE;


4. Set FRA location


ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=SPFILE;


5. Bounce the database


SHUTDOWN IMMEDIATE (wait for shutdown)


STARTUP


6. Check parameters have changed


SHOW PARAMETER RECOVERY


NAME TYPE VALUE

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

db_recovery_file_dest string /data/example/oracle/SID/oradata_06/fra

db_recovery_file_dest_size big integer 3G


7. Once parameters have been checked, shutdown the database and startup in mount state


SHUTDOWN IMMEDIATE (wait for shutdown)


STARTUP MOUNT


8.Turn flashback on


ALTER DATABASE FLASHBACK ON


9. Open the database


ALTER DATABASE OPEN


10. Check flashback is on


SELECT FLASHBACK_ON FROM V$DATABASE


FLASHBACK_ON

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

YES


11. Test flashback database – BE AWARE, A TEST WILL REQUIRE AN 'ALTER DATABASE OPEN RESETLOGS' COMMAND


- sqlplus '/ as sysdba'


- set time on


- Create a table (flash_thirupal for example)

create table flash_thirupal (

a number,

b varchar2(10)

c varchar2(40)

)

tablespace SYSTEM

- Insert some records into the test table


INSERT INTO flash_test VALUES (1, 'ABC','THIRUPAL');

INSERT INTO flash_test VALUES (2, 'DEF','BOREDDY');


- Commit the changes and note the time


- SELECT * FROM FLASH_TEST - should output the records just inserted


- shutdown immediate

- startup mount



- Flashback the database to a time before the table was created –


FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2008-07-24 10:00:00', 'YYYY - MM-DD HH24:MI:SS')

- ALTER DATABASE OPEN RESETLOGS

- SELECT * FROM FLASH _TEST - table should not exist


How far can I flash the database back?


Below is a useful bit of script for finding out the date and time the database can be flashed back to, the retention target (mins), the size of the current flashback area (mb) and the estimated amount of flashback logs needed to meet the retention target (mb).

Set line 160


select to_char(oldest_flashback_time, 'dd/mm/yyyy hh24:mi:ss') oldest_time, retention_target,
sum((flashback_size)/1024/1024) flashback_size_mb,
sum((estimated_flashback_size)/1024/1024) estimated_flashback_size_mb
from v$flashback_database_log
group by to_char(oldest_flashback_time, 'dd/mm/yyyy hh24:mi:ss'), retention_target



This will also indicate whether space needs to be added to the flashback area in order to perform 'flashback database' to the full extent of the retention target. For example, if your retention target is 24 hours and the 'oldest_time' is only 10 hours ago, you will need to add space to the fra or reduce the retention target.


Example of output…
OLDEST_TIME RETENTION_TARGET FLASHBACK_SIZE_MB ESTIMATED_FLASHBACK_SIZE_MB
------------------- ---------------- ----------------- ---------------------------
13/08/2008 21:02:01 360 2675.75 1095.2168


In the example output above it is showing that the database can be flashed back to 13/08/08 at 21:02pm, the retention target is 6 hours, the amount of flashback data currently in the FRA is 2675mb and the estimated amount of flashback data we need to flashback the database 6 hours is 1095mb. These figures mean it is possible to perform flashback database and recover the database to the defined target, and if necessary it can be taken back even further (current date/time is 14/08/08 9:30am so it can be flashed back 12.5 hours approx.)



Viewing FRA stats
set line 300

select to_char(begin_time, 'dd/mm/yyyy hh24:mi:ss') begin_time, to_char(end_time, 'dd/mm/yyyy hh24:mi:ss') end_time,
sum((flashback_data)/1024/1024) flash_data_mb, redo_data as redo_logs_generated, db_data, sum((estimated_flashback_size)/1024/1024) estimated_flashback_size_mb
from v$flashback_database_stat
group by to_char(begin_time, 'dd/mm/yyyy hh24:mi:ss'), to_char(end_time, 'dd/mm/yyyy hh24:mi:ss'), db_data, redo_data
order by begin_time desc

This script outputs certain stats about the FRA which are broken down on an hour-to-hour basis (approximately). The stats are:

- The time interval the stats relate to (beginning and end time)

- The amount of flashback data written (mb)

- The amount of redo logs generated (bytes)

- Amount of database data read and written during the interval (bytes)

- The estimated flashback size (as in the 'How far can I flashback' section)


The example output shows there is a significantly higher amount of flashback data being written to the FRA between 22:32 and 23:32 compared to 2:32 and 3:32 – this kind of information can be useful when configuring your FRA and when reviewing sizes and retention targets.


Information relating to RAC systems

To enable flashback database in a RAC database environment, the database must be started in exclusive before flashback database is enabled. To do so, perform the following steps via SQL*PLUS:

1. Disable clustering:

SQL> alter system set CLUSTER_DATABASE=FALSE scope=spfile sid='';

2. Shutdown immediate all instances

3. From instance where clustering is disabled, mount the database:

SQL> Startup mount;

4. If database is NOT in archivelog mode, issue:

SQL> alter database archivelog;

5. Turn on flashback database:

SQL> alter database flashback on;

6. Reenable clustering:

SQL> alter system set CLUSTER_DATABASE=TRUE scope=spfile sid='';

7. Shutdown database:

SQL> shutdown immediate;

8. Open all the RAC database instances

Active Session History (ASH)

Oracle 10gr2 Introduce new option for capture performance problem.

ASH >>> Active session History <<<<

What is ASH ?

Whenever you want to know information about blocker and waiter identifiers and their associated transaction IDs and SQL.

About V$ACTIVE_SESSION_HISTORY

1. The V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance.

2. Active sessions are sampled every second and are stored in a circular buffer in SGA.

3. Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session.

4. This includes any session that was on the CPU at the time of sampling.

5. Each session sample is a set of rows and the V$ACTIVE_SESSION_HISTORY view returns one row for each active session per sample, returning the latest session sample rows first. Because the active session samples are stored in a circular buffer in SGA, the greater the system activity, the smaller the number of seconds of session activity that can be stored in the circular buffer. This means that the duration for which a session sample appears in the V$ view, or the number of seconds of session activity that is displayed in the V$ view, is completely dependent on the database activity.

6. Using the Active Session History enables you to examine and perform detailed analysis on both current data in the V$ACTIVE_SESSION_HISTORY view

7. Historical data in the DBA_HIST_ACTIVE_SESS_HISTORY view,

SQL identifier of SQL statement

.What information provide ASH view?

1. Object number, file number, and block number

2. Wait event identifier and parameters

3. Session identifier and session serial number

4. Module and action name

5. Client identifier of the session

6. Service hash identifier

How to generate ASH report?

It is New feature of 10GR2(10.2.0.1.0)

For report creation we have to use ASHRPT.SQL script.

Located: In $ORACLE_HOME/rdbms/admin folder

How to run ASHRPT.SQL script

To generate a text report of ASH information, run the ashrpt.sql script at the SQL prompt:

@$ORACLE_HOME/rdbms/admin/ashrpt.sql

/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/ashrpt.sql

First, you need to specify whether you want an HTML or a text report.

Enter value for report_type: text

Specify the time frame to collect ASH information by first specifying the begin time in minutes prior to the system date.

Enter value for begin_time: -10

Note: here you have to just put number in minutes eg: 10 for 10 minutes

Next, enter the duration in minutes that the report for which you want to capture ASH information from the begin time. The default duration of system date minus begin time is accepted in the following example:

Enter value for duration:

Note: left blank for default value. Default value is SYSDATE

The report in this example will gather ASH information beginning from 10 minutes before the current time and ending at the current time. Next, accept the default report name or enter a report name. The default name is accepted in the following example:

Enter value for report_name:

Using the report name ashrpt_1_0310_0131.txt

Note: Left it blank for default value.

The session history report is generated.

Thiru: Thirupal_Boreddy_oracle_DBA: oracle RAC Commands: "Thirupal_Boreddy_oracle_DBA: oracle RAC Commands : 'There are certain checks that can be quickly performed to check the health of CRS crsctl..."

Restarting SSH broker in the event of a hang.
Pre requirements:
This should only be attempted in the event of outbound connections hanging.

• If you are having problems performing a direct SSH connection “into” a system, this is not the correct procedure.

• You should attempt to make an SSH connection to a few other systems first, to ensure they all hang, before running with this. If ANY of the connections work, this is not a broker hang problem, but a problem on the specific system you need to connect to.


Steps to restart the broker:
• Ensure you are logged onto the system as the affected user

• "ps -aef | grep -i broker" take note of the PID on the ssh broker process

• Try command “ssh-broker-g3 –exit” to stop the process

• "ps -aef | grep -i broker" to check that it is now gone

Note: if the “ssh-broker-g3 –exit” command did not work, try it a 2nd, and maybe even a 3rd time before going to the kill option.

• If the broker process will not go, a “kill -9 PID” will need to be used

Note: A new broker will start up the first time the user attempts an outbound SSH connection, so the connection can be tested at this point.

SQL> select name from v$database;


NAME

---------

XXXXXXX


SQL> show parameter db_recovery



NAME TYPE VALUE

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

db_recovery_file_dest string +FLASHDG

db_recovery_file_dest_size big integer 13000M


SQL> alter system set db_recovery_file_dest_size=20G scope=both;


System altered.


SQL> show parameter db_recovery


NAME TYPE VALUE

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

db_recovery_file_dest string +FLASHDG

db_recovery_file_dest_size big integer 20G

Adding the space to tablespace:
===============================

Step 1: Check the space utilization

select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
(select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
where tablespace_name ='MTHLY_DM_DATA' group by tablespace_name) a,
(select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
where tablespace_name ='MTHLY_DM_DATA' group by tablespace_name) b
Where a.tbl=b.tblsp;

Step 2: Check the size of the tablespace;


col file_name for a55
select file_name,BYTES/1024/1024 from dba_data_files where tablespace_name ='MTHLY_DM_DATA' order by file_name;


Step 3: Add the space to tablespace:

1.

alter tablespace MTHLY_DM_DATA add datafile '/db031/oradata/BAPROD/mthly_dm_data_11.dbf' size 5120M;

2. ALTER DATABASE DATAFILE '/u02/oradata/ENGAGPRD/eng_data1.dbf' RESIZE 4608M;


3. ASM

ALTER TABLESPACE "IONBOARD" ADD DATAFILE '+DATADG' SIZE 31457280K REUSE AUTOEXTEND ON NEXT 1024M MAXSIZE 32767M;

Export:

Description:

i. datetime=` date +"%y%m%d_M%S"`


ii. mknod /home/oracle/aakash/devrelease/Exp_Pipe_Test/exp_pipe p


iii. gzip -cNf /home/oracle/aakash/devrelease/Exp_Pipe_Test/bstbuy_lkup_$datetime.dmp.gz &


iv. exp username/password file=/home/oracle/aakash/devrelease/Exp_Pipe_Test/exp_pipe compress=y rows=y tables=CMGT_TASK buffer=8192000 log=/home/oracle/aakash/devrelease/Exp_Pipe_Test/bstbuy_lkup_$datetime.dmp.log


v. rm -f /home/oracle/aakash/devrelease/Exp_Pipe_Test/exp_pipe


Import:


Description:



i. datetime=` date +"%y%m%d_M%S"`


ii. mknod /home/oracle/aakash/devrelease/Exp_Pipe_Test/imp_pipe p


iii. gunzip -c bstbuy_lkup_090102_100114.dmp.gz >/home/oracle/aakash/devrelease/Exp_Pipe_Test/imp_pipe &


iv. imp username/password file=/home/oracle/aakash/devrelease/Exp_Pipe_Test/imp_pipe full=y ignore=y buffer=1024000 commit=y log=/home/oracle/aakash/devrelease/Exp_Pipe_Test/imp_bstbuy_lkup_$datetime.log


v. rm -f /home/oracle/aakash/devrelease/Exp_Pipe_Test/imp_pipe

Change the parameters for RAC database:

1) Login to prod1 server as ORACLE user.

2) Set the . oraenv to orcl1

3) Login to the database as sysdba

3.1) select STATUS,INSTANCE_NUMBER,INSTANCE_NAME from gv$instance;

3.2) Show parameter sga; (this would show the current value for sga_max_size and sga_target to be 500MB)

3.3) alter system set sga_max_size=1g scope=spfile SID='*';

3.4) alter system set sga_target=1g scope=spfile SID='*';

3.5) exit

4) srvctl stop instance -d ORCL -i ORCL1

5) srvctl start instance -d ORCL -i ORCL1

6) Check the alertlog and the listener logs and confirm all ok.

*********************

7) ssh PROD2 server.

8) set the environment .oraenv to ORCL2

9) srvctl stop instance -d ORCL -i ORCL2

10) srvctl start instance -d ORCL -i ORCL2

11) Check the alertlog and the listener logs and confirm all ok.

***********************

12) ssh PROD3 server.

13) set the environment .oraenv to ORCL3

14) srvctl stop instance -d ORCL -i ORCL3

15) srvctl start instance -d ORCL -i ORCL3

16) Check the alertlog and the listener logs and confirm all ok.

************************

17) ssh PROD4 server

18) set the environment .oraenv to ORCL4

19) srvctl stop instance -d ORCL -i ORCL4

20) srvctl start instance -d ORCL -i ORCL4

21) Check the alertlog and the listener logs and confirm all ok

RAC Troubleshooting

This is the one section what will be updated frequently as my experience with RAC grows, as RAC has been around for a while most problems can be resolve with a simple google lookup, but a basic understanding on where to look for the problem is required. In this section I will point you where to look for problems, every instance in the cluster has its own alert logs, which is where you would start to look. Alert logs contain startup and shutdown information, nodes joining and leaving the cluster, etc.

Here is my complete alert log file of my two node RAC starting up.

The cluster itself has a number of log files that can be examined to gain any insight of occurring problems, the table below describes the information that you may need of the CRS components

1. $ORA_CRS_HOME/crs/log : contains trace files for the CRS resources

2. $ORA_CRS_HOME/crs/init : contains trace files for the CRS daemon during startup, a good place to start

3. $ORA_CRS_HOME/css/log : contains cluster reconfigurations, missed check-ins, connects and disconnects from the client CSS listener. Look here to obtain when reboots occur

4. $ORA_CRS_HOME/css/init : contains core dumps from the cluster synchronization service daemon (OCSd)


5. $ORA_CRS_HOME/evm/log : log files for the event volume manager and eventlogger daemon

6. $ORA_CRS_HOME/evm/init : pid and lock files for EVM

7. $ORA_CRS_HOME/srvm/log : log files for Oracle Cluster Registry (OCR)


8. $ORA_CRS_HOME/log : log files for Oracle clusterware which contains diagnostic messages at the Oracle cluster level

As in a normal Oracle single instance environment, a RAC environment contains the standard RDBMS log files, these files are located by the parameter background_dest_dump. The most important of these are

9. $ORACLE_BASE/admin/udump : contains any trace file generated by a user process

10. $ORACLE_BASE/admin/cdump : contains core files that are generated due to a core dump in a user process

Now lets look at a two node startup and the sequence of events

First you must check that the RAC environment is using the connect interconnect, this can be done by either of the following

Logfile ## The location of my alert log, yours may be different

/u01/app/oracle/admin/racdb/bdump/alert_racdb1.log

ifcfg command oifcfg getif

table check select inst_id, pub_ksxpia, picked_ksxpia, ip_ksxpia from x$ksxpia;

Oradebug SQL> oradebug setmypid

SQL> oradebug ipc



Note: check the trace file which can be located by the parameter user_dump_dest

system parameter cluster_interconnects

Note: used to specify which address to use

Post database creation CRS Health check:

2.1 Check that the crs daemons are running on each node, if not then start them.
ps –ef|grep d.bin should return

evmd.bin

ocssd.bin

crsd.bin

oprocd.bin

To start them login as root and source the .profile in /home/oracle and then run the following command

crsctl start crs

2.2 Run the following command as the oracle user on one of the nodes in the cluster and check that everything is online.

crs_stat –t –v


2.3 Run the following command as the oracle user on all nodes in the cluster

olsnodes

This should return all of the nodes of the cluster and that css is up and running and css on each node can talk to the css of the other nodes in the cluster.

2.4 Run the following commands as the root user on each node

crsctl check crs

crsctl check cssd

crsctl check crsd

crsctl check evmd

All of these commands should return that the daemon appears healthy

2.5 As the oracle user check the OCR integrity

Ocrcheck

2.6 Backup the OCR by running the following command as the root user

ocrconfig –export $CRS_HOME/manual_backups/ocr_export.dmp –s online


2.7 As the root user check the automatic backups of the OCR

ocrconfig –showbackup

Voting Disk


The voting disk as I mentioned in the architecture is used to resolve membership issues in the event of a partitioned cluster, the voting disk protects data integrity.


querying : crsctl query css votedisk

adding : crsctl add css votedisk

deleting : crsctl delete css votedisk

Flashback setup in RAC

## Make sure that the database is running in archive log mode

SQL> archive log list

## Setup the flashback

SQL> alter system set cluster_database=false scope=spfile sid='prod1';

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=200M scope=spfile;

SQL> alter system set DB_RECOVERY_FILE_DEST='/ocfs2/flashback' scope=spfile;

srvctl stop database -p prod1

SQL> startup mount

SQL> alter database flashback on;

SQL> shutdown;

srvctl start database -p prod1

Export of table Partition:

exp file=exp_f_usd_022007.dmp

log=exp_f_usd_022007.log

tables=

IPDS.F_USD:F_USD001366,

IPDS.F_USD:F_USD001367,

IPDS.F_USD:F_USD001368,

IPDS.F_USD:F_USD001369,

IPDS.F_USD:F_USD001370,

IPDS.F_USD:F_USD001371

buffer=40480000

compress=n



Truncate table Partition:

ALTER TABLE IPDS.F_USD TRUNCATE PARTITION F_USD001366;

ALTER TABLE IPDS.F_USD TRUNCATE PARTITION F_USD001367;

ALTER TABLE IPDS.F_USD TRUNCATE PARTITION F_USD001368;

ALTER TABLE IPDS.F_USD TRUNCATE PARTITION F_USD001369;

ALTER TABLE IPDS.F_USD TRUNCATE PARTITION F_USD001370;

ALTER TABLE IPDS.F_USD TRUNCATE PARTITION F_USD001371;



Import :



imp file=exp_f_usd_022007.dmp log=imp_f_usd_022007.log fromuser=IPDS touser=IPDS

buffer=40480000 ignore=y rows=y

Thiru: Enabling Archive Logs in a RAC Environment: "Enabling Archive Logs in a RAC Environment - (Oracle10g). ________________________________________ Whether a single instance or clustered da..."

Adding a new vote disk

As root

cd /u01/crs/oracle/product/10.2.0/crs/bin

1. see where your voting disks currently are (run on one node)

./crsctl query css votedisk

2. shut down the clusterware on every node

./crsctl stop crs

3. add the new voting disk (run on one node)

./crsctl add css votedisk /u01/rac4prd_nfs_votedisk_p3/nfs_votedisk_3 -force

4. delete the old on you don’t want (run on one node)

./crsctl delete css votedisk /dev/p3_votedisk_3 -force

5. check what’s there (run on one node)

./crsctl query css votedisk

5.1 Change permissions on new votedisk as per Unix standards


6. bring everything back up on every node

./crsctl start crs



Trace srvctl command

To trace the srvctl command and get the commands that are being run in the background, and any errors then;

$> export SRVM_TRACE=TRUE

$> srvctl start database -d DB

Enabling Archive Logs in a RAC Environment - (Oracle10g). ________________________________________
Whether a single instance or clustered database, Oracle tracks (logs) all changes to database blocks in online redolog files. In an Oracle RAC environment, each instance will have its own set of online redolog files known as a thread. Each Oracle instance will use its set (group) of online redologs in a circular manner. Once an online redolog fills, Oracle moves to the next one. If the database is in "Archive Log Mode", Oracle will make a copy of the online redo log before it gets reused. A thread must contain at least two online redologs (or online redolog groups). The same holds true for a single instance configuration. The single instance must contain at least two online redologs (or online redolog groups).
The size of an online redolog file is completely independent of another intances' redolog size. Although in most configurations the size is the same, it may be different depending on the workload and backup / recovery considerations for each node. It is also worth mentioning that each instance has exclusive write access to its own online redolog files. In a correctly configured RAC environment, however, each instance can read another instance's current online redolog file to perform instance recovery if that instance was terminated abnormally. It is therefore a requirement that online redo logs be located on a shared storage device (just like the database files).
As already mentioned, Oracle writes to its online redolog files in a circular manner. When the current online redolog fills, Oracle will switch to the next one. To facilitate media recovery, Oracle allows the DBA to put the database into "Archive Log Mode" which makes a copy of the online redolog after it fills (and before it gets reused). This is a process known as archiving.
The Database Creation Assistant (DBCA) allows users to configure a new database to be in archive log mode, however most DBA's opt to bypass this option. In cases like this where the database is in no archive log mode, it is a simple task to put the database into archive log mode. Note however that this will require a short database outage. From one of the nodes in the Oracle RAC 10g configuration, use the following tasks to put a RAC enabled database into archive log mode. For the purpose of this article, I will use the node linux1 which runs the orcl1 instance:

1. Login to one of the nodes (i.e. linux1) and disable the cluster instance parameter by setting cluster_database to FALSE from the current instance:

2. $ sqlplus "/ as sysdba"

SQL> alter system set cluster_database=false scope=spfile sid='orcl1';

3. Shutdown all instances accessing the clustered database:

$ srvctl stop database -d orcl

4. Using the local instance, MOUNT the database:

5. $ sqlplus "/ as sysdba"

SQL> startup mount

6. Enable archiving:

SQL> alter database archivelog;

7. Re-enable support for clustering by modifying the instance parameter cluster_database to TRUE from the current instance:

SQL> alter system set cluster_database=true scope=spfile sid='orcl1';

8. Shutdown the local instance:

SQL> shutdown immediate

9. Bring all instance back up using srvctl:

$ srvctl start database -d orcl

10. (Optional) Bring any services (i.e. TAF) back up using srvctl:

$ srvctl start service -d orcl

11. Login to the local instance and verify Archive Log Mode is enabled:

12. $ sqlplus "/ as sysdba"

13. SQL> archive log list

14. Database log mode Archive Mode

15. Automatic archival Enabled

16. Archive destination USE_DB_RECOVERY_FILE_DEST

17. Oldest online log sequence 83

18. Next log sequence to archive 84

Current log sequence 84

After enabling Archive Log Mode, each instance in the RAC configuration can automatically archive redologs!

Thiru: Procedure to relocate services in RAC nodes: "Procedure to relocate services in RAC nodes srvctl relocate service -d RTTAPP4P -s WAS_RTTAPP4P_DCF_01 -i RTTAPP4P2 -t RTTAPP4P1 Check th..."

RAC - Stopping everything Oracle-related for a given RAC LPAR
The objective/scope here is to cleanly close everything that's running under "oracle" username on a given RAC LPAR.

This order of tasks is based on the RAC Administration manual, & tallies with several procedures/experiences found documented on the internet & takes into account our own configurations/running-services.

This procedure has been tested & used in a 10.2.0.3.0 Thiruuction-environment.


0. When it's confirmed that preparatory tasks (as applicable) have all completed & that the LPAR's ready for you to take everything (Oracle-related) down......contact Ops & warn them that they'll likely see alerts relating to this LPAR (including some possibly coming from - but seemingly (to Ops) relating to - the Grid Control LPARs, too).


1. Login under ORACLE username & record the state of things as they stand (for comparison later):

crs_stat -t > $HOME/crs_stat.pre_chxxxxxx


ps -ef > $HOME/psminusef.pre_chxxxxxx


df -g > $HOME/dfminusg.pre_chxxxxxx


Finally, check in Grid Control how much space is currently in-use in the cluster's DATADG & FLASHDG disk-groups & note for comparison later.



1a. Now check to see where "tsmorasched" & "rmarchivelogs" services are currently running:


crs_stat

(tip: they'll typically be listed at the end of the output)



So, if they are currently running on this LPAR, then they must be moved to an alternative LPAR in the cluster - as follows:



crs_relocate rmarchivelogs -c FULLNODENAME

(eg crs_relocate rmarchivelogs -c P13704Thirupal024)




AND



crs_relocate tsmorasched -c FULLNODENAME



When done, double-check via "crs_stat"......& make sure they're now running on the chosen alternative LPAR.





2. Now determine which instances need to be shutdown for this LPAR:



ps -ef | grep -i pmon

(ie show which instances are currently running)





3. For each just-listed non-ASM instance-name, do:

**** WARNING **** Be sure to specify the correct instance NUMBER in the following commands, because it WILL work for a remote instance if you accidentally specify a valid number!



srvctl stop instance -d DBNAME -i INSNAME -o immediate

(eg srvctl stop instance -d SUJITH_GEP -i SUJITHGEP2 -o immediate)



Repeat that SRVCTL command for each just-listed non-ASM instance (nb: changing "immediate" to "abort" only as a last resort).



When done, double-check via "ps -ef | grep -i pmon"......& make sure only the +ASM instance remains.



Note: in each case (& as applicable), any listener-services for this LPAR's database-instance will automatically move across to an alternative LPAR in the cluster during the "srvctl stop instance......" command.



4. Now take down the ASM instance:

**** WARNING **** Be sure to specify the correct instance NUMBER in the following command, because it WILL work for a remote ASM instance if you accidentally specify a valid number!


srvctl stop asm -n FULLNODENAME -i ASMINSTANCENAME

(eg srvctl stop asm -n P13504Thirupal020 -i +ASM2)


When done, double-check via "ps -ef | grep -i pmon"......& make sure nothing remains.



5. Now stop the local listeners:



ps -ef | grep -i lsnr


lsnrctl stop LISTENERNAME

(eg lsnrctl stop LISTENER_RAC2Thirupal_P13504Thirupal020)



Repeat that LSNRCTL command for each just-listed listener, specifying the full-listed-name each time.



When done, double-check via "ps -ef | grep -i lsnr"......& make sure nothing remains.





6. Now stop the Grid Control agent:



ps -ef | grep -i emagent



/u01/app/oracle/Thiruuct/10.2.0/agent10g/bin/emctl stop agent

(nb: might say it's already stopped/down......kill the process only as a last resort)



When done, double-check via "ps -ef | grep -i emagent"......& make sure nothing remains.





7. Logout from ORACLE username & login under ROOT username, then stop Clusterware as follows:



. oraenv

(respond with “crs”)

(nb: sometimes the SID must be entered in uppercase - depending on the entry in "/etc/oratab" file - so, if the utility prompts for the ORACLE_HOME then abort & retry using the opposite case)



crsctl stop crs



Now wait 02 mins.





8. Now check for any remaining CRS-related processes running under "oracle" username:


ps -ef | grep -i oracle | grep -i crs


If any remain that are running a program in the "/u01/crs/oracle/Thiruuct......" directory path, then retry the "crsctl stop crs" command at least once......killing such remaining (CRS-related) processes only as a last resort.



9. Now check for anything else still running under "oracle" username:



ps -ef | grep -i oracle



If anything remains & you know which utility cleanly closes the process, then use it.





Otherwise, kill the process.





10. Now there should be no remaining processes running under "oracle" username. At this point, this procedure is complete. If applicable, advise whoever required/requested the close-down that the
LPAR's ready for them.


NOTE:


If the LPAR is to be subsequently rebooted, then everything SHOULD return automatically by virtue of how the LPAR's startup procedures are configured......& that includes:


Clusterware (ie CRS) & general services

ASM instance

Database instances (& associated listener-services, as applicable)

Listeners

Grid Control agent



Bear in mind, however, that any listener-services for a given database-instance that automatically moved across to an alternative LPAR in the cluster during "srvctl stop instance......" commands you issued
earlier will NOT automatically move back to their normal LPAR - & neither will the "tsmorasched" or "rmarchivelogs" services.

Thiru: Log file locations in RAC: "Log file locations in RAC The locations to view CRS logs are the following directories: - 1. CRS log files: $CRS_HOME/crs/init $CRS_HOME..."

RAC - Healthchecking the Startup of Oracle for a given RAC LPAR

The objective/scope here is to healthcheck (& complete as necessary) the startup of all normal running Oracle-related aspects for a given production RAC LPAR.

This includes the minimum/typical processes post-LPAR-startup that should be running under "oracle" username to support each running local database-instance.

This order of tasks is based on the RAC Administration manual, & tallies with several procedures/experiences found documented on the internet & takes into account our own configurations/running-services.

This procedure has been tested & used in a 10.2.0.3.0 production-environment.


0. The assumption is made here that a production LPAR boot or reboot has just taken place......in which case (by design of our production RAC environments) everything should startup automatically - including:

Clusterware (ie CRS) & general services

ASM instance

Database instances (& associated services, as applicable)

Listeners

Grid Control agent


1. So, when tasked with healthchecking the state of things after the boot/reboot, login under ORACLE username & do the following to check that CRS is up & running & ALL instances have started up......

crsctl check crs

(wait for confirmation that all appears healthy)

more /etc/oratab

ps -ef | grep -i pmon

......& the resulting list of processes should eventually comprise all instances listed in “/etc/oratab” (including the ASM instance).
Note1: the RAC auto-start is NOT dependent on “/etc/oratab” having “Y” value set for any instance to auto-start......rather, the RAC Repository dictates what auto-starts.
Note2: this is very rare, but there may be an instance that fails to auto-start because it is not using the “SPFILE” method for startup-parameters......in which case, the instance must be manually started using the normal SQLPLUS method......&, as at June 2009, this only seems to apply to RACxxxThiru’s “CTC” database instances (which will be changed in due course to “SPFILE” method).

If you find that an instance hasn’t auto-started (but does use “SPFILE” method), then use the following command syntax to start it:

srvctl start instance -d DBNAME -i INSNAME

(eg srvctl start instance -d ThiruDB_GEP -i ThiruDBGEP2)


Repeat that SRVCTL command for each instance that hasn’t started, but should be.


2. Once all instances have started, check all alert-logs for any issues via:

more /u01/app/oracle/admin/*/bdump/alert_*.log

Note: page back to the time of instance-startup & work through to double-check.


3. Now ensure the Grid Control agent is running:


ps -ef | grep -i emagent


There should be a process running under “oracle” username running the program “/u01/app/oracle/product/10.2.0/agent10g/bin/emagent”. So, if not then do:

. oraenv

(respond with “agent”)
(nb: sometimes the SID must be entered in uppercase - depending on the entry in "/etc/oratab" file - so, if the utility prompts for the ORACLE_HOME then abort & retry using the opposite case)

/u01/app/oracle/product/10.2.0/agent10g/bin/emctl start agent


4. Now ensure the local listeners are running:


ps -ef | grep -i lsnr


There should be one process running the Oracle-database listener under “oracle” username via the program “/u01/app/oracle/product/10.2.0/db_1/tnslsnr” & (typically) with a listener named in the form “LISTENER_RACNAME_FULLNODENAME”. So, if not then do (& note that the listener names are case-sensitive):

lsnrctl start LISTENER_RACNAME_FULLNODENAME

(eg lsnrctl start LISTENER_RAC2Thiru_P13504Thiru020)

There should be one process running the Oracle-ASM listener under “oracle” username via the program “/u01/app/oracle/product/10.2.0/asm/tnslsnr” & (typically) with a listener named in the form “ASM_LISTENER_FULLNODENAME”. So, if not then do (& note that the listener names are case-sensitive):

lsnrctl start ASM_LISTENER_FULLNODENAME

(eg lsnrctl start ASM_LISTENER_P13504Thiru020)

Finally, check the listener status via:

lsnrctl status ASM_LISTENER_FULLNODENAME

lsnrctl status LISTENER_RACNAME_FULLNODENAME


5. Now check in Grid Control how much space is currently in-use in the cluster's DATADG & FLASHDG disk-groups & ensure it’s sufficient.


6. At this point, we’ve completed basic checks that everything that should be running is indeed running.

Now we need to perform a check that all “services” are running on their correct LPARs (to ensure that the workload is correctly “balanced” across the cluster).
Note: when an instance is taken down, some listener-services specific (as applicable) to it may have been automatically moved across to an alternative LPAR......however, the services will NOT automatically move back to their normal instance - hence, this check/task must be undertaken.

While under ORACLE username, first record the state of things as they stand (& the assumption is that the healthcheck being undertaken is part of a change......hence the file-name suffix):

crs_stat > $HOME/crs_stat.post_chxxxxxx

ps -ef > $HOME/psminusef.post_chxxxxxx

df -g > $HOME/dfminusg.post_chxxxxxx

Now check the state of non-ASM database/instance services:

ps -ef | grep -i pmon

srvctl status service -d DBNAME

(eg srvctl status service -d CCTM)

nb: if nothing is returned, then skip to the next-listed instance-name as this means there are no specific listener-services applicable to the database/instance

srvctl config service -d DBNAME

This command shows where the service should be located and a 1st preference. If possible follow this to relocate the services using the ‘srvctl relocate service’ command detailed below.

Alternatively, if the SRVCTL STATUS output includes a service-name that is clearly particular to this LPAR (by implication of the naming convention used for the listed services), but it’s shown as running on another instance in the cluster, then relocate that service to this LPAR now as follows:

srvctl relocate service -d DBNAME -s SERVICE -i CURRENT -t TARGET

(eg srvctl relocate service -d CCTM -s WAS_CCTM_02 -i CCTM4 -t CCTM2)

That example causes “WAS_CCTM_02” service to move from CCTM4 instance/lpar to CCTM2 instance/lpar......on the basis that the SRVCTL STATUS output showed:

“Service WAS_CCTM_02 is running on instance(s) CCTM4”

Repeat those two SRVCTL commands (as necessary) for each non-ASM instance listed by the “ps -ef” command.


7. Now check to see where "tsmorasched" & "rmarchivelogs" services are currently running:

NB: we normally run these on the cluster node whose instance-names end with “1”.

crs_stat

(tip: they'll typically be listed at the end of the output)

So, if they are normally supposed to be running on this LPAR but they are currently elsewhere, then they must be relocated back as follows (where FULLNODENAME = this LPAR’s full name):

crs_relocate rmarchivelogs -c FULLNODENAME

(eg crs_relocate rmarchivelogs -c P13704Thiru024)

AND

crs_relocate tsmorasched -c FULLNODENAME

When done, double-check via "crs_stat"......& make sure they're back on this LPAR.


8. If the healthcheck at this time is part of a change that has just rebooted (ie both shutdown & restarted) an LPAR......& if during the shutdown phase of the change you also recorded the state of how things
stood (just as you did earlier in this procedure for post-reboot), then action the following final check as an added comfort-factor.
NB: if the above is not applicable, then just use “crs_stat” & ensure all’s ok.

So, compare the contents of......

$HOME/crs_stat.pre_chxxxxxx

&

$HOME/crs_stat.post_chxxxxxx


......& resolve (ie relocate) any remaining service discrepancies accordingly.


9. At this point, this procedure is complete. If applicable, advise whoever required/requested the healthcheck that all’s ok.