My Journey as an Oracle DBA: December 2011

Saturday 31 December 2011

Archives not applying on Standby Database

Due to abnormal network outage for 10-12 hrs my standby database was behaving abnormally.

All the archives are moving properly from Primary node to DR.But it was not being applied.
Then i checked the gap using below command ,there was no gap.
select * from v$archive_gap;

Then i checked maximum sequence upto which archives had applied.
select THREAD#,max(SEQUENCE#),APPLIED from v$archived_log where APPLIED='YES' group by thread#,applied;

It showed sequence of 10 hrs old.It means my archives had not been applied for 10 hrs.

It means MRP process is hanged.Now, i need to do manually recovery.I cancelled the recovery,
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

But my session got hanged.I waited for 20min but unluckily no progress.

Then I down my database by shutdown immediate command.Even it also got hanged.

Finally,i abnormally shutdown my database by shutdown abort and did manual recovery.

a) shutdown abort;

b) startup nomount;

c) alter database mount standby database;

--Manual recovery:
d) RECOVER AUTOMATIC FROM '/ora_arch/archivelog' STANDBY DATABASE UNTIL CANCEL;
e) alter database recover cancel;
--Manual recovery completed successfully

Now put the database in recovery mode.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

But unfortunately at the same time one more problem encountered.When i checked gap,
SQL> select * from v$archive_gap;

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
2 4470 4470

There was a gap of only 1 archive of node 2.I waited for 50 min but there is no progress.Then i manually registered that archive.
alter database register or replace logfile '/ora_arch/arch_2_4470_718297972.arc';

Finally,there was no gap and even archives were applying normally.

Thursday 29 December 2011

Common commands used in Physical Standby database

Mount Standby Database:
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Check Modes of Standby Database:
SQL> select open_mode, protection_mode, protection_level from v$database;
OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL
-------------------- -------------------- --------------------
MOUNTED              MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

Cancel Recovery:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Check SCN:
SQL> SELECT to_char(CURRENT_SCN) FROM V$DATABASE;

Archives applied upto which sequence:
SQL> select THREAD#,max(SEQUENCE#),APPLIED from v$archived_log where APPLIED='YES' group by thread#,applied;
SQL> select sequence#,applied from v$archived_log;

If manually need to apply any gap log:
SQL> alter database register or replace logfile '/ora_arch/arch_2_4470_718297972.arc';

Manually Recovery/Applying the Logs in the Archive Gap to the Standby Database:
SQL> ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE;

To check if you are using Real-Time Apply:
SQL> SELECT DEST_ID, RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

Check Status of Processes:
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
PROCESS   STATUS
--------- ------------
ARCH      CLOSING
ARCH      CLOSING
ARCH      CONNECTED
ARCH      CLOSING
RFS       IDLE
RFS       IDLE
RFS       IDLE
MRP0      WAIT_FOR_LOG     ------- It means Redo apply is not real-time
RFS       IDLE

To identify standby redo logs:
SQL> select * from v$standby_log;

Check members of standby redo log file:
SQL> select * from v$logfile where type=’STANDBY’;

To add Standby Redo Log File Group to a Specific Group Number:
SQL> alter database add standby logfile group 4 (
‘/<full_path_for_srl>/log04a.dbf’,
‘/<full_path_for_srl>/log04b.dbf’
) size 50m;
Note:Standby Redo Logs should be same size as of Online Redo Logs.

On Primary Node:

Enable/Disable Archive Dest:
alter system set log_archive_dest_state_2=defer sid='*' scope=both;
alter system set log_archive_dest_state_2=enable sid='*' scope=both;

Check Archive Gap:
SQL> SELECT applied.thread#
,last_applied
,newest_log
,newest_log - last_applied gap
FROM (
SELECT thread#
,MAX( sequence# ) last_applied
FROM v$archived_log
WHERE applied='YES'
GROUP BY thread#
) applied,
(
SELECT thread#
,MAX( sequence# ) newest_log
FROM v$archived_log
WHERE applied='NO'
GROUP BY thread#
) newest
WHERE applied.thread# = newest.thread#;

To identify the logs in the archive gap
SQL> SELECT MAX(R.SEQUENCE#) LAST_SEQ_RECD, MAX(L.SEQUENCE#) LAST_SEQ_SENT
FROM V$ARCHIVED_LOG R, V$LOG L WHERE R.DEST_ID=2 AND L.ARCHIVED='YES';

LAST_SEQ_RECD LAST_SEQ_SENT
------------- -------------
15147         15147

To check the Current log sequence
select THREAD#,max(SEQUENCE#) from gv$log where THREAD# in (1,2) group by THREAD#;

Monday 26 December 2011

Concurrent Requests is getting failed

After installing Application EBS R12 on OS AIX 6.1 and where Display is localhost:1.0.Unfortunately,all the concurrent requests are getting failed with error:

REP-0004: Warning: Unable to open user preference file.

REP-3000: Internal error starting Oracle Toolkit.

REP-3000: Internal error starting Oracle Toolkit.

REP-0069: Internal error

REP-57054: In-process job terminated:Terminated with error:

REP-3000: Internal error starting Oracle Toolkit.

Solution:

On Application Node Check the filesets which we had installed

lslpp -l OpenGL.OpenGL_X.dev.vfb

lslpp -l X11.vfb

lslpp -l X11.samples.apps.clients

If one of the above filesets is missing then,

Install the following filesets:

• OpenGL.OpenGL_X.dev.vfb

• X11.vfb

• X11.samples.apps.clients

Take a backup of inittab file & add following lines to it

xserver:2:respawn:/usr/bin/X11/X -force -vfb :1 > /dev/null

startX:2:once:/etc/rc.startX > /dev/console 2>&1

create rc.startX file in /etc if it is not there,change its permission to

774 & add these two line to this file

#!/usr/bin/ksh

/usr/bin/X11/mwm -display :1

Restart the server

Ask the application administrator to add following lines in his application

profile

export DISPLAY=localhost:1.0

xhost +

Saturday 24 December 2011

SSH login without password

Sometimes users want to automatic login from host A to Host B without
entering any password.
For scheduling any script, like to scp from one server another server
without entering any password we required ssh login without password.
So to do this we need to create key.

a=user1, b=user2, A=server1, B=server2

Note: User should be SAME on both servers

 
a@A:~> ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/a/.ssh/id_rsa):
Created directory '/home/a/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/a/.ssh/id_rsa.
Your public key has been saved in /home/a/.ssh/id_rsa.pub.
The key fingerprint is:
3e:4f:05:79:3a:9f:96:7c:3b:ad:e9:58:37:bc:37:e4 a@A

Now use ssh to create a directory ~/.ssh as user b on B. (The directory may already exist, which is fine):

a@A:~> ssh b@B mkdir -p .ssh
b@B's password:

Finally append a's new public key to b@B:.ssh/authorized_keys and enter b's password one last time:

a@A:~> cat .ssh/id_rsa.pub | ssh b@B 'cat >> .ssh/authorized_keys'
b@B's password:

From now on you can log into B as b from A as a without password:

a@A:~> ssh b@B hostname
B

adcfgclone.pl appsTier failed with error

While running adcfgclone.pl appsTier on Application tier i got error:

java.sql.SQLException: The Network Adapter could not establish the connection
Database connection to jdbc:oracle:thin:@uatdr.path.coop:1521:uatprddr failed
UPDATE call failed with exit code 1
Updating parameters: fnd_jdbc_buffer_min=1 fnd_jdbc_buffer_max=5 fnd_jdbc_buffer_decay_interval=300
java.sql.SQLException: The Network Adapter could not establish the connection
Database connection to jdbc:oracle:thin:@uatdr.path.coop:1521:uatprddr failed
Updating parameters: fnd_jdbc_buffer_decay_size=5 fnd_jdbc_usable_check=false fnd_jdbc_context_check=true
java.sql.SQLException: The Network Adapter could not establish the connection
Database connection to jdbc:oracle:thin:@uatdr.path.coop:1521:uatprddr failed
Updating parameters: fnd_jdbc_plsql_reset=false
java.sql.SQLException: The Network Adapter could not establish the connection
Database connection to jdbc:oracle:thin:@uatdr.path.coop:1521:uatprddr failed
DBC generation failed with exit code 1
Updating Server Security Authentication
java.sql.SQLException: Invalid number format for port number
Database connection to jdbc:oracle:thin:@host_name:port_number:database failed
Updating Server Security Authentication failed with exit code 1
adgendbc.sh exiting with status 1
ERRORCODE = 1 ERRORCODE_END
.end std out.

Solution:

The hosts file do not have domain name. Add the Domain name in the Hosts File.

Friday 23 December 2011

Make RAC off

Disable RAC

While Cloning RAC to single instance we need to make the RAC off.Sometimes while making the RAC off we got few error.Below is the Step of making the RAC off ,error message and solution.

$ cd $ORACLE_HOME/rdbms/lib

$ make -f ins_rdbms.mk rac_off

rm -f /orahome/app/oracle/product/11.2.0/dbhome_1/lib/libskgxp11.so

cp /orahome/app/oracle/product/11.2.0/dbhome_1/lib//libskgxpg.so /orahome/app/oracle/product/11.2.0/dbhome_1/lib/libskgxp11.so

rm -f /orahome/app/oracle/product/11.2.0/dbhome_1/lib/libskgxn2.a

cp /orahome/app/oracle/product/11.2.0/dbhome_1/lib//libskgxnr.a /orahome/app/oracle/product/11.2.0/dbhome_1/lib/libskgxn2.a

rm -f /orahome/app/oracle/product/11.2.0/dbhome_1/lib/libskgxn2.a

cp /orahome/app/oracle/product/11.2.0/dbhome_1/lib//libskgxns.a /orahome/app/oracle/product/11.2.0/dbhome_1/lib/libskgxn2.a

cp: /orahome/app/oracle/product/11.2.0/dbhome_1/lib//libskgxns.a: A file or directory in the path name does not exist.

make: 1254-004 The error code from the last command is 1.

Stop.

$ make -f ins_rdbms.mk ioracle

chmod 755 /orahome/app/oracle/product/11.2.0/dbhome_1/bin

- Linking Oracle

rm -f /orahome/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/oracle

ld -b64 -o /orahome/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/oracle -L/orahome/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/

-L/orahome/app/oracle/product/11.2.0/dbhome_1/lib/ -bbigtoc -bnoipath -bI:/orahome/app/oracle/product/11.2.0/dbhome_1/lib/ksms.imp

/orahome/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/opimai.o /orahome/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/ssoraed.o

/orahome/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/ttcsoi.o   -lperfsrv11 /orahome/app/oracle/product/11.2.0/dbhome_1/lib/nautab.o

/orahome/app/oracle/product/11.2.0/dbhome_1/lib/naeet.o /orahome/app/oracle/product/11.2.0/dbhome_1/lib/naect.o

/orahome/app/oracle/product/11.2.0/dbhome_1/lib/naedhs.o /orahome/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/config.o -lserver11 -lodm11

-lcell11 -lnnet11 /orahome/app/oracle/product/11.2.0/dbhome_1/lib/libskgxp11.so -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11

-lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lclient11 -lvsn11 -lcommon11 -lgeneric11 -lknlopt `if

/bin/ar -X64 tv /orahome/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap11

-bE:/orahome/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/olap.exp" ; fi` -lslax11 -lpls11 -lrt -lplp11 -lserver11 -lclient11 -lvsn11

-lcommon11 -lgeneric11 `if [ -f /orahome/app/oracle/product/11.2.0/dbhome_1/lib/libavserver11.a ] ; then echo "-lavserver11" ; else echo

. . . .

. . . .

. . . .

. . . .

. . .

grep '^'kcsm.o > /dev/n                                             lities/cldomain" ]; then echo "-lha_gs_r -lpthreads"; fi; fi`   -lld -lm

`cat /o                                             /sysliblist` -lm   -bI:/usr/lib/aio.exp -bE:/orahome/app/oracle/product/11.2.0/

p/oracle/product/11.2.0/dbhome_1/rdbms/lib//libcorejava.exp -bpT:0x100000000 -bp

-L /orahome/app/oracle/product/11.2.0/dbhome_1/srvm/lib

ld: 0706-006 Cannot find or open library file: -l skgxn2

ld:open(): A file or directory in the path name does not exist.

ld: 0706-006 Cannot find or open library file: -l skgxn2

ld:open(): A file or directory in the path name does not exist.

make: 1254-004 The error code from the last command is 255.

So,if you want to make RAC off just follow below mentioned steps

Solution:

$ cd $ORACLE_HOME

$ cd rdbms/lib

$ ls -lrt libskgxns.a

-rw-r--r--    1 oradr    oinstall      12844 Aug 11 17:13 libskgxns.a

$ cp libskgxns.a $ORACLE_HOME/lib

$ cd $ORACLE_HOME/rdbms/lib

$ make -f ins_rdbms.mk rac_off

$ make -f ins_rdbms.mk ioracle

It will gives many warnings but we can IGNORE these warnings

ld: 0711-319 WARNING: Exported symbol not defined:

Thursday 22 December 2011

Some Important Oracle Database scripts

To get SID from SPID:
select sid,program,process,username,machine,osuser,status,to_char(LOGON_TIME,'DD-MON-YYYY HH:MI:SS') from v$session where paddr in(select addr from v$process where spid=&spid);

To get spid from sid:
select spid from v$process where addr in ( select paddr from v$session where sid = &sid );

Who is locking what:
select
oracle_username
os_user_name,
locked_mode,
object_name,
object_type
from
v$locked_object a,dba_objects b
where
a.object_id = b.object_id

To check blocking locks:
set lines 120
set pages 100
SELECT DECODE(request,0,'Holder: ','Waiter: ')|| sid sess, id1, id2, lmode,request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request;

To check database locks:
select c.owner,c.object_name,c.object_type,b.sid,b.serial#,b.status,b.osuser,b.machine from
v$locked_object a,v$session b,dba_objects c where b.sid = a.session_id and a.object_id = c.object_id;

select oracle_username ,os_user_name, locked_mode, object_name, object_type from v$locked_object a,dba_objects b where
a.object_id = b.object_id;

Script gives the sql_text for a particular sid:

set lines 132

column sql_text format a80 wrap

select username, sql_text from v$sqlarea, v$session where hash_value = sql_hash_value and sid=&sid;

Check sessions exceeded 12 hrs:

SELECT s.sid ,s.username,s.paddr,s.LOGON_TIME,round((s.LAST_CALL_ET/1200),2),s.program,s.osuser,s.process,

s.machine,s.terminal,p.spid

FROM v$session s, v$process p

WHERE s.username IS NOT NULL AND (LAST_CALL_ET / 60) > 720

AND s.STATUS = 'INACTIVE'

and s.paddr=p.addr

order by s.LAST_CALL_ET desc

Undo tablespace usage:

a)SELECT s.sid, s.serial#, s.username, s.program,t.used_ublk, t.used_urec

FROM v$session s, v$transaction t

WHERE s.taddr = t.addr ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

b)select status,SUM(bytes/1024/1024) from dba_undo_extents GROUP BY STATUS;

c)select * from v$fast_start_transactions

d)select status,count(1) from dba_undo_extents group by status

e)select ((select (nvl(sum(bytes),0)) from dba_undo_extents where tablespace_name='UNDOTBS1' and status in ('ACTIVE','UNEXPIRED')) *100) / (select sum(bytes)

from dba_data_files where tablespace_name='UNDOTBS1') "PCT_INUSE" from dual;

f)select

( select sum(bytes)/1024/1024 from dba_data_files

where tablespace_name like 'UND%' ) allocated,

( select sum(bytes)/1024/1024 from dba_free_space

where tablespace_name like 'UND%') free,

( select sum(bytes)/1024/1024 from dba_undo_extents

where tablespace_name like 'UND%') USed

from dual;

Trace SQL session:

a) Set_Sql_Trace_In_Session

Used to set trace on or off in another users session:

EXEC DBMS_System.Set_Sql_Trace_In_Session(sid, serial#, true );

EXEC DBMS_System.Set_Sql_Trace_In_Session(31, 97, true );

b) Set_Ev

Used to set trace on for a specific event:

EXEC DBMS_System.Set_Ev(sid, serial#, event, level, name);

EXEC DBMS_System.Set_Ev(31, 97, 10046, 4, '');

Where level indicates the following levels of trace:

1 - Standard SQL_TRACE functionality.

4 - As level 1 plus tracing of bind variables.

8 - As level 1 plus wait events.

12 - As level 1 plus bind variables and wait events.

CHECK FREE SPACE IN TEMPORARY TABLESPACE:

select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

SELECT * FROM V$TEMPSEG_USAGE WHERE TABLESPACE='TEMP1'

Tuesday 20 December 2011

OEM queries consuming high CPU on 11.2 Database

High CPU Utilization from Queries Executed by EM Agent on 11.2 Database.

Findings:
Queries consuming resources:

/* OracleOEM */ DECLARE rept varchar2(4000); rref varchar2(4000); key_enable varchar2(20); autoimpl_enable varchar2(20); key_count number; TYPE data_cursor_type IS REF CURSOR; data_cursor data_cursor_type; BEGIN rref := dbms_report.build_report_reference_varg( 'sqltune', 'auto_summary', 'section', 'task_stats', 'validate', 'n'); rept := dbms_report.get_report(rref); WITH data AS (select xmltype(rept) rept_xml FROM dual) SELECT extractvalue(rept_xml, '/report/summary/statistics/task_stats/info_group/info[position()=1]'), extractvalue(rept_xml, '/report/summary/statistics/task_stats/info_group/info[position()=2]') INTO key_enable, autoimpl_enable FROM data; SELECT count(*) INTO key_count FROM table(xmlsequence(xmltype(rept).extract('//obj_id'))); OPEN data_cursor FOR SELECT key_count, key_enable, autoimpl_enable FROM dual; :1 := data_cursor; END;

SELECT /*+ leading(oe f r l) */ /* STN_REPT_TOP_PROF */ xmlelement( "top_profiles", xmlagg(xmlelement("obj_id", object_id))) FROM (SELECT task_id, exec_name, exec_id, exec_start, object_id, sql_id, parsing_schema, phv, obj_attr8 FROM (SELECT /*+ cardinality(o 58429) leading(e o) */ e.task_id task_id, e.execution_name exec_name, e.execution_id exec_id, e.execution_start exec_start, o.id object_id, o.attr1 sql_id, o.attr3 parsing_schema, to_number(nvl(o.attr5, '0')) phv, nvl(o.attr8, 0) obj_attr8, row_number() over (partition by o.attr1 order by bitand(o.attr7, 32) asc, e.execution_start desc) rn FROM (SELECT e.task_id, e.name execution_name, e.id execution_id, e.exec_start execution_start, e.status status# FROM (SELECT task_id, min(execution_name) keep (dense_rank first order by execution_start) bename, max(executio n_name) keep (dense_rank last order by execution_start) eename, min(execution_start) bestart, max(execution_start) eestart FROM (SELECT task_id, name execution_name, exec_start execution_start FROM wri$_adv_executions WHERE task_id = :tid and name in (:bename, :eename)) GROUP BY task_id) r, wri$_adv_executions e WHERE e.task_id = r.task_id and e.exec_start >= bestart and e.exec_start <= eestart and e.status IN (3, 4) and (bename <> eename OR e.name = bename)) e /* e */, wri$_adv_objects o WHERE o.task_id = e.task_id AND o.exec_name = e.execution_name AND o.type = :sqltyp) WHERE rn = 1) oe /* oe */, wri$_adv_findings f, wri$_adv_recommendations r, wri$_adv_rationale l WHERE oe.task_id = f.task_id AND oe.exec_name = f.exec_name AND oe.object_id = f.obj_id AND f.task_id = r.task_id AND f.exec_name = r.exec_name AND f.id = r.finding_id AND l.task_id = r.task_id AND l.exec_name = r.exec_name AND l.rec_id = r.id AND r.type = :pr ofiletyp AND l.type = :impltyp AND l.attr1 = :acceptdis AND NOT EXISTS (SELECT 1 FROM dba_sql_profiles p WHERE p.task_id = r.task_id AND p.task_exec_name = r.exec_name AND p.task_obj_id = oe.object_id AND p.task_fnd_id = r.finding_id AND p.task_rec_id = r.id)

Workaround:
This is just a workaround.For Solution please contact Oracle Support.
This is affecting only 11.2 target databases monitored by 11.1 or higher EM agents.

1. Backup and edit database.xmlp
cd $ORACLE_HOME/sysman/admin/default_collection
cp database.xmlp database.xmlp_19dec11

2. Comment the block using  at the end. The commented block will look as follows:



3. Restart the agent
if this is a cluster installation, the operation must be performed on all the nodes.
emctl stop dbconsole
emctl start dbconsole

Monday 19 December 2011

Cron Scheduled script is not running

The SQL script is scheduled in the crontab but it is not giving any output.

Findings:
To investigate the problem we need to check the logs one by one.
1) Check the cron logfile at /var/adm/cron/log

root : CMD ( /usr/sbin/dumpctrl -k >/dev/null 2>/dev/null ) : PID ( 1917118 ) : Fri Dec 16 16:05:00 2011
Cron Job with pid: 1917118 Successful
root : CMD ( /usr/sbin/dumpctrl -k >/dev/null 2>/dev/null ) : PID ( 1060890 ) : Fri Dec 16 16:10:00 2011
Cron Job with pid: 1060890 Successful

The Status is Successful it means cron is running at its scheduled time but there is some problem in the called script.

2) Check /usr/spool/mail/orauat file
It shows some error.
ERROR:
ORA-12162: TNS:net service name is incorrectly specified

SP2-0640: Not connected
SP2-0640: Not connected
*****************************************************************
cron: The previous message is the standard output
and standard error of one of the cron commands.

It means the sql called in the script is not able to connect to the database.

Check the script properly.

In my case,i had have two instances running on same host and i did not set the ORACLE_SID in the script.So,i just set the ORACLE_SID in the script and it worked fine.

Sunday 18 December 2011

OS File Permission

The "chmod" command is used to alter file permissions after the file has been created.

chmod -R 777 abc

-R is to recursively change the permission.All the files in the directory have same permission.

Owner	Group	Others	Permission
7 (u+rwx)	7 (g+rwx)	7 (o+rwx)	read + write + execute
6 (u+wx)	6 (g+wx)	6 (o+wx)	write + execute
5 (u+Rx)	5 (g+Rx)	5 (o+Rx)	read + execute
4 (u+r)	4 (g+r)	4 (o+r)	read only
2 (u+w)	2 (g+w)	2 (o+w)	write only
1 (u+x)	1 (g+x)	1 (o+x)	execute only

Friday 16 December 2011

RMAN - Delete Archives older than 14days from DR site

Script to delete archive files from standby database using RMAN.

We can schedule it in Cron like,

00 01 * * * sh /orabackup/delete_arch.sh > /orabackup/delete_arch.log

/orabackup/delete_arch.sh

export ORACLE_SID=orcl
export ORACLE_HOME=/orahome/app/oracle/product/11.2.0/dbhome_1
export PATH=${PATH}:${ORACLE_HOME}/bin
$ORACLE_HOME/bin/rman target / << EOF
run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
crosscheck archivelog all;
delete noprompt archivelog all completed before 'sysdate -14';
release channel ch1;
release channel ch2;
}
EOF

Steps to Add Redo Logs on RAC database

Steps on Instance 1(10.10.10.1) ORCL1 instance

select GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024,MEMBERS,ARCHIVED,STATUS from v$log where THREAD#=1;

GROUP# THREAD# SEQUENCE# BYTES/1024/1024 MEMBERS ARC STATUS

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

11 1 60656 300 3 YES INACTIVE

12 1 60657 300 3 NO CURRENT

13 1 60655 300 3 YES INACTIVE

14 1 60651 300 3 YES INACTIVE

15 1 60652 300 3 YES INACTIVE

16 1 60653 300 3 YES INACTIVE

17 1 60654 300 3 YES INACTIVE

7 rows selected.

select GROUP#,MEMBER from v$logfile where GROUP# in (11,12,13,14,15,16,17) order by 1;

GROUP# MEMBER

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

11 /data/redo01/ORCL/onlinelog/redo11a.dbf

11 /data/redo02/ORCL/onlinelog/redo11b.dbf

11 /data/redo03/ORCL/onlinelog/redo11c.dbf

12 /data/redo01/ORCL/onlinelog/redo12a.dbf

12 /data/redo02/ORCL/onlinelog/redo12b.dbf

12 /data/redo03/ORCL/onlinelog/redo12c.dbf

13 /data/redo02/ORCL/onlinelog/redo13b.dbf

13 /data/redo03/ORCL/onlinelog/redo13c.dbf

13 /data/redo01/ORCL/onlinelog/redo13a.dbf

14 /data/redo03/ORCL/onlinelog/redo14c.dbf

14 /data/redo01/ORCL/onlinelog/redo14a.dbf

14 /data/redo02/ORCL/onlinelog/redo14b.dbf

15 /data/redo03/ORCL/onlinelog/redo15c.dbf

15 /data/redo02/ORCL/onlinelog/redo15b.dbf

15 /data/redo01/ORCL/onlinelog/redo15a.dbf

16 /data/redo02/ORCL/onlinelog/redo16b.dbf

16 /data/redo03/ORCL/onlinelog/redo16c.dbf

16 /data/redo01/ORCL/onlinelog/redo16a.dbf

17 /data/redo01/ORCL/onlinelog/redo17a.dbf

17 /data/redo02/ORCL/onlinelog/redo17b.dbf

17 /data/redo03/ORCL/onlinelog/redo17c.dbf

21 rows selected.

(1) ALTER DATABASE ADD LOGFILE THREAD 1

GROUP 1 ('/data/redo01/ORCL/onlinelog/redo01a.dbf','/data/redo02/ORCL/onlinelog/redo01b.dbf','/data/redo03/ORCL/onlinelog/redo01c.dbf') SIZE 1024M,

GROUP 2 ('/data/redo01/ORCL/onlinelog/redo02a.dbf','/data/redo02/ORCL/onlinelog/redo02b.dbf','/data/redo03/ORCL/onlinelog/redo02c.dbf') SIZE 1024M,

GROUP 3 ('/data/redo01/ORCL/onlinelog/redo03a.dbf','/data/redo02/ORCL/onlinelog/redo03b.dbf','/data/redo03/ORCL/onlinelog/redo03c.dbf') SIZE 1024M);

(2) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=11;

GROUP# ARC STATUS

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

11 YES INACTIVE

(2a) If not above Result than run

alter system switch logfile;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=11;

GROUP# ARC STATUS

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

11 YES INACTIVE

(3) alter database drop logfile group 11;

(4) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=12;

GROUP# ARC STATUS

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

12 YES INACTIVE

(4a) If not above Result than run

alter system switch logfile;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=12;

GROUP# ARC STATUS

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

12 YES INACTIVE

(5) alter database drop logfile group 12;

(6) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=13;

GROUP# ARC STATUS

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

13 YES INACTIVE

(6a) If not above Result than run

alter system switch logfile;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=13;

GROUP# ARC STATUS

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

13 YES INACTIVE

(7) alter database drop logfile group 13;

(8) ALTER DATABASE ADD LOGFILE THREAD 1

GROUP 4 ('/data/redo01/ORCL/onlinelog/redo04a.dbf','/data/redo02/ORCL/onlinelog/redo04b.dbf','/data/redo03/ORCL/onlinelog/redo04c.dbf') SIZE 1024m;

(9) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=14;

GROUP# ARC STATUS

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

14 YES INACTIVE

(9a) If not above Result than run

alter system switch logfile;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=14;

GROUP# ARC STATUS

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

14 YES INACTIVE

(10) alter database drop logfile group 14;

(11) ALTER DATABASE ADD LOGFILE THREAD 1

GROUP 5 ('/data/redo01/ORCL/onlinelog/redo05a.dbf','/data/redo02/ORCL/onlinelog/redo05b.dbf','/data/redo03/ORCL/onlinelog/redo05c.dbf') SIZE 1024m;

(12) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=15;

GROUP# ARC STATUS

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

15 YES INACTIVE

(12a) If not above Result than run

alter system switch logfile;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=15;

GROUP# ARC STATUS

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

15 YES INACTIVE

(13) alter database drop logfile group 15;

(14) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=16;

GROUP# ARC STATUS

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

16 YES INACTIVE

(14a) If not above Result than run

alter system switch logfile;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=16;

GROUP# ARC STATUS

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

16 YES INACTIVE

(15) alter database drop logfile group 16;

(16) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=17;

GROUP# ARC STATUS

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

17 YES INACTIVE

(16a) If not above Result than run

alter system switch logfile;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=17;

GROUP# ARC STATUS

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

17 YES INACTIVE

(17) alter database drop logfile group 17;

Steps on Instance 2(10.10.10.2) ORCL2 Instance

select GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024,MEMBERS,ARCHIVED,STATUS from v$log where THREAD#=2;

GROUP# THREAD# SEQUENCE# BYTES/1024/1024 MEMBERS ARC STATUS

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

21 2 53168 300 3 YES INACTIVE

22 2 53170 300 3 YES ACTIVE

23 2 53169 300 3 YES INACTIVE

24 2 53171 300 3 NO CURRENT

25 2 53165 300 3 YES INACTIVE

26 2 53166 300 3 YES INACTIVE

27 2 53167 300 3 YES INACTIVE

7 rows selected.

select GROUP#,MEMBER from v$logfile where GROUP# not in (11,12,13,14,15,16,17) order by 1;

GROUP# MEMBER

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

21 /data/redo04/ORCL/onlinelog/redo21a.dbf

21 /data/redo05/ORCL/onlinelog/redo21b.dbf

21 /data/redo06/ORCL/onlinelog/redo21c.dbf

22 /data/redo04/ORCL/onlinelog/redo22a.dbf

22 /data/redo05/ORCL/onlinelog/redo22b.dbf

22 /data/redo06/ORCL/onlinelog/redo22c.dbf

23 /data/redo04/ORCL/onlinelog/redo23a.dbf

23 /data/redo05/ORCL/onlinelog/redo23b.dbf

23 /data/redo06/ORCL/onlinelog/redo23c.dbf

24 /data/redo04/ORCL/onlinelog/redo24a.dbf

24 /data/redo05/ORCL/onlinelog/redo24b.dbf

24 /data/redo06/ORCL/onlinelog/redo24c.dbf

25 /data/redo04/ORCL/onlinelog/redo25a.dbf

25 /data/redo05/ORCL/onlinelog/redo25b.dbf

25 /data/redo06/ORCL/onlinelog/redo25c.dbf

26 /data/redo04/ORCL/onlinelog/redo26a.dbf

26 /data/redo05/ORCL/onlinelog/redo26b.dbf

26 /data/redo06/ORCL/onlinelog/redo26c.dbf

27 /data/redo04/ORCL/onlinelog/redo27a.dbf

27 /data/redo05/ORCL/onlinelog/redo27b.dbf

27 /data/redo06/ORCL/onlinelog/redo27c.dbf

21 rows selected.

(18) ALTER DATABASE ADD LOGFILE THREAD 2

GROUP 6 ('/data/redo04/ORCL/onlinelog/redo06a.dbf','/data/redo05/ORCL/onlinelog/redo06b.dbf','/data/redo06/ORCL/onlinelog/redo06c.dbf') SIZE 1024M,

GROUP 7 ('/data/redo04/ORCL/onlinelog/redo07a.dbf','/data/redo05/ORCL/onlinelog/redo07b.dbf','/data/redo06/ORCL/onlinelog/redo07c.dbf') SIZE 1024M,

GROUP 8 ('/data/redo04/ORCL/onlinelog/redo08a.dbf','/data/redo05/ORCL/onlinelog/redo08b.dbf','/data/redo06/ORCL/onlinelog/redo08c.dbf') SIZE 1024M);

(19) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=21;

GROUP# ARC STATUS

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

21 YES INACTIVE

(19a) If not above Result than run

alter system switch logfile ;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=21;

GROUP# ARC STATUS

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

21 YES INACTIVE

(20) alter database drop logfile group 21;

(21) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=22;

GROUP# ARC STATUS

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

22 YES INACTIVE

(21a) If not above Result than run

alter system switch logfile;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=22;

GROUP# ARC STATUS

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

22 YES INACTIVE

(22) alter database drop logfile group 22;

(23) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=23;

GROUP# ARC STATUS

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

23 YES INACTIVE

(23a) If not above Result than run

alter system switch logfile ;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=23;

GROUP# ARC STATUS

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

23 YES INACTIVE

(24) alter database drop logfile group 23;

(25) ALTER DATABASE ADD LOGFILE THREAD 2

GROUP 9 ('/data/redo04/ORCL/onlinelog/redo09a.dbf','/data/redo05/ORCL/onlinelog/redo09b.dbf','/data/redo06/ORCL/onlinelog/redo09c.dbf') SIZE 1024M;

(26) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=24;

GROUP# ARC STATUS

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

24 YES INACTIVE

(26a) If not above Result than run

alter system switch logfile 24;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=24;

GROUP# ARC STATUS

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

24 YES INACTIVE

(27) alter database drop logfile group 24;

ALTER DATABASE ADD LOGFILE THREAD 2

GROUP 10 ('/data/redo04/ORCL/onlinelog/redo10a.dbf','/data/redo05/ORCL/onlinelog/redo10b.dbf','/data/redo06/ORCL/onlinelog/redo10c.dbf') SIZE 1024M;

(28) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=25;

GROUP# ARC STATUS

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

25 YES INACTIVE

(28a) If not above Result than run

alter system switch logfile ;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=25;

GROUP# ARC STATUS

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

25 YES INACTIVE

(29) alter database drop logfile group 25;

(30) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=26;

GROUP# ARC STATUS

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

26 YES INACTIVE

(30a) If not above Result than run

alter system switch logfile;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=26;

GROUP# ARC STATUS

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

26 YES INACTIVE

(31) alter database drop logfile group 26;

(32) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=27;

GROUP# ARC STATUS

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

27 YES INACTIVE

(31a) If not above Result than run

alter system switch logfile;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=27;

GROUP# ARC STATUS

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

27 YES INACTIVE

(32) alter database drop logfile group 27;

Note:- Find Out the Output and Confirm with Planed Activity.

For Instance 1

select GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024,MEMBERS,ARCHIVED,STATUS from v$log where THREAD#=1;

select GROUP#,MEMBER from v$logfile where GROUP# in (1,2,3,4,5) order by 1;

For Instance 2

select GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024,MEMBERS,ARCHIVED,STATUS from v$log where THREAD#=1;

select GROUP#,MEMBER from v$logfile where GROUP# not in (1,2,3,4,5) order by 1;

Thursday 15 December 2011

Concurrent Services are not getting up

Sometimes after EBS R12 cloning, the Concurrent Services did not get up
ps -ef | grep FND |wc -l
0

Findings:
Login to frontend
Click to OAM ----> Concurrent Manager
Check the hostname
If it is showing hostname of Production Then,

Solution:
While cloning run exec FND_CONC_CLONE.TARGET_CLEAN instead of exec FND_CONC_CLONE.SETUP_CLEAN.
Sometimes SETUP_CLEAN do not properly clean the nodes name.Although it shows nodes count 0 in fnd_nodes.
So its better to run FND_CONC_CLONE.TARGET_CLEAN.

Wednesday 14 December 2011

After EBS R12 Cloning getting error while opening any form

After a successful login, attempts to launch forms based responsibilities fail with the following errors:

Within the Java Console:

proxyHost=null
proxyPort=0
connectMode=HTTP, native.
oracle.forms.net.ConnectionException: Forms session <1> failed during startup: no response from runtime process
at oracle.forms.net.ConnectionException.createConnectionException(Unknown Source)
at oracle.forms.net.HTTPNStream.getResponse(Unknown Source)
at oracle.forms.net.HTTPNStream.doFlush(Unknown Source)
at oracle.forms.net.HTTPNStream.flush(Unknown Source)
at java.io.DataOutputStream.flush(Unknown Source)
at oracle.forms.net.HTTPConnection.connect(Unknown Source)
at oracle.forms.engine.FormsDispatcher.initConnection(Unknown Source)
at oracle.forms.engine.FormsDispatcher.init(Unknown Source)
at oracle.forms.engine.Runform.initConnection(Unknown Source)
at oracle.forms.engine.Runform.startRunform(Unknown Source)
at oracle.forms.engine.Main.createRunform(Unknown Source)
at oracle.forms.engine.Main.start(Unknown Source)
at sun.applet.AppletPanel.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

Solution:

In several customer instances, $ORACLE_HOME/lib32/ldflags was a symbolic link that pointed to a location that did not exist. This was resolved by performing the following UNIX commands to point the $ORACLE_HOME/lib32/ldflags to the $ORACLE_HOME/lib/ldflags :

$ cd $ORACLE_HOME/lib32
$ rm ldflags
$ ln -s $ORACLE_HOME/lib/ldflags ldflags

Then, stop the web tier services (adopmnctl.sh stop) and relink the forms executable(s):

$ cd $ORACLE_HOME/forms/lib32/
$ make -f ins_forms.mk install

Sunday 11 December 2011

Oracle Inventory does not exists on Application Node

Issue: Oracle Inventory does not exists on Application Node

export PATH=$ORACLE_HOME/OPatch:$PATH

$ opatch lsinventory

Oracle Interim Patch Installer version 1.0.0.0.57

Copyright (c) 2007 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under

OPatch/docs for usage reference. We also recommend using

the latest OPatch version. For the latest OPatch version

and other support related issues, please refer to document

293369.1 which is viewable from metalink.oracle.com

Cannot open the file: /etc/oraInst.loc

Please use -invPtrLoc option and specify the correct file for OPatch to look for central inventory.

ERROR: OPatch failed because of Inventory problem.

Solution:

1)   Check oraInst.loc file exists in /etc

2)   If file doesnot exists then create the file and add inventory location

inventory_loc=/home/applclon/oraInventory

Set Permission to oraInst.loc file

3)   Now Run the Installer to create the inventory

$ cd $ORACLE_HOME/oui/bin

$ pwd

/apps_UAT/prodclone/apps/tech_st/10.1.2/oui/bin

./runInstaller -silent -attachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME="/apps_UAT/prodclone/apps/tech_st/10.1.2" ORACLE_HOME_NAME="applclon_apps_tech_st_10_1_2"

Saturday 10 December 2011

Send Mail Using Oracle Package

1) Check Package UTL_MAIL is available

desc sys.utl_mail

PROCEDURE SEND

Argument Name                  Type                    In/Out Default?

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

SENDER                         VARCHAR2                IN

RECIPIENTS                     VARCHAR2                IN

CC                             VARCHAR2                IN     DEFAULT

BCC                            VARCHAR2                IN     DEFAULT

SUBJECT                        VARCHAR2                IN     DEFAULT

MESSAGE                        VARCHAR2                IN     DEFAULT

MIME_TYPE                      VARCHAR2                IN     DEFAULT

PRIORITY                       BINARY_INTEGER          IN     DEFAULT

Package is available, If Package is not available create it using,

SQL> @?/rdbms/admin/utlmail.sql
Package created
Synonym created

2) Create UTL_MAIL Package Body

SQL> sho user

USER is "SYS"

SQL> @?/rdbms/admin/prvtmail.plb

Package created.

Package body created.

Grant succeeded.

Package body created.

No errors.

3) Edit the host file with Mail Server hostname and IP

10.11.1.1       hostname.domainname hostname

4) Port must be Open to access Mail Server

$ telnet delhint1 100

Trying...

Connected to delhint1.iffco.nic.in.

Escape character is '^]'.

220 hostname ESMTP Service (Lotus Domino Release 8.5.2) ready at Wed, 7 Sep 2011 17:25:06 +0530

<<Ctrl c>>

[1] + Stopped (SIGTSTP)        telnet hostname 100

5) Create Directory 'MAIL_DIR'

Conn / as sysdba

CREATE OR REPLACE DIRECTORY MAIL_DIR AS '/home/orauat/vivek/scr/sql/';

6) Run the Procedure

@/home/orauat/vivek/scr/sql/dbstatusmail.sql

$ cat dbstatusmail.sql

alter session SET smtp_out_server = 'hostname:100';

declare

v_utl_filehandler       UTL_FILE.FILE_TYPE;

v_rawfile                       RAW(32767);

v_size                               NUMBER;

v_block                              NUMBER;

v_boolean                   BOOLEAN;

v_file_dir                      varchar2(100);

v_transfer_location varchar2(100) := 'reciever@gmail.com';

v_file_name varchar2(100) := 'dbdetail.txt';

begin

v_file_dir := 'MAIL_DIR';

v_utl_filehandler := UTL_FILE.FOPEN(v_file_dir, v_file_name, 'r');

UTL_FILE.fgetattr(v_file_dir, v_file_name, v_boolean, v_size, v_block);

UTL_FILE.get_raw(v_utl_filehandler, v_rawfile, v_size);

UTL_FILE.FCLOSE(v_utl_filehandler);

sys.utl_mail.send_attach_raw( sender            =>      'sender@gmail.com'

, recipients    =>      v_transfer_location

, subject               =>      'Database backup status from Production'

, message               =>      'Dear Sir'||CHR(13)||CHR(13)||

'Please find attached database status file from Production.'||CHR(13)||CHR(13)||

'For any issue reported in it, please check it on server.'||CHR(13)||CHR(13)||

'Thanks'||CHR(13)||

'Vivek Bhutani'||CHR(13)||CHR(13)

, attachment    =>      v_rawfile

, att_inline    =>      FALSE

, att_filename =>      v_file_name

);

end;

/

exit;

Thursday 8 December 2011

Number of Archives Generation is too high

Number of Archives Generation is too high.Normally around 40 archives got generated from both nodes.But on Monday i was shocked when i checked the no of archives in a day.Around 700 archives were generated in 10 hrs.

Findings:
select trunc(first_time), count(*)
from v$archived_log
where name not like '%dr'
group by trunc(first_time)
order by 1;

(first_time)   count(*)
---------     ----------
27-NOV-11          2
28-NOV-11         20
29-NOV-11         28
30-NOV-11         14
01-DEC-11         17
02-DEC-11         17
03-DEC-11         18
04-DEC-11        359
05-DEC-11        386

DB DATE       TOTAL H00 H01 H02 H03 H04 H05 H06 H07 H08 H09 H10 H11 H12 H13 H14 H15 H16 H17 H18 H19 H20 H21 H22 H23
--- ---------- ------ --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
1 2011-12-01     12   0   0   2   0   0   0   0   0   0   2   0   0   0   2   0   2   0   0   0   0   0   0   2   2
2 2011-12-01     22   0   0   2   0   0   0   2   0   0   2   0   4   0   0   2   2   2   0   0   2   0   0   2   2
1 2011-12-02     12   0   0   2   0   0   0   0   0   0   2   0   0   0   2   0   0   0   0   2   0   0   0   2   2
2 2011-12-02     22   0   0   2   0   0   0   2   0   0   2   0   2   0   2   0   0   2   2   2   2   0   0   0   4
1 2011-12-03     10   0   0   0   0   0   0   0   0   0   2   0   0   2   0   0   2   0   0   0   2   0   0   0   2
2 2011-12-03     26   0   0   2   0   0   0   2   0   0   2   2   0   2   2   0   4   2   2   0   2   0   0   2   2
1 2011-12-04    352   0   0   0   0   0   0   0   0   0   2   0   0   0   2 16 36 42 38 44 34 40 32 28 38
2 2011-12-04    366   0   0   2   0   0   0   2   0   0   2   0   2   0   4 16 36 42 38 44 34 40 36 32 36
1 2011-12-05    384 44 42 22 40 38 36 40 32 30 32 28   0   0   0   0   0   0   0   0   0   0   0   0   0
2 2011-12-05    388 44 42 24 40 38 36 40 32 30 32 28   0   2   0   0   0   0   0   0   0   0   0   0   0

1) The no of archives generated were 700 which inturns can cause me space issue also.Hmmm...But few GB's was used by archives.
Then i checked the size of archive files.The size of archives was in KBs and MBs.

2) It means number of log switch is tooo high.

3) Then i checked the alert log which helped me to diagnose the issue.

Error messages in alert log:
Sun Dec 04 19:51:10 2011
Errors in file /orahome/app/oracle/diag/rdbms/ebsdbprd/ebsdbprd1/trace/ebsdbprd1_arc3_2306070.trc:
ORA-12582: TNS:invalid operation
FAL[server, ARC3]: FAL archive failed, see trace file.
Errors in file /orahome/app/oracle/diag/rdbms/ebsdbprd/ebsdbprd1/trace/ebsdbprd1_arc3_2306070.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance ebsdbprd1 - Archival Error. Archiver continuing.
Sun Dec 04 20:07:13 2011
Errors in file /orahome/app/oracle/diag/rdbms/ebsdbprd/ebsdbprd1/trace/ebsdbprd1_nsa2_1601550.trc:
ORA-00270: error creating archive log

4) It means there is some problem at Standby database.I checked the space at archive destination on standby server.
It was full :( .RMAN backup was failed.I manually deleted old archive files.

Wednesday 7 December 2011

Steps to recreate AWR Repository(Automatic Workload)

We normally do cloning of our databases which sometimes add multiple DBID's in the repository.
We can check the multiple dbid's while generating the awr report using @?/rdbms/admin/awrrpt
To delete all the unwanted dbid's we have to recreate the repository.

DB_ID           DB_NAME   HOST_PLATFORM                                             INST
----------------- ----------- --------------------------------------------------------      -----------
826177187   PRODDEV   ifebdbdev - AIX-Based Systems (64-bit)         1
*3309173529 EBSDBPRD IFEBPRD1 - AIX-Based Systems (64-bit)    1
3309173529 EBSDBPRD IFEBPRD2 - AIX-Based Systems (64-bit)     2
212389454 PRODDDB1 PRODDB - AIX-Based Systems (64-bit)          1

The doc is based on the RAC 2 node database using pfile.

Steps to recreate the AWR (Automatic Workload Repository):

1) shut down application services
2) shut down node 1 database
$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 4 14:50:28 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> create spfile from pfile;
File created.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

3) shut down node 2 database
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

4) Perform rest of the activites on node 1 database
SQL> startup
SQL> select name from v$database;
SQL> show parameter cluster_database
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2

SQL> show parameter statistics_level
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

SQL> show parameter sga_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 4G

SQL> alter system set sga_target=0 scope=spfile;
System altered.

SQL> alter system set cluster_database=false scope=spfile;
System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup restrict
ORACLE instance started.
Total System Global Area 509485056 bytes
Fixed Size                  2208088 bytes
Variable Size             444599976 bytes
Database Buffers           50331648 bytes
Redo Buffers               12345344 bytes
Database mounted.
Database opened.

SQL> show parameter cluster_database
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1

SQL> show parameter statistics_level
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

SQL> show parameter sga_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 0

SQL> alter system set statistics_level = basic scope=both;
System altered.

SQL> show parameter statistics_level
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      BASIC

SQL> @?/rdbms/admin/catnoawr.sql
SQL> alter system flush shared_pool;
SQL> @?/rdbms/admin/catawr.sql
SQL> @?/rdbms/admin/utlrp.sql
sql> @?/rdbms/admin/execsvrm.sql

Remove the spfile created in the previous step and bounce the database with the existing initebsprod1.ora file
SQL> show parameter sga_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 4G

SQL> show parameter statistics_level
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

SQL> show parameter cluster_database;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2

Please check invalid objects exists are not , if exists then please compile it manually
To Create the AWR report, run below script
sql> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

5) startup node2

Tuesday 6 December 2011

OPatch is getting failed with error

Issue:
OPatch is getting failed with error

opatch apply
SEVERE:OPatch invoked as follows: 'apply '
INFO:
Oracle Home       : /ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1
Central Inventory : /ora_DEV/oraInventory
from           : /etc/oraInst.loc
OPatch version    : 11.1.0.6.6
OUI version       : 11.2.0.1.0
OUI location      : /ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/oui
Log file location : /ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/cfgtoollogs/opatch/opatch2011-07-30_10-40-24AM.log

INFO:Patch history file: /ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

INFO:Starting ApplySession at Sat Jul 30 10:40:25 GMT+05:30 2011
INFO:Starting Apply Session at Sat Jul 30 10:40:25 GMT+05:30 2011
INFO:ApplySession applying interim patch '12320006' to OH '/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1'
INFO:Starting to apply patch to local system at Sat Jul 30 10:40:25 GMT+05:30 2011
INFO:Start the Apply initScript at Sat Jul 30 10:40:25 GMT+05:30 2011
INFO:Finish the Apply initScript at Sat Jul 30 10:40:25 GMT+05:30 2011
INFO:Patch 12320006 has no conflicts/superset wiht any other patch processed till now
INFO:Checking conflicts for patch: 12320006
INFO:Checking conflicts/supersets for patch: 12320006 with patch:12320006
INFO:Checking conflicts/supersets for patch: 12320006 with patch:8553944
INFO:Checking conflicts/supersets for patch: 12320006 with patch:8730312
INFO:Checking conflicts/supersets for patch: 12320006 with patch:9147659
INFO:Checking conflicts/supersets for patch: 12320006 with patch:9218789
INFO:
Running prerequisite checks...
INFO:Space Needed : 13812976
INFO:Prereq checkPatchApplicableOnCurrentPlatform Passed for patch : 12320006
INFO:Current working directory is : /ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/OPatch
INFO:OPatch is generic, it is not bundled with OCM.
INFO:
OPatch detected the node list and the local node from the inventory. OPatch will patch the local system then propagate the patch to the remote nodes.

INFO:Start saving patch at Sat Jul 30 10:40:29 GMT+05:30 2011
INFO:Finish saving patch at Sat Jul 30 10:40:29 GMT+05:30 2011
INFO:Start backing up system for restore at Sat Jul 30 10:40:29 GMT+05:30 2011
INFO:Backing up files and inventory (not for auto-rollback) for the Oracle Home
INFO:Backing up files affected by the patch '12320006' for restore. This might take a while...
INFO:Finish backing up system for restore at Sat Jul 30 10:40:29 GMT+05:30 2011
INFO:******* create file /ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/.patch_storage/interim_inventory.txt
INFO:Start backing up system for rollback at Sat Jul 30 10:40:29 GMT+05:30 2011
INFO:Backing up files affected by the patch '12320006' for rollback. This might take a while...
INFO:Finish backing up system for rollback at Sat Jul 30 10:40:29 GMT+05:30 2011
INFO:Start modifying the system at Sat Jul 30 10:40:29 GMT+05:30 2011
INFO:
Patching component oracle.oracore.rsf, 11.2.0.1.0...
INFO:Copying file to "/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/oracore/zoneinfo/timezlrg_16.dat"
INFO:Start Copy Action at Sat Jul 30 10:40:29 GMT+05:30 2011
INFO:Finish Copy Action at Sat Jul 30 10:40:30 GMT+05:30 2011
INFO:Copying file to "/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/oracore/zoneinfo/timezone_16.dat"
INFO:Start Copy Action at Sat Jul 30 10:40:30 GMT+05:30 2011
INFO:Finish Copy Action at Sat Jul 30 10:40:30 GMT+05:30 2011
INFO:Copying file to "/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/oracore/zoneinfo/readme_16.txt"
INFO:Start Copy Action at Sat Jul 30 10:40:30 GMT+05:30 2011
INFO:Finish Copy Action at Sat Jul 30 10:40:30 GMT+05:30 2011
INFO:Copying file to "/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/oracore/zoneinfo/big/timezlrg_16.dat"
INFO:Start Copy Action at Sat Jul 30 10:40:30 GMT+05:30 2011
INFO:Finish Copy Action at Sat Jul 30 10:40:30 GMT+05:30 2011
INFO:Copying file to "/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/oracore/zoneinfo/big/timezone_16.dat"
INFO:Start Copy Action at Sat Jul 30 10:40:30 GMT+05:30 2011
INFO:Finish Copy Action at Sat Jul 30 10:40:30 GMT+05:30 2011
INFO:Copying file to "/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/oracore/zoneinfo/little/timezlrg_16.dat"
INFO:Start Copy Action at Sat Jul 30 10:40:30 GMT+05:30 2011
INFO:Finish Copy Action at Sat Jul 30 10:40:30 GMT+05:30 2011
INFO:Copying file to "/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/oracore/zoneinfo/little/timezone_16.dat"
INFO:Start Copy Action at Sat Jul 30 10:40:30 GMT+05:30 2011
INFO:Finish Copy Action at Sat Jul 30 10:40:30 GMT+05:30 2011
INFO:Finish modifying the system at Sat Jul 30 10:40:30 GMT+05:30 2011
INFO:ApplySession adding interim patch '12320006' to inventory
INFO:Start saving patch to inventory at Sat Jul 30 10:40:30 GMT+05:30 2011
INFO:Registered Encoding value is : ISO8859-1 for the file /ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/inventory/oneoffs/12320006/etc/config/actions.xml
INFO:Finish saving patch to inventory at Sat Jul 30 10:40:30 GMT+05:30 2011
INFO:
Verifying the update...
INFO:Inventory check OK: Patch ID 12320006 is registered in Oracle Home inventory with proper meta-data.
INFO:   verifying 7 copy files.
INFO:Files check OK: Files from Patch ID 12320006 are present in Oracle Home.
INFO:Finish applying patch to local system at Sat Jul 30 10:40:31 GMT+05:30 2011
INFO:Starting to apply patch to remote nodes 'ifebdbdev' 'ifebprd2' at Sat Jul 30 10:40:31 GMT+05:30 2011
INFO:
Patching in all-node mode.

INFO:Updating nodes 'ifebdbdev' 'ifebprd2'
INFO:   Apply-related files are:
FP = "/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/.patch_storage/12320006_Jun_13_2011_23_18_57/rac/copy_files.txt"
DP = "/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/.patch_storage/12320006_Jun_13_2011_23_18_57/rac/copy_dirs.txt"
MP = "/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/.patch_storage/12320006_Jun_13_2011_23_18_57/rac/make_cmds.txt"
RC = "/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/.patch_storage/12320006_Jun_13_2011_23_18_57/rac/remote_cmds.txt"

INFO:Instantiating the file "/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/.patch_storage/12320006_Jun_13_2011_23_18_57/rac/copy_files.txt.instantiated" by replacing $ORACLE_HOME in "/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/.patch_storage/12320006_Jun_13_2011_23_18_57/rac/copy_files.txt" with actual path.
INFO:Propagating files to remote nodes...
WARNING:OUI-67187:
OPatch failed to copy files to remote nodes 'ifebdbdev' 'ifebprd2' . Detail: Error while copying files inside directory '/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1' based on listed file '/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/.patch_storage/12320006_Jun_13_2011_23_18_57/rac/copy_files.txt.instantiated' to nodes 'ifebdbdev,ifebprd2'. [PRKC-1044 : Failed to check remote command execution setup for node ifebdbdev using shells /usr/bin/ssh and /usr/bin/rsh
rshd: 0826-813 Permission is denied.PRKC-1044 : Failed to check remote command execution setup for node ifebprd2 using shells /usr/bin/ssh and /usr/bin/rsh
host: name ifebprd2 NOT FOUND]
INFO:
OPatch failed to copy files to remote nodes 'ifebdbdev' 'ifebprd2' .
Do you want to proceed? [y|n]
INFO:Start to wait for user-input at Sat Jul 30 10:40:31 GMT+05:30 2011
INFO:Finish waiting for user-input at Sat Jul 30 10:41:30 GMT+05:30 2011
INFO:User Responded with: Y
INFO:Instantiating the file "/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/.patch_storage/12320006_Jun_13_2011_23_18_57/rac/copy_dirs.txt.instantiated" by replacing $ORACLE_HOME in "/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/.patch_storage/12320006_Jun_13_2011_23_18_57/rac/copy_dirs.txt" with actual path.
INFO:Propagating directories to remote nodes...
WARNING:OUI-67188:
OPatch failed to copy directories to remote nodes 'ifebdbdev' 'ifebprd2' . Detail: Error while copying files inside directory '/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1' based on listed file '/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/.patch_storage/12320006_Jun_13_2011_23_18_57/rac/copy_dirs.txt.instantiated' with excludeListFile 'null' to nodes 'ifebdbdev,ifebprd2'. [PRCF-2020 : Cannot retrieve the host for node "ifebprd2"
PRCF-2020 : Cannot retrieve the host for node "ifebprd2"]
INFO:
OPatch failed to copy directories to remote nodes 'ifebdbdev' 'ifebprd2' .
Do you want to proceed? [y|n]
INFO:Start to wait for user-input at Sat Jul 30 10:41:30 GMT+05:30 2011
INFO:Finish waiting for user-input at Sat Jul 30 10:41:37 GMT+05:30 2011
INFO:User Responded with: Y
INFO:Finish applying patch to remote nodes at Sat Jul 30 10:41:37 GMT+05:30 2011
INFO:--------------------------------------------------------------------------------
INFO:The following warnings have occurred during OPatch execution:
INFO:1) OUI-67187:
OPatch failed to copy files to remote nodes 'ifebdbdev' 'ifebprd2' . Detail: Error while copying files inside directory '/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1' based on listed file '/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/.patch_storage/12320006_Jun_13_2011_23_18_57/rac/copy_files.txt.instantiated' to nodes 'ifebdbdev,ifebprd2'. [PRKC-1044 : Failed to check remote command execution setup for node ifebdbdev using shells /usr/bin/ssh and /usr/bin/rsh
rshd: 0826-813 Permission is denied.PRKC-1044 : Failed to check remote command execution setup for node ifebprd2 using shells /usr/bin/ssh and /usr/bin/rsh
host: name ifebprd2 NOT FOUND]
INFO:2) OUI-67188:
OPatch failed to copy directories to remote nodes 'ifebdbdev' 'ifebprd2' . Detail: Error while copying files inside directory '/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1' based on listed file '/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/.patch_storage/12320006_Jun_13_2011_23_18_57/rac/copy_dirs.txt.instantiated' with excludeListFile 'null' to nodes 'ifebdbdev,ifebprd2'. [PRCF-2020 : Cannot retrieve the host for node "ifebprd2"
PRCF-2020 : Cannot retrieve the host for node "ifebprd2"]
INFO:--------------------------------------------------------------------------------
SEVERE:OUI-67204:OPatch Session completed with warnings.
INFO:Finishing ApplySession at Sat Jul 30 10:41:37 GMT+05:30 2011
INFO:Total time spent waiting for user-input is 65 seconds. Finish at Sat Jul 30 10:41:37 GMT+05:30 2011

$ opatch lsinventory
Invoking OPatch 11.1.0.6.6
Oracle Interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation. All rights reserved.
Oracle Home       : /ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1
Central Inventory : /ora_DEV/oraInventory
from           : /etc/oraInst.loc
OPatch version    : 11.1.0.6.6
OUI version       : 11.2.0.1.0
OUI location      : /ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/oui
Log file location : /ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/cfgtoollogs/opatch/opatch2011-08-03_16-16-12PM.log
Patch history file: /ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/cfgtoollogs/opatch/opatch_history.txt
Lsinventory Output file location : /ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2011-08-03_16-16-12PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 11g                                                  11.2.0.1.0
Oracle Database 11g Examples                                         11.2.0.1.0
There are 2 products installed in this Oracle Home.
Interim patches (5) :
Patch 12320006     : applied on Sat Jul 30 10:40:30 GMT+05:30 2011
Unique Patch ID: 13845592
Created on 13 Jun 2011, 23:18:57 hrs PST8PDT
Bugs fixed:
12320006

Patch 8553944      : applied on Wed Apr 13 15:35:57 GMT+05:30 2011
Unique Patch ID: 13551690
Created on 22 Mar 2011, 23:46:42 hrs PST8PDT
Bugs fixed:
8553944

Patch 8730312      : applied on Wed Sep 29 00:57:31 GMT+05:30 2010
Unique Patch ID: 12202102
Created on 14 Feb 2010, 21:47:42 hrs PST8PDT
Bugs fixed:
8730312

Patch 9147659      : applied on Fri Sep 24 00:23:37 GMT+05:30 2010
Unique Patch ID: 12128903
Created on 18 Jan 2010, 03:05:31 hrs PST8PDT
Bugs fixed:
9037724, 9007836, 9004697

Patch 9218789      : applied on Thu Jul 01 17:39:01 GMT+05:30 2010
Unique Patch ID: 12217103
Created on 16 Feb 2010, 14:41:20 hrs PST8PDT
Bugs fixed:
9218789

Rac system comprising of multiple nodes
Local node = ifebprd1
Remote node = ifebdbdev
Remote node = ifebprd2
--------------------------------------------------------------------------------

OPatch succeeded.

Findings:
Sometimes we simply copy the Oracle binaries from database server to another server and then create a new database.
The new database will work fine.But we can face issue while applying an patch.
In the above example we copied the binaries from a RAC database(ifebprd) to non-rac database(ifebdbdev).
While applying the opatch an error occured.

Solution:
cd /etc
vi oraInst.loc
inst_group=oinstall
inventory_loc=/home/applprod/oraInventory

Change the inventory_loc entry with right location.
inst_group=oinstall
inventory_loc=/ora_DEV/oraInventory

# chown oradev:dba oraInst.loc
# chmod 777 oraInst.loc

cd $ORACLE_HOME/oui/bin
./runInstaller -silent -attachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME="/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1" ORACLE_HOME_NAME="proddev_oradev_11_2_0_1"
Starting Oracle Universal Installer...
No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-10-15_01-08-47PM. Please wait ...$ 'AttachHome' was successful.

Now try the opatch.